SUBMIT RG ERP

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

    vProcessId              bigint;
    vRgId                   bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vFlagInvoice            character varying(1);
    vEmptyId                bigint;
    vStatusRelease          character varying(1);
    vStatusDraft            character varying(1);
    vStatusFinal            character varying(1);
    vEmptyValue             character varying(1);
    vProductStatus          character varying(50);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vActivityCOA            character varying(10);
    vProductCOA             character varying(10);
    vSystemCOA              character varying(10);
    vPoId                   bigint;
    vUnfinishedItem         bigint;
    vJournalTrxId           bigint;
    vOuId                       bigint;
    vOuWarehouseIdForJournal    bigint;

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

    vReceiveGoodsDocTypeId  bigint;
    vRoundingModeNonTax     character varying(5);

    /*
     * NK, 2 Jan 2015
     * untuk kepentingan auto serial number
     */
    vDocDate                character varying(8);

    vOutletId               bigint;
    vOuWarehouseId          bigint;
    vDbVersion              character varying(10);
BEGIN

    vFlagInvoice := 'N';
    vEmptyId := -99;
    vStatusRelease := 'R';
    vStatusDraft := 'D';
    vStatusFinal := 'F';
    vEmptyValue := ' ';
    vOutletId := -99;
    vOuWarehouseId = -99;
    vDbVersion = '1.0';

    /*
     * NK, 31 Des 2014
     * diganti dng membaca m_product_status
     */
    --vProductStatus := 'GOOD';
    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = 'Y';

    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vActivityCOA := 'ACTIVITY';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vUnfinishedItem := 0;

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

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'pu_submit_receive_goods' AND
        A.process_no = pProcessNo;

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

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

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
    DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
/*
 * 1. add pu_log_po_balance_item
 * 2. add pu_po_balance_invoice
 * 3. add pu_po_balance_invoice_tax
 * 4. add in_produt_balance jika belum exists ( bisa ada serial number dan yang tidak ada serial number )
 * 5. add in_product_price_balance
 * 6. add/update in_product_balance_stock
 * 7. add in_log_product_balance_stock
 * 7b. add data log untuk outlet jika gudang nya adalah gudang outlet
 * 8. add in_log_product_price_balance_stock
 * 9. update status pu_receive_goods
 * 10.update status pu_po_balance_item
 * 10b.update status pu_po. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
 * 11.add balance receive goods item
 * 12.add gl_journal_trx
 * 13.add gl_journal_trx_item
 * 14.add gl_journal_trx_mapping
 *
 *
 */
/* pakai cara lain, NK, 14 Jan 2014
    SELECT A.ref_id, f_get_parent_ou_bu(A.tenant_id, A.ou_id), f_get_journal_type(A.doc_type_id) INTO vPoId, vParentOuId, vJournalType
    FROM pu_receive_goods A
    WHERE A.receive_goods_id = vRgId;
*/

    SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, A.doc_date
    FROM pu_receive_goods A
    WHERE A.receive_goods_id = vRgId INTO result;

    vPoId := result.ref_id;
    vOuStructure := result.ou;
    vDocJournal := result.doc;
    vDocDate := result.doc_date;
