Search This Blog

Wednesday, April 3, 2013

Query To Fetch Customer Account/Site Contact Details in R12


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