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

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

AS

BEGIN TRANSACTION

		-- KHAI BAO THEM ROLE NHAN UY QUYEN

		DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))

		--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) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)

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

				DECLARE @BRANCH_CREATE VARCHAR(15), @p_DEP_ID VARCHAR(15) 

		--PRINT @BRANCH_CREATE 

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

		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)

		END

		IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE MAKER_ID =@p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID)

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, ''  REQ_PAY_ID, N'Người phê duyệt phiếu phải khác với người tạo phiếu! Bạn không được phép duyệt đối tượng này' ErrorDesc

			RETURN '-1'

		END

		-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET

		IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đ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

		-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET

		IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đ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

		-- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET

		IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được KSV phê duyệt trước đó' 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

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được phê duyệt trước đó' ErrorDesc

			RETURN '-1'

		END

		--- KHAI BAO CHUUNG

		   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

		IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE='I'))

		BEGIN

			IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@ROLE_ID 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(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  

			IF(@p_AUTH_STATUS='U') -- KIEM TRA HAN MUC CON LAI CUA USER VA THONG BAO CHO NGUOI DUYET

			BEGIN

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

			END

			IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))

			BEGIN

				SET @LIMIT_AMT =3000000000

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

						AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'

					),0)

				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)

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

							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

			ELSE IF(@p_AUTH_STATUS='A')

			BEGIN

				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

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

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

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

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

			END

			IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011'))

			BEGIN

				SET @LIMIT_AMT =3000000000

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

						AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I'

					),0)

				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)

				--- INSERT VAO BANG LOG

				INSERT INTO TR_REQ_ADVANCE_LIMIT_LOG  VALUES (@p_REQ_PAY_ID,@LIMIT_REMAIN,GETDATE())

				----

			END

			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)

				--PRINT @t_REQ_AMT

				--- 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 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND ROLE_USER=@ROLE_ID)

				SET @NEX_ROLE_STEP = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE PARENT_PROCESS_ID =@CURRENT_PROCESS AND REQ_ID = @p_REQ_PAY_ID )

				--PRINT @NEX_ROLE_STEP

				--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 ='0690405')

					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 '%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')

					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'

					END

				SET @BRANCH_ID_PROC= (SELECT 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

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

					--print @NEXT_ROLE

					SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY')

					SET @MESSAGE = (SELECT 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(),(SELECT ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'Các cấp phê duyệt theo hạn mức')

				END

				ELSE

				BEGIN

					--print @NEX_ROLE_STEP

					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(),(SELECT ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),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 SUM(AMT_PAY_DO) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A')

				SET @SUM_ADVANCE =(SELECT 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'

					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

			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

	BEGIN

		SELECT '1' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,'' ErrorDesc

		RETURN '1'

	END

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc

		RETURN '-1'

END

¿

ALTER PROC [dbo].[TL_SYSROLE_LIMIT_Auto]

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_LIMIT_TYPE VARCHAR(15) = NULL

AS

BEGIN TRANSACTION

IF(@p_LIMIT_TYPE='ADV_PAY')

BEGIN

	DECLARE @BRANCH_ID VARCHAR(15), @BRANCH_NAMME NVARCHAR(200), @BRANCH_TYPE VARCHAR(15),@p_LIMIT_ID VARCHAR(15), @TOTAL_PGD DECIMAL(18,0), @DMMS_ID VARCHAR(15), @LIMIT_PGD DECIMAL(18,0),

	@LIMIT_CN DECIMAL(18,0)

	SET @LIMIT_PGD =10000000

	SET @LIMIT_CN =40000000

	DECLARE CUR_DV CURSOR

	FOR SELECT A.BRANCH_ID, A.BRANCH_NAME, A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_TYPE IN ('CN','PGD') AND (A.BRANCH_ID =@p_BRANCH_ID OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='')

	OPEN CUR_DV

	FETCH NEXT FROM CUR_DV INTO @BRANCH_ID,@BRANCH_NAMME,@BRANCH_TYPE

	WHILE @@FETCH_STATUS = 0

	BEGIN

		--SET @TOTAL_PGD =(SELECT COUNT(*) FROM CM_BRANCH WHERE FATHER_ID =@BRANCH_ID)*10000000

		SET @TOTAL_PGD =0

		EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out

		IF(@BRANCH_TYPE ='CN' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDDV'))

			BEGIN

				INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'GDDV',@LIMIT_CN+@TOTAL_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_CN, NULL)

			END

		ELSE IF(@BRANCH_TYPE ='PGD' AND  NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='TPGD' AND @BRANCH_TYPE ='PGD'))

			BEGIN

				INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'TPGD',@LIMIT_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_PGD, NULL)

			END

		ELSE IF (@BRANCH_TYPE ='CN' AND EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDDV'))

		BEGIN

				UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =@LIMIT_CN +@TOTAL_PGD, LIMIT_PERCENT= @LIMIT_CN WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='GDDV'

		END

		-- KIỂM  TRA XEM ĐÃ KHAI BÁO ĐƠN VỊ / CHI NHÁNH NÀY TRONG CM_DMMS HAY CHƯA

		IF(NOT EXISTS (SELECT * FROM CM_DMMS WHERE BRANCH_ID =@BRANCH_ID))

		BEGIN

			 EXEC SYS_CodeMasters_Gen 'CM_DMMS', @DMMS_ID out

			 INSERT INTO CM_DMMS (DMMS_ID,BRANCH_ID, DEP_ID,DVDM_ID) VALUES (@DMMS_ID,@BRANCH_ID,(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE BRANCH_ID =@BRANCH_ID),'')

		END

		FETCH NEXT FROM CUR_DV INTO @BRANCH_ID,@BRANCH_NAMME,@BRANCH_TYPE

		END

	CLOSE CUR_DV

	DEALLOCATE CUR_DV

END

ELSE

BEGIN

-- INSERT HAN MUC CHO HOI SO

-- GIAM DOC KHOI

	IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDK'))

	BEGIN

		EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out

		INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'GDK',100000000,N'HAN MUC GIAM DOC KHOI','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',50000000,NULL)

	END

	BEGIN

		UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =100000000, LIMIT_PERCENT= 50000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='GDK'

	END

	-- PHO TONG GIAM DOC

	IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='PTGD'))

	BEGIN

		EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out

		INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'PTGD',200000000,N'HAN MUC PHE DUYET PHO TONG GIAM DOC','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',100000000,NULL)

	END

	BEGIN

		UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =200000000, LIMIT_PERCENT= 100000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='PTGD'

	END

	IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='TGD'))

	BEGIN

		EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out

		INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'TGD',2000000000,N'HAN MUC PHE DUYET TONG GIAM DOC','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',500000000,NULL)

	END

	BEGIN

		UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =2000000000, LIMIT_PERCENT= 500000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='TGD'

	END

