Friday, November 2, 2018

Order to Cash query

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;

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...