[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE hxc_ret_pay_latest_details
SET batch_id = l_ret_tab(i)
WHERE time_building_block_id = l_bb_tab(i)
AND object_version_number = l_ovn_tab(i)
AND request_id = FND_GLOBAL.CONC_request_id;
UPDATE hxc_ret_pay_details
SET batch_id = l_ret_tab(i)
WHERE time_building_block_id = l_bb_tab(i)
AND object_version_number = l_ovn_tab(i)
AND request_id = FND_GLOBAL.CONC_request_id;
UPDATE hxc_ret_pay_latest_details
SET batch_id = l_ret_tab(i),
request_id = FND_GLOBAL.conc_request_id
WHERE time_building_block_id = l_bb_tab(i)
AND object_version_number = l_ovn_tab(i)
AND request_id <> FND_GLOBAL.CONC_request_id
AND batch_id = l_old_ret_tab(i)
AND pbl_id IS NULL;
UPDATE hxc_ret_pay_details
SET batch_id = l_ret_tab(i),
request_id = FND_GLOBAL.conc_request_id
WHERE time_building_block_id = l_bb_tab(i)
AND object_version_number = l_ovn_tab(i)
AND request_id <> FND_GLOBAL.CONC_request_id
AND batch_id = l_old_ret_tab(i)
AND pbl_id IS NULL;
g_timid_tab.DELETE;
g_ovn_tab.DELETE;
g_retro_tab.DELETE;
IS SELECT retro_batch_id
FROM hxt_det_hours_worked
WHERE tim_id = p_tim_id
AND pay_status = 'R';
hr_utility.trace('Selected :'||g_old_retro_tab(TO_CHAR(p_tim_id)));
PROCEDURE update_held_retro_batches
IS
l_timecards NUMTAB;
UPDATE hxc_ret_pay_latest_details
SET measure = old_measure,
attribute1 = old_attribute1,
attribute2 = old_attribute2,
attribute3 = old_attribute3,
request_id = old_request_id,
batch_id = old_batch_id,
pbl_id = old_pbl_id,
old_measure = NULL,
old_attribute1 = NULL,
old_attribute2 = NULL,
old_attribute3 = NULL,
old_request_id = NULL,
old_batch_id = NULL,
old_pbl_id = NULL
WHERE time_building_block_id = g_rdb_bb_tab(i)
AND batch_id = g_rdb_retro_tab(i)
AND pbl_id IS NULL
AND old_measure IS NOT NULL
AND old_attribute1 IS NOT NULL
AND old_attribute2 IS NOT NULL
AND old_attribute3 IS NOT NULL;
DELETE FROM hxc_ret_pay_latest_details
WHERE time_building_block_id = g_rdb_bb_tab(i)
AND batch_id = g_rdb_retro_tab(i)
AND pbl_id IS NULL
AND old_measure IS NULL
AND old_ovn IS NULL;
END update_held_retro_batches;
l_dt_update_mode VARCHAR2 (256);
IS SELECT start_time,
stop_time
FROM hxc_time_building_blocks
WHERE time_building_block_id = p_id
AND object_version_number = p_ovn;
SELECT tim_id
FROM hxt_sum_hours_worked_f
WHERE ID = p_sum_id;
SELECT HTF.ID
FROM hxt_timecards_f HTF, per_time_periods ptp
WHERE HTF.for_person_id = p_person_id
AND HTF.time_period_id = ptp.time_period_id
AND TRUNC (p_date_worked) BETWEEN TRUNC (ptp.start_date)
AND TRUNC (ptp.end_date);
o_dt_update_mod => l_dt_update_mode,
o_error_message => l_error_message,
o_return_code => l_return_code,
p_do_intg_check => l_do_intg_check
);
hr_utility.set_location ('l_dt_update_mode = ' || l_dt_update_mode, 11);
IF (l_dt_update_mode IS NULL)
THEN
IF g_debug
THEN
hr_utility.set_location
( ' This line is not retrievable (p_sum_id = '
|| p_sum_id
|| ')',
20
);
SELECT employee_number, full_name
INTO l_employee_number, l_full_name
FROM per_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT paf.payroll_id, paf.business_group_id, paf.assignment_id
INTO p_payroll_id, p_bg_id, p_assignment_id
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y';
SELECT time_period_id, start_date, end_date
INTO l_time_period_id, l_start_date, l_end_date
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND TRUNC (p_date_worked) BETWEEN TRUNC (start_date)
AND TRUNC (end_date);
SELECT hshw.ID, hshw.effective_start_date, hshw.effective_end_date,
hshw.tim_id
INTO p_time_summary_id, p_time_sum_start_date, p_time_sum_end_date,
p_tim_id
FROM hxt_timecards_f HTF, hxt_sum_hours_worked hshw
WHERE HTF.for_person_id = p_person_id
AND HTF.payroll_id = p_payroll_id
AND HTF.time_period_id = l_time_period_id
AND HTF.effective_end_date = hr_general.end_of_time
AND HTF.ID = hshw.tim_id
AND hshw.time_building_block_id = p_bb_id
-- AND hshw.time_building_block_ovn = p_old_ovn
AND TRUNC (hshw.date_worked) = TRUNC (p_date_worked);
fnd_message.set_name ('HXC', 'HXC_HXT_CANNOT_UPDATE');
SELECT petl.element_name
INTO l_element_name
FROM pay_element_types_f pet, pay_element_types_f_tl petl
WHERE pet.element_type_id = p_ele_type_id
AND petl.element_type_id = pet.element_type_id
AND USERENV ('LANG') = petl.LANGUAGE
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT egr.element_type_id
FROM hxt_earning_rules egr, hxt_add_elem_info_f aei
WHERE egr.egp_id = p_earning_policy_id
AND aei.element_type_id = egr.element_type_id
AND aei.earning_category = 'REG'
AND egr.egr_type <> 'HOL';
SELECT proj.project_number
FROM hxt_all_projects_v proj
WHERE proj.project_id = p_project_id;
SELECT task.task_number
FROM hxt_all_tasks_v task
WHERE task.task_id = p_task_id;
SELECT end_user_column_name
FROM fnd_descr_flex_column_usages c, hxc_mapping_components mpc
WHERE c.application_id = 809
AND c.descriptive_flexfield_name = 'OTC Information Types'
AND c.descriptive_flex_context_code =
'ELEMENT - ' || TO_CHAR (p_ele_type_id)
AND c.application_column_name = mpc.SEGMENT
AND UPPER (mpc.field_name) = p_ipv_segment;
SELECT egr.element_type_id
FROM hxt_earning_rules egr, hxt_add_elem_info_f aei
WHERE egr.egp_id = p_earning_policy_id
AND aei.element_type_id = egr.element_type_id
AND aei.earning_category = 'REG'
AND egr.egr_type <> 'HOL';
SELECT proj.project_number
FROM hxt_all_projects_v proj
WHERE proj.project_id = p_project_id;
SELECT task.task_number
FROM hxt_all_tasks_v task
WHERE task.task_id = p_task_id;
SELECT batch_status_cd
FROM hxt_timecards_fmv
WHERE ID = p_tim_id;
SELECT 'X'
FROM hxc_debug
WHERE UPPER (process) = 'HXT_OTC_RETRIEVAL_INTERFACE'
AND TRUNC (debug_date) <= SYSDATE;
SELECT HTF.ID
FROM hxt_timecards_f HTF, pay_batch_headers pbh
WHERE pbh.batch_reference LIKE p_batch_ref || '%'
AND HTF.batch_id = pbh.batch_id
ORDER BY for_person_id, time_period_id;
l_deleted VARCHAR2 (1) := 'N';
l_dt_update_mode VARCHAR2 (255);
SELECT pet.element_type_id
INTO l_ele_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = p_element_name
AND ( pet.business_group_id + 0 = p_bg_id
OR pet.business_group_id IS NULL
)
--and pet.legislation_code = 'US')
--or (pet.business_group_id is null
--and pet.legislation_code is null))
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
p_delete IN VARCHAR2,
p_state_name IN VARCHAR2 DEFAULT NULL,
p_county_name IN VARCHAR2 DEFAULT NULL,
p_city_name IN VARCHAR2 DEFAULT NULL,
p_zip_code IN VARCHAR2 DEFAULT NULL
)
IS
--
CURSOR get_timecard_id (p_tim_sum_id NUMBER)
IS
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;
last_updated_by => '-1',
last_update_login => '-1',
-- ,writesum_yn =>
explode_yn => 'N',
delete_yn => p_delete,
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 => p_time_building_block_id,
p_time_building_block_ovn => p_time_building_block_ovn,
p_validate => FALSE,
p_state_name => p_state_name,
p_county_name => p_county_name,
p_city_name => p_city_name,
p_zip_code => p_zip_code
);
hr_utility.trace('There exists some input values, need to update them');
UPDATE hxt_sum_hours_worked_f
SET attribute1 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute1,
attribute2 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute2,
attribute3 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute3,
attribute4 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute4,
attribute5 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute5,
attribute6 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute6,
attribute7 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute7,
attribute8 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute8,
attribute9 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute9,
attribute10 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute10,
attribute11 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute11,
attribute12 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute12,
attribute13 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute13,
attribute14 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute14,
attribute15 = g_iv_table(TO_CHAR(p_time_building_block_id)).attribute15
WHERE id = l_created_tim_sum_id
AND time_building_block_id = p_time_building_block_id
AND time_building_block_ovn = p_time_building_block_ovn ; -- Bug 9159142
SELECT paf.payroll_id, paf.business_group_id, paf.assignment_id
INTO p_payroll_id, p_bg_id, p_assignment_id
FROM per_all_assignments_f paf
WHERE paf.person_id = p_person_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_type = 'E'
AND paf.primary_flag = 'Y';
SELECT MAX (pbh.batch_name)
INTO l_batch_name
FROM pay_batch_headers pbh
WHERE pbh.batch_reference LIKE p_batch_ref || '%'
AND pbh.batch_reference NOT LIKE '%RETRO%'
AND pbh.batch_status NOT IN ('T', 'TW');
SELECT MAX (pbh.batch_name)
INTO l_batch_name
FROM pay_batch_headers pbh
WHERE pbh.batch_reference LIKE p_batch_ref || '%'
AND pbh.batch_status NOT IN ('T', 'TW');
SELECT COUNT (pbl.batch_line_id)
INTO l_batch_lines
FROM pay_batch_lines pbl, pay_batch_headers pbh
WHERE pbh.batch_name = l_batch_name
AND pbl.batch_id = pbh.batch_id;
g_timecards.DELETE;
g_timecards.DELETE;
l_deleted :=
hxc_generic_retrieval_pkg.t_detail_bld_blks (l_cnt).deleted;
g_iv_table.DELETE(TO_CHAR(l_bb_id));
hr_utility.TRACE ('l_deleted is ' || l_deleted);
SELECT full_name
INTO g_full_name
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_context.DELETE;
l_category.DELETE;
l_old_field_name.DELETE;
l_old_value.DELETE;
l_old_context.DELETE;
l_old_category.DELETE;
IF l_deleted = 'Y' AND l_changed = 'Y'
THEN
-- g_timecards decides if the timecard needs to
-- be re-exploded. In case the timecard is not
-- added already to g_timecards, check if it needs
-- an explosion. If yes, add it to g_timecards.
IF NOT( g_timecards.EXISTS(l_tim_id))
-- Bug 9308216
-- Added to avoid ORA 6502
AND l_tim_id IS NOT NULL
THEN
IF(chk_need_re_explosion( l_assignment_id,
l_date_worked,
l_element_id ))
THEN
IF g_debug
THEN
hr_utility.trace('This timecard needs re-explosion');
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;
IF l_deleted = 'N'
THEN
--
IF g_debug
THEN
hr_utility.TRACE
('---- Creating new timecard ----');
p_delete => 'N',
p_state_name => l_state_name,
p_county_name => l_county_name,
p_city_name => l_city_name,
p_zip_code => l_zip_code
);
update_held_retro_batches;
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => l_process_name,
p_status => g_status,
p_exception_description => g_exception_description,
p_rollback => FALSE
);
o_dt_update_mod => l_dt_update_mode,
o_error_message => l_otm_error,
o_return_code => l_return_code
);
hr_utility.TRACE ('l_dt_update_mode = ' || l_dt_update_mode);
dt_update_mode => l_dt_update_mode,
-- 'CORRECTION',
otm_error => l_otm_error,
oracle_error => l_oracle_error
);
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => l_process_name,
p_status => g_status,
p_exception_description => g_exception_description,
p_rollback => FALSE
);
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => l_process_name,
p_status => g_status,
p_exception_description => g_exception_description,
p_rollback => FALSE
);
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => l_process_name,
p_status => 'ERRORS',
p_exception_description => g_exception_description,
p_rollback => FALSE
);
hxc_generic_retrieval_pkg.update_transaction_status
(p_process => l_process_name,
p_status => 'ERRORS',
p_exception_description => g_exception_description,
p_rollback => FALSE
);
IS SELECT /*+ INDEX(asg HXT_ADD_ASSIGN_INFO_ON1)*/
earning_policy,
effective_start_date,
effective_end_date
FROM hxt_add_assign_info_f asg
WHERE assignment_id = p_asg_id
ORDER BY effective_start_date ;
IS SELECT /*+ LEADING(ep)
INDEX(ep HXT_EARNING_POLICIES_PK)
INDEX(eg HXT_EARN_GROUPS_EGT_FK) */
element_type_id
FROM hxt_earning_policies ep,
hxt_earn_groups eg
WHERE ep.id = p_ep_id
AND eg.egt_id = ep.egt_id
ORDER BY element_type_id ;
hr_utility.trace('Deleted entry, check if re-explosion needed ');