Introduction
This post is about to get transactions of inventory items with available onhand quantity.
Script to get transactions of inventory items with available onhand quantity.
SELECT recs.doc_type, recs.item_number, recs.doc_no, recs.po_number,
recs.organization_id, recs.inventory_item_id, recs.primary_quantity,
recs.transaction_date
FROM (
/* receipt transactions*/
SELECT ‘2-REC’ doc_type, msi.segment1 item_number,
rsh.receipt_num doc_no, poh.segment1 po_number,
mmt.organization_id, mmt.inventory_item_id,
TO_NUMBER (NVL (mmt.primary_quantity, 0)) primary_quantity,
TRUNC (rt.transaction_date) transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_system_items_b msi,
po.rcv_transactions rt,
po.rcv_shipment_headers rsh,
po.po_headers_all poh
WHERE mmt.transaction_type_id IN (,,,) –Enter your transaction type ids
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.primary_quantity > 0
AND mmt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.po_header_id = poh.po_header_id
UNION ALL
/*production transactions*/
SELECT ‘1-PROD’ doc_type, msi.segment1 item_number,
we.wip_entity_name doc_no, ” po_number, mmt.organization_id,
mmt.inventory_item_id,
NVL (mmt.primary_quantity, 0) primary_quantity,
TRUNC (mmt.transaction_date) transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_system_items_b msi,
wip.wip_discrete_jobs jobs,
wip.wip_entities we
WHERE mmt.transaction_type_id IN (,,,) –Enter your transaction type ids
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.transaction_source_id = jobs.wip_entity_id
AND mmt.inventory_item_id = jobs.primary_item_id
AND mmt.organization_id = jobs.organization_id
AND jobs.wip_entity_id = we.wip_entity_id
AND jobs.organization_id = we.organization_id
AND mmt.primary_quantity > 0
/*adjustment transactions*/
UNION ALL
SELECT ‘3-ADJ’ doc_type, msi.segment1 item_number,
TO_CHAR (mmt.transaction_id) doc_no, ” po_number,
mmt.organization_id, mmt.inventory_item_id,
TO_NUMBER (NVL (mmt.primary_quantity, 0)) primary_quantity,
TRUNC (mmt.transaction_date) transaction_date
FROM inv.mtl_material_transactions mmt,
inv.mtl_system_items_b msi
WHERE mmt.transaction_type_id IN (,,,) –Enter your transaction type ids
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.primary_quantity > 0) recs
WHERE (SELECT SUM (motv.on_hand)
FROM apps.mtl_onhand_total_v motv
WHERE recs.inventory_item_id = motv.inventory_item_id
AND recs.organization_id = motv.organization_id) > 0
AND recs.organization_id = :p_org_id
AND recs.inventory_item_id = :p_item_id
What we expect in the script.
This script helps us to comprehend how get transactions of inventory items with available onhand quantity in Oracle EBS R12.Couple of tables which is being used are.
inv.mtl_material_transactions mmt,
inv.mtl_system_items_b msi,
po.rcv_transactions rt,
po.rcv_shipment_headers rsh,
po.po_headers_all poh etc
Summary
This Post described the query get transactions of inventory items with available onhand quantity in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com.com or use the comment section below to ask your questions.
