INV Material Transactions Summary

Description

Categories: Enginatics, Logistics, Manufacturing

Summary of inventory material transactions

Copy

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

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

LOV

Ask a question

Download

Related Blitz Reports

INV Material Transactions Material Accounts Distribution Report (detail version) INV Serial number transaction register INV Transaction register ONT Credit Order Discrepancy INV Lot transaction register (1) INV Vendor lot trace INV Lot transaction register CST Periodic Material and Receiving Distribution Details