Select a.order_number Sales_Order, d.trx_number IC_AR_INVOICE, e.INVOICE_NUM IC_AP_INVOICE, g.segment1 PO_NUMBER,
b.ordered_item, b.invoice_interface_status_code,
b.invoiced_quantity, b.flow_status_code

From apps. oe_order_headers_all a,
     apps. oe_order_lines_all b,
     apps. HR_ALL_ORGANIZATION_UNITS c,
     apps. RA_CUSTOMER_TRX_ALL d,
     apps. AP_INVOICES_ALL e,
     apps. OE_DROP_SHIP_SOURCES f,
     apps. PO_HEADERS_ALL g

Where
b.header_id = a.header_id
AND f.LINE_ID = b.line_id
AND d.org_id=c.organization_id
AND a.order_number='10090178' //SO number
AND c.name LIKE 'XXX%UK%' //Operating unit
AND d.ct_reference= to_char(a.order_number)
AND e.INVOICE_NUM = d.trx_number
AND f.po_header_id=g.po_header_id