OKC_SIGNER_CONTACTS

Details

Schema: FUSION
Object owner: OKC
Object type: VIEW

Columns

Name
ID CONTACT_ID CONTACT_NAME EMAIL_ADDRESS JOB_TITLE USERTYPE ACCESS_LEVEL OWNER_YN SIGNER_YN CONTRACT_ID MAJOR_VERSION PERSON_ID INTERNAL_YN USER_GUID CRO_CODE USERNAME DEPARTMENT PARTY_ID FIRST_NAME LAST_NAME SIGNER_SEQUENCE SIGNATURE_ROLE ROUTING_SEQUENCE PRIMARY_EMAIL_ADDRESS

Name

ID

CONTACT_ID

CONTACT_NAME

EMAIL_ADDRESS

JOB_TITLE

USERTYPE

ACCESS_LEVEL

OWNER_YN

SIGNER_YN

CONTRACT_ID

MAJOR_VERSION

PERSON_ID

INTERNAL_YN

USER_GUID

CRO_CODE

USERNAME

DEPARTMENT

PARTY_ID

FIRST_NAME

LAST_NAME

SIGNER_SEQUENCE

SIGNATURE_ROLE

ROUTING_SEQUENCE

PRIMARY_EMAIL_ADDRESS

Query

SQL_Statement
SELECT C.iD ID,PersonNameDPEO.PERSON_ID AS CONTACT_ID, PersonNameDPEO.DISPLAY_NAME AS CONTACT_NAME, EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS, JobDPEO.NAME AS JOB_TITLE, C.JTOT_OBJECT1_CODE as UserType, C.ACCESS_LEVEL as ACCESS_LEVEL , C.OWNER_YN, C.SIGNER_YN, dnz_chr_id as CONTRACT_ID, major_version, PersonNameDPEO.PERSON_ID AS PERSON_ID, 'Y' internal_yn, USER_GUID, C.cro_code, U.username, DepartmentDPEO.NAME AS Department, h.owner_table_id as PARTY_ID, PersonNameDPEO.first_name as first_name, PersonNameDPEO.last_name as last_name, c.signer_sequence, c.signature_role, c.routing_sequence, NVL((SELECT EmailAddressPEO1.EMAIL_ADDRESS FROM Per_all_people_f,PER_EMAIL_ADDRESSES EmailAddressPEO1 WHERE EmailAddressPEO1.Email_address_Id = Per_all_people_f.Primary_Email_Id AND Per_all_people_f.Person_Id = PersonNameDPEO.person_id AND trunc(SYSDATE) BETWEEN Per_all_people_f.effective_start_date(+) AND Per_all_people_f.effective_end_date(+) AND ROWNUM < 2),EmailAddressPEO.EMAIL_ADDRESS ) AS primary_email_address FROM PER_PERSON_NAMES_F_V PersonNameDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO, PER_ALL_ASSIGNMENTS_M AssignmentDPEO, PER_JOBS_F_VL JobDPEO, okc_contacts c, HZ_ORIG_SYS_REFERENCES h,per_users u,PER_DEPARTMENTS DepartmentDPEO WHERE c.jtot_object1_code='OKX_RESOURCE' AND c.object1_id1 = h.owner_table_id AND h.orig_system_reference = PersonNameDPEO.person_id AND h.orig_system = 'FUSION_HCM' AND h.owner_table_name = 'HZ_PARTIES' AND PersonNameDPEO.person_id = EmailAddressPEO.person_id(+) AND AssignmentDPEO.person_id = PersonNameDPEO.person_id AND DepartmentDPEO.organization_id(+) = AssignmentDPEO.organization_id AND AssignmentDPEO.primary_flag ='Y' AND AssignmentDPEO.assignment_type IN ('E', 'C') AND JobDPEO.job_id(+) = AssignmentDPEO.job_id AND trunc(SYSDATE) BETWEEN JobDPEO.effective_start_date(+) AND JobDPEO.effective_end_date(+) AND trunc(SYSDATE) BETWEEN AssignmentDPEO.effective_start_date AND AssignmentDPEO.effective_end_date AND trunc(sysdate) between PersonNameDPEO.EFFECTIVE_START_DATE and PersonNameDPEO.EFFECTIVE_END_DATE AND trunc(SYSDATE) BETWEEN DepartmentDPEO.effective_start_date(+) AND DepartmentDPEO.effective_end_date(+) AND c.Version_type = 'C' AND C.ACCESS_LEVEL IN ('FULL','READ','NO_ACCESS') AND U.person_id = PersonNameDPEO.person_id UNION ALL SELECT c.ID, r.RELATIONSHIP_ID as CONTACT_ID, c1.PARTY_NAME as contact_name, nvl(EmailPEO.EMAIL_ADDRESS,c1.email_address) email_address, OrganizationContactPEO.JOB_TITLE, c.jtot_object1_code as USER_TYPE, C.ACCESS_LEVEL as ACCESS_LEVEL , C.OWNER_YN, C.SIGNER_YN, dnz_chr_id as CONTRACT_ID, major_version, p.party_id AS PERSON_ID, 'N' internal_yn, NULL User_guid, C.cro_code, NULL Username, NULL Department, p.party_id as PARTY_ID, c1.person_first_name as first_name, c1.person_last_name as last_name, c.signer_sequence, c.signature_role, c.routing_sequence, nvl(EmailPEO.EMAIL_ADDRESS,c1.email_address) primary_email_address FROM HZ_RELATIONSHIPS r, HZ_PARTIES p, HZ_PARTIES c1, HZ_ORG_CONTACTS OrganizationContactPEO, HZ_CONTACT_POINTS EmailPEO, OKC_CONTACTS c WHERE p.party_id= r.object_id and c1.party_id=r.subject_id and r.object_type IN ('ORGANIZATION' ,'PERSON') and r.relationship_code='CONTACT_OF' and r.subject_type='PERSON' and r.subject_table_name = 'HZ_PARTIES' and r.object_table_name = 'HZ_PARTIES' and r.relationship_type = 'CONTACT' and r.RELATIONSHIP_ID = OrganizationContactPEO.PARTY_RELATIONSHIP_ID(+) AND EmailPEO.owner_table_id(+) = r.subject_id AND EmailPEO.RELATIONSHIP_ID(+) = r.RELATIONSHIP_ID AND EmailPEO.owner_table_name(+) = 'HZ_PARTIES' AND EmailPEO.contact_point_type(+) ='EMAIL' AND EmailPEO.primary_flag(+) = 'Y' AND EmailPEO.status(+) = 'A' AND c.jtot_object1_code in ( 'OKX_PCONTACT', 'OKX_VCONTACT') AND c.Version_type = 'C' AND r.RELATIONSHIP_ID = c.object1_id1 UNION ALL select c.ID, p.party_id as CONTACT_ID, p.PARTY_NAME AS contact_name, NVL (EmailPEO.EMAIL_ADDRESS, p.email_address) email_address, NULL, c.jtot_object1_code AS USER_TYPE, C.ACCESS_LEVEL as ACCESS_LEVEL , C.OWNER_YN, C.SIGNER_YN, c.dnz_chr_id AS CONTRACT_ID, c.major_version, p.party_id AS PERSON_ID, 'N' internal_yn, NULL User_guid, C.cro_code, NULL Username, NULL Department, p.party_id AS PARTY_ID, p.person_first_name AS first_name, p.person_last_name AS last_name, c.signer_sequence, c.signature_role, c.routing_sequence, NVL (EmailPEO.EMAIL_ADDRESS, p.email_address) primary_email_address FROM HZ_PARTIES p, HZ_CONTACT_POINTS EmailPEO, OKC_CONTACTS c WHERE c.jtot_object1_code = 'OKX_PERSON' AND c.object1_id1 = p.party_id AND EmailPEO.owner_table_id(+) = p.party_id AND EmailPEO.owner_table_name(+) = 'HZ_PARTIES' AND EmailPEO.contact_point_type(+) = 'EMAIL' AND EmailPEO.primary_flag(+) = 'Y' AND EmailPEO.status(+) = 'A'

