Function Generate jurnal DO

 CREATE OR REPLACE FUNCTION f_generate_jurnal_delivery_order(character varying,bigint,character varying,character varying,
    bigint,character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pDateForm           ALIAS FOR $3;
    pDateTo             ALIAS FOR $4;
    pUserId             ALIAS FOR $5;
    pDatetime           ALIAS FOR $6;

    -- For Journal
    vEmptyId                bigint;
    vSignDebt               character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(5);
    vActivityCOA            character varying(10);
    vSystemCOA              character varying(10);
    vTaxCOA                 character varying(10);
    vEmptyValue             character varying(1);
    vFlgNo                  character varying(1);
    vEmptyString            character varying;
    vStatusDraft            character varying(1);

    vCreditNoteDoc          bigint;
    vDebtNoteDoc            bigint;
    vDownPaymentDoc         bigint;
    vFakturPajakKeluaran    bigint;
    vNotaReturPajakKeluaran bigint;
    vAdvanceInvArDoc        bigint;

    vDocTypeId              bigint;
    vJournalTrxId           bigint;

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;

BEGIN

    vCreditNoteDoc := 251;
    vDebtNoteDoc := 241;
    vDownPaymentDoc := 242;
    vAdvanceInvArDoc := 243;
    vFakturPajakKeluaran := 281;
    vNotaReturPajakKeluaran := 282;

    vEmptyId := -99;
    vEmptyValue := ' ';
    vEmptyString := '';
    vStatusDraft := 'D';
    vSignDebt := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vSystemCOA := 'SYSTEM';
    vTaxCOA := 'TAX';
    vFlgNo := 'N';

    SELECT A.doc_type_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
    FROM fi_invoice_ar A
    WHERE A.invoice_ar_id = pInvoiceArId INTO result;

    vDocTypeId := result.doc_type_id;
    vOuStructure := result.ou;
    vDocJournal := result.doc;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    /*
     * 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', pDatetime, pUserId)
    FROM fi_invoice_ar A
    WHERE A.invoice_ar_id = pInvoiceArId;

    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.invoice_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, pDatetime, pUserId, pDatetime, pUserId
    FROM fi_invoice_ar A
    WHERE A.invoice_ar_id = pInvoiceArId;

    IF vDocTypeId = vCreditNoteDoc THEN
        -- a1. Nota Kredit AR
        --      D   m_activity_gl                   ACTIVITY    AR_COST_DEBIT   (amount > 0)
        --      D   PajakPertambahanNilai - m_tax   TAX         VAT_OUT         gov tax amount
        --      K   m_activity_gl                   ACTIVITY    AR_COST_CREDIT  (amount < 0)
        --      D   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_CREDIT
        --      K   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_DEBIT, VAT_OUT

        --      D   m_activity_gl                   ACTIVITY    AR_COST_DEBIT   (amount > 0)
        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.invoice_ar_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
-- NK, 30 Agustus 2014 - add segment_id
                B.segment_id, vSignDebt, vActivityCOA, B.activity_gl_id,
                D.coa_id, A.curr_code, 0, vEmptyId,
                B.add_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_COST_DEBIT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
        WHERE   A.invoice_ar_id = pInvoiceArId AND
                B.add_amount > 0;

        --      K   m_activity_gl                   ACTIVITY    AR_COST_CREDIT  (amount < 0)
        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.invoice_ar_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
-- NK, 30 Agustus 2014 - add segment_id
                B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
                D.coa_id, A.curr_code, 0, vEmptyId,
                -1 * B.add_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_COST_CREDIT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
        WHERE   A.invoice_ar_id = pInvoiceArId AND
                B.add_amount < 0;

        --      D   PajakPertambahanNilai - m_tax   TAX         VAT_OUT         gov tax amount
        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.invoice_ar_tax_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignDebt, vTaxCOA, vEmptyId,
                D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
                B.gov_tax_amount, A.doc_date, vTypeRate,
                1, 1, 'VAT_OUT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_tax B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_tax D ON B.tax_id = D.tax_id
        WHERE   A.invoice_ar_id = pInvoiceArId;

        --      K   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_DEBIT, VAT_IN
        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 A.session_id, A.tenant_id, A.journal_trx_id, 1,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
            f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
            SUM(A.amount), A.journal_date, vTypeRate,
            1, 1, 'AR', vEmptyValue
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('AR_COST_DEBIT','VAT_OUT')
        GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
            A.partner_id, A.curr_code, A.journal_date;

        --      D   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_CREDIT
        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 A.session_id, A.tenant_id, A.journal_trx_id, 1,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
            f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
            SUM(A.amount), A.journal_date, vTypeRate,
            1, 1, 'AR', vEmptyValue
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('AR_COST_CREDIT')
        GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
            A.partner_id, A.curr_code, A.journal_date;
        -- END of insert tt_journal_trx_item for Nota Kredit

    ELSE IF vDocTypeId = vDownPaymentDoc THEN

        --      K   m_activity_gl                   SYSTEM  AR_ADVANCE
        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.invoice_ar_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
-- NK, 30 Agustus 2014 - add segment_id
                B.segment_id, vSignCredit, vSystemCOA, B.activity_gl_id,
                D.coa_id, A.curr_code, 0, vEmptyId,
                B.add_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_ADVANCE', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
        WHERE   A.invoice_ar_id = pInvoiceArId;

        --      D   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_ADVANCE
        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 A.session_id, A.tenant_id, A.journal_trx_id, 1,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
            f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
            SUM(A.amount), A.journal_date, vTypeRate,
            1, 1, 'AR', vEmptyValue
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('AR_ADVANCE')
        GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
            A.partner_id, A.curr_code, A.journal_date;

        -- END of insert tt_journal_trx_item for DownPayment

    ELSE IF vDocTypeId = vAdvanceInvArDoc THEN

        /*
        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, flg_invoice, flg_invoice_temp, invoice_id,
        version, create_datetime, create_user_id, update_datetime, update_user_id,
        ref_doc_no, ref_doc_date, tax_id, tax_percentage, tax_amount )
        SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id, A.doc_type_id, A.invoice_ar_id,
        A.curr_code, B.base_amount, 'N', 'N', vEmptyId,
        1, A.update_datetime, A.update_user_id, A.update_datetime, A.update_user_id,
        A.doc_no, A.doc_date, B.tax_id, B.tax_percentage, B.tax_amount
        FROM fi_invoice_ar A, fi_invoice_ar_tax B
        WHERE A.invoice_ar_id = pInvoiceArId AND A.invoice_ar_id = B.invoice_ar_id;
        */

        --      K   m_activity_gl                   SYSTEM  AR_ADVANCE
        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.invoice_ar_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
                B.segment_id, vSignCredit, vSystemCOA, B.activity_gl_id,
                D.coa_id, A.curr_code, 0, vEmptyId,
                B.add_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_ADVANCE', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
        WHERE   A.invoice_ar_id = pInvoiceArId;

        --      K   PajakPertambahanNilai - m_tax   TAX         VAT_OUT         gov tax amount
        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.invoice_ar_tax_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
                D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
                B.gov_tax_amount, A.doc_date, vTypeRate,
                1, 1, 'VAT_OUT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_tax B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_tax D ON B.tax_id = D.tax_id
        WHERE   A.invoice_ar_id = pInvoiceArId;

        --      D   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_ADVANCE
        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 A.session_id, A.tenant_id, A.journal_trx_id, 1,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
            f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
            SUM(A.amount), A.journal_date, vTypeRate,
            1, 1, 'AR', vEmptyValue
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('AR_ADVANCE', 'VAT_OUT')
        GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
            A.partner_id, A.curr_code, A.journal_date;

        -- END of insert tt_journal_trx_item for DownPayment

    ELSE
        -- b. Nota Debet
        --      K   m_activity_gl                   ACTIVITY    AR_COST_CREDIT  (amount > 0)
        --      K   PajakPertambahanNilai - m_tax   TAX         VAT_OUT         gov tax amount
        --      D   m_activity_gl                   ACTIVITY    AR_COST_DEBIT   (amount < 0)
        --      K   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_DEBIT
        --      D   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_CREDIT, VAT_OUT

        --      K   m_activity_gl                   ACTIVITY    AR_COST_CREDIT  (amount > 0)
        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.invoice_ar_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
-- NK, 30 Agustus 2014 - add segment_id
                B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
                D.coa_id, A.curr_code, 0, vEmptyId,
                B.add_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_COST_CREDIT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
        WHERE   A.invoice_ar_id = pInvoiceArId AND
                B.add_amount > 0;

        --      D   m_activity_gl                   ACTIVITY    AR_COST_DEBIT   (amount < 0)
        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.invoice_ar_cost_id,
                A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
-- NK, 30 Agustus 2014 - add segment_id
                B.segment_id, vSignDebt, vActivityCOA, B.activity_gl_id,
                D.coa_id, A.curr_code, 0, vEmptyId,
                -1 * B.add_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_COST_DEBIT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_cost B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_activity_gl D ON B.activity_gl_id = D.activity_gl_id
        WHERE   A.invoice_ar_id = pInvoiceArId AND
                B.add_amount < 0;

        --      K   PajakPertambahanNilai - m_tax   TAX         VAT_OUT         gov tax amount
        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.invoice_ar_tax_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
                D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
                B.gov_tax_amount, A.doc_date, vTypeRate,
                1, 1, 'VAT_OUT', B.remark
        FROM    fi_invoice_ar A
        INNER JOIN  fi_invoice_ar_tax B ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN m_tax D ON B.tax_id = D.tax_id
        WHERE   A.invoice_ar_id = pInvoiceArId;

        --      D   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_CREDIT, VAT_OUT
        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 A.session_id, A.tenant_id, A.journal_trx_id, 1,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebt, vSystemCOA, vEmptyId,
            f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
            SUM(A.amount), A.journal_date, vTypeRate,
            1, 1, 'AR', vEmptyValue
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('AR_COST_CREDIT','VAT_OUT')
        GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
            A.partner_id, A.curr_code, A.journal_date;

        --      K   PiutangDagang - m_type_partner  SYSTEM      AR              jumlah nilai dari AR_COST_DEBIT
        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 A.session_id, A.tenant_id, A.journal_trx_id, 1,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
            f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.curr_code, 0, vEmptyId,
            SUM(A.amount), A.journal_date, vTypeRate,
            1, 1, 'AR', vEmptyValue
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            A.journal_desc IN ('AR_COST_DEBIT')
        GROUP BY A.session_id, A.tenant_id, A.journal_trx_id,
            A.partner_id, A.curr_code, A.journal_date;
        -- END of insert tt_journal_trx_item for Nota Debet
    END IF;
    END IF;
    END IF;

    -- d. Summary ke gl_journal_trx_item dan gl_journal_trx_mapping

    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, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        journal_desc IN ('AR_COST_CREDIT','AR_COST_DEBIT', 'VAT_OUT', 'AR_ADVANCE');

    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, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        journal_desc IN ('AR');

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /