Inventory Transaction History

select * from gl_je_headers

select * from xyka_cash_payment_header where x_order_number = 16003100816

select * from xyka.xyka_wasel_revenue_interface   where trunc(TRANSACTION_DATE) = '02-MAR-16'


select * from RCV_TRANSACTIONS_INTERFACE

select * from wms_license_plate_numbers where LICENSE_PLATE_NUMBER=  'EHA120148660'

select * from mtl_material_transactions_temp
  where  lpn_id = :l_lpn_id or transfer_lpn_id = :l_lpn_id
  or content_lpn_id = :l_lpn_id union
  select * from mtl_material_transactions_temp
  where  lpn_id = :l_lpn_id or transfer_lpn_id = :l_lpn_id
  or content_lpn_id = :l_lpn_id


select * from wms_license_plate_numbers where LICENSE_PLATE_NUMBER=  'EHA120148660'        


select * from mtl_material_transactions where trunc(CREATION_DATE) between '16-DEC-15' and '31-DEC-15' and organization_id = 122


/* Formatted on 3-14-2016 1:57:44 PM (QP5 v5.114.809.3010) */
  SELECT   mp1.organization_code "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 ,
           cst.item_cost * nvl(mmt.transaction_quantity,0) "Transaction Cost",          
           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,
           cst_item_costs cst
   WHERE    mmt.organization_id = cst.organization_id
     AND mmt.inventory_item_id = cst.inventory_item_id
           AND 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 = 122
           AND mp1.organization_code = '021'               ---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 ('16-12-2015 00:00:00','DD-MM-YYYY HH24:MI:SS') ---from date
                                        AND  TO_DATE ('31-12-2015 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