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