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)
			)

			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
			ELSE
			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 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

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 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=@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) 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
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) 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=@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