Introduction:

This Post illustrates the steps required to On Hand Quantity with Available To Transact in Oracle EBS.

Script to Fetch the On Hand Quantity with Available To Transact

Step-1

Create the below function

CREATE FUNCTION xx_item_availble_to_transact (
p_inventory_item_id NUMBER,
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_locater NUMBER
)
RETURN NUMBER
IS
l_api_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_qty_oh NUMBER; –v_qoh
l_qty_res_oh NUMBER; –v_rqoh
l_qty_res NUMBER; –v_atr
l_qty_sug NUMBER; –v_rqoh
l_qty_att NUMBER; –v_att
l_qty_atr NUMBER; –v_atr
is_lot_control_code BOOLEAN;
is_serioal_control_code BOOLEAN;
att NUMBER;
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_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 => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locater,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr
);

IF l_api_return_status <> fnd_api.g_ret_sts_success
THEN
att := 0;
ELSE
att := l_qty_att;
END IF;

RETURN att;
END;
/

 

Step-2

Run this query to Fetch the On Hand Quantity with Available To Transact.

 

SELECT item_code item, item_description description,
subinventory_code sun_inv, LOCATOR, SUM (on_hand) on_hand,
availble_to_transact
FROM (SELECT item.segment1 item_code, item.description item_description,
(on_hand.on_hand) on_hand, LOCATOR,
(bolinf.xxth_item_availble_to_transact
(on_hand.inventory_item_id,
on_hand.organization_id,
on_hand.subinventory_code,
on_hand.locator_id
)
) availble_to_transact,
on_hand.subinventory_code
FROM mtl_system_items_b item, mtl_onhand_total_mwb_v on_hand
WHERE item.organization_id = 84
AND item.inventory_item_id = on_hand.inventory_item_id
AND on_hand.subinventory_code = ‘FG’
AND item.segment1 IN
(‘XXXXXX’))
GROUP BY item_code,
item_description,
LOCATOR,
subinventory_code,
availble_to_transact

 

Queries

Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions

Share this post
Recent Posts

Leave a Comment

Start typing and press Enter to search