O2C Query

SELECT hp.party_name customer
               ,hca.account_number
               ,hp.party_number
               ,ooh.order_number
               ,ooh.flow_status_code Order_status
               ,rcta.trx_date Invoice_date
               ,rcta.trx_number Invoice_number
               ,rcta.status_trx Invoice_Status
               ,rcta.invoice_currency_code Invoice_currency
               ,rctla.line_number
               ,msi.segment1 Item_number
               ,rctla.description
               ,rctla.extended_amount line_amt
               ,arp.amount_due_original
               ,arp.amount_due_remaining
               ,rcta.org_id
               ,rcta.customer_trx_id
 FROM ra_customer_trx_all rcta,
             ra_customer_trx_lines_all rctla,
             ra_cust_trx_types_all rctt,
             ar_payment_schedules_all arp,
             hz_cust_accounts_all hca,
             hz_parties hp,
             mtl_system_items_b msi,
             oe_order_lines_all ool,
             oe_order_headers_all ooh
WHERE rcta.customer_trx_id = rctla.customer_trx_id
      AND msi.inventory_item_id = rctla.inventory_item_id
      AND msi.organization_id = <inventory organization id>
      AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
      AND arp.customer_trx_id = rcta.customer_trx_id
      AND rctla.line_type = 'LINE'
      AND rcta.org_id = rctla.org_id
      AND rcta.bill_to_customer_id = hca.cust_account_id
      AND hca.party_id = hp.party_id
      AND rctla.interface_line_attribute6 = ool.line_id
      AND ool.header_id = ooh.header_id
ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;