[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_EOY_PKG
Source
1 package body pay_nl_eoy_pkg AS
2 /* $Header: pynleoy.pkb 120.5 2007/01/17 06:44:29 gkhangar noship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 ---------------------------------------------------------------------------
5 -- Procedure: get_prev_year_tax_income
6 -- Procedure which returns the balance values of a assignment for a given date
7 ---------------------------------------------------------------------------
8 function GET_PREV_YEAR_TAX_INCOME(p_assignment_id NUMBER
9 ,p_effective_date DATE) RETURN NUMBER IS
10 l_result NUMBER;
11 BEGIN
12 l_result := GET_PREV_YEAR_TAX_INCOME(p_assignment_id,p_effective_date,-1);
13 return l_result;
14 null;
15
16 END GET_PREV_YEAR_TAX_INCOME;
17 ---------------------------------------------------------------------------
18 -- Function: GET_PREV_YEAR_TAX_INCOME
19 -- Function which returns the previous year taxable income for a person as
20 --on effective date
21 ---------------------------------------------------------------------------
22
23 function GET_PREV_YEAR_TAX_INCOME(p_assignment_id NUMBER
24 ,p_effective_date DATE
25 ,p_payroll_action_id NUMBER) RETURN NUMBER IS
26
27
28 CURSOR get_all_assignments(p_assignment_id NUMBER) IS
29 select unique(paa1.assignment_id) assignment_id
30 from per_all_assignments_f paa
31 ,per_all_assignments_f paa1
32 where paa.assignment_id=p_assignment_id
33 and paa.person_id=paa1.person_id;
34
35
36 CURSOR get_payroll_id(p_assignment_id NUMBER
37 ,p_effective_date DATE) IS
38 select payroll_id
39 from per_all_assignments_f
40 where assignment_id=p_assignment_id
41 and p_effective_date between effective_start_date
42 and effective_end_date;
43
44 CURSOR get_dates(p_assignment_id NUMBER) IS
45 select min(paa.effective_start_date)
46 ,max(paa.effective_end_date)
47 from per_all_assignments_f paa
48 ,per_assignment_status_types pas
49 where assignment_id=p_assignment_id
50 and paa.assignment_status_type_id=pas.assignment_status_type_id
51 and pas.PER_SYSTEM_STATUS='ACTIVE_ASSIGN';
52
53 CURSOR get_start_date_period( l_payroll_id NUMBER
54 ,l_prev_year VARCHAR2) IS
55 select START_DATE
56 ,END_DATE
57 ,PERIOD_NUM
58 from
59 per_time_periods
60 where payroll_id=l_payroll_id
61 and period_name like '%'||l_prev_year||'%';
62 --and START_DATE >= l_prev_year_start_date
63 --and START_DATE <= l_prev_year_end_date;
64
65 CURSOR get_payroll_period( l_payroll_id NUMBER
66 ,p_effective_date DATE ) IS
67 select START_DATE
68 ,END_DATE
69 from
70 per_time_periods
71 where payroll_id=l_payroll_id
72 and p_effective_date between START_DATE and END_DATE;
73 --
74 CURSOR get_dp_period(c_pay_act_id NUMBER) IS
75 select ptp.start_date
76 ,ptp.end_date
77 from pay_payroll_actions ppa
78 ,per_time_periods ptp
79 where ppa.payroll_action_id = c_pay_act_id
80 AND ptp.time_period_id = ppa.time_period_id;
81 --
82 CURSOR get_ass_act_id(c_assignment_id NUMBER
83 ,c_start_date DATE
84 ,c_end_date DATE) IS
85 select paa.assignment_action_id
86 from pay_assignment_actions paa
87 ,pay_payroll_actions ppa
88 where paa.assignment_id = c_assignment_id
89 and paa.payroll_action_id = ppa.payroll_action_id
90 AND ppa.action_type IN ('Q','R')
91 AND ppa.action_status in ('C','P')
92 AND ppa.date_earned BETWEEN c_start_date AND c_end_date
93 AND paa.source_action_id IS NOT NULL
94 ORDER BY 1 desc;
95 --
96 l_current_year VARCHAR2(10);
97 l_prev_year VARCHAR2(10);
98 l_start_of_the_period NUMBER;
99 l_periods_of_year NUMBER;
100 l_periods_in_service NUMBER;
101 l_first_period NUMBER;
102 l_estimated_hol_allow NUMBER;
103 l_period_start_date DATE;
104 l_period_end_date DATE;
105 l_prev_year_income NUMBER;
106 l_sum_prev_year_income NUMBER;
107 l_std_tax_income NUMBER;
108 l_spl_tax_income NUMBER;
109 l_retrostd_tax_income NUMBER;
110 l_retrostdcurrq_tax_income NUMBER;
111 l_retrospl_tax_income NUMBER;
112 l_hol_allow_pay_income NUMBER;
113 l_hol_allow_tax_income NUMBER;
114 l_retrohol_allow_tax_income NUMBER;
115 l_std_tax_income_ptd NUMBER;
116 l_spl_tax_income_ptd NUMBER;
117 l_retrostd_tax_income_ptd NUMBER;
118 l_retrostdcurrq_tax_income_ptd NUMBER;
119 l_retrospl_tax_income_ptd NUMBER;
120 l_hol_allow_pay_income_ptd NUMBER;
121 l_hol_allow_tax_income_ptd NUMBER;
122 l_rethol_allow_tax_income_ptd NUMBER;
123 l_special_rate_income_ptd NUMBER;
124 l_sum_hol_allow_tax_income NUMBER;
125 l_week_days NUMBER;
126 l_total_week_days NUMBER;
127 l_first_period_income NUMBER;
128 l_sum_hol_first_period NUMBER;
129 l_sum_hol_prev_year NUMBER;
130 l_curr_year_start_date DATE;
131 l_prev_year_start_date DATE;
132 l_prev_year_end_date DATE;
133 l_dummy DATE;
134 l_dummy_num NUMBER;
135 l_hol_allow_perc NUMBER;
136 l_asg_start_date DATE;
137 l_asg_end_date DATE;
138 l_payroll_id NUMBER;
139 l_temp_asg_start_date DATE;
140 l_periods_after_last_period NUMBER;
141 l_last_period NUMBER;
142 l_date_earned_year DATE;
143 l_date_earned_period DATE;
144 l_spl_rate_income_dbaid_ptd NUMBER;
145 l_periods_of_curr_year NUMBER;
146 l_current_year_start_date DATE;
147 l_current_year_end_date DATE;
148 l_current_period_start_date DATE;
149 l_current_period_end_date DATE;
150 l_special_rate_income NUMBER;
151 l_special_rate_annual_income NUMBER;
152 l_special_rate_income_add NUMBER;
153 l_ass_act_id NUMBER;
154 l_type VARCHAR2(10);
155 l_dp_start_date DATE;
156 l_dp_end_date DATE;
157 --
158 Begin
159
160 l_special_rate_annual_income :=0;
161 l_special_rate_income_add :=0;
162 l_sum_prev_year_income :=0;
163 l_prev_year_income :=0;
164
165 IF (hr_utility.chk_product_install('Oracle Payroll','NL')) THEN
166 null;
167 ELSE
168 return 0;
169 END IF;
170
171 --hr_utility.trace_on(null,'GR');
172 --OPEN get_dp_period(payroll_action_id);
173 --FETCH get_dp_period INTO l_dp_start_date,l_dp_end_date;
174 --CLOSE get_dp_period;
175
176 /* Determine the previous year and current year e.g. 2003 and 2004 respectively.*/
177 l_current_year:=TO_CHAR(p_effective_date,'YYYY');
178 l_prev_year:=l_current_year-1;
179 --hr_utility.trace_on(NULL,'NL_PREV');
180 --if g_debug then
181 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year'||l_prev_year,2000);
182 --end if;
183
184 l_curr_year_start_date:=TO_DATE('01/'||'01/'||l_current_year,'DD/MM/YYYY');
185 l_prev_year_start_date:=TO_DATE('01/'||'01/'||l_prev_year,'DD/MM/YYYY');
186 l_prev_year_end_date :=TO_DATE('31/'||'12/'||l_prev_year,'DD/MM/YYYY');
187
188 --if g_debug then
189 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,2100);
190 --end if;
191
192
193
194 /* Get the value of Global NL_TAX_HOLIDAY_ALLOWANCE_PERCENTAGE */
195
196 l_hol_allow_perc:=fnd_number.canonical_to_number(pay_nl_general.get_global_value(
197 l_date_earned =>p_effective_date,
198 l_global_name =>'NL_TAX_HOLIDAY_ALLOWANCE_PERCENTAGE'));
199
200 --if g_debug then
201 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_hol_allow_perc'||l_hol_allow_perc,2200);
202 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: p_assignment_id'||p_assignment_id,2300);
203 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: p_effective_date'||p_effective_date,2300);
204 --end if;
205
206 /* LOOP through all assignments for the person, effective in the previous year and current year.*/
207 FOR l_assignment IN get_all_assignments(p_assignment_id) LOOP
208
209 OPEN get_dates(l_assignment.assignment_id);
210 FETCH get_dates INTO l_asg_start_date,l_asg_end_date;
211 CLOSE get_dates;
212
213 OPEN get_payroll_id(l_assignment.assignment_id,l_asg_start_date);
214 FETCH get_payroll_id INTO l_payroll_id;
215 CLOSE get_payroll_id;
216
217 --FOR l_assignment IN get_all_assignments(p_assignment_id,p_effective_date) LOOP
218
219 --if g_debug then
220 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: EFFECTIVE_START_DATE'||l_asg_start_date,2400);
221 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: EFFECTIVE_END_DATE'||l_asg_end_date,2400);
222 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: PAYROLL_ID'||l_payroll_id,2400);
223 --end if;
224 l_start_of_the_period:=0;
225 l_periods_of_year:=0;
226 l_periods_in_service:=0;
227
228 /* Loop thr all the periods of the previous year to determine the
229 Number of periods in service, Total periods in the year
230 start date and date of the first period,start date and end date of the previous year */
231 OPEN get_start_date_period(l_payroll_id,l_prev_year);
232 FETCH get_start_date_period INTO l_prev_year_start_date,l_dummy,l_dummy_num;
233 CLOSE get_start_date_period;
234
235 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_asg_start_date'||l_asg_start_date,2400);
236 l_temp_asg_start_date:=l_asg_start_date;
237 IF l_asg_start_date < l_prev_year_start_date THEN
238 l_temp_asg_start_date:= l_prev_year_start_date;
239 END IF;
240 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_temp_asg_start_date'||l_temp_asg_start_date,2400);
241 FOR l_start_date IN get_start_date_period(l_payroll_id,l_prev_year) LOOP
242
243 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_start_date.START_DATE'||l_start_date.START_DATE,2410);
244
245 IF l_start_date.START_DATE = l_temp_asg_start_date THEN
246 l_start_of_the_period:=1;
247 END IF;
248 l_periods_of_year:=l_periods_of_year+1;
249 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_start_of_the_period'||l_start_of_the_period,2420);
250 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_of_year'||l_periods_of_year,2420);
251
252 IF l_temp_asg_start_date >= l_start_date.START_DATE THEN
253
254 l_first_period:=l_start_date.PERIOD_NUM;
255 l_period_start_date:=l_start_date.START_DATE;
256 l_period_end_date:=l_start_date.END_DATE;
257 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_first_period'||l_first_period,2430);
258 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_period_start_date'||l_period_start_date,2430);
259 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_period_end_date'||l_period_end_date,2430);
260 END IF;
261 --IF l_start_date.START_DATE >= l_asg_end_date and l_last_period is null THEN
262 --l_last_period:=l_start_date.PERIOD_NUM;
263 --END IF;
264 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_last_period'||l_last_period,2440);
265 IF l_start_date.PERIOD_NUM=1 THEN
266 l_prev_year_start_date:=l_start_date.START_DATE;
267 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_start_date'||l_prev_year_start_date,2440);
268 END IF;
269 l_prev_year_end_date:=l_start_date.END_DATE;
270 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: PAYROLL_ID'||l_payroll_id,2450);
271 END LOOP;
272
273 --if g_debug then
274 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_of_year'||l_periods_of_year,2500);
275 --end if;
276 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_of_year'||l_periods_of_year,2500);
277 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_first_period'||l_first_period,2500);
278
279 l_periods_in_service:=l_periods_of_year - l_first_period;
280 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_in_service'||l_periods_in_service,2501);
281 l_periods_in_service:=l_periods_in_service+1;
282
283 /*IF l_asg_end_date < l_prev_year_end_date THEN
284 l_periods_after_last_period:=l_periods_of_year - l_last_period + 1;
285 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_after_last_period'||l_periods_after_last_period,2502);
286 l_periods_in_service:=l_periods_in_service - l_periods_after_last_period;
287 END IF;*/
288
289 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_of_year'||l_periods_of_year,2503);
290 --if g_debug then
291 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_in_service'||l_periods_in_service,2504);
292 --end if;
293 OPEN get_start_date_period(l_payroll_id,l_current_year);
294 FETCH get_start_date_period INTO l_curr_year_start_date,l_dummy,l_dummy_num;
295 CLOSE get_start_date_period;
296
297
298
299 l_std_tax_income :=0;
300 l_spl_tax_income :=0;
301 l_retrostd_tax_income :=0;
302 l_retrostdcurrq_tax_income :=0;
303 l_retrospl_tax_income :=0;
304 l_hol_allow_pay_income :=0;
305 l_std_tax_income_ptd :=0;
306 l_spl_tax_income_ptd :=0;
307 l_retrostd_tax_income_ptd :=0;
308 l_retrostdcurrq_tax_income_ptd :=0;
309 l_retrospl_tax_income_ptd :=0;
310 l_prev_year_income :=0;
311 l_retrohol_allow_tax_income :=0;
312 l_hol_allow_pay_income_ptd :=0;
313 l_hol_allow_tax_income_ptd :=0;
314 l_rethol_allow_tax_income_ptd :=0;
315 l_sum_hol_allow_tax_income :=0;
316 l_estimated_hol_allow :=0;
317 l_first_period_income :=0;
318 l_sum_hol_prev_year :=0;
319 l_sum_hol_first_period :=0;
320 l_sum_hol_prev_year :=0;
321 l_week_days :=0;
322 l_total_week_days :=0;
323 l_periods_of_curr_year :=0;
324 l_special_rate_income :=0;
325
326 IF l_asg_start_date < l_curr_year_start_date THEN
327 IF l_asg_end_date < l_prev_year_end_date THEN
328 l_date_earned_year:=l_asg_end_date;
329 ELSE
330 l_date_earned_year:=l_prev_year_end_date;
331 END IF;
332
333 IF l_asg_end_date < l_period_end_date THEN
334 l_date_earned_period:=l_asg_end_date;
335 ELSE
336 l_date_earned_period:=l_period_end_date;
337 END IF;
338
339 PAY_NL_EOY_PKG.get_balance_values(l_assignment.assignment_id
340 ,l_date_earned_year
341 ,l_date_earned_period
342 ,l_std_tax_income
343 ,l_spl_tax_income
344 ,l_retrostd_tax_income
345 ,l_retrostdcurrq_tax_income
346 ,l_retrospl_tax_income
347 ,l_hol_allow_pay_income
348 ,l_hol_allow_tax_income
349 ,l_retrohol_allow_tax_income
350 ,l_std_tax_income_ptd
351 ,l_spl_tax_income_ptd
352 ,l_retrostd_tax_income_ptd
353 ,l_retrostdcurrq_tax_income_ptd
354 ,l_retrospl_tax_income_ptd
355 ,l_hol_allow_pay_income_ptd
356 ,l_hol_allow_tax_income_ptd
357 ,l_rethol_allow_tax_income_ptd
358 );
359 END IF;
360
361 --if g_debug then
362 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_std_tax_income'||l_std_tax_income,2600);
363 --end if;
364 IF l_periods_in_service IS NULL OR l_periods_in_service=0 THEN
365 l_periods_in_service:=1;
366 END IF;
367
368 FOR l_start_date IN get_start_date_period(l_payroll_id,l_current_year) LOOP
369
370 l_periods_of_curr_year:=l_periods_of_curr_year+1;
371 IF l_start_date.PERIOD_NUM=1 THEN
372 l_current_year_start_date:=l_start_date.START_DATE;
373 END IF;
374
375 l_current_year_end_date:=l_start_date.END_DATE;
376 END LOOP;
377
378
379 OPEN get_payroll_period (l_payroll_id,p_effective_date);
380 FETCH get_payroll_period INTO l_current_period_start_date ,l_current_period_end_date;
381 CLOSE get_payroll_period;
382
383
384
385 /* If assignment was valid for the whole of the previous year or if it was ended during previous year.*/
386 --Bug 3482065
387 IF (l_asg_start_date <= l_prev_year_start_date and l_asg_end_date >= l_prev_year_end_date )
388 or (l_asg_end_date < l_prev_year_end_date and TO_CHAR(l_asg_end_date,'YYYY') = TO_CHAR(l_prev_year_end_date,'YYYY') )THEN
389
390
394
391 /* Set previous year's taxable income for this assignment to be the sum of balances Standard Taxable Income
392 Special Taxable Income, Retro Standard Taxable Income, Retro Standard Taxable Income Current Quarter
393 and Retro Special Taxable Income for the whole of the previous year */
395 l_prev_year_income:= l_std_tax_income + l_spl_tax_income + l_retrostd_tax_income +
396 l_retrostdcurrq_tax_income + l_retrospl_tax_income;
397
398
399 l_sum_prev_year_income:=l_sum_prev_year_income + l_std_tax_income + l_spl_tax_income + l_retrostd_tax_income +
400 l_retrostdcurrq_tax_income + l_retrospl_tax_income;
401
402 --if g_debug then
403 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_prev_year_income'||l_sum_prev_year_income,2800);
404 --end if;
405
406 /* If assignment started at the beginning of a period in the previous year.*/
407 ELSIF l_start_of_the_period = 1 and l_asg_end_date >= l_prev_year_start_date AND l_asg_start_date < l_curr_year_start_date THEN
408
409 --if g_debug then
410 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,2900);
411 --end if;
412 /* Add together the values of balances Standard Taxable Income, Special Taxable
413 Income, Retro Standard Taxable Income, Retro Standard Taxable Income
414 Current Quarter and Retro Special Taxable Income for the previous year.*/
415
416 l_prev_year_income:= l_std_tax_income + l_spl_tax_income + l_retrostd_tax_income +
417 l_retrostdcurrq_tax_income + l_retrospl_tax_income;
418
419
420 --if g_debug then
421 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_hol_allow_pay_income'||l_hol_allow_pay_income,3000);
422 --end if;
423 /* Remove the value of balance Holiday Allowance Payment for the previous year. */
424
425 l_prev_year_income:= l_prev_year_income - l_hol_allow_pay_income;
426
427 /* Divide the total by the number of payroll periods in service.*/
428 --if g_debug then
429 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,3100);
430 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_in_service'||l_periods_in_service,3200);
431 --end if;
432 l_prev_year_income:= l_prev_year_income/l_periods_in_service;
433 /* Multiply by the total number of payroll periods in the year to provide the estimated taxable income for the previous year */
434 --if g_debug then
435 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_periods_of_year'||l_periods_of_year,3300);
436 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,3300);
437 --end if;
438 l_prev_year_income:=l_prev_year_income * l_periods_of_year;
439
440
441 /* To start with, the value of balance Holiday Allowance Payment must be
442 subtracted from the total of balances Holiday Allowance Taxable Income and
443 Retro Holiday Allowance Taxable Income, to provide the value of holiday
444 allowance taxable income for the part of the year worked.
445 */
446 l_sum_hol_allow_tax_income:=l_hol_allow_tax_income + l_retrohol_allow_tax_income;
447 --Bug 3479044
448 l_sum_hol_allow_tax_income:=l_sum_hol_allow_tax_income-l_hol_allow_pay_income;
449
450 --if g_debug then
451 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_allow_tax_income'||l_sum_hol_allow_tax_income,3400);
452 --end if;
453 l_sum_hol_allow_tax_income:= l_sum_hol_allow_tax_income/l_periods_in_service;
454 l_sum_hol_allow_tax_income:=l_sum_hol_allow_tax_income * l_periods_of_year;
455
456 --if g_debug then
457 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_allow_tax_income'||l_sum_hol_allow_tax_income,3500);
458 --end if;
459 l_estimated_hol_allow:=l_sum_hol_allow_tax_income * l_hol_allow_perc/100;
460
461 --if g_debug then
462 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_estimated_hol_allow'||l_estimated_hol_allow,3600);
463 --end if;
464 /* Add the estimated holiday allowance to the estimated taxable income, to provide
465 the previous year?s taxable income for this assignment */
466
467 l_prev_year_income:= l_prev_year_income + l_estimated_hol_allow;
468
469
470 l_sum_prev_year_income:=l_sum_prev_year_income +l_prev_year_income;
471
472 --if g_debug then
473 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,3700);
474 --end if;
475 /* If the assignment started part way through a period in the previous year */
476 ELSIF l_asg_end_date >= l_prev_year_start_date AND l_asg_start_date < l_curr_year_start_date THEN
477
478 /* Determine the number of week days (Monday to Friday) that the employee
479 worked in the period i.e. between the assignment start date and the end of the period.*/
480
481 l_week_days:= pay_nl_si_pkg.get_week_days(l_asg_start_date,l_period_end_date);
482
483 --if g_debug then
484 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_week_days'||l_week_days,3800);
485 --end if;
486 IF l_week_days IS NULL OR l_week_days=0 THEN
487 l_week_days:=1;
488 END IF;
489 /*Determine the total number of week days in the period i.e. between the period
490 start date and the end of the period.*/
491
492 l_total_week_days := pay_nl_si_pkg.get_week_days(l_period_start_date,l_period_end_date);
493
494 IF l_total_week_days IS NULL THEN
495 l_total_week_days:=0;
496 END IF;
497 --if g_debug then
501 /*Add together the balances Standard Taxable Income, Special Taxable
498 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_total_week_days'||l_total_week_days,3900);
499 --end if;
500
502 Income, Retro Standard Taxable Income, Retro Standard Taxable Income
503 Current Quarter and Retro Special Taxable Income for this first period.*/
504
505 l_first_period_income:= l_std_tax_income_ptd + l_spl_tax_income_ptd + l_retrostd_tax_income_ptd +
506 l_retrostdcurrq_tax_income_ptd + l_retrospl_tax_income_ptd;
507 --if g_debug then
508 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_first_period_income'||l_first_period_income,4000);
509 --end if;
510
511 l_prev_year_income:= l_std_tax_income + l_spl_tax_income + l_retrostd_tax_income +
512 l_retrostdcurrq_tax_income + l_retrospl_tax_income;
513 --if g_debug then
514 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,4100);
515 --end if;
516
517 l_prev_year_income:=l_prev_year_income - l_first_period_income;
518
519 --if g_debug then
520 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,4200);
521 --end if;
522
523 /*Then divide this by the number of week days worked and then multiply by the number
524 of week days in the period. This will provide an estimated taxable income for the
525 whole of the first period.*/
526 --Bug 3479044
527 l_first_period_income:=l_first_period_income-l_hol_allow_pay_income_ptd;
528
529 l_first_period_income:=l_first_period_income/l_week_days;
530 l_first_period_income:=l_first_period_income * l_total_week_days;
531 --if g_debug then
532 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_first_period_income'||l_first_period_income,4300);
533 --end if;
534
535 /*Add together the balances Standard Taxable Income, Special Taxable
536 Income, Retro Standard Taxable Income, Retro Standard Taxable Income
537 Current Quarter and Retro Special Taxable Income for rest of the previous year
538 (excluding the first period), and subtract any value of balance Holiday Allowance
539 Payment for the rest of the previous year (excluding the first period). */
540 --Bug 3479044
541 l_hol_allow_pay_income:=l_hol_allow_pay_income - l_hol_allow_pay_income_ptd;
542
543 l_prev_year_income:=l_prev_year_income - l_hol_allow_pay_income;
544
545 /*Then add this total to the estimated taxable income for the first period. */
546 l_prev_year_income:=l_prev_year_income + l_first_period_income;
547
548 --if g_debug then
549 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,4500);
550 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_hol_allow_pay_income'||l_hol_allow_pay_income,4400);
551 --end if;
552 /*Remove the value of balance Holiday Allowance Payment for the previous year.*/
553
554 -- l_prev_year_income:=l_prev_year_income - l_hol_allow_pay_income;
555
556 --if g_debug then
557 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,4600);
558 --end if;
559 /*Divide the total by the number of periods in service (including the part period and
560 treating this as a full period).*/
561
562 l_prev_year_income:= l_prev_year_income/l_periods_in_service;
563 --if g_debug then
564 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,4700);
565 --end if;
566 /*Multiply by the total number of payroll periods in the year, to provide the
567 estimated taxable income for the previous year.*/
568
569 l_prev_year_income:=l_prev_year_income * l_periods_of_year;
570 --if g_debug then
571 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,4800);
572 --end if;
573 /*Calculate the estimated value for holiday allowance as a percentage of an
574 adjusted total of balances Holiday Allowance Taxable Income and Retro Holiday
575 Allowance Taxable Income in the previous year.*/
576
577 l_sum_hol_first_period:=l_hol_allow_tax_income_ptd + l_rethol_allow_tax_income_ptd;
578 --if g_debug then
579 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_first_period'||l_sum_hol_first_period,4900);
580 --end if;
581 l_sum_hol_prev_year:=l_hol_allow_tax_income + l_retrohol_allow_tax_income;
582
583 --if g_debug then
584 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_prev_year'||l_sum_hol_prev_year,5000);
585 --end if;
586
587 /*convert the holiday allowance taxable income for the first part period to an estimated value
588 for the whole period, adding this to the holiday allowance taxable income for the
589 rest of the year, dividing by the number of periods in service and the multiplying
590 by the total number of payroll periods in the year. */
591
592 l_sum_hol_prev_year:=l_sum_hol_prev_year - l_sum_hol_first_period;
593 --if g_debug then
594 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_prev_year'||l_sum_hol_prev_year,5100);
595 --end if;
596 l_sum_hol_first_period:=l_sum_hol_first_period - l_hol_allow_pay_income_ptd;
597
598 l_sum_hol_first_period:=l_sum_hol_first_period/l_week_days;
599
600 --if g_debug then
601 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_first_period'||l_sum_hol_first_period,5200);
602 --end if;
603
604 l_sum_hol_first_period:=l_sum_hol_first_period * l_total_week_days;
605
606 --if g_debug then
610
607 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_first_period'||l_sum_hol_first_period,5300);
608 --end if;
609 l_sum_hol_prev_year:=l_sum_hol_prev_year - l_hol_allow_pay_income;
611 l_sum_hol_prev_year:=l_sum_hol_prev_year + l_sum_hol_first_period;
612
613 --if g_debug then
614 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_prev_year'||l_sum_hol_prev_year,5400);
615 --end if;
616
617 l_sum_hol_prev_year:=l_sum_hol_prev_year/l_periods_in_service;
618
619 --if g_debug then
620 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_prev_year'||l_sum_hol_prev_year,5500);
621 --end if;
622
623 l_sum_hol_prev_year:=l_sum_hol_prev_year * l_periods_of_year;
624
625 --if g_debug then
626 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_hol_prev_year'||l_sum_hol_prev_year,5600);
627 --end if;
628
629 l_estimated_hol_allow:=l_sum_hol_prev_year * l_hol_allow_perc/100;
630
631 --if g_debug then
632 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_estimated_hol_allow'||l_estimated_hol_allow,5700);
633 --end if;
634 /* Add the estimated holiday allowance to the estimated taxable income, to provide
635 the previous year?s taxable income for this assignment. */
636
637 l_prev_year_income:=l_prev_year_income +l_estimated_hol_allow;
638 --if g_debug then
639 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_prev_year_income'||l_prev_year_income,5800);
640 --end if;
641
642 l_sum_prev_year_income:=l_sum_prev_year_income +l_prev_year_income;
643 --if g_debug then
644 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME: l_sum_prev_year_income'||l_sum_prev_year_income,5900);
645 --end if;
646
647 END IF;
648
649 /* If assignment started in the current year. then set previous year's taxable income to value based on balance Special Rate Income*/
650 IF l_asg_start_date >= l_curr_year_start_date OR l_prev_year_income = 0 THEN
651
652 --
653 l_type := 'DP';
654 IF p_payroll_action_id <> -1 THEN
655 hr_utility.trace('~~VV3');
656 l_ass_act_id := NULL;
657 OPEN get_ass_act_id(l_assignment.assignment_id,l_current_period_start_date,l_current_period_end_date);
658 FETCH get_ass_act_id INTO l_ass_act_id;
659 CLOSE get_ass_act_id;
660 --
661 l_type := pay_nl_general.check_de_dp_dimension(p_payroll_action_id
662 ,l_assignment.assignment_id
663 ,l_ass_act_id);
664 --
665
666 hr_utility.trace('~~VV l_type : '||l_type);
667 IF l_type = 'DE' THEN
668 l_spl_rate_income_dbaid_ptd:=pay_nl_general.get_defined_balance_id('SPECIAL_RATE_INCOME_ASG_BDATE_PTD');
669 IF l_ass_act_id is NOT NULL THEN
670 l_special_rate_income_ptd :=pay_balance_pkg.get_value(p_defined_balance_id=> l_spl_rate_income_dbaid_ptd
671 ,p_assignment_action_id => l_ass_act_id
672 ,p_tax_unit_id => NULL
673 ,p_jurisdiction_code => NULL
674 ,p_source_id => NULL
675 ,p_source_text => NULL
676 ,p_tax_group => NULL
677 ,p_date_earned => NULL
678 ,p_get_rr_route => NULL
679 ,p_get_rb_route => NULL
680 ,p_source_text2 => NULL
681 ,p_source_number => NULL
682 ,p_time_def_id => NULL
683 ,p_balance_date => l_current_period_end_date
684 ,p_payroll_id => NULL);
685 ELSE
686 l_special_rate_income_ptd := 0;
687 END IF;
688 ELSE
689 l_spl_rate_income_dbaid_ptd:=pay_nl_general.get_defined_balance_id('SPECIAL_RATE_INCOME_ASG_PTD');
690 IF l_ass_act_id is NOT NULL THEN
691 pay_balance_pkg.set_context('BALANCE_DATE',NULL);
692 l_special_rate_income_ptd :=pay_balance_pkg.get_value(p_defined_balance_id=> l_spl_rate_income_dbaid_ptd
693 ,p_assignment_action_id => l_ass_act_id
694 ,p_get_rr_route => true
695 ,p_get_rb_route => false);
696 ELSE
697 l_special_rate_income_ptd := 0;
698 END IF;
699 END IF;
700 ELSE
701 hr_utility.trace('~~VV 2');
702 l_spl_rate_income_dbaid_ptd:=pay_nl_general.get_defined_balance_id('SPECIAL_RATE_INCOME_ASG_PTD');
703 l_special_rate_income_ptd :=pay_balance_pkg.get_value(l_spl_rate_income_dbaid_ptd
704 ,l_assignment.assignment_id,l_current_period_end_date);
705 END IF;
706 --
707 l_special_rate_income:=l_special_rate_income_ptd;
711 l_week_days :=pay_nl_si_pkg.get_week_days(l_asg_start_date,l_current_period_end_date);
708 l_special_rate_income_add:=l_special_rate_annual_income;
709
710 IF l_asg_start_date > l_current_period_start_date THEN
712 l_total_week_days:=pay_nl_si_pkg.get_week_days(l_current_period_start_date,l_current_period_end_date);
713
714 IF l_week_days IS NULL OR l_week_days=0 THEN
715 l_week_days:=1;
716 END IF;
717
718 IF l_total_week_days IS NULL THEN
719 l_total_week_days:=0;
720 END IF;
721
722 l_special_rate_income:=(l_special_rate_income_ptd*l_total_week_days)/l_week_days;
723 END IF;
724
725 l_special_rate_annual_income:=l_special_rate_income*l_periods_of_curr_year;
726
727 l_special_rate_annual_income:=FLOOR(l_special_rate_annual_income);
728
729 l_special_rate_annual_income:=l_special_rate_annual_income+l_special_rate_income_add;
730
731 END IF;
732 END LOOP;
733
734 --if g_debug then
735 hr_utility.set_location('Inside GET_PREV_YEAR_TAX_INCOME:l_sum_prev_year_income '||l_sum_prev_year_income,6000);
736 --end if;
737 --Return the Previous Year Taxable Income
738 l_sum_prev_year_income:=FLOOR(l_sum_prev_year_income);
739
740 l_special_rate_annual_income:=FLOOR(l_special_rate_annual_income);
741
742 l_sum_prev_year_income:=l_sum_prev_year_income+l_special_rate_annual_income;
743
744
745 hr_utility.set_location('~~ Final'|| l_sum_prev_year_income,11111);
746 --hr_utility.trace_off();
747 RETURN l_sum_prev_year_income;
748
749
750 EXCEPTION
751 WHEN NO_DATA_FOUND THEN
752 hr_utility.set_location('GET_PREV_YEAR_TAX_INCOME'||SQLERRM||SQLCODE,4200);
753 RETURN 0;
754
755 END GET_PREV_YEAR_TAX_INCOME;
756 --
757 ---------------------------------------------------------------------------
758 -- Procedure: get_balance_values
759 -- Procedure which returns the balance values of a assignment for a given date
760 ---------------------------------------------------------------------------
761
762 Procedure get_balance_values( l_assignment_id IN NUMBER
763 ,l_prev_year_end_date IN DATE
764 ,l_period_end_date IN DATE
765 ,l_std_tax_income OUT NOCOPY NUMBER
766 ,l_spl_tax_income OUT NOCOPY NUMBER
767 ,l_retrostd_tax_income OUT NOCOPY NUMBER
768 ,l_retrostdcurrq_tax_income OUT NOCOPY NUMBER
769 ,l_retrospl_tax_income OUT NOCOPY NUMBER
770 ,l_hol_allow_pay_income OUT NOCOPY NUMBER
771 ,l_hol_allow_tax_income OUT NOCOPY NUMBER
772 ,l_retrohol_allow_tax_income OUT NOCOPY NUMBER
773 ,l_std_tax_income_ptd OUT NOCOPY NUMBER
774 ,l_spl_tax_income_ptd OUT NOCOPY NUMBER
775 ,l_retrostd_tax_income_ptd OUT NOCOPY NUMBER
776 ,l_retrostdcurrq_tax_income_ptd OUT NOCOPY NUMBER
777 ,l_retrospl_tax_income_ptd OUT NOCOPY NUMBER
778 ,l_hol_allow_pay_income_ptd OUT NOCOPY NUMBER
779 ,l_hol_allow_tax_income_ptd OUT NOCOPY NUMBER
780 ,l_rethol_allow_tax_income_ptd OUT NOCOPY NUMBER) IS
781
782
783
784
785 l_std_tax_dbalid NUMBER;
786 l_spl_tax_dbalid NUMBER;
787 l_retrostd_tax_dbalid NUMBER;
788 l_retrostdcurrq_tax_dbalid NUMBER;
789 l_retrospl_tax_dbalid NUMBER;
790 l_hol_allow_pay_dbalid NUMBER;
791 l_hol_allow_tax_dbalid NUMBER;
792 l_retrohol_allow_tax_dbalid NUMBER;
793 l_std_tax_dbalid_ptd NUMBER;
794 l_spl_tax_dbalid_ptd NUMBER;
795 l_retrostd_tax_dbalid_ptd NUMBER;
796 l_retrostdcurrq_tax_dbalid_ptd NUMBER;
797 l_retrospl_tax_dbalid_ptd NUMBER;
798 l_hol_allow_pay_dbalid_ptd NUMBER;
799 l_hol_allow_tax_dbalid_ptd NUMBER;
800 l_retrohol_allow_dbalid_ptd NUMBER;
801
802
803 Begin
804
805 --Get the Defined Balance Id's of the required balances
806 l_std_tax_dbalid :=pay_nl_general.get_defined_balance_id('STANDARD_TAXABLE_INCOME_ASG_YTD');
807 l_spl_tax_dbalid :=pay_nl_general.get_defined_balance_id('SPECIAL_TAXABLE_INCOME_ASG_YTD');
808 l_retrostd_tax_dbalid :=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_ASG_YTD');
809 l_retrostdcurrq_tax_dbalid :=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_YTD');
810 l_retrospl_tax_dbalid :=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAXABLE_INCOME_ASG_YTD');
811
812 l_hol_allow_pay_dbalid :=pay_nl_general.get_defined_balance_id('HOLIDAY_ALLOWANCE_PAYMENT_ASG_YTD');
813 l_hol_allow_tax_dbalid :=pay_nl_general.get_defined_balance_id('HOLIDAY_ALLOWANCE_TAXABLE_INCOME_ASG_YTD');
814 l_retrohol_allow_tax_dbalid :=pay_nl_general.get_defined_balance_id('RETRO_HOLIDAY_ALLOWANCE_TAXABLE_INCOME_ASG_YTD');
815
816
817 l_std_tax_dbalid_ptd :=pay_nl_general.get_defined_balance_id('STANDARD_TAXABLE_INCOME_ASG_PTD');
818 l_spl_tax_dbalid_ptd :=pay_nl_general.get_defined_balance_id('SPECIAL_TAXABLE_INCOME_ASG_PTD');
819 l_retrostd_tax_dbalid_ptd :=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_ASG_PTD');
820 l_retrostdcurrq_tax_dbalid_ptd :=pay_nl_general.get_defined_balance_id('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_PTD');
821 l_retrospl_tax_dbalid_ptd :=pay_nl_general.get_defined_balance_id('RETRO_SPECIAL_TAXABLE_INCOME_ASG_PTD');
822
823
824 l_hol_allow_pay_dbalid_ptd :=pay_nl_general.get_defined_balance_id('HOLIDAY_ALLOWANCE_PAYMENT_ASG_PTD');
825 l_hol_allow_tax_dbalid_ptd :=pay_nl_general.get_defined_balance_id('HOLIDAY_ALLOWANCE_TAXABLE_INCOME_ASG_PTD');
826 l_retrohol_allow_dbalid_ptd :=pay_nl_general.get_defined_balance_id('RETRO_HOLIDAY_ALLOWANCE_TAXABLE_INCOME_ASG_PTD');
827
828
832
829 --if g_debug then
830 hr_utility.set_location('Inside get_balance_values: l_std_tax_dbalid'||l_std_tax_dbalid,2510);
831 --end if;
833 l_std_tax_income :=pay_balance_pkg.get_value(l_std_tax_dbalid
834 ,l_assignment_id
835 ,l_prev_year_end_date);
836 l_spl_tax_income :=pay_balance_pkg.get_value(l_spl_tax_dbalid
837 ,l_assignment_id
838 ,l_prev_year_end_date);
839 l_retrostd_tax_income :=pay_balance_pkg.get_value(l_retrostd_tax_dbalid
840 ,l_assignment_id
841 ,l_prev_year_end_date);
842 l_retrostdcurrq_tax_income :=pay_balance_pkg.get_value(l_retrostdcurrq_tax_dbalid
843 ,l_assignment_id
844 ,l_prev_year_end_date);
845 l_retrospl_tax_income :=pay_balance_pkg.get_value(l_retrospl_tax_dbalid
846 ,l_assignment_id
847 ,l_prev_year_end_date);
848 --if g_debug then
849 hr_utility.set_location('Inside get_balance_values: l_retrospl_tax_income'||l_retrospl_tax_income,2520);
850 --end if;
851
852 l_hol_allow_pay_income :=pay_balance_pkg.get_value(l_hol_allow_pay_dbalid
853 ,l_assignment_id
854 ,l_prev_year_end_date);
855 l_hol_allow_tax_income :=pay_balance_pkg.get_value(l_hol_allow_tax_dbalid
856 ,l_assignment_id
857 ,l_prev_year_end_date);
858 l_retrohol_allow_tax_income :=pay_balance_pkg.get_value(l_retrohol_allow_tax_dbalid
859 ,l_assignment_id
860 ,l_prev_year_end_date);
861
862 --if g_debug then
863 hr_utility.set_location('Inside get_balance_values: l_std_tax_dbalid_ptd'||l_std_tax_dbalid_ptd,2530);
864 hr_utility.set_location('Inside get_balance_values: l_assignment_id'||l_assignment_id,2530);
865 hr_utility.set_location('Inside get_balance_values: l_period_end_date'||l_period_end_date,2530);
866 --end if;
867
868
869 l_std_tax_income_ptd :=pay_balance_pkg.get_value(l_std_tax_dbalid_ptd
870 ,l_assignment_id
871 ,l_period_end_date);
872 --if g_debug then
873 hr_utility.set_location('Inside get_balance_values: l_std_tax_income_ptd'||l_std_tax_income_ptd,2531);
874 --end if;
875
876 l_spl_tax_income_ptd :=pay_balance_pkg.get_value(l_spl_tax_dbalid_ptd
877 ,l_assignment_id
878 ,l_period_end_date);
879 --if g_debug then
880 hr_utility.set_location('Inside get_balance_values: l_spl_tax_income_ptd'||l_spl_tax_income_ptd,2532);
881 --end if;
882
883 l_retrostd_tax_income_ptd :=pay_balance_pkg.get_value(l_retrostd_tax_dbalid_ptd
884 ,l_assignment_id
885 ,l_period_end_date);
886 --if g_debug then
887 hr_utility.set_location('Inside get_balance_values: l_retrostd_tax_income_ptd'||l_retrostd_tax_income_ptd,2533);
888 --end if;
889
890 l_retrostdcurrq_tax_income_ptd :=pay_balance_pkg.get_value(l_retrostdcurrq_tax_dbalid_ptd
891 ,l_assignment_id
892 ,l_period_end_date);
893 --if g_debug then
894 hr_utility.set_location('Inside get_balance_values: l_retrostdcurrq_tax_income_ptd'||l_retrostdcurrq_tax_income_ptd,2534);
895 --end if;
896
897 l_retrospl_tax_income_ptd :=pay_balance_pkg.get_value(l_retrospl_tax_dbalid_ptd
898 ,l_assignment_id
899 ,l_period_end_date);
900 --if g_debug then
901 hr_utility.set_location('Inside get_balance_values: l_retrospl_tax_income_ptd'||l_retrospl_tax_income_ptd,2540);
902 --end if;
903
904 l_hol_allow_pay_income_ptd :=pay_balance_pkg.get_value(l_hol_allow_pay_dbalid_ptd
905 ,l_assignment_id
906 ,l_period_end_date);
907 l_hol_allow_tax_income_ptd :=pay_balance_pkg.get_value(l_hol_allow_tax_dbalid_ptd
908 ,l_assignment_id
909 ,l_period_end_date);
910 l_rethol_allow_tax_income_ptd :=pay_balance_pkg.get_value(l_retrohol_allow_dbalid_ptd
911 ,l_assignment_id
912 ,l_period_end_date);
913 --if g_debug then
914 hr_utility.set_location('Inside get_balance_values: l_rethol_allow_tax_income_ptd'||l_rethol_allow_tax_income_ptd,2550);
915 --end if;
916
917 Exception
918 WHEN NO_DATA_FOUND THEN
919 hr_utility.set_location('get_balance_values'||SQLERRM||SQLCODE,1200);
920 l_std_tax_income:=0;
921 l_spl_tax_income:=0;
922 l_retrostd_tax_income:=0;
923 l_retrostdcurrq_tax_income:=0;
924 l_retrospl_tax_income:=0;
925 l_hol_allow_pay_income:=0;
926 l_hol_allow_tax_income:=0;
927 l_retrohol_allow_tax_income:=0;
928 l_std_tax_income_ptd:=0;
929 l_spl_tax_income_ptd:=0;
930 l_retrostd_tax_income_ptd:=0;
931 l_retrostdcurrq_tax_income_ptd:=0;
932 l_retrospl_tax_income_ptd:=0;
933 l_hol_allow_pay_income_ptd:=0;
934 l_hol_allow_tax_income_ptd:=0;
935 l_rethol_allow_tax_income_ptd:=0;
936
937 END get_balance_values;
938
939
940
941 ---------------------------------------------------------------------------
942 -- Procedure: reset_override_lastyr_sal
943 -- Procedure which resets the override value of all the assignments at the
944 --end of the year
945 ---------------------------------------------------------------------------
946
947 PROCEDURE reset_override_lastyr_sal(errbuf out nocopy varchar2,
948 retcode out nocopy varchar2,
949 p_date in varchar2,
950 p_org_struct_id in number,
951 p_hr_org_id in number,
952 p_business_group_id in number
953 )
954
955 IS
956
957 l_concatenated_segments hr_soft_coding_keyflex.CONCATENATED_SEGMENTS%TYPE;
961 l_effective_end_date per_all_assignments_f.EFFECTIVE_END_DATE%TYPE;
958 l_cagr_grade_def_id per_all_assignments_f.CAGR_GRADE_DEF_ID%TYPE;
959 l_cagr_concatenated_segments per_cagr_grades_def.CONCATENATED_SEGMENTS%TYPE;
960 l_effective_start_date per_all_assignments_f.EFFECTIVE_START_DATE%TYPE;
962 l_comment_id per_all_assignments_f.COMMENT_ID%TYPE;
963 l_soft_coding_keyflex_id per_all_assignments_f.SOFT_CODING_KEYFLEX_ID%TYPE;
964 l_other_manager_warning boolean;
965 l_no_managers_warning boolean;
966 l_hourly_salaried_warning boolean;
967 l_gsp_post_process_warning varchar2(2000);
968 l_object_version_number per_all_assignments_f.OBJECT_VERSION_NUMBER%TYPE;
969 l_end number;
970 l_datetrack_update_mode varchar2(2000);
971
972 ---------------------------------------------------------------------
973 --Cursor fetches details of assignments to be updated
974 ---------------------------------------------------------------------
975 -- Modified for Bug 4200471 to improve performance
976 cursor csr_get_asg_details(p_hr_org_id number,
977 p_date varchar2,
978 p_org_struct_id number,
979 p_business_group_id number) is
980 select asg.assignment_id
981 ,asg.person_id
982 ,asg.object_version_number
983 ,asg.assignment_number
984 ,asg.effective_start_date
985 ,asg.effective_end_date
986 , scl.segment12 last_year_sal
987 , ast.user_status
988 from per_all_assignments_f asg
989 ,hr_soft_coding_keyflex scl
990 , per_assignment_status_types ast
991 where organization_id in
992 (select pose.organization_id_child
993 from per_org_structure_elements pose,per_org_structure_versions posv
994 where
995 posv.org_structure_version_id = pose.org_structure_version_id
996 and posv.organization_structure_id=p_org_struct_id
997 and posv.business_group_id = pose.business_group_id
998 and posv.business_group_id=p_business_group_id
999 UNION ALL
1000 select p_business_group_id FROM DUAL)
1001 and nvl(p_hr_org_id,organization_id)=organization_id
1002 and fnd_date.canonical_to_date(p_date) between effective_start_date and effective_end_date
1003 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1004 and scl.segment12 is not null
1005 and ast.assignment_status_type_id = asg.assignment_status_type_id
1006 and asg.business_group_id = p_business_group_id
1007 and ast.PER_SYSTEM_STATUS='ACTIVE_ASSIGN';
1008
1009 --l_asg_details csr_get_asg_details%ROWTYPE;
1010
1011
1012 Begin
1013 --hr_utility.trace_on(null,'RESET_OVERRIDE');
1014 --hr_utility.trace('In reset override');
1015
1016 retcode := 0;
1017
1018 --------------------------------------------------------------------
1019 --Loop through all assignments and update them
1020 --------------------------------------------------------------------
1021 for l_asg_details in csr_get_asg_details(p_hr_org_id,p_date,p_org_struct_id,p_business_group_id) loop
1022 l_object_version_number := l_asg_details.object_version_number ;
1023 if l_asg_details.effective_start_date = fnd_date.canonical_to_date(p_date) then
1024 l_datetrack_update_mode := 'CORRECTION';
1025 elsif
1026 l_asg_details.effective_end_date <> hr_general.end_of_time then
1027 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1028 else
1029 l_datetrack_update_mode := 'UPDATE';
1030 end if;
1031 Begin
1032 --hr_utility.trace(l_datetrack_update_mode);
1033 --hr_utility.trace(l_asg_details.assignment_id);
1034 --hr_utility.trace(l_asg_details.assignment_number);
1035 --hr_utility.trace(l_asg_details.effective_start_date);
1036 --hr_utility.trace(l_asg_details.effective_end_date);
1037 --hr_utility.trace(fnd_date.canonical_to_date(p_date));
1038 --hr_utility.trace(hr_general.end_of_time);
1039
1040 --fnd_file.put_line(fnd_file.log,l_asg_details.assignment_id);
1041 l_soft_coding_keyflex_id := NULL; -- Bug 5763286
1042 hr_nl_assignment_api.update_nl_emp_asg
1043 (p_validate => FALSE
1044 ,p_effective_date => fnd_date.canonical_to_date(p_date)
1045 ,p_person_id => l_asg_details.person_id
1046 ,p_datetrack_update_mode => l_datetrack_update_mode
1047 ,p_assignment_id => l_asg_details.assignment_id
1048 ,p_object_version_number => l_object_version_number
1049 ,p_assignment_number => l_asg_details.assignment_number
1050 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1051 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1052 ,p_concatenated_segments => l_concatenated_segments
1053 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1054 ,p_comment_id => l_comment_id
1055 ,p_last_year_salary => NULL
1056 ,p_effective_start_date => l_effective_start_date
1057 ,p_effective_end_date => l_effective_end_date
1058 ,p_no_managers_warning => l_no_managers_warning
1059 ,p_other_manager_warning => l_other_manager_warning
1060 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1061 ,p_gsp_post_process_warning => l_gsp_post_process_warning
1062 );
1063 Exception
1064 WHEN others THEN
1065 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
1066 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
1067 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1068 hr_utility.raise_error;
1069 end;
1070 end loop;
1071
1072 End reset_override_lastyr_sal ;
1073
1077 ---------------------------------------------------------------------------
1074 ---------------------------------------------------------------------------
1075 -- Procedure: end_of_year_process
1076 -- Generic Procedure for end of the year process
1078
1079 Procedure end_of_year_process (errbuf out nocopy varchar2,
1080 retcode out nocopy varchar2,
1081 p_date in varchar2,
1082 p_org_struct_id in number,
1083 p_hr_org_id in number,
1084 p_business_group_id in number) IS
1085
1086 Begin
1087
1088 --Call the reset_override_lastyr_sal procedure to reset the override last year salary field
1089
1090 reset_override_lastyr_sal(errbuf,
1091 retcode,
1092 p_date,
1093 p_org_struct_id,
1094 p_hr_org_id,
1095 p_business_group_id
1096 );
1097 End end_of_year_process;
1098
1099 ---------------------------------------------------------------------------
1100 -- Procedure: update_assignments
1101 --Procedure which does the datetrack update of all the assignments of a
1102 --person with override value
1103 ---------------------------------------------------------------------------
1104
1105 Procedure update_assignments (p_assignment_id IN NUMBER
1106 ,p_person_id IN NUMBER
1107 ,p_effective_date IN DATE
1108 ,p_override_value IN NUMBER
1109 ,p_dt_update_mode IN VARCHAR2) IS
1110
1111 CURSOR get_all_assignments (p_person_id NUMBER
1112 ,p_effective_date DATE) IS
1113 select paa.assignment_id
1114 ,paa.effective_start_date
1115 ,paa.effective_end_date
1116 ,paa.object_version_number
1117 ,hsck.segment12
1118 from per_all_assignments_f paa
1119 ,hr_soft_coding_keyflex hsck
1120 ,per_assignment_status_types pas
1121 where person_id=p_person_id
1122 and paa.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id (+)
1123 and pas.assignment_status_type_id=paa.assignment_status_type_id
1124 and pas.PER_SYSTEM_STATUS='ACTIVE_ASSIGN'
1125 and pas.business_group_id IS NULL and pas.legislation_code IS NULL
1126 and p_effective_date between effective_start_date and effective_end_date;
1127
1128
1129 l_concatenated_segments hr_soft_coding_keyflex.CONCATENATED_SEGMENTS%TYPE;
1130 l_cagr_grade_def_id per_all_assignments_f.CAGR_GRADE_DEF_ID%TYPE;
1131 l_cagr_concatenated_segments per_cagr_grades_def.CONCATENATED_SEGMENTS%TYPE;
1132 l_effective_start_date per_all_assignments_f.EFFECTIVE_START_DATE%TYPE;
1133 l_effective_end_date per_all_assignments_f.EFFECTIVE_END_DATE%TYPE;
1134 l_comment_id per_all_assignments_f.COMMENT_ID%TYPE;
1135 l_soft_coding_keyflex_id per_all_assignments_f.SOFT_CODING_KEYFLEX_ID%TYPE;
1136 l_other_manager_warning boolean;
1137 l_no_managers_warning boolean;
1138 l_hourly_salaried_warning boolean;
1139 l_gsp_post_process_warning varchar2(2000);
1140 l_object_version_number per_all_assignments_f.OBJECT_VERSION_NUMBER%TYPE;
1141
1142 l_datetrack_update_mode varchar2(2000);
1143
1144
1145 Begin
1146 --hr_utility.trace_on(NULL,'NL_LYS');
1147 if g_debug then
1148 hr_utility.set_location('Inside update_assignments: p_person_id'||p_person_id,900);
1149 hr_utility.set_location('Inside update_assignments: p_effective_date'||p_effective_date,900);
1150 hr_utility.set_location('Inside update_assignments: p_override_value'||p_override_value,900);
1151 end if;
1152
1153 FOR l_assignment IN get_all_assignments(p_person_id ,p_effective_date) LOOP
1154
1155 if g_debug then
1156 hr_utility.set_location('Inside update_assignments: l_object_version_number'||l_object_version_number,1000);
1157 hr_utility.set_location('Inside update_assignments: p_override_value'||p_override_value,1000);
1158 end if;
1159 /* l_datetrack_update_mode:='UPDATE';
1160
1161 if g_debug then
1162 hr_utility.set_location('Inside l_assignment.effective_start_date'||l_assignment.effective_start_date,1150);
1163 hr_utility.set_location('Inside l_assignment.effective_end_date'||l_assignment.effective_end_date,1150);
1164 end if;
1165
1166 IF p_effective_date = l_assignment.effective_start_date THEN
1167 l_datetrack_update_mode:='CORRECTION';
1168 ELSIF l_assignment.effective_end_date <> hr_general.end_of_time THEN
1169 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
1170 END IF; */
1171
1172 if g_debug then
1173 hr_utility.set_location('Inside l_datetrack_update_mode'||l_datetrack_update_mode,1200);
1174 hr_utility.set_location('Inside p_assignment_id'||p_assignment_id,1200);
1175 hr_utility.set_location('Inside l_assignment.assignment_id'||l_assignment.assignment_id,1200);
1176 hr_utility.set_location('Inside l_assignment.segment12'||l_assignment.segment12,1200);
1177 hr_utility.set_location('Inside update_assignments: p_override_value'||fnd_number.number_to_canonical(p_override_value),1200);
1178 end if;
1179
1180 IF p_assignment_id <> l_assignment.assignment_id and /*4606747*/ nvl(p_override_value,0) <> nvl(fnd_number.canonical_to_number(l_assignment.segment12),0) THEN
1181 hr_utility.set_location('Inside If '||l_assignment.segment12,1200);
1182 l_soft_coding_keyflex_id := NULL; -- Bug 5763286
1183 hr_nl_assignment_api.update_nl_emp_asg
1184 (p_validate => FALSE
1185 ,p_effective_date => p_effective_date
1186 ,p_person_id => p_person_id
1187 ,p_datetrack_update_mode => p_dt_update_mode
1188 ,p_assignment_id => l_assignment.assignment_id
1189 ,p_object_version_number => l_assignment.object_version_number
1190 ,p_last_year_salary => fnd_number.number_to_canonical(p_override_value)
1191 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1192 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1193 ,p_concatenated_segments => l_concatenated_segments
1194 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1195 ,p_comment_id => l_comment_id
1196 ,p_effective_start_date => l_effective_start_date
1197 ,p_effective_end_date => l_effective_end_date
1198 ,p_no_managers_warning => l_no_managers_warning
1199 ,p_other_manager_warning => l_other_manager_warning
1200 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1201 ,p_gsp_post_process_warning => l_gsp_post_process_warning);
1202
1203 END IF;
1204 END LOOP;
1205 --commit; /*commented for bug 4058149 */
1206 Exception
1207 When Others Then
1208 hr_utility.set_location('In update_assignments SQLERRM'||SQLERRM||'SQLCODE'||SQLCODE,2000);
1209
1210 End update_assignments;
1211 END pay_nl_eoy_pkg;