1. Tables and Views for Sales and Fusion Service
  2. OKC_EMPLOYEE_CONTACT_V

OKC_EMPLOYEE_CONTACT_V

Details

  • Schema: FUSION

  • Object owner: OKC

  • Object type: VIEW

Columns

Name

CONTACT_ID

CONTACT_NAME

EMAIL_ADDRESS

STATUS

PARTY_ID

PARTY_NAME

BUSINESS_GROUP_ID

PERSON_NAME_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

EMAIL_ADDRESS_ID

DEPARTMENT

JOB_TITLE

Query

SQL_Statement

SELECT PersonNameDPEO.PERSON_ID AS CONTACT_ID,

PersonNameDPEO.FULL_NAME AS CONTACT_NAME,

EmailAddressPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS,

'A' as STATUS,

TO_NUMBER(NULL) AS PARTY_ID,

NULL AS PARTY_NAME,

PersonNameDPEO.BUSINESS_GROUP_ID,

PersonNameDPEO.PERSON_NAME_ID,

PersonNameDPEO.EFFECTIVE_START_DATE,

PersonNameDPEO.EFFECTIVE_END_DATE,

EmailAddressPEO.EMAIL_ADDRESS_ID,

DepartmentDPEO.NAME AS Department,

JobDPEO.NAME AS JOB_TITLE

FROM PER_PERSON_NAMES_F_V PersonNameDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO, PER_ALL_ASSIGNMENTS_M AssignmentDPEO, PER_DEPARTMENTS DepartmentDPEO, PER_JOBS_F_VL JobDPEO

WHERE 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