[Home] [Help]
1: Package body pqh_gsp_utility as
2: /* $Header: pqgsputl.pkb 120.10.12010000.2 2008/08/05 13:35:44 ubhat ship $ */
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: --
99: l_proc varchar2(72) := g_package||'chk_grade_exist_in_gl';
100:
101: BEGIN
102: if g_debug then
103: hr_utility.set_location('Entering '||l_proc,10);
104: hr_utility.set_location('p_copy_entity_txn_id '||l_proc,20);
105: end if;
106:
107: Open csr_grade_in_gl;
100:
101: BEGIN
102: if g_debug then
103: hr_utility.set_location('Entering '||l_proc,10);
104: hr_utility.set_location('p_copy_entity_txn_id '||l_proc,20);
105: end if;
106:
107: Open csr_grade_in_gl;
108: Fetch csr_grade_in_gl into l_cer;
234:
235:
236: BEGIN
237: if g_debug then
238: hr_utility.set_location('Entering '||l_proc,10);
239: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
240: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
241: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
242: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
235:
236: BEGIN
237: if g_debug then
238: hr_utility.set_location('Entering '||l_proc,10);
239: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
240: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
241: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
242: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
243: end if;
236: BEGIN
237: if g_debug then
238: hr_utility.set_location('Entering '||l_proc,10);
239: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
240: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
241: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
242: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
243: end if;
244:
237: if g_debug then
238: hr_utility.set_location('Entering '||l_proc,10);
239: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
240: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
241: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
242: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
243: end if;
244:
245: hr_multi_message.enable_message_list;
238: hr_utility.set_location('Entering '||l_proc,10);
239: hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
240: hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
241: hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
242: hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
243: end if;
244:
245: hr_multi_message.enable_message_list;
246:
249: Fetch csr_pgm_id into l_pgm_id;
250: Close csr_pgm_id;
251:
252: if g_debug then
253: hr_utility.set_location('Pgm_Id '||l_pgm_id,70);
254: end if;
255:
256: -- Get Grade Id
257: Open csr_grade_id;
258: Fetch csr_grade_id into l_grade_id,l_grade_name;
259: Close csr_grade_id;
260:
261: if g_debug then
262: hr_utility.set_location('Grade Id '||l_grade_id,80);
263: hr_utility.set_location('Grade Name '||l_grade_name,81);
264: end if;
265:
266: IF (l_grade_id is NOT NULL AND l_pgm_id IS NOT NULL ) THEN
259: Close csr_grade_id;
260:
261: if g_debug then
262: hr_utility.set_location('Grade Id '||l_grade_id,80);
263: hr_utility.set_location('Grade Name '||l_grade_name,81);
264: end if;
265:
266: IF (l_grade_id is NOT NULL AND l_pgm_id IS NOT NULL ) THEN
267:
270: Open csr_default_grdldr;
271: Fetch csr_default_grdldr into l_dummy;
272: If csr_default_grdldr%Found Then
273: if g_debug then
274: hr_utility.set_location('Grade Ladder is Default Grade Ladder',85);
275: end if;
276: Close csr_default_grdldr;
277:
278: Open csr_grd_assg_emp(l_grade_id);
279: Fetch csr_grd_assg_emp into l_dummy;
280: IF csr_grd_assg_emp%Found Then
281: Close csr_grd_assg_emp;
282: if g_debug then
283: hr_utility.set_location('Employe placements exist on the Default Grade Ladder Grades',87);
284: end if;
285: if g_debug then
286: hr_utility.set_location('Grade Name '||l_grade_name,88);
287: end if;
282: if g_debug then
283: hr_utility.set_location('Employe placements exist on the Default Grade Ladder Grades',87);
284: end if;
285: if g_debug then
286: hr_utility.set_location('Grade Name '||l_grade_name,88);
287: end if;
288: p_rec_exists := l_grade_name;
289: Else
290: p_rec_exists := 'MAIN';
298: Open csr_gl_grade_assg_emp(l_pgm_id, l_grade_id);
299: Fetch csr_gl_grade_assg_emp into l_assg_id;
300: if csr_gl_grade_assg_emp%found then
301: if g_debug then
302: hr_utility.set_location(' Employe placements exist on the Grades',90);
303: end if;
304: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
305: hr_utility.raise_error;
306: end if;
300: if csr_gl_grade_assg_emp%found then
301: if g_debug then
302: hr_utility.set_location(' Employe placements exist on the Grades',90);
303: end if;
304: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
305: hr_utility.raise_error;
306: end if;
307: Close csr_gl_grade_assg_emp;
308:
301: if g_debug then
302: hr_utility.set_location(' Employe placements exist on the Grades',90);
303: end if;
304: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
305: hr_utility.raise_error;
306: end if;
307: Close csr_gl_grade_assg_emp;
308:
309: l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
320: OPEN csr_grade_crpath_node;
321: FETCH csr_grade_crpath_node into l_grade_crpath_node;
322: if csr_grade_crpath_node%found then
323: if g_debug then
324: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
325: end if;
326: hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
327: hr_utility.raise_error;
328: end if;
322: if csr_grade_crpath_node%found then
323: if g_debug then
324: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
325: end if;
326: hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
327: hr_utility.raise_error;
328: end if;
329: CLOSE csr_grade_crpath_node;
330: end if;
323: if g_debug then
324: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
325: end if;
326: hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
327: hr_utility.raise_error;
328: end if;
329: CLOSE csr_grade_crpath_node;
330: end if;
331: end if;
336: ELSE
337: p_rec_exists := 'STAGE';
338: END IF;
339: if g_debug then
340: hr_utility.set_location('p_rec_exists :'||p_rec_exists,99);
341: hr_utility.set_location('Leaving '||l_proc,100);
342: end if;
343: EXCEPTION
344: WHEN others THEN
337: p_rec_exists := 'STAGE';
338: END IF;
339: if g_debug then
340: hr_utility.set_location('p_rec_exists :'||p_rec_exists,99);
341: hr_utility.set_location('Leaving '||l_proc,100);
342: end if;
343: EXCEPTION
344: WHEN others THEN
345: p_rec_exists := 'STAGE';
373: l_proc varchar2(72) := g_package||'enable_disable_start_icon';
374: --
375: BEGIN
376: if g_debug then
377: hr_utility.set_location('Entering '||l_proc,5);
378: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
379: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
380: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
381: end if;
374: --
375: BEGIN
376: if g_debug then
377: hr_utility.set_location('Entering '||l_proc,5);
378: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
379: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
380: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
381: end if;
382:
375: BEGIN
376: if g_debug then
377: hr_utility.set_location('Entering '||l_proc,5);
378: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
379: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
380: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
381: end if;
382:
383: if (p_table_alias is not null) then
376: if g_debug then
377: hr_utility.set_location('Entering '||l_proc,5);
378: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
379: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
380: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
381: end if;
382:
383: if (p_table_alias is not null) then
384: l_table_alias := p_table_alias;
399: st_icon := get_status(prev_task, p_copy_enty_txn_id, l_table_alias);
400: end if;
401:
402: if g_debug then
403: hr_utility.set_location('Leaving Successfully'||l_proc,5);
404: end if;
405:
406: RETURN st_icon;
407: EXCEPTION WHEN others THEN
405:
406: RETURN st_icon;
407: EXCEPTION WHEN others THEN
408: if g_debug then
409: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
410: end if;
411: return 'N';
412: END;
413: --
424: l_proc varchar2(72) := g_package||'get_status';
425: --
426: BEGIN
427: if g_debug then
428: hr_utility.set_location('Entering '||l_proc,5);
429: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
430: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
431: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
432: end if;
425: --
426: BEGIN
427: if g_debug then
428: hr_utility.set_location('Entering '||l_proc,5);
429: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
430: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
431: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
432: end if;
433:
426: BEGIN
427: if g_debug then
428: hr_utility.set_location('Entering '||l_proc,5);
429: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
430: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
431: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
432: end if;
433:
434: if (p_table_alias is not null) then
427: if g_debug then
428: hr_utility.set_location('Entering '||l_proc,5);
429: hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
430: hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
431: hr_utility.set_location('p_table_alias is '||p_table_alias,15);
432: end if;
433:
434: if (p_table_alias is not null) then
435: l_table_alias := p_table_alias;
508: else
509: st_icon := 'N';
510: end if;
511: if g_debug then
512: hr_utility.set_location('Leaving Successfully'||l_proc,5);
513: end if;
514: RETURN st_icon;
515: EXCEPTION
516: WHEN others THEN
514: RETURN st_icon;
515: EXCEPTION
516: WHEN others THEN
517: if g_debug then
518: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
519: end if;
520: return 'N';
521: END;
522:
544:
545: BEGIN
546:
547: if g_debug then
548: hr_utility.set_location('Leaving '||l_proc,10);
549: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
550: end if;
551:
552: Open csr_use_points;
545: BEGIN
546:
547: if g_debug then
548: hr_utility.set_location('Leaving '||l_proc,10);
549: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
550: end if;
551:
552: Open csr_use_points;
553: Fetch csr_use_points into l_result;
553: Fetch csr_use_points into l_result;
554: Close csr_use_points;
555:
556: if g_debug then
557: hr_utility.set_location('l_result '||l_result,25);
558: end if;
559: return l_result;
560:
561: if g_debug then
558: end if;
559: return l_result;
560:
561: if g_debug then
562: hr_utility.set_location('Leaving '||l_proc,30);
563: end if;
564:
565: EXCEPTION
566: When others THEN
653: and information3 = p_step_id;
654:
655: BEGIN
656: if g_debug then
657: hr_utility.set_location('Entering '||l_proc,10);
658: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
659: hr_utility.set_location('p_use_points '||p_use_points,30);
660: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
661: hr_utility.set_location('p_effective_date '||p_effective_date,70);
654:
655: BEGIN
656: if g_debug then
657: hr_utility.set_location('Entering '||l_proc,10);
658: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
659: hr_utility.set_location('p_use_points '||p_use_points,30);
660: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
661: hr_utility.set_location('p_effective_date '||p_effective_date,70);
662: end if;
655: BEGIN
656: if g_debug then
657: hr_utility.set_location('Entering '||l_proc,10);
658: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
659: hr_utility.set_location('p_use_points '||p_use_points,30);
660: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
661: hr_utility.set_location('p_effective_date '||p_effective_date,70);
662: end if;
663:
656: if g_debug then
657: hr_utility.set_location('Entering '||l_proc,10);
658: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
659: hr_utility.set_location('p_use_points '||p_use_points,30);
660: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
661: hr_utility.set_location('p_effective_date '||p_effective_date,70);
662: end if;
663:
664: hr_multi_message.enable_message_list;
657: hr_utility.set_location('Entering '||l_proc,10);
658: hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
659: hr_utility.set_location('p_use_points '||p_use_points,30);
660: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
661: hr_utility.set_location('p_effective_date '||p_effective_date,70);
662: end if;
663:
664: hr_multi_message.enable_message_list;
665:
673: Fetch csr_grade_id into l_grade_id;
674: Close csr_grade_id;
675:
676: if g_debug then
677: hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
678: hr_utility.set_location('Grade Id :'||l_grade_id,110);
679: hr_utility.set_location('Step Id:'||p_step_id ,120);
680: end if;
681:
674: Close csr_grade_id;
675:
676: if g_debug then
677: hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
678: hr_utility.set_location('Grade Id :'||l_grade_id,110);
679: hr_utility.set_location('Step Id:'||p_step_id ,120);
680: end if;
681:
682: IF (l_pgm_id IS NOT NULL AND -- Grade Ladder Id
675:
676: if g_debug then
677: hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
678: hr_utility.set_location('Grade Id :'||l_grade_id,110);
679: hr_utility.set_location('Step Id:'||p_step_id ,120);
680: end if;
681:
682: IF (l_pgm_id IS NOT NULL AND -- Grade Ladder Id
683: l_grade_id IS NOT NULL AND -- Grade Id
683: l_grade_id IS NOT NULL AND -- Grade Id
684: p_step_id IS NOT NULL ) -- Step Id
685: THEN
686: if g_debug then
687: hr_utility.set_location('Rec is in HR/BEN Tables ',150);
688: end if;
689: p_rec_exists := 'MAIN';
690:
691: /* This call raise error if step is
697: p_sess => p_effective_date);
698:
699: -- If the Step is Ceiling Step Then raise Error
700: if g_debug then
701: hr_utility.set_location('Ceiling Step Y/N '||p_celing_step_flag ,180);
702: end if;
703:
704: If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
705: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
701: hr_utility.set_location('Ceiling Step Y/N '||p_celing_step_flag ,180);
702: end if;
703:
704: If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
705: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
706: hr_utility.raise_error;
707: END IF;
708:
709: l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
702: end if;
703:
704: If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
705: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
706: hr_utility.raise_error;
707: END IF;
708:
709: l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
710: if l_business_area = 'PQH_CORPS_TASK_LIST' then
720: OPEN csr_grade_crpath_node;
721: FETCH csr_grade_crpath_node into l_grade_crpath_node;
722: if csr_grade_crpath_node%found then
723: if g_debug then
724: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
725: end if;
726: hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
727: hr_utility.raise_error;
728: end if;
722: if csr_grade_crpath_node%found then
723: if g_debug then
724: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
725: end if;
726: hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
727: hr_utility.raise_error;
728: end if;
729: CLOSE csr_grade_crpath_node;
730: end if;
723: if g_debug then
724: hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
725: end if;
726: hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
727: hr_utility.raise_error;
728: end if;
729: CLOSE csr_grade_crpath_node;
730: end if;
731: end if;
732:
733:
734: ELSE
735: if g_debug then
736: hr_utility.set_location('Data only in Staging Area',350);
737: end if;
738: p_rec_exists := 'STAGE';
739:
740: End IF;
776: l_exists Varchar2(10);
777:
778: BEGIN
779: if g_debug then
780: hr_utility.set_location('Entering '||l_proc,10);
781: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
782: end if;
783:
784: Open csr_profile_count;
777:
778: BEGIN
779: if g_debug then
780: hr_utility.set_location('Entering '||l_proc,10);
781: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
782: end if;
783:
784: Open csr_profile_count;
785: Fetch csr_profile_count into l_cer;
791: Close csr_profile_count;
792: return l_exists;
793:
794: if g_debug then
795: hr_utility.set_location('Leaving'||l_proc,60);
796: end if;
797:
798: EXCEPTION
799: WHEN others THEN
859: l_dummy Char(1);
860: l_proc varchar2(72) := g_package||'display_icon';
861: BEGIN
862: if g_debug then
863: hr_utility.set_location('Entering'||l_proc,10);
864: hr_utility.set_location('p_page'||p_page,20);
865: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
866: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
867: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
860: l_proc varchar2(72) := g_package||'display_icon';
861: BEGIN
862: if g_debug then
863: hr_utility.set_location('Entering'||l_proc,10);
864: hr_utility.set_location('p_page'||p_page,20);
865: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
866: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
867: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
868: end if;
861: BEGIN
862: if g_debug then
863: hr_utility.set_location('Entering'||l_proc,10);
864: hr_utility.set_location('p_page'||p_page,20);
865: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
866: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
867: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
868: end if;
869:
862: if g_debug then
863: hr_utility.set_location('Entering'||l_proc,10);
864: hr_utility.set_location('p_page'||p_page,20);
865: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
866: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
867: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
868: end if;
869:
870: -- For Grade HGrid Page
863: hr_utility.set_location('Entering'||l_proc,10);
864: hr_utility.set_location('p_page'||p_page,20);
865: hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
866: hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
867: hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
868: end if;
869:
870: -- For Grade HGrid Page
871: If p_page = 'GRADE_HGRID' Then
943:
944: If p_page = 'PRG_RULE_HGRID'
945: Then
946: if g_debug then
947: hr_utility.set_location('Prg Rule Hgrid page' ,40);
948: end if;
949:
950: If p_Table_Alias = 'PGM'
951: Then
954: Elsif p_action = 'UPDATE' Then
955: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
956: p_copy_entity_txn_id => p_copy_entity_txn_id);
957: if g_debug then
958: hr_utility.set_location('ELP recs Exists (Y/N) :'|| l_exists,41);
959: end if;
960: if l_exists = 'Y' then
961: return 'E';
962: else
979: if p_action = 'ADD' Then
980: return 'E';
981: elsif p_action = 'UPDATE' Then
982: if g_debug then
983: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,50);
984: end if;
985: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
986: p_copy_entity_txn_id => p_copy_entity_txn_id);
987: if g_debug then
984: end if;
985: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
986: p_copy_entity_txn_id => p_copy_entity_txn_id);
987: if g_debug then
988: hr_utility.set_location('l_exists :: '||l_exists,60);
989: end if;
990:
991: if l_exists = 'Y' then
992: return 'E';
1001: return 'E';
1002: elsif p_action = 'UPDATE'Then
1003:
1004: if g_debug then
1005: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,80);
1006: end if;
1007:
1008: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1009: p_copy_entity_txn_id => p_copy_entity_txn_id);
1007:
1008: l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1009: p_copy_entity_txn_id => p_copy_entity_txn_id);
1010: if g_debug then
1011: hr_utility.set_location('l_exists :: '||l_exists,90);
1012: end if;
1013:
1014: if l_exists = 'Y' then
1015: return 'E';
1026: Else
1027: return 'N';
1028: End if;
1029: if g_debug then
1030: hr_utility.set_location('Update is through' ,50);
1031: end if;
1032:
1033: End If; -- # Progression Rules HGgrid
1034:
1032:
1033: End If; -- # Progression Rules HGgrid
1034:
1035: if g_debug then
1036: hr_utility.set_location('Leaving'||l_proc,300);
1037: end if;
1038: END DISPLAY_ICON;
1039:
1040:
1090: where copy_entity_result_id = l_grd_cer_id;
1091:
1092: BEGIN
1093: if g_debug then
1094: hr_utility.set_location('Entering '||l_proc,10);
1095: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1096: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1097: end if;
1098:
1091:
1092: BEGIN
1093: if g_debug then
1094: hr_utility.set_location('Entering '||l_proc,10);
1095: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1096: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1097: end if;
1098:
1099: Open Csr_Cer_Ids;
1092: BEGIN
1093: if g_debug then
1094: hr_utility.set_location('Entering '||l_proc,10);
1095: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1096: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1097: end if;
1098:
1099: Open Csr_Cer_Ids;
1100: Fetch Csr_Cer_Ids into l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id;
1100: Fetch Csr_Cer_Ids into l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id;
1101: Close Csr_Cer_Ids;
1102:
1103: if g_debug then
1104: hr_utility.set_location('Option Entity Result Id '||l_opt_cer_id,40);
1105: hr_utility.set_location('Pay Scale Entity Result Id '||l_pay_scale_cer_id,50);
1106: end if;
1107:
1108: Open Csr_Step_No(l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id,p_copy_entity_txn_id);
1101: Close Csr_Cer_Ids;
1102:
1103: if g_debug then
1104: hr_utility.set_location('Option Entity Result Id '||l_opt_cer_id,40);
1105: hr_utility.set_location('Pay Scale Entity Result Id '||l_pay_scale_cer_id,50);
1106: end if;
1107:
1108: Open Csr_Step_No(l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id,p_copy_entity_txn_id);
1109: Fetch Csr_Step_No into l_step_no;
1216:
1217:
1218: BEGIN
1219: if g_debug then
1220: hr_utility.set_location('Entering '||l_proc,10);
1221: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1222: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1223: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1224: end if;
1217:
1218: BEGIN
1219: if g_debug then
1220: hr_utility.set_location('Entering '||l_proc,10);
1221: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1222: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1223: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1224: end if;
1225:
1218: BEGIN
1219: if g_debug then
1220: hr_utility.set_location('Entering '||l_proc,10);
1221: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1222: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1223: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1224: end if;
1225:
1226: l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1219: if g_debug then
1220: hr_utility.set_location('Entering '||l_proc,10);
1221: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1222: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1223: hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1224: end if;
1225:
1226: l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1227: if g_debug then
1224: end if;
1225:
1226: l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1227: if g_debug then
1228: hr_utility.set_location('Point/Step : '||l_step_or_point,40);
1229: end if;
1230:
1231: if (p_Table_Alias = 'PGM') then
1232: Open csr_grdldr_name;
1232: Open csr_grdldr_name;
1233: Fetch csr_grdldr_name into l_name;
1234: Close csr_grdldr_name;
1235: if g_debug then
1236: hr_utility.set_location('Grade Ladder Name '||l_name,60);
1237: end if;
1238: return l_name;
1239: elsif (p_Table_Alias = 'CPP') then
1240: Open csr_grd_name;
1240: Open csr_grd_name;
1241: Fetch csr_grd_name into l_name,l_name1;
1242: Close csr_grd_name;
1243: if g_debug then
1244: hr_utility.set_location('Grade Name '||substr(l_name,1,50),80);
1245: hr_utility.set_location('Pay Scale Name '||substr(l_name1,1,50),90);
1246: end if;
1247: if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL )) then
1248: return l_name || ' (' ||l_name1|| ')';
1241: Fetch csr_grd_name into l_name,l_name1;
1242: Close csr_grd_name;
1243: if g_debug then
1244: hr_utility.set_location('Grade Name '||substr(l_name,1,50),80);
1245: hr_utility.set_location('Pay Scale Name '||substr(l_name1,1,50),90);
1246: end if;
1247: if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL )) then
1248: return l_name || ' (' ||l_name1|| ')';
1249: else
1254: Open csr_step_name;
1255: Fetch csr_step_name into l_name,l_name1;
1256: Close csr_step_name;
1257: if g_debug then
1258: hr_utility.set_location('Step Name '||l_name,100);
1259: hr_utility.set_location('Point Name '||l_name1,120);
1260: end if;
1261: l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1262: p_copy_entity_txn_id => p_copy_entity_txn_id);
1255: Fetch csr_step_name into l_name,l_name1;
1256: Close csr_step_name;
1257: if g_debug then
1258: hr_utility.set_location('Step Name '||l_name,100);
1259: hr_utility.set_location('Point Name '||l_name1,120);
1260: end if;
1261: l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1262: p_copy_entity_txn_id => p_copy_entity_txn_id);
1263:
1261: l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1262: p_copy_entity_txn_id => p_copy_entity_txn_id);
1263:
1264: if g_debug then
1265: hr_utility.set_location('Generated Step Name : '||l_step_name,125);
1266: end if;
1267:
1268:
1269: l_bus_area := pqh_corps_utility.get_cet_business_area( p_copy_entity_txn_id => p_copy_entity_txn_id);
1267:
1268:
1269: l_bus_area := pqh_corps_utility.get_cet_business_area( p_copy_entity_txn_id => p_copy_entity_txn_id);
1270: if g_debug then
1271: hr_utility.set_location('Business Area : '||l_bus_area,130);
1272: end if;
1273: -- For Corps Rates HGrid Page need to Display Step Name like Step (Point Name | Gross Index | Increased Gross Index)
1274: If (nvl(l_bus_area,'PQH_GSP_TASK_LIST') = 'PQH_CORPS_TASK_LIST' And p_hgrid = 'RATES_HGRID' ) Then
1275:
1277: Fetch csr_corps_gross_index into l_gross_index;
1278: Close csr_corps_gross_index;
1279:
1280: if g_debug then
1281: hr_utility.set_location('Gross Index '||l_gross_index,140);
1282: end if;
1283: If l_gross_index IS NOT NULL Then
1284: l_increased_index := pqh_corps_utility.get_increased_index(p_gross_index => l_gross_index,
1285: p_copy_entity_txn_id => p_copy_entity_txn_id);
1285: p_copy_entity_txn_id => p_copy_entity_txn_id);
1286: End If;
1287:
1288: if g_debug then
1289: hr_utility.set_location('Increased Index '||l_increased_index,150);
1290: end if;
1291:
1292: if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL)) then
1293: l_corps_step_name := l_step_name || ' (' ||l_name1;
1319: Open csr_elig_name;
1320: Fetch csr_elig_name into l_name;
1321: Close csr_elig_name;
1322: if g_debug then
1323: hr_utility.set_location('Eligibility Profile Name '||l_name,60);
1324: end if;
1325: return l_name;
1326: else
1327: open csr_table_name;
1333: return null;
1334: end if;
1335: end if; -- table_name
1336: if g_debug then
1337: hr_utility.set_location('Leaving '||l_proc,100);
1338: end if;
1339: END GET_STEP_PRG_RULE_HGRID_NAME;
1340:
1341: --
1394: l_proc varchar2(72) := g_package||'CHK_GRD_DETAILS';
1395:
1396: BEGIN
1397: if g_debug then
1398: hr_utility.set_location('Entering '||l_proc,10);
1399: hr_utility.set_location('p_name '||p_name,20);
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1395:
1396: BEGIN
1397: if g_debug then
1398: hr_utility.set_location('Entering '||l_proc,10);
1399: hr_utility.set_location('p_name '||p_name,20);
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1403: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1396: BEGIN
1397: if g_debug then
1398: hr_utility.set_location('Entering '||l_proc,10);
1399: hr_utility.set_location('p_name '||p_name,20);
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1403: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1404: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1397: if g_debug then
1398: hr_utility.set_location('Entering '||l_proc,10);
1399: hr_utility.set_location('p_name '||p_name,20);
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1403: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1404: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1405: end if;
1398: hr_utility.set_location('Entering '||l_proc,10);
1399: hr_utility.set_location('p_name '||p_name,20);
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1403: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1404: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1405: end if;
1406:
1399: hr_utility.set_location('p_name '||p_name,20);
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1403: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1404: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1405: end if;
1406:
1407: hr_multi_message.enable_message_list;
1400: hr_utility.set_location('p_short_name '||p_short_name,30);
1401: hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1402: hr_utility.set_location('p_grade_id '||p_grade_id,50);
1403: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1404: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1405: end if;
1406:
1407: hr_multi_message.enable_message_list;
1408: Open csr_grade_name;
1408: Open csr_grade_name;
1409: Fetch csr_grade_name into l_grade_name;
1410: If(csr_grade_name%FOUND) THEN
1411: p_status := 'E';
1412: hr_utility.set_message(8302,'PER_7830_DEF_GRADE_EXISTS');
1413: hr_utility.raise_error;
1414: Else
1415: p_status := 'Y';
1416: End if;
1409: Fetch csr_grade_name into l_grade_name;
1410: If(csr_grade_name%FOUND) THEN
1411: p_status := 'E';
1412: hr_utility.set_message(8302,'PER_7830_DEF_GRADE_EXISTS');
1413: hr_utility.raise_error;
1414: Else
1415: p_status := 'Y';
1416: End if;
1417:
1418: Open csr_grade_short_name;
1419: Fetch csr_grade_short_name into l_short_name;
1420: If(csr_grade_short_name%FOUND) THEN
1421: p_status := 'E';
1422: hr_utility.set_message(800,'HR_289555_NON_UNIQ_SHORT_NAME');
1423: hr_utility.raise_error;
1424: Else
1425: p_status := 'Y';
1426: End if;
1419: Fetch csr_grade_short_name into l_short_name;
1420: If(csr_grade_short_name%FOUND) THEN
1421: p_status := 'E';
1422: hr_utility.set_message(800,'HR_289555_NON_UNIQ_SHORT_NAME');
1423: hr_utility.raise_error;
1424: Else
1425: p_status := 'Y';
1426: End if;
1427: if g_debug then
1424: Else
1425: p_status := 'Y';
1426: End if;
1427: if g_debug then
1428: hr_utility.set_location('Leaving '||l_proc,100);
1429: end if;
1430: Exception
1431: when others then
1432: p_status := 'E';
1508: l_proc varchar2(72) := g_package||'enddate_grade_ladder';
1509:
1510: begin
1511: if g_debug then
1512: hr_utility.set_location('Entering '||l_proc,5);
1513: hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1514: hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1515: end if;
1516:
1509:
1510: begin
1511: if g_debug then
1512: hr_utility.set_location('Entering '||l_proc,5);
1513: hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1514: hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1515: end if;
1516:
1517: for each_rec in pgm loop
1510: begin
1511: if g_debug then
1512: hr_utility.set_location('Entering '||l_proc,5);
1513: hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1514: hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1515: end if;
1516:
1517: for each_rec in pgm loop
1518: ben_program_api.delete_Program
1528: for sfl_txn in c2 loop
1529: delete_transaction(sfl_txn.copy_entity_txn_id);
1530: end loop;
1531: if g_debug then
1532: hr_utility.set_location('Successfull completion for '||l_proc,5);
1533: end if;
1534: exception when others then
1535: if g_debug then
1536: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in'||l_proc,5);
1532: hr_utility.set_location('Successfull completion for '||l_proc,5);
1533: end if;
1534: exception when others then
1535: if g_debug then
1536: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in'||l_proc,5);
1537: end if;
1538: raise;
1539: end enddate_grade_ladder;
1540:
1756: l_mode varchar2(10) := p_mode;
1757: l_proc varchar2(72) := 'gen_txn_display_name';
1758: begin
1759: if g_debug then
1760: hr_utility.set_location('Entering '||l_proc,5);
1761: hr_utility.set_location('p_program_name is '||p_program_name, 10);
1762: hr_utility.set_location('p_mode is '||p_mode, 15);
1763: end if;
1764:
1757: l_proc varchar2(72) := 'gen_txn_display_name';
1758: begin
1759: if g_debug then
1760: hr_utility.set_location('Entering '||l_proc,5);
1761: hr_utility.set_location('p_program_name is '||p_program_name, 10);
1762: hr_utility.set_location('p_mode is '||p_mode, 15);
1763: end if;
1764:
1765: --
1758: begin
1759: if g_debug then
1760: hr_utility.set_location('Entering '||l_proc,5);
1761: hr_utility.set_location('p_program_name is '||p_program_name, 10);
1762: hr_utility.set_location('p_mode is '||p_mode, 15);
1763: end if;
1764:
1765: --
1766: --If Mode is null then consider it to be a create transaction
1778: l_display_name := l_mode||':'||l_timestamp;
1779: end if;
1780:
1781: if g_debug then
1782: hr_utility.set_location('Leaving '||l_proc,5);
1783: end if;
1784: return l_display_name;
1785:
1786: EXCEPTION WHEN others THEN
1784: return l_display_name;
1785:
1786: EXCEPTION WHEN others THEN
1787: if g_debug then
1788: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1789: end if;
1790: end;
1791:
1792: --
1805:
1806: l_proc varchar2(72) := 'get_grade_ladder_name_from_txn';
1807: begin
1808: if g_debug then
1809: hr_utility.set_location('Entering '||l_proc, 5);
1810: end if;
1811:
1812: for gr_ldr in c1 loop
1813: l_grade_ladder_name := gr_ldr.grade_ladder_name;
1814: end loop;
1815: return l_grade_ladder_name;
1816:
1817: if g_debug then
1818: hr_utility.set_location('Leaving '||l_proc, 5);
1819: end if;
1820:
1821: EXCEPTION WHEN others THEN
1822: if g_debug then
1819: end if;
1820:
1821: EXCEPTION WHEN others THEN
1822: if g_debug then
1823: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1824: end if;
1825: end;
1826:
1827: --
1863: And Dflt_Pgm_Flag = 'Y';
1864: --
1865: Begin
1866: if g_debug then
1867: hr_utility.set_location('ENTERING:'||l_proc, 5);
1868: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1869: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1870: end if;
1871: --
1864: --
1865: Begin
1866: if g_debug then
1867: hr_utility.set_location('ENTERING:'||l_proc, 5);
1868: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1869: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1870: end if;
1871: --
1872: --
1865: Begin
1866: if g_debug then
1867: hr_utility.set_location('ENTERING:'||l_proc, 5);
1868: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1869: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1870: end if;
1871: --
1872: --
1873: If p_pgm_id IS NOT NULL Then
1871: --
1872: --
1873: If p_pgm_id IS NOT NULL Then
1874: if g_debug then
1875: hr_utility.set_location(' Pgm Id Is not Null', 21);
1876: end if;
1877: Open csr_curr_grdldr;
1878: Fetch csr_curr_grdldr into l_name;
1879: If csr_curr_grdldr%Found Then
1877: Open csr_curr_grdldr;
1878: Fetch csr_curr_grdldr into l_name;
1879: If csr_curr_grdldr%Found Then
1880: if g_debug then
1881: hr_utility.set_location('Current Grade Ladder is Default Grade Ladder' , 22);
1882: end if;
1883: Open csr_default_chk;
1884: Fetch csr_default_chk into l_dummy;
1885: If csr_default_chk%found then
1883: Open csr_default_chk;
1884: Fetch csr_default_chk into l_dummy;
1885: If csr_default_chk%found then
1886: close csr_default_chk;
1887: hr_utility.set_message(8302,'PQH_GSP_GRDLDR_DFLT_ERR');
1888: hr_utility.set_message_token('LADDER',l_name);
1889: hr_multi_message.add;
1890: Else
1891: close csr_default_chk;
1884: Fetch csr_default_chk into l_dummy;
1885: If csr_default_chk%found then
1886: close csr_default_chk;
1887: hr_utility.set_message(8302,'PQH_GSP_GRDLDR_DFLT_ERR');
1888: hr_utility.set_message_token('LADDER',l_name);
1889: hr_multi_message.add;
1890: Else
1891: close csr_default_chk;
1892: End if;
1894: Close csr_curr_grdldr;
1895: End If;
1896: --
1897: if g_debug then
1898: hr_utility.set_location('Leaving:'||l_proc, 35);
1899: end if;
1900:
1901: End chk_default_ladder_exist;
1902:
1971:
1972: --
1973: Begin
1974: if g_debug then
1975: hr_utility.set_location('Entering:'||l_proc, 5);
1976: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
1977: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1978: end if;
1979: --
1972: --
1973: Begin
1974: if g_debug then
1975: hr_utility.set_location('Entering:'||l_proc, 5);
1976: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
1977: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1978: end if;
1979: --
1980: -- Check Steps Exists for a Transaction or not
1973: Begin
1974: if g_debug then
1975: hr_utility.set_location('Entering:'||l_proc, 5);
1976: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
1977: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1978: end if;
1979: --
1980: -- Check Steps Exists for a Transaction or not
1981: Open csr_steps_exists;
2006:
2007: l_no_step_grades := substr(l_no_step_grades,1,length(l_no_step_grades)-2);
2008: l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2009: if g_debug then
2010: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2011: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2012: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2013: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2014: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2007: l_no_step_grades := substr(l_no_step_grades,1,length(l_no_step_grades)-2);
2008: l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2009: if g_debug then
2010: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2011: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2012: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2013: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2014: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2015: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2008: l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2009: if g_debug then
2010: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2011: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2012: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2013: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2014: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2015: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2016: end if;
2009: if g_debug then
2010: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2011: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2012: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2013: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2014: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2015: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2016: end if;
2017:
2010: hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2011: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2012: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2013: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2014: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2015: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2016: end if;
2017:
2018: If l_found1 then
2011: hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2012: hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2013: hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2014: hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2015: hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2016: end if;
2017:
2018: If l_found1 then
2019: if g_debug then
2016: end if;
2017:
2018: If l_found1 then
2019: if g_debug then
2020: hr_utility.set_location('Grades not having Steps found',51);
2021: end if;
2022: hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2023: hr_utility.set_message_token('GRADES',l_no_step_grades);
2024:
2018: If l_found1 then
2019: if g_debug then
2020: hr_utility.set_location('Grades not having Steps found',51);
2021: end if;
2022: hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2023: hr_utility.set_message_token('GRADES',l_no_step_grades);
2024:
2025: hr_multi_message.add;
2026:
2019: if g_debug then
2020: hr_utility.set_location('Grades not having Steps found',51);
2021: end if;
2022: hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2023: hr_utility.set_message_token('GRADES',l_no_step_grades);
2024:
2025: hr_multi_message.add;
2026:
2027:
2027:
2028: End If;
2029: If l_found2 then
2030: if g_debug then
2031: hr_utility.set_location('Grades not having Celing Steps found',52);
2032: end if;
2033: hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2034: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2035: hr_multi_message.add;
2029: If l_found2 then
2030: if g_debug then
2031: hr_utility.set_location('Grades not having Celing Steps found',52);
2032: end if;
2033: hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2034: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2035: hr_multi_message.add;
2036: End If;
2037: if g_debug then
2030: if g_debug then
2031: hr_utility.set_location('Grades not having Celing Steps found',52);
2032: end if;
2033: hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2034: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2035: hr_multi_message.add;
2036: End If;
2037: if g_debug then
2038: hr_utility.set_location('Leaving:'||l_proc,100);
2034: hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2035: hr_multi_message.add;
2036: End If;
2037: if g_debug then
2038: hr_utility.set_location('Leaving:'||l_proc,100);
2039: end if;
2040:
2041: End chk_add_steps_in_all_grades;
2042: --
2077: AND nvl(grd.Information307,hr_general.end_of_time)
2078: AND Information4 = p_business_group_id;
2079: BEGIN
2080: if g_debug then
2081: hr_utility.set_location('Entering:'||l_proc,10);
2082: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2083: hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2084: end if;
2085:
2078: AND Information4 = p_business_group_id;
2079: BEGIN
2080: if g_debug then
2081: hr_utility.set_location('Entering:'||l_proc,10);
2082: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2083: hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2084: end if;
2085:
2086: For l_rec in csr_grades
2079: BEGIN
2080: if g_debug then
2081: hr_utility.set_location('Entering:'||l_proc,10);
2082: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2083: hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2084: end if;
2085:
2086: For l_rec in csr_grades
2087: Loop
2090: End loop;
2091: l_grades := substr(l_grades,1,length(l_grades)-2);
2092:
2093: if g_debug then
2094: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2095: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2096: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2097: end if;
2098:
2091: l_grades := substr(l_grades,1,length(l_grades)-2);
2092:
2093: if g_debug then
2094: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2095: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2096: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2097: end if;
2098:
2099:
2092:
2093: if g_debug then
2094: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2095: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2096: hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2097: end if;
2098:
2099:
2100: If l_found then
2097: end if;
2098:
2099:
2100: If l_found then
2101: hr_utility.set_message(8302,'PQH_GSP_INVALID_GRD_IN_GRDLDR');
2102: hr_utility.set_message_token('GRADES',l_grades);
2103: hr_multi_message.add;
2104: End If;
2105:
2098:
2099:
2100: If l_found then
2101: hr_utility.set_message(8302,'PQH_GSP_INVALID_GRD_IN_GRDLDR');
2102: hr_utility.set_message_token('GRADES',l_grades);
2103: hr_multi_message.add;
2104: End If;
2105:
2106: if g_debug then
2103: hr_multi_message.add;
2104: End If;
2105:
2106: if g_debug then
2107: hr_utility.set_location('Leaving:'||l_proc, 15);
2108: end if;
2109: End chk_valid_grd_in_grdldr;
2110:
2111: --
2149:
2150:
2151: Begin
2152: if g_debug then
2153: hr_utility.set_location('Entering:'||l_proc, 10);
2154: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2155: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2156: hr_utility.set_location('p_activate:'||p_activate, 35);
2157: end if;
2150:
2151: Begin
2152: if g_debug then
2153: hr_utility.set_location('Entering:'||l_proc, 10);
2154: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2155: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2156: hr_utility.set_location('p_activate:'||p_activate, 35);
2157: end if;
2158: hr_multi_message.enable_message_list;
2151: Begin
2152: if g_debug then
2153: hr_utility.set_location('Entering:'||l_proc, 10);
2154: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2155: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2156: hr_utility.set_location('p_activate:'||p_activate, 35);
2157: end if;
2158: hr_multi_message.enable_message_list;
2159:
2152: if g_debug then
2153: hr_utility.set_location('Entering:'||l_proc, 10);
2154: hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2155: hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2156: hr_utility.set_location('p_activate:'||p_activate, 35);
2157: end if;
2158: hr_multi_message.enable_message_list;
2159:
2160: If p_pgm_id IS NOT NULL Then
2158: hr_multi_message.enable_message_list;
2159:
2160: If p_pgm_id IS NOT NULL Then
2161: if g_debug then
2162: hr_utility.set_location('PGM Id Not Null ', 40);
2163: end if;
2164:
2165: -- Get Status of PGM
2166: Open csr_pgm_status;
2166: Open csr_pgm_status;
2167: Fetch csr_pgm_status into l_old_status;
2168: Close csr_pgm_status;
2169: if g_debug then
2170: hr_utility.set_location('PGM Status :'||l_old_status, 41);
2171: end if;
2172:
2173: -- Raise the error in the following case.
2174: -- 1) If PGM is Active And
2181: Fetch csr_active_gl into l_dummy;
2182: If csr_active_gl%Found then
2183: Close csr_active_gl;
2184: if g_debug then
2185: hr_utility.set_location('Found ', 45);
2186: end if;
2187: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2188: hr_utility.raise_error;
2189: Else
2183: Close csr_active_gl;
2184: if g_debug then
2185: hr_utility.set_location('Found ', 45);
2186: end if;
2187: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2188: hr_utility.raise_error;
2189: Else
2190: if g_debug then
2191: hr_utility.set_location('Not Found ', 50);
2184: if g_debug then
2185: hr_utility.set_location('Found ', 45);
2186: end if;
2187: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2188: hr_utility.raise_error;
2189: Else
2190: if g_debug then
2191: hr_utility.set_location('Not Found ', 50);
2192: end if;
2187: hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2188: hr_utility.raise_error;
2189: Else
2190: if g_debug then
2191: hr_utility.set_location('Not Found ', 50);
2192: end if;
2193: Close csr_active_gl;
2194: End If;
2195: End If; -- stauts check
2195: End If; -- stauts check
2196: End If; -- Pgm Not Null
2197:
2198: if g_debug then
2199: hr_utility.set_location('Leaving:'||l_proc, 55);
2200: end if;
2201: EXCEPTION
2202: WHEN others THEN
2203: fnd_msg_pub.add;
2335:
2336:
2337: Begin
2338: if g_debug then
2339: hr_utility.set_location('Entering:'||l_proc, 10);
2340: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2341: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2342: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2343: end if;
2336:
2337: Begin
2338: if g_debug then
2339: hr_utility.set_location('Entering:'||l_proc, 10);
2340: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2341: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2342: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2343: end if;
2344:
2337: Begin
2338: if g_debug then
2339: hr_utility.set_location('Entering:'||l_proc, 10);
2340: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2341: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2342: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2343: end if;
2344:
2345:
2338: if g_debug then
2339: hr_utility.set_location('Entering:'||l_proc, 10);
2340: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2341: hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2342: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2343: end if;
2344:
2345:
2346: Open csr_pgm_id;
2347: Fetch csr_pgm_id into l_pgm_id ;
2348: Close csr_pgm_id;
2349:
2350: if g_debug then
2351: hr_utility.set_location('l_pgm_id:'||l_pgm_id, 30);
2352: end if;
2353:
2354: Open csr_grade_id;
2355: Fetch csr_grade_id into l_grade_id;
2355: Fetch csr_grade_id into l_grade_id;
2356: Close csr_grade_id;
2357:
2358: if g_debug then
2359: hr_utility.set_location('l_grade_id:'||l_grade_id, 40);
2360: end if;
2361:
2362: If (l_pgm_id IS Not Null And l_grade_id IS Not Null) Then
2363: if g_debug then
2360: end if;
2361:
2362: If (l_pgm_id IS Not Null And l_grade_id IS Not Null) Then
2363: if g_debug then
2364: hr_utility.set_location('Record is in Main Tables', 50);
2365: end if;
2366: if p_grade_result_id IS NOT NULL Then
2367: Open csr_ovn(p_grade_result_id);
2368: Fetch csr_ovn into l_ovn;
2377:
2378: end if;
2379: Else
2380: if g_debug then
2381: hr_utility.set_location('Record is in Staging Area', 50);
2382: end if;
2383: if p_grade_result_id IS NOT NULL Then
2384:
2385: -- Update Plan Record with Gs_Mirror_Src_Entity_Result_Id as Null
2412: end if;
2413:
2414: End if;
2415: if g_debug then
2416: hr_utility.set_location('Leaving:'||l_proc, 10);
2417: end if;
2418:
2419: End update_or_delete_grade;
2420:
2488:
2489:
2490: Begin
2491: if g_debug then
2492: hr_utility.set_location('Entering:'||l_proc, 10);
2493: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2494: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2495: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2496: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2489:
2490: Begin
2491: if g_debug then
2492: hr_utility.set_location('Entering:'||l_proc, 10);
2493: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2494: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2495: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2496: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2497: end if;
2490: Begin
2491: if g_debug then
2492: hr_utility.set_location('Entering:'||l_proc, 10);
2493: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2494: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2495: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2496: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2497: end if;
2498:
2491: if g_debug then
2492: hr_utility.set_location('Entering:'||l_proc, 10);
2493: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2494: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2495: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2496: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2497: end if;
2498:
2499:
2492: hr_utility.set_location('Entering:'||l_proc, 10);
2493: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2494: hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2495: hr_utility.set_location('p_step_id:'||p_step_id, 30);
2496: hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2497: end if;
2498:
2499:
2500: l_use_points:= pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id=>p_copy_entity_txn_id);
2498:
2499:
2500: l_use_points:= pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id=>p_copy_entity_txn_id);
2501: if g_debug then
2502: hr_utility.set_location('l_use_points:'||l_use_points, 50);
2503: end if;
2504:
2505: Open csr_pgm_id;
2506: Fetch csr_pgm_id into l_pgm_id;
2505: Open csr_pgm_id;
2506: Fetch csr_pgm_id into l_pgm_id;
2507: Close csr_pgm_id;
2508: if g_debug then
2509: hr_utility.set_location('l_pgm_id:'||l_pgm_id,60);
2510: end if;
2511:
2512: Open csr_grade_id;
2513: Fetch csr_grade_id into l_grade_id;
2512: Open csr_grade_id;
2513: Fetch csr_grade_id into l_grade_id;
2514: Close csr_grade_id;
2515: if g_debug then
2516: hr_utility.set_location('l_grade_id:'||l_grade_id,70);
2517: end if;
2518:
2519: -- Rec In Main Tables
2520: If (l_pgm_id IS NOT Null And l_grade_id IS NOT Null And p_step_id IS NOT Null) Then
2518:
2519: -- Rec In Main Tables
2520: If (l_pgm_id IS NOT Null And l_grade_id IS NOT Null And p_step_id IS NOT Null) Then
2521: if g_debug then
2522: hr_utility.set_location('Rec is in Main Tables',80);
2523: end if;
2524:
2525: -- Update Point Record
2526: if ((l_use_points = 'STEP') AND (p_point_result_id IS NOT NULL)) then
2536: p_information323 => null);
2537:
2538: end if;
2539: if g_debug then
2540: hr_utility.set_location('Point Rec is Updated Sucessfully',90);
2541: end if;
2542:
2543: -- Update Step Record
2544: if p_step_result_id IS NOT NULL then
2554: p_information323 => null);
2555:
2556: End if;
2557: if g_debug then
2558: hr_utility.set_location('Step Rec is Updated Sucessfully',100);
2559: end if;
2560: Else
2561: if g_debug then
2562: hr_utility.set_location('Record is in Staging Area Only',110);
2558: hr_utility.set_location('Step Rec is Updated Sucessfully',100);
2559: end if;
2560: Else
2561: if g_debug then
2562: hr_utility.set_location('Record is in Staging Area Only',110);
2563: end if;
2564: -- Delete Point Record
2565: if ((l_use_points = 'STEP') And p_point_result_id IS NOT NULL) then
2566:
2572: p_effective_date => p_effective_date,
2573: p_object_version_number => l_ovn);
2574: end if;
2575: if g_debug then
2576: hr_utility.set_location('Point Rec is Deleted Sucessfully',120);
2577: end if;
2578:
2579: -- Delete Step Record
2580: if p_step_result_id IS NOT NULL then
2587: p_effective_date => p_effective_date,
2588: p_object_version_number => l_ovn);
2589: end if;
2590: if g_debug then
2591: hr_utility.set_location('Step Rec is Deleted Sucessfully',130);
2592: end if;
2593:
2594: End if;
2595: if g_debug then
2592: end if;
2593:
2594: End if;
2595: if g_debug then
2596: hr_utility.set_location('Leaving :' ||l_proc, 200);
2597: end if;
2598: End update_or_delete_step;
2599:
2600: --
2625: order by opt.information253;
2626: stepnum NUMBER :=0;
2627: begin
2628: if g_debug then
2629: hr_utility.set_location('Entering:'||l_proc, 5);
2630: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2631: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2632: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2633: end if;
2626: stepnum NUMBER :=0;
2627: begin
2628: if g_debug then
2629: hr_utility.set_location('Entering:'||l_proc, 5);
2630: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2631: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2632: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2633: end if;
2634: for i in csr_points loop
2627: begin
2628: if g_debug then
2629: hr_utility.set_location('Entering:'||l_proc, 5);
2630: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2631: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2632: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2633: end if;
2634: for i in csr_points loop
2635: stepnum :=stepnum+1;
2628: if g_debug then
2629: hr_utility.set_location('Entering:'||l_proc, 5);
2630: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2631: hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2632: hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2633: end if;
2634: for i in csr_points loop
2635: stepnum :=stepnum+1;
2636: ben_copy_entity_results_api.update_copy_entity_results(
2642: p_object_version_number => i.object_version_number,
2643: p_information323 => null);
2644: end loop;
2645: if g_debug then
2646: hr_utility.set_location('Leaving:'||l_proc, 35);
2647: end if;
2648: END SET_STEP_NAME;
2649:
2650: --
2715: And nvl(Effective_End_Date,hr_general.end_of_time);
2716:
2717: BEGIN
2718: if g_debug then
2719: hr_utility.set_location('Entering:'||l_proc, 5);
2720: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2721: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2722: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2723: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2716:
2717: BEGIN
2718: if g_debug then
2719: hr_utility.set_location('Entering:'||l_proc, 5);
2720: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2721: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2722: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2723: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2724: end if;
2717: BEGIN
2718: if g_debug then
2719: hr_utility.set_location('Entering:'||l_proc, 5);
2720: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2721: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2722: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2723: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2724: end if;
2725: p_status := null;
2718: if g_debug then
2719: hr_utility.set_location('Entering:'||l_proc, 5);
2720: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2721: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2722: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2723: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2724: end if;
2725: p_status := null;
2726:
2719: hr_utility.set_location('Entering:'||l_proc, 5);
2720: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2721: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2722: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2723: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2724: end if;
2725: p_status := null;
2726:
2727: If p_pgm_id IS NOT NULL Then
2728: Open csr_default_grdldr;
2729: Fetch csr_default_grdldr into l_dummy;
2730: If csr_default_grdldr%NotFound Then
2731: if g_debug then
2732: hr_utility.set_location('Not Default Grade Ladder', 26);
2733: end if;
2734: --If Emp Placements exists on Grades then throw error msg
2735: For l_rec IN csr_grades
2736: Loop
2743: Close csr_gl_grd_emp_assg;
2744: End Loop;
2745: Else
2746: if g_debug then
2747: hr_utility.set_location(' Default Grade Ladder', 27);
2748: end if;
2749: -- If Emp Placements exists on Default Grade Ladder Grades then throw warning msg
2750: For l_rec IN csr_grades
2751: Loop
2752: Open csr_grd_assg_emp(l_rec.Information253);
2753: Fetch csr_grd_assg_emp into l_dummy;
2754: If csr_grd_assg_emp%Found Then
2755: if g_debug then
2756: hr_utility.set_location(' Grades are ... '||l_rec.Information5, 28);
2757: end if;
2758: l_no_war_grades := l_no_war_grades || l_rec.Information5 ||' , ';
2759: l_grd_war_found := TRUE;
2760: End If;
2764: Close csr_default_grdldr;
2765:
2766: If l_found then
2767: if g_debug then
2768: hr_utility.set_location(' Found Error Grades', 27);
2769: end if;
2770: l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2771: p_status := 'E';
2772: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2768: hr_utility.set_location(' Found Error Grades', 27);
2769: end if;
2770: l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2771: p_status := 'E';
2772: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2773: hr_utility.set_message_token('GRADES',l_no_grades );
2774: hr_multi_message.add;
2775: End if;
2776:
2769: end if;
2770: l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2771: p_status := 'E';
2772: hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2773: hr_utility.set_message_token('GRADES',l_no_grades );
2774: hr_multi_message.add;
2775: End if;
2776:
2777: if l_grd_war_found then
2775: End if;
2776:
2777: if l_grd_war_found then
2778: if g_debug then
2779: hr_utility.set_location(' Found Warning Grades', 27);
2780: end if;
2781: l_no_war_grades := substr(l_no_war_grades,1,length(l_no_war_grades)-2);
2782: hr_utility.set_location(' Length '||length(l_no_war_grades), 28);
2783: p_status := l_no_war_grades;
2778: if g_debug then
2779: hr_utility.set_location(' Found Warning Grades', 27);
2780: end if;
2781: l_no_war_grades := substr(l_no_war_grades,1,length(l_no_war_grades)-2);
2782: hr_utility.set_location(' Length '||length(l_no_war_grades), 28);
2783: p_status := l_no_war_grades;
2784: end if;
2785: End If;
2786: if g_debug then
2783: p_status := l_no_war_grades;
2784: end if;
2785: End If;
2786: if g_debug then
2787: hr_utility.set_location('Leaving:'||l_proc, 100);
2788: end if;
2789: Exception
2790: When Others Then
2791: if g_debug then
2788: end if;
2789: Exception
2790: When Others Then
2791: if g_debug then
2792: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2793: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2794: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2795: end if;
2796:
2789: Exception
2790: When Others Then
2791: if g_debug then
2792: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2793: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2794: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2795: end if;
2796:
2797: p_status := null;
2790: When Others Then
2791: if g_debug then
2792: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2793: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2794: hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2795: end if;
2796:
2797: p_status := null;
2798: END chk_unlink_grd_from_grdldr;
2830: And Information4 = p_business_group_id ;
2831:
2832: BEGIN
2833: if g_debug then
2834: hr_utility.set_location('Entering:'||l_proc, 5);
2835: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2836: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2837: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2838: end if;
2831:
2832: BEGIN
2833: if g_debug then
2834: hr_utility.set_location('Entering:'||l_proc, 5);
2835: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2836: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2837: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2838: end if;
2839:
2832: BEGIN
2833: if g_debug then
2834: hr_utility.set_location('Entering:'||l_proc, 5);
2835: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2836: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2837: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2838: end if;
2839:
2840: For l_rec IN csr_steps
2833: if g_debug then
2834: hr_utility.set_location('Entering:'||l_proc, 5);
2835: hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2836: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2837: hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2838: end if;
2839:
2840: For l_rec IN csr_steps
2841: Loop
2842: PER_SPINAL_POINT_STEPS_PKG.del_chks_del(p_step_id => l_rec.Information253,
2843: p_sess => p_effective_date);
2844:
2845: If (l_rec.INFORMATION98 = 'Y') Then -- Ceiling_Step_Flag Values Y/N
2846: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
2847: hr_utility.raise_error;
2848: END IF;
2849: End Loop;
2850:
2843: p_sess => p_effective_date);
2844:
2845: If (l_rec.INFORMATION98 = 'Y') Then -- Ceiling_Step_Flag Values Y/N
2846: hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
2847: hr_utility.raise_error;
2848: END IF;
2849: End Loop;
2850:
2851: if g_debug then
2848: END IF;
2849: End Loop;
2850:
2851: if g_debug then
2852: hr_utility.set_location('Leaving:'||l_proc, 100);
2853: end if;
2854:
2855: END chk_unlink_step_from_grdldr;
2856:
2877:
2878: --
2879: Begin
2880: if g_debug then
2881: hr_utility.set_location('Entering:'||l_proc, 5);
2882: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2883: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2884: end if;
2885:
2878: --
2879: Begin
2880: if g_debug then
2881: hr_utility.set_location('Entering:'||l_proc, 5);
2882: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2883: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2884: end if;
2885:
2886:
2879: Begin
2880: if g_debug then
2881: hr_utility.set_location('Entering:'||l_proc, 5);
2882: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2883: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2884: end if;
2885:
2886:
2887: --
2885:
2886:
2887: --
2888: if g_debug then
2889: hr_utility.set_location('Chk Name...', 50);
2890: end if;
2891: Open csr_name;
2892: Fetch csr_name into l_dummy;
2893: If csr_name%Found then
2891: Open csr_name;
2892: Fetch csr_name into l_dummy;
2893: If csr_name%Found then
2894: Close csr_name;
2895: hr_utility.set_message(8302,'PQH_GSP_GRDLDR_NAME_UNIQUE');
2896: hr_multi_message.add;
2897: Else
2898: Close csr_name;
2899: End If;
2898: Close csr_name;
2899: End If;
2900: --
2901: if g_debug then
2902: hr_utility.set_location('Leaving:'||l_proc, 15);
2903: end if;
2904: End chk_grdldr_name_unique;
2905:
2906: --
2936:
2937: --
2938: Begin
2939: if g_debug then
2940: hr_utility.set_location('Entering:'||l_proc, 5);
2941: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2942: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2943: hr_utility.set_location('p_short_name:'||p_short_name, 20);
2944: hr_utility.set_location('p_short_code:'||p_short_code, 25);
2937: --
2938: Begin
2939: if g_debug then
2940: hr_utility.set_location('Entering:'||l_proc, 5);
2941: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2942: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2943: hr_utility.set_location('p_short_name:'||p_short_name, 20);
2944: hr_utility.set_location('p_short_code:'||p_short_code, 25);
2945: hr_utility.set_location('Chk Short Name...', 60);
2938: Begin
2939: if g_debug then
2940: hr_utility.set_location('Entering:'||l_proc, 5);
2941: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2942: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2943: hr_utility.set_location('p_short_name:'||p_short_name, 20);
2944: hr_utility.set_location('p_short_code:'||p_short_code, 25);
2945: hr_utility.set_location('Chk Short Name...', 60);
2946: end if;
2939: if g_debug then
2940: hr_utility.set_location('Entering:'||l_proc, 5);
2941: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2942: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2943: hr_utility.set_location('p_short_name:'||p_short_name, 20);
2944: hr_utility.set_location('p_short_code:'||p_short_code, 25);
2945: hr_utility.set_location('Chk Short Name...', 60);
2946: end if;
2947:
2940: hr_utility.set_location('Entering:'||l_proc, 5);
2941: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2942: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2943: hr_utility.set_location('p_short_name:'||p_short_name, 20);
2944: hr_utility.set_location('p_short_code:'||p_short_code, 25);
2945: hr_utility.set_location('Chk Short Name...', 60);
2946: end if;
2947:
2948: Open csr_short_name;
2941: hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2942: hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
2943: hr_utility.set_location('p_short_name:'||p_short_name, 20);
2944: hr_utility.set_location('p_short_code:'||p_short_code, 25);
2945: hr_utility.set_location('Chk Short Name...', 60);
2946: end if;
2947:
2948: Open csr_short_name;
2949: Fetch csr_short_name into l_dummy;
2948: Open csr_short_name;
2949: Fetch csr_short_name into l_dummy;
2950: If csr_short_name%Found then
2951: Close csr_short_name;
2952: hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_CODE_UNQ');
2953: hr_multi_message.add;
2954: Else
2955: Close csr_short_name;
2956: End If;
2955: Close csr_short_name;
2956: End If;
2957: --
2958: if g_debug then
2959: hr_utility.set_location('Chk Short Code....', 70);
2960: end if;
2961: Open csr_short_code;
2962: Fetch csr_short_code into l_dummy;
2963: If csr_short_code%Found then
2961: Open csr_short_code;
2962: Fetch csr_short_code into l_dummy;
2963: If csr_short_code%Found then
2964: Close csr_short_code;
2965: hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_CODE_UNQ');
2966: hr_multi_message.add;
2967: Else
2968: Close csr_short_code;
2969: End If;
2968: Close csr_short_code;
2969: End If;
2970: --
2971: if g_debug then
2972: hr_utility.set_location('Leaving:'||l_proc, 15);
2973: end if;
2974: End chk_gl_sht_name_code_unique;
2975:
2976:
3020: l_proc varchar2(72) := g_package||'validate_grade_ladder';
3021:
3022: BEGIN
3023: if g_debug then
3024: hr_utility.set_location('Entering :' ||l_proc, 10);
3025: end if;
3026:
3027: -- Check Grade Ladder Name is Unique with in the Business Group
3028: if g_debug then
3025: end if;
3026:
3027: -- Check Grade Ladder Name is Unique with in the Business Group
3028: if g_debug then
3029: hr_utility.set_location('Check Grade Ladder Name is Unique ', 20);
3030: end if;
3031: pqh_gsp_utility.chk_grdldr_name_unique( p_pgm_id => p_pgm_id
3032: ,p_business_group_id => p_business_group_id
3033: ,p_name => p_name);
3033: ,p_name => p_name);
3034:
3035: -- Check Grade Ladder , Short Name and Short Code is Unique with in the Business Group
3036: if g_debug then
3037: hr_utility.set_location('Check Grade Ladder Short Name and Short Code is Unique ', 25);
3038: end if;
3039:
3040: If (p_short_name IS NOT NULL or p_short_code IS NOT NULL) Then
3041: pqh_gsp_utility.chk_gl_sht_name_code_unique( p_pgm_id => p_pgm_id
3045:
3046: End If;
3047: -- Check if there is only one default grade ladder in the business group
3048: if g_debug then
3049: hr_utility.set_location('Default Grade Ladder in BG ', 30);
3050: end if;
3051:
3052: pqh_gsp_utility.chk_default_ladder_exist ( p_pgm_id => p_pgm_id,
3053: p_business_group_id => p_business_group_id,
3057: -- which there are employee placements.
3058:
3059: /*
3060: if g_debug then
3061: hr_utility.set_location('Inactivated GL ', 40);
3062: end if;
3063: pqh_gsp_utility.chk_inactivate_grdldr ( p_pgm_id => p_pgm_id,
3064: p_effective_date => p_effective_date,
3065: p_business_group_id => p_business_group_id);
3071: -- Check if score calculation method is entered against lookup
3072: -- Check if score calcultion method is rule, the fast formula is selected
3073: -- Validate all lookups
3074: if g_debug then
3075: hr_utility.set_location('Leaving :' ||l_proc, 100);
3076: end if;
3077: End;
3078: --
3079: --
3163:
3164:
3165: BEGIN
3166: if g_debug then
3167: hr_utility.set_location('Entering :' ||l_proc, 10);
3168: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3169: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3170: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3171: end if;
3164:
3165: BEGIN
3166: if g_debug then
3167: hr_utility.set_location('Entering :' ||l_proc, 10);
3168: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3169: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3170: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3171: end if;
3172:
3165: BEGIN
3166: if g_debug then
3167: hr_utility.set_location('Entering :' ||l_proc, 10);
3168: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3169: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3170: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3171: end if;
3172:
3173:
3166: if g_debug then
3167: hr_utility.set_location('Entering :' ||l_proc, 10);
3168: hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3169: hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3170: hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3171: end if;
3172:
3173:
3174: Open csr_grdldr_details;
3175: Fetch csr_grdldr_details into l_curr_gl_result_id,l_curr_gl_currency,l_curr_gl_period;
3176: Close csr_grdldr_details;
3177:
3178: if g_debug then
3179: hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3180: hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3181: hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3182: end if;
3183:
3176: Close csr_grdldr_details;
3177:
3178: if g_debug then
3179: hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3180: hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3181: hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3182: end if;
3183:
3184: -- fix for bug 7114098
3177:
3178: if g_debug then
3179: hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3180: hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3181: hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3182: end if;
3183:
3184: -- fix for bug 7114098
3185: open csr_pgm_val;
3202:
3203: if l_found Then
3204: l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3205: if g_debug then
3206: hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3207: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3208: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3209: end if;
3210:
3203: if l_found Then
3204: l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3205: if g_debug then
3206: hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3207: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3208: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3209: end if;
3210:
3211: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3204: l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3205: if g_debug then
3206: hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3207: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3208: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3209: end if;
3210:
3211: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3212: hr_utility.set_message_token('GRADES',l_dummy );
3207: hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3208: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3209: end if;
3210:
3211: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3212: hr_utility.set_message_token('GRADES',l_dummy );
3213: hr_multi_message.add;
3214: end if;
3215: if g_debug then
3208: hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3209: end if;
3210:
3211: hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3212: hr_utility.set_message_token('GRADES',l_dummy );
3213: hr_multi_message.add;
3214: end if;
3215: if g_debug then
3216: hr_utility.set_location('Leaving :' ||l_proc, 100);
3212: hr_utility.set_message_token('GRADES',l_dummy );
3213: hr_multi_message.add;
3214: end if;
3215: if g_debug then
3216: hr_utility.set_location('Leaving :' ||l_proc, 100);
3217: end if;
3218: END chk_grdldr_grd_curreny_rate;
3219:
3220: --
3255: l_proc varchar2(200) := 'chk_duplicate_crset_exists';
3256: --
3257: Begin
3258: --
3259: hr_utility.set_location('Entering :' ||l_proc, 5);
3260: --
3261: p_duplicate_exists := 'N';
3262: --
3263: For crset_rec in csr_crset loop
3276: nvl(crset_rec.INFORMATION104,'XXX') = nvl(p_rating_type_cd,'XXX')) then
3277: --
3278: p_duplicate_cset_name := crset_rec.INFORMATION151;
3279: p_duplicate_exists := 'Y';
3280: hr_utility.set_location('Duplicate :' ||p_duplicate_cset_name, 7);
3281: --
3282: End if;
3283: --
3284: --
3283: --
3284: --
3285: End loop;
3286: --
3287: hr_utility.set_location('Leaving :' ||l_proc, 10);
3288: --
3289: End;
3290: -------------------------------------------------------------------
3291: -- This procedure validates if criteria set has been properly defined i.e
3312: l_continue boolean;
3313: --
3314: Begin
3315: --
3316: hr_utility.set_location('Entering :' ||l_proc, 5);
3317: --
3318: For crset_rec in csr_crset loop
3319: --
3320: l_continue := true;
3321: --
3322: -- Criteria set name should be entered. Ideally this error should never be raised.
3323: --
3324: If crset_rec.INFORMATION151 is NULL then
3325: hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NAME_ERR');
3326: hr_multi_message.add;
3327: l_continue := false;
3328: End if;
3329: --
3330: -- Criteria set should have at least one criterion
3331: --
3332: If l_continue then
3333: --
3334: hr_utility.set_location('Check 1' , 5);
3335: --
3336: If (crset_rec.INFORMATION232 IS NULL and
3337: crset_rec.INFORMATION233 IS NULL and
3338: crset_rec.INFORMATION234 IS NULL and
3343: crset_rec.INFORMATION102 IS NULL and
3344: crset_rec.INFORMATION103 IS NULL and
3345: crset_rec.INFORMATION104 IS NULL ) then
3346: --
3347: hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NO_CRIT');
3348: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3349: hr_multi_message.add;
3350: l_continue := false;
3351: --
3344: crset_rec.INFORMATION103 IS NULL and
3345: crset_rec.INFORMATION104 IS NULL ) then
3346: --
3347: hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NO_CRIT');
3348: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3349: hr_multi_message.add;
3350: l_continue := false;
3351: --
3352: End if;
3355:
3356: If l_continue then
3357: --
3358: --
3359: hr_utility.set_location('Perf Rating Check ' , 5);
3360: --
3361: -- If performance type is entered, rating type must also be entered.
3362: --
3363: If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3362: --
3363: If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3364: (crset_rec.INFORMATION103 IS NOT NULL and crset_rec.INFORMATION104 IS NULL)) then
3365: --
3366: hr_utility.set_message(8302,'PQH_GSP_INVALID_PERF_RATING');
3367: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3368: hr_multi_message.add;
3369: l_continue := false;
3370: --
3363: If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3364: (crset_rec.INFORMATION103 IS NOT NULL and crset_rec.INFORMATION104 IS NULL)) then
3365: --
3366: hr_utility.set_message(8302,'PQH_GSP_INVALID_PERF_RATING');
3367: hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3368: hr_multi_message.add;
3369: l_continue := false;
3370: --
3371: End if;
3376: --
3377: If l_continue then
3378: --
3379: --
3380: hr_utility.set_location('Duplicate Check' || to_char(crset_rec.information161) , 5);
3381: --
3382: chk_duplicate_crset_exists(
3383: p_copy_entity_txn_id => p_copy_entity_txn_id,
3384: p_effective_date => p_effective_date,
3397: p_duplicate_cset_name => l_duplicate_cset_name);
3398: --
3399: If l_duplicate_exists = 'Y' then
3400: --
3401: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3402: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3403: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3404: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3405: hr_multi_message.add;
3398: --
3399: If l_duplicate_exists = 'Y' then
3400: --
3401: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3402: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3403: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3404: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3405: hr_multi_message.add;
3406: --
3399: If l_duplicate_exists = 'Y' then
3400: --
3401: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3402: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3403: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3404: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3405: hr_multi_message.add;
3406: --
3407: End if;
3400: --
3401: hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3402: hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3403: hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3404: hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3405: hr_multi_message.add;
3406: --
3407: End if;
3408: --
3409: End if;
3410: --
3411: End loop;
3412: --
3413: hr_utility.set_location('Leaving :' ||l_proc, 10);
3414: --
3415: End;
3416: ---------------------------chk_review_submit_val-----------------------------
3417: --
3465:
3466: BEGIN
3467: hr_multi_message.enable_message_list;
3468: if g_debug then
3469: hr_utility.set_location('Entering :' ||l_proc, 10);
3470: end if;
3471:
3472: If p_pgm_id IS NOT NULL Then
3473: Open csr_pgm_details;
3491: end if;
3492:
3493: -- Check Grades can be removed from the Grade Ladder
3494: if g_debug then
3495: hr_utility.set_location('Check Grades can be removed from the Grade Ladder', 20);
3496: end if;
3497: pqh_gsp_utility.chk_unlink_grd_from_grdldr
3498: (p_pgm_id => p_pgm_id
3499: ,p_copy_entity_txn_id => p_copy_entity_txn_id
3501: ,p_effective_date => p_effective_date
3502: ,p_status => l_status
3503: );
3504: if g_debug then
3505: hr_utility.set_location('After Check unlink grd from gl ',25);
3506: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3507: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3508: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3509: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3502: ,p_status => l_status
3503: );
3504: if g_debug then
3505: hr_utility.set_location('After Check unlink grd from gl ',25);
3506: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3507: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3508: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3509: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3510: end if;
3503: );
3504: if g_debug then
3505: hr_utility.set_location('After Check unlink grd from gl ',25);
3506: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3507: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3508: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3509: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3510: end if;
3511:
3504: if g_debug then
3505: hr_utility.set_location('After Check unlink grd from gl ',25);
3506: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3507: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3508: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3509: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3510: end if;
3511:
3512: -- Check if steps have been added to only some steps in a grade
3505: hr_utility.set_location('After Check unlink grd from gl ',25);
3506: hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3507: hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3508: hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3509: hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3510: end if;
3511:
3512: -- Check if steps have been added to only some steps in a grade
3513: -- and warns the user that the setup cannot be saved unless steps are added to all
3513: -- and warns the user that the setup cannot be saved unless steps are added to all
3514: -- the Grades.
3515:
3516: if g_debug then
3517: hr_utility.set_location('Only Some Steps are attached Validation', 30);
3518: end if;
3519: pqh_gsp_utility.chk_add_steps_in_all_grades ( p_copy_entity_txn_id => p_copy_entity_txn_id ,
3520: p_business_group_id => p_business_group_id);
3521:
3527: -- Check if Grades are valid as of the Grade Ladder Effective Date
3528:
3529:
3530: if g_debug then
3531: hr_utility.set_location('InValid Grades Validation', 40);
3532: end if;
3533: pqh_gsp_utility.chk_valid_grd_in_grdldr ( p_copy_entity_txn_id => p_copy_entity_txn_id,
3534: p_effective_date => p_effective_date,
3535: p_business_group_id => p_business_group_id);
3537:
3538: -- Check Currecny, Rate Period
3539:
3540: if g_debug then
3541: hr_utility.set_location('Currecny, Rate Period Validation', 70);
3542: end if;
3543: pqh_gsp_utility.chk_grdldr_grd_curreny_rate ( p_copy_entity_txn_id => p_copy_entity_txn_id
3544: ,p_business_group_id => p_business_group_id
3545: ,p_effective_date => p_effective_date);
3545: ,p_effective_date => p_effective_date);
3546:
3547: -- Check Steps can be removed from the Grade Ladder
3548: if g_debug then
3549: hr_utility.set_location('Check Steps can be removed from the Grade Ladder', 80);
3550: end if;
3551:
3552: pqh_gsp_utility.chk_unlink_step_from_grdldr
3553: (p_copy_entity_txn_id => p_copy_entity_txn_id
3556: );
3557:
3558: -- Check for Plan Type and Life Event
3559: if g_debug then
3560: hr_utility.set_location('Check Plan Type and Life Event', 85);
3561: end if;
3562: pqh_gsp_stage_to_hr.setup_check( p_copy_entity_txn_id => p_copy_entity_txn_id,
3563: p_effective_date => p_effective_date,
3564: p_business_group_id => p_business_group_id,
3568: p_plan_tp_created_flag => p_plan_tp_created_flag
3569: );
3570:
3571: if l_pt_le_status = 'WRONG-DATE-PROG-LE' then
3572: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3573: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3574: hr_multi_message.add;
3575: elsif l_pt_le_status = 'MANY-PROG-LE' then
3576: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3569: );
3570:
3571: if l_pt_le_status = 'WRONG-DATE-PROG-LE' then
3572: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3573: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3574: hr_multi_message.add;
3575: elsif l_pt_le_status = 'MANY-PROG-LE' then
3576: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3577: hr_multi_message.add;
3572: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3573: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3574: hr_multi_message.add;
3575: elsif l_pt_le_status = 'MANY-PROG-LE' then
3576: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3577: hr_multi_message.add;
3578: elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3579: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3580: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3575: elsif l_pt_le_status = 'MANY-PROG-LE' then
3576: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3577: hr_multi_message.add;
3578: elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3579: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3580: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3581: hr_multi_message.add;
3582: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3583: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3576: hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3577: hr_multi_message.add;
3578: elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3579: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3580: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3581: hr_multi_message.add;
3582: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3583: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3584: hr_multi_message.add;
3579: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3580: hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3581: hr_multi_message.add;
3582: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3583: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3584: hr_multi_message.add;
3585: elsif l_pt_le_status = 'WRONG-DATE-PT' then
3586: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3587: hr_multi_message.add;
3582: elsif l_pt_le_status = 'MANY-SYNC-LE' then
3583: hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3584: hr_multi_message.add;
3585: elsif l_pt_le_status = 'WRONG-DATE-PT' then
3586: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3587: hr_multi_message.add;
3588: elsif l_pt_le_status = 'MANY-PT' then
3589: hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3590: hr_multi_message.add;
3585: elsif l_pt_le_status = 'WRONG-DATE-PT' then
3586: hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3587: hr_multi_message.add;
3588: elsif l_pt_le_status = 'MANY-PT' then
3589: hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3590: hr_multi_message.add;
3591: elsif l_pt_le_status = 'PROG-LE-ERR' or l_pt_le_status = 'SYNC-LE-ERR' or l_pt_le_status = 'PT-ERR' then
3592: hr_utility.set_message(8302,'PQH_GSP_PT_LE_ERR');
3593: hr_multi_message.add;
3588: elsif l_pt_le_status = 'MANY-PT' then
3589: hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3590: hr_multi_message.add;
3591: elsif l_pt_le_status = 'PROG-LE-ERR' or l_pt_le_status = 'SYNC-LE-ERR' or l_pt_le_status = 'PT-ERR' then
3592: hr_utility.set_message(8302,'PQH_GSP_PT_LE_ERR');
3593: hr_multi_message.add;
3594: else
3595: null;
3596: end if;
3602: p_effective_date => p_effective_date);
3603: --
3604: --
3605: if g_debug then
3606: hr_utility.set_location('Check FR PS Corps Review Submit Validatsons...', 87);
3607: end if;
3608:
3609: -- Adde a call for FR Corps.
3610: pqh_corps_utility.review_submit_valid_corps(p_copy_entity_txn_id => p_copy_entity_txn_id
3617: hr_multi_message.end_validation_set;
3618:
3619: p_status := l_status;
3620: if g_debug then
3621: hr_utility.set_location('Leaving :' ||l_proc, 200);
3622: end if;
3623: Exception
3624:
3625: when hr_multi_message.error_message_exist then
3624:
3625: when hr_multi_message.error_message_exist then
3626: p_status := null;
3627: if g_debug then
3628: hr_utility.set_location('Error :' ||substr(sqlerrm,1,50), 240);
3629: hr_utility.set_location('Leaving:' || l_proc, 300);
3630: end if;
3631: when others then
3632: p_status := null;
3625: when hr_multi_message.error_message_exist then
3626: p_status := null;
3627: if g_debug then
3628: hr_utility.set_location('Error :' ||substr(sqlerrm,1,50), 240);
3629: hr_utility.set_location('Leaving:' || l_proc, 300);
3630: end if;
3631: when others then
3632: p_status := null;
3633:
3665: return l_get_which_rates;
3666: EXCEPTION
3667: WHEN others THEN
3668: if g_debug then
3669: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3670: end if;
3671: return l_get_which_rates;
3672: end get_which_rates;
3673:
3685: and gs_parent_entity_result_id = p_copy_entity_result_id);
3686: l_proc varchar2(72) := g_package||'.get_rates_icon_enabled';
3687: begin
3688: if g_debug then
3689: hr_utility.set_location('Entering '||l_proc, 5);
3690: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3691: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3692: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3693: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3686: l_proc varchar2(72) := g_package||'.get_rates_icon_enabled';
3687: begin
3688: if g_debug then
3689: hr_utility.set_location('Entering '||l_proc, 5);
3690: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3691: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3692: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3693: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3694: end if;
3687: begin
3688: if g_debug then
3689: hr_utility.set_location('Entering '||l_proc, 5);
3690: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3691: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3692: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3693: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3694: end if;
3695:
3688: if g_debug then
3689: hr_utility.set_location('Entering '||l_proc, 5);
3690: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3691: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3692: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3693: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3694: end if;
3695:
3696: if (p_rate_hgrid_node = 'PGM') then
3689: hr_utility.set_location('Entering '||l_proc, 5);
3690: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3691: hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3692: hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3693: hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3694: end if;
3695:
3696: if (p_rate_hgrid_node = 'PGM') then
3697: if (l_get_which_rates = 'POINT') then
3724: l_icon := 'NONE';
3725: end if;
3726:
3727: if g_debug then
3728: hr_utility.set_location('Successfull completion '||l_proc, 5);
3729: end if;
3730: return l_icon;
3731:
3732: EXCEPTION WHEN others THEN
3730: return l_icon;
3731:
3732: EXCEPTION WHEN others THEN
3733: if g_debug then
3734: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3735: end if;
3736: end get_rates_icon_enabled;
3737: --
3738: -- Function to return the annualization factor for frequency codes used in Benefits.
3793: --
3794: Begin
3795: --
3796: if g_debug then
3797: hr_utility.set_location('Entering :'||l_proc, 5);
3798: end if;
3799:
3800: hr_rate_api.create_rate
3801: (p_validate => false
3812: p_rate_id := l_rate_id;
3813: p_ovn := l_ovn;
3814: --
3815: if g_debug then
3816: hr_utility.set_location('Leaving :'||l_proc, 5);
3817: end if;
3818: --
3819: End;
3820: --
3834: --
3835: Begin
3836: --
3837: if g_debug then
3838: hr_utility.set_location('Entering :'||l_proc, 5);
3839: end if;
3840:
3841: hr_rate_api.create_rate
3842: (p_validate => false
3852: p_rate_id := l_rate_id;
3853: p_ovn := l_ovn;
3854: --
3855: if g_debug then
3856: hr_utility.set_location('Leaving :'||l_proc, 5);
3857: end if;
3858: --
3859: End;
3860: --
3947: begin
3948: select action_date into l_effective_date from pqh_copy_entity_txns
3949: where copy_entity_txn_id = p_copy_entity_txn_id;
3950: if g_debug then
3951: hr_utility.set_location('Entering '||l_proc,5);
3952: end if;
3953: if ((p_rate_hgrid_node = 'CPP') and (l_get_which_rates = 'GRADE')) then
3954: l_crrate_icon := pqh_gsp_hr_to_stage.is_crrate_there(p_plip_cer_id => p_copy_entity_result_id,
3955: p_copy_entity_txn_id => p_copy_entity_txn_id,
3962: l_crrate_icon := 'XYZ';
3963: end if;
3964:
3965: if g_debug then
3966: hr_utility.set_location('Successfull Completion '||l_proc,5);
3967: end if;
3968: return l_crrate_icon;
3969:
3970: EXCEPTION WHEN others THEN
3968: return l_crrate_icon;
3969:
3970: EXCEPTION WHEN others THEN
3971: if g_debug then
3972: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3973: end if;
3974: raise;
3975: end;
3976:
4019:
4020: Begin
4021:
4022: if g_debug then
4023: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4024: hr_utility.set_location(' Parameters passed are as follows: ',6);
4025: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4026: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4027: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4020: Begin
4021:
4022: if g_debug then
4023: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4024: hr_utility.set_location(' Parameters passed are as follows: ',6);
4025: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4026: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4027: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4028: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4021:
4022: if g_debug then
4023: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4024: hr_utility.set_location(' Parameters passed are as follows: ',6);
4025: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4026: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4027: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4028: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4029: end if;
4022: if g_debug then
4023: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4024: hr_utility.set_location(' Parameters passed are as follows: ',6);
4025: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4026: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4027: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4028: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4029: end if;
4030:
4023: hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4024: hr_utility.set_location(' Parameters passed are as follows: ',6);
4025: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4026: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4027: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4028: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4029: end if;
4030:
4031: OPEN C_Pgm_rt_Perd;
4024: hr_utility.set_location(' Parameters passed are as follows: ',6);
4025: hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4026: hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4027: hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4028: hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4029: end if;
4030:
4031: OPEN C_Pgm_rt_Perd;
4032: FETCH C_Pgm_rt_Perd into l_ref_perd_cd,l_precision, l_Update_Salary_cd;
4060: OR (l_pay_basis = 'HOURLY' AND l_ref_perd_cd = 'PHR')
4061: OR (l_pay_basis = 'ANNUAL' AND l_ref_perd_cd = 'PYR') THEN
4062:
4063: if g_debug then
4064: hr_utility.set_location('Salary basis frequency and Grade Ladder frequency matches', 15);
4065: hr_utility.set_location('So returning the Grade/Step rate as it is', 25);
4066: end if;
4067:
4068: -- RETURN trunc(p_amount,l_precision); -- Bug 6608606
4061: OR (l_pay_basis = 'ANNUAL' AND l_ref_perd_cd = 'PYR') THEN
4062:
4063: if g_debug then
4064: hr_utility.set_location('Salary basis frequency and Grade Ladder frequency matches', 15);
4065: hr_utility.set_location('So returning the Grade/Step rate as it is', 25);
4066: end if;
4067:
4068: -- RETURN trunc(p_amount,l_precision); -- Bug 6608606
4069: RETURN p_amount;
4144:
4145: step_cnt number;
4146: begin
4147: if g_debug then
4148: hr_utility.set_location('Entering '||l_proc, 5);
4149: end if;
4150: for steps in csr_steps_in_grade loop
4151: step_cnt := steps.cnt;
4152: end loop;
4150: for steps in csr_steps_in_grade loop
4151: step_cnt := steps.cnt;
4152: end loop;
4153: if g_debug then
4154: hr_utility.set_location('Successfull completion '||l_proc, 5);
4155: end if;
4156: return step_cnt;
4157: EXCEPTION WHEN others THEN
4158: if g_debug then
4155: end if;
4156: return step_cnt;
4157: EXCEPTION WHEN others THEN
4158: if g_debug then
4159: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
4160: end if;
4161: end get_num_steps_in_grade;
4162:
4163: Function get_dflt_point_rate (p_copy_entity_txn_id in number,
4182: --
4183: Begin
4184: --
4185: if g_debug then
4186: hr_utility.set_location('Entering :'||l_proc, 5);
4187: end if;
4188: --
4189: l_point_rate := 0;
4190:
4192: Fetch csr_dflt_point_rate into l_point_rate;
4193: Close csr_dflt_point_rate;
4194: --
4195: if g_debug then
4196: hr_utility.set_location('Leaving :'||l_proc, 5);
4197: end if;
4198: --
4199: Return l_point_rate;
4200: --
4275: Begin
4276: --
4277: p_exists_flag := 'N';
4278: if g_debug then
4279: hr_utility.set_location('Entering :'||l_proc, 5);
4280: end if;
4281: --
4282: Open csr_sal_basis_iv;
4283: Fetch csr_sal_basis_iv into l_dummy ;
4286: End if;
4287: Close csr_sal_basis_iv;
4288: --
4289: if g_debug then
4290: hr_utility.set_location('Leaving :'||l_proc, 5);
4291: end if;
4292: --
4293: End;
4294:
4355: --
4356: hr_multi_message.enable_message_list;
4357:
4358: if g_debug then
4359: hr_utility.set_location('Entering '||l_proc,100);
4360: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4361: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4362: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4363: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4356: hr_multi_message.enable_message_list;
4357:
4358: if g_debug then
4359: hr_utility.set_location('Entering '||l_proc,100);
4360: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4361: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4362: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4363: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4364: end if;
4357:
4358: if g_debug then
4359: hr_utility.set_location('Entering '||l_proc,100);
4360: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4361: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4362: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4363: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4364: end if;
4365:
4358: if g_debug then
4359: hr_utility.set_location('Entering '||l_proc,100);
4360: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4361: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4362: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4363: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4364: end if;
4365:
4366: pqh_gsp_stage_to_hr.gsp_data_push(p_copy_entity_txn_id => p_copy_entity_txn_id,
4359: hr_utility.set_location('Entering '||l_proc,100);
4360: hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4361: hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4362: hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4363: hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4364: end if;
4365:
4366: pqh_gsp_stage_to_hr.gsp_data_push(p_copy_entity_txn_id => p_copy_entity_txn_id,
4367: p_effective_date => p_effective_date,
4368: p_business_group_id => p_business_group_id,
4369: p_business_area => p_business_area,
4370: p_datetrack_mode => p_datetrack_mode);
4371: if g_debug then
4372: hr_utility.set_location('Leaving '||l_proc,100);
4373: end if;
4374:
4375: --
4376: --
4485: ,p_segment30 =>segments.segment30
4486: ,p_ccid => l_ccid
4487: ,p_concat_segments_out => p_concatenated_segments
4488: );
4489: hr_utility.set_location('cccid:'||l_ccid, 5);
4490: hr_utility.set_location('concatened segments:'||p_concatenated_segments,15);
4491: END GET_GRADE_NAME;
4492: ---
4493: --------- get_dml_operation -----------
4486: ,p_ccid => l_ccid
4487: ,p_concat_segments_out => p_concatenated_segments
4488: );
4489: hr_utility.set_location('cccid:'||l_ccid, 5);
4490: hr_utility.set_location('concatened segments:'||p_concatenated_segments,15);
4491: END GET_GRADE_NAME;
4492: ---
4493: --------- get_dml_operation -----------
4494: --
4536: -- if grade_id is enterd and grade_ladder is not entered,
4537: -- need to check gsp is implemented in the system.
4538: -- if the gsp is implemented, a warning message will be appear.
4539: --
4540: hr_utility.set_location('Entering chk_no_asg_grd_ldr', 5);
4541: --
4542: if p_asg_grade_id is not null and p_asg_grade_ladder_id is null then
4543: open csr_chk_gsp_in_system;
4544: fetch csr_chk_gsp_in_system into l_exists;
4550: pqh_utility.set_message(800,'HR_289559_GRADE_LADDER_REQUIRE',p_asg_org_id);
4551: pqh_utility.raise_error;
4552: else
4553: --
4554: hr_utility.set_location('GSP not Implemented', 7);
4555: --
4556: close csr_chk_gsp_in_system;
4557: end if;
4558:
4558:
4559: end if;
4560: --
4561: --
4562: hr_utility.set_location('Leaving chk_no_asg_grd_ldr', 10);
4563: --
4564: End chk_no_asg_grd_ldr;
4565:
4566: FUNCTION bus_area_pgm_entity_exist(p_bus_area_cd IN Varchar2,
4605: AND effective_end_date
4606: AND grade_id = p_grade_id;
4607: BEGIN
4608: hr_multi_message.enable_message_list;
4609: hr_utility.set_location ('Inside chk_new_ceiling', 10);
4610: OPEN csr_grade_id;
4611: FETCH csr_grade_id INTO l_grade_id;
4612: CLOSE csr_grade_id;
4613: hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4609: hr_utility.set_location ('Inside chk_new_ceiling', 10);
4610: OPEN csr_grade_id;
4611: FETCH csr_grade_id INTO l_grade_id;
4612: CLOSE csr_grade_id;
4613: hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4614:
4615: IF l_grade_id IS NOT NULL
4616: THEN
4617: hr_utility.set_location ('grade_id found ', 20);
4613: hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4614:
4615: IF l_grade_id IS NOT NULL
4616: THEN
4617: hr_utility.set_location ('grade_id found ', 20);
4618: OPEN csr_gspine_id (l_grade_id);
4619: FETCH csr_gspine_id INTO l_gspine_id;
4620:
4621: IF csr_gspine_id%FOUND
4619: FETCH csr_gspine_id INTO l_gspine_id;
4620:
4621: IF csr_gspine_id%FOUND
4622: THEN
4623: hr_utility.set_location ('gspine_id found ' || l_gspine_id, 20);
4624: per_grade_spines_pkg.chk_low_ceiling (
4625: p_val_start => p_effective_date,
4626: p_val_end => p_effective_date,
4627: p_gspine_id => l_gspine_id,
4632:
4633: -- Call to raise any errors on multi-message list
4634: hr_multi_message.end_validation_set;
4635: -- p_error_msg := l_msg;
4636: hr_utility.set_location ('Leaving chk_new_ceiling', 90);
4637: EXCEPTION
4638: WHEN OTHERS
4639: THEN
4640: fnd_msg_pub.add;
4669:
4670: l_steps_for_point NUMBER;
4671: BEGIN
4672: hr_multi_message.enable_message_list;
4673: hr_utility.set_location ('Inside Proc', 2);
4674: OPEN csr_steps_for_point;
4675: FETCH csr_steps_for_point INTO l_steps_for_point;
4676:
4677: IF (csr_steps_for_point%FOUND)
4675: FETCH csr_steps_for_point INTO l_steps_for_point;
4676:
4677: IF (csr_steps_for_point%FOUND)
4678: THEN
4679: hr_utility.set_location ('Steps Error', 5);
4680: hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4681: hr_utility.raise_error;
4682: END IF;
4683:
4676:
4677: IF (csr_steps_for_point%FOUND)
4678: THEN
4679: hr_utility.set_location ('Steps Error', 5);
4680: hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4681: hr_utility.raise_error;
4682: END IF;
4683:
4684: hr_multi_message.end_validation_set;
4677: IF (csr_steps_for_point%FOUND)
4678: THEN
4679: hr_utility.set_location ('Steps Error', 5);
4680: hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4681: hr_utility.raise_error;
4682: END IF;
4683:
4684: hr_multi_message.end_validation_set;
4685: hr_utility.set_location ('Leaving Proc', 2);
4681: hr_utility.raise_error;
4682: END IF;
4683:
4684: hr_multi_message.end_validation_set;
4685: hr_utility.set_location ('Leaving Proc', 2);
4686: EXCEPTION
4687: WHEN OTHERS
4688: THEN
4689: hr_utility.set_location ('Some errors', 20);
4685: hr_utility.set_location ('Leaving Proc', 2);
4686: EXCEPTION
4687: WHEN OTHERS
4688: THEN
4689: hr_utility.set_location ('Some errors', 20);
4690: fnd_msg_pub.ADD;
4691: END chk_delete_option;
4692:
4693:
4731: l_final_step_id NUMBER;
4732: l_gspine_id NUMBER;
4733: BEGIN
4734: hr_multi_message.enable_message_list;
4735: hr_utility.set_location ('Inside Proc..', 5);
4736:
4737: if p_initial_ceiling_id <> p_final_ceiling_id THEN
4738: OPEN csr_final_step;
4739: FETCH csr_final_step INTO l_final_step;
4740:
4741: IF csr_final_step%FOUND
4742: THEN
4743: l_gspine_id := l_final_step.information255;
4744: hr_utility.set_location (
4745: 'Going to check for New Ceiling with the following..',
4746: 15
4747: );
4748: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4744: hr_utility.set_location (
4745: 'Going to check for New Ceiling with the following..',
4746: 15
4747: );
4748: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4749: hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4750: hr_utility.set_location (
4751: 'gSpine id' || l_gspine_id,
4752: 35
4745: 'Going to check for New Ceiling with the following..',
4746: 15
4747: );
4748: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4749: hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4750: hr_utility.set_location (
4751: 'gSpine id' || l_gspine_id,
4752: 35
4753: );
4746: 15
4747: );
4748: hr_utility.set_location ('Going to check for New Ceiling..', 25);
4749: hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4750: hr_utility.set_location (
4751: 'gSpine id' || l_gspine_id,
4752: 35
4753: );
4754: hr_utility.set_location (
4750: hr_utility.set_location (
4751: 'gSpine id' || l_gspine_id,
4752: 35
4753: );
4754: hr_utility.set_location (
4755: 'New Ceiling Sequence' || l_final_step.information263,
4756: 45
4757: );
4758: if l_gspine_id is not null THEN
4907: p_effective_date => l_effdate);
4908:
4909: fetch c1 into l_exists;
4910: IF c1%found THEN
4911: hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
4912: close c1;
4913: hr_utility.raise_error;
4914: END IF;
4915:
4909: fetch c1 into l_exists;
4910: IF c1%found THEN
4911: hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
4912: close c1;
4913: hr_utility.raise_error;
4914: END IF;
4915:
4916:
4917: /* per_grade_spines_pkg.stb_del_validation (
4991: l_action_date date;
4992: cnt number := 0;
4993: begin
4994: if g_debug then
4995: hr_utility.set_location('Entering '||l_proc,5);
4996: end if;
4997:
4998: open csr_action_dt;
4999: fetch csr_action_dt into l_action_date;
5000: close csr_action_dt;
5001:
5002: if ((p_rate_hgrid_node = 'CPP') and (l_get_which_rates = 'GRADE')) then
5003: if g_debug then
5004: hr_utility.set_location('These are grade rates ',5);
5005: end if;
5006: for each_pnt in grd_rates(l_action_date) loop
5007: dflt_sal_rate := each_pnt.information297;
5008: end loop;
5007: dflt_sal_rate := each_pnt.information297;
5008: end loop;
5009: elsif ((p_rate_hgrid_node = 'COP') and (l_get_which_rates = 'POINT')) then
5010: if g_debug then
5011: hr_utility.set_location('These are point rates ',5);
5012: end if;
5013: for each_pnt_cer_id in point_cer loop
5014: l_point_cer_id := each_pnt_cer_id.information262;
5015: end loop;
5019: end loop;
5020:
5021: elsif ((p_rate_hgrid_node = 'COP') and (l_get_which_rates = 'STEP')) then
5022: if g_debug then
5023: hr_utility.set_location('These are step rates '||l_proc,5);
5024: end if;
5025: for each_pnt_cer_id in point_cer loop
5026: l_point_cer_id := each_pnt_cer_id.information262;
5027: end loop;
5030: dflt_sal_rate := each_pnt.information297;
5031: end loop;
5032: else
5033: if g_debug then
5034: hr_utility.set_location('This node is not valid for dlft salary determination
5035: '||l_proc,5);
5036: end if;
5037:
5038: dflt_sal_rate := null;
5038: dflt_sal_rate := null;
5039:
5040: end if;
5041: if g_debug then
5042: hr_utility.set_location('Successfull Completion '||l_proc,5);
5043: end if;
5044: return dflt_sal_rate;
5045:
5046: EXCEPTION WHEN others THEN
5044: return dflt_sal_rate;
5045:
5046: EXCEPTION WHEN others THEN
5047: if g_debug then
5048: hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
5049: end if;
5050: end get_dflt_salary_rate;
5051:
5052: ---------------------------------------------------------------------------
5107: from ben_pgm_f
5108: where pgm_id = p_pgm_id;
5109: begin
5110: if g_debug then
5111: hr_utility.set_location('Entering chk_from_steps : ',10);
5112: end if;
5113: for rec_grade_id in csr_grade_id
5114: loop
5115: if g_debug then
5112: end if;
5113: for rec_grade_id in csr_grade_id
5114: loop
5115: if g_debug then
5116: hr_utility.set_location('Fetched the Grade Id : '||rec_grade_id.grade_id,20);
5117: end if;
5118: for rec_plan_id in csr_plan_id(rec_grade_id.grade_id)
5119: loop
5120: if g_debug then
5117: end if;
5118: for rec_plan_id in csr_plan_id(rec_grade_id.grade_id)
5119: loop
5120: if g_debug then
5121: hr_utility.set_location('Fetched the Plan Id : '||rec_plan_id.pl_id,30);
5122: end if;
5123: for rec_pgm_id in csr_pgm_id(rec_plan_id.pl_id)
5124: loop
5125: if g_debug then
5122: end if;
5123: for rec_pgm_id in csr_pgm_id(rec_plan_id.pl_id)
5124: loop
5125: if g_debug then
5126: hr_utility.set_location('Fetched the Pgm Id : '||rec_pgm_id.pgm_id,40);
5127: end if;
5128: for rec_use_points IN csr_use_points(rec_pgm_id.pgm_id)
5129: loop
5130: if g_debug then
5127: end if;
5128: for rec_use_points IN csr_use_points(rec_pgm_id.pgm_id)
5129: loop
5130: if g_debug then
5131: hr_utility.set_location('Fetched the Flag : '||rec_use_points.use_prog_points_flag,50);
5132: end if;
5133: if rec_use_points.use_prog_points_flag = 'N' THEN
5134: l_status := 'Y';
5135: if g_debug then
5132: end if;
5133: if rec_use_points.use_prog_points_flag = 'N' THEN
5134: l_status := 'Y';
5135: if g_debug then
5136: hr_utility.set_location('Changing the Status to : '||l_status,60);
5137: end if;
5138: END IF;
5139: end loop;
5140: end loop;
5139: end loop;
5140: end loop;
5141: end loop;
5142: end loop;
5143: hr_utility.set_location('Returning the Status as : '||l_status,90);
5144: Return l_status;
5145: Exception when others then
5146: hr_utility.set_location('Error and returning : '||l_status,90);
5147: return 'N';
5142: end loop;
5143: hr_utility.set_location('Returning the Status as : '||l_status,90);
5144: Return l_status;
5145: Exception when others then
5146: hr_utility.set_location('Error and returning : '||l_status,90);
5147: return 'N';
5148: END chk_from_steps;
5149:
5150: 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)
5244: ELSE
5245: OPEN csr_plan_dtls;
5246: Fetch csr_plan_dtls into l_plan_dtls;
5247: IF csr_plan_dtls%NOTFOUND THEN
5248: hr_utility.set_location('No Plan avlbl. Exiting..',80);
5249: return;
5250: else
5251: hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5252: OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5247: IF csr_plan_dtls%NOTFOUND THEN
5248: hr_utility.set_location('No Plan avlbl. Exiting..',80);
5249: return;
5250: else
5251: hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5252: OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5253: fetch csr_plip_dtls into l_plip_dtls;
5254: IF csr_plip_dtls%NOTFOUND THEN
5255: hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5251: hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5252: OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5253: fetch csr_plip_dtls into l_plip_dtls;
5254: IF csr_plip_dtls%NOTFOUND THEN
5255: hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5256: return;
5257: else
5258: hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5259: OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5254: IF csr_plip_dtls%NOTFOUND THEN
5255: hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5256: return;
5257: else
5258: hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5259: OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5260: fetch csr_scale_dtls into l_scale_dtls;
5261: IF csr_scale_dtls%NOTFOUND THEN
5262: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5258: hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5259: OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5260: fetch csr_scale_dtls into l_scale_dtls;
5261: IF csr_scale_dtls%NOTFOUND THEN
5262: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5263: return;
5264: Else
5265: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5266: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5261: IF csr_scale_dtls%NOTFOUND THEN
5262: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5263: return;
5264: Else
5265: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5266: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5267: if p_short_name = l_scale_dtls.Information98 then
5268: hr_utility.set_location('The names match ..Exiting..',95);
5269: return;
5262: hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5263: return;
5264: Else
5265: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5266: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5267: if p_short_name = l_scale_dtls.Information98 then
5268: hr_utility.set_location('The names match ..Exiting..',95);
5269: return;
5270: else
5264: Else
5265: hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5266: hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5267: if p_short_name = l_scale_dtls.Information98 then
5268: hr_utility.set_location('The names match ..Exiting..',95);
5269: return;
5270: else
5271: hr_utility.set_location('Got some work to do..',55);
5272:
5267: if p_short_name = l_scale_dtls.Information98 then
5268: hr_utility.set_location('The names match ..Exiting..',95);
5269: return;
5270: else
5271: hr_utility.set_location('Got some work to do..',55);
5272:
5273: update ben_copy_entity_results
5274: set information98 = p_short_name
5275: , dml_operation = get_dml_operation(l_scale_dtls.dml_operation)
5350: and copy_entity_txn_id = p_copy_entity_txn_id;
5351: l_plan_old_date date;
5352: begin
5353: if g_debug then
5354: hr_utility.set_location('Entering change_rates_date',10);
5355: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5356: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5357: hr_utility.set_location('Start Date:'||p_start_date,25);
5358: end if;
5351: l_plan_old_date date;
5352: begin
5353: if g_debug then
5354: hr_utility.set_location('Entering change_rates_date',10);
5355: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5356: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5357: hr_utility.set_location('Start Date:'||p_start_date,25);
5358: end if;
5359: Open csr_plan_dtls;
5352: begin
5353: if g_debug then
5354: hr_utility.set_location('Entering change_rates_date',10);
5355: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5356: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5357: hr_utility.set_location('Start Date:'||p_start_date,25);
5358: end if;
5359: Open csr_plan_dtls;
5360: fetch csr_plan_dtls into l_plan_old_date;
5353: if g_debug then
5354: hr_utility.set_location('Entering change_rates_date',10);
5355: hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5356: hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5357: hr_utility.set_location('Start Date:'||p_start_date,25);
5358: end if;
5359: Open csr_plan_dtls;
5360: fetch csr_plan_dtls into l_plan_old_date;
5361: IF csr_plan_dtls%NOTFOUND THEN
5359: Open csr_plan_dtls;
5360: fetch csr_plan_dtls into l_plan_old_date;
5361: IF csr_plan_dtls%NOTFOUND THEN
5362: if g_debug then
5363: hr_utility.set_location('No Plan records exist',85);
5364: hr_utility.set_location('Leaving change_rates_date',90);
5365: end if;
5366: return;
5367: end if;
5360: fetch csr_plan_dtls into l_plan_old_date;
5361: IF csr_plan_dtls%NOTFOUND THEN
5362: if g_debug then
5363: hr_utility.set_location('No Plan records exist',85);
5364: hr_utility.set_location('Leaving change_rates_date',90);
5365: end if;
5366: return;
5367: end if;
5368:
5368:
5369: close csr_plan_dtls;
5370: if l_plan_old_date = p_start_date THEN
5371: if g_debug then
5372: hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5373: hr_utility.set_location('No date Change needed',85);
5374: hr_utility.set_location('Leaving change_rates_date',90);
5375: end if;
5376: return;
5369: close csr_plan_dtls;
5370: if l_plan_old_date = p_start_date THEN
5371: if g_debug then
5372: hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5373: hr_utility.set_location('No date Change needed',85);
5374: hr_utility.set_location('Leaving change_rates_date',90);
5375: end if;
5376: return;
5377: end if;
5370: if l_plan_old_date = p_start_date THEN
5371: if g_debug then
5372: hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5373: hr_utility.set_location('No date Change needed',85);
5374: hr_utility.set_location('Leaving change_rates_date',90);
5375: end if;
5376: return;
5377: end if;
5378: hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5374: hr_utility.set_location('Leaving change_rates_date',90);
5375: end if;
5376: return;
5377: end if;
5378: hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5379:
5380: for i in csr_rate_details(l_plan_old_date) loop
5381: if g_debug then
5382: hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5378: hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5379:
5380: for i in csr_rate_details(l_plan_old_date) loop
5381: if g_debug then
5382: hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5383: end if;
5384:
5385: if i.information3 < p_start_date then
5386: hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5382: hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5383: end if;
5384:
5385: if i.information3 < p_start_date then
5386: hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5387: hr_utility.raise_error;
5388: end if;
5389:
5390: update ben_copy_entity_results
5383: end if;
5384:
5385: if i.information3 < p_start_date then
5386: hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5387: hr_utility.raise_error;
5388: end if;
5389:
5390: update ben_copy_entity_results
5391: set information2 = p_start_date
5390: update ben_copy_entity_results
5391: set information2 = p_start_date
5392: where copy_entity_result_id = i.copy_entity_result_id;
5393: if g_debug then
5394: hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);
5395: end if;
5396: end loop;
5397: if g_debug then
5398: hr_utility.set_location('Leaving change_rates_date',80);
5394: hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);
5395: end if;
5396: end loop;
5397: if g_debug then
5398: hr_utility.set_location('Leaving change_rates_date',80);
5399: end if;
5400:
5401: END change_rates_date;
5402:
5421:
5422: BEGIN
5423:
5424: if g_debug then
5425: hr_utility.set_location('Leaving '||l_proc,10);
5426: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
5427: end if;
5428:
5429: Open csr_currency_code;
5422: BEGIN
5423:
5424: if g_debug then
5425: hr_utility.set_location('Leaving '||l_proc,10);
5426: hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
5427: end if;
5428:
5429: Open csr_currency_code;
5430: Fetch csr_currency_code into l_result;
5430: Fetch csr_currency_code into l_result;
5431: Close csr_currency_code;
5432:
5433: if g_debug then
5434: hr_utility.set_location('l_result '||l_result,25);
5435: end if;
5436: return l_result;
5437:
5438: if g_debug then
5435: end if;
5436: return l_result;
5437:
5438: if g_debug then
5439: hr_utility.set_location('Leaving '||l_proc,30);
5440: end if;
5441:
5442: EXCEPTION
5443: When others THEN
5599: INTO l_dummy;
5600:
5601: IF csr_name%FOUND
5602: THEN
5603: hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_NAME_UNQ');
5604: hr_multi_message.ADD;
5605: END IF;
5606:
5607: CLOSE csr_name;
5614: INTO l_pgm_name;
5615:
5616: IF csr_dflt_grd_ldr%FOUND
5617: THEN
5618: hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_DFLT_ERR');
5619: hr_utility.set_message_token ('LADDER', l_pgm_name);
5620: hr_multi_message.ADD;
5621: END IF;
5622:
5615:
5616: IF csr_dflt_grd_ldr%FOUND
5617: THEN
5618: hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_DFLT_ERR');
5619: hr_utility.set_message_token ('LADDER', l_pgm_name);
5620: hr_multi_message.ADD;
5621: END IF;
5622:
5623: CLOSE csr_dflt_grd_ldr;
5644: AND information_type = 'PQH_GSP_EXTRA_INFO';
5645: l_proc constant varchar2(72):= g_package||'get_gl_ann_factor';
5646: l_gl_ann_factor ben_pgm_extra_info.pgi_information5%TYPE;
5647: BEGIN
5648: hr_utility.set_location('Entering:'||l_proc, 5);
5649: open csr_gl_ann_factor;
5650: FETCH csr_gl_ann_factor INTO l_gl_ann_factor;
5651: CLOSE csr_gl_ann_factor;
5652: return l_gl_ann_factor;
5649: open csr_gl_ann_factor;
5650: FETCH csr_gl_ann_factor INTO l_gl_ann_factor;
5651: CLOSE csr_gl_ann_factor;
5652: return l_gl_ann_factor;
5653: hr_utility.set_location(' Leaving:'||l_proc, 10);
5654:
5655: Exception
5656: WHEN Others THEN
5657: raise;