[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR CSR1 IS SELECT opt.name /*coverage_type*/
FROM ben_opt_f opt,
ben_oipl_f cop
WHERE opt.opt_id = cop.opt_id
AND cop.oipl_id = P_OIPL_ID
AND cop.effective_start_date =
(SELECT max(cop1.effective_start_date)
FROM ben_oipl_f cop1
WHERE cop1.oipl_id = cop.oipl_id
AND cop1.effective_start_date <= hr_pay_interface_pkg.get_extract_date)
AND opt.effective_start_date =
(SELECT max(opt1.effective_start_date)
FROM ben_opt_f opt1
WHERE opt1.opt_id = opt.opt_id
AND opt1.effective_start_date <= hr_pay_interface_pkg.get_extract_date);
PROCEDURE select_rec(p_prtt_enrt_rslt_id IN
ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE) IS
CURSOR csr_pay_interface_oab_value_v(c_acty_typ_cd
ben_prtt_rt_val.acty_typ_cd%TYPE) IS
SELECT pi.*
FROM hr_pay_interface_oab_value_v pi
WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pi.acty_typ_cd = c_acty_typ_cd
AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
END select_rec;
SELECT 1
FROM hr_pay_interface_oab_value_v pi
WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pi.acty_typ_cd IN ('EEPYC','ERPYC')
AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
RETURN(g_eepyc_rec.OABV_LAST_UPDATE_DATE);
select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
RETURN(g_erpyc_rec.OABV_LAST_UPDATE_DATE);
PROCEDURE select_split_eepyc_rec(p_prtt_enrt_rslt_id IN
ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE) IS
CURSOR csr_pay_interface_oab_value_v IS
SELECT pi.*
FROM hr_pay_interface_oab_value1_v pi
WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pi.acty_typ_cd = 'EEPYC'
AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
END select_split_eepyc_rec;
PROCEDURE select_split_erpyc_rec(p_prtt_enrt_rslt_id
IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE) IS
CURSOR csr_pay_interface_oab_value_v IS
SELECT pi.*
FROM hr_pay_interface_oab_value2_v pi
WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pi.acty_typ_cd = 'ERPYC'
AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
END select_split_erpyc_rec;
SELECT 1
FROM hr_pay_interface_oab_value1_v pi
WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pi.acty_typ_cd = 'EEPYC'
AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
SELECT 1
FROM hr_pay_interface_oab_value2_v pi
WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pi.acty_typ_cd = 'ERPYC'
AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
select_split_eepyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_split_eepyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_split_eepyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
RETURN(g_split_eepyc_rec.OABV_LAST_UPDATE_DATE);
select_split_erpyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_split_erpyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
select_split_erpyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
RETURN(g_split_erpyc_rec.OABV_LAST_UPDATE_DATE);
PROCEDURE disable_ele_entry_delete
---------------------------------------------------------------
IS
--
-- This procedure returns an error if an attempt is being made to
-- perform a Datetrack purge of an element entry. Certain types of Datetrack
-- delete are allowable, so we cannot simply prevent all deletes against
-- pay_element_entries_f.
--
-- If we try to access pay_element_entries_f from a delete trigger on
-- the same table, we hit a mutating table error. So we must perform
-- the delete check against a different table to that against which the
-- delete trigger is created.
--
-- We are making use of the validation rule that states that each
-- element entry must have at least one entry value. Within a commit unit,
-- rows are deleted from pay_element_entries_f before they are deleted from
-- pay_element_entry_values_f.
--
-- So the delete trigger is created against pay_element_entry_values_f, and
-- this procedure looks at pay_element_entries_f. The user is attempting a
-- Datetrack purge if there are no parent element entries that match
-- the element entry value that is being deleted.
--
-- This procedure uses the global g_ele_start_date to verify if the
-- element entry is current or futured dated.
--
-- This procedure uses the global g_ele_entry_id and g_ele_link_id
-- which are assigned in the set_ele_var_ids procedure.
-- The second part of the validation checks the link from the deleted element
-- entry to ensure that the element type does not contain either Y,E or D in
-- the attribute1 column. If it does then an error is raised.
-- Attribute1 in pay_element_types_f is from this point
-- to be used as a flag to identify that it is a deduction element
--
dummy integer;
SELECT COUNT(element_entry_id)
INTO dummy
FROM pay_element_entries_f
WHERE element_entry_id = g_ele_entry_id;
SELECT pt.attribute1
INTO l_attrib1
FROM pay_element_types_f pt,
pay_element_links_f pl
WHERE
pl.element_link_id = g_ele_link_id AND
pt.element_type_id = pl.element_type_id AND
pt.effective_start_date = (SELECT max(pt2.effective_start_date)
FROM pay_element_types_f pt2
WHERE
(pt2.effective_start_date <=
TRUNC(sysdate))
AND
(pt2.element_type_id =
pt.element_type_id)) AND
pl.effective_start_date = (SELECT max(pt2.effective_start_date)
FROM pay_element_links_f pt2
WHERE
(pt2.effective_start_date <=
TRUNC(sysdate))
AND
(pt2.element_link_id =
pl.element_link_id));
hr_utility.set_message (800, 'PER_ELE_ENTRY_DISABLE_DELETE');
END disable_ele_entry_delete;
procedure disable_emp_number_update (p_old_emp_number varchar2 default null,
p_new_emp_number varchar2 default null)
---------------------------------------------------------------
is
-- This procedure returns an error if an attempt is being made to
-- update an employee number.
-- A change in employee number would result in the
-- creation of a new employee record in the vendors payroll.
--
begin
--
if p_old_emp_number is not null
and p_new_emp_number is not null
and p_old_emp_number <> p_new_emp_number then
hr_utility.set_message (800, 'PER_EMP_NUMBER_DISABLE_UPDATE');
end disable_emp_number_update;
SELECT 1
FROM pay_element_types_f et
WHERE et.business_group_id
= hr_pay_interface_pkg.g_reporting_details_rec_var.business_group_id
AND (NVL(et.legislation_code,-99)
= NVL(hr_pay_interface_pkg.g_reporting_details_rec_var.legislation_code,-99))
AND UPPER(et.reporting_name)
= UPPER(hr_pay_interface_pkg.g_reporting_details_rec_var.reporting_name)
AND (et.element_type_id
<> hr_pay_interface_pkg.g_reporting_details_rec_var.element_type_id
OR hr_pay_interface_pkg.g_reporting_details_rec_var.element_type_id ='')
-- Have to ensure the reporting name doesn't exist on date-tracked
-- rows (even though reporting_name isn't datetracked, it still is
-- possible to achieve by altering a datetrack row at the same time)
AND (hr_pay_interface_pkg.g_reporting_details_rec_var.effective_start_date
between et.effective_start_date and et.effective_end_date
OR hr_pay_interface_pkg.g_reporting_details_rec_var.effective_end_date
between et.effective_start_date and et.effective_end_date
OR (hr_pay_interface_pkg.g_reporting_details_rec_var.effective_start_date
< et.effective_start_date
AND hr_pay_interface_pkg.g_reporting_details_rec_var.effective_end_date
> et.effective_end_date)
);
select decode(pliv.default_value,
null,
piv.default_value,
pliv.default_value) screen_entry_value
into hot_default_value
from pay_input_values_f piv,
pay_link_input_values_f pliv
where pliv.element_link_id = p_element_link_id
and pliv.effective_start_date =
(select max(pliv2.effective_start_date)
from pay_link_input_values_f pliv2
where pliv2.effective_start_date <= trunc(sysdate)
and pliv2.element_link_id = p_element_link_id
and pliv2.input_value_id = p_input_value_id)
and pliv.input_value_id = p_input_value_id
and piv.effective_start_date =
(select max(piv2.effective_start_date)
from pay_input_values_f piv2
where piv2.effective_start_date <= trunc(sysdate)
and piv2.input_value_id = p_input_value_id
and hot_default_flag = 'Y')
and piv.input_value_id = p_input_value_id
and piv.hot_default_flag = 'Y';
procedure disable_ppm_update (p_old_priority varchar2 default null,
p_new_priority varchar2 default null)
-------------------------------------------------------------------
is
-- This procedure returns an error if an attempt is being made to
-- update personal payment method priority.
-- A change in priority would result in the
-- creation of a new EFT record in the third party payroll system.
--
begin
--
if p_old_priority is not null
and p_new_priority is not null
and p_old_priority <> p_new_priority then
hr_utility.set_message (800, 'PER_PPM_PRI_DISABLE_UPDATE');
end disable_ppm_update;
procedure disable_ppm_delete_purge
---------------------------------------------------------------------
is
-- This procedure returns an error if an attempt is being made to
-- delete a personal payment method.
--
CURSOR csr_ppm_delete_purge is
select 1
from pay_personal_payment_methods_f
where personal_payment_method_id =
HR_PAY_INTERFACE_PKG.g_personal_payment_method_id;
SELECT 1
FROM per_periods_of_service pps,
per_all_assignments_f paa
WHERE
HR_PAY_INTERFACE_PKG.g_ppm_ass_id = paa.assignment_id
AND paa.person_id = pps.person_id
AND ( pps.final_process_date IS NOT NULL
OR pps.last_standard_process_date IS NOT NULL)
AND HR_PAY_INTERFACE_PKG.g_ppm_start_date > trunc(SYSDATE);
open csr_ppm_delete_purge;
fetch csr_ppm_delete_purge into l_purge;
close csr_ppm_delete_purge;
end disable_ppm_delete_purge;
procedure disable_asg_cost_delete_purge
---------------------------------------------------------------------
is
--
-- This procedure returns an error if an attempt is being made to
-- delete an assignment costing.
--
CURSOR csr_asg_cost_delete_purge is
select 1
from pay_cost_allocations_f
where cost_allocation_id =
HR_PAY_INTERFACE_PKG.g_cost_allocation_id;
SELECT 1
FROM per_periods_of_service pps,
per_all_assignments_f paa
WHERE
HR_PAY_INTERFACE_PKG.g_asg_cost_ass_id = paa.assignment_id
AND paa.person_id = pps.person_id
AND (pps.final_process_date IS NOT NULL
OR pps.last_standard_process_date IS NOT NULL)
AND HR_PAY_INTERFACE_PKG.g_asg_cost_start_date > trunc(SYSDATE);
open csr_asg_cost_delete_purge;
fetch csr_asg_cost_delete_purge into l_purge;
close csr_asg_cost_delete_purge;
end disable_asg_cost_delete_purge;