1. Tables and Views for HCM
  2. HRT_PROFILE_MATCH_V

HRT_PROFILE_MATCH_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

COMPETENCY_COUNT

COMPETENCY_TARGET_COUNT

LANGUAGE_COUNT

LANGUAGE_TARGET_COUNT

CERTIFICATION_COUNT

CERTIFICATION_TARGET_COUNT

HONOR_COUNT

HONOR_TARGET_COUNT

MEMBERSHIP_COUNT

MEMBERSHIP_TARGET_COUNT

DEGREE_COUNT

DEGREE_TARGET_COUNT

WREQ_COUNT

WREQ_TARGET_COUNT

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 ,

(SELECT COUNT (*)

FROM HRT_PROFILE_MATCH_CRIT COMP_CRIT ,

HRT_CONTENT_TYPES_B CTYPEA

WHERE COMP_CRIT.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND COMP_CRIT.CONTENT_TYPE_ID = CTYPEA.CONTENT_TYPE_ID

AND COMP_CRIT.BUSINESS_GROUP_ID = CTYPEA.BUSINESS_GROUP_ID

AND CTYPEA.CONTEXT_NAME = 'COMPETENCY'

) COMPETENCY_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_ITEMS COMP_PROF ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE COMP_PROF.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND COMP_PROF.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND COMP_PROF.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'COMPETENCY'

AND COMP_PROF.DATE_FROM =

(SELECT MAX (COMP_PROF_DT.DATE_FROM)

FROM HRT_PROFILE_ITEMS COMP_PROF_DT

WHERE COMP_PROF_DT.PROFILE_ID = COMP_PROF.PROFILE_ID

AND COMP_PROF_DT.CONTENT_ITEM_ID = COMP_PROF.CONTENT_ITEM_ID

)

) COMPETENCY_TARGET_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_MATCH_CRIT LANG_CRIT ,

HRT_CONTENT_TYPES_B CTYPEA

WHERE LANG_CRIT.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND LANG_CRIT.CONTENT_TYPE_ID = CTYPEA.CONTENT_TYPE_ID

AND LANG_CRIT.BUSINESS_GROUP_ID = CTYPEA.BUSINESS_GROUP_ID

AND CTYPEA.CONTEXT_NAME = 'LANGUAGE'

) LANGUAGE_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_ITEMS LANG_PROF ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE LANG_PROF.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND LANG_PROF.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND LANG_PROF.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'LANGUAGE'

AND LANG_PROF.DATE_FROM =

(SELECT MAX (LANG_PROF_DT.DATE_FROM)

FROM HRT_PROFILE_ITEMS LANG_PROF_DT

WHERE LANG_PROF_DT.PROFILE_ID = LANG_PROF.PROFILE_ID

AND LANG_PROF_DT.CONTENT_ITEM_ID = LANG_PROF.CONTENT_ITEM_ID

)

) LANGUAGE_TARGET_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_MATCH_CRIT CERT_CRIT ,

HRT_CONTENT_TYPES_B CTYPEA

WHERE CERT_CRIT.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND CERT_CRIT.CONTENT_TYPE_ID = CTYPEA.CONTENT_TYPE_ID

AND CERT_CRIT.BUSINESS_GROUP_ID = CTYPEA.BUSINESS_GROUP_ID

AND CTYPEA.CONTEXT_NAME = 'CERTIFICATION'

) CERTIFICATION_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_ITEMS CERT_PROF ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE CERT_PROF.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND CERT_PROF.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND CERT_PROF.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'CERTIFICATION'

AND CERT_PROF.DATE_FROM =

(SELECT MAX (CERT_PROF_DT.DATE_FROM)

FROM HRT_PROFILE_ITEMS CERT_PROF_DT

WHERE CERT_PROF_DT.PROFILE_ID = CERT_PROF.PROFILE_ID

AND CERT_PROF_DT.CONTENT_ITEM_ID = CERT_PROF.CONTENT_ITEM_ID

AND NVL (TRIM (UPPER (CERT_PROF_DT.ITEM_TEXT240_1)) ,'1') = NVL (TRIM (UPPER (CERT_PROF.ITEM_TEXT240_1)) ,'1')

)

) CERTIFICATION_TARGET_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_MATCH_CRIT HONR_CRIT ,

HRT_CONTENT_TYPES_B CTYPEA

WHERE HONR_CRIT.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND HONR_CRIT.CONTENT_TYPE_ID = CTYPEA.CONTENT_TYPE_ID

