ALTER   PROC [dbo].[TR_REQ_ADVANCE_DT_BYID]
@p_REQ_PAY_ID VARCHAR(15)= NULL
AS
BEGIN
SELECT A.*, 
--A.REF_ID AS REF_CODE,
CASE WHEN ISNULL(B.PO_CODE,C.CONTRACT_CODE) IS NOT NULL AND ISNULL(B.PO_CODE,C.CONTRACT_CODE) <>'' THEN ISNULL(B.PO_CODE,C.CONTRACT_CODE) ELSE A.REF_ID END AS REF_CODE, 
ISNULL(B.PO_NAME, C.CONTRACT_NAME) AS REF_NAME, 
ISNULL(S1.SUP_NAME, S2.SUP_NAME) AS SUP_NAME,
ISNULL(S1.TAX_NO, S2.TAX_NO) AS SUP_TAX_NO,
ISNULL(D1.REQ_CODE, D2.REQ_CODE) AS SO_PYC, ISNULL(P1.REQ_CODE, P2.REQ_CODE) AS SO_TO_TRINH, 
ISNULL(P1.REQ_NAME, P2.REQ_NAME) AS TEN_TO_TRINH, '' AS IS_CLOSED, ISNULL(D1.REQ_ID,D2.REQ_ID) AS PYC_ID,ISNULL(P1.REQ_ID, P2.REQ_ID) AS TO_TRINH_ID,
CASE WHEN A.REF_TYPE ='P' THEN 'BVB'+  ISNULL((SELECT ' - ' + BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_CODE = LEFT(S1.ACC_NUM,3)),'') ELSE 'BVB'+  ISNULL((SELECT ' - '+ BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_CODE = LEFT(S2.ACC_NUM,3)),'') END AS BANK_NAME, ISNULL(S1.ACC_NAME,S2.ACC_NAME) AS ACC_NAME, ISNULL(S1.ACC_NUM, S2.ACC_NUM) AS ACC_NUM,
ISNULL(S1.BANK_NAME_OUT, S2.BANK_NAME_OUT) AS BANK_NAME_OUT, ISNULL(S1.ACC_NAME_OUT,S2.ACC_NAME_OUT) AS ACC_NAME_OUT, ISNULL(S1.ACC_NUM_OUT, S2.ACC_NUM_OUT) AS ACC_NUM_OUT, ISNULL(C.CONT_TYPE,'') AS CONT_TYPE,
CASE WHEN CONVERT(DATE, C.END_DT, 103) < CONVERT(DATE, GETDATE(), 103) AND C.END_DT IS NOT NULL AND C.END_DT <> '' 
THEN N'Hợp đồng số :' + C.CONTRACT_ID + N' đã hết hiệu lực kể từ ngày ' + FORMAT(DATEADD(DAY,1,C.END_DT),'dd/MM/yyyy') ELSE '' END AS IS_FLAG_END,
--doanptt 150922 them so hop dong
C.CONTRACT_ID, C.CONTRACT_CODE, C.[CONTRACT_NAME], ISNULL(C.END_DT, GETDATE()) AS END_DT
FROM TR_REQ_ADVANCE_DT A
LEFT JOIN TR_PO_MASTER B ON A.REF_ID= B.PO_ID 
LEFT JOIN CM_SUPPLIER S1 ON B.SUP_ID = S1.SUP_ID 
LEFT JOIN TR_CONTRACT C ON A.REF_ID= C.CONTRACT_ID 
LEFT JOIN CM_SUPPLIER S2 ON C.SUP_ID = S2.SUP_ID 
LEFT JOIN TR_REQUEST_DOC D1 ON C.REQ_DOC_ID = D1.REQ_ID
LEFT JOIN PL_REQUEST_DOC P1 ON D1.PL_REQ_ID = P1.REQ_ID
-- PO
LEFT JOIN TR_REQUEST_DOC D2 ON B.REQ_DOC_ID = D2.REQ_ID
LEFT JOIN PL_REQUEST_DOC P2 ON D2.PL_REQ_ID = P2.REQ_ID
WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID =''
END