ALTER   PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Byid]
@p_REQ_PAY_ID	varchar(15)= NULL
AS
		SELECT A.*,ISNULL(B.AUTH_STATUS_NAME,N'Chờ duyệt') AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
		BR.BRANCH_NAME BRANCH_NAME, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, 
		A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE  ISNULL(H.SOTIEN_TT,0) END AS TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
		TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,
		PO.PO_CODE, PO.PO_NAME AS ACC_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS ACC_NAME,PM.PAY_PHASE AS KY_TAM_UNG,
		DV.DVDM_CODE, DV.DVDM_NAME, PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103))  AS CONF_STATUS, PC1.NOTES AS NEXT_STEP,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, BR1.BRANCH_TYPE AS BRANCH_TYPE_CR, BR.BRANCH_TYPE
        FROM TR_REQ_ADVANCE_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON A.DEP_ID= DP.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE' AND CDTYPE = 'REQ_PAY_ADVANCE'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'
			 LEFT JOIN
			 (
			 SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID = H.PAY_ADV_ID
			 LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'
			 LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID
			 --LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
			  LEFT JOIN 
			 (
				SELECT MAX(PR.PROCESS_ID) AS PROCESS_ID , MAX(PR.REQ_ID) AS REQ_ID, MAX(PR.PROCESS_DESC) AS PROCESS_DESC, MAX(APPROVE_DT) AS APP_DT FROM PL_PROCESS PR 
				GROUP BY PR.PROCESS_ID, PR.REQ_ID
			 ) AS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID

			 --LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C'
			 LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME	
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
        WHERE 1=1 
			  AND(A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL)