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