Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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
- CREATE OR REPLACE FUNCTION fi_submit_alloc_cashbank_ar(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vAllocCashBankArId bigint;
- vUserId bigint;
- vInvoiceArBalanceId bigint;
- vDatetime character varying(14);
- vStatusRelease character varying(1);
- vEmptyId bigint;
- vEmptyValue character varying(1);
- vCreditAmount numeric;
- vDocTypeCreditArId bigint;
- vCashBankInArId bigint;
- vJournalTrxId bigint;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vFlagYes character varying(1);
- vFlagNo character varying(1);
- vStatusDraft character varying(1);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vSystemCOA character varying(10);
- vActivityCOA character varying(10);
- vAllocCashBankArDate character varying(8);
- vGroupCoaPiutangCekGiro character varying(20);
- vDownPaymentDoc bigint;
- vSldDownPaymentDoc bigint;
- vAdvanceInvArDoc bigint;
- vSldAdvanceInvDoc bigint;
- vSldFakturPajakKeluaran bigint;
- vZero bigint;
- docTypeChequeGiro bigint;
- refDocTypeId bigint;
- journalDescDebit character varying(1024);
- vFlgForward character varying(1);
- vCount character varying;
- nextCashbankId bigint;
- BEGIN
- vStatusRelease := 'R';
- vEmptyId := -99;
- vEmptyValue := ' ';
- vCreditAmount := 0;
- vStatusDraft := 'D';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vSystemCOA := 'SYSTEM';
- vActivityCOA := 'ACTIVITY';
- vFlagYes := 'Y';
- vFlagNo := 'N';
- vGroupCoaPiutangCekGiro := 'AyatSilangCekGiro';
- docTypeChequeGiro := 624;
- vDownPaymentDoc := 242;
- vSldDownPaymentDoc := 252;
- vAdvanceInvArDoc := 243;
- vSldAdvanceInvDoc := 254;
- vSldFakturPajakKeluaran := 283;
- vZero := 0;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'fi_submit_alloc_cashbank_ar' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vAllocCashBankArId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'allocCashBankArId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'userId';
- SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * 1.update saldo receipt ar balance untuk document alloc cash bank in ar
- * 2.update status fi_invoice_ar_balance, fi_invoice_tax_ar_balance untuk detail debit Ar
- * 3.insert data fi_allocation_ar_balance
- * 4.update workflow status allocation AR
- * 5.buat data saldo down payment, untuk invoice downpayment yang digunakan untuk alloc cash bank in AR
- */
- 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
- FROM fi_allocation_ar A
- WHERE A.allocation_ar_id = vAllocCashBankArId INTO result;
- vDocTypeCreditArId := result.ref_doc_type_id;
- vCashBankInArId := result.ref_id;
- vCreditAmount := result.credit_amount;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- vAllocCashBankArDate := result.doc_date;
- UPDATE fi_receipt_ar_balance SET flg_alloc = vFlagYes, ref_alloc_id = vAllocCashBankArId,
- update_datetime = vDatetime, update_user_id = vUserId
- WHERE receipt_ar_balance_id = vCashBankInArId;
- UPDATE fi_invoice_ar_balance SET payment_amount = fi_invoice_ar_balance.payment_amount + A.debit_amount,
- flg_payment = CASE WHEN (amount - (fi_invoice_ar_balance.payment_amount + A.debit_amount )) <> 0 THEN 'N' ELSE 'Y' END,
- update_datetime = vDatetime, update_user_id = vUserId
- FROM fi_allocation_ar_invoice A
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- invoice_ar_balance_id = A.ref_id AND
- doc_type_id = A.ref_doc_type_id;
- UPDATE fi_invoice_tax_ar_balance SET payment_amount = fi_invoice_tax_ar_balance.payment_amount + A.debit_amount,
- 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,
- update_datetime = vDatetime, update_user_id = vUserId
- FROM fi_allocation_ar_invoice A
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- invoice_tax_ar_balance_id = A.ref_id AND
- doc_type_id = A.ref_doc_type_id;
- INSERT INTO fi_allocation_ar_balance
- (allocation_ar_id, tenant_id, ou_id,
- credit_doc_type_id, credit_doc_date, credit_id, credit_curr_code, credit_amount,
- debit_doc_type_id, debit_doc_date, debit_id, debit_curr_code, debit_amount,
- flg_alloc, ref_alloc_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
- flg_receipt, ref_receipt_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.allocation_ar_id, A.tenant_id, A.ou_id,
- 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,
- 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,
- 'C', vEmptyId, vEmptyId, vEmptyValue, vEmptyValue,
- vFlagYes, vCashBankInArId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM fi_allocation_ar A, fi_allocation_ar_invoice B
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.allocation_ar_id = B.allocation_ar_id;
- UPDATE fi_allocation_ar SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
- WHERE allocation_ar_id = vAllocCashBankArId;
- -- 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
- SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;
- /*
- * buat saldo down payment untuk downpyament yang sudah diterima cash bank in AR nya
- */
- INSERT INTO fi_invoice_ar_balance
- (invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
- doc_no, doc_date, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, partner_id, due_date,
- curr_code, amount, remark, payment_amount, flg_payment,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vInvoiceArBalanceId, B.tenant_id, B.ou_id, vSldDownPaymentDoc, B.invoice_ar_id,
- B.doc_no, vAllocCashBankArDate, B.ext_doc_no, B.ext_doc_date,
- B.doc_type_id, B.invoice_ar_balance_id, B.partner_id, B.doc_date,
- B.curr_code, B.amount * -1, vEmptyValue, 0, 'N',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM fi_allocation_ar_invoice A, fi_invoice_ar_balance B
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.ref_doc_type_id = vDownPaymentDoc AND
- A.ref_id = B.invoice_ar_balance_id AND
- A.ref_doc_type_id = B.doc_type_id;
- /*
- * Putra, 11 March 2015
- * Call function for insert due_date from fi_invoice_ar_balance into fi_invoice_ar_balance_due_date
- * Call function for insert due_date from fi_invoice_tax_ar_balance into fi_invoice_tax_ar_balance_due_date
- * @see http://jleaf.org:8181/browse/ERPDB-211
- */
- PERFORM fi_insert_invoice_ar_balance_due_date(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
- /*
- * buat saldo advance invoice ar yang sudah diterima cash bank in AR nya
- */
- /*
- * NK, 14 Nov 2014 : tambah field receive_id, receive_date, sales_invoice_id, sales_invoice_date
- */
- /**
- * amount - payment_amount harus 0
- * Adrian, Jul 24, 2017
- */
- INSERT INTO fi_invoice_advance_ar_balance
- (tenant_id, ou_id, doc_type_id, invoice_ar_id,
- doc_no, doc_date, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, partner_id,
- receive_id, receive_date, sales_invoice_id, sales_invoice_date,
- curr_code, amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT B.tenant_id, B.ou_id, vSldAdvanceInvDoc, B.invoice_ar_id,
- B.doc_no, B.doc_date, B.ext_doc_no, B.ext_doc_date,
- B.doc_type_id, B.invoice_ar_balance_id, B.partner_id,
- vAllocCashBankArId, vAllocCashBankArDate, vEmptyId, vEmptyValue,
- B.curr_code, B.amount * -1,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM fi_allocation_ar_invoice A, fi_invoice_ar_balance B
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.ref_doc_type_id = vAdvanceInvArDoc AND
- A.ref_id = B.invoice_ar_balance_id AND
- A.ref_doc_type_id = B.doc_type_id AND
- B.amount - B.payment_amount = vZero AND
- NOT EXISTS (
- SELECT 1 FROM fi_invoice_advance_ar_balance Z
- WHERE Z.invoice_ar_id = B.invoice_ar_id
- );
- /*
- * NK, 17 Nov 2014 :
- * tambah membuat table invoice_tax_advance_ar_balance
- * WTC, 22 Jun 2015: perbaikan join fi_allocation_ar_invoice.ref_id harusnya ke fi_invoice_ar_balance.invoice_ar_balance_id,
- * bukan ke fi_invoice_tax_ar_balance.invoice_tax_ar_balance_id
- */
- /**
- * tax_amount - payment_amount harus 0
- * Adrian, Jul 24, 2017
- */
- INSERT INTO fi_invoice_tax_advance_ar_balance
- (tenant_id, ou_id, doc_type_id, invoice_ar_id, partner_id,
- tax_id, tax_no, tax_date, curr_code, tax_amount,
- tax_curr_code, gov_tax_amount, ref_doc_type_id, ref_id,
- receive_id, receive_date, sales_invoice_id, sales_invoice_date,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT B.tenant_id, B.ou_id, vSldFakturPajakKeluaran, B.invoice_ar_id, B.partner_id,
- C.tax_id, C.tax_no, C.tax_date, C.curr_code, C.tax_amount * -1,
- C.tax_curr_code, C.gov_tax_amount * -1, B.doc_type_id, B.invoice_ar_balance_id,
- vAllocCashBankArId, vAllocCashBankArDate, vEmptyId, vEmptyValue,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM fi_allocation_ar_invoice A, fi_invoice_ar_balance B, fi_invoice_tax_ar_balance C
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.ref_doc_type_id = vAdvanceInvArDoc AND
- A.ref_id = B.invoice_ar_balance_id AND
- C.invoice_ar_balance_id = B.invoice_ar_balance_id AND
- A.ref_doc_type_id = B.doc_type_id AND
- C.gov_tax_amount - C.payment_amount = vZero AND
- NOT EXISTS (
- SELECT 1 FROM fi_invoice_tax_advance_ar_balance Z
- WHERE Z.invoice_ar_id = B.invoice_ar_id
- );
- /*
- * membuat data sl_so_balance_advance_invoice, untuk invoice uang muka ar yang digunakan untuk di alokasi terhadap cash bank in ar
- * - fredi, 10 Feb 2016, http://jleaf.org:8112/issue/ERP-55
- * - add gov_base_amount and gov_tax_amount
- *
- */
- /**
- * amount - payment_amount harus 0
- * Adrian, Jul 24, 2017
- */
- INSERT INTO sl_so_balance_advance_invoice
- (tenant_id, ou_id, partner_id, so_id,
- ref_doc_type_id, ref_id, advance_curr_code, advance_amount,
- tax_id, tax_amount, tax_percentage,
- flg_invoice, flg_invoice_temp, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id,
- ref_doc_no, ref_doc_date, gov_tax_amount, gov_base_amount)
- SELECT A.tenant_id, A.ou_id, A.partner_id, C.ref_id,
- B.ref_doc_type_id, B.ref_id, C.curr_code, C.amount,
- COALESCE(D.tax_id, vEmptyId), COALESCE(D.tax_amount, 0), COALESCE(E.percentage, 0),
- 'N', 'N', vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId,
- C.doc_no, C.doc_date, COALESCE(D.gov_tax_amount, 0), COALESCE(D.gov_base_amount, 0)
- FROM fi_allocation_ar A, fi_allocation_ar_invoice B, fi_invoice_ar_balance C
- LEFT OUTER JOIN fi_invoice_tax_ar_balance D ON C.invoice_ar_balance_id = D.invoice_ar_balance_id
- LEFT OUTER JOIN m_tax E ON D.tax_id = E.tax_id
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.allocation_ar_id = B.allocation_ar_id AND
- B.ref_doc_type_id = vAdvanceInvArDoc AND
- B.ref_id = C.invoice_ar_balance_id AND
- B.ref_doc_type_id = C.doc_type_id AND
- C.amount - C.payment_amount = vZero AND
- NOT EXISTS (
- SELECT 1 FROM sl_so_balance_advance_invoice Z
- WHERE Z.tenant_id = A.tenant_id
- AND Z.ou_id = A.ou_id
- AND Z.so_id = C.ref_id
- AND Z.ref_doc_type_id = B.ref_doc_type_id
- AND Z.ref_id = B.ref_id
- );
- /*
- * Agik, 23 Oct 2015
- * Get RefDocTypeId dari saldo yg dialokasikan
- */
- SELECT doc_type_id INTO refDocTypeId
- FROM fi_receipt_ar_balance
- WHERE receipt_ar_balance_id = vCashBankInArId;
- /*
- * membuat data transaksi jurnal :
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- 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)
- FROM fi_allocation_ar A
- WHERE A.allocation_ar_id = vAllocCashBankArId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- INSERT INTO gl_journal_trx
- (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.allocation_ar_id, A.doc_no, A.doc_date,
- (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,
- A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM fi_allocation_ar A
- WHERE A.allocation_ar_id = vAllocCashBankArId;
- /*
- * journal ref doc type = Cash Bank In Ar Dokumen
- * tanggal rate menggunakan tanggal Cash Bank In Ar Dokumen
- */
- IF refDocTypeId = docTypeChequeGiro THEN
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id , vGroupCoaPiutangCekGiro), A.curr_code, 0, vEmptyId,
- CASE WHEN A.flg_automatic_credit_note_ar = 'Y' THEN A.debit_amount ELSE A.credit_amount END AS amount,
- fi_get_rate_date_invoice_ar(A.ref_doc_type_id, A.ref_id), vTypeRate,
- 1, 1, 'CHEQUE_GIRO', A.remark
- FROM fi_allocation_ar A
- WHERE A.allocation_ar_id = vAllocCashBankArId;
- ELSE
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
- CASE WHEN flg_automatic_credit_note_ar = 'Y' THEN A.debit_amount ELSE A.credit_amount END AS amount,
- fi_get_rate_date_invoice_ar(A.ref_doc_type_id, A.ref_id), vTypeRate,
- 1, 1, 'AR_CASHBANK_IN', A.remark
- FROM fi_allocation_ar A
- WHERE A.allocation_ar_id = vAllocCashBankArId;
- END IF;
- /*
- * jurnal cost alloc cash bank in AR :
- * Debit cost jika nilai cost amount < 0
- * Credit cost jika nilai cost amount > 0
- *
- * Mod by WTC, 160912, tulis data jurnal atas cost, jika tidak mau simpan selisih amount
- * untuk alokasi berikutnya.
- */
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.allocation_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
- E.coa_id, B.curr_code, 0, vEmptyId,
- B.cost_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_COST_CREDIT', B.remark
- FROM fi_allocation_ar A, fi_allocation_ar_cost B, m_activity_gl E
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.allocation_ar_id = B.allocation_ar_id AND
- B.activity_gl_id = E.activity_gl_id AND
- B.cost_amount > 0 AND
- A.flg_automatic_credit_note_ar = 'N';
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.allocation_ar_cost_id,
- A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
- B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
- E.coa_id, B.curr_code, 0, vEmptyId,
- B.cost_amount * -1, A.doc_date, vTypeRate,
- 1, 1, 'AR_COST_DEBIT', B.remark
- FROM fi_allocation_ar A, fi_allocation_ar_cost B, m_activity_gl E
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.allocation_ar_id = B.allocation_ar_id AND
- B.activity_gl_id = E.activity_gl_id AND
- B.cost_amount < 0 AND
- A.flg_automatic_credit_note_ar = 'N';
- /*
- * journal detail debit invoice yang dilunasi oleh credit invoice
- * tanggal rate menggunakan tanggal invoice
- */
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- B.debit_doc_type_id, B.debit_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, A.partner_id), B.debit_curr_code, 0, vEmptyId,
- SUM(B.debit_amount), B.debit_doc_date, vTypeRate,
- 1, 1, 'AR_DEBIT_INVOICE', vEmptyValue
- FROM fi_allocation_ar A, fi_allocation_ar_balance B
- WHERE A.allocation_ar_id = vAllocCashBankArId AND
- A.allocation_ar_id = B.allocation_ar_id
- GROUP BY A.tenant_id, B.debit_doc_type_id, B.debit_id, A.partner_id, B.debit_curr_code, B.debit_doc_date;
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- journal_desc IN ('AR_DEBIT_INVOICE', 'AR_COST_CREDIT', 'AR_COST_DEBIT');
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- journal_desc IN ('AR_CASHBANK_IN', 'CHEQUE_GIRO');
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * Automatic generate Credit Note AR, if flg_automatic_credit_note_ar = Y
- */
- SELECT flg_automatic_credit_note_ar INTO vFlgForward
- FROM fi_allocation_ar
- WHERE allocation_ar_id = vAllocCashBankArId;
- IF vFlgForward = vFlagYes THEN
- --SELECT fi_automatic_create_credit_note_from_alloc_cb_in(pSessionId, pTenantId, vAllocCashBankArId, vUserId, vDatetime) INTO vCount;
- -- get sequence cb_in_out_cashbank_seq
- SELECT NEXTVAL('cb_in_out_cashbank_seq') INTO nextCashbankId;
- -- insert fi_receipt_ar_balance untuk mencatat
- INSERT INTO fi_receipt_ar_balance(
- receipt_ar_balance_id, tenant_id, ou_id, doc_type_id, doc_no,
- doc_date, cashbank_id, partner_id, curr_code, amount, remark,
- flg_alloc, ref_alloc_id, version, create_datetime, create_user_id,
- update_datetime, update_user_id, ref_item_id, cheque_giro_no,
- cheque_giro_date, cheque_giro_bank)
- SELECT nextCashbankId, a.tenant_id, a.ou_id, a.doc_type_id, a.doc_no,
- a.doc_date, a.cashbank_id, a.partner_id, a.curr_code, b.payment_amount, a.remark,
- 'N', -99 AS ref_alloc_id, 0 AS version, vDatetime, vUserId,
- vDatetime, vUserId, a.ref_item_id, a.cheque_giro_no,
- a.cheque_giro_date, a.cheque_giro_bank
- FROM fi_receipt_ar_balance a
- INNER JOIN fi_allocation_ar b ON a.ref_alloc_id = b.allocation_ar_id
- WHERE a.receipt_ar_balance_id = vCashBankInArId;
- UPDATE fi_receipt_ar_balance a
- SET amount = b.debit_amount,
- version = a.version+1
- FROM fi_allocation_ar b
- WHERE a.ref_alloc_id = b.allocation_ar_id
- AND a.receipt_ar_balance_id = vCashBankInArId;
- END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Add Comment
Please, Sign In to add comment