ALTER   PROC [dbo].[TR_REQ_PAY_GetPOSchedule]
	@p_REF_ID VARCHAR(150) = NULL,
	@p_TOP INT = 1000
AS
BEGIN -- PAGING
	-- NHIEU PO HOAC HOP DONG
		DECLARE @l_LSTSERI TABLE (
		[ID] [int] IDENTITY(1,1) NOT NULL,
		[VALUE] [NVARCHAR](MAX) NULL)
		DECLARE @l_FILENAME VARCHAR(MAX)
		INSERT INTO @l_LSTSERI SELECT VALUE FROM WSISPLIT(@p_REF_ID,',')

IF(@p_TOP IS NULL OR @p_TOP='' OR @p_TOP=0)
BEGIN
-- PAGING BEGIN
	SELECT AA.* 
-- SELECT END
	FROM
	(
		SELECT A.PO_ID AS REF_ID,P.PO_CODE AS REF_CODE, A.PAY_ID,A.AMOUNT, A.EXP_DT,A.[PERCENT], A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
			ISNULL((A.AMOUNT - (ISNULL(B.TONG_TAM_UNG,0)- ISNULL(B.TONG_TAM_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS, '' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES, 0.0 AS AMT_PAY_REAL,
			'' AS CURRENCY,  0.0 AS RATE, '' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID,'' AS RECORD_STATUS, A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
		FROM TR_PO_PAYMENT A
			-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
			LEFT JOIN 
			(
				SELECT B1.REQ_PAY_ID, B2.REQ_PAY_CODE, B1.PAY_ID, B1.PAY_PHASE,SUM(B1.AMT_PAY_REAL) AS TONG_TAM_UNG
				FROM TR_REQ_PAY_SCHEDULE B1
				LEFT JOIN TR_REQ_ADVANCE_PAYMENT B2 ON B1.REQ_PAY_ID = B2.REQ_PAY_ID
				WHERE B1.AUTH_STATUS_KT='A' AND B1.TRN_TYPE='ADV_PAY' 
				GROUP BY B1.REQ_PAY_ID,B1.PAY_ID,B1.PAY_PHASE
			)B ON A.PAY_ID = B.PAY_ID
			---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
			LEFT JOIN 
			(
				SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A'
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) SH ON A.PAY_ID = SH.PAY_ID 
			-- LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
			---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
			LEFT JOIN 
			(
				SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) TU ON A.PAY_ID = TU.PAY_ID 
			--- DIEU KIEN
			LEFT JOIN TR_PO_MASTER P ON A.PO_ID = P.PO_ID
		WHERE A.PO_ID IN (SELECT VALUE FROM @l_LSTSERI)
	UNION 
		SELECT A.CONTRACT_ID AS REF_ID,C.CONTRACT_CODE AS REF_CODE,A.PAY_ID, A.AMOUNT,A.EXPECTED_DT AS EXP_DT, A.[PERCENT],A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
			ISNULL((A.AMOUNT - (ISNULL(HU.TONG_HOAN_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS,'' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES,0.0 AS AMT_PAY_REAL,
			'' AS CURRENCY,  0.0 AS RATE,'' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID, '' AS RECORD_STATUS,A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
		FROM TR_CONTRACT_PAYMENT A
			-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
			LEFT JOIN 
			(
				SELECT B1.REQ_PAY_ID, B2.REQ_PAY_CODE, B1.PAY_ID, B1.PAY_PHASE,SUM(B1.AMT_PAY_REAL) AS TONG_TAM_UNG
				FROM TR_REQ_PAY_SCHEDULE B1
				LEFT JOIN TR_REQ_ADVANCE_PAYMENT B2 ON B1.REQ_PAY_ID = B2.REQ_PAY_ID
				WHERE B1.AUTH_STATUS_KT='A' AND B1.TRN_TYPE='ADV_PAY' 
				GROUP BY B1.REQ_PAY_ID,B1.PAY_ID,B1.PAY_PHASE
			)B ON A.PAY_ID = B.PAY_ID
			-- LAY SO TIEN DA HOAN UNG CUA KI DO
			LEFT JOIN 
			(
				SELECT PAY_ID,PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_HOAN_UNG
				FROM TR_REQ_PAY_SCHEDULE
				WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY' AND PROCESS='2'
				GROUP BY PAY_ID, PAY_PHASE
			)HU ON  A.PAY_ID = HU.PAY_ID
			---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
			LEFT JOIN 
			(
				SELECT P.PAY_ID, P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE ='PAY'
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) SH ON A.PAY_ID = SH.PAY_ID 
			---LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
			LEFT JOIN 
			(
				SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) TU ON A.PAY_ID = TU.PAY_ID 
			--- DIEU KIEN
			INNER JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID
		WHERE A.CONTRACT_ID IN (SELECT VALUE FROM @l_LSTSERI) --- Nghia la REF_ID truyen xuong
	) AA
	WHERE 1 = 1
-- PAGING END
	END;
    ELSE 
	BEGIN
-- PAGING BEGIN
SELECT AA.* 
-- SELECT END
	FROM
	(
		SELECT A.PO_ID AS REF_ID,P.PO_CODE AS REF_CODE, A.PAY_ID,A.AMOUNT, A.EXP_DT,A.[PERCENT], A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
			ISNULL((A.AMOUNT - (ISNULL(B.TONG_TAM_UNG,0)- ISNULL(B.TONG_TAM_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS, '' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES, 0.0 AS AMT_PAY_REAL,
			'' AS CURRENCY,  0.0 AS RATE, '' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID,'' AS RECORD_STATUS, A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
		FROM TR_PO_PAYMENT A
			-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
			LEFT JOIN 
			(
				SELECT REQ_PAY_ID, PAY_ID, PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_TAM_UNG
				FROM TR_REQ_PAY_SCHEDULE
				WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY' 
				GROUP BY REQ_PAY_ID,PAY_ID,PAY_PHASE
			)B ON A.PAY_ID = B.PAY_ID
			---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
			LEFT JOIN 
			(
				SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A'
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) SH ON A.PAY_ID = SH.PAY_ID 
			-- LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
			---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
			LEFT JOIN 
			(
				SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) TU ON A.PAY_ID = TU.PAY_ID 
			--- DIEU KIEN
			LEFT JOIN TR_PO_MASTER P ON A.PO_ID = P.PO_ID
		WHERE A.PO_ID IN (SELECT VALUE FROM @l_LSTSERI)
	UNION 
		SELECT A.CONTRACT_ID AS REF_ID,C.CONTRACT_CODE AS REF_CODE,A.PAY_ID, A.AMOUNT,A.EXPECTED_DT AS EXP_DT, A.[PERCENT],A.PAY_PHASE, ISNULL(B.PAY_PHASE,SH.PAY_PHASE) KY_TAM_UNG,ISNULL(B.TONG_TAM_UNG,0) REQ_AMT, ISNULL(SH.AMT_PAY_DO,0) AMT_PAY_DO,
			ISNULL((A.AMOUNT - (ISNULL(HU.TONG_HOAN_UNG,0)+ISNULL(SH.AMT_PAY_DO,0)+ISNULL(TU.AMT_LINK,0))),0) REMAIN_AMT, '' PROCESS,'' AS REQ_PAY_DESC, '' AS REQ_PAY_ENTRIES,0.0 AS AMT_PAY_REAL,
			'' AS CURRENCY,  0.0 AS RATE,'' AS REQ_PAY_AD_CODE,B.REQ_PAY_ID AS REQ_ADV_ID, '' AS RECORD_STATUS,A.[PERCENT] AS TY_LE,ISNULL(TU.AMT_LINK,0) AS AMT_LINK
		FROM TR_CONTRACT_PAYMENT A
			-- LAY TONG SO TIEN DA TAM UNG CUA KY DO
			LEFT JOIN 
			(
				SELECT REQ_PAY_ID, PAY_ID,PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_TAM_UNG
				FROM TR_REQ_PAY_SCHEDULE
				WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY'
				GROUP BY REQ_PAY_ID, PAY_ID, PAY_PHASE
			)B ON  A.PAY_ID = B.PAY_ID
			-- LAY SO TIEN DA HOAN UNG CUA KI DO
			LEFT JOIN 
			(
				SELECT PAY_ID,PAY_PHASE,SUM(AMT_PAY_REAL) AS TONG_HOAN_UNG
				FROM TR_REQ_PAY_SCHEDULE
				WHERE AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY' AND PROCESS='2'
				GROUP BY PAY_ID, PAY_PHASE
			)HU ON  A.PAY_ID = HU.PAY_ID
			---LAY SO TIEN DA THANH TOAN CUA KY DO NEU CO
			LEFT JOIN 
			(
				SELECT P.PAY_ID, P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_PAY_REAL) AS AMT_PAY_DO
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE ='PAY'
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) SH ON A.PAY_ID = SH.PAY_ID 
			---LAY SO TIEN LINK TOI PHIEU TAM UNG NOI BO
			LEFT JOIN 
			(
				SELECT P.PAY_ID,P.PAY_PHASE, MAX(P.AMT_PAY) AS AMT_PAY, SUM(AMT_REMAIN) AS AMT_LINK
				FROM TR_REQ_PAY_SCHEDULE P
				WHERE P.TRN_TYPE='PAY' AND P.AUTH_STATUS_KT ='A' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>''
				GROUP BY P.PAY_ID,P.PAY_PHASE
			) TU ON A.PAY_ID = TU.PAY_ID 
			--- DIEU KIEN
			INNER JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID
		WHERE A.CONTRACT_ID IN (SELECT VALUE FROM @l_LSTSERI) --- Nghia la REF_ID truyen xuong
	) AA
	WHERE 1 = 1
-- PAGING END
    END;
END -- PAGING