select  customer_id, customer_number,customer_name, location, sum(Receivable)- sum(receivable_applied)+sum(cm_app)  receivable	from
(
select 
	rc.customer_id, 
	rc.customer_number,
	rc.customer_name,	
	hcsu.location,
	sum(NVL(rctlgda.amount,0)* NVL(rcta.exchange_rate,1))Receivable, 
	0 receivable_applied,
	0 cm_app  
from ra_customer_trx_all rcta,
	 ra_customers rc,
	 hz_cust_acct_sites_all hcas, 
	 hz_cust_site_uses_all hcsu,
	 ra_cust_trx_line_gl_dist_all rctlgda,
	 ra_cust_trx_types rctta,
	 gl_code_combinations glcc
where rcta.bill_to_customer_id = nvl(:p_customer_id,rc.customer_id) 
and rcta.bill_to_customer_id = rc.customer_id	 
and rcta.bill_to_customer_id   =hcas.cust_account_id 
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hcsu.site_use_id=rcta.bill_to_site_use_id
and rcta.customer_trx_id = rctlgda.customer_trx_id
and rctlgda.account_class = 'REC' 
--and (hcsu.status='A' or  hcsu.location is null)  
--and rc.status='A'
AND rcta.complete_flag='Y'  
and rcta.cust_trx_type_id = rctta.cust_trx_type_id
and rctta.type in ('INV','CM','DM')
and glcc.code_combination_id = rctlgda.code_combination_id
and glcc.segment1 between nvl(:p_frm_location, glcc.segment1) and nvl(:p_to_location, glcc.segment1)
and glcc.segment6 = '23210100'				
and rctlgda.gl_posted_date is not null --Added so as to retrieve only GL transferred data by ptn 11/12/08
and rctlgda.gl_date <= nvl(:p_start_date,rctlgda.gl_date)  
group by rc.customer_id, rc.customer_number,rc.customer_name,hcsu.location
union all
Select 	rc.customer_id, 
		rc.customer_number,
		rc.customer_name,	
		hcsu.location,
		0 Receivable,
		sum(nvl(ara.acctd_amount_applied_to,0)) receivable_applied,
		0 cm_app    
from ra_customers rc,
     ar_receivable_applications_all ara,
     gl_code_combinations glcc,
     ar_cash_receipts_all acr,
	 hz_cust_site_uses_all hcsu
where rc.customer_id = acr.pay_from_customer
and acr.cash_receipt_id = ara.cash_receipt_id 
and acr.customer_site_use_id=hcsu.site_use_id(+) 
--and (hcsu.status='A' or  hcsu.location is null) 
--and rc.status='A'
and ara.status = 'APP'
and rc.customer_id= nvl(:p_customer_id,rc.customer_id) 
and ara.gl_date <= nvl(:p_start_date,ara.gl_date) 
and glcc.code_combination_id = ara.code_combination_id
and glcc.segment1 between nvl(:p_frm_location, glcc.segment1) and nvl(:p_to_location, glcc.segment1)
and glcc.segment6 = '23210100'  --- receivable applied Account 
and ara.gl_posted_date is not null --Added so as to retrieve only GL transferred data by ptn 11/12/08 
group by rc.customer_id, rc.customer_number,rc.customer_name,hcsu.location	   
union all	  
Select  app.customer_id, 
		app.customer_number,
		app.customer_name,	
		app.location,
		0 Receivable,
		0 receivable_applied,  
     	sum(nvl(ada.acctd_amount_dr,0)-nvl(ada.acctd_amount_cr,0)) cm_app 
from ar_distributions_all ada,gl_code_combinations glcc ,
   		(select ara.receivable_application_id,rc.customer_id, 
		        rc.customer_number, rc.customer_name,hcsu.location 
	   	 from ar_receivable_applications_all ara,
	    	  ra_customer_trx_all  rcta,
			  ra_customers rc,
			  hz_cust_site_uses_all hcsu,
			  hz_cust_acct_sites_all hcas
	   	 where rcta.customer_trx_id = ara.customer_trx_id
		 and rcta.bill_to_customer_id = rc.customer_id	
		 and rcta.bill_to_customer_id =  hcas.cust_account_id 
		 and hcas.cust_acct_site_id   =  hcsu.cust_acct_site_id	   
		 --and rc.status='A'
		 and hcsu.site_use_id =  rcta.bill_to_site_use_id 
	   	 and ara.status = 'APP'
	   	 and rcta.bill_to_customer_id= nvl(:p_customer_id,rc.customer_id)	
	   	 and ara.gl_date <= nvl(:p_start_date,ara.gl_date) --changed as per request by ptn 24/09/08	  
	   	 and ara.gl_posted_date is not null --Added so as to retrieve only GL transferred data by ptn 11/12/08  
	   	 )app 
where source_id = app.receivable_application_id 
and source_type ='REC'
and glcc.code_combination_id = ada.code_combination_id
and glcc.segment1 between nvl(:p_frm_location, glcc.segment1) and nvl(:p_to_location, glcc.segment1)
and glcc.segment6 = '23210100'  --- receivable applied Account 	
group by app.customer_id, app.customer_number,app.customer_name,app.location	
)  
group by customer_id, customer_number,customer_name,location