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
x.count,
&proj_cols2
&org_cols2
&item_cols2
&show_subinv_cols2
&category_columns
x.transaction_action,
x.transaction_type,
x.source_type,
x.transaction_action_id,
x.transaction_type_id,
x.transaction_source_type_id
from
(
select
count(*) count,
&proj_cols
&org_cols
&item_cols
&show_subinv_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_subinventory
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
&subinv_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
)x
Parameter NameSQL textValidation
Level
Y
LOV
Item
msiv.concatenated_segments like :item
LOV
Show Subinventory
mmt.organization_id=msi.organization_id(+) and
mmt.subinventory_code = msi.secondary_inventory_name(+)
LOV
Include Expense Subinventory
msi.asset_inventory(+) <> 2
LOV
Category Set 1
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual
LOV
Category Set 2
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual
LOV
Category Set 3
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual
LOV
Transaction within Days
mmt.transaction_date>=sysdate-:days
Number
Transaction Date From
mmt.transaction_date>=:trx_date_from
Date
Transaction Date To
mmt.transaction_date<:trx_date_to+1
Date
Source Type
mtst.transaction_source_type_name=:source_type_id
LOV
Exclude Source Type
mtst.transaction_source_type_name<>:exclude_source_type
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<>:exclude_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