ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_DT_Search]
@P_REQ_ID varchar(15),
@P_GD_CODE VARCHAR(15),
@p_GD_NAME NVARCHAR(500),
@p_DES_GOOD NVARCHAR(500)
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

SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,
PL.AMT_APP,PL.AMT_EXE,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  B.GD_CODE, B.GD_ID, B.GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME
 ,ISNULL(PL.AMT_APP,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
 ISNULL(PL.AMT_APP,0) - ISNULL(PL.AMT_EXE,0) - ISNULL((SELECT SUM(Temp.TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.GOODS_ID=A.GOODS_ID AND Temp.TRADE_ID=A.TRADE_ID GROUP BY Temp.PLAN_ID,Temp.TRADE_ID,Temp.GOODS_ID,Temp.REQ_ID),0) AS AMT_ETM,A.REQDT_TYPE,
  A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE, 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 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_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 (B.GD_CODE LIKE '%'+ @P_GD_CODE +'%' OR @P_GD_CODE IS NULL OR @P_GD_CODE ='')
 AND (B.GD_NAME LIKE '%'+ @P_GD_NAME +'%' OR @P_GD_NAME IS NULL OR @P_GD_NAME ='')
 AND (A.NAME LIKE '%'+ @P_DES_GOOD +'%' OR @P_DES_GOOD IS NULL OR @P_DES_GOOD ='')
 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.AMT_APP,PL.AMT_EXE,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) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
 ISNULL(PL.AMT_APP,0) - ISNULL(PL.AMT_EXE,0) - ISNULL((SELECT SUM(Temp.TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.GOODS_ID=A.GOODS_ID AND Temp.TRADE_ID=A.TRADE_ID GROUP BY Temp.PLAN_ID,Temp.TRADE_ID,Temp.GOODS_ID,Temp.REQ_ID),0) AS AMT_ETM,A.REQDT_TYPE,
  A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE, 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 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_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)
 AND (B.GD_CODE LIKE '%'+ @P_GD_CODE +'%' OR @P_GD_CODE IS NULL OR @P_GD_CODE ='')
 AND (B.GD_NAME LIKE '%'+ @P_GD_NAME +'%' OR @P_GD_NAME IS NULL OR @P_GD_NAME ='')
 AND (A.NAME LIKE '%'+ @P_DES_GOOD +'%' OR @P_DES_GOOD IS NULL OR @P_DES_GOOD ='')