VIEW: APPS.PER_PEOPLE_ASSIGNMENTS_V1

Source

View Text - Preformatted

SELECT P.PERSON_ID, P.BUSINESS_GROUP_ID + 0, P.FULL_NAME, P.ORDER_NAME, P.EMPLOYEE_NUMBER, P.APPLICANT_NUMBER, P.CURRENT_EMPLOYEE_FLAG, P.CURRENT_APPLICANT_FLAG, A.ASSIGNMENT_NUMBER, A.ASSIGNMENT_TYPE, A.ORGANIZATION_ID, A.PAYROLL_ID, A.PRIMARY_FLAG, A.LAST_UPDATE_DATE, A.LAST_UPDATED_BY, A.LAST_UPDATE_LOGIN, A.CREATED_BY, A.CREATION_DATE, HR_GENERAL.DECODE_GRADE(A.GRADE_ID) GRADE_NAME, HR_GENERAL.DECODE_JOB(A.JOB_ID) JOB_NAME, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID) POSITION_NAME, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID) ORG_NAME, PAY.PAYROLL_NAME, /* PTTL.USER_PERSON_TYPE */ hr_person_type_usage_info.get_user_person_type(s.effective_date, p.person_id) P_TYPE, A.MANAGER_FLAG, HR_GENERAL.DECODE_LOCATION(a.location_id) LOCATION_CODE, p1.full_name SUP_NAME, HR_GENERAL.GET_USER_STATUS(A.ASSIGNMENT_STATUS_TYPE_ID) USER_STAT, A.ASSIGNMENT_ID, A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE, A.ASSIGNMENT_STATUS_TYPE_ID, PG.SEGMENT1, PG.SEGMENT2, PG.SEGMENT3, PG.SEGMENT4, PG.SEGMENT5, PG.SEGMENT6, PG.SEGMENT7, PG.SEGMENT8, PG.SEGMENT9, PG.SEGMENT10, PG.SEGMENT11, PG.SEGMENT12, PG.SEGMENT13, PG.SEGMENT14, PG.SEGMENT15, PG.SEGMENT16, PG.SEGMENT17, PG.SEGMENT18, PG.SEGMENT19, PG.SEGMENT20, PG.SEGMENT21, PG.SEGMENT22, PG.SEGMENT23, PG.SEGMENT24, PG.SEGMENT25, PG.SEGMENT26, PG.SEGMENT27, PG.SEGMENT28, PG.SEGMENT29, PG.SEGMENT30, GD.SEGMENT1, GD.SEGMENT2, GD.SEGMENT3, GD.SEGMENT4, GD.SEGMENT5, GD.SEGMENT6, GD.SEGMENT7, GD.SEGMENT8, GD.SEGMENT9, GD.SEGMENT10, GD.SEGMENT11, GD.SEGMENT12, GD.SEGMENT13, GD.SEGMENT14, GD.SEGMENT15, GD.SEGMENT16, GD.SEGMENT17, GD.SEGMENT18, GD.SEGMENT19, GD.SEGMENT20, GD.SEGMENT21, GD.SEGMENT22, GD.SEGMENT23, GD.SEGMENT24, GD.SEGMENT25, GD.SEGMENT26, GD.SEGMENT27, GD.SEGMENT28, GD.SEGMENT29, GD.SEGMENT30, JD.SEGMENT1, JD.SEGMENT2, JD.SEGMENT3, JD.SEGMENT4, JD.SEGMENT5, JD.SEGMENT6, JD.SEGMENT7, JD.SEGMENT8, JD.SEGMENT9, JD.SEGMENT10, JD.SEGMENT11, JD.SEGMENT12, JD.SEGMENT13, JD.SEGMENT14, JD.SEGMENT15, JD.SEGMENT16, JD.SEGMENT17, JD.SEGMENT18, JD.SEGMENT19, JD.SEGMENT20, JD.SEGMENT21, JD.SEGMENT22, JD.SEGMENT23, JD.SEGMENT24, JD.SEGMENT25, JD.SEGMENT26, JD.SEGMENT27, JD.SEGMENT28, JD.SEGMENT29, JD.SEGMENT30, PD.SEGMENT1, PD.SEGMENT2, PD.SEGMENT3, PD.SEGMENT4, PD.SEGMENT5, PD.SEGMENT6, PD.SEGMENT7, PD.SEGMENT8, PD.SEGMENT9, PD.SEGMENT10, PD.SEGMENT11, PD.SEGMENT12, PD.SEGMENT13, PD.SEGMENT14, PD.SEGMENT15, PD.SEGMENT16, PD.SEGMENT17, PD.SEGMENT18, PD.SEGMENT19, PD.SEGMENT20, PD.SEGMENT21, PD.SEGMENT22, PD.SEGMENT23, PD.SEGMENT24, PD.SEGMENT25, PD.SEGMENT26, PD.SEGMENT27, PD.SEGMENT28, PD.SEGMENT29, PD.SEGMENT30, A.ASSIGNMENT_SEQUENCE, P.NPW_NUMBER, P.CURRENT_NPW_FLAG, PGM.NAME GRADE_LADDER_NAME FROM PER_POSITION_DEFINITIONS PD, PER_GRADE_DEFINITIONS GD, PER_GRADES G, PER_GRADES_TL GDT, PER_JOB_DEFINITIONS JD, PER_JOBS J, PER_JOBS_TL JBT, PAY_PEOPLE_GROUPS PG, PER_PEOPLE_F P, PER_ALL_PEOPLE_F P1, PER_ALL_ASSIGNMENTS_F A, PER_PERSON_TYPES_TL PTTL, FND_SESSIONS S, BEN_PGM_F PGM, PAY_ALL_PAYROLLS_F PAY WHERE S. SESSION_ID = USERENV('SESSIONID') AND P.PERSON_ID = A.PERSON_ID AND S.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE AND PAY.PAYROLL_ID(+) = A.PAYROLL_ID AND S.EFFECTIVE_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE,S.EFFECTIVE_DATE) AND NVL(PAY.EFFECTIVE_END_DATE,S.EFFECTIVE_DATE) AND P1.PERSON_ID(+) = A.SUPERVISOR_ID AND S.EFFECTIVE_DATE BETWEEN nvl(P1.EFFECTIVE_START_DATE, S.EFFECTIVE_DATE) AND nvl(P1.EFFECTIVE_END_DATE, S.EFFECTIVE_DATE) AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID(+) AND A.JOB_ID = J.JOB_ID(+) AND A.JOB_ID = JBT.JOB_ID(+) AND JBT.LANGUAGE(+) = USERENV('LANG') AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID(+) AND A. GRADE_ID = G.GRADE_ID(+) AND A.GRADE_ID = GDT.GRADE_ID(+) AND GDT.LANGUAGE(+) = USERENV('LANG') AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID(+) AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(A.POSITION_ID) = PD.POSITION_DEFINITION_ID(+) AND P.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID(+) AND PTTL.LANGUAGE(+) = USERENV('LANG') AND A.GRADE_LADDER_PGM_ID = PGM.PGM_ID(+) AND A.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE(+) AND PGM.EFFECTIVE_END_DATE(+) UNION ALL SELECT P.PERSON_ID, P.BUSINESS_GROUP_ID + 0, P.FULL_NAME, P.ORDER_NAME, P.EMPLOYEE_NUMBER, P.APPLICANT_NUMBER, P.CURRENT_EMPLOYEE_FLAG, P.CURRENT_APPLICANT_FLAG, A.ASSIGNMENT_NUMBER, A.ASSIGNMENT_TYPE, A.ORGANIZATION_ID, A.PAYROLL_ID, A.PRIMARY_FLAG, A.LAST_UPDATE_DATE, A.LAST_UPDATED_BY, A.LAST_UPDATE_LOGIN, A.CREATED_BY, A.CREATION_DATE, HR_GENERAL.DECODE_GRADE(A.GRADE_ID), HR_GENERAL.DECODE_JOB(A. JOB_ID), HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID), HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID), PAY.PAYROLL_NAME, PTTL.USER_PERSON_TYPE, A.MANAGER_FLAG, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID), p1.full_name, HR_GENERAL.GET_USER_STATUS(A.ASSIGNMENT_STATUS_TYPE_ID), A.ASSIGNMENT_ID, A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE, A.ASSIGNMENT_STATUS_TYPE_ID, PG.SEGMENT1, PG.SEGMENT2, PG.SEGMENT3, PG.SEGMENT4, PG.SEGMENT5, PG.SEGMENT6, PG.SEGMENT7, PG.SEGMENT8, PG.SEGMENT9, PG.SEGMENT10, PG.SEGMENT11, PG.SEGMENT12, PG.SEGMENT13, PG.SEGMENT14, PG.SEGMENT15, PG.SEGMENT16, PG.SEGMENT17, PG.SEGMENT18, PG.SEGMENT19, PG.SEGMENT20, PG.SEGMENT21, PG.SEGMENT22, PG.SEGMENT23, PG.SEGMENT24, PG.SEGMENT25, PG.SEGMENT26, PG.SEGMENT27, PG.SEGMENT28, PG.SEGMENT29, PG.SEGMENT30, GD.SEGMENT1, GD.SEGMENT2, GD.SEGMENT3, GD.SEGMENT4, GD.SEGMENT5, GD.SEGMENT6, GD.SEGMENT7, GD.SEGMENT8, GD.SEGMENT9, GD.SEGMENT10, GD.SEGMENT11, GD.SEGMENT12, GD.SEGMENT13, GD.SEGMENT14, GD.SEGMENT15, GD.SEGMENT16, GD.SEGMENT17, GD.SEGMENT18, GD.SEGMENT19, GD.SEGMENT20, GD.SEGMENT21, GD.SEGMENT22, GD.SEGMENT23, GD.SEGMENT24, GD.SEGMENT25, GD.SEGMENT26, GD.SEGMENT27, GD.SEGMENT28, GD.SEGMENT29, GD.SEGMENT30, JD.SEGMENT1, JD.SEGMENT2, JD.SEGMENT3, JD.SEGMENT4, JD.SEGMENT5, JD.SEGMENT6, JD.SEGMENT7, JD.SEGMENT8, JD.SEGMENT9, JD.SEGMENT10, JD.SEGMENT11, JD.SEGMENT12, JD.SEGMENT13, JD.SEGMENT14, JD.SEGMENT15, JD.SEGMENT16, JD.SEGMENT17, JD.SEGMENT18, JD.SEGMENT19, JD.SEGMENT20, JD.SEGMENT21, JD.SEGMENT22, JD.SEGMENT23, JD.SEGMENT24, JD.SEGMENT25, JD.SEGMENT26, JD.SEGMENT27, JD.SEGMENT28, JD.SEGMENT29, JD.SEGMENT30, PD.SEGMENT1, PD.SEGMENT2, PD.SEGMENT3, PD.SEGMENT4, PD.SEGMENT5, PD.SEGMENT6, PD.SEGMENT7, PD.SEGMENT8, PD.SEGMENT9, PD.SEGMENT10, PD.SEGMENT11, PD.SEGMENT12, PD.SEGMENT13, PD.SEGMENT14, PD.SEGMENT15, PD.SEGMENT16, PD.SEGMENT17, PD.SEGMENT18, PD.SEGMENT19, PD.SEGMENT20, PD.SEGMENT21, PD.SEGMENT22, PD.SEGMENT23, PD.SEGMENT24, PD.SEGMENT25, PD.SEGMENT26, PD.SEGMENT27, PD.SEGMENT28, PD.SEGMENT29, PD.SEGMENT30, A.ASSIGNMENT_SEQUENCE, P.NPW_NUMBER, P.CURRENT_NPW_FLAG, PGM.NAME GRADE_LADDER_NAME FROM PER_POSITION_DEFINITIONS PD, PER_GRADE_DEFINITIONS GD, PER_GRADES G, PER_GRADES_TL GDT, PER_JOB_DEFINITIONS JD, PER_JOBS J, PER_JOBS_TL JBT, PAY_PEOPLE_GROUPS PG, PER_PEOPLE_F P, PER_ALL_PEOPLE_F P1, PER_ALL_ASSIGNMENTS_F A, PER_PERSON_TYPES_TL PTTL, FND_SESSIONS S, BEN_PGM_F PGM, PAY_ALL_PAYROLLS_F PAY WHERE S.SESSION_ID = USERENV('SESSIONID') AND P.PERSON_ID = A.PERSON_ID AND PAY.PAYROLL_ID(+) = A.PAYROLL_ID AND A.EFFECTIVE_END_DATE < S.EFFECTIVE_DATE AND A.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE) FROM PER_ASSIGNMENTS_F A2 WHERE A.ASSIGNMENT_ID = A2.ASSIGNMENT_ID) AND P1.PERSON_ID(+) = A.SUPERVISOR_ID AND S.EFFECTIVE_DATE BETWEEN nvl(P1.EFFECTIVE_START_DATE, S.EFFECTIVE_DATE) AND nvl(P1.EFFECTIVE_END_DATE, S.EFFECTIVE_DATE) AND S.EFFECTIVE_DATE BETWEEN NVL (PAY.EFFECTIVE_START_DATE,S.EFFECTIVE_DATE) AND NVL(PAY.EFFECTIVE_END_DATE, S.EFFECTIVE_DATE) AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID(+) AND A.JOB_ID = J.JOB_ID(+) AND A.JOB_ID = JBT.JOB_ID(+) AND JBT.LANGUAGE(+) = USERENV('LANG') AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID(+) AND A.GRADE_ID = G.GRADE_ID(+) AND A.GRADE_ID = GDT.GRADE_ID(+) AND GDT.LANGUAGE(+) = USERENV('LANG') AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID(+) AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(A.POSITION_ID) = PD.POSITION_DEFINITION_ID(+) AND P.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID(+) AND PTTL.LANGUAGE(+) = USERENV('LANG') AND A.GRADE_LADDER_PGM_ID = PGM.PGM_ID(+) AND A.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE(+) AND PGM.EFFECTIVE_END_DATE(+)

