CREATE PROCEDURE dbo.TR_CHECK_ROLE_APPROVE
@TYPE VARCHAR(50) = NULL,
@p_REQ_ID	varchar(15) = NULL,
@p_USER_LOGIN varchar(15) = NULL,
@p_MENU_NAME varchar(150) = NULL-- Dùng để tìm permission cho chức năng để xem user có được ủy quyền hay không
AS
BEGIN TRANSACTION
IF(@TYPE <> 'ADV_PAY')
BEGIN
	IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN AND TYPE_JOB IN ('TP','KS')))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '0' as Result, '' ErrorDesc
		RETURN '0'
	END
END
-- KHAI BAO THEM ROLE NHAN UY QUYEN
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))
DECLARE @TABLE_ROLE2 TABLE (ROLE_AUTH VARCHAR(50))
DECLARE @TABLE_DEP TABLE (ROLE_NEW VARCHAR(50), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
DECLARE @ROLE_ID VARCHAR(50)
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
INSERT INTO @TABLE_ROLE SELECT @ROLE_ID
---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
INSERT INTO @TABLE_ROLE SELECT RL.DisplayName  FROM TL_USER TL
INNER JOIN AbpUserRoles UR ON TL.ID = UR.UserId
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
WHERE TL.TLNANME =@p_USER_LOGIN
--- END LUCTV
DECLARE @MENU_PERMISSION NVARCHAR(500) = (SELECT TOP 1 MENU_PERMISSION FROM TL_MENU WHERE MENU_NAME_EL = @p_MENU_NAME)-- menu permission hieuhm 06092024 check ủy quyền

INSERT INTO @TABLE_ROLE2 SELECT @ROLE_ID

INSERT INTO @TABLE_ROLE2 
SELECT RoleDisplayName 
FROM SYS_PERMISSIONS_PAGE_FOR_USER PER
WHERE TLNAME = @p_USER_LOGIN 
AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = PER.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSION)

