CREATE PROCEDURE dbo.PL_REQUEST_DOC_App @p_REQ_ID VARCHAR(15) = NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_CHECKER_ID VARCHAR(15) = NULL, @p_APPROVE_DT VARCHAR(20) = NULL, @p_ROLE_LOGIN VARCHAR(50) = NULL, @p_BRANCH_LOGIN VARCHAR(1
5), @p_PROCESS_DESC NVARCHAR(500) AS BEGIN TRANSACTION; ---LUCTV KIEM TRA NEU TO TRINH DANG BI TRA VE THI KHONG DUOC PHEP DUYET IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='R' AND REQ_ID =@p_REQ_ID)) BEGIN ROLLBACK TRANSACTION 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 RETURN '-1' END -- IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WH
ERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='A')) BEGIN ROLLBACK TRANSACTION 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'đã được bạn phê duyệt trước đó. Vui lòng đợi các cấp phê duyệt tiế
p theo!' ErrorDesc RETURN '-1' END --SET @p_APPROVE_DT = @p_APPROVE_DT --Validation is here DECLARE @ERRORSYS NVARCHAR(15) = ''; IF (NOT EXISTS (SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)) SET @ERRORSYS = 'REQ-00002'; IF @ERRORSYS <> ''
BEGIN ROLLBACK TRANSACTION; SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS; RETURN '0'; END; DECLARE @ERROR BIT ,@EROOR_DES NVARCHAR(500) SELECT @ERROR=ERROR, @EROOR_DES=ERRO
R_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'APPNEW','PL_REQUEST_DOC',@p_CHECKER_ID,'APPNEW') IF(@ERROR=1) BEGIN ROLLBACK TRANSACTION; SELECT '-1' Result, @EROOR_DES ErrorDesc RETURN '0'; END --UPDATE dbo.PL_REQUEST_TRANSFER S
ET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103) --WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN)) DECLARE @BRANCH_TYPE_LOGIN VARCHAR
(15) SET @BRANCH_TYPE_LOGIN = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN) DECLARE @Result VARCHAR(5), @TOTAL_TRANSFER DECIMAL(18, 2), @TOTAL_AMT DECIMAL(18, 0), @ROLE_USER_NOTIFI VARCHAR(50), @ROLE_
ID VARCHAR(20), @ROLE_TF VARCHAR(20), @LIMIT_VALUE DECIMAL(18, 0), @STEP_CURR VARCHAR(20), @STEP_PARENT VARCHAR(20), @COST_ID VARCHAR(20), @FR_BRANCH_ID VARCHAR(20), @FR_DEP_ID VARCHAR(20), @
DVDM_ID VARCHAR(20), @IS_NEXT BIT = 0, @IS_NEXT_CDT BIT = 0, @TOTAL_AMT_GD DECIMAL(12, 0), @STOP BIT, @NOTES NVARCHAR(100); DECLARE @ROLE_CDT VARCHAR(20), @DVDM_CDT VARCHAR(20), @LIMIT_VALUE_CDT VARCHAR(20
), @NOTES_CDT VARCHAR(20); DECLARE @PROCESS_ID VARCHAR(5),@DVDM_NAME NVARCHAR(20) DECLARE @BRANCH_PARENT VARCHAR(15) DECLARE @SUB_PROCESS VARCHAR(50) DECLARE @DATA_DVDM TABLE ( DVDM_ID VARCHAR(20), TOTAL_AMT DECIMAL(12, 0), IS_GDK BIT, I
S_PTGD BIT ); --UPDATE dbo.PL_REQUEST_COSTCENTER --SET DVMD_ID=(SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE PL_COSTCENTER.COST_ID=PL_REQUEST_COSTCENTER.COST_ID), --TOTAL_AMT_GD=(SELECT SUM(PM.TOTAL_AMT) AS AMT FROM --(SELECT PLAN_ID,GOODS_ID,TOTAL_AMT FR
OM dbo.PL_REQUEST_DOC_DT WHERE REQDT_TYPE='I' AND REQ_ID=@p_REQ_ID) PR --LEFT JOIN dbo.PL_MASTER PM ON PR.PLAN_ID=PM.PLAN_ID --WHERE PM.COST_ID=PL_REQUEST_COSTCENTER.COST_ID) --WHERE REQ_ID=@p_REQ_ID INSERT INTO @DATA_DVDM SELECT KHOI_ID, SUM(TOTA
L_AMT) AS TOTAL_AMT,DM.IS_GDK,DM.IS_PTGD FROM dbo.PL_REQUEST_DOC_DT DT LEFT JOIN CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID = @p_REQ_ID AND DT.KHOI_ID IS NOT NULL AND DT.KHOI_ID <>'' GROUP BY KHOI_ID,DM.IS_GDK,DM.IS_PTGD; SET
@DVDM_CDT = (SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='CDT') DECLARE @IS_SPECIAL BIT SET @IS_SPECIAL=0 IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE DVDM_ID='DM0000000000004' AND REQ_ID = @p_REQ_ID)) SET @IS_SPECIAL=1 DELETE
FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID; DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10), @BRANCH_CREATE_TYPE VARCHAR(10) SELECT @BRANCH_ID =BRANCH_ID,@DE
P_ID=DEP_ID,@BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID SET @BRANCH_TYPE=(SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID) SET @BRANCH_CREATE_TYPE=(SELECT TOP 1 BRANCH_TYPE
FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE) --IF(@BRANCH_TYPE='PGD') -- SET @BRANCH_ID=(SELECT TOP 1 FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID) -- KIEM TRA XEM CO CAP PHE DUYET TRUNG GIAN HAY KHONG 20 05 2020 IF(EXISTS(S
ELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN')) BEGIN DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, AP
PROVE_DT, PROCESS_DESC,NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'SIGN', -- PROCESS_ID - varchar(10) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CONVERT(DATETIME,@p_APPROVE_DT,103) , -- APPROVE_DT - dateti
me --N'Cấp phê duyệt trung gian xác nhận tờ trình chủ trương', @p_PROCESS_DESC,--- LUCTV 2022816: THAY NỘI DUNG MẶC ĐỊNH BẰNG NỘI DUNG BÚT PHÊ N'Cấp phê duyệt trung gian' ) --- DUA CAP PHE DUYET TRUONG DON VI INSERT INTO dbo.PL_REQUEST_PR
OCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES (
@p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) --@BRANCH_CREATE, @BRANCH_ID, @DEP_ID, -- BRANCH_ID
- varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -
- DVDM_ID - varchar(15) N'Chờ trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500) NULL -- IS_HAS_CHILD - bit ) --- UPDATE PROCESS_ID VE APP_NEW UPDATE PL_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID END ELSE
BEGIN -- NGUOC LAI LA GIAM DOC DON VI PHE DUYET IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> '')) BEGIN IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ
_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_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_REQ
UEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác nhận phiếu!' ErrorDesc RETURN '-1' END IF(@p_CHECKER_ID = (SELECT SIGN_USER FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, N'Tờ trình chủ trương số:
'+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi trưởng đơn vị phê duyệt. Bạn không có thẩm quyền phê duyệt cấp trưởng đơn vị! Vui lòng xem lịch sử xử lý phiếu' ErrorDesc RETURN '-1' END END INSERT INTO dbo.PL_REQUES
T_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW',
-- PROCESS_ID - varchar(10) 'P', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) @BRANCH_ID , @DEP_ID, -- BRANCH_ID - varchar(15) @p_CHECKER_ID, -- CHE
CKER_ID - varchar(15) GETDATE() , -- APPROVE_DT - datetime NULL, 'N', N'Trưởng đơn vị phê duyệt'); SET @STEP_PARENT = 'APPNEW'; UPDATE prdd SET prdd.AMT_APP = ISNULL(PL.AMT_APP,0), prdd.AMT_EXE = ISNULL(PL.AMT_EXE,0), prdd.AMT_ETM
= ISNULL(PL.AMT_ETM,0), prdd.AMT_TF = ISNULL(PL.AMT_TF,0), prdd.AMT_RECEIVE_TF = ISNULL(PL.AMT_RECEIVE_TF,0), prdd.AMT_ETM_TMP = (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_DOC_DT DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC
ON DDT.REQ_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> prdd.REQ_ID) + (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL
_REQUEST_TRANSFER DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.FR_TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> @p_REQ_ID)
FROM PL_TRADEDETAIL PL LEFT JOIN PL_REQUEST_DOC_DT prdd ON PL.TRADE_ID = prdd.TRADE_ID WHERE prdd.REQ_ID=@P_REQ_ID UPDATE prdd SET prdd.FR_AMT_APP = ISNULL(PL.AMT_APP,0), prdd.FR_AMT_EXE = ISNULL(PL.AMT_EXE,0), prdd.FR_AMT_ETM
= ISNULL(PL.AMT_ETM,0), prdd.FR_AMT_TF = ISNULL(PL.AMT_TF,0), prdd.FR_AMT_RECEIVE_TF = ISNULL(PL.AMT_RECEIVE_TF,0), prdd.FR_AMT_ETM_TMP = (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_DOC_DT DDT LEFT JOIN dbo.PL_REQUEST_
DOC DOC ON DDT.REQ_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> prdd.REQ_DOC_ID) + (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
FROM dbo.PL_REQUEST_TRANSFER DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.FR_TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> p
rdd.REQ_DOC_ID) FROM PL_TRADEDETAIL PL LEFT JOIN PL_REQUEST_TRANSFER prdd ON PL.TRADE_ID = prdd.FR_TRADE_ID WHERE prdd.REQ_DOC_ID=@P_REQ_ID UPDATE prdd SET prdd.TO_AMT_APP = ISNULL(PL.AMT_APP,0), prdd.TO_AMT_EXE = ISNULL(PL.AMT_E
XE,0), prdd.TO_AMT_ETM = ISNULL(PL.AMT_ETM,0), prdd.TO_AMT_TF = ISNULL(PL.AMT_TF,0), prdd.TO_AMT_RECEIVE_TF = ISNULL(PL.AMT_RECEIVE_TF,0), prdd.TO_AMT_ETM_TMP = (SELECT ISNULL(SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_DOC_DT DDT
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.TRADE_ID = PL.TRADE_ID AND DOC.REQ_ID <> prdd.REQ_DOC_ID) + (SELECT ISNULL(
SUM(DDT.TOTAL_AMT),0) FROM dbo.PL_REQUEST_TRANSFER DDT LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT') AND DDT.FR_TRADE_ID = PL.TRADE_I
D AND DOC.REQ_ID <> prdd.REQ_DOC_ID) FROM PL_TRADEDETAIL PL LEFT JOIN PL_REQUEST_TRANSFER prdd ON PL.TRADE_ID = prdd.TO_TRADE_ID WHERE prdd.REQ_DOC_ID=@P_REQ_ID -- Nếu khổng phải tờ trình có chọn căn cứ IF(NOT EXISTS(SELECT * FRO
M dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PL_BASED_ID IS NOT NULL AND PL_BASED_ID <>'')) BEGIN DECLARE @ROLE_KT VARCHAR(20), @DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(500),@LIMIT_VALUE_KT DECIMAL(18,2),@TOTAL_AMT_PARENT DECIMAL(18,2) SET @ROLE_
KT=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT') SET @LIMIT_VALUE_KT=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE = 'KT') SET @DVDM_KT=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT') SET @NOTES_K
T = (SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDVAL='KT' AND CDNAME='PROCESS_ID' AND CDTYPE='REQ') SET @TOTAL_AMT_PARENT = (SELECT SUM(ISNULL(TOTAL_AMT, 0)) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = (SELECT REQ_PARENT_ID FROM dbo.PL_R
EQUEST_DOC WHERE REQ_ID = @p_REQ_ID)) --SET @TOTAL_AMT_PARENT = (SELECT SUM(ISNULL(CASE WHEN G.MONTHLY_ALLOCATED = '1' THEN (PRDT.PRICE*PRDT.EXCHANGE_RATE)+(PRDT.TAXES*PRDT.EXCHANGE_RATE) ELSE PRDT.TOTAL_AMT END, 0)) AS TOTAL_AMT -- FROM dbo.PL_REQUES
T_DOC_DT PRDT -- LEFT JOIN dbo.CM_GOODS G ON G.GD_ID = PRDT.GOODS_ID -- WHERE REQ_ID = (SELECT REQ_PARENT_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)) -- Kế toán IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_RE
Q_ID AND ROLE_USER=@ROLE_KT AND ( DVDM_ID=@DVDM_KT OR @DVDM_KT IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQU
EST_PROCESS.DEP_ID)) ) )) BEGIN -- Kiểm tra nếu hạn mức >10 triệu đồng thì mới qua phòng kế toán SET @TOTAL_AMT = (SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = @p_REQ_ID) + ISNULL(@TOTAL_AMT_PAREN
T,0) --SET @TOTAL_AMT = (SELECT SUM(CASE WHEN G.MONTHLY_ALLOCATED = '1' THEN (PRDT.PRICE*PRDT.EXCHANGE_RATE)+(PRDT.TAXES*PRDT.EXCHANGE_RATE) ELSE PRDT.TOTAL_AMT END) AS TOTAL_AMT -- FROM dbo.PL_REQUEST_DOC_DT PRDT -- LEFT JOIN dbo.CM_GOODS
G ON G.GD_ID = PRDT.GOODS_ID -- WHERE REQ_ID = @p_REQ_ID) + ISNULL(@TOTAL_AMT_PARENT,0) IF (ISNULL(@LIMIT_VALUE_KT,10000000)<@TOTAL_AMT OR EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID)) BEGIN SET @SUB
_PROCESS = '' IF (EXISTS(SELECT * FROM PL_REQUEST_COSTCENTER prc WHERE prc.REQ_ID = @p_REQ_ID AND prc.COST_ID = 'DM0000000000006') AND EXISTS(SELECT * FROM PL_REQUEST_TRANSFER prt WHERE prt.REQ_DOC_ID = @p_REQ_ID AND prt.FR_BRN_ID = 'D
V0001' AND prt.FR_DEP_ID = 'DEP000000000022' AND prt.FR_BRN_ID <> @BRANCH_CREATE)) BEGIN SET @SUB_PROCESS = 'DVCM/DVDC' END ELSE IF (EXISTS(SELECT * FROM PL_REQUEST_COSTCENTER prc WHERE prc.REQ_ID = @p_REQ_ID AND
prc.COST_ID = 'DM0000000000006')) BEGIN SET @SUB_PROCESS = 'DVCM' END ELSE IF (EXISTS(SELECT * FROM PL_REQUEST_TRANSFER prt WHERE prt.REQ_DOC_ID = @p_REQ_ID AND prt.FR_BRN_ID = 'DV0001' AND prt.FR_DEP_ID = 'DEP000
000000022' AND prt.FR_BRN_ID <> @BRANCH_CREATE)) BEGIN SET @SUB_PROCESS = 'DVDC' END INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID,
CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD, SUB_PROCESS_ID ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'KT'
, -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) @ROLE_KT, -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPR
OVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) @DVDM_KT, N'Chờ phòng kế toán xác nhận', 1, -- DVDM_ID - varchar(15)
@SUB_PROCESS); SET @STEP_PARENT='KT' END END --- LUCTV 2022812: NEU TO TRINH DIEU CHUYEN <=20 TRIEU THI KHONG DI QUA DVDM_DC NGAN SACH DECLARE @TOTAL_AMT_TRANSFER DECIMAL(18,0) SET @TOTAL_AMT_TRANSFER =(SELECT SUM(TOTAL_AMT) AS
TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID) ---END LUCTV -- Có DVCM IF (EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID = @p_REQ_ID)) BEGIN DECLARE lstCostCenter CURSOR FOR SELECT COST_ID FR
OM dbo.PL_REQUEST_COSTCENTER PRC WHERE REQ_ID = @p_REQ_ID AND COST_ID IS NOT NULL AND COST_ID <> '' AND ((@TOTAL_AMT_TRANSFER > 20000000 AND PRC.COST_ID <> 'DM0000000000048') OR @TOTAL_AMT_TRANSFER <= 20000000) AND ((EXISTS(SELECT ID FR
OM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'KT') AND PRC.COST_ID <> 'DM0000000000006') OR NOT EXISTS(SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'KT')) AND NOT EXISTS(SELECT REQ_TRANSF
ER_ID FROM dbo.PL_REQUEST_TRANSFER A LEFT JOIN PL_COSTCENTER_DT pcd ON A.FR_BRN_ID = pcd.BRANCH_ID AND A.FR_DEP_ID = pcd.DEP_ID LEFT JOIN PL_COSTCENTER pc ON pcd.COST_ID = pc.COST_ID WHERE REQ_DOC_ID = @p_REQ_ID AND pc.DVDM_ID =
PRC.COST_ID AND ((A.FR_BRN_ID <> 'DV0001' AND A.FR_BRN_ID <> @BRANCH_CREATE) OR (A.FR_BRN_ID = 'DV0001' AND A.FR_DEP_ID <> @DEP_CREATE))); OPEN lstCostCenter; FETCH NEXT FROM lstCostCenter INTO @COST_ID; WHILE @@FETCH_STATUS = 0 BEGIN I
F(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 LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID))))) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID,
PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15)
'DVCM', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_
DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) @COST_ID, N'Chờ đơn vị chuyên môn xác nhận', 1 -- DVDM_ID - varchar(15) ); END
ELSE BEGIN UPDATE PL_REQUEST_COSTCENTER SET AUTH_STATUS ='A',NOTES=N'Đồng ý' WHERE 1= 1 AND REQ_ID=@p_REQ_ID AND COST_ID=@COST_ID END FETCH NEXT FROM lstCostCenter INTO @COST_ID; END; CLOSE lstCostCenter; DEALLOCAT
E lstCostCenter; IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM')) SET @STEP_PARENT = 'DVCM'; END; SET @TOTAL_AMT =(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = @p_RE
Q_ID); --Có điều chuyển NS IF (EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID)) BEGIN IF (EXISTS(SELECT FR_BRN_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID AND((FR_BRN_ID <> 'DV0001' A
ND FR_BRN_ID <> @BRANCH_CREATE) OR (FR_BRN_ID = 'DV0001' AND FR_DEP_ID <> @DEP_CREATE)) --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) )) BEGIN D
ECLARE lstTransfer CURSOR FOR SELECT FR_BRN_ID,FR_DEP_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID AND(FR_BRN_ID <> @BRANCH_CREATE OR FR_DEP_ID <> @DEP_CREATE) --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) -- AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND (ROLE_USER='GDDV' OR ROLE_USER=@ROLE_KT) AND DVDM_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC
-- LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID -- WHERE PCD.BRANCH_ID=FR_BRN_ID AND PCD.DEP_ID=FR_DEP_ID))) GROUP BY FR_BRN_ID, FR_DEP_ID HAVING ((FR_BRN_ID = 'DV0001' AND ((EXISTS(SELECT ID FROM PL_RE
QUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'KT') AND FR_DEP_ID <> 'DEP000000000022') OR NOT EXISTS(SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'KT')) AND ((@TOTAL_AMT_TRANSFER > 20000000 AND F
R_DEP_ID <> 'DEP000000000023') OR (@TOTAL_AMT_TRANSFER <= 20000000))) OR FR_BRN_ID <> 'DV0001') OPEN lstTransfer; FETCH NEXT FROM lstTransfer INTO @FR_BRANCH_ID, @FR_DEP_ID; WHILE @@FETCH_STATUS = 0 BEGIN IF(NOT EXISTS(SE
LECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND (ROLE_USER='GDDV' OR ROLE_USER=@ROLE_KT) AND DVDM_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID WHERE PCD.BRANCH
_ID=@FR_BRANCH_ID AND PCD.DEP_ID=@FR_DEP_ID)))) BEGIN SET @SUB_PROCESS = '' IF (EXISTS(SELECT * FROM PL_REQUEST_COSTCENTER A LEFT JOIN PL_COSTCENTER pc ON A.COST_ID = pc.DVDM_ID LEFT JOIN PL_COSTCENTER_DT pcd1 ON pc
.COST_ID = pcd1.COST_ID WHERE A.REQ_ID = @p_REQ_ID AND pcd1.BRANCH_ID = @FR_BRANCH_ID AND pcd1.DEP_ID = @FR_DEP_ID)) BEGIN SET @SUB_PROCESS = 'DVCM' END INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCE
SS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD, DEP_ID, SUB_PROCESS_ID ) VALUES ( @p_REQ_ID
, -- REQ_ID - varchar(15) 'DVDC', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) @FR_BRANCH_ID, -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varc
har(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ đơ
n vị điều chuyển xác nhận', 1, @FR_DEP_ID, @SUB_PROCESS); END -- ELSE -- BEGIN -- UPDATE PL_REQUEST_PROCESS SET SUB_PROCESS_ID = 'DVDC' WHERE REQ_ID = @p_REQ_ID AND (ROLE_USER='GDDV' OR ROLE_USER=@ROLE_KT) AND DVDM
_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC -- LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID -- WHERE PCD.BRANCH_ID=@FR_BRANCH_ID AND PCD.DEP_ID=@FR_DEP_ID)) -- END FETCH NEXT FROM lstTransfer INTO @F
R_BRANCH_ID, @FR_DEP_ID; END; CLOSE lstTransfer; DEALLOCATE lstTransfer; IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDC')) SET @STEP_PARENT = 'DVDC'; END; -- Đầu mối nhận DECLARE @
TABLE_TRANFER TABLE ( TRADE_ID VARCHAR(20), TOTAL_TRANFER DECIMAL(18,2) ) DECLARE @TABLE_TRANFER_APP TABLE ( TRADE_ID VARCHAR(20), TOTAL_APP DECIMAL(18,2) ) DECLARE @LIMIT_MAX DECIMAL(18,2),@LIMIT_APP DECIMAL(18,2),@IS_NOIBO BIT,@BRAN
CH_TRANFER VARCHAR(15),@DEP_TRANFER VARCHAR(15),@OVER_LIMT BIT IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND TO_BRN_ID <> FR_BRN_ID OR ISNULL(TO_DEP_ID,'') <> ISNULL(FR_DEP_ID,'')) ) BEGIN SET @IS_NO
IBO=0 END ELSE SET @IS_NOIBO=1 IF(@IS_NOIBO=1) BEGIN SET @BRANCH_TRANFER=(SELECT TOP 1 FR_BRN_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID) SET @DEP_TRANFER =(SELECT TOP 1 FR_BRN_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_
DOC_ID=@p_REQ_ID) INSERT INTO @TABLE_TRANFER ( TRADE_ID, TOTAL_TRANFER ) SELECT FR_TRADE_ID,SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID GROUP BY FR_BRN_ID,FR_DEP_ID,FR_TRADE_ID --- Hạn mức phê duyệ
t SET @LIMIT_MAX=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='GDDV' AND LIMIT_TYPE='LIMIT_DCNS') ---- Tính lũy kế phê duyệt INSERT INTO @TABLE_TRANFER_APP ( TRADE_ID, TOTAL_APP ) SELECT FR_TRADE_ID,SUM(TOTAL_AMT) AS
TOTAL_APP FROM dbo.PL_REQUEST_TRANSFER WHERE TOTAL_AMT <= @LIMIT_MAX AND FR_BRN_ID=TO_BRN_ID AND ISNULL(TO_DEP_ID,'') = ISNULL(FR_DEP_ID,'') AND REQ_DOC_ID IN ( SELECT REQ_ID FROM dbo.PL_REQUEST_PROCESS WHERE BRANCH_ID=@BRANCH_TRANFER AND DEP_ID=@DE
P_TRANFER AND PROCESS_ID='APPNEW' AND STATUS='P' ) GROUP BY FR_TRADE_ID IF(EXISTS( SELECT BT.TRADE_ID FROM @TABLE_TRANFER BT LEFT JOIN @TABLE_TRANFER_APP BTA ON BTA.TRADE_ID = BT.TRADE_ID WHERE ISNULL(BT.TOTAL_TRANFER,0) + ISNULL(BTA.TOTAL
_APP,0) > @LIMIT_MAX )) BEGIN SET @OVER_LIMT=1 END ELSE SET @OVER_LIMT =0 END IF(@IS_NOIBO =0 OR @OVER_LIMT=1) BEGIN DECLARE lstTransfer CURSOR FOR SELECT TO_DVDM_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_
ID AND TO_DVDM_ID IS NOT NULL AND TO_DVDM_ID <>'' AND TO_DVDM_ID <>'DM0000000000048' AND ( (TO_DVDM_ID ='DM0000000000003' AND ISNULL(@TOTAL_AMT_TRANSFER,0) >=10000000) OR TO_DVDM_ID <> 'DM0000000000003')--- LUCTV 2022812: NEU TO TRINH DIEU CHUYEN
<=20 TRIEU THI KHONG DI QUA DVDM_DC NGAN SACH GROUP BY TO_DVDM_ID; OPEN lstTransfer; FETCH NEXT FROM lstTransfer INTO @DVDM_ID; WHILE @@FETCH_STATUS = 0 BEGIN 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 LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID =
PC.COST_ID WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) )) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID,
APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'DVDM_DC', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) '
GDDV', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF
- varchar(1) '', -- COST_ID - varchar(15) @DVDM_ID, -- DVDM_ID - varchar(15) N'Chờ đơn vị đầu mối quản lý NS nhận xác nhận', 1); END FETCH NEXT FROM lstTransfer INTO @DVDM_ID; END; CLOSE lstTransfer; DEALLOCATE ls
tTransfer; IF (EXISTS( SELECT FR_BRN_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID <> @BRANCH_CREATE AND FR_DEP_ID <> @DEP_CREATE )) BEGIN -- Đầu mối cho DECLARE lstTransfer CURSOR
FOR SELECT FR_DVDM_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID <> @BRANCH_CREATE AND FR_DEP_ID <> @DEP_CREATE AND FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>'' AND NOT EXISTS ( SELECT * F
ROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DVDM_DC' AND DVDM_ID = FR_DVDM_ID ) --- LUCTV 2022816 AND (FR_DVDM_ID <>'DM0000000000048' OR (FR_DVDM_ID ='DM0000000000003' AND ISNULL(@TOTAL_AMT_TRANS
FER,0) >=10000000))--- LUCTV 2022816: NEU TO TRINH DIEU CHUYEN <=20 TRIEU THI KHONG DI QUA DVDM_DC NGAN SACH GROUP BY FR_DVDM_ID; OPEN lstTransfer; FETCH NEXT FROM lstTransfer INTO @DVDM_ID; WHILE @@FETCH_STATUS = 0 BEGIN IF(NOT EXI
STS(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 LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) )) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID,
PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15)
'DVDM_DC', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_D
T - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) @DVDM_ID, -- DVDM_ID - varchar(15) N'Chờ đơn vị đầu mối xác nhận', 0); END FETCH
NEXT FROM lstTransfer INTO @DVDM_ID; END; CLOSE lstTransfer; DEALLOCATE lstTransfer; IF(EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDM_DC')) SET @STEP_PARENT='DVDM_DC' END;
IF (@TOTAL_AMT_TRANSFER > 20000000) BEGIN SET @SUB_PROCESS = '' IF (EXISTS(SELECT * FROM PL_REQUEST_COSTCENTER prc WHERE prc.REQ_ID = @p_REQ_ID AND prc.COST_ID = 'DM0000000000048') AND EXISTS(SELECT * FROM PL_REQUEST_TRANSFER prt WHERE pr
t.REQ_DOC_ID = @p_REQ_ID AND prt.FR_BRN_ID = 'DV0001' AND prt.FR_DEP_ID = 'DEP000000000023' AND prt.FR_BRN_ID <> @BRANCH_CREATE)) BEGIN SET @SUB_PROCESS = 'DVCM/DVDC' END ELSE IF (EXISTS(SELECT * FROM PL_REQUEST_COSTCENTER prc WHERE prc.
REQ_ID = @p_REQ_ID AND prc.COST_ID = 'DM0000000000048')) BEGIN SET @SUB_PROCESS = 'DVCM' END ELSE IF (EXISTS(SELECT * FROM PL_REQUEST_TRANSFER prt WHERE prt.REQ_DOC_ID = @p_REQ_ID AND prt.FR_BRN_ID = 'DV0001' AND prt.FR_DEP_ID = 'DEP0000
00000023' AND prt.FR_BRN_ID <> @BRANCH_CREATE)) BEGIN SET @SUB_PROCESS = 'DVDC' END INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID,
IS_LEAF, COST_ID, DVDM_ID, NOTES,IS_HAS_CHILD, SUB_PROCESS_ID ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'TC', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'TC', -- ROLE_USER -
varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', --
COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ đơn vị Tài chính xác nhận',1, @SUB_PROCESS); SET @STEP_PARENT = 'TC'; END END END; --IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_RE
Q_ID AND ROLE_USER='GDDV' --AND (( -- BRANCH_ID=@BRANCH_CREATE -- AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='') -- AND (DEP_ID IS NULL OR DEP_ID=''))) -- ) -- OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC
-- 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) -- ) --)) --BEGIN --INSERT INTO dbo.PL_REQUEST_PROCESS -- ( -- REQ_ID, --
PROCESS_ID, -- STATUS, -- ROLE_USER, -- BRANCH_ID, -- DEP_ID, -- CHECKER_ID, -- APPROVE_DT, -- PARENT_PROCESS_ID, -- IS_LEAF, -- NOTES -- ) -- VALUES -- ( -- @p_REQ_ID, -- REQ_ID - varchar(15) --
'DVC', -- PROCESS_ID - varchar(10) -- 'U', -- STATUS - varchar(5) -- 'GDDV', -- ROLE_USER - varchar(50) -- @BRANCH_CREATE, -- @DEP_CREATE, -- BRANCH_ID - varchar(15)
-- NULL, -- CHECKER_ID - varchar(15) -- NULL , -- APPROVE_DT - datetime -- @STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt'); --SET @STEP_CURR = 'DVC'; --SET @STEP_PARENT = 'DVC'; --END SET @IS_NEXT_CDT=(SELECT dbo.FN_CHE
CK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDDV')) IF(EXISTS( SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID) OR @IS_NEXT_CDT=1) BEGIN SET @IS_NEXT = ( SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDDV') ); IF (@IS_NEXT = 1 OR @IS_NEXT_CDT=
1) BEGIN DECLARE lstCostCenter CURSOR FOR SELECT DVDM_ID, TOTAL_AMT FROM @DATA_DVDM WHERE IS_GDK=1; OPEN lstCostCenter; FETCH NEXT FROM lstCostCenter INTO @DVDM_ID, @TOTAL_AMT_GD; WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD )
VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'GDK_TT', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'GDK',
-- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- A
PPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) @
DVDM_ID, N'Chờ Giám đốc khối phê duyệt', 0 -- DVDM_ID - varchar(15) ); FETCH NEXT FROM lstCostCenter INTO @DVDM_ID, @TOTAL_AMT_GD; END; CLOSE lstCostCenter; DEALLOCATE lstCostCenter; 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)) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROV
E_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES,IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'GDK_TT', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5)
'GDK', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF
- varchar(1) '', -- COST_ID - varchar(15) @DVDM_CDT , N'Chờ Giám đốc khối phê duyệt chỉ định thầu', 0 -- DVDM_ID - varchar(15) ) END SET @IS_NEXT = ( SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_I
D, 'GDK') ); IF(EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_GDK=0) AND (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDDV'))=1) BEGIN SET @IS_NEXT=1 END SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDK'))
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='GDK_TT')) BEGIN SET @STEP_PARENT='GDK_TT' END --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) IF (@IS_NEXT = 1 OR @IS_NEXT_CDT =1) BEGIN IF( EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=1) ) BEGIN DECLARE
lstCostCenter CURSOR FOR SELECT DVDM_ID, TOTAL_AMT 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_I
D) ; OPEN lstCostCenter; FETCH NEXT FROM lstCostCenter INTO @DVDM_ID, @TOTAL_AMT_GD; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, RO
LE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID,
-- REQ_ID - varchar(15) 'PTGDK_TT', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'PTGD', --
ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL,
-- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '',
-- COST_ID - varchar(15) @DVDM_ID, N'Chờ phó tổng giám đốc khối phê duyệt', 0 -- DVDM_ID - varchar(15) ); FETCH NEXT FROM lstCostCenter INTO @DVDM_ID, @TOTAL_AMT_GD; END; CLOSE lstCostCenter; DEA
LLOCATE lstCostCenter; SET @IS_NEXT = ( SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'PTGD') ); END IF(@IS_SPECIAL=1 AND NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='PTGDK_TT' AND
DVDM_ID='DM0000000000014')) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID,
DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'PTGDK_TT', -- PROCESS_ID - varchar(10) 'U',
-- STATUS - varchar(5) 'PTGD', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '',
-- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N',
-- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) 'DM0000000000014', N'Chờ Phó tổng giám đốc khối phê duyệt', 0 -- DVDM_ID - varchar(15) );
END 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) AND EXISTS(SELECT DVDM_ID FROM CM_DVDM WHERE DVDM_ID =@DVDM_CDT AND IS_PTGD =1) ) -- 19.10.2022 LUCTV
FIX BO SUNG THEM DIEU KIEN NEU KHOI DO CO PTGD THI MOI ADD PTGD VAO TO TRINH BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID,
CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES,IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'PTGDK_TT',
-- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'PTGD', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE
_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) @DVDM_CDT , N'Chờ Phó Tổng giám đốc khối phê duyệt',
0 -- DVDM_ID - varchar(15) ) END SET @IS_NEXT_CDT=(SELECT dbo.FN_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'PTGD')) IF(EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=0 ) AND @IS_SPECIAL <> 1 AND (SELECT dbo.FN_CHECK_LIMIT_PL_REQ
(@p_REQ_ID,'GDK'))=1) BEGIN SET @IS_NEXT=1 END IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='PTGDK_TT')) BEGIN SET @STEP_PARENT='PTGDK_TT' END IF (@IS_NEXT = 1 OR @IS_NEXT_CDT=1)
BEGIN ---- THEM THU KI TGD INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID,
DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'TKTGD', -- PROCESS_ID - varchar(10) 'U', -- S
TATUS - varchar(5) 'TKTGD', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL,
-- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(1
5) '', N'Chờ Thư Ký TGĐ xác nhận', 1 -- DVDM_ID - varchar(15) ); SET @STEP_PARENT = 'TKTGD'; ---- END THU KY TGD INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRAN
CH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'TGD',
-- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'TGD', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15)
'', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N',
-- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', N'Chờ Tổng giám đốc phê duyệt', 0 -- DVDM_ID - varchar(15) ); SET @STEP_PARENT = 'TGD'; SET @IS_NEXT = ( SELECT dbo.FN_CHE
CK_LIMIT_PL_REQ(@p_REQ_ID, 'TGD') ); IF(@IS_NEXT=1) BEGIN ---- THEM THU KI HDQT INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPRO
VE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'TKHDQT', -- PROCESS_
ID - varchar(10) 'U', -- STATUS - varchar(5) 'TKHDQT', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '',
-- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1)
'', -- COST_ID - varchar(15) '', N'Chờ Văn Phòng Thư Ký HĐQT xác nhận', 1 -- DVDM_ID - varchar(15) ); SET @STEP_PARENT = 'TKHDQT'; ---- END THU KY HDQT INSERT INTO dbo.PL_REQUEST_PROCESS
( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_
REQ_ID, -- REQ_ID - varchar(15) 'HDQT', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) 'HDQT', -- ROLE_USER -
varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime @STEP_PARENT,
-- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', N'Chờ Chủ Tịch Hội Đồng Quản Trị phê duyệt', 0 -- DVDM_ID - varcha
r(15) ); SET @STEP_PARENT = 'HDQT'; END END; --ELSE --BEGIN --END END; END; END END -- Nếu là tờ trình căn cứ và tồn tại hình thức chỉ định thầu ELSE IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE
REQ_ID = @p_REQ_ID AND TRADE_TYPE = 'CDT') AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID = (SELECT PL_BASED_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID) AND TRADE_TYPE = 'CDT')) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS
( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES,IS_HAS_CHILD ) VALUES ( @p_REQ_ID, 'GDK_TT', 'U', '
GDK', '', '', NULL, @STEP_PARENT, 'N', '', @DVDM_CDT, N'Chờ giám đốc khối xác nhận', 0 ) SET @STEP_PARENT = 'GDK_TT' -- Nếu tổng giá trị chỉ định thầu lớn hơn hạn mức phê duyệt của GDK IF ((SELECT dbo.FN
_CHECK_LIMIT_PL_REQ_CDT(@p_REQ_ID,'GDK')) = 1) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, CO
ST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, 'PTGDK_TT', 'U', 'PTGD', '', '', NULL, @STEP_PARENT, 'N', '', @DVDM_CDT, N'Chờ phó tổng giám đốc khối xác n
hận', 0 ) SET @STEP_PARENT = 'PTGDK_TT' END END INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, NOTES ) V
ALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPROVE', -- PROCESS_ID - varchar(10) 'U', -- STATUS - varchar(5) '', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NU
LL, -- APPROVE_DT - datetime @STEP_PARENT, 'Y', N'Hoàn tất'); IF @@Error <> 0 GOTO ABORT; DECLARE @PROCESS_ID_CURR VARCHAR(10); SET @PROCESS_ID_CURR = ( SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ
_ID = @p_REQ_ID AND PARENT_PROCESS_ID = 'APPNEW' ); UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'C' WHERE PARENT_PROCESS_ID = 'APPNEW' AND REQ_ID = @p_REQ_ID; UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS = @p_AUTH_STATUS, APPROVE_DT
= CONVERT(DATETIME, @p_APPROVE_DT,103), CHECKER_ID = @p_CHECKER_ID, PROCESS_ID = @PROCESS_ID_CURR WHERE REQ_ID = @p_REQ_ID; UPDATE dbo.PL_REQUEST_DOC_DT SET CHECKER_ID=@p_CHECKER_ID, APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT,103) WHERE
REQ_ID = @p_REQ_ID; INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW',
-- PROCESS_ID - varchar(10) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) CONVERT(DATETIME, @p_APPROVE_DT,103), -- APPROVE_DT - datetime
@p_PROCESS_DESC, 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) ); IF (EXISTS ( SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_
ID = @p_REQ_ID AND PROCESS_ID = 'APPROVE' ) ) BEGIN EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID; EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID; SET @Result = '0'; END; SET @Result = '1'; END
COMMIT TRANSACTION; IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID)) BEGIN SELECT '0' 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 trưởng đơn vị phê d
uyệt thành công.' ErrorDesc; RETURN '0'; END ELSE BEGIN 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ị p
hê duyệt' ErrorDesc RETURN '4' END ABORT: BEGIN ROLLBACK TRANSACTION; SELECT '-1' AS Result, '' ROLE_NOTIFI, '' ErrorDesc; RETURN '-1'; END;