Order to Cash query

Order to Cash query / script

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 = 116

  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

--  AND ooh.order_number = '13904756'

--  AND hp.party_name = 'BANQUE MAGNETIQUE'

ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;