ALTER PROCEDURE dbo.rpt_PL_REQ_DOC_Inventory
@p_GD_CODE	varchar(50)	= NULL,
@p_GD_NAME	nvarchar(500)	= NULL,
@p_DEP_ID VARCHAR(20) = NULL,
@p_BRANCH_ID	varchar(20)	= NULL,
@p_USER_LOGIN VARCHAR(15)= NULL
AS
BEGIN 
	SELECT Row_number() over(order by PL.REQ_ID) AS STT,PL.REQ_REASON,PL.EFFEC_DT,PL.REQ_ID,PL.REQ_CODE,CB.BRANCH_CODE,CB.BRANCH_NAME,CD.DEP_CODE,CD.DEP_NAME,Temp2.GD_CODE,Temp2.GD_NAME,TU.TLFullName AS MAKER_NAME,Temp2.AMT_ETM,Temp2.AMT_TF,Temp2.AMT_RECIVE_TF AS AMT_RECEIVE_TF,
  prp.NOTES AS TRANGTHAI_TOTRINH
  FROM
	(
		SELECT Temp.REQ_ID,Temp.GD_CODE,Temp.GD_NAME,SUM(Temp.AMT_ETM) AS AMT_ETM,SUM(Temp.AMT_TF) AS AMT_TF,SUM(Temp.AMT_RECIVE_TF) AS AMT_RECIVE_TF FROM (
		SELECT DT.REQ_ID,CG.GD_CODE,CG.GD_NAME,SUM(DT.TOTAL_AMT) AS AMT_ETM, 0 AMT_TF,0 AMT_RECIVE_TF FROM dbo.PL_REQUEST_DOC_DT DT 
		LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
		WHERE 1=1 
		AND (CG.GD_CODE LIKE '%' +@p_GD_CODE+'%' OR @p_GD_CODE ='' OR @p_GD_CODE IS NULL)
		AND (CG.GD_NAME LIKE '%' +@p_GD_NAME+'%' OR @p_GD_NAME ='' OR @p_GD_NAME IS NULL)
		GROUP BY DT.REQ_ID,CG.GD_CODE,CG.GD_NAME
		UNION ALL
		SELECT DT.REQ_DOC_ID AS REQ_ID,CG.GD_CODE,CG.GD_NAME,0 AS AMT_ETM, SUM(DT.TOTAL_AMT) AMT_TF,0 AMT_RECIVE_TF FROM dbo.PL_REQUEST_TRANSFER DT 
		LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.FR_GOOD_ID
		WHERE 1=1 
		AND (CG.GD_CODE LIKE '%' +@p_GD_CODE+'%' OR @p_GD_CODE ='' OR @p_GD_CODE IS NULL)
		AND (CG.GD_NAME LIKE '%' +@p_GD_NAME+'%' OR @p_GD_NAME ='' OR @p_GD_NAME IS NULL)
		GROUP BY DT.REQ_DOC_ID,CG.GD_CODE,CG.GD_NAME
		UNION ALL
		SELECT DT.REQ_DOC_ID AS REQ_ID,CG.GD_CODE,CG.GD_NAME,0 AS AMT_ETM, 0 AMT_TF,SUM(DT.TOTAL_AMT)AMT_RECIVE_TF FROM dbo.PL_REQUEST_TRANSFER DT 
		LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.TO_GOOD_ID
		WHERE 1=1 
		AND (CG.GD_CODE LIKE '%' +@p_GD_CODE+'%' OR @p_GD_CODE ='' OR @p_GD_CODE IS NULL)
		AND (CG.GD_NAME LIKE '%' +@p_GD_NAME+'%' OR @p_GD_NAME ='' OR @p_GD_NAME IS NULL)
		GROUP BY DT.REQ_DOC_ID,CG.GD_CODE,CG.GD_NAME
		) Temp
		GROUP BY Temp.REQ_ID,Temp.GD_CODE,Temp.GD_NAME
	) Temp2
	LEFT JOIN  dbo.PL_REQUEST_DOC PL ON Pl.REQ_ID=Temp2.REQ_ID
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PL.BRANCH_CREATE
	LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PL.DEP_CREATE
	LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=PL.MAKER_ID
  LEFT JOIN PL_REQUEST_PROCESS prp ON PL.REQ_ID = prp.REQ_ID AND PL.PROCESS_ID = prp.PROCESS_ID AND prp.STATUS = 'C'
  LEFT JOIN CM_ALLCODE CA ON ca.CDTYPE = 'PL' AND ca.CDNAME = 'PROCESS_STATUS' AND ca.CDVAL = PL.PROCESS_ID
	--ORDER BY A.CREATE_DT DESC
END