· 9 years ago · Nov 30, 2016, 12:06 PM
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, flg_void)
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), 'N'
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, flg_void)
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), 'N'
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, flg_void)
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, SUM(B.credit_amount), 'Y'
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 /**
361 INSERT INTO tr_doc_allocation_ar
362 (session_id, tenant_id, ou_id,
363 doc_type_id, doc_id, doc_date, doc_no,
364 curr_code, amount, flg_void)
365 SELECT pSessionId, A.tenant_id, A.ou_id,
366 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),
367 B.debit_curr_code, SUM(B.debit_amount), 'Y'
368 FROM cb_in_out_cashbank A, fi_allocation_ar_balance B
369 WHERE A.tenant_id = pTenantId
370 AND A.ou_id = pOuId
371 AND A.in_out_cashbank_id = B.ref_alloc_id
372 AND A.doc_type_id = B.ref_doc_type_id
373 AND A.doc_type_id = vCGVoidDocTypeId
374 AND B.debit_id <> vEmptyId
375 AND B.debit_doc_type_id IN (vCGReceiptDocTypeId)
376 AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
377 GROUP BY A.tenant_id, A.ou_id, B.debit_doc_type_id, B.debit_id, B.debit_curr_code;
378 **/
379 ---- Untuk Menampilkan Alokasi Balik hasil Void CG (untuk update nilai saldo) -END
380
381 /*
382 * hitung nilai total akumulasi saldo
383 */
384 UPDATE tr_doc_ar_card
385 SET accumulated_amount = accumulated_amount + (
386 SELECT SUM(A.amount)
387 FROM tr_doc_allocation_ar A
388 WHERE tr_doc_ar_card.tenant_id = A.tenant_id
389 AND tr_doc_ar_card.ou_id = A.ou_id
390 AND tr_doc_ar_card.doc_type_id = A.doc_type_id
391 AND tr_doc_ar_card.doc_no = A.doc_no
392 AND tr_doc_ar_card.doc_date = A.doc_date
393 AND tr_doc_ar_card.curr_code = A.curr_code
394 AND tr_doc_ar_card.invoice_ar_id = A.doc_id
395 AND tr_doc_ar_card.session_id = pSessionId
396 AND A.session_id = pSessionId
397 AND A.flg_void = 'N'
398 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
399 )
400 WHERE EXISTS ( SELECT 1 FROM tr_doc_allocation_ar A
401 WHERE tr_doc_ar_card.tenant_id = A.tenant_id AND
402 tr_doc_ar_card.ou_id = A.ou_id AND
403 tr_doc_ar_card.doc_type_id = A.doc_type_id AND
404 tr_doc_ar_card.doc_date = A.doc_date AND
405 tr_doc_ar_card.doc_no = A.doc_no AND
406 tr_doc_ar_card.curr_code = A.curr_code AND
407 tr_doc_ar_card.invoice_ar_id = A.doc_id AND
408 tr_doc_ar_card.session_id = pSessionId AND
409 A.session_id = pSessionId AND
410 A.flg_void = 'N'
411 ) AND
412 tr_doc_ar_card.session_id = pSessionId;
413
414 UPDATE tr_doc_ar_card
415 SET accumulated_amount = accumulated_amount - (
416 SELECT SUM(A.amount)
417 FROM tr_doc_allocation_ar A
418 WHERE tr_doc_ar_card.tenant_id = A.tenant_id
419 AND tr_doc_ar_card.ou_id = A.ou_id
420 AND tr_doc_ar_card.doc_type_id = A.doc_type_id
421 AND tr_doc_ar_card.doc_no = A.doc_no
422 AND tr_doc_ar_card.doc_date = A.doc_date
423 AND tr_doc_ar_card.curr_code = A.curr_code
424 AND tr_doc_ar_card.invoice_ar_id = A.doc_id
425 AND tr_doc_ar_card.session_id = pSessionId
426 AND A.session_id = pSessionId
427 AND A.flg_void = 'Y'
428 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
429 )
430 WHERE EXISTS ( SELECT 1 FROM tr_doc_allocation_ar A
431 WHERE tr_doc_ar_card.tenant_id = A.tenant_id AND
432 tr_doc_ar_card.ou_id = A.ou_id AND
433 tr_doc_ar_card.doc_type_id = A.doc_type_id AND
434 tr_doc_ar_card.doc_date = A.doc_date AND
435 tr_doc_ar_card.doc_no = A.doc_no AND
436 tr_doc_ar_card.curr_code = A.curr_code AND
437 tr_doc_ar_card.invoice_ar_id = A.doc_id AND
438 tr_doc_ar_card.session_id = pSessionId AND
439 A.session_id = pSessionId AND
440 A.flg_void = 'Y'
441 ) AND
442 tr_doc_ar_card.session_id = pSessionId;
443
444 /*
445 * ambil dokumen alokasi untuk ditampilkan
446 */
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.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),
459 ' ', ' ',
460 0, SUM(A.debit_amount), 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.debit_id <> vEmptyId AND
466 A.tenant_id = C.tenant_id AND
467 A.debit_id = C.invoice_ar_balance_id AND
468 A.debit_doc_type_id = C.doc_type_id AND
469 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
470 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;
471
472
473 INSERT INTO tr_doc_ar_card
474 (session_id, tenant_id, type_data,
475 partner_id, curr_code,
476 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
477 ref_doc_type_id, ref_doc_no, ref_doc_date,
478 ext_doc_no, ext_doc_date,
479 debt_amount, credit_amount, accumulated_amount)
480 SELECT pSessionId, A.tenant_id, 'EEEE',
481 C.partner_id, A.credit_curr_code,
482 A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
483 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),
484 ' ', ' ',
485 SUM(A.credit_amount), 0, 0
486 FROM fi_allocation_ar B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
487 WHERE A.tenant_id = pTenantId AND
488 A.ou_id = pOuId AND
489 A.allocation_ar_id = B.allocation_ar_id AND
490 A.credit_id <> vEmptyId AND
491 A.credit_doc_type_id NOT IN (vCGReceiptDocTypeId, vCashBankInArDocTypeId, vCashBankInDpArDocTypeId, vCashBankInNonArDocTypeId, vSldDownPaymentDoc) AND
492 A.tenant_id = C.tenant_id AND
493 A.debit_id = C.invoice_ar_balance_id AND
494 A.debit_doc_type_id = C.doc_type_id AND
495 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
496 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,
497 A.credit_doc_type_id, A.credit_id;
498
499 INSERT INTO tr_doc_ar_card
500 (session_id, tenant_id, type_data,
501 partner_id, curr_code,
502 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
503 ref_doc_type_id, ref_doc_no, ref_doc_date,
504 ext_doc_no, ext_doc_date,
505 debt_amount, credit_amount, accumulated_amount)
506 SELECT pSessionId, A.tenant_id, 'EEEE',
507 C.partner_id, A.debit_curr_code,
508 A.ou_id, B.doc_type_id, B.allocation_ar_id, B.doc_date, B.doc_no,
509 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),
510 ' ', ' ',
511 SUM(A.debit_amount), 0, 0
512 FROM fi_allocation_ar B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
513 WHERE A.tenant_id = pTenantId AND
514 A.ou_id = pOuId AND
515 A.allocation_ar_id = B.allocation_ar_id AND
516 A.credit_id <> vEmptyId AND
517 A.credit_doc_type_id IN (vSldDownPaymentDoc) AND
518 A.tenant_id = C.tenant_id AND
519 A.debit_id = C.invoice_ar_balance_id AND
520 A.debit_doc_type_id = C.doc_type_id AND
521 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
522 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,
523 A.credit_doc_type_id, A.credit_id;
524
525 -- Ambil dokumen CG VOID untuk ditampilkan - START
526 INSERT INTO tr_doc_ar_card
527 (session_id, tenant_id, type_data,
528 partner_id, curr_code,
529 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
530 ref_doc_type_id, ref_doc_no, ref_doc_date,
531 ext_doc_no, ext_doc_date,
532 debt_amount, credit_amount, accumulated_amount)
533 SELECT pSessionId, A.tenant_id, 'VVVV',
534 C.partner_id, A.credit_curr_code,
535 A.ou_id, B.doc_type_id, B.in_out_cashbank_id, B.doc_date, B.doc_no,
536 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),
537 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),
538 SUM(A.credit_amount), 0, 0
539 FROM cb_in_out_cashbank B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
540 WHERE A.tenant_id = pTenantId AND
541 A.ou_id = pOuId AND
542 B.in_out_cashbank_id = A.ref_alloc_id AND
543 B.doc_type_id = A.ref_doc_type_id AND
544 B.doc_type_id = vCGVoidDocTypeId AND
545 A.credit_doc_type_id IN (vSalesInvoiceDocTypeId, vDNArDocTypeId) AND
546 A.credit_id <> vEmptyId AND
547 A.tenant_id = C.tenant_id AND
548 A.credit_id = C.invoice_ar_balance_id AND
549 A.credit_doc_type_id = C.doc_type_id AND
550 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
551 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,
552 A.credit_doc_type_id, A.credit_id, A.debit_doc_type_id, A.debit_id;
553
554
555 /**
556 INSERT INTO tr_doc_ar_card
557 (session_id, tenant_id, type_data,
558 partner_id, curr_code,
559 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
560 ref_doc_type_id, ref_doc_no, ref_doc_date,
561 ext_doc_no, ext_doc_date,
562 debt_amount, credit_amount, accumulated_amount)
563 SELECT pSessionId, A.tenant_id, 'VVVV',
564 C.partner_id, A.debit_curr_code,
565 A.ou_id, B.doc_type_id, B.in_out_cashbank_id, B.doc_date, B.doc_no,
566 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),
567 ' ', ' ',
568 0, SUM(A.debit_amount), 0
569 FROM cb_in_out_cashbank B, fi_allocation_ar_balance A, fi_invoice_ar_balance C
570 WHERE A.tenant_id = pTenantId AND
571 A.ou_id = pOuId AND
572 B.in_out_cashbank_id = A.ref_alloc_id AND
573 B.doc_type_id = A.ref_doc_type_id AND
574 B.doc_type_id = vCGVoidDocTypeId AND
575 A.debit_doc_type_id IN (vCGReceiptDocTypeId) AND
576 A.debit_id <> vEmptyId AND
577 A.tenant_id = C.tenant_id AND
578 A.credit_id = C.invoice_ar_balance_id AND
579 A.credit_doc_type_id = C.doc_type_id AND
580 SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo
581 GROUP BY A.tenant_id, C.partner_id, A.debit_curr_code, A.ou_id, B.doc_type_id, B.in_out_cashbank_id, B.doc_date, B.doc_no, A.debit_doc_type_id, A.debit_id;
582 **/
583 -- Ambil dokumen CG VOID untuk ditampilkan - END
584
585 /*
586 * ambil saldo akhir
587 */
588 INSERT INTO tr_doc_ar_card
589 (session_id, tenant_id, type_data,
590 partner_id, curr_code,
591 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
592 ref_doc_type_id, ref_doc_no, ref_doc_date,
593 ext_doc_no, ext_doc_date,
594 debt_amount, credit_amount, accumulated_amount)
595 SELECT pSessionId, tenant_id, 'ZZZZ',
596 partner_id, curr_code,
597 ou_id, doc_type_id, invoice_ar_id, doc_date, doc_no,
598 ref_doc_type_id, ref_doc_no, ref_doc_date,
599 ext_doc_no, ext_doc_date,
600 SUM(debt_amount - accumulated_amount), SUM(credit_amount - accumulated_amount), 0
601 FROM tr_doc_ar_card
602 WHERE session_id = pSessionId
603 AND type_data NOT IN ('EEEE','VVVV')
604 GROUP BY tenant_id, partner_id, curr_code, ou_id, doc_type_id,
605 invoice_ar_id, doc_date, doc_no,
606 ref_doc_type_id, ref_doc_no, ref_doc_date,
607 ext_doc_no, ext_doc_date;
608
609 DELETE FROM tr_doc_ar_card
610 WHERE session_id = pSessionId AND type_data = 'AAAA' AND debt_amount = 0 AND credit_amount = 0;
611
612 UPDATE tr_doc_ar_card SET debt_amount = 0
613 WHERE debt_amount < 0 AND type_data = 'ZZZZ' AND session_id = pSessionId;
614
615 UPDATE tr_doc_ar_card SET credit_amount = 0
616 WHERE credit_amount < 0 AND type_data = 'ZZZZ' AND session_id = pSessionId;
617
618 /*
619 * delete data yg tidak sesuai dengan filter
620 */
621 IF pPartnerId <> -99 THEN
622 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId AND partner_id <> pPartnerId;
623 END IF;
624
625 IF pCurrCode <> '-99' THEN
626 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId AND curr_code <> pCurrCode;
627 END IF;
628
629 /*
630 * tampung ke table temp result untuk ditampilkan (saldo awal dan rolling transaction)
631 */
632 INSERT INTO tr_doc_ar_card_result
633 (session_id, partner_id, partner_name, curr_code, type_data, order_type_data,
634 doc_type_desc, doc_date, doc_no,
635 ref_doc_type_desc, ref_doc_date, ref_doc_no,
636 ext_doc_no, ext_doc_date,
637 debt_amount, credit_amount, rolling_total)
638 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,
639 f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
640 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,
641 A.ext_doc_no, A.ext_doc_date,
642 A.debt_amount AS debt_amount, A.credit_amount AS credit_amount, 0
643 FROM tr_doc_ar_card A
644 WHERE A.type_data IN ('AAAA', 'BBBB', 'CCCC', 'DDDD', 'EEEE', 'FFFF', 'VVVV')
645 AND A.session_id = pSessionId
646 AND (A.debt_amount > 0 OR A.credit_amount > 0);
647
648 /*
649 * tampung ke table temp result untuk ditampilkan (saldo akhir)
650 */
651 INSERT INTO tr_doc_ar_card_result
652 (session_id, partner_id, partner_name, curr_code, type_data, order_type_data,
653 doc_type_desc, doc_date, doc_no,
654 ref_doc_type_desc, ref_doc_date, ref_doc_no,
655 ext_doc_no, ext_doc_date,
656 debt_amount, credit_amount, rolling_total)
657 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,
658 f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
659 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,
660 A.ext_doc_no, A.ext_doc_date,
661 A.debt_amount AS debt_amount, A.credit_amount AS credit_amount, 0
662 FROM tr_doc_ar_card A
663 WHERE A.type_data IN ('ZZZZ')
664 AND A.session_id = pSessionId
665 AND (A.debt_amount > 0 OR A.credit_amount > 0);
666
667 Open pRefHeader FOR
668 SELECT f_get_ou_name(pOuId) AS ou_name, pPeriodFrom AS period_from, pPeriodTo AS period_to,
669 f_get_username(pUserId) AS username, pDatetime AS datetime;
670
671 RETURN NEXT pRefHeader;
672
673 Open pRefDetail FOR
674 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,
675 A.doc_type_desc AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
676 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,
677 A.ext_doc_date AS ext_doc_date, A.ext_doc_no AS ext_doc_no,
678 A.debt_amount AS debt_amount, A.credit_amount AS credit_amount, A.rolling_total AS rolling_total
679 FROM tr_doc_ar_card_result A
680 WHERE A.session_id = pSessionId
681 ORDER BY A.partner_name, A.curr_code, A.order_type_data, A.doc_date, A.doc_no;
682
683 RETURN NEXT pRefDetail;
684
685 /*
686 * delete isi table temp yang digunakan
687 */
688 DELETE FROM tr_doc_ar_card WHERE session_id = pSessionId;
689 DELETE FROM tr_doc_allocation_ar WHERE session_id = pSessionId;
690 DELETE FROM tr_doc_ar_card_result WHERE session_id = pSessionId;
691
692END;
693$BODY$
694 LANGUAGE plpgsql VOLATILE
695 COST 100;
696 /