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