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