1. Tables and Views for Financials
  2. JE_RU_FA_EMPLOYEE_V

JE_RU_FA_EMPLOYEE_V

Details

  • Schema: FUSION

  • Object owner: JE

  • Object type: VIEW

Columns

Name

PERSON_ID

PERSON_NUMBER

LIST_NAME

Query

SQL_Statement

SELECT PersonPEO.PERSON_ID,

PersonDPEO.PERSON_NUMBER,

PersonNameDPEO.LIST_NAME

FROM PER_PERSONS PersonPEO,

PER_PERSON_NAMES_F_V PersonNameDPEO,

PER_EMAIL_ADDRESSES EmailAddressPEO,

PER_DISPLAY_PHONES_V PhonePEO,

PER_PERSON_TYPES_VL PersonTypePEO,

PER_PERIODS_OF_SERVICE PeriodOfServicePEO,

PER_ALL_ASSIGNMENTS_M AssignmentDPEO,

PER_ASSIGNMENT_STATUS_TYPES_VL AssignmentStatusTypePEO,

HCM_LOOKUPS HcmLookupPEO,

PER_JOBS_F_VL JobDPEO,

PER_DEPARTMENTS DepartmentDPEO,

HR_ALL_ORGANIZATION_UNITS_F_VL LegalEntityDPEO,

HR_ALL_POSITIONS_F_VL PositionDPEO,

PER_GRADES_F_VL GradeDPEO,

PER_ASSIGNMENT_SUPERVISORS_F AssignmentSupervisorDPEO,

PER_PERSON_NAMES_F_V SupervisorNameDPEO,

HR_LOCATIONS_ALL_F_VL LocationDPEO,

PER_ALL_PEOPLE_F PersonDPEO

WHERE PersonPEO.PERSON_ID = PersonDPEO.PERSON_ID

AND TRUNC (SYSDATE) BETWEEN PersonDPEO.EFFECTIVE_START_DATE AND PersonDPEO.EFFECTIVE_END_DATE

AND EmailAddressPEO.EMAIL_ADDRESS_ID(+) = PersonDPEO.PRIMARY_EMAIL_ID

AND PhonePEO.PHONE_ID(+) = PersonDPEO.PRIMARY_PHONE_ID

AND PersonPEO.PERSON_ID = PersonNameDPEO.PERSON_ID

AND TRUNC (SYSDATE) BETWEEN PersonNameDPEO.EFFECTIVE_START_DATE AND PersonNameDPEO.EFFECTIVE_END_DATE

AND PersonPEO.PERSON_ID = AssignmentDPEO.PERSON_ID(+)

AND AssignmentDPEO.WORK_TERMS_ASSIGNMENT_ID(+) IS NOT NULL

AND TRUNC (SYSDATE) BETWEEN AssignmentDPEO.EFFECTIVE_START_DATE AND AssignmentDPEO.EFFECTIVE_END_DATE

AND PersonTypePEO.PERSON_TYPE_ID(+) = AssignmentDPEO.PERSON_TYPE_ID

AND AssignmentDPEO.ASSIGNMENT_STATUS_TYPE_ID = AssignmentStatusTypePEO.ASSIGNMENT_STATUS_TYPE_ID(+)

AND PeriodOfServicePEO.PERIOD_OF_SERVICE_ID(+) = AssignmentDPEO.PERIOD_OF_SERVICE_ID

AND ( HcmLookupPEO.LOOKUP_TYPE(+) = 'YES_NO' AND HcmLookupPEO.LOOKUP_CODE(+) = AssignmentDPEO.PRIMARY_FLAG)

AND ( JobDPEO.JOB_ID(+) = AssignmentDPEO.JOB_ID

AND TRUNC (SYSDATE) BETWEEN JobDPEO.EFFECTIVE_START_DATE(+) AND JobDPEO.EFFECTIVE_END_DATE(+))

AND ( DepartmentDPEO.ORGANIZATION_ID(+) = AssignmentDPEO.ORGANIZATION_ID

AND TRUNC (SYSDATE) BETWEEN DepartmentDPEO.EFFECTIVE_START_DATE(+) AND DepartmentDPEO.EFFECTIVE_END_DATE(+))

AND ( LegalEntityDPEO.ORGANIZATION_ID(+) = AssignmentDPEO.LEGAL_ENTITY_ID

AND TRUNC (SYSDATE) BETWEEN LegalEntityDPEO.EFFECTIVE_START_DATE(+) AND LegalEntityDPEO.EFFECTIVE_END_DATE(+))

AND ( PositionDPEO.POSITION_ID(+) = AssignmentDPEO.POSITION_ID

AND TRUNC (SYSDATE) BETWEEN PositionDPEO.EFFECTIVE_START_DATE(+) AND PositionDPEO.EFFECTIVE_END_DATE(+))

AND ( GradeDPEO.GRADE_ID(+) = AssignmentDPEO.GRADE_ID

AND TRUNC (SYSDATE) BETWEEN GradeDPEO.EFFECTIVE_START_DATE(+) AND GradeDPEO.EFFECTIVE_END_DATE(+))

AND ( AssignmentSupervisorDPEO.ASSIGNMENT_ID(+) = AssignmentDPEO.ASSIGNMENT_ID

AND TRUNC (SYSDATE) BETWEEN AssignmentSupervisorDPEO.EFFECTIVE_START_DATE(+) AND AssignmentSupervisorDPEO.EFFECTIVE_END_DATE(+))

AND ( AssignmentSupervisorDPEO.MANAGER_ID = SupervisorNameDPEO.PERSON_ID(+)

AND TRUNC (SYSDATE) BETWEEN SupervisorNameDPEO.EFFECTIVE_START_DATE(+) AND SupervisorNameDPEO.EFFECTIVE_END_DATE(+)

AND ( LocationDPEO.LOCATION_ID(+) = AssignmentDPEO.LOCATION_ID

AND TRUNC (SYSDATE) BETWEEN LocationDPEO.EFFECTIVE_START_DATE(+) AND LocationDPEO.EFFECTIVE_END_DATE(+))

AND AssignmentSupervisorDPEO.PRIMARY_FLAG(+) = 'Y')

AND NVL (PeriodOfServicePEO.ACTUAL_TERMINATION_DATE, SYSDATE) >= SYSDATE

AND AssignmentDPEO.PRIMARY_FLAG = 'Y'

AND PersonTypePEO.SYSTEM_PERSON_TYPE = 'EMP'