1 Package Body GHR_HISTORY_FETCH as
2 /* $Header: ghhisfet.pkb 120.16.12020000.4 2012/12/27 10:27:40 vmididho ship $ */
3 -- Global Constant
4 c_not_found varchar2(30):='not_found';
5 --made the declaration in header
6 --g_info_type per_position_extra_info.information_type%type;
7
8 Procedure Traverse(
9 p_pa_history_id in number,
10 p_root_pa_request_id in number,
11 p_noa_id in number,
12 p_information1 in number,
13 p_table_name in varchar2,
14 p_result out nocopy boolean,
15 p_hist_data out nocopy ghr_pa_history%rowtype);
16
17 Procedure get_min_hist_id(
18 p_pa_request_id in number,
19 p_noa_id in number,
20 p_pa_history_id out nocopy number,
21 p_result out nocopy boolean);
22
23
24 Procedure Fetch_for_correction(
25 p_table_name in varchar2,
26 p_information1 in number default null,
27 p_date_effective in date default null,
28 p_altered_pa_request_id in number default null,
29 p_noa_id_corrected in number default null,
30 p_hist_data out nocopy ghr_pa_history%rowtype,
31 p_result_code out nocopy varchar2 );
32
33 Procedure get_hist_rec_ason_max_date(
34 p_information1 in varchar2,
35 p_max_date_effective in date,
36 p_table_name in varchar2,
37 p_hist_data out nocopy ghr_pa_history%rowtype,
38 p_result out nocopy Boolean);
39
40 Procedure get_hist_rec_ason_date(
41 p_information1 in varchar2,
42 p_date_effective in date,
43 p_table_name in varchar2,
44 p_pa_history_id in number,
45 p_hist_data out nocopy ghr_pa_history%rowtype,
46 p_result out nocopy Boolean);
47
48 Procedure filter_best_candidate_record(
49 p_hist_data in ghr_pa_history%rowtype,
50 p_pa_req_id_skip in number default NULL,
51 p_noa_id_skip in number default NULL,
52 p_save_noa_id in out nocopy number,
53 p_save_found in out nocopy boolean,
54 p_save_history_id in out nocopy number,
55 p_save_pa_request_id in out nocopy number,
56 p_save_pa_hist_data in out nocopy ghr_pa_history%rowtype);
57
58 Procedure fetch_for_histid (
59 p_table_name in varchar2,
60 p_information1 in number default null,
61 p_date_effective in date default null,
62 p_pa_history_id in number default null,
63 p_hist_data out nocopy ghr_pa_history%rowtype,
64 p_result_code out nocopy varchar2 );
65
66
67 Procedure fetch_for_date_eff(
68 p_table_name in varchar2,
69 p_information1 in number default null,
70 p_date_effective in date default null,
71 p_hist_data out nocopy ghr_pa_history%rowtype,
72 p_result_code out nocopy varchar2 );
73
74
75 Procedure Fetch_hist_data(
76 p_table_name in varchar2,
77 p_information1 in number default null,
78 p_date_effective in date default null,
79 p_altered_pa_request_id in number default null,
80 p_noa_id_corrected in number default null,
81 p_pa_history_id in number default null,
82 p_hist_data out nocopy ghr_pa_history%rowtype,
83 p_result_code out nocopy varchar2 );
84
85 --
86 -- ----------------------------------------------------------------------------
87 -- |--------------------------< <Ghr_History_Fetch> >--------------------------|
88 -- ----------------------------------------------------------------------------
89
90 --
91 -- This procedue fetches the most recent record as of p_max_date_Effective
92 -- it also traverses in the correction chain to find the most recent correction of the
93 -- chain.
94 --
95
96 Procedure get_hist_rec_ason_max_date(
97 p_information1 in varchar2,
98 p_max_date_effective in date,
99 p_table_name in varchar2,
100 p_hist_data out nocopy ghr_pa_history%rowtype,
101 p_result out nocopy Boolean) is
102
103
104 l_proc varchar2(30):='get_hist_rec_ason_max_date';
105
106 l_history_id number;
107 l_root_pa_request_id number;
108 l_noa_id number;
109 l_save_pa_history_data ghr_pa_history%rowtype;
110 l_hist_rec ghr_pa_history%rowtype;
111
112 l_found boolean:=FALSE;
113
114 l_root_hist_id_broken number;
115 l_root_pa_req_id_broken number;
116
117 -- This cursor fetches the most recent record for cp_max_date_effective
118 cursor ghr_hist_ndt_canc3 (cp_information1 in varchar2,
119 cp_max_date_effective in date,
120 cp_table_name in varchar2) is
121 select *
122 from ghr_pa_history hist_1
123 where ( altered_pa_request_id is null OR
124 not exists (select 'exists'
125 from ghr_pa_history hist_2
126 where hist_1.altered_pa_request_id = hist_2.pa_request_id
127 and hist_1.information1 = hist_2.information1
128 and hist_1.nature_of_action_id = hist_2.nature_of_action_id
129 and hist_1.table_name = hist_2.table_name)
130 )
131 and information1 = cp_information1
132 and effective_date = cp_max_date_effective
133 and table_name = cp_table_name
134 order by pa_history_id desc;
135
136 Begin
137
138 hr_utility.set_location (' Entering : ' || l_proc, 10);
139
140 for ghr_hist_ndt_canc3_rec in
141 ghr_hist_ndt_canc3 (cp_information1 => p_information1,
142 cp_max_date_effective => p_max_date_effective,
143 cp_table_name => p_table_name)
144 Loop
145 l_hist_rec := ghr_hist_ndt_canc3_rec;
146
147 filter_best_candidate_record(
148 p_hist_data => l_hist_rec,
149 p_save_noa_id => l_noa_id,
150 p_save_found => l_found,
151 p_save_history_id => l_history_id,
152 p_save_pa_request_id => l_root_pa_request_id,
153 p_save_pa_hist_data => l_save_pa_history_data);
154
155 End loop;
156
157 if not l_found then
158 p_result := FALSE;
159 hr_utility.set_location( 'NOT FOUND ' || l_proc, 50);
160 else
161 if l_root_pa_request_id is null then
162 -- ie core form change is the pre_record
163 -- so no need to traverse.
164 p_hist_data := l_save_pa_history_data;
165 p_result := TRUE;
166 else
167 hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
168 Traverse(
169 p_pa_history_id => NULL,
170 p_root_pa_request_id => l_root_pa_request_id,
171 p_noa_id => l_noa_id,
172 p_information1 => p_information1,
173 p_table_name => p_table_name,
174 p_result => l_found,
175 p_hist_data => p_hist_data);
176
177
178 if not l_found then
179 p_result := FALSE;
180 hr_utility.set_location('Traverse Failed ' || l_proc, 70);
181 hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
182 hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
183
184 hr_utility.set_message(8301,'GHR_38496_TRAVERSE_FAILED');
185 hr_utility.raise_error;
186 else
187 p_result := TRUE;
188 end if;
189 end if;
190 end if;
191
192 hr_utility.set_location (' Leaving : ' || l_proc, 100);
193
194 End;
195
196 -- This procedure fetched most recent, prior to p_pa_history_id, on p_date_Effective
197 -- It also traverses in the chain if it finds the record on p_date_Effective.
198 Procedure get_hist_rec_ason_date(
199 p_information1 in varchar2,
200 p_date_effective in date,
201 p_table_name in varchar2,
202 p_pa_history_id in number,
203 p_hist_data out nocopy ghr_pa_history%rowtype,
204 p_result out nocopy Boolean) is
205
206
207 l_proc varchar2(30):='get_hist_rec_ason_date';
208
209 l_root_pa_history_id number;
210 l_history_id number;
211 l_root_pa_request_id number;
212 l_noa_id number;
213 l_save_pa_history_data ghr_pa_history%rowtype;
214 l_hist_rec ghr_pa_history%rowtype;
215
216 l_found boolean:=FALSE;
217
218 l_root_hist_id_broken number;
219 l_root_pa_req_id_broken number;
220 l_pa_request_id_for_hist number;
221 l_noa_id_for_hist number;
222 l_root_pa_req_for_pa_req number;
223 l_noa_id_for_pa_req number;
224 -- This cursor fetches records from history table for table_name = p_table_name
225 -- for the same date as cp_date_effective, where row is either a root pa_request record
226 -- or core form record with history_id less than cp_root_pa_history_id ie record were created
227 -- before cp_root_pa_history_id was created or if it is a broken chain record then its root
228 -- must have been created before cp_root_pa_history_id.
229 cursor ghr_hist_ndt_canc(cp_root_pa_history_id in number,
230 cp_information1 in varchar2,
231 cp_date_effective in date,
232 cp_pa_history_id in number,
233 cp_table_name in varchar2) is
234 select *
235 from ghr_pa_history hist_1
236 where
237 -- root request or broken chain)
238 ( altered_pa_request_id is null OR
239 not exists (select 'exists'
240 from ghr_pa_history hist_2
241 where hist_1.altered_pa_request_id = hist_2.pa_request_id
242 and hist_1.information1 = hist_2.information1
243 and hist_1.nature_of_action_id = hist_2.nature_of_action_id
244 and hist_1.table_name = hist_2.table_name)
245 )
246 -- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
247 and (cp_root_pa_history_id >=
248 (select min(pa_history_id)
249 from ghr_pa_history
250 where pa_request_id =
251 (select min(pa_request_id)
252 from ghr_pa_requests
253 connect by pa_request_id = prior altered_pa_request_id
254 start with pa_request_id = (select pa_request_id
255 from ghr_pa_history
256 where pa_history_id = hist_1.pa_history_id))
257 and nature_of_action_id = hist_1.nature_of_action_id)
258 -- or fetch record created by core form change with lower history_id
259 OR
260 (cp_root_pa_history_id >= hist_1.pa_history_id and
261 hist_1.pa_request_id is null))
262 -- Bug #6356058 modified above to pick the latest core form change
263 -- with history id greater than the root.
264 /*(hist_1.pa_history_id >= cp_root_pa_history_id and
265 hist_1.pa_request_id is null))*/
266 and information1 = cp_information1
267 and effective_date = cp_date_effective
268 and pa_history_id <> cp_pa_history_id
269 and table_name = cp_table_name
270 order by pa_history_id desc;
271
272 cursor get_root_hist_id(
273 cp_pa_req_id in number,
274 cp_noa_id in number) is
275 select min(pa_history_id),
276 min(pa_request_id),
277 min(nature_of_action_id)
278 from ghr_pa_history
279 where pa_request_id =
280 (select min(pa_request_id)
281 from ghr_pa_requests
282 connect by pa_request_id = prior altered_pa_request_id
283 start with pa_request_id = cp_pa_req_id)
284 and nature_of_action_id = cp_noa_id;
285
286 cursor get_req_and_noa (cp_pa_history_id in number) is
287 select
288 pa_request_id,
289 nature_of_action_id
290 from ghr_pa_history
291 where pa_history_id = cp_pa_history_id;
292
293 Begin
294
295 hr_utility.set_location (' Entering : ' || l_proc, 10);
296
297 open get_req_and_noa( p_pa_history_id);
298 fetch get_req_and_noa into
299 l_pa_request_id_for_hist,
300 l_noa_id_for_hist;
301 close get_req_and_noa;
302
303 open get_root_hist_id(
304 cp_pa_req_id => l_pa_request_id_for_hist,
305 cp_noa_id => l_noa_id_for_hist) ;
306 fetch get_root_hist_id into
307 l_root_pa_history_id,
308 l_root_pa_req_for_pa_req,
309 l_noa_id_for_pa_req;
310
311 if get_root_hist_id%notfound then
312 -- error
313 close get_root_hist_id;
314 hr_utility.set_message(8301,'GHR_38352_ROOT_HISTID_NFND');
315 hr_utility.raise_error;
316 else
317 close get_root_hist_id;
318 hr_utility.set_location('l_root_pa_req_for_pa_req : ' || l_root_pa_req_for_pa_req || l_proc, 12);
319 hr_utility.set_location('l_noa_id_for_pa_req : ' || l_noa_id_for_pa_req || l_proc, 12);
320
321 if l_root_pa_req_for_pa_req = l_pa_request_id_for_hist and
322 l_noa_id_for_pa_req = l_noa_id_for_hist then
323 NULL;
324 else
325 hr_utility.set_location(' Skip settings NULLed' || l_proc, 12);
326 l_root_pa_req_for_pa_req := NULL;
327 l_noa_id_for_pa_req := NULL;
328 end if;
329 end if;
330
331 hr_utility.set_location( 'l_root_pa_history_id :' || l_root_pa_history_id || l_proc, 13);
332 hr_utility.set_location( 'p_pa_history_id : ' || p_pa_history_id || l_proc, 13);
333 hr_utility.set_location( 'p_information1 : ' || p_information1 || l_proc, 13);
334 hr_utility.set_location( 'p_p_table_name : ' || p_table_name || l_proc, 13);
335 hr_utility.set_location( 'p_date_Effective : ' || p_date_Effective || l_proc, 13);
336
337 for ghr_hist_ndt_canc_rec in
338 ghr_hist_ndt_canc(cp_root_pa_history_id => l_root_pa_history_id,
339 cp_information1 => p_information1,
340 cp_date_effective => p_date_effective,
341 cp_pa_history_id => p_pa_history_id,
342 cp_table_name => p_table_name)
343
344 Loop
345 l_hist_rec := ghr_hist_ndt_canc_rec;
346 filter_best_candidate_record(
347 p_hist_data => l_hist_rec,
348 p_pa_req_id_skip => l_root_pa_req_for_pa_req,
349 p_noa_id_skip => l_noa_id_for_pa_req,
350 p_save_noa_id => l_noa_id,
351 p_save_found => l_found,
352 p_save_history_id => l_history_id,
353 p_save_pa_request_id => l_root_pa_request_id,
354 p_save_pa_hist_data => l_save_pa_history_data);
355 End loop;
356
357 if not l_found then
358 p_result := FALSE;
359 hr_utility.set_location( 'NOT FOUND ' || l_proc, 50);
360 else
361 if l_root_pa_request_id is null then
362 -- ie core form change is the pre_record
363 -- so no need to traverse.
364 p_hist_data := l_save_pa_history_data;
365 p_result := TRUE;
366 else
367 hr_utility.set_location('Selected Root Hist Id ' || l_root_pa_history_id || l_proc, 60);
368 hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
369
370 Traverse(
371 p_pa_history_id => p_pa_history_id,
372 p_root_pa_request_id => l_root_pa_request_id,
373 p_noa_id => l_noa_id,
374 p_information1 => p_information1,
375 p_table_name => p_table_name,
376 p_result => l_found,
377 p_hist_data => p_hist_data);
378
379 if not l_found then
380 p_result := FALSE;
381 hr_utility.set_location(' p_pa_history_id : ' || p_pa_history_id || l_proc, 65);
382 hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
383 hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384
385 hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386 hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387 hr_utility.raise_error;
388 else
389 hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
390 hr_utility.set_location('Selected PaRequest Id ' || p_hist_data.pa_request_id || l_proc, 62);
391 p_result := TRUE;
392 end if;
393 end if;
394 end if;
395
396 hr_utility.set_location (' Leaving : ' || l_proc, 100);
397
398 End get_hist_rec_ason_date;
399
400 -- This procedure is called by get_hist_rec_ason_max_date and get_hist_rec_ason_date
401 -- it decides if p_hist_data is the best candidate record so far.
402
403 Procedure filter_best_candidate_record(
404 p_hist_data in ghr_pa_history%rowtype,
405 p_pa_req_id_skip in number default NULL,
406 p_noa_id_skip in number default NULL,
407 p_save_noa_id in out nocopy number,
408 p_save_found in out nocopy boolean,
409 p_save_history_id in out nocopy number,
410 p_save_pa_request_id in out nocopy number,
411 p_save_pa_hist_data in out nocopy ghr_pa_history%rowtype) is
412
413
414 -- this cursor fetches min(pahistory_id) for cp_pa_request_id and cp_nature_of_Action_id
415 cursor c_get_min_hist (cp_pa_request_id in number,
416 cp_nature_of_Action_id in number) is
417 select
418 min(pa_history_id)
419 from ghr_pa_history
420 where
421 pa_request_id = cp_pa_request_id
422 and nature_of_action_id = nvl(cp_nature_of_action_id, nature_of_action_id);
423
424 -- This cursor finds the root pa_request_id
425 cursor c_get_root_req (cp_pa_request_id in number) is
426 select
427 min(pa_request_id)
428 from ghr_pa_requests
429 connect by pa_request_id = prior altered_pa_request_id
430 start with pa_request_id = cp_pa_request_id;
431
432 --- Bug 6314442 start
433 --- Even if the history id is less after checking the core form changes
434 --- if the pa_request_id is not null check for correction if any ---
435
436 cursor cur_pa_corr is
437 select pa_history_id
438 from ghr_pa_history
439 where effective_date = p_hist_data.effective_date
440 and pa_history_id > p_save_history_id
441 and information1 = p_hist_data.information1
442 and table_name = p_hist_data.table_name
443 and pa_request_id in (select pa_request_id
444 from ghr_pa_requests
445 where pa_notification_id is not null
446 start with pa_request_id = p_hist_data.pa_request_id
447 connect by prior pa_request_id = altered_pa_request_id);
448
449 l_corr_record_found BOOLEAN;
450 --- Bug 6314442 end
451
452
453 l_root_hist_id_broken number;
454 l_root_pa_req_id_broken number;
455
456 l_proc varchar2(30):='filter_best_candidate_record';
457
458 Begin
459 l_corr_record_found := FALSE;
460 hr_utility.set_location (' Entering : ' || l_proc, 10);
461 hr_utility.set_location (' p_hist_data.pa_history_id ' || p_hist_data.pa_history_id, 11);
462 hr_utility.set_location (' p_pa_req_id_skip ' || p_pa_req_id_skip, 11);
463 hr_utility.set_location (' p_noa_id_skip ' || p_noa_id_skip, 11);
464
465 if p_hist_data.pa_request_id is null then
466 -- Core Form change created this record.
467 hr_utility.set_location (' Core Form 1' || l_proc, 20);
468 if p_hist_data.pa_history_id > nvl(p_save_history_id, 0) then
469 hr_utility.set_location (' Accept Core Form 2' || l_proc, 21);
470 p_save_found := TRUE;
471 p_save_history_id := p_hist_data.pa_history_id;
472 p_save_pa_request_id := null;
473 p_save_noa_id := null;
474 p_save_pa_hist_data := p_hist_data;
475 end if;
476 elsif p_hist_data.altered_pa_request_id is NULL then
477 -- root pa_request_id record.
478 hr_utility.set_location (' Root pa req 1' || l_proc, 30);
479 if p_hist_data.pa_request_id = nvl(p_pa_req_id_skip, -1) and
480 p_hist_data.nature_of_action_id = nvl(p_noa_id_skip, -1) then
481 hr_utility.set_location('Skip this record : Hist ID ' || p_hist_data.pa_history_id, 35);
482 else
483
484 --Bug 6314442 added to check any corrections are available after manual changes
485 -- Bug # 9800367 considered to check the corrections available greater than the
486 -- saved pa history id only if the highest is core change
487 if p_save_pa_request_id is null then
488 for cur_pa_corr_rec in cur_pa_corr
489 loop
490 l_corr_record_found := TRUE;
491 end loop;
492 end if;
493
494 --Bug 6314442 added l_corr_record_found
495 -- Bug # 6976905 added another condition while checking correction record for dual action
496 if p_hist_data.pa_history_id > nvl(p_save_history_id, 0) or (l_corr_record_found and nvl(p_hist_data.pa_request_id,'-1') <> nvl(p_save_pa_request_id,'-1')) then
497 hr_utility.set_location (' Accept Root pa req 2' || l_proc, 31);
498 p_save_found := TRUE;
499 p_save_history_id := p_hist_data.pa_history_id;
500 p_save_pa_request_id := p_hist_data.pa_request_id;
501 p_save_noa_id := p_hist_data.nature_of_action_id;
502 p_save_pa_hist_data := p_hist_data;
503 end if;
504 end if;
505 else
506 -- Borken chain root
507 -- get root pa_request_id
508 hr_utility.set_location (' Broken Chain 1' || l_proc, 40);
509 open c_get_root_req( p_hist_data.pa_request_id);
510 Fetch c_get_root_req into l_root_pa_req_id_broken;
511 if c_get_root_req%notfound then
512 close c_get_root_req;
513 hr_utility.set_location (' Root for Broken Chain not found (Error) ' || l_proc, 41);
514 -- this must never happen
515 -- raise error;
516 else
517 close c_get_root_req;
518 end if;
519 -- get root pa_history_id on the basis of pa_request_id and NOA_id
520
521 open c_get_min_hist ( l_root_pa_req_id_broken,
522 p_hist_data.nature_of_action_id);
523 Fetch c_get_min_hist into l_root_hist_id_broken;
524 if c_get_min_hist%notfound then
525 close c_get_min_hist;
526 hr_utility.set_location (' Min Hist not found Broken Chain (Error) ' || l_proc, 42);
527 -- this must never happen
528 -- raise error;
529 else
530 close c_get_min_hist;
531 end if;
532
533 if l_root_pa_req_id_broken = nvl(p_pa_req_id_skip, -1) and
534 p_hist_data.nature_of_action_id = nvl(p_noa_id_skip, -1) then
535
536 hr_utility.set_location('Skip this record : Hist ID ' || p_hist_data.pa_history_id, 50);
537 else
538 if l_root_hist_id_broken > nvl(p_save_history_id, 0) then
539 hr_utility.set_location (' Accept Broken Chain 3' || l_proc, 43);
540 p_save_found := TRUE;
541 p_save_history_id := l_root_hist_id_broken;
542 p_save_pa_request_id := l_root_pa_req_id_broken;
543 p_save_noa_id := p_hist_data.nature_of_action_id;
544 p_save_pa_hist_data := p_hist_data;
545
546 end if;
547 end if;
548 end if;
549
550 hr_utility.set_location (' p_save_noa_id ' || p_save_noa_id, 90);
551 hr_utility.set_location (' p_save_history_id ' || p_save_history_id, 90);
552 hr_utility.set_location (' p_save_pa_request_id ' || p_save_pa_request_id, 90);
553 hr_utility.set_location (' Leaving : ' || l_proc, 100);
554
555
556 End;
557
558
559 Procedure fetch_for_histid (
560 p_table_name in varchar2,
561 p_information1 in number default null,
562 p_date_effective in date default null,
563 p_pa_history_id in number default null,
564 p_hist_data out nocopy ghr_pa_history%rowtype,
565 p_result_code out nocopy varchar2 ) is
566
567 l_proc varchar2(30):='Fetch_for_histid';
568 l_max_date_effective date;
569 l_date_effective date;
570 l_root_pa_history_id ghr_pa_history.pa_history_id%type;
571 l_hist_data ghr_pa_history%rowtype;
572 l_found boolean:=FALSE;
573
574 l_result_code varchar2(30);
575
576 -- This cursor fetches records from history table for table_name = p_table_name
577 -- for the same date as cp_date_effective, where row is either a root pa_request record
578 -- or core form record with history_id less than cp_root_pa_history_id ie record were created
579 -- before cp_root_pa_history_id was created or if it is a broken chain record then its root
580 -- must have been created before cp_root_pa_history_id.
581 cursor ghr_hist_ndt_canc(cp_root_pa_history_id in number,
582 cp_information1 in varchar2,
583 cp_date_effective in date,
584 cp_pa_history_id in number,
585 cp_table_name in varchar2) is
586 select *
587 from ghr_pa_history hist_1
588 where
589 -- root request or broken chain)
590 ( altered_pa_request_id is null OR
591 not exists (select 'exists'
592 from ghr_pa_history hist_2
593 where hist_1.altered_pa_request_id = hist_2.pa_request_id
594 and hist_1.information1 = hist_2.information1
595 and hist_1.nature_of_action_id = hist_2.nature_of_action_id)
596 )
597 -- and pa_history_id of the root of the record must be <= cp_root_pa_history_id
598 and (cp_root_pa_history_id >=
599 (select min(pa_history_id)
600 from ghr_pa_history
601 where pa_request_id =
602 (select min(pa_request_id)
603 from ghr_pa_requests
604 connect by pa_request_id = prior altered_pa_request_id
605 start with pa_request_id = (select pa_request_id
606 from ghr_pa_history
607 where pa_history_id = hist_1.pa_history_id))
608 and nature_of_action_id = hist_1.nature_of_action_id)
609 -- or fetch record created by core form change with lower history_id
610 OR
611 (cp_root_pa_history_id >= hist_1.pa_history_id and
612 hist_1.pa_request_id is null))
613 and information1 = cp_information1
614 and effective_date = cp_date_effective
615 and pa_history_id <> cp_pa_history_id
616 and table_name = cp_table_name
617 order by pa_history_id desc;
618
619 -- This cursor fetched the date on which te record was created for assignment_ei table
620 -- prior to cp_date_effective (ie date of the predecessor record).
621 cursor ghr_hist_ndt_canc2 (cp_information1 in varchar2,
622 cp_date_effective in date,
623 cp_table_name in varchar2) is
624 select max(effective_date)
625 from ghr_pa_history hist_1
626 where
627 -- root request or broken chain)
628 ( altered_pa_request_id is null OR
629 not exists (select 'exists'
630 from ghr_pa_history hist_2
631 where hist_1.altered_pa_request_id = hist_2.pa_request_id
632 and hist_1.information1 = hist_2.information1
633 and hist_1.nature_of_action_id = hist_2.nature_of_action_id
634 and hist_1.table_name = hist_2.table_name)
635 )
636 and information1 = cp_information1
637 and effective_date < cp_date_effective
638 and table_name = cp_table_name;
639
640 -- Bug # 6635881 This cursor fetched the date on which te record was created and correction has
641 --been done on the same day for eg:- A new position has been created on the same day
642 --of the RPA Action and doing correction to assign that new position then position details need to be fetched
643 cursor ghr_hist_ndt_canc3 (cp_information1 in varchar2,
644 cp_date_effective in date,
645 cp_table_name in varchar2,
646 cp_pa_history_id in number) is
647 select max(effective_date)
648 from ghr_pa_history hist_1
649 where
650 -- root request or broken chain)
651 ( altered_pa_request_id is null OR
652 not exists (select 'exists'
653 from ghr_pa_history hist_2
654 where hist_1.altered_pa_request_id = hist_2.pa_request_id
655 and hist_1.information1 = hist_2.information1
656 and hist_1.nature_of_action_id = hist_2.nature_of_action_id
657 and hist_1.table_name = hist_2.table_name)
658 )
659 and information1 = cp_information1
660 and effective_date = cp_date_effective
661 and table_name = cp_table_name
662 and pa_history_id <> cp_pa_history_id;
663
664
665
666 Begin
667
668 /* get pre. */
669 hr_utility.set_location( 'Entering : ' || l_proc, 10);
670 hr_utility.set_location( ' Displaying parameters : ' || l_proc, 11);
671 hr_utility.set_location( ' p_table_name : ' || p_table_name || l_proc, 11);
672 hr_utility.set_location( ' p_information1 : ' || p_information1 || l_proc, 11);
673 hr_utility.set_location( ' p_pa_history_id : ' || p_pa_history_id || l_proc, 11);
674
675 get_hist_rec_ason_date(
676 p_information1 => p_information1,
677 p_date_effective => p_date_effective,
678 p_table_name => p_table_name,
679 p_pa_history_id => p_pa_history_id ,
680 p_hist_data => l_hist_data,
681 p_result => l_found);
682 hr_utility.set_location (' End Loop 1' || l_proc, 60);
683
684 p_hist_data := l_hist_data;
685
686 ---Bug 2413991 --AVR
687 if not l_found then
688 if g_info_type = 'GHR_US_POS_VALID_GRADE' then
689 fetch_for_date_eff(
690 p_table_name => p_table_name,
691 p_information1 => p_information1,
692 p_date_effective => p_date_effective,
693 p_hist_data => l_hist_data,
694 p_result_code => l_result_code);
695
696 p_hist_data := l_hist_data;
697 if l_result_code = c_not_found then
698 l_found := FALSE;
699 else
700 l_found := TRUE;
701 end if;
702 end if;
703 end if;
704 ---Bug 2413991 --AVR
705
706 if not l_found then
707 hr_utility.set_location( 'Loop1 not found ' || l_proc, 70);
708 open ghr_hist_ndt_canc2
709 (cp_information1 => p_information1,
710 cp_date_effective => p_date_effective,
711 cp_table_name => p_table_name);
712 fetch ghr_hist_ndt_canc2 into l_max_date_effective;
713 if ( l_max_date_effective is null ) then
714 /* max function always returns a result, so checking for %NOTFOUND is always false.
715 date_effective is a mandatory column, so we can check if there were any rows by
716 checking if the max_date_effective is null. */
717 /* handle case where there is no pre here. */
718 close ghr_hist_ndt_canc2;
719 open ghr_hist_ndt_canc3
720 (cp_information1 => p_information1,
721 cp_date_effective => p_date_effective,
722 cp_table_name => p_table_name,
723 cp_pa_history_id => p_pa_history_id);
724 fetch ghr_hist_ndt_canc3 into l_date_effective;
725 if ( l_date_effective is null ) then
726 hr_utility.set_location( 'NOT FOUND ' || l_proc, 80);
727 p_result_code := c_not_found;
728 close ghr_hist_ndt_canc3;
729 else
730 close ghr_hist_ndt_canc3;
731 get_hist_rec_ason_max_date(
732 p_information1 => p_information1,
733 p_max_date_effective => l_date_effective,
734 p_table_name => p_table_name,
735 p_hist_data => l_hist_data,
736 p_result => l_found);
737 p_hist_data := l_hist_data;
738 if not l_found then
739 /* handle case where there is no pre here. */
740 p_result_code := c_not_found;
741 hr_utility.set_location (' NOT Found ' || l_proc, 150);
742 else
743 hr_utility.set_location (' Found ' || l_proc, 160);
744 end if;
745 end if;
746 else
747 close ghr_hist_ndt_canc2;
748 hr_utility.set_location( 'l_max_date_effective : ' || l_max_date_effective || l_proc, 80);
749 get_hist_rec_ason_max_date(
750 p_information1 => p_information1,
751 p_max_date_effective => l_max_date_effective,
752 p_table_name => p_table_name,
753 p_hist_data => l_hist_data,
754 p_result => l_found);
755 p_hist_data := l_hist_data;
756 if not l_found then
757 /* handle case where there is no pre here. */
758 p_result_code := c_not_found;
759 hr_utility.set_location (' NOT Found ' || l_proc, 150);
760 else
761 hr_utility.set_location (' Found ' || l_proc, 160);
762 end if;
763 end if;
764 End if;
765 hr_utility.set_location (' Leaving ' || l_proc, 112);
766 End;
767
768 Procedure fetch_for_date_eff(
769 p_table_name in varchar2,
770 p_information1 in number default null,
771 p_date_effective in date default null,
772 p_hist_data out nocopy ghr_pa_history%rowtype,
773 p_result_code out nocopy varchar2 ) is
774
775 l_proc varchar2(30):='gen_fet1';
776 l_max_date_effective date;
777 l_hist_data ghr_pa_history%rowtype;
778
779 l_found boolean:=FALSE;
780
781 -- This cursor finds the date of the record which was last created as of cp_date_Effective.
782 cursor ghr_hist_post (cp_information1 in varchar2,
783 cp_date_effective in date,
784 cp_table_name in varchar2) is
785 select max(effective_date)
786 from ghr_pa_history hist_1
787 where ( altered_pa_request_id is null OR
788 not exists (select 'exists'
789 from ghr_pa_history hist_2
790 where hist_1.altered_pa_request_id = hist_2.pa_request_id
791 and hist_1.information1 = hist_2.information1
792 and hist_1.nature_of_action_id = hist_2.nature_of_action_id
793 and hist_1.table_name = hist_2.table_name)
794 )
795 and information1 = cp_information1
796 and effective_date <= cp_date_effective
797 and table_name = cp_table_name;
798
799 Begin
800
801 /* This part of the procedure will fetch the Pre-record values
802 if called before update to database updates the record.
803 if update to database has already applied the changes then it
804 will return the post-update record. The session variable
805 pre-update-record will hold the values which can be used for
806 pre-record values
807 */
808 hr_utility.set_location( l_proc, 20);
809 open ghr_hist_post(
810 cp_information1 => p_information1,
811 cp_date_effective => p_date_effective,
812 cp_table_name => p_table_name);
813
814 fetch ghr_hist_post into l_max_date_effective;
815 if ( l_max_date_effective is null ) then
816 /* max function always returns a result, so checking for %NOTFOUND is always false.
817 date_effective is a mandatory column, so we can check if there were any rows by
818 checking if the max_date_effective is null. */
819 hr_utility.set_location( l_proc, 70);
820 /* handle case where there is no pre here. */
821 p_result_code := c_not_found;
822 close ghr_hist_post;
823 else
824 close ghr_hist_post;
825 get_hist_rec_ason_max_date(
826 p_information1 => p_information1,
827 p_max_date_effective => l_max_date_effective,
828 p_table_name => p_table_name,
829 p_hist_data => l_hist_data,
830 p_result => l_found);
831
832 if not l_found then
833 /* handle case where there is no pre here. */
834 p_result_code := c_not_found;
835 hr_utility.set_location (' NOT Found ' || l_proc, 80);
836 else
837 hr_utility.set_location (' Found ' || l_proc, 80);
838 p_hist_data := l_hist_data;
839 end if;
840 end if;
841
842 hr_utility.set_location( ' Leaving : ' || l_proc, 100);
843
844 End fetch_for_date_eff;
845
846
847 Procedure Traverse(
848 p_pa_history_id in number,
849 p_root_pa_request_id in number,
850 p_noa_id in number,
851 p_information1 in number,
852 p_table_name in varchar2,
853 p_result out nocopy boolean,
854 p_hist_data out nocopy ghr_pa_history%rowtype) is
855
856 -- This cursor traverses in the correction tree and finds the last node in the chain
857 -- other than with history_id = cp_pa_history_id.
858 -- Bug 3278827 Added +0 to nature_of_action_id to use GHR_PA_HISTORY_N1 Index.
859 cursor ghr_hist_ndt_traverse_corrs(cp_pa_history_id in number default hr_api.g_number,
860 cp_pa_request_id in number,
861 cp_noa_id in number,
862 cp_information1 in varchar2,
863 cp_table_name in varchar2) is
864 select *
865 from ghr_pa_history
866 where information1 = cp_information1
867 and pa_history_id <> nvl(cp_pa_history_id, 0)
868 and table_name = cp_table_name
869 and pa_request_id in
870 (select pa_request_id
871 from ghr_pa_requests
872 start with pa_request_id = cp_pa_request_id
873 connect by prior pa_request_id = altered_pa_request_id)
874 and nature_of_action_id + 0 = cp_noa_id
875 order by pa_history_id desc;
876
877 l_hist_data ghr_pA_history%rowtype;
878 l_proc varchar2(30):='Traverse';
879 Begin
880 -- traverse.
881 hr_utility.set_location (' Entering : ' || l_proc, 10);
882 hr_utility.set_location ('hist id ' || p_pa_history_id || l_proc, 20);
883 hr_utility.set_location ('pa req ' || p_root_pa_request_id || l_proc, 30);
884 hr_utility.set_location ('noa Id ' || p_noa_id || l_proc, 40);
885
886 open ghr_hist_ndt_traverse_corrs(
887 cp_pa_history_id => p_pa_history_id,
888 cp_pa_request_id => p_root_pa_request_id,
889 cp_noa_id => p_noa_id,
890 cp_information1 => p_information1,
891 cp_table_name => p_table_name);
892
893 fetch ghr_hist_ndt_traverse_corrs into l_hist_data;
894 p_result:= ghr_hist_ndt_traverse_corrs%found;
895 close ghr_hist_ndt_traverse_corrs;
896 p_hist_data := l_hist_data;
897 hr_utility.set_location ('Hist ID : ' || l_hist_data.pa_history_id, 90);
898 hr_utility.set_location ('Leaving : ' || l_proc, 100);
899 End;
900
901 Procedure Fetch_for_correction(
902 p_table_name in varchar2,
903 p_information1 in number default null,
904 p_date_effective in date default null,
905 p_altered_pa_request_id in number default null,
906 p_noa_id_corrected in number default null,
907 p_hist_data out nocopy ghr_pa_history%rowtype,
908 p_result_code out nocopy varchar2 ) is
909
910 cursor ghr_hist_corr(
911 cp_information1 in varchar2,
912 cp_altered_pa_request_id in number,
913 cp_noa_id_corrected in number,
914 cp_table_name in varchar2) is
915 SELECT /*+ index(GHR_PA_HISTORY GHR_PA_HISTORY_N1) */ *
916 from ghr_pa_history
917 where information1 = cp_information1
918 and nature_of_action_id = cp_noa_id_corrected
919 and pa_request_id = cp_altered_pa_request_id
920 and table_name = cp_table_name;
921
922 --Begin bug# 13633700
923 cursor ghr_get_hist_max_date (cp_information1 in varchar2,
924 cp_date_effective in date,
925 cp_table_name in varchar2) is
926 select max(effective_date)
927 from ghr_pa_history hist_1
928 where ( altered_pa_request_id is null OR
929 not exists (select 'exists'
930 from ghr_pa_history hist_2
931 where hist_1.altered_pa_request_id = hist_2.pa_request_id
932 and hist_1.information1 = hist_2.information1
933 and hist_1.nature_of_action_id = hist_2.nature_of_action_id
934 and hist_1.table_name = hist_2.table_name)
935 )
936 and information1 = cp_information1
937 and effective_date < cp_date_effective
938 and table_name = cp_table_name;
939
940 l_max_date_effective date;
941 l_found boolean:=FALSE;
942 --End bug# 13633700
943 l_pa_history_id number;
944 l_result boolean;
945 l_proc varchar2(30):='Fetch_for_correction';
946
947 Begin
948
949 hr_utility.set_location (' Entering ' || l_proc, 10);
950
951
952 open ghr_hist_corr(
953 cp_information1 => p_information1,
954 cp_altered_pa_request_id => p_altered_pa_request_id,
955 cp_noa_id_corrected => p_noa_id_corrected,
956 cp_table_name => p_table_name) ;
957
958 fetch ghr_hist_corr into p_hist_data;
959 if ( ghr_hist_corr%NOTFOUND ) then
960 hr_utility.set_location ( l_proc, 20);
961 get_min_hist_id(
962 p_pa_request_id => p_altered_pa_request_id,
963 p_noa_id => p_noa_id_corrected,
964 p_pa_history_id => l_pa_history_id,
965 p_result => l_result);
966 if l_result then
967 fetch_for_histid (
968 p_table_name => p_table_name,
969 p_information1 => p_information1,
970 p_date_effective => p_date_effective,
971 p_pa_history_id => l_pa_history_id,
972 p_hist_data => p_hist_data,
973 p_result_code => p_result_code);
974 else
975 hr_utility.set_location (' NOT Found ' || l_proc, 40);
976 --Begin Bug# 13633700
977 --Earlier for every action one history record used to be created with Perf Appraisal SIT , but after the fix 12618741
978 -- it is not creating. So fetch for correction is returning not found record for some actions.
979 -- Now added following code to get at least one history record less than RPA effective date to fix the issue.
980
981 open ghr_get_hist_max_date
982 (cp_information1 => p_information1,
983 cp_date_effective => p_date_effective,
984 cp_table_name => p_table_name);
985
986 fetch ghr_get_hist_max_date into l_max_date_effective;
987
988 if ( l_max_date_effective is null ) then
989 /* max function always returns a result, so checking for %NOTFOUND is always false.
990 date_effective is a mandatory column, so we can check if there were any rows by
991 checking if the max_date_effective is null. */
992 hr_utility.set_location( 'MAX Date NOT FOUND ' || l_proc, 80);
993 p_result_code := c_not_found;
994 close ghr_get_hist_max_date;
995 else
996 close ghr_get_hist_max_date;
997 hr_utility.set_location( 'l_max_date_effective : ' || l_max_date_effective || l_proc, 90);
998 get_hist_rec_ason_max_date(
999 p_information1 => p_information1,
1000 p_max_date_effective => l_max_date_effective,
1001 p_table_name => p_table_name,
1002 p_hist_data => p_hist_data,
1003 p_result => l_found);
1004 if not l_found then
1005 p_result_code := c_not_found;
1006 hr_utility.set_location (' History NOT Found with Max Date ' || l_proc, 150);
1007 else
1008 hr_utility.set_location (' History Found with Max Date ' || l_proc, 160);
1009 end if;
1010 end if;
1011
1012 --End Bug# 13633700
1013 end if;
1014 end if;
1015 close ghr_hist_corr;
1016
1017 hr_utility.set_location( 'Leaving : ' || l_proc, 100);
1018
1019 End Fetch_for_correction;
1020
1021
1022 Procedure Fetch_hist_data(
1023 p_table_name in varchar2,
1024 p_information1 in number default null,
1025 p_date_effective in date default null,
1026 p_altered_pa_request_id in number default null,
1027 p_noa_id_corrected in number default null,
1028 p_pa_history_id in number default null,
1029 p_hist_data out nocopy ghr_pa_history%rowtype,
1030 p_result_code out nocopy varchar2 ) is
1031
1032 l_proc varchar2(30):='Fetch_hist_data';
1033 Begin
1034
1035 hr_utility.set_location (' Entering ' || l_proc, 10);
1036 if p_pa_history_id is not null then
1037 fetch_for_histid (
1038 p_table_name => p_table_name,
1039 p_information1 => p_information1,
1040 p_date_effective => p_date_effective,
1041 p_pa_history_id => p_pa_history_id,
1042 p_hist_data => p_hist_data,
1043 p_result_code => p_result_code);
1044
1045 elsif p_altered_pa_request_id is not null and
1046 p_noa_id_corrected is not null then
1047 Fetch_for_correction(
1048 p_table_name => p_table_name,
1049 p_information1 => p_information1,
1050 p_date_effective => p_date_effective,
1051 p_altered_pa_request_id => p_altered_pa_request_id,
1052 p_noa_id_corrected => p_noa_id_corrected,
1053 p_hist_data => p_hist_data,
1054 p_result_code => p_result_code);
1055
1056 elsif p_date_effective is not null then
1057 fetch_for_date_eff(
1058 p_table_name => p_table_name,
1059 p_information1 => p_information1,
1060 p_date_effective => p_date_effective,
1061 p_hist_data => p_hist_data,
1062 p_result_code => p_result_code);
1063 end if;
1064 hr_utility.set_location (' Leaving ' || l_proc, 100);
1065
1066 End Fetch_hist_data;
1067
1068 --
1069 -- Procedure get_min_hist_id will get the pa_history_id for the pa_request_id and noa
1070 --
1071 Procedure get_min_hist_id(
1072 p_pa_request_id in number,
1073 p_noa_id in number,
1074 p_pa_history_id out nocopy number,
1075 p_result out nocopy boolean) is
1076
1077 cursor get_hist_id
1078 (cp_pa_request_id in number,
1079 cp_noa_id in number) is
1080 select pa_history_id
1081 from ghr_pa_history
1082 where pa_request_id = cp_pa_request_id
1083 and nature_of_action_id = cp_noa_id;
1084
1085 l_proc varchar2(30):='get_min_hist_id';
1086
1087 Begin
1088 hr_utility.set_location('Entering:'|| l_proc, 5);
1089 open get_hist_id(
1090 cp_pa_request_id => p_pa_request_id,
1091 cp_noa_id => p_noa_id);
1092 fetch get_hist_id into p_pa_history_id;
1093 p_result := get_hist_id%found;
1094 close get_hist_id;
1095 hr_utility.set_location('Leaving:'|| l_proc, 10);
1096
1097 End get_min_hist_id;
1098
1099 /* Following pacakge is redundent
1100 --
1101 --
1102 -- Procedure get_hist_id will get the pa_history_id for the pa_request_id and noa
1103 --
1104 Procedure get_hist_id(
1105 p_pa_request_id in number,
1106 p_noa_id in number,
1107 p_pa_history_id out nocopy number,
1108 p_result out nocopy boolean) is
1109 cursor get_hist_id is
1110 select pa_history_id
1111 from ghr_pa_history
1112 where pa_request_id = p_pa_request_id
1113 and nature_of_action_id = p_noa_id;
1114 l_proc varchar2(30):='get_hist_id';
1115 Begin
1116 hr_utility.set_location('Entering:'|| l_proc, 5);
1117 open get_hist_id;
1118 fetch get_hist_id into p_pa_history_id;
1119 if get_hist_id%notfound then
1120 p_result := FALSE;
1121 else
1122 p_result := TRUE;
1123 end if;
1124 close get_hist_id;
1125 End;
1126
1127 */
1128
1129 --
1130 -- Procedure fetch_people fetches the last record from per_people_f or ghr_pa_history
1131 -- which was created between effective start date and effective end date
1132 --
1133 Procedure fetch_people (
1134 p_person_id in number default null,
1135 p_date_effective in date default null,
1136 p_altered_pa_request_id in number default null,
1137 p_noa_id_corrected in number default null,
1138 p_rowid in rowid default null,
1139 p_pa_history_id in number default null,
1140 p_people_data out nocopy per_all_people_f%rowtype,
1141 p_result_code out nocopy varchar2 ) is
1142 l_result_code varchar2(100);
1143 l_hist_data ghr_pa_history%rowtype;
1144 l_proc varchar2(30):='fetch_people';
1145 l_people_data per_all_people_f%rowtype;
1146
1147 cursor per_people_f_cursor is
1148 select *
1149 from per_all_people_f
1150 where person_id = p_person_id
1151 and p_date_effective between effective_start_date and effective_end_date;
1152
1153 cursor per_people_f_rowid_cursor is
1154 select *
1155 from per_all_people_f
1156 where rowid = p_rowid;
1157 Begin
1158 hr_utility.set_location('Entering:'|| l_proc, 5);
1159 p_result_code := null;
1160 if ( p_rowid is not null ) then
1161 /* This part of the procedure is used to fetch the exact row
1162 which will be the post-update record. So if the procedure was
1163 passed with p_row_id parameter it'll always return the
1164 post-update record.
1165 */
1166 hr_utility.set_location( l_proc, 10);
1167 open per_people_f_rowid_cursor;
1168 fetch per_people_f_rowid_cursor into p_people_data;
1169 if ( per_people_f_rowid_cursor%NOTFOUND ) then
1170 p_result_code := c_not_found;
1171 end if;
1172 close per_people_f_rowid_cursor;
1173 hr_utility.set_location( l_proc, 15);
1174 elsif ( p_pa_history_id is null and
1175 p_altered_pa_request_id is null and
1176 p_noa_id_corrected is null ) then
1177 /* This part of the procedure will fetch the Pre-record values
1178 if called before update to database updates the record.
1179 if update to database has already applied the changes then it
1180 will return the post-update record. The session variable
1181 pre-update-record will hold the values which can be used for
1182 pre-record values
1183 */
1184 hr_utility.set_location( l_proc, 20);
1185 open per_people_f_cursor;
1186 fetch per_people_f_cursor into p_people_data;
1187 if ( per_people_f_cursor%NOTFOUND ) then
1188 p_result_code := c_not_found;
1189 end if;
1190 close per_people_f_cursor;
1191 hr_utility.set_location( l_proc, 25);
1192 else
1193 Fetch_hist_data(
1194 p_table_name => ghr_history_api.g_peop_table,
1195 p_information1 => p_person_id,
1196 p_date_effective => p_date_effective,
1197 p_altered_pa_request_id => p_altered_pa_request_id,
1198 p_noa_id_corrected => p_noa_id_corrected,
1199 p_pa_history_id => p_pa_history_id,
1200 p_hist_data => l_hist_data,
1201 p_result_code => l_result_code);
1202
1203 p_result_code := l_result_code;
1204 if nvl(l_result_code, 'found') <> c_not_found then
1205 ghr_history_conv_rg.conv_to_people_rg(
1206 p_history_data => l_hist_data,
1207 p_people_data => l_people_data);
1208 p_people_data := l_people_data;
1209 end if;
1210
1211 end if;
1212 hr_utility.set_location(' Leaving:'||l_proc, 45);
1213 exception
1214 when no_data_found then
1215 p_result_code := c_not_found;
1216 when others then
1217 raise;
1218 End fetch_people;
1219 --
1220 Procedure fetch_asgei (
1221 p_assignment_extra_info_id in number default null,
1222 p_date_effective in date default null,
1223 p_altered_pa_request_id in number default null,
1224 p_noa_id_corrected in number default null,
1225 p_rowid in rowid default null,
1226 p_pa_history_id in number default null,
1227 p_get_ovn_flag in varchar2 default 'N' ,
1228 p_asgei_data out nocopy per_assignment_extra_info%rowtype,
1229 p_result_code out nocopy varchar2 ) is
1230
1231 cursor per_asgei_rowid_cursor is
1232 select *
1233 from per_assignment_extra_info
1234 where rowid = p_rowid;
1235
1236 cursor c_get_ovn is
1237 select object_version_number
1238 from per_assignment_extra_info
1239 where assignment_extra_info_id = p_assignment_extra_info_id;
1240
1241 l_result_code varchar2(30);
1242 l_asgei_data per_assignment_extra_info%rowtype;
1243 l_hist_data ghr_pa_history%rowtype;
1244 l_proc varchar2(30):='fetch_asgei';
1245
1246 Begin
1247 hr_utility.set_location('Entering:'|| l_proc, 5);
1248 p_result_code := null;
1249 if ( p_rowid is not null ) then
1250 hr_utility.set_location( l_proc, 10);
1251 open per_asgei_rowid_cursor;
1252 fetch per_asgei_rowid_cursor into p_asgei_data;
1253 if ( per_asgei_rowid_cursor%NOTFOUND ) then
1254 p_result_code := c_not_found;
1255 end if;
1256 close per_asgei_rowid_cursor;
1257 hr_utility.set_location( l_proc, 15);
1258 else
1259 Fetch_hist_data(
1260 p_table_name => ghr_history_api.g_asgnei_table,
1261 p_information1 => p_assignment_extra_info_id,
1262 p_date_effective => p_date_effective,
1263 p_altered_pa_request_id => p_altered_pa_request_id,
1264 p_noa_id_corrected => p_noa_id_corrected,
1265 p_pa_history_id => p_pa_history_id,
1266 p_hist_data => l_hist_data,
1267 p_result_code => l_result_code);
1268
1269 p_result_code := l_result_code;
1270 if nvl(l_result_code, 'found') <> c_not_found then
1271 ghr_history_conv_rg.conv_to_asgnei_rg(
1272 p_history_data => l_hist_data,
1273 p_asgnei_data => l_asgei_data);
1274 p_asgei_data := l_asgei_data;
1275 if upper(p_get_ovn_flag) = 'Y' then
1276 for ovn in c_get_ovn loop
1277 p_asgei_data.object_version_number := ovn.object_version_number;
1278 exit;
1279 end loop;
1280 end if;
1281 end if;
1282 end if;
1283 End;
1284 --
1285 -- Procedure fetches the last record from per_position_extra_info or
1286 -- ghr_position_extra_info_h_v
1287 --
1288 Procedure fetch_positionei (
1289 p_position_extra_info_id in number default null,
1290 p_date_effective in date default null,
1291 p_altered_pa_request_id in number default null,
1292 p_noa_id_corrected in number default null,
1293 p_rowid in rowid default null,
1294 p_pa_history_id in number default null,
1295 p_get_ovn_flag in varchar2 default 'N',
1296 p_posei_data out nocopy per_position_extra_info%rowtype,
1297 p_result_code out nocopy varchar2 ) is
1298
1299 cursor per_posei_rowid_cursor is
1300 select *
1301 from per_position_extra_info
1302 where rowid = p_rowid;
1303
1304 cursor c_get_ovn is
1305 select object_version_number
1306 from per_position_extra_info
1307 where position_extra_info_id = p_position_extra_info_id;
1308
1309 l_result_code varchar2(30);
1310 l_posei_data per_position_extra_info%rowtype;
1311 l_hist_data ghr_pa_history%rowtype;
1312 l_proc varchar2(30):='fetch_positionei';
1313
1314 Begin
1315 hr_utility.set_location('Entering:'|| l_proc, 5);
1316 p_result_code := null;
1317 if ( p_rowid is not null ) then
1318 hr_utility.set_location( l_proc, 10);
1319 open per_posei_rowid_cursor;
1320 fetch per_posei_rowid_cursor into p_posei_data;
1321 if ( per_posei_rowid_cursor%NOTFOUND ) then
1322 p_result_code := c_not_found;
1323 end if;
1324 close per_posei_rowid_cursor;
1325 hr_utility.set_location( l_proc, 15);
1326 else
1327 hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
1328 hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
1329 hr_utility.set_location( l_proc || 'position_extra_info_id: ' || p_position_extra_info_id, 215);
1330
1331 Fetch_hist_data(
1332 p_table_name => ghr_history_api.g_posnei_table,
1333 p_information1 => p_position_extra_info_id,
1334 p_date_effective => p_date_effective,
1335 p_altered_pa_request_id => p_altered_pa_request_id,
1336 p_noa_id_corrected => p_noa_id_corrected,
1337 p_pa_history_id => p_pa_history_id,
1338 p_hist_data => l_hist_data,
1339 p_result_code => l_result_code);
1340
1341 p_result_code := l_result_code;
1342 if nvl(l_result_code, 'found') <> c_not_found then
1343 hr_utility.set_location (' NOT FOUND ' || l_result_code || l_proc, 90);
1344 -- Bug # 7646662 to get the effective date of the history record
1345 g_cascad_eff_date := l_hist_data.effective_date;
1346 ghr_history_conv_rg.conv_to_positionei_rg(
1347 p_history_data => l_hist_data,
1348 p_position_ei_data => l_posei_data);
1349 p_posei_data := l_posei_data;
1350 if upper(p_get_ovn_flag) = 'Y' then
1351 for ovn in c_get_ovn loop
1352 p_posei_data.object_version_number := ovn.object_version_number;
1353 exit;
1354 end loop;
1355 end if;
1356 end if;
1357 end if;
1358 hr_utility.set_location('poei_info5 ' || l_posei_data.poei_information5,1);
1359 hr_utility.set_location ('Leaving : ' || l_proc, 100);
1360
1361 End fetch_positionei;
1362 --
1363 -- Procedure fetch_assignment fetches the last record from per_assignment or ghr_assignments_h_v
1364 --
1365 Procedure fetch_assignment (
1366 p_assignment_id in number default null,
1367 p_date_effective in date default null,
1368 p_altered_pa_request_id in number default null,
1369 p_noa_id_corrected in number default null,
1370 p_rowid in rowid default null,
1371 p_pa_history_id in number default null,
1372 p_assignment_data out nocopy per_all_assignments_f%rowtype,
1373 p_result_code out nocopy varchar2 ) is
1374 l_result_code varchar2(100);
1375 l_hist_data ghr_pa_history%rowtype;
1376 l_proc varchar2(30):='fetch_assignment';
1377 l_assignment_data per_all_assignments_f%rowtype;
1378 cursor per_assignment_f_cursor is
1379 select *
1380 from per_all_assignments_f
1381 where assignment_id = p_assignment_id
1382 and p_date_effective between effective_start_date and effective_end_date;
1383 cursor per_assignment_f_rowid_cursor is
1384 select *
1385 from per_all_assignments_f
1386 where rowid = p_rowid;
1387 Begin
1388 hr_utility.set_location('Entering:'|| l_proc, 5);
1389 p_result_code := null;
1390 if ( p_rowid is not null ) then
1391 /* This part of the procedure is used to fetch the exact row
1392 which will be the post-update record. So if the procedure was
1393 passed with p_row_id parameter it'll always return the
1394 post-update record.
1395 */
1396 hr_utility.set_location( l_proc, 10);
1397 open per_assignment_f_rowid_cursor;
1398 fetch per_assignment_f_rowid_cursor into p_assignment_data;
1399 if ( per_assignment_f_rowid_cursor%NOTFOUND ) then
1400 p_result_code := c_not_found;
1401 end if;
1402 close per_assignment_f_rowid_cursor;
1403 hr_utility.set_location( l_proc, 15);
1404 elsif ( p_pa_history_id is null and
1405 p_altered_pa_request_id is null and
1406 p_noa_id_corrected is null ) then
1407 /* This part of the procedure will fetch the Pre-record values
1408 if called before update to database updates the record.
1409 if update to database has already applied the changes then it
1410 will return the post-update record. The session variable
1411 pre-update-record will hold the values which can be used for
1412 pre-record values
1413 */
1414 hr_utility.set_location( l_proc, 20);
1415 open per_assignment_f_cursor;
1416 fetch per_assignment_f_cursor into p_assignment_data;
1417 if ( per_assignment_f_cursor%NOTFOUND ) then
1418 p_result_code := c_not_found;
1419 end if;
1420 close per_assignment_f_cursor;
1421 hr_utility.set_location( l_proc, 25);
1422 else
1423 Fetch_hist_data(
1424 p_table_name => ghr_history_api.g_asgn_table,
1425 p_information1 => p_assignment_id,
1426 p_date_effective => p_date_effective,
1427 p_altered_pa_request_id => p_altered_pa_request_id,
1428 p_noa_id_corrected => p_noa_id_corrected,
1429 p_pa_history_id => p_pa_history_id,
1430 p_hist_data => l_hist_data,
1431 p_result_code => l_result_code);
1432
1433 p_result_code := l_result_code;
1434 if nvl(l_result_code, 'found') <> c_not_found then
1435 ghr_history_conv_rg.conv_to_asgn_rg(
1436 p_history_data => l_hist_data,
1437 p_assignment_data => l_assignment_data);
1438 p_assignment_data := l_assignment_data;
1439 end if;
1440
1441 end if;
1442 hr_utility.set_location(' Leaving:'||l_proc, 45);
1443 exception
1444 when no_data_found then
1445 p_result_code := c_not_found;
1446 when OTHERS then
1447 raise;
1448 End fetch_assignment;
1449 --
1450 -- Procedure fetch_element_entries fetches the last record from pay_element_entries_f or
1451 -- ghr_elements_entries_h_v
1452 --
1453 Procedure fetch_element_entries (
1454 p_element_entry_id in number default null,
1455 p_date_effective in date default null,
1456 p_altered_pa_request_id in number default null,
1457 p_noa_id_corrected in number default null,
1458 p_rowid in rowid default null,
1459 p_pa_history_id in number default null,
1460 p_element_entry_data out nocopy pay_element_entries_f%rowtype,
1461 p_result_code out nocopy varchar2 ) is
1462 l_result_code varchar2(100);
1463 l_hist_data ghr_pa_history%rowtype;
1464 l_proc varchar2(30) := 'fetch_element_entries';
1465 l_element_entry_data pay_element_entries_f%rowtype;
1466 cursor pay_e_entry_f_cursor is
1467 select *
1468 from pay_element_entries_f
1469 where element_entry_id = p_element_entry_id
1470 and p_date_effective between effective_start_date and effective_end_date;
1471 cursor pay_e_entry_f_rowid_cursor is
1472 select *
1473 from pay_element_entries_f
1474 where rowid = p_rowid;
1475 Begin
1476 hr_utility.set_location('Entering:'|| l_proc, 5);
1477 p_result_code := null;
1478 if ( p_rowid is not null ) then
1479 /* This part of the procedure is used to fetch the exact row
1480 which will be the post-update record. So if the procedure was
1481 passed with p_row_id parameter it'll always return the
1482 post-update record.
1483 */
1484 hr_utility.set_location( l_proc, 10);
1485 open pay_e_entry_f_rowid_cursor;
1486 fetch pay_e_entry_f_rowid_cursor into p_element_entry_data;
1487 if ( pay_e_entry_f_rowid_cursor%NOTFOUND ) then
1488 p_result_code := c_not_found;
1489 end if;
1490 close pay_e_entry_f_rowid_cursor;
1491 hr_utility.set_location( l_proc, 15);
1492 elsif ( p_pa_history_id is null and
1493 p_altered_pa_request_id is null and
1494 p_noa_id_corrected is null ) then
1495 /* This part of the procedure will fetch the Pre-record values
1496 if called before update to database updates the record.
1497 if update to database has already applied the changes then it
1498 will return the post-update record. The session variable
1499 pre-update-record will hold the values which can be used for
1500 pre-record values
1501 */
1502 hr_utility.set_location( l_proc, 20);
1503 open pay_e_entry_f_cursor;
1504 fetch pay_e_entry_f_cursor into p_element_entry_data;
1505 if ( pay_e_entry_f_cursor%NOTFOUND ) then
1506 p_result_code := c_not_found;
1507 end if;
1508 close pay_e_entry_f_cursor;
1509 else
1510 hr_utility.set_location( l_proc, 25);
1511 Fetch_hist_data(
1512 p_table_name => ghr_history_api.g_eleent_table,
1513 p_information1 => p_element_entry_id,
1514 p_date_effective => p_date_effective,
1515 p_altered_pa_request_id => p_altered_pa_request_id,
1516 p_noa_id_corrected => p_noa_id_corrected,
1517 p_pa_history_id => p_pa_history_id,
1518 p_hist_data => l_hist_data,
1519 p_result_code => l_result_code);
1520
1521 p_result_code := l_result_code;
1522 if nvl(l_result_code, 'found') <> c_not_found then
1523 ghr_history_conv_rg.conv_to_element_entry_rg(
1524 p_history_data => l_hist_data,
1525 p_element_entries_data => l_element_entry_data);
1526 p_element_entry_data := l_element_entry_data;
1527 end if;
1528 end if;
1529 hr_utility.set_location(' Leaving:'||l_proc, 45);
1530 exception
1531 when no_data_found then
1532 p_result_code := c_not_found;
1533 when OTHERS then
1534 raise;
1535 End fetch_element_entries;
1536 --
1537 -- Procedure fetch_peopleei fetches the last record from per_people_extra_info or
1538 -- ghr_people_extra_info_h_v
1539 --
1540 Procedure fetch_peopleei (
1541 p_person_extra_info_id in number default null,
1542 p_date_effective in date default null,
1543 p_altered_pa_request_id in number default null,
1544 p_noa_id_corrected in number default null,
1545 p_rowid in rowid default null,
1546 p_pa_history_id in number default null,
1547 p_get_ovn_flag in varchar2 default 'N',
1548 p_peopleei_data in out nocopy per_people_extra_info%rowtype,
1549 p_result_code out nocopy varchar2 ) is
1550 l_result_code varchar2(100);
1551 l_hist_data ghr_pa_history%rowtype;
1552 l_proc varchar2(30) := 'fetch_peopleei';
1553 l_peopleei_data per_people_extra_info%rowtype;
1554 cursor per_peopleei_rowid_cursor is
1555 select *
1556 from per_people_extra_info
1557 where rowid = p_rowid;
1558 cursor c_get_ovn is
1559 select object_version_number
1560 from per_people_extra_info
1561 where person_extra_info_id = p_person_extra_info_id;
1562 Begin
1563 hr_utility.set_location('Entering:'|| l_proc, 5);
1564 hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565 hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566 hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567 hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568 hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569 hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570 hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1571 hr_utility.set_location(' p_person_extra_info_id:'||p_person_extra_info_id || l_proc, 52);
1572 p_result_code := null;
1573 if ( p_rowid is not null ) then
1574 /* This part of the procedure is used to fetch the exact row
1575 which will be the post-update record. So if the procedure was
1576 passed with p_row_id parameter it'll always return the
1577 post-update record.
1578 */
1579 hr_utility.set_location( l_proc, 10);
1580 open per_peopleei_rowid_cursor;
1581 fetch per_peopleei_rowid_cursor into p_peopleei_data;
1582 if ( per_peopleei_rowid_cursor%NOTFOUND ) then
1583 p_result_code := c_not_found;
1584 end if;
1585 close per_peopleei_rowid_cursor;
1586 hr_utility.set_location( l_proc, 15);
1587 else
1588 Fetch_hist_data(
1589 p_table_name => ghr_history_api.g_peopei_table,
1590 p_information1 => p_person_extra_info_id,
1591 p_date_effective => p_date_effective,
1592 p_altered_pa_request_id => p_altered_pa_request_id,
1593 p_noa_id_corrected => p_noa_id_corrected,
1594 p_pa_history_id => p_pa_history_id,
1595 p_hist_data => l_hist_data,
1596 p_result_code => l_result_code);
1597
1598 p_result_code := l_result_code;
1599 if nvl(l_result_code, 'found') <> c_not_found then
1600 hr_utility.set_location (' NOT FOUND ' || l_result_code || l_proc, 90);
1601 ghr_history_conv_rg.conv_to_peopleei_rg(
1602 p_history_data => l_hist_data,
1603 p_people_ei_data => l_peopleei_data);
1604 p_peopleei_data := l_peopleei_data;
1605 if upper(p_get_ovn_flag) = 'Y' then
1606 for ovn in c_get_ovn loop
1607 p_peopleei_data.object_version_number := ovn.object_version_number;
1608 exit;
1609 end loop;
1610 end if;
1611 end if;
1612 end if;
1613
1614 hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 46);
1615 hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 47);
1616 hr_utility.set_location(' Leaving:'||l_proc, 45);
1617 exception
1618 when no_data_found then
1619 p_result_code := c_not_found;
1620 when OTHERS then
1621 raise;
1622 End fetch_peopleei;
1623
1624 Procedure fetch_asgei ( p_assignment_id in number,
1625 p_information_type in varchar2,
1626 p_date_effective in date,
1627 p_asg_ei_data out nocopy per_assignment_extra_info%rowtype
1628 )
1629 is
1630 l_proc varchar2(72) := 'Fetch_Asgei (2)';
1631 l_asg_ei_data ghr_assignment_extra_info_h_v%rowtype;
1632 l_pa_history_id ghr_pa_history.pa_history_id%type;
1633 l_max_effective_date date;
1634 l_session ghr_history_api.g_session_var_type;
1635 l_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type;
1636 l_result varchar2(20);
1637 cursor c_extra_info_id is
1638 select aei.assignment_extra_info_id
1639 from per_assignment_extra_info aei
1640 where aei.assignment_id = p_assignment_id
1641 and aei.information_type = p_information_type;
1642 --Begin Bug 11680558, 11776559,11781442 added aei_information1 and aei_information2
1643 cursor c_det_extra_info_id is
1644 select aei.assignment_extra_info_id
1645 from per_assignment_extra_info aei
1646 where aei.assignment_id = p_assignment_id
1647 and aei.information_type = p_information_type
1648 AND fnd_date.canonical_to_date(aei.aei_information1) = (
1649 select max(fnd_date.canonical_to_date(aei_in.aei_information1)) from per_assignment_extra_info aei_in
1650 where fnd_date.canonical_to_date(aei_in.aei_information1)<= p_date_effective
1651 AND aei_in.information_type=aei.information_type
1652 and aei_in.assignment_id = aei.assignment_id
1653 AND aei_in.assignment_extra_info_id IN(
1654 select to_number(information1) from ghr_pa_history gph
1655 where gph.assignment_id = p_assignment_id
1656 and gph.information5= p_information_type
1657 and gph.pa_request_id is not null));
1658 --End Bug 11680558
1659 Begin
1660 hr_utility.set_location('Entering ' || l_proc,5);
1661 --Begin Bug 11680558
1662 IF p_information_type = 'GHR_US_ASG_DET_INFO' THEN
1663 For l_det_extra_info_id in c_det_extra_info_id loop
1664 l_extra_info_id := l_det_extra_info_id.assignment_extra_info_id;
1665 End loop;
1666 ELSE
1667 --End Bug 11680558
1668 For extra_info in c_extra_info_id loop
1669 l_extra_info_id := extra_info.assignment_extra_info_id;
1670 End loop;
1671 END IF;
1672 If l_extra_info_id is not null then
1673 hr_utility.set_location(l_proc,10);
1674 ghr_history_api.get_g_session_var(l_session);
1675 ghr_history_fetch.fetch_asgei ( p_assignment_extra_info_id => l_extra_info_id,
1676 p_date_effective => p_date_effective,
1677 p_altered_pa_request_id => l_session.altered_pa_request_id,
1678 p_noa_id_corrected => l_session.noa_id_correct,
1679 p_pa_history_id => l_session.pa_history_id,
1680 p_asgei_data => p_asg_ei_data,
1681 p_get_ovn_flag => 'Y',
1682 p_result_code => l_result
1683 );
1684 End if;
1685 hr_utility.set_location('Leaving ' ||l_proc,20);
1686 End fetch_asgei;
1687
1688 Procedure fetch_peopleei(p_person_id in number,
1689 p_information_type in varchar2,
1690 p_date_effective in date,
1691 p_per_ei_data in out nocopy per_people_extra_info%rowtype
1692 )
1693 is
1694 l_proc varchar2(72) := 'Fetch_peoplei (2)';
1695 l_per_ei_data ghr_people_extra_info_h_v%rowtype;
1696 l_pa_history_id ghr_pa_history.pa_history_id%type;
1697 l_max_effective_date date;
1698 l_session ghr_history_api.g_session_var_type;
1699 l_extra_info_id per_people_extra_info.person_extra_info_id%type;
1700 l_result varchar2(20);
1701 cursor c_extra_info_id is
1702 select pei.person_extra_info_id
1703 from per_people_extra_info pei
1704 where pei.person_id = p_person_id
1705 and pei.information_type = p_information_type;
1706 Begin
1707 hr_utility.set_location('Entering ' || l_proc,5);
1708 For extra_info in c_extra_info_id loop
1709 l_extra_info_id := extra_info.person_extra_info_id;
1710 End loop;
1711 If l_extra_info_id is not null then
1712 hr_utility.set_location(l_proc,10);
1713 ghr_history_api.get_g_session_var(l_session);
1714 ghr_history_fetch.fetch_peopleei ( p_person_extra_info_id => l_extra_info_id,
1715 p_date_effective => p_date_effective,
1716 p_altered_pa_request_id => l_session.altered_pa_request_id,
1717 p_noa_id_corrected => l_session.noa_id_correct,
1718 p_pa_history_id => l_session.pa_history_id,
1719 p_peopleei_data => p_per_ei_data,
1720 p_get_ovn_flag => 'Y',
1721 p_result_code => l_result
1722 );
1723 End if;
1724 hr_utility.set_location('Leaving ' ||l_proc,20);
1725 End fetch_peopleei;
1726
1727 Procedure fetch_positionei(p_position_id in number,
1728 p_information_type in varchar2,
1729 p_date_effective in date,
1730 p_pos_ei_data out nocopy per_position_extra_info%rowtype
1731 )
1732 is
1733 l_proc varchar2(72) := 'Fetch_positionei (2)';
1734 l_pos_ei_data ghr_position_extra_info_h_v%rowtype;
1735 l_pa_history_id ghr_pa_history.pa_history_id%type;
1736 l_max_effective_date date;
1737 l_session ghr_history_api.g_session_var_type;
1738 l_extra_info_id per_position_extra_info.position_extra_info_id%type;
1739 l_result varchar2(20);
1740
1741
1742 cursor c_extra_info_id is
1743 select ghr_pos.position_extra_info_id
1744 from per_position_extra_info ghr_pos
1745 where ghr_pos.position_id = p_position_id
1746 and ghr_pos.information_type = p_information_type;
1747
1748 Begin
1749 g_info_type := p_information_type;
1750 hr_utility.set_location('Entering ' || l_proc,5);
1751 hr_utility.set_location('FETCH: Position_id ' || p_position_id ||l_proc,6);
1752 hr_utility.set_location('FETCH: information_type ' || p_information_type || l_proc,7);
1753 for extra_info in c_extra_info_id loop
1754 l_extra_info_id := extra_info.position_extra_info_id;
1755 end loop;
1756 If l_extra_info_id is not null then
1757 hr_utility.set_location(l_proc,10);
1758 ghr_history_api.get_g_session_var(l_session);
1759 ghr_history_fetch.fetch_positionei(p_position_extra_info_id => l_extra_info_id,
1760 p_date_effective => p_date_effective,
1761 p_altered_pa_request_id => l_session.altered_pa_request_id,
1762 p_noa_id_corrected => l_session.noa_id_correct,
1763 p_pa_history_id => l_session.pa_history_id,
1764 p_posei_data => p_pos_ei_data,
1765 p_get_ovn_flag => 'Y',
1766 p_result_code => l_result
1767 );
1768 End if;
1769 -- Bug# 8267598 assigned g_info_type to NULL after fetching position extra info
1770 --BUG # 8406984 Modified to assign NULL to g_info_type after fetching positionei
1771 -- as g_info_type is used in fetch_for_histid if it is not reassigned back to NULL
1772 -- causing issues.
1773 g_info_type := NULL;
1774 hr_utility.set_location('Leaving ' ||l_proc,20);
1775 End fetch_positionei;
1776 -- The following procedure gets the date_effective row from the
1777 -- ghr_pa_history for the element_entry_values table.
1778 --VSM (What about this procedure) ??
1779 Procedure get_date_eff_eleevl(p_element_entry_value_id in number,
1780 p_date_effective in date,
1781 p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1782 p_result_code out nocopy varchar2,
1783 p_pa_history_id out nocopy number) IS
1784 l_hist_data ghr_pa_history%rowtype;
1785 -- l_result Boolean;
1786 l_proc varchar2(30) := 'get_date_eff_eleevl';
1787 l_element_entval_data pay_element_entry_values_f%rowtype;
1788 BEGIN
1789 hr_utility.set_location( l_proc, 20);
1790 fetch_for_date_eff(
1791 p_table_name => ghr_history_api.g_eleevl_table,
1792 p_information1 => p_element_entry_value_id,
1793 p_date_effective => p_date_effective,
1794 p_hist_data => l_hist_data,
1795 p_result_code => p_result_code);
1796 p_pa_history_id := l_hist_data.pa_history_id;
1797 hr_utility.set_location( l_proc, 75);
1798 ghr_history_conv_rg.conv_to_element_entval_rg(
1799 p_history_data => l_hist_data,
1800 p_element_entval_data => l_element_entval_data );
1801 p_element_entry_data := l_element_entval_data;
1802 hr_utility.set_location( l_proc, 25);
1803 END get_date_eff_eleevl;
1804
1805 Procedure fetch_element_entry_value (
1806 p_element_entry_value_id in number default null,
1807 p_date_effective in date default null,
1808 p_altered_pa_request_id in number default null,
1809 p_noa_id_corrected in number default null,
1810 p_rowid in rowid default null,
1811 p_pa_history_id in number default null,
1812 p_element_entry_data out nocopy pay_element_entry_values_f%rowtype,
1813 p_result_code out nocopy varchar2 ) is
1814 l_hist_data ghr_pa_history%rowtype;
1815 l_result_code varchar2(100);
1816 l_proc varchar2(30) := 'fetch_element_entry_values';
1817 l_element_entval_data pay_element_entry_values_f%rowtype;
1818 cursor pay_e_entry_value_f_cursor is
1819 select *
1820 from pay_element_entry_values_f
1821 where element_entry_value_id = p_element_entry_value_id
1822 and p_date_effective between effective_start_date and effective_end_date;
1823 cursor pay_e_entry_val_f_rowid is
1824 select *
1825 from pay_element_entry_values_f
1826 where rowid = p_rowid;
1827
1828 Begin
1829 hr_utility.set_location('Entering:'|| l_proc, 5);
1830 p_result_code := null;
1831 if ( p_rowid is not null ) then
1832 /* This part of the procedure is used to fetch the exact row
1833 which will be the post-update record. So if the procedure was
1834 passed with p_row_id parameter it'll always return the
1835 post-update record.
1836 */
1837 hr_utility.set_location( l_proc, 10);
1838 open pay_e_entry_val_f_rowid;
1839 fetch pay_e_entry_val_f_rowid into p_element_entry_data;
1840 if ( pay_e_entry_val_f_rowid%NOTFOUND ) then
1841 p_result_code := c_not_found;
1842 end if;
1843 close pay_e_entry_val_f_rowid;
1844 hr_utility.set_location( l_proc, 15);
1845 elsif ( p_pa_history_id is null and
1846 p_altered_pa_request_id is null and
1847 p_noa_id_corrected is null ) then
1848 /* This part of the procedure will fetch the Pre-record values
1849 if called before update to database updates the record.
1850 if update to database has already applied the changes then it
1851 will return the post-update record. The session variable
1852 pre-update-record will hold the values which can be used for
1853 pre-record values
1854 */
1855 hr_utility.set_location( l_proc, 20);
1856 open pay_e_entry_value_f_cursor;
1857 fetch pay_e_entry_value_f_cursor into p_element_entry_data;
1858 if ( pay_e_entry_value_f_cursor%NOTFOUND ) then
1859 p_result_code := c_not_found;
1860 end if;
1861 close pay_e_entry_value_f_cursor;
1862 else
1863 hr_utility.set_location( l_proc, 25);
1864 Fetch_hist_data(
1865 p_table_name => ghr_history_api.g_eleevl_table,
1866 p_information1 => p_element_entry_value_id,
1867 p_date_effective => p_date_effective,
1868 p_altered_pa_request_id => p_altered_pa_request_id,
1869 p_noa_id_corrected => p_noa_id_corrected,
1870 p_pa_history_id => p_pa_history_id,
1871 p_hist_data => l_hist_data,
1872 p_result_code => l_result_code);
1873
1874 p_result_code := l_result_code;
1875 if nvl(l_result_code, 'found') <> c_not_found then
1876 ghr_history_conv_rg.conv_to_element_entval_rg(
1877 p_history_data => l_hist_data,
1878 p_element_entval_data => l_element_entval_data);
1879 p_element_entry_data := l_element_entval_data;
1880 end if;
1881 end if;
1882 hr_utility.set_location(' Leaving:'||l_proc, 45);
1883 exception
1884 when no_data_found then
1885 p_result_code := c_not_found;
1886 when OTHERS then
1887 raise;
1888 End fetch_element_entry_value;
1889
1890 Procedure fetch_element_entry_value
1891 (p_element_name in pay_element_types_f.element_name%type,
1892 p_input_value_name in pay_input_values_f.name%type,
1893 p_assignment_id in per_assignments_f.assignment_id%type,
1894 p_date_effective in date,
1895 p_screen_entry_value out nocopy pay_element_entry_values_f.screen_entry_value%type
1896 )
1897 is
1898 l_proc varchar2(72) := 'fetch_element_entry_value';
1899 l_session ghr_history_api.g_session_var_type;
1900 l_element_entry_value_id pay_element_entry_values.element_entry_value_id%type;
1901 l_result varchar2(15);
1902 l_element_entry_data pay_element_entry_values_f%rowtype;
1903 l_object_version_number pay_element_entries_f.object_version_number%type;
1904 l_input_value_id pay_element_entry_values_f.input_value_id%type;
1905 l_element_link_id pay_element_entries_f.element_link_id%type;
1906 l_element_entry_id pay_element_entries_f.element_entry_id%type;
1907 l_screen_entry_value pay_element_entry_values_f.screen_entry_value%type;
1908 l_element_type_id pay_element_types_f.element_type_id%type;
1909 l_processing_type pay_element_types.processing_type%type;
1910
1911 Cursor fetch_element_entry_value_id(p_element_name IN VARCHAR2
1912 ,p_bg_id IN NUMBER)
1913 is
1914 select eev.element_entry_value_id
1915 from pay_element_types_f elt,
1916 pay_input_values_f ipv,
1917 pay_element_entries_f ele,
1918 pay_element_entry_values_f eev
1919 where trunc(p_date_effective) between elt.effective_start_date
1920 and elt.effective_end_date
1921 and trunc(p_date_effective) between ipv.effective_start_date
1922 and ipv.effective_end_date
1923 and trunc(p_date_effective) between ele.effective_start_date
1924 and ele.effective_end_date
1925 and trunc(p_date_effective) between eev.effective_start_date
1926 and eev.effective_end_date
1927 and elt.element_type_id = ipv.element_type_id
1928 and upper(elt.element_name) = upper(p_element_name)
1929 and ipv.input_value_id = eev.input_value_id
1930 and ele.assignment_id = p_assignment_id
1931 and ele.element_entry_id + 0 = eev.element_entry_id
1932 and upper(ipv.name) = upper(p_input_value_name)
1933 -- and NVL(elt.business_group_id,0)=NVL(ipv.business_group_id,0)
1934 and (elt.business_group_id is NULL or elt.business_group_id=p_bg_id);
1935 --
1936 --added bg id check for business group id striping
1937 --
1938 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
1939 Select distinct business_group_id bg
1940 from per_assignments_f
1941 where assignment_id = p_assignment_id
1942 and p_eff_date between effective_start_date
1943 and effective_end_date;
1944
1945 --
1946 ll_bg_id NUMBER;
1947 ll_pay_basis VARCHAR2(80);
1948 ll_effective_date DATE;
1949 l_new_element_name VARCHAR2(80);
1950 --
1951
1952 begin
1953 hr_utility.set_location('Entering ' || l_proc,5);
1954 -- Initialization
1955 ghr_history_api.get_g_session_var(l_session);
1956 ll_effective_date := p_date_effective;
1957 -- Pick the business group id and also pay basis for later use
1958 For BG_rec in Cur_BG(p_assignment_id,ll_effective_date)
1959 Loop
1960 ll_bg_id:=BG_rec.bg;
1961 End Loop;
1962
1963 ----
1964 ---- The New Changes after 08/22 patch
1965 ---- For all elements in HR User old function will fetch the same name.
1966 ---- because of is_script will be FALSE
1967 ----
1968 ---- For all elements (except BSR) in Payroll user old function.
1969 ---- for BSR a new function which will fetch from assignmnet id.
1970 ----
1971
1972
1973 IF (p_element_name = 'Basic Salary Rate'
1974 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
1975 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- ' || l_proc, 5);
1976 l_new_element_name :=
1977 pqp_fedhr_uspay_int_utils.return_new_element_name(
1978 p_assignment_id => p_assignment_id,
1979 p_business_group_id => ll_bg_id,
1980 p_effective_date => ll_effective_date);
1981 ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
1982 or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
1983 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- ' || l_proc, 5);
1984 l_new_element_name :=
1985 pqp_fedhr_uspay_int_utils.return_new_element_name(
1986 p_fedhr_element_name => p_element_name,
1987 p_business_group_id => ll_bg_id,
1988 p_effective_date => ll_effective_date,
1989 p_pay_basis => NULL);
1990
1991 END IF;
1992
1993 --
1994 -- the p_element_name is replaced with l_new_element_name
1995 -- in further calls.
1996 --
1997 If l_session.noa_id_correct is not null then
1998
1999 -- History package call fetch_element_entry_value picks new element name
2000 -- again in its call so sending old element name.
2001 ghr_history_fetch.fetch_element_info_cor
2002 (p_element_name => p_element_name,
2003 p_input_value_name => p_input_value_name,
2004 p_assignment_id => p_assignment_id,
2005 p_effective_date => p_date_effective,
2006 p_element_link_id => l_element_link_id,
2007 p_input_value_id => l_input_value_id,
2008 p_element_entry_id => l_element_entry_id,
2009 p_value => l_screen_entry_value,
2010 p_object_version_number => l_object_version_number
2011 );
2012 p_screen_entry_value := l_screen_entry_value;
2013 Else
2014 For fetch_elv_id in fetch_element_entry_value_id(
2015 l_new_element_name,
2016 ll_bg_id)
2017 loop
2018 l_element_entry_value_id := fetch_elv_id.element_entry_value_id;
2019 If l_element_entry_value_id is not null then
2020 hr_utility.set_location(l_proc || 'inside cursor '|| to_char(l_element_entry_value_id) ,6);
2021 fetch_element_entry_value(
2022 p_element_entry_value_id => l_element_entry_value_id,
2023 p_date_effective => p_date_effective,
2024 p_altered_pa_request_id => l_session.altered_pa_request_id,
2025 p_noa_id_corrected => l_session.noa_id_correct,
2026 p_pa_history_id => l_session.pa_history_id,
2027 p_element_entry_data => l_element_entry_data,
2028 p_result_code => l_result
2029 );
2030 Else
2031 hr_utility.set_location(l_proc || 'before exit',7);
2032 exit;
2033 End if;
2034 If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2035 hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2036 p_screen_entry_value := l_element_entry_data.screen_entry_value;
2037 hr_utility.set_location(l_proc || 'Value' || l_element_entry_data.screen_entry_value,8);
2038 exit;
2039 End if;
2040 End loop;
2041 End if;
2042 End fetch_element_entry_value;
2043
2044 Procedure fetch_element_info_cor (
2045 p_element_name in pay_element_types_f.element_name%type
2046 ,p_input_value_name in pay_input_values_f.name%type
2047 ,p_assignment_id in pay_element_entries_f.assignment_id%type
2048 ,p_effective_date in date
2049 ,p_element_link_id out nocopy pay_element_links_f.element_link_id%type
2050 ,p_input_value_id out nocopy pay_input_values_f.input_value_id%type
2051 ,p_element_entry_id out nocopy pay_element_entries_f.element_entry_id%type
2052 ,p_value out nocopy pay_element_entry_values_f.screen_entry_value%type
2053 ,p_object_version_number out nocopy pay_element_entries_f.object_version_number%type ) is
2054 -- ,p_multiple_error_flag out nocopy varchar2
2055
2056
2057 l_proc varchar2(72) := 'fetch_element_info_cor';
2058 l_session ghr_history_api.g_session_var_type;
2059 l_element_entry_id pay_element_entries_f.element_entry_id%type;
2060 l_input_value_id pay_input_values_f.input_value_id%type;
2061 l_pa_request_id ghr_pa_requests.pa_request_id%type;
2062 l_processing_type pay_element_types_f.processing_type%type;
2063 l_element_type_id pay_element_types_f.element_type_id%type;
2064 l_element_entry_value_id pay_element_entry_values.element_entry_value_id%type;
2065 l_element_entry_data pay_element_entry_values_f%rowtype;
2066 l_result varchar2(30);
2067
2068 -- Modified Cursor for Payroll Changes
2069 --
2070 Cursor c_ele_type(p_element_name VARCHAR2,p_bg_id NUMBER) is
2071 select elt.element_type_id,
2072 elt.processing_type
2073 from pay_element_types_f elt
2074 where upper(elt.element_name) = upper(p_element_name)
2075 and p_effective_date
2076 between elt.effective_start_date and elt.effective_end_date
2077 and (elt.business_group_id is null or elt.business_group_id=p_bg_id);
2078
2079 -- Modified Cursor for Payroll Changes
2080 --
2081 cursor c_ele_info_cor (l_ele_type_id in NUMBER
2082 ,input_name in varchar2
2083 ,asg_id in number
2084 ,eff_date in date
2085 ,bg_id in number) is
2086 select ele.pa_request_id,
2087 ipv.input_value_id,
2088 eli.element_link_id,
2089 ele.element_entry_id
2090 from
2091 pay_input_values_f ipv,
2092 pay_element_links_f eli,
2093 ghr_element_entries_h_v ele
2094 where
2095 trunc(eff_date) between ipv.effective_start_date
2096 and ipv.effective_end_date
2097 and trunc(eff_date) between eli.effective_start_date
2098 and eli.effective_end_date
2099 and trunc(eff_date) between ele.effective_start_date
2100 and ele.effective_end_date
2101 and ipv.element_type_id = l_ele_type_id
2102 and ipv.element_type_id = eli.element_type_id
2103 and ele.assignment_id = asg_id
2104 and ele.element_link_id = eli.element_link_id
2105 and upper(ipv.name) = upper(input_name)
2106 and ele.nature_of_action_id = l_session.noa_id_correct
2107 and ele.pa_request_id in
2108 (select a.pa_request_id
2109 from ghr_pa_requests a
2110 connect by a.pa_request_id = prior a.altered_pa_request_id
2111 start with a.pa_request_id = l_session.altered_pa_request_id
2112 )
2113 -- and NVL(ipv.business_group_id,0)=NVL(eli.business_group_id,0)
2114 and (ipv.business_group_id is null or ipv.business_group_id=bg_id)
2115 order by 1 desc;
2116
2117 Cursor c_gev is
2118 select gev.screen_entry_value
2119 from ghr_element_entry_values_h_v gev
2120 where gev.element_entry_id = l_element_entry_id
2121 and gev.input_value_id = l_input_value_id
2122 and gev.pa_request_id = l_pa_request_id;
2123
2124
2125 cursor c_ele_ovn is
2126 select object_version_number
2127 from pay_element_entries_f
2128 where element_entry_id = l_element_entry_id
2129 and p_effective_date
2130 between effective_start_date and effective_end_date;
2131
2132 -- Modified Cursor for Payroll Changes
2133 --
2134 Cursor fetch_element_entry_value_id(p_element_type_id IN NUMBER,
2135 p_bg_id IN NUMBER) is
2136 select eev.element_entry_value_id
2137 from pay_input_values_f ipv,
2138 pay_element_entries_f ele,
2139 pay_element_entry_values_f eev
2140 where trunc(p_effective_date) between ipv.effective_start_date
2141 and ipv.effective_end_date
2142 and trunc(p_effective_date) between ele.effective_start_date
2143 and ele.effective_end_date
2144 and trunc(p_effective_date) between eev.effective_start_date
2145 and eev.effective_end_date
2146 and ipv.element_type_id=p_element_type_id
2147 and ipv.input_value_id = eev.input_value_id
2148 and ele.assignment_id = p_assignment_id
2149 and ele.element_entry_id + 0 = eev.element_entry_id
2150 and upper(ipv.name) = upper(p_input_value_name)
2151 and (ipv.business_group_id is NULL or ipv.business_group_id=p_bg_id);
2152 --
2153 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
2154 Select distinct business_group_id bg
2155 from per_assignments_f
2156 where assignment_id = p_assignment_id
2157 and p_eff_date between effective_start_date
2158 and effective_end_date;
2159
2160 l_fam_code VARCHAR2(80);
2161 --
2162 ll_bg_id NUMBER;
2163 ll_effective_date DATE;
2164 ll_pay_basis VARCHAR2(80);
2165 l_new_element_name VARCHAR2(80);
2166 --
2167 begin
2168 hr_utility.set_location('Entering:'||l_proc, 1);
2169 ghr_history_api.get_g_session_var(l_session);
2170 -- Pick pay basis from PAR
2171
2172 For BG_rec in Cur_BG(p_assignment_id,p_effective_date)
2173 Loop
2174 ll_bg_id:=BG_rec.bg;
2175 End Loop;
2176
2177 ----
2178 ---- The New Changes after 08/22 patch
2179 ---- For all elements in HR User old function will fetch the same name.
2180 ---- because of is_script will be FALSE
2181 ----
2182 ---- For all elements (except BSR) in Payroll user old function.
2183 ---- for BSR a new function which will fetch from assignmnet id.
2184 ----
2185
2186 IF (p_element_name = 'Basic Salary Rate'
2187 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
2188 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- '||l_proc, 1);
2189 l_new_element_name :=
2190 pqp_fedhr_uspay_int_utils.return_new_element_name(
2191 p_assignment_id => p_assignment_id,
2192 p_business_group_id => ll_bg_id,
2193 p_effective_date => ll_effective_date);
2194 ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
2195 or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
2196 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- '||l_proc, 1);
2197 l_new_element_name :=
2198 pqp_fedhr_uspay_int_utils.return_new_element_name(
2199 p_fedhr_element_name => p_element_name,
2200 p_business_group_id => ll_bg_id,
2201 p_effective_date => ll_effective_date,
2202 p_pay_basis => NULL);
2203
2204 END IF;
2205
2206 --
2207 --
2208 hr_utility.trace('NEW ELE NAME- ghhisfet.pkb is '||l_new_element_name);
2209 -- commenting this and using the same above
2210 -- ghr_history_api.get_g_session_var(l_session);
2211 l_input_value_id := Null;
2212 l_element_entry_id := Null;
2213 --
2214 hr_utility.set_location('Element - CORRECTION ' ,1);
2215 hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216 hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217 hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218 hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219 hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2220 -- hr_utility.set_location('Eff. Date ' || p_effective_date,5);
2221
2222 for ele_type in c_ele_type(l_new_element_name
2223 ,ll_bg_id)
2224 loop
2225 hr_utility.set_location(l_proc ,20);
2226 l_element_type_id := ele_type.element_type_id;
2227 l_processing_type := ele_type.processing_type;
2228 hr_utility.set_location('proc type ' || l_processing_type,2);
2229 end loop;
2230
2231 If nvl(l_processing_type,hr_api.g_varchar2) = 'R' then
2232 For fetch_elv_id in fetch_element_entry_value_id(
2233 l_element_type_id
2234 ,ll_bg_id)
2235 loop
2236 l_element_entry_value_id := fetch_elv_id.element_entry_value_id;
2237 If l_element_entry_value_id is not null then
2238 hr_utility.set_location(l_proc || 'inside cursor '|| to_char(l_element_entry_value_id) ,6);
2239
2240 fetch_element_entry_value(p_element_entry_value_id => l_element_entry_value_id,
2241 p_date_effective => p_effective_date,
2242 p_altered_pa_request_id => l_session.altered_pa_request_id,
2243 p_noa_id_corrected => l_session.noa_id_correct,
2244 p_pa_history_id => l_session.pa_history_id,
2245 p_element_entry_data => l_element_entry_data,
2246 p_result_code => l_result
2247 );
2248
2249 l_element_entry_id := l_element_entry_data.element_entry_id;
2250
2251 p_input_value_id := l_element_entry_data.input_value_id;
2252 p_element_entry_id := l_element_entry_id;
2253 p_value := l_element_entry_data.screen_entry_value;
2254
2255 Else
2256 hr_utility.set_location(l_proc || 'before exit',7);
2257 exit;
2258 End if;
2259 If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2260 hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2261 p_value := l_element_entry_data.screen_entry_value;
2262 hr_utility.set_location(l_proc || 'Value' || l_element_entry_data.screen_entry_value,8);
2263 exit;
2264 End if;
2265 End loop;
2266
2267 Elsif nvl(l_processing_type,hr_api.g_varchar2) = 'N' then
2268 hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2269 hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270 hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271 hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272 hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273 hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2274 hr_utility.set_location('Element type id ' || to_char(l_element_type_id),1);
2275
2276 /*
2277 If it is a correction action, then we have to read the
2278 element values from the history table to get the correct data
2279 This is definitely required for a non-recurring element,because
2280 the same element can repeat n number of times for the same pay period
2281 */
2282
2283 for ele_info_cor in c_ele_info_cor (l_element_type_id
2284 ,p_input_value_name
2285 ,p_assignment_id
2286 ,p_effective_date
2287 ,ll_bg_id) loop
2288 hr_utility.set_location('Element In.Val. Id ' || ele_info_cor.input_value_id,3);
2289 hr_utility.set_location('Element entry Id ' || ele_info_cor.element_entry_id,3);
2290 l_input_value_id := ele_info_cor.input_value_id;
2291 p_element_link_id := ele_info_cor.element_link_id;
2292 l_element_entry_id := ele_info_cor.element_entry_id;
2293 l_pa_request_id := ele_info_cor.pa_request_id;
2294 exit;
2295 end loop;
2296
2297 p_element_entry_id := l_element_entry_id;
2298 p_input_value_id := l_input_value_id;
2299
2300 Else
2301 -- unknown processing type
2302 --raise error;
2303 Null;
2304 End if;
2305
2306 if l_element_entry_id is not null then
2307 for ele_ovn in c_ele_ovn loop
2308 hr_utility.set_location(l_proc || 'retrieve_element_cor ',1);
2309 p_object_version_number := ele_ovn.object_version_number;
2310 end loop;
2311 if l_input_value_id is not null then -- l_input_value_id would anyway have a value only for Non Rec. Elements.
2312 hr_utility.set_location(l_proc || 'get eev',2);
2313 for screen_value in c_gev loop
2314 p_value := screen_value.screen_entry_value;
2315 hr_utility.set_location('Scr. value is ' || screen_value.screen_entry_value,1);
2316 end loop;
2317 end if;
2318 end if;
2319
2320 End fetch_element_info_cor;
2321
2322
2323 Procedure fetch_address (
2324 p_address_id in number default null,
2325 p_date_effective in date default null,
2326 p_altered_pa_request_id in number default null,
2327 p_noa_id_corrected in number default null,
2328 p_rowid in rowid default null,
2329 p_pa_history_id in number default null,
2330 p_address_data out nocopy per_addresses%rowtype,
2331 p_result_code out nocopy varchar2 ) is
2332 l_result_code varchar2(100);
2333 l_hist_data ghr_pa_history%rowtype;
2334 l_proc varchar2(30):='fetch_address';
2335 l_address_data per_addresses%rowtype;
2336
2337 cursor per_address_rowid_cursor is
2338 select *
2339 from per_addresses
2340 where rowid = p_rowid;
2341
2342 begin
2343 hr_utility.set_location('Entering:'|| l_proc, 5);
2344 p_result_code := null;
2345 if ( p_rowid is not null ) then
2346 /* This part of the procedure is used to fetch the exact row
2347 which will be the post-update record. So if the procedure was
2348 passed with p_row_id parameter it'll always return the
2349 post-update record.
2350 */
2351 hr_utility.set_location( l_proc, 10);
2352 open per_address_rowid_cursor;
2353 fetch per_address_rowid_cursor into p_address_data;
2354 if ( per_address_rowid_cursor%NOTFOUND ) then
2355 p_result_code := c_not_found;
2356 end if;
2357 close per_address_rowid_cursor;
2358 hr_utility.set_location( l_proc, 15);
2359 else
2360
2361 Fetch_hist_data(
2362 p_table_name => ghr_history_api.g_addres_table,
2363 p_information1 => p_address_id,
2364 p_date_effective => p_date_effective,
2365 p_altered_pa_request_id => p_altered_pa_request_id,
2366 p_noa_id_corrected => p_noa_id_corrected,
2367 p_pa_history_id => p_pa_history_id,
2368 p_hist_data => l_hist_data,
2369 p_result_code => l_result_code);
2370
2371 p_result_code := l_result_code;
2372 if nvl(l_result_code, 'found') <> c_not_found then
2373 ghr_history_conv_rg.conv_to_addresses_rg(
2374 p_history_data => l_hist_data,
2375 p_addresses_data => l_address_data);
2376 p_address_data := l_address_data;
2377 end if;
2378 end if;
2379 hr_utility.set_location(' Leaving:'||l_proc, 45);
2380 exception
2381 when no_data_found then
2382 p_result_code := c_not_found;
2383 when OTHERS then
2384 raise;
2385 End fetch_address ;
2386 --
2387 -- Procedure fetch_person_analyses fetches the last record from per_person_analyses or ghr_pa_history
2388 -- which was created between effective start date and effective end date
2389 --
2390 Procedure fetch_person_analyses (
2391 p_person_analysis_id in number default null,
2392 p_date_effective in date default null,
2393 p_altered_pa_request_id in number default null,
2394 p_noa_id_corrected in number default null,
2395 p_rowid in rowid default null,
2396 p_pa_history_id in number default null,
2397 p_peranalyses_data out nocopy per_person_analyses%rowtype,
2398 p_result_code out nocopy varchar2 ) is
2399 l_peranalyses_data per_person_analyses%rowtype;
2400 l_proc varchar2(30) := 'fetch_person_analyses';
2401 l_hist_data ghr_pa_history%rowtype;
2402 l_result_code varchar2(100);
2403 cursor per_peranalyses_rowid_cursor is
2404 select *
2405 from per_person_analyses
2406 where rowid = p_rowid;
2407 Begin
2408 hr_utility.set_location('Entering:'|| l_proc, 5);
2409 p_result_code := null;
2410 if ( p_rowid is not null ) then
2411 /* This part of the procedure is used to fetch the exact row
2412 which will be the post-update record. So if the procedure was
2413 passed with p_row_id parameter it'll always return the
2414 post-update record.
2415 */
2416 hr_utility.set_location( l_proc, 10);
2417 open per_peranalyses_rowid_cursor;
2418 fetch per_peranalyses_rowid_cursor into p_peranalyses_data;
2419 if ( per_peranalyses_rowid_cursor%NOTFOUND ) then
2420 p_result_code := c_not_found;
2421 end if;
2422 close per_peranalyses_rowid_cursor;
2423 hr_utility.set_location( l_proc, 15);
2424 else
2425
2426 Fetch_hist_data(
2427 p_table_name => ghr_history_api.g_perana_table,
2428 p_information1 => p_person_analysis_id,
2429 p_date_effective => p_date_effective,
2430 p_altered_pa_request_id => p_altered_pa_request_id,
2431 p_noa_id_corrected => p_noa_id_corrected,
2432 p_pa_history_id => p_pa_history_id,
2433 p_hist_data => l_hist_data,
2434 p_result_code => l_result_code);
2435
2436 p_result_code := l_result_code;
2437 if nvl(l_result_code, 'found') <> c_not_found then
2438 ghr_history_conv_rg.conv_to_peranalyses_rg(
2439 p_history_data => l_hist_data,
2440 p_peranalyses_data => l_peranalyses_data);
2441 p_peranalyses_data := l_peranalyses_data;
2442 end if;
2443
2444 end if;
2445 hr_utility.set_location(' Leaving:'||l_proc, 45);
2446 exception
2447 when no_data_found then
2448 p_result_code := c_not_found;
2449 when OTHERS then
2450 raise;
2451 End fetch_person_analyses;
2452
2453 procedure fetch_positionei (
2454 p_position_extra_info_id in out nocopy number
2455 ,p_date_effective in out nocopy date
2456 ,p_position_id out nocopy number
2457 ,p_information_type out nocopy varchar2
2458 ,p_request_id out nocopy number
2459 ,p_program_application_id out nocopy number
2460 ,p_program_id out nocopy number
2461 ,p_program_update_date out nocopy date
2462 ,p_poei_attribute_category out nocopy varchar2
2463 ,p_poei_attribute1 out nocopy varchar2
2464 ,p_poei_attribute2 out nocopy varchar2
2465 ,p_poei_attribute3 out nocopy varchar2
2466 ,p_poei_attribute4 out nocopy varchar2
2467 ,p_poei_attribute5 out nocopy varchar2
2468 ,p_poei_attribute6 out nocopy varchar2
2469 ,p_poei_attribute7 out nocopy varchar2
2470 ,p_poei_attribute8 out nocopy varchar2
2471 ,p_poei_attribute9 out nocopy varchar2
2472 ,p_poei_attribute10 out nocopy varchar2
2473 ,p_poei_attribute11 out nocopy varchar2
2474 ,p_poei_attribute12 out nocopy varchar2
2475 ,p_poei_attribute13 out nocopy varchar2
2476 ,p_poei_attribute14 out nocopy varchar2
2477 ,p_poei_attribute15 out nocopy varchar2
2478 ,p_poei_attribute16 out nocopy varchar2
2479 ,p_poei_attribute17 out nocopy varchar2
2480 ,p_poei_attribute18 out nocopy varchar2
2481 ,p_poei_attribute19 out nocopy varchar2
2482 ,p_poei_attribute20 out nocopy varchar2
2483 ,p_poei_information_category out nocopy varchar2
2484 ,p_poei_information1 out nocopy varchar2
2485 ,p_poei_information2 out nocopy varchar2
2486 ,p_poei_information3 out nocopy varchar2
2487 ,p_poei_information4 out nocopy varchar2
2488 ,p_poei_information5 out nocopy varchar2
2489 ,p_poei_information6 out nocopy varchar2
2490 ,p_poei_information7 out nocopy varchar2
2491 ,p_poei_information8 out nocopy varchar2
2492 ,p_poei_information9 out nocopy varchar2
2493 ,p_poei_information10 out nocopy varchar2
2494 ,p_poei_information11 out nocopy varchar2
2495 ,p_poei_information12 out nocopy varchar2
2496 ,p_poei_information13 out nocopy varchar2
2497 ,p_poei_information14 out nocopy varchar2
2498 ,p_poei_information15 out nocopy varchar2
2499 ,p_poei_information16 out nocopy varchar2
2500 ,p_poei_information17 out nocopy varchar2
2501 ,p_poei_information18 out nocopy varchar2
2502 ,p_poei_information19 out nocopy varchar2
2503 ,p_poei_information20 out nocopy varchar2
2504 ,p_poei_information21 out nocopy varchar2
2505 ,p_poei_information22 out nocopy varchar2
2506 ,p_poei_information23 out nocopy varchar2
2507 ,p_poei_information24 out nocopy varchar2
2508 ,p_poei_information25 out nocopy varchar2
2509 ,p_poei_information26 out nocopy varchar2
2510 ,p_poei_information27 out nocopy varchar2
2511 ,p_poei_information28 out nocopy varchar2
2512 ,p_poei_information29 out nocopy varchar2
2513 ,p_poei_information30 out nocopy varchar2
2514 ,p_object_version_number out nocopy number
2515 ,p_last_update_date out nocopy date
2516 ,p_last_updated_by out nocopy number
2517 ,p_last_update_login out nocopy number
2518 ,p_created_by out nocopy number
2519 ,p_creation_date out nocopy date
2520 ,p_result_code out nocopy varchar2
2521 )
2522 Is
2523 r_poi per_position_extra_info%rowtype;
2524 l_proc varchar2(30):='fetch_positionei (1)';
2525 l_result_code varchar2(30);
2526 Begin
2527 hr_utility.set_location('Entering :' || l_proc, 10);
2528 ghr_history_fetch.fetch_positionei(
2529 p_position_extra_info_id => p_position_extra_info_id
2530 , p_date_effective => p_date_effective
2531 , p_posei_data => r_poi
2532 , p_result_code => p_result_code);
2533 hr_utility.set_location(l_proc, 20);
2534 p_position_extra_info_id := r_poi.position_extra_info_id;
2535 p_position_id := r_poi.position_id;
2536 p_information_type := r_poi.information_type;
2537 p_request_id := r_poi.request_id;
2538 p_program_application_id := r_poi.program_application_id;
2539 p_program_id := r_poi.program_id;
2540 p_program_update_date := r_poi.program_update_date;
2541 p_poei_attribute_category := r_poi.poei_attribute_category;
2542 p_poei_attribute1 := r_poi.poei_attribute1;
2543 p_poei_attribute2 := r_poi.poei_attribute2;
2544 p_poei_attribute3 := r_poi.poei_attribute3;
2545 p_poei_attribute4 := r_poi.poei_attribute4;
2546 p_poei_attribute5 := r_poi.poei_attribute5;
2547 p_poei_attribute6 := r_poi.poei_attribute6;
2548 p_poei_attribute7 := r_poi.poei_attribute7;
2549 p_poei_attribute8 := r_poi.poei_attribute8;
2550 p_poei_attribute9 := r_poi.poei_attribute9;
2551 p_poei_attribute10 := r_poi.poei_attribute10;
2552 p_poei_attribute11 := r_poi.poei_attribute11;
2553 p_poei_attribute12 := r_poi.poei_attribute12;
2554 p_poei_attribute13 := r_poi.poei_attribute13;
2555 p_poei_attribute14 := r_poi.poei_attribute14;
2556 p_poei_attribute15 := r_poi.poei_attribute15;
2557 p_poei_attribute16 := r_poi.poei_attribute16;
2558 p_poei_attribute17 := r_poi.poei_attribute17;
2559 p_poei_attribute18 := r_poi.poei_attribute18;
2560 p_poei_attribute19 := r_poi.poei_attribute19;
2561 p_poei_attribute20 := r_poi.poei_attribute20;
2562 p_poei_information_category := r_poi.poei_information_category;
2563 p_poei_information1 := r_poi.poei_information1;
2564 p_poei_information2 := r_poi.poei_information2;
2565 p_poei_information3 := r_poi.poei_information3;
2566 p_poei_information4 := r_poi.poei_information4;
2567 p_poei_information5 := r_poi.poei_information5;
2568 p_poei_information6 := r_poi.poei_information6;
2569 p_poei_information7 := r_poi.poei_information7;
2570 p_poei_information8 := r_poi.poei_information8;
2571 p_poei_information9 := r_poi.poei_information9;
2572 p_poei_information10 := r_poi.poei_information10;
2573 p_poei_information11 := r_poi.poei_information11;
2574 p_poei_information12 := r_poi.poei_information12;
2575 p_poei_information13 := r_poi.poei_information13;
2576 p_poei_information14 := r_poi.poei_information14;
2577 p_poei_information15 := r_poi.poei_information15;
2578 p_poei_information16 := r_poi.poei_information16;
2579 p_poei_information17 := r_poi.poei_information17;
2580 p_poei_information18 := r_poi.poei_information18;
2581 p_poei_information19 := r_poi.poei_information19;
2582 p_poei_information20 := r_poi.poei_information20;
2583 p_poei_information21 := r_poi.poei_information21;
2584 p_poei_information22 := r_poi.poei_information22;
2585 p_poei_information23 := r_poi.poei_information23;
2586 p_poei_information24 := r_poi.poei_information24;
2587 p_poei_information25 := r_poi.poei_information25;
2588 p_poei_information26 := r_poi.poei_information26;
2589 p_poei_information27 := r_poi.poei_information27;
2590 p_poei_information28 := r_poi.poei_information28;
2591 p_poei_information29 := r_poi.poei_information29;
2592 p_poei_information30 := r_poi.poei_information30;
2593 p_object_version_number := r_poi.object_version_number;
2594 p_last_update_date := r_poi.last_update_date;
2595 p_last_updated_by := r_poi.last_updated_by;
2596 p_last_update_login := r_poi.last_update_login;
2597 p_created_by := r_poi.created_by;
2598 p_creation_date := r_poi.creation_date;
2599 hr_utility.set_location('Leaving :' || l_proc, 100);
2600 end fetch_positionei;
2601
2602 --
2603 -- Procedure fetches the last record from per_positions or
2604 -- ghr_positions_h_v
2605 --
2606 Procedure fetch_position (
2607 p_position_id in number default null,
2608 p_date_effective in date default null,
2609 p_altered_pa_request_id in number default null,
2610 p_noa_id_corrected in number default null,
2611 p_rowid in rowid default null,
2612 p_pa_history_id in number default null,
2613 p_get_ovn_flag in varchar2 default 'N',
2614 p_position_data out nocopy hr_all_positions_f%rowtype,
2615 p_result_code out nocopy varchar2 ) is
2616
2617 cursor per_posn_f_cursor is
2618 select *
2619 from hr_all_positions_f
2620 where position_id = p_position_id
2621 and p_date_effective between effective_start_date and
2622 effective_end_date;
2623
2624 cursor per_posn_f_rowid_cursor is
2625 select *
2626 from hr_all_positions_f
2627 where rowid = p_rowid;
2628
2629 cursor c_get_ovn is
2630 select object_version_number
2631 from hr_all_positions
2632 where position_id = p_position_id
2633 and p_date_effective between effective_start_date and
2634 effective_end_date;
2635
2636 cursor c_get_end_date is
2637 select date_end
2638 from hr_all_positions_f
2639 where position_id = p_position_id
2640 and p_date_effective between effective_start_date and
2641 effective_end_date;
2642
2643 l_result_code varchar2(30);
2644 l_position_data hr_all_positions_f%rowtype;
2645 l_hist_data ghr_pa_history%rowtype;
2646 l_proc varchar2(30):='fetch_positionei';
2647
2648 Begin
2649 hr_utility.set_location('Entering:'|| l_proc, 5);
2650 p_result_code := null;
2651 if ( p_rowid is not null ) then
2652 hr_utility.set_location( l_proc, 10);
2653 open per_posn_f_rowid_cursor;
2654 fetch per_posn_f_rowid_cursor into p_position_data;
2655 if ( per_posn_f_rowid_cursor%NOTFOUND ) then
2656 p_result_code := c_not_found;
2657 end if;
2658 close per_posn_f_rowid_cursor;
2659 hr_utility.set_location( l_proc, 15);
2660 else
2661 hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
2662 hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
2663 hr_utility.set_location( l_proc || 'position_id: ' || p_position_id, 215);
2664
2665 Fetch_hist_data(
2666 p_table_name => ghr_history_api.g_posn_table,
2667 p_information1 => p_position_id,
2668 p_date_effective => p_date_effective,
2669 p_altered_pa_request_id => p_altered_pa_request_id,
2670 p_noa_id_corrected => p_noa_id_corrected,
2671 p_pa_history_id => p_pa_history_id,
2672 p_hist_data => l_hist_data,
2673 p_result_code => l_result_code);
2674
2675 p_result_code := l_result_code;
2676 if nvl(l_result_code, 'found') <> c_not_found then
2677 ghr_history_conv_rg.conv_to_position_rg(
2678 p_history_data => l_hist_data,
2679 p_position_data => l_position_data);
2680 p_position_data := l_position_data;
2681 if upper(p_get_ovn_flag) = 'Y' then
2682 for ovn in c_get_ovn loop
2683 p_position_data.object_version_number := ovn.object_version_number;
2684 exit;
2685 end loop;
2686 end if;
2687 for ghr_pos in c_get_end_date loop
2688 p_position_data.date_end := ghr_pos.date_end;
2689 exit;
2690 end loop;
2691 end if;
2692 end if;
2693 hr_utility.set_location ('Leaving : ' || l_proc, 100);
2694
2695 End fetch_position;
2696
2697 procedure fetch_peopleei (
2698 p_person_extra_info_id in out nocopy number
2699 ,p_date_effective in out nocopy date
2700 ,p_person_id out nocopy number
2701 ,p_information_type out nocopy varchar2
2702 ,p_request_id out nocopy number
2703 ,p_program_application_id out nocopy number
2704 ,p_program_id out nocopy number
2705 ,p_program_update_date out nocopy date
2706 ,p_pei_attribute_category out nocopy varchar2
2707 ,p_pei_attribute1 out nocopy varchar2
2708 ,p_pei_attribute2 out nocopy varchar2
2709 ,p_pei_attribute3 out nocopy varchar2
2710 ,p_pei_attribute4 out nocopy varchar2
2711 ,p_pei_attribute5 out nocopy varchar2
2712 ,p_pei_attribute6 out nocopy varchar2
2713 ,p_pei_attribute7 out nocopy varchar2
2714 ,p_pei_attribute8 out nocopy varchar2
2715 ,p_pei_attribute9 out nocopy varchar2
2716 ,p_pei_attribute10 out nocopy varchar2
2717 ,p_pei_attribute11 out nocopy varchar2
2718 ,p_pei_attribute12 out nocopy varchar2
2719 ,p_pei_attribute13 out nocopy varchar2
2720 ,p_pei_attribute14 out nocopy varchar2
2721 ,p_pei_attribute15 out nocopy varchar2
2722 ,p_pei_attribute16 out nocopy varchar2
2723 ,p_pei_attribute17 out nocopy varchar2
2724 ,p_pei_attribute18 out nocopy varchar2
2725 ,p_pei_attribute19 out nocopy varchar2
2726 ,p_pei_attribute20 out nocopy varchar2
2727 ,p_pei_information_category out nocopy varchar2
2728 ,p_pei_information1 out nocopy varchar2
2729 ,p_pei_information2 out nocopy varchar2
2730 ,p_pei_information3 out nocopy varchar2
2731 ,p_pei_information4 out nocopy varchar2
2732 ,p_pei_information5 out nocopy varchar2
2733 ,p_pei_information6 out nocopy varchar2
2734 ,p_pei_information7 out nocopy varchar2
2735 ,p_pei_information8 out nocopy varchar2
2736 ,p_pei_information9 out nocopy varchar2
2737 ,p_pei_information10 out nocopy varchar2
2738 ,p_pei_information11 out nocopy varchar2
2739 ,p_pei_information12 out nocopy varchar2
2740 ,p_pei_information13 out nocopy varchar2
2741 ,p_pei_information14 out nocopy varchar2
2742 ,p_pei_information15 out nocopy varchar2
2743 ,p_pei_information16 out nocopy varchar2
2744 ,p_pei_information17 out nocopy varchar2
2745 ,p_pei_information18 out nocopy varchar2
2746 ,p_pei_information19 out nocopy varchar2
2747 ,p_pei_information20 out nocopy varchar2
2748 ,p_pei_information21 out nocopy varchar2
2749 ,p_pei_information22 out nocopy varchar2
2750 ,p_pei_information23 out nocopy varchar2
2751 ,p_pei_information24 out nocopy varchar2
2752 ,p_pei_information25 out nocopy varchar2
2753 ,p_pei_information26 out nocopy varchar2
2754 ,p_pei_information27 out nocopy varchar2
2755 ,p_pei_information28 out nocopy varchar2
2756 ,p_pei_information29 out nocopy varchar2
2757 ,p_pei_information30 out nocopy varchar2
2758 ,p_object_version_number out nocopy number
2759 ,p_last_update_date out nocopy date
2760 ,p_last_updated_by out nocopy number
2761 ,p_last_update_login out nocopy number
2762 ,p_created_by out nocopy number
2763 ,p_creation_date out nocopy date
2764 ,p_result_code out nocopy varchar2
2765 )
2766 Is
2767 r_pei per_people_extra_info%rowtype;
2768 l_proc varchar2(30):='fetch_peopleei (1)';
2769 l_result_code varchar2(30);
2770 Begin
2771 hr_utility.set_location(' Entering : ' || l_proc, 10);
2772 ghr_history_fetch.fetch_peopleei(
2773 p_person_extra_info_id => p_person_extra_info_id
2774 , p_date_effective => p_date_effective
2775 , p_peopleei_data => r_pei
2776 , p_result_code => p_result_code);
2777
2778 hr_utility.set_location( l_proc, 20);
2779 p_person_extra_info_id := r_pei.person_extra_info_id;
2780 p_person_id := r_pei.person_id;
2781 p_information_type := r_pei.information_type;
2782 p_request_id := r_pei.request_id;
2783 p_program_application_id := r_pei.program_application_id;
2784 p_program_id := r_pei.program_id;
2785 p_program_update_date := r_pei.program_update_date;
2786 p_pei_attribute_category := r_pei.pei_attribute_category;
2787 p_pei_attribute1 := r_pei.pei_attribute1;
2788 p_pei_attribute2 := r_pei.pei_attribute2;
2789 p_pei_attribute3 := r_pei.pei_attribute3;
2790 p_pei_attribute4 := r_pei.pei_attribute4;
2791 p_pei_attribute5 := r_pei.pei_attribute5;
2792 p_pei_attribute6 := r_pei.pei_attribute6;
2793 p_pei_attribute7 := r_pei.pei_attribute7;
2794 p_pei_attribute8 := r_pei.pei_attribute8;
2795 p_pei_attribute9 := r_pei.pei_attribute9;
2796 p_pei_attribute10 := r_pei.pei_attribute10;
2797 p_pei_attribute11 := r_pei.pei_attribute11;
2798 p_pei_attribute12 := r_pei.pei_attribute12;
2799 p_pei_attribute13 := r_pei.pei_attribute13;
2800 p_pei_attribute14 := r_pei.pei_attribute14;
2801 p_pei_attribute15 := r_pei.pei_attribute15;
2802 p_pei_attribute16 := r_pei.pei_attribute16;
2803 p_pei_attribute17 := r_pei.pei_attribute17;
2804 p_pei_attribute18 := r_pei.pei_attribute18;
2805 p_pei_attribute19 := r_pei.pei_attribute19;
2806 p_pei_attribute20 := r_pei.pei_attribute20;
2807 p_pei_information_category := r_pei.pei_information_category;
2808 p_pei_information1 := r_pei.pei_information1;
2809 p_pei_information2 := r_pei.pei_information2;
2810 p_pei_information3 := r_pei.pei_information3;
2811 p_pei_information4 := r_pei.pei_information4;
2812 p_pei_information5 := r_pei.pei_information5;
2813 p_pei_information6 := r_pei.pei_information6;
2814 p_pei_information7 := r_pei.pei_information7;
2815 p_pei_information8 := r_pei.pei_information8;
2816 p_pei_information9 := r_pei.pei_information9;
2817 p_pei_information10 := r_pei.pei_information10;
2818 p_pei_information11 := r_pei.pei_information11;
2819 p_pei_information12 := r_pei.pei_information12;
2820 p_pei_information13 := r_pei.pei_information13;
2821 p_pei_information14 := r_pei.pei_information14;
2822 p_pei_information15 := r_pei.pei_information15;
2823 p_pei_information16 := r_pei.pei_information16;
2824 p_pei_information17 := r_pei.pei_information17;
2825 p_pei_information18 := r_pei.pei_information18;
2826 p_pei_information19 := r_pei.pei_information19;
2827 p_pei_information20 := r_pei.pei_information20;
2828 p_pei_information21 := r_pei.pei_information21;
2829 p_pei_information22 := r_pei.pei_information22;
2830 p_pei_information23 := r_pei.pei_information23;
2831 p_pei_information24 := r_pei.pei_information24;
2832 p_pei_information25 := r_pei.pei_information25;
2833 p_pei_information26 := r_pei.pei_information26;
2834 p_pei_information27 := r_pei.pei_information27;
2835 p_pei_information28 := r_pei.pei_information28;
2836 p_pei_information29 := r_pei.pei_information29;
2837 p_pei_information30 := r_pei.pei_information30;
2838 p_object_version_number := r_pei.object_version_number;
2839 p_last_update_date := r_pei.last_update_date;
2840 p_last_updated_by := r_pei.last_updated_by;
2841 p_last_update_login := r_pei.last_update_login;
2842 p_created_by := r_pei.created_by;
2843 p_creation_date := r_pei.creation_date;
2844 hr_utility.set_location(' Leaving : ' || l_proc, 100);
2845 end fetch_peopleei ;
2846
2847 procedure fetch_asgei (
2848 p_assignment_extra_info_id in out nocopy number
2849 ,p_date_effective in out nocopy date
2850 ,p_assignment_id out nocopy number
2851 ,p_information_type out nocopy varchar2
2852 ,p_request_id out nocopy number
2853 ,p_program_application_id out nocopy number
2854 ,p_program_id out nocopy number
2855 ,p_program_update_date out nocopy date
2856 ,p_aei_attribute_category out nocopy varchar2
2857 ,p_aei_attribute1 out nocopy varchar2
2858 ,p_aei_attribute2 out nocopy varchar2
2859 ,p_aei_attribute3 out nocopy varchar2
2860 ,p_aei_attribute4 out nocopy varchar2
2861 ,p_aei_attribute5 out nocopy varchar2
2862 ,p_aei_attribute6 out nocopy varchar2
2863 ,p_aei_attribute7 out nocopy varchar2
2864 ,p_aei_attribute8 out nocopy varchar2
2865 ,p_aei_attribute9 out nocopy varchar2
2866 ,p_aei_attribute10 out nocopy varchar2
2867 ,p_aei_attribute11 out nocopy varchar2
2868 ,p_aei_attribute12 out nocopy varchar2
2869 ,p_aei_attribute13 out nocopy varchar2
2870 ,p_aei_attribute14 out nocopy varchar2
2871 ,p_aei_attribute15 out nocopy varchar2
2872 ,p_aei_attribute16 out nocopy varchar2
2873 ,p_aei_attribute17 out nocopy varchar2
2874 ,p_aei_attribute18 out nocopy varchar2
2875 ,p_aei_attribute19 out nocopy varchar2
2876 ,p_aei_attribute20 out nocopy varchar2
2877 ,p_aei_information_category out nocopy varchar2
2878 ,p_aei_information1 out nocopy varchar2
2879 ,p_aei_information2 out nocopy varchar2
2880 ,p_aei_information3 out nocopy varchar2
2881 ,p_aei_information4 out nocopy varchar2
2882 ,p_aei_information5 out nocopy varchar2
2883 ,p_aei_information6 out nocopy varchar2
2884 ,p_aei_information7 out nocopy varchar2
2885 ,p_aei_information8 out nocopy varchar2
2886 ,p_aei_information9 out nocopy varchar2
2887 ,p_aei_information10 out nocopy varchar2
2888 ,p_aei_information11 out nocopy varchar2
2889 ,p_aei_information12 out nocopy varchar2
2890 ,p_aei_information13 out nocopy varchar2
2891 ,p_aei_information14 out nocopy varchar2
2892 ,p_aei_information15 out nocopy varchar2
2893 ,p_aei_information16 out nocopy varchar2
2894 ,p_aei_information17 out nocopy varchar2
2895 ,p_aei_information18 out nocopy varchar2
2896 ,p_aei_information19 out nocopy varchar2
2897 ,p_aei_information20 out nocopy varchar2
2898 ,p_aei_information21 out nocopy varchar2
2899 ,p_aei_information22 out nocopy varchar2
2900 ,p_aei_information23 out nocopy varchar2
2901 ,p_aei_information24 out nocopy varchar2
2902 ,p_aei_information25 out nocopy varchar2
2903 ,p_aei_information26 out nocopy varchar2
2904 ,p_aei_information27 out nocopy varchar2
2905 ,p_aei_information28 out nocopy varchar2
2906 ,p_aei_information29 out nocopy varchar2
2907 ,p_aei_information30 out nocopy varchar2
2908 ,p_object_version_number out nocopy number
2909 ,p_last_update_date out nocopy date
2910 ,p_last_updated_by out nocopy number
2911 ,p_last_update_login out nocopy number
2912 ,p_created_by out nocopy number
2913 ,p_creation_date out nocopy date
2914 ,p_result_code out nocopy varchar2
2915 )
2916 Is
2917 r_aei per_assignment_extra_info%rowtype;
2918 l_proc varchar2(30):='fetch_asgei (1)';
2919 l_result_code varchar2(30);
2920 Begin
2921 hr_utility.set_location(' Entering : ' || l_proc, 10);
2922 ghr_history_fetch.fetch_asgei(
2923 p_assignment_extra_info_id => p_assignment_extra_info_id
2924 , p_date_effective => p_date_effective
2925 , p_asgei_data => r_aei
2926 , p_result_code => p_result_code);
2927
2928 hr_utility.set_location( l_proc, 20);
2929 p_assignment_extra_info_id := r_aei.assignment_extra_info_id;
2930 p_assignment_id := r_aei.assignment_id;
2931 p_information_type := r_aei.information_type;
2932 p_request_id := r_aei.request_id;
2933 p_program_application_id := r_aei.program_application_id;
2934 p_program_id := r_aei.program_id;
2935 p_program_update_date := r_aei.program_update_date;
2936 p_aei_attribute_category := r_aei.aei_attribute_category;
2937 p_aei_attribute1 := r_aei.aei_attribute1;
2938 p_aei_attribute2 := r_aei.aei_attribute2;
2939 p_aei_attribute3 := r_aei.aei_attribute3;
2940 p_aei_attribute4 := r_aei.aei_attribute4;
2941 p_aei_attribute5 := r_aei.aei_attribute5;
2942 p_aei_attribute6 := r_aei.aei_attribute6;
2943 p_aei_attribute7 := r_aei.aei_attribute7;
2944 p_aei_attribute8 := r_aei.aei_attribute8;
2945 p_aei_attribute9 := r_aei.aei_attribute9;
2946 p_aei_attribute10 := r_aei.aei_attribute10;
2947 p_aei_attribute11 := r_aei.aei_attribute11;
2948 p_aei_attribute12 := r_aei.aei_attribute12;
2949 p_aei_attribute13 := r_aei.aei_attribute13;
2950 p_aei_attribute14 := r_aei.aei_attribute14;
2951 p_aei_attribute15 := r_aei.aei_attribute15;
2952 p_aei_attribute16 := r_aei.aei_attribute16;
2953 p_aei_attribute17 := r_aei.aei_attribute17;
2954 p_aei_attribute18 := r_aei.aei_attribute18;
2955 p_aei_attribute19 := r_aei.aei_attribute19;
2956 p_aei_attribute20 := r_aei.aei_attribute20;
2957 p_aei_information_category := r_aei.aei_information_category;
2958 p_aei_information1 := r_aei.aei_information1;
2959 p_aei_information2 := r_aei.aei_information2;
2960 p_aei_information3 := r_aei.aei_information3;
2961 p_aei_information4 := r_aei.aei_information4;
2962 p_aei_information5 := r_aei.aei_information5;
2963 p_aei_information6 := r_aei.aei_information6;
2964 p_aei_information7 := r_aei.aei_information7;
2965 p_aei_information8 := r_aei.aei_information8;
2966 p_aei_information9 := r_aei.aei_information9;
2967 p_aei_information10 := r_aei.aei_information10;
2968 p_aei_information11 := r_aei.aei_information11;
2969 p_aei_information12 := r_aei.aei_information12;
2970 p_aei_information13 := r_aei.aei_information13;
2971 p_aei_information14 := r_aei.aei_information14;
2972 p_aei_information15 := r_aei.aei_information15;
2973 p_aei_information16 := r_aei.aei_information16;
2974 p_aei_information17 := r_aei.aei_information17;
2975 p_aei_information18 := r_aei.aei_information18;
2976 p_aei_information19 := r_aei.aei_information19;
2977 p_aei_information20 := r_aei.aei_information20;
2978 p_aei_information21 := r_aei.aei_information21;
2979 p_aei_information22 := r_aei.aei_information22;
2980 p_aei_information23 := r_aei.aei_information23;
2981 p_aei_information24 := r_aei.aei_information24;
2982 p_aei_information25 := r_aei.aei_information25;
2983 p_aei_information26 := r_aei.aei_information26;
2984 p_aei_information27 := r_aei.aei_information27;
2985 p_aei_information28 := r_aei.aei_information28;
2986 p_aei_information29 := r_aei.aei_information29;
2987 p_aei_information30 := r_aei.aei_information30;
2988 p_object_version_number := r_aei.object_version_number;
2989 p_last_update_date := r_aei.last_update_date;
2990 p_last_updated_by := r_aei.last_updated_by;
2991 p_last_update_login := r_aei.last_update_login;
2992 p_created_by := r_aei.created_by;
2993 p_creation_date := r_aei.creation_date;
2994 hr_utility.set_location(' Leaving : ' || l_proc, 100);
2995 end fetch_asgei ;
2996
2997
2998 -- ---------------------------------------------------------------------------
2999 -- |--------------------------< return_special_information >----------------|
3000 -- --------------------------------------------------------------------------
3001
3002 Procedure return_special_information
3003 (p_person_id in number
3004 ,p_structure_name in varchar2
3005 ,p_effective_date in date
3006 ,p_special_info out nocopy ghr_api.special_information_type
3007 )
3008 is
3009 l_proc varchar2(72) := 'return_special_information ';
3010 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
3011 l_analysis_criteria_id per_analysis_criteria.analysis_criteria_id%type;
3012 l_session ghr_history_api.g_session_var_type;
3013 l_person_analysis_id per_person_analyses.person_analysis_id%type;
3014 l_pa_request_id ghr_pa_requests.pa_request_id%type;
3015
3016 Cursor c_flex_num is
3017 select flx.id_flex_num
3018 from fnd_id_flex_structures_tl flx
3019 where flx.id_flex_code = 'PEA' --
3020 and flx.application_id = 800 --
3021 and flx.id_flex_structure_name = p_structure_name
3022 and flx.language = 'US';
3023
3024 --6856387 added order by pa_history_id asc as in 10g if both from date and person analysis id is
3025 -- same then it is not ordering on pa_history_id(In 10g ordering will not happen based on primary key)
3026 Cursor c_person_analyses is
3027 select TO_NUMBER(gan.INFORMATION1) person_analysis_id,
3028 FND_DATE.CANONICAL_TO_DATE(gan.information9) date_from,
3029 FND_DATE.CANONICAL_TO_DATE(gan.information10) date_to ,
3030 pa_request_id,
3031 TO_NUMBER(gan.INFORMATION6) analysis_Criteria_id
3032 from ghr_pa_history gan , -- ghr_person_analyses_h_v gan,
3033 per_person_analyses per
3034 where gan.table_name = 'PER_PERSON_ANALYSES'
3035 and TO_NUMBER(gan.information7) = p_person_id -- information7 holds person_id
3036 and per.person_id = TO_NUMBER(gan.information7)
3037 and TO_NUMBER(gan.INFORMATION11)= l_id_flex_num -- information11 holds id_flex_Number
3038 and per.id_flex_num = TO_NUMBER(gan.INFORMATION11)
3039 and to_char(per.person_analysis_id) = gan.INFORMATION1 -- information1 holds person_analysis_id (3206581)
3040 and p_effective_date
3041 between nvl(fnd_date.canonical_to_date(gan.information9),p_effective_date)
3042 and nvl(fnd_date.canonical_to_date(gan.information10),p_effective_date) -- information9,information10 holds date_from,date_to
3043 order by 2,1 desc, pa_history_id asc;
3044
3045 ---- The following cursor is used only when the l_session.noa_id_correct is not null
3046 --- and from the gh52doup.pkb
3047 --- Not much impact so changing the select statement.
3048
3049 Cursor c_pan_ovn is
3050 select object_version_number
3051 from per_person_analyses pan
3052 where person_analysis_id = l_person_analysis_id;
3053
3054 Cursor c_sit is
3055 select pea.analysis_criteria_id,
3056 pea.segment1,
3057 pea.segment2,
3058 pea.segment3,
3059 pea.segment4,
3060 pea.segment5,
3061 pea.segment6,
3062 pea.segment7,
3063 pea.segment8,
3064 pea.segment9,
3065 pea.segment10,
3066 pea.segment11,
3067 pea.segment12,
3068 pea.segment13,
3069 pea.segment14,
3070 pea.segment15,
3071 pea.segment16,
3072 pea.segment17,
3073 pea.segment18,
3074 pea.segment19,
3075 pea.segment20,
3076 pea.segment21,
3077 pea.segment22,
3078 pea.segment23,
3079 pea.segment24,
3080 pea.segment25,
3081 pea.segment26,
3082 pea.segment27,
3083 pea.segment28,
3084 pea.segment29,
3085 pea.segment30
3086 from per_analysis_Criteria pea
3087 where pea.analysis_Criteria_id = l_analysis_criteria_id
3088 and p_effective_date
3089 between nvl(pea.start_date_active,p_effective_date)
3090 and nvl(pea.end_date_active,p_effective_date);
3091
3092 BEGIN
3093
3094 -- get g_session_var
3095 ghr_history_api.get_g_session_var(l_session);
3096 FOR flex_num IN c_flex_num LOOP
3097 l_id_flex_num := flex_num.id_flex_num;
3098 END LOOP;
3099
3100 IF l_id_flex_num IS NULL THEN
3101 hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
3102 hr_utility.raise_error;
3103 END IF;
3104
3105 hr_utility.set_location('got here 1234', 4777);
3106 hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3107 hr_utility.set_location('l_id_flex_num: ' || l_id_flex_num, 4777);
3108 hr_utility.set_location('l_session.noa_id_correct: ' || l_session.noa_id_correct, 4777);
3109 hr_utility.set_location('l_session.altered_pa_request_id: ' || l_session.altered_pa_request_id, 4777);
3110
3111 FOR per_analyses IN c_person_analyses LOOP
3112 --Bug 3103339
3113 l_pa_request_id := per_analyses.pa_request_id;
3114 IF (l_pa_request_id = l_session.altered_pa_request_id) THEN
3115 p_special_info.person_analysis_id := per_analyses.person_analysis_id;
3116 l_person_analysis_id := per_analyses.person_analysis_id;
3117 l_analysis_criteria_id := per_analyses.analysis_criteria_id;
3118 EXIT;
3119 ELSE
3120 p_special_info.person_analysis_id := per_analyses.person_analysis_id;
3121 l_person_analysis_id := per_analyses.person_analysis_id;
3122 l_analysis_criteria_id := per_analyses.analysis_criteria_id;
3123
3124 END IF;
3125 --Bug 3103339
3126 END LOOP;
3127
3128 hr_utility.set_location('got here 1235', 4778);
3129
3130 -- get ovn
3131
3132 FOR pan_ovn IN c_pan_ovn LOOP
3133 p_special_info.object_version_number := pan_ovn.object_version_number;
3134 END LOOP;
3135
3136 hr_utility.set_location('got here 1236', 4779);
3137
3138 FOR special_info IN c_sit LOOP
3139 p_special_info.segment1 := special_info.segment1;
3140 p_special_info.segment2 := special_info.segment2;
3141 p_special_info.segment3 := special_info.segment3;
3142 p_special_info.segment4 := special_info.segment4;
3143 p_special_info.segment5 := special_info.segment5;
3144 p_special_info.segment6 := special_info.segment6;
3145 p_special_info.segment7 := special_info.segment7;
3146 p_special_info.segment8 := special_info.segment8;
3147 p_special_info.segment9 := special_info.segment9;
3148 p_special_info.segment10 := special_info.segment10;
3149 p_special_info.segment11 := special_info.segment11;
3150 p_special_info.segment12 := special_info.segment12;
3151 p_special_info.segment13 := special_info.segment13;
3152 p_special_info.segment14 := special_info.segment14;
3153 p_special_info.segment15 := special_info.segment15;
3154 p_special_info.segment16 := special_info.segment16;
3155 p_special_info.segment17 := special_info.segment17;
3156 p_special_info.segment18 := special_info.segment18;
3157 p_special_info.segment19 := special_info.segment19;
3158 p_special_info.segment20 := special_info.segment20;
3159 p_special_info.segment21 := special_info.segment21;
3160 p_special_info.segment22 := special_info.segment22;
3161 p_special_info.segment23 := special_info.segment23;
3162 p_special_info.segment24 := special_info.segment24;
3163 p_special_info.segment25 := special_info.segment25;
3164 p_special_info.segment26 := special_info.segment26;
3165 p_special_info.segment27 := special_info.segment27;
3166 p_special_info.segment28 := special_info.segment28;
3167 p_special_info.segment29 := special_info.segment29;
3168 p_special_info.segment30 := special_info.segment30;
3169 END LOOP;
3170
3171 hr_utility.set_location('got here 1237', 4780);
3172 End return_special_information;
3173
3174 Procedure Fetch_ASGEI_prior_root_sf50
3175 (
3176 p_assignment_id in number ,
3177 p_information_type in varchar2,
3178 p_altered_pa_request_id in number ,
3179 p_noa_id_corrected in number ,
3180 p_date_effective in date default null,
3181 p_get_ovn_flag in varchar2 default 'N' ,
3182 p_asgei_data out nocopy per_assignment_extra_info%rowtype) as
3183
3184
3185 cursor get_root_hist_id(
3186 cp_pa_req_id in number,
3187 cp_noa_id in number) is
3188 select min(pa_history_id)
3189 from ghr_pa_history
3190 where pa_request_id =
3191 (select min(pa_request_id)
3192 from ghr_pa_requests
3193 connect by pa_request_id = prior altered_pa_request_id
3194 start with pa_request_id = cp_pa_req_id)
3195 and nature_of_action_id = cp_noa_id;
3196
3197 cursor c_extra_info_id is
3198 select aei.assignment_extra_info_id
3199 from per_assignment_extra_info aei
3200 where aei.assignment_id = p_assignment_id
3201 and aei.information_type = p_information_type;
3202
3203 l_pa_history_id number;
3204 -- l_pa_request_id number;
3205 l_extra_info_id number;
3206 l_result_code varchar2(30);
3207 l_proc varchar2(30):='Fetch_ASGEI_asof_root_sf50';
3208
3209 Begin
3210 hr_utility.set_location('Entering ' || l_proc,5);
3211 For extra_info in c_extra_info_id loop
3212 l_extra_info_id := extra_info.assignment_extra_info_id;
3213 End loop;
3214 If l_extra_info_id is null then
3215 hr_utility.set_location('EI Not Found ' || l_proc, 20);
3216 return;
3217 end if;
3218
3219 open get_root_hist_id( p_altered_pa_request_id, p_noa_id_corrected);
3220 fetch get_root_hist_id into l_pa_history_id;
3221 if get_root_hist_id%NotFound then
3222 close get_root_hist_id;
3223 hr_utility.set_location('Root History Not Found ' || l_proc, 30);
3224 return;
3225 else
3226 close get_root_hist_id;
3227 hr_utility.set_location('Calling Fetch_asgei ' || l_proc, 50);
3228 fetch_asgei (
3229 p_assignment_extra_info_id => l_extra_info_id,
3230 p_date_effective => p_date_effective,
3231 p_pa_history_id => l_pa_history_id,
3232 p_get_ovn_flag => p_get_ovn_flag,
3233 p_asgei_data => p_asgei_data,
3234 p_result_code => l_result_code);
3235 end if;
3236 hr_utility.set_location('Leaving ' || l_proc, 100);
3237
3238 End Fetch_ASGEI_prior_root_sf50;
3239
3240
3241 Procedure Fetch_asgn_prior_root_sf50
3242 (
3243 p_assignment_id in number ,
3244 p_altered_pa_request_id in number ,
3245 p_noa_id_corrected in number ,
3246 p_date_effective in date default null,
3247 -- p_get_ovn_flag in varchar2 default 'N' ,
3248 p_assignment_data out nocopy per_all_assignments_f%rowtype) as
3249
3250
3251
3252 cursor get_root_hist_id(
3253 cp_pa_req_id in number,
3254 cp_noa_id in number) is
3255 select min(pa_history_id)
3256 from ghr_pa_history
3257 where pa_request_id =
3258 (select min(pa_request_id)
3259 from ghr_pa_requests
3260 connect by pa_request_id = prior altered_pa_request_id
3261 start with pa_request_id = cp_pa_req_id)
3262 and nature_of_action_id = cp_noa_id;
3263
3264
3265 l_pa_history_id number;
3266 l_result_code varchar2(30);
3267 l_proc varchar2(30):='Fetch_asgn_prior_root_sf50';
3268
3269 Begin
3270 hr_utility.set_location('Entering ' || l_proc,5);
3271
3272 open get_root_hist_id( p_altered_pa_request_id, p_noa_id_corrected);
3273 fetch get_root_hist_id into l_pa_history_id;
3274 if get_root_hist_id%NotFound then
3275 close get_root_hist_id;
3276 hr_utility.set_location('Root History Not Found ' || l_proc, 30);
3277 return;
3278 else
3279 close get_root_hist_id;
3280 hr_utility.set_location('Calling Fetch_asgn ' || l_proc, 50);
3281 fetch_assignment (
3282 p_assignment_id => p_assignment_id,
3283 p_date_effective => p_date_effective,
3284 p_pa_history_id => l_pa_history_id,
3285 --p_get_ovn_flag => p_get_ovn_flag,
3286 p_assignment_data => p_assignment_data,
3287 p_result_code => l_result_code);
3288 end if;
3289 hr_utility.set_location('Leaving ' || l_proc, 100);
3290
3291 End Fetch_asgn_prior_root_sf50;
3292
3293 End GHR_HISTORY_FETCH;
[Home] [Help]