Friday, September 14, 2012
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
Subscribe to:
Posts (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.