View: HRFV_REQUIRED_COMPETENCES

Product:	PER - Human Resources
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.HRFV_REQUIRED_COMPETENCES

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ORGST.NAME ORGANIZATION_NAME
, POS.NAME POSITION_NAME
, JOBS.NAME JOB_NAME
, GRD.NAME GRADE_NAME
, CPN.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(RATL1.NAME
, ''
, ''
, ' - ' || RATL1.NAME) MIN_PROFICIENCY
, RATL2.STEP_VALUE || DECODE(RATL2.NAME
, ''
, ''
, ' - ' || RATL2.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
, GRD.GRADE_ID GRADE_ID
FROM PER_RATING_LEVELS RATL1
, PER_RATING_LEVELS RATL2
, PER_VALID_GRADES VGRD
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES GRD
, PER_JOBS JOBS
, PER_ALL_POSITIONS POS
, HR_ALL_ORGANIZATION_UNITS_TL ORGST
, PER_COMPETENCES CPN
, 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 COMP.JOB_ID = JOBS.JOB_ID(+)
AND COMP.POSITION_ID = POS.POSITION_ID(+)
AND COMP.VALID_GRADE_ID = VGRD.VALID_GRADE_ID (+)
AND GRD.GRADE_ID(+) = VGRD.GRADE_ID
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.HIGH_PROFICIENCY_LEVEL_ID = RATL2.RATING_LEVEL_ID (+)
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
, POS.NAME POSITION_NAME
, JOBS.NAME JOB_NAME
, GRD.NAME GRADE_NAME
, CPN.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(RATL1.NAME
, ''
, ''
, ' - ' || RATL1.NAME) MIN_PROFICIENCY
, RATL2.STEP_VALUE || DECODE(RATL2.NAME
, ''
, ''
, ' - ' || RATL2.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
, GRD.GRADE_ID GRADE_ID
FROM PER_RATING_LEVELS RATL1
, PER_RATING_LEVELS RATL2
, PER_VALID_GRADES VGRD
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES GRD
, PER_JOBS JOBS
, PER_ALL_POSITIONS POS
, HR_ALL_ORGANIZATION_UNITS_TL ORGST
, PER_COMPETENCES CPN
, 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 COMP.JOB_ID = JOBS.JOB_ID(+)
AND COMP.VALID_GRADE_ID = VGRD.VALID_GRADE_ID (+)
AND GRD.GRADE_ID(+) = VGRD.GRADE_ID
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.HIGH_PROFICIENCY_LEVEL_ID = RATL2.RATING_LEVEL_ID (+)
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) WITH READ ONLY

Columns