VIEW: APPS.PER_ASSIGNMENTS_V11

Source

View Text - Preformatted

SELECT A.POSITION_ID position_id, A.ASSIGNMENT_TYPE assignment_type, PP.PERSON_ID person_id, PP.FULL_NAME full_name,PP.APPLICANT_NUMBER applicant_number, PP.EMPLOYEE_NUMBER employee_number, PP.DATE_OF_BIRTH date_of_birth, A.ASSIGNMENT_ID assignment_id,A.EFFECTIVE_START_DATE start_date, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID, A.POSITION_ID, A.EFFECTIVE_START_DATE,A.EFFECTIVE_END_DATE) END_DATE , PT.SYSTEM_PERSON_TYPE system_person_type, PP.BUSINESS_GROUP_ID business_group_id, PP.NPW_NUMBER npw_number, hr_person_type_usage_info.get_worker_user_person_type(a.EFFECTIVE_START_DATE,PP.PERSON_ID) USER_PERSON_TYPE FROM PER_ALL_PEOPLE_F PP, PER_ALL_ASSIGNMENTS_F A, PER_PERSON_TYPES PT, FND_SESSIONS FS WHERE A.PERSON_ID = PP.PERSON_ID AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID AND trunc(FS.EFFECTIVE_DATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND FS.SESSION_ID = USERENV('SESSIONID') AND A.ASSIGNMENT_TYPE IN ('E','C') 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 = A.ASSIGNMENT_TYPE 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, PP.NPW_NUMBER, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(A.EFFECTIVE_START_DATE, PP.PERSON_ID) USER_PERSON_TYPE FROM PER_ALL_PEOPLE_F PP, PER_ALL_ASSIGNMENTS_F A, PER_PERSON_TYPES PT, FND_SESSIONS FS 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(FS.EFFECTIVE_DATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE */ AND trunc(FS.EFFECTIVE_DATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND FS.SESSION_ID = USERENV('SESSIONID')

View Text - HTML Formatted

SELECT A.POSITION_ID POSITION_ID
, A.ASSIGNMENT_TYPE ASSIGNMENT_TYPE
, PP.PERSON_ID PERSON_ID
, PP.FULL_NAME FULL_NAME
, PP.APPLICANT_NUMBER APPLICANT_NUMBER
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH DATE_OF_BIRTH
, A.ASSIGNMENT_ID ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE START_DATE
, PER_GET_ASG_POS.END_DATE (A.ASSIGNMENT_ID
, A.POSITION_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE) END_DATE
, PT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PP.NPW_NUMBER NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_WORKER_USER_PERSON_TYPE(A.EFFECTIVE_START_DATE
, PP.PERSON_ID) USER_PERSON_TYPE
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
, FND_SESSIONS FS
WHERE A.PERSON_ID = PP.PERSON_ID
AND PP.PERSON_TYPE_ID +0 = PT.PERSON_TYPE_ID
AND TRUNC(FS.EFFECTIVE_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND FS.SESSION_ID = USERENV('SESSIONID')
AND A.ASSIGNMENT_TYPE IN ('E'
, 'C')
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 = A.ASSIGNMENT_TYPE
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
, PP.NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(A.EFFECTIVE_START_DATE
, PP.PERSON_ID) USER_PERSON_TYPE
FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES PT
, FND_SESSIONS FS
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(FS.EFFECTIVE_DATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE */
AND TRUNC(FS.EFFECTIVE_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND FS.SESSION_ID = USERENV('SESSIONID')