[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_WS_IMPORT_PKG
Source
1 Package Body BEN_CWB_WS_IMPORT_PKG as
2 /* $Header: bencwbwsim.pkb 120.23.12020000.3 2013/03/01 11:16:00 sgnanama ship $ */
3
4 g_package Varchar2(30) := 'BEN_CWB_WS_IMPORT_PKG.';
5 g_debug boolean := hr_utility.debug_enabled;
6 TYPE g_iterface_seq_type is varray(6) of number;
7
8 /*
9 This procedure is used by worksheet when rank is updated to
10 prevent any duplicates getting created.
11 */
12 procedure insert_new_rank
13 (p_assignment_id in number
14 ,p_rank in number
15 ,p_rank_by_person_id in number
16 ,p_level_number in number
17 ,p_assignment_extra_info_id out nocopy number
18 ,p_object_version_number out nocopy number) is
19 Cursor Csr_EIT_Dtls IS
20 SELECT 'Y'
21 FROM PER_ASSIGNMENT_EXTRA_INFO ASS_EIT
22 WHERE ASS_EIT.INFORMATION_TYPE = 'CWBRANK'
23 AND ASS_EIT.ASSIGNMENT_ID = p_assignment_Id
24 AND ASS_EIT.AEI_INFORMATION2 = p_rank_by_person_id
25 AND ASS_EIT.AEI_INFORMATION5 IS NULL
26 AND ASS_EIT.AEI_INFORMATION6 IS NULL;
27 l_exists char(1) := 'N';
28 begin
29 --
30 open Csr_EIT_Dtls;
31 fetch Csr_EIT_Dtls into l_exists;
32 close Csr_EIT_Dtls;
33
34 if l_exists = 'N' then
35 hr_assignment_extra_info_api.create_assignment_extra_info
36 (p_assignment_id => p_assignment_Id
37 ,p_information_type => 'CWBRANK'
38 ,p_aei_information_category => 'CWBRANK'
39 ,p_aei_information1 => p_rank
40 ,p_aei_information2 => p_rank_by_person_id
41 ,p_aei_information4 => p_level_number
42 ,p_assignment_extra_info_id => p_assignment_extra_info_id
43 ,p_object_version_number => p_object_version_number);
44 else
45 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
46 hr_utility.raise_error;
47 end if;
48
49 end insert_new_rank;
50 --
51 --------------------------- UPDATE_RANK -----------------------------
52 --
53
54 PROCEDURE UPDATE_RANK(P_GROUP_PER_IN_LER_ID IN NUMBER
55 ,P_RANK IN NUMBER
56 ,P_USER_ID IN VARCHAR2) IS
57
58 Cursor Csr_Assignment_ID IS
59 Select pil_emp.assignment_id
60 ,mgr.lvl_num
61 From ben_per_in_ler pil_emp
62 ,ben_cwb_group_hrchy mgr
63 ,ben_per_in_ler pil_mgr
64 Where pil_emp.per_in_ler_id = p_group_per_in_ler_id
65 and pil_emp.per_in_ler_id = mgr.emp_per_in_ler_id
66 and mgr.lvl_num > 0
67 and mgr.mgr_per_in_ler_id = pil_mgr.per_in_ler_id
68 and pil_mgr.person_id = p_user_id;
69
70 Cursor Csr_EIT_Dtls(l_assignment_Id IN NUMBER )
71 IS
72 SELECT ASS_EIT.ASSIGNMENT_EXTRA_INFO_ID ,
73 ASS_EIT.OBJECT_VERSION_NUMBER
74 FROM PER_ASSIGNMENT_EXTRA_INFO ASS_EIT
75 WHERE ASS_EIT.INFORMATION_TYPE = 'CWBRANK'
76 AND ASS_EIT.ASSIGNMENT_ID = l_assignment_Id
77 AND ASS_EIT.AEI_INFORMATION2 = P_USER_ID
78 AND ASS_EIT.AEI_INFORMATION5 IS NULL
79 AND ASS_EIT.AEI_INFORMATION6 IS NULL;
80
81
82
83 l_proc varchar2(72) := g_package||'UPDATE_RANK';
84 l_assignment_Id Number;
85 l_lvl_num number;
86 l_ovn Number;
87 l_assignment_extra_info_id Number;
88
89 BEGIN
90
91 if g_debug then
92 hr_utility.set_location('Entering '||l_proc,10);
93 hr_utility.set_location('P_GROUP_PER_IN_LER_ID '||P_GROUP_PER_IN_LER_ID,20);
94 hr_utility.set_location('P_RANK '||P_RANK,30);
95 hr_utility.set_location('P_USER_ID '||P_USER_ID,35);
96 end if;
97
98 Open Csr_Assignment_ID;
99 Fetch Csr_Assignment_ID into l_assignment_Id, l_lvl_num;
100 Close Csr_Assignment_ID;
101
102 if g_debug then
103 hr_utility.set_location('l_assignment_Id '||l_assignment_Id,70);
104 end if;
105
106
107 IF (l_assignment_Id IS NOT NULL) THEN
108 --
109 open Csr_EIT_Dtls(l_assignment_Id);
110 fetch Csr_EIT_Dtls into l_assignment_extra_info_id, l_ovn;
111 close Csr_EIT_Dtls;
112
113 if l_assignment_extra_info_id is not null then
114 hr_assignment_extra_info_api.update_assignment_extra_info
115 (p_assignment_extra_info_id => l_assignment_extra_info_id
116 ,p_object_version_number => l_ovn
117 ,p_aei_information_category => 'CWBRANK'
118 ,p_aei_information1 => P_RANK
119 ,p_aei_information4 => l_lvl_num);
120 else
121 hr_assignment_extra_info_api.create_assignment_extra_info
122 (p_assignment_id => l_assignment_Id
123 ,p_information_type => 'CWBRANK'
124 ,p_aei_information_category => 'CWBRANK'
125 ,p_aei_information1 => P_RANK
126 ,p_aei_information2 => P_USER_ID
127 ,p_aei_information4 => l_lvl_num
128 ,p_assignment_extra_info_id => l_assignment_extra_info_id
129 ,p_object_version_number => l_ovn);
130 end if;
131
132 END IF;
133
134 if g_debug then
135 hr_utility.set_location('Leaving '||l_proc,100);
136 end if;
137
138 END UPDATE_RANK;
139
140
141 --
142 --------------------------- UPDATE_WS_AMOUNT -----------------------------
143 --
144
145 function UPDATE_WS_AMOUNT (P_PERSON_RATE_ID IN NUMBER
146 ,P_WS_VAL IN NUMBER default null
147 ,p_add_val in number default null
148 ,P_USER_ID IN VARCHAR2
149 ,P_WS_RT_START_DATE IN DATE DEFAULT NULL)
150 return number
151 IS
152
153 Cursor Csr_PlRt_Dtls
154 IS
155 Select PlRt.GROUP_PER_IN_LER_ID GROUP_PER_IN_LER_ID,
156 PlRt.PL_ID PL_ID,
157 PlRt.OIPL_ID OIPL_ID,
158 PlRt.GROUP_PL_ID GROUP_PL_ID,
159 PlRt.GROUP_OIPL_ID GROUP_OIPL_ID,
160 PlRt.LF_EVT_OCRD_DT LF_EVT_OCRD_DT,
161 PlRt.Object_Version_Number OVN,
162 PlRt.ws_val ws_val,
163 plrt.ws_rt_start_date WS_RT_START_DATE
164 From BEN_CWB_PERSON_RATES PlRt
165 ,ben_cwb_pl_dsgn dsgn
166 Where PlRt.PERSON_RATE_ID = P_PERSON_RATE_ID
167 And PlRt.ELIG_FLAG='Y'
168 and plRt.pl_id = dsgn.pl_id
169 and plRt.oipl_id = dsgn.oipl_id
170 and plRt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
171 and dsgn.ws_abr_id is not null;
172
173 Cursor Csr_Person_Id(l_USER_ID IN NUMBER)
174 IS
175 Select EMPLOYEE_ID
176 From FND_USER
177 Where User_Id = l_USER_ID;
178
179 l_upd_rec Csr_PlRt_Dtls%RowType;
180 l_ovn Number;
181 l_proc varchar2(72) := g_package||'UPDATE_WS_AMOUNT';
182 l_USER_ID Number;
183 l_person_id Number;
184 l_diff number;
185 l_ws_val number := p_ws_val;
186 l_ws_rt_start_date DATE;
187 BEGIN
188
189 if g_debug then
190 hr_utility.set_location('Entering '||l_proc,10);
191 hr_utility.set_location('P_PERSON_RATE_ID '||P_PERSON_RATE_ID,20);
192 hr_utility.set_location('P_WS_VAL '||l_WS_VAL,30);
193 hr_utility.set_location('P_USER_ID '||P_USER_ID,36);
194 end if;
195
196 IF (P_USER_ID IS NOT NULL) THEN
197 l_USER_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_USER_ID);
198 hr_utility.set_location('l_USER_ID :'||l_USER_ID,45);
199 END IF;
200
201 Open Csr_Person_Id(l_USER_ID);
202 Fetch Csr_Person_Id into l_person_id;
203 Close Csr_Person_Id;
204
205 Open Csr_PlRt_Dtls;
206 Fetch Csr_PlRt_Dtls into l_upd_rec;
207
208 If Csr_PlRt_Dtls%Found Then
209 l_ovn := l_upd_rec.OVN;
210
211 if p_add_val is not null then
212 l_ws_val := ben_cwb_utils.add_number_with_null_check
213 (l_upd_rec.ws_val, p_add_val);
214 end if;
215 l_diff := nvl(l_ws_val,0) - nvl(l_upd_rec.ws_val,0);
216 --added by KMG
217 IF p_ws_rt_start_date = default_date THEN
218 l_ws_rt_start_date := l_upd_rec.ws_rt_start_date;
219 ELSE
220 l_ws_rt_start_date := p_ws_rt_start_date;
221 END IF;
222 BEN_CWB_PERSON_RATES_API.update_person_rate
223 ( p_group_per_in_ler_id => l_upd_rec.GROUP_PER_IN_LER_ID
224 ,p_pl_id => l_upd_rec.PL_ID
225 ,p_oipl_id => l_upd_rec.OIPL_ID
226 ,p_group_pl_id => l_upd_rec.GROUP_PL_ID
227 ,p_group_oipl_id => l_upd_rec.GROUP_OIPL_ID
228 ,p_lf_evt_ocrd_dt => l_upd_rec.LF_EVT_OCRD_DT
229 ,p_ws_val_last_upd_date => trunc(Sysdate)
230 ,p_ws_val_last_upd_by => l_person_id
231 ,p_ws_val => l_WS_VAL
232 ,p_object_version_number => l_ovn
233 ,p_ws_rt_start_date => l_ws_rt_start_date
234 );
235 End if;
236 Close Csr_PlRt_Dtls;
237
238 if g_debug then
239 hr_utility.set_location('Leaving '||l_proc,100);
240 end if;
241
242 return l_diff;
243
244 END UPDATE_WS_AMOUNT;
245
246 --
247 --------------------------- REFRESH_PERSON_TASKS -----------------------------
248 --
249
250 PROCEDURE REFRESH_PERSON_TASKS (P_PERSON_RATE_ID IN NUMBER Default Null
251 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
252 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
253 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
254 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null
255 ,P_TASK_ID IN NUMBER
256 ,P_SEC_MGR_LER_ID IN NUMBER Default Null)
257 IS
258 Cursor csr_person_rate_info (l_person_rate_id IN Number)
259 IS
260 Select group_pl_id
261 ,lf_evt_ocrd_dt
262 from ben_cwb_person_rates
263 where person_rate_id = l_person_rate_id;
264
265 Cursor csr_person_tasks_info(l_group_pl_id In Number, l_lf_evt_ocrd_dt Date)
266 IS
267 Select STATUS_CD
268 ,OBJECT_VERSION_NUMBER
269 From ben_cwb_person_tasks
270 Where GROUP_PER_IN_LER_ID = P_SEC_MGR_LER_ID
271 And TASK_ID = P_TASK_ID
272 And GROUP_PL_ID = l_group_pl_id
273 And LF_EVT_OCRD_DT = l_lf_evt_ocrd_dt ;
274
275 l_proc varchar2(72) := g_package||'REFRESH_PERSON_TASKS';
276 l_rate_id Number;
277 l_group_pl_id Number;
278 l_status_cd ben_cwb_person_tasks.STATUS_CD%Type;
279 l_ovn Number;
280 l_lf_evt_ocrd_dt Date;
281 BEGIN
282
283 hr_utility.set_location('Entering :'||l_proc,10);
284
285 If P_PERSON_RATE_ID IS NOT NULL then
286 l_rate_id := P_PERSON_RATE_ID;
287 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
288 l_rate_id := P_OPT1_PERSON_RATE_ID;
289 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
290 l_rate_id := P_OPT2_PERSON_RATE_ID;
291 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
292 l_rate_id := P_OPT3_PERSON_RATE_ID;
293 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
294 l_rate_id := P_OPT4_PERSON_RATE_ID;
295 End if;
296
297 hr_utility.set_location('l_rate_id :'||l_rate_id,20);
298
299 Open csr_person_rate_info(l_rate_id);
300 Fetch csr_person_rate_info into l_group_pl_id,l_lf_evt_ocrd_dt;
301 Close csr_person_rate_info;
302
303 hr_utility.set_location('l_group_pl_id :'||l_group_pl_id,50);
304 hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt,60);
305
306
307 Open csr_person_tasks_info(l_group_pl_id,l_lf_evt_ocrd_dt );
308 Fetch csr_person_tasks_info into l_status_cd,l_ovn;
309 Close csr_person_tasks_info;
310
311 hr_utility.set_location('l_status_cd :'||l_status_cd,70);
312 hr_utility.set_location('l_ovn :'||l_ovn,80);
313
314 If l_status_cd = 'NS' then
315 BEN_CWB_PERSON_TASKS_API.update_person_task
316 ( p_group_per_in_ler_id => P_SEC_MGR_LER_ID
317 ,p_task_id => P_TASK_ID
318 ,p_group_pl_id => l_group_pl_id
319 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
320 ,p_status_cd => 'IP'
321 ,p_task_last_update_date => sysdate
322 ,p_object_version_number => l_ovn
323 );
324 End If;
325
326 hr_utility.set_location('Leaving :'||l_proc,100);
327
328 END REFRESH_PERSON_TASKS;
329
330 --
331 --------------------------- get_group_per_in_ler_id -----------------------------
332 --
333
334 FUNCTION get_group_per_in_ler_id (P_PERSON_RATE_ID IN NUMBER Default Null
335 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
336 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
337 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
338 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
339 Return Number
340 IS
341 Cursor csr_group_per_in_ler_id (l_person_rate_id IN Number)
342 IS
343 Select group_per_in_ler_id
344 from ben_cwb_person_rates
345 where person_rate_id = l_person_rate_id;
346
347 l_proc Varchar2(72) := g_package||'get_group_per_in_ler_id';
348 l_rate_id Number;
349 l_group_per_in_ler_id Number;
350
351
352 BEGIN
353
354 hr_utility.set_location('Entering :'||l_proc,10);
355
356 If P_PERSON_RATE_ID IS NOT NULL then
357 l_rate_id := P_PERSON_RATE_ID;
358 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
359 l_rate_id := P_OPT1_PERSON_RATE_ID;
360 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
361 l_rate_id := P_OPT2_PERSON_RATE_ID;
362 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
363 l_rate_id := P_OPT3_PERSON_RATE_ID;
364 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
365 l_rate_id := P_OPT4_PERSON_RATE_ID;
366 End if;
367
368 hr_utility.set_location('l_rate_id :'||l_rate_id,20);
369
370 Open csr_group_per_in_ler_id(l_rate_id);
371 Fetch csr_group_per_in_ler_id into l_group_per_in_ler_id;
372 Close csr_group_per_in_ler_id;
373
374 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
375 hr_utility.set_location('Leaving :'||l_proc,100);
376
377 return l_group_per_in_ler_id;
378
379 End get_group_per_in_ler_id;
380
381 --
382 --------------------------- chk_processed_emp -----------------------------
383 --
384
385 PROCEDURE chk_processed_emp (P_PERSON_RATE_ID IN NUMBER Default Null
386 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
387 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
388 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
389 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null
390 ,P_EMP_PER_IN_LER_ID IN NUMBER DEFAULT NULL
391 ,P_MGR_PER_IN_LER_ID IN NUMBER DEFAULT NULL)
392 IS
393 l_proc Varchar2(72) := g_package||'chk_processed_emp';
394 l_group_per_in_ler_id Number;
395 l_pp_stat_cd Ben_Cwb_Person_Info.POST_PROCESS_STAT_CD%Type;
396
397 Cursor csr_PP_STAT_CD (l_ler_id IN Number)
398 IS
399 Select POST_PROCESS_STAT_CD
400 From Ben_Cwb_Person_Info
401 Where Group_Per_In_Ler_Id = l_ler_id;
402
403 -- added by kmg for fixing bug#6830922
404 CURSOR csr_ws_stat_cd (p_mgr_ler_id IN NUMBER) IS
405 /* Changed cursor for bug#9692823
406 SELECT submit_cd
407 FROM ben_cwb_person_groups mgr,
408 ben_cwb_group_hrchy hrchy
409 WHERE hrchy.emp_per_in_ler_id = l_ler_id
410 and hrchy.mgr_per_in_ler_id = mgr.group_per_in_ler_id
411 and hrchy.lvl_num = 1*/
412 SELECT 'SU'
413 FROM ben_cwb_group_hrchy h
414 WHERE emp_per_in_ler_id = p_mgr_ler_id
415 AND EXISTS (SELECT 'x'
416 FROM ben_cwb_person_groups
417 WHERE group_per_in_ler_id = h.mgr_per_in_ler_id
418 AND SUBMIT_CD = 'SU')
419 ;
420 l_submit_cd ben_cwb_person_groups.submit_cd%TYPE;
421
422 CURSOR csr_chk_hrchy(p_mgr_ler_id NUMBER,p_emp_ler_id NUMBER) IS -- bug: 8996634
423 SELECT 'Y'
424 FROM DUAL
425 WHERE EXISTS (SELECT 'X'
426 FROM BEN_CWB_GROUP_HRCHY
427 WHERE MGR_PER_IN_LER_ID = p_mgr_ler_id
428 AND EMP_PER_IN_LER_ID = p_emp_ler_id );
429 l_chk_hrchy VARCHAR2(10);
430
431 --bug 14548191
432 CURSOR c_task_access IS
433 select ben_cwb_utils.get_task_access (bcwg.hidden_cd,
434 bcpt.access_cd,
435 bcpg.access_cd,
436 bcwg.wksht_grp_cd,
437 bcpg.bdgt_pop_cd,
438 bcpt.status_cd,
439 bcpg.dist_bdgt_iss_date,
440 NVL (bcpd.upd_start_dt,
441 bcpd.enrt_perd_start_dt
442 ),
443 NVL (bcpd.upd_end_dt,
444 bcpd.enrt_perd_end_dt
445 ),
446 sysdate
447 ) accesscd
448 from ben_cwb_person_info bcpi,
449 ben_cwb_person_groups bcpg,
450 ben_cwb_pl_dsgn bcpd,
451 ben_cwb_person_tasks bcpt,
452 ben_cwb_wksht_grp bcwg
453 WHERE bcpi.group_per_in_ler_id = P_MGR_PER_IN_LER_ID
454 AND bcpg.group_per_in_ler_id = bcpi.group_per_in_ler_id
455 AND bcpg.group_pl_id = bcpi.group_pl_id
456 AND bcpg.group_oipl_id = -1
457 AND bcpd.pl_id = bcpi.group_pl_id
458 AND bcpd.lf_evt_ocrd_dt = bcpi.lf_evt_ocrd_dt
459 AND bcpd.oipl_id = -1
460 AND bcpt.group_per_in_ler_id = bcpi.group_per_in_ler_id
461 AND bcwg.cwb_wksht_grp_id = bcpt.task_id
462 AND bcwg.wksht_grp_cd = 'COMP';
463
464 BEGIN
465 hr_utility.set_location('Entering :'||l_proc,10);
466
467 l_group_per_in_ler_id := get_group_per_in_ler_id (P_PERSON_RATE_ID =>P_PERSON_RATE_ID
468 ,P_OPT1_PERSON_RATE_ID => P_OPT1_PERSON_RATE_ID
469 ,P_OPT2_PERSON_RATE_ID => P_OPT2_PERSON_RATE_ID
470 ,P_OPT3_PERSON_RATE_ID => P_OPT3_PERSON_RATE_ID
471 ,P_OPT4_PERSON_RATE_ID => P_OPT4_PERSON_RATE_ID);
472
473 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
474
475 Open csr_PP_STAT_CD(l_group_per_in_ler_id);
476 Fetch csr_PP_STAT_CD into l_pp_stat_cd;
477 Close csr_PP_STAT_CD;
478
479 hr_utility.set_location('l_pp_stat_cd :'||l_pp_stat_cd,50);
480
481 if l_pp_stat_cd IS NOT NULL then
482 hr_utility.set_message(805,'BEN_93752_CWB_PROCESSES_EMP');
483 hr_utility.raise_error;
484 End if;
485
486 -- added by kmg for fixing bug#6830922
487 OPEN csr_ws_stat_cd(p_mgr_per_in_ler_id);
488 FETCH csr_ws_stat_cd INTO l_submit_cd;
489 CLOSE csr_ws_stat_cd;
490 IF NVL(l_submit_cd,'NS') = 'SU' THEN
491 hr_utility.set_message(805,'BEN_94711_CWB_WS_SUBMITTED');
492 hr_utility.raise_error;
493 END IF;
494
495 FOR l_task_access IN c_task_access LOOP
496 IF nvl(l_task_access.accesscd,'UP') <> 'UP' THEN
497 hr_utility.set_message(805,'BEN_92788_TASK_INACCESSIBLE');
498 hr_utility.raise_error;
499 END IF;
500 END LOOP;
501
502 hr_utility.trace('P_EMP_PER_IN_LER_ID:'||p_emp_per_in_ler_id); -- bug: 8996634
503 hr_utility.trace('P_MGR_PER_IN_LER_ID:'||p_mgr_per_in_ler_id);
504 IF p_emp_per_in_ler_id IS NOT NULL AND p_mgr_per_in_ler_id IS NOT NULL THEN
505 OPEN csr_chk_hrchy(p_mgr_per_in_ler_id,p_emp_per_in_ler_id);
506 FETCH csr_chk_hrchy INTO l_chk_hrchy;
507 hr_utility.trace('Inside the loop:'||l_CHK_HRCHY);
508 CLOSE csr_chk_hrchy;
509 IF NVL(l_chk_hrchy,'N') = 'N' THEN
510 --- Create a new error message for this case
511 hr_utility.trace('KMG_CHANGES: NOT IN HIERARCHY, RAISE ERROR');
512 hr_utility.set_message(805,'BEN_94723_CWB_EMP_NOT_HRCHY');
513 hr_utility.raise_error;
514 END IF;
515 END IF;
516
517 hr_utility.set_location('Leaving :'||l_proc,200);
518 END chk_processed_emp;
519
520
521
522 --
523 --------------------------- update_perf_rating -----------------------------
524 --
525 Procedure update_perf_rating(P_PROPOSED_PERFORMANCE_RATING IN Varchar2
526 ,P_ACTING_PERSON_ID IN NUMBER Default Null
527 ,P_PERSON_RATE_ID IN NUMBER Default Null
528 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
529 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
530 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
531 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
532 IS
533
534 l_proc Varchar2(72) := g_package||'update_perf_rating';
535 l_group_per_in_ler_id Number;
536 l_rating Varchar2(30);
537
538 Cursor Csr_person_info(l_group_per_in_ler_id In Number)
539 IS
540 Select per.assignment_id Assignment_id
541 ,per.person_id Person_id
542 ,per.business_group_id business_group_id
543 ,per.full_name Person_name
544 From ben_cwb_person_info per
545 Where per.group_per_in_ler_id = l_group_per_in_ler_id;
546
547 cursor Csr_person_perf(l_assignment_id In Number, l_perf_revw_strt_dt In Varchar2, l_emp_interview_typ_cd In Varchar2)
548 is
549 select txn.attribute3 rating
550 from ben_transaction txn
551 where txn.transaction_id = l_assignment_id
552 and txn.transaction_type = 'CWBPERF'|| trim(l_perf_revw_strt_dt) || trim(l_emp_interview_typ_cd);
553
554 Cursor Csr_pl_dsgn_info(l_group_per_in_ler_id In Number)
555 IS
556 Select dsgn.emp_interview_typ_cd
557 ,to_char(dsgn.perf_revw_strt_dt,'yyyy/mm/dd') perf_revw_strt_dt
558 ,dsgn.group_pl_id
559 From ben_cwb_pl_dsgn dsgn
560 ,ben_cwb_person_info inf
561 Where dsgn.group_oipl_id = -1
562 And dsgn.pl_id = inf.group_pl_id
563 And dsgn.lf_evt_ocrd_dt = inf.lf_evt_ocrd_dt
564 And inf.group_per_in_ler_id = l_group_per_in_ler_id;
565
566 CURSOR c_assgn_perf_rating_prcd(
567 v_group_per_in_ler_id IN NUMBER) is
568 select new_assgn_ovn, new_perf_event_id, new_perf_review_id
569 from ben_cwb_person_info
570 where group_per_in_ler_id = v_group_per_in_ler_id;
571
572 l_person_info Csr_person_info%RowType;
573 l_pl_dsgn_info Csr_pl_dsgn_info%RowType;
574 l_assgn_perf_rating_prcd c_assgn_perf_rating_prcd%ROWTYPE;
575 l_person_perf Csr_person_perf%ROWTYPE;
576 l_changed varchar2(2) := 'N';
577
578 Begin
579 hr_utility.set_location('Entering :'||l_proc,10);
580 --If P_PROPOSED_PERFORMANCE_RATING IS NOT NULL Then
581 l_group_per_in_ler_id := get_group_per_in_ler_id (P_PERSON_RATE_ID =>P_PERSON_RATE_ID
582 ,P_OPT1_PERSON_RATE_ID => P_OPT1_PERSON_RATE_ID
583 ,P_OPT2_PERSON_RATE_ID => P_OPT2_PERSON_RATE_ID
584 ,P_OPT3_PERSON_RATE_ID => P_OPT3_PERSON_RATE_ID
585 ,P_OPT4_PERSON_RATE_ID => P_OPT4_PERSON_RATE_ID);
586
587 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
588 open c_assgn_perf_rating_prcd(l_group_per_in_ler_id);
589 fetch c_assgn_perf_rating_prcd into l_assgn_perf_rating_prcd;
590 close c_assgn_perf_rating_prcd;
591
592 Open csr_person_info(l_group_per_in_ler_id);
593 Fetch csr_person_info into l_person_info;
594 Close csr_person_info;
595
596 hr_utility.set_location('l_person_info.Assignment_id :'||l_person_info.Assignment_id,70);
597 hr_utility.set_location('l_person_info.Person_id :'||l_person_info.Person_id,80);
598 hr_utility.set_location('l_person_info.business_group_id :'||l_person_info.business_group_id,90);
599 hr_utility.set_location('l_person_info.Person_name :'||l_person_info.Person_name,100);
600
601 Open Csr_pl_dsgn_info(l_group_per_in_ler_id);
602 Fetch Csr_pl_dsgn_info into l_pl_dsgn_info;
603 Close Csr_pl_dsgn_info;
604
605 Open Csr_person_perf(l_person_info.Assignment_id, l_pl_dsgn_info.perf_revw_strt_dt, l_pl_dsgn_info.emp_interview_typ_cd);
606 Fetch Csr_person_perf into l_person_perf;
607 Close Csr_person_perf;
608
609 hr_utility.set_location('l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD :'||l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD,120);
610 hr_utility.set_location('l_pl_dsgn_info.perf_revw_strt_dt :'||l_pl_dsgn_info.perf_revw_strt_dt,130);
611 hr_utility.set_location('P_PROPOSED_PERFORMANCE_RATING :'||P_PROPOSED_PERFORMANCE_RATING,130);
612 hr_utility.set_location('l_person_perf.rating :'||l_person_perf.rating,130);
613
614 --12560928: convert null to -1 before comparing
615 IF(nvl(P_PROPOSED_PERFORMANCE_RATING,-1) <> nvl(l_person_perf.rating,-1)) THEN
616 l_changed := 'Y';
617 ELSE
618 l_changed := 'N';
619 END IF;
620
621 IF(l_assgn_perf_rating_prcd.new_perf_event_id IS NULL
622 AND l_assgn_perf_rating_prcd.new_perf_review_id IS NULL AND l_changed = 'Y') THEN
623
624 hr_utility.set_location('calling process_rating ',140);
625 ben_cwb_asg_update.process_rating
626 (p_validate_data => 'Y'
627 ,p_assignment_id => l_person_info.Assignment_id
628 ,p_person_id => l_person_info.Person_id
629 ,p_business_group_id => l_person_info.business_group_id
630 ,p_perf_revw_strt_dt => l_pl_dsgn_info.perf_revw_strt_dt --to_char(l_pl_dsgn_info.perf_revw_strt_dt,'yyyy/mm/dd')
631 ,p_perf_type => l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD
632 ,p_perf_rating => P_PROPOSED_PERFORMANCE_RATING
633 ,p_person_name => l_person_info.Person_name
634 ,p_update_person_id => P_ACTING_PERSON_ID
635 ,p_update_date => Sysdate
636 ,p_group_pl_id => l_pl_dsgn_info.group_pl_id);
637 ELSE
638 hr_utility.set_location('Already processed, so skip calling process_rating ',140);
639 IF(l_changed = 'Y') THEN
640 hr_utility.set_message(805,'BEN_94740_CWB_RATING_PRCSD');
641 hr_utility.raise_error;
642 END IF;
643 END IF;
644
645 --End If;
646 hr_utility.set_location('Leaving :'||l_proc,100);
647 End update_perf_rating;
648
649
650 Procedure update_promotions(P_PROPOSED_JOB IN Varchar2
651 ,P_PROPOSED_POSITION IN Varchar2
652 ,P_PROPOSED_GRADE IN Varchar2
653 ,P_CHANGE_REASON IN Varchar2
654 ,P_ACTING_PERSON_ID IN NUMBER Default Null
655 ,P_PERSON_RATE_ID IN NUMBER Default Null
656 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
657 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
658 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
659 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
660 IS
661
662 l_proc Varchar2(72) := g_package||'update_promotions';
663 l_group_per_in_ler_id Number;
664 l_job_id Varchar2(30) := null;
665 l_pos_id Varchar2(30) := null;
666 l_grade_id Varchar2(30) := null;
667 l_change_reason Varchar2(50) := P_CHANGE_REASON;
668
669 Cursor Csr_person_info(l_group_per_in_ler_id In Number)
670 IS
671 Select per.assignment_id Assignment_id
672 ,per.person_id Person_id
673 ,per.business_group_id business_group_id
674 ,per.full_name Person_name
675 From ben_cwb_person_info per
676 Where per.group_per_in_ler_id = l_group_per_in_ler_id;
677
678 Cursor Csr_pl_dsgn_info(l_group_per_in_ler_id In Number)
679 IS
680 Select dsgn.EMP_INTERVIEW_TYP_CD
681 ,to_char(dsgn.asg_updt_eff_date,'yyyy/mm/dd') asg_updt_eff_date
682 ,dsgn.group_pl_id
683 From ben_cwb_pl_dsgn dsgn
684 ,ben_cwb_person_info inf
685 Where dsgn.group_oipl_id = -1
686 And dsgn.pl_id = inf.group_pl_id
687 And dsgn.lf_evt_ocrd_dt = inf.lf_evt_ocrd_dt
688 And inf.group_per_in_ler_id = l_group_per_in_ler_id;
689
690 cursor Csr_person_asg(l_assignment_id In Number, l_asg_updt_eff_date In Varchar2)
691 is
692 select txn.attribute3 change_reason
693 ,to_number(txn.attribute5) job_id
694 ,to_number(txn.attribute6) position_id
695 ,to_number(txn.attribute7) grade_id
696 from ben_transaction txn
697 where txn.transaction_id = l_assignment_id
698 and txn.transaction_type = 'CWBASG'|| trim(l_asg_updt_eff_date);
699
700 cursor c_job (bg_id In Number) is
701 select j.job_id
702 from per_jobs_tl jtl , per_jobs j
703 where jtl.name = P_PROPOSED_JOB
704 and jtl.language = 'US'
705 and jtl.job_id = j.job_id
706 and j.business_group_id = bg_id
707 and rownum =1;
708
709 cursor c_position (bg_id In Number) is
710 select p.position_id
711 from hr_all_positions_f_tl ptl, hr_all_positions_f p
712 where ptl.name = P_PROPOSED_POSITION
713 and ptl.language = 'US'
714 and ptl.position_id = p.position_id
715 and p.business_group_id = bg_id
716 and rownum =1;
717
718 cursor c_grade (bg_id In Number) is
719 select p.grade_id
720 from per_grades_tl ptl, per_grades p
721 where ptl.name = P_PROPOSED_GRADE
722 and ptl.language = 'US'
723 and ptl.grade_id = p.grade_id
724 and p.business_group_id = bg_id
725 and rownum =1;
726
727 CURSOR c_assgn_perf_rating_prcd(
728 v_group_per_in_ler_id IN NUMBER) is
729 select new_assgn_ovn, new_perf_event_id, new_perf_review_id
730 from ben_cwb_person_info
731 where group_per_in_ler_id = v_group_per_in_ler_id;
732
733 l_person_info Csr_person_info%RowType;
734 l_pl_dsgn_info Csr_pl_dsgn_info%RowType;
735 l_person_asg Csr_person_asg%RowType;
736 l_assgn_perf_rating_prcd c_assgn_perf_rating_prcd%ROWTYPE;
737 l_changed varchar2(2) := 'N';
738
739 Begin
740 hr_utility.set_location('Entering :'||l_proc,10);
741 l_group_per_in_ler_id := get_group_per_in_ler_id (P_PERSON_RATE_ID =>P_PERSON_RATE_ID
742 ,P_OPT1_PERSON_RATE_ID => P_OPT1_PERSON_RATE_ID
743 ,P_OPT2_PERSON_RATE_ID => P_OPT2_PERSON_RATE_ID
744 ,P_OPT3_PERSON_RATE_ID => P_OPT3_PERSON_RATE_ID
745 ,P_OPT4_PERSON_RATE_ID => P_OPT4_PERSON_RATE_ID);
746
747 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
748
749 open c_assgn_perf_rating_prcd(l_group_per_in_ler_id);
750 fetch c_assgn_perf_rating_prcd into l_assgn_perf_rating_prcd;
751 close c_assgn_perf_rating_prcd;
752
753 Open csr_person_info(l_group_per_in_ler_id);
754 Fetch csr_person_info into l_person_info;
755 Close csr_person_info;
756
757 hr_utility.set_location('l_person_info.Assignment_id :'||l_person_info.Assignment_id,70);
758 hr_utility.set_location('l_person_info.Person_id :'||l_person_info.Person_id,80);
759 hr_utility.set_location('l_person_info.business_group_id :'||l_person_info.business_group_id,90);
760 hr_utility.set_location('l_person_info.Person_name :'||l_person_info.Person_name,100);
761
762 Open Csr_pl_dsgn_info(l_group_per_in_ler_id);
763 Fetch Csr_pl_dsgn_info into l_pl_dsgn_info;
764 Close Csr_pl_dsgn_info;
765
766 Open Csr_person_asg(l_person_info.Assignment_id, l_pl_dsgn_info.asg_updt_eff_date);
767 Fetch Csr_person_asg into l_person_asg;
768 Close Csr_person_asg;
769
770 hr_utility.set_location('l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD :'||l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD,120);
771 hr_utility.set_location('l_pl_dsgn_info.ASG_UPDT_EFF_DATE :'||l_pl_dsgn_info.ASG_UPDT_EFF_DATE,130);
772
773 if(P_PROPOSED_JOB = default_string) then
774 l_job_id := l_person_asg.job_id;
775 else
776 open c_job (l_person_info.business_group_id);
777 fetch c_job into l_job_id;
778 close c_job;
779 end if;
780 hr_utility.set_location('l_job_id :'|| l_job_id,130);
781 if(P_PROPOSED_POSITION = default_string) then
782 l_pos_id := l_person_asg.position_id;
783 else
784 open c_position (l_person_info.business_group_id);
785 fetch c_position into l_pos_id;
786 close c_position;
787 end if;
788 hr_utility.set_location('l_pos_id :'|| l_pos_id,130);
789 if(P_PROPOSED_GRADE = default_string) then
790 l_grade_id := l_person_asg.grade_id;
791 else
792 open c_grade(l_person_info.business_group_id);
793 fetch c_grade into l_grade_id;
794 close c_grade;
795 end if;
796 hr_utility.set_location('l_grade_id :'|| l_grade_id,130);
797 if(P_CHANGE_REASON = default_string) then
798 l_change_reason := l_person_asg.change_reason;
799 end if;
800 --12560928: convert null to -1 before comparing
801 IF(nvl(l_job_id,-1) <> nvl(l_person_asg.job_id,-1) OR nvl(l_pos_id,-1) <> nvl(l_person_asg.position_id,-1) OR
802 nvl(l_grade_id,-1) <> nvl(l_person_asg.grade_id,-1) OR nvl(l_change_reason,-1) <> nvl(l_person_asg.change_reason,-1)) THEN
803 l_changed := 'Y';
804 ELSE
805 l_changed := 'N';
806 END IF;
807
808
809 IF((l_assgn_perf_rating_prcd.new_assgn_ovn IS NULL) AND (l_changed = 'Y') ) THEN
810 --10261233
811 IF(nvl(P_PROPOSED_JOB,-1) <> default_string or nvl(P_PROPOSED_POSITION,-1) <> default_string or
812 nvl(P_PROPOSED_GRADE,-1) <> default_string or nvl(P_CHANGE_REASON,-1) <> default_string ) THEN
813 -- 8925417
814 if(l_pl_dsgn_info.asg_updt_eff_date is null) then
815 hr_utility.set_message(805,'BEN_93191_PROMO_EFFDT_NOT_DFND');
816 hr_utility.raise_error;
817 end if;
818 END IF;
819 hr_utility.set_location('calling process_promotions ',140);
820 ben_cwb_asg_update.process_promotions
821 (p_validate_data => 'Y'
822 ,p_assignment_id => l_person_info.Assignment_id
823 ,p_person_id => l_person_info.Person_id
824 ,p_business_group_id => l_person_info.business_group_id
825 ,p_asg_updt_eff_date => l_pl_dsgn_info.asg_updt_eff_date --to_char(l_pl_dsgn_info.asg_updt_eff_date,'yyyy/mm/dd')
826 ,p_change_reason => l_change_reason
827 ,p_job_id => l_job_id
828 ,p_position_id => l_pos_id
829 ,p_grade_id => l_grade_id
830 ,p_people_group_id => null
831 ,p_soft_coding_keyflex_id => null
832 ,p_ass_attribute1 => null
833 ,p_ass_attribute2 => null
834 ,p_ass_attribute3 => null
835 ,p_ass_attribute4 => null
836 ,p_ass_attribute5 => null
837 ,p_ass_attribute6 => null
838 ,p_ass_attribute7 => null
839 ,p_ass_attribute8 => null
840 ,p_ass_attribute9 => null
841 ,p_ass_attribute10 => null
842 ,p_ass_attribute11 => null
843 ,p_ass_attribute12 => null
844 ,p_ass_attribute13 => null
845 ,p_ass_attribute14 => null
846 ,p_ass_attribute15 => null
847 ,p_ass_attribute16 => null
848 ,p_ass_attribute17 => null
849 ,p_ass_attribute18 => null
850 ,p_ass_attribute19 => null
851 ,p_ass_attribute20 => null
852 ,p_ass_attribute21 => null
853 ,p_ass_attribute22 => null
854 ,p_ass_attribute23 => null
855 ,p_ass_attribute24 => null
856 ,p_ass_attribute25 => null
857 ,p_ass_attribute26 => null
858 ,p_ass_attribute27 => null
859 ,p_ass_attribute28 => null
860 ,p_ass_attribute29 => null
861 ,p_ass_attribute30 => null
862 ,p_person_name => l_person_info.Person_name
863 ,p_update_person_id => P_ACTING_PERSON_ID
864 ,p_update_date => Sysdate
865 ,p_group_pl_id => l_pl_dsgn_info.group_pl_id);
866 ELSE
867 hr_utility.set_location('Already processed, so skip calling process_promotions ',140);
868 IF(l_changed = 'Y') THEN
869 hr_utility.set_message(805,'BEN_94741_CWB_ASSGMT_PRCSD');
870 hr_utility.raise_error;
871 END IF;
872 END IF;
873
874 --End If;
875 hr_utility.set_location('Leaving :'||l_proc,100);
876 End update_promotions;
877
878 --
879 procedure update_other_rates (P_PERSON_RATE_ID IN NUMBER
880 ,p_interface_seq IN g_iterface_seq_type
881 ,p_values in g_iterface_seq_type
882 ,p_interface_code in varchar2
883 ,p_base_layout_code in varchar2 )
884 IS
885 p_final_values g_iterface_seq_type := g_iterface_seq_type(null,null,null,null,null,null);
886 Cursor Csr_PlRt_Dtls
887 IS
888 Select PlRt.GROUP_PER_IN_LER_ID GROUP_PER_IN_LER_ID,
889 PlRt.PL_ID PL_ID,
890 PlRt.OIPL_ID OIPL_ID,
891 PlRt.GROUP_PL_ID GROUP_PL_ID,
892 PlRt.GROUP_OIPL_ID GROUP_OIPL_ID,
893 PlRt.LF_EVT_OCRD_DT LF_EVT_OCRD_DT,
894 PlRt.Object_Version_Number OVN,
895 PlRt.STAT_SAL_VAL,
896 PlRt.OTH_COMP_VAL,
897 PlRt.TOT_COMP_VAL,
898 PlRt.MISC1_VAL,
899 PlRt.MISC2_VAL,
900 PlRt.MISC3_VAL
901 ,dsgn.misc1_rndg_cd m1_rndg_cd
902 ,dsgn.misc2_rndg_cd m2_rndg_cd
903 ,dsgn.misc3_rndg_cd m3_rndg_cd
904 ,dsgn.tot_comp_rndg_cd tc_rndg_cd
905 ,dsgn.oth_comp_rndg_cd oc_rndg_cd
906 ,dsgn.stat_sal_rndg_cd ss_rndg_cd
907 From BEN_CWB_PERSON_RATES PlRt
908 ,ben_cwb_pl_dsgn dsgn
909 Where PlRt.PERSON_RATE_ID = P_PERSON_RATE_ID
910 And PlRt.ELIG_FLAG='Y'
911 and plRt.pl_id = dsgn.pl_id
912 and plRt.oipl_id = dsgn.oipl_id
913 and plRt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt;
914 --and dsgn.ws_abr_id is not null;
915
916 cursor csr_is_read_only (l_seq1 in number,l_seq2 in number,l_seq3 in number,
917 l_seq4 in number,l_seq5 in number, l_seq6 in number) is
918 select interface_seq_num, decode(nvl(lay.read_only_flag,'N')||nvl(intf.read_only_flag,'N'), 'NN','N','Y') read_only
919 from bne_interface_cols_b intf, bne_layout_cols lay
920 where intf.interface_code = nvl(p_interface_code,'BEN_CWB_WRK_SHT_INTF')
921 and intf.application_id = 800
922 and intf.interface_code = lay.interface_code
923 and lay.layout_code = nvl(p_base_layout_code,'BEN_CWB_WRK_SHT_BASE_LYT')
924 and lay.application_id = 800
925 and intf.sequence_num = lay.interface_seq_num
926 and intf.sequence_num in (l_seq1,l_seq2,l_seq3,l_seq4,l_seq5,l_seq6);
927
928 l_proc varchar2(72) := g_package||'update_other_rates';
929
930 BEGIN
931
932 if g_debug then
933 hr_utility.set_location('Entering '||l_proc,10);
934 hr_utility.set_location('P_PERSON_RATE_ID '||P_PERSON_RATE_ID,20);
935 end if;
936
937 for l_upd_rec in Csr_PlRt_Dtls loop
938 p_final_values(1) := l_upd_rec.STAT_SAL_VAL;
939 p_final_values(2) := l_upd_rec.OTH_COMP_VAL;
940 p_final_values(3) := l_upd_rec.TOT_COMP_VAL;
941 p_final_values(4) := l_upd_rec.MISC1_VAL;
942 p_final_values(5) := l_upd_rec.MISC2_VAL;
943 p_final_values(6) := l_upd_rec.MISC3_VAL;
944 end loop;
945
946 if g_debug then
947 hr_utility.set_location(l_proc,20);
948 end if;
949
950 for l_upd_rec in Csr_PlRt_Dtls loop
951 for l_is_read_only in csr_is_read_only(p_interface_seq(1),p_interface_seq(2),p_interface_seq(3),
952 p_interface_seq(4),p_interface_seq(5),p_interface_seq(6))
953 loop
954 IF(l_is_read_only.interface_seq_num = p_interface_seq(1) AND l_is_read_only.read_only = 'N') THEN
955 p_final_values(1) := round_number( check_number_col_avble(l_upd_rec.STAT_SAL_VAL,p_values(1) ), l_upd_rec.ss_rndg_cd );
956 ELSIF(l_is_read_only.interface_seq_num = p_interface_seq(2) AND l_is_read_only.read_only = 'N') THEN
957 p_final_values(2) := round_number( check_number_col_avble(l_upd_rec.OTH_COMP_VAL,p_values(2) ), l_upd_rec.oc_rndg_cd );
958 ELSIF(l_is_read_only.interface_seq_num = p_interface_seq(3) AND l_is_read_only.read_only = 'N') THEN
959 p_final_values(3) := round_number( check_number_col_avble(l_upd_rec.TOT_COMP_VAL,p_values(3) ), l_upd_rec.tc_rndg_cd );
960 ELSIF(l_is_read_only.interface_seq_num = p_interface_seq(4) AND l_is_read_only.read_only = 'N') THEN
961 p_final_values(4) := round_number( check_number_col_avble(l_upd_rec.MISC1_VAL,p_values(4) ), l_upd_rec.m1_rndg_cd );
962 ELSIF(l_is_read_only.interface_seq_num = p_interface_seq(5) AND l_is_read_only.read_only = 'N') THEN
963 p_final_values(5) := round_number( check_number_col_avble(l_upd_rec.MISC2_VAL,p_values(5) ), l_upd_rec.m2_rndg_cd );
964 ELSIF(l_is_read_only.interface_seq_num = p_interface_seq(6) AND l_is_read_only.read_only = 'N') THEN
965 p_final_values(6) := round_number( check_number_col_avble(l_upd_rec.MISC3_VAL,p_values(6) ), l_upd_rec.m3_rndg_cd );
966 END IF;
967 end loop;
968 if g_debug then
969 hr_utility.set_location(l_proc,30);
970 end if;
971 BEN_CWB_PERSON_RATES_API.update_person_rate
972 ( p_group_per_in_ler_id => l_upd_rec.GROUP_PER_IN_LER_ID
973 ,p_pl_id => l_upd_rec.PL_ID
974 ,p_oipl_id => l_upd_rec.OIPL_ID
975 ,p_group_pl_id => l_upd_rec.GROUP_PL_ID
976 ,p_group_oipl_id => l_upd_rec.GROUP_OIPL_ID
977 ,p_lf_evt_ocrd_dt => l_upd_rec.LF_EVT_OCRD_DT
978 ,p_stat_sal_val => p_final_values(1)
979 ,p_oth_comp_val => p_final_values(2)
980 ,p_tot_comp_val => p_final_values(3)
981 ,p_misc1_val => p_final_values(4)
982 ,p_misc2_val => p_final_values(5)
983 ,p_misc3_val => p_final_values(6)
984 ,p_object_version_number => l_upd_rec.ovn
985 );
986 end loop;
987
988 if g_debug then
989 hr_utility.set_location('Leaving '||l_proc,100);
990 end if;
991
992 END update_other_rates;
993
994 --
995 --------------------------- REFRESH_SUMMARY_GROUP_PL -----------------------------
996 --
997
998 PROCEDURE REFRESH_SUMMARY_GROUP_PL (P_PERSON_RATE_ID IN NUMBER Default Null
999 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
1000 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
1001 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
1002 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
1003 IS
1004 Cursor csr_group_pl_id (l_person_rate_id IN Number)
1005 IS
1006 Select group_pl_id,
1007 lf_evt_ocrd_dt
1008 from ben_cwb_person_rates
1009 where person_rate_id = l_person_rate_id;
1010
1011
1012 l_rate_id Number;
1013 l_group_pl_id Number;
1014 l_lf_evt_ocrd_dt Date;
1015 BEGIN
1016
1017 If P_PERSON_RATE_ID IS NOT NULL then
1018 l_rate_id := P_PERSON_RATE_ID;
1019 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
1020 l_rate_id := P_OPT1_PERSON_RATE_ID;
1021 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
1022 l_rate_id := P_OPT2_PERSON_RATE_ID;
1023 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
1024 l_rate_id := P_OPT3_PERSON_RATE_ID;
1025 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
1026 l_rate_id := P_OPT4_PERSON_RATE_ID;
1027 End if;
1028
1029 Open csr_group_pl_id(l_rate_id);
1030 Fetch csr_group_pl_id into l_group_pl_id,l_lf_evt_ocrd_dt;
1031 Close csr_group_pl_id;
1032
1033 ben_cwb_summary_pkg.refresh_summary_group_pl(P_GROUP_PL_ID => l_group_pl_id
1034 ,P_LF_EVT_OCRD_DT => l_lf_evt_ocrd_dt);
1035
1036 END REFRESH_SUMMARY_GROUP_PL;
1037
1038 function get_plan_person_rate_id(p_opt_person_rate_id in number)
1039 return number is
1040 CURSOR c_get_pl_per_rates IS
1041 SELECT PlRt.person_rate_id
1042 FROM BEN_CWB_PERSON_RATES PlRt
1043 ,ben_cwb_person_rates optRt
1044 WHERE optRt.PERSON_RATE_ID = p_opt_person_rate_id
1045 and optRt.group_per_in_ler_id = plRt.group_per_in_ler_id
1046 and optRt.pl_id = plRt.pl_id
1047 and plRt.oipl_id = -1;
1048
1049 l_return_val number := null;
1050 begin
1051 open c_get_pl_per_rates;
1052 fetch c_get_pl_per_rates into l_return_val;
1053 close c_get_pl_per_rates;
1054
1055 return l_return_val;
1056 end;
1057
1058 --
1059 ---------------------------handle_row-----------------------------
1060 --
1061
1062 PROCEDURE handle_row
1063 (
1064 P_EMP_NAME IN VARCHAR2
1065 ,P_MGR_NAME IN VARCHAR2 DEFAULT NULL
1066 ,P_RANK IN NUMBER DEFAULT NULL
1067 ,P_YEARS_EMPLOYED IN NUMBER DEFAULT NULL
1068 ,P_BASE_SALARY IN NUMBER DEFAULT NULL
1069 ,P_PL_NAME IN VARCHAR2 DEFAULT NULL
1070 ,P_PL_XCHG_RATE IN NUMBER DEFAULT NULL
1071 ,P_PL_STAT_SAL_VAL IN NUMBER DEFAULT default_number
1072 ,P_PL_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
1073 ,P_PL_TOT_COMP_VAL IN NUMBER DEFAULT default_number
1074 ,P_PL_OTH_COMP_VAL IN NUMBER DEFAULT default_number
1075 ,P_PL_WS_VAL IN NUMBER DEFAULT NULL
1076 ,P_PL_WS_MIN_VAL IN NUMBER DEFAULT NULL
1077 ,P_PL_WS_MAX_VAL IN NUMBER DEFAULT NULL
1078 ,P_PL_WS_INCR_VAL IN NUMBER DEFAULT NULL
1079 ,P_PL_REC_VAL IN NUMBER DEFAULT NULL
1080 ,P_PL_REC_MIN_VAL IN NUMBER DEFAULT NULL
1081 ,P_PL_REC_MAX_VAL IN NUMBER DEFAULT NULL
1082 ,P_PL_MISC1_VAL IN NUMBER DEFAULT default_number
1083 ,P_PL_MISC2_VAL IN NUMBER DEFAULT default_number
1084 ,P_PL_MISC3_VAL IN NUMBER DEFAULT default_number
1085 ,P_PL_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
1086 ,P_PL_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
1087 ,P_OPT1_NAME IN VARCHAR2 DEFAULT NULL
1088 ,P_OPT1_XCHG_RATE IN NUMBER DEFAULT NULL
1089 ,P_OPT1_STAT_SAL_VAL IN NUMBER DEFAULT default_number
1090 ,P_OPT1_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
1091 ,P_OPT1_TOT_COMP_VAL IN NUMBER DEFAULT default_number
1092 ,P_OPT1_OTH_COMP_VAL IN NUMBER DEFAULT default_number
1093 ,P_OPT1_WS_VAL IN NUMBER DEFAULT NULL
1094 ,P_OPT1_WS_MIN_VAL IN NUMBER DEFAULT NULL
1095 ,P_OPT1_WS_MAX_VAL IN NUMBER DEFAULT NULL
1096 ,P_OPT1_WS_INCR_VAL IN NUMBER DEFAULT NULL
1097 ,P_OPT1_REC_VAL IN NUMBER DEFAULT NULL
1098 ,P_OPT1_REC_MIN_VAL IN NUMBER DEFAULT NULL
1099 ,P_OPT1_REC_MAX_VAL IN NUMBER DEFAULT NULL
1100 ,P_OPT1_MISC1_VAL IN NUMBER DEFAULT default_number
1101 ,P_OPT1_MISC2_VAL IN NUMBER DEFAULT default_number
1102 ,P_OPT1_MISC3_VAL IN NUMBER DEFAULT default_number
1103 ,P_OPT1_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
1104 ,P_OPT1_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
1105 ,P_OPT2_NAME IN VARCHAR2 DEFAULT NULL
1106 ,P_OPT2_XCHG_RATE IN NUMBER DEFAULT NULL
1107 ,P_OPT2_STAT_SAL_VAL IN NUMBER DEFAULT default_number
1108 ,P_OPT2_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
1109 ,P_OPT2_TOT_COMP_VAL IN NUMBER DEFAULT default_number
1110 ,P_OPT2_OTH_COMP_VAL IN NUMBER DEFAULT default_number
1111 ,P_OPT2_WS_VAL IN NUMBER DEFAULT NULL
1112 ,P_OPT2_WS_MIN_VAL IN NUMBER DEFAULT NULL
1113 ,P_OPT2_WS_MAX_VAL IN NUMBER DEFAULT NULL
1114 ,P_OPT2_WS_INCR_VAL IN NUMBER DEFAULT NULL
1115 ,P_OPT2_REC_VAL IN NUMBER DEFAULT NULL
1116 ,P_OPT2_REC_MIN_VAL IN NUMBER DEFAULT NULL
1117 ,P_OPT2_REC_MAX_VAL IN NUMBER DEFAULT NULL
1118 ,P_OPT2_MISC1_VAL IN NUMBER DEFAULT default_number
1119 ,P_OPT2_MISC2_VAL IN NUMBER DEFAULT default_number
1120 ,P_OPT2_MISC3_VAL IN NUMBER DEFAULT default_number
1121 ,P_OPT2_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
1122 ,P_OPT2_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
1123 ,P_OPT3_NAME IN VARCHAR2 DEFAULT NULL
1124 ,P_OPT3_XCHG_RATE IN NUMBER DEFAULT NULL
1125 ,P_OPT3_STAT_SAL_VAL IN NUMBER DEFAULT default_number
1126 ,P_OPT3_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
1127 ,P_OPT3_TOT_COMP_VAL IN NUMBER DEFAULT default_number
1128 ,P_OPT3_OTH_COMP_VAL IN NUMBER DEFAULT default_number
1129 ,P_OPT3_WS_VAL IN NUMBER DEFAULT NULL
1130 ,P_OPT3_WS_MIN_VAL IN NUMBER DEFAULT NULL
1131 ,P_OPT3_WS_MAX_VAL IN NUMBER DEFAULT NULL
1132 ,P_OPT3_WS_INCR_VAL IN NUMBER DEFAULT NULL
1133 ,P_OPT3_REC_VAL IN NUMBER DEFAULT NULL
1134 ,P_OPT3_REC_MIN_VAL IN NUMBER DEFAULT NULL
1135 ,P_OPT3_REC_MAX_VAL IN NUMBER DEFAULT NULL
1136 ,P_OPT3_MISC1_VAL IN NUMBER DEFAULT default_number
1137 ,P_OPT3_MISC2_VAL IN NUMBER DEFAULT default_number
1138 ,P_OPT3_MISC3_VAL IN NUMBER DEFAULT default_number
1139 ,P_OPT3_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
1140 ,P_OPT3_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
1141 ,P_OPT4_NAME IN VARCHAR2 DEFAULT NULL
1142 ,P_OPT4_XCHG_RATE IN NUMBER DEFAULT NULL
1143 ,P_OPT4_STAT_SAL_VAL IN NUMBER DEFAULT default_number
1144 ,P_OPT4_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
1145 ,P_OPT4_TOT_COMP_VAL IN NUMBER DEFAULT default_number
1146 ,P_OPT4_OTH_COMP_VAL IN NUMBER DEFAULT NULL
1147 ,P_OPT4_WS_VAL IN NUMBER DEFAULT NULL
1148 ,P_OPT4_WS_MIN_VAL IN NUMBER DEFAULT NULL
1149 ,P_OPT4_WS_MAX_VAL IN NUMBER DEFAULT NULL
1150 ,P_OPT4_WS_INCR_VAL IN NUMBER DEFAULT NULL
1151 ,P_OPT4_REC_VAL IN NUMBER DEFAULT NULL
1152 ,P_OPT4_REC_MIN_VAL IN NUMBER DEFAULT NULL
1153 ,P_OPT4_REC_MAX_VAL IN NUMBER DEFAULT NULL
1154 ,P_OPT4_MISC1_VAL IN NUMBER DEFAULT default_number
1155 ,P_OPT4_MISC2_VAL IN NUMBER DEFAULT default_number
1156 ,P_OPT4_MISC3_VAL IN NUMBER DEFAULT default_number
1157 ,P_OPT4_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
1158 ,P_OPT4_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
1159 ,P_EMPLOYEE_NUMBER IN VARCHAR2 DEFAULT NULL
1160 ,P_EMP_CATEGORY IN VARCHAR2 DEFAULT NULL
1161 ,P_ASSIGNMENT_STATUS IN VARCHAR2 DEFAULT NULL
1162 ,P_PEOPLE_GROUP_NAME IN VARCHAR2 DEFAULT NULL
1163 ,P_EMAIL_ADDR IN VARCHAR2 DEFAULT NULL
1164 ,P_START_DATE IN DATE DEFAULT NULL
1165 ,P_ORIGINAL_START_DATE IN DATE DEFAULT NULL
1166 ,P_NORMAL_HOURS IN NUMBER DEFAULT NULL
1167 ,P_PAYROLL_NAME IN VARCHAR2 DEFAULT NULL
1168 ,P_BUSINESS_GROUP_NAME IN VARCHAR2 DEFAULT NULL
1169 ,P_ORG_NAME IN VARCHAR2 DEFAULT NULL
1170 ,P_LOC_NAME IN VARCHAR2 DEFAULT NULL
1171 ,P_JOB_NAME IN VARCHAR2 DEFAULT NULL
1172 ,P_POS_NAME IN VARCHAR2 DEFAULT NULL
1173 ,P_GRD_NAME IN VARCHAR2 DEFAULT NULL
1174 ,P_COUNTRY IN VARCHAR2 DEFAULT NULL
1175 ,P_YEARS_IN_JOB IN NUMBER DEFAULT NULL
1176 ,P_YEARS_IN_POSITION IN NUMBER DEFAULT NULL
1177 ,P_YEARS_IN_GRADE IN NUMBER DEFAULT NULL
1178 ,P_GRADE_RANGE IN VARCHAR2 DEFAULT NULL
1179 ,P_GRADE_MID_POINT IN NUMBER DEFAULT NULL
1180 ,P_GRD_QUARTILE IN VARCHAR2 DEFAULT NULL
1181 ,P_GRD_COMPARATIO IN NUMBER DEFAULT NULL
1182 ,P_PERFORMANCE_RATING IN VARCHAR2 DEFAULT NULL
1183 ,P_PERFORMANCE_RATING_TYPE IN VARCHAR2 DEFAULT NULL
1184 ,P_PERFORMANCE_RATING_DATE IN DATE DEFAULT NULL
1185 ,P_LAST_RANK IN NUMBER DEFAULT NULL
1186 ,P_LAST_MGR_NAME IN VARCHAR2 DEFAULT NULL
1187 ,P_RANK_QUARTILE IN NUMBER DEFAULT NULL
1188 ,P_TOTAL_RANK IN NUMBER DEFAULT NULL
1189 ,P_CHANGE_REASON IN VARCHAR2 DEFAULT default_string
1190 ,P_BASE_SALARY_CHANGE_DATE IN DATE DEFAULT NULL
1191 ,P_LF_EVT_OCRD_DT IN DATE DEFAULT NULL
1192 ,P_MGR_LER_ID IN NUMBER DEFAULT NULL
1193 ,P_PL_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
1194 ,P_P_OPT1_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
1195 ,P_P_OPT2_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
1196 ,P_P_OPT3_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
1197 ,P_P_OPT4_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
1198 ,P_LVL_NUM IN NUMBER DEFAULT NULL
1199 ,P_CUSTOM_SEGMENT1 IN VARCHAR2 DEFAULT default_string
1200 ,P_CUSTOM_SEGMENT2 IN VARCHAR2 DEFAULT default_string
1201 ,P_CUSTOM_SEGMENT3 IN VARCHAR2 DEFAULT default_string
1202 ,P_CUSTOM_SEGMENT4 IN VARCHAR2 DEFAULT default_string
1203 ,P_CUSTOM_SEGMENT5 IN VARCHAR2 DEFAULT default_string
1204 ,P_CUSTOM_SEGMENT6 IN VARCHAR2 DEFAULT default_string
1205 ,P_CUSTOM_SEGMENT7 IN VARCHAR2 DEFAULT default_string
1206 ,P_CUSTOM_SEGMENT8 IN VARCHAR2 DEFAULT default_string
1207 ,P_CUSTOM_SEGMENT9 IN VARCHAR2 DEFAULT default_string
1208 ,P_CUSTOM_SEGMENT10 IN VARCHAR2 DEFAULT default_string
1209 ,P_CUSTOM_SEGMENT11 IN NUMBER DEFAULT default_number
1210 ,P_CUSTOM_SEGMENT12 IN NUMBER DEFAULT default_number
1211 ,P_CUSTOM_SEGMENT13 IN NUMBER DEFAULT default_number
1212 ,P_CUSTOM_SEGMENT14 IN NUMBER DEFAULT default_number
1213 ,P_CUSTOM_SEGMENT15 IN NUMBER DEFAULT default_number
1214 ,P_PROPOSED_PERFORMANCE_RATING IN VARCHAR2 DEFAULT NULL
1215 ,P_PROPOSED_JOB IN VARCHAR2 DEFAULT default_string
1216 ,P_PLAN_UOM IN VARCHAR2 DEFAULT NULL
1217 ,P_OPT1_UOM IN VARCHAR2 DEFAULT NULL
1218 ,P_OPT2_UOM IN VARCHAR2 DEFAULT NULL
1219 ,P_OPT3_UOM IN VARCHAR2 DEFAULT NULL
1220 ,P_OPT4_UOM IN VARCHAR2 DEFAULT NULL
1221 ,P_USER_ID IN VARCHAR2 DEFAULT NULL
1222 ,P_PROPOSED_GRADE IN VARCHAR2 DEFAULT default_string
1223 ,P_PROPOSED_POSITION IN VARCHAR2 DEFAULT default_string
1224 ,P_PROPOSED_GROUP IN VARCHAR2 DEFAULT NULL
1225 ,P_TASK_ID IN VARCHAR2 DEFAULT NULL
1226 ,P_SEC_MGR_LER_ID IN VARCHAR2 DEFAULT NULL
1227 ,P_ACTING_PERSON_ID IN VARCHAR2 DEFAULT NULL
1228 ,P_DOWNLOAD_SWITCH IN VARCHAR2 DEFAULT NULL
1229 ,P_CPI_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL
1230 ,P_CPI_ATTRIBUTE1 IN VARCHAR2 DEFAULT default_string
1231 ,P_CPI_ATTRIBUTE2 IN VARCHAR2 DEFAULT default_string
1232 ,P_CPI_ATTRIBUTE3 IN VARCHAR2 DEFAULT default_string
1233 ,P_CPI_ATTRIBUTE4 IN VARCHAR2 DEFAULT default_string
1234 ,P_CPI_ATTRIBUTE5 IN VARCHAR2 DEFAULT default_string
1235 ,P_CPI_ATTRIBUTE6 IN VARCHAR2 DEFAULT default_string
1236 ,P_CPI_ATTRIBUTE7 IN VARCHAR2 DEFAULT default_string
1237 ,P_CPI_ATTRIBUTE8 IN VARCHAR2 DEFAULT default_string
1238 ,P_CPI_ATTRIBUTE9 IN VARCHAR2 DEFAULT default_string
1239 ,P_CPI_ATTRIBUTE10 IN VARCHAR2 DEFAULT default_string
1240 ,P_CPI_ATTRIBUTE11 IN VARCHAR2 DEFAULT default_string
1241 ,P_CPI_ATTRIBUTE12 IN VARCHAR2 DEFAULT default_string
1242 ,P_CPI_ATTRIBUTE13 IN VARCHAR2 DEFAULT default_string
1243 ,P_CPI_ATTRIBUTE14 IN VARCHAR2 DEFAULT default_string
1244 ,P_CPI_ATTRIBUTE15 IN VARCHAR2 DEFAULT default_string
1245 ,P_CPI_ATTRIBUTE16 IN VARCHAR2 DEFAULT default_string
1246 ,P_CPI_ATTRIBUTE17 IN VARCHAR2 DEFAULT default_string
1247 ,P_CPI_ATTRIBUTE18 IN VARCHAR2 DEFAULT default_string
1248 ,P_CPI_ATTRIBUTE19 IN VARCHAR2 DEFAULT default_string
1249 ,P_CPI_ATTRIBUTE20 IN VARCHAR2 DEFAULT default_string
1250 ,P_CPI_ATTRIBUTE21 IN VARCHAR2 DEFAULT default_string
1251 ,P_CPI_ATTRIBUTE22 IN VARCHAR2 DEFAULT default_string
1252 ,P_CPI_ATTRIBUTE23 IN VARCHAR2 DEFAULT default_string
1253 ,P_CPI_ATTRIBUTE24 IN VARCHAR2 DEFAULT default_string
1254 ,P_CPI_ATTRIBUTE25 IN VARCHAR2 DEFAULT default_string
1255 ,P_CPI_ATTRIBUTE26 IN VARCHAR2 DEFAULT default_string
1256 ,P_CPI_ATTRIBUTE27 IN VARCHAR2 DEFAULT default_string
1257 ,P_CPI_ATTRIBUTE28 IN VARCHAR2 DEFAULT default_string
1258 ,P_CPI_ATTRIBUTE29 IN VARCHAR2 DEFAULT default_string
1259 ,P_CPI_ATTRIBUTE30 IN VARCHAR2 DEFAULT default_string
1260 ,P_CUSTOM_SEGMENT16 IN NUMBER DEFAULT default_number
1261 ,P_CUSTOM_SEGMENT17 IN NUMBER DEFAULT default_number
1262 ,P_CUSTOM_SEGMENT18 IN NUMBER DEFAULT default_number
1263 ,P_CUSTOM_SEGMENT19 IN NUMBER DEFAULT default_number
1264 ,P_CUSTOM_SEGMENT20 IN NUMBER DEFAULT default_number
1265 ,P_PL_CURRENCY IN VARCHAR2 DEFAULT NULL
1266 ,P_OPT1_CURRENCY IN VARCHAR2 DEFAULT NULL
1267 ,P_OPT2_CURRENCY IN VARCHAR2 DEFAULT NULL
1268 ,P_OPT3_CURRENCY IN VARCHAR2 DEFAULT NULL
1269 ,P_OPT4_CURRENCY IN VARCHAR2 DEFAULT NULL
1270 ,P_PL_RT_START_DATE IN DATE DEFAULT default_date
1271 ,P_OPT1_RT_START_DATE IN DATE DEFAULT default_date
1272 ,P_OPT2_RT_START_DATE IN DATE DEFAULT default_date
1273 ,P_OPT3_RT_START_DATE IN DATE DEFAULT default_date
1274 ,P_OPT4_RT_START_DATE IN DATE DEFAULT default_date
1275 ,P_PROPOSED_GRADE_RANGE IN VARCHAR2 DEFAULT NULL
1276 ,P_PROPOSED_GRADE_MID_POINT IN NUMBER DEFAULT NULL
1277 ,P_PROPOSED_GRD_COMPARATIO IN NUMBER DEFAULT NULL
1278
1279 ) IS
1280
1281 CURSOR Csr_get_pl_ws_val(l_PL_PERSON_RATE_ID IN NUMBER)
1282 IS
1283 SELECT PlRt.WS_VAL
1284 FROM BEN_CWB_PERSON_RATES PlRt
1285 WHERE PlRt.PERSON_RATE_ID = l_PL_PERSON_RATE_ID;
1286
1287 CURSOR csr_cpi_flex_info(l_group_per_in_ler_id IN NUMBER)
1288 IS
1289 SELECT cpi_attribute_category,
1290 cpi_attribute1,
1291 cpi_attribute2,
1292 cpi_attribute3,
1293 cpi_attribute4,
1294 cpi_attribute5,
1295 cpi_attribute6,
1296 cpi_attribute7,
1297 cpi_attribute8,
1298 cpi_attribute9,
1299 cpi_attribute10,
1300 cpi_attribute11,
1301 cpi_attribute12,
1302 cpi_attribute13,
1303 cpi_attribute14,
1304 cpi_attribute15,
1305 cpi_attribute16,
1306 cpi_attribute17,
1307 cpi_attribute18,
1308 cpi_attribute19,
1309 cpi_attribute20,
1310 cpi_attribute21,
1311 cpi_attribute22,
1312 cpi_attribute23,
1313 cpi_attribute24,
1314 cpi_attribute25,
1315 cpi_attribute26,
1316 cpi_attribute27,
1317 cpi_attribute28,
1318 cpi_attribute29,
1319 cpi_attribute30,
1320 custom_segment1,
1321 custom_segment2,
1322 custom_segment3,
1323 custom_segment4,
1324 custom_segment5,
1325 custom_segment6,
1326 custom_segment7,
1327 custom_segment8,
1328 custom_segment9,
1329 custom_segment10,
1330 custom_segment11,
1331 custom_segment12,
1332 custom_segment13,
1333 custom_segment14,
1334 custom_segment15,
1335 custom_segment16,
1336 custom_segment17,
1337 custom_segment18,
1338 custom_segment19,
1339 custom_segment20,
1340 object_version_number
1341 FROM ben_cwb_person_info
1342 WHERE group_per_in_ler_id = l_group_per_in_ler_id;
1343
1344 CURSOR csr_get_group_plan_info(l_group_per_in_ler_id IN NUMBER)
1345 IS
1346 SELECT group_pl_id, lf_evt_ocrd_dt
1347 FROM ben_per_in_ler
1348 WHERE per_in_ler_id = l_group_per_in_ler_id
1349 AND ROWNUM < 2;
1350
1351 cursor csr_custom_integrator(l_group_pl_id in Number,
1352 l_lf_evt_ocrd_dt in Date) is
1353 select 'BEN_CWB_WS_INTF_' || trim(group_pl_id) intf,
1354 'BEN_CWB_WS_LYT1_' || trim(group_pl_id) base_layout
1355 from ben_cwb_pl_dsgn
1356 where custom_integrator is not null
1357 and group_pl_id = l_group_pl_id
1358 and lf_evt_ocrd_dt = l_lf_evt_ocrd_dt;
1359
1360 cursor csr_is_read_only (l_intf in varchar2,
1361 l_base_layout in varchar2) is
1362 select interface_seq_num, decode(nvl(lay.read_only_flag,'N')||nvl(intf.read_only_flag,'N'), 'NN','N','Y') read_only
1363 from bne_interface_cols_b intf, bne_layout_cols lay
1364 where intf.interface_code = nvl(l_intf,'BEN_CWB_WRK_SHT_INTF')
1365 and intf.application_id = 800
1366 and intf.interface_code = lay.interface_code
1367 and lay.layout_code = nvl(l_base_layout,'BEN_CWB_WRK_SHT_BASE_LYT')
1368 and lay.application_id = 800
1369 and intf.sequence_num = lay.interface_seq_num
1370 and ((intf.sequence_num between 200 and 234) or (intf.sequence_num between 136 and 150));
1371
1372 l_interfac_code bne_interface_cols_b.interface_code%TYPE;
1373 l_base_layout_code bne_layout_cols.layout_code%TYPE;
1374 l_is_read_only csr_is_read_only%RowType;
1375 l_pl_ws_val BEN_CWB_PERSON_RATES.WS_VAL%Type;
1376 l_group_per_in_ler_id BEN_CWB_PERSON_RATES.GROUP_PER_IN_LER_ID%Type;
1377 l_cpi_attribute_category BEN_CWB_PERSON_INFO.cpi_attribute_category%TYPE;
1378 l_cpi_attribute1 BEN_CWB_PERSON_INFO.cpi_attribute1%TYPE;
1379 l_cpi_attribute2 BEN_CWB_PERSON_INFO.cpi_attribute2%TYPE;
1380 l_cpi_attribute3 BEN_CWB_PERSON_INFO.cpi_attribute3%TYPE;
1381 l_cpi_attribute4 BEN_CWB_PERSON_INFO.cpi_attribute4%TYPE;
1382 l_cpi_attribute5 BEN_CWB_PERSON_INFO.cpi_attribute5%TYPE;
1383 l_cpi_attribute6 BEN_CWB_PERSON_INFO.cpi_attribute6%TYPE;
1384 l_cpi_attribute7 BEN_CWB_PERSON_INFO.cpi_attribute7%TYPE;
1385 l_cpi_attribute8 BEN_CWB_PERSON_INFO.cpi_attribute8%TYPE;
1386 l_cpi_attribute9 BEN_CWB_PERSON_INFO.cpi_attribute9%TYPE;
1387 l_cpi_attribute10 BEN_CWB_PERSON_INFO.cpi_attribute10%TYPE;
1388 l_cpi_attribute11 BEN_CWB_PERSON_INFO.cpi_attribute11%TYPE;
1389 l_cpi_attribute12 BEN_CWB_PERSON_INFO.cpi_attribute12%TYPE;
1390 l_cpi_attribute13 BEN_CWB_PERSON_INFO.cpi_attribute13%TYPE;
1391 l_cpi_attribute14 BEN_CWB_PERSON_INFO.cpi_attribute14%TYPE;
1392 l_cpi_attribute15 BEN_CWB_PERSON_INFO.cpi_attribute15%TYPE;
1393 l_cpi_attribute16 BEN_CWB_PERSON_INFO.cpi_attribute16%TYPE;
1394 l_cpi_attribute17 BEN_CWB_PERSON_INFO.cpi_attribute17%TYPE;
1395 l_cpi_attribute18 BEN_CWB_PERSON_INFO.cpi_attribute18%TYPE;
1396 l_cpi_attribute19 BEN_CWB_PERSON_INFO.cpi_attribute19%TYPE;
1397 l_cpi_attribute20 BEN_CWB_PERSON_INFO.cpi_attribute20%TYPE;
1398 l_cpi_attribute21 BEN_CWB_PERSON_INFO.cpi_attribute21%TYPE;
1399 l_cpi_attribute22 BEN_CWB_PERSON_INFO.cpi_attribute22%TYPE;
1400 l_cpi_attribute23 BEN_CWB_PERSON_INFO.cpi_attribute23%TYPE;
1401 l_cpi_attribute24 BEN_CWB_PERSON_INFO.cpi_attribute24%TYPE;
1402 l_cpi_attribute25 BEN_CWB_PERSON_INFO.cpi_attribute25%TYPE;
1403 l_cpi_attribute26 BEN_CWB_PERSON_INFO.cpi_attribute26%TYPE;
1404 l_cpi_attribute27 BEN_CWB_PERSON_INFO.cpi_attribute27%TYPE;
1405 l_cpi_attribute28 BEN_CWB_PERSON_INFO.cpi_attribute28%TYPE;
1406 l_cpi_attribute29 BEN_CWB_PERSON_INFO.cpi_attribute29%TYPE;
1407 l_cpi_attribute30 BEN_CWB_PERSON_INFO.cpi_attribute30%TYPE;
1408 l_custom_segment1 BEN_CWB_PERSON_INFO.custom_segment1%TYPE;
1409 l_custom_segment2 BEN_CWB_PERSON_INFO.custom_segment2%TYPE;
1410 l_custom_segment3 BEN_CWB_PERSON_INFO.custom_segment3%TYPE;
1411 l_custom_segment4 BEN_CWB_PERSON_INFO.custom_segment4%TYPE;
1412 l_custom_segment5 BEN_CWB_PERSON_INFO.custom_segment5%TYPE;
1413 l_custom_segment6 BEN_CWB_PERSON_INFO.custom_segment6%TYPE;
1414 l_custom_segment7 BEN_CWB_PERSON_INFO.custom_segment7%TYPE;
1415 l_custom_segment8 BEN_CWB_PERSON_INFO.custom_segment8%TYPE;
1416 l_custom_segment9 BEN_CWB_PERSON_INFO.custom_segment9%TYPE;
1417 l_custom_segment10 BEN_CWB_PERSON_INFO.custom_segment10%TYPE;
1418 l_custom_segment11 BEN_CWB_PERSON_INFO.custom_segment11%TYPE;
1419 l_custom_segment12 BEN_CWB_PERSON_INFO.custom_segment12%TYPE;
1420 l_custom_segment13 BEN_CWB_PERSON_INFO.custom_segment13%TYPE;
1421 l_custom_segment14 BEN_CWB_PERSON_INFO.custom_segment14%TYPE;
1422 l_custom_segment15 BEN_CWB_PERSON_INFO.custom_segment15%TYPE;
1423 l_custom_segment16 BEN_CWB_PERSON_INFO.custom_segment16%TYPE;
1424 l_custom_segment17 BEN_CWB_PERSON_INFO.custom_segment17%TYPE;
1425 l_custom_segment18 BEN_CWB_PERSON_INFO.custom_segment18%TYPE;
1426 l_custom_segment19 BEN_CWB_PERSON_INFO.custom_segment19%TYPE;
1427 l_custom_segment20 BEN_CWB_PERSON_INFO.custom_segment20%TYPE;
1428 l_ovn BEN_CWB_PERSON_INFO.object_version_number%TYPE;
1429 l_proc varchar2(72) := g_package||'handle_row';
1430 l_pl_person_rate_id Number := null;
1431 l_opt1_person_rate_id Number := null;
1432 l_opt2_person_rate_id Number := null;
1433 l_opt3_person_rate_id Number := null;
1434 l_opt4_person_rate_id Number := null;
1435 l_task_id Number := null;
1436 l_sec_mgr_ler_id Number := null;
1437 l_acting_person_id Number := null;
1438 l_decrypt_switch varchar2(200) := null;
1439 l_download_switch varchar2(200) := null;
1440 l_diff number := null;
1441 l_group_pl_id Number := null;
1442 l_lf_evt_ocrd_dt Date := null;
1443
1444 BEGIN
1445
1446 If g_debug then
1447 hr_utility.set_location('Entering '||l_proc,10);
1448 End if;
1449
1450 --Clear message
1451 hr_utility.clear_message;
1452
1453 -- Issue Savepoint
1454 savepoint update_data;
1455 --
1456 --
1457 BEN_CWB_SUMMARY_PKG.delete_pl_sql_tab;
1458
1459
1460
1461 IF (P_PL_PERSON_RATE_ID IS NULL
1462 AND P_P_OPT1_PERSON_RATE_ID IS NULL
1463 AND P_P_OPT2_PERSON_RATE_ID IS NULL
1464 AND P_P_OPT3_PERSON_RATE_ID IS NULL
1465 AND P_P_OPT4_PERSON_RATE_ID IS NULL) THEN
1466
1467 hr_utility.set_message(805,'BEN_CWB_PL_OPT_NOT_EXISTS');
1468 hr_utility.raise_error;
1469 END IF;
1470
1471 IF (P_PL_PERSON_RATE_ID IS NOT NULL) THEN
1472 l_PL_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_PL_PERSON_RATE_ID);
1473 If g_debug then
1474 hr_utility.set_location('l_PL_PERSON_RATE_ID :'||l_PL_PERSON_RATE_ID,20);
1475 End if;
1476 END IF;
1477
1478 IF (P_P_OPT1_PERSON_RATE_ID IS NOT NULL) THEN
1479 l_OPT1_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT1_PERSON_RATE_ID);
1480 If g_debug then
1481 hr_utility.set_location('l_OPT1_PERSON_RATE_ID :'||l_OPT1_PERSON_RATE_ID,30);
1482 End if;
1483 END IF;
1484
1485 IF (P_P_OPT2_PERSON_RATE_ID IS NOT NULL) THEN
1486 l_OPT2_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT2_PERSON_RATE_ID);
1487 If g_debug then
1488 hr_utility.set_location('l_OPT2_PERSON_RATE_ID :'||l_OPT2_PERSON_RATE_ID,40);
1489 End if;
1490 END IF;
1491
1492 IF (P_P_OPT3_PERSON_RATE_ID IS NOT NULL) THEN
1493 l_OPT3_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT3_PERSON_RATE_ID);
1494 If g_debug then
1495 hr_utility.set_location('l_OPT3_PERSON_RATE_ID :'||l_OPT3_PERSON_RATE_ID,50);
1496 End if;
1497 END IF;
1498
1499 IF (P_P_OPT4_PERSON_RATE_ID IS NOT NULL) THEN
1500 l_OPT4_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT4_PERSON_RATE_ID);
1501 If g_debug then
1502 hr_utility.set_location('l_OPT4_PERSON_RATE_ID :'||l_OPT4_PERSON_RATE_ID,60);
1503 End if;
1504 END IF;
1505
1506 IF (P_TASK_ID IS NOT NULL) THEN
1507 l_TASK_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_TASK_ID);
1508 If g_debug then
1509 hr_utility.set_location('l_TASK_ID :'||l_TASK_ID,65);
1510 End if;
1511 END IF;
1512
1513 IF (P_SEC_MGR_LER_ID IS NOT NULL) THEN
1514 l_SEC_MGR_LER_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_SEC_MGR_LER_ID);
1515 If g_debug then
1516 hr_utility.set_location('l_SEC_MGR_LER_ID :'||l_SEC_MGR_LER_ID,67);
1517 End if;
1518 END IF;
1519
1520 IF (P_ACTING_PERSON_ID IS NOT NULL) THEN
1521 l_ACTING_PERSON_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_ACTING_PERSON_ID);
1522 If g_debug then
1523 hr_utility.set_location('P_ACTING_PERSON_ID :'||l_ACTING_PERSON_ID,68);
1524 End if;
1525 END IF;
1526
1527 -- Using hexadecimal to store the upload column availability information.
1528
1529 IF (P_DOWNLOAD_SWITCH IS NOT NULL) THEN
1530 l_decrypt_switch := BEN_CWB_WEBADI_UTILS.decrypt(p_download_switch);
1531 l_download_switch := lpad(ben_cwb_webadi_utils.int2bin(ben_cwb_webadi_utils.hex2int(substr(l_decrypt_switch,1,7))),28,0);
1532 l_download_switch := nvl(l_download_switch || lpad(nvl(ben_cwb_webadi_utils.int2bin(ben_cwb_webadi_utils.hex2int(substr(rtrim(l_decrypt_switch),8,14))),'0'),28,0),'0');
1533 if g_debug then
1534 hr_utility.set_location('p_download_switch (Decrypted) :'||l_decrypt_switch,69);
1535 End if;
1536 l_download_switch := REPLACE(REPLACE(l_download_switch,'1','2'),'0','1');
1537 l_download_switch := substr(l_download_switch,2);
1538 if g_debug then
1539 hr_utility.set_location('l_final_download_Switch :'||l_download_switch,70);
1540 End if;
1541 END IF;
1542
1543 If g_debug then
1544 hr_utility.set_location('P_PL_WS_VAL : '||P_PL_WS_VAL,75);
1545 hr_utility.set_location('P_OPT1_WS_VAL : '||P_OPT1_WS_VAL,80);
1546 hr_utility.set_location('P_OPT2_WS_VAL : '||P_OPT2_WS_VAL,90);
1547 hr_utility.set_location('P_OPT3_WS_VAL : '||P_OPT3_WS_VAL,100);
1548 hr_utility.set_location('P_OPT4_WS_VAL : '||P_OPT4_WS_VAL,110);
1549 End if;
1550
1551
1552 l_group_per_in_ler_id := get_group_per_in_ler_id(l_PL_PERSON_RATE_ID,
1553 l_OPT1_PERSON_RATE_ID,
1554 l_OPT2_PERSON_RATE_ID,
1555 l_OPT3_PERSON_RATE_ID,
1556 l_OPT4_PERSON_RATE_ID);
1557
1558 -- Check for people that have already been Processed.
1559 -- If already processed, raise error.
1560
1561 chk_processed_emp (P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1562 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1563 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1564 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1565 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
1566 ,P_EMP_PER_IN_LER_ID => l_group_per_in_ler_id -- bug: 8996634
1567 ,P_MGR_PER_IN_LER_ID => l_SEC_MGR_LER_ID);
1568
1569 If g_debug then
1570 hr_utility.set_location('l_group_per_in_ler_id : '||l_group_per_in_ler_id,111);
1571 End if;
1572
1573 l_interfac_code := null;
1574 l_base_layout_code := null;
1575 FOR l_get_group_plan_info in csr_get_group_plan_info(l_group_per_in_ler_id) loop
1576 FOR l_custom_integrator in csr_custom_integrator (l_get_group_plan_info.group_pl_id, l_get_group_plan_info.lf_evt_ocrd_dt) loop
1577 l_interfac_code := l_custom_integrator.intf;
1578 l_base_layout_code := l_custom_integrator.base_layout;
1579 If g_debug then
1580 hr_utility.set_location('l_interfac_code : '||l_interfac_code,112);
1581 hr_utility.set_location('l_base_layout_code : '||l_base_layout_code,113);
1582 end if;
1583 END LOOP;
1584 END LOOP;
1585
1586 -- If No Options Exist then allow Modifying the Plan Worksheet Amount
1587 IF (l_PL_PERSON_RATE_ID IS NOT NULL
1588 AND l_OPT1_PERSON_RATE_ID IS NULL
1589 AND l_OPT2_PERSON_RATE_ID IS NULL
1590 AND l_OPT3_PERSON_RATE_ID IS NULL
1591 AND l_OPT4_PERSON_RATE_ID IS NULL ) THEN
1592
1593 If g_debug then
1594 hr_utility.set_location('No Options Exists',120);
1595 end if;
1596
1597 IF(substr(l_DOWNLOAD_SWITCH,1,1) = '2') THEN
1598 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1599 ,P_WS_VAL => P_PL_WS_VAL
1600 ,P_USER_ID => P_USER_ID
1601 ,P_WS_RT_START_DATE =>P_PL_RT_START_DATE );
1602 If g_debug then
1603 hr_utility.set_location('Updated Plan WS Amt Sucessfully',130);
1604 End if;
1605 End If;
1606
1607 -- ER: ability to update other rates
1608 IF(substr(l_DOWNLOAD_SWITCH,39,1) = '2') THEN
1609 update_other_rates(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1610 ,p_interface_seq => g_iterface_seq_type(8,10,11,19,20,21)
1611 ,p_values => g_iterface_seq_type(P_PL_STAT_SAL_VAL,P_PL_OTH_COMP_VAL,P_PL_TOT_COMP_VAL,
1612 P_PL_MISC1_VAL,P_PL_MISC2_VAL,P_PL_MISC3_VAL)
1613 ,p_interface_code => l_interfac_code
1614 ,p_base_layout_code => l_base_layout_code );
1615 If g_debug then
1616 hr_utility.set_location('Updated Plan Other Rates Sucessfully',130);
1617 End if;
1618 End If;
1619
1620 ELSE
1621 -- Update Option Record with Modified Option Worksheet Amount
1622
1623 If g_debug then
1624 hr_utility.set_location('Options Exists',140);
1625 end if;
1626
1627 -- Plan :
1628 -- If Option Rates exists for a Plan
1629 -- and User tries to update Plan WS Val then Raise Error
1630
1631 Open Csr_get_pl_ws_val(l_PL_PERSON_RATE_ID);
1632 Fetch Csr_get_pl_ws_val into l_pl_ws_val;
1633 Close Csr_get_pl_ws_val;
1634
1635 If g_debug then
1636 hr_utility.set_location('l_pl_ws_val :'||l_pl_ws_val,150);
1637 end if;
1638
1639 IF (l_pl_ws_val <> P_PL_WS_VAL) THEN
1640 hr_utility.set_message(805,'BEN_7830_CWB_NOT_UPD_PL_WSVAL');
1641 hr_utility.raise_error;
1642 END IF;
1643
1644 -- Plan level : bug 14022949: update other rates at plan level for plan with options
1645 IF(l_PL_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,39,1) = '2') THEN
1646 update_other_rates(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1647 ,p_interface_seq => g_iterface_seq_type(8,10,11,19,20,21)
1648 ,p_values => g_iterface_seq_type(P_PL_STAT_SAL_VAL,P_PL_OTH_COMP_VAL,P_PL_TOT_COMP_VAL,
1649 P_PL_MISC1_VAL,P_PL_MISC2_VAL,P_PL_MISC3_VAL)
1650 ,p_interface_code => l_interfac_code
1651 ,p_base_layout_code => l_base_layout_code );
1652 If g_debug then
1653 hr_utility.set_location('Updated Plan Other Rates Sucessfully',130);
1654 End if;
1655 End If;
1656
1657 -- Option 1 :
1658 IF (l_OPT1_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,2,1) = '2') THEN
1659 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1660 ,P_WS_VAL => P_OPT1_WS_VAL
1661 ,P_USER_ID => P_USER_ID
1662 ,P_WS_RT_START_DATE => P_OPT1_RT_START_DATE);
1663 if l_pl_person_rate_id is null then
1664 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID);
1665 end if;
1666
1667 IF NVL(l_diff,0) <> 0 THEN -- bug: 8845299
1668 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1669 ,p_add_val => l_diff
1670 ,P_USER_ID => P_USER_ID
1671 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1672 end if;
1673
1674 If g_debug then
1675 hr_utility.set_location('Updated Option1 WS Amt Sucessfully',160);
1676 end if;
1677
1678 END IF;
1679
1680 -- ER: ability to update other rates
1681 IF (l_OPT1_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,40,1) = '2') THEN
1682 update_other_rates(P_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1683 ,p_interface_seq => g_iterface_seq_type(26,28,29,37,38,39)
1684 ,p_values => g_iterface_seq_type(P_OPT1_STAT_SAL_VAL,P_OPT1_OTH_COMP_VAL,P_OPT1_TOT_COMP_VAL,
1685 P_OPT1_MISC1_VAL,P_OPT1_MISC2_VAL,P_OPT1_MISC3_VAL)
1686 ,p_interface_code => l_interfac_code
1687 ,p_base_layout_code => l_base_layout_code );
1688 If g_debug then
1689 hr_utility.set_location('Updated Option 1 Other Rates Sucessfully',130);
1690 End if;
1691 END IF;
1692
1693 -- Option 2 :
1694 IF (l_OPT2_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,3,1) = '2') THEN
1695 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1696 ,P_WS_VAL => P_OPT2_WS_VAL
1697 ,P_USER_ID => P_USER_ID
1698 ,P_WS_RT_START_DATE => P_OPT2_RT_START_DATE);
1699 if l_pl_person_rate_id is null then
1700 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID); end if;
1701
1702 IF NVL(l_diff,0) <> 0 THEN -- bug: 8845299
1703 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1704 ,p_add_val => l_diff
1705 ,P_USER_ID => P_USER_ID
1706 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1707 end if;
1708
1709 If g_debug then
1710 hr_utility.set_location('Updated Option2 WS Amt Sucessfully',170);
1711 end if;
1712
1713 END IF;
1714
1715 -- ER: ability to update other rates
1716 IF (l_OPT1_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,41,1) = '2') THEN
1717 update_other_rates(P_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1718 ,p_interface_seq => g_iterface_seq_type(44,46,47,55,56,57)
1719 ,p_values => g_iterface_seq_type(P_OPT2_STAT_SAL_VAL,P_OPT2_OTH_COMP_VAL,P_OPT2_TOT_COMP_VAL,
1720 P_OPT2_MISC1_VAL,P_OPT2_MISC2_VAL,P_OPT2_MISC3_VAL)
1721 ,p_interface_code => l_interfac_code
1722 ,p_base_layout_code => l_base_layout_code );
1723 If g_debug then
1724 hr_utility.set_location('Updated Option 2 Other Rates Sucessfully',130);
1725 End if;
1726 END IF;
1727
1728 -- Option 3 :
1729 IF (l_OPT3_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,4,1) = '2') THEN
1730 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1731 ,P_WS_VAL => P_OPT3_WS_VAL
1732 ,P_USER_ID => P_USER_ID
1733 ,P_WS_RT_START_DATE => P_OPT3_RT_START_DATE);
1734 if l_pl_person_rate_id is null then
1735 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID); end if;
1736
1737 IF NVL(l_diff,0) <> 0 THEN -- bug: 8845299
1738 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1739 ,p_add_val => l_diff
1740 ,P_USER_ID => P_USER_ID
1741 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1742 end if;
1743
1744 If g_debug then
1745 hr_utility.set_location('Updated Option3 WS Amt Sucessfully',180);
1746 end if;
1747
1748
1749 END IF;
1750
1751 -- ER: ability to update other rates
1752 IF (l_OPT1_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,42,1) = '2') THEN
1753 update_other_rates(P_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1754 ,p_interface_seq => g_iterface_seq_type(62,64,65,73,74,75)
1755 ,p_values => g_iterface_seq_type(P_OPT3_STAT_SAL_VAL,P_OPT3_OTH_COMP_VAL,P_OPT3_TOT_COMP_VAL,
1756 P_OPT3_MISC1_VAL,P_OPT3_MISC2_VAL,P_OPT3_MISC3_VAL)
1757 ,p_interface_code => l_interfac_code
1758 ,p_base_layout_code => l_base_layout_code );
1759 If g_debug then
1760 hr_utility.set_location('Updated Option 3 Other Rates Sucessfully',130);
1761 End if;
1762 END IF;
1763
1764 -- Option 4 :
1765 IF (l_OPT4_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,5,1) = '2') THEN
1766 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
1767 ,P_WS_VAL => P_OPT4_WS_VAL
1768 ,P_USER_ID => P_USER_ID
1769 ,P_WS_RT_START_DATE => P_OPT4_RT_START_DATE);
1770 if l_pl_person_rate_id is null then
1771 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID); end if;
1772
1773 IF NVL(l_diff,0) <> 0 THEN -- bug: 8845299
1774 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1775 ,p_add_val => l_diff
1776 ,P_USER_ID => P_USER_ID
1777 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1778 end if;
1779
1780 If g_debug then
1781 hr_utility.set_location('Updated Option4 WS Amt Sucessfully',190);
1782 end if;
1783
1784
1785 END IF;
1786
1787 -- ER: ability to update other rates
1788 IF (l_OPT1_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,43,1) = '2') THEN
1789 update_other_rates(P_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
1790 ,p_interface_seq => g_iterface_seq_type(80,82,83,91,92,93)
1791 ,p_values => g_iterface_seq_type(P_OPT4_STAT_SAL_VAL,P_OPT4_OTH_COMP_VAL,P_OPT4_TOT_COMP_VAL,
1792 P_OPT4_MISC1_VAL,P_OPT4_MISC2_VAL,P_OPT4_MISC3_VAL)
1793 ,p_interface_code => l_interfac_code
1794 ,p_base_layout_code => l_base_layout_code );
1795 If g_debug then
1796 hr_utility.set_location('Updated Option 4 Other Rates Sucessfully',130);
1797 End if;
1798 END IF;
1799
1800 END IF;
1801
1802 If g_debug then
1803 hr_utility.set_location('Updated Worksheet Amount Sucessfully ',200);
1804 End if;
1805
1806
1807 -- Update Rank
1808
1809
1810 IF(substr(l_DOWNLOAD_SWITCH,7,1) = '2') THEN
1811
1812 UPDATE_RANK(P_GROUP_PER_IN_LER_ID => l_group_per_in_ler_id
1813 ,P_RANK => P_RANK
1814 ,P_USER_ID => l_ACTING_PERSON_ID);
1815 If g_debug then
1816 hr_utility.set_location('Updated Rank Sucessfully ',220);
1817 End if;
1818
1819 END IF;
1820
1821 BEN_CWB_SUMMARY_PKG.save_pl_sql_tab;
1822
1823 If g_debug then
1824 hr_utility.set_location('save_pl_sql_tab Sucessfully ',230);
1825 End if;
1826
1827 -- Proposed Performance Rating
1828
1829 IF(substr(l_DOWNLOAD_SWITCH,6,1) = '2') THEN
1830 update_perf_rating(P_PROPOSED_PERFORMANCE_RATING =>P_PROPOSED_PERFORMANCE_RATING
1831 ,P_ACTING_PERSON_ID => l_ACTING_PERSON_ID
1832 ,P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1833 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1834 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1835 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1836 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID);
1837 If g_debug then
1838 hr_utility.set_location('Updated Proposed Performance Rating Sucessfully ',240);
1839 End if;
1840 END IF;
1841
1842 IF(substr(l_DOWNLOAD_SWITCH,38,1) = '2') THEN
1843 update_promotions(P_PROPOSED_JOB =>P_PROPOSED_JOB
1844 ,P_PROPOSED_POSITION => P_PROPOSED_POSITION
1845 ,P_PROPOSED_GRADE => P_PROPOSED_GRADE
1846 ,P_CHANGE_REASON => P_CHANGE_REASON
1847 ,P_ACTING_PERSON_ID => l_ACTING_PERSON_ID
1848 ,P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1849 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1850 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1851 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1852 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID);
1853
1854 If g_debug then
1855 hr_utility.set_location('Updated Proposed Promotion Sucessfully ',240);
1856 End if;
1857 END IF;
1858
1859 -- Update Person Tasks Table
1860 REFRESH_PERSON_TASKS (P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1861 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1862 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1863 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1864 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
1865 ,P_TASK_ID => l_TASK_ID
1866 ,P_SEC_MGR_LER_ID => l_SEC_MGR_LER_ID );
1867
1868 If g_debug then
1869 hr_utility.set_location('Update Person Tasks Table Sucessfully ',250);
1870 End if;
1871
1872 -- Update the CPI Flex
1873 -- Update CPI Flex
1874 OPEN csr_cpi_flex_info(l_group_per_in_ler_id);
1875 FETCH csr_cpi_flex_info INTO l_cpi_attribute_category,
1876 l_cpi_attribute1,
1877 l_cpi_attribute2,
1878 l_cpi_attribute3,
1879 l_cpi_attribute4,
1880 l_cpi_attribute5,
1881 l_cpi_attribute6,
1882 l_cpi_attribute7,
1883 l_cpi_attribute8,
1884 l_cpi_attribute9,
1885 l_cpi_attribute10,
1886 l_cpi_attribute11,
1887 l_cpi_attribute12,
1888 l_cpi_attribute13,
1889 l_cpi_attribute14,
1890 l_cpi_attribute15,
1891 l_cpi_attribute16,
1892 l_cpi_attribute17,
1893 l_cpi_attribute18,
1894 l_cpi_attribute19,
1895 l_cpi_attribute20,
1896 l_cpi_attribute21,
1897 l_cpi_attribute22,
1898 l_cpi_attribute23,
1899 l_cpi_attribute24,
1900 l_cpi_attribute25,
1901 l_cpi_attribute26,
1902 l_cpi_attribute27,
1903 l_cpi_attribute28,
1904 l_cpi_attribute29,
1905 l_cpi_attribute30,
1906 l_custom_segment1,
1907 l_custom_segment2,
1908 l_custom_segment3,
1909 l_custom_segment4,
1910 l_custom_segment5,
1911 l_custom_segment6,
1912 l_custom_segment7,
1913 l_custom_segment8,
1914 l_custom_segment9,
1915 l_custom_segment10,
1916 l_custom_segment11,
1917 l_custom_segment12,
1918 l_custom_segment13,
1919 l_custom_segment14,
1920 l_custom_segment15,
1921 l_custom_segment16,
1922 l_custom_segment17,
1923 l_custom_segment18,
1924 l_custom_segment19,
1925 l_custom_segment20,
1926 l_ovn;
1927 CLOSE csr_cpi_flex_info;
1928
1929
1930 FOR l_is_read_only in csr_is_read_only(l_interfac_code,l_base_layout_code) LOOP
1931
1932 IF(l_is_read_only.interface_seq_num = '136' AND l_is_read_only.read_only = 'N') THEN
1933 l_custom_segment1 := check_varchar_col_avble(l_custom_segment1 ,p_custom_segment1);
1934 END IF;
1935 IF(l_is_read_only.interface_seq_num = '137' AND l_is_read_only.read_only = 'N') THEN
1936 l_custom_segment2 := check_varchar_col_avble(l_custom_segment2 ,p_custom_segment2);
1937 END IF;
1938 IF(l_is_read_only.interface_seq_num = '138' AND l_is_read_only.read_only = 'N') THEN
1939 l_custom_segment3 := check_varchar_col_avble(l_custom_segment3 ,p_custom_segment3);
1940 END IF;
1941 IF(l_is_read_only.interface_seq_num = '139' AND l_is_read_only.read_only = 'N') THEN
1942 l_custom_segment4 := check_varchar_col_avble(l_custom_segment4 ,p_custom_segment4);
1943 END IF;
1944 IF(l_is_read_only.interface_seq_num = '140' AND l_is_read_only.read_only = 'N') THEN
1945 l_custom_segment5 := check_varchar_col_avble(l_custom_segment5 ,p_custom_segment5);
1946 END IF;
1947 IF(l_is_read_only.interface_seq_num = '141' AND l_is_read_only.read_only = 'N') THEN
1948 l_custom_segment6 := check_varchar_col_avble(l_custom_segment6 ,p_custom_segment6);
1949 END IF;
1950 IF(l_is_read_only.interface_seq_num = '142' AND l_is_read_only.read_only = 'N') THEN
1951 l_custom_segment7 := check_varchar_col_avble(l_custom_segment7 ,p_custom_segment7);
1952 END IF;
1953 IF(l_is_read_only.interface_seq_num = '143' AND l_is_read_only.read_only = 'N') THEN
1954 l_custom_segment8 := check_varchar_col_avble(l_custom_segment8 ,p_custom_segment8);
1955 END IF;
1956 IF(l_is_read_only.interface_seq_num = '144' AND l_is_read_only.read_only = 'N') THEN
1957 l_custom_segment9 := check_varchar_col_avble(l_custom_segment9 ,p_custom_segment9);
1958 END IF;
1959 IF(l_is_read_only.interface_seq_num = '145' AND l_is_read_only.read_only = 'N') THEN
1960 l_custom_segment10 := check_varchar_col_avble(l_custom_segment10,p_custom_segment10);
1961 END IF;
1962 IF(l_is_read_only.interface_seq_num = '146' AND l_is_read_only.read_only = 'N') THEN
1963 l_custom_segment11 := check_number_col_avble(l_custom_segment11,p_custom_segment11);
1964 END IF;
1965 IF(l_is_read_only.interface_seq_num = '147' AND l_is_read_only.read_only = 'N') THEN
1966 l_custom_segment12 := check_number_col_avble(l_custom_segment12,p_custom_segment12);
1967 END IF;
1968 IF(l_is_read_only.interface_seq_num = '148' AND l_is_read_only.read_only = 'N') THEN
1969 l_custom_segment13 := check_number_col_avble(l_custom_segment13,p_custom_segment13);
1970 END IF;
1971 IF(l_is_read_only.interface_seq_num = '149' AND l_is_read_only.read_only = 'N') THEN
1972 l_custom_segment14 := check_number_col_avble(l_custom_segment14,p_custom_segment14);
1973 END IF;
1974 IF(l_is_read_only.interface_seq_num = '150' AND l_is_read_only.read_only = 'N') THEN
1975 l_custom_segment15 := check_number_col_avble(l_custom_segment15,p_custom_segment15);
1976 END IF;
1977 IF(l_is_read_only.interface_seq_num = '230' AND l_is_read_only.read_only = 'N') THEN
1978 l_custom_segment16 := check_number_col_avble(l_custom_segment16,p_custom_segment16);
1979 END IF;
1980 IF(l_is_read_only.interface_seq_num = '231' AND l_is_read_only.read_only = 'N') THEN
1981 l_custom_segment17 := check_number_col_avble(l_custom_segment17,p_custom_segment17);
1982 END IF;
1983 IF(l_is_read_only.interface_seq_num = '232' AND l_is_read_only.read_only = 'N') THEN
1984 l_custom_segment18 := check_number_col_avble(l_custom_segment18,p_custom_segment18);
1985 END IF;
1986 IF(l_is_read_only.interface_seq_num = '233' AND l_is_read_only.read_only = 'N') THEN
1987 l_custom_segment19 := check_number_col_avble(l_custom_segment19,p_custom_segment19);
1988 END IF;
1989 IF(l_is_read_only.interface_seq_num = '234' AND l_is_read_only.read_only = 'N') THEN
1990 l_custom_segment20 := check_number_col_avble(l_custom_segment20,p_custom_segment20);
1991 END IF;
1992
1993 IF(l_is_read_only.interface_seq_num = '200' AND l_is_read_only.read_only = 'N') THEN
1994 IF(substr(l_DOWNLOAD_SWITCH,8,1) = '2') THEN
1995 l_cpi_attribute1 := check_varchar_col_avble(l_cpi_attribute1 ,p_cpi_attribute1);
1996 If g_debug then
1997 hr_utility.set_location('Upload CPI Attribute1',251);
1998 End if;
1999 END IF;
2000 END IF;
2001 IF(l_is_read_only.interface_seq_num = '201' AND l_is_read_only.read_only = 'N') THEN
2002 IF(substr(l_DOWNLOAD_SWITCH,9,1) = '2') THEN
2003 l_cpi_attribute2 := check_varchar_col_avble(l_cpi_attribute2 ,p_cpi_attribute2);
2004 If g_debug then
2005 hr_utility.set_location('Upload CPI Attribute2',252);
2006 End if;
2007 END IF;
2008 END IF;
2009 IF(l_is_read_only.interface_seq_num = '202' AND l_is_read_only.read_only = 'N') THEN
2010 IF(substr(l_DOWNLOAD_SWITCH,10,1) = '2') THEN
2011 l_cpi_attribute3 := check_varchar_col_avble(l_cpi_attribute3 ,p_cpi_attribute3);
2012 If g_debug then
2013 hr_utility.set_location('Upload CPI Attribute3',253);
2014 End if;
2015 END IF;
2016 END IF;
2017 IF(l_is_read_only.interface_seq_num = '203' AND l_is_read_only.read_only = 'N') THEN
2018 IF(substr(l_DOWNLOAD_SWITCH,11,1) = '2') THEN
2019 l_cpi_attribute4 := check_varchar_col_avble(l_cpi_attribute4 ,p_cpi_attribute4);
2020 If g_debug then
2021 hr_utility.set_location('Upload CPI Attribute4',254);
2022 End if;
2023 END IF;
2024 END IF;
2025 IF(l_is_read_only.interface_seq_num = '204' AND l_is_read_only.read_only = 'N') THEN
2026 IF(substr(l_DOWNLOAD_SWITCH,12,1) = '2') THEN
2027 l_cpi_attribute5 := check_varchar_col_avble(l_cpi_attribute5 ,p_cpi_attribute5);
2028 If g_debug then
2029 hr_utility.set_location('Upload CPI Attribute5',255);
2030 End if;
2031 END IF;
2032 END IF;
2033 IF(l_is_read_only.interface_seq_num = '205' AND l_is_read_only.read_only = 'N') THEN
2034 IF(substr(l_DOWNLOAD_SWITCH,13,1) = '2') THEN
2035 l_cpi_attribute6 := check_varchar_col_avble(l_cpi_attribute6 ,p_cpi_attribute6);
2036 If g_debug then
2037 hr_utility.set_location('Upload CPI Attribute6',256);
2038 End if;
2039 END IF;
2040 END IF;
2041 IF(l_is_read_only.interface_seq_num = '206' AND l_is_read_only.read_only = 'N') THEN
2042 IF(substr(l_DOWNLOAD_SWITCH,14,1) = '2') THEN
2043 l_cpi_attribute7 := check_varchar_col_avble(l_cpi_attribute7 ,p_cpi_attribute7);
2044 If g_debug then
2045 hr_utility.set_location('Upload CPI Attribute7',257);
2046 End if;
2047 END IF;
2048 END IF;
2049 IF(l_is_read_only.interface_seq_num = '207' AND l_is_read_only.read_only = 'N') THEN
2050 IF(substr(l_DOWNLOAD_SWITCH,15,1) = '2') THEN
2051 l_cpi_attribute8 := check_varchar_col_avble(l_cpi_attribute8 ,p_cpi_attribute8);
2052 If g_debug then
2053 hr_utility.set_location('Upload CPI Attribute8',258);
2054 End if;
2055 END IF;
2056 END IF;
2057 IF(l_is_read_only.interface_seq_num = '208' AND l_is_read_only.read_only = 'N') THEN
2058 IF(substr(l_DOWNLOAD_SWITCH,16,1) = '2') THEN
2059 l_cpi_attribute9 := check_varchar_col_avble(l_cpi_attribute9 ,p_cpi_attribute9);
2060 If g_debug then
2061 hr_utility.set_location('Upload CPI Attribute9',259);
2062 End if;
2063 END IF;
2064 END IF;
2065 IF(l_is_read_only.interface_seq_num = '209' AND l_is_read_only.read_only = 'N') THEN
2066 IF(substr(l_DOWNLOAD_SWITCH,17,1) = '2') THEN
2067 l_cpi_attribute10 := check_varchar_col_avble(l_cpi_attribute10 ,p_cpi_attribute10);
2068 If g_debug then
2069 hr_utility.set_location('Upload CPI Attribute10',260);
2070 End if;
2071 END IF;
2072 END IF;
2073 IF(l_is_read_only.interface_seq_num = '210' AND l_is_read_only.read_only = 'N') THEN
2074 IF(substr(l_DOWNLOAD_SWITCH,18,1) = '2') THEN
2075 l_cpi_attribute11 := check_varchar_col_avble(l_cpi_attribute11 ,p_cpi_attribute11);
2076 If g_debug then
2077 hr_utility.set_location('Upload CPI Attribute11',261);
2078 End if;
2079 END IF;
2080 END IF;
2081 IF(l_is_read_only.interface_seq_num = '211' AND l_is_read_only.read_only = 'N') THEN
2082 IF(substr(l_DOWNLOAD_SWITCH,19,1) = '2') THEN
2083 l_cpi_attribute12 := check_varchar_col_avble(l_cpi_attribute12 ,p_cpi_attribute12);
2084 If g_debug then
2085 hr_utility.set_location('Upload CPI Attribute12',262);
2086 End if;
2087 END IF;
2088 END IF;
2089 IF(l_is_read_only.interface_seq_num = '212' AND l_is_read_only.read_only = 'N') THEN
2090 IF(substr(l_DOWNLOAD_SWITCH,20,1) = '2') THEN
2091 l_cpi_attribute13 := check_varchar_col_avble(l_cpi_attribute13 ,p_cpi_attribute13);
2092 If g_debug then
2093 hr_utility.set_location('Upload CPI Attribute13',263);
2094 End if;
2095 END IF;
2096 END IF;
2097 IF(l_is_read_only.interface_seq_num = '213' AND l_is_read_only.read_only = 'N') THEN
2098 IF(substr(l_DOWNLOAD_SWITCH,21,1) = '2') THEN
2099 l_cpi_attribute14 := check_varchar_col_avble(l_cpi_attribute14 ,p_cpi_attribute14);
2100 If g_debug then
2101 hr_utility.set_location('Upload CPI Attribute14',264);
2102 End if;
2103 END IF;
2104 END IF;
2105 IF(l_is_read_only.interface_seq_num = '214' AND l_is_read_only.read_only = 'N') THEN
2106 IF(substr(l_DOWNLOAD_SWITCH,22,1) = '2') THEN
2107 l_cpi_attribute15 := check_varchar_col_avble(l_cpi_attribute15 ,p_cpi_attribute15);
2108 If g_debug then
2109 hr_utility.set_location('Upload CPI Attribute15',265);
2110 End if;
2111 END IF;
2112 END IF;
2113 IF(l_is_read_only.interface_seq_num = '215' AND l_is_read_only.read_only = 'N') THEN
2114 IF(substr(l_DOWNLOAD_SWITCH,23,1) = '2') THEN
2115 l_cpi_attribute16 := check_varchar_col_avble(l_cpi_attribute16 ,p_cpi_attribute16);
2116 If g_debug then
2117 hr_utility.set_location('Upload CPI Attribute16',266);
2118 End if;
2119 END IF;
2120 END IF;
2121 IF(l_is_read_only.interface_seq_num = '216' AND l_is_read_only.read_only = 'N') THEN
2122 IF(substr(l_DOWNLOAD_SWITCH,24,1) = '2') THEN
2123 l_cpi_attribute17 := check_varchar_col_avble(l_cpi_attribute17 ,p_cpi_attribute17);
2124 If g_debug then
2125 hr_utility.set_location('Upload CPI Attribute17',267);
2126 End if;
2127 END IF;
2128 END IF;
2129 IF(l_is_read_only.interface_seq_num = '217' AND l_is_read_only.read_only = 'N') THEN
2130 IF(substr(l_DOWNLOAD_SWITCH,25,1) = '2') THEN
2131 l_cpi_attribute18 := check_varchar_col_avble(l_cpi_attribute18 ,p_cpi_attribute18);
2132 If g_debug then
2133 hr_utility.set_location('Upload CPI Attribute18',268);
2134 End if;
2135 END IF;
2136 END IF;
2137 IF(l_is_read_only.interface_seq_num = '218' AND l_is_read_only.read_only = 'N') THEN
2138 IF(substr(l_DOWNLOAD_SWITCH,26,1) = '2') THEN
2139 l_cpi_attribute19 := check_varchar_col_avble(l_cpi_attribute19 ,p_cpi_attribute19);
2140 If g_debug then
2141 hr_utility.set_location('Upload CPI Attribute19',269);
2142 End if;
2143 END IF;
2144 END IF;
2145 IF(l_is_read_only.interface_seq_num = '219' AND l_is_read_only.read_only = 'N') THEN
2146 IF(substr(l_DOWNLOAD_SWITCH,27,1) = '2') THEN
2147 l_cpi_attribute20 := check_varchar_col_avble(l_cpi_attribute20 ,p_cpi_attribute20);
2148 If g_debug then
2149 hr_utility.set_location('Upload CPI Attribute20',270);
2150 End if;
2151 END IF;
2152 END IF;
2153 IF(l_is_read_only.interface_seq_num = '220' AND l_is_read_only.read_only = 'N') THEN
2154 IF(substr(l_DOWNLOAD_SWITCH,28,1) = '2') THEN
2155 l_cpi_attribute21 := check_varchar_col_avble(l_cpi_attribute21 ,p_cpi_attribute21);
2156 If g_debug then
2157 hr_utility.set_location('Upload CPI Attribute21',271);
2158 End if;
2159 END IF;
2160 END IF;
2161 IF(l_is_read_only.interface_seq_num = '221' AND l_is_read_only.read_only = 'N') THEN
2162 IF(substr(l_DOWNLOAD_SWITCH,29,1) = '2') THEN
2163 l_cpi_attribute22 := check_varchar_col_avble(l_cpi_attribute22 ,p_cpi_attribute22);
2164 If g_debug then
2165 hr_utility.set_location('Upload CPI Attribute22',272);
2166 End if;
2167 END IF;
2168 END IF;
2169 IF(l_is_read_only.interface_seq_num = '222' AND l_is_read_only.read_only = 'N') THEN
2170 IF(substr(l_DOWNLOAD_SWITCH,30,1) = '2') THEN
2171 l_cpi_attribute23 := check_varchar_col_avble(l_cpi_attribute23 ,p_cpi_attribute23);
2172 If g_debug then
2173 hr_utility.set_location('Upload CPI Attribute23',273);
2174 End if;
2175 END IF;
2176 END IF;
2177 IF(l_is_read_only.interface_seq_num = '223' AND l_is_read_only.read_only = 'N') THEN
2178 IF(substr(l_DOWNLOAD_SWITCH,31,1) = '2') THEN
2179 l_cpi_attribute24 := check_varchar_col_avble(l_cpi_attribute24 ,p_cpi_attribute24);
2180 If g_debug then
2181 hr_utility.set_location('Upload CPI Attribute24',274);
2182 End if;
2183 END IF;
2184 END IF;
2185 IF(l_is_read_only.interface_seq_num = '224' AND l_is_read_only.read_only = 'N') THEN
2186 IF(substr(l_DOWNLOAD_SWITCH,32,1) = '2') THEN
2187 l_cpi_attribute25 := check_varchar_col_avble(l_cpi_attribute25 ,p_cpi_attribute25);
2188 If g_debug then
2189 hr_utility.set_location('Upload CPI Attribute25',275);
2190 End if;
2191 END IF;
2192 END IF;
2193 IF(l_is_read_only.interface_seq_num = '225' AND l_is_read_only.read_only = 'N') THEN
2194 IF(substr(l_DOWNLOAD_SWITCH,33,1) = '2') THEN
2195 l_cpi_attribute26 := check_varchar_col_avble(l_cpi_attribute26 ,p_cpi_attribute26);
2196 If g_debug then
2197 hr_utility.set_location('Upload CPI Attribute26',276);
2198 End if;
2199 END IF;
2200 END IF;
2201 IF(l_is_read_only.interface_seq_num = '226' AND l_is_read_only.read_only = 'N') THEN
2202 IF(substr(l_DOWNLOAD_SWITCH,34,1) = '2') THEN
2203 l_cpi_attribute27 := check_varchar_col_avble(l_cpi_attribute27 ,p_cpi_attribute27);
2204 If g_debug then
2205 hr_utility.set_location('Upload CPI Attribute27',277);
2206 End if;
2207 END IF;
2208 END IF;
2209 IF(l_is_read_only.interface_seq_num = '227' AND l_is_read_only.read_only = 'N') THEN
2210 IF(substr(l_DOWNLOAD_SWITCH,35,1) = '2') THEN
2211 l_cpi_attribute28 := check_varchar_col_avble(l_cpi_attribute28 ,p_cpi_attribute28);
2212 If g_debug then
2213 hr_utility.set_location('Upload CPI Attribute28',278);
2214 End if;
2215 END IF;
2216 END IF;
2217 IF(l_is_read_only.interface_seq_num = '228' AND l_is_read_only.read_only = 'N') THEN
2218 IF(substr(l_DOWNLOAD_SWITCH,36,1) = '2') THEN
2219 l_cpi_attribute29 := check_varchar_col_avble(l_cpi_attribute29 ,p_cpi_attribute29);
2220 If g_debug then
2221 hr_utility.set_location('Upload CPI Attribute29',279);
2222 End if;
2223 END IF;
2224 END IF;
2225 IF(l_is_read_only.interface_seq_num = '229' AND l_is_read_only.read_only = 'N') THEN
2226 IF(substr(l_DOWNLOAD_SWITCH,37,1) = '2') THEN
2227 l_cpi_attribute30 := check_varchar_col_avble(l_cpi_attribute30 ,p_cpi_attribute30);
2228 If g_debug then
2229 hr_utility.set_location('Upload CPI Attribute30',280);
2230 End if;
2231 END IF;
2232 END IF;
2233
2234 END LOOP;
2235
2236 BEN_CWB_PERSON_INFO_API.update_person_info
2237 ( P_GROUP_PER_IN_LER_ID => l_GROUP_PER_IN_LER_ID
2238 ,P_CPI_ATTRIBUTE_CATEGORY => P_CPI_ATTRIBUTE_CATEGORY
2239 ,P_CPI_ATTRIBUTE1 => L_CPI_ATTRIBUTE1
2240 ,P_CPI_ATTRIBUTE2 => L_CPI_ATTRIBUTE2
2241 ,P_CPI_ATTRIBUTE3 => l_CPI_ATTRIBUTE3
2242 ,P_CPI_ATTRIBUTE4 => l_CPI_ATTRIBUTE4
2243 ,P_CPI_ATTRIBUTE5 => l_CPI_ATTRIBUTE5
2244 ,P_CPI_ATTRIBUTE6 => l_CPI_ATTRIBUTE6
2245 ,P_CPI_ATTRIBUTE7 => l_CPI_ATTRIBUTE7
2246 ,P_CPI_ATTRIBUTE8 => l_CPI_ATTRIBUTE8
2247 ,P_CPI_ATTRIBUTE9 => l_CPI_ATTRIBUTE9
2248 ,P_CPI_ATTRIBUTE10 => l_CPI_ATTRIBUTE10
2249 ,P_CPI_ATTRIBUTE11 => l_CPI_ATTRIBUTE11
2250 ,P_CPI_ATTRIBUTE12 => l_CPI_ATTRIBUTE12
2251 ,P_CPI_ATTRIBUTE13 => l_CPI_ATTRIBUTE13
2252 ,P_CPI_ATTRIBUTE14 => l_CPI_ATTRIBUTE14
2253 ,P_CPI_ATTRIBUTE15 => l_CPI_ATTRIBUTE15
2254 ,P_CPI_ATTRIBUTE16 => l_CPI_ATTRIBUTE16
2255 ,P_CPI_ATTRIBUTE17 => l_CPI_ATTRIBUTE17
2256 ,P_CPI_ATTRIBUTE18 => l_CPI_ATTRIBUTE18
2257 ,P_CPI_ATTRIBUTE19 => l_CPI_ATTRIBUTE19
2258 ,P_CPI_ATTRIBUTE20 => l_CPI_ATTRIBUTE20
2259 ,P_CPI_ATTRIBUTE21 => l_CPI_ATTRIBUTE21
2260 ,P_CPI_ATTRIBUTE22 => l_CPI_ATTRIBUTE22
2261 ,P_CPI_ATTRIBUTE23 => l_CPI_ATTRIBUTE23
2262 ,P_CPI_ATTRIBUTE24 => l_CPI_ATTRIBUTE24
2263 ,P_CPI_ATTRIBUTE25 => l_CPI_ATTRIBUTE25
2264 ,P_CPI_ATTRIBUTE26 => l_CPI_ATTRIBUTE26
2265 ,P_CPI_ATTRIBUTE27 => l_CPI_ATTRIBUTE27
2266 ,P_CPI_ATTRIBUTE28 => l_CPI_ATTRIBUTE28
2267 ,P_CPI_ATTRIBUTE29 => l_CPI_ATTRIBUTE29
2268 ,P_CPI_ATTRIBUTE30 => l_CPI_ATTRIBUTE30
2269 ,P_CUSTOM_SEGMENT1 => l_CUSTOM_SEGMENT1
2270 ,P_CUSTOM_SEGMENT2 => l_CUSTOM_SEGMENT2
2271 ,P_CUSTOM_SEGMENT3 => l_CUSTOM_SEGMENT3
2272 ,P_CUSTOM_SEGMENT4 => l_CUSTOM_SEGMENT4
2273 ,P_CUSTOM_SEGMENT5 => l_CUSTOM_SEGMENT5
2274 ,P_CUSTOM_SEGMENT6 => l_CUSTOM_SEGMENT6
2275 ,P_CUSTOM_SEGMENT7 => l_CUSTOM_SEGMENT7
2276 ,P_CUSTOM_SEGMENT8 => l_CUSTOM_SEGMENT8
2277 ,P_CUSTOM_SEGMENT9 => l_CUSTOM_SEGMENT9
2278 ,P_CUSTOM_SEGMENT10 => l_CUSTOM_SEGMENT10
2279 ,P_CUSTOM_SEGMENT11 => l_CUSTOM_SEGMENT11
2280 ,P_CUSTOM_SEGMENT12 => l_CUSTOM_SEGMENT12
2281 ,P_CUSTOM_SEGMENT13 => l_CUSTOM_SEGMENT13
2282 ,P_CUSTOM_SEGMENT14 => l_CUSTOM_SEGMENT14
2283 ,P_CUSTOM_SEGMENT15 => l_CUSTOM_SEGMENT15
2284 ,P_CUSTOM_SEGMENT16 => l_CUSTOM_SEGMENT16
2285 ,P_CUSTOM_SEGMENT17 => l_CUSTOM_SEGMENT17
2286 ,P_CUSTOM_SEGMENT18 => l_CUSTOM_SEGMENT18
2287 ,P_CUSTOM_SEGMENT19 => l_CUSTOM_SEGMENT19
2288 ,P_CUSTOM_SEGMENT20 => l_CUSTOM_SEGMENT20
2289 ,P_OBJECT_VERSION_NUMBER => L_OVN);
2290 If g_debug then
2291 hr_utility.set_location('Updated CPI Flex Rating Sucessfully ',281);
2292 End if;
2293
2294 --
2295 -- Call the routine for dynamic calculations.
2296 --
2297 open csr_get_group_plan_info(l_group_per_in_ler_id);
2298 fetch csr_get_group_plan_info into l_group_pl_id, l_lf_evt_ocrd_dt;
2299 close csr_get_group_plan_info;
2300 --
2301 ben_cwb_dyn_calc_pkg.run_dynamic_calculations(
2302 p_group_per_in_ler_id => l_group_per_in_ler_id
2303 ,p_group_pl_id => l_group_pl_id
2304 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2305 ,p_raise_error => true);
2306 --
2307
2308 If g_debug then
2309 hr_utility.set_location('Leaving '||l_proc,290);
2310 End if;
2311
2312 EXCEPTION
2313 WHEN Others THEN
2314 if g_debug then
2315 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 300);
2316 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 301);
2317 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 302);
2318 end if;
2319
2320 Rollback to Update_data;
2321
2322 END handle_row;
2323
2324 FUNCTION check_varchar_col_avble(old_val varchar2, new_val varchar2)
2325 return varchar2
2326 is
2327 begin
2328 if nvl(new_val, 'X') <> default_string then
2329 return new_val;
2330 else
2331 return old_val;
2332 end if;
2333 end check_varchar_col_avble;
2334
2335 FUNCTION check_number_col_avble(old_val number, new_val number)
2336 return number
2337 is
2338 begin
2339 if nvl(new_val, 1) <> default_number then
2340 return new_val;
2341 else
2342 return old_val;
2343 end if;
2344 end check_number_col_avble;
2345
2346 FUNCTION round_number(p_value in number,
2347 p_rndg_cd in varchar2)
2348 return number is
2349 begin
2350 return benutils.do_rounding(p_rounding_cd => p_rndg_cd,
2351 p_rounding_rl => null,
2352 p_assignment_id => null,
2353 p_value => to_number(p_value),
2354 p_effective_date => null);
2355 end round_number;
2356
2357
2358 END BEN_CWB_WS_IMPORT_PKG;
2359