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