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

ALTER PROCEDURE [dbo].[PL_REQUEST_PROCESS_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_DESC NVARCHAR(MAX)

AS

--SET @p_APPROVE_DT= CAST(@p_APPROVE_DT AS DATE)

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

-- LUCTV 06 - 05 - 2021 BO SUNG UY QUYEN KIEM NHIEM

		DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))

		INSERT INTO @TABLE_ROLE SELECT (SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)

		INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) 

		AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL)

		AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL)

-- END

BEGIN TRANSACTION

		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM PL_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' 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

	--UPDATE dbo.PL_REQUEST_TRANSFER SET 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

	@Result VARCHAR(5),

	@PROCESS_CURR VARCHAR(10),

	@STEP_CURR INT,

	@STEP_NEXT INT,

	@PROCESS_NEXT VARCHAR(10),

	@ROLE_USER_NOTIFI VARCHAR(50),

	@DEP_ID VARCHAR(15),

	@IS_LEAF VARCHAR(1),

	@NOTES NVARCHAR(500),

	@IS_NEXT BIT,@ROLE_USER VARCHAR(20),@ROLE_NEXT VARCHAR(20),@LIMTT_MAX  DECIMAL(18,2),@LIMIT_APP DECIMAL(18,2) ,@KHOI_ID_TF VARCHAR(20),@TOTAL_TRANSFER DECIMAL(18,2),@PROCESS_ID_NEXT VARCHAR(20),

	@IS_NEXT_CDT BIT,

	@TOTAL_AMT DECIMAL(18,2),

	@STEP_PARENT VARCHAR(20),

	@NOTES_CDT VARCHAR(20),

	@ROLE_CDT VARCHAR(20),

	@DVDM_CDT VARCHAR(20),

	@LIMIT_VALUE_CDT DECIMAL(18,2),

	@DVDM_ID_TT VARCHAR(20),

	@TOTAL_AMT_GD DECIMAL(18,2),

	@BRANCH_CREATE VARCHAR(15),

	@BRANCH_CREATE_TYPE VARCHAR(15),

	@DEP_CREATE VARCHAR(15),

	@BRANCH_PARENT VARCHAR(15)

	DECLARE @PROCESS_ID VARCHAR(5),@DVDM_NAME NVARCHAR(20),@ROLE_ID VARCHAR(20),@DVDM_ID_CDT VARCHAR(20)

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

	SET @PROCESS_CURR= (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C')

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

	DECLARE @DATA_DVDM TABLE

(

    DVDM_ID VARCHAR(20),

    TOTAL_AMT DECIMAL(12, 0),

	IS_PTGD BIT

);

		SET @NOTES =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   

	DECLARE @DVDM_ID TABLE (

		DVDM_ID VARCHAR(15)

	)

DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)

IF(@PROCESS_CURR LIKE '%_DC')

BEGIN

	SELECT @ERROR=ERROR,

       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_CHECKER_ID,@PROCESS_CURR)

		IF(@ERROR=1)

		BEGIN

	 ROLLBACK TRANSACTION;

    SELECT '-1'  Result,

           @EROOR_DES ErrorDesc

    RETURN '0';

	END

END

SELECT @ERROR=ERROR,

       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDTT','PL_REQUEST_DOC',@p_CHECKER_ID,@PROCESS_CURR)

		IF(@ERROR=1)

		BEGIN

	 ROLLBACK TRANSACTION;

    SELECT '-1'  Result,

           @EROOR_DES ErrorDesc

    RETURN '0';

END

	INSERT INTO @DATA_DVDM

	SELECT KHOI_ID,

		   SUM(TOTAL_AMT) AS TOTAL_AMT,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_PTGD;

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

	INSERT INTO @DVDM_ID

	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN) GROUP BY DVDM_ID

	IF(NOT EXISTS (SELECT REQ_ID FROM dbo.PL_REQUEST_PROCESS WHERE STATUS='C' AND  PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID AND (ROLE_USER=@p_ROLE_LOGIN OR ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_LOGIN) )AND ( DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR DVDM_ID ='' OR DVDM_ID IS NULL)))

	BEGIN

		SELECT '1' as Result , '' AS  ROLE_NOTIFI, '' ErrorDesc

		RETURN '0'

	END		

	UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='P',NOTES=@NOTES+N' đã phê duyệt',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID AND

	(ROLE_USER=@p_ROLE_LOGIN OR ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_LOGIN) OR ROLE_USER IN ( SELECT ISNULL(ROLE_AUTH,'') FROM @TABLE_ROLE))

	AND ( DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR DVDM_ID ='' OR DVDM_ID IS NULL)

	IF(@PROCESS_CURR <>'DVCM' AND @PROCESS_CURR <>'DVDM' AND @PROCESS_CURR <>'TC')

	BEGIN

		UPDATE dbo.PL_REQUEST_DOC_DT SET CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE REQ_ID=@p_REQ_ID AND 

		( KHOI_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @p_ROLE_LOGIN='TGD' OR @p_ROLE_LOGIN='HDQT' OR @p_ROLE_LOGIN='GDDV' OR 

		@p_ROLE_LOGIN IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') )

	END

	IF(@PROCESS_CURR LIKE '%_DC')

	BEGIN

		UPDATE dbo.PL_REQUEST_TRANSFER SET CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE REQ_DOC_ID=@p_REQ_ID AND  ( FR_KHOI_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @p_ROLE_LOGIN='TGD' OR @p_ROLE_LOGIN='HDQT' OR @p_ROLE_LOGIN IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV' ) )

	END

	INSERT INTO dbo.PL_PROCESS

			(

				REQ_ID,

				PROCESS_ID,

				CHECKER_ID,

				APPROVE_DT,

				PROCESS_DESC,NOTES

			)

			VALUES

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

				@PROCESS_CURR,        -- PROCESS_ID - varchar(10)

				@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

				GETDATE(), -- APPROVE_DT - datetime

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

			)	

	SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

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

	SET @Result='1'

	IF(NOT EXISTS(SELECT PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@PROCESS_CURR AND [STATUS] <> 'P'))

	BEGIN	

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

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

				SET @IS_LEAF=(SELECT TOP 1 IS_LEAF FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

			IF(@IS_LEAF='Y')

			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

	END

	IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

-- BAY GIỜ KHÔNG CẦN PHÂN CHIA XÁC NHẬN - DUYỆT NỮA. KHI CẤP LÃNH ĐẠO (GĐK, PTGĐ, TGĐ) BẤM VÀO NÚT DUYỆT THÌ THÔNG BÁO DUYỆT THÀNH CÔNG. KHÔNG CẦN QUAN TÂM CẤP CUỐI CÙNG

SELECT '0' as Result , @ROLE_USER AS  ROLE_NOTIFI, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ROLE_NOTIFI , ERROR_MESSAGE() ErrorDesc

		RETURN '-1'

End