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

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