END

COMMIT TRANSACTION

SELECT '0' as Result, ''  LIMIT_ID, (SELECT TOP 1 LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@p_BRANCH_ID AND LIMIT_TYPE ='ADV_PAY') AS LIMIT_VALUE,'' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' LIMIT_ID, 0.0 AS LIMIT_VALUE, '' ErrorDesc

		RETURN '-1'

END

¿

UPDATE SYS_CODEMASTERS SET CurValue = CurValue+300 WHERE Prefix ='DMMS'

¿

ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_App]

    @p_REQ_ID VARCHAR(15) = NULL,

	@p_AUTH_STATUS VARCHAR(1) = NULL,

	@p_CHECKER_ID varchar(15)  = NULL,

	@p_APPROVE_DT DATETIME = NULL,

	@p_ROLE_LOGIN VARCHAR(50) = NULL,

	@p_BRANCH_LOGIN VARCHAR(15),

	@p_PROCESS_DES NVARCHAR(500)

AS

	--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 

  IF ( NOT EXISTS ( SELECT * FROM TR_REQUEST_DOC WHERE  REQ_ID = @p_REQ_ID))

	SET @ERRORSYS = 'REQ-00002'

IF @ERRORSYS <> '' 

BEGIN

	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

	RETURN '0'

END 

BEGIN TRANSACTION

	-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC

	IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc

			RETURN '-1'

	END

	--- PHE DUYET TRUNG GIAN

	DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE_N VARCHAR(20) ,@DEP_CREATE_N VARCHAR(20),@BRANCH_TYPE VARCHAR(10),

	@BRANCH_CREATE_TYPE VARCHAR(10)

	SELECT @BRANCH_CREATE_N=BRANCH_CREATE,@DEP_CREATE_N=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

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

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

	-- LUCTV 08122020 KIỂM TRA NẾU TỜ TRÌNH LINK TỚI PYCMS LÀ TỜ TRÌNH TOÀN HÀNG THÌ ĐMMS GIAO CHO ĐƠN VỊ TỰ MUA SẮM

	DECLARE @PL_REQ_REF VARCHAR(15), @ISCHECK_ALL VARCHAR(1)

	SET @PL_REQ_REF =(SELECT TOP 1 PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)

	SET @ISCHECK_ALL =(SELECT TOP 1 IS_CHECKALL FROM PL_REQUEST_DOC WHERE REQ_ID =@PL_REQ_REF)

	-- LUCTV 08122020: END

	IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN'))

	BEGIN

		DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

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

			'SIGN',        -- PROCESS_ID - varchar(10)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			@p_APPROVE_DT , -- APPROVE_DT - datetime

			N'Cấp phê duyệt trung gian xác nhận phiếu yêu cầu mua sắm',

			N'Cấp phê duyệt trung gian'

		)

	--- DUA CAP PHE DUYET TRUONG DON VI

		--IF(@BRANCH_CREATE_TYPE='PGD')

		--	SET @BRANCH_CREATE_N=(SELECT FATHER_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N)

	    INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

			DEP_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		VALUES

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

		    'APPNEW',        -- PROCESS_ID - varchar(10)

		    'C',        -- STATUS - varchar(5)

		    'GDDV',        -- ROLE_USER - varchar(50)

		    @BRANCH_CREATE_N,  

			@DEP_CREATE_N,      -- BRANCH_ID - varchar(15)

		    '',        -- CHECKER_ID - varchar(15)

		    NULL,      -- APPROVE_DT - datetime

		    '',        -- PARENT_PROCESS_ID - varchar(10)

		    'N',        -- IS_LEAF - varchar(1)

		    '',        -- COST_ID - varchar(15)

		    '',        -- DVDM_ID - varchar(15)

		    N'Chờ trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)

		    NULL       -- IS_HAS_CHILD - bit

		 )

	--- UPDATE PROCESS_ID VE APP_NEW

	UPDATE TR_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID

	END

	ELSE

	--- PHE DUYET GIU NGUYEN NHU THUONG

	BEGIN

		DECLARE @ROLE_USER_LOGIN VARCHAR(15)=''

		SET @ROLE_USER_LOGIN =(SELECT RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)

		--- KIEM TRA XEM DA PHE DUYET TRUNG GIAN HAY CHUA

		IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> ''))

		BEGIN

			IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác nhận phiếu!' ErrorDesc

				RETURN '-1'

			END

			IF(@p_CHECKER_ID = (SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi trưởng đơn vị phê duyệt. Bạn không có thẩm quyền phê duyệt cấp trưởng đơn vị! Vui lòng xem lịch sử xử lý phiếu' ErrorDesc

				RETURN '-1'

			END

		END

		-- LUCTV: 08 12 2020: KIỂM TRA NẾU CẤP DUYỆT KHÔNG NẰM TRONG CẤP TP, PP THÌ KHÔNG CHO DUYỆT TRƯỞNG ĐƠN VỊ

		--IF(

		DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID

		DECLARE @CURR_PROCESS VARCHAR(20),@TOTAL_AMT DECIMAL(18,2),

		 @LIMIT_VALUE DECIMAL(18,0), @LIMIT_DVCM DECIMAL(18,0),@IS_NEXT BIT,@DMMS_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@BRANCH_HS VARCHAR(20),@DEP_CREATE VARCHAR(15)

		SET @BRANCH_HS= (SELECT TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_TYPE='HS')

		SET @LIMIT_VALUE=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE  LIMIT_TYPE='PYCMS_DVKD')

		SET @LIMIT_DVCM=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE  LIMIT_TYPE='PYCMS_DVCM')

		SET @TOTAL_AMT=(SELECT TOTAL_AMT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		SET @CURR_PROCESS = (SELECT PROCESS_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		UPDATE  dbo.TR_REQUEST_DOC SET AUTH_STATUS='A',REQ_TYPE=0, CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CAST(@p_APPROVE_DT AS DATE),BRANCH_DVMS=BRANCH_CREATE, PROCESS_ID='DMMS' WHERE REQ_ID=@p_REQ_ID

	--	SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))

		SET @BRANCH_CREATE=(SELECT BRANCH_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		SET @DEP_CREATE=(SELECT DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

		--IF(@BRANCH_CREATE_TYPE='PGD')

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

IF(EXISTS (SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS'))

BEGIN

    IF(@TOTAL_AMT <= @LIMIT_DVCM)

	BEGIN

				SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT 

														LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID

	END

	ELSE

	BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

	END

END

ELSE

BEGIN

	IF(EXISTS(	SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD 

		LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID

		LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID

		WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID))

	BEGIN

		    IF(@TOTAL_AMT <= @LIMIT_DVCM)

			BEGIN

				SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT 

														LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID

			END

			ELSE

			BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

			END

	END

	ELSE

	BEGIN

		IF(@TOTAL_AMT<=@LIMIT_VALUE)

			BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID

			END

			ELSE

			BEGIN

				SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

				UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

			END

	END

END

-- LUCTV 08122020 KIỂM TRA NẾU TỜ TRÌNH LINK TỚI PYCMS LÀ TỜ TRÌNH TOÀN HÀNG THÌ ĐMMS GIAO CHO ĐƠN VỊ TỰ MUA SẮM

IF(@ISCHECK_ALL ='1')

BEGIN

	SET @DMMS_ID= (SELECT TOP 1 DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)

	UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID

END

IF(@TOTAL_AMT > 20000000)

BEGIN

	SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

	UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

END

-- LUCTV 08122020

	--IF(EXISTS(	SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD 

	--	LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID

	--	LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID

	--	WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID)  OR @BRANCH_CREATE=@BRANCH_HS)

	--	BEGIN

	--		IF(@TOTAL_AMT<=@LIMIT_VALUE AND NOT EXISTS(SELECT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT 

	--													LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID

	--					GROUP BY HC.DVCM_ID HAVING COUNT(REQDT_ID)>1))

	--		BEGIN

	--			SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT 

	--													LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)

	--			UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID

	--		END

	--		ELSE

	--		BEGIN

	--			SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

	--			UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

	--		END

	--	END

	--	ELSE

	--	BEGIN

	--		IF(@TOTAL_AMT<=@LIMIT_VALUE)

	--		BEGIN

	--			SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)

	--			UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID

	--		END

	--		ELSE

	--		BEGIN

	--			SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)

	--			UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID

	--		END

	--	END

		INSERT INTO dbo.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,

			IS_HAS_CHILD,

			DEP_ID

		)

		VALUES

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

			'APPNEW',        -- PROCESS_ID - varchar(10)

			'P',        -- STATUS - varchar(5)

			'GDDV',        -- ROLE_USER - varchar(50)

			@BRANCH_CREATE,        -- BRANCH_ID - varchar(15)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			GETDATE(), -- APPROVE_DT - datetime

			'',        -- PARENT_PROCESS_ID - varchar(10)

			'',        -- IS_LEAF - varchar(1)

			'',        -- COST_ID - varchar(15)

			'',        -- DVDM_ID - varchar(15)

			N'Trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)

			0 ,      -- IS_HAS_CHILD - bit

			@DEP_CREATE

			)

			INSERT INTO dbo.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,

			IS_HAS_CHILD

		)

		VALUES

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

			'DMMS',        -- PROCESS_ID - varchar(10)

			'C',        -- STATUS - varchar(5)

			'GDDV',        -- ROLE_USER - varchar(50)

			'',        -- BRANCH_ID - varchar(15)

			'',        -- CHECKER_ID - varchar(15)

			GETDATE(), -- APPROVE_DT - datetime

			'APPNEW',        -- PARENT_PROCESS_ID - varchar(10)

			'',        -- IS_LEAF - varchar(1)

			'',        -- COST_ID - varchar(15)

			'',        -- DVDM_ID - varchar(15)

			N'Chờ đầu mối mua sắm xử lý',       -- NOTES - nvarchar(500)

			1       -- IS_HAS_CHILD - bit

		 )

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

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

			'APPNEW',        -- PROCESS_ID - varchar(10)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			@p_APPROVE_DT , -- APPROVE_DT - datetime

			@p_PROCESS_DES,

			 N'Trưởng đơn vị phê duyệt'        -- PROCESS_DESC - nvarchar(1000)

		)

	END

	IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))

