Select

Select hpas1.party_site_number, ac.creation_date, hp.party_name,hca.account_number, hcsu.site_use_code, hca.party_id, hp.email_address, hp.primary_phone_number, hpas1.PARTY_SITE_ID, ac.first_name, ac.last_name, ac.orig_system_reference, ap.phone_number,ap.country_code, ap.area_code ,acr.usage_code,acr.contact_id from apps.hz_cust_accounts_all hca, apps.hz_parties hp, apps.hz_cust_site_uses_all hcsu, apps.hz_cust_acct_sites_all hcas1, apps.hz_party_sites hpas1, apps. AR_CONTACTS_V ac, apps. AR_PHONES_V ap, apps. AR_CONTACT_ROLES_V acr where hca.party_id=hp.party_id and hca.cust_account_id=ac.customer_id and ac.orig_system_reference =ap.orig_system_reference(+) AND hp.party_id=hpas1.party_id AND hcas1.cust_acct_site_id=hcsu.cust_acct_site_id AND hcsu.org_id='XXX' --and hcsu.site_use_code='BILL_TO' AND hpas1.party_site_id=hcas1.party_site_id and hca.orig_system_reference LIKE '%XXX' and ac.orig_system_reference=acr.orig_system_reference Order By ac.creation_date desc