ALTER   PROCEDURE [dbo].[TR_REQ_TRANSFER_EXTERNAL_Upd_Status]  
@p_ET_ID varchar(500) = NULL, 
@p_REF_NO VARCHAR(100) = '',--- ID CUA DONG CHUYEN TIEN
@p_TRN_NO VARCHAR(100) = ''---SO REF CUA CORE TRA VE  
AS  
BEGIN TRY  
	DECLARE @l_REF_NO VARCHAR(15), @l_REQ_PAY_ID VARCHAR(20); 
	SET @l_REF_NO = RIGHT(@p_REF_NO, 7)
	-- LẤY ID PDN THANH TOÁN
	SET @l_REQ_PAY_ID = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_TRANS_OUTSIDE_REC WHERE RIGHT(REQ_TRANS_OUTSIDE_REC_ID,7) = RIGHT(@p_REF_NO, 7))
	-- LẤY MÃ BÚT TOÁN LỚN NHẤT
	DECLARE @l_ENTRY_PAIR_temp DECIMAL(18,0), @l_ENTRY_PAIR DECIMAL(18,0)
	SET @l_ENTRY_PAIR_temp = ISNULL((SELECT TOP 1 CAST(ENTRY_PAIR AS INT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @l_REQ_PAY_ID  ORDER BY CAST(ENTRY_PAIR AS INT) DESC), 0)
	SET @l_ENTRY_PAIR = CAST(@l_ENTRY_PAIR_temp + 1 AS VARCHAR(3))
	print @l_ENTRY_PAIR_temp
	-- LẤY SỐ TIỀN, NỘI DUNG THANH TOÁN VÀ GDV
	DECLARE @t_TRANS_EXTERNAL TABLE (AMT DECIMAL(18, 0), TRN_DESC VARCHAR(125), GDV VARCHAR(20), KSV VARCHAR(20))
	INSERT INTO @t_TRANS_EXTERNAL SELECT TOP 1 A.TRADE_AMT, A.TRADE_DESC, B.MAKER_ID_KT, B.CHECKER_ID_KT
								FROM TR_REQ_PAY_TRANS_OUTSIDE_REC A
								LEFT JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID
								WHERE RIGHT(REQ_TRANS_OUTSIDE_REC_ID,7) = RIGHT(@p_REF_NO, 7)
	DECLARE @l_AMT DECIMAL(18,0), @l_TRN_DESC VARCHAR(125), @l_GDV VARCHAR(20), @l_KSV VARCHAR(20); 
	SET @l_AMT = (SELECT TOP 1 AMT FROM @t_TRANS_EXTERNAL)
	SET @l_TRN_DESC = (SELECT TOP 1 TRN_DESC FROM @t_TRANS_EXTERNAL)
	SET @l_GDV = (SELECT TOP 1 GDV FROM @t_TRANS_EXTERNAL)
	SET @l_KSV = (SELECT TOP 1 KSV FROM @t_TRANS_EXTERNAL)

	UPDATE TR_REQ_PAY_TRANS_OUTSIDE_REC  
	SET IS_TRANSFER = N'Đã chuyển tiền' , 
	TRANSFER_DT = GETDATE(),
	REF_NO = @p_TRN_NO
	WHERE RIGHT(REQ_TRANS_OUTSIDE_REC_ID,7) = RIGHT(@p_REF_NO, 7)

-- NỢ
	DECLARE @l_TR_REQ_PAY_ENTRIES_D VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_D OUT;
	INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],
	[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID],
	[AMT],[CURRENCY],[EXC_RATE],[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
	VALUES(@l_TR_REQ_PAY_ENTRIES_D, @l_REQ_PAY_ID,NULL,NULL,NULL,
	@l_ENTRY_PAIR,N'D',N'Nợ',N'0697044000019',N'NGAN HANG TMCP BAN VIET',N'DV0001',N'',
	@l_AMT,N'VND',1.00,GETDATE(),@l_TRN_DESC,NULL,NULL,NULL,NULL,NULL,@l_GDV,NULL,NULL,NULL,NULL,NULL,NULL)

	DECLARE @l_PAY_ENTRIES_POST_D VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'PAY_ENTRIES_POST', @l_PAY_ENTRIES_POST_D OUT;
	INSERT INTO [PAY_ENTRIES_POST] ([ET_ID],[REF_NO],[TRN_ID],[REF_ID],[TRN_TYPE],
				[ENTRY_PAIR],[DO_BRN],[DRCR],[ACCT],[BRN_ID],[DEPT_ID],
				[AMT],[CURRENCY],[EXC_RATE],[EXP_TO_CORE],[TRN_DATE],[TRN_DESC],[MAKER_ID],[CHECKER_ID])
	VALUES	(@l_PAY_ENTRIES_POST_D, @p_TRN_NO, @l_REQ_PAY_ID, @l_TR_REQ_PAY_ENTRIES_D,NULL,
			@l_ENTRY_PAIR,N'DV0001',N'D',N'0697044000019',N'DV0001',N'',
			@l_AMT,N'VND',1.00,N'1',GETDATE(),@l_TRN_DESC,@l_GDV,@l_KSV)

-- CÓ

	DECLARE @l_TR_REQ_PAY_ENTRIES_C VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_C OUT;
	INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],
	[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID],
	[AMT],[CURRENCY],[EXC_RATE],[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
	VALUES(@l_TR_REQ_PAY_ENTRIES_C, @l_REQ_PAY_ID,NULL,NULL,NULL,
	@l_ENTRY_PAIR,N'C',N'Có',N'0697044000052',N'TRUNG GIAN THANH TOAN - INTERNET BANKING',N'DV0001',N'',
	@l_AMT,N'VND',1.00,GETDATE(),@l_TRN_DESC,NULL,NULL,NULL,NULL,NULL,@l_GDV,NULL,NULL,NULL,NULL,NULL,NULL)

	DECLARE @l_PAY_ENTRIES_POST_C VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'PAY_ENTRIES_POST', @l_PAY_ENTRIES_POST_C OUT;
	INSERT INTO [PAY_ENTRIES_POST] ([ET_ID],[REF_NO],[TRN_ID],[REF_ID],[TRN_TYPE],
				[ENTRY_PAIR],[DO_BRN],[DRCR],[ACCT],[BRN_ID],[DEPT_ID],
				[AMT],[CURRENCY],[EXC_RATE],[EXP_TO_CORE],[TRN_DATE],[TRN_DESC],[MAKER_ID],[CHECKER_ID])
	VALUES	(@l_PAY_ENTRIES_POST_C, @p_TRN_NO, @l_REQ_PAY_ID,@l_TR_REQ_PAY_ENTRIES_C,NULL,
			@l_ENTRY_PAIR,N'DV0001',N'C',N'0697044000052',N'DV0001',N'',
			@l_AMT,N'VND',1.00,N'1',GETDATE(),@l_TRN_DESC,@l_GDV,@l_KSV)


SELECT '0' RESULT, '' ERROR  
RETURN 0  
END TRY  
BEGIN CATCH  
SELECT '-1' RESULT, ERROR_MESSAGE() ERROR  
RETURN -1  
END CATCH