HWM_TM_REC_GRP_SUM

Details

Schema: FUSION
Object owner: HWM
Object type: VIEW

Columns

Name
TM_REC_GRP_ID TM_REC_GRP_VERSION GRP_TYPE_ID ENTERPRISE_ID STATUS STATUS_VALUE START_TIME STOP_TIME RESOURCE_ID RECORDED_HOURS HAS_REASONS SUBMISSION_DATE ABSENCE_HOURS OBJECT_VERSION_NUMBER CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN ERROR_SEV_CD COMMENT_TEXT

Name

TM_REC_GRP_ID

TM_REC_GRP_VERSION

GRP_TYPE_ID

ENTERPRISE_ID

STATUS

STATUS_VALUE

START_TIME

STOP_TIME

RESOURCE_ID

RECORDED_HOURS

HAS_REASONS

SUBMISSION_DATE

ABSENCE_HOURS

OBJECT_VERSION_NUMBER

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

ERROR_SEV_CD

COMMENT_TEXT

Query

SQL_Statement
SELECT T.TM_REC_GRP_ID, T.TM_REC_GRP_VERSION, T.GRP_TYPE_ID, T.ENTERPRISE_ID, T.STATUS, T.STATUS_VALUE, T.START_TIME, T.STOP_TIME, T.RESOURCE_ID, T.RECORDED_HOURS, T.HAS_REASONS, T.SUBMISSION_DATE, T.ABSENCE_HOURS, T.OBJECT_VERSION_NUMBER, T.CREATED_BY, T.CREATION_DATE, T.LAST_UPDATED_BY, T.LAST_UPDATE_DATE, T.LAST_UPDATE_LOGIN, T.ERROR_SEV_CD, T.COMMENT_TEXT FROM (SELECT TC.TM_REC_GRP_ID, TC.TM_REC_GRP_VERSION, TC.ENTERPRISE_ID, TC.GRP_TYPE_ID, LOOKUP.MEANING STATUS, STAT.STATUS_VALUE, TC.START_TIME, TC.STOP_TIME, TC.RESOURCE_ID, TC.RECORDED_HOURS, NULL HAS_REASONS, CASE WHEN STAT.STATUS_VALUE = 'SUBMITTED' OR STAT.STATUS_VALUE = 'APPROVED' OR STAT.STATUS_VALUE = 'REJECTED' THEN (SELECT MAX(STAT1.DATE_FROM) FROM HWM_TM_STATUSES STAT1, HWM_TM_STATUS_DEFINITIONS_VL DEF1 WHERE SYSDATE BETWEEN STAT1.DATE_FROM AND STAT1.DATE_TO AND STAT1.TM_BLDG_BLK_ID(+) = TC.TM_REC_GRP_ID AND STAT1.TM_BLDG_BLK_VERSION(+) = TC.TM_REC_GRP_VERSION AND STAT1.TM_STATUS_DEF_ID =DEF1.TM_STATUS_DEF_ID AND STAT1.STATUS_VALUE = 'SUBMITTED' AND DEF1.DEFINITIONCODE = 'A_USR_STATUS' ) ELSE NULL END AS SUBMISSION_DATE, TC.CREATED_BY, TC.CREATION_DATE, TC.LAST_UPDATE_DATE, TC.LAST_UPDATE_LOGIN, TC.LAST_UPDATED_BY, TC.OBJECT_VERSION_NUMBER, TC1.COMMENT_TEXT, (SELECT STATUS_VALUE FROM HWM_TM_STATUSES STAT2, HWM_TM_STATUS_DEFINITIONS_VL DEF2 WHERE DEF2.DEFINITIONCODE ='A_TC_MSG_LEVEL' AND DEF2.TM_STATUS_DEF_ID =STAT2.TM_STATUS_DEF_ID AND STAT2.TM_BLDG_BLK_ID =TC.TM_REC_GRP_ID AND STAT2.TM_BLDG_BLK_VERSION = TC.TM_REC_GRP_VERSION AND SYSDATE BETWEEN STAT2.DATE_FROM AND STAT2.DATE_TO ) AS ERROR_SEV_CD, TC.ABSENCE_HOURS FROM HWM_TM_REC_GRP_SUM TC, HWM_TM_REC_GRP TC1, HWM_TM_STATUSES STAT, HWM_TM_STATUS_DEFINITIONS_VL DEF, HCM_LOOKUPS LOOKUP WHERE TC.TM_REC_GRP_ID = TC1.TM_REC_GRP_ID AND TC.TM_REC_GRP_VERSION = TC1.TM_REC_GRP_VERSION AND SYSDATE BETWEEN STAT.DATE_FROM AND STAT.DATE_TO AND STAT.TM_BLDG_BLK_ID(+) = TC.TM_REC_GRP_ID AND STAT.TM_BLDG_BLK_VERSION(+) = TC.TM_REC_GRP_VERSION AND STAT.TM_STATUS_DEF_ID =DEF.TM_STATUS_DEF_ID AND DEF.DEFINITIONCODE = 'D_TM_UI_STATUS' AND STAT.date_from = (SELECT MAX(sta1.date_from) FROM hwm_tm_statuses sta1 WHERE sta1.tm_status_def_id = STAT.tm_status_def_id AND sta1.tm_bldg_blk_id = STAT.tm_bldg_blk_id AND sta1.tm_bldg_blk_version = STAT.tm_bldg_blk_version AND SYSDATE BETWEEN sta1.date_from AND sta1.date_to) AND LOOKUP.LOOKUP_CODE = STAT.STATUS_VALUE AND Lookup.LOOKUP_TYPE = DEF.ALLOWABLE_VALUES GROUP BY TC.TM_REC_GRP_ID, TC.TM_REC_GRP_VERSION, TC.ENTERPRISE_ID, TC.GRP_TYPE_ID, STAT.STATUS_VALUE, LOOKUP.Meaning, TC.START_TIME, TC.STOP_TIME, TC.RESOURCE_ID, TC.CREATED_BY, TC.CREATION_DATE, TC.LAST_UPDATE_DATE, TC.LAST_UPDATE_LOGIN, TC.LAST_UPDATED_BY, TC.OBJECT_VERSION_NUMBER, TC1.COMMENT_TEXT, TC.RECORDED_HOURS, TC.ABSENCE_HOURS ) T GROUP BY T.TM_REC_GRP_ID, T.TM_REC_GRP_VERSION, T.ENTERPRISE_ID, T.GRP_TYPE_ID, T.STATUS, T.STATUS_VALUE, T.START_TIME, T.STOP_TIME, T.RESOURCE_ID, T.RECORDED_HOURS, T.HAS_REASONS, T.SUBMISSION_DATE, T.CREATED_BY, T.CREATION_DATE, T.LAST_UPDATE_DATE, T.LAST_UPDATE_LOGIN, T.LAST_UPDATED_BY, T.OBJECT_VERSION_NUMBER, T.ERROR_SEV_CD, T.COMMENT_TEXT, T.ABSENCE_HOURS

