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

Friday, September 14, 2012 by Team search · 5

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.