1. Tables and Views for HCM
  2. IRC_CAND_PERSON_TYPE_V

IRC_CAND_PERSON_TYPE_V

Details

  • Schema: FUSION

  • Object owner: IRC

  • Object type: VIEW

Columns

Name

PERSON_ID

OBJECT_STATUS

SYSTEM_PERSON_TYPE

INTERNAL_FLAG

TYPE

PERSON_TYPE_USAGE_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

Query

SQL_Statement

SELECT PERSON_ID,

OBJECT_STATUS,

SYSTEM_PERSON_TYPE,

INTERNAL_FLAG,

TYPE,

PERSON_TYPE_USAGE_ID,

EFFECTIVE_START_DATE,

EFFECTIVE_END_DATE

FROM (

SELECT PTU.PERSON_ID,

CAND.OBJECT_STATUS,

PTU.SYSTEM_PERSON_TYPE,

PTU.PERSON_TYPE_USAGE_ID,

PTU.EFFECTIVE_START_DATE,

PTU.EFFECTIVE_END_DATE,

PT.INTERNAL_FLAG,

PT.TYPE,

ROW_NUMBER() OVER (PARTITION BY PTU.PERSON_ID ORDER BY PT.ENABLED_FLAG DESC, PT.PRIORITY) AS RN

FROM IRC_CANDIDATES CAND

INNER JOIN PER_PERSON_TYPE_USAGES_M PTU

ON CAND.PERSON_ID = PTU.PERSON_ID

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

AND PTU.EFFECTIVE_LATEST_CHANGE = 'Y'

INNER JOIN IRC_PERSON_TYPES_V PT

ON PTU.SYSTEM_PERSON_TYPE = PT.SYSTEM_PERSON_TYPE

)

WHERE RN = 1