pu_create_doc_buy_po_internal_unsettled

--Modified by Adrian, Jun 19 2017
--Menambahkan group by pada saat update product balance stock

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

    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vStatusFinal            character varying(1);
    vWorkflowStatus         character varying(10);
    vEmptyValue             character varying(1);
    vFlgAcceptTax           character varying(1);
    vPoDocType              bigint;
    vRgDocType              bigint;
    vPurchInvDocType        bigint;
    vPoId                   bigint;
    vRgId                   bigint;
    vPurchInvId             bigint;
    vInvoiceApBalanceId     bigint;
    vProductStatus          character varying(50);
    vFakturPajakMasukan     bigint;
    vOuBuyStructure         OU_BU_STRUCTURE%ROWTYPE;
    vDocJournalRg           DOC_JOURNAL%ROWTYPE;
    vDocJournalPurchInv     DOC_JOURNAL%ROWTYPE;
    vJournalTrxId           bigint;
    result                  RECORD;
    vItemTaxAmount          numeric;
    vGovTaxAmount           numeric;
    vTrxCashbankBalanceId   bigint;

    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vProductCOA             character varying(10);
    vSystemCOA              character varying(10);
    vCashBankCOA            character varying(10);
    vTaxCOA                 character varying(10);

    vPoInternalDocTypeId    bigint;
    vRoundingModeNonTax     character varying(5);
    vCountItemTax           integer;

