Project

General

Profile

Download (45 KB)

PL_REQUEST_DOC_App.txt

Luc Tran Van, 01/27/2021 02:19 PM

 
1 
CREATE 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 FRO
282 
M dbo.PL_COSTCENTER PC
283 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
284 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
285 
BEGIN
286 
INSERT INTO dbo.PL_REQUEST_PROCESS
287 
(
288 
REQ_ID,
289 
PROCESS_ID,
290 
STATUS,
291 
ROLE_USER,
292 
BRANCH_ID,
293 
CHECKER_ID,
294 
APPROVE_DT,
295 
PARENT_PROCESS_ID,
296 
IS_LEAF,
297 
COST_ID,
298 
DVDM_ID,
299 
NOTES,
300 
IS_HAS_CHILD
301 
)
302 
VALUES
303 
( @p_REQ_ID, -- REQ_ID - varchar(15)
304 
'DVCM', -- PROCESS_ID - varchar(10)
305 
'U', -- STATUS - varchar(5)
306 
'GDDV', -- ROLE_USER - varchar(50)
307 
'', -- BRANCH_ID - varchar(15)
308 
'', -- CHECKER_ID - varchar(15)
309 
NULL, -- APPROVE_DT - datetime
310 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
311 
'N', -- IS_LEAF - varchar(1)
312 
'', -- COST_ID - varchar(15)
313 
@COST_ID, N'Chờ đơn vị chuyên môn xác nhận', 1
314 
-- DVDM_ID - varchar(15)
315 
);
316 
END
317 
ELSE
318 
BEGIN
319
320 
UPDATE PL_REQUEST_COSTCENTER SET AUTH_STATUS ='A',NOTES=N'Đồng ý' WHERE 1= 1 AND
321 
REQ_ID=@p_REQ_ID AND COST_ID=@COST_ID
322
323
324
325 
END
326
327 
FETCH NEXT FROM lstCostCenter
328 
INTO @COST_ID;
329 
END;
330 
CLOSE lstCostCenter;
331 
DEALLOCATE lstCostCenter;
332 
IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))
333 
SET @STEP_PARENT = 'DVCM';
334 
END;
335 
SET @TOTAL_AMT =
336 
(
337 
SELECT SUM(TOTAL_AMT) AS TOTAL_AMT
338 
FROM dbo.PL_REQUEST_DOC_DT
339 
WHERE REQ_ID = @p_REQ_ID
340 
);
341 
IF (EXISTS
342 
(
343 
SELECT REQ_TRANSFER_ID
344 
FROM dbo.PL_REQUEST_TRANSFER
345 
WHERE REQ_DOC_ID = @p_REQ_ID
346 
)
347 
)
348 
BEGIN
349
350
351 
IF (EXISTS
352 
(
353 
SELECT FR_BRN_ID
354 
FROM dbo.PL_REQUEST_TRANSFER
355 
WHERE REQ_DOC_ID = @p_REQ_ID
356 
AND
357 
(
358 
FR_BRN_ID <> @BRANCH_CREATE
359 
OR FR_DEP_ID <> @DEP_CREATE
360 
)
361 
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)
362 
)
363 
)
364 
BEGIN
365 
DECLARE lstTransfer CURSOR FOR
366 
SELECT FR_BRN_ID,
367 
FR_DEP_ID
368 
FROM dbo.PL_REQUEST_TRANSFER
369 
WHERE REQ_DOC_ID = @p_REQ_ID
370 
AND
371 
(
372 
FR_BRN_ID <> @BRANCH_CREATE
373 
OR FR_DEP_ID <> @DEP_CREATE
374 
)
375 
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)
376 
GROUP BY FR_BRN_ID,
377 
FR_DEP_ID;
378 
OPEN lstTransfer;
379 
FETCH NEXT FROM lstTransfer
380 
INTO @FR_BRANCH_ID,
381 
@FR_DEP_ID;
382 
WHILE @@FETCH_STATUS = 0
383 
BEGIN
384 
INSERT INTO dbo.PL_REQUEST_PROCESS
385 
(
386 
REQ_ID,
387 
PROCESS_ID,
388 
STATUS,
389 
ROLE_USER,
390 
BRANCH_ID,
391 
CHECKER_ID,
392 
APPROVE_DT,
393 
PARENT_PROCESS_ID,
394 
IS_LEAF,
395 
COST_ID,
396 
DVDM_ID,
397 
NOTES,
398 
IS_HAS_CHILD,
399 
DEP_ID
400 
)
401 
VALUES
402 
( @p_REQ_ID, -- REQ_ID - varchar(15)
403 
'DVDC', -- PROCESS_ID - varchar(10)
404 
'U', -- STATUS - varchar(5)
405 
'GDDV', -- ROLE_USER - varchar(50)
406 
@FR_BRANCH_ID, -- BRANCH_ID - varchar(15)
407 
'', -- CHECKER_ID - varchar(15)
408 
NULL, -- APPROVE_DT - datetime
409 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
410 
'N', -- IS_LEAF - varchar(1)
411 
'', -- COST_ID - varchar(15)
412 
'', -- DVDM_ID - varchar(15)
413 
N'Chờ đơn vị điều chuyển xác nhận', 1, @FR_DEP_ID);
414 
FETCH NEXT FROM lstTransfer
415 
INTO @FR_BRANCH_ID,
416 
@FR_DEP_ID;
417 
END;
418 
CLOSE lstTransfer;
419 
DEALLOCATE lstTransfer;
420 
SET @STEP_PARENT = 'DVDC';
421 
END;
422
423 
-- Đầu mối nhận
424 
DECLARE lstTransfer CURSOR FOR
425 
SELECT TO_DVDM_ID
426 
FROM dbo.PL_REQUEST_TRANSFER
427 
WHERE REQ_DOC_ID = @p_REQ_ID AND TO_DVDM_ID IS NOT NULL AND TO_DVDM_ID <>''
428 
GROUP BY TO_DVDM_ID;
429 
OPEN lstTransfer;
430 
FETCH NEXT FROM lstTransfer
431 
INTO @DVDM_ID;
432 
WHILE @@FETCH_STATUS = 0
433 
BEGIN
434 
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 FR
435 
OM dbo.PL_COSTCENTER PC
436 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
437 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
438
439 
BEGIN
440 
INSERT INTO dbo.PL_REQUEST_PROCESS
441 
(
442 
REQ_ID,
443 
PROCESS_ID,
444 
STATUS,
445 
ROLE_USER,
446 
BRANCH_ID,
447 
CHECKER_ID,
448 
APPROVE_DT,
449 
PARENT_PROCESS_ID,
450 
IS_LEAF,
451 
COST_ID,
452 
DVDM_ID,
453 
NOTES,
454 
IS_HAS_CHILD
455 
)
456 
VALUES
457 
( @p_REQ_ID, -- REQ_ID - varchar(15)
458 
'DVDM_DC', -- PROCESS_ID - varchar(10)
459 
'U', -- STATUS - varchar(5)
460 
'GDDV', -- ROLE_USER - varchar(50)
461 
'', -- BRANCH_ID - varchar(15)
462 
'', -- CHECKER_ID - varchar(15)
463 
NULL, -- APPROVE_DT - datetime
464 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
465 
'N', -- IS_LEAF - varchar(1)
466 
'', -- COST_ID - varchar(15)
467 
@DVDM_ID, -- DVDM_ID - varchar(15)
468 
N'Chờ đơn vị đầu mối xác nhận', 0);
469 
END
470 
FETCH NEXT FROM lstTransfer
471 
INTO @DVDM_ID;
472 
END;
473 
CLOSE lstTransfer;
474 
DEALLOCATE lstTransfer;
475
476
477 
IF (EXISTS
478 
(
479 
SELECT FR_BRN_ID
480 
FROM dbo.PL_REQUEST_TRANSFER
481 
WHERE REQ_DOC_ID = @p_REQ_ID
482 
AND FR_BRN_ID = @BRANCH_CREATE
483 
AND FR_DEP_ID = @DEP_CREATE
484 
)
485 
)
486 
BEGIN
487 
-- Đầu mối cho
488 
DECLARE lstTransfer CURSOR FOR
489 
SELECT FR_DVDM_ID
490 
FROM dbo.PL_REQUEST_TRANSFER
491 
WHERE REQ_DOC_ID = @p_REQ_ID
492 
AND FR_BRN_ID = @BRANCH_CREATE
493 
AND FR_DEP_ID = @DEP_CREATE
494 
AND FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>''
495 
AND NOT EXISTS
496 
(
497 
SELECT *
498 
FROM dbo.PL_REQUEST_PROCESS
499 
WHERE REQ_ID = @p_REQ_ID
500 
AND PROCESS_ID = 'DVDM_DC'
501 
AND DVDM_ID = FR_DVDM_ID
502 
)
503 
GROUP BY FR_DVDM_ID;
504 
OPEN lstTransfer;
505 
FETCH NEXT FROM lstTransfer
506 
INTO @DVDM_ID;
507 
WHILE @@FETCH_STATUS = 0
508 
BEGIN
509 
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 FR
510 
OM dbo.PL_COSTCENTER PC
511 
LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID
512 
WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))
513 
BEGIN
514 
INSERT INTO dbo.PL_REQUEST_PROCESS
515 
(
516 
REQ_ID,
517 
PROCESS_ID,
518 
STATUS,
519 
ROLE_USER,
520 
BRANCH_ID,
521 
CHECKER_ID,
522 
APPROVE_DT,
523 
PARENT_PROCESS_ID,
524 
IS_LEAF,
525 
COST_ID,
526 
DVDM_ID,
527 
NOTES,
528 
IS_HAS_CHILD
529 
)
530 
VALUES
531 
( @p_REQ_ID, -- REQ_ID - varchar(15)
532 
'DVDM_DC', -- PROCESS_ID - varchar(10)
533 
'U', -- STATUS - varchar(5)
534 
'GDDV', -- ROLE_USER - varchar(50)
535 
'', -- BRANCH_ID - varchar(15)
536 
'', -- CHECKER_ID - varchar(15)
537 
NULL, -- APPROVE_DT - datetime
538 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
539 
'N', -- IS_LEAF - varchar(1)
540 
'', -- COST_ID - varchar(15)
541 
@DVDM_ID, -- DVDM_ID - varchar(15)
542 
N'Chờ đơn vị đầu mối xác nhận', 0);
543 
END
544 
FETCH NEXT FROM lstTransfer
545 
INTO @DVDM_ID;
546 
END;
547 
CLOSE lstTransfer;
548 
DEALLOCATE lstTransfer;
549
550
551
552
553 
IF(EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDM_DC'))
554 
SET @STEP_PARENT='DVDM_DC'
555
556
557 
END;
558
559
560
561 
INSERT INTO dbo.PL_REQUEST_PROCESS
562 
(
563 
REQ_ID,
564 
PROCESS_ID,
565 
STATUS,
566 
ROLE_USER,
567 
BRANCH_ID,
568 
CHECKER_ID,
569 
APPROVE_DT,
570 
PARENT_PROCESS_ID,
571 
IS_LEAF,
572 
COST_ID,
573 
DVDM_ID,
574 
NOTES,IS_HAS_CHILD
575 
)
576 
VALUES
577 
( @p_REQ_ID, -- REQ_ID - varchar(15)
578 
'TC', -- PROCESS_ID - varchar(10)
579 
'U', -- STATUS - varchar(5)
580 
'TC', -- ROLE_USER - varchar(50)
581 
'', -- BRANCH_ID - varchar(15)
582 
'', -- CHECKER_ID - varchar(15)
583 
NULL, -- APPROVE_DT - datetime
584 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
585 
'N', -- IS_LEAF - varchar(1)
586 
'', -- COST_ID - varchar(15)
587 
'', -- DVDM_ID - varchar(15)
588 
N'Chờ đơn vị Tài chính xác nhận',1);
589 
SET @STEP_PARENT = 'TC';
590
591
592 
IF (NOT EXISTS
593 
(
594 
SELECT REQ_TRANSFER_ID
595 
FROM dbo.PL_REQUEST_TRANSFER
596 
WHERE REQ_DOC_ID = @p_REQ_ID
597 
AND
598 
(
599 
FR_BRN_ID <> @BRANCH_CREATE
600 
OR FR_DEP_ID <> @DEP_CREATE
601 
)
602 
)
603 
)
604 
BEGIN
605 
---Duyệt DC
606 
IF (
607 
(
608 
SELECT COUNT(T.FR_KHOI_ID) AS COUNT_ROW
609 
FROM
610 
(
611 
SELECT FR_KHOI_ID
612 
FROM dbo.PL_REQUEST_TRANSFER
613 
WHERE REQ_DOC_ID = @p_REQ_ID
614 
GROUP BY FR_KHOI_ID
615 
) T
616 
) > 1
617 
)
618 
BEGIN
619 
INSERT INTO dbo.PL_REQUEST_PROCESS
620 
(
621 
REQ_ID,
622 
PROCESS_ID,
623 
STATUS,
624 
ROLE_USER,
625 
BRANCH_ID,
626 
CHECKER_ID,
627 
APPROVE_DT,
628 
PARENT_PROCESS_ID,
629 
IS_LEAF,
630 
COST_ID,
631 
DVDM_ID,
632 
NOTES,
633 
IS_HAS_CHILD
634 
)
635 
VALUES
636 
( @p_REQ_ID, -- REQ_ID - varchar(15)
637 
'TGD_DC', -- PROCESS_ID - varchar(10)
638 
'U', -- STATUS - varchar(5)
639 
'TGD', -- ROLE_USER - varchar(50)
640 
'', -- BRANCH_ID - varchar(15)
641 
'', -- CHECKER_ID - varchar(15)
642 
NULL, -- APPROVE_DT - datetime
643 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
644 
'', -- IS_LEAF - varchar(1)
645 
'', -- COST_ID - varchar(15)
646 
'', -- DVDM_ID - varchar(15)
647 
N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
648 
NULL -- IS_HAS_CHILD - bit
649 
);
650 
SET @STEP_PARENT = 'TGD_DC';
651 
END;
652 
ELSE
653 
BEGIN
654 
DECLARE @LIMTT_MAX DECIMAL(18, 2),
655 
@LIMIT_APP DECIMAL(18, 2),
656 
@KHOI_ID_TF VARCHAR(20);
657 
SET @KHOI_ID_TF =
658 
(
659 
SELECT TOP 1
660 
FR_KHOI_ID
661 
FROM dbo.PL_REQUEST_TRANSFER
662 
WHERE REQ_DOC_ID = @p_REQ_ID
663 
);
664
665 
IF(@KHOI_ID_TF IS NOT NULL AND @KHOI_ID_TF<>'')
666 
BEGIN
667 
SET @LIMIT_APP =
668 
(
669 
SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
670 
FROM dbo.LIMIT_ACCUMULATE
671 
WHERE ROLE_ID = 'GDK'
672 
AND DVDM_ID = @KHOI_ID_TF
673 
);
674 
SET @TOTAL_TRANSFER =
675 
(
676 
SELECT SUM(TOTAL_AMT) AS TOTAL
677 
FROM dbo.PL_REQUEST_TRANSFER
678 
WHERE REQ_DOC_ID = @p_REQ_ID
679 
);
680 
SET @LIMTT_MAX =
681 
(
682 
SELECT LIMIT_VALUE
683 
FROM dbo.TL_SYSROLE_LIMIT
684 
WHERE ROLE_ID = 'GDK'
685 
AND LIMIT_TYPE = 'DCNS'
686 
);
687
688 
INSERT INTO dbo.PL_REQUEST_PROCESS
689 
(
690 
REQ_ID,
691 
PROCESS_ID,
692 
STATUS,
693 
ROLE_USER,
694 
BRANCH_ID,
695 
CHECKER_ID,
696 
APPROVE_DT,
697 
PARENT_PROCESS_ID,
698 
IS_LEAF,
699 
COST_ID,
700 
DVDM_ID,
701 
NOTES,
702 
IS_HAS_CHILD
703 
)
704 
VALUES
705 
( @p_REQ_ID, -- REQ_ID - varchar(15)
706 
'GDK_DC', -- PROCESS_ID - varchar(10)
707 
'U', -- STATUS - varchar(5)
708 
'GDK', -- ROLE_USER - varchar(50)
709 
'', -- BRANCH_ID - varchar(15)
710 
'', -- CHECKER_ID - varchar(15)
711 
GETDATE(), -- APPROVE_DT - datetime
712 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
713 
'N', -- IS_LEAF - varchar(1)
714 
'', -- COST_ID - varchar(15)
715 
@KHOI_ID_TF, -- DVDM_ID - varchar(15)
716 
N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
717 
NULL -- IS_HAS_CHILD - bit
718 
);
719 
SET @STEP_PARENT = 'GDK_DC';
720
721 
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)
722
723
724 
IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP)
725 
BEGIN
726 
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))
727 
BEGIN
728 
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))
729 
BEGIN
730 
INSERT INTO dbo.PL_REQUEST_PROCESS
731 
(
732 
REQ_ID,
733 
PROCESS_ID,
734 
STATUS,
735 
ROLE_USER,
736 
BRANCH_ID,
737 
CHECKER_ID,
738 
APPROVE_DT,
739 
PARENT_PROCESS_ID,
740 
IS_LEAF,
741 
COST_ID,
742 
DVDM_ID,
743 
NOTES,
744 
IS_HAS_CHILD
745 
)
746 
VALUES
747 
( @p_REQ_ID, -- REQ_ID - varchar(15)
748 
'PTGD_DC', -- PROCESS_ID - varchar(10)
749 
'U', -- STATUS - varchar(5)
750 
'PTGD', -- ROLE_USER - varchar(50)
751 
'', -- BRANCH_ID - varchar(15)
752 
'', -- CHECKER_ID - varchar(15)
753 
GETDATE(), -- APPROVE_DT - datetime
754 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
755 
'N', -- IS_LEAF - varchar(1)
756 
'', -- COST_ID - varchar(15)
757 
@KHOI_ID_TF, -- DVDM_ID - varchar(15)
758 
N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
759 
NULL -- IS_HAS_CHILD - bit
760 
);
761 
SET @STEP_PARENT = 'PTGD_DC';
762 
END
763 
SET @LIMTT_MAX =
764 
(
765 
SELECT LIMIT_VALUE
766 
FROM dbo.TL_SYSROLE_LIMIT
767 
WHERE ROLE_ID = 'PTGD'
768 
AND LIMIT_TYPE = 'DCNS'
769 
);
770 
SET @LIMIT_APP =
771 
(
772 
SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
773 
FROM dbo.LIMIT_ACCUMULATE
774 
WHERE ROLE_ID = 'PTGD'
775 
AND DVDM_ID = @KHOI_ID_TF
776 
);
777
778 
END
779 
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))
780 
BEGIN
781 
INSERT INTO dbo.PL_REQUEST_PROCESS
782 
(
783 
REQ_ID,
784 
PROCESS_ID,
785 
STATUS,
786 
ROLE_USER,
787 
BRANCH_ID,
788 
CHECKER_ID,
789 
APPROVE_DT,
790 
PARENT_PROCESS_ID,
791 
IS_LEAF,
792 
COST_ID,
793 
DVDM_ID,
794 
NOTES,
795 
IS_HAS_CHILD
796 
)
797 
VALUES
798 
( @p_REQ_ID, -- REQ_ID - varchar(15)
799 
'TGD_DC', -- PROCESS_ID - varchar(10)
800 
'U', -- STATUS - varchar(5)
801 
'TGD', -- ROLE_USER - varchar(50)
802 
'', -- BRANCH_ID - varchar(15)
803 
'', -- CHECKER_ID - varchar(15)
804 
NULL, -- APPROVE_DT - datetime
805 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
806 
'', -- IS_LEAF - varchar(1)
807 
'', -- COST_ID - varchar(15)
808 
'', -- DVDM_ID - varchar(15)
809 
N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
810 
NULL -- IS_HAS_CHILD - bit
811 
);
812 
SET @STEP_PARENT = 'TGD_DC';
813 
END;
814
815 
END;
816
817 
END
818
819
820 
END;
821 
----
822 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID LIKE '%_DC'))
823 
BEGIN
824 
DECLARE @DVDM_ID_TT VARCHAR(20)
825 
-- 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
826 
-- INSERT PL_TRADE_DETAIL
827
828 
--DECLARE @l_TRADE_ID VARCHAR(15)
829 
--EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @l_TRADE_ID out
830 
--IF @l_TRADE_ID ='' OR @l_TRADE_ID IS NULL GOTO ABORT
831
832 
--INSERT INTO PL_TRADEDETAIL
833 
--(
834 
-- [TRADE_ID],
835 
-- [PLAN_ID],
836 
-- [GOODS_ID],
837 
-- [GOODS_NAME],
838 
-- [GOODS_TYPE],
839 
-- [UNIT_ID],
840 
-- M1,
841 
-- M2,
842 
-- M3,
843 
-- M4,
844 
-- M5,
845 
-- M6,
846 
-- M7,
847 
-- M8,
848 
-- M9,
849 
-- M10,
850 
-- M11,
851 
-- M12,
852 
-- [QUANTITY],
853 
-- [QUANTITY_EXE],
854 
-- [PRICE],
855 
-- [START_DT_AMORT],
856 
-- [MONTH_AMORT],
857 
-- [END_DT_AMORT],
858 
-- [RATE_AMORT],
859 
-- [NOTES],
860 
-- [RECORD_STATUS],
861 
-- [MAKER_ID],
862 
-- [CREATE_DT],
863 
-- [AUTH_STATUS],
864 
-- [CHECKER_ID],
865 
-- [APPROVE_DT],AMT_RECEIVE_TF
866 
--)
867 
--VALUES
868 
--(
869 
-- @l_TRADE_ID,
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 
-- NULL,
886 
-- NULL,
887 
-- NULL,
888 
-- 0,
889 
-- 0,
890 
-- NULL,
891 
-- NULL,
892 
-- NULL,
893 
-- 0,
894 
-- '',
895 
-- '1', 'admin' ,GETDATE() ,'A' ,'admin' ,GETDATE(),0
896 
--)
897 
---
898 
BEGIN
899 
IF(@BRANCH_CREATE_TYPE='PGD')
900 
BEGIN
901
902
903 
SET @BRANCH_PARENT=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
904 
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='')))
905 
BEGIN
906 
INSERT INTO dbo.PL_REQUEST_PROCESS
907 
(
908 
REQ_ID,
909 
PROCESS_ID,
910 
STATUS,
911 
ROLE_USER,
912 
BRANCH_ID,
913 
DEP_ID,
914 
CHECKER_ID,
915 
APPROVE_DT,
916 
PARENT_PROCESS_ID,
917 
IS_LEAF,
918 
NOTES
919 
)
920 
VALUES
921 
(
922 
@p_REQ_ID, -- REQ_ID - varchar(15)
923 
'DVC', -- PROCESS_ID - varchar(10)
924 
'U', -- STATUS - varchar(5)
925 
'GDDV', -- ROLE_USER - varchar(50)
926 
@BRANCH_PARENT,
927 
NULL, -- BRANCH_ID - varchar(15)
928 
NULL, -- CHECKER_ID - varchar(15)
929 
NULL , -- APPROVE_DT - datetime
930 
@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');
931
932 
--SET @STEP_CURR = 'DVC';
933 
SET @STEP_PARENT = 'DVC';
934 
END
935
936 
END
937 
ELSE
938 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
939 
AND ((
940 
BRANCH_ID=@BRANCH_CREATE
941 
AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='')
942 
AND (DEP_ID IS NULL OR DEP_ID='')))
943 
)
944 
OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC
945 
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)
946 
)
947 
))
948 
BEGIN
949 
INSERT INTO dbo.PL_REQUEST_PROCESS
950 
(
951 
REQ_ID,
952 
PROCESS_ID,
953 
STATUS,
954 
ROLE_USER,
955 
BRANCH_ID,
956 
DEP_ID,
957 
CHECKER_ID,
958 
APPROVE_DT,
959 
PARENT_PROCESS_ID,
960 
IS_LEAF,
961 
NOTES
962 
)
963 
VALUES
964 
(
965 
@p_REQ_ID, -- REQ_ID - varchar(15)
966 
'DVC', -- PROCESS_ID - varchar(10)
967 
'U', -- STATUS - varchar(5)
968 
'GDDV', -- ROLE_USER - varchar(50)
969 
@BRANCH_CREATE,
970 
@DEP_CREATE, -- BRANCH_ID - varchar(15)
971 
NULL, -- CHECKER_ID - varchar(15)
972 
NULL , -- APPROVE_DT - datetime
973 
@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');
974
975 
--SET @STEP_CURR = 'DVC';
976 
SET @STEP_PARENT = 'DVC';
977 
END
978
979
980
981
982
983 
SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDDV'))
984 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDDV'))
985 
SET @TOTAL_AMT = (SELECT TOTAL_AMT FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
986 
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'))
987 
BEGIN
988 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID ) OR @IS_NEXT_CDT=1)
989 
BEGIN
990 
DECLARE lstCostCenter CURSOR FOR
991 
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(
992 
SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='GDK'
993 
)
994 
GROUP BY KHOI_ID
995 
OPEN lstCostCenter
996 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
997 
WHILE @@FETCH_STATUS = 0
998 
BEGIN
999 
INSERT INTO dbo.PL_REQUEST_PROCESS
1000 
(
1001 
REQ_ID,
1002 
PROCESS_ID,
1003 
STATUS,
1004 
ROLE_USER,
1005 
BRANCH_ID,
1006 
CHECKER_ID,
1007 
APPROVE_DT,
1008 
PARENT_PROCESS_ID,
1009 
IS_LEAF,
1010 
COST_ID,
1011 
DVDM_ID,
1012 
NOTES,IS_HAS_CHILD
1013 
)
1014 
VALUES
1015 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1016 
'GDK_TT', -- PROCESS_ID - varchar(10)
1017 
'U', -- STATUS - varchar(5)
1018 
'GDK', -- ROLE_USER - varchar(50)
1019 
'', -- BRANCH_ID - varchar(15)
1020 
'', -- CHECKER_ID - varchar(15)
1021 
NULL, -- APPROVE_DT - datetime
1022 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1023 
'N', -- IS_LEAF - varchar(1)
1024 
'', -- COST_ID - varchar(15)
1025 
@DVDM_ID_TT ,
1026 
N'Chờ giám đốc khối xác nhận',
1027 
0 -- DVDM_ID - varchar(15)
1028 
)
1029
1030 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
1031 
END
1032 
CLOSE lstCostCenter
1033 
DEALLOCATE lstCostCenter
1034
1035
1036 
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))
1037 
BEGIN
1038 
INSERT INTO dbo.PL_REQUEST_PROCESS
1039 
(
1040 
REQ_ID,
1041 
PROCESS_ID,
1042 
STATUS,
1043 
ROLE_USER,
1044 
BRANCH_ID,
1045 
CHECKER_ID,
1046 
APPROVE_DT,
1047 
PARENT_PROCESS_ID,
1048 
IS_LEAF,
1049 
COST_ID,
1050 
DVDM_ID,
1051 
NOTES,IS_HAS_CHILD
1052 
)
1053 
VALUES
1054 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1055 
'GDK_TT', -- PROCESS_ID - varchar(10)
1056 
'U', -- STATUS - varchar(5)
1057 
'GDK', -- ROLE_USER - varchar(50)
1058 
'', -- BRANCH_ID - varchar(15)
1059 
'', -- CHECKER_ID - varchar(15)
1060 
NULL, -- APPROVE_DT - datetime
1061 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1062 
'N', -- IS_LEAF - varchar(1)
1063 
'', -- COST_ID - varchar(15)
1064 
@DVDM_CDT ,
1065 
N'Chờ giám đốc khối xác nhận',
1066 
0 -- DVDM_ID - varchar(15)
1067 
)
1068
1069 
END
1070
1071 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT'))
1072 
BEGIN
1073 
SET @STEP_PARENT='GDK_TT'
1074 
END
1075 
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)
1076
1077
1078 
SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDK'))
1079 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDK'))
1080
1081 
IF(@IS_NEXT=1 OR @IS_NEXT_CDT=1)
1082 
BEGIN
1083 
IF(EXISTS(SELECT KHOI_ID FROM dbo.PL_REQUEST_DOC_DT DT
1084 
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(
1085 
SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='PTGD'
1086 
) ) OR @IS_NEXT_CDT=1)
1087 
BEGIN
1088 
DECLARE lstCostCenter CURSOR FOR
1089 
SELECT KHOI_ID FROM dbo.PL_REQUEST_DOC_DT DT
1090 
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(
1091 
SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='PTGD'
1092 
)
1093 
GROUP BY KHOI_ID
1094 
OPEN lstCostCenter
1095 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
1096 
WHILE @@FETCH_STATUS = 0
1097 
BEGIN
1098 
INSERT INTO dbo.PL_REQUEST_PROCESS
1099 
(
1100 
REQ_ID,
1101 
PROCESS_ID,
1102 
STATUS,
1103 
ROLE_USER,
1104 
BRANCH_ID,
1105 
CHECKER_ID,
1106 
APPROVE_DT,
1107 
PARENT_PROCESS_ID,
1108 
IS_LEAF,
1109 
COST_ID,
1110 
DVDM_ID,
1111 
NOTES,IS_HAS_CHILD
1112 
)
1113 
VALUES
1114 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1115 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1116 
'U', -- STATUS - varchar(5)
1117 
'PTGD', -- ROLE_USER - varchar(50)
1118 
'', -- BRANCH_ID - varchar(15)
1119 
'', -- CHECKER_ID - varchar(15)
1120 
NULL, -- APPROVE_DT - datetime
1121 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1122 
'N', -- IS_LEAF - varchar(1)
1123 
'', -- COST_ID - varchar(15)
1124 
@DVDM_ID_TT ,
1125 
N'Chờ phó tổng giám đốc khối xác nhận',
1126 
0 -- DVDM_ID - varchar(15)
1127 
)
1128
1129
1130 
FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
1131 
END
1132 
CLOSE lstCostCenter
1133 
DEALLOCATE lstCostCenter
1134
1135 
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))
1136 
BEGIN
1137 
INSERT INTO dbo.PL_REQUEST_PROCESS
1138 
(
1139 
REQ_ID,
1140 
PROCESS_ID,
1141 
STATUS,
1142 
ROLE_USER,
1143 
BRANCH_ID,
1144 
CHECKER_ID,
1145 
APPROVE_DT,
1146 
PARENT_PROCESS_ID,
1147 
IS_LEAF,
1148 
COST_ID,
1149 
DVDM_ID,
1150 
NOTES,IS_HAS_CHILD
1151 
)
1152 
VALUES
1153 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1154 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1155 
'U', -- STATUS - varchar(5)
1156 
'PTGD', -- ROLE_USER - varchar(50)
1157 
'', -- BRANCH_ID - varchar(15)
1158 
'', -- CHECKER_ID - varchar(15)
1159 
NULL, -- APPROVE_DT - datetime
1160 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1161 
'N', -- IS_LEAF - varchar(1)
1162 
'', -- COST_ID - varchar(15)
1163 
@DVDM_CDT ,
1164 
N'Chờ phó tổng giám đốc khối xác nhận',
1165 
0 -- DVDM_ID - varchar(15)
1166 
)
1167
1168 
END
1169
1170
1171 
SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'PTGD'))
1172 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'PTGD'))
1173 
END
1174 
IF(EXISTS(SELECT KHOI_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT DT
1175 
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)
1176 
BEGIN
1177 
SET @IS_NEXT=1
1178 
END
1179
1180 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='PTGDK_TT'))
1181 
BEGIN
1182 
SET @STEP_PARENT='PTGDK_TT'
1183 
END
1184
1185 
IF(@IS_NEXT=1 OR @IS_NEXT_CDT=1)
1186 
BEGIN
1187 
INSERT INTO dbo.PL_REQUEST_PROCESS
1188 
(
1189 
REQ_ID,
1190 
PROCESS_ID,
1191 
STATUS,
1192 
ROLE_USER,
1193 
BRANCH_ID,
1194 
CHECKER_ID,
1195 
APPROVE_DT,
1196 
PARENT_PROCESS_ID,
1197 
IS_LEAF,
1198 
COST_ID,
1199 
DVDM_ID,
1200 
NOTES,IS_HAS_CHILD
1201 
)
1202 
VALUES
1203 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1204 
'TGD', -- PROCESS_ID - varchar(10)
1205 
'U', -- STATUS - varchar(5)
1206 
'TGD', -- ROLE_USER - varchar(50)
1207 
'', -- BRANCH_ID - varchar(15)
1208 
'', -- CHECKER_ID - varchar(15)
1209 
NULL, -- APPROVE_DT - datetime
1210 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1211 
'N', -- IS_LEAF - varchar(1)
1212 
'', -- COST_ID - varchar(15)
1213 
'' ,
1214 
N'Chờ tổng giám đốc xác nhận',
1215 
0 -- DVDM_ID - varchar(15)
1216 
)
1217 
SET @STEP_PARENT='TGD'
1218 
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'))
1219 
BEGIN
1220
1221
1222 
INSERT INTO dbo.PL_REQUEST_PROCESS
1223 
(
1224 
REQ_ID,
1225 
PROCESS_ID,
1226 
STATUS,
1227 
ROLE_USER,
1228 
BRANCH_ID,
1229 
CHECKER_ID,
1230 
APPROVE_DT,
1231 
PARENT_PROCESS_ID,
1232 
IS_LEAF,
1233 
COST_ID,
1234 
DVDM_ID,
1235 
NOTES,
1236 
IS_HAS_CHILD
1237 
)
1238 
VALUES
1239 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1240 
'HDQT', -- PROCESS_ID - varchar(10)
1241 
'U', -- STATUS - varchar(5)
1242 
'HDQT', -- ROLE_USER - varchar(50)
1243 
'', -- BRANCH_ID - varchar(15)
1244 
'', -- CHECKER_ID - varchar(15)
1245 
NULL, -- APPROVE_DT - datetime
1246 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1247 
'N', -- IS_LEAF - varchar(1)
1248 
'', -- COST_ID - varchar(15)
1249 
'', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
1250 
);
1251 
SET @STEP_PARENT = 'HDQT';
1252 
END
1253 
END
1254
1255 
--ELSE
1256 
--BEGIN
1257
1258 
--END
1259 
END
1260 
END
1261 
END
1262 
END
1263
1264
1265 
END
1266
1267 
END;
1268
1269
1270
1271
1272 
END;
1273 
ELSE
1274 
BEGIN
1275
1276 
IF(@BRANCH_CREATE_TYPE='PGD')
1277 
BEGIN
1278
1279 
SET @BRANCH_PARENT=(SELECT TOP 1 FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
1280 
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='')))
1281 
BEGIN
1282 
INSERT INTO dbo.PL_REQUEST_PROCESS
1283 
(
1284 
REQ_ID,
1285 
PROCESS_ID,
1286 
STATUS,
1287 
ROLE_USER,
1288 
BRANCH_ID,
1289 
DEP_ID,
1290 
CHECKER_ID,
1291 
APPROVE_DT,
1292 
PARENT_PROCESS_ID,
1293 
IS_LEAF,
1294 
NOTES
1295 
)
1296 
VALUES
1297 
(
1298 
@p_REQ_ID, -- REQ_ID - varchar(15)
1299 
'DVC', -- PROCESS_ID - varchar(10)
1300 
'U', -- STATUS - varchar(5)
1301 
'GDDV', -- ROLE_USER - varchar(50)
1302 
@BRANCH_PARENT,
1303 
NULL, -- BRANCH_ID - varchar(15)
1304 
NULL, -- CHECKER_ID - varchar(15)
1305 
NULL , -- APPROVE_DT - datetime
1306 
@STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt');
1307
1308 
SET @STEP_CURR = 'DVC';
1309 
SET @STEP_PARENT = 'DVC';
1310 
END
1311
1312 
END
1313 
ELSE
1314 
IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
1315 
AND ((
1316 
BRANCH_ID=@BRANCH_CREATE
1317 
AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='')
1318 
AND (DEP_ID IS NULL OR DEP_ID='')))
1319 
)
1320 
OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC
1321 
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)
1322 
)
1323 
))
1324 
BEGIN
1325 
INSERT INTO dbo.PL_REQUEST_PROCESS
1326 
(
1327 
REQ_ID,
1328 
PROCESS_ID,
1329 
STATUS,
1330 
ROLE_USER,
1331 
BRANCH_ID,
1332 
DEP_ID,
1333 
CHECKER_ID,
1334 
APPROVE_DT,
1335 
PARENT_PROCESS_ID,
1336 
IS_LEAF,
1337 
NOTES
1338 
)
1339 
VALUES
1340 
(
1341 
@p_REQ_ID, -- REQ_ID - varchar(15)
1342 
'DVC', -- PROCESS_ID - varchar(10)
1343 
'U', -- STATUS - varchar(5)
1344 
'GDDV', -- ROLE_USER - varchar(50)
1345 
@BRANCH_CREATE,
1346 
@DEP_CREATE, -- BRANCH_ID - varchar(15)
1347 
NULL, -- CHECKER_ID - varchar(15)
1348 
NULL , -- APPROVE_DT - datetime
1349 
@STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt');
1350
1351 
SET @STEP_CURR = 'DVC';
1352 
SET @STEP_PARENT = 'DVC';
1353 
END
1354 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDDV'))
1355 
IF(EXISTS( SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID) OR @IS_NEXT_CDT=1)
1356 
BEGIN
1357 
SET @IS_NEXT =
1358 
(
1359 
SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDDV')
1360 
);
1361
1362
1363
1364 
IF (@IS_NEXT = 1 OR @IS_NEXT_CDT=1)
1365 
BEGIN
1366
1367 
DECLARE lstCostCenter CURSOR FOR
1368 
SELECT DVDM_ID,
1369 
TOTAL_AMT
1370 
FROM @DATA_DVDM;
1371 
OPEN lstCostCenter;
1372 
FETCH NEXT FROM lstCostCenter
1373 
INTO @DVDM_ID,
1374 
@TOTAL_AMT_GD;
1375 
WHILE @@FETCH_STATUS = 0
1376 
BEGIN
1377 
INSERT INTO dbo.PL_REQUEST_PROCESS
1378 
(
1379 
REQ_ID,
1380 
PROCESS_ID,
1381 
STATUS,
1382 
ROLE_USER,
1383 
BRANCH_ID,
1384 
CHECKER_ID,
1385 
APPROVE_DT,
1386 
PARENT_PROCESS_ID,
1387 
IS_LEAF,
1388 
COST_ID,
1389 
DVDM_ID,
1390 
NOTES,
1391 
IS_HAS_CHILD
1392 
)
1393 
VALUES
1394 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1395 
'GDK_TT', -- PROCESS_ID - varchar(10)
1396 
'U', -- STATUS - varchar(5)
1397 
'GDK', -- ROLE_USER - varchar(50)
1398 
'', -- BRANCH_ID - varchar(15)
1399 
'', -- CHECKER_ID - varchar(15)
1400 
NULL, -- APPROVE_DT - datetime
1401 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1402 
'N', -- IS_LEAF - varchar(1)
1403 
'', -- COST_ID - varchar(15)
1404 
@DVDM_ID, N'Chờ giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
1405 
);
1406 
FETCH NEXT FROM lstCostCenter
1407 
INTO @DVDM_ID,
1408 
@TOTAL_AMT_GD;
1409 
END;
1410 
CLOSE lstCostCenter;
1411 
DEALLOCATE lstCostCenter;
1412
1413 
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))
1414 
BEGIN
1415 
INSERT INTO dbo.PL_REQUEST_PROCESS
1416 
(
1417 
REQ_ID,
1418 
PROCESS_ID,
1419 
STATUS,
1420 
ROLE_USER,
1421 
BRANCH_ID,
1422 
CHECKER_ID,
1423 
APPROVE_DT,
1424 
PARENT_PROCESS_ID,
1425 
IS_LEAF,
1426 
COST_ID,
1427 
DVDM_ID,
1428 
NOTES,IS_HAS_CHILD
1429 
)
1430 
VALUES
1431 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1432 
'GDK_TT', -- PROCESS_ID - varchar(10)
1433 
'U', -- STATUS - varchar(5)
1434 
'GDK', -- ROLE_USER - varchar(50)
1435 
'', -- BRANCH_ID - varchar(15)
1436 
'', -- CHECKER_ID - varchar(15)
1437 
NULL, -- APPROVE_DT - datetime
1438 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1439 
'N', -- IS_LEAF - varchar(1)
1440 
'', -- COST_ID - varchar(15)
1441 
@DVDM_CDT ,
1442 
N'Chờ giám đốc khối xác nhận',
1443 
0 -- DVDM_ID - varchar(15)
1444 
)
1445
1446 
END
1447
1448 
SET @IS_NEXT =
1449 
(
1450 
SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDK')
1451 
);
1452
1453 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDK'))
1454
1455
1456 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT'))
1457 
BEGIN
1458 
SET @STEP_PARENT='GDK_TT'
1459 
END
1460
1461
1462 
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)
1463
1464 
IF (@IS_NEXT = 1 OR @IS_NEXT_CDT =1)
1465 
BEGIN
1466
1467 
IF( EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=1) )
1468 
BEGIN
1469 
DECLARE lstCostCenter CURSOR FOR
1470 
SELECT DVDM_ID,
1471 
TOTAL_AMT
1472 
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) ;
1473 
OPEN lstCostCenter;
1474 
FETCH NEXT FROM lstCostCenter
1475 
INTO @DVDM_ID,
1476 
@TOTAL_AMT_GD;
1477 
WHILE @@FETCH_STATUS = 0
1478 
BEGIN
1479 
INSERT INTO dbo.PL_REQUEST_PROCESS
1480 
(
1481 
REQ_ID,
1482 
PROCESS_ID,
1483 
STATUS,
1484 
ROLE_USER,
1485 
BRANCH_ID,
1486 
CHECKER_ID,
1487 
APPROVE_DT,
1488 
PARENT_PROCESS_ID,
1489 
IS_LEAF,
1490 
COST_ID,
1491 
DVDM_ID,
1492 
NOTES,
1493 
IS_HAS_CHILD
1494 
)
1495 
VALUES
1496 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1497 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1498 
'U', -- STATUS - varchar(5)
1499 
'PTGD', -- ROLE_USER - varchar(50)
1500 
'', -- BRANCH_ID - varchar(15)
1501 
'', -- CHECKER_ID - varchar(15)
1502 
NULL, -- APPROVE_DT - datetime
1503 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1504 
'N', -- IS_LEAF - varchar(1)
1505 
'', -- COST_ID - varchar(15)
1506 
@DVDM_ID, N'Chờ phó tổng giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
1507 
);
1508
1509 
FETCH NEXT FROM lstCostCenter
1510 
INTO @DVDM_ID,
1511 
@TOTAL_AMT_GD;
1512 
END;
1513 
CLOSE lstCostCenter;
1514 
DEALLOCATE lstCostCenter;
1515
1516 
SET @IS_NEXT =
1517 
(
1518 
SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'PTGD')
1519 
);
1520
1521
1522 
END
1523
1524 
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))
1525 
BEGIN
1526 
INSERT INTO dbo.PL_REQUEST_PROCESS
1527 
(
1528 
REQ_ID,
1529 
PROCESS_ID,
1530 
STATUS,
1531 
ROLE_USER,
1532 
BRANCH_ID,
1533 
CHECKER_ID,
1534 
APPROVE_DT,
1535 
PARENT_PROCESS_ID,
1536 
IS_LEAF,
1537 
COST_ID,
1538 
DVDM_ID,
1539 
NOTES,IS_HAS_CHILD
1540 
)
1541 
VALUES
1542 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1543 
'PTGDK_TT', -- PROCESS_ID - varchar(10)
1544 
'U', -- STATUS - varchar(5)
1545 
'PTGD', -- ROLE_USER - varchar(50)
1546 
'', -- BRANCH_ID - varchar(15)
1547 
'', -- CHECKER_ID - varchar(15)
1548 
NULL, -- APPROVE_DT - datetime
1549 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1550 
'N', -- IS_LEAF - varchar(1)
1551 
'', -- COST_ID - varchar(15)
1552 
@DVDM_CDT ,
1553 
N'Chờ giám đốc khối xác nhận',
1554 
0 -- DVDM_ID - varchar(15)
1555 
)
1556
1557 
END
1558
1559 
SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'PTGD'))
1560 
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)
1561 
BEGIN
1562 
SET @IS_NEXT=1
1563 
END
1564
1565 
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='PTGDK_TT'))
1566 
BEGIN
1567 
SET @STEP_PARENT='PTGDK_TT'
1568 
END
1569
1570
1571 
IF (@IS_NEXT = 1 OR @IS_NEXT_CDT=1)
1572 
BEGIN
1573 
INSERT INTO dbo.PL_REQUEST_PROCESS
1574 
(
1575 
REQ_ID,
1576 
PROCESS_ID,
1577 
STATUS,
1578 
ROLE_USER,
1579 
BRANCH_ID,
1580 
CHECKER_ID,
1581 
APPROVE_DT,
1582 
PARENT_PROCESS_ID,
1583 
IS_LEAF,
1584 
COST_ID,
1585 
DVDM_ID,
1586 
NOTES,
1587 
IS_HAS_CHILD
1588 
)
1589 
VALUES
1590 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1591 
'TGD', -- PROCESS_ID - varchar(10)
1592 
'U', -- STATUS - varchar(5)
1593 
'TGD', -- ROLE_USER - varchar(50)
1594 
'', -- BRANCH_ID - varchar(15)
1595 
'', -- CHECKER_ID - varchar(15)
1596 
NULL, -- APPROVE_DT - datetime
1597 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1598 
'N', -- IS_LEAF - varchar(1)
1599 
'', -- COST_ID - varchar(15)
1600 
'', N'Chờ tổng giám đốc xác nhận', 0 -- DVDM_ID - varchar(15)
1601 
);
1602 
SET @STEP_PARENT = 'TGD';
1603
1604 
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'))
1605 
BEGIN
1606 
INSERT INTO dbo.PL_REQUEST_PROCESS
1607 
(
1608 
REQ_ID,
1609 
PROCESS_ID,
1610 
STATUS,
1611 
ROLE_USER,
1612 
BRANCH_ID,
1613 
CHECKER_ID,
1614 
APPROVE_DT,
1615 
PARENT_PROCESS_ID,
1616 
IS_LEAF,
1617 
COST_ID,
1618 
DVDM_ID,
1619 
NOTES,
1620 
IS_HAS_CHILD
1621 
)
1622 
VALUES
1623 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1624 
'HDQT', -- PROCESS_ID - varchar(10)
1625 
'U', -- STATUS - varchar(5)
1626 
'HDQT', -- ROLE_USER - varchar(50)
1627 
'', -- BRANCH_ID - varchar(15)
1628 
'', -- CHECKER_ID - varchar(15)
1629 
NULL, -- APPROVE_DT - datetime
1630 
@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
1631 
'N', -- IS_LEAF - varchar(1)
1632 
'', -- COST_ID - varchar(15)
1633 
'', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
1634 
);
1635 
SET @STEP_PARENT = 'HDQT';
1636 
END
1637 
END;
1638
1639
1640
1641
1642 
--ELSE
1643 
--BEGIN
1644
1645 
--END
1646
1647 
END;
1648
1649 
END;
1650 
END
1651
1652
1653
1654 
END
1655
1656
1657 
END
1658
1659 
INSERT INTO dbo.PL_REQUEST_PROCESS
1660 
(
1661 
REQ_ID,
1662 
PROCESS_ID,
1663 
STATUS,
1664 
ROLE_USER,
1665 
BRANCH_ID,
1666 
CHECKER_ID,
1667 
APPROVE_DT,
1668 
PARENT_PROCESS_ID,
1669 
IS_LEAF,
1670 
NOTES
1671 
)
1672 
VALUES
1673 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1674 
'APPROVE', -- PROCESS_ID - varchar(10)
1675 
'U', -- STATUS - varchar(5)
1676 
'', -- ROLE_USER - varchar(50)
1677 
'', -- BRANCH_ID - varchar(15)
1678 
'', -- CHECKER_ID - varchar(15)
1679 
NULL, -- APPROVE_DT - datetime
1680 
@STEP_PARENT, 'Y', N'Hoàn tất');
1681
1682
1683
1684 
IF @@Error <> 0
1685 
GOTO ABORT;
1686
1687
1688
1689 
DECLARE @PROCESS_ID_CURR VARCHAR(10);
1690 
SET @PROCESS_ID_CURR =
1691 
(
1692 
SELECT TOP 1
1693 
PROCESS_ID
1694 
FROM dbo.PL_REQUEST_PROCESS
1695 
WHERE REQ_ID = @p_REQ_ID
1696 
AND PARENT_PROCESS_ID = 'APPNEW'
1697 
);
1698
1699 
UPDATE dbo.PL_REQUEST_PROCESS
1700 
SET STATUS = 'C'
1701 
WHERE PARENT_PROCESS_ID = 'APPNEW'
1702 
AND REQ_ID = @p_REQ_ID;
1703 
UPDATE dbo.PL_REQUEST_DOC
1704 
SET AUTH_STATUS = @p_AUTH_STATUS,
1705 
APPROVE_DT = @p_APPROVE_DT,
1706 
CHECKER_ID = @p_CHECKER_ID,
1707 
PROCESS_ID = @PROCESS_ID_CURR
1708 
WHERE REQ_ID = @p_REQ_ID;
1709
1710 
UPDATE dbo.PL_REQUEST_DOC_DT
1711 
SET CHECKER_ID=@p_CHECKER_ID,
1712 
APPROVE_DT=@p_APPROVE_DT
1713 
WHERE REQ_ID = @p_REQ_ID;
1714
1715 
INSERT INTO dbo.PL_PROCESS
1716 
(
1717 
REQ_ID,
1718 
PROCESS_ID,
1719 
CHECKER_ID,
1720 
APPROVE_DT,
1721 
PROCESS_DESC,
1722 
NOTES
1723 
)
1724 
VALUES
1725 
( @p_REQ_ID, -- REQ_ID - varchar(15)
1726 
'APPNEW', -- PROCESS_ID - varchar(10)
1727 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
1728 
@p_APPROVE_DT, -- APPROVE_DT - datetime
1729 
@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)
1730 
);
1731 
IF (EXISTS
1732 
(
1733 
SELECT REQ_ID
1734 
FROM dbo.PL_REQUEST_DOC
1735 
WHERE REQ_ID = @p_REQ_ID
1736 
AND PROCESS_ID = 'APPROVE'
1737 
)
1738 
)
1739 
BEGIN
1740 
EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID;
1741 
EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID;
1742 
SET @Result = '0';
1743 
END;
1744 
SET @Result = '1';
1745 
END
1746 
COMMIT TRANSACTION;
1747 
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))
1748 
BEGIN
1749 
SELECT @Result AS Result,
1750 
@ROLE_USER_NOTIFI AS ROLE_NOTIFI,
1751 
'' ErrorDesc;
1752 
RETURN '0';
1753 
END
1754 
ELSE
1755 
BEGIN
1756 
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
1757 
RETURN '4'
1758 
END
1759 
ABORT:
1760 
BEGIN
1761
1762 
ROLLBACK TRANSACTION;
1763 
SELECT '-1' AS Result,
1764 
'' ROLE_NOTIFI,
1765 
'' ErrorDesc;
1766 
RETURN '-1';
1767 
END;