/* Formatted on 06-Aug-14 9:41:45 AM (QP5 v5.114.809.3010) */
DECLARE
CURSOR bmd_onhand
IS
-- SELECT * FROM XYKA_BMD_AGE_JUN;
SELECT segment1
FROM mtl_system_items_b
WHERE segment1 IN (SELECT item_code FROM XYKA_BMD_AGE_JUN)
AND organization_id = 798;
l_inv_id number;
l_org_id number;
l_onhand number;
l_reserved number;
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
BEGIN
FOR c1_rec IN bmd_onhand
LOOP
SELECT INVENTORY_ITEM_ID
INTO l_inv_id
FROM mtl_system_items_b
WHERE segment1 = c1_rec.segment1 AND organization_id = 798;
inv_globals.set_org_id (798);
inv_quantity_tree_pub.clear_quantity_cache;
inv_quantity_tree_pub.query_quantities (
p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => 798,
p_inventory_item_id => l_inv_id,
p_tree_mode => 1,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_grade_code => NULL,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr,
x_sqoh => x_sqoh,
x_srqoh => x_srqoh,
x_sqr => x_sqr,
x_sqs => x_sqs,
x_satt => x_satt,
x_satr => x_sqtr
);
UPDATE XYKA_BMD_AGE_JUN
SET ONHAND_QTY = x_qoh, RESERVED_QTY = x_rqoh
WHERE ITEM_CODE = c1_rec.segment1;
DBMS_OUTPUT.put_line (c1_rec.segment1 ||' - '|| x_qoh ||' - '|| x_rqoh);
END LOOP;
END;