1. Tables and Views for HCM
  2. HRT_PROFILE_BESTFIT_V

HRT_PROFILE_BESTFIT_V

Details

  • Schema: FUSION

  • Object owner: HRT

  • Object type: VIEW

Columns

Name

PROFILE_ID

PROFILE_ITEM_ID

CONTENT_TYPE_ID

CONTENT_ITEM_ID

BUSINESS_GROUP_ID

PROFILE_RATING

SEARCH_RATING

SEARCH_IMPORTANCE

SEARCH_TARGET_IMPORTANCE

PROFILE_NAME

SEARCH_INSTANCE_ID

ACTUAL_RATING

ACTUAL_TARGET_RATING

PROFILE_TYPE_CODE

PERSON_ID

SOURCE_PROFILE_ID

Query

SQL_Statement

SELECT PROF_ITEMS.PROFILE_ID ,

PROF_ITEMS.PROFILE_ITEM_ID ,

PROF_ITEMS.CONTENT_TYPE_ID ,

PROF_ITEMS.CONTENT_ITEM_ID ,

PROF_ITEMS.BUSINESS_GROUP_ID ,

RATIN_LVL1.NUMERIC_RATING PROFILE_RATING ,

PROF_CRIT.SEARCH_NUMERIC_RATING1 SEARCH_RATING ,

ROUND (TO_NUMBER (PROF_CRIT.IMPORTANCE) /

(SELECT SUM (TO_NUMBER (ITEMS.IMPORTANCE))

FROM HRT_PROFILE_MATCH_CRIT ITEMS

WHERE ITEMS.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

) * 100 ,2) / 100 SEARCH_IMPORTANCE ,

round (to_number (prof_items.importance) /

(

SELECT sum (to_number (items.importance))

FROM hrt_profile_items items

,hrt_content_types_b ctypeb

WHERE items.profile_id = prof_items.profile_id

AND items.content_type_id = ctypeb.content_type_id

AND ctypeb.context_name IN ('COMPETENCY','LANGUAGE','CERTIFICATION'

,'HONOR','MEMBERSHIP','DEGREE'

,'WORK_REQUIREMENTS')

AND items.last_update_date =

(

SELECT max (prof_dt.last_update_date)

FROM hrt_profile_items prof_dt

WHERE trunc (current_date) >= prof_dt.date_from

AND (

trunc (current_date) <= prof_dt.date_to

OR prof_dt.date_to IS NULL

)

AND prof_dt.profile_id = items.profile_id

AND (

prof_dt.content_type_id = items.content_type_id

)

AND (

prof_dt.content_item_id = items.content_item_id

OR ctypeb.context_name = 'WORK_REQUIREMENTS'

)

)

) * 100

,2) / 100 search_target_importance

,

CASE PROF_TYP.PROFILE_TYPE_CODE

WHEN 'PERSON'

THEN

(SELECT NAM.LIST_NAME

FROM PER_PERSON_NAMES_F_V NAM

WHERE NAM.PERSON_ID = PROF_VL.PERSON_ID

AND TRUNC (SYSDATE) BETWEEN NAM.EFFECTIVE_START_DATE AND NAM.EFFECTIVE_END_DATE

)

ELSE PROF_VL.DESCRIPTION

END PROFILE_NAME ,

PROF_CRIT.SEARCH_INSTANCE_ID ,

CASE

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'Y'

AND RATIN_LVL1.NUMERIC_RATING > PROF_CRIT.SEARCH_NUMERIC_RATING1

THEN 100

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'Y'

AND RATIN_LVL1.NUMERIC_RATING < PROF_CRIT.SEARCH_NUMERIC_RATING1

THEN (RATIN_LVL1.NUMERIC_RATING / PROF_CRIT.SEARCH_NUMERIC_RATING1) * 100

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WP'

AND PROF_VL.PROFILE_USAGE_CODE = 'P'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_3 = PROF_ITEMS.ITEM_TEXT30_3

) ,0)

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WP'

