Inventory - Onhand , Transaction Quantity , Reserve Quantity

-------------------------------------------------------------------------------------------------------------------

create table  xyka_test_todrop_bkp  as ( select * from xyka_test_todrop)

commit

drop table xyka_test_todrop


create table xyka_test_todrop
as
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM, sum(ON_HAND) ON_HAND
from mtl_onhand_total_v
where ORGANIZATION_ID=105
and SUBINVENTORY_CODE = 'BMD'
group by ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM



alter table xyka_test_todrop
add( qty_transact number,
    qty_reserve number );




select * from xyka_test_todrop

--where item = 'BH-28'

select * from hr_operating_units


DECLARE
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;
a number := 0;
b number := 0;
BEGIN
inv_globals.set_org_id (85);
inv_quantity_tree_pub.clear_quantity_cache;
for x in (  
    select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM,ON_HAND
    from xyka_test_todrop
    --where INVENTORY_ITEM_ID = 8367
           --and LOCATOR_ID = 18471
           --and ORGANIZATION_ID = 112
           --and SUBINVENTORY_CODE = 'IBS'
     )
LOOP
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => x.ORGANIZATION_ID,
p_inventory_item_id => x.INVENTORY_ITEM_ID,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => x.SUBINVENTORY_CODE,
p_locator_id => x.LOCATOR_ID,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);
update  xyka_test_todrop
set QTY_TRANSACT = X_att,
    qty_reserve = x_atr
where INVENTORY_ITEM_ID = x.INVENTORY_ITEM_ID
           and LOCATOR_ID = x.LOCATOR_ID
           and ORGANIZATION_ID = 105
           and SUBINVENTORY_CODE = 'BMD';
a := sql%rowcount;
b := a + b;          
end loop;
dbms_output.put_line(to_char(b));
END;


select * from xyka_test_todrop

commit