INV Material Transactions Summary

Description
Categories: Enginatics
Repository: Github
Summary report of Inventory item movement including transaction type, source type, and transaction ID’s.
select
count(*) count,
&org_cols
&item_cols
xxen_util.meaning(mmt.transaction_action_id,'MTL_TRANSACTION_ACTION',700) transaction_action,
mtt.transaction_type_name transaction_type,
mtst.transaction_source_type_name source_type,
mmt.transaction_action_id,
mmt.transaction_type_id,
mmt.transaction_source_type_id
from
mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
(select msiv.* from mtl_system_items_vl msiv where '&show_item'='Y') msiv,
mtl_parameters mp,
mtl_parameters mp2
where
1=1 and
mmt.transaction_type_id=mtt.transaction_type_id and
mtt.transaction_source_type_id=mtst.transaction_source_type_id(+) and
mmt.organization_id=msiv.organization_id(+) and
mmt.inventory_item_id=msiv.inventory_item_id(+) and
mmt.organization_id=mp.organization_id(+) and
mmt.transfer_organization_id=mp2.organization_id(+)
group by
&org_group_by
&item_group_by
mtt.transaction_type_name,
mtst.transaction_source_type_name,
mmt.transaction_action_id,
mmt.transaction_type_id,
mmt.transaction_source_type_id
order by
count(*) desc
Parameter Name SQL text Validation
Level
Y
LOV
Item
msiv.concatenated_segments like :item
LOV
Transaction within Days
mmt.transaction_date>=sysdate-:days
Number
Transaction Date From
mmt.transaction_date>=:trx_date_from
DateTime
Transaction Date To
mmt.transaction_date<=:trx_date_to
DateTime
Source Type
mtst.transaction_source_type_name=:source_type_id
LOV
Exclude Source Type
mtst.transaction_source_type_name<>:source_type_id
LOV
Action
mmt.transaction_action_id=xxen_util.lookup_code(:action,'MTL_TRANSACTION_ACTION',700)
LOV
Exclude Action
mmt.transaction_action_id<>xxen_util.lookup_code(:exclude_action,'MTL_TRANSACTION_ACTION',700)
LOV
Transaction Type
mtt.transaction_type_name=:transaction_type
LOV
Exclude Transaction Type
mtt.transaction_type_name<>:transaction_type
LOV
Created By
mmt.created_by=xxen_util.user_id(:created_by)
LOV
Exclude Logical Transactions
mmt.transaction_action_id not in (24,30) and --Cost update, WIP scrap transaction
(mmt.logical_transaction=2 or mmt.logical_transaction is null)
LOV
Organization Code
mmt.organization_id=:organization_id
LOV Oracle
Subinventory
mmt.subinventory_code=:subinventory
LOV