[Home] [Help]
PACKAGE BODY: APPS.PQH_GSP_POST_PROCESS
Source
1 Package Body pqh_gsp_Post_Process as
2 /* $Header: pqhgsppp.pkb 120.17.12020000.4 2013/03/27 13:15:22 karthmoh ship $ */
3 --
4
5 /**************************************************************************/
6 /**************************Call_PP_From_Assignments************************/
7 /**************************************************************************/
8
9 g_debug boolean := hr_utility.debug_enabled;
10
11 Function dt_del_mode
12 (P_EFFECTIVE_DATE IN DATE
13 ,P_BASE_TABLE_NAME IN VARCHAR2
14 ,P_BASE_KEY_COLUMN IN VARCHAR2
15 ,P_BASE_KEY_VALUE IN NUMBER) Return Varchar2 is
16 --
17 l_zap Boolean;
18 l_delete Boolean;
19 l_future_change Boolean;
20 l_delete_next_change Boolean;
21 --
22 Begin
23 --
24 g_debug := hr_utility.debug_enabled;
25 if g_debug then
26 hr_utility.set_location(' DT Mode ',10);
27 hr_utility.set_location(' P_Effective_Date ' || P_Effective_Date, 20);
28 hr_utility.set_location(' P_BASE_TABLE_NAME ' || P_BASE_TABLE_NAME,30);
29 hr_utility.set_location(' P_BASE_KEY_COLUMN ' || P_BASE_KEY_COLUMN, 40);
30 hr_utility.set_location(' P_BASE_KEY_VALUE ' || P_BASE_KEY_VALUE, 50);
31 End If;
32
33 dt_api.find_dt_del_modes
34 (p_effective_date => p_effective_date
35 ,p_base_table_name => P_BASE_TABLE_NAME
36 ,p_base_key_column => P_BASE_KEY_COLUMN
37 ,p_base_key_value => p_base_key_value
38 ,p_zap => l_zap
39 ,p_delete => l_delete
40 ,p_future_change => l_future_change
41 ,p_delete_next_change => l_delete_next_change
42 );
43 --
44 If l_zap then
45 Return 'ZAP';
46 elsif l_delete then
47 Return 'DELETE';
48 elsif l_future_change then
49 Return 'FUTURE_CHANGE';
50 elsif l_delete_next_change then
51 Return 'DELETE_NEXT_CHANGE';
52 End if;
53 --
54 End;
55 --
56
57 Function DT_Mode
58 (P_EFFECTIVE_DATE IN DATE
59 ,P_BASE_TABLE_NAME IN VARCHAR2
60 ,P_BASE_KEY_COLUMN IN VARCHAR2
61 ,P_BASE_KEY_VALUE IN NUMBER) Return Varchar2 is
62
63 L_CORRECTION Boolean;
64 L_UPDATE Boolean;
65 L_UPDATE_OVERRIDE Boolean;
66 L_UPDATE_CHANGE_INSERT Boolean;
67
68 Begin
69 g_debug := hr_utility.debug_enabled;
70 if g_debug then
71 hr_utility.set_location(' DT Mode ',10);
72 hr_utility.set_location(' P_Effective_Date ' || P_Effective_Date, 20);
73 hr_utility.set_location(' P_BASE_TABLE_NAME ' || P_BASE_TABLE_NAME,30);
74 hr_utility.set_location(' P_BASE_KEY_COLUMN ' || P_BASE_KEY_COLUMN, 40);
75 hr_utility.set_location(' P_BASE_KEY_VALUE ' || P_BASE_KEY_VALUE, 50);
76 End If;
77
78 Dt_Api.FIND_DT_UPD_MODES
79 (P_EFFECTIVE_DATE => P_Effective_Date
80 ,P_BASE_TABLE_NAME => P_BASE_TABLE_NAME
81 ,P_BASE_KEY_COLUMN => P_BASE_KEY_COLUMN
82 ,P_BASE_KEY_VALUE => P_BASE_KEY_VALUE
83 ,P_CORRECTION => L_CORRECTION
84 ,P_UPDATE => L_UPDATE
85 ,P_UPDATE_OVERRIDE => L_UPDATE_OVERRIDE
86 ,P_UPDATE_CHANGE_INSERT => L_UPDATE_CHANGE_INSERT);
87
88 If L_Update Then
89 Return 'UPDATE';
90 Elsif L_UPDATE_CHANGE_INSERT then
91 return 'UPDATE_CHANGE_INSERT';
92 Elsif L_Update_Override Then
93 Return 'UPDATE_OVERRIDE';
94 Elsif L_CORRECTION Then
95 Return 'CORRECTION';
96 End If;
97
98 End;
99
100 Procedure Call_PP_From_Assignments
101 (P_Effective_Date IN Date,
102 P_Assignment_Id IN Number,
103 P_Date_track_Mode IN Varchar2,
104 P_Warning_Mesg OUT NOCOPY Varchar2) Is
105
106 l_Pgm_Id BEN_PGM_F.Pgm_Id%TYPE;
107 l_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
108 l_Update_Salary_cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
109 l_Person_name Per_All_People_F.Full_name%TYPE;
110 l_Person_Id Per_All_People_F.Person_Id%TYPE;
111 l_Last_Name Per_All_People_F.Last_name%TYPE;
112 L_Cnt Number;
113 l_Grade_id Per_all_Assignments_F.Grade_Id%TYPE;
114 l_Dflt_Step_Cd Ben_Pgm_F.Dflt_Step_Cd%TYPE;
115 l_Step_id Per_Spinal_POint_Steps_F.Step_Id%TYPE;
116 l_Mass_update_Call Varchar2(1) := 'N';
117 l_Last_Per_In_ler_Id Ben_Per_In_ler.Per_in_ler_Id%TYPE;
118 l_Lst_Rt_Chg_Dt Ben_Enrt_Rt.Rt_Strt_Dt%TYPE;
119 l_Le_Exists Varchar2(1);
120
121 Cursor Person_Dtls is
122 Select Full_name, Last_NAME, Person.Person_Id, Asgt.Grade_id, Asgt.Grade_Ladder_Pgm_Id
123 from Per_All_Assignments_F Asgt,
124 PER_ASSIGNMENT_STATUS_TYPES Pas,
125 Per_All_People_F Person
126 Where Assignment_Id = P_Assignment_Id
127 and P_Effective_Date
128 Between Asgt.Effective_Start_Date
129 and Asgt.Effective_End_Date
130 and Person.Person_Id = Asgt.Person_Id
131 and asgt.assignment_type ='E'
132 and asgt.primary_flag ='Y'
133 and asgt.ASSIGNMENT_STATUS_TYPE_ID = Pas.ASSIGNMENT_STATUS_TYPE_ID
134 and pas.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
135 and P_Effective_Date
136 Between Person.Effective_Start_Date
137 and Person.Effective_End_Date;
138
139 Cursor Dflt_Step Is
140 Select Dflt_Step_cd, Update_Salary_Cd
141 from Ben_Pgm_F
142 Where Pgm_id = l_Pgm_Id
143 and P_Effective_Date
144 Between Effective_Start_Date
145 and Effective_End_Date;
146
147 Cursor Dflt_grdldr is
148 Select Pgm.Pgm_Id
149 From Ben_Pgm_F Pgm,
150 Ben_Plip_F Plip,
151 Ben_Pl_F Plan
152 where Pgm.Dflt_Pgm_Flag = 'Y'
153 and Pgm.Pgm_Typ_Cd = 'GSP'
154 and P_effective_date
155 Between Pgm.Effective_Start_Date
156 and Pgm.Effective_End_Date
157 and Pgm.Business_Group_id = hr_general.get_Business_group_Id
158 and Plan.Mapping_Table_name = 'PER_GRADES'
159 and Plan.Mapping_Table_Pk_id = l_grade_Id
160 and P_Effective_Date
161 Between Plan.Effective_Start_Date
162 and Plan.Effective_End_Date
163 and Plan.Pl_Id = Plip.Pl_Id
164 and Pgm.Pgm_Id = Plip.Pgm_id
165 and P_Effective_Date
166 between Plip.Effective_Start_Date
167 and Plip.Effective_End_Date;
168
169 Cursor Step_Dtls Is
170 Select Plcmt.Step_id
171 From Per_Spinal_POint_Placements_f Plcmt,
172 Per_Spinal_point_Steps_F Step
173 Where Plcmt.Assignment_id = P_Assignment_Id
174 and P_Effective_Date
175 Between Plcmt.Effective_Start_Date
176 and plcmt.Effective_End_Date
177 and Step.Step_id = Plcmt.Step_Id
178 and P_Effective_Date
179 Between Step.Effective_Start_Date
180 and Step.Effective_End_Date;
181
182 ---
183
184 Cursor csr_le is
185 Select max(pil.Per_in_Ler_Id)
186 From Ben_Per_in_ler PIL,
187 Ben_Ler_F LER
188 Where Pil.Ler_Id = LER.Ler_Id
189 And Pil.LF_EVT_OCRD_DT = P_Effective_Date
190 And ler.typ_Cd = 'GSP'
191 And Pil.person_Id = l_person_id
192 And Pil.Per_In_Ler_Stat_Cd = 'PROCD';
193 ---
194 Cursor csr_sal is
195 Select Rate.Rt_Strt_Dt Sal_Chg_Dt
196 From Ben_Elig_Per_Elctbl_Chc Elct,
197 Ben_Enrt_Rt Rate
198 Where Elct.DFLT_FLAG = 'Y'
199 and Elct.Elctbl_Flag = 'Y'
200 and Elct.Per_in_ler_id = l_Last_Per_In_ler_Id
201 and Elct.Enrt_Cvg_Strt_Dt is Not NULL
202 And Rate.ELIG_PER_ELCTBL_CHC_ID(+) = Elct.ELIG_PER_ELCTBL_CHC_ID;
203
204 Cursor Strtd_Le_Exits is
205 Select 'X'
206 from Ben_Per_in_ler Pler,
207 Ben_Ler_F Ler
208 where Person_id = l_Person_Id
209 and Per_In_ler_Stat_Cd = 'STRTD'
210 and Pler.Ler_Id = Ler.ler_ID
211 and Ler.Typ_Cd = 'GSP'
212 and P_Effective_Date
213 Between Ler.Effective_Start_Date
214 and Ler.Effective_End_Date;
215
216 -- Bug 15928934
217 l_old_grdldr number;
218 l_new_grdldr number;
219 l_old_grd number;
220 l_new_grd number;
221
222 -- Bug 16285614
223 cursor old_grdldr is
224 select GRADE_LADDER_PGM_ID,GRADE_ID from per_all_assignments_f asg
225 where asg.assignment_id = P_Assignment_Id
226 and asg.effective_end_date = P_Effective_Date - 1
227 and asg.primary_flag = 'Y'
228 and asg.assignment_type ='E';
229
230
231
232 cursor new_grdldr is
233 select GRADE_LADDER_PGM_ID, GRADE_ID from per_all_assignments_f asg
234 where asg.assignment_id = P_Assignment_Id
235 and P_Effective_Date between asg.effective_start_date and asg.effective_end_date
236 and asg.primary_flag = 'Y'
237 and asg.assignment_type ='E';
238
239 -- Bug 15928934
240
241 Begin
242
243 g_debug := hr_utility.debug_enabled;
244
245 /* Initialize the Process Log */
246 Savepoint Assgt_Enrolment;
247
248 if g_debug then
249 hr_utility.set_location(' Inside Asgt Call ', 10);
250 End If;
251
252
253 -- Bug 15928934
254 open old_grdldr;
255 fetch old_grdldr into l_old_grdldr,l_old_grd;
256 close old_grdldr;
257
258 open new_grdldr;
259 fetch new_grdldr into l_new_grdldr,l_new_grd;
260 close new_grdldr;
261
262 if not ((nvl(l_new_grd,-1) <> nvl(l_old_grd,-1)) or (nvl(l_old_grdldr,-1) <> nvl(l_new_grdldr,-1))) then
263 return;
264 end if;
265 -- Bug 15928934
266
267
268 Open Person_Dtls;
269 Fetch Person_Dtls Into l_Person_Name, l_Last_Name,l_Person_Id, l_Grade_id, l_Pgm_Id;
270 Close Person_Dtls;
271
272 If l_Grade_id is Null then
273 Return;
274 End If;
275
276 If L_Pgm_Id is Null then
277 Open Dflt_grdldr;
278 Fetch Dflt_Grdldr into L_Pgm_Id;
279 Close Dflt_Grdldr;
280
281 If L_Pgm_Id is NULL Then
282 Return;
283 End if;
284 End If;
285
286 Open Dflt_Step;
287 Fetch Dflt_Step into l_Dflt_Step_Cd, L_Update_Salary_Cd;
288 Close Dflt_Step;
289
290 If l_Dflt_Step_Cd in ('PQH_GSP_SP','PQH_GSP_GSP','MINSALINCR','MINSTEP','NOSTEP') Then
291 Open Step_Dtls;
292 Fetch Step_Dtls Into L_Step_Id;
293 Close Step_Dtls;
294 If l_Step_Id is NULL Then
295 Return;
296 End if;
297 End If;
298
299 If pqh_process_batch_log.g_module_cd is NULL then
300 Pqh_Gsp_process_Log.Start_log
301 (P_Txn_ID => P_Assignment_Id
302 ,P_Txn_Name => l_Last_Name
303 ,P_Module_Cd => 'PQH_GSP_ASSIGN_ENTL');
304 l_Mass_Update_Call := 'N';
305 Else
306 l_Mass_Update_Call := 'Y';
307 End If;
308
309 Open Strtd_Le_Exits;
310 Fetch Strtd_Le_Exits into l_Le_Exists;
311 if Strtd_Le_Exits%FOUND then
312 /* open Life Event already exists for the person */
313 if g_debug then
314 hr_utility.set_location(' leaving Asgt Call ', 20);
315 End If;
316 Close Strtd_Le_Exits;
317 If pqh_process_batch_log.g_module_cd = 'PQH_GSP_ASSIGN_ENTL' Then
318 fnd_message.set_name('PQH','PQH_GSP_LE_STRT');
319 fnd_message.raise_error;
320 Else
321 Return;
322 End If;
323 End If;
324 Close Strtd_Le_Exits;
325
326
327 /* Get the Last Enrollment details for Determining the Previous SalaryChange Dt */
328 If P_DATE_TRACK_MODE = 'CORRECTION' Then
329
330 Open csr_le;
331 Fetch csr_le into l_Last_Per_In_ler_Id;
332 Close Csr_Le;
333
334 If l_Last_Per_In_ler_Id is Not NULL then
335 Open csr_sal;
336 Fetch csr_sal into l_Lst_Rt_Chg_Dt;
337 Close csr_Sal;
338 End If;
339
340 End If;
341
342 pqh_gsp_Post_Process.Override_Eligibility
343 (P_Effective_Date => P_Effective_Date
344 ,P_Assignment_id => P_Assignment_Id
345 ,P_Called_From => 'A'
346 ,P_Date_track_Mode => P_Date_Track_Mode
347 ,P_Elig_Per_Elctbl_Chc_Id => l_Elig_per_Elctbl_Chc_Id);
348
349 if g_debug then
350 hr_utility.set_location(' Completed Override Eligibility ', 30);
351 End If;
352
353
354 If L_Update_Salary_Cd <> 'NO_UPDATE' and l_Elig_per_Elctbl_Chc_Id is NOT NULL Then
355 pqh_gsp_Post_Process.Update_Salary_Info
356 (P_Elig_per_Elctbl_Chc_Id => l_Elig_per_Elctbl_Chc_Id
357 ,P_Effective_Date => P_Effective_Date
358 ,P_Dt_Mode => P_DATE_TRACK_MODE
359 ,P_Called_From => 'A'
360 ,P_Prv_Sal_Chg_Dt => l_Lst_Rt_Chg_Dt);
361 End If;
362
363 if g_debug then
364 hr_utility.set_location(' Completed Salary Update ', 40);
365 End If;
366
367 /* Log the Entry in Process Log as Complete */
368
369 If l_Mass_Update_call = 'N' then
370 /* Not Called from Mass Update and hence logging the process Completion */
371
372 Pqh_Gsp_process_Log.Log_process_Dtls
373 (P_Master_txn_Id => P_Assignment_Id
374 ,P_Txn_Id => P_Assignment_Id
375 ,p_module_cd => 'PQH_GSP_ASSIGN_ENTL'
376 ,p_message_type_cd => 'C'
377 ,p_message_text => NULL
378 ,P_Effective_Date => P_Effective_Date);
379
380 PQH_PROCESS_BATCH_LOG.END_LOG;
381
382 End If;
383
384 P_Warning_Mesg := NULL;
385
386 Exception
387 When Others Then
388 Rollback to Assgt_Enrolment;
389 P_Warning_Mesg := 'PQH_GSP_ASGMT_PP_ERR';
390
391 if l_Mass_Update_Call = 'N' Then
392 Pqh_Gsp_process_Log.Log_process_Dtls
393 (P_Master_txn_Id => P_Assignment_Id
394 ,P_Txn_Id => P_Assignment_Id
395 ,p_module_cd => 'PQH_GSP_ASSIGN_ENTL'
396 ,p_message_type_cd => 'E'
397 ,p_message_text => Nvl(fnd_Message.Get,sqlerrm)
398 ,P_Effective_Date => P_Effective_Date);
399
400 PQH_PROCESS_BATCH_LOG.END_LOG;
401
402 Else
403
404 Pqh_Process_Batch_Log.Set_Context_Level
405 (P_Txn_id => P_assignment_Id
406 ,P_txn_Table_Route_Id => NULL
407 ,P_Level => 2
408 ,P_Log_Context => l_person_Name);
409
410 Pqh_Process_Batch_log.Insert_log
411 (P_Message_Type_Cd => Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','ERR')
412 ,P_Message_text => Nvl(fnd_message.get,Sqlerrm));
413
414 End if;
415
416 End Call_PP_From_Assignments;
417
418 /**************************************************************************/
419 /************************** Call_PP_From_Benmngle *************************/
420 /**************************************************************************/
421
422 Procedure Call_PP_From_Benmngle
423 (P_Effective_Date IN Date,
424 P_Elig_per_Elctbl_Chc_Id IN Number) Is
425
426 Cursor Pler is
427 Select Pler.Per_In_Ler_Id, Pler.Object_Version_Number, Elct.Pgm_Id
428 From Ben_Elig_per_Elctbl_Chc Elct,
429 Ben_Per_In_Ler Pler
430 Where Elct.Elig_per_Elctbl_Chc_Id = P_Elig_per_Elctbl_Chc_Id
431 and Pler.Per_In_Ler_Id = Elct.Per_In_Ler_Id;
432
433 Cursor Pgm_Dtl(P_Pgm_Id In Number) Is
434 Select Update_Salary_Cd
435 from Ben_Pgm_F
436 where Pgm_Id = P_Pgm_Id
437 and P_Effective_Date
438 Between Effective_Start_Date
439 and Effective_End_Date;
440
441 L_Per_In_Ler_id Ben_Per_In_ler.Per_In_Ler_Id%TYPE;
442 L_Pil_Ovn Ben_Per_In_ler.Object_Version_Number%TYPE;
443 l_Pgm_Id Ben_Pgm_F.Pgm_Id%TYPE;
444 l_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
445 l_Assignment_id Per_All_Assignments_F.Assignment_id%TYPE;
446
447 l_PROCD_DT DATE;
448 l_STRTD_DT DATE;
449 l_VOIDD_DT Date;
450
451 Begin
452
453 g_debug := hr_utility.debug_enabled;
454 pqh_process_batch_log.g_module_cd := 'PQH_GSP_BENMNGLE';
455
456 if P_Elig_per_Elctbl_Chc_Id = NULL then
457 fnd_message.set_name('PQH','PQH_GSP_DFLT_SLCT_ERR');
458 fnd_message.raise_error;
459 End If;
460
461 Savepoint Benmngle_Call;
462 Open Pler;
463 Fetch Pler into L_Per_In_Ler_id, l_Pil_Ovn, l_Pgm_Id;
464 Close Pler;
465
466 Open Pgm_Dtl(l_Pgm_Id);
467 Fetch Pgm_Dtl into L_UPdate_Salary_Cd;
468 Close Pgm_Dtl;
469
470 if g_debug then
471 hr_utility.set_location(' Inside Benmngle call ', 10);
472 End If;
473
474 /* Update the Assignment Record with the new grade , Step */
475
476 pqh_gsp_Post_Process.Update_Assgmt_Info
477 (P_Elig_Per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
478 ,P_Effective_Date => P_Effective_Date);
479
480 if g_debug then
481 hr_utility.set_location(' Completed Asgt Update ', 30);
482 End If;
483
484 /* Update the Salary */
485
486 If Nvl(l_Update_Salary_Cd,'NO_UPDATE') <> 'NO_UPDATE' Then
487 pqh_gsp_Post_Process.Update_Salary_Info
488 (P_Elig_per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
489 ,P_Effective_Date => P_Effective_Date);
490 End If;
491
492 if g_debug then
493 hr_utility.set_location(' Completed Sal Update ', 40);
494 End If;
495
496 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
497 (P_PER_IN_LER_ID => l_PER_IN_LER_ID
498 ,P_PER_IN_LER_STAT_CD => 'PROCD'
499 ,P_PROCD_DT => L_PROCD_DT
500 ,P_STRTD_DT => L_STRTD_DT
501 ,P_VOIDD_DT => L_VOIDD_DT
502 ,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
503 ,P_EFFECTIVE_DATE => P_Effective_Date);
504
505 if g_debug then
506 hr_utility.set_location(' Completed PIL Update ', 20);
507 End if;
508
509 pqh_process_batch_log.g_module_cd := NULL;
510 Exception
511 When Others then
512 Rollback to Benmngle_Call;
513 pqh_process_batch_log.g_module_cd := NULL;
514
515 l_Assignment_Id := pqh_gsp_default.get_asg_for_pil(L_PER_IN_LER_ID, P_Effective_Date);
516
517 Pqh_Gsp_process_Log.Log_process_Dtls
518 (P_Master_txn_Id => l_Assignment_id
519 ,P_Txn_Id => l_Assignment_id
520 ,p_module_cd => 'PQH_GSP_DFLT_ENRL'
521 ,p_message_type_cd => 'E'
522 ,p_message_text => Nvl(fnd_Message.Get,sqlerrm)
523 ,P_Effective_Date => P_Effective_Date);
524
525 End;
526
527 /**************************************************************************/
528 /***************************Call_PP_For_Batch_Enrl*************************/
529 /**************************************************************************/
530
531 Procedure Call_PP_For_Batch_Enrl
532 (P_Errbuf OUT NOCOPY Varchar2
533 ,P_Retcode OUT NOCOPY Number
534 ,P_Effective_Date IN Varchar2
535 ,P_Grade_Ladder_Id IN Number Default NULL
536 ,P_Person_Id IN Number Default NULL
537 ,p_grade_id IN Number Default Null
538 ,p_person_selection_rule_id IN Number Default Null) Is
539
540 l_Concurrent_Req_Id Number(18);
541 L_Error Varchar2(1) := 'N';
542 L_Effective_Date Date;
543 l_outputs ff_exec.outputs_t;
544
545 Cursor Asgt_Dtls
546 Is
547 Select Paa.Assignment_Id, Paa.Grade_ladder_Pgm_Id, Paa.Grade_Id, Paa.Person_Id,
548 paa.soft_coding_keyflex_id,paa.people_group_id,paa.special_ceiling_step_id,paa.object_version_number
549 from Per_All_Assignments_F Paa
550 Where L_Effective_Date Between paa.Effective_Start_Date and Paa.Effective_End_Date
551 and Paa.Business_group_id = hr_General.get_Business_group_id
552 and Paa.Person_id = Nvl(P_Person_Id, Paa.Person_Id)
553 and paa.assignment_type ='E'
554 and paa.primary_flag ='Y'
555 and Paa.Grade_ladder_Pgm_Id is null
556 and Paa.grade_id in
557 (select pl.mapping_table_pk_id
558 from ben_plip_f plip,ben_pl_f pl
559 where plip.pgm_id = p_grade_ladder_id
560 and plip.pl_id = pl.pl_id
561 and L_Effective_Date between
562 plip.effective_start_date and plip.effective_end_date
563 and l_effective_date between
564 pl.effective_start_date and pl.effective_end_date
565 and pl.mapping_table_pk_id = nvl(p_grade_id,pl.mapping_table_pk_id));
566
567
568 L_Cnt Number := 0;
569 L_Error_Exists Varchar2(1) := 'N';
570 l_conc_status boolean;
571
572 l_Assignment_Ovn Per_All_Assignments_F.Object_Version_Number%TYPE;
573 l_SPECIAL_CEILING_STEP_ID Per_All_Assignments_F.SPECIAL_CEILING_STEP_ID%TYPE;
574 L_People_Group_Id Per_All_Assignments_F.People_Group_Id%TYPE;
575 l_Soft_Coding_Keyflex_Id Per_All_Assignments_F.Soft_Coding_Keyflex_Id%TYPE;
576 L_Pgm_Id ben_pgm_f.pgm_id%TYPE;
577 L_DATE_TRACK_MODE Varchar2(25);
578 l_assignment_id Per_All_Assignments_F.assignment_id%TYPE;
579 l_group_name Varchar2(250);
580 l_Asg_effective_start_date Per_All_Assignments_F.Effective_Start_Date%TYPE;
581 l_Asg_effective_end_date Per_All_Assignments_F.Effective_End_Date%TYPE;
582 l_org_now_no_manager_warning Boolean;
583 l_other_manager_warning Boolean;
584 l_spp_delete_warning Boolean;
585 l_entries_changed_warning Varchar2(250);
586 l_tax_district_changed_warning Boolean;
587 l_concatenated_segments Varchar2(250);
588 l_person_rule_checked Boolean := true;
589 l_business_group_id Number;
590
591 Begin
592
593 g_debug := hr_utility.debug_enabled;
594
595 L_Effective_Date := Fnd_Date.CANONICAL_TO_DATE(P_Effective_Date);
596 l_business_group_id := hr_General.get_Business_group_id;
597
598 hr_utility.set_location(' Entering ', 10);
599 hr_utility.set_location(' L_Effective_Date'||L_Effective_Date, 20);
600
601
602 L_Concurrent_Req_Id := fnd_global.conc_request_id;
603
604 Pqh_Gsp_process_Log.Start_log
605 (P_Txn_ID => l_Concurrent_Req_Id
606 ,P_Txn_Name => Hr_general.Decode_Lookup('PQH_GSP_GEN_PROMPTS','BENRL') || l_Concurrent_Req_Id
607 ,P_Module_Cd => 'PQH_GSP_BATCH_ENRL');
608
609 For Asgt_Rec in Asgt_Dtls
610 Loop
611 Begin
612
613 Savepoint Batch_Enroll;
614
615
616 If Asgt_Rec.Grade_Id is NULL Then
617 fnd_message.set_name('PQH','PQH_GSP_GRD_NOTLNKD_ASSGT');
618 fnd_message.raise_error;
619 End If;
620
621
622 Select Count(*) into L_Cnt
623 from Ben_Per_in_ler Pler,
624 Ben_Ler_F Ler
625 where Person_id = Asgt_Rec.Person_Id
626 and Per_In_ler_Stat_Cd = 'STRTD'
627 and Pler.Ler_Id = Ler.ler_ID
628 and Ler.Typ_Cd = 'GSP'
629 and L_Effective_Date
630 Between Ler.Effective_Start_Date
631 and Ler.Effective_End_Date;
632
633 If l_Cnt > 0 then
634 if g_debug then
635 hr_utility.set_location(' leaving Asgt Call ', 20);
636 End If;
637 Else
638
639 l_assignment_id := Asgt_Rec.assignment_id;
640 L_Soft_Coding_Keyflex_Id := Asgt_Rec.Soft_Coding_Keyflex_Id;
641 L_People_Group_Id := Asgt_Rec.People_Group_Id;
642 L_special_ceiling_step_id := Asgt_Rec.special_ceiling_step_id;
643 L_Assignment_Ovn := Asgt_Rec.Object_version_Number;
644
645 if p_person_selection_rule_id is not null then
646 l_outputs:=benutils.formula(
647 p_formula_id => p_person_selection_rule_id
648 ,p_effective_date => l_effective_date
649 ,p_business_group_id => l_business_group_id
650 ,p_assignment_id => l_assignment_id);
651 --
652 IF l_outputs(l_outputs.FIRST).VALUE = 'Y' THEN
653 --
654 l_person_rule_checked := true;
655 --
656 ELSIF l_outputs(l_outputs.FIRST).VALUE = 'N' THEN
657 --
658 l_person_rule_checked := false;
659 --
660 END IF;
661 End if;
662 if l_person_rule_checked then
663
664 L_DATE_TRACK_MODE := pqh_gsp_post_process.DT_Mode
665 (P_EFFECTIVE_DATE => L_Effective_Date
666 ,P_BASE_TABLE_NAME => 'PER_ALL_ASSIGNMENTS_F'
667 ,P_BASE_KEY_COLUMN => 'ASSIGNMENT_ID'
668 ,P_BASE_KEY_VALUE => L_Assignment_id);
669 hr_utility.set_location('L_date_track_mode:'||L_DATE_TRACK_MODE,90);
670 IF l_date_track_mode = 'CORRECTION' OR l_date_track_mode = 'UPDATE' THEN
671 Hr_Assignment_Api.Update_Emp_Asg_Criteria
672 (p_effective_date => L_Effective_Date
673 ,p_datetrack_update_mode => L_Date_Track_Mode
674 ,p_assignment_id => L_Assignment_id
675 ,p_grade_ladder_pgm_id => P_Grade_Ladder_Id
676 ,p_object_version_number => L_Assignment_Ovn
677 ,p_special_ceiling_step_id => L_special_ceiling_step_id
678 ,p_people_group_id => L_People_Group_Id
679 ,p_soft_coding_keyflex_id => L_Soft_Coding_Keyflex_Id
680 ,p_group_name => L_group_name
681 ,p_effective_start_date => L_Asg_effective_start_date
682 ,p_effective_end_date => L_Asg_effective_end_date
683 ,p_org_now_no_manager_warning => L_org_now_no_manager_warning
684 ,p_other_manager_warning => L_other_manager_warning
685 ,p_spp_delete_warning => L_spp_delete_warning
686 ,p_entries_changed_warning => L_entries_changed_warning
687 ,p_tax_district_changed_warning => L_tax_district_changed_warning
688 ,p_concatenated_segments => L_concatenated_segments);
689
690 Pqh_Gsp_process_Log.Log_process_Dtls
691 (P_Master_txn_Id => L_Concurrent_Req_Id
692 ,P_TXN_ID => Asgt_Rec.Assignment_Id
693 ,p_module_cd => 'PQH_GSP_BATCH_ENRL'
694 ,p_message_type_cd => 'C'
695 ,p_message_text => NULL
696 ,P_Effective_Date => L_Effective_Date);
697 Else
698
699 fnd_message.set_name('PQH','PQH_FUTURE_DATES_ASSGT_EXIST');
700 Pqh_Gsp_process_Log.Log_process_Dtls
701 (P_Master_txn_Id => L_Concurrent_Req_Id
702 ,P_TXN_ID => Asgt_Rec.Assignment_Id
703 ,p_module_cd => 'PQH_GSP_BATCH_ENRL'
704 ,p_message_type_cd => 'E'
705 ,p_message_text => fnd_message.get
706 ,P_Effective_Date => L_Effective_Date);
707 End if;
708
709 End if;
710
711
712 End If;
713
714 Exception
715 When Others Then
716 Rollback to Batch_Enroll;
717 l_Error_Exists := 'Y';
718 Pqh_Gsp_process_Log.Log_process_Dtls
719 (P_Master_txn_Id => L_Concurrent_Req_Id
720 ,P_Txn_ID => Asgt_Rec.Assignment_Id
721 ,p_module_cd => 'PQH_GSP_BATCH_ENRL'
722 ,p_message_type_cd => 'E'
723 ,p_message_text => Nvl(fnd_Message.get,sqlerrm)
724 ,P_Effective_Date => L_Effective_Date);
725 hr_utility.set_location('Erro:'|| l_Error_Exists,120);
726 End;
727 End Loop;
728
729 If L_Error_Exists = 'N' Then
730 fnd_message.set_name('PQH','PQH_GSP_LOG_SUC');
731 fnd_Message.Set_Token('MODULE',Hr_general.Decode_lookup('PQH_PROCESS_LOG_TREE','PQH_GSP_BATCH_ENRL'));
732 fnd_file.put_line(fnd_file.log,Fnd_Message.get);
733 Else
734 fnd_message.set_name('PQH','PQH_GSP_LOG_ERR');
735 fnd_Message.Set_Token('MODULE',Hr_general.Decode_lookup('PQH_PROCESS_LOG_TREE','PQH_GSP_BATCH_ENRL'));
736 fnd_file.put_line(fnd_file.log,Fnd_Message.get);
737 l_conc_status := fnd_concurrent.set_completion_status(status => 'ERROR'
738 ,message=>SQLERRM);
739 hr_utility.set_location('Some Error:'|| SQLERRM,120);
740 End If;
741 hr_utility.set_location(' Leaving ', 10);
742 PQH_PROCESS_BATCH_LOG.END_LOG;
743 Commit;
744 End Call_PP_For_Batch_Enrl;
745
746
747
748 /**************************************************************************/
749 /************************** Override Eligibility *************************/
750 /**************************************************************************/
751
752 Procedure Override_Eligibility
753 (P_Effective_Date IN Date
754 ,P_Assignment_id IN Number
755 ,P_Called_From In Varchar2
756 ,P_Date_Track_Mode IN Varchar2
757 ,P_Elig_Per_Elctbl_Chc_Id OUT NOCOPY Number) is
758
759 Cursor Life_Event_type(P_Business_Group_Id IN Number) is
760 Select Ler_id
761 from Ben_Ler_F
762 Where Typ_Cd = 'GSP'
763 and LF_EVT_OPER_CD = 'PROG' --ggnanagu 4032221
764 and P_Effective_Date
765 Between Effective_Start_Date
766 and Effective_End_Date
767 and Business_Group_id = P_Business_Group_id;
768
769 Cursor Person_Info Is
770 Select Asgt.Person_Id, Asgt.Business_Group_id,
771 Asgt.Grade_Id , Asgt.pay_basis_id, Asgt.GRADE_LADDER_PGM_ID, Asgt.Effective_Start_Date
772 from Per_All_Assignments_f Asgt
773 Where Assignment_Id = P_Assignment_Id
774 and P_Effective_Date
775 Between Asgt.Effective_Start_Date
776 and Asgt.Effective_End_Date;
777
778 Cursor Ben_Grd_Dtls(P_Pgm_Id In Number, P_Grd_Id In Number) Is
779 Select Pl.Pl_Id, plip.Plip_Id, Pgm.DFLT_STEP_CD,
780 Pgm.UPDATE_SALARY_CD , DFLT_ELEMENT_TYPE_ID
781 from Ben_Pgm_F Pgm,
782 Ben_Pl_F Pl,
783 Ben_Plip_f Plip
784 Where Pgm.Pgm_Id = P_Pgm_Id
785 and P_Effective_Date
786 Between Pgm.Effective_Start_Date
787 and Pgm.Effective_End_Date
788 and Pl.Mapping_table_Name = 'PER_GRADES'
789 and Pl.Mapping_Table_Pk_Id = P_Grd_Id
790 and P_Effective_Date
791 Between Pl.Effective_Start_Date
792 and Pl.Effective_End_Date
793 and Plip.Pgm_Id = Pgm.Pgm_id
794 and Plip.Pl_id = Pl.Pl_Id
795 and P_Effective_Date
796 Between Plip.Effective_Start_Date
797 and Plip.Effective_End_Date;
798
799 Cursor Step_Dtls Is
800 Select Plcmt.Step_id, Step.Spinal_point_id, Plcmt.Effective_Start_Date
801 From Per_Spinal_POint_Placements_f Plcmt,
802 Per_Spinal_point_Steps_F Step
803 Where Plcmt.Assignment_id = P_Assignment_Id
804 and P_Effective_Date
805 Between Plcmt.Effective_Start_Date
806 and plcmt.Effective_End_Date
807 and Step.Step_id = Plcmt.Step_Id
808 and P_Effective_Date
809 Between Step.Effective_Start_Date
810 and Step.Effective_End_Date;
811
812 Cursor Ben_Dtls(P_Pl_id In Number,
813 P_Point_id In Number) Is
814 Select Oipl.Oipl_Id, Opt.Opt_Id
815 From Ben_Opt_F Opt,
816 Ben_Oipl_F Oipl
817 Where Opt.Mapping_table_name = 'PER_SPINAL_POINTS'
818 and Opt.mapping_table_Pk_Id = P_Point_id
819 and P_Effective_Date
820 Between Opt.Effective_Start_Date
821 and Opt.Effective_End_Date
822 and Oipl.Pl_Id = P_Pl_Id
823 and Oipl.Opt_id = Opt.Opt_id
824 and P_Effective_Date
825 Between Oipl.Effective_Start_Date
826 and Oipl.Effective_End_Date;
827
828 Cursor PlanType is
829 Select Pl_typ_Id
830 From Ben_Pl_Typ_F
831 Where Opt_typ_Cd = 'GSP'
832 and P_Effective_Date
833 Between Effective_Start_Date
834 and Effective_End_Date;
835
836 CURSOR Elmt_Asgmnt_link (P_Element_Type_Id IN NUMBER) IS
837 select 'Y'
838 from pay_element_types_f pet
839 ,pay_element_links_f pel
840 ,pay_element_entries_f pee
841 where Pet.Element_type_id = P_Element_type_Id
842 and p_Effective_date
843 Between pet.effective_start_date
844 and pet.effective_end_date
845 and Pel.Element_type_Id = Pet.Element_type_Id
846 and p_Effective_date
847 Between pel.effective_start_date
848 and pel.effective_end_date
849 and pee.element_link_id = pel.element_link_id
850 and pee.assignment_id = p_assignment_id
851 and p_Effective_date
852 between pee.effective_start_date
853 and pee.effective_end_date;
854
855 Cursor Pl_Bas_rt(l_Pl_Id IN Number) Is
856 Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
857 From Ben_Acty_base_Rt_f
858 where Pl_id = l_Pl_Id
859 and P_effective_Date
860 Between Effective_Start_Date
861 and Effective_End_Date;
862
863 Cursor Opt_Bas_Rt(l_Opt_Id IN Number) Is
864 Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
865 From Ben_Acty_Base_rt_f
866 where Opt_Id = L_Opt_id
867 and P_effective_Date
868 Between Effective_Start_Date
869 and Effective_End_Date;
870
871 Cursor Lee_Rsn(P_Pgm_Id IN Number) is
872 Select Lrsn.Lee_Rsn_Id
873 From Ben_Popl_Enrt_Typ_Cycl_F Cylc,
874 Ben_Lee_Rsn_f Lrsn
875 Where Cylc.ENRT_TYP_CYCL_CD = 'L'
876 and Cylc.Pgm_Id = P_Pgm_Id
877 and P_Effective_Date
878 Between Cylc.Effective_Start_Date
879 and Cylc.Effective_End_Date
880 and Lrsn.POPL_ENRT_TYP_CYCL_ID = Cylc.POPL_ENRT_TYP_CYCL_ID
881 and P_Effective_Date
882 Between Lrsn.Effective_Start_Date
883 and Lrsn.Effective_End_Date;
884 -- 6519699
885 Cursor Curr_Enrollment (P_Pgm_Id in Number, P_Person_Id in Number, P_Ler_id in Number) is
886 Select Pl_Id, Oipl_Id
887 from Ben_Elig_per_Elctbl_Chc
888 Where Pgm_Id = P_Pgm_Id
889 and Dflt_Flag = 'Y'
890 and Elctbl_Flag = 'Y'
891 and Per_In_ler_Id = (Select Max(Per_In_ler_Id)
892 from Ben_per_in_Ler
893 Where Ler_id = P_Ler_Id
894 and Person_Id = P_Person_Id
895 and LF_EVT_OCRD_DT = ( SELECT max(LF_EVT_OCRD_DT) FROM Ben_per_in_Ler
896 where Ler_id = P_Ler_Id
897 and Person_Id = P_Person_Id
898 and PER_IN_LER_STAT_CD = 'PROCD'
899 and LF_EVT_OCRD_DT <= p_effective_date)
900 and PER_IN_LER_STAT_CD = 'PROCD'); -- Query changed for Bug 6519699
901
902 --bug 4125962
903 Cursor csr_max_lf_evt_date (P_Person_Id in Number,p_ler_id in number)
904 Is
905 select max(LF_EVT_OCRD_DT)
906 from Ben_per_in_Ler
907 Where Person_Id = P_Person_Id
908 and Ler_id = P_Ler_Id;
909
910 Cursor Csr_prev_assignment(P_Person_Id in Number,p_max_lf_evt_date in date)
911 IS
912 select assignment_id
913 from per_all_assignments_f
914 where person_id = p_person_id
915 and assignment_type ='E'
916 and primary_flag ='Y'
917 and p_max_lf_evt_date
918 between effective_start_date and effective_end_date;
919 --bug 4125962 ends
920
921 -- bug 8886934
922 cursor csr_max_step_date (p_asgid in number)
923 is
924 select max(Effective_Start_Date)
925 from per_spinal_point_placements_f
926 where assignment_id = p_asgid;
927
928 l_sp_max_date date;
929
930
931 /*
932 Cursor Yr_prd (P_Pl_Id IN Number, P_Bg_Id IN Number) Is
933 SELECT pyp.yr_perd_id,
934 pyp.popl_yr_perd_id,
935 yp.start_date,
936 yp.end_date
937 FROM ben_popl_yr_perd pyp,
938 ben_yr_perd yp
939 WHERE pyp.pl_id = P_Pl_Id
940 AND pyp.yr_perd_id = yp.yr_perd_id
941 AND pyp.business_group_id = P_Bg_Id
942 AND P_Effective_Date
943 BETWEEN yp.start_date AND yp.end_date
944 AND yp.business_group_id = P_Bg_Id; */
945
946 Cursor Yr_Prd is
947 Select Yr_Perd_id
948 From Ben_Yr_Perd
949 Where P_Effective_Date
950 Between Start_Date and End_Date;
951
952 Cursor Dflt_GrdLdr is
953 Select Pgm_Id
954 From Ben_PGM_F
955 Where DFLT_PGM_FLAG = 'Y'
956 and Pgm_Typ_Cd = 'GSP'
957 and P_Effective_Date
958 Between Effective_Start_Date
959 and Effective_End_Date
960 and Business_Group_id = Hr_general.get_business_group_id;
961
962 -- Bug 13532919
963 -- Bug 16285614
964 cursor csr_step_val
965 (p_effective_date date,
966 p_assignment_id per_all_assignments_f.assignment_id%type) is
967 SELECT grd.value
968 FROM pay_grade_rules_f grd
969 WHERE p_effective_date between grd.effective_start_date AND grd.effective_end_date
970
971 AND grd.grade_or_spinal_point_id IN
972 (
973 SELECT steps.spinal_point_id
974 FROM per_spinal_point_steps_f steps
975 WHERE p_effective_date between steps.effective_start_date AND steps.effective_end_date
976
977 AND steps.step_id IN
978 (
979 SELECT plcmt.step_id
980 FROM per_spinal_point_placements_f plcmt
981 WHERE p_effective_date between plcmt.effective_start_date AND plcmt.effective_end_date
982
983 AND plcmt.assignment_id = p_assignment_id
984 )
985 );
986
987
988 cursor csr_salary (p_asg number, p_efd date) is
989 select proposed_salary_n
990 from per_pay_proposals
991 where assignment_id = p_asg
992 and p_efd between change_date and nvl(date_to , hr_api.g_eot) ;
993
994
995 cursor csr_annual_factor is
996 select pay_annualization_factor
997 from per_pay_bases
998 where pay_basis_id in (
999 select pay_basis_id
1000 from per_all_assignments_f
1001 where assignment_id= P_Assignment_id
1002 and P_Effective_Date between effective_start_date and effective_end_date);
1003
1004
1005 l_annual_factor number;
1006 l_sal_proposal number;
1007 l_rate_value number;
1008 l_sal_value1 number;
1009 l_sal_value2 number;
1010
1011 L_person_Id Per_All_People_F.Person_Id%TYPE;
1012 l_Ler_id Ben_ler_F.Ler_Id%TYPE;
1013 L_PTNL_LER_FOR_PER_ID Ben_Ptnl_Ler_For_Per.PTNL_LER_FOR_PER_ID%TYPE;
1014 l_Ptnl_Ovn Ben_Ptnl_Ler_For_Per.Object_Version_Number%TYPE;
1015 l_PER_IN_LER_ID Ben_Per_In_Ler.Per_In_Ler_Id%TYPE;
1016 L_Pil_Ovn Ben_Per_In_Ler.Object_version_Number%TYPE;
1017 L_BG_Id Per_All_Assignments_F.Business_Group_id%TYPE;
1018 l_prev_assgt_id Per_All_Assignments_F.assignment_id%TYPE;
1019 l_Grade_Id Per_Grades.Grade_Id%TYPE;
1020 l_Pgm_Id Ben_Pgm_F.Pgm_Id%TYPE;
1021 l_Step_Id Per_Spinal_Point_Steps_f.Step_Id%TYPE;
1022 l_Point_id Per_Spinal_POints.Spinal_Point_Id%TYPE;
1023 l_PROCD_DT DATE;
1024 l_STRTD_DT DATE;
1025 l_VOIDD_DT Date;
1026 l_Step_Exists Varchar2(1) := 'N';
1027 L_Pl_id Ben_Pl_F.Pl_Id%TYPE;
1028 L_plip_Id Ben_PLip_F.Plip_Id%TYPE;
1029 L_Oipl_Id Ben_Oipl_F.Oipl_Id%TYPE;
1030 L_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
1031 L_Oipl_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
1032 l_Pl_Typ_Id Ben_Pl_Typ_F.Pl_Typ_Id%TYPE;
1033 l_Elctbl_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
1034 l_oipl_Elctbl_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
1035 l_Prog_style Ben_Pgm_F.Enrt_Cd%TYPE;
1036 L_Prog_opt Ben_Pgm_F.DFLT_STEP_CD%TYPE;
1037 L_Elctbl_Flag Varchar2(1) := 'N';
1038 L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
1039 L_pay_basis_id per_pay_bases.pay_basis_id%TYPE;
1040 l_Dflt_Element_typ_Id Ben_Pgm_F.DFLT_ELEMENT_TYPE_ID%TYPE;
1041 L_Enrt_Rt_Id Ben_Enrt_Rt.Enrt_Rt_Id%TYPE;
1042 l_Cur_Sal Ben_Enrt_Rt.Val%TYPE;
1043 l_Rt_Typ_Cd Ben_Enrt_Rt.Rt_Typ_Cd%TYPE;
1044 L_Rt_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
1045 L_Acty_Base_rt_Id Ben_Acty_Base_Rt.Acty_Base_rt_Id%TYPE;
1046 l_Enrt_Rt_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
1047 l_Opt_id Ben_Opt_F.Opt_Id%TYPE;
1048 l_Entr_Ann_Val_Flag Ben_Acty_Base_rt_F.Entr_Ann_Val_Flag%TYPE;
1049 L_Lee_Rsn_Id Ben_Lee_Rsn_f.Lee_Rsn_Id%TYPE;
1050 l_yr_perd_id ben_popl_yr_perd.yr_perd_id%TYPE;
1051 L_popl_yr_perd_id ben_popl_yr_perd.popl_yr_perd_id%TYPE;
1052 l_start_date ben_yr_perd.start_date%TYPE;
1053 l_end_date ben_yr_perd.end_date%TYPE;
1054 l_Element_Link_Id Pay_Element_Links_f.Element_Link_Id%TYPE;
1055 l_Curr_Pl_Id Ben_Pl_F.Pl_Id%TYPE;
1056 l_Curr_Oipl_Id Ben_Oipl_F.Oipl_Id%TYPE;
1057 l_New_Enrlmt_Dt Date;
1058 l_max_lf_evt_date Date;
1059
1060 Begin
1061
1062 g_debug := hr_utility.debug_enabled;
1063
1064 Open Person_Info;
1065 Fetch Person_Info Into L_Person_Id, L_BG_Id , L_Grade_Id, L_pay_basis_id, l_Pgm_Id, l_New_Enrlmt_Dt;
1066 Close Person_Info;
1067
1068 if g_debug then
1069 hr_utility.set_location(' Inside Override Eligibility ', 10);
1070 End if;
1071
1072 If L_Grade_Id is NULL then
1073 /* Grade Not defined for the assignment
1074 fnd_message.set_name('PQH','PQH_GSP_GRDNOTLNKD_ASSGT');
1075 fnd_message.raise_error;
1076 -- sgoyal commented out so that assgt for which no grade is there don't report this warning always
1077 */
1078 P_Elig_Per_Elctbl_Chc_Id := NULL;
1079 return;
1080 End If;
1081
1082 If l_Pgm_Id is NULL then
1083 Open Dflt_grdLdr;
1084 Fetch Dflt_GrdLdr into l_pgm_Id;
1085 Close Dflt_Grdldr;
1086 End If;
1087
1088 If l_Pgm_id is NULL Then
1089 P_Elig_Per_Elctbl_Chc_Id := NULL;
1090 Return;
1091 End If;
1092
1093 l_Curr_Pl_Id := NULL;
1094 l_Curr_Oipl_Id := NULL;
1095
1096 Open Life_Event_Type(L_BG_Id);
1097 Fetch Life_Event_Type into l_Ler_Id;
1098 Close Life_Event_Type;
1099
1100 Open Curr_Enrollment(L_Pgm_Id, l_Person_Id, l_ler_id);
1101 Fetch Curr_Enrollment into l_Curr_Pl_Id, l_Curr_Oipl_Id;
1102 Close Curr_Enrollment;
1103
1104 ----4125962 Begins
1105
1106 OPEN csr_max_lf_evt_date(l_Person_Id,l_Ler_id);
1107 Fetch csr_max_lf_evt_date into l_max_lf_evt_date;
1108 Close csr_max_lf_evt_date;
1109
1110 Open Csr_prev_assignment(l_Person_Id,l_max_lf_evt_date);
1111 Fetch Csr_prev_assignment into l_prev_assgt_id;
1112 Close Csr_prev_assignment;
1113
1114 -- Ends
1115
1116 --8886934
1117 Open csr_max_step_date (P_Assignment_id );
1118 Fetch csr_max_step_date into l_sp_max_date;
1119 Close csr_max_step_date;
1120 --8886934
1121
1122 /* sgoyal
1123 if grade ladder is null for assignment, we should check default grade ladder
1124 if def GL is not set, we should return here
1125 */
1126
1127 /*
1128 If l_Pgm_Id is NULL and P_Called_From = 'A' then
1129 Grade Ladder Not Assigned to the Assignment
1130 fnd_message.set_name('PQH','PQH_GSP_NO_GRDLDR');
1131 fnd_message.raise_error;
1132 End If;
1133 */
1134
1135
1136
1137 if g_debug then
1138 hr_utility.set_location(' Pgm_Id : ' || l_Pgm_Id, 20);
1139 hr_utility.set_location(' Grade Id : ' || L_Grade_Id, 30);
1140 End if;
1141
1142 Open Ben_Grd_Dtls(l_Pgm_Id, L_Grade_Id);
1143 Fetch Ben_Grd_Dtls into L_Pl_id, l_plip_Id , l_Prog_style, L_Update_Salary_Cd, l_Dflt_Element_typ_Id;
1144 Close Ben_Grd_Dtls;
1145
1146 If l_Pl_Id Is Null Then
1147 /* Plan is not not linked to the corresponding Grade */
1148 fnd_message.set_name('PQH','PQH_GSP_PLN_NOTLNKD_TO_GRD');
1149 fnd_message.raise_error;
1150 End If;
1151
1152 If l_Prog_style = 'PQH_GSP_NP' Then
1153 P_Elig_Per_Elctbl_Chc_Id := NULL;
1154 Return;
1155 elsif l_Prog_style is NULL then
1156 fnd_message.set_name('PQH','PQH_GSP_PRGSTYLE_NOT_SET');
1157 fnd_message.raise_error;
1158 End If;
1159
1160 If l_Prog_style = 'PQH_GSP_GP' and l_PL_Id = Nvl(L_Curr_Pl_Id, -999) and nvl(l_prev_assgt_id,p_assignment_id) = p_assignment_id Then --4125962
1161 if g_debug then
1162 hr_utility.set_location(' Enrollment Exists 1 .. leaving ' || l_Prog_style, 40);
1163 End if;
1164 P_Elig_Per_Elctbl_Chc_Id := NULL;
1165 Return;
1166 End If;
1167
1168 If L_Update_Salary_Cd = 'SALARY_BASIS' and L_pay_basis_id is NULL Then
1169 /* Grade Ladder is defined for Salary basis and Pay Basis is not attached to the assignment */
1170 fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
1171 fnd_message.raise_error;
1172
1173 ElsIf L_Update_Salary_Cd = 'SALARY_ELEMENT' then
1174
1175 /* Grade Ladder uses Salary Element, but Default Salary Element type is not defined */
1176 If l_Dflt_Element_typ_Id is NULL Then
1177 fnd_message.set_name('PQH','PQH_GSP_DFLT_ELMNT_NOTDFND');
1178 fnd_message.raise_error;
1179 End If;
1180
1181 l_Element_Link_Id := hr_entry_api.get_link
1182 (P_Assignment_id
1183 ,l_Dflt_Element_typ_Id
1184 ,P_Effective_Date);
1185
1186 If l_Element_Link_Id is NULL Then
1187 fnd_message.set_name('PQH','PQH_GSP_ELMNT_NOT_LNKD');
1188 fnd_message.raise_error;
1189 End If;
1190 End If;
1191
1192
1193 If l_Prog_style in ('PQH_GSP_SP','PQH_GSP_GSP','MINSALINCR','MINSTEP','NOSTEP') Then
1194 Open Step_Dtls;
1195 Fetch Step_Dtls Into L_Step_Id, l_Point_id, l_New_Enrlmt_Dt;
1196 Close Step_Dtls;
1197 /* Step not defined for Assignment */
1198 If l_Step_Id is NULL Then
1199 fnd_message.set_name('PQH','PQH_GSP_NO_STEP');
1200 fnd_message.raise_error;
1201 Else
1202 Open Ben_Dtls(L_Pl_id,
1203 L_Point_Id);
1204 Fetch Ben_Dtls Into L_Oipl_Id, l_Opt_id;
1205 Close Ben_Dtls;
1206 If L_Oipl_Id is NULL then
1207 /* Oipl not linked to Step */
1208 fnd_message.set_name('PQH','PQH_GSP_OIPL_NOTLNKD_TO_STEP');
1209 fnd_message.raise_error;
1210 End If;
1211 End If;
1212 L_Elctbl_Flag := 'N';
1213
1214 Open Opt_Bas_rt(l_Opt_Id);
1215 Fetch Opt_Bas_Rt into l_ACTY_BASE_RT_ID, l_Rt_Typ_cd, l_Entr_Ann_Val_Flag;
1216 Close Opt_Bas_Rt;
1217 if g_debug then
1218 hr_utility.set_location(' l_Pgm_Id ' || l_Pgm_Id, 50);
1219 hr_utility.set_location(' L_Pl_id ' || L_Pl_id, 60);
1220 hr_utility.set_location(' l_Oipl_Id ' || l_Oipl_Id, 70);
1221 hr_utility.set_location(' L_Person_Id ' || L_Person_Id, 80);
1222 End if;
1223 If L_PL_Id = Nvl(l_Curr_PL_Id,-999) and l_Oipl_id = Nvl(l_Curr_oipl_Id,-9999) and nvl(l_prev_assgt_id,p_assignment_id) = p_assignment_id Then --4125962
1224
1225 --8886934
1226 hr_utility.set_location(' l_sp_max_date ' || l_sp_max_date, 81);
1227 hr_utility.set_location(' l_max_lf_evt_date ' || l_max_lf_evt_date, 81);
1228 -- 9557105
1229 -- Bug 13532919
1230 /*
1231 hr_utility.set_location(' Enrollment Exists 2 .. leaving ' || l_Prog_style, 90);
1232
1233 P_Elig_Per_Elctbl_Chc_Id := NULL;
1234 Return;
1235
1236 end if;
1237 */
1238
1239 open csr_step_val (p_effective_date,p_assignment_id);
1240 fetch csr_step_val into l_rate_value;
1241 close csr_step_val;
1242
1243 open csr_salary (p_assignment_id,p_effective_date);
1244 fetch csr_salary into l_sal_proposal;
1245 close csr_salary;
1246
1247 open csr_annual_factor;
1248 fetch csr_annual_factor into l_annual_factor;
1249 close csr_annual_factor;
1250
1251 l_sal_value1 :=l_rate_value*l_annual_factor;
1252 l_sal_value2 :=l_sal_proposal*l_annual_factor;
1253
1254 hr_utility.set_location(' p_effective_date ' || p_effective_date, 80);
1255 hr_utility.set_location(' L_Person_Id ' || L_Person_Id, 80);
1256 hr_utility.set_location(' p_assignment_id ' || p_assignment_id, 80);
1257 hr_utility.set_location(' l_rate_value ' || l_rate_value, 80);
1258 hr_utility.set_location(' l_sal_proposal ' || l_sal_proposal, 80);
1259 hr_utility.set_location(' l_sal_value2 ' || l_sal_value2, 80);
1260 hr_utility.set_location(' l_sal_value1 ' || l_sal_value1, 80);
1261 hr_utility.set_location(' l_annual_factor ' || l_annual_factor, 80);
1262
1263 l_rate_value := Pqh_Gsp_Utility.PGM_TO_BASIS_CONVERSION
1264 (P_Pgm_Id => l_Pgm_Id
1265 ,P_EFFECTIVE_DATE => P_Effective_Date
1266 ,P_AMOUNT => l_rate_value
1267 ,P_ASSIGNMENT_ID => p_assignment_id);
1268
1269
1270 hr_utility.set_location(' l_rate_value ' || l_rate_value, 81);
1271 hr_utility.set_location(' l_sal_proposal ' || l_sal_proposal, 81);
1272
1273 if l_rate_value=l_sal_proposal then
1274
1275 if g_debug then
1276 hr_utility.set_location(' Enrollment Exists 2 .. leaving ' || l_Prog_style, 90);
1277 End If;
1278 P_Elig_Per_Elctbl_Chc_Id := NULL;
1279 Return;
1280 End If;
1281
1282 end if;
1283
1284 Else
1285
1286 L_Elctbl_Flag := 'Y';
1287 Open Pl_Bas_rt(l_Pl_Id);
1288 Fetch Pl_Bas_rt into l_ACTY_BASE_RT_ID, l_Rt_Typ_cd, l_Entr_Ann_Val_Flag;
1289 Close Pl_Bas_rt;
1290
1291 End If;
1292
1293
1294 If L_New_Enrlmt_Dt is NULL or P_Date_track_Mode <> 'CORRECTION' Then
1295 l_New_Enrlmt_Dt := P_Effective_Date;
1296 End If;
1297
1298
1299 Open Plantype;
1300 Fetch Plantype into l_Pl_Typ_Id;
1301 Close Plantype;
1302
1303 Open Lee_Rsn(l_Pgm_Id);
1304 Fetch Lee_Rsn into l_LEE_RSN_ID;
1305 Close Lee_Rsn;
1306
1307 If l_LEE_RSN_ID is NULL Then
1308 fnd_message.set_name('PQH','PQH_GSP_LIF_RSN_NOT_DFND');
1309 fnd_message.raise_error;
1310 End If;
1311
1312 /* Create Potential Life Events */
1313 Ben_Ptnl_Ler_For_per_Api.CREATE_PTNL_LER_FOR_PER_PERF
1314 (P_PTNL_LER_FOR_PER_ID => L_PTNL_LER_FOR_PER_ID
1315 ,P_LF_EVT_OCRD_DT => l_New_Enrlmt_Dt
1316 ,P_PTNL_LER_FOR_PER_STAT_CD => 'PROCD'
1317 ,P_LER_ID => l_Ler_Id
1318 ,P_PERSON_ID => L_Person_Id
1319 ,P_BUSINESS_GROUP_ID => L_BG_Id
1320 ,P_OBJECT_VERSION_NUMBER => l_Ptnl_Ovn
1321 ,P_EFFECTIVE_DATE => l_New_Enrlmt_Dt);
1322
1323 if g_debug then
1324 hr_utility.set_location(' Created Potential Life Event records ', 100);
1325 hr_utility.set_location(' L_PTNL_LER_FOR_PER_ID : ' || L_PTNL_LER_FOR_PER_ID, 110);
1326 End If;
1327
1328 /* Create Life Event for the Above created Potential Life Event */
1329 Ben_Person_Life_Event_api.CREATE_PERSON_LIFE_EVENT_PERF
1330 (P_PER_IN_LER_ID => l_PER_IN_LER_ID
1331 ,P_PER_IN_LER_STAT_CD => 'STRTD'
1332 ,P_LF_EVT_OCRD_DT => l_New_Enrlmt_Dt
1333 ,P_PTNL_LER_FOR_PER_ID => L_PTNL_LER_FOR_PER_ID
1334 ,P_PROCD_DT => L_PROCD_DT
1335 ,P_STRTD_DT => L_STRTD_DT
1336 ,P_VOIDD_DT => L_VOIDD_DT
1337 ,P_LER_ID => L_Ler_Id
1338 ,P_PERSON_ID => L_Person_Id
1339 ,P_BUSINESS_GROUP_ID => L_BG_Id
1340 ,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
1341 ,P_EFFECTIVE_DATE => l_New_Enrlmt_Dt);
1342
1343 if g_debug then
1344 hr_utility.set_location(' Created PIL ', 120);
1345 hr_utility.set_location(' l_PER_IN_LER_ID : ' || l_PER_IN_LER_ID, 130);
1346 End If;
1347
1348 /* Create Electable Choice Records */
1349 /* For Grade */
1350
1351 if g_debug then
1352 hr_utility.set_location(' Attempting to create Elig_per ', 140);
1353 hr_utility.set_location(' Business_Group_Id :' || l_Bg_Id, 150);
1354 end if;
1355 -- sgoyal plan year period should be in place now, please give it a try
1356 /*
1357 Open Yr_prd(L_Pl_Id, l_Bg_Id);
1358 Fetch Yr_prd into l_yr_perd_id, L_popl_yr_perd_id, l_start_date, l_end_date;
1359 Close Yr_prd; */
1360
1361 Open Yr_Prd;
1362 Fetch Yr_Prd into l_Yr_Perd_Id;
1363 Close Yr_Prd;
1364
1365 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
1366 (P_ELIG_PER_ELCTBL_CHC_ID => L_Elig_Per_Elctbl_Chc_Id
1367 ,P_ENRT_CVG_STRT_DT_CD => l_New_Enrlmt_Dt
1368 ,P_DFLT_FLAG => 'Y'
1369 ,P_ELCTBL_FLAG => L_Elctbl_Flag
1370 ,P_PL_ID => l_Pl_Id
1371 ,P_PGM_ID => l_Pgm_Id
1372 ,P_PLIP_ID => l_plip_Id
1373 ,P_PGM_TYP_CD => 'GSP'
1374 ,P_PL_TYP_ID => l_Pl_Typ_Id
1375 ,P_PER_IN_LER_ID => l_PER_IN_LER_ID
1376 ,P_YR_PERD_ID => l_yr_perd_id
1377 ,P_Enrt_Cvg_Strt_Dt => l_New_Enrlmt_Dt
1378 ,P_COMP_LVL_CD => 'PLAN'
1379 ,P_LEE_RSN_ID => L_LEE_RSN_ID
1380 ,P_AUTO_ENRT_FLAG => 'Y'
1381 ,P_BUSINESS_GROUP_ID => l_Bg_Id
1382 ,P_ELIG_FLAG => 'N'
1383 ,P_OBJECT_VERSION_NUMBER => l_Elctbl_Ovn
1384 ,P_EFFECTIVE_DATE => l_New_Enrlmt_Dt);
1385
1386 L_Rt_Elig_Per_Elctbl_Chc_Id := L_Elig_Per_Elctbl_Chc_Id;
1387
1388 if g_debug then
1389 hr_utility.set_location(' Created Elig_per ', 160);
1390 hr_utility.set_location(' L_Elig_Per_Elctbl_Chc_Id : ' || L_Elig_Per_Elctbl_Chc_Id, 170);
1391 End If;
1392
1393 If l_Prog_style in ('PQH_GSP_SP','PQH_GSP_GSP','MINSALINCR','MINSTEP','NOSTEP') Then
1394
1395 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
1396 (P_ELIG_PER_ELCTBL_CHC_ID => L_Oipl_Elig_Per_Elctbl_Chc_Id
1397 ,P_ENRT_CVG_STRT_DT_CD => l_New_Enrlmt_Dt
1398 ,P_DFLT_FLAG => 'Y'
1399 ,P_ELCTBL_FLAG => 'Y'
1400 ,P_PL_ID => l_Pl_Id
1401 ,P_PGM_ID => l_Pgm_Id
1402 ,P_PLIP_ID => l_plip_Id
1403 ,P_OIPL_ID => l_Oipl_Id
1404 ,P_PGM_TYP_CD => 'GSP'
1405 ,P_PL_TYP_ID => l_Pl_Typ_Id
1406 ,P_Enrt_Cvg_Strt_Dt => l_New_Enrlmt_Dt
1407 ,P_YR_PERD_ID => l_yr_perd_id
1408 ,P_PER_IN_LER_ID => l_PER_IN_LER_ID
1409 ,P_COMP_LVL_CD => 'OIPL'
1410 ,P_LEE_RSN_ID => L_LEE_RSN_ID
1411 ,P_AUTO_ENRT_FLAG => 'Y'
1412 ,P_BUSINESS_GROUP_ID => l_Bg_Id
1413 ,P_ELIG_FLAG => 'N'
1414 ,P_OBJECT_VERSION_NUMBER => l_Oipl_Elctbl_Ovn
1415 ,P_EFFECTIVE_DATE => l_New_Enrlmt_Dt);
1416
1417 L_Rt_Elig_Per_Elctbl_Chc_Id := L_Oipl_Elig_Per_Elctbl_Chc_Id;
1418 End If;
1419 If L_Update_Salary_Cd is NULL Then
1420 fnd_message.set_name('PQH','PQH_GSP_POSTSTYL_NOT_SET');
1421 fnd_message.raise_error;
1422 End If;
1423 If P_Called_From <> 'BM' Then
1424 if g_debug then
1425 hr_utility.set_location('L_Update_Salary_Cd :' || L_Update_Salary_Cd, 180);
1426 End If;
1427 If L_Update_Salary_Cd in ('SALARY_BASIS','SALARY_ELEMENT') Then
1428
1429 /* Not Batch Mode and Salary Update is Set for the Grade ladder */
1430 if g_debug then
1431 hr_utility.set_location(' Determine Rates ', 190);
1432 End If;
1433
1434 ben_env_object.init(p_business_group_id => l_Bg_Id,
1435 p_effective_date => l_New_Enrlmt_Dt,
1436 p_thread_id => 1,
1437 p_chunk_size => 1,
1438 p_threads => 1,
1439 p_max_errors => 1,
1440 p_benefit_action_id => null);
1441
1442 ben_env_object.setenv(P_LF_EVT_OCRD_DT => P_Effective_Date);
1443 ben_env_object.g_global_env_rec.mode_cd := 'G';
1444 Ben_determine_rates.Main
1445 (P_EFFECTIVE_DATE => l_New_Enrlmt_Dt
1446 ,P_LF_EVT_OCRD_DT => l_New_Enrlmt_Dt
1447 ,P_PERSON_ID => L_Person_Id
1448 ,P_PER_IN_LER_ID => l_PER_IN_LER_ID
1449 ,p_elig_per_elctbl_chc_id => L_Rt_Elig_Per_Elctbl_Chc_Id);
1450 End If;
1451
1452 if g_debug then
1453 hr_utility.set_location(' Determined Rates ', 200);
1454 End if;
1455
1456 Else
1457
1458 l_Cur_Sal := Pqh_gsp_utility.Get_Cur_Sal
1459 (P_Assignment_id => P_Assignment_id
1460 ,P_Effective_Date => P_Effective_date);
1461
1462 ben_Enrollment_Rate_api.CREATE_PERF_ENROLLMENT_RATE
1463 (P_ENRT_RT_ID => L_Enrt_Rt_Id
1464 ,P_ACTY_TYP_CD => 'GSPSA'
1465 ,P_TX_TYP_CD => 'NOTAPPLICABLE'
1466 ,P_DFLT_FLAG => 'Y'
1467 ,P_VAL => l_Cur_Sal
1468 ,P_RT_TYP_CD => l_Rt_Typ_Cd
1469 ,P_ELIG_PER_ELCTBL_CHC_ID => L_Rt_Elig_Per_Elctbl_Chc_Id
1470 ,P_Entr_Ann_Val_Flag => l_Entr_Ann_Val_Flag
1471 ,P_Business_Group_Id => l_Bg_Id
1472 ,P_ACTY_BASE_RT_ID => L_Acty_Base_rt_Id
1473 ,P_OBJECT_VERSION_NUMBER => l_Enrt_Rt_Ovn
1474 ,P_Effective_Date => P_Effective_Date);
1475
1476 End If;
1477
1478 /* Close the person Life Event as Processed */
1479 if g_debug then
1480 hr_utility.set_location(' Close Life Event ', 210);
1481 End if;
1482
1483 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
1484 (P_PER_IN_LER_ID => l_PER_IN_LER_ID
1485 ,P_PER_IN_LER_STAT_CD => 'PROCD'
1486 ,P_PROCD_DT => L_PROCD_DT
1487 ,P_STRTD_DT => L_STRTD_DT
1488 ,P_VOIDD_DT => L_VOIDD_DT
1489 ,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
1490 ,P_EFFECTIVE_DATE => l_New_Enrlmt_Dt);
1491
1492 if g_debug then
1493 hr_utility.set_location(' Closed Life Event ', 220);
1494 End If;
1495
1496 P_Elig_Per_Elctbl_Chc_Id := L_Rt_Elig_Per_Elctbl_Chc_Id;
1497 Exception
1498 When Others Then
1499 Raise;
1500 End Override_Eligibility;
1501
1502 /**********************************************************************/
1503 /************************** Create Enrollment *************************/
1504 /**********************************************************************/
1505
1506 Procedure Create_Enrollment
1507 (P_Elig_Per_Elctbl_Chc_Id IN Number
1508 ,P_Person_id IN Number
1509 ,P_Progression_Style IN Varchar2
1510 ,P_Effective_Date IN Date
1511 ,P_PRTT_ENRT_RSLT_ID IN OUT NOCOPY Number
1512 ,P_Status OUT NOCOPY Varchar2) is
1513
1514 Cursor Enrolment is
1515 Select Elctbl.PRTT_ENRT_RSLT_ID, Elctbl.Enrt_Cvg_Strt_Dt , Elctbl.Crntly_Enrd_Flag, Rate.ENRT_RT_ID, Rate.ENRT_BNFT_ID,
1516 Rate.Val , Elctbl.Business_Group_Id, Elctbl.Per_in_Ler_Id , Rate.ANN_VAL
1517 From Ben_Elig_Per_Elctbl_Chc Elctbl,
1518 Ben_Enrt_Rt Rate
1519 Where Elctbl.ELIG_PER_ELCTBL_CHC_ID = P_ELIG_PER_ELCTBL_CHC_ID
1520 and Rate.ELIG_PER_ELCTBL_CHC_ID = Elctbl.ELIG_PER_ELCTBL_CHC_ID;
1521
1522 l_PRTT_ENRT_RSLT_ID BEN_PRTT_ENRT_RSLT_F.PRTT_ENRT_RSLT_ID%TYPE;
1523 l_Ovn_No BEN_PRTT_ENRT_RSLT_F.Object_Version_Number%TYPE;
1524 L_PRTT_ENRT_INTERIM_ID BEN_PRTT_ENRT_RSLT_F.PRTT_ENRT_RSLT_ID%TYPE;
1525 l_PRTT_RT_VAL_ID1 BEN_PRTT_RT_VAL.PRTT_RT_VAL_ID%TYPE;
1526 l_PRTT_RT_VAL_ID_Cmn BEN_PRTT_RT_VAL.PRTT_RT_VAL_ID%TYPE;
1527 l_Datetrack_Mode Varchar2(25);
1528 l_suspend_flag Varchar2(1) := 'N';
1529 l_EFFECTIVE_START_DATE Date;
1530 L_EFFECTIVE_END_DATE Date;
1531 L_DPNT_ACTN_WARNING Boolean;
1532 L_BNF_ACTN_WARNING Boolean;
1533 L_CTFN_ACTN_WARNING Boolean;
1534 begin
1535 g_debug := hr_utility.debug_enabled;
1536
1537 For Enrl_Rec in Enrolment
1538 Loop
1539
1540 l_PRTT_ENRT_RSLT_ID := Enrl_Rec.PRTT_ENRT_RSLT_ID;
1541
1542 If Enrl_Rec.Crntly_Enrd_Flag = 'N' Then
1543 l_Datetrack_Mode := hr_api.g_Insert;
1544 Else
1545 l_Datetrack_Mode := hr_api.g_update;
1546 End If;
1547
1548 Ben_Election_Information.ELECTION_INFORMATION
1549 (P_ELIG_PER_ELCTBL_CHC_ID => P_Elig_Per_Elctbl_Chc_Id
1550 ,P_PRTT_ENRT_RSLT_ID => l_PRTT_ENRT_RSLT_ID
1551 ,P_EFFECTIVE_DATE => P_Effective_Date
1552 ,P_ENRT_MTHD_CD => P_Progression_Style
1553 ,P_ENRT_BNFT_ID => NULL
1554 ,P_BNFT_VAL => NULL
1555 ,P_ENRT_CVG_STRT_DT => Enrl_Rec.Enrt_Cvg_Strt_Dt
1556 ,P_ENRT_RT_ID1 => Enrl_Rec.Enrt_Rt_Id
1557 ,P_PRTT_RT_VAL_ID1 => l_PRTT_RT_VAL_ID1
1558 ,P_PRTT_RT_VAL_ID2 => l_PRTT_RT_VAL_ID_Cmn
1559 ,P_PRTT_RT_VAL_ID3 => l_PRTT_RT_VAL_ID_Cmn
1560 ,P_PRTT_RT_VAL_ID4 => l_PRTT_RT_VAL_ID_Cmn
1561 ,P_PRTT_RT_VAL_ID5 => l_PRTT_RT_VAL_ID_Cmn
1562 ,P_PRTT_RT_VAL_ID6 => l_PRTT_RT_VAL_ID_Cmn
1563 ,P_PRTT_RT_VAL_ID7 => l_PRTT_RT_VAL_ID_Cmn
1564 ,P_PRTT_RT_VAL_ID8 => l_PRTT_RT_VAL_ID_Cmn
1565 ,P_PRTT_RT_VAL_ID9 => l_PRTT_RT_VAL_ID_Cmn
1566 ,P_PRTT_RT_VAL_ID10 => l_PRTT_RT_VAL_ID_Cmn
1567 ,P_RT_VAL1 => Enrl_Rec.Val
1568 ,P_ANN_RT_VAL1 => Enrl_Rec.ANN_VAL
1569 ,P_DATETRACK_MODE => l_Datetrack_Mode
1570 ,P_SUSPEND_FLAG => l_suspend_flag
1571 ,P_EFFECTIVE_START_DATE => l_EFFECTIVE_START_DATE
1572 ,P_EFFECTIVE_END_DATE => L_EFFECTIVE_END_DATE
1573 ,P_OBJECT_VERSION_NUMBER => l_OVN_No
1574 ,P_PRTT_ENRT_INTERIM_ID => L_PRTT_ENRT_INTERIM_ID
1575 ,P_BUSINESS_GROUP_ID => Enrl_Rec.Business_group_Id
1576 ,P_DPNT_ACTN_WARNING => L_DPNT_ACTN_WARNING
1577 ,P_BNF_ACTN_WARNING => L_BNF_ACTN_WARNING
1578 ,P_CTFN_ACTN_WARNING => L_CTFN_ACTN_WARNING);
1579
1580 Ben_Proc_Common_Enrt_Rslt.PROCESS_POST_RESULTS
1581 (P_PERSON_ID => P_Person_Id
1582 ,P_ENRT_MTHD_CD => P_Progression_Style
1583 ,P_EFFECTIVE_DATE => P_Effective_Date
1584 ,P_BUSINESS_GROUP_ID => Enrl_Rec.Business_Group_Id
1585 ,P_PER_IN_LER_ID => Enrl_Rec.Per_In_Ler_Id);
1586
1587 End Loop;
1588 End Create_Enrollment;
1589
1590 --
1591 -- Procedure to end date prev salary element entry when grade ladder change.
1592 --
1593 Procedure end_prev_gsp_payment(
1594 p_assignment_id IN Number
1595 ,p_business_group_id IN NUMBER
1596 ,P_Effective_Date IN Date
1597 ,P_Elig_per_Elctbl_Chc_Id IN Number
1598 ,p_current_rate_change_dt IN Date) is
1599
1600 --
1601 l_curr_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
1602 l_last_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
1603 l_person_id ben_per_in_ler.person_id%type;
1604 l_prev_sal_change_dt ben_enrt_rt.rt_strt_dt%type;
1605 l_max_rt_change_dt ben_enrt_rt.rt_strt_dt%type;
1606 l_prev_grade_ladder_id ben_elig_per_elctbl_chc.pgm_id%type;
1607 l_prev_sal ben_enrt_rt.val%type;
1608 L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
1609 L_DFLT_INPUT_VALUE_ID Ben_Pgm_F.DFLT_INPUT_VALUE_ID%TYPE;
1610 L_DFLT_ELEMENT_TYPE_ID Ben_Pgm_F.DFLT_ELEMENT_TYPE_ID%TYPE;
1611 l_DFLT_STEP_CD Ben_Pgm_F.DFLT_STEP_CD%TYPE;
1612 L_Element_Entry_ID pay_element_entries_f.Element_Entry_Id%TYPE;
1613 L_Ele_Ovn pay_element_entries_f.Object_Version_Number%TYPE;
1614 l_Del_proposal_Id Per_Pay_proposals.Pay_proposal_Id%TYPE;
1615 l_Del_Proposal_Ovn per_pay_Proposals.Object_version_Number%TYPE;
1616 l_proposal_date_to per_pay_Proposals.date_to%type; -- bug 6856664
1617
1618 l_del_warn boolean;
1619 L_DATE_TRACK_MODE Varchar2(25);
1620 l_inv_next_sal_date_warning boolean;
1621 l_proposed_salary_warning boolean;
1622 l_approved_warning boolean;
1623 l_payroll_warning boolean;
1624 --
1625 Cursor Pgm_Dtl(P_Pgm_Id In Number,P_effective_Date in date) is
1626 Select Update_Salary_Cd, DFLT_INPUT_VALUE_ID, DFLT_ELEMENT_TYPE_ID, DFLT_STEP_CD
1627 From ben_Pgm_F
1628 Where Pgm_id = P_Pgm_Id
1629 and P_effective_Date
1630 Between Effective_Start_Date
1631 and Effective_End_Date;
1632
1633 --
1634 CURSOR Elmt_Entry (p_assignment_id IN NUMBER, P_Business_group_Id IN Number, p_Effective_Date IN Date) IS
1635 select pee.Element_Entry_Id, pee.Object_version_Number
1636 from pay_element_types_f pet
1637 ,pay_element_links_f pel
1638 ,pay_element_entries_f pee
1639 where Pet.Element_type_id = L_DFLT_ELEMENT_TYPE_ID
1640 and p_Effective_date
1641 Between pet.effective_start_date
1642 and pet.effective_end_date
1643 and Pel.Element_type_Id = Pet.Element_type_Id
1644 and p_Effective_date
1645 Between pel.effective_start_date
1646 and pel.effective_end_date
1647 and pee.element_link_id = pel.element_link_id
1648 and pee.assignment_id = p_assignment_id
1649 and p_Effective_date
1650 between pee.effective_start_date
1651 and pee.effective_end_date;
1652 --
1653 -- Get current Per_in_ler
1654 --
1655 Cursor csr_curr_le is
1656 Select pil.Per_in_Ler_Id, pil.person_id
1657 From Ben_Per_in_ler PIL, Ben_Elig_Per_Elctbl_Chc Enrt
1658 Where Enrt.Elig_per_Elctbl_Chc_id = P_Elig_per_Elctbl_Chc_Id
1659 And Enrt.Per_In_Ler_id = Pil.Per_In_Ler_id;
1660 --And Pil.LF_EVT_OCRD_DT = P_Effective_Date;
1661 -- And Pil.Per_In_Ler_Stat_Cd = 'PROCD';
1662 --
1663 -- Get prev per_in_ler
1664 --
1665 Cursor csr_prev_le is
1666 Select max(pil.Per_in_Ler_Id)
1667 From Ben_Per_in_ler PIL,
1668 Ben_Ler_F LER
1669 Where Pil.Ler_Id = LER.Ler_Id
1670 And ler.typ_Cd = 'GSP'
1671 And Pil.person_Id = l_person_id
1672 And Pil.Per_In_Ler_Stat_Cd = 'PROCD'
1673 And pil.Per_in_Ler_Id <> l_curr_per_in_ler_id;
1674 --
1675 --
1676 Cursor csr_prev_sal is
1677 Select Elct.Pgm_id, Rate.Rt_Strt_Dt Sal_Chg_Dt,Rate.Val Val
1678 From Ben_Elig_Per_Elctbl_Chc Elct,
1679 Ben_Enrt_Rt Rate
1680 Where Elct.DFLT_FLAG = 'Y'
1681 and Elct.Elctbl_Flag = 'Y'
1682 and Elct.Per_in_ler_id = l_Last_Per_In_ler_Id
1683 and Elct.Enrt_Cvg_Strt_Dt is Not NULL
1684 And Elct.ELIG_PER_ELCTBL_CHC_ID = Rate.ELIG_PER_ELCTBL_CHC_ID(+);
1685
1686 -- Check the last rate change date., not counting current one.
1687 Cursor csr_max_rt_change is
1688 Select max(Rate.Rt_Strt_Dt) Sal_Chg_Dt
1689 From Ben_Elig_Per_Elctbl_Chc Elct,
1690 Ben_Enrt_Rt Rate
1691 Where Elct.DFLT_FLAG = 'Y'
1692 and Elct.Elctbl_Flag = 'Y'
1693 and Elct.Per_in_ler_id in (Select pil.Per_in_Ler_Id
1694 From Ben_Per_in_ler PIL,
1695 Ben_Ler_F LER
1696 Where Pil.Ler_Id = LER.Ler_Id
1697 And ler.typ_Cd = 'GSP'
1698 And Pil.person_Id = l_person_id
1699 And Pil.Per_In_Ler_Stat_Cd = 'PROCD'
1700 And pil.Per_in_Ler_Id <> l_curr_per_in_ler_id)
1701 and Elct.Enrt_Cvg_Strt_Dt is Not NULL
1702 And Elct.ELIG_PER_ELCTBL_CHC_ID = Rate.ELIG_PER_ELCTBL_CHC_ID(+);
1703 --
1704 Cursor Proposal_Dtls (P_Assignment_Id in Number,p_change_dt in date) is
1705 Select Pay_Proposal_Id, Object_Version_Number, nvl(date_to,to_date('31-12-4712','dd-mm-yyyy')) -- bug 6856664
1706 From Per_Pay_Proposals
1707 Where Change_Date = P_Change_Dt
1708 and Assignment_id = P_Assignment_id;
1709 --
1710 cursor csr_prev_sp_element is
1711 select element_entry_id
1712 from pay_element_entries_f
1713 where assignment_id = p_assignment_id
1714 and creator_type = 'SP'
1715 and l_prev_sal_change_dt between
1716 effective_start_date and effective_end_date;
1717 Begin
1718 --
1719 -- Find the current per_in_ler and person_id.
1720 --
1721 hr_utility.set_location('Starting end dating prev payment',5);
1722 hr_utility.set_location('Elctbl chc = '||to_char(P_Elig_per_Elctbl_Chc_Id),5);
1723 hr_utility.set_location('le occrd dt = '||to_char(p_effective_date,'dd-mon-yyyy'),5);
1724 Open csr_curr_le;
1725 Fetch csr_curr_le into l_curr_per_in_ler_id, l_person_id;
1726 If csr_curr_le%found then
1727 -- Found current life event and person id.
1728 Close csr_curr_le;
1729 --
1730 -- Find when the salary change was made for the last GSP run.
1731 --
1732 hr_utility.set_location('Found current life event and person id = '||to_char(l_curr_per_in_ler_id),5);
1733 Open csr_prev_le;
1734 Fetch csr_prev_le into l_Last_Per_In_ler_Id;
1735 Close csr_prev_le;
1736 If l_Last_Per_In_ler_Id is not null then
1737 --
1738 --
1739 hr_utility.set_location('Found previous per_in_ler = '||to_char(l_Last_Per_In_ler_Id),5);
1740 -- Get the grade ladder, sal change date and rate value for the prev run.
1741 --
1742 Open csr_prev_sal;
1743 Fetch csr_prev_sal into l_prev_grade_ladder_id,l_prev_sal_change_dt,l_prev_sal;
1744 If csr_prev_sal%notfound then
1745 --
1746 hr_utility.set_location('No Previous ladder',10);
1747 Close csr_prev_sal;
1748 Else
1749 Close csr_prev_sal;
1750 --
1751 hr_utility.set_location('Found previous ladder',5);
1752 -- Find the max rate change date. Ignore rate change date for current change.
1753 --
1754 Open csr_max_rt_change;
1755 Fetch csr_max_rt_change into l_max_rt_change_dt;
1756 Close csr_max_rt_change;
1757 --
1758 If (l_prev_sal_change_dt <= p_current_rate_change_dt AND
1759 l_prev_sal_change_dt = l_max_rt_change_dt) then
1760 --
1761 hr_utility.set_location('Last sal change happened just bef current',5);
1762 --
1763 -- Get the grade ladder salary update details
1764 --
1765 OPen Pgm_Dtl(l_prev_grade_ladder_id,l_prev_sal_change_dt);
1766 Fetch Pgm_Dtl into L_Update_Salary_Cd, L_DFLT_INPUT_VALUE_ID, L_DFLT_ELEMENT_TYPE_ID, l_Dflt_Step_Cd;
1767 Close Pgm_Dtl;
1768
1769 If L_Update_Salary_Cd = 'SALARY_ELEMENT' AND
1770 l_prev_sal is not null Then
1771 --
1772 hr_utility.set_location('Sal Element used to pay previously',5);
1773 If L_DFLT_INPUT_VALUE_ID is NULL or L_DFLT_ELEMENT_TYPE_ID is NULL Then
1774 fnd_message.set_name('PQH','PQH_GSP_DFLY_ELMNT_NOT_LNKD');
1775 fnd_message.raise_error;
1776 End If;
1777 --
1778 Open Elmt_Entry(p_Assignment_Id, p_Business_Group_Id, l_prev_sal_change_dt);
1779 Fetch Elmt_Entry into L_Element_Entry_ID, L_Ele_Ovn;
1780 If Elmt_Entry%Found Then
1781 hr_utility.set_location('Found previous element entry',5);
1782
1783 If l_prev_sal_change_dt = p_current_rate_change_dt then
1784 --
1785 hr_utility.set_location('Zap element entry',5);
1786 --
1787 hr_entry_api.delete_element_entry
1788 ('ZAP'
1789 ,p_current_rate_change_dt
1790 ,l_element_entry_id);
1791 --
1792 Else
1793 --
1794 hr_entry_api.delete_element_entry
1795 ('DELETE'
1796 ,p_current_rate_change_dt - 1
1797 ,l_element_entry_id);
1798 --
1799 End If; --If l_prev_sal_change_dt = p_current_rate_change_dt then
1800
1801 /**
1802 -- End date element entry.
1803 L_DATE_TRACK_MODE := DT_del_Mode
1804 (P_EFFECTIVE_DATE => p_current_rate_change_dt
1805 ,P_BASE_TABLE_NAME => 'PAY_ELEMENT_ENTRIES_F'
1806 ,P_BASE_KEY_COLUMN => 'ELEMENT_ENTRY_ID'
1807 ,P_BASE_KEY_VALUE => L_Element_Entry_ID);
1808 --
1809 hr_entry_api.delete_element_entry
1810 (L_DATE_TRACK_MODE
1811 ,p_current_rate_change_dt
1812 ,l_element_entry_id);
1813 --
1814 **/
1815 End if; --If Elmt_Entry%Found Then
1816 --
1817 ElsIf L_Update_Salary_Cd = 'SALARY_BASIS' AND
1818 l_prev_sal is not null Then
1819 --
1820 hr_utility.set_location('Sal Basis used to pay previously',5);
1821 -- End date salary proposal
1822 Open Proposal_Dtls(p_assignment_Id,l_prev_sal_change_dt);
1823 Fetch proposal_Dtls into l_Del_Proposal_Id, l_Del_Proposal_Ovn,l_proposal_date_to; -- bug 6856664
1824 Close Proposal_Dtls;
1825 --
1826 if l_Del_Proposal_Id is Not NULL then
1827 --
1828 hr_utility.set_location('Found previous pay proposal',5);
1829 Open csr_prev_sp_element;
1830 Fetch csr_prev_sp_element into L_Element_Entry_ID;
1831 If csr_prev_sp_element%notfound then
1832 hr_utility.set_location('Cannot find sal proposal element ! ',5);
1833 end if;
1834 Close csr_prev_sp_element;
1835
1836 -- Previous change happened on same date as current change.
1837 --
1838 If l_prev_sal_change_dt = p_current_rate_change_dt then
1839 --
1840 hr_utility.set_location('previous proposal date same as curr change date',5);
1841 Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
1842 (P_PAY_PROPOSAL_ID => l_Del_proposal_Id
1843 ,P_BUSINESS_GROUP_ID => p_business_Group_Id
1844 ,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
1845 ,P_SALARY_WARNING => l_Del_Warn);
1846 --
1847 /**
1848 hr_utility.set_location('Zap element entry',5);
1849 --
1850 hr_entry_api.delete_element_entry
1851 ('ZAP'
1852 ,p_current_rate_change_dt - 1
1853 ,l_element_entry_id);
1854 **/
1855 --
1856 Elsif l_proposal_date_to > p_current_rate_change_dt then -- bug 6856664
1857 --
1858 -- End date Salary proposal
1859 --
1860 --
1861 hr_utility.set_location('previous proposal date before curr change date',5);
1862 --
1863 hr_maintain_proposal_api.update_salary_proposal(
1864 p_validate => false,
1865 p_pay_proposal_id => l_Del_proposal_Id,
1866 p_date_to => p_current_rate_change_dt -1,
1867 p_object_version_number => l_Del_Proposal_Ovn,
1868 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1869 p_proposed_salary_warning => l_proposed_salary_warning,
1870 p_approved_warning => l_approved_warning,
1871 p_payroll_warning => l_payroll_warning);
1872 --
1873 hr_utility.set_location('End dating element entry',5);
1874 --
1875 /* bug 6914468 and bug 6880958
1876 commenting this part as the deleting or end dating of element entries
1877 will be taken care by the Sal admin api's
1878 hr_entry_api.delete_element_entry
1879 ('DELETE'
1880 ,p_current_rate_change_dt - 1
1881 ,l_element_entry_id);
1882 --
1883 */
1884 End If; --If l_prev_sal_change_dt = p_current_rate_change_dt then
1885 --
1886 End if; --if l_Del_Proposal_Id is Not NULL then
1887 --
1888 End if; -- L_Update_Salary_Cd = 'SALARY_ELEMENT'
1889 Else
1890 hr_utility.set_location('Do nothing',99);
1891 hr_utility.set_location('Prev Salary change date ='||to_char(l_prev_sal_change_dt,'dd/mm/yyyy'),99);
1892 hr_utility.set_location('Last Salary change date ='||to_char(l_max_rt_change_dt,'dd/mm/yyyy'),99);
1893 End if; --If (l_prev_sal_change_dt <= p_current_rate_change_dt AND
1894 End if; --If csr_prev_sal%notfound then
1895 --
1896 Else
1897 -- No other salary change happened through GSP.
1898 -- Do nothing.
1899 hr_utility.set_location('No previous salary change by GSP',10);
1900 End if; -- If csr_prev_le%found then
1901 Else
1902 hr_utility.set_location('Error: No Person and Per in ler',10);
1903 Close csr_curr_le;
1904 End if; --csr_curr_le%found
1905 --
1906 End; -- Change by SR
1907
1908
1909
1910 /**************************************************************************/
1911 /************************** Update Salary Info. ***************************/
1912 /**************************************************************************/
1913
1914 Procedure Update_Salary_Info
1915 (P_Elig_per_Elctbl_Chc_Id IN Number
1916 ,P_Effective_Date IN Date
1917 ,P_Dt_Mode IN Varchar2
1918 ,P_Called_From IN Varchar2
1919 ,P_Prv_Sal_Chg_Dt IN Date) Is
1920
1921 --
1922 /* Cursor Enroll_Info is
1923 Select Rate.rt_Val , Rate.Rt_Strt_Dt, Rate.Prtt_Rt_Val_Id,
1924 Rate.pk_Id , Rate.Object_Version_Number, Enrt.Pgm_Id,
1925 Asgt.Assignment_Id, Asgt.pay_basis_id, Enrt.Business_Group_id
1926 From Ben_PRTT_ENRT_RSLT_F Enrt,
1927 ben_prtt_rt_val Rate,
1928 Per_All_Assignments_F Asgt
1929 Where Enrt.PRTT_ENRT_RSLT_ID = P_PRTT_ENRT_RSLT_ID
1930 and P_Effective_Date
1931 between Enrt.Effective_Start_Date
1932 and Enrt.Effective_End_Date
1933 and Enrt.Prtt_Enrt_Rslt_Id = Rate.Prtt_Enrt_Rslt_Id
1934 And Asgt.Person_id = Enrt.Person_id
1935 And Asgt.PRIMARY_FLAG = 'Y'
1936 And Asgt.assignment_type = 'E'
1937 And P_Effective_Date
1938 Between Asgt.Effective_start_Date and Asgt.Effective_end_Date; */
1939
1940 l_Pay_Proposal_Id Per_Pay_Proposals.Pay_Proposal_Id%TYPE;
1941 L_Pay_Proposals_Ovn Per_Pay_Proposals.Object_version_Number%TYPE;
1942
1943 l_Rt_Ovn Ben_Prtt_Rt_Val.Object_Version_Number%TYPE;
1944 l_salary Ben_Prtt_Rt_Val.Rt_Val%TYPE;
1945 L_INV_NEXT_SAL_DATE_WARNING Boolean;
1946 L_PROPOSED_SALARY_WARNING Boolean;
1947 L_APPROVED_WARNING Boolean;
1948 L_PAYROLL_WARNING Boolean;
1949 l_Del_Warn Boolean;
1950 L_ERROR_TEXT Varchar2(250);
1951 L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
1952 L_DFLT_INPUT_VALUE_ID Ben_Pgm_F.DFLT_INPUT_VALUE_ID%TYPE;
1953 L_DFLT_ELEMENT_TYPE_ID Ben_Pgm_F.DFLT_ELEMENT_TYPE_ID%TYPE;
1954 l_Element_Link_Id Pay_Element_Links_f.Element_Link_Id%TYPE;
1955 L_Effective_Start_Date pay_element_entries_f.Effective_Start_Date%TYPE;
1956 L_Effective_End_Date pay_element_entries_f.Effective_End_Date%TYPE;
1957 L_Element_Entry_ID pay_element_entries_f.Element_Entry_Id%TYPE;
1958 L_Ele_Ovn pay_element_entries_f.Object_Version_Number%TYPE;
1959 l_Create_Warn Boolean;
1960 l_DFLT_STEP_CD Ben_Pgm_F.DFLT_STEP_CD%TYPE;
1961 l_Change_Dt Per_Pay_proposals.Change_Date%TYPE;
1962 l_Del_proposal_Id Per_Pay_proposals.Pay_proposal_Id%TYPE;
1963 l_Del_Proposal_Ovn per_pay_Proposals.Object_version_Number%TYPE;
1964
1965 l_payroll_annualization_factor per_time_period_types.number_per_fiscal_year%TYPE;
1966 L_Payroll_name pay_all_payrolls_f.Payroll_name%TYPE;
1967
1968 Cursor Enroll_Info is
1969 Select Rate.Val , Rate.Rt_Strt_Dt, Rate.Prtt_Rt_Val_Id,
1970 Rate.Object_Version_Number, Enrt.Pgm_Id, Enrt.OiPl_Id,
1971 Asgt.Assignment_Id, Asgt.pay_basis_id, Asgt.Grade_Id, Enrt.Business_Group_id
1972 From Ben_ELig_per_Elctbl_Chc Enrt,
1973 ben_Enrt_Rt Rate,
1974 Ben_Per_in_ler PIL,
1975 Per_All_Assignments_F Asgt
1976 Where Enrt.Elig_per_Elctbl_Chc_id = P_Elig_per_Elctbl_Chc_Id
1977 And Enrt.Per_In_Ler_id = Pil.Per_In_Ler_id
1978 And Asgt.Person_id = PIL.Person_id
1979 And P_Effective_Date
1980 Between Asgt.Effective_start_Date and Asgt.Effective_end_Date
1981 and Enrt.Elig_per_Elctbl_Chc_id = Rate.Elig_per_Elctbl_Chc_id(+)
1982 and asgt.assignment_type ='E'
1983 And Asgt.PRIMARY_FLAG = 'Y';
1984
1985
1986 CURSOR Element_Info(P_assignmnet_id number,P_pay_basis_id number, P_Effective_Date in DAte) IS
1987 Select ele.element_entry_id
1988 from per_pay_bases bas,
1989 pay_element_entries_f ele,
1990 pay_element_entry_values_f entval
1991 where bas.pay_basis_id = P_pay_basis_id
1992 and entval.input_value_id = bas.input_value_id
1993 and p_effective_date
1994 between entval.effective_start_date
1995 and entval.effective_end_date
1996 and ele.assignment_id = P_assignmnet_id
1997 and p_effective_date between ele.effective_start_date
1998 and ele.effective_end_date
1999 and ele.element_entry_id = entval.element_entry_id;
2000
2001 Cursor Pgm_Dtl(P_Pgm_Id In Number,P_effective_Date in date) is
2002 Select Update_Salary_Cd, DFLT_INPUT_VALUE_ID, DFLT_ELEMENT_TYPE_ID, DFLT_STEP_CD
2003 From ben_Pgm_F
2004 Where Pgm_id = P_Pgm_Id
2005 and P_effective_Date
2006 Between Effective_Start_Date
2007 and Effective_End_Date;
2008
2009 CURSOR Elmt_Entry (p_assignment_id IN NUMBER, P_Business_group_Id IN Number, p_Effective_Date IN Date) IS
2010 select pee.Element_Entry_Id, pee.Object_version_Number
2011 from pay_element_types_f pet
2012 ,pay_element_links_f pel
2013 ,pay_element_entries_f pee
2014 where Pet.Element_type_id = L_DFLT_ELEMENT_TYPE_ID
2015 and p_Effective_date
2016 Between pet.effective_start_date
2017 and pet.effective_end_date
2018 and Pel.Element_type_Id = Pet.Element_type_Id
2019 and p_Effective_date
2020 Between pel.effective_start_date
2021 and pel.effective_end_date
2022 and pee.element_link_id = pel.element_link_id
2023 and pee.assignment_id = p_assignment_id
2024 and p_Effective_date
2025 between pee.effective_start_date
2026 and pee.effective_end_date;
2027
2028 Cursor Proposal_Dt (P_Assignment_Id IN Number) is
2029 Select Max(Change_Date)
2030 from Per_Pay_Proposals
2031 Where Assignment_Id = P_Assignment_id
2032 AND p_Effective_date BETWEEN Change_Date AND
2033 nvl(DATE_TO,to_date('31-12-4712','dd-mm-yyyy')) ;-- added for bug 6880958
2034 -- bug 6880958 modified the above cursor. as the when performing a date track correction
2035 -- where there is a future dated record , Application was erroring out.
2036
2037
2038 Cursor Proposal_Dtls (P_Assignment_Id in Number) is
2039 Select Pay_Proposal_Id, Object_Version_Number
2040 From Per_Pay_Proposals
2041 Where Change_Date = l_Change_Dt
2042 and Assignment_id = P_Assignment_id;
2043
2044 L_Enroll_Info Enroll_Info%ROWTYPE;
2045 L_DATE_TRACK_MODE Varchar2(25);
2046 --
2047 -- bug 6880958 for updateoverride mode
2048 --
2049 Cursor next_change_date(P_Assignment_id in number)
2050 IS
2051 select change_date ,Pay_Proposal_Id, Object_Version_Number
2052 from per_pay_proposals
2053 where assignment_id = P_Assignment_id
2054 and change_date > p_Effective_date;
2055
2056 l_change_date date;
2057 L_entry_date date;
2058
2059 cursor csr_count_pay_det(P_Assignment_id in number)
2060 is
2061 select count(*)
2062 from per_pay_proposals
2063 where assignment_id = P_Assignment_id
2064 and change_date > p_Effective_date;
2065 --
2066 l_count number;
2067 --
2068 -- bug 6880958 for updateoverride mode
2069 --
2070 Begin
2071
2072 g_debug := hr_utility.debug_enabled;
2073
2074 --
2075 -- Get enrollment details for the current change.
2076 --
2077 hr_utility.set_location(' p_Effective_date :' || p_Effective_date, 30);
2078
2079 Open Enroll_Info;
2080 Fetch Enroll_Info into L_Enroll_Info;
2081 Close Enroll_Info;
2082
2083 if g_debug then
2084 hr_utility.set_location(' Inside Salary Update: Elec_id :' || P_Elig_Per_Elctbl_Chc_Id, 10);
2085 hr_utility.set_location(' L_Enroll_Info.Business_Group_Id :' || L_Enroll_Info.Business_Group_Id, 20);
2086 hr_utility.set_location(' L_Enroll_Info.OVN :' || L_Enroll_Info.Object_version_number, 30);
2087 End If;
2088
2089 --
2090 -- Get the grade ladder salary update details
2091 --
2092 OPen Pgm_Dtl(L_Enroll_Info.Pgm_Id,P_effective_Date);
2093 Fetch Pgm_Dtl into L_Update_Salary_Cd, L_DFLT_INPUT_VALUE_ID, L_DFLT_ELEMENT_TYPE_ID, l_Dflt_Step_Cd;
2094 Close Pgm_Dtl;
2095
2096 -- If there is no rate
2097 If L_Enroll_Info.Val is NULL Then
2098
2099 If l_Dflt_Step_cd = 'NOSTEP' and L_Enroll_Info.Oipl_Id is NULL Then
2100
2101 Return;
2102
2103 Else
2104
2105 fnd_message.set_name('PQH','PQH_GSP_RAT_NOT_DFND');
2106 fnd_message.raise_error;
2107
2108 End If;
2109 End If;
2110 --Covert salary from program frequency to salary basis frequency
2111 l_salary := L_Enroll_Info.Val;
2112 l_Salary := Pqh_Gsp_Utility.PGM_TO_BASIS_CONVERSION
2113 (P_Pgm_Id => L_Enroll_Info.Pgm_Id
2114 ,P_EFFECTIVE_DATE => P_Effective_Date
2115 ,P_AMOUNT => l_Salary
2116 ,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id);
2117 --
2118 -- Current grade ladder uses salary basis to update salary
2119 --
2120 If L_Update_Salary_Cd = 'SALARY_BASIS' Then
2121 If L_Enroll_Info.Pay_Basis_Id is NULL Then
2122 fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
2123 fnd_message.raise_error;
2124 End If;
2125 --
2126 -- Find when was the last proposal change.
2127 --
2128
2129 Open Proposal_Dt(L_Enroll_Info.Assignment_Id);
2130 Fetch Proposal_Dt into l_Change_Dt;
2131 Close Proposal_Dt;
2132
2133 -- If P_Dt_Mode = 'CORRECTION' and P_Called_From = 'A' Then
2134 If P_Called_From = 'A' Then
2135 -- If there is a salary proposal that was created either on current date
2136 -- or during last GSP run,
2137 hr_utility.set_location(' L_Change_Dt:' || L_Change_Dt, 30);
2138
2139 If L_Change_Dt is NOT NULL then
2140 --
2141 -- Get the last pay proposal creation date.
2142 --
2143 Open Proposal_Dtls(L_Enroll_Info.Assignment_Id);
2144 Fetch proposal_Dtls into l_Del_Proposal_Id, l_Del_Proposal_Ovn;
2145 Close Proposal_Dtls;
2146 --
2147 -- If pay proposal already exists as of current date, then delete
2148 -- i.e. If Correction
2149 --
2150 hr_utility.set_location(' P_Prv_Sal_Chg_Dt :' || P_Prv_Sal_Chg_Dt, 31);
2151 hr_utility.set_location(' L_Enroll_Info.RT_Strt_Dt :' || L_Enroll_Info.RT_Strt_Dt, 32);
2152
2153 If ((L_Change_Dt = L_Enroll_Info.RT_Strt_Dt) or
2154 (L_Change_Dt = P_Prv_Sal_Chg_Dt)) Then
2155
2156 -- Get the proposal details and delete it altogether
2157
2158 if l_Del_Proposal_Id is Not NULL then
2159
2160 Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
2161 (P_PAY_PROPOSAL_ID => l_Del_proposal_Id
2162 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
2163 ,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
2164 ,P_SALARY_WARNING => l_Del_Warn);
2165
2166 End If;
2167
2168 Else
2169 --
2170 -- fix for bug 6880958 starts here .. deleting all the pay proposals as we are performing
2171 -- update replace operation.
2172 --
2173 hr_utility.set_location(' P_Dt_Mode :' || P_Dt_Mode, 1);
2174 hr_utility.set_location(' L_Enroll_Info.Assignment_Id :' || L_Enroll_Info.Assignment_Id, 2);
2175
2176 if P_Dt_Mode = 'UPDATE_OVERRIDE' THEN
2177
2178 open csr_count_pay_det(L_Enroll_Info.Assignment_Id);
2179 fetch csr_count_pay_det into l_count;
2180 close csr_count_pay_det;
2181
2182 for i in 1 .. l_count
2183 loop
2184
2185
2186 OPEN next_change_date(L_Enroll_Info.Assignment_Id) ;
2187 FETCH next_change_date INTO l_change_date,l_Del_Proposal_Id, l_Del_Proposal_Ovn;
2188 EXIT WHEN next_change_date%NOTFOUND;
2189
2190 hr_utility.set_location(' l_change_date :' || l_change_date, 10);
2191 hr_utility.set_location(' l_Del_Proposal_Id :' || l_Del_Proposal_Id, 20);
2192 hr_utility.set_location(' l_Del_Proposal_Ovn :' || l_Del_Proposal_Ovn, 30);
2193
2194 -- NOW DELETE ALL THE PROPOSALS AS WE ARE PERFORMING A UPDATE REPLACE OPERATION
2195
2196 Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
2197 (P_PAY_PROPOSAL_ID => l_Del_proposal_Id
2198 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
2199 ,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
2200 ,P_SALARY_WARNING => l_Del_Warn);
2201 --
2202 hr_utility.set_location(' after calling delete Proposal ' || l_Del_Proposal_Id, 20);
2203
2204
2205 CLOSE next_change_date;
2206
2207 END LOOP;
2208
2209 if next_change_date%isopen then
2210 close next_change_date;
2211 end if;
2212
2213 END IF; -- update override IF ..
2214
2215 --
2216
2217 select max(effective_end_date) into L_entry_date
2218 from pay_element_entries_f
2219 where assignment_id=L_Enroll_Info.Assignment_Id
2220 and creator_type='SP';
2221
2222 hr_utility.set_location(' Max Element entry date: ' ||L_entry_date,1);
2223 --
2224 -- fix for bug 6880958 ends..
2225
2226 hr_utility.set_location('Calling end_prev_gsp_payment 0',99);
2227 end_prev_gsp_payment(
2228 p_assignment_id => L_Enroll_Info.Assignment_Id
2229 ,p_business_group_id => L_Enroll_Info.Business_Group_Id
2230 ,P_Effective_Date => p_effective_date
2231 ,P_Elig_per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
2232 ,p_current_rate_change_dt => L_Enroll_Info.RT_Strt_Dt);
2233 hr_utility.set_location('After end_prev_gsp_payment 0',99);
2234 --
2235 End if;
2236 --
2237 Else
2238 hr_utility.set_location('Calling end_prev_gsp_payment 1',99);
2239 end_prev_gsp_payment(
2240 p_assignment_id => L_Enroll_Info.Assignment_Id
2241 ,p_business_group_id => L_Enroll_Info.Business_Group_Id
2242 ,P_Effective_Date => p_effective_date
2243 ,P_Elig_per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
2244 ,p_current_rate_change_dt => L_Enroll_Info.RT_Strt_Dt);
2245 hr_utility.set_location('After end_prev_gsp_payment 1',99);
2246
2247 End If;
2248
2249 --
2250 End If;
2251
2252 --
2253 -- Now Create a new pay proposal
2254 --
2255 Open Element_Info(L_Enroll_Info.Assignment_Id, L_Enroll_Info.pay_basis_id, l_Enroll_Info.Rt_Strt_Dt);
2256 Fetch Element_Info Into L_Element_Entry_Id;
2257 Close Element_Info;
2258
2259 Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
2260 (P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
2261 ,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id
2262 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
2263 ,P_CHANGE_DATE => L_Enroll_Info.RT_Strt_Dt
2264 ,P_PROPOSED_SALARY_N => l_Salary
2265 ,P_OBJECT_VERSION_NUMBER => L_Pay_Proposals_Ovn
2266 ,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
2267 ,P_MULTIPLE_COMPONENTS => 'N'
2268 ,P_APPROVED => 'Y'
2269 ,P_PROPOSAL_REASON => 'GSP'
2270 ,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
2271 ,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
2272 ,P_APPROVED_WARNING => L_APPROVED_WARNING
2273 ,P_PAYROLL_WARNING => L_PAYROLL_WARNING);
2274
2275 /*
2276 Hr_Maintain_Proposal_Api.Approve_Salary_Proposal
2277 (P_PAY_PROPOSAL_ID => L_Pay_Proposal_Id
2278 ,P_OBJECT_VERSION_NUMBER => L_Pay_Proposals_Ovn
2279 ,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
2280 ,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
2281 ,P_APPROVED_WARNING => L_APPROVED_WARNING
2282 ,P_PAYROLL_WARNING => L_PAYROLL_WARNING
2283 ,P_ERROR_TEXT => L_ERROR_TEXT); */
2284
2285 -- l_Rt_Ovn := L_Enroll_Info.Object_Version_Number;
2286
2287 /*
2288 Ben_Prtt_Rt_Val_Api.UPDATE_PRTT_RT_VAL
2289 (P_PRTT_RT_VAL_ID => L_Enroll_Info.PRTT_RT_VAL_ID
2290 ,P_PK_ID_TABLE_NAME => 'PER_PAY_PROPOSALS'
2291 ,P_PK_ID => l_Pay_Proposal_Id
2292 ,P_OBJECT_VERSION_NUMBER => L_Rt_Ovn
2293 ,P_EFFECTIVE_DATE => P_Effective_Date); */
2294
2295 Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
2296
2297 -- If current grade ladder uses a salary element.
2298 --
2299 -- Get default element and input value of current ladder.
2300 --
2301 If L_DFLT_INPUT_VALUE_ID is NULL or L_DFLT_ELEMENT_TYPE_ID is NULL Then
2302 fnd_message.set_name('PQH','PQH_GSP_DFLY_ELMNT_NOT_LNKD');
2303 fnd_message.raise_error;
2304 End If;
2305 --
2306 -- Check if the assignment is eligible for the element as of current rate start date
2307 --
2308 l_Element_Link_Id := hr_entry_api.get_link
2309 (L_Enroll_Info.Assignment_Id
2310 ,L_DFLT_ELEMENT_TYPE_ID
2311 ,L_Enroll_Info.RT_Strt_Dt);
2312
2313 if l_Element_Link_Id is NULL Then
2314 fnd_message.set_name('PQH','PQH_GSP_ELMNT_NOT_LNKD');
2315 fnd_message.raise_error;
2316 End If;
2317
2318 --
2319 -- Check if there is a rate value.
2320 --
2321 If L_Enroll_Info.Val is NULL Then
2322 fnd_message.set_name('PQH','PQH_GSP_RAT_NOT_DFND');
2323 fnd_message.raise_error;
2324 End If;
2325 --
2326 -- Get current pay proposal
2327 --
2328 per_pay_proposals_populate.get_payroll(L_Enroll_Info.Assignment_Id
2329 ,L_Enroll_Info.RT_Strt_Dt
2330 ,l_Payroll_name
2331 ,l_payroll_annualization_factor);
2332
2333 If L_Payroll_name is NULL Then
2334 fnd_message.set_name('PQH','PQH_GSP_PAYROLL_NOT_DFND');
2335 fnd_message.raise_error;
2336 End If;
2337
2338 -- check if a element entry already exists, for the default element
2339 -- If so update the element entru, else create the element entry.
2340 --
2341 Open Elmt_Entry(L_Enroll_Info.Assignment_Id, L_Enroll_Info.Business_Group_Id, L_Enroll_Info.Rt_Strt_Dt);
2342 Fetch Elmt_Entry into L_Element_Entry_ID, L_Ele_Ovn;
2343 If Elmt_Entry%Found Then
2344
2345 L_DATE_TRACK_MODE := DT_Mode
2346 (P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
2347 ,P_BASE_TABLE_NAME => 'PAY_ELEMENT_ENTRIES_F'
2348 ,P_BASE_KEY_COLUMN => 'ELEMENT_ENTRY_ID'
2349 ,P_BASE_KEY_VALUE => L_Element_Entry_ID);
2350 hr_utility.set_location('Sal = '||to_char(L_Salary)||',DT_Mode = '||L_DATE_TRACK_MODE,99);
2351
2352 Pay_Element_Entry_Api.UPDATE_ELEMENT_ENTRY
2353 (P_DATETRACK_UPDATE_MODE => L_DATE_TRACK_MODE
2354 ,P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
2355 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
2356 ,P_ELEMENT_ENTRY_ID => L_ELEMENT_ENTRY_ID
2357 ,P_OBJECT_VERSION_NUMBER => L_Ele_Ovn
2358 ,P_INPUT_VALUE_ID1 => L_DFLT_INPUT_VALUE_ID
2359 ,P_ENTRY_VALUE1 => L_Salary
2360 ,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
2361 ,P_EFFECTIVE_END_DATE => L_Effective_End_Date
2362 ,P_UPDATE_WARNING => l_Create_Warn);
2363
2364 Else
2365 --
2366 -- If the element entry is not found for current element, it maybe due to 2 reasons
2367 -- 1) old grade ladder used a diff element, in which case we have to end date that element
2368 -- 2) old grade ladder used salary basis, in which case we have to end date the proposal.
2369
2370 hr_utility.set_location('Calling end_prev_gsp_payment 2',99);
2371 end_prev_gsp_payment(
2372 p_assignment_id => L_Enroll_Info.Assignment_Id
2373 ,p_business_group_id => L_Enroll_Info.Business_Group_Id
2374 ,P_Effective_Date => p_effective_date
2375 ,P_Elig_per_Elctbl_Chc_Id => P_Elig_per_Elctbl_Chc_Id
2376 ,p_current_rate_change_dt => L_Enroll_Info.RT_Strt_Dt);
2377 hr_utility.set_location('After end_prev_gsp_payment 2',99);
2378
2379 Pay_Element_Entry_Api.CREATE_ELEMENT_ENTRY
2380 (P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
2381 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
2382 ,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id
2383 ,P_ELEMENT_LINK_ID => l_Element_Link_Id
2384 ,P_ENTRY_TYPE => 'E'
2385 ,P_INPUT_VALUE_ID1 => L_DFLT_INPUT_VALUE_ID
2386 ,P_ENTRY_VALUE1 => L_Salary
2387 ,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
2388 ,P_EFFECTIVE_END_DATE => L_Effective_End_Date
2389 ,P_ELEMENT_ENTRY_ID => L_Element_Entry_ID
2390 ,P_OBJECT_VERSION_NUMBER => L_Ele_Ovn
2391 ,P_CREATE_WARNING => l_Create_Warn);
2392 End If;
2393 Close Elmt_Entry;
2394 Else
2395 Return;
2396 End If; /* Update_Salary_Cd */
2397
2398 End Update_Salary_info;
2399
2400
2401 /**************************************************************************/
2402 /************************* Update Assignment info *************************/
2403 /**************************************************************************/
2404
2405 Procedure Update_Assgmt_Info
2406 (P_Elig_Per_Elctbl_Chc_Id IN Number,
2407 P_Effective_Date IN Date) is
2408
2409 Cursor Enrlmt_Dtls is
2410 Select Enrlmt.PL_Id , Enrlmt.Pgm_Id , Enrlmt.Oipl_Id , Enrlmt.Business_Group_Id,
2411 Enrlmt.Enrt_Cvg_Strt_Dt, Pgm.Dflt_Step_CD, Pl.MAPPING_TABLE_PK_ID, Asgt.Assignment_Id,
2412 Asgt.Object_Version_Number , Asgt.SPECIAL_CEILING_STEP_ID, Asgt.People_Group_Id,
2413 Asgt.Soft_Coding_Keyflex_Id, Asgt.Grade_Id, Asgt.Grade_Ladder_Pgm_Id
2414 From Ben_Elig_Per_Elctbl_Chc Enrlmt,
2415 Ben_Per_in_ler PIL,
2416 Ben_Pgm_F Pgm,
2417 Ben_PLip_F Plip,
2418 Ben_Pl_F Pl,
2419 Per_All_Assignments_F Asgt
2420 Where Enrlmt.Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_Id
2421 And Enrlmt.Per_In_Ler_id = Pil.Per_In_Ler_id
2422 And Asgt.Person_id = PIL.Person_id
2423 and Pgm.Pgm_Id = Enrlmt.Pgm_Id
2424 and P_Effective_Date
2425 Between Pgm.Effective_Start_Date
2426 and Pgm.Effective_End_Date
2427 and Pl.Pl_Id = Enrlmt.Pl_Id
2428 and P_Effective_Date
2429 Between Pl.Effective_Start_Date
2430 and Pl.Effective_End_Date
2431 and Plip.Pgm_Id = Pgm.Pgm_Id
2432 and Plip.Pl_Id = Pl.Pl_Id
2433 and P_Effective_Date
2434 Between Plip.Effective_Start_Date
2435 and Plip.Effective_End_Date
2436 and Asgt.Person_id = Pil.Person_id
2437 And Asgt.PRIMARY_FLAG = 'Y'
2438 And Asgt.assignment_type = 'E'
2439 And P_Effective_Date
2440 Between Asgt.Effective_start_Date and Asgt.Effective_end_Date;
2441
2442 Cursor Step_Dtls(P_Oipl_Id IN NUmber) Is
2443 Select Step.Step_id, Step.Spinal_point_id, Step.Grade_spine_id
2444 From Ben_Oipl_F Oipl,
2445 Ben_pl_F Pl,
2446 Ben_Opt_F Opt,
2447 Per_Spinal_points point,
2448 Per_Grade_Spines_f GSpine,
2449 Per_Spinal_point_Steps_F Step
2450 Where Oipl.Oipl_id = P_Oipl_Id
2451 and P_Effective_Date
2452 Between OiPl.Effective_Start_Date and OiPl.Effective_End_Date
2453 and Pl.Pl_id = Oipl.Pl_Id
2454 and P_Effective_Date
2455 Between Pl.Effective_Start_Date and Pl.Effective_End_Date
2456 and Oipl.Opt_id = Opt.Opt_id
2457 and P_Effective_Date
2458 Between Opt.Effective_Start_Date and Opt.Effective_End_Date
2459 and Point.Spinal_Point_id = Opt.Mapping_Table_Pk_Id
2460 and Pl.Mapping_Table_Pk_Id = Gspine.Grade_Id
2461 and P_Effective_Date
2462 Between Gspine.Effective_Start_Date and Gspine.Effective_End_Date
2463 and Step.Grade_Spine_Id = Gspine.Grade_Spine_Id
2464 and P_Effective_Date
2465 Between Step.Effective_Start_Date and Step.Effective_End_Date
2466 and Step.SPINAL_POINT_ID = Point.SPINAL_POINT_ID;
2467
2468 Cursor Plcmt_Dtls(P_Assignment_Id IN NUMBER, P_Effective_Date IN Date) is
2469 Select PLACEMENT_ID , Object_Version_Number,
2470 Effective_Start_Date, Effective_End_Date
2471 from Per_Spinal_Point_Placements_F
2472 Where Assignment_Id = P_Assignment_Id
2473 and P_Effective_Date
2474 Between Effective_Start_Date
2475 and Effective_End_Date;
2476
2477 L_PL_Id Ben_Pl_F.Pl_Id%TYPE;
2478 L_Pgm_Id Ben_Pgm_F.Pgm_Id%TYPE;
2479 L_Oipl_Id Ben_Oipl_F.Oipl_Id%TYPE;
2480 l_DFLT_STEP_CD Ben_Pgm_F.DFLT_STEP_CD%TYPE;
2481 l_Grade_Id Per_Grades.Grade_Id%TYPE;
2482 l_Step_Id Per_Spinal_point_Steps_F.Step_Id%TYPE;
2483 l_Spinal_point_id Per_Spinal_point_Steps_F.Spinal_Point_Id%TYPE;
2484 l_Grade_spine_id Per_Spinal_point_Steps_F.Grade_Spine_Id%TYPE;
2485 l_Assignment_id Per_All_Assignments_F.Assignment_Id%TYPE;
2486 l_Assignment_Ovn Per_All_Assignments_F.Object_Version_Number%TYPE;
2487 l_SPECIAL_CEILING_STEP_ID Per_All_Assignments_F.SPECIAL_CEILING_STEP_ID%TYPE;
2488 L_People_Group_Id Per_All_Assignments_F.People_Group_Id%TYPE;
2489 l_Soft_Coding_Keyflex_Id Per_All_Assignments_F.Soft_Coding_Keyflex_Id%TYPE;
2490 l_PLACEMENT_ID Per_Spinal_Point_Placements_F.PLacement_Id%TYPE;
2491 l_Placement_Ovn Per_Spinal_Point_Placements_F.Object_Version_Number%TYPE;
2492 l_Effective_Start_Date Per_Spinal_Point_Placements_F.Effective_Start_Date%TYPE;
2493 l_Effective_End_Date Per_Spinal_Point_Placements_F.Effective_End_Date%TYPE;
2494 l_Business_Group_Id Ben_PRTT_ENRT_RSLT_F.Business_Group_Id%TYPE;
2495 l_group_name Varchar2(250);
2496 l_Asg_effective_start_date Per_All_Assignments_F.Effective_Start_Date%TYPE;
2497 l_Asg_effective_end_date Per_All_Assignments_F.Effective_End_Date%TYPE;
2498 l_org_now_no_manager_warning Boolean;
2499 l_other_manager_warning Boolean;
2500 l_spp_delete_warning Boolean;
2501 l_entries_changed_warning Varchar2(250);
2502 l_tax_district_changed_warning Boolean;
2503 l_concatenated_segments Varchar2(250);
2504 L_DATE_TRACK_MODE Varchar2(25);
2505 l_Enrt_Cvg_Strt_Dt Ben_Elig_Per_Elctbl_Chc.Enrt_Cvg_Strt_Dt%TYPE;
2506 l_Asgt_grade_Id Per_All_Assignments_F.Grade_Id%TYPE;
2507 l_Asgt_Grdldr_Id Per_all_Assignments_F.Grade_Ladder_Pgm_Id%TYPE;
2508 Begin
2509
2510 if g_debug then
2511 hr_utility.set_location(' Inside Update_Assgmt_Info ', 10);
2512 End If;
2513
2514 Open Enrlmt_Dtls;
2515 Fetch Enrlmt_Dtls into l_pl_Id , l_Pgm_Id ,l_Oipl_id , L_Business_group_Id, l_Enrt_Cvg_Strt_Dt,
2516 l_DFLT_STEP_CD, l_Grade_Id ,l_Assignment_id , l_Assignment_Ovn,
2517 L_SPECIAL_CEILING_STEP_ID, L_People_Group_Id, l_Soft_Coding_Keyflex_Id, l_Asgt_Grade_ID, l_Asgt_Grdldr_Id;
2518 Close Enrlmt_Dtls;
2519
2520 If Nvl(l_DFLT_STEP_CD,'PQH_GSP_NP') = 'PQH_GSP_NP' Then
2521 /* rogression Style is No progression -- So return back */
2522 Return;
2523 End If;
2524
2525 If ((l_Asgt_Grade_Id <> l_Grade_id) or (Nvl(l_Asgt_Grdldr_Id,-1) <> l_Pgm_id)) Then
2526 /* Update Assignments with the Grade Ladder / Grade Details */
2527 L_DATE_TRACK_MODE := DT_Mode
2528 (P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date)
2529 ,P_BASE_TABLE_NAME => 'PER_ALL_ASSIGNMENTS_F'
2530 ,P_BASE_KEY_COLUMN => 'ASSIGNMENT_ID'
2531 ,P_BASE_KEY_VALUE => L_Assignment_id);
2532
2533 if g_debug then
2534 hr_utility.set_location(' L_DATE_TRACK_MODE : ' || L_DATE_TRACK_MODE, 20);
2535 End If;
2536
2537 If Nvl(l_DFLT_STEP_CD,'XX') = 'NOSTEP' Then
2538 Open Plcmt_Dtls(l_Assignment_Id, l_Enrt_Cvg_Strt_Dt);
2539 Fetch Plcmt_Dtls Into l_PLACEMENT_ID , l_Placement_Ovn,
2540 l_Effective_Start_Date, L_Effective_End_Date;
2541 If Plcmt_Dtls%FOUND Then
2542 Hr_Sp_Placement_Api.DELETE_SPP
2543 (P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date) - 1
2544 ,P_DATETRACK_MODE => 'DELETE'
2545 ,P_PLACEMENT_ID => l_PLACEMENT_ID
2546 ,P_OBJECT_VERSION_NUMBER => l_Placement_Ovn
2547 ,P_EFFECTIVE_START_DATE => l_Effective_Start_Date
2548 ,P_EFFECTIVE_END_DATE => L_Effective_End_Date);
2549 End If;
2550 Close Plcmt_Dtls;
2551 End If;
2552
2553 Hr_Assignment_Api.Update_Emp_Asg_Criteria
2554 (p_effective_date => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date)
2555 ,p_datetrack_update_mode => L_Date_Track_Mode
2556 ,p_assignment_id => L_Assignment_id
2557 ,p_grade_id => L_Grade_Id
2558 ,p_grade_ladder_pgm_id => L_Pgm_Id
2559 ,p_object_version_number => L_Assignment_Ovn
2560 ,p_special_ceiling_step_id => L_special_ceiling_step_id
2561 ,p_people_group_id => L_People_Group_Id
2562 ,p_soft_coding_keyflex_id => L_Soft_Coding_Keyflex_Id
2563 ,p_group_name => L_group_name
2564 ,p_effective_start_date => L_Asg_effective_start_date
2565 ,p_effective_end_date => L_Asg_effective_end_date
2566 ,p_org_now_no_manager_warning => L_org_now_no_manager_warning
2567 ,p_other_manager_warning => L_other_manager_warning
2568 ,p_spp_delete_warning => L_spp_delete_warning
2569 ,p_entries_changed_warning => L_entries_changed_warning
2570 ,p_tax_district_changed_warning => L_tax_district_changed_warning
2571 ,p_concatenated_segments => L_concatenated_segments);
2572
2573 If Nvl(l_DFLT_STEP_CD,'XX') = 'NOSTEP' Then
2574 Return;
2575 End If;
2576
2577 End If;
2578 If Nvl(l_DFLT_STEP_CD,'XX') in ('PQH_GSP_GSP','PQH_GSP_SP','MINSALINCR','MINSTEP','NOSTEP') Then
2579 Open Step_Dtls(l_Oipl_id);
2580 Fetch Step_Dtls Into L_Step_id, l_Spinal_point_id, l_Grade_spine_id;
2581
2582 If Step_Dtls%Found Then
2583 Open Plcmt_Dtls(l_Assignment_Id, l_Enrt_Cvg_Strt_Dt);
2584 Fetch Plcmt_Dtls Into l_PLACEMENT_ID , l_Placement_Ovn,
2585 l_Effective_Start_Date, L_Effective_End_Date;
2586
2587 L_DATE_TRACK_MODE := NULL;
2588
2589
2590 If Plcmt_Dtls%FOUND Then
2591
2592 L_DATE_TRACK_MODE := DT_Mode
2593 (P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date)
2594 ,P_BASE_TABLE_NAME => 'PER_SPINAL_POINT_PLACEMENTS_F'
2595 ,P_BASE_KEY_COLUMN => 'PLACEMENT_ID'
2596 ,P_BASE_KEY_VALUE => l_PLACEMENT_ID);
2597
2598 /* Update Placements with the New Step Details */
2599 Hr_Sp_Placement_Api.UPDATE_SPP
2600 (P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date),
2601 P_DATETRACK_MODE => l_Date_Track_Mode,
2602 P_PLACEMENT_ID => l_PLACEMENT_ID,
2603 P_OBJECT_VERSION_NUMBER => l_Placement_Ovn,
2604 P_REASON => 'GSP',
2605 P_STEP_ID => L_Step_id,
2606 P_EFFECTIVE_START_DATE => l_Effective_Start_Date,
2607 P_EFFECTIVE_END_DATE => L_Effective_End_Date);
2608 Else
2609 l_Date_track_Mode := hr_api.g_Insert;
2610 /* Insert Placements with the New Step Details */
2611 Hr_Sp_Placement_Api.CREATE_SPP
2612 (P_EFFECTIVE_DATE => Nvl(l_Enrt_Cvg_Strt_Dt, P_Effective_Date),
2613 P_BUSINESS_GROUP_ID => l_Business_Group_Id,
2614 P_ASSIGNMENT_ID => L_Assignment_Id,
2615 P_STEP_ID => l_Step_Id,
2616 P_PLACEMENT_ID => l_PLACEMENT_ID,
2617 P_OBJECT_VERSION_NUMBER => l_Placement_Ovn,
2618 P_REASON => 'GSP',
2619 P_EFFECTIVE_START_DATE => l_Effective_Start_Date,
2620 P_EFFECTIVE_END_DATE => L_Effective_End_Date);
2621 End If;
2622 Close Plcmt_Dtls;
2623 End If;
2624 Close Step_Dtls;
2625
2626 if g_debug then
2627 hr_utility.set_location(' Leaving Assigmt Updates ', 30);
2628 End If;
2629
2630 End If;
2631 Exception
2632 When Others then
2633
2634 if g_debug then
2635 hr_utility.set_location(' Error ', 40);
2636 End if;
2637
2638 Raise;
2639 End Update_Assgmt_Info;
2640
2641
2642 /**************************************************************************/
2643 /***************************** Call PP from AUI ***************************/
2644 /**************************************************************************/
2645
2646 Procedure Call_PP_From_AUI
2647 (P_Errbuf OUT NOCOPY Varchar2,
2648 P_Retcode OUT NOCOPY Number,
2649 P_Effective_Date IN Varchar2,
2650 P_Approval_Status_Cd IN Varchar2,
2651 P_Elig_per_Elctbl_Chc_Id IN Number) Is
2652
2653
2654 l_Error_Status varchar2(30);
2655 -- L_PRTT_ENRT_RSLT_ID Ben_PRTT_ENRT_RSLT_F.PRTT_ENRT_RSLT_ID%TYPE;
2656 L_INV_NEXT_SAL_DATE_WARNING Boolean;
2657 L_PROPOSED_SALARY_WARNING Boolean;
2658 L_APPROVED_WARNING Boolean;
2659 L_PAYROLL_WARNING Boolean;
2660 l_Error_Message Varchar2(250);
2661 L_Concurrent_Req_Id Number(18);
2662 l_Progression_Style Ben_Pgm_F.Dflt_Step_Cd%TYPE;
2663
2664 L_Per_In_Ler_id Ben_Per_In_ler.Per_In_Ler_Id%TYPE;
2665 L_Pil_Ovn Ben_Per_In_ler.Object_Version_Number%TYPE;
2666 l_PROCD_DT DATE;
2667 l_STRTD_DT DATE;
2668 l_VOIDD_DT Date;
2669 L_Effective_Date Date;
2670 l_Per_In_ler_Stat_Cd Ben_Per_In_Ler.Per_In_Ler_Stat_Cd%TYPE;
2671 l_Error_Exists Varchar2(1) := 'N';
2672 l_conc_status boolean;
2673
2674 l_ptnl_ler_for_per_id ben_ptnl_ler_for_per.ptnl_ler_for_per_id%type;
2675 l_ptnl_ler_for_per_ovn ben_ptnl_ler_for_per.object_version_number%type;
2676
2677 cursor csr_ptnl_ler_dtls(p_per_in_ler_id in number)
2678 is
2679 select ptnl.ptnl_ler_for_per_id, ptnl.object_version_number
2680 from ben_per_in_ler per
2681 ,ben_ptnl_ler_for_per ptnl
2682 where per.per_in_ler_id = p_per_in_ler_id
2683 and per.ptnl_ler_for_per_id = ptnl.ptnl_ler_for_per_id;
2684
2685 Cursor Elctbl is
2686 Select Elct.ELIG_PER_ELCTBL_CHC_ID, Pil.Person_Id, Pil.Per_In_Ler_id, Pil. Object_Version_Number,
2687 Elct.Enrt_Cvg_Strt_Dt, Pgm.Update_Salary_Cd, Pgm.Dflt_Step_Cd, Elct.Approval_Status_Cd, Person.Full_name
2688 From ben_Elig_Per_Elctbl_Chc Elct,
2689 Ben_Ler_F Ler,
2690 Ben_Per_in_ler Pil,
2691 Ben_Pgm_F PGM,
2692 Per_All_People_F Person
2693 where Ler.typ_Cd = 'GSP'
2694 and L_Effective_Date
2695 Between Ler.Effective_Start_Date
2696 and Ler.Effective_End_Date
2697 and Ler.Business_group_id = Hr_general.get_Business_Group_id
2698 and Pil.Ler_Id = Ler.Ler_id
2699 and Pil.PER_IN_LER_STAT_CD = 'STRTD'
2700 and Elct.Per_In_Ler_id = Pil.Per_in_Ler_id
2701 and Elct.DFLT_FLAG = 'Y'
2702 and ((P_Approval_Status_Cd is NULL
2703 and Elct.Approval_Status_Cd in ('PQH_GSP_A','PQH_GSP_R'))
2704 or (Elct.Approval_Status_Cd = P_Approval_Status_Cd))
2705 and Pgm.Pgm_Id = Elct.Pgm_Id
2706 and L_Effective_Date
2707 Between Pgm.Effective_Start_Date
2708 and Pgm.Effective_End_Date
2709 and Person.Person_Id = Pil.Person_id
2710 and l_Effective_Date
2711 Between Person.Effective_Start_Date
2712 and Person.Effective_End_Date
2713 Order By Person.Full_name Desc;
2714
2715
2716 Begin
2717 L_Effective_Date := Fnd_Date.CANONICAL_TO_DATE(P_Effective_Date);
2718 /* Initialise the Main Process Log */
2719 L_Concurrent_Req_Id := fnd_global.conc_request_id;
2720
2721 Pqh_Gsp_process_Log.Start_log
2722 (P_Txn_ID => L_Concurrent_Req_Id
2723 ,P_Txn_Name => Hr_general.Decode_lookup('PQH_GSP_GEN_PROMPTS','AUI') || L_Concurrent_Req_Id
2724 ,P_Module_Cd => 'PQH_GSP_APPROVAL_UI');
2725
2726 For Elctbl_Rec in Elctbl
2727 Loop
2728 Begin
2729
2730 if g_debug then
2731 hr_utility.set_location(' Inside Apprival UI Call ' , 10);
2732 hr_utility.set_location(' Elctbl_Chc_Id : ' || Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID, 20);
2733 End if;
2734
2735 Savepoint Start_Enrlmnt;
2736 /* Start Enrollment process */
2737
2738 /*
2739 Pqh_gsp_Post_Process.Create_Enrollment
2740 (P_Elig_Per_Elctbl_Chc_Id => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
2741 ,P_Person_id => Elctbl_Rec.Person_Id
2742 ,P_Progression_Style => l_Progression_Style
2743 ,P_Effective_Date => Elctbl_Rec.Enrt_Cvg_Strt_Dt
2744 ,P_PRTT_ENRT_RSLT_ID => L_PRTT_ENRT_RSLT_ID
2745 ,P_Status => L_Error_Status); */
2746
2747 If Elctbl_Rec.Approval_Status_Cd = 'PQH_GSP_A' Then
2748
2749 /* Update Employee Salary */
2750 if g_debug then
2751 hr_utility.set_location(' Approve ', 30);
2752 end if;
2753
2754 pqh_gsp_Post_Process.Update_Assgmt_Info
2755 (P_ELIG_PER_ELCTBL_CHC_ID => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
2756 ,P_Effective_Date => Elctbl_Rec.Enrt_Cvg_Strt_Dt);
2757
2758 if g_debug then
2759 hr_utility.set_location(' Update Salary ', 40);
2760 End If;
2761
2762 /* If Update_Salary_Cd is NO_UPDATE -- Salary Updateis not required.
2763 Hence will not make a call to Salary Update */
2764
2765 If Elctbl_Rec.Update_Salary_Cd <> 'NO_UPDATE' Then
2766 pqh_gsp_Post_Process.Update_Salary_Info
2767 (P_ELIG_PER_ELCTBL_CHC_ID => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
2768 ,P_Effective_Date => Elctbl_Rec.Enrt_Cvg_Strt_Dt);
2769 End If;
2770 l_Per_In_ler_Stat_Cd := 'PROCD';
2771 Else
2772 l_per_In_Ler_Stat_Cd := 'VOIDD';
2773 End If;
2774
2775 L_Per_In_Ler_Id := Elctbl_Rec.Per_In_Ler_Id;
2776 L_Pil_Ovn := Elctbl_Rec.Object_Version_NUmber;
2777
2778 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
2779 (P_PER_IN_LER_ID => l_PER_IN_LER_ID
2780 ,P_PER_IN_LER_STAT_CD => l_Per_In_Ler_Stat_Cd
2781 ,P_PROCD_DT => L_PROCD_DT
2782 ,P_STRTD_DT => L_STRTD_DT
2783 ,P_VOIDD_DT => L_VOIDD_DT
2784 ,P_OBJECT_VERSION_NUMBER => L_Pil_Ovn
2785 ,P_EFFECTIVE_DATE => L_Effective_Date);
2786
2787 open csr_ptnl_ler_dtls(l_per_in_ler_id);
2788 fetch csr_ptnl_ler_dtls
2789 into l_ptnl_ler_for_per_id
2790 ,l_ptnl_ler_for_per_ovn ;
2791 close csr_ptnl_ler_dtls ;
2792
2793 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
2794 (p_ptnl_ler_for_per_id => l_PTNL_LER_FOR_PER_ID
2795 ,p_ptnl_ler_for_per_stat_cd => l_Per_In_Ler_Stat_Cd
2796 ,p_voidd_dt => l_effective_date
2797 ,p_object_version_number => l_PTNL_LER_FOR_PER_OVN
2798 ,p_effective_date => l_effective_date);
2799
2800 Pqh_Gsp_process_Log.Log_process_Dtls
2801 (P_Master_txn_Id => L_Concurrent_Req_Id
2802 ,P_Txn_Id => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
2803 ,p_module_cd => 'PQH_GSP_APPROVAL_UI'
2804 ,p_message_type_cd => 'C'
2805 ,p_message_text => NULL
2806 ,P_Effective_Date => L_Effective_Date);
2807
2808 Exception
2809 When Others Then
2810 Rollback to Start_Enrlmnt;
2811 l_Error_Exists := 'Y';
2812 -- l_Error_Message := Fnd_Message.Get;
2813 if g_debug then
2814 hr_utility.set_location(' Error in AUI ', 50);
2815 hr_utility.set_location(' Error : ' || l_Error_Message, 60);
2816 End If;
2817
2818 Pqh_Gsp_process_Log.Log_process_Dtls
2819 (P_Master_txn_Id => L_Concurrent_Req_Id
2820 ,P_Txn_Id => Elctbl_Rec.ELIG_PER_ELCTBL_CHC_ID
2821 ,p_module_cd => 'PQH_GSP_APPROVAL_UI'
2822 ,p_message_type_cd => 'E'
2823 ,p_message_text => Nvl(Fnd_Message.Get,sqlerrm)
2824 ,P_Effective_Date => L_Effective_Date);
2825 End;
2826 End Loop;
2827
2828 If l_Error_Exists = 'N' Then
2829 fnd_message.set_name('PQH','PQH_GSP_LOG_SUC');
2830 fnd_Message.Set_Token('MODULE',Hr_general.Decode_lookup('PQH_PROCESS_LOG_TREE','PQH_GSP_APPROVAL_UI'));
2831 fnd_file.put_line(fnd_file.log,Fnd_Message.get);
2832 Else
2833 fnd_message.set_name('PQH','PQH_GSP_LOG_ERR');
2834 fnd_Message.Set_Token('MODULE',Hr_general.Decode_lookup('PQH_PROCESS_LOG_TREE','PQH_GSP_APPROVAL_UI'));
2835 fnd_file.put_line(fnd_file.log,Fnd_Message.get);
2836 l_conc_status := fnd_concurrent.set_completion_status(status => 'ERROR'
2837 ,message=>SQLERRM);
2838 End If;
2839
2840 PQH_PROCESS_BATCH_LOG.END_LOG;
2841
2842 Commit;
2843
2844 End Call_PP_From_AUI;
2845
2846 Procedure Call_Concurrent_Req_Aui
2847 (P_Approval_Status_Cd IN Varchar2
2848 ,P_Req_Id OUT NOCOPY Varchar2) Is
2849
2850 l_Req_Id Number;
2851 begin
2852 l_Req_Id := -1;
2853
2854 l_Req_Id := fnd_request.submit_request
2855 (application => 'PQH'
2856 ,program => 'PQHGSPPRCSAUI'
2857 ,argument1 => To_Char(Trunc(Sysdate),'rrrr-mm-dd')
2858 ,argument2 => P_Approval_Status_Cd);
2859
2860 P_Req_Id := l_req_id;
2861
2862 Exception When Others then
2863 l_req_id := -1;
2864 End;
2865
2866
2867 Procedure Approve_Reject_AUI
2868 (P_Elig_Per_Elctbl_Chc_id IN Number
2869 ,P_Prog_Dt IN Date
2870 ,P_Sal_Chg_Dt IN Date
2871 ,P_Comments IN Varchar2
2872 ,P_Approve_Rej IN Varchar2) Is
2873
2874 L_Enrt_Rt_Id ben_Enrt_Rt.Enrt_Rt_Id%TYPE;
2875 l_Enrt_ovn ben_Enrt_Rt.Object_Version_Number%TYPE;
2876
2877 Cursor Enrt_Rt is
2878 Select Enrt_Rt_Id,
2879 Object_Version_Number
2880 From Ben_Enrt_Rt
2881 Where Elig_per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
2882
2883 BEgin
2884
2885 If P_Approve_Rej = 'PQH_GSP_A' Then
2886
2887 Open Enrt_Rt;
2888 Fetch Enrt_Rt into L_Enrt_Rt_Id, l_Enrt_ovn;
2889 Close Enrt_Rt;
2890
2891 Update Ben_Elig_per_elctbl_Chc
2892 Set Approval_Status_Cd = 'PQH_GSP_A'
2893 ,Enrt_Cvg_Strt_Dt = P_Prog_Dt
2894 ,Comments = P_Comments
2895 Where Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
2896
2897 If L_Enrt_Rt_Id is Not NULL and P_Sal_Chg_Dt is Not Null then
2898 Update Ben_Enrt_Rt
2899 Set Rt_Strt_Dt = P_Sal_Chg_Dt
2900 Where Enrt_Rt_Id = L_Enrt_Rt_Id;
2901 End If;
2902
2903 Elsif P_Approve_Rej = 'PQH_GSP_R' Then
2904
2905 Update Ben_Elig_per_elctbl_Chc
2906 Set Approval_Status_Cd = 'PQH_GSP_R'
2907 Where Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
2908
2909 End If;
2910 End;
2911
2912 -- The following procedure returns the current grade and grade ladder
2913 -- of an assignment.
2914 --
2915 Procedure get_persons_gl_and_grade(p_person_id in number,
2916 p_business_group_id in number,
2917 p_effective_date in date,
2918 p_persons_pgm_id out nocopy number,
2919 p_persons_plip_id out nocopy number,
2920 p_prog_style out nocopy varchar2)
2921 /*
2922 ************** Called from benmngle ********
2923 */
2924
2925 IS
2926 -- Get assignment details for person from primary assignment.
2927 Cursor csr_asg_details is
2928 Select grade_ladder_pgm_id,grade_id
2929 From per_all_assignments_f
2930 Where person_id = p_person_id
2931 and primary_flag = 'Y'
2932 and assignment_type ='E'
2933 and p_effective_date between effective_start_date and effective_end_date;
2934 --
2935 Cursor csr_get_default_gl is
2936 Select pgm_id,dflt_step_cd
2937 From ben_pgm_f
2938 Where business_group_id = p_business_group_id
2939 and pgm_typ_cd = 'GSP'
2940 and pgm_stat_cd = 'A'
2941 and nvl(dflt_pgm_flag,'N') = 'Y'
2942 and p_effective_date between effective_start_date and effective_end_date;
2943 --
2944 Cursor csr_pgm_details(p_pgm_id in number) is
2945 Select dflt_step_cd
2946 From ben_pgm_f
2947 Where pgm_id = p_pgm_id
2948 and p_effective_date between effective_start_date and effective_end_date;
2949 --
2950 Cursor csr_grade_pl(p_grade_id in number) is
2951 select pl_id
2952 from ben_pl_f pl,ben_pl_typ_f pltyp
2953 where pltyp.business_group_id = p_business_group_id
2954 and pltyp.opt_typ_cd = 'GSP'
2955 and p_effective_date between pltyp.effective_start_date and pltyp.effective_end_date
2956 and pl.pl_typ_id = pltyp.pl_typ_id
2957 and pl.business_group_id = p_business_group_id
2958 and pl.mapping_table_name = 'PER_GRADES'
2959 and pl.mapping_table_pk_id = p_grade_id
2960 and p_effective_date between pl.effective_start_date and pl.effective_end_date
2961 and pl.pl_stat_cd = 'A';
2962 --
2963 Cursor csr_grade_plip(p_pl_id in number, p_pgm_id in number) is
2964 select plip_id from ben_plip_f
2965 where pl_id = p_pl_id
2966 and pgm_id = p_pgm_id
2967 and business_group_id = p_business_group_id
2968 and p_effective_date between effective_start_date and effective_end_date
2969 and plip_stat_cd = 'A';
2970
2971 --
2972 l_asg_gl per_all_assignments_f.grade_ladder_pgm_id%type := NULL;
2973 l_asg_grade per_all_assignments_f.grade_id%type := NULL;
2974 l_def_gl ben_pgm_f.pgm_id%type := NULL;
2975 l_dflt_step_cd ben_pgm_f.dflt_step_cd%type := NULL;
2976 l_grade_pl ben_pl_f.pl_id%type := NULL;
2977 l_grade_plip ben_plip_f.plip_id%type := NULL;
2978 --
2979 Begin
2980 --
2981 hr_utility.set_location(' Entering get_persons_gl_and_grade', 10);
2982 p_persons_pgm_id := NULL;
2983 p_persons_plip_id := NULL;
2984 p_prog_style := NULL;
2985 --
2986 -- Fetch persons grade ladder and grade.
2987 --
2988 Open csr_asg_details;
2989 Fetch csr_asg_details into l_asg_gl, l_asg_grade;
2990 Close csr_asg_details;
2991 --
2992 -- No grade ladder on assignment.
2993 If l_asg_gl is null then
2994 --
2995 hr_utility.set_location(' No grade ladder on assignment', 20);
2996 Open csr_get_default_gl;
2997 Fetch csr_get_default_gl into l_def_gl,l_dflt_step_cd;
2998 Close csr_get_default_gl;
2999 --
3000 If l_def_gl IS NOT NULL then
3001 -- Default found. check if the persons grade belongs to the default GL.
3002 hr_utility.set_location(' Default GL found: '||to_char(l_def_gl), 30);
3003 If l_asg_grade IS NOT NULL then
3004 -- Find the plan corresponding to the grade and check if it belongs to the pgm.
3005 hr_utility.set_location('Assignment has grade:'||to_char(l_asg_grade), 40);
3006 Open csr_grade_pl(l_asg_grade);
3007 Fetch csr_grade_pl into l_grade_pl;
3008 Close csr_grade_pl;
3009 -- Find the plip for the plan
3010 If l_grade_pl is not null then
3011 --
3012 hr_utility.set_location('Found plan for grade:'||to_char(l_grade_pl), 50);
3013 Open csr_grade_plip (p_pgm_id => l_def_gl,p_pl_id => l_grade_pl);
3014 Fetch csr_grade_plip into l_grade_plip;
3015 Close csr_grade_plip;
3016 --
3017 If l_grade_plip is not null then
3018 --
3019 hr_utility.set_location('plip for grade is linked to default GL', 60);
3020 p_persons_pgm_id := l_def_gl;
3021 p_persons_plip_id := l_grade_plip;
3022 p_prog_style := l_dflt_step_cd;
3023 --
3024 End if; /** If l_grade_plip is not null **/
3025 End if; /** If l_grade_pl is not null **/
3026 End if; /** If l_asg_grade IS NOT NULL then **/
3027 --
3028 End if; /** l_def_gl IS NOT NULL **/
3029 --
3030 Else
3031 -- Persons assignment has a grade ladder.
3032 hr_utility.set_location('Person has grade ladder on assignment:'||to_char(l_asg_gl), 70);
3033 p_persons_pgm_id := l_asg_gl;
3034
3035 If l_asg_grade is not null then
3036 -- Find the plan corresponding to the grade and check if it belongs to the pgm.
3037 hr_utility.set_location('Assignment has grade:'||to_char(l_asg_grade), 80);
3038 Open csr_grade_pl(l_asg_grade);
3039 Fetch csr_grade_pl into l_grade_pl;
3040 Close csr_grade_pl;
3041 -- Find the plip for the plan
3042 If l_grade_pl is not null then
3043 --
3044 hr_utility.set_location('Found plan for grade:'|| to_char(l_grade_pl), 90);
3045 Open csr_grade_plip (p_pgm_id => l_asg_gl,p_pl_id => l_grade_pl);
3046 Fetch csr_grade_plip into l_grade_plip;
3047 Close csr_grade_plip;
3048 --
3049 If l_grade_plip is not null then
3050 --
3051 hr_utility.set_location('plip for grade found:'|| to_char(l_grade_plip),100);
3052 p_persons_plip_id := l_grade_plip;
3053 --
3054 Open csr_pgm_details(p_pgm_id => l_asg_gl);
3055 Fetch csr_pgm_details into l_dflt_step_cd;
3056 Close csr_pgm_details;
3057 --
3058 p_prog_style := l_dflt_step_cd;
3059 --
3060 End if; /** If l_grade_plip is not null **/
3061 End if; /** If l_grade_pl is not null **/
3062 End if; /** If l_asg_grade is not null then **/
3063 --
3064 End if; /** If l_asg_gl is null then **/
3065 --
3066 hr_utility.set_location('Leaving get_persons_gl_and_grade',110);
3067 --
3068 End get_persons_gl_and_grade;
3069
3070 /**************************************************************************/
3071 /************************** GSP_RATE_SYNC**********************************/
3072 /**************************************************************************/
3073
3074 Procedure gsp_rate_sync
3075 (P_Effective_Date IN Date
3076 ,p_per_in_ler_id IN NUMBER
3077 ,p_person_id IN NUMBER
3078 ,p_assignment_id IN NUMBER
3079 )
3080 /*
3081 ********* Called from benmngle. Don't change the signature ********
3082 */
3083 is
3084
3085
3086 Cursor per_in_ler_dtls
3087 IS
3088 select ler_id,program_id,business_group_id
3089 from ben_per_in_ler
3090 where per_in_ler_id = p_per_in_ler_id;
3091
3092 Cursor Person_Info
3093 Is
3094 Select Asgt.Business_Group_id,
3095 Asgt.Grade_Id , Asgt.pay_basis_id, Asgt.GRADE_LADDER_PGM_ID
3096 from Per_All_Assignments_f Asgt
3097 Where P_Effective_Date
3098 Between Asgt.Effective_Start_Date
3099 and Asgt.Effective_End_Date
3100 and Asgt.Person_id = p_person_id
3101 And Asgt.assignment_id = p_assignment_id;
3102
3103 Cursor Ben_Grd_Dtls(P_Pgm_Id In Number, P_Grd_Id In Number) Is
3104 Select Pl.Pl_Id, plip.Plip_Id, Pgm.DFLT_STEP_CD,
3105 Pgm.UPDATE_SALARY_CD , DFLT_ELEMENT_TYPE_ID
3106 from Ben_Pgm_F Pgm,
3107 Ben_Pl_F Pl,
3108 Ben_Plip_f Plip
3109 Where Pgm.Pgm_Id = P_Pgm_Id
3110 and P_Effective_Date
3111 Between Pgm.Effective_Start_Date
3112 and Pgm.Effective_End_Date
3113 and Pl.Mapping_table_Name = 'PER_GRADES'
3114 and Pl.Mapping_Table_Pk_Id = P_Grd_Id
3115 and P_Effective_Date
3116 Between Pl.Effective_Start_Date
3117 and Pl.Effective_End_Date
3118 and Plip.Pgm_Id = Pgm.Pgm_id
3119 and Plip.Pl_id = Pl.Pl_Id
3120 and P_Effective_Date
3121 Between Plip.Effective_Start_Date
3122 and Plip.Effective_End_Date;
3123
3124 Cursor Step_Dtls
3125 Is
3126 Select Plcmt.Step_id, Step.Spinal_point_id
3127 From Per_Spinal_POint_Placements_f Plcmt,
3128 Per_Spinal_point_Steps_F Step
3129 Where Plcmt.Assignment_id = P_Assignment_Id
3130 and P_Effective_Date
3131 Between Plcmt.Effective_Start_Date
3132 and plcmt.Effective_End_Date
3133 and Step.Step_id = Plcmt.Step_Id
3134 and P_Effective_Date
3135 Between Step.Effective_Start_Date
3136 and Step.Effective_End_Date;
3137
3138 Cursor Ben_Dtls(P_Pl_id In Number,
3139 P_Point_id In Number) Is
3140 Select Oipl.Oipl_Id, Opt.Opt_Id
3141 From Ben_Opt_F Opt,
3142 Ben_Oipl_F Oipl
3143 Where Opt.Mapping_table_name = 'PER_SPINAL_POINTS'
3144 and Opt.mapping_table_Pk_Id = P_Point_id
3145 and P_Effective_Date
3146 Between Opt.Effective_Start_Date
3147 and Opt.Effective_End_Date
3148 and Oipl.Pl_Id = P_Pl_Id
3149 and Oipl.Opt_id = Opt.Opt_id
3150 and P_Effective_Date
3151 Between Oipl.Effective_Start_Date
3152 and Oipl.Effective_End_Date;
3153
3154 Cursor PlanType(p_business_group_id in number) is
3155 Select Pl_typ_Id
3156 From Ben_Pl_Typ_F
3157 Where Opt_typ_Cd = 'GSP'
3158 and P_Effective_Date
3159 Between Effective_Start_Date
3160 and Effective_End_Date
3161 and business_group_id = p_business_group_id;
3162
3163
3164 CURSOR Elmt_Asgmnt_link (P_Element_Type_Id IN NUMBER) IS
3165 select 'Y'
3166 from pay_element_types_f pet
3167 ,pay_element_links_f pel
3168 ,pay_element_entries_f pee
3169 where Pet.Element_type_id = P_Element_type_Id
3170 and p_Effective_date
3171 Between pet.effective_start_date
3172 and pet.effective_end_date
3173 and Pel.Element_type_Id = Pet.Element_type_Id
3174 and p_Effective_date
3175 Between pel.effective_start_date
3176 and pel.effective_end_date
3177 and pee.element_link_id = pel.element_link_id
3178 and pee.assignment_id = p_assignment_id
3179 and p_Effective_date
3180 between pee.effective_start_date
3181 and pee.effective_end_date;
3182
3183 Cursor Pl_Bas_rt(l_Pl_Id IN Number) Is
3184 Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
3185 From Ben_Acty_base_Rt_f
3186 where Pl_id = l_Pl_Id
3187 and P_effective_Date
3188 Between Effective_Start_Date
3189 and Effective_End_Date;
3190
3191 Cursor Opt_Bas_Rt(l_Opt_Id IN Number) Is
3192 Select ACTY_BASE_RT_ID, Rt_Typ_cd, Entr_Ann_Val_Flag
3193 From Ben_Acty_Base_rt_f
3194 where Opt_Id = L_Opt_id
3195 and P_effective_Date
3196 Between Effective_Start_Date
3197 and Effective_End_Date;
3198
3199 Cursor Yr_Prd is
3200 Select Yr_Perd_id
3201 From Ben_Yr_Perd
3202 Where P_Effective_Date
3203 Between Start_Date and End_Date;
3204
3205 Cursor Dflt_GrdLdr is
3206 Select Pgm_Id
3207 From Ben_PGM_F
3208 Where DFLT_PGM_FLAG = 'Y'
3209 and Pgm_Typ_Cd = 'GSP'
3210 and P_Effective_Date
3211 Between Effective_Start_Date
3212 and Effective_End_Date
3213 and Business_Group_id = Hr_general.get_business_group_id;
3214
3215 L_PTNL_LER_FOR_PER_ID Ben_Ptnl_Ler_For_Per.PTNL_LER_FOR_PER_ID%TYPE;
3216 l_Ptnl_Ovn Ben_Ptnl_Ler_For_Per.Object_Version_Number%TYPE;
3217 L_Pil_Ovn Ben_Per_In_Ler.Object_version_Number%TYPE;
3218 L_BG_Id Per_All_Assignments_F.Business_Group_id%TYPE;
3219 l_Grade_Id Per_Grades.Grade_Id%TYPE;
3220 l_Pgm_Id Ben_Pgm_F.Pgm_Id%TYPE;
3221 l_Step_Id Per_Spinal_Point_Steps_f.Step_Id%TYPE;
3222 l_Point_id Per_Spinal_POints.Spinal_Point_Id%TYPE;
3223 l_PROCD_DT DATE;
3224 l_STRTD_DT DATE;
3225 l_VOIDD_DT Date;
3226 l_Step_Exists Varchar2(1) := 'N';
3227 L_Pl_id Ben_Pl_F.Pl_Id%TYPE;
3228 L_plip_Id Ben_PLip_F.Plip_Id%TYPE;
3229 L_Oipl_Id Ben_Oipl_F.Oipl_Id%TYPE;
3230 L_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
3231 L_Oipl_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
3232 l_Pl_Typ_Id Ben_Pl_Typ_F.Pl_Typ_Id%TYPE;
3233 l_Elctbl_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
3234 l_oipl_Elctbl_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
3235 l_Prog_style Ben_Pgm_F.Enrt_Cd%TYPE;
3236 L_Prog_opt Ben_Pgm_F.DFLT_STEP_CD%TYPE;
3237 L_Elctbl_Flag Varchar2(1) := 'N';
3238 L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
3239 L_pay_basis_id per_pay_bases.pay_basis_id%TYPE;
3240 l_Dflt_Element_typ_Id Ben_Pgm_F.DFLT_ELEMENT_TYPE_ID%TYPE;
3241 L_Enrt_Rt_Id Ben_Enrt_Rt.Enrt_Rt_Id%TYPE;
3242 l_Cur_Sal Ben_Enrt_Rt.Val%TYPE;
3243 l_Rt_Typ_Cd Ben_Enrt_Rt.Rt_Typ_Cd%TYPE;
3244 L_Rt_Elig_Per_Elctbl_Chc_Id Ben_Elig_Per_Elctbl_Chc.Elig_Per_Elctbl_Chc_Id%TYPE;
3245 L_Acty_Base_rt_Id Ben_Acty_Base_Rt.Acty_Base_rt_Id%TYPE;
3246 l_Enrt_Rt_Ovn Ben_Elig_Per_Elctbl_Chc.Object_version_Number%TYPE;
3247 l_Opt_id Ben_Opt_F.Opt_Id%TYPE;
3248 l_Entr_Ann_Val_Flag Ben_Acty_Base_rt_F.Entr_Ann_Val_Flag%TYPE;
3249 L_Lee_Rsn_Id Ben_Lee_Rsn_f.Lee_Rsn_Id%TYPE;
3250 l_yr_perd_id ben_popl_yr_perd.yr_perd_id%TYPE;
3251 L_popl_yr_perd_id ben_popl_yr_perd.popl_yr_perd_id%TYPE;
3252 l_start_date ben_yr_perd.start_date%TYPE;
3253 l_end_date ben_yr_perd.end_date%TYPE;
3254 l_Element_Link_Id Pay_Element_Links_f.Element_Link_Id%TYPE;
3255 l_Curr_Pl_Id Ben_Pl_F.Pl_Id%TYPE;
3256 l_Curr_Oipl_Id Ben_Oipl_F.Oipl_Id%TYPE;
3257
3258
3259 Begin
3260
3261 g_debug := hr_utility.debug_enabled;
3262 if g_debug then
3263 hr_utility.set_location(' Entering pqh_gsp_post_process.gsp_rate_sync ', 5);
3264 hr_utility.set_location(' Assgt_Id : ' || p_assignment_id, 10);
3265 hr_utility.set_location(' Person Id : ' || p_person_id, 20);
3266 hr_utility.set_location(' Per_In_Ler_Id : ' || p_per_in_ler_id, 30);
3267 hr_utility.set_location(' Effective_Date : ' || P_Effective_Date, 40);
3268 End if;
3269
3270 open per_in_ler_dtls;
3271 fetch per_in_ler_dtls into L_Lee_Rsn_Id,l_Pgm_Id,l_bg_id;
3272 close per_in_ler_dtls;
3273 if g_debug then
3274 hr_utility.set_location(' Ler_Id : ' || L_Lee_Rsn_Id, 50);
3275 hr_utility.set_location(' Pgm Id : ' || l_pgm_id, 60);
3276 hr_utility.set_location(' bg_Id : ' || l_bg_id, 70);
3277 End if;
3278
3279 Open Person_Info;
3280 Fetch Person_Info Into L_BG_Id , L_Grade_Id, L_pay_basis_id, l_Pgm_Id;
3281 Close Person_Info;
3282
3283 if g_debug then
3284 hr_utility.set_location(' Pgm_Id : ' || l_Pgm_Id, 20);
3285 hr_utility.set_location(' Pay_basis_Id : ' || l_pay_basis_id, 80);
3286 hr_utility.set_location(' Grade Id : ' || l_grade_id, 90);
3287 hr_utility.set_location(' bg_Id : ' || l_bg_id, 100);
3288 End if;
3289
3290 Open Ben_Grd_Dtls(l_Pgm_Id, L_Grade_Id);
3291 Fetch Ben_Grd_Dtls into L_Pl_id, l_plip_Id , l_Prog_style, L_Update_Salary_Cd, l_Dflt_Element_typ_Id;
3292 Close Ben_Grd_Dtls;
3293
3294 If l_Pl_Id Is Null Then
3295 /* Plan is not not linked to the corresponding Grade */
3296 fnd_message.set_name('PQH','PQH_GSP_PLN_NOTLNKD_TO_GRD');
3297 fnd_message.raise_error;
3298 End If;
3299
3300 If l_Prog_style = 'PQH_GSP_NP' Then
3301 Return;
3302 elsif l_Prog_style is NULL then
3303 fnd_message.set_name('PQH','PQH_GSP_PRGSTYLE_NOT_SET');
3304 fnd_message.raise_error;
3305 End If;
3306
3307 If L_Update_Salary_Cd = 'SALARY_BASIS' and L_pay_basis_id is NULL Then
3308 /* Grade Ladder is defined for Salary basis and Pay Basis is not attached to the assignment */
3309 fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
3310 fnd_message.raise_error;
3311
3312 /*
3313 ElsIf L_Update_Salary_Cd = 'SALARY_ELEMENT' then */
3314
3315 /* Grade Ladder uses Salary Element, but Default Salary Element type is not defined */
3316 /* If l_Dflt_Element_typ_Id is NULL Then
3317 fnd_message.set_name('PQH','PQH_GSP_DFLT_ELMNT_NOTDFND');
3318 fnd_message.raise_error;
3319 End If;
3320
3321 l_Element_Link_Id := hr_entry_api.get_link
3322 (P_Assignment_id
3323 ,l_Dflt_Element_typ_Id
3324 ,P_Effective_Date);
3325
3326 If l_Element_Link_Id is NULL Then
3327 fnd_message.set_name('PQH','PQH_GSP_ELMNT_NOT_LNKD');
3328 fnd_message.raise_error;
3329 End If;
3330 Cursor Step_Dtls(p_assignment_id in number) Is
3331 Select Plcmt.Step_id, Step.Spinal_point_id, Plcmt.Effective_Start_Date
3332 */
3333
3334 End If;
3335
3336
3337 If l_Prog_style in ('PQH_GSP_SP','PQH_GSP_GSP','MINSALINCR','MINSTEP','NOSTEP') Then
3338 Open Step_Dtls;
3339 Fetch Step_Dtls Into L_Step_Id, l_Point_id;
3340 Close Step_Dtls;
3341 /* Step not defined for Assignment */
3342 If l_Step_Id is NULL Then
3343 fnd_message.set_name('PQH','PQH_GSP_NO_STEP');
3344 fnd_message.raise_error;
3345 Else
3346 Open Ben_Dtls(L_Pl_id,
3347 L_Point_Id);
3348 Fetch Ben_Dtls Into L_Oipl_Id, l_Opt_id;
3349 Close Ben_Dtls;
3350 If L_Oipl_Id is NULL then
3351 /* Oipl not linked to Step */
3352 fnd_message.set_name('PQH','PQH_GSP_OIPL_NOTLNKD_TO_STEP');
3353 fnd_message.raise_error;
3354 End If;
3355 End If;
3356 L_Elctbl_Flag := 'N';
3357
3358 Open Opt_Bas_rt(l_Opt_Id);
3359 Fetch Opt_Bas_Rt into l_ACTY_BASE_RT_ID, l_Rt_Typ_cd, l_Entr_Ann_Val_Flag;
3360 Close Opt_Bas_Rt;
3361 if g_debug then
3362 hr_utility.set_location(' l_Pgm_Id ' || l_Pgm_Id, 50);
3363 hr_utility.set_location(' L_Pl_id ' || L_Pl_id, 60);
3364 hr_utility.set_location(' l_Oipl_Id ' || l_Oipl_Id, 70);
3365 hr_utility.set_location(' L_Person_Id ' || p_Person_Id, 80);
3366 End if;
3367 Else
3368
3369 L_Elctbl_Flag := 'Y';
3370 Open Pl_Bas_rt(l_Pl_Id);
3371 Fetch Pl_Bas_rt into l_ACTY_BASE_RT_ID, l_Rt_Typ_cd, l_Entr_Ann_Val_Flag;
3372 Close Pl_Bas_rt;
3373
3374 End If;
3375
3376
3377 Open Plantype(l_bg_id);
3378 Fetch Plantype into l_Pl_Typ_Id;
3379 Close Plantype;
3380
3381 /* Create Electable Choice Records */
3382 /* For Grade */
3383
3384 if g_debug then
3385 hr_utility.set_location(' Attempting to create Elig_per ', 140);
3386 hr_utility.set_location(' Business_Group_Id :' || l_Bg_Id, 150);
3387 end if;
3388
3389 Open Yr_Prd;
3390 Fetch Yr_Prd into l_Yr_Perd_Id;
3391 Close Yr_Prd;
3392
3393 IF l_prog_style = 'PQH_GSP_GP' then
3394
3395 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
3396 (P_ELIG_PER_ELCTBL_CHC_ID => L_Elig_Per_Elctbl_Chc_Id
3397 ,P_ENRT_CVG_STRT_DT_CD => P_Effective_Date
3398 ,P_DFLT_FLAG => 'Y'
3399 ,P_ELCTBL_FLAG => L_Elctbl_Flag
3400 ,P_PL_ID => l_Pl_Id
3401 ,P_PGM_ID => l_Pgm_Id
3402 ,P_PLIP_ID => l_plip_Id
3403 ,P_PGM_TYP_CD => 'GSP'
3404 ,P_PL_TYP_ID => l_Pl_Typ_Id
3405 ,P_PER_IN_LER_ID => p_PER_IN_LER_ID
3406 ,P_YR_PERD_ID => l_yr_perd_id
3407 ,P_Enrt_Cvg_Strt_Dt => P_Effective_Date
3408 ,P_COMP_LVL_CD => 'PLAN' /* abparekh */
3409 ,P_LEE_RSN_ID => L_LEE_RSN_ID
3410 ,P_AUTO_ENRT_FLAG => 'Y'
3411 ,P_BUSINESS_GROUP_ID => l_Bg_Id
3412 ,P_ELIG_FLAG => 'N'
3413 ,P_OBJECT_VERSION_NUMBER => l_Elctbl_Ovn
3414 ,P_EFFECTIVE_DATE => P_Effective_Date);
3415
3416 L_Rt_Elig_Per_Elctbl_Chc_Id := L_Elig_Per_Elctbl_Chc_Id;
3417
3418
3419 if g_debug then
3420 hr_utility.set_location(' Created Elig_per for PLIP', 160);
3421 hr_utility.set_location(' L_Elig_Per_Elctbl_Chc_Id : ' || L_Elig_Per_Elctbl_Chc_Id, 170);
3422 End If;
3423
3424 elsif l_Prog_style in ('PQH_GSP_SP','PQH_GSP_GSP','MINSALINCR','MINSTEP','NOSTEP') Then
3425
3426 Ben_Elig_Per_Elc_Chc_Api.CREATE_PERF_ELIG_PER_ELC_CHC
3427 (P_ELIG_PER_ELCTBL_CHC_ID => L_Oipl_Elig_Per_Elctbl_Chc_Id
3428 ,P_ENRT_CVG_STRT_DT_CD => P_Effective_Date
3429 ,P_DFLT_FLAG => 'Y'
3430 ,P_ELCTBL_FLAG => 'Y'
3431 ,P_PL_ID => l_Pl_Id
3432 ,P_PGM_ID => l_Pgm_Id
3433 ,P_PLIP_ID => l_plip_Id
3434 ,P_OIPL_ID => l_Oipl_Id
3435 ,P_PGM_TYP_CD => 'GSP'
3436 ,P_PL_TYP_ID => l_Pl_Typ_Id
3437 ,P_Enrt_Cvg_Strt_Dt => P_Effective_Date
3438 ,P_YR_PERD_ID => l_yr_perd_id
3439 ,P_PER_IN_LER_ID => p_PER_IN_LER_ID
3440 ,P_COMP_LVL_CD => 'OIPL'
3441 ,P_LEE_RSN_ID => L_LEE_RSN_ID
3442 ,P_AUTO_ENRT_FLAG => 'Y'
3443 ,P_BUSINESS_GROUP_ID => l_Bg_Id
3444 ,P_ELIG_FLAG => 'N'
3445 ,P_OBJECT_VERSION_NUMBER => l_Oipl_Elctbl_Ovn
3446 ,P_EFFECTIVE_DATE => P_Effective_Date);
3447
3448 L_Rt_Elig_Per_Elctbl_Chc_Id := L_Oipl_Elig_Per_Elctbl_Chc_Id;
3449 if g_debug then
3450 hr_utility.set_location(' Created Elig_per for OIPL', 160);
3451 hr_utility.set_location(' L_Elig_Per_Elctbl_Chc_Id : ' || L_Elig_Per_Elctbl_Chc_Id, 170);
3452 End If;
3453
3454 End If;
3455 If L_Update_Salary_Cd is NULL Then
3456 fnd_message.set_name('PQH','PQH_GSP_POSTSTYL_NOT_SET');
3457 fnd_message.raise_error;
3458 End If;
3459 if g_debug then
3460 hr_utility.set_location(' Determine Rates ', 190);
3461 End If;
3462
3463 ben_env_object.init(p_business_group_id => l_Bg_Id,
3464 p_effective_date => P_Effective_Date,
3465 p_thread_id => 1,
3466 p_chunk_size => 1,
3467 p_threads => 1,
3468 p_max_errors => 1,
3469 p_benefit_action_id => null);
3470
3471 ben_env_object.setenv(P_LF_EVT_OCRD_DT => P_Effective_Date);
3472 ben_env_object.g_global_env_rec.mode_cd := 'G';
3473 Ben_determine_rates.Main
3474 (P_EFFECTIVE_DATE => P_Effective_Date
3475 ,P_LF_EVT_OCRD_DT => P_Effective_Date
3476 ,P_PERSON_ID => p_Person_Id
3477 ,P_PER_IN_LER_ID => p_PER_IN_LER_ID
3478 ,p_elig_per_elctbl_chc_id => L_Rt_Elig_Per_Elctbl_Chc_Id);
3479
3480 Exception
3481 When Others Then
3482 Raise;
3483 End gsp_rate_sync;
3484
3485 /**************************************************************************/
3486 /************************** Update Rate Sync Salary. **********************/
3487 /**************************************************************************/
3488
3489 Procedure Update_Rate_Sync_Salary
3490 (P_per_in_ler_Id IN Number
3491 ,P_Effective_Date IN Date
3492 ) Is
3493 /*
3494 ********* Called from benmngle. Don't change the signature ********
3495 */
3496 Cursor csr_elct_chcs
3497 is
3498 Select Elig_per_Elctbl_Chc_Id,COMP_LVL_CD,pgm_id
3499 from Ben_ELig_per_Elctbl_Chc
3500 where per_in_ler_id = p_per_in_ler_id;
3501
3502 Cursor csr_pil_dtls
3503 IS
3504 select object_version_number,PTNL_LER_FOR_PER_ID
3505 from ben_per_in_ler
3506 where per_in_ler_id = p_per_in_ler_id;
3507
3508 Cursor csr_ptnl_ler_dtls(p_PTNL_LER_FOR_PER_ID in number)
3509 IS
3510 select object_version_number
3511 from ben_ptnl_ler_for_per
3512 where PTNL_LER_FOR_PER_ID = p_PTNL_LER_FOR_PER_ID;
3513
3514
3515
3516
3517 l_Pay_Proposal_Id Per_Pay_Proposals.Pay_Proposal_Id%TYPE;
3518 L_Pay_Proposals_Ovn Per_Pay_Proposals.Object_version_Number%TYPE;
3519 l_PTNL_LER_FOR_PER_ID ben_ptnl_ler_for_per.PTNL_LER_FOR_PER_ID%TYPE;
3520 l_PTNL_LER_FOR_PER_OVN ben_ptnl_ler_for_per.object_version_number%TYPE;
3521 l_Rt_Ovn Ben_Prtt_Rt_Val.Object_Version_Number%TYPE;
3522 l_salary Ben_Prtt_Rt_Val.Rt_Val%TYPE;
3523 L_INV_NEXT_SAL_DATE_WARNING Boolean;
3524 L_PROPOSED_SALARY_WARNING Boolean;
3525 L_APPROVED_WARNING Boolean;
3526 l_salary_change_date date;
3527 L_PAYROLL_WARNING Boolean;
3528 l_Del_Warn Boolean;
3529 L_ERROR_TEXT Varchar2(250);
3530 L_Update_Salary_Cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
3531 L_DFLT_INPUT_VALUE_ID Ben_Pgm_F.DFLT_INPUT_VALUE_ID%TYPE;
3532 L_DFLT_ELEMENT_TYPE_ID Ben_Pgm_F.DFLT_ELEMENT_TYPE_ID%TYPE;
3533 l_Element_Link_Id Pay_Element_Links_f.Element_Link_Id%TYPE;
3534 L_Effective_Start_Date pay_element_entries_f.Effective_Start_Date%TYPE;
3535 L_Effective_End_Date pay_element_entries_f.Effective_End_Date%TYPE;
3536 L_Element_Entry_ID pay_element_entries_f.Element_Entry_Id%TYPE;
3537 L_Ele_Ovn pay_element_entries_f.Object_Version_Number%TYPE;
3538 l_Create_Warn Boolean;
3539 l_DFLT_STEP_CD Ben_Pgm_F.DFLT_STEP_CD%TYPE;
3540 l_Change_Dt Per_Pay_proposals.Change_Date%TYPE;
3541 l_Del_proposal_Id Per_Pay_proposals.Pay_proposal_Id%TYPE;
3542 l_Del_Proposal_Ovn per_pay_Proposals.Object_version_Number%TYPE;
3543 l_cur_sal number;
3544 l_per_in_ler_ovn number;
3545 l_procd_date date;
3546 l_strtd_date date;
3547 l_voidd_date date;
3548 l_per_in_ler_id number;
3549
3550 l_ACTY_REF_PERD_CD ben_pgm_f.ACTY_REF_PERD_CD%TYPE;
3551 l_ANN_VAL ben_enrt_rt.ann_val%TYPE;
3552
3553 l_payroll_annualization_factor per_time_period_types.number_per_fiscal_year%TYPE;
3554 L_Payroll_name pay_all_payrolls_f.Payroll_name%TYPE;
3555
3556 Cursor Enroll_Info(P_Elig_per_Elctbl_Chc_Id in number) is
3557 Select Rate.Val , Rate.Rt_Strt_Dt, Rate.Prtt_Rt_Val_Id,
3558 Rate.Object_Version_Number, Enrt.Pgm_Id, Enrt.OiPl_Id,
3559 Asgt.Assignment_Id, Asgt.pay_basis_id, Asgt.Grade_Id, Enrt.Business_Group_id,Pil.per_in_ler_id, Rate.Ann_val
3560 From Ben_ELig_per_Elctbl_Chc Enrt,
3561 ben_Enrt_Rt Rate,
3562 Ben_Per_in_ler PIL,
3563 Per_All_Assignments_F Asgt
3564 Where Enrt.Elig_per_Elctbl_Chc_id = P_Elig_per_Elctbl_Chc_Id
3565 And Enrt.Per_In_Ler_id = Pil.Per_In_Ler_id
3566 And Asgt.Person_id = PIL.Person_id
3567 And P_Effective_Date
3568 Between Asgt.Effective_start_Date and Asgt.Effective_end_Date
3569 and Enrt.Elig_per_Elctbl_Chc_id = Rate.Elig_per_Elctbl_Chc_id(+)
3570 and asgt.assignment_type ='E'
3571 And Asgt.PRIMARY_FLAG = 'Y';
3572
3573
3574 CURSOR Element_Info(P_assignmnet_id number,P_pay_basis_id number, P_Effective_Date in DAte) IS
3575 Select ele.element_entry_id
3576 from per_pay_bases bas,
3577 pay_element_entries_f ele,
3578 pay_element_entry_values_f entval
3579 where bas.pay_basis_id = P_pay_basis_id
3580 and entval.input_value_id = bas.input_value_id
3581 and p_effective_date
3582 between entval.effective_start_date
3583 and entval.effective_end_date
3584 and ele.assignment_id = P_assignmnet_id
3585 and p_effective_date between ele.effective_start_date
3586 and ele.effective_end_date
3587 and ele.element_entry_id = entval.element_entry_id;
3588
3589 Cursor Pgm_Dtl(P_Pgm_Id In Number) is
3590 Select Update_Salary_Cd, DFLT_INPUT_VALUE_ID, DFLT_ELEMENT_TYPE_ID, DFLT_STEP_CD,ACTY_REF_PERD_CD
3591 From ben_Pgm_F
3592 Where Pgm_id = P_Pgm_Id
3593 and P_effective_Date
3594 Between Effective_Start_Date
3595 and Effective_End_Date;
3596
3597 CURSOR Elmt_Entry (p_assignment_id IN NUMBER, P_Business_group_Id IN Number, p_Effective_Date IN Date) IS
3598 select pee.Element_Entry_Id, pee.Object_version_Number
3599 from pay_element_types_f pet
3600 ,pay_element_links_f pel
3601 ,pay_element_entries_f pee
3602 where Pet.Element_type_id = L_DFLT_ELEMENT_TYPE_ID
3603 and p_Effective_date
3604 Between pet.effective_start_date
3605 and pet.effective_end_date
3606 and Pel.Element_type_Id = Pet.Element_type_Id
3607 and p_Effective_date
3608 Between pel.effective_start_date
3609 and pel.effective_end_date
3610 and pee.element_link_id = pel.element_link_id
3611 and pee.assignment_id = p_assignment_id
3612 and p_Effective_date
3613 between pee.effective_start_date
3614 and pee.effective_end_date;
3615
3616 Cursor Proposal_Dt (P_Assignment_Id IN Number) is
3617 Select Max(Change_Date)
3618 from Per_Pay_Proposals
3619 Where Assignment_Id = P_Assignment_id;
3620
3621 Cursor Proposal_Dtls (P_Assignment_Id in Number) is
3622 Select Pay_Proposal_Id, Object_Version_Number
3623 From Per_Pay_Proposals
3624 Where Change_Date = l_Change_Dt
3625 and Assignment_id = P_Assignment_id;
3626
3627 Cursor pay_basis_frequency (p_pay_basis_id in number)is
3628 select pay_basis,PAY_ANNUALIZATION_FACTOR
3629 from per_pay_bases
3630 where pay_basis_id =p_pay_basis_id;
3631
3632
3633 L_Enroll_Info Enroll_Info%ROWTYPE;
3634 L_DATE_TRACK_MODE Varchar2(25);
3635 l_def_elct_chc_id number;
3636 l_plan_elct_chc_id number;
3637 l_oipl_elct_chc_id number;
3638 l_pgm_id number;
3639 l_pay_basis per_pay_bases.PAY_BASIS%TYPE;
3640 l_annualization_factor per_pay_bases.PAY_ANNUALIZATION_FACTOR%TYPE;
3641 l_gl_ann_factor ben_pgm_extra_info.pgi_information5%TYPE;
3642
3643 Begin
3644
3645 g_debug := hr_utility.debug_enabled;
3646 if g_debug then
3647 hr_utility.set_location(' Entering pqh_gsp_post_process.update_rate_sync_salary ', 5);
3648 hr_utility.set_location(' Per_In_Ler_Id : ' || p_per_in_ler_id, 10);
3649 hr_utility.set_location(' Effective_Date : ' || P_Effective_Date, 20);
3650 End if;
3651
3652
3653 for i in csr_elct_chcs loop
3654 if i.comp_lvl_cd = 'OIPL' then
3655 l_oipl_elct_chc_id := i.Elig_per_Elctbl_Chc_Id;
3656 else
3657 l_plan_elct_chc_id := i.Elig_per_Elctbl_Chc_Id;
3658 end if;
3659 l_pgm_id := i.pgm_id;
3660 end loop;
3661
3662 OPen Pgm_Dtl(l_Pgm_Id);
3663 Fetch Pgm_Dtl into L_Update_Salary_Cd, L_DFLT_INPUT_VALUE_ID, L_DFLT_ELEMENT_TYPE_ID, l_Dflt_Step_Cd,l_acty_ref_perd_cd;
3664 Close Pgm_Dtl;
3665
3666 if l_Dflt_Step_cd = 'PQH_GSP_GP' then
3667 l_def_elct_chc_id := l_plan_elct_chc_id;
3668 else
3669 l_def_elct_chc_id := l_oipl_elct_chc_id;
3670 end if;
3671
3672
3673 Open Enroll_Info(l_def_elct_chc_id);
3674 Fetch Enroll_Info into L_Enroll_Info;
3675 Close Enroll_Info;
3676
3677 if g_debug then
3678 hr_utility.set_location(' Inside Salary Update: Elec_id :' ||l_def_elct_chc_id , 10);
3679 hr_utility.set_location(' L_Enroll_Info.Business_Group_Id :' || L_Enroll_Info.Business_Group_Id, 20);
3680 hr_utility.set_location(' L_Enroll_Info.OVN :' || L_Enroll_Info.Object_version_number, 30);
3681 End If;
3682
3683 open csr_pil_dtls;
3684 fetch csr_pil_dtls into l_per_in_ler_ovn, l_PTNL_LER_FOR_PER_ID;
3685 close csr_pil_dtls;
3686
3687 open csr_ptnl_ler_dtls(l_PTNL_LER_FOR_PER_ID);
3688 fetch csr_ptnl_ler_dtls into l_PTNL_LER_FOR_PER_OVN;
3689 close csr_ptnl_ler_dtls;
3690
3691 If L_Enroll_Info.Val is NULL Then
3692
3693 If l_Dflt_Step_cd = 'NOSTEP' and L_Enroll_Info.Oipl_Id is NULL Then
3694
3695 Return;
3696
3697 Else
3698
3699 fnd_message.set_name('PQH','PQH_GSP_RAT_NOT_DFND');
3700 fnd_message.raise_error;
3701
3702 End If;
3703 End If;
3704
3705
3706 l_Cur_Sal := Pqh_gsp_utility.Get_Cur_Sal
3707 (P_Assignment_id => L_Enroll_Info.Assignment_Id
3708 ,P_Effective_Date => P_Effective_date);
3709
3710 open pay_basis_frequency(L_Enroll_Info.pay_basis_id);
3711 fetch pay_basis_frequency into l_pay_basis,l_annualization_factor;
3712 close pay_basis_frequency;
3713
3714
3715 l_gl_ann_factor := pqh_gsp_utility.get_gl_ann_factor(p_pgm_id => l_pgm_id);
3716
3717 hr_utility.set_location('l_gl_ann_factor is:' ||l_gl_ann_factor , 40);
3718
3719 if l_gl_ann_factor is not null then
3720
3721 l_ANN_VAL :=L_Enroll_Info.Val *to_number(l_gl_ann_factor);
3722
3723 ELSIF (l_pay_basis = 'MONTHLY' AND l_ACTY_REF_PERD_CD = 'MO')
3724 OR (l_pay_basis = 'HOURLY' AND l_ACTY_REF_PERD_CD = 'PHR')
3725 OR (l_pay_basis = 'ANNUAL' AND l_ACTY_REF_PERD_CD = 'PYR') THEN
3726
3727 l_ANN_VAL :=L_Enroll_Info.Val *l_annualization_factor;
3728
3729 Else
3730
3731 l_ANN_VAL := L_Enroll_Info.Ann_Val;
3732
3733 end if;
3734
3735 if l_Ann_Val = l_cur_sal then
3736 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
3737 (P_PER_IN_LER_ID => p_PER_IN_LER_ID
3738 ,P_PER_IN_LER_STAT_CD => 'VOIDD'
3739 ,P_PROCD_DT => l_procd_date
3740 ,P_STRTD_DT => l_strtd_date
3741 ,P_VOIDD_DT => l_voidd_date
3742 ,P_OBJECT_VERSION_NUMBER => l_per_in_ler_ovn
3743 ,P_EFFECTIVE_DATE => P_Effective_Date);
3744
3745 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
3746 (p_ptnl_ler_for_per_id => l_PTNL_LER_FOR_PER_ID
3747 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
3748 ,p_voidd_dt => p_effective_date
3749 ,p_object_version_number => l_PTNL_LER_FOR_PER_OVN
3750 ,p_effective_date => p_effective_date);
3751
3752 else
3753 l_salary := L_Enroll_Info.Val;
3754 l_salary_change_date := L_Enroll_Info.Rt_Strt_Dt;
3755
3756 l_Salary := Pqh_Gsp_Utility.PGM_TO_BASIS_CONVERSION
3757 (P_Pgm_Id => L_Enroll_Info.Pgm_Id
3758 ,P_EFFECTIVE_DATE => P_Effective_Date
3759 ,P_AMOUNT => l_Salary
3760 ,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id);
3761
3762
3763
3764 If L_Update_Salary_Cd = 'SALARY_BASIS' Then
3765 If L_Enroll_Info.Pay_Basis_Id is NULL Then
3766 fnd_message.set_name('PQH','PQH_GSP_SALBSIS_NOT_LNKD');
3767 fnd_message.raise_error;
3768 End If;
3769
3770
3771 Open Proposal_Dt(L_Enroll_Info.Assignment_Id);
3772 Fetch Proposal_Dt into l_Change_Dt;
3773 Close Proposal_Dt;
3774
3775 if l_change_dt = l_salary_change_date then
3776
3777 Open Proposal_Dtls(L_Enroll_Info.Assignment_Id);
3778 Fetch proposal_Dtls into l_Del_Proposal_Id, l_Del_Proposal_Ovn;
3779 Close Proposal_Dtls;
3780
3781 if l_Del_Proposal_Id is Not NULL then
3782
3783 Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
3784 (P_PAY_PROPOSAL_ID => l_Del_proposal_Id
3785 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
3786 ,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
3787 ,P_SALARY_WARNING => l_Del_Warn);
3788
3789 End If;
3790
3791 End if;
3792
3793 Open Element_Info(L_Enroll_Info.Assignment_Id, L_Enroll_Info.pay_basis_id, l_Enroll_Info.Rt_Strt_Dt);
3794 Fetch Element_Info Into L_Element_Entry_Id;
3795 Close Element_Info;
3796
3797 Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
3798 (P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
3799 ,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id
3800 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
3801 ,P_CHANGE_DATE => L_Enroll_Info.RT_Strt_Dt
3802 ,P_PROPOSED_SALARY_N => l_Salary
3803 ,P_OBJECT_VERSION_NUMBER => L_Pay_Proposals_Ovn
3804 ,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
3805 ,P_MULTIPLE_COMPONENTS => 'N'
3806 ,P_APPROVED => 'Y'
3807 ,P_PROPOSAL_REASON => 'GSP'
3808 ,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
3809 ,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
3810 ,P_APPROVED_WARNING => L_APPROVED_WARNING
3811 ,P_PAYROLL_WARNING => L_PAYROLL_WARNING);
3812
3813 Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
3814
3815 If L_DFLT_INPUT_VALUE_ID is NULL or L_DFLT_ELEMENT_TYPE_ID is NULL Then
3816 fnd_message.set_name('PQH','PQH_GSP_DFLY_ELMNT_NOT_LNKD');
3817 fnd_message.raise_error;
3818 End If;
3819
3820 l_Element_Link_Id := hr_entry_api.get_link
3821 (L_Enroll_Info.Assignment_Id
3822 ,L_DFLT_ELEMENT_TYPE_ID
3823 ,L_Enroll_Info.RT_Strt_Dt);
3824
3825 if l_Element_Link_Id is NULL Then
3826 fnd_message.set_name('PQH','PQH_GSP_ELMNT_NOT_LNKD');
3827 fnd_message.raise_error;
3828 End If;
3829
3830 If L_Enroll_Info.Val is NULL Then
3831 fnd_message.set_name('PQH','PQH_GSP_RAT_NOT_DFND');
3832 fnd_message.raise_error;
3833 End If;
3834
3835
3836 per_pay_proposals_populate.get_payroll(L_Enroll_Info.Assignment_Id
3837 ,L_Enroll_Info.RT_Strt_Dt
3838 ,l_Payroll_name
3839 ,l_payroll_annualization_factor);
3840
3841 If L_Payroll_name is NULL Then
3842 fnd_message.set_name('PQH','PQH_GSP_PAYROLL_NOT_DFND');
3843 fnd_message.raise_error;
3844 End If;
3845
3846 Open Elmt_Entry(L_Enroll_Info.Assignment_Id, L_Enroll_Info.Business_Group_Id, L_Enroll_Info.Rt_Strt_Dt);
3847 Fetch Elmt_Entry into L_Element_Entry_ID, L_Ele_Ovn;
3848 If Elmt_Entry%Found Then
3849
3850 L_DATE_TRACK_MODE := DT_Mode
3851 (P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
3852 ,P_BASE_TABLE_NAME => 'PAY_ELEMENT_ENTRIES_F'
3853 ,P_BASE_KEY_COLUMN => 'ELEMENT_ENTRY_ID'
3854 ,P_BASE_KEY_VALUE => L_Element_Entry_ID);
3855
3856 Pay_Element_Entry_Api.UPDATE_ELEMENT_ENTRY
3857 (P_DATETRACK_UPDATE_MODE => L_DATE_TRACK_MODE
3858 ,P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
3859 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
3860 ,P_ELEMENT_ENTRY_ID => L_ELEMENT_ENTRY_ID
3861 ,P_OBJECT_VERSION_NUMBER => L_Ele_Ovn
3862 ,P_INPUT_VALUE_ID1 => L_DFLT_INPUT_VALUE_ID
3863 ,P_ENTRY_VALUE1 => L_Salary
3864 ,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
3865 ,P_EFFECTIVE_END_DATE => L_Effective_End_Date
3866 ,P_UPDATE_WARNING => l_Create_Warn);
3867
3868 Else
3869
3870 Pay_Element_Entry_Api.CREATE_ELEMENT_ENTRY
3871 (P_EFFECTIVE_DATE => L_Enroll_Info.RT_Strt_Dt
3872 ,P_BUSINESS_GROUP_ID => L_Enroll_Info.Business_Group_Id
3873 ,P_ASSIGNMENT_ID => L_Enroll_Info.Assignment_Id
3874 ,P_ELEMENT_LINK_ID => l_Element_Link_Id
3875 ,P_ENTRY_TYPE => 'E'
3876 ,P_INPUT_VALUE_ID1 => L_DFLT_INPUT_VALUE_ID
3877 ,P_ENTRY_VALUE1 => L_Salary
3878 ,P_EFFECTIVE_START_DATE => L_Effective_Start_Date
3879 ,P_EFFECTIVE_END_DATE => L_Effective_End_Date
3880 ,P_ELEMENT_ENTRY_ID => L_Element_Entry_ID
3881 ,P_OBJECT_VERSION_NUMBER => L_Ele_Ovn
3882 ,P_CREATE_WARNING => l_Create_Warn);
3883 End If;
3884 Close Elmt_Entry;
3885 Else
3886 Return;
3887 End If; /* Update_Salary_Cd */
3888 Ben_Person_Life_Event_api.UPDATE_PERSON_LIFE_EVENT
3889 (P_PER_IN_LER_ID => p_PER_IN_LER_ID
3890 ,P_PER_IN_LER_STAT_CD => 'PROCD'
3891 ,P_PROCD_DT => l_procd_date
3892 ,P_STRTD_DT => l_strtd_date
3893 ,P_VOIDD_DT => l_voidd_date
3894 ,P_OBJECT_VERSION_NUMBER => l_per_in_ler_ovn
3895 ,P_EFFECTIVE_DATE => P_Effective_Date);
3896 End if;
3897
3898 End Update_Rate_Sync_Salary;
3899
3900 --
3901 Procedure call_from_webadi
3902 (P_Elig_Per_Elctbl_Chc_id IN Number
3903 ,P_PROGRESSION_DATE IN Date
3904 ,P_Sal_Chg_Dt IN Date
3905 ,p_assignment_id IN NUMBER
3906 ,p_proposed_rank in number
3907 ,p_life_event_dt in date
3908 ,p_grade_ladder_id in number
3909 ,p_pl_id in number
3910 ,p_oipl_id in number
3911 )
3912 IS
3913 l_temp_prog_date date;
3914 l_temp_sal_chg_date date;
3915 l_temp_proposed_rank number;
3916 L_Enrt_Rt_Id ben_Enrt_Rt.Enrt_Rt_Id%TYPE;
3917 l_Enrt_ovn ben_Enrt_Rt.Object_Version_Number%TYPE;
3918
3919 Cursor Enrt_Rt is
3920 Select Enrt_Rt_Id,
3921 Object_Version_Number
3922 From Ben_Enrt_Rt
3923 Where Elig_per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
3924
3925 Begin
3926 if g_debug then
3927 hr_utility.set_location(' Entering call_from_webadi',10);
3928 hr_utility.set_location(' P_Elig_Per_Elctbl_Chc_id ' || P_Elig_Per_Elctbl_Chc_id, 20);
3929 hr_utility.set_location(' P_PROGRESSION_DATE ' || P_PROGRESSION_DATE,30);
3930 hr_utility.set_location(' P_Sal_Chg_Dt ' || P_Sal_Chg_Dt, 40);
3931 hr_utility.set_location(' p_assignment_id ' || p_assignment_id, 50);
3932 hr_utility.set_location(' p_proposed_rank ' || p_proposed_rank, 60);
3933 hr_utility.set_location(' p_life_event_dt ' || p_life_event_dt,70);
3934 hr_utility.set_location(' p_grade_ladder_id ' || p_grade_ladder_id, 80);
3935 hr_utility.set_location(' p_pl_id ' || p_pl_id, 90);
3936 hr_utility.set_location(' p_oipl_id ' || p_oipl_id, 100);
3937 End If;
3938
3939 if p_progression_date < p_life_event_dt then
3940 fnd_message.set_name('PQH','PQH_GSP_APPRVL_PRGS_DT_CHK_ERR');
3941 fnd_message.raise_error();
3942 end if;
3943 if p_Sal_Chg_Dt < p_progression_date then
3944 fnd_message.set_name('PQH','PQH_GSP_APPRVL_SALCHG_DT_ERR');
3945 fnd_message.raise_error();
3946 end if;
3947 Open Enrt_Rt;
3948 Fetch Enrt_Rt into L_Enrt_Rt_Id, l_Enrt_ovn;
3949 Close Enrt_Rt;
3950
3951 pqh_rank_utility.update_proposed_rank
3952 (p_proposed_rank => p_proposed_rank
3953 ,p_assignment_id => p_assignment_id
3954 ,p_life_event_dt => p_life_event_dt
3955 ,p_pgm_id => p_grade_ladder_id
3956 ,p_pl_id => p_pl_id
3957 );
3958
3959 Update Ben_Elig_per_elctbl_Chc
3960 Set Enrt_Cvg_Strt_Dt = P_PROGRESSION_DATE
3961 Where Elig_Per_Elctbl_Chc_Id = P_Elig_Per_Elctbl_Chc_id;
3962
3963 If L_Enrt_Rt_Id is Not NULL and P_Sal_Chg_Dt is Not Null then
3964 Update Ben_Enrt_Rt
3965 Set Rt_Strt_Dt = P_Sal_Chg_Dt
3966 Where Enrt_Rt_Id = L_Enrt_Rt_Id;
3967 End If;
3968 hr_utility.set_location(' Leaving call_from_webadi',10);
3969 commit;
3970 exception when others then
3971 rollback;
3972 raise;
3973 End call_from_webadi;
3974 --
3975 END Pqh_Gsp_Post_Process;