VIEW: APPS.PON_EMPLOYEES_CURRENT_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)