[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
i_last_updated_by IN NUMBER,
i_last_update_login IN NUMBER,
i_writesum_yn IN VARCHAR2,
i_explode_yn IN VARCHAR2,
i_batch_status IN VARCHAR2,
i_dt_update_mode IN VARCHAR2, --SIR290
p_time_building_block_id IN NUMBER DEFAULT NULL,
p_time_building_block_ovn IN NUMBER DEFAULT NULL,
o_otm_error OUT NOCOPY VARCHAR2,
o_oracle_error OUT NOCOPY VARCHAR2,
o_created_tim_sum_id OUT NOCOPY NUMBER,
i_start_time IN DATE,
i_end_time IN DATE,
i_state_name IN VARCHAR2 DEFAULT NULL,
i_county_name IN VARCHAR2 DEFAULT NULL,
i_city_name IN VARCHAR2 DEFAULT NULL,
i_zip_code IN VARCHAR2 DEFAULT NULL
)
RETURN NUMBER;
i_last_updated_by IN NUMBER,
i_last_update_login IN NUMBER,
i_writesum_yn IN VARCHAR2,
i_explode_yn IN VARCHAR2,
i_batch_status IN VARCHAR2,
i_dt_update_mode IN VARCHAR2,
p_time_building_block_id IN NUMBER DEFAULT NULL,
p_time_building_block_ovn IN NUMBER DEFAULT NULL,
o_otm_error OUT NOCOPY VARCHAR2,
o_oracle_error OUT NOCOPY VARCHAR2,
o_created_tim_sum_id OUT NOCOPY NUMBER,
i_state_name IN VARCHAR2 DEFAULT NULL,
i_county_name IN VARCHAR2 DEFAULT NULL,
i_city_name IN VARCHAR2 DEFAULT NULL,
i_zip_code IN VARCHAR2 DEFAULT NULL
)
RETURN NUMBER;
FUNCTION delete_summary_record (p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE)
RETURN NUMBER;
last_updated_by IN NUMBER,
last_update_login IN NUMBER,
writesum_yn IN VARCHAR2 DEFAULT 'Y',
explode_yn IN VARCHAR2 DEFAULT 'Y',
delete_yn IN VARCHAR2 DEFAULT 'N',
--AM 001
dt_update_mode IN VARCHAR2 DEFAULT NULL,
created_tim_sum_id OUT NOCOPY NUMBER,
otm_error OUT NOCOPY VARCHAR2,
oracle_error OUT NOCOPY VARCHAR2,
p_time_building_block_id IN NUMBER DEFAULT NULL,
p_time_building_block_ovn IN NUMBER DEFAULT NULL,
p_validate IN BOOLEAN DEFAULT FALSE,
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
l_person_id per_people_f.person_id%TYPE DEFAULT NULL;
l_delete_sys_error EXCEPTION;
l_delete_finished EXCEPTION;
l_dt_update_mode_err EXCEPTION;
IS SELECT 1
FROM hxt_timecards_f tim,
hxc_data_sets hds
WHERE tim.id = p_id
AND tim.data_set_id = hds.data_set_id
AND hds.status IN ('OFF_LINE','RESTORE_IN_PROGRESS','ARCHIVE_IN_PROGRESS');
/* Validate dt_update_mode */
IF (time_summary_id IS NOT NULL) AND (dt_update_mode IS NULL)
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 60);
IF (dt_update_mode IS NOT NULL)
AND -- SIR293
(dt_update_mode NOT IN ('CORRECTION', 'UPDATE')
)
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 70);
RAISE l_dt_update_mode_err;
/* Check for and perform any deletes */
IF delete_yn = 'Y' AND time_summary_id IS NOT NULL
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 130);
l_retcode := delete_summary_record (time_summary_id);
RAISE l_delete_sys_error;
RAISE l_delete_finished;
SELECT last_name, first_name
INTO l_last_name, l_first_name
FROM per_all_people_f
WHERE person_id = l_person_id
AND l_date_worked BETWEEN effective_start_date AND effective_end_date;
last_updated_by,
last_update_login,
writesum_yn,
explode_yn,
l_batch_status,
dt_update_mode, --SIR290
p_time_building_block_id,
p_time_building_block_ovn,
l_otm_error,
l_oracle_error,
l_created_tim_sum_id,
start_time,
end_time);
hr_utility.TRACE ('delete_yn:' || delete_yn);
IF delete_yn = 'N'
THEN
-- Bugs 3384941, 3382457, 3381642 fix
-- i.e., If it is not a deleted record then create it.
-- If it is a deleted record then we should skip this call to
-- record_hours_worked
-- Insert hours to the hxt_sum_hours_worked table and
-- call generate details
IF g_debug
THEN
hr_utility.TRACE ('BEFORE record_hours worked');
last_updated_by,
last_update_login,
writesum_yn,
explode_yn,
l_batch_status,
dt_update_mode,
p_time_building_block_id,
p_time_building_block_ovn,
l_otm_error,
l_oracle_error,
l_created_tim_sum_id,
p_state_name,
p_county_name,
p_city_name,
p_zip_code
-- , p_mode
);
WHEN l_delete_sys_error
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 1620);
WHEN l_delete_finished
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 1630);
WHEN l_dt_update_mode_err
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 1640);
fnd_message.set_token ('DT_UPD_MODE', dt_update_mode);
fnd_message.set_name ('HXC', 'HXC_SELECTED_ARCHIVED_PERIOD');
call_hxthxc_gen_error ('HXC', 'HXC_SELECTED_ARCHIVED_PERIOD', NULL);
SELECT hxt_seqno.NEXTVAL
INTO l_error_seqno
FROM DUAL;
INSERT INTO hxt_timeclock_errors
(ID, otm_error, creation_date, created_by,
ora_error, timecard_source, approver_number,
employee_number, date_worked, start_time, end_time, hours,
earning_policy, hours_type, earn_reason_code, project,
task_number, location_code, hrw_comment, rate_multiple,
hourly_rate, amount, separate_check_flag,
business_group_id, concat_cost_segments, cost_segment1,
cost_segment2, cost_segment3, cost_segment4,
cost_segment5, cost_segment6, cost_segment7,
cost_segment8, cost_segment9, cost_segment10,
cost_segment11, cost_segment12, cost_segment13,
cost_segment14, cost_segment15, cost_segment16,
cost_segment17, cost_segment18, cost_segment19,
cost_segment20, cost_segment21, cost_segment22,
cost_segment23, cost_segment24, cost_segment25,
cost_segment26, cost_segment27, cost_segment28,
cost_segment29, cost_segment30, time_summary_id,
tim_sum_eff_start_date, tim_sum_eff_end_date
)
VALUES (l_error_seqno, otm_msg, g_sysdatetime, created_by,
ora_message, timecard_source, approver_number,
employee_number, date_worked, start_time, end_time, hours,
earning_policy, hours_type, earn_reason_code, project,
task_number, location_code, hrw_comment, rate_multiple,
hourly_rate, amount, separate_check_flag,
business_group_id, concat_cost_segments, cost_segment1,
cost_segment2, cost_segment3, cost_segment4,
cost_segment5, cost_segment6, cost_segment7,
cost_segment8, cost_segment9, cost_segment10,
cost_segment11, cost_segment12, cost_segment13,
cost_segment14, cost_segment15, cost_segment16,
cost_segment17, cost_segment18, cost_segment19,
cost_segment20, cost_segment21, cost_segment22,
cost_segment23, cost_segment24, cost_segment25,
cost_segment26, cost_segment27, cost_segment28,
cost_segment29, cost_segment30, time_summary_id,
tim_sum_eff_start_date, tim_sum_eff_end_date
);
dt_update_mode IN VARCHAR2, --SIR290
otm_error OUT NOCOPY VARCHAR2,
oracle_error OUT NOCOPY VARCHAR2
)
-- p_mode IN VARCHAR2 default 'INSERT')
IS
-- Bug 7359347
-- Changed the below cursors to use a session_date input value.
/*
CURSOR get_timecard_rec (
c_tim_id NUMBER,
c_tim_start_date DATE,
c_tim_end_date DATE
)
IS
SELECT tim.for_person_id, tbh.status, ptp.start_date --SIR286
FROM hxt_timecards tim, --SIR290
hxt_batch_states tbh,
per_time_periods ptp --SIR286
WHERE tim.ID = c_tim_id
AND tbh.batch_id = tim.batch_id
AND ptp.time_period_id = tim.time_period_id -- SIR286
; --SIR290
SELECT tim.for_person_id, tbh.status, ptp.start_date --SIR286
FROM hxt_timecards_f tim, --SIR290
hxt_batch_states tbh,
per_time_periods ptp --SIR286
WHERE tim.ID = c_tim_id
AND tbh.batch_id = tim.batch_id
AND ptp.time_period_id = tim.time_period_id
AND c_tim_start_date BETWEEN effective_start_date
AND effective_end_date ;
SELECT ID, -- group_id,
effective_start_date, effective_end_date, tim_id,
date_worked, assignment_id, seqno, 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, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
actual_time_in, actual_time_out, prev_wage_code, project_id,
earn_pol_id, time_building_block_id,
time_building_block_ovn, state_name, county_name, city_name,
zip_code
FROM hxt_sum_hours_worked
WHERE tim_id = c_tim_id
ORDER BY date_worked, element_type_id, time_in, seqno, ID;
SELECT ID, -- group_id,
effective_start_date, effective_end_date, tim_id,
date_worked, assignment_id, seqno, 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, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
actual_time_in, actual_time_out, prev_wage_code, project_id,
earn_pol_id, time_building_block_id,
time_building_block_ovn, state_name, county_name, city_name,
zip_code
FROM hxt_sum_hours_worked_f
WHERE tim_id = c_tim_id
AND c_tim_end_date BETWEEN effective_start_date
AND effective_end_date
ORDER BY date_worked, element_type_id, time_in, seqno, ID;
l_delete_details_err EXCEPTION;
hxt_time_collection.delete_details (timecard_id,
dt_update_mode,
l_session_date,
l_otm_error
);
RAISE l_delete_details_err;
l_sum_hours_rec.last_updated_by,
l_sum_hours_rec.last_update_login,
'N', --writesum_yn
'Y', --explode_yn
l_timecard_rec.status,
dt_update_mode, --SIR290
l_sum_hours_rec.time_building_block_id,
l_sum_hours_rec.time_building_block_ovn,
l_otm_error,
l_oracle_error,
l_created_tim_sum_id,
l_sum_hours_rec.state_name,
l_sum_hours_rec.county_name,
l_sum_hours_rec.city_name,
l_sum_hours_rec.zip_code
);
WHEN l_delete_details_err
THEN
fnd_message.set_name ('HXT', 'HXT_39563_ERR_IN_RET_DD');
SELECT time_period_id, start_date, end_date
INTO o_time_period, o_start_date, o_end_date
FROM per_time_periods
WHERE payroll_id = i_payroll_id
AND TRUNC (i_date_worked) BETWEEN TRUNC (start_date) AND TRUNC
(end_date);
SELECT ID, auto_gen_flag, batch_id
INTO o_timecard_id, o_auto_gen_flag, g_batch_err_id
FROM hxt_timecards_f
WHERE for_person_id = i_person_id AND time_period_id = i_time_period_id;
SELECT status
INTO o_batch_status
FROM hxt_batch_states
WHERE batch_id = i_batch_id;
/* Insert new timecard info to hxt_timecards */
/* INSERT into hxt_timecards_f
( id,
for_person_id,
payroll_id,
time_period_id,
batch_id,
approv_person_id,
auto_gen_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
effective_start_date,
effective_end_date)
VALUES
( l_timecard_id,
i_person_id,
i_payroll_id,
i_time_period_id,
l_batch_id,
i_approver_id,
i_timecard_source_code,
g_user_id,
g_sysdatetime,
g_user_id,
g_sysdatetime,
g_login_id,
trunc(g_sess_date),
hr_general.end_of_time);
/* Call dml to insert new timecard. */
IF g_debug
THEN
hr_utility.TRACE ('BEFORE DML create timecard');
hxt_dml.insert_hxt_timecards
(p_rowid => l_rowid,
p_id => l_timecard_id,
p_for_person_id => i_person_id,
p_time_period_id => i_time_period_id,
p_auto_gen_flag => i_timecard_source_code,
p_batch_id => l_batch_id,
p_approv_person_id => i_approver_id,
p_approved_timestamp => NULL,
p_created_by => g_user_id,
p_creation_date => g_sysdatetime,
p_last_updated_by => g_user_id,
p_last_update_date => g_sysdatetime,
p_last_update_login => g_login_id,
p_payroll_id => i_payroll_id,
p_status => NULL,
p_effective_start_date => TRUNC (g_sess_date),
p_effective_end_date => hr_general.end_of_time,
p_object_version_number => l_object_version_number
);
hr_utility.TRACE ('BEFORE INSERT batch ');
/* INSERT INTO pay_batch_headers
(batch_id, business_group_id, batch_name, batch_status,
action_if_exists, batch_reference, batch_source,
purge_after_transfer, reject_if_future_changes,
last_update_date, last_updated_by, last_update_login,
created_by, creation_date)
VALUES (l_batch_id, i_business_group_id, l_batch_name, 'U',
'I', l_reference_num, 'OTM',
'N', 'N',
g_sysdatetime, g_user_id, g_login_id,
g_user_id, g_sysdatetime);
hr_utility.TRACE ('AFTER insert batch ');
pay_batch_element_entry_api.update_batch_header
(p_session_date => g_sysdatetime,
p_batch_id => l_batch_id,
p_object_version_number => l_object_version_number,
p_batch_name => l_batch_name
);
SELECT COUNT (ht.ID) num_tcs, MAX (ht.batch_id) batch_id
FROM hxt_timecards ht,
hxt_batch_states hbs,
pay_batch_headers pbh
WHERE ht.time_period_id = p_time_period_id
AND hbs.batch_id = ht.batch_id
AND pbh.batch_id = ht.batch_id
AND hbs.status <> 'VT'
AND pbh.batch_reference LIKE NVL (p_batch_reference, '%') || '%'
HAVING COUNT (ht.ID) < g_max_tc_allowed
GROUP BY ht.batch_id;
i_last_updated_by IN NUMBER,
i_last_update_login IN NUMBER,
i_writesum_yn IN VARCHAR2,
i_explode_yn IN VARCHAR2,
i_batch_status IN VARCHAR2,
i_dt_update_mode IN VARCHAR2, --SIR290
p_time_building_block_id IN NUMBER DEFAULT NULL,
p_time_building_block_ovn IN NUMBER DEFAULT NULL,
o_otm_error OUT NOCOPY VARCHAR2,
o_oracle_error OUT NOCOPY VARCHAR2,
o_created_tim_sum_id OUT NOCOPY NUMBER,
i_start_time IN DATE,
i_end_time IN DATE,
i_state_name IN VARCHAR2 DEFAULT NULL,
i_county_name IN VARCHAR2 DEFAULT NULL,
i_city_name IN VARCHAR2 DEFAULT NULL,
i_zip_code IN VARCHAR2 DEFAULT NULL
)
-- p_mode IN VARCHAR2 default 'INSERT')
RETURN NUMBER
IS
l_hol_rec g_hol_cur%ROWTYPE;
i_last_updated_by,
i_last_update_login,
i_writesum_yn,
i_explode_yn,
i_batch_status,
i_dt_update_mode,
p_time_building_block_id,
p_time_building_block_ovn,
l_otm_error,
l_oracle_error,
l_created_tim_sum_id,
i_state_name,
i_county_name,
i_city_name,
i_zip_code
);
i_last_updated_by IN NUMBER,
i_last_update_login IN NUMBER,
i_writesum_yn IN VARCHAR2,
i_explode_yn IN VARCHAR2,
i_batch_status IN VARCHAR2,
i_dt_update_mode IN VARCHAR2, --SIR290
p_time_building_block_id IN NUMBER DEFAULT NULL,
p_time_building_block_ovn IN NUMBER DEFAULT NULL,
o_otm_error OUT NOCOPY VARCHAR2,
o_oracle_error OUT NOCOPY VARCHAR2,
o_created_tim_sum_id OUT NOCOPY NUMBER,
i_state_name IN VARCHAR2 DEFAULT NULL,
i_county_name IN VARCHAR2 DEFAULT NULL,
i_city_name IN VARCHAR2 DEFAULT NULL,
i_zip_code IN VARCHAR2 DEFAULT NULL
)
RETURN NUMBER
IS
-- Bug 7359347
-- Changed the below cursors to pick up
-- the base table instead of the view.
/*
CURSOR upd_det_cur (p_id NUMBER)
IS
SELECT fcl_earn_reason_code, ffv_cost_center_id, rate_multiple,
hourly_rate, separate_check_flag, seqno, creation_date
-- group_id
FROM hxt_sum_hours_worked
WHERE ID = p_id
AND g_sysdate BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM hxt_sum_hours_worked
WHERE ID = c_sum_id AND effective_start_date = TRUNC (g_sess_date);
SELECT fcl_earn_reason_code, ffv_cost_center_id, rate_multiple,
hourly_rate, separate_check_flag, seqno, creation_date
-- group_id
FROM hxt_sum_hours_worked_f
WHERE ID = p_id
AND g_sysdate BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM hxt_sum_hours_worked_f
WHERE ID = c_sum_id AND effective_start_date = TRUNC (g_sess_date);
l_dt_update_mode VARCHAR2 (20) := i_dt_update_mode;
l_delete_details_err EXCEPTION;
INSERT INTO hxt_sum_hours_worked_f
( id,
tim_id,
date_worked,
seqno,
hours,
group_id,
assignment_id,
element_type_id,
actual_time_in, --SIR374
actual_time_out, --SIR374
time_in,
time_out,
fcl_earn_reason_code,
ffv_cost_center_id,
tas_id,
location_id,
project_id,
earn_pol_id,
separate_check_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
prev_wage_code,
hrw_comment,
rate_multiple,
hourly_rate,
amount,
effective_start_date,
effective_end_date)
VALUES
( l_hours_worked_id,
i_timecard_id,
i_date_worked,
l_sequence_number,
l_hours, -- SIR236
l_group_id,
i_assignment_id,
i_element_id,
i_start_time, -- SIR374 Actual time in
i_end_time, -- SIR374 Actual time out
l_start_time,
l_end_time,
i_earn_reason_code,
i_cost_center_id,
i_task_id,
i_location_id,
i_project_id,
i_earn_pol_id,
i_separate_check_flag,
i_created_by,
g_sysdatetime,
i_last_updated_by,
g_sysdatetime,
i_last_update_login,
i_wage_code,
i_comment,
i_rate_multiple,
i_hourly_rate,
i_amount,
TRUNC(g_sess_date),
hr_general.end_of_time);
/* Call dml to insert hours */
hxt_dml.insert_hxt_sum_hours_worked
(p_rowid => l_rowid,
p_id => l_hours_worked_id,
p_tim_id => i_timecard_id,
p_date_worked => i_date_worked,
p_assignment_id => i_assignment_id,
p_hours => l_hours,
p_time_in => l_start_time,
p_time_out => l_end_time,
p_element_type_id => i_element_id,
p_fcl_earn_reason_code => i_earn_reason_code,
p_ffv_cost_center_id => i_cost_center_id,
p_ffv_labor_account_id => NULL,
p_tas_id => i_task_id,
p_location_id => i_location_id,
p_sht_id => NULL,
p_hrw_comment => i_comment,
p_ffv_rate_code_id => NULL,
p_rate_multiple => i_rate_multiple,
p_hourly_rate => i_hourly_rate,
p_amount => i_amount,
p_fcl_tax_rule_code => NULL,
p_separate_check_flag => i_separate_check_flag,
p_seqno => l_sequence_number,
p_created_by => i_created_by,
p_creation_date => g_sysdatetime,
p_last_updated_by => i_last_updated_by,
p_last_update_date => g_sysdatetime,
p_last_update_login => i_last_update_login,
p_actual_time_in => i_start_time,
p_actual_time_out => i_end_time,
p_effective_start_date => TRUNC (g_sess_date),
p_effective_end_date => hr_general.end_of_time,
p_project_id => i_project_id,
p_prev_wage_code => i_wage_code,
p_job_id => NULL,
p_earn_pol_id => i_earn_pol_id,
p_time_building_block_id => p_time_building_block_id,
p_time_building_block_ovn => p_time_building_block_ovn,
p_object_version_number => l_object_version_number,
p_state_name => i_state_name,
p_county_name => i_county_name,
p_city_name => i_city_name,
p_zip_code => i_zip_code
);
IF (l_dt_update_mode = 'CORRECTION')
AND (l_allow_sum_correction = 'Y')
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 210);
/* UPDATE hxt_sum_hours_worked_f
SET effective_start_date = i_tim_sum_eff_start_date,
effective_end_date = i_tim_sum_eff_end_date,
date_worked = i_date_worked,
assignment_id = i_assignment_id,
hours = l_hours, -- SIR236
time_in = l_start_time, -- SIR236
time_out = l_end_time, -- SIR236
element_type_id = i_element_id,
fcl_earn_reason_code = i_earn_reason_code,
ffv_cost_center_id = i_cost_center_id,
tas_id = i_task_id,
location_id = i_location_id,
hrw_comment = i_comment,
rate_multiple = i_rate_multiple,
hourly_rate = i_hourly_rate,
amount = i_amount,
separate_check_flag = i_separate_check_flag,
last_updated_by = i_last_updated_by,
last_update_date = g_sysdatetime,
last_update_login = i_last_update_login,
prev_wage_code = i_wage_code,
project_id = i_project_id,
earn_pol_id = i_earn_pol_id
WHERE ROWID = (SELECT ROWID
FROM hxt_sum_hours_worked
WHERE id = i_time_summary_id); */
/* Call DML to do the update */
-- Bug 7359347
-- Changed the query below to pick up the base table instead of the
-- view.
/*
SELECT ROWID, tim_id, seqno, created_by,
creation_date, actual_time_in, actual_time_out,
job_id, object_version_number
INTO l_rowid, l_time_id, l_sequence_number, l_created_by,
l_creation_date, l_actual_time_in, l_actual_time_out,
l_job_id, l_object_version_number
FROM hxt_sum_hours_worked
WHERE ID = i_time_summary_id;
SELECT ROWID, tim_id, seqno, created_by,
creation_date, actual_time_in, actual_time_out,
job_id, object_version_number
INTO l_rowid, l_time_id, l_sequence_number, l_created_by,
l_creation_date, l_actual_time_in, l_actual_time_out,
l_job_id, l_object_version_number
FROM hxt_sum_hours_worked_f
WHERE ID = i_time_summary_id
AND g_sess_date BETWEEN effective_start_date
AND effective_end_date ;
hxt_dml.update_hxt_sum_hours_worked
(p_rowid => l_rowid,
p_id => i_time_summary_id,
p_tim_id => l_time_id,
p_date_worked => i_date_worked,
p_assignment_id => i_assignment_id,
p_hours => l_hours,
p_time_in => l_start_time,
p_time_out => l_end_time,
p_element_type_id => i_element_id,
p_fcl_earn_reason_code => i_earn_reason_code,
p_ffv_cost_center_id => i_cost_center_id,
p_ffv_labor_account_id => NULL,
p_tas_id => i_task_id,
p_location_id => i_location_id,
p_sht_id => NULL,
p_hrw_comment => i_comment,
p_ffv_rate_code_id => NULL,
p_rate_multiple => i_rate_multiple,
p_hourly_rate => i_hourly_rate,
p_amount => i_amount,
p_fcl_tax_rule_code => NULL,
p_separate_check_flag => i_separate_check_flag,
p_seqno => l_sequence_number,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => i_last_updated_by,
p_last_update_date => g_sysdatetime,
p_last_update_login => i_last_update_login,
p_actual_time_in => l_actual_time_in,
p_actual_time_out => l_actual_time_out,
p_effective_start_date => i_tim_sum_eff_start_date,
p_effective_end_date => i_tim_sum_eff_end_date,
p_project_id => i_project_id,
p_prev_wage_code => i_wage_code,
p_job_id => l_job_id,
p_earn_pol_id => i_earn_pol_id,
p_time_building_block_id => p_time_building_block_id,
p_time_building_block_ovn => p_time_building_block_ovn,
p_object_version_number => l_object_version_number,
p_state_name => i_state_name,
p_county_name => i_county_name,
p_city_name => i_city_name,
p_zip_code => i_zip_code
);
SELECT ROWID
INTO v_row_id
FROM hxt_sum_hours_worked
WHERE ID = i_time_summary_id;
SELECT ROWID
INTO v_row_id
FROM hxt_sum_hours_worked_f
WHERE ID = i_time_summary_id
AND g_sess_date BETWEEN effective_start_date
AND effective_end_date ;
INSERT INTO hxt_sum_hours_worked_f
( id,
tim_id,
date_worked,
seqno,
hours,
-- group_id,
assignment_id,
element_type_id,
actual_time_in, --SIR374
actual_time_out, --SIR374
time_in,
time_out,
fcl_earn_reason_code,
ffv_cost_center_id,
tas_id,
location_id,
project_id,
earn_pol_id,
separate_check_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
prev_wage_code,
hrw_comment,
rate_multiple,
hourly_rate,
amount,
effective_start_date,
effective_end_date)
SELECT tsm.id,
tsm.tim_id,
i_date_worked, -- SIR290 tsm.date_worked,
tsm.seqno,
i_hours, -- SIR290 tsm.hours,
-- l_group_id,
i_assignment_id, -- SIR290 tsm.assignment_id,
i_element_id, -- SIR290 tsm.element_type_id,
i_start_time, -- SIR374 Actual time in
i_end_time, -- SIR374 Actual time out
i_start_time, -- SIR290 tsm.time_in,
i_end_time, -- SIR290 tsm.time_out,
i_earn_reason_code, -- SIR290 tsm.fcl_earn_reason_code,
i_cost_center_id, -- SIR290 tsm.ffv_cost_center_id,
i_task_id, -- SIR290 tsm.tas_id,
i_location_id, -- SIR290 tsm.location_id,
i_project_id, -- SIR290 tsm.project_id,
i_earn_pol_id, -- SIR290 tsm.earn_pol_id,
i_separate_check_flag, -- SIR290 tsm.separate_check_flag,
i_created_by, -- SIR290 tsm.created_by,
g_sysdatetime, -- SIR290 tsm.creation_date,
i_last_updated_by,
g_sysdatetime,
i_last_update_login,
i_wage_code, -- SIR290 tsm.wage_code,
i_comment, -- SIR290 tsm.hrw_comment,
i_rate_multiple, -- SIR290 tsm.rate_multiple,
i_hourly_rate, -- SIR290 tsm.hourly_rate,
i_amount, -- SIR290 tsm.amount,
trunc(g_sess_date),
hr_general.end_of_time
FROM HXT_SUM_HOURS_WORKED_F TSM
WHERE ROWID = v_row_id;
SELECT tsm.ID, tsm.tim_id, tsm.seqno
INTO l_hours_worked_id, l_time_id, l_sequence_number
FROM hxt_sum_hours_worked_f tsm
WHERE ROWID = v_row_id;
hxt_dml.insert_hxt_sum_hours_worked
(p_rowid => l_rowid,
p_id => l_hours_worked_id,
p_tim_id => l_time_id,
p_date_worked => i_date_worked,
p_assignment_id => i_assignment_id,
p_hours => l_hours,
p_time_in => l_start_time,
p_time_out => l_end_time,
p_element_type_id => i_element_id,
p_fcl_earn_reason_code => i_earn_reason_code,
p_ffv_cost_center_id => i_cost_center_id,
p_ffv_labor_account_id => NULL,
p_tas_id => i_task_id,
p_location_id => i_location_id,
p_sht_id => NULL,
p_hrw_comment => i_comment,
p_ffv_rate_code_id => NULL,
p_rate_multiple => i_rate_multiple,
p_hourly_rate => i_hourly_rate,
p_amount => i_amount,
p_fcl_tax_rule_code => NULL,
p_separate_check_flag => i_separate_check_flag,
p_seqno => l_sequence_number,
p_created_by => i_created_by,
p_creation_date => g_sysdatetime,
p_last_updated_by => i_last_updated_by,
p_last_update_date => g_sysdatetime,
p_last_update_login => i_last_update_login,
p_actual_time_in => i_start_time,
p_actual_time_out => i_end_time,
p_effective_start_date => TRUNC (g_sess_date),
p_effective_end_date => hr_general.end_of_time,
p_project_id => i_project_id,
p_prev_wage_code => i_wage_code,
p_job_id => NULL,
p_earn_pol_id => i_earn_pol_id,
p_time_building_block_id => p_time_building_block_id,
p_time_building_block_ovn => p_time_building_block_ovn,
p_object_version_number => l_object_version_number,
p_state_name => i_state_name,
p_county_name => i_county_name,
p_city_name => i_city_name,
p_zip_code => i_zip_code
);
UPDATE hxt_sum_hours_worked_f
SET effective_end_date = TRUNC (g_sess_date - 1)
WHERE ROWID = v_row_id;
o_dt_update_mod => l_dt_update_mode,
o_error_message => l_otm_error,
o_return_code => o_return_code,
p_parent_id => l_hours_worked_id
);
IF NVL (l_dt_update_mode, 'CORRECTION') = 'CORRECTION'
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 350);
DELETE FROM hxt_det_hours_worked_f
WHERE ROWID IN (SELECT ROWID
FROM hxt_det_hours_worked
WHERE parent_id = l_hours_worked_id);
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = l_hours_worked_id
AND l_session_date BETWEEN effective_start_date
AND effective_end_date;
DELETE FROM hxt_errors_f
WHERE ROWID IN (
SELECT ROWID
FROM hxt_errors
WHERE hrw_id = l_hours_worked_id
AND hrw_id IS NOT NULL
--hxt11ipatch don't delete timecard
); --level errors
UPDATE hxt_det_hours_worked_f
SET effective_end_date = l_session_date - 1
WHERE ROWID IN (SELECT ROWID
FROM hxt_det_hours_worked
WHERE parent_id = l_hours_worked_id);
UPDATE hxt_det_hours_worked_f
SET effective_end_date = TRUNC(l_session_date - 1)
WHERE parent_id = l_hours_worked_id
AND l_session_date BETWEEN effective_start_date
AND effective_end_date ;
UPDATE hxt_errors_f
SET effective_end_date = l_session_date - 1
WHERE ROWID IN (
SELECT ROWID
FROM hxt_errors
WHERE hrw_id = l_hours_worked_id
AND hrw_id IS NOT NULL
--hxt11ipatch don't expire timecard
); -- level errors
END IF; -- Update or Correction
END; -- delete details
i_last_updated_by,
SYSDATE -- last_update_date
,
i_last_update_login,
i_start_date,
NULL,
TRUNC (g_sess_date),
hr_general.end_of_time,
i_project_id -- p_PROJECT_ID
,
NULL -- p_job_id
,
'P' -- p_PAY_STATUS
,
'P' -- p_PA_STATUS
,
NULL -- p_RETRO_BATCH_ID
,
NVL (l_dt_update_mode, 'CORRECTION'),
p_state_name => i_state_name,
p_county_name => i_county_name,
p_city_name => i_city_name,
p_zip_code => i_zip_code
);
WHEN l_delete_details_err
THEN
IF g_debug
THEN
hr_utility.set_location (l_proc, 510);
delete_summary_record()
Deletes hxt_sum_hours_worked_f row indicated by i_tim_sum_id.
***************************************************************/
FUNCTION delete_summary_record (p_sum_id IN hxt_sum_hours_worked_f.ID%TYPE)
RETURN NUMBER
IS
l_proc VARCHAR2 (72);
l_delete_not_allowed EXCEPTION;
l_dt_update_mode VARCHAR2 (20);
SELECT tim_id
FROM hxt_sum_hours_worked_f
WHERE ID = p_sum_id
AND g_sess_date BETWEEN effective_start_date
AND effective_end_date;
DELETE FROM hxt_det_hours_worked_f
WHERE parent_id = p_sum_id;
DELETE FROM hxt_sum_hours_worked_f
WHERE ID = p_sum_id;
BEGIN -- Main delete_summary_record
g_debug := hr_utility.debug_enabled;
l_proc := g_package || 'delete_summary_record';
o_dt_update_mod => l_dt_update_mode,
o_error_message => l_error_message,
o_return_code => l_return_code
);
IF (l_dt_update_mode IS NULL)
THEN
-- Bug 12850901
-- If retro_restrict_edit is sending back the Integration error, raise a
-- specific exception for that. Else raise the retro_edit exception.
IF l_error_message = g_intg_error
THEN
RAISE l_integ_error;
ELSIF (l_dt_update_mode = 'UPDATE')
THEN
RAISE l_delete_not_allowed;
WHEN l_delete_not_allowed
THEN
IF g_debug
THEN
hr_utility.set_location ('Leaving ' || l_proc, 120);
'HXT_TC_CANNOT_BE_DELETED',
l_error_message
);
END delete_summary_record;
PROCEDURE delete_details (
p_tim_id IN NUMBER,
p_dt_update_mode IN VARCHAR2,
p_effective_start_date IN DATE,
o_error_message OUT NOCOPY NUMBER
)
IS
BEGIN
/* Initialize globals */
g_sysdate := TRUNC (SYSDATE);
IF NVL (p_dt_update_mode, 'CORRECTION') = 'CORRECTION'
THEN
-- Bug 7359347
-- Changed the below DELETE to look at the table instead
-- of the view.
/*
DELETE FROM hxt_det_hours_worked_f
WHERE ROWID IN (SELECT ROWID
FROM hxt_det_hours_worked
WHERE tim_id = p_tim_id);
DELETE FROM hxt_det_hours_worked_f
WHERE tim_id = p_tim_id
AND p_effective_start_date BETWEEN effective_start_date
AND effective_end_date ;
DELETE FROM hxt_errors_f
WHERE ROWID IN (
SELECT ROWID
FROM hxt_errors
WHERE tim_id = p_tim_id
AND hrw_id IS NOT NULL
--hxt11ipatch don't delete timecard
); -- level errors
UPDATE hxt_det_hours_worked_f
SET effective_end_date = p_effective_start_date - 1
WHERE ROWID IN (SELECT ROWID
FROM hxt_det_hours_worked
WHERE tim_id = p_tim_id);
UPDATE hxt_det_hours_worked_f
SET effective_end_date = TRUNC(p_effective_start_date - 1)
WHERE tim_id = p_tim_id
AND p_effective_start_date BETWEEN effective_start_date
AND effective_end_date ;
UPDATE hxt_errors_f
SET effective_end_date = p_effective_start_date - 1
WHERE ROWID IN (SELECT ROWID
FROM hxt_errors
WHERE tim_id = p_tim_id AND hrw_id IS NOT NULL
--hxt11ipatch don't expire timecard
); -- level errors
END IF; -- Update or Correction
DELETE FROM HXT_DET_DUPLICATE_REGULAR
WHERE tim_id = p_tim_id;
END; -- delete details
SELECT hxt_rotation_plan
INTO l_rot_id
FROM hxt_per_aei_ddf_v aeiv, per_all_assignments_f asm -- Bug 12776437
WHERE asm.assignment_id = p_assignment_id
AND asm.assignment_id = aeiv.assignment_id
AND p_date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND p_date_worked BETWEEN aeiv.effective_start_date
AND aeiv.effective_end_date;
SELECT min_tcard_intvl, round_up
INTO l_tc_intvl, l_round_up
FROM hxt_earning_policies
WHERE ID = p_earn_pol_id;
SELECT 'E'
FROM hxt_sum_hours_worked --C421
WHERE (p_time_in < time_out AND p_time_out > time_in)
--C421 AND parent_id = 0
AND date_worked = p_date
AND ID <> NVL (p_id, 0)
AND tim_id = p_tim_id;
SELECT 'E'
FROM hxt_sum_hours_worked_f --C421
WHERE (p_time_in < time_out AND p_time_out > time_in)
--C421 AND parent_id = 0
AND date_worked = p_date
AND session_date BETWEEN effective_start_date
AND effective_end_date
AND ID <> NVL (p_id, 0)
AND tim_id = p_tim_id;