[Home] [Help]
PACKAGE BODY: APPS.GHR_SES_CONV_PKG
Source
1 PACKAGE BODY GHR_SES_CONV_PKG
2 /* $Header: ghsescon.pkb 120.1 2010/04/09 05:26:27 vmididho ship $ */
3 AS
4 g_proc_name VARCHAR2(200) := 'GHR_SES_PAY_CONVERSION';
5
6 --
7 -- ---------------------------------------------------------------------------
8 -- |--------------------< ghr_ses_pay_cal_conv >----------------------|
9 -- ---------------------------------------------------------------------------
10 -- {Start of Comments}
11 --
12 -- Used with Concurrent Program - Process SES Pay Conversion
13 --
14 -- {End of Comments}
15 -- ---------------------------------------------------------------------------
16 PROCEDURE ghr_ses_pay_cal_conv
17 (
18 errbuf OUT NOCOPY VARCHAR2 ,
19 retcode OUT NOCOPY NUMBER ,
20 p_business_group_id NUMBER DEFAULT NULL
21 )
22 IS
23
24 l_mesgbuff VARCHAR2(4000);
25 l_prog_name ghr_process_log.program_name%type;
26 l_effective_date ghr_pa_requests.effective_date%type := to_date('2004/01/11','YYYY/MM/DD');
27
28 l_user_tab_name pay_user_tables.user_table_name%type
29 := 'ESSL Oracle Federal Standard Pay Table (EP, ES, IE, IP, FE, SL and ST) No. ESSL';
30
31 l_position_id per_assignments_f.position_id%type;
32 l_essl_tab_id pay_user_tables.user_table_id%type;
33 l_user_tab_id pay_user_tables.user_table_id%type;
34
35 CURSOR cur_user_pay_tab (l_user_table_name IN pay_user_tables.user_table_name%type)
36 is
37 SELECT user_table_id
38 FROM pay_user_tables
39 WHERE user_table_name=l_user_table_name;
40 --
41 --
42 CURSOR cur_user_pay_tab_name IS
43 SELECT substr(user_table_name,1,4) user_table_name
44 FROM pay_user_tables
45 WHERE user_table_id = l_user_tab_id;
46 --
47 --
48 CURSOR cur_pos_ei(l_essl_tab_id NUMBER)
49 IS
50 SELECT pei.position_id,
51 pei.position_extra_info_id,
52 pei.poei_information3 grade_id,
53 to_number(pei.poei_information5) user_tab_id
54 FROM per_position_extra_info pei
55 WHERE pei.information_type = 'GHR_US_POS_VALID_GRADE'
56 AND to_number(pei.poei_information5) <> l_essl_tab_id
57 AND pei.position_id in
58 (SELECT position_id
59 from hr_positions_f pos
60 WHERE pos.position_id = pei.position_id
61 AND business_group_id = p_business_group_id)
62 FOR UPDATE OF poei_information5;
63 --
64 --
65 CURSOR cur_hist_rows(l_essl_tab_id NUMBER)
66 IS
67
68 SELECT pah.pa_history_id,
69 pah.information4 , -- position_id
70 pah.information9 , -- grade_id
71 to_number(pah.information11) user_tab_id,
72 pah.effective_date
73 FROM ghr_pa_history pah
74 WHERE pah.table_name = 'PER_POSITION_EXTRA_INFO'
75 AND pah.information5 = 'GHR_US_POS_VALID_GRADE'
76 AND to_number(pah.information11) <> l_essl_tab_id
77 AND to_number(pah.information4) in
78 (SELECT position_id
79 from hr_positions_f pos
80 WHERE pos.position_id = to_number(pah.information4)
81 AND business_group_id = p_business_group_id
82 AND pah.effective_date
83 between pos.effective_start_date and pos.effective_end_date
84 AND HR_GENERAL.DECODE_AVAILABILITY_STATUS(pos.availability_status_id)
85 NOT IN ('Eliminated','Deleted'));
86 --
87 --
88 CURSOR cur_grd(l_grade_id NUMBER) IS
89 SELECT gdf.segment1 pay_plan
90 ,gdf.segment2 grade_or_level
91 FROM per_grade_definitions gdf
92 ,per_grades grd
93 WHERE grd.grade_id = l_grade_id
94 AND grd.grade_definition_id = gdf.grade_definition_id;
95
96 --
97 CURSOR cur_pos_valid IS
98 SELECT 1
99 FROM hr_positions_f pos1
100 WHERE position_id = l_position_id
101 AND l_effective_date between effective_start_date and effective_end_date
102 AND business_group_id = p_business_group_id
103 AND HR_GENERAL.DECODE_AVAILABILITY_STATUS(pos1.availability_status_id)
104 NOT IN ('Eliminated','Deleted');
105
106 ---
107 ---
108 CURSOR cur_his_cut_off_date IS
109 SELECT 1
110 FROM ghr_pa_history pah1
111 WHERE table_name = 'PER_POSITION_EXTRA_INFO'
112 AND information5 = 'GHR_US_POS_VALID_GRADE'
113 and effective_date = l_effective_date
114 AND to_number(information4) = l_position_id
115 AND to_number(information11) = l_essl_tab_id;
116
117 l_proc varchar2(72) := 'ghr_ses_pay_cal_conv';
118
119 l_his_match BOOLEAN := FALSE;
120 l_pos_valid BOOLEAN := FALSE;
121 l_pos_ei_data per_position_extra_info%rowtype;
122 l_pay_plan per_grade_definitions.segment1%type;
123 l_grade_or_level per_grade_definitions.segment2%type;
124 l_posei_id per_position_extra_info.position_extra_info_id%type;
125 l_grd_id per_position_extra_info.poei_information5%type;
126 l_hist_id ghr_pa_history.pa_history_id%type;
127 l_his_eff_date ghr_pa_requests.effective_date%type;
128 l_counter NUMBER := 0;
129 l_counter1 NUMBER := 0;
130 --
131 --
132 -- Process Log Extention --
133 --
134 l_check_date date;
135 l_log_text varchar2(2000);
136 l_from_user_tab_name pay_user_tables.user_table_name%type;
137 l_name per_people_f.full_name%type;
138 l_ssn per_people_f.national_identifier%type;
139 --bug # 9329643
140 l_employee_number per_people_f.employee_number%type;
141 --
142 --
143 CURSOR cur_emp_det IS
144 SELECT per.full_name full_name
145 ,per.national_identifier national_identifier
146 --bug # 9329643
147 ,per.employee_number employee_number
148 from per_people_f per
149 ,per_assignments_f paf
150 where paf.person_id = per.person_id
151 and l_check_date
152 between paf.effective_start_date and paf.effective_end_date
153 and l_check_date
154 between per.effective_start_date and per.effective_end_date
155 and paf.position_id = l_position_id;
156
157 BEGIN
158
159 hr_utility.set_location( 'Entering : ' || l_proc, 10);
160 l_prog_name := Fnd_profile.value('CONC_REQUEST_ID');
161 hr_utility.set_location('l_prog_name conc_request_id :' || l_prog_name ,11);
162
163 if l_prog_name = '-1' then
164 l_prog_name := NULL;
165 else
166 g_proc_name := g_proc_name || '_' || l_prog_name;
167 end if;
168
169 FOR c_user_paytab in cur_user_pay_tab(l_user_tab_name)
170 LOOP
171 l_essl_tab_id := c_user_paytab.user_table_id;
172 END LOOP;
173
174 ---
175 --- 1. Update position extra information for ES equivalent pay plans with the pay table id
176 --- of ESSL a open pay range pay table.
177 --- i) Here available status is not checked because there is no date concept.
178 --- ii)Also date is not checked because the per position extra is not a date track table.
179 ---
180
181 FOR c_pos IN cur_pos_ei(l_essl_tab_id)
182 LOOP
183
184 l_position_id := c_pos.position_id;
185 l_posei_id := c_pos.position_extra_info_id;
186 l_grd_id := c_pos.grade_id;
187 l_user_tab_id := c_pos.user_tab_id;
188 ---
189 --- Fetch the pay plan to compare with ES equivalent
190 ---
191 FOR c_grd in cur_grd(l_grd_id)
192
193 LOOP
194 l_pay_plan := c_grd.pay_plan;
195 l_grade_or_level := c_grd.grade_or_level;
196 END LOOP;
197 ---
198 --- Compare the pay plans with ES equivalent
199 ---
200
201 IF l_pay_plan in ('ES','EP','IE','FE') THEN
202
203 UPDATE PER_POSITION_EXTRA_INFO
204 SET poei_information5 = to_char(l_essl_tab_id)
205 WHERE current of cur_pos_ei;
206
207 FOR tab_name IN cur_user_pay_tab_name
208 LOOP
209 l_from_user_tab_name := tab_name.user_table_name;
210 END LOOP;
211 ---
212 --- Log Message
213 ---
214 l_log_text := ' Position Title : ' || ghr_api.get_position_title_pos(l_position_id,p_business_group_id);
215 l_log_text := substr(l_log_text || ', Position Number: ' ||
216 ghr_api.get_position_desc_no_pos(l_position_id,p_business_group_id),1,2000);
217 l_log_text := substr(l_log_text || ', Sequence Number: ' ||
218 ghr_api.get_position_sequence_no_pos(l_position_id,p_business_group_id),1,2000);
219 l_log_text := substr(l_log_text || ', From Pay Table : ' || l_from_user_tab_name,1,2000);
220 l_log_text := substr(l_log_text || ', To Pay Table : ' || 'ESSL',1,2000);
221
222 IF SQL%NOTFOUND then
223 ---
224 --- Update failed then capture the error and Enter the log.
225 ---
226 l_log_text := substr(l_log_text || ', Message : ' || 'ERROR NOT UPDATED ,',1,2000);
227 ghr_wgi_pkg.create_ghr_errorlog(
228 p_program_name => g_proc_name,
229 p_message_name => 'ERR-PER_POSITION_EXTRA_INFO',
230 p_log_text => substr(l_log_text || 'DB ERROR is : ' || SQLERRM,1,2000),
231 p_log_date => sysdate);
232 ELSE
233 l_counter := l_counter + 1;
234
235 ---
236 --- Update Successful - Enter log.
237 ---
238 l_log_text := substr(l_log_text || ', Message : ' || 'Updated Successful ',1,2000);
239 ghr_wgi_pkg.create_ghr_errorlog(
240 p_program_name => g_proc_name,
241 p_message_name => 'PER_POSITION_EXTRA_INFO',
242 p_log_text => l_log_text,
243 p_log_date => sysdate);
244 END IF;
245
246
247 END IF; --- for the condtion of ('ES','EP','IE','FE').
248
249 END LOOP;
250
251 IF l_counter = 0 THEN
252 ---
253 --- No Records in PER_POSITION_EXTRA_INFO - so Enter log.
254 ---
255 ghr_wgi_pkg.create_ghr_errorlog(
256 p_program_name => g_proc_name,
257 p_message_name => 'NO-PER_POSITION_EXTRA_INFO',
258 p_log_text => 'Error : NO Valid Extra Position Info records found for Update' ,
259 p_log_date => sysdate);
260 ELSE
261 ---
262 --- All Records in PER_POSITION_EXTRA_INFO updated - so Enter log.
263 ---
264 ghr_wgi_pkg.create_ghr_errorlog(
265 p_program_name => g_proc_name,
266 p_message_name => 'TOT-PER_POSITION_EXTRA_INFO',
267 p_log_text => 'Total Records updated with SES equivalent plans are ' || to_char(l_counter),
268 p_log_date => sysdate);
269 END IF;
270
271
272 --
273 -- 2. Start fetching Positions records on history table
274 -- i) Check for the Available status id for the effective date of the history table.
275 -- ii) Check for the row on the date of 11-JAN-2004 and if not exists create one provided the
276 -- date of creation of the position is less than 11-JAN-2004.
277 -- iii)Update the rows for the effective_date > 11-JAN-2004.
278 --
279 l_counter := 0;
280
281 FOR hist_rec IN cur_hist_rows(l_essl_tab_id)
282 LOOP
283
284
285 l_hist_id := hist_rec.pa_history_id;
286 l_position_id := hist_rec.information4;
287 l_grd_id := hist_rec.information9;
288 l_his_eff_date := hist_rec.effective_date;
289 l_user_tab_id := hist_rec.user_tab_id;
290
291 FOR c_grd in cur_grd(l_grd_id)
292 LOOP
293 l_pay_plan := c_grd.pay_plan;
294 l_grade_or_level := c_grd.grade_or_level;
295 END LOOP;
296
297 IF l_pay_plan in ('ES','EP','IE','FE') THEN
298
299
300 IF l_effective_date >= l_his_eff_date THEN
301
302 l_pos_valid := FALSE;
303 l_his_match := FALSE;
304
305 FOR cur_pos_valid_rec IN cur_pos_valid
306 LOOP
307 l_pos_valid := TRUE;
308 END LOOP;
309
310 if l_pos_valid then
311 FOR cur_his_cut_off_date_rec IN cur_his_cut_off_date
312 LOOP
313 l_his_match := TRUE;
314 END LOOP;
315 end if;
316
317 if l_pos_valid AND not l_his_match then
318
319 Begin
320 ghr_history_fetch.fetch_positionei
321 (p_position_id => l_position_id
322 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
323 ,p_date_effective => l_effective_date
324 ,p_pos_ei_data => l_pos_ei_data);
325
326 if l_pos_ei_data.poei_information3 is not null then
327 FOR c_grd in cur_grd(to_number(l_pos_ei_data.poei_information3))
328 LOOP
329 l_pay_plan := c_grd.pay_plan;
330 l_grade_or_level := c_grd.grade_or_level;
331 END LOOP;
332 end if;
333
334 IF l_pay_plan in ('ES','EP','IE','FE') THEN
335 ---
336 --- Process Log
337 ---
338 l_check_date := l_effective_date;
339 l_name := null;
340 l_ssn := null;
341 FOR cur_emp_det_rec IN cur_emp_det
342 LOOP
343 l_name := cur_emp_det_rec.full_name;
344 l_ssn := cur_emp_det_rec.national_identifier;
345 --Bug #9329643
346 l_employee_number := cur_emp_det_rec.employee_number;
347 EXIT;
348 END LOOP;
349
350 FOR tab_name IN cur_user_pay_tab_name
351 LOOP
352 l_from_user_tab_name := tab_name.user_table_name;
353 END LOOP;
354
355 if l_name is not null then
356 --Bug # 9329643 Modified SSN to Emp No
357 l_log_text := ' Name : ' || l_name || ', Emp No : ' || l_employee_number;
358 else
359 l_log_text := ' Name : Vacant Position';
360 end if;
361 l_log_text := substr(l_log_text || ', Position Title : ' ||
362 ghr_api.get_position_title_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
363 l_log_text := substr(l_log_text || ', Position Number: ' ||
364 ghr_api.get_position_desc_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
365 l_log_text := substr(l_log_text || ', Sequence Number: ' ||
366 ghr_api.get_position_sequence_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
367 l_log_text := substr(l_log_text || ', From Pay Table : ' || l_from_user_tab_name,1,2000);
368 l_log_text := substr(l_log_text || ', To Pay Table : ' || 'ESSL',1,2000);
369 l_log_text := substr(l_log_text || ', Effective Date : ' || to_char(l_check_date,'DD-MON-YYYY'),1,2000);
370
371 g_do_not_cascade := 'Y';
372 ghr_position_extra_info_api.update_position_extra_info
373 ( p_position_extra_info_id => l_pos_ei_data.position_extra_info_id
374 , p_effective_date => l_effective_date
375 , p_object_version_number => l_pos_ei_data.object_version_number
376 , p_poei_information3 => l_pos_ei_data.poei_information3
377 , p_poei_information5 => to_char(l_essl_tab_id));
378 g_do_not_cascade := 'N';
379
380 l_log_text := substr(l_log_text || ', Message : ' || 'Record Inserted',1,2000);
381
382 ghr_validate_perwsdpo.validate_perwsdpo(l_position_id,l_effective_date);
383 ghr_validate_perwsdpo.update_posn_status(l_position_id,l_effective_date);
384
385 l_counter1 := l_counter1 + 1;
386 ghr_wgi_pkg.create_ghr_errorlog(
387 p_program_name => g_proc_name,
388 p_message_name => 'INS-GHR_PA_HISTORY',
389 p_log_text => l_log_text,
390 p_log_date => sysdate);
391
392 END IF;
393 Exception when others then
394 l_log_text := substr(l_log_text || ', Message : ' || 'Record Not Inserted',1,2000);
395 ghr_wgi_pkg.create_ghr_errorlog(
396 p_program_name => g_proc_name,
397 p_message_name => 'INSERR-GHR_PA_HISTORY',
398 p_log_text => substr(l_log_text || 'DB ERROR is : ' || SQLERRM,1,2000),
399 p_log_date => sysdate);
400 End;
401
402 end if;
403 ELSE
404 ---
405 --- Process Log
406 ---
407 l_check_date := l_his_eff_date;
408 l_name := null;
409 l_ssn := null;
410 FOR cur_emp_det_rec IN cur_emp_det
411 LOOP
412 l_name := cur_emp_det_rec.full_name;
413 l_ssn := cur_emp_det_rec.national_identifier;
414 --bug # 9329643
415 l_employee_number := cur_emp_det_rec.employee_number;
416 EXIT;
417 END LOOP;
418
419 FOR tab_name IN cur_user_pay_tab_name
420 LOOP
421 l_from_user_tab_name := tab_name.user_table_name;
422 END LOOP;
423
424 if l_name is not null then
425 --Bug # 9329643 Modified SSN to Emp No
426 l_log_text := ' Name : ' || l_name || ', Emp No : ' || l_employee_number;
427 else
428 l_log_text := ' Name : Vacant Position';
429 end if;
430 l_log_text := substr(l_log_text || ', Position Title : ' ||
431 ghr_api.get_position_title_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
432 l_log_text := substr(l_log_text || ', Position Number: ' ||
433 ghr_api.get_position_desc_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
434 l_log_text := substr(l_log_text || ', Sequence Number: ' ||
435 ghr_api.get_position_sequence_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
436 l_log_text := substr(l_log_text || ', From Pay Table : ' || l_from_user_tab_name,1,2000);
437 l_log_text := substr(l_log_text || ', To Pay Table : ' || 'ESSL',1,2000);
438 l_log_text := substr(l_log_text || ', Effective Date : ' || to_char(l_check_date,'DD-MON-YYYY'),1,2000);
439
440 UPDATE GHR_PA_HISTORY
441 SET information11 = to_char(l_essl_tab_id)
442 WHERE pa_history_id = l_hist_id;
443
444 IF SQL%NOTFOUND THEN
445 l_log_text := substr(l_log_text || ', Message : ' || 'ERROR NOT UPDATED ,',1,2000);
446 ghr_wgi_pkg.create_ghr_errorlog(
447 p_program_name => g_proc_name,
448 p_message_name => 'UPDERR-GHR_PA_HISTORY',
449 p_log_text => substr(l_log_text || 'DB ERROR is : ' || SQLERRM,1,2000),
450 p_log_date => sysdate);
451 ELSE
452 l_counter := l_counter + 1;
453 l_log_text := substr(l_log_text || ', Message : ' || 'Updated Successful ',1,2000);
454 ghr_wgi_pkg.create_ghr_errorlog(
455 p_program_name => g_proc_name,
456 p_message_name => 'UPD-GHR_PA_HISTORY',
457 p_log_text => l_log_text,
458 p_log_date => sysdate);
459
460 END IF;
461
462 END IF;
463
464 END IF;
465 END LOOP; -- Hist records cursor
466
467 IF l_counter1 = 0 AND l_counter = 0 THEN
468 ghr_wgi_pkg.create_ghr_errorlog(
469 p_program_name => g_proc_name,
470 p_message_name => 'GHR_PA_HISTORY',
471 p_log_text => 'Error : NO Valid History Position Records found for Update',
472 p_log_date => sysdate);
473 ELSE
474 ghr_wgi_pkg.create_ghr_errorlog(
475 p_program_name => g_proc_name,
476 p_message_name => 'TOT-GHR_PA_HISTORY',
477 p_log_text => 'Total Records inserted with SES equivalent plans are ' ||
478 to_char(l_counter1) || ' and Updates are ' || to_char(l_counter),
479 p_log_date => sysdate);
480 END IF;
481
482 END; -- End of Procedure
483
484 END; -- End of Package