To Get MATERIAL Transaction Details

select mp1.organization_code "Org" ---Material Transactions by SKU and ORG

,mp1.organization_name

,msi.segment1 "SKU"

,mmt.subinventory_code "Subinventtory"

,mmt.transfer_subinventory "Transfer Subinventory"

,mp2.organization_code "Transfer Org"

,mmt.transaction_date

,mmt.transaction_quantity

,mtst.transaction_source_type_name "Source Type"

,mgd.segment1 "Source",mmt.transaction_type_id

,mtt.transaction_type_name "Transaction Type"

,ml.meaning "Transaction Action"

,mmt.shipment_number

,fu.user_name

,(select order_number from oe_order_headers_all oeh,oe_order_lines_all oel

where oeh.header_id=oel.header_id and oel.line_id=mmt.trx_source_line_id) order_number

,(select poh.segment1 from po_headers_all poh

where poh.po_header_id=mmt.transaction_source_id ) po_number

,mmt.transaction_id

,mmt.source_code

from mtl_material_transactions mmt

-- ,mtl_parameters mp1

,org_organization_definitions mp1

,hr_organization_units hou

,mtl_system_items_b msi

,mtl_parameters mp2

,mtl_transaction_types mtt

,mtl_txn_source_types mtst

,mfg_lookups ml

,fnd_user fu

,mtl_generic_dispositions mgd

where mmt.organization_id = mp1.organization_id

and hou.organization_id = mp1.organization_id

--and mmt.transaction_type_id = 8 --Physical Inv Adjust

-- and mmt.transaction_type_id in (2,31,41) --31 - Alias Issue, 41 - Alias Receipt, 2 - Subinv Transfer

--and mmt.organization_id = 173

-- and hou.type in ('CC','STORE')

and mmt.inventory_item_id = msi.inventory_item_id

and msi.organization_id = 22

and mp1.organization_code = 'EBT' ---organization(DC)

---and msi.segment1 = '47277371'

and mmt.transfer_organization_id = mp2.organization_id(+)

and mtt.transaction_type_id = mmt.transaction_type_id

and mtst.transaction_source_type_id = mmt.TRANSACTION_SOURCE_TYPE_ID

and ml.LOOKUP_TYPE ='MTL_TRANSACTION_ACTION'

and ml.LOOKUP_CODE = mmt.transaction_action_id

and fu.user_id(+) = mmt.created_by

and mgd.disposition_id(+) = mmt.transaction_source_id

and mgd.organization_id(+) = mmt.organization_id

--and mgd.segment1='On-Hand Adjustment'

and mmt.TRANSACTION_DATE BETWEEN to_date('02-03-2009 00:00:00','DD-MM-YYYY HH24:MI:SS') ---from date

and to_date('03-03-2009 23:59:59','DD-MM-YYYY HH24:MI:SS') ---to date

---and msi.segment1='64709397'

-- and ( mmt.TRANSACTION_ACTION_ID NOT IN (24,30) )

--and fu.user_name = 'ESPCOFI' /** Physical Inv job ran under ESPCOFI

order by mp1.organization_code, msi.segment1, mmt.subinventory_code