|
1
|
USE [gAMSPro_VietcapitalBank_v2]
|
|
2
|
GO
|
|
3
|
/****** Object: StoredProcedure [dbo].[TR_REQ_DOC_Ins_To_PO] Script Date: 7/13/2020 2:43:52 PM ******/
|
|
4
|
SET ANSI_NULLS ON
|
|
5
|
GO
|
|
6
|
SET QUOTED_IDENTIFIER ON
|
|
7
|
GO
|
|
8
|
|
|
9
|
ALTER PROCEDURE [dbo].[TR_REQ_DOC_Ins_To_PO] @p_TR_REQ_ID VARCHAR(15)
|
|
10
|
AS
|
|
11
|
BEGIN TRANSACTION;
|
|
12
|
DECLARE @l_SUP_ID VARCHAR(15)
|
|
13
|
DECLARE @p_PO_CODE VARCHAR(15),
|
|
14
|
@p_BRANCH_ID VARCHAR(20),
|
|
15
|
@p_SUP_ID VARCHAR(20),
|
|
16
|
@p_TOTAL_AMT DECIMAL(18,2),
|
|
17
|
@p_MAKER_ID VARCHAR(20),
|
|
18
|
@p_TR_REQ_CODE VARCHAR(20),
|
|
19
|
@p_SUP_NAME NVARCHAR(200),
|
|
20
|
@p_SUP_ADDR NVARCHAR(200),
|
|
21
|
@p_BRANCH_RE VARCHAR(20),
|
|
22
|
@p_PO_NAME NVARCHAR(100);
|
|
23
|
--- LAY DANH SACH NHA CUNG CAP CUA HANG HOA TRONG PYCMS - LUCTV 25052020
|
|
24
|
DECLARE @TABLE_NCC TABLE (SUP_ID VARCHAR(15))
|
|
25
|
INSERT INTO @TABLE_NCC SELECT SUP_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID GROUP BY SUP_ID
|
|
26
|
WHILE((SELECT COUNT(*) FROM @TABLE_NCC)>0)
|
|
27
|
BEGIN
|
|
28
|
SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
|
|
29
|
--insert master
|
|
30
|
DECLARE @l_PO_ID VARCHAR(15);
|
|
31
|
EXEC SYS_CodeMasters_Gen 'TR_PO_MASTER', @l_PO_ID OUT;
|
|
32
|
IF @l_PO_ID = '' OR @l_PO_ID IS NULL
|
|
33
|
GOTO ABORT;
|
|
34
|
SELECT @p_BRANCH_ID=BRANCH_DVMS,@p_BRANCH_RE=BRANCH_CREATE,@p_TR_REQ_CODE=REQ_CODE,@p_PO_NAME=REQ_REASON,@p_MAKER_ID=USER_DVMS FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TR_REQ_ID
|
|
35
|
|
|
36
|
--SELECT TOP 1 @p_SUP_ID = SUP_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID
|
|
37
|
SET @p_SUP_ID =@l_SUP_ID
|
|
38
|
SET @p_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)
|
|
39
|
--TU PHAT SINH SO PO
|
|
40
|
DECLARE @ldate INT = (SELECT YEAR(GETDATE()) );
|
|
41
|
EXEC [dbo].[TR_PO_MASTER_NO_Gen] @p_BRANCH_ID, @ldate, @p_PO_CODE OUT;
|
|
42
|
IF @p_PO_CODE = ''
|
|
43
|
OR @p_PO_CODE IS NULL
|
|
44
|
GOTO ABORT;
|
|
45
|
|
|
46
|
INSERT INTO TR_PO_MASTER
|
|
47
|
(
|
|
48
|
[PO_TYPE],
|
|
49
|
[PO_ID],
|
|
50
|
[PO_CODE],
|
|
51
|
[PO_NAME],
|
|
52
|
[CONTRACT_ID],
|
|
53
|
[SUP_ID],
|
|
54
|
[SUP_NAME],
|
|
55
|
[SUP_ADDR],
|
|
56
|
[INPUT_DT],
|
|
57
|
[PAYMENT_DT],
|
|
58
|
[TOTAL_AMT],
|
|
59
|
[REQ_DOC_ID],
|
|
60
|
[DELIVERY_DT],
|
|
61
|
[PAYAPP_DT],
|
|
62
|
[NOTES],
|
|
63
|
[RECORD_STATUS],
|
|
64
|
[MAKER_ID],
|
|
65
|
[CREATE_DT],
|
|
66
|
[AUTH_STATUS],
|
|
67
|
[CHECKER_ID],
|
|
68
|
[APPROVE_DT],
|
|
69
|
BRANCH_ID
|
|
70
|
)
|
|
71
|
VALUES
|
|
72
|
(1, @l_PO_ID, @p_PO_CODE, @p_PO_NAME, '', @p_SUP_ID, @p_SUP_NAME, @p_SUP_ADDR, GETDATE(),
|
|
73
|
NULL, @p_TOTAL_AMT, @p_TR_REQ_ID, NULL,
|
|
74
|
NULL, '', '1', @p_MAKER_ID, GETDATE(),
|
|
75
|
'E', NULL, NULL, @p_BRANCH_ID);
|
|
76
|
IF @@Error <> 0
|
|
77
|
GOTO ABORT;
|
|
78
|
PRINT 'INSERT MASTER SUCCESS';
|
|
79
|
INSERT INTO dbo.TR_PO_MASTER_TEMP
|
|
80
|
(
|
|
81
|
PO_ID,
|
|
82
|
TR_REQ_ID,
|
|
83
|
TR_REQ_CODE
|
|
84
|
)
|
|
85
|
VALUES
|
|
86
|
( @l_PO_ID, -- PO_ID - varchar(15)
|
|
87
|
@p_TR_REQ_ID, -- TR_REQ_ID - varchar(20)
|
|
88
|
@p_TR_REQ_CODE -- TR_REQ_CODE - varchar(20)
|
|
89
|
);
|
|
90
|
DECLARE @HANGHOA_ID VARCHAR(20),@QUANTITY DECIMAL(18,0),@PRICE DECIMAL(18,2),@TOTAL_AMT DECIMAL(18,2),@HH_TYPE VARCHAR(20),@HH_NAME NVARCHAR(100)
|
|
91
|
DECLARE lstData CURSOR FOR
|
|
92
|
SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID
|
|
93
|
|
|
94
|
OPEN lstData
|
|
95
|
FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
|
|
96
|
WHILE @@FETCH_STATUS = 0
|
|
97
|
BEGIN
|
|
98
|
SELECT @HH_NAME= HH_NAME,@HH_TYPE=HH_TYPE_ID FROM dbo.CM_HANGHOA WHERE HH_ID=@HANGHOA_ID
|
|
99
|
DECLARE @l_PD_ID VARCHAR(15)
|
|
100
|
EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @l_PD_ID out
|
|
101
|
IF @l_PD_ID='' OR @l_PD_ID IS NULL GOTO ABORT
|
|
102
|
|
|
103
|
INSERT INTO dbo.TR_PO_DETAIL
|
|
104
|
(
|
|
105
|
PD_ID,
|
|
106
|
PO_ID,
|
|
107
|
PLAN_ID,
|
|
108
|
TRADE_ID,
|
|
109
|
GOODS_ID,
|
|
110
|
DESCRIPTION,
|
|
111
|
UNIT_ID,
|
|
112
|
QUANTITY,
|
|
113
|
PRICE,
|
|
114
|
TOTAL_AMT,
|
|
115
|
IS_DELIVERY,
|
|
116
|
DELIVERY_DT,
|
|
117
|
PAYMENT_STATUS,
|
|
118
|
AMOUNT_PAID,
|
|
119
|
PAID_DT,
|
|
120
|
INVOICENO,
|
|
121
|
NOTES,
|
|
122
|
RECEIVE_BRANCH,
|
|
123
|
RECEIVE_ADDR,
|
|
124
|
RECEIVE_PERSON,
|
|
125
|
RECEIVE_TEL,
|
|
126
|
RECORD_STATUS,
|
|
127
|
MAKER_ID,
|
|
128
|
CREATE_DT,
|
|
129
|
AUTH_STATUS,
|
|
130
|
CHECKER_ID,
|
|
131
|
APPROVE_DT,
|
|
132
|
EXP_DELIVERY_DT,
|
|
133
|
GOODS_NAME,
|
|
134
|
INVOICE_DT,
|
|
135
|
GOODSTYPE_REAL,
|
|
136
|
VAT,
|
|
137
|
PRICE_VAT,
|
|
138
|
CONTRACT_DT
|
|
139
|
)
|
|
140
|
VALUES
|
|
141
|
( @l_PD_ID, -- PD_ID - varchar(15)
|
|
142
|
@l_PO_ID, -- PO_ID - varchar(15)
|
|
143
|
'', -- PLAN_ID - varchar(15)
|
|
144
|
'', -- TRADE_ID - varchar(15)
|
|
145
|
@HANGHOA_ID, -- GOODS_ID - varchar(15)
|
|
146
|
N'', -- DESCRIPTION - nvarchar(500)
|
|
147
|
'', -- UNIT_ID - varchar(15)
|
|
148
|
@QUANTITY, -- QUANTITY - decimal(18, 0)
|
|
149
|
ROUND(@PRICE/1.1,0), -- PRICE - decimal(18, 0)
|
|
150
|
@TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0)
|
|
151
|
'', -- IS_DELIVERY - varchar(1)
|
|
152
|
NULL, -- DELIVERY_DT - datetime
|
|
153
|
'CTT', -- PAYMENT_STATUS - varchar(4)
|
|
154
|
NULL, -- AMOUNT_PAID - decimal(18, 0)
|
|
155
|
NULL, -- PAID_DT - datetime
|
|
156
|
'', -- INVOICENO - varchar(1000)
|
|
157
|
N'', -- NOTES - nvarchar(1000)
|
|
158
|
@p_BRANCH_RE, -- RECEIVE_BRANCH - varchar(15)
|
|
159
|
N'', -- RECEIVE_ADDR - nvarchar(1000)
|
|
160
|
N'', -- RECEIVE_PERSON - nvarchar(500)
|
|
161
|
'', -- RECEIVE_TEL - varchar(100)
|
|
162
|
'', -- RECORD_STATUS - varchar(1)
|
|
163
|
@p_MAKER_ID, -- MAKER_ID - varchar(15)
|
|
164
|
GETDATE(), -- CREATE_DT - datetime
|
|
165
|
'', -- AUTH_STATUS - varchar(50)
|
|
166
|
'', -- CHECKER_ID - varchar(15)
|
|
167
|
NULL, -- APPROVE_DT - datetime
|
|
168
|
NULL, -- EXP_DELIVERY_DT - datetime
|
|
169
|
@HH_NAME, -- GOODS_NAME - nvarchar(500)
|
|
170
|
NULL, -- INVOICE_DT - datetime
|
|
171
|
@HH_TYPE, -- GOODSTYPE_REAL - varchar(15)
|
|
172
|
10, -- VAT - decimal(18, 2)
|
|
173
|
--@TOTAL_AMT - (ROUND(@PRICE/1.1,0) *@QUANTITY), -- PRICE_VAT - decimal(18, 0)
|
|
174
|
@PRICE -ROUND(@PRICE/1.1,0),
|
|
175
|
'' -- CONTRACT_DT - varchar(15)
|
|
176
|
)
|
|
177
|
|
|
178
|
FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
|
|
179
|
END
|
|
180
|
CLOSE lstData;
|
|
181
|
DEALLOCATE lstData;
|
|
182
|
|
|
183
|
SET @TOTAL_AMT=(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)
|
|
184
|
|
|
185
|
DECLARE @l_PAY_ID VARCHAR(15)
|
|
186
|
EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
|
|
187
|
IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
|
|
188
|
INSERT INTO dbo.TR_PO_PAYMENT
|
|
189
|
(
|
|
190
|
PAY_ID,
|
|
191
|
PO_ID,
|
|
192
|
PAY_PHASE,
|
|
193
|
EXP_DT,
|
|
194
|
[PERCENT],
|
|
195
|
AMOUNT,
|
|
196
|
NOTES,
|
|
197
|
RECORD_STATUS,
|
|
198
|
MAKER_ID,
|
|
199
|
CREATE_DT,
|
|
200
|
AUTH_STATUS,
|
|
201
|
CHECKER_ID,
|
|
202
|
APPROVE_DT
|
|
203
|
)
|
|
204
|
VALUES
|
|
205
|
( @l_PAY_ID, -- PAY_ID - varchar(15)
|
|
206
|
@l_PO_ID, -- PO_ID - varchar(15)
|
|
207
|
'1', -- PAY_PHASE - varchar(20)
|
|
208
|
GETDATE(), -- EXP_DT - datetime
|
|
209
|
50, -- PERCENT - decimal(18, 0)
|
|
210
|
@TOTAL_AMT / 2 , -- AMOUNT - decimal(18, 0)
|
|
211
|
N'', -- NOTES - nvarchar(1000)
|
|
212
|
'1', -- RECORD_STATUS - varchar(1)
|
|
213
|
@p_MAKER_ID, -- MAKER_ID - varchar(15)
|
|
214
|
GETDATE(), -- CREATE_DT - datetime
|
|
215
|
'U', -- AUTH_STATUS - varchar(50)
|
|
216
|
'', -- CHECKER_ID - varchar(15)
|
|
217
|
NULL -- APPROVE_DT - datetime
|
|
218
|
)
|
|
219
|
EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
|
|
220
|
IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
|
|
221
|
INSERT INTO dbo.TR_PO_PAYMENT
|
|
222
|
(
|
|
223
|
PAY_ID,
|
|
224
|
PO_ID,
|
|
225
|
PAY_PHASE,
|
|
226
|
EXP_DT,
|
|
227
|
[PERCENT],
|
|
228
|
AMOUNT,
|
|
229
|
NOTES,
|
|
230
|
RECORD_STATUS,
|
|
231
|
MAKER_ID,
|
|
232
|
CREATE_DT,
|
|
233
|
AUTH_STATUS,
|
|
234
|
CHECKER_ID,
|
|
235
|
APPROVE_DT
|
|
236
|
)
|
|
237
|
VALUES
|
|
238
|
( @l_PAY_ID, -- PAY_ID - varchar(15)
|
|
239
|
@l_PO_ID, -- PO_ID - varchar(15)
|
|
240
|
'2', -- PAY_PHASE - varchar(20)
|
|
241
|
GETDATE(), -- EXP_DT - datetime
|
|
242
|
50, -- PERCENT - decimal(18, 0)
|
|
243
|
@TOTAL_AMT / 2 , -- AMOUNT - decimal(18, 0)
|
|
244
|
N'', -- NOTES - nvarchar(1000)
|
|
245
|
'1', -- RECORD_STATUS - varchar(1)
|
|
246
|
@p_MAKER_ID, -- MAKER_ID - varchar(15)
|
|
247
|
GETDATE(), -- CREATE_DT - datetime
|
|
248
|
'E', -- AUTH_STATUS - varchar(50)
|
|
249
|
'', -- CHECKER_ID - varchar(15)
|
|
250
|
NULL -- APPROVE_DT - datetime
|
|
251
|
)
|
|
252
|
|
|
253
|
IF(@TOTAL_AMT <=5000000)
|
|
254
|
BEGIN
|
|
255
|
UPDATE dbo.TR_PO_MASTER SET AUTH_STATUS='A' WHERE PO_ID=@l_PO_ID
|
|
256
|
END
|
|
257
|
--XOA DI NHA CUNG CAP
|
|
258
|
DELETE FROM @TABLE_NCC WHERE SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
|
|
259
|
--- KET THUC VONG LAP KIEM TRA DANH SACH NHA CUNG CAP PYCMS
|
|
260
|
END
|
|
261
|
COMMIT TRANSACTION;
|
|
262
|
RETURN 1;
|
|
263
|
ABORT:
|
|
264
|
BEGIN
|
|
265
|
ROLLBACK TRANSACTION;
|
|
266
|
RETURN 1;
|
|
267
|
END;
|
|
268
|
ABORT1:
|
|
269
|
BEGIN
|
|
270
|
CLOSE lstData;
|
|
271
|
DEALLOCATE lstData;
|
|
272
|
ROLLBACK TRANSACTION;
|
|
273
|
RETURN 1;
|
|
274
|
END;
|
|
275
|
|
|
276
|
|
|
277
|
|
|
278
|
|
|
279
|
|
|
280
|
|
|
281
|
|