View: PON_EMPLOYEES_CURRENT_V

Product:	PON - Sourcing
Description:	PON_EMPLOYEES_CURRENT_V is a view that provides details of current employee. Each row includes specific information about the current employees such full name, first name, middle names, employee number, person id, effective period, etc.
Implementation/DBA Data:	APPS.PON_EMPLOYEES_CURRENT_V

View Text

SELECT P.FULL_NAME
, P.FIRST_NAME
, P.LAST_NAME
, P.MIDDLE_NAMES
, P.EMPLOYEE_NUMBER
, P.PERSON_ID
, P.BUSINESS_GROUP_ID
, P.EFFECTIVE_START_DATE
, P.EFFECTIVE_END_DATE
, P.EMAIL_ADDRESS
, H.PHONE_NUMBER
, S.NAME POSITION
, A.ORGANIZATION_ID
, J.NAME JOB_TITLE
FROM PER_ALL_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_SERVICE B
, PER_ALL_POSITIONS S
, PER_PHONES H
, PER_JOBS J
WHERE DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, P.PERSON_ID
, P.PERSON_TYPE_ID
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER)) = 'TRUE'
AND P.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND A.PERSON_ID = P.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE = 'E'
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID
AND (B.ACTUAL_TERMINATION_DATE>= TRUNC(SYSDATE) OR B.ACTUAL_TERMINATION_DATE IS NULL)
AND A.POSITION_ID = S.POSITION_ID (+)
AND A.JOB_ID = J.JOB_ID (+)
AND H.PARENT_ID (+) = P.PERSON_ID
AND H.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND H.PHONE_TYPE (+) = 'W1'
AND NVL(H.DATE_FROM(+)
, TRUNC(SYSDATE)) <= TRUNC(SYSDATE)
AND NVL(H.DATE_TO(+)
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE) UNION ALL SELECT P.FULL_NAME
, P.FIRST_NAME
, P.LAST_NAME
, P.MIDDLE_NAMES
, P.NPW_NUMBER AS EMPLOYEE_NUMBER
, P.PERSON_ID
, P.BUSINESS_GROUP_ID
, P.EFFECTIVE_START_DATE
, P.EFFECTIVE_END_DATE
, P.EMAIL_ADDRESS
, H.PHONE_NUMBER
, S.NAME AS POSITION
, A.ORGANIZATION_ID
, J.NAME AS JOB_TITLE
FROM PER_ALL_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_PLACEMENT PP
, PER_ALL_POSITIONS S
, PER_PHONES H
, PER_JOBS J
WHERE DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, P.PERSON_ID
, P.PERSON_TYPE_ID
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER)) = 'TRUE'
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE AND A.PERSON_ID = P.PERSON_ID AND A.PRIMARY_FLAG ='Y' AND A.ASSIGNMENT_TYPE = 'C' AND A.JOB_ID = J.JOB_ID (+) AND H.PARENT_ID (+) = P.PERSON_ID AND H.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F' AND H.PHONE_TYPE (+) = 'W1' AND NVL(H.DATE_FROM(+)
, TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND NVL(H.DATE_TO(+)
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE) AND A.PERSON_ID = PP.PERSON_ID AND A.PERIOD_OF_PLACEMENT_DATE_START= PP.DATE_START AND (PP.ACTUAL_TERMINATION_DATE>= TRUNC(SYSDATE) OR PP.ACTUAL_TERMINATION_DATE IS NULL) AND P.NPW_NUMBER IS NOT NULL
AND A.POSITION_ID = S.POSITION_ID (+)

Columns