Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.
Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.
All These details can be fetched using API => inv_quantity_tree_pub.query_quantities
****************************************************************************
DECLARE
x_return_status VARCHAR2 (50);
x_msg_count VARCHAR2 (50);
x_msg_data VARCHAR2 (50);
v_item_id NUMBER;
v_organization_id NUMBER;
v_qoh NUMBER;
v_rqoh NUMBER;
v_atr NUMBER;
v_att NUMBER;
v_qr NUMBER;
v_qs NUMBER;
v_lot_control_code BOOLEAN;
v_serial_control_code BOOLEAN;
BEGIN
SELECT inventory_item_id, mp.organization_id
INTO v_item_id, v_organization_id
FROM mtl_system_items_b msib, mtl_parameters mp
WHERE segment1 = :item_number
AND msib.organization_id = mp.organization_id
AND mp.organization_code = :organization_code;
v_qoh := NULL;
v_rqoh := NULL;
v_atr := NULL;
v_lot_control_code := FALSE;
v_serial_control_code := FALSE;
fnd_client_info.set_org_context (1);
inv_quantity_tree_pub.query_quantities (
p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => v_organization_id,
p_inventory_item_id => v_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => v_lot_control_code,
p_is_serial_control => v_serial_control_code,
p_revision => NULL, -- p_revision,
p_lot_number => NULL, -- p_lot_number,
p_lot_expiration_date => SYSDATE,
p_subinventory_code => NULL, -- p_subinventory_code,
p_locator_id => NULL, -- p_locator_id,
p_onhand_source => 3,
x_qoh => v_qoh, -- Quantity on-hand
x_rqoh => v_rqoh, --reservable quantity on-hand
x_qr => v_qr,
x_qs => v_qs,
x_att => v_att, -- available to transact
x_atr => v_atr -- available to reserve
);
DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;
****************************************************************************
Let me know, if any questions. ..
Thanks,
... ..
Thank you friend for sharing this query.oracleconnections
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGood info
ReplyDeleteebiztechnics
Good info
ReplyDeleteebiztechnics
nice info and we are the providing ORACLE APPS TECHNICAL Online Training please visit this site
ReplyDeleteThanks for the information and I have read your blog and i got a very useful and knowledgeable information from your Oracle ADF online training blog. Its really a very nice article.You have done a great job . smartmind online training provides on Oracle ADF online training.
ReplyDeleteoracle-11g-adf-online-training
this article nice ,this information is very useful to me and our company OracleAppsTechnicalOnlineTraining
ReplyDeleteNice information you can also find Oracle APPS techno functional Snippets on
ReplyDeleteOracle APPS techno functional
Nice information you can also find Oracle APPS techno functional Snippets on
ReplyDeleteOracle APPS techno functional
Thanks for posting this useful information,
ReplyDeleteIt is very useful to who are searching for oracle apps technical online training .
Thank you for sharing this useful story.
ReplyDeleteI think your shared information is helpful to me and who are want update their knowledge, who want to started their career with Oracle Apps Technical Online Training.
Great Article, thank you for sharing this useful information!!
ReplyDeleteLinux Online Training India
Online devops Training India
Hadoop admin online Training India
Online Assignment Help Tasmania - Australia Best Tutor is responsible for providing an excellent range of Online Assignment help Tasmania to the students pursuing different subjects as part of their studies.
ReplyDeleteRead More : http://prsync.com/australia-best-tutor/get-good-grades-by-using-the-online-assignment-help-tasmania-2589126
Great Article, thank you for sharing this useful information!!
ReplyDeleteCEH Training In Hyderbad
You’d outstanding guidelines there. I did a search about the field and identified that very likely the majority will agree with your web page.
ReplyDeleteBCOM 1st, 2nd & Final Year TimeTable 2020