INV Material Transactions Summary

➜ Back to Library
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
Download
Related Blitz Reports
INV Material Transactions2 GL Account Analysis (with VT) INV Material Transactions GL Account Analysis (Distributions) TB GL Account Analysis (Drilldown)v2 GL Account Analysis (Drilldown) (with inventory and WIP) TB GL Account Analysis (Drilldown)v3 GL Account Analysis ECC Discrete Completions, Assembly Completion Transactions WA: INV Material Transactions Summary
Blitz Report™