VIEW: APPS.HRFV_COMPETENCE_PROFILES

Source

View Text - Preformatted

SELECT bgrt.name business_group_name ,peo.full_name person_name ,cvl.name competence_name ,cmp.effective_date_from date_from ,cmp.effective_date_to date_to ,hr_bis.bis_decode_lookup('PROFICIENCY_SOURCE', cmp.source_of_proficiency_level) proficiency_rating_source ,hr_bis.bis_decode_lookup('CERTIFICATION_METHOD', cmp.certification_method) certification_method ,cmp.certification_date certification_date ,cmp.next_certification_date next_review_date ,rat.step_value step_value ,rtx.name proficiency_level ,hr_bis.bis_decode_lookup('YES_NO',peo.current_employee_flag) current_employee_flag ,hr_bis.bis_decode_lookup('YES_NO',peo.current_applicant_flag) current_applicant_flag ,'_DF:PER:PER_COMPETENCE_ELEMENTS:cmp' ,'_KF:PER:CMP:cdef' ,peo.current_employee_flag current_employee_flag_code ,peo.current_applicant_flag current_applicant_flag_code ,cmp.business_group_id business_group_id ,cmp.competence_id competence_id ,cmp.person_id person_id ,cmp.proficiency_level_id proficiency_level_id FROM per_rating_levels rat ,per_rating_levels_tl rtx ,hr_all_organization_units_tl bgrt ,per_people_x peo ,per_competences_vl cvl ,per_competence_definitions cdef ,per_competence_elements cmp WHERE cvl.competence_id = cmp.competence_id AND cmp.proficiency_level_id = rat.rating_level_id (+) AND cmp.proficiency_level_id = rtx.rating_level_id (+) AND rtx.language(+) = userenv('LANG') AND cmp.person_id = peo.person_id AND cmp.type = 'PERSONAL' AND cdef.competence_definition_id = cvl.competence_definition_id AND TRUNC(sysdate) BETWEEN NVL(peo.effective_start_date,TRUNC(sysdate)) AND NVL(peo.effective_end_date,TRUNC(sysdate)) AND cmp.business_group_id = bgrt.organization_id (+) AND bgrt.language = userenv('LANG') AND (cmp.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, cmp.business_group_id) OR cmp.business_group_id IS NULL) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, CVL.NAME COMPETENCE_NAME
, CMP.EFFECTIVE_DATE_FROM DATE_FROM
, CMP.EFFECTIVE_DATE_TO DATE_TO
, HR_BIS.BIS_DECODE_LOOKUP('PROFICIENCY_SOURCE'
, CMP.SOURCE_OF_PROFICIENCY_LEVEL) PROFICIENCY_RATING_SOURCE
, HR_BIS.BIS_DECODE_LOOKUP('CERTIFICATION_METHOD'
, CMP.CERTIFICATION_METHOD) CERTIFICATION_METHOD
, CMP.CERTIFICATION_DATE CERTIFICATION_DATE
, CMP.NEXT_CERTIFICATION_DATE NEXT_REVIEW_DATE
, RAT.STEP_VALUE STEP_VALUE
, RTX.NAME PROFICIENCY_LEVEL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_EMPLOYEE_FLAG) CURRENT_EMPLOYEE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_APPLICANT_FLAG) CURRENT_APPLICANT_FLAG
, '_DF:PER:PER_COMPETENCE_ELEMENTS:CMP'
, '_KF:PER:CMP:CDEF'
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, CMP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, CMP.COMPETENCE_ID COMPETENCE_ID
, CMP.PERSON_ID PERSON_ID
, CMP.PROFICIENCY_LEVEL_ID PROFICIENCY_LEVEL_ID
FROM PER_RATING_LEVELS RAT
, PER_RATING_LEVELS_TL RTX
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PEOPLE_X PEO
, PER_COMPETENCES_VL CVL
, PER_COMPETENCE_DEFINITIONS CDEF
, PER_COMPETENCE_ELEMENTS CMP
WHERE CVL.COMPETENCE_ID = CMP.COMPETENCE_ID
AND CMP.PROFICIENCY_LEVEL_ID = RAT.RATING_LEVEL_ID (+)
AND CMP.PROFICIENCY_LEVEL_ID = RTX.RATING_LEVEL_ID (+)
AND RTX.LANGUAGE(+) = USERENV('LANG')
AND CMP.PERSON_ID = PEO.PERSON_ID
AND CMP.TYPE = 'PERSONAL'
AND CDEF.COMPETENCE_DEFINITION_ID = CVL.COMPETENCE_DEFINITION_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PEO.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PEO.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND CMP.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID (+)
AND BGRT.LANGUAGE = USERENV('LANG')
AND (CMP.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, CMP.BUSINESS_GROUP_ID) OR CMP.BUSINESS_GROUP_ID IS NULL) WITH READ ONLY