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 --
10 function get_gsp_plntyp_str_date (p_business_group_id in number
11 ,p_copy_entity_txn_id in number default null )
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
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-----------------------------
46 --
47 function gsp_plan_type_exists (p_business_group_id in number)
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
57 where business_group_id = p_business_group_id
58 and opt_typ_cd ='GSP'
59 and pl_typ_stat_cd ='A'
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 --
69 ---------------------------chk_grade_exist_in_gl-----------------------------
70 --
71
72 FUNCTION chk_grade_exist_in_gl
73 (
74 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_result_id%TYPE
75 )
76 RETURN VARCHAR2 IS
77 /*
78 Author : mvankada
79 Purpose : This function checks whether any Grade(s) attached to Grade Later or not.
80 If atleast one Grade has attached to Grade Ladder then this funtion returns 'Y' else 'N'
81
82 Used in : This function call is used in Grade HGrid page to enable/disable or
83 To throw error Message if No grades are attached to Grade Ladder
84 1) Progression Order Icon
85 2) Continue to Next Task button
86
87 */
88
89 CURSOR csr_grade_in_gl
90 IS
91 Select copy_entity_result_id
92 From ben_copy_entity_results
93 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
94 AND Table_Alias = 'CPP'
95 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
96 AND nvl(Information104,'PPP') <> 'UNLINK';
97
98 l_cer number;
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;
108 Fetch csr_grade_in_gl into l_cer;
109 if csr_grade_in_gl%found then
110 return 'Y';
111 Else
112 return 'N';
113 End If;
114 Close csr_grade_in_gl;
115 EXCEPTION
116 WHEN OTHERS THEN
117 return 'N';
118 End chk_grade_exist_in_gl;
119
120 --
121 ---------------------------remove_grade_from_grdldr-----------------------------
122 --
123
124 Procedure remove_grade_from_grdldr
125 (
126 p_Grade_Result_Id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
127 p_Copy_Entity_Txn_Id IN ben_copy_entity_results.copy_entity_txn_id%TYPE,
128 p_Business_Group_Id IN Number,
129 p_Effective_Date IN Date,
130 p_Rec_Exists OUT NOCOPY Varchar2
131 ) IS
132
133 /*
134 Author : mvankada
135 Purpose :
136 I) This procedure checks whether rec to be removed is in Staging Area Only or in Main Tables
137 p_rec_exists has values :
138 =======================
139 MAIN Main Table
140 STAGE Staging Area
141 II) If the record to be removed is in Main Tables then this procedure also checks whether
142 the grade is attached to the employee or not. If attached then raise the error.
143 */
144
145 l_pgm_id Number;
146 l_grade_id Number;
147 l_assg_id Number;
148 l_dummy Char(1);
149 l_grade_name Ben_Copy_Entity_Results.Information5%Type;
150 l_proc varchar2(72) := g_package||'remove_grade_from_grdldr';
151 l_message_type varchar2(10) := 'W';
152 l_warnings_rec pqh_utility.warnings_rec;
153 l_business_area varchar2(50) := 'PQH_GSP_TASK_LIST';
154 l_crpth_hier_ver number;
155 l_corps_id number;
156 l_grade_crpath_node number;
157
158 -- To Get Pgm_Id of PGM based on Txn_id
159 Cursor csr_pgm_id
160 IS
161 Select grdldr.Information1
162 From Ben_Copy_Entity_Results grdldr
163 Where grdldr.Table_Alias = 'PGM'
164 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
165 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
166 And Information4 = p_Business_Group_Id;
167
168 -- To Get Corps_definition_Id of CPD based on Txn_id
169
170 Cursor csr_corps_id
171 IS
172 Select grdldr.Information1
173 From Ben_Copy_Entity_Results grdldr
174 Where grdldr.Table_Alias = 'CPD'
175 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
176 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
177 And Information4 = p_Business_Group_Id;
178
179
180
181 -- To Get Grade_Id (information253) of CPP based on Result_id of CPP
182 Cursor csr_grade_id
183 IS
184 Select grd.Information253,
185 grd.Information5
186 From Ben_Copy_Entity_Results grd
187 Where grd.Table_Alias = 'CPP'
188 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
189 And grd.Copy_Entity_Result_Id = p_Grade_Result_Id;
190
191
192
193 -- Check Grade and Grade Ladder is attached to an employee
194 Cursor csr_gl_grade_assg_emp (l_pgm_id Number, l_grade_id Number)
195 IS
196 Select assignment_id
197 From per_all_assignments_f assgt
198 Where grade_id = l_grade_id
199 AND Grade_Ladder_Pgm_Id = l_pgm_id
200 AND p_Effective_Date BETWEEN assgt.effective_start_date
201 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
202 AND assgt.business_group_id = p_Business_Group_Id;
203
204
205 -- Check Grade is attached to an employee
206 Cursor csr_grd_assg_emp (l_grade_id Number)
207 IS
208 Select Null
209 From per_all_assignments_f assgt
210 Where grade_id = l_grade_id
211 AND p_Effective_Date BETWEEN assgt.effective_start_date
212 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
213 AND assgt.business_group_id = p_Business_Group_Id;
214
215
216 Cursor csr_default_grdldr
217 IS
218 Select Null
219 From ben_pgm_f
220 Where pgm_id = l_pgm_id
221 And pgm_typ_cd = 'GSP'
222 And Dflt_Pgm_Flag = 'Y'
223 And business_group_id = p_business_group_id
224 And p_Effective_Date Between Effective_Start_date
225 And nvl(Effective_End_Date,hr_general.end_of_time);
226
227 Cursor csr_grade_crpath_node
228 IS
229 select null
230 from per_gen_hierarchy_nodes
231 where hierarchy_version_id = l_crpth_hier_ver
232 and information9 = l_corps_id
233 and information23 = l_grade_id;
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);
243 end if;
244
245 hr_multi_message.enable_message_list;
246
247 -- Get Pgm_Id
248 Open csr_pgm_id;
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
267
268 -- If the Grade Ladder is Default Grade Ladder, if the employee placements exists
269 -- on grade then raise warning message.
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;
288 p_rec_exists := l_grade_name;
289 Else
290 p_rec_exists := 'MAIN';
291 Close csr_grd_assg_emp;
292 End if;
293 Else
294 p_rec_exists := 'MAIN';
295 Close csr_default_grdldr;
296
297
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;
307 Close csr_gl_grade_assg_emp;
308
309 l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
310 if l_business_area = 'PQH_CORPS_TASK_LIST' then
311 -- Get Corps_Id
312 Open csr_corps_id;
313 Fetch csr_corps_id into l_corps_id;
314 Close csr_corps_id;
315
316 if l_corps_id is not null then
317
318 l_crpth_hier_ver := pqh_cpd_hr_to_stage.get_crpth_hier_ver;
319
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;
329 CLOSE csr_grade_crpath_node;
330 end if;
331 end if;
332
333
334 END IF;
335
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
345 p_rec_exists := 'STAGE';
346 fnd_msg_pub.add;
347 End remove_grade_from_grdldr;
348
349 --
350 ---------------------------GET_PGM_TYP-----------------------------
351 --
352 FUNCTION GET_PGM_TYP (p_cpy_enty_txn_id in number)
353 RETURN varchar2 is
354 BEGIN
355 RETURN 'GSP' ;
356 EXCEPTION
357 WHEN others THEN
358
359 return 'GSP' ;
360 END;
361
362 --
363 ---------------------------ENABLE_DISABLE_START_ICON-----------------------------
364 --
365 FUNCTION ENABLE_DISABLE_START_ICON(p_gsp_node in varchar2,
366 p_copy_enty_txn_id in number,
367 p_table_alias in varchar2)
368 RETURN varchar2 is
369 st_icon varchar2(10) := 'N';
370 prev_task varchar2(10);
371 l_table_alias varchar2(50) := 'PQH_GSP_TASK_LIST';
372 --
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;
382
383 if (p_table_alias is not null) then
384 l_table_alias := p_table_alias;
385 end if;
386
387 if p_gsp_node = '1' then
388 st_icon := 'Y';
389 else
390 select nvl(pa.decode_function_name, '99') into prev_task from
391 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
392 where
393 bcer.table_route_id = ptr.table_route_id
394 and ptr.table_route_id = pa.master_table_route_id
395 and ptr.table_alias = l_table_alias
396 and pa.attribute_name = p_gsp_node
397 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
398
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
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 --
414 --
415 ---------------------------GET_STATUS-----------------------------
416 --
417 FUNCTION GET_STATUS(p_gsp_node in varchar2,
418 p_copy_enty_txn_id in number,
419 p_table_alias in varchar2)
420 RETURN varchar2 is
421 st_icon varchar2(10) := 'Y';
422 l_table_alias varchar2(50) := 'PQH_GSP_TASK_LIST';
423 --
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;
433
434 if (p_table_alias is not null) then
435 l_table_alias := p_table_alias;
436 end if;
437
438 if l_table_alias = 'PQH_CORPS_TASK_LIST' Then
439
440 st_icon := pqh_corps_utility.get_cpd_status(p_node_number => p_gsp_node,
441 p_copy_entity_txn_id => p_copy_enty_txn_id);
442 Return st_icon;
443
444 End If;
445 if p_gsp_node = '1' then
446 select bcer.information100 into st_icon from
447 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
448 where
449 bcer.table_route_id = ptr.table_route_id
450 and ptr.table_route_id = pa.master_table_route_id
451 and ptr.table_alias = l_table_alias
452 and pa.attribute_name = p_gsp_node
453 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
454 elsif p_gsp_node = '2' then
455 select bcer.information101 into st_icon from
456 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
457 where
458 bcer.table_route_id = ptr.table_route_id
459 and ptr.table_route_id = pa.master_table_route_id
460 and ptr.table_alias = l_table_alias
461 and pa.attribute_name = p_gsp_node
462 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
463 elsif p_gsp_node = '3' then
464 select bcer.information102 into st_icon from
465 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
466 where
467 bcer.table_route_id = ptr.table_route_id
468 and ptr.table_route_id = pa.master_table_route_id
469 and ptr.table_alias = l_table_alias
470 and pa.attribute_name = p_gsp_node
471 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
472 elsif p_gsp_node = '4'then
473 select bcer.information103 into st_icon from
474 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
475 where
476 bcer.table_route_id = ptr.table_route_id
477 and ptr.table_route_id = pa.master_table_route_id
478 and ptr.table_alias = l_table_alias
479 and pa.attribute_name = p_gsp_node
480 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
481 elsif p_gsp_node = '5' then
482 select bcer.information104 into st_icon from
483 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
484 where
485 bcer.table_route_id = ptr.table_route_id
486 and ptr.table_route_id = pa.master_table_route_id
487 and ptr.table_alias = l_table_alias
488 and pa.attribute_name = p_gsp_node
489 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
490 elsif p_gsp_node = '6' then
491 select bcer.information105 into st_icon from
492 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
493 where
494 bcer.table_route_id = ptr.table_route_id
495 and ptr.table_route_id = pa.master_table_route_id
496 and ptr.table_alias = l_table_alias
497 and pa.attribute_name = p_gsp_node
498 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
499 elsif p_gsp_node = '7' then
500 select bcer.information106 into st_icon from
501 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
502 where
503 bcer.table_route_id = ptr.table_route_id
504 and ptr.table_route_id = pa.master_table_route_id
505 and ptr.table_alias = l_table_alias
506 and pa.attribute_name = p_gsp_node
507 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
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
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
523 --
524 ---------------------------USE_POINT_OR_STEP-----------------------------
525 --
526 FUNCTION USE_POINT_OR_STEP(p_copy_entity_txn_id in number)
527 RETURN varchar2 IS
528 /*
529 Author : mvankada
530 Purpose : This Function checks whether Grade Ladder is using Steps or Points
531 and returns the values STEP/POINT
532 */
533 l_result Varchar2(20);
534 l_proc varchar2(72) := g_package||'USE_POINT_OR_STEP';
535
536 Cursor csr_use_points
537 IS
538 Select Decode(nvl(grdldr.INFORMATION18,'N'),'Y','POINT','STEP') /* INFORMATION18 -> Use Progression Points */
539 FROM Ben_Copy_Entity_Results grdldr
540 WHERE grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
541 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
542 AND grdldr.Table_Alias = 'PGM';
543
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;
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
562 hr_utility.set_location('Leaving '||l_proc,30);
563 end if;
564
565 EXCEPTION
566 When others THEN
567 l_result := 'STEP';
568 return l_result;
569
570 END USE_POINT_OR_STEP;
571
572 --
573 ---------------------------remove_step_from_grade-----------------------------
574 --
575 Procedure remove_step_from_grade
576 (
577 p_step_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
578 p_copy_entity_txn_id IN number,
579 p_effective_date IN Date,
580 p_use_points IN varchar2,
581 p_step_id IN ben_copy_entity_results.information1%TYPE,
582 p_celing_step_flag IN varchar2,
583 p_rec_exists OUT NOCOPY Varchar2
584 ) IS
585
586
587 /*
588 Author : mvankada
589 Purpose :
590 1) This procedure is used to Check whether Record to be removed is in Staging Area Only
591 or in Main Tables
592 p_rec_exists has values :
593 =======================
594 MAIN Main Table
595 STAGE Staging Area
596
597 II) If the Record to be removed is the HR/BEN Tables and if the Step is
598 1) Ceiling Step
599 2) Special Ceiling Step
600 3) Employee Placements on this Step
601 then raise the error.
602
603 */
604
605
606 l_business_area varchar2(50) := 'PQH_GSP_TASK_LIST';
607 l_crpth_hier_ver number;
608 l_corps_id number;
609 l_grade_crpath_node number;
610 l_pgm_id Number;
611 l_grade_id Number;
612 l_proc varchar2(72) := g_package||'remove_step_from_grade';
613
614 -- To Get information1 (Pgm_ID) of PGM, based on TXN_ID
615 Cursor csr_pgm_id
616 IS
617 Select grdldr.information1 -- PGM_ID
618 From Ben_Copy_Entity_Results grdldr
619 Where grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
620 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
621 AND grdldr.Table_Alias = 'PGM';
622
623 -- To Get Corps_definition_Id of CPD based on Txn_id
624
625 Cursor csr_corps_id
626 IS
627 Select grdldr.Information1
628 From Ben_Copy_Entity_Results grdldr
629 Where grdldr.Table_Alias = 'CPD'
630 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
631 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id;
632
633 -- To Get information253 (Grade_id) of CPP, based on Step Result Id
634 Cursor csr_grade_id
635 IS
636 Select grd.information253
637 From Ben_Copy_Entity_Results grd
638 Where grd.Table_Alias = 'CPP'
639 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
640 AND grd.copy_entity_result_id = (Select step.gs_parent_entity_result_id
641 From Ben_Copy_Entity_Results step
642 Where step.Copy_Entity_Result_Id= p_step_result_id
643 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
644 AND step.Table_Alias = 'COP');
645
646 Cursor csr_grade_crpath_node
647 IS
648 select null
649 from per_gen_hierarchy_nodes
650 where hierarchy_version_id = l_crpth_hier_ver
651 and information9 = l_corps_id
652 and information23 = l_grade_id
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);
662 end if;
663
664 hr_multi_message.enable_message_list;
665
666 -- Get Pgm_ID
667 Open csr_pgm_id;
668 Fetch csr_pgm_id into l_pgm_id;
669 Close csr_pgm_id;
670
671 -- Get Grade_ID
672 Open csr_grade_id;
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
682 IF (l_pgm_id IS NOT NULL AND -- Grade Ladder 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
692 1) Special Ceiling Step
693 2) Employee Placements on this step
694 */
695
696 PER_SPINAL_POINT_STEPS_PKG.del_chks_del(p_step_id => p_step_id,
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');
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
711 -- Get Corps_Id
712 Open csr_corps_id;
713 Fetch csr_corps_id into l_corps_id;
714 Close csr_corps_id;
715
716 if l_corps_id is not null then
717
718 l_crpth_hier_ver := pqh_cpd_hr_to_stage.get_crpth_hier_ver;
719
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;
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;
741
742
743 EXCEPTION
744 WHEN others THEN
745 p_rec_exists := 'STAGE';
746 fnd_msg_pub.add;
747
748 END remove_step_from_grade;
749 --
750 ---------------------------CHK_PROFILE_EXISTS-----------------------------
751 --
752
753 FUNCTION CHK_PROFILE_EXISTS
754 ( p_copy_entity_result_id IN Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type,
755 p_copy_entity_txn_id IN Ben_Copy_Entity_Results.Copy_Entity_Txn_Id%Type
756 )
757 RETURN varchar2 IS
758
759 /* Author : mvankada
760 Purpose : This function returns whether GSP Entity have eligibility profiles are not
761 If Yes, returns Y otherwise N
762 */
763
764 Cursor csr_profile_count
765 IS
766 Select '1'
767 From Ben_Copy_Entity_Results
768 Where Table_Alias = 'ELP'
769 And Gs_Parent_Entity_Result_Id = p_Copy_Entity_Result_Id
770 And Copy_Entity_Txn_Id = p_copy_entity_txn_id
771 And Result_type_Cd = 'DISPLAY'
772 And Nvl(Information104,'PPP') <> 'UNLINK' ;
773
774 l_cer Varchar2(10);
775 l_proc varchar2(72) := g_package||'chk_profile_exists';
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;
785 Fetch csr_profile_count into l_cer;
786 if csr_profile_count%FOUND then
787 l_exists := 'Y';
788 else
789 l_exists := 'N';
790 end if;
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
800 return 'N';
801 END CHK_PROFILE_EXISTS;
802
803
804
805 --
806 ---------------------------DISPLAY_ICON-----------------------------
807 --
808
809 FUNCTION DISPLAY_ICON
810 (p_page IN Varchar2,
811 p_Table_Alias IN Ben_Copy_Entity_Results.Table_Alias%Type,
812 p_action IN Varchar2,
813 p_copy_entity_txn_id IN Ben_Copy_Entity_Results.Copy_Entity_Txn_Id%Type,
814 p_copy_entity_result_id IN Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type
815 ) RETURN varchar2
816 IS
817
818 /*
819 Author : mvankada
820 Purpose : This Function is Used in all HGrid Pages to Disply Icons -
821 Add, Update, Remove, Progression Order, Score Icons at Different Levels.
822
823 Return Values :
824 =================
825 E Enable
826 D Disable
827 N NoIcon
828
829 p_page has values
830 =================
831 GRADE_HGRID -- Grades Page
832 STEP_HGRID -- Steps Page
833 PRG_RULE_HGRID -- Progression Rules/ Review And Submit Pages
834
835 p_action has values
836 ===================
837 ADD
838 UPDATE
839 REMOVE
840 PRGORDER
841 SCORE
842 GRDQUOTA
843 CARRERPATH
844 */
845
846 Cursor Csr_steps_exists
847 IS
848 Select Null
849 From Ben_COpy_Entity_Results
850 Where Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
851 And table_alias ='COP'
852 And Copy_Entity_Txn_Id = p_copy_entity_txn_id
853 And result_type_cd = 'DISPLAY'
854 And Nvl(Information104,'PPP') <> 'UNLINK';
855
856
857 l_grade_attached Varchar2(40);
858 l_exists Varchar2(40);
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);
868 end if;
869
870 -- For Grade HGrid Page
871 If p_page = 'GRADE_HGRID' Then
872 If p_Table_Alias = 'PGM' Then
873
874 If p_action = 'ADD' Then
875 return 'E';
876 Elsif p_action = 'PRGORDER' then
877 -- Check The Grade Ladder has Grades or not
878 -- If No Grades are attached to Grade Ladder then
879 -- Disable Progression Order Icon otherwise Enable it.
880 l_grade_attached := pqh_gsp_utility.chk_grade_exist_in_gl( p_copy_entity_txn_id => p_copy_entity_txn_id );
881
882 If l_grade_attached = 'N' Then
883 return 'D';
884 Else
885 return 'E';
886 End If;
887 Else -- # other actions
888 return 'N';
889 End If; -- # end of other actions
890
891 Elsif p_Table_Alias = 'CPP' Then
892 -- mvankada
893 -- Added cotion GRDQUOTA for FRPS Cors Functionality.
894 If ( p_action = 'UPDATE' OR p_action = 'REMOVE' OR p_action = 'GRDQUOTA') Then
895 return 'E';
896 Else
897 return 'N';
898 End If;
899 Else
900 return 'N';
901 End If; -- # Table Name
902
903 End If; -- # Grade HGrid Page
904
905
906 -- For Step HGrid Page
907 If p_page = 'STEP_HGRID'
908 Then
909 If p_Table_Alias = 'CPP' Then
910
911 If ( p_action = 'ADD') Then
912 return 'E';
913 Elsif (p_action = 'UPDATE') Then
914
915 -- If Steps Exists for the Grade then Enable Update Button
916 -- Else Disable It
917 Open Csr_steps_exists ;
918 Fetch Csr_steps_exists into l_dummy;
919 If Csr_steps_exists%Found Then
920 return 'E';
921 Else
922 return 'D';
923 End If;
924 Close Csr_steps_exists;
925 Else
926 return 'N';
927 End If;
928
929 Elsif p_Table_Alias = 'COP' Then
930
931 If p_action = 'REMOVE' Then
932 return 'E';
933 Else
934 return 'N';
935 End If;
936
937
938 Else -- # 'PGM'
939 return 'N';
940 End If;
941
942 End If; -- # Step HGrid Page.
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
952 If p_action = 'ADD' Then
953 return 'E';
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
963 return 'D';
964 end if;
965 Else
966 return 'N';
967
968 End If; -- Action
969 Elsif p_Table_Alias = 'ELP' Then
970 if p_action = 'REMOVE' Then
971 return 'E';
972 elsif p_action = 'SCORE' Then
973 return 'D';
974 else
975 return 'N';
976 end if;
977
978 ElsIf p_Table_Alias = 'CPP' then
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
988 hr_utility.set_location('l_exists :: '||l_exists,60);
989 end if;
990
991 if l_exists = 'Y' then
992 return 'E';
993 else
994 return 'D';
995 end if;
996 else
997 return 'N';
998 end if; -- Action
999 ElsIf p_Table_Alias = 'COP' then
1000 if p_action = 'ADD' Then
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);
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';
1016 else
1017 return 'D';
1018 end if;
1019 -- Added CARRERPATH for FR PS Corsp Functionality.
1020 elsif p_action = 'CARRERPATH' then
1021 return 'E';
1022 else
1023 return 'N';
1024 end if; -- Action
1025
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
1035 if g_debug then
1036 hr_utility.set_location('Leaving'||l_proc,300);
1037 end if;
1038 END DISPLAY_ICON;
1039
1040
1041 --
1042 ---------------------------GET_STEP_NAME-----------------------------
1043 --
1044
1045
1046 FUNCTION GET_STEP_NAME( p_copy_entity_result_id in Number,
1047 p_copy_entity_txn_id in Number)
1048 RETURN Number IS
1049
1050 l_proc varchar2(72) := g_package||'GET_STEP_NAME';
1051 l_step_or_point Varchar2(40);
1052 l_opt_cer_id Number;
1053 l_pay_scale_cer_id Number;
1054 l_grd_cer_id Number;
1055 l_step_no Number;
1056 l_starting_step Number;
1057
1058 Cursor Csr_Cer_Ids
1059 IS
1060 Select oipl.Information262, -- Point Cer Id
1061 oipl.Information259, -- Pay Scale Cer Id
1062 oipl.Gs_Parent_Entity_Result_id -- Grade Cer_id
1063 From Ben_Copy_Entity_Results oipl
1064 Where oipl.Copy_Entity_Result_Id = p_copy_entity_result_id
1065 And oipl.Copy_Entity_Txn_Id = p_copy_entity_Txn_id
1066 AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
1067 And oipl.Table_Alias = 'COP';
1068
1069
1070 Cursor Csr_Step_No (l_opt_cer_id IN Number,l_pay_scale_cer_id IN Number,l_grd_cer_id IN Number,l_cet_id in number)
1071 IS
1072 Select count(*)
1073 From Ben_Copy_Entity_Results opt1,
1074 Ben_Copy_Entity_Results opt2,
1075 Ben_Copy_Entity_Results oipl
1076 Where oipl.Information262 = opt2.copy_entity_result_id
1077 And opt1.Information253 >= opt2.Information253
1078 And opt1.copy_entity_result_id = l_opt_cer_id
1079 And oipl.Information259 = l_pay_scale_cer_id -- Information259 is Pay Scale Cer Id
1080 And oipl.Gs_Parent_Entity_Result_Id = l_grd_cer_id
1081 AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
1082 And Nvl(oipl.Information104,'PPP') <> 'UNLINK'
1083 and oipl.copy_entity_txn_id = l_cet_id
1084 group by opt1.Information263,oipl.Gs_Parent_Entity_Result_Id,opt1.Information98;
1085
1086 Cursor csr_starting_step
1087 IS
1088 select information228
1089 from ben_copy_entity_results
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
1099 Open Csr_Cer_Ids;
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);
1109 Fetch Csr_Step_No into l_step_no;
1110 Close Csr_Step_No;
1111
1112 if l_step_no = 0 then
1113 l_step_no := null;
1114 end if;
1115
1116 Open csr_starting_step;
1117 Fetch csr_starting_step into l_starting_step;
1118 Close csr_starting_step;
1119
1120 return l_step_no+nvl(l_starting_step,1)-1;
1121
1122
1123 Exception
1124 When others then
1125 l_step_no := null;
1126 return l_step_no;
1127 End;
1128
1129
1130
1131 --
1132
1133 ---------------------------GET_STEP_PRG_RULE_HGRID_NAME-----------------------------
1134 --
1135
1136 FUNCTION GET_STEP_PRG_RULE_HGRID_NAME( p_copy_entity_result_id in Number,
1137 p_copy_entity_txn_id in Number,
1138 p_Table_Alias in ben_copy_entity_results.Table_Alias%Type,
1139 p_hgrid in Varchar Default NULL)
1140 RETURN varchar2 IS
1141 /*
1142 Author : mvankada
1143 Purpose : This function is used in Step, Progrogression Rules, Review and Submit HGrid Page to display
1144 Name of Gsp Entity.
1145
1146 p_hgrid Values Meaning
1147 ===========================
1148 RATES_HGRID Rates HGrid
1149
1150 */
1151
1152 -- Grade Ladder : PGM information5 -- Name
1153
1154 Cursor csr_grdldr_name IS
1155 Select grdldr.information5
1156 From Ben_Copy_Entity_Results grdldr
1157 Where grdldr.Copy_Entity_Result_Id = p_copy_entity_result_id
1158 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1159 AND grdldr.Table_Alias= p_Table_Alias;
1160
1161 -- Grade : CPP information5 -- Grade Name, information98 -- PayScale Name
1162 Cursor csr_grd_name IS
1163 Select grd.information5,information98
1164 From Ben_Copy_Entity_Results grd
1165 Where grd.Copy_Entity_Result_Id = p_copy_entity_result_id
1166 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1167 AND grd.Table_Alias = p_Table_Alias;
1168
1169 -- Step : COP information5 -- Step Name, information99 -- Point Name
1170 Cursor csr_step_name IS
1171 Select step.information5 , information99
1172 From Ben_Copy_Entity_Results step
1173 Where step.Copy_Entity_Result_Id = p_copy_entity_result_id
1174 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1175 AND step.Table_Alias = p_Table_Alias;
1176
1177 -- Eligibility Profile : ELP information5 -- Profile Name
1178 Cursor csr_elig_name IS
1179 Select elig.information5
1180 From Ben_Copy_Entity_Results elig
1181 Where elig.Copy_Entity_Result_Id = p_copy_entity_result_id
1182 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1183 AND elig.Table_Alias = p_Table_Alias;
1184
1185 Cursor csr_corps_gross_index IS
1186 Select to_number(opt.information173)
1187 From Ben_Copy_Entity_Results Opt
1188 Where opt.Table_Alias = 'OPT'
1189 AND opt.Copy_Entity_Txn_id = p_copy_entity_txn_id
1190 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1191 And opt.Copy_Entity_Result_Id = ( Select oipl.Information262 -- Point Cer Id
1192 From Ben_Copy_Entity_Results oipl
1193 Where Copy_Entity_Result_Id = p_copy_entity_result_id
1194 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1195 And Copy_Entity_Txn_id = p_copy_entity_txn_id
1196 And Table_Alias = 'COP');
1197
1198 Cursor csr_table_name IS
1199 Select Substr(Display_name,1,50)
1200 From Pqh_table_Route
1201 Where Table_Alias = p_Table_Alias;
1202
1203 l_proc varchar2(72) := g_package||'GET_STEP_PRG_RULE_HGRID_NAME';
1204 l_step_or_point Varchar2(40);
1205 l_name Varchar2(4000);
1206 l_name1 Varchar2(4000);
1207 l_step_name Varchar2(4000);
1208
1209
1210 l_gross_index Number;
1211 l_increased_index Number;
1212 l_bus_area Varchar2(240);
1213 l_corps_step_name Varchar2(4000);
1214 l_table_name Varchar2(4000);
1215
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;
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;
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;
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
1250 return l_name;
1251 end if;
1252
1253 elsif (p_Table_Alias = 'COP') then
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);
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);
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
1276 Open csr_corps_gross_index;
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);
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;
1294
1295 if l_gross_index IS NOT NULL Then
1296 l_corps_step_name := l_corps_step_name || ' | IB ' || l_gross_index;
1297 end if;
1298
1299 if l_increased_index IS NOT NULL Then
1300 l_corps_step_name := l_corps_step_name || ' | INM ' || l_increased_index;
1301 end if;
1302 l_corps_step_name :=l_corps_step_name || ' ) ';
1303 return l_corps_step_name;
1304 end if;
1305
1306 Else
1307
1308 -- For GSP
1309
1310 if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL)) then
1311 return l_step_name || ' (' ||l_name1|| ')';
1312 else
1313 return l_name1;
1314 end if;
1315 End if;
1316
1317
1318 elsif (p_Table_Alias = 'ELP') then
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;
1328 fetch csr_table_name into l_table_name;
1329 close csr_table_name;
1330 if l_table_name IS NOT NULL then
1331 return l_table_name;
1332 else
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 --
1342 ---------------------------CHK_GRD_DETAILS-----------------------------
1343 --
1344
1345 PROCEDURE CHK_GRD_DETAILS
1346 (
1347 p_name IN per_grades.name%TYPE,
1348 p_short_name IN per_grades.short_name%TYPE,
1349 p_business_group_id IN per_grades.business_group_id%TYPE,
1350 p_grade_id IN per_grades.grade_id%TYPE default NULL,
1351 p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE default NULL,
1352 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE,
1353 p_status OUT NOCOPY VARCHAR
1354 )
1355 IS
1356
1357 CURSOR csr_grade_name
1358 IS
1359 Select null
1360 From Per_Grades
1361 Where name = p_name
1362 And business_group_id = p_business_group_id
1363 And grade_id <> nvl(p_grade_id,-1)
1364 Union ALL
1365 Select null
1366 From ben_copy_entity_results
1367 Where
1368 information5 = p_name
1369 And information4 = p_business_group_id
1370 And table_alias = 'PLN'
1371 And result_type_cd = 'DISPLAY'
1372 And copy_entity_result_id <> nvl(p_copy_entity_result_id,-1)
1373 And copy_entity_txn_id = p_copy_entity_txn_id;
1374
1375 CURSOR csr_grade_short_name
1376 IS
1377 Select null
1378 From per_grades
1379 Where short_name = p_short_name
1380 And business_group_id = p_business_group_id
1381 And grade_id <> nvl(p_grade_id,-1)
1382 Union ALL
1383 Select null
1384 From ben_copy_entity_results
1385 Where information102 = p_short_name
1386 And information4 = p_business_group_id
1387 And table_alias='PLN'
1388 And result_type_cd = 'DISPLAY'
1389 And copy_entity_result_id <> nvl(p_copy_entity_result_id,-1)
1390 And copy_entity_txn_id=p_copy_entity_txn_id;
1391
1392 l_grade_name per_grades.name%TYPE;
1393 l_short_name per_grades.short_name%TYPE;
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);
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;
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;
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';
1433 fnd_msg_pub.add;
1434 END CHK_GRD_DETAILS;
1435
1436
1437 --
1438 ---------------------------get_standard_rate-----------------------------
1439 --
1440
1441 Function get_standard_rate(p_copy_entity_result_id in number,
1442 p_effective_date in date)
1443 RETURN number is
1444 --
1445 Cursor csr_std_rate is
1446 Select information98
1447 From ben_copy_entity_results std
1448 Where std.gs_parent_entity_result_id = p_copy_entity_result_id
1449 And std.table_alias = 'ABR'
1450 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1451 AND p_effective_date between information2 and nvl(information3,to_date('31/12/4712','dd/mm/RRRR'));
1452 --
1453 l_std_rate number(38,15) := 0;
1454 --
1455 Begin
1456 --
1457 Open csr_std_rate;
1458 Fetch csr_std_rate into l_std_rate;
1459 Close csr_std_rate;
1460 --
1461 Return l_std_rate;
1462 --
1463 End get_standard_rate;
1464
1465 --
1466 ---------------------------delete_transaction-----------------------------
1467 --
1468 procedure delete_transaction
1469 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE) IS
1470
1471
1472 begin
1473 del_gl_details_from_stage(p_pqh_copy_entity_txn_id);
1474 delete from pqh_copy_entity_txns where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
1475 end delete_transaction;
1476
1477 --
1478 ---------------------------del_gl_details_from_stage-----------------------------
1479 --
1480 procedure del_gl_details_from_stage
1481 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE) IS
1482
1483 begin
1484 delete from pqh_copy_entity_attribs where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
1485 delete from ben_copy_entity_results where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
1486 end del_gl_details_from_stage;
1487
1488 --
1489 ---------------------------enddate_grade_ladder-----------------------------
1490 --
1491 procedure enddate_grade_ladder
1492 (p_ben_pgm_id IN ben_pgm_f.pgm_id%TYPE,
1493 p_effective_date_in IN ben_pgm_f.effective_start_date%TYPE) IS
1494
1495 Cursor pgm is
1496 select pgm_id, object_version_number from ben_pgm_f where pgm_id = p_ben_pgm_id and effective_end_date = hr_general.end_of_time;
1497
1498 Cursor c2 is
1499 select copy_entity_txn_id from pqh_copy_entity_txns where copy_entity_txn_id in
1500 (select copy_entity_txn_id from ben_copy_entity_results
1501 where information1 = p_ben_pgm_id and table_alias = 'PGM' and information_category = 'GRADE_LADDER'
1502 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY')
1503 and status = 'SFL';
1504
1505 l_effective_start_date date;
1506 l_effective_end_date date;
1507
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
1517 for each_rec in pgm loop
1518 ben_program_api.delete_Program
1519 (p_pgm_id => p_ben_pgm_id
1520 ,p_effective_start_date => l_effective_start_date
1521 ,p_effective_end_date => l_effective_end_date
1522 ,p_object_version_number => each_rec.object_version_number
1523 ,p_effective_date => p_effective_date_in
1524 ,p_datetrack_mode => 'DELETE'
1525 );
1526 end loop;
1527
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);
1537 end if;
1538 raise;
1539 end enddate_grade_ladder;
1540
1541 --
1542 ---------------------------Get_Step_Dtls-----------------------------
1543 --
1544 /* This function is used to return the Current Step Id and Step Name */
1545 /* pass P_Id_name as I if Step Id is required. Pass 'N' if Stap_name is required */
1546
1547 Function Get_Step_Dtls
1548 (P_Entity_id In Number,
1549 P_Effective_Date In Date,
1550 P_Id_name In Varchar2,
1551 P_Curr_Prop In Varchar2)
1552
1553 RETURN Number Is
1554
1555 Cursor Step is
1556 Select Steps.Step_Id, Steps.Spinal_point_id, Steps.Grade_spine_id
1557 From Per_Spinal_Point_Placements_F Plcmt,
1558 Per_Spinal_Point_Steps_F Steps
1559 Where Plcmt.ASSIGNMENT_ID = P_Entity_id
1560 and P_Effective_Date
1561 Between Plcmt.Effective_Start_Date and Plcmt.Effective_End_Date
1562 and Plcmt.Step_id = Steps.Step_Id
1563 and P_Effective_Date
1564 Between Steps.Effective_Start_Date and Steps.Effective_End_Date;
1565
1566 Cursor PropStep Is
1567 Select Step.Step_id, Step.Spinal_point_id, Step.Grade_spine_id
1568 From Ben_Oipl_F Oipl,
1569 Ben_pl_F Pl,
1570 Ben_Opt_F Opt,
1571 Per_Spinal_points point,
1572 Per_Grade_Spines_f GSpine,
1573 Per_Spinal_point_Steps_F Step
1574 Where Oipl.Oipl_id = P_Entity_Id
1575 and P_Effective_Date
1576 Between OiPl.Effective_Start_Date and OiPl.Effective_End_Date
1577 and Pl.Pl_id = Oipl.Pl_Id
1578 and P_Effective_Date
1579 Between Pl.Effective_Start_Date and Pl.Effective_End_Date
1580 and Oipl.Opt_id = Opt.Opt_id
1581 and P_Effective_Date
1582 Between Opt.Effective_Start_Date and Opt.Effective_End_Date
1583 and Point.Spinal_Point_id = Opt.Mapping_Table_Pk_Id
1584 and Pl.Mapping_Table_Pk_Id = Gspine.Grade_Id
1585 and P_Effective_Date
1586 Between Gspine.Effective_Start_Date and Gspine.Effective_End_Date
1587 and Step.Grade_Spine_Id = Gspine.Grade_Spine_Id
1588 and P_Effective_Date
1589 Between Step.Effective_Start_Date and Step.Effective_End_Date
1590 and Step.SPINAL_POINT_ID = Point.SPINAL_POINT_ID;
1591
1592 l_Point_id Per_Spinal_POint_Steps_f.Spinal_Point_Id%TYPE;
1593 l_Parent_Spine_id Per_Spinal_Point_Placements_F.Parent_Spine_Id%TYPE;
1594 l_Step_Id Per_Spinal_POint_Steps_F.Step_Id%TYPE := NULL;
1595 l_Step_name Number(10) := NULL;
1596 begin
1597
1598 If P_Curr_Prop = 'CURR' then
1599 Open Step;
1600 Fetch Step into l_Step_Id , L_Point_Id, l_Parent_Spine_id;
1601 Close step;
1602 ElsIf P_Curr_Prop = 'PROP' and P_Entity_Id is NOT NULL then
1603 Open PropStep;
1604 Fetch PropStep into l_Step_Id , L_Point_Id, l_Parent_Spine_id;
1605 Close PropStep;
1606 End If;
1607
1608 If P_Id_Name = 'I' Then
1609 Return l_Step_Id;
1610 Else
1611 If l_point_id is Not NULL and l_Parent_Spine_Id is not NULL Then
1612 per_spinal_point_steps_pkg.pop_flds(l_Step_name,
1613 P_Effective_Date,
1614 l_point_id,
1615 l_Parent_Spine_Id);
1616
1617 End If;
1618 Return l_Step_Name;
1619 End If;
1620
1621 End Get_Step_Dtls;
1622
1623 --
1624 --
1625 ---------------------------Get_Cur_Sal-----------------------------
1626 --
1627
1628 Function Get_Cur_Sal
1629 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
1630 P_Effective_Date In Date)
1631
1632 Return Number Is
1633
1634 L_Cur_Sal Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
1635 l_input_value_id pay_input_values_f.Input_Value_id%TYPE;
1636 L_FREQUENCY per_time_period_types.period_type%TYPE;
1637 L_ANNUAL_SALARY Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
1638 L_PAY_BASIS Varchar2(5);
1639 L_REASON_CD Per_pay_Proposals.proposal_reason%TYPE;
1640 l_pay_basis_frequency Per_pay_bases.pay_basis%TYPE;
1641 L_CURRENCY Ben_Pgm_F.Pgm_Uom%TYPE;
1642 L_STATUS Number(10);
1643
1644 Cursor Sal is
1645 Select pev.screen_entry_value
1646 From pay_element_entries_f pee,
1647 pay_input_values_f piv,
1648 pay_element_entry_values_f pev
1649 Where pee.Assignment_id = P_Assignment_id
1650 and P_Effective_Date
1651 between pee.Effective_Start_Date and pee.Effective_End_Date
1652 and Piv.Input_Value_id = l_Input_Value_id
1653 and P_Effective_Date
1654 Between Piv.Effective_Start_Date and Piv.Effective_End_Date
1655 and pev.ELEMENT_ENTRY_ID = Pee.ELEMENT_ENTRY_ID
1656 and Piv.INPUT_VALUE_ID = Pev.INPUT_VALUE_ID
1657 and P_Effective_Date
1658 Between Pev.Effective_Start_Date and Pev.Effective_End_Date;
1659
1660 Cursor Pay_Bases_Element is
1661 Select input_value_id
1662 From Per_Pay_Bases ppb,
1663 Per_All_Assignments_f paf
1664 Where paf.Assignment_Id = p_Assignment_Id
1665 and p_Effective_Date
1666 Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1667 and paf.pay_basis_id = ppb.pay_basis_id;
1668
1669 Cursor GrdLdr_Element is
1670 Select DFLT_INPUT_VALUE_ID
1671 from Ben_Pgm_f pgm,
1672 Per_All_Assignments_f paf
1673 Where paf.Assignment_Id = p_Assignment_Id
1674 and p_Effective_Date
1675 Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1676 and paf.GRADE_LADDER_PGM_ID = pgm.pgm_id
1677 and p_Effective_Date
1678 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
1679
1680 Begin
1681
1682 /* Commenting the following Cursor Calls as the Procedure below will return the required amount */
1683
1684 /* Open Pay_Bases_Element;
1685 Fetch Pay_Bases_Element into l_input_Value_id;
1686 Close Pay_Bases_Element;
1687
1688 If l_input_Value_id is NULL Then
1689 Open GrdLdr_Element;
1690 Fetch GrdLdr_Element into l_input_Value_id;
1691 Close GrdLdr_Element;
1692 End If;
1693
1694 if l_Input_Value_id is Not NULL Then
1695 Open Sal;
1696 Fetch Sal into L_Cur_Sal;
1697 Close Sal;
1698 Else
1699 l_Cur_Sal := 0;
1700 End If;
1701
1702 Return L_Cur_Sal; */
1703
1704 /* the following Procedure will return The Annual Salary */
1705 pqh_employee_salary.GET_EMPLOYEE_SALARY
1706 (P_ASSIGNMENT_ID => p_Assignment_Id
1707 ,P_EFFECTIVE_DATE => p_Effective_Date
1708 ,P_SALARY => L_Cur_Sal
1709 ,P_FREQUENCY => L_FREQUENCY
1710 ,P_ANNUAL_SALARY => L_ANNUAL_SALARY
1711 ,P_PAY_BASIS => L_PAY_BASIS
1712 ,P_REASON_CD => L_REASON_CD
1713 ,P_CURRENCY => L_CURRENCY
1714 ,P_STATUS => L_STATUS
1715 ,p_pay_basis_frequency => l_pay_basis_frequency);
1716
1717
1718 Return L_ANNUAL_SALARY;
1719
1720 End Get_Cur_Sal;
1721
1722 --
1723 ---------------------------Get_CAGR_Name-----------------------------
1724 --
1725
1726 Function Get_CAGR_Name
1727 (P_CAGR_Id IN Per_Collective_Agreements.Collective_Agreement_ID%TYPE)
1728
1729 Return Varchar2 Is
1730 L_Cagr_name Per_Collective_Agreements.Name%TYPE;
1731
1732 Cursor CAGR is
1733 Select Name
1734 from Per_Collective_Agreements
1735 Where Collective_Agreement_Id = P_CAGR_Id;
1736
1737 Begin
1738
1739 Open CAGR;
1740 Fetch CAGR into l_Cagr_name;
1741 Close Cagr;
1742
1743 Return l_Cagr_Name;
1744
1745 End Get_CAGR_Name;
1746 --
1747 ---------------------------gen_txn_display_name-----------------------------
1748 --
1749
1750 Function gen_txn_display_name
1751 (p_program_name IN pqh_copy_entity_txns.display_name%TYPE,
1752 p_mode IN varchar2)
1753 Return Varchar2 is
1754 l_timestamp varchar2(30) := fnd_date.date_to_canonical(sysdate);
1755 l_display_name varchar2(300);
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
1765 --
1766 --If Mode is null then consider it to be a create transaction
1767 --
1768 if p_mode is null then
1769 l_mode := 'C';
1770 end if;
1771
1772 --
1773 -- No need for extra space at the end of generated name if p_display_name is null.
1774 --
1775 if p_program_name is not null then
1776 l_display_name := l_mode||':'||l_timestamp||':'||substr(p_program_name, 1, 78);
1777 else
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
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 --
1793 ---------------------------get_grade_ladder_name_from_txn-----------------------------
1794 --
1795 Function get_grade_ladder_name_from_txn
1796 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE)
1797 Return Varchar2 is
1798 cursor c1 is
1799 select information5 grade_ladder_name from ben_copy_entity_results
1800 where copy_entity_txn_id = p_pqh_copy_entity_txn_id
1801 and table_alias = 'PGM'
1802 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1803 and information_category = 'GRADE_LADDER';
1804 l_grade_ladder_name varchar2(240);
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
1823 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1824 end if;
1825 end;
1826
1827 --
1828 ---------------------------chk_default_ladder_exist-----------------------------
1829 --
1830
1831 Procedure chk_default_ladder_exist
1832 ( p_pgm_id in number,
1833 p_business_group_id in number,
1834 p_effective_date in Date) IS
1835 /* Author : mvanakda
1836 Purpose : This procedure checks whether a Default Grade Ladder Exist
1837 for a business group or not.
1838 */
1839
1840 l_proc varchar2(72) := g_package|| 'chk_default_ladder_exist';
1841 l_name Ben_Pgm_F.Name%Type;
1842 l_dummy Char(1);
1843
1844 Cursor csr_curr_grdldr IS
1845 Select Name
1846 From ben_pgm_f
1847 Where pgm_id = p_pgm_id
1848 And business_group_id = p_business_group_id
1849 And effective_end_date>= p_effective_date
1850 And pgm_typ_cd = 'GSP'
1851 And Dflt_Pgm_Flag = 'Y';
1852
1853
1854
1855 Cursor csr_default_chk IS
1856 Select Null
1857 From ben_pgm_f
1858 Where pgm_id <> nvl(p_pgm_id,-1)
1859 And business_group_id = p_business_group_id
1860 And p_effective_date Between Effective_Start_Date
1861 And nvl(Effective_End_Date, hr_general.end_of_time)
1862 And pgm_typ_cd = 'GSP'
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 --
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
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
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;
1893 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
1903 --
1904 ---------------------------chk_add_steps_in_all_grades-----------------------------
1905 --
1906
1907 Procedure chk_add_steps_in_all_grades
1908 ( p_copy_entity_txn_id in number,
1909 p_business_group_id in number) IS
1910
1911 /* Author : mvanakda
1912 Purpose : This procedure checks if steps have been added to only some steps
1913 in a grade and warns the user that the setup cannot be saved unless steps
1914 are added to all the Grades.
1915 */
1916
1917 l_proc varchar2(72) := g_package|| 'chk_add_steps_in_all_grades';
1918 l_copy_entity_result_id Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type;
1919 l_grade_name Ben_Copy_Entity_Results.Information5%type;
1920 l_dummy Varchar2(10);
1921 l_no_step_grades Varchar2(2000) := null;
1922 l_no_ceil_grades Varchar2(2000) := null;
1923 l_found1 Boolean := FALSE;
1924 l_found2 Boolean := FALSE;
1925 l_steps_exists varchar2(10);
1926
1927
1928 Cursor csr_steps_exists
1929 IS
1930 Select Null
1931 FROM BEN_COPY_ENTITY_RESULTS step
1932 WHERE step.copy_entity_txn_id = p_copy_entity_txn_id
1933 AND step.TABLE_ALIAS ='COP'
1934 And nvl(step.INFORMATION104,'PPP') <> 'UNLINK'
1935 AND step.result_type_cd = 'DISPLAY';
1936
1937
1938 Cursor csr_grades IS
1939 Select grd.Copy_Entity_Result_Id, grd.Information5
1940 From Ben_Copy_Entity_Results grd
1941 Where grd.Copy_Entity_txn_Id = p_copy_entity_txn_id
1942 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1943 And nvl(grd.Information104,'PPP') <> 'UNLINK'
1944 And grd.Table_Alias = 'CPP'
1945 And grd.Information4 = p_business_group_id;
1946
1947
1948 Cursor csr_steps(p_copy_entity_result_id in number) IS
1949 Select Null
1950 From Ben_Copy_Entity_Results step
1951 Where step.Copy_Entity_txn_Id = p_copy_entity_txn_id
1952 And step.Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
1953 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1954 And step.Table_Alias = 'COP'
1955 And nvl(step.Information104,'PPP') <> 'UNLINK'
1956 And step.Information4 = p_business_group_id;
1957
1958
1959 Cursor csr_celing_step(p_copy_entity_result_id in number) IS
1960 Select Null
1961 From Ben_Copy_Entity_Results ceiling
1962 Where ceiling.Copy_Entity_txn_Id = p_copy_entity_txn_id
1963 And ceiling.Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
1964 And ceiling.Table_Alias = 'COP'
1965 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1966 And nvl(ceiling.Information104,'PPP') <> 'UNLINK'
1967 /*Information98 is used to store Ceiling Step Flag */
1968 And ceiling.Information98 = 'Y'
1969 And ceiling.Information4 = p_business_group_id;
1970
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 --
1980 -- Check Steps Exists for a Transaction or not
1981 Open csr_steps_exists;
1982 Fetch csr_steps_exists into l_steps_exists;
1983 If csr_steps_exists%Found Then
1984 For l_rec in csr_grades
1985 Loop
1986 Open csr_steps(l_rec.copy_entity_result_id);
1987 Fetch csr_steps into l_dummy;
1988 If csr_steps%notfound then
1989 l_no_step_grades := l_no_step_grades || l_rec.Information5 ||' , ';
1990 l_found1 := TRUE;
1991 End If;
1992 Close csr_steps;
1993
1994
1995 Open csr_celing_step(l_rec.copy_entity_result_id);
1996 Fetch csr_celing_step into l_dummy;
1997 If csr_celing_step%notfound then
1998 l_no_ceil_grades := l_no_ceil_grades || l_rec.Information5 ||' , ';
1999 l_found2 := TRUE;
2000 End If;
2001 Close csr_celing_step;
2002
2003 End Loop;
2004 End if;
2005 Close 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);
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
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
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;
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 --
2043 ---------------------------chk_valid_grd_in_grdldr-----------------------------
2044 --
2045 Procedure chk_valid_grd_in_grdldr
2046 ( p_copy_entity_txn_id in number,
2047 p_effective_date in date,
2048 p_business_group_id in Number) IS
2049
2050 /* Author : mvanakda
2051 Purpose : This procedure used to check if grades are valid as of the
2052 grade ladder effective date
2053 */
2054
2055 l_proc varchar2(72) := g_package|| 'chk_valid_grd_in_grdldr';
2056 l_dummy Ben_Copy_Entity_Results.Information5%Type;
2057 l_grades Varchar2(2000);
2058 l_found Boolean := FALSE;
2059
2060 -- This cursor checks the newly created grades
2061 /* Information5 : Grade Name
2062 Information306 : Grade Start Date
2063 Information307 : Grade End Date
2064 */
2065 -- Bug : 3161418
2066
2067 Cursor csr_grades IS
2068 Select grd.Information5 ,
2069 grd.Information306,
2070 grd.Information307
2071 From Ben_Copy_Entity_Results grd
2072 Where grd.Copy_Entity_txn_Id = p_copy_entity_txn_id
2073 AND grd.Table_Alias = 'CPP'
2074 AND nvl(grd.Information104,'PPP') <> 'UNLINK'
2075 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2076 AND p_effective_date Not BETWEEN grd.Information306
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
2086 For l_rec in csr_grades
2087 Loop
2088 l_grades := l_grades || l_rec.Information5 ||' , ';
2089 l_found := TRUE;
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
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
2107 hr_utility.set_location('Leaving:'||l_proc, 15);
2108 end if;
2109 End chk_valid_grd_in_grdldr;
2110
2111 --
2112 ---------------------------chk_inactivate_grdldr-----------------------------
2113 --
2114
2115 Procedure chk_inactivate_grdldr
2116 (p_pgm_id in Number,
2117 p_effective_date in Date,
2118 p_business_group_id in Number,
2119 p_activate in Varchar) IS
2120
2121
2122 /* Author : mvanakda
2123 Purpose : This procedure used to check if a grade ladder can be
2124 inactivated or not
2125 */
2126
2127 l_proc Varchar2(72) := g_package|| 'chk_inactivate_grdldr';
2128 l_old_status BEN_PGM_F.PGM_STAT_CD%Type;
2129 l_dummy Char(1);
2130
2131
2132 Cursor csr_active_gl IS
2133 Select null
2134 From Per_All_Assignments_F
2135 Where Grade_Ladder_Pgm_Id = p_pgm_id
2136 And p_effective_date between effective_start_date
2137 And nvl(effective_end_date, hr_general.end_of_time)
2138 And Business_Group_Id = p_business_group_id;
2139
2140 Cursor csr_pgm_status
2141 IS
2142 Select pgm.PGM_STAT_CD -- Activate A , Inactivate I
2143 From BEN_PGM_F pgm
2144 Where pgm.Pgm_Id = p_pgm_id
2145 And pgm.Business_Group_Id = p_business_group_id
2146 And p_effective_date between pgm.effective_start_date
2147 And nvl(pgm.effective_end_date, hr_general.end_of_time);
2148
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;
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;
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
2175 -- 2) Emp Assignments exists on this PGM And
2176 -- 3) On Review And Submit Page changed status from Active to Inactive
2177
2178 if (l_old_status = 'A' And p_activate = 'I') then
2179
2180 Open csr_active_gl;
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
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
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;
2204 End chk_inactivate_grdldr;
2205
2206 --
2207 ---------------------------Get_Emp_Los-----------------------------
2208 --
2209
2210 Function Get_Emp_Los
2211 (P_Person_id In Per_All_PEOPLE_F.Person_Id%TYPE,
2212 P_Effective_Date In Date)
2213 Return Number is
2214 /* Venkatesh :- The following function is used to determine the Length of Service
2215 for a given Person. It is used in Approval UI */
2216
2217 Cursor LOS is
2218 Select Months_Between(P_Effective_Date,Start_Date) / 12
2219 from Per_All_people_F
2220 Where Person_id = p_Person_Id
2221 and P_Effective_Date
2222 Between Effective_Start_Date and Effective_End_Date;
2223
2224 L_Los Number := 0;
2225 begin
2226
2227 If P_Person_id is Not NULL and P_Effective_Date is Not NUll Then
2228 open Los;
2229 Fetch Los into L_Los;
2230 Close los;
2231 End If;
2232 Return l_los;
2233
2234 End Get_Emp_Los;
2235
2236 Function Get_Currency
2237 (P_Corrency_Code In Fnd_Currencies_Vl.Currency_Code%TYPE)
2238
2239 Return Varchar2 is
2240
2241 Cursor Currency is
2242 Select Name
2243 from Fnd_Currencies_Vl
2244 Where Currency_Code = P_Corrency_Code
2245 and Enabled_Flag = 'Y';
2246
2247 P_Currency_name Fnd_Currencies_Vl.Name%TYPE := NULL;
2248 begin
2249
2250 If P_Corrency_Code is Not Null then
2251 open Currency;
2252 Fetch Currency into P_Currency_name;
2253 Close Currency;
2254 End If;
2255
2256 Return P_Currency_name;
2257 End;
2258
2259 Function Get_SpinalPoint_Name
2260 (p_Point_id IN per_spinal_points.Spinal_Point_Id%TYPE)
2261
2262 Return Varchar2 is
2263
2264 Cursor POints is
2265 Select spinal_point
2266 from per_spinal_points
2267 Where Spinal_point_id = p_Point_id;
2268
2269 P_Spinal_Point_name per_spinal_points.Spinal_Point%TYPE := NULL;
2270 begin
2271
2272 If p_Point_id is Not Null then
2273 open POints;
2274 Fetch POints into P_Spinal_Point_name;
2275 Close POints;
2276 End If;
2277
2278 Return P_Spinal_Point_name;
2279 End;
2280
2281
2282 --
2283 ---------------------------update_or_delete_grade-----------------------------
2284 --
2285
2286 Procedure update_or_delete_grade
2287 ( p_copy_entity_txn_id in number,
2288 p_grade_result_id in number,
2289 p_effective_date in Date) IS
2290 /* Author : mvankada
2291 Purpose : This procedure Update/Delete the grade record
2292 */
2293
2294 -- To Get Pgm_Id of PGM based on Txn_id
2295 Cursor csr_pgm_id
2296 IS
2297 Select grdldr.Information1
2298 From Ben_Copy_Entity_Results grdldr
2299 Where grdldr.Table_Alias = 'PGM'
2300 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2301 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id;
2302
2303 -- To Get Grade_Id (information253) of CPP based on Result_id of CPP
2304 Cursor csr_grade_id
2305 IS
2306 Select grd.Information253
2307 From Ben_Copy_Entity_Results grd
2308 Where grd.Table_Alias = 'CPP'
2309 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2310 And grd.Copy_Entity_Result_Id = p_Grade_Result_Id;
2311
2312 -- Get Plan Record
2313 Cursor csr_plan_result_id
2314 IS
2315 Select pln.Copy_Entity_Result_Id
2316 From Ben_Copy_Entity_Results pln
2317 Where pln.Gs_Mirror_Src_Entity_Result_Id = p_grade_result_id
2318 And pln.Copy_Entity_Txn_Id = p_copy_entity_txn_id
2319 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2320 And pln.Table_Alias = 'PLN';
2321
2322
2323 Cursor csr_ovn (l_result_id in Number)
2324 IS
2325 Select Object_Version_Number
2326 From Ben_Copy_Entity_Results
2327 Where Copy_Entity_Result_Id = l_result_id;
2328
2329 l_pgm_id Number;
2330 l_grade_id Number;
2331 l_pln_result_id Number;
2332 l_ovn Number;
2333 l_result_id Number;
2334 l_proc varchar2(72) := g_package|| 'update_or_delete_grade';
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;
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;
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
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;
2369 Close csr_ovn;
2370
2371 ben_copy_entity_results_api.update_copy_entity_results(
2372 p_copy_entity_result_id => p_grade_result_id,
2373 p_effective_date => p_effective_date,
2374 p_information104 => 'UNLINK',
2375 p_object_version_number => l_ovn,
2376 p_information323 => null);
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
2386 Open csr_plan_result_id;
2387 Fetch csr_plan_result_id into l_pln_result_id;
2388 Close csr_plan_result_id;
2389
2390 Open csr_ovn(l_pln_result_id);
2391 Fetch csr_ovn into l_ovn;
2392 Close csr_ovn;
2393
2394 ben_copy_entity_results_api.update_copy_entity_results(
2395 p_copy_entity_result_id => l_pln_result_id,
2396 p_effective_date => p_effective_date,
2397 p_Gs_Mr_Src_Entity_Result_Id => NULL,
2398 p_object_version_number => l_ovn,
2399 p_information323 => null);
2400
2401
2402 -- Delete PLIP Record.
2403
2404 Open csr_ovn(p_grade_result_id);
2405 Fetch csr_ovn into l_ovn;
2406 Close csr_ovn;
2407
2408 ben_copy_entity_results_api.delete_copy_entity_results(
2409 p_copy_entity_result_id => p_grade_result_id,
2410 p_effective_date => p_effective_date,
2411 p_object_version_number => l_ovn);
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
2421 --
2422 ---------------------------update_or_delete_step-----------------------------
2423 --
2424
2425 Procedure update_or_delete_step
2426 ( p_copy_entity_txn_id in Number,
2427 p_step_result_id in Number,
2428 p_step_id in Number,
2429 p_point_result_id in Number,
2430 p_effective_date in Date) IS
2431
2432 /* Author : mvankada
2433 Purpose : This procedure Update/Deletes Step Record
2434
2435 If the Record is in the Staging Area only
2436 1) If Use_Prg_Points = 'STEP' then delete
2437 i) Point Record
2438 ii) Step Record
2439 2) If Use_Prg_Points = 'POINT' then delete
2440 i) Step Record
2441
2442 If the Record is in the Main Tables
2443 1) If Use_Prg_Points = 'STEP' then Update the following recors information104 as UNLINK
2444 i) Point Record
2445 ii) Step Record
2446 2) If Use_Prg_Points = 'POINT' then Update the following recors information104 as UNLINK
2447 i) Step Record
2448
2449 */
2450
2451 -- To Get information1 (Pgm_ID) of PGM, based on TXN_ID
2452 Cursor csr_pgm_id
2453 IS
2454 Select grdldr.information1 -- PGM_ID
2455 From Ben_Copy_Entity_Results grdldr
2456 Where grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
2457 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2458 AND grdldr.Table_Alias = 'PGM';
2459
2460 -- To Get information253 (Grade_id) of CPP, based on Step Result Id
2461 Cursor csr_grade_id
2462 IS
2463 Select grd.information253
2464 From Ben_Copy_Entity_Results grd
2465 Where grd.Table_Alias = 'CPP'
2466 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2467 AND grd.copy_entity_result_id = (Select step.gs_parent_entity_result_id
2468 From Ben_Copy_Entity_Results step
2469 Where step.Copy_Entity_Result_Id= p_step_result_id
2470 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2471 AND step.Table_Alias = 'COP');
2472
2473 Cursor csr_ovn (l_result_id in Number)
2474 IS
2475 Select Object_Version_Number
2476 From Ben_Copy_Entity_Results
2477 Where Copy_Entity_Result_Id = l_result_id;
2478
2479
2480 l_pgm_id Number;
2481 l_grade_id Number;
2482 l_use_points Varchar2(20);
2483 l_ovn Number;
2484 l_result_id Number;
2485
2486
2487 l_proc varchar2(72) := g_package|| 'update_or_delete_step';
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);
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);
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;
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;
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
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
2527
2528 Open csr_ovn(p_point_result_id);
2529 Fetch csr_ovn into l_ovn;
2530 Close csr_ovn;
2531 ben_copy_entity_results_api.update_copy_entity_results(
2532 p_copy_entity_result_id => p_point_result_id,
2533 p_effective_date => p_effective_date,
2534 p_information104 => 'UNLINK',
2535 p_object_version_number => l_ovn,
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
2545
2546 Open csr_ovn(p_step_result_id);
2547 Fetch csr_ovn into l_ovn;
2548 Close csr_ovn;
2549 ben_copy_entity_results_api.update_copy_entity_results(
2550 p_copy_entity_result_id => p_step_result_id,
2551 p_effective_date => p_effective_date,
2552 p_information104 => 'UNLINK',
2553 p_object_version_number => l_ovn,
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);
2563 end if;
2564 -- Delete Point Record
2565 if ((l_use_points = 'STEP') And p_point_result_id IS NOT NULL) then
2566
2567 Open csr_ovn(p_point_result_id);
2568 Fetch csr_ovn into l_ovn;
2569 Close csr_ovn;
2570 ben_copy_entity_results_api.delete_copy_entity_results(
2571 p_copy_entity_result_id => p_point_result_id,
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
2581
2582 Open csr_ovn(p_step_result_id);
2583 Fetch csr_ovn into l_ovn;
2584 Close csr_ovn;
2585 ben_copy_entity_results_api.delete_copy_entity_results(
2586 p_copy_entity_result_id => p_step_result_id,
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
2596 hr_utility.set_location('Leaving :' ||l_proc, 200);
2597 end if;
2598 End update_or_delete_step;
2599
2600 --
2601
2602 --
2603 ---------------------------set_step_name-----------------------------
2604 --
2605
2606 PROCEDURE set_step_name(p_copy_entity_txn_id in number,
2607 p_effective_start_date in date,
2608 p_grd_result_id in number)
2609 IS
2610 l_proc varchar2(72) := g_package||'set_step_name';
2611 CURSOR csr_points
2612 IS
2613 select oipl.copy_entity_result_id,
2614 oipl.object_version_number
2615 from ben_copy_entity_results oipl,
2616 ben_copy_entity_results opt
2617 where nvl(oipl.information104,'PPP') <> 'UNLINK'
2618 AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
2619 AND nvl(opt.result_type_cd,'DISPLAY') = 'DISPLAY'
2620 and oipl.table_alias = 'COP'
2621 and opt.table_alias = 'OPT'
2622 and oipl.copy_entity_txn_id = p_copy_entity_txn_id
2623 and oipl.information262 = opt.copy_entity_result_id
2624 and oipl.gs_parent_entity_result_id = p_grd_result_id
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;
2634 for i in csr_points loop
2635 stepnum :=stepnum+1;
2636 ben_copy_entity_results_api.update_copy_entity_results(
2637 p_copy_entity_result_id => i.copy_entity_result_id,
2638 p_effective_date => p_effective_start_date,
2639 p_copy_entity_txn_id => p_copy_entity_txn_id,
2640 p_information5 => fnd_message.get_string('PQH','PQH_GSP_STEP_PROMPT')||stepnum,
2641 p_information100 => fnd_message.get_string('PQH','PQH_GSP_STEP_PROMPT')||stepnum,
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 --
2651 --------------------------- chk_unlink_grd_from_grdldr -----------------------------
2652 --
2653 Procedure chk_unlink_grd_from_grdldr
2654 (p_pgm_id in Number
2655 ,p_copy_entity_txn_id in Number
2656 ,p_business_group_id in Number
2657 ,p_effective_date in Date
2658 ,p_status OUT NOCOPY Varchar2
2659 ) IS
2660 /*
2661 This procedure raises error if employee placements exists on unlinked grades
2662 */
2663
2664 l_proc varchar2(72) := g_package||'chk_unlink_grd_from_grdldr';
2665 l_dummy char(1);
2666 l_found Boolean;
2667 l_grd_war_found Boolean;
2668 l_no_grades Varchar2(4000) := null;
2669 l_no_war_grades Varchar2(4000) := null;
2670
2671 Cursor csr_grades
2672 IS
2673 Select Information253, -- Grade Id
2674 Information5 -- Grade Name
2675 From Ben_Copy_Entity_Results
2676 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
2677 And Table_Alias = 'CPP'
2678 And Information253 IS NOT NULL
2679 AND Nvl(Information104,'PPP') = 'UNLINK'
2680 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2681 And Information4 = p_Business_Group_Id;
2682
2683 -- Check Grade is attached to an Employee
2684 Cursor csr_gl_grd_emp_assg (l_grade_id Number)
2685 IS
2686 Select Null
2687 From per_all_assignments_f assgt
2688 Where grade_id = l_grade_id
2689 AND Grade_Ladder_Pgm_Id = p_pgm_id
2690 AND p_Effective_Date BETWEEN assgt.effective_start_date
2691 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
2692 AND assgt.business_group_id = p_Business_Group_Id;
2693
2694
2695 -- Check Grade is attached to an employee
2696 Cursor csr_grd_assg_emp (l_grade_id Number)
2697 IS
2698 Select Null
2699 From per_all_assignments_f assgt
2700 Where grade_id = l_grade_id
2701 AND p_Effective_Date BETWEEN assgt.effective_start_date
2702 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
2703 AND assgt.business_group_id = p_Business_Group_Id;
2704
2705 -- Default Grade Ladder
2706 Cursor csr_default_grdldr
2707 IS
2708 Select Null
2709 From ben_pgm_f
2710 Where pgm_id = p_pgm_id
2711 And pgm_typ_cd = 'GSP'
2712 And Dflt_Pgm_Flag = 'Y'
2713 And business_group_id = p_business_group_id
2714 And p_Effective_Date Between Effective_Start_date
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);
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
2737 Open csr_gl_grd_emp_assg(l_rec.Information253);
2738 Fetch csr_gl_grd_emp_assg into l_dummy;
2739 If csr_gl_grd_emp_assg%Found Then
2740 l_no_grades := l_no_grades || l_rec.Information5 ||' , ';
2741 l_found := TRUE;
2742 End If;
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;
2761 Close csr_grd_assg_emp;
2762 End Loop;
2763 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');
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
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
2787 hr_utility.set_location('Leaving:'||l_proc, 100);
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
2797 p_status := null;
2798 END chk_unlink_grd_from_grdldr;
2799
2800 --
2801 --------------------------- chk_unlink_step_from_grdldr -----------------------------
2802 --
2803 Procedure chk_unlink_step_from_grdldr
2804 (p_copy_entity_txn_id in Number
2805 ,p_business_group_id in Number
2806 ,p_effective_date in Date
2807 ) IS
2808 /* This procedure raises error if Step is
2809 1) Ceiling Step
2810 2) Special Ceiling Step
2811 3) Employee Placements on this Step
2812 */
2813
2814 l_proc varchar2(72) := g_package||'chk_unlink_step_from_grdldr ';
2815 l_dummy char(1);
2816 l_found Boolean;
2817 l_no_steps Varchar2(4000) := null;
2818
2819 Cursor csr_steps
2820 IS
2821 Select Information253, -- Step Id
2822 Information5 -- Step Name
2823 INFORMATION98 -- Ceiling Flag
2824 From Ben_Copy_Entity_Results
2825 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
2826 And Table_Alias = 'COP'
2827 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2828 And Information253 IS NOT NULL
2829 AND Nvl(Information104,'PPP') = 'UNLINK'
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;
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
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
2857
2858 --
2859 --------------------------- chk_grdldr_name_unique -----------------------------
2860 --
2861 Procedure chk_grdldr_name_unique
2862 ( p_pgm_id in Number
2863 ,p_business_group_id in Number
2864 ,p_name in varchar2
2865 )
2866 is
2867 l_proc varchar2(72) := g_package||'chk_grdldr_name_unique';
2868 l_dummy char(1);
2869
2870 Cursor csr_name
2871 IS
2872 Select null
2873 From Ben_Pgm_F
2874 Where Pgm_Id <> nvl(p_pgm_id,-1)
2875 And Name = p_name
2876 And Business_Group_Id = p_business_group_id;
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
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
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;
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 --
2907 --------------------------- chk_gl_sht_name_code_unique -----------------------------
2908 --
2909 Procedure chk_gl_sht_name_code_unique
2910 ( p_pgm_id in Number
2911 ,p_business_group_id in Number
2912 ,p_short_name in varchar2 Default Null
2913 ,p_short_code in varchar2 Default Null)
2914 is
2915 l_proc varchar2(72) := g_package||'chk_gl_sht_name_code_unique';
2916 l_dummy char(1);
2917
2918
2919 Cursor csr_short_name
2920 IS
2921 Select null
2922 From Ben_Pgm_F
2923 Where Pgm_Id <> nvl(p_pgm_id,-1)
2924 And Short_Name = nvl(p_short_name,-1)
2925 And Business_Group_Id = p_business_group_id
2926 And p_short_name IS NOT NULL;
2927
2928 Cursor csr_short_code
2929 IS
2930 Select null
2931 From Ben_Pgm_F
2932 Where Pgm_Id <> nvl(p_pgm_id,-1)
2933 And Short_Code = nvl(p_short_code,-1)
2934 And Business_Group_Id = p_business_group_id
2935 And p_short_code IS NOT NULL;
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);
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;
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;
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
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;
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
2977 --
2978 ---------------------------validate_grade_ladder-----------------------------
2979 --
2980 /*
2981 The following procedure validates the grade ladder before it is saved.
2982 */
2983 Procedure validate_grade_ladder(
2984 p_pgm_id in number
2985 ,p_effective_start_date in date
2986 ,p_effective_end_date in date
2987 ,p_name in varchar2
2988 ,p_pgm_stat_cd in varchar2
2989 ,p_pgm_typ_cd in varchar2
2990 ,p_enrt_cvg_strt_dt_cd in varchar2
2991 ,p_enrt_cvg_strt_dt_rl in number
2992 ,p_rt_strt_dt_cd in varchar2
2993 ,p_rt_strt_dt_rl in number
2994 ,p_pgm_uom in varchar2
2995 ,p_enrt_cd in varchar2
2996 ,p_enrt_mthd_cd in varchar2
2997 ,p_enrt_rl in number
2998 ,p_auto_enrt_mthd_rl in number
2999 ,p_business_group_id in number
3000 ,p_Dflt_pgm_flag in Varchar2
3001 ,p_Use_prog_points_flag in Varchar2
3002 ,p_Dflt_step_cd in Varchar2
3003 ,p_Dflt_step_rl in number
3004 ,p_Update_salary_cd in Varchar2
3005 ,p_Use_multi_pay_rates_flag in Varchar2
3006 ,p_dflt_element_type_id in number
3007 ,p_Dflt_input_value_id in number
3008 ,p_Use_scores_cd in Varchar2
3009 ,p_Scores_calc_mthd_cd in Varchar2
3010 ,p_Scores_calc_rl in number
3011 ,p_gsp_allow_override_flag in varchar2
3012 ,p_use_variable_rates_flag in varchar2
3013 ,p_salary_calc_mthd_cd in varchar2
3014 ,p_salary_calc_mthd_rl in number
3015 ,p_effective_date in date
3016 ,p_short_name in varchar2
3017 ,p_short_code in varchar2
3018 ) is
3019
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
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);
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
3042 ,p_business_group_id => p_business_group_id
3043 ,p_short_name => p_short_name
3044 ,p_short_code => p_short_code);
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,
3054 p_effective_date => p_effective_date);
3055
3056 -- The system should not allow inactivating an existing grade ladder on
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);
3066 */
3067
3068
3069
3070
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 --
3080 ---------------------------chk_grdldr_grd_curreny_rate-----------------------------
3081 --
3082 Procedure chk_grdldr_grd_curreny_rate
3083 (p_copy_entity_txn_id In Number,
3084 p_business_group_id In Number,
3085 p_effective_date In Date) IS
3086
3087 -- fix for bug 7114098
3088 l_pgm_id number;
3089 cursor csr_pgm_val is
3090 select information1
3091 from Ben_Copy_Entity_Results
3092 where Copy_Entity_Txn_Id = p_copy_entity_txn_id
3093 and Information4 = p_business_group_id
3094 and table_alias = 'PGM' ;
3095
3096 -- fix for bug 7114098
3097
3098 -- Get Grade Ladder Details
3099 Cursor csr_grdldr_details
3100 IS
3101 Select Copy_Entity_Result_Id,
3102 Information50, -- Currency_Code
3103 Information41 -- Rate_Period
3104 From Ben_Copy_Entity_Results
3105 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
3106 AND Table_Alias = 'PGM'
3107 AND Result_Type_Cd = 'DISPLAY'
3108 AND Nvl(Information104,'PPP') <> 'UNLINK'
3109 AND Information4 = p_business_group_id ;
3110
3111
3112 -- Get Grade Ids
3113 Cursor csr_grade_ids(l_grdldr_result_id IN Number)
3114 IS
3115 Select Information253, -- Grade Id
3116 Information5 -- Grade Name
3117 From Ben_Copy_Entity_Results
3118 Where Gs_Parent_Entity_Result_Id = l_grdldr_result_id
3119 And Copy_Entity_Txn_Id = p_copy_entity_txn_id
3120 And Table_Alias = 'CPP'
3121 AND Nvl(Information104,'PPP') <> 'UNLINK'
3122 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3123 AND Information253 IS NOT NULL
3124 AND Information4 = p_business_group_id ;
3125
3126
3127 Cursor csr_pgm_detials(l_grade_id IN Number, l_rate IN Varchar, l_currency IN Varchar) Is
3128 Select Pgm.ACTY_REF_PERD_CD, -- Rate
3129 Pgm.Pgm_uom -- Currency
3130 From Ben_Pl_f Pl,
3131 Ben_Plip_F Plip,
3132 Ben_Pgm_F Pgm
3133 Where Pl.MAPPING_TABLE_PK_ID = l_grade_Id
3134 and Pl.MAPPING_TABLE_NAME = 'PER_GRADES'
3135 and P_Effective_Date
3136 Between Pl.Effective_Start_Date
3137 and Nvl(Pl.Effective_End_Date,hr_general.end_of_time)
3138 and Plip.Pl_Id = Pl.Pl_Id
3139 and P_Effective_Date
3140 Between Plip.Effective_Start_Date
3141 and Nvl(Plip.Effective_End_Date,hr_general.end_of_time)
3142 and Plip.Pgm_Id = Pgm.Pgm_Id
3143 and P_Effective_Date
3144 Between Pgm.Effective_Start_Date
3145 and Nvl(Pgm.Effective_End_Date,hr_general.end_of_time)
3146 and pgm.Business_group_id = p_Business_Group_Id
3147 and (pgm.ACTY_REF_PERD_CD <> l_rate
3148 or pgm.Pgm_Uom <> l_currency)
3149 and Pgm_Typ_Cd = 'GSP'
3150 and l_pgm_id <> pgm.pgm_id; -- added for bug 7114098
3151
3152 l_proc varchar2(72) := g_package|| 'chk_grdldr_grd_curreny_rate';
3153 l_curr_gl_result_id Number;
3154 l_curr_gl_currency Ben_Copy_Entity_Results.Information50%Type;
3155 l_curr_gl_period Ben_Copy_Entity_Results.Information41%Type;
3156
3157 l_target_currency Ben_Pgm_F.PGM_UOM%Type;
3158 l_target_period Ben_Pgm_F.ACTY_REF_PERD_CD%Type;
3159
3160 l_dummy Varchar2(4000) := null;
3161 l_found Boolean := FALSE;
3162
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;
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
3184 -- fix for bug 7114098
3185 open csr_pgm_val;
3186 fetch csr_pgm_val into l_pgm_id;
3187 close csr_pgm_val;
3188 -- fix for bug 7114098
3189
3190 For l_grade_id_rec IN csr_grade_ids(l_curr_gl_result_id)
3191 Loop
3192
3193 Open csr_pgm_detials(l_grade_id_rec.Information253,l_curr_gl_period, l_curr_gl_currency);
3194 Fetch csr_pgm_detials into l_target_currency,l_target_period;
3195 If csr_pgm_detials%Found Then
3196 l_found := TRUE;
3197 l_dummy := l_dummy || l_grade_id_rec.Information5 ||' , ';
3198 End If;
3199 Close csr_pgm_detials;
3200 End Loop;
3201
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
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);
3217 end if;
3218 END chk_grdldr_grd_curreny_rate;
3219
3220 --
3221 --
3222 ---------------------------------------------------------------------------
3223 -- This procedure validates if duplicate criteria set has been defined
3224 -- and attached to the grade ladder.
3225 --
3226 Procedure chk_duplicate_crset_exists(
3227 p_copy_entity_txn_id in number,
3228 p_effective_date in date,
3229 p_cset_id in number default null,
3230 p_location_id in number default null,
3231 p_job_id in number default null,
3232 p_org_id in number default null,
3233 p_rule_id in number default null,
3234 p_person_type_id in number default null,
3235 p_service_area_id in number default null,
3236 p_barg_unit_cd in varchar2 default null,
3237 p_full_part_time_cd in varchar2 default null,
3238 p_perf_type_cd in varchar2 default null,
3239 p_rating_type_cd in varchar2 default null,
3240 p_duplicate_exists out nocopy varchar2,
3241 p_duplicate_cset_name out nocopy varchar2) is
3242 --
3243 -- Select all crsets in the txn which are not equal to current crset
3244 --
3245 cursor csr_crset is
3246 select *
3247 from ben_copy_entity_results
3248 where copy_entity_txn_id = p_copy_entity_txn_id
3249 and table_alias = 'CRSET'
3250 and p_effective_date between information2 and nvl(information3, to_date('31/12/4712','dd/mm/RRRR'))
3251 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3252 and INFORMATION161 <> nvl(p_cset_id, -1)
3253 order by information161,information2;
3254 --
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
3264 --
3265 -- Check for duplicate criteria set
3266 --
3267 If (nvl(crset_rec.INFORMATION232,-1) = nvl(p_location_id,-1) and
3268 nvl(crset_rec.INFORMATION233,-1) = nvl(p_job_id,-1) and
3269 nvl(crset_rec.INFORMATION234,-1) = nvl(p_org_id,-1) and
3270 nvl(crset_rec.INFORMATION235,-1) = nvl(p_rule_id,-1) and
3271 nvl(crset_rec.INFORMATION236,-1) = nvl(p_person_type_id,-1) and
3272 nvl(crset_rec.INFORMATION237,-1) = nvl(p_service_area_id,-1) and
3273 nvl(crset_rec.INFORMATION101,'XXX') = nvl(p_barg_unit_cd,'XXX') and
3274 nvl(crset_rec.INFORMATION102,'XXX') = nvl(p_full_part_time_cd,'XXX') and
3275 nvl(crset_rec.INFORMATION103,'XXX') = nvl(p_perf_type_cd,'XXX') and
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 --
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
3292 -- 1) Criteria set name is not null
3293 -- 2) At least one criterion is entered
3294 -- 3) If performance type is entered, rating type must also be entered
3295 --
3296 Procedure validate_crset_values(p_copy_entity_txn_id in number,
3297 p_effective_date in date)
3298 is
3299 --
3300 cursor csr_crset is
3301 select *
3302 from ben_copy_entity_results
3303 where copy_entity_txn_id = p_copy_entity_txn_id
3304 and table_alias = 'CRSET'
3305 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3306 and dml_operation in ('INSERT','UPDATE') -- Check only insert/ updates crset
3307 order by information161,information2;
3308 --
3309 l_duplicate_exists varchar2(10);
3310 l_duplicate_cset_name varchar2(240);
3311 l_proc varchar2(200) := 'validate_crset_values';
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
3339 crset_rec.INFORMATION235 IS NULL and
3340 crset_rec.INFORMATION236 IS NULL and
3341 crset_rec.INFORMATION237 IS NULL and
3342 crset_rec.INFORMATION101 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 --
3352 End if;
3353 --
3354 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
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;
3372 End if;
3373 --
3374 --
3375 -- Check if there exists a duplicate for the current criteria set in the transaction
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,
3385 p_cset_id => crset_rec.information161,
3386 p_location_id => crset_rec.INFORMATION232,
3387 p_job_id => crset_rec.INFORMATION233,
3388 p_org_id => crset_rec.INFORMATION234,
3389 p_rule_id => crset_rec.INFORMATION235,
3390 p_person_type_id => crset_rec.INFORMATION236,
3391 p_service_area_id => crset_rec.INFORMATION237,
3392 p_barg_unit_cd => crset_rec.INFORMATION101,
3393 p_full_part_time_cd => crset_rec.INFORMATION102,
3394 p_perf_type_cd => crset_rec.INFORMATION103,
3395 p_rating_type_cd => crset_rec.INFORMATION104,
3396 p_duplicate_exists => l_duplicate_exists,
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;
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 --
3418 Procedure chk_review_submit_val
3419 (p_copy_entity_txn_id in Number,
3420 p_effective_date in Date,
3421 p_pgm_id in Number,
3422 p_business_group_id in Number,
3423 p_status OUT NOCOPY Varchar2,
3424 p_prog_le_created_flag OUT NOCOPY Varchar2,
3425 p_sync_le_created_flag OUT NOCOPY Varchar2,
3426 p_plan_tp_created_flag OUT NOCOPY Varchar2
3427 ) IS
3428 /*
3429 Author : mvankada
3430 Purpose : This procedure checks all validations to be performed
3431 for Review and submit Page
3432
3433 */
3434
3435 l_proc varchar2(72) := g_package||'chk_review_submit_val';
3436 l_pt_le_status Varchar2(30);
3437 l_plan_type_start_date DATE:= get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
3438 Cursor csr_pgm_details
3439 IS
3440 Select Name,
3441 Short_Name,
3442 Short_Code
3443 From Ben_Pgm_F
3444 Where Pgm_id Is Not Null
3445 And Pgm_id = p_pgm_id
3446 And Business_Group_Id = p_business_group_id;
3447
3448 Cursor csr_grdldr_date
3449 IS
3450 Select Information2 EFFECTIVE_START_DATE
3451 From Ben_Copy_Entity_Results
3452 Where Copy_Entity_Txn_id = p_copy_entity_txn_id
3453 And Information4 = p_business_group_id
3454 And Table_Alias = 'PGM'
3455 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3456 And Information_Category = 'GRADE_LADDER';
3457
3458
3459 l_name Ben_Pgm_F.Name%Type;
3460 l_short_name Ben_Pgm_F.Short_Name%Type;
3461 l_short_code Ben_Pgm_F.Short_Code%Type;
3462 l_status Varchar2(20) := 'E';
3463 l_error_status Varchar2(20) := 'E';
3464 l_grdldr_date Date;
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;
3474 Fetch csr_pgm_details into l_name,l_short_name,l_short_code;
3475 Close csr_pgm_details;
3476 End if;
3477
3478
3479 -- Check if there is only one default grade ladder in the business group
3480 -- The system should not allow inactivating an existing grade ladder on
3481 -- which there are employee placements.
3482
3483 if p_pgm_id IS NOT NULL Then
3484 pqh_gsp_utility.validate_grade_ladder(p_pgm_id => p_pgm_id
3485 ,p_effective_date => p_effective_date
3486 ,p_name => l_name
3487 ,p_short_name => l_short_name
3488 ,p_short_code => l_short_code
3489 ,p_business_group_id =>p_business_group_id
3490 );
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
3500 ,p_business_group_id => p_business_group_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);
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
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
3522 -- Get Grade Ladder Effective Date
3523 Open csr_grdldr_date;
3524 Fetch csr_grdldr_date into l_grdldr_date;
3525 Close csr_grdldr_date;
3526
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);
3536
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);
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
3554 ,p_business_group_id => p_business_group_id
3555 ,p_effective_date => p_effective_date
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,
3565 p_status => l_pt_le_status,
3566 p_prog_le_created_flag => p_prog_le_created_flag,
3567 p_sync_le_created_flag => p_sync_le_created_flag,
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');
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;
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;
3594 else
3595 null;
3596 end if;
3597
3598 --
3599 -- check values in crset.
3600 --
3601 validate_crset_values(p_copy_entity_txn_id => p_copy_entity_txn_id,
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
3611 ,p_effective_date => p_effective_date
3612 ,p_business_group_id => p_business_group_id
3613 ,p_status => l_error_status);
3614
3615
3616 -- Call to raise any errors on multi-message list
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
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
3634 END chk_review_submit_val;
3635
3636
3637 --
3638 --
3639 Function get_which_rates (p_copy_entity_txn_id in Number) Return Varchar2 is
3640 cursor c1 is
3641 select 1 from dual where exists (
3642 select 1
3643 FROM BEN_COPY_ENTITY_RESULTS
3644 WHERE NVL(INFORMATION104,'PPP') NOT IN ('UNLINK')
3645 AND TABLE_ALIAS ='COP'
3646 AND copy_entity_txn_id = p_copy_entity_txn_id
3647 AND result_type_cd = 'DISPLAY'
3648 AND gs_parent_entity_result_id is not null);
3649
3650 cnt number;
3651 l_use_point_or_step varchar2(20);
3652 l_copy_entity_txn_id number;
3653 l_get_which_rates varchar2(20);
3654 l_proc varchar2(72) := g_package||'.get_which_rates';
3655 begin
3656 l_use_point_or_step := use_point_or_step(p_copy_entity_txn_id);
3657 open c1;
3658 fetch c1 into cnt;
3659 if (c1%NOTFOUND) then
3660 l_get_which_rates := 'GRADE';
3661 else
3662 l_get_which_rates := l_use_point_or_step;
3663 end if;
3664 close c1;
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
3674 Function get_rates_icon_enabled (p_copy_entity_txn_id in Number,
3675 p_copy_entity_result_id in Number,
3676 p_rate_hgrid_node in varchar2)
3677 Return Varchar2 is
3678 l_get_which_rates varchar2(20) := get_which_rates(p_copy_entity_txn_id);
3679 l_icon varchar2(20) := 'NONE';
3680 cnt number := 0;
3681 cursor c1 is
3682 select 1 from dual where exists (
3683 select 1 from ben_copy_entity_results
3684 where copy_entity_txn_id = p_copy_entity_txn_id
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);
3694 end if;
3695
3696 if (p_rate_hgrid_node = 'PGM') then
3697 if (l_get_which_rates = 'POINT') then
3698 l_icon := 'NONE';
3699 else
3700 open c1;
3701 fetch c1 into cnt;
3702 if (c1%NOTFOUND) then
3703 l_icon := 'DISABLED';
3704 else
3705 l_icon := 'ENABLED';
3706 end if;
3707 close c1;
3708 end if;
3709 elsif (p_rate_hgrid_node = 'CPP') then
3710 if (l_get_which_rates = 'POINT') then
3711
3712 open c1;
3713 fetch c1 into cnt;
3714 if (c1%NOTFOUND) then
3715 l_icon := 'DISABLED';
3716 else
3717 l_icon := 'ENABLED';
3718 end if;
3719 close c1;
3720 else
3721 l_icon := 'NONE';
3722 end if;
3723 else
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
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.
3739 --
3740 Function pgm_freq_annual_factor
3741 (p_ref_perd_cd in varchar2) return number is
3742 --
3743 -- Local variable declaration
3744 --
3745 l_ret_cd NUMBER;
3746 --
3747 BEGIN
3748 --
3749 IF p_ref_perd_cd = 'PWK' THEN
3750 l_ret_cd := 52;
3751 ELSIF p_ref_perd_cd = 'BWK' THEN
3752 l_ret_cd := 26;
3753 ELSIF p_ref_perd_cd = 'SMO' THEN
3754 l_ret_cd := 24;
3755 ELSIF p_ref_perd_cd = 'PQU' THEN
3756 l_ret_cd := 4;
3757 ELSIF p_ref_perd_cd = 'PYR' THEN
3758 l_ret_cd := 1;
3759 ELSIF p_ref_perd_cd = 'SAN' THEN
3760 l_ret_cd := 2;
3761 ELSIF p_ref_perd_cd = 'MO' THEN
3762 l_ret_cd := 12;
3763 ELSIF p_ref_perd_cd = 'NOVAL' THEN
3764 l_ret_cd := 1;
3765 ELSIF p_ref_perd_cd = 'PHR' then
3766 --
3767 l_ret_cd := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
3768 if l_ret_cd is null then
3769 l_ret_cd := 2080;
3770 end if;
3771 --
3772 ELSE
3773 l_ret_cd := 1;
3774 END IF;
3775 --
3776 RETURN l_ret_cd;
3777 END pgm_freq_annual_factor;
3778 --
3779 -- Create Pay Rate for a Pay Scale
3780 --
3781 Procedure create_pay_rate (p_business_group_id in number,
3782 p_scale_id in number,
3783 p_rate_name in varchar2,
3784 p_rate_id Out nocopy number,
3785 p_ovn Out nocopy number)
3786 is
3787 --
3788 -- Local variables
3789 --
3790 l_rate_id pay_rates.rate_id%type;
3791 l_ovn pay_rates.object_version_number%type;
3792 l_proc varchar2(100) := 'pqh_gsp_utility.create_pay_rate';
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
3802 ,p_effective_date => trunc(sysdate)
3803 ,p_business_group_id => p_business_group_id
3804 ,p_name => p_rate_name
3805 ,p_parent_spine_id => p_scale_id
3806 ,p_rate_type => 'SP'
3807 ,p_rate_uom => 'M'
3808 ,p_object_version_number => l_ovn
3809 ,p_rate_id => l_rate_id);
3810 --
3811 --
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 --
3821 -- Create Pay Rate for Grade Rates
3822 --
3823 Procedure create_pay_rate(p_business_group_id in number,
3824 p_ldr_period_code in varchar2,
3825 p_rate_id Out nocopy number,
3826 p_ovn Out nocopy number)
3827 is
3828 --
3829 -- Local variables
3830 --
3831 l_rate_id pay_rates.rate_id%type;
3832 l_ovn pay_rates.object_version_number%type;
3833 l_proc varchar2(100) := 'pqh_gsp_utility.create_pay_rate';
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
3843 ,p_effective_date => trunc(sysdate)
3844 ,p_business_group_id => p_business_group_id
3845 ,p_name => hr_general.decode_lookup('PQH_GSP_GEN_PAY_RATE_NAME',p_ldr_period_code)
3846 ,p_rate_type => 'G'
3847 ,p_rate_uom => 'M'
3848 ,p_object_version_number => l_ovn
3849 ,p_rate_id => l_rate_id);
3850 --
3851 --
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 --
3861 procedure step_exists_for_point(p_copy_entity_txn_id in number,
3862 p_points_result_id in number,
3863 p_status out nocopy varchar)
3864 is
3865 cursor csr_steps_for_point
3866 is
3867 select null
3868 from ben_copy_entity_results
3869 where copy_entity_txn_id = p_copy_entity_txn_id
3870 and information262 = p_points_result_id
3871 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3872 and information104 = 'LINK';
3873 l_steps_for_point NUMBER;
3874 begin
3875 open csr_steps_for_point;
3876 fetch csr_steps_for_point into l_steps_for_point;
3877 if(csr_steps_for_point%NOTFOUND) then
3878 p_status := 'N';
3879 else
3880 p_status := 'Y';
3881 end if;
3882 close csr_steps_for_point;
3883 end step_exists_for_point;
3884 --
3885 procedure chk_scale_name(p_copy_entity_txn_id in number,
3886 p_business_group_id in number,
3887 p_copy_entity_result_id in number,
3888 p_parent_spine_id in number,
3889 p_name in varchar,
3890 p_status out nocopy varchar)
3891 is
3892 cursor csr_scale_name
3893 is
3894 select null
3895 from ben_copy_entity_results
3896 where copy_entity_txn_id = p_copy_entity_txn_id
3897 and copy_entity_result_id <> p_copy_entity_result_id
3898 and information98 = p_name
3899 and table_alias = 'SCALE'
3900 union
3901 select null
3902 from per_parent_spines
3903 where name = p_name
3904 and business_group_id = p_business_group_id
3905 and parent_spine_id <> p_parent_spine_id;
3906
3907 Cursor plip_details
3908 is
3909 select information98,copy_entity_result_id,dml_operation
3910 from ben_copy_entity_results
3911 where copy_entity_txn_id = p_copy_entity_txn_id
3912 and table_alias = 'CPP'
3913 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3914 and information258 = p_copy_entity_result_id;
3915 l_scale_name varchar2(30);
3916 begin
3917 open csr_scale_name;
3918 fetch csr_scale_name into l_scale_name;
3919 if(csr_scale_name%NOTFOUND) then
3920 p_status := 'Y';
3921 for i in plip_details loop
3922 if i.information98<>p_name THEN
3923 update ben_copy_entity_results
3924 set information98 = p_name,
3925 dml_operation = get_dml_operation(i.dml_operation)
3926 where copy_entity_result_id = i.copy_entity_result_id;
3927
3928 END IF;
3929 end loop;
3930 else
3931 p_status :='E';
3932 end if;
3933 close csr_scale_name;
3934 end chk_scale_name;
3935 --
3936 -----IS_CRRATE_THERE_ICON--------------------
3937 Function is_crrate_there_icon
3938 (p_copy_entity_txn_id in Number,
3939 p_copy_entity_result_id in Number,
3940 p_effective_date_in in date,
3941 p_rate_hgrid_node in varchar2)
3942 Return Varchar2 is
3943 l_proc varchar2(72) := g_package||'.is_crrate_there_icon';
3944 l_get_which_rates varchar2(20) := get_which_rates(p_copy_entity_txn_id);
3945 l_crrate_icon varchar2(50);
3946 l_effective_date date;
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,
3956 p_effective_date => l_effective_date);
3957 elsif ((p_rate_hgrid_node = 'COP') and ((l_get_which_rates = 'POINT') or (l_get_which_rates = 'STEP'))) then
3958 l_crrate_icon := pqh_gsp_hr_to_stage.is_crrate_there(p_oipl_cer_id => p_copy_entity_result_id,
3959 p_copy_entity_txn_id => p_copy_entity_txn_id,
3960 p_effective_date => l_effective_date);
3961 else
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
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
3977
3978 ------------------------------------------------------
3979
3980 Function PGM_TO_BASIS_CONVERSION
3981 (P_Pgm_ID IN Number
3982 ,P_EFFECTIVE_DATE IN Date
3983 ,P_AMOUNT IN Number
3984 ,P_ASSIGNMENT_ID IN Number)
3985
3986 Return Number Is
3987
3988 CURSOR C_Pgm_rt_Perd IS
3989 Select Pgm.ACTY_REF_PERD_CD,
3990 Nvl(Cur.Precision,2),
3991 Update_Salary_Cd
3992 From Ben_Pgm_f Pgm,
3993 Fnd_Currencies Cur
3994 Where Pgm.Pgm_id = P_Pgm_Id
3995 and P_Effective_Date
3996 Between Pgm.Effective_Start_Date
3997 and Pgm.Effective_End_Date
3998 and Cur.Currency_Code(+) = Pgm.Pgm_Uom;
3999
4000 CURSOR C_Pay_Basis IS
4001 select Ppb.Pay_Annualization_Factor,Ppb.Pay_basis
4002 From Per_All_Assignments_f asg,
4003 Per_Pay_Bases ppb
4004 where Asg.Assignment_id = P_Assignment_Id
4005 and P_effective_date
4006 between Asg.Effective_Start_Date
4007 and Asg.Effective_End_Date
4008 and Ppb.Pay_Basis_Id = Asg.Pay_Basis_Id;
4009
4010 l_ref_perd_cd Ben_Pgm_F.ACTY_REF_PERD_CD%TYPE;
4011 l_precision Fnd_Currencies.Precision%TYPE;
4012 l_factor Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
4013 l_ret_amount Number;
4014 l_pay_annualization_factor Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
4015 l_update_Salary_cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
4016 L_Payroll_name pay_all_payrolls_f.Payroll_name%TYPE;
4017 l_pay_basis Per_Pay_Bases.Pay_basis%TYPE;
4018 l_gl_ann_factor ben_pgm_extra_info.pgi_information5%TYPE;
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);
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;
4033 IF C_Pgm_rt_Perd%NOTFOUND THEN
4034 l_ref_perd_cd := 'NOVAL';
4035 END IF;
4036 CLOSE C_Pgm_rt_Perd;
4037 --
4038
4039 --
4040 If L_Update_Salary_Cd = 'SALARY_BASIS' Then
4041
4042 OPEN c_pay_basis;
4043 FETCH c_pay_basis into l_factor,l_pay_basis;
4044 CLOSE C_Pay_Basis;
4045
4046 l_gl_ann_factor := pqh_gsp_utility.get_gl_ann_factor(p_pgm_id => p_pgm_id);
4047
4048 if l_gl_ann_factor is not null then
4049 l_ret_amount := (p_amount*to_number(l_gl_ann_factor))/l_factor;
4050 return l_ret_amount;
4051 end if;
4052
4053
4054 /* To fix bug 4907433
4055 * If the Grade Ladder frequency and the Salary Basis frequency are matching then
4056 * Return the amount without any calculations
4057 */
4058
4059 IF (l_pay_basis = 'MONTHLY' AND l_ref_perd_cd = 'MO')
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
4069 RETURN p_amount;
4070
4071 END IF;
4072
4073 -- Bug Fix 4907433 ends
4074
4075 Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
4076
4077 per_pay_proposals_populate.get_payroll(P_Assignment_Id
4078 ,P_Effective_Date
4079 ,l_Payroll_name
4080 ,l_factor);
4081 Else
4082 Return P_Amount;
4083 End If;
4084
4085 IF l_factor is null or l_factor=0 THEN
4086 l_factor := 1;
4087 END IF;
4088
4089 IF l_ref_perd_cd = 'PWK' THEN
4090 l_ret_amount := (p_amount*52)/l_factor;
4091 ELSIF l_ref_perd_cd = 'BWK' THEN
4092 l_ret_amount := (p_amount*26)/l_factor;
4093 ELSIF l_ref_perd_cd = 'SMO' THEN
4094 l_ret_amount := (p_amount*24)/l_factor;
4095 ELSIF l_ref_perd_cd = 'PQU' THEN
4096 l_ret_amount := (p_amount*4)/l_factor;
4097 ELSIF l_ref_perd_cd = 'PYR' THEN
4098 l_ret_amount := (p_amount*1)/l_factor;
4099 ELSIF l_ref_perd_cd = 'SAN' THEN
4100 l_ret_amount := (p_amount*2)/l_factor;
4101 ELSIF l_ref_perd_cd = 'MO' THEN
4102 l_ret_amount := (p_amount*12)/l_factor;
4103 ELSIF l_ref_perd_cd = 'NOVAL' THEN
4104 l_ret_amount := (p_amount*1)/l_factor;
4105 ELSIF l_ref_perd_cd = 'PHR' then
4106
4107 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
4108 If l_pay_annualization_factor is null then
4109 l_pay_annualization_factor := 2080;
4110 End if;
4111 --
4112 l_ret_amount := (p_amount * l_pay_annualization_factor)/l_factor;
4113 --
4114 ELSE
4115 l_ret_amount := (p_amount*1)/l_factor;
4116 END IF;
4117 --
4118 -- RETURN trunc(l_ret_amount,l_precision); -- Bug 6608606
4119 RETURN l_ret_amount;
4120
4121 End PGM_TO_BASIS_CONVERSION;
4122
4123 ----------------------------------------------------------------------------------------------------
4124
4125 Function get_num_steps_in_grade(p_copy_entity_txn_id in number,
4126 p_grade_cer_id in number)
4127 Return Number is
4128
4129 cursor csr_steps_in_grade
4130 is
4131 select count(*) cnt
4132 from ben_copy_entity_results
4133 where table_alias = 'COP'
4134 and copy_entity_txn_id = p_copy_entity_txn_id
4135 and gs_parent_entity_result_id in
4136 (select gs_mirror_src_entity_result_id
4137 from ben_copy_entity_results
4138 where table_alias = 'PLN' and copy_entity_txn_id = p_copy_entity_txn_id
4139 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY' and copy_entity_result_id = p_grade_cer_id)
4140 And result_type_cd = 'DISPLAY'
4141 And Nvl(Information104,'PPP') <> 'UNLINK';
4142
4143 l_proc varchar2(72) := g_package||'.get_num_steps_in_grade';
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;
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
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,
4164 p_point_cer_id in number,
4165 p_effective_date in date)
4166 RETURN NUMBER
4167 is
4168 --
4169 -- Local variables
4170 --
4171 Cursor csr_dflt_point_rate is
4172 Select information297
4173 From ben_copy_entity_results
4174 Where copy_entity_txn_id = p_copy_entity_txn_id
4175 and INFORMATION278 = p_point_cer_id
4176 and p_effective_date between information2 and information3
4177 and table_alias = 'HRRATE'
4178 and result_type_cd = 'DISPLAY';
4179 --
4180 l_point_rate number;
4181 l_proc varchar2(100) := 'pqh_gsp_utility.get_dflt_point_rate';
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
4191 Open csr_dflt_point_rate;
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 --
4201 End;
4202
4203 --
4204
4205 -- Added by vevenkat for Approval UI ----
4206 --
4207 --
4208
4209 Function Get_person_name (P_Person_id IN Number,
4210 P_Effective_Date IN Date)
4211 Return varchar2 is
4212
4213 Cursor Csr_Person is
4214 Select Full_name
4215 From Per_All_people_F
4216 Where Person_id = P_Person_id
4217 and Nvl(P_Effective_Date, Sysdate)
4218 Between Effective_Start_Date
4219 and Effective_End_Date;
4220
4221 L_Person_name per_All_people_F.Full_name%TYPE;
4222 Begin
4223
4224 If P_Person_id is NOT NULL then
4225
4226 Open Csr_Person;
4227 Fetch Csr_Person into L_Person_name;
4228 Close Csr_Person;
4229
4230 End If;
4231
4232 Return l_Person_Name;
4233 End Get_Person_Name;
4234
4235 Function Get_Assgt_Status (P_Assgt_Status_Id IN Number)
4236 Return varchar2 Is
4237
4238 Cursor Csr_Assgt_status is
4239 Select User_Status
4240 From PER_ASSIGNMENT_STATUS_TYPES_TL
4241 where Assignment_Status_Type_Id = P_Assgt_Status_Id
4242 and language = userenv('LANG');
4243
4244 L_User_Status PER_ASSIGNMENT_STATUS_TYPES_TL.User_Status%TYPE;
4245 begin
4246
4247 If P_Assgt_Status_Id is NOT NULL then
4248 Open Csr_Assgt_Status;
4249 Fetch Csr_Assgt_Status into l_User_Status;
4250 Close Csr_Assgt_Status;
4251 End If;
4252
4253 Return L_USer_Status;
4254 End Get_Assgt_Status;
4255 -------------------------------------------------------------------
4256 Procedure check_sal_basis_iv (p_input_value_id in number,
4257 p_basis_id in number,
4258 p_business_group_id in number,
4259 p_exists_flag Out nocopy varchar2)
4260 is
4261 --
4262 -- Local variables
4263 --
4264 Cursor csr_sal_basis_iv is
4265 Select 'x'
4266 From per_pay_bases
4267 where business_group_id = p_business_group_id
4268 and rate_id is null
4269 and input_value_id = p_input_value_id
4270 and pay_basis_id <> p_basis_id;
4271 --
4272 l_dummy varchar2(20);
4273 l_proc varchar2(100) := 'pqh_gsp_utility.check_sal_basis_iv';
4274 --
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 ;
4284 If csr_sal_basis_iv%found then
4285 p_exists_flag := 'Y';
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
4295 -------------------------------------------------------------------
4296 procedure update_oipl_records(
4297 p_effective_date IN DATE,
4298 p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
4299 p_point_name IN ben_copy_entity_results.information99%TYPE,
4300 p_sequence IN ben_copy_entity_results.information263%TYPE,
4301 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE
4302 )
4303 IS
4304
4305
4306 cursor csr_oipl_records
4307 is
4308 select copy_entity_result_id,object_version_number,dml_operation
4309 from ben_copy_entity_results
4310 where table_alias = 'COP'
4311 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4312 and information262 = p_copy_entity_result_id
4313 and copy_entity_txn_id = p_copy_entity_txn_id;
4314 r_oipl_record csr_oipl_records%ROWTYPE;
4315 l_ovn NUMBER;
4316 begin
4317 for r_oipl_record in csr_oipl_records loop
4318
4319 l_ovn := r_oipl_record.object_version_number;
4320 ben_copy_entity_results_api.update_copy_entity_results(
4321 p_effective_date => p_effective_date,
4322 p_copy_entity_result_id => r_oipl_record.copy_entity_result_id,
4323 p_information99 => p_point_name,
4324 p_information263 => p_sequence,
4325 p_information323 => null,
4326 p_dml_operation =>get_dml_operation(r_oipl_record.dml_operation),
4327 p_object_version_number => l_ovn
4328 );
4329 end loop;
4330 end;
4331 --
4332 --
4333 ---------------------------move_data_stage_to_hr-----------------------------
4334 --
4335 Procedure move_data_stage_to_hr
4336 (p_copy_entity_txn_id in Number,
4337 p_effective_date in Date,
4338 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
4339 p_business_group_id in Number,
4340 p_datetrack_mode in Varchar2,
4341 p_error_msg out Nocopy Varchar2
4342 ) IS
4343
4344 l_proc varchar2(72) := g_package||'move_data_stage_to_hr';
4345 l_msg varchar2(2000):= null;
4346
4347
4348 Begin
4349 --
4350 -- Issue Savepoint
4351 savepoint move_data_stage_to_hr;
4352 --
4353 --
4354 -- Initialise Multiple Message Detection
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);
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 --
4377 -- Call to raise any errors on multi-message list
4378 hr_multi_message.end_validation_set;
4379 -- p_error_msg := l_msg;
4380
4381 EXCEPTION
4382 when hr_api.validate_enabled then
4383 Rollback to move_data_stage_to_hr;
4384 null;
4385 when hr_multi_message.error_message_exist then
4386 Rollback to move_data_stage_to_hr;
4387 null;
4388 when others then
4389 l_msg := nvl(fnd_message.get,sqlerrm);
4390 p_error_msg := l_msg;
4391 Rollback to move_data_stage_to_hr;
4392 End move_data_stage_to_hr;
4393
4394 --
4395 ------------------ get_grade_name ----------------------------
4396 --
4397 --
4398
4399 PROCEDURE GET_GRADE_NAME (
4400 p_grade_definition_id IN NUMBER,
4401 p_business_group_id IN NUMBER,
4402 p_concatenated_segments OUT NOCOPY varchar2)
4403 IS
4404 CURSOR csr_segments
4405 IS
4406 select segment1,
4407 segment2,
4408 segment3,
4409 segment4,
4410 segment5,
4411 segment6,
4412 segment7,
4413 segment8,
4414 segment9,
4415 segment10,
4416 segment11,
4417 segment12,
4418 segment13,
4419 segment14,
4420 segment15,
4421 segment16,
4422 segment17,
4423 segment18,
4424 segment19,
4425 segment20,
4426 segment21,
4427 segment22,
4428 segment23,
4429 segment24,
4430 segment25,
4431 segment26,
4432 segment27,
4433 segment28,
4434 segment29,
4435 segment30
4436 from per_grade_definitions
4437 where grade_definition_id = p_grade_definition_id;
4438 segments csr_segments%ROWTYPE;
4439 l_ccid number;
4440 l_flex_num number;
4441
4442 BEGIN
4443
4444 open csr_segments;
4445 fetch csr_segments INTO segments;
4446 close csr_segments;
4447
4448 select grade_structure into l_flex_num
4449 from per_business_groups_perf
4450 where business_group_id =p_business_group_id;
4451
4452 hr_kflex_utility.ins_or_sel_keyflex_comb
4453 (p_appl_short_name => 'PER'
4454 ,p_flex_code => 'GRD'
4455 ,p_flex_num => l_flex_num
4456 ,p_segment1 =>segments.segment1
4457 ,p_segment2 =>segments.segment2
4458 ,p_segment3 =>segments.segment3
4459 ,p_segment4 =>segments.segment4
4460 ,p_segment5 =>segments.segment5
4461 ,p_segment6 =>segments.segment6
4462 ,p_segment7 =>segments.segment7
4463 ,p_segment8 =>segments.segment8
4464 ,p_segment9 =>segments.segment9
4465 ,p_segment10 =>segments.segment10
4466 ,p_segment11 =>segments.segment11
4467 ,p_segment12 =>segments.segment12
4468 ,p_segment13 =>segments.segment13
4469 ,p_segment14 =>segments.segment14
4470 ,p_segment15 =>segments.segment15
4471 ,p_segment16 =>segments.segment16
4472 ,p_segment17 =>segments.segment17
4473 ,p_segment18 =>segments.segment18
4474 ,p_segment19 =>segments.segment19
4475 ,p_segment20 =>segments.segment20
4476 ,p_segment21 =>segments.segment21
4477 ,p_segment22 =>segments.segment22
4478 ,p_segment23 =>segments.segment23
4479 ,p_segment24 =>segments.segment24
4480 ,p_segment25 =>segments.segment25
4481 ,p_segment26 =>segments.segment26
4482 ,p_segment27 =>segments.segment27
4483 ,p_segment28 =>segments.segment28
4484 ,p_segment29 =>segments.segment29
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 -----------
4494 --
4495
4496 FUNCTION GET_DML_OPERATION
4497 (p_in_dml_operation IN ben_copy_entity_results.dml_operation%TYPE)
4498 return VARCHAR2
4499 is
4500 p_out_dml_operation ben_copy_entity_results.dml_operation%TYPE;
4501
4502 begin
4503 IF p_in_dml_operation = 'REUSE' THEN
4504 p_out_dml_operation := 'UPDATE';
4505 ELSIF p_in_dml_operation = 'COPIED' THEN
4506 p_out_dml_operation := 'UPD_INS';
4507 ELSE
4508 p_out_dml_operation := p_in_dml_operation;
4509 END IF;
4510
4511
4512 RETURN p_out_dml_operation;
4513 END get_dml_operation;
4514 --------------------------------------------------------------------------------
4515 Procedure chk_no_asg_grd_ldr(p_asg_grade_ladder_id in number,
4516 p_asg_grade_id in number,
4517 p_asg_org_id in number,
4518 p_asg_bg_id in number,
4519 p_effective_date in date) IS
4520 --
4521 -- Declare cursor
4522 --
4523 cursor csr_chk_gsp_in_system is
4524 select 'x'
4525 from ben_pgm_f pgm
4526 where pgm.business_group_id = p_asg_bg_id
4527 and pgm.pgm_typ_cd = 'GSP'
4528 and p_effective_date
4529 between pgm.effective_start_date
4530 and nvl(pgm.effective_end_date,to_date('31/12/4712','dd/mm/RRRR'));
4531 --
4532 l_exists varchar2(10);
4533 --
4534 Begin
4535 --
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;
4545 if csr_chk_gsp_in_system%FOUND then
4546 close csr_chk_gsp_in_system;
4547 --
4548 -- This message is cofigurable(BUG3219215)
4549 --
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
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,
4567 P_pgm_id IN NUMBER)
4568 RETURN varchar2
4569 IS
4570 l_return Varchar2(50);
4571 BEGIN
4572 l_return := pqh_corps_utility.bus_area_pgm_entity_exist( p_bus_area_cd => p_bus_area_cd
4573 ,P_pgm_id => P_pgm_id );
4574 RETURN l_return;
4575 Return NULL;
4576 END bus_area_pgm_entity_exist;
4577 -----------------------------------------
4578 ---------- < chk_new_ceiling >-----------
4579 --- < ggnanagu >-------------------------
4580 PROCEDURE chk_new_ceiling (
4581 p_effective_date IN DATE,
4582 p_grade_cer_id IN NUMBER,
4583 p_new_ceiling IN NUMBER
4584 )
4585 IS
4586 /*
4587 p_new_ceiling is the new ceiling sequence.
4588 Throw error if there are placements above the New Ceiling sequence.
4589 */
4590 l_gspine_id NUMBER;
4591 l_grade_id NUMBER;
4592
4593 CURSOR csr_grade_id
4594 IS
4595 SELECT information253
4596 FROM ben_copy_entity_results
4597 WHERE copy_entity_result_id = p_grade_cer_id
4598 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY';
4599
4600 CURSOR csr_gspine_id (p_grade_id IN NUMBER)
4601 IS
4602 SELECT grade_spine_id
4603 FROM per_grade_spines_f
4604 WHERE p_effective_date BETWEEN effective_start_date
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);
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
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,
4628 p_new_ceil => p_new_ceiling
4629 );
4630 END IF;
4631 END IF;
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;
4641 END;
4642
4643
4644 PROCEDURE unlink_step_or_point (p_copy_entity_result_id IN NUMBER)
4645 IS
4646 BEGIN
4647 UPDATE ben_copy_entity_results
4648 SET information104 = 'UNLINK'
4649 WHERE copy_entity_result_id = p_copy_entity_result_id;
4650 END unlink_step_or_point;
4651 --------------------------------------------------------------------------
4652 PROCEDURE chk_delete_option (
4653 p_copy_entity_txn_id IN NUMBER,
4654 p_opt_cer_id IN NUMBER,
4655 p_point_id IN NUMBER,
4656 p_opt_id IN NUMBER,
4657 p_pspine_id IN NUMBER,
4658 p_effective_date IN DATE
4659 )
4660 IS
4661 CURSOR csr_steps_for_point
4662 IS
4663 SELECT NULL
4664 FROM ben_copy_entity_results
4665 WHERE copy_entity_txn_id = p_copy_entity_txn_id
4666 AND information262 = p_opt_cer_id
4667 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4668 AND information104 = 'LINK';
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)
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);
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
4694 ---------------------------change_ceiling_step-----------------------------
4695 --
4696
4697 PROCEDURE change_ceiling_step (
4698 p_copy_entity_txn_id IN NUMBER,
4699 p_effective_date IN DATE,
4700 p_initial_ceiling_id IN NUMBER,
4701 p_final_ceiling_id IN NUMBER,
4702 p_grade_result_id IN NUMBER
4703 )
4704 IS
4705 /*
4706 Will throw error if there are employee placements above the new Ceiling Step.
4707 */
4708 CURSOR csr_init_step
4709 IS
4710 SELECT object_version_number, dml_operation
4711 FROM ben_copy_entity_results
4712 WHERE copy_entity_result_id = p_initial_ceiling_id;
4713
4714 CURSOR csr_final_step
4715 IS
4716 SELECT object_version_number, dml_operation, information253,
4717 information255, information263
4718 FROM ben_copy_entity_results
4719 WHERE copy_entity_result_id = p_final_ceiling_id;
4720
4721 CURSOR csr_grade
4722 IS
4723 SELECT object_version_number, dml_operation
4724 FROM ben_copy_entity_results
4725 WHERE copy_entity_result_id = p_grade_result_id;
4726
4727 l_initial_step csr_init_step%ROWTYPE;
4728 l_final_step csr_final_step%ROWTYPE;
4729 l_grade csr_grade%ROWTYPE;
4730 l_final_step_cer_id NUMBER;
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);
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 (
4755 'New Ceiling Sequence' || l_final_step.information263,
4756 45
4757 );
4758 if l_gspine_id is not null THEN
4759 per_grade_spines_pkg.chk_low_ceiling (
4760 p_val_start => p_effective_date,
4761 p_val_end => p_effective_date,
4762 p_gspine_id => l_gspine_id,
4763 p_new_ceil => l_final_step.information263
4764 );
4765 END IF;
4766 ben_copy_entity_results_api.update_copy_entity_results (
4767 p_copy_entity_result_id => p_final_ceiling_id,
4768 p_effective_date => p_effective_date,
4769 p_copy_entity_txn_id => p_copy_entity_txn_id,
4770 p_information98 => 'Y',
4771 p_object_version_number => l_final_step.object_version_number,
4772 p_information323 => NULL,
4773 p_dml_operation => get_dml_operation (
4774 l_final_step.dml_operation
4775 )
4776 );
4777 l_final_step_cer_id := p_final_ceiling_id;
4778 l_final_step_id := l_final_step.information253;
4779 ELSE
4780 l_final_step_id := NULL;
4781 l_final_step_cer_id := NULL;
4782 END IF;
4783
4784 CLOSE csr_final_step;
4785 OPEN csr_init_step;
4786 FETCH csr_init_step INTO l_initial_step;
4787
4788 IF (csr_init_step%FOUND)
4789 THEN
4790 ben_copy_entity_results_api.update_copy_entity_results (
4791 p_copy_entity_result_id => p_initial_ceiling_id,
4792 p_effective_date => p_effective_date,
4793 p_copy_entity_txn_id => p_copy_entity_txn_id,
4794 p_information98 => 'N',
4795 p_object_version_number => l_initial_step.object_version_number,
4796 p_dml_operation => get_dml_operation (
4797 l_initial_step.dml_operation
4798 ),
4799 p_information323 => NULL
4800 );
4801 END IF;
4802
4803 CLOSE csr_init_step;
4804 OPEN csr_grade;
4805 FETCH csr_grade INTO l_grade;
4806 CLOSE csr_grade;
4807 ben_copy_entity_results_api.update_copy_entity_results (
4808 p_copy_entity_result_id => p_grade_result_id,
4809 p_effective_date => p_effective_date,
4810 p_copy_entity_txn_id => p_copy_entity_txn_id,
4811 p_information103 => 'Y',
4812 p_information262 => l_final_step_cer_id,
4813 p_information259 => l_final_step_id,
4814 p_object_version_number => l_grade.object_version_number,
4815 p_information323 => NULL,
4816 p_dml_operation => get_dml_operation (
4817 l_grade.dml_operation
4818 )
4819 );
4820 END IF;
4821 -- Call to raise any errors on multi-message list
4822 hr_multi_message.end_validation_set;
4823 -- p_error_msg := l_msg;
4824 EXCEPTION
4825 WHEN OTHERS
4826 THEN
4827 fnd_msg_pub.ADD;
4828 END change_ceiling_step;
4829
4830 ---
4831 ------------CHK_STEPS_IN_GRADE ---
4832 --ggnanagu
4833 PROCEDURE chk_steps_in_grade (
4834 p_copy_entity_txn_id IN NUMBER,
4835 p_grade_result_id IN NUMBER,
4836 p_status OUT NOCOPY VARCHAR2
4837 )
4838 IS
4839 /*
4840 will return N if there are no steps attached
4841 will return Y if there are steps
4842 Will throw an Error if there are Employee placements..
4843 */
4844 CURSOR csr_steps_in_grade
4845 IS
4846 SELECT NULL
4847 FROM ben_copy_entity_results
4848 WHERE table_alias = 'COP'
4849 AND copy_entity_txn_id = p_copy_entity_txn_id
4850 AND gs_parent_entity_result_id = p_grade_result_id
4851 AND result_type_cd = 'DISPLAY'
4852 AND NVL (information104, 'PPP') <> 'UNLINK';
4853
4854 CURSOR csr_grade_scale_dets
4855 IS
4856 SELECT information253, information255
4857 FROM ben_copy_entity_results
4858 WHERE copy_entity_result_id = p_grade_result_id;
4859
4860
4861 cursor c1(p_pspine_id IN Number,
4862 p_grd_id IN NUMBER,
4863 p_effective_date IN DATE) is
4864 select 'x'
4865 from per_spinal_point_steps_f sps,
4866 per_grade_spines_f gs
4867 where gs.grade_spine_id = sps.grade_spine_id
4868 and gs.parent_spine_id = p_pspine_id
4869 and gs.grade_id = p_grd_id
4870 and exists
4871 (select null
4872 from per_spinal_point_placements_f sp
4873 where sp.step_id = sps.step_id
4874 and (p_effective_date between effective_start_date AND effective_end_date-1
4875 or effective_start_date >= p_effective_date ));
4876
4877
4878 --
4879 l_exists VARCHAR2(1);
4880 l_effdate DATE;
4881 l_steps_in_grade NUMBER;
4882 l_grade_scale_dets csr_grade_scale_dets%ROWTYPE;
4883 BEGIN
4884 hr_multi_message.enable_message_list;
4885
4886
4887 select action_date
4888 into l_effdate
4889 from pqh_copy_entity_txns
4890 where copy_entity_txn_id = p_copy_entity_txn_id;
4891
4892
4893
4894 OPEN csr_steps_in_grade;
4895 FETCH csr_steps_in_grade INTO l_steps_in_grade;
4896
4897 IF (csr_steps_in_grade%FOUND)
4898 THEN
4899 p_status := 'Y';
4900 OPEN csr_grade_scale_dets;
4901 FETCH csr_grade_scale_dets INTO l_grade_scale_dets;
4902
4903 IF csr_grade_scale_dets%FOUND
4904 THEN
4905 OPEN c1(p_pspine_id => l_grade_scale_dets.information255,
4906 p_grd_id => l_grade_scale_dets.information253,
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
4916
4917 /* per_grade_spines_pkg.stb_del_validation (
4918 p_pspine_id => l_grade_scale_dets.information255,
4919 p_grd_id => l_grade_scale_dets.information253
4920 );*/
4921 END IF;
4922
4923 CLOSE csr_grade_scale_dets;
4924 ELSE
4925 p_status := 'N';
4926 END IF;
4927
4928 CLOSE csr_steps_in_grade;
4929 -- Call to raise any errors on multi-message list
4930 hr_multi_message.end_validation_set;
4931 -- p_error_msg := l_msg;
4932 EXCEPTION
4933 WHEN OTHERS
4934 THEN
4935 fnd_msg_pub.ADD;
4936 END chk_steps_in_grade;
4937
4938
4939
4940 --
4941
4942 Function get_dflt_salary_rate
4943 (p_copy_entity_txn_id in Number,
4944 p_copy_entity_result_id in Number,
4945 p_rate_hgrid_node in varchar2)
4946 Return Number is
4947
4948 l_proc varchar2(72) := g_package||'.get_dflt_salary_rate';
4949
4950 l_get_which_rates varchar2(20) := get_which_rates(p_copy_entity_txn_id);
4951 dflt_sal_rate Number := null;
4952
4953
4954 cursor grd_rates(l_action_dt in date) is
4955 select copy_entity_txn_id, information297 from ben_copy_entity_results
4956 where
4957 copy_entity_txn_id = p_copy_entity_txn_id
4958 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4959 and information277 in (
4960 select copy_entity_result_id
4961 from ben_copy_entity_results
4962 where table_alias ='PLN'
4963 and result_type_cd = 'DISPLAY'
4964 and gs_mirror_src_entity_result_id = p_copy_entity_result_id)
4965 and table_alias = 'HRRATE' and l_action_dt
4966 between nvl(information2, hr_general.start_of_time) and nvl(information3, hr_general.end_of_time);
4967
4968 Cursor Csr_Action_Dt is
4969 Select Action_Date
4970 From Pqh_Copy_Entity_txns
4971 Where Copy_Entity_Txn_id = p_copy_entity_txn_id;
4972
4973 cursor point_cer is
4974 select information262 from ben_copy_entity_results
4975 where
4976 copy_entity_txn_id = p_copy_entity_txn_id
4977 and
4978 copy_entity_result_id = p_copy_entity_result_id;
4979
4980 cursor pnt_rates(point_cer_id number,l_action_dt in date) is
4981 select copy_entity_Txn_id, information297 from ben_copy_entity_results
4982 where
4983 copy_entity_txn_id = p_copy_entity_txn_id
4984 and information278 = point_cer_id
4985 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4986 and table_alias = 'HRRATE' and l_action_dt
4987
4988 between nvl(information2, hr_general.start_of_time) and nvl(information3, hr_general.end_of_time);
4989
4990 l_point_cer_id number := -1;
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;
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;
5016
5017 for each_pnt in pnt_rates(l_point_cer_id,l_action_date) loop
5018 dflt_sal_rate := each_pnt.information297;
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;
5028
5029 for each_pnt in pnt_rates(l_point_cer_id,l_action_date) 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;
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
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 ---------------------------------------------------------------------------
5053 -----------------------< Update_frps_point_rate >--------------------------
5054 ---------------------------------------------------------------------------
5055
5056 procedure update_frps_point_rate(p_point_cer_id in number,
5057 p_copy_entity_txn_id in number,
5058 p_business_group_id in number,
5059 p_salary_rate in number,
5060 p_gross_index in number,
5061 p_effective_date in date
5062 )
5063 IS
5064 l_salary_rate NUMBER;
5065 BEGIN
5066 if p_gross_index is null THEN
5067 l_salary_rate := p_salary_rate;
5068 ELSE
5069 l_salary_rate := pqh_corps_utility.get_salary_rate(p_gross_index => p_gross_index,
5070 p_effective_date =>p_effective_date,
5071 p_copy_entity_txn_id => p_copy_entity_txn_id);
5072 END IF;
5073
5074 pqh_gsp_hr_to_stage.update_frps_point_rate(p_point_cer_id => p_point_cer_id,
5075 p_copy_entity_txn_id => p_copy_entity_txn_id,
5076 p_business_group_id => p_business_group_id,
5077 p_point_value => l_salary_rate,
5078 p_effective_date => p_effective_date);
5079 END update_frps_point_rate;
5080
5081 ---------------------------------------------------------------------------------------------
5082 -----------------------------< CHK_FROM_STEPS >----------------------------------------------
5083 -------------------------------------------------------------------------------------------
5084 Function chk_from_steps(p_parent_spine_id IN per_parent_spines.parent_spine_id%TYPE)
5085 RETURN VARCHAR2
5086 IS
5087 l_status varchar2(1) := 'N';
5088 cursor csr_grade_id
5089 is
5090 select grade_id
5091 from per_grade_spines_f
5092 where parent_spine_id = p_parent_spine_id;
5093 Cursor Csr_Plan_id (p_grade_id IN NUMBER)
5094 is
5095 select pl_id
5096 from ben_pl_f
5097 where mapping_table_pk_id = p_grade_id
5098 and mapping_table_name = 'PER_GRADES';
5099 Cursor Csr_Pgm_id (p_pl_id IN NUMBER)
5100 is
5101 select pgm_id
5102 from ben_plip_f
5103 where pl_id = p_pl_id;
5104 Cursor Csr_Use_Points(p_pgm_id IN NUMBER)
5105 is
5106 select USE_PROG_POINTS_FLAG
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
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
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
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
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
5136 hr_utility.set_location('Changing the Status to : '||l_status,60);
5137 end if;
5138 END IF;
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';
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)
5151 return varchar2
5152 IS
5153 Cursor csr_is_steps
5154 Is
5155 select information18
5156 from ben_copy_entity_results
5157 where table_alias = 'PGM'
5158 and copy_entity_txn_id = p_copy_entity_txn_id
5159 and result_type_cd = 'DISPLAY';
5160 Cursor csr_crrate
5161 is
5162 select null
5163 from ben_copy_entity_results
5164 where information160 = p_crset_id
5165 and table_alias = 'CRRATE'
5166 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5167 and copy_entity_txn_id = p_copy_entity_txn_id
5168 and information169 in
5169 (select copy_entity_result_id
5170 from ben_copy_entity_results
5171 where table_alias = 'OPT'
5172 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5173 and information256 = p_scale_cer_id
5174 and copy_entity_txn_id = p_copy_entity_txn_id);
5175 l_crrate csr_crrate%ROWTYPE;
5176 l_is_steps csr_is_steps%ROWTYPE;
5177 begin
5178 OPEN csr_is_steps;
5179 FETCH Csr_is_steps into l_is_steps;
5180 if l_is_steps.information18 = 'N' THEN
5181 close csr_is_steps;
5182 RETURN 'Y';
5183 END IF;
5184 if p_crset_type <> 'POINT' then
5185 return 'Y';
5186 else
5187 open csr_crrate;
5188 fetch csr_crrate into l_crrate;
5189 if(csr_crrate%NOTFOUND)then
5190 return 'N';
5191 else
5192 return 'Y';
5193 end if;
5194 close csr_crrate;
5195 end if;
5196 end check_crset;
5197
5198 procedure change_scale_name(p_copy_entity_txn_id in number,p_pl_cer_id in number,p_short_name in varchar2)
5199 is
5200 Cursor csr_is_steps
5201 Is
5202 select information18
5203 from ben_copy_entity_results
5204 where table_alias = 'PGM'
5205 and copy_entity_txn_id = p_copy_entity_txn_id
5206 and result_type_cd = 'DISPLAY';
5207
5208 Cursor csr_plan_dtls
5209 Is
5210 select Gs_Mirror_Src_Entity_Result_Id,object_version_number
5211 from ben_copy_entity_results
5212 where copy_entity_result_id = p_pl_cer_id
5213 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5214 and copy_entity_txn_id = p_copy_entity_txn_id
5215 and table_alias = 'PLN';
5216
5217 Cursor csr_plip_dtls(p_plip_cer_id in number)
5218 Is
5219 select information258,copy_entity_result_id,dml_operation
5220 from ben_copy_entity_results
5221 where table_alias = 'CPP'
5222 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5223 and copy_entity_txn_id = p_copy_entity_txn_id
5224 and copy_entity_result_id = p_plip_cer_id;
5225
5226 Cursor csr_scale_dtls(p_scale_cer_id in number)
5227 Is
5228 Select Information98,copy_entity_result_id,dml_operation
5229 from ben_copy_entity_results
5230 where copy_entity_result_id = p_scale_cer_id
5231 and table_alias = 'SCALE'
5232 and copy_entity_txn_id = p_copy_entity_txn_id;
5233
5234 l_plan_dtls csr_plan_dtls%ROWTYPE;
5235 l_plip_dtls csr_plip_dtls%ROWTYPE;
5236 l_scale_dtls csr_scale_dtls%ROWTYPE;
5237 l_is_steps csr_is_steps%ROWTYPE;
5238 begin
5239
5240 OPEN csr_is_steps;
5241 FETCH Csr_is_steps into l_is_steps;
5242 if l_is_steps.information18 = 'Y' THEN
5243 RETURN;
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);
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);
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);
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)
5276 where copy_entity_result_id = l_scale_dtls.copy_entity_result_id;
5277
5278 update ben_copy_entity_results
5279 set information98 = p_short_name
5280 ,dml_operation = get_dml_operation(l_plip_dtls.dml_operation)
5281 where copy_entity_result_id = l_plip_dtls.copy_entity_result_id;
5282
5283 end if;
5284 END IF;
5285 END IF;
5286 END IF;
5287 END IF;
5288 END change_scale_name;
5289
5290 procedure remove_steps(p_copy_entity_txn_id IN NUMBER, p_grade_result_id IN NUMBER)
5291 IS
5292 CURSOR csr_steps_in_grade
5293 IS
5294 SELECT copy_entity_result_id,
5295 dml_operation
5296 FROM ben_copy_entity_results
5297 WHERE table_alias = 'COP'
5298 AND copy_entity_txn_id = p_copy_entity_txn_id
5299 AND gs_parent_entity_result_id = p_grade_result_id
5300 AND result_type_cd = 'DISPLAY'
5301 AND NVL (information104, 'PPP') <> 'UNLINK';
5302
5303 Cursor Csr_Action_Dt is
5304 Select Action_Date
5305 From Pqh_Copy_Entity_txns
5306 Where Copy_Entity_Txn_id = p_copy_entity_txn_id;
5307
5308 l_Action_Date date;
5309 begin
5310
5311 Open Csr_Action_Dt;
5312 Fetch Csr_Action_Dt Into L_Action_Date;
5313 Close Csr_Action_Dt;
5314
5315 for rec_steps in csr_steps_in_grade loop
5316
5317 /* update ben_copy_entity_results
5318 set copy_entity_result_id = rec_steps.copy_entity_result_id,
5319 information104 = 'UNLINK',
5320 dml_operation = get_dml_operation(rec_steps.dml_operation)
5321 where copy_entity_result_id = rec_steps.copy_entity_result_id; */
5322
5323 Pqh_Gsp_Grd_Step_Remove.REMOVE_OIPL(P_COPY_ENTITY_TXN_ID => p_copy_entity_txn_id
5324 ,P_COPY_ENTITY_RESULT_ID => rec_steps.copy_entity_result_id
5325 ,P_EFFECTIVE_DATE => Nvl(L_Action_Date, hr_general.Effective_Date));
5326
5327 end loop;
5328
5329
5330 end remove_steps;
5331 procedure change_rates_date(p_copy_entity_txn_id in number,p_pl_cer_id in number,p_start_date in DATE)
5332 is
5333 Cursor csr_plan_dtls
5334 Is
5335 select information307
5336 from ben_copy_entity_results
5337 where copy_entity_result_id = p_pl_cer_id
5338 and copy_entity_txn_id = p_copy_entity_txn_id
5339 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5340 and table_alias = 'PLN';
5341
5342 Cursor Csr_rate_details(p_plan_old_date in date)
5343 is
5344 select information2,information3,copy_entity_result_id
5345 from ben_copy_entity_results
5346 where information277 =p_pl_cer_id
5347 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5348 and table_alias in ('HRRATE','ABR')
5349 and information2 = p_plan_old_date
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;
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;
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;
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);
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
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);
5399 end if;
5400
5401 END change_rates_date;
5402
5403 -----------
5404 FUNCTION GET_CURRENCY_CODE(p_copy_entity_txn_id in number)
5405 RETURN varchar2 IS
5406 /*
5407 Author : ggnanagu
5408 Purpose : This function returns the Currency code associated with the Grade Ladder
5409 */
5410 l_result Varchar2(20);
5411 l_proc varchar2(72) := g_package||'USE_POINT_OR_STEP';
5412
5413 Cursor csr_currency_code
5414 IS
5415 Select nvl(grdldr.INFORMATION50,'USD') /* INFORMATION50 -> Currency Code */
5416 FROM Ben_Copy_Entity_Results grdldr
5417 WHERE grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
5418 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5419 AND grdldr.Table_Alias = 'PGM';
5420
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;
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
5439 hr_utility.set_location('Leaving '||l_proc,30);
5440 end if;
5441
5442 EXCEPTION
5443 When others THEN
5444 l_result := 'STEP';
5445 return l_result;
5446
5447 END GET_CURRENCY_CODE;
5448
5449 --
5450 FUNCTION get_grd_start_date
5451 (p_copy_entity_result_id in ben_copy_entity_results.copy_entity_result_id%TYPE)
5452 RETURN DATE
5453 IS
5454 l_grd_start_date DATE;
5455
5456 begin
5457
5458 select information307
5459 into l_grd_start_date
5460 from ben_copy_entity_results
5461 where copy_entity_result_id = p_copy_entity_result_id;
5462
5463
5464 RETURN l_grd_start_date;
5465 END get_grd_start_date;
5466 procedure change_start_step(p_copy_entity_txn_id in number
5467 ,p_init_start_step in number
5468 ,p_final_start_step in number
5469 ,p_grade_result_id in number
5470 )
5471 is
5472 Cursor csr_indicator
5473 is
5474 select information103
5475 from ben_copy_entity_results
5476 where copy_entity_result_id =p_grade_result_id;
5477 l_indicator varchar2(10);
5478 begin
5479 open csr_indicator;
5480 fetch csr_indicator into l_indicator;
5481 close csr_indicator;
5482 if nvl(l_indicator,'X') = 'Y'
5483 then l_indicator := 'B';
5484 else
5485 l_indicator := 'S';
5486 end if;
5487
5488
5489 update ben_copy_entity_results
5490 set information228 = p_final_start_step
5491 ,information103 = l_indicator
5492 where copy_entity_result_id = p_grade_result_id;
5493 end change_start_step;
5494
5495 --
5496 FUNCTION get_formula_name (p_formula_id IN NUMBER, p_effective_date IN DATE)
5497 RETURN VARCHAR2
5498 IS
5499 CURSOR csr_formula_name
5500 IS
5501 SELECT ff.formula_name
5502 FROM ff_formulas_f ff
5503 WHERE ff.formula_id = p_formula_id
5504 AND p_effective_date BETWEEN ff.effective_start_date
5505 AND ff.effective_end_date;
5506
5507 l_formula_name ff_formulas_f.formula_name%TYPE;
5508 BEGIN
5509
5510 OPEN csr_formula_name;
5511 FETCH csr_formula_name INTO l_formula_name;
5512 CLOSE csr_formula_name;
5513
5514 RETURN l_formula_name;
5515
5516 END get_formula_name;
5517
5518 FUNCTION get_element_name (p_element_type_id IN NUMBER)
5519 RETURN VARCHAR2
5520 IS
5521 CURSOR csr_element_name
5522 IS
5523 SELECT ettl.element_name
5524 FROM pay_element_types_f_tl ettl
5525 WHERE ettl.LANGUAGE = USERENV ('LANG')
5526 AND ettl.element_type_id = p_element_type_id;
5527
5528 l_element_name pay_element_types_f_tl.element_name%TYPE;
5529 BEGIN
5530
5531 OPEN csr_element_name;
5532 FETCH csr_element_name INTO l_element_name;
5533 CLOSE csr_element_name;
5534
5535 RETURN l_element_name;
5536
5537 END get_element_name;
5538
5539 FUNCTION get_input_val_name (p_input_value_id IN NUMBER)
5540 RETURN VARCHAR2
5541 IS
5542 CURSOR csr_input_val_name
5543 IS
5544 SELECT ptl.NAME
5545 FROM pay_input_values_f_tl ptl
5546 WHERE ptl.LANGUAGE = USERENV ('LANG')
5547 AND ptl.input_value_id = p_input_value_id;
5548
5549 l_input_val_name pay_input_values_f_tl.NAME%TYPE;
5550 BEGIN
5551
5552 OPEN csr_input_val_name;
5553 FETCH csr_input_val_name INTO l_input_val_name;
5554 CLOSE csr_input_val_name;
5555
5556 RETURN l_input_val_name;
5557
5558 END get_input_val_name;
5559 --
5560 FUNCTION get_bg_currency(p_business_group_id in number) RETURN varchar2
5561 is
5562 begin
5563 return Hr_General.DEFAULT_CURRENCY_CODE(p_Business_group_Id);
5564 end get_bg_currency;
5565 --
5566 PROCEDURE chk_grd_ldr_details (
5567 p_business_group_id IN NUMBER,
5568 p_name IN VARCHAR2,
5569 p_dflt_pgm_flag IN VARCHAR2,
5570 p_pgm_id IN NUMBER,
5571 p_effective_date IN DATE
5572 )
5573 IS
5574 CURSOR csr_name
5575 IS
5576 SELECT NULL
5577 FROM ben_pgm_f
5578 WHERE NAME = p_name
5579 AND business_group_id = p_business_group_id
5580 AND pgm_id <> NVL (p_pgm_id, -1);
5581
5582 CURSOR csr_dflt_grd_ldr
5583 IS
5584 SELECT NAME
5585 FROM ben_pgm_f
5586 WHERE dflt_pgm_flag = 'Y'
5587 AND business_group_id = p_business_group_id
5588 AND pgm_id <> NVL (p_pgm_id, -1)
5589 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
5590
5591 l_pgm_name ben_pgm_f.NAME%TYPE;
5592 l_dummy VARCHAR2 (10);
5593 BEGIN
5594 hr_multi_message.enable_message_list;
5595
5596 OPEN csr_name;
5597
5598 FETCH csr_name
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;
5608
5609 IF p_dflt_pgm_flag = 'Y'
5610 THEN
5611 OPEN csr_dflt_grd_ldr;
5612
5613 FETCH csr_dflt_grd_ldr
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
5623 CLOSE csr_dflt_grd_ldr;
5624 END IF;
5625 hr_multi_message.end_validation_set;
5626 EXCEPTION
5627 WHEN hr_multi_message.error_message_exist
5628 THEN
5629 NULL;
5630 WHEN OTHERS
5631 THEN
5632 RAISE;
5633 END chk_grd_ldr_details;
5634 --------------------------------------------------------
5635 --------------< get_gl_ann_factor >---------------------
5636 --------------------------------------------------------
5637 function get_gl_ann_factor(p_pgm_id in number)
5638 return varchar2
5639 IS
5640 Cursor csr_gl_ann_factor IS
5641 SELECT pgi_information5 rate_ann_factor
5642 FROM ben_pgm_extra_info
5643 WHERE pgm_id = p_pgm_id
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;
5653 hr_utility.set_location(' Leaving:'||l_proc, 10);
5654
5655 Exception
5656 WHEN Others THEN
5657 raise;
5658 END get_gl_ann_factor;
5659
5660
5661 PROCEDURE upd_ceiling_info(p_grade_cer_id IN NUMBER, p_step_id IN number)
5662 IS
5663 BEGIN
5664
5665 update ben_copy_entity_results
5666 set information259 = p_step_id,
5667 information103 = 'Y'
5668 where copy_entity_result_id = p_grade_cer_id
5669 and table_alias = 'CPP';
5670
5671 END upd_ceiling_info;
5672
5673 --
5674 End pqh_gsp_utility;
5675
[Home] [Help]