View: HXT_BATCH_SUM_HOURS_ROLLUP_V

Product:	HXT - Time and Labor
Description:
Implementation/DBA Data:	APPS.HXT_BATCH_SUM_HOURS_ROLLUP_V

View Text

SELECT PCAK.COST_ALLOCATION_KEYFLEX_ID
, HRW.ELEMENT_TYPE_ID
, HRW.ASSIGNMENT_ID
, TIM.BATCH_ID
, ASM.ASSIGNMENT_NUMBER
, PCAK.CONCATENATED_SEGMENTS
, LEAST(PTP.END_DATE
, ASM.EFFECTIVE_END_DATE) DATE_WORKED
, ELTT.ELEMENT_NAME
, LOOK.MEANING REASON
, 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
, SUM(HRW.HOURS) HOURS
, HRW.AMOUNT
, HRW.RATE_MULTIPLE
, HRW.HOURLY_RATE
, FFVR.FLEX_VALUE RATE_CODE
, LOCT.LOCATION_CODE
, HRW.FCL_TAX_RULE_CODE
, HRW.SEPARATE_CHECK_FLAG
, NULL HRW_ROWID
, HRW.STATE_NAME
, HRW.COUNTY_NAME
, HRW.CITY_NAME
, HRW.ZIP_CODE
FROM PER_TIME_PERIODS PTP
, HXT_TIMECARDS_F TIM
, PER_ASSIGNMENTS_F ASM
, PAY_ELEMENT_TYPES_F ELT
, PAY_ELEMENT_TYPES_F_TL ELTT
, HR_LOOKUPS LOOK
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, FND_FLEX_VALUES FFVR
, HR_LOCATIONS_ALL_TL LOCT
, HR_LOCATIONS_NO_JOIN LOC
, HXT_DET_HOURS_WORKED_F HRW
WHERE TRUNC(SYSDATE) BETWEEN HRW.EFFECTIVE_START_DATE
AND HRW.EFFECTIVE_END_DATE
AND HRW.PARENT_ID > 0
AND HRW.AMOUNT IS NULL
AND HRW.LOCATION_ID = LOC.LOCATION_ID(+)
AND LOC.LOCATION_ID = LOCT.LOCATION_ID(+)
AND DECODE(LOCT.LOCATION_ID
, NULL
, '1'
, LOCT.LANGUAGE) = DECODE(LOCT.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND HRW.FFV_RATE_CODE_ID = FFVR.FLEX_VALUE_ID(+)
AND HRW.FFV_COST_CENTER_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID(+)
AND HRW.DATE_WORKED BETWEEN NVL(LOOK.START_DATE_ACTIVE
, HRW.DATE_WORKED)
AND NVL(LOOK.END_DATE_ACTIVE
, HRW.DATE_WORKED)
AND LOOK.LOOKUP_TYPE(+) = 'ELE_ENTRY_REASON'
AND LOOK.LOOKUP_CODE(+) = HRW.FCL_EARN_REASON_CODE
AND HRW.DATE_WORKED BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND HRW.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ELTT.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ELTT.LANGUAGE = USERENV('LANG')
AND HRW.DATE_WORKED BETWEEN ASM.EFFECTIVE_START_DATE
AND ASM.EFFECTIVE_END_DATE
AND HRW.ASSIGNMENT_ID = ASM.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN TIM.EFFECTIVE_START_DATE
AND TIM.EFFECTIVE_END_DATE
AND TIM.ID = HRW.TIM_ID
AND PTP.TIME_PERIOD_ID = TIM.TIME_PERIOD_ID GROUP BY PCAK.COST_ALLOCATION_KEYFLEX_ID
, HRW.ELEMENT_TYPE_ID
, HRW.ASSIGNMENT_ID
, TIM.BATCH_ID
, ASM.ASSIGNMENT_NUMBER
, PCAK.CONCATENATED_SEGMENTS
, LEAST(PTP.END_DATE
, ASM.EFFECTIVE_END_DATE)
, ELTT.ELEMENT_NAME
, LOOK.MEANING
, 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
, HRW.AMOUNT
, HRW.RATE_MULTIPLE
, HRW.HOURLY_RATE
, FFVR.FLEX_VALUE
, LOCT.LOCATION_CODE
, HRW.FCL_TAX_RULE_CODE
, HRW.SEPARATE_CHECK_FLAG
, HRW.STATE_NAME
, HRW.COUNTY_NAME
, HRW.CITY_NAME
, HRW.ZIP_CODE

Columns