[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_prev_val_errors (
p_batch_id IN hxt_timecards_f.batch_id%TYPE
)
AS
l_proc VARCHAR2 (72) ;
|| 'delete_prev_val_errors';
DELETE FROM hxt_errors_x
WHERE ppb_id = p_batch_id AND location LIKE 'Validate%';
END delete_prev_val_errors;
PROCEDURE delete_prev_val_errors (p_tim_id IN hxt_timecards_f.id%TYPE)
AS
l_proc VARCHAR2 (72) ;
|| 'delete_prev_val_errors';
DELETE FROM hxt_errors_x
WHERE tim_id = p_tim_id AND location LIKE 'Validate%';
END delete_prev_val_errors;
SELECT 1
FROM hxt_errors_x
WHERE tim_id = p_tim_id;
SELECT ptp.end_date
FROM per_time_periods ptp, hxt_timecards_x htx
WHERE ptp.time_period_id = htx.time_period_id AND htx.id = p_tim_id;
SELECT 1
FROM per_people_f ppf
WHERE person_id = p_person_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT /*+ ORDERED
INDEX(paf PER_ASSIGNMENTS_F_N12)
INDEX(past PER_ASSIGNMENT_STATUS_TYPE_PK) */
/* Hints supplied to always force the correct execution plan */
paf.assignment_id
FROM
per_assignments_f paf,
per_assignment_status_types past
WHERE
paf.person_id = p_person_id AND
p_effective_date BETWEEN
paf.effective_start_date AND paf.effective_end_date AND
paf.primary_flag = 'Y'
AND
past.assignment_status_type_id = paf.assignment_status_type_id AND
past.per_system_status = 'ACTIVE_ASSIGN';
SELECT hhc.id
FROM hxt_holiday_calendars hhc,
hxt_earning_policies hep,
hxt_add_assign_info_f haaif
WHERE p_effective_date BETWEEN hhc.effective_start_date
AND hhc.effective_end_date
AND hep.hcl_id = hhc.id
AND p_effective_date BETWEEN hep.effective_start_date
AND hep.effective_end_date
AND haaif.earning_policy = hep.id
AND p_effective_date BETWEEN haaif.effective_start_date
AND haaif.effective_end_date
AND haaif.assignment_id = p_assignment_id;
SELECT hhc.element_type_id
FROM hxt_holiday_calendars hhc
WHERE hhc.id = p_hol_cal_id;
SELECT 1
FROM hxt_holiday_calendars hhc, hxt_holiday_days hhd
WHERE hhc.id = p_hol_cal_id
AND hhc.id = hhd.hcl_id
AND hhd.holiday_date = p_day
AND hhd.hours >= 0;
SELECT pbg.legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_bg_id;
SELECT paf.business_group_id
FROM per_assignments_f paf
WHERE paf.assignment_id = p_asg_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT /*+ ORDERED
INDEX(paf PER_ASSIGNMENTS_F_PK)
INDEX(past PER_ASSIGNMENT_STATUS_TYPE_PK) */
/* Hints supplied to always force the correct execution plan */
1
FROM
per_assignments_f paf,
per_assignment_status_types past
WHERE
paf.assignment_id = p_asg_id AND
p_effective_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND
past.assignment_status_type_id = paf.assignment_status_type_id AND
( past.business_group_id = paf.business_group_id OR
past.business_group_id IS NULL) AND
( past.legislation_code IS NULL OR
past.legislation_code = hxt_batch_val.legislation_code(paf.business_group_id)) AND
past.per_system_status = 'ACTIVE_ASSIGN';
SELECT hhd.hours, hhc.element_type_id
FROM hxt_holiday_days hhd, hxt_holiday_calendars hhc
WHERE hhd.holiday_date = p_day
AND hhc.id = p_hol_cal_id
AND hhd.hcl_id = hhc.id;
SELECT SUM (hshwx.hours)
FROM hxt_sum_hours_worked_x hshwx
WHERE hshwx.tim_id = p_tim_id
AND hshwx.date_worked = p_day
AND ( (p_hours_type IS NULL)
OR ( (p_hours_type IS NOT NULL)
AND (p_hours_type = hshwx.element_type_id)
)
);
SELECT el.element_link_id
INTO l_element_link_id
FROM per_assignments_f asg, pay_element_links_f el
WHERE asg.assignment_id = p_assignment_id
AND el.business_group_id
+ 0 = asg.business_group_id
+ 0
AND el.element_type_id = p_element_type_id
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND p_effective_date BETWEEN el.effective_start_date
AND el.effective_end_date
AND ( ( el.payroll_id IS NOT NULL
AND el.payroll_id = asg.payroll_id
)
OR ( el.link_to_all_payrolls_flag = 'Y'
AND asg.payroll_id IS NOT NULL
)
OR ( el.payroll_id IS NULL
AND el.link_to_all_payrolls_flag = 'N'
)
)
AND ( el.job_id IS NULL
OR el.job_id = asg.job_id
)
AND ( el.grade_id IS NULL
OR el.grade_id = asg.grade_id
)
AND ( el.position_id IS NULL
OR el.position_id = asg.position_id
)
AND ( el.organization_id IS NULL
OR el.organization_id = asg.organization_id
)
AND ( el.location_id IS NULL
OR el.location_id = asg.location_id
)
AND ( el.pay_basis_id IS NULL
OR el.pay_basis_id = asg.pay_basis_id
)
AND ( el.employment_category IS NULL
OR el.employment_category = asg.employment_category
)
AND ( el.people_group_id IS NULL
OR EXISTS (
SELECT NULL
FROM pay_assignment_link_usages_f alu
WHERE alu.assignment_id = asg.assignment_id
AND alu.element_link_id = el.element_link_id
AND p_effective_date
BETWEEN alu.effective_start_date
AND alu.effective_end_date)
);
SELECT SUM (hdhwx.hours) hours, hdhwx.element_type_id,
haeif.earning_category
FROM hxt_det_hours_worked_x hdhwx, hxt_add_elem_info_f haeif
WHERE hdhwx.tim_id = p_tim_id
AND hdhwx.date_worked = p_date_worked
AND hdhwx.element_type_id = haeif.element_type_id
AND hdhwx.date_worked BETWEEN haeif.effective_start_date
AND haeif.effective_end_date
GROUP BY hdhwx.date_worked,
hdhwx.element_type_id,
haeif.earning_category;
SELECT DISTINCT hshwx.date_worked, hshwx.element_type_id,
hshwx.assignment_id
FROM hxt_sum_hours_worked_x hshwx
WHERE tim_id = p_tim_id
AND hshwx.effective_end_date = hr_general.end_of_time;
select 'Y'
from per_all_assignments_f p1,
hxt_timecards_x tim
where tim.effective_start_date between p1.effective_start_date and p1.effective_end_date
and tim.effective_end_date between p1.effective_start_date and p1.effective_end_date
and p1.person_id = p_person_id
and p1.primary_flag = 'Y'
and tim.id = p_tim_id;
hr_session_utilities.insert_session_row (SYSDATE);
SELECT tim.id tim_id, tim.for_person_id, tim.time_period_id,
tim.approv_person_id, tim.auto_gen_flag,
tim.approved_timestamp , tim.created_by,
tim.creation_date , tim.last_updated_by,
tim.last_update_date , tim.last_update_login,
tim.payroll_id , tim.status,
tim.effective_start_date , tim.effective_end_date,
tim.object_version_number, tim.rowid
FROM hxt_timecards_x tim
WHERE tim.batch_id = p_batch_id;
delete_prev_val_errors (p_batch_id => p_batch_id);
hr_session_utilities.insert_session_row (SYSDATE);
delete_prev_val_errors (p_tim_id => rec_tcs_in_batch.tim_id);
p_merge_batches(l_cnt).last_updated_by := rec_tcs_in_batch.last_updated_by;
p_merge_batches(l_cnt).last_update_date := rec_tcs_in_batch.last_update_date;
p_merge_batches(l_cnt).last_update_login := rec_tcs_in_batch.last_update_login;