1. Tables and Views for HCM
  2. HXT_TCLAY_REGION_V

HXT_TCLAY_REGION_V

Details

  • Schema: FUSION

  • Object owner: HWM

  • Object type: VIEW

Columns

Name

TCLAY_ID

TCLAY_CD

TCLAYST_ID

TCLAY_TYPE

TCLAY_TASK_SHORT_NAME

TL_TASK_RESULTS_ID

TL_TASK_FEATURES_ID

TASK_SHORT_NAME

TASK_NAME

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

LAYOUT_REGION_CODE

MATRIX_DECIMAL_PLACE

MATRIX_BLANK_ROWS_DEFAULT

MATRIX_ENABLE_NEGATIVE

MATRIX_TE_FORMAT

MATRIX_DATE_FORMAT

MATRIX_ENABLE_MIDNIGHT

MATRIX_DISPLAY_UOM

MATRIX_HIGHLIGHT_OT

MATRIX_ENABLE_BUTTON_RULE

MATRIX_VIEW_DAILY_TEVT

MATRIX_HIDE_SECONDS

MATRIX_DISPLAY_HOUR_SLIDER

MATRIX_DISPLAY_TIME_SLIDER

ADDTL_LABEL

COMMENT_LABEL

COMMENT_DISPLAY_OPTION

COMMENT_DATE_FORMAT

DAILY_LABEL

DAILY_DATE_FORMAT

TIMETOTALS_DECIMAL_PLACE

HOURS_DECIMAL_PLACE

HOURS_DATE_FORMAT

UNITS_DATE_FORMAT

INSTANCE_FLAG

Query

SQL_Statement

SELECT layout.TCLAY_ID,

layout.TCLAY_CD,

layout.TCLAYST_ID,

layout.TCLAY_TYPE,

layout.TCLAY_TASK_SHORT_NAME,

layout.TL_TASK_RESULTS_ID,

layout.TL_TASK_FEATURES_ID,

layout.TASK_SHORT_NAME,

layout.TASK_NAME,

layout.CREATED_BY,

layout.CREATION_DATE,

layout.LAST_UPDATED_BY,

layout.LAST_UPDATE_DATE,

layout.LAST_UPDATE_LOGIN ,

NVL(NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE), 'RAW_INSTANCE') LAYOUT_REGION_CODE ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND (layout.TCLAY_TYPE LIKE 'Time %'

OR layout.TCLAY_TYPE LIKE 'Fuse Calendar %')

THEN matrix.MATRIX_DECIMAL_PLACE

ELSE NULL

END) MATRIX_DECIMAL_PLACE ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE LIKE 'Time %'

THEN matrix.MATRIX_BLANK_ROWS_DEFAULT

ELSE NULL

END) MATRIX_BLANK_ROWS_DEFAULT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND (layout.TCLAY_TYPE LIKE 'Time %'

OR layout.TCLAY_TYPE LIKE 'Fuse Calendar %')

THEN NVL(matrix.MATRIX_ENABLE_NEGATIVE, 'N')

ELSE NULL

END) MATRIX_ENABLE_NEGATIVE ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND (layout.TCLAY_TYPE LIKE 'Time %'

OR layout.TCLAY_TYPE LIKE 'Fuse Calendar %')

THEN matrix.MATRIX_TE_FORMAT

ELSE NULL

END) MATRIX_TE_FORMAT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE LIKE 'Time %'

THEN matrix.MATRIX_DATE_FORMAT

ELSE NULL

END) MATRIX_DATE_FORMAT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND (layout.TCLAY_TYPE LIKE 'Time %'

OR layout.TCLAY_TYPE LIKE 'Fuse Calendar %')

THEN NVL(matrix.MATRIX_ENABLE_MIDNIGHT, 'N')

ELSE NULL

END) MATRIX_ENABLE_MIDNIGHT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE LIKE 'Time %'

THEN NVL(matrix.MATRIX_DISPLAY_UOM, 'N')

ELSE NULL

END) MATRIX_DISPLAY_UOM ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE LIKE 'Time %'

THEN matrix.MATRIX_HIGHLIGHT_OT

ELSE NULL

END) MATRIX_HIGHLIGHT_OT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE = 'Web Clock Indiv'

