VIEW: APPS.PER_ASSIGNMENTS_V9

Source

View Text - Preformatted

SELECT A.POSITION_ID , A.ASSIGNMENT_TYPE , PP.PERSON_ID , PP.FULL_NAME , PP.APPLICANT_NUMBER , PP.EMPLOYEE_NUMBER , PP.DATE_OF_BIRTH , A.ASSIGNMENT_ID , A.EFFECTIVE_START_DATE , PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID, A.POSITION_ID, A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE , PT.SYSTEM_PERSON_TYPE , PP.BUSINESS_GROUP_ID FROM PER_ALL_PEOPLE_F PP, PER_ALL_ASSIGNMENTS_F A, PER_PERSON_TYPES PT WHERE A.PERSON_ID = PP.PERSON_ID AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID AND trunc(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND A.ASSIGNMENT_TYPE = 'E' AND A.POSITION_ID IS NOT NULL AND NOT EXISTS (SELECT NULL FROM PER_ALL_ASSIGNMENTS_F X WHERE X.ASSIGNMENT_ID = A.ASSIGNMENT_ID AND X.ASSIGNMENT_TYPE = 'E' AND X.POSITION_ID = A.POSITION_ID AND X.EFFECTIVE_END_DATE = A.EFFECTIVE_START_DATE - 1 AND X.EFFECTIVE_START_DATE < A.EFFECTIVE_START_DATE) UNION SELECT A.POSITION_ID, A.ASSIGNMENT_TYPE, PP.PERSON_ID, PP.FULL_NAME, PP.APPLICANT_NUMBER, PP.EMPLOYEE_NUMBER, PP.DATE_OF_BIRTH, A.ASSIGNMENT_ID, PER_GET_ASG_POS.START_DATE (A.ASSIGNMENT_ID, A.POSITION_ID, A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE) EFFECTIVE_START_DATE, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID, A.POSITION_ID, A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE, PT.SYSTEM_PERSON_TYPE, PP.BUSINESS_GROUP_ID FROM PER_ALL_PEOPLE_F PP, PER_ALL_ASSIGNMENTS_F A, PER_PERSON_TYPES PT WHERE A.PERSON_ID = PP.PERSON_ID AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID AND A.ASSIGNMENT_TYPE = 'A' AND A.POSITION_ID IS NOT NULL AND trunc(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE AND trunc(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE

View Text - HTML Formatted

SELECT A.POSITION_ID
, A.ASSIGNMENT_TYPE
, PP.PERSON_ID
, PP.FULL_NAME
, PP.APPLICANT_NUMBER
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PT.SYSTEM_PERSON_TYPE
, PP.BUSINESS_GROUP_ID
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
WHERE A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND A.ASSIGNMENT_TYPE = 'E'
AND A.POSITION_ID IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM PER_ALL_ASSIGNMENTS_F X
WHERE X.ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND X.ASSIGNMENT_TYPE = 'E'
AND X.POSITION_ID = A.POSITION_ID
AND X.EFFECTIVE_END_DATE = A.EFFECTIVE_START_DATE - 1
AND X.EFFECTIVE_START_DATE < A.EFFECTIVE_START_DATE) UNION SELECT A.POSITION_ID
, A.ASSIGNMENT_TYPE
, PP.PERSON_ID
, PP.FULL_NAME
, PP.APPLICANT_NUMBER
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, A.ASSIGNMENT_ID
, PER_GET_ASG_POS.START_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) EFFECTIVE_START_DATE
, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PT.SYSTEM_PERSON_TYPE
, PP.BUSINESS_GROUP_ID
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
WHERE A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID
AND A.ASSIGNMENT_TYPE = 'A'
AND A.POSITION_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE