View: HXT_BATCH_SUM_AMOUNTS_HOURS_V

View Text

SELECT TIM.BATCH_ID
, ASM.ASSIGNMENT_NUMBER
, ELT.ELEMENT_NAME
, 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
, 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
, FFVL.FLEX_VALUE LABOR_DIST_CODE
, LOC.LOCATION_CODE LOCALITY_WORKED
, FFVR.FLEX_VALUE RATE_CODE
, HRW.SEPARATE_CHECK_FLAG
, HRW.FCL_TAX_RULE_CODE TAX_SEPARATELY_FLAG
, SUM(HRW.HOURS) HOURS_WORKED
, SUM(HRW.AMOUNT) AMOUNT
, 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
FROM HXT_TIMECARDS TIM
, PER_TIME_PERIODS PTP
, HXT_DET_HOURS_WORKED HRW
, PER_ASSIGNMENTS_F ASM
, PAY_ELEMENT_TYPES_F ELT
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, FND_FLEX_VALUES FFVL
, 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_LABOR_ACCOUNT_ID = FFVL.FLEX_VALUE_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.HOURS != 0
AND NVL(HRW.AMOUNT
, 0) != 0
AND HRW.PARENT_ID > 0
AND TIM.ID = HRW.TIM_ID
AND TIM.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID GROUP BY TIM.BATCH_ID
, ASM.ASSIGNMENT_NUMBER
, ASM.EFFECTIVE_END_DATE
, PCAK.COST_ALLOCATION_KEYFLEX_ID
, ELT.ELEMENT_NAME
, ELT.EFFECTIVE_END_DATE
, 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)
, HRW.HOURLY_RATE
, FFVL.FLEX_VALUE
, LOC.LOCATION_CODE
, FFVR.FLEX_VALUE
, RATE_MULTIPLE
, HRW.SEPARATE_CHECK_FLAG
, HRW.FCL_TAX_RULE_CODE
, 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)
, HRW.HOURS
, 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

Columns