ALTER   PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Appr]
--Luanlt 2019/17/10 - Sửa params
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_AUTH_STATUS varchar(15) = NULL,
@p_COST_ID VARCHAR(15) = NULL,
@p_IS_AUTHORITY varchar(1) = NULL,
@p_PROCESS_NOTES nvarchar(max) = NULL
AS
-- BEGIN VALIDATE
	IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE MAKER_ID =@p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, ''  REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị tạm ứng này này! Người phê duyệt phiếu phải khác với người tạo phiếu' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phê duyệt phiếu đề nghị tạm ứng thất bại! Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được phê duyệt trước đó' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được kiểm soát viên phê duyệt trước đó' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về giao dịch viên. Vui lòng kiểm tra lại thông tin' ErrorDesc
		RETURN '-1'
	END
	IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='S' AND REQ_PAY_ID =@p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được đề xuất từ chối. Vui lòng kiểm tra lại thông tin' ErrorDesc
		RETURN '-1'
	END
-- CHAN CUOI
	IF (EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS <> 'U' AND REQ_PAY_ID =@p_REQ_PAY_ID))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' chưa được gửi phê duyệt. Vui lòng đợi người tạo gửi phê duyệt trước khi phê duyệt phiếu' ErrorDesc
		RETURN '-1'
	END
-- END VALIDATE

BEGIN TRANSACTION
		IF(@p_IS_AUTHORITY <> 'Y')
		BEGIN
			SET @p_IS_AUTHORITY = 'N'
		END

		-- DOANPTT 261122: SET DVDM NEU DVDM KHONG DUOC TRUYEN TU GIAO DIEN
		IF(ISNULL(@p_COST_ID, '') = '')
		BEGIN
			SET @p_COST_ID = (SELECT DVDM_ID FROM TR_REQ_ADVANCE_PAYMENT where REQ_PAY_ID = @p_REQ_PAY_ID)
		END
		-- KHAI BAO THEM ROLE NHAN UY QUYEN
		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)
		AND TLNAME =@p_CHECKER_ID
	
		-- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM
		DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))
		INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@p_CHECKER_ID 
		--AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
		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)
				DECLARE @BRANCH_CREATE VARCHAR(15), @p_DEP_ID VARCHAR(15) 
		SET @BRANCH_CREATE =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
		DECLARE @DEP_CODE_NEXT VARCHAR(15)
		IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_CREATE)<>'HS')
		BEGIN
			SET @p_DEP_ID =(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE LEFT(DEP_CODE,5) IN (SELECT TOP 1 LEFT(DVDM_CODE,5) FROM CM_DVDM WHERE DVDM_ID =@p_COST_ID))
			SET @DEP_CODE_NEXT = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID)
			--PRINT 'BRANCH_TYPE CN: DEP_CODE_NEXT' +  @DEP_CODE_NEXT
			--PRINT 'BRANCH_TYPE CN: @p_DEP_ID' +  @p_DEP_ID
		END
		ELSE
		BEGIN
			SET @p_DEP_ID =(SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
			SET @DEP_CODE_NEXT = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID)
			--PRINT 'BRANCH_TYPE HS: DEP_CODE_NEXT' +  @DEP_CODE_NEXT
			--PRINT 'BRANCH_TYPE HS: @p_DEP_ID' +  @p_DEP_ID
		END
		
		IF (ISNULL(@p_COST_ID, '') <> '' AND (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_CREATE) = 'HS')
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Xác nhận phiếu đề nghị tạm ứng thất bại! Nhân viên ở hội sở không được phép chọn khối phê duyệt' ErrorDesc
			RETURN '-1'
		END
		
		--- KHAI BAO CHUNG
		   DECLARE @ROLE_ID VARCHAR(200), @BRANCH_TYPE VARCHAR(15), @TOTAL_ADVANCE DECIMAL(18,0), @TOTAL_PAYBACK DECIMAL(18,0), @BRANCH_ID VARCHAR(15),
			@DEP_ID VARCHAR(15), @COSTCENTER_ID VARCHAR(15)= NULL, @BRANCH_RQ VARCHAR(15) = NULL,@DEP_ID_RQ VARCHAR(15), @BRANCH_LOGIN VARCHAR(15),@LIMIT_ONE_OF DECIMAL(18,2)
			DECLARE @LIMIT_AMT DECIMAL(18,0), @REQ_AMT DECIMAL(18,2) =0, @TONG_PGD DECIMAL(18,0), @TONG_PGD_HOAN DECIMAL(18,0)
			SET @REQ_AMT = (SELECT REQ_AMT *ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
			--SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)
			SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)
			--SET  @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
			IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC'))
			BEGIN
				PRINT @ROLE_ID
			END
			ELSE
			BEGIN
				SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID)
				IF(@ROLE_ID IS NULL OR @ROLE_ID ='')
				BEGIN
					SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID))
				END
			END
			SET @BRANCH_ID = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
			SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
			SET @DEP_ID_RQ =(SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
			SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)
			DECLARE @tmp table(BRANCH_ID varchar(15))
			INSERT into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_ID)
			DECLARE @tmp_CN table(BRANCH_ID varchar(15))
			IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='CN')
			BEGIN
				--INSERT into @tmp_CN  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_RQ)
				INSERT into @tmp_CN  VALUES (@BRANCH_RQ)
			END
			ELSE IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='PGD')
			BEGIN
				--DECLARE @FATHER_ID VARCHAR(15) = NULL
				--SET @FATHER_ID =(SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)
				--INSERT into @tmp_CN  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@FATHER_ID)
				INSERT into @tmp_CN  VALUES (@BRANCH_RQ)
			END
			--DECLARE @DEP_ID_LG VARCHAR(15) = NULL	
			--SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)
			DECLARE @TMP_DVDM TABLE(DVDM_ID VARCHAR(15))
			INSERT INTO @TMP_DVDM
			SELECT A.DVDM_ID--, A.COST_CODE, A.COST_NAME, B.DEP_ID --, C.DEP_CODE, C.DEP_NAME
			FROM PL_COSTCENTER A
			LEFT JOIN PL_COSTCENTER_DT B ON A.COST_ID = B.COST_ID
			WHERE B.DEP_ID = @DEP_ID_RQ
			GROUP BY A.DVDM_ID
			-- KHAI BAO BRANCH CUA USER DUYET
			SET @BRANCH_LOGIN = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)
			DECLARE @LIMIT_REMAIN DECIMAL(18,0)
		--CAP NHAT CODE TRONG QUA TRINH TEST UAT
		INSERT INTO @TABLE_ROLE SELECT @ROLE_ID
		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE='I'))
		BEGIN
			IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_CREATE) ='HS')
			BEGIN
				IF(EXISTS(SELECT * FROM @TABLE_ROLE WHERE ISNULL(ROLE_AUTH,'') IN ('GDDV','TP') AND (SELECT ISNULL(PROCESS,'') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) =''))
				BEGIN
					-- doanptt them note khi xac nhan
					IF(@p_PROCESS_NOTES IS NULL OR @p_PROCESS_NOTES = '')
					BEGIN
						SET @p_PROCESS_NOTES = N'Trưởng đơn vị xác nhận phiếu';
					END

					UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS='0', NOTES ='' WHERE REQ_PAY_ID =@p_REQ_PAY_ID
					INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,'0',@p_CHECKER_ID, GETDATE(), @p_PROCESS_NOTES,N'Xác nhận phiếu tạm ứng')
					COMMIT TRANSACTION
					SELECT '1' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'' ErrorDesc
					RETURN '1'
				END
			END
			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND REQ_ID = @p_REQ_PAY_ID))
			BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, ''  REQ_PAY_ID, N'Bạn không có quyền phê duyệt phiếu tạm ứng nội bộ. Vui lòng chọn giao dịch khác để duyệt' ErrorDesc
					RETURN '-1'
			END
			IF NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND STATUS = 'C')
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được bạn phê duyệt xong trước đó! Vui lòng đợi cấp tiếp theo phê duyệt tạm ứng' ErrorDesc
				RETURN '-1'
			END
			
			--IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID AND BRANCH_ID= @BRANCH_ID AND STATUS ='P'))
			--BEGIN
			--		ROLLBACK TRANSACTION
			--		SELECT '-1' as Result, ''  REQ_PAY_ID, N'Bạn đã thực hiện xác nhận phiếu tạm ứng trước đó' ErrorDesc
			--		RETURN '-1'
			--END
			-- KIEM TRA AUTH_STATUS TRUYEN XUONG LA GI, A: DUYET, C: CONFIRM  
