/*		
	SELECT * FROM MW_ENTRIES_POST
	exec [dbo].[PAY_ENTRIES_POST_ByRefId] @p_REF_ID='RQ0001', @p_BRANCH_ID = 'DV0001'	

*/

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.* FROM
(
SELECT A.AMT, A.ACCT +' '+ BR.BRANCH_CODE + ' '+ DP.DEP_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, '' AS DR_D, A.ENTRY_PAIR,A.DRCR
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 + ' '+ DP.DEP_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, '' AS DR_D,A.ENTRY_PAIR,A.DRCR
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 DESC

--DECLARE @ENT_TEMP TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15))
--DECLARE @ENT_TEMP_ROOT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))
--DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)

----------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------
--INSERT INTO @ENT_TEMP_ROOT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL
--FROM MW_ENTRIES_POST A
--LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID
--LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID
--WHERE TRN_ID = @p_REF_ID AND (A.DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')  AND (REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
--GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID
----ORDER BY LEN(CR_ACCT)

----select * from @ENT_TEMP_ROOT
----DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)

----IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1
----SET @TRN_TYPE = 'ADD_NEW'
----ELSE
----SET @TRN_TYPE = (SELECT TOP 1 TRN_TYPE FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID )
------------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------
---------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH
--UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE
--FROM CM_BRANCH B
--INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9

--UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE
--FROM CM_BRANCH B
--INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9

----IF @TRN_TYPE = 'ADD_NEW'  OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER' 
----BEGIN
--	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D
--	FROM
--	(
--		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D
--		FROM @ENT_TEMP_ROOT A
--		--WHERE LEN(A.DR_ACCT) <= 9
--		UNION 
--		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D
--		FROM @ENT_TEMP_ROOT A
--		--WHERE LEN(A.CR_ACCT) <= 9
--		--ORDER BY ID, ACCT DESC
--	) AG
--	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
--	ORDER BY SUM(REF_ID), AG.ACCT DESC
----END
----ELSE
----BEGIN
----	--IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN
----	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D
----	FROM
----	(
----		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.DR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D
----		FROM @ENT_TEMP_ROOT A
----		UNION 
----		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D
----		FROM @ENT_TEMP_ROOT A
----	--ORDER BY ID, ACCT DESC
----	) AG
----	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
----	ORDER BY AG.DR_BRN, AG.ACCT DESC
----END

--DELETE @ENT_TEMP
--DELETE @ENT_TEMP_ROOT
--------------------------DONE--------------------------


/*		
	SELECT * FROM MW_ENTRIES_POST
	exec [dbo].[PAY_ENTRIES_POST_ByRefId_V2] @p_REF_ID='RQ0001', @p_BRANCH_ID = 'DV0001'	

*/
¿
ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_ByRefId_V2] 
@p_REF_ID VARCHAR(200) = NULL, 
@p_BRANCH_ID VARCHAR(50) = NULL, 
@RefNo VARCHAR(500) = NULL
AS

--SELECT A.AMT, A.ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](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, '' AS DR_D
--FROM PAY_ENTRIES_POST A
--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

-- 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.* FROM
(
SELECT A.AMT, A.ACCT +' '+ BR.BRANCH_CODE + ' '+ DP.DEP_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, '' AS DR_D, A.ENTRY_PAIR,A.DRCR
FROM 
(
	SELECT 	A.REQ_PAY_ID AS [TRN_ID], A.REQ_PAY_DT_ID AS [REF_ID], A.TRN_TYPE AS [TRN_TYPE], A.ENTRY_PAIR AS [ENTRY_PAIR], 
			'@DO_BRN' AS [DO_BRN],A.DR_CR AS [DRCR], A.ACCT AS [ACCT], A.BRANCH_ID AS [BRN_ID], A.DEP_ID AS [DEPT_ID],
			A.AMT AS [AMT], A.CURRENCY AS [CURRENCY], A.EXC_RATE AS [EXC_RATE], '1' AS [EXP_TO_CORE],
			GETDATE() AS [TRN_DATE],A.TRN_DESC AS [TRN_DESC],'@p_MAKER_ID' AS [MAKER_ID], '@p_CHECKER_ID' AS [CHECKER_ID]
	FROM TR_REQ_PAY_ENTRIES A 
	WHERE A.REQ_PAY_ID = @p_REF_ID
) 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
UNION
SELECT A.AMT, GL.TL_GL+' '+ BR.BRANCH_CODE + ' '+ DP.DEP_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, '' AS DR_D, A.ENTRY_PAIR,A.DRCR
FROM 
(
	SELECT 	A.REQ_PAY_ID AS [TRN_ID], A.REQ_PAY_DT_ID AS [REF_ID], A.TRN_TYPE AS [TRN_TYPE], A.ENTRY_PAIR AS [ENTRY_PAIR], 
			'@DO_BRN' AS [DO_BRN],A.DR_CR AS [DRCR], A.ACCT AS [ACCT], A.BRANCH_ID AS [BRN_ID], A.DEP_ID AS [DEPT_ID],
			A.AMT AS [AMT], A.CURRENCY AS [CURRENCY], A.EXC_RATE AS [EXC_RATE], '1' AS [EXP_TO_CORE],
			GETDATE() AS [TRN_DATE],A.TRN_DESC AS [TRN_DESC],'@p_MAKER_ID' AS [MAKER_ID], '@p_CHECKER_ID' AS [CHECKER_ID]
	FROM TR_REQ_PAY_ENTRIES A WHERE A.REQ_PAY_ID = @p_REF_ID
) 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 DESC

--DECLARE @ENT_TEMP TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15))
--DECLARE @ENT_TEMP_ROOT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))
--DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)

----------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------
--INSERT INTO @ENT_TEMP_ROOT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL
--FROM MW_ENTRIES_POST A
--LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID
--LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID
--WHERE TRN_ID = @p_REF_ID AND (A.DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')  AND (REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
--GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID
----ORDER BY LEN(CR_ACCT)

----select * from @ENT_TEMP_ROOT
----DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)

----IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1
----SET @TRN_TYPE = 'ADD_NEW'
----ELSE
----SET @TRN_TYPE = (SELECT TOP 1 TRN_TYPE FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID )
------------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------
---------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH
--UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE
--FROM CM_BRANCH B
--INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9

--UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE
--FROM CM_BRANCH B
--INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9

----IF @TRN_TYPE = 'ADD_NEW'  OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER' 
----BEGIN
--	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D
--	FROM
--	(
--		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D
--		FROM @ENT_TEMP_ROOT A
--		--WHERE LEN(A.DR_ACCT) <= 9
--		UNION 
--		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D
--		FROM @ENT_TEMP_ROOT A
--		--WHERE LEN(A.CR_ACCT) <= 9
--		--ORDER BY ID, ACCT DESC
--	) AG
--	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
--	ORDER BY SUM(REF_ID), AG.ACCT DESC
----END
----ELSE
----BEGIN
----	--IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN
----	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D
----	FROM
----	(
----		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.DR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D
----		FROM @ENT_TEMP_ROOT A
----		UNION 
----		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D
----		FROM @ENT_TEMP_ROOT A
----	--ORDER BY ID, ACCT DESC
----	) AG
----	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
----	ORDER BY AG.DR_BRN, AG.ACCT DESC
----END

--DELETE @ENT_TEMP
--DELETE @ENT_TEMP_ROOT
--------------------------DONE--------------------------