[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.7.12020000.4 2013/01/09 12:11:55 agarai 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 27-DEC-2011 nkjaladi 115.17 13425805 Modified get_emp_net_accrual procedure.
67 09-JAN-2013 nkjaladi 115.18 16064956 Modified get_emp_net_accrual procedure
68 to avoid negative current accruals.
69 09-JAN-2013 agarai 115.19 16064956 Modified get_emp_net_accrual procedure
70 to get the value for l_net_accrual_hours
71 using per_accrual_calc_functions.get_net_accrual
72 instead of pay_us_pto_accrual.get_net_accrual.
73 *******************************************************************/
74 AS
75 /*****************************************************
76 ** Global Package Body Variables
77 ******************************************************/
78 gv_package_name VARCHAR2(100) := 'pay_us_emp_payslip_accrual_web';
79
80
81 PROCEDURE get_emp_net_accrual (
82 p_assignment_action_id in number
83 ,p_assignment_id in number
84 ,p_cur_earned_date in date
85 ,p_total_acc_category out NOCOPY number
86 )
87 /******************************************************************
88 **
89 ** Description:
90 ** This procedure puts the Accrual Info for an employee
91 ** and for Payment Date.
92 **
93 ** Access Status:
94 ** Public.
95 **
96 ******************************************************************/
97 IS
98
99 Cursor c_time_periods( cp_payroll_id in number
100 ,cp_effective_date in date
101 ) is
102 select start_date,
103 end_date,
104 period_num,
105 ptpt.number_per_fiscal_year
106 from per_time_period_types ptpt,
107 per_time_periods ptp
108 where ptpt.period_type = ptp.period_type
109 and ptp.payroll_id = cp_payroll_id
110 and cp_effective_date between ptp.start_date
111 and ptp.end_date;
112
113 Cursor c_payroll_id(
114 cp_assignment_action_id in number
115 ) is
116 select payroll_id, business_group_id
117 from pay_payroll_actions ppa,
118 pay_assignment_actions paa
119 where ppa.payroll_action_id = paa.payroll_action_id
120 and paa.assignment_action_id = cp_assignment_action_id;
121 --
122 -- Following cursor changed to filter the lookup values based on security group
123 -- This changes made to fix Bug # 5220628
124 Cursor c_accrual_category(cp_language varchar2) is
125 select distinct flv.lookup_code,
126 flv.meaning
127 from fnd_lookup_values flv
128 where flv.lookup_type = 'US_PTO_ACCRUAL'
129 and flv.language = nvl(cp_language,userenv('LANG'))
130 and flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id);
131
132 Cursor c_accrual_plan( cp_assignment_id in number
133 ,cp_effective_date in date
134 ,cp_accrual_category in varchar2
135 )
136 is
137 select pap.accrual_plan_id,
138 pap.accrual_plan_element_type_id
139 from pay_accrual_plans pap,
140 pay_element_links_f pel,
141 pay_element_entries_f pee
142 where pap.accrual_category = cp_accrual_category
143 and pel.element_type_id= pap.accrual_plan_element_type_id
144 and cp_effective_date between pel.effective_start_date
145 and pel.effective_end_date
146 and pee.element_link_id = pel.element_link_id
147 and pee.assignment_id = cp_assignment_id
148 and cp_effective_date between pee.effective_start_date
149 and pee.effective_end_date;
150
151 ln_payroll_id NUMBER;
152 ln_business_group_id NUMBER;
153
154 lv_accrual_code VARCHAR2(100);
155 lv_accrual_category VARCHAR2(100);
156 lv_correspndence_lang VARCHAR2(100);
157
158 ln_accrual_plan_id NUMBER;
159 ln_accrual_ele_type_id NUMBER;
160
161 ld_period_start_date DATE;
162 ld_period_end_date DATE;
163 ln_period_num NUMBER;
164 ln_fiscal_period_num NUMBER;
165
166 l_net_accrual_hours NUMBER := 0;
167 l_pre_accrual NUMBER := 0;
168 l_latest_accrual Number := 0;
169 l_cur_tot_accrual_hours NUMBER := 0;
170 l_net_tot_accrual_hours NUMBER := 0;
171 l_pre_tot_accrual NUMBER := 0;
172 l_latest_tot_accrual Number := 0;
173 ld_dummy_dat DATE;
174 ld_dummy_dat1 DATE;
175 ld_dummy_dat2 DATE;
176 ln_dummy_num NUMBER;
177
178 i_count NUMBER := 0;
179 lc_plan_exists VARCHAR2(1) := 'N';
180 l_start_date DATE; --16064956
181
182 BEGIN
183
184 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 10);
185 hr_utility.trace('p_assignment_action_id = ' || p_assignment_action_id);
186 hr_utility.trace('p_assignment_id = ' || p_assignment_id );
187 hr_utility.trace('p_cur_earned_date = ' || p_cur_earned_date );
188
189 open c_payroll_id(p_assignment_action_id);
190 fetch c_payroll_id into ln_payroll_id, ln_business_group_id;
191 if c_payroll_id%found then
192 open c_time_periods(ln_payroll_id, p_cur_earned_date);
193 fetch c_time_periods into ld_period_start_date, ld_period_end_date,
194 ln_period_num, ln_fiscal_period_num;
195 close c_time_periods;
196 end if;
197 close c_payroll_id;
198
199 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 30);
200 hr_utility.trace('Payroll Start Date = ' || ld_period_start_date);
201 hr_utility.trace('Payroll End Date = ' || ld_period_end_date);
202
203 lv_correspndence_lang := pay_emp_action_arch.gv_correspondence_language;
204
205 hr_utility.trace('lv_correspndence_lang = ' || lv_correspndence_lang);
206
207 open c_accrual_category(lv_correspndence_lang);
208 loop
209 fetch c_accrual_category into lv_accrual_code, lv_accrual_category;
210 if c_accrual_category%notfound then
211 exit;
212 end if;
213
214 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 50);
215 hr_utility.trace('Accrual Category = ' || lv_accrual_code);
216
217 open c_accrual_plan( p_assignment_id
218 ,p_cur_earned_date
219 ,lv_accrual_code
220 );
221 loop
222 fetch c_accrual_plan into ln_accrual_plan_id
223 ,ln_accrual_ele_type_id;
224 if c_accrual_plan%notfound then
225 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 60);
226 exit;
227 end if;
228
229 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 70);
230 hr_utility.trace('Accrual Plan ID = ' || ln_accrual_plan_id);
231 hr_utility.trace('Accrual Elem ID = ' || ln_accrual_ele_type_id);
232
233
234 /*************************************************************
235 ** Set Var. if Plan Exists.
236 *************************************************************/
237 lc_plan_exists := 'Y';
238 l_pre_accrual := 0;
239 l_latest_accrual := 0;
240 l_net_accrual_hours := 0;
241
242 /*************************************************************
243 ** Get Net Accruals
244 *************************************************************/
245 /* Commented for bug 16064956
246 l_net_accrual_hours := pay_us_pto_accrual.get_net_accrual (
247 p_assignment_id => p_assignment_id
248 ,p_calculation_date => ld_period_end_date
249 ,p_plan_id => ln_accrual_plan_id
250 ,p_plan_category => lv_accrual_code
251 ,p_assignment_action_id => -1
252 );*/
253
254
255 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 80);
256
257 per_accrual_calc_functions.get_net_accrual(
258 p_assignment_id => p_assignment_id,
259 p_plan_id => ln_accrual_plan_id,
260 p_payroll_id => ln_payroll_id,
261 p_business_group_id => ln_business_group_id,
262 p_assignment_action_id => -1, --p_assignment_action_id,
263 p_calculation_date => ld_period_end_date,
264 p_accrual_start_date => null,
265 p_accrual_latest_balance => null,
266 p_calling_point => 'BP',
267 p_accrual => l_latest_accrual,
268 -- p_net_entitlement => ln_dummy_num, commented for bug 16064956
269 p_net_entitlement => l_net_accrual_hours, --added for bug 16064956
270 p_end_date => ld_dummy_dat,
271 p_accrual_end_date => ld_dummy_dat1,
272 p_start_date => l_start_date --ld_dummy_dat2 #16064956
273 );
274
275 IF ld_dummy_dat1 is not null THEN
276 IF ld_dummy_dat1 >= ld_period_start_date AND ld_dummy_dat1 <= ld_period_end_date THEN
277 IF (l_start_date < (ld_period_start_date - 1)) THEN -- #16064956 Added
278 -- New Call Suggested for Bug No 4053111
279 per_accrual_calc_functions.get_net_accrual(
280 p_assignment_id => p_assignment_id,
281 p_plan_id => ln_accrual_plan_id,
282 p_payroll_id => ln_payroll_id,
283 p_business_group_id => ln_business_group_id,
284 --p_calculation_date => ld_period_end_date - 1, -- Commented for bug 5470648
285 --p_calculation_date => ld_dummy_dat1 - 1, -- Added for bug 5470648 #13425805 commented
286 p_calculation_date => ld_period_start_date - 1, -- Added for bug #13425805
287 p_assignment_action_id => -1, ---p_assignment_action_id,
288 p_accrual_start_date => NULL,
289 p_accrual_latest_balance => null,
290 p_calling_point => 'BP',
291 p_accrual => l_pre_accrual,
292 p_net_entitlement => ln_dummy_num,
293 p_end_date => ld_dummy_dat,
294 p_accrual_end_date => ld_dummy_dat1,
295 p_start_date => ld_dummy_dat2
296 );
297 -- #16064956 Start
298 ELSE
299 l_pre_accrual := 0;
300 END IF;
301 -- #16064956 End
302 ELSE
303 l_pre_accrual := l_latest_accrual;
304 END IF;
305 ELSE
306 l_pre_accrual := l_latest_accrual;
307 END IF;
308
309 l_pre_tot_accrual := l_pre_tot_accrual + l_pre_accrual;
310 l_latest_tot_accrual := l_latest_tot_accrual + l_latest_accrual;
311 l_net_tot_accrual_hours := l_net_tot_accrual_hours + l_net_accrual_hours;
312 end loop;
313 close c_accrual_plan;
314 l_cur_tot_accrual_hours := l_latest_tot_accrual - l_pre_tot_accrual;
315
316 if lc_plan_exists = 'Y' then
317
318 i_count := i_count + 1;
319
320 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 100);
321 lc_plan_exists := 'N';
322
323 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_code
324 := lv_accrual_code;
325 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_category
326 := lv_accrual_category;
327 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_cur_value
328 := l_cur_tot_accrual_hours;
329 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i_count).accrual_net_value
330 := l_net_tot_accrual_hours;
331
332 lv_accrual_category := null;
333 lv_accrual_code := null;
334 l_cur_tot_accrual_hours := 0;
335 l_net_tot_accrual_hours := 0;
336 l_latest_tot_accrual := 0;
337 l_pre_tot_accrual := 0;
338 end if;
339 end loop;
340 close c_accrual_category;
341
342 p_total_acc_category := i_count;
343
344
345 for i in 1 .. i_count loop
346 hr_utility.trace('Accrual Code = '
347 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_code);
348 hr_utility.trace('Accrual Type = '
349 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_category);
350 hr_utility.trace('Accrual Current = '
351 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_cur_value);
352 hr_utility.trace('Accrual Bal = '
353 || pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_net_value);
354 end loop;
355
356 hr_utility.trace('Total Accrual Account = ' || i_count);
357 hr_utility.set_location(gv_package_name || '.get_emp_net_accrual', 110);
358
359 END get_emp_net_accrual;
360
361
362 PROCEDURE delete_ltr_assignment_accrual
363
364 /******************************************************************
365 **
366 ** Description:
367 ** This procedure Deletes the ltr_assignment_accrual PL/SQL
368 ** Table
369 **
370 **
371 ** Access Status:
372 ** Public.
373 **
374 ******************************************************************/
375
376 IS
377 BEGIN
378 /* Delete the accrual PL/SQL table */
379 ltr_assignment_accruals.delete;
380 END delete_ltr_assignment_accrual;
381
382
383 --BEGIN
384 -- hr_utility.trace_on(null, 'ORACLE');
385
386 END pay_us_emp_payslip_accrual_web;