VIEW: APPS.HRFV_REQUIRED_COMPETENCES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,orgsT.name organization_name ,pft.name position_name ,jbt.name job_name ,gdt.name grade_name ,cpt.name competence_name ,hr_bis.bis_decode_lookup('YES_NO', comp.mandatory) mandatory ,comp.effective_date_from date_from ,comp.effective_date_to date_to ,comp.comments comments ,ratl1.step_value || DECODE(rtl1.name,'','', ' - ' || rtl1.name) min_proficiency ,ratl2.step_value || DECODE(rtl2.name, '','',' - ' || rtl2.name) max_proficiency ,hr_bis.bis_decode_lookup('YES_NO', DECODE(NVL(to_char(comp.organization_id), NVL(to_char(comp.position_id), NVL(to_char(comp.job_id), 'Y'))) ,'Y','Y','N')) core_competence_flag ,'_DF:PER:PER_COMPETENCE_ELEMENTS:comp' ,'_KF:PER:CMP:cdef' ,comp.business_group_id business_group_id ,comp.competence_id competence_id ,comp.competence_element_id competence_element_id ,comp.enterprise_id enterprise_id ,comp.high_proficiency_level_id high_proficiency_level_id ,comp.job_id job_id ,comp.organization_id organization_id ,comp.position_id position_id ,comp.proficiency_level_id proficiency_level_id ,gdt.grade_id grade_id FROM per_rating_levels ratl1 ,per_rating_levels_tl rtl2 , per_rating_levels_tl rtl1 ,per_rating_levels ratl2 ,per_valid_grades vgrd ,hr_all_organization_units_tl bgrT ,per_grades_tl gdt ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,hr_all_organization_units_tl orgsT ,per_competences cpn ,per_competences_tl cpt ,per_competence_elements comp ,per_competence_definitions cdef WHERE cpn.competence_id = comp.competence_id AND cdef.competence_definition_id = cpn.competence_definition_id AND cpn.competence_id = cpt.competence_id AND cpt.language = userenv('LANG') AND comp.job_id = jbt.job_id(+) AND jbt.language(+) = userenv('LANG') AND comp.position_id = pft.position_id(+) AND pft.language(+) = userenv('LANG') AND comp.valid_grade_id = vgrd.valid_grade_id (+) AND gdt.grade_id(+) = vgrd.grade_id AND gdt.language(+) = userenv('LANG') AND comp.organization_id = orgsT.organization_id (+) AND decode(orgsT.organization_id, null,'X', orgsT.language) = decode(orgsT.organization_id, null,'X', userenv('LANG')) AND comp.proficiency_level_id = ratl1.rating_level_id (+) AND comp.proficiency_level_id = rtl1.rating_level_id (+) AND rtl1.language(+) = userenv('LANG') AND comp.high_proficiency_level_id = ratl2.rating_level_id (+) AND comp.high_proficiency_level_id = rtl2.rating_level_id (+) AND rtl2.language (+) = userenv('LANG') AND comp.type in ('REQUIREMENT') AND comp.business_group_id = bgrT.organization_id (+) AND bgrT.language = userenv('LANG') AND (comp.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, comp.business_group_id) OR comp.business_group_id IS NULL) UNION SELECT bgrT.name business_group_name ,orgsT.name organization_name ,pft.name position_name ,jbt.name job_name ,gdt.name grade_name ,cpt.name competence_name ,hr_bis.bis_decode_lookup('YES_NO', comp.mandatory) mandatory ,comp.effective_date_from date_from ,comp.effective_date_to date_to ,comp.comments comments ,ratl1.step_value || DECODE(rtl1.name,'','', ' - ' || rtl1.name) min_proficiency ,ratl2.step_value || DECODE(rtl2.name, '','',' - ' || rtl2.name) max_proficiency ,hr_bis.bis_decode_lookup('YES_NO', DECODE(NVL(to_char(comp.organization_id), NVL(to_char(comp.position_id), NVL(to_char(comp.job_id), 'Y'))) ,'Y','Y','N')) core_competence_flag ,'_DF:PER:PER_COMPETENCE_ELEMENTS:comp' ,'_KF:PER:CMP:cdef' ,comp.business_group_id business_group_id ,comp.competence_id competence_id ,comp.competence_element_id competence_element_id ,comp.enterprise_id enterprise_id ,comp.high_proficiency_level_id high_proficiency_level_id ,comp.job_id job_id ,comp.organization_id organization_id ,comp.position_id position_id ,comp.proficiency_level_id proficiency_level_id ,gdt.grade_id grade_id FROM per_rating_levels ratl1 , per_rating_levels_tl rtl1 ,per_rating_levels ratl2 ,per_rating_levels_tl rtl2 ,per_valid_grades vgrd ,hr_all_organization_units_tl bgrT ,per_grades_tl gdt ,per_jobs_tl jbt ,per_all_positions pos ,hr_all_positions_f_tl pft ,hr_all_organization_units_tl orgsT ,per_competences cpn ,per_competences_tl cpt ,per_competence_elements comp ,per_competence_definitions cdef WHERE cpn.competence_id = comp.competence_id AND cdef.competence_definition_id = cpn.competence_definition_id AND cpn.competence_id = cpt.competence_id AND cpt.language = userenv('LANG') AND comp.job_id = jbt.job_id(+) AND jbt.language(+) = userenv('LANG') AND comp.valid_grade_id = vgrd.valid_grade_id (+) AND gdt.grade_id(+) = vgrd.grade_id AND gdt.language(+) = userenv('LANG') AND comp.organization_id = orgsT.organization_id (+) AND decode(orgsT.organization_id, null,'X', orgsT.language) = decode(orgsT.organization_id, null,'X', userenv('LANG')) AND comp.proficiency_level_id = ratl1.rating_level_id (+) AND comp.proficiency_level_id = rtl1.rating_level_id (+) AND rtl1.language(+) = userenv('LANG') AND comp.high_proficiency_level_id = ratl2.rating_level_id (+) AND comp.high_proficiency_level_id = rtl2.rating_level_id (+) AND rtl2.language(+) = userenv('LANG') AND comp.type in ('REQUIREMENT') AND comp.business_group_id = bgrT.organization_id (+) AND bgrT.language = userenv('LANG') AND (pos.job_id = comp.job_id OR pos.organization_id = comp.organization_id) AND (comp.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, comp.business_group_id) OR comp.business_group_id IS NULL) AND pos.position_id = pft.position_id AND pft.language(+) = userenv('LANG') WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ORGST.NAME ORGANIZATION_NAME
, PFT.NAME POSITION_NAME
, JBT.NAME JOB_NAME
, GDT.NAME GRADE_NAME
, CPT.NAME COMPETENCE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, COMP.MANDATORY) MANDATORY
, COMP.EFFECTIVE_DATE_FROM DATE_FROM
, COMP.EFFECTIVE_DATE_TO DATE_TO
, COMP.COMMENTS COMMENTS
, RATL1.STEP_VALUE || DECODE(RTL1.NAME
, ''
, ''
, ' - ' || RTL1.NAME) MIN_PROFICIENCY
, RATL2.STEP_VALUE || DECODE(RTL2.NAME
, ''
, ''
, ' - ' || RTL2.NAME) MAX_PROFICIENCY
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(NVL(TO_CHAR(COMP.ORGANIZATION_ID)
, NVL(TO_CHAR(COMP.POSITION_ID)
, NVL(TO_CHAR(COMP.JOB_ID)
, 'Y')))
, 'Y'
, 'Y'
, 'N')) CORE_COMPETENCE_FLAG
, '_DF:PER:PER_COMPETENCE_ELEMENTS:COMP'
, '_KF:PER:CMP:CDEF'
, COMP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, COMP.COMPETENCE_ID COMPETENCE_ID
, COMP.COMPETENCE_ELEMENT_ID COMPETENCE_ELEMENT_ID
, COMP.ENTERPRISE_ID ENTERPRISE_ID
, COMP.HIGH_PROFICIENCY_LEVEL_ID HIGH_PROFICIENCY_LEVEL_ID
, COMP.JOB_ID JOB_ID
, COMP.ORGANIZATION_ID ORGANIZATION_ID
, COMP.POSITION_ID POSITION_ID
, COMP.PROFICIENCY_LEVEL_ID PROFICIENCY_LEVEL_ID
, GDT.GRADE_ID GRADE_ID
FROM PER_RATING_LEVELS RATL1
, PER_RATING_LEVELS_TL RTL2
, PER_RATING_LEVELS_TL RTL1
, PER_RATING_LEVELS RATL2
, PER_VALID_GRADES VGRD
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, HR_ALL_ORGANIZATION_UNITS_TL ORGST
, PER_COMPETENCES CPN
, PER_COMPETENCES_TL CPT
, PER_COMPETENCE_ELEMENTS COMP
, PER_COMPETENCE_DEFINITIONS CDEF
WHERE CPN.COMPETENCE_ID = COMP.COMPETENCE_ID
AND CDEF.COMPETENCE_DEFINITION_ID = CPN.COMPETENCE_DEFINITION_ID
AND CPN.COMPETENCE_ID = CPT.COMPETENCE_ID
AND CPT.LANGUAGE = USERENV('LANG')
AND COMP.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND COMP.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE(+) = USERENV('LANG')
AND COMP.VALID_GRADE_ID = VGRD.VALID_GRADE_ID (+)
AND GDT.GRADE_ID(+) = VGRD.GRADE_ID
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND COMP.ORGANIZATION_ID = ORGST.ORGANIZATION_ID (+)
AND DECODE(ORGST.ORGANIZATION_ID
, NULL
, 'X'
, ORGST.LANGUAGE) = DECODE(ORGST.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND COMP.PROFICIENCY_LEVEL_ID = RATL1.RATING_LEVEL_ID (+)
AND COMP.PROFICIENCY_LEVEL_ID = RTL1.RATING_LEVEL_ID (+)
AND RTL1.LANGUAGE(+) = USERENV('LANG')
AND COMP.HIGH_PROFICIENCY_LEVEL_ID = RATL2.RATING_LEVEL_ID (+)
AND COMP.HIGH_PROFICIENCY_LEVEL_ID = RTL2.RATING_LEVEL_ID (+)
AND RTL2.LANGUAGE (+) = USERENV('LANG')
AND COMP.TYPE IN ('REQUIREMENT')
AND COMP.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID (+)
AND BGRT.LANGUAGE = USERENV('LANG')
AND (COMP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, COMP.BUSINESS_GROUP_ID) OR COMP.BUSINESS_GROUP_ID IS NULL) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ORGST.NAME ORGANIZATION_NAME
, PFT.NAME POSITION_NAME
, JBT.NAME JOB_NAME
, GDT.NAME GRADE_NAME
, CPT.NAME COMPETENCE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, COMP.MANDATORY) MANDATORY
, COMP.EFFECTIVE_DATE_FROM DATE_FROM
, COMP.EFFECTIVE_DATE_TO DATE_TO
, COMP.COMMENTS COMMENTS
, RATL1.STEP_VALUE || DECODE(RTL1.NAME
, ''
, ''
, ' - ' || RTL1.NAME) MIN_PROFICIENCY
, RATL2.STEP_VALUE || DECODE(RTL2.NAME
, ''
, ''
, ' - ' || RTL2.NAME) MAX_PROFICIENCY
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(NVL(TO_CHAR(COMP.ORGANIZATION_ID)
, NVL(TO_CHAR(COMP.POSITION_ID)
, NVL(TO_CHAR(COMP.JOB_ID)
, 'Y')))
, 'Y'
, 'Y'
, 'N')) CORE_COMPETENCE_FLAG
, '_DF:PER:PER_COMPETENCE_ELEMENTS:COMP'
, '_KF:PER:CMP:CDEF'
, COMP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, COMP.COMPETENCE_ID COMPETENCE_ID
, COMP.COMPETENCE_ELEMENT_ID COMPETENCE_ELEMENT_ID
, COMP.ENTERPRISE_ID ENTERPRISE_ID
, COMP.HIGH_PROFICIENCY_LEVEL_ID HIGH_PROFICIENCY_LEVEL_ID
, COMP.JOB_ID JOB_ID
, COMP.ORGANIZATION_ID ORGANIZATION_ID
, COMP.POSITION_ID POSITION_ID
, COMP.PROFICIENCY_LEVEL_ID PROFICIENCY_LEVEL_ID
, GDT.GRADE_ID GRADE_ID
FROM PER_RATING_LEVELS RATL1
, PER_RATING_LEVELS_TL RTL1
, PER_RATING_LEVELS RATL2
, PER_RATING_LEVELS_TL RTL2
, PER_VALID_GRADES VGRD
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, PER_ALL_POSITIONS POS
, HR_ALL_POSITIONS_F_TL PFT
, HR_ALL_ORGANIZATION_UNITS_TL ORGST
, PER_COMPETENCES CPN
, PER_COMPETENCES_TL CPT
, PER_COMPETENCE_ELEMENTS COMP
, PER_COMPETENCE_DEFINITIONS CDEF
WHERE CPN.COMPETENCE_ID = COMP.COMPETENCE_ID
AND CDEF.COMPETENCE_DEFINITION_ID = CPN.COMPETENCE_DEFINITION_ID
AND CPN.COMPETENCE_ID = CPT.COMPETENCE_ID
AND CPT.LANGUAGE = USERENV('LANG')
AND COMP.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND COMP.VALID_GRADE_ID = VGRD.VALID_GRADE_ID (+)
AND GDT.GRADE_ID(+) = VGRD.GRADE_ID
AND GDT.LANGUAGE(+) = USERENV('LANG')
AND COMP.ORGANIZATION_ID = ORGST.ORGANIZATION_ID (+)
AND DECODE(ORGST.ORGANIZATION_ID
, NULL
, 'X'
, ORGST.LANGUAGE) = DECODE(ORGST.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND COMP.PROFICIENCY_LEVEL_ID = RATL1.RATING_LEVEL_ID (+)
AND COMP.PROFICIENCY_LEVEL_ID = RTL1.RATING_LEVEL_ID (+)
AND RTL1.LANGUAGE(+) = USERENV('LANG')
AND COMP.HIGH_PROFICIENCY_LEVEL_ID = RATL2.RATING_LEVEL_ID (+)
AND COMP.HIGH_PROFICIENCY_LEVEL_ID = RTL2.RATING_LEVEL_ID (+)
AND RTL2.LANGUAGE(+) = USERENV('LANG')
AND COMP.TYPE IN ('REQUIREMENT')
AND COMP.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID (+)
AND BGRT.LANGUAGE = USERENV('LANG')
AND (POS.JOB_ID = COMP.JOB_ID OR POS.ORGANIZATION_ID = COMP.ORGANIZATION_ID)
AND (COMP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, COMP.BUSINESS_GROUP_ID) OR COMP.BUSINESS_GROUP_ID IS NULL)
AND POS.POSITION_ID = PFT.POSITION_ID
AND PFT.LANGUAGE(+) = USERENV('LANG') WITH READ ONLY