1 package pay_us_pto_accrual as
2 /* $Header: pyusptoa.pkh 120.1 2005/10/04 02:32:23 schauhan noship $ */
3 --
4 /*
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1993 Oracle Corporation. *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation, *
18 * 500 Oracle Parkway, Redwood City, CA, 94065. *
19 * *
20 ******************************************************************
21
22 Name : pay_us_pto_accrual
23 Description : This package holds building blocks used in PTO accrual
24 calculation.
25
26 Uses : hr_utility
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ---- ---- ---- ------ -----------
32 FEB-16-1994 RMAMGAIN 1.0 Created with following proc.
33 . get_accrual
34 . get_accrual_for_plan
35 . get_first_accrual_period
36 . ceiling_calc
37 OCT-16-2002 DCASEMOR 115.3 2377873 Added delete_plan_from_cache.
38 OCT-04-2005 SCHAUHAN 115.4 Added nocopy hint to OUT and IN OUT
39 parameters.
40
41 */
42 --
43 --
44 -- Global Variable
45 --
46 PROCEDURE delete_plan_from_cache
47 (p_plan_id IN NUMBER);
48 --
49 --
50 FUNCTION get_accrual
51 ( P_assignment_id number,
52 P_calculation_date date,
53 P_plan_id number DEFAULT NULL,
54 P_plan_category varchar2 DEFAULT NULL)
55 RETURN Number;
56 --
57 --
58 PROCEDURE accrual_calc_detail
59 (P_assignment_id IN number,
60 P_calculation_date IN OUT nocopy date,
61 P_plan_id IN number DEFAULT NULL,
62 P_plan_category IN varchar2 DEFAULT NULL,
63 P_mode IN varchar2 DEFAULT 'N',
64 P_accrual OUT nocopy number,
65 P_payroll_id IN OUT nocopy number,
66 P_first_period_start IN OUT nocopy date,
67 P_first_period_end IN OUT nocopy date,
68 P_last_period_start IN OUT nocopy date,
69 P_last_period_end IN OUT nocopy date,
70 P_cont_service_date OUT nocopy date,
71 P_start_date OUT nocopy date,
72 P_end_date OUT nocopy date,
73 P_current_ceiling OUT nocopy number,
74 P_current_carry_over OUT nocopy number);
75 --
76 --
77 PROCEDURE get_accrual_for_plan
78 ( p_plan_id Number,
79 p_first_p_start_date date,
80 p_first_p_end_date date,
81 p_first_calc_P_number number,
82 p_accrual_calc_p_end_date date,
83 P_accrual_calc_P_number number,
84 P_number_of_periods number,
85 P_payroll_id number,
86 P_assignment_id number,
87 P_plan_ele_type_id number,
88 P_continuous_service_date date,
89 P_Plan_accrual OUT nocopy number,
90 P_current_ceiling OUT nocopy number,
91 P_current_carry_over OUT nocopy number );
92 --
93 --
94 FUNCTION get_working_days
95 ( P_start_date date,
96 P_end_date date )
97 RETURN NUMBER;
98 --
99 --
100 FUNCTION get_net_accrual
101 ( P_assignment_id number,
102 P_calculation_date date,
103 P_plan_id number default null,
104 P_plan_category Varchar2 default null,
105 P_assignment_action_id number default null)
106 RETURN NUMBER;
107 --
108 --
109 PROCEDURE net_accruals
110 (P_assignment_id IN number,
111 P_calculation_date IN OUT nocopy date,
112 P_plan_id IN number DEFAULT NULL,
113 P_plan_category IN varchar2 DEFAULT NULL,
114 P_mode IN varchar2 DEFAULT 'N',
115 P_accrual IN OUT nocopy number,
116 P_net_accrual OUT nocopy number,
117 P_payroll_id IN OUT nocopy number,
118 P_first_period_start IN OUT nocopy date,
119 P_first_period_end IN OUT nocopy date,
120 P_last_period_start IN OUT nocopy date,
121 P_last_period_end IN OUT nocopy date,
122 P_cont_service_date OUT nocopy date,
123 P_start_date IN OUT nocopy date,
124 P_end_date IN OUT nocopy date,
125 P_current_ceiling OUT nocopy number,
126 P_current_carry_over OUT nocopy number);
127 --
128 -- Define global cursors which will be shared by different functions in
129 -- this package.
130 --
131 CURSOR csr_get_payroll (P_assignment_id number,
132 P_calculation_date date ) IS
133 select a.payroll_id,
134 a.effective_start_date,
135 a.effective_end_date,
136 a.business_group_id,
137 b.DATE_START,
138 b.ACTUAL_TERMINATION_DATE
139 from PER_ASSIGNMENTS_F a,
140 PER_PERIODS_OF_SERVICE b
141 where a.assignment_id = P_assignment_id
142 and P_calculation_date between a.effective_start_date and
143 a.effective_end_date
144 and a.PERIOD_OF_SERVICE_ID = b.PERIOD_OF_SERVICE_ID;
145 --
146 --
147 CURSOR csr_get_period (p_payroll_id number,
148 p_effective_date date ) is
149 select PERIOD_NUM,
150 START_DATE,
151 END_DATE
152 from PER_TIME_PERIODS
153 where PAYROLL_ID = p_payroll_id
154 and p_effective_date between START_DATE and END_DATE;
155 --
156 -- Lwthomps, disabled indexes for performance problems
157 --
158 CURSOR csr_calc_accrual (P_start_date date,
159 P_end_date date,
160 P_assignment_id number,
161 P_plan_id number ) IS
162 select sum(to_number(nvl(pev.SCREEN_ENTRY_VALUE,'0')) *
163 to_number(pnc.add_or_subtract))
164 from pay_net_calculation_rules pnc,
165 pay_element_entry_values_f pev,
166 pay_element_entries_f pee
167 where pnc.accrual_plan_id = p_plan_id
168 and pnc.input_value_id = pev.input_value_id + 0
169 and pev.element_entry_id = pee.element_entry_id
170 and pee.assignment_id = P_assignment_id
171 and pee.effective_start_date between P_start_date and
172 P_end_date;
173 --
174 --
175 CURSOR csr_get_total_periods ( p_payroll_id number,
176 p_date date ) is
177 select min(start_date),
178 min(end_date),
179 max(start_date),
180 max(end_date),
181 count(period_num)
182 from per_time_periods
183 where payroll_id = p_payroll_id
184 and to_char(P_date,'YYYY') = to_char(end_date,'YYYY');
185 --
186 --
187 END pay_us_pto_accrual;
[Home] [Help]