Project

General

Profile

Download (45 KB)

PL_REQUEST_DOC_APP_V2.txt

Truong Nguyen Vu, 01/27/2021 02:27 PM

 
1 
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_App]
2 
@p_REQ_ID VARCHAR(15) = NULL,
3 
@p_AUTH_STATUS VARCHAR(1) = NULL,
4 
@p_CHECKER_ID VARCHAR(15) = NULL,
5 
@p_APPROVE_DT DATETIME = NULL,
6 
@p_ROLE_LOGIN VARCHAR(50) = NULL,
7 
@p_BRANCH_LOGIN VARCHAR(15),
8 
@p_PROCESS_DES NVARCHAR(500)
9 
AS
10
11 
BEGIN TRANSACTION;
12 
---LUCTV KIEM TRA NEU TO TRINH DANG BI TRA VE THI KHONG DUOC PHEP DUYET
13 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='R' AND REQ_ID =@p_REQ_ID))
14 
BEGIN
15 
ROLLBACK TRANSACTION
16 
SELECT '-1' as Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc
17 
RETURN '-1'
18 
END
19 
--SET @p_APPROVE_DT = @p_APPROVE_DT
20
21 
--Validation is here
22 
DECLARE @ERRORSYS NVARCHAR(15) = '';
23 
IF (NOT EXISTS (SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
24 
SET @ERRORSYS = 'REQ-00002';
25 
IF @ERRORSYS <> ''
26 
BEGIN
27 
ROLLBACK TRANSACTION;
28 
SELECT ErrorCode Result,
29 
ErrorDesc ErrorDesc
30 
FROM SYS_ERROR
31 
WHERE ErrorCode = @ERRORSYS;
32 
RETURN '0';
33 
END;
34 
DECLARE @ERROR BIT ,@EROOR_DES NVARCHAR(500)
35 
SELECT @ERROR=ERROR,
36 
@EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'APPNEW','PL_REQUEST_DOC',@p_CHECKER_ID,'APPNEW')
37 
IF(@ERROR=1)
38 
BEGIN
39 
ROLLBACK TRANSACTION;
40 
SELECT '-1' Result,
41 
@EROOR_DES ErrorDesc
42
43 
RETURN '0';
44 
END
45 
--UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
46 
--WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))
47 
DECLARE @BRANCH_TYPE_LOGIN VARCHAR(15)
48 
SET @BRANCH_TYPE_LOGIN = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
49 
DECLARE @Result VARCHAR(5),
50 
@TOTAL_TRANSFER DECIMAL(18, 0),
51 
@TOTAL_AMT DECIMAL(18, 0),
52 
@ROLE_USER_NOTIFI VARCHAR(50),
53 
@ROLE_ID VARCHAR(20),
54 
@ROLE_TF VARCHAR(20),
55 
@LIMIT_VALUE DECIMAL(18, 0),
56 
@STEP_CURR VARCHAR(20),
57 
@STEP_PARENT VARCHAR(20),
58 
@COST_ID VARCHAR(20),
59 
@FR_BRANCH_ID VARCHAR(20),
60 
@FR_DEP_ID VARCHAR(20),
61 
@DVDM_ID VARCHAR(20),
62 
@IS_NEXT BIT = 0,
63 
@IS_NEXT_CDT BIT = 0,
64 
@TOTAL_AMT_GD DECIMAL(12, 0),
65 
@STOP BIT,
66 
@NOTES NVARCHAR(100);
67 
DECLARE @ROLE_CDT VARCHAR(20),
68 
@DVDM_CDT VARCHAR(20),
69 
@LIMIT_VALUE_CDT VARCHAR(20),
70 
@NOTES_CDT VARCHAR(20);
71 
DECLARE @PROCESS_ID VARCHAR(5),@DVDM_NAME NVARCHAR(20)
72 
DECLARE @BRANCH_PARENT VARCHAR(15)
73 
DECLARE @DATA_DVDM TABLE
74 
(
75 
DVDM_ID VARCHAR(20),
76 
TOTAL_AMT DECIMAL(12, 0),
77 
IS_PTGD BIT
78 
);
79 
--UPDATE dbo.PL_REQUEST_COSTCENTER
80 
--SET DVMD_ID=(SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE PL_COSTCENTER.COST_ID=PL_REQUEST_COSTCENTER.COST_ID),
81 
--TOTAL_AMT_GD=(SELECT SUM(PM.TOTAL_AMT) AS AMT FROM
82 
--(SELECT PLAN_ID,GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT WHERE REQDT_TYPE='I' AND REQ_ID=@p_REQ_ID) PR
83 
--LEFT JOIN dbo.PL_MASTER PM ON PR.PLAN_ID=PM.PLAN_ID
84 
--WHERE PM.COST_ID=PL_REQUEST_COSTCENTER.COST_ID)
85 
--WHERE REQ_ID=@p_REQ_ID
86 
INSERT INTO @DATA_DVDM
87 
SELECT KHOI_ID,
88 
SUM(TOTAL_AMT) AS TOTAL_AMT,DM.IS_PTGD
89 
FROM dbo.PL_REQUEST_DOC_DT DT
90 
LEFT JOIN CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1
91 
WHERE REQ_ID = @p_REQ_ID AND DT.KHOI_ID IS NOT NULL AND DT.KHOI_ID <>''
92 
GROUP BY KHOI_ID,DM.IS_PTGD;
93
94 
SET @DVDM_CDT = (SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='CDT')
95
96
97
98
99
100 
DELETE FROM dbo.PL_REQUEST_PROCESS
101 
WHERE REQ_ID = @p_REQ_ID;
102 
DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10),
103 
@BRANCH_CREATE_TYPE VARCHAR(10)
104
105
106 
SELECT @BRANCH_ID =BRANCH_ID,@DEP_ID=DEP_ID,@BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
107
108 
SET @BRANCH_TYPE=(SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
109 
SET @BRANCH_CREATE_TYPE=(SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
110
111
112 
--IF(@BRANCH_TYPE='PGD')
113 
--SET @BRANCH_ID=(SELECT TOP 1 FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
114
115
116 
-- KIEM TRA XEM CO CAP PHE DUYET TRUNG GIAN HAY KHONG 20 05 2020
117 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN'))
118 
BEGIN
119
120 
DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID
121 
INSERT INTO dbo.PL_PROCESS
122 
(
123 
REQ_ID,
124 
PROCESS_ID,
125 
CHECKER_ID,
126 
APPROVE_DT,
127 
PROCESS_DESC,NOTES
128 
)
129 
VALUES
130 
( @p_REQ_ID, -- REQ_ID - varchar(15)
131 
'SIGN', -- PROCESS_ID - varchar(10)
132 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
133 
@p_APPROVE_DT , -- APPROVE_DT - datetime
134 
N'Cấp phê duyệt trung gian xác nhận tờ trình chủ trương',
135 
N'Cấp phê duyệt trung gian'
136 
)
137 
--- DUA CAP PHE DUYET TRUONG DON VI
138 
INSERT INTO dbo.PL_REQUEST_PROCESS
139 
(
140 
REQ_ID,
141 
PROCESS_ID,
142 
STATUS,
143 
ROLE_USER,
144 
BRANCH_ID,
145 
DEP_ID,
146 
CHECKER_ID,
147 
APPROVE_DT,
148 
PARENT_PROCESS_ID,
149 
IS_LEAF,
150 
COST_ID,
151 
DVDM_ID,
152 
NOTES,
153 
IS_HAS_CHILD
154 
)
155 
VALUES
156 
( @p_REQ_ID, -- REQ_ID - varchar(15)
157 
'APPNEW', -- PROCESS_ID - varchar(10)
158 
'C', -- STATUS - varchar(5)
159 
'GDDV', -- ROLE_USER - varchar(50)
160 
--@BRANCH_CREATE,
161 
@BRANCH_ID,
162 
@DEP_ID, -- BRANCH_ID - varchar(15)
163 
'', -- CHECKER_ID - varchar(15)
164 
NULL, -- APPROVE_DT - datetime
165 
'', -- PARENT_PROCESS_ID - varchar(10)
166 
'N', -- IS_LEAF - varchar(1)
167 
'', -- COST_ID - varchar(15)
168 
'', -- DVDM_ID - varchar(15)
169 
N'Chờ trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500)
170 
NULL -- IS_HAS_CHILD - bit
171 
)
172 
--- UPDATE PROCESS_ID VE APP_NEW
173 
UPDATE PL_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID
174 
END
175 
ELSE
176 
BEGIN -- NGUOC LAI LA GIAM DOC DON VI PHE DUYET
177 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> ''))
178 
BEGIN
179 
IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ_ID))
180 
BEGIN
181 
ROLLBACK TRANSACTION
182 
SELECT '-1' Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác
183 
nhận phiếu!' ErrorDesc
184 
RETURN '-1'
185 
END
186 
END
187 
INSERT INTO dbo.PL_REQUEST_PROCESS
188 
(
189 
REQ_ID,
190 
PROCESS_ID,
191 
STATUS,
192 
ROLE_USER,
193 
BRANCH_ID,
194 
DEP_ID,
195 
CHECKER_ID,
196 
APPROVE_DT,
197 
PARENT_PROCESS_ID,
198 
IS_LEAF,
199 
NOTES
200 
)
201 
VALUES
202 
( @p_REQ_ID, -- REQ_ID - varchar(15)
203 
'APPNEW', -- PROCESS_ID - varchar(10)
204 
'P', -- STATUS - varchar(5)
205 
'GDDV', -- ROLE_USER - varchar(50)
206 
@BRANCH_ID ,
207 
@DEP_ID, -- BRANCH_ID - varchar(15)
208 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
209 
GETDATE() , -- APPROVE_DT - datetime
210 
NULL, 'N', N'Trưởng đơn vị phê duyệt');
211 
SET @STEP_CURR = 'APPNEW';
212 
SET @STEP_PARENT = 'APPNEW';
213
214
215
216
217
218 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PL_BASED_ID IS NOT NULL AND PL_BASED_ID <>''))
219 
BEGIN
220 
DECLARE @ROLE_KT VARCHAR(20), @DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(500)
221 
SET @ROLE_KT=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
222
223 
SET @DVDM_KT=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')
224 
SET @NOTES_KT = (SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDVAL='KT' AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')
225
226 
IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_KT AND ( DVDM_ID=@DVDM_KT OR @DVDM_KT IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
227 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
228 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
229 
BEGIN
230 
INSERT INTO dbo.PL_REQUEST_PROCESS
231 
(
232 
REQ_ID,
233 
PROCESS_ID,
234 
STATUS,
235 
ROLE_USER,
236 
BRANCH_ID,
237 
CHECKER_ID,
238 
APPROVE_DT,
239 
PARENT_PROCESS_ID,
240 
IS_LEAF,
241 
COST_ID,
242 
DVDM_ID,
243 
NOTES,
244 
IS_HAS_CHILD
245 
)
246 
VALUES
247 
( @p_REQ_ID, -- REQ_ID - varchar(15)
248 
'KT', -- PROCESS_ID - varchar(10)
249 
'U', -- STATUS - varchar(5)
250 
@ROLE_KT, -- ROLE_USER - varchar(50)
251 
'', -- BRANCH_ID - varchar(15)
252 
'', -- CHECKER_ID - varchar(15)
253 
NULL, -- APPROVE_DT - datetime
254 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
255 
'N', -- IS_LEAF - varchar(1)
256 
'', -- COST_ID - varchar(15)
257 
@DVDM_KT, N'Chờ phòng kế toán xác nhận', 1
258 
-- DVDM_ID - varchar(15)
259 
);
260
261 
SET @STEP_PARENT='KT'
262 
END
263
264 
IF (EXISTS
265 
(
266 
SELECT REQ_COST_ID
267 
FROM dbo.PL_REQUEST_COSTCENTER
268 
WHERE REQ_ID = @p_REQ_ID
269 
)
270 
)
271 
BEGIN
272 
DECLARE lstCostCenter CURSOR FOR
273 
SELECT COST_ID
274 
FROM dbo.PL_REQUEST_COSTCENTER
275 
WHERE REQ_ID = @p_REQ_ID AND COST_ID IS NOT NULL AND COST_ID <>'';
276 
OPEN lstCostCenter;
277 
FETCH NEXT FROM lstCostCenter
278 
INTO @COST_ID;
279 
WHILE @@FETCH_STATUS = 0
280 
BEGIN
281 
IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND (ROLE_USER='GDDV' OR ROLE_USER IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') )AND ( DVDM_ID=@COST_ID OR @COST_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
282 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
283 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
284 
BEGIN
285 
INSERT INTO dbo.PL_REQUEST_PROCESS
286 
(
287 
REQ_ID,
288 
PROCESS_ID,
289 
STATUS,
290 
ROLE_USER,
291 
BRANCH_ID,
292 
CHECKER_ID,
293 
APPROVE_DT,
294 
PARENT_PROCESS_ID,
295 
IS_LEAF,
296 
COST_ID,
297 
DVDM_ID,
298 
NOTES,
299 
IS_HAS_CHILD
300 
)
301 
VALUES
302 
( @p_REQ_ID, -- REQ_ID - varchar(15)
303 
'DVCM', -- PROCESS_ID - varchar(10)
304 
'U', -- STATUS - varchar(5)
305 
'GDDV', -- ROLE_USER - varchar(50)
306 
'', -- BRANCH_ID - varchar(15)
307 
'', -- CHECKER_ID - varchar(15)
308 
NULL, -- APPROVE_DT - datetime
309 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
310 
'N', -- IS_LEAF - varchar(1)
311 
'', -- COST_ID - varchar(15)
312 
@COST_ID, N'Chờ đơn vị chuyên môn xác nhận', 1
313 
-- DVDM_ID - varchar(15)
314 
);
315 
END
316 
ELSE
317 
BEGIN
318
319 
UPDATE PL_REQUEST_COSTCENTER SET AUTH_STATUS ='A',NOTES=N'Đồng ý' WHERE 1= 1 AND
320 
REQ_ID=@p_REQ_ID AND COST_ID=@COST_ID
321
322
323
324 
END
325
326 
FETCH NEXT FROM lstCostCenter
327 
INTO @COST_ID;
328 
END;
329 
CLOSE lstCostCenter;
330 
DEALLOCATE lstCostCenter;
331 
IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))
332 
SET @STEP_PARENT = 'DVCM';
333 
END;
334 
SET @TOTAL_AMT =
335 
(
336 
SELECT SUM(TOTAL_AMT) AS TOTAL_AMT
337 
FROM dbo.PL_REQUEST_DOC_DT
338 
WHERE REQ_ID = @p_REQ_ID
339 
);
340 
IF (EXISTS
341 
(
342 
SELECT REQ_TRANSFER_ID
343 
FROM dbo.PL_REQUEST_TRANSFER
344 
WHERE REQ_DOC_ID = @p_REQ_ID
345 
)
346 
)
347 
BEGIN
348
349
350 
IF (EXISTS
351 
(
352 
SELECT FR_BRN_ID
353 
FROM dbo.PL_REQUEST_TRANSFER
354 
WHERE REQ_DOC_ID = @p_REQ_ID
355 
AND
356 
(
357 
FR_BRN_ID <> @BRANCH_CREATE
358 
OR FR_DEP_ID <> @DEP_CREATE
359 
)
360 
AND NOT EXISTS(SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_ALL' AND BRANCH_ID=FR_BRN_ID AND DEP_ID=FR_DEP_ID)
361 
)
362 
)
363 
BEGIN
364 
DECLARE lstTransfer CURSOR FOR
365 
SELECT FR_BRN_ID,
366 
FR_DEP_ID
367 
FROM dbo.PL_REQUEST_TRANSFER
368 
WHERE REQ_DOC_ID = @p_REQ_ID
369 
AND
370 
(
371 
FR_BRN_ID <> @BRANCH_CREATE
372 
OR FR_DEP_ID <> @DEP_CREATE
373 
)
374 
AND NOT EXISTS(SELECT * FROM dbo.PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='TRADE_USER_ALL' AND BRANCH_ID=FR_BRN_ID AND DEP_ID=FR_DEP_ID)
375 
GROUP BY FR_BRN_ID,
376 
FR_DEP_ID;
377 
OPEN lstTransfer;
378 
FETCH NEXT FROM lstTransfer
379 
INTO @FR_BRANCH_ID,
380 
@FR_DEP_ID;
381 
WHILE @@FETCH_STATUS = 0
382 
BEGIN
383 
INSERT INTO dbo.PL_REQUEST_PROCESS
384 
(
385 
REQ_ID,
386 
PROCESS_ID,
387 
STATUS,
388 
ROLE_USER,
389 
BRANCH_ID,
390 
CHECKER_ID,
391 
APPROVE_DT,
392 
PARENT_PROCESS_ID,
393 
IS_LEAF,
394 
COST_ID,
395 
DVDM_ID,
396 
NOTES,
397 
IS_HAS_CHILD,
398 
DEP_ID
399 
)
400 
VALUES
401 
( @p_REQ_ID, -- REQ_ID - varchar(15)
402 
'DVDC', -- PROCESS_ID - varchar(10)
403 
'U', -- STATUS - varchar(5)
404 
'GDDV', -- ROLE_USER - varchar(50)
405 
@FR_BRANCH_ID, -- BRANCH_ID - varchar(15)
406 
'', -- CHECKER_ID - varchar(15)
407 
NULL, -- APPROVE_DT - datetime
408 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
409 
'N', -- IS_LEAF - varchar(1)
410 
'', -- COST_ID - varchar(15)
411 
'', -- DVDM_ID - varchar(15)
412 
N'Chờ đơn vị điều chuyển xác nhận', 1, @FR_DEP_ID);
413 
FETCH NEXT FROM lstTransfer
414 
INTO @FR_BRANCH_ID,
415 
@FR_DEP_ID;
416 
END;
417 
CLOSE lstTransfer;
418 
DEALLOCATE lstTransfer;
419 
SET @STEP_PARENT = 'DVDC';
420 
END;
421
422 
-- Đầu mối nhận
423 
DECLARE lstTransfer CURSOR FOR
424 
SELECT TO_DVDM_ID
425 
FROM dbo.PL_REQUEST_TRANSFER
426 
WHERE REQ_DOC_ID = @p_REQ_ID AND TO_DVDM_ID IS NOT NULL AND TO_DVDM_ID <>''
427 
GROUP BY TO_DVDM_ID;
428 
OPEN lstTransfer;
429 
FETCH NEXT FROM lstTransfer
430 
INTO @DVDM_ID;
431 
WHILE @@FETCH_STATUS = 0
432 
BEGIN
433 
IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND (ROLE_USER='GDDV' OR ROLE_USER IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') ) AND ( DVDM_ID=@DVDM_ID OR @DVDM_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
434 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
435 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
436
437 
BEGIN
438 
INSERT INTO dbo.PL_REQUEST_PROCESS
439 
(
440 
REQ_ID,
441 
PROCESS_ID,
442 
STATUS,
443 
ROLE_USER,
444 
BRANCH_ID,
445 
CHECKER_ID,
446 
APPROVE_DT,
447 
PARENT_PROCESS_ID,
448 
IS_LEAF,
449 
COST_ID,
450 
DVDM_ID,
451 
NOTES,
452 
IS_HAS_CHILD
453 
)
454 
VALUES
455 
( @p_REQ_ID, -- REQ_ID - varchar(15)
456 
'DVDM_DC', -- PROCESS_ID - varchar(10)
457 
'U', -- STATUS - varchar(5)
458 
'GDDV', -- ROLE_USER - varchar(50)
459 
'', -- BRANCH_ID - varchar(15)
460 
'', -- CHECKER_ID - varchar(15)
461 
NULL, -- APPROVE_DT - datetime
462 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
463 
'N', -- IS_LEAF - varchar(1)
464 
'', -- COST_ID - varchar(15)
465 
@DVDM_ID, -- DVDM_ID - varchar(15)
466 
N'Chờ đơn vị đầu mối xác nhận', 0);
467 
END
468 
FETCH NEXT FROM lstTransfer
469 
INTO @DVDM_ID;
470 
END;
471 
CLOSE lstTransfer;
472 
DEALLOCATE lstTransfer;
473
474
475 
IF (EXISTS
476 
(
477 
SELECT FR_BRN_ID
478 
FROM dbo.PL_REQUEST_TRANSFER
479 
WHERE REQ_DOC_ID = @p_REQ_ID
480 
AND FR_BRN_ID = @BRANCH_CREATE
481 
AND FR_DEP_ID = @DEP_CREATE
482 
)
483 
)
484 
BEGIN
485 
-- Đầu mối cho
486 
DECLARE lstTransfer CURSOR FOR
487 
SELECT FR_DVDM_ID
488 
FROM dbo.PL_REQUEST_TRANSFER
489 
WHERE REQ_DOC_ID = @p_REQ_ID
490 
AND FR_BRN_ID = @BRANCH_CREATE
491 
AND FR_DEP_ID = @DEP_CREATE
492 
AND FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>''
493 
AND NOT EXISTS
494 
(
495 
SELECT *
496 
FROM dbo.PL_REQUEST_PROCESS
497 
WHERE REQ_ID = @p_REQ_ID
498 
AND PROCESS_ID = 'DVDM_DC'
499 
AND DVDM_ID = FR_DVDM_ID
500 
)
501 
GROUP BY FR_DVDM_ID;
502 
OPEN lstTransfer;
503 
FETCH NEXT FROM lstTransfer
504 
INTO @DVDM_ID;
505 
WHILE @@FETCH_STATUS = 0
506 
BEGIN
507 
IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND (ROLE_USER='GDDV' OR ROLE_USER IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') ) AND ( DVDM_ID=@DVDM_ID OR @DVDM_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
508 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
509 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
510 
BEGIN
511 
INSERT INTO dbo.PL_REQUEST_PROCESS
512 
(
513 
REQ_ID,
514 
PROCESS_ID,
515 
STATUS,
516 
ROLE_USER,
517 
BRANCH_ID,
518 
CHECKER_ID,
519 
APPROVE_DT,
520 
PARENT_PROCESS_ID,
521 
IS_LEAF,
522 
COST_ID,
523 
DVDM_ID,
524 
NOTES,
525 
IS_HAS_CHILD
526 
)
527 
VALUES
528 
( @p_REQ_ID, -- REQ_ID - varchar(15)
529 
'DVDM_DC', -- PROCESS_ID - varchar(10)
530 
'U', -- STATUS - varchar(5)
531 
'GDDV', -- ROLE_USER - varchar(50)
532 
'', -- BRANCH_ID - varchar(15)
533 
'', -- CHECKER_ID - varchar(15)
534 
NULL, -- APPROVE_DT - datetime
535 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
536 
'N', -- IS_LEAF - varchar(1)
537 
'', -- COST_ID - varchar(15)
538 
@DVDM_ID, -- DVDM_ID - varchar(15)
539 
N'Chờ đơn vị đầu mối xác nhận', 0);
540 
END
541 
FETCH NEXT FROM lstTransfer
542 
INTO @DVDM_ID;
543 
END;
544 
CLOSE lstTransfer;
545 
DEALLOCATE lstTransfer;
546
547
548
549
550 
IF(EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDM_DC'))
551 
SET @STEP_PARENT='DVDM_DC'
552
553
554 
END;
555
556
557
558 
INSERT INTO dbo.PL_REQUEST_PROCESS
559 
(
560 
REQ_ID,
561 
PROCESS_ID,
562 
STATUS,
563 
ROLE_USER,
564 
BRANCH_ID,
565 
CHECKER_ID,
566 
APPROVE_DT,
567 
PARENT_PROCESS_ID,
568 
IS_LEAF,
569 
COST_ID,
570 
DVDM_ID,
571 
NOTES,IS_HAS_CHILD
572 
)
573 
VALUES
574 
( @p_REQ_ID, -- REQ_ID - varchar(15)
575 
'TC', -- PROCESS_ID - varchar(10)
576 
'U', -- STATUS - varchar(5)
577 
'TC', -- ROLE_USER - varchar(50)
578 
'', -- BRANCH_ID - varchar(15)
579 
'', -- CHECKER_ID - varchar(15)
580 
NULL, -- APPROVE_DT - datetime
581 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
582 
'N', -- IS_LEAF - varchar(1)
583 
'', -- COST_ID - varchar(15)
584 
'', -- DVDM_ID - varchar(15)
585 
N'Chờ đơn vị Tài chính xác nhận',1);
586 
SET @STEP_PARENT = 'TC';
587
588
589 
IF (NOT EXISTS
590 
(
591 
SELECT REQ_TRANSFER_ID
592 
FROM dbo.PL_REQUEST_TRANSFER
593 
WHERE REQ_DOC_ID = @p_REQ_ID
594 
AND
595 
(
596 
FR_BRN_ID <> @BRANCH_CREATE
597 
OR FR_DEP_ID <> @DEP_CREATE
598 
)
599 
)
600 
)
601 
BEGIN
602 
---Duyệt DC
603 
IF (
604 
(
605 
SELECT COUNT(T.FR_KHOI_ID) AS COUNT_ROW
606 
FROM
607 
(
608 
SELECT FR_KHOI_ID
609 
FROM dbo.PL_REQUEST_TRANSFER
610 
WHERE REQ_DOC_ID = @p_REQ_ID
611 
GROUP BY FR_KHOI_ID
612 
) T
613 
) > 1
614 
)
615 
BEGIN
616 
INSERT INTO dbo.PL_REQUEST_PROCESS
617 
(
618 
REQ_ID,
619 
PROCESS_ID,
620 
STATUS,
621 
ROLE_USER,
622 
BRANCH_ID,
623 
CHECKER_ID,
624 
APPROVE_DT,
625 
PARENT_PROCESS_ID,
626 
IS_LEAF,
627 
COST_ID,
628 
DVDM_ID,
629 
NOTES,
630 
IS_HAS_CHILD
631 
)
632 
VALUES
633 
( @p_REQ_ID, -- REQ_ID - varchar(15)
634 
'TGD_DC', -- PROCESS_ID - varchar(10)
635 
'U', -- STATUS - varchar(5)
636 
'TGD', -- ROLE_USER - varchar(50)
637 
'', -- BRANCH_ID - varchar(15)
638 
'', -- CHECKER_ID - varchar(15)
639 
NULL, -- APPROVE_DT - datetime
640 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
641 
'', -- IS_LEAF - varchar(1)
642 
'', -- COST_ID - varchar(15)
643 
'', -- DVDM_ID - varchar(15)
644 
N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
645 
NULL -- IS_HAS_CHILD - bit
646 
);
647 
SET @STEP_PARENT = 'TGD_DC';
648 
END;
649 
ELSE
650 
BEGIN
651 
DECLARE @LIMTT_MAX DECIMAL(18, 2),
652 
@LIMIT_APP DECIMAL(18, 2),
653 
@KHOI_ID_TF VARCHAR(20);
654 
SET @KHOI_ID_TF =
655 
(
656 
SELECT TOP 1
657 
FR_KHOI_ID
658 
FROM dbo.PL_REQUEST_TRANSFER
659 
WHERE REQ_DOC_ID = @p_REQ_ID
660 
);
661
662 
IF(@KHOI_ID_TF IS NOT NULL AND @KHOI_ID_TF<>'')
663 
BEGIN
664 
SET @LIMIT_APP =
665 
(
666 
SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
667 
FROM dbo.LIMIT_ACCUMULATE
668 
WHERE ROLE_ID = 'GDK'
669 
AND DVDM_ID = @KHOI_ID_TF
670 
);
671 
SET @TOTAL_TRANSFER =
672 
(
673 
SELECT SUM(TOTAL_AMT) AS TOTAL
674 
FROM dbo.PL_REQUEST_TRANSFER
675 
WHERE REQ_DOC_ID = @p_REQ_ID
676 
);
677 
SET @LIMTT_MAX =
678 
(
679 
SELECT LIMIT_VALUE
680 
FROM dbo.TL_SYSROLE_LIMIT
681 
WHERE ROLE_ID = 'GDK'
682 
AND LIMIT_TYPE = 'DCNS'
683 
);
684
685 
INSERT INTO dbo.PL_REQUEST_PROCESS
686 
(
687 
REQ_ID,
688 
PROCESS_ID,
689 
STATUS,
690 
ROLE_USER,
691 
BRANCH_ID,
692 
CHECKER_ID,
693 
APPROVE_DT,
694 
PARENT_PROCESS_ID,
695 
IS_LEAF,
696 
COST_ID,
697 
DVDM_ID,
698 
NOTES,
699 
IS_HAS_CHILD
700 
)
701 
VALUES
702 
( @p_REQ_ID, -- REQ_ID - varchar(15)
703 
'GDK_DC', -- PROCESS_ID - varchar(10)
704 
'U', -- STATUS - varchar(5)
705 
'GDK', -- ROLE_USER - varchar(50)
706 
'', -- BRANCH_ID - varchar(15)
707 
'', -- CHECKER_ID - varchar(15)
708 
GETDATE(), -- APPROVE_DT - datetime
709 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
710 
'N', -- IS_LEAF - varchar(1)
711 
'', -- COST_ID - varchar(15)
712 
@KHOI_ID_TF, -- DVDM_ID - varchar(15)
713 
N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
714 
NULL -- IS_HAS_CHILD - bit
715 
);
716 
SET @STEP_PARENT = 'GDK_DC';
717
718 
UPDATE dbo.PL_REQUEST_PROCESS SET ROLE_USER='PTGD' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_DC' AND NOT EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE CM_DVDM.DVDM_ID=dbo.PL_REQUEST_PROCESS.DVDM_ID AND IS_GDK=1)
719
720
721 
IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP)
722 
BEGIN
723 
IF(EXISTS(SELECT TOP 1 DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=1))
724 
BEGIN
725 
IF(NOT EXISTS (SELECT DVDM_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='PTGD' AND DVDM_ID=@KHOI_ID_TF))
726 
BEGIN
727 
INSERT INTO dbo.PL_REQUEST_PROCESS
728 
(
729 
REQ_ID,
730 
PROCESS_ID,
731 
STATUS,
732 
ROLE_USER,
733 
BRANCH_ID,
734 
CHECKER_ID,
735 
APPROVE_DT,
736 
PARENT_PROCESS_ID,
737 
IS_LEAF,
738 
COST_ID,
739 
DVDM_ID,
740 
NOTES,
741 
IS_HAS_CHILD
742 
)
743 
VALUES
744 
( @p_REQ_ID, -- REQ_ID - varchar(15)
745 
'PTGD_DC', -- PROCESS_ID - varchar(10)
746 
'U', -- STATUS - varchar(5)
747 
'PTGD', -- ROLE_USER - varchar(50)
748 
'', -- BRANCH_ID - varchar(15)
749 
'', -- CHECKER_ID - varchar(15)
750 
GETDATE(), -- APPROVE_DT - datetime
751 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
752 
'N', -- IS_LEAF - varchar(1)
753 
'', -- COST_ID - varchar(15)
754 
@KHOI_ID_TF, -- DVDM_ID - varchar(15)
755 
N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
756 
NULL -- IS_HAS_CHILD - bit
757 
);
758 
SET @STEP_PARENT = 'PTGD_DC';
759 
END
760 
SET @LIMTT_MAX =
761 
(
762 
SELECT LIMIT_VALUE
763 
FROM dbo.TL_SYSROLE_LIMIT
764 
WHERE ROLE_ID = 'PTGD'
765 
AND LIMIT_TYPE = 'DCNS'
766 
);
767 
SET @LIMIT_APP =
768 
(
769 
SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
770 
FROM dbo.LIMIT_ACCUMULATE
771 
WHERE ROLE_ID = 'PTGD'
772 
AND DVDM_ID = @KHOI_ID_TF
773 
);
774
775 
END
776 
IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP OR EXISTS(SELECT TOP 1 DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=0))
777 
BEGIN
778 
INSERT INTO dbo.PL_REQUEST_PROCESS
779 
(
780 
REQ_ID,
781 
PROCESS_ID,
782 
STATUS,
783 
ROLE_USER,
784 
BRANCH_ID,
785 
CHECKER_ID,
786 
APPROVE_DT,
787 
PARENT_PROCESS_ID,
788 
IS_LEAF,
789 
COST_ID,
790 
DVDM_ID,
791 
NOTES,
792 
IS_HAS_CHILD
793 
)
794 
VALUES
795 
( @p_REQ_ID, -- REQ_ID - varchar(15)
796 
'TGD_DC', -- PROCESS_ID - varchar(10)
797 
'U', -- STATUS - varchar(5)
798 
'TGD', -- ROLE_USER - varchar(50)
799 
'', -- BRANCH_ID - varchar(15)
800 
'', -- CHECKER_ID - varchar(15)
801 
NULL, -- APPROVE_DT - datetime
802 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
803 
'', -- IS_LEAF - varchar(1)
804 
'', -- COST_ID - varchar(15)
805 
'', -- DVDM_ID - varchar(15)
806 
N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
807 
NULL -- IS_HAS_CHILD - bit
808 
);
809 
SET @STEP_PARENT = 'TGD_DC';
810 
END;
811
812 
END;
813
814 
END
815
816
817 
END;
818 
----
819 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID LIKE '%_DC'))
820 
BEGIN
821 
DECLARE @DVDM_ID_TT VARCHAR(20)
822 
-- UPDATE dbo.LIMIT_ACCUMULATE SET TOTAL_APP_AMT = ISNULL(TOTAL_APP_AMT,0) + @TOTAL_TRANSFER WHERE ROLE_ID=@ROLE_USER AND DVDM_ID=@KHOI_ID_TF
823 
-- INSERT PL_TRADE_DETAIL
824
825 
--DECLARE @l_TRADE_ID VARCHAR(15)
826 
--EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @l_TRADE_ID out
827 
--IF @l_TRADE_ID ='' OR @l_TRADE_ID IS NULL GOTO ABORT
828
829 
--INSERT INTO PL_TRADEDETAIL
830 
--(
831 
-- [TRADE_ID],
832 
-- [PLAN_ID],
833 
-- [GOODS_ID],
834 
-- [GOODS_NAME],
835 
-- [GOODS_TYPE],
836 
-- [UNIT_ID],
837 
-- M1,
838 
-- M2,
839 
-- M3,
840 
-- M4,
841 
-- M5,
842 
-- M6,
843 
-- M7,
844 
-- M8,
845 
-- M9,
846 
-- M10,
847 
-- M11,
848 
-- M12,
849 
-- [QUANTITY],
850 
-- [QUANTITY_EXE],
851 
-- [PRICE],
852 
-- [START_DT_AMORT],
853 
-- [MONTH_AMORT],
854 
-- [END_DT_AMORT],
855 
-- [RATE_AMORT],
856 
-- [NOTES],
857 
-- [RECORD_STATUS],
858 
-- [MAKER_ID],
859 
-- [CREATE_DT],
860 
-- [AUTH_STATUS],
861 
-- [CHECKER_ID],
862 
-- [APPROVE_DT],AMT_RECEIVE_TF
863 
--)
864 
--VALUES
865 
--(
866 
-- @l_TRADE_ID,
867 
-- NULL,
868 
-- NULL,
869 
-- NULL,
870 
-- NULL,
871 
-- NULL,
872 
-- NULL,
873 
-- NULL,
874 
-- NULL,
875 
-- NULL,
876 
-- NULL,
877 
-- NULL,
878 
-- NULL,
879 
-- NULL,
880 
-- NULL,
881 
-- NULL,
882 
-- NULL,
883 
-- NULL,
884 
-- NULL,
885 
-- 0,
886 
-- 0,
887 
-- NULL,
888 
-- NULL,
889 
-- NULL,
890 
-- 0,
891 
-- '',
892 
-- '1', 'admin' ,GETDATE() ,'A' ,'admin' ,GETDATE(),0
893 
--)
894 
---
895 
BEGIN
896 
IF(@BRANCH_CREATE_TYPE='PGD')
897 
BEGIN
898
899
900 
SET @BRANCH_PARENT=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
901 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND BRANCH_ID=@BRANCH_PARENT AND (DEP_ID IS NULL OR DEP_ID='')))
902 
BEGIN
903 
INSERT INTO dbo.PL_REQUEST_PROCESS
904 
(
905 
REQ_ID,
906 
PROCESS_ID,
907 
STATUS,
908 
ROLE_USER,
909 
BRANCH_ID,
910 
DEP_ID,
911 
CHECKER_ID,
912 
APPROVE_DT,
913 
PARENT_PROCESS_ID,
914 
IS_LEAF,
915 
NOTES
916 
)
917 
VALUES
918 
(
919 
@p_REQ_ID, -- REQ_ID - varchar(15)
920 
'DVC', -- PROCESS_ID - varchar(10)
921 
'U', -- STATUS - varchar(5)
922 
'GDDV', -- ROLE_USER - varchar(50)
923 
@BRANCH_PARENT,
924 
NULL, -- BRANCH_ID - varchar(15)
925 
NULL, -- CHECKER_ID - varchar(15)
926 
NULL , -- APPROVE_DT - datetime
927 
@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');
928
929 
--SET @STEP_CURR = 'DVC';
930 
SET @STEP_PARENT = 'DVC';
931 
END
932
933 
END
934 
ELSE
935 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
936 
AND ((
937 
BRANCH_ID=@BRANCH_CREATE
938 
AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='')
939 
AND (DEP_ID IS NULL OR DEP_ID='')))
940 
)
941 
OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC
942 
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE PL_REQUEST_PROCESS.DVDM_ID=PC.DVDM_ID AND DEP_ID=@DEP_CREATE AND BRANCH_ID=@BRANCH_CREATE)
943 
)
944 
))
945 
BEGIN
946 
INSERT INTO dbo.PL_REQUEST_PROCESS
947 
(
948 
REQ_ID,
949 
PROCESS_ID,
950 
STATUS,
951 
ROLE_USER,
952 
BRANCH_ID,
953 
DEP_ID,
954 
CHECKER_ID,
955 
APPROVE_DT,
956 
PARENT_PROCESS_ID,
957 
IS_LEAF,
958 
NOTES
959 
)
960 
VALUES
961 
(
962 
@p_REQ_ID, -- REQ_ID - varchar(15)
963 
'DVC', -- PROCESS_ID - varchar(10)
964 
'U', -- STATUS - varchar(5)
965 
'GDDV', -- ROLE_USER - varchar(50)
966 
@BRANCH_CREATE,
967 
@DEP_CREATE, -- BRANCH_ID - varchar(15)
968 
NULL, -- CHECKER_ID - varchar(15)
969 
NULL , -- APPROVE_DT - datetime
970 
@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');
971
972 
--SET @STEP_CURR = 'DVC';
973 
SET @STEP_PARENT = 'DVC';
974 
END
975
976
977
978
979
980 
SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDDV'))
981 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDDV'))
982 
SET @TOTAL_AMT = (SELECT TOTAL_AMT FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
983 
IF((@IS_NEXT=1 OR @IS_NEXT_CDT=1 )AND NOT EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='TGD'))
984 
BEGIN
985 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID ) OR @IS_NEXT_CDT=1)
986 
BEGIN
987 
DECLARE lstCostCenter CURSOR FOR
988 
SELECT KHOI_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND KHOI_ID IS NOT NULL AND KHOI_ID <>'' AND NOT EXISTS(
989 
SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='GDK'
990 
)
991 
GROUP BY KHOI_ID
992 
OPEN lstCostCenter
993 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
994 
WHILE @@FETCH_STATUS = 0
995 
BEGIN
996 
INSERT INTO dbo.PL_REQUEST_PROCESS
997 
(
998 
REQ_ID,
999 
PROCESS_ID,
1000 
STATUS,
1001 
ROLE_USER,
1002 
BRANCH_ID,
1003 
CHECKER_ID,
1004 
APPROVE_DT,
1005 
PARENT_PROCESS_ID,
1006 
IS_LEAF,
1007 
COST_ID,
1008 
DVDM_ID,
1009 
NOTES,IS_HAS_CHILD
1010 
)
1011 
VALUES
1012 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1013 
'GDK_TT', -- PROCESS_ID - varchar(10)
1014 
'U', -- STATUS - varchar(5)
1015 
'GDK', -- ROLE_USER - varchar(50)
1016 
'', -- BRANCH_ID - varchar(15)
1017 
'', -- CHECKER_ID - varchar(15)
1018 
NULL, -- APPROVE_DT - datetime
1019 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1020 
'N', -- IS_LEAF - varchar(1)
1021 
'', -- COST_ID - varchar(15)
1022 
@DVDM_ID_TT ,
1023 
N'Chờ giám đốc khối xác nhận',
1024 
0 -- DVDM_ID - varchar(15)
1025 
)
1026
1027 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
1028 
END
1029 
CLOSE lstCostCenter
1030 
DEALLOCATE lstCostCenter
1031
1032
1033 
IF(@IS_NEXT_CDT=1 AND NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDK' AND DVDM_ID=@DVDM_CDT))
1034 
BEGIN
1035 
INSERT INTO dbo.PL_REQUEST_PROCESS
1036 
(
1037 
REQ_ID,
1038 
PROCESS_ID,
1039 
STATUS,
1040 
ROLE_USER,
1041 
BRANCH_ID,
1042 
CHECKER_ID,
1043 
APPROVE_DT,
1044 
PARENT_PROCESS_ID,
1045 
IS_LEAF,
1046 
COST_ID,
1047 
DVDM_ID,
1048 
NOTES,IS_HAS_CHILD
1049 
)
1050 
VALUES
1051 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1052 
'GDK_TT', -- PROCESS_ID - varchar(10)
1053 
'U', -- STATUS - varchar(5)
1054 
'GDK', -- ROLE_USER - varchar(50)
1055 
'', -- BRANCH_ID - varchar(15)
1056 
'', -- CHECKER_ID - varchar(15)
1057 
NULL, -- APPROVE_DT - datetime
1058 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1059 
'N', -- IS_LEAF - varchar(1)
1060 
'', -- COST_ID - varchar(15)
1061 
@DVDM_CDT ,
1062 
N'Chờ giám đốc khối xác nhận',
1063 
0 -- DVDM_ID - varchar(15)
1064 
)
1065
1066 
END
1067
1068 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT'))
1069 
BEGIN
1070 
SET @STEP_PARENT='GDK_TT'
1071 
END
1072 
UPDATE dbo.PL_REQUEST_PROCESS SET ROLE_USER='PTGD' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT' AND NOT EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE CM_DVDM.DVDM_ID=dbo.PL_REQUEST_PROCESS.DVDM_ID AND IS_GDK=1)
1073
1074
1075 
SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDK'))
1076 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDK'))
1077
1078 
IF(@IS_NEXT=1 OR @IS_NEXT_CDT=1)
1079 
BEGIN
1080 
IF(EXISTS(SELECT KHOI_ID FROM dbo.PL_REQUEST_DOC_DT DT
1081 
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID=@p_REQ_ID AND KHOI_ID IS NOT NULL AND KHOI_ID <>'' AND DM.IS_PTGD=1 AND NOT EXISTS(
1082 
SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='PTGD'
1083 
) ) OR @IS_NEXT_CDT=1)
1084 
BEGIN
1085 
DECLARE lstCostCenter CURSOR FOR
1086 
SELECT KHOI_ID FROM dbo.PL_REQUEST_DOC_DT DT
1087 
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID=@p_REQ_ID AND DM.IS_PTGD=1 AND NOT EXISTS(
1088 
SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='PTGD'
1089 
)
1090 
GROUP BY KHOI_ID
1091 
OPEN lstCostCenter
1092 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
1093 
WHILE @@FETCH_STATUS = 0
1094 
BEGIN
1095 
INSERT INTO dbo.PL_REQUEST_PROCESS
1096 
(
1097 
REQ_ID,
1098 
PROCESS_ID,
1099 
STATUS,
1100 
ROLE_USER,
1101 
BRANCH_ID,
1102 
CHECKER_ID,
1103 
APPROVE_DT,
1104 
PARENT_PROCESS_ID,
1105 
IS_LEAF,
1106 
COST_ID,
1107 
DVDM_ID,
1108 
NOTES,IS_HAS_CHILD
1109 
)
1110 
VALUES
1111 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1112 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1113 
'U', -- STATUS - varchar(5)
1114 
'PTGD', -- ROLE_USER - varchar(50)
1115 
'', -- BRANCH_ID - varchar(15)
1116 
'', -- CHECKER_ID - varchar(15)
1117 
NULL, -- APPROVE_DT - datetime
1118 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1119 
'N', -- IS_LEAF - varchar(1)
1120 
'', -- COST_ID - varchar(15)
1121 
@DVDM_ID_TT ,
1122 
N'Chờ phó tổng giám đốc khối xác nhận',
1123 
0 -- DVDM_ID - varchar(15)
1124 
)
1125
1126
1127 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
1128 
END
1129 
CLOSE lstCostCenter
1130 
DEALLOCATE lstCostCenter
1131
1132 
IF(@IS_NEXT_CDT=1 AND NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='PTGD' AND DVDM_ID=@DVDM_CDT))
1133 
BEGIN
1134 
INSERT INTO dbo.PL_REQUEST_PROCESS
1135 
(
1136 
REQ_ID,
1137 
PROCESS_ID,
1138 
STATUS,
1139 
ROLE_USER,
1140 
BRANCH_ID,
1141 
CHECKER_ID,
1142 
APPROVE_DT,
1143 
PARENT_PROCESS_ID,
1144 
IS_LEAF,
1145 
COST_ID,
1146 
DVDM_ID,
1147 
NOTES,IS_HAS_CHILD
1148 
)
1149 
VALUES
1150 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1151 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1152 
'U', -- STATUS - varchar(5)
1153 
'PTGD', -- ROLE_USER - varchar(50)
1154 
'', -- BRANCH_ID - varchar(15)
1155 
'', -- CHECKER_ID - varchar(15)
1156 
NULL, -- APPROVE_DT - datetime
1157 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1158 
'N', -- IS_LEAF - varchar(1)
1159 
'', -- COST_ID - varchar(15)
1160 
@DVDM_CDT ,
1161 
N'Chờ phó tổng giám đốc khối xác nhận',
1162 
0 -- DVDM_ID - varchar(15)
1163 
)
1164
1165 
END
1166
1167
1168 
SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'PTGD'))
1169 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'PTGD'))
1170 
END
1171 
IF(EXISTS(SELECT KHOI_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT DT
1172 
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID=@p_REQ_ID AND DM.IS_PTGD=0 ) AND (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDK'))=1)
1173 
BEGIN
1174 
SET @IS_NEXT=1
1175 
END
1176
1177 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='PTGDK_TT'))
1178 
BEGIN
1179 
SET @STEP_PARENT='PTGDK_TT'
1180 
END
1181
1182 
IF(@IS_NEXT=1 OR @IS_NEXT_CDT=1)
1183 
BEGIN
1184 
INSERT INTO dbo.PL_REQUEST_PROCESS
1185 
(
1186 
REQ_ID,
1187 
PROCESS_ID,
1188 
STATUS,
1189 
ROLE_USER,
1190 
BRANCH_ID,
1191 
CHECKER_ID,
1192 
APPROVE_DT,
1193 
PARENT_PROCESS_ID,
1194 
IS_LEAF,
1195 
COST_ID,
1196 
DVDM_ID,
1197 
NOTES,IS_HAS_CHILD
1198 
)
1199 
VALUES
1200 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1201 
'TGD', -- PROCESS_ID - varchar(10)
1202 
'U', -- STATUS - varchar(5)
1203 
'TGD', -- ROLE_USER - varchar(50)
1204 
'', -- BRANCH_ID - varchar(15)
1205 
'', -- CHECKER_ID - varchar(15)
1206 
NULL, -- APPROVE_DT - datetime
1207 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1208 
'N', -- IS_LEAF - varchar(1)
1209 
'', -- COST_ID - varchar(15)
1210 
'' ,
1211 
N'Chờ tổng giám đốc xác nhận',
1212 
0 -- DVDM_ID - varchar(15)
1213 
)
1214 
SET @STEP_PARENT='TGD'
1215 
IF((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) > (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='TCTT-HDQT'))
1216 
BEGIN
1217
1218
1219 
INSERT INTO dbo.PL_REQUEST_PROCESS
1220 
(
1221 
REQ_ID,
1222 
PROCESS_ID,
1223 
STATUS,
1224 
ROLE_USER,
1225 
BRANCH_ID,
1226 
CHECKER_ID,
1227 
APPROVE_DT,
1228 
PARENT_PROCESS_ID,
1229 
IS_LEAF,
1230 
COST_ID,
1231 
DVDM_ID,
1232 
NOTES,
1233 
IS_HAS_CHILD
1234 
)
1235 
VALUES
1236 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1237 
'HDQT', -- PROCESS_ID - varchar(10)
1238 
'U', -- STATUS - varchar(5)
1239 
'HDQT', -- ROLE_USER - varchar(50)
1240 
'', -- BRANCH_ID - varchar(15)
1241 
'', -- CHECKER_ID - varchar(15)
1242 
NULL, -- APPROVE_DT - datetime
1243 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1244 
'N', -- IS_LEAF - varchar(1)
1245 
'', -- COST_ID - varchar(15)
1246 
'', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
1247 
);
1248 
SET @STEP_PARENT = 'HDQT';
1249 
END
1250 
END
1251
1252 
--ELSE
1253 
--BEGIN
1254
1255 
--END
1256 
END
1257 
END
1258 
END
1259 
END
1260
1261
1262 
END
1263
1264 
END;
1265
1266
1267
1268
1269 
END;
1270 
ELSE
1271 
BEGIN
1272
1273 
IF(@BRANCH_CREATE_TYPE='PGD')
1274 
BEGIN
1275
1276 
SET @BRANCH_PARENT=(SELECT TOP 1 FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
1277 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND BRANCH_ID=@BRANCH_PARENT AND (DEP_ID IS NULL OR DEP_ID='')))
1278 
BEGIN
1279 
INSERT INTO dbo.PL_REQUEST_PROCESS
1280 
(
1281 
REQ_ID,
1282 
PROCESS_ID,
1283 
STATUS,
1284 
ROLE_USER,
1285 
BRANCH_ID,
1286 
DEP_ID,
1287 
CHECKER_ID,
1288 
APPROVE_DT,
1289 
PARENT_PROCESS_ID,
1290 
IS_LEAF,
1291 
NOTES
1292 
)
1293 
VALUES
1294 
(
1295 
@p_REQ_ID, -- REQ_ID - varchar(15)
1296 
'DVC', -- PROCESS_ID - varchar(10)
1297 
'U', -- STATUS - varchar(5)
1298 
'GDDV', -- ROLE_USER - varchar(50)
1299 
@BRANCH_PARENT,
1300 
NULL, -- BRANCH_ID - varchar(15)
1301 
NULL, -- CHECKER_ID - varchar(15)
1302 
NULL , -- APPROVE_DT - datetime
1303 
@STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt');
1304
1305 
SET @STEP_CURR = 'DVC';
1306 
SET @STEP_PARENT = 'DVC';
1307 
END
1308
1309 
END
1310 
ELSE
1311 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
1312 
AND ((
1313 
BRANCH_ID=@BRANCH_CREATE
1314 
AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='')
1315 
AND (DEP_ID IS NULL OR DEP_ID='')))
1316 
)
1317 
OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC
1318 
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE PL_REQUEST_PROCESS.DVDM_ID=PC.DVDM_ID AND DEP_ID=@DEP_CREATE AND BRANCH_ID=@BRANCH_CREATE)
1319 
)
1320 
))
1321 
BEGIN
1322 
INSERT INTO dbo.PL_REQUEST_PROCESS
1323 
(
1324 
REQ_ID,
1325 
PROCESS_ID,
1326 
STATUS,
1327 
ROLE_USER,
1328 
BRANCH_ID,
1329 
DEP_ID,
1330 
CHECKER_ID,
1331 
APPROVE_DT,
1332 
PARENT_PROCESS_ID,
1333 
IS_LEAF,
1334 
NOTES
1335 
)
1336 
VALUES
1337 
(
1338 
@p_REQ_ID, -- REQ_ID - varchar(15)
1339 
'DVC', -- PROCESS_ID - varchar(10)
1340 
'U', -- STATUS - varchar(5)
1341 
'GDDV', -- ROLE_USER - varchar(50)
1342 
@BRANCH_CREATE,
1343 
@DEP_CREATE, -- BRANCH_ID - varchar(15)
1344 
NULL, -- CHECKER_ID - varchar(15)
1345 
NULL , -- APPROVE_DT - datetime
1346 
@STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt');
1347
1348 
SET @STEP_CURR = 'DVC';
1349 
SET @STEP_PARENT = 'DVC';
1350 
END
1351 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDDV'))
1352 
IF(EXISTS( SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID) OR @IS_NEXT_CDT=1)
1353 
BEGIN
1354 
SET @IS_NEXT =
1355 
(
1356 
SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDDV')
1357 
);
1358
1359
1360
1361 
IF (@IS_NEXT = 1 OR @IS_NEXT_CDT=1)
1362 
BEGIN
1363
1364 
DECLARE lstCostCenter CURSOR FOR
1365 
SELECT DVDM_ID,
1366 
TOTAL_AMT
1367 
FROM @DATA_DVDM;
1368 
OPEN lstCostCenter;
1369 
FETCH NEXT FROM lstCostCenter
1370 
INTO @DVDM_ID,
1371 
@TOTAL_AMT_GD;
1372 
WHILE @@FETCH_STATUS = 0
1373 
BEGIN
1374 
INSERT INTO dbo.PL_REQUEST_PROCESS
1375 
(
1376 
REQ_ID,
1377 
PROCESS_ID,
1378 
STATUS,
1379 
ROLE_USER,
1380 
BRANCH_ID,
1381 
CHECKER_ID,
1382 
APPROVE_DT,
1383 
PARENT_PROCESS_ID,
1384 
IS_LEAF,
1385 
COST_ID,
1386 
DVDM_ID,
1387 
NOTES,
1388 
IS_HAS_CHILD
1389 
)
1390 
VALUES
1391 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1392 
'GDK_TT', -- PROCESS_ID - varchar(10)
1393 
'U', -- STATUS - varchar(5)
1394 
'GDK', -- ROLE_USER - varchar(50)
1395 
'', -- BRANCH_ID - varchar(15)
1396 
'', -- CHECKER_ID - varchar(15)
1397 
NULL, -- APPROVE_DT - datetime
1398 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1399 
'N', -- IS_LEAF - varchar(1)
1400 
'', -- COST_ID - varchar(15)
1401 
@DVDM_ID, N'Chờ giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
1402 
);
1403 
FETCH NEXT FROM lstCostCenter
1404 
INTO @DVDM_ID,
1405 
@TOTAL_AMT_GD;
1406 
END;
1407 
CLOSE lstCostCenter;
1408 
DEALLOCATE lstCostCenter;
1409
1410 
IF(@IS_NEXT_CDT=1 AND NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDK' AND DVDM_ID=@DVDM_CDT))
1411 
BEGIN
1412 
INSERT INTO dbo.PL_REQUEST_PROCESS
1413 
(
1414 
REQ_ID,
1415 
PROCESS_ID,
1416 
STATUS,
1417 
ROLE_USER,
1418 
BRANCH_ID,
1419 
CHECKER_ID,
1420 
APPROVE_DT,
1421 
PARENT_PROCESS_ID,
1422 
IS_LEAF,
1423 
COST_ID,
1424 
DVDM_ID,
1425 
NOTES,IS_HAS_CHILD
1426 
)
1427 
VALUES
1428 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1429 
'GDK_TT', -- PROCESS_ID - varchar(10)
1430 
'U', -- STATUS - varchar(5)
1431 
'GDK', -- ROLE_USER - varchar(50)
1432 
'', -- BRANCH_ID - varchar(15)
1433 
'', -- CHECKER_ID - varchar(15)
1434 
NULL, -- APPROVE_DT - datetime
1435 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1436 
'N', -- IS_LEAF - varchar(1)
1437 
'', -- COST_ID - varchar(15)
1438 
@DVDM_CDT ,
1439 
N'Chờ giám đốc khối xác nhận',
1440 
0 -- DVDM_ID - varchar(15)
1441 
)
1442
1443 
END
1444
1445 
SET @IS_NEXT =
1446 
(
1447 
SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDK')
1448 
);
1449
1450 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDK'))
1451
1452
1453 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT'))
1454 
BEGIN
1455 
SET @STEP_PARENT='GDK_TT'
1456 
END
1457
1458
1459 
UPDATE dbo.PL_REQUEST_PROCESS SET ROLE_USER='PTGD' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT' AND NOT EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE CM_DVDM.DVDM_ID=dbo.PL_REQUEST_PROCESS.DVDM_ID AND IS_GDK=1)
1460
1461 
IF (@IS_NEXT = 1 OR @IS_NEXT_CDT =1)
1462 
BEGIN
1463
1464 
IF( EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=1) )
1465 
BEGIN
1466 
DECLARE lstCostCenter CURSOR FOR
1467 
SELECT DVDM_ID,
1468 
TOTAL_AMT
1469 
FROM @DATA_DVDM WHERE IS_PTGD=1 AND NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='PTGD' AND PL_REQUEST_PROCESS.DVDM_ID=[@DATA_DVDM].DVDM_ID) ;
1470 
OPEN lstCostCenter;
1471 
FETCH NEXT FROM lstCostCenter
1472 
INTO @DVDM_ID,
1473 
@TOTAL_AMT_GD;
1474 
WHILE @@FETCH_STATUS = 0
1475 
BEGIN
1476 
INSERT INTO dbo.PL_REQUEST_PROCESS
1477 
(
1478 
REQ_ID,
1479 
PROCESS_ID,
1480 
STATUS,
1481 
ROLE_USER,
1482 
BRANCH_ID,
1483 
CHECKER_ID,
1484 
APPROVE_DT,
1485 
PARENT_PROCESS_ID,
1486 
IS_LEAF,
1487 
COST_ID,
1488 
DVDM_ID,
1489 
NOTES,
1490 
IS_HAS_CHILD
1491 
)
1492 
VALUES
1493 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1494 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1495 
'U', -- STATUS - varchar(5)
1496 
'PTGD', -- ROLE_USER - varchar(50)
1497 
'', -- BRANCH_ID - varchar(15)
1498 
'', -- CHECKER_ID - varchar(15)
1499 
NULL, -- APPROVE_DT - datetime
1500 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1501 
'N', -- IS_LEAF - varchar(1)
1502 
'', -- COST_ID - varchar(15)
1503 
@DVDM_ID, N'Chờ phó tổng giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
1504 
);
1505
1506 
FETCH NEXT FROM lstCostCenter
1507 
INTO @DVDM_ID,
1508 
@TOTAL_AMT_GD;
1509 
END;
1510 
CLOSE lstCostCenter;
1511 
DEALLOCATE lstCostCenter;
1512
1513 
SET @IS_NEXT =
1514 
(
1515 
SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'PTGD')
1516 
);
1517
1518
1519 
END
1520
1521 
IF(@IS_NEXT_CDT=1 AND NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='PTGD' AND DVDM_ID=@DVDM_CDT))
1522 
BEGIN
1523 
INSERT INTO dbo.PL_REQUEST_PROCESS
1524 
(
1525 
REQ_ID,
1526 
PROCESS_ID,
1527 
STATUS,
1528 
ROLE_USER,
1529 
BRANCH_ID,
1530 
CHECKER_ID,
1531 
APPROVE_DT,
1532 
PARENT_PROCESS_ID,
1533 
IS_LEAF,
1534 
COST_ID,
1535 
DVDM_ID,
1536 
NOTES,IS_HAS_CHILD
1537 
)
1538 
VALUES
1539 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1540 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1541 
'U', -- STATUS - varchar(5)
1542 
'PTGD', -- ROLE_USER - varchar(50)
1543 
'', -- BRANCH_ID - varchar(15)
1544 
'', -- CHECKER_ID - varchar(15)
1545 
NULL, -- APPROVE_DT - datetime
1546 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1547 
'N', -- IS_LEAF - varchar(1)
1548 
'', -- COST_ID - varchar(15)
1549 
@DVDM_CDT ,
1550 
N'Chờ giám đốc khối xác nhận',
1551 
0 -- DVDM_ID - varchar(15)
1552 
)
1553
1554 
END
1555
1556 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'PTGD'))
1557 
IF(EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=0) AND (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDK'))=1)
1558 
BEGIN
1559 
SET @IS_NEXT=1
1560 
END
1561
1562 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='PTGDK_TT'))
1563 
BEGIN
1564 
SET @STEP_PARENT='PTGDK_TT'
1565 
END
1566
1567
1568 
IF (@IS_NEXT = 1 OR @IS_NEXT_CDT=1)
1569 
BEGIN
1570 
INSERT INTO dbo.PL_REQUEST_PROCESS
1571 
(
1572 
REQ_ID,
1573 
PROCESS_ID,
1574 
STATUS,
1575 
ROLE_USER,
1576 
BRANCH_ID,
1577 
CHECKER_ID,
1578 
APPROVE_DT,
1579 
PARENT_PROCESS_ID,
1580 
IS_LEAF,
1581 
COST_ID,
1582 
DVDM_ID,
1583 
NOTES,
1584 
IS_HAS_CHILD
1585 
)
1586 
VALUES
1587 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1588 
'TGD', -- PROCESS_ID - varchar(10)
1589 
'U', -- STATUS - varchar(5)
1590 
'TGD', -- ROLE_USER - varchar(50)
1591 
'', -- BRANCH_ID - varchar(15)
1592 
'', -- CHECKER_ID - varchar(15)
1593 
NULL, -- APPROVE_DT - datetime
1594 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1595 
'N', -- IS_LEAF - varchar(1)
1596 
'', -- COST_ID - varchar(15)
1597 
'', N'Chờ tổng giám đốc xác nhận', 0 -- DVDM_ID - varchar(15)
1598 
);
1599 
SET @STEP_PARENT = 'TGD';
1600
1601 
IF((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) > (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='TCTT-HDQT'))
1602 
BEGIN
1603 
INSERT INTO dbo.PL_REQUEST_PROCESS
1604 
(
1605 
REQ_ID,
1606 
PROCESS_ID,
1607 
STATUS,
1608 
ROLE_USER,
1609 
BRANCH_ID,
1610 
CHECKER_ID,
1611 
APPROVE_DT,
1612 
PARENT_PROCESS_ID,
1613 
IS_LEAF,
1614 
COST_ID,
1615 
DVDM_ID,
1616 
NOTES,
1617 
IS_HAS_CHILD
1618 
)
1619 
VALUES
1620 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1621 
'HDQT', -- PROCESS_ID - varchar(10)
1622 
'U', -- STATUS - varchar(5)
1623 
'HDQT', -- ROLE_USER - varchar(50)
1624 
'', -- BRANCH_ID - varchar(15)
1625 
'', -- CHECKER_ID - varchar(15)
1626 
NULL, -- APPROVE_DT - datetime
1627 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1628 
'N', -- IS_LEAF - varchar(1)
1629 
'', -- COST_ID - varchar(15)
1630 
'', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
1631 
);
1632 
SET @STEP_PARENT = 'HDQT';
1633 
END
1634 
END;
1635
1636
1637
1638
1639 
--ELSE
1640 
--BEGIN
1641
1642 
--END
1643
1644 
END;
1645
1646 
END;
1647 
END
1648
1649
1650
1651 
END
1652
1653
1654 
END
1655
1656 
INSERT INTO dbo.PL_REQUEST_PROCESS
1657 
(
1658 
REQ_ID,
1659 
PROCESS_ID,
1660 
STATUS,
1661 
ROLE_USER,
1662 
BRANCH_ID,
1663 
CHECKER_ID,
1664 
APPROVE_DT,
1665 
PARENT_PROCESS_ID,
1666 
IS_LEAF,
1667 
NOTES
1668 
)
1669 
VALUES
1670 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1671 
'APPROVE', -- PROCESS_ID - varchar(10)
1672 
'U', -- STATUS - varchar(5)
1673 
'', -- ROLE_USER - varchar(50)
1674 
'', -- BRANCH_ID - varchar(15)
1675 
'', -- CHECKER_ID - varchar(15)
1676 
NULL, -- APPROVE_DT - datetime
1677 
@STEP_PARENT, 'Y', N'Hoàn tất');
1678
1679
1680
1681 
IF @@Error <> 0
1682 
GOTO ABORT;
1683
1684
1685
1686 
DECLARE @PROCESS_ID_CURR VARCHAR(10);
1687 
SET @PROCESS_ID_CURR =
1688 
(
1689 
SELECT TOP 1
1690 
PROCESS_ID
1691 
FROM dbo.PL_REQUEST_PROCESS
1692 
WHERE REQ_ID = @p_REQ_ID
1693 
AND PARENT_PROCESS_ID = 'APPNEW'
1694 
);
1695
1696 
UPDATE dbo.PL_REQUEST_PROCESS
1697 
SET STATUS = 'C'
1698 
WHERE PARENT_PROCESS_ID = 'APPNEW'
1699 
AND REQ_ID = @p_REQ_ID;
1700 
UPDATE dbo.PL_REQUEST_DOC
1701 
SET AUTH_STATUS = @p_AUTH_STATUS,
1702 
APPROVE_DT = @p_APPROVE_DT,
1703 
CHECKER_ID = @p_CHECKER_ID,
1704 
PROCESS_ID = @PROCESS_ID_CURR
1705 
WHERE REQ_ID = @p_REQ_ID;
1706
1707 
UPDATE dbo.PL_REQUEST_DOC_DT
1708 
SET CHECKER_ID=@p_CHECKER_ID,
1709 
APPROVE_DT=@p_APPROVE_DT
1710 
WHERE REQ_ID = @p_REQ_ID;
1711
1712 
INSERT INTO dbo.PL_PROCESS
1713 
(
1714 
REQ_ID,
1715 
PROCESS_ID,
1716 
CHECKER_ID,
1717 
APPROVE_DT,
1718 
PROCESS_DESC,
1719 
NOTES
1720 
)
1721 
VALUES
1722 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1723 
'APPNEW', -- PROCESS_ID - varchar(10)
1724 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
1725 
@p_APPROVE_DT, -- APPROVE_DT - datetime
1726 
@p_PROCESS_DES, CASE WHEN @BRANCH_TYPE_LOGIN ='PGD' THEN N'Trưởng phòng giao dịch xác nhận phiếu' ELSE N'Trưởng đơn vị phê duyệt' END -- PROCESS_DESC - nvarchar(1000)
1727 
);
1728 
IF (EXISTS
1729 
(
1730 
SELECT REQ_ID
1731 
FROM dbo.PL_REQUEST_DOC
1732 
WHERE REQ_ID = @p_REQ_ID
1733 
AND PROCESS_ID = 'APPROVE'
1734 
)
1735 
)
1736 
BEGIN
1737 
EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID;
1738 
EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID;
1739 
SET @Result = '0';
1740 
END;
1741 
SET @Result = '1';
1742 
END
1743 
COMMIT TRANSACTION;
1744 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))
1745 
BEGIN
1746 
SELECT @Result AS Result,
1747 
@ROLE_USER_NOTIFI AS ROLE_NOTIFI,
1748 
'' ErrorDesc;
1749 
RETURN '0';
1750 
END
1751 
ELSE
1752 
BEGIN
1753 
SELECT '4' as Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đã được cấp phê duyệt trung gian xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt' ErrorDesc
1754 
RETURN '4'
1755 
END
1756 
ABORT:
1757 
BEGIN
1758
1759 
ROLLBACK TRANSACTION;
1760 
SELECT '-1' AS Result,
1761 
'' ROLE_NOTIFI,
1762 
'' ErrorDesc;
1763 
RETURN '-1';
1764 
END;