[Home] [Help]
PACKAGE BODY: APPS.PQP_ITERATIVE_ARREARAGE
Source
1 Package Body PQP_Iterative_Arrearage As
2 /* $Header: pqpitarr.pkb 115.4 2003/07/11 15:30:18 rpinjala noship $ */
3
4
5 Cursor csr_leg_code (c_business_grp_id In Number
6 ) Is
7 select pbg.legislation_code
8 from per_business_groups_perf pbg
9 where pbg.business_group_id = c_business_grp_id;
10
11 Cursor csr_org_info (c_business_grp_id In Number
12 ,c_org_information_context In Varchar2) Is
13 select hoi.org_information1
14 ,hoi.org_information2
15 from hr_organization_information hoi
16 where hoi.organization_id = c_business_grp_id
17 and hoi.org_information_context = c_org_information_context;
18
19 -- ================================================================
20 -- ~Get_Arrearage_Info: fetchs the Arrears allowed and Partial
21 -- ~allowed from the element extra information table based on the
22 -- ~legislation code, as this package would be common for all
23 -- ~legislations which want to enable Pre-Tax Iterative arrearage.
24 -- ================================================================
25 Procedure Get_Arrearage_Info
26 (p_ele_type_id In Number
27 ,p_assignment_id In Number
28 ,p_business_grp_id In Number
29 ,p_effective_date In Date
30 ,p_arrears_allowed Out NoCopy Varchar2
31 ,p_partial_allowed Out NoCopy Varchar2
32 ) Is
33 l_legislation_code per_business_groups_perf.legislation_code%TYPE;
34 Cursor csr_arr_info (c_ele_type_id In Number
35 ,c_business_grp_id In Number
36 ,c_effective_date In Date
37 ,c_information_type In Varchar2) Is
38 select pei.eei_information1 -- Arrears Allowed
39 ,pei.eei_information2 -- Partial Allowed
40 from pay_element_types_f pet,
41 pay_element_type_extra_info pei
42 where pet.element_type_id = c_ele_type_id
43 and (pet.business_group_id = c_business_grp_id or
44 pet.legislation_code = l_legislation_code)
45 and pei.element_type_id = pet.element_type_id
46 and pei.information_type = c_information_type
47 and trunc(c_effective_date) between pet.effective_start_date
48 and pet.effective_end_date;
49
50 l_ele_information_type pay_element_type_extra_info.information_type%TYPE;
51 l_org_information_type hr_organization_information.org_information_context%TYPE;
52 l_arr_info_rec csr_arr_info%ROWTYPE;
53 l_org_info_rec csr_org_info%ROWTYPE;
54 l_calc_method hr_organization_information.org_information1%TYPE;
55 l_to_with_amt number;
56 l_process_name Varchar2(150) := g_pkg_name||'Get_Arrearage_Info';
57 Begin
58 hr_utility.set_location('Entering: '||l_process_name, 5);
59 If g_legislation_code Is Null Then
60 Open csr_leg_code (c_business_grp_id => p_business_grp_id);
61 Fetch csr_leg_code Into l_legislation_code;
62 g_legislation_code := l_legislation_code;
63 If csr_leg_code%NOTFOUND Then
64 hr_utility.set_location('Could not find the Leg Code :',10);
65 Null;
66 End If;
67 Close csr_leg_code;
68 End If;
69 hr_utility.set_location('Leg. Code :'||l_legislation_code,10);
70 If g_legislation_code = 'GB' Then
71 l_ele_information_type := 'PQP_GB_ARREARAGE_INFO';
72 l_org_information_type := 'PQP_GB_ITERATIVE_RULES';
73 Elsif g_legislation_code = 'NL' Then
74 l_ele_information_type := 'PQP_NL_ARREARAGE_INFO';
75 l_org_information_type := 'PQP_NL_ITERATIVE_RULES';
76 End If;
77 hr_utility.set_location('Information Type :'||l_ele_information_type,15);
78 If l_ele_information_type = 'PQP_GB_ARREARAGE_INFO' Then
79 Open csr_arr_info (c_ele_type_id => p_ele_type_id
80 ,c_business_grp_id => p_business_grp_id
81 ,c_effective_date => p_effective_date
82 ,c_information_type => l_ele_information_type);
83 Fetch csr_arr_info Into l_arr_info_rec;
84 If csr_arr_info%FOUND Then
85 hr_utility.set_location('Found Arrearage Info :',20);
86 p_arrears_allowed := l_arr_info_rec.eei_information1;
87 p_partial_allowed := l_arr_info_rec.eei_information2;
88 Else
89 hr_utility.set_location('Defaulting Arrearage Info :',20);
90 p_arrears_allowed := 'N';
91 p_partial_allowed := 'N';
92 End If;
93 Close csr_arr_info;
94 ElsIf l_ele_information_type = 'PQP_NL_ARREARAGE_INFO' Then
95 Null;
96 End If;
97 If l_org_information_type = 'PQP_GB_ITERATIVE_RULES' Then
98 Open csr_org_info (c_business_grp_id => p_business_grp_id
99 ,c_org_information_context => l_org_information_type );
100 Fetch csr_org_info Into l_org_info_rec;
101 If csr_org_info%FOUND Then
102 l_calc_method := nvl(l_org_info_rec.org_information1,'INTERPOLATION');
103 l_to_with_amt := nvl(l_org_info_rec.org_information2,1);
104 Else
105 l_calc_method := 'INTERPOLATION';
106 l_to_with_amt := 1;
107 End If;
108 g_Itr_Method(p_business_grp_id).iterative_method := UPPER(l_calc_method);
109 g_Itr_Method(p_business_grp_id).adjust_to_within := l_to_with_amt;
110 Close csr_org_info;
111 ElsIf l_org_information_type = 'PQP_NL_ITERATIVE_RULES' Then
112 Null;
113 End If;
114
115 hr_utility.set_location('Leaving : '||l_process_name,90);
116 Exception
117 When Others Then
118 hr_utility.set_location('Error Code: '||SQLCODE,130);
119 hr_utility.set_location('Error Msg : '||SQLERRM,140);
120 hr_utility.set_location('Leaving : '||l_process_name,150);
121 End Get_Arrearage_Info;
122
123 -- ============================================================
124 -- ~Get_Arrearage_Options: Returns the Arrears Allowed and
125 -- ~ Partial allowed for any element for the EIT context
126 -- ~ Arrearage Options : PQP_GB_ARREARAGE_OPTIONS
127 -- ============================================================
128 Function Get_Arrearage_Options
129 (p_ele_type_id In Number
130 ,p_assignment_id In Number
131 ,p_business_grp_id In Number
132 ,p_effective_date In Date
133 ,p_arrears_allowed Out NoCopy Varchar2
134 ,p_partial_allowed Out NoCopy Varchar2
135 ,p_error_message Out NoCopy Varchar2
136 ) Return Number Is
137 l_process_name Varchar2(150) := g_pkg_name||'Get_Arrearage_Options';
138 l_return_value number := 0;
139 Begin
140 hr_utility.set_location('Entering: '||l_process_name, 5);
141 Get_Arrearage_Info
142 (p_ele_type_id => p_ele_type_id
143 ,p_assignment_id => p_assignment_id
144 ,p_business_grp_id => p_business_grp_id
145 ,p_effective_date => p_effective_date
146 ,p_arrears_allowed => p_arrears_allowed
147 ,p_partial_allowed => p_partial_allowed
148 );
149 hr_utility.set_location('Leaving : '||l_process_name,90);
150 Return l_return_value;
151
152 Exception
153 When Others Then
154 hr_utility.set_location('Error Code: '||SQLCODE,130);
155 hr_utility.set_location('Error Msg : '||SQLERRM,140);
156 p_error_message := 'Exception: When Others at :'||l_process_name;
157 hr_utility.set_location('Leaving : '||l_process_name,150);
158 l_return_value := -1;
159 Raise;
160 End Get_Arrearage_Options;
161 -- ============================================================
162 -- ~Arrearage: Takes into consideration the arrears amount
163 -- ~based on the arrears allowed and partial allowed flag.
164 -- ~This would be called directly for Vol. Deductions.
165 -- ============================================================
166 Function Arrearage
167 (p_eletype_id In Number -- Context Parameter
168 ,p_ele_entryid In Number -- Context Parameter
169 ,p_assignment_id In Number -- Context Parameter
170 ,p_business_grp_id In Number -- Context Parameter
171 ,p_assignment_action_Id In Number -- Context Parameter
172 ,p_date_earned In Date -- Context Parameter
173 ,p_net_asg_run In Number
174 ,p_maxarrears In Number
175 ,p_dedn_amt In Number
176 ,p_to_arrears In Out NoCopy Number
177 ,p_not_taken In Out NoCopy Number
178 ,p_arrears_taken In Out NoCopy Number
179 ,p_remaining_amount In Number -- Optional Parameter
180 ,p_guaranteed_net In Number -- Optional Parameter
181 ,p_partial_allowed In Varchar2 -- Optional Parameter
182 ,p_arrears_allowed In Varchar2 -- Optional Parameter
183 ) Return Number Is
184
185 l_dedn_amt Number;
186 l_total_dedn Number;
187 l_maxarrears_tobetaken Number;
188 l_arrears_allowed Varchar2(2);
189 l_partial_allowed Varchar2(2);
190 l_process_name Varchar2(150) := g_pkg_name||'Arrearage';
191 Begin
192 hr_utility.set_location('Entering: '||l_process_name, 5);
193 p_to_arrears := 0;
194 p_not_taken := 0;
195 If p_partial_allowed Is Null And
196 p_arrears_allowed Is Null Then
197 If g_Element_Arr_Values.EXISTS(p_eletype_id) Then
198 l_arrears_allowed := g_Element_Arr_Values(p_eletype_id).arrears_allowed;
199 l_partial_allowed := g_Element_Arr_Values(p_eletype_id).partial_allowed;
200 Else
201 Get_Arrearage_Info
202 (p_ele_type_id => p_eletype_id
203 ,p_assignment_id => p_assignment_id
204 ,p_business_grp_id => p_business_grp_id
205 ,p_effective_date => p_date_earned
206 ,p_arrears_allowed => l_arrears_allowed
207 ,p_partial_allowed => l_partial_allowed
208 );
209 g_Element_Arr_Values(p_eletype_id).arrears_allowed := l_arrears_allowed;
210 g_Element_Arr_Values(p_eletype_id).partial_allowed := l_partial_allowed;
211 End If;
212 Else
213 g_Element_Arr_Values(p_eletype_id).arrears_allowed := p_arrears_allowed;
214 g_Element_Arr_Values(p_eletype_id).partial_allowed := p_partial_allowed;
215 l_arrears_allowed := p_arrears_allowed;
216 l_partial_allowed := p_partial_allowed;
217 End If;
218 hr_utility.set_location('..l_arrears_allowed: '||l_arrears_allowed, 6);
219 hr_utility.set_location('..l_partial_allowed: '||l_partial_allowed, 7);
220 hr_utility.set_location('..p_dedn_amt: '||p_dedn_amt, 8);
221 hr_utility.set_location('..p_guaranteed_net: '||p_guaranteed_net, 9);
222 If l_arrears_allowed = 'N' Then
223 -- Arrears are not allowed
224 hr_utility.set_location('Arrearage not allowed',10);
225 If p_net_asg_run - p_dedn_amt >= p_guaranteed_net Then
226 -- There are enough earnings to take the entire deduction amount.
227 hr_utility.set_location('Enough earnings to take the entire ded. amount', 15);
228 p_to_arrears := 0;
229 p_not_taken := 0;
230 l_dedn_amt := p_dedn_amt;
231
232 ElsIf p_net_asg_run <= p_guaranteed_net Then
233 -- Not enough earnings to take the deduction and as arrears are not
234 -- allowed the entire deduction amount goes as not taken.
235 hr_utility.set_location('Not enough earnings, so ded. amt goes as not taken amt',20);
236 p_to_arrears := 0;
237 p_not_taken := p_dedn_amt;
238 l_dedn_amt := 0;
239
240 ElsIf p_net_asg_run - p_dedn_amt < p_guaranteed_net and
241 l_partial_allowed = 'Y' Then
242 -- If partial deduction amount can be taken and partial deductions are
243 -- allowed for the element then take partial amount and the rest goes
244 -- as not taken amount.
245 hr_utility.set_location('Partial allowed and part of the ded. amt can be taken',25);
246 p_to_arrears := 0;
247 p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
248 l_dedn_amt := p_net_asg_run - p_guaranteed_net;
249
250 ElsIf p_net_asg_run - p_dedn_amt < p_guaranteed_net and
251 l_partial_allowed = 'N' Then
252 -- Even if partial deduction amount can be taken but partial deduction
253 -- is NOT allowed then the entire amount is considered as not_taken.
254 hr_utility.set_location('Partial NOT allowed so entire ded. amt goes as not taken amt',30);
255 p_to_arrears := 0;
256 p_not_taken := p_dedn_amt;
257 l_dedn_amt := 0;
258 End If;
259 Else -- Arrearage is on, try and clear any balance currently in arrears.
260 hr_utility.set_location('Arrearage Allowed',35);
261 If p_net_asg_run <= p_guaranteed_net Then
262 -- Earnings are not enough to take the deduction amount, hence put the
263 -- entire amount into arrears as well as not_taken.
264 hr_utility.set_location('Earnings not enough, so ded. amt goes into arrears',40);
265 p_to_arrears := p_dedn_amt;
266 p_not_taken := p_dedn_amt;
267 l_dedn_amt := 0;
268 Else
269 -- take into consideration the arrears amount.
270 hr_utility.set_location('Take the arrears amount if present',45);
271 If p_remaining_amount = p_maxarrears Then
272 p_arrears_taken := p_maxarrears;
273 l_total_dedn := p_dedn_amt + p_arrears_taken;
274 Else
275 p_arrears_taken := Least(p_remaining_amount, p_maxarrears);
276 l_total_dedn := p_dedn_amt + p_arrears_taken;
277 End If;
278 If p_net_asg_run - p_guaranteed_net >= l_total_dedn Then
279 -- Enough earnings to the arrears along with the deduction amount
280 hr_utility.set_location('Earnings enough to take the ded + Arr Amt',50);
281 p_to_arrears := -1 * p_arrears_taken;
282 l_dedn_amt := l_total_dedn;
283 p_not_taken := 0;
284 ElsIf l_partial_allowed = 'Y' Then
285 -- Earnings are not enough to taken the deduction amount so try taking
286 -- partial dedn. amount as partial is allowed for the element.
287 p_to_arrears := (l_total_dedn - (p_net_asg_run - p_guaranteed_net)) +
288 (-1 * p_arrears_taken);
289 p_arrears_taken := abs(p_to_arrears);
290 If (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt Then
291 hr_utility.set_location('Enough earnings to take the entire ded amt',55);
292 p_not_taken := 0;
293 Else
294 p_not_taken := p_dedn_amt - (p_net_asg_run - p_guaranteed_net);
295 hr_utility.set_location('Earnings NOT enough and taking partial amt',60);
296 End If;
297 l_dedn_amt := p_net_asg_run - p_guaranteed_net;
298
299 ElsIf l_partial_allowed = 'N' Then
300 hr_utility.set_location('Partial NOT allowed',65);
301 If (p_net_asg_run - p_guaranteed_net) >= p_dedn_amt Then
302 hr_utility.set_location('Enough earnings to take the entire ded. amt',70);
303 l_dedn_amt := p_dedn_amt;
304 p_to_arrears := 0;
305 p_not_taken := 0;
306 Else
307 hr_utility.set_location('Earnings NOT enough, ded.amt goes into arrears',75);
308 p_to_arrears := p_dedn_amt;
309 p_not_taken := p_dedn_amt;
310 l_dedn_amt := 0;
311 End If;
312 End If;
313 End If;
314 End If;
315 hr_utility.set_location('To Arrears ='||p_to_arrears,40);
316 hr_utility.set_location('Not Taken ='||p_not_taken,45);
317 hr_utility.set_location('Ded. Amount ='||l_dedn_amt,50);
318 hr_utility.set_location('Leaving : '||l_process_name,150);
319 Return l_dedn_amt;
320
321 Exception
322 When Others Then
323 l_dedn_amt := 0;
324 hr_utility.set_location('Error Code :'||SQLCODE,150);
325 hr_utility.set_location('Error Msg :'||SQLERRM,160);
326 hr_utility.set_location('Leaving :'||l_process_name, 170);
327 Raise;
328
329 END Arrearage;
330
331 -- ============================================================
332 -- ~Iterative_Arrearage: This would be called directly for
333 -- ~Pre-Tax Deductions only.
334 -- ============================================================
335 FUNCTION Iterative_Arrearage
336 (p_eletype_id In Number -- Context Parameter
337 ,p_ele_entryid In Number -- Context Parameter
338 ,p_assignment_id In Number -- Context Parameter
339 ,p_business_grp_id In Number -- Context Parameter
340 ,p_assignment_action_Id In Number -- Context Parameter
341 ,p_date_earned In Date -- Context Parameter
342 ,p_net_asg_run In Number
343 ,p_maxarrears In Number
344 ,p_dedn_amt In Number
345 ,p_maxdesired_amt In Number
346 ,p_iter_count In Number
347 ,p_inserted_flag In Varchar2
348 ,p_to_arrears In Out NoCopy Number
349 ,p_not_taken In Out NoCopy Number
350 ,p_arrears_taken In Out NoCopy Number
351 ,p_error_message Out NoCopy Varchar2
352 ,p_warning_message Out NoCopy Varchar2
353 ,p_remaining_amount In Number -- Optional Parameter
354 ,p_guaranteed_net In Number -- Optional Parameter
355 ,p_partial_allowed In Varchar2 -- Optional Parameter
356 ,p_arrears_allowed In Varchar2 -- Optional Parameter
357 ) Return Number Is
358
359 l_dedn_amt Number := 0;
360 l_actual_usercalc_amt Number := 0;
361 l_arrears_allowed Varchar2(2);
362 l_partial_allowed Varchar2(2);
363 l_process_name Varchar2(150) := g_pkg_name||'Iterative_Arrearage';
364
365 Begin
366 hr_utility.set_location('Entering: '||l_process_name, 5);
367 If Trim(p_partial_allowed) Is Null And
368 Trim(p_arrears_allowed) Is Null Then
369 If g_Element_Arr_Values.EXISTS(p_eletype_id) Then
370 l_arrears_allowed := g_Element_Arr_Values(p_eletype_id).arrears_allowed;
371 l_partial_allowed := g_Element_Arr_Values(p_eletype_id).partial_allowed;
372 Else
373 Get_Arrearage_Info
374 (p_ele_type_id => p_eletype_id
375 ,p_assignment_id => p_assignment_id
376 ,p_business_grp_id => p_business_grp_id
377 ,p_effective_date => p_date_earned
378 ,p_arrears_allowed => l_arrears_allowed
379 ,p_partial_allowed => l_partial_allowed
380 );
381 g_Element_Arr_Values(p_eletype_id).arrears_allowed := l_arrears_allowed;
382 g_Element_Arr_Values(p_eletype_id).partial_allowed := l_partial_allowed;
383 End If;
384 Else
385 g_Element_Arr_Values(p_eletype_id).arrears_allowed := p_arrears_allowed;
386 g_Element_Arr_Values(p_eletype_id).partial_allowed := p_partial_allowed;
387 l_arrears_allowed := p_arrears_allowed;
388 l_partial_allowed := p_partial_allowed;
389 End If;
390 hr_utility.set_location('..l_arrears_allowed: '||l_arrears_allowed, 6);
391 hr_utility.set_location('..l_partial_allowed: '||l_partial_allowed, 7);
392
393 If p_iter_count <= 1 Then
394 If g_Element_Values.EXISTS(p_ele_entryid) Then
395 l_actual_usercalc_amt := g_Element_Values(p_ele_entryid).actual_usercalc_amt;
396 End If;
397 If p_inserted_flag ='N' Then
398 l_actual_usercalc_amt := p_dedn_amt;
399 Else
400 l_actual_usercalc_amt := NVL(l_actual_usercalc_amt,0);
401 End If;
402 hr_utility.set_location('Deduction formula call the first time', 10);
403 hr_utility.set_location('Iteration Count='||p_iter_count,15);
404 l_dedn_amt := Arrearage
405 (p_eletype_id => p_eletype_id
406 ,p_ele_entryid => p_ele_entryid
407 ,p_assignment_id => p_assignment_id
408 ,p_business_grp_id => p_business_grp_id
409 ,p_assignment_action_Id => p_assignment_action_Id
410 ,p_date_earned => p_date_earned
411 ,p_net_asg_run => p_net_asg_run
412 ,p_maxarrears => p_maxarrears
413 ,p_dedn_amt => l_actual_usercalc_amt
414 ,p_to_arrears => p_to_arrears
415 ,p_not_taken => p_not_taken
416 ,p_arrears_taken => p_arrears_taken
417 ,p_remaining_amount => p_remaining_amount
418 ,p_guaranteed_net => p_guaranteed_net
419 ,p_partial_allowed => l_partial_allowed
420 ,p_arrears_allowed => l_arrears_allowed
421 );
422 Else
423 hr_utility.set_location('Iteration Count='||p_iter_count,15);
424 p_to_arrears := 0;
425 p_not_taken := 0;
426
427 If l_arrears_allowed = 'N' And
428 l_partial_allowed = 'N' Then
429 hr_utility.set_location('Arrears as well as Partial amount is not allowed',20);
430 p_to_arrears := 0;
431 If p_dedn_amt <> p_maxdesired_amt Then
432 p_not_taken := p_maxdesired_amt;
433 l_dedn_amt := 0;
434 Else
435 p_not_taken := 0;
436 l_dedn_amt := p_maxdesired_amt;
437 End If;
438 ElsIf l_arrears_allowed = 'N' And
439 l_partial_allowed = 'Y' Then
440 hr_utility.set_location('Arrears not allowed, but Partial deduction is allowed',25);
441 p_to_arrears := 0;
442 p_not_taken := p_maxdesired_amt - p_dedn_amt;
443 l_dedn_amt := p_dedn_amt;
444 ElsIf l_arrears_allowed = 'Y' And
445 l_partial_allowed = 'N' Then
446 hr_utility.set_location('Arrears is allowed , but Partial deduction is not allowed',30);
447 If p_dedn_amt < p_maxdesired_amt Then
448 p_to_arrears := p_maxdesired_amt;
449 p_not_taken := p_maxdesired_amt;
450 l_dedn_amt := 0;
451 Else
452 p_to_arrears := 0;
453 p_not_taken := 0;
454 l_dedn_amt := p_dedn_amt;
455 End If;
456 ElsIf l_arrears_allowed = 'Y' And
457 l_partial_allowed = 'Y' Then
458 hr_utility.set_location('Arrears as well as Partial deduction is allowed',35);
459 If p_dedn_amt >= p_maxdesired_amt Then
460 p_not_taken := 0;
461 p_to_arrears := p_maxdesired_amt - p_dedn_amt;
462 Else
463 p_not_taken := p_maxdesired_amt - p_dedn_amt;
464 p_to_arrears := p_maxdesired_amt - p_dedn_amt;
465 End If;
466 l_dedn_amt := p_dedn_amt;
467 End If;
468 End If; -- p_iter_count = 1
469 hr_utility.set_location('To Arrears ='||p_to_arrears,40);
470 hr_utility.set_location('Not Taken ='||p_not_taken,45);
471 hr_utility.set_location('Ded. Amount ='||l_dedn_amt,50);
472 hr_utility.set_location('Leaving: '||l_process_name,150);
473 Return l_dedn_amt;
474 Exception
475 When Others Then
479 hr_utility.set_location('Error Msg :'||SQLERRM,160);
476 l_dedn_amt := 0;
477 p_error_message := 'Exception: When Others at :'||l_process_name;
478 hr_utility.set_location('Error Code :'||SQLCODE,150);
480 hr_utility.set_location('Leaving :'||l_process_name, 170);
481 Raise;
482 END Iterative_Arrearage;
483
484 -- ============================================================
485 -- ~Set_Iteration_Values:
486 -- ============================================================
487 Function Set_Iteration_Values
488 (p_ele_entryid In Number -- Context Parameter
489 ,p_assignment_id In Number -- Context Parameter
490 ,p_business_grp_id In Number -- Context Parameter
491 ,p_assignment_action_Id In Number -- Context Parameter
492 ,p_eletype_id In Number -- Context Parameter
493 ,p_date_earned In Date -- Context Parameter
494 ,p_iter_count In Number
495 ,p_max_amount In Number
496 ,p_min_amount In Number
497 ,p_maxdesired_amt In Number
498 ,p_deduction_amount In Number
499 ,p_actual_usercalc_amt In Number
500 ,p_clr_add_amt In Number
501 ,p_clr_rep_amt In Number
502 ,p_stopper_flag In Varchar2
503 ,p_inserted_flag In Varchar2
504 ,p_calc_method In Varchar2
505 ,p_to_within In Number
506 ,p_error_message Out NoCopy Varchar2
507 ,p_warning_message Out NoCopy Varchar2
508 ) Return Number Is
509
510 l_return_value Number := 0;
511 l_arrears_allowed Varchar2(2);
512 l_partial_allowed Varchar2(2);
513 l_calc_method hr_organization_information.org_information1%TYPE;
514 l_to_with_amt number;
515 l_process_name Varchar2(250) := g_pkg_name||'Set_Iteration_Values';
516 Begin
517
518 hr_utility.set_location('Entering: '||l_process_name, 5);
519 If g_Element_Values.EXISTS(p_ele_entryid) Then
520 hr_utility.set_location('Element Entry exits in PL/SQL table'||p_ele_entryid,10);
521 Else
522 hr_utility.set_location('Element Entry does NOT exists in PL/SQL table'||p_ele_entryid,10);
523 End If;
524 hr_utility.set_location('ded amt='||p_deduction_amount,15);
525 hr_utility.set_location('max amt='||p_max_amount, 20);
526 hr_utility.set_location('min amt='||p_min_amount, 25);
527 hr_utility.set_location('max desired amt='||p_maxdesired_amt,30);
528 If g_Element_Arr_Values.EXISTS(p_eletype_id) Then
529 l_arrears_allowed := g_Element_Arr_Values(p_eletype_id).arrears_allowed;
530 l_partial_allowed := g_Element_Arr_Values(p_eletype_id).partial_allowed;
531 Else
532 l_arrears_allowed := 'N';
533 l_partial_allowed := 'N';
534 End If;
535 If g_Itr_Method.EXISTS(p_business_grp_id) Then
536 l_calc_method := g_Itr_Method(p_business_grp_id).iterative_method;
537 l_to_with_amt := g_Itr_Method(p_business_grp_id).adjust_to_within;
538 Else
539 l_calc_method := 'INTERPOLATION';
540 l_to_with_amt := 1;
541 End If;
542 g_Element_Values(p_ele_entryid).ele_entry_id := p_ele_entryid;
543 g_Element_Values(p_ele_entryid).assignment_id := p_assignment_id;
544 g_Element_Values(p_ele_entryid).assignment_action_id:= p_assignment_action_id;
545 g_Element_Values(p_ele_entryid).iter_count := p_iter_count;
546 g_Element_Values(p_ele_entryid).max_amount := p_max_amount;
547 g_Element_Values(p_ele_entryid).min_amount := p_min_amount;
548 g_Element_Values(p_ele_entryid).maxdesired_amt := p_maxdesired_amt;
549 g_Element_Values(p_ele_entryid).deduction_amount := p_deduction_amount;
550 g_Element_Values(p_ele_entryid).actual_usercalc_amt := p_actual_usercalc_amt;
551 g_Element_Values(p_ele_entryid).stopper_flag := p_stopper_flag;
552 g_Element_Values(p_ele_entryid).inserted_flag := p_inserted_flag;
553 g_Element_Values(p_ele_entryid).arrears_allowed := l_arrears_allowed;
554 g_Element_Values(p_ele_entryid).partial_allowed := l_partial_allowed;
555 g_Element_Values(p_ele_entryid).calc_method := l_calc_method;
556 g_Element_Values(p_ele_entryid).to_within := l_to_with_amt;
557 g_Element_Values(p_ele_entryid).clr_add_amt := p_clr_add_amt;
558 g_Element_Values(p_ele_entryid).clr_rep_amt := p_clr_rep_amt;
559 hr_utility.set_location('Leaving: '||l_process_name, 150);
560
561 Return l_return_value;
562
563 Exception
564 When Others Then
565 l_return_value := -1;
566 p_error_message := 'Exception: When Others at '||l_process_name;
567 hr_utility.set_location('Error : '||p_error_message, 160);
568 hr_utility.set_location('Leaving: '||l_process_name, 170);
569
570 Return l_return_value;
571
572 End Set_Iteration_Values;
573 -- ============================================================
574 -- ~ Set_Formula_Warning_Mesg: Set the formula warn. messgage
575 -- ============================================================
576 Function Set_Formula_Warning_Mesg
577 (p_ele_entryid In Number -- Context Parameter
578 ,p_assignment_id In Number -- Context Parameter
579 ,p_business_grp_id In Number -- Context Parameter
580 ,p_eletype_id In Number -- Context Parameter
581 ,p_date_earned In Date -- Context Parameter
582 ,p_warning_message In Varchar2
583 ,p_warning_code In Varchar2
584 ) Return Number Is
585 l_process_name Varchar2(150) := g_pkg_name||'Set_Formula_Warning_Mesg';
586 l_return_value Number := 0;
590 If g_Element_Values.EXISTS(p_ele_entryid) Then
587 l_error_message varchar2(3000);
588 Begin
589 hr_utility.set_location('Entering: '||l_process_name, 5);
591 g_Element_Values(p_ele_entryid).formula_warning := NVL(p_warning_message,'X');
592 g_Element_Values(p_ele_entryid).warning_code := NVL(p_warning_code, 'X');
593 End If;
594 hr_utility.set_location('Leaving: '||l_process_name, 150);
595 Return l_return_value;
596 Exception
597 When Others Then
598 l_return_value := -1;
599 l_error_message := SQLERRM;
600 hr_utility.set_location('Error : '||l_error_message, 160);
601 hr_utility.set_location('Leaving: '||l_process_name, 170);
602 Return l_return_value;
603 End Set_Formula_Warning_Mesg;
604 -- ============================================================
605 -- ~ Get_Formula_Warning_Mesg: get the formula warn. messgage
606 -- ============================================================
607 Function Get_Formula_Warning_Mesg
608 (p_ele_entryid In Number -- Context Parameter
609 ,p_assignment_id In Number -- Context Parameter
610 ,p_business_grp_id In Number -- Context Parameter
611 ,p_eletype_id In Number -- Context Parameter
612 ,p_date_earned In Date -- Context Parameter
613 ,p_warning_message Out NoCopy Varchar2
614 ,p_warning_code Out NoCopy Varchar2
615 ) Return Number Is
616 l_process_name Varchar2(150) := g_pkg_name||'Get_Formula_Warning_Mesg';
617 l_return_value Number := 0;
618 l_error_message varchar2(3000);
619 Begin
620 hr_utility.set_location('Entering: '||l_process_name, 5);
621 If g_Element_Values.EXISTS(p_ele_entryid) Then
622 p_warning_message := NVL(g_Element_Values(p_ele_entryid).formula_warning,'X');
623 p_warning_message := NVL(g_Element_Values(p_ele_entryid).warning_code, 'X');
624 End If;
625 hr_utility.set_location('Leaving: '||l_process_name, 150);
626 Return l_return_value;
627 Exception
628 When Others Then
629 l_return_value := -1;
630 l_error_message := SQLERRM;
631 hr_utility.set_location('Error : '||l_error_message, 160);
632 hr_utility.set_location('Leaving: '||l_process_name, 170);
633 Return l_return_value;
634 End Get_Formula_Warning_Mesg;
635 -- ============================================================
636 -- ~Get_Iteration_Values:
637 -- ============================================================
638 Function Get_Iteration_Values
639 (p_ele_entryid In Number -- Context Parameter
640 ,p_assignment_id In Number -- Context Parameter
641 ,p_business_grp_id In Number -- Context Parameter
642 ,p_eletype_id In Number -- Context Parameter
643 ,p_date_earned In Date -- Context Parameter
644 ,p_iter_count Out NoCopy Number
645 ,p_max_amount Out NoCopy Number
646 ,p_min_amount Out NoCopy Number
647 ,p_maxdesired_amt Out NoCopy Number
648 ,p_deduction_amount Out NoCopy Number
649 ,p_actual_usercalc_amt Out NoCopy Number
650 ,p_clr_add_amt Out NoCopy Number
651 ,p_clr_rep_amt Out NoCopy Number
652 ,p_stopper_flag Out NoCopy Varchar2
653 ,p_inserted_flag Out NoCopy Varchar2
654 ,p_arrears_allowed Out NoCopy Varchar2
655 ,p_partial_allowed Out NoCopy Varchar2
656 ,p_calc_method Out NoCopy Varchar2
657 ,p_to_within Out NoCopy Number
658 ,p_error_message Out NoCopy Varchar2
659 ,p_warning_message Out NoCopy Varchar2
660 ) Return Number Is
661
662 l_return_value Number := 0;
663 l_process_name Varchar2(150) := g_pkg_name||'Get_Iteration_Values';
664 l_arrears_allowed Varchar2(2);
665 l_partial_allowed Varchar2(2);
666
667 Begin
668
669 hr_utility.set_location('Entering: '||l_process_name, 5);
670 If g_Element_Values.EXISTS(p_ele_entryid) Then
671 p_iter_count := g_Element_Values(p_ele_entryid).iter_count;
672 p_max_amount := g_Element_Values(p_ele_entryid).max_amount;
673 p_min_amount := g_Element_Values(p_ele_entryid).min_amount;
674 p_maxdesired_amt := g_Element_Values(p_ele_entryid).maxdesired_amt;
675 p_deduction_amount := g_Element_Values(p_ele_entryid).deduction_amount;
676 p_actual_usercalc_amt := g_Element_Values(p_ele_entryid).actual_usercalc_amt;
677 p_stopper_flag := g_Element_Values(p_ele_entryid).stopper_flag;
678 p_inserted_flag := g_Element_Values(p_ele_entryid).inserted_flag;
679 p_arrears_allowed := g_Element_Values(p_ele_entryid).arrears_allowed;
680 p_partial_allowed := g_Element_Values(p_ele_entryid).partial_allowed;
681 p_calc_method := g_Element_Values(p_ele_entryid).calc_method;
682 p_to_within := g_Element_Values(p_ele_entryid).to_within;
683 p_clr_add_amt := g_Element_Values(p_ele_entryid).clr_add_amt;
684 p_clr_rep_amt := g_Element_Values(p_ele_entryid).clr_rep_amt;
685 hr_utility.set_location('Iteration Count ='|| p_iter_count,145);
686 Else
687 If g_Element_Arr_Values.EXISTS(p_eletype_id) Then
688 l_arrears_allowed := g_Element_Arr_Values(p_eletype_id).arrears_allowed;
689 l_partial_allowed := g_Element_Arr_Values(p_eletype_id).partial_allowed;
690 Else
691 Get_Arrearage_Info
692 (p_ele_type_id => p_eletype_id
693 ,p_assignment_id => p_assignment_id
694 ,p_business_grp_id => p_business_grp_id
695 ,p_effective_date => p_date_earned
696 ,p_arrears_allowed => l_arrears_allowed
697 ,p_partial_allowed => l_partial_allowed
698 );
702 If g_Itr_Method.EXISTS(p_business_grp_id) Then
699 g_Element_Arr_Values(p_eletype_id).arrears_allowed := l_arrears_allowed;
700 g_Element_Arr_Values(p_eletype_id).partial_allowed := l_partial_allowed;
701 End If;
703 p_calc_method := g_Itr_Method(p_business_grp_id).iterative_method;
704 p_to_within := g_Itr_Method(p_business_grp_id).adjust_to_within;
705 Else
706 p_calc_method := 'INTERPOLATION';
707 p_to_within := 1;
708 End If;
709
710 p_iter_count := 0;
711 p_max_amount := 0;
712 p_min_amount := 0;
713 p_maxdesired_amt := 0;
714 p_deduction_amount := 0;
715 p_actual_usercalc_amt := 0;
716 p_stopper_flag := 'N';
717 p_inserted_flag := 'N';
718 p_arrears_allowed := l_arrears_allowed;
719 p_partial_allowed := l_partial_allowed;
720 p_clr_add_amt := 0;
721 p_clr_rep_amt := 0;
722 p_warning_message := 'Element Values does not exists in PL/SQL table :g_Element_Values';
723 hr_utility.set_location('Element Values does not exists in PL/SQL table :g_Element_Values',145);
724 hr_utility.set_location('Iteration Count ='|| p_iter_count,145);
725 End If;
726 hr_utility.set_location('Leaving: '||l_process_name, 150);
727
728 Return l_return_value;
729
730 Exception
731 When Others Then
732 l_return_value := -1;
733 p_error_message := 'Exception: When Others at '||l_process_name;
734 hr_utility.set_location('Error : '||p_error_message, 160);
735 hr_utility.set_location('Leaving: '||l_process_name, 170);
736 Return l_return_value;
737
738 End Get_Iteration_Values;
739
740 -- ============================================================
741 -- ~Clear_Iteration_Values: Clears the pl/sql table for all other
742 -- ~previously processed assignments in order to reduce the
743 -- ~memory usage.
744 -- ============================================================
745 Function Clear_Iteration_Values
746 (p_ele_entryid In Number -- Context Parameter
747 ,p_assignment_id In Number -- Context Parameter
748 ,p_assignment_action_id In Number -- Context Parameter
749 ,p_error_message Out NoCopy Varchar2
750 ,p_warning_message Out NoCopy Varchar2
751 ) Return Number Is
752
753 l_curr_ele_entry_id pay_element_entries_f.element_entry_id%TYPE;
754 l_prev_ele_entry_id pay_element_entries_f.element_entry_id%TYPE;
755 l_return_value Number := 0;
756 l_recs_deleted Number := 0;
757 l_process_name Varchar2(150) := g_pkg_name||'Clear_Iteration_Values';
758
759 Begin
760
761 hr_utility.set_location('Entering: '||l_process_name, 5);
762 l_curr_ele_entry_id := g_Element_Values.FIRST;
763 While l_curr_ele_entry_id Is Not Null
764 Loop
765 If (g_Element_Values(l_curr_ele_entry_id).assignment_id <> p_assignment_id)
766 Or
767 (g_Element_Values(l_curr_ele_entry_id).assignment_id = p_assignment_id And
768 g_Element_Values(l_curr_ele_entry_id).assignment_action_id <> p_assignment_action_id
769 ) Then
770 g_Element_Values.DELETE(l_curr_ele_entry_id);
771 l_recs_deleted := l_recs_deleted + 1;
772 End If;
773 l_prev_ele_entry_id := l_curr_ele_entry_id;
774 l_curr_ele_entry_id := g_Element_Values.NEXT(l_prev_ele_entry_id);
775 End Loop;
776 p_warning_message := 'Records deleted from PL/SQL table :'||l_recs_deleted;
777 hr_utility.set_location('Warning : '||p_warning_message, 145);
778 hr_utility.set_location('Leaving : '||l_process_name, 150);
779
780 Return l_return_value;
781
782 Exception
783 When Others Then
784 l_return_value := -1;
785 p_error_message := 'Exception: When Others at '||l_process_name;
786 hr_utility.set_location('Error : '||p_error_message, 160);
787 hr_utility.set_location('Leaving: '||l_process_name, 170);
788 Return l_return_value;
789 End Clear_Iteration_Values;
790
791 -- ============================================================
792 -- ~Incr_Iteration_Count: Increment the Iteration count for an
793 -- ~element currently iterating.
794 -- ============================================================
795 Function Incr_Iteration_Count
796 (p_ele_entryid In Number -- Context Parameter
797 ,p_assignment_id In Number -- Context Parameter
798 ,p_error_message Out NoCopy Varchar2
799 ,p_warning_message Out NoCopy Varchar2
800 ) Return Number Is
801 l_return_value Number := 0;
802 l_process_name Varchar2(150) := g_pkg_name||'Incr_Iteration_Count';
803 Begin
804
805 hr_utility.set_location('Entering: '||l_process_name, 5);
806 If g_Element_Values.EXISTS(p_ele_entryid) Then
807 g_Element_Values(p_ele_entryid).iter_count := g_Element_Values(p_ele_entryid).iter_count + 1;
808 Else
809 l_return_value := 1;
810 p_warning_message := 'Could not find record in PL/SQL table :g_Element_Values '||
811 'to Incr. the iteration count';
812 hr_utility.set_location('Iteration Count =0',145 );
813 End If;
814 hr_utility.set_location('Leaving: '||l_process_name, 150);
815
816 Return l_return_value;
817 Exception
818 When Others Then
819 l_return_value := -1;
820 p_error_message := 'Exception: When Others at '||l_process_name;
821 hr_utility.set_location('Error : '||p_error_message, 160);
822 hr_utility.set_location('Leaving: '||l_process_name, 170);
823
824 Return l_return_value;
825
826 End Incr_Iteration_Count;
827
828 -- ============================================================
829 -- ~Stop_Iteration: Stops Iterating a Pretax element once the
830 -- ~stop flag is set to Y.
831 -- ============================================================
832 Function Stop_Iteration
833 (p_ele_entryid In Number -- Context Parameter
834 ,p_assignment_id In Number -- Context Parameter
835 ,p_error_message Out NoCopy Varchar2
836 ,p_warning_message Out NoCopy Varchar2
837
838 ) Return Number Is
839 l_return_value Number := 0;
840 l_process_name Varchar2(150) := g_pkg_name||'Stop_Iteration';
841 Begin
842
843 hr_utility.set_location('Entering: '||l_process_name, 5);
844 If g_Element_Values.EXISTS(p_ele_entryid) Then
845 g_Element_Values(p_ele_entryid).stopper_flag := 'Y';
846 Else
847 l_return_value := -1;
848 p_warning_message := 'Could not find record in PL/SQL table :g_Element_Values '||
849 'to set the stopper flag';
850 End If;
851 hr_utility.set_location('Leaving: '||l_process_name, 150);
852
853 Return l_return_value;
854
855 Exception
856 When Others Then
857 l_return_value := -1;
858 p_error_message := 'Exception: When Others at '||l_process_name;
859 hr_utility.set_location('Error : '||p_error_message, 160);
860 hr_utility.set_location('Leaving: '||l_process_name, 170);
861
862 Return l_return_value;
863
864 End Stop_Iteration;
865 -- ============================================================
866 -- ~Get_Iteration_Count: Gets the Iteration counter value from
867 -- ~from the PL/SQL table for the element entry id
868 -- ============================================================
869 Function Get_Iteration_Count
870 (p_ele_entryid In Number -- Context Parameter
871 ,p_assignment_id In Number -- Context Parameter
872 ,p_error_message Out NoCopy Varchar2
873 ,p_warning_message Out NoCopy Varchar2
874
875 ) Return Number Is
876 l_return_value Number := 0;
877 l_process_name Varchar2(150) := g_pkg_name||'Get_Iteration_Count';
878 Begin
879 hr_utility.set_location('Entering: '||l_process_name, 5);
880 If g_Element_Values.EXISTS(p_ele_entryid) Then
881 l_return_value := g_Element_Values(p_ele_entryid).Iter_Count;
882 Else
883 l_return_value := 0;
884 p_warning_message := 'Could not find record in PL/SQL table :g_Element_Values '||
885 'to get the Iteration Count';
886 End If;
887 hr_utility.set_location('Leaving: '||l_process_name, 150);
888 Return l_return_value;
889
890 Exception
891 When Others Then
892 l_return_value := -1;
893 p_error_message := 'Exception: When Others at '||l_process_name;
894 hr_utility.set_location('Error : '||p_error_message, 145);
895 hr_utility.set_location('Leaving: '||l_process_name, 150);
896 Return l_return_value;
897
898 End Get_Iteration_Count;
899 -- ============================================================
900 -- ~Get_Stopper_Flag: Gets the Stopper Flag value from
901 -- ~the PL/SQL table for the element entry id
902 -- ============================================================
903 Function Get_Stopper_Flag
904 (p_ele_entryid In Number -- Context Parameter
905 ,p_assignment_id In Number -- Context Parameter
906 ,p_error_message Out NoCopy Varchar2
907 ,p_warning_message Out NoCopy Varchar2
908
909 ) Return Varchar2 Is
910 l_return_value Varchar2(2);
911 l_process_name Varchar2(150) := g_pkg_name||'Get_Stopper_Count';
912 Begin
913 hr_utility.set_location('Entering: '||l_process_name, 5);
914 If g_Element_Values.EXISTS(p_ele_entryid) Then
915 l_return_value := g_Element_Values(p_ele_entryid).Stopper_Flag;
916 Else
917 l_return_value := 'N';
918 p_warning_message := 'Could not find record in PL/SQL table :g_Element_Values '||
919 'to get the Stopper Flag';
920 End If;
921 hr_utility.set_location('Leaving: '||l_process_name, 150);
922 Return l_return_value;
923
924 Exception
925 When Others Then
926 l_return_value := -1;
927 p_error_message := 'Exception: When Others at '||l_process_name;
928 hr_utility.set_location('Error : '||p_error_message, 145);
929 hr_utility.set_location('Leaving: '||l_process_name, 150);
930 Return l_return_value;
931
932 End Get_Stopper_Flag;
933
934 End PQP_Iterative_Arrearage;