Receive Goods

-- Function: memeflorist.pu_submit_receive_goods(bigint, character varying, character varying)

-- DROP FUNCTION memeflorist.pu_submit_receive_goods(bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION memeflorist.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;
        vAutonumIdDo            bigint;
        vAutonumIdSI            bigint;
        vDocNoNewDo             character varying;
        vDocNoNewSI             character varying;
        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;
        vSoId                       bigint;
        vOuWarehouseIdForJournal    bigint;
        vDocTypeSo                  bigint;
        vDocTypeDo                  bigint;
        vDocTypeForSelect           bigint;
        vRefIdForSelect             bigint;
        vZeroValue                  bigint;
        vOustandingQtySO            bigint;
        vQtySOFulfilled             bigint;
        vQtySORequest               bigint;
        vFlgDropshipSO              character varying(1);
        vYes                        character varying(1);
        vNo                         character varying(1);
        vEmptyString                character varying(1);

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

        vReceiveGoodsDocTypeId  bigint;
        vRoundingModeNonTax     character varying(5);

        vRoleId                 bigint;
        vFlgUserRole            character varying;

        vSchemeRG               character varying(20) := 'CB01';

        vMappingCbInSoBalanceItemId     bigint;
        vCtgrPartnerIdClientCorporate   bigint;
        vInvoiceArBalanceId     bigint;
        vAutonumIdAllocCbIn     bigint;
        vDocNoNewAllocCbIn              character varying(100);

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

        vOutletId               bigint;
        vOuWarehouseId          bigint;
        vDbVersion              character varying(10);
        vCtgrPartnerId          bigint;
        vPartnerBillToId            bigint;

        vStatus             character varying(1);
        cStatus             character varying(1);
        fStatus             character varying(1);
    BEGIN
        vAutonumIdAllocCbIn := -99;
        vDocNoNewAllocCbIn := '';
        vMappingCbInSoBalanceItemId := -99;
        vInvoiceArBalanceId := -99;

        vCtgrPartnerIdClientCorporate := -99;

        vFlagInvoice := 'N';
        vCtgrPartnerId := -99;
        vPartnerBillToId := -99;
        vEmptyId := -99;
        vStatusRelease := 'R';
        vStatusDraft := 'D';
        vStatusFinal := 'F';
        vEmptyValue := ' ';
        vOutletId := -99;
        vOuWarehouseId := -99;
        vDbVersion := '1.0';
        vDocTypeSo := 301;
        vDocTypeDo := 311;
        vDocTypeForSelect := -99;
        vFlgDropshipSO := 'N';
        vYes := 'Y';
        vNo := 'N';
        vRefIdForSelect := -99;
        vSoId := -99;
    --  vRgId := -99;
        vZeroValue := 0;
        vOustandingQtySO := 0;
        vEmptyString := '';

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

        SELECT A.ctgr_partner_id INTO vCtgrPartnerIdClientCorporate
        FROM m_ctgr_partner A
        WHERE ctgr_partner_code = 'CLIENTCORPORATE';

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

        vStatus := 'V';
        cStatus := 'C';
        fStatus := 'F';

        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;



            -- Get role id, flg user role awe submit RG
        SELECT role_id, flg_user_role INTO vRoleId, vFlgUserRole
        FROM awe_historydoc
        WHERE scheme = vSchemeRG
        AND doc_id = vRgId
        AND activity = 'SUBMIT';

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

        /*Periksa apakah PO sudah selesai (Qty PO = Qty Rcv, qty o/s SO = 0)*/
        SELECT COUNT(1) INTO vOustandingQtySO
        FROM pu_po_balance_item A
        INNER JOIN pu_po_item B ON A.tenant_id = B.tenant_id AND A.po_item_id = B.po_item_id
        WHERE B.tenant_id = pTenantId AND B.po_id = vPoId
        AND (a.qty_po - a.qty_rcv + a.qty_return - a.qty_cancel + a.qty_add) <> 0;

        /* Membuat automatic dokumen Delivery Order, dengan kondisi jika dokumen PO nya referensi dari SO dan SO nya dropship */
        SELECT COALESCE(C.so_id, vEmptyId) AS so_id, COALESCE(D.flag_dropship, vNo) AS flg_dropship, COALESCE(C.partner_bill_to_id, vEmptyId) AS partner_bill_to_id
        FROM pu_receive_goods A
        INNER JOIN pu_po B ON A.ref_id = B.po_id AND A.ref_doc_type_id = B.doc_type_id
        LEFT JOIN sl_so C ON C.doc_type_id = B.ref_doc_type_id AND B.ref_id = C.so_id
        INNER JOIN sl_so_ext D ON C.so_id = D.so_Id
        WHERE receive_goods_id = vRgId
        INTO vSoId, vFlgDropshipSO, vPartnerBillToId;

        IF FOUND THEN
            IF (vSoId <> vEmptyId AND vFlgDropshipSO <> vNo) THEN
                INSERT INTO sl_so_dropship_receive_goods_balance
                (tenant_id, receive_goods_item_id,
                 receive_goods_product_id, serial_number, product_expired_date,
                 lot_number, product_id, receive_goods_id,
                 po_id, so_id, so_item_id,
                 qty_rcv_int,
                 base_uom_id,
                 flg_create_do, do_id, do_item_id, do_product_id, qty_dlv_int,
                 create_datetime, create_user_id, update_datetime,
                 update_user_id, version, product_balance_id)
                SELECT A.tenant_id, B.receive_goods_item_id,
                    COALESCE(C.receive_goods_product_id, vEmptyId) AS receive_goods_product_id, COALESCE(C.serial_number, vEmptyString) AS serial_number, COALESCE(C.product_expired_date, vEmptyString) AS product_expired_date,
                    COALESCE(C.lot_number, vEmptyString) AS lot_number, B.product_id, A.receive_goods_id,
                    vPoId, vSoId, J.ref_id,
                    CASE WHEN C.receive_goods_product_id <> vEmptyId THEN C.qty_rcv_int ELSE B.qty_rcv_int END AS qty_rcv_int,
                    CASE WHEN C.receive_goods_product_id <> vEmptyId THEN C.base_uom_id ELSE B.base_uom_id END AS base_uom_id,
                    vNo, vEmptyId, vEmptyId, vEmptyId, vZeroValue,
                    vDatetime, vUserId, vDatetime,
                    vUserId, 0, COALESCE(D.product_balance_id, vEmptyId)
                FROM pu_receive_goods A
                INNER JOIN pu_receive_goods_item B ON A.tenant_id = B.tenant_id AND A.receive_goods_id = B.receive_goods_id
                LEFT JOIN pu_receive_goods_product C ON B.tenant_id = C.tenant_id AND B.receive_goods_item_id = C.receive_goods_item_id AND B.line_no = C.line_no
                LEFT JOIN in_product_balance D ON C.tenant_id = D.tenant_id AND C.product_id = D.product_id AND C.serial_number = D.serial_number AND C.lot_number = D.lot_number
                INNER JOIN pu_po I ON I.tenant_id = A.tenant_id AND I.doc_type_id = A.ref_doc_type_id AND I.po_id = A.ref_id AND I.ou_id = A.ou_id
                INNER JOIN pu_po_item J ON I.po_id = J.po_id AND B.ref_id = J.po_item_id
                WHERE A.receive_goods_id = vRgId;

                --(tenant_id, product_id, serial_number COLLATE pg_catalog."default", lot_number COLLATE pg_catalog."default");

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

                -- Ambil doc no SO baru
                SELECT A.process_parameter_value INTO vDocNoNewDo
                FROM t_process_parameter A
                WHERE A.process_message_id = vProcessId
                AND A.process_parameter_key = 'autonumDo';

                PERFORM sl_automatic_create_delivery_order(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vPoId, vRgId, vOuId, vAutonumIdDo, vDocNoNewDo, vRoleId, vFlgUserRole);

                /*Cek apakah SO referensi sudah terpenuhi semua*/
                /*
                SELECT COUNT(1) INTO vQtySOFulfilled
                FROM sl_so_balance_item A
                INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
                INNER JOIN sl_so C ON B.so_id = C.so_id
                WHERE C.so_id = vSoId AND C.tenant_id = pTenantId
                AND (A.qty_so_int - A.qty_cancel_int + A.qty_add_int - A.qty_dlv_int + A.qty_return_int) = 0;
                */

                /*
                SELECT COUNT(1) INTO vQtySOFulfilled
                FROM sl_so_balance_item A
                INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
                INNER JOIN sl_so C ON B.so_id = C.so_id
                WHERE C.so_id = vSoId AND C.tenant_id = pTenantId
                AND A.status_item IN (vStatus, cStatus, fStatus);


                SELECT COUNT(1) INTO vQtySORequest
                FROM sl_so_balance_item A
                INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
                INNER JOIN sl_so C ON B.so_id = C.so_id
                WHERE C.so_id = vSoId AND C.tenant_id = pTenantId;
                */

                SELECT ctgr_partner_id INTO vCtgrPartnerId
                FROM m_partner
                WHERE partner_id = vPartnerBillToId;

                SELECT COUNT(1) INTO vQtySOFulfilled
                FROM sl_so_balance_item A
                INNER JOIN sl_so_item B ON A.so_item_id = B.so_item_id
                INNER JOIN sl_so C ON B.so_id = C.so_id
                WHERE C.so_id = vSoId AND C.tenant_id = pTenantId
                AND A.status_item NOT IN (vStatus, cStatus, fStatus);

                --IF (vOustandingQtySO = vZeroValue AND vQtySOFulfilled = vQtySORequest) THEN
                IF (vQtySOFulfilled = vZeroValue AND vFlgDropshipSO <> vNo AND vCtgrPartnerId <> vCtgrPartnerIdClientCorporate) THEN
                    SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdSI
                    FROM t_process_parameter A
                    WHERE A.process_message_id = vProcessId
                    AND A.process_parameter_key = 'autonumIdSI';

                    -- Ambil doc no SI baru
                    SELECT A.process_parameter_value INTO vDocNoNewSI
                    FROM t_process_parameter A
                    WHERE A.process_message_id = vProcessId
                    AND A.process_parameter_key = 'autonumSI';

                            IF EXISTS (
                                SELECT 1 FROM sl_do A
                                WHERE A.tenant_id = pTenantId AND A.doc_type_id = vDocTypeDo AND A.doc_no = vDocNoNewDo
                                    AND A.doc_date = vDocDate AND A.ou_id = vOuId
                            ) THEN

                                --INSERT INTO test_123(test)
                                    --SELECT CONCAT(vSoId,' ',vAutonumIdSI,' ',vDocNoNewSI,' ',vFlgUserRole,' ',' AUTO SI');

                                PERFORM sl_automatic_create_sales_invoice(pSessionId, pTenantId, vUserId, vDatetime,
                                    vSoId, A.do_id, vAutonumIdSI, vDocNoNewSI, vRoleId, vFlgUserRole)
                                FROM sl_do A
                                WHERE A.tenant_id = pTenantId AND A.doc_type_id = vDocTypeDo AND A.doc_no = vDocNoNewDo
                                    AND A.doc_date = vDocDate AND A.ou_id = vOuId;

                                /*
                                 * Get data invoice AR Balance
                                 * Get data mapping_cbin_so_balance_item_id ada atau tidak
                                 * Generate automatic alloc cb in document
                                 */
                                    -- Get invoice AR balance document
                                    SELECT A.invoice_ar_balance_id INTO vInvoiceArBalanceId
                                    FROM fi_invoice_ar_balance A
                                    INNER JOIN sl_so B
                                    ON A.ref_doc_type_id = vDocTypeSo AND A.ref_id = B.so_id AND A.tenant_id = pTenantId
                                    WHERE B.so_id = vSoId
                                    AND A.tenant_id = pTenantId;

                                    IF NOT FOUND THEN
                                        vInvoiceArBalanceId := vEmptyId;
                                    END IF;


                                    --get mapping_cbin_so_balance_item_id
                                    SELECT B.mapping_cbin_so_balance_item_id INTO vMappingCbInSoBalanceItemId
                                    FROM sl_so A
                                    INNER JOIN cb_mapping_cbin_so_balance_item B ON A.tenant_id = B.tenant_id AND A.so_id = B.so_id
                                    WHERE A.tenant_id = pTenantId
                                    AND A.so_id = vSoId;

                                    IF NOT FOUND THEN
                                        vMappingCbInSoBalanceItemId := vEmptyId;
                                    END IF;


                                    -- Ambil doc Allocation CB in baru
                                    SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdAllocCbIn
                                    FROM t_process_parameter A
                                    WHERE A.process_message_id = vProcessId
                                    AND A.process_parameter_key = 'autonumIdAllocCbIn';

                                    IF NOT FOUND THEN
                                        vAutonumIdAllocCbIn := vEmptyId;
                                    END IF;

                                    -- Ambil doc no Allocation CB in baru
                                    SELECT A.process_parameter_value INTO vDocNoNewAllocCbIn
                                    FROM t_process_parameter A
                                    WHERE A.process_message_id = vProcessId
                                    AND A.process_parameter_key = 'autonumAllocCbIn';

                                    IF NOT FOUND THEN
                                        vDocNoNewAllocCbIn := vEmptyString;
                                    END IF;

                                    --INSERT INTO test_123(test)
                                    --  SELECT CONCAT(vSoId,'-',vFlgDropshipSO,'-',vMappingCbInSoBalanceItemId,'-',vInvoiceArBalanceId,'-',vAutonumIdAllocCbIn,'-',vDocNoNewAllocCbIn,'-',vProcessId,'-','SUBMIT RG ');

                                    IF( vSoId <> vEmptyId AND vFlgDropshipSO = vYes AND vMappingCbInSoBalanceItemId <> vEmptyId
                                        AND vInvoiceArBalanceId <> vEmptyId AND vAutonumIdAllocCbIn <> vEmptyId AND vDocNoNewAllocCbIn <> vEmptyString) THEN
                                        PERFORM fi_automatic_alloc_cb_in(pSessionId, pTenantId, vUserId, vDatetime, vAutonumIdAllocCbIn, vDocNoNewAllocCbIn, vMappingCbInSoBalanceItemId, vInvoiceArBalanceId);
                                    END IF;
                      END IF;
                END IF;
            END IF;
        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;
ALTER FUNCTION memeflorist.pu_submit_receive_goods(bigint, character varying, character varying)
  OWNER TO sts;