1
|
ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_Appr]
|
2
|
--Luanlt 2019/17/10 - Sửa params
|
3
|
@p_REQ_PAY_ID varchar(15)= NULL,
|
4
|
@p_CHECKER_ID varchar(15) = NULL
|
5
|
AS
|
6
|
|
7
|
-- BEGIN VALIDATE
|
8
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE MAKER_ID =@p_CHECKER_ID AND REQ_PAY_ID = @p_REQ_PAY_ID)
|
9
|
BEGIN
|
10
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị thanh toán ứng này! Người phê duyệt phiếu phải khác với người tạo phiếu' ErrorDesc
|
11
|
RETURN '-1'
|
12
|
END
|
13
|
IF ( EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE ISNULL(TRASFER_USER_RECIVE, '') <> '' AND TRASFER_USER_RECIVE = @p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
14
|
BEGIN
|
15
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị thanh toán ứng này! Người phê duyệt phiếu phải khác với người phê duyệt trung gian' ErrorDesc
|
16
|
RETURN '-1'
|
17
|
END
|
18
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
19
|
BEGIN
|
20
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phê duyệt phiếu đề nghị thanh toán thất bại! 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 phê duyệt trước đó' ErrorDesc
|
21
|
RETURN '-1'
|
22
|
END
|
23
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
24
|
BEGIN
|
25
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
|
26
|
RETURN '-1'
|
27
|
END
|
28
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
29
|
BEGIN
|
30
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được kiểm soát viên phê duyệt trước đó' ErrorDesc
|
31
|
RETURN '-1'
|
32
|
END
|
33
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
34
|
BEGIN
|
35
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về giao dịch viên. Vui lòng kiểm tra lại thông tin' ErrorDesc
|
36
|
RETURN '-1'
|
37
|
END
|
38
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='S' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
39
|
BEGIN
|
40
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được đề xuất từ chối. Vui lòng kiểm tra lại thông tin' ErrorDesc
|
41
|
RETURN '-1'
|
42
|
END
|
43
|
-- CHAN CUOI
|
44
|
IF (EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'U' AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
45
|
BEGIN
|
46
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' chưa được gửi phê duyệt. Vui lòng đợi người tạo gửi phê duyệt trước khi phê duyệt phiếu' ErrorDesc
|
47
|
RETURN '-1'
|
48
|
END
|
49
|
-- FETCH DATA TU TABLE
|
50
|
Declare @t_BUDG_ID varchar(15)
|
51
|
Declare @t_GD_ID varchar(15)
|
52
|
Declare @t_REQ_PAY_ID varchar(15)
|
53
|
Declare @t_TRADE_ID varchar(15)
|
54
|
Declare @t_AMT_APP decimal
|
55
|
Declare @t_AMT_EXE decimal
|
56
|
Declare @t_AMT_REMAIN decimal
|
57
|
Declare @t_MAKER_ID varchar(15)
|
58
|
Declare @t_CREATE_DT varchar(50)
|
59
|
Declare @t_CURRENCY varchar(15)
|
60
|
Declare @t_RATE decimal
|
61
|
Declare @t_AMT_DO decimal
|
62
|
Declare @t_REASON nvarchar(1000)
|
63
|
Declare @t_NOTES nvarchar(1000)
|
64
|
Declare @t_TYPE_COST varchar(15)
|
65
|
Declare @t_FR_LEVEL int
|
66
|
Declare @t_TO_LEVEL int
|
67
|
Declare @t_MONTH_RATE varchar(4)
|
68
|
Declare @t_YEAR_RATE varchar(4)
|
69
|
Declare @t_BUDGET_TYPE varchar(20)
|
70
|
Declare @t_BRANCH_ID varchar(20)
|
71
|
Declare @t_DEP_ID varchar(20)
|
72
|
Declare @t_TLNAME varchar(20)
|
73
|
Declare @t_ROLENAME varchar(20)
|
74
|
Declare @t_BRANCH_TAKE_COST_ID varchar(20)
|
75
|
Declare @t_DEP_TAKE_COST_ID varchar(20)
|
76
|
Declare @t_KHOI_TAKE_COST_ID varchar(20)
|
77
|
Declare @t_BRANCH_KIND varchar(20)
|
78
|
DECLARE reader_cursor CURSOR FOR
|
79
|
SELECT [BUDG_ID], [GD_ID], [REQ_PAY_ID], [TRADE_ID], [AMT_APP], [AMT_EXE], [AMT_REMAIN], [MAKER_ID], [CREATE_DT], [CURRENCY], [RATE], [AMT_DO], [REASON], [NOTES], [TYPE_COST], [FR_LEVEL], [TO_LEVEL], [MONTH_RATE], [YEAR_RATE], [BUDGET_TYPE], [BRANCH_ID], [DEP_ID], [TLNAME], [ROLENAME], [BRANCH_TAKE_COST_ID], [DEP_TAKE_COST_ID], [KHOI_TAKE_COST_ID], [BRANCH_KIND]
|
80
|
FROM TR_REQ_PAY_BUDGET
|
81
|
WHERE REQ_PAY_ID = @p_REQ_PAY_ID
|
82
|
OPEN reader_cursor;
|
83
|
FETCH NEXT FROM reader_cursor INTO @t_BUDG_ID, @t_GD_ID, @t_REQ_PAY_ID, @t_TRADE_ID, @t_AMT_APP, @t_AMT_EXE, @t_AMT_REMAIN, @t_MAKER_ID, @t_CREATE_DT, @t_CURRENCY, @t_RATE, @t_AMT_DO, @t_REASON, @t_NOTES, @t_TYPE_COST, @t_FR_LEVEL, @t_TO_LEVEL, @t_MONTH_RATE, @t_YEAR_RATE, @t_BUDGET_TYPE, @t_BRANCH_ID, @t_DEP_ID, @t_TLNAME, @t_ROLENAME, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_KHOI_TAKE_COST_ID, @t_BRANCH_KIND
|
84
|
WHILE @@FETCH_STATUS = 0
|
85
|
BEGIN
|
86
|
DECLARE @TenCot NVARCHAR(128) = @t_MONTH_RATE; -- Tham số tên cột
|
87
|
DECLARE @GiaTriMoi DECIMAL = @t_AMT_EXE; -- Tham số giá trị mới
|
88
|
DECLARE @DieuKien NVARCHAR(MAX) = 'BUDG_ID = ' + '''' + @t_BUDG_ID + ''''; -- Tham số điều kiện WHERE
|
89
|
|
90
|
IF(ISNULL(@t_BUDGET_TYPE, '') = 'thang')
|
91
|
BEGIN
|
92
|
IF (NOT EXISTS (SELECT * FROM TR_REQ_BUDGET_MONTH_LIMIT_DETAIL WHERE BUDG_ID = @t_BUDG_ID))
|
93
|
BEGIN
|
94
|
DECLARE @l_BUDGET_LIMIT_MONTH_DETAIL_ID VARCHAR(15);
|
95
|
EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_MONTH_DETAIL', @l_BUDGET_LIMIT_MONTH_DETAIL_ID OUT;
|
96
|
|
97
|
INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT_DETAIL(BUDGET_MONTH_LIMIT_DETAIL_ID, REQ_PAY_ID, GD_ID , BRANCH_ID,DEP_ID, BUDGET_YEAR, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID , APPROVE_DT, BRANCH_KIND,BUDG_ID, KHOI_ID, TLNAME, ROLENAME)
|
98
|
VALUES(@l_BUDGET_LIMIT_MONTH_DETAIL_ID, @p_REQ_PAY_ID, @t_GD_ID, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_YEAR_RATE, @p_CHECKER_ID, GETDATE(), '1', 'A', NULL, NULL, @t_BRANCH_KIND, @t_BUDG_ID, @t_KHOI_TAKE_COST_ID, @t_TLNAME, @t_ROLENAME)
|
99
|
|
100
|
DECLARE @sql_thang NVARCHAR(MAX);
|
101
|
SET @sql_thang = N'UPDATE TR_REQ_BUDGET_MONTH_LIMIT_DETAIL SET ' + QUOTENAME(@TenCot) + N' = @GiaTriMoi WHERE ' + @DieuKien;
|
102
|
EXEC sp_executesql @sql_thang, N'@GiaTriMoi DECIMAL', @GiaTriMoi = @GiaTriMoi;
|
103
|
END
|
104
|
END
|
105
|
|
106
|
IF(ISNULL(@t_BUDGET_TYPE, '') = 'nam')
|
107
|
BEGIN
|
108
|
IF (NOT EXISTS (SELECT * FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE BUDG_ID = @t_BUDG_ID))
|
109
|
BEGIN
|
110
|
print @TenCot
|
111
|
print @GiaTriMoi
|
112
|
print @DieuKien
|
113
|
|
114
|
DECLARE @l_BUDGET_LIMIT_YEAR_DETAIL_ID VARCHAR(15);
|
115
|
EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_YEAR_DETAIL', @l_BUDGET_LIMIT_YEAR_DETAIL_ID OUT;
|
116
|
|
117
|
INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT_DETAIL(BUDGET_YEAR_LIMIT_DETAIL_ID,REQ_PAY_ID, GD_ID , BRANCH_ID, DEP_ID, KHOI_ID, TLNAME, ROLENAME, BUDGET_YEAR, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID , APPROVE_DT, BRANCH_KIND, BUDG_ID)
|
118
|
VALUES(@l_BUDGET_LIMIT_YEAR_DETAIL_ID, @p_REQ_PAY_ID, @t_GD_ID, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_KHOI_TAKE_COST_ID, @t_TLNAME, @t_RoleName, @t_YEAR_RATE, @p_CHECKER_ID, GETDATE(), '1', 'A', NULL, NULL, @t_BRANCH_KIND, @t_BUDG_ID)
|
119
|
|
120
|
DECLARE @sql_nam NVARCHAR(MAX);
|
121
|
SET @sql_nam = N'UPDATE TR_REQ_BUDGET_YEAR_LIMIT_DETAIL SET ' + QUOTENAME(@TenCot) + N' = @GiaTriMoi WHERE ' + @DieuKien;
|
122
|
EXEC sp_executesql @sql_nam, N'@GiaTriMoi DECIMAL', @GiaTriMoi = @GiaTriMoi;
|
123
|
END
|
124
|
END
|
125
|
FETCH NEXT FROM reader_cursor INTO @t_BUDG_ID, @t_GD_ID, @t_REQ_PAY_ID, @t_TRADE_ID, @t_AMT_APP, @t_AMT_EXE, @t_AMT_REMAIN, @t_MAKER_ID, @t_CREATE_DT, @t_CURRENCY, @t_RATE, @t_AMT_DO, @t_REASON, @t_NOTES, @t_TYPE_COST, @t_FR_LEVEL, @t_TO_LEVEL, @t_MONTH_RATE, @t_YEAR_RATE, @t_BUDGET_TYPE, @t_BRANCH_ID, @t_DEP_ID, @t_TLNAME, @t_ROLENAME, @t_BRANCH_TAKE_COST_ID, @t_DEP_TAKE_COST_ID, @t_KHOI_TAKE_COST_ID, @t_BRANCH_KIND
|
126
|
END
|
127
|
CLOSE reader_cursor;
|
128
|
DEALLOCATE reader_cursor;
|
129
|
-- FETCH DATA TU TABLE
|
130
|
-- END VALIDATE
|
131
|
|
132
|
BEGIN TRANSACTION
|
133
|
DECLARE @ROLE_ID VARCHAR(15)
|
134
|
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
|
135
|
IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD'))
|
136
|
BEGIN
|
137
|
PRINT @ROLE_ID
|
138
|
END
|
139
|
ELSE
|
140
|
BEGIN
|
141
|
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID)
|
142
|
IF(@ROLE_ID IS NULL OR @ROLE_ID ='')
|
143
|
BEGIN
|
144
|
SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID))
|
145
|
END
|
146
|
END
|
147
|
|
148
|
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))
|
149
|
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@p_CHECKER_ID
|
150
|
AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
|
151
|
AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='')
|
152
|
AND RECORD_STATUS = '1'
|
153
|
|
154
|
IF(@ROLE_ID NOT IN ('TPGD','PPGD','PP','TP','GDDV','PGD','KTT','TP','PP','TPTC','TC','GDK') AND
|
155
|
NOT EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('TPGD','PPGD','PP','TP','GDDV','PGD','KTT','TP','PP','TPTC','TC','GDK')))
|
156
|
BEGIN
|
157
|
ROLLBACK TRANSACTION
|
158
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không nằm trong nhóm quyền được phép phê duyệt phiếu đề nghị thanh toán' ErrorDesc
|
159
|
RETURN '-1'
|
160
|
END
|
161
|
-- KIEM TRA NGUOI TAO PHAI KHAC NGUOI DUYET
|
162
|
IF(EXISTS(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID AND RoleName NOT IN ('GDK','GDDV','PTGD','TGD','HDQT','TBP')))
|
163
|
BEGIN
|
164
|
IF EXISTS (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE MAKER_ID = @p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
165
|
BEGIN
|
166
|
ROLLBACK TRANSACTION
|
167
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn là người tạo phiếu đề nghị thanh toán. Bạn không được phép duyệt phiếu này' ErrorDesc
|
168
|
RETURN '-1'
|
169
|
END
|
170
|
END
|
171
|
-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
|
172
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
173
|
BEGIN
|
174
|
ROLLBACK TRANSACTION
|
175
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
|
176
|
RETURN '-1'
|
177
|
END
|
178
|
-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
|
179
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
180
|
BEGIN
|
181
|
ROLLBACK TRANSACTION
|
182
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
|
183
|
RETURN '-1'
|
184
|
END
|
185
|
-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
|
186
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
187
|
BEGIN
|
188
|
ROLLBACK TRANSACTION
|
189
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được KSV phê duyệt trước đó' ErrorDesc
|
190
|
RETURN '-1'
|
191
|
END
|
192
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
193
|
BEGIN
|
194
|
ROLLBACK TRANSACTION
|
195
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được phê duyệt trước đó' ErrorDesc
|
196
|
RETURN '-1'
|
197
|
END
|
198
|
-- CHECK XEM CO CAP DUYET TRUNG GIAN HAY KHONG
|
199
|
IF((SELECT ISNULL(PROCESS, '') FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) = '')
|
200
|
BEGIN
|
201
|
DECLARE @USER_SIGN VARCHAR(15)
|
202
|
SET @USER_SIGN =(SELECT ISNULL(TRASFER_USER_RECIVE,'') FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
|
203
|
IF(@p_CHECKER_ID <> @USER_SIGN AND @USER_SIGN <> '' AND (SELECT TOP 1 PROCESS_ID FROM PL_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID ORDER BY ID DESC ) = 'SEND')
|
204
|
BEGIN
|
205
|
ROLLBACK TRANSACTION
|
206
|
SELECT '-1' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,N'Phiếu đề nghị thanh toán đang chờ cấp phê duyệt trung gian xác nhận. User domain: '+ISNULL(@USER_SIGN,'') ErrorDesc
|
207
|
RETURN '-1'
|
208
|
END
|
209
|
END
|
210
|
|
211
|
|
212
|
--END - CAP NHAT CODE TRONG QUA TRINH TEST UAT 19 11 2019
|
213
|
DECLARE @INDEX INT =0
|
214
|
DECLARE @PAY_ID VARCHAR(15),@PAY_PHASE VARCHAR(15)
|
215
|
DECLARE @REQ_TYPE VARCHAR(15),@PAY_ADVANCE_ID VARCHAR(15),@AMT_PAY DECIMAL(18,2), @AMT_USE DECIMAL(18,2), @AMT_REVERT DECIMAL(12)
|
216
|
SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
|
217
|
IF(@REQ_TYPE ='I')
|
218
|
BEGIN
|
219
|
DECLARE CURS CURSOR FOR SELECT A.PAY_ADV_ID,A.AMT_REMAIN,A.AMT_USE,A.AMT_REVERT FROM TR_REQ_PAYMENT_DT A WHERE A.PAY_ID =@p_REQ_PAY_ID
|
220
|
OPEN CURS
|
221
|
FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_USE, @AMT_REVERT
|
222
|
WHILE @@FETCH_STATUS = 0
|
223
|
BEGIN
|
224
|
SET @INDEX = @INDEX +1
|
225
|
-- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
|
226
|
IF((SELECT A.REQ_AMT - A.PAY_AMT FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_PAY_ID = @PAY_ADVANCE_ID)=0)
|
227
|
BEGIN
|
228
|
ROLLBACK TRANSACTION
|
229
|
SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Phiếu tạm ứng này đã được hoàn tạm ứng xong. Vui lòng xóa khỏi danh sách hoàn tạm ứng' ErrorDesc
|
230
|
RETURN '-1'
|
231
|
END
|
232
|
--UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT = ISNULL(PAY_AMT,0) + ISNULL(@AMT_PAY,0.00) +ISNULL(@AMT_REVERT,0.00) WHERE REQ_PAY_ID =@PAY_ADVANCE_ID
|
233
|
FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_USE, @AMT_REVERT
|
234
|
END
|
235
|
CLOSE CURS
|
236
|
DEALLOCATE CURS
|
237
|
END
|
238
|
-- NEU LOAI TAM UNG LA THANH TOAN PO
|
239
|
ELSE IF(@REQ_TYPE ='P')
|
240
|
BEGIN
|
241
|
DECLARE CURS CURSOR FOR SELECT A.PAY_ID, A.PAY_PHASE FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
242
|
OPEN CURS
|
243
|
FETCH NEXT FROM CURS INTO @PAY_ID,@PAY_PHASE
|
244
|
WHILE @@FETCH_STATUS = 0
|
245
|
BEGIN
|
246
|
SET @INDEX = @INDEX +1
|
247
|
---- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
|
248
|
--IF((SELECT MIN(A.AMT_REMAIN)FROM TR_REQ_PAY_SCHEDULE A WHERE A.PAY_ID =@PAY_ID GROUP BY A.PAY_ID)=0)
|
249
|
--BEGIN
|
250
|
-- ROLLBACK TRANSACTION
|
251
|
-- SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N':Kì '+ @PAY_PHASE+N' đã thanh toán xong' ErrorDesc
|
252
|
-- RETURN '-1'
|
253
|
--END
|
254
|
FETCH NEXT FROM CURS INTO @PAY_ID,@PAY_PHASE
|
255
|
END
|
256
|
CLOSE CURS
|
257
|
DEALLOCATE CURS
|
258
|
END
|
259
|
UPDATE TR_REQ_PAYMENT
|
260
|
SET AUTH_STATUS='A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = GETDATE(), AUTH_STATUS_KT='U'
|
261
|
WHERE REQ_PAY_ID = @p_REQ_PAY_ID
|
262
|
--UPDATE DETAIL
|
263
|
UPDATE TR_REQ_PAYMENT_DT SET AUTH_STATUS='A',CHECKER_ID =@p_CHECKER_ID,APPROVE_DT = GETDATE()
|
264
|
WHERE PAY_ID =@p_REQ_PAY_ID
|
265
|
-- INSERT VAO PL_PROCESS
|
266
|
INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
|
267
|
VALUES(@p_REQ_PAY_ID,'APP',@p_CHECKER_ID,GETDATE(), N'Trưởng đơn vị phê duyệt phiếu',N'Phê duyệt cấp đơn vị')
|
268
|
IF @@Error <> 0 GOTO ABORT
|
269
|
COMMIT TRANSACTION
|
270
|
SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,'' ErrorDesc
|
271
|
RETURN '0'
|
272
|
ABORT:
|
273
|
BEGIN
|
274
|
ROLLBACK TRANSACTION
|
275
|
SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
|
276
|
RETURN '-1'
|
277
|
END
|
278
|
GO
|
279
|
ALTER PROC [dbo].[TR_BUDGET_MONTH_DETAIL_DVKD_Import] (
|
280
|
@p_GD_ID VARCHAR(20),
|
281
|
@p_BRANCH_ID VARCHAR(20),
|
282
|
@p_BUDGET_TYPE VARCHAR(20),
|
283
|
@p_BRANCH_TYPE VARCHAR(20),
|
284
|
@p_MAKER_ID VARCHAR(20),
|
285
|
@p_BRANCH_KIND VARCHAR(20),
|
286
|
@p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL XML = NULL
|
287
|
)
|
288
|
AS
|
289
|
IF(@p_GD_ID IS NULL OR @p_GD_ID = '')
|
290
|
BEGIN
|
291
|
SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
|
292
|
RETURN '-1'
|
293
|
END
|
294
|
IF(@p_BUDGET_TYPE IS NULL OR @p_BUDGET_TYPE = '')
|
295
|
BEGIN
|
296
|
SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
|
297
|
RETURN '-1'
|
298
|
END
|
299
|
IF(@p_BRANCH_TYPE IS NULL OR @p_BRANCH_TYPE = '')
|
300
|
BEGIN
|
301
|
SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
|
302
|
RETURN '-1'
|
303
|
END
|
304
|
BEGIN TRANSACTION
|
305
|
-- Đảm bảo ID ngân sách luôn được khai báo. Mõi ID ngân sách chỉ được dùng cho tháng hoặc cho năm.
|
306
|
-- CMT LAI DO CHO PHEP THAY DOI
|
307
|
--IF(EXISTS(SELECT * FROM TR_GOODSTYPE WHERE GD_ID = @p_GD_ID AND ISNULL(BUDGET_TYPE, '') <> 'thang'))
|
308
|
--BEGIN
|
309
|
-- ROLLBACK TRANSACTION
|
310
|
-- SELECT '-1' as Result, N'Import thất bại, Mã ngân sách đang được quản lý định mức theo loại khác' AS ErrorDesc
|
311
|
-- RETURN '-1'
|
312
|
--END
|
313
|
|
314
|
DELETE FROM TR_GOODSTYPE WHERE GD_ID = @p_GD_ID
|
315
|
INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS)
|
316
|
VALUES (@p_GD_ID, 'thang', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')
|
317
|
|
318
|
DECLARE @hdoc INT
|
319
|
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL
|
320
|
DECLARE XmlBudgetLimitMonthDT CURSOR FOR
|
321
|
SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitMonthDT', 2)
|
322
|
WITH (
|
323
|
BRANCH_CODE VARCHAR(15),
|
324
|
BRANCH_NAME NVARCHAR(250),
|
325
|
BRANCH_KIND VARCHAR(20),
|
326
|
BRANCH_LEVEL NVARCHAR(50),
|
327
|
BUDGET_YEAR VARCHAR(15),
|
328
|
M1 DECIMAL(18, 0),
|
329
|
M2 DECIMAL(18, 0),
|
330
|
M3 DECIMAL(18, 0),
|
331
|
M4 DECIMAL(18, 0),
|
332
|
M5 DECIMAL(18, 0),
|
333
|
M6 DECIMAL(18, 0),
|
334
|
M7 DECIMAL(18, 0),
|
335
|
M8 DECIMAL(18, 0),
|
336
|
M9 DECIMAL(18, 0),
|
337
|
M10 DECIMAL(18, 0),
|
338
|
M11 DECIMAL(18, 0),
|
339
|
M12 DECIMAL(18, 0)
|
340
|
)
|
341
|
|
342
|
DECLARE @BRANCH_CODE VARCHAR(15),
|
343
|
@BRANCH_NAME NVARCHAR(250),
|
344
|
@BRANCH_KIND VARCHAR(20),
|
345
|
@BRANCH_LEVEL NVARCHAR(50),
|
346
|
@BUDGET_YEAR VARCHAR(15),
|
347
|
@M1 DECIMAL(18, 0),
|
348
|
@M2 DECIMAL(18, 0),
|
349
|
@M3 DECIMAL(18, 0),
|
350
|
@M4 DECIMAL(18, 0),
|
351
|
@M5 DECIMAL(18, 0),
|
352
|
@M6 DECIMAL(18, 0),
|
353
|
@M7 DECIMAL(18, 0),
|
354
|
@M8 DECIMAL(18, 0),
|
355
|
@M9 DECIMAL(18, 0),
|
356
|
@M10 DECIMAL(18, 0),
|
357
|
@M11 DECIMAL(18, 0),
|
358
|
@M12 DECIMAL(18, 0)
|
359
|
|
360
|
------------------------------------------------ validate begin ------------------------------------------------
|
361
|
|
362
|
DECLARE @ERROR_MESSAGE nvarchar(MAX)
|
363
|
DECLARE @INDEX INT
|
364
|
SET @INDEX = 2
|
365
|
|
366
|
OPEN XmlBudgetLimitMonthDT;
|
367
|
|
368
|
FETCH NEXT FROM XmlBudgetLimitMonthDT
|
369
|
INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BRANCH_LEVEL, @BUDGET_YEAR , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
370
|
WHILE @@fetch_status=0
|
371
|
BEGIN
|
372
|
SET @INDEX = @INDEX +1
|
373
|
DECLARE @l_BRANCH_ID VARCHAR(20);
|
374
|
DECLARE @l_DEP_ID VARCHAR(20);
|
375
|
|
376
|
SET @l_BRANCH_ID = (SELECT TOP 1 BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE = @BRANCH_CODE)
|
377
|
|
378
|
IF(@BRANCH_KIND IS NULL OR @BRANCH_KIND = '')
|
379
|
BEGIN
|
380
|
ROLLBACK TRANSACTION
|
381
|
CLOSE XmlBudgetLimitMonthDT;
|
382
|
DEALLOCATE XmlBudgetLimitMonthDT;
|
383
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Hình thức đơn vị không được để trống' AS ErrorDesc
|
384
|
RETURN '-1'
|
385
|
END
|
386
|
|
387
|
IF(ISNULL(@l_BRANCH_ID,'') = '')
|
388
|
BEGIN
|
389
|
ROLLBACK TRANSACTION
|
390
|
CLOSE XmlBudgetLimitMonthDT;
|
391
|
DEALLOCATE XmlBudgetLimitMonthDT;
|
392
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Đơn vị ' + @BRANCH_CODE + N' không có trong hệ thống' AS ErrorDesc
|
393
|
RETURN '-1'
|
394
|
END
|
395
|
-- NEU DA TON TAI NGAN SACH THI CHI UPDATE
|
396
|
IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_MONTH_LIMIT WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND))
|
397
|
BEGIN
|
398
|
IF(ISNULL(@M1, 0) >= 0)
|
399
|
BEGIN
|
400
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M1 = @M1, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
401
|
END
|
402
|
IF(ISNULL(@M2, 0) >= 0)
|
403
|
BEGIN
|
404
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M2 = @M2, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
405
|
END
|
406
|
IF(ISNULL(@M3, 0) >= 0)
|
407
|
BEGIN
|
408
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M3 = @M3, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
409
|
END
|
410
|
IF(ISNULL(@M4, 0) >= 0)
|
411
|
BEGIN
|
412
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M4 = @M4, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
413
|
END
|
414
|
IF(ISNULL(@M5, 0) >= 0)
|
415
|
BEGIN
|
416
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M5 = @M5, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
417
|
END
|
418
|
IF(ISNULL(@M6, 0) >= 0)
|
419
|
BEGIN
|
420
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M6 = @M6, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
421
|
END
|
422
|
IF(ISNULL(@M7, 0) >= 0)
|
423
|
BEGIN
|
424
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M7 = @M7, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
425
|
END
|
426
|
IF(ISNULL(@M8, 0) >= 0)
|
427
|
BEGIN
|
428
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M8 = @M8, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
429
|
END
|
430
|
IF(ISNULL(@M9, 0) >= 0)
|
431
|
BEGIN
|
432
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M9 = @M9, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
433
|
END
|
434
|
IF(ISNULL(@M10, 0) >= 0)
|
435
|
BEGIN
|
436
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M10 = @M10, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
437
|
END
|
438
|
IF(ISNULL(@M11, 0) >= 0)
|
439
|
BEGIN
|
440
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M11 = @M11, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
441
|
END
|
442
|
IF(ISNULL(@M12, 0) >= 0)
|
443
|
BEGIN
|
444
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M12 = @M12, BRANCH_LEVEL = @BRANCH_LEVEL WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @l_BRANCH_ID AND BUDGET_YEAR = @BUDGET_YEAR AND BRANCH_KIND = @BRANCH_KIND
|
445
|
END
|
446
|
IF @@error<>0 GOTO ABORT;
|
447
|
FETCH NEXT FROM XmlBudgetLimitMonthDT
|
448
|
INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BRANCH_LEVEL, @BUDGET_YEAR , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
449
|
END
|
450
|
ELSE
|
451
|
BEGIN
|
452
|
-- Tự động sinh mã code
|
453
|
DECLARE @BUDGET_MONTH_LIMIT_ID VARCHAR(15);
|
454
|
EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_MONTH', @BUDGET_MONTH_LIMIT_ID OUT;
|
455
|
IF @BUDGET_MONTH_LIMIT_ID = '' OR @BUDGET_MONTH_LIMIT_ID IS NULL GOTO ABORT;
|
456
|
|
457
|
INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT(BUDGET_MONTH_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID,
|
458
|
M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT, BRANCH_KIND, BRANCH_LEVEL)
|
459
|
VALUES(@BUDGET_MONTH_LIMIT_ID, @p_GD_ID, 'thang', @p_BRANCH_TYPE, @BUDGET_YEAR, @l_BRANCH_ID, NULL,
|
460
|
@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL, @BRANCH_KIND, @BRANCH_LEVEL)
|
461
|
|
462
|
IF @@error<>0 GOTO ABORT;
|
463
|
FETCH NEXT FROM XmlBudgetLimitMonthDT
|
464
|
INTO @BRANCH_CODE, @BRANCH_NAME, @BRANCH_KIND, @BRANCH_LEVEL, @BUDGET_YEAR , @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
465
|
END
|
466
|
END
|
467
|
CLOSE XmlBudgetLimitMonthDT;
|
468
|
DEALLOCATE XmlBudgetLimitMonthDT
|
469
|
COMMIT TRANSACTION
|
470
|
|
471
|
SELECT '0' AS Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
|
472
|
RETURN '0'
|
473
|
|
474
|
ABORT:
|
475
|
BEGIN
|
476
|
ROLLBACK TRANSACTION
|
477
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, '' ErrorDesc
|
478
|
RETURN '-1'
|
479
|
END
|
480
|
GO
|
481
|
ALTER PROC [dbo].[TR_BUDGET_MONTH_DETAIL_Import] (
|
482
|
@GD_ID VARCHAR(20),
|
483
|
@BUDGET_TYPE VARCHAR(20),
|
484
|
@BRANCH_TYPE VARCHAR(20),
|
485
|
@p_MAKER_ID VARCHAR(20),
|
486
|
@p_MONTH_IMPORT VARCHAR(20),
|
487
|
@p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL XML = NULL
|
488
|
)
|
489
|
AS
|
490
|
-- Validation
|
491
|
IF(ISNULL(@GD_ID, '') = '')
|
492
|
BEGIN
|
493
|
SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
|
494
|
RETURN '-1'
|
495
|
END
|
496
|
IF(ISNULL(@BUDGET_TYPE, '') = '')
|
497
|
BEGIN
|
498
|
SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
|
499
|
RETURN '-1'
|
500
|
END
|
501
|
IF(ISNULL(@BRANCH_TYPE, '') = '')
|
502
|
BEGIN
|
503
|
SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
|
504
|
RETURN '-1'
|
505
|
END
|
506
|
|
507
|
BEGIN TRANSACTION
|
508
|
-- Đảm bảo ID ngân sách luôn được khai báo. Mõi ID ngân sách chỉ được dùng cho tháng hoặc cho năm
|
509
|
--IF(EXISTS(SELECT * FROM TR_GOODSTYPE WHERE GD_ID = @GD_ID AND ISNULL(BUDGET_TYPE, '') <> 'thang'))
|
510
|
--BEGIN
|
511
|
-- ROLLBACK TRANSACTION
|
512
|
-- SELECT '-1' as Result, N'Import thất bại, Mã ngân sách đang được quản lý định mức theo loại khác' AS ErrorDesc
|
513
|
-- RETURN '-1'
|
514
|
--END
|
515
|
DELETE FROM TR_GOODSTYPE WHERE GD_ID = @GD_ID
|
516
|
INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS)
|
517
|
VALUES (@GD_ID, 'thang', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')
|
518
|
|
519
|
-- KHAI BÁO CURSOR VÀ CÁC BIẾN LIÊN QUAN CHO VIỆC LẶP FILE IMPORT
|
520
|
DECLARE @ERROR_MESSAGE nvarchar(MAX)
|
521
|
DECLARE @INDEX INT = 2, @l_DEP_ID VARCHAR(20), @l_TLNAME VARCHAR(20), @l_KHOI_ID VARCHAR(20), @l_ROLENAME VARCHAR(20), @l_KHOI_NAME NVARCHAR(250);
|
522
|
|
523
|
DECLARE
|
524
|
@DEP_CODE VARCHAR(25),
|
525
|
@DEP_NAME NVARCHAR(500),
|
526
|
@ROLENAME NVARCHAR(20),
|
527
|
@BUDGET_YEAR VARCHAR(15),
|
528
|
@M1 DECIMAL(18, 0),
|
529
|
@M2 DECIMAL(18, 0),
|
530
|
@M3 DECIMAL(18, 0),
|
531
|
@M4 DECIMAL(18, 0),
|
532
|
@M5 DECIMAL(18, 0),
|
533
|
@M6 DECIMAL(18, 0),
|
534
|
@M7 DECIMAL(18, 0),
|
535
|
@M8 DECIMAL(18, 0),
|
536
|
@M9 DECIMAL(18, 0),
|
537
|
@M10 DECIMAL(18, 0),
|
538
|
@M11 DECIMAL(18, 0),
|
539
|
@M12 DECIMAL(18, 0)
|
540
|
|
541
|
DECLARE @hdoc INT
|
542
|
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_MONTH_DETAIL
|
543
|
DECLARE XmlBudgetLimitMonthDT CURSOR FOR
|
544
|
SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitMonthDT', 2)
|
545
|
WITH (
|
546
|
DEP_CODE VARCHAR(25),
|
547
|
DEP_NAME NVARCHAR(500),
|
548
|
ROLENAME NVARCHAR(20),
|
549
|
BUDGET_YEAR VARCHAR(15),
|
550
|
M1 DECIMAL(18, 0),
|
551
|
M2 DECIMAL(18, 0),
|
552
|
M3 DECIMAL(18, 0),
|
553
|
M4 DECIMAL(18, 0),
|
554
|
M5 DECIMAL(18, 0),
|
555
|
M6 DECIMAL(18, 0),
|
556
|
M7 DECIMAL(18, 0),
|
557
|
M8 DECIMAL(18, 0),
|
558
|
M9 DECIMAL(18, 0),
|
559
|
M10 DECIMAL(18, 0),
|
560
|
M11 DECIMAL(18, 0),
|
561
|
M12 DECIMAL(18, 0)
|
562
|
)
|
563
|
|
564
|
OPEN XmlBudgetLimitMonthDT;
|
565
|
FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
|
566
|
WHILE @@fetch_status=0
|
567
|
BEGIN
|
568
|
SET @INDEX = @INDEX +1
|
569
|
--------------------------------------------------------------------------------- VALIDATE BEGIN ---------------------------------------------------------------------------------
|
570
|
-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ ĐƠN VỊ
|
571
|
SET @l_DEP_ID = (SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE = @DEP_CODE AND BRANCH_ID = 'DV0001')
|
572
|
-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ USER ĐẶC BIỆT
|
573
|
SET @l_TLNAME = (SELECT TOP 1 TLNANME FROM TL_USER WHERE RoleName = @ROLENAME AND TLSUBBRID = 'DV0001' AND DEP_ID = @l_DEP_ID)
|
574
|
IF(ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@ROLENAME, '') <> '')
|
575
|
BEGIN
|
576
|
SET @l_KHOI_ID = ( SELECT TOP 1 A.KHOI_ID
|
577
|
FROM CM_DEPARTMENT A
|
578
|
WHERE A.DEP_ID = @l_DEP_ID
|
579
|
)
|
580
|
SET @l_KHOI_NAME = (SELECT TOP 1 C.DVDM_NAME
|
581
|
FROM CM_DEPARTMENT B
|
582
|
LEFT JOIN CM_DVDM C ON B.KHOI_ID = C.DVDM_ID
|
583
|
WHERE B.DEP_ID = @l_DEP_ID
|
584
|
)
|
585
|
SET @l_ROLENAME = @ROLENAME
|
586
|
END
|
587
|
ELSE
|
588
|
BEGIN
|
589
|
SET @l_KHOI_ID = NULL
|
590
|
SET @l_KHOI_NAME = NULL
|
591
|
SET @l_ROLENAME = NULL
|
592
|
END
|
593
|
|
594
|
IF(ISNULL(@l_DEP_ID,'') = '')
|
595
|
BEGIN
|
596
|
ROLLBACK TRANSACTION
|
597
|
CLOSE XmlBudgetLimitMonthDT;
|
598
|
DEALLOCATE XmlBudgetLimitMonthDT;
|
599
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Phòng ban ' + @DEP_CODE + N' không có trong hệ thống' AS ErrorDesc
|
600
|
RETURN '-1'
|
601
|
END
|
602
|
IF(ISNULL(@ROLENAME,'') <> '' AND ISNULL(@l_DEP_ID,'') = '')
|
603
|
BEGIN
|
604
|
ROLLBACK TRANSACTION
|
605
|
CLOSE XmlBudgetLimitMonthDT;
|
606
|
DEALLOCATE XmlBudgetLimitMonthDT;
|
607
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Khối ' + @l_KHOI_NAME + ' hiện tại không có ' + @ROLENAME AS ErrorDesc
|
608
|
RETURN '-1'
|
609
|
END
|
610
|
--------------------------------------------------------------------------------- VALIDATE END -------------------------------------------------------------------------------------------
|
611
|
-- NEU DA TON TAI NGAN SACH CHI PHI NAY THI CHI UPDATE
|
612
|
-- NEU DON VI CHIU CHI PHI LA DON VI
|
613
|
IF ( EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_MONTH_LIMIT WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR)
|
614
|
AND ISNULL(@l_DEP_ID,'') <> ''
|
615
|
AND ISNULL(@l_ROLENAME,'') = ''
|
616
|
)
|
617
|
BEGIN
|
618
|
print 'DON VI'
|
619
|
print '@GD_ID: ' + @GD_ID;
|
620
|
print '@l_DEP_ID: ' + @l_DEP_ID;
|
621
|
print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
|
622
|
print '@l_ROLENAME: ' + @l_ROLENAME;
|
623
|
print '@l_KHOI_ID: ' + @l_KHOI_ID;
|
624
|
print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
|
625
|
print '@l_TLNAME: ' + @l_TLNAME;
|
626
|
IF(ISNULL(@M1, 0) > 0)
|
627
|
BEGIN
|
628
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M1 = @M1 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
629
|
END
|
630
|
IF(ISNULL(@M2, 0) > 0)
|
631
|
BEGIN
|
632
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M2 = @M2 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
633
|
END
|
634
|
IF(ISNULL(@M3, 0) > 0)
|
635
|
BEGIN
|
636
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M3 = @M3 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
637
|
END
|
638
|
IF(ISNULL(@M4, 0) > 0)
|
639
|
BEGIN
|
640
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M4 = @M4 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
641
|
END
|
642
|
IF(ISNULL(@M5, 0) > 0)
|
643
|
BEGIN
|
644
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M5 = @M5 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
645
|
END
|
646
|
IF(ISNULL(@M6, 0) > 0)
|
647
|
BEGIN
|
648
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M6 = @M6 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
649
|
END
|
650
|
IF(ISNULL(@M7, 0) > 0)
|
651
|
BEGIN
|
652
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M7 = @M7 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
653
|
END
|
654
|
IF(ISNULL(@M8, 0) > 0)
|
655
|
BEGIN
|
656
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M8 = @M8 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
657
|
END
|
658
|
IF(ISNULL(@M9, 0) > 0)
|
659
|
BEGIN
|
660
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M9 = @M9 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
661
|
END
|
662
|
IF(ISNULL(@M10, 0) > 0)
|
663
|
BEGIN
|
664
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M10 = @M10 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
665
|
END
|
666
|
IF(ISNULL(@M11, 0) > 0)
|
667
|
BEGIN
|
668
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M11 = @M11 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
669
|
END
|
670
|
IF(ISNULL(@M12, 0) > 0)
|
671
|
BEGIN
|
672
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M12 = @M12 WHERE GD_ID = @GD_ID AND DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
673
|
END
|
674
|
|
675
|
IF @@error<>0 GOTO ABORT;
|
676
|
FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
|
677
|
END
|
678
|
ELSE IF ( EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_MONTH_LIMIT WHERE GD_ID = @GD_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND KHOI_ID = @l_KHOI_ID AND BUDGET_YEAR = @BUDGET_YEAR)
|
679
|
AND ISNULL(@l_DEP_ID,'') <> ''
|
680
|
AND ISNULL(@l_KHOI_ID,'') <> ''
|
681
|
)
|
682
|
BEGIN
|
683
|
print 'USER'
|
684
|
print '@GD_ID: ' + @GD_ID;
|
685
|
print '@l_DEP_ID: ' + @l_DEP_ID;
|
686
|
print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
|
687
|
print '@l_ROLENAME: ' + @l_ROLENAME;
|
688
|
print '@l_KHOI_ID: ' + @l_KHOI_ID;
|
689
|
print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
|
690
|
print '@l_TLNAME: ' + @l_TLNAME;
|
691
|
IF(ISNULL(@M1, 0) > 0)
|
692
|
BEGIN
|
693
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M1 = @M1 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
694
|
END
|
695
|
IF(ISNULL(@M2, 0) > 0)
|
696
|
BEGIN
|
697
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M2 = @M2 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
698
|
END
|
699
|
IF(ISNULL(@M3, 0) > 0)
|
700
|
BEGIN
|
701
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M3 = @M3 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
702
|
END
|
703
|
IF(ISNULL(@M4, 0) > 0)
|
704
|
BEGIN
|
705
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M4 = @M4 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
706
|
END
|
707
|
IF(ISNULL(@M5, 0) > 0)
|
708
|
BEGIN
|
709
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M5 = @M5 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
710
|
END
|
711
|
IF(ISNULL(@M6, 0) > 0)
|
712
|
BEGIN
|
713
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M6 = @M6 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
714
|
END
|
715
|
IF(ISNULL(@M7, 0) > 0)
|
716
|
BEGIN
|
717
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M7 = @M7 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
718
|
END
|
719
|
IF(ISNULL(@M8, 0) > 0)
|
720
|
BEGIN
|
721
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M8 = @M8 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
722
|
END
|
723
|
IF(ISNULL(@M9, 0) > 0)
|
724
|
BEGIN
|
725
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M9 = @M9 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
726
|
END
|
727
|
IF(ISNULL(@M10, 0) > 0)
|
728
|
BEGIN
|
729
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M10 = @M10 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
730
|
END
|
731
|
IF(ISNULL(@M11, 0) > 0)
|
732
|
BEGIN
|
733
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M11 = @M11 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
734
|
END
|
735
|
IF(ISNULL(@M12, 0) > 0)
|
736
|
BEGIN
|
737
|
UPDATE TR_REQ_BUDGET_MONTH_LIMIT SET M12 = @M12 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
738
|
END
|
739
|
|
740
|
|
741
|
IF @@error<>0 GOTO ABORT;
|
742
|
FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
|
743
|
END
|
744
|
ELSE
|
745
|
BEGIN
|
746
|
print 'THEM MOI'
|
747
|
print '@GD_ID: ' + @GD_ID;
|
748
|
print '@l_DEP_ID: ' + @l_DEP_ID;
|
749
|
print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
|
750
|
print '@l_ROLENAME: ' + @l_ROLENAME;
|
751
|
print '@l_KHOI_ID: ' + @l_KHOI_ID;
|
752
|
print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
|
753
|
print '@l_TLNAME: ' + @l_TLNAME;
|
754
|
-- Tự động sinh mã code
|
755
|
DECLARE @BUDGET_MONTH_LIMIT_ID VARCHAR(15);
|
756
|
EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_MONTH', @BUDGET_MONTH_LIMIT_ID OUT;
|
757
|
IF ISNULL(@BUDGET_MONTH_LIMIT_ID,'') = '' GOTO ABORT;
|
758
|
|
759
|
-- NEU LA IMPORT USER CHIU CHI PHI
|
760
|
IF(ISNULL(@l_ROLENAME, '') <> '')
|
761
|
BEGIN
|
762
|
INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT(BUDGET_MONTH_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, KHOI_ID, TLNAME, ROLENAME,
|
763
|
M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
|
764
|
VALUES(@BUDGET_MONTH_LIMIT_ID, @GD_ID, 'thang', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID, @l_KHOI_ID, @l_TLNAME, @l_ROLENAME,
|
765
|
@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
|
766
|
END
|
767
|
ELSE
|
768
|
BEGIN
|
769
|
INSERT INTO TR_REQ_BUDGET_MONTH_LIMIT(BUDGET_MONTH_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID,
|
770
|
M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
|
771
|
VALUES(@BUDGET_MONTH_LIMIT_ID, @GD_ID, 'thang', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID,
|
772
|
@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
|
773
|
END
|
774
|
|
775
|
|
776
|
IF @@error<>0 GOTO ABORT;
|
777
|
FETCH NEXT FROM XmlBudgetLimitMonthDT INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR, @M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12
|
778
|
END
|
779
|
END
|
780
|
CLOSE XmlBudgetLimitMonthDT;
|
781
|
DEALLOCATE XmlBudgetLimitMonthDT
|
782
|
|
783
|
COMMIT TRANSACTION
|
784
|
|
785
|
SELECT '0' AS Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
|
786
|
RETURN '0'
|
787
|
|
788
|
ABORT:
|
789
|
BEGIN
|
790
|
ROLLBACK TRANSACTION
|
791
|
CLOSE XmlBudgetLimitMonthDT;
|
792
|
DEALLOCATE XmlBudgetLimitMonthDT
|
793
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, '' ErrorDesc
|
794
|
RETURN '-1'
|
795
|
END
|
796
|
GO
|
797
|
ALTER PROC [dbo].[TR_BUDGET_YEAR_DETAIL_IMPORT] (
|
798
|
@GD_ID VARCHAR(20),
|
799
|
@BUDGET_TYPE VARCHAR(20),
|
800
|
@BRANCH_TYPE VARCHAR(20),
|
801
|
@p_MAKER_ID VARCHAR(20),
|
802
|
@p_MONTH_IMPORT VARCHAR(20),
|
803
|
@p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL XML = NULL
|
804
|
)
|
805
|
AS
|
806
|
IF(@GD_ID IS NULL OR @GD_ID = '')
|
807
|
BEGIN
|
808
|
SELECT '-1' as Result, N'Mã ngân sách chi phí không được để trống' AS ErrorDesc
|
809
|
RETURN '-1'
|
810
|
END
|
811
|
IF(@BUDGET_TYPE IS NULL OR @BUDGET_TYPE = '')
|
812
|
BEGIN
|
813
|
SELECT '-1' as Result, N'Loại ngân sách chi phí không được để trống' AS ErrorDesc
|
814
|
RETURN '-1'
|
815
|
END
|
816
|
IF(@BRANCH_TYPE IS NULL OR @BRANCH_TYPE = '')
|
817
|
BEGIN
|
818
|
SELECT '-1' as Result, N'Loại đơn vị không được để trống' AS ErrorDesc
|
819
|
RETURN '-1'
|
820
|
END
|
821
|
BEGIN TRANSACTION
|
822
|
|
823
|
DECLARE @hdoc INT
|
824
|
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TR_BUDGET_LIMIT_YEAR_DETAIL
|
825
|
|
826
|
DELETE FROM TR_GOODSTYPE WHERE GD_ID = @GD_ID
|
827
|
INSERT INTO TR_GOODSTYPE(GD_ID, BUDGET_TYPE, CREATE_DT, MAKER_ID, APPROVE_DT, CHECKER_ID, RECORD_STATUS, AUTH_STATUS)
|
828
|
VALUES (@GD_ID, 'nam', GETDATE(), @p_MAKER_ID, NULL, NULL, '1', 'A')
|
829
|
|
830
|
DECLARE XmlBudgetLimitYearhDT CURSOR FOR
|
831
|
SELECT * FROM OPENXML(@hdoc, '/Root/XmlBudgetLimitYearhDT', 2)
|
832
|
WITH (
|
833
|
DEP_CODE VARCHAR(15),
|
834
|
DEP_NAME NVARCHAR(250),
|
835
|
ROLENAME NVARCHAR(20),
|
836
|
BUDGET_YEAR VARCHAR(15),
|
837
|
BUDGET_LIMIT_AMT DECIMAL(18,2),
|
838
|
BUDGET_MONTH VARCHAR(50),
|
839
|
M1 DECIMAL(18, 0),
|
840
|
M2 DECIMAL(18, 0),
|
841
|
M3 DECIMAL(18, 0),
|
842
|
M4 DECIMAL(18, 0),
|
843
|
M5 DECIMAL(18, 0),
|
844
|
M6 DECIMAL(18, 0),
|
845
|
M7 DECIMAL(18, 0),
|
846
|
M8 DECIMAL(18, 0),
|
847
|
M9 DECIMAL(18, 0),
|
848
|
M10 DECIMAL(18, 0),
|
849
|
M11 DECIMAL(18, 0),
|
850
|
M12 DECIMAL(18, 0)
|
851
|
)
|
852
|
|
853
|
DECLARE
|
854
|
@DEP_CODE VARCHAR(15),
|
855
|
@DEP_NAME NVARCHAR(250),
|
856
|
@ROLENAME NVARCHAR(20),
|
857
|
@BUDGET_YEAR VARCHAR(15),
|
858
|
@BUDGET_LIMIT_AMT DECIMAL(18,2),
|
859
|
@BUDGET_MONTH VARCHAR(50),
|
860
|
@M1 DECIMAL(18, 0),
|
861
|
@M2 DECIMAL(18, 0),
|
862
|
@M3 DECIMAL(18, 0),
|
863
|
@M4 DECIMAL(18, 0),
|
864
|
@M5 DECIMAL(18, 0),
|
865
|
@M6 DECIMAL(18, 0),
|
866
|
@M7 DECIMAL(18, 0),
|
867
|
@M8 DECIMAL(18, 0),
|
868
|
@M9 DECIMAL(18, 0),
|
869
|
@M10 DECIMAL(18, 0),
|
870
|
@M11 DECIMAL(18, 0),
|
871
|
@M12 DECIMAL(18, 0)
|
872
|
------------------------------------------------ validate begin ------------------------------------------------
|
873
|
|
874
|
DECLARE @ERROR_MESSAGE nvarchar(MAX)
|
875
|
DECLARE @INDEX INT
|
876
|
SET @INDEX = 2
|
877
|
|
878
|
OPEN XmlBudgetLimitYearhDT;
|
879
|
|
880
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
881
|
INTO @DEP_CODE, @DEP_NAME , @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
882
|
WHILE @@fetch_status=0
|
883
|
BEGIN
|
884
|
SET @INDEX = @INDEX +1
|
885
|
----------------------------------------- validate begin -----------------------------------------
|
886
|
DECLARE @l_DEP_ID VARCHAR(20), @l_TLNAME VARCHAR(20);
|
887
|
-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ ĐƠN VỊ
|
888
|
SET @l_DEP_ID = (SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE = @DEP_CODE AND BRANCH_ID = 'DV0001')
|
889
|
-- NẾU ĐƠN VỊ CHỊU CHI PHÍ LÀ USER
|
890
|
SET @l_TLNAME = (SELECT TOP 1 TLNANME FROM TL_USER WHERE RoleName = @ROLENAME AND TLSUBBRID = 'DV0001' AND DEP_ID = @l_DEP_ID)
|
891
|
DECLARE @l_KHOI_ID VARCHAR(20), @l_ROLENAME VARCHAR(20), @l_KHOI_NAME NVARCHAR(250);
|
892
|
IF(ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@ROLENAME, '') <> '')
|
893
|
BEGIN
|
894
|
SET @l_KHOI_ID = ( SELECT TOP 1 A.KHOI_ID
|
895
|
FROM CM_DEPARTMENT A
|
896
|
WHERE A.DEP_ID = @l_DEP_ID
|
897
|
)
|
898
|
SET @l_KHOI_NAME = (SELECT TOP 1 C.DVDM_NAME
|
899
|
FROM CM_DEPARTMENT B
|
900
|
LEFT JOIN CM_DVDM C ON B.KHOI_ID = C.DVDM_ID
|
901
|
WHERE B.DEP_ID = @l_DEP_ID
|
902
|
)
|
903
|
SET @l_ROLENAME = @ROLENAME
|
904
|
END
|
905
|
ELSE
|
906
|
BEGIN
|
907
|
SET @l_KHOI_ID = NULL
|
908
|
SET @l_KHOI_NAME = NULL
|
909
|
SET @l_ROLENAME = NULL
|
910
|
END
|
911
|
|
912
|
IF(ISNULL(@l_DEP_ID,'') = '')
|
913
|
BEGIN
|
914
|
ROLLBACK TRANSACTION
|
915
|
CLOSE XmlBudgetLimitYearhDT;
|
916
|
DEALLOCATE XmlBudgetLimitYearhDT;
|
917
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Phòng ban ' + @DEP_CODE + N' không có trong hệ thống' AS ErrorDesc
|
918
|
RETURN '-1'
|
919
|
END
|
920
|
IF(ISNULL(@ROLENAME,'') <> '' AND ISNULL(@l_DEP_ID,'') = '')
|
921
|
BEGIN
|
922
|
ROLLBACK TRANSACTION
|
923
|
CLOSE XmlBudgetLimitYearhDT;
|
924
|
DEALLOCATE XmlBudgetLimitYearhDT;
|
925
|
SELECT '-1' as Result, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Khối ' + @l_KHOI_NAME + ' hiện tại không có ' + @ROLENAME AS ErrorDesc
|
926
|
RETURN '-1'
|
927
|
END
|
928
|
----------------------------------------- validate end -----------------------------------------
|
929
|
-- NEU DA TON TAI NGAN SACH CHI PHI NAY THI CHI UPDATE
|
930
|
-- NEU DON VI CHIU CHI PHI LA DON VI
|
931
|
IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR) AND ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@l_KHOI_ID,'') = '')
|
932
|
BEGIN
|
933
|
print 'DON VI'
|
934
|
print '@GD_ID: ' + @GD_ID;
|
935
|
print '@l_DEP_ID: ' + @l_DEP_ID;
|
936
|
print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
|
937
|
print '@l_ROLENAME: ' + @l_ROLENAME;
|
938
|
print '@l_KHOI_ID: ' + @l_KHOI_ID;
|
939
|
print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
|
940
|
print '@l_TLNAME: ' + @l_TLNAME;
|
941
|
IF(ISNULL(@M1, 0) > 0)
|
942
|
BEGIN
|
943
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M1 = @M1 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
944
|
END
|
945
|
IF(ISNULL(@M2, 0) > 0)
|
946
|
BEGIN
|
947
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M2 = @M2 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
948
|
END
|
949
|
IF(ISNULL(@M3, 0) > 0)
|
950
|
BEGIN
|
951
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M3 = @M3 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
952
|
END
|
953
|
IF(ISNULL(@M4, 0) > 0)
|
954
|
BEGIN
|
955
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M4 = @M4 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
956
|
END
|
957
|
IF(ISNULL(@M5, 0) > 0)
|
958
|
BEGIN
|
959
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M5 = @M5 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
960
|
END
|
961
|
IF(ISNULL(@M6, 0) > 0)
|
962
|
BEGIN
|
963
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M6 = @M6 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
964
|
END
|
965
|
IF(ISNULL(@M7, 0) > 0)
|
966
|
BEGIN
|
967
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M7 = @M7 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
968
|
END
|
969
|
IF(ISNULL(@M8, 0) > 0)
|
970
|
BEGIN
|
971
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M8 = @M8 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
972
|
END
|
973
|
IF(ISNULL(@M9, 0) > 0)
|
974
|
BEGIN
|
975
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M9 = @M9 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
976
|
END
|
977
|
IF(ISNULL(@M10, 0) > 0)
|
978
|
BEGIN
|
979
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M10 = @M10 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
980
|
END
|
981
|
IF(ISNULL(@M11, 0) > 0)
|
982
|
BEGIN
|
983
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M11 = @M11 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
984
|
END
|
985
|
IF(ISNULL(@M12, 0) > 0)
|
986
|
BEGIN
|
987
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M12 = @M12 where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
988
|
END
|
989
|
IF(ISNULL(@BUDGET_LIMIT_AMT, 0) > 0)
|
990
|
BEGIN
|
991
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET BUDGET_LIMIT_AMT = @BUDGET_LIMIT_AMT where GD_ID = @GD_ID and DEP_ID = @l_DEP_ID AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR AND ISNULL(ROLENAME, '') = ''
|
992
|
END
|
993
|
|
994
|
IF @@error<>0 GOTO ABORT;
|
995
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
996
|
INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
997
|
END
|
998
|
ELSE IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @GD_ID and ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND KHOI_ID = @l_KHOI_ID AND BUDGET_YEAR = @BUDGET_YEAR) AND ISNULL(@l_DEP_ID,'') <> '' AND ISNULL(@l_KHOI_ID,'') <> '')
|
999
|
BEGIN
|
1000
|
print 'USER'
|
1001
|
print '@GD_ID: ' + @GD_ID;
|
1002
|
print '@l_DEP_ID: ' + @l_DEP_ID;
|
1003
|
print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
|
1004
|
print '@l_ROLENAME: ' + @l_ROLENAME;
|
1005
|
print '@l_KHOI_ID: ' + @l_KHOI_ID;
|
1006
|
print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
|
1007
|
print '@l_TLNAME: ' + @l_TLNAME;
|
1008
|
IF(ISNULL(@M1, 0) > 0)
|
1009
|
BEGIN
|
1010
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M1 = @M1 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1011
|
END
|
1012
|
IF(ISNULL(@M2, 0) > 0)
|
1013
|
BEGIN
|
1014
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M2 = @M2 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1015
|
END
|
1016
|
IF(ISNULL(@M3, 0) > 0)
|
1017
|
BEGIN
|
1018
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M3 = @M3 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1019
|
END
|
1020
|
IF(ISNULL(@M4, 0) > 0)
|
1021
|
BEGIN
|
1022
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M4 = @M4 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1023
|
END
|
1024
|
IF(ISNULL(@M5, 0) > 0)
|
1025
|
BEGIN
|
1026
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M5 = @M5 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1027
|
END
|
1028
|
IF(ISNULL(@M6, 0) > 0)
|
1029
|
BEGIN
|
1030
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M6 = @M6 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1031
|
END
|
1032
|
IF(ISNULL(@M7, 0) > 0)
|
1033
|
BEGIN
|
1034
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M7 = @M7 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1035
|
END
|
1036
|
IF(ISNULL(@M8, 0) > 0)
|
1037
|
BEGIN
|
1038
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M8 = @M8 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1039
|
END
|
1040
|
IF(ISNULL(@M9, 0) > 0)
|
1041
|
BEGIN
|
1042
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M9 = @M9 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1043
|
END
|
1044
|
IF(ISNULL(@M10, 0) > 0)
|
1045
|
BEGIN
|
1046
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M10 = @M10 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1047
|
END
|
1048
|
IF(ISNULL(@M11, 0) > 0)
|
1049
|
BEGIN
|
1050
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M11 = @M11 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1051
|
END
|
1052
|
IF(ISNULL(@M12, 0) > 0)
|
1053
|
BEGIN
|
1054
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET M12 = @M12 WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1055
|
END
|
1056
|
IF(ISNULL(@BUDGET_LIMIT_AMT, 0) > 0)
|
1057
|
BEGIN
|
1058
|
UPDATE TR_REQ_BUDGET_YEAR_LIMIT SET TLNAME = @l_TLNAME, BUDGET_LIMIT_AMT = @BUDGET_LIMIT_AMT WHERE GD_ID = @GD_ID AND KHOI_ID = @l_KHOI_ID AND ROLENAME = @l_ROLENAME AND BRANCH_ID = 'DV0001' AND BUDGET_YEAR = @BUDGET_YEAR
|
1059
|
END
|
1060
|
|
1061
|
|
1062
|
IF @@error<>0 GOTO ABORT;
|
1063
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
1064
|
INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
1065
|
END
|
1066
|
ELSE
|
1067
|
BEGIN
|
1068
|
print 'THEM MOI'
|
1069
|
print '@GD_ID: ' + @GD_ID;
|
1070
|
print '@l_DEP_ID: ' + @l_DEP_ID;
|
1071
|
print '@BUDGET_YEAR: ' + CONVERT(VARCHAR(20), @BUDGET_YEAR);
|
1072
|
print '@l_ROLENAME: ' + @l_ROLENAME;
|
1073
|
print '@l_KHOI_ID: ' + @l_KHOI_ID;
|
1074
|
print '@l_KHOI_NAME: ' + @l_KHOI_NAME;
|
1075
|
print '@l_TLNAME: ' + @l_TLNAME;
|
1076
|
-- Tự động sinh mã code
|
1077
|
DECLARE @BUDGET_YEAR_LIMIT_ID VARCHAR(15);
|
1078
|
EXEC SYS_CodeMasters_Gen 'BUDGET_LIMIT_YEAR', @BUDGET_YEAR_LIMIT_ID OUT;
|
1079
|
IF @BUDGET_YEAR_LIMIT_ID='' OR @BUDGET_YEAR_LIMIT_ID IS NULL GOTO ABORT;
|
1080
|
|
1081
|
-- NEU LA IMPORT USER CHIU CHI PHI
|
1082
|
IF(ISNULL(@l_TLNAME, '') <> '')
|
1083
|
BEGIN
|
1084
|
INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT(BUDGET_YEAR_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, BUDGET_LIMIT_AMT, KHOI_ID, TLNAME, ROLENAME,
|
1085
|
M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
|
1086
|
VALUES(@BUDGET_YEAR_LIMIT_ID, @GD_ID, 'nam', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID, @BUDGET_LIMIT_AMT, @l_KHOI_ID, @l_TLNAME, @l_ROLENAME,
|
1087
|
@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
|
1088
|
END
|
1089
|
ELSE
|
1090
|
BEGIN
|
1091
|
INSERT INTO TR_REQ_BUDGET_YEAR_LIMIT(BUDGET_YEAR_LIMIT_ID, GD_ID, BUDGET_TYPE, BRANCH_TYPE, BUDGET_YEAR, BRANCH_ID, DEP_ID, BUDGET_LIMIT_AMT,
|
1092
|
M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, MAKER_ID, CREATE_DT, RECORD_STATUS, AUTH_STATUS, CHECKER_ID, APPROVE_DT)
|
1093
|
VALUES(@BUDGET_YEAR_LIMIT_ID, @GD_ID, 'nam', 'ho', @BUDGET_YEAR, 'DV0001', @l_DEP_ID, @BUDGET_LIMIT_AMT,
|
1094
|
@M1, @M2, @M3, @M4, @M5, @M6, @M7, @M8, @M9, @M10, @M11, @M12, @p_MAKER_ID, GETDATE(), '1', 'A', NULL, NULL)
|
1095
|
END
|
1096
|
|
1097
|
|
1098
|
IF @@error<>0 GOTO ABORT;
|
1099
|
FETCH NEXT FROM XmlBudgetLimitYearhDT
|
1100
|
INTO @DEP_CODE, @DEP_NAME, @ROLENAME, @BUDGET_YEAR , @BUDGET_LIMIT_AMT, @BUDGET_MONTH, @M1 , @M2 , @M3 , @M4 , @M5 , @M6 , @M7 , @M8 , @M9 , @M10, @M11, @M12
|
1101
|
END
|
1102
|
|
1103
|
END
|
1104
|
CLOSE XmlBudgetLimitYearhDT;
|
1105
|
DEALLOCATE XmlBudgetLimitYearhDT
|
1106
|
COMMIT TRANSACTION
|
1107
|
|
1108
|
SELECT '0' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
|
1109
|
RETURN '0'
|
1110
|
|
1111
|
ABORT:
|
1112
|
BEGIN
|
1113
|
ROLLBACK TRANSACTION
|
1114
|
SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, '' ErrorDesc
|
1115
|
RETURN '-1'
|
1116
|
End
|
1117
|
GO
|
1118
|
--04102024_SECRETKEY
|