inv_material_txns Sample Queries

As we know inv_material_txns is one of the important tables in Oracle Fusion, here are a few sample queries to use with this table:

To find out the quantity issued for Sales Order last month can be:

select ABS(sum(txn.TRANSACTION_QUANTITY))
FROM inv_material_txns txn,
inv_transaction_types_tl typ,
inv_org_parameters iop1,
egp_system_items_b i
WHERE 1=1
AND txn.inventory_item_id = esi.inventory_item_id
and txn.transaction_type_id = typ.transaction_type_id
and typ.transaction_type_name = ‘Sales Order Issue’
AND typ.language = USERENV(‘LANG’)
AND txn.transaction_date >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))+1
AND txn.organization_id = iop1.organization_id
AND iop1.organization_code = <item master org>
AND i.item_number = <item number>

Similarly query for getting the Miscellaneous Receipt quantity can be like this:

select ABS(sum(txn.TRANSACTION_QUANTITY))
FROM from inv_material_txns imt,
INV_TRANSACTION_TYPES_tl itl,
INV_TXN_SOURCE_TYPES_vl itsl,
INV_TRANSACTION_REASONS_tl itr,
inv_org_parameters iop1,
egp_system_items_b i
where imt.TRANSACTION_TYPE_ID = itl.TRANSACTION_TYPE_ID
and itl.TRANSACTION_TYPE_NAME = ‘Miscellaneous Receipt’
and imt.TRANSACTION_SOURCE_TYPE_ID = itsl.TRANSACTION_SOURCE_TYPE_ID
AND imt.inventory_item_id = esi.inventory_item_id
AND imt.organization_id = iop1.organization_id
AND iop1.organization_code =<item master org code>
AND i.item_number =<item Number>

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..