View: HXT_WEEKLY_HOURS_WORKED_V

View Text

SELECT SUBSTR(TO_CHAR(HRW.DATE_WORKED + (TO_NUMBER(TO_CHAR(TRUNC( HRW.DATE_WORKED
, 'WW')
, 'D')) -2/* ERP WEEK_START */)
, 'YYYY' )
, 1
, 4) YEAR_NO
, SUBSTR(TO_CHAR(HRW.DATE_WORKED + (TO_NUMBER(TO_CHAR(TRUNC( HRW.DATE_WORKED
, 'WW')
, 'D' )) -2/* ERP WEEK_START */)
, 'WW' )
, 1
, 2) WEEK_NO
, /* ADJUSTS FOR DOW THAT JAN 1 HAPPENS TO FALL ON (DIFFERENT EACH YEAR) */ ASM.PERSON_ID
, SUBSTR(ELTV.HXT_EARNING_CATEGORY
, 1
, 10) EARNING_CATEGORY
, SUM(HRW.HOURS) WEEKLY_HOURS
FROM HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV
, PAY_ELEMENT_TYPES_F ELT
, HXT_EARNING_POLICIES EGP
, HXT_TIMECARDS TIM
, HXT_PER_AEI_DDF_V AEIV
, PER_ASSIGNMENTS_F ASM
, HXT_DET_HOURS_WORKED HRW
WHERE ASM.ASSIGNMENT_ID = HRW.ASSIGNMENT_ID
AND HRW.DATE_WORKED BETWEEN ASM.EFFECTIVE_START_DATE
AND ASM.EFFECTIVE_END_DATE
AND AEIV.ASSIGNMENT_ID=ASM.ASSIGNMENT_ID
AND AEIV.INFORMATION_TYPE='HXT_ASS_INFO'
AND TIM.ID = HRW.TIM_ID
AND ELT.ELEMENT_TYPE_ID = HRW.ELEMENT_TYPE_ID
AND ( /* IF ABSENCE
, ONLY INCLUDE EARNINGS TO BE COUNTED TOWARD HOURS TO BE WORKED BEFORE BEING ELIGIBLE FOR OVERTIME. */ (EXISTS (SELECT 1
FROM HXT_EARN_GROUPS ERG
WHERE ERG.EGT_ID = EGP.EGT_ID
AND ERG.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID )
AND ELTV.HXT_EARNING_CATEGORY = 'ABS'
AND EGP.EGT_ID IS NOT NULL ) OR ELTV.HXT_EARNING_CATEGORY IN('REG'
, 'OVT') )
AND HRW.DATE_WORKED BETWEEN EGP.EFFECTIVE_START_DATE
AND EGP.EFFECTIVE_END_DATE
AND EGP.ID = AEIV.HXT_EARNING_POLICY
AND HRW.DATE_WORKED BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND ELTV.ROW_ID = ELT.ROWID GROUP BY SUBSTR(TO_CHAR(HRW.DATE_WORKED + (TO_NUMBER(TO_CHAR(TRUNC( HRW.DATE_WORKED
, 'WW')
, 'D')) -2/* ERP WEEK_START */)
, 'YYYY' )
, 1
, 4)
, SUBSTR(TO_CHAR(HRW.DATE_WORKED + (TO_NUMBER(TO_CHAR(TRUNC( HRW.DATE_WORKED
, 'WW')
, 'D')) -2/* ERP WEEK_START */)
, 'WW' )
, 1
, 2)
, ASM.PERSON_ID
, SUBSTR(ELTV.HXT_EARNING_CATEGORY
, 1
, 10)

Columns