BEGIN

	SELECT '0' as Result, '' ErrorDesc

	RETURN '0'

END

ELSE

BEGIN

	SELECT '4' as Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đã được đợi cấp phê duyệt trung gian xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt' ErrorDesc

	RETURN '4'

END

ABORT:

BEGIN

		PRINT 'ERROR'

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

End

¿

ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Ins]

@p_REQ_PAY_ID	varchar(15)= NULL,

@p_REQ_PAY_CODE	varchar(50)	= NULL,

@p_REQ_DT VARCHAR(10)= NULL,

@p_BRANCH_ID	varchar(15)	= NULL,

@p_DEP_ID	varchar(15)	= NULL,

@p_REQ_REASON	nvarchar(MAX)	= NULL,

@p_REQ_TYPE	varchar(15)	= NULL,

@P_REQ_ENTRIES nvarchar(MAX)	= NULL,

@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,

@p_REF_ID	varchar(15)	= NULL,

@p_RECEIVER_PO	nvarchar(250)	= NULL,

@p_RECEIVER_DEBIT	nvarchar(250)	= NULL,

@p_REQ_PAY_TYPE	varchar(15)	= NULL,

@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,

@p_REQ_AMT	decimal(18, 0)	= NULL,

@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,

@p_MAKER_ID	varchar(15)	= NULL,

@p_CREATE_DT	varchar(25)	= NULL,

@p_EDITOR_ID	varchar(15)	= NULL,

@p_AUTH_STATUS	varchar(1)	= NULL,

@p_CHECKER_ID	varchar(15)	= NULL,

@p_APPROVE_DT	varchar(25)	= NULL,

@p_CREATE_DT_KT	varchar(25)	= NULL,

@p_MAKER_ID_KT	varchar(15)	= NULL,

@p_AUTH_STATUS_KT	varchar(1)	= NULL,

@p_CHECKER_ID_KT	varchar(1)	= NULL,

@p_APPROVE_DT_KT  varchar(25)= null,

@p_CONFIRM_NOTE	nvarchar(500)	= NULL,

@p_BRANCH_CREATE	varchar(15)	= NULL,

@p_NOTES	varchar(15)	= NULL,

@p_RECORD_STATUS	varchar(1)	= NULL,

@p_TRANSFER_MAKER	nvarchar(50)	= NULL,

@p_TRANSFER_DT	varchar(25)	= NULL,

@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,

@p_PROCESS	varchar(15)	= NULL,

@p_PAY_PHASE VARCHAR(15)= NULL,

@p_DVDM_ID VARCHAR(15) = NULL,

@p_RATE DECIMAL(18,0)= NULL,

@p_RECIVER_MONEY VARCHAR(15) = NULL,

@p_IS_PERIOD VARCHAR(5) = NULL,

@p_TYPE_FUNCTION VARCHAR(15) = NULL,

@p_XMP_TEMP XML = NULL

AS

