Sales Order Dispatch details (Link between Material Transactions and Invoice)

SELECT
    oola.line_number,
    oola.line_id,
    ooha.orig_sys_document_ref ,
    ooha.order_source_id,
    ooha.order_number sale_order_no,
    trunc(ooha.ordered_date) sale_order_date,
    hp_ship.party_name customer_name,
    --hl_ship.address1,
    hcs_ship.location customer_site,
    ooha.cust_po_number customer_po_ref,
    rct.trx_number customer_invoice_num,
    rct.trx_date invoice_date,
    msib.segment1 item_code,
    msib.description item_desc,
    oola.shipping_quantity qty,
    oola.order_quantity_uom uom,
    ms.serial_number serial_num,
    mth.request_number move_order_ref,
    trunc(actual_shipment_date) shipment_date
FROM
    oe_order_headers_all ooha,
    oe_order_lines_all oola,
    mtl_system_items_b msib,
    wsh_delivery_details wdd,
    wsh_delivery_assignments wda,
    wsh_new_deliveries wnd,
    hz_cust_site_uses_all hcs_ship,
    hz_cust_acct_sites_all hca_ship,
    hz_party_sites hps_ship,
    hz_parties hp_ship,
    hz_locations hl_ship,
    oe_order_sources oos,
    mtl_txn_request_lines mtl,
    mtl_txn_request_headers mth,
    mtl_material_transactions mt,
    mtl_unit_transactions ms,
    ra_customer_trx_all rct,
    ra_customer_trx_lines_all rcta
WHERE
    ooha.header_id = oola.header_id
    AND NVL(ooha.orig_sys_document_ref,-1) NOT IN (select prha.segment1 from po_requisition_headers_all prha)
    AND oola.ship_from_org_id = msib.organization_id
    AND oola.inventory_item_id = msib.inventory_item_id
    AND wdd.source_header_id = ooha.header_id
    AND wdd.source_line_id = oola.line_id
    AND wda.delivery_detail_id = wdd.delivery_detail_id
    AND wnd.delivery_id = wda.delivery_id
    AND wdd.released_status = 'C'
    AND wnd.status_code = 'CL'
    AND ooha.ship_to_org_id = hcs_ship.site_use_id
    AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
    AND hca_ship.party_site_id = hps_ship.party_site_id
    AND hps_ship.party_id = hp_ship.party_id
    AND hps_ship.location_id = hl_ship.location_id
    AND oos.order_source_id=ooha.order_source_id
    AND mtl.txn_source_line_id = oola.line_id
    AND mth.header_id = mtl.header_id
    AND mt.move_order_line_id(+) = mtl.line_id
    AND mt.transaction_id = ms.transaction_id(+)
    AND mt.subinventory_code(+) = mtl.from_subinventory_code
    AND rcta.interface_line_attribute1(+)=to_char(ooha.order_number)
    AND rcta.interface_line_attribute6(+)=to_char(oola.line_id)
    AND rcta.customer_trx_id=rct.customer_trx_id(+)
    order by ooha.order_number;