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