[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
16-Oct-02 dcasemor 115.12 2628433 Added delete_plan_from_cache and
use_fast_formula. These remove the dependency
on the "Use FF-based PTO Accruals" profile
option.
14-DEC-05 risgupta 115.14 4761039 passing -1 into l_assignment_action_id as net
entitlement during archival process is not getting
returned correctly,hence discrepancy in SOE and
payslip.
14-DEC-05 risgupta 115.15 CHANGES DONE in VERSION 115.14 UNDONE.
06-NOV-12 lbodired 115.16 modified the procedure 'accrual_calc_detail'
*/
--
-- Private PL/SQL table to cache a list of accrual plans.
--
TYPE per_plans IS TABLE OF BOOLEAN INDEX BY binary_integer;
PROCEDURE delete_plan_from_cache (p_plan_id IN NUMBER)
IS
BEGIN
IF g_plan_list.exists(p_plan_id) THEN
--
-- Delete the plan from the cache.
--
g_plan_list.DELETE(p_plan_id);
END delete_plan_from_cache;
SELECT NULL
FROM pay_accrual_plans pap
,ff_formulas_f ff
WHERE pap.accrual_plan_id = p_plan_id
AND pap.accrual_formula_id = ff.formula_id
AND p_effective_date BETWEEN
ff.effective_start_date and ff.effective_end_date
AND (ff.formula_name = 'PTO_PAYROLL_CALCULATION'
OR (ff.formula_name = 'PTO_PAYROLL_BALANCE_CALCULATION' AND
pap.defined_balance_id IS NULL));
select pap.accrual_plan_id,
pap.accrual_plan_element_type_id,
pap.accrual_units_of_measure,
pap.ineligible_period_type,
pap.ineligible_period_length,
pap.accrual_start,
pev.SCREEN_ENTRY_VALUE,
pee.element_entry_id
from pay_accrual_plans pap,
pay_element_entry_values_f pev,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_input_values_f piv
where ( pap.accrual_plan_id = p_plan_id OR
pap.accrual_category = P_plan_category )
and pap.business_group_id + 0 = P_business_group
and pap.accrual_plan_element_type_id = pet.element_type_id
and P_calculation_date between pet.effective_start_date and
pet.effective_end_date
and pet.element_type_id = pel.element_type_id
and P_calculation_date between pel.effective_start_date and
pel.effective_end_date
and pel.element_link_id = pee.element_link_id
and pee.assignment_id = P_assignment_id
and P_calculation_date between pee.effective_start_date and
pee.effective_end_date
and piv.element_type_id =
pap.accrual_plan_element_type_id
and piv.name = 'Continuous Service Date'
and P_calculation_date between piv.effective_start_date and
piv.effective_end_date
and pev.element_entry_id = pee.element_entry_id
and pev.input_value_id + 0 = piv.input_value_id
and P_calculation_date between pev.effective_start_date and
pev.effective_end_date;
SELECT number_per_fiscal_year
INTO l_number_of_period
FROM per_time_period_types TPT,
pay_payrolls_f PPF
WHERE TPT.period_type = PPF.period_type
AND PPF.payroll_id = P_payroll_id
AND l_calc_end_date BETWEEN PPF.effective_start_date
AND PPF.effective_end_date;
select min (pee.effective_start_date) into l_plan_enrl_date
from pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
where pel.element_link_id = pee.element_link_id
and pel.element_type_id = pet.element_type_id
and pee.assignment_id = p_assignment_id
and pet.element_type_id = (select accrual_plan_element_type_id
from pay_accrual_plans
where accrual_plan_id = p_plan_id)
and p_calculation_date between pee.effective_start_date
and pee.effective_end_date;
select min(effective_start_date)
into l_plan_start_date
from pay_element_entries_f
where element_entry_id = l_element_entry_id;
select a.effective_start_date,
a.effective_end_date,
b.PER_SYSTEM_STATUS
from per_assignments_f a,
per_assignment_status_types b
where a.assignment_id = P_assignment_id
and a.effective_end_date between p_first_p_start_date and
to_date('31-12-4712','DD-MM-YYYY')
and a.ASSIGNMENT_STATUS_TYPE_ID =
b.ASSIGNMENT_STATUS_TYPE_ID;
select annual_rate,
ceiling,
lower_limit,
upper_limit,
max_carry_over
from pay_accrual_bands
where accrual_plan_id = P_plan_id
and P_time_worked >= lower_limit
and P_time_worked < upper_limit;
select start_date,
end_date,
period_num
from per_time_periods
where to_char(end_date,'YYYY') =
to_char(p_accrual_calc_p_end_date,'YYYY')
and end_date <= p_accrual_calc_p_end_date
and period_num >=
decode (to_char(p_first_p_start_date,'YYYY'),
to_char(p_accrual_calc_p_end_date,'YYYY'),
p_first_calc_P_number, 1)
and payroll_id = p_payroll_id
ORDER by period_num;
select business_group_id,
payroll_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_calculation_date between effective_start_date
and effective_end_date;