-- KIEM TRA HAN MUC CON LAI CUA USER VA THONG BAO CHO NGUOI DUYET
			IF(@p_AUTH_STATUS='U') -- KIEM TRA HAN MUC CON LAI CUA USER VA THONG BAO CHO NGUOI DUYET
			BEGIN
			-- KIEM TRA NEU ROLE CHUA CO HAN MUC PHE DUYET THI PHAI THONG BAO
				IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE ROLE_ID =@ROLE_ID AND LIMIT_TYPE ='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN))
				BEGIN
						ROLLBACK TRANSACTION
						SELECT '-1' as Result, ''  REQ_PAY_ID, N'Bạn chưa được cấu hình hạn mức phê duyệt tạm ứng nội bộ. Vui lòng thông báo Admin cấu hình hạn mức phê duyệt' ErrorDesc
						RETURN '-1'
				END
			-- BAT DAU DUYET THEO NGAN SACH			
			--- LAY HAN MUC CUA USER
				SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)

				--print @LIMIT_AMT
				SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
				IF(@ROLE_ID ='GDK' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
				BEGIN
					SET @LIMIT_AMT =6000000000 -- TANG LEN 6 TY
					SET @LIMIT_ONE_OF =1000000000
				
				END
				IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
				BEGIN
						SET @LIMIT_AMT =4000000000 --- TANG LEN 4 TY
						SET @LIMIT_ONE_OF =500000000
						IF(EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD ='PTGD' AND ROLE_NEW ='TGD' 
						AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='')))
						BEGIN
							SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID='TGD' AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
							SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID='TGD' AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
							IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER ='TGD' AND REQ_ID =@p_REQ_PAY_ID))
							BEGIN
								UPDATE PL_REQUEST_PROCESS SET STATUS ='P' WHERE ROLE_USER ='PTGD' AND REQ_ID =@p_REQ_PAY_ID
								UPDATE PL_REQUEST_PROCESS SET STATUS ='C',ROLE_USER ='PTGD' WHERE ROLE_USER ='TGD' AND REQ_ID =@p_REQ_PAY_ID
								SET @ROLE_ID ='TGD'
							END
						END
				
				END
			-- NEU LA HOI SO THI LAY TONG SO TIEN TAM UNG CUA CAC PHONG BAN CHUNG VOI KHOI CUA PHIEU DANG DUOC DUYET
				SET @TONG_PGD =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID = @BRANCH_RQ
					 AND REQ_TYPE ='I' AND AUTH_STATUS ='A') + @REQ_AMT
				SET @TONG_PGD_HOAN =(
						--SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT))
						--FROM TR_REQ_PAYMENT_DT B
						--INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID
						--INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID
						SELECT SUM (ISNULL(A.PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT A
						WHERE A.BRANCH_ID = @BRANCH_RQ
						AND A.AUTH_STATUS_KT ='A' AND A.REQ_TYPE='I') 
				IF(@BRANCH_TYPE='HS')
				BEGIN
					SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE
					 DEP_ID IN
					 (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
						LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
						WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ)
					 AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0)
					SET @TOTAL_PAYBACK =
					ISNULL(
					(
						SELECT (SUM(ISNULL(C.PAY_AMT,0)))
						FROM TR_REQ_ADVANCE_PAYMENT C
						WHERE C.DEP_ID IN 
						(SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
						LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
						WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ)
						AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
					),0)
				END
				--- BO SUNG NEU RIENG PHONG THUONG HIEU VA TRUYEN THONG --- 2021824
				IF(@BRANCH_TYPE='HS' AND @DEP_CODE_NEXT ='0690405' )
				BEGIN
					SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE
					 DEP_ID IN
					 (SELECT DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE ='0690405' AND BRANCH_ID =@BRANCH_RQ)
					 AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0)
					SET @TOTAL_PAYBACK =
					ISNULL(
					(
						SELECT (SUM(ISNULL(C.PAY_AMT,0)))
						FROM TR_REQ_ADVANCE_PAYMENT C
						WHERE C.DEP_ID IN 
						(SELECT DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE ='0690405' AND BRANCH_ID =@BRANCH_RQ)
						AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
					),0)
				PRINT @TOTAL_ADVANCE
				PRINT @TOTAL_PAYBACK
				END
				--- END
				-- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC
				ELSE IF(@BRANCH_TYPE <>'HS')
				BEGIN
					SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN)
					 AND REQ_TYPE ='I' AND AUTH_STATUS='A')
					SET @TOTAL_PAYBACK =
					(
						SELECT (SUM(ISNULL(B.PAY_AMT,0)))
						FROM TR_REQ_ADVANCE_PAYMENT B
						WHERE B.BRANCH_ID IN 
						(SELECT BRANCH_ID FROM @tmp_CN)
						AND B.AUTH_STATUS_KT ='A' AND B.REQ_TYPE='I'
					)
				END
				
				SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0)
				PRINT @LIMIT_REMAIN
				-- KIEM TRA NEU LA CHU TICH HDQT THI PHE DUYET LUON, KHONG CAN XET HAN MUC GI
				IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName ='HDQT'))
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
					--N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
					--+ CHAR(10) +
					--N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
					--+ CHAR(10) + CHAR(13) +
					N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
					+ CHAR(10) + 
					N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
					RETURN '-4'
				END
				IF(@LIMIT_AMT <(SELECT REQ_AMT * ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) + @LIMIT_REMAIN)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-2' as Result, @p_REQ_PAY_ID  REQ_PAY_ID,
					N'Tổng hạn mức phê duyệt là: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10)+
					N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
					+ CHAR(10) + CHAR(13)+
					N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN') 
					+ CHAR(10) +
					N'Số tiền tạm ứng đã vượt mức phê duyệt là: '+ FORMAT((@LIMIT_REMAIN +@REQ_AMT) -@LIMIT_AMT,'#,#', 'vi-VN') 
					+ CHAR(10) +
					N'Bạn muốn chuyển giao dịch lên cấp cao hơn để duyệt hay tiếp tục chờ duyệt' ErrorDesc
					RETURN '-2'
				END
				--- NEU DU HAN MUC THI THONG BAO DE NGUOI DUYET CAN NHAC CO NEN DUYET PHIEU HAY KHONG
				IF(@LIMIT_AMT >=(SELECT REQ_AMT * ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) +@LIMIT_REMAIN)
				BEGIN
					IF(@REQ_AMT >@LIMIT_ONE_OF)
					BEGIN
						ROLLBACK TRANSACTION
						SELECT '-2' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
						N'Tổng hạn mức phê duyệt là: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') 
						+ CHAR(10)+
						N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
						+ CHAR(10) + CHAR(13) +
						N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN') 
						+ CHAR(10) + 
						N'Số tiền tạm ứng đã vượt mức so với số tiền của một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
						+ CHAR(10) + 
						N'Bạn muốn chuyển giao dịch lên cấp cao hơn để duyệt hay tiếp tục chờ duyệt' ErrorDesc
						RETURN '-2'
					END
					ELSE
					BEGIN
						-- KIEM TRA NEU LA PGD THI CANH BAO
						IF(@BRANCH_TYPE ='HS')
						BEGIN
							IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName NOT IN('TGD','HQDT')))
							BEGIN
								ROLLBACK TRANSACTION
								SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
								--N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
								--+ CHAR(10) + CHAR(13) +  
								N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
								+ CHAR(10) + 
								N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
								RETURN '-4'
							END
							ELSE
							BEGIN
								ROLLBACK TRANSACTION
								SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
								--N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
								--+ CHAR(10) + CHAR(13) +  
								--N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
								--+ CHAR(10) + 
								N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
								RETURN '-4'
							END
						END
						ELSE IF(@BRANCH_TYPE ='CN')
						BEGIN
							---LUCTV 24.11.2022 NẾU PHIẾU TỪ CN GỬI LÊN THÌ CHỈ CẦN XÉT SỐ TIỀN DUYỆT 1 LẦN CÓ ĐỦ HẠN MỨC KHÔNG
							IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName  IN('GDK','PTGD','TGD')))
							BEGIN
								IF(@REQ_AMT >@LIMIT_ONE_OF)
								BEGIN
									ROLLBACK TRANSACTION
									SELECT '-2' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
									N'Tổng hạn mức phê duyệt là: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') 
									+ CHAR(10)+
									N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
									+ CHAR(10) + CHAR(13) +
									N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN') 
									+ CHAR(10) + 
									N'Số tiền tạm ứng đã vượt mức so với số tiền của một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
									+ CHAR(10) + 
									N'Bạn muốn chuyển giao dịch lên cấp cao hơn để duyệt hay tiếp tục chờ duyệt' ErrorDesc
									RETURN '-2'
								END
								ELSE
								BEGIN
									ROLLBACK TRANSACTION
									SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
									--N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
									--+ CHAR(10) +
									N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
									+ CHAR(10) + CHAR(13) +
									N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
									+ CHAR(10) + 
									N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
									RETURN '-4'
								END
							END--- END LUCTV
							ELSE IF(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0) > 40000000)
							BEGIN
								ROLLBACK TRANSACTION
								SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
								N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
								+ CHAR(10) + CHAR(13) +
								N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
								+ CHAR(10) +
								N'Số tiền tạm ứng đang vượt quá số tổng hạn mức tạm ứng của 1 CN: '+ FORMAT(40000000,'#,#', 'vi-VN') +N'. Số tiền vượt là: '+ FORMAT(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0)-40000000,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
								RETURN '-4'
							END
							ELSE
							BEGIN
								IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName NOT IN('TGD','HQDT')))
								BEGIN
									ROLLBACK TRANSACTION
									SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
									--N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
									--+ CHAR(10) +
									N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
									+ CHAR(10) + CHAR(13) +
									N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
									+ CHAR(10) + 
									N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
									RETURN '-4'
								END
								ELSE
								BEGIN
									ROLLBACK TRANSACTION
									SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
									--N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
									--+ CHAR(10) +
									--N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
									--+ CHAR(10) + CHAR(13) +
									N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
									+ CHAR(10) + 
									N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
									RETURN '-4'
								END
							END
						END
						ELSE IF(@BRANCH_TYPE ='PGD')
						BEGIN
							IF(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0) > 10000000)
							BEGIN
								ROLLBACK TRANSACTION
								SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID,
								N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')+ CHAR(10) +
								N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
								+ CHAR(10) + CHAR(13) + 
								N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Số tiền tạm ứng đang vượt quá số tổng hạn mức tạm ứng của 1 PGD: '+ FORMAT(10000000,'#,#', 'vi-VN') +N'. Số tiền vượt là: '+ FORMAT(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0)-10000000,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
								RETURN '-4'
							END
							ELSE
							BEGIN
								ROLLBACK TRANSACTION
								SELECT '-4' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, 
								N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') 
								+ CHAR(10) + CHAR(13) +
								N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN')
								+ CHAR(10) + 
								N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc
								RETURN '-4'
							END
						END
					END
				END
			-- NEU THOA MAN CAC DIEU KIEN SE TIEN HANH DUYET
			END -- END DU HAN MUC DUYET
