Friday, November 2, 2018

Invoice List for a Customer query

Invoice List for a Customer query

Query to retrieve Invoice List for a specific Customer
SELECT hp.party_name customer
      ,hca.account_number
      ,rcta.trx_date
      ,rcta.trx_number Invoice_number
      ,rcta.status_trx
      ,rcta.invoice_currency_code Invoice_currency
      ,rctla.line_number
      ,msi.segment1 Item_number
      ,rctla.description
      ,rctla.extended_amount line_amt
      ,rcta.org_id
      ,rctt.cust_trx_type_id    
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       RA_CUST_TRX_TYPES_ALL rctt,
       HZ_CUST_ACCOUNTS_ALL HCA,
       HZ_PARTIES HP,
       MTL_SYSTEM_ITEMS_B msi
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 rctt.TYPE = 'INV'
  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 hp.party_name = 'A. C. Networks'
--  AND hca.account_number = '1581'
--  AND rcta.status_trx = 'OP'
  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...