Oracle Apps + Query to get customer contact information

 -- Retrieve Party Contact Points

SELECT cp.*
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_contact_points cp
WHERE
c.account_number = :P_CUST_ACCT_NUM
AND p.party_id = c.party_id
AND cp.owner_table_name = 'HZ_PARTIES'
AND cp.owner_table_id = p.party_id;

-- Site contact point (this is different from site contact)

SELECT p.party_number,
c.cust_account_id,
c.account_number,
cp.*
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_party_sites s,
ar.hz_contact_points cp
WHERE c.account_number = :P_CUST_ACCT_NUM
AND p.party_id = c.party_id
AND p.party_id = s.party_id
AND cp.owner_table_name = 'HZ_PARTY_SITES'
AND cp.owner_table_id = s.party_site_id;

-- Site Contacts with contact points

  SELECT p.party_name,

p.party_id,
p.party_number,
p.party_type,
c.cust_account_id,
c.account_number,
pp.party_name contact_name,
pp.PARTY_ID contact_party_id,
pp.party_type,
r.role_type,
co.phone_country_code,
co.phone_area_code,
co.phone_number,
co.phone_line_type,
co.raw_phone_number,
co.email_address,
co.contact_point_purpose,
co.primary_flag,
co.last_update_date
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_cust_account_roles r,
AR.HZ_PARTIES cp,
ar.hz_contact_points co,
ar.hz_cust_acct_sites_all s, --needed only if you require org_id
ar.hz_parties pp,
AR.hz_relationships rel
WHERE s.cust_account_id = c.cust_account_id
AND p.party_id = c.party_id
AND r.cust_account_id = c.cust_account_id
AND cp.PARTY_ID = r.party_id
AND co.owner_table_name = 'HZ_PARTIES'
AND co.OWNER_TABLE_ID = cp.party_id
AND r.cust_acct_site_id IS NOT NULL
AND r.cust_acct_site_id = s.cust_acct_site_id
AND pp.party_id = rel.subject_id
AND rel.party_id = cp.party_id
AND rel.relationship_code = 'CONTACT_OF'
AND rel.directional_flag = 'F'
AND rel.subject_table_name = 'HZ_PARTIES'
AND c.account_number = :P_CUST_ACCT_NUM
ORDER BY c.cust_account_id DESC;

-- CUSTOMER ACCOUNT CONTACT INFO

select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hcar.cust_acct_site_id
,hca.cust_account_id
from apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hca.party_id = hp.party_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.cust_acct_site_id is null
AND hcar.STATUS like 'A';

--CUSTOMER ACCOUNT SITE CONTACT INFO

select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hps.party_site_id
,hps.PARTY_SITE_NUMBER
from apps.hz_parties hp
,apps.hz_party_sites hps
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hp.party_id = hps.party_id
and hca.party_id = hp.party_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.party_site_id = hps.party_site_id
and hcar.cust_acct_site_id(+) = hcas.cust_acct_site_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.STATUS like 'A';

Regards,

Amar Alam