Home » Applications » Oracle Fusion Apps & E-Business Suite » AR aging query problem (aging query)
AR aging query problem [message #511776] Wed, 15 June 2011 01:25
riya123
Messages: 12
Registered: June 2011
Junior Member
hi experts!
please correct me where i made mistake.
it is a aging query...but data is not match with front end.
please check the query & joins.

select * from
   (select DISTINCT
   a.trx_date trx_date,
   e.CUSTOMER_NAME,
  e.CUSTOMER_NUMBER,
  e.ATTRIBUTE14,
 a.ATTRIBUTE10,
 e.ATTRIBUTE15,
 a.trx_number trx_number,
 p.status,
C.type TYPE,
p.AMOUNT_DUE_ORIGINAL,
p.AMOUNT_DUE_REMAINING,
p.AMOUNT_APPLIED,
P.GL_DATE,
sum(nvl(a.exchange_rate, 1) * b.extended_amount) trx_amount
  from  ra_customer_trx_all a,
        ra_customer_trx_lines_all b,
        ra_cust_trx_types_all c,
        RA_CUST_TRX_LINE_GL_DIST_ALL D,
        ra_customers  e,
        ra_site_uses_all  f,
	ar_payment_schedules_all p,
	ar_cash_receipts_all r
      where  a.complete_flag = 'Y'
	  AND E.CUSTOMER_NUMBER='12284'
      and a.bill_to_site_use_id = f.site_use_id
      and f.org_id=84
      and a.bill_to_customer_id = e.customer_id
      and c.org_id = a.org_id
	  AND A.ATTRIBUTE10=NVL(:PRODUCT, A.ATTRIBUTE10)
	  AND E.ATTRIBUTE15=NVL(:BUSINESS_CENTRE, E.ATTRIBUTE15)
      and a.cust_trx_type_id = c.cust_trx_type_id
      and c.type in ('INV', 'DM')
      and a.customer_trx_id = b.customer_trx_id
	  AND A.CUSTOMER_TRX_ID=D.CUSTOMER_TRX_ID
	  and p.CASH_RECEIPT_ID=r.CASH_RECEIPT_ID
	  and p.CUSTOMER_ID=e.customer_id
	  AND NVL(D.CUSTOMER_TRX_LINE_ID,0)=0
          and sysdate-p.due_date>90
	  AND D.ACCOUNT_CLASS='REC'
	  	  and d.latest_rec_flag = 'Y'
          GROUP BY     a.trx_date,c.name, a.trx_number,p.status,C.type,a.customer_trx_id,e.CUSTOMER_NAME,e.CUSTOMER_NUMBER,e.attribute14,a.attribute10,p.AMOUNT_DUE_ORIGINAL,p.AMOUNT_DUE_REMAINING,p.AMOUNT_APPLIED,p.AMOUNT_CREDITED,P.GL_DATE,e.attribute15
order by trx_date desc);






Previous Topic: navigating
Next Topic: Make payment for multiple invoices in single shot
Goto Forum:
  


Current Time: Fri Jul 23 18:18:22 CDT 2021