ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_ByRefId] 
@p_REF_ID VARCHAR(200) = NULL, 
@p_BRANCH_ID VARCHAR(50) = NULL, 
@RefNo VARCHAR(500) = NULL
AS
-- LUCTV 03062020 BO SUNG BANG MAP GL
DECLARE  @TABLE_MAP_GL TABLE (ACCT VARCHAR(100), TL_GL VARCHAR(100), GL_NAME VARCHAR(1000))
INSERT INTO @TABLE_MAP_GL SELECT ACC_NO, TK_GL, TK_GL_NAME FROM CM_ACCOUNT
--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, TK_GL, TK_GL_NAME FROM CM_ACCOUNT_PAY
--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, (SELECT ParaValue FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL'), (SELECT Description FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL') FROM CM_SUPPLIER
--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM_OUT, (SELECT ParaValue FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL_O'), (SELECT Description FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL_O') FROM CM_SUPPLIER
---
SELECT A.REF_NO, MAX(A.DR_ACCT) AS DR_ACCT, MAX(A.DR_ACCT_NAME) AS DR_ACCT_NAME,
MAX(A.DR_BRN) AS DR_BRN,MAX(A.DR_D) AS DR_D, MAX(A.ACCT) AS ACCT, MAX(A.REF_ID) AS REF_ID, MAX(A.DR_D) AS DR_D, MAX(A.ENTRY_PAIR) AS ENTRY_PAIR,
MAX(A.DRCR) AS DRCR,  REPLACE (FORMAT (SUM(A.AMT),'#,###'),',','.') +',00 VND' AS AMT
FROM
(
SELECT A.AMT, A.ACCT +' '+ BR.BRANCH_CODE AS DR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) AS DR_ACCT_NAME, '' DR_BRN, 
(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, ISNULL(DP.DEP_CODE,'') AS DR_D, A.ENTRY_PAIR,A.DRCR,A.REF_NO,A.DEPT_ID
FROM PAY_ENTRIES_POST A
LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID
LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID
WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '') 
AND LEN(A.ACCT) <= 9
--ORDER BY A.ENTRY_PAIR, A.DRCR DESC

UNION 

SELECT A.AMT, GL.TL_GL +' '+ BR.BRANCH_CODE  AS DR_ACCT, GL.GL_NAME AS DR_ACCT_NAME, '' DR_BRN, 
(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, ISNULL(DP.DEP_CODE,'') AS DR_D,A.ENTRY_PAIR,A.DRCR,A.REF_NO,A.DEPT_ID
FROM PAY_ENTRIES_POST A
INNER JOIN @TABLE_MAP_GL GL ON A.ACCT = GL.ACCT
LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID
LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID
WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '') 
AND LEN(A.ACCT) > 9
) A
--ORDER BY A.ENTRY_PAIR, A.DRCR,A.REF_NO
GROUP BY A.REF_NO, A.DR_ACCT, A.DRCR, A.DR_BRN,A.DEPT_ID
ORDER BY A.DRCR DESC