-- THOA MAN CAC DIEU KIEN SE TIEN HANH DUYET
			ELSE IF(@p_AUTH_STATUS='A')
			BEGIN
				DECLARE @l_REQ_AMT DECIMAL(18,0) = (SELECT REQ_AMT*ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
				SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
				--
				SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
				IF(@ROLE_ID ='GDK' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
				BEGIN
					SET @LIMIT_AMT =6000000000 -- TANG LEN 6 TY ROLE HAN MUC GDK
					SET @LIMIT_ONE_OF =1000000000 --- TANG LEN 1 TY 1 LAN VOI GDK HO TRO
				
				END
				IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))
				BEGIN
					SET @LIMIT_AMT =4000000000 --- TANG LEN 4 TY
					SET @LIMIT_ONE_OF =500000000
					IF(EXISTS(SELECT * FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD ='PTGD' AND ROLE_NEW ='TGD' 
					AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='')))
					BEGIN
						SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID='TGD' AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
						SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID='TGD' AND  LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN)
						IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER ='TGD' AND REQ_ID =@p_REQ_PAY_ID))
						BEGIN
							UPDATE PL_REQUEST_PROCESS SET STATUS ='P' WHERE ROLE_USER ='PTGD' AND REQ_ID =@p_REQ_PAY_ID
							UPDATE PL_REQUEST_PROCESS SET STATUS ='C',ROLE_USER ='PTGD' WHERE ROLE_USER ='TGD' AND REQ_ID =@p_REQ_PAY_ID
							SET @ROLE_ID ='TGD'
						END
					END
				END
				UPDATE TR_REQ_ADVANCE_PAYMENT
				SET    AUTH_STATUS='A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT =  GETDATE(), AUTH_STATUS_KT='U', IS_AUTHORITY = @p_IS_AUTHORITY
				WHERE  REQ_PAY_ID = @p_REQ_PAY_ID
				--- CAP NHAT LAI TINH TRANG TRONG REQUEST_PROCESS
				DECLARE @PROCESS_CURR VARCHAR(5)
				SET @PROCESS_CURR =(SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND ROLE_USER =@ROLE_ID)
				IF(ISNULL(@p_PROCESS_NOTES,'') <> '')
				BEGIN
					INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@PROCESS_CURR,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N': ' + ISNULL(@p_PROCESS_NOTES,'')  FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'Các cấp phê duyệt theo hạn mức')
				END
				ELSE
				BEGIN
					INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@PROCESS_CURR,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' duyệt'  FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'Các cấp phê duyệt theo hạn mức')
				END
				--- CAP NHAT PROCESS CUA PHIEU DE NGHI TAM UNG LA DA DUYET
				UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS=@PROCESS_CURR WHERE REQ_PAY_ID=@p_REQ_PAY_ID
				--
				UPDATE PL_REQUEST_PROCESS SET STATUS ='A', NOTES = (SELECT ROLE_DESC + N' duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) WHERE  ROLE_USER =@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID
			-- NEU LA HOI SO THI LAY TONG SO TIEN TAM UNG CUA CAC PHONG BAN CHUNG VOI KHOI CUA PHIEU DANG DUOC DUYET
			SET @TONG_PGD =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID = @BRANCH_RQ
					 AND REQ_TYPE ='I' AND AUTH_STATUS ='A') + @REQ_AMT
			SET @TONG_PGD_HOAN =(
						--SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT))
						--FROM TR_REQ_PAYMENT_DT B
						--INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID
						--INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID
						SELECT SUM (ISNULL(A.PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT A
						WHERE A.BRANCH_ID = @BRANCH_RQ
						AND A.AUTH_STATUS_KT ='A' AND A.REQ_TYPE='I') 
				IF(@BRANCH_TYPE='HS')
				BEGIN
					SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE
					 DEP_ID IN
					 (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
						LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
						WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ)
					 AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0)
					SET @TOTAL_PAYBACK =
					ISNULL(
					(
						SELECT (SUM(ISNULL(C.PAY_AMT,0)))
						FROM TR_REQ_ADVANCE_PAYMENT C
						WHERE C.DEP_ID IN 
						(SELECT A.DEP_ID FROM PL_COSTCENTER_DT A
						LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID
						WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_ID)
						AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
					),0)
				END
				--- BO SUNG NEU RIENG PHONG THUONG HIEU VA TRUYEN THONG --- 2021824
				IF(@BRANCH_TYPE='HS' AND @DEP_CODE_NEXT ='0690405' )
				BEGIN
					SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE
					 DEP_ID IN
					 (SELECT DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE ='0690405' AND BRANCH_ID =@BRANCH_RQ)
					 AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0)
					SET @TOTAL_PAYBACK =
					ISNULL(
					(
						SELECT (SUM(ISNULL(C.PAY_AMT,0)))
						FROM TR_REQ_ADVANCE_PAYMENT C
						WHERE C.DEP_ID IN 
						(SELECT DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE ='0690405' AND BRANCH_ID =@BRANCH_RQ)
						AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'
					),0)
				END
				--- END
				-- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC
				-- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC
				ELSE IF(@BRANCH_TYPE <>'HS')
				BEGIN
					SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN)
					 AND REQ_TYPE ='I' AND AUTH_STATUS='A')
					SET @TOTAL_PAYBACK =
					(
						SELECT (SUM(ISNULL(B.PAY_AMT,0)))
						FROM TR_REQ_ADVANCE_PAYMENT B
						WHERE B.BRANCH_ID IN 
						(SELECT BRANCH_ID FROM @tmp_CN)
						AND B.AUTH_STATUS_KT ='A' AND B.REQ_TYPE='I'
					)
				END
				SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0)
				--- INSERT VAO BANG LOG
				INSERT INTO TR_REQ_ADVANCE_LIMIT_LOG  VALUES (@p_REQ_PAY_ID,@LIMIT_REMAIN,GETDATE())
				----
			END
			--ELSE IF (@p_AUTH_STATUS='T')
			--BEGIN
			---- NEU AUTH_STATUS = T LA THU KI TGD / HDQT
			--	IF( @ROLE_ID ='TKTGD')
			--	BEGIN
			--		UPDATE PL_REQUEST_PROCESS SET STATUS ='C' WHERE ROLE_USER ='TGD' AND REQ_ID =@p_REQ_PAY_ID
			--		UPDATE PL_REQUEST_PROCESS SET STATUS ='P' WHERE ROLE_USER ='TKTGD' AND REQ_ID =@p_REQ_PAY_ID
			--		INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
			--		 VALUES(@p_REQ_PAY_ID,'TKTGD',@p_CHECKER_ID,GETDATE(), N'Thư kí văn phòng Tổng Giám Đốc đã phê duyệt',N'Thư kí văn phòng Tổng Giám Đốc phê duyệt')	
			--	END
			--	ELSE IF( @ROLE_ID ='TKHDQT')
			--	BEGIN
			--		UPDATE PL_REQUEST_PROCESS SET STATUS ='C' WHERE ROLE_USER ='HDQT' AND REQ_ID =@p_REQ_PAY_ID
			--		UPDATE PL_REQUEST_PROCESS SET STATUS ='P' WHERE ROLE_USER ='TKHDQT' AND REQ_ID =@p_REQ_PAY_ID
			--		INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
			--		VALUES(@p_REQ_PAY_ID,'TKHDQT',@p_CHECKER_ID,GETDATE(), N'Thư kí văn phòng Hội Đồng Quản Trị đã phê duyệt',N'Thư kí văn phòng Hội Đồng Quản Trị phê duyệt')	
			--	END
			--ENDr
