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_revision_control mtl_system_items_b.revision_qty_control_code%TYPE;

l_lot_control_type mtl_system_items_b.lot_control_code%TYPE;

l_item_serial_control_code mtl_system_items_b.serial_number_control_code%TYPE;

--contorl parameter

l_is_revision_control BOOLEAN;

l_is_lot_control BOOLEAN;

l_is_serial_control BOOLEAN;

BEGIN

--

-- get attribute of the item

--

SELECT msi.revision_qty_control_code, msi.lot_control_code, msi.serial_number_control_code

INTO l_revision_control, l_lot_control_type, l_item_serial_control_code

FROM mtl_system_items_b msi

WHERE msi.inventory_item_id = p_inventory_item_id

AND msi.organization_id = p_organization_id;

IF l_revision_control = 2 THEN

l_is_revision_control := TRUE;

ELSE

l_is_revision_control := FALSE;

END IF;

IF l_lot_control_type <> 1 AND p_lot_number IS NOT NULL THEN

l_is_lot_control := TRUE;

ELSE

l_is_lot_control := FALSE;

END IF;

IF l_item_serial_control_code = 2 THEN

l_is_serial_control := TRUE;

ELSE

l_is_serial_control := FALSE;

END IF;

inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,

p_init_msg_lst => fnd_api.g_false,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_organization_id => p_organization_id,

p_inventory_item_id => p_inventory_item_id,

p_tree_mode => 3,

p_is_revision_control => l_is_revision_control,

p_is_lot_control => l_is_lot_control,

p_is_serial_control => l_is_serial_control,

p_grade_code => NULL,

p_demand_source_type_id => -1,

p_demand_source_header_id => -1,

p_demand_source_line_id => -1,

p_demand_source_name => NULL,

p_revision => NULL,

p_lot_number => p_lot_number,

p_subinventory_code => p_subinventory,

p_locator_id => p_locator_id,

p_onhand_source => p_onhand_source,

x_qoh => l_qty_on_hand,

x_rqoh => l_qty_res_on_hand,

x_qr => l_qty_res,

x_qs => l_qty_sug,

x_att => l_qty_att,

x_atr => l_qty_available_to_reserve,

x_sqoh => l_sqty_on_hand,

x_srqoh => l_sqty_res_on_hand,

x_sqr => l_sqty_res,

x_sqs => l_sqty_sug,

x_satt => l_sqty_att,

x_satr => l_sqty_available_to_reserve);

IF l_return_status = fnd_api.g_ret_sts_success THEN

IF p_qty_type = 'QOH' THEN

l_quantity := l_qty_on_hand;

ELSIF p_qty_type = 'ATT' THEN

l_quantity := l_qty_att;

ELSIF p_qty_type = 'QR' THEN

l_quantity := l_qty_res;

ELSIF p_qty_type = 'ATR' THEN

l_quantity := l_qty_available_to_reserve;

ELSE

l_quantity := l_qty_att;

END IF; --IF p_qty_type = 'QOH' THEN

ELSE

l_quantity := NULL;

END IF;

dbms_output.put_line('现有量l_qty_on_hand=' || l_qty_on_hand);

dbms_output.put_line('保留现有量l_qty_res_on_hand=' || l_qty_res_on_hand);

dbms_output.put_line('保留量l_qty_res=' || l_qty_res);

dbms_output.put_line('分配量l_qty_sug=' || l_qty_sug);

dbms_output.put_line('可处理l_qty_att=' || l_qty_att);

dbms_output.put_line('可保留l_qty_available_to_reserve=' || l_qty_available_to_reserve);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('出错' || SQLERRM);

-- RETURN NULL;

END;