Project

General

Profile

Download (58.7 KB)

REQ_AD_PAY_APPR.txt

Luc Tran Van, 12/07/2020 01:28 PM

 
1 
??
2
3 
EXEC SP_HELPTEXT 'TR_REQ_ADVANCE_PAYMENT_APPR'
4
5 
Tr?ng th?i: Select th?nh c?ng
6
7
8
9 
Text
10
11 
CREATE PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Appr]
12 
--Luanlt 2019/17/10 - S?a params
13 
@p_REQ_PAY_ID varchar(15)= NULL,
14 
@p_CHECKER_ID varchar(15) = NULL,
15 
@p_AUTH_STATUS varchar(15) = NULL,
16 
@p_COST_ID VARCHAR(15) = NULL
17 
AS
18 
BEGIN TRANSACTION
19 
DECLARE @BRANCH_CREATE VARCHAR(15), @p_DEP_ID VARCHAR(15)
20 
--PRINT @BRANCH_CREATE
21 
SET @BRANCH_CREATE =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
22 
DECLARE @DEP_CODE_NEXT VARCHAR(15)
23 
IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_CREATE)<>'HS')
24 
BEGIN
25 
SET @p_DEP_ID =(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE LEFT(DEP_CODE,5) IN (SELECT TOP 1 LEFT(DVDM_CODE,5) FROM CM_DVDM WHERE DVDM_ID =@p_COST_ID))
26 
SET @DEP_CODE_NEXT = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID)
27 
--PRINT @DEP_CODE_NEXT
28 
END
29 
ELSE
30 
BEGIN
31 
SET @p_DEP_ID =(SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
32 
SET @DEP_CODE_NEXT = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID)
33 
END
34 
IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE MAKER_ID =@p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID)
35 
BEGIN
36 
ROLLBACK TRANSACTION
37 
SELECT '-1' as Result, '' REQ_PAY_ID, N'Ng??i ph? duy?t phi?u ph?i kh?c v?i ng??i t?o phi?u! B?n kh?ng ??c ph?p duy?t ?i t??ng n?y' ErrorDesc
38 
RETURN '-1'
39 
END
40 
-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
41 
IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
42 
BEGIN
43 
ROLLBACK TRANSACTION
44 
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phi?u ? ngh? t?m ?ng 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
45 
RETURN '-1'
46 
END
47 
-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
48 
IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
49 
BEGIN
50 
ROLLBACK TRANSACTION
51 
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phi?u ? ngh? t?m ?ng 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
52 
RETURN '-1'
53 
END
54 
-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET
55 
IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
56 
BEGIN
57 
ROLLBACK TRANSACTION
58 
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phi?u ? ngh? t?m ?ng ? ??c KSV ph? duy?t tr??c ?' ErrorDesc
59 
RETURN '-1'
60 
END
61 
IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
62 
BEGIN
63 
ROLLBACK TRANSACTION
64 
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phi?u ? ngh? t?m ?ng ? ??c ph? duy?t tr??c ?' ErrorDesc
65 
RETURN '-1'
66 
END
67 
--- KHAI BAO CHUUNG
68 
DECLARE @ROLE_ID VARCHAR(200), @BRANCH_TYPE VARCHAR(15), @TOTAL_ADVANCE DECIMAL(18,0), @TOTAL_PAYBACK DECIMAL(18,0), @BRANCH_ID VARCHAR(15),
69 
@DEP_ID VARCHAR(15), @COSTCENTER_ID VARCHAR(15)= NULL, @BRANCH_RQ VARCHAR(15) = NULL,@DEP_ID_RQ VARCHAR(15), @BRANCH_LOGIN VARCHAR(15),@LIMIT_ONE_OF DECIMAL(18,2)
70 
DECLARE @LIMIT_AMT DECIMAL(18,0), @REQ_AMT DECIMAL(18,2) =0, @TONG_PGD DECIMAL(18,0), @TONG_PGD_HOAN DECIMAL(18,0)
71 
SET @REQ_AMT = (SELECT REQ_AMT *ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
72 
--SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)
73 
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)
74 
--SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
75 
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'))
76 
BEGIN
77 
PRINT @ROLE_ID
78 
END
79 
ELSE
80 
BEGIN
81 
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID)
82 
IF(@ROLE_ID IS NULL OR @ROLE_ID ='')
83 
BEGIN
84 
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))
85 
END
86 
END
87 
SET @BRANCH_ID = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
88 
SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
89 
SET @DEP_ID_RQ =(SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
90 
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)
91 
DECLARE @tmp table(BRANCH_ID varchar(15))
92 
INSERT into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_ID)
93 
DECLARE @tmp_CN table(BRANCH_ID varchar(15))
94 
IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='CN')
95 
BEGIN
96 
INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_RQ)
97 
END
98 
ELSE IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='PGD')
99 
BEGIN
100 
DECLARE @FATHER_ID VARCHAR(15) = NULL
101 
SET @FATHER_ID =(SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)
102 
INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@FATHER_ID)
103 
END
104 
--DECLARE @DEP_ID_LG VARCHAR(15) = NULL
105 
--SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)
106 
DECLARE @TMP_DVDM TABLE(DVDM_ID VARCHAR(15))
107 
INSERT INTO @TMP_DVDM
108 
SELECT A.DVDM_ID--, A.COST_CODE, A.COST_NAME, B.DEP_ID --, C.DEP_CODE, C.DEP_NAME
109 
FROM PL_COSTCENTER A
110 
LEFT JOIN PL_COSTCENTER_DT B ON A.COST_ID = B.COST_ID
111 
WHERE B.DEP_ID = @DEP_ID_RQ
112 
GROUP BY A.DVDM_ID
113 
-- KHAI BAO BRANCH CUA USER DUYET
114 
SET @BRANCH_LOGIN = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
115 
DECLARE @LIMIT_REMAIN DECIMAL(18,0)
116 
--CAP NHAT CODE TRONG QUA TRINH TEST UAT
117 
IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE='I'))
118 
BEGIN
119 
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID))
120 
BEGIN
121 
ROLLBACK TRANSACTION
122 
SELECT '-1' as Result, '' REQ_PAY_ID, N'B?n kh?ng c? quy?n ph? duy?t phi?u t?m ?ng n?i b?. Vui l?ng ch?n giao d?ch kh?c ? duy?t' ErrorDesc
123 
RETURN '-1'
124 
END
125 
--IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID AND BRANCH_ID= @BRANCH_ID AND STATUS ='P'))
126 
--BEGIN
127 
-- ROLLBACK TRANSACTION
128 
-- SELECT '-1' as Result, '' REQ_PAY_ID, N'B?n ? th?c hi?n x?c nh?n phi?u t?m ?ng tr??c ?' ErrorDesc
129 
-- RETURN '-1'
130 
--END
131 
-- KIEM TRA AUTH_STATUS TRUYEN XUONG LA GI, A: DUYET, C: CONFIRM
132 
IF(@p_AUTH_STATUS='U') -- KIEM TRA HAN MUC CON LAI CUA USER VA THONG BAO CHO NGUOI DUYET
133 
BEGIN
134 
-- BAT DAU DUYET THEO NGAN SACH
135 
--- LAY HAN MUC CUA USER
136 
SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
137
138 
print @LIMIT_AMT
139 
--
140 
SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
141 
IF(@ROLE_ID ='GDK' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
142 
BEGIN
143 
SET @LIMIT_AMT =1000000000
144
145 
END
146 
IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
147 
BEGIN
148 
SET @LIMIT_AMT =3000000000
149
150 
END
151 
-- NEU LA HOI SO THI LAY TONG SO TIEN TAM UNG CUA CAC PHONG BAN CHUNG VOI KHOI CUA PHIEU DANG DUOC DUYET
152 
SET @TONG_PGD =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID = @BRANCH_RQ
153 
AND REQ_TYPE ='I' AND AUTH_STATUS ='A') + @REQ_AMT
154 
SET @TONG_PGD_HOAN =(
155 
--SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT))
156 
--FROM TR_REQ_PAYMENT_DT B
157 
--INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID
158 
--INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID
159 
SELECT SUM (ISNULL(A.PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT A
160 
WHERE A.BRANCH_ID = @BRANCH_RQ
161 
AND A.AUTH_STATUS_KT ='A' AND A.REQ_TYPE='I')
162 
IF(@BRANCH_TYPE='HS')
163 
BEGIN
164 
SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE
165 
DEP_ID IN
166 
(SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
167 
LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
168 
WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ)
169 
AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0)
170 
SET @TOTAL_PAYBACK =
171 
--ISNULL(
172 
--(
173 
-- SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT))
174 
-- FROM TR_REQ_PAYMENT_DT B
175 
-- INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID
176 
-- INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID
177 
-- WHERE A.DEP_ID IN
178 
-- (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
179 
-- LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
180 
-- WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_ID)
181 
-- AND A.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
182 
--),0)
183 
ISNULL(
184 
(
185 
SELECT (SUM(ISNULL(C.PAY_AMT,0)))
186 
FROM TR_REQ_ADVANCE_PAYMENT C
187 
WHERE C.DEP_ID IN
188 
(SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
189 
LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
190 
WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_ID)
191 
AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
192 
),0)
193 
END
194 
-- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC
195 
ELSE IF(@BRANCH_TYPE <>'HS')
196 
BEGIN
197 
SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN)
198 
AND REQ_TYPE ='I' AND AUTH_STATUS='A')
199 
--SET @TOTAL_PAYBACK =
200 
--(
201 
-- SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT))
202 
-- FROM TR_REQ_PAYMENT_DT B
203 
-- INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID
204 
-- INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID
205 
-- WHERE A.BRANCH_ID IN
206 
-- (SELECT BRANCH_ID FROM @tmp_CN)
207 
-- AND A.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
208 
--)
209 
SET @TOTAL_PAYBACK =
210 
(
211 
SELECT (SUM(ISNULL(B.PAY_AMT,0)))
212 
FROM TR_REQ_ADVANCE_PAYMENT B
213 
WHERE B.BRANCH_ID IN
214 
(SELECT BRANCH_ID FROM @tmp_CN)
215 
AND B.AUTH_STATUS_KT ='A' AND B.REQ_TYPE='I'
216 
)
217 
END
218
219 
SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0)
220 
-- KIEM TRA NEU LA CHU TICH HDQT THI PHE DUYET LUON, KHONG CAN XET HAN MUC GI
221 
IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName ='HDQT'))
222 
BEGIN
223 
ROLLBACK TRANSACTION
224 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
225 
--N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
226 
--+ CHAR(10) +
227 
--N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
228 
--+ CHAR(10) + CHAR(13) +
229 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
230 
+ CHAR(10) +
231 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
232 
RETURN '-4'
233 
END
234 
IF(@LIMIT_AMT <(SELECT REQ_AMT * ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) + @LIMIT_REMAIN)
235 
BEGIN
236 
ROLLBACK TRANSACTION
237 
SELECT '-2' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
238 
N'T?ng h?n m?c ph? duy?t l?: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10)+
239 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
240 
+ CHAR(10) + CHAR(13)+
241 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN')
242 
+ CHAR(10) +
243 
N'S? ti?n t?m ?ng ? v??t m?c ph? duy?t l?: '+ FORMAT((@LIMIT_REMAIN +@REQ_AMT) -@LIMIT_AMT,'#,#', 'vi-VN')
244 
+ CHAR(10) +
245 
N'B?n mu?n chuy?n giao d?ch l?n c?p cao h?n ? duy?t hay ti?p t?c ch? duy?t' ErrorDesc
246 
RETURN '-2'
247 
END
248 
--- NEU DU HAN MUC THI THONG BAO DE NGUOI DUYET CAN NHAC CO NEN DUYET PHIEU HAY KHONG
249 
IF(@LIMIT_AMT >=(SELECT REQ_AMT * ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) +@LIMIT_REMAIN)
250 
BEGIN
251 
IF(@REQ_AMT >@LIMIT_ONE_OF)
252 
BEGIN
253 
ROLLBACK TRANSACTION
254 
SELECT '-2' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
255 
N'T?ng h?n m?c ph? duy?t l?: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
256 
+ CHAR(10)+
257 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
258 
+ CHAR(10) + CHAR(13) +
259 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN')
260 
+ CHAR(10) +
261 
N'S? ti?n t?m ?ng ? v??t m?c so v?i s? ti?n c?a m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
262 
+ CHAR(10) +
263 
N'B?n mu?n chuy?n giao d?ch l?n c?p cao h?n ? duy?t hay ti?p t?c ch? duy?t' ErrorDesc
264 
RETURN '-2'
265 
END
266 
ELSE
267 
BEGIN
268 
-- KIEM TRA NEU LA PGD THI CANH BAO
269 
IF(@BRANCH_TYPE ='HS')
270 
BEGIN
271 
IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName NOT IN('TGD','HQDT')))
272 
BEGIN
273 
ROLLBACK TRANSACTION
274 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
275 
N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
276 
+ CHAR(10) + CHAR(13) +
277 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
278 
+ CHAR(10) +
279 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
280 
+ CHAR(10) +
281 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
282 
RETURN '-4'
283 
END
284 
ELSE
285 
BEGIN
286 
ROLLBACK TRANSACTION
287 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
288 
--N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
289 
--+ CHAR(10) + CHAR(13) +
290 
--N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
291 
--+ CHAR(10) +
292 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
293 
+ CHAR(10) +
294 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
295 
RETURN '-4'
296 
END
297 
END
298 
ELSE IF(@BRANCH_TYPE ='CN')
299 
BEGIN
300 
IF(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0) > 40000000)
301 
BEGIN
302 
ROLLBACK TRANSACTION
303 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
304 
N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
305 
+ CHAR(10) +
306 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
307 
+ CHAR(10) + CHAR(13) +
308 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
309 
+ CHAR(10) +
310 
N'S? ti?n t?m ?ng ang v??t qu? s? t?ng h?n m?c t?m ?ng c?a 1 CN: '+ FORMAT(40000000,'#,#', 'vi-VN') +N'. S? ti?n v??t l?: '+ FORMAT(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0)-40000000,'#,#', 'vi-VN')
311 
+ CHAR(10) +
312 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
313 
RETURN '-4'
314 
END
315 
ELSE
316 
BEGIN
317 
IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName NOT IN('TGD','HQDT')))
318 
BEGIN
319 
ROLLBACK TRANSACTION
320 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
321 
N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
322 
+ CHAR(10) +
323 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
324 
+ CHAR(10) + CHAR(13) +
325 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
326 
+ CHAR(10) +
327 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
328 
RETURN '-4'
329 
END
330 
ELSE
331 
BEGIN
332 
ROLLBACK TRANSACTION
333 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
334 
--N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
335 
--+ CHAR(10) +
336 
--N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
337 
--+ CHAR(10) + CHAR(13) +
338 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
339 
+ CHAR(10) +
340 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
341 
RETURN '-4'
342 
END
343 
END
344 
END
345 
ELSE IF(@BRANCH_TYPE ='PGD')
346 
BEGIN
347 
IF(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0) > 10000000)
348 
BEGIN
349 
ROLLBACK TRANSACTION
350 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
351 
N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')+ CHAR(10) +
352 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
353 
+ CHAR(10) + CHAR(13) +
354 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
355 
+ CHAR(10) +
356 
N'S? ti?n t?m ?ng ang v??t qu? s? t?ng h?n m?c t?m ?ng c?a 1 PGD: '+ FORMAT(10000000,'#,#', 'vi-VN') +N'. S? ti?n v??t l?: '+ FORMAT(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0)-10000000,'#,#', 'vi-VN')
357 
+ CHAR(10) +
358 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
359 
RETURN '-4'
360 
END
361 
ELSE
362 
BEGIN
363 
ROLLBACK TRANSACTION
364 
SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID,
365 
N'T?ng h?n m?c ph? duy?t c?a b?n l?: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
366 
+ CHAR(10) +
367 
N'H?n m?c tr?n m?t l?n ph? duy?t l?: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN')
368 
+ CHAR(10) + CHAR(13) +
369 
N'S? d? t?m ?ng liy k? c?a ?n v? t?nh t?i th?i i?m n?y l? : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
370 
+ CHAR(10) +
371 
N'B?n c? mu?n ti?p t?c duy?t hay kh?ng' ErrorDesc
372 
RETURN '-4'
373 
END
374 
END
375 
END
376 
END
377 
-- NEU THOA MAN CAC DIEU KIEN SE TIEN HANH DUYET
378 
END -- END DU HAN MUC DUYET
379 
ELSE IF(@p_AUTH_STATUS='A')
380 
BEGIN
381 
UPDATE TR_REQ_ADVANCE_PAYMENT
382 
SET AUTH_STATUS='A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = GETDATE(), AUTH_STATUS_KT='U'
383 
WHERE REQ_PAY_ID = @p_REQ_PAY_ID
384 
--- CAP NHAT LAI TINH TRANG TRONG REQUEST_PROCESS
385 
DECLARE @PROCESS_CURR VARCHAR(5)
386 
SET @PROCESS_CURR =(SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND ROLE_USER =@ROLE_ID)
387 
INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@PROCESS_CURR,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' duy?t' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'C?c c?p ph? duy?t theo h?n m?c')
388 
--- CAP NHAT PROCESS CUA PHIEU DE NGHI TAM UNG LA DA DUYET
389 
UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS=@PROCESS_CURR WHERE REQ_PAY_ID=@p_REQ_PAY_ID
390 
--
391 
UPDATE PL_REQUEST_PROCESS SET STATUS ='A', NOTES = (SELECT ROLE_DESC + N' duy?t' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) WHERE ROLE_USER =@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID
392 
DECLARE @l_REQ_AMT DECIMAL(18,0) = (SELECT REQ_AMT*ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
393 
SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
394 
--
395 
SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
396 
IF(@ROLE_ID ='GDK' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
397 
BEGIN
398 
SET @LIMIT_AMT =1000000000
399
400 
END
401 
IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
402 
BEGIN
403 
SET @LIMIT_AMT =3000000000
404
405 
END
406 
-- NEU LA HOI SO THI LAY TONG SO TIEN TAM UNG CUA CAC PHONG BAN CHUNG VOI KHOI CUA PHIEU DANG DUOC DUYET
407 
SET @TONG_PGD =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID = @BRANCH_RQ
408 
AND REQ_TYPE ='I' AND AUTH_STATUS ='A') + @REQ_AMT
409 
SET @TONG_PGD_HOAN =(
410 
--SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT))
411 
--FROM TR_REQ_PAYMENT_DT B
412 
--INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID
413 
--INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID
414 
SELECT SUM (ISNULL(A.PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT A
415 
WHERE A.BRANCH_ID = @BRANCH_RQ
416 
AND A.AUTH_STATUS_KT ='A' AND A.REQ_TYPE='I')
417 
IF(@BRANCH_TYPE='HS')
418 
BEGIN
419 
SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE
420 
DEP_ID IN
421 
(SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
422 
LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
423 
WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ)
424 
AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0)
425 
SET @TOTAL_PAYBACK =
426 
ISNULL(
427 
(
428 
SELECT (SUM(ISNULL(C.PAY_AMT,0)))
429 
FROM TR_REQ_ADVANCE_PAYMENT C
430 
WHERE C.DEP_ID IN
431 
(SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
432 
LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
433 
WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_ID)
434 
AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
435 
),0)
436 
END
437 
-- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC
438 
ELSE IF(@BRANCH_TYPE <>'HS')
439 
BEGIN
440 
SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN)
441 
AND REQ_TYPE ='I' AND AUTH_STATUS='A')
442 
SET @TOTAL_PAYBACK =
443 
(
444 
SELECT (SUM(ISNULL(B.PAY_AMT,0)))
445 
FROM TR_REQ_ADVANCE_PAYMENT B
446 
WHERE B.BRANCH_ID IN
447 
(SELECT BRANCH_ID FROM @tmp_CN)
448 
AND B.AUTH_STATUS_KT ='A' AND B.REQ_TYPE='I'
449 
)
450 
END
451 
SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0)
452 
--- INSERT VAO BANG LOG
453 
INSERT INTO TR_REQ_ADVANCE_LIMIT_LOG VALUES (@p_REQ_PAY_ID,@LIMIT_REMAIN,GETDATE())
454 
----
455 
END
456 
ELSE -- NEW KHONG DU HAN MUC THI BAT DAU XAC NHAN VA DUYET THEO HAN MUC
457 
BEGIN
458 
DECLARE @t_REQ_AMT DECIMAL(18,0) = (SELECT REQ_AMT*ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
459
460 
--PRINT @t_REQ_AMT
461 
--- NEU GIAM DOC KHOI VAN CHUA DU HAN MUC THI GIAM DOC KHOI XAC NHAN V? TIEP TUC BUOC TIEP THEO LA PHO TONG GIAM DOC
462 
DECLARE @MAX_STEP INT, @NEXT_ROLE VARCHAR(20), @MESSAGE NVARCHAR(100), @PROCESS_ID_NEXT VARCHAR(5), @PROCESS_PARENT VARCHAR(5),
463 
@BRANCH_ID_PROC VARCHAR(15), @NEX_ROLE_STEP VARCHAR(25), @CURRENT_PROCESS VARCHAR(15)
464 
SET @CURRENT_PROCESS =(SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND ROLE_USER=@ROLE_ID)
465 
SET @NEX_ROLE_STEP = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE PARENT_PROCESS_ID =@CURRENT_PROCESS AND REQ_ID = @p_REQ_PAY_ID )
466 
--PRINT @NEX_ROLE_STEP
467 
--SET @MAX_STEP = CONVERT(INT,(SELECT MAX(PROCESS_ID) FROM TR_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID))
468 
SET @PROCESS_ID_NEXT = CONVERT(VARCHAR(5),@CURRENT_PROCESS+1)
469 
--SET @PROCESS_PARENT = CONVERT(VARCHAR(5),@MAX_STEP)
470 
DECLARE @TMP_ROLE TABLE (ROLE_ID VARCHAR(15), BRANCH_ID VARCHAR(15), LIMIT_VALUE DECIMAL(18,0),LIMIT_TYPE VARCHAR(15))
471 
--IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06908%' AND DEP_ID =@p_DEP_ID)
472 
IF(LEFT(@DEP_CODE_NEXT,5) ='06908' OR @DEP_CODE_NEXT ='0690605')
473 
BEGIN
474 
INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK'
475 
END
476 
--ELSE IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06910%' AND DEP_ID =@p_DEP_ID)
477 
ELSE IF(LEFT(@DEP_CODE_NEXT,5) ='06910')
478 
BEGIN
479 
INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK'
480 
END
481 
--ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06900%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06901%' AND DEP_ID =@p_DEP_ID)
482 
--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06902%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06903%' AND DEP_ID =@p_DEP_ID)
483 
--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06904%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06920%' AND DEP_ID =@p_DEP_ID))
484 
ELSE IF((LEFT(@DEP_CODE_NEXT,5) ='06900') OR (LEFT(@DEP_CODE_NEXT,5)) ='06901' OR (LEFT(@DEP_CODE_NEXT,5))='06904'
485 
OR (LEFT(@DEP_CODE_NEXT,5))='06902' OR (LEFT(@DEP_CODE_NEXT,5))='06903' OR (LEFT(@DEP_CODE_NEXT,5))='06920')
486 
BEGIN
487 
INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID NOT IN ('GDK','PTGD')
488 
END
489 
--ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06907%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06909%' AND DEP_ID =@p_DEP_ID)
490 
--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06911%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06921%' AND DEP_ID =@p_DEP_ID)
491 
--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06922%' AND DEP_ID =@p_DEP_ID))
492 
ELSE IF((LEFT(@DEP_CODE_NEXT,5) ='06907') OR (LEFT(@DEP_CODE_NEXT,5)) ='06909' OR (LEFT(@DEP_CODE_NEXT,5))='06911'
493 
OR (LEFT(@DEP_CODE_NEXT,5))='06922' OR (LEFT(@DEP_CODE_NEXT,5))='06921' OR (LEFT(@DEP_CODE_NEXT,5))='06920')
494 
BEGIN
495 
INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'PTGD'
496 
END
497 
ELSE
498 
BEGIN
499 
INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001'
500 
END
501 
SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY')
502 
print @DEP_CODE_NEXT
503 
print @BRANCH_ID_PROC
504 
print @NEX_ROLE_STEP
505 
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@NEX_ROLE_STEP AND REQ_ID= @p_REQ_PAY_ID))
506 
BEGIN
507 
--SET @NEXT_ROLE =(SELECT TOP 1 ROLE_ID FROM @TMP_ROLE WHERE LIMIT_VALUE >@t_REQ_AMT
508 
SET @NEXT_ROLE =(SELECT TOP 1 ROLE_ID FROM @TMP_ROLE WHERE 1 =1
509 
AND ROLE_ID NOT IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID) AND LIMIT_TYPE ='ADV_PAY')
510 
--print @NEXT_ROLE
511 
SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY')
512 
SET @MESSAGE = (SELECT N'?i '+ ROLE_DESC + N' ph? duy?t' FROM TL_SYSROLE WHERE ROLE_ID = @NEXT_ROLE)
513 
INSERT INTO PL_REQUEST_PROCESS (REQ_ID,PROCESS_ID,STATUS,ROLE_USER,BRANCH_ID,CHECKER_ID,APPROVE_DT,PARENT_PROCESS_ID,IS_LEAF, COST_ID, DVDM_ID, NOTES)
514 
VALUES (@p_REQ_PAY_ID,@PROCESS_ID_NEXT,'C',@NEXT_ROLE,@BRANCH_ID_PROC,@NEXT_ROLE,GETDATE(),@PROCESS_PARENT,'Y',NULL,@p_COST_ID,@MESSAGE)
515 
INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@CURRENT_PROCESS,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' x?c nh?n' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'C?c c?p ph? duy?t theo h?n m?c')
516 
END
517 
ELSE
518 
BEGIN
519 
--print @NEX_ROLE_STEP
520 
SET @NEXT_ROLE =@NEX_ROLE_STEP
521 
UPDATE PL_REQUEST_PROCESS SET STATUS='C',DVDM_ID=@p_COST_ID WHERE ROLE_USER =@NEXT_ROLE AND REQ_ID = @p_REQ_PAY_ID
522 
INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@CURRENT_PROCESS,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' x?c nh?n' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'C?c c?p ph? duy?t theo h?n m?c')
523 
END
524 
--
525 
IF @@Error <> 0 GOTO ABORT
526 
-- UPDATE STATUS CUA STEP HIEN TAI
527 
UPDATE PL_REQUEST_PROCESS SET STATUS='P',IS_LEAF ='N', NOTES = (SELECT ROLE_DESC + N' x?c nh?n' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) WHERE ROLE_USER =@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID
528 
--INSERT VAO TR_PROCESS
529 
UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS =@CURRENT_PROCESS , AUTH_STATUS='U',DVDM_ID = @p_COST_ID WHERE REQ_PAY_ID =@p_REQ_PAY_ID
530 
---
531 
UPDATE PL_REQUEST_PROCESS SET DVDM_ID= @p_COST_ID WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER <>@ROLE_ID
532 
END
533 
END
534 
ELSE
535 
BEGIN
536 
--- NEU LA TAM UNG NHA CUNG CAP THI CHECK XEM KI TAM UNG DO DA HOAN THANH HAY CHUA, NEU DA HOAN THANH THI KHONG CHO DUYET
537 
DECLARE @PAY_ID VARCHAR(15), @SUM_OF_PAY_ID DECIMAL(18,2), @SUM_ADVANCE DECIMAL(18,2),
538 
@AMOUNT DECIMAL(18,2),@AMT_PAY_DO DECIMAL(18,2),@SCH_ID VARCHAR(15),@AMT_ADVANCE DECIMAL(18,0)
539 
DECLARE CUR_SH CURSOR FOR SELECT A.PAY_ID,A.AMT_PAY, A.AMT_PAY_DO,A.SCHEDULE_ID,A.AMT_ADVANCE FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID= @p_REQ_PAY_ID
540 
OPEN CUR_SH
541 
FETCH NEXT FROM CUR_SH INTO @PAY_ID,@AMOUNT,@AMT_PAY_DO,@SCH_ID,@AMT_ADVANCE
542 
WHILE @@FETCH_STATUS =0
543 
BEGIN
544 
SET @SUM_OF_PAY_ID =(SELECT SUM(AMT_PAY_DO) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A')
545 
SET @SUM_ADVANCE =(SELECT SUM(AMT_ADVANCE) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A')
546 
IF(@SUM_OF_PAY_ID +@AMT_PAY_DO >@AMOUNT)
547 
BEGIN
548 
ROLLBACK TRANSACTION
549 
SELECT '-1' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'S? ti?n t?m ?ng ? v??t m?c s? ti?n c?n l?i c?n ph?i thanh to?n'
550 
RETURN '-1'
551 
END
552 
IF(@SUM_OF_PAY_ID+@AMT_PAY_DO+@SUM_ADVANCE < @AMOUNT)
553 
BEGIN
554 
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTU' WHERE SCHEDULE_ID=@SCH_ID
555 
END
556 
ELSE IF(@SUM_OF_PAY_ID+@AMT_PAY_DO+@SUM_ADVANCE = @AMOUNT)
557 
BEGIN
558 
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTT' WHERE SCHEDULE_ID=@SCH_ID
559 
END
560 
ELSE IF(@SUM_OF_PAY_ID=0 AND @SUM_ADVANCE =0 )
561 
BEGIN
562 
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='CTT' WHERE SCHEDULE_ID=@SCH_ID
563 
END
564 
FETCH NEXT FROM CUR_SH INTO @PAY_ID,@AMOUNT,@AMT_PAY_DO,@SCH_ID,@AMT_ADVANCE
565 
END
566 
CLOSE CUR_SH
567 
DEALLOCATE CUR_SH
568
569 
UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS='A', CHECKER_ID=@p_CHECKER_ID, APPROVE_DT = GETDATE(),AUTH_STATUS_KT='U' WHERE REQ_PAY_ID =@p_REQ_PAY_ID
570 
SET @p_AUTH_STATUS ='A'
571 
--- INSERT 1 DONG VAO PL_PROCESS
572 
-- INSERT VAO BANG PL_PROCESS 1 DONG TRA VE
573 
INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
574 
VALUES(@p_REQ_PAY_ID,'APP',@p_CHECKER_ID,GETDATE(), N'Tr??ng ?n v? ? ph? duy?t',N'Tr??ng ?n v? ph? duy?t')
575 
END
576 
IF @@Error <> 0 GOTO ABORT
577 
COMMIT TRANSACTION
578 
IF(@p_AUTH_STATUS='A')
579 
BEGIN
580 
SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,N'12345' +@DEP_CODE_NEXT ErrorDesc
581 
RETURN '0'
582 
END
583 
ELSE
584 
BEGIN
585 
SELECT '1' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,'' ErrorDesc
586 
RETURN '1'
587 
END
588 
ABORT:
589 
BEGIN
590 
ROLLBACK TRANSACTION
591 
SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
592 
RETURN '-1'
593 
END
594
595 
Result ErrorDesc
596 
SUCCESSFULL
597