R12 Customer Contacts Query ...

/* Formatted on 5/8/2013 12:32:34 PM (QP5 v5.114.809.3010) */
SELECT   hcasa.org_id,
         role_acct.account_number,
         hcasa.orig_system_reference,
         rel.subject_id,
         rel.object_id,
         party.party_id party_id,
         rel_party.party_id rel_party_id,
         acct_role.cust_account_id,
         acct_role.cust_acct_site_id,
         party.person_pre_name_adjunct contact_prefix,
         SUBSTRB (party.person_first_name, 1, 40) contact_first_name,
         SUBSTRB (party.person_middle_name, 1, 40) contact_middle_name,
         SUBSTRB (party.person_last_name, 1, 50) contact_last_name,
         party.person_name_suffix contact_suffix,
         acct_role.status,
         org_cont.job_title contact_job_title,
         org_cont.job_title_code contact_job_title_code,
         rel_party.address1 contact_address1,
         rel_party.address2 contact_address2,
         rel_party.address3 contact_address3,
         rel_party.address4 contact_address4,
         rel_party.country contact_country,
         rel_party.state contact_state,
         rel_party.city contact_city,
         rel_party.county contact_county,
         rel_party.postal_code contact_postal_code
  FROM   hz_contact_points cont_point,
         hz_cust_account_roles acct_role,
         hz_parties party,
         hz_parties rel_party,
         hz_relationships rel,
         hz_org_contacts org_cont,
         hz_cust_accounts role_acct,
         hz_contact_restrictions cont_res,
         hz_person_language per_lang,
         hz_cust_acct_sites_all hcasa
 WHERE       acct_role.party_id = rel.party_id
         AND acct_role.role_type = 'CONTACT'
         AND org_cont.party_relationship_id = rel.relationship_id
         AND rel.subject_id = party.party_id
         AND rel_party.party_id = rel.party_id
         AND cont_point.owner_table_id(+) = rel_party.party_id
         AND cont_point.contact_point_type(+) = 'EMAIL'
         AND cont_point.primary_flag(+) = 'Y'
         AND acct_role.cust_account_id = role_acct.cust_account_id
         AND role_acct.party_id = rel.object_id
         AND party.party_id = per_lang.party_id(+)
         AND per_lang.native_language(+) = 'Y'
         AND party.party_id = cont_res.subject_id(+)
         AND cont_res.subject_table(+) = 'HZ_PARTIES'
         AND role_acct.cust_account_id = hcasa.cust_account_id
         AND hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
--  AND   acct_role.cust_account_id                        =  p_customer_id --3177
--  AND   acct_role.cust_acct_site_id                      =  p_address_id
--  AND   hcasa.org_id                                     =  90