-- NEW KHONG DU HAN MUC THI BAT DAU XAC NHAN VA DUYET THEO HAN MUC
			ELSE -- NEW KHONG DU HAN MUC THI BAT DAU XAC NHAN VA DUYET THEO HAN MUC
			BEGIN
				DECLARE @t_REQ_AMT DECIMAL(18,0) = (SELECT REQ_AMT*ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
		
				--- NEU GIAM DOC KHOI VAN CHUA DU HAN MUC THI GIAM DOC KHOI XAC NHAN VÀ TIEP TUC BUOC TIEP THEO LA PHO TONG GIAM DOC
				DECLARE @MAX_STEP INT, @NEXT_ROLE VARCHAR(20), @MESSAGE NVARCHAR(100), @PROCESS_ID_NEXT VARCHAR(5), @PROCESS_PARENT VARCHAR(5),
				@BRANCH_ID_PROC VARCHAR(15), @NEX_ROLE_STEP VARCHAR(25), @CURRENT_PROCESS VARCHAR(15)

				SET @CURRENT_PROCESS =(SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND STATUS = 'C' AND ROLE_USER IN (SELECT ROLE_AUTH FROM @TABLE_ROLE))
				SET @NEX_ROLE_STEP = (SELECT TOP 1 ROLE_USER FROM PL_REQUEST_PROCESS WHERE PARENT_PROCESS_ID =@CURRENT_PROCESS AND REQ_ID = @p_REQ_PAY_ID )
				--SET @MAX_STEP = CONVERT(INT,(SELECT MAX(PROCESS_ID) FROM TR_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID))
				SET @PROCESS_ID_NEXT = CONVERT(VARCHAR(5),@CURRENT_PROCESS+1)
				--SET @PROCESS_PARENT = CONVERT(VARCHAR(5),@MAX_STEP)
				DECLARE @TMP_ROLE TABLE (ROLE_ID VARCHAR(15), BRANCH_ID VARCHAR(15), LIMIT_VALUE DECIMAL(18,0),LIMIT_TYPE VARCHAR(15))
				--IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06908%' AND DEP_ID =@p_DEP_ID)
					IF(LEFT(@DEP_CODE_NEXT,5) ='06908' OR @DEP_CODE_NEXT ='0690605' OR @DEP_CODE_NEXT LIKE '06906%')
					BEGIN
						--LUCTV 29032021: KHOI TAI CHINH SE KHONG CO PTGD NUA, CHI CO GDK TAI CHINH
						INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'PTGD'
						--INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK'
					END
					ELSE
					BEGIN
						IF( @DEP_CODE_NEXT ='0690405')
						BEGIN
							--LUCTV 29032021: KHOI TAI CHINH SE KHONG CO PTGD NUA, CHI CO GDK TAI CHINH
							INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <> 'GDK'
							--INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK'
						END
						--ELSE IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06910%' AND DEP_ID =@p_DEP_ID)
						ELSE IF(LEFT(@DEP_CODE_NEXT,5) ='06910')
						BEGIN
							INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK'
						END
						--ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06900%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06901%' AND DEP_ID =@p_DEP_ID)
						--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06902%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06903%' AND DEP_ID =@p_DEP_ID) 
						--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06904%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06920%' AND DEP_ID =@p_DEP_ID))
						ELSE IF((LEFT(@DEP_CODE_NEXT,5) ='06900') OR (LEFT(@DEP_CODE_NEXT,5)) ='06901' OR (LEFT(@DEP_CODE_NEXT,5))='06904'
						OR (LEFT(@DEP_CODE_NEXT,5))='06902' OR (LEFT(@DEP_CODE_NEXT,5))='06903'  OR (LEFT(@DEP_CODE_NEXT,5))='06920' OR (LEFT(@DEP_CODE_NEXT,3))='799'  OR (LEFT(@DEP_CODE_NEXT,3))='899')
						BEGIN
							INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID NOT IN ('GDK','PTGD')
						END
						--ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06907%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06909%' AND DEP_ID =@p_DEP_ID)
						--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06911%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06921%' AND DEP_ID =@p_DEP_ID) 
						--OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06922%' AND DEP_ID =@p_DEP_ID))
						ELSE IF((LEFT(@DEP_CODE_NEXT,5) ='06907') OR (LEFT(@DEP_CODE_NEXT,5)) ='06909' OR (LEFT(@DEP_CODE_NEXT,5))='06911'
						OR (LEFT(@DEP_CODE_NEXT,5))='06922' OR (LEFT(@DEP_CODE_NEXT,5))='06921'  OR (LEFT(@DEP_CODE_NEXT,5))='06920')
						BEGIN
							INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'PTGD'
						END
						ELSE
						BEGIN
							INSERT INTO @TMP_ROLE  SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' ORDER BY LIMIT_VALUE
						END
					END
					/*IF(1=1)
					BEGIN
					ROLLBACK TRANSACTION
					select @DEP_CODE_NEXT AS DEP_CODE, * from @TMP_ROLE ORDER BY LIMIT_VALUE
					RETURN '-1'
					return 
					END*/
				SET @BRANCH_ID_PROC= (SELECT TOP 1 BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY')
				--print @DEP_CODE_NEXT
				--print @BRANCH_ID_PROC 
			    --print @NEX_ROLE_STEP
				IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@NEX_ROLE_STEP AND REQ_ID= @p_REQ_PAY_ID))
				BEGIN
					-- doanptt them note khi xac nhan
					IF(@p_PROCESS_NOTES IS NULL OR @p_PROCESS_NOTES = '')
					BEGIN
						SET @p_PROCESS_NOTES = (SELECT TOP 1 ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID);
					END
					--SET @NEXT_ROLE =(SELECT TOP 1 ROLE_ID FROM @TMP_ROLE WHERE LIMIT_VALUE >@t_REQ_AMT 
					SET @NEXT_ROLE =(	SELECT TOP 1 ROLE_ID 
										FROM @TMP_ROLE 
										WHERE 1 =1
										AND ROLE_ID NOT IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID) 
										AND LIMIT_TYPE ='ADV_PAY' ORDER BY LIMIT_VALUE)
					--print @NEXT_ROLE
					SET @BRANCH_ID_PROC= (SELECT TOP 1 BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY')
					SET @MESSAGE = (SELECT TOP 1 N'Đợi '+ ROLE_DESC + N' phê duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @NEXT_ROLE)
					INSERT INTO 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)
					VALUES (@p_REQ_PAY_ID,@PROCESS_ID_NEXT,'C',@NEXT_ROLE,@BRANCH_ID_PROC,@NEXT_ROLE,GETDATE(),@PROCESS_PARENT,'Y',NULL,@p_COST_ID,@MESSAGE)
					INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@CURRENT_PROCESS,@p_CHECKER_ID, GETDATE(), @p_PROCESS_NOTES,N'Các cấp phê duyệt theo hạn mức')
				END
				ELSE
				BEGIN
					-- doanptt them note khi xac nhan
					IF(@p_PROCESS_NOTES IS NULL OR @p_PROCESS_NOTES = '')
					BEGIN
						SET @p_PROCESS_NOTES = (SELECT TOP 1 ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID);
					END
					ELSE
					BEGIN
						SET @p_PROCESS_NOTES = (SELECT TOP 1 ROLE_DESC + N': ' + @p_PROCESS_NOTES FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID);
					END

					SET @NEXT_ROLE =@NEX_ROLE_STEP
					UPDATE PL_REQUEST_PROCESS SET STATUS='C',DVDM_ID=@p_COST_ID WHERE ROLE_USER =@NEXT_ROLE AND REQ_ID = @p_REQ_PAY_ID	
					INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@CURRENT_PROCESS,@p_CHECKER_ID, GETDATE(), @p_PROCESS_NOTES,N'Các cấp phê duyệt theo hạn mức')
				END
				--				
				IF @@Error <> 0 GOTO ABORT
					-- UPDATE STATUS CUA STEP HIEN TAI
				UPDATE PL_REQUEST_PROCESS SET STATUS='P',IS_LEAF ='N', NOTES = (SELECT TOP 1 ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID =@ROLE_ID)
				WHERE ROLE_USER IN(SELECT * FROM @TABLE_ROLE) AND REQ_ID = @p_REQ_PAY_ID		
				--INSERT VAO TR_PROCESS		
				UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS =@CURRENT_PROCESS , AUTH_STATUS='U',DVDM_ID = @p_COST_ID WHERE REQ_PAY_ID =@p_REQ_PAY_ID	
				--- 
				UPDATE PL_REQUEST_PROCESS SET DVDM_ID= @p_COST_ID WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER <>@ROLE_ID
		  END
		END	
		ELSE
		BEGIN
			--- NEU LA TAM UNG NHA CUNG CAP THI CHECK XEM KI TAM UNG DO DA HOAN THANH HAY CHUA, NEU DA HOAN THANH THI KHONG CHO DUYET
			DECLARE @PAY_ID VARCHAR(15), @SUM_OF_PAY_ID DECIMAL(18,2), @SUM_ADVANCE DECIMAL(18,2),
			@AMOUNT DECIMAL(18,2),@AMT_PAY_DO DECIMAL(18,2),@SCH_ID VARCHAR(15),@AMT_ADVANCE DECIMAL(18,0)
			DECLARE CUR_SH CURSOR FOR SELECT A.PAY_ID,A.AMT_PAY, A.AMT_PAY_DO,A.SCHEDULE_ID,A.AMT_ADVANCE FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID= @p_REQ_PAY_ID
			OPEN CUR_SH
			FETCH NEXT FROM CUR_SH INTO @PAY_ID,@AMOUNT,@AMT_PAY_DO,@SCH_ID,@AMT_ADVANCE
			WHILE @@FETCH_STATUS =0
			BEGIN
				SET @SUM_OF_PAY_ID =(SELECT TOP 1 SUM(AMT_PAY_DO) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A')
				SET @SUM_ADVANCE =(SELECT TOP 1 SUM(AMT_ADVANCE) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A')
				IF(@SUM_OF_PAY_ID +@AMT_PAY_DO >@AMOUNT)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, N'Số tiền tạm ứng đã vượt mức số tiền còn lại cần phải thanh toán' ErrorDesc
					RETURN '-1'
				END
				IF(@SUM_OF_PAY_ID+@AMT_PAY_DO+@SUM_ADVANCE < @AMOUNT)
				BEGIN
					UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTU' WHERE SCHEDULE_ID=@SCH_ID
				END
				ELSE IF(@SUM_OF_PAY_ID+@AMT_PAY_DO+@SUM_ADVANCE = @AMOUNT)
				BEGIN
					UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTT' WHERE SCHEDULE_ID=@SCH_ID
				END
				ELSE IF(@SUM_OF_PAY_ID=0 AND @SUM_ADVANCE =0 )
				BEGIN
					UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='CTT' WHERE SCHEDULE_ID=@SCH_ID
				END
				FETCH NEXT FROM CUR_SH INTO @PAY_ID,@AMOUNT,@AMT_PAY_DO,@SCH_ID,@AMT_ADVANCE
			END
			CLOSE CUR_SH
			DEALLOCATE CUR_SH 

			UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS='A', CHECKER_ID=@p_CHECKER_ID, APPROVE_DT = GETDATE(),AUTH_STATUS_KT='U' WHERE REQ_PAY_ID =@p_REQ_PAY_ID
			UPDATE PL_REQUEST_PROCESS SET [STATUS] ='A' WHERE [STATUS] ='C' AND REQ_ID =@p_REQ_PAY_ID
			SET @p_AUTH_STATUS ='A'
			--- INSERT 1 DONG VAO PL_PROCESS
			-- INSERT VAO BANG PL_PROCESS 1 DONG TRA VE
			INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
				   VALUES(@p_REQ_PAY_ID,'APP',@p_CHECKER_ID,GETDATE(), N'Trưởng đơn vị đã phê duyệt',N'Trưởng đơn vị phê duyệt')			
		END	
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
	IF(@p_AUTH_STATUS='A')
	BEGIN
		SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'12345' +@DEP_CODE_NEXT ErrorDesc
		RETURN '0'
	END
	--ELSE IF(@p_AUTH_STATUS='T' AND @ROLE_ID ='TKTGD')
	--BEGIN
	--	SELECT '5' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'Thư kí văn phòng Tổng Giám Đốc phê duyệt phiếu đề nghị tạm ứng số: ' +(SELECT TOP 1 REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+ N' thành công' +@DEP_CODE_NEXT ErrorDesc
	--	RETURN '5'
	--END
	--ELSE IF(@p_AUTH_STATUS='T' AND @ROLE_ID ='TKHDQT')
	--BEGIN
	--	SELECT '5' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'Thư kí văn phòng Hội Đồng Quản Trị phê duyệt phiếu đề nghị tạm ứng số: ' +(SELECT TOP 1 REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+ N' thành công' +@DEP_CODE_NEXT ErrorDesc
	--	RETURN '5'
	--END
	ELSE
	BEGIN
		SELECT '1' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,'Phiếu đề nghị tạm ứng số: ' + @p_REQ_PAY_ID + ' đã được phê duyệt thành công. Vui lòng đợi bộ phận kế toán xử lý phiếu.' ErrorDesc
		RETURN '1'
	END
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
END

GO

ALTER   PROCEDURE [dbo].[TR_CHECK_ROLE_APPROVE]
@TYPE VARCHAR(15) = NULL,
@p_REQ_ID	varchar(15) = NULL,
@p_USER_LOGIN varchar(15) = NULL
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_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
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID AND TLNAME =@p_USER_LOGIN AND
(CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR (EFF_DATE IS NULL OR EXP_DATE IS NULL))

INSERT INTO @TABLE_DEP SELECT ROLE_NEW, BRANCH_ID, DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID AND TLNAME =@p_USER_LOGIN AND
(CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR (EFF_DATE IS NULL OR EXP_DATE IS NULL))

	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 RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	INSERT INTO @AUTHOR_DVDM
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
	    DVDM_ID
	)
	SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE TU.TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU 
	LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
	LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	INSERT INTO @AUTHOR_DMMS
	(
	    ROLE_ID,
	    BRANCH_ID,
	    DEP_ID,
		DMMS_ID
	)
	SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TLNANME=@p_USER_LOGIN
	UNION ALL
	SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
	UNION ALL
	SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
	LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
	LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
	LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
	WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)

	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
		-- 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
		--doanptt: THU KY DA DIEU PHOI CHO NHAN VIEN XU LY THI KHONG DUOC DUYET NUA
		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
		--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(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_ROLE A 
							WHERE A.ROLE_AUTH IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','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 ='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, '' 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, '' 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
		-- 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_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, '' 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, '' 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, '' 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, '' 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

GO

ALTER   PROCEDURE [dbo].[TR_REQ_PAY_DETAIL]  
@p_REQ_PAY_ID VARCHAR(15) = NULL  
AS  
BEGIN  
 
	DECLARE @TABLE_RETURN_01 TABLE ([NAME] NVARCHAR(500))  
	DECLARE @TABLE_RETURN_01_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500))  
	DECLARE @TABLE_RETURN_02 TABLE ([NAME] NVARCHAR(250))  
	DECLARE @TABLE_RETURN_02_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500))  
	DECLARE @TABLE_RETURN_03 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500), AMT DECIMAL(18,2))  
	DECLARE @TABLE_RETURN_04 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500), AMT DECIMAL(18,2))  
	DECLARE @TABLE_RETURN_06 TABLE ([NAME] NVARCHAR(500), AMT DECIMAL(18,2))  
	DECLARE @INDEX INT =1  
	DECLARE @INDEX_TT INT =0  
	DECLARE @HINHTHUC_CK NVARCHAR(100)  
	DECLARE @CHUOI NVARCHAR(180)

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'))  
	BEGIN  
		SET @INDEX_TT = @INDEX_TT+1  
		--INSERT INTO @TABLE_RETURN  
		--SELECT N'Tiền mặt' AS PHUONG_THUC, ISNULL(SUM (TOTAL_AMT),0) AS T1 FROM TR_REQ_PAY_METHOD  
		--WHERE REQ_PAY_TYPE ='1' AND REQ_PAY_ID = @p_REQ_PAY_ID  

		INSERT INTO @TABLE_RETURN_03  
		SELECT N'. Người nhận: '+ ACC_NAME + CHAR(10) + N' CMND số: ' + ACC_NO + char(10) + N' Được cấp bởi: '+ ISSUED_BY + N' ngày cấp: '+ CONVERT(VARCHAR,ISSUED_DT,103) AS ACC_NAME, SUM(TOTAL_AMT) AS T2  
		FROM TR_REQ_PAY_METHOD 
		WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'  
		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT  

		-- INSERT BANG TAM THE HIEN SO TIEN  
		INSERT INTO @TABLE_RETURN_04  
		SELECT 'N'+ CHAR(10) + 'D'+char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T2  
		FROM TR_REQ_PAY_METHOD 
		WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'  
		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT  
	END  

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <> '1'))  
	BEGIN  
		SET @INDEX_TT = @INDEX_TT+1  
		--INSERT INTO @TABLE_RETURN  
		--SELECT N'Chuyển khoản' AS PHUONG_THUC,ISNULL(SUM (TOTAL_AMT),0) AS T3 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_TYPE ='0' AND REQ_PAY_ID = @p_REQ_PAY_ID  

		INSERT INTO @TABLE_RETURN_03  
		SELECT CASE WHEN TYPE_TRANSFER ='R' AND TYPE_TRANSFER IS NOT NULL AND TYPE_TRANSFER <>'' AND ISNULL(SUB_ISSUED_BY, '') = '' THEN (N'. Trích từ tài khoản: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY)
					WHEN TYPE_TRANSFER ='R' AND TYPE_TRANSFER IS NOT NULL AND TYPE_TRANSFER <>'' AND ISNULL(SUB_ISSUED_BY, '') <> '' THEN (N'. Trích từ tài khoản: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY + ' - ' + SUB_ISSUED_BY )
					WHEN TYPE_TRANSFER <> 'R' AND ISNULL(TYPE_TRANSFER, '') <> '' AND ISNULL(SUB_ISSUED_BY, '') <> '' THEN (N'. Đơn vị thụ hưởng: '+ ACC_NAME  + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY + ' - ' + SUB_ISSUED_BY)
					ELSE (N'. Đơn vị thụ hưởng: '+ ACC_NAME  + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY) 
					END AS ACC_NAME, 
					SUM(TOTAL_AMT) 
				AS T4  
		FROM TR_REQ_PAY_METHOD 
		WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <>'1'  
		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY,TYPE_TRANSFER , SUB_ISSUED_BY

		-- INSERT BANG TAM THE HIEN SO TIEN  
		INSERT INTO @TABLE_RETURN_04  
		SELECT 'N'+ CHAR(10)+ 'S'+ char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T4  
		FROM TR_REQ_PAY_METHOD 
		WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <>'1'  
		GROUP BY ACC_NO, ACC_NAME, ISSUED_BY,TYPE_TRANSFER  
	END  

-- KIEM TRA NOI DUNG SU DUNG CHI PHI VA NGAN SACH  
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_PERIOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SERVICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  
	BEGIN  
		--INSERT INTO @TABLE_RETURN_01 VALUES (CONVERT(VARCHAR(15),@INDEX) +N'. Nội dung tạm ứng: ')  
		INSERT INTO @TABLE_RETURN_01 VALUES( N'A - Nội dung thanh toán: ')  
		--INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_REASON FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
		--INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REASON FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
		INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.REASON,150) + LEFT(A.REASON,30), A.AMT_PAY FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
 
		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_DESC FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
		INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.REQ_PAY_DESC,150)+ LEFT(A.REQ_PAY_DESC,30), A.AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
 
		INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.[SERVICE_NAME] FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
		INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.[SERVICE_NAME],200)+LEFT(A.[SERVICE_NAME],70), A.TOTAL_AMT FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  
	END  

