VIEW: APPS.HR_ALL_POSITIONS_D

Source

View Text - Preformatted

SELECT PSF.POSITION_ID, PSF.EFFECTIVE_START_DATE, PSF.EFFECTIVE_END_DATE, SUBSTR(HR_GENERAL.DECODE_AVAILABILITY_STATUS(PSF.AVAILABILITY_STATUS_ID), 1, 200) AVAILABILITY_STATUS_DESC, HR_GENERAL.DECODE_STEP(PSF.ENTRY_STEP_ID,DATE_EFFECTIVE) ENTRY_STEP_DESC, SUBSTR(HR_GENERAL.DECODE_JOB(PSF.JOB_ID), 1, 200) JOB_DESC, SUBSTR(HR_GENERAL.DECODE_LOCATION(PSF.LOCATION_ID) , 1, 200) LOCATION_DESC, SUBSTR(HR_GENERAL.DECODE_ORGANIZATION(PSF.ORGANIZATION_ID), 1, 200) ORGANIZATION_DESC, SUBSTR(HR_GENERAL.DECODE_PAYROLL(PSF.PAY_FREQ_PAYROLL_ID), 1, 200) PAY_FREQ_PAYROLL_DESC, SUBSTR(HR_GENERAL.DECODE_POSITION_LATEST_NAME(PSF.RELIEF_POSITION_ID, PSF.EFFECTIVE_START_DATE), 1, 200) RELIEF_POSITION_DESC, SUBSTR(HR_GENERAL.DECODE_GRADE(PSF.ENTRY_GRADE_ID), 1, 200) SALARY_ENTRY_GRADE_DESC, SUBSTR(HR_GENERAL.DECODE_POSITION_LATEST_NAME(PSF.SUCCESSOR_POSITION_ID, PSF.EFFECTIVE_START_DATE), 1, 200) SUCCESSOR_POSITION_DESC, SUBSTR(HR_GENERAL.DECODE_POSITION_LATEST_NAME(PSF.SUPERVISOR_POSITION_ID, PSF.EFFECTIVE_START_DATE), 1, 200) SUPERVISOR_POSITION_DESC, SUBSTR(HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE' ,PSF.BARGAINING_UNIT_CD), 1, 200) BARGAINING_UNIT_DESC, PSF.DATE_EFFECTIVE, HR_GENERAL.GET_POSITION_DATE_END (PSF.POSITION_ID), SUBSTR(HR_GENERAL.DECODE_LOOKUP('FREQUENCY',PSF.FREQUENCY), 1, 200) FREQUENCY_DESC, PSF.FTE, PSF.MAX_PERSONS, FND_FLEX_EXT.GET_SEGS('PER', 'POS', PPD.ID_FLEX_NUM, PSF.POSITION_DEFINITION_ID) name , PSF.OVERLAP_PERIOD, SUBSTR(HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS' , PSF.OVERLAP_UNIT_CD), 1, 200) OVERLAP_UNIT_DESC, PSF.PERMANENT_TEMPORARY_FLAG, SUBSTR(HR_GENERAL.DECODE_LOOKUP('POSITION_TYPE' , PSF.POSITION_TYPE), 1, 200) POSITION_TYPE_DESC, PSF.POSTING_DESCRIPTION, PSF.PROBATION_PERIOD, SUBSTR(HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS' ,PSF.PROBATION_PERIOD_UNIT_CD), 1, 200) PROBATION_UNIT_DESC, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO' ,PSF.REPLACEMENT_REQUIRED_FLAG), 1, 200) REPLACEMENT_REQUIRED_DESC, PSF.SEASONAL_FLAG, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DAY_CODE' , PSF.TERM_START_DAY_CD), 1, 200) TERM_START_DAY_DESC, SUBSTR(HR_GENERAL.DECODE_LOOKUP('MONTH_CODE' , PSF.TERM_START_MONTH_CD), 1, 200) TERM_START_MONTH_DESC, PSF.TIME_NORMAL_FINISH, PSF.WORKING_HOURS, PSF.LAST_UPDATE_DATE, USR.USER_NAME LAST_UPDATED_BY FROM PER_POSITION_DEFINITIONS PPD, HR_ALL_POSITIONS_F PSF, PAY_GRADE_RULES PGR, FND_USER USR WHERE PPD.POSITION_DEFINITION_ID = PSF.POSITION_DEFINITION_ID AND PSF.ENTRY_GRADE_RULE_ID = PGR.GRADE_RULE_ID(+) AND PSF.LAST_UPDATED_BY = USR.USER_ID(+)

