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

USE [gAMSPro_VCCB_v2]

GO

/****** Object:  StoredProcedure [dbo].[PAY_ENTRIES_POST_CoreByRef]    Script Date: 3/15/2021 2:20:16 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_CoreByRef]

	@p_TRN_ID varchar(200) = NULL,

	@p_TRN_TYPE VARCHAR(50) = ''

AS

	DECLARE @l_MAKER_ID VARCHAR(15), @l_CHECKER_ID VARCHAR(15), @l_TRN_DESC NVARCHAR(MAX), @lTRN_DATE DATETIME

	SELECT TOP 1 @l_MAKER_ID = MAKER_ID,@l_CHECKER_ID = CHECKER_ID, @l_TRN_DESC = TRN_DESC, @lTRN_DATE = TRN_DATE

	FROM PAY_ENTRIES_POST 

	WHERE TRN_ID = @p_TRN_ID --AND TRN_TYPE = @p_TRN_TYPE

		--SELECT  CR_BRN, CR_ACCT, 

		--DR_BRN, DEPT_ID DR_DEP, DR_ACCT, 

		--SUM(AMT) AS AMT, 

		--STUFF(( SELECT ',' + C1.ET_ID

		--FROM PAY_ENTRIES_POST C1

		--WHERE C2.CR_BRN=C1.CR_BRN AND C2.CR_ACCT=C1.CR_ACCT AND C2.DR_BRN=C1.DR_BRN AND C2.DR_ACCT=C1.DR_ACCT --AND C1.TRN_ID = @p_REF_ID

		--AND  C2.DEPT_ID = C1.DEPT_ID

		--AND TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE

		--FOR

		--XML PATH('')

		--), 1, 1, '') AS ET_ID

		----'VND' AS CURRENCY, 1.0 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC

		--FROM PAY_ENTRIES_POST  C2	

		--WHERE TRN_ID = @p_REF_ID AND TRN_TYPE = @p_TRN_TYPE

		--GROUP BY C2.CR_BRN, C2.CR_ACCT, C2.DR_BRN, C2.DR_ACCT, C2.DEPT_ID

		SELECT P.ET_ID, P.ENTRY_PAIR, P.DRCR, P.ACCT, P.AMT, 

			P.CURRENCY,P.EXC_RATE,P.TRN_DESC, P.MAKER_ID, P.CHECKER_ID,

			C.BRANCH_CODE, D.DEP_CODE, E.BRANCH_CODE AS DO_BRN

		FROM PAY_ENTRIES_POST P

		LEFT JOIN CM_BRANCH C ON C.BRANCH_ID = P.BRN_ID

		LEFT JOIN CM_DEPARTMENT D ON D.DEP_ID = P.DEPT_ID

		--thieuvq 121120 bo sung dobranch

		LEFT JOIN CM_BRANCH E ON E.BRANCH_ID = P.DO_BRN

		WHERE TRN_ID = @p_TRN_ID AND (REF_NO IS NULL OR REF_NO ='')

		ORDER BY P.ENTRY_PAIR