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>

Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. Try them only when nothing else works.. kind of SOS...

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..