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

ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_KT_Appr]

--Luanlt 2019/17/10 - Sửa params

@p_REQ_PAY_ID	varchar(15)= NULL,

@p_CHECKER_ID_KT	varchar(15)	= NULL

AS

	--Luanlt 2019/10/17 Validate CORE NOTE không được rỗng	

	--IF ((SELECT CORE_NOTE FROM TR_REQUEST_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) IS NULL OR (SELECT CORE_NOTE FROM TR_REQUEST_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) = '')

	--Luanlt 2019/10/29 Sửa lại validate tồn tại diễn giải hạch toán từ REQUEST_ENTRIES_POST

	--IF (NOT EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID))

	--BEGIN

	--	SELECT '-1' Result,'' REQ_PAY_ID,N'Chưa cập nhật thông tin hạch toán' ErrorDesc

	--	RETURN '-1'

	--END

-- BEGIN VALIDATE

	IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE MAKER_ID_KT = @p_CHECKER_ID_KT 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ị thanh toán này! Giao dịch viên không có quyền duyệt phiếu đề nghị thanh toán' ErrorDesc

		RETURN '-1'

	END

	IF (NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_PAY_ID AND TLNAME =@p_CHECKER_ID_KT AND TYPE_JOB = 'KS'))

	BEGIN

		SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị thanh toán này. Phiếu đề nghị thanh toán đang được kiểm soát viên khác xử lý' ErrorDesc

		RETURN '-1'

	END

	IF EXISTS (SELECT * FROM TR_REQ_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ị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_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_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ị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_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_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ị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_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

-- CHAN CUOI

	IF (EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT <> 'P' AND AUTH_STATUS_KT <> 'S' AND REQ_PAY_ID =@p_REQ_PAY_ID))

	BEGIN

		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_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

