· 8 years ago · Jan 31, 2018, 07:42 AM
1CREATE OR REPLACE FUNCTION r_ledger_card_report_grouping(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying)
2 RETURNS SETOF refcursor AS
3$BODY$
4DECLARE
5 pRefHeader REFCURSOR := 'refHeader';
6 pRefDetail REFCURSOR := 'refDetail';
7 pSessionId ALIAS FOR $1;
8 pTenantId ALIAS FOR $2;
9 pUserId ALIAS FOR $3;
10 pRoleId ALIAS FOR $4;
11 pDatetime ALIAS FOR $5;
12 pOuBuId ALIAS FOR $6;
13 pOuBranchId ALIAS FOR $7;
14 pOuSubBuId ALIAS FOR $8;
15 pGroupCoaId ALIAS FOR $9;
16 pOuRc ALIAS FOR $10;
17 pSegment ALIAS FOR $11;
18 pPeriodFrom ALIAS FOR $12;
19 pPeriodTo ALIAS FOR $13;
20 pMainAcc ALIAS FOR $14;
21 pSubAcc ALIAS FOR $15;
22
23 vEmptyId bigint := -99;
24 vEmptyString character varying := '';
25 vEmptyAmount numeric := 0;
26 vYes character varying := 'Y';
27 vStatusDocTrx character varying := '';
28 vStatusDocDraft character varying := 'D';
29 vStatusDocRelease character varying := 'R';
30 vSignDebt character varying := 'D';
31 vSignCredit character varying := 'C';
32 vTypeRateCom character varying := 'COM';
33 vCurrGL character varying(5);
34 vJoinOuRc text := '';
35 vJoinSegment text := '';
36 vFilterBranch text := '';
37 vFilterSubBu text := '';
38 vFilterGroupCoa text := '';
39 vFilterOuRc text := '';
40 vFilterSegment text := '';
41 vFilterCoaMainAcc text := '';
42 vFilterCoaSubAcc text := '';
43 vRoundingDigit integer;
44 vTypeSaldoAwal character varying := 'SALDO AWAL';
45
46BEGIN
47 vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
48
49 DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
50 DELETE FROM tr_gl_detail_ledger_card WHERE session_id = pSessionId;
51
52 /* ambil nilai pembulatan*/
53 vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
54
55 /* vJoinOuRc, jika ouRc diisi */
56 IF (pOuRc <> vEmptyString) THEN
57 vJoinOuRc := ' INNER JOIN t_ou K ON B.ou_rc_id = K.ou_id
58 INNER JOIN t_ou_type L ON K.ou_type_id = L.ou_type_id AND L.flg_accounting = '''|| vYes || '''';
59 END IF;
60
61 /* vJoinSegment, jika segment diisi*/
62 IF (pSegment <> vEmptyString) THEN
63 vJoinSegment := ' INNER JOIN m_segment_coa M ON B.segmen_id = M.segment_coa_id ';
64 END IF;
65
66 /* branchId <> -99*/
67 IF (pOuBranchId <> vEmptyId) THEN
68 vFilterBranch := ' AND B.ou_branch_id = ' || pOuBranchId;
69 END IF;
70
71 /* subBuId <> -99*/
72 IF (pOuSubBuId <> vEmptyId) THEN
73 vFilterSubBu := ' AND B.ou_sub_bu_id = ' || pOuSubBuId;
74 END IF;
75
76 /* gorupCoaId <> -99*/
77 IF (pGroupCoaId <> vEmptyId) THEN
78 vFilterGroupCoa := ' AND E.group_coa_id = ' || pGroupCoaId;
79 END IF;
80
81 /* jika ouRc diisi*/
82 IF (pOuRc <> vEmptyString) THEN
83 vFilterOuRc := ' AND UPPER(K.ou_name) LIKE UPPER(''%' || pOuRc || '%'')';
84 END IF;
85
86 /* jika segment diisi*/
87 IF (pSegment <> vEmptyString) THEN
88 vFilterSegment := ' AND UPPER(M.segment_coa_name) LIKE UPPER(''%' || pSegment || '%'')';
89 END IF;
90
91 /* jika main acc diisi*/
92 IF (pMainAcc <> vEmptyString) THEN
93 vFilterCoaMainAcc := ' AND E.main_acc LIKE ''' || pMainAcc || '%''';
94 END IF;
95
96 /* jika sub acc diisi*/
97 IF (pSubAcc <> vEmptyString) THEN
98 vFilterCoaSubAcc := ' AND E.sub_acc LIKE ''' || pSubAcc || '%''';
99 END IF;
100
101 /*
102 * ambil saldo awal ledger card
103 */
104 PERFORM gl_get_ledger_card_beginning_balance(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pOuBuId, pOuBranchId, pOuSubBuId, pGroupCoaId, pOuRc, pSegment, pPeriodFrom, pMainAcc, pSubAcc);
105
106 /* ambil data detail yang, status doc = R
107 * 1) Ada transaksi tapi tidak ada saldo
108 * 2) Ada transaksi dan ada saldo awal
109 */
110 EXECUTE '
111 INSERT INTO tr_gl_detail_ledger_card(
112 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
113 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
114 journal_date, numerator_rate, denominator_rate, gl_curr_code,
115 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
116 ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
117 sub_acc, beginning_balance, sign_coa, coa_desc, remark, order_num)
118 WITH gl_journal_trx_details AS (
119 SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
120 a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
121 a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark,
122 CASE WHEN a.ou_branch_id = $7 AND a.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE a.ou_branch_id END AS ou_branch_id,
123 CASE WHEN a.ou_branch_id = $7 AND a.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE a.ou_sub_bu_id END AS ou_sub_bu_id
124 FROM gl_journal_trx_item a
125 INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
126 INNER JOIN dt_date p ON x.doc_date = p.string_date
127 WHERE a.tenant_id = $2
128 AND p.year_month_date BETWEEN $3 AND $4
129 UNION ALL
130 SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
131 b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
132 b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark,
133 CASE WHEN b.ou_branch_id = $7 AND b.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE b.ou_branch_id END AS ou_branch_id,
134 CASE WHEN b.ou_branch_id = $7 AND b.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE b.ou_sub_bu_id END AS ou_sub_bu_id
135 FROM gl_journal_trx_mapping b
136 INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id
137 INNER JOIN dt_date p ON x.doc_date = p.string_date
138 WHERE b.tenant_id = $2
139 AND p.year_month_date BETWEEN $3 AND $4
140 UNION ALL
141 SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
142 c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
143 c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark,
144 CASE WHEN c.ou_branch_id = $7 AND c.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE c.ou_branch_id END AS ou_branch_id,
145 CASE WHEN c.ou_branch_id = $7 AND c.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE c.ou_sub_bu_id END AS ou_sub_bu_id
146 FROM gl_journal_trx_fx c
147 INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
148 INNER JOIN dt_date p ON x.doc_date = p.string_date
149 WHERE c.tenant_id = $2
150 AND p.year_month_date BETWEEN $3 AND $4
151 )
152 SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
153 A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
154 B.journal_date, B.numerator_rate, B.denominator_rate, B.gl_curr_code,
155 B.gl_amount, B.ou_branch_id, f_get_ou_name(B.ou_branch_id) AS branch_name, B.ou_sub_bu_id,f_get_ou_name(B.ou_sub_bu_id) AS sub_bu_name,
156 B.ou_rc_id, f_get_ou_name(B.ou_rc_id) AS rc_name, B.segmen_id, f_get_segment_name(B.segmen_id) AS segment_name,
157 B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
158 CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark, 2
159 FROM gl_journal_trx A
160 INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
161 INNER JOIN m_coa E ON B.coa_id = E.coa_id' ||
162 vJoinOuRc ||
163 vJoinSegment ||
164 ' LEFT OUTER JOIN tr_gl_beginning_balance_ledger_card C ON A.ou_bu_id = C.ou_bu_id AND A.ou_branch_id = C.ou_branch_id
165 AND A.ou_sub_bu_id = C.ou_sub_bu_id AND B.coa_id = C.coa_id AND B.ou_rc_id = C.ou_rc_id AND B.segmen_id = C.segment_id
166 AND C.session_id = $1
167 WHERE A.tenant_id = $2
168 AND A.status_doc = $5
169 AND A.ou_bu_id = $6' ||
170 vFilterBranch ||
171 vFilterSubBu ||
172 vFilterGroupCoa ||
173 vFilterOuRc ||
174 vFilterSegment ||
175 vFilterCoaMainAcc ||
176 vFilterCoaSubAcc ||
177 ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, pPeriodFrom, pPeriodTo, vStatusDocRelease, pOuBuId, vEmptyId;
178
179 /* ambil data detail, status doc = D */
180 EXECUTE '
181 INSERT INTO tr_gl_detail_ledger_card(
182 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
183 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
184 journal_date, numerator_rate, denominator_rate, gl_curr_code,
185 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
186 ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
187 sub_acc, beginning_balance, sign_coa, coa_desc, remark, order_num)
188 WITH gl_journal_trx_details AS (
189 SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
190 a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
191 a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark, a.type_rate,
192 CASE WHEN a.ou_branch_id = $10 AND a.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE a.ou_branch_id END AS ou_branch_id,
193 CASE WHEN a.ou_branch_id = $10 AND a.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE a.ou_sub_bu_id END AS ou_sub_bu_id
194 FROM gl_journal_trx_item a
195 INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
196 INNER JOIN dt_date p ON x.doc_date = p.string_date
197 WHERE a.tenant_id = $2
198 AND p.year_month_date BETWEEN $3 AND $4
199 UNION ALL
200 SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
201 b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
202 b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark, b.type_rate,
203 CASE WHEN b.ou_branch_id = $10 AND b.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE b.ou_branch_id END AS ou_branch_id,
204 CASE WHEN b.ou_branch_id = $10 AND b.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE b.ou_sub_bu_id END AS ou_sub_bu_id
205 FROM gl_journal_trx_mapping b
206 INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id
207 INNER JOIN dt_date p ON x.doc_date = p.string_date
208 WHERE b.tenant_id = $2
209 AND p.year_month_date BETWEEN $3 AND $4
210 UNION ALL
211 SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
212 c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
213 c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark, $8,
214 CASE WHEN c.ou_branch_id = $10 AND c.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE c.ou_branch_id END AS ou_branch_id,
215 CASE WHEN c.ou_branch_id = $10 AND c.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE c.ou_sub_bu_id END AS ou_sub_bu_id
216 FROM gl_journal_trx_fx c
217 INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
218 INNER JOIN dt_date p ON x.doc_date = p.string_date
219 WHERE c.tenant_id = $2
220 AND p.year_month_date BETWEEN $3 AND $4
221 ), tt_gl_admin_journal_rate AS (
222 SELECT A.type_rate, A.journal_date, A.curr_code, $9, f_get_exchange_rate($2, A.type_rate, A.journal_date, A.curr_code, $9) AS data_exchange_rate
223 FROM (
224 SELECT A.journal_date, A.type_rate, A.curr_code
225 FROM gl_journal_trx_details A
226 GROUP BY journal_date, type_rate, curr_code
227 ) A
228 )
229 SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
230 A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
231 B.journal_date, (D.data_exchange_rate).numerator_rate AS numerator_rate, (D.data_exchange_rate).denominator_rate AS denominator_rate, CASE WHEN (B.gl_curr_code is null OR TRIM(B.gl_curr_code) = '''') THEN $9 ELSE B.gl_curr_code END gl_curr_code,
232 ROUND(B.amount * (D.data_exchange_rate).numerator_rate / (D.data_exchange_rate).denominator_rate, $7) AS gl_amount,
233 B.ou_branch_id, f_get_ou_name(B.ou_branch_id) AS branch_name, B.ou_sub_bu_id, f_get_ou_name(B.ou_sub_bu_id) AS sub_bu_name,
234 B.ou_rc_id, f_get_ou_name(B.ou_rc_id) AS rc_name, B.segmen_id, f_get_segment_name(B.segmen_id) AS segment_name,
235 B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
236 CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark, 2
237 FROM gl_journal_trx A
238 INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
239 INNER JOIN m_coa E ON B.coa_id = E.coa_id
240 INNER JOIN tt_gl_admin_journal_rate D ON D.type_rate=B.type_rate AND B.journal_date=D.journal_date AND D.curr_code=B.curr_code'||
241 vJoinOuRc ||
242 vJoinSegment ||
243 ' LEFT OUTER JOIN tr_gl_beginning_balance_ledger_card C ON A.ou_bu_id = C.ou_bu_id AND A.ou_branch_id = C.ou_branch_id
244 AND A.ou_sub_bu_id = C.ou_sub_bu_id AND B.coa_id = C.coa_id AND B.ou_rc_id = C.ou_rc_id AND B.segmen_id = C.segment_id
245 AND C.session_id = $1
246 WHERE A.tenant_id = $2
247 AND A.status_doc = $5
248 AND A.ou_bu_id = $6' ||
249 vFilterBranch ||
250 vFilterSubBu ||
251 vFilterGroupCoa ||
252 vFilterOuRc ||
253 vFilterSegment ||
254 vFilterCoaMainAcc ||
255 vFilterCoaSubAcc ||
256 ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, pPeriodFrom, pPeriodTo, vStatusDocDraft, pOuBuId, vRoundingDigit, vTypeRateCom, vCurrGL, vEmptyId;
257
258 /* ambil data detail yang tidak ada transaksi tapi ada saldo */
259 INSERT INTO tr_gl_detail_ledger_card(
260 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
261 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
262 journal_date, gl_curr_code,
263 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
264 ou_rc_id, rc_name, segment_id, segment_name,
265 coa_id, main_acc, sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
266 SELECT pSessionId, pTenantId, A.ou_bu_id, vEmptyId, vEmptyString,
267 vEmptyId, vEmptyString, vEmptyString, L.sign_coa, vEmptyString, vEmptyAmount,
268 vEmptyString, vEmptyString,
269 vEmptyAmount, A.ou_branch_id, f_get_ou_name(A.ou_branch_id) AS branch_name, A.ou_sub_bu_id, f_get_ou_name(A.ou_sub_bu_id) AS sub_bu_name,
270 A.ou_rc_id, f_get_ou_name(A.ou_rc_id) AS rc_name, A.segment_id, f_get_segment_name(A.segment_id) AS segment_name,
271 A.coa_id, L.main_acc, L.sub_acc, COALESCE(A.beginning_balance, 0) AS beginning_balance, L.sign_coa, L.coa_desc, 1, vEmptyString
272 FROM tr_gl_beginning_balance_ledger_card A
273 INNER JOIN m_coa L ON A.coa_id = L.coa_id
274 WHERE NOT EXISTS (SELECT 1
275 FROM tr_gl_detail_ledger_card B
276 WHERE A.ou_bu_id = B.ou_bu_id
277 AND A.ou_branch_id = B.ou_branch_id
278 AND A.ou_sub_bu_id = B.ou_sub_bu_id
279 AND A.coa_id = B.coa_id
280 AND A.ou_rc_id = B.ou_rc_id
281 AND A.segment_id = B.segment_id
282 AND A.session_id = B.session_id)
283 AND A.tenant_id = pTenantId
284 AND A.session_id = pSessionId;
285 /*
286 /* Insert data beginning balance yang ada transaksinya ambil dari table tr_gl_detail_ledger_card yang order_num= 2 */
287 INSERT INTO tr_gl_detail_ledger_card(
288 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
289 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
290 journal_date, gl_curr_code,
291 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
292 ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
293 sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
294 WITH data_saldo_awal_with_transaksi AS (
295 SELECT A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
296 A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
297 A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
298 FROM tr_gl_detail_ledger_card A
299 WHERE A.session_id = pSessionId
300 AND A.tenant_id = pTenantId
301 AND A.order_num = 2
302 GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
303 A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
304 A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
305 )
306 SELECT pSessionId, A.tenant_id, A.ou_bu_id, vEmptyId, vTypeSaldoAwal,
307 vEmptyId, vEmptyString, vEmptyString, A.sign_coa, vEmptyString, vEmptyAmount,
308 vEmptyString, vEmptyString,
309 A.beginning_balance, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
310 A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
311 A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc, 1, vEmptyString
312 FROM data_saldo_awal_with_transaksi A
313 ORDER BY A.coa_id;
314 */
315
316 /* header result*/
317 Open pRefHeader FOR
318 SELECT pOuBuId AS ou_id, f_get_ou_name(pOuBuId) AS ou_name, pPeriodFrom AS period_from, pPeriodTo AS period_to, f_get_username(pUserId) AS username, pDatetime AS datetime;
319 RETURN NEXT pRefHeader;
320
321 Open pRefDetail FOR
322 SELECT A.doc_type_id, A.doc_type_desc, A.doc_no, A.doc_date, A.sign_journal, A.curr_code_trx, SUM(A.amount_trx) AS amount_trx, A.journal_date,
323 A.numerator_rate, A.denominator_rate, A.sign_coa, A.gl_curr_code, SUM(A.gl_amount) AS gl_amount, A.ou_branch_id, A.branch_name,
324 A.ou_sub_bu_id, A.sub_bu_name, A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id,
325 A.main_acc, A.sub_acc, A.beginning_balance, A.coa_desc, A.remark,
326 CASE WHEN A.sign_journal = vSignDebt THEN SUM(A.gl_amount) ELSE 0 END AS gl_amount_debt,
327 CASE WHEN A.sign_journal = vSignCredit THEN SUM(A.gl_amount) ELSE 0 END AS gl_amount_credit,
328 (A.curr_code_trx || to_char(A.denominator_rate, '999,999.90') || ' = ' || A.gl_curr_code || to_char(A.numerator_rate, '999,999.90')) AS rate
329 FROM tr_gl_detail_ledger_card A
330 WHERE A.session_id = pSessionId
331 AND A.tenant_id = pTenantId
332 GROUP BY A.doc_type_id, A.doc_type_desc, A.doc_no, A.doc_date, A.sign_journal, A.curr_code_trx, A.journal_date, A.numerator_rate,
333 A.denominator_rate, A.sign_coa, A.gl_curr_code, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name, A.ou_rc_id, A.rc_name,
334 A.segment_id, A.segment_name, A.coa_id, A.main_acc, A.sub_acc, A.beginning_balance, A.coa_desc, A.remark
335-- ORDER BY A.branch_name, A.sub_bu_name, A.main_acc, A.sub_acc, A.rc_name, A.segment_name, A.coa_desc, A.doc_date, A.doc_no, A.curr_code_trx, A.gl_curr_code;
336 ORDER BY A.branch_name, A.sub_bu_name, A.coa_id, A.rc_name, A.segment_name, A.doc_date, A.doc_type_desc, A.doc_no, A.sign_journal;
337
338 RETURN NEXT pRefDetail ;
339
340 DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
341 DELETE FROM tr_gl_detail_ledger_card WHERE session_id = pSessionId;
342END;
343$BODY$
344 LANGUAGE plpgsql VOLATILE
345 COST 100
346 ROWS 1000;
347/