ALTER PROCEDURE dbo.PL_REQUEST_DOC_DT_ById
@P_REQ_ID varchar(15),
@P_TYPE VARCHAR(20),
@P_USER_LOGIN VARCHAR(20)
AS


			DECLARE @listTRADE TABLE
			(
				GOODS_ID VARCHAR(20),
				TRADE_ID VARCHAR(20),
				PLAN_ID VARCHAR(20),
				AMT_APP DECIMAL(18,2),
				AMT_EXE DECIMAL (18,2),
				AMT_ETM DECIMAL (18,2),
				AMT_TF DECIMAL (18,2),
				AMT_RECEIVE_TF DECIMAL (18,2),
        NOTES NVARCHAR(1000),
        AMT_ETM_TMP DECIMAL (18,2)
				
			)

--			IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID='APPROVE'))
--			BEGIN
--				IF(EXISTS(SELECT * FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID))
--				BEGIN
--					INSERT INTO @listTRADE
--					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES)
--					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID
--				END
--				ELSE
--				BEGIN
--					INSERT INTO @listTRADE
--					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF, NOTES)
--					SELECT PT.GOODS_ID,PT.TRADE_ID,PT.PLAN_ID,PT.AMT_APP,PT.AMT_EXE,PT.AMT_ETM-PLDT.TOTAL_AMT,PT.AMT_TF-PLFT.TOTAL_AMT,PT.AMT_RECEIVE_TF-PLTT.TOTAL_AMT, PT.NOTES FROM dbo.PL_TRADEDETAIL PT
--					LEFT JOIN 
--					(
--					SELECT TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
--					dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
--					GROUP BY TRADE_ID
--					) PLDT ON PLDT.TRADE_ID = PT.TRADE_ID
--					LEFT JOIN 
--					(
--					SELECT FR_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
--					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
--					GROUP BY FR_TRADE_ID
--					) PLFT ON PLFT.FR_TRADE_ID = PT.TRADE_ID
--
--					LEFT JOIN 
--					(
--					SELECT TO_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM
--					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
--					GROUP BY TO_TRADE_ID
--					) PLTT ON PLTT.TO_TRADE_ID = PT.TRADE_ID
--					WHERE (PT.TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR PT.TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
--				END
--			END
			
      
      IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID IN ('','SIGN','APPNEW','REJECT')))
      BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
					SELECT DISTINCT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,
          (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
              FROM dbo.PL_REQUEST_DOC_DT DDT
              LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
              WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
              AND DDT.TRADE_ID = PL.TRADE_ID
              AND DOC.REQ_ID <> @p_REQ_ID)
          +
          (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
          FROM dbo.PL_REQUEST_TRANSFER DDT
          LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
          WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
          AND DDT.FR_TRADE_ID = PL.TRADE_ID
          AND DOC.REQ_ID <> @p_REQ_ID)
          AS AMT_ETM_TMP
          FROM dbo.PL_TRADEDETAIL PL
          WHERE (TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID)
          OR TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
      END
      ELSE
			BEGIN
					INSERT INTO @listTRADE
					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
          SELECT * FROM
          (
          SELECT DISTINCT GOODS_ID,TRADE_ID,PLAN_ID,ISNULL(AMT_APP,0) AS AMT_APP,ISNULL(AMT_EXE,0) AS AMT_EXE,ISNULL(AMT_ETM,0) AS AMT_ETM,ISNULL(AMT_TF,0) AS AMT_TF,ISNULL(AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,'' NOTES,ISNULL(AMT_ETM_TMP,0) AS AMT_ETM_TMP FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID
          UNION ALL
          SELECT DISTINCT FR_GOOD_ID GOODS_ID,FR_TRADE_ID TRADE_ID,FR_PLAN_ID PLAN_ID,ISNULL(FR_AMT_APP,0) AMT_APP,ISNULL(FR_AMT_EXE,0) AMT_EXE,ISNULL(FR_AMT_ETM,0) AMT_ETM,ISNULL(FR_AMT_TF,0) AMT_TF,ISNULL(FR_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(FR_AMT_ETM_TMP,0) AMT_ETM_TMP FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
          UNION ALL
          SELECT DISTINCT TO_GOOD_ID GOODS_ID,TO_TRADE_ID TRADE_ID,TO_PLAN_ID PLAN_ID,ISNULL(TO_AMT_APP,0) AMT_APP,ISNULL(TO_AMT_EXE,0) AMT_EXE,ISNULL(TO_AMT_ETM,0) AMT_ETM,ISNULL(TO_AMT_TF,0) AMT_TF,ISNULL(TO_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(TO_AMT_ETM_TMP,0) AMT_ETM_TMP FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID
          ) T GROUP BY GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP
			END

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID  VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0

DECLARE
@COST_ID TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID TABLE (
	DVDM_ID VARCHAR(15)
)


SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@P_USER_LOGIN)

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@P_USER_LOGIN)
 IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
		SET @IS_ALL=1
SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@P_USER_LOGIN)

	INSERT INTO @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID



