· 4 years ago · Mar 25, 2021, 07:50 AM
1-- Function: r_ledger_card_report_csv(bigint, bigint, character varying, character varying)
2
3-- DROP FUNCTION r_ledger_card_report_csv(bigint, bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION r_ledger_card_report_csv(
6 bigint,
7 bigint,
8 character varying,
9 character varying)
10 RETURNS SETOF refcursor AS
11$BODY$
12DECLARE
13 pRefHeader REFCURSOR := 'refHeader';
14 pRefDetail REFCURSOR := 'refDetail';
15
16 pTenantId ALIAS FOR $1;
17 pReportMessageId ALIAS FOR $2;
18 pSessionId ALIAS FOR $3;
19 pProcessNo ALIAS FOR $4;
20
21 vEmptyId bigint := -99;
22 vEmptyString character varying := '';
23 vEmptyAmount numeric := 0;
24 vYes character varying := 'Y';
25 vStatusDocTrx character varying := '';
26 vStatusDocDraft character varying := 'D';
27 vStatusDocRelease character varying := 'R';
28 vSignDebt character varying := 'D';
29 vSignCredit character varying := 'C';
30 vTypeRateCom character varying := 'COM';
31 vCurrGL character varying(5);
32 vJoinOuRc text := '';
33 vJoinSegment text := '';
34 vFilterBranch text := '';
35 vFilterSubBu text := '';
36 vFilterGroupCoa text := '';
37 vFilterOuRc text := '';
38 vFilterSegment text := '';
39 vFilterCoaMainAcc text := '';
40 vFilterCoaSubAcc text := '';
41 vRoundingDigit integer;
42 vTypeSaldoAwal character varying := 'SALDO AWAL';
43
44 vUserId bigint;
45 vRoleId bigint;
46 vDatetime character varying;
47 vOuBuId bigint;
48 vOuBranchId bigint;
49 vOuSubBuId bigint;
50 vGroupCoaId bigint;
51 vOuRc character varying;
52 vSegment character varying;
53 vPeriodFrom character varying;
54 vPeriodTo character varying;
55 vMainAcc character varying;
56 vSubAcc character varying;
57
58BEGIN
59
60 vUserId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'userId') AS bigint);
61 vRoleId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'roleId') AS bigint);
62 vDatetime := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'datetime') AS character varying);
63 vOuBuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouBuId') AS bigint);
64 vOuBranchId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouBranchId') AS bigint);
65 vOuSubBuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouSubBuId') AS bigint);
66 vGroupCoaId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'groupCoaId') AS bigint);
67 vOuRc := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouRc') AS character varying);
68 vSegment := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'segment') AS character varying);
69 vPeriodFrom := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'periodFrom') AS character varying);
70 vPeriodTo := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'periodTo') AS character varying);
71 vMainAcc := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'mainAcc') AS character varying);
72 vSubAcc := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'subAcc') AS character varying);
73
74 vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
75
76 DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
77 DELETE FROM tr_gl_detail_ledger_card_csv WHERE session_id = pSessionId;
78
79 /* ambil nilai pembulatan*/
80 vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
81
82 /* vJoinOuRc, jika ouRc diisi */
83 IF (vOuRc <> vEmptyString) THEN
84 vJoinOuRc := ' INNER JOIN t_ou K ON B.ou_rc_id = K.ou_id
85 INNER JOIN t_ou_type L ON K.ou_type_id = L.ou_type_id AND L.flg_accounting = '''|| vYes || '''';
86 END IF;
87
88 /* vJoinSegment, jika segment diisi*/
89 IF (vSegment <> vEmptyString) THEN
90 vJoinSegment := ' INNER JOIN m_segment_coa M ON B.segmen_id = M.segment_coa_id ';
91 END IF;
92
93 /* branchId <> -99*/
94 IF (vOuBranchId <> vEmptyId) THEN
95 vFilterBranch := ' AND B.ou_branch_id = ' || vOuBranchId;
96 END IF;
97
98 /* subBuId <> -99*/
99 IF (vOuSubBuId <> vEmptyId) THEN
100 vFilterSubBu := ' AND B.ou_sub_bu_id = ' || vOuSubBuId;
101 END IF;
102
103 /* gorupCoaId <> -99*/
104 IF (vGroupCoaId <> vEmptyId) THEN
105 vFilterGroupCoa := ' AND E.group_coa_id = ' || vGroupCoaId;
106 END IF;
107
108 /* jika ouRc diisi*/
109 IF (vOuRc <> vEmptyString) THEN
110 vFilterOuRc := ' AND UPPER(K.ou_name) LIKE UPPER(''%' || vOuRc || '%'')';
111 END IF;
112
113 /* jika segment diisi*/
114 IF (vSegment <> vEmptyString) THEN
115 vFilterSegment := ' AND UPPER(M.segment_coa_name) LIKE UPPER(''%' || vSegment || '%'')';
116 END IF;
117
118 /* jika main acc diisi*/
119 IF (vMainAcc <> vEmptyString) THEN
120 vFilterCoaMainAcc := ' AND E.main_acc LIKE ''' || vMainAcc || '%''';
121 END IF;
122
123 /* jika sub acc diisi*/
124 IF (vSubAcc <> vEmptyString) THEN
125 vFilterCoaSubAcc := ' AND E.sub_acc LIKE ''' || vSubAcc || '%''';
126 END IF;
127
128 /*
129 * ambil saldo awal ledger card
130 */
131 PERFORM gl_get_ledger_card_beginning_balance_csv(pSessionId, pTenantId, vUserId, vRoleId, vDatetime, vOuBuId, vOuBranchId, vOuSubBuId, vGroupCoaId, vOuRc, vSegment, vPeriodFrom, vMainAcc, vSubAcc);
132
133 /* ambil data detail yang, status doc = R
134 * 1) Ada transaksi tapi tidak ada saldo
135 * 2) Ada transaksi dan ada saldo awal
136 */
137 EXECUTE '
138 INSERT INTO tr_gl_detail_ledger_card_csv(
139 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
140 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
141 journal_date, numerator_rate, denominator_rate, gl_curr_code,
142 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
143 ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
144 sub_acc, beginning_balance, sign_coa, coa_desc, remark, remark_external, order_num)
145 WITH gl_journal_trx_details AS (
146 SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
147 a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
148 a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark, COALESCE(AA.remark_external, A.remark) AS remark_external,
149 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,
150 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
151 FROM gl_journal_trx_item a
152 INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
153 INNER JOIN dt_date p ON x.doc_date = p.string_date
154 LEFT JOIN gl_journal_trx_item_mapping_custom AA ON A.journal_trx_item_id = AA.journal_trx_item_mapping_id
155 WHERE a.tenant_id = $2
156 AND p.year_month_date BETWEEN $3 AND $4
157 UNION ALL
158 SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
159 b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
160 b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark, COALESCE(BB.remark_external, B.remark) AS remark_external,
161 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,
162 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
163 FROM gl_journal_trx_mapping b
164 INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id
165 INNER JOIN dt_date p ON x.doc_date = p.string_date
166 LEFT JOIN gl_journal_trx_item_mapping_custom BB ON B.journal_trx_mapping_id = BB.journal_trx_item_mapping_id
167 WHERE b.tenant_id = $2
168 AND p.year_month_date BETWEEN $3 AND $4
169 UNION ALL
170 SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
171 c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
172 c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark, COALESCE(CC.remark_external, C.remark) AS remark_external,
173 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,
174 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
175 FROM gl_journal_trx_fx c
176 INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
177 INNER JOIN dt_date p ON x.doc_date = p.string_date
178 LEFT JOIN gl_journal_trx_item_mapping_custom CC ON C.journal_trx_fx_id = CC.journal_trx_item_mapping_id
179 WHERE c.tenant_id = $2
180 AND p.year_month_date BETWEEN $3 AND $4
181 )
182 SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
183 A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
184 B.journal_date, B.numerator_rate, B.denominator_rate, B.gl_curr_code,
185 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,
186 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,
187 B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
188 CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark,
189 CASE WHEN (B.remark_external is null OR TRIM(B.remark_external) = '''') THEN A.remark ELSE B.remark_external END AS remark_external,
190 2
191 FROM gl_journal_trx A
192 INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
193 INNER JOIN m_coa E ON B.coa_id = E.coa_id' ||
194 vJoinOuRc ||
195 vJoinSegment ||
196 ' 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
197 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
198 AND C.session_id = $1
199 WHERE A.tenant_id = $2
200 AND A.status_doc = $5
201 AND A.ou_bu_id = $6' ||
202 vFilterBranch ||
203 vFilterSubBu ||
204 vFilterGroupCoa ||
205 vFilterOuRc ||
206 vFilterSegment ||
207 vFilterCoaMainAcc ||
208 vFilterCoaSubAcc ||
209 ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, vPeriodFrom, vPeriodTo, vStatusDocRelease, vOuBuId, vEmptyId, vEmptyString;
210
211 /* ambil data detail, status doc = D */
212 EXECUTE '
213 INSERT INTO tr_gl_detail_ledger_card_csv(
214 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
215 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
216 journal_date, numerator_rate, denominator_rate, gl_curr_code,
217 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
218 ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
219 sub_acc, beginning_balance, sign_coa, coa_desc, remark, remark_external, order_num)
220 WITH gl_journal_trx_details AS (
221 SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
222 a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
223 a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark, COALESCE(AA.remark_external, A.remark) AS remark_external,
224 a.type_rate,
225 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,
226 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
227 FROM gl_journal_trx_item a
228 INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
229 INNER JOIN dt_date p ON x.doc_date = p.string_date
230 LEFT JOIN gl_journal_trx_item_mapping_custom AA ON A.journal_trx_item_id = AA.journal_trx_item_mapping_id
231 WHERE a.tenant_id = $2
232 AND p.year_month_date BETWEEN $3 AND $4
233 UNION ALL
234 SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
235 b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
236 b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark, COALESCE(BB.remark_external, B.remark) AS remark_external,
237 b.type_rate,
238 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,
239 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
240 FROM gl_journal_trx_mapping b
241 INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id
242 INNER JOIN dt_date p ON x.doc_date = p.string_date
243 LEFT JOIN gl_journal_trx_item_mapping_custom BB ON B.journal_trx_mapping_id = BB.journal_trx_item_mapping_id
244 WHERE b.tenant_id = $2
245 AND p.year_month_date BETWEEN $3 AND $4
246 UNION ALL
247 SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
248 c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
249 c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark, COALESCE(CC.remark_external, C.remark) AS remark_external,
250 $8,
251 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,
252 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
253 FROM gl_journal_trx_fx c
254 INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
255 INNER JOIN dt_date p ON x.doc_date = p.string_date
256 LEFT JOIN gl_journal_trx_item_mapping_custom CC ON C.journal_trx_fx_id = CC.journal_trx_item_mapping_id
257 WHERE c.tenant_id = $2
258 AND p.year_month_date BETWEEN $3 AND $4
259 ), tt_gl_admin_journal_rate AS (
260 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
261 FROM (
262 SELECT A.journal_date, A.type_rate, A.curr_code
263 FROM gl_journal_trx_details A
264 GROUP BY journal_date, type_rate, curr_code
265 ) A
266 )
267 SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
268 A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
269 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,
270 ROUND(B.amount * (D.data_exchange_rate).numerator_rate / (D.data_exchange_rate).denominator_rate, $7) AS gl_amount,
271 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,
272 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,
273 B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
274 CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark,
275 CASE WHEN (B.remark_external is null OR TRIM(B.remark_external) = '''') THEN A.remark ELSE B.remark_external END AS remark_external, 2
276 FROM gl_journal_trx A
277 INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
278 INNER JOIN m_coa E ON B.coa_id = E.coa_id
279 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'||
280 vJoinOuRc ||
281 vJoinSegment ||
282 ' 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
283 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
284 AND C.session_id = $1
285 WHERE A.tenant_id = $2
286 AND A.status_doc = $5
287 AND A.ou_bu_id = $6' ||
288 vFilterBranch ||
289 vFilterSubBu ||
290 vFilterGroupCoa ||
291 vFilterOuRc ||
292 vFilterSegment ||
293 vFilterCoaMainAcc ||
294 vFilterCoaSubAcc ||
295 ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, vPeriodFrom, vPeriodTo, vStatusDocDraft, vOuBuId, vRoundingDigit, vTypeRateCom, vCurrGL, vEmptyId, vEmptyString;
296
297
298 /* ambil data detail yang tidak ada transaksi tapi ada saldo */
299 INSERT INTO tr_gl_detail_ledger_card_csv(
300 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
301 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
302 journal_date, gl_curr_code,
303 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
304 ou_rc_id, rc_name, segment_id, segment_name,
305 coa_id, main_acc, sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
306 SELECT pSessionId, pTenantId, A.ou_bu_id, vEmptyId, vTypeSaldoAwal,
307 vEmptyId, vEmptyString, vEmptyString, L.sign_coa, vEmptyString, vEmptyAmount,
308 vEmptyString, vEmptyString,
309 COALESCE(A.beginning_balance, 0), 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,
310 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,
311 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
312 FROM tr_gl_beginning_balance_ledger_card A
313 INNER JOIN m_coa L ON A.coa_id = L.coa_id
314 WHERE NOT EXISTS (SELECT 1
315 FROM tr_gl_detail_ledger_card_csv B
316 WHERE A.ou_bu_id = B.ou_bu_id
317 AND A.ou_branch_id = B.ou_branch_id
318 AND A.ou_sub_bu_id = B.ou_sub_bu_id
319 AND A.coa_id = B.coa_id
320 AND A.ou_rc_id = B.ou_rc_id
321 AND A.segment_id = B.segment_id
322 AND A.session_id = B.session_id)
323 AND A.tenant_id = pTenantId
324 AND A.session_id = pSessionId;
325
326 (A.curr_code_trx || to_char(A.denominator_rate, '999,999.90') || ' = ' || A.gl_curr_code || to_char(A.numerator_rate, '999,999.90'))
327 /* Insert data beginning balance yang ada transaksinya ambil dari table tr_gl_detail_ledger_card_csv yang order_num= 2 */
328 INSERT INTO tr_gl_detail_ledger_card_csv(
329 session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
330 doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
331 journal_date, gl_curr_code,
332 gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
333 ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
334 sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
335 WITH data_saldo_awal_with_transaksi AS (
336 SELECT A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
337 A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
338 A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
339 FROM tr_gl_detail_ledger_card_csv A
340 WHERE A.session_id = pSessionId
341 AND A.tenant_id = pTenantId
342 AND A.order_num = 2
343 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,
344 A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
345 A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
346 )
347 SELECT pSessionId, A.tenant_id, A.ou_bu_id, vEmptyId, vTypeSaldoAwal,
348 vEmptyId, vEmptyString, vEmptyString, A.sign_coa, vEmptyString, vEmptyAmount,
349 vEmptyString, vEmptyString,
350 A.beginning_balance, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
351 A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
352 A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc, 1, vEmptyString
353 FROM data_saldo_awal_with_transaksi A
354 ORDER BY A.coa_id;
355
356
357 /* header result*/
358 Open pRefHeader FOR
359 SELECT 'OU BU', 'OU Branch', 'OU Sub BU', 'Main Acc', 'Sub Acc', 'COA Description',
360 'Sign COA', 'OU RC', 'Segment','Document Type', 'Doc No', 'Doc Date', 'Remark', 'Remark External',
361 'Currency Transaction', 'Transaction Amount', 'Journal Date', 'Rate', 'Debt Amount', 'Credit Amount',
362 'Total Amount';
363 RETURN NEXT pRefHeader;
364
365 Open pRefDetail FOR
366
367 WITH data_detail_report_ledger_card AS (
368
369 -- Untuk data detail
370 SELECT f_get_ou_name(A.ou_bu_id) AS ou_bu, A.branch_name, A.sub_bu_name, A.main_acc, A.sub_acc, A.coa_desc, A.coa_id,
371 A.sign_coa, A.rc_name, A.segment_name, A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.remark_external,
372 A.curr_code_trx, A.amount_trx, A.journal_date,
373 (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,
374 CASE WHEN A.sign_journal = 'D' THEN A.gl_amount ELSE vEmptyAmount END AS gl_amount_debt,
375 CASE WHEN A.sign_journal = 'C' THEN A.gl_amount ELSE vEmptyAmount END AS gl_amount_credit,
376 A.sign_journal, A.gl_curr_code, A.gl_amount,
377 CASE WHEN A.sign_journal = A.sign_coa THEN A.gl_amount ELSE (A.gl_amount * -1) END AS total_amount, A.order_num
378 FROM tr_gl_detail_ledger_card_csv A
379 WHERE A.session_id = pSessionId
380 AND A.tenant_id = pTenantId
381 AND A.order_num = 2
382 UNION ALL
383 -- Untuk data saldo awal
384 SELECT f_get_ou_name(A.ou_bu_id) AS ou_bu, A.branch_name, A.sub_bu_name, A.main_acc, A.sub_acc, A.coa_desc, A.coa_id,
385 A.sign_coa, A.rc_name, A.segment_name, A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.remark_external,
386 A.curr_code_trx, A.amount_trx, A.journal_date,
387 vEmptyString AS rate,
388 CASE WHEN A.sign_journal = 'D' THEN A.beginning_balance ELSE vEmptyAmount END AS gl_amount_debt,
389 CASE WHEN A.sign_journal = 'C' THEN A.beginning_balance ELSE vEmptyAmount END AS gl_amount_credit,
390 A.sign_journal, A.gl_curr_code, A.gl_amount,
391 A.beginning_balance AS total_amount, A.order_num
392 FROM tr_gl_detail_ledger_card_csv A
393 WHERE A.session_id = pSessionId
394 AND A.tenant_id = pTenantId
395 AND A.order_num = 1
396
397 ORDER BY ou_bu, branch_name, sub_bu_name, coa_id, rc_name, segment_name, order_num
398
399 ), data_final AS (
400 SELECT *, ROW_NUMBER()OVER(ORDER BY ou_bu, branch_name, sub_bu_name, coa_id, rc_name, segment_name, order_num, doc_date, doc_type_desc, doc_no) AS ordering_tingkat2
401 FROM data_detail_report_ledger_card
402 ORDER BY ou_bu, branch_name, sub_bu_name, coa_id, rc_name, segment_name, order_num, doc_date, doc_type_desc, doc_no
403 )
404
405 SELECT A.ou_bu, A.branch_name, A.sub_bu_name, A.main_acc, A.sub_acc, A.coa_desc,
406 A.sign_coa, A.rc_name, A.segment_name, A.doc_type_desc, A.doc_no, A.doc_date, A.remark, A.remark_external,
407 A.curr_code_trx, A.amount_trx, A.journal_date, A.rate, A.gl_amount_debt, A.gl_amount_credit,
408 SUM(A.total_amount) OVER (PARTITION BY A.ou_bu, A.branch_name, A.sub_bu_name, A.coa_id, A.rc_name, A.segment_name ORDER BY A.ou_bu, A.branch_name, A.sub_bu_name, A.coa_id, A.rc_name, A.segment_name, A.order_num, A.doc_date, A.doc_type_desc, A.doc_no, A.ordering_tingkat2) AS total_amount
409 FROM data_final A
410 ORDER BY A.ou_bu, A.branch_name, A.sub_bu_name, A.coa_id, A.rc_name, A.segment_name, A.order_num, A.doc_date, A.doc_type_desc, A.doc_no, A.ordering_tingkat2 ;
411
412 RETURN NEXT pRefDetail ;
413
414 DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
415 DELETE FROM tr_gl_detail_ledger_card_csv WHERE session_id = pSessionId;
416END;
417$BODY$
418 LANGUAGE plpgsql VOLATILE
419 COST 100
420 ROWS 1000;