CREATE TABLE [dbo].[PL_TRADEDETAIL_PAYMENT](
	[TRADE_ID] [varchar](15) NOT NULL,
	[AMT_ETM] [decimal](18, 2) NULL,
	[CREATE_DT] [datetime] NULL
)
GO


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 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  @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), @IS_PERIOD VARCHAR(15)
		SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
		SET @IS_PERIOD =(SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) ---- 15.04.2023 LUCTV BỔ SUNG KHAI BÁO KIỂM TRA XEM CÓ PHẢI THANH TOÁN CHO CÁC HỢP ĐỒNG ĐỊNH KỲ HAY KHÔNG
		--- KIEM TRA XEM CO PHAI BUOC DUYET CUOI CUNG CHUA
		IF(@LEVEL_JOB='1')
		BEGIN
		---TRU NGAN SACH 
		INSERT INTO PL_TRADE_DETAIL_LOG_PAY SELECT *, GETDATE(), N'Trước khi duyệt kế toán' FROM PL_TRADEDETAIL WHERE TRADE_ID IN (SELECT DISTINCT TRADE_ID FROM tr_req_pay_budget WHERE REQ_PAY_ID = @p_REQ_PAY_ID)

		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
				IF(@REQ_TYPE = 'P' AND ISNULL(@IS_PERIOD,'') ='Y') --- 20062023 LUCTV BỔ SUNG NẾU NHƯ THANH TOÁN CHO CÁC HĐ ĐỊNH KỲ (TỨC KHÔNG LINK TỚI TỜ TRÌNH THÌ  KHI THANH TOÁN SẼ + AMT_ETM)
				BEGIN
					DECLARE @RATE DECIMAL(18,2)
					SET @RATE = (SELECT TOP 1 RATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)

					IF(NOT EXISTS(SELECT * FROM PL_TRADEDETAIL_PAYMENT WHERE TRADE_ID = @TRADED_ID))
					BEGIN
						INSERT INTO PL_TRADEDETAIL_PAYMENT(TRADE_ID, AMT_ETM, CREATE_DT)
						VALUES(@TRADED_ID, @AMT_EXE*@RATE, GETDATE())
					END
					ELSE
					BEGIN
						UPDATE PL_TRADEDETAIL_PAYMENT SET AMT_ETM = ISNULL(AMT_ETM,0) + @AMT_EXE*@RATE WHERE TRADE_ID = @TRADED_ID
					END
				END
				---- END LUCTV 15.04.2023
				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

		INSERT INTO PL_TRADE_DETAIL_LOG_PAY SELECT *, GETDATE(), N'Sau khi duyệt kế toán' FROM PL_TRADEDETAIL WHERE TRADE_ID IN (SELECT DISTINCT TRADE_ID FROM tr_req_pay_budget WHERE REQ_PAY_ID = @p_REQ_PAY_ID)

		----
		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
		-- DOANPTT 291222: THEM LOG KPI
		UPDATE TR_REQ_KPI 
		SET AUTH_STATUS_KT ='A', APPROVE_DT_KT = GETDATE(),CHECKER_ID_KT = @p_CHECKER_ID_KT, REQ_STATUS = 'APP', IS_DONE = '1'
		WHERE REQ_ID = @p_REQ_PAY_ID AND AUTH_STATUS_KT = 'P' AND IS_DONE = '0'
		
		---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

		
		IF((SELECT COUNT(*) FROM PL_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND PROCESS_ID = 'APP' AND PROCESS_DESC = N'Kiểm soát viên phê duyệt phiếu') > 1)
		BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT ISNULL(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 đó. Lỗi này xảy ra khi duyệt 2 lần liên tiếp. Vui lòng liên hệ IT để được hỗ trợ thêm' ErrorDesc
			RETURN '-1'
		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
--23062023_secretkey