, , , ,

Get the Inventory code and Inventory Name/description

Note:- Inventory Organization will be represented with the Organization_id column.
We many give some code and name to the Inventory. With the following Query we can know the Code and Name details of all the Inventories in the Organization.

SELECT distinct SUBSTR(loc.location_code, 1, 4) “Inventory Code”
,DECODE(SUBSTR(loc.tax_name, 1, 3) 
,’Bay’, ‘H’ 
,’Zel’, ‘Z’ 
,’KMT’, ‘Z’) || lpad(substr(loc.location_code, 1, 4), 4, ‘0’)|| ‘ ‘ ||
SUBSTR(loc.address_line_2, 1, 30) “Inventory Name/Description”,
ou.ORGANIZATION_ID “Inventory Organization ID”
FROM hr_locations_all loc,
hr_all_organization_units ou 
WHERE loc.location_id = ou.location_id;

/by
, , , ,

On-hand Quatity of one Particular Item in whole Organization

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = ‘Your Inventory Organization ID’;

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = ‘Your Inventory Organization ID’
group by organization_id;

/by
, , , , , ,

Query : PO, Supplier and Inventory Item

Select pha.segment1 “PO_NUMBER”,pla.line_num,plla.shipment_num,
pv.segment1 “Oracle_Supplier_number”, msib.segment1 “item_number”,pla.item_revision, pla.item_description,
NULL as “Delivery_date”, NULL as “Blank Column”, pla.unit_price, pha.rate,plla.Quantity_received,plla.Quantity_rejected,
(pla.unit_price*pla.Quantity) as “Line_Amount”, NULL as “ITEM_COST”, NULL as “Amount”, pla.VENDOR_PRODUCT_NUM
from apps. PO_HEADERS_ALL pha,
apps. PO_VENDORS pv,
apps. PO_VENDOR_SITES_ALL pvsa,
apps. PO_LINES_ALL pla,
apps. MTL_SYSTEM_ITEMS_B msib,
apps. PO_LINE_LOCATIONS_ALL plla
where pha.org_id=’xxxx’
AND pha.org_id=pvsa.org_id
AND pv.vendor_id=pha.vendor_id
AND pvsa.vendor_id=pv.vendor_id
AND pha.po_header_id=pla.po_header_id(+)
–AND pha.po_header_id=plla.po_header_id
AND plla.po_line_id(+)=pla.po_line_id
–AND msib.organization_id=’xxxx’
AND pla.item_id=msib.inventory_item_id(+)
Order by pha.segment1
/by