--- HĐ & CHỨNG TỪ ĐÍNH KÈM  
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  
	BEGIN  
		SET @INDEX = @INDEX+1  
		--INSERT INTO @TABLE_RETURN_01 SELECT CONVERT(VARCHAR(15),@INDEX)+ N'. Chứng từ đính kèm: '  
		INSERT INTO @TABLE_RETURN_02 SELECT N'B - Chứng từ đính kèm: '  
	END  

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='C'))  
	BEGIN  
		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin hợp đồng đính kèm: '  
		INSERT INTO @TABLE_RETURN_02_DT SELECT ' '+ N'. Hợp đồng số ' + REF_CODE + ISNULL(N' ngày '+ FORMAT(REF_DT,'dd/MM/yyyy'),'')+ CASE WHEN AMT >0 THEN N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH  
		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='C'  
	END  

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='R'))  
	BEGIN  
		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: '  
		INSERT INTO @TABLE_RETURN_02_DT SELECT N'. '+ [DESCRIPTION]+ CASE WHEN AMT >0 THEN N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH  
		WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='R'  
	END  

	IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  
		BEGIN  
		--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: '  
		INSERT INTO @TABLE_RETURN_02_DT SELECT N'. Số hóa đơn '+ HD.INVOICE_NO +ISNULL(N' ngày '+ FORMAT(HD.INVOICE_DT,'dd/MM/yyyy'),'')+ N', số tiền '+ FORMAT( ISNULL(HD.PRICE+HD.VAT,0),'#,#', 'vi-VN') AS T1
		FROM
		(
		SELECT MAX(A.INVOICE_NO) AS INVOICE_NO, MAX(A.INVOICE_DT) AS INVOICE_DT, SUM(A.PRICE) AS PRICE, SUM(A.VAT) AS VAT
		FROM
		TR_REQ_PAY_INVOICE  A
		WHERE A.REQ_PAY_ID = @p_REQ_PAY_ID AND A.TYPE_FUNC='HC'  
		GROUP BY A.INVOICE_NO,A.INVOICE_NO_SIGN,A.INVOICE_SIGN,A.TAX_NO
		)
		AS HD
	END  

	
	DECLARE @ROLE VARCHAR(15)  
	DECLARE @TABLE_RETURN_07 TABLE (HDQT NVARCHAR(50),TGD NVARCHAR(50),PTGD NVARCHAR(50),GDK NVARCHAR(50),GDDV NVARCHAR(50))  
	DECLARE @GDDV NVARCHAR(50),@GDK NVARCHAR(50),@PTGD NVARCHAR(50),@TGD NVARCHAR(50),@HDQT NVARCHAR(50)  
	DECLARE @APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),@APPR5 VARCHAR(15)  
	DECLARE @POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50)  
	DECLARE @DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50)
	DECLARE @BRANCH_TYPE VARCHAR(15), @BRANCH_RQ VARCHAR(15)  
	DECLARE @PROCESS_GDDV VARCHAR(5), @PROCESS_GDK VARCHAR(5), @PROCESS_PTGD VARCHAR(5), @PROCESS_TGD VARCHAR(5), @PROCESS_HDQT VARCHAR(5)

	-- LAY THONG TIN CAP PHE DUYET  
	IF(	EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE ='I'))  
	BEGIN  
 -- GDDV
		SET @PROCESS_GDDV = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'GDDV')
		SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV))  
		SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR)  
		SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV) AND  
		@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  
 
		SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)  
		SET @DATE =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV  
		AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV) AND (@APPR IS NOT NULL AND @APPR <>''))  
		-------------  