View Text - HTML Formatted

SELECT PSF.POSITION_ID
, PSF.EFFECTIVE_START_DATE
, PSF.EFFECTIVE_END_DATE
, SUBSTR(HR_GENERAL.DECODE_AVAILABILITY_STATUS(PSF.AVAILABILITY_STATUS_ID)
, 1
, 200) AVAILABILITY_STATUS_DESC
, HR_GENERAL.DECODE_STEP(PSF.ENTRY_STEP_ID
, DATE_EFFECTIVE) ENTRY_STEP_DESC
, SUBSTR(HR_GENERAL.DECODE_JOB(PSF.JOB_ID)
, 1
, 200) JOB_DESC
, SUBSTR(HR_GENERAL.DECODE_LOCATION(PSF.LOCATION_ID)
, 1
, 200) LOCATION_DESC
, SUBSTR(HR_GENERAL.DECODE_ORGANIZATION(PSF.ORGANIZATION_ID)
, 1
, 200) ORGANIZATION_DESC
, SUBSTR(HR_GENERAL.DECODE_PAYROLL(PSF.PAY_FREQ_PAYROLL_ID)
, 1
, 200) PAY_FREQ_PAYROLL_DESC
, SUBSTR(HR_GENERAL.DECODE_POSITION_LATEST_NAME(PSF.RELIEF_POSITION_ID
, PSF.EFFECTIVE_START_DATE)
, 1
, 200) RELIEF_POSITION_DESC
, SUBSTR(HR_GENERAL.DECODE_GRADE(PSF.ENTRY_GRADE_ID)
, 1
, 200) SALARY_ENTRY_GRADE_DESC
, SUBSTR(HR_GENERAL.DECODE_POSITION_LATEST_NAME(PSF.SUCCESSOR_POSITION_ID
, PSF.EFFECTIVE_START_DATE)
, 1
, 200) SUCCESSOR_POSITION_DESC
, SUBSTR(HR_GENERAL.DECODE_POSITION_LATEST_NAME(PSF.SUPERVISOR_POSITION_ID
, PSF.EFFECTIVE_START_DATE)
, 1
, 200) SUPERVISOR_POSITION_DESC
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE'
, PSF.BARGAINING_UNIT_CD)
, 1
, 200) BARGAINING_UNIT_DESC
, PSF.DATE_EFFECTIVE
, HR_GENERAL.GET_POSITION_DATE_END (PSF.POSITION_ID)
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PSF.FREQUENCY)
, 1
, 200) FREQUENCY_DESC
, PSF.FTE
, PSF.MAX_PERSONS
, FND_FLEX_EXT.GET_SEGS('PER'
, 'POS'
, PPD.ID_FLEX_NUM
, PSF.POSITION_DEFINITION_ID) NAME
, PSF.OVERLAP_PERIOD
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, PSF.OVERLAP_UNIT_CD)
, 1
, 200) OVERLAP_UNIT_DESC
, PSF.PERMANENT_TEMPORARY_FLAG
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('POSITION_TYPE'
, PSF.POSITION_TYPE)
, 1
, 200) POSITION_TYPE_DESC
, PSF.POSTING_DESCRIPTION
, PSF.PROBATION_PERIOD
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('QUALIFYING_UNITS'
, PSF.PROBATION_PERIOD_UNIT_CD)
, 1
, 200) PROBATION_UNIT_DESC
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PSF.REPLACEMENT_REQUIRED_FLAG)
, 1
, 200) REPLACEMENT_REQUIRED_DESC
, PSF.SEASONAL_FLAG
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PSF.TERM_START_DAY_CD)
, 1
, 200) TERM_START_DAY_DESC
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PSF.TERM_START_MONTH_CD)
, 1
, 200) TERM_START_MONTH_DESC
, PSF.TIME_NORMAL_FINISH
, PSF.WORKING_HOURS
, PSF.LAST_UPDATE_DATE
, USR.USER_NAME LAST_UPDATED_BY
FROM PER_POSITION_DEFINITIONS PPD
, HR_ALL_POSITIONS_F PSF
, PAY_GRADE_RULES PGR
, FND_USER USR
WHERE PPD.POSITION_DEFINITION_ID = PSF.POSITION_DEFINITION_ID
AND PSF.ENTRY_GRADE_RULE_ID = PGR.GRADE_RULE_ID(+)
AND PSF.LAST_UPDATED_BY = USR.USER_ID(+)