[Home] [Help]
1: Package body pqh_gsp_utility as
2: /* $Header: pqgsputl.pkb 120.15 2011/05/09 10:15:14 lbodired noship $ */
3:
4: g_package Varchar2(30) := 'pqh_gsp_utility.';
5: g_debug boolean := hr_utility.debug_enabled;
6:
7: --
8: ---------------------------get_gsp_plntyp_str_date-----------------------------
9: --
12: return date is
13: l_proc varchar2(72) := g_package||'get_gsp_plntyp_str_date';
14: l_plan_type_date DATE ;
15: begin
16: hr_utility.set_location('Entering:'|| l_proc, 10);
17: select min(effective_start_date)
18: into l_plan_type_date
19: from ben_pl_typ_f
20: where business_group_id = p_business_group_id
19: from ben_pl_typ_f
20: where business_group_id = p_business_group_id
21: and opt_typ_cd ='GSP'
22: and pl_typ_stat_cd ='A';
23: hr_utility.set_location('Plan Type date is :'|| l_plan_type_date, 20);
24: if l_plan_type_date is null and p_copy_entity_txn_id is not null
25: then
26: begin
27: select information308
28: into l_plan_type_date
29: from ben_copy_entity_results
30: where copy_entity_txn_id = p_copy_entity_txn_id
31: and table_alias = 'PGM';
32: hr_utility.set_location('Plan Type date is :'|| l_plan_type_date, 30);
33: exception
34: when no_data_found then
35: l_plan_type_date := null ;
36: end;
37: end if ;
38: return l_plan_type_date ;
39: exception
40: when others then
41: hr_utility.set_location('Problem in determining Plan Type date ',40);
42: raise;
43: end get_gsp_plntyp_str_date ;
44: --
45: ---------------------------gsp_plan_type_exists-----------------------------
48: return varchar2 is
49: l_proc varchar2(72) := g_package||'gsp_plan_type_exists';
50: l_status varchar2(1) ;
51: begin
52: hr_utility.set_location('Entering:'|| l_proc, 10);
53: begin
54: select 'Y'
55: into l_status
56: from ben_pl_typ_f
60: and rownum<2 ;
61: exception
62: when no_data_found then
63: l_status := 'N';
64: hr_utility.set_location('GSP Plan Type does not exist ',40);
65: end ;
66: return l_status;
67: end gsp_plan_type_exists ;
68:
103: l_proc varchar2(72) := g_package||'chk_ps_for_upd_del';
104:
105: begin
106: --
107: hr_utility.set_location('Entering '||l_proc,10);
108:
109: open chk_ps_gsp_association;
110: fetch chk_ps_gsp_association into l_gname,l_pgm_name;
111: if chk_ps_gsp_association%found then
109: open chk_ps_gsp_association;
110: fetch chk_ps_gsp_association into l_gname,l_pgm_name;
111: if chk_ps_gsp_association%found then
112: close chk_ps_gsp_association;
113: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
114: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
115: hr_utility.set_message_token('GRADE_NAME',l_gname);
116: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
117: hr_utility.raise_error;
110: fetch chk_ps_gsp_association into l_gname,l_pgm_name;
111: if chk_ps_gsp_association%found then
112: close chk_ps_gsp_association;
113: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
114: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
115: hr_utility.set_message_token('GRADE_NAME',l_gname);
116: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
117: hr_utility.raise_error;
118: else
111: if chk_ps_gsp_association%found then
112: close chk_ps_gsp_association;
113: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
114: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
115: hr_utility.set_message_token('GRADE_NAME',l_gname);
116: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
117: hr_utility.raise_error;
118: else
119: close chk_ps_gsp_association;
112: close chk_ps_gsp_association;
113: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
114: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
115: hr_utility.set_message_token('GRADE_NAME',l_gname);
116: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
117: hr_utility.raise_error;
118: else
119: close chk_ps_gsp_association;
120: end if;
113: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
114: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
115: hr_utility.set_message_token('GRADE_NAME',l_gname);
116: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
117: hr_utility.raise_error;
118: else
119: close chk_ps_gsp_association;
120: end if;
121:
118: else
119: close chk_ps_gsp_association;
120: end if;
121:
122: hr_utility.set_location('Leaving '||l_proc,100);
123: --
124: end;
125:
126: --
163: l_proc varchar2(72) := g_package||'chk_rate_for_upd_del';
164: --
165: begin
166: --
167: hr_utility.set_location('Entering '||l_proc,10);
168:
169: open chk_ps_gsp_association;
170: fetch chk_ps_gsp_association into l_gname,l_pgm_name;
171: if chk_ps_gsp_association%found then
169: open chk_ps_gsp_association;
170: fetch chk_ps_gsp_association into l_gname,l_pgm_name;
171: if chk_ps_gsp_association%found then
172: close chk_ps_gsp_association;
173: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
174: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
175: hr_utility.set_message_token('GRADE_NAME',l_gname);
176: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
177: hr_utility.raise_error;
170: fetch chk_ps_gsp_association into l_gname,l_pgm_name;
171: if chk_ps_gsp_association%found then
172: close chk_ps_gsp_association;
173: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
174: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
175: hr_utility.set_message_token('GRADE_NAME',l_gname);
176: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
177: hr_utility.raise_error;
178: else
171: if chk_ps_gsp_association%found then
172: close chk_ps_gsp_association;
173: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
174: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
175: hr_utility.set_message_token('GRADE_NAME',l_gname);
176: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
177: hr_utility.raise_error;
178: else
179: close chk_ps_gsp_association;
172: close chk_ps_gsp_association;
173: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
174: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
175: hr_utility.set_message_token('GRADE_NAME',l_gname);
176: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
177: hr_utility.raise_error;
178: else
179: close chk_ps_gsp_association;
180: end if;
173: hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
174: hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
175: hr_utility.set_message_token('GRADE_NAME',l_gname);
176: hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
177: hr_utility.raise_error;
178: else
179: close chk_ps_gsp_association;
180: end if;
181:
178: else
179: close chk_ps_gsp_association;
180: end if;
181:
182: hr_utility.set_location('Leaving '||l_proc,100);
183: --
184: end;
185: -- End changes for bug 9328526
186:
218: l_proc varchar2(72) := g_package||'chk_grade_exist_in_gl';
219:
220: BEGIN
221: if g_debug then
222: hr_utility.set_location('Entering '||l_proc,10);
223: hr_utility.set_location('p_copy_entity_txn_id '||l_proc,20);
224: end if;
225:
226: Open csr_grade_in_gl;
219:
220: BEGIN
221: if g_debug then
222: hr_utility.set_location('Entering '||l_proc,10);
223: hr_utility.set_location('p_copy_entity_txn_id '||l_proc,20);
224: end if;
225:
226: Open csr_grade_in_gl;
227: Fetch csr_grade_in_gl into l_cer;
353:
354:
355: BEGIN
356: if g_debug then
357: hr_utility.set_location('Entering '||l_proc,10);
358: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
359: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
360: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
361: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
354:
355: BEGIN
356: if g_debug then
357: hr_utility.set_location('Entering '||l_proc,10);
358: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
359: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
360: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
361: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
362: end if;
355: BEGIN
356: if g_debug then
357: hr_utility.set_location('Entering '||l_proc,10);
358: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
359: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
360: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
361: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
362: end if;
363:
356: if g_debug then
357: hr_utility.set_location('Entering '||l_proc,10);
358: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
359: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
360: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
361: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
362: end if;
363:
364: hr_multi_message.enable_message_list;
357: hr_utility.set_location('Entering '||l_proc,10);
358: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
359: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
360: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
361: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
362: end if;
363:
364: hr_multi_message.enable_message_list;
365:
368: Fetch csr_pgm_id into l_pgm_id;
369: Close csr_pgm_id;
370:
371: if g_debug then
372: hr_utility.set_location('Pgm_Id '||l_pgm_id,70);
373: end if;
374:
375: -- Get Grade Id
376: Open csr_grade_id;
377: Fetch csr_grade_id into l_grade_id,l_grade_name;
378: Close csr_grade_id;
379:
380: if g_debug then
381: hr_utility.set_location('Grade Id '||l_grade_id,80);
382: hr_utility.set_location('Grade Name '||l_grade_name,81);
383: end if;
384:
385: IF (l_grade_id is NOT NULL AND l_pgm_id IS NOT NULL ) THEN
378: Close csr_grade_id;
379:
380: if g_debug then
381: hr_utility.set_location('Grade Id '||l_grade_id,80);
382: hr_utility.set_location('Grade Name '||l_grade_name,81);
383: end if;
384:
385: IF (l_grade_id is NOT NULL AND l_pgm_id IS NOT NULL ) THEN
386:
389: Open csr_default_grdldr;
390: Fetch csr_default_grdldr into l_dummy;
391: If csr_default_grdldr%Found Then
392: if g_debug then
393: hr_utility.set_location('Grade Ladder is Default Grade Ladder',85);
394: end if;
395: Close csr_default_grdldr;
396:
397: Open csr_grd_assg_emp(l_grade_id);
398: Fetch csr_grd_assg_emp into l_dummy;
399: IF csr_grd_assg_emp%Found Then
400: Close csr_grd_assg_emp;
401: if g_debug then
402: hr_utility.set_location('Employe placements exist on the Default Grade Ladder Grades',87);
403: end if;
404: if g_debug then
405: hr_utility.set_location('Grade Name '||l_grade_name,88);
406: end if;
401: if g_debug then
402: hr_utility.set_location('Employe placements exist on the Default Grade Ladder Grades',87);
403: end if;
404: if g_debug then
405: hr_utility.set_location('Grade Name '||l_grade_name,88);
406: end if;
407: p_rec_exists := l_grade_name;
408: Else
409: p_rec_exists := 'MAIN';
417: Open csr_gl_grade_assg_emp(l_pgm_id, l_grade_id);
418: Fetch csr_gl_grade_assg_emp into l_assg_id;
419: if csr_gl_grade_assg_emp%found then
420: if g_debug then
421: hr_utility.set_location(' Employe placements exist on the Grades',90);
422: end if;
423: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
424: hr_utility.raise_error;
425: end if;
419: if csr_gl_grade_assg_emp%found then
420: if g_debug then
421: hr_utility.set_location(' Employe placements exist on the Grades',90);
422: end if;
423: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
424: hr_utility.raise_error;
425: end if;
426: Close csr_gl_grade_assg_emp;
427:
420: if g_debug then
421: hr_utility.set_location(' Employe placements exist on the Grades',90);
422: end if;
423: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
424: hr_utility.raise_error;
425: end if;
426: Close csr_gl_grade_assg_emp;
427:
428: l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
439: OPEN csr_grade_crpath_node;
440: FETCH csr_grade_crpath_node into l_grade_crpath_node;
441: if csr_grade_crpath_node%found then
442: if g_debug then
443: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
444: end if;
445: hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
446: hr_utility.raise_error;
447: end if;
441: if csr_grade_crpath_node%found then
442: if g_debug then
443: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
444: end if;
445: hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
446: hr_utility.raise_error;
447: end if;
448: CLOSE csr_grade_crpath_node;
449: end if;
442: if g_debug then
443: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
444: end if;
445: hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
446: hr_utility.raise_error;
447: end if;
448: CLOSE csr_grade_crpath_node;
449: end if;
450: end if;
455: ELSE
456: p_rec_exists := 'STAGE';
457: END IF;
458: if g_debug then
459: hr_utility.set_location('p_rec_exists :'||p_rec_exists,99);
460: hr_utility.set_location('Leaving '||l_proc,100);
461: end if;
462: EXCEPTION
463: WHEN others THEN
456: p_rec_exists := 'STAGE';
457: END IF;
458: if g_debug then
459: hr_utility.set_location('p_rec_exists :'||p_rec_exists,99);
460: hr_utility.set_location('Leaving '||l_proc,100);
461: end if;
462: EXCEPTION
463: WHEN others THEN
464: p_rec_exists := 'STAGE';
492: l_proc varchar2(72) := g_package||'enable_disable_start_icon';
493: --
494: BEGIN
495: if g_debug then
496: hr_utility.set_location('Entering '||l_proc,5);
497: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
498: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
499: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
500: end if;
493: --
494: BEGIN
495: if g_debug then
496: hr_utility.set_location('Entering '||l_proc,5);
497: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
498: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
499: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
500: end if;
501:
494: BEGIN
495: if g_debug then
496: hr_utility.set_location('Entering '||l_proc,5);
497: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
498: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
499: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
500: end if;
501:
502: if (p_table_alias is not null) then
495: if g_debug then
496: hr_utility.set_location('Entering '||l_proc,5);
497: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
498: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
499: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
500: end if;
501:
502: if (p_table_alias is not null) then
503: l_table_alias := p_table_alias;
518: st_icon := get_status(prev_task, p_copy_enty_txn_id, l_table_alias);
519: end if;
520:
521: if g_debug then
522: hr_utility.set_location('Leaving Successfully'||l_proc,5);
523: end if;
524:
525: RETURN st_icon;
526: EXCEPTION WHEN others THEN
524:
525: RETURN st_icon;
526: EXCEPTION WHEN others THEN
527: if g_debug then
528: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
529: end if;
530: return 'N';
531: END;
532: --
543: l_proc varchar2(72) := g_package||'get_status';
544: --
545: BEGIN
546: if g_debug then
547: hr_utility.set_location('Entering '||l_proc,5);
548: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
549: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
550: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
551: end if;
544: --
545: BEGIN
546: if g_debug then
547: hr_utility.set_location('Entering '||l_proc,5);
548: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
549: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
550: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
551: end if;
552:
545: BEGIN
546: if g_debug then
547: hr_utility.set_location('Entering '||l_proc,5);
548: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
549: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
550: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
551: end if;
552:
553: if (p_table_alias is not null) then
546: if g_debug then
547: hr_utility.set_location('Entering '||l_proc,5);
548: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
549: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
550: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
551: end if;
552:
553: if (p_table_alias is not null) then
554: l_table_alias := p_table_alias;
627: else
628: st_icon := 'N';
629: end if;
630: if g_debug then
631: hr_utility.set_location('Leaving Successfully'||l_proc,5);
632: end if;
633: RETURN st_icon;
634: EXCEPTION
635: WHEN others THEN
633: RETURN st_icon;
634: EXCEPTION
635: WHEN others THEN
636: if g_debug then
637: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
638: end if;
639: return 'N';
640: END;
641:
663:
664: BEGIN
665:
666: if g_debug then
667: hr_utility.set_location('Leaving '||l_proc,10);
668: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
669: end if;
670:
671: Open csr_use_points;
664: BEGIN
665:
666: if g_debug then
667: hr_utility.set_location('Leaving '||l_proc,10);
668: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
669: end if;
670:
671: Open csr_use_points;
672: Fetch csr_use_points into l_result;
672: Fetch csr_use_points into l_result;
673: Close csr_use_points;
674:
675: if g_debug then
676: hr_utility.set_location('l_result '||l_result,25);
677: end if;
678: return l_result;
679:
680: if g_debug then
677: end if;
678: return l_result;
679:
680: if g_debug then
681: hr_utility.set_location('Leaving '||l_proc,30);
682: end if;
683:
684: EXCEPTION
685: When others THEN
772: and information3 = p_step_id;
773:
774: BEGIN
775: if g_debug then
776: hr_utility.set_location('Entering '||l_proc,10);
777: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
778: hr_utility.set_location('p_use_points '||p_use_points,30);
779: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
780: hr_utility.set_location('p_effective_date '||p_effective_date,70);
773:
774: BEGIN
775: if g_debug then
776: hr_utility.set_location('Entering '||l_proc,10);
777: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
778: hr_utility.set_location('p_use_points '||p_use_points,30);
779: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
780: hr_utility.set_location('p_effective_date '||p_effective_date,70);
781: end if;
774: BEGIN
775: if g_debug then
776: hr_utility.set_location('Entering '||l_proc,10);
777: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
778: hr_utility.set_location('p_use_points '||p_use_points,30);
779: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
780: hr_utility.set_location('p_effective_date '||p_effective_date,70);
781: end if;
782:
775: if g_debug then
776: hr_utility.set_location('Entering '||l_proc,10);
777: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
778: hr_utility.set_location('p_use_points '||p_use_points,30);
779: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
780: hr_utility.set_location('p_effective_date '||p_effective_date,70);
781: end if;
782:
783: hr_multi_message.enable_message_list;
776: hr_utility.set_location('Entering '||l_proc,10);
777: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
778: hr_utility.set_location('p_use_points '||p_use_points,30);
779: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
780: hr_utility.set_location('p_effective_date '||p_effective_date,70);
781: end if;
782:
783: hr_multi_message.enable_message_list;
784:
792: Fetch csr_grade_id into l_grade_id;
793: Close csr_grade_id;
794:
795: if g_debug then
796: hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
797: hr_utility.set_location('Grade Id :'||l_grade_id,110);
798: hr_utility.set_location('Step Id:'||p_step_id ,120);
799: end if;
800:
793: Close csr_grade_id;
794:
795: if g_debug then
796: hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
797: hr_utility.set_location('Grade Id :'||l_grade_id,110);
798: hr_utility.set_location('Step Id:'||p_step_id ,120);
799: end if;
800:
801: IF (l_pgm_id IS NOT NULL AND -- Grade Ladder Id
794:
795: if g_debug then
796: hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
797: hr_utility.set_location('Grade Id :'||l_grade_id,110);
798: hr_utility.set_location('Step Id:'||p_step_id ,120);
799: end if;
800:
801: IF (l_pgm_id IS NOT NULL AND -- Grade Ladder Id
802: l_grade_id IS NOT NULL AND -- Grade Id
802: l_grade_id IS NOT NULL AND -- Grade Id
803: p_step_id IS NOT NULL ) -- Step Id
804: THEN
805: if g_debug then
806: hr_utility.set_location('Rec is in HR/BEN Tables ',150);
807: end if;
808: p_rec_exists := 'MAIN';
809:
810: /* This call raise error if step is
816: p_sess => p_effective_date);
817:
818: -- If the Step is Ceiling Step Then raise Error
819: if g_debug then
820: hr_utility.set_location('Ceiling Step Y/N '||p_celing_step_flag ,180);
821: end if;
822:
823: If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
824: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
820: hr_utility.set_location('Ceiling Step Y/N '||p_celing_step_flag ,180);
821: end if;
822:
823: If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
824: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
825: hr_utility.raise_error;
826: END IF;
827:
828: l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
821: end if;
822:
823: If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
824: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
825: hr_utility.raise_error;
826: END IF;
827:
828: l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
829: if l_business_area = 'PQH_CORPS_TASK_LIST' then
839: OPEN csr_grade_crpath_node;
840: FETCH csr_grade_crpath_node into l_grade_crpath_node;
841: if csr_grade_crpath_node%found then
842: if g_debug then
843: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
844: end if;
845: hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
846: hr_utility.raise_error;
847: end if;
841: if csr_grade_crpath_node%found then
842: if g_debug then
843: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
844: end if;
845: hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
846: hr_utility.raise_error;
847: end if;
848: CLOSE csr_grade_crpath_node;
849: end if;
842: if g_debug then
843: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
844: end if;
845: hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
846: hr_utility.raise_error;
847: end if;
848: CLOSE csr_grade_crpath_node;
849: end if;
850: end if;
851:
852:
853: ELSE
854: if g_debug then
855: hr_utility.set_location('Data only in Staging Area',350);
856: end if;
857: p_rec_exists := 'STAGE';
858:
859: End IF;
895: l_exists Varchar2(10);
896:
897: BEGIN
898: if g_debug then
899: hr_utility.set_location('Entering '||l_proc,10);
900: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
901: end if;
902:
903: Open csr_profile_count;
896:
897: BEGIN
898: if g_debug then
899: hr_utility.set_location('Entering '||l_proc,10);
900: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
901: end if;
902:
903: Open csr_profile_count;
904: Fetch csr_profile_count into l_cer;
910: Close csr_profile_count;
911: return l_exists;
912:
913: if g_debug then
914: hr_utility.set_location('Leaving'||l_proc,60);
915: end if;
916:
917: EXCEPTION
918: WHEN others THEN
978: l_dummy Char(1);
979: l_proc varchar2(72) := g_package||'display_icon';
980: BEGIN
981: if g_debug then
982: hr_utility.set_location('Entering'||l_proc,10);
983: hr_utility.set_location('p_page'||p_page,20);
984: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
985: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
986: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
979: l_proc varchar2(72) := g_package||'display_icon';
980: BEGIN
981: if g_debug then
982: hr_utility.set_location('Entering'||l_proc,10);
983: hr_utility.set_location('p_page'||p_page,20);
984: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
985: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
986: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
987: end if;
980: BEGIN
981: if g_debug then
982: hr_utility.set_location('Entering'||l_proc,10);
983: hr_utility.set_location('p_page'||p_page,20);
984: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
985: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
986: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
987: end if;
988:
981: if g_debug then
982: hr_utility.set_location('Entering'||l_proc,10);
983: hr_utility.set_location('p_page'||p_page,20);
984: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
985: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
986: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
987: end if;
988:
989: -- For Grade HGrid Page
982: hr_utility.set_location('Entering'||l_proc,10);
983: hr_utility.set_location('p_page'||p_page,20);
984: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
985: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
986: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
987: end if;
988:
989: -- For Grade HGrid Page
990: If p_page = 'GRADE_HGRID' Then
1062:
1063: If p_page = 'PRG_RULE_HGRID'
1064: Then
1065: if g_debug then
1066: hr_utility.set_location('Prg Rule Hgrid page' ,40);
1067: end if;
1068:
1069: If p_Table_Alias = 'PGM'
1070: Then
1073: Elsif p_action = 'UPDATE' Then
1074: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1075: p_copy_entity_txn_id => p_copy_entity_txn_id);
1076: if g_debug then
1077: hr_utility.set_location('ELP recs Exists (Y/N) :'|| l_exists,41);
1078: end if;
1079: if l_exists = 'Y' then
1080: return 'E';
1081: else
1098: if p_action = 'ADD' Then
1099: return 'E';
1100: elsif p_action = 'UPDATE' Then
1101: if g_debug then
1102: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,50);
1103: end if;
1104: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1105: p_copy_entity_txn_id => p_copy_entity_txn_id);
1106: if g_debug then
1103: end if;
1104: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1105: p_copy_entity_txn_id => p_copy_entity_txn_id);
1106: if g_debug then
1107: hr_utility.set_location('l_exists :: '||l_exists,60);
1108: end if;
1109:
1110: if l_exists = 'Y' then
1111: return 'E';
1120: return 'E';
1121: elsif p_action = 'UPDATE'Then
1122:
1123: if g_debug then
1124: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,80);
1125: end if;
1126:
1127: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1128: p_copy_entity_txn_id => p_copy_entity_txn_id);
1126:
1127: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1128: p_copy_entity_txn_id => p_copy_entity_txn_id);
1129: if g_debug then
1130: hr_utility.set_location('l_exists :: '||l_exists,90);
1131: end if;
1132:
1133: if l_exists = 'Y' then
1134: return 'E';
1145: Else
1146: return 'N';
1147: End if;
1148: if g_debug then
1149: hr_utility.set_location('Update is through' ,50);
1150: end if;
1151:
1152: End If; -- # Progression Rules HGgrid
1153:
1151:
1152: End If; -- # Progression Rules HGgrid
1153:
1154: if g_debug then
1155: hr_utility.set_location('Leaving'||l_proc,300);
1156: end if;
1157: END DISPLAY_ICON;
1158:
1159:
1209: where copy_entity_result_id = l_grd_cer_id;
1210:
1211: BEGIN
1212: if g_debug then
1213: hr_utility.set_location('Entering '||l_proc,10);
1214: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1215: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1216: end if;
1217:
1210:
1211: BEGIN
1212: if g_debug then
1213: hr_utility.set_location('Entering '||l_proc,10);
1214: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1215: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1216: end if;
1217:
1218: Open Csr_Cer_Ids;
1211: BEGIN
1212: if g_debug then
1213: hr_utility.set_location('Entering '||l_proc,10);
1214: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1215: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1216: end if;
1217:
1218: Open Csr_Cer_Ids;
1219: Fetch Csr_Cer_Ids into l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id;
1219: Fetch Csr_Cer_Ids into l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id;
1220: Close Csr_Cer_Ids;
1221:
1222: if g_debug then
1223: hr_utility.set_location('Option Entity Result Id '||l_opt_cer_id,40);
1224: hr_utility.set_location('Pay Scale Entity Result Id '||l_pay_scale_cer_id,50);
1225: end if;
1226:
1227: Open Csr_Step_No(l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id,p_copy_entity_txn_id);
1220: Close Csr_Cer_Ids;
1221:
1222: if g_debug then
1223: hr_utility.set_location('Option Entity Result Id '||l_opt_cer_id,40);
1224: hr_utility.set_location('Pay Scale Entity Result Id '||l_pay_scale_cer_id,50);
1225: end if;
1226:
1227: Open Csr_Step_No(l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id,p_copy_entity_txn_id);
1228: Fetch Csr_Step_No into l_step_no;
1335:
1336:
1337: BEGIN
1338: if g_debug then
1339: hr_utility.set_location('Entering '||l_proc,10);
1340: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1341: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1342: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1343: end if;
1336:
1337: BEGIN
1338: if g_debug then
1339: hr_utility.set_location('Entering '||l_proc,10);
1340: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1341: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1342: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1343: end if;
1344:
1337: BEGIN
1338: if g_debug then
1339: hr_utility.set_location('Entering '||l_proc,10);
1340: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1341: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1342: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1343: end if;
1344:
1345: l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1338: if g_debug then
1339: hr_utility.set_location('Entering '||l_proc,10);
1340: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1341: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1342: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1343: end if;
1344:
1345: l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1346: if g_debug then
1343: end if;
1344:
1345: l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1346: if g_debug then
1347: hr_utility.set_location('Point/Step : '||l_step_or_point,40);
1348: end if;
1349:
1350: if (p_Table_Alias = 'PGM') then
1351: Open csr_grdldr_name;
1351: Open csr_grdldr_name;
1352: Fetch csr_grdldr_name into l_name;
1353: Close csr_grdldr_name;
1354: if g_debug then
1355: hr_utility.set_location('Grade Ladder Name '||l_name,60);
1356: end if;
1357: return l_name;
1358: elsif (p_Table_Alias = 'CPP') then
1359: Open csr_grd_name;
1359: Open csr_grd_name;
1360: Fetch csr_grd_name into l_name,l_name1;
1361: Close csr_grd_name;
1362: if g_debug then
1363: hr_utility.set_location('Grade Name '||substr(l_name,1,50),80);
1364: hr_utility.set_location('Pay Scale Name '||substr(l_name1,1,50),90);
1365: end if;
1366: if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL )) then
1367: return l_name || ' (' ||l_name1|| ')';
1360: Fetch csr_grd_name into l_name,l_name1;
1361: Close csr_grd_name;
1362: if g_debug then
1363: hr_utility.set_location('Grade Name '||substr(l_name,1,50),80);
1364: hr_utility.set_location('Pay Scale Name '||substr(l_name1,1,50),90);
1365: end if;
1366: if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL )) then
1367: return l_name || ' (' ||l_name1|| ')';
1368: else
1373: Open csr_step_name;
1374: Fetch csr_step_name into l_name,l_name1;
1375: Close csr_step_name;
1376: if g_debug then
1377: hr_utility.set_location('Step Name '||l_name,100);
1378: hr_utility.set_location('Point Name '||l_name1,120);
1379: end if;
1380: l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1381: p_copy_entity_txn_id => p_copy_entity_txn_id);
1374: Fetch csr_step_name into l_name,l_name1;
1375: Close csr_step_name;
1376: if g_debug then
1377: hr_utility.set_location('Step Name '||l_name,100);
1378: hr_utility.set_location('Point Name '||l_name1,120);
1379: end if;
1380: l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1381: p_copy_entity_txn_id => p_copy_entity_txn_id);
1382:
1380: l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1381: p_copy_entity_txn_id => p_copy_entity_txn_id);
1382:
1383: if g_debug then
1384: hr_utility.set_location('Generated Step Name : '||l_step_name,125);
1385: end if;
1386:
1387:
1388: l_bus_area := pqh_corps_utility.get_cet_business_area( p_copy_entity_txn_id => p_copy_entity_txn_id);
1386:
1387:
1388: l_bus_area := pqh_corps_utility.get_cet_business_area( p_copy_entity_txn_id => p_copy_entity_txn_id);
1389: if g_debug then
1390: hr_utility.set_location('Business Area : '||l_bus_area,130);
1391: end if;
1392: -- For Corps Rates HGrid Page need to Display Step Name like Step (Point Name | Gross Index | Increased Gross Index)
1393: If (nvl(l_bus_area,'PQH_GSP_TASK_LIST') = 'PQH_CORPS_TASK_LIST' And p_hgrid = 'RATES_HGRID' ) Then
1394:
1396: Fetch csr_corps_gross_index into l_gross_index;
1397: Close csr_corps_gross_index;
1398:
1399: if g_debug then
1400: hr_utility.set_location('Gross Index '||l_gross_index,140);
1401: end if;
1402: If l_gross_index IS NOT NULL Then
1403: l_increased_index := pqh_corps_utility.get_increased_index(p_gross_index => l_gross_index,
1404: p_copy_entity_txn_id => p_copy_entity_txn_id);
1404: p_copy_entity_txn_id => p_copy_entity_txn_id);
1405: End If;
1406:
1407: if g_debug then
1408: hr_utility.set_location('Increased Index '||l_increased_index,150);
1409: end if;
1410:
1411: if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL)) then
1412: l_corps_step_name := l_step_name || ' (' ||l_name1;
1438: Open csr_elig_name;
1439: Fetch csr_elig_name into l_name;
1440: Close csr_elig_name;
1441: if g_debug then
1442: hr_utility.set_location('Eligibility Profile Name '||l_name,60);
1443: end if;
1444: return l_name;
1445: else
1446: open csr_table_name;
1452: return null;
1453: end if;
1454: end if; -- table_name
1455: if g_debug then
1456: hr_utility.set_location('Leaving '||l_proc,100);
1457: end if;
1458: END GET_STEP_PRG_RULE_HGRID_NAME;
1459:
1460: --
1513: l_proc varchar2(72) := g_package||'CHK_GRD_DETAILS';
1514:
1515: BEGIN
1516: if g_debug then
1517: hr_utility.set_location('Entering '||l_proc,10);
1518: hr_utility.set_location('p_name '||p_name,20);
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1514:
1515: BEGIN
1516: if g_debug then
1517: hr_utility.set_location('Entering '||l_proc,10);
1518: hr_utility.set_location('p_name '||p_name,20);
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1515: BEGIN
1516: if g_debug then
1517: hr_utility.set_location('Entering '||l_proc,10);
1518: hr_utility.set_location('p_name '||p_name,20);
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1523: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1516: if g_debug then
1517: hr_utility.set_location('Entering '||l_proc,10);
1518: hr_utility.set_location('p_name '||p_name,20);
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1523: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1524: end if;
1517: hr_utility.set_location('Entering '||l_proc,10);
1518: hr_utility.set_location('p_name '||p_name,20);
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1523: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1524: end if;
1525:
1518: hr_utility.set_location('p_name '||p_name,20);
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1523: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1524: end if;
1525:
1526: hr_multi_message.enable_message_list;
1519: hr_utility.set_location('p_short_name '||p_short_name,30);
1520: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1523: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1524: end if;
1525:
1526: hr_multi_message.enable_message_list;
1527: Open csr_grade_name;
1527: Open csr_grade_name;
1528: Fetch csr_grade_name into l_grade_name;
1529: If(csr_grade_name%FOUND) THEN
1530: p_status := 'E';
1531: hr_utility.set_message(8302,'PER_7830_DEF_GRADE_EXISTS');
1532: hr_utility.raise_error;
1533: Else
1534: p_status := 'Y';
1535: End if;
1528: Fetch csr_grade_name into l_grade_name;
1529: If(csr_grade_name%FOUND) THEN
1530: p_status := 'E';
1531: hr_utility.set_message(8302,'PER_7830_DEF_GRADE_EXISTS');
1532: hr_utility.raise_error;
1533: Else
1534: p_status := 'Y';
1535: End if;
1536:
1537: Open csr_grade_short_name;
1538: Fetch csr_grade_short_name into l_short_name;
1539: If(csr_grade_short_name%FOUND) THEN
1540: p_status := 'E';
1541: hr_utility.set_message(800,'HR_289555_NON_UNIQ_SHORT_NAME');
1542: hr_utility.raise_error;
1543: Else
1544: p_status := 'Y';
1545: End if;
1538: Fetch csr_grade_short_name into l_short_name;
1539: If(csr_grade_short_name%FOUND) THEN
1540: p_status := 'E';
1541: hr_utility.set_message(800,'HR_289555_NON_UNIQ_SHORT_NAME');
1542: hr_utility.raise_error;
1543: Else
1544: p_status := 'Y';
1545: End if;
1546: if g_debug then
1543: Else
1544: p_status := 'Y';
1545: End if;
1546: if g_debug then
1547: hr_utility.set_location('Leaving '||l_proc,100);
1548: end if;
1549: Exception
1550: when others then
1551: p_status := 'E';
1627: l_proc varchar2(72) := g_package||'enddate_grade_ladder';
1628:
1629: begin
1630: if g_debug then
1631: hr_utility.set_location('Entering '||l_proc,5);
1632: hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1633: hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1634: end if;
1635:
1628:
1629: begin
1630: if g_debug then
1631: hr_utility.set_location('Entering '||l_proc,5);
1632: hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1633: hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1634: end if;
1635:
1636: for each_rec in pgm loop
1629: begin
1630: if g_debug then
1631: hr_utility.set_location('Entering '||l_proc,5);
1632: hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1633: hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1634: end if;
1635:
1636: for each_rec in pgm loop
1637: ben_program_api.delete_Program
1647: for sfl_txn in c2 loop
1648: delete_transaction(sfl_txn.copy_entity_txn_id);
1649: end loop;
1650: if g_debug then
1651: hr_utility.set_location('Successfull completion for '||l_proc,5);
1652: end if;
1653: exception when others then
1654: if g_debug then
1655: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in'||l_proc,5);
1651: hr_utility.set_location('Successfull completion for '||l_proc,5);
1652: end if;
1653: exception when others then
1654: if g_debug then
1655: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in'||l_proc,5);
1656: end if;
1657: raise;
1658: end enddate_grade_ladder;
1659:
1875: l_mode varchar2(10) := p_mode;
1876: l_proc varchar2(72) := 'gen_txn_display_name';
1877: begin
1878: if g_debug then
1879: hr_utility.set_location('Entering '||l_proc,5);
1880: hr_utility.set_location('p_program_name is '||p_program_name, 10);
1881: hr_utility.set_location('p_mode is '||p_mode, 15);
1882: end if;
1883:
1876: l_proc varchar2(72) := 'gen_txn_display_name';
1877: begin
1878: if g_debug then
1879: hr_utility.set_location('Entering '||l_proc,5);
1880: hr_utility.set_location('p_program_name is '||p_program_name, 10);
1881: hr_utility.set_location('p_mode is '||p_mode, 15);
1882: end if;
1883:
1884: --
1877: begin
1878: if g_debug then
1879: hr_utility.set_location('Entering '||l_proc,5);
1880: hr_utility.set_location('p_program_name is '||p_program_name, 10);
1881: hr_utility.set_location('p_mode is '||p_mode, 15);
1882: end if;
1883:
1884: --
1885: --If Mode is null then consider it to be a create transaction
1897: l_display_name := l_mode||':'||l_timestamp;
1898: end if;
1899:
1900: if g_debug then
1901: hr_utility.set_location('Leaving '||l_proc,5);
1902: end if;
1903: return l_display_name;
1904:
1905: EXCEPTION WHEN others THEN
1903: return l_display_name;
1904:
1905: EXCEPTION WHEN others THEN
1906: if g_debug then
1907: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1908: end if;
1909: end;
1910:
1911: --
1924:
1925: l_proc varchar2(72) := 'get_grade_ladder_name_from_txn';
1926: begin
1927: if g_debug then
1928: hr_utility.set_location('Entering '||l_proc, 5);
1929: end if;
1930:
1931: for gr_ldr in c1 loop
1932: l_grade_ladder_name := gr_ldr.grade_ladder_name;
1933: end loop;
1934: return l_grade_ladder_name;
1935:
1936: if g_debug then
1937: hr_utility.set_location('Leaving '||l_proc, 5);
1938: end if;
1939:
1940: EXCEPTION WHEN others THEN
1941: if g_debug then
1938: end if;
1939:
1940: EXCEPTION WHEN others THEN
1941: if g_debug then
1942: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1943: end if;
1944: end;
1945:
1946: --
1982: And Dflt_Pgm_Flag = 'Y';
1983: --
1984: Begin
1985: if g_debug then
1986: hr_utility.set_location('ENTERING:'||l_proc, 5);
1987: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1988: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1989: end if;
1990: --
1983: --
1984: Begin
1985: if g_debug then
1986: hr_utility.set_location('ENTERING:'||l_proc, 5);
1987: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1988: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1989: end if;
1990: --
1991: --
1984: Begin
1985: if g_debug then
1986: hr_utility.set_location('ENTERING:'||l_proc, 5);
1987: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1988: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1989: end if;
1990: --
1991: --
1992: If p_pgm_id IS NOT NULL Then
1990: --
1991: --
1992: If p_pgm_id IS NOT NULL Then
1993: if g_debug then
1994: hr_utility.set_location(' Pgm Id Is not Null', 21);
1995: end if;
1996: Open csr_curr_grdldr;
1997: Fetch csr_curr_grdldr into l_name;
1998: If csr_curr_grdldr%Found Then
1996: Open csr_curr_grdldr;
1997: Fetch csr_curr_grdldr into l_name;
1998: If csr_curr_grdldr%Found Then
1999: if g_debug then
2000: hr_utility.set_location('Current Grade Ladder is Default Grade Ladder' , 22);
2001: end if;
2002: Open csr_default_chk;
2003: Fetch csr_default_chk into l_dummy;
2004: If csr_default_chk%found then
2002: Open csr_default_chk;
2003: Fetch csr_default_chk into l_dummy;
2004: If csr_default_chk%found then
2005: close csr_default_chk;
2006: hr_utility.set_message(8302,'PQH_GSP_GRDLDR_DFLT_ERR');
2007: hr_utility.set_message_token('LADDER',l_name);
2008: hr_multi_message.add;
2009: Else
2010: close csr_default_chk;
2003: Fetch csr_default_chk into l_dummy;
2004: If csr_default_chk%found then
2005: close csr_default_chk;
2006: hr_utility.set_message(8302,'PQH_GSP_GRDLDR_DFLT_ERR');
2007: hr_utility.set_message_token('LADDER',l_name);
2008: hr_multi_message.add;
2009: Else
2010: close csr_default_chk;
2011: End if;
2013: Close csr_curr_grdldr;
2014: End If;
2015: --
2016: if g_debug then
2017: hr_utility.set_location('Leaving:'||l_proc, 35);
2018: end if;
2019:
2020: End chk_default_ladder_exist;
2021:
2090:
2091: --
2092: Begin
2093: if g_debug then
2094: hr_utility.set_location('Entering:'||l_proc, 5);
2095: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2096: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2097: end if;
2098: --
2091: --
2092: Begin
2093: if g_debug then
2094: hr_utility.set_location('Entering:'||l_proc, 5);
2095: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2096: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2097: end if;
2098: --
2099: -- Check Steps Exists for a Transaction or not
2092: Begin
2093: if g_debug then
2094: hr_utility.set_location('Entering:'||l_proc, 5);
2095: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2096: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2097: end if;
2098: --
2099: -- Check Steps Exists for a Transaction or not
2100: Open csr_steps_exists;
2125:
2126: l_no_step_grades := substr(l_no_step_grades,1,length(l_no_step_grades)-2);
2127: l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2128: if g_debug then
2129: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2130: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2126: l_no_step_grades := substr(l_no_step_grades,1,length(l_no_step_grades)-2);
2127: l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2128: if g_debug then
2129: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2130: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2134: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2127: l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2128: if g_debug then
2129: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2130: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2134: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2135: end if;
2128: if g_debug then
2129: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2130: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2134: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2135: end if;
2136:
2129: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2130: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2134: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2135: end if;
2136:
2137: If l_found1 then
2130: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2134: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2135: end if;
2136:
2137: If l_found1 then
2138: if g_debug then
2135: end if;
2136:
2137: If l_found1 then
2138: if g_debug then
2139: hr_utility.set_location('Grades not having Steps found',51);
2140: end if;
2141: hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2142: hr_utility.set_message_token('GRADES',l_no_step_grades);
2143:
2137: If l_found1 then
2138: if g_debug then
2139: hr_utility.set_location('Grades not having Steps found',51);
2140: end if;
2141: hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2142: hr_utility.set_message_token('GRADES',l_no_step_grades);
2143:
2144: hr_multi_message.add;
2145:
2138: if g_debug then
2139: hr_utility.set_location('Grades not having Steps found',51);
2140: end if;
2141: hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2142: hr_utility.set_message_token('GRADES',l_no_step_grades);
2143:
2144: hr_multi_message.add;
2145:
2146:
2146:
2147: End If;
2148: If l_found2 then
2149: if g_debug then
2150: hr_utility.set_location('Grades not having Celing Steps found',52);
2151: end if;
2152: hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2153: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2154: hr_multi_message.add;
2148: If l_found2 then
2149: if g_debug then
2150: hr_utility.set_location('Grades not having Celing Steps found',52);
2151: end if;
2152: hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2153: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2154: hr_multi_message.add;
2155: End If;
2156: if g_debug then
2149: if g_debug then
2150: hr_utility.set_location('Grades not having Celing Steps found',52);
2151: end if;
2152: hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2153: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2154: hr_multi_message.add;
2155: End If;
2156: if g_debug then
2157: hr_utility.set_location('Leaving:'||l_proc,100);
2153: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2154: hr_multi_message.add;
2155: End If;
2156: if g_debug then
2157: hr_utility.set_location('Leaving:'||l_proc,100);
2158: end if;
2159:
2160: End chk_add_steps_in_all_grades;
2161: --
2196: AND nvl(grd.Information307,hr_general.end_of_time)
2197: AND Information4 = p_business_group_id;
2198: BEGIN
2199: if g_debug then
2200: hr_utility.set_location('Entering:'||l_proc,10);
2201: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2202: hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2203: end if;
2204:
2197: AND Information4 = p_business_group_id;
2198: BEGIN
2199: if g_debug then
2200: hr_utility.set_location('Entering:'||l_proc,10);
2201: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2202: hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2203: end if;
2204:
2205: For l_rec in csr_grades
2198: BEGIN
2199: if g_debug then
2200: hr_utility.set_location('Entering:'||l_proc,10);
2201: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2202: hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2203: end if;
2204:
2205: For l_rec in csr_grades
2206: Loop
2209: End loop;
2210: l_grades := substr(l_grades,1,length(l_grades)-2);
2211:
2212: if g_debug then
2213: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2214: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2215: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2216: end if;
2217:
2210: l_grades := substr(l_grades,1,length(l_grades)-2);
2211:
2212: if g_debug then
2213: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2214: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2215: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2216: end if;
2217:
2218:
2211:
2212: if g_debug then
2213: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2214: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2215: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2216: end if;
2217:
2218:
2219: If l_found then
2216: end if;
2217:
2218:
2219: If l_found then
2220: hr_utility.set_message(8302,'PQH_GSP_INVALID_GRD_IN_GRDLDR');
2221: hr_utility.set_message_token('GRADES',l_grades);
2222: hr_multi_message.add;
2223: End If;
2224:
2217:
2218:
2219: If l_found then
2220: hr_utility.set_message(8302,'PQH_GSP_INVALID_GRD_IN_GRDLDR');
2221: hr_utility.set_message_token('GRADES',l_grades);
2222: hr_multi_message.add;
2223: End If;
2224:
2225: if g_debug then
2222: hr_multi_message.add;
2223: End If;
2224:
2225: if g_debug then
2226: hr_utility.set_location('Leaving:'||l_proc, 15);
2227: end if;
2228: End chk_valid_grd_in_grdldr;
2229:
2230: --
2268:
2269:
2270: Begin
2271: if g_debug then
2272: hr_utility.set_location('Entering:'||l_proc, 10);
2273: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2274: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2275: hr_utility.set_location('p_activate:'||p_activate, 35);
2276: end if;
2269:
2270: Begin
2271: if g_debug then
2272: hr_utility.set_location('Entering:'||l_proc, 10);
2273: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2274: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2275: hr_utility.set_location('p_activate:'||p_activate, 35);
2276: end if;
2277: hr_multi_message.enable_message_list;
2270: Begin
2271: if g_debug then
2272: hr_utility.set_location('Entering:'||l_proc, 10);
2273: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2274: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2275: hr_utility.set_location('p_activate:'||p_activate, 35);
2276: end if;
2277: hr_multi_message.enable_message_list;
2278:
2271: if g_debug then
2272: hr_utility.set_location('Entering:'||l_proc, 10);
2273: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2274: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2275: hr_utility.set_location('p_activate:'||p_activate, 35);
2276: end if;
2277: hr_multi_message.enable_message_list;
2278:
2279: If p_pgm_id IS NOT NULL Then
2277: hr_multi_message.enable_message_list;
2278:
2279: If p_pgm_id IS NOT NULL Then
2280: if g_debug then
2281: hr_utility.set_location('PGM Id Not Null ', 40);
2282: end if;
2283:
2284: -- Get Status of PGM
2285: Open csr_pgm_status;
2285: Open csr_pgm_status;
2286: Fetch csr_pgm_status into l_old_status;
2287: Close csr_pgm_status;
2288: if g_debug then
2289: hr_utility.set_location('PGM Status :'||l_old_status, 41);
2290: end if;
2291:
2292: -- Raise the error in the following case.
2293: -- 1) If PGM is Active And
2300: Fetch csr_active_gl into l_dummy;
2301: If csr_active_gl%Found then
2302: Close csr_active_gl;
2303: if g_debug then
2304: hr_utility.set_location('Found ', 45);
2305: end if;
2306: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2307: hr_utility.raise_error;
2308: Else
2302: Close csr_active_gl;
2303: if g_debug then
2304: hr_utility.set_location('Found ', 45);
2305: end if;
2306: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2307: hr_utility.raise_error;
2308: Else
2309: if g_debug then
2310: hr_utility.set_location('Not Found ', 50);
2303: if g_debug then
2304: hr_utility.set_location('Found ', 45);
2305: end if;
2306: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2307: hr_utility.raise_error;
2308: Else
2309: if g_debug then
2310: hr_utility.set_location('Not Found ', 50);
2311: end if;
2306: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2307: hr_utility.raise_error;
2308: Else
2309: if g_debug then
2310: hr_utility.set_location('Not Found ', 50);
2311: end if;
2312: Close csr_active_gl;
2313: End If;
2314: End If; -- stauts check
2314: End If; -- stauts check
2315: End If; -- Pgm Not Null
2316:
2317: if g_debug then
2318: hr_utility.set_location('Leaving:'||l_proc, 55);
2319: end if;
2320: EXCEPTION
2321: WHEN others THEN
2322: fnd_msg_pub.add;
2454:
2455:
2456: Begin
2457: if g_debug then
2458: hr_utility.set_location('Entering:'||l_proc, 10);
2459: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2460: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2461: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2462: end if;
2455:
2456: Begin
2457: if g_debug then
2458: hr_utility.set_location('Entering:'||l_proc, 10);
2459: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2460: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2461: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2462: end if;
2463:
2456: Begin
2457: if g_debug then
2458: hr_utility.set_location('Entering:'||l_proc, 10);
2459: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2460: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2461: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2462: end if;
2463:
2464:
2457: if g_debug then
2458: hr_utility.set_location('Entering:'||l_proc, 10);
2459: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2460: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2461: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2462: end if;
2463:
2464:
2465: Open csr_pgm_id;
2466: Fetch csr_pgm_id into l_pgm_id ;
2467: Close csr_pgm_id;
2468:
2469: if g_debug then
2470: hr_utility.set_location('l_pgm_id:'||l_pgm_id, 30);
2471: end if;
2472:
2473: Open csr_grade_id;
2474: Fetch csr_grade_id into l_grade_id;
2474: Fetch csr_grade_id into l_grade_id;
2475: Close csr_grade_id;
2476:
2477: if g_debug then
2478: hr_utility.set_location('l_grade_id:'||l_grade_id, 40);
2479: end if;
2480:
2481: If (l_pgm_id IS Not Null And l_grade_id IS Not Null) Then
2482: if g_debug then
2479: end if;
2480:
2481: If (l_pgm_id IS Not Null And l_grade_id IS Not Null) Then
2482: if g_debug then
2483: hr_utility.set_location('Record is in Main Tables', 50);
2484: end if;
2485: if p_grade_result_id IS NOT NULL Then
2486: Open csr_ovn(p_grade_result_id);
2487: Fetch csr_ovn into l_ovn;
2496:
2497: end if;
2498: Else
2499: if g_debug then
2500: hr_utility.set_location('Record is in Staging Area', 50);
2501: end if;
2502: if p_grade_result_id IS NOT NULL Then
2503:
2504: -- Update Plan Record with Gs_Mirror_Src_Entity_Result_Id as Null
2531: end if;
2532:
2533: End if;
2534: if g_debug then
2535: hr_utility.set_location('Leaving:'||l_proc, 10);
2536: end if;
2537:
2538: End update_or_delete_grade;
2539:
2607:
2608:
2609: Begin
2610: if g_debug then
2611: hr_utility.set_location('Entering:'||l_proc, 10);
2612: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2613: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2614: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2615: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2608:
2609: Begin
2610: if g_debug then
2611: hr_utility.set_location('Entering:'||l_proc, 10);
2612: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2613: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2614: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2615: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2616: end if;
2609: Begin
2610: if g_debug then
2611: hr_utility.set_location('Entering:'||l_proc, 10);
2612: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2613: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2614: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2615: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2616: end if;
2617:
2610: if g_debug then
2611: hr_utility.set_location('Entering:'||l_proc, 10);
2612: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2613: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2614: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2615: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2616: end if;
2617:
2618:
2611: hr_utility.set_location('Entering:'||l_proc, 10);
2612: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2613: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2614: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2615: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2616: end if;
2617:
2618:
2619: l_use_points:= pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id=>p_copy_entity_txn_id);
2617:
2618:
2619: l_use_points:= pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id=>p_copy_entity_txn_id);
2620: if g_debug then
2621: hr_utility.set_location('l_use_points:'||l_use_points, 50);
2622: end if;
2623:
2624: Open csr_pgm_id;
2625: Fetch csr_pgm_id into l_pgm_id;
2624: Open csr_pgm_id;
2625: Fetch csr_pgm_id into l_pgm_id;
2626: Close csr_pgm_id;
2627: if g_debug then
2628: hr_utility.set_location('l_pgm_id:'||l_pgm_id,60);
2629: end if;
2630:
2631: Open csr_grade_id;
2632: Fetch csr_grade_id into l_grade_id;
2631: Open csr_grade_id;
2632: Fetch csr_grade_id into l_grade_id;
2633: Close csr_grade_id;
2634: if g_debug then
2635: hr_utility.set_location('l_grade_id:'||l_grade_id,70);
2636: end if;
2637:
2638: -- Rec In Main Tables
2639: If (l_pgm_id IS NOT Null And l_grade_id IS NOT Null And p_step_id IS NOT Null) Then
2637:
2638: -- Rec In Main Tables
2639: If (l_pgm_id IS NOT Null And l_grade_id IS NOT Null And p_step_id IS NOT Null) Then
2640: if g_debug then
2641: hr_utility.set_location('Rec is in Main Tables',80);
2642: end if;
2643:
2644: -- Update Point Record
2645: if ((l_use_points = 'STEP') AND (p_point_result_id IS NOT NULL)) then
2655: p_information323 => null);
2656:
2657: end if;
2658: if g_debug then
2659: hr_utility.set_location('Point Rec is Updated Sucessfully',90);
2660: end if;
2661:
2662: -- Update Step Record
2663: if p_step_result_id IS NOT NULL then
2673: p_information323 => null);
2674:
2675: End if;
2676: if g_debug then
2677: hr_utility.set_location('Step Rec is Updated Sucessfully',100);
2678: end if;
2679: Else
2680: if g_debug then
2681: hr_utility.set_location('Record is in Staging Area Only',110);
2677: hr_utility.set_location('Step Rec is Updated Sucessfully',100);
2678: end if;
2679: Else
2680: if g_debug then
2681: hr_utility.set_location('Record is in Staging Area Only',110);
2682: end if;
2683: -- Delete Point Record
2684: if ((l_use_points = 'STEP') And p_point_result_id IS NOT NULL) then
2685:
2691: p_effective_date => p_effective_date,
2692: p_object_version_number => l_ovn);
2693: end if;
2694: if g_debug then
2695: hr_utility.set_location('Point Rec is Deleted Sucessfully',120);
2696: end if;
2697:
2698: -- Delete Step Record
2699: if p_step_result_id IS NOT NULL then
2706: p_effective_date => p_effective_date,
2707: p_object_version_number => l_ovn);
2708: end if;
2709: if g_debug then
2710: hr_utility.set_location('Step Rec is Deleted Sucessfully',130);
2711: end if;
2712:
2713: End if;
2714: if g_debug then
2711: end if;
2712:
2713: End if;
2714: if g_debug then
2715: hr_utility.set_location('Leaving :' ||l_proc, 200);
2716: end if;
2717: End update_or_delete_step;
2718:
2719: --
2744: order by opt.information253;
2745: stepnum NUMBER :=0;
2746: begin
2747: if g_debug then
2748: hr_utility.set_location('Entering:'||l_proc, 5);
2749: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2750: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2751: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2752: end if;
2745: stepnum NUMBER :=0;
2746: begin
2747: if g_debug then
2748: hr_utility.set_location('Entering:'||l_proc, 5);
2749: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2750: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2751: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2752: end if;
2753: for i in csr_points loop
2746: begin
2747: if g_debug then
2748: hr_utility.set_location('Entering:'||l_proc, 5);
2749: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2750: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2751: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2752: end if;
2753: for i in csr_points loop
2754: stepnum :=stepnum+1;
2747: if g_debug then
2748: hr_utility.set_location('Entering:'||l_proc, 5);
2749: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2750: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2751: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2752: end if;
2753: for i in csr_points loop
2754: stepnum :=stepnum+1;
2755: ben_copy_entity_results_api.update_copy_entity_results(
2761: p_object_version_number => i.object_version_number,
2762: p_information323 => null);
2763: end loop;
2764: if g_debug then
2765: hr_utility.set_location('Leaving:'||l_proc, 35);
2766: end if;
2767: END SET_STEP_NAME;
2768:
2769: --
2834: And nvl(Effective_End_Date,hr_general.end_of_time);
2835:
2836: BEGIN
2837: if g_debug then
2838: hr_utility.set_location('Entering:'||l_proc, 5);
2839: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2840: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2841: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2842: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2835:
2836: BEGIN
2837: if g_debug then
2838: hr_utility.set_location('Entering:'||l_proc, 5);
2839: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2840: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2841: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2842: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2843: end if;
2836: BEGIN
2837: if g_debug then
2838: hr_utility.set_location('Entering:'||l_proc, 5);
2839: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2840: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2841: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2842: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2843: end if;
2844: p_status := null;
2837: if g_debug then
2838: hr_utility.set_location('Entering:'||l_proc, 5);
2839: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2840: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2841: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2842: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2843: end if;
2844: p_status := null;
2845:
2838: hr_utility.set_location('Entering:'||l_proc, 5);
2839: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2840: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2841: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2842: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2843: end if;
2844: p_status := null;
2845:
2846: If p_pgm_id IS NOT NULL Then
2847: Open csr_default_grdldr;
2848: Fetch csr_default_grdldr into l_dummy;
2849: If csr_default_grdldr%NotFound Then
2850: if g_debug then
2851: hr_utility.set_location('Not Default Grade Ladder', 26);
2852: end if;
2853: --If Emp Placements exists on Grades then throw error msg
2854: For l_rec IN csr_grades
2855: Loop
2862: Close csr_gl_grd_emp_assg;
2863: End Loop;
2864: Else
2865: if g_debug then
2866: hr_utility.set_location(' Default Grade Ladder', 27);
2867: end if;
2868: -- If Emp Placements exists on Default Grade Ladder Grades then throw warning msg
2869: For l_rec IN csr_grades
2870: Loop
2871: Open csr_grd_assg_emp(l_rec.Information253);
2872: Fetch csr_grd_assg_emp into l_dummy;
2873: If csr_grd_assg_emp%Found Then
2874: if g_debug then
2875: hr_utility.set_location(' Grades are ... '||l_rec.Information5, 28);
2876: end if;
2877: l_no_war_grades := l_no_war_grades || l_rec.Information5 ||' , ';
2878: l_grd_war_found := TRUE;
2879: End If;
2883: Close csr_default_grdldr;
2884:
2885: If l_found then
2886: if g_debug then
2887: hr_utility.set_location(' Found Error Grades', 27);
2888: end if;
2889: l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2890: p_status := 'E';
2891: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2887: hr_utility.set_location(' Found Error Grades', 27);
2888: end if;
2889: l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2890: p_status := 'E';
2891: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2892: hr_utility.set_message_token('GRADES',l_no_grades );
2893: hr_multi_message.add;
2894: End if;
2895:
2888: end if;
2889: l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2890: p_status := 'E';
2891: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2892: hr_utility.set_message_token('GRADES',l_no_grades );
2893: hr_multi_message.add;
2894: End if;
2895:
2896: if l_grd_war_found then
2894: End if;
2895:
2896: if l_grd_war_found then
2897: if g_debug then
2898: hr_utility.set_location(' Found Warning Grades', 27);
2899: end if;
2900: l_no_war_grades := substr(l_no_war_grades,1,length(l_no_war_grades)-2);
2901: hr_utility.set_location(' Length '||length(l_no_war_grades), 28);
2902: p_status := l_no_war_grades;
2897: if g_debug then
2898: hr_utility.set_location(' Found Warning Grades', 27);
2899: end if;
2900: l_no_war_grades := substr(l_no_war_grades,1,length(l_no_war_grades)-2);
2901: hr_utility.set_location(' Length '||length(l_no_war_grades), 28);
2902: p_status := l_no_war_grades;
2903: end if;
2904: End If;
2905: if g_debug then
2902: p_status := l_no_war_grades;
2903: end if;
2904: End If;
2905: if g_debug then
2906: hr_utility.set_location('Leaving:'||l_proc, 100);
2907: end if;
2908: Exception
2909: When Others Then
2910: if g_debug then
2907: end if;
2908: Exception
2909: When Others Then
2910: if g_debug then
2911: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2912: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2913: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2914: end if;
2915:
2908: Exception
2909: When Others Then
2910: if g_debug then
2911: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2912: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2913: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2914: end if;
2915:
2916: p_status := null;
2909: When Others Then
2910: if g_debug then
2911: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2912: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2913: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2914: end if;
2915:
2916: p_status := null;
2917: END chk_unlink_grd_from_grdldr;
2949: And Information4 = p_business_group_id ;
2950:
2951: BEGIN
2952: if g_debug then
2953: hr_utility.set_location('Entering:'||l_proc, 5);
2954: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2955: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2956: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2957: end if;
2950:
2951: BEGIN
2952: if g_debug then
2953: hr_utility.set_location('Entering:'||l_proc, 5);
2954: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2955: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2956: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2957: end if;
2958:
2951: BEGIN
2952: if g_debug then
2953: hr_utility.set_location('Entering:'||l_proc, 5);
2954: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2955: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2956: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2957: end if;
2958:
2959: For l_rec IN csr_steps
2952: if g_debug then
2953: hr_utility.set_location('Entering:'||l_proc, 5);
2954: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2955: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2956: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2957: end if;
2958:
2959: For l_rec IN csr_steps
2960: Loop
2961: PER_SPINAL_POINT_STEPS_PKG.del_chks_del(p_step_id => l_rec.Information253,
2962: p_sess => p_effective_date);
2963:
2964: If (l_rec.INFORMATION98 = 'Y') Then -- Ceiling_Step_Flag Values Y/N
2965: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
2966: hr_utility.raise_error;
2967: END IF;
2968: End Loop;
2969:
2962: p_sess => p_effective_date);
2963:
2964: If (l_rec.INFORMATION98 = 'Y') Then -- Ceiling_Step_Flag Values Y/N
2965: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
2966: hr_utility.raise_error;
2967: END IF;
2968: End Loop;
2969:
2970: if g_debug then
2967: END IF;
2968: End Loop;
2969:
2970: if g_debug then
2971: hr_utility.set_location('Leaving:'||l_proc, 100);
2972: end if;
2973:
2974: END chk_unlink_step_from_grdldr;
2975:
2996:
2997: --
2998: Begin
2999: if g_debug then
3000: hr_utility.set_location('Entering:'||l_proc, 5);
3001: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3002: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3003: end if;
3004:
2997: --
2998: Begin
2999: if g_debug then
3000: hr_utility.set_location('Entering:'||l_proc, 5);
3001: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3002: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3003: end if;
3004:
3005:
2998: Begin
2999: if g_debug then
3000: hr_utility.set_location('Entering:'||l_proc, 5);
3001: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3002: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3003: end if;
3004:
3005:
3006: --
3004:
3005:
3006: --
3007: if g_debug then
3008: hr_utility.set_location('Chk Name...', 50);
3009: end if;
3010: Open csr_name;
3011: Fetch csr_name into l_dummy;
3012: If csr_name%Found then
3010: Open csr_name;
3011: Fetch csr_name into l_dummy;
3012: If csr_name%Found then
3013: Close csr_name;
3014: hr_utility.set_message(8302,'PQH_GSP_GRDLDR_NAME_UNIQUE');
3015: hr_multi_message.add;
3016: Else
3017: Close csr_name;
3018: End If;
3017: Close csr_name;
3018: End If;
3019: --
3020: if g_debug then
3021: hr_utility.set_location('Leaving:'||l_proc, 15);
3022: end if;
3023: End chk_grdldr_name_unique;
3024:
3025: --
3055:
3056: --
3057: Begin
3058: if g_debug then
3059: hr_utility.set_location('Entering:'||l_proc, 5);
3060: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062: hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063: hr_utility.set_location('p_short_code:'||p_short_code, 25);
3056: --
3057: Begin
3058: if g_debug then
3059: hr_utility.set_location('Entering:'||l_proc, 5);
3060: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062: hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063: hr_utility.set_location('p_short_code:'||p_short_code, 25);
3064: hr_utility.set_location('Chk Short Name...', 60);
3057: Begin
3058: if g_debug then
3059: hr_utility.set_location('Entering:'||l_proc, 5);
3060: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062: hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063: hr_utility.set_location('p_short_code:'||p_short_code, 25);
3064: hr_utility.set_location('Chk Short Name...', 60);
3065: end if;
3058: if g_debug then
3059: hr_utility.set_location('Entering:'||l_proc, 5);
3060: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062: hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063: hr_utility.set_location('p_short_code:'||p_short_code, 25);
3064: hr_utility.set_location('Chk Short Name...', 60);
3065: end if;
3066:
3059: hr_utility.set_location('Entering:'||l_proc, 5);
3060: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062: hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063: hr_utility.set_location('p_short_code:'||p_short_code, 25);
3064: hr_utility.set_location('Chk Short Name...', 60);
3065: end if;
3066:
3067: Open csr_short_name;
3060: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062: hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063: hr_utility.set_location('p_short_code:'||p_short_code, 25);
3064: hr_utility.set_location('Chk Short Name...', 60);
3065: end if;
3066:
3067: Open csr_short_name;
3068: Fetch csr_short_name into l_dummy;
3067: Open csr_short_name;
3068: Fetch csr_short_name into l_dummy;
3069: If csr_short_name%Found then
3070: Close csr_short_name;
3071: hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_CODE_UNQ');
3072: hr_multi_message.add;
3073: Else
3074: Close csr_short_name;
3075: End If;
3074: Close csr_short_name;
3075: End If;
3076: --
3077: if g_debug then
3078: hr_utility.set_location('Chk Short Code....', 70);
3079: end if;
3080: Open csr_short_code;
3081: Fetch csr_short_code into l_dummy;
3082: If csr_short_code%Found then
3080: Open csr_short_code;
3081: Fetch csr_short_code into l_dummy;
3082: If csr_short_code%Found then
3083: Close csr_short_code;
3084: hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_CODE_UNQ');
3085: hr_multi_message.add;
3086: Else
3087: Close csr_short_code;
3088: End If;
3087: Close csr_short_code;
3088: End If;
3089: --
3090: if g_debug then
3091: hr_utility.set_location('Leaving:'||l_proc, 15);
3092: end if;
3093: End chk_gl_sht_name_code_unique;
3094:
3095:
3139: l_proc varchar2(72) := g_package||'validate_grade_ladder';
3140:
3141: BEGIN
3142: if g_debug then
3143: hr_utility.set_location('Entering :' ||l_proc, 10);
3144: end if;
3145:
3146: -- Check Grade Ladder Name is Unique with in the Business Group
3147: if g_debug then
3144: end if;
3145:
3146: -- Check Grade Ladder Name is Unique with in the Business Group
3147: if g_debug then
3148: hr_utility.set_location('Check Grade Ladder Name is Unique ', 20);
3149: end if;
3150: pqh_gsp_utility.chk_grdldr_name_unique( p_pgm_id => p_pgm_id
3151: ,p_business_group_id => p_business_group_id
3152: ,p_name => p_name);
3152: ,p_name => p_name);
3153:
3154: -- Check Grade Ladder , Short Name and Short Code is Unique with in the Business Group
3155: if g_debug then
3156: hr_utility.set_location('Check Grade Ladder Short Name and Short Code is Unique ', 25);
3157: end if;
3158:
3159: If (p_short_name IS NOT NULL or p_short_code IS NOT NULL) Then
3160: pqh_gsp_utility.chk_gl_sht_name_code_unique( p_pgm_id => p_pgm_id
3164:
3165: End If;
3166: -- Check if there is only one default grade ladder in the business group
3167: if g_debug then
3168: hr_utility.set_location('Default Grade Ladder in BG ', 30);
3169: end if;
3170:
3171: pqh_gsp_utility.chk_default_ladder_exist ( p_pgm_id => p_pgm_id,
3172: p_business_group_id => p_business_group_id,
3176: -- which there are employee placements.
3177:
3178: /*
3179: if g_debug then
3180: hr_utility.set_location('Inactivated GL ', 40);
3181: end if;
3182: pqh_gsp_utility.chk_inactivate_grdldr ( p_pgm_id => p_pgm_id,
3183: p_effective_date => p_effective_date,
3184: p_business_group_id => p_business_group_id);
3190: -- Check if score calculation method is entered against lookup
3191: -- Check if score calcultion method is rule, the fast formula is selected
3192: -- Validate all lookups
3193: if g_debug then
3194: hr_utility.set_location('Leaving :' ||l_proc, 100);
3195: end if;
3196: End;
3197: --
3198: --
3282:
3283:
3284: BEGIN
3285: if g_debug then
3286: hr_utility.set_location('Entering :' ||l_proc, 10);
3287: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3288: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3289: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3290: end if;
3283:
3284: BEGIN
3285: if g_debug then
3286: hr_utility.set_location('Entering :' ||l_proc, 10);
3287: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3288: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3289: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3290: end if;
3291:
3284: BEGIN
3285: if g_debug then
3286: hr_utility.set_location('Entering :' ||l_proc, 10);
3287: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3288: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3289: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3290: end if;
3291:
3292:
3285: if g_debug then
3286: hr_utility.set_location('Entering :' ||l_proc, 10);
3287: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3288: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3289: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3290: end if;
3291:
3292:
3293: Open csr_grdldr_details;
3294: Fetch csr_grdldr_details into l_curr_gl_result_id,l_curr_gl_currency,l_curr_gl_period;
3295: Close csr_grdldr_details;
3296:
3297: if g_debug then
3298: hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3299: hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3300: hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3301: end if;
3302:
3295: Close csr_grdldr_details;
3296:
3297: if g_debug then
3298: hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3299: hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3300: hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3301: end if;
3302:
3303: -- fix for bug 7114098
3296:
3297: if g_debug then
3298: hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3299: hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3300: hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3301: end if;
3302:
3303: -- fix for bug 7114098
3304: open csr_pgm_val;
3321:
3322: if l_found Then
3323: l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3324: if g_debug then
3325: hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3326: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3327: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3328: end if;
3329:
3322: if l_found Then
3323: l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3324: if g_debug then
3325: hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3326: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3327: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3328: end if;
3329:
3330: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3323: l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3324: if g_debug then
3325: hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3326: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3327: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3328: end if;
3329:
3330: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3331: hr_utility.set_message_token('GRADES',l_dummy );
3326: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3327: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3328: end if;
3329:
3330: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3331: hr_utility.set_message_token('GRADES',l_dummy );
3332: hr_multi_message.add;
3333: end if;
3334: if g_debug then
3327: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3328: end if;
3329:
3330: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3331: hr_utility.set_message_token('GRADES',l_dummy );
3332: hr_multi_message.add;
3333: end if;
3334: if g_debug then
3335: hr_utility.set_location('Leaving :' ||l_proc, 100);
3331: hr_utility.set_message_token('GRADES',l_dummy );
3332: hr_multi_message.add;
3333: end if;
3334: if g_debug then
3335: hr_utility.set_location('Leaving :' ||l_proc, 100);
3336: end if;
3337: END chk_grdldr_grd_curreny_rate;
3338:
3339: --
3374: l_proc varchar2(200) := 'chk_duplicate_crset_exists';
3375: --
3376: Begin
3377: --
3378: hr_utility.set_location('Entering :' ||l_proc, 5);
3379: --
3380: p_duplicate_exists := 'N';
3381: --
3382: For crset_rec in csr_crset loop
3395: nvl(crset_rec.INFORMATION104,'XXX') = nvl(p_rating_type_cd,'XXX')) then
3396: --
3397: p_duplicate_cset_name := crset_rec.INFORMATION151;
3398: p_duplicate_exists := 'Y';
3399: hr_utility.set_location('Duplicate :' ||p_duplicate_cset_name, 7);
3400: --
3401: End if;
3402: --
3403: --
3402: --
3403: --
3404: End loop;
3405: --
3406: hr_utility.set_location('Leaving :' ||l_proc, 10);
3407: --
3408: End;
3409: -------------------------------------------------------------------
3410: -- This procedure validates if criteria set has been properly defined i.e
3431: l_continue boolean;
3432: --
3433: Begin
3434: --
3435: hr_utility.set_location('Entering :' ||l_proc, 5);
3436: --
3437: For crset_rec in csr_crset loop
3438: --
3439: l_continue := true;
3440: --
3441: -- Criteria set name should be entered. Ideally this error should never be raised.
3442: --
3443: If crset_rec.INFORMATION151 is NULL then
3444: hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NAME_ERR');
3445: hr_multi_message.add;
3446: l_continue := false;
3447: End if;
3448: --
3449: -- Criteria set should have at least one criterion
3450: --
3451: If l_continue then
3452: --
3453: hr_utility.set_location('Check 1' , 5);
3454: --
3455: If (crset_rec.INFORMATION232 IS NULL and
3456: crset_rec.INFORMATION233 IS NULL and
3457: crset_rec.INFORMATION234 IS NULL and
3462: crset_rec.INFORMATION102 IS NULL and
3463: crset_rec.INFORMATION103 IS NULL and
3464: crset_rec.INFORMATION104 IS NULL ) then
3465: --
3466: hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NO_CRIT');
3467: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3468: hr_multi_message.add;
3469: l_continue := false;
3470: --
3463: crset_rec.INFORMATION103 IS NULL and
3464: crset_rec.INFORMATION104 IS NULL ) then
3465: --
3466: hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NO_CRIT');
3467: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3468: hr_multi_message.add;
3469: l_continue := false;
3470: --
3471: End if;
3474:
3475: If l_continue then
3476: --
3477: --
3478: hr_utility.set_location('Perf Rating Check ' , 5);
3479: --
3480: -- If performance type is entered, rating type must also be entered.
3481: --
3482: If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3481: --
3482: If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3483: (crset_rec.INFORMATION103 IS NOT NULL and crset_rec.INFORMATION104 IS NULL)) then
3484: --
3485: hr_utility.set_message(8302,'PQH_GSP_INVALID_PERF_RATING');
3486: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3487: hr_multi_message.add;
3488: l_continue := false;
3489: --
3482: If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3483: (crset_rec.INFORMATION103 IS NOT NULL and crset_rec.INFORMATION104 IS NULL)) then
3484: --
3485: hr_utility.set_message(8302,'PQH_GSP_INVALID_PERF_RATING');
3486: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3487: hr_multi_message.add;
3488: l_continue := false;
3489: --
3490: End if;
3495: --
3496: If l_continue then
3497: --
3498: --
3499: hr_utility.set_location('Duplicate Check' || to_char(crset_rec.information161) , 5);
3500: --
3501: chk_duplicate_crset_exists(
3502: p_copy_entity_txn_id => p_copy_entity_txn_id,
3503: p_effective_date => p_effective_date,
3516: p_duplicate_cset_name => l_duplicate_cset_name);
3517: --
3518: If l_duplicate_exists = 'Y' then
3519: --
3520: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3521: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3522: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3523: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3524: hr_multi_message.add;
3517: --
3518: If l_duplicate_exists = 'Y' then
3519: --
3520: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3521: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3522: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3523: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3524: hr_multi_message.add;
3525: --
3518: If l_duplicate_exists = 'Y' then
3519: --
3520: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3521: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3522: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3523: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3524: hr_multi_message.add;
3525: --
3526: End if;
3519: --
3520: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3521: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3522: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3523: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3524: hr_multi_message.add;
3525: --
3526: End if;
3527: --
3528: End if;
3529: --
3530: End loop;
3531: --
3532: hr_utility.set_location('Leaving :' ||l_proc, 10);
3533: --
3534: End;
3535: ---------------------------chk_review_submit_val-----------------------------
3536: --
3584:
3585: BEGIN
3586: hr_multi_message.enable_message_list;
3587: if g_debug then
3588: hr_utility.set_location('Entering :' ||l_proc, 10);
3589: end if;
3590:
3591: If p_pgm_id IS NOT NULL Then
3592: Open csr_pgm_details;
3610: end if;
3611:
3612: -- Check Grades can be removed from the Grade Ladder
3613: if g_debug then
3614: hr_utility.set_location('Check Grades can be removed from the Grade Ladder', 20);
3615: end if;
3616: pqh_gsp_utility.chk_unlink_grd_from_grdldr
3617: (p_pgm_id => p_pgm_id
3618: ,p_copy_entity_txn_id => p_copy_entity_txn_id
3620: ,p_effective_date => p_effective_date
3621: ,p_status => l_status
3622: );
3623: if g_debug then
3624: hr_utility.set_location('After Check unlink grd from gl ',25);
3625: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3626: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3627: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3628: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3621: ,p_status => l_status
3622: );
3623: if g_debug then
3624: hr_utility.set_location('After Check unlink grd from gl ',25);
3625: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3626: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3627: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3628: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3629: end if;
3622: );
3623: if g_debug then
3624: hr_utility.set_location('After Check unlink grd from gl ',25);
3625: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3626: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3627: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3628: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3629: end if;
3630:
3623: if g_debug then
3624: hr_utility.set_location('After Check unlink grd from gl ',25);
3625: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3626: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3627: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3628: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3629: end if;
3630:
3631: -- Check if steps have been added to only some steps in a grade
3624: hr_utility.set_location('After Check unlink grd from gl ',25);
3625: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3626: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3627: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3628: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3629: end if;
3630:
3631: -- Check if steps have been added to only some steps in a grade
3632: -- and warns the user that the setup cannot be saved unless steps are added to all
3632: -- and warns the user that the setup cannot be saved unless steps are added to all
3633: -- the Grades.
3634:
3635: if g_debug then
3636: hr_utility.set_location('Only Some Steps are attached Validation', 30);
3637: end if;
3638: pqh_gsp_utility.chk_add_steps_in_all_grades ( p_copy_entity_txn_id => p_copy_entity_txn_id ,
3639: p_business_group_id => p_business_group_id);
3640:
3646: -- Check if Grades are valid as of the Grade Ladder Effective Date
3647:
3648:
3649: if g_debug then
3650: hr_utility.set_location('InValid Grades Validation', 40);
3651: end if;
3652: pqh_gsp_utility.chk_valid_grd_in_grdldr ( p_copy_entity_txn_id => p_copy_entity_txn_id,
3653: p_effective_date => p_effective_date,
3654: p_business_group_id => p_business_group_id);
3656:
3657: -- Check Currecny, Rate Period
3658:
3659: if g_debug then
3660: hr_utility.set_location('Currecny, Rate Period Validation', 70);
3661: end if;
3662: pqh_gsp_utility.chk_grdldr_grd_curreny_rate ( p_copy_entity_txn_id => p_copy_entity_txn_id
3663: ,p_business_group_id => p_business_group_id
3664: ,p_effective_date => p_effective_date);
3664: ,p_effective_date => p_effective_date);
3665:
3666: -- Check Steps can be removed from the Grade Ladder
3667: if g_debug then
3668: hr_utility.set_location('Check Steps can be removed from the Grade Ladder', 80);
3669: end if;
3670:
3671: pqh_gsp_utility.chk_unlink_step_from_grdldr
3672: (p_copy_entity_txn_id => p_copy_entity_txn_id
3675: );
3676:
3677: -- Check for Plan Type and Life Event
3678: if g_debug then
3679: hr_utility.set_location('Check Plan Type and Life Event', 85);
3680: end if;
3681: pqh_gsp_stage_to_hr.setup_check( p_copy_entity_txn_id => p_copy_entity_txn_id,
3682: p_effective_date => p_effective_date,
3683: p_business_group_id => p_business_group_id,
3687: p_plan_tp_created_flag => p_plan_tp_created_flag
3688: );
3689:
3690: if l_pt_le_status = 'WRONG-DATE-PROG-LE' then
3691: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3692: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3693: hr_multi_message.add;
3694: elsif l_pt_le_status = 'MANY-PROG-LE' then
3695: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3688: );
3689:
3690: if l_pt_le_status = 'WRONG-DATE-PROG-LE' then
3691: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3692: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3693: hr_multi_message.add;
3694: elsif l_pt_le_status = 'MANY-PROG-LE' then
3695: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3696: hr_multi_message.add;
3691: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3692: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3693: hr_multi_message.add;
3694: elsif l_pt_le_status = 'MANY-PROG-LE' then
3695: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3696: hr_multi_message.add;
3697: elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3698: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3699: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3694: elsif l_pt_le_status = 'MANY-PROG-LE' then
3695: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3696: hr_multi_message.add;
3697: elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3698: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3699: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3700: hr_multi_message.add;
3701: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3702: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3695: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3696: hr_multi_message.add;
3697: elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3698: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3699: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3700: hr_multi_message.add;
3701: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3702: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3703: hr_multi_message.add;
3698: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3699: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3700: hr_multi_message.add;
3701: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3702: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3703: hr_multi_message.add;
3704: elsif l_pt_le_status = 'WRONG-DATE-PT' then
3705: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3706: hr_multi_message.add;
3701: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3702: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3703: hr_multi_message.add;
3704: elsif l_pt_le_status = 'WRONG-DATE-PT' then
3705: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3706: hr_multi_message.add;
3707: elsif l_pt_le_status = 'MANY-PT' then
3708: hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3709: hr_multi_message.add;
3704: elsif l_pt_le_status = 'WRONG-DATE-PT' then
3705: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3706: hr_multi_message.add;
3707: elsif l_pt_le_status = 'MANY-PT' then
3708: hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3709: hr_multi_message.add;
3710: elsif l_pt_le_status = 'PROG-LE-ERR' or l_pt_le_status = 'SYNC-LE-ERR' or l_pt_le_status = 'PT-ERR' then
3711: hr_utility.set_message(8302,'PQH_GSP_PT_LE_ERR');
3712: hr_multi_message.add;
3707: elsif l_pt_le_status = 'MANY-PT' then
3708: hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3709: hr_multi_message.add;
3710: elsif l_pt_le_status = 'PROG-LE-ERR' or l_pt_le_status = 'SYNC-LE-ERR' or l_pt_le_status = 'PT-ERR' then
3711: hr_utility.set_message(8302,'PQH_GSP_PT_LE_ERR');
3712: hr_multi_message.add;
3713: else
3714: null;
3715: end if;
3721: p_effective_date => p_effective_date);
3722: --
3723: --
3724: if g_debug then
3725: hr_utility.set_location('Check FR PS Corps Review Submit Validatsons...', 87);
3726: end if;
3727:
3728: -- Adde a call for FR Corps.
3729: pqh_corps_utility.review_submit_valid_corps(p_copy_entity_txn_id => p_copy_entity_txn_id
3736: hr_multi_message.end_validation_set;
3737:
3738: p_status := l_status;
3739: if g_debug then
3740: hr_utility.set_location('Leaving :' ||l_proc, 200);
3741: end if;
3742: Exception
3743:
3744: when hr_multi_message.error_message_exist then
3743:
3744: when hr_multi_message.error_message_exist then
3745: p_status := null;
3746: if g_debug then
3747: hr_utility.set_location('Error :' ||substr(sqlerrm,1,50), 240);
3748: hr_utility.set_location('Leaving:' || l_proc, 300);
3749: end if;
3750: when others then
3751: p_status := null;
3744: when hr_multi_message.error_message_exist then
3745: p_status := null;
3746: if g_debug then
3747: hr_utility.set_location('Error :' ||substr(sqlerrm,1,50), 240);
3748: hr_utility.set_location('Leaving:' || l_proc, 300);
3749: end if;
3750: when others then
3751: p_status := null;
3752:
3784: return l_get_which_rates;
3785: EXCEPTION
3786: WHEN others THEN
3787: if g_debug then
3788: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3789: end if;
3790: return l_get_which_rates;
3791: end get_which_rates;
3792:
3804: and gs_parent_entity_result_id = p_copy_entity_result_id);
3805: l_proc varchar2(72) := g_package||'.get_rates_icon_enabled';
3806: begin
3807: if g_debug then
3808: hr_utility.set_location('Entering '||l_proc, 5);
3809: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3810: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3811: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3812: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3805: l_proc varchar2(72) := g_package||'.get_rates_icon_enabled';
3806: begin
3807: if g_debug then
3808: hr_utility.set_location('Entering '||l_proc, 5);
3809: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3810: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3811: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3812: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3813: end if;
3806: begin
3807: if g_debug then
3808: hr_utility.set_location('Entering '||l_proc, 5);
3809: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3810: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3811: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3812: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3813: end if;
3814:
3807: if g_debug then
3808: hr_utility.set_location('Entering '||l_proc, 5);
3809: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3810: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3811: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3812: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3813: end if;
3814:
3815: if (p_rate_hgrid_node = 'PGM') then
3808: hr_utility.set_location('Entering '||l_proc, 5);
3809: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3810: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3811: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3812: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3813: end if;
3814:
3815: if (p_rate_hgrid_node = 'PGM') then
3816: if (l_get_which_rates = 'POINT') then
3843: l_icon := 'NONE';
3844: end if;
3845:
3846: if g_debug then
3847: hr_utility.set_location('Successfull completion '||l_proc, 5);
3848: end if;
3849: return l_icon;
3850:
3851: EXCEPTION WHEN others THEN
3849: return l_icon;
3850:
3851: EXCEPTION WHEN others THEN
3852: if g_debug then
3853: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3854: end if;
3855: end get_rates_icon_enabled;
3856: --
3857: -- Function to return the annualization factor for frequency codes used in Benefits.
3912: --
3913: Begin
3914: --
3915: if g_debug then
3916: hr_utility.set_location('Entering :'||l_proc, 5);
3917: end if;
3918:
3919: hr_rate_api.create_rate
3920: (p_validate => false
3931: p_rate_id := l_rate_id;
3932: p_ovn := l_ovn;
3933: --
3934: if g_debug then
3935: hr_utility.set_location('Leaving :'||l_proc, 5);
3936: end if;
3937: --
3938: End;
3939: --
3953: --
3954: Begin
3955: --
3956: if g_debug then
3957: hr_utility.set_location('Entering :'||l_proc, 5);
3958: end if;
3959:
3960: hr_rate_api.create_rate
3961: (p_validate => false
3971: p_rate_id := l_rate_id;
3972: p_ovn := l_ovn;
3973: --
3974: if g_debug then
3975: hr_utility.set_location('Leaving :'||l_proc, 5);
3976: end if;
3977: --
3978: End;
3979: --
4066: begin
4067: select action_date into l_effective_date from pqh_copy_entity_txns
4068: where copy_entity_txn_id = p_copy_entity_txn_id;
4069: if g_debug then
4070: hr_utility.set_location('Entering '||l_proc,5);
4071: end if;
4072: if ((p_rate_hgrid_node = 'CPP') and (l_get_which_rates = 'GRADE')) then
4073: l_crrate_icon := pqh_gsp_hr_to_stage.is_crrate_there(p_plip_cer_id => p_copy_entity_result_id,
4074: p_copy_entity_txn_id => p_copy_entity_txn_id,
4081: l_crrate_icon := 'XYZ';
4082: end if;
4083:
4084: if g_debug then
4085: hr_utility.set_location('Successfull Completion '||l_proc,5);
4086: end if;
4087: return l_crrate_icon;
4088:
4089: EXCEPTION WHEN others THEN
4087: return l_crrate_icon;
4088:
4089: EXCEPTION WHEN others THEN
4090: if g_debug then
4091: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
4092: end if;
4093: raise;
4094: end;
4095:
4138:
4139: Begin
4140:
4141: if g_debug then
4142: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4143: hr_utility.set_location(' Parameters passed are as follows: ',6);
4144: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4139: Begin
4140:
4141: if g_debug then
4142: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4143: hr_utility.set_location(' Parameters passed are as follows: ',6);
4144: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4147: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4140:
4141: if g_debug then
4142: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4143: hr_utility.set_location(' Parameters passed are as follows: ',6);
4144: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4147: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4148: end if;
4141: if g_debug then
4142: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4143: hr_utility.set_location(' Parameters passed are as follows: ',6);
4144: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4147: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4148: end if;
4149:
4142: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4143: hr_utility.set_location(' Parameters passed are as follows: ',6);
4144: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4147: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4148: end if;
4149:
4150: OPEN C_Pgm_rt_Perd;
4143: hr_utility.set_location(' Parameters passed are as follows: ',6);
4144: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4147: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4148: end if;
4149:
4150: OPEN C_Pgm_rt_Perd;
4151: FETCH C_Pgm_rt_Perd into l_ref_perd_cd,l_precision, l_Update_Salary_cd;
4179: OR (l_pay_basis = 'HOURLY' AND l_ref_perd_cd = 'PHR')
4180: OR (l_pay_basis = 'ANNUAL' AND l_ref_perd_cd = 'PYR') THEN
4181:
4182: if g_debug then
4183: hr_utility.set_location('Salary basis frequency and Grade Ladder frequency matches', 15);
4184: hr_utility.set_location('So returning the Grade/Step rate as it is', 25);
4185: end if;
4186:
4187: -- RETURN trunc(p_amount,l_precision); -- Bug 6608606
4180: OR (l_pay_basis = 'ANNUAL' AND l_ref_perd_cd = 'PYR') THEN
4181:
4182: if g_debug then
4183: hr_utility.set_location('Salary basis frequency and Grade Ladder frequency matches', 15);
4184: hr_utility.set_location('So returning the Grade/Step rate as it is', 25);
4185: end if;
4186:
4187: -- RETURN trunc(p_amount,l_precision); -- Bug 6608606
4188: RETURN p_amount;
4263:
4264: step_cnt number;
4265: begin
4266: if g_debug then
4267: hr_utility.set_location('Entering '||l_proc, 5);
4268: end if;
4269: for steps in csr_steps_in_grade loop
4270: step_cnt := steps.cnt;
4271: end loop;
4269: for steps in csr_steps_in_grade loop
4270: step_cnt := steps.cnt;
4271: end loop;
4272: if g_debug then
4273: hr_utility.set_location('Successfull completion '||l_proc, 5);
4274: end if;
4275: return step_cnt;
4276: EXCEPTION WHEN others THEN
4277: if g_debug then
4274: end if;
4275: return step_cnt;
4276: EXCEPTION WHEN others THEN
4277: if g_debug then
4278: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
4279: end if;
4280: end get_num_steps_in_grade;
4281:
4282: Function get_dflt_point_rate (p_copy_entity_txn_id in number,
4301: --
4302: Begin
4303: --
4304: if g_debug then
4305: hr_utility.set_location('Entering :'||l_proc, 5);
4306: end if;
4307: --
4308: l_point_rate := 0;
4309:
4311: Fetch csr_dflt_point_rate into l_point_rate;
4312: Close csr_dflt_point_rate;
4313: --
4314: if g_debug then
4315: hr_utility.set_location('Leaving :'||l_proc, 5);
4316: end if;
4317: --
4318: Return l_point_rate;
4319: --
4394: Begin
4395: --
4396: p_exists_flag := 'N';
4397: if g_debug then
4398: hr_utility.set_location('Entering :'||l_proc, 5);
4399: end if;
4400: --
4401: Open csr_sal_basis_iv;
4402: Fetch csr_sal_basis_iv into l_dummy ;
4405: End if;
4406: Close csr_sal_basis_iv;
4407: --
4408: if g_debug then
4409: hr_utility.set_location('Leaving :'||l_proc, 5);
4410: end if;
4411: --
4412: End;
4413:
4474: --
4475: hr_multi_message.enable_message_list;
4476:
4477: if g_debug then
4478: hr_utility.set_location('Entering '||l_proc,100);
4479: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4480: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4481: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4482: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4475: hr_multi_message.enable_message_list;
4476:
4477: if g_debug then
4478: hr_utility.set_location('Entering '||l_proc,100);
4479: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4480: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4481: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4482: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4483: end if;
4476:
4477: if g_debug then
4478: hr_utility.set_location('Entering '||l_proc,100);
4479: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4480: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4481: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4482: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4483: end if;
4484:
4477: if g_debug then
4478: hr_utility.set_location('Entering '||l_proc,100);
4479: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4480: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4481: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4482: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4483: end if;
4484:
4485: pqh_gsp_stage_to_hr.gsp_data_push(p_copy_entity_txn_id => p_copy_entity_txn_id,
4478: hr_utility.set_location('Entering '||l_proc,100);
4479: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4480: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4481: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4482: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4483: end if;
4484:
4485: pqh_gsp_stage_to_hr.gsp_data_push(p_copy_entity_txn_id => p_copy_entity_txn_id,
4486: p_effective_date => p_effective_date,
4487: p_business_group_id => p_business_group_id,
4488: p_business_area => p_business_area,
4489: p_datetrack_mode => p_datetrack_mode);
4490: if g_debug then
4491: hr_utility.set_location('Leaving '||l_proc,100);
4492: end if;
4493:
4494: --
4495: --
4604: ,p_segment30 =>segments.segment30
4605: ,p_ccid => l_ccid
4606: ,p_concat_segments_out => p_concatenated_segments
4607: );
4608: hr_utility.set_location('cccid:'||l_ccid, 5);
4609: hr_utility.set_location('concatened segments:'||p_concatenated_segments,15);
4610: END GET_GRADE_NAME;
4611: ---
4612: --------- get_dml_operation -----------
4605: ,p_ccid => l_ccid
4606: ,p_concat_segments_out => p_concatenated_segments
4607: );
4608: hr_utility.set_location('cccid:'||l_ccid, 5);
4609: hr_utility.set_location('concatened segments:'||p_concatenated_segments,15);
4610: END GET_GRADE_NAME;
4611: ---
4612: --------- get_dml_operation -----------
4613: --
4655: -- if grade_id is enterd and grade_ladder is not entered,
4656: -- need to check gsp is implemented in the system.
4657: -- if the gsp is implemented, a warning message will be appear.
4658: --
4659: hr_utility.set_location('Entering chk_no_asg_grd_ldr', 5);
4660: --
4661: if p_asg_grade_id is not null and p_asg_grade_ladder_id is null then
4662: open csr_chk_gsp_in_system;
4663: fetch csr_chk_gsp_in_system into l_exists;
4669: pqh_utility.set_message(800,'HR_289559_GRADE_LADDER_REQUIRE',p_asg_org_id);
4670: pqh_utility.raise_error;
4671: else
4672: --
4673: hr_utility.set_location('GSP not Implemented', 7);
4674: --
4675: close csr_chk_gsp_in_system;
4676: end if;
4677:
4677:
4678: end if;
4679: --
4680: --
4681: hr_utility.set_location('Leaving chk_no_asg_grd_ldr', 10);
4682: --
4683: End chk_no_asg_grd_ldr;
4684:
4685: FUNCTION bus_area_pgm_entity_exist(p_bus_area_cd IN Varchar2,
4724: AND effective_end_date
4725: AND grade_id = p_grade_id;
4726: BEGIN
4727: hr_multi_message.enable_message_list;
4728: hr_utility.set_location ('Inside chk_new_ceiling', 10);
4729: OPEN csr_grade_id;
4730: FETCH csr_grade_id INTO l_grade_id;
4731: CLOSE csr_grade_id;
4732: hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4728: hr_utility.set_location ('Inside chk_new_ceiling', 10);
4729: OPEN csr_grade_id;
4730: FETCH csr_grade_id INTO l_grade_id;
4731: CLOSE csr_grade_id;
4732: hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4733:
4734: IF l_grade_id IS NOT NULL
4735: THEN
4736: hr_utility.set_location ('grade_id found ', 20);
4732: hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4733:
4734: IF l_grade_id IS NOT NULL
4735: THEN
4736: hr_utility.set_location ('grade_id found ', 20);
4737: OPEN csr_gspine_id (l_grade_id);
4738: FETCH csr_gspine_id INTO l_gspine_id;
4739:
4740: IF csr_gspine_id%FOUND
4738: FETCH csr_gspine_id INTO l_gspine_id;
4739:
4740: IF csr_gspine_id%FOUND
4741: THEN
4742: hr_utility.set_location ('gspine_id found ' || l_gspine_id, 20);
4743: per_grade_spines_pkg.chk_low_ceiling (
4744: p_val_start => p_effective_date,
4745: p_val_end => p_effective_date,
4746: p_gspine_id => l_gspine_id,
4751:
4752: -- Call to raise any errors on multi-message list
4753: hr_multi_message.end_validation_set;
4754: -- p_error_msg := l_msg;
4755: hr_utility.set_location ('Leaving chk_new_ceiling', 90);
4756: EXCEPTION
4757: WHEN OTHERS
4758: THEN
4759: fnd_msg_pub.add;
4788:
4789: l_steps_for_point NUMBER;
4790: BEGIN
4791: hr_multi_message.enable_message_list;
4792: hr_utility.set_location ('Inside Proc', 2);
4793: OPEN csr_steps_for_point;
4794: FETCH csr_steps_for_point INTO l_steps_for_point;
4795:
4796: IF (csr_steps_for_point%FOUND)
4794: FETCH csr_steps_for_point INTO l_steps_for_point;
4795:
4796: IF (csr_steps_for_point%FOUND)
4797: THEN
4798: hr_utility.set_location ('Steps Error', 5);
4799: hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4800: hr_utility.raise_error;
4801: END IF;
4802:
4795:
4796: IF (csr_steps_for_point%FOUND)
4797: THEN
4798: hr_utility.set_location ('Steps Error', 5);
4799: hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4800: hr_utility.raise_error;
4801: END IF;
4802:
4803: hr_multi_message.end_validation_set;
4796: IF (csr_steps_for_point%FOUND)
4797: THEN
4798: hr_utility.set_location ('Steps Error', 5);
4799: hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4800: hr_utility.raise_error;
4801: END IF;
4802:
4803: hr_multi_message.end_validation_set;
4804: hr_utility.set_location ('Leaving Proc', 2);
4800: hr_utility.raise_error;
4801: END IF;
4802:
4803: hr_multi_message.end_validation_set;
4804: hr_utility.set_location ('Leaving Proc', 2);
4805: EXCEPTION
4806: WHEN OTHERS
4807: THEN
4808: hr_utility.set_location ('Some errors', 20);
4804: hr_utility.set_location ('Leaving Proc', 2);
4805: EXCEPTION
4806: WHEN OTHERS
4807: THEN
4808: hr_utility.set_location ('Some errors', 20);
4809: fnd_msg_pub.ADD;
4810: END chk_delete_option;
4811:
4812:
4850: l_final_step_id NUMBER;
4851: l_gspine_id NUMBER;
4852: BEGIN
4853: hr_multi_message.enable_message_list;
4854: hr_utility.set_location ('Inside Proc..', 5);
4855:
4856: if p_initial_ceiling_id <> p_final_ceiling_id THEN
4857: OPEN csr_final_step;
4858: FETCH csr_final_step INTO l_final_step;
4859:
4860: IF csr_final_step%FOUND
4861: THEN
4862: l_gspine_id := l_final_step.information255;
4863: hr_utility.set_location (
4864: 'Going to check for New Ceiling with the following..',
4865: 15
4866: );
4867: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4863: hr_utility.set_location (
4864: 'Going to check for New Ceiling with the following..',
4865: 15
4866: );
4867: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4868: hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4869: hr_utility.set_location (
4870: 'gSpine id' || l_gspine_id,
4871: 35
4864: 'Going to check for New Ceiling with the following..',
4865: 15
4866: );
4867: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4868: hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4869: hr_utility.set_location (
4870: 'gSpine id' || l_gspine_id,
4871: 35
4872: );
4865: 15
4866: );
4867: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4868: hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4869: hr_utility.set_location (
4870: 'gSpine id' || l_gspine_id,
4871: 35
4872: );
4873: hr_utility.set_location (
4869: hr_utility.set_location (
4870: 'gSpine id' || l_gspine_id,
4871: 35
4872: );
4873: hr_utility.set_location (
4874: 'New Ceiling Sequence' || l_final_step.information263,
4875: 45
4876: );
4877: if l_gspine_id is not null THEN
5026: p_effective_date => l_effdate);
5027:
5028: fetch c1 into l_exists;
5029: IF c1%found THEN
5030: hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
5031: close c1;
5032: hr_utility.raise_error;
5033: END IF;
5034:
5028: fetch c1 into l_exists;
5029: IF c1%found THEN
5030: hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
5031: close c1;
5032: hr_utility.raise_error;
5033: END IF;
5034:
5035:
5036: /* per_grade_spines_pkg.stb_del_validation (
5110: l_action_date date;
5111: cnt number := 0;
5112: begin
5113: if g_debug then
5114: hr_utility.set_location('Entering '||l_proc,5);
5115: end if;
5116:
5117: open csr_action_dt;
5118: fetch csr_action_dt into l_action_date;
5119: close csr_action_dt;
5120:
5121: if ((p_rate_hgrid_node = 'CPP') and (l_get_which_rates = 'GRADE')) then
5122: if g_debug then
5123: hr_utility.set_location('These are grade rates ',5);
5124: end if;
5125: for each_pnt in grd_rates(l_action_date) loop
5126: dflt_sal_rate := each_pnt.information297;
5127: end loop;
5126: dflt_sal_rate := each_pnt.information297;
5127: end loop;
5128: elsif ((p_rate_hgrid_node = 'COP') and (l_get_which_rates = 'POINT')) then
5129: if g_debug then
5130: hr_utility.set_location('These are point rates ',5);
5131: end if;
5132: for each_pnt_cer_id in point_cer loop
5133: l_point_cer_id := each_pnt_cer_id.information262;
5134: end loop;
5138: end loop;
5139:
5140: elsif ((p_rate_hgrid_node = 'COP') and (l_get_which_rates = 'STEP')) then
5141: if g_debug then
5142: hr_utility.set_location('These are step rates '||l_proc,5);
5143: end if;
5144: for each_pnt_cer_id in point_cer loop
5145: l_point_cer_id := each_pnt_cer_id.information262;
5146: end loop;
5149: dflt_sal_rate := each_pnt.information297;
5150: end loop;
5151: else
5152: if g_debug then
5153: hr_utility.set_location('This node is not valid for dlft salary determination
5154: '||l_proc,5);
5155: end if;
5156:
5157: dflt_sal_rate := null;
5157: dflt_sal_rate := null;
5158:
5159: end if;
5160: if g_debug then
5161: hr_utility.set_location('Successfull Completion '||l_proc,5);
5162: end if;
5163: return dflt_sal_rate;
5164:
5165: EXCEPTION WHEN others THEN
5163: return dflt_sal_rate;
5164:
5165: EXCEPTION WHEN others THEN
5166: if g_debug then
5167: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
5168: end if;
5169: end get_dflt_salary_rate;
5170:
5171: ---------------------------------------------------------------------------
5226: from ben_pgm_f
5227: where pgm_id = p_pgm_id;
5228: begin
5229: if g_debug then
5230: hr_utility.set_location('Entering chk_from_steps : ',10);
5231: end if;
5232: for rec_grade_id in csr_grade_id
5233: loop
5234: if g_debug then
5231: end if;
5232: for rec_grade_id in csr_grade_id
5233: loop
5234: if g_debug then
5235: hr_utility.set_location('Fetched the Grade Id : '||rec_grade_id.grade_id,20);
5236: end if;
5237: for rec_plan_id in csr_plan_id(rec_grade_id.grade_id)
5238: loop
5239: if g_debug then
5236: end if;
5237: for rec_plan_id in csr_plan_id(rec_grade_id.grade_id)
5238: loop
5239: if g_debug then
5240: hr_utility.set_location('Fetched the Plan Id : '||rec_plan_id.pl_id,30);
5241: end if;
5242: for rec_pgm_id in csr_pgm_id(rec_plan_id.pl_id)
5243: loop
5244: if g_debug then
5241: end if;
5242: for rec_pgm_id in csr_pgm_id(rec_plan_id.pl_id)
5243: loop
5244: if g_debug then
5245: hr_utility.set_location('Fetched the Pgm Id : '||rec_pgm_id.pgm_id,40);
5246: end if;
5247: for rec_use_points IN csr_use_points(rec_pgm_id.pgm_id)
5248: loop
5249: if g_debug then
5246: end if;
5247: for rec_use_points IN csr_use_points(rec_pgm_id.pgm_id)
5248: loop
5249: if g_debug then
5250: hr_utility.set_location('Fetched the Flag : '||rec_use_points.use_prog_points_flag,50);
5251: end if;
5252: if rec_use_points.use_prog_points_flag = 'N' THEN
5253: l_status := 'Y';
5254: if g_debug then
5251: end if;
5252: if rec_use_points.use_prog_points_flag = 'N' THEN
5253: l_status := 'Y';
5254: if g_debug then
5255: hr_utility.set_location('Changing the Status to : '||l_status,60);
5256: end if;
5257: END IF;
5258: end loop;
5259: end loop;
5258: end loop;
5259: end loop;
5260: end loop;
5261: end loop;
5262: hr_utility.set_location('Returning the Status as : '||l_status,90);
5263: Return l_status;
5264: Exception when others then
5265: hr_utility.set_location('Error and returning : '||l_status,90);
5266: return 'N';
5261: end loop;
5262: hr_utility.set_location('Returning the Status as : '||l_status,90);
5263: Return l_status;
5264: Exception when others then
5265: hr_utility.set_location('Error and returning : '||l_status,90);
5266: return 'N';
5267: END chk_from_steps;
5268:
5269: function check_crset(p_crset_type in VARCHAR2,p_crset_id IN NUMBER,p_copy_entity_txn_id IN NUMBER,p_scale_cer_id in number)
5363: ELSE
5364: OPEN csr_plan_dtls;
5365: Fetch csr_plan_dtls into l_plan_dtls;
5366: IF csr_plan_dtls%NOTFOUND THEN
5367: hr_utility.set_location('No Plan avlbl. Exiting..',80);
5368: return;
5369: else
5370: hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5371: OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5366: IF csr_plan_dtls%NOTFOUND THEN
5367: hr_utility.set_location('No Plan avlbl. Exiting..',80);
5368: return;
5369: else
5370: hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5371: OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5372: fetch csr_plip_dtls into l_plip_dtls;
5373: IF csr_plip_dtls%NOTFOUND THEN
5374: hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5370: hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5371: OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5372: fetch csr_plip_dtls into l_plip_dtls;
5373: IF csr_plip_dtls%NOTFOUND THEN
5374: hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5375: return;
5376: else
5377: hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5378: OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5373: IF csr_plip_dtls%NOTFOUND THEN
5374: hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5375: return;
5376: else
5377: hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5378: OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5379: fetch csr_scale_dtls into l_scale_dtls;
5380: IF csr_scale_dtls%NOTFOUND THEN
5381: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5377: hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5378: OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5379: fetch csr_scale_dtls into l_scale_dtls;
5380: IF csr_scale_dtls%NOTFOUND THEN
5381: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5382: return;
5383: Else
5384: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5385: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5380: IF csr_scale_dtls%NOTFOUND THEN
5381: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5382: return;
5383: Else
5384: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5385: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5386: if p_short_name = l_scale_dtls.Information98 then
5387: hr_utility.set_location('The names match ..Exiting..',95);
5388: return;
5381: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5382: return;
5383: Else
5384: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5385: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5386: if p_short_name = l_scale_dtls.Information98 then
5387: hr_utility.set_location('The names match ..Exiting..',95);
5388: return;
5389: else
5383: Else
5384: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5385: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5386: if p_short_name = l_scale_dtls.Information98 then
5387: hr_utility.set_location('The names match ..Exiting..',95);
5388: return;
5389: else
5390: hr_utility.set_location('Got some work to do..',55);
5391:
5386: if p_short_name = l_scale_dtls.Information98 then
5387: hr_utility.set_location('The names match ..Exiting..',95);
5388: return;
5389: else
5390: hr_utility.set_location('Got some work to do..',55);
5391:
5392: update ben_copy_entity_results
5393: set information98 = p_short_name
5394: , dml_operation = get_dml_operation(l_scale_dtls.dml_operation)
5469: and copy_entity_txn_id = p_copy_entity_txn_id;
5470: l_plan_old_date date;
5471: begin
5472: if g_debug then
5473: hr_utility.set_location('Entering change_rates_date',10);
5474: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5475: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5476: hr_utility.set_location('Start Date:'||p_start_date,25);
5477: end if;
5470: l_plan_old_date date;
5471: begin
5472: if g_debug then
5473: hr_utility.set_location('Entering change_rates_date',10);
5474: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5475: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5476: hr_utility.set_location('Start Date:'||p_start_date,25);
5477: end if;
5478: Open csr_plan_dtls;
5471: begin
5472: if g_debug then
5473: hr_utility.set_location('Entering change_rates_date',10);
5474: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5475: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5476: hr_utility.set_location('Start Date:'||p_start_date,25);
5477: end if;
5478: Open csr_plan_dtls;
5479: fetch csr_plan_dtls into l_plan_old_date;
5472: if g_debug then
5473: hr_utility.set_location('Entering change_rates_date',10);
5474: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5475: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5476: hr_utility.set_location('Start Date:'||p_start_date,25);
5477: end if;
5478: Open csr_plan_dtls;
5479: fetch csr_plan_dtls into l_plan_old_date;
5480: IF csr_plan_dtls%NOTFOUND THEN
5478: Open csr_plan_dtls;
5479: fetch csr_plan_dtls into l_plan_old_date;
5480: IF csr_plan_dtls%NOTFOUND THEN
5481: if g_debug then
5482: hr_utility.set_location('No Plan records exist',85);
5483: hr_utility.set_location('Leaving change_rates_date',90);
5484: end if;
5485: return;
5486: end if;
5479: fetch csr_plan_dtls into l_plan_old_date;
5480: IF csr_plan_dtls%NOTFOUND THEN
5481: if g_debug then
5482: hr_utility.set_location('No Plan records exist',85);
5483: hr_utility.set_location('Leaving change_rates_date',90);
5484: end if;
5485: return;
5486: end if;
5487:
5487:
5488: close csr_plan_dtls;
5489: if l_plan_old_date = p_start_date THEN
5490: if g_debug then
5491: hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5492: hr_utility.set_location('No date Change needed',85);
5493: hr_utility.set_location('Leaving change_rates_date',90);
5494: end if;
5495: return;
5488: close csr_plan_dtls;
5489: if l_plan_old_date = p_start_date THEN
5490: if g_debug then
5491: hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5492: hr_utility.set_location('No date Change needed',85);
5493: hr_utility.set_location('Leaving change_rates_date',90);
5494: end if;
5495: return;
5496: end if;
5489: if l_plan_old_date = p_start_date THEN
5490: if g_debug then
5491: hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5492: hr_utility.set_location('No date Change needed',85);
5493: hr_utility.set_location('Leaving change_rates_date',90);
5494: end if;
5495: return;
5496: end if;
5497: hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5493: hr_utility.set_location('Leaving change_rates_date',90);
5494: end if;
5495: return;
5496: end if;
5497: hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5498:
5499: for i in csr_rate_details(l_plan_old_date) loop
5500: if g_debug then
5501: hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5497: hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5498:
5499: for i in csr_rate_details(l_plan_old_date) loop
5500: if g_debug then
5501: hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5502: end if;
5503:
5504: if i.information3 < p_start_date then
5505: hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5501: hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5502: end if;
5503:
5504: if i.information3 < p_start_date then
5505: hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5506: hr_utility.raise_error;
5507: end if;
5508:
5509: update ben_copy_entity_results
5502: end if;
5503:
5504: if i.information3 < p_start_date then
5505: hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5506: hr_utility.raise_error;
5507: end if;
5508:
5509: update ben_copy_entity_results
5510: set information2 = p_start_date
5509: update ben_copy_entity_results
5510: set information2 = p_start_date
5511: where copy_entity_result_id = i.copy_entity_result_id;
5512: if g_debug then
5513: hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);
5514: end if;
5515: end loop;
5516: if g_debug then
5517: hr_utility.set_location('Leaving change_rates_date',80);
5513: hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);
5514: end if;
5515: end loop;
5516: if g_debug then
5517: hr_utility.set_location('Leaving change_rates_date',80);
5518: end if;
5519:
5520: END change_rates_date;
5521:
5540:
5541: BEGIN
5542:
5543: if g_debug then
5544: hr_utility.set_location('Leaving '||l_proc,10);
5545: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
5546: end if;
5547:
5548: Open csr_currency_code;
5541: BEGIN
5542:
5543: if g_debug then
5544: hr_utility.set_location('Leaving '||l_proc,10);
5545: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
5546: end if;
5547:
5548: Open csr_currency_code;
5549: Fetch csr_currency_code into l_result;
5549: Fetch csr_currency_code into l_result;
5550: Close csr_currency_code;
5551:
5552: if g_debug then
5553: hr_utility.set_location('l_result '||l_result,25);
5554: end if;
5555: return l_result;
5556:
5557: if g_debug then
5554: end if;
5555: return l_result;
5556:
5557: if g_debug then
5558: hr_utility.set_location('Leaving '||l_proc,30);
5559: end if;
5560:
5561: EXCEPTION
5562: When others THEN
5718: INTO l_dummy;
5719:
5720: IF csr_name%FOUND
5721: THEN
5722: hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_NAME_UNQ');
5723: hr_multi_message.ADD;
5724: END IF;
5725:
5726: CLOSE csr_name;
5733: INTO l_pgm_name;
5734:
5735: IF csr_dflt_grd_ldr%FOUND
5736: THEN
5737: hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_DFLT_ERR');
5738: hr_utility.set_message_token ('LADDER', l_pgm_name);
5739: hr_multi_message.ADD;
5740: END IF;
5741:
5734:
5735: IF csr_dflt_grd_ldr%FOUND
5736: THEN
5737: hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_DFLT_ERR');
5738: hr_utility.set_message_token ('LADDER', l_pgm_name);
5739: hr_multi_message.ADD;
5740: END IF;
5741:
5742: CLOSE csr_dflt_grd_ldr;
5763: AND information_type = 'PQH_GSP_EXTRA_INFO';
5764: l_proc constant varchar2(72):= g_package||'get_gl_ann_factor';
5765: l_gl_ann_factor ben_pgm_extra_info.pgi_information5%TYPE;
5766: BEGIN
5767: hr_utility.set_location('Entering:'||l_proc, 5);
5768: open csr_gl_ann_factor;
5769: FETCH csr_gl_ann_factor INTO l_gl_ann_factor;
5770: CLOSE csr_gl_ann_factor;
5771: return l_gl_ann_factor;
5768: open csr_gl_ann_factor;
5769: FETCH csr_gl_ann_factor INTO l_gl_ann_factor;
5770: CLOSE csr_gl_ann_factor;
5771: return l_gl_ann_factor;
5772: hr_utility.set_location(' Leaving:'||l_proc, 10);
5773:
5774: Exception
5775: WHEN Others THEN
5776: raise;
5809: -- l_rate_value Number(10) := NULL;
5810: l_rate_value Number(10,2) := NULL; -- Fix for the bug#11934026
5811:
5812: begin
5813: hr_utility.set_location('Entering:'||l_proc, 5);
5814: hr_utility.set_location('P_Entity_id :'||P_Entity_id, 55);
5815: hr_utility.set_location('P_Effective_Date :'||P_Effective_Date , 555);
5816:
5817: Open Step;
5810: l_rate_value Number(10,2) := NULL; -- Fix for the bug#11934026
5811:
5812: begin
5813: hr_utility.set_location('Entering:'||l_proc, 5);
5814: hr_utility.set_location('P_Entity_id :'||P_Entity_id, 55);
5815: hr_utility.set_location('P_Effective_Date :'||P_Effective_Date , 555);
5816:
5817: Open Step;
5818: Fetch Step into l_Spinal_Point_Id;
5811:
5812: begin
5813: hr_utility.set_location('Entering:'||l_proc, 5);
5814: hr_utility.set_location('P_Entity_id :'||P_Entity_id, 55);
5815: hr_utility.set_location('P_Effective_Date :'||P_Effective_Date , 555);
5816:
5817: Open Step;
5818: Fetch Step into l_Spinal_Point_Id;
5819: Close step;
5817: Open Step;
5818: Fetch Step into l_Spinal_Point_Id;
5819: Close step;
5820:
5821: hr_utility.set_location('l_Spinal_Point_Id: '||l_Spinal_Point_Id, 10);
5822:
5823: if l_Spinal_Point_Id is not null then
5824: select value into l_rate_value
5825: from pay_grade_rules_f
5828: and P_Effective_Date
5829: between Effective_Start_Date and Effective_End_Date;
5830: end if;
5831:
5832: hr_utility.set_location('l_rate_valued: '||l_rate_value, 20);
5833: hr_utility.set_location('Leaving '||l_proc,30);
5834: return l_rate_value;
5835:
5836: exception
5829: between Effective_Start_Date and Effective_End_Date;
5830: end if;
5831:
5832: hr_utility.set_location('l_rate_valued: '||l_rate_value, 20);
5833: hr_utility.set_location('Leaving '||l_proc,30);
5834: return l_rate_value;
5835:
5836: exception
5837: when others then
5834: return l_rate_value;
5835:
5836: exception
5837: when others then
5838: hr_utility.set_location('Exception ', 40);
5839: raise;
5840: End Get_current_rate_value;
5841:
5842: --