|
1
|
|
|
2
|
|
|
3
|
ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_Ins]
|
|
4
|
@p_REQ_PAY_AUTO_ID VARCHAR(20)= NULL,
|
|
5
|
@p_REQ_PAY_AUTO_CODE VARCHAR(20)= NULL,
|
|
6
|
@p_REQ_PAY_AUTO_DT VARCHAR(20)= NULL,
|
|
7
|
@p_REQ_PAY_AUTO_TYPE VARCHAR(20)= NULL,
|
|
8
|
@p_REQ_PAY_AUTO_SERVICE_TYPE VARCHAR(20)= NULL,
|
|
9
|
@p_REQ_PAY_AUTO_STATUS VARCHAR(20)= NULL,
|
|
10
|
@p_REQ_PAY_AUTO_EFFECTIVE_DT VARCHAR(20)= NULL,
|
|
11
|
@p_BRANCH_ID VARCHAR(20)= NULL,
|
|
12
|
@p_DEP_ID VARCHAR(20)= NULL,
|
|
13
|
@p_BRANCH_CREATE VARCHAR(20)= NULL,
|
|
14
|
@p_TRANSFER_USER_RECEIVE VARCHAR(20)= NULL,
|
|
15
|
@p_CONFIRM_NOTE VARCHAR(20)= NULL,
|
|
16
|
@p_CONTRACT_ID VARCHAR(20)= NULL,
|
|
17
|
@p_PROCESS VARCHAR(20)= NULL,
|
|
18
|
@p_TRANSFER_MAKER VARCHAR(20)= NULL,
|
|
19
|
@p_TRANSFER_DT VARCHAR(20)= NULL,
|
|
20
|
@p_MAKER_ID VARCHAR(20)= NULL,
|
|
21
|
@p_CREATE_DT VARCHAR(20)= NULL,
|
|
22
|
@p_EDITOR_ID VARCHAR(20)= NULL,
|
|
23
|
@p_EDIT_DT VARCHAR(20)= NULL,
|
|
24
|
@p_CHECKER_ID VARCHAR(20)= NULL,
|
|
25
|
@p_APPROVE_DT VARCHAR(20)= NULL,
|
|
26
|
@p_AUTH_STATUS VARCHAR(20)= NULL,
|
|
27
|
@p_MAKER_ID_KT VARCHAR(20)= NULL,
|
|
28
|
@p_CREATE_DT_KT VARCHAR(20)= NULL,
|
|
29
|
@p_CHECKER_ID_KT VARCHAR(20)= NULL,
|
|
30
|
@p_APPROVE_DT_KT VARCHAR(20)= NULL,
|
|
31
|
@p_AUTH_STATUS_KT VARCHAR(20)= NULL,
|
|
32
|
@p_RECORD_STATUS VARCHAR(20)= NULL,
|
|
33
|
@p_FRMDATE VARCHAR(20)= NULL,
|
|
34
|
@p_TODATE VARCHAR(20)= NULL,
|
|
35
|
@p_LEVEL VARCHAR(20)= NULL,
|
|
36
|
@p_USER_LOGIN VARCHAR(20)= NULL,
|
|
37
|
@p_IS_SEND_APPR VARCHAR(20)= NULL,
|
|
38
|
@p_TYPE_SEARCH VARCHAR(20)= NULL,
|
|
39
|
@p_BRANCH_LOGIN VARCHAR(20)= NULL,
|
|
40
|
@p_NOTIFI_EXPIRED_DT VARCHAR(20)= NULL,
|
|
41
|
@p_IS_MAKER_CONFIRM_CHECKBOX VARCHAR(20)= NULL,
|
|
42
|
@p_XMP_SCHEDULE XML = NULL,
|
|
43
|
@p_XMP_SCHEDULE_DETAIL XML = NULL,
|
|
44
|
@p_XMP_RECURRING XML = NULL,
|
|
45
|
@p_XMP_BUDGET_ALLOCATION XML = NULL,
|
|
46
|
@p_XMP_METHOD XML = NULL
|
|
47
|
AS
|
|
48
|
-- BEGIN VALIDATE MASTER
|
|
49
|
IF(ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '' OR ISNULL(@p_MAKER_ID, '') = '' OR ISNULL(@p_BRANCH_ID, '') = '' OR ISNULL(@p_DEP_ID, '') = '' OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
|
|
50
|
BEGIN
|
|
51
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Các thông tin sau không được để trống: loại thanh toán tự động, người tạo yêu cầu, đơn vị tạo yêu cầu, phòng ban tạo yêu cầu, loại dịch vụ thanh toán' ErrorDesc
|
|
52
|
RETURN '-1'
|
|
53
|
END
|
|
54
|
IF(ISNULL(@p_CONTRACT_ID, '') = '')
|
|
55
|
BEGIN
|
|
56
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Số hợp đồng(ID hệ thống) không được đế trống' ErrorDesc
|
|
57
|
RETURN '-1'
|
|
58
|
END
|
|
59
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID <> @p_REQ_PAY_AUTO_ID AND CONTRACT_ID = @p_CONTRACT_ID AND AUTH_STATUS NOT IN ('E', 'R', 'D') AND REQ_PAY_AUTO_TYPE = 'A'))
|
|
60
|
BEGIN
|
|
61
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Số hợp đồng(ID hệ thống) ' + @p_CONTRACT_ID + N' đang được đề nghị thanh toán tự động trong hệ thống' ErrorDesc
|
|
62
|
RETURN '-1'
|
|
63
|
END
|
|
64
|
-- END VALIDATE MASTER
|
|
65
|
BEGIN TRANSACTION
|
|
66
|
|
|
67
|
-- GEN MA SO PHIEU TU DONG
|
|
68
|
EXEC [dbo].[PAY_CODE_GenKey] 'TR_REQ_PAYMENT_AUTO', '','', @p_REQ_PAY_AUTO_CODE out
|
|
69
|
IF (@p_REQ_PAY_AUTO_CODE IS NULL OR @p_REQ_PAY_AUTO_CODE ='')
|
|
70
|
BEGIN
|
|
71
|
ROLLBACK TRANSACTION
|
|
72
|
SELECT '-1' Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Mã số phiếu không được để trống' ErrorDesc
|
|
73
|
RETURN '-1'
|
|
74
|
END
|
|
75
|
IF EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE )
|
|
76
|
BEGIN
|
|
77
|
ROLLBACK TRANSACTION
|
|
78
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Số phiếu yêu cầu thanh toán tự động đã tồn tại trong hệ thống' ErrorDesc
|
|
79
|
RETURN '-1'
|
|
80
|
END
|
|
81
|
|
|
82
|
DECLARE @BRANCH_MANAGE_ID VARCHAR(20)
|
|
83
|
SET @BRANCH_MANAGE_ID = ISNULL((SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID), '');
|
|
84
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT_AUTO', @p_REQ_PAY_AUTO_ID out
|
|
85
|
IF @p_REQ_PAY_AUTO_ID='' OR @p_REQ_PAY_AUTO_ID IS NULL GOTO ABORT
|
|
86
|
INSERT INTO TR_REQ_PAYMENT_AUTO(REQ_PAY_AUTO_ID, REQ_PAY_AUTO_CODE, REQ_PAY_AUTO_TYPE, REQ_PAY_AUTO_SERVICE_TYPE, REQ_PAY_AUTO_STATUS, REQ_PAY_AUTO_EFFECTIVE_DT,
|
|
87
|
BRANCH_ID, DEP_ID, BRANCH_CREATE, TRANSFER_USER_RECEIVE, CONFIRM_NOTE, CONTRACT_ID, TRANSFER_MAKER, TRANSFER_DT, PROCESS, MAKER_ID, CREATE_DT, EDITOR_ID, EDIT_DT,
|
|
88
|
CHECKER_ID, APPROVE_DT, AUTH_STATUS, MAKER_ID_KT, CREATE_DT_KT, CHECKER_ID_KT, APPROVE_DT_KT, AUTH_STATUS_KT, RECORD_STATUS, NOTIFI_EXPIRED_DT, IS_MAKER_CONFIRM, IS_MAKER_CONFIRM_CHECKBOX, BRANCH_MANAGE_ID)
|
|
89
|
VALUES(@p_REQ_PAY_AUTO_ID, @p_REQ_PAY_AUTO_CODE, @p_REQ_PAY_AUTO_TYPE, @p_REQ_PAY_AUTO_SERVICE_TYPE, @p_REQ_PAY_AUTO_STATUS, @p_REQ_PAY_AUTO_EFFECTIVE_DT,
|
|
90
|
@p_BRANCH_ID, @p_DEP_ID, @p_BRANCH_CREATE, @p_TRANSFER_USER_RECEIVE, @p_CONFIRM_NOTE, @p_CONTRACT_ID, @p_TRANSFER_MAKER, @p_TRANSFER_DT, @p_PROCESS, @p_MAKER_ID, GETDATE(), NULL, NULL,
|
|
91
|
NULL,NULL, 'E', NULL, NULL, NULL, NULL, NULL, '1', CONVERT(DATE,@p_NOTIFI_EXPIRED_DT,103), @p_IS_MAKER_CONFIRM_CHECKBOX, @p_IS_MAKER_CONFIRM_CHECKBOX, @BRANCH_MANAGE_ID)
|
|
92
|
IF @@Error <> 0 GOTO ABORT
|
|
93
|
|
|
94
|
--- INSERT LICH THANH TOAN
|
|
95
|
DECLARE @hDocSchedule INT
|
|
96
|
EXEC sp_xml_preparedocument @hDocSchedule OUTPUT, @p_XMP_SCHEDULE;
|
|
97
|
|
|
98
|
DECLARE @REQ_PAY_AUTO_SCHEDULE_ID VARCHAR(20), @CONTRACT_ID VARCHAR(20), @TYPE_PERIOD VARCHAR(20), @START_DT VARCHAR(20), @END_DT VARCHAR(20), @PERIOD_AMT DECIMAL(18,2), @PERIOD_NUMBER DECIMAL(18,2), @PERIOD_PAY_DT VARCHAR(20), @PERIOD_PAY_DAY DECIMAL(18,0)
|
|
99
|
|
|
100
|
DECLARE XmlDataSchedule CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocSchedule, 'Root/XmlDataSchedule',2)
|
|
101
|
WITH(REQ_PAY_AUTO_CONTRACT_ID VARCHAR(20), CONTRACT_ID VARCHAR(20), TYPE_PERIOD VARCHAR(20), START_DT VARCHAR(20), END_DT VARCHAR(20), PERIOD_AMT DECIMAL(18,2), PERIOD_NUMBER DECIMAL(18,2), PERIOD_PAY_DT VARCHAR(20), PERIOD_PAY_DAY DECIMAL(18,0))
|
|
102
|
OPEN XmlDataSchedule
|
|
103
|
|
|
104
|
FETCH NEXT FROM XmlDataSchedule INTO @REQ_PAY_AUTO_SCHEDULE_ID, @CONTRACT_ID, @TYPE_PERIOD, @START_DT, @END_DT, @PERIOD_AMT, @PERIOD_NUMBER , @PERIOD_PAY_DT, @PERIOD_PAY_DAY
|
|
105
|
WHILE @@fetch_status=0
|
|
106
|
BEGIN
|
|
107
|
EXEC SYS_CodeMasters_Gen 'REQ_PAY_AUTO_SCHEDULE_ID', @REQ_PAY_AUTO_SCHEDULE_ID OUT;
|
|
108
|
IF @REQ_PAY_AUTO_SCHEDULE_ID='' OR @REQ_PAY_AUTO_SCHEDULE_ID IS NULL GOTO ABORT;
|
|
109
|
|
|
110
|
INSERT INTO TR_REQ_PAY_AUTO_SCHEDULE(REQ_PAY_AUTO_SCHEDULE_ID, REQ_PAY_AUTO_ID, CONTRACT_ID, TYPE_PERIOD, START_DT, END_DT, PERIOD_AMT, PERIOD_NUMBER, PERIOD_PAY_DT, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, AUTH_STATUS, RECORD_STATUS, PERIOD_PAY_DAY)
|
|
111
|
VALUES (@REQ_PAY_AUTO_SCHEDULE_ID, @p_REQ_PAY_AUTO_ID, @CONTRACT_ID, @TYPE_PERIOD, CONVERT(DATE,@START_DT,103), CONVERT(DATE,@END_DT,103), @PERIOD_AMT, @PERIOD_NUMBER, CONVERT(DATE,@PERIOD_PAY_DT,103), @p_MAKER_ID, GETDATE(), NULL, NULL, 'A', '1', @PERIOD_PAY_DAY)
|
|
112
|
|
|
113
|
IF @@error<>0 GOTO ABORT;
|
|
114
|
FETCH NEXT FROM XmlDataSchedule INTO @REQ_PAY_AUTO_SCHEDULE_ID, @CONTRACT_ID, @TYPE_PERIOD, @START_DT, @END_DT, @PERIOD_AMT, @PERIOD_NUMBER , @PERIOD_PAY_DT, @PERIOD_PAY_DAY
|
|
115
|
END
|
|
116
|
CLOSE XmlDataSchedule;
|
|
117
|
DEALLOCATE XmlDataSchedule;
|
|
118
|
|
|
119
|
--- INSERT CHI TIET LICH THANH TOAN
|
|
120
|
DECLARE @hDocScheduleDetail INT
|
|
121
|
EXEC sp_xml_preparedocument @hDocScheduleDetail OUTPUT, @p_XMP_SCHEDULE_DETAIL;
|
|
122
|
|
|
123
|
DECLARE @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID VARCHAR(20), @EXPECTED_DT VARCHAR(20), @PAY_AUTO_AMT DECIMAL(18,2), @PAY_AUTO_DESC NVARCHAR(1000), @PAY_AUTO_STATUS VARCHAR(20), @EXACT_DT VARCHAR(20), @END_PERIOD_DT VARCHAR(20), @NOTES NVARCHAR(255)
|
|
124
|
|
|
125
|
DECLARE XmlDataScheduleDetail CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocScheduleDetail, 'Root/XmlDataScheduleDetail',2)
|
|
126
|
WITH(REQ_PAY_AUTO_SCHEDULE_DETAIL_ID VARCHAR(20),REQ_PAY_AUTO_SCHEDULE_ID VARCHAR(20),EXPECTED_DT VARCHAR(20),PAY_AUTO_AMT DECIMAL(18,2),PAY_AUTO_DESC NVARCHAR(1000),PAY_AUTO_STATUS VARCHAR(20), EXACT_DT VARCHAR(20), START_DT VARCHAR(20), END_DT VARCHAR(20), TYPE_PERIOD VARCHAR(20), END_PERIOD_DT VARCHAR(20), NOTES NVARCHAR(255) )
|
|
127
|
OPEN XmlDataScheduleDetail
|
|
128
|
|
|
129
|
FETCH NEXT FROM XmlDataScheduleDetail INTO @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, @REQ_PAY_AUTO_SCHEDULE_ID, @EXPECTED_DT, @PAY_AUTO_AMT, @PAY_AUTO_DESC, @PAY_AUTO_STATUS, @EXACT_DT, @START_DT, @END_DT, @TYPE_PERIOD, @END_PERIOD_DT, @NOTES
|
|
130
|
WHILE @@fetch_status=0
|
|
131
|
BEGIN
|
|
132
|
EXEC SYS_CodeMasters_Gen 'REQ_PAY_AUTO_SCHEDULE_DETAIL_ID', @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID OUT;
|
|
133
|
IF @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID='' OR @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID IS NULL GOTO ABORT;
|
|
134
|
|
|
135
|
INSERT INTO TR_REQ_PAY_AUTO_SCHEDULE_DETAIL(REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, REQ_PAY_AUTO_SCHEDULE_ID,REQ_PAY_AUTO_ID, EXPECTED_DT, PAY_AUTO_AMT, PAY_AUTO_DESC, PAY_AUTO_STATUS, EXACT_DT, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, AUTH_STATUS, RECORD_STATUS, START_DT, END_DT, TYPE_PERIOD, END_PERIOD_DT, NOTES)
|
|
136
|
VALUES (@REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, @REQ_PAY_AUTO_SCHEDULE_ID, @p_REQ_PAY_AUTO_ID, CONVERT(DATE,@EXPECTED_DT,103), @PAY_AUTO_AMT, @PAY_AUTO_DESC, @PAY_AUTO_STATUS, CONVERT(DATE,@EXACT_DT,103), @p_MAKER_ID, GETDATE(), NULL, NULL, 'A', '1', CONVERT(DATE,@START_DT,103), CONVERT(DATE,@END_DT,103), @TYPE_PERIOD, CONVERT(DATE,@END_PERIOD_DT,103), @NOTES)
|
|
137
|
|
|
138
|
IF @@error<>0 GOTO ABORT;
|
|
139
|
FETCH NEXT FROM XmlDataScheduleDetail INTO @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, @REQ_PAY_AUTO_SCHEDULE_ID, @EXPECTED_DT, @PAY_AUTO_AMT, @PAY_AUTO_DESC, @PAY_AUTO_STATUS, @EXACT_DT, @START_DT, @END_DT, @TYPE_PERIOD, @END_PERIOD_DT, @NOTES
|
|
140
|
END
|
|
141
|
CLOSE XmlDataScheduleDetail;
|
|
142
|
DEALLOCATE XmlDataScheduleDetail;
|
|
143
|
--- INSERT PHUONG THUC THANH TOAN
|
|
144
|
DECLARE @hDocMeThod INT
|
|
145
|
EXEC sp_xml_preparedocument @hDocMeThod OUTPUT, @p_XMP_METHOD;
|
|
146
|
|
|
147
|
DECLARE @RECEIVE_ID VARCHAR(15), @RECEIVE_NAME NVARCHAR(100), @REQ_PAY_REASON NVARCHAR(MAX), @TOTAL_AMT DECIMAL(18,2),
|
|
148
|
@REQ_PAY_TYPE VARCHAR(1), @REQ_PAY_DESC NVARCHAR(MAX), @REQ_PAY_ENTRIES NVARCHAR(MAX), @ACC_NO VARCHAR(25), @ACC_NAME NVARCHAR(250),
|
|
149
|
@ISSUED_BY NVARCHAR(250), @ISSUED_DT VARCHAR(20), @CURRENCY VARCHAR(15), @RATE DECIMAL(18,2), @CHECK_IN VARCHAR(15), @TYPE_TRANSFER VARCHAR(15),
|
|
150
|
@BANKCODE_NAPAS VARCHAR(20), @BANKCODE VARCHAR(20), @BANKNAME VARCHAR(250), @SUB_ISSUED_BY NVARCHAR(250), @RECEIVER_DEBIT VARCHAR(20)
|
|
151
|
|
|
152
|
DECLARE XmlDataMethod CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocMeThod, 'Root/XmlDataMethod',2)
|
|
153
|
WITH(RECEIVE_ID VARCHAR(15),RECEIVE_NAME NVARCHAR(100),REQ_PAY_REASON NVARCHAR(MAX),TOTAL_AMT DECIMAL(18,2),
|
|
154
|
REQ_PAY_TYPE VARCHAR(1),REQ_PAY_DESC NVARCHAR(MAX),REQ_PAY_ENTRIES NVARCHAR(MAX),ACC_NO VARCHAR(25),
|
|
155
|
ACC_NAME NVARCHAR(250),ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),CHECK_IN VARCHAR(15),TYPE_TRANSFER VARCHAR(15),
|
|
156
|
BANKCODE_NAPAS VARCHAR(20), BANKCODE VARCHAR(20), BANKNAME VARCHAR(250), SUB_ISSUED_BY NVARCHAR(250), RECEIVER_DEBIT VARCHAR(20))
|
|
157
|
OPEN XmlDataMethod
|
|
158
|
|
|
159
|
FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT,@REQ_PAY_TYPE,
|
|
160
|
@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO, @ACC_NAME,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT
|
|
161
|
WHILE @@fetch_status=0
|
|
162
|
BEGIN
|
|
163
|
IF(@REQ_PAY_TYPE<>'1')
|
|
164
|
BEGIN
|
|
165
|
SET @ISSUED_DT = NULL
|
|
166
|
END
|
|
167
|
IF(@TYPE_TRANSFER IS NULL OR @TYPE_TRANSFER ='')
|
|
168
|
BEGIN
|
|
169
|
SET @TYPE_TRANSFER ='A'
|
|
170
|
END
|
|
171
|
|
|
172
|
DECLARE @p_REQ_PAY_METHOD_ID VARCHAR(15);
|
|
173
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_METHOD', @p_REQ_PAY_METHOD_ID OUT;
|
|
174
|
IF @p_REQ_PAY_METHOD_ID='' OR @p_REQ_PAY_METHOD_ID IS NULL GOTO ABORT;
|
|
175
|
|
|
176
|
INSERT INTO TR_REQ_PAY_AUTO_METHOD(METHOD_ID, REQ_PAY_AUTO_ID, EMP_ID, REQ_PAY_REASON, TOTAL_AMT, REQ_PAY_TYPE, REQ_PAY_DESC, REQ_PAY_ENTRIES, TEMP, MAKER_ID, CREATE_DT, ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT, CURRENCY, RATE, CHECK_IN, TYPE_TRANSFER, BANKCODE_NAPAS, BANKCODE, BANKNAME, SUB_ISSUED_BY, RECEIVER_DEBIT)
|
|
177
|
VALUES (@p_REQ_PAY_METHOD_ID,@p_REQ_PAY_AUTO_ID,@RECEIVE_ID,@REQ_PAY_REASON, @TOTAL_AMT,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,'',@p_MAKER_ID,GETDATE(),@ACC_NO, @ACC_NAME,@ISSUED_BY,CONVERT(DATE,@ISSUED_DT,103), 'VND' ,'1',@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME,@SUB_ISSUED_BY, @RECEIVER_DEBIT )
|
|
178
|
|
|
179
|
IF @@error<>0 GOTO ABORT;
|
|
180
|
FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT,@REQ_PAY_TYPE,
|
|
181
|
@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO, @ACC_NAME,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT
|
|
182
|
END
|
|
183
|
CLOSE XmlDataMethod;
|
|
184
|
DEALLOCATE XmlDataMethod;
|
|
185
|
|
|
186
|
--- INSERT THÔNG TIN DỊCH VỤ
|
|
187
|
DECLARE @hDocRecurring INT
|
|
188
|
EXEC sp_xml_preparedocument @hDocRecurring OUTPUT, @p_XMP_RECURRING;
|
|
189
|
|
|
190
|
DECLARE @REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), @SEVCODE VARCHAR(20), @SEVNAME NVARCHAR(255), @PRVCODE VARCHAR(20), @PRVNAME NVARCHAR(255), @ROUTETO VARCHAR(20), @TRANSACCOUNT VARCHAR(20), @TRANSACCOUNT_NAME VARCHAR(250),
|
|
191
|
@TRANS_GLCODE VARCHAR(20), @TRANS_GLNAME VARCHAR(250), @RECEIVE_GLCODE VARCHAR(20), @RECEIVE_GLNAME VARCHAR(250),
|
|
192
|
@CUSTOMER_ID VARCHAR(20), @CUSTOMER_CODE VARCHAR(20), @CUSTOMER_NAME NVARCHAR(255), @CUSTOMER_CODE_SYSTEM VARCHAR(20), @CUSTOMER_NAME_SYSTEM NVARCHAR(255), @CUSTOMER_ADDRESS_SYSTEM NVARCHAR(255), @IS_REGISTER VARCHAR(2), @IS_VALID VARCHAR(2),
|
|
193
|
@BILLCODE VARCHAR(50), @BILLALIAS NVARCHAR(255), @BILLID VARCHAR(20), @BILLTIME VARCHAR(20), @IS_CHECKED VARCHAR(1)
|
|
194
|
|
|
195
|
DECLARE XmlDataRecurring CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocRecurring, 'Root/XmlDataManufacturer',2)
|
|
196
|
WITH(REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), SEVCODE VARCHAR(20), SEVNAME NVARCHAR(255), PRVCODE VARCHAR(20), PRVNAME NVARCHAR(255), ROUTETO VARCHAR(20), TRANSACCOUNT VARCHAR(20), TRANSACCOUNT_NAME VARCHAR(250), TRANS_GLCODE VARCHAR(20), TRANS_GLNAME VARCHAR(250),
|
|
197
|
RECEIVE_GLCODE VARCHAR(20), RECEIVE_GLNAME VARCHAR(250), CUSTOMER_ID VARCHAR(20), CUSTOMER_CODE VARCHAR(20), CUSTOMER_NAME NVARCHAR(255), CUSTOMER_CODE_SYSTEM VARCHAR(20), CUSTOMER_NAME_SYSTEM NVARCHAR(255), CUSTOMER_ADDRESS_SYSTEM NVARCHAR(255),
|
|
198
|
IS_REGISTER VARCHAR(2), IS_VALID VARCHAR(2), BILLCODE VARCHAR(50), BILLALIAS NVARCHAR(255), BILLID VARCHAR(20), BILLTIME VARCHAR(20), IS_CHECKED VARCHAR(1))
|
|
199
|
OPEN XmlDataRecurring
|
|
200
|
|
|
201
|
|
|
202
|
FETCH NEXT FROM XmlDataRecurring INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @SEVCODE, @SEVNAME, @PRVCODE, @PRVNAME, @ROUTETO, @TRANSACCOUNT, @TRANSACCOUNT_NAME, @TRANS_GLCODE, @TRANS_GLNAME, @RECEIVE_GLCODE, @TRANS_GLNAME,
|
|
203
|
@CUSTOMER_ID, @CUSTOMER_CODE, @CUSTOMER_NAME, @CUSTOMER_CODE_SYSTEM, @CUSTOMER_NAME_SYSTEM, @CUSTOMER_ADDRESS_SYSTEM, @IS_REGISTER, @IS_VALID, @BILLCODE, @BILLALIAS, @BILLID, @BILLTIME, @IS_CHECKED
|
|
204
|
WHILE @@fetch_status=0
|
|
205
|
BEGIN
|
|
206
|
DECLARE @REQ_PAY_AUTO_RECURRING_ID VARCHAR(20)
|
|
207
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_AUTO_RECURRING', @REQ_PAY_AUTO_RECURRING_ID OUT;
|
|
208
|
IF @REQ_PAY_AUTO_RECURRING_ID = '' OR @REQ_PAY_AUTO_RECURRING_ID IS NULL GOTO ABORT;
|
|
209
|
|
|
210
|
INSERT INTO TR_REQ_PAY_AUTO_RECURRING(REQ_PAY_AUTO_RECURRING_ID, REQ_PAY_AUTO_MANUFACTURER_ID, REQ_PAY_AUTO_ID, SEVCODE, SEVNAME, PRVCODE, PRVNAME, ROUTETO, TRANSACCOUNT, TRANSACCOUNT_NAME, TRANS_GLCODE, TRANS_GLNAME, RECEIVE_GLCODE, RECEIVE_GLNAME,
|
|
211
|
CUSTOMER_CODE, CUSTOMER_NAME, CUSTOMER_CODE_SYSTEM, CUSTOMER_NAME_SYSTEM, CUSTOMER_ADDRESS_SYSTEM, IS_REGISTER, IS_VALID, MAKER_ID, CREATE_DT, RECORD_STATUS, BILLCODE, BILLALIAS, BILLID, BILLTIME, IS_CHECKED)
|
|
212
|
VALUES (@REQ_PAY_AUTO_RECURRING_ID, @REQ_PAY_AUTO_MANUFACTURER_ID, @p_REQ_PAY_AUTO_ID, @SEVCODE, @SEVNAME, @PRVCODE, @PRVNAME, @ROUTETO, @TRANSACCOUNT, @TRANSACCOUNT_NAME, @TRANS_GLCODE, @TRANS_GLNAME, @RECEIVE_GLCODE, @RECEIVE_GLNAME,
|
|
213
|
@CUSTOMER_CODE, @CUSTOMER_NAME, @CUSTOMER_CODE_SYSTEM, @CUSTOMER_NAME_SYSTEM, @CUSTOMER_ADDRESS_SYSTEM, @IS_REGISTER, @IS_VALID, @p_MAKER_ID, GETDATE(), '1', @BILLCODE, @BILLALIAS, @BILLID, @BILLTIME, @IS_CHECKED)
|
|
214
|
|
|
215
|
IF @@error<>0 GOTO ABORT;
|
|
216
|
FETCH NEXT FROM XmlDataRecurring INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @SEVCODE, @SEVNAME, @PRVCODE, @PRVNAME, @ROUTETO, @TRANSACCOUNT, @TRANSACCOUNT_NAME, @TRANS_GLCODE, @TRANS_GLNAME, @RECEIVE_GLCODE, @TRANS_GLNAME,
|
|
217
|
@CUSTOMER_ID, @CUSTOMER_CODE, @CUSTOMER_NAME, @CUSTOMER_CODE_SYSTEM, @CUSTOMER_NAME_SYSTEM, @CUSTOMER_ADDRESS_SYSTEM, @IS_REGISTER, @IS_VALID, @BILLCODE, @BILLALIAS, @BILLID, @BILLTIME, @IS_CHECKED
|
|
218
|
END
|
|
219
|
CLOSE XmlDataRecurring;
|
|
220
|
DEALLOCATE XmlDataRecurring;
|
|
221
|
|
|
222
|
--- INSERT PHÂN CHIA
|
|
223
|
DECLARE @hDocBudgetAllocation INT
|
|
224
|
EXEC sp_xml_preparedocument @hDocBudgetAllocation OUTPUT, @p_XMP_BUDGET_ALLOCATION;
|
|
225
|
|
|
226
|
DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID NVARCHAR(255), @TYPE_BUDGET_ALLOCAITON VARCHAR(20), @PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), @AMT_BUDGET_ALLOCAITON DECIMAL(18,2)
|
|
227
|
|
|
228
|
DECLARE XmlDataBudgetAllocation CURSOR FOR SELECT * FROM OPENXML(@hDocBudgetAllocation, 'Root/XmlDataBudgetAllocation',2)
|
|
229
|
WITH(REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), ACC_NO VARCHAR(20), ACC_NAME NVARCHAR(255), BRANCH_ID VARCHAR(20), DEP_ID NVARCHAR(255), TYPE_BUDGET_ALLOCAITON VARCHAR(20), PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), AMT_BUDGET_ALLOCAITON DECIMAL(18,2))
|
|
230
|
OPEN XmlDataBudgetAllocation
|
|
231
|
|
|
232
|
|
|
233
|
FETCH NEXT FROM XmlDataBudgetAllocation INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON
|
|
234
|
WHILE @@fetch_status=0
|
|
235
|
BEGIN
|
|
236
|
DECLARE @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID VARCHAR(20)
|
|
237
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_AUTO_ALLCOTION', @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID OUT;
|
|
238
|
IF @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID = '' OR @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID IS NULL GOTO ABORT;
|
|
239
|
|
|
240
|
INSERT INTO TR_REQ_PAY_AUTO_BUDGET_ALLOCATION(REQ_PAY_AUTO_BUDGET_ALLOCATION_ID, REQ_PAY_AUTO_MANUFACTURER_ID, REQ_PAY_AUTO_ID, ACC_NO, ACC_NAME, BRANCH_ID, DEP_ID, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON, MAKER_ID, CREATE_DT, RECORD_STATUS)
|
|
241
|
VALUES (@REQ_PAY_AUTO_BUDGET_ALLOCATION_ID, @REQ_PAY_AUTO_MANUFACTURER_ID, @p_REQ_PAY_AUTO_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON, @p_MAKER_ID, GETDATE(), '1')
|
|
242
|
|
|
243
|
IF @@error<>0 GOTO ABORT;
|
|
244
|
FETCH NEXT FROM XmlDataBudgetAllocation INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON
|
|
245
|
END
|
|
246
|
CLOSE XmlDataBudgetAllocation;
|
|
247
|
DEALLOCATE XmlDataBudgetAllocation;
|
|
248
|
|
|
249
|
COMMIT TRANSACTION
|
|
250
|
|
|
251
|
SELECT '0' as Result, @p_REQ_PAY_AUTO_ID REQ_PAY_ID, @p_REQ_PAY_AUTO_CODE AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
|
|
252
|
RETURN '0'
|
|
253
|
|
|
254
|
ABORT:
|
|
255
|
BEGIN
|
|
256
|
ROLLBACK TRANSACTION
|
|
257
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Lỗi không xác định' ErrorDesc
|
|
258
|
RETURN '-1'
|
|
259
|
End
|
|
260
|
|
|
261
|
GO
|
|
262
|
|
|
263
|
|
|
264
|
|
|
265
|
ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_Upd]
|
|
266
|
@p_REQ_PAY_AUTO_ID VARCHAR(20)= NULL,
|
|
267
|
@p_REQ_PAY_AUTO_CODE VARCHAR(20)= NULL,
|
|
268
|
@p_REQ_PAY_AUTO_DT VARCHAR(20)= NULL,
|
|
269
|
@p_REQ_PAY_AUTO_TYPE VARCHAR(20)= NULL,
|
|
270
|
@p_REQ_PAY_AUTO_SERVICE_TYPE VARCHAR(20)= NULL,
|
|
271
|
@p_REQ_PAY_AUTO_STATUS VARCHAR(20)= NULL,
|
|
272
|
@p_REQ_PAY_AUTO_EFFECTIVE_DT VARCHAR(20)= NULL,
|
|
273
|
@p_BRANCH_ID VARCHAR(20)= NULL,
|
|
274
|
@p_DEP_ID VARCHAR(20)= NULL,
|
|
275
|
@p_BRANCH_CREATE VARCHAR(20)= NULL,
|
|
276
|
@p_TRANSFER_USER_RECEIVE VARCHAR(20)= NULL,
|
|
277
|
@p_CONFIRM_NOTE VARCHAR(20)= NULL,
|
|
278
|
@p_CONTRACT_ID VARCHAR(20)= NULL,
|
|
279
|
@p_PROCESS VARCHAR(20)= NULL,
|
|
280
|
@p_TRANSFER_MAKER VARCHAR(20)= NULL,
|
|
281
|
@p_TRANSFER_DT VARCHAR(20)= NULL,
|
|
282
|
@p_MAKER_ID VARCHAR(20)= NULL,
|
|
283
|
@p_CREATE_DT VARCHAR(20)= NULL,
|
|
284
|
@p_EDITOR_ID VARCHAR(20)= NULL,
|
|
285
|
@p_EDIT_DT VARCHAR(20)= NULL,
|
|
286
|
@p_CHECKER_ID VARCHAR(20)= NULL,
|
|
287
|
@p_APPROVE_DT VARCHAR(20)= NULL,
|
|
288
|
@p_AUTH_STATUS VARCHAR(20)= NULL,
|
|
289
|
@p_MAKER_ID_KT VARCHAR(20)= NULL,
|
|
290
|
@p_CREATE_DT_KT VARCHAR(20)= NULL,
|
|
291
|
@p_CHECKER_ID_KT VARCHAR(20)= NULL,
|
|
292
|
@p_APPROVE_DT_KT VARCHAR(20)= NULL,
|
|
293
|
@p_AUTH_STATUS_KT VARCHAR(20)= NULL,
|
|
294
|
@p_RECORD_STATUS VARCHAR(20)= NULL,
|
|
295
|
@p_FRMDATE VARCHAR(20)= NULL,
|
|
296
|
@p_TODATE VARCHAR(20)= NULL,
|
|
297
|
@p_LEVEL VARCHAR(20)= NULL,
|
|
298
|
@p_USER_LOGIN VARCHAR(20)= NULL,
|
|
299
|
@p_IS_SEND_APPR VARCHAR(20)= NULL,
|
|
300
|
@p_TYPE_SEARCH VARCHAR(20)= NULL,
|
|
301
|
@p_BRANCH_LOGIN VARCHAR(20)= NULL,
|
|
302
|
@p_NOTIFI_EXPIRED_DT VARCHAR(20)= NULL,
|
|
303
|
@p_IS_MAKER_CONFIRM_CHECKBOX VARCHAR(20)= NULL,
|
|
304
|
@p_XMP_SCHEDULE XML = NULL,
|
|
305
|
@p_XMP_SCHEDULE_DETAIL XML = NULL,
|
|
306
|
@p_XMP_RECURRING XML = NULL,
|
|
307
|
@p_XMP_BUDGET_ALLOCATION XML = NULL,
|
|
308
|
@p_XMP_METHOD XML = NULL
|
|
309
|
AS
|
|
310
|
-- BEGIN VALIDATE MASTER
|
|
311
|
IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(MAKER_ID, '') <> @p_USER_LOGIN AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
|
|
312
|
BEGIN
|
|
313
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Chỉnh sửa phiếu thất bại! Chỉ người tạo phiếu mới có quyền chỉnh sửa phiếu' ErrorDesc
|
|
314
|
RETURN '-1'
|
|
315
|
END
|
|
316
|
IF(ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '' OR ISNULL(@p_MAKER_ID, '') = '' OR ISNULL(@p_BRANCH_ID, '') = '' OR ISNULL(@p_DEP_ID, '') = '' OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
|
|
317
|
BEGIN
|
|
318
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Các thông tin sau không được để trống: loại thanh toán tự động, người tạo yêu cầu, đơn vị tạo yêu cầu, phòng ban tạo yêu cầu, loại dịch vụ thanh toán' ErrorDesc
|
|
319
|
RETURN '-1'
|
|
320
|
END
|
|
321
|
IF(ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '' OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
|
|
322
|
BEGIN
|
|
323
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Mã số phiếu không được đế trống' ErrorDesc
|
|
324
|
RETURN '-1'
|
|
325
|
END
|
|
326
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID <> @p_REQ_PAY_AUTO_ID AND CONTRACT_ID = @p_CONTRACT_ID AND AUTH_STATUS NOT IN ('E', 'R', 'D') AND REQ_PAY_AUTO_TYPE = 'A'))
|
|
327
|
BEGIN
|
|
328
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Số hợp đồng(ID hệ thống) ' + @p_CONTRACT_ID + N' đang được đề nghị thanh toán tự động trong hệ thống' ErrorDesc
|
|
329
|
RETURN '-1'
|
|
330
|
END
|
|
331
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
332
|
BEGIN
|
|
333
|
IF(ISNULL(@p_CONTRACT_ID, '') = '')
|
|
334
|
BEGIN
|
|
335
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Số hợp đồng(ID hệ thống) không được đế trống' ErrorDesc
|
|
336
|
RETURN '-1'
|
|
337
|
END
|
|
338
|
END
|
|
339
|
-- END VALIDATE MASTER
|
|
340
|
-- BEGIN VALIDATE FLOW
|
|
341
|
IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS = 'A' AND REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID))
|
|
342
|
BEGIN
|
|
343
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Phê duyệt thất bại! Phiếu yêu cầu thanh toán tự động đã được trưởng đơn vị phê duyệt trước đó' ErrorDesc
|
|
344
|
RETURN '-1'
|
|
345
|
END
|
|
346
|
IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS_KT = 'A' AND REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID))
|
|
347
|
BEGIN
|
|
348
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Phê duyệt thất bại! Phiếu yêu cầu thanh toán tự động đã được kiểm sát viên phê duyệt' ErrorDesc
|
|
349
|
RETURN '-1'
|
|
350
|
END
|
|
351
|
IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(PROCESS, '') <> '' AND REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID))
|
|
352
|
BEGIN
|
|
353
|
SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Cập nhật thất bại! Phiếu yêu cầu thanh toán tự động đã được gửi phê duyệt trước đó' ErrorDesc
|
|
354
|
RETURN '-1'
|
|
355
|
END
|
|
356
|
-- END VALIDATE FLOW
|
|
357
|
BEGIN TRANSACTION
|
|
358
|
DECLARE @BRANCH_MANAGE_ID VARCHAR(20)
|
|
359
|
SET @BRANCH_MANAGE_ID = ISNULL((SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID), '');
|
|
360
|
UPDATE TR_REQ_PAYMENT_AUTO
|
|
361
|
SET REQ_PAY_AUTO_SERVICE_TYPE = @p_REQ_PAY_AUTO_SERVICE_TYPE, AUTH_STATUS = 'E', CREATE_DT = GETDATE(),
|
|
362
|
TRANSFER_USER_RECEIVE = @p_TRANSFER_USER_RECEIVE, CONTRACT_ID = @p_CONTRACT_ID,
|
|
363
|
NOTIFI_EXPIRED_DT = CONVERT(DATE, @p_NOTIFI_EXPIRED_DT, 103), IS_MAKER_CONFIRM = @p_IS_MAKER_CONFIRM_CHECKBOX,
|
|
364
|
IS_MAKER_CONFIRM_CHECKBOX = @p_IS_MAKER_CONFIRM_CHECKBOX, BRANCH_MANAGE_ID = @BRANCH_MANAGE_ID
|
|
365
|
WHERE 1 = 1
|
|
366
|
AND REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
|
|
367
|
IF @@Error <> 0 GOTO ABORT
|
|
368
|
|
|
369
|
--- INSERT LICH THANH TOAN
|
|
370
|
DELETE FROM TR_REQ_PAY_AUTO_SCHEDULE WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
|
|
371
|
DECLARE @hDocSchedule INT
|
|
372
|
EXEC sp_xml_preparedocument @hDocSchedule OUTPUT, @p_XMP_SCHEDULE;
|
|
373
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
374
|
BEGIN
|
|
375
|
IF(@p_REQ_PAY_AUTO_TYPE = 'A' AND NOT EXISTS(SELECT * FROM OPENXML(@hDocSchedule, '/Root/XmlDataSchedule', 2) WITH(REQ_PAY_AUTO_ID varchar(20))))
|
|
376
|
BEGIN
|
|
377
|
ROLLBACK TRANSACTION
|
|
378
|
SELECT '-1' Result, '' REQ_PAY_ID, N'Danh sách thông tin định kỳ thanh toán không được phép để trống' ErrorDesc
|
|
379
|
RETURN '-1'
|
|
380
|
END
|
|
381
|
END
|
|
382
|
|
|
383
|
DECLARE @REQ_PAY_AUTO_SCHEDULE_ID VARCHAR(20), @CONTRACT_ID VARCHAR(20), @TYPE_PERIOD VARCHAR(20), @START_DT VARCHAR(20), @END_DT VARCHAR(20), @PERIOD_AMT DECIMAL(18,2), @PERIOD_NUMBER DECIMAL(18,2), @PERIOD_PAY_DT VARCHAR(20), @PERIOD_PAY_DAY DECIMAL(18,0)
|
|
384
|
|
|
385
|
DECLARE XmlDataSchedule CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocSchedule, 'Root/XmlDataSchedule',2)
|
|
386
|
WITH(REQ_PAY_AUTO_CONTRACT_ID VARCHAR(20), CONTRACT_ID VARCHAR(20), TYPE_PERIOD VARCHAR(20), START_DT VARCHAR(20), END_DT VARCHAR(20), PERIOD_AMT DECIMAL(18,2), PERIOD_NUMBER DECIMAL(18,2), PERIOD_PAY_DT VARCHAR(20), PERIOD_PAY_DAY DECIMAL(18,0))
|
|
387
|
OPEN XmlDataSchedule
|
|
388
|
|
|
389
|
DECLARE @INDEX_SCHEDULE INT = 0;
|
|
390
|
FETCH NEXT FROM XmlDataSchedule INTO @REQ_PAY_AUTO_SCHEDULE_ID, @CONTRACT_ID, @TYPE_PERIOD, @START_DT, @END_DT, @PERIOD_AMT, @PERIOD_NUMBER , @PERIOD_PAY_DT, @PERIOD_PAY_DAY
|
|
391
|
WHILE @@fetch_status=0
|
|
392
|
BEGIN
|
|
393
|
-- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE
|
|
394
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
395
|
BEGIN
|
|
396
|
SET @INDEX_SCHEDULE = @INDEX_SCHEDULE + 1
|
|
397
|
|
|
398
|
IF(@TYPE_PERIOD IS NULL OR @TYPE_PERIOD = '')
|
|
399
|
BEGIN
|
|
400
|
ROLLBACK TRANSACTION
|
|
401
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Định kỳ không được để trống' ErrorDesc
|
|
402
|
RETURN '-1'
|
|
403
|
END
|
|
404
|
IF(@START_DT IS NULL OR @START_DT = '')
|
|
405
|
BEGIN
|
|
406
|
ROLLBACK TRANSACTION
|
|
407
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Ngày bắt đầu không được để trống' ErrorDesc
|
|
408
|
RETURN '-1'
|
|
409
|
END
|
|
410
|
IF(@END_DT IS NULL OR @END_DT = '')
|
|
411
|
BEGIN
|
|
412
|
ROLLBACK TRANSACTION
|
|
413
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Ngày kết thúc không được để trống' ErrorDesc
|
|
414
|
RETURN '-1'
|
|
415
|
END
|
|
416
|
IF(@PERIOD_AMT IS NULL OR @PERIOD_AMT < 0)
|
|
417
|
BEGIN
|
|
418
|
ROLLBACK TRANSACTION
|
|
419
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Số tiền định kỳ thanh toán không được để trống hoặc bé hơn 0' ErrorDesc
|
|
420
|
RETURN '-1'
|
|
421
|
END
|
|
422
|
IF(@PERIOD_PAY_DAY IS NULL OR @PERIOD_PAY_DAY < 0)
|
|
423
|
BEGIN
|
|
424
|
ROLLBACK TRANSACTION
|
|
425
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Ngày thanh toán định kỳ không được để trống' ErrorDesc
|
|
426
|
RETURN '-1'
|
|
427
|
END
|
|
428
|
IF(CONVERT(DATE, @START_DT, 103) > CONVERT(DATE, @END_DT, 103))
|
|
429
|
BEGIN
|
|
430
|
ROLLBACK TRANSACTION
|
|
431
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Ngày bắt đầu phải bé hơn ngày kết thúc' ErrorDesc
|
|
432
|
RETURN '-1'
|
|
433
|
END
|
|
434
|
END
|
|
435
|
|
|
436
|
EXEC SYS_CodeMasters_Gen 'REQ_PAY_AUTO_SCHEDULE_ID', @REQ_PAY_AUTO_SCHEDULE_ID OUT;
|
|
437
|
IF @REQ_PAY_AUTO_SCHEDULE_ID='' OR @REQ_PAY_AUTO_SCHEDULE_ID IS NULL GOTO ABORT;
|
|
438
|
|
|
439
|
INSERT INTO TR_REQ_PAY_AUTO_SCHEDULE(REQ_PAY_AUTO_SCHEDULE_ID, REQ_PAY_AUTO_ID, CONTRACT_ID, TYPE_PERIOD, START_DT, END_DT, PERIOD_AMT, PERIOD_NUMBER, PERIOD_PAY_DT, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, AUTH_STATUS, RECORD_STATUS, PERIOD_PAY_DAY)
|
|
440
|
VALUES (@REQ_PAY_AUTO_SCHEDULE_ID, @p_REQ_PAY_AUTO_ID, @CONTRACT_ID, @TYPE_PERIOD, CONVERT(DATE,@START_DT,103), CONVERT(DATE,@END_DT,103), @PERIOD_AMT, @PERIOD_NUMBER, CONVERT(DATE,@PERIOD_PAY_DT,103), @p_MAKER_ID, GETDATE(), NULL, NULL, 'A', '1', @PERIOD_PAY_DAY)
|
|
441
|
|
|
442
|
IF @@error<>0 GOTO ABORT;
|
|
443
|
FETCH NEXT FROM XmlDataSchedule INTO @REQ_PAY_AUTO_SCHEDULE_ID, @CONTRACT_ID, @TYPE_PERIOD, @START_DT, @END_DT, @PERIOD_AMT, @PERIOD_NUMBER , @PERIOD_PAY_DT, @PERIOD_PAY_DAY
|
|
444
|
END
|
|
445
|
CLOSE XmlDataSchedule;
|
|
446
|
DEALLOCATE XmlDataSchedule;
|
|
447
|
|
|
448
|
--- INSERT CHI TIET LICH THANH TOAN
|
|
449
|
DELETE FROM TR_REQ_PAY_AUTO_SCHEDULE_DETAIL WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID --AND PAY_AUTO_STATUS <> 'Y'
|
|
450
|
|
|
451
|
DECLARE @hDocScheduleDetail INT
|
|
452
|
EXEC sp_xml_preparedocument @hDocScheduleDetail OUTPUT, @p_XMP_SCHEDULE_DETAIL;
|
|
453
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
454
|
BEGIN
|
|
455
|
IF(@p_REQ_PAY_AUTO_TYPE = 'A' AND NOT EXISTS(SELECT * FROM OPENXML(@hDocScheduleDetail, '/Root/XmlDataScheduleDetail', 2) WITH(REQ_PAY_AUTO_ID varchar(20))))
|
|
456
|
BEGIN
|
|
457
|
ROLLBACK TRANSACTION
|
|
458
|
SELECT '-1' Result, '' REQ_PAY_ID, N'Danh sách lịch thanh toán chi tiết không được phép để trống' ErrorDesc
|
|
459
|
RETURN '-1'
|
|
460
|
END
|
|
461
|
END
|
|
462
|
|
|
463
|
DECLARE @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID VARCHAR(20), @EXPECTED_DT VARCHAR(20), @PAY_AUTO_AMT DECIMAL(18,2), @PAY_AUTO_DESC NVARCHAR(1000), @PAY_AUTO_STATUS VARCHAR(20), @EXACT_DT VARCHAR(20), @END_PERIOD_DT VARCHAR(20), @NOTES NVARCHAR(1000)
|
|
464
|
|
|
465
|
DECLARE XmlDataScheduleDetail CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocScheduleDetail, 'Root/XmlDataScheduleDetail',2)
|
|
466
|
WITH(REQ_PAY_AUTO_SCHEDULE_DETAIL_ID VARCHAR(20),REQ_PAY_AUTO_SCHEDULE_ID VARCHAR(20),EXPECTED_DT VARCHAR(20),PAY_AUTO_AMT DECIMAL(18,2),PAY_AUTO_DESC NVARCHAR(1000),PAY_AUTO_STATUS VARCHAR(20), EXACT_DT VARCHAR(20), START_DT VARCHAR(20), END_DT VARCHAR(20), TYPE_PERIOD VARCHAR(20), END_PERIOD_DT VARCHAR(20), NOTES NVARCHAR(255))
|
|
467
|
OPEN XmlDataScheduleDetail
|
|
468
|
|
|
469
|
DECLARE @INDEX_SCHEDULE_DETAIL INT = 0
|
|
470
|
FETCH NEXT FROM XmlDataScheduleDetail INTO @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, @REQ_PAY_AUTO_SCHEDULE_ID, @EXPECTED_DT, @PAY_AUTO_AMT, @PAY_AUTO_DESC, @PAY_AUTO_STATUS, @EXACT_DT, @START_DT, @END_DT, @TYPE_PERIOD, @END_PERIOD_DT, @NOTES
|
|
471
|
WHILE @@fetch_status=0
|
|
472
|
BEGIN
|
|
473
|
-- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE
|
|
474
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
475
|
BEGIN
|
|
476
|
SET @INDEX_SCHEDULE_DETAIL = @INDEX_SCHEDULE_DETAIL + 1
|
|
477
|
|
|
478
|
IF(@EXPECTED_DT IS NULL OR @EXPECTED_DT = '')
|
|
479
|
BEGIN
|
|
480
|
ROLLBACK TRANSACTION
|
|
481
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Ngày dự kiến thanh toán không được để trống' ErrorDesc
|
|
482
|
RETURN '-1'
|
|
483
|
END
|
|
484
|
IF(@PAY_AUTO_AMT IS NULL OR @PAY_AUTO_AMT < 0)
|
|
485
|
BEGIN
|
|
486
|
ROLLBACK TRANSACTION
|
|
487
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Số tiền thanh toán không được để trống' ErrorDesc
|
|
488
|
RETURN '-1'
|
|
489
|
END
|
|
490
|
/*
|
|
491
|
IF(@PAY_AUTO_DESC IS NULL OR @PAY_AUTO_DESC = '')
|
|
492
|
BEGIN
|
|
493
|
ROLLBACK TRANSACTION
|
|
494
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Nội dung thanh toán không được để trống' ErrorDesc
|
|
495
|
RETURN '-1'
|
|
496
|
END
|
|
497
|
*/
|
|
498
|
IF(@PAY_AUTO_STATUS IS NULL OR @PAY_AUTO_STATUS = '')
|
|
499
|
BEGIN
|
|
500
|
ROLLBACK TRANSACTION
|
|
501
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin định kỳ thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_SCHEDULE)+N': Tình trạng không được để trống' ErrorDesc
|
|
502
|
RETURN '-1'
|
|
503
|
END
|
|
504
|
END
|
|
505
|
|
|
506
|
EXEC SYS_CodeMasters_Gen 'REQ_PAY_AUTO_SCHEDULE_DETAIL_ID', @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID OUT;
|
|
507
|
IF @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID='' OR @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID IS NULL GOTO ABORT;
|
|
508
|
|
|
509
|
INSERT INTO TR_REQ_PAY_AUTO_SCHEDULE_DETAIL(REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, REQ_PAY_AUTO_SCHEDULE_ID, REQ_PAY_AUTO_ID, EXPECTED_DT, PAY_AUTO_AMT, PAY_AUTO_DESC, PAY_AUTO_STATUS, EXACT_DT, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, AUTH_STATUS, RECORD_STATUS, START_DT, END_DT, TYPE_PERIOD, END_PERIOD_DT, NOTES)
|
|
510
|
VALUES (@REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, @REQ_PAY_AUTO_SCHEDULE_ID, @p_REQ_PAY_AUTO_ID, CONVERT(DATE,@EXPECTED_DT,103), @PAY_AUTO_AMT, @PAY_AUTO_DESC, @PAY_AUTO_STATUS, CONVERT(DATE,@EXACT_DT,103), @p_MAKER_ID, GETDATE(), NULL, NULL, 'A', '1', CONVERT(DATE,@START_DT,103), CONVERT(DATE,@END_DT,103), @TYPE_PERIOD, CONVERT(DATE,@END_PERIOD_DT,103), @NOTES)
|
|
511
|
|
|
512
|
IF @@error<>0 GOTO ABORT;
|
|
513
|
FETCH NEXT FROM XmlDataScheduleDetail INTO @REQ_PAY_AUTO_SCHEDULE_DETAIL_ID, @REQ_PAY_AUTO_SCHEDULE_ID, @EXPECTED_DT, @PAY_AUTO_AMT, @PAY_AUTO_DESC, @PAY_AUTO_STATUS, @EXACT_DT, @START_DT, @END_DT, @TYPE_PERIOD, @END_PERIOD_DT, @NOTES
|
|
514
|
END
|
|
515
|
CLOSE XmlDataScheduleDetail;
|
|
516
|
DEALLOCATE XmlDataScheduleDetail;
|
|
517
|
|
|
518
|
--- INSERT PHUONG THUC THANH TOAN
|
|
519
|
DELETE FROM TR_REQ_PAY_AUTO_METHOD WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
|
|
520
|
print '121223'
|
|
521
|
DECLARE @hDocMeThod INT
|
|
522
|
EXEC sp_xml_preparedocument @hDocMeThod OUTPUT, @p_XMP_METHOD;
|
|
523
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
524
|
BEGIN
|
|
525
|
IF(@p_REQ_PAY_AUTO_TYPE = 'A' AND NOT EXISTS(SELECT * FROM OPENXML(@hDocMeThod, '/Root/XmlDataMethod', 2) WITH(REQ_PAY_AUTO_ID varchar(20))))
|
|
526
|
BEGIN
|
|
527
|
ROLLBACK TRANSACTION
|
|
528
|
SELECT '-1' Result, '' REQ_PAY_ID, N'Danh sách phương thức thanh toán không được phép để trống' ErrorDesc
|
|
529
|
RETURN '-1'
|
|
530
|
END
|
|
531
|
END
|
|
532
|
|
|
533
|
DECLARE @RECEIVE_ID VARCHAR(15), @RECEIVE_NAME NVARCHAR(100), @REQ_PAY_REASON NVARCHAR(MAX), @TOTAL_AMT DECIMAL(18,2),
|
|
534
|
@REQ_PAY_TYPE VARCHAR(1), @REQ_PAY_DESC NVARCHAR(MAX), @REQ_PAY_ENTRIES NVARCHAR(MAX), @ACC_NO VARCHAR(25), @ACC_NAME NVARCHAR(250),
|
|
535
|
@ISSUED_BY NVARCHAR(250), @ISSUED_DT VARCHAR(20), @CURRENCY VARCHAR(15), @RATE DECIMAL(18,2), @CHECK_IN VARCHAR(15), @TYPE_TRANSFER VARCHAR(15),
|
|
536
|
@BANKCODE_NAPAS VARCHAR(20), @BANKCODE VARCHAR(20), @BANKNAME VARCHAR(250), @SUB_ISSUED_BY NVARCHAR(250), @RECEIVER_DEBIT VARCHAR(20)
|
|
537
|
|
|
538
|
DECLARE XmlDataMethod CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocMeThod, 'Root/XmlDataMethod',2)
|
|
539
|
WITH(RECEIVE_ID VARCHAR(15),RECEIVE_NAME NVARCHAR(100),REQ_PAY_REASON NVARCHAR(MAX),TOTAL_AMT DECIMAL(18,2),
|
|
540
|
REQ_PAY_TYPE VARCHAR(1),REQ_PAY_DESC NVARCHAR(MAX),REQ_PAY_ENTRIES NVARCHAR(MAX),ACC_NO VARCHAR(25),
|
|
541
|
ACC_NAME NVARCHAR(250),ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),CHECK_IN VARCHAR(15),TYPE_TRANSFER VARCHAR(15),
|
|
542
|
BANKCODE_NAPAS VARCHAR(20), BANKCODE VARCHAR(20), BANKNAME VARCHAR(250), SUB_ISSUED_BY NVARCHAR(250), RECEIVER_DEBIT VARCHAR(20))
|
|
543
|
OPEN XmlDataMethod
|
|
544
|
|
|
545
|
DECLARE @INDEX_METHOD INT = 0
|
|
546
|
FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT,@REQ_PAY_TYPE,
|
|
547
|
@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO, @ACC_NAME,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT
|
|
548
|
WHILE @@fetch_status=0
|
|
549
|
BEGIN
|
|
550
|
-- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE
|
|
551
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
552
|
BEGIN
|
|
553
|
SET @INDEX_METHOD = @INDEX_METHOD + 1
|
|
554
|
----------------------- begin validate ------------------------
|
|
555
|
IF(@ACC_NO IS NULL OR @ACC_NO = '')
|
|
556
|
BEGIN
|
|
557
|
ROLLBACK TRANSACTION
|
|
558
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_METHOD)+N': số tài khoản '+ N' không được để trống' ErrorDesc
|
|
559
|
RETURN '-1'
|
|
560
|
END
|
|
561
|
|
|
562
|
IF(@REQ_PAY_TYPE = '1' AND (@ISSUED_DT = '' OR @ISSUED_DT IS NULL))
|
|
563
|
BEGIN
|
|
564
|
ROLLBACK TRANSACTION
|
|
565
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_METHOD)+N': Ngày cấp CMND '+ N' không được để trống nếu phương thức thanh toán là bằng tiền mặt' ErrorDesc
|
|
566
|
RETURN '-1'
|
|
567
|
END
|
|
568
|
|
|
569
|
IF(@TOTAL_AMT IS NULL OR @TOTAL_AMT < 0)
|
|
570
|
BEGIN
|
|
571
|
ROLLBACK TRANSACTION
|
|
572
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX_METHOD)+N': Số tiền thanh toán không được để trống hoặc bé hơn 0' ErrorDesc
|
|
573
|
RETURN '-1'
|
|
574
|
END
|
|
575
|
END
|
|
576
|
|
|
577
|
IF(@REQ_PAY_TYPE<>'1')
|
|
578
|
BEGIN
|
|
579
|
SET @ISSUED_DT = NULL
|
|
580
|
END
|
|
581
|
IF(@TYPE_TRANSFER IS NULL OR @TYPE_TRANSFER ='')
|
|
582
|
BEGIN
|
|
583
|
SET @TYPE_TRANSFER ='A'
|
|
584
|
END
|
|
585
|
|
|
586
|
DECLARE @p_REQ_PAY_METHOD_ID VARCHAR(15);
|
|
587
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_METHOD', @p_REQ_PAY_METHOD_ID OUT;
|
|
588
|
IF @p_REQ_PAY_METHOD_ID='' OR @p_REQ_PAY_METHOD_ID IS NULL GOTO ABORT;
|
|
589
|
|
|
590
|
INSERT INTO TR_REQ_PAY_AUTO_METHOD(METHOD_ID, REQ_PAY_AUTO_ID, EMP_ID, REQ_PAY_REASON, TOTAL_AMT, REQ_PAY_TYPE, REQ_PAY_DESC, REQ_PAY_ENTRIES, TEMP, MAKER_ID, CREATE_DT, ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT, CURRENCY, RATE, CHECK_IN, TYPE_TRANSFER, BANKCODE_NAPAS, BANKCODE, BANKNAME, SUB_ISSUED_BY, RECEIVER_DEBIT)
|
|
591
|
VALUES (@p_REQ_PAY_METHOD_ID,@p_REQ_PAY_AUTO_ID,@RECEIVE_ID,@REQ_PAY_REASON, @TOTAL_AMT,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,'',@p_MAKER_ID,GETDATE(),@ACC_NO, @ACC_NAME,@ISSUED_BY,CONVERT(DATE,@ISSUED_DT,103), 'VND' ,'1',@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME,@SUB_ISSUED_BY, @RECEIVER_DEBIT )
|
|
592
|
|
|
593
|
IF @@error<>0 GOTO ABORT;
|
|
594
|
FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT,@REQ_PAY_TYPE,
|
|
595
|
@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO, @ACC_NAME,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT
|
|
596
|
END
|
|
597
|
CLOSE XmlDataMethod;
|
|
598
|
DEALLOCATE XmlDataMethod;
|
|
599
|
|
|
600
|
--- INSERT THÔNG TIN DỊCH VỤ
|
|
601
|
DECLARE @hDocRecurring INT
|
|
602
|
EXEC sp_xml_preparedocument @hDocRecurring OUTPUT, @p_XMP_RECURRING;
|
|
603
|
|
|
604
|
IF(@p_IS_SEND_APPR = 'SEND')
|
|
605
|
BEGIN
|
|
606
|
IF(@p_REQ_PAY_AUTO_TYPE ='B')
|
|
607
|
BEGIN
|
|
608
|
IF(NOT EXISTS(SELECT * FROM OPENXML(@hDocRecurring, '/Root/XmlDataManufacturer', 2) WITH(REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20))))
|
|
609
|
BEGIN
|
|
610
|
ROLLBACK TRANSACTION
|
|
611
|
SELECT '-1' Result, '' REQ_PAY_ID, N'Lưới Thông tin dịch vụ không được để trống' ErrorDesc
|
|
612
|
RETURN '-1'
|
|
613
|
END
|
|
614
|
END
|
|
615
|
END
|
|
616
|
|
|
617
|
DECLARE @REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), @SEVCODE VARCHAR(20), @SEVNAME NVARCHAR(255), @PRVCODE VARCHAR(20), @PRVNAME NVARCHAR(255), @ROUTETO VARCHAR(20), @TRANSACCOUNT VARCHAR(20), @TRANSACCOUNT_NAME VARCHAR(250),
|
|
618
|
@TRANS_GLCODE VARCHAR(20), @TRANS_GLNAME VARCHAR(250), @RECEIVE_GLCODE VARCHAR(20), @RECEIVE_GLNAME VARCHAR(250),
|
|
619
|
@CUSTOMER_ID VARCHAR(20), @CUSTOMER_CODE VARCHAR(20), @CUSTOMER_NAME NVARCHAR(255), @CUSTOMER_CODE_SYSTEM VARCHAR(20), @CUSTOMER_NAME_SYSTEM NVARCHAR(255), @CUSTOMER_ADDRESS_SYSTEM NVARCHAR(255), @IS_REGISTER VARCHAR(2), @IS_VALID VARCHAR(2),
|
|
620
|
@BILLCODE VARCHAR(50), @BILLALIAS NVARCHAR(255), @BILLID VARCHAR(20), @BILLTIME VARCHAR(20), @IS_CHECKED VARCHAR(1)
|
|
621
|
|
|
622
|
DECLARE XmlDataRecurring CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDocRecurring, 'Root/XmlDataManufacturer',2)
|
|
623
|
WITH(REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), SEVCODE VARCHAR(20), SEVNAME NVARCHAR(255), PRVCODE VARCHAR(20), PRVNAME NVARCHAR(255), ROUTETO VARCHAR(20), TRANSACCOUNT VARCHAR(20), TRANSACCOUNT_NAME VARCHAR(250), TRANS_GLCODE VARCHAR(20), TRANS_GLNAME VARCHAR(250),
|
|
624
|
RECEIVE_GLCODE VARCHAR(20), RECEIVE_GLNAME VARCHAR(250), CUSTOMER_ID VARCHAR(20), CUSTOMER_CODE VARCHAR(20), CUSTOMER_NAME NVARCHAR(255), CUSTOMER_CODE_SYSTEM VARCHAR(20), CUSTOMER_NAME_SYSTEM NVARCHAR(255), CUSTOMER_ADDRESS_SYSTEM NVARCHAR(255),
|
|
625
|
IS_REGISTER VARCHAR(2), IS_VALID VARCHAR(2), BILLCODE VARCHAR(50), BILLALIAS NVARCHAR(255), BILLID VARCHAR(20), BILLTIME VARCHAR(20), IS_CHECKED VARCHAR(1))
|
|
626
|
OPEN XmlDataRecurring
|
|
627
|
|
|
628
|
DELETE FROM TR_REQ_PAY_AUTO_RECURRING WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
|
|
629
|
DECLARE @INDEX_AUTO_RECURRING INT = 0
|
|
630
|
FETCH NEXT FROM XmlDataRecurring INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @SEVCODE, @SEVNAME, @PRVCODE, @PRVNAME, @ROUTETO, @TRANSACCOUNT, @TRANSACCOUNT_NAME, @TRANS_GLCODE, @TRANS_GLNAME, @RECEIVE_GLCODE, @RECEIVE_GLNAME,
|
|
631
|
@CUSTOMER_ID, @CUSTOMER_CODE, @CUSTOMER_NAME, @CUSTOMER_CODE_SYSTEM, @CUSTOMER_NAME_SYSTEM, @CUSTOMER_ADDRESS_SYSTEM, @IS_REGISTER, @IS_VALID, @BILLCODE, @BILLALIAS, @BILLID, @BILLTIME, @IS_CHECKED
|
|
632
|
WHILE @@fetch_status=0
|
|
633
|
BEGIN
|
|
634
|
-- BEGIN VALIDATE
|
|
635
|
IF(@p_IS_SEND_APPR ='SEND')
|
|
636
|
BEGIN
|
|
637
|
SET @INDEX_AUTO_RECURRING = @INDEX_AUTO_RECURRING + 1
|
|
638
|
|
|
639
|
IF(ISNULL(@IS_CHECKED, '') = '')
|
|
640
|
BEGIN
|
|
641
|
ROLLBACK TRANSACTION
|
|
642
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin dịch vụ, dòng ' + CONVERT(VARCHAR(5),@INDEX_AUTO_RECURRING)+N', cột kiểm tra thông tin: Chưa nhấn nút kiểm tra thông tin! Bạn cần kiểm tra thông tin trước khi gửi phê duyệt' ErrorDesc
|
|
643
|
RETURN '-1'
|
|
644
|
END
|
|
645
|
IF(ISNULL(@CUSTOMER_CODE_SYSTEM, '') = '')
|
|
646
|
BEGIN
|
|
647
|
ROLLBACK TRANSACTION
|
|
648
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin dịch vụ, dòng ' + CONVERT(VARCHAR(5),@INDEX_AUTO_RECURRING)+N', Mã khách hàng tại NCC sai, hoặc quá trình kiểm tra thông tin gặp sự cố! Vui lòng kiểm tra lại mã khách hàng tại NCC trước khi gửi phê duyệt' ErrorDesc
|
|
649
|
RETURN '-1'
|
|
650
|
END
|
|
651
|
IF(ISNULL(@IS_REGISTER, '') = '1')
|
|
652
|
BEGIN
|
|
653
|
ROLLBACK TRANSACTION
|
|
654
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thông tin dịch vụ, dòng ' + CONVERT(VARCHAR(5),@INDEX_AUTO_RECURRING)+N', Mã khách hàng tại NCC đã được đăng ký thanh toán tự động trước đó.' ErrorDesc
|
|
655
|
RETURN '-1'
|
|
656
|
END
|
|
657
|
END
|
|
658
|
-- END VALIDATE
|
|
659
|
|
|
660
|
DECLARE @REQ_PAY_AUTO_RECURRING_ID VARCHAR(20)
|
|
661
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_AUTO_RECURRING', @REQ_PAY_AUTO_RECURRING_ID OUT;
|
|
662
|
IF @REQ_PAY_AUTO_RECURRING_ID = '' OR @REQ_PAY_AUTO_RECURRING_ID IS NULL GOTO ABORT;
|
|
663
|
|
|
664
|
INSERT INTO TR_REQ_PAY_AUTO_RECURRING(REQ_PAY_AUTO_RECURRING_ID, REQ_PAY_AUTO_MANUFACTURER_ID, REQ_PAY_AUTO_ID, SEVCODE, SEVNAME, PRVCODE, PRVNAME, ROUTETO, TRANSACCOUNT, TRANSACCOUNT_NAME, TRANS_GLCODE, TRANS_GLNAME, RECEIVE_GLCODE, RECEIVE_GLNAME,
|
|
665
|
CUSTOMER_CODE, CUSTOMER_NAME, CUSTOMER_CODE_SYSTEM, CUSTOMER_NAME_SYSTEM, CUSTOMER_ADDRESS_SYSTEM, IS_REGISTER, IS_VALID, MAKER_ID, CREATE_DT, RECORD_STATUS, BILLCODE, BILLALIAS, BILLID, BILLTIME, IS_CHECKED)
|
|
666
|
VALUES (@REQ_PAY_AUTO_RECURRING_ID, @REQ_PAY_AUTO_MANUFACTURER_ID, @p_REQ_PAY_AUTO_ID, @SEVCODE, @SEVNAME, @PRVCODE, @PRVNAME, @ROUTETO, @TRANSACCOUNT, @TRANSACCOUNT_NAME, @TRANS_GLCODE, @TRANS_GLNAME, @RECEIVE_GLCODE, @RECEIVE_GLNAME,
|
|
667
|
@CUSTOMER_CODE, @CUSTOMER_NAME, @CUSTOMER_CODE_SYSTEM, @CUSTOMER_NAME_SYSTEM, @CUSTOMER_ADDRESS_SYSTEM, @IS_REGISTER, @IS_VALID, @p_MAKER_ID, GETDATE(), '1', @BILLCODE, @BILLALIAS, @BILLID, CONVERT(DATE, @BILLTIME, 103), @IS_CHECKED)
|
|
668
|
|
|
669
|
IF @@error<>0 GOTO ABORT;
|
|
670
|
FETCH NEXT FROM XmlDataRecurring INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @SEVCODE, @SEVNAME, @PRVCODE, @PRVNAME, @ROUTETO, @TRANSACCOUNT, @TRANSACCOUNT_NAME, @TRANS_GLCODE, @TRANS_GLNAME, @RECEIVE_GLCODE, @RECEIVE_GLNAME,
|
|
671
|
@CUSTOMER_ID, @CUSTOMER_CODE, @CUSTOMER_NAME, @CUSTOMER_CODE_SYSTEM, @CUSTOMER_NAME_SYSTEM, @CUSTOMER_ADDRESS_SYSTEM, @IS_REGISTER, @IS_VALID, @BILLCODE, @BILLALIAS, @BILLID, @BILLTIME, @IS_CHECKED
|
|
672
|
END
|
|
673
|
CLOSE XmlDataRecurring;
|
|
674
|
DEALLOCATE XmlDataRecurring;
|
|
675
|
|
|
676
|
--- INSERT PHÂN CHIA
|
|
677
|
DECLARE @hDocBudgetAllocation INT
|
|
678
|
EXEC sp_xml_preparedocument @hDocBudgetAllocation OUTPUT, @p_XMP_BUDGET_ALLOCATION;
|
|
679
|
|
|
680
|
DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID NVARCHAR(255), @TYPE_BUDGET_ALLOCAITON VARCHAR(20), @PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), @AMT_BUDGET_ALLOCAITON DECIMAL(18,2)
|
|
681
|
|
|
682
|
DECLARE XmlDataBudgetAllocation CURSOR FOR SELECT * FROM OPENXML(@hDocBudgetAllocation, 'Root/XmlDataBudgetAllocation',2)
|
|
683
|
WITH(REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), ACC_NO VARCHAR(20), ACC_NAME NVARCHAR(255), BRANCH_ID VARCHAR(20), DEP_ID NVARCHAR(255), TYPE_BUDGET_ALLOCAITON VARCHAR(20), PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), AMT_BUDGET_ALLOCAITON DECIMAL(18,2))
|
|
684
|
OPEN XmlDataBudgetAllocation
|
|
685
|
|
|
686
|
DELETE FROM TR_REQ_PAY_AUTO_BUDGET_ALLOCATION WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
|
|
687
|
FETCH NEXT FROM XmlDataBudgetAllocation INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON
|
|
688
|
WHILE @@fetch_status=0
|
|
689
|
BEGIN
|
|
690
|
DECLARE @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID VARCHAR(20)
|
|
691
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_AUTO_ALLCOTION', @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID OUT;
|
|
692
|
IF @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID = '' OR @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID IS NULL GOTO ABORT;
|
|
693
|
|
|
694
|
INSERT INTO TR_REQ_PAY_AUTO_BUDGET_ALLOCATION(REQ_PAY_AUTO_BUDGET_ALLOCATION_ID, REQ_PAY_AUTO_MANUFACTURER_ID, REQ_PAY_AUTO_ID, ACC_NO, ACC_NAME, BRANCH_ID, DEP_ID, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON, MAKER_ID, CREATE_DT, RECORD_STATUS)
|
|
695
|
VALUES (@REQ_PAY_AUTO_BUDGET_ALLOCATION_ID, @REQ_PAY_AUTO_MANUFACTURER_ID, @p_REQ_PAY_AUTO_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON, @p_MAKER_ID, GETDATE(), '1')
|
|
696
|
|
|
697
|
IF @@error<>0 GOTO ABORT;
|
|
698
|
FETCH NEXT FROM XmlDataBudgetAllocation INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON
|
|
699
|
END
|
|
700
|
CLOSE XmlDataBudgetAllocation;
|
|
701
|
DEALLOCATE XmlDataBudgetAllocation;
|
|
702
|
|
|
703
|
COMMIT TRANSACTION
|
|
704
|
|
|
705
|
IF(@p_IS_SEND_APPR ='SEND') -- KIEM TRA NEU 2 BIEN XML KHAC NULL TUC LA DANG TRONG QUA TRINH LUU NHAP
|
|
706
|
BEGIN
|
|
707
|
UPDATE TR_REQ_PAYMENT_AUTO SET AUTH_STATUS = 'U', PROCESS = '0' WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
|
|
708
|
-- INSERT VAO PL_PROCESS
|
|
709
|
INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
|
|
710
|
VALUES(@p_REQ_PAY_AUTO_ID,'SEND',@p_MAKER_ID,GETDATE(), N'Người tạo gửi phê duyệt',N'Gửi phê duyệt cấp đơn vị')
|
|
711
|
|
|
712
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE (TRANSFER_USER_RECEIVE <> '' AND TRANSFER_USER_RECEIVE IS NOT NULL) AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
|
|
713
|
BEGIN
|
|
714
|
SELECT '1' as Result, @p_REQ_PAY_AUTO_ID REQ_PAY_ID, N'Đơn vị gửi phiếu yêu cầu thanh toán tự động số: ' + ISNULL(@p_REQ_PAY_AUTO_CODE,'') + N' thành công. Vui lòng đợi cấp phê duyệt trung gian xác nhận phiếu' ErrorDesc
|
|
715
|
RETURN '1'
|
|
716
|
END
|
|
717
|
ELSE
|
|
718
|
BEGIN
|
|
719
|
SELECT '2' as Result, @p_REQ_PAY_AUTO_ID REQ_PAY_ID, N'Đơn vị gửi phiếu yêu cầu thanh toán tự động số: ' + ISNULL(@p_REQ_PAY_AUTO_CODE,'') + N' thành công. Vui lòng đợi trưởng đơn vị phê duyệt phiếu' ErrorDesc
|
|
720
|
RETURN '2'
|
|
721
|
END
|
|
722
|
END
|
|
723
|
|
|
724
|
SELECT '0' as Result, @p_REQ_PAY_AUTO_ID REQ_PAY_AUTO_ID, @p_REQ_PAY_AUTO_CODE AS REQ_PAY_AUTO_CODE, N'Thêm mới thành công' ErrorDesc
|
|
725
|
RETURN '0'
|
|
726
|
|
|
727
|
ABORT:
|
|
728
|
BEGIN
|
|
729
|
ROLLBACK TRANSACTION
|
|
730
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Lỗi không xác định' ErrorDesc
|
|
731
|
RETURN '-1'
|
|
732
|
End
|