[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_STAGE_TO_HR
Source
1 package body pqh_gsp_stage_to_hr as
2 /* $Header: pqgspshr.pkb 120.1 2005/12/14 03:36 hmehta noship $ */
3
4 g_package Varchar2(30) := 'pqh_gsp_stage_to_hr.';
5
6 procedure update_lept_data(p_copy_entity_txn_id in number,
7 p_le_id in number,
8 p_pt_id in number) is
9 begin
10 -- make sure plan type row exists so that plan copy can read it
11 -- if no row doesnot exist, then we have to create it
12 -- make sure ptip row also exists else, we have to create it
13 -- do we need row in ben_opt_typ_pl_typ for plan copy
14 begin
15 update ben_copy_entity_results
16 set information248 = p_pt_id
17 where copy_entity_txn_id = p_copy_entity_txn_id
18 and table_alias = 'PLN';
19 exception
20 when others then
21 hr_utility.set_location('issue in updating PT',10);
22 raise;
23 end;
24 end update_lept_data;
25
26 function get_le_pt_name (p_cd in varchar2 )
27 return varchar2 is
28 l_proc varchar2(72) := g_package||'get_le_pt_name';
29 l_name varchar2(80) ;
30 begin
31 select meaning into l_name
32 from hr_lookups
33 where lookup_type = 'PQH_GSP_LE_PT_NAME'
34 and lookup_code = decode(p_cd,'PROG','PROG_LE'
35 ,'SYNC','SYNC_LE'
36 ,'PLAN','GSP_PT');
37 return l_name ;
38 exception
39 when others then
40 hr_utility.set_location('issue in lookup ',10);
41 raise;
42 end get_le_pt_name;
43
44 function create_life_event (p_business_group_id in number
45 ,p_copy_entity_txn_id in number
46 ,p_lf_evt_oper_cd in varchar2
47 ,p_name in varchar2)
48 return number is
49 l_proc varchar2(72) := g_package||'create_life_event';
50 l_start_of_time DATE:= pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
51 l_ler_id number;
52 l_effective_start_date date;
53 l_effective_end_date date;
54 l_object_version_number number;
55 begin
56 hr_utility.set_location('Entering:'|| l_proc, 10);
57 ben_Life_Event_Reason_api.create_Life_Event_Reason(
58 p_ler_id => l_ler_id
59 ,p_effective_start_date => l_effective_start_date
60 ,p_effective_end_date => l_effective_end_date
61 ,p_object_version_number => l_object_version_number
62 ,p_effective_date => l_start_of_time
63 ,p_name => p_name
64 ,p_business_group_id => p_business_group_id
65 ,p_typ_cd => 'GSP'
66 ,p_lf_evt_oper_cd => p_lf_evt_oper_cd
67 ,p_ovridg_le_flag => 'N'
68 ,p_CK_RLTD_PER_ELIG_FLAG => 'N'
69 ,p_CM_APLY_FLAG => 'N'
70 ,p_QUALG_EVT_FLAG => 'N'
71 );
72 return l_ler_id ;
73 end create_life_event ;
74
75 function create_plan_type (p_business_group_id in number
76 ,p_copy_entity_txn_id in number
77 ,p_name in varchar2)
78 return number is
79 l_proc varchar2(72) := g_package||'create_plan_type';
80 l_start_of_time DATE:= pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
81 l_pl_typ_id number;
82 l_effective_start_date date;
83 l_effective_end_date date;
84 l_object_version_number number;
85 begin
86 hr_utility.set_location('Entering:'|| l_proc, 10);
87 ben_plan_type_api.create_plan_type(
88 p_pl_typ_id => l_pl_typ_id
89 ,p_effective_start_date => l_effective_start_date
90 ,p_effective_end_date => l_effective_end_date
91 ,p_object_version_number => l_object_version_number
92 ,p_effective_date => l_start_of_time
93 ,p_name => p_name
94 ,p_business_group_id => p_business_group_id
95 ,p_opt_typ_cd => 'GSP'
96 ,p_pl_typ_stat_cd => 'A'
97 ,p_no_mx_enrl_num_dfnd_flag => 'N'
98 ,p_no_mn_enrl_num_dfnd_flag => 'N'
99 );
100 return l_pl_typ_id;
101 end create_plan_type;
102
103 procedure setup_check(p_copy_entity_txn_id in number
104 ,p_effective_date in date
105 ,p_business_group_id in number
106 ,p_status out nocopy varchar2
107 ,p_prog_le_created_flag out nocopy varchar2
108 ,p_sync_le_created_flag out nocopy varchar2
109 ,p_plan_tp_created_flag out nocopy varchar2
110 ) is
111 l_status varchar2(30) ;
112 l_ler_id number;
113 l_ler_name varchar2(240);
114 l_pt_id number;
115 l_pt_name varchar2(240);
116 l_start_of_time DATE:= pqh_gsp_utility.get_gsp_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
117 l_effective_start_date date ;
118 l_le_pt_name varchar2(80) ;
119 l_prog_le_created_flag varchar2(1) :='N' ;
120 l_sync_le_created_flag varchar2(1) :='N' ;
121 l_plan_tp_created_flag varchar2(1) :='N' ;
122
123
124 begin
125 begin
126 select ler_id,name,effective_start_date
127 into l_ler_id,l_ler_name,l_effective_start_date
128 from ben_ler_f
129 where effective_end_date = hr_general.end_of_time
130 and business_group_id = p_business_group_id
131 and typ_cd ='GSP'
132 and lf_evt_oper_cd ='PROG';
133 hr_utility.set_location('life event '||substr(l_ler_name,1,40),10);
134
135 if l_effective_start_date <> l_start_of_time then
136 l_status := 'WRONG-DATE-PROG-LE' ;
137 end if ;
138
139 exception
140 when no_data_found then
141 hr_utility.set_location('No life event of GSP prog type exists',20);
142 l_le_pt_name := get_le_pt_name ('PROG');
143 l_ler_id := create_life_event (p_business_group_id,p_copy_entity_txn_id,'PROG',l_le_pt_name) ;
144 l_prog_le_created_flag := 'Y';
145 when too_many_rows then
146 hr_utility.set_location('2 life event of GSP prog type exists',20);
147 l_status := 'MANY-PROG-LE';
148 when others then
149 hr_utility.set_location('issue in Getting GSP PROG LE',20);
150 l_status := 'PROG-LE-ERR';
151 end;
152
153 if l_status is null then
154 begin
155 select ler_id,name,effective_start_date
156 into l_ler_id,l_ler_name,l_effective_start_date
157 from ben_ler_f
158 where effective_end_date = hr_general.end_of_time
159 and business_group_id = p_business_group_id
160 and typ_cd ='GSP'
161 and lf_evt_oper_cd ='SYNC';
162 hr_utility.set_location('life event '||substr(l_ler_name,1,40),10);
163
164 if l_effective_start_date <> l_start_of_time then
165 l_status := 'WRONG-DATE-SYNC-LE' ;
166 end if ;
167
168 exception
169 when no_data_found then
170 hr_utility.set_location('No life event of GSP sync type exists',20);
171 l_le_pt_name := get_le_pt_name ('SYNC');
172 l_ler_id := create_life_event (p_business_group_id,p_copy_entity_txn_id,'SYNC',l_le_pt_name) ;
173 l_sync_le_created_flag := 'Y';
174 when too_many_rows then
175 hr_utility.set_location('2 life event of GSP sync type exists',20);
176 l_status := 'MANY-SYNC-LE';
177 when others then
178 hr_utility.set_location('issue in Getting GSP SYNC LE',20);
179 l_status := 'SYNC-LE-ERR';
180 end;
181 end if;
182
183 if l_status is null then
184 begin
185 select pl_typ_id,name,effective_start_date
186 into l_pt_id,l_pt_name,l_effective_start_date
187 from ben_pl_typ_f
188 where effective_end_date = hr_general.end_of_time
189 and business_group_id = p_business_group_id
190 and opt_typ_cd ='GSP'
191 and pl_typ_stat_cd ='A';
192 hr_utility.set_location('pl_typ name '||substr(l_pt_name,1,40),10);
193
194 if l_effective_start_date <> l_start_of_time then
195 l_status := 'WRONG-DATE-PT' ;
196 end if ;
197
198 exception
199 when no_data_found then
200 hr_utility.set_location('No PT of GSP ',20);
201 l_le_pt_name := get_le_pt_name ('PLAN');
202 l_pt_id := create_plan_type (p_business_group_id,p_copy_entity_txn_id,l_le_pt_name);
203 l_plan_tp_created_flag := 'Y';
204 when too_many_rows then
205 hr_utility.set_location('many PT of GSP ',20);
206 l_status := 'MANY-PT';
207 when others then
208 hr_utility.set_location('issue in Getting GSP PT ',20);
209 l_status := 'PT-ERR';
210 end;
211 end if;
212
213 if l_status is null then
214 hr_utility.set_location('setup is fine, update staging area',10);
215 update_lept_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
216 p_le_id => l_ler_id,
217 p_pt_id => l_pt_id);
218 p_prog_le_created_flag := l_prog_le_created_flag;
219 p_sync_le_created_flag := l_sync_le_created_flag;
220 p_plan_tp_created_flag := l_plan_tp_created_flag;
221 else
222 p_status := l_status;
223 hr_utility.set_location('control goes back with status'||l_status,10);
224 end if;
225
226 end setup_check;
227
228 procedure delete_steps(p_grade_spine_id in number,
229 p_effective_date in date) is
230 cursor csr_steps is
231 select step_id,object_version_number,effective_start_date,effective_end_date
232 from per_spinal_point_steps_f
233 where grade_spine_id = p_grade_spine_id
234 and p_effective_date between effective_start_date and effective_end_date;
235 l_step_id number;
236 l_step_ovn number;
237 l_step_esd date;
238 l_step_eed date;
239 begin
240 for step_rec in csr_steps loop
241 l_step_id := step_rec.step_id;
242 l_step_ovn := step_rec.object_version_number;
243 l_step_esd := step_rec.effective_start_date;
244 l_step_eed := step_rec.effective_end_date;
245 hr_utility.set_location('deleting step '||l_step_id,10);
246 hr_utility.set_location('ovn '||l_step_ovn,15);
247 hr_grade_step_api.delete_grade_step
248 (p_validate => FALSE
249 ,p_effective_date => p_effective_date
250 ,p_datetrack_mode => 'DELETE'
251 ,p_step_id => l_step_id
252 ,p_object_version_number => l_step_ovn
253 ,p_effective_start_date => l_step_esd
254 ,p_effective_end_date => l_step_eed
255 );
256 hr_utility.set_location('delete step complete'||l_step_id,20);
257 end loop;
258 exception
259 when others then
260 hr_utility.set_location('steps could not be deleted',40);
261 raise;
262 end delete_steps;
263
264 procedure delete_grade_spine(p_grade_spine_id in number,
265 p_effective_date in date,
266 P_Date_track_mode In Varchar2 Default 'DELETE') is
267 l_gs_ovn number;
268 l_gs_esd date;
269 l_gs_eed date;
270 begin
271 hr_utility.set_location('inside dele grade spine',10);
272 begin
273 select object_version_number,effective_start_date,effective_end_date
274 into l_gs_ovn,l_gs_esd,l_gs_eed
275 from per_grade_spines_f
276 where grade_spine_id = p_grade_spine_id
277 and p_effective_date between effective_start_date and effective_end_date;
278 exception
279 when others then
280 hr_utility.set_location('issues in selecting grade spine',20);
281 raise;
282 end;
283 hr_utility.set_location('grade spine id is'||p_grade_spine_id,10);
284 hr_utility.set_location('grade spine ovn is'||l_gs_ovn,15);
285 hr_grade_scale_api.delete_grade_scale
286 (p_validate => FALSE
287 ,p_effective_date => p_effective_date
288 ,p_datetrack_mode => P_Date_track_mode
289 ,p_grade_spine_id => p_grade_spine_id
290 ,p_object_version_number => l_gs_ovn
291 ,p_effective_start_date => l_gs_esd
292 ,p_effective_end_date => l_gs_eed
293 );
294 exception
295 when others then
296 hr_utility.set_location('issues in deleting grade spine',40);
297 raise;
298 end delete_grade_spine;
299
300 Procedure Delete_Step (p_copy_entity_txn_id in number,
301 p_effective_date in date,
302 p_date_track_mode in varchar2 default null) Is
303 --
304
305 l_Step_Id Per_Spinal_Point_Steps_F.Step_Id%TYPE;
306 l_Effective_Start_Date Per_Spinal_Point_Steps_F.Effective_Start_Date%TYPE;
307 l_Effective_End_Date Per_Spinal_Point_Steps_F.Effective_End_Date%TYPE;
308 l_Step_Ovn Per_Spinal_Point_Steps_F.Object_Version_Number%TYPE;
309 l_datetrack_mode Varchar2(30);
310 l_Scale_Delete Varchar2(1) := 'N';
311 l_Effective_Date Date;
312 L_Hr_parent_Spine Per_grade_Spines_F.Parent_Spine_Id%TYPE;
313 l_Hr_grade_Scale_id Per_grade_Spines_F.Grade_Spine_Id%TYPE;
314 L_Esd Date;
315 l_Eed Date;
316 L_ZAP BOOLEAN;
317 L_DELETE BOOLEAN;
318 L_FUTURE_CHANGE BOOLEAN;
319 L_DELETE_NEXT_CHANGE BOOLEAN;
320 l_Plcmt_Cnt Number;
321
322 Cursor csr_Del_Oipl (P_Plip_Cer_Id IN Number) is
323 Select information253 Step_id,
324 information254 Step_Ovn
325 From ben_copy_entity_results
326 Where copy_entity_txn_id = p_copy_entity_txn_id
327 And Gs_parent_entity_result_id = P_Plip_Cer_Id
328 And table_alias = 'COP'
329 And dml_operation = 'DELETE';
330
331 Cursor Csr_Step_Dtl(P_Step_Id IN Number) is
332 Select Effective_Start_Date,
333 Effective_End_Date,
334 Object_Version_Number
335 From Per_Spinal_Point_Steps_F
336 Where Step_Id = P_Step_Id
337 and P_Effective_Date
338 Between Effective_Start_Date
339 and Effective_End_Date;
340
341 Cursor Csr_Stg_Grd_Scale is
342 Select Copy_Entity_Result_id,
343 Information253 Grade_Id,
344 Information255 Scale_Id,
345 Information258 Scale_Cer_Id
346 from Ben_Copy_Entity_Results Cpp
347 Where Copy_Entity_Txn_id = P_Copy_Entity_Txn_Id
348 and Table_Alias = 'CPP'
349 and Result_type_Cd = 'DISPLAY'
350 and Exists
351 (Select 1
352 from Ben_Copy_Entity_Results
353 Where Copy_Entity_txn_id = P_Copy_Entity_Txn_id
354 and Gs_parent_Entity_Result_id = CPP.Copy_Entity_Result_id
355 and Table_Alias = 'COP'
356 and Dml_Operation = 'DELETE');
357
358 Cursor Csr_Hr_Scale (P_Grade_Id in Number) Is
359 Select Parent_Spine_Id , Grade_Spine_Id,
360 Effective_Start_Date, Effective_End_Date
361 From Per_Grade_Spines_F
362 Where Grade_Id = P_grade_Id
363 and P_Effective_Date
364 Between Effective_Start_Date
365 and Effective_End_Date;
366
367 Cursor Csr_Del_Scle (P_Scale_Cer_id IN Number) is
368 Select 'N'
369 From Ben_Copy_Entity_Results Opt
370 Where Opt.Copy_Entity_txn_id = P_Copy_Entity_txn_id
371 and Opt.Table_Alias = 'OPT'
372 and Information256 = P_Scale_Cer_Id
373 and Nvl(Dml_Operation,'XX') <> 'DELETE';
374
375 Begin
376
377 For Stg_Rec In Csr_Stg_Grd_Scale
378 Loop
379
380 l_Scale_Delete := 'N';
381 l_Datetrack_Mode := P_Date_Track_Mode;
382
383 Open Csr_Hr_Scale(Stg_rec.Grade_id);
384 Fetch Csr_Hr_Scale Into L_Hr_parent_Spine, l_Hr_grade_Scale_id, L_Esd, l_Eed;
385 Close Csr_Hr_Scale;
386
387 If Nvl(Stg_Rec.Scale_id,-1) = Nvl(L_Hr_parent_Spine,-1) then
388
389 Open Csr_Del_Scle (Stg_rec.Scale_Cer_id);
390 Fetch Csr_Del_Scle into l_Scale_Delete;
391 If Csr_Del_Scle%FOUND Then
392 l_Scale_Delete := 'N';
393 l_Effective_Date := P_Effective_Date;
394 -- l_Datetrack_Mode := 'DELETE';
395 Else
396 l_Scale_Delete := 'Y';
397 l_Effective_Date := P_Effective_Date;
398 End If;
399 Close Csr_Del_Scle;
400 Else
401
402 L_ZAP := FALSE;
403 L_DELETE := FALSE;
404 L_FUTURE_CHANGE := FALSE;
405 L_DELETE_NEXT_CHANGE := FALSE;
406
407 l_Scale_Delete := 'Y';
408 l_Effective_Date := (P_Effective_Date - 1);
409
410 End If;
411
412 Dt_Api.FIND_DT_DEL_MODES
413 (P_EFFECTIVE_DATE => l_Effective_Date
414 ,P_BASE_TABLE_NAME => 'PER_GRADE_SPINES_F'
415 ,P_BASE_KEY_COLUMN => 'GRADE_SPINE_ID'
416 ,P_BASE_KEY_VALUE => l_Hr_grade_Scale_id
417 ,P_ZAP => L_ZAP
418 ,P_DELETE => L_DELETE
419 ,P_FUTURE_CHANGE => L_FUTURE_CHANGE
420 ,P_DELETE_NEXT_CHANGE => L_DELETE_NEXT_CHANGE);
421
422 If l_Datetrack_Mode = 'DELETE' then
423
424 IF L_DELETE THEN
425
426 l_Datetrack_Mode := 'DELETE';
427
428 ElsIf L_FUTURE_CHANGE Then
429
430 l_Datetrack_Mode := 'FUTURE_CHANGE';
431
432 ElsIf L_DELETE_NEXT_CHANGE Then
433
434 l_Datetrack_Mode := 'DELETE_NEXT_CHANGE';
435
436 Elsif L_ZAP Then
437
438 l_Datetrack_Mode := 'ZAP';
439
440 End If;
441
442 ElsIf l_Datetrack_Mode = 'ZAP' then
443
444 IF L_ZAP THEN
445
446 l_Datetrack_Mode := 'ZAP';
447
448 Elsif L_DELETE Then
449
450 l_Datetrack_Mode := 'DELETE';
451
452 ElsIf L_FUTURE_CHANGE Then
453
454 l_Datetrack_Mode := 'FUTURE_CHANGE';
455
456 ElsIf L_DELETE_NEXT_CHANGE Then
457
458 l_Datetrack_Mode := 'DELETE_NEXT_CHANGE';
459
460 End If;
461
462 End If;
463
464 If l_Datetrack_Mode = 'ZAP' then
465
466 Select Count(Placement_Id)
467 into l_Plcmt_Cnt
468 from Per_Spinal_POint_Placements_f
469 Where Step_Id in
470 (Select Step_Id
471 from Per_Spinal_Point_Steps_f
472 Where Grade_Spine_id = L_hr_Grade_Scale_id);
473
474 If l_Plcmt_Cnt <> 0 Then
475 l_Datetrack_Mode := 'DELETE';
476 End If;
477
478 End If;
479
480 If l_DateTrack_Mode is Not NULL then
481
482 -- If L_Scale_Delete = 'N' then
483 For Oipl_Rec in Csr_Del_Oipl(Stg_Rec.Copy_Entity_Result_id)
484 Loop
485 If Oipl_Rec.Step_id is NOT NULL Then
486
487 Open Csr_Step_Dtl(Oipl_Rec.Step_id);
488 Fetch Csr_Step_Dtl into l_Effective_Start_Date, l_Effective_End_Date, l_Step_Ovn;
489 If Csr_Step_Dtl%NOTFOUND Then
490 hr_utility.set_location('Invalid Step Id .. ',10);
491 Close Csr_Step_Dtl;
492 Return;
493 End If;
494 Close Csr_Step_Dtl;
495 hr_utility.set_location('Effective Date.. ' || l_Effective_Date,10);
496 hr_grade_step_api.delete_grade_step
497 (p_validate => FALSE
498 ,p_effective_date => L_effective_date
499 ,p_datetrack_mode => L_DateTrack_Mode
500 ,p_step_id => Oipl_Rec.Step_id
501 ,p_object_version_number => l_step_ovn
502 ,p_effective_start_date => l_Effective_Start_Date
503 ,p_effective_end_date => l_Effective_End_Date);
504
505 End If;
506 End Loop;
507 -- End if;
508
509 If L_Scale_Delete = 'Y' then
510 delete_grade_spine(L_hr_Grade_Scale_id,
511 L_effective_date,
512 L_Datetrack_Mode);
513 End If;
514 End If;
515 End Loop;
516
517 End Delete_Step;
518
519
520 Function Delete_Rate (p_copy_entity_txn_id in number,
521 p_effective_date in date)
522
523 Return Varchar2 Is
524
525
526 Cursor Csr_Pay_Rts is
527 Select Distinct Information293 Rt_Id
528 From Ben_Copy_Entity_results
529 Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
530 and Table_Alias = 'HRRATE'
531 and Dml_operation = 'DELETE';
532
533 Cursor Csr_Hr_Rt (P_Rat_Name_id IN Number) is
534 Select Information1,
535 Information2,
536 Information3,
537 Information298
538 From Ben_Copy_Entity_results
539 Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
540 and Table_Alias = 'HRRATE'
541 and Dml_operation = 'DELETE'
542 and Information293 = P_Rat_Name_Id;
543
544 Cursor Rt_Nam_Dtl (P_Rate_Id IN NUMBER) is
545 Select Rate_Id, Rate_type, Object_version_Number
546 From Pay_rates
547 Where Rate_Id = P_Rate_Id
548 and Not Exists
549 (Select 1
550 from Ben_Copy_Entity_results
551 Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
552 and Table_Alias = 'HRRATE'
553 and Nvl(Dml_operation,'XX') <> 'DELETE'
554 and Information293 = P_Rate_Id);
555
556 L_Hr_Rt_Esd Ben_Acty_Vrbl_Rt_F.Effective_Start_Date%TYPE;
557 L_Hr_Rt_Eed Ben_Acty_Vrbl_Rt_F.Effective_End_Date%TYPE;
558 l_Hr_RT_Ovn Ben_Acty_Vrbl_Rt_F.Object_version_Number%TYPE;
559
560 l_Rate_type Pay_Rates.Rate_Type%TYPE;
561 l_Rate_Ovn PAy_rates.Object_version_Number%TYPE;
562 l_rate_Id Pay_rates.Rate_Id%TYPE;
563 Begin
564
565 For Rt_Rec In Csr_Pay_Rts
566 Loop
567
568 For Hr_Rate_rec in Csr_Hr_Rt(Rt_Rec.Rt_Id)
569 Loop
570
571 hr_utility.set_location('Delete HR Rate' || Hr_Rate_Rec.Information1 ,20);
572 If Hr_Rate_Rec.Information1 is NOT NULL then
573
574 l_Hr_Rt_Ovn := Hr_rate_Rec.Information298;
575
576 Hr_Rate_Values_Api.DELETE_RATE_VALUE
577 (P_GRADE_RULE_ID => Hr_Rate_Rec.Information1
578 ,P_DATETRACK_MODE => 'ZAP'
579 ,P_EFFECTIVE_DATE => P_Effective_Date
580 ,P_OBJECT_VERSION_NUMBER => l_Hr_Rt_Ovn
581 ,P_EFFECTIVE_START_DATE => l_Hr_Rt_Esd
582 ,P_EFFECTIVE_END_DATE => l_Hr_Rt_Eed);
583
584 End If;
585 End Loop;
586
587 l_Rate_type := NULL;
588 L_rate_Ovn := NULL;
589 l_rate_Id := NULL;
590
591 Open Rt_Nam_Dtl (Rt_Rec.Rt_id);
592 Fetch Rt_Nam_Dtl into l_rate_Id, l_Rate_type, L_rate_Ovn;
593 Close Rt_Nam_Dtl;
594
595 hr_utility.set_location('Delete HR Rate Name ' || l_Rate_Id ,10);
596 If l_Rate_Id is NOT NULL Then
597
598 hr_rate_api.DELETE_RATE
599 (P_EFFECTIVE_DATE => P_Effective_Date
600 ,P_RATE_ID => l_Rate_Id
601 ,P_RATE_TYPE => l_rate_type
602 ,P_OBJECT_VERSION_NUMBER => L_Rate_Ovn);
603
604 End If;
605 End Loop;
606 Return 'SUCCESS';
607
608 Exception When Others Then
609 Return 'FAILURE';
610 End Delete_Rate;
611
612 Function delete_option (p_copy_entity_txn_id in number,
613 p_effective_date in date)
614
615 RETURN varchar2 is
616 --
617 Cursor csr_delete_opt is
618 Select information1 opt_id,
619 information2 effective_start_date,
620 information3 effective_end_date,
621 information4 business_group_id,
622 information265 opt_ovn
623 From ben_copy_entity_results
624 Where copy_entity_txn_id = p_copy_entity_txn_id
625 And table_alias = 'OPT'
626 And dml_operation ='DELETE';
627 -- And information104 = 'UNLINK';
628 --
629 Cursor csr_ben_opt (p_opt_id in Number) Is
630 Select Mapping_Table_Pk_Id
631 From Ben_opt_F
632 Where opt_id = p_opt_id
633 and p_effective_date
634 between Effective_Start_Date and Effective_End_Date
635 and Mapping_table_name = 'PER_SPINAL_POINTS';
636
637
638 Cursor Csr_Spinal_Point (P_Point_id IN Number) is
639 Select parent_spine_Id, Object_version_Number
640 from Per_Spinal_Points
641 Where Spinal_Point_Id = P_Point_id;
642
643 Cursor Csr_Step (P_Point_id IN Number) Is
644 Select Step_id
645 From Per_Spinal_Point_Steps_F
646 Where Spinal_Point_Id = P_Point_id;
647
648 Cursor Csr_Parent_Spine (P_Parent_Spine_Id IN Number) is
649 Select Object_Version_Number
650 from per_Parent_spines
651 Where Parent_spine_Id = P_Parent_Spine_Id;
652
653 --
654
655 l_Point_Id Per_Spinal_points.spinal_point_id%TYPE;
656 l_parent_Spine_id Per_Parent_Spines.Parent_Spine_Id%TYPE;
657 l_Step_id Per_Spinal_POint_Steps_F.Step_Id%TYPE;
658 l_Point_ovn Per_Spinal_Points.Object_Version_Number%TYPE;
659 L_Spinal_Cnt Number;
660 l_Prnt_ovn Per_Parent_Spines.Object_Version_Number%TYPE;
661 --
662 --
663 --
664 Begin
665 --
666 hr_utility.set_location('Entering: delete_option',5);
667 --
668 -- Select all the deleted opt rows.
669 --
670 For del_opt_rec in csr_delete_opt loop
671 --
672 -- When a point that is not used as step is brought to staging area, an option
673 -- is created for it anyway, if the option does not already exist.
674 -- When the point is deleted the option record will be marked
675 -- delete but theere will be not opt_id as there is no record in BEN.
676 --
677 --
678 If del_opt_rec.opt_id is not null then
679 --
680 -- Determine the date-tracked mode to use when deleting the row. If no date-tracked
681 -- mode is passed, the system will determine date-tracked mode to use when deleting
682 -- by reading actual BEN table rows.
683 --
684 --
685 Open csr_ben_opt(del_opt_rec.opt_id);
686 Fetch csr_ben_opt into l_point_Id;
687 Close csr_ben_opt;
688
689 Open Csr_Step (l_Point_Id);
690 Fetch Csr_Step into l_Step_id;
691 If Csr_Step%NOTFOUND then
692 --
693 Open Csr_Spinal_Point (l_point_Id);
694 Fetch Csr_Spinal_Point Into l_parent_Spine_id, l_Point_Ovn;
695 Close Csr_Spinal_Point;
696
697 hr_utility.set_location('Going to Delete Progression Points' || l_Point_Id ,10);
698
699 hr_progression_point_api.Delete_Progression_point
700 (P_SPINAL_POINT_ID => l_Point_Id,
701 P_OBJECT_VERSION_NUMBER => l_Point_Ovn);
702
703 hr_utility.set_location('Progression Points deleted' || l_Point_Id ,20);
704
705 Select Count(Spinal_Point_Id) into L_Spinal_Cnt
706 From Per_Spinal_Points
707 where Parent_Spine_Id = l_Parent_Spine_Id;
708
709 If l_Spinal_Cnt = 0 Then
710 -- Delete The Pay Scale also
711 Open Csr_Parent_Spine (l_Parent_Spine_Id);
712 Fetch Csr_Parent_Spine Into l_Prnt_Ovn;
713 Close Csr_Parent_Spine ;
714
715 hr_utility.set_location('PARENT SPINE TO delete' || l_Parent_Spine_Id ,30);
716
717 hr_pay_scale_api.DELETE_PAY_SCALE
718 (P_PARENT_SPINE_ID => l_Parent_Spine_Id
719 ,P_OBJECT_VERSION_NUMBER => l_Prnt_Ovn);
720
721 hr_utility.set_location('PARENT SPINE deleted' || l_Parent_Spine_Id ,30);
722
723 End If;
724 --
725 End if; -- Csr_ste
726 Close Csr_step;
727 --
728 End If;
729
730 End loop;
731 --
732 hr_utility.set_location('Leaving: delete_option',10);
733 --
734 Return 'SUCCESS';
735 --
736 Exception
737 When Others Then
738 hr_utility.set_location('Exception raised: delete_option',99);
739 Hr_Utility.Set_Location(Nvl(fnd_message.get,sqlerrm),100);
740 Return 'FAILURE';
741 End;
742
743 Function get_payrate(p_frequency in varchar2 default null,
744 p_business_group_id in number,
745 p_scale_id in number default null) return number is
746 l_rate_id number;
747 begin
748 if p_frequency is null and p_scale_id is null then
749 hr_utility.set_location('either freq or scale has to be there ',10);
750 elsif p_frequency is not null then
751 begin
752 select rt.rate_id
753 into l_rate_id
754 from hr_lookups lkp, pay_rates rt
755 where lkp.lookup_code = p_frequency
756 and rt.rate_type ='G'
757 and rt.business_group_id = p_business_group_id
758 and lkp.lookup_type = 'PQH_GSP_GEN_PAY_RATE_NAME'
759 and rt.name = lkp.meaning;
760 hr_utility.set_location('rate exists for freq '||l_rate_id,22);
761 exception
762 when no_data_found then
763 hr_utility.set_location('no pay rate exists for freq '||p_frequency,25);
764 return l_rate_id;
765 when others then
766 hr_utility.set_location('issues in selecting freq payrate ',30);
767 raise;
768 end;
769 elsif p_scale_id is not null then
770 begin
771 select rt.rate_id
772 into l_rate_id
773 from per_parent_spines scl, pay_rates rt
774 where rt.parent_spine_id = scl.parent_spine_id
775 and scl.parent_spine_id = p_scale_id
776 and rt.rate_type ='SP'
777 and rt.name = scl.name;
778 hr_utility.set_location('rate exists for scl '||l_rate_id,22);
779 exception
780 when no_data_found then
781 hr_utility.set_location('no pay rate exists for scl '||p_scale_id,25);
782 return l_rate_id;
783 when others then
784 hr_utility.set_location('issues in selecting scl payrate ',30);
785 raise;
786 end;
787 end if;
788 return l_rate_id;
789 end get_payrate;
790 procedure stage_to_prate(p_copy_entity_txn_id in number,
791 p_effective_date in date,
792 p_business_group_id in number,
793 p_gl_frequency in varchar2) is
794 l_grd_payrate_id number;
795 l_scl_payrate_id number;
796 cursor c1 is
797 select copy_entity_result_id,information1,information98
798 from ben_copy_entity_results Scl
799 where copy_entity_txn_id = p_copy_entity_txn_id
800 and table_alias = 'SCALE'
801 and Not Exists
802 (Select 1
803 from ben_copy_entity_results
804 where copy_entity_txn_id = p_copy_entity_txn_id
805 and table_alias = 'OPT'
806 and (information256 = Scl.Copy_Entity_Result_Id or information255 = scl.Information1)
807 and Dml_Operation = 'DELETE')
808 and Exists
809 (Select 1
810 from Ben_Copy_Entity_Results
811 Where copy_entity_txn_id = p_copy_entity_txn_id
812 and table_alias = 'HRRATE'
813 and dml_operation in ('INSERT','UPDATE'));
814
815 cursor c2(p_scl_cer_id number,p_scale_id number) is
816 select copy_entity_result_id,information1
817 from ben_copy_entity_results
818 where copy_entity_txn_id = p_copy_entity_txn_id
819 and table_alias = 'OPT'
820 and (information256 = p_scl_cer_id or information255 = p_scale_id);
821 l_ovn number;
822 begin
823 hr_utility.set_location('inside stage_to_payrate',10);
824 hr_utility.set_location('gl_freq is '||p_gl_frequency,10);
825 hr_utility.set_location('bg is '||p_business_group_id,10);
826 hr_utility.set_location('inside stage_to_payrate',10);
827 if p_gl_frequency is not null then
828 l_grd_payrate_id := get_payrate(p_frequency => p_gl_frequency,
829 p_business_group_id => p_business_group_id);
830 if l_grd_payrate_id is null then
831 hr_utility.set_location('create grd payrate',20);
832 pqh_gsp_utility.create_pay_rate
833 (p_business_group_id => p_business_group_id,
834 p_ldr_period_code => p_gl_frequency,
835 p_rate_id => l_grd_payrate_id,
836 p_ovn => l_ovn);
837 end if;
838 hr_utility.set_location('grd payrate is:'||l_grd_payrate_id,30);
839 begin
840 update ben_copy_entity_results
841 set information293 = l_grd_payrate_id
842 where copy_entity_txn_id = p_copy_entity_txn_id
843 and table_alias = 'HRRATE'
844 and information277 is not null
845 and information293 is null;
846 hr_utility.set_location('num of hrrs updated'||sql%rowcount,20);
847 exception
848 when others then
849 hr_utility.set_location('issues in updating hrrate',50);
850 raise;
851 end;
852 hr_utility.set_location('grd hrrate rows updated',30);
853 end if;
854 for i in c1 loop
855 if i.information1 is not null then
856 l_scl_payrate_id := get_payrate(p_scale_id => i.information1,
857 p_business_group_id => p_business_group_id);
858 if l_scl_payrate_id is null then
859 hr_utility.set_location('create scl payrate',20);
860 pqh_gsp_utility.create_pay_rate
861 (p_business_group_id => p_business_group_id,
862 p_scale_id => i.information1,
863 p_rate_name => i.information98,
864 p_rate_id => l_scl_payrate_id,
865 p_ovn => l_ovn);
866 end if;
867 hr_utility.set_location('scl payrate is:'||l_scl_payrate_id,20);
868 hr_utility.set_location('scl cer is:'||i.copy_entity_result_id,25);
869 for j in c2(i.copy_entity_result_id, i.information1) loop
870 -- get all the points for the scale
871 hr_utility.set_location('pt cer is:'||j.copy_entity_result_id,28);
872 begin
873 update ben_copy_entity_results
874 set information293 = l_scl_payrate_id
875 where copy_entity_txn_id = p_copy_entity_txn_id
876 and table_alias = 'HRRATE'
877 and information278 = j.copy_entity_result_id
878 and information293 is null;
879 hr_utility.set_location('num of hrrs updated'||sql%rowcount,20);
880 exception
881 when others then
882 hr_utility.set_location('issues in updating hrrate',50);
883 raise;
884 end;
885 end loop;
886 else
887 hr_utility.set_location('scl id is:'||i.information1,20);
888 end if;
889 end loop;
890 hr_utility.set_location('pt hrrate rows updated',30);
891 end stage_to_prate;
892 procedure pt_writeback(p_copy_entity_txn_id in number,
893 p_point_id in number,
894 p_point_cer_id in number) is
895 begin
896 hr_utility.set_location('pt writeback start for pt :'||p_point_id,10);
897 hr_utility.set_location('pt cer:'||p_point_cer_id,10);
898 begin
899 -- opt row is updated with point id
900 update ben_copy_entity_results
901 set information257 = p_point_id
902 where copy_entity_txn_id = p_copy_entity_txn_id
903 and table_alias ='OPT'
904 and copy_entity_result_id = p_point_cer_id;
905 hr_utility.set_location('num of opt updated'||sql%rowcount,20);
906 exception
907 when others then
908 hr_utility.set_location('issues in updating pt to opt',10);
909 raise;
910 end;
911 begin
912 -- oipl rows are to be updated with point id
913 update ben_copy_entity_results
914 set information256 = p_point_id
915 where table_alias = 'COP'
916 and copy_entity_txn_id = p_copy_entity_txn_id
917 and information262 = p_point_cer_id;
918 hr_utility.set_location('num of oipl updated'||sql%rowcount,20);
919 exception
920 when others then
921 hr_utility.set_location('issues in updating pt to oipl ',20);
922 raise;
923 end;
924 begin
925 -- hrrate rows are to be updated with point id
926 update ben_copy_entity_results
927 set information276 = p_point_id
928 where table_alias = 'HRRATE'
929 and copy_entity_txn_id = p_copy_entity_txn_id
930 and information278 = p_point_cer_id;
931 hr_utility.set_location('num of hrrs updated'||sql%rowcount,20);
932 exception
933 when others then
934 hr_utility.set_location('issues in updating pt to hrrate ',30);
935 raise;
936 end;
937 end pt_writeback;
938 procedure step_writeback(p_copy_entity_txn_id in number,
939 p_step_id in number,
940 p_step_cer_id in number,
941 p_effective_date in date) is
942 l_oipl_id number;
943 begin
944 hr_utility.set_location('step writeback start for step :'||p_step_id,10);
945 hr_utility.set_location('step cer:'||p_step_cer_id,10);
946 begin
947 l_oipl_id := pqh_gsp_hr_to_stage.get_oipl_for_step(p_step_id => p_step_id,
948 p_effective_date => p_effective_date);
949 exception
950 when others then
951 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
952 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
953 P_TXN_ID => p_copy_entity_txn_id,
954 p_context => 'pqh_gsp_hr_to_stage.get_oipl_for_step',
955 P_MODULE_CD => 'PQH_GSP_STGBEN',
956 P_MESSAGE_TYPE_CD => 'E',
957 P_MESSAGE_TEXT => sqlerrm,
958 p_effective_date => p_effective_date);
959 raise;
960 end;
961 begin
962 -- oipl row is updated with step id
963 update ben_copy_entity_results
964 set information253 = p_step_id,
965 information1 = nvl(information1,l_oipl_id)
966 where copy_entity_txn_id = p_copy_entity_txn_id
967 and table_alias ='COP'
968 and copy_entity_result_id = p_step_cer_id;
969 hr_utility.set_location('num of oipl updated'||sql%rowcount,20);
970 exception
971 when others then
972 hr_utility.set_location('issues in updating step to cop',10);
973 raise;
974 end;
975 end step_writeback;
976 procedure hrr_writeback(p_grade_cer_id in number,
977 p_point_cer_id in number,
978 p_copy_entity_txn_id in number,
979 p_hrrate_id in number) is
980 begin
981 begin
982 -- abr row is updated with grade spine id
983 update ben_copy_entity_results
984 set information266 = p_hrrate_id
985 where copy_entity_txn_id = p_copy_entity_txn_id
986 and table_alias ='ABR'
987 and (information277 is null or information277 = p_grade_cer_id)
988 and (information278 is null or information278 = p_point_cer_id);
989 hr_utility.set_location('num of abrs updated'||sql%rowcount,20);
990 exception
991 when others then
992 hr_utility.set_location('issues in updating hrrate to abr',10);
993 raise;
994 end;
995 end hrr_writeback;
996 procedure grd_sp_writeback(p_plip_cer_id in number,
997 p_grade_spine_id in number,
998 p_copy_entity_txn_id in number) is
999 begin
1000 begin
1001 -- oipl row is updated with grade spine id
1002 update ben_copy_entity_results
1003 set information280 = p_grade_spine_id
1004 where copy_entity_txn_id = p_copy_entity_txn_id
1005 and table_alias ='CPP'
1006 and copy_entity_result_id = p_plip_cer_id;
1007 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
1008 exception
1009 when others then
1010 hr_utility.set_location('issues in updating gs to plip',10);
1011 raise;
1012 end;
1013 begin
1014 -- oipl row is updated with grade spine id
1015 update ben_copy_entity_results
1016 set information255 = p_grade_spine_id
1017 where copy_entity_txn_id = p_copy_entity_txn_id
1018 and table_alias ='COP'
1019 and gs_parent_entity_result_id = p_plip_cer_id;
1020 hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
1021 exception
1022 when others then
1023 hr_utility.set_location('issues in updating gs to oipl',10);
1024 raise;
1025 end;
1026 end grd_sp_writeback;
1027 procedure scl_writeback(p_copy_entity_txn_id in number,
1028 p_scale_id in number,
1029 p_scale_cer_id in number) is
1030 begin
1031 begin
1032 -- scale row is updated with Scale id
1033 update ben_copy_entity_results
1034 set information1 = p_scale_id
1035 where copy_entity_txn_id = p_copy_entity_txn_id
1036 and table_alias ='SCALE'
1037 and copy_entity_result_id = p_scale_cer_id;
1038 hr_utility.set_location('num of scales updated'||sql%rowcount,20);
1039 exception
1040 when others then
1041 hr_utility.set_location('issues in updating sclid to scl',5);
1042 raise;
1043 end;
1044 begin
1045 -- plip row is updated with Scale id
1046 update ben_copy_entity_results
1047 set information255 = p_scale_id
1048 where copy_entity_txn_id = p_copy_entity_txn_id
1049 and table_alias ='CPP'
1050 and information258 = p_scale_cer_id;
1051 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
1052 exception
1053 when others then
1054 hr_utility.set_location('issues in updating scl to plip',10);
1055 raise;
1056 end;
1057 begin
1058 -- opt rows are to be updated with Scale id
1059 update ben_copy_entity_results
1060 set information255 = p_scale_id
1061 where table_alias = 'OPT'
1062 and copy_entity_txn_id = p_copy_entity_txn_id
1063 and information256 = p_scale_cer_id;
1064 hr_utility.set_location('num of opts updated'||sql%rowcount,20);
1065 exception
1066 when others then
1067 hr_utility.set_location('issues in updating scl to opt ',20);
1068 raise;
1069 end;
1070 begin
1071 -- oipl rows are to be updated with Scale id
1072 update ben_copy_entity_results
1073 set information260 = p_scale_id
1074 where table_alias = 'COP'
1075 and copy_entity_txn_id = p_copy_entity_txn_id
1076 and information259 = p_scale_cer_id;
1077 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
1078 exception
1079 when others then
1080 hr_utility.set_location('issues in updating scl to oipl ',30);
1081 raise;
1082 end;
1083 end scl_writeback;
1084 procedure grd_writeback(p_copy_entity_txn_id in number,
1085 p_grade_id in number,
1086 p_grade_cer_id in number) is
1087 begin
1088 hr_utility.set_location('writing back grd '||p_grade_id,10);
1089 hr_utility.set_location('writing back grdcer '||p_grade_cer_id,10);
1090 begin
1091 -- plip row is updated with Grade id
1092 update ben_copy_entity_results
1093 set information253 = p_grade_id
1094 where copy_entity_txn_id = p_copy_entity_txn_id
1095 and table_alias = 'CPP'
1096 and information252 = p_grade_cer_id;
1097 hr_utility.set_location('num of plips updated'||sql%rowcount,20);
1098 exception
1099 when others then
1100 hr_utility.set_location('issues in updating grd to plip',10);
1101 raise;
1102 end;
1103 begin
1104 -- plan row is updated with Grade id
1105 update ben_copy_entity_results
1106 set information223 = p_grade_id,
1107 information294 = p_grade_id
1108 where copy_entity_result_id = p_grade_cer_id;
1109 hr_utility.set_location('num of pl updated'||sql%rowcount,20);
1110 exception
1111 when others then
1112 hr_utility.set_location('issues in updating grd to pl',10);
1113 raise;
1114 end;
1115 begin
1116 -- hrrate row is to be updated with Grade id
1117 update ben_copy_entity_results
1118 set information255 = p_grade_id
1119 where table_alias = 'HRRATE'
1120 and copy_entity_txn_id = p_copy_entity_txn_id
1121 and information277 = p_grade_cer_id;
1122 hr_utility.set_location('num of hrate updated'||sql%rowcount,20);
1123 exception
1124 when others then
1125 hr_utility.set_location('issues in updating grd to hrrate',20);
1126 raise;
1127 end;
1128 end grd_writeback;
1129 function get_max_grd_seq(p_business_group_id in number) return number is
1130 l_max_seq number;
1131 begin
1132 select max(sequence) into l_max_seq
1133 from per_grades
1134 where business_group_id = p_business_group_id;
1135 l_max_seq := nvl(l_max_seq,0) +1;
1136 return l_max_seq;
1137 exception
1138 when no_data_found then
1139 hr_utility.set_location('no grd found ',10);
1140 return 0;
1141 when others then
1142 hr_utility.set_location('issues in getting max grd seq',20);
1143 raise;
1144 end get_max_grd_seq;
1145 function get_bg_for_cet(p_copy_entity_txn_id in number) return number is
1146 l_bg_id number;
1147 begin
1148 select context_business_group_id
1149 into l_bg_id
1150 from pqh_copy_entity_txns
1151 where copy_entity_txn_id = p_copy_entity_txn_id;
1152 return l_bg_id;
1153 exception
1154 when no_data_found then
1155 hr_utility.set_location('CET doesnot exist'||p_copy_entity_txn_id,10);
1156 raise;
1157 when others then
1158 hr_utility.set_location('issues in getting bg for CET ',20);
1159 raise;
1160 end get_bg_for_cet;
1161 function get_grd_segment(p_grade_id in number,
1162 p_grade_definition_id in number) return varchar2 is
1163 l_concat_segs varchar2(2000);
1164 begin
1165 -- logic needs to be written which will go in here
1166 return l_concat_segs;
1167 end get_grd_segment;
1168
1169 procedure pre_push_data(p_copy_entity_txn_id in number,
1170 p_effective_date in date,
1171 p_business_group_id in number,
1172 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
1173 P_Date_Track_Mode in Varchar2) is
1174 l_return varchar2(30);
1175 l_effective_date date;
1176 l_Del_Dt_Mode Varchar2(30);
1177 begin
1178 hr_utility.set_location('inside pre-push',1);
1179 If P_Date_Track_Mode = 'UPDATE_OVERRIDE' Then
1180 l_Del_Dt_Mode := 'DELETE';
1181 Else
1182 l_Del_Dt_Mode := 'ZAP';
1183 End If;
1184 begin
1185 select effective_date
1186 into l_effective_date
1187 from fnd_sessions
1188 where session_id = userenv('sessionid');
1189 update fnd_sessions
1190 set effective_date = p_effective_date
1191 where session_id = userenv('sessionid');
1192 exception
1193 when no_data_found then
1194 insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'), p_effective_date);
1195 when others then
1196 raise;
1197 end;
1198 hr_utility.set_location('effective date set',1);
1199 begin
1200 update ben_copy_entity_results
1201 set dml_operation = 'DELETE'
1202 where copy_entity_txn_id = p_copy_entity_txn_id
1203 and table_alias in ('COP','OPT','CPP','HRRATE','ABR')
1204 and information104 = 'UNLINK';
1205 hr_utility.set_location('num of unlinks updated'||sql%rowcount,2);
1206 exception
1207 when others then
1208 hr_utility.set_location('issues in marking recs for delete',1);
1209 raise;
1210 end;
1211 hr_utility.set_location('calling delete obj',1);
1212
1213 -- Delete HR Steps if any found. This will inturn Delete the OIPLs
1214
1215
1216 /* l_return := pqh_gsp_del_grade_ladder_obj.delete_from_ben
1217 (p_copy_entity_txn_id => p_copy_entity_txn_id,
1218 p_effective_date => p_effective_date,
1219 p_datetrack_mode => 'DELETE'); */
1220
1221 -- Unlink Eligibility Profiles
1222 l_return := pqh_gsp_del_grade_ladder_obj.unlink_elig_prfl(p_copy_entity_txn_id => p_copy_entity_txn_id,
1223 p_effective_date => p_effective_date,
1224 p_datetrack_mode => l_Del_Dt_Mode);
1225 If l_return = 'FAILURE' Then
1226
1227 fnd_message.set_name('PQH','PQH_GSP_BEN_DEL_FAILED');
1228 fnd_message.raise_error;
1229
1230 End If;
1231
1232 -- Unlink Plan In Programs
1233 l_return := pqh_gsp_del_grade_ladder_obj.unlink_plan_from_pgm (p_copy_entity_txn_id => p_copy_entity_txn_id,
1234 p_effective_date => p_effective_date,
1235 p_datetrack_mode => l_Del_Dt_Mode);
1236
1237
1238 If l_return = 'FAILURE' Then
1239
1240 fnd_message.set_name('PQH','PQH_GSP_BEN_DEL_FAILED');
1241 fnd_message.raise_error;
1242
1243 End If;
1244
1245 -- Delete option
1246
1247 Delete_Step(p_copy_entity_txn_id => p_copy_entity_txn_id,
1248 p_effective_date => p_effective_date,
1249 P_Date_Track_Mode => l_Del_Dt_Mode);
1250
1251 If l_Del_Dt_Mode = 'ZAP' Then
1252
1253 l_return := Delete_Rate (p_copy_entity_txn_id => p_copy_entity_txn_id,
1254 p_effective_date => p_effective_date);
1255
1256 l_return := delete_option (p_copy_entity_txn_id => p_copy_entity_txn_id,
1257 p_effective_date => p_effective_date);
1258
1259 If l_return = 'FAILURE' Then
1260
1261 fnd_message.set_name('PQH','PQH_GSP_BEN_DEL_FAILED');
1262 fnd_message.raise_error;
1263
1264 End If;
1265 End If;
1266 if p_business_area = 'PQH_GSP_TASK_LIST' THEN
1267
1268 pqh_gsp_hr_to_stage.create_payrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1269 p_effective_date => p_effective_date,
1270 p_business_group_id => p_business_group_id);
1271
1272 END IF;
1273 hr_utility.set_location('leaving pre-push',100);
1274 end pre_push_data;
1275
1276 procedure post_push_data(p_copy_entity_txn_id in number,
1277 p_effective_date in date,
1278 p_business_group_id in number,
1279 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1280 l_message_text varchar2(2000);
1281 begin
1282 hr_utility.set_location('inside post_data_push',10);
1283 /* l_message_text := 'cet id'||p_copy_entity_txn_id
1284 ||'business_group_id is '||p_business_group_id
1285 ||'effdt is '||to_char(p_effective_date,'dd-mm-RRRR')
1286 ||'bus area is '||p_business_area;
1287 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1288 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1289 P_TXN_ID => p_copy_entity_txn_id,
1290 p_context => 'inside_post_DATA_PUSH',
1291 P_MODULE_CD => 'PQH_GSP_STGBEN',
1292 P_MESSAGE_TYPE_CD => 'E',
1293 P_MESSAGE_TEXT => l_message_text,
1294 p_effective_date => p_effective_date); */
1295 pqh_gsp_hr_to_stage.update_gsp_control_rec
1296 (p_copy_entity_txn_id => p_copy_entity_txn_id,
1297 p_effective_date => p_effective_date,
1298 p_business_area => p_business_area);
1299 hr_utility.set_location('leaving post_data_push',10);
1300 end post_push_data;
1301 procedure gsp_data_push(p_copy_entity_txn_id in number,
1302 p_effective_date in date,
1303 p_business_group_id in number,
1304 p_datetrack_mode in varchar2,
1305 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1306 l_gl_currency varchar2(30);
1307 l_gl_freq varchar2(30);
1308 l_gl_name varchar2(80);
1309 l_datetrack_mode varchar2(30);
1310 l_continue varchar2(30) := 'Y';
1311 begin
1312 hr_utility.set_location('inside gsp_data_push',10);
1313 if p_datetrack_mode = 'CORR' then
1314 l_datetrack_mode := 'CORRECTION';
1315 elsif p_datetrack_mode = 'UPDATE' then
1316 l_datetrack_mode := 'UPDATE_OVERRIDE';
1317 else
1318 hr_utility.set_location('invalid dt mode passed',10);
1319 l_continue := 'N';
1320 end if;
1321 if l_continue = 'Y' then
1322 hr_utility.set_location('dt_mode set',10);
1323 begin
1324 hr_utility.set_location('going for pgm datapull',20);
1325 select information50,information41,substr(information5,1,80)
1326 into l_gl_currency,l_gl_freq,l_gl_name
1327 from ben_copy_entity_results
1328 where copy_entity_txn_id = p_copy_entity_txn_id
1329 and result_type_cd ='DISPLAY'
1330 and table_alias = 'PGM';
1331 hr_utility.set_location('curr and freq of pgm fetched',10);
1332 exception
1333 when others then
1334 hr_utility.set_location('issues in selecting pgm row ',10);
1335 raise;
1336 end;
1337 end if;
1338 if l_continue = 'Y' then
1339 PQH_GSP_PROCESS_LOG.START_LOG
1340 (P_TXN_ID => p_copy_entity_txn_id,
1341 P_TXN_NAME => l_gl_name||p_business_area,
1342 P_MODULE_CD => 'PQH_GSP_STGBEN');
1343 begin
1344
1345 pre_push_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1346 p_effective_date => p_effective_date,
1347 p_business_group_id => p_business_group_id,
1348 P_Date_Track_Mode => l_datetrack_mode);
1349
1350 hr_utility.set_location('pre push done ',20);
1351
1352 stage_to_hr(p_copy_entity_txn_id => p_copy_entity_txn_id,
1353 p_effective_date => p_effective_date,
1354 p_business_group_id => p_business_group_id,
1355 p_gl_currency => l_gl_currency,
1356 p_gl_frequency => l_gl_freq,
1357 p_gl_name => l_gl_name,
1358 p_datetrack_mode => l_datetrack_mode,
1359 p_business_area => p_business_area);
1360
1361 hr_utility.set_location('data pushed to hr ',20);
1362
1363 pqh_gsp_stage_to_ben.cre_update_elig_prfl(
1364 p_copy_entity_txn_id => p_copy_entity_txn_id
1365 ,p_effective_date => p_effective_date
1366 ,p_business_group_id => p_business_group_id);
1367
1368 hr_utility.set_location('Elpros created/updated',20);
1369
1370 pqh_gsp_stage_to_ben.stage_to_ben
1371 (p_copy_entity_txn_id => p_copy_entity_txn_id,
1372 p_effective_date => p_effective_date,
1373 p_business_group_id => p_business_group_id,
1374 p_datetrack_mode => l_datetrack_mode,
1375 p_business_area => p_business_area);
1376
1377 hr_utility.set_location('data pushed to ben ',20);
1378
1379 post_push_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
1380 p_effective_date => p_effective_date,
1381 p_business_group_id => p_business_group_id,
1382 p_business_area => p_business_area);
1383
1384 hr_utility.set_location('post data push done ',40);
1385
1386 begin
1387
1388 update pqh_copy_entity_txns
1389 set status ='COMPLETED'
1390 where copy_entity_txn_id = p_copy_entity_txn_id;
1391
1392 -- Purging the Copy Entity Txn record as it is no longer required --
1393
1394 Delete from Ben_Copy_Entity_Results
1395 where Copy_Entity_Txn_Id = p_copy_entity_txn_id
1396 and Table_Alias Not In ('PQH_GSP_TASK_LIST','PQH_CORPS_TASK_LIST');
1397
1398 hr_utility.set_location('txn stat chg to comp',40);
1399 exception
1400 when others then
1401 hr_utility.set_location('issues in updating cet row ',10);
1402 raise;
1403 end;
1404 PQH_PROCESS_BATCH_LOG.END_LOG;
1405 exception
1406 when others then
1407 hr_utility.set_location('issues in writing data ',10);
1408 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1409 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1410 P_TXN_ID => p_copy_entity_txn_id,
1411 p_context => 'GSP_DATA_PUSH',
1412 P_MODULE_CD => 'PQH_GSP_STGBEN',
1413 P_MESSAGE_TYPE_CD => 'E',
1414 P_MESSAGE_TEXT => sqlerrm,
1415 p_effective_date => p_effective_date);
1416
1417 PQH_PROCESS_BATCH_LOG.END_LOG;
1418 raise;
1419 end;
1420 end if;
1421 end gsp_data_push;
1422 procedure stage_to_hr(p_copy_entity_txn_id in number,
1423 p_effective_date in date,
1424 p_business_group_id in number,
1425 p_gl_currency in varchar2,
1426 p_gl_name in varchar2,
1427 p_gl_frequency in varchar2,
1428 p_datetrack_mode in varchar2,
1429 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1430 -- this procedure will be the callable routine and will be starting before
1431 -- stage_to_ben starts copying the data from staging table to ben tables
1432 -- in this procedure we will traverse the hierarchy and find out what all is
1433 -- hr data
1434 -- for any plan created/updated stage_to_grade
1435 -- for any option created/ updated stage_to_point
1436 -- for any oipl created/ updated stage_to_step
1437 -- for any standard rate created/ updated stage_to_hrate
1438 /* the data should be written in this order
1439 1) Grades
1440 2) Scales
1441 3) Points
1442 4) Grade spines
1443 5) Steps
1444 6) Rates
1445 */
1446 l_proc varchar2(61) := 'stage_to_hr' ;
1447 l_effective_date date := p_effective_date;
1448 begin
1449 hr_utility.set_location('inside '||l_proc,10);
1450 hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1451 hr_utility.set_location('bg is '||p_business_group_id,2);
1452 hr_utility.set_location('curr is '||p_gl_currency,3);
1453 hr_utility.set_location('dt mode is '||p_datetrack_mode,4);
1454 stage_to_grade(p_copy_entity_txn_id => p_copy_entity_txn_id,
1455 p_effective_date => l_effective_date,
1456 p_business_group_id => p_business_group_id);
1457 hr_utility.set_location('grade row checked for update',30);
1458 stage_to_scale(p_copy_entity_txn_id => p_copy_entity_txn_id,
1459 p_effective_date => l_effective_date,
1460 p_business_group_id => p_business_group_id,
1461 p_business_area => p_business_area);
1462 hr_utility.set_location('Scale row updated',40);
1463 stage_to_prate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1464 p_effective_date => l_effective_date,
1465 p_business_group_id => p_business_group_id,
1466 p_gl_frequency => p_gl_frequency);
1467 hr_utility.set_location('pay rates created if any reqd',41);
1468 stage_to_grd_sp(p_copy_entity_txn_id => p_copy_entity_txn_id,
1469 p_effective_date => l_effective_date,
1470 p_business_group_id => p_business_group_id,
1471 p_datetrack_mode => p_datetrack_mode);
1472 hr_utility.set_location('grade spine row updated',40);
1473 stage_to_point(p_copy_entity_txn_id => p_copy_entity_txn_id,
1474 p_effective_date => l_effective_date,
1475 p_business_group_id => p_business_group_id,
1476 p_business_area => p_business_area);
1477 hr_utility.set_location('option row updated',50);
1478 stage_to_step(p_copy_entity_txn_id => p_copy_entity_txn_id,
1479 p_effective_date => l_effective_date,
1480 p_business_group_id => p_business_group_id,
1481 p_datetrack_mode => p_datetrack_mode);
1482 hr_utility.set_location('oipl row updated',60);
1483 stage_to_hrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
1484 p_effective_date => l_effective_date,
1485 p_gl_currency => p_gl_currency,
1486 p_business_group_id => p_business_group_id,
1487 p_datetrack_mode => p_datetrack_mode);
1488 hr_utility.set_location('Hrate row updated',70);
1489 exception
1490 when others then
1491 hr_utility.set_location('error encountered',420);
1492 raise;
1493 end stage_to_hr;
1494 function get_grd_spine(p_grade_id in number,
1495 p_scale_id in number,
1496 p_effective_date in date) return number is
1497 l_grade_spine_id number;
1498 l_parent_spine_id number;
1499 begin
1500 begin
1501 select grade_spine_id,parent_spine_id
1502 into l_grade_spine_id,l_parent_spine_id
1503 from per_grade_spines_f
1504 where grade_id = p_grade_id
1505 and p_effective_date between effective_start_date and effective_end_date;
1506 hr_utility.set_location('grade_spine id is '||l_grade_spine_id,35);
1507 exception
1508 when no_data_found then
1509 hr_utility.set_location('grade is not attached to any scale ',40);
1510 when others then
1511 hr_utility.set_location('issues in getting grade_spine ',50);
1512 raise;
1513 end;
1514 if l_grade_spine_id is null then
1515 -- grade spine doesnot exist, return null to create new
1516 hr_utility.set_location('grade spine doesnot exist',50);
1517 return l_grade_spine_id;
1518 else
1519 if l_parent_spine_id = p_scale_id then
1520 -- grade is linked to same scale in db use the same grade spine
1521 hr_utility.set_location('grade is linked to same scale ',50);
1522 return l_grade_spine_id;
1523 else
1524 -- grade is linked to different scale in db , we have to delete this grade spine
1525 -- and steps before create this one.
1526 hr_utility.set_location('grade is linked to diff scale ',50);
1527 /* delete_steps(p_grade_spine_id => l_grade_spine_id,
1528 p_effective_date => p_effective_date);
1529 delete_grade_spine(p_grade_spine_id => l_grade_spine_id,
1530 p_effective_date => p_effective_date); */
1531 return null;
1532 end if;
1533 end if;
1534 end get_grd_spine;
1535 procedure stage_to_grd_sp(p_copy_entity_txn_id in number,
1536 p_effective_date in date,
1537 p_business_group_id in number,
1538 p_datetrack_mode in varchar2) is
1539 cursor csr_gsps is
1540 select *
1541 from ben_copy_entity_results
1542 where copy_entity_txn_id = p_copy_entity_txn_id
1543 and table_alias = 'CPP'
1544 and Dml_Operation <> 'DELETE';
1545 l_proc varchar2(61) := 'stage_to_grd_sp' ;
1546 l_grade_spine_id number;
1547 l_gs_ovn number;
1548 l_gs_esd date;
1549 l_gs_eed date;
1550 l_message_text varchar2(2000);
1551 l_ceiling_step_id number;
1552 l_scale_id number;
1553 l_grade_id number;
1554 l_grd_effstdt date; --DN code for BugId: 3242976
1555 l_starting_step number;
1556 l_db_ovn number;
1557 l_dt_mode varchar2(30);
1558 l_object varchar2(80);
1559
1560 begin
1561 hr_utility.set_location('inside '||l_proc,10);
1562 for grd_spine in csr_gsps loop
1563 l_ceiling_step_id := grd_spine.information259;
1564 l_starting_step := grd_spine.information228;
1565 l_gs_ovn := grd_spine.information281;
1566
1567 if grd_spine.information255 is null and grd_spine.information258 is not null then
1568 begin
1569 select information1
1570 into l_scale_id
1571 from ben_copy_entity_results
1572 where copy_entity_result_id = grd_spine.information258;
1573 exception
1574 when others then
1575 hr_utility.set_location('scale was created but deleted',10);
1576 end;
1577 else
1578 l_scale_id := grd_spine.information255;
1579 end if;
1580 if grd_spine.information253 is null and grd_spine.information252 is not null then
1581 select information223
1582 into l_grade_id
1583 from ben_copy_entity_results
1584 where copy_entity_result_id = grd_spine.information252;
1585 else
1586 l_grade_id := grd_spine.information253;
1587 end if;
1588 if l_grade_id is not null and l_scale_id is not null then
1589 l_grade_spine_id := get_grd_spine(p_grade_id => l_grade_id,
1590 p_scale_id => l_scale_id,
1591 p_effective_date => p_effective_date);
1592 end if;
1593 if l_grade_spine_id is null
1594 and grd_spine.information253 is not null
1595 and grd_spine.information255 is not null then
1596 hr_utility.set_location('going for cr ',30);
1597 hr_utility.set_location('grade is '||grd_spine.information253,30);
1598 hr_utility.set_location('scale is '||grd_spine.information255,30);
1599 hr_utility.set_location('ceiling step is '||l_ceiling_step_id,30);
1600 -- HM: Start code for BugId: 3928277
1601 -- If the payscale linked to grade is changed create grade spine as of effective date
1602 begin
1603 select grade_id
1604 into l_grade_id
1605 from per_grade_spines_f
1606 where grade_id = l_grade_id
1607 and rownum < 2 ;
1608 l_grd_effstdt := p_effective_date;
1609 exception
1610 when no_data_found then
1611 hr_utility.set_location('new payscale attached ',30);
1612 l_grd_effstdt := null; -- ggnanagu 115.48
1613 end;
1614 -- End code for BugId: 3928277
1615
1616 -- DN: Start code for BugId: 3242976
1617 if l_grd_effstdt is null then
1618 -- No Payscale is attached to grade previously
1619 begin
1620 select date_from
1621 into l_grd_effstdt
1622 from per_grades
1623 where grade_id = l_grade_id;
1624 exception
1625 when others then
1626 l_grd_effstdt := p_effective_date;
1627 end;
1628 end if;
1629 --End code for BugId: 3242976
1630 hr_grade_scale_api.create_grade_scale
1631 (p_effective_date => l_grd_effstdt --p_effective_date --DN code for BugId: 3242976
1632 ,p_business_group_id => p_business_group_id
1633 ,p_parent_spine_id => l_scale_id
1634 ,p_grade_id => l_grade_id
1635 ,p_ceiling_step_id => l_ceiling_step_id
1636 ,p_grade_spine_id => l_grade_spine_id
1637 ,p_effective_start_date => l_gs_esd
1638 ,p_effective_end_date => l_gs_eed
1639 ,p_object_version_number => l_gs_ovn
1640 ,p_starting_step => l_starting_step
1641 );
1642 elsif l_grade_spine_id is not null and grd_spine.information103 in ('Y','B','S') then
1643 hr_utility.set_location('grd_spine exists,ceiling step updated',10);
1644 begin
1645 hr_utility.set_location('going for upd',30);
1646 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn
1647 (p_table_name => 'PER_GRADE_SPINES_F',
1648 p_key_column_name => 'GRADE_SPINE_ID',
1649 p_key_column_value => l_grade_spine_id,
1650 p_effective_date => p_effective_date);
1651 hr_utility.set_location(' l_db_ovn is '||l_db_ovn,30);
1652 hr_utility.set_location(' l_gs_ovn is '||l_gs_ovn,30);
1653 if l_db_ovn <> l_gs_ovn then
1654 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','GSPINE');
1655 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1656 fnd_message.set_token('OBJECT ',l_object);
1657 fnd_message.set_token('OBJECT_NAME ',l_grade_spine_id);
1658 fnd_message.raise_error;
1659 else
1660 if p_datetrack_mode <> 'CORRECTION' then
1661 l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode
1662 (p_table_name => 'PER_GRADE_SPINES_F',
1663 p_key_column_name => 'GRADE_SPINE_ID',
1664 p_key_column_value => l_grade_spine_id,
1665 p_effective_date => p_effective_date);
1666 else
1667 l_dt_mode := p_datetrack_mode;
1668 end if;
1669 hr_utility.set_location('l_dt_mode is'||l_dt_mode,30);
1670 hr_grade_scale_api.update_grade_scale
1671 (
1672 p_effective_date => p_effective_date --l_grd_effstdt
1673 ,p_datetrack_mode => l_dt_mode
1674 ,p_grade_spine_id => l_grade_spine_id
1675 ,p_object_version_number => l_gs_ovn
1676 ,p_business_group_id => p_business_group_id
1677 ,p_parent_spine_id => l_scale_id
1678 ,p_grade_id => l_grade_id
1679 ,p_ceiling_step_id => l_ceiling_step_id
1680 ,p_starting_step => l_starting_step
1681 ,p_effective_start_date => l_gs_esd
1682 ,p_effective_end_date => l_gs_eed
1683 );
1684 end if ;
1685 exception
1686 when others then
1687 hr_utility.set_location('issue in update grade scale'||l_ceiling_step_id,23);
1688 hr_utility.set_location('for grd_sp '||l_grade_spine_id,23);
1689 raise;
1690 end;
1691
1692 /* if(grd_spine.information103 in ('Y','B')) then
1693 if grd_spine.information259 is not null then
1694 begin
1695 update per_grade_spines_f
1696 set ceiling_step_id = l_ceiling_step_id
1697 where grade_spine_id = l_grade_spine_id
1698 and p_effective_date between effective_start_date and effective_end_date;
1699 hr_utility.set_location('num of grd_sps updated'||sql%rowcount,20);
1700 exception
1701 when others then
1702 hr_utility.set_location('issue in upd ceil step'||l_ceiling_step_id,23);
1703 hr_utility.set_location('for grd_sp '||l_grade_spine_id,23);
1704 raise;
1705 end;
1706 end if;
1707 end if;
1708 if (grd_spine.information103 in ('S','B')) then
1709 hr_utility.set_location('grd_spine exists,starting step updated',10);
1710 if grd_spine.information228 is not null then
1711 begin
1712 update per_grade_spines_f
1713 set starting_step = l_starting_step
1714 where grade_spine_id = l_grade_spine_id
1715 and p_effective_date between effective_start_date and effective_end_date;
1716 hr_utility.set_location('num of grd_sps updated'||sql%rowcount,20);
1717 exception
1718 when others then
1719 hr_utility.set_location('issue in upd ceil step'||l_ceiling_step_id,23);
1720 hr_utility.set_location('for grd_sp '||l_grade_spine_id,23);
1721 raise;
1722 end;
1723 end if;
1724 end if;
1725 */
1726 elsif l_grade_spine_id is not null then
1727 hr_utility.set_location('grd_spine exists,no ceiling step changed',10);
1728 elsif l_grade_spine_id is null and l_scale_id is null and grd_spine.information258 is null then
1729 hr_utility.set_location('scale is not attached ',10);
1730 /* else
1731 l_message_text := 'grade spine is'||l_grade_spine_id
1732 ||' grade is'||l_grade_id
1733 ||' pl_cer_id is'||grd_spine.information252
1734 ||' scl_cer_id is'||grd_spine.information258
1735 ||' ceil_upd_flg is'||grd_spine.information103
1736 ||' dml_oper is'||grd_spine.dml_operation
1737 ||' ceiling step id is'||l_ceiling_step_id
1738 ||' scale is'||l_scale_id;
1739 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1740 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1741 P_TXN_ID => nvl(l_grade_spine_id,p_copy_entity_txn_id),
1742 P_MODULE_CD => 'PQH_GSP_STGBEN',
1743 p_context => 'GRADE_SPINE',
1744 P_MESSAGE_TYPE_CD => 'E',
1745 P_MESSAGE_TEXT => l_message_text,
1746 p_effective_date => p_effective_date); */
1747 end if;
1748 if l_grade_spine_id is not null then
1749 hr_utility.set_location('gs writeback'||l_grade_spine_id,60);
1750 grd_sp_writeback(p_plip_cer_id => grd_spine.copy_entity_result_id,
1751 p_grade_spine_id => l_grade_spine_id,
1752 p_copy_entity_txn_id => p_copy_entity_txn_id);
1753 hr_utility.set_location('grade spine done'||l_grade_spine_id,80);
1754 end if;
1755 end loop;
1756 hr_utility.set_location('out of gs loop'||l_proc,200);
1757 exception
1758 when others then
1759 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1760 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1761 P_TXN_ID => p_copy_entity_txn_id,
1762 P_MODULE_CD => 'PQH_GSP_STGBEN',
1763 p_context => 'GRADE-STEP',
1764 P_MESSAGE_TYPE_CD => 'E',
1765 P_MESSAGE_TEXT => 'Grade spine',
1766 p_effective_date => p_effective_date);
1767 raise;
1768 end stage_to_grd_sp;
1769 procedure stage_to_point(p_copy_entity_txn_id in number,
1770 p_business_group_id in number,
1771 p_effective_date in date,
1772 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
1773 l_proc varchar2(61) :='stage_to_point';
1774 l_point_id number;
1775 l_scale_id number;
1776 l_point_ovn number;
1777 l_db_ovn number;
1778 l_object varchar2(80);
1779 l_message_text varchar2(2000);
1780 cursor csr_points is
1781 select *
1782 from ben_copy_entity_results
1783 where copy_entity_txn_id = p_copy_entity_txn_id
1784 and table_alias = 'OPT'
1785 and dml_operation in ('INSERT','UPDATE','UPD_INS')
1786 order by information253 desc; -- do highest seq. first
1787 begin
1788 hr_utility.set_location('inside '||l_proc,10);
1789 for point_rec in csr_points loop
1790 l_point_id := point_rec.information257;
1791 l_point_ovn := point_rec.information254;
1792 if point_rec.information255 is null and point_rec.information256 is not null then
1793 begin
1794 select information1
1795 into l_scale_id
1796 from ben_copy_entity_results
1797 where copy_entity_result_id = point_rec.information256;
1798 exception
1799 when others then
1800 hr_utility.set_location('scale created but removed',10);
1801 end;
1802 elsif point_rec.information255 is not null then
1803 l_scale_id := point_rec.information255;
1804 end if;
1805 if point_rec.dml_operation ='INSERT'
1806 and point_rec.information257 is null
1807 and l_scale_id is not null then
1808 hr_utility.set_location('going for ins',20);
1809 if p_business_area = 'PQH_CORPS_TASK_LIST' then
1810 pqh_cpd_hr_to_stage.create_point(p_point_id => l_point_id,
1811 p_point_ovn => l_point_ovn,
1812 p_information_category => point_rec.information101,
1813 p_information1 => point_rec.information173,
1814 p_information2 => point_rec.information175,
1815 p_information3 => point_rec.information179,
1816 p_information4 => point_rec.information181,
1817 p_information5 => point_rec.information182,
1818 p_effective_date => p_effective_date,
1819 p_business_group_id => p_business_group_id,
1820 p_parent_spine_id => l_scale_id,
1821 p_sequence => point_rec.information253,
1822 p_spinal_point => point_rec.information98);
1823 else
1824 hr_progression_point_api.create_progression_point
1825 (p_effective_date => p_effective_date
1826 ,p_business_group_id => p_business_group_id
1827 ,p_parent_spine_id => l_scale_id
1828 ,p_sequence => point_rec.information253
1829 ,p_spinal_point => point_rec.information98
1830 ,p_spinal_point_id => l_point_id
1831 ,p_object_version_number => l_point_ovn
1832 );
1833 end if;
1834 hr_utility.set_location('ins done '||l_point_id,22);
1835 pt_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1836 p_point_id => l_point_id,
1837 p_point_cer_id => point_rec.copy_entity_result_id);
1838 hr_utility.set_location('wrt_back done '||l_point_id,25);
1839 elsif point_rec.dml_operation in ('UPDATE','UPD_INS')
1840 and point_rec.information257 is not null
1841 and point_rec.information255 is not null then
1842 hr_utility.set_location('going for upd',30);
1843 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn
1844 (p_table_name => 'PER_SPINAL_POINTS',
1845 p_key_column_name => 'SPINAL_POINT_ID',
1846 p_key_column_value => l_point_id);
1847 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1848 if l_db_ovn <> l_point_ovn then
1849 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','POINT');
1850 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1851 fnd_message.set_token('OBJECT ',l_object);
1852 fnd_message.set_token('OBJECT_NAME ',point_rec.information98);
1853 fnd_message.raise_error;
1854 else
1855 if p_business_area = 'PQH_CORPS_TASK_LIST' then
1856 pqh_cpd_hr_to_stage.update_point(p_point_id => l_point_id,
1857 p_point_ovn => l_point_ovn,
1858 p_information_category => point_rec.information101,
1859 p_information1 => point_rec.information173,
1860 p_information2 => point_rec.information175,
1861 p_information3 => point_rec.information179,
1862 p_information4 => point_rec.information181,
1863 p_information5 => point_rec.information182,
1864 p_effective_date => p_effective_date,
1865 p_business_group_id => p_business_group_id,
1866 p_parent_spine_id => l_scale_id,
1867 p_sequence => point_rec.information253,
1868 p_spinal_point => point_rec.information98);
1869 else
1870 hr_progression_point_api.update_progression_point
1871 (p_effective_date => p_effective_date
1872 ,p_business_group_id => p_business_group_id
1873 ,p_parent_spine_id => l_scale_id
1874 ,p_sequence => point_rec.information253
1875 ,p_spinal_point => point_rec.information98
1876 ,p_spinal_point_id => l_point_id
1877 ,p_object_version_number => l_point_ovn
1878 );
1879 end if;
1880 hr_utility.set_location('upd done ',32);
1881 end if;
1882 else
1883 l_message_text := 'invalid dml_oper is '||point_rec.dml_operation
1884 ||' point id is '||l_point_id
1885 ||' point ovn is '||l_point_ovn
1886 ||' point seq is '||point_rec.information253
1887 ||' point name is '||point_rec.information98
1888 ||' business_area is '||p_business_area
1889 ||' scale id is '||l_scale_id;
1890 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1891 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1892 P_TXN_ID => nvl(l_point_id,p_copy_entity_txn_id),
1893 P_MODULE_CD => 'PQH_GSP_STGBEN',
1894 p_context => 'POINT',
1895 P_MESSAGE_TYPE_CD => 'E',
1896 P_MESSAGE_TEXT => l_message_text,
1897 p_effective_date => p_effective_date);
1898 end if;
1899 end loop;
1900 hr_utility.set_location('leaving '||l_proc,100);
1901 exception
1902 when others then
1903 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
1904 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
1905 P_TXN_ID => p_copy_entity_txn_id,
1906 P_MODULE_CD => 'PQH_GSP_STGBEN',
1907 p_context => 'POINT',
1908 P_MESSAGE_TYPE_CD => 'E',
1909 P_MESSAGE_TEXT => 'Point',
1910 p_effective_date => p_effective_date);
1911 raise;
1912 end stage_to_point;
1913 Procedure stage_to_grade(p_copy_entity_txn_id in number,
1914 p_business_group_id in number,
1915 p_effective_date in date) is
1916 l_proc varchar2(61) :='stage_to_grade';
1917 l_grade_id number;
1918 l_grd_seq number;
1919 l_db_ovn number;
1920 l_ovn number;
1921 l_object varchar2(80);
1922 l_concat_segments varchar2(600);
1923 l_message_text varchar2(2000);
1924 cursor csr_grades is
1925 select *
1926 from ben_copy_entity_results
1927 where copy_entity_txn_id = p_copy_entity_txn_id
1928 and table_alias = 'PLN'
1929 and dml_operation in ('INSERT','UPDATE','UPD_INS');
1930 begin
1931 hr_utility.set_location('inside'||l_proc,10);
1932 for grd_rec in csr_grades loop
1933 l_ovn := grd_rec.information222;
1934 l_grade_id := grd_rec.information223;
1935 if grd_rec.dml_operation = 'INSERT'
1936 and l_grade_id is null
1937 and grd_rec.information221 is not null
1938 and grd_rec.information5 is not null then
1939 hr_utility.set_location('new grade is being created'||l_proc,20);
1940 if l_grd_seq is null then
1941 l_grd_seq := get_max_grd_seq(p_business_group_id => p_business_group_id);
1942 else
1943 l_grd_seq := l_grd_seq + 1;
1944 end if;
1945 begin
1946 hr_utility.set_location('grade name'||substr(grd_rec.information5,1,45),20);
1947 hr_utility.set_location('grade seq'||l_grd_seq,20);
1948 hr_utility.set_location('date from'||to_char(grd_rec.information307,'DD/MM/RRRR'),20);
1949 hr_utility.set_location('date to'||to_char(grd_rec.information308,'DD/MM/RRRR'),20);
1950 hr_grade_api.create_grade(p_business_group_id => p_business_group_id
1951 ,p_date_from => grd_rec.information307
1952 ,p_sequence => l_grd_seq
1953 ,p_effective_date => p_effective_date
1954 ,p_date_to => grd_rec.information308
1955 ,p_short_name => grd_rec.information102
1956 ,p_grade_id => l_grade_id
1957 ,p_object_version_number => l_ovn
1958 ,p_grade_definition_id => grd_rec.information221
1959 ,p_name => grd_rec.information5);
1960 hr_utility.set_location('grade id'||l_grade_id,20);
1961 exception
1962 when others then
1963 hr_utility.set_location('issues in creating grade'||grd_rec.information5,30);
1964 raise;
1965 end;
1966 hr_utility.set_location('grade id is '||l_grade_id,30);
1967 hr_utility.set_location('grade cer id is '||grd_rec.copy_entity_result_id,30);
1968 grd_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
1969 p_grade_id => l_grade_id,
1970 p_grade_cer_id => grd_rec.copy_entity_result_id);
1971 hr_utility.set_location('grade writeback comp '||l_grade_id,40);
1972 elsif grd_rec.dml_operation in ('UPDATE','UPD_INS')
1973 and l_ovn is not null
1974 and grd_rec.information221 is not null
1975 and grd_rec.information5 is not null
1976 and l_grade_id is not null then
1977 hr_utility.set_location('grade is being updated'||l_grade_id,60);
1978 hr_utility.set_location('grade ovn'||grd_rec.information222,60);
1979 l_concat_segments := get_grd_segment(p_grade_id => l_grade_id,
1980 p_grade_definition_id => grd_rec.information221);
1981 hr_utility.set_location('con seg is'||substr(l_concat_segments,1,55),61);
1982 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn
1983 (p_table_name => 'PER_GRADES',
1984 p_key_column_name => 'GRADE_ID',
1985 p_key_column_value => l_grade_id);
1986 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1987 if l_db_ovn <> l_ovn then
1988 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','GRADE');
1989 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1990 fnd_message.set_token('OBJECT ',l_object);
1991 fnd_message.set_token('OBJECT_NAME ',grd_rec.information5);
1992 fnd_message.raise_error;
1993 else
1994 begin
1995 hr_grade_api.update_grade(p_date_from => grd_rec.information307
1996 ,p_effective_date => p_effective_date
1997 ,p_date_to => grd_rec.information308
1998 ,p_short_name => grd_rec.information102
1999 ,p_grade_id => l_grade_id
2000 ,p_object_version_number => l_ovn
2001 ,p_concat_segments => l_concat_segments
2002 ,p_grade_definition_id => grd_rec.information221
2003 ,p_name => grd_rec.information5);
2004 exception
2005 when others then
2006 hr_utility.set_location('issues in updating grade'||l_grade_id,70);
2007 hr_utility.set_location('grade ovn'||l_ovn,75);
2008 hr_utility.set_location('grade name'||substr(grd_rec.information5,1,45),78);
2009 raise;
2010 end;
2011 end if;
2012 else
2013 l_message_text := 'invalid operation '||grd_rec.dml_operation
2014 ||' grd_id'||l_grade_id
2015 ||' grd_ovn'||l_ovn
2016 ||' grd_def_id'||grd_rec.information221
2017 ||' grd_name'||grd_rec.information5;
2018 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2019 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2020 P_TXN_ID => nvl(l_grade_id,p_copy_entity_txn_id),
2021 P_MODULE_CD => 'PQH_GSP_STGBEN',
2022 p_context => 'GRADE',
2023 P_MESSAGE_TYPE_CD => 'E',
2024 P_MESSAGE_TEXT => l_message_text,
2025 p_effective_date => p_effective_date);
2026 end if;
2027 end loop;
2028 hr_utility.set_location('leaving '||l_proc,100);
2029 exception
2030 when others then
2031 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2032 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2033 P_TXN_ID => p_copy_entity_txn_id,
2034 P_MODULE_CD => 'PQH_GSP_STGBEN',
2035 p_context => 'GRADE',
2036 P_MESSAGE_TYPE_CD => 'E',
2037 P_MESSAGE_TEXT => 'Grade',
2038 p_effective_date => p_effective_date);
2039 raise;
2040 end stage_to_grade;
2041 procedure stage_to_step(p_copy_entity_txn_id in number,
2042 p_business_group_id in number,
2043 p_effective_date in date,
2044 p_datetrack_mode in varchar2) is
2045 l_proc varchar2(61) :='stage_to_step';
2046 l_step_id number;
2047 l_step_ovn number;
2048 l_step_seq number;
2049 l_step_esd date;
2050 l_step_eed date;
2051 l_grd_sp_id number;
2052 l_point_id number;
2053 l_message_text varchar2(2000);
2054 l_grd_effstdt date; --DN code for BugId: 3242976
2055 cursor csr_steps is
2056 select *
2057 from ben_copy_entity_results
2058 where copy_entity_txn_id = p_copy_entity_txn_id
2059 and table_alias = 'COP'
2060 and dml_operation = 'INSERT';
2061 begin
2062 hr_utility.set_location('inside '||l_proc,10);
2063 for step_rec in csr_steps loop
2064 l_step_id := step_rec.information253;
2065 l_step_ovn := step_rec.information254;
2066 if step_rec.information255 is null then
2067 select information280
2068 into l_grd_sp_id
2069 from ben_copy_entity_results
2070 where copy_entity_result_id = step_rec.gs_parent_entity_result_id;
2071 else
2072 l_grd_sp_id := step_rec.information255;
2073 end if;
2074 if step_rec.information256 is null and step_rec.information262 is not null then
2075 select information257
2076 into l_point_id
2077 from ben_copy_entity_results
2078 where copy_entity_result_id = step_rec.information262;
2079 else
2080 l_point_id := step_rec.information256;
2081 end if;
2082 if step_rec.dml_operation ='INSERT'
2083 and step_rec.information253 is null
2084 and step_rec.information104 = 'LINK'
2085 and l_grd_sp_id is not null
2086 and l_point_id is not null then
2087 hr_utility.set_location('going for ins',20);
2088 hr_utility.set_location('sequence '||step_rec.information263,20);
2089 hr_utility.set_location('point id is '||l_point_id,20);
2090 hr_utility.set_location('grade spine'||l_grd_sp_id,20);
2091 --DN: Start code for BugId: 3242976
2092 -- Create step as of grade spine start date
2093 begin
2094 SELECT pgs.EFFECTIVE_START_DATE
2095 INTO l_grd_effstdt
2096 FROM per_grade_spines_f pgs
2097 WHERE pgs.grade_spine_id = l_grd_sp_id
2098 AND p_effective_date BETWEEN pgs.effective_start_date
2099 AND pgs.effective_end_date;
2100 exception
2101 WHEN OTHERS THEN
2102 l_grd_effstdt := p_effective_date;
2103 end;
2104 --End code for BugId: 3242976
2105 hr_grade_step_api.create_grade_step
2106 (p_effective_date => l_grd_effstdt --p_effective_date --DN code for BugId: 3242976
2107 ,p_business_group_id => p_business_group_id
2108 ,p_effective_start_date => l_step_esd
2109 ,p_effective_end_date => l_step_eed
2110 ,p_grade_spine_id => l_grd_sp_id
2111 ,p_sequence => step_rec.information263
2112 ,p_spinal_point_id => l_point_id
2113 ,p_step_id => l_step_id
2114 ,p_object_version_number => l_step_ovn
2115 );
2116 hr_utility.set_location('ins done '||l_step_id,22);
2117 step_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2118 p_step_id => l_step_id,
2119 p_step_cer_id => step_rec.copy_entity_result_id,
2120 p_effective_date => p_effective_date);
2121 hr_utility.set_location('step_writeback done',22);
2122 if nvl(step_rec.information98,'N') = 'Y' and l_step_id is not null then
2123 hr_utility.set_location('ceiling step, update grd_sp',23);
2124 begin
2125 update per_grade_spines_f
2126 set ceiling_step_id = l_step_id
2127 where grade_spine_id = l_grd_sp_id
2128 and p_effective_date between effective_start_date and effective_end_date;
2129 hr_utility.set_location('num of grd_sps updated'||sql%rowcount,20);
2130 exception
2131 when others then
2132 hr_utility.set_location('issue in upd ceil step'||l_step_id,23);
2133 hr_utility.set_location('for grd_sp '||l_grd_sp_id,23);
2134 raise;
2135 end;
2136 else
2137 hr_utility.set_location('not a ceiling step',24);
2138 end if;
2139 else
2140 l_message_text := 'invalid operation '||step_rec.dml_operation
2141 ||' step id is '||step_rec.information253
2142 ||' point id is '||l_point_id
2143 ||' grade_spine id is '||l_grd_sp_id
2144 ||' link flag is '||step_rec.information104;
2145 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2146 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2147 P_TXN_ID => nvl(l_step_id,p_copy_entity_txn_id),
2148 P_MODULE_CD => 'PQH_GSP_STGBEN',
2149 p_context => 'STEP',
2150 P_MESSAGE_TYPE_CD => 'E',
2151 P_MESSAGE_TEXT => l_message_text,
2152 p_effective_date => p_effective_date);
2153 end if;
2154 end loop;
2155 hr_utility.set_location('leaving '||l_proc,100);
2156 exception
2157 when others then
2158 hr_utility.set_location('issues with steps '||l_proc,420);
2159 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2160 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2161 P_TXN_ID => p_copy_entity_txn_id,
2162 P_MODULE_CD => 'PQH_GSP_STGBEN',
2163 p_context => 'STEP',
2164 P_MESSAGE_TYPE_CD => 'E',
2165 P_MESSAGE_TEXT => 'STEP',
2166 p_effective_date => p_effective_date);
2167 raise;
2168 end stage_to_step;
2169 Procedure stage_to_scale(p_copy_entity_txn_id in number,
2170 p_business_group_id in number,
2171 p_effective_date in date,
2172 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
2173 l_proc varchar2(61) :='scale_to_stage';
2174 l_scale_id number;
2175 l_object varchar2(80);
2176 l_scale_ovn number;
2177 l_db_ovn number;
2178 l_message_text varchar2(2000);
2179 cursor csr_scales is
2180 select *
2181 from ben_copy_entity_results
2182 where copy_entity_txn_id = p_copy_entity_txn_id
2183 and table_alias ='SCALE'
2184 and dml_operation in ('INSERT','UPDATE');
2185 begin
2186 hr_utility.set_location('inside'||l_proc,10);
2187 for scl_rec in csr_scales loop
2188 l_scale_ovn := scl_rec.information254;
2189 l_scale_id := scl_rec.information1;
2190 if scl_rec.dml_operation = 'INSERT'
2191 and l_scale_id is null
2192 and scl_rec.information98 is not null then
2193 hr_utility.set_location('new scale is being created'||l_proc,20);
2194 hr_utility.set_location('incr_fre'||scl_rec.information253,20);
2195 hr_utility.set_location('incr_per'||scl_rec.information99,20);
2196 hr_utility.set_location('scale_id'||l_scale_id,20);
2197 hr_utility.set_location('scl_ovn'||l_scale_ovn,20);
2198 hr_utility.set_location('bus_area is'||p_business_area,20);
2199 if p_business_area = 'PQH_CORPS_TASK_LIST' then
2200 pqh_cpd_hr_to_stage.create_scale(p_scale_id => l_scale_id,
2201 p_scale_ovn => l_scale_ovn,
2202 p_information_category => scl_rec.information101,
2203 p_information1 => scl_rec.information112,
2204 p_information2 => scl_rec.information113,
2205 p_business_group_id => p_business_group_id,
2206 p_name => scl_rec.information98,
2207 p_effective_date => p_effective_date ,
2208 p_increment_frequency => scl_rec.information253,
2209 p_increment_period => scl_rec.information99);
2210 else
2211 begin
2212 hr_pay_scale_api.create_pay_scale
2213 (p_business_group_id => p_business_group_id
2214 ,p_name => scl_rec.information98
2215 ,p_effective_date => p_effective_date
2216 ,p_increment_frequency => scl_rec.information253
2217 ,p_increment_period => scl_rec.information99
2218 ,p_parent_spine_id => l_scale_id
2219 ,p_object_version_number => l_scale_ovn
2220 ) ;
2221 exception
2222 when others then
2223 hr_utility.set_location('issues in creating scale'||scl_rec.information98,30);
2224 raise;
2225 end;
2226 end if;
2227 hr_utility.set_location('scale id is '||l_scale_id,30);
2228 scl_writeback(p_copy_entity_txn_id => p_copy_entity_txn_id,
2229 p_scale_id => l_scale_id,
2230 p_scale_cer_id => scl_rec.copy_entity_result_id);
2231 hr_utility.set_location('scale writeback comp'||l_scale_id,40);
2232 elsif scl_rec.dml_operation ='UPDATE' and l_scale_id is not null
2233 and scl_rec.information98 is not null and l_scale_ovn is not null then
2234 hr_utility.set_location('scale is being updated'||l_proc,60);
2235 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn
2236 (p_table_name => 'PER_PARENT_SPINES',
2237 p_key_column_name => 'PARENT_SPINE_ID',
2238 p_key_column_value => l_scale_id);
2239 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2240 if l_db_ovn <> l_scale_ovn then
2241 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','SCALE');
2242 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2243 fnd_message.set_token('OBJECT ',l_object);
2244 fnd_message.set_token('OBJECT_NAME ',scl_rec.information98);
2245 fnd_message.raise_error;
2246 else
2247 if p_business_area = 'PQH_CORPS_TASK_LIST' then
2248 pqh_cpd_hr_to_stage.update_scale(p_scale_id => l_scale_id,
2249 p_scale_ovn => l_scale_ovn,
2250 p_information_category => scl_rec.information101,
2251 p_information1 => scl_rec.information112,
2252 p_information2 => scl_rec.information113,
2253 p_business_group_id => p_business_group_id,
2254 p_name => scl_rec.information98,
2255 p_effective_date => p_effective_date ,
2256 p_increment_frequency => scl_rec.information253,
2257 p_increment_period => scl_rec.information99);
2258 else
2259 begin
2260 hr_pay_scale_api.update_pay_scale
2261 (p_name => scl_rec.information98
2262 ,p_increment_frequency => scl_rec.information253
2263 ,p_increment_period => scl_rec.information99
2264 ,p_parent_spine_id => l_scale_id
2265 ,p_object_version_number => l_scale_ovn
2266 ) ;
2267 exception
2268 when others then
2269 hr_utility.set_location('issues in updating scale'||l_scale_id,70);
2270 hr_utility.set_location('scale ovn'||l_scale_ovn,75);
2271 hr_utility.set_location('scale name'||substr(scl_rec.information98,1,45),78);
2272 raise;
2273 end;
2274 end if;
2275 end if;
2276 else
2277 l_message_text := 'invalid operation '||scl_rec.dml_operation
2278 ||' scale id is '||l_scale_id
2279 ||' scale ovn is '||l_scale_ovn
2280 ||' scale name'||scl_rec.information98;
2281 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2282 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2283 P_TXN_ID => nvl(l_scale_id,p_copy_entity_txn_id),
2284 P_MODULE_CD => 'PQH_GSP_STGBEN',
2285 p_context => 'SCALE',
2286 P_MESSAGE_TYPE_CD => 'E',
2287 P_MESSAGE_TEXT => l_message_text,
2288 p_effective_date => p_effective_date);
2289 end if;
2290 end loop;
2291 hr_utility.set_location('leaving '||l_proc,420);
2292 exception
2293 when others then
2294 hr_utility.set_location('issue in scale writing'||l_proc,520);
2295 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2296 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2297 P_TXN_ID => p_copy_entity_txn_id,
2298 P_MODULE_CD => 'PQH_GSP_STGBEN',
2299 p_context => 'SCALE',
2300 P_MESSAGE_TYPE_CD => 'E',
2301 P_MESSAGE_TEXT => 'SCALE',
2302 p_effective_date => p_effective_date);
2303 raise;
2304 end stage_to_scale;
2305 procedure stage_to_hrate(p_copy_entity_txn_id in number,
2306 p_business_group_id in number,
2307 p_gl_currency in varchar2,
2308 p_effective_date in date,
2309 p_datetrack_mode in varchar2) is
2310 l_proc varchar2(61) :='stage_to_hrate';
2311 l_payrate_id number;
2312 l_hrrate_id number;
2313 l_old_grd_cer_id number;
2314 l_old_pnt_cer_id number;
2315 l_old_hrr_id number;
2316 l_hrr_ovn number;
2317 l_db_ovn number;
2318 l_old_hrr_ovn number;
2319 l_grd_sp_id number;
2320 l_rate_type varchar2(30);
2321 l_dt_mode varchar2(30);
2322 l_effective_date date;
2323 l_hrr_esd date;
2324 l_hrr_eed date;
2325 l_object varchar2(80);
2326 l_message_text varchar2(2000);
2327 l_dml_operation varchar2(30);
2328 cursor csr_hrr is
2329 select *
2330 from ben_copy_entity_results
2331 where copy_entity_txn_id = p_copy_entity_txn_id
2332 and table_alias = 'HRRATE'
2333 and dml_operation in ('INSERT','UPDATE')
2334 order by INFORMATION277,INFORMATION278,INFORMATION2;
2335 begin
2336 hr_utility.set_location('inside hr rate ',10);
2337 for hrr_rec in csr_hrr loop
2338 hr_utility.set_location('hrrate cer is'||hrr_rec.information1,15);
2339 if hrr_rec.information1 is null then
2340 if (hrr_rec.information277 is null or hrr_rec.information277 = l_old_grd_cer_id)
2341 and (hrr_rec.information278 is null or hrr_rec.information278 = l_old_pnt_cer_id) then
2342 hr_utility.set_location('reusing prev row pk and ovn',16);
2343 hr_utility.set_location('grd cer is'||hrr_rec.information277,16);
2344 hr_utility.set_location('pnt cer is'||hrr_rec.information278,16);
2345 hr_utility.set_location('old grd cer is'||l_old_grd_cer_id,16);
2346 hr_utility.set_location('old pnt cer is'||l_old_pnt_cer_id,16);
2347 l_hrrate_id := l_old_hrr_id; -- previous row created id can be used
2348 l_hrr_ovn := l_old_hrr_ovn;
2349 else
2350 l_hrrate_id := hrr_rec.information1;
2351 l_hrr_ovn := hrr_rec.information298;
2352 end if;
2353 else
2354 l_hrrate_id := hrr_rec.information1;
2355 l_hrr_ovn := hrr_rec.information298;
2356 end if;
2357 l_effective_date := hrr_rec.information2;
2358 hr_utility.set_location('hrr effdt is'||to_char(l_effective_date,'DD/MM/RRRR'),15);
2359 l_payrate_id := hrr_rec.information293;
2360 if hrr_rec.dml_operation = 'INSERT'
2361 and nvl(hrr_rec.datetrack_mode,'CORRECTION') <> 'UPDATE_REPLACE' then
2362 l_dml_operation := 'INSERT';
2363 elsif hrr_rec.dml_operation = 'INSERT' and hrr_rec.datetrack_mode = 'UPDATE_REPLACE' then
2364 l_dml_operation := 'UPDATE';
2365 elsif hrr_rec.dml_operation = 'UPDATE' then
2366 l_dml_operation := 'UPDATE';
2367 else
2368 l_dml_operation := '';
2369 end if;
2370 hr_utility.set_location('opt cer id is '||hrr_rec.INFORMATION278,3);
2371 hr_utility.set_location('pl cer id is '||hrr_rec.INFORMATION277,3);
2372 if hrr_rec.INFORMATION277 is not null and hrr_rec.INFORMATION255 is null then
2373 hr_utility.set_location('going for getting pl_id ',3);
2374 begin
2375 select information223
2376 into l_grd_sp_id
2377 from ben_copy_entity_results
2378 where copy_entity_result_id = hrr_rec.INFORMATION277;
2379 exception
2380 when others then
2381 l_grd_sp_id := null;
2382 end;
2383 l_rate_type := 'G';
2384 elsif hrr_rec.INFORMATION255 is not null then
2385 l_grd_sp_id := hrr_rec.INFORMATION255;
2386 l_rate_type := 'G';
2387 elsif hrr_rec.INFORMATION276 is not null then
2388 l_grd_sp_id := hrr_rec.INFORMATION276;
2389 l_rate_type := 'SP';
2390 elsif hrr_rec.INFORMATION278 is not null and hrr_rec.INFORMATION276 is null then
2391 hr_utility.set_location('going for getting opt_id ',3);
2392 begin
2393 select information257
2394 into l_grd_sp_id
2395 from ben_copy_entity_results
2396 where copy_entity_result_id = hrr_rec.INFORMATION278;
2397 exception
2398 when others then
2399 l_grd_sp_id := null;
2400 end;
2401 l_rate_type := 'SP';
2402 else
2403 l_grd_sp_id := null;
2404 hr_utility.set_location('pl id is '||hrr_rec.INFORMATION255,3);
2405 hr_utility.set_location('pl cer id is '||hrr_rec.INFORMATION277,3);
2406 hr_utility.set_location('opt id is '||hrr_rec.INFORMATION276,3);
2407 hr_utility.set_location('opt cer id is '||hrr_rec.INFORMATION278,3);
2408 end if;
2409 hr_utility.set_location('grade or point id '||l_grd_sp_id,20);
2410 if l_dml_operation = 'INSERT'
2411 and l_hrrate_id is null
2412 and l_grd_sp_id is not null
2413 and l_payrate_id is not null then
2414 hr_utility.set_location('new hrrate is being created'||l_proc,20);
2415 hr_utility.set_location('grade or point id '||l_grd_sp_id,20);
2416 hr_utility.set_location('value'||hrr_rec.information297,20);
2417 hr_utility.set_location('pay rate id'||l_payrate_id,20);
2418 hr_utility.set_location('hrr_ovn'||l_hrr_ovn,20);
2419 begin
2420 hr_rate_values_api.create_rate_value
2421 (p_effective_date => l_effective_date
2422 ,p_business_group_id => p_business_group_id
2423 ,p_rate_id => l_payrate_id
2424 ,p_grade_or_spinal_point_id => l_grd_sp_id
2425 ,p_rate_type => l_rate_type
2426 ,p_currency_code => p_gl_currency
2427 ,p_maximum => hrr_rec.information295
2428 ,p_mid_value => hrr_rec.information296
2429 ,p_minimum => hrr_rec.information294
2430 ,p_value => nvl(hrr_rec.information297,0)
2431 ,p_grade_rule_id => l_hrrate_id
2432 ,p_object_version_number => l_hrr_ovn
2433 ,p_effective_start_date => l_hrr_esd
2434 ,p_effective_end_date => l_hrr_eed);
2435 exception
2436 when others then
2437 hr_utility.set_location('grade or point id '||l_grd_sp_id,20);
2438 raise;
2439 end;
2440 hr_utility.set_location('hrrate id is '||l_hrrate_id,30);
2441 hrr_writeback(p_grade_cer_id => hrr_rec.information277,
2442 p_point_cer_id => hrr_rec.information278,
2443 p_copy_entity_txn_id => p_copy_entity_txn_id,
2444 p_hrrate_id => l_hrrate_id);
2445 hr_utility.set_location('hrrate wrtback comp '||l_hrrate_id,30);
2446 elsif l_dml_operation ='UPDATE'
2447 and l_hrrate_id is not null
2448 and l_grd_sp_id is not null
2449 and l_hrr_ovn is not null
2450 and l_payrate_id is not null then
2451 hr_utility.set_location('hrrate is being updated'||l_proc,60);
2452 hr_utility.set_location('grade or point id '||l_grd_sp_id,20);
2453 hr_utility.set_location('value'||hrr_rec.information297,20);
2454 hr_utility.set_location('pay rate id'||l_payrate_id,20);
2455 hr_utility.set_location('hrr_ovn'||l_hrr_ovn,20);
2456 if hrr_rec.datetrack_mode <> 'CORRECTION' then
2457 l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode
2458 (p_table_name => 'PAY_GRADE_RULES_F',
2459 p_key_column_name => 'GRADE_RULE_ID',
2460 p_key_column_value => l_hrrate_id,
2461 p_effective_date => l_effective_date);
2462 else
2463 l_dt_mode := hrr_rec.datetrack_mode;
2464 end if;
2465 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
2466 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn
2467 (p_table_name => 'PAY_GRADE_RULES_F',
2468 p_key_column_name => 'GRADE_RULE_ID',
2469 p_key_column_value => l_hrrate_id,
2470 p_effective_date => l_effective_date);
2471 hr_utility.set_location(' ovn is '||l_db_ovn,30);
2472 if l_db_ovn <> l_hrr_ovn then
2473 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','HRRATE');
2474 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
2475 fnd_message.set_token('OBJECT ',l_object);
2476 fnd_message.set_token('OBJECT_NAME ',l_rate_type ||':'|| l_grd_sp_id);
2477 fnd_message.raise_error;
2478 else
2479 begin
2480 hr_rate_values_api.update_rate_value
2481 (p_effective_date => l_effective_date
2482 ,p_currency_code => p_gl_currency
2483 ,p_maximum => hrr_rec.information295
2484 ,p_mid_value => hrr_rec.information296
2485 ,p_minimum => hrr_rec.information294
2486 ,p_value => nvl(hrr_rec.information297,0)
2487 ,p_grade_rule_id => l_hrrate_id
2488 ,p_datetrack_mode => l_dt_mode
2489 ,p_object_version_number => l_hrr_ovn
2490 ,p_effective_start_date => l_hrr_esd
2491 ,p_effective_end_date => l_hrr_eed);
2492 exception
2493 when others then
2494 hr_utility.set_location('grade or point id '||l_grd_sp_id,20);
2495 raise;
2496 end;
2497 end if;
2498 hr_utility.set_location('hrrate id is '||l_hrrate_id,30);
2499 else
2500 l_message_text := 'invalid operation '||l_dml_operation
2501 ||' hrrate id is '||l_hrrate_id
2502 ||' grade or point id '||l_grd_sp_id
2503 ||' payrate'||l_payrate_id
2504 ||' hrr_ovn'||l_hrr_ovn
2505 ||' rate type'||l_rate_type
2506 ||' dt mode'||hrr_rec.datetrack_mode;
2507 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2508 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2509 P_TXN_ID => nvl(l_hrrate_id,p_copy_entity_txn_id),
2510 P_MODULE_CD => 'PQH_GSP_STGBEN',
2511 p_context => 'GRADE_RATE',
2512 P_MESSAGE_TYPE_CD => 'E',
2513 P_MESSAGE_TEXT => l_message_text,
2514 p_effective_date => p_effective_date);
2515 end if;
2516 l_old_hrr_id := l_hrrate_id;
2517 l_old_hrr_ovn := l_hrr_ovn;
2518 l_old_grd_cer_id := hrr_rec.information277;
2519 l_old_pnt_cer_id := hrr_rec.information278;
2520 end loop;
2521 hr_utility.set_location('leaving hr rate ',420);
2522 exception
2523 when others then
2524 PQH_GSP_PROCESS_LOG.LOG_PROCESS_DTLS
2525 (P_MASTER_TXN_ID => p_copy_entity_txn_id,
2526 P_TXN_ID => p_copy_entity_txn_id,
2527 P_MODULE_CD => 'PQH_GSP_STGBEN',
2528 p_context => 'GRADE_RATE',
2529 P_MESSAGE_TYPE_CD => 'E',
2530 P_MESSAGE_TEXT => 'Grade point Rate',
2531 p_effective_date => p_effective_date);
2532 raise;
2533 end stage_to_hrate;
2534
2535
2536 end pqh_gsp_stage_to_hr;