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

ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_App]  

@p_REQ_ID VARCHAR(15) = NULL,  

@p_AUTH_STATUS VARCHAR(1) = NULL,  

@p_CHECKER_ID varchar(15) = NULL,  

@p_APPROVE_DT DATETIME = NULL,  

@p_ROLE_LOGIN VARCHAR(50) = NULL,  

@p_BRANCH_LOGIN VARCHAR(15),  

@p_PROCESS_DES NVARCHAR(500)  

AS  

--Validation is here  

DECLARE @ERRORSYS NVARCHAR(15) = ''  

IF ( NOT EXISTS ( SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))  

SET @ERRORSYS = 'REQ-00002'  

IF @ERRORSYS <> ''  

BEGIN  

SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS  

RETURN '0'  

END  

BEGIN TRANSACTION  

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

IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_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  

--- PHE DUYET TRUNG GIAN  

DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE_N VARCHAR(20) ,@DEP_CREATE_N VARCHAR(20),@BRANCH_TYPE VARCHAR(10),  

@BRANCH_CREATE_TYPE VARCHAR(10)  

SELECT @BRANCH_CREATE_N=BRANCH_CREATE,@DEP_CREATE_N=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID  

SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)  

SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N)  

IF(EXISTS(SELECT * FROM TR_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,  

APPROVE_DT,  

PROCESS_DESC,NOTES  

)  

VALUES  

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

'SIGN', -- PROCESS_ID - varchar(10)  

@p_CHECKER_ID, -- CHECKER_ID - varchar(15)  

@p_APPROVE_DT , -- APPROVE_DT - datetime  

N'Cấp phê duyệt trung gian xác nhận phiếu yêu cầu mua sắm',  

N'Cấp phê duyệt trung gian'  

)  

--- DUA CAP PHE DUYET TRUONG DON VI  

IF(@BRANCH_CREATE_TYPE='PGD')  

SET @BRANCH_CREATE_N=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N)  

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,  

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_N,  

@DEP_CREATE_N, -- 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 TR_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID  

END  

ELSE  

--- PHE DUYET GIU NGUYEN NHU THUONG  

BEGIN  

--- KIEM TRA XEM DA PHE DUYET TRUNG GIAN HAY CHUA  

IF(EXISTS(SELECT * FROM TR_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'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 đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác  

	nhận phiếu!' ErrorDesc  

	RETURN -1  

	END  

	IF(@p_CHECKER_ID = (SELECT TOP 1 SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))

	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 đợ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  

DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  

DECLARE @CURR_PROCESS VARCHAR(20),@TOTAL_AMT DECIMAL(18,2),  

@LIMIT_VALUE DECIMAL(18,0), @LIMIT_DVCM DECIMAL(18,0),@IS_NEXT BIT,@DMMS_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@BRANCH_HS VARCHAR(20),@DEP_CREATE VARCHAR(15)  

SET @BRANCH_HS= (SELECT TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_TYPE='HS')  

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

SET @LIMIT_DVCM=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PYCMS_DVCM')  

SET @TOTAL_AMT=(SELECT TOTAL_AMT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)  

SET @CURR_PROCESS = (SELECT PROCESS_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)  

UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='A',REQ_TYPE=0, CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CAST(@p_APPROVE_DT AS DATE),BRANCH_DVMS=BRANCH_CREATE, PROCESS_ID='DMMS' WHERE REQ_ID=@p_REQ_ID  

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

SET @BRANCH_CREATE=(SELECT BRANCH_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)  

SET @DEP_CREATE=(SELECT DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)  

IF(@BRANCH_CREATE_TYPE='PGD')  

SET @BRANCH_CREATE=(SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)  

IF(EXISTS (SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS'))  

BEGIN  

IF(@TOTAL_AMT <= @LIMIT_DVCM)  

BEGIN  

SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT  

LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)  

UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID  

END  

ELSE  

BEGIN  

SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)  

UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID  

END  

END  

ELSE  

BEGIN  

IF(EXISTS( SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD  

LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID  

LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID  

WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID))  

BEGIN  

IF(@TOTAL_AMT <= @LIMIT_DVCM)  

BEGIN  

SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT  

LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)  

UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID  

END  

ELSE  

BEGIN  

SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)  

UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID  

END  

END  

ELSE  

BEGIN  

IF(@TOTAL_AMT<=@LIMIT_VALUE)  

BEGIN  

SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)  

UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID  

END  

ELSE  

BEGIN  

SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)  

UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID  

END  

END  

END  

--IF(EXISTS( SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD  

-- LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID  

-- LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID  

-- WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID) OR @BRANCH_CREATE=@BRANCH_HS)  

-- BEGIN  

-- IF(@TOTAL_AMT<=@LIMIT_VALUE AND NOT EXISTS(SELECT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT  

-- LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID  

-- GROUP BY HC.DVCM_ID HAVING COUNT(REQDT_ID)>1))  

-- BEGIN  

-- SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT  

-- LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)  

-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID  

-- END  

-- ELSE  

-- BEGIN  

-- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)  

-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID  

-- END  

-- END  

-- ELSE  

-- BEGIN  

-- IF(@TOTAL_AMT<=@LIMIT_VALUE)  

-- BEGIN  

-- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)  

-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID  

-- END  

-- ELSE  

-- BEGIN  

-- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)  

-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID  

-- 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,  

COST_ID,  

DVDM_ID,  

NOTES,  

IS_HAS_CHILD,  

DEP_ID  

)  

VALUES  

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

'APPNEW', -- PROCESS_ID - varchar(10)  

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

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

@BRANCH_CREATE, -- BRANCH_ID - varchar(15)  

@p_CHECKER_ID, -- CHECKER_ID - varchar(15)  

GETDATE(), -- APPROVE_DT - datetime  

'', -- PARENT_PROCESS_ID - varchar(10)  

'', -- IS_LEAF - varchar(1)  

'', -- COST_ID - varchar(15)  

'', -- DVDM_ID - varchar(15)  

N'Trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500)  

0 , -- IS_HAS_CHILD - bit  

@DEP_CREATE  

)  

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)  

'DMMS', -- PROCESS_ID - varchar(10)  

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

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

'', -- BRANCH_ID - varchar(15)  

'', -- CHECKER_ID - varchar(15)  

GETDATE(), -- APPROVE_DT - datetime  

'APPNEW', -- PARENT_PROCESS_ID - varchar(10)  

'', -- IS_LEAF - varchar(1)  

'', -- COST_ID - varchar(15)  

'', -- DVDM_ID - varchar(15)  

N'Chờ đầu mối mua sắm xử lý', -- NOTES - nvarchar(500)  

1 -- IS_HAS_CHILD - bit  

)  

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)  

@p_APPROVE_DT , -- APPROVE_DT - datetime  

@p_PROCESS_DES,  

N'Trưởng đơn vị phê duyệt' -- PROCESS_DESC - nvarchar(1000)  

)  

END  

IF @@Error <> 0 GOTO ABORT  

COMMIT TRANSACTION  

IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))  

BEGIN  

SELECT '0' as Result, '' ErrorDesc  

RETURN '0'  

END  

ELSE  

BEGIN  

SELECT '4' as 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' đã được đợi 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  

RETURN '4'  

END  

ABORT:  

BEGIN  

PRINT 'ERROR'  

ROLLBACK TRANSACTION  

SELECT '-1' as Result, '' ErrorDesc  

RETURN '-1'  

End