1. Tables and Views for HCM
  2. IRC_PROFILE_CONT_SECTIONS_V

IRC_PROFILE_CONT_SECTIONS_V

Details

  • Schema: FUSION

  • Object owner: IRC

  • Object type: VIEW

Columns

Name

SECTION_ID

CONTENT_TYPE_ID

PROFILE_TYPE_ID

BUSINESS_GROUP_ID

CREATION_DATE

SKILL_RECOMMEND_ENABLED_FLAG

DEFAULT_SECTION

Query

SQL_Statement

SELECT PTS.SECTION_ID SECTION_ID,

PTS.CONTENT_TYPE_ID,

PROFILE_TYPE_ID,

PTS.BUSINESS_GROUP_ID,

PTS.CREATION_DATE,

NVL (PTS.SKILL_RECOMMEND_ENABLED_FLAG, 'N') SKILL_RECOMMEND_ENABLED_FLAG,

CASE

WHEN (FIRST_VALUE(PTS.SECTION_ID)

OVER(PARTITION BY PTS.CONTENT_TYPE_ID ORDER BY PTS.CREATION_DATE ASC)) =

PTS.SECTION_ID THEN 'Y'

ELSE 'N'

END DEFAULT_SECTION

FROM HRT_PROFILE_TYP_SECTIONS PTS, HRT_CONTENT_SOURCE_RLATS SR

WHERE PTS.SECTION_ID = SR.SECTION_ID

AND PTS.CONTENT_TYPE_ID = SR.CONTENT_TYPE_ID

AND PTS.BUSINESS_GROUP_ID = SR.BUSINESS_GROUP_ID

AND PTS.PROFILE_TYPE_ID = 1

AND SR.SOURCE_ID = 102

AND PTS.CONTENT_TYPE_ID IN (103, 106, 109, 117, 129, 134)

UNION ALL

SELECT MIN(PTS.SECTION_ID) SECTION_ID,

PTS.CONTENT_TYPE_ID,

PROFILE_TYPE_ID,

PTS.BUSINESS_GROUP_ID,

PTS.CREATION_DATE,

'N' SKILL_RECOMMEND_ENABLED_FLAG,

'Y' DEFAULT_SECTION

FROM HRT_PROFILE_TYP_SECTIONS PTS,

HRT_CONTENT_SOURCE_RLATS SR

WHERE PTS.PARENT_SECTION_ID = SR.SECTION_ID

AND PTS.CONTENT_TYPE_ID = SR.CONTENT_TYPE_ID

AND PTS.BUSINESS_GROUP_ID = SR.BUSINESS_GROUP_ID

AND PTS.PROFILE_TYPE_ID = 2

AND SR.SOURCE_ID = 102

AND PTS.CONTENT_TYPE_ID = 117

GROUP BY PTS.CONTENT_TYPE_ID,PROFILE_TYPE_ID,PTS.BUSINESS_GROUP_ID,PTS.CREATION_DATE