SQL to get Material Transaction Details (Oracle Fusion Inventory Management)

 SQL to get Material Transaction Details

SELECT iop.organization_code warehouse_code
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.primary_quantity
     , imt.transaction_date
     , imt.transaction_source_id
     , imt.transaction_source_name
     , imt.rcv_transaction_id     
     , imt.distribution_account_id
     , imt.trx_source_line_id
     , imt.trx_source_delivery_id
     , imt.rma_line_id
     , imt.transfer_transaction_id
     , imt.transaction_Set_id
     , imt.source_code
     , imt.source_line_id
     , imt.transfer_organization_id
     , imt.transfer_subinventory
     , imt.shipment_number
  FROM inv_material_txns imt
     , inv_org_parameters iop
     , egp_system_items_b esi
     , inv_transaction_types_vl ittv
     , fnd_lookup_values_vl flva
     , inv_txn_source_types_vl itst
 WHERE imt.organization_id = iop.organization_id
   AND imt.organization_id = esi.organization_id
   AND imt.inventory_item_id = esi.inventory_item_id
   AND imt.transaction_type_id = ittv.transaction_type_id
   AND ittv.transaction_action_id = flva.lookup_code
   AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
   AND flva.enabled_flag = 'Y'
   AND ittv.transaction_source_type_id = itst.transaction_source_type_id
   AND ittv.transaction_type_name = 'RMA Receipt';

/

Sales Order Issue Materail Transaction Report. 

SELECT iop.organization_code warehouse_code
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.primary_quantity
     , imt.transaction_date
     , imt.transaction_source_id
     , imt.transaction_source_name
     , imt.rcv_transaction_id
     , imt.distribution_account_id
     , imt.trx_source_line_id
     , imt.trx_source_delivery_id
     , imt.rma_line_id
     , imt.transfer_transaction_id
     , imt.transaction_set_id
     , imt.source_code
     , imt.source_line_id
     , imt.transfer_organization_id
     , imt.transfer_subinventory
     , imt.shipment_number
     , dha.source_order_number
     , dha.order_number
     , dfla.status_code
     , dfla.source_line_number
  FROM inv_material_txns imt
     , inv_org_parameters iop
     , egp_system_items_b esi
     , inv_transaction_types_vl ittv
     , fnd_lookup_values_vl flva
     , inv_txn_source_types_vl itst
     , fusion.doo_headers_all dha
     , fusion.doo_fulfill_lines_all dfla
     , fusion.wsh_delivery_assignments wda
     , fusion.wsh_new_deliveries wnd
     , fusion.wsh_delivery_details wdd
 WHERE imt.organization_id = iop.organization_id
   AND imt.organization_id = esi.organization_id
   AND imt.inventory_item_id = esi.inventory_item_id
   AND imt.transaction_type_id = ittv.transaction_type_id
   AND ittv.transaction_action_id = flva.lookup_code
   AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
   AND flva.enabled_flag = 'Y'
   AND ittv.transaction_source_type_id = itst.transaction_source_type_id
   AND ittv.transaction_type_name LIKE 'Sales Order Issue'
   AND dha.header_id = dfla.header_id
   AND dfla.fulfill_line_id = wdd.source_shipment_id
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id (+)
   AND wda.delivery_detail_id = imt.trx_source_line_id (+)
   AND dha.submitted_flag = 'Y'
/