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_ASG_MATCH_X_V

Source

View Text - Preformatted


SELECT 1 row_indicator , crq.core_cmptnc_indicator , crq.org_rqrmnt_indicator , crq.job_rqrmnt_indicator , crq.grd_rqrmnt_indicator , crq.pos_rqrmnt_indicator , crq.essntl_cmptnc_indicator , crq.min_scale_level_value , crq.max_scale_level_value , crq.min_rank_level_value , crq.max_rank_level_value , crq.low_scale_level_value , crq.low_rank_level_value , crq.low_nrmlzd_scale_level_value , crq.low_nrmlzd_rank_level_value , crq.high_scale_level_value , crq.high_rank_level_value , crq.high_nrmlzd_scale_level_value , crq.high_nrmlzd_rank_level_value , cpl.scale_level_value per_scale_level_value , cpl.rank_level_value per_rank_level_value , cpl.nrmlzd_scale_level_value per_nrmlzd_scale_level_value , cpl.nrmlzd_rank_level_value per_nrmlzd_rank_level_value /* Matching Calculations */ , cpl.rank_level_value - crq.low_rank_level_value low_rank_diff_value , cpl.rank_level_value - crq.high_rank_level_value high_rank_diff_value , CASE WHEN cpl.persnl_level_id IS NULL THEN 1 ELSE 0 END no_cmptnc_match_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND cpl.rank_level_value IS NULL AND (crq.low_rank_level_value IS NOT NULL OR crq.high_rank_level_value IS NOT NULL) THEN 1 ELSE 0 END no_rating_match_indicator /* Compared to Rank Minimum */ , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 3) < crq.low_rank_level_value THEN 1 ELSE 0 END low_below3more_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 3) = crq.low_rank_level_value THEN 1 ELSE 0 END low_below3_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 2) = crq.low_rank_level_value THEN 1 ELSE 0 END low_below2_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 1) = crq.low_rank_level_value THEN 1 ELSE 0 END low_below1_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value = crq.low_rank_level_value OR crq.low_rank_level_value IS NULL) THEN 1 ELSE 0 END low_match_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 1) = crq.low_rank_level_value THEN 1 ELSE 0 END low_above1_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 2) = crq.low_rank_level_value THEN 1 ELSE 0 END low_above2_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 3) = crq.low_rank_level_value THEN 1 ELSE 0 END low_above3_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 3) > crq.low_rank_level_value THEN 1 ELSE 0 END low_above3more_indicator /* Compared to Rank Range Requirement*/ , CASE WHEN cpl.persnl_level_id IS NOT NULL AND ( crq.low_rank_level_value IS NULL OR cpl.rank_level_value >= crq.low_rank_level_value) AND ( crq.high_rank_level_value IS NULL OR cpl.rank_level_value <= crq.high_rank_level_value) THEN 1 ELSE 0 END range_match_indicator /* Compared to Rank High Requirement */ , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 3) < crq.high_rank_level_value THEN 1 ELSE 0 END high_below3more_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 3) = crq.high_rank_level_value THEN 1 ELSE 0 END high_below3_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 2) = crq.high_rank_level_value THEN 1 ELSE 0 END high_below2_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value + 1) = crq.high_rank_level_value THEN 1 ELSE 0 END high_below1_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value = crq.high_rank_level_value OR crq.high_rank_level_value IS NULL) THEN 1 ELSE 0 END high_exact_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 1) = crq.high_rank_level_value THEN 1 ELSE 0 END high_above1_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 2) = crq.high_rank_level_value THEN 1 ELSE 0 END high_above2_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 3) = crq.high_rank_level_value THEN 1 ELSE 0 END high_above3_indicator , CASE WHEN cpl.persnl_level_id IS NOT NULL AND (cpl.rank_level_value - 3) > crq.high_rank_level_value THEN 1 ELSE 0 END high_above3more_indicator /* Time */ , crq.effective_start_date rqrmnt_effective_start_date , crq.effective_end_date rqrmnt_effective_end_date , cpl.effective_start_date persnl_effective_start_date , cpl.effective_end_date persnl_effective_end_date /* Person Dimension */ , crq.person_id , crq.assignment_id /* Workstructure Dimensions */ , crq.business_group_id , crq.organization_id , crq.supervisor_person_id , crq.location_id , crq.job_id , crq.grade_id , crq.position_id , crq.primary_flag_code , crq.assignment_type_code , crq.assignment_status_type_id , crq.per_system_status_code , crq.pay_system_status_code /* Competence */ , 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 , cpl.persnl_level_id persnl_level_id , crq.essntl_cmptnc_flag_code essntl_cmptnc_flag_code /* Security */ , crq.hr_security_flag_code , crq.bg_security_flag_code /* Data Currency */ , GREATEST(crq.last_change_date ,NVL(cpl.last_change_date,hr_general.start_of_time) ) last_change_date FROM hri_md_cmptnc_rqrasg_v crq , hri_mb_cmptnc_persnl_v cpl WHERE crq.person_id = cpl.person_id (+) AND crq.competence_id = cpl.competence_id (+) AND TRUNC(SYSDATE) BETWEEN crq.effective_start_date AND crq.effective_end_date AND TRUNC(SYSDATE) BETWEEN cpl.effective_start_date (+) AND cpl.effective_end_date (+) WITH READ ONLY

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, CRQ.CORE_CMPTNC_INDICATOR
, CRQ.ORG_RQRMNT_INDICATOR
, CRQ.JOB_RQRMNT_INDICATOR
, CRQ.GRD_RQRMNT_INDICATOR
, CRQ.POS_RQRMNT_INDICATOR
, CRQ.ESSNTL_CMPTNC_INDICATOR
, CRQ.MIN_SCALE_LEVEL_VALUE
, CRQ.MAX_SCALE_LEVEL_VALUE
, CRQ.MIN_RANK_LEVEL_VALUE
, CRQ.MAX_RANK_LEVEL_VALUE
, CRQ.LOW_SCALE_LEVEL_VALUE
, CRQ.LOW_RANK_LEVEL_VALUE
, CRQ.LOW_NRMLZD_SCALE_LEVEL_VALUE
, CRQ.LOW_NRMLZD_RANK_LEVEL_VALUE
, CRQ.HIGH_SCALE_LEVEL_VALUE
, CRQ.HIGH_RANK_LEVEL_VALUE
, CRQ.HIGH_NRMLZD_SCALE_LEVEL_VALUE
, CRQ.HIGH_NRMLZD_RANK_LEVEL_VALUE
, CPL.SCALE_LEVEL_VALUE PER_SCALE_LEVEL_VALUE
, CPL.RANK_LEVEL_VALUE PER_RANK_LEVEL_VALUE
, CPL.NRMLZD_SCALE_LEVEL_VALUE PER_NRMLZD_SCALE_LEVEL_VALUE
, CPL.NRMLZD_RANK_LEVEL_VALUE PER_NRMLZD_RANK_LEVEL_VALUE /* MATCHING CALCULATIONS */
, CPL.RANK_LEVEL_VALUE - CRQ.LOW_RANK_LEVEL_VALUE LOW_RANK_DIFF_VALUE
, CPL.RANK_LEVEL_VALUE - CRQ.HIGH_RANK_LEVEL_VALUE HIGH_RANK_DIFF_VALUE
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NULL THEN 1 ELSE 0 END NO_CMPTNC_MATCH_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND CPL.RANK_LEVEL_VALUE IS NULL
AND (CRQ.LOW_RANK_LEVEL_VALUE IS NOT NULL OR CRQ.HIGH_RANK_LEVEL_VALUE IS NOT NULL) THEN 1 ELSE 0 END NO_RATING_MATCH_INDICATOR /* COMPARED TO RANK MINIMUM */
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 3) < CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_BELOW3MORE_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 3) = CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_BELOW3_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 2) = CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_BELOW2_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 1) = CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_BELOW1_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE = CRQ.LOW_RANK_LEVEL_VALUE OR CRQ.LOW_RANK_LEVEL_VALUE IS NULL) THEN 1 ELSE 0 END LOW_MATCH_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 1) = CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_ABOVE1_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 2) = CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_ABOVE2_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 3) = CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_ABOVE3_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 3) > CRQ.LOW_RANK_LEVEL_VALUE THEN 1 ELSE 0 END LOW_ABOVE3MORE_INDICATOR /* COMPARED TO RANK RANGE REQUIREMENT*/
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND ( CRQ.LOW_RANK_LEVEL_VALUE IS NULL OR CPL.RANK_LEVEL_VALUE >= CRQ.LOW_RANK_LEVEL_VALUE)
AND ( CRQ.HIGH_RANK_LEVEL_VALUE IS NULL OR CPL.RANK_LEVEL_VALUE <= CRQ.HIGH_RANK_LEVEL_VALUE) THEN 1 ELSE 0 END RANGE_MATCH_INDICATOR /* COMPARED TO RANK HIGH REQUIREMENT */
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 3) < CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_BELOW3MORE_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 3) = CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_BELOW3_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 2) = CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_BELOW2_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE + 1) = CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_BELOW1_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE = CRQ.HIGH_RANK_LEVEL_VALUE OR CRQ.HIGH_RANK_LEVEL_VALUE IS NULL) THEN 1 ELSE 0 END HIGH_EXACT_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 1) = CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_ABOVE1_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 2) = CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_ABOVE2_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 3) = CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_ABOVE3_INDICATOR
, CASE WHEN CPL.PERSNL_LEVEL_ID IS NOT NULL
AND (CPL.RANK_LEVEL_VALUE - 3) > CRQ.HIGH_RANK_LEVEL_VALUE THEN 1 ELSE 0 END HIGH_ABOVE3MORE_INDICATOR /* TIME */
, CRQ.EFFECTIVE_START_DATE RQRMNT_EFFECTIVE_START_DATE
, CRQ.EFFECTIVE_END_DATE RQRMNT_EFFECTIVE_END_DATE
, CPL.EFFECTIVE_START_DATE PERSNL_EFFECTIVE_START_DATE
, CPL.EFFECTIVE_END_DATE PERSNL_EFFECTIVE_END_DATE /* PERSON DIMENSION */
, CRQ.PERSON_ID
, CRQ.ASSIGNMENT_ID /* WORKSTRUCTURE DIMENSIONS */
, CRQ.BUSINESS_GROUP_ID
, CRQ.ORGANIZATION_ID
, CRQ.SUPERVISOR_PERSON_ID
, CRQ.LOCATION_ID
, CRQ.JOB_ID
, CRQ.GRADE_ID
, CRQ.POSITION_ID
, CRQ.PRIMARY_FLAG_CODE
, CRQ.ASSIGNMENT_TYPE_CODE
, CRQ.ASSIGNMENT_STATUS_TYPE_ID
, CRQ.PER_SYSTEM_STATUS_CODE
, CRQ.PAY_SYSTEM_STATUS_CODE /* COMPETENCE */
, 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
, CPL.PERSNL_LEVEL_ID PERSNL_LEVEL_ID
, CRQ.ESSNTL_CMPTNC_FLAG_CODE ESSNTL_CMPTNC_FLAG_CODE /* SECURITY */
, CRQ.HR_SECURITY_FLAG_CODE
, CRQ.BG_SECURITY_FLAG_CODE /* DATA CURRENCY */
, GREATEST(CRQ.LAST_CHANGE_DATE
, NVL(CPL.LAST_CHANGE_DATE
, HR_GENERAL.START_OF_TIME) ) LAST_CHANGE_DATE
FROM HRI_MD_CMPTNC_RQRASG_V CRQ
, HRI_MB_CMPTNC_PERSNL_V CPL
WHERE CRQ.PERSON_ID = CPL.PERSON_ID (+)
AND CRQ.COMPETENCE_ID = CPL.COMPETENCE_ID (+)
AND TRUNC(SYSDATE) BETWEEN CRQ.EFFECTIVE_START_DATE
AND CRQ.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN CPL.EFFECTIVE_START_DATE (+)
AND CPL.EFFECTIVE_END_DATE (+) WITH READ ONLY