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

INV Material Transactions

Description
Categories: Enginatics, Logistics, Manufacturing
Inventory material transactions

select
mp.organization_code org,
xxen_util.client_time(mmt.transaction_date) transaction_date,
msiv.concatenated_segments item,
msiv.description item_desc,
mmt.transaction_quantity,
mmt.transaction_uom,
mmt.primary_quantity,
mmt.secondary_transaction_quantity secondary_quantity,
mmt.secondary_uom_code secondary_uom,
mmt.subinventory_code subinventory,
inv_project.get_locator(mmt.locator_id,mmt.organization_id) locator,
decode(inv_check_product_install.check_cse_install,'Y',nvl(hl.clli_code,substr(hl.city,1,10)||substr(hl.location_id,1,10)),substr(hl.city,1,10)||substr(hl.location_id,1,10)) location,
mmt.revision,
mmt.transfer_subinventory,
inv_project.get_locator(mmt.transfer_locator_id,mmt.transfer_organization_id) transfer_locator,
mp2.organization_code transfer_org,
mp3.organization_code||'-'||haou3.name owning_party,
mp4.organization_code||'-'||haou4.name planning_party,
mtst.transaction_source_type_name source_type,
case
when mmt.transaction_source_type_id=6 then mgd.segment1 --Account Alias
when mmt.transaction_source_type_id in (2,8,12) then mso.segment1||'.'||mso.segment2||'.'||mso.segment3 --Sales Order, Internal Order, RMA
when mmt.transaction_source_type_id=11 then ccu.description --Cost Update
when mmt.transaction_source_type_id=9 then mcch.cycle_count_header_name --Cycle Count
when mmt.transaction_source_type_id=3 then gcck.concatenated_segments --Account
when mmt.transaction_source_type_id=13 or mmt.transaction_source_type_id>100 then mmt.transaction_source_name --Inventory
when mmt.transaction_source_type_id=10 then mpi.physical_inventory_name --Physical Inventory
when mmt.transaction_source_type_id=1 then pha.segment1 --PO
when mmt.transaction_source_type_id=16 then okhab.contract_number --Project Contracts
when mmt.transaction_source_type_id=7 then prha.segment1 --Requisition
when mmt.transaction_source_type_id=5 then we.wip_entity_name --WIP Job or Schedule
when mmt.transaction_source_type_id=4 then mtrh.request_number --Move Order
end source,
mtt.transaction_type_name transaction_type,
xxen_util.meaning(mmt.transaction_action_id,'MTL_TRANSACTION_ACTION',700) transaction_action,
mtr.reason_name reason,
mtr.description reason_description,
mmt.source_line_id,
mmt.transaction_id,
mmt.rcv_transaction_id receiving_transaction_id,
xxen_util.user_name(mmt.created_by) created_by,
xxen_util.client_time(mmt.creation_date) creation_date,
sum(mmt.transaction_quantity) over (partition by mmt.inventory_item_id order by mmt.transaction_date,mmt.transaction_id) sum_transaction_quantity
from
mtl_parameters mp,
mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_system_items_vl msiv,
mtl_parameters mp2,
hz_locations hl,
mtl_parameters mp3,
mtl_parameters mp4,
hr_all_organization_units haou3,
hr_all_organization_units haou4,
mtl_transaction_reasons mtr,
mtl_generic_dispositions mgd,
mtl_sales_orders mso,
cst_cost_updates ccu,
mtl_cycle_count_headers mcch,
gl_code_combinations_kfv gcck,
mtl_physical_inventories mpi,
po_headers_all pha,
okc_k_headers_all_b okhab,
po_requisition_headers_all prha,
wip_entities we,
&xrrpv_table
mtl_txn_request_headers mtrh
where
1=1 and
mp.organization_id=mmt.organization_id and
mmt.transaction_type_id=mtt.transaction_type_id and
mmt.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.transfer_organization_id=mp2.organization_id(+) and
mmt.ship_to_location_id=hl.location_id(+) and
mmt.owning_organization_id=mp3.organization_id(+) and
mmt.owning_organization_id=haou3.organization_id(+) and
mmt.planning_organization_id=mp4.organization_id(+) and
mmt.planning_organization_id=haou4.organization_id(+) and
mmt.reason_id=mtr.reason_id(+) and
decode(mmt.transaction_source_type_id,6,mmt.transaction_source_id)=mgd.disposition_id(+) and
decode(mmt.transaction_source_type_id,6,mmt.organization_id)=mgd.organization_id(+) and
case when mmt.transaction_source_type_id in (2,8,12) then mmt.transaction_source_id end=mso.sales_order_id(+) and
decode(mmt.transaction_source_type_id,11,mmt.transaction_source_id)=ccu.cost_update_id(+) and
decode(mmt.transaction_source_type_id,9,mmt.transaction_source_id)=mcch.cycle_count_header_id(+) and
decode(mmt.transaction_source_type_id,3,mmt.transaction_source_id)=gcck.code_combination_id(+) and
decode(mmt.transaction_source_type_id,10,mmt.transaction_source_id)=mpi.physical_inventory_id(+) and
decode(mmt.transaction_source_type_id,10,mmt.organization_id)=mpi.organization_id(+) and
decode(mmt.transaction_source_type_id,1,mmt.transaction_source_id)=pha.po_header_id(+) and
decode(mmt.transaction_source_type_id,16,mmt.transaction_source_id)=okhab.id(+) and
decode(mmt.transaction_source_type_id,7,mmt.transaction_source_id)=prha.requisition_header_id(+) and
decode(mmt.transaction_source_type_id,5,mmt.transaction_source_id)=we.wip_entity_id(+) and
decode(mmt.transaction_source_type_id,4,mmt.transaction_source_id)=mtrh.header_id(+)
order by
mp.organization_code,
msiv.concatenated_segments,
mmt.transaction_date desc,
mmt.transaction_id 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
Organization Code
mp.organization_code=:organization_code
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
organization_id|inventory_item_id
xrrpv.organization_id=mmt.organization_id and
xrrpv.inventory_item_id=mmt.inventory_item_id
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
organization_id|inventory_item_id
(
select distinct
dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1) organization_id,
dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id
from
xxen_report_run_param_values xrrpv
where
xrrpv.run_id=:run_id
) xrrpv,

By continuing to use the site, you agree to the use of cookies. Accept