|
1
|
|
|
2
|
|
|
3
|
ALTER PROCEDURE [dbo].[PL_REQ_GOOD_GetById]
|
|
4
|
@p_REQ_ID VARCHAR(15)
|
|
5
|
AS
|
|
6
|
BEGIN
|
|
7
|
DECLARE
|
|
8
|
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
|
|
9
|
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,
|
|
10
|
@BRANCH_NAME NVARCHAR(200), @DEP_NAME NVARCHAR(200)
|
|
11
|
|
|
12
|
SET @BRANCH_NAME = (SELECT BRANCH_NAME FROM dbo.CM_BRANCH
|
|
13
|
WHERE BRANCH_ID = (SELECT BRANCH_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
|
|
14
|
SET @DEP_NAME = (SELECT DEP_NAME FROM dbo.CM_DEPARTMENT
|
|
15
|
WHERE DEP_ID = (SELECT DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
|
|
16
|
|
|
17
|
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,
|
|
18
|
ISNULL(DT.QUANTITY,0) AS QUANTITY,
|
|
19
|
ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
|
|
20
|
ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
|
|
21
|
ISNULL(DT.QUANTITY,0)- ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
|
|
22
|
ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM,
|
|
23
|
ISNULL( DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_APP,
|
|
24
|
ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
|
|
25
|
ISNULL( DT.AMT_ETM,0) AS AMT_ETM,
|
|
26
|
ISNULL( DT.AMT_TF,0) AS AMT_TF,
|
|
27
|
ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
|
|
28
|
ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN,
|
|
29
|
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,
|
|
30
|
CB.BRANCH_CODE,CB.BRANCH_NAME, DEP.DEP_NAME,'I' AS REQDT_TYPE,
|
|
31
|
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,
|
|
32
|
DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
|
|
33
|
FROM dbo.PL_MASTER PM
|
|
34
|
LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
|
|
35
|
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
|
|
36
|
LEFT JOIN dbo.PL_IMPORT_DT LOG_DT ON LOG_DT.TRADE_ID=DT.TRADE_ID
|
|
37
|
LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
|
|
38
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
|
|
39
|
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PM.DEPT_ID
|
|
40
|
LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
|
|
41
|
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=PM.COST_ID
|
|
42
|
LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=DM.DVDM_ID
|
|
43
|
LEFT JOIN dbo.CM_DVDM_KHOI DMKH ON DMKH.DVDM_ID = DM.DVDM_ID
|
|
44
|
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=DMKH.KHOI_ID
|
|
45
|
LEFT JOIN dbo.PL_REQUEST_DOC_DT RD ON RD.GOODS_ID=DT.GOODS_ID AND RD.TRADE_ID=DT.TRADE_ID
|
|
46
|
LEFT JOIN dbo.PL_REQUEST_DOC PD ON PD.REQ_ID=RD.REQ_ID
|
|
47
|
WHERE (1=1)
|
|
48
|
AND RD.REQ_ID=@p_REQ_ID
|
|
49
|
|
|
50
|
|
|
51
|
|
|
52
|
UNION ALL
|
|
53
|
|
|
54
|
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,
|
|
55
|
@l_QUANTITY AS QUANTITY,
|
|
56
|
@l_QUANTITY_EXE AS QUANTITY_EXE,
|
|
57
|
@l_QUANTITY_EXE AS QUANTITY_ETM,
|
|
58
|
@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
|
|
59
|
@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
|
|
60
|
@l_AMT AS AMT_APP,
|
|
61
|
@l_AMT_EXE AS AMT_EXE,
|
|
62
|
@l_AMT_EXE AS AMT_ETM,
|
|
63
|
@l_AMT_EXE AS AMT_TF,
|
|
64
|
@l_AMT_EXE AS AMT_RECEIVE_TF,
|
|
65
|
@l_AMT_REMAIN AS AMT_REMAIN,
|
|
66
|
@l_AMT_REMAIN AS AMT_REMAIN_ETM,
|
|
67
|
'' AS BRANCH_CODE,
|
|
68
|
--N'Ngoài kế hoạch' AS BRANCH_NAME, '' AS DEP_NAME,
|
|
69
|
@BRANCH_NAME AS BRANCH_NAME, @DEP_NAME AS DEP_NAME,
|
|
70
|
'O' AS REQDT_TYPE,
|
|
71
|
'' 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,
|
|
72
|
DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
|
|
73
|
FROM dbo.CM_GOODS CG
|
|
74
|
LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
|
|
75
|
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
|
|
76
|
LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
|
|
77
|
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
|
|
78
|
LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
|
|
79
|
WHERE (1=1)
|
|
80
|
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')
|
|
81
|
|
|
82
|
END
|
|
83
|
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s
|
|
84
|
|
|
85
|
|
|
86
|
|
|
87
|
|
|
88
|
|