DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

USE [gAMSPro_VietcapitalBank_v2_TEST]

GO

/****** Object:  StoredProcedure [dbo].[PL_REQ_GOOD_GetById]    Script Date: 13-Sep-20 23:04:32 ******/

SET ANSI_NULLS ON

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

			SELECT DISTINCT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.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,'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.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'

			LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_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,

			'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

--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s