fi_submit_alloc_cashbank_ar

--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;

    /*
     * 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
     */
    /**
     * 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
        B.amount - B.payment_amount = vZero;

    /*
     * 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;

    /*
     * 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;
  /