[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.13.12010000.2 2008/08/05 14:38:11 ubhat ship $ */
3
4 g_package Varchar2(30) := 'BEN_CWB_WS_IMPORT_PKG.';
5 g_debug boolean := hr_utility.debug_enabled;
6
7 /*
8 This procedure is used by worksheet when rank is updated to
9 prevent any duplicates getting created.
10 */
11 procedure insert_new_rank
12 (p_assignment_id in number
13 ,p_rank in number
14 ,p_rank_by_person_id in number
15 ,p_level_number in number
16 ,p_assignment_extra_info_id out nocopy number
17 ,p_object_version_number out nocopy number) is
18 Cursor Csr_EIT_Dtls IS
19 SELECT 'Y'
20 FROM PER_ASSIGNMENT_EXTRA_INFO ASS_EIT
21 WHERE ASS_EIT.INFORMATION_TYPE = 'CWBRANK'
22 AND ASS_EIT.ASSIGNMENT_ID = p_assignment_Id
23 AND ASS_EIT.AEI_INFORMATION2 = p_rank_by_person_id
24 AND ASS_EIT.AEI_INFORMATION5 IS NULL
25 AND ASS_EIT.AEI_INFORMATION6 IS NULL;
26 l_exists char(1) := 'N';
27 begin
28 --
29 open Csr_EIT_Dtls;
30 fetch Csr_EIT_Dtls into l_exists;
31 close Csr_EIT_Dtls;
32
33 if l_exists = 'N' then
34 hr_assignment_extra_info_api.create_assignment_extra_info
35 (p_assignment_id => p_assignment_Id
36 ,p_information_type => 'CWBRANK'
37 ,p_aei_information_category => 'CWBRANK'
38 ,p_aei_information1 => p_rank
39 ,p_aei_information2 => p_rank_by_person_id
40 ,p_aei_information4 => p_level_number
41 ,p_assignment_extra_info_id => p_assignment_extra_info_id
42 ,p_object_version_number => p_object_version_number);
43 else
44 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
45 hr_utility.raise_error;
46 end if;
47
48 end insert_new_rank;
49 --
50 --------------------------- UPDATE_RANK -----------------------------
51 --
52
53 PROCEDURE UPDATE_RANK(P_GROUP_PER_IN_LER_ID IN NUMBER
54 ,P_RANK IN NUMBER
55 ,P_USER_ID IN VARCHAR2) IS
56
57 Cursor Csr_Assignment_ID IS
58 Select pil_emp.assignment_id
59 ,mgr.lvl_num
60 From ben_per_in_ler pil_emp
61 ,ben_cwb_group_hrchy mgr
62 ,ben_per_in_ler pil_mgr
63 Where pil_emp.per_in_ler_id = p_group_per_in_ler_id
64 and pil_emp.per_in_ler_id = mgr.emp_per_in_ler_id
65 and mgr.lvl_num > 0
66 and mgr.mgr_per_in_ler_id = pil_mgr.per_in_ler_id
67 and pil_mgr.person_id = p_user_id;
68
69 Cursor Csr_EIT_Dtls(l_assignment_Id IN NUMBER )
70 IS
71 SELECT ASS_EIT.ASSIGNMENT_EXTRA_INFO_ID ,
72 ASS_EIT.OBJECT_VERSION_NUMBER
73 FROM PER_ASSIGNMENT_EXTRA_INFO ASS_EIT
74 WHERE ASS_EIT.INFORMATION_TYPE = 'CWBRANK'
75 AND ASS_EIT.ASSIGNMENT_ID = l_assignment_Id
76 AND ASS_EIT.AEI_INFORMATION2 = P_USER_ID
77 AND ASS_EIT.AEI_INFORMATION5 IS NULL
78 AND ASS_EIT.AEI_INFORMATION6 IS NULL;
79
80
81
82 l_proc varchar2(72) := g_package||'UPDATE_RANK';
83 l_assignment_Id Number;
84 l_lvl_num number;
85 l_ovn Number;
86 l_assignment_extra_info_id Number;
87
88 BEGIN
89
90 if g_debug then
91 hr_utility.set_location('Entering '||l_proc,10);
92 hr_utility.set_location('P_GROUP_PER_IN_LER_ID '||P_GROUP_PER_IN_LER_ID,20);
93 hr_utility.set_location('P_RANK '||P_RANK,30);
94 hr_utility.set_location('P_USER_ID '||P_USER_ID,35);
95 end if;
96
97 Open Csr_Assignment_ID;
98 Fetch Csr_Assignment_ID into l_assignment_Id, l_lvl_num;
99 Close Csr_Assignment_ID;
100
101 if g_debug then
102 hr_utility.set_location('l_assignment_Id '||l_assignment_Id,70);
103 end if;
104
105
106 IF (l_assignment_Id IS NOT NULL) THEN
107 --
108 open Csr_EIT_Dtls(l_assignment_Id);
109 fetch Csr_EIT_Dtls into l_assignment_extra_info_id, l_ovn;
110 close Csr_EIT_Dtls;
111
112 if l_assignment_extra_info_id is not null then
113 hr_assignment_extra_info_api.update_assignment_extra_info
114 (p_assignment_extra_info_id => l_assignment_extra_info_id
115 ,p_object_version_number => l_ovn
116 ,p_aei_information_category => 'CWBRANK'
117 ,p_aei_information1 => P_RANK
118 ,p_aei_information4 => l_lvl_num);
119 else
120 hr_assignment_extra_info_api.create_assignment_extra_info
121 (p_assignment_id => l_assignment_Id
122 ,p_information_type => 'CWBRANK'
123 ,p_aei_information_category => 'CWBRANK'
124 ,p_aei_information1 => P_RANK
125 ,p_aei_information2 => P_USER_ID
126 ,p_aei_information4 => l_lvl_num
127 ,p_assignment_extra_info_id => l_assignment_extra_info_id
128 ,p_object_version_number => l_ovn);
129 end if;
130
131 END IF;
132
133 if g_debug then
134 hr_utility.set_location('Leaving '||l_proc,100);
135 end if;
136
137 END UPDATE_RANK;
138
139
140 --
141 --------------------------- UPDATE_WS_AMOUNT -----------------------------
142 --
143
144 function UPDATE_WS_AMOUNT (P_PERSON_RATE_ID IN NUMBER
145 ,P_WS_VAL IN NUMBER default null
146 ,p_add_val in number default null
147 ,P_USER_ID IN VARCHAR2
148 ,P_WS_RT_START_DATE IN DATE DEFAULT NULL)
149 return number
150 IS
151
152 Cursor Csr_PlRt_Dtls
153 IS
154 Select PlRt.GROUP_PER_IN_LER_ID GROUP_PER_IN_LER_ID,
155 PlRt.PL_ID PL_ID,
156 PlRt.OIPL_ID OIPL_ID,
157 PlRt.GROUP_PL_ID GROUP_PL_ID,
158 PlRt.GROUP_OIPL_ID GROUP_OIPL_ID,
159 PlRt.LF_EVT_OCRD_DT LF_EVT_OCRD_DT,
160 PlRt.Object_Version_Number OVN,
161 PlRt.ws_val ws_val,
162 plrt.ws_rt_start_date WS_RT_START_DATE
163 From BEN_CWB_PERSON_RATES PlRt
164 ,ben_cwb_pl_dsgn dsgn
165 Where PlRt.PERSON_RATE_ID = P_PERSON_RATE_ID
166 And PlRt.ELIG_FLAG='Y'
167 and plRt.pl_id = dsgn.pl_id
168 and plRt.oipl_id = dsgn.oipl_id
169 and plRt.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
170 and dsgn.ws_abr_id is not null;
171
172 Cursor Csr_Person_Id(l_USER_ID IN NUMBER)
173 IS
174 Select EMPLOYEE_ID
175 From FND_USER
176 Where User_Id = l_USER_ID;
177
178 l_upd_rec Csr_PlRt_Dtls%RowType;
179 l_ovn Number;
180 l_proc varchar2(72) := g_package||'UPDATE_WS_AMOUNT';
181 l_USER_ID Number;
182 l_person_id Number;
183 l_diff number;
184 l_ws_val number := p_ws_val;
185 l_ws_rt_start_date DATE;
186 BEGIN
187
188 if g_debug then
189 hr_utility.set_location('Entering '||l_proc,10);
190 hr_utility.set_location('P_PERSON_RATE_ID '||P_PERSON_RATE_ID,20);
191 hr_utility.set_location('P_WS_VAL '||l_WS_VAL,30);
192 hr_utility.set_location('P_USER_ID '||P_USER_ID,36);
193 end if;
194
195 IF (P_USER_ID IS NOT NULL) THEN
196 l_USER_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_USER_ID);
197 hr_utility.set_location('l_USER_ID :'||l_USER_ID,45);
198 END IF;
199
200 Open Csr_Person_Id(l_USER_ID);
201 Fetch Csr_Person_Id into l_person_id;
202 Close Csr_Person_Id;
203
204 Open Csr_PlRt_Dtls;
205 Fetch Csr_PlRt_Dtls into l_upd_rec;
206
207 If Csr_PlRt_Dtls%Found Then
208 l_ovn := l_upd_rec.OVN;
209
210 if p_add_val is not null then
211 l_ws_val := ben_cwb_utils.add_number_with_null_check
212 (l_upd_rec.ws_val, p_add_val);
213 end if;
214 l_diff := nvl(l_ws_val,0) - nvl(l_upd_rec.ws_val,0);
215 --added by KMG
216 IF p_ws_rt_start_date = default_date THEN
217 l_ws_rt_start_date := l_upd_rec.ws_rt_start_date;
218 ELSE
219 l_ws_rt_start_date := p_ws_rt_start_date;
220 END IF;
221 BEN_CWB_PERSON_RATES_API.update_person_rate
222 ( p_group_per_in_ler_id => l_upd_rec.GROUP_PER_IN_LER_ID
223 ,p_pl_id => l_upd_rec.PL_ID
224 ,p_oipl_id => l_upd_rec.OIPL_ID
225 ,p_group_pl_id => l_upd_rec.GROUP_PL_ID
226 ,p_group_oipl_id => l_upd_rec.GROUP_OIPL_ID
227 ,p_lf_evt_ocrd_dt => l_upd_rec.LF_EVT_OCRD_DT
228 ,p_ws_val_last_upd_date => trunc(Sysdate)
229 ,p_ws_val_last_upd_by => l_person_id
230 ,p_ws_val => l_WS_VAL
231 ,p_object_version_number => l_ovn
232 ,p_ws_rt_start_date => l_ws_rt_start_date
233 );
234 End if;
235 Close Csr_PlRt_Dtls;
236
237 if g_debug then
238 hr_utility.set_location('Leaving '||l_proc,100);
239 end if;
240
241 return l_diff;
242
243 END UPDATE_WS_AMOUNT;
244
245 --
246 --------------------------- REFRESH_PERSON_TASKS -----------------------------
247 --
248
249 PROCEDURE REFRESH_PERSON_TASKS (P_PERSON_RATE_ID IN NUMBER Default Null
250 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
251 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
252 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
253 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null
254 ,P_TASK_ID IN NUMBER
255 ,P_SEC_MGR_LER_ID IN NUMBER Default Null)
256 IS
257 Cursor csr_person_rate_info (l_person_rate_id IN Number)
258 IS
259 Select group_pl_id
260 ,lf_evt_ocrd_dt
261 from ben_cwb_person_rates
262 where person_rate_id = l_person_rate_id;
263
264 Cursor csr_person_tasks_info(l_group_pl_id In Number, l_lf_evt_ocrd_dt Date)
265 IS
266 Select STATUS_CD
267 ,OBJECT_VERSION_NUMBER
268 From ben_cwb_person_tasks
269 Where GROUP_PER_IN_LER_ID = P_SEC_MGR_LER_ID
270 And TASK_ID = P_TASK_ID
271 And GROUP_PL_ID = l_group_pl_id
272 And LF_EVT_OCRD_DT = l_lf_evt_ocrd_dt ;
273
274 l_proc varchar2(72) := g_package||'REFRESH_PERSON_TASKS';
275 l_rate_id Number;
276 l_group_pl_id Number;
277 l_status_cd ben_cwb_person_tasks.STATUS_CD%Type;
278 l_ovn Number;
279 l_lf_evt_ocrd_dt Date;
280 BEGIN
281
282 hr_utility.set_location('Entering :'||l_proc,10);
283
284 If P_PERSON_RATE_ID IS NOT NULL then
285 l_rate_id := P_PERSON_RATE_ID;
286 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
287 l_rate_id := P_OPT1_PERSON_RATE_ID;
288 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
289 l_rate_id := P_OPT2_PERSON_RATE_ID;
290 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
291 l_rate_id := P_OPT3_PERSON_RATE_ID;
292 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
293 l_rate_id := P_OPT4_PERSON_RATE_ID;
294 End if;
295
296 hr_utility.set_location('l_rate_id :'||l_rate_id,20);
297
298 Open csr_person_rate_info(l_rate_id);
299 Fetch csr_person_rate_info into l_group_pl_id,l_lf_evt_ocrd_dt;
300 Close csr_person_rate_info;
301
302 hr_utility.set_location('l_group_pl_id :'||l_group_pl_id,50);
303 hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt,60);
304
305
306 Open csr_person_tasks_info(l_group_pl_id,l_lf_evt_ocrd_dt );
307 Fetch csr_person_tasks_info into l_status_cd,l_ovn;
308 Close csr_person_tasks_info;
309
310 hr_utility.set_location('l_status_cd :'||l_status_cd,70);
311 hr_utility.set_location('l_ovn :'||l_ovn,80);
312
313 If l_status_cd = 'NS' then
314 BEN_CWB_PERSON_TASKS_API.update_person_task
315 ( p_group_per_in_ler_id => P_SEC_MGR_LER_ID
316 ,p_task_id => P_TASK_ID
317 ,p_group_pl_id => l_group_pl_id
318 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
319 ,p_status_cd => 'IP'
320 ,p_task_last_update_date => sysdate
321 ,p_object_version_number => l_ovn
322 );
323 End If;
324
325 hr_utility.set_location('Leaving :'||l_proc,100);
326
327 END REFRESH_PERSON_TASKS;
328
329 --
330 --------------------------- get_group_per_in_ler_id -----------------------------
331 --
332
333 FUNCTION get_group_per_in_ler_id (P_PERSON_RATE_ID IN NUMBER Default Null
334 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
335 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
336 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
337 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
338 Return Number
339 IS
340 Cursor csr_group_per_in_ler_id (l_person_rate_id IN Number)
341 IS
342 Select group_per_in_ler_id
343 from ben_cwb_person_rates
344 where person_rate_id = l_person_rate_id;
345
346 l_proc Varchar2(72) := g_package||'get_group_per_in_ler_id';
347 l_rate_id Number;
348 l_group_per_in_ler_id Number;
349
350
351 BEGIN
352
353 hr_utility.set_location('Entering :'||l_proc,10);
354
355 If P_PERSON_RATE_ID IS NOT NULL then
356 l_rate_id := P_PERSON_RATE_ID;
357 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
358 l_rate_id := P_OPT1_PERSON_RATE_ID;
359 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
360 l_rate_id := P_OPT2_PERSON_RATE_ID;
361 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
362 l_rate_id := P_OPT3_PERSON_RATE_ID;
363 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
364 l_rate_id := P_OPT4_PERSON_RATE_ID;
365 End if;
366
367 hr_utility.set_location('l_rate_id :'||l_rate_id,20);
368
369 Open csr_group_per_in_ler_id(l_rate_id);
370 Fetch csr_group_per_in_ler_id into l_group_per_in_ler_id;
371 Close csr_group_per_in_ler_id;
372
373 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
374 hr_utility.set_location('Leaving :'||l_proc,100);
375
376 return l_group_per_in_ler_id;
377
378 End get_group_per_in_ler_id;
379
380 --
381 --------------------------- chk_processed_emp -----------------------------
382 --
383
384 PROCEDURE chk_processed_emp (P_PERSON_RATE_ID IN NUMBER Default Null
385 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
386 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
387 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
388 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
389 IS
390 l_proc Varchar2(72) := g_package||'chk_processed_emp';
391 l_group_per_in_ler_id Number;
392 l_pp_stat_cd Ben_Cwb_Person_Info.POST_PROCESS_STAT_CD%Type;
393
394 Cursor csr_PP_STAT_CD (l_ler_id IN Number)
395 IS
396 Select POST_PROCESS_STAT_CD
397 From Ben_Cwb_Person_Info
398 Where Group_Per_In_Ler_Id = l_ler_id;
399
400 -- added by kmg for fixing bug#6830922
401 CURSOR csr_ws_stat_cd (l_ler_id IN NUMBER) IS
402 SELECT submit_cd
403 FROM ben_cwb_person_groups mgr,
404 ben_cwb_group_hrchy hrchy
405 WHERE hrchy.emp_per_in_ler_id = l_ler_id
406 and hrchy.mgr_per_in_ler_id = mgr.group_per_in_ler_id
407 and hrchy.lvl_num = 1;
408 l_submit_cd ben_cwb_person_groups.submit_cd%TYPE;
409
410 BEGIN
411 hr_utility.set_location('Entering :'||l_proc,10);
412
413 l_group_per_in_ler_id := get_group_per_in_ler_id (P_PERSON_RATE_ID =>P_PERSON_RATE_ID
414 ,P_OPT1_PERSON_RATE_ID => P_OPT1_PERSON_RATE_ID
415 ,P_OPT2_PERSON_RATE_ID => P_OPT2_PERSON_RATE_ID
416 ,P_OPT3_PERSON_RATE_ID => P_OPT3_PERSON_RATE_ID
417 ,P_OPT4_PERSON_RATE_ID => P_OPT4_PERSON_RATE_ID);
418
419 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
420
421 Open csr_PP_STAT_CD(l_group_per_in_ler_id);
422 Fetch csr_PP_STAT_CD into l_pp_stat_cd;
423 Close csr_PP_STAT_CD;
424
425 hr_utility.set_location('l_pp_stat_cd :'||l_pp_stat_cd,50);
426
427 if l_pp_stat_cd IS NOT NULL then
428 hr_utility.set_message(805,'BEN_93752_CWB_PROCESSES_EMP');
429 hr_utility.raise_error;
430 End if;
431
432 -- added by kmg for fixing bug#6830922
433 OPEN csr_ws_stat_cd(l_group_per_in_ler_id);
434 FETCH csr_ws_stat_cd INTO l_submit_cd;
435 CLOSE csr_ws_stat_cd;
436 IF NVL(l_submit_cd,'NS') = 'SU' THEN
437 hr_utility.set_message(805,'BEN_94711_CWB_WS_SUBMITTED');
438 hr_utility.raise_error;
439 END IF;
440
441 hr_utility.set_location('Leaving :'||l_proc,200);
442 END chk_processed_emp;
443
444
445
446 --
447 --------------------------- update_perf_rating -----------------------------
448 --
449 Procedure update_perf_rating(P_PROPOSED_PERFORMANCE_RATING IN Varchar2
450 ,P_ACTING_PERSON_ID IN NUMBER Default Null
451 ,P_PERSON_RATE_ID IN NUMBER Default Null
452 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
453 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
454 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
455 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
456 IS
457
458 l_proc Varchar2(72) := g_package||'update_perf_rating';
459 l_group_per_in_ler_id Number;
460 l_rating Varchar2(30);
461
462 Cursor Csr_person_info(l_group_per_in_ler_id In Number)
463 IS
464 Select per.assignment_id Assignment_id
465 ,per.person_id Person_id
466 ,per.business_group_id business_group_id
467 ,per.full_name Person_name
468 From ben_cwb_person_info per
469 Where per.group_per_in_ler_id = l_group_per_in_ler_id;
470
471
472
473 Cursor Csr_pl_dsgn_info(l_group_per_in_ler_id In Number)
474 IS
475 Select dsgn.EMP_INTERVIEW_TYP_CD
476 ,dsgn.perf_revw_strt_dt
477 ,dsgn.group_pl_id
478 From ben_cwb_pl_dsgn dsgn
479 ,ben_cwb_person_info inf
480 Where dsgn.group_oipl_id = -1
481 And dsgn.pl_id = inf.group_pl_id
482 And dsgn.lf_evt_ocrd_dt = inf.lf_evt_ocrd_dt
483 And inf.group_per_in_ler_id = l_group_per_in_ler_id;
484
485
486 l_person_info Csr_person_info%RowType;
487 l_pl_dsgn_info Csr_pl_dsgn_info%RowType;
488
489 Begin
490 hr_utility.set_location('Entering :'||l_proc,10);
491 --If P_PROPOSED_PERFORMANCE_RATING IS NOT NULL Then
492 l_group_per_in_ler_id := get_group_per_in_ler_id (P_PERSON_RATE_ID =>P_PERSON_RATE_ID
493 ,P_OPT1_PERSON_RATE_ID => P_OPT1_PERSON_RATE_ID
494 ,P_OPT2_PERSON_RATE_ID => P_OPT2_PERSON_RATE_ID
495 ,P_OPT3_PERSON_RATE_ID => P_OPT3_PERSON_RATE_ID
496 ,P_OPT4_PERSON_RATE_ID => P_OPT4_PERSON_RATE_ID);
497
498 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
499
500
501 Open csr_person_info(l_group_per_in_ler_id);
502 Fetch csr_person_info into l_person_info;
503 Close csr_person_info;
504
505 hr_utility.set_location('l_person_info.Assignment_id :'||l_person_info.Assignment_id,70);
506 hr_utility.set_location('l_person_info.Person_id :'||l_person_info.Person_id,80);
507 hr_utility.set_location('l_person_info.business_group_id :'||l_person_info.business_group_id,90);
508 hr_utility.set_location('l_person_info.Person_name :'||l_person_info.Person_name,100);
509
510 Open Csr_pl_dsgn_info(l_group_per_in_ler_id);
511 Fetch Csr_pl_dsgn_info into l_pl_dsgn_info;
512 Close Csr_pl_dsgn_info;
513
514
515 hr_utility.set_location('l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD :'||l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD,120);
516 hr_utility.set_location('l_pl_dsgn_info.perf_revw_strt_dt :'||l_pl_dsgn_info.perf_revw_strt_dt,130);
517
518 ben_cwb_asg_update.process_rating
519 (p_validate_data => 'Y'
520 ,p_assignment_id => l_person_info.Assignment_id
521 ,p_person_id => l_person_info.Person_id
522 ,p_business_group_id => l_person_info.business_group_id
523 ,p_perf_revw_strt_dt => to_char(l_pl_dsgn_info.perf_revw_strt_dt,'yyyy/mm/dd')
524 ,p_perf_type => l_pl_dsgn_info.EMP_INTERVIEW_TYP_CD
525 ,p_perf_rating => P_PROPOSED_PERFORMANCE_RATING
526 ,p_person_name => l_person_info.Person_name
527 ,p_update_person_id => P_ACTING_PERSON_ID
528 ,p_update_date => Sysdate
529 ,p_group_pl_id => l_pl_dsgn_info.group_pl_id);
530
531
532
533
534 --End If;
535 hr_utility.set_location('Leaving :'||l_proc,100);
536 End update_perf_rating;
537
538 --
539 --------------------------- REFRESH_SUMMARY_GROUP_PL -----------------------------
540 --
541
542 PROCEDURE REFRESH_SUMMARY_GROUP_PL (P_PERSON_RATE_ID IN NUMBER Default Null
543 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
544 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
545 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
546 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
547 IS
548 Cursor csr_group_pl_id (l_person_rate_id IN Number)
549 IS
550 Select group_pl_id,
551 lf_evt_ocrd_dt
552 from ben_cwb_person_rates
553 where person_rate_id = l_person_rate_id;
554
555
556 l_rate_id Number;
557 l_group_pl_id Number;
558 l_lf_evt_ocrd_dt Date;
559 BEGIN
560
561 If P_PERSON_RATE_ID IS NOT NULL then
562 l_rate_id := P_PERSON_RATE_ID;
563 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
564 l_rate_id := P_OPT1_PERSON_RATE_ID;
565 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
566 l_rate_id := P_OPT2_PERSON_RATE_ID;
567 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
568 l_rate_id := P_OPT3_PERSON_RATE_ID;
569 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
570 l_rate_id := P_OPT4_PERSON_RATE_ID;
571 End if;
572
573 Open csr_group_pl_id(l_rate_id);
574 Fetch csr_group_pl_id into l_group_pl_id,l_lf_evt_ocrd_dt;
575 Close csr_group_pl_id;
576
577 ben_cwb_summary_pkg.refresh_summary_group_pl(P_GROUP_PL_ID => l_group_pl_id
578 ,P_LF_EVT_OCRD_DT => l_lf_evt_ocrd_dt);
579
580 END REFRESH_SUMMARY_GROUP_PL;
581
582 function get_plan_person_rate_id(p_opt_person_rate_id in number)
583 return number is
584 CURSOR c_get_pl_per_rates IS
585 SELECT PlRt.person_rate_id
586 FROM BEN_CWB_PERSON_RATES PlRt
587 ,ben_cwb_person_rates optRt
588 WHERE optRt.PERSON_RATE_ID = p_opt_person_rate_id
589 and optRt.group_per_in_ler_id = plRt.group_per_in_ler_id
590 and optRt.pl_id = plRt.pl_id
591 and plRt.oipl_id = -1;
592
593 l_return_val number := null;
594 begin
595 open c_get_pl_per_rates;
596 fetch c_get_pl_per_rates into l_return_val;
597 close c_get_pl_per_rates;
598
599 return l_return_val;
600 end;
601
602 --
603 ---------------------------handle_row-----------------------------
604 --
605
606 PROCEDURE handle_row
607 (
608 P_EMP_NAME IN VARCHAR2
609 ,P_MGR_NAME IN VARCHAR2 DEFAULT NULL
610 ,P_RANK IN NUMBER DEFAULT NULL
611 ,P_YEARS_EMPLOYED IN NUMBER DEFAULT NULL
612 ,P_BASE_SALARY IN NUMBER DEFAULT NULL
613 ,P_PL_NAME IN VARCHAR2 DEFAULT NULL
614 ,P_PL_XCHG_RATE IN NUMBER DEFAULT NULL
615 ,P_PL_STAT_SAL_VAL IN NUMBER DEFAULT NULL
616 ,P_PL_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
617 ,P_PL_TOT_COMP_VAL IN NUMBER DEFAULT NULL
618 ,P_PL_OTH_COMP_VAL IN NUMBER DEFAULT NULL
619 ,P_PL_WS_VAL IN NUMBER DEFAULT NULL
620 ,P_PL_WS_MIN_VAL IN NUMBER DEFAULT NULL
621 ,P_PL_WS_MAX_VAL IN NUMBER DEFAULT NULL
622 ,P_PL_WS_INCR_VAL IN NUMBER DEFAULT NULL
623 ,P_PL_REC_VAL IN NUMBER DEFAULT NULL
624 ,P_PL_REC_MIN_VAL IN NUMBER DEFAULT NULL
625 ,P_PL_REC_MAX_VAL IN NUMBER DEFAULT NULL
626 ,P_PL_MISC1_VAL IN NUMBER DEFAULT NULL
627 ,P_PL_MISC2_VAL IN NUMBER DEFAULT NULL
628 ,P_PL_MISC3_VAL IN NUMBER DEFAULT NULL
629 ,P_PL_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
630 ,P_PL_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
631 ,P_OPT1_NAME IN VARCHAR2 DEFAULT NULL
632 ,P_OPT1_XCHG_RATE IN NUMBER DEFAULT NULL
633 ,P_OPT1_STAT_SAL_VAL IN NUMBER DEFAULT NULL
634 ,P_OPT1_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
635 ,P_OPT1_TOT_COMP_VAL IN NUMBER DEFAULT NULL
636 ,P_OPT1_OTH_COMP_VAL IN NUMBER DEFAULT NULL
637 ,P_OPT1_WS_VAL IN NUMBER DEFAULT NULL
638 ,P_OPT1_WS_MIN_VAL IN NUMBER DEFAULT NULL
639 ,P_OPT1_WS_MAX_VAL IN NUMBER DEFAULT NULL
640 ,P_OPT1_WS_INCR_VAL IN NUMBER DEFAULT NULL
641 ,P_OPT1_REC_VAL IN NUMBER DEFAULT NULL
642 ,P_OPT1_REC_MIN_VAL IN NUMBER DEFAULT NULL
643 ,P_OPT1_REC_MAX_VAL IN NUMBER DEFAULT NULL
644 ,P_OPT1_MISC1_VAL IN NUMBER DEFAULT NULL
645 ,P_OPT1_MISC2_VAL IN NUMBER DEFAULT NULL
646 ,P_OPT1_MISC3_VAL IN NUMBER DEFAULT NULL
647 ,P_OPT1_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
648 ,P_OPT1_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
649 ,P_OPT2_NAME IN VARCHAR2 DEFAULT NULL
650 ,P_OPT2_XCHG_RATE IN NUMBER DEFAULT NULL
651 ,P_OPT2_STAT_SAL_VAL IN NUMBER DEFAULT NULL
652 ,P_OPT2_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
653 ,P_OPT2_TOT_COMP_VAL IN NUMBER DEFAULT NULL
654 ,P_OPT2_OTH_COMP_VAL IN NUMBER DEFAULT NULL
655 ,P_OPT2_WS_VAL IN NUMBER DEFAULT NULL
656 ,P_OPT2_WS_MIN_VAL IN NUMBER DEFAULT NULL
657 ,P_OPT2_WS_MAX_VAL IN NUMBER DEFAULT NULL
658 ,P_OPT2_WS_INCR_VAL IN NUMBER DEFAULT NULL
659 ,P_OPT2_REC_VAL IN NUMBER DEFAULT NULL
660 ,P_OPT2_REC_MIN_VAL IN NUMBER DEFAULT NULL
661 ,P_OPT2_REC_MAX_VAL IN NUMBER DEFAULT NULL
662 ,P_OPT2_MISC1_VAL IN NUMBER DEFAULT NULL
663 ,P_OPT2_MISC2_VAL IN NUMBER DEFAULT NULL
664 ,P_OPT2_MISC3_VAL IN NUMBER DEFAULT NULL
665 ,P_OPT2_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
666 ,P_OPT2_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
667 ,P_OPT3_NAME IN VARCHAR2 DEFAULT NULL
668 ,P_OPT3_XCHG_RATE IN NUMBER DEFAULT NULL
669 ,P_OPT3_STAT_SAL_VAL IN NUMBER DEFAULT NULL
670 ,P_OPT3_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
671 ,P_OPT3_TOT_COMP_VAL IN NUMBER DEFAULT NULL
672 ,P_OPT3_OTH_COMP_VAL IN NUMBER DEFAULT NULL
673 ,P_OPT3_WS_VAL IN NUMBER DEFAULT NULL
674 ,P_OPT3_WS_MIN_VAL IN NUMBER DEFAULT NULL
675 ,P_OPT3_WS_MAX_VAL IN NUMBER DEFAULT NULL
676 ,P_OPT3_WS_INCR_VAL IN NUMBER DEFAULT NULL
677 ,P_OPT3_REC_VAL IN NUMBER DEFAULT NULL
678 ,P_OPT3_REC_MIN_VAL IN NUMBER DEFAULT NULL
679 ,P_OPT3_REC_MAX_VAL IN NUMBER DEFAULT NULL
680 ,P_OPT3_MISC1_VAL IN NUMBER DEFAULT NULL
681 ,P_OPT3_MISC2_VAL IN NUMBER DEFAULT NULL
682 ,P_OPT3_MISC3_VAL IN NUMBER DEFAULT NULL
683 ,P_OPT3_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
684 ,P_OPT3_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
685 ,P_OPT4_NAME IN VARCHAR2 DEFAULT NULL
686 ,P_OPT4_XCHG_RATE IN NUMBER DEFAULT NULL
687 ,P_OPT4_STAT_SAL_VAL IN NUMBER DEFAULT NULL
688 ,P_OPT4_ELIG_SAL_VAL IN NUMBER DEFAULT NULL
689 ,P_OPT4_TOT_COMP_VAL IN NUMBER DEFAULT NULL
690 ,P_OPT4_OTH_COMP_VAL IN NUMBER DEFAULT NULL
691 ,P_OPT4_WS_VAL IN NUMBER DEFAULT NULL
692 ,P_OPT4_WS_MIN_VAL IN NUMBER DEFAULT NULL
693 ,P_OPT4_WS_MAX_VAL IN NUMBER DEFAULT NULL
694 ,P_OPT4_WS_INCR_VAL IN NUMBER DEFAULT NULL
695 ,P_OPT4_REC_VAL IN NUMBER DEFAULT NULL
696 ,P_OPT4_REC_MIN_VAL IN NUMBER DEFAULT NULL
697 ,P_OPT4_REC_MAX_VAL IN NUMBER DEFAULT NULL
698 ,P_OPT4_MISC1_VAL IN NUMBER DEFAULT NULL
699 ,P_OPT4_MISC2_VAL IN NUMBER DEFAULT NULL
700 ,P_OPT4_MISC3_VAL IN NUMBER DEFAULT NULL
701 ,P_OPT4_WS_LAST_UPD_DATE IN DATE DEFAULT NULL
702 ,P_OPT4_WS_LAST_UPD_NAME IN VARCHAR2 DEFAULT NULL
703 ,P_EMPLOYEE_NUMBER IN VARCHAR2 DEFAULT NULL
704 ,P_EMP_CATEGORY IN VARCHAR2 DEFAULT NULL
705 ,P_ASSIGNMENT_STATUS IN VARCHAR2 DEFAULT NULL
706 ,P_PEOPLE_GROUP_NAME IN VARCHAR2 DEFAULT NULL
707 ,P_EMAIL_ADDR IN VARCHAR2 DEFAULT NULL
708 ,P_START_DATE IN DATE DEFAULT NULL
709 ,P_ORIGINAL_START_DATE IN DATE DEFAULT NULL
710 ,P_NORMAL_HOURS IN NUMBER DEFAULT NULL
711 ,P_PAYROLL_NAME IN VARCHAR2 DEFAULT NULL
712 ,P_BUSINESS_GROUP_NAME IN VARCHAR2 DEFAULT NULL
713 ,P_ORG_NAME IN VARCHAR2 DEFAULT NULL
714 ,P_LOC_NAME IN VARCHAR2 DEFAULT NULL
715 ,P_JOB_NAME IN VARCHAR2 DEFAULT NULL
716 ,P_POS_NAME IN VARCHAR2 DEFAULT NULL
717 ,P_GRD_NAME IN VARCHAR2 DEFAULT NULL
718 ,P_COUNTRY IN VARCHAR2 DEFAULT NULL
719 ,P_YEARS_IN_JOB IN NUMBER DEFAULT NULL
720 ,P_YEARS_IN_POSITION IN NUMBER DEFAULT NULL
721 ,P_YEARS_IN_GRADE IN NUMBER DEFAULT NULL
722 ,P_GRADE_RANGE IN VARCHAR2 DEFAULT NULL
723 ,P_GRADE_MID_POINT IN NUMBER DEFAULT NULL
724 ,P_GRD_QUARTILE IN VARCHAR2 DEFAULT NULL
725 ,P_GRD_COMPARATIO IN NUMBER DEFAULT NULL
726 ,P_PERFORMANCE_RATING IN VARCHAR2 DEFAULT NULL
727 ,P_PERFORMANCE_RATING_TYPE IN VARCHAR2 DEFAULT NULL
728 ,P_PERFORMANCE_RATING_DATE IN DATE DEFAULT NULL
729 ,P_LAST_RANK IN NUMBER DEFAULT NULL
730 ,P_LAST_MGR_NAME IN VARCHAR2 DEFAULT NULL
731 ,P_RANK_QUARTILE IN NUMBER DEFAULT NULL
732 ,P_TOTAL_RANK IN NUMBER DEFAULT NULL
733 ,P_CHANGE_REASON IN VARCHAR2 DEFAULT NULL
734 ,P_BASE_SALARY_CHANGE_DATE IN DATE DEFAULT NULL
735 ,P_LF_EVT_OCRD_DT IN DATE DEFAULT NULL
736 ,P_MGR_LER_ID IN NUMBER DEFAULT NULL
737 ,P_PL_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
738 ,P_P_OPT1_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
739 ,P_P_OPT2_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
740 ,P_P_OPT3_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
741 ,P_P_OPT4_PERSON_RATE_ID IN VARCHAR2 DEFAULT NULL
742 ,P_LVL_NUM IN NUMBER DEFAULT NULL
743 ,P_CUSTOM_SEGMENT1 IN VARCHAR2 DEFAULT default_string
744 ,P_CUSTOM_SEGMENT2 IN VARCHAR2 DEFAULT default_string
745 ,P_CUSTOM_SEGMENT3 IN VARCHAR2 DEFAULT default_string
746 ,P_CUSTOM_SEGMENT4 IN VARCHAR2 DEFAULT default_string
747 ,P_CUSTOM_SEGMENT5 IN VARCHAR2 DEFAULT default_string
748 ,P_CUSTOM_SEGMENT6 IN VARCHAR2 DEFAULT default_string
749 ,P_CUSTOM_SEGMENT7 IN VARCHAR2 DEFAULT default_string
750 ,P_CUSTOM_SEGMENT8 IN VARCHAR2 DEFAULT default_string
751 ,P_CUSTOM_SEGMENT9 IN VARCHAR2 DEFAULT default_string
752 ,P_CUSTOM_SEGMENT10 IN VARCHAR2 DEFAULT default_string
753 ,P_CUSTOM_SEGMENT11 IN NUMBER DEFAULT default_number
754 ,P_CUSTOM_SEGMENT12 IN NUMBER DEFAULT default_number
755 ,P_CUSTOM_SEGMENT13 IN NUMBER DEFAULT default_number
756 ,P_CUSTOM_SEGMENT14 IN NUMBER DEFAULT default_number
757 ,P_CUSTOM_SEGMENT15 IN NUMBER DEFAULT default_number
758 ,P_PROPOSED_PERFORMANCE_RATING IN VARCHAR2 DEFAULT NULL
759 ,P_PROPOSED_JOB IN VARCHAR2 DEFAULT NULL
760 ,P_PLAN_UOM IN VARCHAR2 DEFAULT NULL
761 ,P_OPT1_UOM IN VARCHAR2 DEFAULT NULL
762 ,P_OPT2_UOM IN VARCHAR2 DEFAULT NULL
763 ,P_OPT3_UOM IN VARCHAR2 DEFAULT NULL
764 ,P_OPT4_UOM IN VARCHAR2 DEFAULT NULL
765 ,P_USER_ID IN VARCHAR2 DEFAULT NULL
766 ,P_PROPOSED_GRADE IN VARCHAR2 DEFAULT NULL
767 ,P_PROPOSED_POSITION IN VARCHAR2 DEFAULT NULL
768 ,P_PROPOSED_GROUP IN VARCHAR2 DEFAULT NULL
769 ,P_TASK_ID IN VARCHAR2 DEFAULT NULL
770 ,P_SEC_MGR_LER_ID IN VARCHAR2 DEFAULT NULL
771 ,P_ACTING_PERSON_ID IN VARCHAR2 DEFAULT NULL
772 ,P_DOWNLOAD_SWITCH IN VARCHAR2 DEFAULT NULL
773 ,P_CPI_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL
774 ,P_CPI_ATTRIBUTE1 IN VARCHAR2 DEFAULT default_string
775 ,P_CPI_ATTRIBUTE2 IN VARCHAR2 DEFAULT default_string
776 ,P_CPI_ATTRIBUTE3 IN VARCHAR2 DEFAULT default_string
777 ,P_CPI_ATTRIBUTE4 IN VARCHAR2 DEFAULT default_string
778 ,P_CPI_ATTRIBUTE5 IN VARCHAR2 DEFAULT default_string
779 ,P_CPI_ATTRIBUTE6 IN VARCHAR2 DEFAULT default_string
780 ,P_CPI_ATTRIBUTE7 IN VARCHAR2 DEFAULT default_string
781 ,P_CPI_ATTRIBUTE8 IN VARCHAR2 DEFAULT default_string
782 ,P_CPI_ATTRIBUTE9 IN VARCHAR2 DEFAULT default_string
783 ,P_CPI_ATTRIBUTE10 IN VARCHAR2 DEFAULT default_string
784 ,P_CPI_ATTRIBUTE11 IN VARCHAR2 DEFAULT default_string
785 ,P_CPI_ATTRIBUTE12 IN VARCHAR2 DEFAULT default_string
786 ,P_CPI_ATTRIBUTE13 IN VARCHAR2 DEFAULT default_string
787 ,P_CPI_ATTRIBUTE14 IN VARCHAR2 DEFAULT default_string
788 ,P_CPI_ATTRIBUTE15 IN VARCHAR2 DEFAULT default_string
789 ,P_CPI_ATTRIBUTE16 IN VARCHAR2 DEFAULT default_string
790 ,P_CPI_ATTRIBUTE17 IN VARCHAR2 DEFAULT default_string
791 ,P_CPI_ATTRIBUTE18 IN VARCHAR2 DEFAULT default_string
792 ,P_CPI_ATTRIBUTE19 IN VARCHAR2 DEFAULT default_string
793 ,P_CPI_ATTRIBUTE20 IN VARCHAR2 DEFAULT default_string
794 ,P_CPI_ATTRIBUTE21 IN VARCHAR2 DEFAULT default_string
795 ,P_CPI_ATTRIBUTE22 IN VARCHAR2 DEFAULT default_string
796 ,P_CPI_ATTRIBUTE23 IN VARCHAR2 DEFAULT default_string
797 ,P_CPI_ATTRIBUTE24 IN VARCHAR2 DEFAULT default_string
798 ,P_CPI_ATTRIBUTE25 IN VARCHAR2 DEFAULT default_string
799 ,P_CPI_ATTRIBUTE26 IN VARCHAR2 DEFAULT default_string
800 ,P_CPI_ATTRIBUTE27 IN VARCHAR2 DEFAULT default_string
801 ,P_CPI_ATTRIBUTE28 IN VARCHAR2 DEFAULT default_string
802 ,P_CPI_ATTRIBUTE29 IN VARCHAR2 DEFAULT default_string
803 ,P_CPI_ATTRIBUTE30 IN VARCHAR2 DEFAULT default_string
804 ,P_CUSTOM_SEGMENT16 IN NUMBER DEFAULT default_number
805 ,P_CUSTOM_SEGMENT17 IN NUMBER DEFAULT default_number
806 ,P_CUSTOM_SEGMENT18 IN NUMBER DEFAULT default_number
807 ,P_CUSTOM_SEGMENT19 IN NUMBER DEFAULT default_number
808 ,P_CUSTOM_SEGMENT20 IN NUMBER DEFAULT default_number
809 ,P_PL_CURRENCY IN VARCHAR2 DEFAULT NULL
810 ,P_OPT1_CURRENCY IN VARCHAR2 DEFAULT NULL
811 ,P_OPT2_CURRENCY IN VARCHAR2 DEFAULT NULL
812 ,P_OPT3_CURRENCY IN VARCHAR2 DEFAULT NULL
813 ,P_OPT4_CURRENCY IN VARCHAR2 DEFAULT NULL
814 ,P_PL_RT_START_DATE IN DATE DEFAULT default_date
815 ,P_OPT1_RT_START_DATE IN DATE DEFAULT default_date
816 ,P_OPT2_RT_START_DATE IN DATE DEFAULT default_date
817 ,P_OPT3_RT_START_DATE IN DATE DEFAULT default_date
818 ,P_OPT4_RT_START_DATE IN DATE DEFAULT default_date
819
820 ) IS
821
822 CURSOR Csr_get_pl_ws_val(l_PL_PERSON_RATE_ID IN NUMBER)
823 IS
824 SELECT PlRt.WS_VAL
825 FROM BEN_CWB_PERSON_RATES PlRt
826 WHERE PlRt.PERSON_RATE_ID = l_PL_PERSON_RATE_ID;
827
828 CURSOR csr_cpi_flex_info(l_group_per_in_ler_id IN NUMBER)
829 IS
830 SELECT cpi_attribute_category,
831 cpi_attribute1,
832 cpi_attribute2,
833 cpi_attribute3,
834 cpi_attribute4,
835 cpi_attribute5,
836 cpi_attribute6,
837 cpi_attribute7,
838 cpi_attribute8,
839 cpi_attribute9,
840 cpi_attribute10,
841 cpi_attribute11,
842 cpi_attribute12,
843 cpi_attribute13,
844 cpi_attribute14,
845 cpi_attribute15,
846 cpi_attribute16,
847 cpi_attribute17,
848 cpi_attribute18,
849 cpi_attribute19,
850 cpi_attribute20,
851 cpi_attribute21,
852 cpi_attribute22,
853 cpi_attribute23,
854 cpi_attribute24,
855 cpi_attribute25,
856 cpi_attribute26,
857 cpi_attribute27,
858 cpi_attribute28,
859 cpi_attribute29,
860 cpi_attribute30,
861 custom_segment1,
862 custom_segment2,
863 custom_segment3,
864 custom_segment4,
865 custom_segment5,
866 custom_segment6,
867 custom_segment7,
868 custom_segment8,
869 custom_segment9,
870 custom_segment10,
871 custom_segment11,
872 custom_segment12,
873 custom_segment13,
874 custom_segment14,
875 custom_segment15,
876 custom_segment16,
877 custom_segment17,
878 custom_segment18,
879 custom_segment19,
880 custom_segment20,
881 object_version_number
882 FROM ben_cwb_person_info
883 WHERE group_per_in_ler_id = l_group_per_in_ler_id;
884
885 CURSOR csr_get_group_plan_info(l_group_per_in_ler_id IN NUMBER)
886 IS
887 SELECT group_pl_id, lf_evt_ocrd_dt
888 FROM ben_per_in_ler
889 WHERE per_in_ler_id = l_group_per_in_ler_id
890 AND ROWNUM < 2;
891
892 cursor csr_is_read_only is
893 select interface_seq_num, decode(nvl(lay.read_only_flag,'N')||nvl(intf.read_only_flag,'N'), 'NN','N','Y') read_only
894 from bne_interface_cols_b intf, bne_layout_cols lay
895 where intf.interface_code = 'BEN_CWB_WRK_SHT_INTF'
896 and intf.application_id = 800
897 and intf.interface_code = lay.interface_code
898 and lay.layout_code = 'BEN_CWB_WRK_SHT_BASE_LYT'
899 and lay.application_id = 800
900 and intf.sequence_num = lay.interface_seq_num
901 and ((intf.sequence_num between 200 and 234) or (intf.sequence_num between 136 and 150));
902
903 l_is_read_only csr_is_read_only%RowType;
904 l_pl_ws_val BEN_CWB_PERSON_RATES.WS_VAL%Type;
905 l_group_per_in_ler_id BEN_CWB_PERSON_RATES.GROUP_PER_IN_LER_ID%Type;
906 l_cpi_attribute_category BEN_CWB_PERSON_INFO.cpi_attribute_category%TYPE;
907 l_cpi_attribute1 BEN_CWB_PERSON_INFO.cpi_attribute1%TYPE;
908 l_cpi_attribute2 BEN_CWB_PERSON_INFO.cpi_attribute2%TYPE;
909 l_cpi_attribute3 BEN_CWB_PERSON_INFO.cpi_attribute3%TYPE;
910 l_cpi_attribute4 BEN_CWB_PERSON_INFO.cpi_attribute4%TYPE;
911 l_cpi_attribute5 BEN_CWB_PERSON_INFO.cpi_attribute5%TYPE;
912 l_cpi_attribute6 BEN_CWB_PERSON_INFO.cpi_attribute6%TYPE;
913 l_cpi_attribute7 BEN_CWB_PERSON_INFO.cpi_attribute7%TYPE;
914 l_cpi_attribute8 BEN_CWB_PERSON_INFO.cpi_attribute8%TYPE;
915 l_cpi_attribute9 BEN_CWB_PERSON_INFO.cpi_attribute9%TYPE;
916 l_cpi_attribute10 BEN_CWB_PERSON_INFO.cpi_attribute10%TYPE;
917 l_cpi_attribute11 BEN_CWB_PERSON_INFO.cpi_attribute11%TYPE;
918 l_cpi_attribute12 BEN_CWB_PERSON_INFO.cpi_attribute12%TYPE;
919 l_cpi_attribute13 BEN_CWB_PERSON_INFO.cpi_attribute13%TYPE;
920 l_cpi_attribute14 BEN_CWB_PERSON_INFO.cpi_attribute14%TYPE;
921 l_cpi_attribute15 BEN_CWB_PERSON_INFO.cpi_attribute15%TYPE;
922 l_cpi_attribute16 BEN_CWB_PERSON_INFO.cpi_attribute16%TYPE;
923 l_cpi_attribute17 BEN_CWB_PERSON_INFO.cpi_attribute17%TYPE;
924 l_cpi_attribute18 BEN_CWB_PERSON_INFO.cpi_attribute18%TYPE;
925 l_cpi_attribute19 BEN_CWB_PERSON_INFO.cpi_attribute19%TYPE;
926 l_cpi_attribute20 BEN_CWB_PERSON_INFO.cpi_attribute20%TYPE;
927 l_cpi_attribute21 BEN_CWB_PERSON_INFO.cpi_attribute21%TYPE;
928 l_cpi_attribute22 BEN_CWB_PERSON_INFO.cpi_attribute22%TYPE;
929 l_cpi_attribute23 BEN_CWB_PERSON_INFO.cpi_attribute23%TYPE;
930 l_cpi_attribute24 BEN_CWB_PERSON_INFO.cpi_attribute24%TYPE;
931 l_cpi_attribute25 BEN_CWB_PERSON_INFO.cpi_attribute25%TYPE;
932 l_cpi_attribute26 BEN_CWB_PERSON_INFO.cpi_attribute26%TYPE;
933 l_cpi_attribute27 BEN_CWB_PERSON_INFO.cpi_attribute27%TYPE;
934 l_cpi_attribute28 BEN_CWB_PERSON_INFO.cpi_attribute28%TYPE;
935 l_cpi_attribute29 BEN_CWB_PERSON_INFO.cpi_attribute29%TYPE;
936 l_cpi_attribute30 BEN_CWB_PERSON_INFO.cpi_attribute30%TYPE;
937 l_custom_segment1 BEN_CWB_PERSON_INFO.custom_segment1%TYPE;
938 l_custom_segment2 BEN_CWB_PERSON_INFO.custom_segment2%TYPE;
939 l_custom_segment3 BEN_CWB_PERSON_INFO.custom_segment3%TYPE;
940 l_custom_segment4 BEN_CWB_PERSON_INFO.custom_segment4%TYPE;
941 l_custom_segment5 BEN_CWB_PERSON_INFO.custom_segment5%TYPE;
942 l_custom_segment6 BEN_CWB_PERSON_INFO.custom_segment6%TYPE;
943 l_custom_segment7 BEN_CWB_PERSON_INFO.custom_segment7%TYPE;
944 l_custom_segment8 BEN_CWB_PERSON_INFO.custom_segment8%TYPE;
945 l_custom_segment9 BEN_CWB_PERSON_INFO.custom_segment9%TYPE;
946 l_custom_segment10 BEN_CWB_PERSON_INFO.custom_segment10%TYPE;
947 l_custom_segment11 BEN_CWB_PERSON_INFO.custom_segment11%TYPE;
948 l_custom_segment12 BEN_CWB_PERSON_INFO.custom_segment12%TYPE;
949 l_custom_segment13 BEN_CWB_PERSON_INFO.custom_segment13%TYPE;
950 l_custom_segment14 BEN_CWB_PERSON_INFO.custom_segment14%TYPE;
951 l_custom_segment15 BEN_CWB_PERSON_INFO.custom_segment15%TYPE;
952 l_custom_segment16 BEN_CWB_PERSON_INFO.custom_segment16%TYPE;
953 l_custom_segment17 BEN_CWB_PERSON_INFO.custom_segment17%TYPE;
954 l_custom_segment18 BEN_CWB_PERSON_INFO.custom_segment18%TYPE;
955 l_custom_segment19 BEN_CWB_PERSON_INFO.custom_segment19%TYPE;
956 l_custom_segment20 BEN_CWB_PERSON_INFO.custom_segment20%TYPE;
957 l_ovn BEN_CWB_PERSON_INFO.object_version_number%TYPE;
958 l_proc varchar2(72) := g_package||'handle_row';
959 l_pl_person_rate_id Number := null;
960 l_opt1_person_rate_id Number := null;
961 l_opt2_person_rate_id Number := null;
962 l_opt3_person_rate_id Number := null;
963 l_opt4_person_rate_id Number := null;
964 l_task_id Number := null;
965 l_sec_mgr_ler_id Number := null;
966 l_acting_person_id Number := null;
967 l_decrypt_switch varchar2(200) := null;
968 l_download_switch varchar2(200) := null;
969 l_diff number := null;
970 l_group_pl_id Number := null;
971 l_lf_evt_ocrd_dt Date := null;
972
973 BEGIN
974
975 If g_debug then
976 hr_utility.set_location('Entering '||l_proc,10);
977 End if;
978
979 --Clear message
980 hr_utility.clear_message;
981
982 -- Issue Savepoint
983 savepoint update_data;
984 --
985 --
986 BEN_CWB_SUMMARY_PKG.delete_pl_sql_tab;
987
988
989
990 IF (P_PL_PERSON_RATE_ID IS NULL
991 AND P_P_OPT1_PERSON_RATE_ID IS NULL
992 AND P_P_OPT2_PERSON_RATE_ID IS NULL
993 AND P_P_OPT3_PERSON_RATE_ID IS NULL
994 AND P_P_OPT4_PERSON_RATE_ID IS NULL) THEN
995
996 hr_utility.set_message(805,'BEN_CWB_PL_OPT_NOT_EXISTS');
997 hr_utility.raise_error;
998 END IF;
999
1000 IF (P_PL_PERSON_RATE_ID IS NOT NULL) THEN
1001 l_PL_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_PL_PERSON_RATE_ID);
1002 If g_debug then
1003 hr_utility.set_location('l_PL_PERSON_RATE_ID :'||l_PL_PERSON_RATE_ID,20);
1004 End if;
1005 END IF;
1006
1007 IF (P_P_OPT1_PERSON_RATE_ID IS NOT NULL) THEN
1008 l_OPT1_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT1_PERSON_RATE_ID);
1009 If g_debug then
1010 hr_utility.set_location('l_OPT1_PERSON_RATE_ID :'||l_OPT1_PERSON_RATE_ID,30);
1011 End if;
1012 END IF;
1013
1014 IF (P_P_OPT2_PERSON_RATE_ID IS NOT NULL) THEN
1015 l_OPT2_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT2_PERSON_RATE_ID);
1016 If g_debug then
1017 hr_utility.set_location('l_OPT2_PERSON_RATE_ID :'||l_OPT2_PERSON_RATE_ID,40);
1018 End if;
1019 END IF;
1020
1021 IF (P_P_OPT3_PERSON_RATE_ID IS NOT NULL) THEN
1022 l_OPT3_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT3_PERSON_RATE_ID);
1023 If g_debug then
1024 hr_utility.set_location('l_OPT3_PERSON_RATE_ID :'||l_OPT3_PERSON_RATE_ID,50);
1025 End if;
1026 END IF;
1027
1028 IF (P_P_OPT4_PERSON_RATE_ID IS NOT NULL) THEN
1029 l_OPT4_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT4_PERSON_RATE_ID);
1030 If g_debug then
1031 hr_utility.set_location('l_OPT4_PERSON_RATE_ID :'||l_OPT4_PERSON_RATE_ID,60);
1032 End if;
1033 END IF;
1034
1035 IF (P_TASK_ID IS NOT NULL) THEN
1036 l_TASK_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_TASK_ID);
1037 If g_debug then
1038 hr_utility.set_location('l_TASK_ID :'||l_TASK_ID,65);
1039 End if;
1040 END IF;
1041
1042 IF (P_SEC_MGR_LER_ID IS NOT NULL) THEN
1043 l_SEC_MGR_LER_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_SEC_MGR_LER_ID);
1044 If g_debug then
1045 hr_utility.set_location('l_SEC_MGR_LER_ID :'||l_SEC_MGR_LER_ID,67);
1046 End if;
1047 END IF;
1048
1049 IF (P_ACTING_PERSON_ID IS NOT NULL) THEN
1050 l_ACTING_PERSON_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_ACTING_PERSON_ID);
1051 If g_debug then
1052 hr_utility.set_location('P_ACTING_PERSON_ID :'||l_ACTING_PERSON_ID,68);
1053 End if;
1054 END IF;
1055
1056 -- Using hexadecimal to store the upload column availability information.
1057
1058 IF (P_DOWNLOAD_SWITCH IS NOT NULL) THEN
1059 l_decrypt_switch := BEN_CWB_WEBADI_UTILS.decrypt(p_download_switch);
1060 l_download_switch := lpad(ben_cwb_webadi_utils.int2bin(ben_cwb_webadi_utils.hex2int(substr(l_decrypt_switch,1,7))),28,0);
1061 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');
1062 if g_debug then
1063 hr_utility.set_location('p_download_switch (Decrypted) :'||l_decrypt_switch,69);
1064 End if;
1065 l_download_switch := REPLACE(REPLACE(l_download_switch,'1','2'),'0','1');
1066 if g_debug then
1067 hr_utility.set_location('l_final_download_Switch :'||l_download_switch,70);
1068 End if;
1069 END IF;
1070
1071 If g_debug then
1072 hr_utility.set_location('P_PL_WS_VAL : '||P_PL_WS_VAL,75);
1073 hr_utility.set_location('P_OPT1_WS_VAL : '||P_OPT1_WS_VAL,80);
1074 hr_utility.set_location('P_OPT2_WS_VAL : '||P_OPT2_WS_VAL,90);
1075 hr_utility.set_location('P_OPT3_WS_VAL : '||P_OPT3_WS_VAL,100);
1076 hr_utility.set_location('P_OPT4_WS_VAL : '||P_OPT4_WS_VAL,110);
1077 End if;
1078
1079
1080 -- Check for people that have already been Processed.
1081 -- If already processed, raise error.
1082 chk_processed_emp (P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1083 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1084 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1085 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1086 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID);
1087
1088 -- If No Options Exist then allow Modifying the Plan Worksheet Amount
1089 IF (l_PL_PERSON_RATE_ID IS NOT NULL
1090 AND l_OPT1_PERSON_RATE_ID IS NULL
1091 AND l_OPT2_PERSON_RATE_ID IS NULL
1092 AND l_OPT3_PERSON_RATE_ID IS NULL
1093 AND l_OPT4_PERSON_RATE_ID IS NULL ) THEN
1094
1095 If g_debug then
1096 hr_utility.set_location('No Options Exists',120);
1097 end if;
1098
1099 IF(substr(l_DOWNLOAD_SWITCH,1,1) = '2') THEN
1100 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1101 ,P_WS_VAL => P_PL_WS_VAL
1102 ,P_USER_ID => P_USER_ID
1103 ,P_WS_RT_START_DATE =>P_PL_RT_START_DATE );
1104 If g_debug then
1105 hr_utility.set_location('Updated Plan WS Amt Sucessfully',130);
1106 End if;
1107 End If;
1108
1109
1110
1111 ELSE
1112 -- Update Option Record with Modified Option Worksheet Amount
1113
1114 If g_debug then
1115 hr_utility.set_location('Options Exists',140);
1116 end if;
1117
1118 -- Plan :
1119 -- If Option Rates exists for a Plan
1120 -- and User tries to update Plan WS Val then Raise Error
1121
1122 Open Csr_get_pl_ws_val(l_PL_PERSON_RATE_ID);
1123 Fetch Csr_get_pl_ws_val into l_pl_ws_val;
1124 Close Csr_get_pl_ws_val;
1125
1126 If g_debug then
1127 hr_utility.set_location('l_pl_ws_val :'||l_pl_ws_val,150);
1128 end if;
1129
1130 IF (l_pl_ws_val <> P_PL_WS_VAL) THEN
1131 hr_utility.set_message(805,'BEN_7830_CWB_NOT_UPD_PL_WSVAL');
1132 hr_utility.raise_error;
1133 END IF;
1134
1135 -- Option 1 :
1136 IF (l_OPT1_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,2,1) = '2') THEN
1137 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1138 ,P_WS_VAL => P_OPT1_WS_VAL
1139 ,P_USER_ID => P_USER_ID
1140 ,P_WS_RT_START_DATE => P_OPT1_RT_START_DATE);
1141 if l_pl_person_rate_id is null then
1142 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID);
1143 end if;
1144
1145 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1146 ,p_add_val => l_diff
1147 ,P_USER_ID => P_USER_ID
1148 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1149 If g_debug then
1150 hr_utility.set_location('Updated Option1 WS Amt Sucessfully',160);
1151 end if;
1152
1153 END IF;
1154
1155 -- Option 2 :
1156 IF (l_OPT2_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,3,1) = '2') THEN
1157 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1158 ,P_WS_VAL => P_OPT2_WS_VAL
1159 ,P_USER_ID => P_USER_ID
1160 ,P_WS_RT_START_DATE => P_OPT2_RT_START_DATE);
1161 if l_pl_person_rate_id is null then
1162 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID); end if;
1163 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1164 ,p_add_val => l_diff
1165 ,P_USER_ID => P_USER_ID
1166 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1167 If g_debug then
1168 hr_utility.set_location('Updated Option2 WS Amt Sucessfully',170);
1169 end if;
1170
1171 END IF;
1172
1173 -- Option 3 :
1174 IF (l_OPT3_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,4,1) = '2') THEN
1175 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1176 ,P_WS_VAL => P_OPT3_WS_VAL
1177 ,P_USER_ID => P_USER_ID
1178 ,P_WS_RT_START_DATE => P_OPT3_RT_START_DATE);
1179 if l_pl_person_rate_id is null then
1180 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID); end if;
1181 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1182 ,p_add_val => l_diff
1183 ,P_USER_ID => P_USER_ID
1184 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1185 If g_debug then
1186 hr_utility.set_location('Updated Option3 WS Amt Sucessfully',180);
1187 end if;
1188
1189
1190 END IF;
1191
1192 -- Option 4 :
1193 IF (l_OPT4_PERSON_RATE_ID IS NOT NULL AND substr(l_DOWNLOAD_SWITCH,5,1) = '2') THEN
1194 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
1195 ,P_WS_VAL => P_OPT4_WS_VAL
1196 ,P_USER_ID => P_USER_ID
1197 ,P_WS_RT_START_DATE => P_OPT4_RT_START_DATE);
1198 if l_pl_person_rate_id is null then
1199 l_pl_person_rate_id :=get_plan_person_rate_id(l_OPT1_PERSON_RATE_ID); end if;
1200 l_diff := UPDATE_WS_AMOUNT(P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1201 ,p_add_val => l_diff
1202 ,P_USER_ID => P_USER_ID
1203 ,P_WS_RT_START_DATE => P_PL_RT_START_DATE);
1204 If g_debug then
1205 hr_utility.set_location('Updated Option4 WS Amt Sucessfully',190);
1206 end if;
1207
1208
1209 END IF;
1210
1211 END IF;
1212
1213 If g_debug then
1214 hr_utility.set_location('Updated Worksheet Amount Sucessfully ',200);
1215 End if;
1216
1217
1218 -- Update Rank
1219
1220 l_group_per_in_ler_id := get_group_per_in_ler_id(l_PL_PERSON_RATE_ID,
1221 l_OPT1_PERSON_RATE_ID,
1222 l_OPT2_PERSON_RATE_ID,
1223 l_OPT3_PERSON_RATE_ID,
1224 l_OPT4_PERSON_RATE_ID);
1225
1226 If g_debug then
1227 hr_utility.set_location('l_group_per_in_ler_id : '||l_group_per_in_ler_id,210);
1228 End if;
1229
1230 IF(substr(l_DOWNLOAD_SWITCH,7,1) = '2') THEN
1231
1232 UPDATE_RANK(P_GROUP_PER_IN_LER_ID => l_group_per_in_ler_id
1233 ,P_RANK => P_RANK
1234 ,P_USER_ID => l_ACTING_PERSON_ID);
1235 If g_debug then
1236 hr_utility.set_location('Updated Rank Sucessfully ',220);
1237 End if;
1238
1239 END IF;
1240
1241 BEN_CWB_SUMMARY_PKG.save_pl_sql_tab;
1242
1243 If g_debug then
1244 hr_utility.set_location('save_pl_sql_tab Sucessfully ',230);
1245 End if;
1246
1247 -- Proposed Performance Rating
1248
1249 IF(substr(l_DOWNLOAD_SWITCH,6,1) = '2') THEN
1250 update_perf_rating(P_PROPOSED_PERFORMANCE_RATING =>P_PROPOSED_PERFORMANCE_RATING
1251 ,P_ACTING_PERSON_ID => l_ACTING_PERSON_ID
1252 ,P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1253 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1254 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1255 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1256 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID);
1257 If g_debug then
1258 hr_utility.set_location('Updated Proposed Performance Rating Sucessfully ',240);
1259 End if;
1260
1261 END IF;
1262
1263 -- Update Person Tasks Table
1264 REFRESH_PERSON_TASKS (P_PERSON_RATE_ID => l_PL_PERSON_RATE_ID
1265 ,P_OPT1_PERSON_RATE_ID => l_OPT1_PERSON_RATE_ID
1266 ,P_OPT2_PERSON_RATE_ID => l_OPT2_PERSON_RATE_ID
1267 ,P_OPT3_PERSON_RATE_ID => l_OPT3_PERSON_RATE_ID
1268 ,P_OPT4_PERSON_RATE_ID => l_OPT4_PERSON_RATE_ID
1269 ,P_TASK_ID => l_TASK_ID
1270 ,P_SEC_MGR_LER_ID => l_SEC_MGR_LER_ID );
1271
1272 If g_debug then
1273 hr_utility.set_location('Update Person Tasks Table Sucessfully ',250);
1274 End if;
1275
1276 -- Update the CPI Flex
1277 -- Update CPI Flex
1278 OPEN csr_cpi_flex_info(l_group_per_in_ler_id);
1279 FETCH csr_cpi_flex_info INTO l_cpi_attribute_category,
1280 l_cpi_attribute1,
1281 l_cpi_attribute2,
1282 l_cpi_attribute3,
1283 l_cpi_attribute4,
1284 l_cpi_attribute5,
1285 l_cpi_attribute6,
1286 l_cpi_attribute7,
1287 l_cpi_attribute8,
1288 l_cpi_attribute9,
1289 l_cpi_attribute10,
1290 l_cpi_attribute11,
1291 l_cpi_attribute12,
1292 l_cpi_attribute13,
1293 l_cpi_attribute14,
1294 l_cpi_attribute15,
1295 l_cpi_attribute16,
1296 l_cpi_attribute17,
1297 l_cpi_attribute18,
1298 l_cpi_attribute19,
1299 l_cpi_attribute20,
1300 l_cpi_attribute21,
1301 l_cpi_attribute22,
1302 l_cpi_attribute23,
1303 l_cpi_attribute24,
1304 l_cpi_attribute25,
1305 l_cpi_attribute26,
1306 l_cpi_attribute27,
1307 l_cpi_attribute28,
1308 l_cpi_attribute29,
1309 l_cpi_attribute30,
1310 l_custom_segment1,
1311 l_custom_segment2,
1312 l_custom_segment3,
1313 l_custom_segment4,
1314 l_custom_segment5,
1315 l_custom_segment6,
1316 l_custom_segment7,
1317 l_custom_segment8,
1318 l_custom_segment9,
1319 l_custom_segment10,
1320 l_custom_segment11,
1321 l_custom_segment12,
1322 l_custom_segment13,
1323 l_custom_segment14,
1324 l_custom_segment15,
1325 l_custom_segment16,
1326 l_custom_segment17,
1327 l_custom_segment18,
1328 l_custom_segment19,
1329 l_custom_segment20,
1330 l_ovn;
1331 CLOSE csr_cpi_flex_info;
1332
1333 FOR l_is_read_only in csr_is_read_only LOOP
1334
1335 IF(l_is_read_only.interface_seq_num = '136' AND l_is_read_only.read_only = 'N') THEN
1336 l_custom_segment1 := check_varchar_col_avble(l_custom_segment1 ,p_custom_segment1);
1337 END IF;
1338 IF(l_is_read_only.interface_seq_num = '137' AND l_is_read_only.read_only = 'N') THEN
1339 l_custom_segment2 := check_varchar_col_avble(l_custom_segment2 ,p_custom_segment2);
1340 END IF;
1341 IF(l_is_read_only.interface_seq_num = '138' AND l_is_read_only.read_only = 'N') THEN
1342 l_custom_segment3 := check_varchar_col_avble(l_custom_segment3 ,p_custom_segment3);
1343 END IF;
1344 IF(l_is_read_only.interface_seq_num = '139' AND l_is_read_only.read_only = 'N') THEN
1345 l_custom_segment4 := check_varchar_col_avble(l_custom_segment4 ,p_custom_segment4);
1346 END IF;
1347 IF(l_is_read_only.interface_seq_num = '140' AND l_is_read_only.read_only = 'N') THEN
1348 l_custom_segment5 := check_varchar_col_avble(l_custom_segment5 ,p_custom_segment5);
1349 END IF;
1350 IF(l_is_read_only.interface_seq_num = '141' AND l_is_read_only.read_only = 'N') THEN
1351 l_custom_segment6 := check_varchar_col_avble(l_custom_segment6 ,p_custom_segment6);
1352 END IF;
1353 IF(l_is_read_only.interface_seq_num = '142' AND l_is_read_only.read_only = 'N') THEN
1354 l_custom_segment7 := check_varchar_col_avble(l_custom_segment7 ,p_custom_segment7);
1355 END IF;
1356 IF(l_is_read_only.interface_seq_num = '143' AND l_is_read_only.read_only = 'N') THEN
1357 l_custom_segment8 := check_varchar_col_avble(l_custom_segment8 ,p_custom_segment8);
1358 END IF;
1359 IF(l_is_read_only.interface_seq_num = '144' AND l_is_read_only.read_only = 'N') THEN
1360 l_custom_segment9 := check_varchar_col_avble(l_custom_segment9 ,p_custom_segment9);
1361 END IF;
1362 IF(l_is_read_only.interface_seq_num = '145' AND l_is_read_only.read_only = 'N') THEN
1363 l_custom_segment10 := check_varchar_col_avble(l_custom_segment10,p_custom_segment10);
1364 END IF;
1365 IF(l_is_read_only.interface_seq_num = '146' AND l_is_read_only.read_only = 'N') THEN
1366 l_custom_segment11 := check_number_col_avble(l_custom_segment11,p_custom_segment11);
1367 END IF;
1368 IF(l_is_read_only.interface_seq_num = '147' AND l_is_read_only.read_only = 'N') THEN
1369 l_custom_segment12 := check_number_col_avble(l_custom_segment12,p_custom_segment12);
1370 END IF;
1371 IF(l_is_read_only.interface_seq_num = '148' AND l_is_read_only.read_only = 'N') THEN
1372 l_custom_segment13 := check_number_col_avble(l_custom_segment13,p_custom_segment13);
1373 END IF;
1374 IF(l_is_read_only.interface_seq_num = '149' AND l_is_read_only.read_only = 'N') THEN
1375 l_custom_segment14 := check_number_col_avble(l_custom_segment14,p_custom_segment14);
1376 END IF;
1377 IF(l_is_read_only.interface_seq_num = '150' AND l_is_read_only.read_only = 'N') THEN
1378 l_custom_segment15 := check_number_col_avble(l_custom_segment15,p_custom_segment15);
1379 END IF;
1380 IF(l_is_read_only.interface_seq_num = '230' AND l_is_read_only.read_only = 'N') THEN
1381 l_custom_segment16 := check_number_col_avble(l_custom_segment16,p_custom_segment16);
1382 END IF;
1383 IF(l_is_read_only.interface_seq_num = '231' AND l_is_read_only.read_only = 'N') THEN
1384 l_custom_segment17 := check_number_col_avble(l_custom_segment17,p_custom_segment17);
1385 END IF;
1386 IF(l_is_read_only.interface_seq_num = '232' AND l_is_read_only.read_only = 'N') THEN
1387 l_custom_segment18 := check_number_col_avble(l_custom_segment18,p_custom_segment18);
1388 END IF;
1389 IF(l_is_read_only.interface_seq_num = '233' AND l_is_read_only.read_only = 'N') THEN
1390 l_custom_segment19 := check_number_col_avble(l_custom_segment19,p_custom_segment19);
1391 END IF;
1392 IF(l_is_read_only.interface_seq_num = '234' AND l_is_read_only.read_only = 'N') THEN
1393 l_custom_segment20 := check_number_col_avble(l_custom_segment20,p_custom_segment20);
1394 END IF;
1395
1396 IF(l_is_read_only.interface_seq_num = '200' AND l_is_read_only.read_only = 'N') THEN
1397 IF(substr(l_DOWNLOAD_SWITCH,8,1) = '2') THEN
1398 l_cpi_attribute1 := check_varchar_col_avble(l_cpi_attribute1 ,p_cpi_attribute1);
1399 If g_debug then
1400 hr_utility.set_location('Upload CPI Attribute1',251);
1401 End if;
1402 END IF;
1403 END IF;
1404 IF(l_is_read_only.interface_seq_num = '201' AND l_is_read_only.read_only = 'N') THEN
1405 IF(substr(l_DOWNLOAD_SWITCH,9,1) = '2') THEN
1406 l_cpi_attribute2 := check_varchar_col_avble(l_cpi_attribute2 ,p_cpi_attribute2);
1407 If g_debug then
1408 hr_utility.set_location('Upload CPI Attribute2',252);
1409 End if;
1410 END IF;
1411 END IF;
1412 IF(l_is_read_only.interface_seq_num = '202' AND l_is_read_only.read_only = 'N') THEN
1413 IF(substr(l_DOWNLOAD_SWITCH,10,1) = '2') THEN
1414 l_cpi_attribute3 := check_varchar_col_avble(l_cpi_attribute3 ,p_cpi_attribute3);
1415 If g_debug then
1416 hr_utility.set_location('Upload CPI Attribute3',253);
1417 End if;
1418 END IF;
1419 END IF;
1420 IF(l_is_read_only.interface_seq_num = '203' AND l_is_read_only.read_only = 'N') THEN
1421 IF(substr(l_DOWNLOAD_SWITCH,11,1) = '2') THEN
1422 l_cpi_attribute4 := check_varchar_col_avble(l_cpi_attribute4 ,p_cpi_attribute4);
1423 If g_debug then
1424 hr_utility.set_location('Upload CPI Attribute4',254);
1425 End if;
1426 END IF;
1427 END IF;
1428 IF(l_is_read_only.interface_seq_num = '204' AND l_is_read_only.read_only = 'N') THEN
1429 IF(substr(l_DOWNLOAD_SWITCH,12,1) = '2') THEN
1430 l_cpi_attribute5 := check_varchar_col_avble(l_cpi_attribute5 ,p_cpi_attribute5);
1431 If g_debug then
1432 hr_utility.set_location('Upload CPI Attribute5',255);
1433 End if;
1434 END IF;
1435 END IF;
1436 IF(l_is_read_only.interface_seq_num = '205' AND l_is_read_only.read_only = 'N') THEN
1437 IF(substr(l_DOWNLOAD_SWITCH,13,1) = '2') THEN
1438 l_cpi_attribute6 := check_varchar_col_avble(l_cpi_attribute6 ,p_cpi_attribute6);
1439 If g_debug then
1440 hr_utility.set_location('Upload CPI Attribute6',256);
1441 End if;
1442 END IF;
1443 END IF;
1444 IF(l_is_read_only.interface_seq_num = '206' AND l_is_read_only.read_only = 'N') THEN
1445 IF(substr(l_DOWNLOAD_SWITCH,14,1) = '2') THEN
1446 l_cpi_attribute7 := check_varchar_col_avble(l_cpi_attribute7 ,p_cpi_attribute7);
1447 If g_debug then
1448 hr_utility.set_location('Upload CPI Attribute7',257);
1449 End if;
1450 END IF;
1451 END IF;
1452 IF(l_is_read_only.interface_seq_num = '207' AND l_is_read_only.read_only = 'N') THEN
1453 IF(substr(l_DOWNLOAD_SWITCH,15,1) = '2') THEN
1454 l_cpi_attribute8 := check_varchar_col_avble(l_cpi_attribute8 ,p_cpi_attribute8);
1455 If g_debug then
1456 hr_utility.set_location('Upload CPI Attribute8',258);
1457 End if;
1458 END IF;
1459 END IF;
1460 IF(l_is_read_only.interface_seq_num = '208' AND l_is_read_only.read_only = 'N') THEN
1461 IF(substr(l_DOWNLOAD_SWITCH,16,1) = '2') THEN
1462 l_cpi_attribute9 := check_varchar_col_avble(l_cpi_attribute9 ,p_cpi_attribute9);
1463 If g_debug then
1464 hr_utility.set_location('Upload CPI Attribute9',259);
1465 End if;
1466 END IF;
1467 END IF;
1468 IF(l_is_read_only.interface_seq_num = '209' AND l_is_read_only.read_only = 'N') THEN
1469 IF(substr(l_DOWNLOAD_SWITCH,17,1) = '2') THEN
1470 l_cpi_attribute10 := check_varchar_col_avble(l_cpi_attribute10 ,p_cpi_attribute10);
1471 If g_debug then
1472 hr_utility.set_location('Upload CPI Attribute10',260);
1473 End if;
1474 END IF;
1475 END IF;
1476 IF(l_is_read_only.interface_seq_num = '210' AND l_is_read_only.read_only = 'N') THEN
1477 IF(substr(l_DOWNLOAD_SWITCH,18,1) = '2') THEN
1478 l_cpi_attribute11 := check_varchar_col_avble(l_cpi_attribute11 ,p_cpi_attribute11);
1479 If g_debug then
1480 hr_utility.set_location('Upload CPI Attribute11',261);
1481 End if;
1482 END IF;
1483 END IF;
1484 IF(l_is_read_only.interface_seq_num = '211' AND l_is_read_only.read_only = 'N') THEN
1485 IF(substr(l_DOWNLOAD_SWITCH,19,1) = '2') THEN
1486 l_cpi_attribute12 := check_varchar_col_avble(l_cpi_attribute12 ,p_cpi_attribute12);
1487 If g_debug then
1488 hr_utility.set_location('Upload CPI Attribute12',262);
1489 End if;
1490 END IF;
1491 END IF;
1492 IF(l_is_read_only.interface_seq_num = '212' AND l_is_read_only.read_only = 'N') THEN
1493 IF(substr(l_DOWNLOAD_SWITCH,20,1) = '2') THEN
1494 l_cpi_attribute13 := check_varchar_col_avble(l_cpi_attribute13 ,p_cpi_attribute13);
1495 If g_debug then
1496 hr_utility.set_location('Upload CPI Attribute13',263);
1497 End if;
1498 END IF;
1499 END IF;
1500 IF(l_is_read_only.interface_seq_num = '213' AND l_is_read_only.read_only = 'N') THEN
1501 IF(substr(l_DOWNLOAD_SWITCH,21,1) = '2') THEN
1502 l_cpi_attribute14 := check_varchar_col_avble(l_cpi_attribute14 ,p_cpi_attribute14);
1503 If g_debug then
1504 hr_utility.set_location('Upload CPI Attribute14',264);
1505 End if;
1506 END IF;
1507 END IF;
1508 IF(l_is_read_only.interface_seq_num = '214' AND l_is_read_only.read_only = 'N') THEN
1509 IF(substr(l_DOWNLOAD_SWITCH,22,1) = '2') THEN
1510 l_cpi_attribute15 := check_varchar_col_avble(l_cpi_attribute15 ,p_cpi_attribute15);
1511 If g_debug then
1512 hr_utility.set_location('Upload CPI Attribute15',265);
1513 End if;
1514 END IF;
1515 END IF;
1516 IF(l_is_read_only.interface_seq_num = '215' AND l_is_read_only.read_only = 'N') THEN
1517 IF(substr(l_DOWNLOAD_SWITCH,23,1) = '2') THEN
1518 l_cpi_attribute16 := check_varchar_col_avble(l_cpi_attribute16 ,p_cpi_attribute16);
1519 If g_debug then
1520 hr_utility.set_location('Upload CPI Attribute16',266);
1521 End if;
1522 END IF;
1523 END IF;
1524 IF(l_is_read_only.interface_seq_num = '216' AND l_is_read_only.read_only = 'N') THEN
1525 IF(substr(l_DOWNLOAD_SWITCH,24,1) = '2') THEN
1526 l_cpi_attribute17 := check_varchar_col_avble(l_cpi_attribute17 ,p_cpi_attribute17);
1527 If g_debug then
1528 hr_utility.set_location('Upload CPI Attribute17',267);
1529 End if;
1530 END IF;
1531 END IF;
1532 IF(l_is_read_only.interface_seq_num = '217' AND l_is_read_only.read_only = 'N') THEN
1533 IF(substr(l_DOWNLOAD_SWITCH,25,1) = '2') THEN
1534 l_cpi_attribute18 := check_varchar_col_avble(l_cpi_attribute18 ,p_cpi_attribute18);
1535 If g_debug then
1536 hr_utility.set_location('Upload CPI Attribute18',268);
1537 End if;
1538 END IF;
1539 END IF;
1540 IF(l_is_read_only.interface_seq_num = '218' AND l_is_read_only.read_only = 'N') THEN
1541 IF(substr(l_DOWNLOAD_SWITCH,26,1) = '2') THEN
1542 l_cpi_attribute19 := check_varchar_col_avble(l_cpi_attribute19 ,p_cpi_attribute19);
1543 If g_debug then
1544 hr_utility.set_location('Upload CPI Attribute19',269);
1545 End if;
1546 END IF;
1547 END IF;
1548 IF(l_is_read_only.interface_seq_num = '219' AND l_is_read_only.read_only = 'N') THEN
1549 IF(substr(l_DOWNLOAD_SWITCH,27,1) = '2') THEN
1550 l_cpi_attribute20 := check_varchar_col_avble(l_cpi_attribute20 ,p_cpi_attribute20);
1551 If g_debug then
1552 hr_utility.set_location('Upload CPI Attribute20',270);
1553 End if;
1554 END IF;
1555 END IF;
1556 IF(l_is_read_only.interface_seq_num = '220' AND l_is_read_only.read_only = 'N') THEN
1557 IF(substr(l_DOWNLOAD_SWITCH,28,1) = '2') THEN
1558 l_cpi_attribute21 := check_varchar_col_avble(l_cpi_attribute21 ,p_cpi_attribute21);
1559 If g_debug then
1560 hr_utility.set_location('Upload CPI Attribute21',271);
1561 End if;
1562 END IF;
1563 END IF;
1564 IF(l_is_read_only.interface_seq_num = '221' AND l_is_read_only.read_only = 'N') THEN
1565 IF(substr(l_DOWNLOAD_SWITCH,29,1) = '2') THEN
1566 l_cpi_attribute22 := check_varchar_col_avble(l_cpi_attribute22 ,p_cpi_attribute22);
1567 If g_debug then
1568 hr_utility.set_location('Upload CPI Attribute22',272);
1569 End if;
1570 END IF;
1571 END IF;
1572 IF(l_is_read_only.interface_seq_num = '222' AND l_is_read_only.read_only = 'N') THEN
1573 IF(substr(l_DOWNLOAD_SWITCH,30,1) = '2') THEN
1574 l_cpi_attribute23 := check_varchar_col_avble(l_cpi_attribute23 ,p_cpi_attribute23);
1575 If g_debug then
1576 hr_utility.set_location('Upload CPI Attribute23',273);
1577 End if;
1578 END IF;
1579 END IF;
1580 IF(l_is_read_only.interface_seq_num = '223' AND l_is_read_only.read_only = 'N') THEN
1581 IF(substr(l_DOWNLOAD_SWITCH,31,1) = '2') THEN
1582 l_cpi_attribute24 := check_varchar_col_avble(l_cpi_attribute24 ,p_cpi_attribute24);
1583 If g_debug then
1584 hr_utility.set_location('Upload CPI Attribute24',274);
1585 End if;
1586 END IF;
1587 END IF;
1588 IF(l_is_read_only.interface_seq_num = '224' AND l_is_read_only.read_only = 'N') THEN
1589 IF(substr(l_DOWNLOAD_SWITCH,32,1) = '2') THEN
1590 l_cpi_attribute25 := check_varchar_col_avble(l_cpi_attribute25 ,p_cpi_attribute25);
1591 If g_debug then
1592 hr_utility.set_location('Upload CPI Attribute25',275);
1593 End if;
1594 END IF;
1595 END IF;
1596 IF(l_is_read_only.interface_seq_num = '225' AND l_is_read_only.read_only = 'N') THEN
1597 IF(substr(l_DOWNLOAD_SWITCH,33,1) = '2') THEN
1598 l_cpi_attribute26 := check_varchar_col_avble(l_cpi_attribute26 ,p_cpi_attribute26);
1599 If g_debug then
1600 hr_utility.set_location('Upload CPI Attribute26',276);
1601 End if;
1602 END IF;
1603 END IF;
1604 IF(l_is_read_only.interface_seq_num = '226' AND l_is_read_only.read_only = 'N') THEN
1605 IF(substr(l_DOWNLOAD_SWITCH,34,1) = '2') THEN
1606 l_cpi_attribute27 := check_varchar_col_avble(l_cpi_attribute27 ,p_cpi_attribute27);
1607 If g_debug then
1608 hr_utility.set_location('Upload CPI Attribute27',277);
1609 End if;
1610 END IF;
1611 END IF;
1612 IF(l_is_read_only.interface_seq_num = '227' AND l_is_read_only.read_only = 'N') THEN
1613 IF(substr(l_DOWNLOAD_SWITCH,35,1) = '2') THEN
1614 l_cpi_attribute28 := check_varchar_col_avble(l_cpi_attribute28 ,p_cpi_attribute28);
1615 If g_debug then
1616 hr_utility.set_location('Upload CPI Attribute28',278);
1617 End if;
1618 END IF;
1619 END IF;
1620 IF(l_is_read_only.interface_seq_num = '228' AND l_is_read_only.read_only = 'N') THEN
1621 IF(substr(l_DOWNLOAD_SWITCH,36,1) = '2') THEN
1622 l_cpi_attribute29 := check_varchar_col_avble(l_cpi_attribute29 ,p_cpi_attribute29);
1623 If g_debug then
1624 hr_utility.set_location('Upload CPI Attribute29',279);
1625 End if;
1626 END IF;
1627 END IF;
1628 IF(l_is_read_only.interface_seq_num = '229' AND l_is_read_only.read_only = 'N') THEN
1629 IF(substr(l_DOWNLOAD_SWITCH,37,1) = '2') THEN
1630 l_cpi_attribute30 := check_varchar_col_avble(l_cpi_attribute30 ,p_cpi_attribute30);
1631 If g_debug then
1632 hr_utility.set_location('Upload CPI Attribute30',280);
1633 End if;
1634 END IF;
1635 END IF;
1636
1637 END LOOP;
1638
1639 BEN_CWB_PERSON_INFO_API.update_person_info
1640 ( P_GROUP_PER_IN_LER_ID => l_GROUP_PER_IN_LER_ID
1641 ,P_CPI_ATTRIBUTE_CATEGORY => P_CPI_ATTRIBUTE_CATEGORY
1642 ,P_CPI_ATTRIBUTE1 => L_CPI_ATTRIBUTE1
1643 ,P_CPI_ATTRIBUTE2 => L_CPI_ATTRIBUTE2
1644 ,P_CPI_ATTRIBUTE3 => l_CPI_ATTRIBUTE3
1645 ,P_CPI_ATTRIBUTE4 => l_CPI_ATTRIBUTE4
1646 ,P_CPI_ATTRIBUTE5 => l_CPI_ATTRIBUTE5
1647 ,P_CPI_ATTRIBUTE6 => l_CPI_ATTRIBUTE6
1648 ,P_CPI_ATTRIBUTE7 => l_CPI_ATTRIBUTE7
1649 ,P_CPI_ATTRIBUTE8 => l_CPI_ATTRIBUTE8
1650 ,P_CPI_ATTRIBUTE9 => l_CPI_ATTRIBUTE9
1651 ,P_CPI_ATTRIBUTE10 => l_CPI_ATTRIBUTE10
1652 ,P_CPI_ATTRIBUTE11 => l_CPI_ATTRIBUTE11
1653 ,P_CPI_ATTRIBUTE12 => l_CPI_ATTRIBUTE12
1654 ,P_CPI_ATTRIBUTE13 => l_CPI_ATTRIBUTE13
1655 ,P_CPI_ATTRIBUTE14 => l_CPI_ATTRIBUTE14
1656 ,P_CPI_ATTRIBUTE15 => l_CPI_ATTRIBUTE15
1657 ,P_CPI_ATTRIBUTE16 => l_CPI_ATTRIBUTE16
1658 ,P_CPI_ATTRIBUTE17 => l_CPI_ATTRIBUTE17
1659 ,P_CPI_ATTRIBUTE18 => l_CPI_ATTRIBUTE18
1660 ,P_CPI_ATTRIBUTE19 => l_CPI_ATTRIBUTE19
1661 ,P_CPI_ATTRIBUTE20 => l_CPI_ATTRIBUTE20
1662 ,P_CPI_ATTRIBUTE21 => l_CPI_ATTRIBUTE21
1663 ,P_CPI_ATTRIBUTE22 => l_CPI_ATTRIBUTE22
1664 ,P_CPI_ATTRIBUTE23 => l_CPI_ATTRIBUTE23
1665 ,P_CPI_ATTRIBUTE24 => l_CPI_ATTRIBUTE24
1666 ,P_CPI_ATTRIBUTE25 => l_CPI_ATTRIBUTE25
1667 ,P_CPI_ATTRIBUTE26 => l_CPI_ATTRIBUTE26
1668 ,P_CPI_ATTRIBUTE27 => l_CPI_ATTRIBUTE27
1669 ,P_CPI_ATTRIBUTE28 => l_CPI_ATTRIBUTE28
1670 ,P_CPI_ATTRIBUTE29 => l_CPI_ATTRIBUTE29
1671 ,P_CPI_ATTRIBUTE30 => l_CPI_ATTRIBUTE30
1672 ,P_CUSTOM_SEGMENT1 => l_CUSTOM_SEGMENT1
1673 ,P_CUSTOM_SEGMENT2 => l_CUSTOM_SEGMENT2
1674 ,P_CUSTOM_SEGMENT3 => l_CUSTOM_SEGMENT3
1675 ,P_CUSTOM_SEGMENT4 => l_CUSTOM_SEGMENT4
1676 ,P_CUSTOM_SEGMENT5 => l_CUSTOM_SEGMENT5
1677 ,P_CUSTOM_SEGMENT6 => l_CUSTOM_SEGMENT6
1678 ,P_CUSTOM_SEGMENT7 => l_CUSTOM_SEGMENT7
1679 ,P_CUSTOM_SEGMENT8 => l_CUSTOM_SEGMENT8
1680 ,P_CUSTOM_SEGMENT9 => l_CUSTOM_SEGMENT9
1681 ,P_CUSTOM_SEGMENT10 => l_CUSTOM_SEGMENT10
1682 ,P_CUSTOM_SEGMENT11 => l_CUSTOM_SEGMENT11
1683 ,P_CUSTOM_SEGMENT12 => l_CUSTOM_SEGMENT12
1684 ,P_CUSTOM_SEGMENT13 => l_CUSTOM_SEGMENT13
1685 ,P_CUSTOM_SEGMENT14 => l_CUSTOM_SEGMENT14
1686 ,P_CUSTOM_SEGMENT15 => l_CUSTOM_SEGMENT15
1687 ,P_CUSTOM_SEGMENT16 => l_CUSTOM_SEGMENT16
1688 ,P_CUSTOM_SEGMENT17 => l_CUSTOM_SEGMENT17
1689 ,P_CUSTOM_SEGMENT18 => l_CUSTOM_SEGMENT18
1690 ,P_CUSTOM_SEGMENT19 => l_CUSTOM_SEGMENT19
1691 ,P_CUSTOM_SEGMENT20 => l_CUSTOM_SEGMENT20
1692 ,P_OBJECT_VERSION_NUMBER => L_OVN);
1693 If g_debug then
1694 hr_utility.set_location('Updated CPI Flex Rating Sucessfully ',281);
1695 End if;
1696
1697 --
1698 -- Call the routine for dynamic calculations.
1699 --
1700 open csr_get_group_plan_info(l_group_per_in_ler_id);
1701 fetch csr_get_group_plan_info into l_group_pl_id, l_lf_evt_ocrd_dt;
1702 close csr_get_group_plan_info;
1703 --
1704 ben_cwb_dyn_calc_pkg.run_dynamic_calculations(
1705 p_group_per_in_ler_id => l_group_per_in_ler_id
1706 ,p_group_pl_id => l_group_pl_id
1707 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1708 ,p_raise_error => true);
1709 --
1710
1711 If g_debug then
1712 hr_utility.set_location('Leaving '||l_proc,290);
1713 End if;
1714
1715 EXCEPTION
1716 WHEN Others THEN
1717 if g_debug then
1718 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 300);
1719 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 301);
1720 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 302);
1721 end if;
1722
1723 Rollback to Update_data;
1724
1725 END handle_row;
1726
1727 function check_varchar_col_avble(old_val varchar2, new_val varchar2)
1728 return varchar2
1729 is
1730 begin
1731 if nvl(new_val, 'X') <> default_string then
1732 return new_val;
1733 else
1734 return old_val;
1735 end if;
1736 end check_varchar_col_avble;
1737
1738 function check_number_col_avble(old_val number, new_val number)
1739 return number
1740 is
1741 begin
1742 if nvl(new_val, 1) <> default_number then
1743 return new_val;
1744 else
1745 return old_val;
1746 end if;
1747 end check_number_col_avble;
1748
1749 END BEN_CWB_WS_IMPORT_PKG;
1750