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,
&proj_cols
&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,
pa_projects_all ppa,
pa_tasks pt,
pa_projects_all ppa2,
pa_tasks pt2,
pa_projects_all ppa3,
pa_tasks pt3
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(+) and
mmt.project_id=ppa.project_id(+) and
mmt.task_id=pt.task_id(+) and
mmt.source_project_id=ppa2.project_id(+) and
mmt.source_task_id=pt2.task_id(+) and
mmt.to_project_id=ppa3.project_id(+) and
mmt.to_task_id=pt3.task_id(+)
group by
&proj_group_cols
&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
mp.organization_code=:organization_code
LOV
Subinventory
mmt.subinventory_code=:subinventory
LOV