1 Package body pqh_gsp_utility as
2 /* $Header: pqgsputl.pkb 120.15 2011/05/09 10:15:14 lbodired noship $ */
3
4 g_package Varchar2(30) := 'pqh_gsp_utility.';
5 g_debug boolean := hr_utility.debug_enabled;
6
7 --
8 ---------------------------get_gsp_plntyp_str_date-----------------------------
9 --
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 -- Start changes for bug 9328526
70 --
71 ------------------------chk_ps_for_upd_del------------------
72 --
73 procedure chk_ps_for_upd_del(
74 p_parent_spine_id in number
75 ,p_entity in varchar2
76 ) is
77 --
78 Cursor chk_ps_gsp_association is
79 select
80 pg.name
81 ,bpgm.name
82 from
83 ben_pgm_f bpgm,
84 ben_pl_f bp,
85 BEN_PL_TYP_F bpt,
86 ben_plip_f bplip,
87 per_grade_spines_f pgs,
88 per_grades pg
89 where
90 bp.pl_typ_id = bpt.pl_typ_id
91 and bpt.OPT_TYP_CD = 'GSP'
92 and bp.mapping_table_name = 'PER_GRADES'
93 and bp.pl_id = bplip.pl_id
94 and bplip.pgm_id = bpgm.pgm_id
95 and pgs.grade_id = bp.mapping_table_pk_id
96 and pgs.parent_spine_id = p_parent_spine_id
97 and pg.grade_id = pgs.grade_id;
98 --
99
100 l_gname per_grades.name%type;
101 l_pgm_name ben_pgm_f.name%type;
102
103 l_proc varchar2(72) := g_package||'chk_ps_for_upd_del';
104
105 begin
106 --
107 hr_utility.set_location('Entering '||l_proc,10);
108
109 open chk_ps_gsp_association;
110 fetch chk_ps_gsp_association into l_gname,l_pgm_name;
111 if chk_ps_gsp_association%found then
112 close chk_ps_gsp_association;
113 hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
114 hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
115 hr_utility.set_message_token('GRADE_NAME',l_gname);
116 hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
117 hr_utility.raise_error;
118 else
119 close chk_ps_gsp_association;
120 end if;
121
122 hr_utility.set_location('Leaving '||l_proc,100);
123 --
124 end;
125
126 --
127 ------------------------chk_ps_for_upd_del------------------
128 --
129 procedure chk_rate_for_upd_del(
130 p_rate_id in number
131 ,p_entity in varchar2
132 ) is
133 --
134 Cursor chk_ps_gsp_association is
135 select
136 pg.name
137 ,bpgm.name
138 from
139 ben_pgm_f bpgm,
140 ben_pl_f bp,
141 BEN_PL_TYP_F bpt,
142 ben_plip_f bplip,
143 per_grade_spines_f pgs,
144 per_grades pg,
145 pay_rates pr,
146 per_parent_spines pps
147 where
148 bp.pl_typ_id = bpt.pl_typ_id
149 and bpt.OPT_TYP_CD = 'GSP'
150 and bp.mapping_table_name = 'PER_GRADES'
151 and bp.pl_id = bplip.pl_id
152 and bplip.pgm_id = bpgm.pgm_id
153 and pgs.grade_id = bp.mapping_table_pk_id
154 and pr.rate_id = p_rate_id
155 and pg.grade_id = pgs.grade_id
156 and pr.parent_spine_id = pgs.parent_spine_id
157 and pr.rate_type = 'SP'
158 and pr.name = pps.name
159 and pps.parent_spine_id = pgs.parent_spine_id;
160 --
161 l_gname per_grades.name%type;
162 l_pgm_name ben_pgm_f.name%type;
163 l_proc varchar2(72) := g_package||'chk_rate_for_upd_del';
164 --
165 begin
166 --
167 hr_utility.set_location('Entering '||l_proc,10);
168
169 open chk_ps_gsp_association;
170 fetch chk_ps_gsp_association into l_gname,l_pgm_name;
171 if chk_ps_gsp_association%found then
172 close chk_ps_gsp_association;
173 hr_utility.set_message(800,'PER_449871_GSPW_ENTITY');
174 hr_utility.set_message_token('ENTITY',lower(replace(p_entity,'_',' ')));
175 hr_utility.set_message_token('GRADE_NAME',l_gname);
176 hr_utility.set_message_token('GRADE_LADDER_NAME',l_pgm_name);
177 hr_utility.raise_error;
178 else
179 close chk_ps_gsp_association;
180 end if;
181
182 hr_utility.set_location('Leaving '||l_proc,100);
183 --
184 end;
185 -- End changes for bug 9328526
186
187 --
188 ---------------------------chk_grade_exist_in_gl-----------------------------
189 --
190
191 FUNCTION chk_grade_exist_in_gl
192 (
193 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_result_id%TYPE
194 )
195 RETURN VARCHAR2 IS
196 /*
197 Author : mvankada
198 Purpose : This function checks whether any Grade(s) attached to Grade Later or not.
199 If atleast one Grade has attached to Grade Ladder then this funtion returns 'Y' else 'N'
200
201 Used in : This function call is used in Grade HGrid page to enable/disable or
202 To throw error Message if No grades are attached to Grade Ladder
203 1) Progression Order Icon
204 2) Continue to Next Task button
205
206 */
207
208 CURSOR csr_grade_in_gl
209 IS
210 Select copy_entity_result_id
211 From ben_copy_entity_results
212 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
213 AND Table_Alias = 'CPP'
214 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
215 AND nvl(Information104,'PPP') <> 'UNLINK';
216
217 l_cer number;
218 l_proc varchar2(72) := g_package||'chk_grade_exist_in_gl';
219
220 BEGIN
221 if g_debug then
222 hr_utility.set_location('Entering '||l_proc,10);
223 hr_utility.set_location('p_copy_entity_txn_id '||l_proc,20);
224 end if;
225
226 Open csr_grade_in_gl;
227 Fetch csr_grade_in_gl into l_cer;
228 if csr_grade_in_gl%found then
229 return 'Y';
230 Else
231 return 'N';
232 End If;
233 Close csr_grade_in_gl;
234 EXCEPTION
235 WHEN OTHERS THEN
236 return 'N';
237 End chk_grade_exist_in_gl;
238
239 --
240 ---------------------------remove_grade_from_grdldr-----------------------------
241 --
242
243 Procedure remove_grade_from_grdldr
244 (
245 p_Grade_Result_Id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
246 p_Copy_Entity_Txn_Id IN ben_copy_entity_results.copy_entity_txn_id%TYPE,
247 p_Business_Group_Id IN Number,
248 p_Effective_Date IN Date,
249 p_Rec_Exists OUT NOCOPY Varchar2
250 ) IS
251
252 /*
253 Author : mvankada
254 Purpose :
255 I) This procedure checks whether rec to be removed is in Staging Area Only or in Main Tables
256 p_rec_exists has values :
257 =======================
258 MAIN Main Table
259 STAGE Staging Area
260 II) If the record to be removed is in Main Tables then this procedure also checks whether
261 the grade is attached to the employee or not. If attached then raise the error.
262 */
263
264 l_pgm_id Number;
265 l_grade_id Number;
266 l_assg_id Number;
267 l_dummy Char(1);
268 l_grade_name Ben_Copy_Entity_Results.Information5%Type;
269 l_proc varchar2(72) := g_package||'remove_grade_from_grdldr';
270 l_message_type varchar2(10) := 'W';
271 l_warnings_rec pqh_utility.warnings_rec;
272 l_business_area varchar2(50) := 'PQH_GSP_TASK_LIST';
273 l_crpth_hier_ver number;
274 l_corps_id number;
275 l_grade_crpath_node number;
276
277 -- To Get Pgm_Id of PGM based on Txn_id
278 Cursor csr_pgm_id
279 IS
280 Select grdldr.Information1
281 From Ben_Copy_Entity_Results grdldr
282 Where grdldr.Table_Alias = 'PGM'
283 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
284 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
285 And Information4 = p_Business_Group_Id;
286
287 -- To Get Corps_definition_Id of CPD based on Txn_id
288
289 Cursor csr_corps_id
290 IS
291 Select grdldr.Information1
292 From Ben_Copy_Entity_Results grdldr
293 Where grdldr.Table_Alias = 'CPD'
294 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
295 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
296 And Information4 = p_Business_Group_Id;
297
298
299
300 -- To Get Grade_Id (information253) of CPP based on Result_id of CPP
301 Cursor csr_grade_id
302 IS
303 Select grd.Information253,
304 grd.Information5
305 From Ben_Copy_Entity_Results grd
306 Where grd.Table_Alias = 'CPP'
307 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
308 And grd.Copy_Entity_Result_Id = p_Grade_Result_Id;
309
310
311
312 -- Check Grade and Grade Ladder is attached to an employee
313 Cursor csr_gl_grade_assg_emp (l_pgm_id Number, l_grade_id Number)
314 IS
315 Select assignment_id
316 From per_all_assignments_f assgt
317 Where grade_id = l_grade_id
318 AND Grade_Ladder_Pgm_Id = l_pgm_id
319 AND p_Effective_Date BETWEEN assgt.effective_start_date
320 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
321 AND assgt.business_group_id = p_Business_Group_Id;
322
323
324 -- Check Grade is attached to an employee
325 Cursor csr_grd_assg_emp (l_grade_id Number)
326 IS
327 Select Null
328 From per_all_assignments_f assgt
329 Where grade_id = l_grade_id
330 AND p_Effective_Date BETWEEN assgt.effective_start_date
331 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
332 AND assgt.business_group_id = p_Business_Group_Id;
333
334
335 Cursor csr_default_grdldr
336 IS
337 Select Null
338 From ben_pgm_f
339 Where pgm_id = l_pgm_id
340 And pgm_typ_cd = 'GSP'
341 And Dflt_Pgm_Flag = 'Y'
342 And business_group_id = p_business_group_id
343 And p_Effective_Date Between Effective_Start_date
344 And nvl(Effective_End_Date,hr_general.end_of_time);
345
346 Cursor csr_grade_crpath_node
347 IS
348 select null
349 from per_gen_hierarchy_nodes
350 where hierarchy_version_id = l_crpth_hier_ver
351 and information9 = l_corps_id
352 and information23 = l_grade_id;
353
354
355 BEGIN
356 if g_debug then
357 hr_utility.set_location('Entering '||l_proc,10);
358 hr_utility.set_location('p_Grade_Result_Id '||p_Grade_Result_Id,20);
359 hr_utility.set_location('p_Copy_Entity_Txn_Id '||p_Copy_Entity_Txn_Id,30);
360 hr_utility.set_location('p_Effective_Date '||p_Effective_Date,40);
361 hr_utility.set_location('p_Business_Group_Id '||p_business_group_id,50);
362 end if;
363
364 hr_multi_message.enable_message_list;
365
366 -- Get Pgm_Id
367 Open csr_pgm_id;
368 Fetch csr_pgm_id into l_pgm_id;
369 Close csr_pgm_id;
370
371 if g_debug then
372 hr_utility.set_location('Pgm_Id '||l_pgm_id,70);
373 end if;
374
375 -- Get Grade Id
376 Open csr_grade_id;
377 Fetch csr_grade_id into l_grade_id,l_grade_name;
378 Close csr_grade_id;
379
380 if g_debug then
381 hr_utility.set_location('Grade Id '||l_grade_id,80);
382 hr_utility.set_location('Grade Name '||l_grade_name,81);
383 end if;
384
385 IF (l_grade_id is NOT NULL AND l_pgm_id IS NOT NULL ) THEN
386
387 -- If the Grade Ladder is Default Grade Ladder, if the employee placements exists
388 -- on grade then raise warning message.
389 Open csr_default_grdldr;
390 Fetch csr_default_grdldr into l_dummy;
391 If csr_default_grdldr%Found Then
392 if g_debug then
393 hr_utility.set_location('Grade Ladder is Default Grade Ladder',85);
394 end if;
395 Close csr_default_grdldr;
396
397 Open csr_grd_assg_emp(l_grade_id);
398 Fetch csr_grd_assg_emp into l_dummy;
399 IF csr_grd_assg_emp%Found Then
400 Close csr_grd_assg_emp;
401 if g_debug then
402 hr_utility.set_location('Employe placements exist on the Default Grade Ladder Grades',87);
403 end if;
404 if g_debug then
405 hr_utility.set_location('Grade Name '||l_grade_name,88);
406 end if;
407 p_rec_exists := l_grade_name;
408 Else
409 p_rec_exists := 'MAIN';
410 Close csr_grd_assg_emp;
411 End if;
412 Else
413 p_rec_exists := 'MAIN';
414 Close csr_default_grdldr;
415
416
417 Open csr_gl_grade_assg_emp(l_pgm_id, l_grade_id);
418 Fetch csr_gl_grade_assg_emp into l_assg_id;
419 if csr_gl_grade_assg_emp%found then
420 if g_debug then
421 hr_utility.set_location(' Employe placements exist on the Grades',90);
422 end if;
423 hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRD');
424 hr_utility.raise_error;
425 end if;
426 Close csr_gl_grade_assg_emp;
427
428 l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
429 if l_business_area = 'PQH_CORPS_TASK_LIST' then
430 -- Get Corps_Id
431 Open csr_corps_id;
432 Fetch csr_corps_id into l_corps_id;
433 Close csr_corps_id;
434
435 if l_corps_id is not null then
436
437 l_crpth_hier_ver := pqh_cpd_hr_to_stage.get_crpth_hier_ver;
438
439 OPEN csr_grade_crpath_node;
440 FETCH csr_grade_crpath_node into l_grade_crpath_node;
441 if csr_grade_crpath_node%found then
442 if g_debug then
443 hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
444 end if;
445 hr_utility.set_message(8302,'PQH_GSP_GRD_PART_OF_CRPATH');
446 hr_utility.raise_error;
447 end if;
448 CLOSE csr_grade_crpath_node;
449 end if;
450 end if;
451
452
453 END IF;
454
455 ELSE
456 p_rec_exists := 'STAGE';
457 END IF;
458 if g_debug then
459 hr_utility.set_location('p_rec_exists :'||p_rec_exists,99);
460 hr_utility.set_location('Leaving '||l_proc,100);
461 end if;
462 EXCEPTION
463 WHEN others THEN
464 p_rec_exists := 'STAGE';
465 fnd_msg_pub.add;
466 End remove_grade_from_grdldr;
467
468 --
469 ---------------------------GET_PGM_TYP-----------------------------
470 --
471 FUNCTION GET_PGM_TYP (p_cpy_enty_txn_id in number)
472 RETURN varchar2 is
473 BEGIN
474 RETURN 'GSP' ;
475 EXCEPTION
476 WHEN others THEN
477
478 return 'GSP' ;
479 END;
480
481 --
482 ---------------------------ENABLE_DISABLE_START_ICON-----------------------------
483 --
484 FUNCTION ENABLE_DISABLE_START_ICON(p_gsp_node in varchar2,
485 p_copy_enty_txn_id in number,
486 p_table_alias in varchar2)
487 RETURN varchar2 is
488 st_icon varchar2(10) := 'N';
489 prev_task varchar2(10);
490 l_table_alias varchar2(50) := 'PQH_GSP_TASK_LIST';
491 --
492 l_proc varchar2(72) := g_package||'enable_disable_start_icon';
493 --
494 BEGIN
495 if g_debug then
496 hr_utility.set_location('Entering '||l_proc,5);
497 hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
498 hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
499 hr_utility.set_location('p_table_alias is '||p_table_alias,15);
500 end if;
501
502 if (p_table_alias is not null) then
503 l_table_alias := p_table_alias;
504 end if;
505
506 if p_gsp_node = '1' then
507 st_icon := 'Y';
508 else
509 select nvl(pa.decode_function_name, '99') into prev_task from
510 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
511 where
512 bcer.table_route_id = ptr.table_route_id
513 and ptr.table_route_id = pa.master_table_route_id
514 and ptr.table_alias = l_table_alias
515 and pa.attribute_name = p_gsp_node
516 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
517
518 st_icon := get_status(prev_task, p_copy_enty_txn_id, l_table_alias);
519 end if;
520
521 if g_debug then
522 hr_utility.set_location('Leaving Successfully'||l_proc,5);
523 end if;
524
525 RETURN st_icon;
526 EXCEPTION WHEN others THEN
527 if g_debug then
528 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
529 end if;
530 return 'N';
531 END;
532 --
533 --
534 ---------------------------GET_STATUS-----------------------------
535 --
536 FUNCTION GET_STATUS(p_gsp_node in varchar2,
537 p_copy_enty_txn_id in number,
538 p_table_alias in varchar2)
539 RETURN varchar2 is
540 st_icon varchar2(10) := 'Y';
541 l_table_alias varchar2(50) := 'PQH_GSP_TASK_LIST';
542 --
543 l_proc varchar2(72) := g_package||'get_status';
544 --
545 BEGIN
546 if g_debug then
547 hr_utility.set_location('Entering '||l_proc,5);
548 hr_utility.set_location('p_gsp_node is '||p_gsp_node,5);
549 hr_utility.set_location('p_copy_entity_txn_id is '||p_copy_enty_txn_id,10);
550 hr_utility.set_location('p_table_alias is '||p_table_alias,15);
551 end if;
552
553 if (p_table_alias is not null) then
554 l_table_alias := p_table_alias;
555 end if;
556
557 if l_table_alias = 'PQH_CORPS_TASK_LIST' Then
558
559 st_icon := pqh_corps_utility.get_cpd_status(p_node_number => p_gsp_node,
560 p_copy_entity_txn_id => p_copy_enty_txn_id);
561 Return st_icon;
562
563 End If;
564 if p_gsp_node = '1' then
565 select bcer.information100 into st_icon from
566 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
567 where
568 bcer.table_route_id = ptr.table_route_id
569 and ptr.table_route_id = pa.master_table_route_id
570 and ptr.table_alias = l_table_alias
571 and pa.attribute_name = p_gsp_node
572 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
573 elsif p_gsp_node = '2' then
574 select bcer.information101 into st_icon from
575 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
576 where
577 bcer.table_route_id = ptr.table_route_id
578 and ptr.table_route_id = pa.master_table_route_id
579 and ptr.table_alias = l_table_alias
580 and pa.attribute_name = p_gsp_node
581 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
582 elsif p_gsp_node = '3' then
583 select bcer.information102 into st_icon from
584 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
585 where
586 bcer.table_route_id = ptr.table_route_id
587 and ptr.table_route_id = pa.master_table_route_id
588 and ptr.table_alias = l_table_alias
589 and pa.attribute_name = p_gsp_node
590 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
591 elsif p_gsp_node = '4'then
592 select bcer.information103 into st_icon from
593 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
594 where
595 bcer.table_route_id = ptr.table_route_id
596 and ptr.table_route_id = pa.master_table_route_id
597 and ptr.table_alias = l_table_alias
598 and pa.attribute_name = p_gsp_node
599 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
600 elsif p_gsp_node = '5' then
601 select bcer.information104 into st_icon from
602 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
603 where
604 bcer.table_route_id = ptr.table_route_id
605 and ptr.table_route_id = pa.master_table_route_id
606 and ptr.table_alias = l_table_alias
607 and pa.attribute_name = p_gsp_node
608 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
609 elsif p_gsp_node = '6' then
610 select bcer.information105 into st_icon from
611 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
612 where
613 bcer.table_route_id = ptr.table_route_id
614 and ptr.table_route_id = pa.master_table_route_id
615 and ptr.table_alias = l_table_alias
616 and pa.attribute_name = p_gsp_node
617 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
618 elsif p_gsp_node = '7' then
619 select bcer.information106 into st_icon from
620 ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
621 where
622 bcer.table_route_id = ptr.table_route_id
623 and ptr.table_route_id = pa.master_table_route_id
624 and ptr.table_alias = l_table_alias
625 and pa.attribute_name = p_gsp_node
626 and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
627 else
628 st_icon := 'N';
629 end if;
630 if g_debug then
631 hr_utility.set_location('Leaving Successfully'||l_proc,5);
632 end if;
633 RETURN st_icon;
634 EXCEPTION
635 WHEN others THEN
636 if g_debug then
637 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
638 end if;
639 return 'N';
640 END;
641
642 --
643 ---------------------------USE_POINT_OR_STEP-----------------------------
644 --
645 FUNCTION USE_POINT_OR_STEP(p_copy_entity_txn_id in number)
646 RETURN varchar2 IS
647 /*
648 Author : mvankada
649 Purpose : This Function checks whether Grade Ladder is using Steps or Points
650 and returns the values STEP/POINT
651 */
652 l_result Varchar2(20);
653 l_proc varchar2(72) := g_package||'USE_POINT_OR_STEP';
654
655 Cursor csr_use_points
656 IS
657 Select Decode(nvl(grdldr.INFORMATION18,'N'),'Y','POINT','STEP') /* INFORMATION18 -> Use Progression Points */
658 FROM Ben_Copy_Entity_Results grdldr
659 WHERE grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
660 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
661 AND grdldr.Table_Alias = 'PGM';
662
663
664 BEGIN
665
666 if g_debug then
667 hr_utility.set_location('Leaving '||l_proc,10);
668 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
669 end if;
670
671 Open csr_use_points;
672 Fetch csr_use_points into l_result;
673 Close csr_use_points;
674
675 if g_debug then
676 hr_utility.set_location('l_result '||l_result,25);
677 end if;
678 return l_result;
679
680 if g_debug then
681 hr_utility.set_location('Leaving '||l_proc,30);
682 end if;
683
684 EXCEPTION
685 When others THEN
686 l_result := 'STEP';
687 return l_result;
688
689 END USE_POINT_OR_STEP;
690
691 --
692 ---------------------------remove_step_from_grade-----------------------------
693 --
694 Procedure remove_step_from_grade
695 (
696 p_step_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
697 p_copy_entity_txn_id IN number,
698 p_effective_date IN Date,
699 p_use_points IN varchar2,
700 p_step_id IN ben_copy_entity_results.information1%TYPE,
701 p_celing_step_flag IN varchar2,
702 p_rec_exists OUT NOCOPY Varchar2
703 ) IS
704
705
706 /*
707 Author : mvankada
708 Purpose :
709 1) This procedure is used to Check whether Record to be removed is in Staging Area Only
710 or in Main Tables
711 p_rec_exists has values :
712 =======================
713 MAIN Main Table
714 STAGE Staging Area
715
716 II) If the Record to be removed is the HR/BEN Tables and if the Step is
717 1) Ceiling Step
718 2) Special Ceiling Step
719 3) Employee Placements on this Step
720 then raise the error.
721
722 */
723
724
725 l_business_area varchar2(50) := 'PQH_GSP_TASK_LIST';
726 l_crpth_hier_ver number;
727 l_corps_id number;
728 l_grade_crpath_node number;
729 l_pgm_id Number;
730 l_grade_id Number;
731 l_proc varchar2(72) := g_package||'remove_step_from_grade';
732
733 -- To Get information1 (Pgm_ID) of PGM, based on TXN_ID
734 Cursor csr_pgm_id
735 IS
736 Select grdldr.information1 -- PGM_ID
737 From Ben_Copy_Entity_Results grdldr
738 Where grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
739 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
740 AND grdldr.Table_Alias = 'PGM';
741
742 -- To Get Corps_definition_Id of CPD based on Txn_id
743
744 Cursor csr_corps_id
745 IS
746 Select grdldr.Information1
747 From Ben_Copy_Entity_Results grdldr
748 Where grdldr.Table_Alias = 'CPD'
749 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
750 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id;
751
752 -- To Get information253 (Grade_id) of CPP, based on Step Result Id
753 Cursor csr_grade_id
754 IS
755 Select grd.information253
756 From Ben_Copy_Entity_Results grd
757 Where grd.Table_Alias = 'CPP'
758 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
759 AND grd.copy_entity_result_id = (Select step.gs_parent_entity_result_id
760 From Ben_Copy_Entity_Results step
761 Where step.Copy_Entity_Result_Id= p_step_result_id
762 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
763 AND step.Table_Alias = 'COP');
764
765 Cursor csr_grade_crpath_node
766 IS
767 select null
768 from per_gen_hierarchy_nodes
769 where hierarchy_version_id = l_crpth_hier_ver
770 and information9 = l_corps_id
771 and information23 = l_grade_id
772 and information3 = p_step_id;
773
774 BEGIN
775 if g_debug then
776 hr_utility.set_location('Entering '||l_proc,10);
777 hr_utility.set_location('p_step_result_id '||p_step_result_id,20);
778 hr_utility.set_location('p_use_points '||p_use_points,30);
779 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,40);
780 hr_utility.set_location('p_effective_date '||p_effective_date,70);
781 end if;
782
783 hr_multi_message.enable_message_list;
784
785 -- Get Pgm_ID
786 Open csr_pgm_id;
787 Fetch csr_pgm_id into l_pgm_id;
788 Close csr_pgm_id;
789
790 -- Get Grade_ID
791 Open csr_grade_id;
792 Fetch csr_grade_id into l_grade_id;
793 Close csr_grade_id;
794
795 if g_debug then
796 hr_utility.set_location('Grade Ladder Id :'||l_pgm_id,100);
797 hr_utility.set_location('Grade Id :'||l_grade_id,110);
798 hr_utility.set_location('Step Id:'||p_step_id ,120);
799 end if;
800
801 IF (l_pgm_id IS NOT NULL AND -- Grade Ladder Id
802 l_grade_id IS NOT NULL AND -- Grade Id
803 p_step_id IS NOT NULL ) -- Step Id
804 THEN
805 if g_debug then
806 hr_utility.set_location('Rec is in HR/BEN Tables ',150);
807 end if;
808 p_rec_exists := 'MAIN';
809
810 /* This call raise error if step is
811 1) Special Ceiling Step
812 2) Employee Placements on this step
813 */
814
815 PER_SPINAL_POINT_STEPS_PKG.del_chks_del(p_step_id => p_step_id,
816 p_sess => p_effective_date);
817
818 -- If the Step is Ceiling Step Then raise Error
819 if g_debug then
820 hr_utility.set_location('Ceiling Step Y/N '||p_celing_step_flag ,180);
821 end if;
822
823 If (p_celing_step_flag = 'Y') Then -- Ceiling_Step_Flag Values Y/N
824 hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
825 hr_utility.raise_error;
826 END IF;
827
828 l_business_area := pqh_corps_utility.get_cet_business_area(p_copy_entity_txn_id);
829 if l_business_area = 'PQH_CORPS_TASK_LIST' then
830 -- Get Corps_Id
831 Open csr_corps_id;
832 Fetch csr_corps_id into l_corps_id;
833 Close csr_corps_id;
834
835 if l_corps_id is not null then
836
837 l_crpth_hier_ver := pqh_cpd_hr_to_stage.get_crpth_hier_ver;
838
839 OPEN csr_grade_crpath_node;
840 FETCH csr_grade_crpath_node into l_grade_crpath_node;
841 if csr_grade_crpath_node%found then
842 if g_debug then
843 hr_utility.set_location(' Corps and Grade are part of a Career Path',90);
844 end if;
845 hr_utility.set_message(8302,'PQH_GSP_STEP_PART_OF_CRPATH');
846 hr_utility.raise_error;
847 end if;
848 CLOSE csr_grade_crpath_node;
849 end if;
850 end if;
851
852
853 ELSE
854 if g_debug then
855 hr_utility.set_location('Data only in Staging Area',350);
856 end if;
857 p_rec_exists := 'STAGE';
858
859 End IF;
860
861
862 EXCEPTION
863 WHEN others THEN
864 p_rec_exists := 'STAGE';
865 fnd_msg_pub.add;
866
867 END remove_step_from_grade;
868 --
869 ---------------------------CHK_PROFILE_EXISTS-----------------------------
870 --
871
872 FUNCTION CHK_PROFILE_EXISTS
873 ( p_copy_entity_result_id IN Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type,
874 p_copy_entity_txn_id IN Ben_Copy_Entity_Results.Copy_Entity_Txn_Id%Type
875 )
876 RETURN varchar2 IS
877
878 /* Author : mvankada
879 Purpose : This function returns whether GSP Entity have eligibility profiles are not
880 If Yes, returns Y otherwise N
881 */
882
883 Cursor csr_profile_count
884 IS
885 Select '1'
886 From Ben_Copy_Entity_Results
887 Where Table_Alias = 'ELP'
888 And Gs_Parent_Entity_Result_Id = p_Copy_Entity_Result_Id
889 And Copy_Entity_Txn_Id = p_copy_entity_txn_id
890 And Result_type_Cd = 'DISPLAY'
891 And Nvl(Information104,'PPP') <> 'UNLINK' ;
892
893 l_cer Varchar2(10);
894 l_proc varchar2(72) := g_package||'chk_profile_exists';
895 l_exists Varchar2(10);
896
897 BEGIN
898 if g_debug then
899 hr_utility.set_location('Entering '||l_proc,10);
900 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
901 end if;
902
903 Open csr_profile_count;
904 Fetch csr_profile_count into l_cer;
905 if csr_profile_count%FOUND then
906 l_exists := 'Y';
907 else
908 l_exists := 'N';
909 end if;
910 Close csr_profile_count;
911 return l_exists;
912
913 if g_debug then
914 hr_utility.set_location('Leaving'||l_proc,60);
915 end if;
916
917 EXCEPTION
918 WHEN others THEN
919 return 'N';
920 END CHK_PROFILE_EXISTS;
921
922
923
924 --
925 ---------------------------DISPLAY_ICON-----------------------------
926 --
927
928 FUNCTION DISPLAY_ICON
929 (p_page IN Varchar2,
930 p_Table_Alias IN Ben_Copy_Entity_Results.Table_Alias%Type,
931 p_action IN Varchar2,
932 p_copy_entity_txn_id IN Ben_Copy_Entity_Results.Copy_Entity_Txn_Id%Type,
933 p_copy_entity_result_id IN Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type
934 ) RETURN varchar2
935 IS
936
937 /*
938 Author : mvankada
939 Purpose : This Function is Used in all HGrid Pages to Disply Icons -
940 Add, Update, Remove, Progression Order, Score Icons at Different Levels.
941
942 Return Values :
943 =================
944 E Enable
945 D Disable
946 N NoIcon
947
948 p_page has values
949 =================
950 GRADE_HGRID -- Grades Page
951 STEP_HGRID -- Steps Page
952 PRG_RULE_HGRID -- Progression Rules/ Review And Submit Pages
953
954 p_action has values
955 ===================
956 ADD
957 UPDATE
958 REMOVE
959 PRGORDER
960 SCORE
961 GRDQUOTA
962 CARRERPATH
963 */
964
965 Cursor Csr_steps_exists
966 IS
967 Select Null
968 From Ben_COpy_Entity_Results
969 Where Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
970 And table_alias ='COP'
971 And Copy_Entity_Txn_Id = p_copy_entity_txn_id
972 And result_type_cd = 'DISPLAY'
973 And Nvl(Information104,'PPP') <> 'UNLINK';
974
975
976 l_grade_attached Varchar2(40);
977 l_exists Varchar2(40);
978 l_dummy Char(1);
979 l_proc varchar2(72) := g_package||'display_icon';
980 BEGIN
981 if g_debug then
982 hr_utility.set_location('Entering'||l_proc,10);
983 hr_utility.set_location('p_page'||p_page,20);
984 hr_utility.set_location('p_Table_Alias'||p_Table_Alias,30);
985 hr_utility.set_location('p_copy_entity_txn_id'||p_copy_entity_txn_id,40);
986 hr_utility.set_location('p_copy_entity_result_id'||p_copy_entity_result_id,50);
987 end if;
988
989 -- For Grade HGrid Page
990 If p_page = 'GRADE_HGRID' Then
991 If p_Table_Alias = 'PGM' Then
992
993 If p_action = 'ADD' Then
994 return 'E';
995 Elsif p_action = 'PRGORDER' then
996 -- Check The Grade Ladder has Grades or not
997 -- If No Grades are attached to Grade Ladder then
998 -- Disable Progression Order Icon otherwise Enable it.
999 l_grade_attached := pqh_gsp_utility.chk_grade_exist_in_gl( p_copy_entity_txn_id => p_copy_entity_txn_id );
1000
1001 If l_grade_attached = 'N' Then
1002 return 'D';
1003 Else
1004 return 'E';
1005 End If;
1006 Else -- # other actions
1007 return 'N';
1008 End If; -- # end of other actions
1009
1010 Elsif p_Table_Alias = 'CPP' Then
1011 -- mvankada
1012 -- Added cotion GRDQUOTA for FRPS Cors Functionality.
1013 If ( p_action = 'UPDATE' OR p_action = 'REMOVE' OR p_action = 'GRDQUOTA') Then
1014 return 'E';
1015 Else
1016 return 'N';
1017 End If;
1018 Else
1019 return 'N';
1020 End If; -- # Table Name
1021
1022 End If; -- # Grade HGrid Page
1023
1024
1025 -- For Step HGrid Page
1026 If p_page = 'STEP_HGRID'
1027 Then
1028 If p_Table_Alias = 'CPP' Then
1029
1030 If ( p_action = 'ADD') Then
1031 return 'E';
1032 Elsif (p_action = 'UPDATE') Then
1033
1034 -- If Steps Exists for the Grade then Enable Update Button
1035 -- Else Disable It
1036 Open Csr_steps_exists ;
1037 Fetch Csr_steps_exists into l_dummy;
1038 If Csr_steps_exists%Found Then
1039 return 'E';
1040 Else
1041 return 'D';
1042 End If;
1043 Close Csr_steps_exists;
1044 Else
1045 return 'N';
1046 End If;
1047
1048 Elsif p_Table_Alias = 'COP' Then
1049
1050 If p_action = 'REMOVE' Then
1051 return 'E';
1052 Else
1053 return 'N';
1054 End If;
1055
1056
1057 Else -- # 'PGM'
1058 return 'N';
1059 End If;
1060
1061 End If; -- # Step HGrid Page.
1062
1063 If p_page = 'PRG_RULE_HGRID'
1064 Then
1065 if g_debug then
1066 hr_utility.set_location('Prg Rule Hgrid page' ,40);
1067 end if;
1068
1069 If p_Table_Alias = 'PGM'
1070 Then
1071 If p_action = 'ADD' Then
1072 return 'E';
1073 Elsif p_action = 'UPDATE' Then
1074 l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1075 p_copy_entity_txn_id => p_copy_entity_txn_id);
1076 if g_debug then
1077 hr_utility.set_location('ELP recs Exists (Y/N) :'|| l_exists,41);
1078 end if;
1079 if l_exists = 'Y' then
1080 return 'E';
1081 else
1082 return 'D';
1083 end if;
1084 Else
1085 return 'N';
1086
1087 End If; -- Action
1088 Elsif p_Table_Alias = 'ELP' Then
1089 if p_action = 'REMOVE' Then
1090 return 'E';
1091 elsif p_action = 'SCORE' Then
1092 return 'D';
1093 else
1094 return 'N';
1095 end if;
1096
1097 ElsIf p_Table_Alias = 'CPP' then
1098 if p_action = 'ADD' Then
1099 return 'E';
1100 elsif p_action = 'UPDATE' Then
1101 if g_debug then
1102 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,50);
1103 end if;
1104 l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1105 p_copy_entity_txn_id => p_copy_entity_txn_id);
1106 if g_debug then
1107 hr_utility.set_location('l_exists :: '||l_exists,60);
1108 end if;
1109
1110 if l_exists = 'Y' then
1111 return 'E';
1112 else
1113 return 'D';
1114 end if;
1115 else
1116 return 'N';
1117 end if; -- Action
1118 ElsIf p_Table_Alias = 'COP' then
1119 if p_action = 'ADD' Then
1120 return 'E';
1121 elsif p_action = 'UPDATE'Then
1122
1123 if g_debug then
1124 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,80);
1125 end if;
1126
1127 l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
1128 p_copy_entity_txn_id => p_copy_entity_txn_id);
1129 if g_debug then
1130 hr_utility.set_location('l_exists :: '||l_exists,90);
1131 end if;
1132
1133 if l_exists = 'Y' then
1134 return 'E';
1135 else
1136 return 'D';
1137 end if;
1138 -- Added CARRERPATH for FR PS Corsp Functionality.
1139 elsif p_action = 'CARRERPATH' then
1140 return 'E';
1141 else
1142 return 'N';
1143 end if; -- Action
1144
1145 Else
1146 return 'N';
1147 End if;
1148 if g_debug then
1149 hr_utility.set_location('Update is through' ,50);
1150 end if;
1151
1152 End If; -- # Progression Rules HGgrid
1153
1154 if g_debug then
1155 hr_utility.set_location('Leaving'||l_proc,300);
1156 end if;
1157 END DISPLAY_ICON;
1158
1159
1160 --
1161 ---------------------------GET_STEP_NAME-----------------------------
1162 --
1163
1164
1165 FUNCTION GET_STEP_NAME( p_copy_entity_result_id in Number,
1166 p_copy_entity_txn_id in Number)
1167 RETURN Number IS
1168
1169 l_proc varchar2(72) := g_package||'GET_STEP_NAME';
1170 l_step_or_point Varchar2(40);
1171 l_opt_cer_id Number;
1172 l_pay_scale_cer_id Number;
1173 l_grd_cer_id Number;
1174 l_step_no Number;
1175 l_starting_step Number;
1176
1177 Cursor Csr_Cer_Ids
1178 IS
1179 Select oipl.Information262, -- Point Cer Id
1180 oipl.Information259, -- Pay Scale Cer Id
1181 oipl.Gs_Parent_Entity_Result_id -- Grade Cer_id
1182 From Ben_Copy_Entity_Results oipl
1183 Where oipl.Copy_Entity_Result_Id = p_copy_entity_result_id
1184 And oipl.Copy_Entity_Txn_Id = p_copy_entity_Txn_id
1185 AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
1186 And oipl.Table_Alias = 'COP';
1187
1188
1189 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)
1190 IS
1191 Select count(*)
1192 From Ben_Copy_Entity_Results opt1,
1193 Ben_Copy_Entity_Results opt2,
1194 Ben_Copy_Entity_Results oipl
1195 Where oipl.Information262 = opt2.copy_entity_result_id
1196 And opt1.Information253 >= opt2.Information253
1197 And opt1.copy_entity_result_id = l_opt_cer_id
1198 And oipl.Information259 = l_pay_scale_cer_id -- Information259 is Pay Scale Cer Id
1199 And oipl.Gs_Parent_Entity_Result_Id = l_grd_cer_id
1200 AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
1201 And Nvl(oipl.Information104,'PPP') <> 'UNLINK'
1202 and oipl.copy_entity_txn_id = l_cet_id
1203 group by opt1.Information263,oipl.Gs_Parent_Entity_Result_Id,opt1.Information98;
1204
1205 Cursor csr_starting_step
1206 IS
1207 select information228
1208 from ben_copy_entity_results
1209 where copy_entity_result_id = l_grd_cer_id;
1210
1211 BEGIN
1212 if g_debug then
1213 hr_utility.set_location('Entering '||l_proc,10);
1214 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1215 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1216 end if;
1217
1218 Open Csr_Cer_Ids;
1219 Fetch Csr_Cer_Ids into l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id;
1220 Close Csr_Cer_Ids;
1221
1222 if g_debug then
1223 hr_utility.set_location('Option Entity Result Id '||l_opt_cer_id,40);
1224 hr_utility.set_location('Pay Scale Entity Result Id '||l_pay_scale_cer_id,50);
1225 end if;
1226
1227 Open Csr_Step_No(l_opt_cer_id,l_pay_scale_cer_id,l_grd_cer_id,p_copy_entity_txn_id);
1228 Fetch Csr_Step_No into l_step_no;
1229 Close Csr_Step_No;
1230
1231 if l_step_no = 0 then
1232 l_step_no := null;
1233 end if;
1234
1235 Open csr_starting_step;
1236 Fetch csr_starting_step into l_starting_step;
1237 Close csr_starting_step;
1238
1239 return l_step_no+nvl(l_starting_step,1)-1;
1240
1241
1242 Exception
1243 When others then
1244 l_step_no := null;
1245 return l_step_no;
1246 End;
1247
1248
1249
1250 --
1251
1252 ---------------------------GET_STEP_PRG_RULE_HGRID_NAME-----------------------------
1253 --
1254
1255 FUNCTION GET_STEP_PRG_RULE_HGRID_NAME( p_copy_entity_result_id in Number,
1256 p_copy_entity_txn_id in Number,
1257 p_Table_Alias in ben_copy_entity_results.Table_Alias%Type,
1258 p_hgrid in Varchar Default NULL)
1259 RETURN varchar2 IS
1260 /*
1261 Author : mvankada
1262 Purpose : This function is used in Step, Progrogression Rules, Review and Submit HGrid Page to display
1263 Name of Gsp Entity.
1264
1265 p_hgrid Values Meaning
1266 ===========================
1267 RATES_HGRID Rates HGrid
1268
1269 */
1270
1271 -- Grade Ladder : PGM information5 -- Name
1272
1273 Cursor csr_grdldr_name IS
1274 Select grdldr.information5
1275 From Ben_Copy_Entity_Results grdldr
1276 Where grdldr.Copy_Entity_Result_Id = p_copy_entity_result_id
1277 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1278 AND grdldr.Table_Alias= p_Table_Alias;
1279
1280 -- Grade : CPP information5 -- Grade Name, information98 -- PayScale Name
1281 Cursor csr_grd_name IS
1282 Select grd.information5,information98
1283 From Ben_Copy_Entity_Results grd
1284 Where grd.Copy_Entity_Result_Id = p_copy_entity_result_id
1285 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1286 AND grd.Table_Alias = p_Table_Alias;
1287
1288 -- Step : COP information5 -- Step Name, information99 -- Point Name
1289 Cursor csr_step_name IS
1290 Select step.information5 , information99
1291 From Ben_Copy_Entity_Results step
1292 Where step.Copy_Entity_Result_Id = p_copy_entity_result_id
1293 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1294 AND step.Table_Alias = p_Table_Alias;
1295
1296 -- Eligibility Profile : ELP information5 -- Profile Name
1297 Cursor csr_elig_name IS
1298 Select elig.information5
1299 From Ben_Copy_Entity_Results elig
1300 Where elig.Copy_Entity_Result_Id = p_copy_entity_result_id
1301 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1302 AND elig.Table_Alias = p_Table_Alias;
1303
1304 Cursor csr_corps_gross_index IS
1305 Select to_number(opt.information173)
1306 From Ben_Copy_Entity_Results Opt
1307 Where opt.Table_Alias = 'OPT'
1308 AND opt.Copy_Entity_Txn_id = p_copy_entity_txn_id
1309 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1310 And opt.Copy_Entity_Result_Id = ( Select oipl.Information262 -- Point Cer Id
1311 From Ben_Copy_Entity_Results oipl
1312 Where Copy_Entity_Result_Id = p_copy_entity_result_id
1313 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1314 And Copy_Entity_Txn_id = p_copy_entity_txn_id
1315 And Table_Alias = 'COP');
1316
1317 Cursor csr_table_name IS
1318 Select Substr(Display_name,1,50)
1319 From Pqh_table_Route
1320 Where Table_Alias = p_Table_Alias;
1321
1322 l_proc varchar2(72) := g_package||'GET_STEP_PRG_RULE_HGRID_NAME';
1323 l_step_or_point Varchar2(40);
1324 l_name Varchar2(4000);
1325 l_name1 Varchar2(4000);
1326 l_step_name Varchar2(4000);
1327
1328
1329 l_gross_index Number;
1330 l_increased_index Number;
1331 l_bus_area Varchar2(240);
1332 l_corps_step_name Varchar2(4000);
1333 l_table_name Varchar2(4000);
1334
1335
1336
1337 BEGIN
1338 if g_debug then
1339 hr_utility.set_location('Entering '||l_proc,10);
1340 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,20);
1341 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,30);
1342 hr_utility.set_location('p_Table_Alias '||p_Table_Alias,40);
1343 end if;
1344
1345 l_step_or_point := pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id => p_copy_entity_txn_id);
1346 if g_debug then
1347 hr_utility.set_location('Point/Step : '||l_step_or_point,40);
1348 end if;
1349
1350 if (p_Table_Alias = 'PGM') then
1351 Open csr_grdldr_name;
1352 Fetch csr_grdldr_name into l_name;
1353 Close csr_grdldr_name;
1354 if g_debug then
1355 hr_utility.set_location('Grade Ladder Name '||l_name,60);
1356 end if;
1357 return l_name;
1358 elsif (p_Table_Alias = 'CPP') then
1359 Open csr_grd_name;
1360 Fetch csr_grd_name into l_name,l_name1;
1361 Close csr_grd_name;
1362 if g_debug then
1363 hr_utility.set_location('Grade Name '||substr(l_name,1,50),80);
1364 hr_utility.set_location('Pay Scale Name '||substr(l_name1,1,50),90);
1365 end if;
1366 if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL )) then
1367 return l_name || ' (' ||l_name1|| ')';
1368 else
1369 return l_name;
1370 end if;
1371
1372 elsif (p_Table_Alias = 'COP') then
1373 Open csr_step_name;
1374 Fetch csr_step_name into l_name,l_name1;
1375 Close csr_step_name;
1376 if g_debug then
1377 hr_utility.set_location('Step Name '||l_name,100);
1378 hr_utility.set_location('Point Name '||l_name1,120);
1379 end if;
1380 l_step_name := GET_STEP_NAME( p_copy_entity_result_id => p_copy_entity_result_id,
1381 p_copy_entity_txn_id => p_copy_entity_txn_id);
1382
1383 if g_debug then
1384 hr_utility.set_location('Generated Step Name : '||l_step_name,125);
1385 end if;
1386
1387
1388 l_bus_area := pqh_corps_utility.get_cet_business_area( p_copy_entity_txn_id => p_copy_entity_txn_id);
1389 if g_debug then
1390 hr_utility.set_location('Business Area : '||l_bus_area,130);
1391 end if;
1392 -- For Corps Rates HGrid Page need to Display Step Name like Step (Point Name | Gross Index | Increased Gross Index)
1393 If (nvl(l_bus_area,'PQH_GSP_TASK_LIST') = 'PQH_CORPS_TASK_LIST' And p_hgrid = 'RATES_HGRID' ) Then
1394
1395 Open csr_corps_gross_index;
1396 Fetch csr_corps_gross_index into l_gross_index;
1397 Close csr_corps_gross_index;
1398
1399 if g_debug then
1400 hr_utility.set_location('Gross Index '||l_gross_index,140);
1401 end if;
1402 If l_gross_index IS NOT NULL Then
1403 l_increased_index := pqh_corps_utility.get_increased_index(p_gross_index => l_gross_index,
1404 p_copy_entity_txn_id => p_copy_entity_txn_id);
1405 End If;
1406
1407 if g_debug then
1408 hr_utility.set_location('Increased Index '||l_increased_index,150);
1409 end if;
1410
1411 if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL)) then
1412 l_corps_step_name := l_step_name || ' (' ||l_name1;
1413
1414 if l_gross_index IS NOT NULL Then
1415 l_corps_step_name := l_corps_step_name || ' | IB ' || l_gross_index;
1416 end if;
1417
1418 if l_increased_index IS NOT NULL Then
1419 l_corps_step_name := l_corps_step_name || ' | INM ' || l_increased_index;
1420 end if;
1421 l_corps_step_name :=l_corps_step_name || ' ) ';
1422 return l_corps_step_name;
1423 end if;
1424
1425 Else
1426
1427 -- For GSP
1428
1429 if ( (l_step_or_point ='POINT') AND (l_name1 IS NOT NULL)) then
1430 return l_step_name || ' (' ||l_name1|| ')';
1431 else
1432 return l_name1;
1433 end if;
1434 End if;
1435
1436
1437 elsif (p_Table_Alias = 'ELP') then
1438 Open csr_elig_name;
1439 Fetch csr_elig_name into l_name;
1440 Close csr_elig_name;
1441 if g_debug then
1442 hr_utility.set_location('Eligibility Profile Name '||l_name,60);
1443 end if;
1444 return l_name;
1445 else
1446 open csr_table_name;
1447 fetch csr_table_name into l_table_name;
1448 close csr_table_name;
1449 if l_table_name IS NOT NULL then
1450 return l_table_name;
1451 else
1452 return null;
1453 end if;
1454 end if; -- table_name
1455 if g_debug then
1456 hr_utility.set_location('Leaving '||l_proc,100);
1457 end if;
1458 END GET_STEP_PRG_RULE_HGRID_NAME;
1459
1460 --
1461 ---------------------------CHK_GRD_DETAILS-----------------------------
1462 --
1463
1464 PROCEDURE CHK_GRD_DETAILS
1465 (
1466 p_name IN per_grades.name%TYPE,
1467 p_short_name IN per_grades.short_name%TYPE,
1468 p_business_group_id IN per_grades.business_group_id%TYPE,
1469 p_grade_id IN per_grades.grade_id%TYPE default NULL,
1470 p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE default NULL,
1471 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE,
1472 p_status OUT NOCOPY VARCHAR
1473 )
1474 IS
1475
1476 CURSOR csr_grade_name
1477 IS
1478 Select null
1479 From Per_Grades
1480 Where name = p_name
1481 And business_group_id = p_business_group_id
1482 And grade_id <> nvl(p_grade_id,-1)
1483 Union ALL
1484 Select null
1485 From ben_copy_entity_results
1486 Where
1487 information5 = p_name
1488 And information4 = p_business_group_id
1489 And table_alias = 'PLN'
1490 And result_type_cd = 'DISPLAY'
1491 And copy_entity_result_id <> nvl(p_copy_entity_result_id,-1)
1492 And copy_entity_txn_id = p_copy_entity_txn_id;
1493
1494 CURSOR csr_grade_short_name
1495 IS
1496 Select null
1497 From per_grades
1498 Where short_name = p_short_name
1499 And business_group_id = p_business_group_id
1500 And grade_id <> nvl(p_grade_id,-1)
1501 Union ALL
1502 Select null
1503 From ben_copy_entity_results
1504 Where information102 = p_short_name
1505 And information4 = p_business_group_id
1506 And table_alias='PLN'
1507 And result_type_cd = 'DISPLAY'
1508 And copy_entity_result_id <> nvl(p_copy_entity_result_id,-1)
1509 And copy_entity_txn_id=p_copy_entity_txn_id;
1510
1511 l_grade_name per_grades.name%TYPE;
1512 l_short_name per_grades.short_name%TYPE;
1513 l_proc varchar2(72) := g_package||'CHK_GRD_DETAILS';
1514
1515 BEGIN
1516 if g_debug then
1517 hr_utility.set_location('Entering '||l_proc,10);
1518 hr_utility.set_location('p_name '||p_name,20);
1519 hr_utility.set_location('p_short_name '||p_short_name,30);
1520 hr_utility.set_location('p_business_group_id '||p_business_group_id,40);
1521 hr_utility.set_location('p_grade_id '||p_grade_id,50);
1522 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id,60);
1523 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,70);
1524 end if;
1525
1526 hr_multi_message.enable_message_list;
1527 Open csr_grade_name;
1528 Fetch csr_grade_name into l_grade_name;
1529 If(csr_grade_name%FOUND) THEN
1530 p_status := 'E';
1531 hr_utility.set_message(8302,'PER_7830_DEF_GRADE_EXISTS');
1532 hr_utility.raise_error;
1533 Else
1534 p_status := 'Y';
1535 End if;
1536
1537 Open csr_grade_short_name;
1538 Fetch csr_grade_short_name into l_short_name;
1539 If(csr_grade_short_name%FOUND) THEN
1540 p_status := 'E';
1541 hr_utility.set_message(800,'HR_289555_NON_UNIQ_SHORT_NAME');
1542 hr_utility.raise_error;
1543 Else
1544 p_status := 'Y';
1545 End if;
1546 if g_debug then
1547 hr_utility.set_location('Leaving '||l_proc,100);
1548 end if;
1549 Exception
1550 when others then
1551 p_status := 'E';
1552 fnd_msg_pub.add;
1553 END CHK_GRD_DETAILS;
1554
1555
1556 --
1557 ---------------------------get_standard_rate-----------------------------
1558 --
1559
1560 Function get_standard_rate(p_copy_entity_result_id in number,
1561 p_effective_date in date)
1562 RETURN number is
1563 --
1564 Cursor csr_std_rate is
1565 Select information98
1566 From ben_copy_entity_results std
1567 Where std.gs_parent_entity_result_id = p_copy_entity_result_id
1568 And std.table_alias = 'ABR'
1569 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1570 AND p_effective_date between information2 and nvl(information3,to_date('31/12/4712','dd/mm/RRRR'));
1571 --
1572 l_std_rate number(38,15) := 0;
1573 --
1574 Begin
1575 --
1576 Open csr_std_rate;
1577 Fetch csr_std_rate into l_std_rate;
1578 Close csr_std_rate;
1579 --
1580 Return l_std_rate;
1581 --
1582 End get_standard_rate;
1583
1584 --
1585 ---------------------------delete_transaction-----------------------------
1586 --
1587 procedure delete_transaction
1588 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE) IS
1589
1590
1591 begin
1592 del_gl_details_from_stage(p_pqh_copy_entity_txn_id);
1593 delete from pqh_copy_entity_txns where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
1594 end delete_transaction;
1595
1596 --
1597 ---------------------------del_gl_details_from_stage-----------------------------
1598 --
1599 procedure del_gl_details_from_stage
1600 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE) IS
1601
1602 begin
1603 delete from pqh_copy_entity_attribs where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
1604 delete from ben_copy_entity_results where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
1605 end del_gl_details_from_stage;
1606
1607 --
1608 ---------------------------enddate_grade_ladder-----------------------------
1609 --
1610 procedure enddate_grade_ladder
1611 (p_ben_pgm_id IN ben_pgm_f.pgm_id%TYPE,
1612 p_effective_date_in IN ben_pgm_f.effective_start_date%TYPE) IS
1613
1614 Cursor pgm is
1615 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;
1616
1617 Cursor c2 is
1618 select copy_entity_txn_id from pqh_copy_entity_txns where copy_entity_txn_id in
1619 (select copy_entity_txn_id from ben_copy_entity_results
1620 where information1 = p_ben_pgm_id and table_alias = 'PGM' and information_category = 'GRADE_LADDER'
1621 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY')
1622 and status = 'SFL';
1623
1624 l_effective_start_date date;
1625 l_effective_end_date date;
1626
1627 l_proc varchar2(72) := g_package||'enddate_grade_ladder';
1628
1629 begin
1630 if g_debug then
1631 hr_utility.set_location('Entering '||l_proc,5);
1632 hr_utility.set_location('p_ben_pgm_id is '||p_ben_pgm_id, 10);
1633 hr_utility.set_location('p_effective_date_in is '||p_effective_date_in, 15);
1634 end if;
1635
1636 for each_rec in pgm loop
1637 ben_program_api.delete_Program
1638 (p_pgm_id => p_ben_pgm_id
1639 ,p_effective_start_date => l_effective_start_date
1640 ,p_effective_end_date => l_effective_end_date
1641 ,p_object_version_number => each_rec.object_version_number
1642 ,p_effective_date => p_effective_date_in
1643 ,p_datetrack_mode => 'DELETE'
1644 );
1645 end loop;
1646
1647 for sfl_txn in c2 loop
1648 delete_transaction(sfl_txn.copy_entity_txn_id);
1649 end loop;
1650 if g_debug then
1651 hr_utility.set_location('Successfull completion for '||l_proc,5);
1652 end if;
1653 exception when others then
1654 if g_debug then
1655 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in'||l_proc,5);
1656 end if;
1657 raise;
1658 end enddate_grade_ladder;
1659
1660 --
1661 ---------------------------Get_Step_Dtls-----------------------------
1662 --
1663 /* This function is used to return the Current Step Id and Step Name */
1664 /* pass P_Id_name as I if Step Id is required. Pass 'N' if Stap_name is required */
1665
1666 Function Get_Step_Dtls
1667 (P_Entity_id In Number,
1668 P_Effective_Date In Date,
1669 P_Id_name In Varchar2,
1670 P_Curr_Prop In Varchar2)
1671
1672 RETURN Number Is
1673
1674 Cursor Step is
1675 Select Steps.Step_Id, Steps.Spinal_point_id, Steps.Grade_spine_id
1676 From Per_Spinal_Point_Placements_F Plcmt,
1677 Per_Spinal_Point_Steps_F Steps
1678 Where Plcmt.ASSIGNMENT_ID = P_Entity_id
1679 and P_Effective_Date
1680 Between Plcmt.Effective_Start_Date and Plcmt.Effective_End_Date
1681 and Plcmt.Step_id = Steps.Step_Id
1682 and P_Effective_Date
1683 Between Steps.Effective_Start_Date and Steps.Effective_End_Date;
1684
1685 Cursor PropStep Is
1686 Select Step.Step_id, Step.Spinal_point_id, Step.Grade_spine_id
1687 From Ben_Oipl_F Oipl,
1688 Ben_pl_F Pl,
1689 Ben_Opt_F Opt,
1690 Per_Spinal_points point,
1691 Per_Grade_Spines_f GSpine,
1692 Per_Spinal_point_Steps_F Step
1693 Where Oipl.Oipl_id = P_Entity_Id
1694 and P_Effective_Date
1695 Between OiPl.Effective_Start_Date and OiPl.Effective_End_Date
1696 and Pl.Pl_id = Oipl.Pl_Id
1697 and P_Effective_Date
1698 Between Pl.Effective_Start_Date and Pl.Effective_End_Date
1699 and Oipl.Opt_id = Opt.Opt_id
1700 and P_Effective_Date
1701 Between Opt.Effective_Start_Date and Opt.Effective_End_Date
1702 and Point.Spinal_Point_id = Opt.Mapping_Table_Pk_Id
1703 and Pl.Mapping_Table_Pk_Id = Gspine.Grade_Id
1704 and P_Effective_Date
1705 Between Gspine.Effective_Start_Date and Gspine.Effective_End_Date
1706 and Step.Grade_Spine_Id = Gspine.Grade_Spine_Id
1707 and P_Effective_Date
1708 Between Step.Effective_Start_Date and Step.Effective_End_Date
1709 and Step.SPINAL_POINT_ID = Point.SPINAL_POINT_ID;
1710
1711 l_Point_id Per_Spinal_POint_Steps_f.Spinal_Point_Id%TYPE;
1712 l_Parent_Spine_id Per_Spinal_Point_Placements_F.Parent_Spine_Id%TYPE;
1713 l_Step_Id Per_Spinal_POint_Steps_F.Step_Id%TYPE := NULL;
1714 l_Step_name Number(10) := NULL;
1715 begin
1716
1717 If P_Curr_Prop = 'CURR' then
1718 Open Step;
1719 Fetch Step into l_Step_Id , L_Point_Id, l_Parent_Spine_id;
1720 Close step;
1721 ElsIf P_Curr_Prop = 'PROP' and P_Entity_Id is NOT NULL then
1722 Open PropStep;
1723 Fetch PropStep into l_Step_Id , L_Point_Id, l_Parent_Spine_id;
1724 Close PropStep;
1725 End If;
1726
1727 If P_Id_Name = 'I' Then
1728 Return l_Step_Id;
1729 Else
1730 If l_point_id is Not NULL and l_Parent_Spine_Id is not NULL Then
1731 per_spinal_point_steps_pkg.pop_flds(l_Step_name,
1732 P_Effective_Date,
1733 l_point_id,
1734 l_Parent_Spine_Id);
1735
1736 End If;
1737 Return l_Step_Name;
1738 End If;
1739
1740 End Get_Step_Dtls;
1741
1742 --
1743 --
1744 ---------------------------Get_Cur_Sal-----------------------------
1745 --
1746
1747 Function Get_Cur_Sal
1748 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
1749 P_Effective_Date In Date)
1750
1751 Return Number Is
1752
1753 L_Cur_Sal Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
1754 l_input_value_id pay_input_values_f.Input_Value_id%TYPE;
1755 L_FREQUENCY per_time_period_types.period_type%TYPE;
1756 L_ANNUAL_SALARY Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
1757 L_PAY_BASIS Varchar2(5);
1758 L_REASON_CD Per_pay_Proposals.proposal_reason%TYPE;
1759 l_pay_basis_frequency Per_pay_bases.pay_basis%TYPE;
1760 L_CURRENCY Ben_Pgm_F.Pgm_Uom%TYPE;
1761 L_STATUS Number(10);
1762
1763 Cursor Sal is
1764 Select pev.screen_entry_value
1765 From pay_element_entries_f pee,
1766 pay_input_values_f piv,
1767 pay_element_entry_values_f pev
1768 Where pee.Assignment_id = P_Assignment_id
1769 and P_Effective_Date
1770 between pee.Effective_Start_Date and pee.Effective_End_Date
1771 and Piv.Input_Value_id = l_Input_Value_id
1772 and P_Effective_Date
1773 Between Piv.Effective_Start_Date and Piv.Effective_End_Date
1774 and pev.ELEMENT_ENTRY_ID = Pee.ELEMENT_ENTRY_ID
1775 and Piv.INPUT_VALUE_ID = Pev.INPUT_VALUE_ID
1776 and P_Effective_Date
1777 Between Pev.Effective_Start_Date and Pev.Effective_End_Date;
1778
1779 Cursor Pay_Bases_Element is
1780 Select input_value_id
1781 From Per_Pay_Bases ppb,
1782 Per_All_Assignments_f paf
1783 Where paf.Assignment_Id = p_Assignment_Id
1784 and p_Effective_Date
1785 Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1786 and paf.pay_basis_id = ppb.pay_basis_id;
1787
1788 Cursor GrdLdr_Element is
1789 Select DFLT_INPUT_VALUE_ID
1790 from Ben_Pgm_f pgm,
1791 Per_All_Assignments_f paf
1792 Where paf.Assignment_Id = p_Assignment_Id
1793 and p_Effective_Date
1794 Between Paf.Effective_Start_Date and Paf.Effective_End_Date
1795 and paf.GRADE_LADDER_PGM_ID = pgm.pgm_id
1796 and p_Effective_Date
1797 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
1798
1799 Begin
1800
1801 /* Commenting the following Cursor Calls as the Procedure below will return the required amount */
1802
1803 /* Open Pay_Bases_Element;
1804 Fetch Pay_Bases_Element into l_input_Value_id;
1805 Close Pay_Bases_Element;
1806
1807 If l_input_Value_id is NULL Then
1808 Open GrdLdr_Element;
1809 Fetch GrdLdr_Element into l_input_Value_id;
1810 Close GrdLdr_Element;
1811 End If;
1812
1813 if l_Input_Value_id is Not NULL Then
1814 Open Sal;
1815 Fetch Sal into L_Cur_Sal;
1816 Close Sal;
1817 Else
1818 l_Cur_Sal := 0;
1819 End If;
1820
1821 Return L_Cur_Sal; */
1822
1823 /* the following Procedure will return The Annual Salary */
1824 pqh_employee_salary.GET_EMPLOYEE_SALARY
1825 (P_ASSIGNMENT_ID => p_Assignment_Id
1826 ,P_EFFECTIVE_DATE => p_Effective_Date
1827 ,P_SALARY => L_Cur_Sal
1828 ,P_FREQUENCY => L_FREQUENCY
1829 ,P_ANNUAL_SALARY => L_ANNUAL_SALARY
1830 ,P_PAY_BASIS => L_PAY_BASIS
1831 ,P_REASON_CD => L_REASON_CD
1832 ,P_CURRENCY => L_CURRENCY
1833 ,P_STATUS => L_STATUS
1834 ,p_pay_basis_frequency => l_pay_basis_frequency);
1835
1836
1837 Return L_ANNUAL_SALARY;
1838
1839 End Get_Cur_Sal;
1840
1841 --
1842 ---------------------------Get_CAGR_Name-----------------------------
1843 --
1844
1845 Function Get_CAGR_Name
1846 (P_CAGR_Id IN Per_Collective_Agreements.Collective_Agreement_ID%TYPE)
1847
1848 Return Varchar2 Is
1849 L_Cagr_name Per_Collective_Agreements.Name%TYPE;
1850
1851 Cursor CAGR is
1852 Select Name
1853 from Per_Collective_Agreements
1854 Where Collective_Agreement_Id = P_CAGR_Id;
1855
1856 Begin
1857
1858 Open CAGR;
1859 Fetch CAGR into l_Cagr_name;
1860 Close Cagr;
1861
1862 Return l_Cagr_Name;
1863
1864 End Get_CAGR_Name;
1865 --
1866 ---------------------------gen_txn_display_name-----------------------------
1867 --
1868
1869 Function gen_txn_display_name
1870 (p_program_name IN pqh_copy_entity_txns.display_name%TYPE,
1871 p_mode IN varchar2)
1872 Return Varchar2 is
1873 l_timestamp varchar2(30) := fnd_date.date_to_canonical(sysdate);
1874 l_display_name varchar2(300);
1875 l_mode varchar2(10) := p_mode;
1876 l_proc varchar2(72) := 'gen_txn_display_name';
1877 begin
1878 if g_debug then
1879 hr_utility.set_location('Entering '||l_proc,5);
1880 hr_utility.set_location('p_program_name is '||p_program_name, 10);
1881 hr_utility.set_location('p_mode is '||p_mode, 15);
1882 end if;
1883
1884 --
1885 --If Mode is null then consider it to be a create transaction
1886 --
1887 if p_mode is null then
1888 l_mode := 'C';
1889 end if;
1890
1891 --
1892 -- No need for extra space at the end of generated name if p_display_name is null.
1893 --
1894 if p_program_name is not null then
1895 l_display_name := l_mode||':'||l_timestamp||':'||substr(p_program_name, 1, 78);
1896 else
1897 l_display_name := l_mode||':'||l_timestamp;
1898 end if;
1899
1900 if g_debug then
1901 hr_utility.set_location('Leaving '||l_proc,5);
1902 end if;
1903 return l_display_name;
1904
1905 EXCEPTION WHEN others THEN
1906 if g_debug then
1907 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1908 end if;
1909 end;
1910
1911 --
1912 ---------------------------get_grade_ladder_name_from_txn-----------------------------
1913 --
1914 Function get_grade_ladder_name_from_txn
1915 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE)
1916 Return Varchar2 is
1917 cursor c1 is
1918 select information5 grade_ladder_name from ben_copy_entity_results
1919 where copy_entity_txn_id = p_pqh_copy_entity_txn_id
1920 and table_alias = 'PGM'
1921 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
1922 and information_category = 'GRADE_LADDER';
1923 l_grade_ladder_name varchar2(240);
1924
1925 l_proc varchar2(72) := 'get_grade_ladder_name_from_txn';
1926 begin
1927 if g_debug then
1928 hr_utility.set_location('Entering '||l_proc, 5);
1929 end if;
1930
1931 for gr_ldr in c1 loop
1932 l_grade_ladder_name := gr_ldr.grade_ladder_name;
1933 end loop;
1934 return l_grade_ladder_name;
1935
1936 if g_debug then
1937 hr_utility.set_location('Leaving '||l_proc, 5);
1938 end if;
1939
1940 EXCEPTION WHEN others THEN
1941 if g_debug then
1942 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
1943 end if;
1944 end;
1945
1946 --
1947 ---------------------------chk_default_ladder_exist-----------------------------
1948 --
1949
1950 Procedure chk_default_ladder_exist
1951 ( p_pgm_id in number,
1952 p_business_group_id in number,
1953 p_effective_date in Date) IS
1954 /* Author : mvanakda
1955 Purpose : This procedure checks whether a Default Grade Ladder Exist
1956 for a business group or not.
1957 */
1958
1959 l_proc varchar2(72) := g_package|| 'chk_default_ladder_exist';
1960 l_name Ben_Pgm_F.Name%Type;
1961 l_dummy Char(1);
1962
1963 Cursor csr_curr_grdldr IS
1964 Select Name
1965 From ben_pgm_f
1966 Where pgm_id = p_pgm_id
1967 And business_group_id = p_business_group_id
1968 And effective_end_date>= p_effective_date
1969 And pgm_typ_cd = 'GSP'
1970 And Dflt_Pgm_Flag = 'Y';
1971
1972
1973
1974 Cursor csr_default_chk IS
1975 Select Null
1976 From ben_pgm_f
1977 Where pgm_id <> nvl(p_pgm_id,-1)
1978 And business_group_id = p_business_group_id
1979 And p_effective_date Between Effective_Start_Date
1980 And nvl(Effective_End_Date, hr_general.end_of_time)
1981 And pgm_typ_cd = 'GSP'
1982 And Dflt_Pgm_Flag = 'Y';
1983 --
1984 Begin
1985 if g_debug then
1986 hr_utility.set_location('ENTERING:'||l_proc, 5);
1987 hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
1988 hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
1989 end if;
1990 --
1991 --
1992 If p_pgm_id IS NOT NULL Then
1993 if g_debug then
1994 hr_utility.set_location(' Pgm Id Is not Null', 21);
1995 end if;
1996 Open csr_curr_grdldr;
1997 Fetch csr_curr_grdldr into l_name;
1998 If csr_curr_grdldr%Found Then
1999 if g_debug then
2000 hr_utility.set_location('Current Grade Ladder is Default Grade Ladder' , 22);
2001 end if;
2002 Open csr_default_chk;
2003 Fetch csr_default_chk into l_dummy;
2004 If csr_default_chk%found then
2005 close csr_default_chk;
2006 hr_utility.set_message(8302,'PQH_GSP_GRDLDR_DFLT_ERR');
2007 hr_utility.set_message_token('LADDER',l_name);
2008 hr_multi_message.add;
2009 Else
2010 close csr_default_chk;
2011 End if;
2012 End If;
2013 Close csr_curr_grdldr;
2014 End If;
2015 --
2016 if g_debug then
2017 hr_utility.set_location('Leaving:'||l_proc, 35);
2018 end if;
2019
2020 End chk_default_ladder_exist;
2021
2022 --
2023 ---------------------------chk_add_steps_in_all_grades-----------------------------
2024 --
2025
2026 Procedure chk_add_steps_in_all_grades
2027 ( p_copy_entity_txn_id in number,
2028 p_business_group_id in number) IS
2029
2030 /* Author : mvanakda
2031 Purpose : This procedure checks if steps have been added to only some steps
2032 in a grade and warns the user that the setup cannot be saved unless steps
2033 are added to all the Grades.
2034 */
2035
2036 l_proc varchar2(72) := g_package|| 'chk_add_steps_in_all_grades';
2037 l_copy_entity_result_id Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type;
2038 l_grade_name Ben_Copy_Entity_Results.Information5%type;
2039 l_dummy Varchar2(10);
2040 l_no_step_grades Varchar2(2000) := null;
2041 l_no_ceil_grades Varchar2(2000) := null;
2042 l_found1 Boolean := FALSE;
2043 l_found2 Boolean := FALSE;
2044 l_steps_exists varchar2(10);
2045
2046
2047 Cursor csr_steps_exists
2048 IS
2049 Select Null
2050 FROM BEN_COPY_ENTITY_RESULTS step
2051 WHERE step.copy_entity_txn_id = p_copy_entity_txn_id
2052 AND step.TABLE_ALIAS ='COP'
2053 And nvl(step.INFORMATION104,'PPP') <> 'UNLINK'
2054 AND step.result_type_cd = 'DISPLAY';
2055
2056
2057 Cursor csr_grades IS
2058 Select grd.Copy_Entity_Result_Id, grd.Information5
2059 From Ben_Copy_Entity_Results grd
2060 Where grd.Copy_Entity_txn_Id = p_copy_entity_txn_id
2061 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2062 And nvl(grd.Information104,'PPP') <> 'UNLINK'
2063 And grd.Table_Alias = 'CPP'
2064 And grd.Information4 = p_business_group_id;
2065
2066
2067 Cursor csr_steps(p_copy_entity_result_id in number) IS
2068 Select Null
2069 From Ben_Copy_Entity_Results step
2070 Where step.Copy_Entity_txn_Id = p_copy_entity_txn_id
2071 And step.Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
2072 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2073 And step.Table_Alias = 'COP'
2074 And nvl(step.Information104,'PPP') <> 'UNLINK'
2075 And step.Information4 = p_business_group_id;
2076
2077
2078 Cursor csr_celing_step(p_copy_entity_result_id in number) IS
2079 Select Null
2080 From Ben_Copy_Entity_Results ceiling
2081 Where ceiling.Copy_Entity_txn_Id = p_copy_entity_txn_id
2082 And ceiling.Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
2083 And ceiling.Table_Alias = 'COP'
2084 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2085 And nvl(ceiling.Information104,'PPP') <> 'UNLINK'
2086 /*Information98 is used to store Ceiling Step Flag */
2087 And ceiling.Information98 = 'Y'
2088 And ceiling.Information4 = p_business_group_id;
2089
2090
2091 --
2092 Begin
2093 if g_debug then
2094 hr_utility.set_location('Entering:'||l_proc, 5);
2095 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2096 hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2097 end if;
2098 --
2099 -- Check Steps Exists for a Transaction or not
2100 Open csr_steps_exists;
2101 Fetch csr_steps_exists into l_steps_exists;
2102 If csr_steps_exists%Found Then
2103 For l_rec in csr_grades
2104 Loop
2105 Open csr_steps(l_rec.copy_entity_result_id);
2106 Fetch csr_steps into l_dummy;
2107 If csr_steps%notfound then
2108 l_no_step_grades := l_no_step_grades || l_rec.Information5 ||' , ';
2109 l_found1 := TRUE;
2110 End If;
2111 Close csr_steps;
2112
2113
2114 Open csr_celing_step(l_rec.copy_entity_result_id);
2115 Fetch csr_celing_step into l_dummy;
2116 If csr_celing_step%notfound then
2117 l_no_ceil_grades := l_no_ceil_grades || l_rec.Information5 ||' , ';
2118 l_found2 := TRUE;
2119 End If;
2120 Close csr_celing_step;
2121
2122 End Loop;
2123 End if;
2124 Close csr_steps_exists;
2125
2126 l_no_step_grades := substr(l_no_step_grades,1,length(l_no_step_grades)-2);
2127 l_no_ceil_grades := substr(l_no_ceil_grades,1,length(l_no_ceil_grades)-2);
2128 if g_debug then
2129 hr_utility.set_location('Grades not having Steps 1:'||substr(l_no_step_grades,1,50),50);
2130 hr_utility.set_location('Grades not having Steps 2:'||substr(l_no_step_grades,51,100),51);
2131 hr_utility.set_location('Grades not having Steps 3:'||substr(l_no_step_grades,101,150),52);
2132 hr_utility.set_location('Grades not having Ceiling Steps 1: '||substr(l_no_ceil_grades,1,50),55);
2133 hr_utility.set_location('Grades not having Ceiling Steps 2: '||substr(l_no_ceil_grades,51,100),55);
2134 hr_utility.set_location('Grades not having Ceiling Steps 3: '||substr(l_no_ceil_grades,101,150),55);
2135 end if;
2136
2137 If l_found1 then
2138 if g_debug then
2139 hr_utility.set_location('Grades not having Steps found',51);
2140 end if;
2141 hr_utility.set_message(8302,'PQH_GSP_ADD_STEPS_IN_ALL_GRDS');
2142 hr_utility.set_message_token('GRADES',l_no_step_grades);
2143
2144 hr_multi_message.add;
2145
2146
2147 End If;
2148 If l_found2 then
2149 if g_debug then
2150 hr_utility.set_location('Grades not having Celing Steps found',52);
2151 end if;
2152 hr_utility.set_message(8302,'PQH_GSP_ADD_CEIL_IN_ALL_GRDS');
2153 hr_utility.set_message_token('GRADES',l_no_ceil_grades);
2154 hr_multi_message.add;
2155 End If;
2156 if g_debug then
2157 hr_utility.set_location('Leaving:'||l_proc,100);
2158 end if;
2159
2160 End chk_add_steps_in_all_grades;
2161 --
2162 ---------------------------chk_valid_grd_in_grdldr-----------------------------
2163 --
2164 Procedure chk_valid_grd_in_grdldr
2165 ( p_copy_entity_txn_id in number,
2166 p_effective_date in date,
2167 p_business_group_id in Number) IS
2168
2169 /* Author : mvanakda
2170 Purpose : This procedure used to check if grades are valid as of the
2171 grade ladder effective date
2172 */
2173
2174 l_proc varchar2(72) := g_package|| 'chk_valid_grd_in_grdldr';
2175 l_dummy Ben_Copy_Entity_Results.Information5%Type;
2176 l_grades Varchar2(2000);
2177 l_found Boolean := FALSE;
2178
2179 -- This cursor checks the newly created grades
2180 /* Information5 : Grade Name
2181 Information306 : Grade Start Date
2182 Information307 : Grade End Date
2183 */
2184 -- Bug : 3161418
2185
2186 Cursor csr_grades IS
2187 Select grd.Information5 ,
2188 grd.Information306,
2189 grd.Information307
2190 From Ben_Copy_Entity_Results grd
2191 Where grd.Copy_Entity_txn_Id = p_copy_entity_txn_id
2192 AND grd.Table_Alias = 'CPP'
2193 AND nvl(grd.Information104,'PPP') <> 'UNLINK'
2194 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2195 AND p_effective_date Not BETWEEN grd.Information306
2196 AND nvl(grd.Information307,hr_general.end_of_time)
2197 AND Information4 = p_business_group_id;
2198 BEGIN
2199 if g_debug then
2200 hr_utility.set_location('Entering:'||l_proc,10);
2201 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id,20);
2202 hr_utility.set_location('p_effective_date:'||p_effective_date,30);
2203 end if;
2204
2205 For l_rec in csr_grades
2206 Loop
2207 l_grades := l_grades || l_rec.Information5 ||' , ';
2208 l_found := TRUE;
2209 End loop;
2210 l_grades := substr(l_grades,1,length(l_grades)-2);
2211
2212 if g_debug then
2213 hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,1,50),50);
2214 hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,51,100),51);
2215 hr_utility.set_location('Invalid Grades are ... :'||substr(l_grades,101,150),52);
2216 end if;
2217
2218
2219 If l_found then
2220 hr_utility.set_message(8302,'PQH_GSP_INVALID_GRD_IN_GRDLDR');
2221 hr_utility.set_message_token('GRADES',l_grades);
2222 hr_multi_message.add;
2223 End If;
2224
2225 if g_debug then
2226 hr_utility.set_location('Leaving:'||l_proc, 15);
2227 end if;
2228 End chk_valid_grd_in_grdldr;
2229
2230 --
2231 ---------------------------chk_inactivate_grdldr-----------------------------
2232 --
2233
2234 Procedure chk_inactivate_grdldr
2235 (p_pgm_id in Number,
2236 p_effective_date in Date,
2237 p_business_group_id in Number,
2238 p_activate in Varchar) IS
2239
2240
2241 /* Author : mvanakda
2242 Purpose : This procedure used to check if a grade ladder can be
2243 inactivated or not
2244 */
2245
2246 l_proc Varchar2(72) := g_package|| 'chk_inactivate_grdldr';
2247 l_old_status BEN_PGM_F.PGM_STAT_CD%Type;
2248 l_dummy Char(1);
2249
2250
2251 Cursor csr_active_gl IS
2252 Select null
2253 From Per_All_Assignments_F
2254 Where Grade_Ladder_Pgm_Id = p_pgm_id
2255 And p_effective_date between effective_start_date
2256 And nvl(effective_end_date, hr_general.end_of_time)
2257 And Business_Group_Id = p_business_group_id;
2258
2259 Cursor csr_pgm_status
2260 IS
2261 Select pgm.PGM_STAT_CD -- Activate A , Inactivate I
2262 From BEN_PGM_F pgm
2263 Where pgm.Pgm_Id = p_pgm_id
2264 And pgm.Business_Group_Id = p_business_group_id
2265 And p_effective_date between pgm.effective_start_date
2266 And nvl(pgm.effective_end_date, hr_general.end_of_time);
2267
2268
2269
2270 Begin
2271 if g_debug then
2272 hr_utility.set_location('Entering:'||l_proc, 10);
2273 hr_utility.set_location('p_pgm_id:'||p_pgm_id, 15);
2274 hr_utility.set_location('p_effective_date:'||p_effective_date, 20);
2275 hr_utility.set_location('p_activate:'||p_activate, 35);
2276 end if;
2277 hr_multi_message.enable_message_list;
2278
2279 If p_pgm_id IS NOT NULL Then
2280 if g_debug then
2281 hr_utility.set_location('PGM Id Not Null ', 40);
2282 end if;
2283
2284 -- Get Status of PGM
2285 Open csr_pgm_status;
2286 Fetch csr_pgm_status into l_old_status;
2287 Close csr_pgm_status;
2288 if g_debug then
2289 hr_utility.set_location('PGM Status :'||l_old_status, 41);
2290 end if;
2291
2292 -- Raise the error in the following case.
2293 -- 1) If PGM is Active And
2294 -- 2) Emp Assignments exists on this PGM And
2295 -- 3) On Review And Submit Page changed status from Active to Inactive
2296
2297 if (l_old_status = 'A' And p_activate = 'I') then
2298
2299 Open csr_active_gl;
2300 Fetch csr_active_gl into l_dummy;
2301 If csr_active_gl%Found then
2302 Close csr_active_gl;
2303 if g_debug then
2304 hr_utility.set_location('Found ', 45);
2305 end if;
2306 hr_utility.set_message(8302,'PQH_GSP_INACTIVATE_GRGLDR_ERR');
2307 hr_utility.raise_error;
2308 Else
2309 if g_debug then
2310 hr_utility.set_location('Not Found ', 50);
2311 end if;
2312 Close csr_active_gl;
2313 End If;
2314 End If; -- stauts check
2315 End If; -- Pgm Not Null
2316
2317 if g_debug then
2318 hr_utility.set_location('Leaving:'||l_proc, 55);
2319 end if;
2320 EXCEPTION
2321 WHEN others THEN
2322 fnd_msg_pub.add;
2323 End chk_inactivate_grdldr;
2324
2325 --
2326 ---------------------------Get_Emp_Los-----------------------------
2327 --
2328
2329 Function Get_Emp_Los
2330 (P_Person_id In Per_All_PEOPLE_F.Person_Id%TYPE,
2331 P_Effective_Date In Date)
2332 Return Number is
2333 /* Venkatesh :- The following function is used to determine the Length of Service
2334 for a given Person. It is used in Approval UI */
2335
2336 Cursor LOS is
2337 Select Months_Between(P_Effective_Date,Start_Date) / 12
2338 from Per_All_people_F
2339 Where Person_id = p_Person_Id
2340 and P_Effective_Date
2341 Between Effective_Start_Date and Effective_End_Date;
2342
2343 L_Los Number := 0;
2344 begin
2345
2346 If P_Person_id is Not NULL and P_Effective_Date is Not NUll Then
2347 open Los;
2348 Fetch Los into L_Los;
2349 Close los;
2350 End If;
2351 Return l_los;
2352
2353 End Get_Emp_Los;
2354
2355 Function Get_Currency
2356 (P_Corrency_Code In Fnd_Currencies_Vl.Currency_Code%TYPE)
2357
2358 Return Varchar2 is
2359
2360 Cursor Currency is
2361 Select Name
2362 from Fnd_Currencies_Vl
2363 Where Currency_Code = P_Corrency_Code
2364 and Enabled_Flag = 'Y';
2365
2366 P_Currency_name Fnd_Currencies_Vl.Name%TYPE := NULL;
2367 begin
2368
2369 If P_Corrency_Code is Not Null then
2370 open Currency;
2371 Fetch Currency into P_Currency_name;
2372 Close Currency;
2373 End If;
2374
2375 Return P_Currency_name;
2376 End;
2377
2378 Function Get_SpinalPoint_Name
2379 (p_Point_id IN per_spinal_points.Spinal_Point_Id%TYPE)
2380
2381 Return Varchar2 is
2382
2383 Cursor POints is
2384 Select spinal_point
2385 from per_spinal_points
2386 Where Spinal_point_id = p_Point_id;
2387
2388 P_Spinal_Point_name per_spinal_points.Spinal_Point%TYPE := NULL;
2389 begin
2390
2391 If p_Point_id is Not Null then
2392 open POints;
2393 Fetch POints into P_Spinal_Point_name;
2394 Close POints;
2395 End If;
2396
2397 Return P_Spinal_Point_name;
2398 End;
2399
2400
2401 --
2402 ---------------------------update_or_delete_grade-----------------------------
2403 --
2404
2405 Procedure update_or_delete_grade
2406 ( p_copy_entity_txn_id in number,
2407 p_grade_result_id in number,
2408 p_effective_date in Date) IS
2409 /* Author : mvankada
2410 Purpose : This procedure Update/Delete the grade record
2411 */
2412
2413 -- To Get Pgm_Id of PGM based on Txn_id
2414 Cursor csr_pgm_id
2415 IS
2416 Select grdldr.Information1
2417 From Ben_Copy_Entity_Results grdldr
2418 Where grdldr.Table_Alias = 'PGM'
2419 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2420 And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id;
2421
2422 -- To Get Grade_Id (information253) of CPP based on Result_id of CPP
2423 Cursor csr_grade_id
2424 IS
2425 Select grd.Information253
2426 From Ben_Copy_Entity_Results grd
2427 Where grd.Table_Alias = 'CPP'
2428 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2429 And grd.Copy_Entity_Result_Id = p_Grade_Result_Id;
2430
2431 -- Get Plan Record
2432 Cursor csr_plan_result_id
2433 IS
2434 Select pln.Copy_Entity_Result_Id
2435 From Ben_Copy_Entity_Results pln
2436 Where pln.Gs_Mirror_Src_Entity_Result_Id = p_grade_result_id
2437 And pln.Copy_Entity_Txn_Id = p_copy_entity_txn_id
2438 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2439 And pln.Table_Alias = 'PLN';
2440
2441
2442 Cursor csr_ovn (l_result_id in Number)
2443 IS
2444 Select Object_Version_Number
2445 From Ben_Copy_Entity_Results
2446 Where Copy_Entity_Result_Id = l_result_id;
2447
2448 l_pgm_id Number;
2449 l_grade_id Number;
2450 l_pln_result_id Number;
2451 l_ovn Number;
2452 l_result_id Number;
2453 l_proc varchar2(72) := g_package|| 'update_or_delete_grade';
2454
2455
2456 Begin
2457 if g_debug then
2458 hr_utility.set_location('Entering:'||l_proc, 10);
2459 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2460 hr_utility.set_location('p_grade_result_id:'||p_grade_result_id, 20);
2461 hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2462 end if;
2463
2464
2465 Open csr_pgm_id;
2466 Fetch csr_pgm_id into l_pgm_id ;
2467 Close csr_pgm_id;
2468
2469 if g_debug then
2470 hr_utility.set_location('l_pgm_id:'||l_pgm_id, 30);
2471 end if;
2472
2473 Open csr_grade_id;
2474 Fetch csr_grade_id into l_grade_id;
2475 Close csr_grade_id;
2476
2477 if g_debug then
2478 hr_utility.set_location('l_grade_id:'||l_grade_id, 40);
2479 end if;
2480
2481 If (l_pgm_id IS Not Null And l_grade_id IS Not Null) Then
2482 if g_debug then
2483 hr_utility.set_location('Record is in Main Tables', 50);
2484 end if;
2485 if p_grade_result_id IS NOT NULL Then
2486 Open csr_ovn(p_grade_result_id);
2487 Fetch csr_ovn into l_ovn;
2488 Close csr_ovn;
2489
2490 ben_copy_entity_results_api.update_copy_entity_results(
2491 p_copy_entity_result_id => p_grade_result_id,
2492 p_effective_date => p_effective_date,
2493 p_information104 => 'UNLINK',
2494 p_object_version_number => l_ovn,
2495 p_information323 => null);
2496
2497 end if;
2498 Else
2499 if g_debug then
2500 hr_utility.set_location('Record is in Staging Area', 50);
2501 end if;
2502 if p_grade_result_id IS NOT NULL Then
2503
2504 -- Update Plan Record with Gs_Mirror_Src_Entity_Result_Id as Null
2505 Open csr_plan_result_id;
2506 Fetch csr_plan_result_id into l_pln_result_id;
2507 Close csr_plan_result_id;
2508
2509 Open csr_ovn(l_pln_result_id);
2510 Fetch csr_ovn into l_ovn;
2511 Close csr_ovn;
2512
2513 ben_copy_entity_results_api.update_copy_entity_results(
2514 p_copy_entity_result_id => l_pln_result_id,
2515 p_effective_date => p_effective_date,
2516 p_Gs_Mr_Src_Entity_Result_Id => NULL,
2517 p_object_version_number => l_ovn,
2518 p_information323 => null);
2519
2520
2521 -- Delete PLIP Record.
2522
2523 Open csr_ovn(p_grade_result_id);
2524 Fetch csr_ovn into l_ovn;
2525 Close csr_ovn;
2526
2527 ben_copy_entity_results_api.delete_copy_entity_results(
2528 p_copy_entity_result_id => p_grade_result_id,
2529 p_effective_date => p_effective_date,
2530 p_object_version_number => l_ovn);
2531 end if;
2532
2533 End if;
2534 if g_debug then
2535 hr_utility.set_location('Leaving:'||l_proc, 10);
2536 end if;
2537
2538 End update_or_delete_grade;
2539
2540 --
2541 ---------------------------update_or_delete_step-----------------------------
2542 --
2543
2544 Procedure update_or_delete_step
2545 ( p_copy_entity_txn_id in Number,
2546 p_step_result_id in Number,
2547 p_step_id in Number,
2548 p_point_result_id in Number,
2549 p_effective_date in Date) IS
2550
2551 /* Author : mvankada
2552 Purpose : This procedure Update/Deletes Step Record
2553
2554 If the Record is in the Staging Area only
2555 1) If Use_Prg_Points = 'STEP' then delete
2556 i) Point Record
2557 ii) Step Record
2558 2) If Use_Prg_Points = 'POINT' then delete
2559 i) Step Record
2560
2561 If the Record is in the Main Tables
2562 1) If Use_Prg_Points = 'STEP' then Update the following recors information104 as UNLINK
2563 i) Point Record
2564 ii) Step Record
2565 2) If Use_Prg_Points = 'POINT' then Update the following recors information104 as UNLINK
2566 i) Step Record
2567
2568 */
2569
2570 -- To Get information1 (Pgm_ID) of PGM, based on TXN_ID
2571 Cursor csr_pgm_id
2572 IS
2573 Select grdldr.information1 -- PGM_ID
2574 From Ben_Copy_Entity_Results grdldr
2575 Where grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
2576 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2577 AND grdldr.Table_Alias = 'PGM';
2578
2579 -- To Get information253 (Grade_id) of CPP, based on Step Result Id
2580 Cursor csr_grade_id
2581 IS
2582 Select grd.information253
2583 From Ben_Copy_Entity_Results grd
2584 Where grd.Table_Alias = 'CPP'
2585 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2586 AND grd.copy_entity_result_id = (Select step.gs_parent_entity_result_id
2587 From Ben_Copy_Entity_Results step
2588 Where step.Copy_Entity_Result_Id= p_step_result_id
2589 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2590 AND step.Table_Alias = 'COP');
2591
2592 Cursor csr_ovn (l_result_id in Number)
2593 IS
2594 Select Object_Version_Number
2595 From Ben_Copy_Entity_Results
2596 Where Copy_Entity_Result_Id = l_result_id;
2597
2598
2599 l_pgm_id Number;
2600 l_grade_id Number;
2601 l_use_points Varchar2(20);
2602 l_ovn Number;
2603 l_result_id Number;
2604
2605
2606 l_proc varchar2(72) := g_package|| 'update_or_delete_step';
2607
2608
2609 Begin
2610 if g_debug then
2611 hr_utility.set_location('Entering:'||l_proc, 10);
2612 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2613 hr_utility.set_location('p_step_result_id:'||p_step_result_id, 20);
2614 hr_utility.set_location('p_step_id:'||p_step_id, 30);
2615 hr_utility.set_location('p_point_result_id:'||p_point_result_id, 40);
2616 end if;
2617
2618
2619 l_use_points:= pqh_gsp_utility.USE_POINT_OR_STEP(p_copy_entity_txn_id=>p_copy_entity_txn_id);
2620 if g_debug then
2621 hr_utility.set_location('l_use_points:'||l_use_points, 50);
2622 end if;
2623
2624 Open csr_pgm_id;
2625 Fetch csr_pgm_id into l_pgm_id;
2626 Close csr_pgm_id;
2627 if g_debug then
2628 hr_utility.set_location('l_pgm_id:'||l_pgm_id,60);
2629 end if;
2630
2631 Open csr_grade_id;
2632 Fetch csr_grade_id into l_grade_id;
2633 Close csr_grade_id;
2634 if g_debug then
2635 hr_utility.set_location('l_grade_id:'||l_grade_id,70);
2636 end if;
2637
2638 -- Rec In Main Tables
2639 If (l_pgm_id IS NOT Null And l_grade_id IS NOT Null And p_step_id IS NOT Null) Then
2640 if g_debug then
2641 hr_utility.set_location('Rec is in Main Tables',80);
2642 end if;
2643
2644 -- Update Point Record
2645 if ((l_use_points = 'STEP') AND (p_point_result_id IS NOT NULL)) then
2646
2647 Open csr_ovn(p_point_result_id);
2648 Fetch csr_ovn into l_ovn;
2649 Close csr_ovn;
2650 ben_copy_entity_results_api.update_copy_entity_results(
2651 p_copy_entity_result_id => p_point_result_id,
2652 p_effective_date => p_effective_date,
2653 p_information104 => 'UNLINK',
2654 p_object_version_number => l_ovn,
2655 p_information323 => null);
2656
2657 end if;
2658 if g_debug then
2659 hr_utility.set_location('Point Rec is Updated Sucessfully',90);
2660 end if;
2661
2662 -- Update Step Record
2663 if p_step_result_id IS NOT NULL then
2664
2665 Open csr_ovn(p_step_result_id);
2666 Fetch csr_ovn into l_ovn;
2667 Close csr_ovn;
2668 ben_copy_entity_results_api.update_copy_entity_results(
2669 p_copy_entity_result_id => p_step_result_id,
2670 p_effective_date => p_effective_date,
2671 p_information104 => 'UNLINK',
2672 p_object_version_number => l_ovn,
2673 p_information323 => null);
2674
2675 End if;
2676 if g_debug then
2677 hr_utility.set_location('Step Rec is Updated Sucessfully',100);
2678 end if;
2679 Else
2680 if g_debug then
2681 hr_utility.set_location('Record is in Staging Area Only',110);
2682 end if;
2683 -- Delete Point Record
2684 if ((l_use_points = 'STEP') And p_point_result_id IS NOT NULL) then
2685
2686 Open csr_ovn(p_point_result_id);
2687 Fetch csr_ovn into l_ovn;
2688 Close csr_ovn;
2689 ben_copy_entity_results_api.delete_copy_entity_results(
2690 p_copy_entity_result_id => p_point_result_id,
2691 p_effective_date => p_effective_date,
2692 p_object_version_number => l_ovn);
2693 end if;
2694 if g_debug then
2695 hr_utility.set_location('Point Rec is Deleted Sucessfully',120);
2696 end if;
2697
2698 -- Delete Step Record
2699 if p_step_result_id IS NOT NULL then
2700
2701 Open csr_ovn(p_step_result_id);
2702 Fetch csr_ovn into l_ovn;
2703 Close csr_ovn;
2704 ben_copy_entity_results_api.delete_copy_entity_results(
2705 p_copy_entity_result_id => p_step_result_id,
2706 p_effective_date => p_effective_date,
2707 p_object_version_number => l_ovn);
2708 end if;
2709 if g_debug then
2710 hr_utility.set_location('Step Rec is Deleted Sucessfully',130);
2711 end if;
2712
2713 End if;
2714 if g_debug then
2715 hr_utility.set_location('Leaving :' ||l_proc, 200);
2716 end if;
2717 End update_or_delete_step;
2718
2719 --
2720
2721 --
2722 ---------------------------set_step_name-----------------------------
2723 --
2724
2725 PROCEDURE set_step_name(p_copy_entity_txn_id in number,
2726 p_effective_start_date in date,
2727 p_grd_result_id in number)
2728 IS
2729 l_proc varchar2(72) := g_package||'set_step_name';
2730 CURSOR csr_points
2731 IS
2732 select oipl.copy_entity_result_id,
2733 oipl.object_version_number
2734 from ben_copy_entity_results oipl,
2735 ben_copy_entity_results opt
2736 where nvl(oipl.information104,'PPP') <> 'UNLINK'
2737 AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
2738 AND nvl(opt.result_type_cd,'DISPLAY') = 'DISPLAY'
2739 and oipl.table_alias = 'COP'
2740 and opt.table_alias = 'OPT'
2741 and oipl.copy_entity_txn_id = p_copy_entity_txn_id
2742 and oipl.information262 = opt.copy_entity_result_id
2743 and oipl.gs_parent_entity_result_id = p_grd_result_id
2744 order by opt.information253;
2745 stepnum NUMBER :=0;
2746 begin
2747 if g_debug then
2748 hr_utility.set_location('Entering:'||l_proc, 5);
2749 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 10);
2750 hr_utility.set_location('p_effective_date:'||p_effective_start_date, 20);
2751 hr_utility.set_location('p_grd_result_id:'||p_grd_result_id, 30);
2752 end if;
2753 for i in csr_points loop
2754 stepnum :=stepnum+1;
2755 ben_copy_entity_results_api.update_copy_entity_results(
2756 p_copy_entity_result_id => i.copy_entity_result_id,
2757 p_effective_date => p_effective_start_date,
2758 p_copy_entity_txn_id => p_copy_entity_txn_id,
2759 p_information5 => fnd_message.get_string('PQH','PQH_GSP_STEP_PROMPT')||stepnum,
2760 p_information100 => fnd_message.get_string('PQH','PQH_GSP_STEP_PROMPT')||stepnum,
2761 p_object_version_number => i.object_version_number,
2762 p_information323 => null);
2763 end loop;
2764 if g_debug then
2765 hr_utility.set_location('Leaving:'||l_proc, 35);
2766 end if;
2767 END SET_STEP_NAME;
2768
2769 --
2770 --------------------------- chk_unlink_grd_from_grdldr -----------------------------
2771 --
2772 Procedure chk_unlink_grd_from_grdldr
2773 (p_pgm_id in Number
2774 ,p_copy_entity_txn_id in Number
2775 ,p_business_group_id in Number
2776 ,p_effective_date in Date
2777 ,p_status OUT NOCOPY Varchar2
2778 ) IS
2779 /*
2780 This procedure raises error if employee placements exists on unlinked grades
2781 */
2782
2783 l_proc varchar2(72) := g_package||'chk_unlink_grd_from_grdldr';
2784 l_dummy char(1);
2785 l_found Boolean;
2786 l_grd_war_found Boolean;
2787 l_no_grades Varchar2(4000) := null;
2788 l_no_war_grades Varchar2(4000) := null;
2789
2790 Cursor csr_grades
2791 IS
2792 Select Information253, -- Grade Id
2793 Information5 -- Grade Name
2794 From Ben_Copy_Entity_Results
2795 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
2796 And Table_Alias = 'CPP'
2797 And Information253 IS NOT NULL
2798 AND Nvl(Information104,'PPP') = 'UNLINK'
2799 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2800 And Information4 = p_Business_Group_Id;
2801
2802 -- Check Grade is attached to an Employee
2803 Cursor csr_gl_grd_emp_assg (l_grade_id Number)
2804 IS
2805 Select Null
2806 From per_all_assignments_f assgt
2807 Where grade_id = l_grade_id
2808 AND Grade_Ladder_Pgm_Id = p_pgm_id
2809 AND p_Effective_Date BETWEEN assgt.effective_start_date
2810 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
2811 AND assgt.business_group_id = p_Business_Group_Id;
2812
2813
2814 -- Check Grade is attached to an employee
2815 Cursor csr_grd_assg_emp (l_grade_id Number)
2816 IS
2817 Select Null
2818 From per_all_assignments_f assgt
2819 Where grade_id = l_grade_id
2820 AND p_Effective_Date BETWEEN assgt.effective_start_date
2821 AND nvl(assgt.effective_end_date,hr_general.end_of_time)
2822 AND assgt.business_group_id = p_Business_Group_Id;
2823
2824 -- Default Grade Ladder
2825 Cursor csr_default_grdldr
2826 IS
2827 Select Null
2828 From ben_pgm_f
2829 Where pgm_id = p_pgm_id
2830 And pgm_typ_cd = 'GSP'
2831 And Dflt_Pgm_Flag = 'Y'
2832 And business_group_id = p_business_group_id
2833 And p_Effective_Date Between Effective_Start_date
2834 And nvl(Effective_End_Date,hr_general.end_of_time);
2835
2836 BEGIN
2837 if g_debug then
2838 hr_utility.set_location('Entering:'||l_proc, 5);
2839 hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
2840 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2841 hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2842 hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2843 end if;
2844 p_status := null;
2845
2846 If p_pgm_id IS NOT NULL Then
2847 Open csr_default_grdldr;
2848 Fetch csr_default_grdldr into l_dummy;
2849 If csr_default_grdldr%NotFound Then
2850 if g_debug then
2851 hr_utility.set_location('Not Default Grade Ladder', 26);
2852 end if;
2853 --If Emp Placements exists on Grades then throw error msg
2854 For l_rec IN csr_grades
2855 Loop
2856 Open csr_gl_grd_emp_assg(l_rec.Information253);
2857 Fetch csr_gl_grd_emp_assg into l_dummy;
2858 If csr_gl_grd_emp_assg%Found Then
2859 l_no_grades := l_no_grades || l_rec.Information5 ||' , ';
2860 l_found := TRUE;
2861 End If;
2862 Close csr_gl_grd_emp_assg;
2863 End Loop;
2864 Else
2865 if g_debug then
2866 hr_utility.set_location(' Default Grade Ladder', 27);
2867 end if;
2868 -- If Emp Placements exists on Default Grade Ladder Grades then throw warning msg
2869 For l_rec IN csr_grades
2870 Loop
2871 Open csr_grd_assg_emp(l_rec.Information253);
2872 Fetch csr_grd_assg_emp into l_dummy;
2873 If csr_grd_assg_emp%Found Then
2874 if g_debug then
2875 hr_utility.set_location(' Grades are ... '||l_rec.Information5, 28);
2876 end if;
2877 l_no_war_grades := l_no_war_grades || l_rec.Information5 ||' , ';
2878 l_grd_war_found := TRUE;
2879 End If;
2880 Close csr_grd_assg_emp;
2881 End Loop;
2882 End if;
2883 Close csr_default_grdldr;
2884
2885 If l_found then
2886 if g_debug then
2887 hr_utility.set_location(' Found Error Grades', 27);
2888 end if;
2889 l_no_grades := substr(l_no_grades,1,length(l_no_grades)-2);
2890 p_status := 'E';
2891 hr_utility.set_message(8302,'PQH_GSP_CANNOT_UNLINK_GRADES');
2892 hr_utility.set_message_token('GRADES',l_no_grades );
2893 hr_multi_message.add;
2894 End if;
2895
2896 if l_grd_war_found then
2897 if g_debug then
2898 hr_utility.set_location(' Found Warning Grades', 27);
2899 end if;
2900 l_no_war_grades := substr(l_no_war_grades,1,length(l_no_war_grades)-2);
2901 hr_utility.set_location(' Length '||length(l_no_war_grades), 28);
2902 p_status := l_no_war_grades;
2903 end if;
2904 End If;
2905 if g_debug then
2906 hr_utility.set_location('Leaving:'||l_proc, 100);
2907 end if;
2908 Exception
2909 When Others Then
2910 if g_debug then
2911 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
2912 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
2913 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
2914 end if;
2915
2916 p_status := null;
2917 END chk_unlink_grd_from_grdldr;
2918
2919 --
2920 --------------------------- chk_unlink_step_from_grdldr -----------------------------
2921 --
2922 Procedure chk_unlink_step_from_grdldr
2923 (p_copy_entity_txn_id in Number
2924 ,p_business_group_id in Number
2925 ,p_effective_date in Date
2926 ) IS
2927 /* This procedure raises error if Step is
2928 1) Ceiling Step
2929 2) Special Ceiling Step
2930 3) Employee Placements on this Step
2931 */
2932
2933 l_proc varchar2(72) := g_package||'chk_unlink_step_from_grdldr ';
2934 l_dummy char(1);
2935 l_found Boolean;
2936 l_no_steps Varchar2(4000) := null;
2937
2938 Cursor csr_steps
2939 IS
2940 Select Information253, -- Step Id
2941 Information5 -- Step Name
2942 INFORMATION98 -- Ceiling Flag
2943 From Ben_Copy_Entity_Results
2944 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
2945 And Table_Alias = 'COP'
2946 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
2947 And Information253 IS NOT NULL
2948 AND Nvl(Information104,'PPP') = 'UNLINK'
2949 And Information4 = p_business_group_id ;
2950
2951 BEGIN
2952 if g_debug then
2953 hr_utility.set_location('Entering:'||l_proc, 5);
2954 hr_utility.set_location('p_copy_entity_txn_id:'||p_copy_entity_txn_id, 15);
2955 hr_utility.set_location('p_business_group_id:'||p_business_group_id, 20);
2956 hr_utility.set_location('p_effective_date:'||p_effective_date, 25);
2957 end if;
2958
2959 For l_rec IN csr_steps
2960 Loop
2961 PER_SPINAL_POINT_STEPS_PKG.del_chks_del(p_step_id => l_rec.Information253,
2962 p_sess => p_effective_date);
2963
2964 If (l_rec.INFORMATION98 = 'Y') Then -- Ceiling_Step_Flag Values Y/N
2965 hr_utility.set_message(801, 'PER_7937_DEL_CEIL_STEP');
2966 hr_utility.raise_error;
2967 END IF;
2968 End Loop;
2969
2970 if g_debug then
2971 hr_utility.set_location('Leaving:'||l_proc, 100);
2972 end if;
2973
2974 END chk_unlink_step_from_grdldr;
2975
2976
2977 --
2978 --------------------------- chk_grdldr_name_unique -----------------------------
2979 --
2980 Procedure chk_grdldr_name_unique
2981 ( p_pgm_id in Number
2982 ,p_business_group_id in Number
2983 ,p_name in varchar2
2984 )
2985 is
2986 l_proc varchar2(72) := g_package||'chk_grdldr_name_unique';
2987 l_dummy char(1);
2988
2989 Cursor csr_name
2990 IS
2991 Select null
2992 From Ben_Pgm_F
2993 Where Pgm_Id <> nvl(p_pgm_id,-1)
2994 And Name = p_name
2995 And Business_Group_Id = p_business_group_id;
2996
2997 --
2998 Begin
2999 if g_debug then
3000 hr_utility.set_location('Entering:'||l_proc, 5);
3001 hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3002 hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3003 end if;
3004
3005
3006 --
3007 if g_debug then
3008 hr_utility.set_location('Chk Name...', 50);
3009 end if;
3010 Open csr_name;
3011 Fetch csr_name into l_dummy;
3012 If csr_name%Found then
3013 Close csr_name;
3014 hr_utility.set_message(8302,'PQH_GSP_GRDLDR_NAME_UNIQUE');
3015 hr_multi_message.add;
3016 Else
3017 Close csr_name;
3018 End If;
3019 --
3020 if g_debug then
3021 hr_utility.set_location('Leaving:'||l_proc, 15);
3022 end if;
3023 End chk_grdldr_name_unique;
3024
3025 --
3026 --------------------------- chk_gl_sht_name_code_unique -----------------------------
3027 --
3028 Procedure chk_gl_sht_name_code_unique
3029 ( p_pgm_id in Number
3030 ,p_business_group_id in Number
3031 ,p_short_name in varchar2 Default Null
3032 ,p_short_code in varchar2 Default Null)
3033 is
3034 l_proc varchar2(72) := g_package||'chk_gl_sht_name_code_unique';
3035 l_dummy char(1);
3036
3037
3038 Cursor csr_short_name
3039 IS
3040 Select null
3041 From Ben_Pgm_F
3042 Where Pgm_Id <> nvl(p_pgm_id,-1)
3043 And Short_Name = nvl(p_short_name,-1)
3044 And Business_Group_Id = p_business_group_id
3045 And p_short_name IS NOT NULL;
3046
3047 Cursor csr_short_code
3048 IS
3049 Select null
3050 From Ben_Pgm_F
3051 Where Pgm_Id <> nvl(p_pgm_id,-1)
3052 And Short_Code = nvl(p_short_code,-1)
3053 And Business_Group_Id = p_business_group_id
3054 And p_short_code IS NOT NULL;
3055
3056 --
3057 Begin
3058 if g_debug then
3059 hr_utility.set_location('Entering:'||l_proc, 5);
3060 hr_utility.set_location('p_pgm_id :'||p_pgm_id ,10);
3061 hr_utility.set_location('p_business_group_id:'||p_business_group_id, 15);
3062 hr_utility.set_location('p_short_name:'||p_short_name, 20);
3063 hr_utility.set_location('p_short_code:'||p_short_code, 25);
3064 hr_utility.set_location('Chk Short Name...', 60);
3065 end if;
3066
3067 Open csr_short_name;
3068 Fetch csr_short_name into l_dummy;
3069 If csr_short_name%Found then
3070 Close csr_short_name;
3071 hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_CODE_UNQ');
3072 hr_multi_message.add;
3073 Else
3074 Close csr_short_name;
3075 End If;
3076 --
3077 if g_debug then
3078 hr_utility.set_location('Chk Short Code....', 70);
3079 end if;
3080 Open csr_short_code;
3081 Fetch csr_short_code into l_dummy;
3082 If csr_short_code%Found then
3083 Close csr_short_code;
3084 hr_utility.set_message(8302,'PQH_GSP_GL_SHT_NAME_CODE_UNQ');
3085 hr_multi_message.add;
3086 Else
3087 Close csr_short_code;
3088 End If;
3089 --
3090 if g_debug then
3091 hr_utility.set_location('Leaving:'||l_proc, 15);
3092 end if;
3093 End chk_gl_sht_name_code_unique;
3094
3095
3096 --
3097 ---------------------------validate_grade_ladder-----------------------------
3098 --
3099 /*
3100 The following procedure validates the grade ladder before it is saved.
3101 */
3102 Procedure validate_grade_ladder(
3103 p_pgm_id in number
3104 ,p_effective_start_date in date
3105 ,p_effective_end_date in date
3106 ,p_name in varchar2
3107 ,p_pgm_stat_cd in varchar2
3108 ,p_pgm_typ_cd in varchar2
3109 ,p_enrt_cvg_strt_dt_cd in varchar2
3110 ,p_enrt_cvg_strt_dt_rl in number
3111 ,p_rt_strt_dt_cd in varchar2
3112 ,p_rt_strt_dt_rl in number
3113 ,p_pgm_uom in varchar2
3114 ,p_enrt_cd in varchar2
3115 ,p_enrt_mthd_cd in varchar2
3116 ,p_enrt_rl in number
3117 ,p_auto_enrt_mthd_rl in number
3118 ,p_business_group_id in number
3119 ,p_Dflt_pgm_flag in Varchar2
3120 ,p_Use_prog_points_flag in Varchar2
3121 ,p_Dflt_step_cd in Varchar2
3122 ,p_Dflt_step_rl in number
3123 ,p_Update_salary_cd in Varchar2
3124 ,p_Use_multi_pay_rates_flag in Varchar2
3125 ,p_dflt_element_type_id in number
3126 ,p_Dflt_input_value_id in number
3127 ,p_Use_scores_cd in Varchar2
3128 ,p_Scores_calc_mthd_cd in Varchar2
3129 ,p_Scores_calc_rl in number
3130 ,p_gsp_allow_override_flag in varchar2
3131 ,p_use_variable_rates_flag in varchar2
3132 ,p_salary_calc_mthd_cd in varchar2
3133 ,p_salary_calc_mthd_rl in number
3134 ,p_effective_date in date
3135 ,p_short_name in varchar2
3136 ,p_short_code in varchar2
3137 ) is
3138
3139 l_proc varchar2(72) := g_package||'validate_grade_ladder';
3140
3141 BEGIN
3142 if g_debug then
3143 hr_utility.set_location('Entering :' ||l_proc, 10);
3144 end if;
3145
3146 -- Check Grade Ladder Name is Unique with in the Business Group
3147 if g_debug then
3148 hr_utility.set_location('Check Grade Ladder Name is Unique ', 20);
3149 end if;
3150 pqh_gsp_utility.chk_grdldr_name_unique( p_pgm_id => p_pgm_id
3151 ,p_business_group_id => p_business_group_id
3152 ,p_name => p_name);
3153
3154 -- Check Grade Ladder , Short Name and Short Code is Unique with in the Business Group
3155 if g_debug then
3156 hr_utility.set_location('Check Grade Ladder Short Name and Short Code is Unique ', 25);
3157 end if;
3158
3159 If (p_short_name IS NOT NULL or p_short_code IS NOT NULL) Then
3160 pqh_gsp_utility.chk_gl_sht_name_code_unique( p_pgm_id => p_pgm_id
3161 ,p_business_group_id => p_business_group_id
3162 ,p_short_name => p_short_name
3163 ,p_short_code => p_short_code);
3164
3165 End If;
3166 -- Check if there is only one default grade ladder in the business group
3167 if g_debug then
3168 hr_utility.set_location('Default Grade Ladder in BG ', 30);
3169 end if;
3170
3171 pqh_gsp_utility.chk_default_ladder_exist ( p_pgm_id => p_pgm_id,
3172 p_business_group_id => p_business_group_id,
3173 p_effective_date => p_effective_date);
3174
3175 -- The system should not allow inactivating an existing grade ladder on
3176 -- which there are employee placements.
3177
3178 /*
3179 if g_debug then
3180 hr_utility.set_location('Inactivated GL ', 40);
3181 end if;
3182 pqh_gsp_utility.chk_inactivate_grdldr ( p_pgm_id => p_pgm_id,
3183 p_effective_date => p_effective_date,
3184 p_business_group_id => p_business_group_id);
3185 */
3186
3187
3188
3189
3190 -- Check if score calculation method is entered against lookup
3191 -- Check if score calcultion method is rule, the fast formula is selected
3192 -- Validate all lookups
3193 if g_debug then
3194 hr_utility.set_location('Leaving :' ||l_proc, 100);
3195 end if;
3196 End;
3197 --
3198 --
3199 ---------------------------chk_grdldr_grd_curreny_rate-----------------------------
3200 --
3201 Procedure chk_grdldr_grd_curreny_rate
3202 (p_copy_entity_txn_id In Number,
3203 p_business_group_id In Number,
3204 p_effective_date In Date) IS
3205
3206 -- fix for bug 7114098
3207 l_pgm_id number;
3208 cursor csr_pgm_val is
3209 select information1
3210 from Ben_Copy_Entity_Results
3211 where Copy_Entity_Txn_Id = p_copy_entity_txn_id
3212 and Information4 = p_business_group_id
3213 and table_alias = 'PGM' ;
3214
3215 -- fix for bug 7114098
3216
3217 -- Get Grade Ladder Details
3218 Cursor csr_grdldr_details
3219 IS
3220 Select Copy_Entity_Result_Id,
3221 Information50, -- Currency_Code
3222 Information41 -- Rate_Period
3223 From Ben_Copy_Entity_Results
3224 Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
3225 AND Table_Alias = 'PGM'
3226 AND Result_Type_Cd = 'DISPLAY'
3227 AND Nvl(Information104,'PPP') <> 'UNLINK'
3228 AND Information4 = p_business_group_id ;
3229
3230
3231 -- Get Grade Ids
3232 Cursor csr_grade_ids(l_grdldr_result_id IN Number)
3233 IS
3234 Select Information253, -- Grade Id
3235 Information5 -- Grade Name
3236 From Ben_Copy_Entity_Results
3237 Where Gs_Parent_Entity_Result_Id = l_grdldr_result_id
3238 And Copy_Entity_Txn_Id = p_copy_entity_txn_id
3239 And Table_Alias = 'CPP'
3240 AND Nvl(Information104,'PPP') <> 'UNLINK'
3241 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3242 AND Information253 IS NOT NULL
3243 AND Information4 = p_business_group_id ;
3244
3245
3246 Cursor csr_pgm_detials(l_grade_id IN Number, l_rate IN Varchar, l_currency IN Varchar) Is
3247 Select Pgm.ACTY_REF_PERD_CD, -- Rate
3248 Pgm.Pgm_uom -- Currency
3249 From Ben_Pl_f Pl,
3250 Ben_Plip_F Plip,
3251 Ben_Pgm_F Pgm
3252 Where Pl.MAPPING_TABLE_PK_ID = l_grade_Id
3253 and Pl.MAPPING_TABLE_NAME = 'PER_GRADES'
3254 and P_Effective_Date
3255 Between Pl.Effective_Start_Date
3256 and Nvl(Pl.Effective_End_Date,hr_general.end_of_time)
3257 and Plip.Pl_Id = Pl.Pl_Id
3258 and P_Effective_Date
3259 Between Plip.Effective_Start_Date
3260 and Nvl(Plip.Effective_End_Date,hr_general.end_of_time)
3261 and Plip.Pgm_Id = Pgm.Pgm_Id
3262 and P_Effective_Date
3263 Between Pgm.Effective_Start_Date
3264 and Nvl(Pgm.Effective_End_Date,hr_general.end_of_time)
3265 and pgm.Business_group_id = p_Business_Group_Id
3266 and (pgm.ACTY_REF_PERD_CD <> l_rate
3267 or pgm.Pgm_Uom <> l_currency)
3268 and Pgm_Typ_Cd = 'GSP'
3269 and l_pgm_id <> pgm.pgm_id; -- added for bug 7114098
3270
3271 l_proc varchar2(72) := g_package|| 'chk_grdldr_grd_curreny_rate';
3272 l_curr_gl_result_id Number;
3273 l_curr_gl_currency Ben_Copy_Entity_Results.Information50%Type;
3274 l_curr_gl_period Ben_Copy_Entity_Results.Information41%Type;
3275
3276 l_target_currency Ben_Pgm_F.PGM_UOM%Type;
3277 l_target_period Ben_Pgm_F.ACTY_REF_PERD_CD%Type;
3278
3279 l_dummy Varchar2(4000) := null;
3280 l_found Boolean := FALSE;
3281
3282
3283
3284 BEGIN
3285 if g_debug then
3286 hr_utility.set_location('Entering :' ||l_proc, 10);
3287 hr_utility.set_location('p_copy_entity_txn_id:' ||p_copy_entity_txn_id, 20);
3288 hr_utility.set_location('p_business_group_id:' ||p_business_group_id, 30);
3289 hr_utility.set_location('p_effective_date:' ||p_effective_date, 40);
3290 end if;
3291
3292
3293 Open csr_grdldr_details;
3294 Fetch csr_grdldr_details into l_curr_gl_result_id,l_curr_gl_currency,l_curr_gl_period;
3295 Close csr_grdldr_details;
3296
3297 if g_debug then
3298 hr_utility.set_location('l_curr_gl_result_id:' ||l_curr_gl_result_id, 50);
3299 hr_utility.set_location('l_curr_gl_currency:' ||l_curr_gl_currency, 60);
3300 hr_utility.set_location('l_curr_gl_period:' ||l_curr_gl_period, 70);
3301 end if;
3302
3303 -- fix for bug 7114098
3304 open csr_pgm_val;
3305 fetch csr_pgm_val into l_pgm_id;
3306 close csr_pgm_val;
3307 -- fix for bug 7114098
3308
3309 For l_grade_id_rec IN csr_grade_ids(l_curr_gl_result_id)
3310 Loop
3311
3312 Open csr_pgm_detials(l_grade_id_rec.Information253,l_curr_gl_period, l_curr_gl_currency);
3313 Fetch csr_pgm_detials into l_target_currency,l_target_period;
3314 If csr_pgm_detials%Found Then
3315 l_found := TRUE;
3316 l_dummy := l_dummy || l_grade_id_rec.Information5 ||' , ';
3317 End If;
3318 Close csr_pgm_detials;
3319 End Loop;
3320
3321
3322 if l_found Then
3323 l_dummy := substr(l_dummy,1,length(l_dummy)-2);
3324 if g_debug then
3325 hr_utility.set_location('l_dummy 1:' ||substr(l_dummy,1,50), 80);
3326 hr_utility.set_location('l_dummy 2:' ||substr(l_dummy,51,100), 81);
3327 hr_utility.set_location('l_dummy 3:' ||substr(l_dummy,101,150), 82);
3328 end if;
3329
3330 hr_utility.set_message(8302,'PQH_GSP_GRDS_DIFF_CURR_PERIOD');
3331 hr_utility.set_message_token('GRADES',l_dummy );
3332 hr_multi_message.add;
3333 end if;
3334 if g_debug then
3335 hr_utility.set_location('Leaving :' ||l_proc, 100);
3336 end if;
3337 END chk_grdldr_grd_curreny_rate;
3338
3339 --
3340 --
3341 ---------------------------------------------------------------------------
3342 -- This procedure validates if duplicate criteria set has been defined
3343 -- and attached to the grade ladder.
3344 --
3345 Procedure chk_duplicate_crset_exists(
3346 p_copy_entity_txn_id in number,
3347 p_effective_date in date,
3348 p_cset_id in number default null,
3349 p_location_id in number default null,
3350 p_job_id in number default null,
3351 p_org_id in number default null,
3352 p_rule_id in number default null,
3353 p_person_type_id in number default null,
3354 p_service_area_id in number default null,
3355 p_barg_unit_cd in varchar2 default null,
3356 p_full_part_time_cd in varchar2 default null,
3357 p_perf_type_cd in varchar2 default null,
3358 p_rating_type_cd in varchar2 default null,
3359 p_duplicate_exists out nocopy varchar2,
3360 p_duplicate_cset_name out nocopy varchar2) is
3361 --
3362 -- Select all crsets in the txn which are not equal to current crset
3363 --
3364 cursor csr_crset is
3365 select *
3366 from ben_copy_entity_results
3367 where copy_entity_txn_id = p_copy_entity_txn_id
3368 and table_alias = 'CRSET'
3369 and p_effective_date between information2 and nvl(information3, to_date('31/12/4712','dd/mm/RRRR'))
3370 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3371 and INFORMATION161 <> nvl(p_cset_id, -1)
3372 order by information161,information2;
3373 --
3374 l_proc varchar2(200) := 'chk_duplicate_crset_exists';
3375 --
3376 Begin
3377 --
3378 hr_utility.set_location('Entering :' ||l_proc, 5);
3379 --
3380 p_duplicate_exists := 'N';
3381 --
3382 For crset_rec in csr_crset loop
3383 --
3384 -- Check for duplicate criteria set
3385 --
3386 If (nvl(crset_rec.INFORMATION232,-1) = nvl(p_location_id,-1) and
3387 nvl(crset_rec.INFORMATION233,-1) = nvl(p_job_id,-1) and
3388 nvl(crset_rec.INFORMATION234,-1) = nvl(p_org_id,-1) and
3389 nvl(crset_rec.INFORMATION235,-1) = nvl(p_rule_id,-1) and
3390 nvl(crset_rec.INFORMATION236,-1) = nvl(p_person_type_id,-1) and
3391 nvl(crset_rec.INFORMATION237,-1) = nvl(p_service_area_id,-1) and
3392 nvl(crset_rec.INFORMATION101,'XXX') = nvl(p_barg_unit_cd,'XXX') and
3393 nvl(crset_rec.INFORMATION102,'XXX') = nvl(p_full_part_time_cd,'XXX') and
3394 nvl(crset_rec.INFORMATION103,'XXX') = nvl(p_perf_type_cd,'XXX') and
3395 nvl(crset_rec.INFORMATION104,'XXX') = nvl(p_rating_type_cd,'XXX')) then
3396 --
3397 p_duplicate_cset_name := crset_rec.INFORMATION151;
3398 p_duplicate_exists := 'Y';
3399 hr_utility.set_location('Duplicate :' ||p_duplicate_cset_name, 7);
3400 --
3401 End if;
3402 --
3403 --
3404 End loop;
3405 --
3406 hr_utility.set_location('Leaving :' ||l_proc, 10);
3407 --
3408 End;
3409 -------------------------------------------------------------------
3410 -- This procedure validates if criteria set has been properly defined i.e
3411 -- 1) Criteria set name is not null
3412 -- 2) At least one criterion is entered
3413 -- 3) If performance type is entered, rating type must also be entered
3414 --
3415 Procedure validate_crset_values(p_copy_entity_txn_id in number,
3416 p_effective_date in date)
3417 is
3418 --
3419 cursor csr_crset is
3420 select *
3421 from ben_copy_entity_results
3422 where copy_entity_txn_id = p_copy_entity_txn_id
3423 and table_alias = 'CRSET'
3424 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3425 and dml_operation in ('INSERT','UPDATE') -- Check only insert/ updates crset
3426 order by information161,information2;
3427 --
3428 l_duplicate_exists varchar2(10);
3429 l_duplicate_cset_name varchar2(240);
3430 l_proc varchar2(200) := 'validate_crset_values';
3431 l_continue boolean;
3432 --
3433 Begin
3434 --
3435 hr_utility.set_location('Entering :' ||l_proc, 5);
3436 --
3437 For crset_rec in csr_crset loop
3438 --
3439 l_continue := true;
3440 --
3441 -- Criteria set name should be entered. Ideally this error should never be raised.
3442 --
3443 If crset_rec.INFORMATION151 is NULL then
3444 hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NAME_ERR');
3445 hr_multi_message.add;
3446 l_continue := false;
3447 End if;
3448 --
3449 -- Criteria set should have at least one criterion
3450 --
3451 If l_continue then
3452 --
3453 hr_utility.set_location('Check 1' , 5);
3454 --
3455 If (crset_rec.INFORMATION232 IS NULL and
3456 crset_rec.INFORMATION233 IS NULL and
3457 crset_rec.INFORMATION234 IS NULL and
3458 crset_rec.INFORMATION235 IS NULL and
3459 crset_rec.INFORMATION236 IS NULL and
3460 crset_rec.INFORMATION237 IS NULL and
3461 crset_rec.INFORMATION101 IS NULL and
3462 crset_rec.INFORMATION102 IS NULL and
3463 crset_rec.INFORMATION103 IS NULL and
3464 crset_rec.INFORMATION104 IS NULL ) then
3465 --
3466 hr_utility.set_message(8302,'PQH_GSP_CRI_SET_NO_CRIT');
3467 hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3468 hr_multi_message.add;
3469 l_continue := false;
3470 --
3471 End if;
3472 --
3473 End if;
3474
3475 If l_continue then
3476 --
3477 --
3478 hr_utility.set_location('Perf Rating Check ' , 5);
3479 --
3480 -- If performance type is entered, rating type must also be entered.
3481 --
3482 If ((crset_rec.INFORMATION103 IS NULL and crset_rec.INFORMATION104 IS NOT NULL) OR
3483 (crset_rec.INFORMATION103 IS NOT NULL and crset_rec.INFORMATION104 IS NULL)) then
3484 --
3485 hr_utility.set_message(8302,'PQH_GSP_INVALID_PERF_RATING');
3486 hr_utility.set_message_token('CSET',crset_rec.INFORMATION151);
3487 hr_multi_message.add;
3488 l_continue := false;
3489 --
3490 End if;
3491 End if;
3492 --
3493 --
3494 -- Check if there exists a duplicate for the current criteria set in the transaction
3495 --
3496 If l_continue then
3497 --
3498 --
3499 hr_utility.set_location('Duplicate Check' || to_char(crset_rec.information161) , 5);
3500 --
3501 chk_duplicate_crset_exists(
3502 p_copy_entity_txn_id => p_copy_entity_txn_id,
3503 p_effective_date => p_effective_date,
3504 p_cset_id => crset_rec.information161,
3505 p_location_id => crset_rec.INFORMATION232,
3506 p_job_id => crset_rec.INFORMATION233,
3507 p_org_id => crset_rec.INFORMATION234,
3508 p_rule_id => crset_rec.INFORMATION235,
3509 p_person_type_id => crset_rec.INFORMATION236,
3510 p_service_area_id => crset_rec.INFORMATION237,
3511 p_barg_unit_cd => crset_rec.INFORMATION101,
3512 p_full_part_time_cd => crset_rec.INFORMATION102,
3513 p_perf_type_cd => crset_rec.INFORMATION103,
3514 p_rating_type_cd => crset_rec.INFORMATION104,
3515 p_duplicate_exists => l_duplicate_exists,
3516 p_duplicate_cset_name => l_duplicate_cset_name);
3517 --
3518 If l_duplicate_exists = 'Y' then
3519 --
3520 hr_utility.set_location('Duplicate cset || l_duplicate_cset_name' , 5);
3521 hr_utility.set_message(8302,'PQH_GSP_DUPLICATE_CSETS_ERR');
3522 hr_utility.set_message_token('CSET1',crset_rec.INFORMATION151);
3523 hr_utility.set_message_token('CSET2',l_duplicate_cset_name);
3524 hr_multi_message.add;
3525 --
3526 End if;
3527 --
3528 End if;
3529 --
3530 End loop;
3531 --
3532 hr_utility.set_location('Leaving :' ||l_proc, 10);
3533 --
3534 End;
3535 ---------------------------chk_review_submit_val-----------------------------
3536 --
3537 Procedure chk_review_submit_val
3538 (p_copy_entity_txn_id in Number,
3539 p_effective_date in Date,
3540 p_pgm_id in Number,
3541 p_business_group_id in Number,
3542 p_status OUT NOCOPY Varchar2,
3543 p_prog_le_created_flag OUT NOCOPY Varchar2,
3544 p_sync_le_created_flag OUT NOCOPY Varchar2,
3545 p_plan_tp_created_flag OUT NOCOPY Varchar2
3546 ) IS
3547 /*
3548 Author : mvankada
3549 Purpose : This procedure checks all validations to be performed
3550 for Review and submit Page
3551
3552 */
3553
3554 l_proc varchar2(72) := g_package||'chk_review_submit_val';
3555 l_pt_le_status Varchar2(30);
3556 l_plan_type_start_date DATE:= get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
3557 Cursor csr_pgm_details
3558 IS
3559 Select Name,
3560 Short_Name,
3561 Short_Code
3562 From Ben_Pgm_F
3563 Where Pgm_id Is Not Null
3564 And Pgm_id = p_pgm_id
3565 And Business_Group_Id = p_business_group_id;
3566
3567 Cursor csr_grdldr_date
3568 IS
3569 Select Information2 EFFECTIVE_START_DATE
3570 From Ben_Copy_Entity_Results
3571 Where Copy_Entity_Txn_id = p_copy_entity_txn_id
3572 And Information4 = p_business_group_id
3573 And Table_Alias = 'PGM'
3574 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3575 And Information_Category = 'GRADE_LADDER';
3576
3577
3578 l_name Ben_Pgm_F.Name%Type;
3579 l_short_name Ben_Pgm_F.Short_Name%Type;
3580 l_short_code Ben_Pgm_F.Short_Code%Type;
3581 l_status Varchar2(20) := 'E';
3582 l_error_status Varchar2(20) := 'E';
3583 l_grdldr_date Date;
3584
3585 BEGIN
3586 hr_multi_message.enable_message_list;
3587 if g_debug then
3588 hr_utility.set_location('Entering :' ||l_proc, 10);
3589 end if;
3590
3591 If p_pgm_id IS NOT NULL Then
3592 Open csr_pgm_details;
3593 Fetch csr_pgm_details into l_name,l_short_name,l_short_code;
3594 Close csr_pgm_details;
3595 End if;
3596
3597
3598 -- Check if there is only one default grade ladder in the business group
3599 -- The system should not allow inactivating an existing grade ladder on
3600 -- which there are employee placements.
3601
3602 if p_pgm_id IS NOT NULL Then
3603 pqh_gsp_utility.validate_grade_ladder(p_pgm_id => p_pgm_id
3604 ,p_effective_date => p_effective_date
3605 ,p_name => l_name
3606 ,p_short_name => l_short_name
3607 ,p_short_code => l_short_code
3608 ,p_business_group_id =>p_business_group_id
3609 );
3610 end if;
3611
3612 -- Check Grades can be removed from the Grade Ladder
3613 if g_debug then
3614 hr_utility.set_location('Check Grades can be removed from the Grade Ladder', 20);
3615 end if;
3616 pqh_gsp_utility.chk_unlink_grd_from_grdldr
3617 (p_pgm_id => p_pgm_id
3618 ,p_copy_entity_txn_id => p_copy_entity_txn_id
3619 ,p_business_group_id => p_business_group_id
3620 ,p_effective_date => p_effective_date
3621 ,p_status => l_status
3622 );
3623 if g_debug then
3624 hr_utility.set_location('After Check unlink grd from gl ',25);
3625 hr_utility.set_location('l_status : '||substr(l_status,1,50),26);
3626 hr_utility.set_location('l_status : '||substr(l_status,51,100),27);
3627 hr_utility.set_location('l_status : '||substr(l_status,101,150),28);
3628 hr_utility.set_location('l_status : '||substr(l_status,151,200),29);
3629 end if;
3630
3631 -- Check if steps have been added to only some steps in a grade
3632 -- and warns the user that the setup cannot be saved unless steps are added to all
3633 -- the Grades.
3634
3635 if g_debug then
3636 hr_utility.set_location('Only Some Steps are attached Validation', 30);
3637 end if;
3638 pqh_gsp_utility.chk_add_steps_in_all_grades ( p_copy_entity_txn_id => p_copy_entity_txn_id ,
3639 p_business_group_id => p_business_group_id);
3640
3641 -- Get Grade Ladder Effective Date
3642 Open csr_grdldr_date;
3643 Fetch csr_grdldr_date into l_grdldr_date;
3644 Close csr_grdldr_date;
3645
3646 -- Check if Grades are valid as of the Grade Ladder Effective Date
3647
3648
3649 if g_debug then
3650 hr_utility.set_location('InValid Grades Validation', 40);
3651 end if;
3652 pqh_gsp_utility.chk_valid_grd_in_grdldr ( p_copy_entity_txn_id => p_copy_entity_txn_id,
3653 p_effective_date => p_effective_date,
3654 p_business_group_id => p_business_group_id);
3655
3656
3657 -- Check Currecny, Rate Period
3658
3659 if g_debug then
3660 hr_utility.set_location('Currecny, Rate Period Validation', 70);
3661 end if;
3662 pqh_gsp_utility.chk_grdldr_grd_curreny_rate ( p_copy_entity_txn_id => p_copy_entity_txn_id
3663 ,p_business_group_id => p_business_group_id
3664 ,p_effective_date => p_effective_date);
3665
3666 -- Check Steps can be removed from the Grade Ladder
3667 if g_debug then
3668 hr_utility.set_location('Check Steps can be removed from the Grade Ladder', 80);
3669 end if;
3670
3671 pqh_gsp_utility.chk_unlink_step_from_grdldr
3672 (p_copy_entity_txn_id => p_copy_entity_txn_id
3673 ,p_business_group_id => p_business_group_id
3674 ,p_effective_date => p_effective_date
3675 );
3676
3677 -- Check for Plan Type and Life Event
3678 if g_debug then
3679 hr_utility.set_location('Check Plan Type and Life Event', 85);
3680 end if;
3681 pqh_gsp_stage_to_hr.setup_check( p_copy_entity_txn_id => p_copy_entity_txn_id,
3682 p_effective_date => p_effective_date,
3683 p_business_group_id => p_business_group_id,
3684 p_status => l_pt_le_status,
3685 p_prog_le_created_flag => p_prog_le_created_flag,
3686 p_sync_le_created_flag => p_sync_le_created_flag,
3687 p_plan_tp_created_flag => p_plan_tp_created_flag
3688 );
3689
3690 if l_pt_le_status = 'WRONG-DATE-PROG-LE' then
3691 hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PROG_LE');
3692 hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3693 hr_multi_message.add;
3694 elsif l_pt_le_status = 'MANY-PROG-LE' then
3695 hr_utility.set_message(8302,'PQH_GSP_MANY_PROG_LE');
3696 hr_multi_message.add;
3697 elsif l_pt_le_status = 'WRONG-DATE-SYNC-LE' then
3698 hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_SYNC_LE');
3699 hr_utility.set_message_token('PLANTYPESTARTDATE',l_plan_type_start_date);
3700 hr_multi_message.add;
3701 elsif l_pt_le_status = 'MANY-SYNC-LE' then
3702 hr_utility.set_message(8302,'PQH_GSP_MANY_SYNC_LE');
3703 hr_multi_message.add;
3704 elsif l_pt_le_status = 'WRONG-DATE-PT' then
3705 hr_utility.set_message(8302,'PQH_GSP_WRONG_ST_DT_PT');
3706 hr_multi_message.add;
3707 elsif l_pt_le_status = 'MANY-PT' then
3708 hr_utility.set_message(8302,'PQH_GSP_MANY_PT');
3709 hr_multi_message.add;
3710 elsif l_pt_le_status = 'PROG-LE-ERR' or l_pt_le_status = 'SYNC-LE-ERR' or l_pt_le_status = 'PT-ERR' then
3711 hr_utility.set_message(8302,'PQH_GSP_PT_LE_ERR');
3712 hr_multi_message.add;
3713 else
3714 null;
3715 end if;
3716
3717 --
3718 -- check values in crset.
3719 --
3720 validate_crset_values(p_copy_entity_txn_id => p_copy_entity_txn_id,
3721 p_effective_date => p_effective_date);
3722 --
3723 --
3724 if g_debug then
3725 hr_utility.set_location('Check FR PS Corps Review Submit Validatsons...', 87);
3726 end if;
3727
3728 -- Adde a call for FR Corps.
3729 pqh_corps_utility.review_submit_valid_corps(p_copy_entity_txn_id => p_copy_entity_txn_id
3730 ,p_effective_date => p_effective_date
3731 ,p_business_group_id => p_business_group_id
3732 ,p_status => l_error_status);
3733
3734
3735 -- Call to raise any errors on multi-message list
3736 hr_multi_message.end_validation_set;
3737
3738 p_status := l_status;
3739 if g_debug then
3740 hr_utility.set_location('Leaving :' ||l_proc, 200);
3741 end if;
3742 Exception
3743
3744 when hr_multi_message.error_message_exist then
3745 p_status := null;
3746 if g_debug then
3747 hr_utility.set_location('Error :' ||substr(sqlerrm,1,50), 240);
3748 hr_utility.set_location('Leaving:' || l_proc, 300);
3749 end if;
3750 when others then
3751 p_status := null;
3752
3753 END chk_review_submit_val;
3754
3755
3756 --
3757 --
3758 Function get_which_rates (p_copy_entity_txn_id in Number) Return Varchar2 is
3759 cursor c1 is
3760 select 1 from dual where exists (
3761 select 1
3762 FROM BEN_COPY_ENTITY_RESULTS
3763 WHERE NVL(INFORMATION104,'PPP') NOT IN ('UNLINK')
3764 AND TABLE_ALIAS ='COP'
3765 AND copy_entity_txn_id = p_copy_entity_txn_id
3766 AND result_type_cd = 'DISPLAY'
3767 AND gs_parent_entity_result_id is not null);
3768
3769 cnt number;
3770 l_use_point_or_step varchar2(20);
3771 l_copy_entity_txn_id number;
3772 l_get_which_rates varchar2(20);
3773 l_proc varchar2(72) := g_package||'.get_which_rates';
3774 begin
3775 l_use_point_or_step := use_point_or_step(p_copy_entity_txn_id);
3776 open c1;
3777 fetch c1 into cnt;
3778 if (c1%NOTFOUND) then
3779 l_get_which_rates := 'GRADE';
3780 else
3781 l_get_which_rates := l_use_point_or_step;
3782 end if;
3783 close c1;
3784 return l_get_which_rates;
3785 EXCEPTION
3786 WHEN others THEN
3787 if g_debug then
3788 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3789 end if;
3790 return l_get_which_rates;
3791 end get_which_rates;
3792
3793 Function get_rates_icon_enabled (p_copy_entity_txn_id in Number,
3794 p_copy_entity_result_id in Number,
3795 p_rate_hgrid_node in varchar2)
3796 Return Varchar2 is
3797 l_get_which_rates varchar2(20) := get_which_rates(p_copy_entity_txn_id);
3798 l_icon varchar2(20) := 'NONE';
3799 cnt number := 0;
3800 cursor c1 is
3801 select 1 from dual where exists (
3802 select 1 from ben_copy_entity_results
3803 where copy_entity_txn_id = p_copy_entity_txn_id
3804 and gs_parent_entity_result_id = p_copy_entity_result_id);
3805 l_proc varchar2(72) := g_package||'.get_rates_icon_enabled';
3806 begin
3807 if g_debug then
3808 hr_utility.set_location('Entering '||l_proc, 5);
3809 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id, 10);
3810 hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id, 15);
3811 hr_utility.set_location('p_rate_hgrid_node '||p_rate_hgrid_node, 20);
3812 hr_utility.set_location('l_get_which_rates :'||l_get_which_rates, 25);
3813 end if;
3814
3815 if (p_rate_hgrid_node = 'PGM') then
3816 if (l_get_which_rates = 'POINT') then
3817 l_icon := 'NONE';
3818 else
3819 open c1;
3820 fetch c1 into cnt;
3821 if (c1%NOTFOUND) then
3822 l_icon := 'DISABLED';
3823 else
3824 l_icon := 'ENABLED';
3825 end if;
3826 close c1;
3827 end if;
3828 elsif (p_rate_hgrid_node = 'CPP') then
3829 if (l_get_which_rates = 'POINT') then
3830
3831 open c1;
3832 fetch c1 into cnt;
3833 if (c1%NOTFOUND) then
3834 l_icon := 'DISABLED';
3835 else
3836 l_icon := 'ENABLED';
3837 end if;
3838 close c1;
3839 else
3840 l_icon := 'NONE';
3841 end if;
3842 else
3843 l_icon := 'NONE';
3844 end if;
3845
3846 if g_debug then
3847 hr_utility.set_location('Successfull completion '||l_proc, 5);
3848 end if;
3849 return l_icon;
3850
3851 EXCEPTION WHEN others THEN
3852 if g_debug then
3853 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
3854 end if;
3855 end get_rates_icon_enabled;
3856 --
3857 -- Function to return the annualization factor for frequency codes used in Benefits.
3858 --
3859 Function pgm_freq_annual_factor
3860 (p_ref_perd_cd in varchar2) return number is
3861 --
3862 -- Local variable declaration
3863 --
3864 l_ret_cd NUMBER;
3865 --
3866 BEGIN
3867 --
3868 IF p_ref_perd_cd = 'PWK' THEN
3869 l_ret_cd := 52;
3870 ELSIF p_ref_perd_cd = 'BWK' THEN
3871 l_ret_cd := 26;
3872 ELSIF p_ref_perd_cd = 'SMO' THEN
3873 l_ret_cd := 24;
3874 ELSIF p_ref_perd_cd = 'PQU' THEN
3875 l_ret_cd := 4;
3876 ELSIF p_ref_perd_cd = 'PYR' THEN
3877 l_ret_cd := 1;
3878 ELSIF p_ref_perd_cd = 'SAN' THEN
3879 l_ret_cd := 2;
3880 ELSIF p_ref_perd_cd = 'MO' THEN
3881 l_ret_cd := 12;
3882 ELSIF p_ref_perd_cd = 'NOVAL' THEN
3883 l_ret_cd := 1;
3884 ELSIF p_ref_perd_cd = 'PHR' then
3885 --
3886 l_ret_cd := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
3887 if l_ret_cd is null then
3888 l_ret_cd := 2080;
3889 end if;
3890 --
3891 ELSE
3892 l_ret_cd := 1;
3893 END IF;
3894 --
3895 RETURN l_ret_cd;
3896 END pgm_freq_annual_factor;
3897 --
3898 -- Create Pay Rate for a Pay Scale
3899 --
3900 Procedure create_pay_rate (p_business_group_id in number,
3901 p_scale_id in number,
3902 p_rate_name in varchar2,
3903 p_rate_id Out nocopy number,
3904 p_ovn Out nocopy number)
3905 is
3906 --
3907 -- Local variables
3908 --
3909 l_rate_id pay_rates.rate_id%type;
3910 l_ovn pay_rates.object_version_number%type;
3911 l_proc varchar2(100) := 'pqh_gsp_utility.create_pay_rate';
3912 --
3913 Begin
3914 --
3915 if g_debug then
3916 hr_utility.set_location('Entering :'||l_proc, 5);
3917 end if;
3918
3919 hr_rate_api.create_rate
3920 (p_validate => false
3921 ,p_effective_date => trunc(sysdate)
3922 ,p_business_group_id => p_business_group_id
3923 ,p_name => p_rate_name
3924 ,p_parent_spine_id => p_scale_id
3925 ,p_rate_type => 'SP'
3926 ,p_rate_uom => 'M'
3927 ,p_object_version_number => l_ovn
3928 ,p_rate_id => l_rate_id);
3929 --
3930 --
3931 p_rate_id := l_rate_id;
3932 p_ovn := l_ovn;
3933 --
3934 if g_debug then
3935 hr_utility.set_location('Leaving :'||l_proc, 5);
3936 end if;
3937 --
3938 End;
3939 --
3940 -- Create Pay Rate for Grade Rates
3941 --
3942 Procedure create_pay_rate(p_business_group_id in number,
3943 p_ldr_period_code in varchar2,
3944 p_rate_id Out nocopy number,
3945 p_ovn Out nocopy number)
3946 is
3947 --
3948 -- Local variables
3949 --
3950 l_rate_id pay_rates.rate_id%type;
3951 l_ovn pay_rates.object_version_number%type;
3952 l_proc varchar2(100) := 'pqh_gsp_utility.create_pay_rate';
3953 --
3954 Begin
3955 --
3956 if g_debug then
3957 hr_utility.set_location('Entering :'||l_proc, 5);
3958 end if;
3959
3960 hr_rate_api.create_rate
3961 (p_validate => false
3962 ,p_effective_date => trunc(sysdate)
3963 ,p_business_group_id => p_business_group_id
3964 ,p_name => hr_general.decode_lookup('PQH_GSP_GEN_PAY_RATE_NAME',p_ldr_period_code)
3965 ,p_rate_type => 'G'
3966 ,p_rate_uom => 'M'
3967 ,p_object_version_number => l_ovn
3968 ,p_rate_id => l_rate_id);
3969 --
3970 --
3971 p_rate_id := l_rate_id;
3972 p_ovn := l_ovn;
3973 --
3974 if g_debug then
3975 hr_utility.set_location('Leaving :'||l_proc, 5);
3976 end if;
3977 --
3978 End;
3979 --
3980 procedure step_exists_for_point(p_copy_entity_txn_id in number,
3981 p_points_result_id in number,
3982 p_status out nocopy varchar)
3983 is
3984 cursor csr_steps_for_point
3985 is
3986 select null
3987 from ben_copy_entity_results
3988 where copy_entity_txn_id = p_copy_entity_txn_id
3989 and information262 = p_points_result_id
3990 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
3991 and information104 = 'LINK';
3992 l_steps_for_point NUMBER;
3993 begin
3994 open csr_steps_for_point;
3995 fetch csr_steps_for_point into l_steps_for_point;
3996 if(csr_steps_for_point%NOTFOUND) then
3997 p_status := 'N';
3998 else
3999 p_status := 'Y';
4000 end if;
4001 close csr_steps_for_point;
4002 end step_exists_for_point;
4003 --
4004 procedure chk_scale_name(p_copy_entity_txn_id in number,
4005 p_business_group_id in number,
4006 p_copy_entity_result_id in number,
4007 p_parent_spine_id in number,
4008 p_name in varchar,
4009 p_status out nocopy varchar)
4010 is
4011 cursor csr_scale_name
4012 is
4013 select null
4014 from ben_copy_entity_results
4015 where copy_entity_txn_id = p_copy_entity_txn_id
4016 and copy_entity_result_id <> p_copy_entity_result_id
4017 and information98 = p_name
4018 and table_alias = 'SCALE'
4019 union
4020 select null
4021 from per_parent_spines
4022 where name = p_name
4023 and business_group_id = p_business_group_id
4024 and parent_spine_id <> p_parent_spine_id;
4025
4026 Cursor plip_details
4027 is
4028 select information98,copy_entity_result_id,dml_operation
4029 from ben_copy_entity_results
4030 where copy_entity_txn_id = p_copy_entity_txn_id
4031 and table_alias = 'CPP'
4032 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4033 and information258 = p_copy_entity_result_id;
4034 l_scale_name varchar2(30);
4035 begin
4036 open csr_scale_name;
4037 fetch csr_scale_name into l_scale_name;
4038 if(csr_scale_name%NOTFOUND) then
4039 p_status := 'Y';
4040 for i in plip_details loop
4041 if i.information98<>p_name THEN
4042 update ben_copy_entity_results
4043 set information98 = p_name,
4044 dml_operation = get_dml_operation(i.dml_operation)
4045 where copy_entity_result_id = i.copy_entity_result_id;
4046
4047 END IF;
4048 end loop;
4049 else
4050 p_status :='E';
4051 end if;
4052 close csr_scale_name;
4053 end chk_scale_name;
4054 --
4055 -----IS_CRRATE_THERE_ICON--------------------
4056 Function is_crrate_there_icon
4057 (p_copy_entity_txn_id in Number,
4058 p_copy_entity_result_id in Number,
4059 p_effective_date_in in date,
4060 p_rate_hgrid_node in varchar2)
4061 Return Varchar2 is
4062 l_proc varchar2(72) := g_package||'.is_crrate_there_icon';
4063 l_get_which_rates varchar2(20) := get_which_rates(p_copy_entity_txn_id);
4064 l_crrate_icon varchar2(50);
4065 l_effective_date date;
4066 begin
4067 select action_date into l_effective_date from pqh_copy_entity_txns
4068 where copy_entity_txn_id = p_copy_entity_txn_id;
4069 if g_debug then
4070 hr_utility.set_location('Entering '||l_proc,5);
4071 end if;
4072 if ((p_rate_hgrid_node = 'CPP') and (l_get_which_rates = 'GRADE')) then
4073 l_crrate_icon := pqh_gsp_hr_to_stage.is_crrate_there(p_plip_cer_id => p_copy_entity_result_id,
4074 p_copy_entity_txn_id => p_copy_entity_txn_id,
4075 p_effective_date => l_effective_date);
4076 elsif ((p_rate_hgrid_node = 'COP') and ((l_get_which_rates = 'POINT') or (l_get_which_rates = 'STEP'))) then
4077 l_crrate_icon := pqh_gsp_hr_to_stage.is_crrate_there(p_oipl_cer_id => p_copy_entity_result_id,
4078 p_copy_entity_txn_id => p_copy_entity_txn_id,
4079 p_effective_date => l_effective_date);
4080 else
4081 l_crrate_icon := 'XYZ';
4082 end if;
4083
4084 if g_debug then
4085 hr_utility.set_location('Successfull Completion '||l_proc,5);
4086 end if;
4087 return l_crrate_icon;
4088
4089 EXCEPTION WHEN others THEN
4090 if g_debug then
4091 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
4092 end if;
4093 raise;
4094 end;
4095
4096
4097 ------------------------------------------------------
4098
4099 Function PGM_TO_BASIS_CONVERSION
4100 (P_Pgm_ID IN Number
4101 ,P_EFFECTIVE_DATE IN Date
4102 ,P_AMOUNT IN Number
4103 ,P_ASSIGNMENT_ID IN Number)
4104
4105 Return Number Is
4106
4107 CURSOR C_Pgm_rt_Perd IS
4108 Select Pgm.ACTY_REF_PERD_CD,
4109 Nvl(Cur.Precision,2),
4110 Update_Salary_Cd
4111 From Ben_Pgm_f Pgm,
4112 Fnd_Currencies Cur
4113 Where Pgm.Pgm_id = P_Pgm_Id
4114 and P_Effective_Date
4115 Between Pgm.Effective_Start_Date
4116 and Pgm.Effective_End_Date
4117 and Cur.Currency_Code(+) = Pgm.Pgm_Uom;
4118
4119 CURSOR C_Pay_Basis IS
4120 select Ppb.Pay_Annualization_Factor,Ppb.Pay_basis
4121 From Per_All_Assignments_f asg,
4122 Per_Pay_Bases ppb
4123 where Asg.Assignment_id = P_Assignment_Id
4124 and P_effective_date
4125 between Asg.Effective_Start_Date
4126 and Asg.Effective_End_Date
4127 and Ppb.Pay_Basis_Id = Asg.Pay_Basis_Id;
4128
4129 l_ref_perd_cd Ben_Pgm_F.ACTY_REF_PERD_CD%TYPE;
4130 l_precision Fnd_Currencies.Precision%TYPE;
4131 l_factor Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
4132 l_ret_amount Number;
4133 l_pay_annualization_factor Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
4134 l_update_Salary_cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
4135 L_Payroll_name pay_all_payrolls_f.Payroll_name%TYPE;
4136 l_pay_basis Per_Pay_Bases.Pay_basis%TYPE;
4137 l_gl_ann_factor ben_pgm_extra_info.pgi_information5%TYPE;
4138
4139 Begin
4140
4141 if g_debug then
4142 hr_utility.set_location('Entering PGM_TO_BASIS_CONVERSION', 5);
4143 hr_utility.set_location(' Parameters passed are as follows: ',6);
4144 hr_utility.set_location('P_Pgm_ID '||P_Pgm_ID ,6);
4145 hr_utility.set_location('P_EFFECTIVE_DATE '||P_EFFECTIVE_DATE ,6);
4146 hr_utility.set_location('P_AMOUNT '||P_AMOUNT ,6);
4147 hr_utility.set_location('P_ASSIGNMENT_ID '||P_ASSIGNMENT_ID ,6);
4148 end if;
4149
4150 OPEN C_Pgm_rt_Perd;
4151 FETCH C_Pgm_rt_Perd into l_ref_perd_cd,l_precision, l_Update_Salary_cd;
4152 IF C_Pgm_rt_Perd%NOTFOUND THEN
4153 l_ref_perd_cd := 'NOVAL';
4154 END IF;
4155 CLOSE C_Pgm_rt_Perd;
4156 --
4157
4158 --
4159 If L_Update_Salary_Cd = 'SALARY_BASIS' Then
4160
4161 OPEN c_pay_basis;
4162 FETCH c_pay_basis into l_factor,l_pay_basis;
4163 CLOSE C_Pay_Basis;
4164
4165 l_gl_ann_factor := pqh_gsp_utility.get_gl_ann_factor(p_pgm_id => p_pgm_id);
4166
4167 if l_gl_ann_factor is not null then
4168 l_ret_amount := (p_amount*to_number(l_gl_ann_factor))/l_factor;
4169 return l_ret_amount;
4170 end if;
4171
4172
4173 /* To fix bug 4907433
4174 * If the Grade Ladder frequency and the Salary Basis frequency are matching then
4175 * Return the amount without any calculations
4176 */
4177
4178 IF (l_pay_basis = 'MONTHLY' AND l_ref_perd_cd = 'MO')
4179 OR (l_pay_basis = 'HOURLY' AND l_ref_perd_cd = 'PHR')
4180 OR (l_pay_basis = 'ANNUAL' AND l_ref_perd_cd = 'PYR') THEN
4181
4182 if g_debug then
4183 hr_utility.set_location('Salary basis frequency and Grade Ladder frequency matches', 15);
4184 hr_utility.set_location('So returning the Grade/Step rate as it is', 25);
4185 end if;
4186
4187 -- RETURN trunc(p_amount,l_precision); -- Bug 6608606
4188 RETURN p_amount;
4189
4190 END IF;
4191
4192 -- Bug Fix 4907433 ends
4193
4194 Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
4195
4196 per_pay_proposals_populate.get_payroll(P_Assignment_Id
4197 ,P_Effective_Date
4198 ,l_Payroll_name
4199 ,l_factor);
4200 Else
4201 Return P_Amount;
4202 End If;
4203
4204 IF l_factor is null or l_factor=0 THEN
4205 l_factor := 1;
4206 END IF;
4207
4208 IF l_ref_perd_cd = 'PWK' THEN
4209 l_ret_amount := (p_amount*52)/l_factor;
4210 ELSIF l_ref_perd_cd = 'BWK' THEN
4211 l_ret_amount := (p_amount*26)/l_factor;
4212 ELSIF l_ref_perd_cd = 'SMO' THEN
4213 l_ret_amount := (p_amount*24)/l_factor;
4214 ELSIF l_ref_perd_cd = 'PQU' THEN
4215 l_ret_amount := (p_amount*4)/l_factor;
4216 ELSIF l_ref_perd_cd = 'PYR' THEN
4217 l_ret_amount := (p_amount*1)/l_factor;
4218 ELSIF l_ref_perd_cd = 'SAN' THEN
4219 l_ret_amount := (p_amount*2)/l_factor;
4220 ELSIF l_ref_perd_cd = 'MO' THEN
4221 l_ret_amount := (p_amount*12)/l_factor;
4222 ELSIF l_ref_perd_cd = 'NOVAL' THEN
4223 l_ret_amount := (p_amount*1)/l_factor;
4224 ELSIF l_ref_perd_cd = 'PHR' then
4225
4226 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
4227 If l_pay_annualization_factor is null then
4228 l_pay_annualization_factor := 2080;
4229 End if;
4230 --
4231 l_ret_amount := (p_amount * l_pay_annualization_factor)/l_factor;
4232 --
4233 ELSE
4234 l_ret_amount := (p_amount*1)/l_factor;
4235 END IF;
4236 --
4237 -- RETURN trunc(l_ret_amount,l_precision); -- Bug 6608606
4238 RETURN l_ret_amount;
4239
4240 End PGM_TO_BASIS_CONVERSION;
4241
4242 ----------------------------------------------------------------------------------------------------
4243
4244 Function get_num_steps_in_grade(p_copy_entity_txn_id in number,
4245 p_grade_cer_id in number)
4246 Return Number is
4247
4248 cursor csr_steps_in_grade
4249 is
4250 select count(*) cnt
4251 from ben_copy_entity_results
4252 where table_alias = 'COP'
4253 and copy_entity_txn_id = p_copy_entity_txn_id
4254 and gs_parent_entity_result_id in
4255 (select gs_mirror_src_entity_result_id
4256 from ben_copy_entity_results
4257 where table_alias = 'PLN' and copy_entity_txn_id = p_copy_entity_txn_id
4258 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY' and copy_entity_result_id = p_grade_cer_id)
4259 And result_type_cd = 'DISPLAY'
4260 And Nvl(Information104,'PPP') <> 'UNLINK';
4261
4262 l_proc varchar2(72) := g_package||'.get_num_steps_in_grade';
4263
4264 step_cnt number;
4265 begin
4266 if g_debug then
4267 hr_utility.set_location('Entering '||l_proc, 5);
4268 end if;
4269 for steps in csr_steps_in_grade loop
4270 step_cnt := steps.cnt;
4271 end loop;
4272 if g_debug then
4273 hr_utility.set_location('Successfull completion '||l_proc, 5);
4274 end if;
4275 return step_cnt;
4276 EXCEPTION WHEN others THEN
4277 if g_debug then
4278 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
4279 end if;
4280 end get_num_steps_in_grade;
4281
4282 Function get_dflt_point_rate (p_copy_entity_txn_id in number,
4283 p_point_cer_id in number,
4284 p_effective_date in date)
4285 RETURN NUMBER
4286 is
4287 --
4288 -- Local variables
4289 --
4290 Cursor csr_dflt_point_rate is
4291 Select information297
4292 From ben_copy_entity_results
4293 Where copy_entity_txn_id = p_copy_entity_txn_id
4294 and INFORMATION278 = p_point_cer_id
4295 and p_effective_date between information2 and information3
4296 and table_alias = 'HRRATE'
4297 and result_type_cd = 'DISPLAY';
4298 --
4299 l_point_rate number;
4300 l_proc varchar2(100) := 'pqh_gsp_utility.get_dflt_point_rate';
4301 --
4302 Begin
4303 --
4304 if g_debug then
4305 hr_utility.set_location('Entering :'||l_proc, 5);
4306 end if;
4307 --
4308 l_point_rate := 0;
4309
4310 Open csr_dflt_point_rate;
4311 Fetch csr_dflt_point_rate into l_point_rate;
4312 Close csr_dflt_point_rate;
4313 --
4314 if g_debug then
4315 hr_utility.set_location('Leaving :'||l_proc, 5);
4316 end if;
4317 --
4318 Return l_point_rate;
4319 --
4320 End;
4321
4322 --
4323
4324 -- Added by vevenkat for Approval UI ----
4325 --
4326 --
4327
4328 Function Get_person_name (P_Person_id IN Number,
4329 P_Effective_Date IN Date)
4330 Return varchar2 is
4331
4332 Cursor Csr_Person is
4333 Select Full_name
4334 From Per_All_people_F
4335 Where Person_id = P_Person_id
4336 and Nvl(P_Effective_Date, Sysdate)
4337 Between Effective_Start_Date
4338 and Effective_End_Date;
4339
4340 L_Person_name per_All_people_F.Full_name%TYPE;
4341 Begin
4342
4343 If P_Person_id is NOT NULL then
4344
4345 Open Csr_Person;
4346 Fetch Csr_Person into L_Person_name;
4347 Close Csr_Person;
4348
4349 End If;
4350
4351 Return l_Person_Name;
4352 End Get_Person_Name;
4353
4354 Function Get_Assgt_Status (P_Assgt_Status_Id IN Number)
4355 Return varchar2 Is
4356
4357 Cursor Csr_Assgt_status is
4358 Select User_Status
4359 From PER_ASSIGNMENT_STATUS_TYPES_TL
4360 where Assignment_Status_Type_Id = P_Assgt_Status_Id
4361 and language = userenv('LANG');
4362
4363 L_User_Status PER_ASSIGNMENT_STATUS_TYPES_TL.User_Status%TYPE;
4364 begin
4365
4366 If P_Assgt_Status_Id is NOT NULL then
4367 Open Csr_Assgt_Status;
4368 Fetch Csr_Assgt_Status into l_User_Status;
4369 Close Csr_Assgt_Status;
4370 End If;
4371
4372 Return L_USer_Status;
4373 End Get_Assgt_Status;
4374 -------------------------------------------------------------------
4375 Procedure check_sal_basis_iv (p_input_value_id in number,
4376 p_basis_id in number,
4377 p_business_group_id in number,
4378 p_exists_flag Out nocopy varchar2)
4379 is
4380 --
4381 -- Local variables
4382 --
4383 Cursor csr_sal_basis_iv is
4384 Select 'x'
4385 From per_pay_bases
4386 where business_group_id = p_business_group_id
4387 and rate_id is null
4388 and input_value_id = p_input_value_id
4389 and pay_basis_id <> p_basis_id;
4390 --
4391 l_dummy varchar2(20);
4392 l_proc varchar2(100) := 'pqh_gsp_utility.check_sal_basis_iv';
4393 --
4394 Begin
4395 --
4396 p_exists_flag := 'N';
4397 if g_debug then
4398 hr_utility.set_location('Entering :'||l_proc, 5);
4399 end if;
4400 --
4401 Open csr_sal_basis_iv;
4402 Fetch csr_sal_basis_iv into l_dummy ;
4403 If csr_sal_basis_iv%found then
4404 p_exists_flag := 'Y';
4405 End if;
4406 Close csr_sal_basis_iv;
4407 --
4408 if g_debug then
4409 hr_utility.set_location('Leaving :'||l_proc, 5);
4410 end if;
4411 --
4412 End;
4413
4414 -------------------------------------------------------------------
4415 procedure update_oipl_records(
4416 p_effective_date IN DATE,
4417 p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
4418 p_point_name IN ben_copy_entity_results.information99%TYPE,
4419 p_sequence IN ben_copy_entity_results.information263%TYPE,
4420 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE
4421 )
4422 IS
4423
4424
4425 cursor csr_oipl_records
4426 is
4427 select copy_entity_result_id,object_version_number,dml_operation
4428 from ben_copy_entity_results
4429 where table_alias = 'COP'
4430 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4431 and information262 = p_copy_entity_result_id
4432 and copy_entity_txn_id = p_copy_entity_txn_id;
4433 r_oipl_record csr_oipl_records%ROWTYPE;
4434 l_ovn NUMBER;
4435 begin
4436 for r_oipl_record in csr_oipl_records loop
4437
4438 l_ovn := r_oipl_record.object_version_number;
4439 ben_copy_entity_results_api.update_copy_entity_results(
4440 p_effective_date => p_effective_date,
4441 p_copy_entity_result_id => r_oipl_record.copy_entity_result_id,
4442 p_information99 => p_point_name,
4443 p_information263 => p_sequence,
4444 p_information323 => null,
4445 p_dml_operation =>get_dml_operation(r_oipl_record.dml_operation),
4446 p_object_version_number => l_ovn
4447 );
4448 end loop;
4449 end;
4450 --
4451 --
4452 ---------------------------move_data_stage_to_hr-----------------------------
4453 --
4454 Procedure move_data_stage_to_hr
4455 (p_copy_entity_txn_id in Number,
4456 p_effective_date in Date,
4457 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
4458 p_business_group_id in Number,
4459 p_datetrack_mode in Varchar2,
4460 p_error_msg out Nocopy Varchar2
4461 ) IS
4462
4463 l_proc varchar2(72) := g_package||'move_data_stage_to_hr';
4464 l_msg varchar2(2000):= null;
4465
4466
4467 Begin
4468 --
4469 -- Issue Savepoint
4470 savepoint move_data_stage_to_hr;
4471 --
4472 --
4473 -- Initialise Multiple Message Detection
4474 --
4475 hr_multi_message.enable_message_list;
4476
4477 if g_debug then
4478 hr_utility.set_location('Entering '||l_proc,100);
4479 hr_utility.set_location('p_copy_entity_txn_id :'||p_copy_entity_txn_id,20);
4480 hr_utility.set_location('p_effective_date :'||p_effective_date ,30);
4481 hr_utility.set_location('p_business_group_id :'||p_business_group_id ,40);
4482 hr_utility.set_location('p_datetrack_mode :'||p_datetrack_mode,50);
4483 end if;
4484
4485 pqh_gsp_stage_to_hr.gsp_data_push(p_copy_entity_txn_id => p_copy_entity_txn_id,
4486 p_effective_date => p_effective_date,
4487 p_business_group_id => p_business_group_id,
4488 p_business_area => p_business_area,
4489 p_datetrack_mode => p_datetrack_mode);
4490 if g_debug then
4491 hr_utility.set_location('Leaving '||l_proc,100);
4492 end if;
4493
4494 --
4495 --
4496 -- Call to raise any errors on multi-message list
4497 hr_multi_message.end_validation_set;
4498 -- p_error_msg := l_msg;
4499
4500 EXCEPTION
4501 when hr_api.validate_enabled then
4502 Rollback to move_data_stage_to_hr;
4503 null;
4504 when hr_multi_message.error_message_exist then
4505 Rollback to move_data_stage_to_hr;
4506 null;
4507 when others then
4508 l_msg := nvl(fnd_message.get,sqlerrm);
4509 p_error_msg := l_msg;
4510 Rollback to move_data_stage_to_hr;
4511 End move_data_stage_to_hr;
4512
4513 --
4514 ------------------ get_grade_name ----------------------------
4515 --
4516 --
4517
4518 PROCEDURE GET_GRADE_NAME (
4519 p_grade_definition_id IN NUMBER,
4520 p_business_group_id IN NUMBER,
4521 p_concatenated_segments OUT NOCOPY varchar2)
4522 IS
4523 CURSOR csr_segments
4524 IS
4525 select segment1,
4526 segment2,
4527 segment3,
4528 segment4,
4529 segment5,
4530 segment6,
4531 segment7,
4532 segment8,
4533 segment9,
4534 segment10,
4535 segment11,
4536 segment12,
4537 segment13,
4538 segment14,
4539 segment15,
4540 segment16,
4541 segment17,
4542 segment18,
4543 segment19,
4544 segment20,
4545 segment21,
4546 segment22,
4547 segment23,
4548 segment24,
4549 segment25,
4550 segment26,
4551 segment27,
4552 segment28,
4553 segment29,
4554 segment30
4555 from per_grade_definitions
4556 where grade_definition_id = p_grade_definition_id;
4557 segments csr_segments%ROWTYPE;
4558 l_ccid number;
4559 l_flex_num number;
4560
4561 BEGIN
4562
4563 open csr_segments;
4564 fetch csr_segments INTO segments;
4565 close csr_segments;
4566
4567 select grade_structure into l_flex_num
4568 from per_business_groups_perf
4569 where business_group_id =p_business_group_id;
4570
4571 hr_kflex_utility.ins_or_sel_keyflex_comb
4572 (p_appl_short_name => 'PER'
4573 ,p_flex_code => 'GRD'
4574 ,p_flex_num => l_flex_num
4575 ,p_segment1 =>segments.segment1
4576 ,p_segment2 =>segments.segment2
4577 ,p_segment3 =>segments.segment3
4578 ,p_segment4 =>segments.segment4
4579 ,p_segment5 =>segments.segment5
4580 ,p_segment6 =>segments.segment6
4581 ,p_segment7 =>segments.segment7
4582 ,p_segment8 =>segments.segment8
4583 ,p_segment9 =>segments.segment9
4584 ,p_segment10 =>segments.segment10
4585 ,p_segment11 =>segments.segment11
4586 ,p_segment12 =>segments.segment12
4587 ,p_segment13 =>segments.segment13
4588 ,p_segment14 =>segments.segment14
4589 ,p_segment15 =>segments.segment15
4590 ,p_segment16 =>segments.segment16
4591 ,p_segment17 =>segments.segment17
4592 ,p_segment18 =>segments.segment18
4593 ,p_segment19 =>segments.segment19
4594 ,p_segment20 =>segments.segment20
4595 ,p_segment21 =>segments.segment21
4596 ,p_segment22 =>segments.segment22
4597 ,p_segment23 =>segments.segment23
4598 ,p_segment24 =>segments.segment24
4599 ,p_segment25 =>segments.segment25
4600 ,p_segment26 =>segments.segment26
4601 ,p_segment27 =>segments.segment27
4602 ,p_segment28 =>segments.segment28
4603 ,p_segment29 =>segments.segment29
4604 ,p_segment30 =>segments.segment30
4605 ,p_ccid => l_ccid
4606 ,p_concat_segments_out => p_concatenated_segments
4607 );
4608 hr_utility.set_location('cccid:'||l_ccid, 5);
4609 hr_utility.set_location('concatened segments:'||p_concatenated_segments,15);
4610 END GET_GRADE_NAME;
4611 ---
4612 --------- get_dml_operation -----------
4613 --
4614
4615 FUNCTION GET_DML_OPERATION
4616 (p_in_dml_operation IN ben_copy_entity_results.dml_operation%TYPE)
4617 return VARCHAR2
4618 is
4619 p_out_dml_operation ben_copy_entity_results.dml_operation%TYPE;
4620
4621 begin
4622 IF p_in_dml_operation = 'REUSE' THEN
4623 p_out_dml_operation := 'UPDATE';
4624 ELSIF p_in_dml_operation = 'COPIED' THEN
4625 p_out_dml_operation := 'UPD_INS';
4626 ELSE
4627 p_out_dml_operation := p_in_dml_operation;
4628 END IF;
4629
4630
4631 RETURN p_out_dml_operation;
4632 END get_dml_operation;
4633 --------------------------------------------------------------------------------
4634 Procedure chk_no_asg_grd_ldr(p_asg_grade_ladder_id in number,
4635 p_asg_grade_id in number,
4636 p_asg_org_id in number,
4637 p_asg_bg_id in number,
4638 p_effective_date in date) IS
4639 --
4640 -- Declare cursor
4641 --
4642 cursor csr_chk_gsp_in_system is
4643 select 'x'
4644 from ben_pgm_f pgm
4645 where pgm.business_group_id = p_asg_bg_id
4646 and pgm.pgm_typ_cd = 'GSP'
4647 and p_effective_date
4648 between pgm.effective_start_date
4649 and nvl(pgm.effective_end_date,to_date('31/12/4712','dd/mm/RRRR'));
4650 --
4651 l_exists varchar2(10);
4652 --
4653 Begin
4654 --
4655 -- if grade_id is enterd and grade_ladder is not entered,
4656 -- need to check gsp is implemented in the system.
4657 -- if the gsp is implemented, a warning message will be appear.
4658 --
4659 hr_utility.set_location('Entering chk_no_asg_grd_ldr', 5);
4660 --
4661 if p_asg_grade_id is not null and p_asg_grade_ladder_id is null then
4662 open csr_chk_gsp_in_system;
4663 fetch csr_chk_gsp_in_system into l_exists;
4664 if csr_chk_gsp_in_system%FOUND then
4665 close csr_chk_gsp_in_system;
4666 --
4667 -- This message is cofigurable(BUG3219215)
4668 --
4669 pqh_utility.set_message(800,'HR_289559_GRADE_LADDER_REQUIRE',p_asg_org_id);
4670 pqh_utility.raise_error;
4671 else
4672 --
4673 hr_utility.set_location('GSP not Implemented', 7);
4674 --
4675 close csr_chk_gsp_in_system;
4676 end if;
4677
4678 end if;
4679 --
4680 --
4681 hr_utility.set_location('Leaving chk_no_asg_grd_ldr', 10);
4682 --
4683 End chk_no_asg_grd_ldr;
4684
4685 FUNCTION bus_area_pgm_entity_exist(p_bus_area_cd IN Varchar2,
4686 P_pgm_id IN NUMBER)
4687 RETURN varchar2
4688 IS
4689 l_return Varchar2(50);
4690 BEGIN
4691 l_return := pqh_corps_utility.bus_area_pgm_entity_exist( p_bus_area_cd => p_bus_area_cd
4692 ,P_pgm_id => P_pgm_id );
4693 RETURN l_return;
4694 Return NULL;
4695 END bus_area_pgm_entity_exist;
4696 -----------------------------------------
4697 ---------- < chk_new_ceiling >-----------
4698 --- < ggnanagu >-------------------------
4699 PROCEDURE chk_new_ceiling (
4700 p_effective_date IN DATE,
4701 p_grade_cer_id IN NUMBER,
4702 p_new_ceiling IN NUMBER
4703 )
4704 IS
4705 /*
4706 p_new_ceiling is the new ceiling sequence.
4707 Throw error if there are placements above the New Ceiling sequence.
4708 */
4709 l_gspine_id NUMBER;
4710 l_grade_id NUMBER;
4711
4712 CURSOR csr_grade_id
4713 IS
4714 SELECT information253
4715 FROM ben_copy_entity_results
4716 WHERE copy_entity_result_id = p_grade_cer_id
4717 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY';
4718
4719 CURSOR csr_gspine_id (p_grade_id IN NUMBER)
4720 IS
4721 SELECT grade_spine_id
4722 FROM per_grade_spines_f
4723 WHERE p_effective_date BETWEEN effective_start_date
4724 AND effective_end_date
4725 AND grade_id = p_grade_id;
4726 BEGIN
4727 hr_multi_message.enable_message_list;
4728 hr_utility.set_location ('Inside chk_new_ceiling', 10);
4729 OPEN csr_grade_id;
4730 FETCH csr_grade_id INTO l_grade_id;
4731 CLOSE csr_grade_id;
4732 hr_utility.set_location ('Grade Id is :' || l_grade_id, 10);
4733
4734 IF l_grade_id IS NOT NULL
4735 THEN
4736 hr_utility.set_location ('grade_id found ', 20);
4737 OPEN csr_gspine_id (l_grade_id);
4738 FETCH csr_gspine_id INTO l_gspine_id;
4739
4740 IF csr_gspine_id%FOUND
4741 THEN
4742 hr_utility.set_location ('gspine_id found ' || l_gspine_id, 20);
4743 per_grade_spines_pkg.chk_low_ceiling (
4744 p_val_start => p_effective_date,
4745 p_val_end => p_effective_date,
4746 p_gspine_id => l_gspine_id,
4747 p_new_ceil => p_new_ceiling
4748 );
4749 END IF;
4750 END IF;
4751
4752 -- Call to raise any errors on multi-message list
4753 hr_multi_message.end_validation_set;
4754 -- p_error_msg := l_msg;
4755 hr_utility.set_location ('Leaving chk_new_ceiling', 90);
4756 EXCEPTION
4757 WHEN OTHERS
4758 THEN
4759 fnd_msg_pub.add;
4760 END;
4761
4762
4763 PROCEDURE unlink_step_or_point (p_copy_entity_result_id IN NUMBER)
4764 IS
4765 BEGIN
4766 UPDATE ben_copy_entity_results
4767 SET information104 = 'UNLINK'
4768 WHERE copy_entity_result_id = p_copy_entity_result_id;
4769 END unlink_step_or_point;
4770 --------------------------------------------------------------------------
4771 PROCEDURE chk_delete_option (
4772 p_copy_entity_txn_id IN NUMBER,
4773 p_opt_cer_id IN NUMBER,
4774 p_point_id IN NUMBER,
4775 p_opt_id IN NUMBER,
4776 p_pspine_id IN NUMBER,
4777 p_effective_date IN DATE
4778 )
4779 IS
4780 CURSOR csr_steps_for_point
4781 IS
4782 SELECT NULL
4783 FROM ben_copy_entity_results
4784 WHERE copy_entity_txn_id = p_copy_entity_txn_id
4785 AND information262 = p_opt_cer_id
4786 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
4787 AND information104 = 'LINK';
4788
4789 l_steps_for_point NUMBER;
4790 BEGIN
4791 hr_multi_message.enable_message_list;
4792 hr_utility.set_location ('Inside Proc', 2);
4793 OPEN csr_steps_for_point;
4794 FETCH csr_steps_for_point INTO l_steps_for_point;
4795
4796 IF (csr_steps_for_point%FOUND)
4797 THEN
4798 hr_utility.set_location ('Steps Error', 5);
4799 hr_utility.set_message (8302,'PER_7926_DEL_POINT_STEP');
4800 hr_utility.raise_error;
4801 END IF;
4802
4803 hr_multi_message.end_validation_set;
4804 hr_utility.set_location ('Leaving Proc', 2);
4805 EXCEPTION
4806 WHEN OTHERS
4807 THEN
4808 hr_utility.set_location ('Some errors', 20);
4809 fnd_msg_pub.ADD;
4810 END chk_delete_option;
4811
4812
4813 ---------------------------change_ceiling_step-----------------------------
4814 --
4815
4816 PROCEDURE change_ceiling_step (
4817 p_copy_entity_txn_id IN NUMBER,
4818 p_effective_date IN DATE,
4819 p_initial_ceiling_id IN NUMBER,
4820 p_final_ceiling_id IN NUMBER,
4821 p_grade_result_id IN NUMBER
4822 )
4823 IS
4824 /*
4825 Will throw error if there are employee placements above the new Ceiling Step.
4826 */
4827 CURSOR csr_init_step
4828 IS
4829 SELECT object_version_number, dml_operation
4830 FROM ben_copy_entity_results
4831 WHERE copy_entity_result_id = p_initial_ceiling_id;
4832
4833 CURSOR csr_final_step
4834 IS
4835 SELECT object_version_number, dml_operation, information253,
4836 information255, information263
4837 FROM ben_copy_entity_results
4838 WHERE copy_entity_result_id = p_final_ceiling_id;
4839
4840 CURSOR csr_grade
4841 IS
4842 SELECT object_version_number, dml_operation
4843 FROM ben_copy_entity_results
4844 WHERE copy_entity_result_id = p_grade_result_id;
4845
4846 l_initial_step csr_init_step%ROWTYPE;
4847 l_final_step csr_final_step%ROWTYPE;
4848 l_grade csr_grade%ROWTYPE;
4849 l_final_step_cer_id NUMBER;
4850 l_final_step_id NUMBER;
4851 l_gspine_id NUMBER;
4852 BEGIN
4853 hr_multi_message.enable_message_list;
4854 hr_utility.set_location ('Inside Proc..', 5);
4855
4856 if p_initial_ceiling_id <> p_final_ceiling_id THEN
4857 OPEN csr_final_step;
4858 FETCH csr_final_step INTO l_final_step;
4859
4860 IF csr_final_step%FOUND
4861 THEN
4862 l_gspine_id := l_final_step.information255;
4863 hr_utility.set_location (
4864 'Going to check for New Ceiling with the following..',
4865 15
4866 );
4867 hr_utility.set_location ('Going to check for New Ceiling..', 25);
4868 hr_utility.set_location ('Eff Date' || p_effective_date, 30);
4869 hr_utility.set_location (
4870 'gSpine id' || l_gspine_id,
4871 35
4872 );
4873 hr_utility.set_location (
4874 'New Ceiling Sequence' || l_final_step.information263,
4875 45
4876 );
4877 if l_gspine_id is not null THEN
4878 per_grade_spines_pkg.chk_low_ceiling (
4879 p_val_start => p_effective_date,
4880 p_val_end => p_effective_date,
4881 p_gspine_id => l_gspine_id,
4882 p_new_ceil => l_final_step.information263
4883 );
4884 END IF;
4885 ben_copy_entity_results_api.update_copy_entity_results (
4886 p_copy_entity_result_id => p_final_ceiling_id,
4887 p_effective_date => p_effective_date,
4888 p_copy_entity_txn_id => p_copy_entity_txn_id,
4889 p_information98 => 'Y',
4890 p_object_version_number => l_final_step.object_version_number,
4891 p_information323 => NULL,
4892 p_dml_operation => get_dml_operation (
4893 l_final_step.dml_operation
4894 )
4895 );
4896 l_final_step_cer_id := p_final_ceiling_id;
4897 l_final_step_id := l_final_step.information253;
4898 ELSE
4899 l_final_step_id := NULL;
4900 l_final_step_cer_id := NULL;
4901 END IF;
4902
4903 CLOSE csr_final_step;
4904 OPEN csr_init_step;
4905 FETCH csr_init_step INTO l_initial_step;
4906
4907 IF (csr_init_step%FOUND)
4908 THEN
4909 ben_copy_entity_results_api.update_copy_entity_results (
4910 p_copy_entity_result_id => p_initial_ceiling_id,
4911 p_effective_date => p_effective_date,
4912 p_copy_entity_txn_id => p_copy_entity_txn_id,
4913 p_information98 => 'N',
4914 p_object_version_number => l_initial_step.object_version_number,
4915 p_dml_operation => get_dml_operation (
4916 l_initial_step.dml_operation
4917 ),
4918 p_information323 => NULL
4919 );
4920 END IF;
4921
4922 CLOSE csr_init_step;
4923 OPEN csr_grade;
4924 FETCH csr_grade INTO l_grade;
4925 CLOSE csr_grade;
4926 ben_copy_entity_results_api.update_copy_entity_results (
4927 p_copy_entity_result_id => p_grade_result_id,
4928 p_effective_date => p_effective_date,
4929 p_copy_entity_txn_id => p_copy_entity_txn_id,
4930 p_information103 => 'Y',
4931 p_information262 => l_final_step_cer_id,
4932 p_information259 => l_final_step_id,
4933 p_object_version_number => l_grade.object_version_number,
4934 p_information323 => NULL,
4935 p_dml_operation => get_dml_operation (
4936 l_grade.dml_operation
4937 )
4938 );
4939 END IF;
4940 -- Call to raise any errors on multi-message list
4941 hr_multi_message.end_validation_set;
4942 -- p_error_msg := l_msg;
4943 EXCEPTION
4944 WHEN OTHERS
4945 THEN
4946 fnd_msg_pub.ADD;
4947 END change_ceiling_step;
4948
4949 ---
4950 ------------CHK_STEPS_IN_GRADE ---
4951 --ggnanagu
4952 PROCEDURE chk_steps_in_grade (
4953 p_copy_entity_txn_id IN NUMBER,
4954 p_grade_result_id IN NUMBER,
4955 p_status OUT NOCOPY VARCHAR2
4956 )
4957 IS
4958 /*
4959 will return N if there are no steps attached
4960 will return Y if there are steps
4961 Will throw an Error if there are Employee placements..
4962 */
4963 CURSOR csr_steps_in_grade
4964 IS
4965 SELECT NULL
4966 FROM ben_copy_entity_results
4967 WHERE table_alias = 'COP'
4968 AND copy_entity_txn_id = p_copy_entity_txn_id
4969 AND gs_parent_entity_result_id = p_grade_result_id
4970 AND result_type_cd = 'DISPLAY'
4971 AND NVL (information104, 'PPP') <> 'UNLINK';
4972
4973 CURSOR csr_grade_scale_dets
4974 IS
4975 SELECT information253, information255
4976 FROM ben_copy_entity_results
4977 WHERE copy_entity_result_id = p_grade_result_id;
4978
4979
4980 cursor c1(p_pspine_id IN Number,
4981 p_grd_id IN NUMBER,
4982 p_effective_date IN DATE) is
4983 select 'x'
4984 from per_spinal_point_steps_f sps,
4985 per_grade_spines_f gs
4986 where gs.grade_spine_id = sps.grade_spine_id
4987 and gs.parent_spine_id = p_pspine_id
4988 and gs.grade_id = p_grd_id
4989 and exists
4990 (select null
4991 from per_spinal_point_placements_f sp
4992 where sp.step_id = sps.step_id
4993 and (p_effective_date between effective_start_date AND effective_end_date-1
4994 or effective_start_date >= p_effective_date ));
4995
4996
4997 --
4998 l_exists VARCHAR2(1);
4999 l_effdate DATE;
5000 l_steps_in_grade NUMBER;
5001 l_grade_scale_dets csr_grade_scale_dets%ROWTYPE;
5002 BEGIN
5003 hr_multi_message.enable_message_list;
5004
5005
5006 select action_date
5007 into l_effdate
5008 from pqh_copy_entity_txns
5009 where copy_entity_txn_id = p_copy_entity_txn_id;
5010
5011
5012
5013 OPEN csr_steps_in_grade;
5014 FETCH csr_steps_in_grade INTO l_steps_in_grade;
5015
5016 IF (csr_steps_in_grade%FOUND)
5017 THEN
5018 p_status := 'Y';
5019 OPEN csr_grade_scale_dets;
5020 FETCH csr_grade_scale_dets INTO l_grade_scale_dets;
5021
5022 IF csr_grade_scale_dets%FOUND
5023 THEN
5024 OPEN c1(p_pspine_id => l_grade_scale_dets.information255,
5025 p_grd_id => l_grade_scale_dets.information253,
5026 p_effective_date => l_effdate);
5027
5028 fetch c1 into l_exists;
5029 IF c1%found THEN
5030 hr_utility.set_message(801, 'PER_7933_DEL_GRDSPN_PLACE');
5031 close c1;
5032 hr_utility.raise_error;
5033 END IF;
5034
5035
5036 /* per_grade_spines_pkg.stb_del_validation (
5037 p_pspine_id => l_grade_scale_dets.information255,
5038 p_grd_id => l_grade_scale_dets.information253
5039 );*/
5040 END IF;
5041
5042 CLOSE csr_grade_scale_dets;
5043 ELSE
5044 p_status := 'N';
5045 END IF;
5046
5047 CLOSE csr_steps_in_grade;
5048 -- Call to raise any errors on multi-message list
5049 hr_multi_message.end_validation_set;
5050 -- p_error_msg := l_msg;
5051 EXCEPTION
5052 WHEN OTHERS
5053 THEN
5054 fnd_msg_pub.ADD;
5055 END chk_steps_in_grade;
5056
5057
5058
5059 --
5060
5061 Function get_dflt_salary_rate
5062 (p_copy_entity_txn_id in Number,
5063 p_copy_entity_result_id in Number,
5064 p_rate_hgrid_node in varchar2)
5065 Return Number is
5066
5067 l_proc varchar2(72) := g_package||'.get_dflt_salary_rate';
5068
5069 l_get_which_rates varchar2(20) := get_which_rates(p_copy_entity_txn_id);
5070 dflt_sal_rate Number := null;
5071
5072
5073 cursor grd_rates(l_action_dt in date) is
5074 select copy_entity_txn_id, information297 from ben_copy_entity_results
5075 where
5076 copy_entity_txn_id = p_copy_entity_txn_id
5077 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5078 and information277 in (
5079 select copy_entity_result_id
5080 from ben_copy_entity_results
5081 where table_alias ='PLN'
5082 and result_type_cd = 'DISPLAY'
5083 and gs_mirror_src_entity_result_id = p_copy_entity_result_id)
5084 and table_alias = 'HRRATE' and l_action_dt
5085 between nvl(information2, hr_general.start_of_time) and nvl(information3, hr_general.end_of_time);
5086
5087 Cursor Csr_Action_Dt is
5088 Select Action_Date
5089 From Pqh_Copy_Entity_txns
5090 Where Copy_Entity_Txn_id = p_copy_entity_txn_id;
5091
5092 cursor point_cer is
5093 select information262 from ben_copy_entity_results
5094 where
5095 copy_entity_txn_id = p_copy_entity_txn_id
5096 and
5097 copy_entity_result_id = p_copy_entity_result_id;
5098
5099 cursor pnt_rates(point_cer_id number,l_action_dt in date) is
5100 select copy_entity_Txn_id, information297 from ben_copy_entity_results
5101 where
5102 copy_entity_txn_id = p_copy_entity_txn_id
5103 and information278 = point_cer_id
5104 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5105 and table_alias = 'HRRATE' and l_action_dt
5106
5107 between nvl(information2, hr_general.start_of_time) and nvl(information3, hr_general.end_of_time);
5108
5109 l_point_cer_id number := -1;
5110 l_action_date date;
5111 cnt number := 0;
5112 begin
5113 if g_debug then
5114 hr_utility.set_location('Entering '||l_proc,5);
5115 end if;
5116
5117 open csr_action_dt;
5118 fetch csr_action_dt into l_action_date;
5119 close csr_action_dt;
5120
5121 if ((p_rate_hgrid_node = 'CPP') and (l_get_which_rates = 'GRADE')) then
5122 if g_debug then
5123 hr_utility.set_location('These are grade rates ',5);
5124 end if;
5125 for each_pnt in grd_rates(l_action_date) loop
5126 dflt_sal_rate := each_pnt.information297;
5127 end loop;
5128 elsif ((p_rate_hgrid_node = 'COP') and (l_get_which_rates = 'POINT')) then
5129 if g_debug then
5130 hr_utility.set_location('These are point rates ',5);
5131 end if;
5132 for each_pnt_cer_id in point_cer loop
5133 l_point_cer_id := each_pnt_cer_id.information262;
5134 end loop;
5135
5136 for each_pnt in pnt_rates(l_point_cer_id,l_action_date) loop
5137 dflt_sal_rate := each_pnt.information297;
5138 end loop;
5139
5140 elsif ((p_rate_hgrid_node = 'COP') and (l_get_which_rates = 'STEP')) then
5141 if g_debug then
5142 hr_utility.set_location('These are step rates '||l_proc,5);
5143 end if;
5144 for each_pnt_cer_id in point_cer loop
5145 l_point_cer_id := each_pnt_cer_id.information262;
5146 end loop;
5147
5148 for each_pnt in pnt_rates(l_point_cer_id,l_action_date) loop
5149 dflt_sal_rate := each_pnt.information297;
5150 end loop;
5151 else
5152 if g_debug then
5153 hr_utility.set_location('This node is not valid for dlft salary determination
5154 '||l_proc,5);
5155 end if;
5156
5157 dflt_sal_rate := null;
5158
5159 end if;
5160 if g_debug then
5161 hr_utility.set_location('Successfull Completion '||l_proc,5);
5162 end if;
5163 return dflt_sal_rate;
5164
5165 EXCEPTION WHEN others THEN
5166 if g_debug then
5167 hr_utility.set_location('ERROR. Unhandled Exception occurred. ERROR in '||l_proc,5);
5168 end if;
5169 end get_dflt_salary_rate;
5170
5171 ---------------------------------------------------------------------------
5172 -----------------------< Update_frps_point_rate >--------------------------
5173 ---------------------------------------------------------------------------
5174
5175 procedure update_frps_point_rate(p_point_cer_id in number,
5176 p_copy_entity_txn_id in number,
5177 p_business_group_id in number,
5178 p_salary_rate in number,
5179 p_gross_index in number,
5180 p_effective_date in date
5181 )
5182 IS
5183 l_salary_rate NUMBER;
5184 BEGIN
5185 if p_gross_index is null THEN
5186 l_salary_rate := p_salary_rate;
5187 ELSE
5188 l_salary_rate := pqh_corps_utility.get_salary_rate(p_gross_index => p_gross_index,
5189 p_effective_date =>p_effective_date,
5190 p_copy_entity_txn_id => p_copy_entity_txn_id);
5191 END IF;
5192
5193 pqh_gsp_hr_to_stage.update_frps_point_rate(p_point_cer_id => p_point_cer_id,
5194 p_copy_entity_txn_id => p_copy_entity_txn_id,
5195 p_business_group_id => p_business_group_id,
5196 p_point_value => l_salary_rate,
5197 p_effective_date => p_effective_date);
5198 END update_frps_point_rate;
5199
5200 ---------------------------------------------------------------------------------------------
5201 -----------------------------< CHK_FROM_STEPS >----------------------------------------------
5202 -------------------------------------------------------------------------------------------
5203 Function chk_from_steps(p_parent_spine_id IN per_parent_spines.parent_spine_id%TYPE)
5204 RETURN VARCHAR2
5205 IS
5206 l_status varchar2(1) := 'N';
5207 cursor csr_grade_id
5208 is
5209 select grade_id
5210 from per_grade_spines_f
5211 where parent_spine_id = p_parent_spine_id;
5212 Cursor Csr_Plan_id (p_grade_id IN NUMBER)
5213 is
5214 select pl_id
5215 from ben_pl_f
5216 where mapping_table_pk_id = p_grade_id
5217 and mapping_table_name = 'PER_GRADES';
5218 Cursor Csr_Pgm_id (p_pl_id IN NUMBER)
5219 is
5220 select pgm_id
5221 from ben_plip_f
5222 where pl_id = p_pl_id;
5223 Cursor Csr_Use_Points(p_pgm_id IN NUMBER)
5224 is
5225 select USE_PROG_POINTS_FLAG
5226 from ben_pgm_f
5227 where pgm_id = p_pgm_id;
5228 begin
5229 if g_debug then
5230 hr_utility.set_location('Entering chk_from_steps : ',10);
5231 end if;
5232 for rec_grade_id in csr_grade_id
5233 loop
5234 if g_debug then
5235 hr_utility.set_location('Fetched the Grade Id : '||rec_grade_id.grade_id,20);
5236 end if;
5237 for rec_plan_id in csr_plan_id(rec_grade_id.grade_id)
5238 loop
5239 if g_debug then
5240 hr_utility.set_location('Fetched the Plan Id : '||rec_plan_id.pl_id,30);
5241 end if;
5242 for rec_pgm_id in csr_pgm_id(rec_plan_id.pl_id)
5243 loop
5244 if g_debug then
5245 hr_utility.set_location('Fetched the Pgm Id : '||rec_pgm_id.pgm_id,40);
5246 end if;
5247 for rec_use_points IN csr_use_points(rec_pgm_id.pgm_id)
5248 loop
5249 if g_debug then
5250 hr_utility.set_location('Fetched the Flag : '||rec_use_points.use_prog_points_flag,50);
5251 end if;
5252 if rec_use_points.use_prog_points_flag = 'N' THEN
5253 l_status := 'Y';
5254 if g_debug then
5255 hr_utility.set_location('Changing the Status to : '||l_status,60);
5256 end if;
5257 END IF;
5258 end loop;
5259 end loop;
5260 end loop;
5261 end loop;
5262 hr_utility.set_location('Returning the Status as : '||l_status,90);
5263 Return l_status;
5264 Exception when others then
5265 hr_utility.set_location('Error and returning : '||l_status,90);
5266 return 'N';
5267 END chk_from_steps;
5268
5269 function check_crset(p_crset_type in VARCHAR2,p_crset_id IN NUMBER,p_copy_entity_txn_id IN NUMBER,p_scale_cer_id in number)
5270 return varchar2
5271 IS
5272 Cursor csr_is_steps
5273 Is
5274 select information18
5275 from ben_copy_entity_results
5276 where table_alias = 'PGM'
5277 and copy_entity_txn_id = p_copy_entity_txn_id
5278 and result_type_cd = 'DISPLAY';
5279 Cursor csr_crrate
5280 is
5281 select null
5282 from ben_copy_entity_results
5283 where information160 = p_crset_id
5284 and table_alias = 'CRRATE'
5285 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5286 and copy_entity_txn_id = p_copy_entity_txn_id
5287 and information169 in
5288 (select copy_entity_result_id
5289 from ben_copy_entity_results
5290 where table_alias = 'OPT'
5291 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5292 and information256 = p_scale_cer_id
5293 and copy_entity_txn_id = p_copy_entity_txn_id);
5294 l_crrate csr_crrate%ROWTYPE;
5295 l_is_steps csr_is_steps%ROWTYPE;
5296 begin
5297 OPEN csr_is_steps;
5298 FETCH Csr_is_steps into l_is_steps;
5299 if l_is_steps.information18 = 'N' THEN
5300 close csr_is_steps;
5301 RETURN 'Y';
5302 END IF;
5303 if p_crset_type <> 'POINT' then
5304 return 'Y';
5305 else
5306 open csr_crrate;
5307 fetch csr_crrate into l_crrate;
5308 if(csr_crrate%NOTFOUND)then
5309 return 'N';
5310 else
5311 return 'Y';
5312 end if;
5313 close csr_crrate;
5314 end if;
5315 end check_crset;
5316
5317 procedure change_scale_name(p_copy_entity_txn_id in number,p_pl_cer_id in number,p_short_name in varchar2)
5318 is
5319 Cursor csr_is_steps
5320 Is
5321 select information18
5322 from ben_copy_entity_results
5323 where table_alias = 'PGM'
5324 and copy_entity_txn_id = p_copy_entity_txn_id
5325 and result_type_cd = 'DISPLAY';
5326
5327 Cursor csr_plan_dtls
5328 Is
5329 select Gs_Mirror_Src_Entity_Result_Id,object_version_number
5330 from ben_copy_entity_results
5331 where copy_entity_result_id = p_pl_cer_id
5332 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5333 and copy_entity_txn_id = p_copy_entity_txn_id
5334 and table_alias = 'PLN';
5335
5336 Cursor csr_plip_dtls(p_plip_cer_id in number)
5337 Is
5338 select information258,copy_entity_result_id,dml_operation
5339 from ben_copy_entity_results
5340 where table_alias = 'CPP'
5341 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5342 and copy_entity_txn_id = p_copy_entity_txn_id
5343 and copy_entity_result_id = p_plip_cer_id;
5344
5345 Cursor csr_scale_dtls(p_scale_cer_id in number)
5346 Is
5347 Select Information98,copy_entity_result_id,dml_operation
5348 from ben_copy_entity_results
5349 where copy_entity_result_id = p_scale_cer_id
5350 and table_alias = 'SCALE'
5351 and copy_entity_txn_id = p_copy_entity_txn_id;
5352
5353 l_plan_dtls csr_plan_dtls%ROWTYPE;
5354 l_plip_dtls csr_plip_dtls%ROWTYPE;
5355 l_scale_dtls csr_scale_dtls%ROWTYPE;
5356 l_is_steps csr_is_steps%ROWTYPE;
5357 begin
5358
5359 OPEN csr_is_steps;
5360 FETCH Csr_is_steps into l_is_steps;
5361 if l_is_steps.information18 = 'Y' THEN
5362 RETURN;
5363 ELSE
5364 OPEN csr_plan_dtls;
5365 Fetch csr_plan_dtls into l_plan_dtls;
5366 IF csr_plan_dtls%NOTFOUND THEN
5367 hr_utility.set_location('No Plan avlbl. Exiting..',80);
5368 return;
5369 else
5370 hr_utility.set_location('Plip cer Id :..'||l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id,20);
5371 OPEN csr_plip_dtls(l_plan_dtls.Gs_Mirror_Src_Entity_Result_Id);
5372 fetch csr_plip_dtls into l_plip_dtls;
5373 IF csr_plip_dtls%NOTFOUND THEN
5374 hr_utility.set_location('No Plip avlbl for Plan. Exiting..',90);
5375 return;
5376 else
5377 hr_utility.set_location('Scale cer Id :..'||l_plip_dtls.Information258,30);
5378 OPEN Csr_scale_dtls(l_plip_dtls.Information258);
5379 fetch csr_scale_dtls into l_scale_dtls;
5380 IF csr_scale_dtls%NOTFOUND THEN
5381 hr_utility.set_location('No scale avlbl for Plip... Exiting..',90);
5382 return;
5383 Else
5384 hr_utility.set_location('Scale Name is :..'||l_scale_dtls.Information98,40);
5385 hr_utility.set_location('Grade Short Name Passed is :..'||l_scale_dtls.Information98,45);
5386 if p_short_name = l_scale_dtls.Information98 then
5387 hr_utility.set_location('The names match ..Exiting..',95);
5388 return;
5389 else
5390 hr_utility.set_location('Got some work to do..',55);
5391
5392 update ben_copy_entity_results
5393 set information98 = p_short_name
5394 , dml_operation = get_dml_operation(l_scale_dtls.dml_operation)
5395 where copy_entity_result_id = l_scale_dtls.copy_entity_result_id;
5396
5397 update ben_copy_entity_results
5398 set information98 = p_short_name
5399 ,dml_operation = get_dml_operation(l_plip_dtls.dml_operation)
5400 where copy_entity_result_id = l_plip_dtls.copy_entity_result_id;
5401
5402 end if;
5403 END IF;
5404 END IF;
5405 END IF;
5406 END IF;
5407 END change_scale_name;
5408
5409 procedure remove_steps(p_copy_entity_txn_id IN NUMBER, p_grade_result_id IN NUMBER)
5410 IS
5411 CURSOR csr_steps_in_grade
5412 IS
5413 SELECT copy_entity_result_id,
5414 dml_operation
5415 FROM ben_copy_entity_results
5416 WHERE table_alias = 'COP'
5417 AND copy_entity_txn_id = p_copy_entity_txn_id
5418 AND gs_parent_entity_result_id = p_grade_result_id
5419 AND result_type_cd = 'DISPLAY'
5420 AND NVL (information104, 'PPP') <> 'UNLINK';
5421
5422 Cursor Csr_Action_Dt is
5423 Select Action_Date
5424 From Pqh_Copy_Entity_txns
5425 Where Copy_Entity_Txn_id = p_copy_entity_txn_id;
5426
5427 l_Action_Date date;
5428 begin
5429
5430 Open Csr_Action_Dt;
5431 Fetch Csr_Action_Dt Into L_Action_Date;
5432 Close Csr_Action_Dt;
5433
5434 for rec_steps in csr_steps_in_grade loop
5435
5436 /* update ben_copy_entity_results
5437 set copy_entity_result_id = rec_steps.copy_entity_result_id,
5438 information104 = 'UNLINK',
5439 dml_operation = get_dml_operation(rec_steps.dml_operation)
5440 where copy_entity_result_id = rec_steps.copy_entity_result_id; */
5441
5442 Pqh_Gsp_Grd_Step_Remove.REMOVE_OIPL(P_COPY_ENTITY_TXN_ID => p_copy_entity_txn_id
5443 ,P_COPY_ENTITY_RESULT_ID => rec_steps.copy_entity_result_id
5444 ,P_EFFECTIVE_DATE => Nvl(L_Action_Date, hr_general.Effective_Date));
5445
5446 end loop;
5447
5448
5449 end remove_steps;
5450 procedure change_rates_date(p_copy_entity_txn_id in number,p_pl_cer_id in number,p_start_date in DATE)
5451 is
5452 Cursor csr_plan_dtls
5453 Is
5454 select information307
5455 from ben_copy_entity_results
5456 where copy_entity_result_id = p_pl_cer_id
5457 and copy_entity_txn_id = p_copy_entity_txn_id
5458 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5459 and table_alias = 'PLN';
5460
5461 Cursor Csr_rate_details(p_plan_old_date in date)
5462 is
5463 select information2,information3,copy_entity_result_id
5464 from ben_copy_entity_results
5465 where information277 =p_pl_cer_id
5466 and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5467 and table_alias in ('HRRATE','ABR')
5468 and information2 = p_plan_old_date
5469 and copy_entity_txn_id = p_copy_entity_txn_id;
5470 l_plan_old_date date;
5471 begin
5472 if g_debug then
5473 hr_utility.set_location('Entering change_rates_date',10);
5474 hr_utility.set_location('Copy Entity Txn Id:'||p_copy_entity_txn_id,15);
5475 hr_utility.set_location('Plan cer Id:'||p_pl_cer_id,20);
5476 hr_utility.set_location('Start Date:'||p_start_date,25);
5477 end if;
5478 Open csr_plan_dtls;
5479 fetch csr_plan_dtls into l_plan_old_date;
5480 IF csr_plan_dtls%NOTFOUND THEN
5481 if g_debug then
5482 hr_utility.set_location('No Plan records exist',85);
5483 hr_utility.set_location('Leaving change_rates_date',90);
5484 end if;
5485 return;
5486 end if;
5487
5488 close csr_plan_dtls;
5489 if l_plan_old_date = p_start_date THEN
5490 if g_debug then
5491 hr_utility.set_location('Old Date:'||l_plan_old_date,75);
5492 hr_utility.set_location('No date Change needed',85);
5493 hr_utility.set_location('Leaving change_rates_date',90);
5494 end if;
5495 return;
5496 end if;
5497 hr_utility.set_location('Old Date:'||l_plan_old_date,60);
5498
5499 for i in csr_rate_details(l_plan_old_date) loop
5500 if g_debug then
5501 hr_utility.set_location('Updating record with cer_id:'||i.copy_entity_result_id,40);
5502 end if;
5503
5504 if i.information3 < p_start_date then
5505 hr_utility.set_message(8302,'PER_289567_GRADE_DATE_FROM');
5506 hr_utility.raise_error;
5507 end if;
5508
5509 update ben_copy_entity_results
5510 set information2 = p_start_date
5511 where copy_entity_result_id = i.copy_entity_result_id;
5512 if g_debug then
5513 hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);
5514 end if;
5515 end loop;
5516 if g_debug then
5517 hr_utility.set_location('Leaving change_rates_date',80);
5518 end if;
5519
5520 END change_rates_date;
5521
5522 -----------
5523 FUNCTION GET_CURRENCY_CODE(p_copy_entity_txn_id in number)
5524 RETURN varchar2 IS
5525 /*
5526 Author : ggnanagu
5527 Purpose : This function returns the Currency code associated with the Grade Ladder
5528 */
5529 l_result Varchar2(20);
5530 l_proc varchar2(72) := g_package||'USE_POINT_OR_STEP';
5531
5532 Cursor csr_currency_code
5533 IS
5534 Select nvl(grdldr.INFORMATION50,'USD') /* INFORMATION50 -> Currency Code */
5535 FROM Ben_Copy_Entity_Results grdldr
5536 WHERE grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
5537 AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
5538 AND grdldr.Table_Alias = 'PGM';
5539
5540
5541 BEGIN
5542
5543 if g_debug then
5544 hr_utility.set_location('Leaving '||l_proc,10);
5545 hr_utility.set_location('p_copy_entity_txn_id '||p_copy_entity_txn_id,20);
5546 end if;
5547
5548 Open csr_currency_code;
5549 Fetch csr_currency_code into l_result;
5550 Close csr_currency_code;
5551
5552 if g_debug then
5553 hr_utility.set_location('l_result '||l_result,25);
5554 end if;
5555 return l_result;
5556
5557 if g_debug then
5558 hr_utility.set_location('Leaving '||l_proc,30);
5559 end if;
5560
5561 EXCEPTION
5562 When others THEN
5563 l_result := 'STEP';
5564 return l_result;
5565
5566 END GET_CURRENCY_CODE;
5567
5568 --
5569 FUNCTION get_grd_start_date
5570 (p_copy_entity_result_id in ben_copy_entity_results.copy_entity_result_id%TYPE)
5571 RETURN DATE
5572 IS
5573 l_grd_start_date DATE;
5574
5575 begin
5576
5577 select information307
5578 into l_grd_start_date
5579 from ben_copy_entity_results
5580 where copy_entity_result_id = p_copy_entity_result_id;
5581
5582
5583 RETURN l_grd_start_date;
5584 END get_grd_start_date;
5585 procedure change_start_step(p_copy_entity_txn_id in number
5586 ,p_init_start_step in number
5587 ,p_final_start_step in number
5588 ,p_grade_result_id in number
5589 )
5590 is
5591 Cursor csr_indicator
5592 is
5593 select information103
5594 from ben_copy_entity_results
5595 where copy_entity_result_id =p_grade_result_id;
5596 l_indicator varchar2(10);
5597 begin
5598 open csr_indicator;
5599 fetch csr_indicator into l_indicator;
5600 close csr_indicator;
5601 if nvl(l_indicator,'X') = 'Y'
5602 then l_indicator := 'B';
5603 else
5604 l_indicator := 'S';
5605 end if;
5606
5607
5608 update ben_copy_entity_results
5609 set information228 = p_final_start_step
5610 ,information103 = l_indicator
5611 where copy_entity_result_id = p_grade_result_id;
5612 end change_start_step;
5613
5614 --
5615 FUNCTION get_formula_name (p_formula_id IN NUMBER, p_effective_date IN DATE)
5616 RETURN VARCHAR2
5617 IS
5618 CURSOR csr_formula_name
5619 IS
5620 SELECT ff.formula_name
5621 FROM ff_formulas_f ff
5622 WHERE ff.formula_id = p_formula_id
5623 AND p_effective_date BETWEEN ff.effective_start_date
5624 AND ff.effective_end_date;
5625
5626 l_formula_name ff_formulas_f.formula_name%TYPE;
5627 BEGIN
5628
5629 OPEN csr_formula_name;
5630 FETCH csr_formula_name INTO l_formula_name;
5631 CLOSE csr_formula_name;
5632
5633 RETURN l_formula_name;
5634
5635 END get_formula_name;
5636
5637 FUNCTION get_element_name (p_element_type_id IN NUMBER)
5638 RETURN VARCHAR2
5639 IS
5640 CURSOR csr_element_name
5641 IS
5642 SELECT ettl.element_name
5643 FROM pay_element_types_f_tl ettl
5644 WHERE ettl.LANGUAGE = USERENV ('LANG')
5645 AND ettl.element_type_id = p_element_type_id;
5646
5647 l_element_name pay_element_types_f_tl.element_name%TYPE;
5648 BEGIN
5649
5650 OPEN csr_element_name;
5651 FETCH csr_element_name INTO l_element_name;
5652 CLOSE csr_element_name;
5653
5654 RETURN l_element_name;
5655
5656 END get_element_name;
5657
5658 FUNCTION get_input_val_name (p_input_value_id IN NUMBER)
5659 RETURN VARCHAR2
5660 IS
5661 CURSOR csr_input_val_name
5662 IS
5663 SELECT ptl.NAME
5664 FROM pay_input_values_f_tl ptl
5665 WHERE ptl.LANGUAGE = USERENV ('LANG')
5666 AND ptl.input_value_id = p_input_value_id;
5667
5668 l_input_val_name pay_input_values_f_tl.NAME%TYPE;
5669 BEGIN
5670
5671 OPEN csr_input_val_name;
5672 FETCH csr_input_val_name INTO l_input_val_name;
5673 CLOSE csr_input_val_name;
5674
5675 RETURN l_input_val_name;
5676
5677 END get_input_val_name;
5678 --
5679 FUNCTION get_bg_currency(p_business_group_id in number) RETURN varchar2
5680 is
5681 begin
5682 return Hr_General.DEFAULT_CURRENCY_CODE(p_Business_group_Id);
5683 end get_bg_currency;
5684 --
5685 PROCEDURE chk_grd_ldr_details (
5686 p_business_group_id IN NUMBER,
5687 p_name IN VARCHAR2,
5688 p_dflt_pgm_flag IN VARCHAR2,
5689 p_pgm_id IN NUMBER,
5690 p_effective_date IN DATE
5691 )
5692 IS
5693 CURSOR csr_name
5694 IS
5695 SELECT NULL
5696 FROM ben_pgm_f
5697 WHERE NAME = p_name
5698 AND business_group_id = p_business_group_id
5699 AND pgm_id <> NVL (p_pgm_id, -1);
5700
5701 CURSOR csr_dflt_grd_ldr
5702 IS
5703 SELECT NAME
5704 FROM ben_pgm_f
5705 WHERE dflt_pgm_flag = 'Y'
5706 AND business_group_id = p_business_group_id
5707 AND pgm_id <> NVL (p_pgm_id, -1)
5708 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
5709
5710 l_pgm_name ben_pgm_f.NAME%TYPE;
5711 l_dummy VARCHAR2 (10);
5712 BEGIN
5713 hr_multi_message.enable_message_list;
5714
5715 OPEN csr_name;
5716
5717 FETCH csr_name
5718 INTO l_dummy;
5719
5720 IF csr_name%FOUND
5721 THEN
5722 hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_NAME_UNQ');
5723 hr_multi_message.ADD;
5724 END IF;
5725
5726 CLOSE csr_name;
5727
5728 IF p_dflt_pgm_flag = 'Y'
5729 THEN
5730 OPEN csr_dflt_grd_ldr;
5731
5732 FETCH csr_dflt_grd_ldr
5733 INTO l_pgm_name;
5734
5735 IF csr_dflt_grd_ldr%FOUND
5736 THEN
5737 hr_utility.set_message (8302, 'PQH_GSP_GRDLDR_DFLT_ERR');
5738 hr_utility.set_message_token ('LADDER', l_pgm_name);
5739 hr_multi_message.ADD;
5740 END IF;
5741
5742 CLOSE csr_dflt_grd_ldr;
5743 END IF;
5744 hr_multi_message.end_validation_set;
5745 EXCEPTION
5746 WHEN hr_multi_message.error_message_exist
5747 THEN
5748 NULL;
5749 WHEN OTHERS
5750 THEN
5751 RAISE;
5752 END chk_grd_ldr_details;
5753 --------------------------------------------------------
5754 --------------< get_gl_ann_factor >---------------------
5755 --------------------------------------------------------
5756 function get_gl_ann_factor(p_pgm_id in number)
5757 return varchar2
5758 IS
5759 Cursor csr_gl_ann_factor IS
5760 SELECT pgi_information5 rate_ann_factor
5761 FROM ben_pgm_extra_info
5762 WHERE pgm_id = p_pgm_id
5763 AND information_type = 'PQH_GSP_EXTRA_INFO';
5764 l_proc constant varchar2(72):= g_package||'get_gl_ann_factor';
5765 l_gl_ann_factor ben_pgm_extra_info.pgi_information5%TYPE;
5766 BEGIN
5767 hr_utility.set_location('Entering:'||l_proc, 5);
5768 open csr_gl_ann_factor;
5769 FETCH csr_gl_ann_factor INTO l_gl_ann_factor;
5770 CLOSE csr_gl_ann_factor;
5771 return l_gl_ann_factor;
5772 hr_utility.set_location(' Leaving:'||l_proc, 10);
5773
5774 Exception
5775 WHEN Others THEN
5776 raise;
5777 END get_gl_ann_factor;
5778
5779
5780 PROCEDURE upd_ceiling_info(p_grade_cer_id IN NUMBER, p_step_id IN number)
5781 IS
5782 BEGIN
5783
5784 update ben_copy_entity_results
5785 set information259 = p_step_id,
5786 information103 = 'Y'
5787 where copy_entity_result_id = p_grade_cer_id
5788 and table_alias = 'CPP';
5789
5790 END upd_ceiling_info;
5791 Function Get_current_rate_value
5792 (P_Entity_id In Number,
5793 P_Effective_Date In Date
5794 ) RETURN Number Is
5795
5796 Cursor Step is
5797 Select Steps.spinal_point_id
5798 From Per_Spinal_Point_Placements_F Plcmt,
5799 Per_Spinal_Point_Steps_F Steps
5800 Where Plcmt.ASSIGNMENT_ID = P_Entity_id
5801 and P_Effective_Date
5802 Between Plcmt.Effective_Start_Date and Plcmt.Effective_End_Date
5803 and Plcmt.Step_id = Steps.Step_Id
5804 and P_Effective_Date
5805 Between Steps.Effective_Start_Date and Steps.Effective_End_Date;
5806
5807 l_proc constant varchar2(72):= g_package||'Get_current_rate_value';
5808 l_Spinal_Point_Id Per_Spinal_POint_Steps_F.spinal_point_id%TYPE := NULL;
5809 -- l_rate_value Number(10) := NULL;
5810 l_rate_value Number(10,2) := NULL; -- Fix for the bug#11934026
5811
5812 begin
5813 hr_utility.set_location('Entering:'||l_proc, 5);
5814 hr_utility.set_location('P_Entity_id :'||P_Entity_id, 55);
5815 hr_utility.set_location('P_Effective_Date :'||P_Effective_Date , 555);
5816
5817 Open Step;
5818 Fetch Step into l_Spinal_Point_Id;
5819 Close step;
5820
5821 hr_utility.set_location('l_Spinal_Point_Id: '||l_Spinal_Point_Id, 10);
5822
5823 if l_Spinal_Point_Id is not null then
5824 select value into l_rate_value
5825 from pay_grade_rules_f
5826 where grade_or_spinal_point_id = l_Spinal_Point_Id
5827 and rate_type = 'SP'
5828 and P_Effective_Date
5829 between Effective_Start_Date and Effective_End_Date;
5830 end if;
5831
5832 hr_utility.set_location('l_rate_valued: '||l_rate_value, 20);
5833 hr_utility.set_location('Leaving '||l_proc,30);
5834 return l_rate_value;
5835
5836 exception
5837 when others then
5838 hr_utility.set_location('Exception ', 40);
5839 raise;
5840 End Get_current_rate_value;
5841
5842 --
5843 End pqh_gsp_utility;
5844
[Home] [Help]