THEN NVL(matrix.MATRIX_ENABLE_BUTTON_RULE, 'N')

ELSE NULL

END) MATRIX_ENABLE_BUTTON_RULE ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE = 'Web Clock Indiv'

THEN NVL(matrix.MATRIX_VIEW_DAILY_TEVT, 'N')

ELSE NULL

END) MATRIX_VIEW_DAILY_TEVT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE = 'Web Clock Indiv'

THEN NVL(matrix.MATRIX_HIDE_SECONDS, 'N')

ELSE NULL

END) MATRIX_HIDE_SECONDS ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE LIKE 'Fuse Calendar %'

THEN NVL(matrix.MATRIX_DISPLAY_HOUR_SLIDER, 'N')

ELSE NULL

END) MATRIX_DISPLAY_HOUR_SLIDER ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

AND layout.TCLAY_TYPE LIKE 'Fuse Calendar %'

THEN NVL(matrix.MATRIX_DISPLAY_TIME_SLIDER, 'N')

ELSE NULL

END) MATRIX_DISPLAY_TIME_SLIDER ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

THEN addtl.ADDTL_LABEL

ELSE NULL

END) ADDTL_LABEL ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

THEN comments.COMMENT_LABEL

ELSE NULL

END) COMMENT_LABEL ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

THEN comments.COMMENT_DISPLAY_OPTION

ELSE NULL

END) COMMENT_DISPLAY_OPTION ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

THEN comments.COMMENT_DATE_FORMAT

ELSE NULL

END) COMMENT_DATE_FORMAT ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

THEN daily.DAILY_LABEL

ELSE NULL

END) DAILY_LABEL ,

(

CASE

WHEN NVL(hour.LAYOUT_SUBTABLE_CODE, matrix.LAYOUT_TABLE_CODE) <> 'TCR_INSTANCE_CCTT'

THEN daily.DAILY_DATE_FORMAT

ELSE NULL

END) DAILY_DATE_FORMAT ,

(

CASE

WHEN layout.TCLAY_TYPE LIKE 'Fuse Calendar %'

THEN NVL(timeT.TIMETOTALS_DECIMAL_PLACE, 0)

ELSE NULL

END) TIMETOTALS_DECIMAL_PLACE ,

hour.HOURS_DECIMAL_PLACE,

hour.HOURS_DATE_FORMAT,

unit.UNITS_DATE_FORMAT,

'Y' INSTANCE_FLAG

FROM HXT_TCLAY_V layout,

(SELECT NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 15, 3), 'RAW')

|| '_INSTANCE'

|| SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 18, 5) LAYOUT_TABLE_CODE,

TCLAY_ID,

LABEL MATRIX_LABEL,

TCLAYFLD_ATTRIBUTE_NUMBER1 MATRIX_DECIMAL_PLACE,

TCLAYFLD_ATTRIBUTE_NUMBER2 MATRIX_BLANK_ROWS_DEFAULT,

TCLAYFLD_ATTRIBUTE_CHAR1 MATRIX_ENABLE_NEGATIVE,

TCLAYFLD_ATTRIBUTE_CHAR2 MATRIX_TE_FORMAT,

TCLAYFLD_ATTRIBUTE_CHAR3 MATRIX_DATE_FORMAT,

TCLAYFLD_ATTRIBUTE_CHAR9 MATRIX_ENABLE_MIDNIGHT,

TCLAYFLD_ATTRIBUTE_CHAR10 MATRIX_DISPLAY_UOM,

TCLAYFLD_ATTRIBUTE_CHAR11 MATRIX_HIGHLIGHT_OT,

TCLAYFLD_ATTRIBUTE_CHAR15 MATRIX_ENABLE_BUTTON_RULE,

TCLAYFLD_ATTRIBUTE_CHAR17 MATRIX_VIEW_DAILY_TEVT,

TCLAYFLD_ATTRIBUTE_CHAR18 MATRIX_HIDE_SECONDS,

TCLAYFLD_ATTRIBUTE_CHAR26 MATRIX_DISPLAY_HOUR_SLIDER,

TCLAYFLD_ATTRIBUTE_CHAR27 MATRIX_DISPLAY_TIME_SLIDER

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME = 'MATRIX_PARAMS'

) matrix,

