Order to Cash query / script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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; |