[Home] [Help]
134: order by pa_history_id desc;
135:
136: Begin
137:
138: hr_utility.set_location (' Entering : ' || l_proc, 10);
139:
140: for ghr_hist_ndt_canc3_rec in
141: ghr_hist_ndt_canc3 (cp_information1 => p_information1,
142: cp_max_date_effective => p_max_date_effective,
155: End loop;
156:
157: if not l_found then
158: p_result := FALSE;
159: hr_utility.set_location( 'NOT FOUND ' || l_proc, 50);
160: else
161: if l_root_pa_request_id is null then
162: -- ie core form change is the pre_record
163: -- so no need to traverse.
163: -- so no need to traverse.
164: p_hist_data := l_save_pa_history_data;
165: p_result := TRUE;
166: else
167: hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
168: Traverse(
169: p_pa_history_id => NULL,
170: p_root_pa_request_id => l_root_pa_request_id,
171: p_noa_id => l_noa_id,
176:
177:
178: if not l_found then
179: p_result := FALSE;
180: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
181: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
182: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
183:
184: hr_utility.set_message(8301,'GHR_38496_TRAVERSE_FAILED');
177:
178: if not l_found then
179: p_result := FALSE;
180: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
181: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
182: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
183:
184: hr_utility.set_message(8301,'GHR_38496_TRAVERSE_FAILED');
185: hr_utility.raise_error;
178: if not l_found then
179: p_result := FALSE;
180: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
181: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
182: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
183:
184: hr_utility.set_message(8301,'GHR_38496_TRAVERSE_FAILED');
185: hr_utility.raise_error;
186: else
180: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
181: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
182: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
183:
184: hr_utility.set_message(8301,'GHR_38496_TRAVERSE_FAILED');
185: hr_utility.raise_error;
186: else
187: p_result := TRUE;
188: end if;
181: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
182: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
183:
184: hr_utility.set_message(8301,'GHR_38496_TRAVERSE_FAILED');
185: hr_utility.raise_error;
186: else
187: p_result := TRUE;
188: end if;
189: end if;
188: end if;
189: end if;
190: end if;
191:
192: hr_utility.set_location (' Leaving : ' || l_proc, 100);
193:
194: End;
195:
196: -- This procedure fetched most recent, prior to p_pa_history_id, on p_date_Effective
291: where pa_history_id = cp_pa_history_id;
292:
293: Begin
294:
295: hr_utility.set_location (' Entering : ' || l_proc, 10);
296:
297: open get_req_and_noa( p_pa_history_id);
298: fetch get_req_and_noa into
299: l_pa_request_id_for_hist,
310:
311: if get_root_hist_id%notfound then
312: -- error
313: close get_root_hist_id;
314: hr_utility.set_message(8301,'GHR_38352_ROOT_HISTID_NFND');
315: hr_utility.raise_error;
316: else
317: close get_root_hist_id;
318: hr_utility.set_location('l_root_pa_req_for_pa_req : ' || l_root_pa_req_for_pa_req || l_proc, 12);
311: if get_root_hist_id%notfound then
312: -- error
313: close get_root_hist_id;
314: hr_utility.set_message(8301,'GHR_38352_ROOT_HISTID_NFND');
315: hr_utility.raise_error;
316: else
317: close get_root_hist_id;
318: hr_utility.set_location('l_root_pa_req_for_pa_req : ' || l_root_pa_req_for_pa_req || l_proc, 12);
319: hr_utility.set_location('l_noa_id_for_pa_req : ' || l_noa_id_for_pa_req || l_proc, 12);
314: hr_utility.set_message(8301,'GHR_38352_ROOT_HISTID_NFND');
315: hr_utility.raise_error;
316: else
317: close get_root_hist_id;
318: hr_utility.set_location('l_root_pa_req_for_pa_req : ' || l_root_pa_req_for_pa_req || l_proc, 12);
319: hr_utility.set_location('l_noa_id_for_pa_req : ' || l_noa_id_for_pa_req || l_proc, 12);
320:
321: if l_root_pa_req_for_pa_req = l_pa_request_id_for_hist and
322: l_noa_id_for_pa_req = l_noa_id_for_hist then
315: hr_utility.raise_error;
316: else
317: close get_root_hist_id;
318: hr_utility.set_location('l_root_pa_req_for_pa_req : ' || l_root_pa_req_for_pa_req || l_proc, 12);
319: hr_utility.set_location('l_noa_id_for_pa_req : ' || l_noa_id_for_pa_req || l_proc, 12);
320:
321: if l_root_pa_req_for_pa_req = l_pa_request_id_for_hist and
322: l_noa_id_for_pa_req = l_noa_id_for_hist then
323: NULL;
321: if l_root_pa_req_for_pa_req = l_pa_request_id_for_hist and
322: l_noa_id_for_pa_req = l_noa_id_for_hist then
323: NULL;
324: else
325: hr_utility.set_location(' Skip settings NULLed' || l_proc, 12);
326: l_root_pa_req_for_pa_req := NULL;
327: l_noa_id_for_pa_req := NULL;
328: end if;
329: end if;
327: l_noa_id_for_pa_req := NULL;
328: end if;
329: end if;
330:
331: hr_utility.set_location( 'l_root_pa_history_id :' || l_root_pa_history_id || l_proc, 13);
332: hr_utility.set_location( 'p_pa_history_id : ' || p_pa_history_id || l_proc, 13);
333: hr_utility.set_location( 'p_information1 : ' || p_information1 || l_proc, 13);
334: hr_utility.set_location( 'p_p_table_name : ' || p_table_name || l_proc, 13);
335: hr_utility.set_location( 'p_date_Effective : ' || p_date_Effective || l_proc, 13);
328: end if;
329: end if;
330:
331: hr_utility.set_location( 'l_root_pa_history_id :' || l_root_pa_history_id || l_proc, 13);
332: hr_utility.set_location( 'p_pa_history_id : ' || p_pa_history_id || l_proc, 13);
333: hr_utility.set_location( 'p_information1 : ' || p_information1 || l_proc, 13);
334: hr_utility.set_location( 'p_p_table_name : ' || p_table_name || l_proc, 13);
335: hr_utility.set_location( 'p_date_Effective : ' || p_date_Effective || l_proc, 13);
336:
329: end if;
330:
331: hr_utility.set_location( 'l_root_pa_history_id :' || l_root_pa_history_id || l_proc, 13);
332: hr_utility.set_location( 'p_pa_history_id : ' || p_pa_history_id || l_proc, 13);
333: hr_utility.set_location( 'p_information1 : ' || p_information1 || l_proc, 13);
334: hr_utility.set_location( 'p_p_table_name : ' || p_table_name || l_proc, 13);
335: hr_utility.set_location( 'p_date_Effective : ' || p_date_Effective || l_proc, 13);
336:
337: for ghr_hist_ndt_canc_rec in
330:
331: hr_utility.set_location( 'l_root_pa_history_id :' || l_root_pa_history_id || l_proc, 13);
332: hr_utility.set_location( 'p_pa_history_id : ' || p_pa_history_id || l_proc, 13);
333: hr_utility.set_location( 'p_information1 : ' || p_information1 || l_proc, 13);
334: hr_utility.set_location( 'p_p_table_name : ' || p_table_name || l_proc, 13);
335: hr_utility.set_location( 'p_date_Effective : ' || p_date_Effective || l_proc, 13);
336:
337: for ghr_hist_ndt_canc_rec in
338: ghr_hist_ndt_canc(cp_root_pa_history_id => l_root_pa_history_id,
331: hr_utility.set_location( 'l_root_pa_history_id :' || l_root_pa_history_id || l_proc, 13);
332: hr_utility.set_location( 'p_pa_history_id : ' || p_pa_history_id || l_proc, 13);
333: hr_utility.set_location( 'p_information1 : ' || p_information1 || l_proc, 13);
334: hr_utility.set_location( 'p_p_table_name : ' || p_table_name || l_proc, 13);
335: hr_utility.set_location( 'p_date_Effective : ' || p_date_Effective || l_proc, 13);
336:
337: for ghr_hist_ndt_canc_rec in
338: ghr_hist_ndt_canc(cp_root_pa_history_id => l_root_pa_history_id,
339: cp_information1 => p_information1,
355: End loop;
356:
357: if not l_found then
358: p_result := FALSE;
359: hr_utility.set_location( 'NOT FOUND ' || l_proc, 50);
360: else
361: if l_root_pa_request_id is null then
362: -- ie core form change is the pre_record
363: -- so no need to traverse.
363: -- so no need to traverse.
364: p_hist_data := l_save_pa_history_data;
365: p_result := TRUE;
366: else
367: hr_utility.set_location('Selected Root Hist Id ' || l_root_pa_history_id || l_proc, 60);
368: hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
369:
370: Traverse(
371: p_pa_history_id => p_pa_history_id,
364: p_hist_data := l_save_pa_history_data;
365: p_result := TRUE;
366: else
367: hr_utility.set_location('Selected Root Hist Id ' || l_root_pa_history_id || l_proc, 60);
368: hr_utility.set_location('Selected Root Request Id ' || l_root_pa_request_id || l_proc, 60);
369:
370: Traverse(
371: p_pa_history_id => p_pa_history_id,
372: p_root_pa_request_id => l_root_pa_request_id,
377: p_hist_data => p_hist_data);
378:
379: if not l_found then
380: p_result := FALSE;
381: hr_utility.set_location(' p_pa_history_id : ' || p_pa_history_id || l_proc, 65);
382: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
383: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384:
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
378:
379: if not l_found then
380: p_result := FALSE;
381: hr_utility.set_location(' p_pa_history_id : ' || p_pa_history_id || l_proc, 65);
382: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
383: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384:
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
379: if not l_found then
380: p_result := FALSE;
381: hr_utility.set_location(' p_pa_history_id : ' || p_pa_history_id || l_proc, 65);
382: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
383: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384:
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387: hr_utility.raise_error;
381: hr_utility.set_location(' p_pa_history_id : ' || p_pa_history_id || l_proc, 65);
382: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
383: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384:
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387: hr_utility.raise_error;
388: else
389: hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
382: hr_utility.set_location(' l_root_pa_request_id : ' || l_root_pa_request_id || l_proc, 65);
383: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384:
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387: hr_utility.raise_error;
388: else
389: hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
390: hr_utility.set_location('Selected PaRequest Id ' || p_hist_data.pa_request_id || l_proc, 62);
383: hr_utility.set_location(' l_noa_id : ' || l_noa_id || l_proc, 65);
384:
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387: hr_utility.raise_error;
388: else
389: hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
390: hr_utility.set_location('Selected PaRequest Id ' || p_hist_data.pa_request_id || l_proc, 62);
391: p_result := TRUE;
385: hr_utility.set_location('Traverse Failed ' || l_proc, 70);
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387: hr_utility.raise_error;
388: else
389: hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
390: hr_utility.set_location('Selected PaRequest Id ' || p_hist_data.pa_request_id || l_proc, 62);
391: p_result := TRUE;
392: end if;
393: end if;
386: hr_utility.set_message(8301,'GHR_38497_TRAVERSE_FAILED');
387: hr_utility.raise_error;
388: else
389: hr_utility.set_location('Selected Hist Id ' || p_hist_data.pa_history_id || l_proc, 61);
390: hr_utility.set_location('Selected PaRequest Id ' || p_hist_data.pa_request_id || l_proc, 62);
391: p_result := TRUE;
392: end if;
393: end if;
394: end if;
392: end if;
393: end if;
394: end if;
395:
396: hr_utility.set_location (' Leaving : ' || l_proc, 100);
397:
398: End get_hist_rec_ason_date;
399:
400: -- This procedure is called by get_hist_rec_ason_max_date and get_hist_rec_ason_date
456: l_proc varchar2(30):='filter_best_candidate_record';
457:
458: Begin
459: l_corr_record_found := FALSE;
460: hr_utility.set_location (' Entering : ' || l_proc, 10);
461: hr_utility.set_location (' p_hist_data.pa_history_id ' || p_hist_data.pa_history_id, 11);
462: hr_utility.set_location (' p_pa_req_id_skip ' || p_pa_req_id_skip, 11);
463: hr_utility.set_location (' p_noa_id_skip ' || p_noa_id_skip, 11);
464:
457:
458: Begin
459: l_corr_record_found := FALSE;
460: hr_utility.set_location (' Entering : ' || l_proc, 10);
461: hr_utility.set_location (' p_hist_data.pa_history_id ' || p_hist_data.pa_history_id, 11);
462: hr_utility.set_location (' p_pa_req_id_skip ' || p_pa_req_id_skip, 11);
463: hr_utility.set_location (' p_noa_id_skip ' || p_noa_id_skip, 11);
464:
465: if p_hist_data.pa_request_id is null then
458: Begin
459: l_corr_record_found := FALSE;
460: hr_utility.set_location (' Entering : ' || l_proc, 10);
461: hr_utility.set_location (' p_hist_data.pa_history_id ' || p_hist_data.pa_history_id, 11);
462: hr_utility.set_location (' p_pa_req_id_skip ' || p_pa_req_id_skip, 11);
463: hr_utility.set_location (' p_noa_id_skip ' || p_noa_id_skip, 11);
464:
465: if p_hist_data.pa_request_id is null then
466: -- Core Form change created this record.
459: l_corr_record_found := FALSE;
460: hr_utility.set_location (' Entering : ' || l_proc, 10);
461: hr_utility.set_location (' p_hist_data.pa_history_id ' || p_hist_data.pa_history_id, 11);
462: hr_utility.set_location (' p_pa_req_id_skip ' || p_pa_req_id_skip, 11);
463: hr_utility.set_location (' p_noa_id_skip ' || p_noa_id_skip, 11);
464:
465: if p_hist_data.pa_request_id is null then
466: -- Core Form change created this record.
467: hr_utility.set_location (' Core Form 1' || l_proc, 20);
463: hr_utility.set_location (' p_noa_id_skip ' || p_noa_id_skip, 11);
464:
465: if p_hist_data.pa_request_id is null then
466: -- Core Form change created this record.
467: hr_utility.set_location (' Core Form 1' || l_proc, 20);
468: if p_hist_data.pa_history_id > nvl(p_save_history_id, 0) then
469: hr_utility.set_location (' Accept Core Form 2' || l_proc, 21);
470: p_save_found := TRUE;
471: p_save_history_id := p_hist_data.pa_history_id;
465: if p_hist_data.pa_request_id is null then
466: -- Core Form change created this record.
467: hr_utility.set_location (' Core Form 1' || l_proc, 20);
468: if p_hist_data.pa_history_id > nvl(p_save_history_id, 0) then
469: hr_utility.set_location (' Accept Core Form 2' || l_proc, 21);
470: p_save_found := TRUE;
471: p_save_history_id := p_hist_data.pa_history_id;
472: p_save_pa_request_id := null;
473: p_save_noa_id := null;
474: p_save_pa_hist_data := p_hist_data;
475: end if;
476: elsif p_hist_data.altered_pa_request_id is NULL then
477: -- root pa_request_id record.
478: hr_utility.set_location (' Root pa req 1' || l_proc, 30);
479: if p_hist_data.pa_request_id = nvl(p_pa_req_id_skip, -1) and
480: p_hist_data.nature_of_action_id = nvl(p_noa_id_skip, -1) then
481: hr_utility.set_location('Skip this record : Hist ID ' || p_hist_data.pa_history_id, 35);
482: else
477: -- root pa_request_id record.
478: hr_utility.set_location (' Root pa req 1' || l_proc, 30);
479: if p_hist_data.pa_request_id = nvl(p_pa_req_id_skip, -1) and
480: p_hist_data.nature_of_action_id = nvl(p_noa_id_skip, -1) then
481: hr_utility.set_location('Skip this record : Hist ID ' || p_hist_data.pa_history_id, 35);
482: else
483:
484: --Bug 6314442 added to check any corrections are available after manual changes
485: -- Bug # 9800367 considered to check the corrections available greater than the
493:
494: --Bug 6314442 added l_corr_record_found
495: -- Bug # 6976905 added another condition while checking correction record for dual action
496: if p_hist_data.pa_history_id > nvl(p_save_history_id, 0) or (l_corr_record_found and nvl(p_hist_data.pa_request_id,'-1') <> nvl(p_save_pa_request_id,'-1')) then
497: hr_utility.set_location (' Accept Root pa req 2' || l_proc, 31);
498: p_save_found := TRUE;
499: p_save_history_id := p_hist_data.pa_history_id;
500: p_save_pa_request_id := p_hist_data.pa_request_id;
501: p_save_noa_id := p_hist_data.nature_of_action_id;
504: end if;
505: else
506: -- Borken chain root
507: -- get root pa_request_id
508: hr_utility.set_location (' Broken Chain 1' || l_proc, 40);
509: open c_get_root_req( p_hist_data.pa_request_id);
510: Fetch c_get_root_req into l_root_pa_req_id_broken;
511: if c_get_root_req%notfound then
512: close c_get_root_req;
509: open c_get_root_req( p_hist_data.pa_request_id);
510: Fetch c_get_root_req into l_root_pa_req_id_broken;
511: if c_get_root_req%notfound then
512: close c_get_root_req;
513: hr_utility.set_location (' Root for Broken Chain not found (Error) ' || l_proc, 41);
514: -- this must never happen
515: -- raise error;
516: else
517: close c_get_root_req;
522: p_hist_data.nature_of_action_id);
523: Fetch c_get_min_hist into l_root_hist_id_broken;
524: if c_get_min_hist%notfound then
525: close c_get_min_hist;
526: hr_utility.set_location (' Min Hist not found Broken Chain (Error) ' || l_proc, 42);
527: -- this must never happen
528: -- raise error;
529: else
530: close c_get_min_hist;
532:
533: if l_root_pa_req_id_broken = nvl(p_pa_req_id_skip, -1) and
534: p_hist_data.nature_of_action_id = nvl(p_noa_id_skip, -1) then
535:
536: hr_utility.set_location('Skip this record : Hist ID ' || p_hist_data.pa_history_id, 50);
537: else
538: if l_root_hist_id_broken > nvl(p_save_history_id, 0) then
539: hr_utility.set_location (' Accept Broken Chain 3' || l_proc, 43);
540: p_save_found := TRUE;
535:
536: hr_utility.set_location('Skip this record : Hist ID ' || p_hist_data.pa_history_id, 50);
537: else
538: if l_root_hist_id_broken > nvl(p_save_history_id, 0) then
539: hr_utility.set_location (' Accept Broken Chain 3' || l_proc, 43);
540: p_save_found := TRUE;
541: p_save_history_id := l_root_hist_id_broken;
542: p_save_pa_request_id := l_root_pa_req_id_broken;
543: p_save_noa_id := p_hist_data.nature_of_action_id;
546: end if;
547: end if;
548: end if;
549:
550: hr_utility.set_location (' p_save_noa_id ' || p_save_noa_id, 90);
551: hr_utility.set_location (' p_save_history_id ' || p_save_history_id, 90);
552: hr_utility.set_location (' p_save_pa_request_id ' || p_save_pa_request_id, 90);
553: hr_utility.set_location (' Leaving : ' || l_proc, 100);
554:
547: end if;
548: end if;
549:
550: hr_utility.set_location (' p_save_noa_id ' || p_save_noa_id, 90);
551: hr_utility.set_location (' p_save_history_id ' || p_save_history_id, 90);
552: hr_utility.set_location (' p_save_pa_request_id ' || p_save_pa_request_id, 90);
553: hr_utility.set_location (' Leaving : ' || l_proc, 100);
554:
555:
548: end if;
549:
550: hr_utility.set_location (' p_save_noa_id ' || p_save_noa_id, 90);
551: hr_utility.set_location (' p_save_history_id ' || p_save_history_id, 90);
552: hr_utility.set_location (' p_save_pa_request_id ' || p_save_pa_request_id, 90);
553: hr_utility.set_location (' Leaving : ' || l_proc, 100);
554:
555:
556: End;
549:
550: hr_utility.set_location (' p_save_noa_id ' || p_save_noa_id, 90);
551: hr_utility.set_location (' p_save_history_id ' || p_save_history_id, 90);
552: hr_utility.set_location (' p_save_pa_request_id ' || p_save_pa_request_id, 90);
553: hr_utility.set_location (' Leaving : ' || l_proc, 100);
554:
555:
556: End;
557:
665:
666: Begin
667:
668: /* get pre. */
669: hr_utility.set_location( 'Entering : ' || l_proc, 10);
670: hr_utility.set_location( ' Displaying parameters : ' || l_proc, 11);
671: hr_utility.set_location( ' p_table_name : ' || p_table_name || l_proc, 11);
672: hr_utility.set_location( ' p_information1 : ' || p_information1 || l_proc, 11);
673: hr_utility.set_location( ' p_pa_history_id : ' || p_pa_history_id || l_proc, 11);
666: Begin
667:
668: /* get pre. */
669: hr_utility.set_location( 'Entering : ' || l_proc, 10);
670: hr_utility.set_location( ' Displaying parameters : ' || l_proc, 11);
671: hr_utility.set_location( ' p_table_name : ' || p_table_name || l_proc, 11);
672: hr_utility.set_location( ' p_information1 : ' || p_information1 || l_proc, 11);
673: hr_utility.set_location( ' p_pa_history_id : ' || p_pa_history_id || l_proc, 11);
674:
667:
668: /* get pre. */
669: hr_utility.set_location( 'Entering : ' || l_proc, 10);
670: hr_utility.set_location( ' Displaying parameters : ' || l_proc, 11);
671: hr_utility.set_location( ' p_table_name : ' || p_table_name || l_proc, 11);
672: hr_utility.set_location( ' p_information1 : ' || p_information1 || l_proc, 11);
673: hr_utility.set_location( ' p_pa_history_id : ' || p_pa_history_id || l_proc, 11);
674:
675: get_hist_rec_ason_date(
668: /* get pre. */
669: hr_utility.set_location( 'Entering : ' || l_proc, 10);
670: hr_utility.set_location( ' Displaying parameters : ' || l_proc, 11);
671: hr_utility.set_location( ' p_table_name : ' || p_table_name || l_proc, 11);
672: hr_utility.set_location( ' p_information1 : ' || p_information1 || l_proc, 11);
673: hr_utility.set_location( ' p_pa_history_id : ' || p_pa_history_id || l_proc, 11);
674:
675: get_hist_rec_ason_date(
676: p_information1 => p_information1,
669: hr_utility.set_location( 'Entering : ' || l_proc, 10);
670: hr_utility.set_location( ' Displaying parameters : ' || l_proc, 11);
671: hr_utility.set_location( ' p_table_name : ' || p_table_name || l_proc, 11);
672: hr_utility.set_location( ' p_information1 : ' || p_information1 || l_proc, 11);
673: hr_utility.set_location( ' p_pa_history_id : ' || p_pa_history_id || l_proc, 11);
674:
675: get_hist_rec_ason_date(
676: p_information1 => p_information1,
677: p_date_effective => p_date_effective,
678: p_table_name => p_table_name,
679: p_pa_history_id => p_pa_history_id ,
680: p_hist_data => l_hist_data,
681: p_result => l_found);
682: hr_utility.set_location (' End Loop 1' || l_proc, 60);
683:
684: p_hist_data := l_hist_data;
685:
686: ---Bug 2413991 --AVR
703: end if;
704: ---Bug 2413991 --AVR
705:
706: if not l_found then
707: hr_utility.set_location( 'Loop1 not found ' || l_proc, 70);
708: open ghr_hist_ndt_canc2
709: (cp_information1 => p_information1,
710: cp_date_effective => p_date_effective,
711: cp_table_name => p_table_name);
722: cp_table_name => p_table_name,
723: cp_pa_history_id => p_pa_history_id);
724: fetch ghr_hist_ndt_canc3 into l_date_effective;
725: if ( l_date_effective is null ) then
726: hr_utility.set_location( 'NOT FOUND ' || l_proc, 80);
727: p_result_code := c_not_found;
728: close ghr_hist_ndt_canc3;
729: else
730: close ghr_hist_ndt_canc3;
737: p_hist_data := l_hist_data;
738: if not l_found then
739: /* handle case where there is no pre here. */
740: p_result_code := c_not_found;
741: hr_utility.set_location (' NOT Found ' || l_proc, 150);
742: else
743: hr_utility.set_location (' Found ' || l_proc, 160);
744: end if;
745: end if;
739: /* handle case where there is no pre here. */
740: p_result_code := c_not_found;
741: hr_utility.set_location (' NOT Found ' || l_proc, 150);
742: else
743: hr_utility.set_location (' Found ' || l_proc, 160);
744: end if;
745: end if;
746: else
747: close ghr_hist_ndt_canc2;
744: end if;
745: end if;
746: else
747: close ghr_hist_ndt_canc2;
748: hr_utility.set_location( 'l_max_date_effective : ' || l_max_date_effective || l_proc, 80);
749: get_hist_rec_ason_max_date(
750: p_information1 => p_information1,
751: p_max_date_effective => l_max_date_effective,
752: p_table_name => p_table_name,
755: p_hist_data := l_hist_data;
756: if not l_found then
757: /* handle case where there is no pre here. */
758: p_result_code := c_not_found;
759: hr_utility.set_location (' NOT Found ' || l_proc, 150);
760: else
761: hr_utility.set_location (' Found ' || l_proc, 160);
762: end if;
763: end if;
757: /* handle case where there is no pre here. */
758: p_result_code := c_not_found;
759: hr_utility.set_location (' NOT Found ' || l_proc, 150);
760: else
761: hr_utility.set_location (' Found ' || l_proc, 160);
762: end if;
763: end if;
764: End if;
765: hr_utility.set_location (' Leaving ' || l_proc, 112);
761: hr_utility.set_location (' Found ' || l_proc, 160);
762: end if;
763: end if;
764: End if;
765: hr_utility.set_location (' Leaving ' || l_proc, 112);
766: End;
767:
768: Procedure fetch_for_date_eff(
769: p_table_name in varchar2,
804: will return the post-update record. The session variable
805: pre-update-record will hold the values which can be used for
806: pre-record values
807: */
808: hr_utility.set_location( l_proc, 20);
809: open ghr_hist_post(
810: cp_information1 => p_information1,
811: cp_date_effective => p_date_effective,
812: cp_table_name => p_table_name);
815: if ( l_max_date_effective is null ) then
816: /* max function always returns a result, so checking for %NOTFOUND is always false.
817: date_effective is a mandatory column, so we can check if there were any rows by
818: checking if the max_date_effective is null. */
819: hr_utility.set_location( l_proc, 70);
820: /* handle case where there is no pre here. */
821: p_result_code := c_not_found;
822: close ghr_hist_post;
823: else
831:
832: if not l_found then
833: /* handle case where there is no pre here. */
834: p_result_code := c_not_found;
835: hr_utility.set_location (' NOT Found ' || l_proc, 80);
836: else
837: hr_utility.set_location (' Found ' || l_proc, 80);
838: p_hist_data := l_hist_data;
839: end if;
833: /* handle case where there is no pre here. */
834: p_result_code := c_not_found;
835: hr_utility.set_location (' NOT Found ' || l_proc, 80);
836: else
837: hr_utility.set_location (' Found ' || l_proc, 80);
838: p_hist_data := l_hist_data;
839: end if;
840: end if;
841:
838: p_hist_data := l_hist_data;
839: end if;
840: end if;
841:
842: hr_utility.set_location( ' Leaving : ' || l_proc, 100);
843:
844: End fetch_for_date_eff;
845:
846:
877: l_hist_data ghr_pA_history%rowtype;
878: l_proc varchar2(30):='Traverse';
879: Begin
880: -- traverse.
881: hr_utility.set_location (' Entering : ' || l_proc, 10);
882: hr_utility.set_location ('hist id ' || p_pa_history_id || l_proc, 20);
883: hr_utility.set_location ('pa req ' || p_root_pa_request_id || l_proc, 30);
884: hr_utility.set_location ('noa Id ' || p_noa_id || l_proc, 40);
885:
878: l_proc varchar2(30):='Traverse';
879: Begin
880: -- traverse.
881: hr_utility.set_location (' Entering : ' || l_proc, 10);
882: hr_utility.set_location ('hist id ' || p_pa_history_id || l_proc, 20);
883: hr_utility.set_location ('pa req ' || p_root_pa_request_id || l_proc, 30);
884: hr_utility.set_location ('noa Id ' || p_noa_id || l_proc, 40);
885:
886: open ghr_hist_ndt_traverse_corrs(
879: Begin
880: -- traverse.
881: hr_utility.set_location (' Entering : ' || l_proc, 10);
882: hr_utility.set_location ('hist id ' || p_pa_history_id || l_proc, 20);
883: hr_utility.set_location ('pa req ' || p_root_pa_request_id || l_proc, 30);
884: hr_utility.set_location ('noa Id ' || p_noa_id || l_proc, 40);
885:
886: open ghr_hist_ndt_traverse_corrs(
887: cp_pa_history_id => p_pa_history_id,
880: -- traverse.
881: hr_utility.set_location (' Entering : ' || l_proc, 10);
882: hr_utility.set_location ('hist id ' || p_pa_history_id || l_proc, 20);
883: hr_utility.set_location ('pa req ' || p_root_pa_request_id || l_proc, 30);
884: hr_utility.set_location ('noa Id ' || p_noa_id || l_proc, 40);
885:
886: open ghr_hist_ndt_traverse_corrs(
887: cp_pa_history_id => p_pa_history_id,
888: cp_pa_request_id => p_root_pa_request_id,
893: fetch ghr_hist_ndt_traverse_corrs into l_hist_data;
894: p_result:= ghr_hist_ndt_traverse_corrs%found;
895: close ghr_hist_ndt_traverse_corrs;
896: p_hist_data := l_hist_data;
897: hr_utility.set_location ('Hist ID : ' || l_hist_data.pa_history_id, 90);
898: hr_utility.set_location ('Leaving : ' || l_proc, 100);
899: End;
900:
901: Procedure Fetch_for_correction(
894: p_result:= ghr_hist_ndt_traverse_corrs%found;
895: close ghr_hist_ndt_traverse_corrs;
896: p_hist_data := l_hist_data;
897: hr_utility.set_location ('Hist ID : ' || l_hist_data.pa_history_id, 90);
898: hr_utility.set_location ('Leaving : ' || l_proc, 100);
899: End;
900:
901: Procedure Fetch_for_correction(
902: p_table_name in varchar2,
945: l_proc varchar2(30):='Fetch_for_correction';
946:
947: Begin
948:
949: hr_utility.set_location (' Entering ' || l_proc, 10);
950:
951:
952: open ghr_hist_corr(
953: cp_information1 => p_information1,
956: cp_table_name => p_table_name) ;
957:
958: fetch ghr_hist_corr into p_hist_data;
959: if ( ghr_hist_corr%NOTFOUND ) then
960: hr_utility.set_location ( l_proc, 20);
961: get_min_hist_id(
962: p_pa_request_id => p_altered_pa_request_id,
963: p_noa_id => p_noa_id_corrected,
964: p_pa_history_id => l_pa_history_id,
971: p_pa_history_id => l_pa_history_id,
972: p_hist_data => p_hist_data,
973: p_result_code => p_result_code);
974: else
975: hr_utility.set_location (' NOT Found ' || l_proc, 40);
976: --Begin Bug# 13633700
977: --Earlier for every action one history record used to be created with Perf Appraisal SIT , but after the fix 12618741
978: -- it is not creating. So fetch for correction is returning not found record for some actions.
979: -- Now added following code to get at least one history record less than RPA effective date to fix the issue.
988: if ( l_max_date_effective is null ) then
989: /* max function always returns a result, so checking for %NOTFOUND is always false.
990: date_effective is a mandatory column, so we can check if there were any rows by
991: checking if the max_date_effective is null. */
992: hr_utility.set_location( 'MAX Date NOT FOUND ' || l_proc, 80);
993: p_result_code := c_not_found;
994: close ghr_get_hist_max_date;
995: else
996: close ghr_get_hist_max_date;
993: p_result_code := c_not_found;
994: close ghr_get_hist_max_date;
995: else
996: close ghr_get_hist_max_date;
997: hr_utility.set_location( 'l_max_date_effective : ' || l_max_date_effective || l_proc, 90);
998: get_hist_rec_ason_max_date(
999: p_information1 => p_information1,
1000: p_max_date_effective => l_max_date_effective,
1001: p_table_name => p_table_name,
1002: p_hist_data => p_hist_data,
1003: p_result => l_found);
1004: if not l_found then
1005: p_result_code := c_not_found;
1006: hr_utility.set_location (' History NOT Found with Max Date ' || l_proc, 150);
1007: else
1008: hr_utility.set_location (' History Found with Max Date ' || l_proc, 160);
1009: end if;
1010: end if;
1004: if not l_found then
1005: p_result_code := c_not_found;
1006: hr_utility.set_location (' History NOT Found with Max Date ' || l_proc, 150);
1007: else
1008: hr_utility.set_location (' History Found with Max Date ' || l_proc, 160);
1009: end if;
1010: end if;
1011:
1012: --End Bug# 13633700
1013: end if;
1014: end if;
1015: close ghr_hist_corr;
1016:
1017: hr_utility.set_location( 'Leaving : ' || l_proc, 100);
1018:
1019: End Fetch_for_correction;
1020:
1021:
1031:
1032: l_proc varchar2(30):='Fetch_hist_data';
1033: Begin
1034:
1035: hr_utility.set_location (' Entering ' || l_proc, 10);
1036: if p_pa_history_id is not null then
1037: fetch_for_histid (
1038: p_table_name => p_table_name,
1039: p_information1 => p_information1,
1060: p_date_effective => p_date_effective,
1061: p_hist_data => p_hist_data,
1062: p_result_code => p_result_code);
1063: end if;
1064: hr_utility.set_location (' Leaving ' || l_proc, 100);
1065:
1066: End Fetch_hist_data;
1067:
1068: --
1084:
1085: l_proc varchar2(30):='get_min_hist_id';
1086:
1087: Begin
1088: hr_utility.set_location('Entering:'|| l_proc, 5);
1089: open get_hist_id(
1090: cp_pa_request_id => p_pa_request_id,
1091: cp_noa_id => p_noa_id);
1092: fetch get_hist_id into p_pa_history_id;
1091: cp_noa_id => p_noa_id);
1092: fetch get_hist_id into p_pa_history_id;
1093: p_result := get_hist_id%found;
1094: close get_hist_id;
1095: hr_utility.set_location('Leaving:'|| l_proc, 10);
1096:
1097: End get_min_hist_id;
1098:
1099: /* Following pacakge is redundent
1112: where pa_request_id = p_pa_request_id
1113: and nature_of_action_id = p_noa_id;
1114: l_proc varchar2(30):='get_hist_id';
1115: Begin
1116: hr_utility.set_location('Entering:'|| l_proc, 5);
1117: open get_hist_id;
1118: fetch get_hist_id into p_pa_history_id;
1119: if get_hist_id%notfound then
1120: p_result := FALSE;
1154: select *
1155: from per_all_people_f
1156: where rowid = p_rowid;
1157: Begin
1158: hr_utility.set_location('Entering:'|| l_proc, 5);
1159: p_result_code := null;
1160: if ( p_rowid is not null ) then
1161: /* This part of the procedure is used to fetch the exact row
1162: which will be the post-update record. So if the procedure was
1162: which will be the post-update record. So if the procedure was
1163: passed with p_row_id parameter it'll always return the
1164: post-update record.
1165: */
1166: hr_utility.set_location( l_proc, 10);
1167: open per_people_f_rowid_cursor;
1168: fetch per_people_f_rowid_cursor into p_people_data;
1169: if ( per_people_f_rowid_cursor%NOTFOUND ) then
1170: p_result_code := c_not_found;
1169: if ( per_people_f_rowid_cursor%NOTFOUND ) then
1170: p_result_code := c_not_found;
1171: end if;
1172: close per_people_f_rowid_cursor;
1173: hr_utility.set_location( l_proc, 15);
1174: elsif ( p_pa_history_id is null and
1175: p_altered_pa_request_id is null and
1176: p_noa_id_corrected is null ) then
1177: /* This part of the procedure will fetch the Pre-record values
1180: will return the post-update record. The session variable
1181: pre-update-record will hold the values which can be used for
1182: pre-record values
1183: */
1184: hr_utility.set_location( l_proc, 20);
1185: open per_people_f_cursor;
1186: fetch per_people_f_cursor into p_people_data;
1187: if ( per_people_f_cursor%NOTFOUND ) then
1188: p_result_code := c_not_found;
1187: if ( per_people_f_cursor%NOTFOUND ) then
1188: p_result_code := c_not_found;
1189: end if;
1190: close per_people_f_cursor;
1191: hr_utility.set_location( l_proc, 25);
1192: else
1193: Fetch_hist_data(
1194: p_table_name => ghr_history_api.g_peop_table,
1195: p_information1 => p_person_id,
1208: p_people_data := l_people_data;
1209: end if;
1210:
1211: end if;
1212: hr_utility.set_location(' Leaving:'||l_proc, 45);
1213: exception
1214: when no_data_found then
1215: p_result_code := c_not_found;
1216: when others then
1243: l_hist_data ghr_pa_history%rowtype;
1244: l_proc varchar2(30):='fetch_asgei';
1245:
1246: Begin
1247: hr_utility.set_location('Entering:'|| l_proc, 5);
1248: p_result_code := null;
1249: if ( p_rowid is not null ) then
1250: hr_utility.set_location( l_proc, 10);
1251: open per_asgei_rowid_cursor;
1246: Begin
1247: hr_utility.set_location('Entering:'|| l_proc, 5);
1248: p_result_code := null;
1249: if ( p_rowid is not null ) then
1250: hr_utility.set_location( l_proc, 10);
1251: open per_asgei_rowid_cursor;
1252: fetch per_asgei_rowid_cursor into p_asgei_data;
1253: if ( per_asgei_rowid_cursor%NOTFOUND ) then
1254: p_result_code := c_not_found;
1253: if ( per_asgei_rowid_cursor%NOTFOUND ) then
1254: p_result_code := c_not_found;
1255: end if;
1256: close per_asgei_rowid_cursor;
1257: hr_utility.set_location( l_proc, 15);
1258: else
1259: Fetch_hist_data(
1260: p_table_name => ghr_history_api.g_asgnei_table,
1261: p_information1 => p_assignment_extra_info_id,
1311: l_hist_data ghr_pa_history%rowtype;
1312: l_proc varchar2(30):='fetch_positionei';
1313:
1314: Begin
1315: hr_utility.set_location('Entering:'|| l_proc, 5);
1316: p_result_code := null;
1317: if ( p_rowid is not null ) then
1318: hr_utility.set_location( l_proc, 10);
1319: open per_posei_rowid_cursor;
1314: Begin
1315: hr_utility.set_location('Entering:'|| l_proc, 5);
1316: p_result_code := null;
1317: if ( p_rowid is not null ) then
1318: hr_utility.set_location( l_proc, 10);
1319: open per_posei_rowid_cursor;
1320: fetch per_posei_rowid_cursor into p_posei_data;
1321: if ( per_posei_rowid_cursor%NOTFOUND ) then
1322: p_result_code := c_not_found;
1321: if ( per_posei_rowid_cursor%NOTFOUND ) then
1322: p_result_code := c_not_found;
1323: end if;
1324: close per_posei_rowid_cursor;
1325: hr_utility.set_location( l_proc, 15);
1326: else
1327: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
1328: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
1329: hr_utility.set_location( l_proc || 'position_extra_info_id: ' || p_position_extra_info_id, 215);
1323: end if;
1324: close per_posei_rowid_cursor;
1325: hr_utility.set_location( l_proc, 15);
1326: else
1327: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
1328: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
1329: hr_utility.set_location( l_proc || 'position_extra_info_id: ' || p_position_extra_info_id, 215);
1330:
1331: Fetch_hist_data(
1324: close per_posei_rowid_cursor;
1325: hr_utility.set_location( l_proc, 15);
1326: else
1327: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
1328: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
1329: hr_utility.set_location( l_proc || 'position_extra_info_id: ' || p_position_extra_info_id, 215);
1330:
1331: Fetch_hist_data(
1332: p_table_name => ghr_history_api.g_posnei_table,
1325: hr_utility.set_location( l_proc, 15);
1326: else
1327: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
1328: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
1329: hr_utility.set_location( l_proc || 'position_extra_info_id: ' || p_position_extra_info_id, 215);
1330:
1331: Fetch_hist_data(
1332: p_table_name => ghr_history_api.g_posnei_table,
1333: p_information1 => p_position_extra_info_id,
1339: p_result_code => l_result_code);
1340:
1341: p_result_code := l_result_code;
1342: if nvl(l_result_code, 'found') <> c_not_found then
1343: hr_utility.set_location (' NOT FOUND ' || l_result_code || l_proc, 90);
1344: -- Bug # 7646662 to get the effective date of the history record
1345: g_cascad_eff_date := l_hist_data.effective_date;
1346: ghr_history_conv_rg.conv_to_positionei_rg(
1347: p_history_data => l_hist_data,
1354: end loop;
1355: end if;
1356: end if;
1357: end if;
1358: hr_utility.set_location('poei_info5 ' || l_posei_data.poei_information5,1);
1359: hr_utility.set_location ('Leaving : ' || l_proc, 100);
1360:
1361: End fetch_positionei;
1362: --
1355: end if;
1356: end if;
1357: end if;
1358: hr_utility.set_location('poei_info5 ' || l_posei_data.poei_information5,1);
1359: hr_utility.set_location ('Leaving : ' || l_proc, 100);
1360:
1361: End fetch_positionei;
1362: --
1363: -- Procedure fetch_assignment fetches the last record from per_assignment or ghr_assignments_h_v
1384: select *
1385: from per_all_assignments_f
1386: where rowid = p_rowid;
1387: Begin
1388: hr_utility.set_location('Entering:'|| l_proc, 5);
1389: p_result_code := null;
1390: if ( p_rowid is not null ) then
1391: /* This part of the procedure is used to fetch the exact row
1392: which will be the post-update record. So if the procedure was
1392: which will be the post-update record. So if the procedure was
1393: passed with p_row_id parameter it'll always return the
1394: post-update record.
1395: */
1396: hr_utility.set_location( l_proc, 10);
1397: open per_assignment_f_rowid_cursor;
1398: fetch per_assignment_f_rowid_cursor into p_assignment_data;
1399: if ( per_assignment_f_rowid_cursor%NOTFOUND ) then
1400: p_result_code := c_not_found;
1399: if ( per_assignment_f_rowid_cursor%NOTFOUND ) then
1400: p_result_code := c_not_found;
1401: end if;
1402: close per_assignment_f_rowid_cursor;
1403: hr_utility.set_location( l_proc, 15);
1404: elsif ( p_pa_history_id is null and
1405: p_altered_pa_request_id is null and
1406: p_noa_id_corrected is null ) then
1407: /* This part of the procedure will fetch the Pre-record values
1410: will return the post-update record. The session variable
1411: pre-update-record will hold the values which can be used for
1412: pre-record values
1413: */
1414: hr_utility.set_location( l_proc, 20);
1415: open per_assignment_f_cursor;
1416: fetch per_assignment_f_cursor into p_assignment_data;
1417: if ( per_assignment_f_cursor%NOTFOUND ) then
1418: p_result_code := c_not_found;
1417: if ( per_assignment_f_cursor%NOTFOUND ) then
1418: p_result_code := c_not_found;
1419: end if;
1420: close per_assignment_f_cursor;
1421: hr_utility.set_location( l_proc, 25);
1422: else
1423: Fetch_hist_data(
1424: p_table_name => ghr_history_api.g_asgn_table,
1425: p_information1 => p_assignment_id,
1438: p_assignment_data := l_assignment_data;
1439: end if;
1440:
1441: end if;
1442: hr_utility.set_location(' Leaving:'||l_proc, 45);
1443: exception
1444: when no_data_found then
1445: p_result_code := c_not_found;
1446: when OTHERS then
1472: select *
1473: from pay_element_entries_f
1474: where rowid = p_rowid;
1475: Begin
1476: hr_utility.set_location('Entering:'|| l_proc, 5);
1477: p_result_code := null;
1478: if ( p_rowid is not null ) then
1479: /* This part of the procedure is used to fetch the exact row
1480: which will be the post-update record. So if the procedure was
1480: which will be the post-update record. So if the procedure was
1481: passed with p_row_id parameter it'll always return the
1482: post-update record.
1483: */
1484: hr_utility.set_location( l_proc, 10);
1485: open pay_e_entry_f_rowid_cursor;
1486: fetch pay_e_entry_f_rowid_cursor into p_element_entry_data;
1487: if ( pay_e_entry_f_rowid_cursor%NOTFOUND ) then
1488: p_result_code := c_not_found;
1487: if ( pay_e_entry_f_rowid_cursor%NOTFOUND ) then
1488: p_result_code := c_not_found;
1489: end if;
1490: close pay_e_entry_f_rowid_cursor;
1491: hr_utility.set_location( l_proc, 15);
1492: elsif ( p_pa_history_id is null and
1493: p_altered_pa_request_id is null and
1494: p_noa_id_corrected is null ) then
1495: /* This part of the procedure will fetch the Pre-record values
1498: will return the post-update record. The session variable
1499: pre-update-record will hold the values which can be used for
1500: pre-record values
1501: */
1502: hr_utility.set_location( l_proc, 20);
1503: open pay_e_entry_f_cursor;
1504: fetch pay_e_entry_f_cursor into p_element_entry_data;
1505: if ( pay_e_entry_f_cursor%NOTFOUND ) then
1506: p_result_code := c_not_found;
1506: p_result_code := c_not_found;
1507: end if;
1508: close pay_e_entry_f_cursor;
1509: else
1510: hr_utility.set_location( l_proc, 25);
1511: Fetch_hist_data(
1512: p_table_name => ghr_history_api.g_eleent_table,
1513: p_information1 => p_element_entry_id,
1514: p_date_effective => p_date_effective,
1525: p_element_entries_data => l_element_entry_data);
1526: p_element_entry_data := l_element_entry_data;
1527: end if;
1528: end if;
1529: hr_utility.set_location(' Leaving:'||l_proc, 45);
1530: exception
1531: when no_data_found then
1532: p_result_code := c_not_found;
1533: when OTHERS then
1559: select object_version_number
1560: from per_people_extra_info
1561: where person_extra_info_id = p_person_extra_info_id;
1562: Begin
1563: hr_utility.set_location('Entering:'|| l_proc, 5);
1564: hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1560: from per_people_extra_info
1561: where person_extra_info_id = p_person_extra_info_id;
1562: Begin
1563: hr_utility.set_location('Entering:'|| l_proc, 5);
1564: hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1561: where person_extra_info_id = p_person_extra_info_id;
1562: Begin
1563: hr_utility.set_location('Entering:'|| l_proc, 5);
1564: hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1562: Begin
1563: hr_utility.set_location('Entering:'|| l_proc, 5);
1564: hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570: hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1563: hr_utility.set_location('Entering:'|| l_proc, 5);
1564: hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570: hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1571: hr_utility.set_location(' p_person_extra_info_id:'||p_person_extra_info_id || l_proc, 52);
1564: hr_utility.set_location('extra info id:'|| to_char(p_person_extra_info_id) || l_proc, 6);
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570: hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1571: hr_utility.set_location(' p_person_extra_info_id:'||p_person_extra_info_id || l_proc, 52);
1572: p_result_code := null;
1565: hr_utility.set_location('date_effective:'|| to_char(p_date_effective) || l_proc, 7);
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570: hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1571: hr_utility.set_location(' p_person_extra_info_id:'||p_person_extra_info_id || l_proc, 52);
1572: p_result_code := null;
1573: if ( p_rowid is not null ) then
1566: hr_utility.set_location('noa_id_corrected:'|| to_char(p_noa_id_corrected) || l_proc, 8);
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570: hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1571: hr_utility.set_location(' p_person_extra_info_id:'||p_person_extra_info_id || l_proc, 52);
1572: p_result_code := null;
1573: if ( p_rowid is not null ) then
1574: /* This part of the procedure is used to fetch the exact row
1567: hr_utility.set_location('altered_pa_request_id:'|| to_char(p_altered_pa_request_id) || l_proc, 9);
1568: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 48);
1569: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 49);
1570: hr_utility.set_location(' p_pa_history_id:'||p_pa_history_id || l_proc, 51);
1571: hr_utility.set_location(' p_person_extra_info_id:'||p_person_extra_info_id || l_proc, 52);
1572: p_result_code := null;
1573: if ( p_rowid is not null ) then
1574: /* This part of the procedure is used to fetch the exact row
1575: which will be the post-update record. So if the procedure was
1575: which will be the post-update record. So if the procedure was
1576: passed with p_row_id parameter it'll always return the
1577: post-update record.
1578: */
1579: hr_utility.set_location( l_proc, 10);
1580: open per_peopleei_rowid_cursor;
1581: fetch per_peopleei_rowid_cursor into p_peopleei_data;
1582: if ( per_peopleei_rowid_cursor%NOTFOUND ) then
1583: p_result_code := c_not_found;
1582: if ( per_peopleei_rowid_cursor%NOTFOUND ) then
1583: p_result_code := c_not_found;
1584: end if;
1585: close per_peopleei_rowid_cursor;
1586: hr_utility.set_location( l_proc, 15);
1587: else
1588: Fetch_hist_data(
1589: p_table_name => ghr_history_api.g_peopei_table,
1590: p_information1 => p_person_extra_info_id,
1596: p_result_code => l_result_code);
1597:
1598: p_result_code := l_result_code;
1599: if nvl(l_result_code, 'found') <> c_not_found then
1600: hr_utility.set_location (' NOT FOUND ' || l_result_code || l_proc, 90);
1601: ghr_history_conv_rg.conv_to_peopleei_rg(
1602: p_history_data => l_hist_data,
1603: p_people_ei_data => l_peopleei_data);
1604: p_peopleei_data := l_peopleei_data;
1610: end if;
1611: end if;
1612: end if;
1613:
1614: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 46);
1615: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 47);
1616: hr_utility.set_location(' Leaving:'||l_proc, 45);
1617: exception
1618: when no_data_found then
1611: end if;
1612: end if;
1613:
1614: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 46);
1615: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 47);
1616: hr_utility.set_location(' Leaving:'||l_proc, 45);
1617: exception
1618: when no_data_found then
1619: p_result_code := c_not_found;
1612: end if;
1613:
1614: hr_utility.set_location(' Information11:'|| p_peopleei_data.pei_information11 || l_proc, 46);
1615: hr_utility.set_location(' Information5:'||p_peopleei_data.pei_information5 || l_proc, 47);
1616: hr_utility.set_location(' Leaving:'||l_proc, 45);
1617: exception
1618: when no_data_found then
1619: p_result_code := c_not_found;
1620: when OTHERS then
1656: and gph.information5= p_information_type
1657: and gph.pa_request_id is not null));
1658: --End Bug 11680558
1659: Begin
1660: hr_utility.set_location('Entering ' || l_proc,5);
1661: --Begin Bug 11680558
1662: IF p_information_type = 'GHR_US_ASG_DET_INFO' THEN
1663: For l_det_extra_info_id in c_det_extra_info_id loop
1664: l_extra_info_id := l_det_extra_info_id.assignment_extra_info_id;
1669: l_extra_info_id := extra_info.assignment_extra_info_id;
1670: End loop;
1671: END IF;
1672: If l_extra_info_id is not null then
1673: hr_utility.set_location(l_proc,10);
1674: ghr_history_api.get_g_session_var(l_session);
1675: ghr_history_fetch.fetch_asgei ( p_assignment_extra_info_id => l_extra_info_id,
1676: p_date_effective => p_date_effective,
1677: p_altered_pa_request_id => l_session.altered_pa_request_id,
1681: p_get_ovn_flag => 'Y',
1682: p_result_code => l_result
1683: );
1684: End if;
1685: hr_utility.set_location('Leaving ' ||l_proc,20);
1686: End fetch_asgei;
1687:
1688: Procedure fetch_peopleei(p_person_id in number,
1689: p_information_type in varchar2,
1703: from per_people_extra_info pei
1704: where pei.person_id = p_person_id
1705: and pei.information_type = p_information_type;
1706: Begin
1707: hr_utility.set_location('Entering ' || l_proc,5);
1708: For extra_info in c_extra_info_id loop
1709: l_extra_info_id := extra_info.person_extra_info_id;
1710: End loop;
1711: If l_extra_info_id is not null then
1708: For extra_info in c_extra_info_id loop
1709: l_extra_info_id := extra_info.person_extra_info_id;
1710: End loop;
1711: If l_extra_info_id is not null then
1712: hr_utility.set_location(l_proc,10);
1713: ghr_history_api.get_g_session_var(l_session);
1714: ghr_history_fetch.fetch_peopleei ( p_person_extra_info_id => l_extra_info_id,
1715: p_date_effective => p_date_effective,
1716: p_altered_pa_request_id => l_session.altered_pa_request_id,
1720: p_get_ovn_flag => 'Y',
1721: p_result_code => l_result
1722: );
1723: End if;
1724: hr_utility.set_location('Leaving ' ||l_proc,20);
1725: End fetch_peopleei;
1726:
1727: Procedure fetch_positionei(p_position_id in number,
1728: p_information_type in varchar2,
1746: and ghr_pos.information_type = p_information_type;
1747:
1748: Begin
1749: g_info_type := p_information_type;
1750: hr_utility.set_location('Entering ' || l_proc,5);
1751: hr_utility.set_location('FETCH: Position_id ' || p_position_id ||l_proc,6);
1752: hr_utility.set_location('FETCH: information_type ' || p_information_type || l_proc,7);
1753: for extra_info in c_extra_info_id loop
1754: l_extra_info_id := extra_info.position_extra_info_id;
1747:
1748: Begin
1749: g_info_type := p_information_type;
1750: hr_utility.set_location('Entering ' || l_proc,5);
1751: hr_utility.set_location('FETCH: Position_id ' || p_position_id ||l_proc,6);
1752: hr_utility.set_location('FETCH: information_type ' || p_information_type || l_proc,7);
1753: for extra_info in c_extra_info_id loop
1754: l_extra_info_id := extra_info.position_extra_info_id;
1755: end loop;
1748: Begin
1749: g_info_type := p_information_type;
1750: hr_utility.set_location('Entering ' || l_proc,5);
1751: hr_utility.set_location('FETCH: Position_id ' || p_position_id ||l_proc,6);
1752: hr_utility.set_location('FETCH: information_type ' || p_information_type || l_proc,7);
1753: for extra_info in c_extra_info_id loop
1754: l_extra_info_id := extra_info.position_extra_info_id;
1755: end loop;
1756: If l_extra_info_id is not null then
1753: for extra_info in c_extra_info_id loop
1754: l_extra_info_id := extra_info.position_extra_info_id;
1755: end loop;
1756: If l_extra_info_id is not null then
1757: hr_utility.set_location(l_proc,10);
1758: ghr_history_api.get_g_session_var(l_session);
1759: ghr_history_fetch.fetch_positionei(p_position_extra_info_id => l_extra_info_id,
1760: p_date_effective => p_date_effective,
1761: p_altered_pa_request_id => l_session.altered_pa_request_id,
1770: --BUG # 8406984 Modified to assign NULL to g_info_type after fetching positionei
1771: -- as g_info_type is used in fetch_for_histid if it is not reassigned back to NULL
1772: -- causing issues.
1773: g_info_type := NULL;
1774: hr_utility.set_location('Leaving ' ||l_proc,20);
1775: End fetch_positionei;
1776: -- The following procedure gets the date_effective row from the
1777: -- ghr_pa_history for the element_entry_values table.
1778: --VSM (What about this procedure) ??
1785: -- l_result Boolean;
1786: l_proc varchar2(30) := 'get_date_eff_eleevl';
1787: l_element_entval_data pay_element_entry_values_f%rowtype;
1788: BEGIN
1789: hr_utility.set_location( l_proc, 20);
1790: fetch_for_date_eff(
1791: p_table_name => ghr_history_api.g_eleevl_table,
1792: p_information1 => p_element_entry_value_id,
1793: p_date_effective => p_date_effective,
1793: p_date_effective => p_date_effective,
1794: p_hist_data => l_hist_data,
1795: p_result_code => p_result_code);
1796: p_pa_history_id := l_hist_data.pa_history_id;
1797: hr_utility.set_location( l_proc, 75);
1798: ghr_history_conv_rg.conv_to_element_entval_rg(
1799: p_history_data => l_hist_data,
1800: p_element_entval_data => l_element_entval_data );
1801: p_element_entry_data := l_element_entval_data;
1798: ghr_history_conv_rg.conv_to_element_entval_rg(
1799: p_history_data => l_hist_data,
1800: p_element_entval_data => l_element_entval_data );
1801: p_element_entry_data := l_element_entval_data;
1802: hr_utility.set_location( l_proc, 25);
1803: END get_date_eff_eleevl;
1804:
1805: Procedure fetch_element_entry_value (
1806: p_element_entry_value_id in number default null,
1825: from pay_element_entry_values_f
1826: where rowid = p_rowid;
1827:
1828: Begin
1829: hr_utility.set_location('Entering:'|| l_proc, 5);
1830: p_result_code := null;
1831: if ( p_rowid is not null ) then
1832: /* This part of the procedure is used to fetch the exact row
1833: which will be the post-update record. So if the procedure was
1833: which will be the post-update record. So if the procedure was
1834: passed with p_row_id parameter it'll always return the
1835: post-update record.
1836: */
1837: hr_utility.set_location( l_proc, 10);
1838: open pay_e_entry_val_f_rowid;
1839: fetch pay_e_entry_val_f_rowid into p_element_entry_data;
1840: if ( pay_e_entry_val_f_rowid%NOTFOUND ) then
1841: p_result_code := c_not_found;
1840: if ( pay_e_entry_val_f_rowid%NOTFOUND ) then
1841: p_result_code := c_not_found;
1842: end if;
1843: close pay_e_entry_val_f_rowid;
1844: hr_utility.set_location( l_proc, 15);
1845: elsif ( p_pa_history_id is null and
1846: p_altered_pa_request_id is null and
1847: p_noa_id_corrected is null ) then
1848: /* This part of the procedure will fetch the Pre-record values
1851: will return the post-update record. The session variable
1852: pre-update-record will hold the values which can be used for
1853: pre-record values
1854: */
1855: hr_utility.set_location( l_proc, 20);
1856: open pay_e_entry_value_f_cursor;
1857: fetch pay_e_entry_value_f_cursor into p_element_entry_data;
1858: if ( pay_e_entry_value_f_cursor%NOTFOUND ) then
1859: p_result_code := c_not_found;
1859: p_result_code := c_not_found;
1860: end if;
1861: close pay_e_entry_value_f_cursor;
1862: else
1863: hr_utility.set_location( l_proc, 25);
1864: Fetch_hist_data(
1865: p_table_name => ghr_history_api.g_eleevl_table,
1866: p_information1 => p_element_entry_value_id,
1867: p_date_effective => p_date_effective,
1878: p_element_entval_data => l_element_entval_data);
1879: p_element_entry_data := l_element_entval_data;
1880: end if;
1881: end if;
1882: hr_utility.set_location(' Leaving:'||l_proc, 45);
1883: exception
1884: when no_data_found then
1885: p_result_code := c_not_found;
1886: when OTHERS then
1949: l_new_element_name VARCHAR2(80);
1950: --
1951:
1952: begin
1953: hr_utility.set_location('Entering ' || l_proc,5);
1954: -- Initialization
1955: ghr_history_api.get_g_session_var(l_session);
1956: ll_effective_date := p_date_effective;
1957: -- Pick the business group id and also pay basis for later use
1971:
1972:
1973: IF (p_element_name = 'Basic Salary Rate'
1974: and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
1975: hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- ' || l_proc, 5);
1976: l_new_element_name :=
1977: pqp_fedhr_uspay_int_utils.return_new_element_name(
1978: p_assignment_id => p_assignment_id,
1979: p_business_group_id => ll_bg_id,
1979: p_business_group_id => ll_bg_id,
1980: p_effective_date => ll_effective_date);
1981: ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
1982: or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
1983: hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- ' || l_proc, 5);
1984: l_new_element_name :=
1985: pqp_fedhr_uspay_int_utils.return_new_element_name(
1986: p_fedhr_element_name => p_element_name,
1987: p_business_group_id => ll_bg_id,
2016: ll_bg_id)
2017: loop
2018: l_element_entry_value_id := fetch_elv_id.element_entry_value_id;
2019: If l_element_entry_value_id is not null then
2020: hr_utility.set_location(l_proc || 'inside cursor '|| to_char(l_element_entry_value_id) ,6);
2021: fetch_element_entry_value(
2022: p_element_entry_value_id => l_element_entry_value_id,
2023: p_date_effective => p_date_effective,
2024: p_altered_pa_request_id => l_session.altered_pa_request_id,
2027: p_element_entry_data => l_element_entry_data,
2028: p_result_code => l_result
2029: );
2030: Else
2031: hr_utility.set_location(l_proc || 'before exit',7);
2032: exit;
2033: End if;
2034: If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2035: hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2031: hr_utility.set_location(l_proc || 'before exit',7);
2032: exit;
2033: End if;
2034: If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2035: hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2036: p_screen_entry_value := l_element_entry_data.screen_entry_value;
2037: hr_utility.set_location(l_proc || 'Value' || l_element_entry_data.screen_entry_value,8);
2038: exit;
2039: End if;
2033: End if;
2034: If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2035: hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2036: p_screen_entry_value := l_element_entry_data.screen_entry_value;
2037: hr_utility.set_location(l_proc || 'Value' || l_element_entry_data.screen_entry_value,8);
2038: exit;
2039: End if;
2040: End loop;
2041: End if;
2164: ll_pay_basis VARCHAR2(80);
2165: l_new_element_name VARCHAR2(80);
2166: --
2167: begin
2168: hr_utility.set_location('Entering:'||l_proc, 1);
2169: ghr_history_api.get_g_session_var(l_session);
2170: -- Pick pay basis from PAR
2171:
2172: For BG_rec in Cur_BG(p_assignment_id,p_effective_date)
2184: ----
2185:
2186: IF (p_element_name = 'Basic Salary Rate'
2187: and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
2188: hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- '||l_proc, 1);
2189: l_new_element_name :=
2190: pqp_fedhr_uspay_int_utils.return_new_element_name(
2191: p_assignment_id => p_assignment_id,
2192: p_business_group_id => ll_bg_id,
2192: p_business_group_id => ll_bg_id,
2193: p_effective_date => ll_effective_date);
2194: ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
2195: or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN
2196: hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- '||l_proc, 1);
2197: l_new_element_name :=
2198: pqp_fedhr_uspay_int_utils.return_new_element_name(
2199: p_fedhr_element_name => p_element_name,
2200: p_business_group_id => ll_bg_id,
2204: END IF;
2205:
2206: --
2207: --
2208: hr_utility.trace('NEW ELE NAME- ghhisfet.pkb is '||l_new_element_name);
2209: -- commenting this and using the same above
2210: -- ghr_history_api.get_g_session_var(l_session);
2211: l_input_value_id := Null;
2212: l_element_entry_id := Null;
2210: -- ghr_history_api.get_g_session_var(l_session);
2211: l_input_value_id := Null;
2212: l_element_entry_id := Null;
2213: --
2214: hr_utility.set_location('Element - CORRECTION ' ,1);
2215: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2211: l_input_value_id := Null;
2212: l_element_entry_id := Null;
2213: --
2214: hr_utility.set_location('Element - CORRECTION ' ,1);
2215: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2212: l_element_entry_id := Null;
2213: --
2214: hr_utility.set_location('Element - CORRECTION ' ,1);
2215: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2220: -- hr_utility.set_location('Eff. Date ' || p_effective_date,5);
2213: --
2214: hr_utility.set_location('Element - CORRECTION ' ,1);
2215: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2220: -- hr_utility.set_location('Eff. Date ' || p_effective_date,5);
2221:
2214: hr_utility.set_location('Element - CORRECTION ' ,1);
2215: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2220: -- hr_utility.set_location('Eff. Date ' || p_effective_date,5);
2221:
2222: for ele_type in c_ele_type(l_new_element_name
2215: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2220: -- hr_utility.set_location('Eff. Date ' || p_effective_date,5);
2221:
2222: for ele_type in c_ele_type(l_new_element_name
2223: ,ll_bg_id)
2216: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2217: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2218: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2219: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2220: -- hr_utility.set_location('Eff. Date ' || p_effective_date,5);
2221:
2222: for ele_type in c_ele_type(l_new_element_name
2223: ,ll_bg_id)
2224: loop
2221:
2222: for ele_type in c_ele_type(l_new_element_name
2223: ,ll_bg_id)
2224: loop
2225: hr_utility.set_location(l_proc ,20);
2226: l_element_type_id := ele_type.element_type_id;
2227: l_processing_type := ele_type.processing_type;
2228: hr_utility.set_location('proc type ' || l_processing_type,2);
2229: end loop;
2224: loop
2225: hr_utility.set_location(l_proc ,20);
2226: l_element_type_id := ele_type.element_type_id;
2227: l_processing_type := ele_type.processing_type;
2228: hr_utility.set_location('proc type ' || l_processing_type,2);
2229: end loop;
2230:
2231: If nvl(l_processing_type,hr_api.g_varchar2) = 'R' then
2232: For fetch_elv_id in fetch_element_entry_value_id(
2234: ,ll_bg_id)
2235: loop
2236: l_element_entry_value_id := fetch_elv_id.element_entry_value_id;
2237: If l_element_entry_value_id is not null then
2238: hr_utility.set_location(l_proc || 'inside cursor '|| to_char(l_element_entry_value_id) ,6);
2239:
2240: fetch_element_entry_value(p_element_entry_value_id => l_element_entry_value_id,
2241: p_date_effective => p_effective_date,
2242: p_altered_pa_request_id => l_session.altered_pa_request_id,
2252: p_element_entry_id := l_element_entry_id;
2253: p_value := l_element_entry_data.screen_entry_value;
2254:
2255: Else
2256: hr_utility.set_location(l_proc || 'before exit',7);
2257: exit;
2258: End if;
2259: If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2260: hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2256: hr_utility.set_location(l_proc || 'before exit',7);
2257: exit;
2258: End if;
2259: If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2260: hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2261: p_value := l_element_entry_data.screen_entry_value;
2262: hr_utility.set_location(l_proc || 'Value' || l_element_entry_data.screen_entry_value,8);
2263: exit;
2264: End if;
2258: End if;
2259: If nvl(lower(l_result),hr_api.g_varchar2) <> c_not_found then
2260: hr_utility.set_location(l_proc || 'l_result' || l_result,8);
2261: p_value := l_element_entry_data.screen_entry_value;
2262: hr_utility.set_location(l_proc || 'Value' || l_element_entry_data.screen_entry_value,8);
2263: exit;
2264: End if;
2265: End loop;
2266:
2264: End if;
2265: End loop;
2266:
2267: Elsif nvl(l_processing_type,hr_api.g_varchar2) = 'N' then
2268: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2269: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2265: End loop;
2266:
2267: Elsif nvl(l_processing_type,hr_api.g_varchar2) = 'N' then
2268: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2269: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273: hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2266:
2267: Elsif nvl(l_processing_type,hr_api.g_varchar2) = 'N' then
2268: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2269: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273: hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2274: hr_utility.set_location('Element type id ' || to_char(l_element_type_id),1);
2267: Elsif nvl(l_processing_type,hr_api.g_varchar2) = 'N' then
2268: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2269: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273: hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2274: hr_utility.set_location('Element type id ' || to_char(l_element_type_id),1);
2275:
2268: hr_utility.set_location('NOA ID COR ' || to_char(l_session.noa_id_correct),1);
2269: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273: hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2274: hr_utility.set_location('Element type id ' || to_char(l_element_type_id),1);
2275:
2276: /*
2269: hr_utility.set_location('PAR ID COR ' || to_char(l_session.altered_pa_request_id),1);
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273: hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2274: hr_utility.set_location('Element type id ' || to_char(l_element_type_id),1);
2275:
2276: /*
2277: If it is a correction action, then we have to read the
2270: hr_utility.set_location('Element Name ' || l_new_element_name,2);
2271: hr_utility.set_location('IV Name ' || p_input_value_name,3);
2272: hr_utility.set_location('Asg id ' || to_char(p_assignment_id),4);
2273: hr_utility.set_location('Eff. Date ' || (p_effective_date),5);
2274: hr_utility.set_location('Element type id ' || to_char(l_element_type_id),1);
2275:
2276: /*
2277: If it is a correction action, then we have to read the
2278: element values from the history table to get the correct data
2284: ,p_input_value_name
2285: ,p_assignment_id
2286: ,p_effective_date
2287: ,ll_bg_id) loop
2288: hr_utility.set_location('Element In.Val. Id ' || ele_info_cor.input_value_id,3);
2289: hr_utility.set_location('Element entry Id ' || ele_info_cor.element_entry_id,3);
2290: l_input_value_id := ele_info_cor.input_value_id;
2291: p_element_link_id := ele_info_cor.element_link_id;
2292: l_element_entry_id := ele_info_cor.element_entry_id;
2285: ,p_assignment_id
2286: ,p_effective_date
2287: ,ll_bg_id) loop
2288: hr_utility.set_location('Element In.Val. Id ' || ele_info_cor.input_value_id,3);
2289: hr_utility.set_location('Element entry Id ' || ele_info_cor.element_entry_id,3);
2290: l_input_value_id := ele_info_cor.input_value_id;
2291: p_element_link_id := ele_info_cor.element_link_id;
2292: l_element_entry_id := ele_info_cor.element_entry_id;
2293: l_pa_request_id := ele_info_cor.pa_request_id;
2304: End if;
2305:
2306: if l_element_entry_id is not null then
2307: for ele_ovn in c_ele_ovn loop
2308: hr_utility.set_location(l_proc || 'retrieve_element_cor ',1);
2309: p_object_version_number := ele_ovn.object_version_number;
2310: end loop;
2311: if l_input_value_id is not null then -- l_input_value_id would anyway have a value only for Non Rec. Elements.
2312: hr_utility.set_location(l_proc || 'get eev',2);
2308: hr_utility.set_location(l_proc || 'retrieve_element_cor ',1);
2309: p_object_version_number := ele_ovn.object_version_number;
2310: end loop;
2311: if l_input_value_id is not null then -- l_input_value_id would anyway have a value only for Non Rec. Elements.
2312: hr_utility.set_location(l_proc || 'get eev',2);
2313: for screen_value in c_gev loop
2314: p_value := screen_value.screen_entry_value;
2315: hr_utility.set_location('Scr. value is ' || screen_value.screen_entry_value,1);
2316: end loop;
2311: if l_input_value_id is not null then -- l_input_value_id would anyway have a value only for Non Rec. Elements.
2312: hr_utility.set_location(l_proc || 'get eev',2);
2313: for screen_value in c_gev loop
2314: p_value := screen_value.screen_entry_value;
2315: hr_utility.set_location('Scr. value is ' || screen_value.screen_entry_value,1);
2316: end loop;
2317: end if;
2318: end if;
2319:
2339: from per_addresses
2340: where rowid = p_rowid;
2341:
2342: begin
2343: hr_utility.set_location('Entering:'|| l_proc, 5);
2344: p_result_code := null;
2345: if ( p_rowid is not null ) then
2346: /* This part of the procedure is used to fetch the exact row
2347: which will be the post-update record. So if the procedure was
2347: which will be the post-update record. So if the procedure was
2348: passed with p_row_id parameter it'll always return the
2349: post-update record.
2350: */
2351: hr_utility.set_location( l_proc, 10);
2352: open per_address_rowid_cursor;
2353: fetch per_address_rowid_cursor into p_address_data;
2354: if ( per_address_rowid_cursor%NOTFOUND ) then
2355: p_result_code := c_not_found;
2354: if ( per_address_rowid_cursor%NOTFOUND ) then
2355: p_result_code := c_not_found;
2356: end if;
2357: close per_address_rowid_cursor;
2358: hr_utility.set_location( l_proc, 15);
2359: else
2360:
2361: Fetch_hist_data(
2362: p_table_name => ghr_history_api.g_addres_table,
2375: p_addresses_data => l_address_data);
2376: p_address_data := l_address_data;
2377: end if;
2378: end if;
2379: hr_utility.set_location(' Leaving:'||l_proc, 45);
2380: exception
2381: when no_data_found then
2382: p_result_code := c_not_found;
2383: when OTHERS then
2404: select *
2405: from per_person_analyses
2406: where rowid = p_rowid;
2407: Begin
2408: hr_utility.set_location('Entering:'|| l_proc, 5);
2409: p_result_code := null;
2410: if ( p_rowid is not null ) then
2411: /* This part of the procedure is used to fetch the exact row
2412: which will be the post-update record. So if the procedure was
2412: which will be the post-update record. So if the procedure was
2413: passed with p_row_id parameter it'll always return the
2414: post-update record.
2415: */
2416: hr_utility.set_location( l_proc, 10);
2417: open per_peranalyses_rowid_cursor;
2418: fetch per_peranalyses_rowid_cursor into p_peranalyses_data;
2419: if ( per_peranalyses_rowid_cursor%NOTFOUND ) then
2420: p_result_code := c_not_found;
2419: if ( per_peranalyses_rowid_cursor%NOTFOUND ) then
2420: p_result_code := c_not_found;
2421: end if;
2422: close per_peranalyses_rowid_cursor;
2423: hr_utility.set_location( l_proc, 15);
2424: else
2425:
2426: Fetch_hist_data(
2427: p_table_name => ghr_history_api.g_perana_table,
2441: p_peranalyses_data := l_peranalyses_data;
2442: end if;
2443:
2444: end if;
2445: hr_utility.set_location(' Leaving:'||l_proc, 45);
2446: exception
2447: when no_data_found then
2448: p_result_code := c_not_found;
2449: when OTHERS then
2523: r_poi per_position_extra_info%rowtype;
2524: l_proc varchar2(30):='fetch_positionei (1)';
2525: l_result_code varchar2(30);
2526: Begin
2527: hr_utility.set_location('Entering :' || l_proc, 10);
2528: ghr_history_fetch.fetch_positionei(
2529: p_position_extra_info_id => p_position_extra_info_id
2530: , p_date_effective => p_date_effective
2531: , p_posei_data => r_poi
2529: p_position_extra_info_id => p_position_extra_info_id
2530: , p_date_effective => p_date_effective
2531: , p_posei_data => r_poi
2532: , p_result_code => p_result_code);
2533: hr_utility.set_location(l_proc, 20);
2534: p_position_extra_info_id := r_poi.position_extra_info_id;
2535: p_position_id := r_poi.position_id;
2536: p_information_type := r_poi.information_type;
2537: p_request_id := r_poi.request_id;
2595: p_last_updated_by := r_poi.last_updated_by;
2596: p_last_update_login := r_poi.last_update_login;
2597: p_created_by := r_poi.created_by;
2598: p_creation_date := r_poi.creation_date;
2599: hr_utility.set_location('Leaving :' || l_proc, 100);
2600: end fetch_positionei;
2601:
2602: --
2603: -- Procedure fetches the last record from per_positions or
2645: l_hist_data ghr_pa_history%rowtype;
2646: l_proc varchar2(30):='fetch_positionei';
2647:
2648: Begin
2649: hr_utility.set_location('Entering:'|| l_proc, 5);
2650: p_result_code := null;
2651: if ( p_rowid is not null ) then
2652: hr_utility.set_location( l_proc, 10);
2653: open per_posn_f_rowid_cursor;
2648: Begin
2649: hr_utility.set_location('Entering:'|| l_proc, 5);
2650: p_result_code := null;
2651: if ( p_rowid is not null ) then
2652: hr_utility.set_location( l_proc, 10);
2653: open per_posn_f_rowid_cursor;
2654: fetch per_posn_f_rowid_cursor into p_position_data;
2655: if ( per_posn_f_rowid_cursor%NOTFOUND ) then
2656: p_result_code := c_not_found;
2655: if ( per_posn_f_rowid_cursor%NOTFOUND ) then
2656: p_result_code := c_not_found;
2657: end if;
2658: close per_posn_f_rowid_cursor;
2659: hr_utility.set_location( l_proc, 15);
2660: else
2661: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
2662: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
2663: hr_utility.set_location( l_proc || 'position_id: ' || p_position_id, 215);
2657: end if;
2658: close per_posn_f_rowid_cursor;
2659: hr_utility.set_location( l_proc, 15);
2660: else
2661: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
2662: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
2663: hr_utility.set_location( l_proc || 'position_id: ' || p_position_id, 215);
2664:
2665: Fetch_hist_data(
2658: close per_posn_f_rowid_cursor;
2659: hr_utility.set_location( l_proc, 15);
2660: else
2661: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
2662: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
2663: hr_utility.set_location( l_proc || 'position_id: ' || p_position_id, 215);
2664:
2665: Fetch_hist_data(
2666: p_table_name => ghr_history_api.g_posn_table,
2659: hr_utility.set_location( l_proc, 15);
2660: else
2661: hr_utility.set_location( l_proc || 'altered_pa_request_id: ' || p_altered_pa_request_id, 115);
2662: hr_utility.set_location( l_proc || 'noa_id_corrected: ' || p_noa_id_corrected, 215);
2663: hr_utility.set_location( l_proc || 'position_id: ' || p_position_id, 215);
2664:
2665: Fetch_hist_data(
2666: p_table_name => ghr_history_api.g_posn_table,
2667: p_information1 => p_position_id,
2689: exit;
2690: end loop;
2691: end if;
2692: end if;
2693: hr_utility.set_location ('Leaving : ' || l_proc, 100);
2694:
2695: End fetch_position;
2696:
2697: procedure fetch_peopleei (
2767: r_pei per_people_extra_info%rowtype;
2768: l_proc varchar2(30):='fetch_peopleei (1)';
2769: l_result_code varchar2(30);
2770: Begin
2771: hr_utility.set_location(' Entering : ' || l_proc, 10);
2772: ghr_history_fetch.fetch_peopleei(
2773: p_person_extra_info_id => p_person_extra_info_id
2774: , p_date_effective => p_date_effective
2775: , p_peopleei_data => r_pei
2774: , p_date_effective => p_date_effective
2775: , p_peopleei_data => r_pei
2776: , p_result_code => p_result_code);
2777:
2778: hr_utility.set_location( l_proc, 20);
2779: p_person_extra_info_id := r_pei.person_extra_info_id;
2780: p_person_id := r_pei.person_id;
2781: p_information_type := r_pei.information_type;
2782: p_request_id := r_pei.request_id;
2840: p_last_updated_by := r_pei.last_updated_by;
2841: p_last_update_login := r_pei.last_update_login;
2842: p_created_by := r_pei.created_by;
2843: p_creation_date := r_pei.creation_date;
2844: hr_utility.set_location(' Leaving : ' || l_proc, 100);
2845: end fetch_peopleei ;
2846:
2847: procedure fetch_asgei (
2848: p_assignment_extra_info_id in out nocopy number
2917: r_aei per_assignment_extra_info%rowtype;
2918: l_proc varchar2(30):='fetch_asgei (1)';
2919: l_result_code varchar2(30);
2920: Begin
2921: hr_utility.set_location(' Entering : ' || l_proc, 10);
2922: ghr_history_fetch.fetch_asgei(
2923: p_assignment_extra_info_id => p_assignment_extra_info_id
2924: , p_date_effective => p_date_effective
2925: , p_asgei_data => r_aei
2924: , p_date_effective => p_date_effective
2925: , p_asgei_data => r_aei
2926: , p_result_code => p_result_code);
2927:
2928: hr_utility.set_location( l_proc, 20);
2929: p_assignment_extra_info_id := r_aei.assignment_extra_info_id;
2930: p_assignment_id := r_aei.assignment_id;
2931: p_information_type := r_aei.information_type;
2932: p_request_id := r_aei.request_id;
2990: p_last_updated_by := r_aei.last_updated_by;
2991: p_last_update_login := r_aei.last_update_login;
2992: p_created_by := r_aei.created_by;
2993: p_creation_date := r_aei.creation_date;
2994: hr_utility.set_location(' Leaving : ' || l_proc, 100);
2995: end fetch_asgei ;
2996:
2997:
2998: -- ---------------------------------------------------------------------------
3097: l_id_flex_num := flex_num.id_flex_num;
3098: END LOOP;
3099:
3100: IF l_id_flex_num IS NULL THEN
3101: hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
3102: hr_utility.raise_error;
3103: END IF;
3104:
3105: hr_utility.set_location('got here 1234', 4777);
3098: END LOOP;
3099:
3100: IF l_id_flex_num IS NULL THEN
3101: hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
3102: hr_utility.raise_error;
3103: END IF;
3104:
3105: hr_utility.set_location('got here 1234', 4777);
3106: hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3101: hr_utility.set_message(8301,'GHR_38275_INV_SP_INFO_TYPE');
3102: hr_utility.raise_error;
3103: END IF;
3104:
3105: hr_utility.set_location('got here 1234', 4777);
3106: hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3107: hr_utility.set_location('l_id_flex_num: ' || l_id_flex_num, 4777);
3108: hr_utility.set_location('l_session.noa_id_correct: ' || l_session.noa_id_correct, 4777);
3109: hr_utility.set_location('l_session.altered_pa_request_id: ' || l_session.altered_pa_request_id, 4777);
3102: hr_utility.raise_error;
3103: END IF;
3104:
3105: hr_utility.set_location('got here 1234', 4777);
3106: hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3107: hr_utility.set_location('l_id_flex_num: ' || l_id_flex_num, 4777);
3108: hr_utility.set_location('l_session.noa_id_correct: ' || l_session.noa_id_correct, 4777);
3109: hr_utility.set_location('l_session.altered_pa_request_id: ' || l_session.altered_pa_request_id, 4777);
3110:
3103: END IF;
3104:
3105: hr_utility.set_location('got here 1234', 4777);
3106: hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3107: hr_utility.set_location('l_id_flex_num: ' || l_id_flex_num, 4777);
3108: hr_utility.set_location('l_session.noa_id_correct: ' || l_session.noa_id_correct, 4777);
3109: hr_utility.set_location('l_session.altered_pa_request_id: ' || l_session.altered_pa_request_id, 4777);
3110:
3111: FOR per_analyses IN c_person_analyses LOOP
3104:
3105: hr_utility.set_location('got here 1234', 4777);
3106: hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3107: hr_utility.set_location('l_id_flex_num: ' || l_id_flex_num, 4777);
3108: hr_utility.set_location('l_session.noa_id_correct: ' || l_session.noa_id_correct, 4777);
3109: hr_utility.set_location('l_session.altered_pa_request_id: ' || l_session.altered_pa_request_id, 4777);
3110:
3111: FOR per_analyses IN c_person_analyses LOOP
3112: --Bug 3103339
3105: hr_utility.set_location('got here 1234', 4777);
3106: hr_utility.set_location('p_person_id: ' || p_person_id, 4777);
3107: hr_utility.set_location('l_id_flex_num: ' || l_id_flex_num, 4777);
3108: hr_utility.set_location('l_session.noa_id_correct: ' || l_session.noa_id_correct, 4777);
3109: hr_utility.set_location('l_session.altered_pa_request_id: ' || l_session.altered_pa_request_id, 4777);
3110:
3111: FOR per_analyses IN c_person_analyses LOOP
3112: --Bug 3103339
3113: l_pa_request_id := per_analyses.pa_request_id;
3124: END IF;
3125: --Bug 3103339
3126: END LOOP;
3127:
3128: hr_utility.set_location('got here 1235', 4778);
3129:
3130: -- get ovn
3131:
3132: FOR pan_ovn IN c_pan_ovn LOOP
3132: FOR pan_ovn IN c_pan_ovn LOOP
3133: p_special_info.object_version_number := pan_ovn.object_version_number;
3134: END LOOP;
3135:
3136: hr_utility.set_location('got here 1236', 4779);
3137:
3138: FOR special_info IN c_sit LOOP
3139: p_special_info.segment1 := special_info.segment1;
3140: p_special_info.segment2 := special_info.segment2;
3167: p_special_info.segment29 := special_info.segment29;
3168: p_special_info.segment30 := special_info.segment30;
3169: END LOOP;
3170:
3171: hr_utility.set_location('got here 1237', 4780);
3172: End return_special_information;
3173:
3174: Procedure Fetch_ASGEI_prior_root_sf50
3175: (
3206: l_result_code varchar2(30);
3207: l_proc varchar2(30):='Fetch_ASGEI_asof_root_sf50';
3208:
3209: Begin
3210: hr_utility.set_location('Entering ' || l_proc,5);
3211: For extra_info in c_extra_info_id loop
3212: l_extra_info_id := extra_info.assignment_extra_info_id;
3213: End loop;
3214: If l_extra_info_id is null then
3211: For extra_info in c_extra_info_id loop
3212: l_extra_info_id := extra_info.assignment_extra_info_id;
3213: End loop;
3214: If l_extra_info_id is null then
3215: hr_utility.set_location('EI Not Found ' || l_proc, 20);
3216: return;
3217: end if;
3218:
3219: open get_root_hist_id( p_altered_pa_request_id, p_noa_id_corrected);
3219: open get_root_hist_id( p_altered_pa_request_id, p_noa_id_corrected);
3220: fetch get_root_hist_id into l_pa_history_id;
3221: if get_root_hist_id%NotFound then
3222: close get_root_hist_id;
3223: hr_utility.set_location('Root History Not Found ' || l_proc, 30);
3224: return;
3225: else
3226: close get_root_hist_id;
3227: hr_utility.set_location('Calling Fetch_asgei ' || l_proc, 50);
3223: hr_utility.set_location('Root History Not Found ' || l_proc, 30);
3224: return;
3225: else
3226: close get_root_hist_id;
3227: hr_utility.set_location('Calling Fetch_asgei ' || l_proc, 50);
3228: fetch_asgei (
3229: p_assignment_extra_info_id => l_extra_info_id,
3230: p_date_effective => p_date_effective,
3231: p_pa_history_id => l_pa_history_id,
3232: p_get_ovn_flag => p_get_ovn_flag,
3233: p_asgei_data => p_asgei_data,
3234: p_result_code => l_result_code);
3235: end if;
3236: hr_utility.set_location('Leaving ' || l_proc, 100);
3237:
3238: End Fetch_ASGEI_prior_root_sf50;
3239:
3240:
3266: l_result_code varchar2(30);
3267: l_proc varchar2(30):='Fetch_asgn_prior_root_sf50';
3268:
3269: Begin
3270: hr_utility.set_location('Entering ' || l_proc,5);
3271:
3272: open get_root_hist_id( p_altered_pa_request_id, p_noa_id_corrected);
3273: fetch get_root_hist_id into l_pa_history_id;
3274: if get_root_hist_id%NotFound then
3272: open get_root_hist_id( p_altered_pa_request_id, p_noa_id_corrected);
3273: fetch get_root_hist_id into l_pa_history_id;
3274: if get_root_hist_id%NotFound then
3275: close get_root_hist_id;
3276: hr_utility.set_location('Root History Not Found ' || l_proc, 30);
3277: return;
3278: else
3279: close get_root_hist_id;
3280: hr_utility.set_location('Calling Fetch_asgn ' || l_proc, 50);
3276: hr_utility.set_location('Root History Not Found ' || l_proc, 30);
3277: return;
3278: else
3279: close get_root_hist_id;
3280: hr_utility.set_location('Calling Fetch_asgn ' || l_proc, 50);
3281: fetch_assignment (
3282: p_assignment_id => p_assignment_id,
3283: p_date_effective => p_date_effective,
3284: p_pa_history_id => l_pa_history_id,
3285: --p_get_ovn_flag => p_get_ovn_flag,
3286: p_assignment_data => p_assignment_data,
3287: p_result_code => l_result_code);
3288: end if;
3289: hr_utility.set_location('Leaving ' || l_proc, 100);
3290:
3291: End Fetch_asgn_prior_root_sf50;
3292:
3293: End GHR_HISTORY_FETCH;