1. Tables and Views for HCM
  2. HWM_TM_COMP_MSGS_V

HWM_TM_COMP_MSGS_V

Details

  • Schema: FUSION

  • Object owner: HWM

  • Object type: VIEW

Columns

Name

COMPLIANCE_ID

COMPLIANCE_GRP_TYPE_ID

COMPLIANCE_START_TIME

COMPLIANCE_STOP_TIME

COMPLIANCE_COMMIT_TIMESTAMP

RESOURCE_ID

COMPLIANCE_SUBRESOURCE_ID

COMPLIANCE_DELETE_FLAG

COMPLIANCE_LATEST_VERSION

TC_TM_REC_GRP_ID

TC_TM_REC_GRP_VERSION

TC_DELETE_FLAG

TC_LATEST_VERSION

DAY_TM_REC_GRP_ID

DAY_TM_REC_GRP_VERSION

DAY_DELETE_FLAG

DAY_LATEST_VERSION

TE_TM_REC_ID

TE_TM_REC_VERSION

TE_DELETE_FLAG

TE_LATEST_VERSION

COMPLIANCE_CREATED_BY

COMPLIANCE_CREATION_DATE

COMPLIANCE_LAST_UPDATED_BY

COMPLIANCE_LAST_UPDATE_DATE

COMPLIANCE_LAST_UPDATE_LOGIN

TM_REP_MSGS_ID

TM_BLDG_BLK_ID

TM_BLDG_BLK_VERSION

DATE_FROM

DATE_TO

MESSAGE_NAME

APPLICATION_SHORT_NAME

MESSAGE_LEVEL

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

Query

SQL_Statement

SELECT

TimeRepositoryMessagePEO.TM_BLDG_BLK_ID COMPLIANCE_ID,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.GRP_TYPE_ID,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.GRP_TYPE_ID,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.GRP_TYPE_ID) COMPLIANCE_GRP_TYPE_ID,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.START_TIME,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.START_TIME,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.START_TIME) COMPLIANCE_START_TIME,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.STOP_TIME,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.STOP_TIME,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.STOP_TIME) COMPLIANCE_STOP_TIME,

TCTimeRecordGroupPEO.COMMIT_TIMESTAMP COMPLIANCE_COMMIT_TIMESTAMP,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.RESOURCE_ID,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.RESOURCE_ID,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.RESOURCE_ID) RESOURCE_ID,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.SUBRESOURCE_ID,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.SUBRESOURCE_ID,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.SUBRESOURCE_ID) COMPLIANCE_SUBRESOURCE_ID,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.DELETE_FLAG,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.DELETE_FLAG,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.DELETE_FLAG) COMPLIANCE_DELETE_FLAG,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.LATEST_VERSION,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.LATEST_VERSION,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.LATEST_VERSION) COMPLIANCE_LATEST_VERSION,

TCTimeRecordGroupPEO.TM_REC_GRP_ID TC_TM_REC_GRP_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_VERSION TC_TM_REC_GRP_VERSION,

TCTimeRecordGroupPEO.DELETE_FLAG TC_DELETE_FLAG,

TCTimeRecordGroupPEO.LATEST_VERSION TC_LATEST_VERSION,

DayTimeRecordGroupPEO.TM_REC_GRP_ID DAY_TM_REC_GRP_ID,

DayTimeRecordGroupPEO.TM_REC_GRP_VERSION DAY_TM_REC_GRP_VERSION,

DayTimeRecordGroupPEO.DELETE_FLAG DAY_DELETE_FLAG,

DayTimeRecordGroupPEO.LATEST_VERSION DAY_LATEST_VERSION,

TimeRecordPEO.TM_REC_ID TE_TM_REC_ID,

TimeRecordPEO.TM_REC_VERSION TE_TM_REC_VERSION,

TimeRecordPEO.DELETE_FLAG TE_DELETE_FLAG,

