[Home] [Help]
| Product: | AMW - Internal Controls Manager (Obsolete) |
|---|---|
| Description: | view based on per_all_people_f, per_all_assignments_f, per_periods_of_service |
| Implementation/DBA Data: | Not implemented in this database |
SELECT P.BUSINESS_GROUP_ID
, A.ORGANIZATION_ID
, P.PERSON_ID
, A.ASSIGNMENT_ID
, P.LAST_UPDATE_DATE
, P.LAST_UPDATED_BY
, P.LAST_UPDATE_LOGIN
, P.CREATION_DATE
, P.CREATED_BY
, P.EMPLOYEE_NUMBER
, P.FULL_NAME
, P.FIRST_NAME
, P.MIDDLE_NAMES
, P.LAST_NAME
, P.TITLE
, A.LOCATION_ID
, A.SUPERVISOR_ID
, B.ACTUAL_TERMINATION_DATE
, P.EMAIL_ADDRESS
, P.PARTY_ID
FROM PER_ALL_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_SERVICE B
WHERE P.PERSON_ID = A.PERSON_ID
AND P.PARTY_ID IS NOT NULL
AND A.PRIMARY_FLAG = 'Y'
AND (A.ASSIGNMENT_TYPE = 'E' OR A.ASSIGNMENT_TYPE = 'C')
AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID (+)
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND (B.ACTUAL_TERMINATION_DATE>= TRUNC(SYSDATE) OR B.ACTUAL_TERMINATION_DATE IS NULL)