[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
deletedFlag boolean := true;
--p_timecard_rec.deleted := 'N';
deletedFlag := false;
--p_timecard_rec.deleted := 'Y';
if(deletedFlag) then
p_timecard_rec.deleted := 'Y';
p_timecard_rec.deleted := 'N';
select time_category_id
from hxc_time_Categories
where time_category_name = 'GARRYS TIME SQL TEST';
SELECT MAX( asg.effective_start_date)
, asg.assignment_id
FROM per_assignments_f asg
WHERE asg.person_id = p_resource_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type in ('E','C')
AND asg.effective_start_date <= TRUNC(p_tc_end)
AND asg.effective_end_date >= TRUNC(p_tc_start)
GROUP BY asg.assignment_id;
select 1
from hxc_tmp_blks;
IF ( p_timecard_info.deleted = 'N' )
THEN
if g_debug then
hr_utility.trace('Entering check_commit');
END IF; -- p_timecard_info.delete
l_message_table.DELETE;
l_message_table.DELETE; ----Bug#3090409
l_period_tab.delete;
select /*+ ORDERED *
tbbdet.start_time, tbbdet.stop_time
from hxc_time_building_blocks tbbdet,
hxc_time_building_blocks tbbday,
hxc_time_building_blocks tbbtc
where
tbbtc.scope = 'TIMECARD'
and tbbdet.scope = 'DETAIL'
and tbbdet.type = 'RANGE'
and tbbday.scope = 'DAY'
and tbbtc.time_building_block_id = tbbday.parent_building_block_id
and tbbtc.object_version_number = tbbday.parent_building_block_ovn
and tbbdet.parent_building_block_id = tbbday.time_building_block_id
and tbbdet.parent_building_block_ovn = tbbday.object_version_number
and trunc(tbbday.start_time) = trunc(p_day_date)
and tbbdet.resource_id = l_resource_id
and tbbday.resource_id = l_resource_id
and tbbtc.resource_id = l_resource_id
and tbbdet.date_to = hr_general.end_of_time;
SELECT /*+ LEADING(tbbday)
INDEX(tbbday HXC_TIME_BUILDING_BLOCKS_FK2)
INDEX(tbbdet HXC_TIME_BUILDING_BLOCKS_FK3)
INDEX(tbbtc HXC_TIME_BUILDING_BLOCKS_PK) */
tbbdet.start_time,
tbbdet.stop_time
FROM hxc_time_building_blocks tbbdet,
hxc_time_building_blocks tbbday,
hxc_time_building_blocks tbbtc
WHERE tbbtc.scope = 'TIMECARD'
AND tbbdet.scope = 'DETAIL'
AND tbbdet.type = 'RANGE'
AND tbbday.scope = 'DAY'
AND tbbtc.time_building_block_id = tbbday.parent_building_block_id
AND tbbtc.object_version_number = tbbday.parent_building_block_ovn
AND tbbdet.parent_building_block_id = tbbday.time_building_block_id
AND tbbdet.parent_building_block_ovn = tbbday.object_version_number
AND TRUNC(tbbday.start_time) = TRUNC(p_day_date)
AND tbbdet.resource_id = l_resource_id
AND tbbday.resource_id = l_resource_id
AND tbbtc.resource_id = l_resource_id
AND tbbdet.date_to = hr_general.end_of_time;
( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'DELETE_ONLY' AND l_timecard_info_rec.deleted = 'Y' ) OR
( p_operation = 'SUBMIT' AND r_rules.rule_usage = 'BOTH_EX_DEL' AND l_timecard_info_rec.deleted = 'N' )
)
THEN
if g_debug then
hr_utility.trace('');
SELECT NVL(SUM(SUM(NVL(hxc_time_category_utils_pkg.category_detail_hrs( tbb_detail.time_building_block_id,
tbb_detail.object_version_number),0)) ),0)
FROM
hxc_timecard_summary ts,
hxc_time_building_blocks tbb_day,
hxc_time_building_blocks tbb_detail
WHERE
tbb_day.time_building_block_id = tbb_detail.parent_building_block_id and
ts.timecard_id = tbb_day.parent_building_block_id and
tbb_detail.scope='DETAIL' and
tbb_detail.date_to=hr_general.end_of_time and
tbb_day.scope='DAY' and
tbb_day.type='RANGE' and
tbb_day.date_to=hr_general.end_of_time and
ts.resource_id = p_resource_id and
to_date(to_char(tbb_day.start_time,
'DD-MON-YYYY'),
'DD-MON-YYYY') BETWEEN p_start_date AND p_end_date
AND to_date(to_char(tbb_day.stop_time,
'DD-MON-YYYY'),
'DD-MON-YYYY') BETWEEN p_start_date AND p_end_date
group by ts.timecard_id ;
SELECT a.assignment_status_type_id
FROM per_assignment_status_types a
, per_assignments_f asg
WHERE
asg.assignment_id = p_assignment_id AND
TO_DATE(p_effective_date, 'YYYY/MM/DD HH24:MI:SS')
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND
asg.assignment_status_type_id = a.assignment_status_type_id;
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 1
FROM hxc_transactions t
, hxc_transaction_details td
WHERE td.time_building_block_id = p_timecard_id
AND
t.transaction_id = td.transaction_id AND
t.type = 'RETRIEVAL' AND
t.status = 'SUCCESS' );
SELECT pap.accrual_plan_id
FROM
pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
WHERE
pap.accrual_plan_id = p_accrual_plan_id AND
pap.accrual_plan_element_type_id = pet.element_type_id
AND
pet.element_type_id = pel.element_type_id AND
pee.effective_start_date BETWEEN
pet.effective_start_date AND pet.effective_end_date
AND
pel.element_link_id = pee.element_link_id AND
pee.effective_start_date BETWEEN
pel.effective_start_date AND pel.effective_end_date
AND
pee.assignment_id = p_assignment_id AND
to_date(p_effective_date, 'YYYY/MM/DD HH24:MI:SS')
BETWEEN pee.effective_start_date AND pee.effective_end_date;
SELECT detail.time_building_block_id, detail.TYPE, detail.measure,
detail.unit_of_measure, detail.start_time,
detail.stop_time, detail.parent_building_block_id,
'N' parent_is_new, detail.SCOPE,
detail.object_version_number, detail.approval_status,
detail.resource_id, detail.resource_type,
detail.approval_style_id, detail.date_from, detail.date_to,
detail.comment_text, detail.parent_building_block_ovn,
'N' NEW, 'N' changed
FROM hxc_time_building_blocks detail,
hxc_time_building_blocks DAY
WHERE DAY.parent_building_block_id =
p_timecard_rec.timecard_bb_id
AND DAY.parent_building_block_ovn =
p_timecard_rec.timecard_ovn
AND detail.date_to = hr_general.end_of_time
AND detail.SCOPE = 'DETAIL'
AND detail.parent_building_block_id =
DAY.time_building_block_id
AND detail.parent_building_block_ovn =
DAY.object_version_number
AND DAY.SCOPE = 'DAY'
AND DAY.date_to = hr_general.end_of_time;
SELECT a.time_attribute_id, au.time_building_block_id,
bbit.bld_blk_info_type, a.attribute_category, a.attribute1,
a.attribute2, a.attribute3, a.attribute4, a.attribute5,
a.attribute6, a.attribute7, a.attribute8, a.attribute9,
a.attribute10, a.attribute11, a.attribute12, a.attribute13,
a.attribute14, a.attribute15, a.attribute16, a.attribute17,
a.attribute18, a.attribute19, a.attribute20, a.attribute21,
a.attribute22, a.attribute23, a.attribute24, a.attribute25,
a.attribute26, a.attribute27, a.attribute28, a.attribute29,
a.attribute30, a.bld_blk_info_type_id,
a.object_version_number, 'N' NEW, 'N' changed
FROM hxc_time_attributes a,
hxc_time_attribute_usages au,
hxc_bld_blk_info_types bbit
WHERE au.time_building_block_id = p_building_block_id
AND au.time_building_block_ovn = p_bb_ovn
AND au.time_attribute_id = a.time_attribute_id
AND NOT (a.attribute_category = 'TEMPLATES')
AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND a.object_version_number =
(SELECT MAX (object_version_number)
FROM hxc_time_attributes
WHERE time_attribute_id = a.time_attribute_id);
SELECT /*+ LEADING(AU)
INDEX(AU HXC_TIME_ATTRIBUTE_USAGES_FK2)
INDEX(A HXC_TIME_ATTRIBUTES_PK)
INDEX(BBIT HXC_BUILD_BLK_INFO_TYPES_PK) */
a.time_attribute_id, au.time_building_block_id,
bbit.bld_blk_info_type, a.attribute_category, a.attribute1,
a.attribute2, a.attribute3, a.attribute4, a.attribute5,
a.attribute6, a.attribute7, a.attribute8, a.attribute9,
a.attribute10, a.attribute11, a.attribute12, a.attribute13,
a.attribute14, a.attribute15, a.attribute16, a.attribute17,
a.attribute18, a.attribute19, a.attribute20, a.attribute21,
a.attribute22, a.attribute23, a.attribute24, a.attribute25,
a.attribute26, a.attribute27, a.attribute28, a.attribute29,
a.attribute30, a.bld_blk_info_type_id,
a.object_version_number, 'N' NEW, 'N' changed
FROM hxc_time_attributes a,
hxc_time_attribute_usages au,
hxc_bld_blk_info_types bbit
WHERE au.time_building_block_id = p_building_block_id
AND au.time_building_block_ovn = p_bb_ovn
AND au.time_attribute_id = a.time_attribute_id
AND NOT (a.attribute_category = 'TEMPLATES')
AND a.bld_blk_info_type_id = bbit.bld_blk_info_type_id ;
p_timecard_building_blocks.DELETE;
p_timecard_attributes.DELETE;
p_tbb_deleted BOOLEAN,
p_change_att_tab IN OUT NOCOPY t_change_att_tab
)
RETURN VARCHAR2
IS
CURSOR csr_get_mapping_name (
p_attribute VARCHAR2,
p_attribute_id NUMBER,
p_att_ovn NUMBER
)
IS
SELECT hmc.field_name
FROM hxc_mapping_components hmc, hxc_time_attributes hta
WHERE hta.time_attribute_id = p_attribute_id
AND hta.object_version_number = p_att_ovn
AND hta.bld_blk_info_type_id = hmc.bld_blk_info_type_id
AND hmc.SEGMENT = UPPER (p_attribute);
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
OR p_tbb_deleted)
)
THEN
l_mapping_name := NULL;
p_tbb_deleted BOOLEAN,
p_change_att_tab IN OUT NOCOPY t_change_att_tab
)
RETURN VARCHAR2
IS
CURSOR csr_get_mapping_name(p_segment varchar2)
IS
SELECT hmc.field_name
FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
WHERE hmc.segment = upper(p_segment)
AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';
select substr(fcu.form_left_prompt,1,30)
from hxc_mapping_components mc
,hxc_bld_blk_info_types bbit
,fnd_descr_flex_col_usage_tl fcu
where
mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
and fcu.application_column_name = mc.segment
and fcu.descriptive_flex_context_code = bbit.bld_blk_info_type
and fcu.descriptive_flexfield_name = 'OTC Information Types'
and fcu.application_id = 809
and fcu.language = userenv('LANG')
and fcu.application_column_name=p_segment
AND bbit.bld_blk_info_type='BUILDING_BLOCKS';
(p_block1.measure is not null and p_tbb_deleted ))
THEN
if g_debug then
hr_utility.TRACE ('Before assignment');
(p_block1.start_time is not null and p_tbb_deleted )
)
THEN
if g_debug then
hr_utility.TRACE ( 'l_ret_val'
|| l_ret_val);
(p_block1.stop_time is not null and p_tbb_deleted )
)
THEN
OPEN csr_get_mapping_name('ATTRIBUTE3');
(p_block1.comment_text is not null and p_tbb_deleted )
)
THEN
OPEN csr_get_mapping_name('ATTRIBUTE4');
SELECT hmc.field_name
FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
WHERE hmc.segment = upper(p_segment)
AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';*/
l_tbb_deleted BOOLEAN;
l_tbb_deleted := FALSE;
L_CHANGE_ATT_TAB.delete;
l_tbb_deleted := TRUE;
l_tbb_deleted := FALSE;
EXIT WHEN NOT l_old_tco_bb.EXISTS (l_old_tbb_index); -- OR l_tbb_deleted;
l_tbb_deleted,
l_change_att_tab
);
l_tbb_deleted,
l_change_att_tab
);
/* if (l_tbb_deleted ) then
open csr_get_mapping_name('attribute1');
SELECT substr(fcu.form_left_prompt,1,30) prompt
FROM hxc_mapping_components mc
,hxc_bld_blk_info_types bbit
,fnd_descr_flex_col_usage_tl fcu
,hxc_time_attributes hta
WHERE mc.SEGMENT= UPPER(p_attribute) --mapping_component_id = p_comp_id
AND mc.bld_blk_info_type_id = hta.bld_blk_info_type_id
AND hta.time_attribute_id = p_block_id
AND hta.object_version_number = p_blk_ovn
AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND fcu.application_column_name = mc.segment
AND fcu.descriptive_flex_context_code = bbit.bld_blk_info_type
AND fcu.descriptive_flexfield_name = 'OTC Information Types'
AND fcu.application_id = 809
AND fcu.language = userenv('LANG');
SELECT hmc.field_name
FROM hxc_mapping_components hmc, hxc_bld_blk_info_types hbb
WHERE hmc.segment = upper(p_attribute)
AND hbb.BLD_BLK_INFO_TYPE_ID = hmc.bld_blk_info_type_id
AND hbb.BLD_BLK_INFO_TYPE = 'BUILDING_BLOCKS';
select 'Y' from hxc_data_sets
where status in ('OFF_LINE','BACKUP_IN_PROGRESS','RESTORE_IN_PROGRESS')
and trunc(p_start_date) <=end_date
and trunc(p_end_date) >=start_date;
select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;