TimeRecordPEO.LATEST_VERSION TE_LATEST_VERSION,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.CREATED_BY,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.CREATED_BY,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.CREATED_BY) COMPLIANCE_CREATED_BY,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.CREATION_DATE,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.CREATION_DATE,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.CREATION_DATE) COMPLIANCE_CREATION_DATE,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.LAST_UPDATED_BY,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.LAST_UPDATED_BY,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.LAST_UPDATED_BY) COMPLIANCE_LAST_UPDATED_BY,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.LAST_UPDATE_DATE,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.LAST_UPDATE_DATE,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.LAST_UPDATE_DATE) COMPLIANCE_LAST_UPDATE_DATE,

DECODE(TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TCTimeRecordGroupPEO.TM_REC_GRP_ID, TCTimeRecordGroupPEO.LAST_UPDATE_LOGIN,

DayTimeRecordGroupPEO.TM_REC_GRP_ID, DayTimeRecordGroupPEO.LAST_UPDATE_LOGIN,

TimeRecordPEO.TM_REC_ID, TimeRecordPEO.LAST_UPDATE_LOGIN) COMPLIANCE_LAST_UPDATE_LOGIN,

TimeRepositoryMessagePEO.TM_REP_MSGS_ID,

TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TimeRepositoryMessagePEO.TM_BLDG_BLK_VERSION,

TimeRepositoryMessagePEO.DATE_FROM,

TimeRepositoryMessagePEO.DATE_TO,

TimeRepositoryMessagePEO.MESSAGE_NAME,

TimeRepositoryMessagePEO.APPLICATION_SHORT_NAME,

TimeRepositoryMessagePEO.MESSAGE_LEVEL,

TimeRepositoryMessagePEO.CREATED_BY,

TimeRepositoryMessagePEO.CREATION_DATE,

TimeRepositoryMessagePEO.LAST_UPDATED_BY,

TimeRepositoryMessagePEO.LAST_UPDATE_DATE,

TimeRepositoryMessagePEO.LAST_UPDATE_LOGIN

FROM HWM_TM_REC_GRP TCTimeRecordGroupPEO,

HWM_TM_REC_GRP DayTimeRecordGroupPEO,

HWM_TM_REC_GRP_USAGES TimeRecordGroupUsagePEO,

HWM_TM_REC TimeRecordPEO,

HWM_TM_REP_MSGS TimeRepositoryMessagePEO

WHERE (TCTimeRecordGroupPEO.GRP_TYPE_ID = 100

AND TCTimeRecordGroupPEO.TM_REC_GRP_ID= DayTimeRecordGroupPEO.PARENT_TM_REC_GRP_ID

AND TCTimeRecordGroupPEO.TM_REC_GRP_VERSION = DayTimeRecordGroupPEO.PARENT_TM_REC_GRP_VERSION

AND DayTimeRecordGroupPEO.GRP_TYPE_ID = 101

AND DayTimeRecordGroupPEO.TM_REC_GRP_ID= TimeRecordGroupUsagePEO.TM_REC_GRP_ID

AND DayTimeRecordGroupPEO.TM_REC_GRP_VERSION= TimeRecordGroupUsagePEO.TM_REC_GRP_VERSION

AND TimeRecordGroupUsagePEO.TM_REC_ID = TimeRecordPEO.TM_REC_ID

AND TimeRecordGroupUsagePEO.TM_REC_VERSION = TimeRecordPEO.TM_REC_VERSION

AND TimeRecordPEO.GRP_TYPE_ID = 102

AND TimeRepositoryMessagePEO.MESSAGE_LEVEL = 'COMPLIANCE'

AND ( (TimeRepositoryMessagePEO.TM_BLDG_BLK_ID = TCTimeRecordGroupPEO.TM_REC_GRP_ID

AND TimeRepositoryMessagePEO.TM_BLDG_BLK_VERSION = TCTimeRecordGroupPEO.TM_REC_GRP_VERSION)

OR (TimeRepositoryMessagePEO.TM_BLDG_BLK_ID = DayTimeRecordGroupPEO.TM_REC_GRP_ID

AND TimeRepositoryMessagePEO.TM_BLDG_BLK_VERSION = DayTimeRecordGroupPEO.TM_REC_GRP_VERSION)

OR (TimeRepositoryMessagePEO.TM_BLDG_BLK_ID = TimeRecordPEO.TM_REC_ID

AND TimeRepositoryMessagePEO.TM_BLDG_BLK_VERSION = TimeRecordPEO.TM_REC_VERSION))

)

