· 3 years ago · Mar 21, 2022, 11:40 AM
1CREATE OR REPLACE FUNCTION r_buku_besar(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, character varying, character varying)
2 RETURNS SETOF refcursor AS
3$BODY$
4DECLARE
5 pRefDetailBukuBesar REFCURSOR := 'refDetailBukuBesar';
6 pSessionId ALIAS FOR $1;
7 pTenantId ALIAS FOR $2;
8 pUserId ALIAS FOR $3;
9 pRoleId ALIAS FOR $4;
10 pDatetime ALIAS FOR $5;
11 pOuBuId ALIAS FOR $6;
12 pOuBranchId ALIAS FOR $7;
13 pOuSubBuId ALIAS FOR $8;
14 pCoaId ALIAS FOR $9;
15 pPeriodFrom ALIAS FOR $10;
16 pPeriodTo ALIAS FOR $11;
17
18 vEmptyId bigint := -99;
19 vEmptyString character varying := '';
20 vEmptyAmount numeric := 0;
21 vStatusDocDraft character varying := 'D';
22 vStatusDocRelease character varying := 'R';
23 vSignDebt character varying := 'D';
24 vSignCredit character varying := 'C';
25 vTypeRateCom character varying := 'COM';
26 vCurrGL character varying(5);
27 vFlgDataDetail character varying := 'BBBB';
28 vFlgDataSaldo character varying := 'AAAA';
29 vCoaDescSaldoAwal character varying := 'SALDO AWAL';
30
31 vFilterBranch text := '';
32 vFilterSubBu text := '';
33 vFilterBeginingBranch text := '';
34 vFilterBeginingSubBu text := '';
35 vRoundingDigit integer;
36
37BEGIN
38 vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
39
40 DELETE FROM tr_gl_beginning_balance_buku_besar WHERE session_id = pSessionId;
41 DELETE FROM tr_gl_detail_buku_besar WHERE session_id = pSessionId;
42 DELETE FROM tr_gl_data_journal_trx_for_buku_besar WHERE session_id = pSessionId;
43
44 /* ambil nilai pembulatan*/
45 vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
46
47 /* branchId <> -99*/
48 IF (pOuBranchId <> vEmptyId) THEN
49 vFilterBranch := ' AND A.coa_journal_ou_branch_id = ' || pOuBranchId;
50 END IF;
51
52 /* subBuId <> -99*/
53 IF (pOuSubBuId <> vEmptyId) THEN
54 vFilterSubBu := ' AND A.coa_journal_ou_branch_id = ' || pOuSubBuId;
55 END IF;
56
57 /* branchId <> -99*/
58 IF (pOuBranchId <> vEmptyId) THEN
59 vFilterBeginingBranch := ' AND A.ou_branch_id = ' || pOuBranchId;
60 END IF;
61
62 /* subBuId <> -99*/
63 IF (pOuSubBuId <> vEmptyId) THEN
64 vFilterBeginingSubBu := ' AND A.ou_sub_bu_id = ' || pOuSubBuId;
65 END IF;
66
67 /*
68 RAISE NOTICE 'vFilterSubBu: %, vFilterBranch: %, pSessionId: %, pTenantId: %, pOuBuId: %, pCoaId: %, pPeriodFrom: %, pPeriodTo: %, vFlgDataDetail: %, vStatusDocDraft: %, vTypeRateCom: %, vCurrGL: %, vRoundingDigit: %',
69 vFilterSubBu, vFilterBranch, pSessionId, pTenantId, pOuBuId, pCoaId, pPeriodFrom, pPeriodTo, vFlgDataDetail, vStatusDocDraft, vTypeRateCom, vCurrGL, vRoundingDigit;
70 */
71
72 /*
73 * ambil saldo awal untuk buku besar
74 */
75 PERFORM gl_get_buku_besar_beginning_balance(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pOuBuId, pOuBranchId, pOuSubBuId, pCoaId, pPeriodFrom);
76
77
78 -- Ambil daftar journal_trx_id dan data COA sesuai filter (exclude filter ou branch dan ou sub bu)
79 INSERT INTO tr_gl_data_journal_trx_for_buku_besar(
80 session_id, tenant_id, journal_trx_id, ou_bu_id,
81 coa_id, coa_name, coa_desc, sign_coa, ou_branch_id, ou_sub_bu_id)
82 SELECT pSessionId, A.tenant_id, B.journal_trx_id, B.ou_bu_id,
83 A.coa_id, C.main_acc||'-'||C.sub_acc AS coa_name, C.coa_desc, C.sign_coa,
84 CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_branch_id ELSE A.ou_branch_id END,
85 CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END
86 FROM gl_journal_trx_item A
87 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
88 INNER JOIN m_coa C ON A.coa_id = C.coa_id
89 INNER JOIN dt_date D ON B.doc_date = D.string_date
90 WHERE A.tenant_id = pTenantId
91 AND B.ou_bu_id = pOuBuId
92 AND A.coa_id = pCoaId
93 AND D.year_month_date BETWEEN pPeriodFrom AND pPeriodTo
94 UNION
95 SELECT pSessionId, A.tenant_id, B.journal_trx_id, B.ou_bu_id,
96 A.coa_id, C.main_acc||'-'||C.sub_acc AS coa_name, C.coa_desc, C.sign_coa,
97 CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_branch_id ELSE A.ou_branch_id END,
98 CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END
99 FROM gl_journal_trx_mapping A
100 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
101 INNER JOIN m_coa C ON A.coa_id = C.coa_id
102 INNER JOIN dt_date D ON B.doc_date = D.string_date
103 WHERE A.tenant_id = pTenantId
104 AND B.ou_bu_id = pOuBuId
105 AND A.coa_id = pCoaId
106 AND D.year_month_date BETWEEN pPeriodFrom AND pPeriodTo
107 UNION
108 SELECT pSessionId, A.tenant_id, B.journal_trx_id, B.ou_bu_id,
109 A.coa_id, C.main_acc||'-'||C.sub_acc AS coa_name, C.coa_desc, C.sign_coa,
110 CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_branch_id ELSE A.ou_branch_id END,
111 CASE WHEN A.ou_branch_id = vEmptyId AND A.ou_sub_bu_id = vEmptyId THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END
112 FROM gl_journal_trx_fx A
113 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
114 INNER JOIN m_coa C ON A.coa_id = C.coa_id
115 INNER JOIN dt_date D ON B.doc_date = D.string_date
116 WHERE A.tenant_id = pTenantId
117 AND B.ou_bu_id = pOuBuId
118 AND A.coa_id = pCoaId
119 AND D.year_month_date BETWEEN pPeriodFrom AND pPeriodTo
120 ORDER BY journal_trx_id;
121
122 /*
123 * Ambil data detail untuk buku besar yang status doc nya R
124 */
125 EXECUTE '
126 INSERT INTO tr_gl_detail_buku_besar(
127 session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
128 ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id, doc_type_desc,
129 doc_id, doc_no, doc_date, remark, coa_journal_id, coa_journal_desc, sign_journal,
130 curr_code_trx, amount_trx, numerator_rate, denominator_rate, gl_curr_code,
131 gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
132 coa_journal_ou_branch_id,
133 coa_journal_branch_name,
134 coa_journal_ou_sub_bu_id,
135 coa_journal_sub_bu_name )
136 WITH gl_journal_trx_details AS (
137 -- Ambil details journal dimana coa nya bukan coa filter
138
139 SELECT B.tenant_id, D.ou_bu_id,
140 D.ou_branch_id,D.ou_sub_bu_id,
141 D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
142 B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
143 C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
144 A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc,
145 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id,
146 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
147 FROM gl_journal_trx_item A
148 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
149 INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
150 INNER JOIN m_coa C ON A.coa_id = C.coa_id
151 WHERE A.tenant_id = $2
152 AND A.coa_id <> D.coa_id
153 AND B.status_doc = $4
154
155 UNION ALL
156
157 SELECT B.tenant_id, B.ou_bu_id,
158 D.ou_branch_id, D.ou_sub_bu_id,
159 D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
160 B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
161 C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
162 A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc,
163 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id,
164 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
165 FROM gl_journal_trx_mapping A
166 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
167 INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
168 INNER JOIN m_coa C ON A.coa_id = C.coa_id
169 WHERE A.tenant_id = $2
170 AND A.coa_id <> D.coa_id
171 AND B.status_doc = $4
172
173 UNION ALL
174
175 SELECT B.tenant_id, B.ou_bu_id,
176 D.ou_branch_id, D.ou_sub_bu_id,
177 D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
178 B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
179 C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
180 A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc,
181 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id,
182 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
183 FROM gl_journal_trx_fx A
184 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
185 INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
186 INNER JOIN m_coa C ON A.coa_id = C.coa_id
187 WHERE A.tenant_id = $2
188 AND A.coa_id <> D.coa_id
189 AND B.status_doc = $4
190
191 )
192 SELECT $1 AS session_id, A.tenant_id, A.ou_bu_id, f_get_ou_name(A.ou_bu_id) AS ou_bu_name,
193 A.ou_branch_id, f_get_ou_name(A.ou_branch_id) AS branch_name,
194 A.ou_sub_bu_id, f_get_ou_name(A.ou_sub_bu_id) AS sub_bu_name,
195 A.coa_id, A.coa_desc, A.sign_coa, A.doc_type_id, A.doc_type_desc,
196 A.doc_id, A.doc_no, A.doc_date, A.remark, A.coa_journal_id, A.coa_journal_desc, A.sign_journal,
197 A.curr_code_trx, A.amount_trx, A.numerator_rate, A.denominator_rate, A.gl_curr_code,
198 A.gl_amount, $3 AS flg_data, A.status_doc, A.coa_name, A.coa_journal_name,
199 A.coa_journal_ou_branch_id,
200 f_get_ou_name(A.coa_journal_ou_branch_id) AS coa_journal_branch_name,
201 A.coa_journal_ou_sub_bu_id,
202 f_get_ou_name(A.coa_journal_ou_branch_id) AS coa_journal_sub_bu_name
203 FROM gl_journal_trx_details A
204 WHERE A.tenant_id = $2 '||
205 vFilterBranch ||
206 vFilterSubBu || '
207 ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.coa_journal_id, A.doc_date, A.doc_no
208 '
209 USING pSessionId, pTenantId, vFlgDataDetail, vStatusDocRelease, vEmptyId;
210
211 /*
212 * Ambil data detail untuk buku besar yang status doc nya D
213 */
214 EXECUTE '
215 INSERT INTO tr_gl_detail_buku_besar(
216 session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
217 ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id, doc_type_desc,
218 doc_id, doc_no, doc_date, remark, coa_journal_id, coa_journal_desc, sign_journal,
219 curr_code_trx, amount_trx, numerator_rate, denominator_rate, gl_curr_code,
220 gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
221 coa_journal_ou_branch_id,
222 coa_journal_branch_name,
223 coa_journal_ou_sub_bu_id,
224 coa_journal_sub_bu_name )
225
226 WITH gl_journal_trx_details AS (
227 -- Ambil details journal dimana coa nya bukan coa filter
228
229 SELECT B.tenant_id, B.ou_bu_id,
230 D.ou_branch_id, D.ou_sub_bu_id,
231 D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
232 B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
233 C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
234 A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc, A.type_rate, A.journal_date,
235 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id ,
236 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
237 FROM gl_journal_trx_item A
238 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
239 INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
240 INNER JOIN m_coa C ON A.coa_id = C.coa_id
241 WHERE A.tenant_id = $2
242 AND A.coa_id <> D.coa_id
243 AND B.status_doc = $4
244
245 UNION ALL
246
247 SELECT B.tenant_id, B.ou_bu_id,
248 D.ou_branch_id, D.ou_sub_bu_id,
249 D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
250 B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
251 C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
252 A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc, A.type_rate, A.journal_date,
253 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id ,
254 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
255 FROM gl_journal_trx_mapping A
256 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
257 INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
258 INNER JOIN m_coa C ON A.coa_id = C.coa_id
259 WHERE A.tenant_id = $2
260 AND A.coa_id <> D.coa_id
261 AND B.status_doc = $4
262
263 UNION ALL
264
265 SELECT B.tenant_id, B.ou_bu_id,
266 D.ou_branch_id, D.ou_sub_bu_id,
267 D.coa_id, D.coa_name, D.coa_desc, D.sign_coa, B.doc_type_id, f_get_doc_desc(B.doc_type_id) AS doc_type_desc,
268 B.doc_id, B.doc_no, B.doc_date, A.remark, A.coa_id AS coa_journal_id, C.main_acc||''-''||C.sub_acc AS coa_journal_name,
269 C.coa_desc AS coa_journal_desc, A.sign_journal, A.curr_code AS curr_code_trx, A.amount AS amount_trx,
270 A.numerator_rate, A.denominator_rate, A.gl_curr_code, A.gl_amount, B.status_doc, $6 AS type_rate, A.journal_date,
271 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_branch_id ELSE A.ou_branch_id END ) AS coa_journal_ou_branch_id ,
272 ( CASE WHEN A.ou_branch_id = $5 AND A.ou_sub_bu_id = $5 THEN B.ou_sub_bu_id ELSE A.ou_sub_bu_id END ) AS coa_journal_ou_sub_bu_id
273 FROM gl_journal_trx_fx A
274 INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
275 INNER JOIN tr_gl_data_journal_trx_for_buku_besar D ON B.journal_trx_id = D.journal_trx_id AND D.session_id = $1
276 INNER JOIN m_coa C ON A.coa_id = C.coa_id
277 WHERE A.tenant_id = $2
278 AND A.coa_id <> D.coa_id
279 AND B.status_doc = $4
280
281 ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id, coa_journal_id, doc_date, doc_no
282
283 ), tt_gl_admin_journal_rate AS (
284 SELECT A.type_rate, A.journal_date, A.curr_code_trx, $7 AS gl_curr_code, f_get_exchange_rate($2, A.type_rate, A.journal_date, A.curr_code_trx, $7) AS data_exchange_rate
285 FROM (
286 SELECT A.journal_date, A.type_rate, A.curr_code_trx
287 FROM gl_journal_trx_details A
288 GROUP BY journal_date, type_rate, curr_code_trx
289 ) A
290 )
291 SELECT $1 AS session_id, A.tenant_id, A.ou_bu_id, f_get_ou_name(A.ou_bu_id) AS ou_bu_name,
292 A.ou_branch_id, f_get_ou_name(A.ou_branch_id) AS branch_name,
293 A.ou_sub_bu_id, f_get_ou_name(A.ou_sub_bu_id) AS sub_bu_name,
294 A.coa_id, A.coa_desc, A.sign_coa, A.doc_type_id, A.doc_type_desc,
295 A.doc_id, A.doc_no, A.doc_date, A.remark, A.coa_journal_id, A.coa_journal_desc, A.sign_journal,
296 A.curr_code_trx, A.amount_trx, A.numerator_rate, A.denominator_rate, CASE WHEN (A.gl_curr_code is null OR TRIM(A.gl_curr_code) = '''') THEN $7 ELSE A.gl_curr_code END gl_curr_code,
297 ROUND(A.amount_trx * (B.data_exchange_rate).numerator_rate / (B.data_exchange_rate).denominator_rate, $8) AS gl_amount,
298 $3 AS flg_data, A.status_doc, A.coa_name, A.coa_journal_name,
299 A.coa_journal_ou_branch_id,
300 f_get_ou_name(A.coa_journal_ou_branch_id) AS coa_journal_branch_name,
301 A.coa_journal_ou_sub_bu_id,
302 f_get_ou_name(A.coa_journal_ou_sub_bu_id) AS coa_journal_sub_bu_name
303 FROM gl_journal_trx_details A
304 INNER JOIN tt_gl_admin_journal_rate B ON A.type_rate = B.type_rate AND A.journal_date = B.journal_date AND A.curr_code_trx = B.curr_code_trx
305 WHERE A.tenant_id = $2 '||
306 vFilterBranch ||
307 vFilterSubBu || '
308 ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.coa_journal_id, A.doc_date, A.doc_no
309 '
310 USING pSessionId, pTenantId, vFlgDataDetail, vStatusDocDraft, vEmptyId, vTypeRateCom, vCurrGL, vRoundingDigit;
311
312 /*
313 * Insert data ke table temporary, untuk transaksi yang nggak ada saldo nya (flg_data = AAAA <-- untuk data saldo)
314 *
315 * perubahan 2017-12-05, saldo awal hanya muncul 1 kali diatas, perhitungan saldo berdasarkan ou bu dan coa yang dipilih. Kondisi sekarang ou bu dan coa harus dipilih
316 * Membuatkan data saldo jika saldo awal 0
317 */
318 INSERT INTO tr_gl_detail_buku_besar(
319 session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
320 ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id,
321 doc_type_desc, doc_id, doc_no, doc_date, remark,
322 coa_journal_id, coa_journal_desc, sign_journal,
323 curr_code_trx, amount_trx, gl_curr_code,
324 gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
325 coa_journal_ou_branch_id,
326 coa_journal_branch_name,
327 coa_journal_ou_sub_bu_id,
328 coa_journal_sub_bu_name )
329
330 SELECT pSessionId AS session_id, pTenantId AS tenant_id, pOuBuId AS ou_bu_id, f_get_ou_name(pOuBuId) AS ou_bu_name,
331 vEmptyId AS ou_branch_id, vEmptyString AS branch_name, vEmptyId AS ou_sub_bu_id, vEmptyString AS sub_bu_name,
332 A.coa_id, vCoaDescSaldoAwal AS coa_desc_saldo_awal, A.sign_coa, vEmptyId AS doc_type_id,
333 vEmptyString AS doc_type_desc, vEmptyId AS doc_id, vEmptyString AS doc_no, vEmptyString AS doc_date,
334 vEmptyString AS remark, vEmptyId AS coa_journal_id, vEmptyString AS coa_journal_desc, A.sign_coa AS sign_journal,
335 vEmptyString AS curr_code_trx, vEmptyAmount AS amount_trx, vEmptyString AS gl_curr_code,
336 vEmptyAmount AS gl_amount, vFlgDataSaldo AS flg_data, vEmptyString AS status_doc, A.main_acc||'-'||A.sub_acc AS coa_name, vEmptyString AS coa_journal_name,
337 vEmptyId,
338 vEmptyString,
339 vEmptyId,
340 vEmptyString
341 FROM m_coa A
342 WHERE A.coa_id = pCoaId
343 AND NOT EXISTS (SELECT 1 FROM tr_gl_beginning_balance_buku_besar B
344 WHERE B.session_id = pSessionId
345 AND B.tenant_id = pTenantId
346 AND B.ou_bu_id = pOuBuId
347 AND B.coa_id = A.coa_id);
348
349
350 -- Ambil data beginning balance untuk di masukan ke table temporary
351 EXECUTE '
352 INSERT INTO tr_gl_detail_buku_besar(
353 session_id, tenant_id, ou_bu_id, ou_bu_name, ou_branch_id, branch_name,
354 ou_sub_bu_id, sub_bu_name, coa_id, coa_desc, sign_coa, doc_type_id, doc_type_desc,
355 doc_id, doc_no, doc_date, remark, coa_journal_id, coa_journal_desc, sign_journal,
356 curr_code_trx, amount_trx, gl_curr_code,
357 gl_amount, flg_data, status_doc, coa_name, coa_journal_name,
358 coa_journal_ou_branch_id,
359 coa_journal_branch_name,
360 coa_journal_ou_sub_bu_id,
361 coa_journal_sub_bu_name )
362 SELECT $1 AS session_id, A.tenant_id, A.ou_bu_id, f_get_ou_name(A.ou_bu_id) AS ou_bu_name,
363 A.ou_branch_id, f_get_ou_name(A.ou_branch_id) AS branch_name,
364 A.ou_sub_bu_id, f_get_ou_name(A.ou_sub_bu_id) AS sub_bu_name,
365 A.coa_id, $6 AS coa_desc_saldo_awal, B.sign_coa, $7 AS doc_type_id, $8 AS doc_type_desc,
366 $7 AS doc_id, $8 AS doc_no, $8 AS doc_date, $8 AS remark, $7 AS coa_journal_id, $8 AS coa_journal_desc, B.sign_coa AS sign_journal,
367 $8 AS curr_code_trx, $9 AS amount_trx, $8 AS gl_curr_code,
368 A.beginning_balance AS gl_amount, $5 AS flg_data, $8 AS status_doc,
369 B.main_acc||''-''||B.sub_acc AS coa_name, $8 AS coa_journal_name,
370 $7,
371 $8,
372 $7,
373 $8
374 FROM tr_gl_beginning_balance_buku_besar A
375 INNER JOIN m_coa B ON A.coa_id = B.coa_id
376 WHERE A.session_id = $1
377 AND A.tenant_id = $2
378 AND A.ou_bu_id = $3
379 AND A.coa_id = $4 '||
380 vFilterBeginingBranch ||
381 vFilterBeginingSubBu || '
382 ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id
383 '
384 USING pSessionId, pTenantId, pOuBuId, pCoaId, vFlgDataSaldo, vCoaDescSaldoAwal, vEmptyId, vEmptyString, vEmptyAmount;
385
386 /* header result*/
387
388 Open pRefDetailBukuBesar FOR
389 WITH data_details_buku_besar AS (
390 SELECT A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
391 A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
392 A.curr_code_trx, CAST(SUM(A.amount_trx) AS CHARACTER VARYING(50)) AS amount_trx, A.gl_curr_code, SUM(A.gl_amount) AS gl_amount,
393 (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,
394 CASE WHEN A.sign_journal = vSignDebt THEN CAST(SUM(A.gl_amount) AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_credit,
395 CASE WHEN A.sign_journal = vSignCredit THEN CAST(SUM(A.gl_amount) AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_debt,
396 CASE WHEN A.sign_journal = A.sign_coa THEN (SUM(A.gl_amount) * -1) ELSE SUM(A.gl_amount) END AS balance_amount,
397 A.flg_data, A.numerator_rate, A.denominator_rate, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
398 A.coa_id, A.coa_journal_id,
399 A.coa_journal_branch_name,
400 A.coa_journal_sub_bu_name
401 FROM tr_gl_detail_buku_besar A
402 WHERE A.session_id = pSessionId
403 AND A.tenant_id = pTenantId
404 AND A.flg_data = vFlgDataDetail
405 GROUP BY A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
406 A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
407 A.curr_code_trx, A.gl_curr_code, A.flg_data, A.numerator_rate, A.denominator_rate, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
408 A.coa_id, A.coa_journal_id,A.doc_no,A.coa_journal_branch_name,A.coa_journal_sub_bu_name
409 UNION ALL
410
411 SELECT A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
412 A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
413 A.curr_code_trx, CAST(A.amount_trx AS CHARACTER VARYING(50)) AS amount_trx, A.gl_curr_code, A.gl_amount,
414 vEmptyString AS rate,
415 CASE WHEN A.sign_coa = vSignCredit THEN CAST(A.gl_amount AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_credit,
416 CASE WHEN A.sign_coa = vSignDebt THEN CAST(A.gl_amount AS CHARACTER VARYING(50)) ELSE CAST(vEmptyAmount AS CHARACTER VARYING(5)) END AS gl_amount_debt,
417 A.gl_amount AS balance_amount,
418 A.flg_data, A.numerator_rate, A.denominator_rate, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
419 A.coa_id, A.coa_journal_id,
420 A.coa_journal_branch_name,
421 A.coa_journal_sub_bu_name
422 FROM tr_gl_detail_buku_besar A
423 WHERE A.session_id = pSessionId
424 AND A.tenant_id = pTenantId
425 AND A.flg_data = vFlgDataSaldo
426 ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id, flg_data, doc_date, doc_no, coa_journal_id, sign_journal
427 --ORDER BY ou_bu_id, ou_branch_id, ou_sub_bu_id, flg_data, coa_journal_id, doc_date, doc_no
428 )
429 SELECT A.ou_bu_name, A.branch_name, A.sub_bu_name, A.coa_name, A.coa_desc, A.sign_coa,
430 A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.coa_journal_name, A.coa_journal_desc, A.sign_journal,
431 A.curr_code_trx, A.amount_trx, A.gl_curr_code, A.gl_amount, A.rate, A.gl_amount_credit, A.gl_amount_debt,
432 CAST(SUM(A.balance_amount) OVER (PARTITION BY A.ou_bu_id ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.flg_data,A.doc_date, A.doc_no, A.coa_journal_id) AS CHARACTER VARYING(50)) AS balance_amount,
433 A.flg_data, A.numerator_rate, A.denominator_rate, A.coa_journal_branch_name,A.coa_journal_sub_bu_name
434 FROM data_details_buku_besar A
435 ORDER BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.flg_data, A.doc_date, A.doc_no, A.coa_journal_id ;
436
437 RETURN NEXT pRefDetailBukuBesar ;
438
439
440 DELETE FROM tr_gl_beginning_balance_buku_besar WHERE session_id = pSessionId;
441 DELETE FROM tr_gl_detail_buku_besar WHERE session_id = pSessionId;
442 DELETE FROM tr_gl_data_journal_trx_for_buku_besar WHERE session_id = pSessionId;
443
444END;
445$BODY$
446 LANGUAGE plpgsql VOLATILE
447 COST 100;
448 /