BEGIN

    vEmptyId := -99;
    vStatusRelease := 'R';
    vStatusFinal := 'F';
    vStatusDraft := 'D';
    vWorkflowStatus := 'APPROVED';
    vEmptyValue := ' ';
    vPoDocType := 101;
    vRgDocType := 111;
    vPurchInvDocType := 131;
    vFakturPajakMasukan := 221;
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vCashBankCOA := 'CASHBANK';
    vTaxCOA := 'TAX';
    vCountItemTax := 0;

    vPoInternalDocTypeId := 151;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    /*
     * WTC, 17 Jan 2015
     * diganti dng membaca m_product_status
     */
    --vProductStatus := 'GOOD';
    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = 'Y';



    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
    DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    SELECT NEXTVAL('pu_po_seq') INTO vPoId;
    SELECT NEXTVAL('pu_receive_goods_seq') INTO vRgId;
    SELECT NEXTVAL('pu_invoice_seq') INTO vPurchInvId;

    SELECT f_get_ou_bu_structure(A.ou_buy_id) AS ou_buy, A.flg_accept_tax,
        f_get_document_journal(vRgDocType) AS doc_rg,
        f_get_document_journal(vPurchInvDocType) AS doc_inv
    FROM pu_po_internal A
    WHERE A.po_internal_id = pPoInternalId INTO result;

    vFlgAcceptTax := result.flg_accept_tax;
    vOuBuyStructure := result.ou_buy;
    vDocJournalRg := result.doc_rg;
    vDocJournalPurchInv := result.doc_inv;


    UPDATE pu_po_internal_doc_no SET ref_id = vPoId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vPoDocType;
    UPDATE pu_po_internal_doc_no SET ref_id = vRgId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vRgDocType;
    UPDATE pu_po_internal_doc_no SET ref_id = vPurchInvId WHERE po_internal_id = pPoInternalId AND ref_doc_type_id = vPurchInvDocType;

    /*
     * menghitung total nilai tax berdasarkan nilai item
     * hal ini diperlukan untuk mendapatkan nilai selisih gov tax amount thd total tax item amount,
     * dimana nilai akan diadjust ke nilai item barang, untuk kondisi po internal yang tidak accept tax (FlgAcceptTax = N )
     */
    SELECT COUNT(*) INTO vCountItemTax
    FROM pu_po_internal_item A
    WHERE A.po_internal_id = pPoInternalId AND
        A.tax_id <> vEmptyId;

    IF vCountItemTax > 0 THEN
        SELECT SUM(A.tax_amount)
            INTO vItemTaxAmount
        FROM pu_po_internal_item A
        WHERE A.po_internal_id = pPoInternalId AND
            A.tax_id <> vEmptyId;

        SELECT A.gov_tax_amount INTO vGovTaxAmount
        FROM pu_po_internal_tax A
        WHERE A.po_internal_id = pPoInternalId;

    ELSE
        vItemTaxAmount := 0;
        vGovTaxAmount := 0;
    END IF;
    /*
     * buat data header PO,
     * status = Release
     */
    INSERT INTO pu_po
    (po_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
    ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
    partner_id, purchaser_id, warehouse_id, flg_delivery, curr_code,
    add_discount_percentage, add_discount_amount, top_code, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vPoId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date, A.ou_buy_id,
        A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark,
        A.partner_sell_id, A.purchaser_id, A.warehouse_buy_id, A.flg_delivery, A.curr_code,
        0, 0, 'COD', vStatusRelease, vWorkflowStatus,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_internal A,  pu_po_internal_doc_no B
    WHERE A.po_internal_id = pPoInternalId AND
        A.po_internal_id = B.po_internal_id AND
        B.ref_doc_type_id = vPoDocType;

    /*
     * buat data item PO
     * harus perhatikan apakah nilai pajak dari penjual diterima oleh pembeli
     * arti diterima adalah , harga product tidak akan termasuk nilai pajak
     * arti tidak diterima adalah, harga product akan termasuk nilai pajak
     */
    IF vFlgAcceptTax = 'Y' THEN
        INSERT INTO pu_po_item
        (tenant_id, po_id, line_no, ref_doc_type_id, ref_id,
        warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
        flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
        qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
        discount_amount, gross_item_amount, nett_item_amount, tax_amount, activity_gl_id,
        product_coa_id, ou_rc_id, eta, tolerance_rcv_qty, remark,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, vPoId, B.line_no, A.doc_type_id, B.po_internal_item_id,
            A.warehouse_buy_id, B.product_id, 'Y', B.curr_code, B.gross_price_po,
            B.flg_tax_amount, B.tax_id, B.tax_percentage, B.tax_price, B.nett_price_po,
            B.qty_int, B.base_uom_id, B.qty_int, B.base_uom_id, B.discount_percentage,
            B.discount_amount, B.gross_item_amount, B.nett_item_amount, B.tax_amount, vEmptyId,
            f_get_product_coa_group_product(A.tenant_id, B.product_id), vEmptyId, A.doc_date, 0, B.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_po_internal A, pu_po_internal_item B
        WHERE A.po_internal_id = pPoInternalId AND
            A.po_internal_id = B.po_internal_id;

        INSERT INTO pu_po_tax
        (tenant_id, po_id, tax_id, flg_amount,
        tax_percentage, base_amount, tax_amount, remark,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, vPoId, A.tax_id, A.flg_amount,
            A.tax_percentage, A.base_amount, A.tax_amount, A.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_po_internal_tax A
        WHERE A.po_internal_id = pPoInternalId;

    ELSE

        /*
         * karena flg accept tax = N, tax id, tax percentage, tax amount dianggap tidak ada
         * jika flg tax amount = Y,
         *  maka nilai akan ambil apa adanya dari pu_po_internal_item
         * jika flg tax amount = N,
         *  maka nilai gross price = nett price, gross amount = nett amount,dimana nilai harus sudah ditambah tax percentage
         *
         */
        INSERT INTO pu_po_item
        (tenant_id, po_id, line_no, ref_doc_type_id, ref_id,
        warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
        flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
        qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
        discount_amount, gross_item_amount, nett_item_amount, tax_amount, activity_gl_id,
        product_coa_id, ou_rc_id, eta, tolerance_rcv_qty, remark,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, vPoId, B.line_no, A.doc_type_id, B.po_internal_item_id,
            A.warehouse_buy_id, B.product_id, 'Y', B.curr_code,
            CASE WHEN B.flg_tax_amount = 'Y' THEN (B.gross_price_po - B.discount_amount)
                ELSE
                    CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
                    ELSE ROUND((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
                END,
            'N', vEmptyId, 0, 0,
            CASE WHEN B.flg_tax_amount = 'Y' THEN (B.gross_price_po - B.discount_amount)
                ELSE
                    CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
                    ELSE ROUND((B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
                END,
            B.qty_int, B.base_uom_id, B.qty_int, B.base_uom_id, 0,
            0,
            CASE WHEN B.flg_tax_amount = 'Y' THEN B.gross_item_amount - (B.qty_int * B.discount_amount)
                ELSE
                    CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
                    ELSE ROUND(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
                END,
            CASE WHEN B.flg_tax_amount = 'Y' THEN B.gross_item_amount - (B.qty_int * B.discount_amount)
                ELSE
                    CASE WHEN vRoundingModeNonTax = 'RD' THEN TRUNC(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code))
                    ELSE ROUND(B.qty_int * (B.gross_price_po - B.discount_amount) * (100 + B.tax_percentage) / 100, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, B.curr_code)) END
                END,
            0, vEmptyId,
            f_get_product_coa_group_product(A.tenant_id, B.product_id), vEmptyId, A.doc_date, 0, B.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_po_internal A, pu_po_internal_item B
        WHERE A.po_internal_id = pPoInternalId AND
            A.po_internal_id = B.po_internal_id;

        /*
         * selisih nilai tax amount di detail item dan gov tax amount akan dialokasi kan ke po item yang paling akhir
         */
        UPDATE pu_po_item SET nett_item_amount = nett_item_amount + (vGovTaxAmount - vItemTaxAmount)
        WHERE po_id = vPoId AND
            po_item_id IN (SELECT MAX(po_item_id) FROM pu_po_item WHERE po_id = vPoId);

    END IF;

    /*
     * buat saldo po item dengan status sudah Final
     * karena po internal langsung dianggap terima barang semua nya.
     */
    INSERT INTO pu_po_balance_item
    (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
     qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
     tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
            A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
            A.tolerance_rcv_qty, vStatusFinal,
            0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_item A, pu_po B
    WHERE A.po_id = vPoId AND
        A.po_id = B.po_id;

    /*
     * buat log saldo po item untuk transaksi po item sendiri
     */
    INSERT INTO pu_log_po_balance_item
    (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
     qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
        A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_item A
    WHERE A.po_id = vPoId;

    /*
     * buat data header receive goods sesuai dengan po id di atas
     */
    INSERT INTO pu_receive_goods
    (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
    ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
    remark, partner_id, warehouse_id, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vRgId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
        A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, vPoDocType, vPoId,
        A.remark, A.partner_sell_id, A.warehouse_buy_id, vStatusRelease, vWorkflowStatus,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_internal A,  pu_po_internal_doc_no B
    WHERE A.po_internal_id = pPoInternalId AND
        A.po_internal_id = B.po_internal_id AND
        B.ref_doc_type_id = vRgDocType;

    /*
     * buat data item receive goods
     */
    INSERT INTO pu_receive_goods_item
    (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
    product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
    remark, "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, vRgId, A.line_no, vPoDocType, A.po_item_id,
        A.product_id, A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id,
        A.remark, 0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_item A
    WHERE A.po_id = vPoId;

    /*
     * buat data product receive goods
     * item yang memiliki product balance id
     */
    INSERT INTO pu_receive_goods_product
    (tenant_id, receive_goods_item_id, line_no, product_id, serial_number,
    product_expired_date, product_year_made, lot_number, qty_rcv_po, po_uom_id,
    qty_rcv_int, base_uom_id, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, C.receive_goods_item_id, A.line_no, A.product_id, D.serial_number,
        D.product_expired_date, D.product_year_made, D.lot_number, A.qty_int, A.base_uom_Id,
        A.qty_int, A.base_uom_id, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_internal_product A, pu_po_item B, pu_receive_goods_item C, in_product_balance D
    WHERE A.po_internal_item_id = B.ref_id AND
        B.po_id = vPoId AND
        B.po_item_id = C.ref_id AND
        A.product_balance_id = D.product_balance_id;

    /*
     * buat log saldo po item untuk item receive goods
     */
    INSERT INTO pu_log_po_balance_item
    (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
     qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ref_id, B.ref_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
        B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_receive_goods A, pu_receive_goods_item B
    WHERE A.receive_goods_id = vRgId AND
          A.receive_goods_id = B.receive_goods_id;

    /*
     * buat saldo po invoice
     * dengan status sudah invoice = Y
     * nett item amount langsung digunakan, karena receive goods tidak partial, qty terima sesuai dengan qty order
     */
    INSERT INTO pu_po_balance_invoice
    (tenant_id, ou_id, partner_id, po_id,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
    curr_code, price_po, item_amount, flg_invoice, invoice_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
        A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
        C.curr_code, C.nett_price_po,
        C.nett_item_amount,
        'Y', vPurchInvId,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
    WHERE A.receive_goods_id = vRgId AND
          A.receive_goods_id = B.receive_goods_id AND
          B.ref_id = C.po_item_id;

    INSERT INTO pu_po_balance_invoice_tax
    (tenant_id, ou_id, partner_id, po_id,
    ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
    tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
        A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
        C.tax_percentage, C.curr_code,
        f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, C.curr_code), vRoundingModeNonTax),
        f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
        'Y', vPurchInvId,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
    WHERE A.receive_goods_id = vRgId AND
          A.receive_goods_id = B.receive_goods_id AND
          B.ref_id = C.po_item_id AND
          C.tax_id = D.tax_id;

    /*
     * tampung data item receive goods, untuk dapat digunakan dan disimpan ke product_balance, product_price_balance
     */
    INSERT INTO tt_pu_product_balance
    (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
    serial_number, lot_number, product_expired_date, product_year_made,
    product_price_balance_id, product_buy_date, partner_id,
    doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
    po_id, po_no, po_date, po_item_id,
    curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
    SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
        vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
        vEmptyId, A.doc_date, A.partner_id,
        A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
        E.po_id, E.doc_no, E.doc_date, D.po_item_id,
        D.curr_code, D.gross_price_po - D.discount_amount, SUM(B.qty_rcv_po), D.po_uom_id, SUM(B.qty_rcv_int), D.base_uom_id, D.flg_stock
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item D, pu_po E
    WHERE A.receive_goods_id = vRgId AND
        A.receive_goods_id = B.receive_goods_id AND
        NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
                    WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
        B.ref_id = D.po_item_id AND
        D.po_id = E.po_id
    GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
        A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no,
        E.po_id, E.doc_no, E.doc_date, D.po_item_id,
        D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;


    INSERT INTO tt_pu_product_balance
    (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
    serial_number, lot_number, product_expired_date, product_year_made,
    product_price_balance_id, product_buy_date, partner_id,
    doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
    po_id, po_no, po_date, po_item_id,
    curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
    SELECT pSessionId, A.warehouse_id, F.product_balance_id, A.tenant_id, A.ou_id, B.product_id,
        C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
        vEmptyId, A.doc_date, A.partner_id,
        A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date,
        E.po_id, E.doc_no, E.doc_date, D.po_item_id,
        D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, SUM(C.qty_rcv_int), D.base_uom_id, D.flg_stock
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product C, pu_po_item D, pu_po E, pu_po_internal_product F
    WHERE A.receive_goods_id = vRgId AND
        A.receive_goods_id = B.receive_goods_id AND
        B.receive_goods_item_id = C.receive_goods_item_id AND
        B.ref_id = D.po_item_id AND
        D.po_id = E.po_id AND
        F.po_internal_item_id = D.ref_id AND
        D.po_item_id = B.ref_id AND
        C.line_no = F.line_no
    GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
        C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
        A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_item_id, A.doc_no,
        E.po_id, E.doc_no, E.doc_date, D.po_item_id,
        D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock, F.product_balance_id;

    INSERT INTO in_product_price_balance
    (tenant_id, ou_id, product_id, product_balance_id,
    product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
    curr_code, amount, qty, uom_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
        A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
        A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, A.curr_code), vRoundingModeNonTax)),
        SUM(A.qty_int_rcv), A.base_uom_id,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_product_balance A, pu_po_item B
    WHERE A.session_id = pSessionId AND
        A.po_item_id = B.po_item_id
    GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
        A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id, B.nett_item_amount;

    UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
    FROM in_product_price_balance A
    WHERE tt_pu_product_balance.session_id = pSessionId AND
        tt_pu_product_balance.tenant_id = A.tenant_id AND
        tt_pu_product_balance.ou_id = A.ou_id AND
        tt_pu_product_balance.product_id = A.product_id AND
        tt_pu_product_balance.product_balance_id = A.product_balance_id AND
        tt_pu_product_balance.partner_id = A.partner_id AND
        tt_pu_product_balance.doc_type_id = A.doc_type_id AND
        tt_pu_product_balance.ref_item_id = A.ref_id AND
        tt_pu_product_balance.doc_no = A.doc_no AND
        tt_pu_product_balance.doc_date = A.doc_date;

    /*
     * update product_balance_stock, yang sudah ada di in_produc_balance_stock
     */
    --Modified by Adrian, Jun 19, 2017
    --Mengubah update product balance stock menjadi dari hasil group by
    WITH summed_tt_pu_product_balance AS (
        SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
            SUM(A.qty_int_rcv) AS qty_int_rcv
        FROM tt_pu_product_balance A
        WHERE A.session_id = pSessionId
            AND A.flg_stock = 'Y'
        GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id
    )
    UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv, update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
    FROM summed_tt_pu_product_balance A
    WHERE --A.session_id = pSessionId AND
        in_product_balance_stock.product_id = A.product_id AND
        in_product_balance_stock.tenant_id = A.tenant_id AND
        in_product_balance_stock.warehouse_id = A.warehouse_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = vProductStatus;
        --AND A.flg_stock = 'Y';

    /*
     * insert data in_product_balance_stock
     */
    INSERT INTO in_product_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
        A.base_uom_id, SUM(A.qty_int_rcv),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y' AND
        NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
                    WHERE A.tenant_id = B.tenant_id AND
                        A.warehouse_id = B.warehouse_id AND
                        A.product_id = B.product_id AND
                        A.product_balance_id = B.product_balance_id AND
                        B.product_status = vProductStatus)
    GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;

    /*
     * insert data product_price_balance_stock
     */
    INSERT INTO in_product_price_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id,
    product_price_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
        A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;

    /*
     * buat data log product balance stock
     */
    INSERT INTO in_log_product_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_product_balance A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y'
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;

    /*
     * buat data log product price balance stock
     */
    INSERT INTO in_log_product_price_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_product_balance A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y'
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;

    /*
     * add data balance receive goods item yang akan digunakan di inventory,
     * saat akan membuat claim note
     */
    INSERT INTO in_balance_receive_goods_item
    (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
      po_id, po_no, po_date, po_item_id,
      qty_rcv, qty_return, po_uom_id, qty_int_rcv,
      qty_int_return, base_uom_id, status_item,
      "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
            A.po_id, A.po_no, A.po_date, A.po_item_id,
            SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
            0, A.base_uom_id, vStatusRelease,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId
    GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
            A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;

    /*
     * create monthly price product
     */
    INSERT INTO tt_pu_monthly_price_product
    (session_id, tenant_id, ou_id, year_month_date,
    product_id, curr_code, amount, qty, base_uom_id,
     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
     flg_tax_amount, tax_id, tax_percentage)
    SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
        A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vPoInternalDocTypeId, A.curr_code), vRoundingModeNonTax)),
        SUM(A.qty_int_rcv), A.base_uom_id,
        A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
        B.flg_tax_amount, B.tax_id, B.tax_percentage
    FROM tt_pu_product_balance A, pu_po_item B, dt_date C
    WHERE A.session_id = pSessionId AND
        A.po_item_id = B.po_item_id AND
        A.doc_date = C.string_date
    GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
        A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date,
        B.flg_tax_amount, B.tax_id, B.tax_percentage;

    UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
                                        qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
                                        ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
                                        source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
                                        tax_id = A.tax_id, tax_percentage = A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId AND
        pu_monthly_price_product.tenant_id = A.tenant_id AND
        pu_monthly_price_product.ou_id = A.ou_id AND
        pu_monthly_price_product.year_month_date = A.year_month_date AND
        pu_monthly_price_product.product_id = A.product_id;

    INSERT INTO pu_monthly_price_product
    (tenant_id, ou_id, year_month_date, product_id,
    curr_code, amount, qty, base_uom_id, source_price,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    flg_tax_amount, tax_id, tax_percentage)
    SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
            A.curr_code, A.amount, A.qty, A.base_uom_id, A.source_price,
            A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
            0, pDatetime, pUserId, pDatetime, pUserId,
            A.flg_tax_amount, A.tax_id, A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId AND
        NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
                    WHERE A.tenant_id = B.tenant_id AND
                        A.ou_id = B.ou_id AND
                        A.product_id = B.product_id AND
                        A.year_month_date = B.year_month_date);

    INSERT INTO pu_log_monthly_price_product
    (tenant_id, ou_id, year_month_date, product_id,
    curr_code, amount, qty, base_uom_id, source_price,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    flg_tax_amount, tax_id, tax_percentage)
    SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
        A.curr_code, A.amount, A.qty, A.base_uom_id, A.source_price,
        A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
        0, pDatetime, pUserId, pDatetime, pUserId,
        A.flg_tax_amount, A.tax_id, A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId;

    IF vFlgAcceptTax = 'Y' THEN
        INSERT INTO pu_invoice
        (invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
        ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
        remark, partner_id, ou_legal_id, due_date, purchaser_id,
        curr_code, gross_amount, advance_amount, tax_amount, add_amount,
        total_amount, status_doc, workflow_status,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT vPurchInvId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
            A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, vPoDocType, vPoId,
            A.remark, A.partner_sell_id, vEmptyId, A.doc_date, A.purchaser_id,
            A.curr_code, SUM(C.item_amount) + SUM(D.gov_tax_amount), 0, SUM(D.gov_tax_amount), 0,
            SUM(C.item_amount), vStatusRelease, vWorkflowStatus,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_po_internal A,  pu_po_internal_doc_no B, pu_po_balance_invoice C, pu_po_internal_tax D
        WHERE A.po_internal_id = pPoInternalId AND
            A.po_internal_id = B.po_internal_id AND
            B.ref_doc_type_id = vPurchInvDocType AND
            A.po_internal_id = D.po_internal_id AND
            C.invoice_id = vPurchInvId
        GROUP BY A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date, A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, A.remark,
                A.partner_sell_id, A.purchaser_id, A.curr_code;
    ELSE
        INSERT INTO pu_invoice
        (invoice_id, tenant_id, doc_type_id, doc_no, doc_date,
        ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
        remark, partner_id, ou_legal_id, due_date, purchaser_id,
        curr_code, gross_amount, advance_amount, tax_amount, add_amount,
        total_amount, status_doc, workflow_status,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT vPurchInvId, A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date,
            A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, vPoDocType, vPoId,
            A.remark, A.partner_sell_id, vEmptyId, A.doc_date, A.purchaser_id,
            A.curr_code, SUM(C.item_amount), 0, 0, 0,
            SUM(C.item_amount), vStatusRelease, vWorkflowStatus,
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_po_internal A,  pu_po_internal_doc_no B, pu_po_balance_invoice C
        WHERE A.po_internal_id = pPoInternalId AND
            A.po_internal_id = B.po_internal_id AND
            B.ref_doc_type_id = vPurchInvDocType AND
            C.invoice_id = vPurchInvId
        GROUP BY A.tenant_id, B.ref_doc_type_id, B.ref_doc_no, A.doc_date, A.ou_buy_id, A.ext_doc_no, A.ext_doc_date, A.remark,
                A.partner_sell_id, A.purchaser_id, A.curr_code;

    END IF;

    INSERT INTO pu_invoice_item
    (tenant_id, invoice_id, line_no, ref_doc_type_id, ref_id,
    ref_item_id, ref_item_amount, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.invoice_id, ROW_NUMBER() OVER ( PARTITION BY A.invoice_id), A.ref_doc_type_id, A.ref_id,
        A.ref_item_id, A.item_amount, vEmptyValue,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_balance_invoice A
    WHERE A.invoice_id = vPurchInvId;

    IF vFlgAcceptTax = 'Y' THEN
        INSERT INTO pu_invoice_tax
        (tenant_id, invoice_id, tax_id, flg_amount, tax_percentage,
        base_amount, tax_amount, tax_no, tax_date, tax_curr_code, gov_tax_amount,
        remark, "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT A.tenant_id, vPurchInvId, A.tax_id, A.flg_amount, A.tax_percentage,
            A.base_amount, A.tax_amount, A.tax_no, A.tax_date, A.tax_curr_code, A.gov_tax_amount,
            A.remark, 0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_po_internal_tax A
        WHERE A.po_internal_id = pPoInternalId;

        -- Modified by David, 2 Januari 2015 add next val for using for insert into vat in
        SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;

        INSERT INTO fi_invoice_ap_balance
        (invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_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 vInvoiceApBalanceId, 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, 'N',
                0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_invoice A
        WHERE A.invoice_id = vPurchInvId;

        INSERT INTO fi_invoice_tax_ap_balance
        (tenant_id, ou_id, doc_type_id, invoice_ap_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)
        SELECT A.tenant_id, A.ou_id, vFakturPajakMasukan, C.invoice_ap_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, 'N',
            0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_invoice A, pu_invoice_tax B, fi_invoice_ap_balance C
        WHERE A.invoice_id = vPurchInvId AND
            A.invoice_id = B.invoice_id AND
            A.tenant_id = C.tenant_id AND
            A.ou_id = C.ou_id AND
            A.partner_id = C.partner_id AND
            A.doc_type_id = C.doc_type_id AND
            A.invoice_id = C.invoice_ap_id;

        /**
         * David, 2 Januari 2015
         * Call function for insert tax data into fi_vat_in_reporting for tax
         * @see http://jleaf.org:8181/browse/ERPDB-235
         */
        PERFORM fi_insert_vat_in_for_reporting(pTenantId, pSessionId, vInvoiceApBalanceId, pUserId, pDatetime);


     ELSE
        INSERT INTO fi_invoice_ap_balance
        ( tenant_id, ou_id, doc_type_id, invoice_ap_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 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, 'N',
                0, pDatetime, pUserId, pDatetime, pUserId
        FROM pu_invoice A
        WHERE A.invoice_id = vPurchInvId;

    END IF;

    /*
     * membuat data transaksi jurnal Receive Goods :
     * 1. buat admin
     * 2. buat temlate jurnal
     */

    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuBuyStructure).ou_bu_id, A.ou_id, (vDocJournalRg).journal_type, (vDocJournalRg).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
    FROM pu_receive_goods A
    WHERE A.receive_goods_id = vRgId;

    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, (vDocJournalRg).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
        (vOuBuyStructure).ou_bu_id, (vOuBuyStructure).ou_branch_id, (vOuBuyStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_receive_goods A, pu_po B
    WHERE A.receive_goods_id = vRgId AND
        A.ref_doc_type_id = B.doc_type_id AND
        A.ref_id = B.po_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.receive_goods_item_id,
        A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
        vEmptyId, vSignDebit, vProductCOA, C.activity_gl_id,
        C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
        C.nett_item_amount,
        A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
    WHERE A.receive_goods_id = vRgId AND
        A.receive_goods_id = B.receive_goods_id AND
        B.ref_id = C.po_item_id AND
        C.flg_stock = 'Y';

    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;

    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 ),
        vEmptyId, vEmptyId,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
        SUM(A.amount), A.journal_date, A.type_rate,
        1, 1, 'ACCR_AP', vEmptyValue,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    /*
     * membuat data transaksi jurnal Purch Invoice:
     * 1. buat admin
     * 2. buat temlate jurnal
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuBuyStructure).ou_bu_id, A.ou_id, (vDocJournalPurchInv).journal_type, (vDocJournalPurchInv).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
    FROM pu_invoice A
    WHERE A.invoice_id = vPurchInvId;

    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, (vDocJournalPurchInv).journal_type, A.doc_type_id, A.invoice_id, A.doc_no, A.doc_date,
        (vOuBuyStructure).ou_bu_id, (vOuBuyStructure).ou_branch_id, (vOuBuyStructure).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 pu_invoice A
    WHERE A.invoice_id = vPurchInvId;

    /*
     * journal detail item receive goods
     * tanggal rate menggunakan tanggal receive goods
     *
     */
    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,
        C.doc_type_id, C.receive_goods_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
        B.ref_item_amount, C.doc_date, vTypeRate,
        1, 1, 'ACCR_AP', B.remark
    FROM pu_invoice A, pu_invoice_item B, pu_receive_goods C
    WHERE A.invoice_id = vPurchInvId AND
        A.invoice_id = B.invoice_id AND
        B.ref_id = C.receive_goods_id;

    /*
     * journal detail tax purch 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, vSignDebit, vTaxCOA, vEmptyId,
        C.receive_coa_id, C.tax_curr_code, 0, vEmptyId,
        B.gov_tax_amount, A.doc_date, vTypeRate,
        1, 1, 'VAT_IN', B.remark
    FROM pu_invoice A, pu_invoice_tax B, m_tax C
    WHERE A.invoice_id = vPurchInvId AND
        A.invoice_id = B.invoice_id AND
        B.tax_id = C.tax_id;
    /*
     * buat juournal offside untuk yg credit ap
     * dengan grouping data journal yang lain melakukan debit 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,
        B.doc_type_id, B.doc_id,
        B.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_ap_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
        SUM(A.amount), B.doc_date, vTypeRate,
        1, 1, 'AP', 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 ('ACCR_AP','VAT_IN')
    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, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        journal_desc IN ('ACCR_AP','VAT_IN');

    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 ('AP');

    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
    DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

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