Function Submit Sales Invoice

CREATE OR REPLACE FUNCTION sl_submit_sales_invoice(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vSalesInvoiceId         bigint;
    vSalesInvoiceTempId     bigint;
    vInvoiceArBalanceId     bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vFlagInvoice            character varying(1);
    vFlagPayment            character varying(1);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vStatusFinal            character varying(1);
    vEmptyValue             character varying(1);
    vEmptyString            character varying;
    vJournalType            character varying(20);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vSystemCOA              character varying(10);
    vTaxCOA                 character varying(10);
    vParentOuId             bigint;
    vJournalTrxId           bigint;
    vOuStructureDo          OU_BU_STRUCTURE%ROWTYPE;

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;
    vFakturPajakKeluaran    bigint;
    vInvArBalanceId         bigint;
    vInvTaxArBalanceId      bigint;
    vSalesInvDate           character varying(8);
    vSlsInvTempDocTypeId    bigint;
    vDoDocTypeId            bigint;
    vRegularDiscount        bigint;
    vPromoDiscount          bigint;
    vOuId                   bigint;
    vOuWarehouseId          bigint;
    vCoaIdGIT               bigint;
    vProductCOA             character varying(10);
    vRoundingModeNonTax     character varying(5);
    vDiffAdjRegularDiscAmount       numeric;
    vDiffAdjPromoDiscAmount         numeric;
    vMaxItemAmount          numeric;
    vMaxAdjPromoItemAmount  numeric;
    vInProgress                 character varying(1);
    vYes                        character varying(1);
    vNo                         character varying(1);
    vNol                        numeric;
    vReleased                   character varying(1);
    vFlgGunggung                character varying(1);

    vJointDppPpn            character varying(1);
BEGIN

    vFlagInvoice := 'Y';
    vFlagPayment := 'N';
    vEmptyId := -99;
    vStatusRelease := 'R';
    vStatusDraft := 'D';
    vStatusFinal := 'F';
    vEmptyValue := ' ';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vSystemCOA := 'SYSTEM';
    vProductCOA := 'PRODUCT';
    vTaxCOA := 'TAX';
    vFakturPajakKeluaran := 281;
    vSlsInvTempDocTypeId := 361;
    vDoDocTypeId := 311;
    vInProgress := 'I';
    vYes := 'Y';
    vNo := 'N';
    vNol := 0;
    vReleased := 'R';
    vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
    vEmptyString := '';
    vCoaIdGIT:=f_get_system_coa_by_group_coa(pTenantId, 'PersediaanInTransit');

    /*
     * membuat data transaksi jurnal :
     * 1. buat admin
     * 2. buat temlate jurnal
     */
    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'sl_submit_sales_invoice' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vSalesInvoiceId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'salesInvoiceId';


    SELECT B.ou_id, C.ou_id INTO vOuId, vOuWarehouseId
    FROM sl_invoice_temp_intransit A
    JOIN sl_do B ON A.do_id = B.do_id
    INNER JOIN m_warehouse_ou C ON B.warehouse_id = C.warehouse_id
    WHERE A.invoice_id = vSalesInvoiceId;

    IF (vOuId <> vOuWarehouseId) THEN
        SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
        vOuStructureDo := result.ou_structure;
    ELSE
        vOuStructureDo := ROW(-99, -99, -99);
    END IF;


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

    WITH activity_gl_regular_disc AS (
        SELECT A.activity_gl_code
        FROM m_activity_gl A
        WHERE A.activity_gl_code = 'REGULAR_DISC'
    )
    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, A.activity_gl_code) AS bigint) INTO vRegularDiscount
    FROM activity_gl_regular_disc A;

    WITH activity_gl_promo_disc AS (
        SELECT A.activity_gl_code
        FROM m_activity_gl A
        WHERE A.activity_gl_code = 'PROMO_DISC'
    )
    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, A.activity_gl_code) AS bigint) INTO vPromoDiscount
    FROM activity_gl_promo_disc A;

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
/*
 * 1. update status doc sl_invoice
 * 2. update flg_invoice di tabel sl_so_balance_invoice
 * 3. update adj_regular_disc_amount di tabel sl_so_balance_invoice
 * 4. update adj_promo_disc_amount di tabel sl_so_balance_invoice
 * 5. update sl_so_balance_invoice_tax
 * 6. add fi_invoice_ar_balance
 * 7. add fi_invoice_tax_ar_balance
 * 8.add gl_journal_trx
 * 9.add gl_journal_trx_item
 * 10.add gl_journal_trx_mapping
 */
    SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, ref_inv_temp_id as salesInvTempId, A.doc_date
    FROM sl_invoice A
    WHERE A.invoice_id = vSalesInvoiceId INTO result;

    vOuStructure := result.ou;
    vDocJournal := result.doc;
    vSalesInvoiceTempId := result.salesInvTempId;
    vSalesInvDate := result.doc_date;

    UPDATE sl_invoice SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE invoice_id = vSalesInvoiceId;

    UPDATE sl_so_balance_invoice A SET flg_invoice = vFlagInvoice, update_datetime = vDatetime, update_user_id = vUserId
    FROM sl_invoice B, sl_invoice_item C
    WHERE B.invoice_id = vSalesInvoiceId AND
        B.invoice_id = C.invoice_id AND
        A.ou_id = B.ou_id AND
        A.so_id = B.ref_id AND
        A.ref_doc_type_id = vDoDocTypeId AND
        A.ref_id = C.ref_id AND
        A.ref_item_id = C.ref_item_id AND
        A.do_receipt_item_id = C.do_receipt_item_id;

    -- Update discount di sl_so_balance_invoice
    IF vRoundingModeNonTax = 'RD' THEN

        -- Update adj_regular_disc_amount
        WITH total_regular_disc_amount AS (
            SELECT D.add_amount, SUM(A.regular_disc_amount) AS sum_regular_disc_amount
            FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
            WHERE B.invoice_id = vSalesInvoiceId AND
                B.invoice_id = C.invoice_id AND
                A.ou_id = B.ou_id AND
                A.so_id = B.ref_id AND
                A.ref_doc_type_id = vDoDocTypeId AND
                A.ref_id = C.ref_id AND
                A.ref_item_id = C.ref_item_id AND
                A.do_receipt_item_id = C.do_receipt_item_id AND
                D.invoice_id = B.invoice_id AND
                D.activity_gl_id = vRegularDiscount
            GROUP BY D.add_amount
        )
        UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = TRUNC(((A.regular_disc_amount / B.sum_regular_disc_amount) * (B.add_amount * -1)) - A.regular_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
            update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
        FROM total_regular_disc_amount B
        WHERE A.invoice_id = vSalesInvoiceId AND B.sum_regular_disc_amount > 0;

        -- Update adj_promo_disc_amount
        WITH total_promo_disc_amount AS (
            SELECT D.add_amount, SUM(A.promo_disc_amount) AS sum_promo_disc_amount
            FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
            WHERE B.invoice_id = vSalesInvoiceId AND
                B.invoice_id = C.invoice_id AND
                A.ou_id = B.ou_id AND
                A.so_id = B.ref_id AND
                A.ref_doc_type_id = vDoDocTypeId AND
                A.ref_id = C.ref_id AND
                A.ref_item_id = C.ref_item_id AND
                A.do_receipt_item_id = C.do_receipt_item_id AND
                D.invoice_id = B.invoice_id AND
                D.activity_gl_id = vPromoDiscount
            GROUP BY D.add_amount
        )
        UPDATE sl_so_balance_invoice A SET adj_promo_disc_amount = TRUNC(((A.promo_disc_amount / B.sum_promo_disc_amount) * (B.add_amount * -1)) - A.promo_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
            update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
        FROM total_promo_disc_amount B
        WHERE A.invoice_id = vSalesInvoiceId AND B.sum_promo_disc_amount > 0;

    ELSE

        -- Update adj_regular_disc_amount
        WITH total_regular_disc_amount AS (
            SELECT D.add_amount, SUM(A.regular_disc_amount) AS sum_regular_disc_amount
            FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
            WHERE B.invoice_id = vSalesInvoiceId AND
                B.invoice_id = C.invoice_id AND
                A.ou_id = B.ou_id AND
                A.so_id = B.ref_id AND
                A.ref_doc_type_id = vDoDocTypeId AND
                A.ref_id = C.ref_id AND
                A.ref_item_id = C.ref_item_id AND
                A.do_receipt_item_id = C.do_receipt_item_id AND
                D.invoice_id = B.invoice_id AND
                D.activity_gl_id = vRegularDiscount
            GROUP BY D.add_amount
        )
        UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = ROUND(((A.regular_disc_amount / B.sum_regular_disc_amount) * (B.add_amount * -1)) - A.regular_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
            update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
        FROM total_regular_disc_amount B
        WHERE A.invoice_id = vSalesInvoiceId AND B.sum_regular_disc_amount > 0;

        -- Update adj_promo_disc_amount
        WITH total_promo_disc_amount AS (
            SELECT D.add_amount, SUM(A.promo_disc_amount) AS sum_promo_disc_amount
            FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
            WHERE B.invoice_id = vSalesInvoiceId AND
                B.invoice_id = C.invoice_id AND
                A.ou_id = B.ou_id AND
                A.so_id = B.ref_id AND
                A.ref_doc_type_id = vDoDocTypeId AND
                A.ref_id = C.ref_id AND
                A.ref_item_id = C.ref_item_id AND
                A.do_receipt_item_id = C.do_receipt_item_id AND
                D.invoice_id = B.invoice_id AND
                D.activity_gl_id = vPromoDiscount
            GROUP BY D.add_amount
        )
        UPDATE sl_so_balance_invoice A SET adj_promo_disc_amount = ROUND(((A.promo_disc_amount / B.sum_promo_disc_amount) * (B.add_amount * -1)) - A.promo_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
            update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
        FROM total_promo_disc_amount B
        WHERE A.invoice_id = vSalesInvoiceId AND B.sum_promo_disc_amount > 0;

    END IF;

    -- cari sisa dari hasil pembulatan adj_regular_disc_amount
    SELECT (D.add_amount * -1) - SUM(A.regular_disc_amount) - SUM(A.adj_regular_disc_amount) AS diff_adj_regular_disc_amount,
            MAX(A.item_amount) AS max_item_amount INTO vDiffAdjRegularDiscAmount, vMaxItemAmount
    FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
    WHERE B.invoice_id = vSalesInvoiceId AND
        B.invoice_id = C.invoice_id AND
        A.ou_id = B.ou_id AND
        A.so_id = B.ref_id AND
        A.ref_doc_type_id = vDoDocTypeId AND
        A.ref_id = C.ref_id AND
        A.ref_item_id = C.ref_item_id AND
        A.do_receipt_item_id = C.do_receipt_item_id AND
        D.invoice_id = B.invoice_id AND
        D.activity_gl_id = vRegularDiscount
    GROUP BY D.add_amount;

    -- cari sisa dari hasil pembulatan adj_promo_disc_amount
    SELECT (D.add_amount * -1) - SUM(A.promo_disc_amount) - SUM(A.adj_promo_disc_amount) AS diff_adj_promo_disc_amount,
            MAX(A.item_amount) AS max_item_amount INTO vDiffAdjPromoDiscAmount, vMaxAdjPromoItemAmount
    FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
    WHERE B.invoice_id = vSalesInvoiceId AND
        B.invoice_id = C.invoice_id AND
        A.ou_id = B.ou_id AND
        A.so_id = B.ref_id AND
        A.ref_doc_type_id = vDoDocTypeId AND
        A.ref_id = C.ref_id AND
        A.ref_item_id = C.ref_item_id AND
        A.do_receipt_item_id = C.do_receipt_item_id AND
        D.invoice_id = B.invoice_id AND
        D.activity_gl_id = vPromoDiscount
    GROUP BY D.add_amount;

    vDiffAdjPromoDiscAmount := COALESCE(vDiffAdjPromoDiscAmount, 0);
    vDiffAdjRegularDiscAmount := COALESCE(vDiffAdjRegularDiscAmount, 0);
    vMaxItemAmount := COALESCE(vMaxItemAmount, 0);
    vMaxAdjPromoItemAmount := COALESCE(vMaxAdjPromoItemAmount, 0);

    SELECT GREATEST(vMaxItemAmount, vMaxAdjPromoItemAmount) INTO vMaxItemAmount;

    -- tambahkan sisa pembulatan adj_regular_disc_amount dan adj_promo_disc_amount ke item yg punya invoice paling tinggi
    UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = A.adj_regular_disc_amount + vDiffAdjRegularDiscAmount,
        adj_promo_disc_amount = A.adj_promo_disc_amount + vDiffAdjPromoDiscAmount,
        update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
    FROM (SELECT so_balance_invoice_id FROM sl_so_balance_invoice WHERE invoice_id = vSalesInvoiceId AND item_amount = vMaxItemAmount LIMIT 1) B
    WHERE A.so_balance_invoice_id = B.so_balance_invoice_id;


    UPDATE sl_so_balance_invoice_tax A SET flg_invoice = vFlagInvoice, update_datetime = vDatetime, update_user_id = vUserId
    FROM sl_invoice B, sl_invoice_item C
    WHERE B.invoice_id = vSalesInvoiceId AND
        B.invoice_id = C.invoice_id AND
        A.ou_id = B.ou_id AND
        A.so_id = B.ref_id AND
        A.ref_doc_type_id = vDoDocTypeId AND
        A.ref_id = C.ref_id AND
        A.ref_item_id = C.ref_item_id AND
        A.do_receipt_item_id = C.do_receipt_item_id;

    -- Modified by fredi, 3 Dec add next val for using for insert into vat out
    SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;

    IF vJointDppPpn = 'N' THEN
        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, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
                A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
                A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
                A.curr_code, A.total_amount, A.remark, 0, vFlagPayment,
                0, vDatetime, vUserId, vDatetime, vUserId
        FROM sl_invoice A
        WHERE A.invoice_id = vSalesInvoiceId;

        -- Modified by fredi, 2 Dec add insert gov_base_amount into fi_invoice_tax_ap_balance
        -- get gov_base_tax_amount from fi_invoice_ar_tax.base_amount
        INSERT INTO fi_invoice_tax_ar_balance
        (tenant_id, ou_id, doc_type_id, invoice_ar_balance_id,
        partner_id, tax_id, tax_no, tax_date,
        curr_code, tax_amount, tax_curr_code, gov_tax_amount,
        due_date, remark, payment_amount, flg_payment,
        "version", create_datetime, create_user_id, update_datetime, update_user_id,
        gov_base_amount)
        SELECT A.tenant_id, A.ou_id, vFakturPajakKeluaran, C.invoice_ar_balance_id,
            A.partner_id, B.tax_id, B.tax_no, B.tax_date,
            A.curr_code, B.tax_amount, B.tax_curr_code, B.gov_tax_amount,
            A.due_date, B.remark, 0, vFlagPayment,
            0, vDatetime, vUserId, vDatetime, vUserId,
            f_get_amount_in_gov_tax_rate(A.tenant_id, B.base_amount - B.advance_amount, B.tax_date, A.curr_code)
        FROM sl_invoice A, sl_invoice_tax B, fi_invoice_ar_balance C
        WHERE A.invoice_id = vSalesInvoiceId AND
            A.invoice_id = B.invoice_id AND
            A.tenant_id = C.tenant_id AND
            A.doc_type_id = C.doc_type_id AND
            A.invoice_id = C.invoice_ar_id;

    ELSE
        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, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
                A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
                A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
                A.curr_code, A.total_amount + COALESCE(B.gov_tax_amount, 0), A.remark, 0, vFlagPayment,
                0, vDatetime, vUserId, vDatetime, vUserId
        FROM sl_invoice A
        LEFT OUTER JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
        WHERE A.invoice_id = vSalesInvoiceId;

    END IF;

    -- ====================================================================================== --
    /* HS, 30 Jan 2020
     * Menambahkan data invoice ar balance untuk keperluan serah terima invoice AR
     * berdasarkan data balance invoice ar dari table fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
     */
    INSERT INTO fi_invoice_ar_balance_handover(
            tenant_id, invoice_id, doc_type_id, employee_id, flg_handover,
            handover_id, handover_doc_type_id, flg_handover_back, handover_back_id, handover_back_doc_type_id,
            handover_back_status, handover_back_remark, version, create_user_id, create_datetime,
            update_user_id, update_datetime)
    SELECT  tenant_id, invoice_ar_balance_id, doc_type_id, vEmptyId, vNo,
            vEmptyId, vEmptyId, vNo, vEmptyId, vEmptyId,
            vEmptyString, vEmptyString, 0, vUserId, vDatetime,
            vUserId, vDatetime
    FROM fi_invoice_ar_balance
    WHERE invoice_ar_balance_id = vInvoiceArBalanceId;

    INSERT INTO fi_invoice_ar_balance_handover(
            tenant_id, invoice_id, doc_type_id, employee_id, flg_handover,
            handover_id, handover_doc_type_id, flg_handover_back, handover_back_id, handover_back_doc_type_id,
            handover_back_status, handover_back_remark, version, create_user_id, create_datetime,
            update_user_id, update_datetime)
    SELECT  tenant_id, invoice_tax_ar_balance_id, doc_type_id, vEmptyId, vNo,
            vEmptyId, vEmptyId, vNo, vEmptyId, vEmptyId,
            vEmptyString, vEmptyString, 0, vUserId, vDatetime,
            vUserId, vDatetime
    FROM fi_invoice_tax_ar_balance
    WHERE invoice_ar_balance_id = vInvoiceArBalanceId;

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

    IF vSalesInvoiceTempId <> -99 THEN

        SELECT invoice_ar_balance_id INTO vInvArBalanceId
        FROM fi_invoice_ar_balance
        WHERE invoice_ar_id = vSalesInvoiceTempId AND doc_type_id = vSlsInvTempDocTypeId;

        SELECT invoice_tax_ar_balance_id INTO vInvTaxArBalanceId
        FROM fi_invoice_tax_ar_balance A
        WHERE A.invoice_ar_balance_id = vInvArBalanceId;

        DELETE FROM fi_invoice_ar_balance_due_date WHERE invoice_ar_balance_id = vInvArBalanceId;

        DELETE FROM fi_invoice_tax_ar_balance_due_date WHERE invoice_tax_ar_balance_id = vInvTaxArBalanceId;

        DELETE FROM fi_invoice_ar_balance WHERE invoice_ar_balance_id = vInvArBalanceId;

        DELETE FROM fi_invoice_tax_ar_balance WHERE invoice_ar_balance_id = vInvArBalanceId;


        UPDATE sl_do_inv SET flg_invoice = 'Y',
                            update_datetime = vDatetime, update_user_id = vUserId, version = sl_do_inv.version + 1
        FROM sl_invoice_item A
        WHERE A.invoice_id = vSalesInvoiceId AND
            A.ref_id = sl_do_inv.do_id;

    END IF;

    /*
     * ========================================================================================================================

    IF vJointDppPpn = 'N' THEN
        /**
         * Fredi, 3 Dec 2014
         * Call function for insert tax data into fi_vat_out_reporting for tax
         * @see http://jleaf.org:8181/browse/ERPDB-211
         */
        PERFORM fi_insert_vat_out_for_reporting(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
    ELSE
        PERFORM fi_insert_vat_out_invoice_ar_for_reporting(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
    END IF;*/

    /*
     * NK, 14 Nov 2014
     */
    UPDATE fi_invoice_advance_ar_balance SET sales_invoice_id = vSalesInvoiceId, sales_invoice_date = vSalesInvDate,
                                            update_datetime = vDatetime, update_user_id = vUserId,
                                            version = fi_invoice_advance_ar_balance.version + 1
    FROM sl_invoice_advance A, fi_invoice_ar_balance B
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.ref_id = B.invoice_ar_balance_id AND
        B.invoice_ar_id = fi_invoice_advance_ar_balance.invoice_ar_id;

    UPDATE fi_invoice_tax_advance_ar_balance SET sales_invoice_id = vSalesInvoiceId, sales_invoice_date = vSalesInvDate,
                                            update_datetime = vDatetime, update_user_id = vUserId,
                                            version = fi_invoice_tax_advance_ar_balance.version + 1
    FROM sl_invoice_advance A, fi_invoice_ar_balance B
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.ref_id = B.invoice_ar_balance_id AND
        B.invoice_ar_id = fi_invoice_tax_advance_ar_balance.invoice_ar_id;

    /*
     * modified by TKP 23 Maret 2016
     * update nilai flg payment = y di fi_invoice_ar_balance jika amount invoice = 0
     */
    UPDATE fi_invoice_ar_balance A
    SET flg_payment = 'Y'
    WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId
        AND A.amount = 0;

    /*
     * update flg saldo so advance invoice, karena sudah digunakan oleh sales invoice
     */
    UPDATE sl_so_balance_advance_invoice SET flg_invoice = 'Y', invoice_id = vSalesInvoiceId,
                                            update_datetime = vDatetime, update_user_id = vUserId,
                                            version = sl_so_balance_advance_invoice.version + 1
    FROM sl_invoice_advance A
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.ref_id = sl_so_balance_advance_invoice.ref_id;

    /*
     * Get data flg Gunggung untuk keperluan buat efaktur
     */
    SELECT flg_gunggung INTO vFlgGunggung
    FROM sl_invoice_tax
    WHERE invoice_id = vSalesInvoiceId
    AND tenant_id = pTenantId;

    IF NOT FOUND THEN
          vFlgGunggung = vNo;
    END IF;

    /*
     * Add by Deo 7 april 2021
     * if flg gunggung = 'N' then create efaktur
     */
    IF vFlgGunggung = 'N' THEN
        IF vJointDppPpn = 'N' THEN
            /**
             * add by fredi, 16 Juni 2015
             * - insert data for vat out
             * http://jleaf.org:8112/issue/ERP-52
             */
            PERFORM fi_insert_vat_out_for_efaktur(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
        ELSE
            PERFORM fi_insert_vat_out_invoice_ar_for_efaktur(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
        END IF;
    END IF;
    /*
     * Adrian, Dec 7 2016
     * Menambahkan insert ke sl_do_item_balance_for_rrs untuk Request Return Sales
     * modified Fitra 09 Jan 2017
     *
     */
    INSERT INTO sl_do_item_balance_for_rrs
        ( tenant_id, ou_id, partner_id, product_id,
        invoice_id, invoice_type_id, invoice_no, invoice_date,
        qty_request, qty_dlv,
        so_uom_id, so_item_id,
        gross_sell_price, curr_code, flg_tax_amount, tax_id, discount_amount,
        flg_requested,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT
        A.tenant_id, B.ou_id, B.partner_id, C.product_id,
        B.invoice_id, B.doc_type_id, B.doc_no, B.doc_date,
        0, SUM(F.qty_dlv_so),
        C.so_uom_id, E.so_item_id,
        E.gross_sell_price, E.curr_code, E.flg_tax_amount, E.tax_id,
        E.gross_sell_price - (SUM(F.item_amount -
                F.regular_disc_amount -
                F.adj_regular_disc_amount -
                F.promo_disc_amount -
                F.adj_promo_disc_amount +
                COALESCE(G.tax_amount, 0)) / SUM(F.qty_dlv_so)) AS discount_amount,
        'N',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_invoice_item A
    INNER JOIN sl_invoice B ON B.invoice_id = A.invoice_id
    INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id
    INNER JOIN sl_do D ON C.do_id = D.do_id
    INNER JOIN sl_so_item E ON C.ref_id = E.so_item_id
    INNER JOIN sl_so_balance_invoice F
        ON F.tenant_id = B.tenant_id
            AND F.ou_id = B.ou_id
            AND F.partner_id = B.partner_id
            AND F.ref_doc_type_id = A.ref_doc_type_id
            AND F.ref_id = A.ref_id
            AND F.ref_item_id = A.ref_item_id
            AND F.do_receipt_item_id = A.do_receipt_item_id
    LEFT JOIN sl_so_balance_invoice_tax G
        ON F.tenant_id = G.tenant_id
            AND F.ou_id = G.ou_id
            AND F.partner_id = G.partner_id
            AND F.ref_doc_type_id = G.ref_doc_type_id
            AND F.ref_id = G.ref_id
            AND F.ref_item_id = G.ref_item_id
            AND F.do_receipt_item_id = G.do_receipt_item_id
    WHERE A.invoice_id = vSalesInvoiceId
    GROUP BY A.tenant_id, B.ou_id, B.invoice_id, B.doc_type_id, C.product_id, C.so_uom_id, E.so_item_id
    HAVING SUM(F.qty_dlv_so) <> 0;

    /*
     * 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 sl_invoice A
    WHERE A.invoice_id = vSalesInvoiceId;

    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_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 sl_invoice A
    WHERE A.invoice_id = vSalesInvoiceId;

    /*
     * journal detail item do
     * tanggal rate menggunakan tanggal invoice
     * yang digunakan partner_id bukan partner_bill_to_id
     */
    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_item_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), A.curr_code, 0, vEmptyId,
        B.ref_item_amount, A.doc_date, vTypeRate,
        1, 1, 'SALES_INCOME', B.remark
    FROM sl_invoice A, sl_invoice_item B
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.invoice_id = B.invoice_id;

    /*
     * journal detail additional cost sales 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,
        A.doc_type_id, B.invoice_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,
        C.coa_id, B.curr_code, 0, vEmptyId,
        B.add_amount, A.doc_date, vTypeRate,
        1, 1, 'SALES_COST', B.remark
    FROM sl_invoice A, sl_invoice_cost B, m_activity_gl C
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.invoice_id = B.invoice_id AND
        B.activity_gl_id = C.activity_gl_id AND
        B.add_amount <> 0;

    /*
     * journal detail tax sales 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,
        A.doc_type_id, B.invoice_tax_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
        C.create_coa_id, C.tax_curr_code, 0, vEmptyId,
        B.gov_tax_amount, A.doc_date, vTypeRate,
        1, 1, 'VAT_OUT', B.remark
    FROM sl_invoice A, sl_invoice_tax B, m_tax C
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.invoice_id = B.invoice_id AND
        B.tax_id = C.tax_id;

    /*
     * journal detail alokasi advance sales invoice
     * tanggal rate menggunakan tanggal advance 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.ref_doc_type_id, B.ref_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'UangMukaPenjualan'), B.advance_curr_code, 0, vEmptyId,
        B.advance_amount, E.doc_date, vTypeRate,
        1, 1, 'AR_ADVANCE', B.remark
    FROM sl_invoice A, sl_invoice_advance B, fi_invoice_ar_balance E
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.invoice_id = B.invoice_id AND
        B.ref_doc_type_id = E.doc_type_id AND
        B.ref_id = E.invoice_ar_balance_id;

    /*
     * buat juournal offside untuk yg debit ar
     * dengan grouping data journal yang lain melakukan credit account lain
     */
    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, B.tenant_id, B.journal_trx_id, 1,
        vEmptyId, vEmptyId,
        B.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
        f_get_ar_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
        SUM(A.amount), B.doc_date, vTypeRate,
        1, 1, 'AR', B.remark
    FROM tt_journal_trx_item A, gl_journal_trx B
    WHERE A.session_id = pSessionId AND
        A.journal_trx_id = B.journal_trx_id AND
        B.journal_trx_id = vJournalTrxId AND
        A.sign_journal = vSignCredit AND
        journal_desc IN ('SALES_INCOME','SALES_COST','VAT_OUT')
    GROUP BY A.session_id, B.tenant_id, B.journal_trx_id,
        B.partner_id, A.curr_code, B.remark;

    /*
     * buat juournal offside untuk yg credit ar
     * dengan grouping data journal yang lain melakukan debit account lain
     * contoh : 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, B.tenant_id, B.journal_trx_id, 1,
        B.doc_type_id, B.doc_id,
        B.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_ar_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
        SUM(A.amount), B.doc_date, vTypeRate,
        1, 1, 'AR', B.remark
    FROM tt_journal_trx_item A, gl_journal_trx B
    WHERE A.session_id = pSessionId AND
        A.journal_trx_id = B.journal_trx_id AND
        B.journal_trx_id = vJournalTrxId AND
        A.sign_journal = vSignDebit AND
        A.journal_desc IN ('AR_ADVANCE')
    GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
        B.partner_id, A.curr_code, B.remark;

    /*
    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 ('SALES_INCOME','SALES_COST','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, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        journal_desc IN ('AR');
    */

    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(
        SELECT A.tenant_id, 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,
            vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            journal_desc IN ('SALES_INCOME','SALES_COST','VAT_OUT','AR_ADVANCE')
        UNION ALL
        -- tambahan jounal cogs
        SELECT A.tenant_id, vJournalTrxId,
            vEmptyId, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
            f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
            0, A.doc_date, vTypeRate,
            1, 1, 'COGS', vEmptyString,
            vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
        FROM sl_invoice A
        WHERE A.invoice_id = vSalesInvoiceId
    ) A;

    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 (
        SELECT A.tenant_id, 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,
            vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
        FROM tt_journal_trx_item A
        WHERE A.session_id = pSessionId AND
            journal_desc IN ('AR')
        UNION ALL
        SELECT h.tenant_id, vJournalTrxId,
            g.ref_doc_type_id, g.ref_item_id,
            h.partner_id, C.product_id, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vProductCOA, vEmptyId,
            vCoaIdGIT, g.curr_code, SUM(g.qty_dlv_so), g.so_uom_id,
            0, h.doc_date, vTypeRate,
            1, 1, 'PRODUCT_STOCK_IN_TRANSIT', C.remark,
            vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
        FROM sl_invoice_item a
        INNER JOIN sl_invoice h ON a.tenant_id = h.tenant_id
            AND a.invoice_id = h.invoice_id
        INNER JOIN sl_do b ON a.tenant_id = b.tenant_id
            AND a.ref_id = b.do_id
        INNER JOIN sl_do_item c ON a.tenant_id = c.tenant_id AND a.ref_item_id = c.do_item_id
        INNER JOIN sl_so_balance_invoice g ON a.tenant_id = g.tenant_id
            AND g.ou_id = h.ou_id
            AND g.so_id = b.ref_id
            AND g.ref_item_id = a.ref_item_id
            AND g.ref_doc_type_id = 311
            AND g.ref_id = b.do_id
            AND a.do_receipt_item_id = g.do_receipt_item_id
        WHERE A.invoice_id = vSalesInvoiceId
        GROUP BY h.tenant_id, g.ref_doc_type_id, g.ref_item_id,
                h.partner_id, C.product_id, g.curr_code,
                g.so_uom_id, h.doc_date,C.remark
    ) A;


    UPDATE sl_invoice_temp_intransit A
    SET invoice_journal_trx_id = vJournalTrxId,
        status_process = vReleased,
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = A.version+1
    FROM sl_invoice B
    WHERE A.invoice_id = B.invoice_id
    AND B.invoice_id = vSalesInvoiceId;


    /*
     * Insert ke table log fi_vat_out_balance_efaktur_gunggung_seq
     */
    INSERT INTO fi_vat_out_balance_efaktur_gunggung(
            tenant_id, ou_id, invoice_id,
            invoice_doc_type_id, invoice_tax_id, tax_code, tax_percentage,
            doc_no, doc_date, invoice_dpp_amount,
            invoice_tax_amount, invoice_gov_tax_amount,
            flg_gunggung, flg_realization, realization_doc_type_id,
            realization_id, realization_tax_no, realization_tax_date,
            version, create_datetime,
            create_user_id, update_datetime, update_user_id)

    SELECT pTenantId, A.ou_id, vSalesInvoiceId,
            A.doc_type_id, B.invoice_tax_id, C.tax_code, B.tax_percentage,
            A.doc_no, A.doc_date, B.base_amount,
            B.tax_amount, B.gov_tax_amount,
            B.flg_gunggung, vNo, vEmptyId,
            vEmptyId, vEmptyString, vEmptyString,
            0, vDatetime,
            vUserId, vDatetime, vUserId

    FROM sl_invoice A, sl_invoice_tax B, m_tax C
    WHERE A.invoice_id = vSalesInvoiceId AND
        A.invoice_id = B.invoice_id AND
        B.tax_id = C.tax_id;

    --GROUP BY A.doc_type_id, B.invoice_tax_id, C.tax_code, B.tax_percentage,
    --      A.doc_no, A.doc_date,

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

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