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