/*
    SELECT INTO vOuStructure f_get_ou_bu_structure(A.ou_id)
    FROM pu_receive_goods A
    WHERE A.receive_goods_id = vRgId;

    SELECT INTO vDocJournal f_get_document_journal(A.doc_type_id)
    FROM pu_receive_goods A
    WHERE A.receive_goods_id = vRgId;

    SELECT A.ref_id INTO vPoId
    FROM pu_receive_goods A
    WHERE A.receive_goods_id = vRgId;
    */

    /*
     * NK, 2 Jan 2015
     * memanggil function untuk membuat auto serial number
     */
    PERFORM pu_create_sn_receive_goods(pTenantId, pSessionId, vDocDate, vRgId, vUserId, vDatetime );

    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, C.po_id, C.po_item_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, vDatetime, vUserId, vDatetime, vUserId
    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
    (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,
        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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
        vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    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(vReceiveGoodsDocTypeId, 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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
        vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    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 untuk data product yang tidak memiliki serial number
    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
        NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn 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 untuk data product yang memiliki serial number dan serial number tidak di-generate
    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,
        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
    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
    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;

/* NK, 5 Jan 2015 sudah diganti dengan function pu_create_sn_receive_goods
     -- Insert untuk data product yang memiliki serial number dan serial number di-generate
    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,
        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, 1, D.base_uom_id, D.flg_stock
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product_auto_sn C, pu_po_item D, pu_po E
    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;
*
 */
    /*
     * update product_balance_id dari in_product_balance
     */
    UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
    FROM in_product_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.product_id = A.product_id AND
        tt_pu_product_balance.serial_number = A.serial_number AND
        tt_pu_product_balance.lot_number = A.lot_number;

    /*
     * insert data ke in_product_balance yang product_balance_id masih empty
     */
    INSERT INTO in_product_balance
    (tenant_id, product_id,
    serial_number, lot_number, product_expired_date, product_year_made,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.product_id,
        A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.product_balance_id = vEmptyId
    GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;

    /*
     * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
     */
    UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
    FROM in_product_balance A
    WHERE tt_pu_product_balance.session_id = pSessionId AND
        tt_pu_product_balance.product_balance_id = vEmptyId AND
        tt_pu_product_balance.tenant_id = A.tenant_id AND
        tt_pu_product_balance.product_id = A.product_id AND
        tt_pu_product_balance.serial_number = A.serial_number AND
        tt_pu_product_balance.lot_number = A.lot_number;

    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(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
        SUM(A.qty_int_rcv), A.base_uom_id,
        0, vDatetime, vUserId, vDatetime, vUserId
    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;

    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;

    /*
     * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
     */
    INSERT INTO tt_pu_product_balance_summary_stock
            (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
            product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
    SELECT  pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
            vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
    FROM    tt_pu_product_balance A
    WHERE   A.session_id = pSessionId
    GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
             A.po_uom_id, A.base_uom_id, A.flg_stock;

    /*
     * update product_balance_stock, yang sudah ada di in_product_balance_stock
     */
    UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv, update_datetime = vDatetime, update_user_id = vUserId,
    version = version + 1
    FROM tt_pu_product_balance_summary_stock 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, vDatetime, vUserId, vDatetime, vUserId
    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, vDatetime, vUserId, vDatetime, vUserId
    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, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A, m_warehouse_ou B
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y' AND
        A.warehouse_id = B.warehouse_id
    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;

    /*
     * 7b. buat data log untuk update stok outlet jika ou ini adalah ou outlet
     */
    -- cek dulu warehouse ou nya
    SELECT B.ou_id INTO vOuWarehouseId
    FROM pu_receive_goods A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE receive_goods_id = vRgId;

    IF vOuWarehouseId IS NULL THEN
        vOuWarehouseId = vEmptyId;
    END IF;

    -- cek dulu apakah ou yang digunakan ini merupakan ou outlet
    IF EXISTS(SELECT 1 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId) THEN
        SELECT A.outlet_id INTO vOutletId
        FROM i_outlet A WHERE A.ou_id = vOuWarehouseId;
    END IF;

    IF vOutletId IS NULL THEN
        vOutletId = vEmptyId;
    END IF;

    -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
    IF vOutletId <> vEmptyId THEN
        vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');


        INSERT INTO i_trx_data_log(
            tenant_id, ou_id, doc_date, db_version, type_data,
            mode_log, data_log, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
        SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_product_balance',
               'A', '{"id":'||C.product_balance_id||',"tenantId":"'||C.tenant_id||'","productId":'||C.product_id||',"serialNumber":"'||C.serial_number||'","lotNumber":"'||C.lot_number||'","productExpiredDate":"'||C.product_expired_date||'","productYearMade":"'||C.product_year_made||'","createDateTime":"'||C.create_datetime||'","createUserId":'||C.create_user_id||',"version":'||C.version||',"updateDateTime":"'||C.update_datetime||'","updateUserId":'||C.update_user_id||'}',
               0, vDatetime, vUserId, vDatetime, vUserId
        FROM pu_receive_goods A
        INNER JOIN in_log_product_balance_stock B
            ON A.tenant_id = B.tenant_id
            AND A.doc_no = B.doc_no
            AND A.doc_date = B.doc_date
            AND A.doc_type_id = B.doc_type_id
            AND A.receive_goods_id = B.ref_id
            AND A.warehouse_id = B.warehouse_id
        INNER JOIN in_product_balance C
            ON B.product_balance_id = C.product_balance_id
        WHERE A.receive_goods_id = vRgId
        GROUP BY B.tenant_id, B.ou_id, B.doc_date, C.product_balance_id;

        INSERT INTO i_trx_data_log(
            tenant_id, ou_id, doc_date, db_version, type_data,
            mode_log, data_log, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
        SELECT B.tenant_id, B.ou_id, B.doc_date, vDbVersion, 'in_log_product_balance_stock',
               'A', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
               0, vDatetime, vUserId, vDatetime, vUserId
        FROM pu_receive_goods A
        INNER JOIN in_log_product_balance_stock B
            ON A.tenant_id = B.tenant_id
            AND A.doc_no = B.doc_no
            AND A.doc_date = B.doc_date
            AND A.doc_type_id = B.doc_type_id
            AND A.receive_goods_id = B.ref_id
            AND A.warehouse_id = B.warehouse_id
        WHERE A.receive_goods_id = vRgId;
    END IF;

    /*
     * 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, A.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, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.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, vDatetime, vUserId, vDatetime, vUserId
    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;

    UPDATE pu_receive_goods SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE receive_goods_id = vRgId;

    UPDATE pu_po_balance_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    FROM pu_receive_goods_item A
    WHERE pu_po_balance_item.po_item_id = A.ref_id AND
        pu_po_balance_item.tenant_id = A.tenant_id AND
        A.receive_goods_id = vRgId AND
        pu_po_balance_item.qty_po - pu_po_balance_item.qty_cancel + pu_po_balance_item.qty_add - pu_po_balance_item.qty_rcv > 0;

    UPDATE pu_po_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
    FROM pu_receive_goods_item A
    WHERE pu_po_balance_item.po_item_id = A.ref_id AND
        pu_po_balance_item.tenant_id = A.tenant_id AND
        A.receive_goods_id = vRgId AND
        pu_po_balance_item.qty_po - pu_po_balance_item.qty_cancel + pu_po_balance_item.qty_add - pu_po_balance_item.qty_rcv <= 0;

    SELECT COUNT(1) INTO vUnfinishedItem
    FROM pu_po_balance_item A, pu_po_item B
    WHERE A.po_item_id = B.po_item_id AND
        B.po_id = vPoId AND
        A.status_item = vStatusRelease;

    IF vUnfinishedItem = 0 THEN
        UPDATE pu_po SET status_doc = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
        WHERE po_id = vPoId;
    END IF;

    /*
     * 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(vReceiveGoodsDocTypeId, 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, SUM(A.amount), SUM(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, vDatetime, vUserId, vDatetime, vUserId,
            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)
    GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
             A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;

    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, SUM(A.amount), SUM(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, vDatetime, vUserId, vDatetime, vUserId,
        A.flg_tax_amount, A.tax_id, A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
             A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
             A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;

    /*
     * @author TKP, 9 Jun 2016
     * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
     * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
     */

    SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseIdForJournal
    FROM pu_receive_goods A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.receive_goods_id = vRgId;

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

    /*
     * 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 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, (vDocJournal).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).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, vDatetime, vUserId, vDatetime, vUserId
    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,
        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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
        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 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,
-- NK, 30 Agustus 2014 - add segment_id
        C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id,
        E.coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax),
        A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_NON_STOCK', B.remark
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_activity_gl E
    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 = 'N' AND
        C.activity_gl_id = E.activity_gl_id;


    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,
    ou_branch_id, ou_sub_bu_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,
        (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
    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, vDatetime, vUserId, vDatetime, vUserId
    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_pu_product_balance WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
    DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /