[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IS SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date ;
IS SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_id
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date;
IS SELECT batch_name,
batch_status
FROM pay_batch_headers
WHERE batch_id = p_batch_id;
IS SELECT batch_name,
batch_status
FROM pay_batch_headers
WHERE batch_id = p_batch_id;
IS SELECT status
FROM hxc_data_sets
WHERE data_set_id = p_data_set_id;
IS SELECT sum.id,
tim.batch_id
FROM hxc_ret_pay_latest_details ret,
hxt_sum_hours_worked_f sum,
hxt_timecards_f tim
WHERE ret.timecard_id = p_timecard_id
AND ret.time_building_block_id = sum.time_building_block_id
AND sum.tim_id = tim.id
AND ROWNUM < 2;
IS SELECT data_set_id
FROM hxc_timecard_summary
WHERE timecard_id = p_timecard_id;
IS SELECT timecard_id
FROM hxc_rollback_timecards_all
WHERE timecard_id = p_timecard_id
AND timecard_type = 'HXC_TIMECARD_SUMMARY';
IS SELECT timecard_id
FROM hxc_ld_rollback_timecards_all
WHERE timecard_id = p_timecard_id;
DELETE FROM hxc_ld_rollback_timecards_all
WHERE timecard_id = p_timecard_id;
PROCEDURE update_transferred_to(p_timecard_id IN NUMBER)
IS
CURSOR get_transferred_to
IS SELECT transferred_to
FROM hxc_timecard_summary
WHERE timecard_id = p_timecard_id;
UPDATE hxc_timecard_summary
SET transferred_to = l_transferred_to
WHERE timecard_id = p_timecard_id;
END update_transferred_to;
PROCEDURE delete_old_log
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM hxc_rollback_log
WHERE type = 'HXCROLL';
END delete_old_log;
IS SELECT text
FROM hxc_rollback_log
WHERE type = 'HXCROLL'
ORDER BY seq;
DELETE FROM hxc_rollback_log;
IS SELECT retrieval_process_id
FROM hxc_retrieval_processes
WHERE name = 'BEE Retrieval Process';
g_reverse_batches.DELETE;
g_hours_iv_position.DELETE;
g_reversal_batches.DELETE;
g_rev_batch_seq.DELETE;
g_tainted_timecards.DELETE;
delete_old_log;
IS SELECT display_sequence
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = 'Hours';
IS SELECT time_building_block_id,
tc.object_version_number,
start_time,
TRUNC(stop_time),
full_name,
NVL(employee_number,npw_number)
FROM hxc_time_building_blocks tc,
per_all_people_f ppf
WHERE tc.time_building_block_id = p_timecard_id
AND tc.resource_id = ppf.person_id
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
ORDER BY tc.object_version_number DESC;
IS SELECT time_building_block_id,
tc.object_version_number,
start_time,
TRUNC(stop_time),
full_name,
NVL(employee_number,npw_number)
FROM hxc_time_building_blocks tc,
per_all_people_f ppf
WHERE tc.time_building_block_id = p_timecard_id
AND tc.resource_id = ppf.person_id
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
IS SELECT batch_status
FROM pay_batch_headers
WHERE batch_id = p_batch_id;
IS SELECT batch_line_id,
object_version_number
FROM pay_batch_lines pbl,
TABLE(return_ids(l_lines)) list
WHERE list.column_value = pbl.batch_line_id;
'SELECT *
FROM pay_batch_lines
WHERE batch_line_id IN ';
'SELECT *
FROM pay_batch_lines
WHERE batch_line_id IN ';
pay_batch_element_entry_api.delete_batch_line
( p_batch_line_id => line_tab(i).batch_line_id,
p_object_version_number => line_tab(i).object_version_number);
IS SELECT NVL(htb.measure,(htb.stop_time-htb.start_time)*24),
htb.time_building_block_id,
htb.application_set_id
FROM hxc_pay_latest_details hld,
hxc_time_building_blocks htb
WHERE hld.timecard_id = p_timecard_id
AND htb.time_building_block_id = hld.time_building_block_id
AND htb.object_version_number = hld.object_version_number;
IS SELECT REPLACE(hta.attribute_category,'ELEMENT - '),
hld.time_building_block_id
FROM hxc_pay_latest_details hld,
hxc_time_attribute_usages hau,
hxc_time_attributes hta
WHERE hld.timecard_id = p_timecard_id
AND hau.time_building_block_id = hld.time_building_block_id
AND hau.time_building_block_ovn = hld.object_version_number
AND hta.time_attribute_id = hau.time_attribute_id
AND hta.attribute_category LIKE 'ELEMENT%';
UPDATE hxc_pay_latest_details
SET measure = l_measure(i),
application_set_id = l_application(i)
WHERE time_building_block_id = l_dettab(i);
UPDATE hxc_pay_latest_details
SET attribute1 = l_attribute(i)
WHERE time_building_block_id = l_dettab(i);
'SELECT /*+ LEADING(tc)
INDEX(det HXC_RET_PAY_LATEST_DETAILS_N3)
INDEX(tc HXC_TIMECARD_SUMMARY_FK1) */
DISTINCT det.timecard_id,batch_id,retro_batch_id
FROM hxc_ret_pay_latest_details det,
hxc_timecard_summary tc
WHERE tc.timecard_id = det.timecard_id
';
IS SELECT latest.old_batch_id,
latest.request_id,
latest.old_request_id,
latest.resource_id,
latest.time_building_block_id,
latest.approval_status,
latest.start_time,
latest.stop_time,
latest.org_id,
latest.business_group_id,
latest.timecard_id,
latest.attribute1,
latest.attribute2,
latest.attribute3,
latest.measure,
latest.object_version_number,
latest.old_ovn,
latest.old_measure,
latest.old_attribute1,
latest.old_attribute2,
latest.old_attribute3,
latest.pbl_id,
latest.retro_pbl_id,
latest.old_pbl_id,
latest.batch_id,
latest.retro_batch_id,
latest.application_set_id,
latest.comment_text,
latest.last_update_date,
latest.resource_type,
htd.transaction_detail_id,
ht.transaction_id
FROM hxc_ret_pay_latest_details latest,
hxc_ret_pay_details det,
hxc_transaction_details htd,
hxc_transactions ht
WHERE det.timecard_id = p_timecard_id
AND det.batch_id = p_batch_id
AND det.retro_batch_id = p_retro_batch_id
AND det.time_building_block_id = latest.time_building_block_id
AND det.object_version_number = latest.object_version_number
AND latest.object_version_number = htd.time_building_block_ovn
AND latest.time_building_block_id = htd.time_building_block_id
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.transaction_process_id = g_bee_process_id;
IS SELECT latest.old_batch_id,
latest.request_id,
latest.old_request_id,
latest.resource_id,
latest.time_building_block_id,
latest.approval_status,
latest.start_time,
latest.stop_time,
latest.org_id,
latest.business_group_id,
latest.timecard_id,
latest.attribute1,
latest.attribute2,
latest.attribute3,
latest.measure,
latest.object_version_number,
latest.old_ovn,
latest.old_measure,
latest.old_attribute1,
latest.old_attribute2,
latest.old_attribute3,
latest.pbl_id,
latest.retro_pbl_id,
latest.old_pbl_id,
latest.batch_id,
latest.retro_batch_id,
latest.application_set_id,
latest.comment_text,
latest.last_update_date,
latest.resource_type,
htd.transaction_detail_id,
ht.transaction_id
FROM hxc_ret_pay_latest_details latest,
hxc_ret_pay_details det,
hxc_transaction_details htd,
hxc_transactions ht
WHERE det.timecard_id = p_timecard_id
AND det.batch_id = p_batch_id
AND det.retro_batch_id IS NULL
AND det.time_building_block_id = latest.time_building_block_id
AND det.object_version_number = latest.object_version_number
AND latest.object_version_number = htd.time_building_block_ovn
AND latest.time_building_block_id = htd.time_building_block_id
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.transaction_process_id = g_bee_process_id;
IS SELECT latest.old_batch_id,
latest.request_id,
latest.old_request_id,
latest.resource_id,
latest.time_building_block_id,
latest.approval_status,
latest.start_time,
latest.stop_time,
latest.org_id,
latest.business_group_id,
latest.timecard_id,
latest.attribute1,
latest.attribute2,
latest.attribute3,
latest.measure,
latest.object_version_number,
latest.old_ovn,
latest.old_measure,
latest.old_attribute1,
latest.old_attribute2,
latest.old_attribute3,
latest.pbl_id,
latest.retro_pbl_id,
latest.old_pbl_id,
latest.batch_id,
latest.retro_batch_id,
latest.application_set_id,
latest.comment_text,
latest.last_update_date,
latest.resource_type,
htd.transaction_detail_id,
ht.transaction_id
FROM hxc_ret_pay_latest_details latest,
hxc_ret_pay_details det,
hxc_transaction_details htd,
hxc_transactions ht
WHERE det.timecard_id = p_timecard_id
AND det.batch_id IS NULL
AND det.retro_batch_id = p_retro_batch_id
AND det.time_building_block_id = latest.time_building_block_id
AND det.object_version_number = latest.object_version_number
AND latest.object_version_number = htd.time_building_block_ovn
AND latest.time_building_block_id = htd.time_building_block_id
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.transaction_process_id = g_bee_process_id;
l_lines_last_update_date DATETAB ;
l_asg_criteria := FND_GLOBAL.newline||' AND tc.resource_id IN ( SELECT person_id
FROM per_all_assignments_f paf
WHERE business_group_id = '||p_bg_id
||l_location_filter
||l_org_filter
||l_payroll_filter
||')';
l_asg_criteria := FND_GLOBAL.newline||' AND tc.resource_id IN ( SELECT person_id
FROM per_all_assignments_f paf,
hr_soft_coding_keyflex hsk
WHERE business_group_id = '||p_bg_id
||l_location_filter
||l_org_filter
||l_payroll_filter
||FND_GLOBAL.newline||' AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
AND hsk.segment1 = '''||p_gre_id||''''
||')';
l_lines_last_update_date ,
l_lines_resource_type ,
l_lines_td_id ,
l_lines_trans_id ;
UPDATE hxc_transaction_details
SET status = 'ROLLEDBACK',
exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
WHERE transaction_detail_id = l_lines_trans_id(i);
DELETE FROM hxc_ret_pay_details
WHERE time_building_block_id = l_lines_time_building_block_id(i)
AND object_version_number = l_lines_object_version_number(i)
AND batch_id = l_lines_batch_id(i)
AND retro_batch_id = l_lines_retro_batch_id(i);
INSERT INTO hxc_pay_latest_details
(
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure)
VALUES (
l_lines_resource_id(i),
l_lines_time_building_block_id(i),
l_lines_object_version_number(i),
l_lines_approval_status(i),
l_lines_start_time(i),
l_lines_stop_time(i),
l_lines_application_set_id(i),
NVL(l_lines_last_update_date(i),SYSDATE),
l_lines_comment_text(i),
'PERSON',
l_lines_org_id(i),
l_lines_business_group_id(i),
l_lines_timecard_id(i),
l_lines_attribute1(i),
l_lines_attribute2(i),
l_lines_attribute3(i),
l_lines_measure(i));
UPDATE hxc_ret_pay_latest_details
SET attribute1 = l_lines_old_attribute1(i),
attribute2 = l_lines_old_attribute2(i),
attribute3 = l_lines_old_attribute3(i),
measure = l_lines_old_measure(i),
request_id = old_request_id,
batch_id = old_batch_id,
pbl_id = old_pbl_id,
retro_batch_id = NULL,
retro_pbl_id = NULL,
comment_text = NULL,
old_request_id = NULL,
old_batch_id = NULL,
old_pbl_id = NULL,
old_attribute1 = NULL,
old_attribute2 = NULL,
old_attribute3 = NULL,
old_measure = NULL,
old_ovn = NULL
WHERE time_building_block_id = l_lines_time_building_block_id(i)
AND object_version_number = l_lines_object_version_number(i);
l_lines_last_update_date ,
l_lines_resource_type ,
l_lines_td_id ,
l_lines_trans_id ;
UPDATE hxc_transaction_details
SET status = 'ROLLEDBACK',
exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
WHERE transaction_detail_id = l_lines_td_id(i);
DELETE FROM hxc_ret_pay_details
WHERE time_building_block_id = l_lines_time_building_block_id(i)
AND object_version_number = l_lines_object_version_number(i)
AND batch_id = l_lines_batch_id(i)
AND retro_batch_id IS NULL;
INSERT INTO hxc_pay_latest_details
(
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure)
VALUES (
l_lines_resource_id(i),
l_lines_time_building_block_id(i),
l_lines_object_version_number(i),
l_lines_approval_status(i),
l_lines_start_time(i),
l_lines_stop_time(i),
l_lines_application_set_id(i),
NVL(l_lines_last_update_date(i),SYSDATE),
l_lines_comment_text(i),
'PERSON',
l_lines_org_id(i),
l_lines_business_group_id(i),
l_lines_timecard_id(i),
l_lines_attribute1(i),
l_lines_attribute2(i),
l_lines_attribute3(i),
l_lines_measure(i));
DELETE FROM hxc_ret_pay_latest_details
WHERE time_building_block_id = l_lines_time_building_block_id(i)
AND object_version_number = l_lines_object_version_number(i);
l_lines_last_update_date ,
l_lines_resource_type ,
l_lines_td_id ,
l_lines_trans_id ;
UPDATE hxc_transaction_details
SET status = 'ROLLEDBACK',
exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
WHERE transaction_detail_id = l_lines_td_id(i);
DELETE FROM hxc_ret_pay_details
WHERE time_building_block_id = l_lines_time_building_block_id(i)
AND object_version_number = l_lines_object_version_number(i)
AND retro_batch_id = l_lines_retro_batch_id(i)
AND batch_id IS NULL;
INSERT INTO hxc_pay_latest_details
(
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure)
VALUES (
l_lines_resource_id(i),
l_lines_time_building_block_id(i),
l_lines_object_version_number(i),
l_lines_approval_status(i),
l_lines_start_time(i),
l_lines_stop_time(i),
l_lines_application_set_id(i),
NVL(l_lines_last_update_date(i),SYSDATE),
l_lines_comment_text(i),
'PERSON',
l_lines_org_id(i),
l_lines_business_group_id(i),
l_lines_timecard_id(i),
l_lines_attribute1(i),
l_lines_attribute2(i),
l_lines_attribute3(i),
l_lines_measure(i));
UPDATE hxc_ret_pay_latest_details
SET attribute1 = l_lines_old_attribute1(i),
attribute2 = l_lines_old_attribute2(i),
attribute3 = l_lines_old_attribute3(i),
measure = l_lines_old_measure(i),
request_id = old_request_id,
batch_id = old_batch_id,
pbl_id = old_pbl_id,
retro_batch_id = NULL,
retro_pbl_id = NULL,
comment_text = NULL,
old_request_id = NULL,
old_batch_id = NULL,
old_pbl_id = NULL,
old_attribute1 = NULL,
old_attribute2 = NULL,
old_attribute3 = NULL,
old_measure = NULL,
old_ovn = NULL
WHERE time_building_block_id = l_lines_time_building_block_id(i)
AND object_version_number = l_lines_object_version_number(i);
g_reverse_batches.DELETE;
update_transferred_to(l_tc_tab(x));
'SELECT timecard_id
FROM hxc_timecard_summary sum
WHERE 1 = 1';
IS SELECT det.time_building_block_id,
det.object_version_number,
det.start_time,
det.batch_id,
det.pbl_id,
det.retro_batch_id,
det.retro_pbl_id,
det.timecard_id,
htd.transaction_detail_id
FROM hxc_ret_pay_details det,
hxc_transaction_details htd,
hxc_transactions ht
WHERE det.timecard_id = p_timecard_id
AND det.time_building_block_id = htd.time_building_block_id
AND det.object_version_number = htd.time_building_block_ovn
AND htd.status = 'SUCCESS'
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.status = 'SUCCESS';
IS SELECT latest.old_batch_id,
latest.request_id,
latest.old_request_id,
latest.resource_id,
latest.time_building_block_id,
latest.approval_status,
latest.start_time,
latest.stop_time,
latest.org_id,
latest.business_group_id,
latest.timecard_id,
latest.attribute1,
latest.attribute2,
latest.attribute3,
latest.measure,
latest.object_version_number,
latest.old_ovn,
latest.old_measure,
latest.old_attribute1,
latest.old_attribute2,
latest.old_attribute3,
latest.pbl_id,
latest.retro_pbl_id,
latest.old_pbl_id,
latest.batch_id,
latest.retro_batch_id,
latest.application_set_id,
latest.comment_text,
latest.last_update_date,
latest.resource_type
FROM hxc_ret_pay_latest_details latest
WHERE timecard_id = p_timecard_id;
l_lines_last_update_date DATETAB ;
l_asg_criteria := FND_GLOBAL.newline||' AND resource_id IN ( SELECT person_id
FROM per_all_assignments_f paf
WHERE business_group_id = '||p_bg_id
||l_location_filter
||l_org_filter
||l_payroll_filter
||')';
l_asg_criteria := FND_GLOBAL.newline||' AND resource_id IN ( SELECT person_id
FROM per_all_assignments_f paf,
hr_soft_coding_keyflex hsk
WHERE business_group_id = '||p_bg_id
||l_location_filter
||l_org_filter
||l_payroll_filter
||FND_GLOBAL.newline||' AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
AND hsk.segment1 = '''||p_gre_id||''''
||')';
l_lines_last_update_date ,
l_lines_resource_type;
INSERT INTO hxc_pay_latest_details
(
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure)
VALUES (
l_lines_resource_id(i),
l_lines_time_building_block_id(i),
l_lines_object_version_number(i),
l_lines_approval_status(i),
l_lines_start_time(i),
l_lines_stop_time(i),
l_lines_application_set_id(i),
NVL(l_lines_last_update_date(i),SYSDATE),
l_lines_comment_text(i),
'PERSON',
l_lines_org_id(i),
l_lines_business_group_id(i),
l_lines_timecard_id(i),
l_lines_attribute1(i),
l_lines_attribute2(i),
l_lines_attribute3(i),
l_lines_measure(i));
DELETE FROM hxc_ret_pay_latest_details
WHERE time_building_block_id = l_lines_time_building_block_id(i);
UPDATE hxc_transaction_details
SET status = 'ROLLEDBACK',
exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
WHERE transaction_detail_id = l_tc_data_td_id(i);
DELETE FROM hxc_ret_pay_details
WHERE time_building_block_id = l_tc_data_tbb_id(i)
AND object_version_number = l_tc_data_ovn(i);
g_reverse_batches.DELETE;
update_transferred_to(l_tc_tab(x));
IS SELECT batch_id
FROM hxc_pay_trans_code_all
WHERE transaction_code = p_trans_code
AND batch_id IS NOT NULL;
IS SELECT batch_id
FROM hxc_pay_trans_code_all
WHERE request_id = p_request_id
AND batch_id IS NOT NULL;
IS SELECT batch_id
FROM pay_batch_headers
WHERE batch_reference = p_batch_ref;
INSERT INTO hxc_rollback_log
(seq,text,type)
VALUES
(g_seq,p_text,'HXCROLL');