VIEW: APPS.HR_ALL_POSITIONS_V1

Source

View Text - Preformatted

SELECT PSF.POSITION_ID , PSF.EFFECTIVE_START_DATE, PSF.EFFECTIVE_END_DATE, PSF.AVAILABILITY_STATUS_ID, PSF.BUSINESS_GROUP_ID, PSF.ENTRY_GRADE_ID, PSF.ENTRY_GRADE_RULE_ID, PSF.ENTRY_STEP_ID, PSF.JOB_ID, PSF.LOCATION_ID, PSF.ORGANIZATION_ID, PSF.PAY_FREQ_PAYROLL_ID, psl.position_definition_id, PSF.POSITION_TRANSACTION_ID, PSF.PRIOR_POSITION_ID, PSF.RELIEF_POSITION_ID, PSF.SUCCESSOR_POSITION_ID, PSF.SUPERVISOR_POSITION_ID, PSF.AMENDMENT_DATE, PSF.AMENDMENT_RECOMMENDATION, PSF.AMENDMENT_REF_NUMBER, PSF.BARGAINING_UNIT_CD, PSF.COMMENTS, PSF.CURRENT_JOB_PROP_END_DATE, PSF.CURRENT_ORG_PROP_END_DATE, PSF.AVAIL_STATUS_PROP_END_DATE, PSF.DATE_EFFECTIVE, HR_GENERAL.GET_POSITION_DATE_END(PSF.position_id), PSF.EARLIEST_HIRE_DATE, PSF.FILL_BY_DATE, PSF.FREQUENCY, PSF.FTE, PSF.MAX_PERSONS, PFT.name, PSF.OVERLAP_PERIOD, PSF.OVERLAP_UNIT_CD, PSF.PAY_TERM_END_DAY_CD, PSF.PAY_TERM_END_MONTH_CD, PSF.PERMANENT_TEMPORARY_FLAG, PSF.PERMIT_RECRUITMENT_FLAG, PSF.POSITION_TYPE, PSF.POSTING_DESCRIPTION, PSF.PROBATION_PERIOD, PSF.PROBATION_PERIOD_UNIT_CD, PSF.PROPOSED_FTE_FOR_LAYOFF, PSF.PROPOSED_DATE_FOR_LAYOFF, PSF.REPLACEMENT_REQUIRED_FLAG, PSF.REVIEW_FLAG, PSF.SEASONAL_FLAG, PSF.SECURITY_REQUIREMENTS, PSF.STATUS, PSF.TERM_START_DAY_CD, PSF.TERM_START_MONTH_CD, PSF.TIME_NORMAL_FINISH, PSF.TIME_NORMAL_START, PSF.UPDATE_SOURCE_CD, PSF.WORKING_HOURS, PSF.WORKS_COUNCIL_APPROVAL_FLAG, PSF.WORK_PERIOD_TYPE_CD, PSF.WORK_TERM_END_DAY_CD, PSF.WORK_TERM_END_MONTH_CD ,PSF.PAY_BASIS_ID ,PSF.SUPERVISOR_ID ,PSF.INFORMATION1 ,PSF.INFORMATION2 ,PSF.INFORMATION3 ,PSF.INFORMATION4 ,PSF.INFORMATION5 ,PSF.INFORMATION6 ,PSF.INFORMATION7 ,PSF.INFORMATION8 ,PSF.INFORMATION9 ,PSF.INFORMATION10 ,PSF.INFORMATION11 ,PSF.INFORMATION12 ,PSF.INFORMATION13 ,PSF.INFORMATION14 ,PSF.INFORMATION15 ,PSF.INFORMATION16 ,PSF.INFORMATION17 ,PSF.INFORMATION18 ,PSF.INFORMATION19 ,PSF.INFORMATION20 ,PSF.INFORMATION21 ,PSF.INFORMATION22 ,PSF.INFORMATION23 ,PSF.INFORMATION24 ,PSF.INFORMATION25 ,PSF.INFORMATION26 ,PSF.INFORMATION27 ,PSF.INFORMATION28 ,PSF.INFORMATION29 ,PSF.INFORMATION30 ,PSF.INFORMATION_CATEGORY, PSF.ATTRIBUTE1, PSF.ATTRIBUTE2, PSF.ATTRIBUTE3, PSF.ATTRIBUTE4, PSF.ATTRIBUTE5, PSF.ATTRIBUTE6 ,PSF.ATTRIBUTE7 ,PSF.ATTRIBUTE8 ,PSF.ATTRIBUTE9 ,PSF.ATTRIBUTE10 ,PSF.ATTRIBUTE11 ,PSF.ATTRIBUTE12 ,PSF.ATTRIBUTE13 ,PSF.ATTRIBUTE14 ,PSF.ATTRIBUTE15 ,PSF.ATTRIBUTE16 ,PSF.ATTRIBUTE17 ,PSF.ATTRIBUTE18 ,PSF.ATTRIBUTE19 ,PSF.ATTRIBUTE20 ,PSF.ATTRIBUTE21 ,PSF.ATTRIBUTE22 ,PSF.ATTRIBUTE23 ,PSF.ATTRIBUTE24 ,PSF.ATTRIBUTE25 ,PSF.ATTRIBUTE26 ,PSF.ATTRIBUTE27 ,PSF.ATTRIBUTE28 ,PSF.ATTRIBUTE29 ,PSF.ATTRIBUTE30 ,PSF.ATTRIBUTE_CATEGORY ,PSF.REQUEST_ID ,PSF.PROGRAM_APPLICATION_ID ,PSF.PROGRAM_ID ,PSF.PROGRAM_UPDATE_DATE ,PSF.CREATED_BY ,PSF.CREATION_DATE ,PSF.LAST_UPDATED_BY ,PSF.LAST_UPDATE_DATE ,PSF.LAST_UPDATE_LOGIN ,PSF.OBJECT_VERSION_NUMBER FROM HR_ALL_POSITIONS_F_TL PFT , HR_ALL_POSITIONS_F PSL , HR_ALL_POSITIONS_F PSF WHERE (SELECT SS.EFFECTIVE_DATE FROM FND_SESSIONS SS WHERE SS.SESSION_ID = USERENV('sessionid') ) between PSF.EFFECTIVE_START_DATE AND PSF.EFFECTIVE_END_DATE AND PSF.POSITION_ID = PSL.POSITION_ID AND PSL.EFFECTIVE_END_DATE = HR_GENERAL.END_OF_TIME AND PFT.POSITION_ID = PSL.POSITION_ID AND PFT.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT PSF.POSITION_ID
, PSF.EFFECTIVE_START_DATE
, PSF.EFFECTIVE_END_DATE
, PSF.AVAILABILITY_STATUS_ID
, PSF.BUSINESS_GROUP_ID
, PSF.ENTRY_GRADE_ID
, PSF.ENTRY_GRADE_RULE_ID
, PSF.ENTRY_STEP_ID
, PSF.JOB_ID
, PSF.LOCATION_ID
, PSF.ORGANIZATION_ID
, PSF.PAY_FREQ_PAYROLL_ID
, PSL.POSITION_DEFINITION_ID
, PSF.POSITION_TRANSACTION_ID
, PSF.PRIOR_POSITION_ID
, PSF.RELIEF_POSITION_ID
, PSF.SUCCESSOR_POSITION_ID
, PSF.SUPERVISOR_POSITION_ID
, PSF.AMENDMENT_DATE
, PSF.AMENDMENT_RECOMMENDATION
, PSF.AMENDMENT_REF_NUMBER
, PSF.BARGAINING_UNIT_CD
, PSF.COMMENTS
, PSF.CURRENT_JOB_PROP_END_DATE
, PSF.CURRENT_ORG_PROP_END_DATE
, PSF.AVAIL_STATUS_PROP_END_DATE
, PSF.DATE_EFFECTIVE
, HR_GENERAL.GET_POSITION_DATE_END(PSF.POSITION_ID)
, PSF.EARLIEST_HIRE_DATE
, PSF.FILL_BY_DATE
, PSF.FREQUENCY
, PSF.FTE
, PSF.MAX_PERSONS
, PFT.NAME
, PSF.OVERLAP_PERIOD
, PSF.OVERLAP_UNIT_CD
, PSF.PAY_TERM_END_DAY_CD
, PSF.PAY_TERM_END_MONTH_CD
, PSF.PERMANENT_TEMPORARY_FLAG
, PSF.PERMIT_RECRUITMENT_FLAG
, PSF.POSITION_TYPE
, PSF.POSTING_DESCRIPTION
, PSF.PROBATION_PERIOD
, PSF.PROBATION_PERIOD_UNIT_CD
, PSF.PROPOSED_FTE_FOR_LAYOFF
, PSF.PROPOSED_DATE_FOR_LAYOFF
, PSF.REPLACEMENT_REQUIRED_FLAG
, PSF.REVIEW_FLAG
, PSF.SEASONAL_FLAG
, PSF.SECURITY_REQUIREMENTS
, PSF.STATUS
, PSF.TERM_START_DAY_CD
, PSF.TERM_START_MONTH_CD
, PSF.TIME_NORMAL_FINISH
, PSF.TIME_NORMAL_START
, PSF.UPDATE_SOURCE_CD
, PSF.WORKING_HOURS
, PSF.WORKS_COUNCIL_APPROVAL_FLAG
, PSF.WORK_PERIOD_TYPE_CD
, PSF.WORK_TERM_END_DAY_CD
, PSF.WORK_TERM_END_MONTH_CD
, PSF.PAY_BASIS_ID
, PSF.SUPERVISOR_ID
, PSF.INFORMATION1
, PSF.INFORMATION2
, PSF.INFORMATION3
, PSF.INFORMATION4
, PSF.INFORMATION5
, PSF.INFORMATION6
, PSF.INFORMATION7
, PSF.INFORMATION8
, PSF.INFORMATION9
, PSF.INFORMATION10
, PSF.INFORMATION11
, PSF.INFORMATION12
, PSF.INFORMATION13
, PSF.INFORMATION14
, PSF.INFORMATION15
, PSF.INFORMATION16
, PSF.INFORMATION17
, PSF.INFORMATION18
, PSF.INFORMATION19
, PSF.INFORMATION20
, PSF.INFORMATION21
, PSF.INFORMATION22
, PSF.INFORMATION23
, PSF.INFORMATION24
, PSF.INFORMATION25
, PSF.INFORMATION26
, PSF.INFORMATION27
, PSF.INFORMATION28
, PSF.INFORMATION29
, PSF.INFORMATION30
, PSF.INFORMATION_CATEGORY
, PSF.ATTRIBUTE1
, PSF.ATTRIBUTE2
, PSF.ATTRIBUTE3
, PSF.ATTRIBUTE4
, PSF.ATTRIBUTE5
, PSF.ATTRIBUTE6
, PSF.ATTRIBUTE7
, PSF.ATTRIBUTE8
, PSF.ATTRIBUTE9
, PSF.ATTRIBUTE10
, PSF.ATTRIBUTE11
, PSF.ATTRIBUTE12
, PSF.ATTRIBUTE13
, PSF.ATTRIBUTE14
, PSF.ATTRIBUTE15
, PSF.ATTRIBUTE16
, PSF.ATTRIBUTE17
, PSF.ATTRIBUTE18
, PSF.ATTRIBUTE19
, PSF.ATTRIBUTE20
, PSF.ATTRIBUTE21
, PSF.ATTRIBUTE22
, PSF.ATTRIBUTE23
, PSF.ATTRIBUTE24
, PSF.ATTRIBUTE25
, PSF.ATTRIBUTE26
, PSF.ATTRIBUTE27
, PSF.ATTRIBUTE28
, PSF.ATTRIBUTE29
, PSF.ATTRIBUTE30
, PSF.ATTRIBUTE_CATEGORY
, PSF.REQUEST_ID
, PSF.PROGRAM_APPLICATION_ID
, PSF.PROGRAM_ID
, PSF.PROGRAM_UPDATE_DATE
, PSF.CREATED_BY
, PSF.CREATION_DATE
, PSF.LAST_UPDATED_BY
, PSF.LAST_UPDATE_DATE
, PSF.LAST_UPDATE_LOGIN
, PSF.OBJECT_VERSION_NUMBER
FROM HR_ALL_POSITIONS_F_TL PFT
, HR_ALL_POSITIONS_F PSL
, HR_ALL_POSITIONS_F PSF
WHERE (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID') ) BETWEEN PSF.EFFECTIVE_START_DATE
AND PSF.EFFECTIVE_END_DATE
AND PSF.POSITION_ID = PSL.POSITION_ID
AND PSL.EFFECTIVE_END_DATE = HR_GENERAL.END_OF_TIME
AND PFT.POSITION_ID = PSL.POSITION_ID
AND PFT.LANGUAGE = USERENV('LANG')