View Text - HTML Formatted

SELECT P.PERSON_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.ORDER_NAME
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, A.ASSIGNMENT_NUMBER
, A.ASSIGNMENT_TYPE
, A.ORGANIZATION_ID
, A.PAYROLL_ID
, A.PRIMARY_FLAG
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, HR_GENERAL.DECODE_GRADE(A.GRADE_ID) GRADE_NAME
, HR_GENERAL.DECODE_JOB(A.JOB_ID) JOB_NAME
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID) POSITION_NAME
, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID) ORG_NAME
, PAY.PAYROLL_NAME
, /* PTTL.USER_PERSON_TYPE */ HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(S.EFFECTIVE_DATE
, P.PERSON_ID) P_TYPE
, A.MANAGER_FLAG
, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID) LOCATION_CODE
, P1.FULL_NAME SUP_NAME
, HR_GENERAL.GET_USER_STATUS(A.ASSIGNMENT_STATUS_TYPE_ID) USER_STAT
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.ASSIGNMENT_STATUS_TYPE_ID
, PG.SEGMENT1
, PG.SEGMENT2
, PG.SEGMENT3
, PG.SEGMENT4
, PG.SEGMENT5
, PG.SEGMENT6
, PG.SEGMENT7
, PG.SEGMENT8
, PG.SEGMENT9
, PG.SEGMENT10
, PG.SEGMENT11
, PG.SEGMENT12
, PG.SEGMENT13
, PG.SEGMENT14
, PG.SEGMENT15
, PG.SEGMENT16
, PG.SEGMENT17
, PG.SEGMENT18
, PG.SEGMENT19
, PG.SEGMENT20
, PG.SEGMENT21
, PG.SEGMENT22
, PG.SEGMENT23
, PG.SEGMENT24
, PG.SEGMENT25
, PG.SEGMENT26
, PG.SEGMENT27
, PG.SEGMENT28
, PG.SEGMENT29
, PG.SEGMENT30
, GD.SEGMENT1
, GD.SEGMENT2
, GD.SEGMENT3
, GD.SEGMENT4
, GD.SEGMENT5
, GD.SEGMENT6
, GD.SEGMENT7
, GD.SEGMENT8
, GD.SEGMENT9
, GD.SEGMENT10
, GD.SEGMENT11
, GD.SEGMENT12
, GD.SEGMENT13
, GD.SEGMENT14
, GD.SEGMENT15
, GD.SEGMENT16
, GD.SEGMENT17
, GD.SEGMENT18
, GD.SEGMENT19
, GD.SEGMENT20
, GD.SEGMENT21
, GD.SEGMENT22
, GD.SEGMENT23
, GD.SEGMENT24
, GD.SEGMENT25
, GD.SEGMENT26
, GD.SEGMENT27
, GD.SEGMENT28
, GD.SEGMENT29
, GD.SEGMENT30
, JD.SEGMENT1
, JD.SEGMENT2
, JD.SEGMENT3
, JD.SEGMENT4
, JD.SEGMENT5
, JD.SEGMENT6
, JD.SEGMENT7
, JD.SEGMENT8
, JD.SEGMENT9
, JD.SEGMENT10
, JD.SEGMENT11
, JD.SEGMENT12
, JD.SEGMENT13
, JD.SEGMENT14
, JD.SEGMENT15
, JD.SEGMENT16
, JD.SEGMENT17
, JD.SEGMENT18
, JD.SEGMENT19
, JD.SEGMENT20
, JD.SEGMENT21
, JD.SEGMENT22
, JD.SEGMENT23
, JD.SEGMENT24
, JD.SEGMENT25
, JD.SEGMENT26
, JD.SEGMENT27
, JD.SEGMENT28
, JD.SEGMENT29
, JD.SEGMENT30
, PD.SEGMENT1
, PD.SEGMENT2
, PD.SEGMENT3
, PD.SEGMENT4
, PD.SEGMENT5
, PD.SEGMENT6
, PD.SEGMENT7
, PD.SEGMENT8
, PD.SEGMENT9
, PD.SEGMENT10
, PD.SEGMENT11
, PD.SEGMENT12
, PD.SEGMENT13
, PD.SEGMENT14
, PD.SEGMENT15
, PD.SEGMENT16
, PD.SEGMENT17
, PD.SEGMENT18
, PD.SEGMENT19
, PD.SEGMENT20
, PD.SEGMENT21
, PD.SEGMENT22
, PD.SEGMENT23
, PD.SEGMENT24
, PD.SEGMENT25
, PD.SEGMENT26
, PD.SEGMENT27
, PD.SEGMENT28
, PD.SEGMENT29
, PD.SEGMENT30
, A.ASSIGNMENT_SEQUENCE
, P.NPW_NUMBER
, P.CURRENT_NPW_FLAG
, PGM.NAME GRADE_LADDER_NAME
FROM PER_POSITION_DEFINITIONS PD
, PER_GRADE_DEFINITIONS GD
, PER_GRADES G
, PER_GRADES_TL GDT
, PER_JOB_DEFINITIONS JD
, PER_JOBS J
, PER_JOBS_TL JBT
, PAY_PEOPLE_GROUPS PG
, PER_PEOPLE_F P
, PER_ALL_PEOPLE_F P1
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES_TL PTTL
, FND_SESSIONS S
, BEN_PGM_F PGM
, PAY_ALL_PAYROLLS_F PAY
WHERE S. SESSION_ID = USERENV('SESSIONID')
AND P.PERSON_ID = A.PERSON_ID
AND S.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND PAY.PAYROLL_ID(+) = A.PAYROLL_ID
AND S.EFFECTIVE_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE
, S.EFFECTIVE_DATE)
AND NVL(PAY.EFFECTIVE_END_DATE
, S.EFFECTIVE_DATE)
AND P1.PERSON_ID(+) = A.SUPERVISOR_ID
AND S.EFFECTIVE_DATE BETWEEN NVL(P1.EFFECTIVE_START_DATE
, S.EFFECTIVE_DATE)
AND NVL(P1.EFFECTIVE_END_DATE
, S.EFFECTIVE_DATE)
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID(+)
AND A.JOB_ID = J.JOB_ID(+)
AND A.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID(+)
AND A. GRADE_ID = G.GRADE_ID(+)
AND A.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID(+)
AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(A.POSITION_ID) = PD.POSITION_DEFINITION_ID(+)
AND P.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID(+)
AND PTTL.LANGUAGE(+) = USERENV('LANG')
AND A.GRADE_LADDER_PGM_ID = PGM.PGM_ID(+)
AND A.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE(+)
AND PGM.EFFECTIVE_END_DATE(+) UNION ALL SELECT P.PERSON_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.ORDER_NAME
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, A.ASSIGNMENT_NUMBER
, A.ASSIGNMENT_TYPE
, A.ORGANIZATION_ID
, A.PAYROLL_ID
, A.PRIMARY_FLAG
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, HR_GENERAL.DECODE_GRADE(A.GRADE_ID)
, HR_GENERAL.DECODE_JOB(A. JOB_ID)
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID)
, HR_GENERAL.DECODE_ORGANIZATION(A.ORGANIZATION_ID)
, PAY.PAYROLL_NAME
, PTTL.USER_PERSON_TYPE
, A.MANAGER_FLAG
, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID)
, P1.FULL_NAME
, HR_GENERAL.GET_USER_STATUS(A.ASSIGNMENT_STATUS_TYPE_ID)
, A.ASSIGNMENT_ID
, A.EFFECTIVE_START_DATE
, A.EFFECTIVE_END_DATE
, A.ASSIGNMENT_STATUS_TYPE_ID
, PG.SEGMENT1
, PG.SEGMENT2
, PG.SEGMENT3
, PG.SEGMENT4
, PG.SEGMENT5
, PG.SEGMENT6
, PG.SEGMENT7
, PG.SEGMENT8
, PG.SEGMENT9
, PG.SEGMENT10
, PG.SEGMENT11
, PG.SEGMENT12
, PG.SEGMENT13
, PG.SEGMENT14
, PG.SEGMENT15
, PG.SEGMENT16
, PG.SEGMENT17
, PG.SEGMENT18
, PG.SEGMENT19
, PG.SEGMENT20
, PG.SEGMENT21
, PG.SEGMENT22
, PG.SEGMENT23
, PG.SEGMENT24
, PG.SEGMENT25
, PG.SEGMENT26
, PG.SEGMENT27
, PG.SEGMENT28
, PG.SEGMENT29
, PG.SEGMENT30
, GD.SEGMENT1
, GD.SEGMENT2
, GD.SEGMENT3
, GD.SEGMENT4
, GD.SEGMENT5
, GD.SEGMENT6
, GD.SEGMENT7
, GD.SEGMENT8
, GD.SEGMENT9
, GD.SEGMENT10
, GD.SEGMENT11
, GD.SEGMENT12
, GD.SEGMENT13
, GD.SEGMENT14
, GD.SEGMENT15
, GD.SEGMENT16
, GD.SEGMENT17
, GD.SEGMENT18
, GD.SEGMENT19
, GD.SEGMENT20
, GD.SEGMENT21
, GD.SEGMENT22
, GD.SEGMENT23
, GD.SEGMENT24
, GD.SEGMENT25
, GD.SEGMENT26
, GD.SEGMENT27
, GD.SEGMENT28
, GD.SEGMENT29
, GD.SEGMENT30
, JD.SEGMENT1
, JD.SEGMENT2
, JD.SEGMENT3
, JD.SEGMENT4
, JD.SEGMENT5
, JD.SEGMENT6
, JD.SEGMENT7
, JD.SEGMENT8
, JD.SEGMENT9
, JD.SEGMENT10
, JD.SEGMENT11
, JD.SEGMENT12
, JD.SEGMENT13
, JD.SEGMENT14
, JD.SEGMENT15
, JD.SEGMENT16
, JD.SEGMENT17
, JD.SEGMENT18
, JD.SEGMENT19
, JD.SEGMENT20
, JD.SEGMENT21
, JD.SEGMENT22
, JD.SEGMENT23
, JD.SEGMENT24
, JD.SEGMENT25
, JD.SEGMENT26
, JD.SEGMENT27
, JD.SEGMENT28
, JD.SEGMENT29
, JD.SEGMENT30
, PD.SEGMENT1
, PD.SEGMENT2
, PD.SEGMENT3
, PD.SEGMENT4
, PD.SEGMENT5
, PD.SEGMENT6
, PD.SEGMENT7
, PD.SEGMENT8
, PD.SEGMENT9
, PD.SEGMENT10
, PD.SEGMENT11
, PD.SEGMENT12
, PD.SEGMENT13
, PD.SEGMENT14
, PD.SEGMENT15
, PD.SEGMENT16
, PD.SEGMENT17
, PD.SEGMENT18
, PD.SEGMENT19
, PD.SEGMENT20
, PD.SEGMENT21
, PD.SEGMENT22
, PD.SEGMENT23
, PD.SEGMENT24
, PD.SEGMENT25
, PD.SEGMENT26
, PD.SEGMENT27
, PD.SEGMENT28
, PD.SEGMENT29
, PD.SEGMENT30
, A.ASSIGNMENT_SEQUENCE
, P.NPW_NUMBER
, P.CURRENT_NPW_FLAG
, PGM.NAME GRADE_LADDER_NAME
FROM PER_POSITION_DEFINITIONS PD
, PER_GRADE_DEFINITIONS GD
, PER_GRADES G
, PER_GRADES_TL GDT
, PER_JOB_DEFINITIONS JD
, PER_JOBS J
, PER_JOBS_TL JBT
, PAY_PEOPLE_GROUPS PG
, PER_PEOPLE_F P
, PER_ALL_PEOPLE_F P1
, PER_ALL_ASSIGNMENTS_F A
, PER_PERSON_TYPES_TL PTTL
, FND_SESSIONS S
, BEN_PGM_F PGM
, PAY_ALL_PAYROLLS_F PAY
WHERE S.SESSION_ID = USERENV('SESSIONID')
AND P.PERSON_ID = A.PERSON_ID
AND PAY.PAYROLL_ID(+) = A.PAYROLL_ID
AND A.EFFECTIVE_END_DATE < S.EFFECTIVE_DATE
AND A.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F A2
WHERE A.ASSIGNMENT_ID = A2.ASSIGNMENT_ID)
AND P1.PERSON_ID(+) = A.SUPERVISOR_ID
AND S.EFFECTIVE_DATE BETWEEN NVL(P1.EFFECTIVE_START_DATE
, S.EFFECTIVE_DATE)
AND NVL(P1.EFFECTIVE_END_DATE
, S.EFFECTIVE_DATE)
AND S.EFFECTIVE_DATE BETWEEN NVL (PAY.EFFECTIVE_START_DATE
, S.EFFECTIVE_DATE)
AND NVL(PAY.EFFECTIVE_END_DATE
, S.EFFECTIVE_DATE)
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID(+)
AND A.JOB_ID = J.JOB_ID(+)
AND A.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID(+)
AND A.GRADE_ID = G.GRADE_ID(+)
AND A.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID(+)
AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(A.POSITION_ID) = PD.POSITION_DEFINITION_ID(+)
AND P.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID(+)
AND PTTL.LANGUAGE(+) = USERENV('LANG')
AND A.GRADE_LADDER_PGM_ID = PGM.PGM_ID(+)
AND A.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE(+)
AND PGM.EFFECTIVE_END_DATE(+)