(SELECT NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 13, 3), 'RAW')

|| '_INSTANCE'

|| SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 16, 5) LAYOUT_TABLE_CODE,

TCLAY_ID,

LABEL ADDTL_LABEL

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME = 'ADDTL_ATTRS'

) addtl,

(SELECT NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 10, 3), 'RAW')

|| '_INSTANCE'

|| SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 13, 5) LAYOUT_TABLE_CODE,

TCLAY_ID,

LABEL COMMENT_LABEL,

TCLAYFLD_ATTRIBUTE_CHAR1 COMMENT_DISPLAY_OPTION,

TCLAYFLD_ATTRIBUTE_CHAR4 COMMENT_DATE_FORMAT

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME = 'Comments'

) comments,

(SELECT NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 15, 3), 'RAW')

|| '_INSTANCE'

|| SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 18, 5) LAYOUT_TABLE_CODE,

TCLAY_ID,

LABEL DAILY_LABEL,

TCLAYFLD_ATTRIBUTE_CHAR1 DAILY_DATE_FORMAT

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME = 'DAILY_DETAILS'

) daily,

(SELECT 'RAW_INSTANCE' LAYOUT_TABLE_CODE,

TCLAY_ID,

TCLAYFLD_ATTRIBUTE_NUMBER1 TIMETOTALS_DECIMAL_PLACE

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME = 'TIME_TOTALS'

) timeT,

(SELECT NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 18, 3), 'RAW')

|| '_INSTANCE' LAYOUT_TABLE_CODE,

NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 18, 3), 'RAW')

|| '_INSTANCE'

|| SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 21, 5) LAYOUT_SUBTABLE_CODE,

TCLAY_ID,

TCLAYFLD_ATTRIBUTE_NUMBER1 HOURS_DECIMAL_PLACE,

NVL(TCLAYFLD_ATTRIBUTE_CHAR3, 'EEEEE, MMMMM dd') HOURS_DATE_FORMAT

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME IN ('HOURS_CATEGORIES', 'HOURS_CATEGORIES_TCR')

) hour,

(SELECT NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 17, 3), 'RAW')

|| '_INSTANCE' LAYOUT_TABLE_CODE,

NVL(SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 17, 3), 'RAW')

|| '_INSTANCE'

|| SUBSTR(TCLAYFLD_ATTRIBUTE_CATEGORY, 20, 5) LAYOUT_SUBTABLE_CODE,

TCLAY_ID,

NVL(TCLAYFLD_ATTRIBUTE_CHAR3, 'EEEEE, MMMMM dd') UNITS_DATE_FORMAT

FROM HXT_TCLAYFLD_DEFNS_VL

WHERE NAME IN ('DAYS_CATEGORIES', 'DAYS_CATEGORIES_TCR')

) unit

WHERE layout.TCLAY_ID = matrix.TCLAY_ID (+)

AND matrix.TCLAY_ID = addtl.TCLAY_ID (+)

AND matrix.LAYOUT_TABLE_CODE = addtl.LAYOUT_TABLE_CODE (+)

AND matrix.TCLAY_ID = comments.TCLAY_ID (+)

AND matrix.LAYOUT_TABLE_CODE = comments.LAYOUT_TABLE_CODE (+)

AND matrix.TCLAY_ID = daily.TCLAY_ID (+)

AND matrix.LAYOUT_TABLE_CODE = daily.LAYOUT_TABLE_CODE (+)

AND matrix.TCLAY_ID = timeT.TCLAY_ID (+)

AND matrix.LAYOUT_TABLE_CODE = timeT.LAYOUT_TABLE_CODE (+)

AND matrix.TCLAY_ID = hour.TCLAY_ID (+)

AND matrix.LAYOUT_TABLE_CODE = hour.LAYOUT_TABLE_CODE (+)

AND hour.TCLAY_ID = unit.TCLAY_ID (+)

AND hour.LAYOUT_TABLE_CODE = unit.LAYOUT_TABLE_CODE (+)

AND hour.LAYOUT_SUBTABLE_CODE = unit.LAYOUT_SUBTABLE_CODE (+)