[Home] [Help]
PACKAGE BODY: APPS.PAY_US_EMP_PAYSLIP_ACCRUAL_WEB
Source
1 PACKAGE BODY pay_us_emp_payslip_accrual_web
2 /* $Header: pyusacrw.pkb 120.5.12000000.3 2007/03/05 06:20:04 sackumar ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_emp_payslip_accrual_web
21
22 Description : Package gets all the Accrual plans for an
23 Employee and populates a PL/SQL table -
24 LTR_ASSIGNMENT_ACCRUALS.
25
26 Uses :
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ---- ---- ---- ------ -----------
32 01-JUL-1999 ahanda 110.0 Created.
33 24-DEC-1999 ahanda 110.1 1115325 Added paramter to procedure
34 get_emp_net_accrual to return the
35 total no. of Accrual Categories.
36 17-JAN-2000 ahanda 110.2 Changed the logic for getting the
37 Current Accruals.
38 20-feb-2001 djoshi 115.2 Added procedure to delete the
39 pl/sql table for the accurual
40 procedure is
41 delete_ltr_assignment_accrual
42 29-NOV-2001 asasthan Changed get_net_accruals
43 Added p_assignment_action_id
44 parameter
45 24-JAN-2002 ahanda Added fnd_date.canonical_to_date
46 for getting Cont Calc Date
47 12-JUN-2002 ahanda 115.5 Changed length of var lv_accrual_code
48 to 100.
49 26-JUN-2003 ahanda 115.6 Changed cursor c_accrual_category
50 06-Aug-2003 vpandya 115.8 Added NOCOPY with out parameter.
51 28-May-2005 sackumar 115.9 4053111 in get_emp_net_accrual procedure the
52 values of current arrcrual is coming
53 incorrectly for that i introduce a new
54 call of the procedure
55 per_accrual_calc_functions.get_net_accrual
56 17-JUN-2005 sackumar 115.10 Add distinct in c_accrual_category cursor.
57 17-OCT-2005 ahanda 115.11 4681780 Initalized variables
58 18-OCT-2005 ahanda 115.12 4681780 Initalized all total variables
59 09-JAN-2006 ahanda 115.13 4761039 Passing -1 for asg action in
60 pay_us_pto_accrual.get_net_accrual
61 31-MAY-2006 ppanda 115.14 5220628 Cursor c_accrual_category changed in procedure
62 get_emp_net_accrual to filter values based on
63 Security Group
64 02-OCT-2006 ahanda 115.15 5473954 Storing Accrual Code in PL/SQL table
65 05-MAR-2007 sackumar 115.16 5470648 Modified get_emp_net_accrual procedure.
66 *******************************************************************/
67 AS
68 /*****************************************************
69 ** Global Package Body Variables
70 ******************************************************/
71 gv_package_name VARCHAR2(100) := 'pay_us_emp_payslip_accrual_web';
72
73
74 PROCEDURE get_emp_net_accrual (
75 p_assignment_action_id in number
76 ,p_assignment_id in number
77 ,p_cur_earned_date in date
78 ,p_total_acc_category out NOCOPY number
79 )
80 /******************************************************************
81 **
82 ** Description:
83 ** This procedure puts the Accrual Info for an employee
84 ** and for Payment Date.
85 **
86 ** Access Status:
87 ** Public.
88 **
89 ******************************************************************/
90 IS
91
92 Cursor c_time_periods( cp_payroll_id in number
93 ,cp_effective_date in date
94 ) is
95 select start_date,
96 end_date,
97 period_num,
98 ptpt.number_per_fiscal_year
99 from per_time_period_types ptpt,
100 per_time_periods ptp
101 where ptpt.period_type = ptp.period_type
102 and ptp.payroll_id = cp_payroll_id
103 and cp_effective_date between ptp.start_date
104 and ptp.end_date;
105
106 Cursor c_payroll_id(
107 cp_assignment_action_id in number
108 ) is
109 select payroll_id, business_group_id
110 from pay_payroll_actions ppa,
111 pay_assignment_actions paa
112 where ppa.payroll_action_id = paa.payroll_action_id
113 and paa.assignment_action_id = cp_assignment_action_id;
114 --
115 -- Following cursor changed to filter the lookup values based on security group
116 -- This changes made to fix Bug # 5220628
117 Cursor c_accrual_category(cp_language varchar2) is
118 select distinct flv.lookup_code,
119 flv.meaning
120 from fnd_lookup_values flv
121 where flv.lookup_type = 'US_PTO_ACCRUAL'
122 and flv.language = nvl(cp_language,userenv('LANG'))
123 and flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id);
124
125 Cursor c_accrual_plan( cp_assignment_id in number
126 ,cp_effective_date in date
127 ,cp_accrual_category in varchar2
128 )
129 is
130 select pap.accrual_plan_id,
131 pap.accrual_plan_element_type_id
132 from pay_accrual_plans pap,
133 pay_element_links_f pel,
134 pay_element_entries_f pee
135 where pap.accrual_category = cp_accrual_category
136 and pel.element_type_id= pap.accrual_plan_element_type_id
137 and cp_effective_date between pel.effective_start_date
138 and pel.effective_end_date
139 and pee.element_link_id = pel.element_link_id
140 and pee.assignment_id = cp_assignment_id
141 and cp_effective_date between pee.effective_start_date
142 and pee.effective_end_date;
143
144 ln_payroll_id NUMBER;
145 ln_business_group_id NUMBER;
146
147 lv_accrual_code VARCHAR2(100);
148 lv_accrual_category VARCHAR2(100);
149 lv_correspndence_lang VARCHAR2(100);
150
151 ln_accrual_plan_id NUMBER;
152 ln_accrual_ele_type_id NUMBER;
153
154 ld_period_start_date DATE;
155 ld_period_end_date DATE;
156 ln_period_num NUMBER;
157 ln_fiscal_period_num NUMBER;
158
159 l_net_accrual_hours NUMBER := 0;
160 l_pre_accrual NUMBER := 0;
161 l_latest_accrual Number := 0;
162 l_cur_tot_accrual_hours NUMBER := 0;
163 l_net_tot_accrual_hours NUMBER := 0;
164 l_pre_tot_accrual NUMBER := 0;
165 l_latest_tot_accrual Number := 0;
166 ld_dummy_dat DATE;
167 ld_dummy_dat1 DATE;
168 ld_dummy_dat2 DATE;
169 ln_dummy_num NUMBER;
170
171 i_count NUMBER := 0;
172 lc_plan_exists VARCHAR2(1) := 'N';
173
174 BEGIN
175
176 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 10);
177 hr_utility.trace('p_assignment_action_id = ' || p_assignment_action_id);
178 hr_utility.trace('p_assignment_id = ' || p_assignment_id );
179 hr_utility.trace('p_cur_earned_date = ' || p_cur_earned_date );
180
181 open c_payroll_id(p_assignment_action_id);
182 fetch c_payroll_id into ln_payroll_id, ln_business_group_id;
183 if c_payroll_id%found then
184 open c_time_periods(ln_payroll_id, p_cur_earned_date);
185 fetch c_time_periods into ld_period_start_date, ld_period_end_date,
186 ln_period_num, ln_fiscal_period_num;
187 close c_time_periods;
188 end if;
189 close c_payroll_id;
190
191 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 30);
192 hr_utility.trace('Payroll Start Date = ' || ld_period_start_date);
193 hr_utility.trace('Payroll End Date = ' || ld_period_end_date);
194
195 lv_correspndence_lang := pay_emp_action_arch.gv_correspondence_language;
196
197 hr_utility.trace('lv_correspndence_lang = ' || lv_correspndence_lang);
198
199 open c_accrual_category(lv_correspndence_lang);
200 loop
201 fetch c_accrual_category into lv_accrual_code, lv_accrual_category;
202 if c_accrual_category%notfound then
203 exit;
204 end if;
205
206 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 50);
207 hr_utility.trace('Accrual Category = ' || lv_accrual_code);
208
209 open c_accrual_plan( p_assignment_id
210 ,p_cur_earned_date
211 ,lv_accrual_code
212 );
213 loop
214 fetch c_accrual_plan into ln_accrual_plan_id
215 ,ln_accrual_ele_type_id;
216 if c_accrual_plan%notfound then
217 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 60);
218 exit;
219 end if;
220
221 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 70);
222 hr_utility.trace('Accrual Plan ID = ' || ln_accrual_plan_id);
223 hr_utility.trace('Accrual Elem ID = ' || ln_accrual_ele_type_id);
224
225
226 /*************************************************************
227 ** Set Var. if Plan Exists.
228 *************************************************************/
229 lc_plan_exists := 'Y';
230 l_pre_accrual := 0;
231 l_latest_accrual := 0;
232 l_net_accrual_hours := 0;
233
234 /*************************************************************
235 ** Get Net Accruals
236 *************************************************************/
237 l_net_accrual_hours := pay_us_pto_accrual.get_net_accrual (
238 p_assignment_id => p_assignment_id
239 ,p_calculation_date => ld_period_end_date
240 ,p_plan_id => ln_accrual_plan_id
241 ,p_plan_category => lv_accrual_code
242 ,p_assignment_action_id => -1
243 );
244
245
246 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 80);
247
248 per_accrual_calc_functions.get_net_accrual(
249 p_assignment_id => p_assignment_id,
250 p_plan_id => ln_accrual_plan_id,
251 p_payroll_id => ln_payroll_id,
252 p_business_group_id => ln_business_group_id,
253 p_assignment_action_id => -1, --p_assignment_action_id,
254 p_calculation_date => ld_period_end_date,
255 p_accrual_start_date => null,
256 p_accrual_latest_balance => null,
257 p_calling_point => 'BP',
258 p_accrual => l_latest_accrual,
259 p_net_entitlement => ln_dummy_num,
260 p_end_date => ld_dummy_dat,
261 p_accrual_end_date => ld_dummy_dat1,
262 p_start_date => ld_dummy_dat2
263 );
264
265 IF ld_dummy_dat1 is not null THEN
266 IF ld_dummy_dat1 >= ld_period_start_date AND ld_dummy_dat1 <= ld_period_end_date THEN
267 -- New Call Suggested for Bug No 4053111
268 per_accrual_calc_functions.get_net_accrual(
269 p_assignment_id => p_assignment_id,
270 p_plan_id => ln_accrual_plan_id,
271 p_payroll_id => ln_payroll_id,
272 p_business_group_id => ln_business_group_id,
273 --p_calculation_date => ld_period_end_date - 1, -- Commented for bug 5470648
274 p_calculation_date => ld_dummy_dat1 - 1, -- Added for bug 5470648
275 p_assignment_action_id => -1, ---p_assignment_action_id,
276 p_accrual_start_date => NULL,
277 p_accrual_latest_balance => null,
278 p_calling_point => 'BP',
279 p_accrual => l_pre_accrual,
280 p_net_entitlement => ln_dummy_num,
281 p_end_date => ld_dummy_dat,
282 p_accrual_end_date => ld_dummy_dat1,
283 p_start_date => ld_dummy_dat2
284 );
285 ELSE
286 l_pre_accrual := l_latest_accrual;
287 END IF;
288 ELSE
289 l_pre_accrual := l_latest_accrual;
290 END IF;
291
292 l_pre_tot_accrual := l_pre_tot_accrual + l_pre_accrual;
293 l_latest_tot_accrual := l_latest_tot_accrual + l_latest_accrual;
294 l_net_tot_accrual_hours := l_net_tot_accrual_hours + l_net_accrual_hours;
295 end loop;
296 close c_accrual_plan;
297 l_cur_tot_accrual_hours := l_latest_tot_accrual - l_pre_tot_accrual;
298
299 if lc_plan_exists = 'Y' then
300
301 i_count := i_count + 1;
302
303 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 100);
304 lc_plan_exists := 'N';
305
306 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_code
307 := lv_accrual_code;
308 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_category
309 := lv_accrual_category;
310 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_cur_value
311 := l_cur_tot_accrual_hours;
312 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_net_value
313 := l_net_tot_accrual_hours;
314
315 lv_accrual_category := null;
316 lv_accrual_code := null;
317 l_cur_tot_accrual_hours := 0;
318 l_net_tot_accrual_hours := 0;
319 l_latest_tot_accrual := 0;
320 l_pre_tot_accrual := 0;
321 end if;
322 end loop;
323 close c_accrual_category;
324
325 p_total_acc_category := i_count;
326
327
328 for i in 1 .. i_count loop
329 hr_utility.trace('Accrual Code = '
330 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_code);
331 hr_utility.trace('Accrual Type = '
332 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_category);
333 hr_utility.trace('Accrual Current = '
334 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_cur_value);
335 hr_utility.trace('Accrual Bal = '
336 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_net_value);
337 end loop;
338
339 hr_utility.trace('Total Accrual Account = ' || i_count);
340 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 110);
341
342 END get_emp_net_accrual;
343
344
345 PROCEDURE delete_ltr_assignment_accrual
346
347 /******************************************************************
348 **
349 ** Description:
350 ** This procedure Deletes the ltr_assignment_accrual PL/SQL
351 ** Table
352 **
353 **
354 ** Access Status:
355 ** Public.
356 **
357 ******************************************************************/
358
359 IS
360 BEGIN
361 /* Delete the accrual PL/SQL table */
362 ltr_assignment_accruals.delete;
363 END delete_ltr_assignment_accrual;
364
365
366 --BEGIN
367 -- hr_utility.trace_on(null, 'ORACLE');
368
369 END pay_us_emp_payslip_accrual_web;