IF(@P_TYPE='DVKD')
BEGIN

SELECT DISTINCT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,A.NAME as NOTES,A.DESCRIPTION,A.HANGHOA_ID,A.HANGHOA_ID HH_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,A.REQ_ID,
  ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) AS GD_CODE, ISNULL(B.GD_ID,DT_LOG.GOOD_ID) AS GD_ID, ISNULL(B.GD_NAME,DT_LOG.GOOD_NAME) AS GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
  
	ISNULL(PL.AMT_APP,0) AS AMT_APP,
	ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
	ISNULL(PL.AMT_ETM,0) + ISNULL(PL.AMT_ETM_TMP,0) AS AMT_ETM,
	ISNULL(PL.AMT_TF,0) AS AMT_TF,
	ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)- ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL(PL.AMT_ETM_TMP,0) AS AMT_REMAIN_ETM,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID AND Temp.GOODS_ID = A.GOODS_ID
	AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
	GROUP BY Temp.TRADE_ID),0)
	 AS AMT_REMAIN_REQ,
	A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
	 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT,CASE WHEN A.TRADE_TYPE ='VCCB' THEN N'Theo quy định BVB' ELSE N'Chỉ định thầu' END AS TRADE_TYPE_NAME,
	-- GIANT 24/12/2021 MỚI SỬA Ở TTCT CHƯA SỬA Ở MÀN HÌNH LIÊN QUAN
	AA.REQ_CODE,
	CASE WHEN A.PLAN_ID IS NOT NULL AND A.PLAN_ID <> '' THEN AC1.BRANCH_NAME ELSE AC2.BRANCH_NAME END AS BRANCH_NAME_FEE,
	CASE WHEN A.PLAN_ID IS NOT NULL AND A.PLAN_ID <> '' THEN AC1.BRANCH_ID ELSE AC2.BRANCH_ID END AS BRANCH_ID_FEE,
	CASE WHEN A.PLAN_ID IS NOT NULL AND A.PLAN_ID <> '' THEN AD1.DEP_NAME ELSE AD2.DEP_NAME END AS DEP_NAME_FEE,
	CASE WHEN A.PLAN_ID IS NOT NULL AND A.PLAN_ID <> '' THEN AD1.DEP_ID ELSE AD2.DEP_ID END AS DEP_ID_FEE,
	CASE WHEN A.PLAN_ID IS NOT NULL AND A.PLAN_ID <> '' THEN ISNULL(AD1.DEP_NAME + ' - ','') + AC1.BRANCH_NAME ELSE ISNULL(AD2.DEP_NAME + ' - ','') + AC2.BRANCH_NAME END AS BRANCH_FEE_FULL,
	CASE WHEN A.REQDT_TYPE = 'I' THEN 1 ELSE 0 END AS IS_NGAN_SACH,
	AB.YEAR AS GD_YEAR
	FROM PL_REQUEST_DOC_DT A
	LEFT JOIN @listTRADE PL ON A.TRADE_ID=PL.TRADE_ID
	LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
	LEFT JOIN dbo.PL_IMPORT_DT DT_LOG ON DT_LOG.TRADE_ID=A.TRADE_ID
	LEFT JOIN dbo.CM_GOODSTYPE GT ON ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) LIKE '%.' + GT.GD_TYPE_CODE +'.%'
	LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

	LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
	LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
	LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
	LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
	-- GIANT 23/12/2021
	LEFT JOIN PL_REQUEST_DOC AA ON AA.REQ_ID = A.REQ_ID
	LEFT JOIN PL_MASTER AB ON AB.PLAN_ID = A.PLAN_ID
	LEFT JOIN CM_BRANCH AC1 ON AC1.BRANCH_ID = AB.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT AD1 ON AD1.DEP_ID = AB.DEPT_ID
	LEFT JOIN dbo.TL_USER US ON US.TLNANME = A.MAKER_ID
	LEFT JOIN CM_BRANCH AC2 ON AC2.BRANCH_ID = US.TLSUBBRID
	LEFT JOIN CM_DEPARTMENT AD2 ON AD2.DEP_ID = US.SECUR_CODE
	 WHERE A.REQ_ID=@P_REQ_ID 
	 END
	ELSE IF(@P_TYPE='PDTT')
	BEGIN

	SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
	  ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) AS GD_CODE, ISNULL(B.GD_ID,DT_LOG.GOOD_ID) AS GD_ID, ISNULL(B.GD_NAME,DT_LOG.GOOD_NAME) AS GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
  
	ISNULL(PL.AMT_APP,0) AS AMT_APP,
	ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
	ISNULL(PL.AMT_ETM,0) + ISNULL(PL.AMT_ETM_TMP,0) AS AMT_ETM,
	ISNULL(PL.AMT_TF,0) AS AMT_TF,
	ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)- ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL(PL.AMT_ETM_TMP,0) AS AMT_REMAIN_ETM,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
	AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
	GROUP BY Temp.TRADE_ID),0)
	 AS AMT_REMAIN_REQ,
	A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
	 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT,CASE WHEN A.TRADE_TYPE ='VCCB' THEN N'Theo quy định BVB' ELSE N'Chỉ định thầu' END AS TRADE_TYPE_NAME
	FROM PL_REQUEST_DOC_DT A
	LEFT JOIN @listTRADE PL ON A.TRADE_ID=PL.TRADE_ID
	LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
	LEFT JOIN dbo.PL_IMPORT_DT DT_LOG ON DT_LOG.TRADE_ID=A.TRADE_ID
	LEFT JOIN dbo.CM_GOODSTYPE GT ON ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) LIKE '%.' + GT.GD_TYPE_CODE +'.%'
	LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

	LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
	LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
	LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
	LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
	LEFT JOIN dbo.PL_REQUEST_TRANSFER PLT ON PLT.REQ_DOC_ID=A.REQ_ID AND PLT.TO_GOOD_ID=A.GOODS_ID
	 WHERE A.REQ_ID=@P_REQ_ID 
	 --AND (@ROLE='TGD' OR @IS_ALL=1   OR @ROLE='HDQT' OR  EXISTS(SELECT * FROM @DVDM_ID WHERE [@DVDM_ID].DVDM_ID=A.DVDM_ID OR [@DVDM_ID].DVDM_ID=A.KHOI_ID ) OR  EXISTS(SELECT * FROM @DVDM_ID WHERE [@DVDM_ID].DVDM_ID=PLT.FR_DVDM_ID OR [@DVDM_ID].DVDM_ID=PLT.FR_KHOI_ID ))
	 END
	 ELSE IF(@P_TYPE='XLTT')
	BEGIN

	SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
	  ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) AS GD_CODE, ISNULL(B.GD_ID,DT_LOG.GOOD_ID) AS GD_ID, ISNULL(B.GD_NAME,DT_LOG.GOOD_NAME) AS GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
  
	ISNULL(PL.AMT_APP,0) AS AMT_APP,
	ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
	ISNULL(PL.AMT_ETM,0) + ISNULL(PL.AMT_ETM_TMP,0) AS AMT_ETM,
	ISNULL(PL.AMT_TF,0) AS AMT_TF,
	ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)- ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL(PL.AMT_ETM_TMP,0) AS AMT_REMAIN_ETM,
	ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
	AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
	GROUP BY Temp.TRADE_ID),0)
	 AS AMT_REMAIN_REQ,
	A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
	 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT,CASE WHEN A.TRADE_TYPE ='VCCB' THEN N'Theo quy định BVB' ELSE N'Chỉ định thầu' END AS TRADE_TYPE_NAME
	FROM PL_REQUEST_DOC_DT A
	LEFT JOIN @listTRADE PL ON A.TRADE_ID=PL.TRADE_ID
	LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
	LEFT JOIN dbo.PL_IMPORT_DT DT_LOG ON DT_LOG.TRADE_ID=A.TRADE_ID
	LEFT JOIN dbo.CM_GOODSTYPE GT ON ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) LIKE '%.' + GT.GD_TYPE_CODE +'.%'
	LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

	LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
	LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
	LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
	LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
	 WHERE A.REQ_ID=@P_REQ_ID 
	 --AND (@ROLE='TGD' OR @IS_ALL=1 OR @ROLE='HDQT' OR @ROLE ='KSV' OR @ROLE ='GDV' OR  EXISTS(SELECT * FROM @DVDM_ID WHERE [@DVDM_ID].DVDM_ID=A.DVDM_ID OR [@DVDM_ID].DVDM_ID=A.KHOI_ID )
	 --OR EXISTS
	 --(SELECT * FROM dbo.CM_GOOD_DVDM CGD WHERE CGD.GD_ID=A.GOODS_ID AND CGD.DVCM_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
	 --OR EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER PT WHERE PT.TO_TRADE_ID=A.TRADE_ID AND PT.TO_GOOD_ID=A.GOODS_ID AND 
	 --(PT.FR_BRN_ID=@BRANCH_ID AND ( PT.FR_DEP_ID=@DEP_ID OR PT.FR_DEP_ID IS NULL OR PT.FR_DEP_ID='') ))
	 --)

 END