AND PROF_VL.PROFILE_USAGE_CODE = 'M'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_3 = PROF_ITEMS.ITEM_TEXT30_2

) ,0)

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WM'

AND PROF_VL.PROFILE_USAGE_CODE = 'P'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_2 = PROF_ITEMS.ITEM_TEXT30_3

) ,0)

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WM'

AND PROF_VL.PROFILE_USAGE_CODE = 'M'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_2 = PROF_ITEMS.ITEM_TEXT30_2

) ,0)

ELSE 100

END ACTUAL_RATING ,

CASE

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'Y'

AND RATIN_LVL1.NUMERIC_RATING < PROF_CRIT.SEARCH_NUMERIC_RATING1

THEN 100

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'Y'

AND RATIN_LVL1.NUMERIC_RATING > PROF_CRIT.SEARCH_NUMERIC_RATING1

THEN (PROF_CRIT.SEARCH_NUMERIC_RATING1 / RATIN_LVL1.NUMERIC_RATING) * 100

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WP'

AND PROF_VL.PROFILE_USAGE_CODE = 'P'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_3 = PROF_ITEMS.ITEM_TEXT30_3

) ,0)

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WP'

AND PROF_VL.PROFILE_USAGE_CODE = 'M'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_3 = PROF_ITEMS.ITEM_TEXT30_2

) ,0)

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WM'

AND PROF_VL.PROFILE_USAGE_CODE = 'P'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT.ITEM_TEXT30_2 = PROF_ITEMS.ITEM_TEXT30_3

) ,0)

WHEN PROF_CRIT.SEARCH_RATING_USAGE = 'WM'

AND PROF_VL.PROFILE_USAGE_CODE = 'M'

THEN NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_1 = PROF_ITEMS.ITEM_TEXT30_1

) ,0) + NVL (

(SELECT 50

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT2 ,

HRT_CONTENT_TYPES_B CTYPEB2

WHERE PROF_CRIT2.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND PROF_CRIT2.CONTENT_TYPE_ID = CTYPEB2.CONTENT_TYPE_ID

AND PROF_CRIT2.BUSINESS_GROUP_ID = CTYPEB2.BUSINESS_GROUP_ID

AND CTYPEB2.CONTEXT_NAME = 'WORK_REQUIREMENTS'

AND PROF_CRIT2.ITEM_TEXT30_2 = PROF_ITEMS.ITEM_TEXT30_2

) ,0)

ELSE 100

END ACTUAL_TARGET_RATING ,

PROF_TYP.PROFILE_TYPE_CODE ,

PROF_VL.PERSON_ID ,

PROF_CRIT.PROFILE_ID SOURCE_PROFILE_ID

FROM HRT_PROFILE_MATCH_CRIT PROF_CRIT ,

HRT_PROFILES_B REF_PROF ,

HRT_PROFILE_ITEMS PROF_ITEMS ,

HRT_RATING_LEVELS_B RATIN_LVL1 ,

HRT_PROFILES_VL PROF_VL ,

HRT_PROFILE_TYPES_VL PROF_TYP ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE TRUNC (CURRENT_DATE) >= PROF_ITEMS.DATE_FROM

AND ( TRUNC (CURRENT_DATE) <= PROF_ITEMS.DATE_TO

OR PROF_ITEMS.DATE_TO IS NULL )

AND PROF_CRIT.CONTENT_TYPE_ID = PROF_ITEMS.CONTENT_TYPE_ID

AND REF_PROF.PROFILE_ID = PROF_CRIT.PROFILE_ID

