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