-- VALIDATE LUOI HACH TOAN

	DECLARE @SUMVAT DECIMAL(18,0) = 0, @INDEX_ENTRIES INT = 0

		DECLARE @SUM3532 DECIMAL(18,0) = 0

		DECLARE @tmp3532 TABLE(

		AMT DECIMAL(18,0),

		ACCT VARCHAR(20)

		)

		SET @SUM3532 = (SELECT ISNULL(SUM(AMT),0) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND ACCT = '353200002')

		SET @SUMVAT = (SELECT ISNULL(SUM(VAT),0) FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND TYPE_FUNC = 'KT')

		IF(@SUM3532 <> @SUMVAT)

		BEGIN

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán dòng ' + CONVERT(VARCHAR(5),@INDEX_ENTRIES) + N': Tổng số tiền thuế GTGT ở lưới hóa đơn đính kèm có VAT không bằng tổng số tiền tài khoản nợ 353200002 ở lưới hạch toán : Tổng tiền thuế GTGT: ' + FORMAT(@SUMVAT,'#,#', 'vi-VN') + N',Tổng tiền tài khoản 3532: ' + FORMAT(@SUM3532,'#,#', 'vi-VN')   ErrorDesc

			RETURN '-1'

		END

		DECLARE  @REQ_PAY_ID varchar(15), @ET_ID varchar(15), @AMT_ET DECIMAL(18,0), @ACC_ET varchar(25), @DRCR_ET varchar(25), @SUM_ET DECIMAL(18,0) = 0

		DECLARE cursorProduct CURSOR LOCAL FOR SELECT REQ_PAY_ID,ENTRY_PAIR,AMT,ACCT,DR_CR FROM TR_REQ_PAY_ENTRIES A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

		Open cursorProduct

		FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID , @ET_ID, @AMT_ET, @ACC_ET , @DRCR_ET

		WHILE @@FETCH_STATUS = 0

		BEGIN

			IF(@DRCR_ET='C' AND EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND ACC_NO =@ACC_ET))

			BEGIN

				SET @SUM_ET =(SELECT SUM(AMT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C' AND ACCT =@ACC_ET)

				IF(ISNULL(@SUM_ET,0) <> (SELECT SUM(TOTAL_AMT) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND ACC_NO =@ACC_ET))

				BEGIN

					SELECT '-1' as Result, '' REQ_PAY_ID, N' Số tiền hạch toán có của tài khoản: '+@ACC_ET + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACC_ET AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###')  ErrorDesc

					RETURN '-1'

				END

			END

			-- KIEM TRA SO TIEN NO CO

			IF (ISNULL((SELECT SUM(AMT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C' AND ENTRY_PAIR =@ET_ID),0) <> ISNULL((SELECT SUM(AMT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D' AND ENTRY_PAIR =@ET_ID),0))

			BEGIN

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Mã bút toán: ' + @ET_ID + N' Số tiền bút toán Nợ phải bằng với số tiền bút toán Có'  ErrorDesc

				RETURN '-1'

			END

			FETCH NEXT FROM cursorProduct INTO  @REQ_PAY_ID , @ET_ID, @AMT_ET, @ACC_ET , @DRCR_ET

		END

		-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP

		IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

		BEGIN

			DECLARE  @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)

			SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C'),0)

			SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D'),0)

			IF(@SUM_CR <> @SUM_DR)

			BEGIN

				SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có' ErrorDesc

				RETURN '-1'

			END

		END

-- END VALIDATE

BEGIN TRANSACTION

		DECLARE @p_MAKER_ID VARCHAR(15)

		SET @p_MAKER_ID=(SELECT MAKER_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)

		-- CAC BUOC DUYET

		DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT

		SET @LEVEL_JOB =(SELECT TOP 1 LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TYPE_JOB='KS')

		SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1

		---

		DECLARE @INDEX1 INT =0, @INDEX INT =0, @INDEX_AD INT =0

		DECLARE @REQ_TYPE VARCHAR(15),@PAY_ADVANCE_ID VARCHAR(15),@AMT_PAY DECIMAL(18,0)

		DECLARE @PAY_ID VARCHAR(15),@PAY_PHASE VARCHAR(15), @AMT_ADVANCE DECIMAL(18,0),@SCHEDULE_ID VARCHAR(15),@AMT_PAY_REAL DECIMAL(18,2), @AMT_REMAIN DECIMAL(18,2),@REF_ID VARCHAR(15),

		@TRADED_ID VARCHAR(15), @AMT_EXE DECIMAL(18,2), @AMT_EXE_REMAIN DECIMAL(18,2), @AMT_ADD DECIMAL(18,2), @AMT_REVERT DECIMAL(18,2), @AD_PAY_ID VARCHAR(15),@PARENT_ID VARCHAR(15), 

		@REASON_TTDK NVARCHAR(2000), @GD_ID VARCHAR(15)

		SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)

		--- KIEM TRA XEM CO PHAI BUOC DUYET CUOI CUNG CHUA

		IF(@LEVEL_JOB='1')

		BEGIN

		---TRU NGAN SACH 

		DECLARE CURS_TRADE CURSOR FOR SELECT A.TRADE_ID, A.AMT_EXE,A.GD_ID FROM TR_REQ_PAY_BUDGET A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

		OPEN CURS_TRADE

			FETCH NEXT FROM CURS_TRADE INTO @TRADED_ID,@AMT_EXE,@GD_ID

			WHILE @@FETCH_STATUS = 0

			BEGIN

				IF(@REQ_TYPE <> 'P')

				BEGIN

					SET @INDEX = @INDEX +1

					--IF(EXISTS(SELECT * FROM CM_GOODS WHERE GD_ID =@GD_ID AND GD_TYPE_ID ='NS'))

					--BEGIN

					--	SET @AMT_EXE_REMAIN =(SELECT A.AMT_APP - A.AMT_EXE  FROM PL_TRADEDETAIL A WHERE A.TRADE_ID = @TRADED_ID)

					--	 --KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET

					--	IF((@AMT_EXE_REMAIN -@AMT_EXE)<0)

					--	BEGIN

					--		ROLLBACK TRANSACTION

					--		SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Ngân sách sử dụng thực tế đã vượt mức ngân sách sử dụng còn lại: '+ FORMAT(@AMT_EXE_REMAIN,'#,#', 'vi-VN')  ErrorDesc

					--		RETURN '-1'

					--	END

					--END

				END

				UPDATE PL_TRADEDETAIL SET AMT_EXE = ISNULL(AMT_EXE,0) + @AMT_EXE*(SELECT TOP 1 RATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)

				WHERE TRADE_ID =@TRADED_ID

				FETCH NEXT FROM CURS_TRADE INTO @TRADED_ID,@AMT_EXE,@GD_ID

			END

		CLOSE CURS_TRADE

		DEALLOCATE CURS_TRADE

		----

		IF(@REQ_TYPE ='I')

		BEGIN

		DECLARE CURS CURSOR FOR SELECT A.PAY_ADV_ID,A.AMT_USE,A.AMT_ADD,A.AMT_REVERT FROM TR_REQ_PAYMENT_DT A WHERE A.PAY_ID =@p_REQ_PAY_ID

		OPEN CURS

			FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_ADD , @AMT_REVERT

			WHILE @@FETCH_STATUS = 0

			BEGIN

				SET @INDEX_AD = @INDEX_AD +1

				 --KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET

				IF((SELECT A.REQ_AMT - A.PAY_AMT FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_PAY_ID = @PAY_ADVANCE_ID)<=0)

				BEGIN

					ROLLBACK TRANSACTION

					SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_AD)+ N': Phiếu tạm ứng này đã được hoàn tạm ứng xong. Vui lòng xóa khỏi danh sách hoàn tạm ứng' ErrorDesc

					RETURN '-1'

				END

				UPDATE TR_REQ_ADVANCE_PAYMENT

				SET PAY_AMT = ISNULL(PAY_AMT,0)+@AMT_PAY +@AMT_REVERT-@AMT_ADD WHERE REQ_PAY_ID=@PAY_ADVANCE_ID

				-- NEU HOAN UNG PDN TAM UNG HD DINH KI THI CUNG PHAI UPDATE

				--UPDATE TR_REQ_PAY_PERIOD SET PROCESS ='2' WHERE REQ_PAY_ID =@PAY_ADVANCE_ID 

				UPDATE TR_REQ_PAY_SCHEDULE SET PROCESS ='2' WHERE REQ_PAY_ID =@PAY_ADVANCE_ID

				-- UPDATE PDN TAM UNG THANH PROCESS = 2 ==> DA HOAN UNG

				DECLARE @PAY_AMOUNT_I DECIMAL(18,0),@PAY_DT_I DATETIME,@l_CONTRACT_ID_I VARCHAR(15), @OLD_INDEX_I DECIMAL(18, 0), @NEW_INDEX_I DECIMAL(18, 0), @PARENT_ID_I VARCHAR(15),  

				@PAY_PHASE_I VARCHAR(15), @AD_PAY_ID_I VARCHAR(15), @PROCESS_I VARCHAR(15), @REASON_TTDK_I VARCHAR(15)

				DECLARE CURS_PERIOD_I CURSOR FOR SELECT A.AMT_PAY,A.PAY_DT_REAL,A.CONTRACT_ID,A.PAY_PHASE,A.AD_PAY_ID,A.PROCESS,A.PARENT_ID,A.REASON, A.OLD_INDEX, A.NEW_INDEX FROM TR_REQ_PAY_PERIOD A  WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID ORDER BY A.CONTRACT_ID

				OPEN CURS_PERIOD_I

				FETCH NEXT FROM CURS_PERIOD_I INTO @PAY_AMOUNT_I ,@PAY_DT_I ,@l_CONTRACT_ID_I,@PAY_PHASE_I,@AD_PAY_ID_I, @PROCESS_I,@PARENT_ID_I,@REASON_TTDK_I, @OLD_INDEX_I, @NEW_INDEX_I

				WHILE @@FETCH_STATUS = 0

				BEGIN

					UPDATE TR_REQ_PAY_PERIOD SET PROCESS ='2' WHERE (PERIOD_ID =@PARENT_ID_I OR AD_PAY_ID =@PARENT_ID_I) AND PAY_TYPE ='ADV_PAY' 

					AND PAY_PHASE = @PAY_PHASE_I AND CONTRACT_ID = @l_CONTRACT_ID_I AND OLD_INDEX = @OLD_INDEX_I AND NEW_INDEX = @NEW_INDEX_I

				IF @@Error <> 0 GOTO ABORT

				FETCH NEXT FROM CURS_PERIOD_I INTO @PAY_AMOUNT_I ,@PAY_DT_I ,@l_CONTRACT_ID_I,@PAY_PHASE_I,@AD_PAY_ID_I, @PROCESS_I,@PARENT_ID_I,@REASON_TTDK_I, @OLD_INDEX_I, @NEW_INDEX_I

				END

				CLOSE CURS_PERIOD_I

				DEALLOCATE CURS_PERIOD_I

			FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_ADD , @AMT_REVERT

			END

		CLOSE CURS

		DEALLOCATE CURS

		END

		-- KIEM TRA NEU LA THANH TOAN PO

		ELSE IF((@REQ_TYPE ='P' AND ((SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) <>'Y' OR (SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IS NULL )) OR (@REQ_TYPE ='I'))

		BEGIN

			DECLARE @PROCESS VARCHAR(15), @PDN_TAM_UNG VARCHAR(15), @SO_TIEN_TAM_UNG DECIMAL(18,0)

			DECLARE CURS_AD CURSOR FOR SELECT A.SCHEDULE_ID,A.AMT_PAY_DO,A.PAY_ID,A.PAY_PHASE,A.AMT_ADVANCE,A.AMT_PAY_REAL, A.AMT_REMAIN,A.REF_ID,A.PROCESS

			FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

			OPEN CURS_AD

			FETCH NEXT FROM CURS_AD INTO @SCHEDULE_ID,@AMT_PAY,@PAY_ID,@PAY_PHASE,@AMT_ADVANCE,@AMT_PAY_REAL, @AMT_REMAIN,@REF_ID,@PROCESS

			WHILE @@FETCH_STATUS = 0

			BEGIN

				SET @INDEX1 = @INDEX1 +1

				-- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET

				--IF((SELECT MIN(A.AMT_REMAIN) FROM TR_REQ_PAY_SCHEDULE A WHERE A.PAY_ID = @PAY_ID GROUP BY A.PAY_ID )=0)

				--BEGIN

				--	ROLLBACK TRANSACTION

				--	SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX1)+ N':Kì '+ @PAY_PHASE+N' đã thanh toán xong'  ErrorDesc

				--	RETURN '-1'

				--END

					IF(@AMT_ADVANCE >0 AND @AMT_PAY=0)

					BEGIN

						UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTU' WHERE SCHEDULE_ID=@SCHEDULE_ID

					END

					ELSE IF(@AMT_PAY_REAL>0)

					BEGIN

						UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTT' WHERE SCHEDULE_ID=@SCHEDULE_ID

					END

					ELSE IF(@AMT_PAY=0 AND @AMT_ADVANCE =0 AND @AMT_PAY_REAL =0)

					BEGIN

						UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='CTT' WHERE SCHEDULE_ID=@SCHEDULE_ID

					END

					ELSE IF(@AMT_REMAIN =0)

					BEGIN

						UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='TTX' WHERE SCHEDULE_ID=@SCHEDULE_ID

					END

					-- HOAN TAM UNG 100% CHO 1 DOT THANH TOAN PO NAO DO

					IF(@PROCESS ='2')

					BEGIN

						SET @PDN_TAM_UNG = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)

						SET @SO_TIEN_TAM_UNG =(SELECT TOP 1 AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)

						UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT =ISNULL(PAY_AMT,0) + @SO_TIEN_TAM_UNG WHERE REQ_PAY_ID =@PDN_TAM_UNG

						-- CAP NHAT DOT TAM UNG VE TINH TRANG LA DA HOAN TAM UNG

						UPDATE TR_REQ_PAY_SCHEDULE SET PROCESS ='2' WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' AND REQ_PAY_ID=@PDN_TAM_UNG

					END

					IF(@PROCESS ='0')

					BEGIN

						SET @PDN_TAM_UNG = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)

						SET @SO_TIEN_TAM_UNG =(SELECT TOP 1 AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)

						UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT =ISNULL(PAY_AMT,0) + @SO_TIEN_TAM_UNG WHERE REQ_PAY_ID =@PDN_TAM_UNG

						-- CAP NHAT DOT TAM UNG VE TINH TRANG LA DA HOAN TAM UNG

						UPDATE TR_REQ_PAY_SCHEDULE SET PROCESS ='2' WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' AND REQ_PAY_ID=@PDN_TAM_UNG

					END

				--UPDATE TR_REQ_ADVANCE_PAYMENT

				--SET PAY_AMT = ISNULL(PAY_AMT,0)+@AMT_PAY

				FETCH NEXT FROM CURS_AD INTO @SCHEDULE_ID,@AMT_PAY,@PAY_ID,@PAY_PHASE,@AMT_ADVANCE,@AMT_PAY_REAL, @AMT_REMAIN,@REF_ID,@PROCESS

			END

			CLOSE CURS_AD

			DEALLOCATE CURS_AD

			DECLARE  @IS_CLOSED VARCHAR(1)

			DECLARE CURS_PO CURSOR FOR SELECT A.REF_ID, A.IS_CLOSED FROM TR_REQ_ADVANCE_DT A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

			OPEN CURS_PO

			FETCH NEXT FROM CURS_PO INTO  @REF_ID , @IS_CLOSED 

			WHILE @@FETCH_STATUS = 0

			BEGIN -- BAT DAU KIEM TRA DONG PO

				IF(@IS_CLOSED ='Y')

				BEGIN

					IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID =@REF_ID))

					BEGIN

						UPDATE TR_CONTRACT SET IS_CLOSED='Y' WHERE CONTRACT_ID =@REF_ID

					END

					ELSE

					BEGIN

						UPDATE TR_PO_MASTER SET IS_CLOSED='Y' WHERE PO_ID =@REF_ID

					END

				END

				ELSE

				BEGIN

					DECLARE @TOAL_PAY_REAL DECIMAL(18,2) =0,@TOAL_PAY_DK DECIMAL(18,2) =0, @TOTAL_AMT_BACK DECIMAL(18,2)

					-- TONG SO TIEN THANH TOAN VA HOAN UNG

					SET @TOAL_PAY_REAL =(SELECT SUM(AMT_PAY_REAL) FROM TR_REQ_PAY_SCHEDULE WHERE REF_ID=@REF_ID)

					-- @TOTAL_AMT_BACK: TONG SO TIEN CON LAI CAN THANH TOAN

					SET @TOTAL_AMT_BACK =(SELECT SUM(AMT_REMAIN) FROM TR_REQ_PAY_SCHEDULE WHERE REF_ID=@REF_ID AND REQ_ADV_ID IS NOT NULL)

					IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID =@REF_ID))

					BEGIN

						-- @TOAL_PAY_DK: TONG SO TIEN TAT CA KY THANH TOAN

						SET @TOAL_PAY_DK =(SELECT SUM(AMOUNT) FROM TR_CONTRACT_PAYMENT WHERE CONTRACT_ID=@REF_ID)

						IF((@TOAL_PAY_DK-@TOAL_PAY_REAL-@TOTAL_AMT_BACK)=0)

						BEGIN

							UPDATE TR_CONTRACT SET IS_CLOSED='Y' WHERE CONTRACT_ID =@REF_ID

						END

					END

					BEGIN

						SET @TOAL_PAY_DK =(SELECT SUM(AMOUNT) FROM TR_PO_PAYMENT WHERE PO_ID=@REF_ID)

						IF((@TOAL_PAY_DK- @TOAL_PAY_REAL-@TOTAL_AMT_BACK)=0)

						BEGIN

							UPDATE TR_PO_MASTER SET IS_CLOSED='Y' WHERE PO_ID =@REF_ID

						END

					END

				END

				FETCH NEXT FROM CURS_PO INTO  @REF_ID , @IS_CLOSED

			END -- KET THUC KIEM TRA DONG PO

			CLOSE CURS_PO

			DEALLOCATE CURS_PO

		END -- KET THUC KIEM TRA NEU LA THANH TOAN PO

		--- KIEM TRA NEU LA THANH TOAN CAC HOP DONG DINH KI

		ELSE IF((@REQ_TYPE ='P' AND (SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ='Y') OR (@REQ_TYPE ='I'))

		BEGIN

			UPDATE TR_REQ_PAY_PERIOD SET PAY_DT_REAL = GETDATE() WHERE REQ_PAY_ID =@p_REQ_PAY_ID

			DECLARE @PAY_AMOUNT DECIMAL(18,0),@PAY_DT DATETIME,@l_CONTRACT_ID VARCHAR(15), @OLD_INDEX DECIMAL(18, 0), @NEW_INDEX DECIMAL(18, 0)

			DECLARE CURS_PERIOD CURSOR FOR SELECT A.AMT_PAY,A.PAY_DT_REAL,A.CONTRACT_ID,A.PAY_PHASE,A.AD_PAY_ID,A.PROCESS,A.PARENT_ID,A.REASON, A.OLD_INDEX, A.NEW_INDEX FROM TR_REQ_PAY_PERIOD A  WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID ORDER BY A.CONTRACT_ID

			OPEN CURS_PERIOD

			FETCH NEXT FROM CURS_PERIOD INTO @PAY_AMOUNT ,@PAY_DT ,@l_CONTRACT_ID,@PAY_PHASE,@AD_PAY_ID, @PROCESS,@PARENT_ID,@REASON_TTDK, @OLD_INDEX, @NEW_INDEX

			WHILE @@FETCH_STATUS = 0

			BEGIN

				IF(@PROCESS ='1')

				BEGIN

					DECLARE @l_PAY_ID VARCHAR(15)

					EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @l_PAY_ID out

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

					INSERT INTO TR_CONTRACT_PAYMENT([PAY_AMOUNT],[PAY_DT], [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])

					VALUES(@PAY_AMOUNT,CONVERT(DATE,@PAY_DT,103), 

					@l_PAY_ID ,@l_CONTRACT_ID ,@PAY_PHASE ,CONVERT(DATE,@PAY_DT,103),100,@PAY_AMOUNT,'1',@REASON_TTDK ,'1' ,@p_MAKER_ID ,GETDATE() ,'A' ,@p_CHECKER_ID_KT,GETDATE())

				END

				ELSE

				BEGIN

					IF(@PROCESS ='2' AND (@AD_PAY_ID IS NULL OR @AD_PAY_ID =''))

					BEGIN

						ROLLBACK TRANSACTION

						SELECT '-1' Result,'' REQ_PAY_ID,N'Lưới chi tiết thanh toán định kì: Tại hình thức thanh toán Hoàn ứng, số phiếu tạm ứng không được phép để trống' ErrorDesc

						RETURN '-1'

					END

					UPDATE TR_REQ_PAY_PERIOD SET PROCESS ='2' WHERE (PERIOD_ID =@PARENT_ID OR AD_PAY_ID =@PARENT_ID) AND PAY_TYPE ='ADV_PAY' AND PAY_PHASE = @PAY_PHASE AND CONTRACT_ID = @l_CONTRACT_ID AND OLD_INDEX = @OLD_INDEX AND NEW_INDEX = @NEW_INDEX

					UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT = ISNULL(PAY_AMT, 0) + @PAY_AMOUNT WHERE REQ_PAY_ID =@AD_PAY_ID

				END

			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM CURS_PERIOD INTO @PAY_AMOUNT ,@PAY_DT ,@l_CONTRACT_ID,@PAY_PHASE,@AD_PAY_ID, @PROCESS,@PARENT_ID,@REASON_TTDK, @OLD_INDEX, @NEW_INDEX

			END

			CLOSE CURS_PERIOD

			DEALLOCATE CURS_PERIOD

		END

		---END

		UPDATE TR_REQ_PAYMENT

	    SET    AUTH_STATUS_KT='A', CHECKER_ID_KT = @p_CHECKER_ID_KT, APPROVE_DT_KT =  GETDATE()

	    WHERE  REQ_PAY_ID = @p_REQ_PAY_ID

		---UPDATE DETAIL

		UPDATE TR_REQ_PAYMENT_DT SET AUTH_STATUS_KT='A',CHECKER_ID_KT =@p_CHECKER_ID_KT,APPROVE_DT_KT = GETDATE()

		WHERE PAY_ID =@p_REQ_PAY_ID

		-- UPDATE SCHEDULE

		UPDATE TR_REQ_PAY_SCHEDULE SET AUTH_STATUS_KT='A' WHERE REQ_PAY_ID =@p_REQ_PAY_ID

		-- UPDATE PERIOD

		UPDATE TR_REQ_PAY_PERIOD SET AUTH_STATUS_KT='A' WHERE REQ_PAY_ID =@p_REQ_PAY_ID

		-- INSERT VAO PL_PROCESS

	   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_KT,GETDATE(), N'Kiểm soát viên phê duyệt phiếu',N'Kế toán duyệt phiếu')

		IF @@Error <> 0 GOTO ABORT

	   UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TYPE_JOB ='KS' AND LEVEL_JOB =@LEVEL_JOB

			--thieuvq ban vao core - 171219 BEGIN 

			DECLARE @MAKER VARCHAR(15) = (SELECT MAKER_ID_KT FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)

			DECLARE @RES VARCHAR(10)

			--- BAN BUT TOAN VAO CORE

			EXEC [dbo].[PAY_ENTRIES_POST_SetApp] @p_REQ_PAY_ID,@MAKER,@p_CHECKER_ID_KT, @RES OUT 

			--END

		END

		ELSE

		BEGIN

			-- CAP NHAT C CHO CAP CHA

			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_CHECKER_ID_KT

			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)

			SELECT '2' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'Xác nhận phiếu thành công' ErrorDesc

			RETURN '2'

		END

COMMIT TRANSACTION

		SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID ,N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được phê duyệt thành công!' ErrorDesc

		RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

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

		RETURN '-1'

END