-- GDK
		SET @PROCESS_GDK = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'GDK')
		SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK))  
		SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR1)  
		SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK) AND  
		@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  
 
		SET @POS1 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)  
		SET @DATE1 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK) AND (@APPR1 IS NOT NULL AND @APPR1 <>'')) 
		------------  
-- PTGD 
		SET @PROCESS_PTGD = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'PTGD')
		SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD))  
		SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR2)  
		SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD) AND  
		@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  
	
		SET @POS2 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)  
		SET @DATE2 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD) AND (@APPR2 IS NOT NULL AND @APPR2 <>''))  
-- TGD 
		SET @PROCESS_TGD = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'TGD')
		SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD))  
		SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR3)  
		SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD) AND  
		@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  
 
		SET @POS3 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)  
		SET @DATE3 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD  
		AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD) AND (@APPR3 IS NOT NULL AND @APPR3 <>''))  
-- HDQT 
		SET @PROCESS_HDQT = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'HDQT')
		SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT))  
		SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR4)  
		SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT) AND  
		@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  
		SET @POS4 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)  
		SET @DATE4 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT AND  
		ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT) AND @APPR4 IS NOT NULL AND @APPR4 <>'')    
		SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)  
		SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ) 
		
		IF(@BRANCH_TYPE='CN')  
		BEGIN  
			SET @DATE =(SELECT FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)

			SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN= (SELECT CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

			INSERT INTO @TABLE_RETURN_07 
			VALUES	(	(	SELECT TLFULLNAME 
							FROM TL_USER 
							WHERE TLNANME = @APPR4),
						(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3),  
						(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR2),
						(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),
						(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = (SELECT CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)))  
			INSERT INTO @TABLE_RETURN_07 VALUES (@POS4,@POS3,@POS2,@POS1,@POS)  
			INSERT INTO @TABLE_RETURN_07 VALUES (CASE WHEN @DATE4 <>'' THEN N'Đã chấp thuận '+ @DATE4 ELSE '' END,CASE WHEN @DATE3 <>'' THEN N'Đã chấp thuận '+ @DATE3 ELSE '' END,  
			CASE WHEN @DATE2 <>'' THEN N'Đã chấp thuận '+ @DATE2 ELSE '' END,CASE WHEN @DATE1 <>'' THEN N'Đã chấp thuận '+ @DATE1 ELSE '' END,CASE WHEN @DATE <>'' THEN N'Đã chấp thuận '+ @DATE ELSE '' END)  
		END  
		ELSE  
		BEGIN  
			INSERT INTO @TABLE_RETURN_07 
			VALUES (	(	SELECT TLFULLNAME 
							FROM TL_USER 
							WHERE TLNANME = @APPR4),
						(	SELECT TLFULLNAME 
							FROM TL_USER 
							WHERE TLNANME = @APPR3),  
						(	SELECT TLFULLNAME 
							FROM TL_USER 
							WHERE TLNANME = @APPR2),
						(	SELECT TLFULLNAME 
							FROM TL_USER 
							WHERE TLNANME = @APPR1),
						(	SELECT TLFULLNAME 
							FROM TL_USER 
							WHERE TLNANME = @APPR)
					)  

			INSERT INTO @TABLE_RETURN_07 
			VALUES (@POS4,@POS3,@POS2,@POS1,@POS)  

			INSERT INTO @TABLE_RETURN_07 
			VALUES (	CASE	WHEN @DATE4 <>'' THEN N'Đã chấp thuận '+ @DATE4 
								ELSE '' 
						END,
						CASE	WHEN @DATE3 <>'' THEN N'Đã chấp thuận '+ @DATE3 
								ELSE '' 
						END,  
						CASE	WHEN @DATE2 <>'' THEN N'Đã chấp thuận '+ @DATE2 
								ELSE '' 
						END,
						CASE	WHEN @DATE1 <>'' THEN N'Đã chấp thuận '+ @DATE1 
								ELSE '' 
						END,
						CASE	WHEN @DATE <>'' THEN N'Đã chấp thuận '+ @DATE 
								ELSE '' 
						END
					)  
		END  
	END  

SELECT * FROM @TABLE_RETURN_01  
SELECT * FROM @TABLE_RETURN_01_DT  
SELECT * FROM @TABLE_RETURN_02  
SELECT * FROM @TABLE_RETURN_02_DT  
SELECT * FROM @TABLE_RETURN_03  
SELECT * FROM @TABLE_RETURN_04  
SELECT * FROM @TABLE_RETURN_06  
SELECT * FROM @TABLE_RETURN_07  
END