[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
petf.element_type_id element_type_id
from
hr_lookups hl,
pay_element_types_f petf
where
upper(petf.element_name) = hl.lookup_code
and hl.lookup_type = p_lookup_type
and petf.effective_end_date = hr_general.end_of_time;
SELECT
DECODE(p_element_segment,
'ATTRIBUTE1' , ta_det.attribute1,
'ATTRIBUTE2' , ta_det.attribute2,
'ATTRIBUTE3' , ta_det.attribute3,
'ATTRIBUTE4' , ta_det.attribute4,
'ATTRIBUTE5' , ta_det.attribute5,
'ATTRIBUTE6' , ta_det.attribute6,
'ATTRIBUTE7' , ta_det.attribute7,
'ATTRIBUTE8' , ta_det.attribute8,
'ATTRIBUTE9' , ta_det.attribute9,
'ATTRIBUTE10' , ta_det.attribute10,
'ATTRIBUTE11' , ta_det.attribute11,
'ATTRIBUTE12' , ta_det.attribute12,
'ATTRIBUTE13' , ta_det.attribute13,
'ATTRIBUTE14' , ta_det.attribute14,
'ATTRIBUTE15' , ta_det.attribute15,
'ATTRIBUTE16' , ta_det.attribute16,
'ATTRIBUTE17' , ta_det.attribute17,
'ATTRIBUTE18' , ta_det.attribute18,
'ATTRIBUTE19' , ta_det.attribute19,
'ATTRIBUTE20' , ta_det.attribute20,
'ATTRIBUTE21' , ta_det.attribute21,
'ATTRIBUTE22' , ta_det.attribute22,
'ATTRIBUTE23' , ta_det.attribute23,
'ATTRIBUTE24' , ta_det.attribute24,
'ATTRIBUTE25' , ta_det.attribute25,
'ATTRIBUTE26' , ta_det.attribute26,
'ATTRIBUTE27' , ta_det.attribute27,
'ATTRIBUTE28' , ta_det.attribute28,
'ATTRIBUTE29' , ta_det.attribute29,
'ATTRIBUTE30' , ta_det.attribute30,
'ATTRIBUTE_CATEGORY', ta_det.attribute_category) element_id,
tbb_det.measure,
tbb_det.unit_of_measure,
tbb_det.start_time,
tbb_det.stop_time,
tbb_det.type,
tbb_det.scope
FROM hxc_time_building_blocks tbb_day,
hxc_time_building_blocks tbb_det,
hxc_time_attribute_usages tau_det,
hxc_time_attributes ta_det
WHERE tbb_det.scope = 'DETAIL'
and tbb_day.scope = 'DAY'
and tbb_det.date_to = hr_general.end_of_time
and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
and tbb_day.parent_building_block_id = p_timecard_id
and tbb_day.parent_building_block_ovn = p_timecard_ovn
and tau_det.time_building_block_id = tbb_det.time_building_block_id
and tau_det.time_building_block_ovn = tbb_det.object_version_number
and tau_det.time_attribute_id = ta_det.time_attribute_id
and ta_det.attribute_category = nvl(p_element_context,ta_det.attribute_category)
and ta_det.BLD_BLK_INFO_TYPE_ID in
(select BLD_BLK_INFO_TYPE_ID from HXC_BLD_BLK_INFO_TYPE_USAGES
where BUILDING_BLOCK_CATEGORY=p_element_category);
select ma.context context,
ma.segment segment,
bbitu.building_block_category category
from hxc_mapping_attributes_v ma,
hxc_deposit_processes dp,
hxc_mappings mp,
hxc_bld_blk_info_type_usages bbitu
where ma.map = mp.name
and mp.mapping_id = dp.mapping_id
and dp.name = 'OTL Deposit Process'
and ma.field_name = 'Dummy Element Context'
and bbitu.bld_blk_info_type_id=ma.bld_blk_info_type_id;
select
petf.element_type_id element_type_id
from
hr_lookups hl,
pay_element_types_f petf
where
upper(petf.element_name) = hl.lookup_code
and hl.lookup_type = p_lookup_type
and petf.effective_end_date = hr_general.end_of_time;
SELECT
DECODE(p_element_segment,
'ATTRIBUTE1' , ta_det.attribute1,
'ATTRIBUTE2' , ta_det.attribute2,
'ATTRIBUTE3' , ta_det.attribute3,
'ATTRIBUTE4' , ta_det.attribute4,
'ATTRIBUTE5' , ta_det.attribute5,
'ATTRIBUTE6' , ta_det.attribute6,
'ATTRIBUTE7' , ta_det.attribute7,
'ATTRIBUTE8' , ta_det.attribute8,
'ATTRIBUTE9' , ta_det.attribute9,
'ATTRIBUTE10' , ta_det.attribute10,
'ATTRIBUTE11' , ta_det.attribute11,
'ATTRIBUTE12' , ta_det.attribute12,
'ATTRIBUTE13' , ta_det.attribute13,
'ATTRIBUTE14' , ta_det.attribute14,
'ATTRIBUTE15' , ta_det.attribute15,
'ATTRIBUTE16' , ta_det.attribute16,
'ATTRIBUTE17' , ta_det.attribute17,
'ATTRIBUTE18' , ta_det.attribute18,
'ATTRIBUTE19' , ta_det.attribute19,
'ATTRIBUTE20' , ta_det.attribute20,
'ATTRIBUTE21' , ta_det.attribute21,
'ATTRIBUTE22' , ta_det.attribute22,
'ATTRIBUTE23' , ta_det.attribute23,
'ATTRIBUTE24' , ta_det.attribute24,
'ATTRIBUTE25' , ta_det.attribute25,
'ATTRIBUTE26' , ta_det.attribute26,
'ATTRIBUTE27' , ta_det.attribute27,
'ATTRIBUTE28' , ta_det.attribute28,
'ATTRIBUTE29' , ta_det.attribute29,
'ATTRIBUTE30' , ta_det.attribute30,
'ATTRIBUTE_CATEGORY', ta_det.attribute_category) element_id,
tbb_det.measure,
tbb_det.unit_of_measure,
tbb_det.start_time,
tbb_det.stop_time,
tbb_det.type,
tbb_det.scope
FROM hxc_time_building_blocks tbb_day,
hxc_time_building_blocks tbb_det,
hxc_time_attribute_usages tau_det,
hxc_time_attributes ta_det
WHERE tbb_det.scope = 'DETAIL'
and tbb_day.scope = 'DAY'
and tbb_day.date_to = hr_general.end_of_time
and tbb_det.date_to = hr_general.end_of_time
and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
and tbb_day.start_time >= p_period_start_time
and tbb_day.stop_time <= p_period_stop_time
and tbb_det.resource_id = p_resource_id
and tau_det.time_building_block_id = tbb_det.time_building_block_id
and tau_det.time_building_block_ovn = tbb_det.object_version_number
and tau_det.time_attribute_id = ta_det.time_attribute_id
and ta_det.attribute_category = nvl(p_element_context,ta_det.attribute_category)
and ta_det.BLD_BLK_INFO_TYPE_ID in
(select BLD_BLK_INFO_TYPE_ID from HXC_BLD_BLK_INFO_TYPE_USAGES
where BUILDING_BLOCK_CATEGORY=p_element_category);
select ma.context context,
ma.segment segment,
bbitu.building_block_category category
from hxc_mapping_attributes_v ma,
hxc_deposit_processes dp,
hxc_mappings mp,
hxc_bld_blk_info_type_usages bbitu
where ma.map = mp.name
and mp.mapping_id = dp.mapping_id
and dp.name = 'OTL Deposit Process'
and ma.field_name = 'Dummy Element Context'
and bbitu.bld_blk_info_type_id=ma.bld_blk_info_type_id;
select
sum(decode(tbb_det.type,
'MEASURE',nvl(tbb_det.measure,0),
'RANGE', nvl(tbb_det.stop_time-tbb_det.start_time,0)*24)) into l_sum
FROM hxc_time_building_blocks tbb_day,
hxc_time_building_blocks tbb_det,
hxc_time_building_blocks tbb_tim
WHERE tbb_det.scope = 'DETAIL'
and tbb_day.scope = 'DAY'
and tbb_tim.scope = 'TIMECARD'
and tbb_det.date_to = hr_general.end_of_time
and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
and tbb_day.parent_building_block_ovn = tbb_tim.object_version_number
and tbb_day.parent_building_block_id = tbb_tim.time_building_block_id
and tbb_day.start_time >= l_start_time
and tbb_day.stop_time <= l_stop_time
and tbb_det.resource_id = p_resource_id;
select
sum(decode(tbb_det.type,
'MEASURE',nvl(tbb_det.measure,0),
'RANGE', nvl(tbb_det.stop_time-tbb_det.start_time,0)*24)) into l_sum
FROM hxc_time_building_blocks tbb_day,
hxc_time_building_blocks tbb_det,
hxc_time_building_blocks tbb_tim
WHERE tbb_det.scope = 'DETAIL'
and tbb_day.scope = 'DAY'
and tbb_tim.scope = 'TIMECARD'
and tbb_day.parent_building_block_ovn = tbb_tim.object_version_number
and tbb_day.parent_building_block_id = tbb_tim.time_building_block_id
and tbb_det.date_to = hr_general.end_of_time
and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
and tbb_day.parent_building_block_id = p_timecard_id
and tbb_day.parent_building_block_ovn = p_timecard_ovn;