--Validation is here

/*

DECLARE @ERRORSYS NVARCHAR(15) = '' 

  IF ( NOT EXISTS ( SELECT * FROM ASS_COLLECT WHERE ))

	 SET @ERRORSYS = ''

IF @ERRORSYS <> '' 

BEGIN

	SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

	RETURN '0'

END 

*/

	--DECLARE @ERRORSYS NVARCHAR(15) = '' 

	--IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE REF_ID  = @p_REF_ID )

	--BEGIN

	--	SET @ERRORSYS = 'ASSC-00005'

	--	SELECT ErrorCode Result, '' COLLECT_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

	--	RETURN '0'

	--END

	IF(@p_REQ_TYPE ='I')

	BEGIN

		DECLARE @AMT_AD_PAY DECIMAL(18,0) =0

		DECLARE @AMT_AD_PAY_BACK DECIMAL(18,0) =0

		DECLARE @AMT_AD_PAY_REMAIN DECIMAL(18,0) =0

		SET @AMT_AD_PAY = (SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE REF_ID=@p_MAKER_ID AND AUTH_STATUS_KT='A' AND REQ_TYPE ='I')

		SET @AMT_AD_PAY_BACK = (SELECT SUM(D.AMT_USE) +SUM(D.AMT_REVERT) - SUM (D.AMT_ADD)

					FROM TR_REQ_PAYMENT_DT D

					LEFT JOIN TR_REQ_ADVANCE_PAYMENT X ON D.PAY_ADV_ID = X.REQ_PAY_ID  AND D.AUTH_STATUS_KT ='A'

					WHERE X.REF_ID =@p_REF_ID)

	 SET @AMT_AD_PAY_REMAIN =ISNULL(@AMT_AD_PAY,0) -ISNULL(@AMT_AD_PAY_BACK,0)

	 SET @p_REQ_TEMP_AMT =ISNULL(@AMT_AD_PAY_REMAIN,0)

	 END

