SQL to get the RMA Details In Oracle Fusion (Inventory/Order Management)

SELECT DISTINCT iop.organization_code warehouse_code
              , haotl.name warehouse_name
              , rt.subinventory
              , rah.ra_document_number AS rma_number
              , dla.display_line_number line_number
              , dla.display_line_number
                  || '.'
                  || dfla.fulfill_line_number rma_line_number
              , rsh.receipt_num rma_receipt_number
              , esi.item_number sku
              , nvl2(dlsn.item_serial_number_from, 1, rsl.quantity_received) quantity_received
              , dlsn.item_serial_number_from
              , dlsn.item_serial_number_to
              , rt.transaction_id
              , rt.transaction_type
              , rt.quantity
              , rt.uom_code
              , ral.receipt_advice_line_number AS doo_fulfil_line_id
              , dfld.rma_receipt_date AS rma_receipt_date
              , rt.creation_date
              , rt.transaction_date
              , dha.order_type_code
              , DECODE(dha.order_type_code, 'B2BRMA', NVL(substr(dfla.source_line_number,0,instr(dfla.source_line_number,'.')-1),dfla.source_line_number),1) source_line_number
  FROM rcv_shipment_headers rah
     , rcv_shipment_lines ral
     , rcv_shipment_headers rsh
     , rcv_shipment_lines rsl
     , rcv_transactions rt
     , inv_org_parameters iop
     , fusion.hr_organization_units_f_tl haotl
     , fusion.hr_org_unit_classifications_f houc
     , doo_fulfill_lines_all dfla
     , doo_fulfill_line_details dfld
     , doo_lot_serial_numbers dlsn
     , egp_system_items_b esi
     , doo_lines_all dla
     , doo_headers_all dha
 WHERE 1 = 1
--   AND rah.ra_document_number = '1595254717339306'
   AND rt.transaction_type = 'DELIVER'
   AND rt.source_document_code = 'RMA'
   AND rt.shipment_header_id = rsh.shipment_header_id
   AND rt.shipment_line_id = rsl.shipment_line_id
   AND rsh.shipment_header_id = rsl.shipment_header_id
   AND rt.receipt_advice_header_id = rah.shipment_header_id
   AND rt.receipt_advice_line_id = ral.shipment_line_id
   AND iop.organization_id = rsl.to_organization_id
   AND haotl.organization_id = rsl.to_organization_id
   AND haotl.language = userenv('LANG')
   AND houc.organization_id = haotl.organization_id
   AND houc.classification_code = 'INV'
   AND rt.transaction_id = dfld.rma_receipt_transaction_id
   AND dfla.fulfill_line_id = dfld.fulfill_line_id
   AND upper(dfld.task_type) = 'RETURN'
   AND dfla.fulfill_line_id = dlsn.fulfill_line_id (+)
   AND dfla.inventory_item_id = esi.inventory_item_id
   AND dfla.inventory_organization_id = esi.organization_id
   AND dfla.line_id = dla.line_id
   AND dla.header_id = dha.header_id
  ORDER BY 4
        , 5
        , 6

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'
/