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

    vProcessId          bigint;
    vId                 bigint;
    vUserId             bigint;
    vDatetime           character varying(14);
    vFlagNo             character varying(1);
    vFlagYes            character varying(1);
    vStatusRelease      character varying(1);
BEGIN

    vFlagNo := 'N';
    vFlagYes := 'Y';
    vStatusRelease := 'R';

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

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

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

    ------------------------------- ini bagian warehouse from --------------------------------------

    UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE inventory_id = vId;

    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, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C, m_product_custom D
    WHERE A.inventory_id = vId AND
        A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id
        AND B.product_id = D.product_id
        AND D.flg_buy_konsinyasi = vFlagNo
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;

    INSERT INTO in_log_product_consignment_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_status, base_uom_id, qty,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C, m_product_custom D
    WHERE A.inventory_id = vId AND
        A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id
        AND B.product_id = D.product_id
        AND D.flg_buy_konsinyasi = vFlagYes
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;

    -- INSERT kan ke in_balance_transfer_out langsung dengan flg_real = Y
    INSERT INTO in_balance_transfer_out
    (inventory_id, tenant_id, ou_id, warehouse_id, flg_real, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.inventory_id, A.tenant_id, A.ou_to_id, A.warehouse_to_id, vFlagYes, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A
    WHERE A.inventory_id = vId;

    -----------------------------------------------------------------------------------------
    -------------------------------ini bagian warehouse to-----------------------------------


    DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;

        /*
     * 1.insert data ke in_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, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C, m_product_custom D
    WHERE A.inventory_id = vId AND
        A.inventory_id = B.inventory_id AND
        A.warehouse_to_id = C.warehouse_id AND
        B.product_id = D.product_id AND
        D.flg_buy_konsinyasi = vFlagNo
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;

    /*
     * insert data ke in_log_product_consignment_balance_stock
     */
    INSERT INTO in_log_product_consignment_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_status, base_uom_id, qty,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, A.warehouse_to_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C, m_product_custom D
    WHERE A.inventory_id = vId AND
        A.inventory_id = B.inventory_id AND
        A.warehouse_to_id = C.warehouse_id AND
        B.product_id = D.product_id AND
        D.flg_buy_konsinyasi = vFlagYes
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;

    /*
     * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
     */
    INSERT INTO tt_in_product_balance_summary_stock
            (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
    SELECT  pSessionId, A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status,
            B.base_uom_id, SUM(B.qty_realization)
    FROM    in_inventory A, in_inventory_item B
    WHERE   A.inventory_id = B.inventory_id
    AND     A.inventory_id = vId
    GROUP BY A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;

    UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId
    FROM tt_in_product_balance_summary_stock A
    WHERE A.session_id = pSessionId AND
        A.inventory_id = vId 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_id = A.product_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = A.product_status;

    /*
     * 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_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
           0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A
    INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    WHERE A.inventory_id = vId AND
    NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
                WHERE A.tenant_id = C.tenant_id AND
                      A.warehouse_to_id = C.warehouse_id AND
                      B.product_id = C.product_id AND
                      B.product_balance_id = C.product_balance_id AND
                      B.product_status = C.product_status)
    GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;


    INSERT INTO in_balance_transfer_in_item
    (inventory_item_id, tenant_id, inventory_id, line_no, ou_from_id, ou_to_id, qty_out, qty_in,
     base_uom_id, warehouse_from_id, warehouse_to_id, product_id, product_balance_id, product_status,
     ref_doc_type_id, ref_id, ref_item_id, flg_receipt, receipt_id,
     version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.inventory_item_id, A.tenant_id, A.inventory_id, A.line_no, B.ou_from_id, B.ou_to_id, A.qty_request, A.qty_realization,
           A.base_uom_id, B.warehouse_from_id, B.warehouse_to_id, A.product_id, A.product_balance_id, A.product_status,
           A.ref_doc_type_id, A.ref_id, A.ref_item_id, vFlagNo, vEmptyId,
           0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
    WHERE B.inventory_id = vId
    AND A.qty_request <> A.qty_realization;

    INSERT INTO in_balance_transfer_in_item
    (inventory_item_id, tenant_id, inventory_id, line_no, ou_from_id, ou_to_id, qty_out, qty_in,
     base_uom_id, warehouse_from_id, warehouse_to_id, product_id, product_balance_id, product_status,
     ref_doc_type_id, ref_id, ref_item_id, flg_receipt, receipt_id,
     version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.inventory_item_id, A.tenant_id, A.inventory_id, A.line_no, B.ou_from_id, B.ou_to_id, A.qty_request, A.qty_realization,
           A.base_uom_id, B.warehouse_from_id, B.warehouse_to_id, A.product_id, A.product_balance_id, A.product_status,
           A.ref_doc_type_id, A.ref_id, A.ref_item_id, vFlagYes, vEmptyId,
           0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
    WHERE B.inventory_id = vId
    AND A.qty_request = A.qty_realization;


    DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;


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