---- LUCTV 16062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
INSERT INTO @TABLE_ROLE2 SELECT RL.DisplayName  FROM TL_USER TL
INNER JOIN AbpUserRoles UR ON TL.ID = UR.UserId
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
WHERE TL.TLNANME = @p_USER_LOGIN
--- END LUCTV
INSERT INTO @TABLE_DEP	
SELECT RoleDisplayName, BRANCH_ID, DEP_ID 
FROM SYS_PERMISSIONS_PAGE_FOR_USER PER
WHERE TLNAME = @p_USER_LOGIN 
AND (DATEDIFF(DAY, CONVERT(DATE, EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR EffectiveDate IS NULL OR EffectiveDate = '')
AND (DATEDIFF(DAY, CONVERT(DATE, ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR ExpirationDate IS NULL OR ExpirationDate = '')
AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = PER.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSION)

	DECLARE @AUTHOR TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		BRANCH_TYPE VARCHAR(20)
	)
	DECLARE @AUTHOR_DVDM TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DVDM_ID VARCHAR(20)
	)
	DECLARE @AUTHOR_DMMS TABLE
	(
		ROLE_ID VARCHAR(20),
		BRANCH_ID VARCHAR(20),
		DEP_ID VARCHAR(20),
		DMMS_ID VARCHAR(20)
	)

	INSERT INTO @AUTHOR (ROLE_ID, BRANCH_ID, DEP_ID, BRANCH_TYPE)
	SELECT ROLE_DISPLAYNAME, BRANCH_ID, DEP_ID, BRANCH_TYPE FROM [dbo].[FN_GET_ROLE_USER_BY_TLNAME](@p_USER_LOGIN, @MENU_PERMISSION)

	INSERT INTO @AUTHOR_DVDM (ROLE_ID, BRANCH_ID, DEP_ID, DVDM_ID)
	SELECT ROLE_DISPLAYNAME, BRANCH_ID, DEP_ID, DVDM_ID FROM [dbo].[FN_GET_ROLE_DVDM_USER_BY_TLNAME](@p_USER_LOGIN, @MENU_PERMISSION)

	INSERT INTO @AUTHOR_DMMS (ROLE_ID, BRANCH_ID, DEP_ID, DMMS_ID)
	SELECT ROLE_DISPLAYNAME, BRANCH_ID, DEP_ID, DMMS_ID FROM [dbo].[FN_GET_ROLE_DMMS_USER_BY_TLNAME](@p_USER_LOGIN, @MENU_PERMISSION)

	--- LUCTV 22-03-2023: NEU DANG CHO DVCM PYCMS THI CO THE DUYET NGAY
	IF(@TYPE='PYCMS-DMMS')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='DVCM')
				AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='DVCM' AND STATUS ='C'))
		BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, 'DONE ENABLE APPROVE PYCMS DVCM' ErrorDesc
				RETURN '0'
		END	
	END
	--- END LUCTV
	IF(@TYPE='ADV_PAY')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_ID))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Phiếu đã duyệt xong. Vui lòng chờ các giao dịch tiếp theo để thực hiện phê duyệt' ErrorDesc
			RETURN '-1'
		END
		-- NEU LA TPHC O HOI SO 
		IF(	EXISTS(SELECT * FROM TL_USER WHERE TLNANME = @p_USER_LOGIN AND TLSUBBRID = 'DV0001' AND DEP_ID = 'DEP000000000014' AND RoleName = 'GDDV'))
			--OR EXISTS(SELECT * FROM @TABLE_DEP WHERE ROLE_NEW = 'GDDV' AND BRANCH_ID = 'DV0001' AND DEP_ID = 'DEP000000000014') 
		BEGIN
			IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND PROCESS <> '' AND PROCESS IS NOT NULL))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
				RETURN '-1'
			END
		END
		--doanptt: THU KY DA DIEU PHOI CHO NHAN VIEN XU LY THI KHONG DUOC DUYET NUA
		ELSE IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB <> 'C' AND TLNAME =@p_USER_LOGIN AND TYPE_JOB IN ('KS')))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Phiếu đang được điều phối đến nhân viên xử lý' ErrorDesc
			RETURN '-1'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS,'') = ''))
		BEGIN
			PRINT '1'
		END
		ELSE IF	(	(	EXISTS(SELECT * FROM TL_USER WHERE TLNANME = @p_USER_LOGIN AND TLSUBBRID = 'DV0001' AND RoleName = 'GDDV')
						OR EXISTS(SELECT * FROM @TABLE_DEP WHERE ROLE_NEW = 'GDDV' AND BRANCH_ID = 'DV0001') 
					)
					OR EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS,'') = '')
				)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
		-- CAC TRUONG HOP CON LAI
		ELSE
		BEGIN
			IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID) )
			BEGIN
				IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND ROLE_USER IN (SELECT * FROM @TABLE_ROLE)))
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
					RETURN '-1'
				END
			END
		END
		
		--IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND BRANCH_CREATE ='DV0001')
		--BEGIN
		--	IF(EXISTS(SELECT * FROM PL_PROCESS WHERE CHEC))
		--END
		--ELSE
		--BEGIN
		--	ROLLBACK TRANSACTION
		--	SELECT '-1' as Result, '' ErrorDesc
		--	RETURN '-1'
		--END
	END
	ELSE IF(@TYPE='TR_REQ_PAYMENT')
	BEGIN
		IF((SELECT COUNT(*) FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '' AND AUTH_STATUS <> 'A') > 0)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, N'' ErrorDesc
			RETURN '0'
		END
		IF((SELECT COUNT(*) FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '0' AND AUTH_STATUS <> 'A') > 0)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'' ErrorDesc
			RETURN '-1'
		END
		IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_ID))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Phiếu đã duyệt xong. Vui lòng chờ các giao dịch tiếp theo để thực hiện phê duyệt' ErrorDesc
			RETURN '-1'
		END
		IF	(	(	SELECT COUNT(*) 
					FROM @TABLE_ROLE2 A 
					WHERE A.ROLE_AUTH IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
				) = 0
			) 
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
			RETURN '-1'
		END
	END
	ELSE IF(@TYPE='TR_REQ_PAYMENT_AUTO')
	BEGIN
	-- BEGIN CAP DUYET TRUNG GIAN
		IF((SELECT COUNT(*) FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '0' AND AUTH_STATUS <> 'A') > 0)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, N'Cấp phê duyệt trung gian xác nhận phiếu' ErrorDesc
			RETURN '0'
		END
		ELSE IF((SELECT COUNT(*) FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '1' AND AUTH_STATUS <> 'A') > 0)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Cấp phê duyệt trung đã xác nhận phiếu trước đó' ErrorDesc
			RETURN '-1'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '6' AND ISNULL(REQUEST_STATUS, '') = 'SUSPEND'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, N'Cấp phê duyệt trung gian xác nhận tạm dừng phiếu' ErrorDesc
			RETURN '0'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '7' AND ISNULL(REQUEST_STATUS, '') = 'SUSPEND'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Cấp phê duyệt trung gian đã xác nhận tạm dừng phiếu trước đó' ErrorDesc
			RETURN '-1'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '9' AND ISNULL(REQUEST_STATUS, '') = 'UNSUSPEND'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, N'Cấp phê duyệt trung gian xác nhận khôi phục phiếu' ErrorDesc
			RETURN '0'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '10' AND ISNULL(REQUEST_STATUS, '') = 'UNSUSPEND'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Cấp phê duyệt trung gian đã xác nhận tạm khôi phục trước đó' ErrorDesc
			RETURN '-1'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '15' AND ISNULL(REQUEST_STATUS, '') = 'DEL'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, N'Cấp phê duyệt trung gian xác nhận hủy phiếu' ErrorDesc
			RETURN '0'
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '16' AND ISNULL(REQUEST_STATUS, '') = 'DEL'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Cấp phê duyệt trung gian đã xác nhận hủy phiếu trước đó' ErrorDesc
			RETURN '-1'
		END
	-- END CAP DUYET TRUNG GIAN
		IF	(	(	SELECT COUNT(*) 
					FROM @TABLE_ROLE2 A 
					WHERE A.ROLE_AUTH IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
				) = 0
			) 
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
			RETURN '-1'
		END
		ELSE
		BEGIN
			IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS ='U' AND ISNULL(PROCESS, '') IN ('0', '1') AND REQ_PAY_AUTO_ID =@p_REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, N'TĐV duyệt phiếu, luồng tạo phiếu có hiệu lực' ErrorDesc
				RETURN '0'
			END
			ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS ='A' AND ISNULL(PROCESS, '') IN ('6', '7') AND REQ_PAY_AUTO_ID =@p_REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, N'TĐV duyệt phiếu, luồng tạm dừng phiếu' ErrorDesc
				RETURN '0'
			END
			ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS ='N' AND ISNULL(PROCESS, '') IN ('9', '10') AND REQ_PAY_AUTO_ID =@p_REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, N'TĐV duyệt phiếu, luồng khôi phục phiếu tạm dừng' ErrorDesc
				RETURN '0'
			END
			ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS ='A' AND ISNULL(PROCESS, '') IN ('15', '16') AND REQ_PAY_AUTO_ID =@p_REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, N'TĐV hủy phiếu, luồng hủy phiếu tạm dừng' ErrorDesc
				RETURN '0'
			END
			ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS ='N' AND ISNULL(PROCESS, '') IN ('15', '16') AND REQ_PAY_AUTO_ID =@p_REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, N'TĐV hủy phiếu, luồng hủy phiếu tạm dừng' ErrorDesc
				RETURN '0'
			END
			ELSE
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, N'Phiếu đã hủy xong. Vui lòng chờ các giao dịch tiếp theo để thực hiện phê duyệt' ErrorDesc
				RETURN '-1'
			END
		END
	END
	ELSE IF(@TYPE='TR_REQ_PAYMENT_AUTO_KT')
	BEGIN
		IF	(	(	SELECT COUNT(*) 
					FROM @TABLE_ROLE2 A 
					WHERE A.ROLE_AUTH IN ('KSV')
				) = 0
			) 
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
			RETURN '-1'
		END
		IF(NOT EXISTS(SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID = @p_REQ_ID AND PROCESS IN ('2','4','11','13','13.1','17','19','19.1')))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, N'Chưa đến bước duyệt' ErrorDesc
			RETURN '-1'
		END
	END
	ELSE IF (@TYPE ='CORE')
	BEGIN
		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT ='A') OR  EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT ='A'))
		BEGIN
			IF(EXISTS(SELECT * FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REQ_ID AND ISNULL(REF_NO,'') <> ''))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '6' as Result, N'Giao dịch được phê duyệt thành công trên AMS và hạch toán thành công vào hệ thống Core FC' ErrorDesc
				RETURN '6'
			END
			ELSE
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '5' as Result, N'Giao dịch được phê duyệt thành công nhưng bắn vào Core FC thất bại. Vui lòng rà soát lại nội dung hạch toán (không được phép chứa kí tự & hoặc kí tự <)' ErrorDesc
				RETURN '5'
			END
		END
		ELSE
		BEGIN
			IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT <> 'A') AND  EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT <>'A'))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '7' as Result, '' ErrorDesc
				RETURN '7'
			END
		END
	END
	ELSE IF(@TYPE ='CON_LAYOUT')
	BEGIN
		-- NEU CAP DUYET LA VAN PHONG THU KI TGD THI CO THE DUYET LUON
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKTGD' AND
		(@ROLE_ID ='TKTGD' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKTGD'))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
		-- NEU CAP DUYET LA VAN PHONG THU KI HDQT THI CO THE DUYET LUON
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKHDQT' AND (@ROLE_ID ='TKHDQT' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKHDQT'))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, 'NOT DONE' ErrorDesc
			RETURN '0'
		END
		IF(EXISTS(SELECT * FROM CON_LAYOUT_BLUEPRINT WHERE CON_LAYOUT_BLUEPRINT_ID = @p_REQ_ID AND PROCESS_ID ='APPROVE'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, 'NOT DONE' ErrorDesc
			RETURN '-1'
		END
		IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS PR WHERE PR.REQ_ID =@p_REQ_ID AND STATUS ='C' AND (
									EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND AUTH.BRANCH_ID=PR.BRANCH_ID AND (AUTH.DEP_ID=PR.DEP_ID OR AUTH.BRANCH_TYPE<>'HS'))
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND PR.DVDM_ID=AUTH.DVDM_ID)
									OR ( (PR.BRANCH_ID IS NULL OR PR.BRANCH_ID='') AND (PR.DVDM_ID ='' OR PR.DVDM_ID  IS NULL) )
									)))
		BEGIN
			print 'haha'
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
		END
	END
	ELSE
	BEGIN
		--- LUCTV 22.03.23 DVCM CO THE DUYET NGAY LUC DIEU PHOI
		IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='DVCM')
				AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='DVCM' AND STATUS ='C'))
		BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, 'DONE' ErrorDesc
				RETURN '0'
		END	
		--- END LUCTV
		-- NEU CAP DUYET LA VAN PHONG THU KI TGD THI CO THE DUYET LUON
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKTGD' AND
		(@ROLE_ID ='TKTGD' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKTGD'))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
		-- NEU CAP DUYET LA VAN PHONG THU KI HDQT THI CO THE DUYET LUON
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKHDQT' AND
    (@ROLE_ID ='TKHDQT' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKHDQT'))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
    -- NEU CAP DUYET LA DVCM THI CO THE DUYET LUON
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='DVCM' AND
    (@ROLE_ID ='GDDV' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='GDDV'))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '0' as Result, '' ErrorDesc
			RETURN '0'
		END
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='DVDC' AND
		(@ROLE_ID ='GDDV' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='GDDV'))))
		BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, '' ErrorDesc
				RETURN '0'
		END
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '0' as Result, '' ErrorDesc
				RETURN '0'
		END	
		IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS PR WHERE PR.REQ_ID =@p_REQ_ID AND STATUS ='C' AND (
									EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND AUTH.BRANCH_ID=PR.BRANCH_ID AND (AUTH.DEP_ID=PR.DEP_ID OR AUTH.BRANCH_TYPE<>'HS'))
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND PR.DVDM_ID=AUTH.DVDM_ID)
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND EXISTS (SELECT * FROM dbo.TR_REQUEST_DOC A  WHERE A.REQ_ID=@p_REQ_ID AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS'))
									OR EXISTS(SELECT  AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND EXISTS (SELECT * FROM dbo.TR_REQUEST_DOC A  WHERE A.REQ_ID=@p_REQ_ID AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS'))
									OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PR.ID AND  TLNAME=@p_USER_LOGIN AND TYPE_JOB='KS' AND STATUS_JOB='C')
									OR ((PR.BRANCH_ID IS NULL OR PR.BRANCH_ID='') AND (PR.DVDM_ID ='' OR PR.DVDM_ID  IS NULL) AND EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID = PR.ROLE_USER))
									)))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, 'NOT DONE ENABLE APPROVE' ErrorDesc
			RETURN '-1'
		END
		IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='APPROVE'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, 'NOT DONE' ErrorDesc
			RETURN '-1'
		END
		IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='APPROVE'))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, 'NOT DONE' ErrorDesc
			RETURN '-1'
		END
		-- GiaNT
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='P' AND PROCESS_ID ='KT' AND
		(@ROLE_ID ='KSV' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='KSV'))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, 'NOT DONE' ErrorDesc
			RETURN '-1'
		END
	END
	COMMIT TRANSACTION
	print 'haha'
	SELECT '0' as Result, '' ErrorDesc
	RETURN '0'
	ABORT:
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' ErrorDesc
			RETURN '-1'
	End
--08062025_secretkey