WSH_CARRIER_CONTACTS

Details

Schema: FUSION
Object owner: RCS
Object type: VIEW

Columns

Name
CARRIER_ID RELATIONSHIP_REC_ID RELATIONSHIP_ID ORG_CONTACT_ID ORG_JOB_TITLE PERSON_NUMBER PERSON_FIRST_NAME PERSON_LAST_NAME PERSON_ID PROFILE_ID EMAIL_CONTACT_POINT_ID EMAIL_ADDRESS PHONE_CONTACT_POINT_ID PHONE_NUMBER CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN LAST_UPDATE_DATE

Name

CARRIER_ID

RELATIONSHIP_REC_ID

RELATIONSHIP_ID

ORG_CONTACT_ID

ORG_JOB_TITLE

PERSON_NUMBER

PERSON_FIRST_NAME

PERSON_LAST_NAME

PERSON_ID

PROFILE_ID

EMAIL_CONTACT_POINT_ID

EMAIL_ADDRESS

PHONE_CONTACT_POINT_ID

PHONE_NUMBER

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

LAST_UPDATE_DATE

Query

SQL_Statement
select wc.carrier_id as carrier_id, hr.relationship_rec_id as relationship_rec_id, hr.relationship_id as relationship_id, hoc.org_contact_id as org_contact_id, hoc.job_title as org_job_title, hp_person.party_number as person_number, hp_profile.person_first_name as person_first_name, hp_profile.person_last_name as person_last_name, hp_person.party_id as person_id, hp_profile.person_profile_id as profile_id, hcp_email.contact_point_id as email_contact_point_id, hcp_email.email_address as email_address, hcp_phone.contact_point_id as phone_contact_point_id, hcp_phone.phone_number as phone_number, hp_profile.created_by, hp_profile.creation_date, hp_profile.last_updated_by, hp_profile.last_update_login, hp_profile.last_update_date from wsh_carriers wc, hz_relationships hr, hz_org_contacts hoc, hz_parties hp_person, hz_person_profiles hp_profile, hz_contact_points hcp_email, hz_contact_points hcp_phone where wc.carrier_id = hr.object_id and hr.relationship_code = 'CONTACT_OF' and hr.object_type = 'ORGANIZATION' and hr.subject_table_name = 'HZ_PARTIES' and hr.subject_type = 'PERSON' and hr.object_table_name = 'HZ_PARTIES' and hoc.party_relationship_id(+) = hr.relationship_id and hr.subject_id = hp_person.party_id and hp_person.party_id = hp_profile.party_id and hcp_email.owner_table_id(+) = hp_person.party_id and hcp_phone.owner_table_id(+) = hp_person.party_id and hr.status = 'A' and hp_person.status = 'A' and (hcp_email.relationship_id = hr.relationship_id OR hcp_email.contact_point_id IS NULL) and hcp_email.status(+) = 'A' and hcp_email.contact_point_type(+) = 'EMAIL' and (hcp_phone.relationship_id = hr.relationship_id OR hcp_phone.contact_point_id IS NULL) and hcp_phone.status(+) = 'A' and hcp_phone.contact_point_type(+) = 'PHONE' and TRUNC(sysdate) between hp_profile.effective_start_date and hp_profile.effective_end_date and hp_profile.actual_content_source = 'SST' and hcp_email.primary_flag(+) = 'Y' and hcp_phone.primary_flag(+) = 'Y'

SQL_Statement

select wc.carrier_id as carrier_id,

hr.relationship_rec_id as relationship_rec_id,

hr.relationship_id as relationship_id,

hoc.org_contact_id as org_contact_id,

hoc.job_title as org_job_title,

hp_person.party_number as person_number,

hp_profile.person_first_name as person_first_name,

hp_profile.person_last_name as person_last_name,

hp_person.party_id as person_id,

hp_profile.person_profile_id as profile_id,

hcp_email.contact_point_id as email_contact_point_id,

hcp_email.email_address as email_address,

hcp_phone.contact_point_id as phone_contact_point_id,

hcp_phone.phone_number as phone_number,

hp_profile.created_by,

hp_profile.creation_date,

hp_profile.last_updated_by,

hp_profile.last_update_login,

hp_profile.last_update_date

from wsh_carriers wc,

hz_relationships hr,

hz_org_contacts hoc,

hz_parties hp_person,

hz_person_profiles hp_profile,

hz_contact_points hcp_email,

hz_contact_points hcp_phone

where

wc.carrier_id = hr.object_id

and hr.relationship_code = 'CONTACT_OF'

and hr.object_type = 'ORGANIZATION'

and hr.subject_table_name = 'HZ_PARTIES'

and hr.subject_type = 'PERSON'

and hr.object_table_name = 'HZ_PARTIES'

and hoc.party_relationship_id(+) = hr.relationship_id

and hr.subject_id = hp_person.party_id

and hp_person.party_id = hp_profile.party_id

and hcp_email.owner_table_id(+) = hp_person.party_id

and hcp_phone.owner_table_id(+) = hp_person.party_id

and hr.status = 'A'

and hp_person.status = 'A'

and (hcp_email.relationship_id = hr.relationship_id OR hcp_email.contact_point_id IS NULL)

and hcp_email.status(+) = 'A'

and hcp_email.contact_point_type(+) = 'EMAIL'

and (hcp_phone.relationship_id = hr.relationship_id OR hcp_phone.contact_point_id IS NULL)

and hcp_phone.status(+) = 'A'

and hcp_phone.contact_point_type(+) = 'PHONE'

and TRUNC(sysdate) between hp_profile.effective_start_date and hp_profile.effective_end_date

and hp_profile.actual_content_source = 'SST'

and hcp_email.primary_flag(+) = 'Y'

and hcp_phone.primary_flag(+) = 'Y'