VIEW: APPS.PER_EXPANDED_ASSIGNMENTS_VIEW

Source

View Text - Preformatted

SELECT P.PERSON_ID , P.BUSINESS_GROUP_ID , 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 , 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 FROM PER_POSITION_DEFINITIONS PD , PER_GRADE_DEFINITIONS GD , PER_GRADES G , PER_JOB_DEFINITIONS JD , PER_JOBS J , PAY_PEOPLE_GROUPS PG , PER_PEOPLE_F P , PER_ASSIGNMENTS_F A WHERE P.PERSON_ID = A.PERSON_ID AND P.EFFECTIVE_START_DATE <= (SELECT S.EFFECTIVE_DATE FROM FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID')) AND P.EFFECTIVE_END_DATE >= (SELECT S.EFFECTIVE_DATE FROM FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID')) AND A.EFFECTIVE_START_DATE <= (SELECT S.EFFECTIVE_DATE FROM FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID')) AND A.EFFECTIVE_END_DATE >= (SELECT S.EFFECTIVE_DATE FROM FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID')) AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID (+) AND A.JOB_ID = J.JOB_ID (+) AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID (+) AND A.GRADE_ID = G.GRADE_ID (+) AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID (+) AND hr_general.DECODE_LATEST_POSITION_DEF_ID (a.position_id) = PD.POSITION_DEFINITION_ID (+)

View Text - HTML Formatted

SELECT P.PERSON_ID
, P.BUSINESS_GROUP_ID
, 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
, 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
FROM PER_POSITION_DEFINITIONS PD
, PER_GRADE_DEFINITIONS GD
, PER_GRADES G
, PER_JOB_DEFINITIONS JD
, PER_JOBS J
, PAY_PEOPLE_GROUPS PG
, PER_PEOPLE_F P
, PER_ASSIGNMENTS_F A
WHERE P.PERSON_ID = A.PERSON_ID
AND P.EFFECTIVE_START_DATE <= (SELECT S.EFFECTIVE_DATE
FROM FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID'))
AND P.EFFECTIVE_END_DATE >= (SELECT S.EFFECTIVE_DATE
FROM FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID'))
AND A.EFFECTIVE_START_DATE <= (SELECT S.EFFECTIVE_DATE
FROM FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID'))
AND A.EFFECTIVE_END_DATE >= (SELECT S.EFFECTIVE_DATE
FROM FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID'))
AND A.PEOPLE_GROUP_ID = PG.PEOPLE_GROUP_ID (+)
AND A.JOB_ID = J.JOB_ID (+)
AND J.JOB_DEFINITION_ID = JD.JOB_DEFINITION_ID (+)
AND A.GRADE_ID = G.GRADE_ID (+)
AND G.GRADE_DEFINITION_ID = GD.GRADE_DEFINITION_ID (+)
AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID (A.POSITION_ID) = PD.POSITION_DEFINITION_ID (+)