HWM_TM_REP_WORK_HOURS_SUM

Details

Schema: FUSION
Object owner: HWM
Object type: VIEW

Columns

Name
TM_REC_ID PERSON_ID HOURS START_TIME ASSIGNMENT_ID PAYROLL_TIME_TYPE PYR_APRV_STATUS GRP_TYPE_ID

Name

TM_REC_ID

PERSON_ID

HOURS

START_TIME

ASSIGNMENT_ID

PAYROLL_TIME_TYPE

PYR_APRV_STATUS

GRP_TYPE_ID

Query

SQL_Statement
SELECT REP_WORK_HOURS.TM_REC_ID, REP_WORK_HOURS.PERSON_ID, REP_WORK_HOURS.HOURS, REP_WORK_HOURS.START_TIME, REP_WORK_HOURS.ASSIGNMENT_ID, ATRBS.ATTRIBUTE_CATEGORY PAYROLL_TIME_TYPE, PYR_STATUS.STATUS_VALUE PYR_APRV_STATUS, REP_WORK_HOURS.GRP_TYPE_ID FROM (SELECT REC.TM_REC_ID TM_REC_ID, REC.ORIG_TM_REC_ID ORIG_TM_REC_ID, REC.TM_REC_VERSION TM_REC_VERSION, REC.ORIG_TM_REC_VERSION ORIG_TM_REC_VERSION, REC.RESOURCE_ID PERSON_ID, NVL(REC.MEASURE,EXTRACT(HOUR FROM REC.STOP_TIME-REC.START_TIME)) HOURS, REC.START_TIME, REC.SUBRESOURCE_ID ASSIGNMENT_ID, TIME_CARD.GRP_TYPE_ID FROM FUSION.HWM_TM_REC_GRP TIME_CARD, FUSION.HWM_TM_REC_GRP TC_DAY, FUSION.HWM_TM_REC_GRP_USAGES USG, FUSION.HWM_TM_REC REC WHERE TIME_CARD.TM_REC_GRP_ID = TC_DAY.PARENT_TM_REC_GRP_ID AND TIME_CARD.TM_REC_GRP_VERSION = TC_DAY.PARENT_TM_REC_GRP_VERSION AND TC_DAY.TM_REC_GRP_ID = USG.TM_REC_GRP_ID AND TC_DAY.TM_REC_GRP_VERSION = USG.TM_REC_GRP_VERSION AND USG.TM_REC_ID = REC.TM_REC_ID AND USG.TM_REC_VERSION = REC.TM_REC_VERSION AND NVL(REC.DELETE_FLAG, 'N') = 'N' AND USG.LATEST_VERSION = 'Y' AND REC.UNIT_OF_MEASURE IN ('HR', 'HOURS') AND REC.RESOURCE_TYPE = 'PERSON' ) REP_WORK_HOURS LEFT JOIN (SELECT ATR_USG.USAGES_SOURCE_ID, ATR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY FROM FUSION.HWM_TM_REP_ATRB_USAGES ATR_USG, FUSION.HWM_TM_REP_ATRBS ATR, FUSION.hwm_tm_atrb_flds_vl TAF WHERE ATR_USG.LATEST_VERSION = 'Y' AND ATR_USG.TM_REP_ATRB_ID = ATR.TM_REP_ATRB_ID AND ATR.MASTER_ATTRIBUTE_ID = TAF.tm_atrb_fld_id AND TAF.name = 'PayrollTimeType' ) ATRBS ON REP_WORK_HOURS.TM_REC_ID = ATRBS.USAGES_SOURCE_ID LEFT JOIN (SELECT s.STATUS_VALUE, s.tm_bldg_blk_id, s.tm_bldg_blk_version FROM fusion.HWM_TM_ACTIVE_STATUS_V s WHERE s.STATUS_DEF_CD = 'A_APP_STATUS_PYR' ) PYR_STATUS ON NVL(REP_WORK_HOURS.ORIG_TM_REC_ID, REP_WORK_HOURS.TM_REC_ID) = PYR_STATUS.tm_bldg_blk_id AND NVL(REP_WORK_HOURS.ORIG_TM_REC_VERSION, REP_WORK_HOURS.TM_REC_VERSION) = PYR_STATUS.tm_bldg_blk_version

