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