OKC_SEARCH_INT_CONTACTS

Details

Schema: FUSION
Object owner: OKC
Object type: VIEW

Columns

Name
CONTACT_NAME EMAIL_ADDRESS DEPARTMENT JOB_TITLE PERSON_ID PARTY_ID OBJECT_ID JTOT_OBJECT_CODE OBJECT2_ID JTOT_OBJECT2_CODE

Name

CONTACT_NAME

EMAIL_ADDRESS

DEPARTMENT

JOB_TITLE

PERSON_ID

PARTY_ID

OBJECT_ID

JTOT_OBJECT_CODE

OBJECT2_ID

JTOT_OBJECT2_CODE

Query

SQL_Statement
SELECT PersonNameDPEO.FULL_NAME AS CONTACT_NAME, EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS, DepartmentDPEO.NAME AS Department, JobDPEO.NAME AS JOB_TITLE, PersonNameDPEO.PERSON_ID as person_id, h.owner_table_id as party_id, PersonNameDPEO.PERSON_ID as object_id, 'OKX_RSCEMP' as jtot_object_code, h.owner_table_id as object2_id, nvl2(h.owner_table_id,'OKX_RESOURCE',null) as jtot_object2_code FROM PER_PERSON_NAMES_F_V PersonNameDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO, PER_ALL_ASSIGNMENTS_M AssignmentDPEO, PER_DEPARTMENTS DepartmentDPEO, PER_JOBS_F_VL JobDPEO, HZ_ORIG_SYS_REFERENCES h WHERE PersonNameDPEO.PERSON_ID = h.orig_system_reference(+) 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 AssignmentDPEO.primary_flag ='Y' AND AssignmentDPEO.assignment_type IN ('E', 'C') AND DepartmentDPEO.organization_id(+) = AssignmentDPEO.organization_id AND JobDPEO.job_id(+) = AssignmentDPEO.job_id AND trunc(SYSDATE) BETWEEN JobDPEO.effective_start_date(+) AND JobDPEO.effective_end_date(+) AND trunc(SYSDATE) BETWEEN DepartmentDPEO.effective_start_date(+) AND DepartmentDPEO.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 UNION ALL SELECT PartyPEO.PARTY_NAME as contact_name, EmailPEO.EMAIL_ADDRESS as email_address, null as department, ResourceRolePEO.ROLE_NAME as job_title, null as person_id, ResourcePEO.PARTY_ID as party_id, ResourcePEO.PARTY_ID as object_id, 'OKX_RESOURCE' as jtot_object_code, null as object2_id, null as jtot_object2_code FROM JTF_RS_RESOURCE_PROFILES ResourcePEO, HZ_PARTIES PartyPEO, HZ_CONTACT_POINTS EmailPEO, JTF_RS_ROLE_RELATIONS ResourceRoleAssignPEO, JTF_RS_ROLES_VL ResourceRolePEO WHERE ResourcePEO.PARTY_ID = PartyPEO.PARTY_ID AND PartyPEO.PRIMARY_EMAIL_CONTACT_PT_ID = EmailPEO.CONTACT_POINT_ID(+) AND ResourcePEO.PARTY_ID = ResourceRoleAssignPEO.ROLE_RESOURCE_ID(+) AND ResourceRoleAssignPEO.ROLE_RESOURCE_TYPE(+) = 'RS_INDIVIDUAL' AND ResourceRoleAssignPEO.ROLE_ID = ResourceRolePEO.ROLE_ID(+) AND ResourceRoleAssignPEO.delete_flag(+)='N' AND NOT EXISTS ( SELECT 'Y' FROM HZ_ORIG_SYS_REFERENCES h WHERE h.owner_table_id = ResourcePEO.PARTY_ID and h.orig_system = 'FUSION_HCM' and h.owner_table_name = 'HZ_PARTIES' )

SQL_Statement

SELECT PersonNameDPEO.FULL_NAME AS CONTACT_NAME,

EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS,

DepartmentDPEO.NAME AS Department,

JobDPEO.NAME AS JOB_TITLE,

PersonNameDPEO.PERSON_ID as person_id,

h.owner_table_id as party_id,

PersonNameDPEO.PERSON_ID as object_id,

'OKX_RSCEMP' as jtot_object_code,

h.owner_table_id as object2_id,

nvl2(h.owner_table_id,'OKX_RESOURCE',null) as jtot_object2_code

FROM PER_PERSON_NAMES_F_V PersonNameDPEO,

PER_EMAIL_ADDRESSES EmailAddressPEO,

PER_ALL_ASSIGNMENTS_M AssignmentDPEO,

PER_DEPARTMENTS DepartmentDPEO,

PER_JOBS_F_VL JobDPEO,

HZ_ORIG_SYS_REFERENCES h

WHERE PersonNameDPEO.PERSON_ID = h.orig_system_reference(+)

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 AssignmentDPEO.primary_flag ='Y'

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

AND DepartmentDPEO.organization_id(+) = AssignmentDPEO.organization_id

AND JobDPEO.job_id(+) = AssignmentDPEO.job_id

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

AND trunc(SYSDATE) BETWEEN DepartmentDPEO.effective_start_date(+) AND DepartmentDPEO.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

UNION ALL

SELECT PartyPEO.PARTY_NAME as contact_name,

EmailPEO.EMAIL_ADDRESS as email_address,

null as department,

ResourceRolePEO.ROLE_NAME as job_title,

null as person_id,

ResourcePEO.PARTY_ID as party_id,

ResourcePEO.PARTY_ID as object_id,

'OKX_RESOURCE' as jtot_object_code,

null as object2_id,

null as jtot_object2_code

FROM JTF_RS_RESOURCE_PROFILES ResourcePEO,

HZ_PARTIES PartyPEO,

HZ_CONTACT_POINTS EmailPEO,

JTF_RS_ROLE_RELATIONS ResourceRoleAssignPEO,

JTF_RS_ROLES_VL ResourceRolePEO

WHERE ResourcePEO.PARTY_ID = PartyPEO.PARTY_ID

AND PartyPEO.PRIMARY_EMAIL_CONTACT_PT_ID = EmailPEO.CONTACT_POINT_ID(+)

AND ResourcePEO.PARTY_ID = ResourceRoleAssignPEO.ROLE_RESOURCE_ID(+)

AND ResourceRoleAssignPEO.ROLE_RESOURCE_TYPE(+) = 'RS_INDIVIDUAL'

AND ResourceRoleAssignPEO.ROLE_ID = ResourceRolePEO.ROLE_ID(+)

AND ResourceRoleAssignPEO.delete_flag(+)='N'

AND NOT EXISTS

( SELECT 'Y'

FROM HZ_ORIG_SYS_REFERENCES h

WHERE h.owner_table_id = ResourcePEO.PARTY_ID

and h.orig_system = 'FUSION_HCM'

and h.owner_table_name = 'HZ_PARTIES'

)