[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE synchronize_deletes_in_otlr (
p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info,
p_time_att_info IN hxc_self_service_time_deposit.app_attributes_info,
p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
p_timecard_source IN VARCHAR -- added for 5137310
)
IS
l_time_building_blocks hxc_self_service_time_deposit.timecard_info
:= p_time_building_blocks;
SELECT ID, tim_id
FROM hxt_sum_hours_worked_f
WHERE time_building_block_id = day_bb_id;
SELECT tbb.time_building_block_id
FROM hxc_time_building_blocks tbb
WHERE tbb.parent_building_block_id IN (
SELECT /*+ INDEX( tbb1 HXC_TIME_BUILDING_BLOCKS_FK2)*/
time_building_block_id
FROM hxc_time_building_blocks tbb1
WHERE tbb1.resource_id = l_resource_id
AND tbb1.resource_type = 'PERSON'
AND tbb1.start_time = l_parent_start_time
AND tbb1.SCOPE = 'DAY')
AND tbb.object_version_number =
(SELECT /*+ NO_UNNEST */
MAX (dyovn.object_version_number)
FROM hxc_time_building_blocks dyovn
WHERE dyovn.time_building_block_id =
tbb.time_building_block_id
AND dyovn.date_to <> hr_general.end_of_time)
AND tbb.date_to <> hr_general.end_of_time
AND EXISTS (
SELECT /*+ LEADING(txd)
INDEX(txd HXC_TRANSACTION_DETAILS_FK1)
INDEX(tx HXC_TRANSACTIONS_PK) */
'x'
FROM hxc_transaction_details txd, hxc_transactions tx
WHERE tx.transaction_process_id = -1
AND tx.TYPE = 'RETRIEVAL'
AND tx.status = 'SUCCESS'
AND tx.transaction_id = txd.transaction_id
AND txd.status = 'SUCCESS'
AND txd.time_building_block_id =
tbb.time_building_block_id
AND txd.time_building_block_ovn <=
tbb.object_version_number);
(select 'x'
FROM hxc_transaction_details txd1
,hxc_transactions tx1
WHERE tx1.transaction_process_id = -1
AND tx1.type = 'RETRIEVAL'
AND tx1.status = 'SUCCESS'
AND tx1.transaction_id = txd1.transaction_id
AND txd1.status = 'SUCCESS'
AND txd1.time_building_block_id = tbb.time_building_block_id
AND txd1.time_building_block_ovn = tbb.object_version_number
);*/
l_dt_update_mode VARCHAR2 (30);
l_proc := 'hxt_hxc_retrieval_process.synchronize_deletes_in_otlr';
/* select start_time into l_parent_start_time
from hxc_time_building_blocks
where time_building_block_id = l_parent_bb_id
and object_version_number = l_parent_bb_ovn; */
o_dt_update_mod => l_dt_update_mode,
o_error_message => l_otm_error,
o_return_code => o_return_code,
p_parent_id => l_time_summary_id
);
IF(l_dt_update_mode IS NULL)
THEN
l_dt_update_mode := 'UPDATE';
IF l_dt_update_mode = 'UPDATE'
THEN
-- Check if user has entered zero hours while deleting a TC row in which case
-- we need to synchronize the deletes in OTLR. We also need to synchronize the deletes
-- in case user has replaced straight hours with start/stop time
BEGIN
SELECT count(*)
INTO l_measure_count
FROM hxc_time_building_blocks
WHERE time_building_block_id = l_day_bb_id
AND ((measure = 0) or (start_time is not null and stop_time is not null))
AND scope = 'DETAIL'
AND date_to = hr_general.end_of_time;
IF (l_dt_update_mode = 'CORRECTION')
OR (l_dt_update_mode = 'UPDATE' AND l_measure_count <> 0) /*** 4890370 ***/
THEN
-- End Bug 4590163
--
-- Delete detail rows associated with summary row.
--
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = l_time_summary_id;
DELETE FROM hxt_sum_hours_worked_f
WHERE ID = l_time_summary_id;
SELECT hshw.tim_id, ht.time_period_id
FROM hxt_sum_hours_worked hshw, hxt_timecards ht
WHERE hshw.ID = p_tim_sum_id AND hshw.tim_id = ht.ID;
SELECT 'X'
FROM hxc_debug
WHERE process = 'otm_validate_timecard'
AND TRUNC (debug_date) <= SYSDATE;
l_delete VARCHAR2 (1);
AND ( -- First process deleted detail records
( l_date_to <> hr_general.end_of_time
AND l_new = 'N'
AND i = 1
)
-- Next process the updated detail records
OR ( l_date_to = hr_general.end_of_time
AND l_new = 'N'
AND i = 2
)
-- And the last to be processed are the Inserts
OR ( l_date_to = hr_general.end_of_time
AND l_new = 'Y'
AND i = 3
)
) --2966729
)
-- bug 3650967
OR ( ( (l_type = 'MEASURE' AND l_measure IS NULL)
OR ( l_type = 'RANGE'
AND l_start_time IS NULL
AND l_stop_time IS NULL
)
)
AND l_date_to <> hr_general.end_of_time
AND l_new = 'N'
AND i = 1
)
-- bug 3650967
)
AND l_scope = 'DETAIL'
THEN
-- (l_date_to = hr_general.end_of_time) THEN
--Bug 2770487 Sonarasi 04-Apr-2003
--Commented the above check l_date_to = hr_general.end_of_time because we need
--the deleted blocks also to be considered for explosion.
--Bug 2770487 Sonarasi Over
--
IF g_debug
THEN
hr_utility.set_location (l_proc, 21);
SELECT full_name, business_group_id
INTO hxt_otc_retrieval_interface.g_full_name, l_bg_id
FROM per_all_people_f
WHERE person_id = l_person_id
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
l_field_name.DELETE;
l_value.DELETE;
l_category.DELETE;
l_context.DELETE;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = l_time_summary_id;
l_delete := 'N';
l_delete := 'Y';
last_updated_by => '-1',
last_update_login => '-1',
dt_update_mode => 'CORRECTION',
created_tim_sum_id => l_created_tim_sum_id,
otm_error => l_otm_error,
oracle_error => l_oracle_error,
p_time_building_block_id => l_bb_id,
p_time_building_block_ovn => l_ovn,
p_validate => FALSE,
delete_yn => l_delete,
p_state_name => l_state_name,
p_county_name => l_county_name,
p_city_name => l_city_name,
p_zip_code => l_zip_code
);
g_otm_messages.DELETE;
l_timecards.DELETE;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = l_tim_sum_id_tab (l_tim_sum);
dt_update_mode => 'CORRECTION',
otm_error => l_otm_error,
oracle_error => l_oracle_error
);
g_otm_messages.DELETE;
SELECT DISTINCT (rrc.time_recipient_id)
FROM hxc_retrieval_rule_comps rrc,
hxc_retrieval_rules rr
WHERE rr.retrieval_rule_id = p_rtr_id
AND rrc.retrieval_rule_id = rr.retrieval_rule_id
AND rrc.status <> 'WORKING';
SELECT apsc.time_recipient_id
FROM hxc_application_set_comps_v apsc, hxc_application_sets_v aps
WHERE aps.application_set_id = p_app_set_id
AND apsc.application_set_id = aps.application_set_id;
synchronize_deletes_in_otlr
(p_time_building_blocks => l_time_building_blocks,
p_time_att_info => l_time_att_info,
p_messages => p_messages,
p_timecard_source => NULL
);
SELECT date_worked, hours, time_in, time_out, element_type_id
FROM hxt_det_hours_worked
WHERE parent_id = p_tim_sum_id;
SELECT hshw.tim_id, ht.time_period_id
FROM hxt_sum_hours_worked hshw, hxt_timecards ht
WHERE hshw.ID = p_tim_sum_id AND hshw.tim_id = ht.ID;
SELECT 'X'
FROM hxc_debug
WHERE process = 'hxt_hxc_retrieval_process'
AND TRUNC (debug_date) <= SYSDATE;
l_delete VARCHAR2 (1);
g_an_id.DELETE;
synchronize_deletes_in_otlr
(p_time_building_blocks => l_time_building_blocks,
p_time_att_info => l_time_attributes,
p_messages => p_messages,
p_timecard_source => 'Timecard Review'
);
l_timecard_detail.DELETE;
l_detail_attributes (l).updated := NULL;
l_detail_attributes.DELETE;
l_tim_sum_id_tab.DELETE;
AND ( -- First process deleted detail records
( l_date_to <> hr_general.end_of_time
AND l_new = 'N'
AND i = 1
)
-- Next process the updated detail records
OR ( l_date_to = hr_general.end_of_time
AND l_new = 'N'
AND i = 2
)
-- And the last to be processed are the Inserts
OR ( l_date_to = hr_general.end_of_time
AND l_new = 'Y'
AND i = 3
)
) --2966729
)
-- start bug 3650967
OR ( ( (l_type = 'MEASURE' AND l_measure IS NULL)
OR ( l_type = 'RANGE'
AND l_start_time IS NULL
AND l_stop_time IS NULL
)
)
AND l_date_to <> hr_general.end_of_time
AND l_new = 'N'
AND i = 1
)
-- end bug 3650967
)
AND l_scope = 'DETAIL'
THEN
-- (l_date_to = hr_general.end_of_time) THEN
--Bug 2770487 Sonarasi 04-Apr-2003
--Commented the above check l_date_to = hr_general.end_of_time because we need
--the deleted blocks also to be considered for explosion.
--Bug 2770487 Sonarasi Over
--
l_valid := 'Y';
SELECT full_name, business_group_id
INTO g_full_name, l_bg_id
FROM per_all_people_f
WHERE person_id = l_person_id
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
l_field_name.DELETE;
l_value.DELETE;
l_category.DELETE;
l_context.DELETE;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = l_time_summary_id;
l_delete := 'N';
l_delete := 'Y';
last_updated_by => '-1',
last_update_login => '-1',
dt_update_mode => 'CORRECTION',
created_tim_sum_id => l_created_tim_sum_id,
otm_error => l_otm_error,
oracle_error => l_oracle_error,
p_time_building_block_id => l_bb_id,
p_time_building_block_ovn => l_bb_ovn,
p_validate => FALSE,
delete_yn => l_delete,
p_state_name => l_state_name,
p_county_name => l_county_name,
p_city_name => l_city_name,
p_zip_code => l_zip_code
);
g_otm_messages.DELETE;
l_detail_attributes (l_next_index).updated := NULL;
l_timecards.DELETE;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = l_tim_sum_id_tab (l_tim_sum);
dt_update_mode => 'CORRECTION',
otm_error => l_otm_error,
oracle_error => l_oracle_error
);
g_otm_messages.DELETE;
l_detail_attributes (l_next_index).updated := NULL;
SELECT mc.SEGMENT, bbit.bld_blk_info_type_id
FROM hxc_mapping_components mc,
hxc_mapping_comp_usages mcu,
hxc_mappings m,
hxc_deposit_processes dp,
hxc_bld_blk_info_types bbit,
hxc_bld_blk_info_type_usages bbui
WHERE dp.mapping_id = m.mapping_id
AND dp.deposit_process_id = p_deposit_process_id --AI3
AND m.mapping_id = mcu.mapping_id
AND mcu.mapping_component_id = mc.mapping_component_id
AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND mc.field_name = p_field_name
AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
AND bbit.bld_blk_info_type = p_attribute_category;
SELECT dp.deposit_process_id
INTO l_deposit_process_id
FROM hxc_deposit_processes dp
WHERE dp.NAME = 'OTL Deposit Process';
SELECT bld_blk_info_type_id
INTO l_bbit
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = 'ALTERNATE NAME IDENTIFIERS';
l_attributes.DELETE;
g_an_id.DELETE;
IS SELECT attribute2
FROM hxc_alias_values
WHERE alias_definition_id = g_alias_id
AND attribute1 = p_element_id
AND attribute2 IS NOT NULL
ORDER BY attribute2 ASC;
IS SELECT tim_id,
id
FROM hxt_sum_hours_worked_f sum,
fnd_sessions fnd
WHERE time_building_block_id = p_bb_id
AND fnd.effective_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
ORDER BY time_building_block_ovn DESC;
l_dt_update_mode VARCHAR2(50);
hr_utility.trace('It is a deleted detail');
hr_utility.trace('There are deleted details, need to process ');
SELECT 1
INTO l_retrieved
FROM hxc_transaction_details td,
hxc_transactions t
WHERE td.time_building_block_id = g_detail_tab(i).detail_bb_id
AND td.time_building_block_ovn <= g_detail_tab(i).detail_bb_ovn
AND td.status = 'SUCCESS'
AND t.transaction_id = td.transaction_id
AND t.type = 'RETRIEVAL'
AND t.transaction_process_id = -1
AND ROWNUM < 2;
o_dt_update_mod => l_dt_update_mode,
o_error_message => l_otm_error,
o_return_code => o_return_code,
p_parent_id => l_sum_id
);
hr_utility.trace('update mode '||l_dt_update_mode);
IF l_dt_update_mode = 'UPDATE'
THEN
hxc_time_entry_rules_utils_pkg.add_error_to_table
(p_message_table => p_messages,
p_message_name => 'HXT_TC_CANNOT_BE_DELETED',
p_message_token => NULL,
p_message_level => 'ERROR',
p_message_field => NULL,
p_application_short_name => 'HXT',
p_timecard_bb_id => g_detail_tab(i).detail_bb_id,
p_time_attribute_id => NULL,
p_timecard_bb_ovn => g_detail_tab(i).detail_bb_ovn,
p_time_attribute_ovn => NULL
);
g_detail_tab.DELETE;
ELSIF l_dt_update_mode IS NULL
THEN
hxc_time_entry_rules_utils_pkg.add_error_to_table
(p_message_table => p_messages,
p_message_name => 'HXT_TC_CANNOT_BE_CHANGED_TODAY',
p_message_token => NULL,
p_message_level => 'ERROR',
p_message_field => NULL,
p_application_short_name => 'HXT',
p_timecard_bb_id => g_detail_tab(i).detail_bb_id,
p_time_attribute_id => NULL,
p_timecard_bb_ovn => g_detail_tab(i).detail_bb_ovn,
p_time_attribute_ovn => NULL
);
g_detail_tab.DELETE;
g_detail_tab.DELETE;