MSC_HIERARCHY_LEVELS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
HIERARCHY_ID HIERARCHY_CODE HIERARCHY_NAME HIERARCHY_TYPE DIMENSION_ID DIMENSION_NAME IS_SEEDED_HIERARCHY NUMBER_OF_LEVELS LEVEL_ID LEVEL_NAME LEVEL_POS_NUM LEVEL_TYPE LEVEL_CATEGORY CALENDAR_LEVEL PARENT_LEVEL_ID PARENT_LEVEL_NAME PARENT_LEVEL_POS_NUM PARENT_LEVEL_TYPE LORDER RELATIONSHIP_VIEW CUSTOM LAST_REFRESH_NUMBER CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN

Name

HIERARCHY_ID

HIERARCHY_CODE

HIERARCHY_NAME

HIERARCHY_TYPE

DIMENSION_ID

DIMENSION_NAME

IS_SEEDED_HIERARCHY

NUMBER_OF_LEVELS

LEVEL_ID

LEVEL_NAME

LEVEL_POS_NUM

LEVEL_TYPE

LEVEL_CATEGORY

CALENDAR_LEVEL

PARENT_LEVEL_ID

PARENT_LEVEL_NAME

PARENT_LEVEL_POS_NUM

PARENT_LEVEL_TYPE

LORDER

RELATIONSHIP_VIEW

CUSTOM

LAST_REFRESH_NUMBER

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

Query

SQL_Statement
SELECT MHL.HIERARCHY_ID HIERARCHY_ID , MHV.HIERARCHY_CODE HIERARCHY_CODE , MHV.HIERARCHY_NAME HIERARCHY_NAME , MHV.HIERARCHY_TYPE HIERARCHY_TYPE , MHV.DIMENSION_ID DIMENSION_ID , MHV.DIMENSION_NAME DIMENSION_NAME , MHV.IS_SEEDED IS_SEEDED_HIERARCHY , MHV.NUMBER_OF_LEVELS NUMBER_OF_LEVELS , MHL.LEVEL_ID LEVEL_ID , MLV_CHILD.LEVEL_NAME LEVEL_NAME , MLV_CHILD.LEVEL_POS_NUM LEVEL_POS_NUM , MLV_CHILD.TYPE_NAME LEVEL_TYPE , MLV_CHILD.LEVEL_CATEGORY LEVEL_CATEGORY , DECODE(MLV_CHILD.CALENDAR_LEVEL,'Day', Decode(MHV.HIERARCHY_CODE, 'TIM_GRE','Gregorian Day', 'TIM_MFG','Manufacturing Day', 'TIM_FIS','Fiscal Day', 'TIM_CTM','Custom Calender Day'), MLV_CHILD.CALENDAR_LEVEL) CALENDAR_LEVEL , MHL.PARENT_LEVEL_ID PARENT_LEVEL_ID , DECODE(MHL.PARENT_LEVEL_ID, NULL, NULL, MLV_PARENT.LEVEL_NAME) PARENT_LEVEL_NAME , DECODE(MHL.PARENT_LEVEL_ID, NULL, NULL, MLV_PARENT.LEVEL_POS_NUM) PARENT_LEVEL_POS_NUM, DECODE(MHL.PARENT_LEVEL_ID, NULL, NULL, MLV_PARENT.TYPE_NAME) PARENT_LEVEL_TYPE , MHL.LORDER LORDER , MHL.RELATIONSHIP_VIEW RELATIONSHIP_VIEW , MHL.CUSTOM CUSTOM , MHL.LAST_REFRESH_NUMBER LAST_REFRESH_NUMBER , MHL.CREATED_BY CREATED_BY , MHL.CREATION_DATE CREATION_DATE , MHL.LAST_UPDATED_BY LAST_UPDATED_BY , MHL.LAST_UPDATE_DATE LAST_UPDATE_DATE , MHL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN FROM MSC_HIERARCHIES_V MHV, MSC_HIERARCHY_LEVELS MHL, MSC_LEVELS_V MLV_CHILD, MSC_LEVELS_V MLV_PARENT WHERE MHL.HIERARCHY_ID = MHV.HIERARCHY_ID AND MLV_CHILD.LEVEL_ID = MHL.LEVEL_ID AND MLV_PARENT.LEVEL_ID = DECODE (MHL.PARENT_LEVEL_ID, NULL, MHL.LEVEL_ID, MHL.PARENT_LEVEL_ID)

SQL_Statement

SELECT

MHL.HIERARCHY_ID HIERARCHY_ID ,

MHV.HIERARCHY_CODE HIERARCHY_CODE ,

MHV.HIERARCHY_NAME HIERARCHY_NAME ,

MHV.HIERARCHY_TYPE HIERARCHY_TYPE ,

MHV.DIMENSION_ID DIMENSION_ID ,

MHV.DIMENSION_NAME DIMENSION_NAME ,

MHV.IS_SEEDED IS_SEEDED_HIERARCHY ,

MHV.NUMBER_OF_LEVELS NUMBER_OF_LEVELS ,

MHL.LEVEL_ID LEVEL_ID ,

MLV_CHILD.LEVEL_NAME LEVEL_NAME ,

MLV_CHILD.LEVEL_POS_NUM LEVEL_POS_NUM ,

MLV_CHILD.TYPE_NAME LEVEL_TYPE ,

MLV_CHILD.LEVEL_CATEGORY LEVEL_CATEGORY ,

DECODE(MLV_CHILD.CALENDAR_LEVEL,'Day',

Decode(MHV.HIERARCHY_CODE, 'TIM_GRE','Gregorian Day',

'TIM_MFG','Manufacturing Day',

'TIM_FIS','Fiscal Day',

'TIM_CTM','Custom Calender Day'),

MLV_CHILD.CALENDAR_LEVEL) CALENDAR_LEVEL ,

MHL.PARENT_LEVEL_ID PARENT_LEVEL_ID ,

DECODE(MHL.PARENT_LEVEL_ID, NULL, NULL, MLV_PARENT.LEVEL_NAME) PARENT_LEVEL_NAME ,

DECODE(MHL.PARENT_LEVEL_ID, NULL, NULL, MLV_PARENT.LEVEL_POS_NUM) PARENT_LEVEL_POS_NUM,

DECODE(MHL.PARENT_LEVEL_ID, NULL, NULL, MLV_PARENT.TYPE_NAME) PARENT_LEVEL_TYPE ,

MHL.LORDER LORDER ,

MHL.RELATIONSHIP_VIEW RELATIONSHIP_VIEW ,

MHL.CUSTOM CUSTOM ,

MHL.LAST_REFRESH_NUMBER LAST_REFRESH_NUMBER ,

MHL.CREATED_BY CREATED_BY ,

MHL.CREATION_DATE CREATION_DATE ,

MHL.LAST_UPDATED_BY LAST_UPDATED_BY ,

MHL.LAST_UPDATE_DATE LAST_UPDATE_DATE ,

MHL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN

FROM MSC_HIERARCHIES_V MHV,

MSC_HIERARCHY_LEVELS MHL,

MSC_LEVELS_V MLV_CHILD,

MSC_LEVELS_V MLV_PARENT

WHERE MHL.HIERARCHY_ID = MHV.HIERARCHY_ID

AND MLV_CHILD.LEVEL_ID = MHL.LEVEL_ID

AND MLV_PARENT.LEVEL_ID = DECODE (MHL.PARENT_LEVEL_ID, NULL, MHL.LEVEL_ID, MHL.PARENT_LEVEL_ID)