View: PQH_PTX_HISTORY_V

Product:	PQH - Public Sector HR
Description:
Implementation/DBA Data:	APPS.PQH_PTX_HISTORY_V

View Text

SELECT PSF.POSITION_ID
, PSF.EFFECTIVE_START_DATE
, PQH_UTILITY.GET_POS_REC_EED(PSF.POSITION_ID
, PSF.EFFECTIVE_START_DATE)
, HR_GENERAL.DECODE_LOOKUP('PQH_TRANSACTION_STATUS'
, 'APPLIED')
, PSF.DATE_EFFECTIVE
, PSF.NAME
, HR_GENERAL.DECODE_LOOKUP('POSITION_TYPE'
, PSF.POSITION_TYPE)
, PSF.PERMANENT_TEMPORARY_FLAG
, PSF.SEASONAL_FLAG
, HR_GENERAL.DECODE_LOCATION(PSF.LOCATION_ID)
, HR_GENERAL.DECODE_ORGANIZATION(PSF.ORGANIZATION_ID)
, PSF.CURRENT_ORG_PROP_END_DATE
, HR_GENERAL.DECODE_JOB(PSF.JOB_ID)
, PSF.CURRENT_JOB_PROP_END_DATE
, HR_GENERAL.DECODE_AVAILABILITY_STATUS(PSF.AVAILABILITY_STATUS_ID)
, PSF.AVAIL_STATUS_PROP_END_DATE
, PSF.FTE
, PSF.MAX_PERSONS
, HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE'
, PSF.BARGAINING_UNIT_CD)
, PSF.EARLIEST_HIRE_DATE
, PSF.FILL_BY_DATE
, PSF.PERMIT_RECRUITMENT_FLAG
, HR_GENERAL.DECODE_PAYROLL(PSF.PAY_FREQ_PAYROLL_ID)
, HR_GENERAL.DECODE_PAY_BASIS(PSF.PAY_BASIS_ID)
, HR_GENERAL.DECODE_GRADE(PSF.ENTRY_GRADE_ID)
, HR_GENERAL.DECODE_STEP(PSF.ENTRY_STEP_ID
, PSF.DATE_EFFECTIVE)
, PQH_UTILITY.DECODE_GRADE_RULE(PSF.ENTRY_GRADE_RULE_ID
, 'VALUE')
, HR_GENERAL.DECODE_GRADE_RULE(PSF.ENTRY_GRADE_RULE_ID)
, PQH_UTILITY.DECODE_GRADE_RULE(PSF.ENTRY_GRADE_RULE_ID
, 'MIN')
, PQH_UTILITY.DECODE_GRADE_RULE(PSF.ENTRY_GRADE_RULE_ID
, 'MID')
, PQH_UTILITY.DECODE_GRADE_RULE(PSF.ENTRY_GRADE_RULE_ID
, 'MAX')
, PSF.PROBATION_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PSF.PROBATION_PERIOD_UNIT_CD)
, PSF.OVERLAP_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PSF.OVERLAP_UNIT_CD)
, PSF.PROPOSED_FTE_FOR_LAYOFF
, PSF.PROPOSED_DATE_FOR_LAYOFF
, PSF.WORKING_HOURS
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PSF.FREQUENCY)
, PSF.TIME_NORMAL_START
, PSF.TIME_NORMAL_FINISH
, HR_GENERAL.DECODE_LOOKUP('PER_LENGTHS_OF_SERVICE'
, DPF.SERVICE_MINIMUM)
, HR_GENERAL.DECODE_LOOKUP('PER_WORK_SCHEDULE'
, DPF.WORK_SCHEDULE)
, HR_GENERAL.DECODE_LOOKUP('PER_TIME_SCALES'
, DPF.WORK_DURATION)
, HR_GENERAL.DECODE_LOOKUP('PER_FTE_CAPACITY'
, DPF.FTE_CAPACITY)
, HR_GENERAL.DECODE_PERSON_NAME(PSF.SUPERVISOR_ID)
, PSF.REPLACEMENT_REQUIRED_FLAG
, PSF.WORKS_COUNCIL_APPROVAL_FLAG
, HR_GENERAL.DECODE_POSITION(PSF.SUPERVISOR_POSITION_ID)
, HR_GENERAL.DECODE_POSITION(PSF.RELIEF_POSITION_ID)
, HR_GENERAL.DECODE_POSITION(PSF.SUCCESSOR_POSITION_ID)
, PSF.WORK_PERIOD_TYPE_CD
, HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PSF.TERM_START_DAY_CD)
, HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PSF.TERM_START_MONTH_CD)
, HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PSF.WORK_TERM_END_DAY_CD)
, HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PSF.WORK_TERM_END_MONTH_CD)
, HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PSF.PAY_TERM_END_DAY_CD)
, HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PSF.PAY_TERM_END_MONTH_CD)
, DPF.WORK_ANY_LOCATION
, DPF.RELOCATE_DOMESTICALLY
, DPF.TRAVEL_REQUIRED
, HR_GENERAL.DECODE_LOCATION(DPF.LOCATION1)
, HR_GENERAL.DECODE_LOCATION(DPF.LOCATION2)
, HR_GENERAL.DECODE_LOCATION(DPF.LOCATION3)
, DPF.WORK_ANY_COUNTRY
, DPF.RELOCATE_INTERNATIONALLY
, DPF.PASSPORT_REQUIRED
, HR_GENERAL.DECODE_TERRITORY(DPF.COUNTRY1)
, HR_GENERAL.DECODE_TERRITORY(DPF.COUNTRY2)
, HR_GENERAL.DECODE_TERRITORY(DPF.COUNTRY3)
, DPF.OTHER_REQUIREMENTS
, PSF.POSTING_DESCRIPTION
, PSF.SECURITY_REQUIREMENTS
, PSF.AMENDMENT_DATE
, PSF.AMENDMENT_REF_NUMBER
, PSF.AMENDMENT_RECOMMENDATION
FROM HR_ALL_POSITIONS_F PSF
, PER_DEPLOYMENT_FACTORS DPF
WHERE PSF.POSITION_ID = DPF.POSITION_ID(+)
AND EFFECTIVE_START_DATE NOT IN (SELECT ACTION_DATE
FROM PQH_POSITION_TRANSACTIONS PTX
WHERE PTX.POSITION_ID = PSF.POSITION_ID
AND TRANSACTION_STATUS = 'SUBMITTED') UNION SELECT PTX.POSITION_ID
, PTX.ACTION_DATE
, PQH_UTILITY.GET_POS_REC_EED(PTX.POSITION_ID
, PTX.ACTION_DATE)
, HR_GENERAL.DECODE_LOOKUP('PQH_TRANSACTION_STATUS'
, PTX.TRANSACTION_STATUS)
, PTX.DATE_EFFECTIVE
, PTX.NAME
, HR_GENERAL.DECODE_LOOKUP('POSITION_TYPE'
, PTX.POSITION_TYPE)
, PTX.PERMANENT_TEMPORARY_FLAG
, PTX.SEASONAL_FLAG
, HR_GENERAL.DECODE_LOCATION(PTX.LOCATION_ID)
, HR_GENERAL.DECODE_ORGANIZATION(PTX.ORGANIZATION_ID)
, PTX.CURRENT_ORG_PROP_END_DATE
, HR_GENERAL.DECODE_JOB(PTX.JOB_ID)
, PTX.CURRENT_JOB_PROP_END_DATE
, HR_GENERAL.DECODE_AVAILABILITY_STATUS(PTX.AVAILABILITY_STATUS_ID)
, PTX.AVAIL_STATUS_PROP_END_DATE
, PTX.FTE
, PTX.MAX_PERSONS
, HR_GENERAL.DECODE_LOOKUP('BARGAINING_UNIT_CODE'
, PTX.BARGAINING_UNIT_CD)
, PTX.EARLIEST_HIRE_DATE
, PTX.FILL_BY_DATE
, PTX.PERMIT_RECRUITMENT_FLAG
, HR_GENERAL.DECODE_PAYROLL(PTX.PAY_FREQ_PAYROLL_ID)
, HR_GENERAL.DECODE_PAY_BASIS(PTX.PAY_BASIS_ID)
, HR_GENERAL.DECODE_GRADE(PTX.ENTRY_GRADE_ID)
, HR_GENERAL.DECODE_STEP(PTX.ENTRY_STEP_ID
, PTX.DATE_EFFECTIVE)
, PQH_UTILITY.DECODE_GRADE_RULE(PTX.ENTRY_GRADE_RULE_ID
, 'VALUE')
, HR_GENERAL.DECODE_GRADE_RULE(PTX.ENTRY_GRADE_RULE_ID)
, PQH_UTILITY.DECODE_GRADE_RULE(PTX.ENTRY_GRADE_RULE_ID
, 'MIN')
, PQH_UTILITY.DECODE_GRADE_RULE(PTX.ENTRY_GRADE_RULE_ID
, 'MID')
, PQH_UTILITY.DECODE_GRADE_RULE(PTX.ENTRY_GRADE_RULE_ID
, 'MAX')
, PTX.PROBATION_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PTX.PROBATION_PERIOD_UNIT_CD)
, PTX.OVERLAP_PERIOD
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PTX.OVERLAP_UNIT_CD)
, PTX.PROPOSED_FTE_FOR_LAYOFF
, PTX.PROPOSED_DATE_FOR_LAYOFF
, PTX.WORKING_HOURS
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, PTX.FREQUENCY)
, PTX.TIME_NORMAL_START
, PTX.TIME_NORMAL_FINISH
, HR_GENERAL.DECODE_LOOKUP('PER_LENGTHS_OF_SERVICE'
, PTX.SERVICE_MINIMUM)
, HR_GENERAL.DECODE_LOOKUP('PER_WORK_SCHEDULE'
, PTX.WORK_SCHEDULE)
, HR_GENERAL.DECODE_LOOKUP('PER_TIME_SCALES'
, PTX.WORK_DURATION)
, HR_GENERAL.DECODE_LOOKUP('PER_FTE_CAPACITY'
, PTX.FTE_CAPACITY)
, HR_GENERAL.DECODE_PERSON_NAME(PTX.SUPERVISOR_ID)
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PTX.REPLACEMENT_REQUIRED_FLAG)
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PTX.WORKS_COUNCIL_APPROVAL_FLAG)
, HR_GENERAL.DECODE_POSITION(PTX.SUPERVISOR_POSITION_ID)
, HR_GENERAL.DECODE_POSITION(PTX.RELIEF_POSITION_ID)
, HR_GENERAL.DECODE_POSITION(PTX.SUCCESSOR_POSITION_ID)
, PTX.WORK_PERIOD_TYPE_CD
, HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PTX.TERM_START_DAY_CD)
, HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PTX.TERM_START_MONTH_CD)
, HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PTX.WORK_TERM_END_DAY_CD)
, HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PTX.WORK_TERM_END_MONTH_CD)
, HR_GENERAL.DECODE_LOOKUP('DAY_CODE'
, PTX.PAY_TERM_END_DAY_CD)
, HR_GENERAL.DECODE_LOOKUP('MONTH_CODE'
, PTX.PAY_TERM_END_MONTH_CD)
, PTX.WORK_ANY_LOCATION
, PTX.RELOCATE_DOMESTICALLY
, PTX.TRAVEL_REQUIRED
, HR_GENERAL.DECODE_LOCATION(PTX.LOCATION1)
, HR_GENERAL.DECODE_LOCATION(PTX.LOCATION2)
, HR_GENERAL.DECODE_LOCATION(PTX.LOCATION3)
, PTX.WORK_ANY_COUNTRY
, PTX.RELOCATE_INTERNATIONALLY
, PTX.PASSPORT_REQUIRED
, HR_GENERAL.DECODE_TERRITORY(PTX.COUNTRY1) COUNTRY1_DESC
, HR_GENERAL.DECODE_TERRITORY(PTX.COUNTRY2) COUNTRY2_DESC
, HR_GENERAL.DECODE_TERRITORY(PTX.COUNTRY3) COUNTRY3_DESC
, PTX.OTHER_REQUIREMENTS
, PTX.POSTING_DESCRIPTION
, PTX.SECURITY_REQUIREMENTS
, PTX.AMENDMENT_DATE
, PTX.AMENDMENT_REF_NUMBER
, PTX.AMENDMENT_RECOMMENDATION
FROM PQH_POSITION_TRANSACTIONS PTX
WHERE PTX.TRANSACTION_STATUS = 'SUBMITTED'

Columns