Monday 3 October 2016

Query to get the Outstanding Amount of Customers in Project Accounting

SELECT ar.customer_number "CUSTOMER NUMEBR",
  ar.customer_name CLIENT,
  (arp.amount_due_original - arp.amount_due_remaining ) "PAID AMOUNT",
  (TRUNC(sysdate)          -rac.trx_date) +1 "DAYS OUTSTANDING",
  rac.trx_number "INVOICE NUMBER",
  rac.trx_date "TRANSACTION DATE",
  arp.due_date "DUE DATE",
  rac.interface_header_attribute1 "PROJECT NUMBER",
  arp.amount_due_original "ORIGINAL AMOUNT",
  amount_due_remaining "OUTSTANDING AMOUNT"
FROM apps.ra_customer_trx_all rac,
  apps.ar_customers ar,
  apps.AR_PAYMENT_SCHEDULES_ALL arp ,
  apps.pa_draft_invoices_all pa
WHERE rac.bill_to_customer_id =ar.customer_id
AND arp.customer_trx_id       =rac.customer_trx_id
AND arp.trx_number            =pa.ra_invoice_number(+)
AND arp.amount_due_remaining <> 0

No comments:

Post a Comment