Project

General

Profile

script_upd_04102024.txt

Luc Tran Van, 10/04/2024 10:20 AM

 
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