|
1
|
/*
|
|
2
|
SELECT * FROM MW_ENTRIES_POST
|
|
3
|
exec [dbo].[PAY_ENTRIES_POST_ByRefId] @p_REF_ID='RQ0001', @p_BRANCH_ID = 'DV0001'
|
|
4
|
|
|
5
|
*/
|
|
6
|
|
|
7
|
ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_ByRefId]
|
|
8
|
@p_REF_ID VARCHAR(200) = NULL,
|
|
9
|
@p_BRANCH_ID VARCHAR(50) = NULL,
|
|
10
|
@RefNo VARCHAR(500) = NULL
|
|
11
|
AS
|
|
12
|
-- LUCTV 03062020 BO SUNG BANG MAP GL
|
|
13
|
DECLARE @TABLE_MAP_GL TABLE (ACCT VARCHAR(100), TL_GL VARCHAR(100), GL_NAME VARCHAR(1000))
|
|
14
|
INSERT INTO @TABLE_MAP_GL SELECT ACC_NO, TK_GL, TK_GL_NAME FROM CM_ACCOUNT
|
|
15
|
INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, TK_GL, TK_GL_NAME FROM CM_ACCOUNT_PAY
|
|
16
|
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
|
|
17
|
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
|
|
18
|
---
|
|
19
|
SELECT A.* FROM
|
|
20
|
(
|
|
21
|
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,
|
|
22
|
(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
|
|
23
|
FROM PAY_ENTRIES_POST A
|
|
24
|
LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID
|
|
25
|
LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID
|
|
26
|
WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
|
|
27
|
AND LEN(A.ACCT) <= 9
|
|
28
|
--ORDER BY A.ENTRY_PAIR, A.DRCR DESC
|
|
29
|
|
|
30
|
UNION
|
|
31
|
|
|
32
|
SELECT A.AMT, GL.TL_GL +' '+ BR.BRANCH_CODE + ' '+ DP.DEP_CODE AS DR_ACCT, GL.GL_NAME AS DR_ACCT_NAME, '' DR_BRN,
|
|
33
|
(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
|
|
34
|
FROM PAY_ENTRIES_POST A
|
|
35
|
INNER JOIN @TABLE_MAP_GL GL ON A.ACCT = GL.ACCT
|
|
36
|
LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID
|
|
37
|
LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID
|
|
38
|
WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
|
|
39
|
AND LEN(A.ACCT) > 9
|
|
40
|
) A
|
|
41
|
ORDER BY A.ENTRY_PAIR, A.DRCR DESC
|
|
42
|
|
|
43
|
--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))
|
|
44
|
--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))
|
|
45
|
--DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)
|
|
46
|
|
|
47
|
----------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------
|
|
48
|
--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
|
|
49
|
--FROM MW_ENTRIES_POST A
|
|
50
|
--LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID
|
|
51
|
--LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID
|
|
52
|
--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 = '')
|
|
53
|
--GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID
|
|
54
|
----ORDER BY LEN(CR_ACCT)
|
|
55
|
|
|
56
|
----select * from @ENT_TEMP_ROOT
|
|
57
|
----DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)
|
|
58
|
|
|
59
|
----IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1
|
|
60
|
----SET @TRN_TYPE = 'ADD_NEW'
|
|
61
|
----ELSE
|
|
62
|
----SET @TRN_TYPE = (SELECT TOP 1 TRN_TYPE FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID )
|
|
63
|
------------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------
|
|
64
|
---------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH
|
|
65
|
--UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE
|
|
66
|
--FROM CM_BRANCH B
|
|
67
|
--INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9
|
|
68
|
|
|
69
|
--UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE
|
|
70
|
--FROM CM_BRANCH B
|
|
71
|
--INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9
|
|
72
|
|
|
73
|
----IF @TRN_TYPE = 'ADD_NEW' OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER'
|
|
74
|
----BEGIN
|
|
75
|
-- 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
|
|
76
|
-- FROM
|
|
77
|
-- (
|
|
78
|
-- 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
|
|
79
|
-- FROM @ENT_TEMP_ROOT A
|
|
80
|
-- --WHERE LEN(A.DR_ACCT) <= 9
|
|
81
|
-- UNION
|
|
82
|
-- 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
|
|
83
|
-- FROM @ENT_TEMP_ROOT A
|
|
84
|
-- --WHERE LEN(A.CR_ACCT) <= 9
|
|
85
|
-- --ORDER BY ID, ACCT DESC
|
|
86
|
-- ) AG
|
|
87
|
-- GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
|
|
88
|
-- ORDER BY SUM(REF_ID), AG.ACCT DESC
|
|
89
|
----END
|
|
90
|
----ELSE
|
|
91
|
----BEGIN
|
|
92
|
---- --IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN
|
|
93
|
---- 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
|
|
94
|
---- FROM
|
|
95
|
---- (
|
|
96
|
---- 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
|
|
97
|
---- FROM @ENT_TEMP_ROOT A
|
|
98
|
---- UNION
|
|
99
|
---- 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
|
|
100
|
---- FROM @ENT_TEMP_ROOT A
|
|
101
|
---- --ORDER BY ID, ACCT DESC
|
|
102
|
---- ) AG
|
|
103
|
---- GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
|
|
104
|
---- ORDER BY AG.DR_BRN, AG.ACCT DESC
|
|
105
|
----END
|
|
106
|
|
|
107
|
--DELETE @ENT_TEMP
|
|
108
|
--DELETE @ENT_TEMP_ROOT
|
|
109
|
--------------------------DONE--------------------------
|
|
110
|
|
|
111
|
|
|
112
|
/*
|
|
113
|
SELECT * FROM MW_ENTRIES_POST
|
|
114
|
exec [dbo].[PAY_ENTRIES_POST_ByRefId_V2] @p_REF_ID='RQ0001', @p_BRANCH_ID = 'DV0001'
|
|
115
|
|
|
116
|
*/
|
|
117
|
¿
|
|
118
|
ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_ByRefId_V2]
|
|
119
|
@p_REF_ID VARCHAR(200) = NULL,
|
|
120
|
@p_BRANCH_ID VARCHAR(50) = NULL,
|
|
121
|
@RefNo VARCHAR(500) = NULL
|
|
122
|
AS
|
|
123
|
|
|
124
|
--SELECT A.AMT, A.ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.ACCT) AS DR_ACCT_NAME, '' DR_BRN,
|
|
125
|
--(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, '' AS DR_D
|
|
126
|
--FROM PAY_ENTRIES_POST A
|
|
127
|
--WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
|
|
128
|
--AND LEN(A.ACCT) <= 9
|
|
129
|
--ORDER BY A.ENTRY_PAIR, A.DRCR DESC
|
|
130
|
|
|
131
|
-- LUCTV 03062020 BO SUNG BANG MAP GL
|
|
132
|
DECLARE @TABLE_MAP_GL TABLE (ACCT VARCHAR(100), TL_GL VARCHAR(100), GL_NAME VARCHAR(1000))
|
|
133
|
INSERT INTO @TABLE_MAP_GL SELECT ACC_NO, TK_GL, TK_GL_NAME FROM CM_ACCOUNT
|
|
134
|
INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, TK_GL, TK_GL_NAME FROM CM_ACCOUNT_PAY
|
|
135
|
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
|
|
136
|
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
|
|
137
|
---
|
|
138
|
SELECT A.* FROM
|
|
139
|
(
|
|
140
|
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,
|
|
141
|
(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
|
|
142
|
FROM
|
|
143
|
(
|
|
144
|
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],
|
|
145
|
'@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],
|
|
146
|
A.AMT AS [AMT], A.CURRENCY AS [CURRENCY], A.EXC_RATE AS [EXC_RATE], '1' AS [EXP_TO_CORE],
|
|
147
|
GETDATE() AS [TRN_DATE],A.TRN_DESC AS [TRN_DESC],'@p_MAKER_ID' AS [MAKER_ID], '@p_CHECKER_ID' AS [CHECKER_ID]
|
|
148
|
FROM TR_REQ_PAY_ENTRIES A
|
|
149
|
WHERE A.REQ_PAY_ID = @p_REF_ID
|
|
150
|
) A
|
|
151
|
LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID
|
|
152
|
LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID
|
|
153
|
WHERE A.TRN_ID = @p_REF_ID --AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
|
|
154
|
AND LEN(A.ACCT) <= 9
|
|
155
|
UNION
|
|
156
|
SELECT A.AMT, GL.TL_GL+' '+ BR.BRANCH_CODE + ' '+ DP.DEP_CODE AS DR_ACCT, GL.GL_NAME AS DR_ACCT_NAME, '' DR_BRN,
|
|
157
|
(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
|
|
158
|
FROM
|
|
159
|
(
|
|
160
|
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],
|
|
161
|
'@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],
|
|
162
|
A.AMT AS [AMT], A.CURRENCY AS [CURRENCY], A.EXC_RATE AS [EXC_RATE], '1' AS [EXP_TO_CORE],
|
|
163
|
GETDATE() AS [TRN_DATE],A.TRN_DESC AS [TRN_DESC],'@p_MAKER_ID' AS [MAKER_ID], '@p_CHECKER_ID' AS [CHECKER_ID]
|
|
164
|
FROM TR_REQ_PAY_ENTRIES A WHERE A.REQ_PAY_ID = @p_REF_ID
|
|
165
|
) A
|
|
166
|
INNER JOIN @TABLE_MAP_GL GL ON A.ACCT = GL.ACCT
|
|
167
|
LEFT JOIN CM_BRANCH BR ON A.BRN_ID = BR.BRANCH_ID
|
|
168
|
LEFT JOIN CM_DEPARTMENT DP ON A.DEPT_ID = DP.DEP_ID
|
|
169
|
WHERE A.TRN_ID = @p_REF_ID --AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')
|
|
170
|
AND LEN(A.ACCT) > 9
|
|
171
|
) A
|
|
172
|
|
|
173
|
ORDER BY A.ENTRY_PAIR, A.DRCR DESC
|
|
174
|
|
|
175
|
--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))
|
|
176
|
--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))
|
|
177
|
--DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)
|
|
178
|
|
|
179
|
----------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------
|
|
180
|
--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
|
|
181
|
--FROM MW_ENTRIES_POST A
|
|
182
|
--LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID
|
|
183
|
--LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID
|
|
184
|
--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 = '')
|
|
185
|
--GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID
|
|
186
|
----ORDER BY LEN(CR_ACCT)
|
|
187
|
|
|
188
|
----select * from @ENT_TEMP_ROOT
|
|
189
|
----DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)
|
|
190
|
|
|
191
|
----IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1
|
|
192
|
----SET @TRN_TYPE = 'ADD_NEW'
|
|
193
|
----ELSE
|
|
194
|
----SET @TRN_TYPE = (SELECT TOP 1 TRN_TYPE FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID )
|
|
195
|
------------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------
|
|
196
|
---------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH
|
|
197
|
--UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE
|
|
198
|
--FROM CM_BRANCH B
|
|
199
|
--INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9
|
|
200
|
|
|
201
|
--UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE
|
|
202
|
--FROM CM_BRANCH B
|
|
203
|
--INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9
|
|
204
|
|
|
205
|
----IF @TRN_TYPE = 'ADD_NEW' OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER'
|
|
206
|
----BEGIN
|
|
207
|
-- 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
|
|
208
|
-- FROM
|
|
209
|
-- (
|
|
210
|
-- 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
|
|
211
|
-- FROM @ENT_TEMP_ROOT A
|
|
212
|
-- --WHERE LEN(A.DR_ACCT) <= 9
|
|
213
|
-- UNION
|
|
214
|
-- 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
|
|
215
|
-- FROM @ENT_TEMP_ROOT A
|
|
216
|
-- --WHERE LEN(A.CR_ACCT) <= 9
|
|
217
|
-- --ORDER BY ID, ACCT DESC
|
|
218
|
-- ) AG
|
|
219
|
-- GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
|
|
220
|
-- ORDER BY SUM(REF_ID), AG.ACCT DESC
|
|
221
|
----END
|
|
222
|
----ELSE
|
|
223
|
----BEGIN
|
|
224
|
---- --IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN
|
|
225
|
---- 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
|
|
226
|
---- FROM
|
|
227
|
---- (
|
|
228
|
---- 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
|
|
229
|
---- FROM @ENT_TEMP_ROOT A
|
|
230
|
---- UNION
|
|
231
|
---- 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
|
|
232
|
---- FROM @ENT_TEMP_ROOT A
|
|
233
|
---- --ORDER BY ID, ACCT DESC
|
|
234
|
---- ) AG
|
|
235
|
---- GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D
|
|
236
|
---- ORDER BY AG.DR_BRN, AG.ACCT DESC
|
|
237
|
----END
|
|
238
|
|
|
239
|
--DELETE @ENT_TEMP
|
|
240
|
--DELETE @ENT_TEMP_ROOT
|
|
241
|
--------------------------DONE--------------------------
|