[Home] [Help]
34: and table_alias ='CRSET'
35: and information161 = p_crset_id;
36: exception
37: when others then
38: hr_utility.set_location('crset type upd issue ',11);
39: raise;
40: end;
41: function is_crrate_there(p_oipl_cer_id in number default null,
42: p_plip_cer_id in number default null,
97: p_effective_date in date) return number is
98: l_elp_id number;
99: l_message_text varchar2(240);
100: begin
101: hr_utility.set_location('vpf_cer_id is '||p_vpf_cer_id,1);
102: select information1
103: into l_elp_id
104: from ben_copy_entity_results
105: where copy_entity_txn_id = p_copy_entity_txn_id
105: where copy_entity_txn_id = p_copy_entity_txn_id
106: and table_alias ='ELP'
107: and parent_entity_result_id = p_vpf_cer_id
108: and p_effective_date between information2 and information3;
109: hr_utility.set_location('elp id is '||l_elp_id,1);
110: return l_elp_id;
111: exception
112: when no_data_found then
113: l_message_text := 'no_data_found for vpf_cer is :'||p_vpf_cer_id
122: P_MESSAGE_TEXT => l_message_text,
123: p_effective_date => p_effective_date);
124: raise;
125: when others then
126: hr_utility.set_location('elp pull had issues',10);
127: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
128: (P_MASTER_TXN_ID => g_master_txn_id,
129: P_TXN_ID => g_txn_id,
130: p_context => 'get_elp_for_vpf_other',
235: p_opt_id out nocopy number,
236: p_grade_cer_id out nocopy number,
237: p_point_cer_id out nocopy number) is
238: begin
239: hr_utility.set_location('abr cer is'||p_abr_cer_id,10);
240: select information261,information247,information277,information278
241: into p_pl_id,p_opt_id,p_grade_cer_id,p_point_cer_id
242: from ben_copy_entity_results
243: where copy_entity_result_id = p_abr_cer_id;
240: select information261,information247,information277,information278
241: into p_pl_id,p_opt_id,p_grade_cer_id,p_point_cer_id
242: from ben_copy_entity_results
243: where copy_entity_result_id = p_abr_cer_id;
244: hr_utility.set_location('pl is'||p_pl_id,10);
245: hr_utility.set_location('opt is'||p_opt_id,10);
246: if p_pl_id is not null then
247: p_abr_type := 'GRADE';
248: elsif p_opt_id is not null then
241: into p_pl_id,p_opt_id,p_grade_cer_id,p_point_cer_id
242: from ben_copy_entity_results
243: where copy_entity_result_id = p_abr_cer_id;
244: hr_utility.set_location('pl is'||p_pl_id,10);
245: hr_utility.set_location('opt is'||p_opt_id,10);
246: if p_pl_id is not null then
247: p_abr_type := 'GRADE';
248: elsif p_opt_id is not null then
249: p_abr_type := 'POINT';
251: p_abr_type := '';
252: end if;
253: exception
254: when others then
255: hr_utility.set_location('abr fetch issues '||p_abr_cer_id,11);
256: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
257: (P_MASTER_TXN_ID => g_master_txn_id,
258: P_TXN_ID => g_txn_id,
259: p_context => 'get_abr_detl',
270: l_abr_cer_id number;
271: begin
272: if p_pl_cer_id is not null then
273: begin
274: hr_utility.set_location('getting abr cer for pl cer'||p_pl_cer_id,10);
275: select copy_entity_result_id
276: into l_abr_cer_id
277: from ben_copy_entity_results
278: where INFORMATION277 = p_pl_cer_id
278: where INFORMATION277 = p_pl_cer_id
279: and table_alias = 'ABR'
280: and copy_entity_txn_id = p_copy_entity_txn_id
281: and p_effective_date between information2 and information3;
282: hr_utility.set_location('abr cer '||l_abr_cer_id,20);
283: return l_abr_cer_id;
284: exception
285: when no_data_found then
286: hr_utility.set_location('abr cer doesnot exist',30);
282: hr_utility.set_location('abr cer '||l_abr_cer_id,20);
283: return l_abr_cer_id;
284: exception
285: when no_data_found then
286: hr_utility.set_location('abr cer doesnot exist',30);
287: return l_abr_cer_id;
288: when others then
289: hr_utility.set_location('issues in getting abr cer ',40);
290: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
285: when no_data_found then
286: hr_utility.set_location('abr cer doesnot exist',30);
287: return l_abr_cer_id;
288: when others then
289: hr_utility.set_location('issues in getting abr cer ',40);
290: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
291: (P_MASTER_TXN_ID => g_master_txn_id,
292: P_TXN_ID => g_txn_id,
293: p_context => 'get_abr_cer',
298: raise;
299: end;
300: elsif p_opt_cer_id is not null then
301: begin
302: hr_utility.set_location('getting abr cer for opt cer'||p_opt_cer_id,10);
303: select copy_entity_result_id
304: into l_abr_cer_id
305: from ben_copy_entity_results
306: where INFORMATION278 = p_opt_cer_id
306: where INFORMATION278 = p_opt_cer_id
307: and table_alias = 'ABR'
308: and copy_entity_txn_id = p_copy_entity_txn_id
309: and p_effective_date between information2 and information3;
310: hr_utility.set_location('abr cer '||l_abr_cer_id,20);
311: return l_abr_cer_id;
312: exception
313: when no_data_found then
314: hr_utility.set_location('abr cer doesnot exist',30);
310: hr_utility.set_location('abr cer '||l_abr_cer_id,20);
311: return l_abr_cer_id;
312: exception
313: when no_data_found then
314: hr_utility.set_location('abr cer doesnot exist',30);
315: return l_abr_cer_id;
316: when others then
317: hr_utility.set_location('issues in getting abr cer ',40);
318: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
313: when no_data_found then
314: hr_utility.set_location('abr cer doesnot exist',30);
315: return l_abr_cer_id;
316: when others then
317: hr_utility.set_location('issues in getting abr cer ',40);
318: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
319: (P_MASTER_TXN_ID => g_master_txn_id,
320: P_TXN_ID => g_txn_id,
321: p_context => 'get_abr_cer',
325: p_effective_date => p_effective_date);
326: raise;
327: end;
328: else
329: hr_utility.set_location('pl or opt cer is to be passed',50);
330: end if;
331: end get_abr_cer;
332: function is_hrr_exists(p_copy_entity_txn_id in number,
333: p_grade_cer_id in number default null,
334: p_point_cer_id in number default null,
335: p_hrr_esd in date) return number is
336: l_hrr_cer_id number;
337: begin
338: hr_utility.set_location('inside is_hrr_exists',10);
339: select copy_entity_result_id
340: into l_hrr_cer_id
341: from ben_copy_entity_results
342: where copy_entity_txn_id = p_copy_entity_txn_id
346: and p_hrr_esd between information2 and information3;
347: return l_hrr_cer_id;
348: exception
349: when no_data_found then
350: hr_utility.set_location('hrr cer doesnot exist',30);
351: return l_hrr_cer_id;
352: when others then
353: hr_utility.set_location('issues in getting hrr cer ',40);
354: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
349: when no_data_found then
350: hr_utility.set_location('hrr cer doesnot exist',30);
351: return l_hrr_cer_id;
352: when others then
353: hr_utility.set_location('issues in getting hrr cer ',40);
354: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
355: (P_MASTER_TXN_ID => g_master_txn_id,
356: P_TXN_ID => g_txn_id,
357: p_context => 'is_hrr_exists',
367: p_point_cer_id in number,
368: p_crr_esd in date) return boolean is
369: l_count number;
370: begin
371: hr_utility.set_location('inside is_crr_exists',10);
372: select count(*) into l_count
373: from ben_copy_entity_results
374: where copy_entity_txn_id = p_copy_entity_txn_id
375: and table_alias = 'CRRATE'
376: and (information230 is null or information230 = p_grade_cer_id)
377: and (information169 is null or information169 = p_point_cer_id)
378: and p_crr_esd between information2 and information3
379: and information160 = p_crset_id;
380: hr_utility.set_location('count is '||l_count,20);
381: if nvl(l_count,0) > 0 then
382: return TRUE;
383: else
384: return FALSE;
408: l_crr_cer_id number;
409: l_continue boolean := TRUE;
410: l_crr_exists boolean ;
411: begin
412: hr_utility.set_location('inside crr create',10);
413: get_table_route_details(p_table_alias => 'CRRATE',
414: p_table_route_id => l_crr_tr_id,
415: p_table_name => l_crr_tr_name);
416: hr_utility.set_location('crr tr name'||l_crr_tr_name,20);
412: hr_utility.set_location('inside crr create',10);
413: get_table_route_details(p_table_alias => 'CRRATE',
414: p_table_route_id => l_crr_tr_id,
415: p_table_name => l_crr_tr_name);
416: hr_utility.set_location('crr tr name'||l_crr_tr_name,20);
417: if l_crr_tr_name is null then
418: hr_utility.set_location('crr tr name'||l_crr_tr_name,45);
419: l_continue := FALSE;
420: end if;
414: p_table_route_id => l_crr_tr_id,
415: p_table_name => l_crr_tr_name);
416: hr_utility.set_location('crr tr name'||l_crr_tr_name,20);
417: if l_crr_tr_name is null then
418: hr_utility.set_location('crr tr name'||l_crr_tr_name,45);
419: l_continue := FALSE;
420: end if;
421: if p_copy_entity_txn_id is null then
422: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
418: hr_utility.set_location('crr tr name'||l_crr_tr_name,45);
419: l_continue := FALSE;
420: end if;
421: if p_copy_entity_txn_id is null then
422: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
423: l_continue := FALSE;
424: end if;
425: if p_crset_id is null then
426: hr_utility.set_location('crset id passed is null',55);
422: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
423: l_continue := FALSE;
424: end if;
425: if p_crset_id is null then
426: hr_utility.set_location('crset id passed is null',55);
427: -- in case of create grrate, we will like to create crrate with no crset id.
428: end if;
429: if p_abr_cer_id is null then
430: hr_utility.set_location('crr has to have abr cer',55);
426: hr_utility.set_location('crset id passed is null',55);
427: -- in case of create grrate, we will like to create crrate with no crset id.
428: end if;
429: if p_abr_cer_id is null then
430: hr_utility.set_location('crr has to have abr cer',55);
431: l_continue := FALSE;
432: end if;
433: if p_grade_cer_id is null and p_point_cer_id is null then
434: hr_utility.set_location('crr has to have grd/pt cer',55);
430: hr_utility.set_location('crr has to have abr cer',55);
431: l_continue := FALSE;
432: end if;
433: if p_grade_cer_id is null and p_point_cer_id is null then
434: hr_utility.set_location('crr has to have grd/pt cer',55);
435: l_continue := FALSE;
436: end if;
437: if p_vpf_esd is null or p_vpf_eed is null then
438: hr_utility.set_location('crr has to have dt range',55);
434: hr_utility.set_location('crr has to have grd/pt cer',55);
435: l_continue := FALSE;
436: end if;
437: if p_vpf_esd is null or p_vpf_eed is null then
438: hr_utility.set_location('crr has to have dt range',55);
439: l_continue := FALSE;
440: end if;
441: if p_dml_operation not in ('INSERT','REUSE','UPDATE') then
442: hr_utility.set_location('wrong dml_oper passed ',55);
438: hr_utility.set_location('crr has to have dt range',55);
439: l_continue := FALSE;
440: end if;
441: if p_dml_operation not in ('INSERT','REUSE','UPDATE') then
442: hr_utility.set_location('wrong dml_oper passed ',55);
443: l_continue := FALSE;
444: end if;
445: if p_datetrack_mode not in ('INSERT','UPDATE_REPLACE') then
446: hr_utility.set_location('wrong dt_mode passed ',55);
442: hr_utility.set_location('wrong dml_oper passed ',55);
443: l_continue := FALSE;
444: end if;
445: if p_datetrack_mode not in ('INSERT','UPDATE_REPLACE') then
446: hr_utility.set_location('wrong dt_mode passed ',55);
447: l_continue := FALSE;
448: end if;
449: l_crr_exists := is_crr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
450: p_crset_id => p_crset_id,
451: p_grade_cer_id => p_grade_cer_id,
452: p_point_cer_id => p_point_cer_id,
453: p_crr_esd => p_vpf_esd);
454: if l_crr_exists then
455: hr_utility.set_location('crr exists, no insert',55);
456: l_continue := FALSE;
457: end if;
458: if l_continue then
459: begin
456: l_continue := FALSE;
457: end if;
458: if l_continue then
459: begin
460: hr_utility.set_location('cer insert api called',55);
461: ben_copy_entity_results_api.create_copy_entity_results
462: (p_effective_date => p_effective_date
463: ,p_copy_entity_txn_id => p_copy_entity_txn_id
464: ,p_result_type_cd => 'DISPLAY'
484: ,p_copy_entity_result_id => l_crr_cer_id
485: ,p_object_version_number => l_crr_cer_ovn);
486: exception
487: when others then
488: hr_utility.set_location('some issue in creating crr row ',120);
489: raise;
490: end;
491: p_crr_cer_id := l_crr_cer_id;
492: end if;
489: raise;
490: end;
491: p_crr_cer_id := l_crr_cer_id;
492: end if;
493: hr_utility.set_location('leaving create crrate',55);
494: end create_crrate_row;
495: procedure create_crrate_row(p_grade_cer_id in number default null,
496: p_point_cer_id in number default null,
497: p_copy_entity_txn_id in number,
505: l_abr_cer_id number;
506: l_grd_st_dt date;
507: l_continue boolean := true;
508: begin
509: hr_utility.set_location('inside create crrate',55);
510: -- this routine will be called by setup UI.
511: if p_grade_cer_id is null and p_point_cer_id is null then
512: hr_utility.set_location('grd/pt cer id should be there',10);
513: l_continue := false;
508: begin
509: hr_utility.set_location('inside create crrate',55);
510: -- this routine will be called by setup UI.
511: if p_grade_cer_id is null and p_point_cer_id is null then
512: hr_utility.set_location('grd/pt cer id should be there',10);
513: l_continue := false;
514: else
515: l_abr_cer_id := get_abr_cer(p_copy_entity_txn_id => p_copy_entity_txn_id,
516: p_pl_cer_id => p_grade_cer_id,
516: p_pl_cer_id => p_grade_cer_id,
517: p_opt_cer_id => p_point_cer_id,
518: p_effective_date => p_effective_date);
519: if l_abr_cer_id is null then
520: hr_utility.set_location('going for abr row create ',40);
521: if p_grade_cer_id is not null then
522: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => p_grade_cer_id);
523: end if;
524: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
529: p_start_date => nvl(l_grd_st_dt,l_sot),
530: p_abr_cer_id => l_abr_cer_id,
531: p_create_hrr => 'Y',
532: p_dml_oper => 'INSERT');
533: hr_utility.set_location('abr cer is '||l_abr_cer_id,50);
534: if l_abr_cer_id is null then
535: l_continue := false;
536: end if;
537: end if;
549: p_crset_id => p_crset_id,
550: p_elp_id => '',
551: p_crr_cer_id => p_crr_cer_id);
552: end if;
553: hr_utility.set_location('leaving create crrate',56);
554: end create_crrate_row;
555: procedure create_crset_row(p_crset_id in number,
556: p_effective_date in date,
557: p_copy_entity_txn_id in number,
605: begin
606: get_table_route_details(p_table_alias => 'CRSET',
607: p_table_route_id => l_crs_tr_id,
608: p_table_name => l_crs_tr_name);
609: hr_utility.set_location('crs tr name'||l_crs_tr_name,20);
610: if l_crs_tr_name is null then
611: hr_utility.set_location('crs tr name'||l_crs_tr_name,45);
612: l_continue := FALSE;
613: end if;
607: p_table_route_id => l_crs_tr_id,
608: p_table_name => l_crs_tr_name);
609: hr_utility.set_location('crs tr name'||l_crs_tr_name,20);
610: if l_crs_tr_name is null then
611: hr_utility.set_location('crs tr name'||l_crs_tr_name,45);
612: l_continue := FALSE;
613: end if;
614: if p_copy_entity_txn_id is null then
615: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
611: hr_utility.set_location('crs tr name'||l_crs_tr_name,45);
612: l_continue := FALSE;
613: end if;
614: if p_copy_entity_txn_id is null then
615: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
616: l_continue := FALSE;
617: end if;
618: if p_crset_id is null then
619: hr_utility.set_location('crset id has to be passed ',55);
615: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
616: l_continue := FALSE;
617: end if;
618: if p_crset_id is null then
619: hr_utility.set_location('crset id has to be passed ',55);
620: l_continue := FALSE;
621: end if;
622: if p_elp_id is null then
623: l_dml_oper := 'INSERT';
697: ,p_copy_entity_result_id => l_crs_cer_id
698: ,p_object_version_number => l_crs_cer_ovn);
699: exception
700: when others then
701: hr_utility.set_location('some issue in creating crs row ',120);
702: raise;
703: end;
704: end if;
705: end create_crset_row;
743: l_upd_effdt varchar2(30);
744: l_ins_row varchar2(30);
745: l_eot date := to_date('31-12-4712','DD-MM-RRRR');
746: begin
747: hr_utility.set_location('inside update_crset ',10);
748: validate_crset(p_name => p_crset_name,
749: p_bu_cd => p_bu_cd,
750: p_bu_name => p_bu_name,
751: p_fp_cd => p_fp_cd,
766: p_sa_name => p_sa_name,
767: p_ff_id => p_ff_id,
768: p_ff_name => p_ff_name,
769: p_valid => l_continue);
770: hr_utility.set_location('data validated ',10);
771: if p_datetrack_mode not in ('CORRECTION','UPDATE_REPLACE') then
772: hr_utility.set_location('invalid dt mode '||p_datetrack_mode,1);
773: l_continue := false;
774: else
768: p_ff_name => p_ff_name,
769: p_valid => l_continue);
770: hr_utility.set_location('data validated ',10);
771: if p_datetrack_mode not in ('CORRECTION','UPDATE_REPLACE') then
772: hr_utility.set_location('invalid dt mode '||p_datetrack_mode,1);
773: l_continue := false;
774: else
775: hr_utility.set_location('dt mode '||p_datetrack_mode,1);
776: end if;
771: if p_datetrack_mode not in ('CORRECTION','UPDATE_REPLACE') then
772: hr_utility.set_location('invalid dt mode '||p_datetrack_mode,1);
773: l_continue := false;
774: else
775: hr_utility.set_location('dt mode '||p_datetrack_mode,1);
776: end if;
777: if l_continue then
778: for crset_rec in csr_crset loop
779: hr_utility.set_location('criteria set row found'||substr(p_crset_name,1,40),10);
775: hr_utility.set_location('dt mode '||p_datetrack_mode,1);
776: end if;
777: if l_continue then
778: for crset_rec in csr_crset loop
779: hr_utility.set_location('criteria set row found'||substr(p_crset_name,1,40),10);
780: if nvl(crset_rec.dml_operation,'REUSE') = 'REUSE' then
781: l_dml_operation := 'UPDATE';
782: else
783: l_dml_operation := crset_rec.dml_operation;
781: l_dml_operation := 'UPDATE';
782: else
783: l_dml_operation := crset_rec.dml_operation;
784: end if;
785: hr_utility.set_location('new dml oper is '||l_dml_operation,4);
786: if p_datetrack_mode = 'CORRECTION' then
787: hr_utility.set_location('same row is to be updated',10);
788: l_upd_curr := 'Y';
789: l_del_future := 'N';
783: l_dml_operation := crset_rec.dml_operation;
784: end if;
785: hr_utility.set_location('new dml oper is '||l_dml_operation,4);
786: if p_datetrack_mode = 'CORRECTION' then
787: hr_utility.set_location('same row is to be updated',10);
788: l_upd_curr := 'Y';
789: l_del_future := 'N';
790: l_crset_eed := crset_rec.information3;
791: l_upd_effdt := 'N';
792: l_ins_row := 'N';
793: else
794: if crset_rec.information2 = p_effective_date then
795: -- row is getting updated on same date, so no insert only update
796: hr_utility.set_location('rec started as of today',5);
797: l_ins_row := 'N';
798: l_upd_curr := 'Y';
799: l_upd_effdt := 'N';
800: l_crset_eed := l_eot;
799: l_upd_effdt := 'N';
800: l_crset_eed := l_eot;
801: if crset_rec.information3 <> l_eot then
802: -- current row goes till end of time so no delete too
803: hr_utility.set_location('rec is not till eot ',5);
804: l_del_future := 'Y';
805: else
806: hr_utility.set_location('rec is till eot ',5);
807: l_del_future := 'N';
802: -- current row goes till end of time so no delete too
803: hr_utility.set_location('rec is not till eot ',5);
804: l_del_future := 'Y';
805: else
806: hr_utility.set_location('rec is till eot ',5);
807: l_del_future := 'N';
808: end if;
809: else
810: hr_utility.set_location('rec started earlier than today',5);
806: hr_utility.set_location('rec is till eot ',5);
807: l_del_future := 'N';
808: end if;
809: else
810: hr_utility.set_location('rec started earlier than today',5);
811: l_del_future := 'Y';
812: l_upd_curr := 'N';
813: l_ins_row := 'Y';
814: l_upd_effdt := 'Y';
815: l_crset_eed := p_effective_date - 1;
816: end if;
817: end if;
818: if l_del_future = 'Y' then
819: hr_utility.set_location('deleting future recs',6);
820: delete from ben_copy_entity_results
821: where copy_entity_txn_id = p_copy_entity_txn_id
822: and table_alias = 'CRSET'
823: and information161 = p_crset_id
823: and information161 = p_crset_id
824: and information2 > p_effective_date;
825: end if;
826: if l_upd_effdt ='Y' then
827: hr_utility.set_location('updating effdt of curr row',6);
828: update ben_copy_entity_results
829: set INFORMATION3 = l_crset_eed
830: where copy_entity_result_id = crset_rec.copy_entity_result_id;
831: end if;
829: set INFORMATION3 = l_crset_eed
830: where copy_entity_result_id = crset_rec.copy_entity_result_id;
831: end if;
832: if l_upd_curr ='Y' then
833: hr_utility.set_location('updating curr row with new values',6);
834: update ben_copy_entity_results
835: set dml_operation = l_dml_operation,
836: INFORMATION232 = p_loc_id,
837: INFORMATION233 = p_job_id,
857: information3 = l_crset_eed
858: where copy_entity_result_id = crset_rec.copy_entity_result_id;
859: end if;
860: if l_ins_row ='Y' then
861: hr_utility.set_location('creating new row with new values',6);
862: create_crset_row(p_crset_id => p_crset_id,
863: p_effective_date => p_effective_date,
864: p_copy_entity_txn_id => p_copy_entity_txn_id,
865: p_bu_cd => p_bu_cd,
897: p_elp_eed => l_eot,
898: p_name => p_crset_name,
899: p_elp_cer_id => crset_rec.information160);
900: end if;
901: hr_utility.set_location('end of update_crset ',6);
902: end loop;
903: else
904: hr_utility.set_location('invalid data passed',7);
905: end if;
900: end if;
901: hr_utility.set_location('end of update_crset ',6);
902: end loop;
903: else
904: hr_utility.set_location('invalid data passed',7);
905: end if;
906: hr_utility.set_location('out of update_crset',8);
907: end update_crset;
908: procedure create_crset_row(p_crset_id out nocopy number,
902: end loop;
903: else
904: hr_utility.set_location('invalid data passed',7);
905: end if;
906: hr_utility.set_location('out of update_crset',8);
907: end update_crset;
908: procedure create_crset_row(p_crset_id out nocopy number,
909: p_effective_date in date,
910: p_copy_entity_txn_id in number,
958: p_ff_id => p_ff_id,
959: p_ff_name => p_ff_name,
960: p_valid => l_continue);
961: else
962: hr_utility.set_location('data is not to be validated coming from SFL',11);
963: end if;
964: if l_continue then
965: select pqh_gsp_criteria_set_id_s.nextval into l_crset_id from dual;
966: hr_utility.set_location('crset id value is '||l_crset_id,30);
962: hr_utility.set_location('data is not to be validated coming from SFL',11);
963: end if;
964: if l_continue then
965: select pqh_gsp_criteria_set_id_s.nextval into l_crset_id from dual;
966: hr_utility.set_location('crset id value is '||l_crset_id,30);
967: create_crset_row
968: (p_crset_id => l_crset_id,
969: p_effective_date => p_effective_date,
970: p_copy_entity_txn_id => p_copy_entity_txn_id,
992: p_elp_esd => p_effective_date,
993: p_elp_eed => l_eot,
994: p_name => p_name);
995: else
996: hr_utility.set_location('data incomplete',20);
997: end if;
998: p_crset_id := l_crset_id;
999: end create_crset_row;
1000: procedure is_elp_exists_in_crset(p_copy_entity_txn_id in number,
1013: begin
1014: -- crset may exist but not for the date range, with this routine we are
1015: -- telling whether it exists or not and also , for this range can the row is to be inserted
1016: -- or not
1017: hr_utility.set_location('inside is_elp_exists_in_crset ',10);
1018: hr_utility.set_location('elp_id is '||p_elp_id,10);
1019: hr_utility.set_location('elp esd '||to_char(p_start_date,'DD-MM-RRRR'),11);
1020: hr_utility.set_location('elp eed '||to_char(p_end_date,'DD-MM-RRRR'),11);
1021: p_ins_flag := true;
1014: -- crset may exist but not for the date range, with this routine we are
1015: -- telling whether it exists or not and also , for this range can the row is to be inserted
1016: -- or not
1017: hr_utility.set_location('inside is_elp_exists_in_crset ',10);
1018: hr_utility.set_location('elp_id is '||p_elp_id,10);
1019: hr_utility.set_location('elp esd '||to_char(p_start_date,'DD-MM-RRRR'),11);
1020: hr_utility.set_location('elp eed '||to_char(p_end_date,'DD-MM-RRRR'),11);
1021: p_ins_flag := true;
1022: for i in csr_crset loop
1015: -- telling whether it exists or not and also , for this range can the row is to be inserted
1016: -- or not
1017: hr_utility.set_location('inside is_elp_exists_in_crset ',10);
1018: hr_utility.set_location('elp_id is '||p_elp_id,10);
1019: hr_utility.set_location('elp esd '||to_char(p_start_date,'DD-MM-RRRR'),11);
1020: hr_utility.set_location('elp eed '||to_char(p_end_date,'DD-MM-RRRR'),11);
1021: p_ins_flag := true;
1022: for i in csr_crset loop
1023: p_crset_id := i.information161;
1016: -- or not
1017: hr_utility.set_location('inside is_elp_exists_in_crset ',10);
1018: hr_utility.set_location('elp_id is '||p_elp_id,10);
1019: hr_utility.set_location('elp esd '||to_char(p_start_date,'DD-MM-RRRR'),11);
1020: hr_utility.set_location('elp eed '||to_char(p_end_date,'DD-MM-RRRR'),11);
1021: p_ins_flag := true;
1022: for i in csr_crset loop
1023: p_crset_id := i.information161;
1024: p_crset_type := i.information100;
1021: p_ins_flag := true;
1022: for i in csr_crset loop
1023: p_crset_id := i.information161;
1024: p_crset_type := i.information100;
1025: hr_utility.set_location('crset name is '||substr(i.information151,1,45),12);
1026: hr_utility.set_location('crset cer id is '||i.copy_entity_result_id,12);
1027: hr_utility.set_location('crset esd '||to_char(i.information2,'DD-MM-RRRR'),13);
1028: hr_utility.set_location('crset eed '||to_char(i.information3,'DD-MM-RRRR'),14);
1029: if p_start_date between i.information2 and i.information3 and
1022: for i in csr_crset loop
1023: p_crset_id := i.information161;
1024: p_crset_type := i.information100;
1025: hr_utility.set_location('crset name is '||substr(i.information151,1,45),12);
1026: hr_utility.set_location('crset cer id is '||i.copy_entity_result_id,12);
1027: hr_utility.set_location('crset esd '||to_char(i.information2,'DD-MM-RRRR'),13);
1028: hr_utility.set_location('crset eed '||to_char(i.information3,'DD-MM-RRRR'),14);
1029: if p_start_date between i.information2 and i.information3 and
1030: p_end_date between i.information2 and i.information3 then
1023: p_crset_id := i.information161;
1024: p_crset_type := i.information100;
1025: hr_utility.set_location('crset name is '||substr(i.information151,1,45),12);
1026: hr_utility.set_location('crset cer id is '||i.copy_entity_result_id,12);
1027: hr_utility.set_location('crset esd '||to_char(i.information2,'DD-MM-RRRR'),13);
1028: hr_utility.set_location('crset eed '||to_char(i.information3,'DD-MM-RRRR'),14);
1029: if p_start_date between i.information2 and i.information3 and
1030: p_end_date between i.information2 and i.information3 then
1031: p_ins_flag := false;
1024: p_crset_type := i.information100;
1025: hr_utility.set_location('crset name is '||substr(i.information151,1,45),12);
1026: hr_utility.set_location('crset cer id is '||i.copy_entity_result_id,12);
1027: hr_utility.set_location('crset esd '||to_char(i.information2,'DD-MM-RRRR'),13);
1028: hr_utility.set_location('crset eed '||to_char(i.information3,'DD-MM-RRRR'),14);
1029: if p_start_date between i.information2 and i.information3 and
1030: p_end_date between i.information2 and i.information3 then
1031: p_ins_flag := false;
1032: end if;
1091: where parent_entity_result_id = l_elp_cer_id
1092: and copy_entity_txn_id = p_copy_entity_txn_id
1093: and l_elp_esd between information2 and information3;
1094: begin
1095: hr_utility.set_location('inside build_crset_for_elp ',10);
1096: for elp_rec in csr_elig_prof loop
1097: hr_utility.set_location('elp avail,pull detl ',30);
1098: l_ins_flag := TRUE;
1099: l_elp_esd := elp_rec.information2;
1093: and l_elp_esd between information2 and information3;
1094: begin
1095: hr_utility.set_location('inside build_crset_for_elp ',10);
1096: for elp_rec in csr_elig_prof loop
1097: hr_utility.set_location('elp avail,pull detl ',30);
1098: l_ins_flag := TRUE;
1099: l_elp_esd := elp_rec.information2;
1100: hr_utility.set_location('elp esd is '||to_char(l_elp_esd,'DD-MM-RRRR'),13);
1101: if l_elp_cer_id is null then
1096: for elp_rec in csr_elig_prof loop
1097: hr_utility.set_location('elp avail,pull detl ',30);
1098: l_ins_flag := TRUE;
1099: l_elp_esd := elp_rec.information2;
1100: hr_utility.set_location('elp esd is '||to_char(l_elp_esd,'DD-MM-RRRR'),13);
1101: if l_elp_cer_id is null then
1102: -- as all criteria rows are linked to display elp row, we are getting that
1103: l_elp_cer_id := elp_rec.copy_entity_result_id;
1104: end if;
1101: if l_elp_cer_id is null then
1102: -- as all criteria rows are linked to display elp row, we are getting that
1103: l_elp_cer_id := elp_rec.copy_entity_result_id;
1104: end if;
1105: hr_utility.set_location('elp cer id is '||l_elp_cer_id,13);
1106: -- if crset exists in this txn then we don't have to create it
1107: is_elp_exists_in_crset(p_copy_entity_txn_id => p_copy_entity_txn_id,
1108: p_elp_id => elp_rec.information1,
1109: p_start_date => elp_rec.information2,
1110: p_end_date => elp_rec.information3,
1111: p_crset_id => l_crset_id,
1112: p_ins_flag => l_ins_flag,
1113: p_crset_type => l_crset_type);
1114: hr_utility.set_location('exist crset type is '||l_crset_type,30);
1115: if l_crset_type is null then
1116: l_crset_type := p_abr_type;
1117: elsif ((l_crset_type ='GRADE' and p_abr_type ='POINT') or
1118: (l_crset_type ='POINT' and p_abr_type ='GRADE')) then
1116: l_crset_type := p_abr_type;
1117: elsif ((l_crset_type ='GRADE' and p_abr_type ='POINT') or
1118: (l_crset_type ='POINT' and p_abr_type ='GRADE')) then
1119: l_crset_type := 'GRADE_POINT';
1120: hr_utility.set_location('upd crset type is '||l_crset_type,30);
1121: update_crset_type(p_copy_entity_txn_id => p_copy_entity_txn_id,
1122: p_crset_id => l_crset_id,
1123: p_crset_type => l_crset_type);
1124: end if;
1121: update_crset_type(p_copy_entity_txn_id => p_copy_entity_txn_id,
1122: p_crset_id => l_crset_id,
1123: p_crset_type => l_crset_type);
1124: end if;
1125: hr_utility.set_location('crset type updated to be '||l_crset_type,30);
1126: if l_ins_flag then
1127: -- the elp current row is to be inserted in crset table
1128: -- if crset id is null then new crset is to be pulled from seq.
1129: if l_crset_id is null then
1127: -- the elp current row is to be inserted in crset table
1128: -- if crset id is null then new crset is to be pulled from seq.
1129: if l_crset_id is null then
1130: select pqh_gsp_criteria_set_id_s.nextval into l_crset_id from dual;
1131: hr_utility.set_location('crset id value is '||l_crset_id,30);
1132: end if;
1133:
1134: l_bu_cd := null;
1135: l_bu_name := null;
1169: l_ff_id := null;
1170: l_ff_name := null;
1171: l_ff_cer_id := null;
1172:
1173: hr_utility.set_location('local_var initialized',11);
1174: for crit_rec in csr_elig_crit(l_elp_cer_id ) loop
1175: hr_utility.set_location('table alias is '||crit_rec.table_alias,30);
1176: if crit_rec.table_alias = 'EBU' then
1177: l_bu_cd := crit_rec.information11;
1171: l_ff_cer_id := null;
1172:
1173: hr_utility.set_location('local_var initialized',11);
1174: for crit_rec in csr_elig_crit(l_elp_cer_id ) loop
1175: hr_utility.set_location('table alias is '||crit_rec.table_alias,30);
1176: if crit_rec.table_alias = 'EBU' then
1177: l_bu_cd := crit_rec.information11;
1178: l_bu_name := crit_rec.information5;
1179: l_bu_cer_id := crit_rec.copy_entity_result_id;
1176: if crit_rec.table_alias = 'EBU' then
1177: l_bu_cd := crit_rec.information11;
1178: l_bu_name := crit_rec.information5;
1179: l_bu_cer_id := crit_rec.copy_entity_result_id;
1180: hr_utility.set_location('bargain_unit is '||substr(l_bu_name,1,40),11);
1181: elsif crit_rec.table_alias = 'EFP' then
1182: l_fp_cd := crit_rec.information12;
1183: l_fp_name := crit_rec.information5;
1184: l_fp_cer_id := crit_rec.copy_entity_result_id;
1181: elsif crit_rec.table_alias = 'EFP' then
1182: l_fp_cd := crit_rec.information12;
1183: l_fp_name := crit_rec.information5;
1184: l_fp_cer_id := crit_rec.copy_entity_result_id;
1185: hr_utility.set_location('fp is '||substr(l_fp_name,1,40),11);
1186: elsif crit_rec.table_alias = 'EJP' then
1187: l_job_id := crit_rec.information174;
1188: l_job_name := crit_rec.information5;
1189: l_job_cer_id := crit_rec.copy_entity_result_id;
1186: elsif crit_rec.table_alias = 'EJP' then
1187: l_job_id := crit_rec.information174;
1188: l_job_name := crit_rec.information5;
1189: l_job_cer_id := crit_rec.copy_entity_result_id;
1190: hr_utility.set_location('job is '||substr(l_job_name,1,40),11);
1191: elsif crit_rec.table_alias = 'EOU' then
1192: l_org_id := crit_rec.information174;
1193: l_org_name := crit_rec.information5;
1194: l_org_cer_id := crit_rec.copy_entity_result_id;
1191: elsif crit_rec.table_alias = 'EOU' then
1192: l_org_id := crit_rec.information174;
1193: l_org_name := crit_rec.information5;
1194: l_org_cer_id := crit_rec.copy_entity_result_id;
1195: hr_utility.set_location('org is '||substr(l_org_name,1,40),11);
1196: elsif crit_rec.table_alias = 'EPT' then
1197: l_pt_id := crit_rec.information174;
1198: l_pt_name := crit_rec.information5;
1199: l_pt_cer_id := crit_rec.copy_entity_result_id;
1196: elsif crit_rec.table_alias = 'EPT' then
1197: l_pt_id := crit_rec.information174;
1198: l_pt_name := crit_rec.information5;
1199: l_pt_cer_id := crit_rec.copy_entity_result_id;
1200: hr_utility.set_location('per_typ is '||substr(l_pt_name,1,40),11);
1201: elsif crit_rec.table_alias = 'EWL' then
1202: l_loc_id := crit_rec.information174;
1203: l_loc_name := crit_rec.information5;
1204: l_loc_cer_id := crit_rec.copy_entity_result_id;
1201: elsif crit_rec.table_alias = 'EWL' then
1202: l_loc_id := crit_rec.information174;
1203: l_loc_name := crit_rec.information5;
1204: l_loc_cer_id := crit_rec.copy_entity_result_id;
1205: hr_utility.set_location('loc is '||substr(l_loc_name,1,40),11);
1206: elsif crit_rec.table_alias = 'ERG' then
1207: l_event_type := crit_rec.information12;
1208: l_perf_rtng_cd := crit_rec.information13;
1209: l_pr_name := crit_rec.information5;
1212: l_hyphen := instr(l_pr_name,'-');
1213: l_perf_rtng_name := substr(l_pr_name,1,l_hyphen-1);
1214: l_event_name := substr(l_pr_name,l_hyphen+2);
1215: end if;
1216: hr_utility.set_location('perf_rtng is '||substr(l_pr_name,1,40),11);
1217: elsif crit_rec.table_alias = 'ESA' then
1218: l_sa_id := crit_rec.information241;
1219: l_sa_name := crit_rec.information5;
1220: l_sa_cer_id := crit_rec.copy_entity_result_id;
1217: elsif crit_rec.table_alias = 'ESA' then
1218: l_sa_id := crit_rec.information241;
1219: l_sa_name := crit_rec.information5;
1220: l_sa_cer_id := crit_rec.copy_entity_result_id;
1221: hr_utility.set_location('svc_area is '||substr(l_sa_name,1,40),11);
1222: elsif crit_rec.table_alias = 'ERL' then
1223: l_ff_id := crit_rec.information251;
1224: l_ff_name := crit_rec.information5;
1225: l_ff_cer_id := crit_rec.copy_entity_result_id;
1222: elsif crit_rec.table_alias = 'ERL' then
1223: l_ff_id := crit_rec.information251;
1224: l_ff_name := crit_rec.information5;
1225: l_ff_cer_id := crit_rec.copy_entity_result_id;
1226: hr_utility.set_location('rule is '||substr(l_ff_name,1,40),11);
1227: end if;
1228: end loop;
1229: hr_utility.set_location('all variables pulled',11);
1230: create_crset_row(p_crset_id => l_crset_id,
1225: l_ff_cer_id := crit_rec.copy_entity_result_id;
1226: hr_utility.set_location('rule is '||substr(l_ff_name,1,40),11);
1227: end if;
1228: end loop;
1229: hr_utility.set_location('all variables pulled',11);
1230: create_crset_row(p_crset_id => l_crset_id,
1231: p_effective_date => p_effective_date,
1232: p_copy_entity_txn_id => p_copy_entity_txn_id,
1233: p_bu_cd => l_bu_cd,
1265: p_elp_eed => elp_rec.information3,
1266: p_name => substr(elp_rec.information5,1,150),
1267: p_elp_cer_id => elp_rec.copy_entity_result_id);
1268: else
1269: hr_utility.set_location('elp row in crset,skipping',50);
1270: end if;
1271: end loop;
1272: p_crset_id := l_crset_id;
1273: hr_utility.set_location('build_crset_for_elp done',50);
1269: hr_utility.set_location('elp row in crset,skipping',50);
1270: end if;
1271: end loop;
1272: p_crset_id := l_crset_id;
1273: hr_utility.set_location('build_crset_for_elp done',50);
1274: end build_crset_for_elp;
1275: procedure pull_elp_for_crset(p_elp_id in number,
1276: p_copy_entity_txn_id in number,
1277: p_crset_type in varchar2,
1309: and table_alias ='CRSET'
1310: and information161 = p_crset_id
1311: and p_effective_date between information2 and information3;
1312: begin
1313: hr_utility.set_location('elig-prof to be copied',1);
1314: savepoint pull_elp;
1315: populate_ep_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
1316: p_effective_date => p_effective_date,
1317: p_business_group_id => p_business_group_id,
1317: p_business_group_id => p_business_group_id,
1318: p_business_area => 'PQH_GSP_TASK_LIST',
1319: p_ep_id => p_elp_id,
1320: p_ep_cer_id => l_elp_cer_id);
1321: hr_utility.set_location('elig-prof copied',10);
1322: build_crset_for_elp(p_elp_id => p_elp_id,
1323: p_copy_entity_txn_id => p_copy_entity_txn_id,
1324: p_abr_type => p_crset_type,
1325: p_effective_date => p_effective_date,
1324: p_abr_type => p_crset_type,
1325: p_effective_date => p_effective_date,
1326: p_business_group_id => p_business_group_id,
1327: p_crset_id => p_crset_id);
1328: hr_utility.set_location('crset is '||p_crset_id,20);
1329: if p_crset_id is not null then
1330: open crset_rec;
1331: fetch crset_rec into l_crset_name,l_loc_id,l_job_id,l_org_id,l_rule_id,l_pt_id,l_sa_id,l_bu_cd,l_fp_cd,l_pr_cd,l_event_type;
1332: if crset_rec%found then
1379: l_vpf_eed date;
1380: l_bg number;
1381: l_elp_id number;
1382: begin
1383: hr_utility.set_location('inside vpf_to_stage ',10);
1384: -- variable rate row is pulled to staging area, based on this we have to build
1385: -- crrate row and crset row
1386: -- the hierarchy on the ben side is like this
1387: -- abr -> avr -> vpf -> vep -> elp -> criteria rows
1400: and information72 ='GSPSA'
1401: and INFORMATION77 = 'A';
1402: exception
1403: when no_data_found then
1404: hr_utility.set_location('vpf is not for GSP ',10);
1405: l_continue := 'N';
1406: when others then
1407: hr_utility.set_location('issues in getting vpf ',20);
1408: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1403: when no_data_found then
1404: hr_utility.set_location('vpf is not for GSP ',10);
1405: l_continue := 'N';
1406: when others then
1407: hr_utility.set_location('issues in getting vpf ',20);
1408: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1409: (P_MASTER_TXN_ID => g_master_txn_id,
1410: P_TXN_ID => g_txn_id,
1411: p_context => 'vpf_to_stage_vpf_pull',
1414: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
1415: p_effective_date => p_effective_date);
1416: raise;
1417: end;
1418: hr_utility.set_location('abr cer id is '||l_abr_cer_id,10);
1419: get_abr_detl(p_abr_cer_id => l_abr_cer_id,
1420: p_abr_type => l_abr_type,
1421: p_pl_id => l_pl_id,
1422: p_opt_id => l_opt_id,
1421: p_pl_id => l_pl_id,
1422: p_opt_id => l_opt_id,
1423: p_grade_cer_id => l_grade_cer_id,
1424: p_point_cer_id => l_point_cer_id);
1425: hr_utility.set_location('abr_type is '||l_abr_type,10);
1426: if l_abr_type is null then
1427: l_continue := 'N';
1428: end if;
1429: if p_result_type_cd <> 'DISPLAY' then
1468: p_dml_operation => 'REUSE',
1469: p_elp_id => l_elp_id,
1470: p_crr_cer_id => l_crr_cer_id);
1471: end if;
1472: hr_utility.set_location('vpf_to_stage done',50);
1473: exception
1474: when others then
1475: hr_utility.set_location('issues in vpf_to_stage ',20);
1476: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1471: end if;
1472: hr_utility.set_location('vpf_to_stage done',50);
1473: exception
1474: when others then
1475: hr_utility.set_location('issues in vpf_to_stage ',20);
1476: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1477: (P_MASTER_TXN_ID => g_master_txn_id,
1478: P_TXN_ID => g_txn_id,
1479: p_context => 'vpf_to_stage',
1495: and copy_entity_txn_id = p_copy_entity_txn_id
1496: and (information258 = p_scale_cer_id or information255 = p_scale_id);
1497: exception
1498: when others then
1499: hr_utility.set_location('issue in updating parent ',420);
1500: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1501: (P_MASTER_TXN_ID => g_master_txn_id,
1502: P_TXN_ID => g_txn_id,
1503: p_context => 'scl_writeback',
1512: procedure change_parent_oipl(p_copy_entity_txn_id in number,
1513: p_oipl_cer_id in number,
1514: p_opt_cer_id in number) is
1515: begin
1516: hr_utility.set_location('inside chg_parent for oipl'||p_oipl_cer_id,10);
1517: begin
1518: update ben_copy_entity_results
1519: set parent_entity_result_id = p_oipl_cer_id,
1520: gs_parent_entity_result_id = p_oipl_cer_id
1520: gs_parent_entity_result_id = p_oipl_cer_id
1521: where table_alias = 'ELP'
1522: and copy_entity_txn_id = p_copy_entity_txn_id
1523: and parent_entity_result_id = p_opt_cer_id;
1524: hr_utility.set_location('ELP rows changed '||p_oipl_cer_id,20);
1525: exception
1526: when others then
1527: hr_utility.set_location('issue in updating parent ',420);
1528: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1523: and parent_entity_result_id = p_opt_cer_id;
1524: hr_utility.set_location('ELP rows changed '||p_oipl_cer_id,20);
1525: exception
1526: when others then
1527: hr_utility.set_location('issue in updating parent ',420);
1528: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1529: (P_MASTER_TXN_ID => g_master_txn_id,
1530: P_TXN_ID => g_txn_id,
1531: p_context => 'change_parent_oipl',
1539: procedure change_parent_plip(p_copy_entity_txn_id in number,
1540: p_plip_cer_id in number,
1541: p_pl_cer_id in number) is
1542: begin
1543: hr_utility.set_location('inside chg_parent for plp: plip_cer'||p_plip_cer_id,10);
1544: hr_utility.set_location('inside chg_parent for plp: pl_cer'||p_pl_cer_id,20);
1545: -- The following Update Call is commented as the same is taken care by plan Copy APIs
1546: -- update_txn_table_route(p_copy_entity_txn_id);
1547: hr_utility.set_location('table routes updated ',25);
1540: p_plip_cer_id in number,
1541: p_pl_cer_id in number) is
1542: begin
1543: hr_utility.set_location('inside chg_parent for plp: plip_cer'||p_plip_cer_id,10);
1544: hr_utility.set_location('inside chg_parent for plp: pl_cer'||p_pl_cer_id,20);
1545: -- The following Update Call is commented as the same is taken care by plan Copy APIs
1546: -- update_txn_table_route(p_copy_entity_txn_id);
1547: hr_utility.set_location('table routes updated ',25);
1548: begin
1543: hr_utility.set_location('inside chg_parent for plp: plip_cer'||p_plip_cer_id,10);
1544: hr_utility.set_location('inside chg_parent for plp: pl_cer'||p_pl_cer_id,20);
1545: -- The following Update Call is commented as the same is taken care by plan Copy APIs
1546: -- update_txn_table_route(p_copy_entity_txn_id);
1547: hr_utility.set_location('table routes updated ',25);
1548: begin
1549: update ben_copy_entity_results
1550: set parent_entity_result_id = p_plip_cer_id,
1551: gs_parent_entity_result_id = p_plip_cer_id
1551: gs_parent_entity_result_id = p_plip_cer_id
1552: where table_alias = 'COP'
1553: and copy_entity_txn_id = p_copy_entity_txn_id
1554: and parent_entity_result_id = p_pl_cer_id;
1555: hr_utility.set_location('oipl rows changed '||p_plip_cer_id,420);
1556: exception
1557: when others then
1558: hr_utility.set_location('issue in updating parent ',420);
1559: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1554: and parent_entity_result_id = p_pl_cer_id;
1555: hr_utility.set_location('oipl rows changed '||p_plip_cer_id,420);
1556: exception
1557: when others then
1558: hr_utility.set_location('issue in updating parent ',420);
1559: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1560: (P_MASTER_TXN_ID => g_master_txn_id,
1561: P_TXN_ID => g_txn_id,
1562: p_context => 'change_parent_plip',
1572: gs_parent_entity_result_id = p_plip_cer_id
1573: where table_alias = 'ELP'
1574: and copy_entity_txn_id = p_copy_entity_txn_id
1575: and parent_entity_result_id = p_pl_cer_id;
1576: hr_utility.set_location('ELP rows changed '||p_plip_cer_id,420);
1577: exception
1578: when others then
1579: hr_utility.set_location('issue in updating parent ',420);
1580: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1575: and parent_entity_result_id = p_pl_cer_id;
1576: hr_utility.set_location('ELP rows changed '||p_plip_cer_id,420);
1577: exception
1578: when others then
1579: hr_utility.set_location('issue in updating parent ',420);
1580: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1581: (P_MASTER_TXN_ID => g_master_txn_id,
1582: P_TXN_ID => g_txn_id,
1583: p_context => 'change_parent_plip-2',
1618: from ben_copy_entity_results
1619: where copy_entity_txn_id = p_copy_entity_txn_id
1620: and table_alias = 'CPP';
1621: l_ordr_num := nvl(l_ordr_num,0) + 1;
1622: hr_utility.set_location('ord_num is '||l_ordr_num,20);
1623: end;
1624: begin
1625: ben_copy_entity_results_api.create_copy_entity_results(
1626: p_effective_date => p_effective_date
1647: ,p_copy_entity_result_id => p_plip_cer_id
1648: ,p_object_version_number => p_plip_cer_ovn);
1649: exception
1650: when others then
1651: hr_utility.set_location('issue in creation cpp cer '||p_grade_id,400);
1652: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1653: (P_MASTER_TXN_ID => g_master_txn_id,
1654: P_TXN_ID => g_txn_id,
1655: p_context => 'create_plip_row',
1660: raise;
1661: end;
1662: exception
1663: when others then
1664: hr_utility.set_location('issue in cpp cer '||p_grade_id,420);
1665: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1666: (P_MASTER_TXN_ID => g_master_txn_id,
1667: P_TXN_ID => g_txn_id,
1668: p_context => 'create_plip_row2',
1681: where copy_entity_result_id = p_grade_cer_id;
1682: return l_start_date;
1683: exception
1684: when others then
1685: hr_utility.set_location('issue in getting grd start date '||p_grade_cer_id,10);
1686: raise;
1687: end get_grd_start_date;
1688: procedure post_pull_process(p_copy_entity_txn_id in number,
1689: p_start_cer in number default null,
1704: p_business_group_id => p_business_group_id,
1705: p_business_area => p_business_area);
1706: exception
1707: when others then
1708: hr_utility.set_location('issues in copying hr data on hier',30);
1709: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1710: (P_MASTER_TXN_ID => g_master_txn_id,
1711: P_TXN_ID => g_txn_id,
1712: p_context => 'post_pull_process',
1716: p_effective_date => p_effective_date);
1717: raise;
1718: end;
1719: for scl_rec in csr_scl loop
1720: hr_utility.set_location('scale cer id is '||scl_rec.copy_entity_result_id,20);
1721: scl_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1722: p_scale_id => scl_rec.information1,
1723: p_scale_cer_id => scl_rec.copy_entity_result_id);
1724: end loop;
1726: end post_pull_process;
1727: procedure cep_to_stage(p_cep_cer_id in number,
1728: p_copy_entity_txn_id in number) is
1729: begin
1730: hr_utility.set_location('inside cep_to_stage ',10);
1731: update ben_copy_entity_results
1732: set gs_parent_entity_result_id = parent_entity_result_id,
1733: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
1734: where copy_entity_txn_id = p_copy_entity_txn_id
1733: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
1734: where copy_entity_txn_id = p_copy_entity_txn_id
1735: and copy_entity_result_id = p_cep_cer_id
1736: and gs_parent_entity_result_id is null;
1737: hr_utility.set_location('cep_to_stage done',50);
1738: exception
1739: when others then
1740: hr_utility.set_location('issues in updating cep row',20);
1741: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1736: and gs_parent_entity_result_id is null;
1737: hr_utility.set_location('cep_to_stage done',50);
1738: exception
1739: when others then
1740: hr_utility.set_location('issues in updating cep row',20);
1741: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1742: (P_MASTER_TXN_ID => g_master_txn_id,
1743: P_TXN_ID => g_txn_id,
1744: p_context => 'cep_to_stage',
1750: end cep_to_stage;
1751: procedure epa_to_stage(p_epa_cer_id in number,
1752: p_copy_entity_txn_id in number) is
1753: begin
1754: hr_utility.set_location('inside epa_to_stage ',10);
1755: update ben_copy_entity_results
1756: set gs_parent_entity_result_id = parent_entity_result_id,
1757: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
1758: where copy_entity_txn_id = p_copy_entity_txn_id
1757: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
1758: where copy_entity_txn_id = p_copy_entity_txn_id
1759: and copy_entity_result_id = p_epa_cer_id
1760: and gs_parent_entity_result_id is null;
1761: hr_utility.set_location('epa_to_stage done',50);
1762: exception
1763: when others then
1764: hr_utility.set_location('issues in updating epa row',20);
1765: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1760: and gs_parent_entity_result_id is null;
1761: hr_utility.set_location('epa_to_stage done',50);
1762: exception
1763: when others then
1764: hr_utility.set_location('issues in updating epa row',20);
1765: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1766: (P_MASTER_TXN_ID => g_master_txn_id,
1767: P_TXN_ID => g_txn_id,
1768: p_context => 'epa_to_stage',
1793: Open Cs_Cep;
1794: Fetch Cs_Cep into l_Result_type_Cd;
1795: Close cs_Cep;
1796:
1797: hr_utility.set_location('inside elp_to_stage ',10);
1798: begin
1799: hr_utility.set_location('updating crit rows ',15);
1800: update ben_copy_entity_results
1801: set gs_parent_entity_result_id = parent_entity_result_id,
1795: Close cs_Cep;
1796:
1797: hr_utility.set_location('inside elp_to_stage ',10);
1798: begin
1799: hr_utility.set_location('updating crit rows ',15);
1800: update ben_copy_entity_results
1801: set gs_parent_entity_result_id = parent_entity_result_id,
1802: gs_mirror_src_entity_result_id = mirror_src_entity_result_id,
1803: information101 = information1,
1805: where copy_entity_txn_id = p_copy_entity_txn_id
1806: and parent_entity_result_id = p_elp_cer_id;
1807: exception
1808: when others then
1809: hr_utility.set_location('issues in updating criteria rows ',20);
1810: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1811: (P_MASTER_TXN_ID => g_master_txn_id,
1812: P_TXN_ID => g_txn_id,
1813: p_context => 'elp_to_stage',
1817: p_effective_date => trunc(sysdate));
1818: raise;
1819: end;
1820: begin
1821: hr_utility.set_location('updating elp row ',15);
1822: update ben_copy_entity_results
1823: set gs_parent_entity_result_id = parent_entity_result_id,
1824: gs_mirror_src_entity_result_id = mirror_src_entity_result_id,
1825: Result_type_cd = Nvl(l_Result_type_Cd, result_type_Cd)
1838: ,p_copy_entity_txn_id => p_copy_entity_txn_id
1839: );
1840: exception
1841: when others then
1842: hr_utility.set_location('issues in updating criteria rows ',20);
1843: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1844: (P_MASTER_TXN_ID => g_master_txn_id,
1845: P_TXN_ID => g_txn_id,
1846: p_context => 'elp_to_stage2',
1850: p_effective_date => trunc(sysdate));
1851: raise;
1852: end;
1853:
1854: hr_utility.set_location('elp_to_stage done',50);
1855:
1856: end elp_to_stage;
1857: function get_ep_id(p_prtn_elig_prfl_id in number,
1858: p_effective_date in date) return number is
1862: where p_effective_date between effective_start_date and effective_end_date
1863: and prtn_elig_prfl_id = p_prtn_elig_prfl_id;
1864: l_ep_id number;
1865: begin
1866: hr_utility.set_location('getting EP for PEP'||p_prtn_elig_prfl_id,10);
1867: for i in csr_elp loop
1868: l_ep_id := i.eligy_prfl_id;
1869: end loop;
1870: return l_ep_id;
1869: end loop;
1870: return l_ep_id;
1871: exception
1872: when others then
1873: hr_utility.set_location('issues in pulling EP for PEP',20);
1874: raise;
1875: end;
1876: function get_co_std_rate(p_plan_id in number default null,
1877: p_opt_id in number default null,
1879: p_pay_rule_id out nocopy number) return number is
1880: l_abr_id number;
1881: begin
1882: if p_plan_id is null and p_opt_id is null then
1883: hr_utility.set_location('both plan and option passed is null',10);
1884: return 0;
1885: elsif p_plan_id is not null and p_opt_id is not null then
1886: hr_utility.set_location('both plan and option are passed ',20);
1887: return 0;
1882: if p_plan_id is null and p_opt_id is null then
1883: hr_utility.set_location('both plan and option passed is null',10);
1884: return 0;
1885: elsif p_plan_id is not null and p_opt_id is not null then
1886: hr_utility.set_location('both plan and option are passed ',20);
1887: return 0;
1888: elsif p_plan_id is not null and p_opt_id is null then
1889: begin
1890: select acty_base_rt_id,pay_rate_grade_rule_id
1894: and pl_id = p_plan_id
1895: and acty_typ_cd ='GSPSA'; -- we are interested only in this type
1896: exception
1897: when no_data_found then
1898: hr_utility.set_location('no pl rate defined '||p_plan_id,30);
1899: when others then
1900: hr_utility.set_location('issues in getting rate ',40);
1901: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1902: (P_MASTER_TXN_ID => g_master_txn_id,
1896: exception
1897: when no_data_found then
1898: hr_utility.set_location('no pl rate defined '||p_plan_id,30);
1899: when others then
1900: hr_utility.set_location('issues in getting rate ',40);
1901: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1902: (P_MASTER_TXN_ID => g_master_txn_id,
1903: P_TXN_ID => g_txn_id,
1904: p_context => 'get_co_for_abr_other',
1907: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
1908: p_effective_date => trunc(sysdate));
1909: raise;
1910: end;
1911: hr_utility.set_location('rate id is '||l_abr_id,50);
1912: return l_abr_id;
1913: elsif p_plan_id is null and p_opt_id is not null then
1914: begin
1915: select acty_base_rt_id,pay_rate_grade_rule_id
1919: and opt_id = p_opt_id
1920: and acty_typ_cd ='GSPSA'; -- we are interested only in this type
1921: exception
1922: when no_data_found then
1923: hr_utility.set_location('no opt rate defined '||p_opt_id,30);
1924: when others then
1925: hr_utility.set_location('issues in getting rate ',40);
1926: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1927: (P_MASTER_TXN_ID => g_master_txn_id,
1921: exception
1922: when no_data_found then
1923: hr_utility.set_location('no opt rate defined '||p_opt_id,30);
1924: when others then
1925: hr_utility.set_location('issues in getting rate ',40);
1926: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1927: (P_MASTER_TXN_ID => g_master_txn_id,
1928: P_TXN_ID => g_txn_id,
1929: p_context => 'get_co_for_abr_other2',
1932: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
1933: p_effective_date => trunc(sysdate));
1934: raise;
1935: end;
1936: hr_utility.set_location('rate id is '||l_abr_id,50);
1937: return l_abr_id;
1938: end if;
1939: end get_co_std_rate;
1940: function get_scl_pay_rate(p_scale_id in number) return number is
1939: end get_co_std_rate;
1940: function get_scl_pay_rate(p_scale_id in number) return number is
1941: l_pay_rate_id number;
1942: begin
1943: hr_utility.set_location('scale id is '||p_scale_id,10);
1944: -- if the pay rate doesn't exist with this name, no value will be set.
1945: select rate.rate_id
1946: into l_pay_rate_id
1947: from pay_rates rate, per_parent_spines scale
1948: where rate.name = scale.name
1949: and rate.rate_type ='SP'
1950: and rate.parent_spine_id = p_scale_id
1951: and scale.parent_spine_id = p_scale_id;
1952: hr_utility.set_location('rate is '||l_pay_rate_id,20);
1953: return l_pay_rate_id;
1954: exception
1955: when no_data_found then
1956: hr_utility.set_location('no rate is defined '||p_scale_id,30);
1952: hr_utility.set_location('rate is '||l_pay_rate_id,20);
1953: return l_pay_rate_id;
1954: exception
1955: when no_data_found then
1956: hr_utility.set_location('no rate is defined '||p_scale_id,30);
1957: null;
1958: when others then
1959: hr_utility.set_location('issues in pulling scl pay rate'||p_scale_id,40);
1960: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1955: when no_data_found then
1956: hr_utility.set_location('no rate is defined '||p_scale_id,30);
1957: null;
1958: when others then
1959: hr_utility.set_location('issues in pulling scl pay rate'||p_scale_id,40);
1960: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1961: (P_MASTER_TXN_ID => g_master_txn_id,
1962: P_TXN_ID => g_txn_id,
1963: p_context => 'get_scl_pay_rate_other',
1973: p_step_id out nocopy number,
1974: p_step_ovn out nocopy number,
1975: p_step_name out nocopy number) is
1976: begin
1977: hr_utility.set_location('grade scale id is '||p_grade_spine_id,1);
1978: hr_utility.set_location('point id is '||p_point_id,2);
1979: select step_id,sequence,object_version_number
1980: into p_step_id, p_step_name,p_step_ovn
1981: from per_spinal_point_steps_f
1974: p_step_ovn out nocopy number,
1975: p_step_name out nocopy number) is
1976: begin
1977: hr_utility.set_location('grade scale id is '||p_grade_spine_id,1);
1978: hr_utility.set_location('point id is '||p_point_id,2);
1979: select step_id,sequence,object_version_number
1980: into p_step_id, p_step_name,p_step_ovn
1981: from per_spinal_point_steps_f
1982: where grade_spine_id = p_grade_spine_id
1983: and spinal_point_id = p_point_id
1984: and p_effective_date between effective_start_date and effective_end_date;
1985: exception
1986: when no_data_found then
1987: hr_utility.set_location('grade scale does not have point '||p_grade_spine_id,20);
1988: hr_utility.set_location('point '||p_point_id,30);
1989: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1990: (P_MASTER_TXN_ID => g_master_txn_id,
1991: P_TXN_ID => g_txn_id,
1984: and p_effective_date between effective_start_date and effective_end_date;
1985: exception
1986: when no_data_found then
1987: hr_utility.set_location('grade scale does not have point '||p_grade_spine_id,20);
1988: hr_utility.set_location('point '||p_point_id,30);
1989: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1990: (P_MASTER_TXN_ID => g_master_txn_id,
1991: P_TXN_ID => g_txn_id,
1992: p_context => 'get_step_details_no_data',
1995: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
1996: p_effective_date => trunc(sysdate));
1997: raise;
1998: when others then
1999: hr_utility.set_location('issues in selected step',40);
2000: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2001: (P_MASTER_TXN_ID => g_master_txn_id,
2002: P_TXN_ID => g_txn_id,
2003: p_context => 'get_step_details_other',
2013: p_ceiling_step_id out nocopy number,
2014: p_grade_spine_id out nocopy number,
2015: p_starting_step out nocopy number) is
2016: begin
2017: hr_utility.set_location('grade id is '||p_grade_id,1);
2018: hr_utility.set_location('scale id is '||p_scale_id,2);
2019: select ceiling_step_id,grade_spine_id,starting_step
2020: into p_ceiling_step_id,p_grade_spine_id,p_starting_step
2021: from per_grade_spines_f
2014: p_grade_spine_id out nocopy number,
2015: p_starting_step out nocopy number) is
2016: begin
2017: hr_utility.set_location('grade id is '||p_grade_id,1);
2018: hr_utility.set_location('scale id is '||p_scale_id,2);
2019: select ceiling_step_id,grade_spine_id,starting_step
2020: into p_ceiling_step_id,p_grade_spine_id,p_starting_step
2021: from per_grade_spines_f
2022: where grade_id = p_grade_id
2021: from per_grade_spines_f
2022: where grade_id = p_grade_id
2023: and parent_spine_id = p_scale_id
2024: and p_effective_date between effective_start_date and effective_end_date;
2025: hr_utility.set_location('grade scale id is '||p_grade_spine_id,10);
2026: exception
2027: when no_data_found then
2028: hr_utility.set_location('grade scale combination does not exist '||p_grade_id,20);
2029: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2024: and p_effective_date between effective_start_date and effective_end_date;
2025: hr_utility.set_location('grade scale id is '||p_grade_spine_id,10);
2026: exception
2027: when no_data_found then
2028: hr_utility.set_location('grade scale combination does not exist '||p_grade_id,20);
2029: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2030: (P_MASTER_TXN_ID => g_master_txn_id,
2031: P_TXN_ID => g_txn_id,
2032: p_context => 'get_scale_details_no_data',
2035: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
2036: p_effective_date => trunc(sysdate));
2037: raise;
2038: when others then
2039: hr_utility.set_location('issues in selected grade_spine',30);
2040: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2041: (P_MASTER_TXN_ID => g_master_txn_id,
2042: P_TXN_ID => g_txn_id,
2043: p_context => 'get_scale_details_other',
2052: p_point_seq out nocopy number,
2053: p_point_ovn out nocopy number,
2054: p_spinal_point out nocopy varchar2) is
2055: begin
2056: hr_utility.set_location('point is '||p_point_id,1);
2057: select parent_spine_id,sequence,spinal_point,object_version_number
2058: into p_scale_id, p_point_seq, p_spinal_point,p_point_ovn
2059: from per_spinal_points
2060: where spinal_point_id = p_point_id;
2057: select parent_spine_id,sequence,spinal_point,object_version_number
2058: into p_scale_id, p_point_seq, p_spinal_point,p_point_ovn
2059: from per_spinal_points
2060: where spinal_point_id = p_point_id;
2061: hr_utility.set_location('scale id is '||p_scale_id,2);
2062: exception
2063: when no_data_found then
2064: hr_utility.set_location('point does not exist',20);
2065: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2060: where spinal_point_id = p_point_id;
2061: hr_utility.set_location('scale id is '||p_scale_id,2);
2062: exception
2063: when no_data_found then
2064: hr_utility.set_location('point does not exist',20);
2065: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2066: (P_MASTER_TXN_ID => g_master_txn_id,
2067: P_TXN_ID => g_txn_id,
2068: p_context => 'get_point_details_no_data',
2071: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
2072: p_effective_date => trunc(sysdate));
2073: raise;
2074: when others then
2075: hr_utility.set_location('issue in pulling point ',30);
2076: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2077: (P_MASTER_TXN_ID => g_master_txn_id,
2078: P_TXN_ID => g_txn_id,
2079: p_context => 'get_point_details_other',
2091: into l_spinal_point_id
2092: from per_spinal_point_steps_f
2093: where step_id = p_step_id
2094: and p_effective_date between effective_start_date and effective_end_date;
2095: hr_utility.set_location('spinal point id is'||l_spinal_point_id,10);
2096: return l_spinal_point_id;
2097: exception
2098: when no_data_found then
2099: hr_utility.set_location('invalid step '||p_step_id,15);
2095: hr_utility.set_location('spinal point id is'||l_spinal_point_id,10);
2096: return l_spinal_point_id;
2097: exception
2098: when no_data_found then
2099: hr_utility.set_location('invalid step '||p_step_id,15);
2100: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2101: (P_MASTER_TXN_ID => g_master_txn_id,
2102: P_TXN_ID => g_txn_id,
2103: p_context => 'get_point_for_step_no_data',
2106: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
2107: p_effective_date => trunc(sysdate));
2108: raise;
2109: when others then
2110: hr_utility.set_location('issues in selecting step',20);
2111: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2112: (P_MASTER_TXN_ID => g_master_txn_id,
2113: P_TXN_ID => g_txn_id,
2114: p_context => 'get_point_for_step_other',
2121: function get_point_for_opt(p_option_id in number,
2122: p_effective_date in date) return number is
2123: l_spinal_point_id number;
2124: begin
2125: hr_utility.set_location('opt id is'||p_option_id,10);
2126: hr_utility.set_location('effdate is'||to_char(p_effective_date,'DD-MM-RRRRy'),10);
2127: select mapping_table_pk_id
2128: into l_spinal_point_id
2129: from ben_opt_f
2122: p_effective_date in date) return number is
2123: l_spinal_point_id number;
2124: begin
2125: hr_utility.set_location('opt id is'||p_option_id,10);
2126: hr_utility.set_location('effdate is'||to_char(p_effective_date,'DD-MM-RRRRy'),10);
2127: select mapping_table_pk_id
2128: into l_spinal_point_id
2129: from ben_opt_f
2130: where opt_id = p_option_id
2129: from ben_opt_f
2130: where opt_id = p_option_id
2131: and p_effective_date between effective_start_date and effective_end_date
2132: and mapping_table_name ='PER_SPINAL_POINTS';
2133: hr_utility.set_location('spinal point id is'||l_spinal_point_id,10);
2134: return l_spinal_point_id;
2135: exception
2136: when no_data_found then
2137: hr_utility.set_location('selected option is not linked to SP'||p_option_id,15);
2133: hr_utility.set_location('spinal point id is'||l_spinal_point_id,10);
2134: return l_spinal_point_id;
2135: exception
2136: when no_data_found then
2137: hr_utility.set_location('selected option is not linked to SP'||p_option_id,15);
2138: return l_spinal_point_id;
2139: when others then
2140: hr_utility.set_location('issues in selecting point',20);
2141: raise;
2136: when no_data_found then
2137: hr_utility.set_location('selected option is not linked to SP'||p_option_id,15);
2138: return l_spinal_point_id;
2139: when others then
2140: hr_utility.set_location('issues in selecting point',20);
2141: raise;
2142: end get_point_for_opt;
2143: function get_opt_for_point(p_point_id in number,
2144: p_effective_date in date) return number is
2143: function get_opt_for_point(p_point_id in number,
2144: p_effective_date in date) return number is
2145: l_opt_id number;
2146: begin
2147: hr_utility.set_location('pt id is'||p_point_id,10);
2148: hr_utility.set_location('effdt is'||to_char(p_effective_date,'DD-MM-RRRR'),20);
2149: select opt_id
2150: into l_opt_id
2151: from ben_opt_f
2144: p_effective_date in date) return number is
2145: l_opt_id number;
2146: begin
2147: hr_utility.set_location('pt id is'||p_point_id,10);
2148: hr_utility.set_location('effdt is'||to_char(p_effective_date,'DD-MM-RRRR'),20);
2149: select opt_id
2150: into l_opt_id
2151: from ben_opt_f
2152: where mapping_table_pk_id = p_point_id
2151: from ben_opt_f
2152: where mapping_table_pk_id = p_point_id
2153: and p_effective_date between effective_start_date and effective_end_date
2154: and mapping_table_name ='PER_SPINAL_POINTS';
2155: hr_utility.set_location('opt id is'||l_opt_id,30);
2156: return l_opt_id;
2157: exception
2158: when no_data_found then
2159: hr_utility.set_location('option is not linked to point'||p_point_id,15);
2155: hr_utility.set_location('opt id is'||l_opt_id,30);
2156: return l_opt_id;
2157: exception
2158: when no_data_found then
2159: hr_utility.set_location('option is not linked to point'||p_point_id,15);
2160: return l_opt_id;
2161: when others then
2162: hr_utility.set_location('issues in selecting option',20);
2163: raise;
2158: when no_data_found then
2159: hr_utility.set_location('option is not linked to point'||p_point_id,15);
2160: return l_opt_id;
2161: when others then
2162: hr_utility.set_location('issues in selecting option',20);
2163: raise;
2164: end get_opt_for_point;
2165: function get_grade_for_plan(p_plan_id in number,
2166: p_effective_date in date) return number is
2171: from ben_pl_f
2172: where pl_id = p_plan_id
2173: and p_effective_date between effective_start_date and effective_end_date
2174: and mapping_table_name ='PER_GRADES';
2175: hr_utility.set_location('grade id is'||l_grade_id,10);
2176: return l_grade_id;
2177: exception
2178: when no_data_found then
2179: hr_utility.set_location('selected plan is not linked to grade'||p_plan_id,15);
2175: hr_utility.set_location('grade id is'||l_grade_id,10);
2176: return l_grade_id;
2177: exception
2178: when no_data_found then
2179: hr_utility.set_location('selected plan is not linked to grade'||p_plan_id,15);
2180: return l_grade_id;
2181: when others then
2182: hr_utility.set_location('issues in selecting grade',20);
2183: raise;
2178: when no_data_found then
2179: hr_utility.set_location('selected plan is not linked to grade'||p_plan_id,15);
2180: return l_grade_id;
2181: when others then
2182: hr_utility.set_location('issues in selecting grade',20);
2183: raise;
2184: end get_grade_for_plan;
2185: function get_plan_for_grade(p_grade_id in number,
2186: p_effective_date in date) return number is
2191: from ben_pl_f
2192: where mapping_table_pk_id = p_grade_id
2193: and p_effective_date between effective_start_date and effective_end_date
2194: and mapping_table_name ='PER_GRADES';
2195: hr_utility.set_location('plan id is'||l_pl_id,10);
2196: return l_pl_id;
2197: exception
2198: when no_data_found then
2199: hr_utility.set_location('grade is not linked to plan'||p_grade_id,15);
2195: hr_utility.set_location('plan id is'||l_pl_id,10);
2196: return l_pl_id;
2197: exception
2198: when no_data_found then
2199: hr_utility.set_location('grade is not linked to plan'||p_grade_id,15);
2200: return l_pl_id;
2201: when others then
2202: hr_utility.set_location('issues in selecting plan',20);
2203: raise;
2198: when no_data_found then
2199: hr_utility.set_location('grade is not linked to plan'||p_grade_id,15);
2200: return l_pl_id;
2201: when others then
2202: hr_utility.set_location('issues in selecting plan',20);
2203: raise;
2204: end get_plan_for_grade;
2205: function get_oipl_for_step(p_step_id in number,
2206: p_effective_date in date) return number is
2221: and p_effective_date between gs.effective_start_date and gs.effective_end_date
2222: and sps.step_id = p_step_id;
2223: exception
2224: when no_data_found then
2225: hr_utility.set_location('invalid step '||p_step_id,20);
2226: raise;
2227: when others then
2228: hr_utility.set_location('issues in selecting step detail'||p_step_id,30);
2229: raise;
2224: when no_data_found then
2225: hr_utility.set_location('invalid step '||p_step_id,20);
2226: raise;
2227: when others then
2228: hr_utility.set_location('issues in selecting step detail'||p_step_id,30);
2229: raise;
2230: end;
2231: hr_utility.set_location('grade is '||l_grade_id,10);
2232: hr_utility.set_location('point is '||l_point_id,15);
2227: when others then
2228: hr_utility.set_location('issues in selecting step detail'||p_step_id,30);
2229: raise;
2230: end;
2231: hr_utility.set_location('grade is '||l_grade_id,10);
2232: hr_utility.set_location('point is '||l_point_id,15);
2233: l_pl_id := get_plan_for_grade(p_grade_id => l_grade_id,
2234: p_effective_date => p_effective_date);
2235: hr_utility.set_location('plan is '||l_pl_id,10);
2228: hr_utility.set_location('issues in selecting step detail'||p_step_id,30);
2229: raise;
2230: end;
2231: hr_utility.set_location('grade is '||l_grade_id,10);
2232: hr_utility.set_location('point is '||l_point_id,15);
2233: l_pl_id := get_plan_for_grade(p_grade_id => l_grade_id,
2234: p_effective_date => p_effective_date);
2235: hr_utility.set_location('plan is '||l_pl_id,10);
2236: l_opt_id := get_opt_for_point(p_point_id => l_point_id,
2231: hr_utility.set_location('grade is '||l_grade_id,10);
2232: hr_utility.set_location('point is '||l_point_id,15);
2233: l_pl_id := get_plan_for_grade(p_grade_id => l_grade_id,
2234: p_effective_date => p_effective_date);
2235: hr_utility.set_location('plan is '||l_pl_id,10);
2236: l_opt_id := get_opt_for_point(p_point_id => l_point_id,
2237: p_effective_date => p_effective_date);
2238: hr_utility.set_location('option is '||l_opt_id,20);
2239: if l_pl_id is not null and l_opt_id is not null then
2234: p_effective_date => p_effective_date);
2235: hr_utility.set_location('plan is '||l_pl_id,10);
2236: l_opt_id := get_opt_for_point(p_point_id => l_point_id,
2237: p_effective_date => p_effective_date);
2238: hr_utility.set_location('option is '||l_opt_id,20);
2239: if l_pl_id is not null and l_opt_id is not null then
2240: begin
2241: select oipl_id
2242: into l_oipl_id
2243: from ben_oipl_f
2244: where pl_id = l_pl_id
2245: and opt_id = l_opt_id
2246: and p_effective_date between effective_start_date and effective_end_date;
2247: hr_utility.set_location('oipl is '||l_oipl_id,30);
2248: return l_oipl_id;
2249: exception
2250: when no_data_found then
2251: hr_utility.set_location('invalid oipl for pl'||l_pl_id,100);
2247: hr_utility.set_location('oipl is '||l_oipl_id,30);
2248: return l_oipl_id;
2249: exception
2250: when no_data_found then
2251: hr_utility.set_location('invalid oipl for pl'||l_pl_id,100);
2252: raise;
2253: when others then
2254: hr_utility.set_location('issues in selecting oipl detail',120);
2255: raise;
2250: when no_data_found then
2251: hr_utility.set_location('invalid oipl for pl'||l_pl_id,100);
2252: raise;
2253: when others then
2254: hr_utility.set_location('issues in selecting oipl detail',120);
2255: raise;
2256: end;
2257: else
2258: hr_utility.set_location('either plan or opt is null',150);
2254: hr_utility.set_location('issues in selecting oipl detail',120);
2255: raise;
2256: end;
2257: else
2258: hr_utility.set_location('either plan or opt is null',150);
2259: return null;
2260: end if;
2261: end get_oipl_for_step;
2262: function get_step_for_oipl(p_oipl_id in number,
2275: where oipl_id = p_oipl_id
2276: and p_effective_date between effective_start_date and effective_end_date;
2277: exception
2278: when no_data_found then
2279: hr_utility.set_location('invalid oipl '||p_oipl_id,100);
2280: raise;
2281: when others then
2282: hr_utility.set_location('issues in selecting oipl detail'||p_oipl_id,120);
2283: raise;
2278: when no_data_found then
2279: hr_utility.set_location('invalid oipl '||p_oipl_id,100);
2280: raise;
2281: when others then
2282: hr_utility.set_location('issues in selecting oipl detail'||p_oipl_id,120);
2283: raise;
2284: end;
2285: hr_utility.set_location('plan is '||l_pl_id,10);
2286: hr_utility.set_location('option is '||l_opt_id,20);
2281: when others then
2282: hr_utility.set_location('issues in selecting oipl detail'||p_oipl_id,120);
2283: raise;
2284: end;
2285: hr_utility.set_location('plan is '||l_pl_id,10);
2286: hr_utility.set_location('option is '||l_opt_id,20);
2287: l_grade_id := get_grade_for_plan(p_plan_id => l_pl_id,
2288: p_effective_date => p_effective_date);
2289: hr_utility.set_location('grade is '||l_grade_id,30);
2282: hr_utility.set_location('issues in selecting oipl detail'||p_oipl_id,120);
2283: raise;
2284: end;
2285: hr_utility.set_location('plan is '||l_pl_id,10);
2286: hr_utility.set_location('option is '||l_opt_id,20);
2287: l_grade_id := get_grade_for_plan(p_plan_id => l_pl_id,
2288: p_effective_date => p_effective_date);
2289: hr_utility.set_location('grade is '||l_grade_id,30);
2290: l_point_id := get_point_for_opt(p_option_id => l_opt_id,
2285: hr_utility.set_location('plan is '||l_pl_id,10);
2286: hr_utility.set_location('option is '||l_opt_id,20);
2287: l_grade_id := get_grade_for_plan(p_plan_id => l_pl_id,
2288: p_effective_date => p_effective_date);
2289: hr_utility.set_location('grade is '||l_grade_id,30);
2290: l_point_id := get_point_for_opt(p_option_id => l_opt_id,
2291: p_effective_date => p_effective_date);
2292: hr_utility.set_location('point is '||l_point_id,40);
2293: if l_grade_id is not null and l_point_id is not null then
2288: p_effective_date => p_effective_date);
2289: hr_utility.set_location('grade is '||l_grade_id,30);
2290: l_point_id := get_point_for_opt(p_option_id => l_opt_id,
2291: p_effective_date => p_effective_date);
2292: hr_utility.set_location('point is '||l_point_id,40);
2293: if l_grade_id is not null and l_point_id is not null then
2294: begin
2295: -- for a grade there can be only one spine
2296: select step_id
2300: and p_effective_date between sps.effective_start_date and sps.effective_end_date
2301: and p_effective_date between gs.effective_start_date and gs.effective_end_date
2302: and gs.grade_id = l_grade_id
2303: and sps.spinal_point_id = l_point_id;
2304: hr_utility.set_location('step is '||l_step_id,60);
2305: return l_step_id;
2306: exception
2307: when no_data_found then
2308: hr_utility.set_location('invalid grade step comb ',60);
2304: hr_utility.set_location('step is '||l_step_id,60);
2305: return l_step_id;
2306: exception
2307: when no_data_found then
2308: hr_utility.set_location('invalid grade step comb ',60);
2309: raise;
2310: when others then
2311: hr_utility.set_location('issues in selecting step ',80);
2312: raise;
2307: when no_data_found then
2308: hr_utility.set_location('invalid grade step comb ',60);
2309: raise;
2310: when others then
2311: hr_utility.set_location('issues in selecting step ',80);
2312: raise;
2313: end;
2314: else
2315: hr_utility.set_location('grade or point is null ',100);
2311: hr_utility.set_location('issues in selecting step ',80);
2312: raise;
2313: end;
2314: else
2315: hr_utility.set_location('grade or point is null ',100);
2316: return null;
2317: end if;
2318: end get_step_for_oipl;
2319: procedure get_grade_for_plip(p_plip_id in number,
2326: into p_plan_id
2327: from ben_plip_f
2328: where plip_id = p_plip_id
2329: and p_effective_date between effective_start_date and effective_end_date;
2330: hr_utility.set_location('plan is '||p_plan_id,10);
2331: exception
2332: when no_data_found then
2333: hr_utility.set_location('plip does not exist '||p_plip_id,10);
2334: when others then
2329: and p_effective_date between effective_start_date and effective_end_date;
2330: hr_utility.set_location('plan is '||p_plan_id,10);
2331: exception
2332: when no_data_found then
2333: hr_utility.set_location('plip does not exist '||p_plip_id,10);
2334: when others then
2335: hr_utility.set_location('issues in selected plip',20);
2336: raise;
2337: end;
2331: exception
2332: when no_data_found then
2333: hr_utility.set_location('plip does not exist '||p_plip_id,10);
2334: when others then
2335: hr_utility.set_location('issues in selected plip',20);
2336: raise;
2337: end;
2338: if p_plan_id is not null then
2339: p_grade_id := get_grade_for_plan(p_plan_id => p_plan_id,
2337: end;
2338: if p_plan_id is not null then
2339: p_grade_id := get_grade_for_plan(p_plan_id => p_plan_id,
2340: p_effective_date => p_effective_date);
2341: hr_utility.set_location('grade id is'||p_grade_id,40);
2342: end if;
2343: end get_grade_for_plip;
2344: procedure get_table_route_details(p_table_alias in varchar2,
2345: p_table_route_id out nocopy number ,
2348: select table_route_id, substrb(display_name,1,30) -- fix for the bug#12797263
2349: into p_table_route_id, p_table_name
2350: from pqh_table_route_vl
2351: where table_alias = p_table_alias;
2352: hr_utility.set_location('table_route name is '||p_table_name,10);
2353: exception
2354: when no_data_found then
2355: hr_utility.set_location('no table_route is defined for this alias'||p_table_alias,10);
2356: null;
2351: where table_alias = p_table_alias;
2352: hr_utility.set_location('table_route name is '||p_table_name,10);
2353: exception
2354: when no_data_found then
2355: hr_utility.set_location('no table_route is defined for this alias'||p_table_alias,10);
2356: null;
2357: when others then
2358: hr_utility.set_location('issues in pulling table_route details ',20);
2359: raise;
2354: when no_data_found then
2355: hr_utility.set_location('no table_route is defined for this alias'||p_table_alias,10);
2356: null;
2357: when others then
2358: hr_utility.set_location('issues in pulling table_route details ',20);
2359: raise;
2360: end get_table_route_details;
2361: procedure update_gsp_control_rec(p_copy_entity_txn_id in number,
2362: p_effective_date in date,
2373: l_rule_row number;
2374: l_ctrl_rec_cer_id number;
2375: l_ctrl_rec_ovn number;
2376: begin
2377: hr_utility.set_location('bus area is'||p_business_area,10);
2378: hr_utility.set_location('cet is'||p_copy_entity_txn_id,10);
2379: select count(*) into l_plip_row
2380: from ben_copy_entity_results
2381: where copy_entity_txn_id = p_copy_entity_txn_id
2374: l_ctrl_rec_cer_id number;
2375: l_ctrl_rec_ovn number;
2376: begin
2377: hr_utility.set_location('bus area is'||p_business_area,10);
2378: hr_utility.set_location('cet is'||p_copy_entity_txn_id,10);
2379: select count(*) into l_plip_row
2380: from ben_copy_entity_results
2381: where copy_entity_txn_id = p_copy_entity_txn_id
2382: and table_alias ='CPP'
2382: and table_alias ='CPP'
2383: and Result_type_Cd = 'DISPLAY'
2384: and information104 ='LINK'; -- plip record is linked as well
2385:
2386: hr_utility.set_location('grade in GL'||l_plip_row,10);
2387:
2388: select count(*) into l_oipl_row
2389: from ben_copy_entity_results
2390: where copy_entity_txn_id = p_copy_entity_txn_id
2389: from ben_copy_entity_results
2390: where copy_entity_txn_id = p_copy_entity_txn_id
2391: and table_alias ='COP'
2392: and Result_Type_Cd = 'DISPLAY';
2393: hr_utility.set_location('steps'||l_oipl_row,20);
2394:
2395: select count(*) into l_rate_row
2396: from ben_copy_entity_results
2397: where copy_entity_txn_id = p_copy_entity_txn_id
2396: from ben_copy_entity_results
2397: where copy_entity_txn_id = p_copy_entity_txn_id
2398: and table_alias = 'ABR'
2399: and Result_Type_Cd = 'DISPLAY';
2400: hr_utility.set_location('rates'||l_rate_row,30);
2401:
2402: select count(*) into l_rule_row
2403: from ben_copy_entity_results
2404: where copy_entity_txn_id = p_copy_entity_txn_id
2403: from ben_copy_entity_results
2404: where copy_entity_txn_id = p_copy_entity_txn_id
2405: and table_alias ='CEP'
2406: and Result_Type_Cd = 'DISPLAY';
2407: hr_utility.set_location('rules'||l_rule_row,40);
2408:
2409: if l_plip_row > 0 then
2410: l_grd_exists := 'Y';
2411: end if;
2426: else
2427: l_cpd_exists := '';
2428: l_cdd_exists := '';
2429: end if;
2430: hr_utility.set_location('bus area is'||p_business_area,101);
2431: hr_utility.set_location('cet is'||p_copy_entity_txn_id,101);
2432: begin
2433: select copy_entity_result_id
2434: into l_ctrl_rec_cer_id
2427: l_cpd_exists := '';
2428: l_cdd_exists := '';
2429: end if;
2430: hr_utility.set_location('bus area is'||p_business_area,101);
2431: hr_utility.set_location('cet is'||p_copy_entity_txn_id,101);
2432: begin
2433: select copy_entity_result_id
2434: into l_ctrl_rec_cer_id
2435: from ben_copy_entity_results
2434: into l_ctrl_rec_cer_id
2435: from ben_copy_entity_results
2436: where copy_entity_txn_id = p_copy_entity_txn_id
2437: and table_alias = p_business_area;
2438: hr_utility.set_location('ctrl_rec_cer is'||l_ctrl_rec_cer_id,50);
2439: exception
2440: when no_data_found then
2441: hr_utility.set_location('no control rec exists',60);
2442: raise;
2437: and table_alias = p_business_area;
2438: hr_utility.set_location('ctrl_rec_cer is'||l_ctrl_rec_cer_id,50);
2439: exception
2440: when no_data_found then
2441: hr_utility.set_location('no control rec exists',60);
2442: raise;
2443: when others then
2444: hr_utility.set_location('issues in updating control rec ',70);
2445: raise;
2440: when no_data_found then
2441: hr_utility.set_location('no control rec exists',60);
2442: raise;
2443: when others then
2444: hr_utility.set_location('issues in updating control rec ',70);
2445: raise;
2446: end;
2447:
2448: begin
2458: ,information108 = l_cdd_exists
2459: where copy_entity_result_id = l_ctrl_rec_cer_id;
2460: exception
2461: when others then
2462: hr_utility.set_location('issues in updating control rec ',80);
2463: raise;
2464: end;
2465: end update_gsp_control_rec;
2466: procedure create_gsp_control_rec(p_copy_entity_txn_id in number,
2492: p_effective_date => p_effective_date,
2493: p_business_area => p_business_area);
2494: exception
2495: when others then
2496: hr_utility.set_location('issues in creating control rec ',10);
2497: raise;
2498: end create_gsp_control_rec;
2499:
2500: procedure create_gsp_control_rec(p_copy_entity_txn_id in number,
2537: ,p_copy_entity_result_id => l_ctrl_rec_cer_id
2538: ,p_object_version_number => l_ctrl_rec_ovn);
2539: exception
2540: when others then
2541: hr_utility.set_location('issues in creating control rec ',10);
2542: raise;
2543: end create_gsp_control_rec;
2544: function get_bg_grd_pay_rate(p_business_group_id in number) return number is
2545: l_pay_rate_name pay_rates.name%type;
2549: -- having name based on lookup code
2550: l_pay_rate_name := hr_general.decode_lookup(p_lookup_type => 'PQH_GSP_GEN_NAME',
2551: p_lookup_code => 'GRADE_RATEGSP');
2552: if l_pay_rate_name is not null then
2553: hr_utility.set_location('pay rate name'||l_pay_rate_name,10);
2554: hr_utility.set_location('bg id is '||p_business_group_id,15);
2555: -- if the pay rate doesn't exist with this name, no value will be set.
2556: begin
2557: select rate_id
2550: l_pay_rate_name := hr_general.decode_lookup(p_lookup_type => 'PQH_GSP_GEN_NAME',
2551: p_lookup_code => 'GRADE_RATEGSP');
2552: if l_pay_rate_name is not null then
2553: hr_utility.set_location('pay rate name'||l_pay_rate_name,10);
2554: hr_utility.set_location('bg id is '||p_business_group_id,15);
2555: -- if the pay rate doesn't exist with this name, no value will be set.
2556: begin
2557: select rate_id
2558: into l_pay_rate_id
2559: from pay_rates
2560: where name = l_pay_rate_name
2561: and rate_type ='G'
2562: and business_group_id = p_business_group_id;
2563: hr_utility.set_location('rate is '||l_pay_rate_id,20);
2564: return l_pay_rate_id;
2565: exception
2566: when no_data_found then
2567: hr_utility.set_location('no rate is defined for this BG'||p_business_group_id,30);
2563: hr_utility.set_location('rate is '||l_pay_rate_id,20);
2564: return l_pay_rate_id;
2565: exception
2566: when no_data_found then
2567: hr_utility.set_location('no rate is defined for this BG'||p_business_group_id,30);
2568: null;
2569: when others then
2570: hr_utility.set_location('issues in pulling grd pay rate'||p_business_group_id,40);
2571: raise;
2566: when no_data_found then
2567: hr_utility.set_location('no rate is defined for this BG'||p_business_group_id,30);
2568: null;
2569: when others then
2570: hr_utility.set_location('issues in pulling grd pay rate'||p_business_group_id,40);
2571: raise;
2572: end;
2573: else
2574: hr_utility.set_location('pay rate is not defined ',100);
2570: hr_utility.set_location('issues in pulling grd pay rate'||p_business_group_id,40);
2571: raise;
2572: end;
2573: else
2574: hr_utility.set_location('pay rate is not defined ',100);
2575: end if;
2576: end get_bg_grd_pay_rate;
2577: procedure get_grade_details(p_grade_id in number,
2578: p_date_from out nocopy date,
2581: p_grade_name out nocopy varchar2,
2582: p_grd_ovn out nocopy number,
2583: p_grade_definition_id out nocopy number) is
2584: begin
2585: hr_utility.set_location('grade id is'||p_grade_id,20);
2586: -- get the grade info in local variables
2587: select date_from, date_to, short_name, name,
2588: grade_definition_id, object_version_number
2589: into p_date_from,p_date_to,p_short_name,p_grade_name,
2589: into p_date_from,p_date_to,p_short_name,p_grade_name,
2590: p_grade_definition_id,p_grd_ovn
2591: from per_grades
2592: where grade_id = p_grade_id;
2593: hr_utility.set_location('grade name'||p_grade_name,20);
2594: exception
2595: when no_data_found then
2596: hr_utility.set_location('grade doesnot exist '||p_grade_id,35);
2597: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2592: where grade_id = p_grade_id;
2593: hr_utility.set_location('grade name'||p_grade_name,20);
2594: exception
2595: when no_data_found then
2596: hr_utility.set_location('grade doesnot exist '||p_grade_id,35);
2597: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2598: (P_MASTER_TXN_ID => g_master_txn_id,
2599: P_TXN_ID => g_txn_id,
2600: p_context => 'get_grade_details_no_data',
2603: P_MESSAGE_TEXT => Nvl(fnd_Message.Get,sqlerrm),
2604: p_effective_date => trunc(sysdate));
2605: raise;
2606: when others then
2607: hr_utility.set_location('issue in pulling grade details ',120);
2608: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2609: (P_MASTER_TXN_ID => g_master_txn_id,
2610: P_TXN_ID => g_txn_id,
2611: p_context => 'get_grade_details_others',
2626: l_pay_rule_id number;
2627: l_abr_id number;
2628: l_message_text varchar2(2000);
2629: begin
2630: hr_utility.set_location('grade passed is '||p_grade_id,10);
2631: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2632: l_message_text := 'grade passed is '||p_grade_id
2633: ||' effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR');
2634: l_plan_id := get_plan_for_grade(p_grade_id => p_grade_id,
2627: l_abr_id number;
2628: l_message_text varchar2(2000);
2629: begin
2630: hr_utility.set_location('grade passed is '||p_grade_id,10);
2631: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2632: l_message_text := 'grade passed is '||p_grade_id
2633: ||' effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR');
2634: l_plan_id := get_plan_for_grade(p_grade_id => p_grade_id,
2635: p_effective_date => p_effective_date);
2632: l_message_text := 'grade passed is '||p_grade_id
2633: ||' effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR');
2634: l_plan_id := get_plan_for_grade(p_grade_id => p_grade_id,
2635: p_effective_date => p_effective_date);
2636: hr_utility.set_location('plan is '||l_plan_id,30);
2637: if l_plan_id is not null then
2638: l_abr_id := get_co_std_rate(p_plan_id => l_Plan_id,
2639: p_effective_date => p_effective_date,
2640: p_pay_rule_id => l_pay_rule_id);
2639: p_effective_date => p_effective_date,
2640: p_pay_rule_id => l_pay_rule_id);
2641: end if;
2642: if l_pay_rule_id is not null then
2643: hr_utility.set_location('Rate passed is '||l_pay_rule_id,40);
2644: begin
2645: select value,minimum,mid_value,maximum
2646: into p_grd_value,p_grd_min,p_grd_mid,p_grd_max
2647: from pay_grade_rules_f
2650: and p_effective_date between effective_start_date and effective_end_date
2651: and rate_type ='G';
2652: exception
2653: when no_data_found then
2654: hr_utility.set_location('Grade doesnot have a standard rate'||p_grade_id,100);
2655: null;
2656: when others then
2657: hr_utility.set_location('some issues in rate pull',120);
2658: raise;
2653: when no_data_found then
2654: hr_utility.set_location('Grade doesnot have a standard rate'||p_grade_id,100);
2655: null;
2656: when others then
2657: hr_utility.set_location('some issues in rate pull',120);
2658: raise;
2659: end;
2660: else
2661: hr_utility.set_location('no rate defined or issues in getting pay rule ',140);
2657: hr_utility.set_location('some issues in rate pull',120);
2658: raise;
2659: end;
2660: else
2661: hr_utility.set_location('no rate defined or issues in getting pay rule ',140);
2662: end if;
2663: exception
2664: when others then
2665: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2682: -- this procedure takes the hr route of finding the rate value for an grade
2683: -- only issue in this is pay rate name is assumed to be fixed based on lookup value
2684: l_grd_pay_rate_id number;
2685: begin
2686: hr_utility.set_location('grade passed is '||p_grade_id,10);
2687: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2688: l_grd_pay_rate_id := get_bg_grd_pay_rate(p_business_group_id => p_business_group_id);
2689: if l_grd_pay_rate_id is not null then
2690: hr_utility.set_location('Rate passed is '||l_grd_pay_rate_id,40);
2683: -- only issue in this is pay rate name is assumed to be fixed based on lookup value
2684: l_grd_pay_rate_id number;
2685: begin
2686: hr_utility.set_location('grade passed is '||p_grade_id,10);
2687: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2688: l_grd_pay_rate_id := get_bg_grd_pay_rate(p_business_group_id => p_business_group_id);
2689: if l_grd_pay_rate_id is not null then
2690: hr_utility.set_location('Rate passed is '||l_grd_pay_rate_id,40);
2691: begin
2686: hr_utility.set_location('grade passed is '||p_grade_id,10);
2687: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2688: l_grd_pay_rate_id := get_bg_grd_pay_rate(p_business_group_id => p_business_group_id);
2689: if l_grd_pay_rate_id is not null then
2690: hr_utility.set_location('Rate passed is '||l_grd_pay_rate_id,40);
2691: begin
2692: select value,minimum,mid_value,maximum
2693: into p_grd_value,p_grd_min,p_grd_mid,p_grd_max
2694: from pay_grade_rules_f
2697: and p_effective_date between effective_start_date and effective_end_date
2698: and rate_type ='G';
2699: exception
2700: when no_data_found then
2701: hr_utility.set_location('Grade doesnot have a standard rate'||p_grade_id,100);
2702: null;
2703: when others then
2704: hr_utility.set_location('some issues in rate pull',120);
2705: raise;
2700: when no_data_found then
2701: hr_utility.set_location('Grade doesnot have a standard rate'||p_grade_id,100);
2702: null;
2703: when others then
2704: hr_utility.set_location('some issues in rate pull',120);
2705: raise;
2706: end;
2707: else
2708: hr_utility.set_location('issue in getting bg grd pay rate ',120);
2704: hr_utility.set_location('some issues in rate pull',120);
2705: raise;
2706: end;
2707: else
2708: hr_utility.set_location('issue in getting bg grd pay rate ',120);
2709: end if;
2710: exception
2711: when others then
2712: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2727: l_pay_rule_id number;
2728: l_option_id number;
2729: l_abr_id number;
2730: begin
2731: hr_utility.set_location('option is '||p_opt_id,10);
2732: if p_opt_id is not null then
2733: hr_utility.set_location('going for rates',35);
2734: l_abr_id := get_co_std_rate(p_opt_id => p_opt_id,
2735: p_effective_date => p_effective_date,
2729: l_abr_id number;
2730: begin
2731: hr_utility.set_location('option is '||p_opt_id,10);
2732: if p_opt_id is not null then
2733: hr_utility.set_location('going for rates',35);
2734: l_abr_id := get_co_std_rate(p_opt_id => p_opt_id,
2735: p_effective_date => p_effective_date,
2736: p_pay_rule_id => l_pay_rule_id);
2737: end if;
2735: p_effective_date => p_effective_date,
2736: p_pay_rule_id => l_pay_rule_id);
2737: end if;
2738: if l_pay_rule_id is not null then
2739: hr_utility.set_location('Rate passed is '||l_pay_rule_id,40);
2740: begin
2741: select value
2742: into p_point_value
2743: from pay_grade_rules_f
2746: and p_effective_date between effective_start_date and effective_end_date
2747: and rate_type ='SP';
2748: exception
2749: when no_data_found then
2750: hr_utility.set_location('Point doesnot have a standard rate'||p_point_id,100);
2751: null;
2752: when others then
2753: hr_utility.set_location('some issues in rate pull',120);
2754: raise;
2749: when no_data_found then
2750: hr_utility.set_location('Point doesnot have a standard rate'||p_point_id,100);
2751: null;
2752: when others then
2753: hr_utility.set_location('some issues in rate pull',120);
2754: raise;
2755: end;
2756: else
2757: hr_utility.set_location('issue in getting pay rate ',130);
2753: hr_utility.set_location('some issues in rate pull',120);
2754: raise;
2755: end;
2756: else
2757: hr_utility.set_location('issue in getting pay rate ',130);
2758: end if;
2759: exception
2760: when others then
2761: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2775: l_pay_rule_id number;
2776: l_option_id number;
2777: l_abr_id number;
2778: begin
2779: hr_utility.set_location('point passed is '||p_point_id,10);
2780: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2781: l_option_id := get_opt_for_point(p_point_id => p_point_id,
2782: p_effective_date => p_effective_date);
2783: hr_utility.set_location('option is '||l_option_id,30);
2776: l_option_id number;
2777: l_abr_id number;
2778: begin
2779: hr_utility.set_location('point passed is '||p_point_id,10);
2780: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2781: l_option_id := get_opt_for_point(p_point_id => p_point_id,
2782: p_effective_date => p_effective_date);
2783: hr_utility.set_location('option is '||l_option_id,30);
2784: get_point_rate_values(p_effective_date => p_effective_date,
2779: hr_utility.set_location('point passed is '||p_point_id,10);
2780: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2781: l_option_id := get_opt_for_point(p_point_id => p_point_id,
2782: p_effective_date => p_effective_date);
2783: hr_utility.set_location('option is '||l_option_id,30);
2784: get_point_rate_values(p_effective_date => p_effective_date,
2785: p_opt_id => l_option_id,
2786: p_point_id => p_point_id,
2787: p_point_value => p_point_value);
2793: -- this procedure takes the hr route of finding the rate value for an grade
2794: -- only issue in this is pay rate name is assumed to be fixed based on scale name
2795: l_scl_pay_rate_id number;
2796: begin
2797: hr_utility.set_location('scale passed is '||p_scale_id,10);
2798: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2799: l_scl_pay_rate_id := get_scl_pay_rate(p_scale_id => p_scale_id);
2800: if l_scl_pay_rate_id is not null then
2801: hr_utility.set_location('Rate passed is '||l_scl_pay_rate_id,40);
2794: -- only issue in this is pay rate name is assumed to be fixed based on scale name
2795: l_scl_pay_rate_id number;
2796: begin
2797: hr_utility.set_location('scale passed is '||p_scale_id,10);
2798: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2799: l_scl_pay_rate_id := get_scl_pay_rate(p_scale_id => p_scale_id);
2800: if l_scl_pay_rate_id is not null then
2801: hr_utility.set_location('Rate passed is '||l_scl_pay_rate_id,40);
2802: begin
2797: hr_utility.set_location('scale passed is '||p_scale_id,10);
2798: hr_utility.set_location('effective date passed is '||to_char(p_effective_date,'DD-MM-RRRR'),20);
2799: l_scl_pay_rate_id := get_scl_pay_rate(p_scale_id => p_scale_id);
2800: if l_scl_pay_rate_id is not null then
2801: hr_utility.set_location('Rate passed is '||l_scl_pay_rate_id,40);
2802: begin
2803: select value
2804: into p_point_value
2805: from pay_grade_rules_f
2808: and p_effective_date between effective_start_date and effective_end_date
2809: and rate_type ='SP';
2810: exception
2811: when no_data_found then
2812: hr_utility.set_location('Point doesnot have a standard rate'||p_point_id,100);
2813: null;
2814: when others then
2815: hr_utility.set_location('some issues in rate pull',120);
2816: raise;
2811: when no_data_found then
2812: hr_utility.set_location('Point doesnot have a standard rate'||p_point_id,100);
2813: null;
2814: when others then
2815: hr_utility.set_location('some issues in rate pull',120);
2816: raise;
2817: end;
2818: else
2819: hr_utility.set_location('issue in getting scl pay rate ',130);
2815: hr_utility.set_location('some issues in rate pull',120);
2816: raise;
2817: end;
2818: else
2819: hr_utility.set_location('issue in getting scl pay rate ',130);
2820: end if;
2821: exception
2822: when others then
2823: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2846: l_point_ovn number;
2847: l_ceiling_step_id number;
2848: l_starting_step number;
2849: begin
2850: hr_utility.set_location('point id is'||p_point_id,10);
2851: if p_point_id is not null then
2852: get_point_details(p_point_id => p_point_id,
2853: p_scale_id => p_scale_id,
2854: p_point_seq => l_point_seq,
2853: p_scale_id => p_scale_id,
2854: p_point_seq => l_point_seq,
2855: p_point_ovn => l_point_ovn,
2856: p_spinal_point => p_point_name);
2857: hr_utility.set_location('scale id is'||p_scale_id,20);
2858: get_point_rate_values(p_effective_date => p_effective_date,
2859: p_opt_id => p_option_id,
2860: p_point_id => p_point_id,
2861: p_point_value => p_point_value);
2858: get_point_rate_values(p_effective_date => p_effective_date,
2859: p_opt_id => p_option_id,
2860: p_point_id => p_point_id,
2861: p_point_value => p_point_value);
2862: hr_utility.set_location('point value is'||p_point_value,30);
2863: end if;
2864: if p_grade_id is not null and p_scale_id is not null then
2865: get_grade_scale_details(p_grade_id => p_grade_id,
2866: p_scale_id => p_scale_id,
2868: p_ceiling_step_id => l_ceiling_step_id,
2869: p_grade_spine_id => p_grade_spine_id,
2870: p_starting_step => l_starting_step);
2871:
2872: hr_utility.set_location('grade spine id is'||p_grade_spine_id,40);
2873: end if;
2874: if p_grade_spine_id is not null then
2875: get_step_details(p_grade_spine_id => p_grade_spine_id,
2876: p_point_id => p_point_id,
2877: p_effective_date => p_effective_date,
2878: p_step_id => p_step_id,
2879: p_step_ovn => p_step_ovn,
2880: p_step_name => p_step_name) ;
2881: hr_utility.set_location('step id is'||p_step_id,50);
2882: if l_ceiling_step_id = p_step_id then
2883: p_ceiling_flag := 'Y';
2884: else
2885: p_ceiling_flag := 'N';
2913: l_plan_id number;
2914: begin
2915: -- because oipl is only in ben, we will pick plan and option and use that to get the details
2916: if p_step_id is null and p_oipl_id is not null then
2917: hr_utility.set_location('oipl_id is '||p_oipl_id,10);
2918: begin
2919: select opt_id,pl_id
2920: into l_option_id,l_plan_id
2921: from ben_oipl_f
2920: into l_option_id,l_plan_id
2921: from ben_oipl_f
2922: where oipl_id = p_oipl_id
2923: and p_effective_date between effective_start_date and effective_end_date;
2924: hr_utility.set_location('option is '||l_option_id,10);
2925: hr_utility.set_location('plan is '||l_plan_id,15);
2926: exception
2927: when no_data_found then
2928: hr_utility.set_location('oipl does not exist '||p_oipl_id,18);
2921: from ben_oipl_f
2922: where oipl_id = p_oipl_id
2923: and p_effective_date between effective_start_date and effective_end_date;
2924: hr_utility.set_location('option is '||l_option_id,10);
2925: hr_utility.set_location('plan is '||l_plan_id,15);
2926: exception
2927: when no_data_found then
2928: hr_utility.set_location('oipl does not exist '||p_oipl_id,18);
2929: raise;
2924: hr_utility.set_location('option is '||l_option_id,10);
2925: hr_utility.set_location('plan is '||l_plan_id,15);
2926: exception
2927: when no_data_found then
2928: hr_utility.set_location('oipl does not exist '||p_oipl_id,18);
2929: raise;
2930: when others then
2931: hr_utility.set_location('issues in selected oipl',20);
2932: raise;
2927: when no_data_found then
2928: hr_utility.set_location('oipl does not exist '||p_oipl_id,18);
2929: raise;
2930: when others then
2931: hr_utility.set_location('issues in selected oipl',20);
2932: raise;
2933: end;
2934: -- get point for option
2935: p_point_id := get_point_for_opt(p_option_id => l_option_id,
2933: end;
2934: -- get point for option
2935: p_point_id := get_point_for_opt(p_option_id => l_option_id,
2936: p_effective_date => p_effective_date);
2937: hr_utility.set_location('spinal_point_id is'||p_point_id,40);
2938: -- get grade for plan
2939: p_grade_id := get_grade_for_plan(p_plan_id => l_plan_id,
2940: p_effective_date => p_effective_date);
2941: hr_utility.set_location('grade id is'||p_grade_id,50);
2937: hr_utility.set_location('spinal_point_id is'||p_point_id,40);
2938: -- get grade for plan
2939: p_grade_id := get_grade_for_plan(p_plan_id => l_plan_id,
2940: p_effective_date => p_effective_date);
2941: hr_utility.set_location('grade id is'||p_grade_id,50);
2942: elsif p_step_id is not null then
2943: hr_utility.set_location('step_id is '||p_step_id,10);
2944: begin
2945: select sps.spinal_point_id,gs.grade_id, gs.grade_spine_id
2939: p_grade_id := get_grade_for_plan(p_plan_id => l_plan_id,
2940: p_effective_date => p_effective_date);
2941: hr_utility.set_location('grade id is'||p_grade_id,50);
2942: elsif p_step_id is not null then
2943: hr_utility.set_location('step_id is '||p_step_id,10);
2944: begin
2945: select sps.spinal_point_id,gs.grade_id, gs.grade_spine_id
2946: into p_point_id,p_grade_id,p_grade_spine_id
2947: from per_spinal_point_steps_f sps, per_grade_spines_f gs
2948: where sps.step_id = p_step_id
2949: and sps.grade_spine_id = gs.grade_spine_id
2950: and p_effective_date between sps.effective_start_date and sps.effective_end_date
2951: and p_effective_date between gs.effective_start_date and gs.effective_end_date;
2952: hr_utility.set_location('point is '||p_point_id,10);
2953: hr_utility.set_location('grade is '||p_grade_id,15);
2954: exception
2955: when no_data_found then
2956: hr_utility.set_location('step does not exist '||p_step_id,18);
2949: and sps.grade_spine_id = gs.grade_spine_id
2950: and p_effective_date between sps.effective_start_date and sps.effective_end_date
2951: and p_effective_date between gs.effective_start_date and gs.effective_end_date;
2952: hr_utility.set_location('point is '||p_point_id,10);
2953: hr_utility.set_location('grade is '||p_grade_id,15);
2954: exception
2955: when no_data_found then
2956: hr_utility.set_location('step does not exist '||p_step_id,18);
2957: raise;
2952: hr_utility.set_location('point is '||p_point_id,10);
2953: hr_utility.set_location('grade is '||p_grade_id,15);
2954: exception
2955: when no_data_found then
2956: hr_utility.set_location('step does not exist '||p_step_id,18);
2957: raise;
2958: when others then
2959: hr_utility.set_location('issues in selected step',20);
2960: raise;
2955: when no_data_found then
2956: hr_utility.set_location('step does not exist '||p_step_id,18);
2957: raise;
2958: when others then
2959: hr_utility.set_location('issues in selected step',20);
2960: raise;
2961: end;
2962: else
2963: hr_utility.set_location('oipl id is null and step id is null ',10);
2959: hr_utility.set_location('issues in selected step',20);
2960: raise;
2961: end;
2962: else
2963: hr_utility.set_location('oipl id is null and step id is null ',10);
2964: end if;
2965: if p_point_id is not null and p_grade_id is not null then
2966: get_step_all_details(p_point_id => p_point_id,
2967: p_effective_date => p_effective_date,
2974: p_grade_spine_id => p_grade_spine_id,
2975: p_ceiling_flag => p_ceiling_flag,
2976: p_point_value => p_point_value,
2977: p_scale_id => p_scale_id);
2978: hr_utility.set_location('details pulled',60);
2979: end if;
2980: end get_step_details_for_oipl;
2981: procedure get_co_for_abr(p_abr_id in number,
2982: p_effective_date in date,
2988: into p_plan_id, p_option_id,p_pay_rule_id
2989: from ben_acty_base_rt_f
2990: where acty_base_rt_id = p_abr_id
2991: and p_effective_date between effective_start_date and effective_end_date;
2992: hr_utility.set_location('rate is for plan'||p_plan_id,1);
2993: hr_utility.set_location('rate is for opt'||p_option_id,2);
2994: exception
2995: when no_data_found then
2996: hr_utility.set_location('no data exists '||p_abr_id,10);
2989: from ben_acty_base_rt_f
2990: where acty_base_rt_id = p_abr_id
2991: and p_effective_date between effective_start_date and effective_end_date;
2992: hr_utility.set_location('rate is for plan'||p_plan_id,1);
2993: hr_utility.set_location('rate is for opt'||p_option_id,2);
2994: exception
2995: when no_data_found then
2996: hr_utility.set_location('no data exists '||p_abr_id,10);
2997: raise;
2992: hr_utility.set_location('rate is for plan'||p_plan_id,1);
2993: hr_utility.set_location('rate is for opt'||p_option_id,2);
2994: exception
2995: when no_data_found then
2996: hr_utility.set_location('no data exists '||p_abr_id,10);
2997: raise;
2998: when others then
2999: hr_utility.set_location('issues in pulling txn data ',20);
3000: raise;
2995: when no_data_found then
2996: hr_utility.set_location('no data exists '||p_abr_id,10);
2997: raise;
2998: when others then
2999: hr_utility.set_location('issues in pulling txn data ',20);
3000: raise;
3001: end get_co_for_abr;
3002: procedure get_grd_pay_rate_values(p_grade_id in number,
3003: p_pay_rule_id in number,
3013: where grade_rule_id = p_pay_rule_id
3014: and rate_type ='G'
3015: and grade_or_spinal_point_id = p_grade_id
3016: and p_effective_date between effective_start_date and effective_end_date;
3017: hr_utility.set_location('grade value is '||p_grd_value,10);
3018: exception
3019: when no_data_found then
3020: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3021: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3016: and p_effective_date between effective_start_date and effective_end_date;
3017: hr_utility.set_location('grade value is '||p_grd_value,10);
3018: exception
3019: when no_data_found then
3020: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3021: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3022: raise;
3023: when others then
3024: hr_utility.set_location('issues in pulling grade rate data ',40);
3017: hr_utility.set_location('grade value is '||p_grd_value,10);
3018: exception
3019: when no_data_found then
3020: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3021: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3022: raise;
3023: when others then
3024: hr_utility.set_location('issues in pulling grade rate data ',40);
3025: raise;
3020: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3021: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3022: raise;
3023: when others then
3024: hr_utility.set_location('issues in pulling grade rate data ',40);
3025: raise;
3026: end get_grd_pay_rate_values;
3027: procedure get_point_pay_rate_values(p_point_id in number,
3028: p_pay_rule_id in number,
3035: where grade_rule_id = p_pay_rule_id
3036: and rate_type ='SP'
3037: and grade_or_spinal_point_id = p_point_id
3038: and p_effective_date between effective_start_date and effective_end_date;
3039: hr_utility.set_location('point value is '||p_point_value,10);
3040: exception
3041: when no_data_found then
3042: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3043: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3038: and p_effective_date between effective_start_date and effective_end_date;
3039: hr_utility.set_location('point value is '||p_point_value,10);
3040: exception
3041: when no_data_found then
3042: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3043: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3044: raise;
3045: when others then
3046: hr_utility.set_location('issues in pulling point rate data ',40);
3039: hr_utility.set_location('point value is '||p_point_value,10);
3040: exception
3041: when no_data_found then
3042: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3043: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3044: raise;
3045: when others then
3046: hr_utility.set_location('issues in pulling point rate data ',40);
3047: raise;
3042: hr_utility.set_location('no rule exists '||p_pay_rule_id,20);
3043: hr_utility.set_location('as of this date'||to_char(p_effective_date,'DD-MM-RRRR'),30);
3044: raise;
3045: when others then
3046: hr_utility.set_location('issues in pulling point rate data ',40);
3047: raise;
3048: end get_point_pay_rate_values;
3049: procedure pgm_on_stage(p_pgm_id in number,
3050: p_pgm_cer_id in number,
3070: where table_alias = 'LEN'
3071: and information257 = p_ler_id
3072: and copy_entity_txn_id = p_copy_entity_txn_id;
3073: begin
3074: hr_utility.set_location('inside pgm update ',10);
3075: begin
3076: select information16,information14,information4
3077: into l_sal_update_cd,l_dflt_step_cd,l_business_group_id
3078: from ben_copy_entity_results
3086: end if;
3087: close csr_ler_id;
3088: exception
3089: when others then
3090: hr_utility.set_location('issues in updating pgm ',20);
3091: raise;
3092: end;
3093: if nvl(l_sal_update_cd,'NO_UPDATE') = 'NO_UPDATE' then
3094: l_sal_upd_flag := 'N';
3095: l_sal_update_cd := '';
3096: elsif l_sal_update_cd in ('SALARY_ELEMENT','SALARY_BASIS') then
3097: l_sal_upd_flag := 'Y';
3098: else
3099: hr_utility.set_location('invalid sal upd ',30);
3100: end if;
3101: if l_dflt_step_cd in ('PQH_GSP_GP','PQH_GSP_SP','PQH_GSP_NP') then
3102: l_enrt_cd := l_dflt_step_cd;
3103: l_dflt_step_cd := '';
3103: l_dflt_step_cd := '';
3104: else
3105: l_enrt_cd := 'PQH_GSP_GSP';
3106: end if;
3107: hr_utility.set_location('prog_style is '||l_enrt_cd,35);
3108: begin
3109: update ben_copy_entity_results
3110: set information_category = 'GRADE_LADDER',
3111: information16 = l_sal_upd_flag,
3116: dml_operation = 'UPDATE'
3117: where copy_entity_result_id = p_pgm_cer_id;
3118: exception
3119: when others then
3120: hr_utility.set_location('issues in updating pgm ',40);
3121: raise;
3122: end;
3123: hr_utility.set_location('done pgm update ',50);
3124: end pgm_on_stage;
3119: when others then
3120: hr_utility.set_location('issues in updating pgm ',40);
3121: raise;
3122: end;
3123: hr_utility.set_location('done pgm update ',50);
3124: end pgm_on_stage;
3125: procedure hrate_to_stage(p_abr_cer_id in number,
3126: p_copy_entity_txn_id in number,
3127: p_effective_date in date,
3134: l_point_id number;
3135: l_pl_cer_id number;
3136: l_opt_cer_id number;
3137: begin
3138: hr_utility.set_location('inside standard rate ',10);
3139: get_co_for_abr(p_abr_id => p_abr_id,
3140: p_effective_date => p_effective_date,
3141: p_plan_id => l_plan_id,
3142: p_option_id => l_option_id,
3140: p_effective_date => p_effective_date,
3141: p_plan_id => l_plan_id,
3142: p_option_id => l_option_id,
3143: p_pay_rule_id => l_pay_rule_id);
3144: hr_utility.set_location('comp object selected ',20);
3145: if l_plan_id is not null then
3146: hr_utility.set_location('its plan '||l_plan_id,30);
3147: l_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3148: p_pl_id => l_plan_id);
3142: p_option_id => l_option_id,
3143: p_pay_rule_id => l_pay_rule_id);
3144: hr_utility.set_location('comp object selected ',20);
3145: if l_plan_id is not null then
3146: hr_utility.set_location('its plan '||l_plan_id,30);
3147: l_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3148: p_pl_id => l_plan_id);
3149: hr_utility.set_location('pl cer is '||l_pl_cer_id,35);
3150: l_grade_id := get_grade_for_plan(p_plan_id => l_plan_id,
3145: if l_plan_id is not null then
3146: hr_utility.set_location('its plan '||l_plan_id,30);
3147: l_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3148: p_pl_id => l_plan_id);
3149: hr_utility.set_location('pl cer is '||l_pl_cer_id,35);
3150: l_grade_id := get_grade_for_plan(p_plan_id => l_plan_id,
3151: p_effective_date => p_effective_date);
3152: hr_utility.set_location('grade is '||l_grade_id,40);
3153: if l_grade_id is not null and p_abr_id is not null then
3148: p_pl_id => l_plan_id);
3149: hr_utility.set_location('pl cer is '||l_pl_cer_id,35);
3150: l_grade_id := get_grade_for_plan(p_plan_id => l_plan_id,
3151: p_effective_date => p_effective_date);
3152: hr_utility.set_location('grade is '||l_grade_id,40);
3153: if l_grade_id is not null and p_abr_id is not null then
3154: pqh_gsp_rates.create_grade_hrrate
3155: (p_copy_entity_txn_id => p_copy_entity_txn_id,
3156: p_effective_date => p_effective_date,
3157: p_abr_id => p_abr_id,
3158: p_abr_cer_id => p_abr_cer_id,
3159: p_pay_rule_id => l_pay_rule_id,
3160: p_grade_id => l_grade_id);
3161: hr_utility.set_location('grade hrrate created ',45);
3162: end if;
3163: elsif l_option_id is not null then
3164: hr_utility.set_location('its point '||l_option_id,50);
3165: l_opt_cer_id := is_option_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3160: p_grade_id => l_grade_id);
3161: hr_utility.set_location('grade hrrate created ',45);
3162: end if;
3163: elsif l_option_id is not null then
3164: hr_utility.set_location('its point '||l_option_id,50);
3165: l_opt_cer_id := is_option_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3166: p_opt_id => l_option_id);
3167: hr_utility.set_location('opt cer is '||l_opt_cer_id,55);
3168: l_point_id := get_point_for_opt(p_option_id => l_option_id,
3163: elsif l_option_id is not null then
3164: hr_utility.set_location('its point '||l_option_id,50);
3165: l_opt_cer_id := is_option_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3166: p_opt_id => l_option_id);
3167: hr_utility.set_location('opt cer is '||l_opt_cer_id,55);
3168: l_point_id := get_point_for_opt(p_option_id => l_option_id,
3169: p_effective_date => p_effective_date);
3170: hr_utility.set_location('point is '||l_point_id,60);
3171: if l_point_id is not null and p_abr_id is not null then
3166: p_opt_id => l_option_id);
3167: hr_utility.set_location('opt cer is '||l_opt_cer_id,55);
3168: l_point_id := get_point_for_opt(p_option_id => l_option_id,
3169: p_effective_date => p_effective_date);
3170: hr_utility.set_location('point is '||l_point_id,60);
3171: if l_point_id is not null and p_abr_id is not null then
3172: pqh_gsp_rates.create_point_hrrate
3173: (p_copy_entity_txn_id => p_copy_entity_txn_id,
3174: p_effective_date => p_effective_date,
3175: p_abr_id => p_abr_id,
3176: p_abr_cer_id => p_abr_cer_id,
3177: p_pay_rule_id => l_pay_rule_id,
3178: p_point_id => l_point_id);
3179: hr_utility.set_location('point hrrate created ',70);
3180: end if;
3181: end if;
3182: if l_pl_cer_id is null and l_opt_cer_id is null then
3183: hr_utility.set_location('rate is for something else',100);
3179: hr_utility.set_location('point hrrate created ',70);
3180: end if;
3181: end if;
3182: if l_pl_cer_id is null and l_opt_cer_id is null then
3183: hr_utility.set_location('rate is for something else',100);
3184: else
3185: begin
3186: update ben_copy_entity_results
3187: set information277 = l_pl_cer_id,
3189: gs_mirror_src_entity_result_id = mirror_src_entity_result_id
3190: where copy_entity_result_id = p_abr_cer_id;
3191: exception
3192: when others then
3193: hr_utility.set_location('issue in update abr with co cer',120);
3194: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3195: (P_MASTER_TXN_ID => g_master_txn_id,
3196: P_TXN_ID => g_txn_id,
3197: p_context => 'hrate_to_stage',
3217: and p_effective_date between gs.effective_start_date and gs.effective_end_date
3218: and copy_entity_result_id > nvl(p_start_cer_id,0);
3219: l_scale_cer_id number;
3220: begin
3221: hr_utility.set_location('inside pre-process ',10);
3222: hr_utility.set_location('bus_area is '||p_business_area,10);
3223: for i in csr_grd_scales loop
3224: hr_utility.set_location('copying scale for grade'||i.grade_id,20);
3225: populate_scale_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
3218: and copy_entity_result_id > nvl(p_start_cer_id,0);
3219: l_scale_cer_id number;
3220: begin
3221: hr_utility.set_location('inside pre-process ',10);
3222: hr_utility.set_location('bus_area is '||p_business_area,10);
3223: for i in csr_grd_scales loop
3224: hr_utility.set_location('copying scale for grade'||i.grade_id,20);
3225: populate_scale_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
3226: p_effective_date => p_effective_date,
3220: begin
3221: hr_utility.set_location('inside pre-process ',10);
3222: hr_utility.set_location('bus_area is '||p_business_area,10);
3223: for i in csr_grd_scales loop
3224: hr_utility.set_location('copying scale for grade'||i.grade_id,20);
3225: populate_scale_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
3226: p_effective_date => p_effective_date,
3227: p_business_group_id => p_business_group_id,
3228: p_business_area => p_business_area,
3229: p_scale_id => i.scale_id,
3230: p_mode => 'BAREBONE',
3231: p_scale_cer_id => l_scale_cer_id);
3232: end loop;
3233: hr_utility.set_location('done pre-process ',40);
3234: end pre_pull_process;
3235:
3236: procedure pgi_to_stage(p_pgm_id in number,
3237: p_copy_entity_txn_id in number) is
3244: FROM ben_pgm_extra_info
3245: where information_type ='PQH_GSP_EXTRA_INFO'
3246: and pgm_id = p_pgm_id;
3247: begin
3248: hr_utility.set_location('pgm id is'||p_pgm_id,10);
3249: if p_pgm_id is not null then
3250: for i in c1 loop
3251: l_old_flag := 'N';
3252: BEN_COPY_ENTITY_RESULTS_API.CREATE_COPY_ENTITY_RESULTS(
3286: end if;
3287: end if;
3288: exception
3289: when no_data_found then
3290: hr_utility.set_location('no extra info ',10);
3291: when others then
3292: hr_utility.set_location('issues is selecting pgm extra info',10);
3293: raise;
3294: end pgi_to_stage;
3288: exception
3289: when no_data_found then
3290: hr_utility.set_location('no extra info ',10);
3291: when others then
3292: hr_utility.set_location('issues is selecting pgm extra info',10);
3293: raise;
3294: end pgi_to_stage;
3295:
3296: procedure hr_to_stage(p_copy_entity_txn_id in number,
3334: l_pgm_id number;
3335: l_pgm_cer_id number;
3336: begin
3337: -- for all the pln rows get the scale attached and populate it to staging area
3338: hr_utility.set_location('start cer is'||p_start_cer_id,5);
3339: pre_pull_process(p_copy_entity_txn_id => p_copy_entity_txn_id,
3340: p_start_cer_id => p_start_cer_id,
3341: p_effective_date => p_effective_date,
3342: p_business_area => p_business_area,
3341: p_effective_date => p_effective_date,
3342: p_business_area => p_business_area,
3343: p_business_group_id => p_business_group_id);
3344: for txn_cer_rec in csr_txn_cer loop
3345: hr_utility.set_location('inside cer loop'||txn_cer_rec.copy_entity_result_id,10);
3346: begin
3347: if txn_cer_rec.table_alias ='PLN' then
3348: -- it's a plan row, grade is mapped to plan
3349: hr_utility.set_location('its plan row ',20);
3345: hr_utility.set_location('inside cer loop'||txn_cer_rec.copy_entity_result_id,10);
3346: begin
3347: if txn_cer_rec.table_alias ='PLN' then
3348: -- it's a plan row, grade is mapped to plan
3349: hr_utility.set_location('its plan row ',20);
3350: l_grade_id := get_grade_for_plan(p_plan_id => txn_cer_rec.information1,
3351: p_effective_date => p_effective_date);
3352: grade_to_pl_stage(p_grade_id => l_grade_id,
3353: p_pl_cer_id => txn_cer_rec.copy_entity_result_id,
3351: p_effective_date => p_effective_date);
3352: grade_to_pl_stage(p_grade_id => l_grade_id,
3353: p_pl_cer_id => txn_cer_rec.copy_entity_result_id,
3354: p_effective_date => p_effective_date);
3355: hr_utility.set_location('plan row updated',22);
3356: elsif txn_cer_rec.table_alias ='PGM' then
3357: hr_utility.set_location('its PGM row ',25);
3358: pgm_on_stage(p_pgm_id => txn_cer_rec.information1,
3359: p_pgm_cer_id => txn_cer_rec.copy_entity_result_id,
3353: p_pl_cer_id => txn_cer_rec.copy_entity_result_id,
3354: p_effective_date => p_effective_date);
3355: hr_utility.set_location('plan row updated',22);
3356: elsif txn_cer_rec.table_alias ='PGM' then
3357: hr_utility.set_location('its PGM row ',25);
3358: pgm_on_stage(p_pgm_id => txn_cer_rec.information1,
3359: p_pgm_cer_id => txn_cer_rec.copy_entity_result_id,
3360: p_copy_entity_txn_id =>p_copy_entity_txn_id);
3361: hr_utility.set_location('PGM row updated',28);
3357: hr_utility.set_location('its PGM row ',25);
3358: pgm_on_stage(p_pgm_id => txn_cer_rec.information1,
3359: p_pgm_cer_id => txn_cer_rec.copy_entity_result_id,
3360: p_copy_entity_txn_id =>p_copy_entity_txn_id);
3361: hr_utility.set_location('PGM row updated',28);
3362: l_pgm_id := txn_cer_rec.information1;
3363: l_pgm_cer_id := txn_cer_rec.copy_entity_result_id;
3364: pgi_to_stage(p_pgm_id => l_pgm_id,
3365: p_copy_entity_txn_id => p_copy_entity_txn_id);
3362: l_pgm_id := txn_cer_rec.information1;
3363: l_pgm_cer_id := txn_cer_rec.copy_entity_result_id;
3364: pgi_to_stage(p_pgm_id => l_pgm_id,
3365: p_copy_entity_txn_id => p_copy_entity_txn_id);
3366: hr_utility.set_location('PGI row updated',28);
3367: elsif txn_cer_rec.table_alias ='CPP' and txn_cer_rec.information1 is not null then
3368: -- it's a plip row
3369: hr_utility.set_location('its plip row ',30);
3370: get_grade_for_plip(p_plip_id => txn_cer_rec.information1,
3365: p_copy_entity_txn_id => p_copy_entity_txn_id);
3366: hr_utility.set_location('PGI row updated',28);
3367: elsif txn_cer_rec.table_alias ='CPP' and txn_cer_rec.information1 is not null then
3368: -- it's a plip row
3369: hr_utility.set_location('its plip row ',30);
3370: get_grade_for_plip(p_plip_id => txn_cer_rec.information1,
3371: p_effective_date => p_effective_date,
3372: p_plan_id => l_plan_id,
3373: p_grade_id => l_grade_id);
3370: get_grade_for_plip(p_plip_id => txn_cer_rec.information1,
3371: p_effective_date => p_effective_date,
3372: p_plan_id => l_plan_id,
3373: p_grade_id => l_grade_id);
3374: hr_utility.set_location('grade is '||l_grade_id,32);
3375: hr_utility.set_location('plan is '||l_plan_id,34);
3376: if l_plan_id is not null then
3377: l_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3378: p_pl_id => l_plan_id);
3371: p_effective_date => p_effective_date,
3372: p_plan_id => l_plan_id,
3373: p_grade_id => l_grade_id);
3374: hr_utility.set_location('grade is '||l_grade_id,32);
3375: hr_utility.set_location('plan is '||l_plan_id,34);
3376: if l_plan_id is not null then
3377: l_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3378: p_pl_id => l_plan_id);
3379: hr_utility.set_location('pl cer is'||l_pl_cer_id,10);
3375: hr_utility.set_location('plan is '||l_plan_id,34);
3376: if l_plan_id is not null then
3377: l_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3378: p_pl_id => l_plan_id);
3379: hr_utility.set_location('pl cer is'||l_pl_cer_id,10);
3380: end if;
3381: grade_to_plip_stage(p_grade_id => l_grade_id,
3382: p_plip_cer_id => txn_cer_rec.copy_entity_result_id,
3383: p_pl_cer_id => l_pl_cer_id,
3384: p_parent_cer_id => txn_cer_rec.parent_entity_result_id,
3385: p_mirror_ser_id => txn_cer_rec.mirror_src_entity_result_id,
3386: p_effective_date => p_effective_date,
3387: p_business_area => p_business_area);
3388: hr_utility.set_location('plip rec updated ',38);
3389: elsif txn_cer_rec.table_alias ='OPT' then
3390: -- it's a option row
3391: hr_utility.set_location('its option row ',40);
3392: point_to_opt_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
3387: p_business_area => p_business_area);
3388: hr_utility.set_location('plip rec updated ',38);
3389: elsif txn_cer_rec.table_alias ='OPT' then
3390: -- it's a option row
3391: hr_utility.set_location('its option row ',40);
3392: point_to_opt_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
3393: p_option_id => txn_cer_rec.information1,
3394: p_opt_cer_id => txn_cer_rec.copy_entity_result_id,
3395: p_effective_date => p_effective_date,
3394: p_opt_cer_id => txn_cer_rec.copy_entity_result_id,
3395: p_effective_date => p_effective_date,
3396: p_business_area => p_business_area,
3397: p_business_group_id => p_business_group_id);
3398: hr_utility.set_location('option row updated',42);
3399: elsif txn_cer_rec.table_alias ='COP' then
3400: -- it's a OIPL row
3401: hr_utility.set_location('its oipl row ',50);
3402: step_to_oipl_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
3397: p_business_group_id => p_business_group_id);
3398: hr_utility.set_location('option row updated',42);
3399: elsif txn_cer_rec.table_alias ='COP' then
3400: -- it's a OIPL row
3401: hr_utility.set_location('its oipl row ',50);
3402: step_to_oipl_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
3403: p_oipl_cer_id => txn_cer_rec.copy_entity_result_id,
3404: p_oipl_id => txn_cer_rec.information1,
3405: p_step_id => txn_cer_rec.information253,
3405: p_step_id => txn_cer_rec.information253,
3406: p_parent_cer_id => txn_cer_rec.parent_entity_result_id,
3407: p_effective_date => p_effective_date,
3408: p_business_area => p_business_area);
3409: hr_utility.set_location('oipl row updated',52);
3410: elsif txn_cer_rec.table_alias ='ABR' then
3411: -- it's a Rate row
3412: hr_utility.set_location('its ABR row ',60);
3413: hrate_to_stage(p_abr_cer_id => txn_cer_rec.copy_entity_result_id,
3408: p_business_area => p_business_area);
3409: hr_utility.set_location('oipl row updated',52);
3410: elsif txn_cer_rec.table_alias ='ABR' then
3411: -- it's a Rate row
3412: hr_utility.set_location('its ABR row ',60);
3413: hrate_to_stage(p_abr_cer_id => txn_cer_rec.copy_entity_result_id,
3414: p_effective_date => p_effective_date,
3415: p_copy_entity_txn_id => p_copy_entity_txn_id,
3416: p_abr_id => txn_cer_rec.information1,
3414: p_effective_date => p_effective_date,
3415: p_copy_entity_txn_id => p_copy_entity_txn_id,
3416: p_abr_id => txn_cer_rec.information1,
3417: p_parent_cer_id => txn_cer_rec.parent_entity_result_id);
3418: hr_utility.set_location('ABR row updated',62);
3419: elsif txn_cer_rec.table_alias ='VPF' then
3420: -- it's a variable rate row
3421: hr_utility.set_location('its VPF row ',70);
3422: vpf_to_stage(p_vpf_cer_id => txn_cer_rec.copy_entity_result_id,
3417: p_parent_cer_id => txn_cer_rec.parent_entity_result_id);
3418: hr_utility.set_location('ABR row updated',62);
3419: elsif txn_cer_rec.table_alias ='VPF' then
3420: -- it's a variable rate row
3421: hr_utility.set_location('its VPF row ',70);
3422: vpf_to_stage(p_vpf_cer_id => txn_cer_rec.copy_entity_result_id,
3423: p_copy_entity_txn_id => p_copy_entity_txn_id,
3424: p_effective_date => p_effective_date,
3425: p_result_type_cd => txn_cer_rec.result_type_cd);
3424: p_effective_date => p_effective_date,
3425: p_result_type_cd => txn_cer_rec.result_type_cd);
3426: elsif txn_cer_rec.table_alias ='ELP' then
3427: -- it's a eligibility profile row
3428: hr_utility.set_location('its ELP row ',80);
3429: elp_to_stage(p_elp_cer_id => txn_cer_rec.copy_entity_result_id,
3430: p_copy_entity_txn_id => p_copy_entity_txn_id,
3431: p_effective_date => p_effective_date,
3432: p_elpro_id => txn_cer_rec.information1,
3430: p_copy_entity_txn_id => p_copy_entity_txn_id,
3431: p_effective_date => p_effective_date,
3432: p_elpro_id => txn_cer_rec.information1,
3433: p_business_group_id => p_business_group_id);
3434: hr_utility.set_location('ELP row updated',85);
3435: elsif txn_cer_rec.table_alias ='EPA' then
3436: hr_utility.set_location('its EPA row ',90);
3437: epa_to_stage(p_epa_cer_id => txn_cer_rec.copy_entity_result_id,
3438: p_copy_entity_txn_id => p_copy_entity_txn_id);
3432: p_elpro_id => txn_cer_rec.information1,
3433: p_business_group_id => p_business_group_id);
3434: hr_utility.set_location('ELP row updated',85);
3435: elsif txn_cer_rec.table_alias ='EPA' then
3436: hr_utility.set_location('its EPA row ',90);
3437: epa_to_stage(p_epa_cer_id => txn_cer_rec.copy_entity_result_id,
3438: p_copy_entity_txn_id => p_copy_entity_txn_id);
3439: hr_utility.set_location('Epa row updated',100);
3440: elsif txn_cer_rec.table_alias ='CEP' then
3435: elsif txn_cer_rec.table_alias ='EPA' then
3436: hr_utility.set_location('its EPA row ',90);
3437: epa_to_stage(p_epa_cer_id => txn_cer_rec.copy_entity_result_id,
3438: p_copy_entity_txn_id => p_copy_entity_txn_id);
3439: hr_utility.set_location('Epa row updated',100);
3440: elsif txn_cer_rec.table_alias ='CEP' then
3441: hr_utility.set_location('its cep row ',110);
3442: cep_to_stage(p_cep_cer_id => txn_cer_rec.copy_entity_result_id,
3443: p_copy_entity_txn_id => p_copy_entity_txn_id);
3437: epa_to_stage(p_epa_cer_id => txn_cer_rec.copy_entity_result_id,
3438: p_copy_entity_txn_id => p_copy_entity_txn_id);
3439: hr_utility.set_location('Epa row updated',100);
3440: elsif txn_cer_rec.table_alias ='CEP' then
3441: hr_utility.set_location('its cep row ',110);
3442: cep_to_stage(p_cep_cer_id => txn_cer_rec.copy_entity_result_id,
3443: p_copy_entity_txn_id => p_copy_entity_txn_id);
3444: hr_utility.set_location('cep row updated',115);
3445: end if;
3440: elsif txn_cer_rec.table_alias ='CEP' then
3441: hr_utility.set_location('its cep row ',110);
3442: cep_to_stage(p_cep_cer_id => txn_cer_rec.copy_entity_result_id,
3443: p_copy_entity_txn_id => p_copy_entity_txn_id);
3444: hr_utility.set_location('cep row updated',115);
3445: end if;
3446: exception
3447: when others then
3448: hr_utility.set_location('error encountered, row being skipped',420);
3444: hr_utility.set_location('cep row updated',115);
3445: end if;
3446: exception
3447: when others then
3448: hr_utility.set_location('error encountered, row being skipped',420);
3449: end;
3450: end loop;
3451: hr_utility.set_location('done reading all rows ',120);
3452: if p_business_area = 'PQH_CORPS_TASK_LIST' and l_pgm_id is not null then
3447: when others then
3448: hr_utility.set_location('error encountered, row being skipped',420);
3449: end;
3450: end loop;
3451: hr_utility.set_location('done reading all rows ',120);
3452: if p_business_area = 'PQH_CORPS_TASK_LIST' and l_pgm_id is not null then
3453: pqh_cpd_hr_to_stage.corps_to_stage(p_copy_entity_txn_id => p_copy_entity_txn_id,
3454: p_pgm_id => l_pgm_id,
3455: p_effective_date => p_effective_date,
3455: p_effective_date => p_effective_date,
3456: p_pgm_cer_id => l_pgm_cer_id);
3457: end if;
3458: if p_start_cer_id is null then
3459: hr_utility.set_location('going for ctrlrec create',125);
3460: create_gsp_control_rec(p_copy_entity_txn_id => p_copy_entity_txn_id,
3461: p_effective_date => p_effective_date,
3462: p_business_area => p_business_area);
3463: hr_utility.set_location('done creating control row ',130);
3459: hr_utility.set_location('going for ctrlrec create',125);
3460: create_gsp_control_rec(p_copy_entity_txn_id => p_copy_entity_txn_id,
3461: p_effective_date => p_effective_date,
3462: p_business_area => p_business_area);
3463: hr_utility.set_location('done creating control row ',130);
3464: end if;
3465: end hr_to_stage;
3466: procedure point_to_opt_stage(p_copy_entity_txn_id in number,
3467: p_option_id in number,
3482: l_information3 varchar2(150);
3483: l_information4 varchar2(150);
3484: l_information5 varchar2(150);
3485: begin
3486: hr_utility.set_location('option is '||p_option_id,1);
3487: l_point_id := get_point_for_opt(p_option_id => p_option_id,
3488: p_effective_date => p_effective_date);
3489: hr_utility.set_location('point for option is '||l_point_id,10);
3490: if l_point_id is not null then
3485: begin
3486: hr_utility.set_location('option is '||p_option_id,1);
3487: l_point_id := get_point_for_opt(p_option_id => p_option_id,
3488: p_effective_date => p_effective_date);
3489: hr_utility.set_location('point for option is '||l_point_id,10);
3490: if l_point_id is not null then
3491: get_point_details(p_point_id => l_point_id,
3492: p_scale_id => l_scale_id,
3493: p_point_seq => l_point_seq,
3492: p_scale_id => l_scale_id,
3493: p_point_seq => l_point_seq,
3494: p_point_ovn => l_point_ovn,
3495: p_spinal_point => l_spinal_point);
3496: hr_utility.set_location('point is '||l_spinal_point,20);
3497: hr_utility.set_location('bus_area is '||p_business_area,20);
3498: if p_business_area ='PQH_CORPS_TASK_LIST' then
3499: pqh_cpd_hr_to_stage.get_point_details(p_point_id => l_point_id,
3500: p_information_category => l_information_category,
3493: p_point_seq => l_point_seq,
3494: p_point_ovn => l_point_ovn,
3495: p_spinal_point => l_spinal_point);
3496: hr_utility.set_location('point is '||l_spinal_point,20);
3497: hr_utility.set_location('bus_area is '||p_business_area,20);
3498: if p_business_area ='PQH_CORPS_TASK_LIST' then
3499: pqh_cpd_hr_to_stage.get_point_details(p_point_id => l_point_id,
3500: p_information_category => l_information_category,
3501: p_information1 => l_information1,
3508: -- create scale if it doesnot exist in staging area.
3509: l_scale_cer_id := is_scale_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
3510: p_scale_id => l_scale_id);
3511: if l_scale_cer_id is null then
3512: hr_utility.set_location('scale not in stage,copy it',10);
3513: scale_to_stage(p_scale_id => l_scale_id,
3514: p_business_group_id => p_business_group_id,
3515: p_copy_entity_txn_id => p_copy_entity_txn_id,
3516: p_effective_date => p_effective_date,
3516: p_effective_date => p_effective_date,
3517: p_business_area => p_business_area,
3518: p_scale_cer_id => l_scale_cer_id);
3519: else
3520: hr_utility.set_location('scale is already in staging area ',15);
3521: end if;
3522: hr_utility.set_location('scale cer id is '||l_scale_cer_id,20);
3523: end if;
3524: end if;
3518: p_scale_cer_id => l_scale_cer_id);
3519: else
3520: hr_utility.set_location('scale is already in staging area ',15);
3521: end if;
3522: hr_utility.set_location('scale cer id is '||l_scale_cer_id,20);
3523: end if;
3524: end if;
3525: if p_opt_cer_id is not null then
3526: begin
3538: information101 = nvl(l_information_category,information101)
3539: where copy_entity_result_id = p_opt_cer_id;
3540: exception
3541: when others then
3542: hr_utility.set_location('some issue in updating option row '||l_proc,120);
3543: end;
3544: else
3545: hr_utility.set_location('opt_cer_id is '||p_opt_cer_id,130);
3546: end if;
3541: when others then
3542: hr_utility.set_location('some issue in updating option row '||l_proc,120);
3543: end;
3544: else
3545: hr_utility.set_location('opt_cer_id is '||p_opt_cer_id,130);
3546: end if;
3547: end point_to_opt_stage;
3548: Procedure grade_to_pl_stage(p_grade_id in number,
3549: p_pl_cer_id in number,
3560: l_grade_name per_grades.name%type;
3561: l_grade_definition_id number;
3562: l_grd_ovn number;
3563: begin
3564: hr_utility.set_location('inside'||l_proc,10);
3565: if p_grade_id is not null then
3566: get_grade_details(p_grade_id => p_grade_id,
3567: p_date_from => l_date_from,
3568: p_date_to => l_date_to,
3569: p_short_name => l_short_name,
3570: p_grade_name => l_grade_name,
3571: p_grd_ovn => l_grd_ovn,
3572: p_grade_definition_id => l_grade_definition_id);
3573: hr_utility.set_location('grd name is'||substr(l_grade_name,1,40),20);
3574: end if;
3575: if p_pl_cer_id is not null then
3576: -- update plan row with Grade data
3577: begin
3588: mirror_src_entity_result_id)
3589: where copy_entity_result_id = p_pl_cer_id;
3590: exception
3591: when others then
3592: hr_utility.set_location('some issue in updating Plan row '||l_proc,120);
3593: end;
3594: else
3595: hr_utility.set_location('pl_cer_id is '||p_pl_cer_id,130);
3596: end if;
3591: when others then
3592: hr_utility.set_location('some issue in updating Plan row '||l_proc,120);
3593: end;
3594: else
3595: hr_utility.set_location('pl_cer_id is '||p_pl_cer_id,130);
3596: end if;
3597: hr_utility.set_location('leaving '||l_proc,420);
3598: end grade_to_pl_stage;
3599: procedure get_grd_scale_details(p_grade_id in number,
3593: end;
3594: else
3595: hr_utility.set_location('pl_cer_id is '||p_pl_cer_id,130);
3596: end if;
3597: hr_utility.set_location('leaving '||l_proc,420);
3598: end grade_to_pl_stage;
3599: procedure get_grd_scale_details(p_grade_id in number,
3600: p_effective_date in date,
3601: p_scale_id out nocopy number,
3606: p_scale_name out nocopy varchar2,
3607: p_starting_step out nocopy number) is
3608: begin
3609: -- get the scale which this grade is linked to
3610: hr_utility.set_location('grade id is '||p_grade_id,10);
3611: hr_utility.set_location('effdt is '||to_char(p_effective_date,'DD-MM-RRRR'),12);
3612: select psp.parent_spine_id,psp.name,gsp.ceiling_step_id,gsp.grade_spine_id,
3613: gsp.object_version_number,psp.object_version_number,gsp.starting_step
3614: into p_scale_id,p_scale_name, p_ceiling_step_id,p_grade_spine_id,p_grade_spine_ovn,p_scale_ovn,p_starting_step
3607: p_starting_step out nocopy number) is
3608: begin
3609: -- get the scale which this grade is linked to
3610: hr_utility.set_location('grade id is '||p_grade_id,10);
3611: hr_utility.set_location('effdt is '||to_char(p_effective_date,'DD-MM-RRRR'),12);
3612: select psp.parent_spine_id,psp.name,gsp.ceiling_step_id,gsp.grade_spine_id,
3613: gsp.object_version_number,psp.object_version_number,gsp.starting_step
3614: into p_scale_id,p_scale_name, p_ceiling_step_id,p_grade_spine_id,p_grade_spine_ovn,p_scale_ovn,p_starting_step
3615: from per_grade_spines_f gsp, per_parent_spines psp
3615: from per_grade_spines_f gsp, per_parent_spines psp
3616: where gsp.grade_id = p_grade_id
3617: and psp.parent_spine_id = gsp.parent_spine_id
3618: and p_effective_date between gsp.effective_start_date and gsp.effective_end_date;
3619: hr_utility.set_location('scale name is '||p_scale_name,40);
3620: exception
3621: when no_data_found then
3622: hr_utility.set_location('no scale is linked to grade'||p_grade_id,40);
3623: null;
3618: and p_effective_date between gsp.effective_start_date and gsp.effective_end_date;
3619: hr_utility.set_location('scale name is '||p_scale_name,40);
3620: exception
3621: when no_data_found then
3622: hr_utility.set_location('no scale is linked to grade'||p_grade_id,40);
3623: null;
3624: when others then
3625: hr_utility.set_location('issues in getting scale '||p_grade_id,40);
3626: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3621: when no_data_found then
3622: hr_utility.set_location('no scale is linked to grade'||p_grade_id,40);
3623: null;
3624: when others then
3625: hr_utility.set_location('issues in getting scale '||p_grade_id,40);
3626: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3627: (P_MASTER_TXN_ID => g_master_txn_id,
3628: P_TXN_ID => g_txn_id,
3629: p_context => 'get_grade_scl_details_others',
3673: l_cet_id number;
3674: l_starting_step number;
3675: l_corps_extra_info_id number;
3676: begin
3677: hr_utility.set_location('inside'||l_proc,10);
3678: l_message_text := 'inside grade_to_plip'
3679: ||' grade id is '||p_grade_id
3680: ||' plip_cer_id is '||p_plip_cer_id;
3681: if p_grade_id is not null then
3685: p_short_name => l_short_name,
3686: p_grade_name => l_grade_name,
3687: p_grd_ovn => l_grd_ovn,
3688: p_grade_definition_id => l_grade_definition_id);
3689: hr_utility.set_location('grade name is'||l_grade_name,20);
3690: -- get the scale data corresponding to the grade
3691: get_grd_scale_details(p_grade_id => p_grade_id,
3692: p_effective_date => p_effective_date,
3693: p_scale_id => l_scale_id,
3696: p_scale_ovn => l_scale_ovn,
3697: p_ceiling_step_id => l_ceiling_step_id,
3698: p_scale_name => l_scale_name,
3699: p_starting_step => l_starting_step);
3700: hr_utility.set_location('scale name is'||l_scale_name,30);
3701: hr_utility.set_location('starting step is'||l_starting_step,370);
3702: get_grd_rate_values(p_grade_id => p_grade_id,
3703: p_effective_date => p_effective_date,
3704: p_grd_value => l_grd_value,
3697: p_ceiling_step_id => l_ceiling_step_id,
3698: p_scale_name => l_scale_name,
3699: p_starting_step => l_starting_step);
3700: hr_utility.set_location('scale name is'||l_scale_name,30);
3701: hr_utility.set_location('starting step is'||l_starting_step,370);
3702: get_grd_rate_values(p_grade_id => p_grade_id,
3703: p_effective_date => p_effective_date,
3704: p_grd_value => l_grd_value,
3705: p_grd_min => l_grd_min,
3704: p_grd_value => l_grd_value,
3705: p_grd_min => l_grd_min,
3706: p_grd_mid => l_grd_mid,
3707: p_grd_max => l_grd_max);
3708: hr_utility.set_location('grade value is'||l_grd_value,40);
3709: if p_business_area ='PQH_CORPS_TASK_LIST' then
3710: hr_utility.set_location('getting quota for plip',40);
3711: pqh_cpd_hr_to_stage.get_corp(p_pgm_cer_id => p_parent_cer_id,
3712: p_corps_id => l_corps_definition_id,
3706: p_grd_mid => l_grd_mid,
3707: p_grd_max => l_grd_max);
3708: hr_utility.set_location('grade value is'||l_grd_value,40);
3709: if p_business_area ='PQH_CORPS_TASK_LIST' then
3710: hr_utility.set_location('getting quota for plip',40);
3711: pqh_cpd_hr_to_stage.get_corp(p_pgm_cer_id => p_parent_cer_id,
3712: p_corps_id => l_corps_definition_id,
3713: p_cet_id => l_cet_id);
3714: hr_utility.set_location('corps is '||l_corps_definition_id,40);
3710: hr_utility.set_location('getting quota for plip',40);
3711: pqh_cpd_hr_to_stage.get_corp(p_pgm_cer_id => p_parent_cer_id,
3712: p_corps_id => l_corps_definition_id,
3713: p_cet_id => l_cet_id);
3714: hr_utility.set_location('corps is '||l_corps_definition_id,40);
3715: hr_utility.set_location('cet is '||l_cet_id,40);
3716: if l_corps_definition_id >0 AND l_cet_id >0 THEN
3717: pqh_cpd_hr_to_stage.get_grd_quota(p_pgm_cer_id => p_parent_cer_id,
3718: p_grade_id => p_grade_id,
3711: pqh_cpd_hr_to_stage.get_corp(p_pgm_cer_id => p_parent_cer_id,
3712: p_corps_id => l_corps_definition_id,
3713: p_cet_id => l_cet_id);
3714: hr_utility.set_location('corps is '||l_corps_definition_id,40);
3715: hr_utility.set_location('cet is '||l_cet_id,40);
3716: if l_corps_definition_id >0 AND l_cet_id >0 THEN
3717: pqh_cpd_hr_to_stage.get_grd_quota(p_pgm_cer_id => p_parent_cer_id,
3718: p_grade_id => p_grade_id,
3719: p_corps_definition_id => l_corps_definition_id,
3728: end if;
3729: end if;
3730: if p_plip_cer_id is not null then
3731: begin
3732: hr_utility.set_location('going for update ',40);
3733: hr_utility.set_location('starting step is'||l_starting_step,370);
3734: update ben_copy_entity_results set
3735: information5 = nvl(l_grade_name,information5), -- plip name is same as plan name and overridden
3736: information306 = nvl(l_date_from,information306),
3729: end if;
3730: if p_plip_cer_id is not null then
3731: begin
3732: hr_utility.set_location('going for update ',40);
3733: hr_utility.set_location('starting step is'||l_starting_step,370);
3734: update ben_copy_entity_results set
3735: information5 = nvl(l_grade_name,information5), -- plip name is same as plan name and overridden
3736: information306 = nvl(l_date_from,information306),
3737: information307 = nvl(l_date_to,information307),
3757: information281 = l_grade_spine_ovn
3758: where copy_entity_result_id = p_plip_cer_id;
3759: exception
3760: when others then
3761: hr_utility.set_location('some issue in updating Plip row '||l_proc,120);
3762: end;
3763: else
3764: hr_utility.set_location('plip_cer_id is '||p_plip_cer_id,130);
3765: hr_utility.set_location('grade_name is '||l_grade_name,140);
3760: when others then
3761: hr_utility.set_location('some issue in updating Plip row '||l_proc,120);
3762: end;
3763: else
3764: hr_utility.set_location('plip_cer_id is '||p_plip_cer_id,130);
3765: hr_utility.set_location('grade_name is '||l_grade_name,140);
3766: hr_utility.set_location('starting step is'||l_starting_step,370);
3767: end if;
3768:
3761: hr_utility.set_location('some issue in updating Plip row '||l_proc,120);
3762: end;
3763: else
3764: hr_utility.set_location('plip_cer_id is '||p_plip_cer_id,130);
3765: hr_utility.set_location('grade_name is '||l_grade_name,140);
3766: hr_utility.set_location('starting step is'||l_starting_step,370);
3767: end if;
3768:
3769: hr_utility.set_location('leaving '||l_proc,420);
3762: end;
3763: else
3764: hr_utility.set_location('plip_cer_id is '||p_plip_cer_id,130);
3765: hr_utility.set_location('grade_name is '||l_grade_name,140);
3766: hr_utility.set_location('starting step is'||l_starting_step,370);
3767: end if;
3768:
3769: hr_utility.set_location('leaving '||l_proc,420);
3770: exception
3765: hr_utility.set_location('grade_name is '||l_grade_name,140);
3766: hr_utility.set_location('starting step is'||l_starting_step,370);
3767: end if;
3768:
3769: hr_utility.set_location('leaving '||l_proc,420);
3770: exception
3771: when others then
3772: hr_utility.set_location('some issues in geting grd dtl '||l_proc,120);
3773: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3768:
3769: hr_utility.set_location('leaving '||l_proc,420);
3770: exception
3771: when others then
3772: hr_utility.set_location('some issues in geting grd dtl '||l_proc,120);
3773: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3774: (P_MASTER_TXN_ID => g_master_txn_id,
3775: P_TXN_ID => g_txn_id,
3776: p_context => 'grade_to_plip_stage',
3800: l_scale_cer_id number;
3801: l_point_value number;
3802: l_ceiling_flag varchar2(10);
3803: begin
3804: hr_utility.set_location('inside '||l_proc,10);
3805: l_step_id := p_step_id;
3806: if p_oipl_id is not null or l_step_id is not null then
3807: get_step_details_for_oipl(p_oipl_id => p_oipl_id,
3808: p_effective_date => p_effective_date,
3827: (p_copy_entity_txn_id => p_copy_entity_txn_id,
3828: p_point_id => l_point_id);
3829: end if;
3830: if p_oipl_cer_id is not null then
3831: hr_utility.set_location('going for update '||l_proc,20);
3832: begin
3833: update ben_copy_entity_results set
3834: information253 = nvl(l_step_id, information253),
3835: information255 = nvl(l_grade_spine_id,information255),
3846: gs_parent_entity_result_id = nvl(gs_parent_entity_result_id,p_parent_cer_id)
3847: where copy_entity_result_id = p_oipl_cer_id;
3848: exception
3849: when others then
3850: hr_utility.set_location('some issue in updating oipl row '||l_proc,120);
3851: end;
3852: else
3853: hr_utility.set_location('oipl_cer_id is '||p_oipl_cer_id,130);
3854: end if;
3849: when others then
3850: hr_utility.set_location('some issue in updating oipl row '||l_proc,120);
3851: end;
3852: else
3853: hr_utility.set_location('oipl_cer_id is '||p_oipl_cer_id,130);
3854: end if;
3855: if p_business_area = 'PQH_CORPS_TASK_LIST' and l_step_id is not null then
3856: pqh_cpd_hr_to_stage.pull_career_path(p_copy_entity_txn_id => p_copy_entity_txn_id,
3857: p_step_id => l_step_id,
3859: p_grade_id => l_grade_id);
3860: end if;
3861: exception
3862: when others then
3863: hr_utility.set_location('some issues in geting grd dtl '||l_proc,120);
3864: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3865: (P_MASTER_TXN_ID => g_master_txn_id,
3866: P_TXN_ID => g_txn_id,
3867: p_context => 'step_to_oipl_stage',
3889: end if;
3890: return l_exists;
3891: exception
3892: when no_data_found then
3893: hr_utility.set_location('no row for hrrate for abr in stage',10);
3894: return false;
3895: when too_many_rows then
3896: hr_utility.set_location('rows for hrrate for abr',20);
3897: return true;
3892: when no_data_found then
3893: hr_utility.set_location('no row for hrrate for abr in stage',10);
3894: return false;
3895: when too_many_rows then
3896: hr_utility.set_location('rows for hrrate for abr',20);
3897: return true;
3898: when others then
3899: hr_utility.set_location('issues is checking hrrate for abr',30);
3900: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3895: when too_many_rows then
3896: hr_utility.set_location('rows for hrrate for abr',20);
3897: return true;
3898: when others then
3899: hr_utility.set_location('issues is checking hrrate for abr',30);
3900: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3901: (P_MASTER_TXN_ID => g_master_txn_id,
3902: P_TXN_ID => g_txn_id,
3903: p_context => 'is_hrrate_for_abr_exists',
3929: begin
3930: get_table_route_details(p_table_alias => 'HRRATE',
3931: p_table_route_id => l_hrr_tr_id,
3932: p_table_name => l_hrr_tr_name);
3933: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,20);
3934: if l_hrr_tr_name is null then
3935: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
3936: l_continue := FALSE;
3937: end if;
3931: p_table_route_id => l_hrr_tr_id,
3932: p_table_name => l_hrr_tr_name);
3933: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,20);
3934: if l_hrr_tr_name is null then
3935: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
3936: l_continue := FALSE;
3937: end if;
3938: if p_copy_entity_txn_id is null then
3939: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
3935: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
3936: l_continue := FALSE;
3937: end if;
3938: if p_copy_entity_txn_id is null then
3939: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
3940: l_continue := FALSE;
3941: end if;
3942: if p_abr_cer_id is null then
3943: hr_utility.set_location('abr cer is '||p_abr_cer_id,50);
3939: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
3940: l_continue := FALSE;
3941: end if;
3942: if p_abr_cer_id is null then
3943: hr_utility.set_location('abr cer is '||p_abr_cer_id,50);
3944: l_continue := FALSE;
3945: end if;
3946: if l_continue then
3947: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
3982: ,p_copy_entity_result_id => p_hrrate_cer_id
3983: ,p_object_version_number => l_hrr_cer_ovn);
3984: exception
3985: when others then
3986: hr_utility.set_location('some issue in creating hrrate row ',120);
3987: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
3988: (P_MASTER_TXN_ID => g_master_txn_id,
3989: P_TXN_ID => g_txn_id,
3990: p_context => 'create_hrrate_row_grd',
4016: begin
4017: get_table_route_details(p_table_alias => 'HRRATE',
4018: p_table_route_id => l_hrr_tr_id,
4019: p_table_name => l_hrr_tr_name);
4020: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,20);
4021: if l_hrr_tr_name is null then
4022: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
4023: l_continue := FALSE;
4024: end if;
4018: p_table_route_id => l_hrr_tr_id,
4019: p_table_name => l_hrr_tr_name);
4020: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,20);
4021: if l_hrr_tr_name is null then
4022: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
4023: l_continue := FALSE;
4024: end if;
4025: if p_copy_entity_txn_id is null then
4026: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
4022: hr_utility.set_location('hrrate tr name'||l_hrr_tr_name,45);
4023: l_continue := FALSE;
4024: end if;
4025: if p_copy_entity_txn_id is null then
4026: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
4027: l_continue := FALSE;
4028: end if;
4029: if p_abr_cer_id is null then
4030: hr_utility.set_location('abr cer is '||p_abr_cer_id,50);
4026: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
4027: l_continue := FALSE;
4028: end if;
4029: if p_abr_cer_id is null then
4030: hr_utility.set_location('abr cer is '||p_abr_cer_id,50);
4031: l_continue := FALSE;
4032: end if;
4033: if l_continue then
4034: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
4063: ,p_copy_entity_result_id => p_hrrate_cer_id
4064: ,p_object_version_number => l_hrr_cer_ovn);
4065: exception
4066: when others then
4067: hr_utility.set_location('some issue in creating hrrate row ',120);
4068: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4069: (P_MASTER_TXN_ID => g_master_txn_id,
4070: P_TXN_ID => g_txn_id,
4071: p_context => 'create_hrrate_row_pnt',
4093: l_hrr_cer_id number;
4094: l_continue boolean := TRUE;
4095: begin
4096: if p_pl_cer_id is null and p_opt_cer_id is null then
4097: hr_utility.set_location('pl or opt cer is reqd',55);
4098: l_continue := FALSE;
4099: else
4100: p_abr_cer_id := get_abr_cer(p_copy_entity_txn_id => p_copy_entity_txn_id,
4101: p_pl_cer_id => p_pl_cer_id,
4105: if p_abr_cer_id is null then
4106: get_table_route_details(p_table_alias => 'ABR',
4107: p_table_route_id => l_abr_tr_id,
4108: p_table_name => l_abr_tr_name);
4109: hr_utility.set_location('abr tr name'||l_abr_tr_name,20);
4110: if l_abr_tr_name is null then
4111: hr_utility.set_location('abr tr name'||l_abr_tr_name,45);
4112: l_continue := FALSE;
4113: end if;
4107: p_table_route_id => l_abr_tr_id,
4108: p_table_name => l_abr_tr_name);
4109: hr_utility.set_location('abr tr name'||l_abr_tr_name,20);
4110: if l_abr_tr_name is null then
4111: hr_utility.set_location('abr tr name'||l_abr_tr_name,45);
4112: l_continue := FALSE;
4113: end if;
4114: if p_copy_entity_txn_id is null then
4115: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
4111: hr_utility.set_location('abr tr name'||l_abr_tr_name,45);
4112: l_continue := FALSE;
4113: end if;
4114: if p_copy_entity_txn_id is null then
4115: hr_utility.set_location('CET is '||p_copy_entity_txn_id,50);
4116: l_continue := FALSE;
4117: end if;
4118: else
4119: l_continue := FALSE;
4138: ,p_copy_entity_result_id => p_abr_cer_id
4139: ,p_object_version_number => l_abr_cer_ovn);
4140: exception
4141: when others then
4142: hr_utility.set_location('some issue in creating abr row ',120);
4143: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4144: (P_MASTER_TXN_ID => g_master_txn_id,
4145: P_TXN_ID => g_txn_id,
4146: p_context => 'create_abr_row',
4200: l_information_category varchar2(30);
4201: l_information1 varchar2(150);
4202: l_information2 varchar2(150);
4203: begin
4204: hr_utility.set_location('inside'||l_proc,10);
4205: hr_utility.set_location('bus_area is'||p_business_area,10);
4206: begin
4207: -- get the scale info in local variables
4208: select name, increment_frequency,business_group_id, increment_period,object_version_number
4201: l_information1 varchar2(150);
4202: l_information2 varchar2(150);
4203: begin
4204: hr_utility.set_location('inside'||l_proc,10);
4205: hr_utility.set_location('bus_area is'||p_business_area,10);
4206: begin
4207: -- get the scale info in local variables
4208: select name, increment_frequency,business_group_id, increment_period,object_version_number
4209: into l_scale_name,l_increment_frequency,l_bg,l_increment_period,l_scale_ovn
4209: into l_scale_name,l_increment_frequency,l_bg,l_increment_period,l_scale_ovn
4210: from per_parent_spines
4211: where parent_spine_id = p_scale_id
4212: and business_group_id = p_business_group_id;
4213: hr_utility.set_location('scale name'||l_scale_name,20);
4214: exception
4215: when others then
4216: hr_utility.set_location('scale doesnot exist '||p_scale_id,35);
4217: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4212: and business_group_id = p_business_group_id;
4213: hr_utility.set_location('scale name'||l_scale_name,20);
4214: exception
4215: when others then
4216: hr_utility.set_location('scale doesnot exist '||p_scale_id,35);
4217: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4218: (P_MASTER_TXN_ID => g_master_txn_id,
4219: P_TXN_ID => g_txn_id,
4220: p_context => 'scale_to_stage',
4228: -- get the table route id and table alias
4229: get_table_route_details(p_table_alias => 'SCALE',
4230: p_table_route_id => l_scl_tr_id,
4231: p_table_name => l_scl_tr_name);
4232: hr_utility.set_location('scale tr name'||l_scl_tr_name,50);
4233: else
4234: hr_utility.set_location('no scale table route exists',55);
4235: end if;
4236: if p_business_area ='PQH_CORPS_TASK_LIST' then
4230: p_table_route_id => l_scl_tr_id,
4231: p_table_name => l_scl_tr_name);
4232: hr_utility.set_location('scale tr name'||l_scl_tr_name,50);
4233: else
4234: hr_utility.set_location('no scale table route exists',55);
4235: end if;
4236: if p_business_area ='PQH_CORPS_TASK_LIST' then
4237: pqh_cpd_hr_to_stage.get_scale_ddf_det(p_scale_id => p_scale_id,
4238: p_information_category => l_information_category,
4266: ,p_copy_entity_result_id => p_scale_cer_id
4267: ,p_object_version_number => l_scl_cer_ovn);
4268: exception
4269: when others then
4270: hr_utility.set_location('some issue in creating scale row '||l_proc,120);
4271: raise;
4272: end;
4273: else
4274: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_entity_txn_id,130);
4270: hr_utility.set_location('some issue in creating scale row '||l_proc,120);
4271: raise;
4272: end;
4273: else
4274: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_entity_txn_id,130);
4275: hr_utility.set_location('l_scale_name is '||l_scale_name,140);
4276: hr_utility.set_location('l_scl_tr_name is '||l_scl_tr_name,140);
4277: end if;
4278: end Scale_to_stage;
4271: raise;
4272: end;
4273: else
4274: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_entity_txn_id,130);
4275: hr_utility.set_location('l_scale_name is '||l_scale_name,140);
4276: hr_utility.set_location('l_scl_tr_name is '||l_scl_tr_name,140);
4277: end if;
4278: end Scale_to_stage;
4279: function get_current_max_cer(p_copy_entity_txn_id in number) return number is
4272: end;
4273: else
4274: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_entity_txn_id,130);
4275: hr_utility.set_location('l_scale_name is '||l_scale_name,140);
4276: hr_utility.set_location('l_scl_tr_name is '||l_scl_tr_name,140);
4277: end if;
4278: end Scale_to_stage;
4279: function get_current_max_cer(p_copy_entity_txn_id in number) return number is
4280: l_max_cer_id number;
4282: select max(copy_entity_result_id)
4283: into l_max_cer_id
4284: from ben_copy_entity_results
4285: where copy_entity_txn_id = p_copy_entity_txn_id;
4286: hr_utility.set_location('max cer is'||l_max_cer_id,10);
4287: return l_max_cer_id;
4288: exception
4289: when others then
4290: hr_utility.set_location('issue in getting max cer',40);
4286: hr_utility.set_location('max cer is'||l_max_cer_id,10);
4287: return l_max_cer_id;
4288: exception
4289: when others then
4290: hr_utility.set_location('issue in getting max cer',40);
4291: raise;
4292: end get_current_max_cer;
4293: procedure update_txn_table_route(p_copy_entity_txn_id in number) is
4294: cursor tr is select table_route_id,display_name,table_alias
4309: p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
4310: l_cea_ovn number;
4311: l_cea_id number;
4312: begin
4313: hr_utility.set_location('creating txn user',10);
4314: pqh_copy_entity_attribs_api.create_copy_entity_attrib
4315: (p_copy_entity_attrib_id => l_cea_id,
4316: p_copy_entity_txn_id => p_copy_entity_txn_id,
4317: p_information_category => 'PQH_GSP_TXN',
4321: p_information8 => p_replacement_type_cd,
4322: p_information9 => p_business_area,
4323: p_object_version_number => l_cea_ovn,
4324: p_effective_date => trunc(sysdate));
4325: hr_utility.set_location('cea '||l_cea_id,20);
4326: exception
4327: when others then
4328: hr_utility.set_location('issues in creating txn user',30);
4329: raise;
4324: p_effective_date => trunc(sysdate));
4325: hr_utility.set_location('cea '||l_cea_id,20);
4326: exception
4327: when others then
4328: hr_utility.set_location('issues in creating txn user',30);
4329: raise;
4330: end create_txn_user;
4331: procedure update_or_view_GL(p_calling_mode in varchar2,
4332: p_action_type in varchar2 default 'REFRESH',
4349: l_cet_ovn number;
4350: l_txn_name varchar2(100);
4351: begin
4352: if p_calling_mode ='U' then
4353: hr_utility.set_location('calling for update with'||p_pgm_name,10);
4354: update_GL(p_pgm_id => p_pgm_id,
4355: p_pgm_name => p_pgm_name,
4356: p_effective_date => p_effective_date,
4357: p_business_group_id => p_business_group_id,
4359: p_user_id => p_user_id,
4360: p_business_area => p_business_area,
4361: p_copy_entity_txn_id => p_copy_entity_txn_id);
4362: elsif p_calling_mode ='V' then
4363: hr_utility.set_location('calling for view',15);
4364: open csr_txns;
4365: fetch csr_txns into l_cet_id;
4366: if csr_txns%found then
4367: --close csr_txns;
4364: open csr_txns;
4365: fetch csr_txns into l_cet_id;
4366: if csr_txns%found then
4367: --close csr_txns;
4368: hr_utility.set_location('deleting details',20);
4369: --pqh_gsp_utility.del_gl_details_from_stage(p_pqh_copy_entity_txn_id => l_cet_id);
4370: pqh_gsp_utility.delete_transaction(p_pqh_copy_entity_txn_id =>l_cet_id);
4371:
4372: end if;
4373: close csr_txns;
4374: l_txn_name := pqh_gsp_utility.gen_txn_display_name
4375: (p_program_name => p_pgm_name,
4376: p_mode => 'V');
4377: hr_utility.set_location('starting cet row ',30);
4378: start_gsp_txn(p_copy_entity_txn_id => l_cet_id
4379: ,p_business_group_id => p_business_group_id
4380: ,p_name => p_pgm_name
4381: ,p_effective_date => p_effective_date
4381: ,p_effective_date => p_effective_date
4382: ,p_status => 'VIEW'
4383: ,p_object_version_number => l_cet_ovn);
4384: -- end if;
4385: hr_utility.set_location('populating pgm hier for cet '||l_cet_id,40);
4386: PQH_GSP_PROCESS_LOG.START_LOG
4387: (P_TXN_ID => p_pgm_id,
4388: P_TXN_NAME => p_pgm_name,
4389: P_MODULE_CD => 'PQH_GSP_BENSTG');
4393: p_effective_date => p_effective_date,
4394: p_business_group_id => p_business_group_id,
4395: p_business_area => p_business_area,
4396: p_pgm_id => p_pgm_id);
4397: hr_utility.set_location('done populating pgm hier ',50);
4398: p_copy_entity_txn_id := l_cet_id;
4399: else
4400: hr_utility.set_location('invalid mode passed '||p_calling_mode,100);
4401: end if;
4396: p_pgm_id => p_pgm_id);
4397: hr_utility.set_location('done populating pgm hier ',50);
4398: p_copy_entity_txn_id := l_cet_id;
4399: else
4400: hr_utility.set_location('invalid mode passed '||p_calling_mode,100);
4401: end if;
4402: end update_or_view_gl;
4403: procedure update_GL(p_pgm_id in number,
4404: p_action_type in varchar2 default 'REFRESH',
4434: -- 2) if this pgm is in staging area open for update then
4435: -- 3) get the user who has it
4436: -- 4) if not then create a cet row.
4437: -- 5) call populate_pgm_hierarchy
4438: hr_utility.set_location('inside update '||p_pgm_id,10);
4439: for txn_rec in csr_txns loop
4440: l_rec_found := TRUE;
4441: hr_utility.set_location('inside loop',15);
4442: l_copy_entity_txn_id := txn_rec.copy_entity_txn_id;
4437: -- 5) call populate_pgm_hierarchy
4438: hr_utility.set_location('inside update '||p_pgm_id,10);
4439: for txn_rec in csr_txns loop
4440: l_rec_found := TRUE;
4441: hr_utility.set_location('inside loop',15);
4442: l_copy_entity_txn_id := txn_rec.copy_entity_txn_id;
4443: end loop;
4444: if l_copy_entity_txn_id is not null then
4445: hr_utility.set_location('stage has data',16);
4441: hr_utility.set_location('inside loop',15);
4442: l_copy_entity_txn_id := txn_rec.copy_entity_txn_id;
4443: end loop;
4444: if l_copy_entity_txn_id is not null then
4445: hr_utility.set_location('stage has data',16);
4446: if p_action_type ='REFRESH' then
4447: hr_utility.set_location('staging data to be refreshed',18);
4448: pqh_gsp_utility.delete_transaction(p_pqh_copy_entity_txn_id => l_copy_entity_txn_id);
4449: l_rec_found := FALSE;
4443: end loop;
4444: if l_copy_entity_txn_id is not null then
4445: hr_utility.set_location('stage has data',16);
4446: if p_action_type ='REFRESH' then
4447: hr_utility.set_location('staging data to be refreshed',18);
4448: pqh_gsp_utility.delete_transaction(p_pqh_copy_entity_txn_id => l_copy_entity_txn_id);
4449: l_rec_found := FALSE;
4450: elsif p_action_type ='CONTINUE' then
4451: hr_utility.set_location('staging data to be used',18);
4447: hr_utility.set_location('staging data to be refreshed',18);
4448: pqh_gsp_utility.delete_transaction(p_pqh_copy_entity_txn_id => l_copy_entity_txn_id);
4449: l_rec_found := FALSE;
4450: elsif p_action_type ='CONTINUE' then
4451: hr_utility.set_location('staging data to be used',18);
4452: p_copy_entity_txn_id := l_copy_entity_txn_id;
4453: else
4454: hr_utility.set_location('cet_id will be going null ',19);
4455: end if;
4450: elsif p_action_type ='CONTINUE' then
4451: hr_utility.set_location('staging data to be used',18);
4452: p_copy_entity_txn_id := l_copy_entity_txn_id;
4453: else
4454: hr_utility.set_location('cet_id will be going null ',19);
4455: end if;
4456: else
4457: hr_utility.set_location('nothing in staging',18);
4458: end if;
4453: else
4454: hr_utility.set_location('cet_id will be going null ',19);
4455: end if;
4456: else
4457: hr_utility.set_location('nothing in staging',18);
4458: end if;
4459: if not l_rec_found then
4460: -- create txn_name
4461: l_txn_name := pqh_gsp_utility.gen_txn_display_name
4501: begin
4502: if p_status in ('VIEW','SFL') then
4503: l_error := FALSE;
4504: else
4505: hr_utility.set_location('invalid status passed',10);
4506: l_error := TRUE;
4507: end if;
4508: if not l_error then
4509: select transaction_category_id
4530: ,p_effective_date => p_effective_date
4531: ) ;
4532: exception
4533: when others then
4534: hr_utility.set_location('issues in creating CET row',100);
4535: raise;
4536: end;
4537: end if;
4538: end start_gsp_txn;
4556: ,p_no_dup_rslt => 'Y'
4557: ) ;
4558: exception
4559: when others then
4560: hr_utility.set_location('issues in copying pgm hier',20);
4561: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4562: (P_MASTER_TXN_ID => g_master_txn_id,
4563: P_TXN_ID => g_txn_id,
4564: p_context => 'populate_pgm_hierarchy',
4582: where copy_entity_txn_id = p_copy_entity_txn_id
4583: and table_alias ='SCALE'
4584: and information1 = p_scale_id;
4585: begin
4586: hr_utility.set_location('checking scale'||p_scale_id,10);
4587: for i in csr_scale loop
4588: l_scl_cer_id := i.copy_entity_result_id;
4589: end loop;
4590: return l_scl_cer_id;
4589: end loop;
4590: return l_scl_cer_id;
4591: exception
4592: when others then
4593: hr_utility.set_location('issues in checking scale ',20);
4594: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4595: (P_MASTER_TXN_ID => g_master_txn_id,
4596: P_TXN_ID => g_txn_id,
4597: p_context => 'is_scale_exists_in_txn',
4610: where copy_entity_txn_id = p_copy_entity_txn_id
4611: and table_alias ='PLN'
4612: and information223 = p_grd_id;
4613: begin
4614: hr_utility.set_location('checking grd'||p_grd_id,10);
4615: for i in csr_grd loop
4616: l_grd_cer_id := i.copy_entity_result_id;
4617: end loop;
4618: return l_grd_cer_id;
4617: end loop;
4618: return l_grd_cer_id;
4619: exception
4620: when others then
4621: hr_utility.set_location('issues in finding grade ',20);
4622: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4623: (P_MASTER_TXN_ID => g_master_txn_id,
4624: P_TXN_ID => g_txn_id,
4625: p_context => 'is_grd_exists_in_txn',
4633: p_pl_cer_id in number,
4634: p_grade_id in number) return number is
4635: l_plip_cer_id number;
4636: begin
4637: hr_utility.set_location('pl_cer'||p_pl_cer_id,10);
4638: hr_utility.set_location('grd'||p_grade_id,15);
4639: if p_grade_id is not null then
4640: select copy_entity_result_id
4641: into l_plip_cer_id
4634: p_grade_id in number) return number is
4635: l_plip_cer_id number;
4636: begin
4637: hr_utility.set_location('pl_cer'||p_pl_cer_id,10);
4638: hr_utility.set_location('grd'||p_grade_id,15);
4639: if p_grade_id is not null then
4640: select copy_entity_result_id
4641: into l_plip_cer_id
4642: from ben_copy_entity_results
4650: where copy_entity_txn_id = p_copy_entity_txn_id
4651: and table_alias ='CPP'
4652: and information252 = p_pl_cer_id;
4653: end if;
4654: hr_utility.set_location('plip_cer'||l_plip_cer_id,20);
4655: return l_plip_cer_id;
4656: exception
4657: when no_data_found then
4658: hr_utility.set_location('issues in finding plip ',30);
4654: hr_utility.set_location('plip_cer'||l_plip_cer_id,20);
4655: return l_plip_cer_id;
4656: exception
4657: when no_data_found then
4658: hr_utility.set_location('issues in finding plip ',30);
4659: return l_plip_cer_id;
4660: when others then
4661: hr_utility.set_location('issues in finding plip ',30);
4662: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4657: when no_data_found then
4658: hr_utility.set_location('issues in finding plip ',30);
4659: return l_plip_cer_id;
4660: when others then
4661: hr_utility.set_location('issues in finding plip ',30);
4662: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4663: (P_MASTER_TXN_ID => g_master_txn_id,
4664: P_TXN_ID => g_txn_id,
4665: p_context => 'is_plip_exists_in_txn',
4678: where copy_entity_txn_id = p_copy_entity_txn_id
4679: and table_alias ='PLN'
4680: and information1 = p_pl_id;
4681: begin
4682: hr_utility.set_location('checking pl'||p_pl_id,10);
4683: for i in csr_pln loop
4684: l_pl_cer_id := i.copy_entity_result_id;
4685: end loop;
4686: return l_pl_cer_id;
4685: end loop;
4686: return l_pl_cer_id;
4687: exception
4688: when others then
4689: hr_utility.set_location('issues in finding plan ',20);
4690: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4691: (P_MASTER_TXN_ID => g_master_txn_id,
4692: P_TXN_ID => g_txn_id,
4693: p_context => 'is_pl_exists_in_txn',
4708: and table_alias ='ELP'
4709: and copy_entity_result_id > p_start_cer
4710: and (information1 = p_ep_id OR information101 = p_ep_id) ;
4711: begin
4712: hr_utility.set_location('checking EP'||p_ep_id,10);
4713: for i in csr_epn loop
4714: l_ep_cer_id := i.copy_entity_result_id;
4715: end loop;
4716: return l_ep_cer_id;
4715: end loop;
4716: return l_ep_cer_id;
4717: exception
4718: when others then
4719: hr_utility.set_location('issues in finding EP ',20);
4720: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4721: (P_MASTER_TXN_ID => g_master_txn_id,
4722: P_TXN_ID => g_txn_id,
4723: p_context => 'is_ep_exists_in_txn',
4742: end loop;
4743: return l_opt_cer_id;
4744: exception
4745: when others then
4746: hr_utility.set_location('issues in copying opt hier',20);
4747: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4748: (P_MASTER_TXN_ID => g_master_txn_id,
4749: P_TXN_ID => g_txn_id,
4750: p_context => 'is_opt_exists_in_txn',
4769: end loop;
4770: return l_pt_cer_id;
4771: exception
4772: when others then
4773: hr_utility.set_location('issues in finding point ',20);
4774: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4775: (P_MASTER_TXN_ID => g_master_txn_id,
4776: P_TXN_ID => g_txn_id,
4777: p_context => 'is_pnt_exists_in_txn',
4794: and information253 = p_step_id;
4795: begin
4796: if p_option_id is not null and p_pl_id is not null then
4797: begin
4798: hr_utility.set_location('checking oipl ofr opt '||p_option_id,10);
4799: hr_utility.set_location('checking oipl for pl '||p_pl_id,12);
4800: select copy_entity_result_id
4801: into l_oipl_cer_id
4802: from ben_copy_entity_results
4795: begin
4796: if p_option_id is not null and p_pl_id is not null then
4797: begin
4798: hr_utility.set_location('checking oipl ofr opt '||p_option_id,10);
4799: hr_utility.set_location('checking oipl for pl '||p_pl_id,12);
4800: select copy_entity_result_id
4801: into l_oipl_cer_id
4802: from ben_copy_entity_results
4803: where copy_entity_txn_id = p_copy_entity_txn_id
4805: and information247 = p_option_id
4806: and information261 = p_pl_id;
4807: exception
4808: when no_data_found then
4809: hr_utility.set_location('oipl doesnot exist',20);
4810: return l_oipl_cer_id;
4811: when others then
4812: hr_utility.set_location('issues in finding point ',20);
4813: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4808: when no_data_found then
4809: hr_utility.set_location('oipl doesnot exist',20);
4810: return l_oipl_cer_id;
4811: when others then
4812: hr_utility.set_location('issues in finding point ',20);
4813: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4814: (P_MASTER_TXN_ID => g_master_txn_id,
4815: P_TXN_ID => g_txn_id,
4816: p_context => 'is_step_exists_in_txn',
4820: p_effective_date => trunc(sysdate));
4821: raise;
4822: end;
4823: else
4824: hr_utility.set_location('checking oipl ofr step '||p_step_id,30);
4825: for i in csr_oipl loop
4826: l_oipl_cer_id := i.copy_entity_result_id;
4827: end loop;
4828: end if;
4828: end if;
4829: return l_oipl_cer_id;
4830: exception
4831: when others then
4832: hr_utility.set_location('issues in finding oipl ',40);
4833: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4834: (P_MASTER_TXN_ID => g_master_txn_id,
4835: P_TXN_ID => g_txn_id,
4836: p_context => 'is_step_exists_in_txn2',
4851: l_start_cer number;
4852: begin
4853: p_opt_cer_id := is_option_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
4854: p_opt_id => p_opt_id);
4855: hr_utility.set_location('opt cer is'||p_opt_cer_id,10);
4856: if p_opt_cer_id is null then
4857: hr_utility.set_location('opt not in staging area, going for copy ',15);
4858: l_start_cer:= get_current_max_cer(p_copy_entity_txn_id );
4859: begin
4853: p_opt_cer_id := is_option_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
4854: p_opt_id => p_opt_id);
4855: hr_utility.set_location('opt cer is'||p_opt_cer_id,10);
4856: if p_opt_cer_id is null then
4857: hr_utility.set_location('opt not in staging area, going for copy ',15);
4858: l_start_cer:= get_current_max_cer(p_copy_entity_txn_id );
4859: begin
4860: ben_plan_design_plan_module.create_opt_result
4861: (p_copy_entity_result_id => l_copy_entity_result_id
4867: ,p_effective_date => p_effective_date
4868: ,p_parent_entity_result_id => l_copy_entity_result_id
4869: ,p_no_dup_rslt => 'Y'
4870: );
4871: hr_utility.set_location('copied opt hier',20);
4872: exception
4873: when others then
4874: hr_utility.set_location('issues in copying opt hier',25);
4875: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4870: );
4871: hr_utility.set_location('copied opt hier',20);
4872: exception
4873: when others then
4874: hr_utility.set_location('issues in copying opt hier',25);
4875: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
4876: (P_MASTER_TXN_ID => g_master_txn_id,
4877: P_TXN_ID => g_txn_id,
4878: p_context => 'populate_opt_hierarchy',
4889: p_business_area => p_business_area);
4890: p_opt_cer_id := is_option_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
4891: p_opt_id => p_opt_id);
4892: if p_opt_cer_id is null then
4893: hr_utility.set_location('issues in copying option',50);
4894: else
4895: hr_utility.set_location('opt cer is'||p_opt_cer_id,55);
4896: end if;
4897: else
4891: p_opt_id => p_opt_id);
4892: if p_opt_cer_id is null then
4893: hr_utility.set_location('issues in copying option',50);
4894: else
4895: hr_utility.set_location('opt cer is'||p_opt_cer_id,55);
4896: end if;
4897: else
4898: hr_utility.set_location('opt exists and cer is'||p_opt_cer_id,65);
4899: end if;
4894: else
4895: hr_utility.set_location('opt cer is'||p_opt_cer_id,55);
4896: end if;
4897: else
4898: hr_utility.set_location('opt exists and cer is'||p_opt_cer_id,65);
4899: end if;
4900: end populate_opt_hierarchy;
4901: procedure populate_grd_hierarchy(p_copy_entity_txn_id in number,
4902: p_effective_date in date,
4929: l_start_cer number;
4930: l_option_id number;
4931: l_plip_cer_ovn number;
4932: begin
4933: hr_utility.set_location('populating grade'||p_grade_id,10);
4934: -- initialize message pub before trying to add grade into the ladder
4935: fnd_msg_pub.initialize; --- Bug 8365941
4936: PQH_GSP_PROCESS_LOG.START_LOG
4937: (P_TXN_ID => Nvl(p_grade_id,p_in_pl_cer_id) ,
4960: p_plip_cer_id => p_plip_cer_id,
4961: p_plip_cer_ovn => l_plip_cer_ovn);
4962: end if;
4963: if l_pl_id is not null then
4964: hr_utility.set_location('grade is linked to plan'||l_pl_id,20);
4965: populate_pl_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
4966: p_effective_date => p_effective_date,
4967: p_business_group_id => p_business_group_id,
4968: p_plip_cer_id => p_plip_cer_id,
4969: p_pl_id => l_pl_id,
4970: p_mode => 'BAREBONE',
4971: p_business_area => p_business_area,
4972: p_pl_cer_id => p_out_pl_cer_id);
4973: hr_utility.set_location('pl hier populated '||p_out_pl_cer_id,30);
4974: else
4975: hr_utility.set_location('grade not linked ',40);
4976: if p_grade_id is not null then
4977: p_out_pl_cer_id := is_grd_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
4971: p_business_area => p_business_area,
4972: p_pl_cer_id => p_out_pl_cer_id);
4973: hr_utility.set_location('pl hier populated '||p_out_pl_cer_id,30);
4974: else
4975: hr_utility.set_location('grade not linked ',40);
4976: if p_grade_id is not null then
4977: p_out_pl_cer_id := is_grd_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
4978: p_grd_id => p_grade_id);
4979: if p_out_pl_cer_id is null then
4976: if p_grade_id is not null then
4977: p_out_pl_cer_id := is_grd_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
4978: p_grd_id => p_grade_id);
4979: if p_out_pl_cer_id is null then
4980: hr_utility.set_location('grade not in stage'||p_grade_id,42);
4981: create_plan_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
4982: p_effective_date => p_effective_date,
4983: p_business_group_id => p_business_group_id,
4984: p_grade_id => p_grade_id,
4985: p_plip_cer_id => p_plip_cer_id,
4986: p_dml_operation => 'COPIED',
4987: p_pl_cer_id => p_out_pl_cer_id,
4988: p_pl_cer_ovn => l_pl_cer_ovn);
4989: hr_utility.set_location('plan row created for grade'||p_out_pl_cer_id,45);
4990: else
4991: hr_utility.set_location('pl cer is'||p_out_pl_cer_id,48);
4992: end if;
4993: else
4987: p_pl_cer_id => p_out_pl_cer_id,
4988: p_pl_cer_ovn => l_pl_cer_ovn);
4989: hr_utility.set_location('plan row created for grade'||p_out_pl_cer_id,45);
4990: else
4991: hr_utility.set_location('pl cer is'||p_out_pl_cer_id,48);
4992: end if;
4993: else
4994: hr_utility.set_location('pl cer is updating with plip'||p_in_pl_cer_id,48);
4995: begin
4990: else
4991: hr_utility.set_location('pl cer is'||p_out_pl_cer_id,48);
4992: end if;
4993: else
4994: hr_utility.set_location('pl cer is updating with plip'||p_in_pl_cer_id,48);
4995: begin
4996: update ben_copy_entity_results
4997: set gs_mirror_src_entity_result_id = p_plip_cer_id,
4998: mirror_src_entity_result_id = p_plip_cer_id,
4999: information104 = ''
5000: where copy_entity_result_id = p_in_pl_cer_id;
5001: exception
5002: when others then
5003: hr_utility.set_location('pl cer is'||p_in_pl_cer_id,48);
5004: raise;
5005: end;
5006: end if;
5007: end if;
5005: end;
5006: end if;
5007: end if;
5008: if p_grade_id is not null then
5009: hr_utility.set_location('going for grade details update on plip ',49);
5010: grade_to_plip_stage(p_grade_id => p_grade_id,
5011: p_plip_cer_id => p_plip_cer_id,
5012: p_pl_cer_id => p_out_pl_cer_id,
5013: p_parent_cer_id => p_pgm_cer_id,
5013: p_parent_cer_id => p_pgm_cer_id,
5014: p_mirror_ser_id => p_pgm_cer_id,
5015: p_effective_date => p_effective_date,
5016: p_business_area => p_business_area);
5017: hr_utility.set_location('plip updated with Grade ',50);
5018: hr_utility.set_location('going for grade components ',50);
5019: for i in csr_grd_scale loop
5020: -- copy the attached scale to stage
5021: l_grade_scale_id := i.parent_spine_id;
5014: p_mirror_ser_id => p_pgm_cer_id,
5015: p_effective_date => p_effective_date,
5016: p_business_area => p_business_area);
5017: hr_utility.set_location('plip updated with Grade ',50);
5018: hr_utility.set_location('going for grade components ',50);
5019: for i in csr_grd_scale loop
5020: -- copy the attached scale to stage
5021: l_grade_scale_id := i.parent_spine_id;
5022: populate_scale_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
5026: p_mode => 'BAREBONE',
5027: p_business_area => p_business_area,
5028: p_scale_cer_id => p_scale_cer_id);
5029: end loop;
5030: hr_utility.set_location('scale populated if any'||p_scale_cer_id,55);
5031: -- now plan and options are in staging area, we have to check whether oipl exists in stage
5032: -- or not corresponding to the step in HR
5033: if l_grade_scale_id is not null then
5034: for grd_step in csr_grd_steps loop
5031: -- now plan and options are in staging area, we have to check whether oipl exists in stage
5032: -- or not corresponding to the step in HR
5033: if l_grade_scale_id is not null then
5034: for grd_step in csr_grd_steps loop
5035: hr_utility.set_location('point passed is '||grd_step.spinal_point_id,22);
5036: l_option_id := get_opt_for_point(p_point_id => grd_step.spinal_point_id,
5037: p_effective_date => p_effective_date);
5038: hr_utility.set_location('option is '||l_option_id,30);
5039: hr_utility.set_location('creating step of Grade '||grd_step.step_id,60);
5034: for grd_step in csr_grd_steps loop
5035: hr_utility.set_location('point passed is '||grd_step.spinal_point_id,22);
5036: l_option_id := get_opt_for_point(p_point_id => grd_step.spinal_point_id,
5037: p_effective_date => p_effective_date);
5038: hr_utility.set_location('option is '||l_option_id,30);
5039: hr_utility.set_location('creating step of Grade '||grd_step.step_id,60);
5040: l_oipl_cer_id := is_step_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5041: p_step_id => grd_step.step_id,
5042: p_option_id => l_option_id,
5035: hr_utility.set_location('point passed is '||grd_step.spinal_point_id,22);
5036: l_option_id := get_opt_for_point(p_point_id => grd_step.spinal_point_id,
5037: p_effective_date => p_effective_date);
5038: hr_utility.set_location('option is '||l_option_id,30);
5039: hr_utility.set_location('creating step of Grade '||grd_step.step_id,60);
5040: l_oipl_cer_id := is_step_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5041: p_step_id => grd_step.step_id,
5042: p_option_id => l_option_id,
5043: p_pl_id => l_pl_id);
5041: p_step_id => grd_step.step_id,
5042: p_option_id => l_option_id,
5043: p_pl_id => l_pl_id);
5044: if l_oipl_cer_id is null then
5045: hr_utility.set_location('step doesnot exist, create it',70);
5046: l_opt_cer_id := is_point_exists_in_txn
5047: (p_copy_entity_txn_id => p_copy_entity_txn_id,
5048: p_point_id => grd_step.spinal_point_id);
5049: hr_utility.set_location('opt cer is '||l_opt_cer_id,35);
5045: hr_utility.set_location('step doesnot exist, create it',70);
5046: l_opt_cer_id := is_point_exists_in_txn
5047: (p_copy_entity_txn_id => p_copy_entity_txn_id,
5048: p_point_id => grd_step.spinal_point_id);
5049: hr_utility.set_location('opt cer is '||l_opt_cer_id,35);
5050: create_oipl_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5051: p_effective_date => p_effective_date,
5052: p_business_group_id => p_business_group_id,
5053: p_grade_id => p_grade_id,
5057: p_scale_cer_id => p_scale_cer_id,
5058: p_option_id => l_option_id,
5059: p_dml_operation => 'COPIED',
5060: p_oipl_cer_id => l_oipl_cer_id);
5061: hr_utility.set_location('oipl row created'||l_oipl_cer_id,80);
5062: else
5063: hr_utility.set_location('oipl row exists'||l_oipl_cer_id,80);
5064: end if;
5065: end loop;
5059: p_dml_operation => 'COPIED',
5060: p_oipl_cer_id => l_oipl_cer_id);
5061: hr_utility.set_location('oipl row created'||l_oipl_cer_id,80);
5062: else
5063: hr_utility.set_location('oipl row exists'||l_oipl_cer_id,80);
5064: end if;
5065: end loop;
5066: else
5067: hr_utility.set_location('scale not there ',90);
5063: hr_utility.set_location('oipl row exists'||l_oipl_cer_id,80);
5064: end if;
5065: end loop;
5066: else
5067: hr_utility.set_location('scale not there ',90);
5068: end if;
5069: else
5070: -- grade and components are all in staging
5071: hr_utility.set_location('grade in stage ',90);
5067: hr_utility.set_location('scale not there ',90);
5068: end if;
5069: else
5070: -- grade and components are all in staging
5071: hr_utility.set_location('grade in stage ',90);
5072: end if;
5073: post_pull_process(p_copy_entity_txn_id => p_copy_entity_txn_id,
5074: p_start_cer => l_start_cer,
5075: p_effective_date => p_effective_date,
5150: where copy_entity_result_id = p_plip_cer_id;
5151: end if;
5152: exception
5153: when others then
5154: hr_utility.set_location('issue in creation oipl cer '||p_point_id,400);
5155: raise;
5156: end;
5157: exception
5158: when others then
5155: raise;
5156: end;
5157: exception
5158: when others then
5159: hr_utility.set_location('issue in oipl cer '||p_point_id,420);
5160: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5161: (P_MASTER_TXN_ID => g_master_txn_id,
5162: P_TXN_ID => g_txn_id,
5163: p_context => 'create_oipl_row',
5174: p_pl_cer_id out nocopy number,
5175: p_pl_cer_ovn out nocopy number) is
5176: l_pl_cer_id number;
5177: begin
5178: hr_utility.set_location('copying pl hier'||p_pl_id,10);
5179: ben_plan_design_plan_module.create_plan_result
5180: (p_copy_entity_result_id => l_pl_cer_id
5181: ,p_copy_entity_txn_id => p_copy_entity_txn_id
5182: ,p_pl_id => p_pl_id
5185: ,p_object_version_number => p_pl_cer_ovn
5186: ,p_effective_date => p_effective_date
5187: ,p_no_dup_rslt => 'Y'
5188: );
5189: hr_utility.set_location('copied pl hier'||l_pl_cer_id,20);
5190: p_pl_cer_id := l_pl_cer_id;
5191: exception
5192: when others then
5193: hr_utility.set_location('issues in copying pl hier',25);
5189: hr_utility.set_location('copied pl hier'||l_pl_cer_id,20);
5190: p_pl_cer_id := l_pl_cer_id;
5191: exception
5192: when others then
5193: hr_utility.set_location('issues in copying pl hier',25);
5194: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5195: (P_MASTER_TXN_ID => g_master_txn_id,
5196: P_TXN_ID => g_txn_id,
5197: p_context => 'copy_pl_hierarchy',
5213: l_start_cer number;
5214: begin
5215: p_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5216: p_pl_id => p_pl_id);
5217: hr_utility.set_location('pl cer is'||p_pl_cer_id,10);
5218: if p_pl_cer_id is null then
5219: hr_utility.set_location('pl not in staging area, going for copy ',15);
5220: l_start_cer:= get_current_max_cer(p_copy_entity_txn_id );
5221: copy_pl_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
5215: p_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5216: p_pl_id => p_pl_id);
5217: hr_utility.set_location('pl cer is'||p_pl_cer_id,10);
5218: if p_pl_cer_id is null then
5219: hr_utility.set_location('pl not in staging area, going for copy ',15);
5220: l_start_cer:= get_current_max_cer(p_copy_entity_txn_id );
5221: copy_pl_hierarchy(p_copy_entity_txn_id => p_copy_entity_txn_id,
5222: p_pl_id => p_pl_id,
5223: p_bg_id => p_business_group_id,
5223: p_bg_id => p_business_group_id,
5224: p_effective_date => p_effective_date,
5225: p_pl_cer_id => p_pl_cer_id,
5226: p_pl_cer_ovn => l_cer_ovn);
5227: hr_utility.set_location('pl cer id is'||p_pl_cer_id,16);
5228: hr_utility.set_location('plip cer id is'||p_plip_cer_id,17);
5229: if p_pl_cer_id is not null then
5230: begin
5231: update ben_copy_entity_results
5224: p_effective_date => p_effective_date,
5225: p_pl_cer_id => p_pl_cer_id,
5226: p_pl_cer_ovn => l_cer_ovn);
5227: hr_utility.set_location('pl cer id is'||p_pl_cer_id,16);
5228: hr_utility.set_location('plip cer id is'||p_plip_cer_id,17);
5229: if p_pl_cer_id is not null then
5230: begin
5231: update ben_copy_entity_results
5232: set gs_mirror_src_entity_result_id = p_plip_cer_id,
5233: mirror_src_entity_result_id = p_plip_cer_id
5234: where copy_entity_result_id = p_pl_cer_id;
5235: exception
5236: when others then
5237: hr_utility.set_location('pl update had issues ',18);
5238: raise;
5239: end;
5240: hr_utility.set_location('changing parent to plip',19);
5241: change_parent_plip(p_copy_entity_txn_id => p_copy_entity_txn_id,
5236: when others then
5237: hr_utility.set_location('pl update had issues ',18);
5238: raise;
5239: end;
5240: hr_utility.set_location('changing parent to plip',19);
5241: change_parent_plip(p_copy_entity_txn_id => p_copy_entity_txn_id,
5242: p_plip_cer_id => p_plip_cer_id,
5243: p_pl_cer_id => p_pl_cer_id);
5244: hr_utility.set_location('parent plip done',20);
5240: hr_utility.set_location('changing parent to plip',19);
5241: change_parent_plip(p_copy_entity_txn_id => p_copy_entity_txn_id,
5242: p_plip_cer_id => p_plip_cer_id,
5243: p_pl_cer_id => p_pl_cer_id);
5244: hr_utility.set_location('parent plip done',20);
5245: else
5246: hr_utility.set_location('Issue in copy ',30);
5247: end if;
5248: if p_mode = 'COMPLETE' then
5242: p_plip_cer_id => p_plip_cer_id,
5243: p_pl_cer_id => p_pl_cer_id);
5244: hr_utility.set_location('parent plip done',20);
5245: else
5246: hr_utility.set_location('Issue in copy ',30);
5247: end if;
5248: if p_mode = 'COMPLETE' then
5249: post_pull_process(p_copy_entity_txn_id => p_copy_entity_txn_id,
5250: p_start_cer => l_start_cer,
5254: p_pl_cer_id := is_pl_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5255: p_pl_id => p_pl_id);
5256: end if;
5257: if p_pl_cer_id is null then
5258: hr_utility.set_location('issues in copying plan',50);
5259: else
5260: hr_utility.set_location('pl cer is'||p_pl_cer_id,55);
5261: end if;
5262: else
5256: end if;
5257: if p_pl_cer_id is null then
5258: hr_utility.set_location('issues in copying plan',50);
5259: else
5260: hr_utility.set_location('pl cer is'||p_pl_cer_id,55);
5261: end if;
5262: else
5263: hr_utility.set_location('plan exists ',100);
5264: end if;
5259: else
5260: hr_utility.set_location('pl cer is'||p_pl_cer_id,55);
5261: end if;
5262: else
5263: hr_utility.set_location('plan exists ',100);
5264: end if;
5265: end populate_pl_hierarchy;
5266: procedure populate_ep_hierarchy(p_copy_entity_txn_id in number,
5267: p_effective_date in date,
5280: P_TXN_NAME => 'ELP_id : '||p_ep_id,
5281: P_MODULE_CD => 'PQH_GSP_BENSTG');
5282: g_master_txn_id := p_ep_id;
5283: g_txn_id := p_ep_id;
5284: hr_utility.set_location('ep id is'||p_ep_id,10);
5285: l_start_cer:= get_current_max_cer(p_copy_entity_txn_id );
5286: begin
5287: ben_plan_design_elpro_module.create_elig_prfl_results
5288: (p_copy_entity_txn_id => p_copy_entity_txn_id
5294: ,p_number_of_copies => 1
5295: ,p_object_version_number => l_cer_ovn
5296: ,p_effective_date => p_effective_date
5297: );
5298: hr_utility.set_location('copied ep hier',20);
5299:
5300: BEN_PDW_COPY_BEN_TO_STG.populate_extra_mapping_ELP(
5301: p_copy_entity_txn_id => p_copy_entity_txn_id,
5302: p_effective_date => p_effective_date,
5301: p_copy_entity_txn_id => p_copy_entity_txn_id,
5302: p_effective_date => p_effective_date,
5303: p_elig_prfl_id =>p_ep_id
5304: );
5305: hr_utility.set_location('Done with the mapping',25);
5306: /*
5307: exception
5308: when others then
5309: hr_utility.set_location('issues in copying ep hier',25);
5305: hr_utility.set_location('Done with the mapping',25);
5306: /*
5307: exception
5308: when others then
5309: hr_utility.set_location('issues in copying ep hier',25);
5310: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5311: (P_MASTER_TXN_ID => g_master_txn_id,
5312: P_TXN_ID => g_txn_id,
5313: p_context => 'copy_ep_hierarchy',
5329: p_business_group_id => p_business_group_id,
5330: p_business_area => p_business_area);
5331:
5332: if p_ep_cer_id is null then
5333: hr_utility.set_location('issues in copying ep',50);
5334: else
5335: hr_utility.set_location('ep cer is'||p_ep_cer_id,55);
5336: end if;
5337:
5331:
5332: if p_ep_cer_id is null then
5333: hr_utility.set_location('issues in copying ep',50);
5334: else
5335: hr_utility.set_location('ep cer is'||p_ep_cer_id,55);
5336: end if;
5337:
5338: -- For elp/child_records which have future records , pdw needs the
5339: -- attribute FUTURE_DATA_EXISTS properly set so that they can properly
5350: where b.copy_entity_txn_id = a.copy_entity_txn_id
5351: and b.table_alias = a.table_alias
5352: and b.information1 = a.information1
5353: and b.information2 = a.information3+1);
5354: hr_utility.set_location('Updated bcer records for future_data_exists flag',25);
5355:
5356: end populate_ep_hierarchy;
5357:
5358: procedure create_plan_row(p_copy_entity_txn_id in number,
5410: ,p_copy_entity_result_id => p_pl_cer_id
5411: ,p_object_version_number => p_pl_cer_ovn);
5412: exception
5413: when others then
5414: hr_utility.set_location('issue in creation pln cer '||p_grade_id,400);
5415: raise;
5416: end;
5417: exception
5418: when others then
5415: raise;
5416: end;
5417: exception
5418: when others then
5419: hr_utility.set_location('issue in pln cer '||p_grade_id,420);
5420: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5421: (P_MASTER_TXN_ID => g_master_txn_id,
5422: P_TXN_ID => g_txn_id,
5423: p_context => 'create_plan_row',
5498: ,p_copy_entity_result_id => p_opt_cer_id
5499: ,p_object_version_number => p_opt_cer_ovn);
5500: exception
5501: when others then
5502: hr_utility.set_location('issue in creation opt cer '||p_point_id,400);
5503: raise;
5504: end;
5505: if p_business_area = 'PQH_CORPS_TASK_LIST' then
5506: pqh_gsp_utility.update_frps_point_rate(p_point_cer_id => p_opt_cer_id,
5511: p_effective_date => p_effective_date);
5512: end if;
5513: exception
5514: when others then
5515: hr_utility.set_location('issue in opt cer '||p_point_id,420);
5516: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5517: (P_MASTER_TXN_ID => g_master_txn_id,
5518: P_TXN_ID => g_txn_id,
5519: p_context => 'create_opt_row',
5561: g_master_txn_id := P_Scale_id;
5562: g_txn_id := P_Scale_id;
5563: End If;
5564: if p_scale_cer_id is null then
5565: hr_utility.set_location('scale not in stage,copy it',10);
5566: scale_to_stage(p_scale_id => p_scale_id,
5567: p_business_group_id => p_business_group_id,
5568: p_business_area => p_business_area,
5569: p_copy_entity_txn_id => p_copy_entity_txn_id,
5569: p_copy_entity_txn_id => p_copy_entity_txn_id,
5570: p_effective_date => p_effective_date,
5571: p_scale_cer_id => p_scale_cer_id);
5572: else
5573: hr_utility.set_location('scale is already in staging area ',15);
5574: end if;
5575: for i in csr_scale_points loop
5576: begin
5577: hr_utility.set_location('point passed is '||i.spinal_point_id,22);
5573: hr_utility.set_location('scale is already in staging area ',15);
5574: end if;
5575: for i in csr_scale_points loop
5576: begin
5577: hr_utility.set_location('point passed is '||i.spinal_point_id,22);
5578: l_option_id := get_opt_for_point(p_point_id => i.spinal_point_id,
5579: p_effective_date => p_effective_date);
5580: hr_utility.set_location('option is '||l_option_id,30);
5581: l_opt_cer_id := is_point_exists_in_txn
5576: begin
5577: hr_utility.set_location('point passed is '||i.spinal_point_id,22);
5578: l_option_id := get_opt_for_point(p_point_id => i.spinal_point_id,
5579: p_effective_date => p_effective_date);
5580: hr_utility.set_location('option is '||l_option_id,30);
5581: l_opt_cer_id := is_point_exists_in_txn
5582: (p_copy_entity_txn_id => p_copy_entity_txn_id,
5583: p_point_id => i.spinal_point_id);
5584: if l_opt_cer_id is null and l_option_id is not null then
5581: l_opt_cer_id := is_point_exists_in_txn
5582: (p_copy_entity_txn_id => p_copy_entity_txn_id,
5583: p_point_id => i.spinal_point_id);
5584: if l_opt_cer_id is null and l_option_id is not null then
5585: hr_utility.set_location('opt is linked to point but doesnot exist in txn',37);
5586: begin
5587: hr_utility.set_location('calling opt hier copy ',38);
5588: ben_plan_design_plan_module.create_opt_result
5589: (p_copy_entity_result_id => l_opt_cer_id
5583: p_point_id => i.spinal_point_id);
5584: if l_opt_cer_id is null and l_option_id is not null then
5585: hr_utility.set_location('opt is linked to point but doesnot exist in txn',37);
5586: begin
5587: hr_utility.set_location('calling opt hier copy ',38);
5588: ben_plan_design_plan_module.create_opt_result
5589: (p_copy_entity_result_id => l_opt_cer_id
5590: ,p_copy_entity_txn_id => p_copy_entity_txn_id
5591: ,p_opt_id => l_option_id
5594: ,p_object_version_number => l_cer_ovn
5595: ,p_effective_date => p_effective_date
5596: ,p_parent_entity_result_id => l_parent_opt_cer_id
5597: );
5598: hr_utility.set_location('opt hier copy done',39);
5599: exception
5600: when others then
5601: hr_utility.set_location('issues in copying opt hier',40);
5602: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5597: );
5598: hr_utility.set_location('opt hier copy done',39);
5599: exception
5600: when others then
5601: hr_utility.set_location('issues in copying opt hier',40);
5602: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5603: (P_MASTER_TXN_ID => g_master_txn_id,
5604: P_TXN_ID => g_txn_id,
5605: p_context => 'populate_scale_hierarchy',
5609: p_effective_date => p_effective_date);
5610: raise;
5611: end;
5612: elsif l_option_id is null and l_opt_cer_id is null then
5613: hr_utility.set_location('point is not mapped,create option row ',25);
5614: create_option_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5615: p_effective_date => p_effective_date,
5616: p_business_group_id => p_business_group_id,
5617: p_scale_id => p_scale_id,
5620: p_opt_cer_id => l_opt_cer_id,
5621: p_dml_operation => 'COPIED',
5622: p_business_area => p_business_area,
5623: p_opt_cer_ovn => l_cer_ovn);
5624: hr_utility.set_location('option created '||l_opt_cer_id,35);
5625: else
5626: hr_utility.set_location('option exists in staging '||l_opt_cer_id,35);
5627: end if;
5628: exception
5622: p_business_area => p_business_area,
5623: p_opt_cer_ovn => l_cer_ovn);
5624: hr_utility.set_location('option created '||l_opt_cer_id,35);
5625: else
5626: hr_utility.set_location('option exists in staging '||l_opt_cer_id,35);
5627: end if;
5628: exception
5629: when others then
5630: hr_utility.set_location('issues in getting opt ',25);
5626: hr_utility.set_location('option exists in staging '||l_opt_cer_id,35);
5627: end if;
5628: exception
5629: when others then
5630: hr_utility.set_location('issues in getting opt ',25);
5631: PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
5632: (P_MASTER_TXN_ID => g_master_txn_id,
5633: P_TXN_ID => g_txn_id,
5634: p_context => 'populate_scale_hierarchy',
5646: p_business_group_id => p_business_group_id,
5647: p_business_area => p_business_area);
5648: end if;
5649: else
5650: hr_utility.set_location('scale id passed is null',25);
5651: end if;
5652: end populate_scale_hierarchy;
5653: procedure pull_payrate(p_copy_entity_txn_id in number,
5654: p_payrate_id in number,
5682: for grd_rt in csr_grd_rt loop
5683: l_sot := pqh_gsp_utility.get_gsp_plntyp_str_date(grd_rt.business_group_id,p_copy_entity_txn_id);
5684: if grd_rt.rate_type = 'G' then
5685: l_grade_id := grd_rt.grade_or_spinal_point_id;
5686: hr_utility.set_location('grade id is '||l_grade_id,13);
5687: -- check grade is part of grade ladder or not
5688: -- if rate is not defined then we create abr and hrrate rows.
5689: l_pl_cer_id := is_grd_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5690: p_grd_id => l_grade_id);
5688: -- if rate is not defined then we create abr and hrrate rows.
5689: l_pl_cer_id := is_grd_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5690: p_grd_id => l_grade_id);
5691: if l_pl_cer_id is not null then
5692: hr_utility.set_location('pl_cer_id id is '||l_pl_cer_id,14);
5693: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5694: p_grade_cer_id => l_pl_cer_id,
5695: p_hrr_esd => p_effective_date);
5696: if l_hrr_cer_id is not null then
5693: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5694: p_grade_cer_id => l_pl_cer_id,
5695: p_hrr_esd => p_effective_date);
5696: if l_hrr_cer_id is not null then
5697: hr_utility.set_location('rate for grade exists',12);
5698: else
5699: hr_utility.set_location('rate for grade doesnot exist, have to create',12);
5700: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => l_pl_cer_id);
5701: hr_utility.set_location('grd_st_dt is '||to_char(l_grd_st_dt,'DD/MM/RRRR'),12);
5695: p_hrr_esd => p_effective_date);
5696: if l_hrr_cer_id is not null then
5697: hr_utility.set_location('rate for grade exists',12);
5698: else
5699: hr_utility.set_location('rate for grade doesnot exist, have to create',12);
5700: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => l_pl_cer_id);
5701: hr_utility.set_location('grd_st_dt is '||to_char(l_grd_st_dt,'DD/MM/RRRR'),12);
5702: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5703: p_start_date => l_grd_st_dt,
5697: hr_utility.set_location('rate for grade exists',12);
5698: else
5699: hr_utility.set_location('rate for grade doesnot exist, have to create',12);
5700: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => l_pl_cer_id);
5701: hr_utility.set_location('grd_st_dt is '||to_char(l_grd_st_dt,'DD/MM/RRRR'),12);
5702: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5703: p_start_date => l_grd_st_dt,
5704: p_pl_cer_id => l_pl_cer_id,
5705: p_business_group_id => grd_rt.business_group_id,
5706: p_effective_date => p_effective_date,
5707: p_abr_cer_id => l_abr_cer_id,
5708: p_create_hrr => 'N',
5709: p_dml_oper => 'INSERT');
5710: hr_utility.set_location('abr_cer_id is '||l_abr_cer_id,16);
5711: create_hrrate_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5712: p_effective_date => p_effective_date,
5713: p_start_date => l_grd_st_dt,
5714: p_business_group_id => grd_rt.business_group_id,
5719: p_grd_mid_value => grd_rt.mid_value,
5720: p_grd_max_value => grd_rt.maximum,
5721: p_dml_oper => 'INSERT',
5722: p_hrrate_cer_id => l_hrr_cer_id);
5723: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5724: end if;
5725: else
5726: hr_utility.set_location('grade is not in GL',11);
5727: end if;
5722: p_hrrate_cer_id => l_hrr_cer_id);
5723: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5724: end if;
5725: else
5726: hr_utility.set_location('grade is not in GL',11);
5727: end if;
5728: elsif grd_rt.rate_type = 'SP' then
5729: l_point_id := grd_rt.grade_or_spinal_point_id;
5730: hr_utility.set_location('point id is '||l_point_id,13);
5726: hr_utility.set_location('grade is not in GL',11);
5727: end if;
5728: elsif grd_rt.rate_type = 'SP' then
5729: l_point_id := grd_rt.grade_or_spinal_point_id;
5730: hr_utility.set_location('point id is '||l_point_id,13);
5731: -- check point is part of grade ladder or not
5732: -- if rate is not defined then we create abr and hrrate rows.
5733: l_opt_cer_id := is_point_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5734: p_point_id => l_point_id);
5732: -- if rate is not defined then we create abr and hrrate rows.
5733: l_opt_cer_id := is_point_exists_in_txn(p_copy_entity_txn_id => p_copy_entity_txn_id,
5734: p_point_id => l_point_id);
5735: if l_opt_cer_id is not null then
5736: hr_utility.set_location('pl_cer_id id is '||l_pl_cer_id,14);
5737: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5738: p_point_cer_id => l_opt_cer_id,
5739: p_hrr_esd => p_effective_date);
5740: if l_hrr_cer_id is not null then
5737: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5738: p_point_cer_id => l_opt_cer_id,
5739: p_hrr_esd => p_effective_date);
5740: if l_hrr_cer_id is not null then
5741: hr_utility.set_location('rate for point exists',12);
5742: else
5743: hr_utility.set_location('rate for point doesnot exist, have to create',12);
5744: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5745: p_start_date => l_sot,
5739: p_hrr_esd => p_effective_date);
5740: if l_hrr_cer_id is not null then
5741: hr_utility.set_location('rate for point exists',12);
5742: else
5743: hr_utility.set_location('rate for point doesnot exist, have to create',12);
5744: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5745: p_start_date => l_sot,
5746: p_opt_cer_id => l_opt_cer_id,
5747: p_business_group_id => grd_rt.business_group_id,
5748: p_effective_date => p_effective_date,
5749: p_abr_cer_id => l_abr_cer_id,
5750: p_create_hrr => 'N',
5751: p_dml_oper => 'INSERT');
5752: hr_utility.set_location('abr_cer_id is '||l_abr_cer_id,16);
5753: create_hrrate_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5754: p_effective_date => p_effective_date,
5755: p_start_date => l_sot,
5756: p_business_group_id => grd_rt.business_group_id,
5758: p_point_cer_id => l_opt_cer_id,
5759: p_point_value => grd_rt.value,
5760: p_dml_oper => 'INSERT',
5761: p_hrrate_cer_id => l_hrr_cer_id);
5762: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5763: end if;
5764: else
5765: hr_utility.set_location('grade is not in GL',11);
5766: end if;
5761: p_hrrate_cer_id => l_hrr_cer_id);
5762: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5763: end if;
5764: else
5765: hr_utility.set_location('grade is not in GL',11);
5766: end if;
5767: else
5768: hr_utility.set_location('invalid rate type'||grd_rt.rate_type,10);
5769: end if;
5764: else
5765: hr_utility.set_location('grade is not in GL',11);
5766: end if;
5767: else
5768: hr_utility.set_location('invalid rate type'||grd_rt.rate_type,10);
5769: end if;
5770: end loop;
5771: PQH_PROCESS_BATCH_LOG.END_LOG;
5772: end pull_payrate;
5787: p_effective_date => p_effective_date,
5788: p_abr_cer_id => l_abr_cer_id,
5789: p_create_hrr => 'N',
5790: p_dml_oper => 'INSERT');
5791: hr_utility.set_location('abr_cer_id is '||l_abr_cer_id,16);
5792: create_hrrate_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5793: p_effective_date => p_effective_date,
5794: p_start_date => l_sot,
5795: p_business_group_id => p_business_group_id,
5797: p_point_cer_id => p_point_cer_id,
5798: p_point_value => p_point_value,
5799: p_dml_oper => 'INSERT',
5800: p_hrrate_cer_id => l_hrr_cer_id);
5801: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5802: end if;
5803: end create_frps_point_rate;
5804: procedure update_frps_point_rate(p_point_cer_id in number,
5805: p_copy_entity_txn_id in number,
5882: IF l_prog_style = 'PQH_GSP_GP' THEN
5883: for i in csr_grd_details loop
5884: l_grade_cer_id := i.copy_entity_result_id;
5885: if l_grade_cer_id is not null then
5886: hr_utility.set_location('grd_cer_id id is '||l_grade_cer_id,14);
5887: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5888: p_grade_cer_id => l_grade_cer_id,
5889: p_hrr_esd => p_effective_date);
5890: if l_hrr_cer_id is not null then
5887: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5888: p_grade_cer_id => l_grade_cer_id,
5889: p_hrr_esd => p_effective_date);
5890: if l_hrr_cer_id is not null then
5891: hr_utility.set_location('rate for grade exists',12);
5892: else
5893: hr_utility.set_location('rate for grade doesnot exist, have to create',12);
5894: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => l_grade_cer_id);
5895: hr_utility.set_location('grd_st_dt is '||to_char(l_grd_st_dt,'DD/MM/RRRR'),12);
5889: p_hrr_esd => p_effective_date);
5890: if l_hrr_cer_id is not null then
5891: hr_utility.set_location('rate for grade exists',12);
5892: else
5893: hr_utility.set_location('rate for grade doesnot exist, have to create',12);
5894: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => l_grade_cer_id);
5895: hr_utility.set_location('grd_st_dt is '||to_char(l_grd_st_dt,'DD/MM/RRRR'),12);
5896: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5897: p_start_date => l_grd_st_dt,
5891: hr_utility.set_location('rate for grade exists',12);
5892: else
5893: hr_utility.set_location('rate for grade doesnot exist, have to create',12);
5894: l_grd_st_dt := get_grd_start_date(p_grade_cer_id => l_grade_cer_id);
5895: hr_utility.set_location('grd_st_dt is '||to_char(l_grd_st_dt,'DD/MM/RRRR'),12);
5896: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5897: p_start_date => l_grd_st_dt,
5898: p_pl_cer_id => l_grade_cer_id,
5899: p_business_group_id => p_business_group_id ,
5900: p_effective_date => p_effective_date,
5901: p_abr_cer_id => l_abr_cer_id,
5902: p_create_hrr => 'N',
5903: p_dml_oper => 'INSERT');
5904: hr_utility.set_location('abr_cer_id is '||l_abr_cer_id,16);
5905: create_hrrate_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5906: p_effective_date => p_effective_date,
5907: p_start_date => l_grd_st_dt,
5908: p_business_group_id => p_business_group_id,
5913: p_grd_mid_value => 0,
5914: p_grd_max_value => 0,
5915: p_dml_oper => 'INSERT',
5916: p_hrrate_cer_id => l_hrr_cer_id);
5917: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5918: end if;
5919: else
5920: hr_utility.set_location('grade is not in GL',11);
5921: end if;
5916: p_hrrate_cer_id => l_hrr_cer_id);
5917: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5918: end if;
5919: else
5920: hr_utility.set_location('grade is not in GL',11);
5921: end if;
5922: end loop;
5923: ELSE
5924: for i in csr_opt_details loop
5922: end loop;
5923: ELSE
5924: for i in csr_opt_details loop
5925: l_opt_cer_id := i.copy_entity_result_id;
5926: hr_utility.set_location('opt_cer_id id is '||l_opt_cer_id,14);
5927: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5928: p_point_cer_id => l_opt_cer_id,
5929: p_hrr_esd => p_effective_date);
5930: if l_hrr_cer_id is not null then
5927: l_hrr_cer_id := is_hrr_exists(p_copy_entity_txn_id => p_copy_entity_txn_id,
5928: p_point_cer_id => l_opt_cer_id,
5929: p_hrr_esd => p_effective_date);
5930: if l_hrr_cer_id is not null then
5931: hr_utility.set_location('rate for point exists',12);
5932: else
5933: hr_utility.set_location('rate for point doesnot exist, have to create',12);
5934: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5935: p_start_date => l_sot,
5929: p_hrr_esd => p_effective_date);
5930: if l_hrr_cer_id is not null then
5931: hr_utility.set_location('rate for point exists',12);
5932: else
5933: hr_utility.set_location('rate for point doesnot exist, have to create',12);
5934: create_abr_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5935: p_start_date => l_sot,
5936: p_opt_cer_id => l_opt_cer_id,
5937: p_business_group_id => p_business_group_id,
5938: p_effective_date => p_effective_date,
5939: p_abr_cer_id => l_abr_cer_id,
5940: p_create_hrr => 'N',
5941: p_dml_oper => 'INSERT');
5942: hr_utility.set_location('abr_cer_id is '||l_abr_cer_id,16);
5943: create_hrrate_row(p_copy_entity_txn_id => p_copy_entity_txn_id,
5944: p_effective_date => p_effective_date,
5945: p_start_date => l_sot,
5946: p_business_group_id => p_business_group_id,
5948: p_point_cer_id => l_opt_cer_id,
5949: p_point_value => 0,
5950: p_dml_oper => 'INSERT',
5951: p_hrrate_cer_id => l_hrr_cer_id);
5952: hr_utility.set_location('hrr_cer_id is '||l_hrr_cer_id,17);
5953: end if;
5954: end loop;
5955: END IF;
5956: END IF;
5967: from ben_plip_f
5968: where pgm_id = p_pgm_id
5969: and pl_id = p_plan_id
5970: and p_effective_date between effective_start_date and effective_end_date;
5971: hr_utility.set_location('plip is '||l_plip_id,10);
5972: exception
5973: when no_data_found then
5974: hr_utility.set_location('plip does not exist '||l_plip_id,10);
5975: when others then
5970: and p_effective_date between effective_start_date and effective_end_date;
5971: hr_utility.set_location('plip is '||l_plip_id,10);
5972: exception
5973: when no_data_found then
5974: hr_utility.set_location('plip does not exist '||l_plip_id,10);
5975: when others then
5976: hr_utility.set_location('issues in selected plip',20);
5977: raise;
5978: end;
5972: exception
5973: when no_data_found then
5974: hr_utility.set_location('plip does not exist '||l_plip_id,10);
5975: when others then
5976: hr_utility.set_location('issues in selected plip',20);
5977: raise;
5978: end;
5979: return l_plip_id;
5980: end get_plip_for_pgm_plan;