|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[PL_REQ_DOC_UPDATE_AFTER_APPROVE]
|
|
3
|
@p_REQ_ID VARCHAR(15)
|
|
4
|
AS
|
|
5
|
|
|
6
|
|
|
7
|
BEGIN TRANSACTION
|
|
8
|
|
|
9
|
DECLARE
|
|
10
|
@PLAN_ID VARCHAR(15),
|
|
11
|
@TRADE_ID VARCHAR(15),
|
|
12
|
@GOOD_ID VARCHAR(15),
|
|
13
|
@QTY DECIMAL(18,0),
|
|
14
|
@TOTAL_AMT DECIMAL(18,2),
|
|
15
|
@REQ_DT DATETIME
|
|
16
|
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND IS_BACKDAY=1))
|
|
17
|
BEGIN
|
|
18
|
SET @REQ_DT = (SELECT REQ_DT FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
|
|
19
|
UPDATE dbo.PL_REQUEST_DOC SET EFFEC_DT=@REQ_DT,APPROVE_DT=@REQ_DT WHERE REQ_ID=@p_REQ_ID
|
|
20
|
UPDATE dbo.PL_REQUEST_PROCESS SET APPROVE_DT=@REQ_DT WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID <>'APPROVE'
|
|
21
|
END
|
|
22
|
ELSE
|
|
23
|
UPDATE dbo.PL_REQUEST_DOC SET EFFEC_DT=GETDATE() WHERE REQ_ID=@p_REQ_ID
|
|
24
|
|
|
25
|
|
|
26
|
|
|
27
|
DELETE FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@p_REQ_ID
|
|
28
|
INSERT dbo.PL_TRADEDETAIL_HIST
|
|
29
|
(
|
|
30
|
TRADE_ID,PLAN_ID,GOODS_ID,GOODS_CODE,GOODS_NAME,GOODS_TYPE,UNIT_ID,M1,M2,M3,M4,M5,M6,M7,M8,M9, M10, M11,M12,QUANTITY,QUANTITY_EXE,PRICE,START_DT_AMORT,MONTH_AMORT,END_DT_AMORT,RATE_AMORT, NOTES,RECORD_STATUS,MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,QUANTITY_ETM,
|
|
31
|
AMT_ETM,AMT_EXE,AMT_APP,AMT_TF,AMT_RECEIVE_TF, REQ_ID
|
|
32
|
)
|
|
33
|
SELECT TRADE_ID,PLAN_ID,GOODS_ID,GOODS_CODE,GOODS_NAME,GOODS_TYPE,UNIT_ID,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,QUANTITY,QUANTITY_EXE,PRICE,START_DT_AMORT,MONTH_AMORT,END_DT_AMORT,RATE_AMORT,NOTES,RECORD_STATUS,MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,QUANTITY_ETM,
|
|
34
|
AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,@p_REQ_ID 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))
|
|
35
|
|
|
36
|
|
|
37
|
|
|
38
|
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (TRADE_ID IS NULL OR TRADE_ID='')))
|
|
39
|
BEGIN
|
|
40
|
|
|
41
|
DECLARE @BRANCH_ID VARCHAR(20),@DEP_ID VARCHAR(20),@l_PLAN_ID VARCHAR(20),@l_PLAN_CODE VARCHAR(20),@DVDM_ID VARCHAR(20),@GD_ID VARCHAR(20),
|
|
42
|
@l_PLAN_NAME NVARCHAR(200),@CREATE_DT DATETIME,@DEP_CODE VARCHAR(20),@MARKER_ID VARCHAR(20),@l_TRADE_ID VARCHAR(20)
|
|
43
|
|
|
44
|
SELECT @BRANCH_ID=BRANCH_ID,@DEP_ID=DEP_ID,@CREATE_DT=CREATE_DT,@MARKER_ID=MAKER_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
|
|
45
|
SELECT @DEP_CODE=DEP_CODE FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@DEP_ID
|
|
46
|
|
|
47
|
|
|
48
|
DECLARE lstPLAN CURSOR FOR
|
|
49
|
SELECT DVDM_ID FROM dbo.CM_GOOD_DVDM WHERE GD_ID IN (SELECT GOODS_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND (TRADE_ID IS NULL OR TRADE_ID=''))
|
|
50
|
OPEN lstPLAN
|
|
51
|
|
|
52
|
FETCH NEXT FROM lstPLAN INTO @DVDM_ID
|
|
53
|
WHILE @@FETCH_STATUS=0
|
|
54
|
BEGIN
|
|
55
|
|
|
56
|
SET @l_PLAN_NAME=N'KẾ HOẠCH NGÂN SÁCH '+ CAST( YEAR(@CREATE_DT) AS VARCHAR(10))
|
|
57
|
SET @l_PLAN_CODE= @DEP_CODE+(SELECT RIGHT(CAST( YEAR(@CREATE_DT) AS VARCHAR(10)),2))
|
|
58
|
|
|
59
|
|
|
60
|
EXEC SYS_CodeMasters_Gen 'PL_MASTER', @l_PLAN_ID out
|
|
61
|
|
|
62
|
INSERT INTO dbo.PL_MASTER
|
|
63
|
(
|
|
64
|
PLAN_ID, PLAN_CODE,PLAN_NAME,BRANCH_ID,DEPT_ID,EFFECT_DT,PLAN_TYPE,TOTAL_AMT,APPROVE_VALUE,
|
|
65
|
VERSON,YEAR,STATUS,NOTES,RECORD_STATUS, MAKER_ID,CREATE_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,PLAN_TYPE_ID,COST_ID)
|
|
66
|
VALUES
|
|
67
|
( @l_PLAN_ID,@l_PLAN_CODE,@l_PLAN_NAME,@BRANCH_ID,@DEP_ID,GETDATE(),'',0, 0,
|
|
68
|
1,CAST( YEAR(@CREATE_DT) AS VARCHAR(10)),'1', N'','1', @MARKER_ID, GETDATE(),'A', @MARKER_ID, GETDATE(), '',@DVDM_ID
|
|
69
|
)
|
|
70
|
|
|
71
|
DECLARE lstTRADE CURSOR FOR
|
|
72
|
SELECT DT.GOODS_ID FROM dbo.PL_REQUEST_DOC_DT DT
|
|
73
|
LEFT JOIN dbo.CM_GOOD_DVDM CGD ON CGD.GD_ID=DT.GOODS_ID
|
|
74
|
WHERE REQ_ID=@p_REQ_ID AND (TRADE_ID IS NULL OR TRADE_ID='') AND CGD.DVDM_ID=@DVDM_ID
|
|
75
|
GROUP BY DT.GOODS_ID
|
|
76
|
|
|
77
|
OPEN lstTRADE
|
|
78
|
FETCH NEXT FROM lstTRADE INTO @GD_ID
|
|
79
|
WHILE @@FETCH_STATUS=0
|
|
80
|
BEGIN
|
|
81
|
|
|
82
|
EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @l_TRADE_ID OUT
|
|
83
|
INSERT INTO dbo.PL_TRADEDETAIL
|
|
84
|
(
|
|
85
|
TRADE_ID,
|
|
86
|
PLAN_ID,
|
|
87
|
GOODS_ID,
|
|
88
|
QUANTITY,
|
|
89
|
AMT_APP,
|
|
90
|
NOTES,
|
|
91
|
RECORD_STATUS,
|
|
92
|
MAKER_ID,
|
|
93
|
CREATE_DT,
|
|
94
|
AUTH_STATUS,
|
|
95
|
CHECKER_ID,
|
|
96
|
APPROVE_DT
|
|
97
|
)
|
|
98
|
VALUES
|
|
99
|
( @l_TRADE_ID, -- TRADE_ID - varchar(15)
|
|
100
|
@l_PLAN_ID, -- PLAN_ID - varchar(15)
|
|
101
|
@GD_ID, -- GOODS_ID - varchar(15)
|
|
102
|
0, -- QUANTITY - decimal(18, 0)
|
|
103
|
0, -- PRICE - decimal(18, 0)
|
|
104
|
'', -- NOTES - nvarchar(1000)
|
|
105
|
'1', -- RECORD_STATUS - varchar(1)
|
|
106
|
@MARKER_ID, -- MAKER_ID - varchar(12)
|
|
107
|
GETDATE(), -- CREATE_DT - datetime
|
|
108
|
'A', -- AUTH_STATUS - varchar(50)
|
|
109
|
@MARKER_ID, -- CHECKER_ID - varchar(12)
|
|
110
|
GETDATE() -- APPROVE_DT - datetime
|
|
111
|
)
|
|
112
|
|
|
113
|
UPDATE dbo.PL_REQUEST_DOC_DT SET TRADE_ID=@l_TRADE_ID,PLAN_ID=@l_PLAN_ID WHERE REQ_ID=@p_REQ_ID AND GOODS_ID=@GD_ID AND (TRADE_ID IS NULL OR TRADE_ID='')
|
|
114
|
UPDATE dbo.PL_REQUEST_TRANSFER SET TO_TRADE_ID=@l_TRADE_ID,TO_PLAN_ID=@l_PLAN_ID WHERE REQ_DOC_ID=@p_REQ_ID AND TO_GOOD_ID=@GD_ID AND (TO_TRADE_ID IS NULL OR TO_TRADE_ID='')
|
|
115
|
|
|
116
|
FETCH NEXT FROM lstTRADE INTO @GD_ID
|
|
117
|
END
|
|
118
|
CLOSE lstTRADE
|
|
119
|
DEALLOCATE lstTRADE
|
|
120
|
|
|
121
|
|
|
122
|
FETCH NEXT FROM lstPLAN INTO @DVDM_ID
|
|
123
|
END
|
|
124
|
CLOSE lstPLAN
|
|
125
|
DEALLOCATE lstPLAN
|
|
126
|
|
|
127
|
|
|
128
|
END
|
|
129
|
|
|
130
|
IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))
|
|
131
|
BEGIN
|
|
132
|
DECLARE @FR_PLAN_ID VARCHAR(20),@FR_TRADE_ID VARCHAR(20),@FR_GOOD_ID VARCHAR(20)
|
|
133
|
,@TO_PLAN_ID VARCHAR(20),@TO_TRADE_ID VARCHAR(20),@TO_GOOD_ID VARCHAR(20)
|
|
134
|
DECLARE TransferDT CURSOR FOR
|
|
135
|
SELECT FR_PLAN_ID,FR_TRADE_ID,FR_GOOD_ID,TO_TRADE_ID,TO_PLAN_ID,TO_GOOD_ID,TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID
|
|
136
|
OPEN TransferDT
|
|
137
|
FETCH NEXT FROM TransferDT INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@TO_TRADE_ID,@TO_PLAN_ID,@TO_GOOD_ID,@TOTAL_AMT
|
|
138
|
WHILE @@FETCH_STATUS = 0
|
|
139
|
BEGIN
|
|
140
|
UPDATE dbo.PL_TRADEDETAIL SET AMT_TF=ISNULL(AMT_TF,0)+@TOTAL_AMT WHERE PLAN_ID=@FR_PLAN_ID AND TRADE_ID=@FR_TRADE_ID AND GOODS_ID=@FR_GOOD_ID
|
|
141
|
UPDATE dbo.PL_TRADEDETAIL SET AMT_RECEIVE_TF=ISNULL(AMT_RECEIVE_TF,0)+@TOTAL_AMT WHERE PLAN_ID=@TO_PLAN_ID AND TRADE_ID=@TO_TRADE_ID AND GOODS_ID=@TO_GOOD_ID
|
|
142
|
FETCH NEXT FROM TransferDT INTO @FR_PLAN_ID,@FR_TRADE_ID,@FR_GOOD_ID,@TO_TRADE_ID,@TO_PLAN_ID,@TO_GOOD_ID,@TOTAL_AMT
|
|
143
|
END
|
|
144
|
CLOSE TransferDT
|
|
145
|
DEALLOCATE TransferDT
|
|
146
|
|
|
147
|
END
|
|
148
|
|
|
149
|
|
|
150
|
DECLARE RequestDT CURSOR FOR
|
|
151
|
SELECT PLAN_ID,TRADE_ID,GOODS_ID,QUANTITY,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID
|
|
152
|
OPEN RequestDT
|
|
153
|
FETCH NEXT FROM RequestDT INTO @PLAN_ID,@TRADE_ID,@GOOD_ID,@QTY,@TOTAL_AMT
|
|
154
|
WHILE @@FETCH_STATUS = 0
|
|
155
|
BEGIN
|
|
156
|
UPDATE dbo.PL_TRADEDETAIL SET QUANTITY_ETM=ISNULL(QUANTITY_ETM,0)+@QTY,AMT_ETM=ISNULL(AMT_ETM,0)+@TOTAL_AMT WHERE PLAN_ID=@PLAN_ID AND TRADE_ID=@TRADE_ID AND GOODS_ID=@GOOD_ID
|
|
157
|
FETCH NEXT FROM RequestDT INTO @PLAN_ID,@TRADE_ID,@GOOD_ID,@QTY,@TOTAL_AMT
|
|
158
|
END
|
|
159
|
CLOSE RequestDT
|
|
160
|
DEALLOCATE RequestDT
|
|
161
|
|
|
162
|
|
|
163
|
|
|
164
|
|
|
165
|
COMMIT TRANSACTION
|
|
166
|
RETURN 1
|
|
167
|
ABORT:
|
|
168
|
BEGIN
|
|
169
|
ROLLBACK TRANSACTION
|
|
170
|
RETURN 1
|
|
171
|
End
|
|
172
|
ABORT1:
|
|
173
|
BEGIN
|
|
174
|
CLOSE RequestDT
|
|
175
|
DEALLOCATE RequestDT
|
|
176
|
CLOSE TransferDT
|
|
177
|
DEALLOCATE TransferDT
|
|
178
|
ROLLBACK TRANSACTION
|
|
179
|
RETURN 1
|
|
180
|
End
|
|
181
|
|
|
182
|
|
|
183
|
|
|
184
|
|
|
185
|
|
|
186
|
|
|
187
|
|
|
188
|
|