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')
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')
|
|
|