Reports2017-11-18T12:27:27+00:00

INV Material Transactions Summary

Description
Categories: Enginatics, Logistics, Manufacturing
Summary of inventory material transactions

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 '&enable_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
Created By
mmt.created_by in (select fu.user_id from fnd_user fu where fu.user_name=: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
Item
msiv.concatenated_segments like :item
LOV
Item Description
upper(msiv.description) like upper(:item_descr)
LOV
Exclude Action
mmt.transaction_action_id not in
(
select
to_number(flv.lookup_code)
from
fnd_lookup_values flv
where
flv.meaning=:action and
flv.lookup_type='MTL_TRANSACTION_ACTION' and
flv.view_application_id=700 and
flv.language=userenv('lang') and
flv.security_group_id=0
)
LOV
Action
mmt.transaction_action_id in
(
select
to_number(flv.lookup_code)
from
fnd_lookup_values flv
where
flv.meaning=:action and
flv.lookup_type='MTL_TRANSACTION_ACTION' and
flv.view_application_id=700 and
flv.language=userenv('lang') and
flv.security_group_id=0
)
LOV
Exclude Transaction Type
mtt.transaction_type_name<>:transaction_type
LOV
Transaction Type
mtt.transaction_type_name=:transaction_type
LOV
Exclude Source Type
mtst.transaction_source_type_name<>:source_type_id
LOV
Source Type
mtst.transaction_source_type_name=:source_type_id
LOV
Item
msiv.concatenated_segments,
msiv.description,
Item
msiv.concatenated_segments item,
msiv.description item_description,
sum(mmt.primary_quantity) primary_quantity,
Item
Y
Level
mp.organization_code,
mp2.organization_code,
Level
mp.organization_code,
mp2.organization_code transfer_organization_code,
Level
msiv.concatenated_segments,
msiv.description,
Level
msiv.concatenated_segments item,
msiv.description item_description,
sum(mmt.primary_quantity) primary_quantity,
Organization Code
mmt.organization_id=:organization_id
LOV Oracle
Transaction Date To
mmt.transaction_date<=:trx_date_to
DateTime
Transaction Date From
mmt.transaction_date>=:trx_date_from
DateTime
Transaction within Days
mmt.transaction_date>=sysdate-:days
Number
Level
Y
LOV