Friday, September 14, 2012

Query To Fetch Customer Account/Site Contact Details in R12



Queries to Fetch Customer Contact Details


SELECT hr.relationship_id,
       hr.subject_id,
       hr.subject_type,
       hr.subject_table_name,
       hr.object_id,
       hr.object_type,
       hr.object_table_name,
       hr.party_id rel_party_id,
       hr.relationship_code,
       hr.comments rel_comments,
       hr.start_date,
       hr.end_date,
       hr.status rel_status,
       hr.relationship_type,
       hr.created_by_module rel_created_by_module,
       hr.percentage_ownership,
       hr.content_source_type rel_content_source_type,
       hr.actual_content_source rel_actual_content_source,
       rel_hp.party_number rel_party_number,
       hoc.org_contact_id,
       hoc.party_relationship_id,
       hoc.comments org_cont_comments,
       hoc.contact_number,
       hoc.department_code,
       hoc.department,
       hoc.title,
       hoc.job_title,
       hoc.mail_stop,
       hoc.decision_maker_flag,
       hoc.job_title_code,
       hoc.reference_use_flag,
       hoc.RANK,
       hcar.cust_account_role_id,
       hcar.party_id acct_role_party_id,
       hcar.cust_account_id acct_role_cust_account_id,
       hcar.cust_acct_site_id acct_role_cust_acct_site_id,
       hcar.primary_flag acct_role_primary_flag,
       hcar.role_type,
       hcar.source_code acct_role_source_code,
       hcar.status acct_role_status,
       hcar.created_by_module acct_role_created_by_module,
       hp.party_id,
       hp.party_number,
       hp.party_name,
       hp.party_type,
       hp.validated_flag,
       hp.salutation par_salutation,
       hp.status par_status,
       hpp.person_profile_id,
       hpp.party_id per_party_id,
       hpp.person_name,
       hpp.person_pre_name_adjunct,
       hpp.person_first_name,
       hpp.person_middle_name,
       hpp.person_last_name,
       hpp.person_name_suffix,
       hpp.person_title,
       hpp.person_academic_title,
       hpp.person_previous_last_name,
       hpp.person_initials,
       hpp.known_as per_known_as,
       hpp.person_name_phonetic,
       hpp.person_first_name_phonetic,
       hpp.person_last_name_phonetic,
       hpp.tax_reference per_tax_reference,
       hpp.jgzz_fiscal_code per_jgzz_fiscal_code,
       hpp.person_iden_type,
       hpp.person_identifier,
       hpp.date_of_birth,
       hpp.place_of_birth,
       hpp.date_of_death,
       hpp.gender,
       hpp.declared_ethnicity,
       hpp.marital_status,
       hpp.marital_status_effective_date,
       hpp.personal_income,
       hpp.head_of_household_flag,
       hpp.household_income,
       hpp.household_size,
       hpp.rent_own_ind per_rent_own_ind,
       hpp.last_known_gps,
       hpp.known_as2 per_known_as2,
       hpp.known_as3 per_known_as3,
       hpp.known_as4 per_known_as4,
       hpp.known_as5 per_known_as5,
       hpp.middle_name_phonetic,
       hpp.created_by_module per_created_by_module,
       hpp.actual_content_source per_actual_content_source,
       hpp.internal_flag internal_flag
  FROM hz_parties hp,
       hz_parties rel_hp,
       hz_person_profiles hpp,
       hz_relationships hr,
       hz_org_contacts hoc,
       hz_cust_account_roles hcar
 WHERE hoc.party_relationship_id = hr.relationship_id
   AND hr.subject_id             = hp.party_id
   AND rel_hp.party_id           = hr.party_id
   AND hp.party_id               = hpp.party_id(+)
   AND hpp.content_source_type(+) = user_entered
   AND hpp.effective_end_date IS NULL
   AND rel_hp.party_id           = hcar.party_id(+)
   AND hoc.party_relationship_id = hr.relationship_id
   AND hr.subject_table_name     = 'HZ_PARTIES'
   AND hr.subject_type           = 'PERSON'
   AND hr.relationship_code      = 'CONTACT_OF'
   AND hcar.cust_account_id      = vl_cust_account_id
   AND hcar.cust_acct_site_id    = vl_acct_site_id

5 Responses to “Query To Fetch Customer Account/Site Contact Details in R12”

zcat08 said...
November 18, 2013 at 10:52 AM

-- 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 = '&&Account_Number'
AND p.party_id = c.party_id
AND cp.owner_table_name = 'HZ_PARTIES'
AND cp.owner_table_id = p.party_id;


zcat08 said...
November 18, 2013 at 10:53 AM

--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 = '&&Account_Number'
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;


zcat08 said...
November 18, 2013 at 10:57 AM

-- 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 = '&&Account_Number'
ORDER BY c.cust_account_id DESC;


Anonymous said...
September 24, 2014 at 9:24 AM

THANK YOU zcat08 for tying contacts and contact points together!


Shainee Mangal said...
January 7, 2019 at 6:04 AM

-------------------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';


Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search