AND HONR_CRIT.BUSINESS_GROUP_ID = CTYPEA.BUSINESS_GROUP_ID

AND CTYPEA.CONTEXT_NAME = 'HONOR'

) HONOR_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_ITEMS HONR_PROF ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE HONR_PROF.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND HONR_PROF.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND HONR_PROF.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'HONOR'

AND HONR_PROF.DATE_FROM =

(SELECT MAX (HONR_PROF_DT.DATE_FROM)

FROM HRT_PROFILE_ITEMS HONR_PROF_DT

WHERE HONR_PROF_DT.PROFILE_ID = HONR_PROF.PROFILE_ID

AND HONR_PROF_DT.CONTENT_ITEM_ID = HONR_PROF.CONTENT_ITEM_ID

)

) HONOR_TARGET_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_MATCH_CRIT MMBR_CRIT ,

HRT_CONTENT_TYPES_B CTYPEA

WHERE MMBR_CRIT.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND MMBR_CRIT.CONTENT_TYPE_ID = CTYPEA.CONTENT_TYPE_ID

AND MMBR_CRIT.BUSINESS_GROUP_ID = CTYPEA.BUSINESS_GROUP_ID

AND CTYPEA.CONTEXT_NAME = 'MEMBERSHIP'

) MEMBERSHIP_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_ITEMS MMBR_PROF ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE MMBR_PROF.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND MMBR_PROF.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND MMBR_PROF.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'MEMBERSHIP'

AND MMBR_PROF.DATE_FROM =

(SELECT MAX (MMBR_PROF_DT.DATE_FROM)

FROM HRT_PROFILE_ITEMS MMBR_PROF_DT

WHERE MMBR_PROF_DT.PROFILE_ID = MMBR_PROF.PROFILE_ID

AND MMBR_PROF_DT.CONTENT_ITEM_ID = MMBR_PROF.CONTENT_ITEM_ID

)

) MEMBERSHIP_TARGET_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_MATCH_CRIT DEGR_CRIT ,

HRT_CONTENT_TYPES_B CTYPEA

WHERE DEGR_CRIT.SEARCH_INSTANCE_ID = PROF_CRIT.SEARCH_INSTANCE_ID

AND DEGR_CRIT.CONTENT_TYPE_ID = CTYPEA.CONTENT_TYPE_ID

AND DEGR_CRIT.BUSINESS_GROUP_ID = CTYPEA.BUSINESS_GROUP_ID

AND CTYPEA.CONTEXT_NAME = 'DEGREE'

) DEGREE_COUNT ,

(SELECT COUNT (*)

FROM HRT_PROFILE_ITEMS DEGR_PROF ,

HRT_CONTENT_TYPES_B CTYPEB

WHERE DEGR_PROF.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND DEGR_PROF.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_ID

AND DEGR_PROF.BUSINESS_GROUP_ID = CTYPEB.BUSINESS_GROUP_ID

AND CTYPEB.CONTEXT_NAME = 'DEGREE'

AND DEGR_PROF.DATE_FROM =

(SELECT MAX (DEGR_PROF_DT.DATE_FROM)

FROM HRT_PROFILE_ITEMS DEGR_PROF_DT

WHERE DEGR_PROF_DT.PROFILE_ID = DEGR_PROF.PROFILE_ID

AND DEGR_PROF_DT.CONTENT_ITEM_ID = DEGR_PROF.CONTENT_ITEM_ID

)

) DEGREE_TARGET_COUNT ,

1 WREQ_COUNT ,

1 WREQ_TARGET_COUNT

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_ITEMS.CONTENT_TYPE_ID = CTYPEB.CONTENT_TYPE_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.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_ITEM4.LAST_UPDATE_DATE)

FROM HRT_PROFILE_ITEMS PROF_ITEM4

WHERE TRUNC (CURRENT_DATE) >= PROF_ITEM4.DATE_FROM

AND ( TRUNC (CURRENT_DATE) <= PROF_ITEM4.DATE_TO

OR PROF_ITEM4.DATE_TO IS NULL )

AND PROF_ITEM4.PROFILE_ID = PROF_ITEMS.PROFILE_ID

AND ( PROF_ITEM4.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_ITEM4.ITEM_TEXT240_1)) ,'1') ,'CERTIFICATION' ,NVL (TRIM (UPPER (PROF_ITEM4.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 (+)