|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[PL_REQ_DOC_Ins_To_TR_REQ_DOC]
|
|
3
|
@p_PL_REQ_ID VARCHAR(15)
|
|
4
|
AS
|
|
5
|
BEGIN TRANSACTION
|
|
6
|
DECLARE
|
|
7
|
@l_REQ_ID VARCHAR(15),
|
|
8
|
@p_REQ_CODE VARCHAR(20),
|
|
9
|
@PL_REQDT_ID varchar(15),
|
|
10
|
@GOODS_ID varchar(15),
|
|
11
|
@DESCRIPTION nvarchar(500),
|
|
12
|
@QUANTITY DECIMAL(18,0),
|
|
13
|
@PRICE DECIMAL(18,0),
|
|
14
|
@TOTAL_AMT DECIMAL(18,0),
|
|
15
|
@DVDM_ID VARCHAR(20),
|
|
16
|
@CURRENCY VARCHAR(10),
|
|
17
|
@TAXES DECIMAL(18,2),
|
|
18
|
@EXCHANGE_RATE DECIMAL(18,2),
|
|
19
|
@COST_ID VARCHAR(20),
|
|
20
|
@MAKER_ID VARCHAR(20),
|
|
21
|
@HH_ID VARCHAR(20),
|
|
22
|
@SUP_ID VARCHAR(20),
|
|
23
|
@l_REQDT_ID VARCHAR(15),
|
|
24
|
@l_REQ_COST_ID VARCHAR(15),
|
|
25
|
@NOTES_COST NVARCHAR(500),
|
|
26
|
@TRADE_TYPE VARCHAR(15),
|
|
27
|
@REASON_CDT NVARCHAR(MAX),
|
|
28
|
@REQPL_DT_ID VARCHAR(20),
|
|
29
|
@UNIT_NAME NVARCHAR(100)
|
|
30
|
--- KIEM TRA TO TRINH MUA SAM MOI PHAT SINH PYCMS LUCTV 8.11.2022
|
|
31
|
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID=@p_PL_REQ_ID))
|
|
32
|
BEGIN
|
|
33
|
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_PL_REQ_ID AND TRADE_TYPE='VCCB'))
|
|
34
|
BEGIN
|
|
35
|
--insert master
|
|
36
|
|
|
37
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC', @l_REQ_ID out
|
|
38
|
IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT
|
|
39
|
|
|
40
|
|
|
41
|
exec [TR_CODE_GenKey] 'TR_REQUEST_DOC', '','PUR', @p_REQ_CODE out
|
|
42
|
IF @p_REQ_CODE='' OR @p_REQ_CODE IS NULL GOTO ABORT
|
|
43
|
|
|
44
|
|
|
45
|
INSERT INTO dbo.TR_REQUEST_DOC
|
|
46
|
(
|
|
47
|
REQ_ID,
|
|
48
|
REQ_CODE,
|
|
49
|
REQ_DT,
|
|
50
|
REQ_REASON,
|
|
51
|
PL_REQ_ID,
|
|
52
|
NOTES,
|
|
53
|
RECORD_STATUS,
|
|
54
|
CREATE_DT,
|
|
55
|
AUTH_STATUS,
|
|
56
|
TOTAL_AMT,
|
|
57
|
BRANCH_CREATE,
|
|
58
|
DEP_CREATE,
|
|
59
|
USER_REQUEST,MAKER_ID,PROCESS_ID
|
|
60
|
)
|
|
61
|
SELECT @l_REQ_ID,@p_REQ_CODE,GETDATE(),REQ_REASON, REQ_ID,'','1',GETDATE(),'E',TOTAL_AMT,BRANCH_CREATE,DEP_CREATE,MAKER_ID,MAKER_ID,'NEW' FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID
|
|
62
|
|
|
63
|
IF @@Error <> 0 GOTO ABORT
|
|
64
|
--Insert into TABLE PL_REQUEST_DOC_DT
|
|
65
|
|
|
66
|
------------BAODNQ 28/10/2022 : LẤY THÊM UNIT_NAME TRONG PL_REQUEST_DOC_DT------
|
|
67
|
DECLARE ListGoods CURSOR FOR
|
|
68
|
SELECT REQDT_ID,GOODS_ID,[DESCRIPTION],QUANTITY,PRICE,TOTAL_AMT,HANGHOA_ID,DVDM_ID,CURRENCY,TAXES,EXCHANGE_RATE,SUP_ID,TRADE_TYPE,NAME,UNIT_NAME FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_PL_REQ_ID AND TRADE_TYPE='VCCB'
|
|
69
|
OPEN ListGoods
|
|
70
|
SET @MAKER_ID=(SELECT MAKER_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID)
|
|
71
|
FETCH NEXT FROM ListGoods INTO @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,@HH_ID,@DVDM_ID,@CURRENCY,@TAXES,@EXCHANGE_RATE,@SUP_ID,@TRADE_TYPE,@REASON_CDT,@UNIT_NAME
|
|
72
|
WHILE @@FETCH_STATUS = 0
|
|
73
|
BEGIN
|
|
74
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_DT', @l_REQDT_ID out
|
|
75
|
IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT
|
|
76
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_PL_DT', @REQPL_DT_ID out
|
|
77
|
IF @REQPL_DT_ID='' OR @REQPL_DT_ID IS NULL GOTO ABORT
|
|
78
|
|
|
79
|
INSERT INTO dbo.TR_REQUEST_DOC_PL_DT
|
|
80
|
(
|
|
81
|
REQPL_DT_ID,
|
|
82
|
REQ_DOC_ID,
|
|
83
|
PL_REQDT_ID,
|
|
84
|
GD_ID,
|
|
85
|
SUP_ID,
|
|
86
|
HH_ID,
|
|
87
|
TRAN_TYPE_ID,
|
|
88
|
MAKER_ID,
|
|
89
|
CREATE_DT,
|
|
90
|
AUTH_STATUS,
|
|
91
|
CHECKER_ID,
|
|
92
|
APPROVE_DT,
|
|
93
|
DES_GOOD,
|
|
94
|
QUANTITY_PL,
|
|
95
|
PRICE_PL,
|
|
96
|
CURRENCY,
|
|
97
|
EXCHANGE_RATE,
|
|
98
|
TAXES,
|
|
99
|
TOTAL_AMT_ETM,
|
|
100
|
REASON_CDT,
|
|
101
|
REQ_DT,
|
|
102
|
NOTES,
|
|
103
|
RECORD_STATUS
|
|
104
|
)
|
|
105
|
VALUES
|
|
106
|
( @REQPL_DT_ID, -- REQPL_DT_ID - varchar(15)
|
|
107
|
@l_REQ_ID, -- REQ_DOC_ID - varchar(15)
|
|
108
|
@PL_REQDT_ID, -- PL_REQDT_ID - varchar(15)
|
|
109
|
@GOODS_ID, -- GD_ID - varchar(15)
|
|
110
|
@SUP_ID, -- SUP_ID - varchar(15)
|
|
111
|
@HH_ID, -- HH_ID - varchar(15)
|
|
112
|
@TRADE_TYPE, -- TRAN_TYPE_ID - varchar(15)
|
|
113
|
@MAKER_ID, -- MAKER_ID - varchar(15)
|
|
114
|
GETDATE(), -- CREATE_DT - datetime
|
|
115
|
'', -- AUTH_STATUS - varchar(50)
|
|
116
|
'', -- CHECKER_ID - varchar(15)
|
|
117
|
NULL, -- APPROVE_DT - datetime
|
|
118
|
@DESCRIPTION, -- DES_GOOD - nvarchar(500)
|
|
119
|
@QUANTITY, -- QUANTITY_PL - decimal(18, 0)
|
|
120
|
@PRICE, -- PRICE_PL - decimal(18, 2)
|
|
121
|
@CURRENCY, -- CURRENCY - nvarchar(50)
|
|
122
|
@EXCHANGE_RATE, -- EXCHANGE_RATE - decimal(18, 2)
|
|
123
|
@TAXES, -- TAXES - decimal(18, 2)
|
|
124
|
@TOTAL_AMT, -- TOTAL_AMT_ETM - decimal(18, 2)
|
|
125
|
@REASON_CDT, -- REASON_CDT - nvarchar(500)
|
|
126
|
GETDATE(), -- REQ_DT - datetime
|
|
127
|
N'', -- NOTES - nvarchar(500)
|
|
128
|
'1' -- RECORD_STATUS - varchar(1)
|
|
129
|
)
|
|
130
|
|
|
131
|
print 'haha'
|
|
132
|
|
|
133
|
INSERT INTO dbo.TR_REQUEST_DOC_DT
|
|
134
|
(
|
|
135
|
REQDT_ID,
|
|
136
|
REQ_DOC_ID,
|
|
137
|
PL_REQDT_ID,
|
|
138
|
GD_ID,
|
|
139
|
DESCRIPTION,
|
|
140
|
RECORD_STATUS,
|
|
141
|
MAKER_ID,
|
|
142
|
CREATE_DT,
|
|
143
|
AUTH_STATUS,
|
|
144
|
CHECKER_ID,
|
|
145
|
APPROVE_DT,
|
|
146
|
QUANTITY,
|
|
147
|
PRICE,
|
|
148
|
TOTAL_AMT,
|
|
149
|
PRICE_ETM,
|
|
150
|
TOTAL_AMT_ETM,CURRENCY,HANGHOA_ID,DVDM_ID,TAXES,EXCHANGE_RATE,SUP_ID,REQPL_DT_ID,
|
|
151
|
---------BAODNQ 28/10/2022: KHI TỰ SINH PYCMS INSERT THÊM ĐƠN VỊ TÍNH THEO TỜ TRÌNH-----
|
|
152
|
UNIT_NAME,TRAN_TYPE_ID -- LUCTV 17-03-2023 KHI PHAT SINH PYCMS CÓ HÌNH THỨC MUA SẮM TỪ TỜ TRÌNH LÀ THEO QUY ĐỊNH THÌ TẠI PUR ĐƯỢC GEN CŨNG SINH RA HÌNH THỨC MUA SẮM TƯƠNG ỨNG
|
|
153
|
|
|
154
|
)
|
|
155
|
VALUES
|
|
156
|
( @l_REQDT_ID, -- REQDT_ID - varchar(15)
|
|
157
|
@l_REQ_ID, -- REQ_ID - varchar(15)
|
|
158
|
@PL_REQDT_ID, -- PLAN_ID - varchar(15)
|
|
159
|
@GOODS_ID, -- GOODS_ID - varchar(15
|
|
160
|
-- NAME - nvarchar(200)
|
|
161
|
@DESCRIPTION, -- DESCRIPTION - nvarchar(500)
|
|
162
|
|
|
163
|
'1', -- RECORD_STATUS - varchar(1)
|
|
164
|
'', -- MAKER_ID - varchar(15)
|
|
165
|
GETDATE(), -- CREATE_DT - datetime
|
|
166
|
'U', -- AUTH_STATUS - varchar(50)
|
|
167
|
'', -- CHECKER_ID - varchar(15)
|
|
168
|
NULL, -- APPROVE_DT - datetime
|
|
169
|
@QUANTITY,
|
|
170
|
@PRICE,
|
|
171
|
@TOTAL_AMT,
|
|
172
|
@PRICE,
|
|
173
|
@TOTAL_AMT,
|
|
174
|
@CURRENCY,@HH_ID,@DVDM_ID,@TAXES,@EXCHANGE_RATE,@SUP_ID,@REQPL_DT_ID,
|
|
175
|
---------BAODNQ 28/10/2022: KHI TỰ SINH PYCMS INSERT THÊM ĐƠN VỊ TÍNH THEO TỜ TRÌNH-----
|
|
176
|
@UNIT_NAME,'TRN0000000009' -- LUCTV 17-03-2023 KHI PHAT SINH PYCMS CÓ HÌNH THỨC MUA SẮM TỪ TỜ TRÌNH LÀ THEO QUY ĐỊNH THÌ TẠI PUR ĐƯỢC GEN CŨNG SINH RA HÌNH THỨC MUA SẮM TƯƠNG ỨNG
|
|
177
|
)
|
|
178
|
IF @@ERROR <> 0 GOTO ABORT1
|
|
179
|
-- next Group_Id
|
|
180
|
FETCH NEXT FROM ListGoods INTO @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,@HH_ID,@DVDM_ID,@CURRENCY,@TAXES,@EXCHANGE_RATE,@SUP_ID,@TRADE_TYPE,@REASON_CDT,@UNIT_NAME
|
|
181
|
END
|
|
182
|
|
|
183
|
UPDATE dbo.TR_REQUEST_DOC SET TOTAL_AMT = (
|
|
184
|
SELECT SUM(QUANTITY*PRICE) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID = @l_REQ_ID
|
|
185
|
) WHERE REQ_ID = @l_REQ_ID
|
|
186
|
|
|
187
|
CLOSE ListGoods
|
|
188
|
DEALLOCATE ListGoods
|
|
189
|
IF @@Error <> 0 GOTO ABORT
|
|
190
|
--LUCTV: 10 06 2021 KHI PHÁT SINH PYCMS TỪ TỜ TRÌNH CHỦ TRƯƠNG, KHÔNG CẦN PHÁT SINH ĐƠN VỊ CHUYÊN MÔN
|
|
191
|
--DECLARE lstCostCenter CURSOR FOR
|
|
192
|
--SELECT COST_ID, NOTES FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_PL_REQ_ID
|
|
193
|
--GROUP BY COST_ID,NOTES
|
|
194
|
--OPEN lstCostCenter
|
|
195
|
--FETCH NEXT FROM lstCostCenter INTO @COST_ID, @NOTES_COST
|
|
196
|
--WHILE @@FETCH_STATUS=0
|
|
197
|
--BEGIN
|
|
198
|
-- EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out
|
|
199
|
-- IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT
|
|
200
|
-- INSERT INTO dbo.TR_REQUEST_COSTCENTER
|
|
201
|
-- (
|
|
202
|
-- REQ_COST_ID,
|
|
203
|
-- COST_ID,
|
|
204
|
-- REQ_ID,
|
|
205
|
-- NOTES,
|
|
206
|
-- AUTH_STATUS,
|
|
207
|
-- MAKER_ID,
|
|
208
|
-- CREATE_DT,
|
|
209
|
-- CHECKER_ID,
|
|
210
|
-- APPROVE_DT
|
|
211
|
-- )
|
|
212
|
-- VALUES
|
|
213
|
-- ( @l_REQ_COST_ID, -- REQ_COST_ID - varchar(15)
|
|
214
|
-- @COST_ID, -- COST_ID - varchar(15)
|
|
215
|
-- @l_REQ_ID, -- REQ_ID - varchar(15)
|
|
216
|
-- @NOTES_COST, -- NOTES - nvarchar(500)
|
|
217
|
-- '', -- AUTH_STATUS - varchar(1)
|
|
218
|
-- @MAKER_ID, -- MAKER_ID - varchar(15)
|
|
219
|
-- NULL, -- CREATE_DT - datetime
|
|
220
|
-- '', -- CHECKER_ID - varchar(15)
|
|
221
|
-- NULL -- APPROVE_DT - datetime
|
|
222
|
-- )
|
|
223
|
-- FETCH NEXT FROM lstCostCenter INTO @COST_ID,@NOTES_COST
|
|
224
|
--END
|
|
225
|
--CLOSE lstCostCenter
|
|
226
|
--DEALLOCATE lstCostCenter
|
|
227
|
INSERT INTO dbo.PL_REQUEST_PROCESS
|
|
228
|
(
|
|
229
|
REQ_ID,
|
|
230
|
PROCESS_ID,
|
|
231
|
STATUS,
|
|
232
|
ROLE_USER,
|
|
233
|
BRANCH_ID,
|
|
234
|
CHECKER_ID,
|
|
235
|
APPROVE_DT,
|
|
236
|
PARENT_PROCESS_ID,
|
|
237
|
IS_LEAF,
|
|
238
|
COST_ID,
|
|
239
|
DVDM_ID,
|
|
240
|
NOTES,
|
|
241
|
IS_HAS_CHILD
|
|
242
|
)
|
|
243
|
VALUES
|
|
244
|
( @l_REQ_ID, -- REQ_ID - varchar(15)
|
|
245
|
'NEW', -- PROCESS_ID - varchar(10)
|
|
246
|
'C', -- STATUS - varchar(5)
|
|
247
|
'', -- ROLE_USER - varchar(50)
|
|
248
|
'', -- BRANCH_ID - varchar(15)
|
|
249
|
'', -- CHECKER_ID - varchar(15)
|
|
250
|
NULL, -- APPROVE_DT - datetime
|
|
251
|
'', -- PARENT_PROCESS_ID - varchar(10)
|
|
252
|
'N', -- IS_LEAF - varchar(1)
|
|
253
|
'', -- COST_ID - varchar(15)
|
|
254
|
'', -- DVDM_ID - varchar(15)
|
|
255
|
N'Chờ gửi phê duyệt', -- NOTES - nvarchar(500)
|
|
256
|
NULL -- IS_HAS_CHILD - bit
|
|
257
|
)
|
|
258
|
IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@l_REQ_ID AND TOTAL_AMT <=10000000 AND NOT EXISTS (SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID=BRANCH_CREATE AND BRANCH_TYPE='HS')))
|
|
259
|
BEGIN
|
|
260
|
|
|
261
|
UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES =N'Tự động phát sinh & duyệt hoàn tất' WHERE REQ_ID=@l_REQ_ID AND PROCESS_ID='NEW'
|
|
262
|
INSERT INTO dbo.PL_REQUEST_PROCESS
|
|
263
|
(
|
|
264
|
REQ_ID,
|
|
265
|
PROCESS_ID,
|
|
266
|
STATUS,
|
|
267
|
ROLE_USER,
|
|
268
|
BRANCH_ID,
|
|
269
|
CHECKER_ID,
|
|
270
|
APPROVE_DT,
|
|
271
|
PARENT_PROCESS_ID,
|
|
272
|
IS_LEAF,
|
|
273
|
COST_ID,
|
|
274
|
DVDM_ID,
|
|
275
|
NOTES,
|
|
276
|
IS_HAS_CHILD
|
|
277
|
)
|
|
278
|
VALUES
|
|
279
|
( @l_REQ_ID, -- REQ_ID - varchar(15)
|
|
280
|
'APPROVE', -- PROCESS_ID - varchar(10)
|
|
281
|
'C', -- STATUS - varchar(5)
|
|
282
|
'', -- ROLE_USER - varchar(50)
|
|
283
|
'', -- BRANCH_ID - varchar(15)
|
|
284
|
'', -- CHECKER_ID - varchar(15)
|
|
285
|
NULL, -- APPROVE_DT - datetime
|
|
286
|
'NEW', -- PARENT_PROCESS_ID - varchar(10)
|
|
287
|
'N', -- IS_LEAF - varchar(1)
|
|
288
|
'', -- COST_ID - varchar(15)
|
|
289
|
'', -- DVDM_ID - varchar(15)
|
|
290
|
N'Hoàn tất', -- NOTES - nvarchar(500)
|
|
291
|
NULL -- IS_HAS_CHILD - bit
|
|
292
|
)
|
|
293
|
UPDATE TR_REQUEST_DOC SET AUTH_STATUS='A',PROCESS_ID='APPROVE',USER_DVMS=MAKER_ID,BRANCH_DVMS=BRANCH_CREATE WHERE REQ_ID=@l_REQ_ID
|
|
294
|
|
|
295
|
EXEC dbo.TR_REQ_DOC_Ins_To_PO @p_TR_REQ_ID = @l_REQ_ID -- varchar(15)
|
|
296
|
|
|
297
|
END
|
|
298
|
END
|
|
299
|
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_PL_REQ_ID AND TRADE_TYPE='CDT'))
|
|
300
|
BEGIN
|
|
301
|
--insert master
|
|
302
|
|
|
303
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC', @l_REQ_ID out
|
|
304
|
IF @l_REQ_ID='' OR @l_REQ_ID IS NULL GOTO ABORT
|
|
305
|
|
|
306
|
|
|
307
|
exec [TR_CODE_GenKey] 'TR_REQUEST_DOC', '','PUR', @p_REQ_CODE out
|
|
308
|
IF @p_REQ_CODE='' OR @p_REQ_CODE IS NULL GOTO ABORT
|
|
309
|
|
|
310
|
|
|
311
|
INSERT INTO dbo.TR_REQUEST_DOC
|
|
312
|
(
|
|
313
|
REQ_ID,
|
|
314
|
REQ_CODE,
|
|
315
|
REQ_DT,
|
|
316
|
REQ_REASON,
|
|
317
|
PL_REQ_ID,
|
|
318
|
NOTES,
|
|
319
|
RECORD_STATUS,
|
|
320
|
CREATE_DT,
|
|
321
|
AUTH_STATUS,
|
|
322
|
TOTAL_AMT,
|
|
323
|
BRANCH_CREATE,
|
|
324
|
DEP_CREATE,
|
|
325
|
USER_REQUEST,MAKER_ID,PROCESS_ID
|
|
326
|
)
|
|
327
|
SELECT @l_REQ_ID,@p_REQ_CODE,GETDATE(),REQ_REASON, REQ_ID,'','1',GETDATE(),'E',TOTAL_AMT,BRANCH_CREATE,DEP_CREATE,MAKER_ID,MAKER_ID,'NEW' FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID
|
|
328
|
IF @@Error <> 0 GOTO ABORT
|
|
329
|
--Insert into TABLE PL_REQUEST_DOC_DT
|
|
330
|
|
|
331
|
DECLARE ListGoods CURSOR FOR
|
|
332
|
SELECT REQDT_ID,GOODS_ID,[DESCRIPTION],QUANTITY,PRICE,TOTAL_AMT,HANGHOA_ID,DVDM_ID,CURRENCY,TAXES,EXCHANGE_RATE,SUP_ID,TRADE_TYPE,NAME FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_PL_REQ_ID AND TRADE_TYPE='CDT'
|
|
333
|
OPEN ListGoods
|
|
334
|
|
|
335
|
DECLARE @TRN_TYPE VARCHAR(20)
|
|
336
|
SET @TRN_TYPE = (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='CDT')
|
|
337
|
SET @MAKER_ID=(SELECT MAKER_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID)
|
|
338
|
FETCH NEXT FROM ListGoods INTO @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,@HH_ID,@DVDM_ID,@CURRENCY,@TAXES,@EXCHANGE_RATE,@SUP_ID,@TRADE_TYPE,@REASON_CDT
|
|
339
|
WHILE @@FETCH_STATUS = 0
|
|
340
|
BEGIN
|
|
341
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_DT', @l_REQDT_ID out
|
|
342
|
IF @l_REQDT_ID='' OR @l_REQDT_ID IS NULL GOTO ABORT
|
|
343
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_PL_DT', @REQPL_DT_ID out
|
|
344
|
IF @REQPL_DT_ID='' OR @REQPL_DT_ID IS NULL GOTO ABORT
|
|
345
|
|
|
346
|
INSERT INTO dbo.TR_REQUEST_DOC_PL_DT
|
|
347
|
(
|
|
348
|
REQPL_DT_ID,
|
|
349
|
REQ_DOC_ID,
|
|
350
|
PL_REQDT_ID,
|
|
351
|
GD_ID,
|
|
352
|
SUP_ID,
|
|
353
|
HH_ID,
|
|
354
|
TRAN_TYPE_ID,
|
|
355
|
MAKER_ID,
|
|
356
|
CREATE_DT,
|
|
357
|
AUTH_STATUS,
|
|
358
|
CHECKER_ID,
|
|
359
|
APPROVE_DT,
|
|
360
|
DES_GOOD,
|
|
361
|
QUANTITY_PL,
|
|
362
|
PRICE_PL,
|
|
363
|
CURRENCY,
|
|
364
|
EXCHANGE_RATE,
|
|
365
|
TAXES,
|
|
366
|
TOTAL_AMT_ETM,
|
|
367
|
REASON_CDT,
|
|
368
|
REQ_DT,
|
|
369
|
NOTES,
|
|
370
|
RECORD_STATUS
|
|
371
|
)
|
|
372
|
VALUES
|
|
373
|
( @REQPL_DT_ID, -- REQPL_DT_ID - varchar(15)
|
|
374
|
@l_REQ_ID, -- REQ_DOC_ID - varchar(15)
|
|
375
|
@PL_REQDT_ID, -- PL_REQDT_ID - varchar(15)
|
|
376
|
@GOODS_ID, -- GD_ID - varchar(15)
|
|
377
|
@SUP_ID, -- SUP_ID - varchar(15)
|
|
378
|
@HH_ID, -- HH_ID - varchar(15)
|
|
379
|
@TRADE_TYPE, -- TRAN_TYPE_ID - varchar(15)
|
|
380
|
@MAKER_ID, -- MAKER_ID - varchar(15)
|
|
381
|
GETDATE(), -- CREATE_DT - datetime
|
|
382
|
'', -- AUTH_STATUS - varchar(50)
|
|
383
|
'', -- CHECKER_ID - varchar(15)
|
|
384
|
NULL, -- APPROVE_DT - datetime
|
|
385
|
@DESCRIPTION, -- DES_GOOD - nvarchar(500)
|
|
386
|
@QUANTITY, -- QUANTITY_PL - decimal(18, 0)
|
|
387
|
@PRICE, -- PRICE_PL - decimal(18, 2)
|
|
388
|
@CURRENCY, -- CURRENCY - nvarchar(50)
|
|
389
|
@EXCHANGE_RATE, -- EXCHANGE_RATE - decimal(18, 2)
|
|
390
|
@TAXES, -- TAXES - decimal(18, 2)
|
|
391
|
@TOTAL_AMT, -- TOTAL_AMT_ETM - decimal(18, 2)
|
|
392
|
@REASON_CDT, -- REASON_CDT - nvarchar(500)
|
|
393
|
GETDATE(), -- REQ_DT - datetime
|
|
394
|
N'', -- NOTES - nvarchar(500)
|
|
395
|
'1' -- RECORD_STATUS - varchar(1)
|
|
396
|
)
|
|
397
|
INSERT INTO dbo.TR_REQUEST_DOC_DT
|
|
398
|
(
|
|
399
|
REQDT_ID,
|
|
400
|
REQ_DOC_ID,
|
|
401
|
PL_REQDT_ID,
|
|
402
|
GD_ID,
|
|
403
|
DESCRIPTION,
|
|
404
|
RECORD_STATUS,
|
|
405
|
MAKER_ID,
|
|
406
|
CREATE_DT,
|
|
407
|
AUTH_STATUS,
|
|
408
|
CHECKER_ID,
|
|
409
|
APPROVE_DT,
|
|
410
|
QUANTITY,
|
|
411
|
PRICE,
|
|
412
|
TOTAL_AMT,
|
|
413
|
PRICE_ETM,
|
|
414
|
TOTAL_AMT_ETM,CURRENCY,HANGHOA_ID,DVDM_ID,TAXES,EXCHANGE_RATE,SUP_ID,TRAN_TYPE_ID,REQPL_DT_ID
|
|
415
|
|
|
416
|
)
|
|
417
|
VALUES
|
|
418
|
( @l_REQDT_ID, -- REQDT_ID - varchar(15)
|
|
419
|
@l_REQ_ID, -- REQ_ID - varchar(15)
|
|
420
|
@PL_REQDT_ID, -- PLAN_ID - varchar(15)
|
|
421
|
@GOODS_ID, -- GOODS_ID - varchar(15
|
|
422
|
-- NAME - nvarchar(200)
|
|
423
|
@DESCRIPTION, -- DESCRIPTION - nvarchar(500)
|
|
424
|
|
|
425
|
'1', -- RECORD_STATUS - varchar(1)
|
|
426
|
'', -- MAKER_ID - varchar(15)
|
|
427
|
GETDATE(), -- CREATE_DT - datetime
|
|
428
|
'U', -- AUTH_STATUS - varchar(50)
|
|
429
|
'', -- CHECKER_ID - varchar(15)
|
|
430
|
NULL, -- APPROVE_DT - datetime
|
|
431
|
@QUANTITY,
|
|
432
|
@PRICE,
|
|
433
|
@TOTAL_AMT,
|
|
434
|
@PRICE,
|
|
435
|
@TOTAL_AMT,
|
|
436
|
@CURRENCY,@HH_ID,@DVDM_ID,@TAXES,@EXCHANGE_RATE,@SUP_ID,@TRN_TYPE,@REQPL_DT_ID
|
|
437
|
)
|
|
438
|
|
|
439
|
|
|
440
|
IF @@ERROR <> 0 GOTO ABORT1
|
|
441
|
-- next Group_Id
|
|
442
|
FETCH NEXT FROM ListGoods INTO @PL_REQDT_ID,@GOODS_ID,@DESCRIPTION,@QUANTITY,@PRICE,@TOTAL_AMT,@HH_ID,@DVDM_ID,@CURRENCY,@TAXES,@EXCHANGE_RATE,@SUP_ID,@TRADE_TYPE,@REASON_CDT
|
|
443
|
END
|
|
444
|
|
|
445
|
UPDATE dbo.TR_REQUEST_DOC SET TOTAL_AMT = (
|
|
446
|
SELECT SUM(QUANTITY*PRICE) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID = @l_REQ_ID
|
|
447
|
) WHERE REQ_ID = @l_REQ_ID
|
|
448
|
|
|
449
|
CLOSE ListGoods
|
|
450
|
DEALLOCATE ListGoods
|
|
451
|
IF @@Error <> 0 GOTO ABORT
|
|
452
|
DECLARE lstCostCenter CURSOR FOR
|
|
453
|
SELECT COST_ID,NOTES FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_PL_REQ_ID
|
|
454
|
GROUP BY COST_ID,NOTES
|
|
455
|
OPEN lstCostCenter
|
|
456
|
FETCH NEXT FROM lstCostCenter INTO @COST_ID,@NOTES_COST
|
|
457
|
WHILE @@FETCH_STATUS=0
|
|
458
|
BEGIN
|
|
459
|
EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out
|
|
460
|
IF @l_REQ_COST_ID='' OR @l_REQ_COST_ID IS NULL GOTO ABORT
|
|
461
|
--INSERT INTO dbo.TR_REQUEST_COSTCENTER -- LUCTV 12-07-2021 KHI PHAT SINH PUR TU TTCT THÌ KHÔNG KÈM THEO DVCM
|
|
462
|
--(
|
|
463
|
-- REQ_COST_ID,
|
|
464
|
-- COST_ID,
|
|
465
|
-- REQ_ID,
|
|
466
|
-- NOTES,
|
|
467
|
-- AUTH_STATUS,
|
|
468
|
-- MAKER_ID,
|
|
469
|
-- CREATE_DT,
|
|
470
|
-- CHECKER_ID,
|
|
471
|
-- APPROVE_DT
|
|
472
|
--)
|
|
473
|
--VALUES
|
|
474
|
--( @l_REQ_COST_ID, -- REQ_COST_ID - varchar(15)
|
|
475
|
-- @COST_ID, -- COST_ID - varchar(15)
|
|
476
|
-- @l_REQ_ID, -- REQ_ID - varchar(15)
|
|
477
|
-- @NOTES_COST, -- NOTES - nvarchar(500)
|
|
478
|
-- '', -- AUTH_STATUS - varchar(1)
|
|
479
|
-- @MAKER_ID, -- MAKER_ID - varchar(15)
|
|
480
|
-- NULL, -- CREATE_DT - datetime
|
|
481
|
-- '', -- CHECKER_ID - varchar(15)
|
|
482
|
-- NULL -- APPROVE_DT - datetime
|
|
483
|
-- )
|
|
484
|
FETCH NEXT FROM lstCostCenter INTO @COST_ID,@NOTES_COST
|
|
485
|
END
|
|
486
|
CLOSE lstCostCenter
|
|
487
|
DEALLOCATE lstCostCenter
|
|
488
|
INSERT INTO dbo.PL_REQUEST_PROCESS
|
|
489
|
(
|
|
490
|
REQ_ID,
|
|
491
|
PROCESS_ID,
|
|
492
|
STATUS,
|
|
493
|
ROLE_USER,
|
|
494
|
BRANCH_ID,
|
|
495
|
CHECKER_ID,
|
|
496
|
APPROVE_DT,
|
|
497
|
PARENT_PROCESS_ID,
|
|
498
|
IS_LEAF,
|
|
499
|
COST_ID,
|
|
500
|
DVDM_ID,
|
|
501
|
NOTES,
|
|
502
|
IS_HAS_CHILD
|
|
503
|
)
|
|
504
|
VALUES
|
|
505
|
( @l_REQ_ID, -- REQ_ID - varchar(15)
|
|
506
|
'NEW', -- PROCESS_ID - varchar(10)
|
|
507
|
'C', -- STATUS - varchar(5)
|
|
508
|
'', -- ROLE_USER - varchar(50)
|
|
509
|
'', -- BRANCH_ID - varchar(15)
|
|
510
|
'', -- CHECKER_ID - varchar(15)
|
|
511
|
NULL, -- APPROVE_DT - datetime
|
|
512
|
'', -- PARENT_PROCESS_ID - varchar(10)
|
|
513
|
'N', -- IS_LEAF - varchar(1)
|
|
514
|
'', -- COST_ID - varchar(15)
|
|
515
|
'', -- DVDM_ID - varchar(15)
|
|
516
|
N'Chờ gửi phê duyệt', -- NOTES - nvarchar(500)
|
|
517
|
NULL -- IS_HAS_CHILD - bit
|
|
518
|
)
|
|
519
|
IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@l_REQ_ID AND TOTAL_AMT <=10000000 AND
|
|
520
|
NOT EXISTS (SELECT * FROM dbo.CM_BRANCH WHERE BRANCH_ID=BRANCH_CREATE AND BRANCH_TYPE='HS')))
|
|
521
|
BEGIN
|
|
522
|
UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P', NOTES =N'Tự động phát sinh & duyệt hoàn tất' WHERE REQ_ID=@l_REQ_ID AND PROCESS_ID='NEW'
|
|
523
|
INSERT INTO dbo.PL_REQUEST_PROCESS
|
|
524
|
(
|
|
525
|
REQ_ID,
|
|
526
|
PROCESS_ID,
|
|
527
|
STATUS,
|
|
528
|
ROLE_USER,
|
|
529
|
BRANCH_ID,
|
|
530
|
CHECKER_ID,
|
|
531
|
APPROVE_DT,
|
|
532
|
PARENT_PROCESS_ID,
|
|
533
|
IS_LEAF,
|
|
534
|
COST_ID,
|
|
535
|
DVDM_ID,
|
|
536
|
NOTES,
|
|
537
|
IS_HAS_CHILD
|
|
538
|
)
|
|
539
|
VALUES
|
|
540
|
( @l_REQ_ID, -- REQ_ID - varchar(15)
|
|
541
|
'APPROVE', -- PROCESS_ID - varchar(10)
|
|
542
|
'C', -- STATUS - varchar(5)
|
|
543
|
'', -- ROLE_USER - varchar(50)
|
|
544
|
'', -- BRANCH_ID - varchar(15)
|
|
545
|
'', -- CHECKER_ID - varchar(15)
|
|
546
|
NULL, -- APPROVE_DT - datetime
|
|
547
|
'NEW', -- PARENT_PROCESS_ID - varchar(10)
|
|
548
|
'N', -- IS_LEAF - varchar(1)
|
|
549
|
'', -- COST_ID - varchar(15)
|
|
550
|
'', -- DVDM_ID - varchar(15)
|
|
551
|
N'Hoàn tất', -- NOTES - nvarchar(500)
|
|
552
|
NULL -- IS_HAS_CHILD - bit
|
|
553
|
)
|
|
554
|
UPDATE TR_REQUEST_DOC SET AUTH_STATUS='A',PROCESS_ID='APPROVE',USER_DVMS=MAKER_ID,BRANCH_DVMS=BRANCH_CREATE WHERE REQ_ID=@l_REQ_ID
|
|
555
|
EXEC dbo.TR_REQ_DOC_Ins_To_PO @p_TR_REQ_ID = @l_REQ_ID -- varchar(15)
|
|
556
|
|
|
557
|
END
|
|
558
|
END
|
|
559
|
END --- END LUCTV 8.11.2022
|
|
560
|
COMMIT TRANSACTION
|
|
561
|
RETURN 1
|
|
562
|
ABORT:
|
|
563
|
BEGIN
|
|
564
|
ROLLBACK TRANSACTION
|
|
565
|
RETURN 1
|
|
566
|
End
|
|
567
|
ABORT1:
|
|
568
|
BEGIN
|
|
569
|
CLOSE ListGoods
|
|
570
|
DEALLOCATE ListGoods
|
|
571
|
ROLLBACK TRANSACTION
|
|
572
|
RETURN 1
|
|
573
|
End
|