ELSE IF( @P_TYPE='PYC')
BEGIN

SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) AS GD_CODE, ISNULL(B.GD_ID,DT_LOG.GOOD_ID) AS GD_ID, ISNULL(B.GD_NAME,DT_LOG.GOOD_NAME) AS GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT,CASE WHEN A.TRADE_TYPE ='VCCB' THEN N'Theo quy định BVB' ELSE N'Chỉ định thầu' END AS TRADE_TYPE_NAME
FROM PL_REQUEST_DOC_DT A
LEFT JOIN @listTRADE PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.PL_IMPORT_DT DT_LOG ON DT_LOG.TRADE_ID=A.TRADE_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON B.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
 WHERE A.REQ_ID=@P_REQ_ID 

 UNION ALL
 
SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) AS GD_CODE, ISNULL(B.GD_ID,DT_LOG.GOOD_ID) AS GD_ID, ISNULL(B.GD_NAME,DT_LOG.GOOD_NAME) AS GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT,CASE WHEN A.TRADE_TYPE ='VCCB' THEN N'Theo quy định BVB' ELSE N'Chỉ định thầu' END AS TRADE_TYPE_NAME
FROM PL_REQUEST_DOC_DT A
LEFT JOIN @listTRADE PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.PL_IMPORT_DT DT_LOG ON DT_LOG.TRADE_ID=A.TRADE_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON ISNULL(B.GD_CODE,DT_LOG.GOOD_CODE) LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
 WHERE A.REQ_ID IN (SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_PARENT_ID =@P_REQ_ID)
 END