oracle ebs 查询物料现有量sql,Oracle EBS OPM 查询现有量

--查询现有量

--created by jenrry

DECLARE

p_inventory_item_id NUMBER := 231652; --NOT NULL

p_organization_id NUMBER := 104; --NOT NULL

p_subinventory VARCHAR2(30) := 'JSK01';

p_locator_id NUMBER := NULL;

p_lot_number VARCHAR2(30) := NULL;

p_qty_type VARCHAR2(30) := 'QOH';

p_onhand_source NUMBER := 3;

l_quantity NUMBER;

-- reutnr msg parameter

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

-- return quantity parameter

l_qty_on_hand NUMBER;

l_qty_res_on_hand NUMBER;

l_qty_res NUMBER;

l_qty_sug NUMBER;

l_qty_att NUMBER;

l_qty_available_to_reserve NUMBER;

l_sqty_on_hand NUMBER;

l_sqty_res_on_hand NUMBER;

l_sqty_res NUMBER;

l_sqty_sug NUMBER;

l_sqty_att NUMBER;

l_sqty_available_to_reserve NUMBER;

l_rev