Oracle EBS Function: Get Inventory Item On-Hand Quantity

CREATE OR REPLACE FUNCTION xx_onhand_qty (

    p_inv_item_id  IN  VARCHAR2,

    p_org_id       IN  NUMBER,

    p_sub_inv      IN  VARCHAR2,

    p_loc_id       IN  NUMBER

) RETURN NUMBER IS

    x_return_status        VARCHAR2(50);

    x_msg_count            VARCHAR2(50);

    x_msg_data             VARCHAR2(50);

    v_item_id              NUMBER;

    v_organization_id      NUMBER;

    v_qoh                  NUMBER;

    v_rqoh                 NUMBER;

    v_atr                  NUMBER;

    v_att                  NUMBER;

    v_qr                   NUMBER;

    v_qs                   NUMBER;

    v_lot_control_code     BOOLEAN;

    v_serial_control_code  BOOLEAN;

    l_qty                  NUMBER;

    l_qty_avai_reserve     NUMBER := 0;

    l_qty_avai_transact    NUMBER := 0;

    l_reserved_qty         NUMBER := 0;

BEGIN

    SELECT

        inventory_item_id,

        mp.organization_id

    INTO

        v_item_id,

        v_organization_id

    FROM

        mtl_system_items_b  msib,

        mtl_parameters      mp

    WHERE

            msib.inventory_item_id = p_inv_item_id

        AND msib.organization_id = mp.organization_id

        AND msib.organization_id = p_org_id; -- :organization_code;

    BEGIN

        SELECT

            nvl(SUM(reservation_quantity), 0)

        INTO l_reserved_qty

        FROM

            mtl_reservations

        WHERE

                inventory_item_id = p_inv_item_id

            AND organization_id = p_org_id

            AND subinventory_code = p_sub_inv

            AND locator_id = p_loc_id;

    EXCEPTION

        WHEN OTHERS THEN

            l_reserved_qty := 0;

            dbms_output.put_line('ERROR: ' || sqlerrm);

    END;

    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := false;

    v_serial_control_code := false;

    fnd_client_info.set_org_context(1);

    inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, p_init_msg_lst => 'F',

                                          x_return_status => x_return_status,

                                          x_msg_count => x_msg_count,

                                          x_msg_data => x_msg_data,

                                          p_organization_id => v_organization_id,

                                          p_inventory_item_id => v_item_id,

                                          p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,

                                          p_is_revision_control => false,

                                          p_is_lot_control => v_lot_control_code,

                                          p_is_serial_control => v_serial_control_code,

                                          p_revision => NULL,                          -- p_revision,

                                          p_lot_number => NULL,                        -- p_lot_number,

                                          p_lot_expiration_date => sysdate,

                                          p_subinventory_code => nvl(p_sub_inv, NULL),                 -- p_subinventory_code,

                                          p_locator_id => nvl(p_loc_id, NULL),                        -- p_locator_id,

                                          p_onhand_source => 3,

                                          x_qoh => v_qoh,                    -- Quantity on-hand

                                          x_rqoh => v_rqoh,         --reservable quantity on-hand

                                          x_qr => v_qr,

                                          x_qs => v_qs,

                                          x_att => v_att,               -- available to transact

                                          x_atr => v_atr                 -- available to reserve

                                          );

    l_qty := nvl(v_qoh, 0); --On Hand qty

    l_qty_avai_reserve := nvl(v_atr, 0); --Available to Reserve

    l_qty_avai_transact := nvl(v_att, 0); --Available to Transact

    RETURN nvl((l_qty_avai_transact - l_reserved_qty), 0);

    dbms_output.put_line('On-Hand Quantity: ' || v_qoh);

    dbms_output.put_line('Available to reserve: ' || v_atr);

    dbms_output.put_line('Quantity Reserved: ' || v_qr);

    dbms_output.put_line('Quantity Suggested: ' || v_qs);

    dbms_output.put_line('Available to Transact: ' || v_att);

EXCEPTION

    WHEN OTHERS THEN

        RETURN NULL;

        dbms_output.put_line('ERROR: ' || sqlerrm);

END xx_onhand_qty; 

/

--Testing Script: get onhand qty--

select XX_ONHAND_QTY(8334013,85,'ZONEA',147167) from dual;  --Item_id,Organization_id,Sub_inv,Locator_id