· 9 years ago · Dec 01, 2016, 07:50 AM
1CREATE OR REPLACE FUNCTION r_report_ar_card(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, bigint, 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 pOuId ALIAS FOR $6;
13 pPeriodFrom ALIAS FOR $7;
14 pPeriodTo ALIAS FOR $8;
15 pPartnerId ALIAS FOR $9;
16 pCurrCode ALIAS FOR $10;
17 pShowDpBalance ALIAS FOR $11;
18
19 vEmptyId bigint := -99;
20 vSldDownPaymentDoc bigint := 252;
21 vAdvanceInvArDoc bigint := 243;
22 vCashBankInArDocTypeId bigint := 621;
23 vCashBankInNonArDocTypeId bigint := 623;
24 vCashBankInDpArDocTypeId bigint := 628;
25 vCGVoidDocTypeId bigint := 629;
26 vSalesInvoiceDocTypeId bigint := 321;
27 vDNArDocTypeId bigint := 241;
28 vCGReceiptDocTypeId bigint := 624;
29BEGIN
30
31 /*
32 * delete isi table temp yang digunakan
33 */
34
35 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId;
36 DELETE FROM tr_doc_allocation_ar WHERE session_id = pSessionId;
37 DELETE FROM tr_doc_ar_card_result WHERE session_id = pSessionId;
38
39 /* ambil saldo awal */
40 PERFORM f_get_ar_card_beginning_balance(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pOuId, pPeriodFrom, pPartnerId, pCurrCode, pShowDpBalance);
41
42 /*
43 * ambil semua dokumen penambah hutang dari saldo AR
44 */
45 INSERT INTO tr_doc_ar_card
46 (session_id, tenant_id, type_data,
47 partner_id, curr_code,
48 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
49 ref_doc_type_id, ref_doc_no, ref_doc_date,
50 ext_doc_no, ext_doc_date,
51 debt_amount, credit_amount, accumulated_amount)
52 SELECT pSessionId, A.tenant_id, 'BBBB',
53 A.partner_id, A.curr_code,
54 A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.doc_date, A.doc_no,
55 A.ref_doc_type_id, COALESCE(B.doc_no, COALESCE(C.doc_no, ' ')), COALESCE(B.doc_date, COALESCE(C.doc_date, ' ')),
56 A.ext_doc_no, A.ext_doc_date,
57 A.amount, 0, 0
58 FROM fi_invoice_ar_balance A
59 LEFT OUTER JOIN sl_so B ON A.ref_doc_type_id = B.doc_type_id AND B.so_id = A.ref_id
60 LEFT OUTER JOIN cb_in_out_cashbank C ON A.ref_doc_type_id = C.doc_type_id AND C.in_out_cashbank_id = A.ref_id
61 WHERE A.tenant_id = pTenantId
62 AND A.ou_id = pOuId
63 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
64 AND A.amount > 0
65 AND A.doc_type_id NOT IN (vSldDownPaymentDoc)
66 AND NOT EXISTS (
67 SELECT 1 FROM fi_invoice_advance_ar_balance Z
68 WHERE A.invoice_ar_id = Z.sales_invoice_id
69 );
70
71 /*
72 * ambil semua dokumen faktur pajak penambah hutang dari saldo AR
73 */
74 INSERT INTO tr_doc_ar_card
75 (session_id, tenant_id, type_data,
76 partner_id, curr_code,
77 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
78 ref_doc_type_id, ref_doc_no, ref_doc_date,
79 ext_doc_no, ext_doc_date,
80 debt_amount, credit_amount, accumulated_amount)
81 SELECT pSessionId, A.tenant_id, 'BBBB',
82 A.partner_id, A.tax_curr_code,
83 A.ou_id, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_date, A.tax_no,
84 B.ref_doc_type_id, COALESCE(C.doc_no, COALESCE(D.doc_no, ' ')), COALESCE(C.doc_date, COALESCE(D.doc_date, ' ')),
85 B.ext_doc_no, B.ext_doc_date,
86 A.gov_tax_amount, 0, 0
87 FROM fi_invoice_tax_ar_balance A
88 INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id
89 LEFT OUTER JOIN sl_so C ON B.ref_doc_type_id = C.doc_type_id AND C.so_id = B.ref_id
90 LEFT OUTER JOIN cb_in_out_cashbank D ON B.ref_doc_type_id = D.doc_type_id AND D.in_out_cashbank_id = B.ref_id
91 WHERE A.tenant_id = pTenantId
92 AND A.ou_id = pOuId
93 AND SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
94 AND A.gov_tax_amount > 0
95 AND B.doc_type_id NOT IN (vSldDownPaymentDoc)
96 AND NOT EXISTS (
97 SELECT 1 FROM fi_invoice_tax_advance_ar_balance Z
98 WHERE B.invoice_ar_id = Z.sales_invoice_id
99 );
100
101 IF pShowDpBalance = 'N' THEN
102
103 /* jika saldo Down Payment Balance tidak mau dimunculkan sebagai Kredit AR */
104
105 /*
106 * ambil semua dokumen pengurang hutang dari saldo AR
107 */
108 INSERT INTO tr_doc_ar_card
109 (session_id, tenant_id, type_data,
110 partner_id, curr_code,
111 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
112 ref_doc_type_id, ref_doc_no, ref_doc_date,
113 ext_doc_no, ext_doc_date,
114 debt_amount, credit_amount, accumulated_amount)
115 SELECT pSessionId, A.tenant_id, 'CCCC',
116 A.partner_id, A.curr_code,
117 A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.doc_date, A.doc_no,
118 A.ref_doc_type_id, COALESCE(B.doc_no, ' '), COALESCE(B.doc_date, ' '),
119 A.ext_doc_no, A.ext_doc_date,
120 0, -1 * A.amount, 0
121 FROM fi_invoice_ar_balance A
122 LEFT OUTER JOIN sl_so B ON A.ref_doc_type_id = B.doc_type_id AND B.so_id = A.ref_id
123 WHERE A.tenant_id = pTenantId
124 AND A.ou_id = pOuId
125 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
126 AND A.amount < 0
127 AND A.doc_type_id NOT IN (vSldDownPaymentDoc)
128 AND NOT EXISTS (
129 SELECT 1 FROM fi_invoice_advance_ar_balance Z
130 WHERE A.invoice_ar_id = Z.sales_invoice_id
131 );
132
133 /*
134 * ambil semua dokumen faktur pajak pengurang hutang dari saldo AR
135 */
136 INSERT INTO tr_doc_ar_card
137 (session_id, tenant_id, type_data,
138 partner_id, curr_code,
139 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
140 ref_doc_type_id, ref_doc_no, ref_doc_date,
141 ext_doc_no, ext_doc_date,
142 debt_amount, credit_amount, accumulated_amount)
143 SELECT pSessionId, A.tenant_id, 'CCCC',
144 A.partner_id, A.tax_curr_code,
145 A.ou_id, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_date, A.tax_no,
146 B.ref_doc_type_id, COALESCE(C.doc_no, ' '), COALESCE(C.doc_date, ' '),
147 B.ext_doc_no, B.ext_doc_date,
148 0, -1 * A.gov_tax_amount, 0
149 FROM fi_invoice_tax_ar_balance A
150 INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id
151 LEFT OUTER JOIN sl_so C ON B.ref_doc_type_id = C.doc_type_id AND C.so_id = B.ref_id
152 WHERE A.tenant_id = pTenantId
153 AND A.ou_id = pOuId
154 AND SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
155 AND A.gov_tax_amount < 0
156 AND B.doc_type_id NOT IN (vSldDownPaymentDoc)
157 AND NOT EXISTS (
158 SELECT 1 FROM fi_invoice_tax_advance_ar_balance Z
159 WHERE B.invoice_ar_id = Z.sales_invoice_id
160 );
161
162 /*
163 * ambil saldo uang muka yang sudah dialokasikan
164 */
165 INSERT INTO tr_doc_ar_card
166 (session_id, tenant_id, type_data,
167 partner_id, curr_code,
168 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
169 ref_doc_type_id, ref_doc_no, ref_doc_date,
170 ext_doc_no, ext_doc_date,
171 debt_amount, credit_amount, accumulated_amount)
172 SELECT pSessionId, A.tenant_id, 'BBBB',
173 A.partner_id, C.debit_curr_code,
174 A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.doc_date, A.doc_no,
175 A.ref_doc_type_id, COALESCE(E.doc_no, COALESCE(F.doc_no, ' ')), COALESCE(E.doc_date, COALESCE(F.doc_date, ' ')),
176 A.ext_doc_no, A.ext_doc_date,
177 0, SUM(C.debit_amount), 0
178 FROM fi_invoice_ar_balance A
179 INNER JOIN fi_allocation_ar_balance C ON C.credit_doc_type_id = A.doc_type_id AND C.credit_id = A.invoice_ar_balance_id
180 INNER JOIN fi_allocation_ar D ON D.allocation_ar_id = C.allocation_ar_id
181 LEFT OUTER JOIN sl_so E ON A.ref_doc_type_id = E.doc_type_id AND E.so_id = A.ref_id
182 LEFT OUTER JOIN fi_invoice_ar_balance F ON A.ref_doc_type_id = F.doc_type_id AND F.invoice_ar_balance_id = A.ref_id
183 WHERE A.tenant_id = pTenantId
184 AND A.ou_id = pOuId
185 AND SUBSTR(D.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
186 AND A.doc_type_id IN (vSldDownPaymentDoc)
187 GROUP BY A.tenant_id, A.partner_id, C.debit_curr_code, A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.doc_date, A.doc_no, A.ext_doc_no, A.ext_doc_date, E.doc_no, F.doc_no, E.doc_date, F.doc_date;
188
189
190 ELSE
191
192 /* jika saldo Down Payment Balance mau dimunculkan sebagai Kredit AR */
193
194 INSERT INTO tr_doc_ar_card
195 (session_id, tenant_id, type_data,
196 partner_id, curr_code,
197 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
198 ref_doc_type_id, ref_doc_no, ref_doc_date,
199 ext_doc_no, ext_doc_date,
200 debt_amount, credit_amount, accumulated_amount)
201 SELECT pSessionId, A.tenant_id, 'CCCC',
202 A.partner_id, A.curr_code,
203 A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.doc_date, A.doc_no,
204 A.ref_doc_type_id, COALESCE(B.doc_no, COALESCE(C.doc_no, ' ')), COALESCE(B.doc_date, COALESCE(C.doc_date, ' ')),
205 A.ext_doc_no, A.ext_doc_date,
206 0, -1 * A.amount, 0
207 FROM fi_invoice_ar_balance A
208 LEFT OUTER JOIN sl_so B ON A.ref_doc_type_id = B.doc_type_id AND B.so_id = A.ref_id
209 LEFT OUTER JOIN fi_invoice_ar_balance C ON A.ref_doc_type_id = C.doc_type_id AND C.invoice_ar_balance_id = A.ref_id
210 WHERE A.tenant_id = pTenantId
211 AND A.ou_id = pOuId
212 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
213 AND A.amount < 0
214 AND NOT EXISTS (
215 SELECT 1 FROM fi_invoice_advance_ar_balance Z
216 WHERE A.invoice_ar_id = Z.sales_invoice_id
217 );
218
219 INSERT INTO tr_doc_ar_card
220 (session_id, tenant_id, type_data,
221 partner_id, curr_code,
222 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
223 ref_doc_type_id, ref_doc_no, ref_doc_date,
224 ext_doc_no, ext_doc_date,
225 debt_amount, credit_amount, accumulated_amount)
226 SELECT pSessionId, A.tenant_id, 'CCCC',
227 A.partner_id, A.tax_curr_code,
228 A.ou_id, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_date, A.tax_no,
229 B.ref_doc_type_id, COALESCE(C.doc_no, COALESCE(D.doc_no,' ')), COALESCE(C.doc_date, COALESCE(D.doc_date,' ')),
230 B.ext_doc_no, B.ext_doc_date,
231 0, -1 * A.gov_tax_amount, 0
232 FROM fi_invoice_tax_ar_balance A
233 INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id
234 LEFT OUTER JOIN sl_so C ON B.ref_doc_type_id = C.doc_type_id AND C.so_id = B.ref_id
235 LEFT OUTER JOIN fi_invoice_ar_balance D ON D.doc_type_id = B.ref_doc_type_id AND B.ref_id = D.invoice_ar_balance_id
236 WHERE A.tenant_id = pTenantId
237 AND A.ou_id = pOuId
238 AND SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
239 AND A.gov_tax_amount < 0
240 AND NOT EXISTS (
241 SELECT 1 FROM fi_invoice_tax_advance_ar_balance Z
242 WHERE B.invoice_ar_id = Z.sales_invoice_id
243 );
244
245 END IF;
246
247
248 /*
249 * ambil saldo uang muka invoice yang sudah digunakan di invoice
250 */
251 INSERT INTO tr_doc_ar_card
252 (session_id, tenant_id, type_data,
253 partner_id, curr_code,
254 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
255 ref_doc_type_id, ref_doc_no, ref_doc_date,
256 ext_doc_no, ext_doc_date,
257 debt_amount, credit_amount, accumulated_amount)
258 SELECT pSessionId, A.tenant_id, 'BBBB',
259 A.partner_id, A.curr_code,
260 A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.doc_date, A.doc_no,
261 B.doc_type_id, B.doc_no, B.doc_date,
262 A.ext_doc_no, A.ext_doc_date,
263 (A.amount + (-1 * B.amount)), -1 * B.amount, -1 * B.amount
264 FROM fi_invoice_ar_balance A
265 INNER JOIN fi_invoice_advance_ar_balance B ON A.invoice_ar_id = B.sales_invoice_id
266 WHERE A.tenant_id = pTenantId
267 AND A.ou_id = pOuId
268 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo;
269
270 INSERT INTO tr_doc_ar_card
271 (session_id, tenant_id, type_data,
272 partner_id, curr_code,
273 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
274 ref_doc_type_id, ref_doc_no, ref_doc_date,
275 ext_doc_no, ext_doc_date,
276 debt_amount, credit_amount, accumulated_amount)
277 SELECT pSessionId, A.tenant_id, 'BBBB',
278 A.partner_id, A.tax_curr_code,
279 A.ou_id, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_date, A.tax_no,
280 C.doc_type_id, C.tax_no, C.tax_date,
281 B.ext_doc_no, B.ext_doc_date,
282 (A.gov_tax_amount + (-1 * C.gov_tax_amount)), -1 * C.gov_tax_amount, -1 * C.gov_tax_amount
283 FROM fi_invoice_tax_ar_balance A
284 INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id
285 INNER JOIN fi_invoice_tax_advance_ar_balance C ON B.invoice_ar_id = C.sales_invoice_id
286 WHERE A.tenant_id = pTenantId
287 AND A.ou_id = pOuId
288 AND SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo;
289
290 /*
291 * ambil dokumen alokasi dalam periode tersebut (untuk update nilai saldo)
292 */
293 INSERT INTO tr_doc_allocation_ar
294 (session_id, tenant_id, ou_id,
295 doc_type_id, doc_id, doc_date, doc_no,
296 curr_code, amount, flg_void)
297 SELECT pSessionId, A.tenant_id, A.ou_id,
298 B.debit_doc_type_id, B.debit_id, fi_get_date_invoice_ar(B.debit_doc_type_id, B.debit_id), fi_get_no_invoice_ar(B.debit_doc_type_id, B.debit_id),
299 B.debit_curr_code, SUM(B.debit_amount), 'N'
300 FROM fi_allocation_ar A, fi_allocation_ar_balance B
301 WHERE A.tenant_id = pTenantId
302 AND A.ou_id = pOuId
303 AND A.allocation_ar_id = B.allocation_ar_id
304 AND B.debit_id <> vEmptyId
305 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
306 GROUP BY A.tenant_id, A.ou_id, B.debit_doc_type_id, B.debit_id, B.debit_curr_code;
307
308
309 INSERT INTO tr_doc_allocation_ar
310 (session_id, tenant_id, ou_id,
311 doc_type_id, doc_id, doc_date, doc_no,
312 curr_code, amount)
313 SELECT pSessionId, A.tenant_id, A.ou_id,
314 B.credit_doc_type_id, B.credit_id, fi_get_date_invoice_ar(B.credit_doc_type_id, B.credit_id), fi_get_no_invoice_ar(B.credit_doc_type_id, B.credit_id),
315 B.credit_curr_code, SUM(B.credit_amount)
316 FROM fi_allocation_ar A, fi_allocation_ar_balance B
317 WHERE A.tenant_id = pTenantId
318 AND A.ou_id = pOuId
319 AND A.allocation_ar_id = B.allocation_ar_id
320 AND B.credit_id <> vEmptyId
321 AND B.credit_doc_type_id NOT IN (vCGReceiptDocTypeId, vCashBankInArDocTypeId, vCashBankInDpArDocTypeId, vCashBankInNonArDocTypeId, vSldDownPaymentDoc)
322 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
323 GROUP BY A.tenant_id, A.ou_id, B.credit_doc_type_id, B.credit_id, B.credit_curr_code;
324
325 INSERT INTO tr_doc_allocation_ar
326 (session_id, tenant_id, ou_id,
327 doc_type_id, doc_id, doc_date, doc_no,
328 curr_code, amount)
329 SELECT pSessionId, A.tenant_id, A.ou_id,
330 B.credit_doc_type_id, B.credit_id, fi_get_date_invoice_ar(B.credit_doc_type_id, B.credit_id), fi_get_no_invoice_ar(B.credit_doc_type_id, B.credit_id),
331 B.debit_curr_code, SUM(B.debit_amount)
332 FROM fi_allocation_ar A, fi_allocation_ar_balance B
333 WHERE A.tenant_id = pTenantId
334 AND A.ou_id = pOuId
335 AND A.allocation_ar_id = B.allocation_ar_id
336 AND B.credit_id <> vEmptyId
337 AND B.credit_doc_type_id IN (vSldDownPaymentDoc)
338 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
339 GROUP BY A.tenant_id, A.ou_id, B.credit_doc_type_id, B.credit_id, B.debit_curr_code;
340
341 ---- Untuk Menampilkan Alokasi Balik hasil Void CG (untuk update nilai saldo) -START
342 INSERT INTO tr_doc_allocation_ar
343 (session_id, tenant_id, ou_id,
344 doc_type_id, doc_id, doc_date, doc_no,
345 curr_code, amount)
346 SELECT pSessionId, A.tenant_id, A.ou_id,
347 B.credit_doc_type_id, B.credit_id, fi_get_date_invoice_ar(B.credit_doc_type_id, B.credit_id), fi_get_no_invoice_ar(B.credit_doc_type_id, B.credit_id),
348 B.credit_curr_code, -1 * SUM(B.credit_amount)
349 FROM cb_in_out_cashbank A, fi_allocation_ar_balance B
350 WHERE A.tenant_id = pTenantId
351 AND A.ou_id = pOuId
352 AND A.in_out_cashbank_id = B.ref_alloc_id
353 AND A.doc_type_id = B.ref_doc_type_id
354 AND A.doc_type_id = vCGVoidDocTypeId
355 AND B.credit_id <> vEmptyId
356 AND B.credit_doc_type_id IN (vSalesInvoiceDocTypeId, vDNArDocTypeId)
357 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
358 GROUP BY A.tenant_id, A.ou_id, B.credit_doc_type_id, B.credit_id, B.credit_curr_code;
359
360 ---- Untuk Menampilkan Alokasi Balik hasil Void CG (untuk update nilai saldo) -END
361
362 /*
363 * hitung nilai total akumulasi saldo
364 */
365 UPDATE tr_doc_ar_card
366 SET accumulated_amount = accumulated_amount + (
367 SELECT SUM(A.amount)
368 FROM tr_doc_allocation_ar A
369 WHERE tr_doc_ar_card.tenant_id = A.tenant_id
370 AND tr_doc_ar_card.ou_id = A.ou_id
371 AND tr_doc_ar_card.doc_type_id = A.doc_type_id
372 AND tr_doc_ar_card.doc_no = A.doc_no
373 AND tr_doc_ar_card.doc_date = A.doc_date
374 AND tr_doc_ar_card.curr_code = A.curr_code
375 AND tr_doc_ar_card.invoice_ar_id = A.doc_id
376 AND tr_doc_ar_card.session_id = pSessionId
377 AND A.session_id = pSessionId
378 GROUP BY A.session_id, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date, A.curr_code, A.doc_id
379 )
380 WHERE EXISTS ( SELECT 1 FROM tr_doc_allocation_ar A
381 WHERE tr_doc_ar_card.tenant_id = A.tenant_id AND
382 tr_doc_ar_card.ou_id = A.ou_id AND
383 tr_doc_ar_card.doc_type_id = A.doc_type_id AND
384 tr_doc_ar_card.doc_date = A.doc_date AND
385 tr_doc_ar_card.doc_no = A.doc_no AND
386 tr_doc_ar_card.curr_code = A.curr_code AND
387 tr_doc_ar_card.invoice_ar_id = A.doc_id AND
388 tr_doc_ar_card.session_id = pSessionId AND
389 A.session_id = pSessionId
390 ) AND
391 tr_doc_ar_card.session_id = pSessionId;
392
393 /*
394 * ambil dokumen alokasi untuk ditampilkan
395 */
396
397 INSERT INTO tr_doc_ar_card
398 (session_id, tenant_id, type_data,
399 partner_id, curr_code,
400 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
401 ref_doc_type_id, ref_doc_no, ref_doc_date,
402 ext_doc_no, ext_doc_date,
403 debt_amount, credit_amount, accumulated_amount)
404 SELECT pSessionId, A.tenant_id, 'EEEE',
405 C.partner_id, A.debit_curr_code,
406 A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
407 A.debit_doc_type_id, fi_get_no_invoice_ar(A.debit_doc_type_id, A.debit_id), fi_get_date_invoice_ar(A.debit_doc_type_id, A.debit_id),
408 ' ', ' ',
409 0, SUM(A.debit_amount), 0
410 FROM fi_allocation_ar B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
411 WHERE A.tenant_id = pTenantId AND
412 A.ou_id = pOuId AND
413 A.allocation_ar_id = B.allocation_ar_id AND
414 A.debit_id <> vEmptyId AND
415 A.tenant_id = C.tenant_id AND
416 A.debit_id = C.invoice_ar_balance_id AND
417 A.debit_doc_type_id = C.doc_type_id AND
418 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
419 GROUP BY A.tenant_id, C.partner_id, A.debit_curr_code, A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no, A.debit_doc_type_id, A.debit_id;
420
421
422 INSERT INTO tr_doc_ar_card
423 (session_id, tenant_id, type_data,
424 partner_id, curr_code,
425 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
426 ref_doc_type_id, ref_doc_no, ref_doc_date,
427 ext_doc_no, ext_doc_date,
428 debt_amount, credit_amount, accumulated_amount)
429 SELECT pSessionId, A.tenant_id, 'EEEE',
430 C.partner_id, A.credit_curr_code,
431 A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
432 A.credit_doc_type_id, fi_get_no_invoice_ar(A.credit_doc_type_id, A.credit_id), fi_get_date_invoice_ar(A.credit_doc_type_id, A.credit_id),
433 ' ', ' ',
434 SUM(A.credit_amount), 0, 0
435 FROM fi_allocation_ar B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
436 WHERE A.tenant_id = pTenantId AND
437 A.ou_id = pOuId AND
438 A.allocation_ar_id = B.allocation_ar_id AND
439 A.credit_id <> vEmptyId AND
440 A.credit_doc_type_id NOT IN (vCGReceiptDocTypeId, vCashBankInArDocTypeId, vCashBankInDpArDocTypeId, vCashBankInNonArDocTypeId, vSldDownPaymentDoc) AND
441 A.tenant_id = C.tenant_id AND
442 A.debit_id = C.invoice_ar_balance_id AND
443 A.debit_doc_type_id = C.doc_type_id AND
444 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
445 GROUP BY A.tenant_id, C.partner_id, A.credit_curr_code, A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
446 A.credit_doc_type_id, A.credit_id;
447
448 INSERT INTO tr_doc_ar_card
449 (session_id, tenant_id, type_data,
450 partner_id, curr_code,
451 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
452 ref_doc_type_id, ref_doc_no, ref_doc_date,
453 ext_doc_no, ext_doc_date,
454 debt_amount, credit_amount, accumulated_amount)
455 SELECT pSessionId, A.tenant_id, 'EEEE',
456 C.partner_id, A.debit_curr_code,
457 A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
458 A.credit_doc_type_id, fi_get_no_invoice_ar(A.credit_doc_type_id, A.credit_id), fi_get_date_invoice_ar(A.credit_doc_type_id, A.credit_id),
459 ' ', ' ',
460 SUM(A.debit_amount), 0, 0
461 FROM fi_allocation_ar B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
462 WHERE A.tenant_id = pTenantId AND
463 A.ou_id = pOuId AND
464 A.allocation_ar_id = B.allocation_ar_id AND
465 A.credit_id <> vEmptyId AND
466 A.credit_doc_type_id IN (vSldDownPaymentDoc) AND
467 A.tenant_id = C.tenant_id AND
468 A.debit_id = C.invoice_ar_balance_id AND
469 A.debit_doc_type_id = C.doc_type_id AND
470 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
471 GROUP BY A.tenant_id, C.partner_id, A.debit_curr_code, A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
472 A.credit_doc_type_id, A.credit_id;
473
474 -- Ambil dokumen CG VOID untuk ditampilkan - START
475 INSERT INTO tr_doc_ar_card
476 (session_id, tenant_id, type_data,
477 partner_id, curr_code,
478 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
479 ref_doc_type_id, ref_doc_no, ref_doc_date,
480 ext_doc_no, ext_doc_date,
481 debt_amount, credit_amount, accumulated_amount)
482 SELECT pSessionId, A.tenant_id, 'VVVV',
483 C.partner_id, A.credit_curr_code,
484 A.ou_id, B.doc_type_id, B.in_out_cashbank_id, B.doc_date, B.doc_no,
485 A.credit_doc_type_id, fi_get_no_invoice_ar(A.credit_doc_type_id, A.credit_id), fi_get_date_invoice_ar(A.credit_doc_type_id, A.credit_id),
486 fi_get_no_invoice_ar(A.debit_doc_type_id, A.debit_id), fi_get_date_invoice_ar(A.debit_doc_type_id, A.debit_id),
487 SUM(A.credit_amount), 0, 0
488 FROM cb_in_out_cashbank B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
489 WHERE A.tenant_id = pTenantId AND
490 A.ou_id = pOuId AND
491 B.in_out_cashbank_id = A.ref_alloc_id AND
492 B.doc_type_id = A.ref_doc_type_id AND
493 B.doc_type_id = vCGVoidDocTypeId AND
494 A.credit_doc_type_id IN (vSalesInvoiceDocTypeId, vDNArDocTypeId) AND
495 A.credit_id <> vEmptyId AND
496 A.tenant_id = C.tenant_id AND
497 A.credit_id = C.invoice_ar_balance_id AND
498 A.credit_doc_type_id = C.doc_type_id AND
499 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
500 GROUP BY A.tenant_id, C.partner_id, A.credit_curr_code, A.ou_id, B.doc_type_id, B.in_out_cashbank_id, B.doc_date, B.doc_no,
501 A.credit_doc_type_id, A.credit_id, A.debit_doc_type_id, A.debit_id;
502
503 -- Ambil dokumen CG VOID untuk ditampilkan - END
504
505 /*
506 * ambil saldo akhir
507 */
508 INSERT INTO tr_doc_ar_card
509 (session_id, tenant_id, type_data,
510 partner_id, curr_code,
511 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
512 ref_doc_type_id, ref_doc_no, ref_doc_date,
513 ext_doc_no, ext_doc_date,
514 debt_amount, credit_amount, accumulated_amount)
515 SELECT pSessionId, tenant_id, 'ZZZZ',
516 partner_id, curr_code,
517 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
518 ref_doc_type_id, ref_doc_no, ref_doc_date,
519 ext_doc_no, ext_doc_date,
520 SUM(debt_amount - accumulated_amount), SUM(credit_amount - accumulated_amount), 0
521 FROM tr_doc_ar_card
522 WHERE session_id = pSessionId
523 AND type_data NOT IN ('EEEE','VVVV')
524 GROUP BY tenant_id, partner_id, curr_code, ou_id, doc_type_id,
525 invoice_ar_id, doc_date, doc_no,
526 ref_doc_type_id, ref_doc_no, ref_doc_date,
527 ext_doc_no, ext_doc_date;
528
529 DELETE FROM tr_doc_ar_card
530 WHERE session_id = pSessionId AND type_data = 'AAAA' AND debt_amount = 0 AND credit_amount = 0;
531
532 UPDATE tr_doc_ar_card SET debt_amount = 0
533 WHERE debt_amount < 0 AND type_data = 'ZZZZ' AND session_id = pSessionId;
534
535 UPDATE tr_doc_ar_card SET credit_amount = 0
536 WHERE credit_amount < 0 AND type_data = 'ZZZZ' AND session_id = pSessionId;
537
538 /*
539 * delete data yg tidak sesuai dengan filter
540 */
541 IF pPartnerId <> -99 THEN
542 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId AND partner_id <> pPartnerId;
543 END IF;
544
545 IF pCurrCode <> '-99' THEN
546 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId AND curr_code <> pCurrCode;
547 END IF;
548
549 /*
550 * tampung ke table temp result untuk ditampilkan (saldo awal dan rolling transaction)
551 */
552 INSERT INTO tr_doc_ar_card_result
553 (session_id, partner_id, partner_name, curr_code, type_data, order_type_data,
554 doc_type_desc, doc_date, doc_no,
555 ref_doc_type_desc, ref_doc_date, ref_doc_no,
556 ext_doc_no, ext_doc_date,
557 debt_amount, credit_amount, rolling_total)
558 SELECT pSessionId, A.partner_id, f_get_partner_name(A.partner_id) AS partner_name, A.curr_code AS curr_code, CASE WHEN A.type_data = 'AAAA' THEN 'Beginning Balance' ELSE 'Rolling Transaction' END AS type_data, CASE WHEN A.type_data = 'AAAA' THEN 1 ELSE 2 END AS order_type_data,
559 f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
560 f_get_doc_desc(A.ref_doc_type_id) AS ref_doc_type_desc, A.ref_doc_date AS ref_doc_date, A.ref_doc_no AS ref_doc_no,
561 A.ext_doc_no, A.ext_doc_date,
562 A.debt_amount AS debt_amount, A.credit_amount AS credit_amount, 0
563 FROM tr_doc_ar_card A
564 WHERE A.type_data IN ('AAAA', 'BBBB', 'CCCC', 'DDDD', 'EEEE', 'FFFF', 'VVVV')
565 AND A.session_id = pSessionId
566 AND (A.debt_amount > 0 OR A.credit_amount > 0);
567
568 /*
569 * tampung ke table temp result untuk ditampilkan (saldo akhir)
570 */
571 INSERT INTO tr_doc_ar_card_result
572 (session_id, partner_id, partner_name, curr_code, type_data, order_type_data,
573 doc_type_desc, doc_date, doc_no,
574 ref_doc_type_desc, ref_doc_date, ref_doc_no,
575 ext_doc_no, ext_doc_date,
576 debt_amount, credit_amount, rolling_total)
577 SELECT pSessionId, A.partner_id, f_get_partner_name(A.partner_id) AS partner_name, A.curr_code AS curr_code, 'Ending Balance' AS type_data, 3 AS order_type_data,
578 f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
579 f_get_doc_desc(A.ref_doc_type_id) AS ref_doc_type_desc, A.ref_doc_date AS ref_doc_date, A.ref_doc_no AS ref_doc_no,
580 A.ext_doc_no, A.ext_doc_date,
581 A.debt_amount AS debt_amount, A.credit_amount AS credit_amount, 0
582 FROM tr_doc_ar_card A
583 WHERE A.type_data IN ('ZZZZ')
584 AND A.session_id = pSessionId
585 AND (A.debt_amount > 0 OR A.credit_amount > 0);
586
587 Open pRefHeader FOR
588 SELECT f_get_ou_name(pOuId) AS ou_name, pPeriodFrom AS period_from, pPeriodTo AS period_to,
589 f_get_username(pUserId) AS username, pDatetime AS datetime;
590
591 RETURN NEXT pRefHeader;
592
593 Open pRefDetail FOR
594 SELECT A.partner_id AS partner_id, A.partner_name AS partner_name, A.curr_code AS curr_code, A.type_data AS type_data, A.order_type_data AS order_type_data,
595 A.doc_type_desc AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
596 A.ref_doc_type_desc AS ref_doc_type_desc, A.ref_doc_date AS ref_doc_date, A.ref_doc_no AS ref_doc_no,
597 A.ext_doc_date AS ext_doc_date, A.ext_doc_no AS ext_doc_no,
598 A.debt_amount AS debt_amount, A.credit_amount AS credit_amount, A.rolling_total AS rolling_total
599 FROM tr_doc_ar_card_result A
600 WHERE A.session_id = pSessionId
601 ORDER BY A.partner_name, A.curr_code, A.order_type_data, A.doc_date, A.doc_no;
602
603 RETURN NEXT pRefDetail;
604
605 /*
606 * delete isi table temp yang digunakan
607 */
608 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId;
609 DELETE FROM tr_doc_allocation_ar WHERE session_id = pSessionId;
610 DELETE FROM tr_doc_ar_card_result WHERE session_id = pSessionId;
611
612END;
613$BODY$
614 LANGUAGE plpgsql VOLATILE
615 COST 100;