SQL_Statement

SELECT C.iD ID,PersonNameDPEO.PERSON_ID AS CONTACT_ID,

PersonNameDPEO.DISPLAY_NAME AS CONTACT_NAME,

EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS,

JobDPEO.NAME AS JOB_TITLE,

C.JTOT_OBJECT1_CODE as UserType,

C.ACCESS_LEVEL as ACCESS_LEVEL ,

C.OWNER_YN,

C.SIGNER_YN,

dnz_chr_id as CONTRACT_ID,

major_version,

PersonNameDPEO.PERSON_ID AS PERSON_ID,

'Y' internal_yn,

USER_GUID,

C.cro_code,

U.username,

DepartmentDPEO.NAME AS Department,

h.owner_table_id as PARTY_ID,

PersonNameDPEO.first_name as first_name,

PersonNameDPEO.last_name as last_name,

c.signer_sequence,

c.signature_role,

c.routing_sequence,

NVL((SELECT EmailAddressPEO1.EMAIL_ADDRESS

FROM Per_all_people_f,PER_EMAIL_ADDRESSES EmailAddressPEO1

WHERE EmailAddressPEO1.Email_address_Id =

Per_all_people_f.Primary_Email_Id

AND Per_all_people_f.Person_Id = PersonNameDPEO.person_id

AND trunc(SYSDATE) BETWEEN

Per_all_people_f.effective_start_date(+) AND

Per_all_people_f.effective_end_date(+) AND ROWNUM <

2),EmailAddressPEO.EMAIL_ADDRESS ) AS primary_email_address

FROM PER_PERSON_NAMES_F_V PersonNameDPEO, PER_EMAIL_ADDRESSES

EmailAddressPEO, PER_ALL_ASSIGNMENTS_M AssignmentDPEO, PER_JOBS_F_VL

JobDPEO,

okc_contacts c, HZ_ORIG_SYS_REFERENCES h,per_users u,PER_DEPARTMENTS DepartmentDPEO

WHERE c.jtot_object1_code='OKX_RESOURCE'

AND c.object1_id1 = h.owner_table_id

AND h.orig_system_reference = PersonNameDPEO.person_id

