VIEW: APPS.PER_EXPANDED_POSITIONS_V

Source

View Text - Preformatted

SELECT PP.POSITION_ID , PP.BUSINESS_GROUP_ID , PP.JOB_ID , PP.ORGANIZATION_ID , PP.LAST_UPDATE_DATE , PP.LAST_UPDATED_BY , PP.DATE_EFFECTIVE , PP.LAST_UPDATE_LOGIN , PP.CREATED_BY , PP.CREATION_DATE , PP.REQUEST_ID , PP.PROGRAM_APPLICATION_ID , PP.PROGRAM_ID , PP.PROGRAM_UPDATE_DATE , PPD.POSITION_DEFINITION_ID , PP.COMMENTS , HR_GENERAL.GET_POSITION_DATE_END(PP.POSITION_ID) , HR_GENERAL.DECODE_POSITION_LATEST_NAME(PP.POSITION_ID) , PP.LOCATION_ID , PPD.ID_FLEX_NUM , PPD.SUMMARY_FLAG , PPD.ENABLED_FLAG , PPD.START_DATE_ACTIVE , PPD.END_DATE_ACTIVE , PPD.SEGMENT1 , PPD.SEGMENT2 , PPD.SEGMENT3 , PPD.SEGMENT4 , PPD.SEGMENT5 , PPD.SEGMENT6 , PPD.SEGMENT7 , PPD.SEGMENT8 , PPD.SEGMENT9 , PPD.SEGMENT10 , PPD.SEGMENT11 , PPD.SEGMENT12 , PPD.SEGMENT13 , PPD.SEGMENT14 , PPD.SEGMENT15 , PPD.SEGMENT16 , PPD.SEGMENT17 , PPD.SEGMENT18 , PPD.SEGMENT19 , PPD.SEGMENT20 , PPD.SEGMENT21 , PPD.SEGMENT22 , PPD.SEGMENT23 , PPD.SEGMENT24 , PPD.SEGMENT25 , PPD.SEGMENT26 , PPD.SEGMENT27 , PPD.SEGMENT28 , PPD.SEGMENT29 , PPD.SEGMENT30 , PER.FULL_NAME , PER.EMPLOYEE_NUMBER , PER.NPW_NUMBER ,HR_PERSON_TYPE_USAGE_INFO.GET_WORKER_USER_PERSON_TYPE(SES.EFFECTIVE_DATE, PER.PERSON_ID) FROM HR_ALL_POSITIONS PP, PER_POSITION_DEFINITIONS PPD, PER_PEOPLE_F PER, PER_ASSIGNMENTS_F ASG, PER_ASSIGNMENT_STATUS_TYPES AST, FND_SESSIONS SES WHERE PPD.POSITION_DEFINITION_ID = HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PP.POSITION_ID) AND PER.PERSON_ID = ASG.PERSON_ID AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID AND AST.PER_SYSTEM_STATUS != 'TERM_ASSIGN' AND SES.EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND ASG.ASSIGNMENT_TYPE IN ( 'E', 'C') AND ASG.POSITION_ID = PP.POSITION_ID AND SES.SESSION_ID = USERENV ('sessionid')

View Text - HTML Formatted

SELECT PP.POSITION_ID
, PP.BUSINESS_GROUP_ID
, PP.JOB_ID
, PP.ORGANIZATION_ID
, PP.LAST_UPDATE_DATE
, PP.LAST_UPDATED_BY
, PP.DATE_EFFECTIVE
, PP.LAST_UPDATE_LOGIN
, PP.CREATED_BY
, PP.CREATION_DATE
, PP.REQUEST_ID
, PP.PROGRAM_APPLICATION_ID
, PP.PROGRAM_ID
, PP.PROGRAM_UPDATE_DATE
, PPD.POSITION_DEFINITION_ID
, PP.COMMENTS
, HR_GENERAL.GET_POSITION_DATE_END(PP.POSITION_ID)
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PP.POSITION_ID)
, PP.LOCATION_ID
, PPD.ID_FLEX_NUM
, PPD.SUMMARY_FLAG
, PPD.ENABLED_FLAG
, PPD.START_DATE_ACTIVE
, PPD.END_DATE_ACTIVE
, PPD.SEGMENT1
, PPD.SEGMENT2
, PPD.SEGMENT3
, PPD.SEGMENT4
, PPD.SEGMENT5
, PPD.SEGMENT6
, PPD.SEGMENT7
, PPD.SEGMENT8
, PPD.SEGMENT9
, PPD.SEGMENT10
, PPD.SEGMENT11
, PPD.SEGMENT12
, PPD.SEGMENT13
, PPD.SEGMENT14
, PPD.SEGMENT15
, PPD.SEGMENT16
, PPD.SEGMENT17
, PPD.SEGMENT18
, PPD.SEGMENT19
, PPD.SEGMENT20
, PPD.SEGMENT21
, PPD.SEGMENT22
, PPD.SEGMENT23
, PPD.SEGMENT24
, PPD.SEGMENT25
, PPD.SEGMENT26
, PPD.SEGMENT27
, PPD.SEGMENT28
, PPD.SEGMENT29
, PPD.SEGMENT30
, PER.FULL_NAME
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_WORKER_USER_PERSON_TYPE(SES.EFFECTIVE_DATE
, PER.PERSON_ID)
FROM HR_ALL_POSITIONS PP
, PER_POSITION_DEFINITIONS PPD
, PER_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
, FND_SESSIONS SES
WHERE PPD.POSITION_DEFINITION_ID = HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PP.POSITION_ID)
AND PER.PERSON_ID = ASG.PERSON_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS != 'TERM_ASSIGN'
AND SES.EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.ASSIGNMENT_TYPE IN ( 'E'
, 'C')
AND ASG.POSITION_ID = PP.POSITION_ID
AND SES.SESSION_ID = USERENV ('SESSIONID')