[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_MEDICAL_ADJUST
Source
1 PACKAGE BODY pay_ie_medical_adjust AS
2 /* $Header: pyiemadj.pkb 120.2 2008/01/07 07:02:05 rrajaman noship $ */
3 g_package varchar2(33) := ' pay_ie_medical_adjust.';
4
5 /*---------------------------------------------------------------------------*/
6 /*-------------------------- Medical_Balance_Adjust ---------------------------*/
7 /*---------------------------------------------------------------------------*/
8 PROCEDURE Medical_Balance_Adjust(p_bg_id IN NUMBER,
9 p_eff_date IN DATE,
10 p_asg_id IN VARCHAR2,
11 p_benefit_type IN VARCHAR2,
12 p_validate_commit IN VARCHAR2,
13 p_entry_value1 IN NUMBER)
14 IS
15 l_proc_name Varchar2(100) := 'Medical_Balance_Adjust';
16
17 --
18 CURSOR element_csr IS
19 SELECT element_type_id
20 FROM pay_element_types_f
21 WHERE element_name = 'Setup BIK Medical Insurance'
22 -- AND nvl(business_group_id, p_bg_id) = p_bg_id
23 AND legislation_code = 'IE'
24 AND p_eff_date BETWEEN effective_start_date AND effective_end_date;
25 --
26 element_rec element_csr%ROWTYPE;
27 --
28 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
29 SELECT input_value_id
30 FROM pay_input_values_f
31 WHERE element_type_id = p_element_type_id
32 AND name = p_name
33 --AND nvl(business_group_id, p_bg_id) = p_bg_id
34 AND legislation_code = 'IE'
35 AND p_eff_date BETWEEN effective_start_date AND effective_end_date;
36 --
37 input_val_rec1 input_val_csr%ROWTYPE;
38 --
39 CURSOR link_csr(p_element_type_id IN NUMBER) IS
40 SELECT links.element_link_id
41 FROM pay_element_links_f links, per_all_assignments_f assign
42 WHERE links.element_type_id = p_element_type_id
43 AND links.business_group_id=p_bg_id
44 AND assign.assignment_id=p_asg_id
45 AND (( links.payroll_id is not null
46 and links.payroll_id = assign.payroll_id)
47 OR ( links.link_to_all_payrolls_flag='Y'
48 and assign.payroll_id is not null)
49 OR ( links.payroll_id is null
50 and links.link_to_all_payrolls_flag='N')
51 OR links.job_id=assign.job_id
52 OR links.position_id=assign.position_id
53 OR links.people_group_id=assign.people_group_id
54 OR links.organization_id=assign.organization_id
55 OR links.grade_id=assign.grade_id
56 OR links.location_id=assign.location_id
57 OR links.pay_basis_id=assign.pay_basis_id
58 OR links.employment_category=assign.employment_category)
59 AND p_eff_date BETWEEN links.effective_start_date
60 AND links.effective_end_date;
61 --
62 link_rec link_csr%ROWTYPE;
63 --
64 l_element_entry_id NUMBER;
65 l_effective_start_date DATE;
66 l_effective_end_date DATE;
67 l_object_version_number NUMBER;
68 l_create_warning BOOLEAN := FALSE;
69 --
70
71 BEGIN
72
73 hr_utility.set_location('Entering '||g_package||l_proc_name,2000);
74 fnd_file.put_line(FND_FILE.LOG,'Value of p_bg_id is '||p_bg_id);
75 fnd_file.put_line(FND_FILE.LOG,'Value of p_eff_date is '||p_eff_date);
76 fnd_file.put_line(FND_FILE.LOG,'Value of p_asg_id is '||p_asg_id);
77 fnd_file.put_line(FND_FILE.LOG,'Value of p_benefit_type is '||p_benefit_type);
78 fnd_file.put_line(FND_FILE.LOG,'Value of p_validate_commit is '||p_validate_commit);
79
80 --
81 -- Get Element information
82 --
83 OPEN element_csr;
84 FETCH element_csr INTO element_rec;
85 CLOSE element_csr;
86 hr_utility.set_location('element_rec.element_type_id '||element_rec.element_type_id,2010);
87 --
88 -- Get Input Values
89 --
90 OPEN input_val_csr(element_rec.element_type_id, 'Taxable Value for Run');
91 FETCH input_val_csr INTO input_val_rec1;
92 CLOSE input_val_csr;
93 hr_utility.set_location('input_val_rec1.input_value_id '||input_val_rec1.input_value_id,2020);
94 --
95 -- Get element link information
96 --
97 OPEN link_csr(element_rec.element_type_id);
98 FETCH link_csr INTO link_rec;
99 CLOSE link_csr;
100 hr_utility.set_location('link_rec.element_link_id '||link_rec.element_link_id,2030);
101 --
102 -- Call API To Create Element Entry
103 --
104 hr_utility.set_location('Before Calling API py_element_entry_api.create_element_entry ',2040);
105
106 py_element_entry_api.create_element_entry(p_effective_date => p_eff_date,
107 p_business_group_id => p_bg_id,
108 --p_original_entry_id => p_original_entry_id, -- default
109 p_assignment_id => p_asg_id,
110 p_element_link_id => link_rec.element_link_id,
111 p_entry_type => 'B',
112 p_creator_type => 'B',
113 p_target_entry_id => 999999,
114 p_input_value_id1 => input_val_rec1.input_value_id,
115 p_entry_value1 => nvl(p_entry_value1,0),
116 p_effective_start_date => l_effective_start_date,
117 p_effective_end_date => l_effective_end_date,
118 p_element_entry_id => l_element_entry_id,
119 p_object_version_number => l_object_version_number,
120 p_create_warning => l_create_warning);
121 hr_utility.set_location('After Calling API py_element_entry_api.create_element_entry ',2040);
122
123 update pay_element_entries_f pee
124 set pee.creator_type = 'B',
125 pee.target_entry_id = NULL
126 where pee.element_entry_id = l_element_entry_id
127 and p_eff_date between pee.effective_start_date and pee.effective_end_date;
128
129 hr_utility.set_location('After updating pay_element_entries_f ',2050);
130 -- Deal with the creation of Payroll and Assignment
131 -- Action for the adjustment. We call the existing
132 -- routine to ensure that we get support for altering
133 -- latest balances and creation of Action Contexts.
134 hr_utility.set_location('Before calling hrassact.bal_adjust ',2060);
135
136 hrassact.bal_adjust (consetid => NULL,
137 eentryid => l_element_entry_id,
138 effdate => p_eff_date,
139 prepay_flag => NULL,
140 run_type_id => NULL);
141
142 hr_utility.set_location('After calling hrassact.bal_adjust ',2060);
143
144 hr_utility.set_location('Leaving '||g_package||l_proc_name,2000);
145 END;
146
147 /*---------------------------------------------------------------------------*/
148 /*-------------------------- Medical_Validate_Commit ---------------------------*/
149 /*---------------------------------------------------------------------------*/
150 PROCEDURE Medical_Validate_Commit(errbuf OUT NOCOPY VARCHAR2,
151 retcode OUT NOCOPY VARCHAR2,
152 p_bg_id IN NUMBER,
153 p_eff_date IN VARCHAR2,
154 p_asg_id IN VARCHAR2,
155 p_benefit_type IN VARCHAR2,
156 p_validate_commit IN VARCHAR2)
157 IS
158 l_proc_name Varchar2(100) := 'Medical_Validate_Commit';
159 l_effective_date DATE;
160 l_ele_type_id number;
161 l_input_val_id_med number;
162 l_input_val_id_tax number;
163 l_assignment_id number := NULL;
164 l_assig_number per_all_assignments_f.assignment_number%TYPE;
165 l_report_item_type varchar(10) := NULL;
166 l_result_val varchar2(20);
167 l_result_val_l varchar2(20);
168 l_result_val_num number := 0;
169 l_full_name per_all_people_f.full_name%TYPE;
170 l_temp_assg_id number;
171 l_string varchar2(200);
172 counter number := 0;
173 l_assign_id varchar2(30);
174 l_instr_length number := 0;
175 l_under_line varchar2(130) := '----------------------------------------------------------------------------------------------------------------------------------';
176 l_asg_counter number :=0;
177 l_c_already_run number :=0; -- (0 = NOT RUN 1 = RAN )
178
179 TYPE Med_Record is Record(l_m_asg_id per_all_assignments_f.assignment_id%TYPE,
180 l_m_Value NUMBER);
181 l_Med_Record Med_Record;
182
183 TYPE Med_pl_table is table of Med_Record index by binary_integer;
184 l_Med_pl_table Med_pl_table;
185
186 -- Cursor to get element_type_id
187 cursor csr_get_ele_id IS
188 select distinct element_type_id
189 from pay_element_types_f
190 where element_name = 'IE BIK Other Reportable Item Details'
191 and legislation_code = 'IE';
192
193 -- Cursor to get input_val_id
194 cursor csr_get_input_val_id (p_ele_type_id in number,
195 p_input_val_name in varchar2) IS
196 select distinct input_value_id
197 from pay_input_values_f
198 where name = p_input_val_name
199 and legislation_code = 'IE'
200 and element_type_id = p_ele_type_id;
201
202 -- fetch all assigment_action_ids and corresponding
203 cursor csr_assig_act (p_assig_id in number,
204 p_input_val_id_med in number,
205 p_ele_type_id in number,
206 p_report_item_type in varchar2,
207 p_input_val_id_tax in number) IS
208 select paa.assignment_id, sum(fnd_number.canonical_to_number(prrv1.result_value)) result_value --paa.assignment_action_id, paa.payroll_action_id
209 from pay_assignment_actions paa,
210 pay_payroll_actions ppa,
211 pay_run_results prr,
212 pay_run_result_values prrv,
213 pay_run_result_values prrv1
214 where paa.action_status = 'C'
215 and paa.payroll_action_id = ppa.payroll_action_id
216 and paa.assignment_id = nvl(p_assig_id, paa.assignment_id)
217 and paa.assignment_action_id = prr.assignment_action_id
218 and prr.element_type_id = p_ele_type_id
219 and prr.run_result_id = prrv.run_result_id
220 and prrv.input_value_id = p_input_val_id_med
221 and prrv.result_value = p_report_item_type
222 and prr.run_result_id = prrv1.run_result_id
223 and prrv1.input_value_id = p_input_val_id_tax
224 and ppa.effective_date between to_date('01/01/2007','DD/MM/RRRR') and l_effective_date
225 and ppa.action_type in ('R','Q','B','V')
226 group by paa.assignment_id
227 having sum(fnd_number.canonical_to_number(prrv1.result_value)) <> 0
228 order by paa.assignment_id;
229
230 -- Cursor to get result values
231 cursor csr_get_value (p_ele_type_id in number,
232 p_input_val_id in number,
233 p_assg_act_id in number) IS
234 select result_value from pay_run_result_values where
235 input_value_id = p_input_val_id
236 and run_result_id in (select run_result_id from pay_run_results
237 where assignment_action_id = p_assg_act_id
238 and element_type_id = p_ele_type_id) ;
239
240 -- Get person details
241 Cursor csr_person_details (p_assg_id in number) IS
242 select distinct ppf.full_name ,
243 paaf.assignment_number
244 from per_all_people_f ppf,
245 per_all_assignments_f paaf
246 where paaf.assignment_id = p_assg_id
247 and paaf.person_id = ppf.person_id
248 and l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
249
250 -- Cursor to prevent this process running twice.
251 CURSOR c_already_run IS
252 SELECT 1
253 FROM pay_patch_status
254 WHERE patch_number = 6506755
255 AND patch_name = 'IE_MED_BAL_ADJ'
256 AND phase = 'C'
257 AND legislation_code = 'IE';
258
259 BEGIN
260 hr_utility.set_location('Entering '||g_package||l_proc_name,1000);
261 hr_utility.set_location('p_bg_id '||p_bg_id,1005);
262 hr_utility.set_location('p_eff_date '||p_eff_date,1005);
263 hr_utility.set_location('p_asg_id '||p_asg_id,1005);
264 hr_utility.set_location('p_benefit_type '||p_benefit_type,1005);
268
265 hr_utility.set_location('p_validate_commit '||p_validate_commit,1005);
266
267 --fnd_file.put_line(fnd_file.log,'p_bg_id: '||p_bg_id||' p_eff_date: '||p_eff_date||' p_asg_id: '||p_asg_id||' p_benefit_type: '||p_benefit_type||' p_validate_commit: '||p_validate_commit);
269 retcode := 0;
270 l_effective_date := fnd_date.canonical_to_date(p_eff_date);
271
272 hr_utility.set_location('l_effective_date'||to_char(l_effective_date),1010);
273 hr_utility.set_location('Deleting PL table l_Med_pl_table',1010);
274 l_Med_pl_table.delete;
275
276 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
277 fnd_file.put_line(FND_FILE.OUTPUT,'Date:'||to_char(l_effective_date)||' IE Medical Insurance Upgrade Process');
278 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
279 fnd_file.put_line(FND_FILE.OUTPUT,' ');
280
281 fnd_file.put_line(FND_FILE.LOG,l_under_line);
282 fnd_file.put_line(FND_FILE.LOG,'Date:'||to_char(l_effective_date)||' IE Medical Insurance Upgrade Process');
283 fnd_file.put_line(FND_FILE.LOG,l_under_line);
284 fnd_file.put_line(FND_FILE.LOG,' ');
285
286 l_assignment_id := p_asg_id;
287 l_report_item_type := p_benefit_type;
288 /*
289 IF l_assignment_id = -1 THEN
290 l_assignment_id := NULL;
291 END IF;
292
293 IF l_report_item_type = '-1' THEN
294 l_report_item_type := NULL;
295 fnd_file.put_line(FND_FILE.OUTPUT,'Reportable Item Type cannot be null. You must enter valid Reportable Item Type');
296 raise_application_error(-20001,'Reportable Item Type cannot be null. You must enter valid Reportable Item Type');
297 END IF;
298 */
299 -- get element_type_Id;
300 open csr_get_ele_id;
301 fetch csr_get_ele_id into l_ele_type_id;
302 close csr_get_ele_id;
303 hr_utility.set_location('l_ele_type_id:'||l_ele_type_id,1020);
304
305 -- get input_val_id of Taxable Value for Run
306 open csr_get_input_val_id(l_ele_type_id, 'Taxable Value for Run');
307 fetch csr_get_input_val_id into l_input_val_id_tax;
308 close csr_get_input_val_id;
309 hr_utility.set_location('l_input_val_id_tax:'||l_input_val_id_tax,1030);
310
311 -- get input_val_id of Reportable Item Type
312 open csr_get_input_val_id(l_ele_type_id, 'Reportable Item Type');
313 fetch csr_get_input_val_id into l_input_val_id_med;
314 close csr_get_input_val_id;
315 hr_utility.set_location('l_input_val_id_med:'||l_input_val_id_med,1040);
316
317 l_temp_assg_id := NULL;
318
319 l_string := lpad('Person Full Name',35,' ')
320 || lpad('Assignment Number',25,' ')
321 ||lpad('Assignment ID',20,' ')
322 ||lpad('Report Item',20,' ')
323 ||lpad('Taxable Value',30,' ');
324 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
325 fnd_file.put_line(FND_FILE.LOG,l_string);
326
327 l_string := NULL;
328 l_string := lpad('=========================',35,' ')
329 || lpad('=================',25,' ')
330 ||lpad('=============',20,' ')
331 ||lpad('===========',20,' ')
332 ||lpad('=====================',30,' ');
333 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
334 fnd_file.put_line(FND_FILE.LOG,l_string);
335
336 hr_utility.set_location('l_assignment_id:'||l_assignment_id,1050);
337 hr_utility.set_location('l_input_val_id_med:'||l_input_val_id_med,1050);
338 hr_utility.set_location('l_ele_type_id:'||l_ele_type_id,1050);
339 hr_utility.set_location('l_report_item_type:'||l_report_item_type,1050);
340 hr_utility.set_location('l_input_val_id_tax:'||l_input_val_id_tax,1050);
341 hr_utility.set_location('l_c_already_run:'||l_c_already_run,1060);
342
343
344 -- Get all assignment_ids
345 hr_utility.set_location('Before cursor loop csr_assig_act',1060);
346 OPEN c_already_run;
347 FETCH c_already_run INTO l_c_already_run;
348 CLOSE c_already_run;
349
350 hr_utility.set_location('l_c_already_run:'||l_c_already_run,1065);
351
352 IF l_c_already_run <> 1 THEN
353 FOR l_csr_assig_act in csr_assig_act (l_assignment_id,
354 l_input_val_id_med,
355 l_ele_type_id,
356 l_report_item_type,
357 l_input_val_id_tax)
358 LOOP
359 l_asg_counter := l_asg_counter + 1;
360 hr_utility.set_location('Starting Loop counter l_asg_counter:'||l_asg_counter,1070);
361 /*
362 IF counter <> 0 and l_temp_assg_id <> l_csr_assig_act.assignment_id THEN
363 -- Here display Emp Name, assignment number, assignment_id and total result value.
364 l_string := null;
365 l_string := lpad(nvl(l_full_name,' '),35,' ')|| lpad(nvl(l_assig_number,' '),25,' ')||lpad(nvl(l_assign_id,' '),20,' ')||lpad(nvl(l_report_item_type,' '),20,' ')||lpad(to_char(l_result_val_num),30,' ');
366 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
367 dbms_output.put_line(l_string);
368 --utl_file.put_line (vOutHandle, l_string);
369 END IF;
370
371 counter := counter + 1;
372 IF l_temp_assg_id <> l_csr_assig_act.assignment_id THEN
373 l_result_val_num := 0;
374 END IF;
375
376 l_temp_assg_id := l_csr_assig_act.assignment_id;
377
378 open csr_get_value (l_ele_type_id,
379 l_input_val_id_tax,
380 l_csr_assig_act.assignment_action_id);
381 fetch csr_get_value into l_result_val;
382 close csr_get_value;
383
384 l_result_val_num := l_result_val_num + to_number(l_result_val);
385 l_result_val_num := l_result_val_num + fnd_number.canonical_to_number(l_result_val);
386 */
387 -- get the Person_name, assignment_number
388 open csr_person_details (l_csr_assig_act.assignment_id);
389 fetch csr_person_details into l_full_name, l_assig_number;
390 close csr_person_details;
391 l_assign_id := to_char(l_csr_assig_act.assignment_id);
392
393 -- Here display Emp Name, assignment number, assignment_id and total result value.
394 l_string := null;
398 fnd_file.put_line(FND_FILE.LOG,l_string);
395 l_string := lpad(nvl(l_full_name,' '),35,' ')|| lpad(nvl(l_assig_number,' '),25,' ')||lpad(nvl(l_assign_id,' '),20,' ')||lpad(nvl(l_report_item_type,' '),20,' ')||lpad(to_char(l_csr_assig_act.result_value),30,' ');
396 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
397
399
400 -- populate PL table only in Commit mode and
401 IF p_validate_commit = 'IE_VALIDATE_COMMIT'
402 AND l_c_already_run <> 1
403 THEN
404 l_Med_pl_table(l_asg_counter).l_m_asg_id := l_csr_assig_act.assignment_id;
405 l_Med_pl_table(l_asg_counter).l_m_value := l_csr_assig_act.result_value;
406 END IF;
407
408 hr_utility.set_location('Ending Loop counter l_asg_counter:'||l_asg_counter,1070);
409 END LOOP;
410
411 --
412 hr_utility.set_location('After cursor loop csr_assig_act',1060);
413 --
414 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
415 fnd_file.put_line(FND_FILE.LOG,l_under_line);
416 hr_utility.set_location('p_validate_commit:'||p_validate_commit,1080);
417 hr_utility.set_location('l_Med_pl_table.COUNT:'||l_Med_pl_table.COUNT,1080);
418 --
419 IF p_validate_commit = 'IE_VALIDATE_COMMIT' AND l_Med_pl_table.COUNT > 0
420 THEN
421 FOR l_index IN 1..l_Med_pl_table.COUNT
422 LOOP
423 BEGIN
424 hr_utility.set_location('l_index:'||l_index,1090);
425 hr_utility.set_location('l_Med_pl_table(l_index).l_m_asg_id:'||l_Med_pl_table(l_index).l_m_asg_id,1090);
426 hr_utility.set_location('l_Med_pl_table(l_index).l_m_value:'||l_Med_pl_table(l_index).l_m_value,1090);
427 hr_utility.set_location('Before calling Medical_Balance_Adjust:',1090);
428
429 Medical_Balance_Adjust(p_bg_id,
430 l_effective_date,
431 l_Med_pl_table(l_index).l_m_asg_id,
432 p_benefit_type,
433 p_validate_commit,
434 l_Med_pl_table(l_index).l_m_value);
435
436 hr_utility.set_location('After calling procedure Medical_Balance_Adjust:',1090);
437 /*
438 EXCEPTION
439 WHEN OTHERS THEN
440 fnd_file.put_line(FND_FILE.LOG,' ');
441 fnd_file.put_line(FND_FILE.LOG,'Error encountered for assignment ID:'||l_Med_pl_table(l_index).l_m_asg_id);
442 fnd_file.put_line(FND_FILE.LOG,sqlerrm);
443 Hr_Utility.set_location('Error encountered for assignment ID:'||l_Med_pl_table(l_index).l_m_asg_id,1100);
444 Hr_Utility.set_location('SQLERRM :'||sqlerrm,1100);
445 ROLLBACK;
446 */
447 END;
448 END LOOP;
449 --
450 INSERT INTO pay_patch_status(id
451 ,patch_number
452 ,patch_name
453 ,phase
454 ,applied_date
455 ,legislation_code)
456 SELECT pay_patch_status_s.nextval
457 ,6506755
458 ,'IE_MED_BAL_ADJ'
459 ,'C'
460 ,sysdate
461 ,'IE'
462 FROM dual;
463 --
464 END IF;
465 ELSE
466 --
467 fnd_file.put_line(FND_FILE.LOG,l_under_line);
468 fnd_file.put_line(FND_FILE.log,'This process has already been run.');
469 fnd_file.put_line(FND_FILE.LOG,l_under_line);
470 --
471 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
472 fnd_file.put_line(FND_FILE.OUTPUT,'This process has already been run.');
473 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
474 --
475 END IF;
476 --hr_utility.trace_off;
477 -- Display the last assignment picked, outside the loop
478 /*
479 l_string := null;
480 l_string := lpad(nvl(l_full_name,' '),35,' ')|| lpad(nvl(l_assig_number,' '),25,' ')||lpad(nvl(l_assign_id,' '),20,' ')||lpad(nvl(l_report_item_type,' '),20,' ')||lpad(to_char(l_result_val_num),30,' ');
481 fnd_file.put_line(FND_FILE.OUTPUT,l_string);
482 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
483 dbms_output.put_line(l_string);
484 */
485
486 hr_utility.set_location('Leaving '||g_package||l_proc_name,1000);
487
488 EXCEPTION
489 WHEN Others THEN
490 fnd_file.put_line(FND_FILE.LOG,'..'||'SQL-ERRM :'||SQLERRM);
491 fnd_file.put_line(FND_FILE.OUTPUT,l_under_line);
492 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1000);
493 hr_utility.set_location('Leaving '||g_package||l_proc_name,1000);
494 raise;
495 END;
496
497 END pay_ie_medical_adjust;