· 5 years ago · Oct 05, 2020, 08:36 AM
1--Modified by Adrian, Jul 24, 2017, mengubah insert ke fi_invoice_advance_ar_balance dan fi_invoice_tax_advance_ar_balance menjadi setelah invoice lunas
2--Modified by HS, 05 Okt, 2020, mengubah value vGroupCoaPiutangCekGiro : dari AyatSilangCekGiro menjadi PiutangCekGiro
3
4CREATE OR REPLACE FUNCTION fi_submit_alloc_cashbank_ar(bigint, character varying, character varying)
5 RETURNS void AS
6$BODY$
7DECLARE
8 pTenantId ALIAS FOR $1;
9 pSessionId ALIAS FOR $2;
10 pProcessNo ALIAS FOR $3;
11
12 vProcessId bigint;
13 vAllocCashBankArId bigint;
14 vUserId bigint;
15 vInvoiceArBalanceId bigint;
16 vDatetime character varying(14);
17 vStatusRelease character varying(1);
18 vEmptyId bigint;
19 vEmptyValue character varying(1);
20
21 vCreditAmount numeric;
22 vDocTypeCreditArId bigint;
23 vCashBankInArId bigint;
24 vJournalTrxId bigint;
25 vDocJournal DOC_JOURNAL%ROWTYPE;
26 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
27 result RECORD;
28
29 vFlagYes character varying(1);
30 vFlagNo character varying(1);
31 vStatusDraft character varying(1);
32 vSignDebit character varying(1);
33 vSignCredit character varying(1);
34 vTypeRate character varying(3);
35 vSystemCOA character varying(10);
36 vActivityCOA character varying(10);
37 vAllocCashBankArDate character varying(8);
38 vGroupCoaPiutangCekGiro character varying(20);
39
40 vDownPaymentDoc bigint;
41 vSldDownPaymentDoc bigint;
42 vAdvanceInvArDoc bigint;
43 vSldAdvanceInvDoc bigint;
44 vSldFakturPajakKeluaran bigint;
45 vZero bigint;
46
47 docTypeChequeGiro bigint;
48 refDocTypeId bigint;
49 journalDescDebit character varying(1024);
50
51 vFlgForward character varying(1);
52 vCount character varying;
53 nextCashbankId bigint;
54 vRemainingAmount numeric := 0;
55
56BEGIN
57
58 vStatusRelease := 'R';
59 vEmptyId := -99;
60 vEmptyValue := ' ';
61 vCreditAmount := 0;
62 vStatusDraft := 'D';
63 vSignDebit := 'D';
64 vSignCredit := 'C';
65 vTypeRate := 'COM';
66 vSystemCOA := 'SYSTEM';
67 vActivityCOA := 'ACTIVITY';
68 vFlagYes := 'Y';
69 vFlagNo := 'N';
70 vGroupCoaPiutangCekGiro := 'PiutangCekGiro';
71 docTypeChequeGiro := 624;
72
73 vDownPaymentDoc := 242;
74 vSldDownPaymentDoc := 252;
75 vAdvanceInvArDoc := 243;
76 vSldAdvanceInvDoc := 254;
77 vSldFakturPajakKeluaran := 283;
78 vZero := 0;
79
80 SELECT A.process_message_id INTO vProcessId
81 FROM t_process_message A
82 WHERE A.tenant_id = pTenantId AND
83 A.process_name = 'fi_submit_alloc_cashbank_ar' AND
84 A.process_no = pProcessNo;
85
86 SELECT CAST(A.process_parameter_value AS bigint) INTO vAllocCashBankArId
87 FROM t_process_parameter A
88 WHERE A.process_message_id = vProcessId AND
89 A.process_parameter_key = 'allocCashBankArId';
90
91 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
92 FROM t_process_parameter A
93 WHERE A.process_message_id = vProcessId AND
94 A.process_parameter_key = 'userId';
95
96 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
97 FROM t_process_parameter A
98 WHERE A.process_message_id = vProcessId AND
99 A.process_parameter_key = 'datetime';
100
101 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
102
103/*
104 * 1.update saldo receipt ar balance untuk document alloc cash bank in ar
105 * 2.update status fi_invoice_ar_balance, fi_invoice_tax_ar_balance untuk detail debit Ar
106 * 3.insert data fi_allocation_ar_balance
107 * 4.update workflow status allocation AR
108 * 5.buat data saldo down payment, untuk invoice downpayment yang digunakan untuk alloc cash bank in AR
109 */
110 SELECT A.ref_doc_type_id, A.ref_id, A.credit_amount, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, A.doc_date
111 FROM fi_allocation_ar A
112 WHERE A.allocation_ar_id = vAllocCashBankArId INTO result;
113
114 vDocTypeCreditArId := result.ref_doc_type_id;
115 vCashBankInArId := result.ref_id;
116 vCreditAmount := result.credit_amount;
117 vOuStructure := result.ou;
118 vDocJournal := result.doc;
119 vAllocCashBankArDate := result.doc_date;
120
121
122 UPDATE fi_receipt_ar_balance SET flg_alloc = vFlagYes, ref_alloc_id = vAllocCashBankArId,
123 update_datetime = vDatetime, update_user_id = vUserId
124 WHERE receipt_ar_balance_id = vCashBankInArId;
125
126 UPDATE fi_invoice_ar_balance SET payment_amount = fi_invoice_ar_balance.payment_amount + A.debit_amount,
127 flg_payment = CASE WHEN (amount - (fi_invoice_ar_balance.payment_amount + A.debit_amount )) <> 0 THEN 'N' ELSE 'Y' END,
128 update_datetime = vDatetime, update_user_id = vUserId, version = fi_invoice_ar_balance.version + 1
129 FROM fi_allocation_ar_invoice A
130 WHERE A.allocation_ar_id = vAllocCashBankArId AND
131 invoice_ar_balance_id = A.ref_id AND
132 doc_type_id = A.ref_doc_type_id;
133
134 UPDATE fi_invoice_tax_ar_balance SET payment_amount = fi_invoice_tax_ar_balance.payment_amount + A.debit_amount,
135 flg_payment = CASE WHEN (fi_invoice_tax_ar_balance.gov_tax_amount - (fi_invoice_tax_ar_balance.payment_amount + A.debit_amount )) <> 0 THEN 'N' ELSE 'Y' END,
136 update_datetime = vDatetime, update_user_id = vUserId, version = fi_invoice_tax_ar_balance.version + 1
137 FROM fi_allocation_ar_invoice A
138 WHERE A.allocation_ar_id = vAllocCashBankArId AND
139 invoice_tax_ar_balance_id = A.ref_id AND
140 doc_type_id = A.ref_doc_type_id;
141
142 INSERT INTO fi_allocation_ar_balance
143 (allocation_ar_id, tenant_id, ou_id,
144 credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
145 debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount,
146 flg_alloc, ref_alloc_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
147 flg_receipt, ref_receipt_id,
148 "version", create_datetime, create_user_id, update_datetime, update_user_id)
149 SELECT A.allocation_ar_id, A.tenant_id, A.ou_id,
150 A.ref_doc_type_id, fi_get_rate_date_invoice_ar(A.ref_doc_type_id, A.ref_id), A.ref_id, A.curr_code, B.payment_amount,
151 B.ref_doc_type_id, fi_get_rate_date_invoice_ar(B.ref_doc_type_id, B.ref_id), B.ref_id, B.curr_code, B.debit_amount,
152 'C', vEmptyId, vEmptyId, vEmptyValue, vEmptyValue,
153 vFlagYes, vCashBankInArId,
154 0, vDatetime, vUserId, vDatetime, vUserId
155 FROM fi_allocation_ar A, fi_allocation_ar_invoice B
156 WHERE A.allocation_ar_id = vAllocCashBankArId AND
157 A.allocation_ar_id = B.allocation_ar_id;
158
159 UPDATE fi_allocation_ar SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
160 WHERE allocation_ar_id = vAllocCashBankArId;
161
162 -- Modified by Putra, 11 March 2015 add next val used for function fi_insert_invoice_ar_balance_due_date and fi_insert_invoice_tax_ar_balance_due_date
163 SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;
164
165 /*
166 * buat saldo down payment untuk downpyament yang sudah diterima cash bank in AR nya
167 */
168 INSERT INTO fi_invoice_ar_balance
169 (invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
170 doc_no, doc_date, ext_doc_no, ext_doc_date,
171 ref_doc_type_id, ref_id, partner_id, due_date,
172 curr_code, amount, remark, payment_amount, flg_payment,
173 "version", create_datetime, create_user_id, update_datetime, update_user_id)
174 SELECT vInvoiceArBalanceId, B.tenant_id, B.ou_id, vSldDownPaymentDoc, B.invoice_ar_id,
175 B.doc_no, vAllocCashBankArDate, B.ext_doc_no, B.ext_doc_date,
176 B.doc_type_id, B.invoice_ar_balance_id, B.partner_id, B.doc_date,
177 B.curr_code, B.amount * -1, vEmptyValue, 0, 'N',
178 0, vDatetime, vUserId, vDatetime, vUserId
179 FROM fi_allocation_ar_invoice A, fi_invoice_ar_balance B
180 WHERE A.allocation_ar_id = vAllocCashBankArId AND
181 A.ref_doc_type_id = vDownPaymentDoc AND
182 A.ref_id = B.invoice_ar_balance_id AND
183 A.ref_doc_type_id = B.doc_type_id;
184
185 /*
186 * Putra, 11 March 2015
187 * Call function for insert due_date from fi_invoice_ar_balance into fi_invoice_ar_balance_due_date
188 * Call function for insert due_date from fi_invoice_tax_ar_balance into fi_invoice_tax_ar_balance_due_date
189 * @see http://jleaf.org:8181/browse/ERPDB-211
190 */
191 PERFORM fi_insert_invoice_ar_balance_due_date(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
192
193 /*
194 * buat saldo advance invoice ar yang sudah diterima cash bank in AR nya
195 */
196 /*
197 * NK, 14 Nov 2014 : tambah field receive_id, receive_date, sales_invoice_id, sales_invoice_date
198 */
199 /**
200 * amount - payment_amount harus 0
201 * Adrian, Jul 24, 2017
202 */
203 INSERT INTO fi_invoice_advance_ar_balance
204 (tenant_id, ou_id, doc_type_id, invoice_ar_id,
205 doc_no, doc_date, ext_doc_no, ext_doc_date,
206 ref_doc_type_id, ref_id, partner_id,
207 receive_id, receive_date, sales_invoice_id, sales_invoice_date,
208 curr_code, amount,
209 "version", create_datetime, create_user_id, update_datetime, update_user_id)
210 SELECT B.tenant_id, B.ou_id, vSldAdvanceInvDoc, B.invoice_ar_id,
211 B.doc_no, B.doc_date, B.ext_doc_no, B.ext_doc_date,
212 B.doc_type_id, B.invoice_ar_balance_id, B.partner_id,
213 vAllocCashBankArId, vAllocCashBankArDate, vEmptyId, vEmptyValue,
214 B.curr_code, B.amount * -1,
215 0, vDatetime, vUserId, vDatetime, vUserId
216 FROM fi_allocation_ar_invoice A, fi_invoice_ar_balance B
217 WHERE A.allocation_ar_id = vAllocCashBankArId AND
218 A.ref_doc_type_id = vAdvanceInvArDoc AND
219 A.ref_id = B.invoice_ar_balance_id AND
220 A.ref_doc_type_id = B.doc_type_id AND
221 B.amount - B.payment_amount = vZero AND
222 NOT EXISTS (
223 SELECT 1 FROM fi_invoice_advance_ar_balance Z
224 WHERE Z.invoice_ar_id = B.invoice_ar_id
225 );
226
227 /*
228 * NK, 17 Nov 2014 :
229 * tambah membuat table invoice_tax_advance_ar_balance
230 * WTC, 22 Jun 2015: perbaikan join fi_allocation_ar_invoice.ref_id harusnya ke fi_invoice_ar_balance.invoice_ar_balance_id,
231 * bukan ke fi_invoice_tax_ar_balance.invoice_tax_ar_balance_id
232 */
233 /**
234 * tax_amount - payment_amount harus 0
235 * Adrian, Jul 24, 2017
236 */
237 INSERT INTO fi_invoice_tax_advance_ar_balance
238 (tenant_id, ou_id, doc_type_id, invoice_ar_id, partner_id,
239 tax_id, tax_no, tax_date, curr_code, tax_amount,
240 tax_curr_code, gov_tax_amount, ref_doc_type_id, ref_id,
241 receive_id, receive_date, sales_invoice_id, sales_invoice_date,
242 "version", create_datetime, create_user_id, update_datetime, update_user_id)
243 SELECT B.tenant_id, B.ou_id, vSldFakturPajakKeluaran, B.invoice_ar_id, B.partner_id,
244 C.tax_id, C.tax_no, C.tax_date, C.curr_code, C.tax_amount * -1,
245 C.tax_curr_code, C.gov_tax_amount * -1, B.doc_type_id, B.invoice_ar_balance_id,
246 vAllocCashBankArId, vAllocCashBankArDate, vEmptyId, vEmptyValue,
247 0, vDatetime, vUserId, vDatetime, vUserId
248 FROM fi_allocation_ar_invoice A, fi_invoice_ar_balance B, fi_invoice_tax_ar_balance C
249 WHERE A.allocation_ar_id = vAllocCashBankArId AND
250 A.ref_doc_type_id = vAdvanceInvArDoc AND
251 A.ref_id = B.invoice_ar_balance_id AND
252 C.invoice_ar_balance_id = B.invoice_ar_balance_id AND
253 A.ref_doc_type_id = B.doc_type_id AND
254 C.gov_tax_amount - C.payment_amount = vZero AND
255 NOT EXISTS (
256 SELECT 1 FROM fi_invoice_tax_advance_ar_balance Z
257 WHERE Z.invoice_ar_id = B.invoice_ar_id
258 );
259
260 /*
261 * membuat data sl_so_balance_advance_invoice, untuk invoice uang muka ar yang digunakan untuk di alokasi terhadap cash bank in ar
262 * - fredi, 10 Feb 2016, http://jleaf.org:8112/issue/ERP-55
263 * - add gov_base_amount and gov_tax_amount
264 *
265 */
266 /**
267 * amount - payment_amount harus 0
268 * Adrian, Jul 24, 2017
269 */
270 INSERT INTO sl_so_balance_advance_invoice
271 (tenant_id, ou_id, partner_id, so_id,
272 ref_doc_type_id, ref_id, advance_curr_code, advance_amount,
273 tax_id, tax_amount, tax_percentage,
274 flg_invoice, flg_invoice_temp, invoice_id,
275 "version", create_datetime, create_user_id, update_datetime, update_user_id,
276 ref_doc_no, ref_doc_date, gov_tax_amount, gov_base_amount)
277 SELECT A.tenant_id, A.ou_id, A.partner_id, C.ref_id,
278 B.ref_doc_type_id, B.ref_id, C.curr_code, C.amount,
279 COALESCE(D.tax_id, vEmptyId), COALESCE(D.tax_amount, 0), COALESCE(E.percentage, 0),
280 'N', 'N', vEmptyId,
281 0, vDatetime, vUserId, vDatetime, vUserId,
282 C.doc_no, C.doc_date, COALESCE(D.gov_tax_amount, 0), COALESCE(D.gov_base_amount, 0)
283 FROM fi_allocation_ar A, fi_allocation_ar_invoice B, fi_invoice_ar_balance C
284 LEFT OUTER JOIN fi_invoice_tax_ar_balance D ON C.invoice_ar_balance_id = D.invoice_ar_balance_id
285 LEFT OUTER JOIN m_tax E ON D.tax_id = E.tax_id
286 WHERE A.allocation_ar_id = vAllocCashBankArId AND
287 A.allocation_ar_id = B.allocation_ar_id AND
288 B.ref_doc_type_id = vAdvanceInvArDoc AND
289 B.ref_id = C.invoice_ar_balance_id AND
290 B.ref_doc_type_id = C.doc_type_id AND
291 C.amount - C.payment_amount = vZero AND
292 NOT EXISTS (
293 SELECT 1 FROM sl_so_balance_advance_invoice Z
294 WHERE Z.tenant_id = A.tenant_id
295 AND Z.ou_id = A.ou_id
296 AND Z.so_id = C.ref_id
297 AND Z.ref_doc_type_id = B.ref_doc_type_id
298 AND Z.ref_id = B.ref_id
299 );
300
301 /*
302 * Agik, 23 Oct 2015
303 * Get RefDocTypeId dari saldo yg dialokasikan
304 */
305 SELECT doc_type_id INTO refDocTypeId
306 FROM fi_receipt_ar_balance
307 WHERE receipt_ar_balance_id = vCashBankInArId;
308
309 /*
310 * membuat data transaksi jurnal :
311 * 1. buat admin
312 * 2. buat temlate jurnal
313 */
314 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
315 FROM fi_allocation_ar A
316 WHERE A.allocation_ar_id = vAllocCashBankArId;
317
318 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
319
320 INSERT INTO gl_journal_trx
321 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
322 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
323 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
324 "version", create_datetime, create_user_id, update_datetime, update_user_id)
325 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.allocation_ar_id, A.doc_no, A.doc_date,
326 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
327 A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
328 0, vDatetime, vUserId, vDatetime, vUserId
329 FROM fi_allocation_ar A
330 WHERE A.allocation_ar_id = vAllocCashBankArId;
331
332 /*
333 * journal ref doc type = Cash Bank In Ar Dokumen
334 * tanggal rate menggunakan tanggal Cash Bank In Ar Dokumen
335 */
336 IF refDocTypeId = docTypeChequeGiro THEN
337
338 INSERT INTO tt_journal_trx_item
339 (session_id, tenant_id, journal_trx_id, line_no,
340 ref_doc_type_id, ref_id,
341 partner_id, product_id, cashbank_id, ou_rc_id,
342 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
343 coa_id, curr_code, qty, uom_id,
344 amount, journal_date, type_rate,
345 numerator_rate, denominator_rate, journal_desc, remark)
346 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
347 A.ref_doc_type_id, A.ref_id,
348 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
349 vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
350 f_get_system_coa_by_group_coa(A.tenant_id , vGroupCoaPiutangCekGiro), A.curr_code, 0, vEmptyId,
351 CASE WHEN A.flg_automatic_credit_note_ar = 'Y' THEN A.debit_amount ELSE A.credit_amount END AS amount,
352 fi_get_rate_date_invoice_ar(A.ref_doc_type_id, A.ref_id), vTypeRate,
353 1, 1, 'CHEQUE_GIRO', A.remark
354 FROM fi_allocation_ar A
355 WHERE A.allocation_ar_id = vAllocCashBankArId;
356
357 ELSE
358
359 INSERT INTO tt_journal_trx_item
360 (session_id, tenant_id, journal_trx_id, line_no,
361 ref_doc_type_id, ref_id,
362 partner_id, product_id, cashbank_id, ou_rc_id,
363 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
364 coa_id, curr_code, qty, uom_id,
365 amount, journal_date, type_rate,
366 numerator_rate, denominator_rate, journal_desc, remark)
367 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
368 A.ref_doc_type_id, A.ref_id,
369 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
370 vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
371 f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
372 CASE WHEN flg_automatic_credit_note_ar = 'Y' THEN A.debit_amount ELSE A.credit_amount END AS amount,
373 fi_get_rate_date_invoice_ar(A.ref_doc_type_id, A.ref_id), vTypeRate,
374 1, 1, 'AR_CASHBANK_IN', A.remark
375 FROM fi_allocation_ar A
376 WHERE A.allocation_ar_id = vAllocCashBankArId;
377
378 END IF;
379
380 /*
381 * jurnal cost alloc cash bank in AR :
382 * Debit cost jika nilai cost amount < 0
383 * Credit cost jika nilai cost amount > 0
384 *
385 * Mod by WTC, 160912, tulis data jurnal atas cost, jika tidak mau simpan selisih amount
386 * untuk alokasi berikutnya.
387 */
388 INSERT INTO tt_journal_trx_item
389 (session_id, tenant_id, journal_trx_id, line_no,
390 ref_doc_type_id, ref_id,
391 partner_id, product_id, cashbank_id, ou_rc_id,
392 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
393 coa_id, curr_code, qty, uom_id,
394 amount, journal_date, type_rate,
395 numerator_rate, denominator_rate, journal_desc, remark)
396 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
397 A.doc_type_id, B.allocation_ar_cost_id,
398 A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
399 B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
400 E.coa_id, B.curr_code, 0, vEmptyId,
401 B.cost_amount, A.doc_date, vTypeRate,
402 1, 1, 'AR_COST_CREDIT', B.remark
403 FROM fi_allocation_ar A, fi_allocation_ar_cost B, m_activity_gl E
404 WHERE A.allocation_ar_id = vAllocCashBankArId AND
405 A.allocation_ar_id = B.allocation_ar_id AND
406 B.activity_gl_id = E.activity_gl_id AND
407 B.cost_amount > 0 AND
408 A.flg_automatic_credit_note_ar = 'N';
409
410 INSERT INTO tt_journal_trx_item
411 (session_id, tenant_id, journal_trx_id, line_no,
412 ref_doc_type_id, ref_id,
413 partner_id, product_id, cashbank_id, ou_rc_id,
414 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
415 coa_id, curr_code, qty, uom_id,
416 amount, journal_date, type_rate,
417 numerator_rate, denominator_rate, journal_desc, remark)
418 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
419 A.doc_type_id, B.allocation_ar_cost_id,
420 A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
421 B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
422 E.coa_id, B.curr_code, 0, vEmptyId,
423 B.cost_amount * -1, A.doc_date, vTypeRate,
424 1, 1, 'AR_COST_DEBIT', B.remark
425 FROM fi_allocation_ar A, fi_allocation_ar_cost B, m_activity_gl E
426 WHERE A.allocation_ar_id = vAllocCashBankArId AND
427 A.allocation_ar_id = B.allocation_ar_id AND
428 B.activity_gl_id = E.activity_gl_id AND
429 B.cost_amount < 0 AND
430 A.flg_automatic_credit_note_ar = 'N';
431
432 /*
433 * journal detail debit invoice yang dilunasi oleh credit invoice
434 * tanggal rate menggunakan tanggal invoice
435 */
436 INSERT INTO tt_journal_trx_item
437 (session_id, tenant_id, journal_trx_id, line_no,
438 ref_doc_type_id, ref_id,
439 partner_id, product_id, cashbank_id, ou_rc_id,
440 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
441 coa_id, curr_code, qty, uom_id,
442 amount, journal_date, type_rate,
443 numerator_rate, denominator_rate, journal_desc, remark)
444 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
445 B.debit_doc_type_id, B.debit_id,
446 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
447 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
448 f_get_ar_coa_partner(A.tenant_id, A.partner_id), B.debit_curr_code, 0, vEmptyId,
449 SUM(B.debit_amount), B.debit_doc_date, vTypeRate,
450 1, 1, 'AR_DEBIT_INVOICE', vEmptyValue
451 FROM fi_allocation_ar A, fi_allocation_ar_balance B
452 WHERE A.allocation_ar_id = vAllocCashBankArId AND
453 A.allocation_ar_id = B.allocation_ar_id
454 GROUP BY A.tenant_id, B.debit_doc_type_id, B.debit_id, A.partner_id, B.debit_curr_code, B.debit_doc_date;
455
456 INSERT INTO gl_journal_trx_item
457 (tenant_id, journal_trx_id, line_no,
458 ref_doc_type_id, ref_id,
459 partner_id, product_id, cashbank_id, ou_rc_id,
460 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
461 coa_id, curr_code, qty, uom_id,
462 amount, journal_date, type_rate,
463 numerator_rate, denominator_rate, journal_desc, remark,
464 "version", create_datetime, create_user_id, update_datetime, update_user_id)
465 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
466 A.ref_doc_type_id, A.ref_id,
467 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
468 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
469 A.coa_id, A.curr_code, A.qty, A.uom_id,
470 A.amount, A.journal_date, A.type_rate,
471 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
472 0, vDatetime, vUserId, vDatetime, vUserId
473 FROM tt_journal_trx_item A
474 WHERE A.session_id = pSessionId AND
475 journal_desc IN ('AR_DEBIT_INVOICE', 'AR_COST_CREDIT', 'AR_COST_DEBIT');
476
477 INSERT INTO gl_journal_trx_mapping
478 (tenant_id, journal_trx_id, line_no,
479 ref_doc_type_id, ref_id,
480 partner_id, product_id, cashbank_id, ou_rc_id,
481 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
482 coa_id, curr_code, qty, uom_id,
483 amount, journal_date, type_rate,
484 numerator_rate, denominator_rate, journal_desc, remark,
485 "version", create_datetime, create_user_id, update_datetime, update_user_id)
486 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
487 A.ref_doc_type_id, A.ref_id,
488 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
489 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
490 A.coa_id, A.curr_code, A.qty, A.uom_id,
491 A.amount, A.journal_date, A.type_rate,
492 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
493 0, vDatetime, vUserId, vDatetime, vUserId
494 FROM tt_journal_trx_item A
495 WHERE A.session_id = pSessionId AND
496 journal_desc IN ('AR_CASHBANK_IN', 'CHEQUE_GIRO');
497
498 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
499
500 /*
501 * Automatic generate Credit Note AR, if flg_automatic_credit_note_ar = Y and remaining amount > 0
502 */
503 SELECT flg_automatic_credit_note_ar, payment_amount
504 INTO vFlgForward, vRemainingAmount
505 FROM fi_allocation_ar
506 WHERE allocation_ar_id = vAllocCashBankArId;
507
508 IF (vFlgForward = vFlagYes AND vRemainingAmount > 0) THEN
509 --SELECT fi_automatic_create_credit_note_from_alloc_cb_in(pSessionId, pTenantId, vAllocCashBankArId, vUserId, vDatetime) INTO vCount;
510
511 -- get sequence cb_in_out_cashbank_seq
512 SELECT NEXTVAL('cb_in_out_cashbank_seq') INTO nextCashbankId;
513
514 -- insert fi_receipt_ar_balance untuk mencatat
515 INSERT INTO fi_receipt_ar_balance(
516 receipt_ar_balance_id, tenant_id, ou_id, doc_type_id, doc_no,
517 doc_date, cashbank_id, partner_id, curr_code, amount, remark,
518 flg_alloc, ref_alloc_id, version, create_datetime, create_user_id,
519 update_datetime, update_user_id, ref_item_id, cheque_giro_no,
520 cheque_giro_date, cheque_giro_bank)
521 SELECT nextCashbankId, a.tenant_id, a.ou_id, a.doc_type_id, a.doc_no,
522 a.doc_date, a.cashbank_id, a.partner_id, a.curr_code, b.payment_amount, a.remark,
523 'N', -99 AS ref_alloc_id, 0 AS version, vDatetime, vUserId,
524 vDatetime, vUserId, a.ref_item_id, a.cheque_giro_no,
525 a.cheque_giro_date, a.cheque_giro_bank
526 FROM fi_receipt_ar_balance a
527 INNER JOIN fi_allocation_ar b ON a.ref_alloc_id = b.allocation_ar_id
528 WHERE a.receipt_ar_balance_id = vCashBankInArId;
529
530 UPDATE fi_receipt_ar_balance a
531 SET amount = b.debit_amount, version = a.version+1
532 FROM fi_allocation_ar b
533 WHERE a.ref_alloc_id = b.allocation_ar_id
534 AND a.receipt_ar_balance_id = vCashBankInArId;
535
536 END IF;
537END;
538$BODY$
539 LANGUAGE plpgsql VOLATILE
540 COST 100;
541 /