BEGIN TRANSACTION

	-- LUCTV 17H51 BO SUNG LAY SO TIEN DA TAM UNG CUA NHAN VIEN --

		--SET @AMT_DO =(SELECT * FROM [dbo].TR_RE)

	--THIEUVQ 281119---

		DECLARE @REC_DEBIT_AUTO VARCHAR(15),@ROLE_KI_NHAY VARCHAR(50)

		SET @REC_DEBIT_AUTO =(SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID = @p_REF_ID)

		--IF (@p_REQ_TYPE='I')

		--BEGIN

		--	--SET @p_REF_ID = @p_MAKER_ID

		--	--IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM = @p_RECEIVER_DEBIT))

		--	--BEGIN

		--	--	SET @p_RECEIVER_DEBIT = @p_RECEIVER_DEBIT+''

		--	--END

		--	--ELSE

		--	--BEGIN

		--	--	IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM = @p_RECEIVER_DEBIT AND REF_ID != @p_REF_ID ) AND @p_TYPE_FUNCTION ='SEND')

		--	--	BEGIN

		--	--		SET @p_RECEIVER_DEBIT =(SELECT ACC_NUM FROM CM_ACCOUNT_PAY WHERE ACC_NUM = @p_RECEIVER_DEBIT)

		--	--	END

		--	--END

		--	IF(@p_RECEIVER_DEBIT IS NOT NULL AND @p_RECEIVER_DEBIT <>'')

		--	BEGIN

		--		IF(@REC_DEBIT_AUTO IS NOT NULL AND @REC_DEBIT_AUTO <>''  AND @REC_DEBIT_AUTO <> @p_RECEIVER_DEBIT)

		--		BEGIN

		--			SET @p_RECEIVER_DEBIT =@REC_DEBIT_AUTO

		--		END

		--		--IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID <> @p_REF_ID AND ACC_NUM =@p_RECEIVER_DEBIT))

		--		--BEGIN

		--		--	ROLLBACK TRANSACTION

		--		--	SELECT '-1' Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Tài khoản tạm ứng này đã tồn tại và thuộc về nhân viên khác' ErrorDesc

		--		--	RETURN '-1'

		--		--END

		--	END

		--	ELSE

		--	BEGIN

		--		SET @p_RECEIVER_DEBIT =(SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID) 

		--	END

		--END

		EXEC [dbo].[ADVANCE_CODE_GenKey] 'TR_REQ_ADVANCE_PAYMENT', '',@p_REQ_TYPE, @p_REQ_PAY_CODE out

	--END--

		IF @p_REQ_PAY_CODE IS NULL OR @p_REQ_PAY_CODE =''

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Mã số phiếu không được để trống' ErrorDesc

			RETURN '-1'

		END

		IF (@p_REQ_TYPE='I' AND (@p_RECEIVER_DEBIT IS NULL OR @p_RECEIVER_DEBIT ='') AND @p_TYPE_FUNCTION ='SEND')

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Tài khoản nhân viên tạm ứng không được để trống' ErrorDesc

			RETURN '-1'

		END

		IF EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE  = @p_REQ_PAY_CODE )

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Số phiếu tạm ứng đã tồn tại trong hệ thống' ErrorDesc

			RETURN '-1'

		END

		IF(LEN(ISNULL(@p_RECEIVER_DEBIT,''))>15 AND @p_REQ_TYPE ='I')

		BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Tài khoản tạm ứng này không được vượt quá 15 kí tự' ErrorDesc

				RETURN '-1'

		END

		-- START 19-11-2019

		-- LUCTV BO SUNG VALIDATE TRONG QUA TRINH TEST SAU UAT- KIEM TRA NEU DOT THANH TOAN CHUA CO THI KHONG DUOC THEM MOI

		--IF(@p_REQ_TYPE='P' AND (@p_PAY_PHASE IS NULL OR @p_PAY_PHASE =''))

		--BEGIN

		--	ROLLBACK TRANSACTION

		--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Đợt thanh toán không được phép để trống' ErrorDesc

		--	RETURN '-1'

		--END

		---- NEU THANH TOAN XU LY NO THI NHAN VIEN NHAN TIEN TAM UNG KHONG DUOC PHEP TRONG

		--IF(@p_REQ_TYPE='D' AND (@p_RECEIVER_DEBIT IS NULL OR @p_RECEIVER_DEBIT =''))

		--BEGIN

		--	ROLLBACK TRANSACTION

		--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Nhân viên nhận tạm ứng không được phép để trống' ErrorDesc

		--	RETURN '-1'

		--END

		-- SO TIEN THANH TOÁN PHAI LON HON KHONG

		IF(@p_REQ_AMT <=0)

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Số tiền tạm ứng phải lớn hơn 0' ErrorDesc

			RETURN '-1'

		END

		-----

		---- KIEM TRA NEU CHUA CHON KHOI PHE DUYET THI TU DONG CHUYEN VÊ KHOI TAI CHINH

		--IF(@p_DVDM_ID IS NULL OR @p_DVDM_ID ='')

		--BEGIN

		--	SET @p_DVDM_ID = (SELECT TOP 1 DVDM_ID FROM CM_DVDM WHERE DVDM_NAME ='KHOI TAI CHINH')

		--END

		--

		--KIEM TRA NEU TAI KHOAN NHAN VIEN TAM UNG NEU CHUA TON TAI TRONG HE THONG THI BAO LOI

		--IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID) AND @p_REQ_TYPE='I')

		--BEGIN

		--	INSERT INTO CM_ACCOUNT_PAY (REF_ID,ACC_TYPE,ACC_NUM,ACC_NAME,FUNTION,MAKER_ID,AUTH_STATUS,CREATE_DT,TK_GL,TK_GL_NAME) VALUES (@p_REF_ID,'ADV_PAY',@p_RECEIVER_DEBIT,'TK TAM UNG CUA NHAN VIEN ' +@p_REF_ID,'I',@p_MAKER_ID,'U',GETDATE(),'361200001','TAM UNG DE HOAT DONG NGHIEP VU')

		--END

		--ELSE

		--BEGIN

		--	IF(((SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID)<>@p_RECEIVER_DEBIT) AND @p_REQ_TYPE='I')

		--	BEGIN

		--		ROLLBACK TRANSACTION

		--		SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE, N'Tài khoản tạm ứng của nhân viên đã tồn tại trong hệ thống! Bạn không được phép chỉnh sửa' ErrorDesc

		--		RETURN '-1'

		--	END

		--END

		--SET @ROLE_KI_NHAY =(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE)

		--IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <> '' AND @ROLE_KI_NHAY IN ('TP','GDDV','KTT','TPTC','TC'))

		--BEGIN

		--	ROLLBACK TRANSACTION

		--	SELECT '-1' Result, '' REQ_PAY_ID, N'Bạn không được phép chọn cấp trưởng đơn vị làm cấp phê duyệt trung gian. Vui lòng bỏ qua hoặc chọn cấp phó phòng, trưởng bộ phận' ErrorDesc

		--	RETURN '-1'

		--END

		--DECLARE @ROLE_KI_NHAY VARCHAR(50)

		IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@p_RECEIVER_DEBIT AND REF_ID =  @p_REF_ID) AND @p_REQ_TYPE='I')

		BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng số '+ @p_RECEIVER_DEBIT + N' không phải tài khoản của user: '+@p_REF_ID  ErrorDesc

				RETURN '-1'

		END

		SET  @ROLE_KI_NHAY =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_TRASFER_USER_RECIVE)

		IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <>'' AND @ROLE_KI_NHAY IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC'))

		BEGIN

		PRINT @ROLE_KI_NHAY

		END

		ELSE

		BEGIN

		SET @ROLE_KI_NHAY =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE)

		IF(@ROLE_KI_NHAY IS NULL OR @ROLE_KI_NHAY ='')

		BEGIN

				SET @ROLE_KI_NHAY =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_TRASFER_USER_RECIVE))

		END

		END

		--SET @ROLE_KI_NHAY =(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE)

		IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <> '' AND @ROLE_KI_NHAY IN ('TP','GDDV','KTT','TPTC','TC','TPGD') AND @p_REQ_TYPE <> 'I')

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' Result, '' REQ_PAY_ID, N'Bạn không được phép chọn cấp trưởng đơn vị làm cấp phê duyệt trung gian. Vui lòng bỏ qua hoặc chọn cấp phó phòng, trưởng bộ phận' ErrorDesc

			RETURN '-1'

		END

		EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_PAMENT', @p_REQ_PAY_ID out

		IF @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL GOTO ABORT

		INSERT INTO [dbo].[TR_REQ_ADVANCE_PAYMENT]

           ([REQ_PAY_ID]

           ,[REQ_PAY_CODE]

           ,[BRANCH_ID],[REQ_DT],

           [DEP_ID]

           ,[REQ_REASON]

           ,[REQ_TYPE],REQ_ENTRIES,

            [REQ_DESCRIPTION]

           ,REF_ID,

			RECEIVER_PO, RECEIVER_DEBIT

           ,[REQ_PAY_TYPE]

           ,[REQ_TYPE_CURRENCY]

           ,[REQ_AMT]

           ,[REQ_TEMP_AMT]

           ,[MAKER_ID]

           ,[CREATE_DT]

           ,[EDITOR_ID]

           ,[AUTH_STATUS]

           ,[CHECKER_ID]

           ,[APPROVE_DT]

           ,[CREATE_DT_KT]

           ,[MAKER_ID_KT]

           ,[AUTH_STATUS_KT]

           ,[CHECKER_ID_KT]

		   ,[APPROVE_DT_KT]

           ,[CONFIRM_NOTES]

           ,[BRANCH_CREATE]

           ,[NOTES],RECORD_STATUS,TRANSFER_MAKER,TRANSFER_DT,TRASFER_USER_RECIVE,PROCESS,PAY_PHASE,DVDM_ID,RATE,RECIVER_MONEY,IS_PERIOD)

			VALUES

           (@p_REQ_PAY_ID,

			@p_REQ_PAY_CODE,

			@p_BRANCH_ID,CONVERT(DATE,GETDATE(),103),

			@p_DEP_ID,

			@p_REQ_REASON,

			@p_REQ_TYPE,

			@P_REQ_ENTRIES,

			@p_REQ_DESCRIPTION,

			@p_REF_ID,

			@p_RECEIVER_PO,

			@p_RECEIVER_DEBIT,

			@p_REQ_PAY_TYPE,

			@p_REQ_TYPE_CURRENCY,

			@p_REQ_AMT,

			@p_REQ_TEMP_AMT,

			@p_MAKER_ID,

			GETDATE(),

			@p_EDITOR_ID,

			'E',

			NULL,

			NULL,

			NULL,

			NULL,

			NULL,

			NULL,

			NULL,

			NULL,

			@p_BRANCH_CREATE,

			@p_NOTES,'1',

			@p_TRANSFER_MAKER,

			NULL,

			@p_TRASFER_USER_RECIVE,

			@p_PROCESS,@p_PAY_PHASE,@p_DVDM_ID,@p_RATE,@p_RECIVER_MONEY,@p_IS_PERIOD)

			IF @@Error <> 0 GOTO ABORT

		--- INSERT VÀO BẢNG DETAIL NÊU TẠM ỨNG LÀ TẠM ỨNG THANH TOÁN

			DECLARE @hdoc INT

			EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;

			-- KIEM TRA NEU TAM UNG THANH TOAN

			IF((@p_REQ_TYPE='P' AND (@p_IS_PERIOD <>'Y' OR @p_IS_PERIOD IS NULL)) OR @p_REQ_TYPE='D')

			BEGIN

				DECLARE @PO_ID varchar(15),@TERM_PAY varchar(15),@AMT_PAY_SCHEDULE decimal(18,0),@REQ_ADV_ID_SCHE VARCHAR(15),@AMT_ADVANCE_SCHEDULE decimal(18,0),@AMT_PAY_DO decimal(18,2),

				@AMT_REMAIN_SCHEDULE decimal(18,0),@REQ_AD_DT varchar(20),@PROCESS varchar(15),@PAY_ID VARCHAR(15),

				@REF_ID VARCHAR(15),@REQ_PAY_DESC NVARCHAR(500), @REQ_PAY_ENTRIES NVARCHAR(250),@CURRENCY VARCHAR(15),@RATE DECIMAL(18,0),@CUST_ID VARCHAR(15), @EMP_ID VARCHAR(15),

				@AMT_PAY_REAL DECIMAL(18,2), @IS_CLOSED VARCHAR(1)= NULL,@INDEX_NS INT =0,@REASON NVARCHAR(1000), @REF_TYPE VARCHAR(15)

				DECLARE XmlDataPO CURSOR FOR

				SELECT *

				FROM

				OPENXML(@hdoc, '/Root/XmlDataPO', 2)

				WITH(REF_ID varchar(15),IS_CLOSED VARCHAR(1),REF_TYPE VARCHAR(15))

				OPEN XmlDataPO;

				DECLARE @INDEX_PO INT =0

				SET @INDEX_PO = 0

				FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE

				WHILE @@fetch_status=0 

				BEGIN

					SET @INDEX_PO = @INDEX_PO +1

					-- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH

					--IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID))

					--BEGIN

					--	ROLLBACK TRANSACTION

					--	SELECT '-1' as Result, '' REQ_PAY_ID, ''  AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO/ hợp đồng số '+(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc

					--	RETURN '-1'

					--END

					---- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH

					--IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL)))

					--BEGIN

					--	ROLLBACK TRANSACTION

					--	SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO/ hợp đồng số '+(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc

					--	RETURN '-1'

					--END

					-- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH

					-- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE

					IF(@p_TYPE_FUNCTION ='SEND')

					BEGIN

					IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='C' AND REQ_PAY_ID 

					IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL)))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID, ''  AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': Hợp đồng số '+

						(SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc

						RETURN '-1'

					END

					-- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH

					IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='C' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': Hợp đồng số '+

						(SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc

						RETURN '-1'

					END

					-- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH

					IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REQ_PAY_ID 

					IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL)))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID, ''  AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+

						(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc

						RETURN '-1'

					END

					-- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH

					IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+

						(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc

						RETURN '-1'

					END

					END

					DECLARE @REQ_PAYDTID VARCHAR(15);

					EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID OUT;

					IF @REQ_PAYDTID='' OR @REQ_PAYDTID IS NULL GOTO ABORT;

					INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID,REQ_PAY_ID,REF_ID,TYPE,MAKER_ID,CREATE_DT,IS_CLOSED,REF_TYPE) VALUES

					(@REQ_PAYDTID,@p_REQ_PAY_ID,@REF_ID,'ADV_PAY',@p_MAKER_ID,GETDATE(),@IS_CLOSED,@REF_TYPE)

				IF @@error<>0 GOTO ABORT;

				FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE

				END

				CLOSE XmlDataPO;

				DEALLOCATE XmlDataPO;

				--- CUSOR DANH SACH LICH THANH TOAN PO - HD	

				DECLARE XmlDataSchedule CURSOR FOR

				SELECT *

				FROM

				OPENXML(@hdoc, 'Root/XmlDataSchedule',2)

				WITH(PO_ID varchar(15),TERM_PAY varchar(15),AMT_PAY_SCHEDULE decimal(18,0),REQ_ADV_ID varchar(15),AMT_ADVANCE_SCHEDULE decimal(18,0), AMT_PAY_DO decimal(18,0),

				AMT_REMAIN_SCHEDULE decimal(18,0),REQ_AD_DT varchar(20),PROCESS varchar(15), PAY_ID VARCHAR(15),REF_ID VARCHAR(15),

				REQ_PAY_DESC NVARCHAR(500), REQ_PAY_ENTRIES NVARCHAR(250),CURRENCY VARCHAR(15),RATE DECIMAL(18,0),AMT_PAY_REAL DECIMAL(18,2))

				OPEN XmlDataSchedule

				FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO,

				@AMT_REMAIN_SCHEDULE,@REQ_AD_DT,@PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL

				WHILE @@fetch_status=0 

				BEGIN

					DECLARE @p_REQ_PAY_SCHEDULE_ID VARCHAR(15);

					EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_SCHEDULE', @p_REQ_PAY_SCHEDULE_ID OUT;

					IF @p_REQ_PAY_SCHEDULE_ID='' OR @p_REQ_PAY_SCHEDULE_ID IS NULL GOTO ABORT;

					INSERT INTO TR_REQ_PAY_SCHEDULE(

					SCHEDULE_ID,REQ_PAY_ID,PAY_ID,AMT_PAY,PAY_PHASE,REQ_ADV_ID,AMT_ADVANCE,AMT_PAY_DO,AMT_REMAIN,REQ_PAY_DT,REQ_AD_DT,PROCESS,MAKER_ID,

					CREATE_DT,AUTH_STATUS_KT,PAYMENT_STATUS,TRN_TYPE,REF_ID,REQ_PAY_DESC,REQ_PAY_ENTRIES,CURRENCY,RATE,AMT_PAY_REAL)

					VALUES (@p_REQ_PAY_SCHEDULE_ID,@p_REQ_PAY_ID,@PAY_ID,@AMT_PAY_SCHEDULE,@TERM_PAY,@p_REQ_PAY_ID,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO,@AMT_REMAIN_SCHEDULE,

					GETDATE(),CONVERT(DATETIME,@REQ_AD_DT,103),NULL,@p_MAKER_ID,GETDATE(),'U','','ADV_PAY',@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL)

				--- END KHAI BAO CURSOR

				IF @@error<>0 GOTO ABORT;

				FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO,

				@AMT_REMAIN_SCHEDULE,@REQ_AD_DT,@PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL

				END

				CLOSE XmlDataSchedule;

				DEALLOCATE XmlDataSchedule;

			END

		--- END TẠM ỨNG THANH TOÁN

		--- TẠM ỨNG HĐ ĐỊNH KỲ

		IF(@p_REQ_TYPE = 'P' AND @p_IS_PERIOD='Y')

		BEGIN

				DECLARE XmlDataPO CURSOR FOR

				SELECT *

				FROM

				OPENXML(@hdoc, '/Root/XmlDataPO', 2)

				WITH(REF_ID varchar(15),IS_CLOSED VARCHAR(1),REF_TYPE VARCHAR(15))

				OPEN XmlDataPO;

				SET @INDEX_PO = 0

				FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE

				WHILE @@fetch_status=0 

				BEGIN

					SET @INDEX_PO = @INDEX_PO +1

					DECLARE @REQ_PAYDTID_C VARCHAR(15);

					EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID_C OUT;

					IF @REQ_PAYDTID_C='' OR @REQ_PAYDTID_C IS NULL GOTO ABORT;

					INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID,REQ_PAY_ID,REF_ID,TYPE,MAKER_ID,CREATE_DT,IS_CLOSED,REF_TYPE) VALUES

					(@REQ_PAYDTID_C,@p_REQ_PAY_ID,@REF_ID,'ADV_PAY',@p_MAKER_ID,GETDATE(),@IS_CLOSED,@REF_TYPE)

				IF @@error<>0 GOTO ABORT;

				FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE

				END

				CLOSE XmlDataPO;

				DEALLOCATE XmlDataPO;

				--- CUSOR DANH SACH LICH THANH TOAN PO - HD	

			----------------------------

			--INSERT FROM PERIOD	

				DECLARE XmlDataPeriod CURSOR FOR

				SELECT *

				FROM

				OPENXML(@hdoc, '/Root/XmlDataPeriod', 2)

				WITH(CONTRACT_ID varchar(15),PAY_DT_REAL VARCHAR(20),PAY_TYPE VARCHAR(5),

				OLD_INDEX DECIMAL(18,0),NEW_INDEX DECIMAL(18,0),AMT_PAY DECIMAL(18,2),CURRENCY VARCHAR(5), RATE DECIMAL(18,0), FROM_DATE VARCHAR(20), TO_DATE VARCHAR(20),PAY_PHASE NVARCHAR(250),REASON NVARCHAR(2000))

				OPEN XmlDataPeriod;

				DECLARE @CONTRACT_ID varchar(15),@PAY_DT_REAL VARCHAR(20),@PAY_TYPE VARCHAR(5),

				@OLD_INDEX DECIMAL(18,0),@NEW_INDEX DECIMAL(18,0),@AMT_PAY_PERIOD DECIMAL(18,2),@FROM_DATE VARCHAR(20),@TO_DATE VARCHAR(20),@PAY_PHASE NVARCHAR(250),@REASON_TTDK NVARCHAR(2000)

				FETCH NEXT FROM XmlDataPeriod INTO @CONTRACT_ID ,@PAY_DT_REAL ,@PAY_TYPE ,

				@OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,@CURRENCY, @RATE,@FROM_DATE,@TO_DATE,@PAY_PHASE,@REASON_TTDK

				WHILE @@fetch_status=0 

				BEGIN

					-- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE

					IF(@p_TYPE_FUNCTION ='SEND')

					BEGIN

						IF(EXISTS(SELECT CONTRACT_ID 

						FROM TR_CONTRACT 

						WHERE CONTRACT_ID = @REF_ID

						AND IS_CLOSED='Y' ))

						BEGIN

							ROLLBACK TRANSACTION

							SELECT '-1' as Result, '' REQ_PAY_ID, ''  AS REQ_PAY_CODE,N'Tạm ứng hợp đồng định kì đã đóng' ErrorDesc

							RETURN '-1'

						END

					IF(@NEW_INDEX <=@OLD_INDEX AND @NEW_INDEX >0 AND @OLD_INDEX >0  )

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, @p_REQ_PAY_ID  REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Chỉ số mới phải lớn hơn chỉ số cũ' ErrorDesc

						RETURN '-1'

					END

					END

					DECLARE @PERIOD_ID VARCHAR(15);

					EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_PERIOD', @PERIOD_ID OUT;

					IF @PERIOD_ID='' OR @PERIOD_ID IS NULL GOTO ABORT;

					INSERT INTO TR_REQ_PAY_PERIOD(PERIOD_ID,REQ_PAY_ID,PAY_TYPE, CONTRACT_ID,PAY_DT_REAL,OLD_INDEX,NEW_INDEX,AMT_PAY,AUTH_STATUS_KT,CURRENCY,RATE,FROM_DATE, TO_DATE,AD_PAY_ID,PROCESS,PAY_PHASE,REASON)

					VALUES (@PERIOD_ID,@p_REQ_PAY_ID,'ADV_PAY',@CONTRACT_ID,CONVERT(DATE,@PAY_DT_REAL,103),

				@OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,'U',@CURRENCY, @RATE,CONVERT(DATE,@FROM_DATE,103),CONVERT(DATE,@TO_DATE,103),@p_REQ_PAY_ID,'0',@PAY_PHASE,@REASON_TTDK)

			IF @@error<>0 GOTO ABORT;

			FETCH NEXT FROM XmlDataPeriod INTO @CONTRACT_ID ,@PAY_DT_REAL ,@PAY_TYPE ,

				@OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,@CURRENCY, @RATE,@FROM_DATE,@TO_DATE,@PAY_PHASE,@REASON_TTDK

			END

			CLOSE XmlDataPeriod;

			DEALLOCATE XmlDataPeriod;

			-- VALIDATE SO TIEN

			--IF(@p_REQ_AMT >= @TOTAL_SCHEDULE_AMT)

			--BEGIN

			--	ROLLBACK TRANSACTION

			--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Số tiền thanh toán phải nhỏ hơn hoặc bằng tổng số tiền còn lại cần thanh toán: '+FORMAT(SUM(@TOTAL_SCHEDULE_AMT),'#,#', 'vi-VN') ErrorDesc

			--	RETURN '-1'

			--END

			----

		END

		---- END TẠM ỨNG HỢP ĐỒNG ĐỊNH KÌ

		--- INSERT PHƯƠNG THỨC THANH TOÁN

		----MethodCursor

			DECLARE @RECEIVE_ID varchar(15),@RECEIVE_NAME nvarchar(100),@REQ_PAY_REASON nvarchar(MAX),@TOTAL_AMT_METHOD decimal(18,2),@REQ_PAY_TYPE varchar(1),

					@ACC_NO VARCHAR(50), @ACC_NAME NVARCHAR(200), @ISSED_BY NVARCHAR(250), @ISSUED_DT VARCHAR(20),@CHECK_IN VARCHAR(15)

			DECLARE XmlDataMethod CURSOR FOR

			SELECT *

			FROM

			OPENXML(@hdoc, 'Root/XmlDataMethod',2)

			WITH(RECEIVE_ID varchar(15),RECEIVE_NAME nvarchar(100),REQ_PAY_REASON nvarchar(MAX),TOTAL_AMT decimal(18,2),REQ_PAY_TYPE varchar(1),

			REQ_PAY_DESC nvarchar(MAX),REQ_PAY_ENTRIES nvarchar(MAX),ACC_NO VARCHAR(50), 

			ACC_NAME NVARCHAR(200), ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15),RATE DECIMAL(18,0),CHECK_IN VARCHAR(15))

			OPEN XmlDataMethod

			FETCH NEXT FROM XmlDataMethod 

			INTO  @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO,@ACC_NAME ,@ISSED_BY , @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN

			WHILE @@fetch_status=0 

			BEGIN

				IF(@REQ_PAY_TYPE <>'1')

				BEGIN

					SET @ISSUED_DT = NULL

				END

				DECLARE @p_REQ_PAY_METHOD_ID VARCHAR(15);

				EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_METHOD', @p_REQ_PAY_METHOD_ID OUT;

				IF @p_REQ_PAY_METHOD_ID='' OR @p_REQ_PAY_METHOD_ID IS NULL GOTO ABORT;

				INSERT INTO TR_REQ_PAY_METHOD(METHOD_ID,REQ_PAY_ID,EMP_ID,REQ_PAY_REASON,TOTAL_AMT,REQ_PAY_TYPE,REQ_PAY_DESC,REQ_PAY_ENTRIES, TEMP,MAKER_ID,

				CREATE_DT,ACC_NO,ACC_NAME,ISSUED_BY,ISSUED_DT,CURRENCY,RATE,CHECK_IN)

				VALUES (@p_REQ_PAY_METHOD_ID,@p_REQ_PAY_ID,@RECEIVE_ID,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,'',

				@p_MAKER_ID,GETDATE(),@ACC_NO,@ACC_NAME,@ISSED_BY, CONVERT(DATE, @ISSUED_DT, 103),@CURRENCY,@RATE,@CHECK_IN)

			IF @@error<>0 GOTO ABORT;

			FETCH NEXT FROM XmlDataMethod 

			INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO,@ACC_NAME , @ISSED_BY , @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN

			END

			CLOSE XmlDataMethod;

			DEALLOCATE XmlDataMethod

		----END INSERT PHƯƠNG THỨC THANH TOÁN

		----INSERT VAO BANG DS KHACH HANG

			DECLARE XmlDataCus CURSOR FOR

			SELECT *

			FROM

			OPENXML(@hdoc, '/Root/XmlDataCus', 2)

			WITH(CUS_ID varchar(15), EMP_ID VARCHAR(15), AMT_PAY_DO DECIMAL(18,2),CURRENCY VARCHAR(15),RATE DECIMAL(18,0))

			OPEN XmlDataCus;

			FETCH NEXT FROM XmlDataCus INTO @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE

				WHILE @@fetch_status=0 

				BEGIN		

					INSERT INTO TR_REQ_ADVANCE_CUS(REQ_PAY_ID,CUST_ID,EMP_ID,AMT_PAY_DO,CURRENCY,RATE,MAKER_ID,CREATE_DT)

					VALUES (@p_REQ_PAY_ID, @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE,@p_MAKER_ID,GETDATE())

				--- END KHAI BAO CURSOR

				IF @@error<>0 GOTO ABORT;

				FETCH NEXT FROM XmlDataCus INTO  @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE

				END

				CLOSE XmlDataCus;

				DEALLOCATE XmlDataCus;

		----END

			-- HANG MUC CHI PHI VA NGAN SACH

			DECLARE @TRADE_ID varchar(15),@GD_ID varchar(15),@AMT_APP decimal(18,2), @AMT_DO decimal(18,2), @AMT_EXE decimal(18,2),@AMT_REMAIN_GD decimal(18,2)

			DECLARE XmlDataGood CURSOR FOR

			SELECT *

			FROM

			OPENXML(@hdoc, 'Root/XmlDataGood',2) 

			WITH(TRADE_ID varchar(15),GD_ID varchar(15),AMT_APP decimal(18,2),AMT_DO decimal(18,2),AMT_EXE decimal(18,2),AMT_REMAIN decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),REASON NVARCHAR(1000))

			OPEN XmlDataGood

			FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON

			WHILE @@fetch_status=0 BEGIN

				--- KIEM TRA NEU NGAN SACH SU DUNG THUC THE LON HON NGAN SACH CON LAI

				SET @INDEX_NS = @INDEX_NS +1

				-- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE

				--IF(@p_TYPE_FUNCTION ='SEND')

				--BEGIN

				--IF((@AMT_EXE  > @AMT_REMAIN_GD))

				--BEGIN

				--		ROLLBACK TRANSACTION

				--		SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế không được vượt quá ngân sách còn lại.' ErrorDesc

				--		RETURN '-1'

				--END

				--END

				IF(@p_TYPE_FUNCTION ='SEND')

				BEGIN

				IF(ISNULL(@AMT_EXE,0) =0)

				BEGIN

					ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế phải lớn hơn không.' ErrorDesc

						RETURN '-1'

				END

				IF((@AMT_EXE  > @AMT_REMAIN_GD) AND ((SELECT GD_TYPE_ID FROM CM_GOODS WHERE GD_ID =@GD_ID) ='NS'))

				BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế không được vượt quá ngân sách còn lại.' ErrorDesc

						RETURN '-1'

				END		

			END

				DECLARE @p_BUDGET_ID VARCHAR(15);

				EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_BUDGET', @p_BUDGET_ID OUT;

				IF @p_BUDGET_ID='' OR @p_BUDGET_ID IS NULL GOTO ABORT;

				INSERT INTO TR_REQ_PAY_BUDGET(BUDG_ID,GD_ID,REQ_PAY_ID,TRADE_ID,AMT_APP,AMT_DO, AMT_EXE,AMT_REMAIN,MAKER_ID,CREATE_DT,CURRENCY,RATE,REASON) 

				VALUES (@p_BUDGET_ID,@GD_ID,@p_REQ_PAY_ID,@TRADE_ID,@AMT_APP,@AMT_DO, @AMT_EXE,@AMT_REMAIN_GD,@p_MAKER_ID, GETDATE(),@CURRENCY,@RATE,@REASON)

			IF @@error<>0 GOTO ABORT;

			FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON

			END;

		CLOSE XmlDataGood;

		DEALLOCATE XmlDataGood;

		--- END INSERT NGAN SACH