[Home] [Help]
PACKAGE BODY: APPS.GHR_SES_CONV_PKG
Source
1 PACKAGE BODY GHR_SES_CONV_PKG
2 /* $Header: ghsescon.pkb 115.5 2004/02/05 21:37:03 asubrahm noship $ */
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 --
140 --
141 CURSOR cur_emp_det IS
142 SELECT per.full_name full_name
143 ,per.national_identifier national_identifier
144 from per_people_f per
145 ,per_assignments_f paf
146 where paf.person_id = per.person_id
147 and l_check_date
148 between paf.effective_start_date and paf.effective_end_date
149 and l_check_date
150 between per.effective_start_date and per.effective_end_date
151 and paf.position_id = l_position_id;
152
153 BEGIN
154
155 hr_utility.set_location( 'Entering : ' || l_proc, 10);
156 l_prog_name := Fnd_profile.value('CONC_REQUEST_ID');
157 hr_utility.set_location('l_prog_name conc_request_id :' || l_prog_name ,11);
158
159 if l_prog_name = '-1' then
160 l_prog_name := NULL;
161 else
162 g_proc_name := g_proc_name || '_' || l_prog_name;
163 end if;
164
165 FOR c_user_paytab in cur_user_pay_tab(l_user_tab_name)
166 LOOP
167 l_essl_tab_id := c_user_paytab.user_table_id;
168 END LOOP;
169
170 ---
171 --- 1. Update position extra information for ES equivalent pay plans with the pay table id
172 --- of ESSL a open pay range pay table.
173 --- i) Here available status is not checked because there is no date concept.
174 --- ii)Also date is not checked because the per position extra is not a date track table.
175 ---
176
177 FOR c_pos IN cur_pos_ei(l_essl_tab_id)
178 LOOP
179
180 l_position_id := c_pos.position_id;
181 l_posei_id := c_pos.position_extra_info_id;
182 l_grd_id := c_pos.grade_id;
183 l_user_tab_id := c_pos.user_tab_id;
184 ---
185 --- Fetch the pay plan to compare with ES equivalent
186 ---
187 FOR c_grd in cur_grd(l_grd_id)
188
189 LOOP
190 l_pay_plan := c_grd.pay_plan;
191 l_grade_or_level := c_grd.grade_or_level;
192 END LOOP;
193 ---
194 --- Compare the pay plans with ES equivalent
195 ---
196
197 IF l_pay_plan in ('ES','EP','IE','FE') THEN
198
199 UPDATE PER_POSITION_EXTRA_INFO
200 SET poei_information5 = to_char(l_essl_tab_id)
201 WHERE current of cur_pos_ei;
202
203 FOR tab_name IN cur_user_pay_tab_name
204 LOOP
205 l_from_user_tab_name := tab_name.user_table_name;
206 END LOOP;
207 ---
208 --- Log Message
209 ---
210 l_log_text := ' Position Title : ' || ghr_api.get_position_title_pos(l_position_id,p_business_group_id);
211 l_log_text := substr(l_log_text || ', Position Number: ' ||
212 ghr_api.get_position_desc_no_pos(l_position_id,p_business_group_id),1,2000);
213 l_log_text := substr(l_log_text || ', Sequence Number: ' ||
214 ghr_api.get_position_sequence_no_pos(l_position_id,p_business_group_id),1,2000);
215 l_log_text := substr(l_log_text || ', From Pay Table : ' || l_from_user_tab_name,1,2000);
216 l_log_text := substr(l_log_text || ', To Pay Table : ' || 'ESSL',1,2000);
217
218 IF SQL%NOTFOUND then
219 ---
220 --- Update failed then capture the error and Enter the log.
221 ---
222 l_log_text := substr(l_log_text || ', Message : ' || 'ERROR NOT UPDATED ,',1,2000);
223 ghr_wgi_pkg.create_ghr_errorlog(
224 p_program_name => g_proc_name,
225 p_message_name => 'ERR-PER_POSITION_EXTRA_INFO',
226 p_log_text => substr(l_log_text || 'DB ERROR is : ' || SQLERRM,1,2000),
227 p_log_date => sysdate);
228 ELSE
229 l_counter := l_counter + 1;
230
231 ---
232 --- Update Successful - Enter log.
233 ---
234 l_log_text := substr(l_log_text || ', Message : ' || 'Updated Successful ',1,2000);
235 ghr_wgi_pkg.create_ghr_errorlog(
236 p_program_name => g_proc_name,
237 p_message_name => 'PER_POSITION_EXTRA_INFO',
238 p_log_text => l_log_text,
239 p_log_date => sysdate);
240 END IF;
241
242
243 END IF; --- for the condtion of ('ES','EP','IE','FE').
244
245 END LOOP;
246
247 IF l_counter = 0 THEN
248 ---
249 --- No Records in PER_POSITION_EXTRA_INFO - so Enter log.
250 ---
251 ghr_wgi_pkg.create_ghr_errorlog(
252 p_program_name => g_proc_name,
253 p_message_name => 'NO-PER_POSITION_EXTRA_INFO',
254 p_log_text => 'Error : NO Valid Extra Position Info records found for Update' ,
255 p_log_date => sysdate);
256 ELSE
257 ---
258 --- All Records in PER_POSITION_EXTRA_INFO updated - so Enter log.
259 ---
260 ghr_wgi_pkg.create_ghr_errorlog(
261 p_program_name => g_proc_name,
262 p_message_name => 'TOT-PER_POSITION_EXTRA_INFO',
263 p_log_text => 'Total Records updated with SES equivalent plans are ' || to_char(l_counter),
264 p_log_date => sysdate);
265 END IF;
266
267
268 --
269 -- 2. Start fetching Positions records on history table
270 -- i) Check for the Available status id for the effective date of the history table.
271 -- ii) Check for the row on the date of 11-JAN-2004 and if not exists create one provided the
272 -- date of creation of the position is less than 11-JAN-2004.
273 -- iii)Update the rows for the effective_date > 11-JAN-2004.
274 --
275 l_counter := 0;
276
277 FOR hist_rec IN cur_hist_rows(l_essl_tab_id)
278 LOOP
279
280
281 l_hist_id := hist_rec.pa_history_id;
282 l_position_id := hist_rec.information4;
283 l_grd_id := hist_rec.information9;
284 l_his_eff_date := hist_rec.effective_date;
285 l_user_tab_id := hist_rec.user_tab_id;
286
287 FOR c_grd in cur_grd(l_grd_id)
288 LOOP
289 l_pay_plan := c_grd.pay_plan;
290 l_grade_or_level := c_grd.grade_or_level;
291 END LOOP;
292
293 IF l_pay_plan in ('ES','EP','IE','FE') THEN
294
295
296 IF l_effective_date >= l_his_eff_date THEN
297
298 l_pos_valid := FALSE;
299 l_his_match := FALSE;
300
301 FOR cur_pos_valid_rec IN cur_pos_valid
302 LOOP
303 l_pos_valid := TRUE;
304 END LOOP;
305
306 if l_pos_valid then
307 FOR cur_his_cut_off_date_rec IN cur_his_cut_off_date
308 LOOP
309 l_his_match := TRUE;
310 END LOOP;
311 end if;
312
313 if l_pos_valid AND not l_his_match then
314
315 Begin
316 ghr_history_fetch.fetch_positionei
317 (p_position_id => l_position_id
318 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
319 ,p_date_effective => l_effective_date
320 ,p_pos_ei_data => l_pos_ei_data);
321
322 if l_pos_ei_data.poei_information3 is not null then
323 FOR c_grd in cur_grd(to_number(l_pos_ei_data.poei_information3))
324 LOOP
325 l_pay_plan := c_grd.pay_plan;
326 l_grade_or_level := c_grd.grade_or_level;
327 END LOOP;
328 end if;
329
330 IF l_pay_plan in ('ES','EP','IE','FE') THEN
331 ---
332 --- Process Log
333 ---
334 l_check_date := l_effective_date;
335 l_name := null;
336 l_ssn := null;
337 FOR cur_emp_det_rec IN cur_emp_det
338 LOOP
339 l_name := cur_emp_det_rec.full_name;
340 l_ssn := cur_emp_det_rec.national_identifier;
341 EXIT;
342 END LOOP;
343
344 FOR tab_name IN cur_user_pay_tab_name
345 LOOP
346 l_from_user_tab_name := tab_name.user_table_name;
347 END LOOP;
348
349 if l_name is not null then
350 l_log_text := ' Name : ' || l_name || ', SSN : ' || l_ssn;
351 else
352 l_log_text := ' Name : Vacant Position';
353 end if;
354 l_log_text := substr(l_log_text || ', Position Title : ' ||
355 ghr_api.get_position_title_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
356 l_log_text := substr(l_log_text || ', Position Number: ' ||
357 ghr_api.get_position_desc_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
358 l_log_text := substr(l_log_text || ', Sequence Number: ' ||
359 ghr_api.get_position_sequence_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
360 l_log_text := substr(l_log_text || ', From Pay Table : ' || l_from_user_tab_name,1,2000);
361 l_log_text := substr(l_log_text || ', To Pay Table : ' || 'ESSL',1,2000);
362 l_log_text := substr(l_log_text || ', Effective Date : ' || to_char(l_check_date,'DD-MON-YYYY'),1,2000);
363
364 g_do_not_cascade := 'Y';
365 ghr_position_extra_info_api.update_position_extra_info
366 ( p_position_extra_info_id => l_pos_ei_data.position_extra_info_id
367 , p_effective_date => l_effective_date
368 , p_object_version_number => l_pos_ei_data.object_version_number
369 , p_poei_information3 => l_pos_ei_data.poei_information3
370 , p_poei_information5 => to_char(l_essl_tab_id));
371 g_do_not_cascade := 'N';
372
373 l_log_text := substr(l_log_text || ', Message : ' || 'Record Inserted',1,2000);
374
375 ghr_validate_perwsdpo.validate_perwsdpo(l_position_id,l_effective_date);
376 ghr_validate_perwsdpo.update_posn_status(l_position_id,l_effective_date);
377
378 l_counter1 := l_counter1 + 1;
379 ghr_wgi_pkg.create_ghr_errorlog(
380 p_program_name => g_proc_name,
381 p_message_name => 'INS-GHR_PA_HISTORY',
382 p_log_text => l_log_text,
383 p_log_date => sysdate);
384
385 END IF;
386 Exception when others then
387 l_log_text := substr(l_log_text || ', Message : ' || 'Record Not Inserted',1,2000);
388 ghr_wgi_pkg.create_ghr_errorlog(
389 p_program_name => g_proc_name,
390 p_message_name => 'INSERR-GHR_PA_HISTORY',
391 p_log_text => substr(l_log_text || 'DB ERROR is : ' || SQLERRM,1,2000),
392 p_log_date => sysdate);
393 End;
394
395 end if;
396 ELSE
397 ---
398 --- Process Log
399 ---
400 l_check_date := l_his_eff_date;
401 l_name := null;
402 l_ssn := null;
403 FOR cur_emp_det_rec IN cur_emp_det
404 LOOP
405 l_name := cur_emp_det_rec.full_name;
406 l_ssn := cur_emp_det_rec.national_identifier;
407 EXIT;
408 END LOOP;
409
410 FOR tab_name IN cur_user_pay_tab_name
411 LOOP
412 l_from_user_tab_name := tab_name.user_table_name;
413 END LOOP;
414
415 if l_name is not null then
416 l_log_text := ' Name : ' || l_name || ', SSN : ' || l_ssn;
417 else
418 l_log_text := ' Name : Vacant Position';
419 end if;
420 l_log_text := substr(l_log_text || ', Position Title : ' ||
421 ghr_api.get_position_title_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
422 l_log_text := substr(l_log_text || ', Position Number: ' ||
423 ghr_api.get_position_desc_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
424 l_log_text := substr(l_log_text || ', Sequence Number: ' ||
425 ghr_api.get_position_sequence_no_pos(l_position_id,p_business_group_id,l_check_date),1,2000);
426 l_log_text := substr(l_log_text || ', From Pay Table : ' || l_from_user_tab_name,1,2000);
427 l_log_text := substr(l_log_text || ', To Pay Table : ' || 'ESSL',1,2000);
428 l_log_text := substr(l_log_text || ', Effective Date : ' || to_char(l_check_date,'DD-MON-YYYY'),1,2000);
429
430 UPDATE GHR_PA_HISTORY
431 SET information11 = to_char(l_essl_tab_id)
432 WHERE pa_history_id = l_hist_id;
433
434 IF SQL%NOTFOUND THEN
435 l_log_text := substr(l_log_text || ', Message : ' || 'ERROR NOT UPDATED ,',1,2000);
436 ghr_wgi_pkg.create_ghr_errorlog(
437 p_program_name => g_proc_name,
438 p_message_name => 'UPDERR-GHR_PA_HISTORY',
439 p_log_text => substr(l_log_text || 'DB ERROR is : ' || SQLERRM,1,2000),
440 p_log_date => sysdate);
441 ELSE
442 l_counter := l_counter + 1;
443 l_log_text := substr(l_log_text || ', Message : ' || 'Updated Successful ',1,2000);
444 ghr_wgi_pkg.create_ghr_errorlog(
445 p_program_name => g_proc_name,
446 p_message_name => 'UPD-GHR_PA_HISTORY',
447 p_log_text => l_log_text,
448 p_log_date => sysdate);
449
450 END IF;
451
452 END IF;
453
454 END IF;
455 END LOOP; -- Hist records cursor
456
457 IF l_counter1 = 0 AND l_counter = 0 THEN
458 ghr_wgi_pkg.create_ghr_errorlog(
459 p_program_name => g_proc_name,
460 p_message_name => 'GHR_PA_HISTORY',
461 p_log_text => 'Error : NO Valid History Position Records found for Update',
462 p_log_date => sysdate);
463 ELSE
464 ghr_wgi_pkg.create_ghr_errorlog(
465 p_program_name => g_proc_name,
466 p_message_name => 'TOT-GHR_PA_HISTORY',
467 p_log_text => 'Total Records inserted with SES equivalent plans are ' ||
468 to_char(l_counter1) || ' and Updates are ' || to_char(l_counter),
469 p_log_date => sysdate);
470 END IF;
471
472 END; -- End of Procedure
473
474 END; -- End of Package