Product: | PER - Human Resources |
---|---|
Description: | Business view template from which the flexfield view is generated. |
Implementation/DBA Data: | APPS.HRFV_COMPETENCE_PROFILES |
SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, CPN.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.NAME PROFICIENCY_LEVEL
, '_DF:PER:PER_COMPETENCE_ELEMENTS:CMP'
, '_KF:PER:CMP:CDEF'
, CMP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, CMP.COMPETENCE_ID COMPETENCE_ID
, CMP.PERSON_ID PERSON_ID
FROM PER_RATING_LEVELS RAT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PEOPLE_X PEO
, PER_COMPETENCES CPN
, PER_COMPETENCE_DEFINITIONS CDEF
, PER_COMPETENCE_ELEMENTS CMP
WHERE CPN.COMPETENCE_ID = CMP.COMPETENCE_ID
AND CMP.PROFICIENCY_LEVEL_ID = RAT.RATING_LEVEL_ID (+)
AND CMP.PERSON_ID = PEO.PERSON_ID
AND CMP.TYPE = 'PERSONAL'
AND CDEF.COMPETENCE_DEFINITION_ID = CPN.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