SQL_Statement

SELECT T.TM_REC_GRP_ID,

T.TM_REC_GRP_VERSION,

T.GRP_TYPE_ID,

T.ENTERPRISE_ID,

T.STATUS,

T.STATUS_VALUE,

T.START_TIME,

T.STOP_TIME,

T.RESOURCE_ID,

T.RECORDED_HOURS,

T.HAS_REASONS,

T.SUBMISSION_DATE,

T.ABSENCE_HOURS,

T.OBJECT_VERSION_NUMBER,

T.CREATED_BY,

T.CREATION_DATE,

T.LAST_UPDATED_BY,

T.LAST_UPDATE_DATE,

T.LAST_UPDATE_LOGIN,

T.ERROR_SEV_CD,

T.COMMENT_TEXT

FROM

(SELECT TC.TM_REC_GRP_ID,

TC.TM_REC_GRP_VERSION,

TC.ENTERPRISE_ID,

TC.GRP_TYPE_ID,

LOOKUP.MEANING STATUS,

STAT.STATUS_VALUE,

TC.START_TIME,

TC.STOP_TIME,

TC.RESOURCE_ID,

TC.RECORDED_HOURS,

NULL HAS_REASONS,

CASE

WHEN STAT.STATUS_VALUE = 'SUBMITTED'

OR STAT.STATUS_VALUE = 'APPROVED'

OR STAT.STATUS_VALUE = 'REJECTED'

THEN

(SELECT MAX(STAT1.DATE_FROM)

FROM HWM_TM_STATUSES STAT1,

HWM_TM_STATUS_DEFINITIONS_VL DEF1

WHERE SYSDATE BETWEEN STAT1.DATE_FROM AND STAT1.DATE_TO

AND STAT1.TM_BLDG_BLK_ID(+) = TC.TM_REC_GRP_ID

AND STAT1.TM_BLDG_BLK_VERSION(+) = TC.TM_REC_GRP_VERSION

AND STAT1.TM_STATUS_DEF_ID =DEF1.TM_STATUS_DEF_ID

AND STAT1.STATUS_VALUE = 'SUBMITTED'

AND DEF1.DEFINITIONCODE = 'A_USR_STATUS'

)

ELSE NULL

END AS SUBMISSION_DATE,

TC.CREATED_BY,

TC.CREATION_DATE,

TC.LAST_UPDATE_DATE,

TC.LAST_UPDATE_LOGIN,

TC.LAST_UPDATED_BY,

TC.OBJECT_VERSION_NUMBER,

TC1.COMMENT_TEXT,

(SELECT STATUS_VALUE FROM HWM_TM_STATUSES STAT2, HWM_TM_STATUS_DEFINITIONS_VL DEF2

WHERE DEF2.DEFINITIONCODE ='A_TC_MSG_LEVEL'

AND DEF2.TM_STATUS_DEF_ID =STAT2.TM_STATUS_DEF_ID

AND STAT2.TM_BLDG_BLK_ID =TC.TM_REC_GRP_ID

AND STAT2.TM_BLDG_BLK_VERSION = TC.TM_REC_GRP_VERSION

AND SYSDATE BETWEEN STAT2.DATE_FROM AND STAT2.DATE_TO ) AS ERROR_SEV_CD,

TC.ABSENCE_HOURS

FROM

HWM_TM_REC_GRP_SUM TC,

HWM_TM_REC_GRP TC1,

HWM_TM_STATUSES STAT,

HWM_TM_STATUS_DEFINITIONS_VL DEF,

HCM_LOOKUPS LOOKUP

WHERE TC.TM_REC_GRP_ID = TC1.TM_REC_GRP_ID

AND TC.TM_REC_GRP_VERSION = TC1.TM_REC_GRP_VERSION

AND SYSDATE BETWEEN STAT.DATE_FROM AND STAT.DATE_TO

AND STAT.TM_BLDG_BLK_ID(+) = TC.TM_REC_GRP_ID

AND STAT.TM_BLDG_BLK_VERSION(+) = TC.TM_REC_GRP_VERSION

AND STAT.TM_STATUS_DEF_ID =DEF.TM_STATUS_DEF_ID

AND DEF.DEFINITIONCODE = 'D_TM_UI_STATUS'

AND STAT.date_from = (SELECT MAX(sta1.date_from)

FROM hwm_tm_statuses sta1

WHERE sta1.tm_status_def_id = STAT.tm_status_def_id

AND sta1.tm_bldg_blk_id = STAT.tm_bldg_blk_id

AND sta1.tm_bldg_blk_version = STAT.tm_bldg_blk_version

AND SYSDATE BETWEEN sta1.date_from AND sta1.date_to)

AND LOOKUP.LOOKUP_CODE = STAT.STATUS_VALUE

AND Lookup.LOOKUP_TYPE = DEF.ALLOWABLE_VALUES

GROUP BY TC.TM_REC_GRP_ID,