[Home] [Help]
PACKAGE BODY: APPS.HR_PTO_VIEWS
Source
1 PACKAGE BODY HR_PTO_VIEWS AS
2 /* $Header: hrptovws.pkb 120.0 2005/05/31 02:22 appldev noship $ */
3 --
4 -- Package global variables
5 --
6 --
7 cursor csr_get_asg_details(cp_assignment_id number, cp_effective_date date) is
8 select business_group_id, payroll_id
9 from per_all_assignments_f
10 where assignment_id = cp_assignment_id
11 and cp_effective_date between effective_start_date and effective_end_date;
12 --
13 cursor csr_get_aaid_details(cp_assignment_action_id number) is
14 select paa.assignment_id, ppa.business_group_id, ppa.payroll_id
15 from pay_assignment_actions paa
16 ,pay_payroll_actions ppa
17 where paa.assignment_action_id = cp_assignment_action_id
18 and paa.payroll_action_id = ppa.payroll_action_id;
19 --
20 -- ---------------------------------------------------------------------- +
21 -- ---------------------------------------------------------------------- +
22 -- This includes c/o + other net contribution
23 --
24 PROCEDURE Get_pto_ytd_net_entitlement
25 (p_assignment_id number
26 ,p_plan_id number
27 ,p_payroll_id number
28 ,p_business_group_id number
29 ,p_assignment_action_id number
30 ,p_calculation_date date
31 ,p_net_entitlement OUT nocopy number
32 ,p_last_accrual_date OUT nocopy date) IS
33 --
34 l_entitlement number;
35 l_assignment_action_id number;
36 d1 date;
37 d2 date;
38 d3 date;
39 n1 number;
40
41 BEGIN
42 -- Here we set a null assignment_action_id to -1 to prevent
43 -- an error running the Accrual formula later.
44
45 if p_assignment_action_id is null then
46 l_assignment_action_id := -1;
47 else
48 l_assignment_action_id := p_assignment_action_id;
49 end if;
50 --
51 per_accrual_calc_functions.get_net_accrual(
52 P_assignment_id => p_assignment_id,
53 P_plan_id => p_plan_id,
54 P_payroll_id => p_payroll_id,
55 p_business_group_id => p_business_group_id,
56 p_assignment_action_id => l_assignment_action_id,
57 P_calculation_date => p_calculation_date,
58 p_accrual_start_date => null,
59 p_accrual_latest_balance => null,
60 p_calling_point => 'BP',
61 P_start_date => d1,
62 P_End_Date => d2,
63 P_Accrual_End_Date => d3,
64 P_accrual => n1,
65 P_net_entitlement => l_entitlement
66 );
67 -- set out prms
68 p_net_entitlement := l_entitlement;
69 p_last_accrual_date := d3;
70 --
71 END Get_pto_ytd_net_entitlement;
72 -- ---------------------------------------------------------------------- +
73 -- ---------------------------------------------------------------------- +
74 PROCEDURE Get_pto_ytd_net_entitlement(
75 p_assignment_id number
76 ,p_plan_id number
77 ,p_calculation_date date
78 ,p_net_entitlement OUT nocopy number
79 ,p_last_accrual_date OUT nocopy date) IS
80 --
81 l_asg_rec csr_get_asg_details%ROWTYPE;
82 l_result number;
83 --
84 BEGIN
85 open csr_get_asg_details(p_assignment_id,p_calculation_date) ;
86 fetch csr_get_asg_details into l_asg_rec;
87 close csr_get_asg_details;
88 Get_pto_ytd_net_entitlement(
89 p_assignment_id => p_assignment_id
90 ,p_plan_id => p_plan_id
91 ,p_payroll_id => l_asg_rec.payroll_id
92 ,p_business_group_id => l_asg_rec.business_group_id
93 ,p_assignment_action_id => -1
94 ,p_calculation_date => p_calculation_date
95 ,p_net_entitlement => p_net_entitlement
96 ,p_last_accrual_date => p_last_accrual_date);
97 --
98 END Get_pto_ytd_net_entitlement;
99 --
100 -- ---------------------------------------------------------------------- +
101 -- --------------------<< Get_pto_ytd_gross >>--------------------------- +
102 -- ---------------------------------------------------------------------- +
103 -- similar to latest balance but it does not consider the other net contrib
104 PROCEDURE Get_pto_ytd_gross(p_assignment_id number
105 ,p_plan_id number
106 ,p_payroll_id number
107 ,p_business_group_id number
108 ,p_assignment_action_id number
109 ,p_calculation_date date
110 ,p_gross_accruals OUT nocopy number
111 ,p_last_accrual_date OUT nocopy date) IS
112 --
113 l_tot_accrual_hours number;
114 l_assignment_action_id number;
115 ln_dummy_num number;
116 ld_dummy_dat date;
117 ld_dummy_dat1 date;
118 ld_dummy_dat2 date;
119
120 BEGIN
121
122 if p_assignment_action_id is null then
123 l_assignment_action_id := -1;
124 else
125 l_assignment_action_id := p_assignment_action_id;
126 end if;
127
128 per_accrual_calc_functions.get_net_accrual (
129 p_assignment_id => p_assignment_id,
130 p_plan_id => p_plan_id,
131 p_payroll_id => p_payroll_id,
132 p_business_group_id => p_business_group_id,
133 p_calculation_date => p_calculation_date,
134 p_assignment_action_id => l_assignment_action_id,
135 p_accrual => l_tot_accrual_hours, -- return this value
136 p_net_entitlement => ln_dummy_num,
137 p_end_date => ld_dummy_dat,
138 p_accrual_end_date => ld_dummy_dat1,
139 p_start_date => ld_dummy_dat2
140 );
141 -- set OUT prms
142 p_gross_accruals := l_tot_accrual_hours;
143 p_last_accrual_date := ld_dummy_dat1;
144 --
145 END Get_pto_ytd_gross;
146 -- ---------------------------------------------------------------------- +
147 -- ---------------------------------------------------------------------- +
148 PROCEDURE Get_pto_ytd_gross(
149 p_assignment_id number
150 ,p_plan_id number
151 ,p_calculation_date date
152 ,p_gross_accruals OUT nocopy number
153 ,p_last_accrual_date OUT nocopy date) IS
154 --
155 l_asg_rec csr_get_asg_details%ROWTYPE;
156 l_result number;
157 --
158 BEGIN
159 open csr_get_asg_details(p_assignment_id, p_calculation_date);
160 fetch csr_get_asg_details into l_asg_rec;
161 close csr_get_asg_details;
162 Get_pto_ytd_gross(
163 p_assignment_id => p_assignment_id
164 ,p_plan_id => p_plan_id
165 ,p_payroll_id => l_asg_rec.payroll_id
166 ,p_business_group_id => l_asg_rec.business_group_id
167 ,p_assignment_action_id => -1
168 ,p_calculation_date => p_calculation_date
169 ,p_gross_accruals => p_gross_accruals
170 ,p_last_accrual_date => p_last_accrual_date);
171 --
172 END Get_pto_ytd_gross;
173 --
174 -- ---------------------------------------------------------------------- +
175 -- ---------------------<< Get_pto_ptd_gross >>-------------------------- +
176 -- ---------------------------------------------------------------------- +
177 PROCEDURE Get_pto_ptd_gross(p_assignment_id number
178 ,p_plan_id number
179 ,p_payroll_id number
180 ,p_business_group_id number
181 ,p_assignment_action_id number
182 ,p_calculation_date date
183 ,p_gross_accruals OUT nocopy number
184 ,p_last_accrual_date OUT nocopy date) IS
185 --
186 l_entitlement number;
187 l_assignment_action_id number;
188 l_latest_balance number;
189 l_previous_balance number;
190 acc_end_date date;
191 d1 date;
192 d2 date;
193 d3 date;
194 n1 number;
195
196 BEGIN
197 l_latest_balance := 0;
198 --
199 if p_assignment_action_id is null then
200 l_assignment_action_id := -1;
201 else
202 l_assignment_action_id := p_assignment_action_id;
203 end if;
204 --
205 per_accrual_calc_functions.get_net_accrual(
206 P_assignment_id => p_assignment_id,
207 P_plan_id => p_plan_id,
208 P_payroll_id => p_payroll_id,
209 p_business_group_id => p_business_group_id,
210 p_assignment_action_id => l_assignment_action_id,
211 P_calculation_date => p_calculation_date,
212 p_accrual_start_date => null,
213 p_accrual_latest_balance => null,
214 p_calling_point => 'BP',
215 P_start_date => d1,
216 P_End_Date => d2,
217 P_Accrual_End_Date => d3,
218 P_accrual => l_latest_balance,
219 P_net_entitlement => n1
220 );
221 --
222 acc_end_date := d3; -- this identifies the date when last accrual was calculated
223 -- as of calculation date
224 per_accrual_calc_functions.get_net_accrual(
225 P_assignment_id => p_assignment_id,
226 P_plan_id => p_plan_id,
227 P_payroll_id => p_payroll_id,
228 p_business_group_id => p_business_group_id,
229 p_assignment_action_id => l_assignment_action_id,
230 P_calculation_date => acc_end_date -1 , -- this will calculate as of previous period
231 p_accrual_start_date => null,
232 p_accrual_latest_balance => null,
233 p_calling_point => 'BP',
234 P_start_date => d1,
235 P_End_Date => d2,
236 P_Accrual_End_Date => d3,
237 p_accrual => l_previous_balance,
238 P_net_entitlement => n1
239 );
240 -- set OUT prms
241 p_gross_accruals := l_latest_balance - l_previous_balance;
242 p_last_accrual_date := acc_end_date;
243 --
244 END Get_pto_ptd_gross;
245 -- ---------------------------------------------------------------------- +
246 -- ---------------------------------------------------------------------- +
247 PROCEDURE Get_pto_ptd_gross
248 (p_assignment_id number
249 ,p_plan_id number
250 ,p_calculation_date date
251 ,p_gross_accruals OUT nocopy number
252 ,p_last_accrual_date OUT nocopy date) IS
253 --
254 l_asg_rec csr_get_asg_details%ROWTYPE;
255 l_result number;
256 --
257 BEGIN
258 open csr_get_asg_details(p_assignment_id,p_calculation_date) ;
259 fetch csr_get_asg_details into l_asg_rec;
260 close csr_get_asg_details;
261 Get_pto_ptd_gross(
262 p_assignment_id => p_assignment_id
263 ,p_plan_id => p_plan_id
264 ,p_payroll_id => l_asg_rec.payroll_id
265 ,p_business_group_id => l_asg_rec.business_group_id
266 ,p_assignment_action_id => -1
267 ,p_calculation_date => p_calculation_date
268 ,p_gross_accruals => p_gross_accruals
269 ,p_last_accrual_date => p_last_accrual_date);
270 --
271 END Get_pto_ptd_gross;
272 --
273 -- ---------------------------------------------------------------------- +
274 -- Get_pto_all_plans
275 -- ---------------------------------------------------------------------- +
276 FUNCTION Get_pto_all_plans(
277 p_person_id number
278 ,p_calculation_date date) RETURN g_per_acc_plan_tab_type IS
279 --
280 CURSOR csr_get_plans(cp_person_id number, cp_effective_date date) IS
281 select rownum, et.element_name, paf.assignment_id, plan.accrual_plan_id
282 ,plan.accrual_plan_name, lookup.meaning UOM
283 ,paf.business_group_id, paf.payroll_id
284 ,ee.element_entry_id, ee.effective_start_date ee_start_date
285 from pay_element_entries_f ee
286 ,pay_element_types_f et
287 ,pay_accrual_plans plan
288 ,per_all_assignments_f paf
289 ,per_all_people_f peo
290 ,hr_lookups lookup
291 where peo.person_id = cp_person_id
292 and cp_effective_date between peo.effective_start_date
293 and peo.effective_end_date
294 and peo.person_id = paf.person_id
295 and paf.effective_start_date between peo.effective_start_date
296 and peo.effective_end_date
297 and ee.assignment_id = paf.assignment_id
298 and ee.element_type_id = plan.ACCRUAL_PLAN_ELEMENT_TYPE_ID
299 and ee.element_type_id = et.element_type_id
300 and ee.effective_start_date between et.effective_start_date
301 and et.effective_end_date
302 and lookup.lookup_type = 'HOURS_OR_DAYS'
303 and plan.ACCRUAL_UNITS_OF_MEASURE = lookup_code
304 and lookup.enabled_flag = 'Y';
305 --
306 l_person_plans HR_PTO_VIEWS.g_per_acc_plan_tab_type;
307 l_last_accrual_date date;
308 --
309 BEGIN
310 for plans_rec in csr_get_plans(p_person_id, p_calculation_date) loop
311 l_person_plans(plans_rec.rownum).plan_id := plans_rec.accrual_plan_id;
312 l_person_plans(plans_rec.rownum).plan_name := plans_rec.accrual_plan_name;
313 l_person_plans(plans_rec.rownum).UOM := plans_rec.UOM;
314 l_person_plans(plans_rec.rownum).assignment_id := plans_rec.assignment_id;
315 l_person_plans(plans_rec.rownum).plan_element_entry_id := plans_rec.element_entry_id;
316 l_person_plans(plans_rec.rownum).ee_start_date := plans_rec.ee_start_date;
317 HR_PTO_VIEWS.Get_pto_ytd_net_entitlement
318 (p_assignment_id => plans_rec.assignment_id
319 ,p_plan_id => plans_rec.accrual_plan_id
320 ,p_payroll_id => plans_rec.Payroll_id
321 ,p_business_group_id => plans_rec.business_group_id
322 ,p_assignment_action_id => null
323 ,p_calculation_date => p_calculation_date
324 ,p_net_entitlement => l_person_plans(plans_rec.rownum).net_entitlement_ytd
325 ,p_last_accrual_date => l_last_accrual_date);
326 --
327 HR_PTO_VIEWS.Get_pto_ytd_gross
328 (p_assignment_id => plans_rec.assignment_id
329 ,p_plan_id => plans_rec.accrual_plan_id
330 ,p_payroll_id => plans_rec.Payroll_id
331 ,p_business_group_id => plans_rec.business_group_id
332 ,p_assignment_action_id => null
333 ,p_calculation_date => p_calculation_date
334 ,p_gross_accruals => l_person_plans(plans_rec.rownum).gross_accruals_ytd
335 ,p_last_accrual_date => l_last_accrual_date);
336 --
337 HR_PTO_VIEWS.Get_pto_ptd_gross
338 (p_assignment_id => plans_rec.assignment_id
339 ,p_plan_id => plans_rec.accrual_plan_id
340 ,p_payroll_id => plans_rec.Payroll_id
341 ,p_business_group_id => plans_rec.business_group_id
342 ,p_assignment_action_id => null
343 ,p_calculation_date => p_calculation_date
344 ,p_gross_accruals => l_person_plans(plans_rec.rownum).gross_accruals_ptd
345 ,p_last_accrual_date => l_last_accrual_date);
346 --
347 END LOOP;
348 RETURN l_person_plans;
349 END Get_pto_all_plans;
350 --
351 -- ---------------------------------------------------------------------- +
352 -- Get_pto_stored_balance
353 -- ---------------------------------------------------------------------- +
354 FUNCTION Get_pto_stored_balance(
355 p_assignment_action_id number
356 ,p_plan_id number) RETURN NUMBER IS
357 --
358 cursor csr_get_balance is
359 select defined_balance_id
360 from pay_accrual_plans
361 where accrual_plan_id = p_plan_id;
362 --
363 l_asg_rec csr_get_aaid_details%ROWTYPE;
364 l_balance_id pay_accrual_plans.defined_balance_id%TYPE;
365 l_result number;
366 l_date date;
367 --
368 BEGIN
369 open csr_get_balance;
370 fetch csr_get_balance into l_balance_id;
371 if csr_get_balance%FOUND and nvl(p_assignment_action_id,-1) <> -1 then
372 close csr_get_balance;
373 open csr_get_aaid_details(p_assignment_action_id);
374 fetch csr_get_aaid_details into l_asg_rec;
375 close csr_get_aaid_details;
376 Get_pto_ytd_net_entitlement(
377 p_assignment_id => l_asg_rec.assignment_id
378 ,p_plan_id => p_plan_id
379 ,p_payroll_id => l_asg_rec.payroll_id
380 ,p_business_group_id => l_asg_rec.business_group_id
381 ,p_assignment_action_id => p_assignment_action_id
382 ,p_calculation_date => NULL
383 ,p_net_entitlement => l_result
384 ,p_last_accrual_date => l_date);
385 l_result := nvl(l_result,0);
386 else
387 close csr_get_balance;
388 l_result := NULL;
389 end if;
390 return l_result;
391 END Get_pto_stored_balance;
392 --
393 END HR_PTO_VIEWS;