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;
No comments:
Post a Comment