Order To Cash 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 = :p_org_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;
