VIEW: APPS.PER_POS_STRUCTURE_ELEMENTS_V3

Source

View Text - Preformatted

SELECT PSE.ROWID , PSE.POS_STRUCTURE_ELEMENT_ID , PSE.BUSINESS_GROUP_ID , PSE.POS_STRUCTURE_VERSION_ID , PSE.SUBORDINATE_POSITION_ID , POS.ORGANIZATION_ID , PSE.PARENT_POSITION_ID , PSE.REQUEST_ID , PSE.PROGRAM_APPLICATION_ID , PSE.PROGRAM_ID , PSE.PROGRAM_UPDATE_DATE , PSE.LAST_UPDATE_DATE , PSE.LAST_UPDATED_BY , PSE.LAST_UPDATE_LOGIN , PSE.CREATED_BY , PSE.CREATION_DATE , hr_general.decode_position_latest_name(pos.position_id) , 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_POSITIONS_F POS, PER_POS_STRUCTURE_ELEMENTS PSE, PER_ALL_PEOPLE_F PER, PER_ASSIGNMENTS_F ASG, PER_ASSIGNMENT_STATUS_TYPES AST, FND_SESSIONS SES WHERE PSE.SUBORDINATE_POSITION_ID = POS.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 = POS.POSITION_ID AND SES.SESSION_ID = USERENV ('sessionid') and POS.EFFECTIVE_END_DATE = TO_DATE('31124712','DDMMYYYY')

View Text - HTML Formatted

SELECT PSE.ROWID
, PSE.POS_STRUCTURE_ELEMENT_ID
, PSE.BUSINESS_GROUP_ID
, PSE.POS_STRUCTURE_VERSION_ID
, PSE.SUBORDINATE_POSITION_ID
, POS.ORGANIZATION_ID
, PSE.PARENT_POSITION_ID
, PSE.REQUEST_ID
, PSE.PROGRAM_APPLICATION_ID
, PSE.PROGRAM_ID
, PSE.PROGRAM_UPDATE_DATE
, PSE.LAST_UPDATE_DATE
, PSE.LAST_UPDATED_BY
, PSE.LAST_UPDATE_LOGIN
, PSE.CREATED_BY
, PSE.CREATION_DATE
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(POS.POSITION_ID)
, 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_POSITIONS_F POS
, PER_POS_STRUCTURE_ELEMENTS PSE
, PER_ALL_PEOPLE_F PER
, PER_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
, FND_SESSIONS SES
WHERE PSE.SUBORDINATE_POSITION_ID = POS.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 = POS.POSITION_ID
AND SES.SESSION_ID = USERENV ('SESSIONID')
AND POS.EFFECTIVE_END_DATE = TO_DATE('31124712'
, 'DDMMYYYY')