eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRI_MD_CMPTNC_RQRASG_V

Source

View Text - Preformatted


SELECT 1 , crq.core_cmptnc_indicator core_cmptnc_indicator , CASE WHEN asg.organization_id = crq.organization_id THEN 1 ELSE 0 END org_rqrmnt_indicator , CASE WHEN asg.job_id = crq.job_id THEN 1 ELSE 0 END job_rqrmnt_indicator , CASE WHEN asg.grade_id = crq.grade_id THEN 1 ELSE 0 END grd_rqrmnt_indicator , CASE WHEN asg.position_id = crq.position_id THEN 1 ELSE 0 END pos_rqrmnt_indicator , crq.essntl_cmptnc_indicator essntl_cmptnc_indicator , llvl.min_scale_level_value min_scale_level_value , llvl.max_scale_level_value max_scale_level_value , llvl.min_rank_level_value min_rank_level_value , llvl.max_rank_level_value max_rank_level_value , llvl.scale_level_value low_scale_level_value , llvl.rank_level_value low_rank_level_value , llvl.nrmlzd_scale_level_value low_nrmlzd_scale_level_value , llvl.nrmlzd_rank_level_value low_nrmlzd_rank_level_value , hlvl.scale_level_value high_scale_level_value , hlvl.rank_level_value high_rank_level_value , hlvl.nrmlzd_scale_level_value high_nrmlzd_scale_level_value , hlvl.nrmlzd_rank_level_value high_nrmlzd_rank_level_value , GREATEST(asg.effective_start_date ,crq.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE , LEAST(asg.effective_end_date ,crq.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE , crq.effective_start_date rqrmnt_effective_start_date , crq.effective_end_date rqrmnt_effective_end_date , asg.person_id person_id , asg.assignment_id assignment_id , asg.business_group_id business_group_id , asg.organization_id organization_id , NVL(asg.supervisor_id,-1) supervisor_person_id , NVL(asg.location_id,-1) location_id , NVL(asg.job_id,-1) job_id , NVL(asg.grade_id,-1) grade_id , NVL(asg.position_id,-1) position_id , asg.primary_flag primary_flag_code , asg.assignment_type assignment_type_code , ast.assignment_status_type_id assignment_status_type_id , ast.per_system_status per_system_status_code , ast.pay_system_status pay_system_status_code , crq.competence_id competence_id , crq.competence_element_id competence_element_id , crq.business_group_id rqrmnt_business_group_id , crq.organization_id rqrmnt_organization_id , crq.job_id rqrmnt_job_id , crq.grade_id rqrmnt_grade_id , crq.position_id rqrmnt_position_id , crq.low_level_id low_level_id , crq.high_level_id high_level_id , crq.essntl_cmptnc_flag_code essntl_cmptnc_flag_code , CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD('PER_ALL_ASSIGNMENTS_F' , asg.assignment_id , asg.person_id , asg.assignment_type ) = 'TRUE' ) THEN 'Y' ELSE 'N' END , CASE WHEN asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id ,asg.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code , GREATEST(crq.last_change_date ,asg.last_update_date) last_change_date FROM hri_mb_cmptnc_rqrmnt_ct crq , per_all_assignments_f asg , per_assignment_status_types ast , hri_cs_cmptnc_lvl_ct llvl , hri_cs_cmptnc_lvl_ct hlvl WHERE asg.assignment_type IN ('A','E','C') AND ast.per_system_status <> 'TERM_ASSIGN' AND crq.job_id = asg.job_id AND ( crq.grade_id = -1 OR crq.grade_id = asg.grade_id) AND asg.assignment_status_type_id = ast.assignment_status_type_id AND crq.competence_id = llvl.competence_id AND NVL(crq.low_level_id, -1) = llvl.level_id AND crq.competence_id = hlvl.competence_id AND NVL(crq.high_level_id, -1) = hlvl.level_id WITH READ ONLY

View Text - HTML Formatted

SELECT 1
, CRQ.CORE_CMPTNC_INDICATOR CORE_CMPTNC_INDICATOR
, CASE WHEN ASG.ORGANIZATION_ID = CRQ.ORGANIZATION_ID THEN 1 ELSE 0 END ORG_RQRMNT_INDICATOR
, CASE WHEN ASG.JOB_ID = CRQ.JOB_ID THEN 1 ELSE 0 END JOB_RQRMNT_INDICATOR
, CASE WHEN ASG.GRADE_ID = CRQ.GRADE_ID THEN 1 ELSE 0 END GRD_RQRMNT_INDICATOR
, CASE WHEN ASG.POSITION_ID = CRQ.POSITION_ID THEN 1 ELSE 0 END POS_RQRMNT_INDICATOR
, CRQ.ESSNTL_CMPTNC_INDICATOR ESSNTL_CMPTNC_INDICATOR
, LLVL.MIN_SCALE_LEVEL_VALUE MIN_SCALE_LEVEL_VALUE
, LLVL.MAX_SCALE_LEVEL_VALUE MAX_SCALE_LEVEL_VALUE
, LLVL.MIN_RANK_LEVEL_VALUE MIN_RANK_LEVEL_VALUE
, LLVL.MAX_RANK_LEVEL_VALUE MAX_RANK_LEVEL_VALUE
, LLVL.SCALE_LEVEL_VALUE LOW_SCALE_LEVEL_VALUE
, LLVL.RANK_LEVEL_VALUE LOW_RANK_LEVEL_VALUE
, LLVL.NRMLZD_SCALE_LEVEL_VALUE LOW_NRMLZD_SCALE_LEVEL_VALUE
, LLVL.NRMLZD_RANK_LEVEL_VALUE LOW_NRMLZD_RANK_LEVEL_VALUE
, HLVL.SCALE_LEVEL_VALUE HIGH_SCALE_LEVEL_VALUE
, HLVL.RANK_LEVEL_VALUE HIGH_RANK_LEVEL_VALUE
, HLVL.NRMLZD_SCALE_LEVEL_VALUE HIGH_NRMLZD_SCALE_LEVEL_VALUE
, HLVL.NRMLZD_RANK_LEVEL_VALUE HIGH_NRMLZD_RANK_LEVEL_VALUE
, GREATEST(ASG.EFFECTIVE_START_DATE
, CRQ.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, LEAST(ASG.EFFECTIVE_END_DATE
, CRQ.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, CRQ.EFFECTIVE_START_DATE RQRMNT_EFFECTIVE_START_DATE
, CRQ.EFFECTIVE_END_DATE RQRMNT_EFFECTIVE_END_DATE
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL(ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_ID
, NVL(ASG.LOCATION_ID
, -1) LOCATION_ID
, NVL(ASG.JOB_ID
, -1) JOB_ID
, NVL(ASG.GRADE_ID
, -1) GRADE_ID
, NVL(ASG.POSITION_ID
, -1) POSITION_ID
, ASG.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_CODE
, CRQ.COMPETENCE_ID COMPETENCE_ID
, CRQ.COMPETENCE_ELEMENT_ID COMPETENCE_ELEMENT_ID
, CRQ.BUSINESS_GROUP_ID RQRMNT_BUSINESS_GROUP_ID
, CRQ.ORGANIZATION_ID RQRMNT_ORGANIZATION_ID
, CRQ.JOB_ID RQRMNT_JOB_ID
, CRQ.GRADE_ID RQRMNT_GRADE_ID
, CRQ.POSITION_ID RQRMNT_POSITION_ID
, CRQ.LOW_LEVEL_ID LOW_LEVEL_ID
, CRQ.HIGH_LEVEL_ID HIGH_LEVEL_ID
, CRQ.ESSNTL_CMPTNC_FLAG_CODE ESSNTL_CMPTNC_FLAG_CODE
, CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD('PER_ALL_ASSIGNMENTS_F'
, ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.ASSIGNMENT_TYPE ) = 'TRUE' ) THEN 'Y' ELSE 'N' END
, CASE WHEN ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
, GREATEST(CRQ.LAST_CHANGE_DATE
, ASG.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM HRI_MB_CMPTNC_RQRMNT_CT CRQ
, PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
, HRI_CS_CMPTNC_LVL_CT LLVL
, HRI_CS_CMPTNC_LVL_CT HLVL
WHERE ASG.ASSIGNMENT_TYPE IN ('A'
, 'E'
, 'C')
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND CRQ.JOB_ID = ASG.JOB_ID
AND ( CRQ.GRADE_ID = -1 OR CRQ.GRADE_ID = ASG.GRADE_ID)
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND CRQ.COMPETENCE_ID = LLVL.COMPETENCE_ID
AND NVL(CRQ.LOW_LEVEL_ID
, -1) = LLVL.LEVEL_ID
AND CRQ.COMPETENCE_ID = HLVL.COMPETENCE_ID
AND NVL(CRQ.HIGH_LEVEL_ID
, -1) = HLVL.LEVEL_ID WITH READ ONLY