Introduction:
This blog has the SQL query that retrieves the complete lot genealogy, detailing the full lifecycle of product creation.
Cause of the issue:
Business wants a report that contains the complete lot genealogy, detailing the full lifecycle of product creation.
How do we solve: Create a report with the below SQL query
SELECT
ooh.order_number as “doc num”,
mmt.transaction_quantity as “qty”,
mmt.transaction_uom as “uom”,
mtln.lot_number as “lot”,
mtln.status_id as “lot status”,
msi.segment1 as “item code”,
msi.description as “item name”,
ood.organization_code as “whse”,
mmt.subinventory_code as “location”,
hca.account_number as “customer no”,
hp.party_name as “customer name”,
ooh.cust_po_number as “customer po”
FROM
mtl_material_transactions mmt,
oe_order_lines_all ool,
oe_order_headers_all ooh,
mtl_transaction_lot_numbers mtln,
mtl_system_items_b msi,
org_organization_definitions ood,
hz_cust_accounts hca,
hz_parties hp
WHERE
mmt.trx_source_line_id = ool.line_id
and ool.header_id = ooh.header_id
and mmt.transaction_id = mtln.transaction_id(+)
and mmt.inventory_item_id = msi.inventory_item_id
and mmt.organization_id = msi.organization_id
and mmt.organization_id = ood.organization_id
and ooh.sold_to_org_id = hca.cust_account_id(+)
and hca.party_id = hp.party_id(+)
and mmt.transaction_type_id = 33
and mmt.transaction_date between nvl(:from_txn_date,’01-jan-1951′) and nvl(:to_txn_date,sysdate)
order by 1