UNION

SELECT

TimeRecordGroupPEO.TM_REC_GRP_ID COMPLIANCE_ID,

TimeRecordGroupPEO.GRP_TYPE_ID COMPLIANCE_GRP_TYPE_ID,

TimeRecordGroupPEO.START_TIME COMPLIANCE_START_TIME,

TimeRecordGroupPEO.STOP_TIME COMPLIANCE_STOP_TIME,

TimeRecordGroupPEO.COMMIT_TIMESTAMP COMPLIANCE_COMMIT_TIMESTAMP,

TimeRecordGroupPEO.RESOURCE_ID RESOURCE_ID,

TimeRecordGroupPEO.SUBRESOURCE_ID COMPLIANCE_SUBRESOURCE_ID,

TimeRecordGroupPEO.DELETE_FLAG COMPLIANCE_DELETE_FLAG,

TimeRecordGroupPEO.LATEST_VERSION COMPLIANCE_LATEST_VERSION,

NULL TC_TM_REC_GRP_ID,

NULL TC_TM_REC_GRP_VERSION,

NULL TC_DELETE_FLAG,

'Y' TC_LATEST_VERSION,

NULL DAY_TM_REC_GRP_ID,

NULL DAY_TM_REC_GRP_VERSION,

NULL DAY_DELETE_FLAG,

'Y' DAY_LATEST_VERSION,

NULL TE_TM_REC_ID,

NULL TE_TM_REC_VERSION,

NULL TE_DELETE_FLAG,

'Y' TE_LATEST_VERSION,

TimeRecordGroupPEO.CREATED_BY COMPLIANCE_CREATED_BY,

TimeRecordGroupPEO.CREATION_DATE COMPLIANCE_CREATION_DATE,

TimeRecordGroupPEO.LAST_UPDATED_BY COMPLIANCE_LAST_UPDATED_BY,

TimeRecordGroupPEO.LAST_UPDATE_DATE COMPLIANCE_LAST_UPDATE_DATE,

TimeRecordGroupPEO.LAST_UPDATE_LOGIN COMPLIANCE_LAST_UPDATE_LOGIN,

TimeRepositoryMessagePEO.TM_REP_MSGS_ID,

TimeRepositoryMessagePEO.TM_BLDG_BLK_ID,

TimeRepositoryMessagePEO.TM_BLDG_BLK_VERSION,

TimeRepositoryMessagePEO.DATE_FROM,

TimeRepositoryMessagePEO.DATE_TO,

TimeRepositoryMessagePEO.MESSAGE_NAME,

TimeRepositoryMessagePEO.APPLICATION_SHORT_NAME,

TimeRepositoryMessagePEO.MESSAGE_LEVEL,

TimeRepositoryMessagePEO.CREATED_BY,

TimeRepositoryMessagePEO.CREATION_DATE,

TimeRepositoryMessagePEO.LAST_UPDATED_BY,

TimeRepositoryMessagePEO.LAST_UPDATE_DATE,

TimeRepositoryMessagePEO.LAST_UPDATE_LOGIN

FROM HWM_TM_REP_MSGS TimeRepositoryMessagePEO,

HWM_TM_REC_GRP TimeRecordGroupPEO

WHERE (TimeRepositoryMessagePEO.TM_BLDG_BLK_ID = TimeRecordGroupPEO.TM_REC_GRP_ID

AND TimeRepositoryMessagePEO.TM_BLDG_BLK_VERSION = TimeRecordGroupPEO.TM_REC_GRP_VERSION

AND TimeRecordGroupPEO.GRP_TYPE_ID = 111

AND TimeRepositoryMessagePEO.MESSAGE_LEVEL = 'COMPLIANCE')