[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM FND_SESSIONS
WHERE session_id = USERENV('SESSIONID');
INSERT INTO FND_SESSIONS
( session_id,effective_date)
VALUES ( USERENV('SESSIONID'),TRUNC(SYSDATE));
IS SELECT payroll_name
FROM pay_all_payrolls_f
WHERE payroll_id = p_payroll_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 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 'Period Name: '||period_name
||' Start: '||TO_CHAR(start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
||' End: '||TO_CHAR(end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
from per_time_periods
where time_period_id = p_time_period_id;
IS SELECT text
FROM hxc_rollback_log
WHERE type = g_program
ORDER BY seq;
DELETE FROM hxc_rollback_log
WHERE type = g_program;
PROCEDURE delete_old_log
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE FROM hxc_rollback_log
WHERE type = g_program;
END delete_old_log;
IS SELECT payroll_id,
time_period_id,
batch_id,
for_person_id
FROM hxt_timecards
WHERE id = p_tim_id;
IS SELECT timecard_id
FROM hxc_rollback_timecards_all
WHERE timecard_id = p_timecard_id
AND timecard_type = 'HXT_TIMECARDS_F';
IS SELECT data_set_id
FROM hxt_timecards_f
WHERE id = p_timecard_id;
IS SELECT DISTINCT retro_batch_id
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND retro_batch_id IS NOT NULL
AND rollback_status IS NULL;
PROCEDURE update_transferred_to(p_timecards IN NUMTAB)
IS
CURSOR get_transferred_to(p_timecard_id IN NUMBER)
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_timecards(i);
END update_transferred_to;
IS SELECT payroll_id,
time_period_id,
batch_id,
for_person_id
FROM hxt_timecards
WHERE id = p_tim_id;
' select id,
parent_id,
date_worked,
decode(effective_end_date,hr_general.end_of_time,''A'',''I'') status,
hours,
element_type_id,
pay_status,
pbl_line_id,
retro_pbl_line_id,
retro_batch_id,
retro_batch_id_2,
retro_pbl_line_id_2
from hxt_det_hours_worked_f
WHERE id IN ';
IS SELECT tim_id
FROM hxt_det_hours_worked_f
WHERE retro_batch_id = p_batch_id;
IS SELECT id
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND retro_batch_id_2 = p_batch_id
AND pay_status IN ('A','D');
IS SELECT id
FROM hxt_det_hours_worked_f det,
pay_batch_lines pbl
WHERE tim_id = p_tim_id
AND pbl.batch_id = p_batch_id
AND pbl.batch_line_id = retro_pbl_line_id
AND pay_status = 'B';
IS SELECT id
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND retro_batch_id = p_batch_id
AND pay_status = 'C' ;
' select id,
parent_id,
date_worked,
decode(effective_end_date,hr_general.end_of_time,''A'',''I'') status,
hours,
element_type_id,
pay_status,
pbl_line_id,
retro_pbl_line_id,
retro_batch_id,
retro_batch_id_2,
retro_pbl_line_id_2
from hxt_det_hours_worked_f
WHERE tim_id =:1
ORDER BY effective_end_date,id,date_worked, parent_id ';
IS SELECT tim_id
FROM hxt_det_hours_worked_f
WHERE retro_batch_id = p_batch_id;
IS SELECT id
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND retro_batch_id = p_batch_id
AND pay_status = 'R';
IS SELECT id
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND pay_status = 'C' ;
INSERT INTO hxc_rollback_log
(seq,text,type)
VALUES
(g_seq,p_text,g_program);
IS SELECT display_sequence
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = 'Hours';
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_id = :1
AND 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);
INSERT INTO hxt_reversed_batch_lines_all
(batch_id,
replacement_batch_id,
batch_line_id,
batch_line_ovn,
replacement_batch_line_id,
replacement_batch_line_ovn)
VALUES ( line_tab(i).batch_id,
l_reversal_batch_id,
line_tab(i).batch_line_id,
line_tab(i).object_version_number,
l_batch_line_id,
l_object_version_number);
PROCEDURE update_replacement_batch_id(p_batch_id IN NUMBER,
p_tim_id IN NUMBER)
IS
CURSOR get_row
IS SELECT ROWIDTOCHAR(tim.ROWID)
FROM hxt_timecards_f tim,
FND_sessions sess
WHERE id = p_tim_id
AND session_id = USERENV('SESSIONID')
AND effective_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE hxt_timecards_f
SET batch_id = p_batch_id
WHERE ROWID = CHARTOROWID(l_rowid);
END update_replacement_batch_id;
PROCEDURE update_original_batch_id(p_batch_id NUMBER,
p_replacement_batch_id NUMBER,
p_tim_tab NUMTAB)
IS
CURSOR get_batch_details(p_batch NUMBER)
IS SELECT object_version_number
FROM pay_batch_headers pbh
WHERE batch_id = p_batch;
UPDATE hxt_timecards
SET batch_id = p_batch_id
WHERE id = p_tim_tab(i)
AND batch_id = p_replacement_batch_id;
DELETE FROM hxt_reversed_batch_tc_all
WHERE batch_id = p_batch_id
AND replacement_batch_id = p_replacement_batch_id
AND tim_id = p_tim_tab(i);
put_log('Updated the batches back ');
UPDATE hxt_batch_states
SET status = 'H'
WHERE batch_id = p_batch_id;
pay_batch_element_entry_api.delete_batch_header(p_batch_id => p_replacement_batch_id,
p_object_version_number => l_ovn);
END update_original_batch_id;
PROCEDURE delete_batch_line(p_batch_line_id IN NUMBER,
p_ovn IN NUMBER)
IS
BEGIN
put_log('Deleting line '||p_batch_line_id||', Ovn '||p_ovn);
PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
(p_batch_line_id => p_batch_line_id
,p_object_version_number => p_ovn
);
put_log('Deleted ');
END delete_batch_line;
IS SELECT retro_batch_id,
tim_id
FROM hxt_timecards_f tim,
hxt_det_hours_worked_f det
WHERE tim.batch_id = p_batch_id
AND tim.id = det.tim_id;
IS SELECT id
FROM hxt_timecards
WHERE batch_id = p_batch_id;
IS SELECT id
FROM hxt_timecards
WHERE batch_id = p_batch_id
AND for_person_id = p_person_id;
IS SELECT retro_batch_id,
tim_id
FROM hxt_timecards_f tim,
hxt_det_hours_worked_f det
WHERE tim.batch_id = p_batch_id
AND tim.id = det.tim_id
AND tim.for_person_id = p_person_id;
IS SELECT det.ROWID,
ret.ROWID,
det.pbl_line_id,
pbl.object_version_number,
ret.time_building_block_id,
ret.object_version_number,
ret2.measure,
ret2.attribute1,
ret2.attribute2,
ret2.attribute3,
ret2.request_id,
rank() over ( partition by ret.time_building_block_id,ret.object_version_number
ORDER by ret.ROWID) rank
FROM hxt_sum_hours_worked_f sum,
hxt_det_hours_worked_f det,
hxc_ret_pay_latest_details ret,
hxc_ret_pay_details ret2,
FND_sessions sess,
pay_batch_lines pbl
WHERE sum.tim_id = p_tim_id
AND sess.session_id = USERENV('sessionid')
AND sess.effective_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND det.parent_id = sum.id
AND det.pay_status = 'C'
AND det.pbl_line_id = pbl.batch_line_id
AND sess.effective_date BETWEEN det.effective_start_date
AND det.effective_end_date
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND sum.time_building_block_id = ret2.time_building_block_id
AND sum.time_building_block_ovn = ret2.object_version_number;
IS SELECT det.ROWID,
det.pbl_line_id,
pbl.object_version_number
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
pay_batch_lines pbl,
FND_Sessions sess
WHERE sum.tim_id = p_tim_id
AND sess.session_id = USERENV('sessionid')
AND sess.effective_date BETWEEN sum.effective_start_date
AND sum.effective_end_date
AND det.parent_id = sum.id
AND det.pay_status = 'C'
AND det.pbl_line_id = pbl.batch_line_id
AND sess.effective_date BETWEEN det.effective_start_date
AND det.effective_end_date;
UPDATE hxt_det_hours_worked_f
SET pay_status = 'P',
pbl_line_id = NULL
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
DELETE FROM hxc_ret_pay_latest_details
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
AND l_rank_tab(i) <> 1;
UPDATE hxc_ret_pay_latest_details
SET request_id = l_req_tab(i),
measure = l_hrs_tab(i),
attribute1 = l_att1_tab(i),
attribute2 = l_att2_tab(i),
attribute3 = l_att3_tab(i)
WHERE time_building_block_id = l_bb_tab(i)
AND object_version_number = l_ovn_tab(i)
AND l_rank_tab(i) = 1;
update_replacement_batch_id(l_replacement_batch,
l_tim_tab(i));
INSERT INTO hxt_reversed_batch_tc_all
(batch_id,
replacement_batch_id,
tim_id)
VALUES
(p_batch_id,
l_replacement_batch,
l_tim_tab(i));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'P',
pbl_line_id = NULL
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
update_replacement_batch_id(l_replacement_batch,
l_tim_tab(i));
update_original_batch_id(p_batch_id,
l_replacement_batch,
l_tim_tab);
' SELECT pbh.batch_id,
pbh.batch_status
FROM pay_batch_headers pbh,
hxt_batch_states hbs
WHERE pbh.business_group_id = :bg_id
AND pbh.batch_id = hbs.batch_id
AND hbs.status = ''VT''';
delete_old_log;
' is Transferred. Option selected is to Delete lines from Unprocessed batches ',g_cache);
IS SELECT DISTINCT tim_id
FROM pay_batch_lines pbl,
hxt_det_hours_worked_f det
WHERE pbl.batch_id = p_batch_id
AND ( pbl.batch_line_id = det.retro_pbl_line_id
OR pbl.batch_id = det.retro_batch_id);
IS SELECT DISTINCT
ROWIDTOCHAR(det.ROWID),
ROWIDTOCHAR(ret.ROWID),
pbl.batch_line_id
FROM pay_batch_lines pbl,
hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id
AND det.tim_id = p_tim_id
AND det.pay_status = 'B'
AND det.parent_id = sum.id
AND sum.effective_start_date <= det.effective_start_date
AND sum.effective_end_date >= det.effective_end_date
AND sum.effective_end_date < hr_general.end_of_time
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.old_ovn
AND det.retro_pbl_line_id = ret.retro_pbl_id
AND ret.retro_batch_id = pbl.batch_id;
IS SELECT DISTINCT
ROWIDTOCHAR(det.ROWID),
ROWIDTOCHAR(ret.ROWID),
pbl.batch_line_id
FROM pay_batch_lines pbl,
hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id
AND det.pay_status = 'B'
AND det.tim_id = p_tim_id
AND det.parent_id = sum.id
AND sum.effective_start_date <= det.effective_start_date
AND sum.effective_end_date >= det.effective_end_date
AND sum.effective_end_date = hr_general.end_of_time
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND det.retro_pbl_line_id = ret.retro_pbl_id
AND ret.retro_batch_id = pbl.batch_id;
CURSOR get_updated_records(p_batch_id IN NUMBER,
p_tim_id IN NUMBER)
IS SELECT DISTINCT
ROWIDTOCHAR(ret.ROWID)
FROM hxc_ret_pay_latest_details ret,
hxt_sum_hours_worked_f sum
WHERE hxt_batch_id = p_batch_id
AND retro_batch_id IS NULL
AND retro_pbl_id IS NULL
AND pbl_id IS NOT NULL
AND sum.tim_id = p_tim_id
AND sum.time_building_block_id = ret.time_building_block_id;
IS SELECT DISTINCT
ROWIDTOCHAR(det.ROWID),
ROWIDTOCHAR(ret.ROWID),
pbl.batch_line_id
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret,
pay_batch_lines pbl
WHERE det.pay_status = 'C'
AND det.retro_batch_id = p_batch_id
AND det.retro_pbl_line_id = pbl.batch_line_id
AND det.tim_id = p_tim_id
AND pbl.batch_id = det.retro_batch_id
AND det.parent_id = sum.id
AND det.effective_start_date >= sum.effective_start_date
AND det.effective_end_date <= sum.effective_end_date
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND ret.pbl_id = det.retro_pbl_line_id
AND hxt_request_id IS NULL ;
IS SELECT DISTINCT
ROWIDTOCHAR(det.ROWID),
ROWIDTOCHAR(ret.ROWID),
pbl.batch_line_id,
RANK() OVER ( PARTITION BY ret.time_building_block_id,
ret.object_version_number
ORDER BY det.ROWID) rank
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret,
pay_batch_lines pbl
WHERE det.pay_status = 'C'
AND det.retro_batch_id = p_batch_id
AND det.tim_id = p_tim_id
AND det.retro_pbl_line_id = pbl.batch_line_id
AND pbl.batch_id = det.retro_batch_id
AND det.parent_id = sum.id
AND det.effective_start_date >= sum.effective_start_date
AND det.effective_end_date <= sum.effective_end_date
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND ret.pbl_id = det.retro_pbl_line_id
AND hxt_request_id IS NOT NULL ;
IS SELECT DISTINCT
ROWIDTOCHAR(det.ROWID)
FROM hxt_det_hours_worked_f det
WHERE det.pay_status IN ('A','D')
AND det.tim_id = p_tim_id
AND retro_batch_id_2 = p_batch_id;
IS SELECT ROWIDTOCHAR(det.ROWID),
pbl.batch_line_id
FROM pay_batch_lines pbl,
hxt_det_hours_worked_f det
WHERE pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id
AND det.tim_id = p_tim_id
AND det.pay_status = 'B'
AND NVL(det.retro_batch_id,0) <> p_batch_id;
IS SELECT ROWIDTOCHAR(det.ROWID)
FROM hxt_det_hours_worked_f det
WHERE retro_batch_id = p_batch_id
AND pay_status = 'C'
AND det.tim_id = p_tim_id
AND retro_pbl_line_id IS NULL;
IS SELECT ROWIDTOCHAR(det.ROWID),
pbl.batch_line_id
FROM pay_batch_lines pbl,
hxt_det_hours_worked_f det
WHERE pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id
AND det.tim_id = p_tim_id
AND det.retro_batch_id = pbl.batch_id
AND pay_status = 'C';
UPDATE hxt_det_hours_worked_f
SET pay_status = 'C',
retro_pbl_line_id = retro_pbl_line_id_2
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
UPDATE hxc_ret_pay_latest_details
SET retro_batch_id = NULL,
retro_pbl_id = NULL,
request_id = hxt_request_id,
measure = hxt_measure,
attribute1 = hxt_element_id,
attribute2 = hxt_attribute2,
attribute3 = hxt_attribute3,
batch_id = hxt_batch_id,
pbl_id = NULL,
hxt_request_id = NULL,
hxt_measure = NULL,
hxt_element_id = NULL,
hxt_attribute2 = NULL,
hxt_attribute3 = NULL,
hxt_batch_id = NULL
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'C',
retro_pbl_line_id = retro_pbl_line_id_2
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
UPDATE hxc_ret_pay_latest_details
SET retro_batch_id = NULL,
retro_pbl_id = NULL,
request_id = old_request_id,
measure = old_measure,
attribute1 = old_attribute1,
attribute2 = old_attribute2,
attribute3 = old_attribute3,
batch_id = old_batch_id,
pbl_id = old_pbl_id,
old_request_id = NULL,
old_measure = NULL,
old_attribute1 = NULL,
old_attribute2 = NULL,
old_attribute3 = NULL,
old_batch_id = NULL,
old_pbl_id = NULL
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
DELETE FROM hxc_ret_pay_latest_details
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
UPDATE hxt_det_hours_worked_f
SET retro_pbl_line_id = NULL,
pay_status = 'R'
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
DELETE FROM hxc_ret_pay_latest_details
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
AND l_rank_tab(i) <> 1;
UPDATE hxc_ret_pay_latest_details
SET request_id = hxt_request_id,
attribute1 = hxt_element_id,
attribute2 = hxt_attribute2,
attribute3 = hxt_attribute3,
measure = hxt_measure,
pbl_id = NULL,
hxt_request_id = NULL,
hxt_measure = NULL,
hxt_element_id = NULL,
hxt_attribute2 = NULL,
hxt_attribute3 = NULL,
hxt_batch_id = NULL
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
AND l_rank_tab(i) = 1;
UPDATE hxt_det_hours_worked_f
SET retro_pbl_line_id = NULL,
pay_status = 'R'
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
OPEN get_updated_records(p_batch_id,l_tim_tab(x));
FETCH get_updated_records BULK COLLECT INTO l_ret_rowtab;
CLOSE get_updated_records;
UPDATE hxc_ret_pay_latest_details
SET old_measure = measure,
old_attribute1 = attribute1,
old_attribute2 = attribute2,
old_attribute3 = attribute3,
old_request_id = request_id,
old_pbl_id = pbl_id,
old_batch_id = batch_id,
attribute1 = hxt_element_id,
attribute2 = hxt_attribute2,
attribute3 = hxt_attribute3,
measure = hxt_measure,
request_id = hxt_request_id,
batch_id = hxt_batch_id,
pbl_id = NULL,
hxt_measure = NULL,
hxt_request_id = NULL,
hxt_element_id = NULL,
hxt_attribute2 = NULL,
hxt_attribute3 = NULL,
hxt_batch_id = NULL
WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'C'
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'C',
retro_pbl_line_id = retro_pbl_line_id_2,
retro_pbl_line_id_2 = NULL
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'R'
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'R',
retro_pbl_line_id = NULL
WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
UPDATE hxt_batch_states
SET status = 'H'
WHERE batch_id = p_batch_id;
' SELECT pbh.batch_id,
pbh.batch_status
FROM pay_batch_headers pbh,
hxt_batch_states hbs
WHERE pbh.business_group_id = :bg_id
AND pbh.batch_id = hbs.batch_id
AND hbs.status = ''VT''';
delete_old_log;
IS SELECT id,
parent_id,
tim_id,
date_worked,
assignment_id,
hours,
time_in,
time_out,
element_type_id,
fcl_earn_reason_code,
ffv_cost_center_id,
tas_id,
location_id,
sht_id,
hrw_comment,
ffv_rate_code_id,
rate_multiple,
hourly_rate,
amount,
fcl_tax_rule_code,
separate_check_flag,
seqno,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
actual_time_in,
actual_time_out,
effective_start_date,
effective_end_date,
project_id,
job_id,
earn_pol_id,
NULL retro_batch_id,
'P' pa_status,
'P' pay_status,
object_version_number,
STATE_NAME ,
COUNTY_NAME ,
CITY_NAME ,
ZIP_CODE
FROM hxt_det_hours_worked
WHERE tim_id = p_tim_id;
SELECT hxt_seqno.NEXTVAL
FROM dual
connect by level <= p_count;
UPDATE hxt_det_hours_worked_f
SET effective_end_date = SYSDATE -1
WHERE tim_id = p_tim_id
AND effective_end_date = hr_general.end_of_time
AND pay_status IN ('A','B','D','R');
UPDATE hxt_det_hours_worked_f
SET pay_status = 'Z'
WHERE tim_id = p_tim_id
AND effective_end_date <> hr_general.end_of_time
AND pay_status = 'C';
UPDATE hxt_det_hours_worked_f
SET effective_end_date = SYSDATE -1,
pay_status = 'Z'
WHERE tim_id = p_tim_id
AND effective_end_date = hr_general.end_of_time
AND pay_status = 'C';
UPDATE hxt_det_hours_worked_f
SET rollback_status = 'R'
WHERE tim_id = p_tim_id
AND rollback_status IS NULL;
HXT_DML.insert_HXT_DET_HOURS_WORKED
(p_rowid => l_rowid
,p_id => l_ids_tab(i) -- Bug 13832327
,p_parent_id => l_det_tab(i).parent_id
,p_tim_id => l_det_tab(i).tim_id
,p_date_worked => l_det_tab(i).date_worked
,p_assignment_id => l_det_tab(i).assignment_id
,p_hours => l_det_tab(i).hours
,p_time_in => l_det_tab(i).time_in
,p_time_out => l_det_tab(i).time_out
,p_element_type_id => l_det_tab(i).element_type_id
,p_fcl_earn_reason_code => l_det_tab(i).fcl_earn_reason_code
,p_ffv_cost_center_id => l_det_tab(i).ffv_cost_center_id
,p_ffv_labor_account_id => NULL
,p_tas_id => l_det_tab(i).tas_id
,p_location_id => l_det_tab(i).location_id
,p_sht_id => l_det_tab(i).sht_id
,p_hrw_comment => l_det_tab(i).hrw_comment
,p_ffv_rate_code_id => l_det_tab(i).ffv_rate_code_id
,p_rate_multiple => l_det_tab(i).rate_multiple
,p_hourly_rate => l_det_tab(i).hourly_rate
,p_amount => l_det_tab(i).amount
,p_fcl_tax_rule_code => l_det_tab(i).fcl_tax_rule_code
,p_separate_check_flag => l_det_tab(i).separate_check_flag
,p_seqno => l_det_tab(i).seqno
,p_created_by => l_det_tab(i).created_by
,p_creation_date => l_det_tab(i).creation_date
,p_last_updated_by => l_det_tab(i).last_updated_by
,p_last_update_date => l_det_tab(i).last_update_date
,p_last_update_login => l_det_tab(i).last_update_login
,p_actual_time_in => l_det_tab(i).actual_time_in
,p_actual_time_out => l_det_tab(i).actual_time_out
,p_effective_start_date => l_det_tab(i).effective_start_date
,p_effective_end_date => l_det_tab(i).effective_end_date
,p_project_id => l_det_tab(i).project_id
,p_job_id => l_det_tab(i).job_id
,p_earn_pol_id => l_det_tab(i).earn_pol_id
,p_retro_batch_id => l_det_tab(i).retro_batch_id
,p_pa_status => l_det_tab(i).pa_status
,p_pay_status => l_det_tab(i).pay_status
,p_object_version_number => l_ovn
,p_STATE_NAME => l_det_tab(i).STATE_NAME
,p_COUNTY_NAME => l_det_tab(i).COUNTY_NAME
,p_CITY_NAME => l_det_tab(i).CITY_NAME
,p_ZIP_CODE => l_det_tab(i).ZIP_CODE
);
UPDATE hxt_timecards
SET batch_id = l_replacement_batch
WHERE id = p_tim_id;
INSERT INTO hxt_reversed_batch_tc_all
(batch_id,
replacement_batch_id,
tim_id)
VALUES
(p_batch_id,
l_replacement_batch,
p_tim_id);
DELETE FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id;
DELETE FROM hxt_sum_hours_worked_f
WHERE tim_id = p_tim_id;
DELETE FROM hxt_timecards_f
WHERE id = p_tim_id;
put_log('All Details for this timecard are deleted from OTM',g_cache);
IS SELECT det.tim_id
FROM hxt_timecards tim,
hxt_det_hours_worked det,
hxt_batch_states hbs
WHERE tim.id = p_tim_id
AND det.tim_id = tim.id
AND hbs.batch_id = tim.batch_id
AND hbs.status NOT IN ('VT')
AND det.pay_status = 'P';
IS SELECT id,
pbl_line_id,
retro_pbl_line_id,
retro_pbl_line_id_2,
retro_batch_id,
retro_batch_id_2,
pay_status
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND pay_status IN ('A','B','C','D')
ORDER BY pay_status;
IS SELECT DISTINCT
sum.id,
ret.time_building_block_id,
sum.time_building_block_ovn,
ret2.object_version_number,
ret2.measure,
ret2.attribute1,
ret2.attribute2,
ret2.attribute3,
ret2.request_id
FROM hxt_sum_hours_worked sum,
hxc_ret_pay_latest_details ret,
hxc_ret_pay_details ret2
WHERE sum.tim_id = p_tim_id
AND sum.time_building_block_id = ret.time_building_block_id
AND ret.time_building_block_id = ret2.time_building_block_id;
UPDATE hxt_det_hours_worked_f
SET rollback_status = 'X'
WHERE id = l_id_tab(i);
DELETE FROM hxc_ret_pay_latest_details
WHERE time_building_block_id = l_sum_id_tab(i)
AND object_version_number <> l_sum_ovn_tab(i);
DELETE FROM hxc_ret_pay_details
WHERE time_building_block_id = l_sum_id_tab(i)
AND object_version_number <> l_sum_ovn_tab(i);
UPDATE hxc_ret_pay_latest_details
SET measure = l_hrs_tab(i),
attribute1 = l_att1_tab(i),
attribute2 = l_att2_tab(i),
attribute3 = l_att3_tab(i),
request_id = l_req_tab(i),
old_measure = NULL,
old_attribute1 = NULL,
old_attribute2 = NULL,
old_attribute3 = NULL,
old_request_id = NULL,
old_batch_id = NULL,
old_pbl_id = NULL,
retro_batch_id = NULL,
retro_pbl_id = NULL,
hxt_request_id = NULL,
hxt_element_id = NULL,
hxt_attribute2 = NULL,
hxt_attribute3 = NULL,
old_ovn = NULL
WHERE time_building_block_id = l_sum_id_tab(i)
AND object_version_number = l_sum_ovn_tab(i)
AND l_ret_ovn_tab(i) = l_sum_ovn_tab(i);
IS SELECT det.tim_id
FROM hxt_timecards tim,
hxt_det_hours_worked det,
hxt_batch_states hbs
WHERE tim.id = p_tim_id
AND det.tim_id = tim.id
AND hbs.batch_id = tim.batch_id
AND hbs.status NOT IN ('VT')
AND det.pay_status = 'P';
IS SELECT id,
pbl_line_id,
retro_pbl_line_id,
retro_pbl_line_id_2,
retro_batch_id,
retro_batch_id_2,
pay_status
FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND pay_status IN ('A','B','C','D')
ORDER BY pay_status;
IS SELECT id
FROM hxt_sum_hours_worked_f
WHERE tim_id = p_tim_id
AND time_building_block_id IS NULL ;
IS SELECT DISTINCT
sum.id,
ret.time_building_block_id,
ret2.object_version_number,
ret.resource_id,
ret.approval_status,
ret.start_time,
ret.stop_time,
ret.application_set_id,
ret.last_update_date,
ret.comment_text,
ret.resource_type ,
ret.org_id,
ret.business_group_id,
ret.timecard_id,
ret2.attribute1,
ret2.attribute2,
ret2.attribute3,
ret2.measure
FROM hxt_sum_hours_worked sum,
hxc_ret_pay_latest_details ret,
hxc_ret_pay_details ret2
WHERE sum.tim_id = p_tim_id
AND sum.time_building_block_id = ret2.time_building_block_id
AND sum.time_building_block_ovn = ret2.object_version_number
AND ret.time_building_block_id = ret2.time_building_block_id
AND ret.object_version_number = ret2.object_version_number;
IS SELECT DISTINCT
sum.id,
ret2.time_building_block_id,
ret2.object_version_number,
htd.transaction_detail_id
FROM hxt_sum_hours_worked_f sum,
hxc_ret_pay_details ret2,
hxc_transaction_details htd,
hxc_transactions ht
WHERE sum.tim_id = p_tim_id
AND sum.time_building_block_id = ret2.time_building_block_id
AND sum.time_building_block_ovn = ret2.object_version_number
AND htd.time_building_block_id = ret2.time_building_block_id
AND htd.time_building_block_ovn = ret2.object_version_number
AND htd.transaction_id = ht.transaction_id
AND ht.type = 'RETRIEVAL'
AND ht.transaction_process_id = -1 ;
l_lines_last_update_date DATETAB ;
l_lines_last_update_date,
l_lines_comment_text,
l_lines_resource_type,
l_lines_org_id,
l_lines_business_group_id,
l_lines_timecard_id,
l_lines_attribute1,
l_lines_attribute2,
l_lines_attribute3,
l_lines_measure;
DELETE FROM hxc_ret_pay_latest_details
WHERE time_building_block_id = l_lines_time_building_block_id(i);
DELETE FROM hxc_ret_pay_details
WHERE time_building_block_id = l_lines_time_building_block_id(i);
UPDATE hxc_transaction_details
SET status = 'ROLLEDBACK'
WHERE transaction_detail_id = l_lines_td_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_transferred_to(SET(l_lines_timecard_id));
put_log('Selecting Retrieval History from Time Store');
DELETE FROM hxc_ret_pay_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_lines_td_id(i);
p_delete IN VARCHAR2 DEFAULT 'REPORT'
)
IS
l_query VARCHAR2(4000) :=
'SELECT id,batch_id
FROM hxt_timecards_f tim,
per_time_periods ptp
WHERE tim.payroll_id = :payroll_id
AND tim.time_period_id = :period_id
AND ptp.time_period_id = tim.time_period_id
AND ptp.payroll_id = tim.payroll_id';
'SELECT id
FROM hxt_timecards_f tim,
per_all_assignments_f
WHERE payroll_id = :payroll_id
AND time_period_id = :period_id';
IS SELECT batch_id
FROM pay_batch_headers
WHERE batch_reference = p_batch_ref;
put_log('p_delete '||p_delete );
delete_old_log;
l_asg_criteria := l_batch_filter||FND_Global.newline||' AND for_person_id IN ( SELECT person_id
FROM per_all_assignments_f paf
WHERE paf.effective_start_date <= ptp.end_date
AND paf.effective_end_date >= ptp.start_date
AND business_group_id = '||p_bg_id
||l_location_filter
||l_org_filter
||l_payroll_filter
||')';
l_asg_criteria := l_batch_filter||FND_Global.newline||' AND for_person_id IN ( SELECT person_id
FROM per_all_assignments_f paf,
hr_soft_coding_keyflex hsk
WHERE paf.effective_start_date <= ptp.end_date
AND paf.effective_end_date >= ptp.start_date
AND 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||''''
||')';
p_delete);