select * from
(select fnar.batch_id,
fnar.customer_trx_id,
rct.bill_to_customer_id cust_acct_id,
rct.bill_to_site_use_id cust_site_use_id,
fnar.ar_txn_rank,
nvl(fnar.netted_amt,0) as netted_amt,
fnb.batch_currency as reckoning_currency,
rct.trx_number,
rct.invoice_currency_code as transaction_currency,
rctt.description as transaction_type,
hp.party_name as cust_acct_name,
hca.account_number as cust_acct_number,
rct.trx_date,
hcsu.location,
hcsu.tax_reference as location-tax_regn_num,
hp.tax_reference as cust_tax_regn_num,
hp.jgzz_fiscal_code as cust_taxpayer_id,
sum(rctl.extended_amount) as transaction_amt,
acr.receipt_number,
fnar.applied_disc,
rct.cust_trx_type_id
from fun_net_ar_txns_all fnar,
fun_net_batches_all fnb,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_types_all rctt,
hz_cust_accounts_all hca,
hz_cust_site_uses_all hcsu,
hz_parties hp,
ar_cash_receipts_all acr
where fnar.batch_id=fnb.batch_id
and fnar.customer_trx_id=rct.customer_trx_id
and rct.customer_trx_id=rctl.customer_trx_id
and rct.cust_trx_type_id=rctt.cust_trx_type_id
and fnar.org_id=rctt.org_id
and hca.cust_account_id=rct.bill_to_customer_id
and rct.bill_to_site_use_id=hcsu.site_use_id
and hca.party_id=hp.party_id
and fnar.cash_receipt_id=acr.cash_receipt_id(+)
group by fnar.batch_id,
fnar.customer_trx_id,
rct.bill_to_customer_id,
rct.bill_to_site_use_id,
fnar.ar_txn_rank,
fnar.netted_amt,
fnb.batch_currency,
rct.trx_number,
rct.invoice_currency_code,
hca.account_number,
rct.trx_date,
hcsu.location,
hcsu.tax_reference,
hp.tax_reference,
hp.jgzz_fiscal_code,
acr.receipt_number,
fnar.applied_disc,
rct.cust_trx_type_id,
order by fnar.ar_txn_rank) qrslt
where 1=1
order by ar_txn_rank
