VIEW: APPS.HXT_BATCH_SUM_HOURS_ROLLUP_V

Source

View Text - Preformatted

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, elt.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, max(hrw.parent_id) FROM per_time_periods ptp, hxt_timecards_f tim, per_assignments_f asm, pay_element_types_f elt, 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 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), elt.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

View Text - HTML Formatted

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
, ELT.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
, MAX(HRW.PARENT_ID)
FROM PER_TIME_PERIODS PTP
, HXT_TIMECARDS_F TIM
, PER_ASSIGNMENTS_F ASM
, PAY_ELEMENT_TYPES_F ELT
, 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 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)
, ELT.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