[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
20-SEP-05 ghshanka 115.11 bug 4123194 deleted the functions
calc_accrual_pay and accrual_time_taken .
29-Nov-05 irgonzal 115.12 Bug fix 4762608. Altered
get_accrual_ineligibility procedure.
Handled scenario when accrual plan
does not have a "start date" rule.
31-AUG-06 risgupta 115.13 5405255 obsoleted functions being re-added on request
of US payroll
Description: User-Defined Functions required for US implementations.
*/
--
-- **********************************************************************
--
-- Procedure
-- get_accrual_ineligibility
--
-- Purpose
-- Check for accrual plan ineligibility period and indicate if the current
-- assignment is within the ineligible period - ie. the batch line entry
-- for time taken against the accrual should be invalidated.
--
-- Arguments
-- p_iv_id
-- p_bg_id
-- p_asg_id
-- p_sess_date
--
-- History
-- 8th July 1995 Hankins Parichabutr Created.
-- 4th Oct 1995 Ranjana Murthy Added NO_DATA_FOUND exception
-- 05-Jan-96 rfine 323214 Prevented TOO_MANY_ROWS error when
-- > 1 Plan has the same absence element
-- 11-JAN-96 ramurthy 326766 Set eligible dates properly for
-- start rules 'Hire Date' and
-- 'Beginning of Year'.
--
PROCEDURE get_accrual_ineligibility( p_iv_id IN NUMBER,
p_bg_id IN NUMBER,
p_asg_id IN NUMBER,
p_sess_date IN DATE,
p_eligible OUT NOCOPY VARCHAR2
) IS
v_inel_length NUMBER(2);
SELECT nvl(ineligible_period_length, 0),
ineligible_period_type,
accrual_start,
accrual_plan_id,
ineligibility_formula_id
FROM PAY_ACCRUAL_PLANS
WHERE pto_input_value_id = p_iv_id
AND business_group_id = p_bg_id;
SELECT pps.date_start
INTO v_service_start
FROM per_periods_of_service pps,
per_assignments_f paf
WHERE paf.assignment_id = p_asg_id
AND p_sess_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pps.person_id = paf.person_id
AND paf.business_group_id = p_bg_id
AND p_sess_date BETWEEN pps.date_start AND pps.final_process_date;
select fnd_number.canonical_to_number(pev.screen_entry_value)
from pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pap.accrual_category = 'V'
and pap.business_group_id = p_bg_id
and pev.input_value_id = pap.pto_input_value_id
and p_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = p_asg_id
and p_eff_date between pee.effective_start_date
and pee.effective_end_date;
select fnd_number.canonical_to_number(pev.screen_entry_value)
from pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pap.accrual_category = 'S'
and pap.business_group_id = p_bg_id
and pev.input_value_id = pap.pto_input_value_id
and p_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = p_asg_id
and p_eff_date between pee.effective_start_date
and pee.effective_end_date;
select sum(fnd_number.canonical_to_number(pev.screen_entry_value))
into l_hours_taken
from pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pap.accrual_category = p_mode
and pap.business_group_id = p_bg_id
and pev.input_value_id = pap.pto_input_value_id
and p_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = p_asg_id
and p_eff_date between pee.effective_start_date
and pee.effective_end_date;