DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

CREATE PROCEDURE [dbo].[TR_REQ_PROCESS_CHILD_App]  

@p_REQ_ID VARCHAR(20),  

@p_PROCESS_ID VARCHAR(20),  

@p_TLNAME VARCHAR(20),  

@p_MAKER_ID VARCHAR(20),  

@p_TYPE_JOB VARCHAR(20),  

@p_PROCESS_DES NVARCHAR(500),  

@p_REF_ID INT,  

@p_XMLDATA XML  

AS  

BEGIN TRANSACTION  

-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC  

--IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID=@p_PROCESS_ID))  

--BEGIN  

-- SELECT 1 as Result, '' ErrorDesc  

-- RETURN 0  

--END  

IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))  

OR(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='R')))  

BEGIN  

ROLLBACK TRANSACTION  

SELECT -1 Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_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  

Declare @hdoc INT  

EXEC sp_xml_preparedocument @hdoc Output,@p_XMLDATA  

DECLARE @lstFILE TABLE(  

ATTACH_ID VARCHAR(20),  

IS_READ BIT  

)  

INSERT INTO @lstFILE  

SELECT *  

FROM OPENXML(@hDoc,'/Root/ATTACH_FILE',2)  

WITH  

(  

ATTACH_ID VARCHAR(20),  

IS_READ BIT  

)  

IF(EXISTS(SELECT TR_REQUEST_DOC_FILE_ID FROM dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REQ_ID AND IS_VIEW=1  

AND EXISTS(SELECT ATTACH_ID FROM @lstFILE WHERE [@lstFILE].ATTACH_ID=TR_REQUEST_DOC_FILE.ATTACH_ID AND IS_READ=0)))  

BEGIN  

ROLLBACK TRANSACTION  

SELECT -1 Result, N'File đinh kèm bắt buộc đọc' ErrorDesc  

RETURN 0  

END  

DECLARE @LEVEL INT, @TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)  

UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='P'  

WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB  

SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB  

FROM dbo.PL_REQUEST_PROCESS_CHILD  

WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB  

ORDER BY LEVEL_JOB DESC),0)  

UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C'  

WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND LEVEL_JOB=(@LEVEL-1)  

SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@p_TYPE_JOB)  

SET @FULLNAME =(SELECT TLFullName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME )  

INSERT INTO dbo.PL_PROCESS  

(  

REQ_ID,  

PROCESS_ID,  

CHECKER_ID,  

APPROVE_DT,  

PROCESS_DESC,NOTES  

)  

VALUES  

( @p_REQ_ID, -- REQ_ID - varchar(15)  

@p_PROCESS_ID, -- PROCESS_ID - varchar(10)  

@p_MAKER_ID, -- CHECKER_ID - varchar(15)  

GETDATE() , -- APPROVE_DT - datetime  

@p_PROCESS_DES ,  

@TYPE_JOB_NAME+ N' đã phê duyệt' -- PROCESS_DESC - nvarchar(1000)  

)  

IF(NOT EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD  

WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND STATUS_JOB <> 'P'))  

BEGIN  

IF(@p_PROCESS_ID='DMMS')  

BEGIN  

DECLARE @PROCESS_PARENT VARCHAR(20)  

SET @PROCESS_PARENT='DMMS'  

UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE(),NOTES=N'Đầu mối mua sắm đã phê duyệt' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_PROCESS_ID  

DECLARE @LIMIT_VALUE_KT DECIMAL(18,0),@ROLE_KT VARCHAR(20),@DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(200),@TOTAL_AMT_REQ DECIMAL(18,0)  

SET @LIMIT_VALUE_KT = (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')  

SET @ROLE_KT=(SELECT ROLE_ID 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 @TOTAL_AMT_REQ =(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT  

WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' OR NOTES ='CDT'))  

IF(@TOTAL_AMT_REQ > @LIMIT_VALUE_KT AND 1 > 2)  

BEGIN  

DECLARE @l_REQ_COST_ID VARCHAR(15)  

EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out  

INSERT dbo.TR_REQUEST_COSTCENTER  

(  

REQ_COST_ID,  

COST_ID,  

REQ_ID,  

NOTES,  

AUTH_STATUS,  

MAKER_ID,  

CREATE_DT,  

CHECKER_ID,  

APPROVE_DT  

)  

VALUES  

( @l_REQ_COST_ID, -- REQ_COST_ID - varchar(15)  

@DVDM_KT, -- COST_ID - varchar(15)  

@p_REQ_ID, -- REQ_ID - varchar(15)  

N'', -- NOTES - nvarchar(500)  

'U', -- AUTH_STATUS - varchar(1)  

@p_MAKER_ID, -- MAKER_ID - varchar(15)  

GETDATE(), -- CREATE_DT - datetime  

'', -- CHECKER_ID - varchar(15)  

NULL -- APPROVE_DT - datetime  

)  

END  

IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID))  

BEGIN  

DECLARE @COST_ID VARCHAR(20)  

DECLARE lstCostCenter CURSOR FOR  

SELECT COST_ID FROM dbo.TR_REQUEST_COSTCENTER  

WHERE REQ_ID=@p_REQ_ID  

OPEN lstCostCenter  

FETCH NEXT FROM lstCostCenter INTO @COST_ID  

WHILE @@FETCH_STATUS = 0  

BEGIN  

IF(@COST_ID=@DVDM_KT AND 1> 2)  

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)  

'KSV', -- ROLE_USER - varchar(50)  

'', -- BRANCH_ID - varchar(15)  

'', -- CHECKER_ID - varchar(15)  

NULL, -- APPROVE_DT - datetime  

@PROCESS_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  

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  

@PROCESS_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  

FETCH NEXT FROM lstCostCenter INTO @COST_ID  

