DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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