AND h.orig_system = 'FUSION_HCM'

AND h.owner_table_name = 'HZ_PARTIES'

AND PersonNameDPEO.person_id = EmailAddressPEO.person_id(+)

AND AssignmentDPEO.person_id = PersonNameDPEO.person_id

AND DepartmentDPEO.organization_id(+) = AssignmentDPEO.organization_id

AND AssignmentDPEO.primary_flag ='Y'

AND AssignmentDPEO.assignment_type IN ('E', 'C')

AND JobDPEO.job_id(+) = AssignmentDPEO.job_id

AND trunc(SYSDATE) BETWEEN JobDPEO.effective_start_date(+) AND

JobDPEO.effective_end_date(+)

AND trunc(SYSDATE) BETWEEN AssignmentDPEO.effective_start_date AND

AssignmentDPEO.effective_end_date

AND trunc(sysdate) between PersonNameDPEO.EFFECTIVE_START_DATE and

PersonNameDPEO.EFFECTIVE_END_DATE

AND trunc(SYSDATE) BETWEEN DepartmentDPEO.effective_start_date(+) AND DepartmentDPEO.effective_end_date(+)

AND c.Version_type = 'C'

AND C.ACCESS_LEVEL IN ('FULL','READ','NO_ACCESS')

AND U.person_id = PersonNameDPEO.person_id

UNION ALL

SELECT c.ID, r.RELATIONSHIP_ID as CONTACT_ID,

c1.PARTY_NAME as contact_name,

nvl(EmailPEO.EMAIL_ADDRESS,c1.email_address) email_address,

OrganizationContactPEO.JOB_TITLE,

c.jtot_object1_code as USER_TYPE,

C.ACCESS_LEVEL as ACCESS_LEVEL ,

C.OWNER_YN,

C.SIGNER_YN,

dnz_chr_id as CONTRACT_ID,

major_version,

p.party_id AS PERSON_ID,

'N' internal_yn,

NULL User_guid,

C.cro_code,

NULL Username,

NULL Department,

p.party_id as PARTY_ID,

c1.person_first_name as first_name,

c1.person_last_name as last_name,

c.signer_sequence,

c.signature_role,

c.routing_sequence,

nvl(EmailPEO.EMAIL_ADDRESS,c1.email_address) primary_email_address

FROM HZ_RELATIONSHIPS r, HZ_PARTIES p, HZ_PARTIES c1,

HZ_ORG_CONTACTS OrganizationContactPEO, HZ_CONTACT_POINTS EmailPEO,

OKC_CONTACTS c

WHERE p.party_id= r.object_id and

c1.party_id=r.subject_id and

r.object_type IN ('ORGANIZATION' ,'PERSON') and

r.relationship_code='CONTACT_OF' and

r.subject_type='PERSON' and

r.subject_table_name = 'HZ_PARTIES' and

r.object_table_name = 'HZ_PARTIES' and

r.relationship_type = 'CONTACT' and

r.RELATIONSHIP_ID =

OrganizationContactPEO.PARTY_RELATIONSHIP_ID(+) AND

EmailPEO.owner_table_id(+) = r.subject_id AND

EmailPEO.RELATIONSHIP_ID(+) = r.RELATIONSHIP_ID AND

EmailPEO.owner_table_name(+) = 'HZ_PARTIES' AND

EmailPEO.contact_point_type(+) ='EMAIL' AND

EmailPEO.primary_flag(+) = 'Y' AND

EmailPEO.status(+) = 'A' AND

c.jtot_object1_code in ( 'OKX_PCONTACT', 'OKX_VCONTACT')

AND c.Version_type = 'C'

AND r.RELATIONSHIP_ID = c.object1_id1

UNION ALL

select c.ID,

p.party_id as CONTACT_ID,

p.PARTY_NAME AS contact_name,

NVL (EmailPEO.EMAIL_ADDRESS, p.email_address) email_address,

NULL,

c.jtot_object1_code AS USER_TYPE,

C.ACCESS_LEVEL as ACCESS_LEVEL ,

C.OWNER_YN,

C.SIGNER_YN,

c.dnz_chr_id AS CONTRACT_ID,

c.major_version,

p.party_id AS PERSON_ID,

'N' internal_yn,

NULL User_guid,

C.cro_code,

NULL Username,

NULL Department,

p.party_id AS PARTY_ID,

p.person_first_name AS first_name,

p.person_last_name AS last_name,

c.signer_sequence,

c.signature_role,

c.routing_sequence,

NVL (EmailPEO.EMAIL_ADDRESS, p.email_address) primary_email_address

FROM HZ_PARTIES p,

HZ_CONTACT_POINTS EmailPEO,

OKC_CONTACTS c

WHERE c.jtot_object1_code = 'OKX_PERSON'

AND c.object1_id1 = p.party_id

AND EmailPEO.owner_table_id(+) = p.party_id

AND EmailPEO.owner_table_name(+) = 'HZ_PARTIES'

AND EmailPEO.contact_point_type(+) = 'EMAIL'

AND EmailPEO.primary_flag(+) = 'Y'

AND EmailPEO.status(+) = 'A'