VIEW: APPS.HRI_MB_CMPTNC_RQRMNT_V

Source

View Text - Preformatted

SELECT 1 row_indicator , CASE WHEN creq.enterprise_id IS NOT NULL THEN 1 ELSE 0 END core_cmptnc_indicator , CASE WHEN creq.mandatory = 'Y' THEN 1 ELSE 0 END essntl_cmptnc_indicator , CASE WHEN creq.organization_id IS NOT NULL THEN 1 ELSE 0 END org_rqrmnt_indicator , CASE WHEN creq.job_id IS NOT NULL THEN 1 ELSE 0 END job_rqrmnt_indicator , CASE WHEN creq.valid_grade_id IS NOT NULL THEN 1 ELSE 0 END grade_rqrmnt_indicator , CASE WHEN creq.position_id IS NOT NULL THEN 1 ELSE 0 END position_rqrmnt_indicator , NVL(creq.effective_date_from,hr_general.start_of_time) effective_start_date , NVL(creq.effective_date_to,hr_general.end_of_time) effective_end_date , creq.business_group_id business_group_id , NVL(creq.organization_id,-1) organization_id , NVL(creq.job_id, -1) job_id , NVL(vgrd.grade_id,-1) grade_id , NVL(creq.position_id,-1) position_id , NVL(creq.enterprise_id,-1) enterprise_id , creq.competence_id competence_id , creq.competence_element_id competence_element_id , NVL(creq.proficiency_level_id,-1) low_level_id , NVL(creq.high_proficiency_level_id,-1) high_level_id , creq.mandatory essntl_cmptnc_flag_code , GREATEST(creq.last_update_date ,NVL(vgrd.last_update_date,hr_general.start_of_time) ) last_change_date , CASE WHEN ( creq.business_group_id IS NULL OR creq.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,creq.business_group_id) ) THEN 'Y' ELSE 'N' END bg_security_flag_code FROM per_competence_elements creq , per_valid_grades vgrd WHERE creq.type = 'REQUIREMENT' AND creq.valid_grade_id = vgrd.valid_grade_id (+) WITH READ ONLY

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, CASE WHEN CREQ.ENTERPRISE_ID IS NOT NULL THEN 1 ELSE 0 END CORE_CMPTNC_INDICATOR
, CASE WHEN CREQ.MANDATORY = 'Y' THEN 1 ELSE 0 END ESSNTL_CMPTNC_INDICATOR
, CASE WHEN CREQ.ORGANIZATION_ID IS NOT NULL THEN 1 ELSE 0 END ORG_RQRMNT_INDICATOR
, CASE WHEN CREQ.JOB_ID IS NOT NULL THEN 1 ELSE 0 END JOB_RQRMNT_INDICATOR
, CASE WHEN CREQ.VALID_GRADE_ID IS NOT NULL THEN 1 ELSE 0 END GRADE_RQRMNT_INDICATOR
, CASE WHEN CREQ.POSITION_ID IS NOT NULL THEN 1 ELSE 0 END POSITION_RQRMNT_INDICATOR
, NVL(CREQ.EFFECTIVE_DATE_FROM
, HR_GENERAL.START_OF_TIME) EFFECTIVE_START_DATE
, NVL(CREQ.EFFECTIVE_DATE_TO
, HR_GENERAL.END_OF_TIME) EFFECTIVE_END_DATE
, CREQ.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, NVL(CREQ.ORGANIZATION_ID
, -1) ORGANIZATION_ID
, NVL(CREQ.JOB_ID
, -1) JOB_ID
, NVL(VGRD.GRADE_ID
, -1) GRADE_ID
, NVL(CREQ.POSITION_ID
, -1) POSITION_ID
, NVL(CREQ.ENTERPRISE_ID
, -1) ENTERPRISE_ID
, CREQ.COMPETENCE_ID COMPETENCE_ID
, CREQ.COMPETENCE_ELEMENT_ID COMPETENCE_ELEMENT_ID
, NVL(CREQ.PROFICIENCY_LEVEL_ID
, -1) LOW_LEVEL_ID
, NVL(CREQ.HIGH_PROFICIENCY_LEVEL_ID
, -1) HIGH_LEVEL_ID
, CREQ.MANDATORY ESSNTL_CMPTNC_FLAG_CODE
, GREATEST(CREQ.LAST_UPDATE_DATE
, NVL(VGRD.LAST_UPDATE_DATE
, HR_GENERAL.START_OF_TIME) ) LAST_CHANGE_DATE
, CASE WHEN ( CREQ.BUSINESS_GROUP_ID IS NULL OR CREQ.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, CREQ.BUSINESS_GROUP_ID) ) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_COMPETENCE_ELEMENTS CREQ
, PER_VALID_GRADES VGRD
WHERE CREQ.TYPE = 'REQUIREMENT'
AND CREQ.VALID_GRADE_ID = VGRD.VALID_GRADE_ID (+) WITH READ ONLY