View: HXT_BATCH_SUM_HOURS_V

View Text

SELECT HRW.DATE_WORKED
, TIM.BATCH_ID
, ASM.ASSIGNMENT_NUMBER
, ELT.ELEMENT_NAME
, /* USE THE LATEST OF PPD_START
, ELT_START
,
AND ASM_START */ DECODE( SIGN( DECODE( SIGN(PTP.START_DATE-ASM.EFFECTIVE_START_DATE)
, 1
, PTP.START_DATE
, ASM.EFFECTIVE_START_DATE)-ELT.EFFECTIVE_START_DATE)
, 1
, DECODE( SIGN(PTP.START_DATE-ASM.EFFECTIVE_START_DATE)
, 1
, PTP.START_DATE
, ASM.EFFECTIVE_START_DATE)
, ELT.EFFECTIVE_START_DATE) FROM_DATE
, /* USE THE EARLIEST OF PPD_END
, ELT_END
,
AND ASM_END */ DECODE( SIGN( DECODE( SIGN(PTP.END_DATE-ASM.EFFECTIVE_END_DATE)
, -1
, PTP.END_DATE
, ASM.EFFECTIVE_END_DATE)-ELT.EFFECTIVE_END_DATE)
, -1
, DECODE( SIGN(PTP.END_DATE-ASM.EFFECTIVE_END_DATE)
, -1
, PTP.END_DATE
, ASM.EFFECTIVE_END_DATE)
, ELT.EFFECTIVE_END_DATE) TO_DATE
, RATE_MULTIPLE
, HRW.HOURLY_RATE
, PCAK.COST_ALLOCATION_KEYFLEX_ID
, PCAK.CONCATENATED_SEGMENTS
, PCAK.SEGMENT1
, PCAK.SEGMENT2
, PCAK.SEGMENT3
, PCAK.SEGMENT4
, PCAK.SEGMENT5
, PCAK.SEGMENT6
, PCAK.SEGMENT7
, PCAK.SEGMENT8
, PCAK.SEGMENT9
, PCAK.SEGMENT10
, PCAK.SEGMENT11
, PCAK.SEGMENT12
, PCAK.SEGMENT13
, PCAK.SEGMENT14
, PCAK.SEGMENT15
, PCAK.SEGMENT16
, PCAK.SEGMENT17
, PCAK.SEGMENT18
, PCAK.SEGMENT19
, PCAK.SEGMENT20
, PCAK.SEGMENT21
, PCAK.SEGMENT22
, PCAK.SEGMENT23
, PCAK.SEGMENT24
, PCAK.SEGMENT25
, PCAK.SEGMENT26
, PCAK.SEGMENT27
, PCAK.SEGMENT28
, PCAK.SEGMENT29
, PCAK.SEGMENT30
, LOC.LOCATION_CODE LOCALITY_WORKED
, FFVR.FLEX_VALUE RATE_CODE
, HRW.SEPARATE_CHECK_FLAG
, HRW.FCL_TAX_RULE_CODE TAX_SEPARATELY_FLAG
, HRW.AMOUNT
, HRW.HOURS
, HRW.ROWID HRW_ROWID
FROM HXT_TIMECARDS_F TIM
, PER_TIME_PERIODS PTP
, HXT_DET_HOURS_WORKED_F HRW
, PER_ASSIGNMENTS_F ASM
, PAY_ELEMENT_TYPES_F ELT
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, HR_LOCATIONS LOC
, FND_FLEX_VALUES FFVR
WHERE HRW.FFV_RATE_CODE_ID = FFVR.FLEX_VALUE_ID(+)
AND HRW.LOCATION_ID = LOC.LOCATION_ID(+)
AND HRW.FFV_COST_CENTER_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID(+)
AND HRW.DATE_WORKED BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND HRW.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND HRW.DATE_WORKED BETWEEN ASM.EFFECTIVE_START_DATE
AND ASM.EFFECTIVE_END_DATE
AND HRW.ASSIGNMENT_ID = ASM.ASSIGNMENT_ID
AND HRW.AMOUNT IS NULL
AND HRW.PARENT_ID > 0
AND TIM.ID = HRW.TIM_ID
AND TIM.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND HRW.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND HRW.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
AND TIM.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND TIM.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)

Columns