View: PER_EXPANDED_ASSIGNMENTS_V1

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_EXPANDED_ASSIGNMENTS_V1

View Text

SELECT /* * THIS SELECT GETS ALL THE ASSIGNMENTS WITH SALARIES (NON-NULL PAY BASES)
, * ASSIGNMENTS WITH A NULL PAY BASIS
,
AND ASSIGNMENTS WITH A NON-NULL PAY * BASIS BUT NO ELEMENT ENTRY. */ DISTINCT P.PERSON_ID
, P.BUSINESS_GROUP_ID + 0
, P.FULL_NAME
, P.EMPLOYEE_NUMBER
, P.APPLICANT_NUMBER
, P.CURRENT_EMPLOYEE_FLAG
, P.CURRENT_APPLICANT_FLAG
, A.ASSIGNMENT_ID
, A.ASSIGNMENT_NUMBER
, A.ASSIGNMENT_TYPE
, A.ORGANIZATION_ID
, A.PAYROLL_ID
, A.PRIMARY_FLAG
, GDT.NAME
, JBT.NAME
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(A.POSITION_ID)
, ORGTL.NAME
, HR_GENERAL.DECODE_PAYROLL(A.PAYROLL_ID) PAYROLL_NAME /*
, PTTL.USER_PERSON_TYPE */
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(S.EFFECTIVE_DATE
, P.PERSON_ID)
, A.MANAGER_FLAG
, HR_GENERAL.DECODE_LOCATION(A.LOCATION_ID) LOCATION_CODE
, P2.FULL_NAME
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS)
, 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
, HR_GENERAL.GET_SALARY(A.PAY_BASIS_ID
, A.ASSIGNMENT_ID) SCREEN_ENTRY_VALUE
, INITCAP(HR_GENERAL.DECODE_PAY_BASIS(A.PAY_BASIS_ID)) PAY_BASIS
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.LAST_UPDATE_LOGIN
, A.CREATED_BY
, A.CREATION_DATE
, P.NPW_NUMBER
, P.CURRENT_NPW_FLAG
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_ASSIGNMENTS_F A
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_PERSON_TYPES_TL PTTL
, PER_PERSON_TYPES PT
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTATL
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, PER_ASSIGNMENT_STATUS_TYPES AST
, FND_SESSIONS S
, PER_ALL_PEOPLE_F P2
WHERE P.PERSON_ID = A.PERSON_ID
AND S.EFFECTIVE_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND S.EFFECTIVE_DATE BETWEEN A.EFFECTIVE_START_DATE
AND A.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 A.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND P.PERSON_TYPE_ID = PT.PERSON_TYPE_ID (+)
AND A.SUPERVISOR_ID = P2.PERSON_ID (+)
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID (+)
AND DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, ORGTL.LANGUAGE) =DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND PT.PERSON_TYPE_ID = PTTL.PERSON_TYPE_ID (+)
AND DECODE(PTTL.PERSON_TYPE_ID
, NULL
, '1'
, PTTL.LANGUAGE) =DECODE(PTTL.PERSON_TYPE_ID
, NULL
, '1'
, USERENV('LANG'))
AND ( (A.SUPERVISOR_ID IS NOT NULL
AND (( S.EFFECTIVE_DATE BETWEEN P2.EFFECTIVE_START_DATE
AND P2.EFFECTIVE_END_DATE ) OR ( P2.START_DATE > S.EFFECTIVE_DATE
AND P2.START_DATE = P2.EFFECTIVE_START_DATE )) ) OR A.SUPERVISOR_ID IS NULL )
AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTTL.LANGUAGE = USERENV('LANG')
AND A.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTATL.ASS_STATUS_TYPE_AMEND_ID (+)
AND DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, ASTATL.LANGUAGE) =DECODE(ASTATL.ASS_STATUS_TYPE_AMEND_ID
, NULL
, '1'
, USERENV('LANG'))
AND A.BUSINESS_GROUP_ID +0 = ASTA.BUSINESS_GROUP_ID(+) +0
AND S.SESSION_ID = USERENV('SESSIONID')

Columns