Product: | PQH - Public Sector HR |
---|---|
Description: | |
Implementation/DBA Data: | APPS.PQH_CORPS_DEFINITIONS_V |
SELECT CPD.CORPS_DEFINITION_ID CORPS_DEFINITION_ID
, CPD.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, CPD.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, CPD.NAME NAME
, HR_GENERAL.DECODE_LOOKUP('PQH_CORPS_STATUS'
, CPD.STATUS_CD) STATUS_DESC
, CPD.STATUS_CD STATUS_CD
, CPD.RETIREMENT_AGE RETIREMENT_AGE
, HR_GENERAL.DECODE_LOOKUP('PQH_CORPS_CATEGORY'
, CPD.CATEGORY_CD) CATEGORY_DESC
, CPD.CATEGORY_CD CATEGORY_CD
, GRD.NAME STARTING_GRADE
, SPT.SPINAL_POINT STARTING_STEP
, PSP.NAME SCALE
, GRD.GRADE_ID STARTING_GRADE_ID
, CPD.STARTING_GRADE_STEP_ID STARTING_GRADE_STEP_ID
, HR_GENERAL.DECODE_LOOKUP('FR_PQH_ORG_CATEGORY'
, CPD.NATURE_TYPE_CD) NATURE_TYPE_DESC
, CPD.NATURE_TYPE_CD NATURE_TYPE_CD
, HR_GENERAL.DECODE_LOOKUP('PQH_CORPS_TYPE'
, CPD.CORPS_TYPE_CD) TYPE_DESC
, CPD.CORPS_TYPE_CD TYPE_CD
, CPD.TASK_DESC TASKS
, CPD.SECONDMENT_THRESHOLD SECONDMENT_THRESHOLD
, CPD.NORMAL_HOURS NORMAL_HOURS
, CPD.NORMAL_HOURS_FREQUENCY NORMAL_HOURS_FREQUENCY
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, CPD.NORMAL_HOURS_FREQUENCY) NOR_FREQUENCY_DESC
, CPD.MINIMUM_HOURS MINIMUM_HOURS
, CPD.MINIMUM_HOURS_FREQUENCY MINIMUM_HOURS_FREQUENCY
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, CPD.MINIMUM_HOURS_FREQUENCY) MIN_FREQUENCY_DESC
, CPD.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, CPD.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, CPD.RECRUITMENT_END_DATE RECRUITMENT_END_DATE
FROM PQH_CORPS_DEFINITIONS CPD
, PER_GRADES GRD
, PER_GRADE_SPINES GSP
, PER_SPINAL_POINT_STEPS SPS
, PER_SPINAL_POINTS SPT
, PER_PARENT_SPINES PSP
WHERE CPD.STARTING_GRADE_STEP_ID = SPS.STEP_ID(+)
AND SPS.GRADE_SPINE_ID = GSP.GRADE_SPINE_ID(+)
AND GSP.PARENT_SPINE_ID = PSP.PARENT_SPINE_ID(+)
AND SPS.SPINAL_POINT_ID = SPT.SPINAL_POINT_ID(+)
AND GSP.GRADE_ID = GRD.GRADE_ID(+)