END  

CLOSE lstCostCenter  

DEALLOCATE lstCostCenter  

END  

DECLARE @TOTAL_AMT DECIMAL(18,2),@ROLE_PDTH VARCHAR(20) ,@LIMIT_VALUE DECIMAL(18,2) ,@IS_NEXT BIT,@PROCESS_ID VARCHAR(5),@ROLE_ID VARCHAR(20),@DVDM_ID VARCHAR(20),@NOTES NVARCHAR(50),@DVDM_NAME NVARCHAR(200)  

IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))  

SET @PROCESS_PARENT='DVCM'  

SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))  

--SET @IS_NEXT=1  

IF(@IS_NEXT =1)  

BEGIN  

SET @DVDM_ID=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')  

SET @ROLE_ID=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')  

SET @NOTES =(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)  

SET @DVDM_NAME= (SELECT DVDM_NAME FROM dbo.CM_DVDM WHERE DVDM_ID=@DVDM_ID)  

SET @NOTES = @NOTES + ' ' + @DVDM_NAME  

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_PYC', -- PROCESS_ID - varchar(10)  

'U', -- STATUS - varchar(5)  

@ROLE_ID, -- ROLE_USER - varchar(50)  

'', -- BRANCH_ID - varchar(15)  

'', -- CHECKER_ID - varchar(15)  

NULL, -- APPROVE_DT - datetime  

@PROCESS_PARENT, -- PARENT_PROCESS_ID - varchar(10)  

'', -- IS_LEAF - varchar(1)  

'', -- COST_ID - varchar(15)  

@DVDM_ID, -- DVDM_ID - varchar(15)  

N'Chờ ' +@NOTES+N' phê duyệt', -- NOTES - nvarchar(500)  

0 -- IS_HAS_CHILD - bit  

)  

SET @PROCESS_PARENT= 'GDK_PYC'  

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  

)  

VALUES  

( @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)  

NULL, -- APPROVE_DT - datetime  

@PROCESS_PARENT, -- PARENT_PROCESS_ID - varchar(10)  

'N', -- IS_LEAF - varchar(1)  

'', -- COST_ID - varchar(15)  

'', -- DVDM_ID - varchar(15)  

N'Hoàn tất', -- NOTES - nvarchar(500)  

NULL -- IS_HAS_CHILD - bit  

)  

DECLARE @PROCESS_NEXT_ID VARCHAR(10)  

SET @PROCESS_NEXT_ID = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)  

UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID  

UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT_ID WHERE REQ_ID=@p_REQ_ID  

END  

ELSE IF(@p_PROCESS_ID='DVCM')  

BEGIN  

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@PROCESS_NEXT VARCHAR(10)  

SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)  

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)  

SET @ROLE_ID=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)  

-- LUCTV 07082020 KIEM TRA NEU ROLE KHAC ROLE GDDV THI PHAI CHUYEN ROLE THANH GDDV  

--IF(@ROLE_ID ='KTT')  

--BEGIN  

-- SET @ROLE_ID ='GDDV'  

--END  

SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)  

UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='A',APPROVE_DT=GETDATE(),CHECKER_ID=@p_MAKER_ID WHERE REQ_ID=@p_REQ_ID AND COST_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN  

dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID)  

UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,  

APPROVE_DT=GETDATE() ,NOTES=N'Đơn vị chuyên môn xác nhận'  

WHERE REQ_ID=@p_REQ_ID AND( ROLE_USER=@ROLE_ID OR ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))AND DVDM_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN  

dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID  

WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID)  

IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_REQ_ID AND STATUS='C'))  

BEGIN  

UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID=@PROCESS_NEXT AND REQ_ID=@p_REQ_ID  

UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID  

END  

END  

--IF(@PROCESS_NEXT='APPROVE')  

IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE PROCESS_ID ='APPROVE' AND REQ_ID =@p_REQ_ID))  

BEGIN  

DECLARE @TempTB TABLE  

(  

TOTAL_AMT DECIMAL(18,2),  

TRADE_ID VARCHAR(20),  

PLAN_ID VARCHAR(20)  

)  

INSERT INTO @TempTB  

SELECT SUM(DT.TOTAL_AMT) TOTAL_AMT,PLDT.TRADE_ID,PLDT.PLAN_ID FROM dbo.TR_REQUEST_DOC_DT DT  

LEFT JOIN dbo.PL_REQUEST_DOC_DT PLDT ON DT.PL_REQDT_ID=PLDT.REQDT_ID  

WHERE TRAN_TYPE_ID IN (SELECT TRAN_TYPE_ID FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK') AND DT.REQ_DOC_ID=@p_REQ_ID  

GROUP BY PLDT.TRADE_ID,PLDT.PLAN_ID  

UPDATE dbo.PL_TRADEDETAIL SET AMT_EXE =AMT_EXE + (SELECT TOTAL_AMT FROM @TempTB WHERE [@TempTB].TRADE_ID=PL_TRADEDETAIL.TRADE_ID AND PL_TRADEDETAIL.PLAN_ID=[@TempTB].PLAN_ID) WHERE PL_TRADEDETAIL.TRADE_ID IN (SELECT TRADE_ID FROM @TempTB)  

IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK')))  

EXEC dbo.TR_REQ_DOC_Ins_To_PO @p_TR_REQ_ID = @p_REQ_ID -- varchar(15)  

END  

END  

IF @@Error <> 0 GOTO ABORT  

COMMIT TRANSACTION  

SELECT 0 as Result, '' ErrorDesc  

RETURN 0  

ABORT:  

BEGIN  

ROLLBACK TRANSACTION  

SELECT -1 as Result, '' ErrorDesc  

RETURN -1  

End