Category Archives: Inventory

API approach to find On Hand Quantity of an Item

Below script is useful in finding On hand quantity accurately using Oracle Standard API INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES. This script is tested in R12.1.3 instance. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33… Read More »

What is the Difference between Available to Reserve & Available to Transact

Available to Transact = Quantity already reserved and ready to transact Available to Reserve = (Total Quantity in Reservable Subinventory) – (Total Quantity of the Item in the MTL_RESERVATIONS table) or (Quantity in your onhand) – (Quantity already reserved for other orders) How to Calculate the quantity SELECT SUM(transaction_quantity) INTO TR_QTY FROM MTL_ONHAND_QUANTITIES_DETAIL WHERE inventory_item_id… Read More »

SQL Query to find Status of Inventory Accounting Periods

Here is the query to find out the status of Inventory Accounting Periods from back end: SELECT ood.organization_id "Organization ID" , ood.organization_code "Organization Code" , ood.organization_name "Organization Name" , oap.period_name "Period Name" , oap.period_start_date "Start Date" , oap.period_close_date "Closed Date" , oap.schedule_close_date "Scheduled Close" , DECODE(oap.open_flag, ‘P’,’P – Period Close is processing’ , ‘N’,’N -… Read More »

API for UOM Conversion

A unit of measure conversion is a mathematical relationship between two different units of measure. For example, 12 Each = 1 Dozen. If you want to transact items in units of measure belonging to classes other than their primary UOM class, you must define conversions between the base units of measure in different UOM classes.… Read More »

How costing is performed on Inventory Material Transactions

How and when costing will be done for material transactions? Once  inventory transactions are completed then records will be inserted into MTL_MATERIAL_TRANSACTIONS table and can be viewed through Material Transaction from. After that ‘Cost Manager’ program will pick these transactions and try to cost them. If eligible records are presents then Cost Manager triggers, ‘Material… Read More »

Inter-Org Transfer has Zero Quantity on Receiving end!

Once I encountered a situation where in a Shipment Line of Inter-Org  Transfer has Zero Quantity on Receiving end. Here is the screenshot of the problem I have encountered. –To Findout Shipmet_Header_ID SELECT * FROM rcv_shipment_headers WHERE 1 = 1 AND receipt_num = ‘&Recipt_Num’ AND ship_to_org_id = ‘&ship_to_org_id’;–To Findout Shipmet_Header_ID SELECT * FROM rcv_shipment_headers WHERE… Read More »