|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_KT_Upd]
|
|
3
|
@p_REQ_PAY_ID varchar(15)= NULL,
|
|
4
|
@p_REQ_PAY_CODE varchar(50) = NULL,
|
|
5
|
@p_REQ_DT VARCHAR(20)= NULL,
|
|
6
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
7
|
@p_DEP_ID varchar(15) = NULL,
|
|
8
|
@p_REQ_REASON nvarchar(MAX) = NULL,
|
|
9
|
@p_REQ_TYPE varchar(15) = NULL,
|
|
10
|
@P_REQ_ENTRIES nvarchar(MAX) = NULL,
|
|
11
|
@p_REQ_DESCRIPTION nvarchar(MAX) = NULL,
|
|
12
|
@p_REF_ID varchar(15) = NULL,
|
|
13
|
@p_RECEIVER_PO nvarchar(250) = NULL,
|
|
14
|
@p_REQ_PAY_TYPE varchar(15) = NULL,
|
|
15
|
@p_REQ_TYPE_CURRENCY nvarchar(50) = NULL,
|
|
16
|
@p_REQ_AMT decimal(18, 0) = NULL,
|
|
17
|
@p_REQ_TEMP_AMT decimal(18, 0) = NULL,
|
|
18
|
@p_MAKER_ID varchar(15) = NULL,
|
|
19
|
@p_CREATE_DT varchar(25) = NULL,
|
|
20
|
@p_EDITOR_ID varchar(15) = NULL,
|
|
21
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
22
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
23
|
@p_APPROVE_DT varchar(25) = NULL,
|
|
24
|
@p_CREATE_DT_KT varchar(25) = NULL,
|
|
25
|
@p_MAKER_ID_KT varchar(15) = NULL,
|
|
26
|
@p_AUTH_STATUS_KT varchar(1) = NULL,
|
|
27
|
@p_CHECKER_ID_KT varchar(1) = NULL,
|
|
28
|
@p_APPROVE_DT_KT varchar(25)= null,
|
|
29
|
@p_CONFIRM_NOTE nvarchar(500) = NULL,
|
|
30
|
@p_BRANCH_CREATE varchar(15) = NULL,
|
|
31
|
@p_NOTES varchar(15) = NULL,
|
|
32
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
33
|
@p_TRANSFER_MAKER nvarchar(50) = NULL,
|
|
34
|
@p_TRANSFER_DT varchar(25) = NULL,
|
|
35
|
@p_TRASFER_USER_RECIVE varchar(15) = NULL,
|
|
36
|
@p_PROCESS varchar(15) = NULL,
|
|
37
|
@p_PAY_PHASE VARCHAR(15) = NULL,
|
|
38
|
@p_XMP_TEMP XML = NULL,
|
|
39
|
@p_XMP_TEMP_2 XML = NULL
|
|
40
|
AS
|
|
41
|
|
|
42
|
--Validation is here
|
|
43
|
IF ((SELECT AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) ='R')
|
|
44
|
BEGIN
|
|
45
|
SELECT '-1' Result,'' REQ_PAY_ID,N'Phiếu đề nghị thanh toán đang được trả về đơn vị. Vui lòng chờ đơn vị cập nhật thông tin và duyệt lại!' ErrorDesc
|
|
46
|
RETURN '-1'
|
|
47
|
END
|
|
48
|
DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),
|
|
49
|
@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000),@GL_CODE VARCHAR(100)
|
|
50
|
DECLARE @VAT decimal(18, 0) =NULL, @TRANS_NO nvarchar(50)=NULL, @TRANS_DT VARCHAR(20)=NULL,@INVOICE_SIGN nvarchar(50) = NULL,@INVOICE_NO_SIGN nvarchar(50)=null,
|
|
51
|
@INVOICE_NO nvarchar(50) = NULL,@INVOICE_DT VARCHAR(20) = NULL,@SELLER nvarchar(250) = NULL,@TAX_NO nvarchar(15) = NULL,@GOODS_NAME nvarchar(250) = NULL,
|
|
52
|
@PRICE decimal(18, 0) = NULL,@TAX decimal(18, 0) = NULL,@NOTES NVARCHAR(MAX) = NULL,@VAT_RATE decimal(18,2),
|
|
53
|
@ACC_NO VARCHAR(25),@ACC_NAME NVARCHAR(250),@ISSUED_BY NVARCHAR(250),@ISSUED_DT VARCHAR(20),@RATE DECIMAL(18,2),@PRICE_KT DECIMAL(18,2) =0,@TOTAL_AMT_KT DECIMAL(18,2) =0,@VAT_KT DECIMAL(18,2) =0,
|
|
54
|
@TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15)
|
|
55
|
DECLARE @hdoc INT;
|
|
56
|
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
|
|
57
|
DECLARE XmlData CURSOR FOR
|
|
58
|
SELECT *
|
|
59
|
FROM
|
|
60
|
OPENXML(@hdoc, '/Root/XmlData', 2)
|
|
61
|
WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),
|
|
62
|
BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000),GL_CODE VARCHAR(100))
|
|
63
|
OPEN XmlData;
|
|
64
|
---
|
|
65
|
DECLARE XmlInvoice CURSOR FOR
|
|
66
|
SELECT *
|
|
67
|
FROM
|
|
68
|
OPENXML(@hdoc, '/Root/XmlInvoice', 2)
|
|
69
|
WITH(TRANS_NO nvarchar(50),TRANS_DT VARCHAR(20),INVOICE_SIGN nvarchar(50),INVOICE_NO_SIGN nvarchar(50),
|
|
70
|
INVOICE_NO nvarchar(50),INVOICE_DT VARCHAR(20) ,SELLER nvarchar(250),TAX_NO nvarchar(15),GOODS_NAME nvarchar(250) ,
|
|
71
|
PRICE decimal(18,2),TAX decimal(18, 2),VAT decimal(18,2),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),PRICE_KT DECIMAL(18,2),VAT_KT DECIMAL(18,2),TOTAL_AMT_KT DECIMAL(18,2),TYPE_VAT VARCHAR(15), TYPE_FUNC VARCHAR(15))
|
|
72
|
OPEN XmlInvoice;
|
|
73
|
DECLARE @INDEX INT =0, @INDEX_IV INT =0
|
|
74
|
BEGIN TRANSACTION
|
|
75
|
-- KHAI BAO CAC BUOC DUYET- XAC NHAN
|
|
76
|
DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT
|
|
77
|
SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND STATUS_JOB='C')
|
|
78
|
SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1
|
|
79
|
----
|
|
80
|
--IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))
|
|
81
|
|
|
82
|
IF(@p_XMP_TEMP IS NOT NULL)
|
|
83
|
BEGIN
|
|
84
|
UPDATE TR_REQ_PAYMENT SET
|
|
85
|
AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTE=@p_CONFIRM_NOTE
|
|
86
|
WHERE REQ_PAY_ID =@p_REQ_PAY_ID
|
|
87
|
DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID
|
|
88
|
DELETE FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID= @p_REQ_PAY_ID
|
|
89
|
IF @@Error <> 0 GOTO ABORT
|
|
90
|
DECLARE @DR_CR_NAME NVARCHAR(50),@ACC_NAME_FN NVARCHAR(500)
|
|
91
|
FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
|
|
92
|
WHILE @@fetch_status=0 BEGIN
|
|
93
|
SET @INDEX = @INDEX +1
|
|
94
|
SET @GL_CODE = RIGHT(@GL_CODE,9)
|
|
95
|
IF(@DR_CR='D')
|
|
96
|
BEGIN
|
|
97
|
SET @DR_CR_NAME =N'Nợ'
|
|
98
|
END
|
|
99
|
ELSE
|
|
100
|
BEGIN
|
|
101
|
SET @DR_CR_NAME =N'Có'
|
|
102
|
END
|
|
103
|
--SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)
|
|
104
|
IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')
|
|
105
|
BEGIN
|
|
106
|
ROLLBACK TRANSACTION
|
|
107
|
CLOSE XmlData;
|
|
108
|
DEALLOCATE XmlData;
|
|
109
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc
|
|
110
|
RETURN '-1'
|
|
111
|
END
|
|
112
|
IF(@DR_CR IS NULL OR @DR_CR ='')
|
|
113
|
BEGIN
|
|
114
|
ROLLBACK TRANSACTION
|
|
115
|
CLOSE XmlData;
|
|
116
|
DEALLOCATE XmlData;
|
|
117
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản nợ không được phép để trống' ErrorDesc
|
|
118
|
RETURN '-1'
|
|
119
|
END
|
|
120
|
IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')
|
|
121
|
BEGIN
|
|
122
|
ROLLBACK TRANSACTION
|
|
123
|
CLOSE XmlData;
|
|
124
|
DEALLOCATE XmlData;
|
|
125
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Đơn vị nhận không được phép để trống' ErrorDesc
|
|
126
|
RETURN '-1'
|
|
127
|
END
|
|
128
|
IF(@ACCT IS NULL OR @ACCT ='')
|
|
129
|
BEGIN
|
|
130
|
ROLLBACK TRANSACTION
|
|
131
|
CLOSE XmlData;
|
|
132
|
DEALLOCATE XmlData;
|
|
133
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc
|
|
134
|
RETURN '-1'
|
|
135
|
END
|
|
136
|
IF(@TRN_DESC IS NULL OR @TRN_DESC ='')
|
|
137
|
BEGIN
|
|
138
|
ROLLBACK TRANSACTION
|
|
139
|
CLOSE XmlData;
|
|
140
|
DEALLOCATE XmlData;
|
|
141
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc
|
|
142
|
RETURN '-1'
|
|
143
|
END
|
|
144
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
145
|
BEGIN
|
|
146
|
IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
147
|
BEGIN
|
|
148
|
ROLLBACK TRANSACTION
|
|
149
|
CLOSE XmlData;
|
|
150
|
DEALLOCATE XmlData;
|
|
151
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###') ErrorDesc
|
|
152
|
RETURN '-1'
|
|
153
|
END
|
|
154
|
END
|
|
155
|
IF(LEN(@ACCT) >9 AND (@GL_CODE IS NULL OR @GL_CODE =''))
|
|
156
|
BEGIN
|
|
157
|
ROLLBACK TRANSACTION
|
|
158
|
CLOSE XmlData;
|
|
159
|
DEALLOCATE XmlData;
|
|
160
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N'Nếu bạn đang hạch toán CASA. Vui lòng bấm vào nút kiểm tra tài khoản để lấy chính xác thông tin tài khoản (tên tài khoản, tài khoản GL tương tứng) từ Core' ErrorDesc
|
|
161
|
RETURN '-1'
|
|
162
|
---
|
|
163
|
END
|
|
164
|
DECLARE @p_ET_ID VARCHAR(15);
|
|
165
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;
|
|
166
|
IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;
|
|
167
|
INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR,DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)
|
|
168
|
VALUES (@p_ET_ID,@p_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@TRN_DESC,GETDATE(),@p_MAKER_ID_KT)
|
|
169
|
-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT
|
|
170
|
IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))
|
|
171
|
BEGIN
|
|
172
|
INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID,CREATE_DT) VALUES
|
|
173
|
(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin',GETDATE())
|
|
174
|
END
|
|
175
|
ELSE
|
|
176
|
BEGIN
|
|
177
|
IF(@GL_CODE IS NOT NULL AND @GL_CODE <> '')
|
|
178
|
BEGIN
|
|
179
|
UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)
|
|
180
|
WHERE ACC_NO=@ACCT
|
|
181
|
END
|
|
182
|
--UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE), CREATE_DT = GETDATE()
|
|
183
|
--WHERE ACC_NO=@ACCT
|
|
184
|
END
|
|
185
|
IF @@error<>0 GOTO ABORT;
|
|
186
|
FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
|
|
187
|
END;
|
|
188
|
CLOSE XmlData;
|
|
189
|
DEALLOCATE XmlData;
|
|
190
|
-- UPDATE INVOICE
|
|
191
|
--Insert XmlData
|
|
192
|
|
|
193
|
FETCH NEXT FROM XmlInvoice INTO @TRANS_NO , @TRANS_DT ,@INVOICE_SIGN ,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,
|
|
194
|
@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC
|
|
195
|
WHILE @@fetch_status=0 BEGIN
|
|
196
|
SET @INDEX_IV = @INDEX_IV+1
|
|
197
|
IF(LEN(@INVOICE_NO) >7)
|
|
198
|
BEGIN
|
|
199
|
ROLLBACK TRANSACTION
|
|
200
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' không được lớn hơn 7 kí tự' ErrorDesc
|
|
201
|
RETURN '-1'
|
|
202
|
END
|
|
203
|
IF(LEN(@INVOICE_NO) <7)
|
|
204
|
BEGIN
|
|
205
|
ROLLBACK TRANSACTION
|
|
206
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' phải đủ 7 kí tự' ErrorDesc
|
|
207
|
RETURN '-1'
|
|
208
|
END
|
|
209
|
IF(@TYPE_FUNC ='HC')
|
|
210
|
BEGIN
|
|
211
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN AND INVOICE_SIGN =@INVOICE_SIGN AND
|
|
212
|
TAX_NO =@TAX_NO AND TAX =@TAX AND TYPE_FUNC ='HC' AND AUTH_STATUS <>'D' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'E')))
|
|
213
|
BEGIN
|
|
214
|
ROLLBACK TRANSACTION
|
|
215
|
SELECT '-1' as Result, '' REQ_PAY_ID,N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc
|
|
216
|
RETURN '-1'
|
|
217
|
END
|
|
218
|
END
|
|
219
|
IF(@TYPE_FUNC ='KT')
|
|
220
|
BEGIN
|
|
221
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN
|
|
222
|
AND INVOICE_SIGN =@INVOICE_SIGN AND TAX_NO =@TAX_NO AND TAX =@TAX AND TYPE_FUNC ='KT' AND AUTH_STATUS <>'D' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'E')))
|
|
223
|
BEGIN
|
|
224
|
ROLLBACK TRANSACTION
|
|
225
|
SELECT '-1' as Result, '' REQ_PAY_ID,N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc
|
|
226
|
RETURN '-1'
|
|
227
|
END
|
|
228
|
END
|
|
229
|
DECLARE @p_REQ_INV_ID VARCHAR(15);
|
|
230
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE', @p_REQ_INV_ID OUT;
|
|
231
|
IF @p_REQ_INV_ID='' OR @p_REQ_INV_ID IS NULL GOTO ABORT;
|
|
232
|
INSERT INTO TR_REQ_PAY_INVOICE(REQ_PAYDT_ID,REQ_PAY_ID,TRANS_NO,TRANS_DT,INVOICE_SIGN,INVOICE_NO,INVOICE_DT,SELLER,TAX_NO,GOODS_NAME,PRICE,TAX,VAT,NOTE,
|
|
233
|
MAKER_ID,CREATE_DT,EDITOR_ID,AUTH_STATUS,CHECKER_ID,APPROVE_DT,
|
|
234
|
CREATE_DT_KT,MAKER_ID_KT,AUTH_STATUS_KT,CHECKER_ID_KT,APPROVE_DT_KT,RECORD_STATUS,INVOICE_NO_SIGN,VAT_RATE,CURRENCY,RATE,PRICE_KT ,VAT_KT,TOTAL_AMT_KT,TYPE_VAT,TYPE_FUNC)
|
|
235
|
VALUES (@p_REQ_INV_ID,@p_REQ_PAY_ID , @TRANS_NO , CONVERT(DATE,@TRANS_DT,103) ,@INVOICE_SIGN ,@INVOICE_NO ,CONVERT(DATE,@INVOICE_DT,103) ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,
|
|
236
|
@p_MAKER_ID,GETDATE(),NULL,'U',NULL,NULL,GETDATE(),@p_MAKER_ID_KT,'U',NULL,NULL,'1',@INVOICE_NO_SIGN,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC)
|
|
237
|
IF @@error<>0 GOTO ABORT;
|
|
238
|
FETCH NEXT FROM XmlInvoice
|
|
239
|
INTO @TRANS_NO , @TRANS_DT,@INVOICE_SIGN,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,
|
|
240
|
@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC
|
|
241
|
END;
|
|
242
|
CLOSE XmlInvoice;
|
|
243
|
DEALLOCATE XmlInvoice;
|
|
244
|
--- Luu log chinh sua
|
|
245
|
INSERT INTO TR_REQ_PAY_INVOICE_LOG SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID
|
|
246
|
-----
|
|
247
|
--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN
|
|
248
|
END
|
|
249
|
COMMIT TRANSACTION
|
|
250
|
IF(@p_XMP_TEMP IS NULL)
|
|
251
|
BEGIN
|
|
252
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'S'))
|
|
253
|
BEGIN
|
|
254
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được được đề xuất từ chối! Vui lòng đợi KSV xử lý phiếu' ErrorDesc
|
|
255
|
RETURN '-1'
|
|
256
|
END
|
|
257
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'P'))
|
|
258
|
BEGIN
|
|
259
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được gửi phê duyệt thành công trước đó! Vui lòng đợi KSV xử lý phiếu' ErrorDesc
|
|
260
|
RETURN '-1'
|
|
261
|
END
|
|
262
|
-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP
|
|
263
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
264
|
BEGIN
|
|
265
|
DECLARE @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)
|
|
266
|
SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C'),0)
|
|
267
|
SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D'),0)
|
|
268
|
IF(@SUM_CR <> @SUM_DR)
|
|
269
|
BEGIN
|
|
270
|
--ROLLBACK TRANSACTION
|
|
271
|
--CLOSE XmlData;
|
|
272
|
--DEALLOCATE XmlData;
|
|
273
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có' ErrorDesc
|
|
274
|
RETURN '-1'
|
|
275
|
END
|
|
276
|
END
|
|
277
|
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT
|
|
278
|
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)
|
|
279
|
-- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY
|
|
280
|
UPDATE TR_REQ_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT = GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID
|
|
281
|
INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
|
|
282
|
VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt',N'Giao dịch viên cập nhật thông tin')
|
|
283
|
SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
|
|
284
|
RETURN '4'
|
|
285
|
END
|
|
286
|
SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID, '' ErrorDesc
|
|
287
|
RETURN '0'
|
|
288
|
ABORT:
|
|
289
|
BEGIN
|
|
290
|
ROLLBACK TRANSACTION
|
|
291
|
CLOSE XmlData;
|
|
292
|
DEALLOCATE XmlData;
|
|
293
|
SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
|
|
294
|
RETURN '-1'
|
|
295
|
End
|
|
296
|
|