1. Tables and Views for HCM
  2. IRC_GEO_HIER_HIRING_V

IRC_GEO_HIER_HIRING_V

Details

  • Schema: FUSION

  • Object owner: IRC

  • Object type: VIEW

Columns

Name

GEOGRAPHY_NODE_ID

GEOGRAPHY_ID

HIERARCHY_ID

GEOGRAPHY_NAME

COUNTRY_NAME

Query

SQL_Statement

WITH

ActiveHierarchy AS (

SELECT H1.HIERARCHY_ID

FROM IRC_GEO_HIERARCHIES H1

WHERE H1.STATUS_CODE = 'ORA_ACTIVE'

AND H1.START_DATE <= SYSDATE

AND NOT EXISTS (SELECT 1

FROM IRC_GEO_HIERARCHIES H2

WHERE H2.HIERARCHY_ID <> H1.HIERARCHY_ID

AND H2.START_DATE > H1.START_DATE

AND H2.STATUS_CODE = 'ORA_ACTIVE'

AND H2.START_DATE <= SYSDATE)

),

GeographyNames AS (

SELECT IRC_GEO_HIER_NODES_OPTI.GEOGRAPHY_NODE_ID AS GEOGRAPHY_NODE_ID,

IRC_GEO_HIER_NODES_OPTI.RELATIVE_LEVEL AS RELATIVE_LEVEL,

IRC_GEO_HIER_NODES_IDEN.GEOGRAPHY_NAME AS GEOGRAPHY_NAME

FROM IRC_GEO_HIER_NODES_OPTI,

IRC_GEO_HIER_NODES_IDEN,

ActiveHierarchy

WHERE IRC_GEO_HIER_NODES_OPTI.HIERARCHY_ID = ActiveHierarchy.HIERARCHY_ID

AND IRC_GEO_HIER_NODES_IDEN.GEOGRAPHY_NODE_ID in IRC_GEO_HIER_NODES_OPTI.PARENT_GEO_NODE_ID

/* Geography Hierarchy Node identifier filtering */

/* we look for an identifier with user lang first,

else we take the oldest primary identifier */

AND IRC_GEO_HIER_NODES_IDEN.IRC_GEO_HIER_NODES_IDENT_ID =

(SELECT IRC_GEO_HIER_NODES_IDENT_ID

FROM (SELECT IRC_GEO_HIER_NODES_IDENT_ID

FROM IRC_GEO_HIER_NODES_IDEN

WHERE GEOGRAPHY_NODE_ID = IRC_GEO_HIER_NODES_OPTI.PARENT_GEO_NODE_ID

/* order by user lang first, primary second and creation date asc third */

ORDER BY DECODE(LANGUAGE_CODE, USERENV('LANG'), 1, 2) ASC,

PRIMARY_FLAG DESC,

CREATION_DATE ASC )

WHERE ROWNUM = 1)

)

SELECT GHN.GEOGRAPHY_NODE_ID AS GEOGRAPHY_NODE_ID,

(SELECT GEOGRAPHY_ID FROM IRC_GEO_HIER_NODES WHERE GEOGRAPHY_NODE_ID = GHN.GEOGRAPHY_NODE_ID) AS GEOGRAPHY_ID,

GHN.HIERARCHY_ID AS HIERARCHY_ID,

listAgg(GeographyNames.GEOGRAPHY_NAME, ', ') WITHIN GROUP (ORDER BY GeographyNames.RELATIVE_LEVEL ASC) AS GEOGRAPHY_NAME,

MIN(GeographyNames.GEOGRAPHY_NAME) keep(dense_rank last order by GeographyNames.RELATIVE_LEVEL ASC) COUNTRY_NAME

FROM IRC_GEO_HIER_NODES GHN,

GeographyNames,

ActiveHierarchy

WHERE GeographyNames.GEOGRAPHY_NODE_ID = GHN.GEOGRAPHY_NODE_ID

AND GHN.HIERARCHY_ID = ActiveHierarchy.HIERARCHY_ID

GROUP BY GHN.GEOGRAPHY_NODE_ID, GHN.HIERARCHY_ID