[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_WEBADI_UTILS
Source
1 Package Body BEN_CWB_WEBADI_UTILS as
2 /* $Header: bencwbadiutl.pkb 120.20.12020000.2 2012/07/03 12:19:04 amnaraya ship $ */
3
4 --key_string VARCHAR2(32) := 'A!190j2#Az19?j1@A!190j2#Az19?j1@';
5 ---added for custom download
6 g_interface_code VARCHAR2(40);
7
8 l_layout_lock_time VARCHAR2(5) := FND_PROFILE.VALUE('BEN_CWB_LAYOUT_LOCK_TIME');
9 key_string VARCHAR2(16) := substr(FND_PROFILE.VALUE('BEN_CWB_ENCRYPT_KEY'),1,16);
10
11 g_package Varchar2(30) := 'BEN_CWB_WEBADI_UTILS.';
12 g_debug boolean := hr_utility.debug_enabled;
13
14 --
15 --------------------------create_cwb_layout_row----------------------
16 --
17
18 PROCEDURE create_cwb_layout_row(
19 p_layout_code IN VARCHAR2
20 ,p_user_name IN VARCHAR2
21 ,p_base_layout_code IN VARCHAR2)
22 IS
23
24 l_rowid VARCHAR2(200);
25 no_default_layout EXCEPTION;
26 l_proc varchar2(72) := g_package||'create_cwb_layout_row';
27
28
29 CURSOR c_layout_row
30 IS
31 SELECT application_id
32 ,object_version_number
33 ,stylesheet_app_id
34 ,stylesheet_code
35 ,integrator_app_id
36 ,integrator_code
37 ,style
38 ,style_class
39 ,reporting_flag
40 ,reporting_interface_app_id
41 ,reporting_interface_code
42 ,created_by
43 ,last_updated_by
44 ,last_update_login
45 FROM bne_layouts_b
46 WHERE application_id = 800
47 AND layout_code = p_base_layout_code;
48
49 l_layout_row c_layout_row%ROWTYPE;
50
51
52 BEGIN
53
54 OPEN c_layout_row;
55 FETCH c_layout_row INTO l_layout_row;
56 IF c_layout_row%NOTFOUND THEN
57 RAISE no_default_layout;
58 END IF;
59 CLOSE c_layout_row;
60
61 bne_layouts_pkg.insert_row
62 (x_rowid => l_rowid
63 ,x_application_id => l_layout_row.application_id
64 ,x_layout_code => p_layout_code
65 ,x_object_version_number => 1
66 ,x_stylesheet_app_id => l_layout_row.stylesheet_app_id
67 ,x_stylesheet_code => l_layout_row.stylesheet_code
68 ,x_integrator_app_id => l_layout_row.integrator_app_id
69 ,x_integrator_code => l_layout_row.integrator_code
70 ,x_style => l_layout_row.style
71 ,x_style_class => l_layout_row.style_class
72 ,x_reporting_flag => l_layout_row.reporting_flag
73 ,x_reporting_interface_app_id => l_layout_row.reporting_interface_app_id
74 ,x_reporting_interface_code => l_layout_row.reporting_interface_code
75 ,x_user_name => p_user_name
76 ,x_creation_date => SYSDATE
77 ,x_created_by => l_layout_row.created_by
78 ,x_last_update_date => SYSDATE
79 ,x_last_updated_by => l_layout_row.last_updated_by
80 ,x_last_update_login => l_layout_row.last_update_login);
81 END create_cwb_layout_row;
82
83 --
84 --------------------------create_cwb_layout_blocks_row----------------------
85 --
86
87 PROCEDURE create_cwb_layout_blocks_row(
88 p_layout_code IN VARCHAR2
89 ,p_user_name IN VARCHAR2
90 ,p_base_layout_code IN VARCHAR2)
91 IS
92
93 l_rowid VARCHAR2(200);
94 CURSOR c_layout_blocks_row IS
95 SELECT application_id
96 ,block_id
97 ,parent_id
98 ,layout_element
99 ,style_class
100 ,style
101 ,row_style_class
102 ,row_style
103 ,col_style_class
104 ,col_style
105 ,prompt_displayed_flag
106 ,prompt_style_class
107 ,prompt_style
108 ,hint_displayed_flag
109 ,hint_style_class
110 ,hint_style
111 ,orientation
112 ,layout_control
113 ,display_flag
114 ,BLOCKSIZE
115 ,minsize
116 ,MAXSIZE
117 ,sequence_num
118 ,prompt_colspan
119 ,hint_colspan
120 ,row_colspan
121 ,summary_style_class
122 ,summary_style
123 ,created_by
124 ,last_updated_by
125 ,last_update_login
126 FROM bne_layout_blocks_b
127 WHERE application_id = 800
128 AND layout_code = p_base_layout_code
129 ORDER BY block_id;
130
131 l_layout_blocks_row c_layout_blocks_row%ROWTYPE;
132 BEGIN
133
134 OPEN c_layout_blocks_row;
135 LOOP
136 FETCH c_layout_blocks_row INTO l_layout_blocks_row;
137 EXIT WHEN c_layout_blocks_row%NOTFOUND;
138 bne_layout_blocks_pkg.insert_row
139 (x_rowid => l_rowid
140 ,x_application_id => l_layout_blocks_row.application_id
141 ,x_layout_code => p_layout_code
142 ,x_block_id => l_layout_blocks_row.block_id
143 ,x_object_version_number => 1
144 ,x_parent_id => l_layout_blocks_row.parent_id
145 ,x_layout_element => l_layout_blocks_row.layout_element
146 ,x_style_class => l_layout_blocks_row.style_class
147 ,x_style => l_layout_blocks_row.style
148 ,x_row_style_class => l_layout_blocks_row.row_style_class
149 ,x_row_style => l_layout_blocks_row.row_style
150 ,x_col_style_class => l_layout_blocks_row.col_style_class
151 ,x_col_style => l_layout_blocks_row.col_style
152 ,x_prompt_displayed_flag => l_layout_blocks_row.prompt_displayed_flag
153 ,x_prompt_style_class => l_layout_blocks_row.prompt_style_class
154 ,x_prompt_style => l_layout_blocks_row.prompt_style
155 ,x_hint_displayed_flag => l_layout_blocks_row.hint_displayed_flag
156 ,x_hint_style_class => l_layout_blocks_row.hint_style_class
157 ,x_hint_style => l_layout_blocks_row.hint_style
158 ,x_orientation => l_layout_blocks_row.orientation
159 ,x_layout_control => l_layout_blocks_row.layout_control
160 ,x_display_flag => l_layout_blocks_row.display_flag
161 ,x_blocksize => l_layout_blocks_row.BLOCKSIZE
162 ,x_minsize => l_layout_blocks_row.minsize
163 ,x_maxsize => l_layout_blocks_row.MAXSIZE
164 ,x_sequence_num => l_layout_blocks_row.sequence_num
165 ,x_prompt_colspan => l_layout_blocks_row.prompt_colspan
166 ,x_hint_colspan => l_layout_blocks_row.hint_colspan
167 ,x_row_colspan => l_layout_blocks_row.row_colspan
168 ,x_summary_style_class => l_layout_blocks_row.summary_style_class
169 ,x_summary_style => l_layout_blocks_row.summary_style
170 ,x_user_name => p_user_name
171 ,x_creation_date => SYSDATE
172 ,x_created_by => l_layout_blocks_row.created_by
173 ,x_last_update_date => SYSDATE
174 ,x_last_updated_by => l_layout_blocks_row.last_updated_by
175 ,x_last_update_login => l_layout_blocks_row.last_update_login);
176 END LOOP;
177
178 CLOSE c_layout_blocks_row;
179 END create_cwb_layout_blocks_row;
180
181 --
182 --------------------------create_cwb_layout_cols_row----------------------
183 --
184
185 PROCEDURE create_cwb_layout_cols_row(p_layout_code IN VARCHAR2
186 ,p_base_layout_code IN VARCHAR2) IS
187 l_rowid VARCHAR2(200);
188
189 CURSOR c_layout_cols_row IS
190 SELECT application_id
191 ,layout_code
192 ,block_id
193 ,interface_app_id
194 ,interface_code
195 ,interface_seq_num
196 ,sequence_num
197 ,style
198 ,style_class
199 ,hint_style
200 ,hint_style_class
201 ,prompt_style
202 ,prompt_style_class
203 ,default_type
204 ,DEFAULT_VALUE
205 ,created_by
206 ,last_updated_by
207 ,last_update_login
208 ,read_only_flag
209 FROM bne_layout_cols
210 WHERE application_id = 800
211 AND layout_code = p_base_layout_code
212 ORDER BY block_id;
213
214 l_layout_cols_row c_layout_cols_row%ROWTYPE;
215
216 l_read_only_flag VARCHAR2(1) := NULL;
217 l_display_width NUMBER := NULL;
218
219 BEGIN
220 OPEN c_layout_cols_row;
221 LOOP
222 FETCH c_layout_cols_row INTO l_layout_cols_row;
223
224 EXIT WHEN c_layout_cols_row%NOTFOUND;
225
226 IF ((substr(p_base_layout_code,1,16) = 'BEN_CWB_WS_LYT1_')
227 OR (p_base_layout_code = 'BEN_CWB_WRK_SHT_BASE_LYT')) THEN
228 l_read_only_flag := 'Y';
229 --Hide the contextual security keys
230 IF (l_layout_cols_row.interface_seq_num IN (158, 189, 194, 195, 196, 198)) then
231 l_display_width := 0;
232 --Hide the security keys in lines
233 ELSIF (l_layout_cols_row.interface_seq_num IN (130, 131, 132, 133, 134)) then
234 l_display_width := 0;
235 l_read_only_flag := 'N';
236 --Make non-updateable columns as read-only
237 ELSIF (l_layout_cols_row.interface_seq_num IN (3,12,30,48,66,84,151,152,192,191,126) OR
238 l_layout_cols_row.interface_seq_num BETWEEN 200 AND 234 OR
239 -- ER : ability to update other rates and custom segments
240 l_layout_cols_row.interface_seq_num BETWEEN 136 AND 150 OR
241 l_layout_cols_row.interface_seq_num in (165,8,26,44,62,80,10,28,46,64,82,11,29,47,65,83,19,20,21,37,38,39,55,56,57,73,74,75,91,92,93)
242 OR l_layout_cols_row.interface_seq_num in ('246','247','248')
243 --changed by KMG included the below condition
244 OR l_layout_cols_row.interface_seq_num BETWEEN 240 and 244) THEN
245 l_read_only_flag := 'N';
246 END IF;
247 IF(l_read_only_flag = 'N') THEN
248 l_read_only_flag := l_layout_cols_row.read_only_flag;
249 END IF;
250 END IF;
251
252
253 bne_layout_cols_pkg.insert_row
254 (x_rowid => l_rowid
255 ,x_application_id => l_layout_cols_row.application_id
256 ,x_layout_code => p_layout_code
257 ,x_block_id => l_layout_cols_row.block_id
258 ,x_sequence_num => l_layout_cols_row.sequence_num
259 ,x_object_version_number => 1
260 ,x_interface_app_id => l_layout_cols_row.interface_app_id
261 ,x_interface_code => l_layout_cols_row.interface_code
262 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
263 ,x_style_class => l_layout_cols_row.style_class
264 ,x_hint_style => l_layout_cols_row.hint_style
265 ,x_hint_style_class => l_layout_cols_row.hint_style_class
266 ,x_prompt_style => l_layout_cols_row.prompt_style
267 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
268 ,x_default_type => l_layout_cols_row.default_type
269 ,x_default_value => l_layout_cols_row.DEFAULT_VALUE
270 ,x_style => l_layout_cols_row.style
271 ,x_creation_date => SYSDATE
272 ,x_created_by => l_layout_cols_row.created_by
273 ,x_last_update_date => SYSDATE
274 ,x_last_updated_by => l_layout_cols_row.last_updated_by
275 ,x_last_update_login => l_layout_cols_row.last_update_login
276 ,x_read_only_flag => l_read_only_flag
277 ,x_display_width => l_display_width);
278 END LOOP;
279 CLOSE c_layout_cols_row;
280
281 END create_cwb_layout_cols_row;
282
283 --
284 --------------------------create_cwb_layout----------------------
285 --
286
287 PROCEDURE create_cwb_layout(
288 p_layout_code IN VARCHAR2
289 ,p_user_name IN VARCHAR2
290 ,p_base_layout_code IN VARCHAR2) IS
291 BEGIN
292 create_cwb_layout_row(p_layout_code => p_layout_code
293 ,p_user_name => p_user_name
294 ,p_base_layout_code => p_base_layout_code);
295
296 create_cwb_layout_blocks_row(p_layout_code => p_layout_code
297 ,p_user_name => p_user_name
298 ,p_base_layout_code => p_base_layout_code);
299
300 create_cwb_layout_cols_row(p_layout_code => p_layout_code
301 ,p_base_layout_code => p_base_layout_code);
302
303 END create_cwb_layout;
304
305 --
306 --------------------------delete_cwb_layout_cols----------------------
307 --
308
312
309 PROCEDURE delete_cwb_layout_cols(p_layout_code IN VARCHAR2
310 ,p_application_id IN NUMBER )
311 IS
313 l_proc Varchar2(72):= 'delete_cwb_layout_cols';
314 CURSOR c_layout_cols
315 IS
316 SELECT blc.application_id
317 ,blc.layout_code
318 ,blc.block_id
319 ,blc.sequence_num
320 FROM bne_layout_cols blc
321 WHERE blc.application_id = p_application_id
322 AND blc.layout_code = p_layout_code;
323
324 BEGIN
325 hr_utility.set_location('Entering '||l_proc,10);
326 For l_layout_col_rec In c_layout_cols
327 LOOP
328 hr_utility.set_location('Seq Num :'||l_layout_col_rec.sequence_num,25);
329 bne_layout_cols_pkg.delete_row
330 (x_application_id => l_layout_col_rec.application_id
331 ,x_layout_code => l_layout_col_rec.layout_code
332 ,x_block_id => l_layout_col_rec.block_id
333 ,x_sequence_num => l_layout_col_rec.sequence_num);
334 END LOOP;
335 hr_utility.set_location('Leaving '||l_proc,100);
336 EXCEPTION
337 WHEN OTHERS THEN
338 hr_utility.set_location('ERROR occured',30);
339 Null;
340 END delete_cwb_layout_cols;
341
342 --
343 ----------------------------check_hidden_worksheet_columns ------------------------
344 --
345 procedure check_hidden_worksheet_columns( p_group_pl_id IN NUMBER
346 ,p_lf_evt_ocrd_dt IN DATE
347 ,p_show_hide_data OUT NOCOPY p_show_hide_data
348 )
349 IS
350
351 l_show_hide_data ben_cwb_webadi_utils.p_show_hide_data;
352
353 cursor group_opt_exists
354 IS
355 select count(group_oipl_id) IdCount
356 ,max(ws_abr_id) ws_abr_id
357 ,max(elig_sal_abr_id) elig_sal_abr_id
358 ,max(ws_nnmntry_uom) ws_nnmntry_uom
359 ,null ws_sub_acty_typ_cd
360 from ben_cwb_pl_dsgn
361 where group_pl_id = p_group_pl_id
362 and group_pl_id = pl_id
363 and group_oipl_id = oipl_id
364 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
365 and group_oipl_id <> -1;
366
367 cursor actual_opt_exists
368 IS
369 select count(oipl_id) IdCount
370 ,max(ws_abr_id) ws_abr_id
371 ,max(elig_sal_abr_id) elig_sal_abr_id
372 ,max(ws_nnmntry_uom) ws_nnmntry_uom
373 ,null ws_sub_acty_typ_cd
374 from ben_cwb_pl_dsgn
375 where group_pl_id = p_group_pl_id
376 and group_pl_id = pl_id
377 and group_oipl_id <> oipl_id
378 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
379 and group_oipl_id <> -1;
380
381 cursor opt1_exists
382 IS
383 select count(group_oipl_id) IdCount
384 ,max(ws_abr_id) ws_abr_id
385 ,max(elig_sal_abr_id) elig_sal_abr_id
386 ,max(ws_nnmntry_uom) ws_nnmntry_uom
387 ,null ws_sub_acty_typ_cd
388 from ben_cwb_pl_dsgn
389 where group_pl_id = p_group_pl_id
390 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
391 and group_oipl_id <> -1
392 and oipl_ordr_num = 1;
393
394 cursor opt2_exists
395 IS
396 select count(group_oipl_id) IdCount
397 ,max(ws_abr_id) ws_abr_id
398 ,max(elig_sal_abr_id) elig_sal_abr_id
399 ,max(ws_nnmntry_uom) ws_nnmntry_uom
400 ,null ws_sub_acty_typ_cd
401 from ben_cwb_pl_dsgn
402 where group_pl_id = p_group_pl_id
403 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
404 and group_oipl_id <> -1
405 and oipl_ordr_num = 2;
406
407 cursor opt3_exists
408 IS
409 select count(group_oipl_id) IdCount
410 ,max(ws_abr_id) ws_abr_id
411 ,max(elig_sal_abr_id) elig_sal_abr_id
412 ,max(ws_nnmntry_uom) ws_nnmntry_uom
413 ,null ws_sub_acty_typ_cd
414 from ben_cwb_pl_dsgn
415 where group_pl_id = p_group_pl_id
416 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
417 and group_oipl_id <> -1
418 and oipl_ordr_num = 3;
419
420 cursor opt4_exists
421 IS
422 select count(group_oipl_id) IdCount
423 ,max(ws_abr_id) ws_abr_id
424 ,max(elig_sal_abr_id) elig_sal_abr_id
425 ,max(ws_nnmntry_uom) ws_nnmntry_uom
426 ,null ws_sub_acty_typ_cd
427 from ben_cwb_pl_dsgn
428 where group_pl_id = p_group_pl_id
429 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
430 and group_oipl_id <> -1
431 and oipl_ordr_num = 4;
432
433 cursor actual_plans
434 IS
435 select count(pl_id) IdCount
436 ,max(ws_abr_id) ws_abr_id
437 ,max(elig_sal_abr_id) elig_sal_abr_id
438 ,max(ws_nnmntry_uom) ws_nnmntry_uom
439 ,max(ws_sub_acty_typ_cd) ws_sub_acty_typ_cd
440 from ben_cwb_pl_dsgn
441 where group_pl_id = p_group_pl_id
442 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
443 and group_pl_id <> pl_id
444 and group_oipl_id = -1
445 and oipl_id = -1;
446
447 cursor group_plan
448 IS
449 select count(group_pl_id) IdCount
450 ,max(ws_abr_id) ws_abr_id
451 ,max(elig_sal_abr_id) elig_sal_abr_id
452 ,max(ws_nnmntry_uom) ws_nnmntry_uom
453 ,max(ws_sub_acty_typ_cd) ws_sub_acty_typ_cd
454 from ben_cwb_pl_dsgn
455 where group_pl_id = p_group_pl_id
456 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
457 and group_pl_id = pl_id
461 l_group_opt_exists_rec group_opt_exists%RowType;
458 and group_oipl_id = -1
459 and oipl_id = -1;
460
462 l_actual_opt_exists_rec actual_opt_exists%RowType;
463 l_group_plan_rec group_plan%RowType;
464 l_actual_plans_rec actual_plans%RowType;
465 l_opt1_exists_rec opt1_exists%RowType;
466 l_opt2_exists_rec opt2_exists%RowType;
467 l_opt3_exists_rec opt3_exists%RowType;
468 l_opt4_exists_rec opt4_exists%RowType;
469 l_count Number;
470
471
472 begin
473 Open group_opt_exists;
474 Fetch group_opt_exists into l_group_opt_exists_rec;
475 Close group_opt_exists;
476
477 Open actual_opt_exists;
478 Fetch actual_opt_exists into l_actual_opt_exists_rec;
479 Close actual_opt_exists;
480
481 Open actual_plans;
482 Fetch actual_plans into l_actual_plans_rec;
483 Close actual_plans;
484
485 Open group_plan;
486 Fetch group_plan into l_group_plan_rec;
487 Close group_plan;
488
489
490 If (l_group_opt_exists_rec.IdCount <> 0 OR l_actual_opt_exists_rec.IdCount <> 0) Then
491
492 -- If Group Options Exists
493 Begin
494 hr_utility.set_location('Options Exists',50);
495 Open opt1_exists;
496 Fetch opt1_exists into l_opt1_exists_rec;
497 Close opt1_exists;
498
499 Open opt2_exists;
500 Fetch opt2_exists into l_opt2_exists_rec;
501 Close opt2_exists;
502
503 Open opt3_exists;
504 Fetch opt3_exists into l_opt3_exists_rec;
505 Close opt3_exists;
506
507 Open opt4_exists;
508 Fetch opt4_exists into l_opt4_exists_rec;
509 Close opt4_exists;
510
511 ----------------------------- Option 1 ------------------------------------------
512 l_count := l_show_hide_data.count +1 ;
513 If (l_opt1_exists_rec.IdCount <> 0) then
514 l_show_hide_data(l_count).p_type := 'OPT1';
515 l_show_hide_data(l_count).p_opt_defined := 'Y';
516
517 hr_utility.set_location('Opt1 Exists .. Count :'||l_count,100);
518 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_type,105);
519
520
521 If l_opt1_exists_rec.ws_abr_id is not Null Then
522 l_show_hide_data(l_count).p_ws_defined := 'Y';
523 Else
524 l_show_hide_data(l_count).p_ws_defined := 'N';
525 End if;
526
527 If l_opt1_exists_rec.elig_sal_abr_id is not Null Then
528 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
529 Else
530 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
531 End if;
532
533 If l_opt1_exists_rec.ws_nnmntry_uom is not Null Then
534 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
535 Else
536 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
537 End if;
538 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_type,105);
539 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_ws_defined,115);
540 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,120);
541 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_nnmntry_uom,125);
542
543 Else
544 l_show_hide_data(l_count).p_type := 'OPT1';
545 l_show_hide_data(l_count).p_opt_defined := 'N';
546 hr_utility.set_location('Opt1 Not Exists',120);
547 End If;
548
549 ----------------------------- Option 2 ------------------------------------------
550 l_count := l_show_hide_data.count +1 ;
551 If (l_opt2_exists_rec.IdCount <> 0) then
552 l_show_hide_data(l_count).p_type := 'OPT2';
553 l_show_hide_data(l_count).p_opt_defined := 'Y';
554
555 hr_utility.set_location('Opt2 Exists... Count :'||l_count,170);
556 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_type,175);
557
558 If l_opt2_exists_rec.ws_abr_id is not Null Then
559 l_show_hide_data(l_count).p_ws_defined := 'Y';
560 Else
561 l_show_hide_data(l_count).p_ws_defined := 'N';
562 End if;
563
564 If l_opt2_exists_rec.elig_sal_abr_id is not Null Then
565 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
566 Else
567 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
568 End if;
569
570 If l_opt2_exists_rec.ws_nnmntry_uom is not Null Then
571 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
572 Else
573 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
574 End if;
575 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_type,250);
576 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_ws_defined,255);
577 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,260);
578 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_nnmntry_uom,265);
579
580 Else
581 hr_utility.set_location('Opt2 Not Exists',240);
582 l_show_hide_data(l_count).p_type := 'OPT2';
586
583 l_show_hide_data(l_count).p_opt_defined := 'N';
584 End If;
585
587 ----------------------------- Option 3 ------------------------------------------
588 l_count := l_show_hide_data.count +1 ;
589 If (l_opt3_exists_rec.IdCount <> 0) then
590
591 l_show_hide_data(l_count).p_type := 'OPT3';
592 l_show_hide_data(l_count).p_opt_defined := 'Y';
593
594 hr_utility.set_location('Opt3 Exists...Count :'||l_count,300);
595 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_type,305);
596 If l_opt3_exists_rec.ws_abr_id is not Null Then
597 l_show_hide_data(l_count).p_ws_defined := 'Y';
598 Else
599 l_show_hide_data(l_count).p_ws_defined := 'N';
600 End if;
601
602 If l_opt3_exists_rec.elig_sal_abr_id is not Null Then
603 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
604 Else
605 l_show_hide_data(l_count).p_eligy_sal_defined:= 'N';
606 End if;
607
608 If l_opt3_exists_rec.ws_nnmntry_uom is not Null Then
609 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
610 Else
611 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
612 End if;
613 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_type,355);
614 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_ws_defined,360);
615 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,365);
619 l_show_hide_data(l_count).p_type := 'OPT3';
616 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_nnmntry_uom,370);
617
618 Else
620 l_show_hide_data(l_count).p_opt_defined := 'N';
621 hr_utility.set_location('Opt3 Not Exists',350);
622 End If;
623
624 ----------------------------- Option 4 ------------------------------------------
625 l_count := l_show_hide_data.count +1 ;
626 If (l_opt4_exists_rec.IdCount <> 0) then
627
628 l_show_hide_data(l_count).p_type := 'OPT4';
629 l_show_hide_data(l_count).p_opt_defined := 'Y';
630
631 hr_utility.set_location('Opt4 Exists...Count :'||l_count,400);
632 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_type,405);
633 If l_opt4_exists_rec.ws_abr_id is not Null Then
634 l_show_hide_data(l_count).p_ws_defined := 'Y';
635 Else
636 l_show_hide_data(l_count).p_ws_defined := 'N';
637 End if;
638
639 If l_opt4_exists_rec.elig_sal_abr_id is not Null Then
640 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
641 Else
642 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
643 End if;
644
645 If l_opt4_exists_rec.ws_nnmntry_uom is not Null Then
646 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
647 Else
648 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
649 End if;
650 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_type,555);
651 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_ws_defined,560);
652 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,565);
653 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_nnmntry_uom,570);
654
655 Else
656 l_show_hide_data(l_count).p_type := 'OPT4';
657 l_show_hide_data(l_count).p_opt_defined := 'N';
658 hr_utility.set_location('Opt4 Not Exists',500);
659 End If;
660 End;
661
662 Else
663 ----------------------------- Plan Only------------------------------------------
664
665 Begin
666 -- If Group Options not exists
667 l_count := l_show_hide_data.count +1 ;
668 l_show_hide_data(l_count).p_type := 'PLOY';
669 l_show_hide_data(l_count).p_opt_defined := 'N';
670 hr_utility.set_location('Options Not Exists ...Count :'||l_count,560);
671 If (l_actual_plans_rec.ws_abr_id is not Null OR
672 l_group_plan_rec.ws_abr_id is not Null )Then
673 l_show_hide_data(l_count).p_ws_defined := 'Y';
674 Else
678 If ( l_actual_plans_rec.elig_sal_abr_id is not Null OR
675 l_show_hide_data(l_count).p_ws_defined := 'N';
676 End if;
677
679 l_group_plan_rec.elig_sal_abr_id is not Null) Then
680 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
681 Else
682 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
683 End if;
684
685 If (l_actual_plans_rec.ws_nnmntry_uom is not Null OR
686 l_group_plan_rec.ws_nnmntry_uom is not Null) Then
687 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
688 Else
689 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
690 End if;
691
692 If (l_actual_plans_rec.ws_sub_acty_typ_cd = 'ICM7' OR
693 l_group_plan_rec.ws_sub_acty_typ_cd = 'ICM7') Then
694 l_show_hide_data(l_count).p_ws_sub_acty_typ_cd := 'Y';
695 Else
696 l_show_hide_data(l_count).p_ws_sub_acty_typ_cd := 'N';
697 End if;
698
699
700 End;
701 End If;
702 hr_utility.set_location('Final count'||l_count, 500);
703
704 For i in l_show_hide_data.first..l_show_hide_data.last
705 Loop
706 hr_utility.set_location('p_type('||i||') :'||l_show_hide_data(i).p_type, 500);
707 hr_utility.set_location('p_opt_defined('||i||') :'||l_show_hide_data(i).p_opt_defined, 510);
708 hr_utility.set_location('p_ws_defined('||i||') :'||l_show_hide_data(i).p_ws_defined, 520);
709 hr_utility.set_location('p_eligy_sal_defined('||i||') :'||l_show_hide_data(i).p_eligy_sal_defined, 530);
710 hr_utility.set_location('p_nnmntry_uom('||i||') :'||l_show_hide_data(i).p_nnmntry_uom, 540);
711 hr_utility.set_location('ws_sub_acty_typ_cd('||i||') :'||l_show_hide_data(i).p_ws_sub_acty_typ_cd, 541);
712
713 End Loop;
714 p_show_hide_data := l_show_hide_data;
715 Exception
716 when others then
717 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
718 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
719 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
720
721 end check_hidden_worksheet_columns;
722 --
723 ------------------------ decide_insert_rec-----------------------
724 --
725 Function decide_insert_rec (p_inf_seq IN NUMBER
726 ,p_group_pl_id IN NUMBER
727 ,p_lf_evt_ocrd_dt IN DATE )
728 Return Varchar IS
729
730 Cursor csr_is_options IS
731 Select '1'
732 From bne_interface_cols_b
733 where (substr(interface_code,1,15)='BEN_CWB_WS_INTF'
734 OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
735 And INTERFACE_COL_NAME like '%OPT%'
736 AND interface_code = g_interface_code
737 And application_id = 800
738 And SEQUENCE_NUM = p_inf_seq;
739
740 Cursor csr_opt1 IS
741 Select '1'
742 From bne_interface_cols_b
743 where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
744 OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
745 And INTERFACE_COL_NAME like '%OPT1%'
746 AND interface_code = g_interface_code
747 And application_id = 800
748 And SEQUENCE_NUM = p_inf_seq;
749
750 Cursor csr_opt2 IS
751 Select '1'
752 From bne_interface_cols_b
753 where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
754 OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
755 AND interface_code = g_interface_code
756 And INTERFACE_COL_NAME like '%OPT2%'
757 And application_id = 800
758 And SEQUENCE_NUM = p_inf_seq;
759
760 Cursor csr_opt3 IS
761 Select '1'
762 From bne_interface_cols_b
763 where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
764 OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
765 AND interface_code = g_interface_code
766 And INTERFACE_COL_NAME like '%OPT3%'
767 And application_id = 800
768 And SEQUENCE_NUM = p_inf_seq;
769
770 Cursor csr_opt4 IS
771 Select '1'
772 From bne_interface_cols_b
773 where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
774 OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
775 AND interface_code = g_interface_code
776 And INTERFACE_COL_NAME like '%OPT4%'
777 And application_id = 800
778 And SEQUENCE_NUM = p_inf_seq;
779
780 cursor csr_asg_updt_date is
781 select '1'
782 from ben_cwb_pl_dsgn
783 where pl_id = p_group_pl_id
784 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
785 and asg_updt_eff_date is not null;
786
787 Cursor csr_promotions IS
788 Select '1'
789 From bne_interface_cols_b
790 where (substr(interface_code,1,15) ='BEN_CWB_WS_INTF'
791 OR interface_code = 'BEN_CWB_WRK_SHT_INTF')
792 AND interface_code = g_interface_code
793 And SEQUENCE_NUM in (126,152,191,192)
794 And application_id = 800
795 And SEQUENCE_NUM = p_inf_seq;
796
797
798 l_insert_rec Varchar(1):= 'Y';
799 l_option VARCHAR2(1);
800 l_show_hide_data ben_cwb_webadi_utils.p_show_hide_data;
801 l_PLOY_index Number := 0;
802 l_OPT1_index Number := 0;
803 l_OPT2_index Number := 0;
804 l_OPT3_index Number := 0;
805 l_OPT4_index Number := 0;
806 l_proc Varchar2(72) := g_package||'decide_insert_rec';
807
808
809 Begin
810
811 hr_utility.set_location('Entering :'||l_proc,10);
812 hr_utility.set_location('** p_inf_seq :'||p_inf_seq,20);
813
814 check_hidden_worksheet_columns( p_group_pl_id => p_group_pl_id
815 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
816 ,p_show_hide_data => l_show_hide_data);
817
818
819
823
820 hr_utility.set_location('l_show_hide_data.count :'||l_show_hide_data.count,785);
821 hr_utility.set_location('l_show_hide_data.first :'||l_show_hide_data.first,786);
822 hr_utility.set_location('l_show_hide_data.last :'||l_show_hide_data.last,787);
824
825 /*open csr_promotions;
826 fetch csr_promotions into l_option;
827 close csr_promotions;
828 If l_option is not null then
829 open csr_asg_updt_date;
830 fetch csr_asg_updt_date into l_option;
831 close csr_asg_updt_date;
832 if l_option is null then
833 l_insert_rec := 'N';
834 hr_utility.set_location('Assignment Update Effective Date Not defined for the plan :' || p_group_pl_id,785);
835 end if;
836 End if;*/
837
838
839 For i in l_show_hide_data.first..l_show_hide_data.last
840 Loop
841 hr_utility.set_location('p_type('||i||') :'||l_show_hide_data(i).p_type, 4900);
842 hr_utility.set_location('p_opt_defined('||i||') :'||l_show_hide_data(i).p_opt_defined, 500);
843 hr_utility.set_location('p_ws_defined('||i||') :'||l_show_hide_data(i).p_ws_defined, 510);
844 hr_utility.set_location('p_eligy_sal_defined('||i||') :'||l_show_hide_data(i).p_eligy_sal_defined, 520);
845 hr_utility.set_location('p_nnmntry_uom('||i||') :'||l_show_hide_data(i).p_nnmntry_uom, 530);
846
847 if (l_show_hide_data(i).p_type = 'PLOY') then
848 l_PLOY_index := i;
849 end if;
850
851 if (l_show_hide_data(i).p_type = 'OPT1') then
852 l_OPT1_index := i;
853 end if;
854
855 if (l_show_hide_data(i).p_type = 'OPT2') then
856 l_OPT2_index := i;
857 end if;
858
859 if (l_show_hide_data(i).p_type = 'OPT3') then
860 l_OPT3_index := i;
861 end if;
862
863 if (l_show_hide_data(i).p_type = 'OPT4') then
864 l_OPT4_index := i;
865 end if;
866 End Loop;
867 hr_utility.set_location('l_PLOY_index :'||l_PLOY_index,540);
868 hr_utility.set_location('l_OPT1_index :'||l_OPT1_index,541);
869 hr_utility.set_location('l_OPT2_index :'||l_OPT2_index,542);
870 hr_utility.set_location('l_OPT3_index :'||l_OPT3_index,543);
871 hr_utility.set_location('l_OPT4_index :'||l_OPT4_index,544);
872
873
874 --------------------------------Start of Plan Only--------------------------------
875 IF (l_PLOY_index <> 0 and l_show_hide_data(l_PLOY_index).p_type = 'PLOY' ) THEN
876 Begin
877 hr_utility.set_location('Start of Plan Only',545);
878 -- If No Options Exists then disable all Option Columns
879 Open csr_is_options;
880 Fetch csr_is_options into l_option;
881 Close csr_is_options;
882 If l_option is not null then
883 l_insert_rec := 'N';
884 End if;
885
886 -- Plan Level Checks
887 If l_show_hide_data(l_PLOY_index).p_ws_defined = 'N' And (p_inf_seq in (12,7,159,165,9,5,164) ) then
888 hr_utility.set_location('Plan WS Aount Not defined....',800);
889 hr_utility.set_location('p_inf_seq : (12,7,159,165,9,5,164)'||p_inf_seq,820);
890 l_insert_rec := 'N';
891 End if;
892
893 If l_show_hide_data(l_PLOY_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (165,9,5,164) ) then
894 hr_utility.set_location( 'Plan Eligible Sal Not Defined...',820);
895 hr_utility.set_location('p_inf_seq : (165,9,5,164)'||p_inf_seq,850);
896 l_insert_rec := 'N';
897 End if;
898
899 /* If l_show_hide_data(l_PLOY_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (7,159,165,9,5,164)) then
900 hr_utility.set_location( 'Plan is Non monetory Unit...',860);
901 hr_utility.set_location('p_inf_seq : (7,159,165,9,5,164)'||p_inf_seq,850);
902 l_insert_rec := 'N';
903 End if;
904 */
905 ----------------------------------
906 -- If Plan is Non-Monetory unit and Eligible Salary is defined then
907 -- enable Eligible Salary and
908 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Base Salary, New Salary
909
910 -- If Plan is Non-Monetory unit and Eligible Salary is not defined then
911 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary Base Salary, New Salary
912 If l_show_hide_data(l_PLOY_index).p_nnmntry_uom = 'Y' Then
913
914 If l_show_hide_data(l_PLOY_index).p_eligy_sal_defined = 'Y' Then
915 -- mvankada
916 -- Bug : 3751069
917 -- Modified the if condition to show the % of Eligible Salary
923 Else
918 If p_inf_seq In (9,165) Then
919 l_insert_rec := 'Y';
920 elsif p_inf_seq in (7,159,5,164) Then
921 l_insert_rec := 'N';
922 end if;
924 If p_inf_seq in (7,159,165,9,5,164) then
925 l_insert_rec := 'N';
926 End if;
927
928 end if;
929 End If;
930 ----------------------------------
931 -- Bug : 3576954
932 -- Author : mvankada
933 -- Display Base Salary
934 If p_inf_seq = 5 then
935 l_insert_rec := 'Y';
936 End if;
937
938 ----------------------------------
939
940 If (l_show_hide_data(l_PLOY_index).p_ws_sub_acty_typ_cd = 'N' And p_inf_seq = 164) then
941 hr_utility.set_location( 'Plan is Salary ...',870);
942 hr_utility.set_location('(p_inf_seq = 164) :'||p_inf_seq,890);
943 l_insert_rec := 'N';
944 End if;
945
946 End;
947 End If;
948 --------------------------------End of Plan Only--------------------------------
949
950
951 --------------------------------Start of Option1 Only--------------------------------
952
953 -- Option1
954 IF (l_OPT1_index <> 0 And l_show_hide_data(l_OPT1_index).p_type = 'OPT1' ) THEN
955 Begin
956 If l_show_hide_data(l_OPT1_index).p_opt_defined = 'N' then
957 hr_utility.set_location( 'Option 1 is not defined...',900);
958 -- Disable all Option1 Columns
959 Open csr_opt1;
960 Fetch csr_opt1 into l_option;
961 Close csr_opt1;
962 If l_option is not null then
963 l_insert_rec := 'N';
964 End if;
965 Else
966 hr_utility.set_location( 'Option 1 is defined...',910);
967 -- Option1 Level Checks
968 If l_show_hide_data(l_OPT1_index).p_ws_defined = 'N' And (p_inf_seq in (30,25,160,166,27) ) then
969 hr_utility.set_location( 'Option 1 worksheet Amount is not defined...',920);
970 hr_utility.set_location('p_inf_seq : (30,25,160,166,27)'||p_inf_seq,950);
971 l_insert_rec := 'N';
972 End if;
973
974 If l_show_hide_data(l_OPT1_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (166,27) ) then
975 hr_utility.set_location( 'Option 1 Eligibile Sal is not defined...',970);
976 hr_utility.set_location('p_inf_seq : (166,27)'||p_inf_seq,1000);
977 l_insert_rec := 'N';
978 End if;
979
980 /* If l_show_hide_data(l_OPT1_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (25,160,166,27)) then
981 hr_utility.set_location( 'Option 1 is Non Monetory Unit...',1010);
982 hr_utility.set_location('p_inf_seq : (25,160,166,27)'||p_inf_seq,1020);
983 l_insert_rec := 'N';
984 End if;
985 */
986 ----------------------------------
987 -- If Option1 is Non-Monetory unit and Eligible Salary is defined then
988 -- enable Eligible Salary and
989 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
990
991 -- If Option1 is Non-Monetory unit and Eligible Salary is not defined then
992 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
993
994 If l_show_hide_data(l_OPT1_index).p_nnmntry_uom = 'Y' Then
995
996 If l_show_hide_data(l_OPT1_index).p_eligy_sal_defined = 'Y' Then
997 If p_inf_seq = 27 Then
998 l_insert_rec := 'Y';
999 elsif p_inf_seq in (25,160,166) Then
1000 l_insert_rec := 'N';
1001 end if;
1002 Else
1003 If p_inf_seq in (25,160,166,27) then
1004 l_insert_rec := 'N';
1005 End if;
1006
1007 end if;
1008 End If;
1009 ----------------------------------
1010 End If;
1011 End;
1012 End If;
1013 -------------------------------End of Option1 Only--------------------------------
1014
1015 -- Option2
1016
1017 -------------------------------Start of Option2 Only--------------------------------
1018 IF (l_OPT2_index <> 0 And l_show_hide_data(l_OPT2_index).p_type = 'OPT2' ) THEN
1019 Begin
1020 If l_show_hide_data(l_OPT2_index).p_opt_defined = 'N' then
1021 -- Disable all Option1 Columns
1022 Open csr_opt2;
1023 Fetch csr_opt2 into l_option;
1024 Close csr_opt2;
1025 If l_option is not null then
1026 l_insert_rec := 'N';
1027 End if;
1028 Else
1029 -- Option1 Level Checks
1030 If l_show_hide_data(l_OPT2_index).p_ws_defined = 'N' And (p_inf_seq in (48,43,161,167,45) ) then
1031 l_insert_rec := 'N';
1032 End if;
1033
1034 If l_show_hide_data(l_OPT2_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (167,45) ) then
1035 l_insert_rec := 'N';
1036 End if;
1037
1038 /* If l_show_hide_data(l_OPT2_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (43,161,167,45)) then
1039 l_insert_rec := 'N';
1040 End if;
1041 */
1042 ----------------------------------
1043 -- If Option2 is Non-Monetory unit and Eligible Salary is defined then
1044 -- enable Eligible Salary and
1045 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
1046
1047 -- If Option2 is Non-Monetory unit and Eligible Salary is not defined then
1048 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
1049
1053 If p_inf_seq = 45 Then
1050 If l_show_hide_data(l_OPT2_index).p_nnmntry_uom = 'Y' Then
1051
1052 If l_show_hide_data(l_OPT2_index).p_eligy_sal_defined = 'Y' Then
1054 l_insert_rec := 'Y';
1055 elsif p_inf_seq in (43,161,167) Then
1056 l_insert_rec := 'N';
1057 end if;
1058 Else
1059 If p_inf_seq in (43,161,167,45) then
1060 l_insert_rec := 'N';
1061 End if;
1062
1063 end if;
1064 End If;
1065 ----------------------------------
1066 End If;
1067 end;
1068 End If;
1069 -------------------------------End of Option2 Only--------------------------------
1073 IF (l_OPT3_index <> 0 And l_show_hide_data(l_OPT3_index).p_type = 'OPT3' ) THEN
1070 -- Option3
1071
1072 -------------------------------Start of Option3 Only--------------------------------
1074 Begin
1075 If l_show_hide_data(l_OPT3_index).p_opt_defined = 'N' then
1076 -- Disable all Option1 Columns
1077 Open csr_opt3;
1078 Fetch csr_opt3 into l_option;
1079 Close csr_opt3;
1080 If l_option is not null then
1081 l_insert_rec := 'N';
1082 End if;
1083 Else
1084 -- Option1 Level Checks
1085 If l_show_hide_data(l_OPT3_index).p_ws_defined = 'N' And (p_inf_seq in (66,61,162,168,63) ) then
1086 l_insert_rec := 'N';
1087 End if;
1088
1089 If l_show_hide_data(l_OPT3_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (168,63) ) then
1090 l_insert_rec := 'N';
1091 End if;
1092
1093 /* If l_show_hide_data(l_OPT3_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (61,162,168,63)) then
1094 l_insert_rec := 'N';
1095 End if;
1096 */
1097 ----------------------------------
1098 -- If Option3 is Non-Monetory unit and Eligible Salary is defined then
1099 -- enable Eligible Salary and
1100 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
1101
1102 -- If Option3 is Non-Monetory unit and Eligible Salary is not defined then
1103 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
1104
1105 If l_show_hide_data(l_OPT3_index).p_nnmntry_uom = 'Y' Then
1106
1107 If l_show_hide_data(l_OPT3_index).p_eligy_sal_defined = 'Y' Then
1108 If p_inf_seq = 63 Then
1109 l_insert_rec := 'Y';
1110 elsif p_inf_seq in (61,162,168) Then
1111 l_insert_rec := 'N';
1112 end if;
1113 Else
1114 If p_inf_seq in (61,162,168,63) then
1115 l_insert_rec := 'N';
1116 End if;
1117
1118 end if;
1119 End If;
1120 ----------------------------------
1121 End If;
1122 end;
1123 End if;
1124
1125 -------------------------------End of Option3 Only--------------------------------
1126
1127
1128 -------------------------------Start of Option4 Only--------------------------------
1129
1130 IF (l_OPT4_index <> 0 And l_show_hide_data(l_OPT4_index).p_type = 'OPT4' ) THEN
1131 Begin
1132 -- Option4
1133 If l_show_hide_data(l_OPT4_index).p_opt_defined = 'N' then
1134 -- Disable all Option1 Columns
1135 Open csr_opt4;
1136 Fetch csr_opt4 into l_option;
1137 Close csr_opt4;
1138 If l_option is not null then
1139 l_insert_rec := 'N';
1140 End if;
1141 Else
1142 -- Option1 Level Checks
1143 If l_show_hide_data(l_OPT4_index).p_ws_defined = 'N' And (p_inf_seq in (84,79,163,169,81) ) then
1144 l_insert_rec := 'N';
1145 End if;
1146
1147 If l_show_hide_data(l_OPT4_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (169,81) ) then
1148 l_insert_rec := 'N';
1149 End if;
1150
1151 /* If l_show_hide_data(l_OPT4_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (79,163,169,81)) then
1152 l_insert_rec := 'N';
1153 End if;
1154 */
1155 ----------------------------------
1156 -- If Option4 is Non-Monetory unit and Eligible Salary is defined then
1157 -- enable Eligible Salary and
1158 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
1159
1163 If l_show_hide_data(l_OPT4_index).p_nnmntry_uom = 'Y' Then
1160 -- If Option4 is Non-Monetory unit and Eligible Salary is not defined then
1161 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
1162
1164
1165 If l_show_hide_data(l_OPT4_index).p_eligy_sal_defined = 'Y' Then
1166 If p_inf_seq = 81 Then
1167 l_insert_rec := 'Y';
1168 elsif p_inf_seq in (79,163,169,81) Then
1169 l_insert_rec := 'N';
1170 end if;
1171 Else
1172 If p_inf_seq in (79,163,169,81) then
1173 l_insert_rec := 'N';
1174 End if;
1175
1176 end if;
1177 End If;
1178 ----------------------------------
1179 End If;
1180
1181 End;
1182 End if;
1183 -------------------------------End of Option4 Only--------------------------------
1184
1185 -- bug 14022949: commented the following, to insert PL_PERSON_RATE_ID(130) for plan with options, so that other rates like MiscRate1 etc can be uploaded at plan level.
1186 -- If Options Exists then don't display Plan Group Columns
1187 /*Begin
1188 IF ( (l_OPT1_index <> 0 And l_show_hide_data(l_OPT1_index).p_type = 'OPT1') OR
1189 (l_OPT2_index <> 0 And l_show_hide_data(l_OPT2_index).p_type = 'OPT2') OR
1190 (l_OPT3_index <> 0 And l_show_hide_data(l_OPT3_index).p_type = 'OPT3') OR
1191 (l_OPT4_index <> 0 And l_show_hide_data(l_OPT4_index).p_type = 'OPT4')
1192 ) THEN
1193 If p_inf_seq = 130 then
1194 l_insert_rec := 'N';
1195 end if;
1196
1197 End if;
1198 End;*/
1199
1200 return l_insert_rec;
1201 End decide_insert_rec ;
1202
1203 --
1204 --------------------- insert_cwb_layout_cols_row---------------------
1205 --
1206 Procedure insert_cwb_layout_cols_row(p_application_id In Number,
1207 p_base_layout_code In Varchar2,
1208 p_act_layout_code In Varchar2,
1209 p_inf_seq In Number,
1210 p_dis_seq In Number,
1211 p_group_pl_id In Number Default Null,
1212 p_lf_evt_ocrd_dt In Date Default Null
1213 )
1214 IS
1215 CURSOR csr_layout_cols_row
1216 IS
1217 SELECT blc.application_id
1218 ,blc.layout_code
1219 ,blc.block_id
1220 ,blc.interface_app_id
1221 ,blc.interface_code
1222 ,blc.interface_seq_num
1223 ,blc.sequence_num
1224 ,blc.style
1225 ,blc.style_class
1226 ,blc.hint_style
1227 ,blc.hint_style_class
1228 ,blc.prompt_style
1232 ,blc.created_by
1229 ,blc.prompt_style_class
1230 ,blc.default_type
1231 ,blc.DEFAULT_VALUE
1233 ,blc.last_updated_by
1234 ,blc.last_update_login
1235 ,blc.read_only_flag
1236 FROM bne_layout_cols blc
1237 WHERE blc.application_id = p_application_id
1238 AND blc.layout_code = p_base_layout_code
1239 AND blc.interface_seq_num = p_inf_seq;
1240
1241 /*cursor csr_interface_cols_row is
1242 select bic.read_only_flag
1243 from bne_interface_cols_b bic,
1244 bne_layout_cols blc
1245 where blc.application_id = p_application_id
1246 and blc.layout_code = p_base_layout_code
1247 and blc.interface_seq_num = p_inf_seq
1248 and bic.sequence_num = p_inf_seq
1249 and blc.interface_code = bic.interface_code;*/
1250
1251 cursor csr_layout_cols_promotion is
1252 select '1'
1253 FROM bne_layout_cols blc
1254 WHERE blc.application_id = p_application_id
1255 AND blc.layout_code = p_act_layout_code
1256 AND blc.interface_seq_num = p_inf_seq
1257 AND blc.interface_seq_num in (152,191,192,105,126);
1258
1259
1260
1261 l_layout_cols_row csr_layout_cols_row%RowType;
1262 l_layout_cols_promotion varchar2(1) := null;
1263
1264 l_proc Varchar2(72) := 'create_cwb_layout_cols_row';
1265 l_rowid VARCHAR2(200);
1266 l_option VARCHAR2(1);
1267
1268 l_insert_rec VARCHAR2(1) := 'Y';
1269 l_show_hide_data p_show_hide_data;
1270 l_read_only_flag VARCHAR2(1) := NULL;
1271 l_display_width NUMBER := NULL;
1272
1273 Begin
1274
1275 hr_utility.set_location('Entering '||l_proc,10);
1276 hr_utility.set_location('p_group_pl_id :'||p_group_pl_id,20);
1277 hr_utility.set_location('p_lf_evt_ocrd_dt :'||p_lf_evt_ocrd_dt,30);
1278
1279 If substr(p_base_layout_code,1,16) <> 'BEN_CWB_WS_LYT1_'
1280 AND p_base_layout_code <> 'BEN_CWB_WRK_SHT_BASE_LYT' then
1281 l_insert_rec := 'Y';
1282 Else
1286 End If;
1283 l_insert_rec := decide_insert_rec (p_inf_seq => p_inf_seq
1284 ,p_group_pl_id => p_group_pl_id
1285 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1287 hr_utility.set_location('p_inf_seq #: '||p_inf_seq||' l_insert_rec #:'||l_insert_rec,60);
1288
1289 OPEN csr_layout_cols_row;
1290 FETCH csr_layout_cols_row INTO l_layout_cols_row;
1291 if csr_layout_cols_row%NOTFOUND then
1292 l_insert_rec := 'N';
1293 hr_utility.set_location('Base Layout Does Not Have Item Checked:'||l_proc, 70);
1294 end if;
1295 CLOSE csr_layout_cols_row;
1296
1297 open csr_layout_cols_promotion;
1298 fetch csr_layout_cols_promotion into l_layout_cols_promotion;
1299 close csr_layout_cols_promotion;
1300 if (l_layout_cols_promotion is not null) then
1301 l_insert_rec := 'N'; -- layout col already inserted
1302 end if;
1303
1304 If l_insert_rec = 'Y' then
1305
1306 IF (substr(p_base_layout_code,1,16) = 'BEN_CWB_WS_LYT1_'
1307 OR p_base_layout_code = 'BEN_CWB_WRK_SHT_BASE_LYT') THEN
1308 l_read_only_flag := 'Y';
1309 --Hide the contextual security keys
1310 IF (l_layout_cols_row.interface_seq_num IN (158, 189, 194, 195, 196, 198)) then
1311 l_display_width := 0;
1312 --Hide the security keys in lines
1313 ELSIF (l_layout_cols_row.interface_seq_num IN (130, 131, 132, 133, 134)) then
1314 l_display_width := 0;
1315 l_read_only_flag := 'N';
1316 --Make non-updateable columns as read-only
1317 ELSIF (l_layout_cols_row.interface_seq_num IN (3,12,30,48,66,84,151,152,192,191,126) OR
1318 l_layout_cols_row.interface_seq_num BETWEEN 200 AND 234 OR
1319 -- ER : ability to update other rates and custom segments
1320 l_layout_cols_row.interface_seq_num BETWEEN 136 AND 150 OR
1321 l_layout_cols_row.interface_seq_num in (165,8,26,44,62,80,10,28,46,64,82,11,29,47,65,83,19,20,21,37,38,39,55,56,57,73,74,75,91,92,93)
1322 OR l_layout_cols_row.interface_seq_num in ('246','247','248')
1323 --changed by KMG included the below condition
1324 OR l_layout_cols_row.interface_seq_num BETWEEN 240 and 244) THEN
1325 l_read_only_flag := 'N';
1326 END IF;
1327 IF(l_read_only_flag = 'N') THEN
1328 l_read_only_flag := l_layout_cols_row.read_only_flag;
1329 /* IF(l_read_only_flag = 'N') THEN
1330 for l_interface_cols_row in csr_interface_cols_row loop
1331 l_read_only_flag := l_interface_cols_row.read_only_flag;
1332 hr_utility.set_location('p_inf_seq '||p_inf_seq,10);
1333 hr_utility.set_location('l_read_only_flag '||l_read_only_flag,10);
1334 end loop;
1335 end if;*/
1336 END IF;
1337 END IF;
1338 hr_utility.set_location('p_inf_seq '||p_inf_seq||' l_read_only_flag '||l_read_only_flag,10);
1339 bne_layout_cols_pkg.insert_row
1340 (x_rowid => l_rowid
1341 ,x_application_id => l_layout_cols_row.application_id
1342 ,x_layout_code => p_act_layout_code
1343 ,x_block_id => l_layout_cols_row.block_id
1344 ,x_sequence_num => p_dis_seq
1345 ,x_object_version_number => 1
1346 ,x_interface_app_id => l_layout_cols_row.interface_app_id
1347 ,x_interface_code => l_layout_cols_row.interface_code
1348 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
1349 ,x_style_class => l_layout_cols_row.style_class
1350 ,x_hint_style => l_layout_cols_row.hint_style
1351 ,x_hint_style_class => l_layout_cols_row.hint_style_class
1352 ,x_prompt_style => l_layout_cols_row.prompt_style
1353 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
1354 ,x_default_type => l_layout_cols_row.default_type
1355 ,x_default_value => l_layout_cols_row.DEFAULT_VALUE
1356 ,x_style => l_layout_cols_row.style
1357 ,x_creation_date => SYSDATE
1358 ,x_created_by => l_layout_cols_row.created_by
1359 ,x_last_update_date => SYSDATE
1360 ,x_last_updated_by => l_layout_cols_row.last_updated_by
1361 ,x_last_update_login => l_layout_cols_row.last_update_login
1362 ,x_read_only_flag => l_read_only_flag
1363 ,x_display_width => l_display_width);
1364 end if;
1365
1366 hr_utility.set_location('Leaving '||l_proc,100);
1367 End insert_cwb_layout_cols_row;
1368
1369 --
1370 --------------------------update_cwb_layout----------------------
1371 --
1372
1373 PROCEDURE update_cwb_layout(
1374 p_layout_code IN VARCHAR2
1375 ,p_base_layout IN VARCHAR2
1376 ,p_interface_seq IN VARCHAR2
1377 ,p_rendered_seq IN VARCHAR2
1378 ,p_group_pl_id IN NUMBER Default Null
1379 ,p_lf_evt_ocrd_dt IN DATE Default Null
1380 ,p_download_switch OUT NOCOPY VARCHAR2
1381 ) IS
1382
1383 LIST column_list := column_list();
1384 list_rec column_rec;
1385 l_application_id NUMBER(3) := 800;
1386 l_temp_layout_code VARCHAR2(100) := p_base_layout;
1387 l_params VARCHAR2(1000) := p_rendered_seq;
1388 l_interface_seq VARCHAR2(1000) := p_interface_seq;
1389 l_rowid VARCHAR2(200);
1390 param_loc NUMBER(3);
1391 interface_seq_loc NUMBER(3);
1392 param_len NUMBER(3) := LENGTH(l_params);
1393 interface_seq_len NUMBER(3) := LENGTH(l_interface_seq);
1394 param_val VARCHAR2(1000);
1398 l_opt1_ws_amt_switch VARCHAR(1) := '1';
1395 interface_seq_val VARCHAR2(100);
1396 idx NUMBER := 1;
1397 l_pl_ws_amt_switch VARCHAR(1) := '1';
1399 l_opt2_ws_amt_switch VARCHAR(1) := '1';
1400 l_opt3_ws_amt_switch VARCHAR(1) := '1';
1401 l_opt4_ws_amt_switch VARCHAR(1) := '1';
1402 l_perf_switch VARCHAR(1) := '1';
1403 l_rank_switch VARCHAR(1) := '1';
1404 l_cpi_attribute1_switch VARCHAR(1) := '1';
1405 l_cpi_attribute2_switch VARCHAR(1) := '1';
1406 l_cpi_attribute3_switch VARCHAR(1) := '1';
1407 l_cpi_attribute4_switch VARCHAR(1) := '1';
1408 l_cpi_attribute5_switch VARCHAR(1) := '1';
1409 l_cpi_attribute6_switch VARCHAR(1) := '1';
1410 l_cpi_attribute7_switch VARCHAR(1) := '1';
1411 l_cpi_attribute8_switch VARCHAR(1) := '1';
1412 l_cpi_attribute9_switch VARCHAR(1) := '1';
1413 l_cpi_attribute10_switch VARCHAR(1) := '1';
1414 l_cpi_attribute11_switch VARCHAR(1) := '1';
1415 l_cpi_attribute12_switch VARCHAR(1) := '1';
1416 l_cpi_attribute13_switch VARCHAR(1) := '1';
1417 l_cpi_attribute14_switch VARCHAR(1) := '1';
1418 l_cpi_attribute15_switch VARCHAR(1) := '1';
1419 l_cpi_attribute16_switch VARCHAR(1) := '1';
1420 l_cpi_attribute17_switch VARCHAR(1) := '1';
1421 l_cpi_attribute18_switch VARCHAR(1) := '1';
1422 l_cpi_attribute19_switch VARCHAR(1) := '1';
1423 l_cpi_attribute20_switch VARCHAR(1) := '1';
1424 l_cpi_attribute21_switch VARCHAR(1) := '1';
1425 l_cpi_attribute22_switch VARCHAR(1) := '1';
1426 l_cpi_attribute23_switch VARCHAR(1) := '1';
1427 l_cpi_attribute24_switch VARCHAR(1) := '1';
1428 l_cpi_attribute25_switch VARCHAR(1) := '1';
1429 l_cpi_attribute26_switch VARCHAR(1) := '1';
1430 l_cpi_attribute27_switch VARCHAR(1) := '1';
1431 l_cpi_attribute28_switch VARCHAR(1) := '1';
1432 l_cpi_attribute29_switch VARCHAR(1) := '1';
1433 l_cpi_attribute30_switch VARCHAR(1) := '1';
1434 l_promotion_switch VARCHAR(1) := '1';
1435 l_pl_other_rates_switch VARCHAR(1) := '1';
1436 l_opt1_other_rates_switch VARCHAR(1) := '1';
1437 l_opt2_other_rates_switch VARCHAR(1) := '1';
1438 l_opt3_other_rates_switch VARCHAR(1) := '1';
1439 l_opt4_other_rates_switch VARCHAR(1) := '1';
1440
1441
1442 CURSOR c_layout_cols( v_application_id IN NUMBER
1443 ,v_layout_code IN VARCHAR2)
1444 IS
1445 SELECT blc.application_id
1446 ,blc.layout_code
1447 ,blc.block_id
1448 ,blc.sequence_num
1449 FROM bne_layout_cols blc
1450 WHERE blc.application_id = v_application_id
1451 AND blc.layout_code = v_layout_code;
1452
1453 CURSOR c_layout_cols_row(
1454 v_interface_seq IN NUMBER
1455 ,v_application_id IN NUMBER
1456 ,v_layout_code IN VARCHAR2) IS
1457 SELECT blc.application_id
1458 ,blc.layout_code
1459 ,blc.block_id
1460 ,blc.interface_app_id
1461 ,blc.interface_code
1462 ,blc.interface_seq_num
1463 ,blc.sequence_num
1464 ,blc.style
1465 ,blc.style_class
1466 ,blc.hint_style
1467 ,blc.hint_style_class
1468 ,blc.prompt_style
1469 ,blc.prompt_style_class
1470 ,blc.default_type
1471 ,blc.DEFAULT_VALUE
1472 ,blc.created_by
1473 ,blc.last_updated_by
1474 ,blc.last_update_login
1475 FROM bne_layout_cols blc
1476 WHERE blc.application_id = v_application_id
1477 AND blc.layout_code = v_layout_code
1478 AND blc.interface_seq_num = v_interface_seq;
1479
1480 l_layout_cols_row c_layout_cols_row%ROWTYPE;
1481 l_layout_col_rec c_layout_cols%ROWTYPE;
1482 BEGIN
1483
1484 LOOP
1485 param_loc := INSTR(l_params, '+');
1486 interface_seq_loc := INSTR(l_interface_seq, '+');
1487 param_val := SUBSTR(l_params, 1, param_loc - 1);
1488 interface_seq_val := SUBSTR(l_interface_seq, 1, interface_seq_loc - 1);
1489
1490 IF param_loc = 0 THEN
1491 param_val := l_params;
1492 END IF;
1493
1494 IF interface_seq_loc = 0 THEN
1495 interface_seq_val := l_interface_seq;
1496 END IF;
1497
1498 l_params := SUBSTR(l_params, param_loc + 1, param_len);
1499 l_interface_seq := SUBSTR(l_interface_seq, interface_seq_loc + 1, interface_seq_len);
1500 If g_debug then
1501 hr_utility.set_location('parameter : '||param_val||' '||interface_seq_val,20);
1502 END IF;
1503 LIST.EXTEND;
1504 list_rec.p_sequence := TO_NUMBER(param_val);
1505 list_rec.p_interface_seq := TO_NUMBER(interface_seq_val);
1506 LIST(idx) := list_rec;
1507
1508 EXIT WHEN param_loc = 0;
1509 idx := idx + 1;
1510 END LOOP;
1511
1512 delete_cwb_layout_cols(p_layout_code => p_layout_code
1513 ,p_application_id => l_application_id);
1514
1515 /* BEGIN
1516 OPEN c_layout_cols(l_application_id, p_layout_code);
1517
1518 LOOP
1519 FETCH c_layout_cols INTO l_layout_col_rec;
1520 EXIT WHEN c_layout_cols%NOTFOUND;
1521
1522 -- DBMS_OUTPUT.put_line('## Seq Num :'||l_layout_col_rec.sequence_num);
1523 hr_utility.set_location('Seq Num :'||l_layout_col_rec.sequence_num,25);
1524 bne_layout_cols_pkg.delete_row
1525 (x_application_id => l_layout_col_rec.application_id
1526 ,x_layout_code => l_layout_col_rec.layout_code
1527 ,x_block_id => l_layout_col_rec.block_id
1531 CLOSE c_layout_cols;
1528 ,x_sequence_num => l_layout_col_rec.sequence_num);
1529 END LOOP;
1530
1532 EXCEPTION
1533 WHEN OTHERS
1534 THEN
1535 Null;
1536 hr_utility.set_location('ERROR occured',30);
1537 -- DBMS_OUTPUT.put_line('ERROR WHILE DELETING');
1538 END;
1539 */
1540
1541
1542 FOR k IN LIST.FIRST .. LIST.LAST
1543 LOOP
1544 IF (LIST(k).p_sequence <> 0) THEN
1545
1546 IF(LIST(k).p_interface_seq = 12) THEN
1547 l_pl_ws_amt_switch := '2';
1548 ELSIF(LIST(k).p_interface_seq = 30) THEN
1549 l_opt1_ws_amt_switch := '2';
1550 ELSIF(LIST(k).p_interface_seq = 48) THEN
1551 l_opt2_ws_amt_switch := '2';
1552 ELSIF(LIST(k).p_interface_seq = 66) THEN
1553 l_opt3_ws_amt_switch := '2';
1554 ELSIF(LIST(k).p_interface_seq = 84) THEN
1555 l_opt4_ws_amt_switch := '2';
1556 ELSIF(LIST(k).p_interface_seq = 151) THEN
1557 l_perf_switch := '2';
1558 ELSIF(LIST(k).p_interface_seq = 3) THEN
1559 l_rank_switch := '2';
1560 ELSIF(LIST(k).p_interface_seq = 200) THEN
1561 l_cpi_attribute1_switch := '2';
1562 ELSIF(LIST(k).p_interface_seq = 201) THEN
1563 l_cpi_attribute2_switch := '2';
1564 ELSIF(LIST(k).p_interface_seq = 202) THEN
1565 l_cpi_attribute3_switch := '2';
1566 ELSIF(LIST(k).p_interface_seq = 203) THEN
1567 l_cpi_attribute4_switch := '2';
1568 ELSIF(LIST(k).p_interface_seq = 204) THEN
1569 l_cpi_attribute5_switch := '2';
1570 ELSIF(LIST(k).p_interface_seq = 205) THEN
1571 l_cpi_attribute6_switch := '2';
1572 ELSIF(LIST(k).p_interface_seq = 206) THEN
1573 l_cpi_attribute7_switch := '2';
1574 ELSIF(LIST(k).p_interface_seq = 207) THEN
1575 l_cpi_attribute8_switch := '2';
1576 ELSIF(LIST(k).p_interface_seq = 208) THEN
1577 l_cpi_attribute9_switch := '2';
1578 ELSIF(LIST(k).p_interface_seq = 209) THEN
1579 l_cpi_attribute10_switch := '2';
1580 ELSIF(LIST(k).p_interface_seq = 210) THEN
1581 l_cpi_attribute11_switch := '2';
1582 ELSIF(LIST(k).p_interface_seq = 211) THEN
1583 l_cpi_attribute12_switch := '2';
1584 ELSIF(LIST(k).p_interface_seq = 212) THEN
1585 l_cpi_attribute13_switch := '2';
1586 ELSIF(LIST(k).p_interface_seq = 213) THEN
1587 l_cpi_attribute14_switch := '2';
1588 ELSIF(LIST(k).p_interface_seq = 214) THEN
1589 l_cpi_attribute15_switch := '2';
1590 ELSIF(LIST(k).p_interface_seq = 215) THEN
1591 l_cpi_attribute16_switch := '2';
1592 ELSIF(LIST(k).p_interface_seq = 216) THEN
1593 l_cpi_attribute17_switch := '2';
1594 ELSIF(LIST(k).p_interface_seq = 217) THEN
1595 l_cpi_attribute18_switch := '2';
1596 ELSIF(LIST(k).p_interface_seq = 218) THEN
1597 l_cpi_attribute19_switch := '2';
1598 ELSIF(LIST(k).p_interface_seq = 219) THEN
1599 l_cpi_attribute20_switch := '2';
1600 ELSIF(LIST(k).p_interface_seq = 220) THEN
1601 l_cpi_attribute21_switch := '2';
1602 ELSIF(LIST(k).p_interface_seq = 221) THEN
1603 l_cpi_attribute22_switch := '2';
1604 ELSIF(LIST(k).p_interface_seq = 222) THEN
1605 l_cpi_attribute23_switch := '2';
1606 ELSIF(LIST(k).p_interface_seq = 223) THEN
1607 l_cpi_attribute24_switch := '2';
1608 ELSIF(LIST(k).p_interface_seq = 224) THEN
1609 l_cpi_attribute25_switch := '2';
1610 ELSIF(LIST(k).p_interface_seq = 225) THEN
1611 l_cpi_attribute26_switch := '2';
1612 ELSIF(LIST(k).p_interface_seq = 226) THEN
1613 l_cpi_attribute27_switch := '2';
1614 ELSIF(LIST(k).p_interface_seq = 227) THEN
1615 l_cpi_attribute28_switch := '2';
1616 ELSIF(LIST(k).p_interface_seq = 228) THEN
1617 l_cpi_attribute29_switch := '2';
1618 ELSIF(LIST(k).p_interface_seq = 229) THEN
1619 l_cpi_attribute30_switch := '2';
1620 ELSIF(LIST(k).p_interface_seq in (126,152,191,192) ) THEN
1621 l_promotion_switch := '2';
1622 ELSIF(LIST(k).p_interface_seq in (8,10,11,19,20,21) ) THEN
1623 l_pl_other_rates_switch := '2';
1624 ELSIF(LIST(k).p_interface_seq in (26,28,29,37,38,39) ) THEN
1625 l_opt1_other_rates_switch := '2';
1626 ELSIF(LIST(k).p_interface_seq in (44,46,47,55,56,57) ) THEN
1627 l_opt2_other_rates_switch := '2';
1628 ELSIF(LIST(k).p_interface_seq in (62,64,65,73,74,75) ) THEN
1629 l_opt3_other_rates_switch := '2';
1630 ELSIF(LIST(k).p_interface_seq in (80,82,83,91,92,931) ) THEN
1631 l_opt4_other_rates_switch := '2';
1635 ,p_base_layout_code => p_base_layout
1632 END IF;
1633
1634 insert_cwb_layout_cols_row( p_application_id => l_application_id
1636 ,p_act_layout_code => p_layout_code
1637 ,p_inf_seq => LIST(k).p_interface_seq
1638 ,p_dis_seq => LIST(k).p_sequence
1639 ,p_group_pl_id => p_group_pl_id
1640 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
1641 /* OPEN c_layout_cols_row(LIST(k).p_interface_seq
1642 ,l_application_id
1643 ,l_temp_layout_code);
1644
1645 FETCH c_layout_cols_row INTO l_layout_cols_row;
1646 CLOSE c_layout_cols_row;
1647
1648 -- DBMS_OUTPUT.put_line('Sequence :'||LIST(k).p_sequence ||'Inf Seq :'|| l_layout_cols_row.interface_seq_num);
1649
1650 hr_utility.set_location('Sequence :'||LIST(k).p_sequence||'Inf Seq : '||l_layout_cols_row.interface_seq_num ,50);
1651 bne_layout_cols_pkg.insert_row
1652 (x_rowid => l_rowid
1653 ,x_application_id => l_layout_cols_row.application_id
1654 ,x_layout_code => p_layout_code
1655 ,x_block_id => l_layout_cols_row.block_id
1656 ,x_sequence_num => LIST(k).p_sequence
1657 ,x_object_version_number => 1
1658 ,x_interface_app_id => l_layout_cols_row.interface_app_id
1659 ,x_interface_code => l_layout_cols_row.interface_code
1660 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
1661 ,x_style_class => l_layout_cols_row.style_class
1662 ,x_hint_style => l_layout_cols_row.hint_style
1663 ,x_hint_style_class => l_layout_cols_row.hint_style_class
1664 ,x_prompt_style => l_layout_cols_row.prompt_style
1665 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
1666 ,x_default_type => l_layout_cols_row.default_type
1667 ,x_default_value => l_layout_cols_row.DEFAULT_VALUE
1668 ,x_style => l_layout_cols_row.style
1669 ,x_creation_date => SYSDATE
1670 ,x_created_by => l_layout_cols_row.created_by
1671 ,x_last_update_date => SYSDATE
1672 ,x_last_updated_by => l_layout_cols_row.last_updated_by
1673 ,x_last_update_login => l_layout_cols_row.last_update_login);
1674 */
1675 END IF;
1676 END LOOP;
1677 p_download_switch := '2' ||
1678 l_pl_ws_amt_switch||
1679 l_opt1_ws_amt_switch||
1680 l_opt2_ws_amt_switch||
1681 l_opt3_ws_amt_switch||
1682 l_opt4_ws_amt_switch||
1683 l_perf_switch||
1684 l_rank_switch||
1685 l_cpi_attribute1_switch||
1686 l_cpi_attribute2_switch||
1687 l_cpi_attribute3_switch||
1688 l_cpi_attribute4_switch||
1689 l_cpi_attribute5_switch||
1690 l_cpi_attribute6_switch||
1691 l_cpi_attribute7_switch||
1692 l_cpi_attribute8_switch||
1693 l_cpi_attribute9_switch||
1694 l_cpi_attribute10_switch||
1695 l_cpi_attribute11_switch||
1696 l_cpi_attribute12_switch||
1697 l_cpi_attribute13_switch||
1698 l_cpi_attribute14_switch||
1699 l_cpi_attribute15_switch||
1700 l_cpi_attribute16_switch||
1701 l_cpi_attribute17_switch||
1702 l_cpi_attribute18_switch||
1703 l_cpi_attribute19_switch||
1704 l_cpi_attribute20_switch||
1705 l_cpi_attribute21_switch||
1706 l_cpi_attribute22_switch||
1707 l_cpi_attribute23_switch||
1708 l_cpi_attribute24_switch||
1709 l_cpi_attribute25_switch||
1710 l_cpi_attribute26_switch||
1711 l_cpi_attribute27_switch||
1712 l_cpi_attribute28_switch||
1713 l_cpi_attribute29_switch||
1714 l_cpi_attribute30_switch||
1715 l_promotion_switch||
1716 l_pl_other_rates_switch||
1717 l_opt1_other_rates_switch||
1718 l_opt2_other_rates_switch||
1719 l_opt3_other_rates_switch||
1720 l_opt4_other_rates_switch;
1721
1722 p_download_switch := REPLACE(REPLACE(p_download_switch,'1','0'),'2','1');
1723 p_download_switch := nvl(lpad(int2hex(bin2int(substr(p_download_switch,1,28))),7,0)||
1724 int2hex(bin2int(rpad(substr(p_download_switch,29),28,0))),'0000000');
1725 END update_cwb_layout;
1726
1727 --
1728 --------------------------encrypt----------------------
1729 --
1730
1731 FUNCTION encrypt(
1732 input_string IN VARCHAR2)
1733 RETURN VARCHAR2
1734 IS
1735 l_encrypted_string VARCHAR2(2048);
1736 BEGIN
1737 l_encrypted_string := NULL;
1741
1738 DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => input_string
1739 ,key_string => key_string
1740 ,encrypted_string => l_encrypted_string);
1742 RETURN rawtohex(UTL_RAW.CAST_TO_RAW(l_encrypted_string));
1743 END;
1744
1745 --
1746 --------------------------decrypt----------------------
1747 --
1748 FUNCTION decrypt( input_string IN VARCHAR2) RETURN VARCHAR2
1749 IS
1750 l_decrypted_string VARCHAR2(2048);
1751 l_convert_string VARCHAR2(2048);
1752 BEGIN
1753 l_convert_string := hextoraw(input_string);
1754 l_convert_string := UTL_RAW.CAST_TO_VARCHAR2(input_string);
1755
1756 l_decrypted_string := NULL;
1757 DBMS_OBFUSCATION_TOOLKIT.des3decrypt
1758 (input_string => l_convert_string
1759 ,key_string => key_string
1760 ,decrypted_string => l_decrypted_string);
1761 RETURN l_decrypted_string;
1762 END;
1763 --
1764 --------------------------lock_cwb_layout----------------------
1765 --
1766 FUNCTION lock_cwb_layout(p_integrator_code IN Varchar2
1767 ,p_base_layout_code IN VARCHAR2)
1768 RETURN VARCHAR2
1769 IS
1770 CURSOR c_layout IS
1771 SELECT layout_code
1772 FROM bne_layouts_b
1773 WHERE integrator_code = p_integrator_code
1774 AND integrator_app_id = 800
1775 AND application_id = 800
1776 AND layout_code <> p_base_layout_code
1777 AND layout_code NOT IN (SELECT attribute1
1778 FROM ben_transaction
1779 WHERE transaction_type = 'CWBWEBADI'
1780 AND attribute2 IS NOT NULL
1781 AND attribute1 IS NOT NULL
1782 AND DECODE(transaction_type, 'CWBWEBADI', SYSDATE - (to_number(l_layout_lock_time) /(24 * 60))
1783 - TO_DATE(attribute2, 'yyyy/mm/dd:hh:mi'),0) < 0);
1784
1785 l_layout VARCHAR2(200);
1786 l_new_layout_user_name Varchar2(4000);
1787 l_date Varchar2(100);
1788 BEGIN
1789 OPEN c_layout;
1790 FETCH c_layout INTO l_layout;
1791 CLOSE c_layout;
1792
1793 IF l_layout IS NULL THEN
1794 BEGIN
1795 l_date := to_char(sysdate,'yyyymmddhhmi');
1796 l_layout := '__'||to_char(sysdate,'yyyymmddhhmi')||'__';
1797 l_new_layout_user_name := '__'||to_char(sysdate,'yyyymmddhhmi')||'__';
1798 create_cwb_layout( p_layout_code => l_layout
1799 ,p_user_name => l_new_layout_user_name
1803 ELSE
1800 ,p_base_layout_code => p_base_layout_code);
1801
1802 END;
1804 BEGIN
1805 DELETE ben_transaction
1806 WHERE transaction_type = 'CWBWEBADI'
1807 AND attribute1 = l_layout;
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 NULL;
1811 END;
1812
1813 INSERT INTO ben_transaction
1814 (transaction_id
1815 ,transaction_type
1816 ,attribute1
1817 ,attribute2)
1818 VALUES (ben_transaction_s.NEXTVAL
1819 ,'CWBWEBADI'
1820 ,l_layout
1821 ,TO_CHAR(SYSDATE, 'yyyy/mm/dd:hh:mi'));
1822 END IF;
1823
1824
1825
1826 RETURN l_layout;
1827
1828 END;
1829 --
1830 --------------------------unlock_cwb_layout----------------------
1831 --
1832
1833 PROCEDURE unlock_cwb_layout( p_layout_code IN VARCHAR2)
1834 IS
1835 BEGIN
1836 DELETE ben_transaction
1837 WHERE transaction_type = 'CWBWEBADI'
1838 AND attribute1 = p_layout_code;
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 NULL;
1842 END;
1843
1844 --
1845 -------------------------- create_custom_row ----------------------
1846 --
1847 Procedure create_custom_row ( p_key IN VARCHAR2
1848 ,p_region_key IN VARCHAR2
1849 ,p_integrator_code IN VARCHAR2
1850 ,p_interface_code IN VARCHAR2
1851 ,p_interface_col_code IN VARCHAR2
1852 ,p_display_seq IN Number)
1853 IS
1854
1855 Cursor csr_col_prompt
1856 IS
1857 Select InfColsTl.prompt_left
1858 From bne_interface_cols_b InfCols,
1859 bne_interface_cols_tl InfColsTl
1860 Where InfCols.interface_code = p_interface_code
1861 And InfCols.INTERFACE_COL_NAME = p_interface_col_code
1865 And InfColsTl.application_id = 800
1862 And InfCols.application_id = 800
1863 And InfCols.interface_code = InfColsTl.interface_code
1864 And InfCols.sequence_num = InfColsTl.sequence_num
1866 And InfColsTl.Language = Userenv('LANG');
1867
1868 l_proc Varchar2(72) := g_package||'create_custom_row';
1869 l_col_prompt bne_interface_cols_tl.prompt_left%Type;
1870
1871 Begin
1872 if g_debug then
1873 hr_utility.set_location('Entering '||l_proc,10);
1874 hr_utility.set_location('p_key :'||p_key,30);
1875 hr_utility.set_location('p_integrator_code :'||p_integrator_code,40);
1876 hr_utility.set_location('p_interface_code :'||p_interface_code,50);
1877 end if;
1878
1879 Open csr_col_prompt;
1880 Fetch csr_col_prompt into l_col_prompt;
1881 Close csr_col_prompt;
1882
1883
1884 Insert Into BEN_CUSTOM_REGION_ITEMS
1885 (
1886 REGION_CODE -- task code / integrator code
1887 ,CUSTOM_KEY -- mgr_per_in_ler_id
1888 ,CUSTOM_TYPE -- integrator code
1889 ,ITEM_NAME -- interface_col_name
1890 ,DISPLAY_FLAG -- Y/N
1891 ,LABEL -- interface_prompt_above
1892 ,ORDR_NUM -- Display Order
1893 )
1894 Values
1895 (
1896 p_region_key --p_integrator_code -- task code / integrator code
1897 ,p_key -- mgr_per_in_ler_id
1898 ,p_integrator_code -- integrator code
1899 ,p_interface_col_code -- interface_col_name
1900 ,'N'
1901 ,l_col_prompt -- interface_prompt_above
1902 ,p_display_seq -- Display Order
1903 );
1904
1905 hr_utility.set_location('Entering '||l_proc,10);
1906 Exception
1907 When Others then
1908 hr_utility.set_location('Error :'||substr(sqlerrm,1,50),100);
1909 hr_utility.set_location('Error :'||substr(sqlerrm,51,100),110);
1910 raise;
1911
1912 End create_custom_row;
1913
1914 --
1915 --------------------------chk_entry_in_custom_table----------------------
1916 --
1917 /* Purpose :
1918 This function checks whether data exists in table BEN_CUSTOM_REGION_ITEMS or not.
1919 If data exists in the table then returns 'Y' else 'N'
1920 */
1921
1922 Function chk_entry_in_custom_table( p_key IN VARCHAR2
1923 ,p_region_key IN VARCHAR2
1924 ,p_integrator_code IN VARCHAR2
1925 ) Return Varchar
1926 IS
1927 Cursor Csr_entry
1928 IS
1929 Select '1'
1930 From BEN_CUSTOM_REGION_ITEMS
1931 Where CUSTOM_TYPE = p_integrator_code
1932 And REGION_CODE = p_region_key
1933 And CUSTOM_KEY = p_key;
1934
1935 l_exists Varchar2(1);
1936 l_return_val Varchar2(1) := 'N';
1937 l_proc Varchar2(72) := g_package||'chk_entry_in_custom_table';
1938
1939 BEGIN
1940 hr_utility.set_location('Entering '||l_proc,10);
1941
1942 Open Csr_entry;
1943 Fetch Csr_entry into l_exists;
1944 Close Csr_entry;
1945
1946 -- If entires are not there insert data into table BEN_CUSTOM_REGION_ITEMS
1947 If l_exists is null then
1948 l_return_val := 'N';
1949 Else
1950 l_return_val := 'Y';
1951 End if;
1952
1953 hr_utility.set_location('Entry Exists (Y/N) : '||l_return_val,100);
1954 hr_utility.set_location('Leaving '||l_proc,200);
1955
1956 return l_return_val;
1957
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 hr_utility.set_location('Error :'||substr(sqlerrm,1,50),100);
1961 hr_utility.set_location('Error :'||substr(sqlerrm,51,100),110);
1962 return 'N';
1963
1964 END chk_entry_in_custom_table;
1965
1966 --
1967 --------------------------manipulate_seleted_data----------------------
1968 --
1969
1970
1971 Procedure manipulate_selected_data( p_key IN VARCHAR2
1972 ,p_region_key IN VARCHAR2
1973 ,p_integrator_code IN VARCHAR2
1974 ,p_interface_code IN VARCHAR2
1975 ,p_interface_col_code IN VARCHAR2
1976 ,p_display_seq IN Number )
1977 IS
1978
1979
1980 l_proc Varchar2(72) := g_package||'chk_entry_in_custom_table';
1981
1982
1983 Begin
1984 hr_utility.set_location('Entering '||l_proc,10);
1985
1989 create_custom_row( p_key => p_key
1986 -- If data not exists in the Custom Table insert data
1987
1988
1990 ,p_region_key => p_region_key
1994 ,p_display_seq => p_display_seq);
1991 ,p_integrator_code => p_integrator_code
1992 ,p_interface_code => p_interface_code
1993 ,p_interface_col_code => p_interface_col_code
1995
1996 hr_utility.set_location('Leaving '||l_proc,200);
1997 EXCEPTION
1998 WHEN OTHERS THEN
1999 hr_utility.set_location('Error :'||substr(sqlerrm,1,50),100);
2000 hr_utility.set_location('Error :'||substr(sqlerrm,51,100),110);
2001 raise;
2002 End manipulate_selected_data;
2003
2004 --
2005 ----------------------delete_custom_data ---------------------------
2006 --
2007 Procedure delete_custom_data(p_key IN VARCHAR2,
2008 p_region_key IN VARCHAR2,
2009 p_integrator_code IN VARCHAR2)
2010 IS
2011 l_exist_in_table Varchar2(1);
2012 BEGIN
2013 l_exist_in_table := chk_entry_in_custom_table( p_key => p_key
2014 ,p_region_key => p_region_key
2015 ,p_integrator_code => p_integrator_code);
2016
2017 hr_utility.set_location('l_exist_in_table :'||l_exist_in_table,20);
2018
2019
2020 If l_exist_in_table = 'Y' Then
2021 hr_utility.set_location('Data Not exists in custom Table ',25);
2022 Delete From BEN_CUSTOM_REGION_ITEMS
2023 Where CUSTOM_KEY = p_key
2024 And REGION_CODE = p_region_key
2025 And CUSTOM_TYPE = p_integrator_code;
2026 End If;
2027 END;
2028
2029 --
2030 --------------------- update_cwb_custom_layout ---------------------
2031 --
2032
2033 Procedure update_cwb_custom_layout( p_key IN VARCHAR2
2034 ,p_region_key IN VARCHAR2
2035 ,p_integrator_code IN VARCHAR2
2036 ,p_interface_code IN VARCHAR2
2037 ,p_act_layout_code IN VARCHAR2
2038 ,p_base_layout_code IN VARCHAR2
2039 ,p_group_pl_id IN NUMBER Default Null
2040 ,p_lf_evt_ocrd_dt IN DATE Default Null
2041 ,p_download_switch OUT NOCOPY VARCHAR2
2042 )
2043 IS
2044 l_num NUMBER := 0;
2045 l_pl_ws_amt_switch VARCHAR(1) := '1';
2046 l_opt1_ws_amt_switch VARCHAR(1) := '1';
2047 l_opt2_ws_amt_switch VARCHAR(1) := '1';
2048 l_opt3_ws_amt_switch VARCHAR(1) := '1';
2049 l_opt4_ws_amt_switch VARCHAR(1) := '1';
2050 l_perf_switch VARCHAR(1) := '1';
2051 l_rank_switch VARCHAR(1) := '1';
2052 l_cpi_attribute1_switch VARCHAR(1) := '1';
2053 l_cpi_attribute2_switch VARCHAR(1) := '1';
2054 l_cpi_attribute3_switch VARCHAR(1) := '1';
2055 l_cpi_attribute4_switch VARCHAR(1) := '1';
2056 l_cpi_attribute5_switch VARCHAR(1) := '1';
2057 l_cpi_attribute6_switch VARCHAR(1) := '1';
2058 l_cpi_attribute7_switch VARCHAR(1) := '1';
2059 l_cpi_attribute8_switch VARCHAR(1) := '1';
2060 l_cpi_attribute9_switch VARCHAR(1) := '1';
2061 l_cpi_attribute10_switch VARCHAR(1) := '1';
2062 l_cpi_attribute11_switch VARCHAR(1) := '1';
2063 l_cpi_attribute12_switch VARCHAR(1) := '1';
2064 l_cpi_attribute13_switch VARCHAR(1) := '1';
2065 l_cpi_attribute14_switch VARCHAR(1) := '1';
2066 l_cpi_attribute15_switch VARCHAR(1) := '1';
2067 l_cpi_attribute16_switch VARCHAR(1) := '1';
2068 l_cpi_attribute17_switch VARCHAR(1) := '1';
2069 l_cpi_attribute18_switch VARCHAR(1) := '1';
2070 l_cpi_attribute19_switch VARCHAR(1) := '1';
2071 l_cpi_attribute20_switch VARCHAR(1) := '1';
2072 l_cpi_attribute21_switch VARCHAR(1) := '1';
2073 l_cpi_attribute22_switch VARCHAR(1) := '1';
2074 l_cpi_attribute23_switch VARCHAR(1) := '1';
2075 l_cpi_attribute24_switch VARCHAR(1) := '1';
2076 l_cpi_attribute25_switch VARCHAR(1) := '1';
2077 l_cpi_attribute26_switch VARCHAR(1) := '1';
2078 l_cpi_attribute27_switch VARCHAR(1) := '1';
2079 l_cpi_attribute28_switch VARCHAR(1) := '1';
2080 l_cpi_attribute29_switch VARCHAR(1) := '1';
2081 l_cpi_attribute30_switch VARCHAR(1) := '1';
2082 l_promotion_switch VARCHAR(1) := '1';
2083 l_pl_other_rates_switch VARCHAR(1) := '1';
2084 l_opt1_other_rates_switch VARCHAR(1) := '1';
2085 l_opt2_other_rates_switch VARCHAR(1) := '1';
2086 l_opt3_other_rates_switch VARCHAR(1) := '1';
2087 l_opt4_other_rates_switch VARCHAR(1) := '1';
2088
2089 Cursor csr_bne_data
2090 IS
2091 Select infCols.interface_col_name inf_col_name
2092 ,infCols.sequence_num inf_Seq_Num
2093 ,layCols.SEQUENCE_NUM Dis_Seq_Num
2094 From bne_interfaces_b inf,
2095 bne_interface_cols_b infCols,
2096 bne_layout_cols layCols,
2097 bne_layout_blocks_b layBlk
2098 Where inf.integrator_code = p_integrator_code
2099 And inf.integrator_app_id = 800
2100 And inf.interface_code = p_interface_code
2101 And inf.application_id = 800
2102 And inf.interface_code = infCols.interface_code
2103 And infCols.application_id = 800
2104 And infCols.sequence_num = layCols.INTERFACE_SEQ_NUM
2105 And layCols.layout_code = p_base_layout_code
2106 And layCols.application_id = 800
2107 And layCols.layout_code = layBlk.layout_code
2108 And layCols.block_id = layBlk.block_id
2109 And layBlk.STYLE_CLASS = 'BNE_LINES'
2110 And layBlk.application_id = 800;
2111
2112 Cursor csr_cust_data
2113 IS
2114 Select cust.ITEM_NAME inf_col_name
2115 ,infCols.SEQUENCE_NUM inf_Seq_Num
2116 ,cust.ORDR_NUM Dis_Seq_Num
2117 From BEN_CUSTOM_REGION_ITEMS cust,
2118 Bne_interface_cols_b infCols
2119 Where cust.CUSTOM_KEY = p_key
2123 And infCols.interface_code = p_interface_code
2120 And cust.REGION_CODE = p_region_key --p_integrator_code
2121 And cust.CUSTOM_TYPE = p_integrator_code
2122 And cust.ITEM_NAME = infCols.INTERFACE_COL_NAME
2124 And infCols.application_id = 800;
2125
2126
2127
2128 cursor do_not_disturb is
2129 select interface_seq_num inf_Seq_Num,
2130 2000+rownum Dis_Seq_Num
2131 from bne_layout_cols
2132 where interface_seq_num in (130,131,132,133,134)
2133 and layout_code = p_base_layout_code
2134 and interface_code = p_interface_code
2135 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2136 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2137 and application_id = 800;
2138
2139 cursor group_pl_key is
2140 select decode (col1.interface_seq_num, 7, 1,
2141 159,2,
2142 165,3,
2143 9,4,
2144 5,5,
2145 164,6
2146 ),
2147 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2148 from bne_layout_cols col1,
2149 bne_layout_cols col2
2150 where col1.interface_seq_num in (7,159,165,9,5,164)
2151 and col1.layout_code = p_base_layout_code
2152 and col1.interface_code = p_interface_code
2153 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2154 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2155 and col1.application_id = 800
2156 and col1.interface_code = col2.interface_code
2157 and col2.application_id = 800
2158 and col2.interface_seq_num = 12
2159 and col2.layout_code = p_act_layout_code
2160 order by 1;
2161
2162 cursor group_opt1_key is
2163 select decode (col1.interface_seq_num, 25, 1,
2164 160,2,
2165 166,3,
2166 27,4
2167 ),
2168 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2169 from bne_layout_cols col1,
2170 bne_layout_cols col2
2171 where col1.interface_seq_num in (25,160,166,27)
2172 and col1.layout_code = p_base_layout_code
2173 and col1.interface_code = col2.interface_code
2174 and col2.interface_seq_num = 30
2175 and col2.interface_code = p_interface_code
2176 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2177 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2178 and col2.layout_code = p_act_layout_code
2179 and col1.application_id = 800
2180 and col2.application_id = 800
2181 order by 1;
2182
2183 cursor group_opt2_key is
2184 select decode (col1.interface_seq_num, 43, 1,
2185 161,2,
2186 167,3,
2187 45,4
2188 ),
2189 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2190 from bne_layout_cols col1,
2191 bne_layout_cols col2
2192 where col1.interface_seq_num in (43,161,167,45)
2193 and col1.layout_code = p_base_layout_code
2194 and col1.interface_code = col2.interface_code
2195 and col2.interface_seq_num = 48
2196 and col2.interface_code = p_interface_code
2197 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2198 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2199 and col2.layout_code = p_act_layout_code
2200 and col1.application_id = 800
2201 and col2.application_id = 800
2202 order by 1;
2203
2204 cursor group_opt3_key is
2205 select decode (col1.interface_seq_num, 61, 1,
2206 162,2,
2207 168,3,
2208 63,4
2209 ),
2210 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2211 from bne_layout_cols col1,
2212 bne_layout_cols col2
2213 where col1.interface_seq_num in (61,162,168,63)
2214 and col1.layout_code = p_base_layout_code
2215 and col1.interface_code = col2.interface_code
2216 and col2.interface_seq_num = 66
2217 and col2.interface_code = p_interface_code
2218 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2219 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2220 and col2.layout_code = p_act_layout_code
2221 and col1.application_id = 800
2222 and col2.application_id = 800
2223 order by 1;
2224
2225 cursor group_opt4_key is
2226 select decode (col1.interface_seq_num, 79, 1,
2227 163,2,
2228 169,3,
2229 81,4
2230 ),
2231 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2232 from bne_layout_cols col1,
2233 bne_layout_cols col2
2234 where col1.interface_seq_num in (79,163,169,81)
2235 and col1.layout_code = p_base_layout_code
2236 and col1.interface_code = col2.interface_code
2237 and col2.interface_seq_num = 84
2238 and col2.interface_code = p_interface_code
2239 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2240 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2241 and col2.layout_code = p_act_layout_code
2242 and col1.application_id = 800
2243 and col2.application_id = 800
2244 order by 1;
2245
2246 cursor group_rank_key is
2247 select decode (col1.interface_seq_num, 125, 1,
2248 197,2
2249 ),
2250 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2251 from bne_layout_cols col1,
2252 bne_layout_cols col2
2253 where col1.interface_seq_num in (125,197)
2254 and col1.layout_code = p_base_layout_code
2255 and col1.interface_code = col2.interface_code
2256 and col2.interface_seq_num = 3
2257 and col2.interface_code = p_interface_code
2258 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2259 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2260 and col2.layout_code = p_act_layout_code
2261 and col1.application_id = 800
2262 and col2.application_id = 800
2263 order by 1;
2264
2265 cursor group_grade_key is
2269 ),
2266 select decode (col1.interface_seq_num,
2267 126,1,
2268 105,2
2270 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2271 from bne_layout_cols col1,
2272 bne_layout_cols col2
2273 where col1.interface_seq_num in (105,126)
2274 and col1.layout_code = p_base_layout_code
2275 and col1.interface_code = col2.interface_code
2276 and col2.interface_seq_num = 191
2277 and col2.interface_code = p_interface_code
2278 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2279 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2280 and col2.layout_code = p_act_layout_code
2281 and col1.application_id = 800
2282 and col2.application_id = 800
2283 order by 1;
2284
2285 --bug fix 12988780 removed 192
2286 cursor group_job_key is
2287 select decode (col1.interface_seq_num,
2288 -- 192,1,
2289 126,1,
2290 105,2
2291 ),
2292 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2293 from bne_layout_cols col1,
2294 bne_layout_cols col2
2295 where col1.interface_seq_num in (105,126)
2296 and col1.layout_code = p_base_layout_code
2297 and col1.interface_code = col2.interface_code
2298 and col2.interface_seq_num = 152
2299 and col2.interface_code = p_interface_code
2300 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2301 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2302 and col2.layout_code = p_act_layout_code
2303 and col1.application_id = 800
2304 and col2.application_id = 800
2305 order by 1;
2306
2307 cursor group_position_key is
2308 select decode (col1.interface_seq_num,
2312 ),
2309 152,1,
2310 126,2,
2311 105,3
2313 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2314 from bne_layout_cols col1,
2315 bne_layout_cols col2
2316 where col1.interface_seq_num in (105,152,126)
2317 and col1.layout_code = p_base_layout_code
2318 and col1.interface_code = col2.interface_code
2319 and col2.interface_seq_num = 192
2320 and col2.interface_code = p_interface_code
2321 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2322 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2323 and col2.layout_code = p_act_layout_code
2324 and col1.application_id = 800
2325 and col2.application_id = 800
2326 order by 1;
2327
2328 Cursor add_cols IS
2329 (Select interface_seq_num inf_Seq_Num
2330 , sequence_num Dis_Seq_Num
2331 , decode (interface_seq_num, 170, 1,
2332 171,2,
2333 172,3,
2334 173,4,
2335 174,5,
2336 175,6,
2337 176,7,
2338 177,8,
2339 178,9,
2340 179,10,
2341 180,11,
2342 181,12,
2343 182,13,
2344 189,14,
2345 158,15,
2346 194,16,
2347 195,17,
2348 196,18,
2349 198,19,
2350 188,20,
2351 190,21
2352 ) order_in_layout
2353 From bne_layout_cols
2354 Where interface_code = p_interface_code
2355 and (substr(p_interface_code,1,15) = 'BEN_CWB_WS_INTF'
2356 OR p_interface_code = 'BEN_CWB_WRK_SHT_INTF')
2357 And interface_seq_num in (170,171,172,173,174,175,176,177,178,179,180,181,182,189,158,188,190,194,195,196,198)
2358 And layout_code = p_base_layout_code
2359 And application_id = 800)
2360 union
2361 (Select interface_seq_num inf_Seq_Num
2362 ,sequence_num Dis_Seq_Num
2363 , interface_seq_num order_in_layout
2364 From bne_layout_cols
2365 Where interface_code = p_interface_code
2366 And p_interface_code = 'BEN_CWB_BGT_SHT_INTF'
2367 And interface_seq_num in (37,40,41,42,43,44,45,46,47,48,49,50,51,52,53)
2368 And layout_code = p_base_layout_code
2369 And application_id = 800
2370 )
2371 union
2372 (Select interface_seq_num inf_Seq_Num
2373 ,sequence_num Dis_Seq_Num
2374 , interface_seq_num order_in_layout
2375 From bne_layout_cols
2376 Where interface_code = p_interface_code
2377 And p_interface_code = 'BEN_CWB_SUMM_DIR_REP_INTF'
2378 And interface_seq_num in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)
2379 And layout_code = p_base_layout_code
2380 And application_id = 800
2381 ) Order by 3 ;
2382 l_application_id Number := 800;
2383 l_exist_in_table Varchar2(1);
2384 l_proc Varchar2(72) := g_package||'update_cwb_custom_layout';
2385
2386 BEGIN
2387 If g_debug then
2388 hr_utility.set_location('Entering '||l_proc,10);
2389 End if;
2390 g_interface_code := p_interface_code;
2391 --
2395 l_exist_in_table := chk_entry_in_custom_table( p_key => p_key
2392 delete_cwb_layout_cols(p_layout_code => p_act_layout_code
2393 ,p_application_id => 800);
2394
2396 ,p_region_key => p_region_key
2397 ,p_integrator_code => p_integrator_code);
2398 If g_debug then
2399 hr_utility.set_location('l_exist_in_table :'||l_exist_in_table,20);
2400 End if;
2401
2402 -- If data not exists in the custom table
2403 If l_exist_in_table = 'N' Then
2404 For l_bne_row In csr_bne_data
2405 Loop
2406
2407 IF(l_bne_row.inf_Seq_Num = 12) THEN
2408 l_pl_ws_amt_switch := '2';
2409 ELSIF(l_bne_row.inf_Seq_Num = 30) THEN
2410 l_opt1_ws_amt_switch := '2';
2411 ELSIF(l_bne_row.inf_Seq_Num = 48) THEN
2412 l_opt2_ws_amt_switch := '2';
2413 ELSIF(l_bne_row.inf_Seq_Num = 66) THEN
2414 l_opt3_ws_amt_switch := '2';
2415 ELSIF(l_bne_row.inf_Seq_Num = 84) THEN
2416 l_opt4_ws_amt_switch := '2';
2417 ELSIF(l_bne_row.inf_Seq_Num = 151) THEN
2418 l_perf_switch := '2';
2419 ELSIF(l_bne_row.inf_Seq_Num = 3) THEN
2420 l_rank_switch := '2';
2421 ELSIF(l_bne_row.inf_Seq_Num = 200) THEN
2422 l_cpi_attribute1_switch := '2';
2423 ELSIF(l_bne_row.inf_Seq_Num = 201) THEN
2424 l_cpi_attribute2_switch := '2';
2425 ELSIF(l_bne_row.inf_Seq_Num = 202) THEN
2426 l_cpi_attribute3_switch := '2';
2427 ELSIF(l_bne_row.inf_Seq_Num = 203) THEN
2428 l_cpi_attribute4_switch := '2';
2429 ELSIF(l_bne_row.inf_Seq_Num = 204) THEN
2430 l_cpi_attribute5_switch := '2';
2431 ELSIF(l_bne_row.inf_Seq_Num = 205) THEN
2432 l_cpi_attribute6_switch := '2';
2433 ELSIF(l_bne_row.inf_Seq_Num = 206) THEN
2434 l_cpi_attribute7_switch := '2';
2435 ELSIF(l_bne_row.inf_Seq_Num = 207) THEN
2436 l_cpi_attribute8_switch := '2';
2437 ELSIF(l_bne_row.inf_Seq_Num = 208) THEN
2438 l_cpi_attribute9_switch := '2';
2439 ELSIF(l_bne_row.inf_Seq_Num = 209) THEN
2440 l_cpi_attribute10_switch := '2';
2441 ELSIF(l_bne_row.inf_Seq_Num = 210) THEN
2442 l_cpi_attribute11_switch := '2';
2443 ELSIF(l_bne_row.inf_Seq_Num = 211) THEN
2444 l_cpi_attribute12_switch := '2';
2445 ELSIF(l_bne_row.inf_Seq_Num = 212) THEN
2446 l_cpi_attribute13_switch := '2';
2447 ELSIF(l_bne_row.inf_Seq_Num = 213) THEN
2448 l_cpi_attribute14_switch := '2';
2449 ELSIF(l_bne_row.inf_Seq_Num = 214) THEN
2450 l_cpi_attribute15_switch := '2';
2451 ELSIF(l_bne_row.inf_Seq_Num = 215) THEN
2452 l_cpi_attribute16_switch := '2';
2453 ELSIF(l_bne_row.inf_Seq_Num = 216) THEN
2454 l_cpi_attribute17_switch := '2';
2455 ELSIF(l_bne_row.inf_Seq_Num = 217) THEN
2456 l_cpi_attribute18_switch := '2';
2457 ELSIF(l_bne_row.inf_Seq_Num = 218) THEN
2458 l_cpi_attribute19_switch := '2';
2459 ELSIF(l_bne_row.inf_Seq_Num = 219) THEN
2460 l_cpi_attribute20_switch := '2';
2461 ELSIF(l_bne_row.inf_Seq_Num = 220) THEN
2462 l_cpi_attribute21_switch := '2';
2463 ELSIF(l_bne_row.inf_Seq_Num = 221) THEN
2464 l_cpi_attribute22_switch := '2';
2465 ELSIF(l_bne_row.inf_Seq_Num = 222) THEN
2469 ELSIF(l_bne_row.inf_Seq_Num = 224) THEN
2466 l_cpi_attribute23_switch := '2';
2467 ELSIF(l_bne_row.inf_Seq_Num = 223) THEN
2468 l_cpi_attribute24_switch := '2';
2470 l_cpi_attribute25_switch := '2';
2471 ELSIF(l_bne_row.inf_Seq_Num = 225) THEN
2472 l_cpi_attribute26_switch := '2';
2473 ELSIF(l_bne_row.inf_Seq_Num = 226) THEN
2474 l_cpi_attribute27_switch := '2';
2475 ELSIF(l_bne_row.inf_Seq_Num = 227) THEN
2476 l_cpi_attribute28_switch := '2';
2477 ELSIF(l_bne_row.inf_Seq_Num = 228) THEN
2478 l_cpi_attribute29_switch := '2';
2479 ELSIF(l_bne_row.inf_Seq_Num = 229) THEN
2480 l_cpi_attribute30_switch := '2';
2481 ELSIF(l_bne_row.inf_Seq_Num in (126,152,191,192) ) THEN
2482 l_promotion_switch := '2';
2483 ELSIF(l_bne_row.inf_Seq_Num in (8,10,11,19,20,21) ) THEN
2484 l_pl_other_rates_switch := '2';
2485 ELSIF(l_bne_row.inf_Seq_Num in (26,28,29,37,38,39) ) THEN
2486 l_opt1_other_rates_switch := '2';
2487 ELSIF(l_bne_row.inf_Seq_Num in (44,46,47,55,56,57) ) THEN
2488 l_opt2_other_rates_switch := '2';
2489 ELSIF(l_bne_row.inf_Seq_Num in (62,64,65,73,74,75) ) THEN
2490 l_opt3_other_rates_switch := '2';
2494
2491 ELSIF(l_bne_row.inf_Seq_Num in (80,82,83,91,92,931) ) THEN
2492 l_opt4_other_rates_switch := '2';
2493 END IF;
2495
2496 insert_cwb_layout_cols_row( p_application_id => 800
2497 ,p_base_layout_code => p_base_layout_code
2498 ,p_act_layout_code => p_act_layout_code
2499 ,p_inf_seq => l_bne_row.inf_Seq_Num
2500 ,p_dis_seq => l_bne_row.Dis_Seq_Num * 10
2501 ,p_group_pl_id => p_group_pl_id
2502 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2503 End Loop;
2504 Else
2505 For l_cust_row In csr_cust_data
2506 Loop
2507 IF(l_cust_row.inf_Seq_Num = 12) THEN
2508 l_pl_ws_amt_switch := '2';
2509 ELSIF(l_cust_row.inf_Seq_Num = 30) THEN
2510 l_opt1_ws_amt_switch := '2';
2511 ELSIF(l_cust_row.inf_Seq_Num = 48) THEN
2512 l_opt2_ws_amt_switch := '2';
2513 ELSIF(l_cust_row.inf_Seq_Num = 66) THEN
2514 l_opt3_ws_amt_switch := '2';
2515 ELSIF(l_cust_row.inf_Seq_Num = 84) THEN
2516 l_opt4_ws_amt_switch := '2';
2517 ELSIF(l_cust_row.inf_Seq_Num = 151) THEN
2518 l_perf_switch := '2';
2519 ELSIF(l_cust_row.inf_Seq_Num = 3) THEN
2520 l_rank_switch := '2';
2521 ELSIF(l_cust_row.inf_Seq_Num = 200) THEN
2522 l_cpi_attribute1_switch := '2';
2523 ELSIF(l_cust_row.inf_Seq_Num = 201) THEN
2524 l_cpi_attribute2_switch := '2';
2525 ELSIF(l_cust_row.inf_Seq_Num = 202) THEN
2526 l_cpi_attribute3_switch := '2';
2527 ELSIF(l_cust_row.inf_Seq_Num = 203) THEN
2528 l_cpi_attribute4_switch := '2';
2529 ELSIF(l_cust_row.inf_Seq_Num = 204) THEN
2530 l_cpi_attribute5_switch := '2';
2531 ELSIF(l_cust_row.inf_Seq_Num = 205) THEN
2532 l_cpi_attribute6_switch := '2';
2533 ELSIF(l_cust_row.inf_Seq_Num = 206) THEN
2534 l_cpi_attribute7_switch := '2';
2535 ELSIF(l_cust_row.inf_Seq_Num = 207) THEN
2536 l_cpi_attribute8_switch := '2';
2537 ELSIF(l_cust_row.inf_Seq_Num = 208) THEN
2538 l_cpi_attribute9_switch := '2';
2539 ELSIF(l_cust_row.inf_Seq_Num = 209) THEN
2540 l_cpi_attribute10_switch := '2';
2541 ELSIF(l_cust_row.inf_Seq_Num = 210) THEN
2542 l_cpi_attribute11_switch := '2';
2543 ELSIF(l_cust_row.inf_Seq_Num = 211) THEN
2544 l_cpi_attribute12_switch := '2';
2545 ELSIF(l_cust_row.inf_Seq_Num = 212) THEN
2546 l_cpi_attribute13_switch := '2';
2547 ELSIF(l_cust_row.inf_Seq_Num = 213) THEN
2548 l_cpi_attribute14_switch := '2';
2549 ELSIF(l_cust_row.inf_Seq_Num = 214) THEN
2550 l_cpi_attribute15_switch := '2';
2551 ELSIF(l_cust_row.inf_Seq_Num = 215) THEN
2552 l_cpi_attribute16_switch := '2';
2553 ELSIF(l_cust_row.inf_Seq_Num = 216) THEN
2554 l_cpi_attribute17_switch := '2';
2555 ELSIF(l_cust_row.inf_Seq_Num = 217) THEN
2556 l_cpi_attribute18_switch := '2';
2557 ELSIF(l_cust_row.inf_Seq_Num = 218) THEN
2558 l_cpi_attribute19_switch := '2';
2559 ELSIF(l_cust_row.inf_Seq_Num = 219) THEN
2560 l_cpi_attribute20_switch := '2';
2561 ELSIF(l_cust_row.inf_Seq_Num = 220) THEN
2562 l_cpi_attribute21_switch := '2';
2563 ELSIF(l_cust_row.inf_Seq_Num = 221) THEN
2564 l_cpi_attribute22_switch := '2';
2565 ELSIF(l_cust_row.inf_Seq_Num = 222) THEN
2566 l_cpi_attribute23_switch := '2';
2567 ELSIF(l_cust_row.inf_Seq_Num = 223) THEN
2568 l_cpi_attribute24_switch := '2';
2569 ELSIF(l_cust_row.inf_Seq_Num = 224) THEN
2570 l_cpi_attribute25_switch := '2';
2571 ELSIF(l_cust_row.inf_Seq_Num = 225) THEN
2572 l_cpi_attribute26_switch := '2';
2573 ELSIF(l_cust_row.inf_Seq_Num = 226) THEN
2574 l_cpi_attribute27_switch := '2';
2575 ELSIF(l_cust_row.inf_Seq_Num = 227) THEN
2576 l_cpi_attribute28_switch := '2';
2577 ELSIF(l_cust_row.inf_Seq_Num = 228) THEN
2578 l_cpi_attribute29_switch := '2';
2579 ELSIF(l_cust_row.inf_Seq_Num = 229) THEN
2580 l_cpi_attribute30_switch := '2';
2581 ELSIF(l_cust_row.inf_Seq_Num in (126,152,191,192) ) THEN
2582 l_promotion_switch := '2';
2583 ELSIF(l_cust_row.inf_Seq_Num in (8,10,11,19,20,21) ) THEN
2584 l_pl_other_rates_switch := '2';
2585 ELSIF(l_cust_row.inf_Seq_Num in (26,28,29,37,38,39) ) THEN
2586 l_opt1_other_rates_switch := '2';
2587 ELSIF(l_cust_row.inf_Seq_Num in (44,46,47,55,56,57) ) THEN
2588 l_opt2_other_rates_switch := '2';
2589 ELSIF(l_cust_row.inf_Seq_Num in (62,64,65,73,74,75) ) THEN
2590 l_opt3_other_rates_switch := '2';
2591 ELSIF(l_cust_row.inf_Seq_Num in (80,82,83,91,92,931) ) THEN
2592 l_opt4_other_rates_switch := '2';
2593 END IF;
2594
2595
2596 insert_cwb_layout_cols_row( p_application_id => 800
2597 ,p_base_layout_code => p_base_layout_code
2598 ,p_act_layout_code => p_act_layout_code
2602 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2599 ,p_inf_seq => l_cust_row.inf_Seq_Num
2600 ,p_dis_seq => l_cust_row.dis_Seq_Num * 10
2601 ,p_group_pl_id => p_group_pl_id
2603
2604 End Loop;
2605 End if;
2606
2607 l_num := 1;
2608 -- Plan Group Cols
2609 For l_row In group_pl_key
2610 Loop
2611 insert_cwb_layout_cols_row( p_application_id => 800
2612 ,p_base_layout_code => p_base_layout_code
2613 ,p_act_layout_code => p_act_layout_code
2614 ,p_inf_seq => l_row.inf_Seq_Num
2615 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2616 ,p_group_pl_id => p_group_pl_id
2617 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2618
2619 l_num := l_num + 1;
2620 End Loop;
2621
2622 l_num := 1;
2623 -- Option1 Group Cols
2624 For l_row In group_opt1_key
2625 Loop
2626 insert_cwb_layout_cols_row( p_application_id => 800
2627 ,p_base_layout_code => p_base_layout_code
2628 ,p_act_layout_code => p_act_layout_code
2629 ,p_inf_seq => l_row.inf_Seq_Num
2630 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2631 ,p_group_pl_id => p_group_pl_id
2632 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
2633
2634 l_num := l_num + 1;
2635 End Loop;
2636 -- Option2 Group Cols
2637 For l_row In group_opt2_key
2638 Loop
2639 insert_cwb_layout_cols_row( p_application_id => 800
2640 ,p_base_layout_code => p_base_layout_code
2641 ,p_act_layout_code => p_act_layout_code
2642 ,p_inf_seq => l_row.inf_Seq_Num
2643 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2644 ,p_group_pl_id => p_group_pl_id
2645 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2646
2647 l_num := l_num + 1;
2648 End Loop;
2649 -- Option3 Group Cols
2650 l_num := 1;
2651 For l_row In group_opt3_key
2652 Loop
2653 insert_cwb_layout_cols_row( p_application_id => 800
2654 ,p_base_layout_code => p_base_layout_code
2655 ,p_act_layout_code => p_act_layout_code
2656 ,p_inf_seq => l_row.inf_Seq_Num
2657 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2658 ,p_group_pl_id => p_group_pl_id
2659 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2660
2661 l_num := l_num + 1;
2662 End Loop;
2663 l_num := 1;
2664 -- Option4 Group Cols
2665 For l_row In group_opt4_key
2666 Loop
2667 insert_cwb_layout_cols_row( p_application_id => 800
2668 ,p_base_layout_code => p_base_layout_code
2669 ,p_act_layout_code => p_act_layout_code
2670 ,p_inf_seq => l_row.inf_Seq_Num
2671 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2672 ,p_group_pl_id => p_group_pl_id
2673 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2674 l_num := l_num + 1;
2675
2676 End Loop;
2677
2678
2679 l_num := 1;
2680 -- Rank Group Cols
2681 For l_row In group_rank_key
2682 Loop
2683 insert_cwb_layout_cols_row( p_application_id => 800
2684 ,p_base_layout_code => p_base_layout_code
2685 ,p_act_layout_code => p_act_layout_code
2686 ,p_inf_seq => l_row.inf_Seq_Num
2687 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2688 ,p_group_pl_id => p_group_pl_id
2689 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2690 l_num := l_num + 1;
2691
2692 End Loop;
2693
2694 l_num := 1;
2695 -- Group Job Cols
2696 For l_row In group_job_key
2697 Loop
2698 insert_cwb_layout_cols_row( p_application_id => 800
2699 ,p_base_layout_code => p_base_layout_code
2700 ,p_act_layout_code => p_act_layout_code
2701 ,p_inf_seq => l_row.inf_Seq_Num
2702 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2703 ,p_group_pl_id => p_group_pl_id
2704 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2705 l_num := l_num + 1;
2706 End Loop;
2707
2708 l_num := 1;
2709 -- Group Position Cols
2710 For l_row In group_position_key
2711 Loop
2712 insert_cwb_layout_cols_row( p_application_id => 800
2713 ,p_base_layout_code => p_base_layout_code
2717 ,p_group_pl_id => p_group_pl_id
2714 ,p_act_layout_code => p_act_layout_code
2715 ,p_inf_seq => l_row.inf_Seq_Num
2716 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2718 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2719 l_num := l_num + 1;
2720 End Loop;
2721
2722 l_num := 1;
2723 -- Grade Group Cols
2724 For l_row In group_grade_key
2725 Loop
2726 insert_cwb_layout_cols_row( p_application_id => 800
2727 ,p_base_layout_code => p_base_layout_code
2728 ,p_act_layout_code => p_act_layout_code
2729 ,p_inf_seq => l_row.inf_Seq_Num
2730 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2731 ,p_group_pl_id => p_group_pl_id
2732 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2733 l_num := l_num + 1;
2734 End Loop;
2735
2736 l_num := 10;
2737
2738 For l_row In add_cols
2739 Loop
2740 insert_cwb_layout_cols_row( p_application_id => 800
2741 ,p_base_layout_code => p_base_layout_code
2742 ,p_act_layout_code => p_act_layout_code
2743 ,p_inf_seq => l_row.inf_Seq_Num
2744 ,p_dis_seq => l_num
2745 ,p_group_pl_id => p_group_pl_id
2746 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2747
2748 l_num := l_num + 10;
2749
2750 END Loop;
2751
2752 -- Don't distrub Cols
2753 For l_row In do_not_disturb
2754 Loop
2755 insert_cwb_layout_cols_row( p_application_id => 800
2756 ,p_base_layout_code => p_base_layout_code
2757 ,p_act_layout_code => p_act_layout_code
2758 ,p_inf_seq => l_row.inf_Seq_Num
2759 ,p_dis_seq => l_row.dis_Seq_Num
2760 ,p_group_pl_id => p_group_pl_id
2761 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
2762
2763
2764 End Loop;
2765
2766 p_download_switch := '2' ||
2767 l_pl_ws_amt_switch||
2768 l_opt1_ws_amt_switch||
2769 l_opt2_ws_amt_switch||
2770 l_opt3_ws_amt_switch||
2771 l_opt4_ws_amt_switch||
2772 l_perf_switch||
2773 l_rank_switch||
2774 l_cpi_attribute1_switch||
2775 l_cpi_attribute2_switch||
2776 l_cpi_attribute3_switch||
2777 l_cpi_attribute4_switch||
2778 l_cpi_attribute5_switch||
2779 l_cpi_attribute6_switch||
2780 l_cpi_attribute7_switch||
2781 l_cpi_attribute8_switch||
2782 l_cpi_attribute9_switch||
2783 l_cpi_attribute10_switch||
2784 l_cpi_attribute11_switch||
2785 l_cpi_attribute12_switch||
2786 l_cpi_attribute13_switch||
2787 l_cpi_attribute14_switch||
2788 l_cpi_attribute15_switch||
2789 l_cpi_attribute16_switch||
2790 l_cpi_attribute17_switch||
2791 l_cpi_attribute18_switch||
2792 l_cpi_attribute19_switch||
2793 l_cpi_attribute20_switch||
2794 l_cpi_attribute21_switch||
2795 l_cpi_attribute22_switch||
2796 l_cpi_attribute23_switch||
2797 l_cpi_attribute24_switch||
2798 l_cpi_attribute25_switch||
2799 l_cpi_attribute26_switch||
2800 l_cpi_attribute27_switch||
2801 l_cpi_attribute28_switch||
2802 l_cpi_attribute29_switch||
2803 l_cpi_attribute30_switch||
2804 l_promotion_switch||
2805 l_pl_other_rates_switch||
2806 l_opt1_other_rates_switch||
2807 l_opt2_other_rates_switch||
2808 l_opt3_other_rates_switch||
2809 l_opt4_other_rates_switch;
2810
2811 p_download_switch := REPLACE(REPLACE(p_download_switch,'1','0'),'2','1');
2812 p_download_switch := nvl(lpad(int2hex(bin2int(substr(p_download_switch,1,28))),7,0)||
2813 int2hex(bin2int(rpad(substr(p_download_switch,29),28,0))),'0000000');
2814 If g_debug then
2815 hr_utility.set_location('Leaving '||l_proc,10);
2816 END IF;
2817 Exception
2818 When others then
2819 Null;
2820 End update_cwb_custom_layout;
2821
2822 --
2823 ------------------- upsert_webadi_download_records ------------------------
2824 --
2825
2826 Procedure upsert_webadi_download_records(p_session_id IN Varchar2,
2827 p_download_type IN Varchar2,
2828 p_param1 IN Varchar2 default null,
2829 p_param2 IN Varchar2 default null,
2830 p_param3 IN Varchar2 default null,
2831 p_param4 IN Varchar2 default null,
2835 p_param8 IN Varchar2 default null,
2832 p_param5 IN Varchar2 default null,
2833 p_param6 IN Varchar2 default null,
2834 p_param7 IN Varchar2 default null,
2836 p_param9 IN Varchar2 default null,
2837 p_param10 IN Varchar2 default null)
2838 Is
2839 begin
2840 --
2841 --
2842 -- Store the time of download.
2843 --
2844 icx_sec.putSessionAttributeValue(p_session_id => p_session_id,
2845 p_name => p_download_type||'_TIME',
2846 p_value => to_char(sysdate,'YYYYMMDDHH24MISS')
2847 );
2848 --
2849 -- Store the parameter.
2850 --
2851 icx_sec.putSessionAttributeValue(p_session_id => p_session_id,
2852 p_name => p_download_type,
2853 p_value => p_param1
2854 );
2855
2856 --
2857 --
2858 End upsert_webadi_download_records;
2859 --
2860 --
2861
2862 --
2863 --------------------------validate_grade_range----------------------
2864 --
2865 FUNCTION validate_grade_range(
2866 P_PL_PERSON_RATE_ID IN VARCHAR2
2867 ,P_P_OPT1_PERSON_RATE_ID IN VARCHAR2
2868 ,P_P_OPT2_PERSON_RATE_ID IN VARCHAR2
2869 ,P_P_OPT3_PERSON_RATE_ID IN VARCHAR2
2870 ,P_P_OPT4_PERSON_RATE_ID IN VARCHAR2
2871 ,P_PL_WS_VAL IN VARCHAR2
2872 ,P_OPT1_WS_VAL IN VARCHAR2
2873 ,P_OPT2_WS_VAL IN VARCHAR2
2874 ,P_OPT3_WS_VAL IN VARCHAR2
2875 ,P_OPT4_WS_VAL IN VARCHAR2
2876 )
2877 RETURN VARCHAR2
2878 IS
2879
2880 cursor c_plan_validation ( l_group_per_in_ler_id number) is
2881 select
2882 nvl(lcl_pl.grade_range_validation,grp_pl.grade_range_validation) grade_range_validation
2883 from
2884 ben_cwb_pl_dsgn grp_pl
2885 ,ben_cwb_pl_dsgn lcl_pl
2886 ,ben_cwb_person_rates rates
2887 where rates.group_per_in_ler_id = l_group_per_in_ler_id
2888 and rates.group_pl_id = grp_pl.pl_id
2889 and rates.lf_evt_ocrd_dt = grp_pl.lf_evt_ocrd_dt
2890 and grp_pl.oipl_id = -1
2891 and rates.pl_id = lcl_pl.pl_id
2895
2892 and rates.lf_evt_ocrd_dt = lcl_pl.lf_evt_ocrd_dt
2893 and lcl_pl.oipl_id = -1
2894 and rownum = 1;
2896 cursor c_plan_salary ( l_group_per_in_ler_id number,l_ws_val number) is
2897 select
2898 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
2899 decode(l_ws_val,'-0.0000000000000001',plRt.ws_val,l_ws_val) ws_val,
2900 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
2901 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
2902 from
2903 ben_cwb_pl_dsgn pl
2904 ,ben_cwb_person_rates plRt
2905 ,ben_cwb_person_info per
2906 where plRt.group_per_in_ler_id = l_group_per_in_ler_id
2907 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
2908 and plRt.pl_id = pl.pl_id
2909 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
2910 and plRt.oipl_id = pl.oipl_id
2911 and pl.oipl_id = -1
2912 and pl.ws_sub_acty_typ_cd = 'ICM7';
2913
2914 cursor c_grade_range( l_group_per_in_ler_id number) is
2915 select
2916 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
2917 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
2918 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
2919 from
2920 ben_cwb_pl_dsgn pl
2921 ,ben_cwb_person_rates plRt
2922 ,ben_cwb_person_info per
2923 where plRt.group_per_in_ler_id = l_group_per_in_ler_id
2924 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
2925 and plRt.pl_id = pl.pl_id
2926 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
2927 and plRt.oipl_id = pl.oipl_id
2928 and pl.oipl_id = -1;
2929
2930 cursor c_option_salary ( l_rate_id number, l_ws_val number) is
2931 select
2932 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
2933 decode(l_ws_val,'-0.0000000000000001',optRt.ws_val,l_ws_val) ws_val,
2934 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
2935 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max,
2936 per.group_per_in_ler_id
2937 from
2938 ben_cwb_pl_dsgn pl
2939 ,ben_cwb_person_rates optRt
2940 ,ben_cwb_person_info per
2941 where optRt.person_rate_id = l_rate_id
2942 and optRt.group_per_in_ler_id = per.group_per_in_ler_id
2943 and optRt.pl_id = pl.pl_id
2947 and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
2944 and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
2945 and optRt.oipl_id = pl.oipl_id
2946 and pl.oipl_id <> -1
2948
2949 l_proc varchar2(72) := g_package||'validate_grade_range';
2950 l_pl_person_rate_id Number := null;
2951 l_opt1_person_rate_id Number := null;
2952 l_opt2_person_rate_id Number := null;
2953 l_opt3_person_rate_id Number := null;
2954 l_opt4_person_rate_id Number := null;
2955 l_group_per_in_ler_id BEN_CWB_PERSON_RATES.GROUP_PER_IN_LER_ID%Type;
2956 l_pl_ws_val Number := 0;
2957 l_opt1_ws_val Number := 0;
2958 l_opt2_ws_val Number := 0;
2959 l_opt3_ws_val Number := 0;
2960 l_opt4_ws_val Number := 0;
2961 l_new_salary Number := null;
2962 l_base_salary Number := null;
2963 l_grd_min Number := null;
2964 l_grd_max Number := null;
2965 l_return_msg varchar2(500) := null;
2966
2967 BEGIN
2968 If g_debug then
2969 hr_utility.set_location('Entering '||l_proc,10);
2970 End if;
2971 IF (P_PL_PERSON_RATE_ID IS NULL
2972 AND P_P_OPT1_PERSON_RATE_ID IS NULL
2973 AND P_P_OPT2_PERSON_RATE_ID IS NULL
2974 AND P_P_OPT3_PERSON_RATE_ID IS NULL
2975 AND P_P_OPT4_PERSON_RATE_ID IS NULL) THEN
2976
2977 If g_debug then
2978 hr_utility.set_location('No rates found '||l_proc,11);
2979 End if;
2980 END IF;
2981 IF (P_PL_PERSON_RATE_ID IS NOT NULL) THEN
2982 l_PL_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_PL_PERSON_RATE_ID);
2983 If g_debug then
2984 hr_utility.set_location('l_PL_PERSON_RATE_ID :'||l_PL_PERSON_RATE_ID,20);
2985 End if;
2986 END IF;
2987
2988 IF (P_P_OPT1_PERSON_RATE_ID IS NOT NULL) THEN
2989 l_OPT1_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT1_PERSON_RATE_ID);
2990 If g_debug then
2991 hr_utility.set_location('l_OPT1_PERSON_RATE_ID :'||l_OPT1_PERSON_RATE_ID,30);
2992 End if;
2993 END IF;
2994
2995 IF (P_P_OPT2_PERSON_RATE_ID IS NOT NULL) THEN
2996 l_OPT2_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT2_PERSON_RATE_ID);
2997 If g_debug then
2998 hr_utility.set_location('l_OPT2_PERSON_RATE_ID :'||l_OPT2_PERSON_RATE_ID,40);
2999 End if;
3000 END IF;
3001
3002 IF (P_P_OPT3_PERSON_RATE_ID IS NOT NULL) THEN
3003 l_OPT3_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT3_PERSON_RATE_ID);
3004 If g_debug then
3005 hr_utility.set_location('l_OPT3_PERSON_RATE_ID :'||l_OPT3_PERSON_RATE_ID,50);
3006 End if;
3007 END IF;
3008
3009 IF (P_P_OPT4_PERSON_RATE_ID IS NOT NULL) THEN
3010 l_OPT4_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT4_PERSON_RATE_ID);
3011 If g_debug then
3012 hr_utility.set_location('l_OPT4_PERSON_RATE_ID :'||l_OPT4_PERSON_RATE_ID,60);
3013 End if;
3014 END IF;
3015
3016 l_group_per_in_ler_id := get_group_per_in_ler_id(l_PL_PERSON_RATE_ID,
3017 l_OPT1_PERSON_RATE_ID,
3018 l_OPT2_PERSON_RATE_ID,
3019 l_OPT3_PERSON_RATE_ID,
3020 l_OPT4_PERSON_RATE_ID);
3021 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,70);
3022
3023 for l_plan_validation in c_plan_validation(l_group_per_in_ler_id) loop
3024 for l_grade_range in c_grade_range(l_group_per_in_ler_id) loop
3025 l_base_salary := l_grade_range.base_salary;
3026 l_grd_min := l_grade_range.grd_min;
3027 l_grd_max := l_grade_range.grd_max;
3028 l_new_salary := 0;
3029 if((l_grd_min is not null) and (l_grd_max is not null)) then
3030 for l_option_salary in c_option_salary(l_OPT1_PERSON_RATE_ID,P_OPT1_WS_VAL) loop
3031 l_opt1_ws_val := l_option_salary.ws_val;
3032 end loop;
3033 for l_option_salary in c_option_salary(l_OPT2_PERSON_RATE_ID,P_OPT2_WS_VAL) loop
3034 l_opt2_ws_val := l_option_salary.ws_val;
3035 end loop;
3036 for l_option_salary in c_option_salary(l_OPT3_PERSON_RATE_ID,P_OPT3_WS_VAL) loop
3037 l_opt3_ws_val := l_option_salary.ws_val;
3038 end loop;
3039 for l_option_salary in c_option_salary(l_OPT4_PERSON_RATE_ID,P_OPT4_WS_VAL) loop
3040 l_opt4_ws_val := l_option_salary.ws_val;
3041 end loop;
3042 l_new_salary := l_base_salary + l_opt1_ws_val + l_opt2_ws_val + l_opt3_ws_val + l_opt4_ws_val;
3043 If g_debug then
3044 hr_utility.set_location('l_new_salary :'||l_new_salary,80);
3048 if((l_new_salary < l_grd_min) or (l_new_salary > l_grd_max) )then
3045 hr_utility.set_location('l_grd_min :'||l_grd_min,80);
3046 hr_utility.set_location('l_grd_max :'||l_grd_max,80);
3047 End if;
3049 If g_debug then
3050 hr_utility.set_location('Leaving'||l_proc,90);
3051 End if;
3052 -- l_return_msg := fnd_message.get_string('BEN','');
3056
3053 l_return_msg := l_plan_validation.grade_range_validation;
3054 return l_return_msg;
3055 end if;
3057 for l_plan_salary in c_plan_salary(l_group_per_in_ler_id,P_PL_WS_VAL) loop
3058 l_new_salary := l_base_salary + l_plan_salary.ws_val;
3059 if((l_new_salary < l_grd_min) or (l_new_salary > l_grd_max) )then
3060 If g_debug then
3061 hr_utility.set_location('Leaving'||l_proc,100);
3062 End if;
3063 -- l_return_msg := fnd_message.get_string('BEN','');
3064 l_return_msg := l_plan_validation.grade_range_validation;
3065 return l_return_msg;
3066 end if;
3067 end loop;
3068 end if;
3069 end loop;
3070 end loop;
3071 If g_debug then
3072 hr_utility.set_location('Leaving'||l_proc,110);
3073 End if;
3074 return null;
3075
3076 END;
3077 --
3078 --
3079
3080 --
3081 --------------------------get_new_salary----------------------
3082 --
3083 FUNCTION get_new_salary(
3084 P_PL_PERSON_RATE_ID IN VARCHAR2 Default Null
3085 ,P_P_OPT1_PERSON_RATE_ID IN VARCHAR2 Default Null
3086 ,P_P_OPT2_PERSON_RATE_ID IN VARCHAR2 Default Null
3087 ,P_P_OPT3_PERSON_RATE_ID IN VARCHAR2 Default Null
3088 ,P_P_OPT4_PERSON_RATE_ID IN VARCHAR2 Default Null
3089 ,P_PL_WS_VAL IN VARCHAR2 Default Null
3090 ,P_OPT1_WS_VAL IN VARCHAR2 Default Null
3091 ,P_OPT2_WS_VAL IN VARCHAR2 Default Null
3092 ,P_OPT3_WS_VAL IN VARCHAR2 Default Null
3093 ,P_OPT4_WS_VAL IN VARCHAR2 Default Null
3094 )
3095 RETURN NUMBER
3096 IS
3097
3098 cursor c_plan_salary ( l_group_per_in_ler_id number,l_ws_val number) is
3099 select
3100 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
3101 decode(l_ws_val,'-0.0000000000000001',plRt.ws_val,l_ws_val) ws_val,
3102 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
3103 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
3104 from
3105 ben_cwb_pl_dsgn pl
3106 ,ben_cwb_person_rates plRt
3107 ,ben_cwb_person_info per
3108 where plRt.group_per_in_ler_id = l_group_per_in_ler_id
3109 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
3110 and plRt.pl_id = pl.pl_id
3111 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3112 and plRt.oipl_id = pl.oipl_id
3113 and pl.oipl_id = -1
3114 and pl.ws_sub_acty_typ_cd = 'ICM7';
3115
3116 cursor c_grade_range( l_group_per_in_ler_id number) is
3117 select
3118 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
3119 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
3120 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
3121 from
3122 ben_cwb_pl_dsgn pl
3123 ,ben_cwb_person_rates plRt
3124 ,ben_cwb_person_info per
3125 where plRt.group_per_in_ler_id = l_group_per_in_ler_id
3126 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
3127 and plRt.pl_id = pl.pl_id
3128 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3129 and plRt.oipl_id = pl.oipl_id
3130 and pl.oipl_id = -1;
3131
3132 cursor c_option_salary ( l_rate_id number, l_ws_val number) is
3133 select
3134 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
3135 decode(l_ws_val,'-0.0000000000000001',optRt.ws_val,l_ws_val) ws_val,
3136 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
3137 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max,
3138 per.group_per_in_ler_id
3139 from
3140 ben_cwb_pl_dsgn pl
3141 ,ben_cwb_person_rates optRt
3142 ,ben_cwb_person_info per
3143 where optRt.person_rate_id = l_rate_id
3144 and optRt.group_per_in_ler_id = per.group_per_in_ler_id
3145 and optRt.pl_id = pl.pl_id
3146 and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3147 and optRt.oipl_id = pl.oipl_id
3148 and pl.oipl_id <> -1
3149 and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
3150
3151 l_proc varchar2(72) := g_package||'get_new_salary';
3152 l_pl_person_rate_id Number := null;
3153 l_opt1_person_rate_id Number := null;
3154 l_opt2_person_rate_id Number := null;
3155 l_opt3_person_rate_id Number := null;
3156 l_opt4_person_rate_id Number := null;
3157 l_group_per_in_ler_id BEN_CWB_PERSON_RATES.GROUP_PER_IN_LER_ID%Type;
3158 l_pl_ws_val Number := 0;
3159 l_opt1_ws_val Number := 0;
3160 l_opt2_ws_val Number := 0;
3161 l_opt3_ws_val Number := 0;
3162 l_opt4_ws_val Number := 0;
3163 l_new_salary Number := null;
3164 l_base_salary Number := null;
3165 l_grd_min Number := null;
3166 l_grd_max Number := null;
3167
3168 BEGIN
3169 If g_debug then
3170 hr_utility.set_location('Entering '||l_proc,10);
3171 End if;
3172 IF (P_PL_PERSON_RATE_ID IS NULL
3173 AND P_P_OPT1_PERSON_RATE_ID IS NULL
3174 AND P_P_OPT2_PERSON_RATE_ID IS NULL
3175 AND P_P_OPT3_PERSON_RATE_ID IS NULL
3176 AND P_P_OPT4_PERSON_RATE_ID IS NULL) THEN
3177
3178 If g_debug then
3179 hr_utility.set_location('No rates found '||l_proc,11);
3180 End if;
3181 END IF;
3182 IF (P_PL_PERSON_RATE_ID IS NOT NULL) THEN
3183 l_PL_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_PL_PERSON_RATE_ID);
3184 If g_debug then
3185 hr_utility.set_location('l_PL_PERSON_RATE_ID :'||l_PL_PERSON_RATE_ID,20);
3186 End if;
3187 END IF;
3188
3189 IF (P_P_OPT1_PERSON_RATE_ID IS NOT NULL) THEN
3190 l_OPT1_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT1_PERSON_RATE_ID);
3191 If g_debug then
3195
3192 hr_utility.set_location('l_OPT1_PERSON_RATE_ID :'||l_OPT1_PERSON_RATE_ID,30);
3193 End if;
3194 END IF;
3196 IF (P_P_OPT2_PERSON_RATE_ID IS NOT NULL) THEN
3197 l_OPT2_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT2_PERSON_RATE_ID);
3198 If g_debug then
3199 hr_utility.set_location('l_OPT2_PERSON_RATE_ID :'||l_OPT2_PERSON_RATE_ID,40);
3200 End if;
3201 END IF;
3202
3203 IF (P_P_OPT3_PERSON_RATE_ID IS NOT NULL) THEN
3204 l_OPT3_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT3_PERSON_RATE_ID);
3205 If g_debug then
3206 hr_utility.set_location('l_OPT3_PERSON_RATE_ID :'||l_OPT3_PERSON_RATE_ID,50);
3207 End if;
3208 END IF;
3209
3210 IF (P_P_OPT4_PERSON_RATE_ID IS NOT NULL) THEN
3211 l_OPT4_PERSON_RATE_ID := BEN_CWB_WEBADI_UTILS.decrypt(P_P_OPT4_PERSON_RATE_ID);
3212 If g_debug then
3213 hr_utility.set_location('l_OPT4_PERSON_RATE_ID :'||l_OPT4_PERSON_RATE_ID,60);
3214 End if;
3215 END IF;
3216
3217 l_group_per_in_ler_id := get_group_per_in_ler_id(l_PL_PERSON_RATE_ID,
3218 l_OPT1_PERSON_RATE_ID,
3219 l_OPT2_PERSON_RATE_ID,
3220 l_OPT3_PERSON_RATE_ID,
3221 l_OPT4_PERSON_RATE_ID);
3222 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,70);
3223
3224 for l_grade_range in c_grade_range(l_group_per_in_ler_id) loop
3225 l_base_salary := l_grade_range.base_salary;
3226 l_grd_min := l_grade_range.grd_min;
3227 l_grd_max := l_grade_range.grd_max;
3228 l_new_salary := 0;
3229 for l_option_salary in c_option_salary(l_OPT1_PERSON_RATE_ID,P_OPT1_WS_VAL) loop
3230 l_opt1_ws_val := l_option_salary.ws_val;
3231 end loop;
3232 for l_option_salary in c_option_salary(l_OPT2_PERSON_RATE_ID,P_OPT2_WS_VAL) loop
3233 l_opt2_ws_val := l_option_salary.ws_val;
3234 end loop;
3235 for l_option_salary in c_option_salary(l_OPT3_PERSON_RATE_ID,P_OPT3_WS_VAL) loop
3236 l_opt3_ws_val := l_option_salary.ws_val;
3237 end loop;
3238 for l_option_salary in c_option_salary(l_OPT4_PERSON_RATE_ID,P_OPT4_WS_VAL) loop
3239 l_opt4_ws_val := l_option_salary.ws_val;
3240 end loop;
3241 l_new_salary := l_base_salary + l_opt1_ws_val + l_opt2_ws_val + l_opt3_ws_val + l_opt4_ws_val;
3242 If g_debug then
3243 hr_utility.set_location('1..l_new_salary :'||l_new_salary,80);
3244 End if;
3245
3246 for l_plan_salary in c_plan_salary(l_group_per_in_ler_id,P_PL_WS_VAL) loop
3247 l_new_salary := l_base_salary + l_plan_salary.ws_val;
3248 end loop;
3249 If g_debug then
3250 hr_utility.set_location('2..l_new_salary :'||l_new_salary,90);
3251 End if;
3252 end loop;
3253
3254 If g_debug then
3255 hr_utility.set_location(' return l_new_salary :'||l_new_salary,90);
3256 hr_utility.set_location('Leaving'||l_proc,110);
3257 End if;
3258 return l_new_salary;
3259 END;
3260
3261
3262
3263 FUNCTION get_new_salary(
3264 p_group_per_in_ler_id IN number Default Null
3265 )
3266 RETURN NUMBER
3267 IS
3268
3269 cursor c_plan_salary is
3270 select
3271 nvl(plRt.ws_val,0) ws_val
3272 from
3273 ben_cwb_pl_dsgn pl
3274 ,ben_cwb_person_rates plRt
3275 ,ben_cwb_person_info per
3276 where plRt.group_per_in_ler_id = p_group_per_in_ler_id
3277 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
3278 and plRt.pl_id = pl.pl_id
3279 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3280 and plRt.oipl_id = pl.oipl_id
3281 and pl.oipl_id = -1
3282 and pl.ws_sub_acty_typ_cd = 'ICM7';
3283
3284 cursor c_grade_range is
3285 select
3286 (per.base_salary*per.pay_annulization_factor/pl.pl_annulization_factor) base_salary,
3287 (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
3288 (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
3289 from
3290 ben_cwb_pl_dsgn pl
3291 ,ben_cwb_person_rates plRt
3292 ,ben_cwb_person_info per
3293 where plRt.group_per_in_ler_id = p_group_per_in_ler_id
3294 and plRt.group_per_in_ler_id = per.group_per_in_ler_id
3295 and plRt.pl_id = pl.pl_id
3296 and plRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3297 and plRt.oipl_id = pl.oipl_id
3298 and pl.oipl_id = -1;
3299
3300 cursor c_option_salary ( l_oipl_ordr_num number) is
3301 select
3302 nvl(optRt.ws_val,0) ws_val
3303 from
3304 ben_cwb_pl_dsgn pl
3305 ,ben_cwb_person_rates optRt
3306 ,ben_cwb_person_info per
3307 where optRt.group_per_in_ler_id = p_group_per_in_ler_id
3308 and optRt.group_per_in_ler_id = per.group_per_in_ler_id
3309 and optRt.pl_id = pl.pl_id
3310 and optRt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3311 and optRt.oipl_id = pl.oipl_id
3312 and pl.oipl_id <> -1
3313 and pl.oipl_ordr_num = l_oipl_ordr_num
3314 and pl.ws_sub_acty_typ_cd in ('ICM7','ICM11');
3315
3316 l_proc varchar2(72) := g_package||'get_new_salary';
3317 l_pl_ws_val Number := 0;
3318 l_opt1_ws_val Number := 0;
3319 l_opt2_ws_val Number := 0;
3320 l_opt3_ws_val Number := 0;
3321 l_opt4_ws_val Number := 0;
3322 l_new_salary Number := null;
3323 l_base_salary Number := null;
3324
3325 BEGIN
3326 If g_debug then
3327 hr_utility.set_location('Entering '||l_proc,10);
3328 End if;
3329
3330 hr_utility.set_location('p_group_per_in_ler_id :'||p_group_per_in_ler_id,20);
3331
3332 for l_grade_range in c_grade_range loop
3333 l_base_salary := l_grade_range.base_salary;
3334 l_new_salary := 0;
3335 for l_option_salary in c_option_salary(1) loop
3336 l_opt1_ws_val := l_option_salary.ws_val;
3337 end loop;
3341 for l_option_salary in c_option_salary(3) loop
3338 for l_option_salary in c_option_salary(2) loop
3339 l_opt2_ws_val := l_option_salary.ws_val;
3340 end loop;
3342 l_opt3_ws_val := l_option_salary.ws_val;
3343 end loop;
3344 for l_option_salary in c_option_salary(4) loop
3345 l_opt4_ws_val := l_option_salary.ws_val;
3346 end loop;
3347 l_new_salary := l_base_salary + l_opt1_ws_val + l_opt2_ws_val + l_opt3_ws_val + l_opt4_ws_val;
3348 If g_debug then
3349 hr_utility.set_location('1..l_new_salary :'||l_new_salary,30);
3350 End if;
3351
3352 for l_plan_salary in c_plan_salary loop
3353 l_new_salary := l_base_salary + l_plan_salary.ws_val;
3354 end loop;
3355 If g_debug then
3356 hr_utility.set_location('2..l_new_salary :'||l_new_salary,40);
3357 End if;
3358 end loop;
3359
3360 If g_debug then
3361 hr_utility.set_location(' return l_new_salary :'||l_new_salary,50);
3362 hr_utility.set_location('Leaving'||l_proc,60);
3363 End if;
3364 if(l_new_salary = 0) then
3365 l_new_salary := null;
3366 end if;
3367 return l_new_salary;
3368 END;
3369
3370
3371 procedure populate_proposed_grade_rec(
3372 p_group_per_in_ler_id IN number
3373 ,p_asg_updt_eff_date IN varchar2
3374 ) IS
3375
3376 CURSOR c_proposed_grade is
3377 select per.group_per_in_ler_id
3378 ,nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.minimum)
3379 || ' - ' || nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.maximum) proposed_grade_range
3380 ,nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.mid_value) proposed_grade_mid
3381 ,get_new_salary(p_group_per_in_ler_id) * 100 / (nvl(per.grade_annulization_factor/pl.pl_annulization_factor,1)*fnd_number.canonical_to_number(pgr.mid_value)) proposed_grade_comparatio
3382 from ben_cwb_pl_dsgn pl
3383 ,pay_grade_rules_f pgr
3384 ,ben_transaction atxn
3385 ,ben_cwb_person_info per
3386 WHERE per.group_per_in_ler_id = p_group_per_in_ler_id
3387 and atxn.transaction_id = per.assignment_id
3388 AND atxn.transaction_type = 'CWBASG' || p_asg_updt_eff_date
3389 AND TO_NUMBER (atxn.attribute7) = pgr.grade_or_spinal_point_id
3390 and pgr.rate_id = per.pay_rate_id
3391 and per.group_pl_id = pl.pl_id
3392 and per.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3393 and pl.group_oipl_id = -1
3394 and per.lf_evt_ocrd_dt between pgr.effective_start_date and pgr.effective_end_date;
3395
3396 BEGIN
3397 g_proposed_grade_rec := null;
3398 open c_proposed_grade;
3399 fetch c_proposed_grade into g_proposed_grade_rec;
3400 close c_proposed_grade;
3401 END populate_proposed_grade_rec;
3402
3403 function get_proposed_grade_range(
3404 p_group_per_in_ler_id IN number
3405 ,p_asg_updt_eff_date IN varchar2
3406 ) return varchar2 IS
3407 begin
3408 populate_proposed_grade_rec(p_group_per_in_ler_id,p_asg_updt_eff_date);
3409 return g_proposed_grade_rec.proposed_grade_range;
3410 end get_proposed_grade_range;
3411
3412 function get_proposed_grade_mid(
3413 p_group_per_in_ler_id IN number
3414 ,p_asg_updt_eff_date IN varchar2
3415 ) return number IS
3416 begin
3417 populate_proposed_grade_rec(p_group_per_in_ler_id,p_asg_updt_eff_date);
3418 return g_proposed_grade_rec.proposed_grade_mid;
3419 end get_proposed_grade_mid;
3420
3421 FUNCTION get_proposed_grade_comparatio(
3422 p_group_per_in_ler_id IN number
3423 ,p_asg_updt_eff_date IN varchar2
3424 )
3425 RETURN number IS
3426 begin
3427 populate_proposed_grade_rec(p_group_per_in_ler_id,p_asg_updt_eff_date);
3428 return g_proposed_grade_rec.proposed_grade_comparatio;
3429 end get_proposed_grade_comparatio;
3430
3431 --
3432 --------------------------- get_group_per_in_ler_id -----------------------------
3433 --
3434
3435 FUNCTION get_group_per_in_ler_id (P_PERSON_RATE_ID IN NUMBER Default Null
3436 ,P_OPT1_PERSON_RATE_ID IN NUMBER Default Null
3437 ,P_OPT2_PERSON_RATE_ID IN NUMBER Default Null
3438 ,P_OPT3_PERSON_RATE_ID IN NUMBER Default Null
3439 ,P_OPT4_PERSON_RATE_ID IN NUMBER Default Null)
3440 Return Number
3441 IS
3442 Cursor csr_group_per_in_ler_id (l_person_rate_id IN Number)
3443 IS
3444 Select group_per_in_ler_id
3445 from ben_cwb_person_rates
3446 where person_rate_id = l_person_rate_id;
3447
3448 l_proc Varchar2(72) := g_package||'get_group_per_in_ler_id';
3449 l_rate_id Number;
3450 l_group_per_in_ler_id Number;
3451
3452
3453 BEGIN
3454
3455 hr_utility.set_location('Entering :'||l_proc,10);
3456
3457 If P_PERSON_RATE_ID IS NOT NULL then
3458 l_rate_id := P_PERSON_RATE_ID;
3459 Elsif P_OPT1_PERSON_RATE_ID IS NOT NULL then
3460 l_rate_id := P_OPT1_PERSON_RATE_ID;
3461 Elsif P_OPT2_PERSON_RATE_ID IS NOT NULL then
3462 l_rate_id := P_OPT2_PERSON_RATE_ID;
3463 Elsif P_OPT3_PERSON_RATE_ID IS NOT NULL then
3464 l_rate_id := P_OPT3_PERSON_RATE_ID;
3465 Elsif P_OPT4_PERSON_RATE_ID IS NOT NULL then
3466 l_rate_id := P_OPT4_PERSON_RATE_ID;
3467 End if;
3468
3469 hr_utility.set_location('l_rate_id :'||l_rate_id,20);
3470
3471 Open csr_group_per_in_ler_id(l_rate_id);
3472 Fetch csr_group_per_in_ler_id into l_group_per_in_ler_id;
3473 Close csr_group_per_in_ler_id;
3474
3475 hr_utility.set_location('l_group_per_in_ler_id :'||l_group_per_in_ler_id,40);
3476 hr_utility.set_location('Leaving :'||l_proc,100);
3477
3478 return l_group_per_in_ler_id;
3479
3483 ---------------Utility functions for number conversions-------------
3480 End get_group_per_in_ler_id;
3481
3482 --
3484 --
3485 FUNCTION bin2int (bin VARCHAR2)
3486 RETURN PLS_INTEGER IS
3487 len PLS_INTEGER := LENGTH(bin);
3488 BEGIN
3489 IF NVL(len,1) = 1 THEN
3490 RETURN bin;
3491 ELSE RETURN
3492 2 * bin2int(SUBSTR(bin,1,len-1)) + SUBSTR(bin,-1);
3493 END IF;
3494 END bin2int;
3495
3496 FUNCTION int2bin(int PLS_INTEGER)
3497 RETURN VARCHAR2 IS
3498 BEGIN
3499 hr_utility.set_location('int2bin:'||int, 300);
3500 IF int > 0 THEN
3501 RETURN int2bin(TRUNC(int/2))||
3502 SUBSTR('01',MOD(int,2)+1,1);
3503 ELSE
3504 RETURN NULL;
3505 END IF;
3506 END int2bin;
3507
3508 FUNCTION hex2int(hex VARCHAR2)
3509 RETURN PLS_INTEGER IS
3510 len PLS_INTEGER := LENGTH(hex);
3511 BEGIN
3512 hr_utility.set_location('hex2int:'||hex, 300);
3513 IF NVL(len,1) = 1 THEN
3514 RETURN INSTR('0123456789ABCDEF',hex) - 1;
3515 ELSE
3516 hr_utility.set_location('hex2int length:'||len, 300);
3517 RETURN 16 * hex2int(SUBSTR(hex,1,len-1)) +
3518 INSTR('0123456789ABCDEF',SUBSTR(hex,-1)) - 1;
3519 END IF;
3520 END hex2int;
3521
3522 FUNCTION int2hex(n PLS_INTEGER)
3523 RETURN VARCHAR2 IS
3524 BEGIN
3525 IF n > 0 THEN
3526 RETURN int2hex(TRUNC(n/16))||
3527 SUBSTR('0123456789ABCDEF',MOD(n,16)+1,1);
3528 ELSE
3529 RETURN NULL;
3530 END IF;
3531 END int2hex;
3532
3533 FUNCTION int2base(int PLS_INTEGER,base PLS_INTEGER)
3534 RETURN VARCHAR2 IS
3535 BEGIN
3536 IF int > 0 THEN
3537 RETURN int2base(TRUNC(int/base),base)||
3538 SUBSTR('0123456789ABCDEF',MOD(int,base)+1,1);
3539 ELSE
3540 RETURN NULL;
3541 END IF;
3542 END int2base;
3543
3544 FUNCTION base2int(num VARCHAR2,base PLS_INTEGER)
3545 RETURN PLS_INTEGER IS
3546 len PLS_INTEGER := LENGTH(num);
3547 BEGIN
3548 IF NVL(len,1) = 1 THEN
3549 RETURN INSTR('0123456789ABCDEF',num) - 1;
3550 ELSE
3551 RETURN base * base2int(SUBSTR(num,1,len-1),base) +
3552 INSTR('0123456789ABCDEF',SUBSTR(num,-1)) - 1;
3553 END IF;
3554 END base2int;
3555 --
3556 --
3557 END ben_cwb_webadi_utils;
3558