AND ( ( PROF_CRIT.CONTENT_ITEM_ID = PROF_ITEMS.CONTENT_ITEM_ID

AND ( REF_PROF.PROFILE_USAGE_CODE = 'M'

OR PROF_VL.PROFILE_USAGE_CODE = 'M'

OR ( DECODE (CTYPEB.CONTEXT_NAME ,'DEGREE' ,NVL (TRIM (UPPER (PROF_CRIT.ITEM_TEXT240_1)) ,'1') ,'CERTIFICATION' ,NVL (TRIM (UPPER (PROF_CRIT.ITEM_TEXT240_1)) ,'1') ,'1') = DECODE (CTYPEB.CONTEXT_NAME ,'DEGREE' ,NVL (TRIM (UPPER (PROF_ITEMS.ITEM_TEXT240_1)) ,'1') ,'CERTIFICATION' ,NVL (TRIM (UPPER (PROF_ITEMS.ITEM_TEXT240_1)) ,'1') ,'1') ) ) )

OR ( PROF_CRIT.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND PROF_CRIT.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'WORK_REQUIREMENTS' ) )

AND PROF_ITEMS.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND PROF_ITEMS.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND PROF_ITEMS.RATING_MODEL_ID1 = RATIN_LVL1.RATING_MODEL_ID (+)

AND PROF_ITEMS.RATING_LEVEL_ID1 = RATIN_LVL1.RATING_LEVEL_ID (+)

AND PROF_VL.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND PROF_VL.PROFILE_STATUS_CODE = 'A'

AND ( PROF_VL.PROFILE_USAGE_CODE = 'M'

OR ( PROF_VL.PROFILE_USAGE_CODE = 'P'

AND PROF_VL.PERSON_ID IS NOT NULL ) )

AND PROF_TYP.PROFILE_TYPE_ID = PROF_VL.PROFILE_TYPE_ID

AND PROF_ITEMS.LAST_UPDATE_DATE = DECODE (CTYPEB.CONTEXT_NAME ,'WORK_REQUIREMENTS' ,

(SELECT MAX (PROF_ITEM2.LAST_UPDATE_DATE)

FROM HRT_PROFILE_ITEMS PROF_ITEM2

WHERE TRUNC (CURRENT_DATE) >= PROF_ITEM2.DATE_FROM

AND ( TRUNC (CURRENT_DATE) <= PROF_ITEM2.DATE_TO

OR PROF_ITEM2.DATE_TO IS NULL )

AND PROF_ITEM2.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND PROF_ITEM2.CONTENT_TYPE_ID = PROF_ITEMS.CONTENT_TYPE_ID

) ,

(SELECT MAX (PROF_ITEM2.LAST_UPDATE_DATE)

FROM HRT_PROFILE_ITEMS PROF_ITEM2

WHERE TRUNC (CURRENT_DATE) >= PROF_ITEM2.DATE_FROM

AND ( TRUNC (CURRENT_DATE) <= PROF_ITEM2.DATE_TO

OR PROF_ITEM2.DATE_TO IS NULL )

AND PROF_ITEM2.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND ( PROF_ITEM2.CONTENT_ITEM_ID = PROF_ITEMS.CONTENT_ITEM_ID

AND ( REF_PROF.PROFILE_USAGE_CODE = 'M'

OR PROF_VL.PROFILE_USAGE_CODE = 'M'

OR ( DECODE (CTYPEB.CONTEXT_NAME ,'DEGREE' ,NVL (TRIM (UPPER (PROF_ITEM2.ITEM_TEXT240_1)) ,'1') ,'CERTIFICATION' ,NVL (TRIM (UPPER (PROF_ITEM2.ITEM_TEXT240_1)) ,'1') ,'1') = DECODE (CTYPEB.CONTEXT_NAME ,'DEGREE' ,NVL (TRIM (UPPER (PROF_ITEMS.ITEM_TEXT240_1)) ,'1') ,'CERTIFICATION' ,NVL (TRIM (UPPER (PROF_ITEMS.ITEM_TEXT240_1)) ,'1') ,'1') ) ) )

))

AND PROF_ITEMS.BUSINESS_GROUP_ID = PROF_TYP.BUSINESS_GROUP_ID

AND PROF_ITEMS.BUSINESS_GROUP_ID = RATIN_LVL1.BUSINESS_GROUP_ID (+)