· 5 years ago · Oct 19, 2020, 09:10 AM
1-- Function: public.gl_financial_report(bigint, character varying, character varying, bigint)
2
3-- DROP FUNCTION public.gl_financial_report(bigint, character varying, character varying, bigint);
4
5CREATE OR REPLACE FUNCTION public.gl_financial_report(
6 bigint,
7 character varying,
8 character varying,
9 bigint)
10 RETURNS refcursor AS
11$BODY$
12DECLARE
13
14 pTenantId ALIAS FOR $1;
15 pSessionId ALIAS FOR $2;
16 pProcessNo ALIAS FOR $3;
17 pCounter ALIAS FOR $4;
18
19 pHasil REFCURSOR;
20 vReportMessageId bigint;
21 vTemplateReportId bigint;
22 vOuBuId bigint;
23 vOuId bigint;
24 vEmptyId bigint;
25 vStartYearMonth character varying(6);
26 vEndYearMonth character varying(6);
27 vFlagNo character varying(1);
28 vFlagYes character varying(1);
29 vFlagBegBalance character varying(10);
30 vFlagMutation character varying(10);
31 vFlagMutationDebit character varying(10);
32 vFlagMutationCredit character varying(10);
33 vFlagEndBalance character varying(10);
34 vFlagBranch character varying(1);
35 vFlagSubBu character varying(1);
36 vSignDebt character varying(1);
37 vSignCredit character varying(1);
38 vTypeData character varying(10);
39
40BEGIN
41
42 vFlagNo := 'N';
43 vFlagYes := 'Y';
44 vEmptyId := -99;
45 vFlagBegBalance := 'BEG';
46 vFlagMutation := 'MUT';
47 vFlagMutationDebit := 'MUTDEB';
48 vFlagMutationCredit := 'MUTCRD';
49 vFlagEndBalance := 'END';
50 vTypeData := 'DATA';
51 vSignDebt := 'D';
52 vSignCredit := 'C';
53
54 SELECT A.report_message_id INTO vReportMessageId
55 FROM t_report_message A
56 WHERE A.tenant_id = pTenantId AND
57 A.report_name = 'gl_financial_report' AND
58 A.report_no = pProcessNo;
59
60 SELECT CAST(A.report_parameter_value AS bigint) INTO vTemplateReportId
61 FROM t_report_parameter A
62 WHERE A.report_message_id = vReportMessageId AND
63 A.report_parameter_key = 'templateReportId';
64
65 SELECT CAST(A.report_parameter_value AS bigint) INTO vOuBuId
66 FROM t_report_parameter A
67 WHERE A.report_message_id = vReportMessageId AND
68 A.report_parameter_key = 'ouBuId';
69
70 SELECT CAST(A.report_parameter_value AS bigint) INTO vOuId
71 FROM t_report_parameter A
72 WHERE A.report_message_id = vReportMessageId AND
73 A.report_parameter_key = 'ouId' || pCounter;
74
75 SELECT CAST(A.report_parameter_value AS character varying(6)) INTO vStartYearMonth
76 FROM t_report_parameter A
77 WHERE A.report_message_id = vReportMessageId AND
78 A.report_parameter_key = 'startYearMonth' || pCounter;
79
80 SELECT CAST(A.report_parameter_value AS character varying(6)) INTO vEndYearMonth
81 FROM t_report_parameter A
82 WHERE A.report_message_id = vReportMessageId AND
83 A.report_parameter_key = 'endYearMonth' || pCounter;
84
85 DELETE FROM tt_gl_report_ou WHERE session_id = pSessionId;
86 DELETE FROM tt_gl_report_data WHERE session_id = pSessionId;
87
88 /*
89 * Cek tipe ouId apakah BU, Branch, atau Sub BU.
90 * Ambil daftar OU berdasarkan tipe ouId, startYearMonth, dan endYearMonth dari table gl_trial_balance.
91 * Lalu insert ke temp table tt_gl_report_ou.
92 */
93 IF vOuBuId = vOuId THEN
94
95 INSERT INTO tt_gl_report_ou (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id)
96 SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id
97 FROM gl_trial_balance A
98 WHERE A.tenant_id = pTenantId AND
99 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
100 A.ou_bu_id = vOuId
101 GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id;
102
103 ELSE
104 SELECT B.flg_branch, B.flg_sub_bu INTO vFlagBranch, vFlagSubBu
105 FROM t_ou A
106 INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
107 WHERE A.ou_id = vOuId;
108
109 IF vFlagBranch = vFlagYes THEN
110
111 INSERT INTO tt_gl_report_ou (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id)
112 SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id
113 FROM gl_trial_balance A
114 WHERE A.tenant_id = pTenantId AND
115 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
116 A.ou_bu_id = vOuBuId AND
117 A.ou_branch_id = vOuId
118 GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id;
119
120 ELSIF vFlagSubBu = vFlagYes THEN
121
122 INSERT INTO tt_gl_report_ou (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id)
123 SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id
124 FROM gl_trial_balance A
125 WHERE A.tenant_id = pTenantId AND
126 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
127 A.ou_bu_id = vOuBuId AND
128 A.ou_sub_bu_id = vOuId
129 GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id;
130
131 END IF;
132 END IF;
133
134 /*
135 * Ambil data gl_trial_balance berdasarkan COA
136 */
137 INSERT INTO tt_gl_report_data
138 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
139 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
140 amount)
141 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
142 A.coa_id, vEmptyId, A.sign_coa, D.sign_coa, D.flg_balance,
143 CASE D.flg_balance
144 WHEN vFlagBegBalance THEN A.beg_balance_1
145 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
146 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
147 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
148 WHEN vFlagEndBalance THEN A.end_balance_3
149 END
150 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D
151 WHERE A.tenant_id = B.tenant_id AND
152 A.ou_bu_id = B.ou_bu_id AND
153 A.ou_branch_id = B.ou_branch_id AND
154 A.ou_sub_bu_id = B.ou_sub_bu_id AND
155 C.template_report_item_id = D.template_report_item_id AND
156 A.tenant_id = D.tenant_id AND
157 A.coa_id = D.coa_id AND
158 A.tenant_id = pTenantId AND
159 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
160 B.session_id = pSessionId AND
161 C.template_report_id = vTemplateReportId AND
162 D.flg_group_coa = vFlagNo AND
163 D.ou_rc = vEmptyId AND
164 D.segment_id = vEmptyId;
165
166 /*
167 * Ambil data gl_trial_balance berdasarkan COA dan OU RC
168 */
169 INSERT INTO tt_gl_report_data
170 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
171 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
172 amount)
173 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
174 A.coa_id, vEmptyId, A.sign_coa, D.sign_coa, D.flg_balance,
175 CASE D.flg_balance
176 WHEN vFlagBegBalance THEN A.beg_balance_1
177 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
178 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
179 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
180 WHEN vFlagEndBalance THEN A.end_balance_3
181 END
182 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D
183 WHERE A.tenant_id = B.tenant_id AND
184 A.ou_bu_id = B.ou_bu_id AND
185 A.ou_branch_id = B.ou_branch_id AND
186 A.ou_sub_bu_id = B.ou_sub_bu_id AND
187 C.template_report_item_id = D.template_report_item_id AND
188 A.tenant_id = D.tenant_id AND
189 A.coa_id = D.coa_id AND
190 A.ou_rc_id = D.ou_rc AND
191 A.tenant_id = pTenantId AND
192 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
193 B.session_id = pSessionId AND
194 C.template_report_id = vTemplateReportId AND
195 D.flg_group_coa = vFlagNo AND
196 D.ou_rc <> vEmptyId AND
197 D.segment_id = vEmptyId;
198
199 /*
200 * Ambil data gl_trial_balance berdasarkan COA dan segment
201 */
202 INSERT INTO tt_gl_report_data
203 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
204 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
205 amount)
206 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
207 A.coa_id, vEmptyId, A.sign_coa, D.sign_coa, D.flg_balance,
208 CASE D.flg_balance
209 WHEN vFlagBegBalance THEN A.beg_balance_1
210 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
211 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
212 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
213 WHEN vFlagEndBalance THEN A.end_balance_3
214 END
215 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D
216 WHERE A.tenant_id = B.tenant_id AND
217 A.ou_bu_id = B.ou_bu_id AND
218 A.ou_branch_id = B.ou_branch_id AND
219 A.ou_sub_bu_id = B.ou_sub_bu_id AND
220 C.template_report_item_id = D.template_report_item_id AND
221 A.tenant_id = D.tenant_id AND
222 A.coa_id = D.coa_id AND
223 A.ou_rc_id = D.ou_rc AND
224 A.segmen_id = D.segment_id AND
225 A.tenant_id = pTenantId AND
226 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
227 B.session_id = pSessionId AND
228 C.template_report_id = vTemplateReportId AND
229 D.flg_group_coa = vFlagNo AND
230 D.ou_rc = vEmptyId AND
231 D.segment_id <> vEmptyId;
232
233 /*
234 * Ambil data gl_trial_balance berdasarkan COA, OU RC, dan segment
235 */
236 INSERT INTO tt_gl_report_data
237 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
238 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
239 amount)
240 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
241 A.coa_id, vEmptyId, A.sign_coa, D.sign_coa, D.flg_balance,
242 CASE D.flg_balance
243 WHEN vFlagBegBalance THEN A.beg_balance_1
244 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
245 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
246 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
247 WHEN vFlagEndBalance THEN A.end_balance_3
248 END
249 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D
250 WHERE A.tenant_id = B.tenant_id AND
251 A.ou_bu_id = B.ou_bu_id AND
252 A.ou_branch_id = B.ou_branch_id AND
253 A.ou_sub_bu_id = B.ou_sub_bu_id AND
254 C.template_report_item_id = D.template_report_item_id AND
255 A.tenant_id = D.tenant_id AND
256 A.coa_id = D.coa_id AND
257 A.ou_rc_id = D.ou_rc AND
258 A.segmen_id = D.segment_id AND
259 A.tenant_id = pTenantId AND
260 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
261 B.session_id = pSessionId AND
262 C.template_report_id = vTemplateReportId AND
263 D.flg_group_coa = vFlagNo AND
264 D.ou_rc <> vEmptyId AND
265 D.segment_id <> vEmptyId;
266
267
268
269 /*
270 * Ambil data gl_trial_balance berdasarkan Group COA
271 */
272 INSERT INTO tt_gl_report_data
273 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
274 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
275 amount)
276 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
277 A.coa_id, E.group_coa_id, A.sign_coa, D.sign_coa, D.flg_balance,
278 CASE D.flg_balance
279 WHEN vFlagBegBalance THEN A.beg_balance_1
280 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
281 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
282 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
283 WHEN vFlagEndBalance THEN A.end_balance_3
284 END
285 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D, m_coa E
286 WHERE A.tenant_id = B.tenant_id AND
287 A.ou_bu_id = B.ou_bu_id AND
288 A.ou_branch_id = B.ou_branch_id AND
289 A.ou_sub_bu_id = B.ou_sub_bu_id AND
290 C.template_report_item_id = D.template_report_item_id AND
291 A.tenant_id = D.tenant_id AND
292 A.coa_id = E.coa_id AND
293 E.group_coa_id = D.group_coa_id AND
294 A.tenant_id = pTenantId AND
295 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
296 B.session_id = pSessionId AND
297 C.template_report_id = vTemplateReportId AND
298 D.flg_group_coa = vFlagYes AND
299 D.ou_rc = vEmptyId AND
300 D.segment_id = vEmptyId;
301
302 /*
303 * Ambil data gl_trial_balance berdasarkan Group COA dan OU RC
304 */
305 INSERT INTO tt_gl_report_data
306 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
307 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
308 amount)
309 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
310 A.coa_id, E.group_coa_id, A.sign_coa, D.sign_coa, D.flg_balance,
311 CASE D.flg_balance
312 WHEN vFlagBegBalance THEN A.beg_balance_1
313 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
314 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
315 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
316 WHEN vFlagEndBalance THEN A.end_balance_3
317 END
318 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D, m_coa E
319 WHERE A.tenant_id = B.tenant_id AND
320 A.ou_bu_id = B.ou_bu_id AND
321 A.ou_branch_id = B.ou_branch_id AND
322 A.ou_sub_bu_id = B.ou_sub_bu_id AND
323 C.template_report_item_id = D.template_report_item_id AND
324 A.tenant_id = D.tenant_id AND
325 A.coa_id = E.coa_id AND
326 E.group_coa_id = D.group_coa_id AND
327 A.ou_rc_id = D.ou_rc AND
328 A.tenant_id = pTenantId AND
329 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
330 B.session_id = pSessionId AND
331 C.template_report_id = vTemplateReportId AND
332 D.flg_group_coa = vFlagYes AND
333 D.ou_rc <> vEmptyId AND
334 D.segment_id = vEmptyId;
335
336 /*
337 * Ambil data gl_trial_balance berdasarkan Group COA dan segment
338 */
339 INSERT INTO tt_gl_report_data
340 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
341 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
342 amount)
343 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
344 A.coa_id, E.group_coa_id, A.sign_coa, D.sign_coa, D.flg_balance,
345 CASE D.flg_balance
346 WHEN vFlagBegBalance THEN A.beg_balance_1
347 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
348 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
349 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
350 WHEN vFlagEndBalance THEN A.end_balance_3
351 END
352 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D, m_coa E
353 WHERE A.tenant_id = B.tenant_id AND
354 A.ou_bu_id = B.ou_bu_id AND
355 A.ou_branch_id = B.ou_branch_id AND
356 A.ou_sub_bu_id = B.ou_sub_bu_id AND
357 C.template_report_item_id = D.template_report_item_id AND
358 A.tenant_id = D.tenant_id AND
359 A.coa_id = E.coa_id AND
360 E.group_coa_id = D.group_coa_id AND
361 A.segmen_id = D.segment_id AND
362 A.tenant_id = pTenantId AND
363 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
364 B.session_id = pSessionId AND
365 C.template_report_id = vTemplateReportId AND
366 D.flg_group_coa = vFlagYes AND
367 D.ou_rc = vEmptyId AND
368 D.segment_id <> vEmptyId;
369
370 /*
371 * Ambil data gl_trial_balance berdasarkan Group COA, OU RC, dan segment
372 */
373 INSERT INTO tt_gl_report_data
374 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
375 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance,
376 amount)
377 SELECT pSessionId, A.tenant_id, C.var_name, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.ou_rc_id, A.segmen_id,
378 A.coa_id, E.group_coa_id, A.sign_coa, D.sign_coa, D.flg_balance,
379 CASE D.flg_balance
380 WHEN vFlagBegBalance THEN A.beg_balance_1
381 WHEN vFlagMutation THEN (A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3) - (A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3)
382 WHEN vFlagMutationDebit THEN A.mut_debit_1 + A.mut_debit_2 + A.mut_debit_3
383 WHEN vFlagMutationCredit THEN A.mut_credit_1 + A.mut_credit_2 + A.mut_credit_3
384 WHEN vFlagEndBalance THEN A.end_balance_3
385 END
386 FROM gl_trial_balance A, tt_gl_report_ou B, gl_template_report_item C, gl_template_report_data D, m_coa E
387 WHERE A.tenant_id = B.tenant_id AND
388 A.ou_bu_id = B.ou_bu_id AND
389 A.ou_branch_id = B.ou_branch_id AND
390 A.ou_sub_bu_id = B.ou_sub_bu_id AND
391 C.template_report_item_id = D.template_report_item_id AND
392 A.tenant_id = D.tenant_id AND
393 A.coa_id = E.coa_id AND
394 E.group_coa_id = D.group_coa_id AND
395 A.ou_rc_id = D.ou_rc AND
396 A.segmen_id = D.segment_id AND
397 A.tenant_id = pTenantId AND
398 A.date_year_month BETWEEN vStartYearMonth AND vEndYearMonth AND
399 B.session_id = pSessionId AND
400 C.template_report_id = vTemplateReportId AND
401 D.flg_group_coa = vFlagYes AND
402 D.ou_rc <> vEmptyId AND
403 D.segment_id <> vEmptyId;
404
405 /*
406 * Nilai positif dan negatif berdasarkan sign coa di master dan di report:
407 *
408 * sign coa BEG MUT(total debit-total credit) MUTDEB(deb1+deb2+deb3) MUTCRD(crd1+crd2+crd3) END
409 * ----------- --- ----------------------------- ---------------------- ---------------------- ---
410 * MST=D,RPT=D + + + - +
411 * MST=D,RPT=C - - - + -
412 * MST=C,RPT=C + - - + +
413 * MST=C,RPT=D - + + - -
414 *
415 * NOTE:
416 * MST=sign coa di master coa
417 * RPT=sign coa pada report
418 * (-)=nilai atau hasil formula dikalikan -1
419 * (+)=nilai sesuai dengan formula (MUT, MUTDEB, MUTCRD) atau nilai apa adanya (BEG, END)
420 */
421 UPDATE tt_gl_report_data SET amount = amount * -1
422 WHERE sign_coa = vSignDebt AND
423 sign_coa_report = vSignDebt AND
424 flg_balance = vFlagMutationCredit AND
425 session_id = pSessionId;
426
427 UPDATE tt_gl_report_data SET amount = amount * -1
428 WHERE sign_coa = vSignDebt AND
429 sign_coa_report = vSignCredit AND
430 flg_balance <> vFlagMutationCredit AND
431 session_id = pSessionId;
432
433 UPDATE tt_gl_report_data SET amount = amount * -1
434 WHERE sign_coa = vSignCredit AND
435 sign_coa_report = vSignCredit AND
436 flg_balance IN (vFlagMutation, vFlagMutationDebit) AND
437 session_id = pSessionId;
438
439 UPDATE tt_gl_report_data SET amount = amount * -1
440 WHERE sign_coa = vSignCredit AND
441 sign_coa_report = vSignDebt AND
442 flg_balance NOT IN (vFlagMutation, vFlagMutationDebit) AND
443 session_id = pSessionId;
444
445 /*
446 * Insert data gl_template_report_item yg belum ada di tt_gl_report_data
447 */
448 INSERT INTO tt_gl_report_data
449 (session_id, tenant_id, var_name, ou_bu_id, ou_branch_id, ou_sub_bu_id, ou_rc_id, segmen_id,
450 coa_id, group_coa_id, sign_coa, sign_coa_report, flg_balance, amount)
451 SELECT pSessionId, C.tenant_id, C.var_name, vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
452 vEmptyId, vEmptyId, vSignDebt, vSignDebt, vFlagEndBalance, 0
453 FROM gl_template_report_item C
454 WHERE C.tenant_id = pTenantId AND
455 C.template_report_id = vTemplateReportId AND
456 C.type_value = vTypeData AND
457 NOT EXISTS (SELECT 1 FROM tt_gl_report_data E
458 WHERE E.session_id = pSessionId AND
459 E.tenant_id = C.tenant_id AND
460 E.var_name = C.var_name);
461
462 /*
463 * Return hasil rekap
464 */
465 Open pHasil FOR
466 SELECT var_name, SUM(amount)
467 FROM tt_gl_report_data
468 WHERE session_id = pSessionId
469 GROUP BY var_name
470 ORDER BY var_name;
471
472 RETURN pHasil;
473
474 DELETE FROM tt_gl_report_ou WHERE session_id = pSessionId;
475 DELETE FROM tt_gl_report_data WHERE session_id = pSessionId;
476
477 END;
478$BODY$
479 LANGUAGE plpgsql VOLATILE
480 COST 100;
481ALTER FUNCTION public.gl_financial_report(bigint, character varying, character varying, bigint)
482 OWNER TO sts;
483