SQL_Statement

SELECT REP_WORK_HOURS.TM_REC_ID,

REP_WORK_HOURS.PERSON_ID,

REP_WORK_HOURS.HOURS,

REP_WORK_HOURS.START_TIME,

REP_WORK_HOURS.ASSIGNMENT_ID,

ATRBS.ATTRIBUTE_CATEGORY PAYROLL_TIME_TYPE,

PYR_STATUS.STATUS_VALUE PYR_APRV_STATUS,

REP_WORK_HOURS.GRP_TYPE_ID

FROM

(SELECT REC.TM_REC_ID TM_REC_ID,

REC.ORIG_TM_REC_ID ORIG_TM_REC_ID,

REC.TM_REC_VERSION TM_REC_VERSION,

REC.ORIG_TM_REC_VERSION ORIG_TM_REC_VERSION,

REC.RESOURCE_ID PERSON_ID,

NVL(REC.MEASURE,EXTRACT(HOUR FROM REC.STOP_TIME-REC.START_TIME))

HOURS,

REC.START_TIME,

REC.SUBRESOURCE_ID ASSIGNMENT_ID,

TIME_CARD.GRP_TYPE_ID

FROM FUSION.HWM_TM_REC_GRP TIME_CARD,

FUSION.HWM_TM_REC_GRP TC_DAY,

FUSION.HWM_TM_REC_GRP_USAGES USG,

FUSION.HWM_TM_REC REC

WHERE TIME_CARD.TM_REC_GRP_ID = TC_DAY.PARENT_TM_REC_GRP_ID

AND TIME_CARD.TM_REC_GRP_VERSION = TC_DAY.PARENT_TM_REC_GRP_VERSION

AND TC_DAY.TM_REC_GRP_ID = USG.TM_REC_GRP_ID

AND TC_DAY.TM_REC_GRP_VERSION = USG.TM_REC_GRP_VERSION

AND USG.TM_REC_ID = REC.TM_REC_ID

AND USG.TM_REC_VERSION = REC.TM_REC_VERSION

AND NVL(REC.DELETE_FLAG, 'N') = 'N'

AND USG.LATEST_VERSION = 'Y'

AND REC.UNIT_OF_MEASURE IN ('HR', 'HOURS')

AND REC.RESOURCE_TYPE = 'PERSON'

) REP_WORK_HOURS

LEFT JOIN

(SELECT ATR_USG.USAGES_SOURCE_ID,

ATR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY

FROM FUSION.HWM_TM_REP_ATRB_USAGES ATR_USG,

FUSION.HWM_TM_REP_ATRBS ATR,

FUSION.hwm_tm_atrb_flds_vl TAF

WHERE ATR_USG.LATEST_VERSION = 'Y'

AND ATR_USG.TM_REP_ATRB_ID = ATR.TM_REP_ATRB_ID

AND ATR.MASTER_ATTRIBUTE_ID = TAF.tm_atrb_fld_id

AND TAF.name = 'PayrollTimeType'

) ATRBS

ON REP_WORK_HOURS.TM_REC_ID = ATRBS.USAGES_SOURCE_ID

LEFT JOIN

(SELECT s.STATUS_VALUE,

s.tm_bldg_blk_id,

s.tm_bldg_blk_version

FROM fusion.HWM_TM_ACTIVE_STATUS_V s

WHERE s.STATUS_DEF_CD = 'A_APP_STATUS_PYR'

) PYR_STATUS

ON NVL(REP_WORK_HOURS.ORIG_TM_REC_ID, REP_WORK_HOURS.TM_REC_ID) = PYR_STATUS.tm_bldg_blk_id

AND NVL(REP_WORK_HOURS.ORIG_TM_REC_VERSION, REP_WORK_HOURS.TM_REC_VERSION) = PYR_STATUS.tm_bldg_blk_version