ALTER PROCEDURE [dbo].[PL_REQ_GOOD_GetById]
	@p_REQ_ID VARCHAR(15)
AS
BEGIN
DECLARE	
	 @l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
	 @l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,
	 @BRANCH_NAME NVARCHAR(200), @DEP_NAME NVARCHAR(200)
	
		SET @BRANCH_NAME = (SELECT BRANCH_NAME FROM dbo.CM_BRANCH 
							WHERE BRANCH_ID = (SELECT BRANCH_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
		SET @DEP_NAME = (SELECT DEP_NAME FROM dbo.CM_DEPARTMENT 
							WHERE DEP_ID = (SELECT DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
	
			SELECT DISTINCT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,ISNULL(CG.GD_CODE,LOG_DT.GOOD_CODE) AS GD_CODE,ISNULL(CG.GD_NAME,LOG_DT.GOOD_NAME) AS GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
			ISNULL(DT.QUANTITY,0) AS QUANTITY,
			ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
			ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
			ISNULL(DT.QUANTITY,0)-	ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
			ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM, 
			ISNULL( DT.AMT_APP,0)  + ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_APP,
			ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
			ISNULL( DT.AMT_ETM,0) AS AMT_ETM, 
			ISNULL( DT.AMT_TF,0) AS AMT_TF, 
			ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,	
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN, 
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
			CB.BRANCH_CODE,CB.BRANCH_NAME, DEP.DEP_NAME,'I' AS REQDT_TYPE,
			DT.NOTES,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM dbo.PL_MASTER PM
			LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
			LEFT JOIN dbo.PL_IMPORT_DT LOG_DT ON LOG_DT.TRADE_ID=DT.TRADE_ID
			LEFT JOIN dbo.CM_GOODSTYPE GT ON ISNULL(CG.GD_CODE,LOG_DT.GOOD_CODE) LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PM.DEPT_ID
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID  
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID  
			LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.KHOI_ID  
			LEFT JOIN dbo.PL_REQUEST_DOC_DT RD ON RD.GOODS_ID=DT.GOODS_ID AND RD.TRADE_ID=DT.TRADE_ID
			LEFT JOIN dbo.PL_REQUEST_DOC PD ON PD.REQ_ID=RD.REQ_ID
			WHERE (1=1)
			AND RD.REQ_ID=@p_REQ_ID
			 


			UNION ALL

			SELECT '' AS PLAN_ID,'' AS PLAN_CODE,'' AS TRADE_ID, CG.GD_ID AS GOODS_ID, CG.GD_CODE ,CG.GD_NAME ,'' AS BRANCH_ID,'' AS DEPT_ID,
			@l_QUANTITY AS QUANTITY,
			@l_QUANTITY_EXE AS QUANTITY_EXE,
			@l_QUANTITY_EXE AS QUANTITY_ETM,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
			@l_AMT AS AMT_APP,
			@l_AMT_EXE AS AMT_EXE,
			@l_AMT_EXE AS AMT_ETM,
			@l_AMT_EXE AS AMT_TF,
			@l_AMT_EXE AS AMT_RECEIVE_TF,
			@l_AMT_REMAIN AS AMT_REMAIN,
			@l_AMT_REMAIN AS AMT_REMAIN_ETM,
			'' AS BRANCH_CODE,
			--N'Ngoài kế hoạch' AS BRANCH_NAME, '' AS DEP_NAME,
			@BRANCH_NAME AS BRANCH_NAME, @DEP_NAME AS DEP_NAME,
			'O' AS REQDT_TYPE,
			'' AS NOTES,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM  dbo.CM_GOODS CG 
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			WHERE (1=1)
			AND EXISTS(SELECT RD.REQDT_ID FROM dbo.PL_REQUEST_DOC_DT RD WHERE RD.REQ_ID=@p_REQ_ID AND RD.GOODS_ID=CG.GD_ID AND RD.REQDT_TYPE='O')

END