[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_WEBADI_UTILS
Source
1 Package Body BEN_CWB_WEBADI_UTILS as
2 /* $Header: bencwbadiutl.pkb 120.10.12010000.2 2008/08/05 14:36:48 ubhat ship $ */
3
4 --key_string VARCHAR2(32) := 'A!190j2#Az19?j1@A!190j2#Az19?j1@';
5
6 l_layout_lock_time VARCHAR2(5) := FND_PROFILE.VALUE('BEN_CWB_LAYOUT_LOCK_TIME');
7 key_string VARCHAR2(16) := substr(FND_PROFILE.VALUE('BEN_CWB_ENCRYPT_KEY'),1,16);
8
9 g_package Varchar2(30) := 'BEN_CWB_WEBADI_UTILS.';
10 g_debug boolean := hr_utility.debug_enabled;
11
12 --
13 --------------------------create_cwb_layout_row----------------------
14 --
15
16 PROCEDURE create_cwb_layout_row(
17 p_layout_code IN VARCHAR2
18 ,p_user_name IN VARCHAR2
19 ,p_base_layout_code IN VARCHAR2)
20 IS
21
22 l_rowid VARCHAR2(200);
23 no_default_layout EXCEPTION;
24 l_proc varchar2(72) := g_package||'create_cwb_layout_row';
25
26
27 CURSOR c_layout_row
28 IS
29 SELECT application_id
30 ,object_version_number
31 ,stylesheet_app_id
32 ,stylesheet_code
33 ,integrator_app_id
34 ,integrator_code
35 ,style
36 ,style_class
37 ,reporting_flag
38 ,reporting_interface_app_id
39 ,reporting_interface_code
40 ,created_by
41 ,last_updated_by
42 ,last_update_login
43 FROM bne_layouts_b
44 WHERE application_id = 800
45 AND layout_code = p_base_layout_code;
46
47 l_layout_row c_layout_row%ROWTYPE;
48
49
50 BEGIN
51
52 OPEN c_layout_row;
53 FETCH c_layout_row INTO l_layout_row;
54 IF c_layout_row%NOTFOUND THEN
55 RAISE no_default_layout;
56 END IF;
57 CLOSE c_layout_row;
58
59 bne_layouts_pkg.insert_row
60 (x_rowid => l_rowid
61 ,x_application_id => l_layout_row.application_id
62 ,x_layout_code => p_layout_code
63 ,x_object_version_number => 1
64 ,x_stylesheet_app_id => l_layout_row.stylesheet_app_id
65 ,x_stylesheet_code => l_layout_row.stylesheet_code
66 ,x_integrator_app_id => l_layout_row.integrator_app_id
67 ,x_integrator_code => l_layout_row.integrator_code
68 ,x_style => l_layout_row.style
69 ,x_style_class => l_layout_row.style_class
70 ,x_reporting_flag => l_layout_row.reporting_flag
71 ,x_reporting_interface_app_id => l_layout_row.reporting_interface_app_id
72 ,x_reporting_interface_code => l_layout_row.reporting_interface_code
73 ,x_user_name => p_user_name
74 ,x_creation_date => SYSDATE
75 ,x_created_by => l_layout_row.created_by
76 ,x_last_update_date => SYSDATE
77 ,x_last_updated_by => l_layout_row.last_updated_by
78 ,x_last_update_login => l_layout_row.last_update_login);
79 END create_cwb_layout_row;
80
81 --
82 --------------------------create_cwb_layout_blocks_row----------------------
83 --
84
85 PROCEDURE create_cwb_layout_blocks_row(
86 p_layout_code IN VARCHAR2
87 ,p_user_name IN VARCHAR2
88 ,p_base_layout_code IN VARCHAR2)
89 IS
90
91 l_rowid VARCHAR2(200);
92 CURSOR c_layout_blocks_row IS
93 SELECT application_id
94 ,block_id
95 ,parent_id
96 ,layout_element
97 ,style_class
98 ,style
99 ,row_style_class
100 ,row_style
101 ,col_style_class
102 ,col_style
103 ,prompt_displayed_flag
104 ,prompt_style_class
105 ,prompt_style
106 ,hint_displayed_flag
107 ,hint_style_class
108 ,hint_style
109 ,orientation
110 ,layout_control
111 ,display_flag
112 ,BLOCKSIZE
113 ,minsize
114 ,MAXSIZE
115 ,sequence_num
116 ,prompt_colspan
117 ,hint_colspan
118 ,row_colspan
119 ,summary_style_class
120 ,summary_style
121 ,created_by
122 ,last_updated_by
123 ,last_update_login
124 FROM bne_layout_blocks_b
125 WHERE application_id = 800
126 AND layout_code = p_base_layout_code
127 ORDER BY block_id;
128
129 l_layout_blocks_row c_layout_blocks_row%ROWTYPE;
130 BEGIN
131
132 OPEN c_layout_blocks_row;
133 LOOP
134 FETCH c_layout_blocks_row INTO l_layout_blocks_row;
135 EXIT WHEN c_layout_blocks_row%NOTFOUND;
136 bne_layout_blocks_pkg.insert_row
137 (x_rowid => l_rowid
138 ,x_application_id => l_layout_blocks_row.application_id
139 ,x_layout_code => p_layout_code
140 ,x_block_id => l_layout_blocks_row.block_id
141 ,x_object_version_number => 1
142 ,x_parent_id => l_layout_blocks_row.parent_id
143 ,x_layout_element => l_layout_blocks_row.layout_element
144 ,x_style_class => l_layout_blocks_row.style_class
145 ,x_style => l_layout_blocks_row.style
146 ,x_row_style_class => l_layout_blocks_row.row_style_class
147 ,x_row_style => l_layout_blocks_row.row_style
148 ,x_col_style_class => l_layout_blocks_row.col_style_class
149 ,x_col_style => l_layout_blocks_row.col_style
150 ,x_prompt_displayed_flag => l_layout_blocks_row.prompt_displayed_flag
151 ,x_prompt_style_class => l_layout_blocks_row.prompt_style_class
152 ,x_prompt_style => l_layout_blocks_row.prompt_style
153 ,x_hint_displayed_flag => l_layout_blocks_row.hint_displayed_flag
154 ,x_hint_style_class => l_layout_blocks_row.hint_style_class
155 ,x_hint_style => l_layout_blocks_row.hint_style
156 ,x_orientation => l_layout_blocks_row.orientation
157 ,x_layout_control => l_layout_blocks_row.layout_control
158 ,x_display_flag => l_layout_blocks_row.display_flag
159 ,x_blocksize => l_layout_blocks_row.BLOCKSIZE
160 ,x_minsize => l_layout_blocks_row.minsize
161 ,x_maxsize => l_layout_blocks_row.MAXSIZE
162 ,x_sequence_num => l_layout_blocks_row.sequence_num
163 ,x_prompt_colspan => l_layout_blocks_row.prompt_colspan
164 ,x_hint_colspan => l_layout_blocks_row.hint_colspan
165 ,x_row_colspan => l_layout_blocks_row.row_colspan
166 ,x_summary_style_class => l_layout_blocks_row.summary_style_class
167 ,x_summary_style => l_layout_blocks_row.summary_style
168 ,x_user_name => p_user_name
169 ,x_creation_date => SYSDATE
170 ,x_created_by => l_layout_blocks_row.created_by
171 ,x_last_update_date => SYSDATE
172 ,x_last_updated_by => l_layout_blocks_row.last_updated_by
173 ,x_last_update_login => l_layout_blocks_row.last_update_login);
174 END LOOP;
175
176 CLOSE c_layout_blocks_row;
177 END create_cwb_layout_blocks_row;
178
179 --
180 --------------------------create_cwb_layout_cols_row----------------------
181 --
182
183 PROCEDURE create_cwb_layout_cols_row(p_layout_code IN VARCHAR2
184 ,p_base_layout_code IN VARCHAR2) IS
185 l_rowid VARCHAR2(200);
186
187 CURSOR c_layout_cols_row IS
188 SELECT application_id
189 ,layout_code
190 ,block_id
191 ,interface_app_id
192 ,interface_code
193 ,interface_seq_num
194 ,sequence_num
195 ,style
196 ,style_class
197 ,hint_style
198 ,hint_style_class
199 ,prompt_style
200 ,prompt_style_class
201 ,default_type
202 ,DEFAULT_VALUE
203 ,created_by
204 ,last_updated_by
205 ,last_update_login
206 ,read_only_flag
207 FROM bne_layout_cols
208 WHERE application_id = 800
209 AND layout_code = p_base_layout_code
210 ORDER BY block_id;
211
212 l_layout_cols_row c_layout_cols_row%ROWTYPE;
213
214 l_read_only_flag VARCHAR2(1) := NULL;
215 l_display_width NUMBER := NULL;
216
217 BEGIN
218 OPEN c_layout_cols_row;
219 LOOP
220 FETCH c_layout_cols_row INTO l_layout_cols_row;
221
222 EXIT WHEN c_layout_cols_row%NOTFOUND;
223
224 IF (p_base_layout_code = 'BEN_CWB_WRK_SHT_BASE_LYT') THEN
225 l_read_only_flag := 'Y';
226 --Hide the contextual security keys
227 IF (l_layout_cols_row.interface_seq_num IN (158, 189, 194, 195, 196, 198)) then
228 l_display_width := 0;
229 --Hide the security keys in lines
230 ELSIF (l_layout_cols_row.interface_seq_num IN (130, 131, 132, 133, 134)) then
231 l_display_width := 0;
232 l_read_only_flag := 'N';
233 --Make non-updateable columns as read-only
234 ELSIF (l_layout_cols_row.interface_seq_num IN (3,12,30,48,66,84,151) OR
235 l_layout_cols_row.interface_seq_num BETWEEN 200 AND 229
236 --changed by KMG included the below condition
237 OR l_layout_cols_row.interface_seq_num BETWEEN 240 and 244) THEN
238 l_read_only_flag := 'N';
239 END IF;
240 IF(l_read_only_flag = 'N') THEN
241 l_read_only_flag := l_layout_cols_row.read_only_flag;
242 END IF;
243 END IF;
244
245
246 bne_layout_cols_pkg.insert_row
247 (x_rowid => l_rowid
248 ,x_application_id => l_layout_cols_row.application_id
249 ,x_layout_code => p_layout_code
250 ,x_block_id => l_layout_cols_row.block_id
251 ,x_sequence_num => l_layout_cols_row.sequence_num
252 ,x_object_version_number => 1
253 ,x_interface_app_id => l_layout_cols_row.interface_app_id
254 ,x_interface_code => l_layout_cols_row.interface_code
255 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
256 ,x_style_class => l_layout_cols_row.style_class
257 ,x_hint_style => l_layout_cols_row.hint_style
258 ,x_hint_style_class => l_layout_cols_row.hint_style_class
259 ,x_prompt_style => l_layout_cols_row.prompt_style
260 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
261 ,x_default_type => l_layout_cols_row.default_type
262 ,x_default_value => l_layout_cols_row.DEFAULT_VALUE
263 ,x_style => l_layout_cols_row.style
264 ,x_creation_date => SYSDATE
265 ,x_created_by => l_layout_cols_row.created_by
266 ,x_last_update_date => SYSDATE
267 ,x_last_updated_by => l_layout_cols_row.last_updated_by
268 ,x_last_update_login => l_layout_cols_row.last_update_login
269 ,x_read_only_flag => l_read_only_flag
270 ,x_display_width => l_display_width);
271 END LOOP;
272 CLOSE c_layout_cols_row;
273
274 END create_cwb_layout_cols_row;
275
276 --
277 --------------------------create_cwb_layout----------------------
278 --
279
280 PROCEDURE create_cwb_layout(
281 p_layout_code IN VARCHAR2
282 ,p_user_name IN VARCHAR2
283 ,p_base_layout_code IN VARCHAR2) IS
284 BEGIN
285 create_cwb_layout_row(p_layout_code => p_layout_code
286 ,p_user_name => p_user_name
287 ,p_base_layout_code => p_base_layout_code);
288
289 create_cwb_layout_blocks_row(p_layout_code => p_layout_code
290 ,p_user_name => p_user_name
291 ,p_base_layout_code => p_base_layout_code);
292
293 create_cwb_layout_cols_row(p_layout_code => p_layout_code
294 ,p_base_layout_code => p_base_layout_code);
295
296 END create_cwb_layout;
297
298 --
299 --------------------------delete_cwb_layout_cols----------------------
300 --
301
302 PROCEDURE delete_cwb_layout_cols(p_layout_code IN VARCHAR2
303 ,p_application_id IN NUMBER )
304 IS
305
306 l_proc Varchar2(72):= 'delete_cwb_layout_cols';
307 CURSOR c_layout_cols
308 IS
309 SELECT blc.application_id
310 ,blc.layout_code
311 ,blc.block_id
312 ,blc.sequence_num
313 FROM bne_layout_cols blc
314 WHERE blc.application_id = p_application_id
315 AND blc.layout_code = p_layout_code;
316
317 BEGIN
318 hr_utility.set_location('Entering '||l_proc,10);
319 For l_layout_col_rec In c_layout_cols
320 LOOP
321 hr_utility.set_location('Seq Num :'||l_layout_col_rec.sequence_num,25);
322 bne_layout_cols_pkg.delete_row
323 (x_application_id => l_layout_col_rec.application_id
324 ,x_layout_code => l_layout_col_rec.layout_code
325 ,x_block_id => l_layout_col_rec.block_id
326 ,x_sequence_num => l_layout_col_rec.sequence_num);
327 END LOOP;
328 hr_utility.set_location('Leaving '||l_proc,100);
329 EXCEPTION
330 WHEN OTHERS THEN
331 hr_utility.set_location('ERROR occured',30);
332 Null;
333 END delete_cwb_layout_cols;
334
335 --
336 ----------------------------check_hidden_worksheet_columns ------------------------
337 --
338 procedure check_hidden_worksheet_columns( p_group_pl_id IN NUMBER
339 ,p_lf_evt_ocrd_dt IN DATE
340 ,p_show_hide_data OUT NOCOPY p_show_hide_data
341 )
342 IS
343
344 l_show_hide_data ben_cwb_webadi_utils.p_show_hide_data;
345
346 cursor group_opt_exists
347 IS
348 select count(group_oipl_id) IdCount
349 ,max(ws_abr_id) ws_abr_id
350 ,max(elig_sal_abr_id) elig_sal_abr_id
351 ,max(ws_nnmntry_uom) ws_nnmntry_uom
352 ,null ws_sub_acty_typ_cd
353 from ben_cwb_pl_dsgn
354 where group_pl_id = p_group_pl_id
355 and group_pl_id = pl_id
356 and group_oipl_id = oipl_id
357 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
358 and group_oipl_id <> -1;
359
360 cursor actual_opt_exists
361 IS
362 select count(oipl_id) IdCount
363 ,max(ws_abr_id) ws_abr_id
364 ,max(elig_sal_abr_id) elig_sal_abr_id
365 ,max(ws_nnmntry_uom) ws_nnmntry_uom
366 ,null ws_sub_acty_typ_cd
367 from ben_cwb_pl_dsgn
368 where group_pl_id = p_group_pl_id
369 and group_pl_id = pl_id
370 and group_oipl_id <> oipl_id
371 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
372 and group_oipl_id <> -1;
373
374 cursor opt1_exists
375 IS
376 select count(group_oipl_id) IdCount
377 ,max(ws_abr_id) ws_abr_id
378 ,max(elig_sal_abr_id) elig_sal_abr_id
379 ,max(ws_nnmntry_uom) ws_nnmntry_uom
380 ,null ws_sub_acty_typ_cd
381 from ben_cwb_pl_dsgn
382 where group_pl_id = p_group_pl_id
383 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
384 and group_oipl_id <> -1
385 and oipl_ordr_num = 1;
386
387 cursor opt2_exists
388 IS
389 select count(group_oipl_id) IdCount
390 ,max(ws_abr_id) ws_abr_id
391 ,max(elig_sal_abr_id) elig_sal_abr_id
392 ,max(ws_nnmntry_uom) ws_nnmntry_uom
393 ,null ws_sub_acty_typ_cd
394 from ben_cwb_pl_dsgn
395 where group_pl_id = p_group_pl_id
396 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
397 and group_oipl_id <> -1
398 and oipl_ordr_num = 2;
399
400 cursor opt3_exists
401 IS
402 select count(group_oipl_id) IdCount
403 ,max(ws_abr_id) ws_abr_id
404 ,max(elig_sal_abr_id) elig_sal_abr_id
405 ,max(ws_nnmntry_uom) ws_nnmntry_uom
406 ,null ws_sub_acty_typ_cd
407 from ben_cwb_pl_dsgn
408 where group_pl_id = p_group_pl_id
409 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
410 and group_oipl_id <> -1
411 and oipl_ordr_num = 3;
412
413 cursor opt4_exists
414 IS
415 select count(group_oipl_id) IdCount
416 ,max(ws_abr_id) ws_abr_id
417 ,max(elig_sal_abr_id) elig_sal_abr_id
418 ,max(ws_nnmntry_uom) ws_nnmntry_uom
419 ,null ws_sub_acty_typ_cd
420 from ben_cwb_pl_dsgn
421 where group_pl_id = p_group_pl_id
422 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
423 and group_oipl_id <> -1
424 and oipl_ordr_num = 4;
425
426 cursor actual_plans
427 IS
428 select count(pl_id) IdCount
429 ,max(ws_abr_id) ws_abr_id
430 ,max(elig_sal_abr_id) elig_sal_abr_id
431 ,max(ws_nnmntry_uom) ws_nnmntry_uom
432 ,max(ws_sub_acty_typ_cd) ws_sub_acty_typ_cd
433 from ben_cwb_pl_dsgn
434 where group_pl_id = p_group_pl_id
435 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
436 and group_pl_id <> pl_id
437 and group_oipl_id = -1
438 and oipl_id = -1;
439
440 cursor group_plan
441 IS
442 select count(group_pl_id) IdCount
443 ,max(ws_abr_id) ws_abr_id
444 ,max(elig_sal_abr_id) elig_sal_abr_id
445 ,max(ws_nnmntry_uom) ws_nnmntry_uom
446 ,max(ws_sub_acty_typ_cd) ws_sub_acty_typ_cd
447 from ben_cwb_pl_dsgn
448 where group_pl_id = p_group_pl_id
449 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
450 and group_pl_id = pl_id
451 and group_oipl_id = -1
452 and oipl_id = -1;
453
454 l_group_opt_exists_rec group_opt_exists%RowType;
455 l_actual_opt_exists_rec actual_opt_exists%RowType;
456 l_group_plan_rec group_plan%RowType;
457 l_actual_plans_rec actual_plans%RowType;
458 l_opt1_exists_rec opt1_exists%RowType;
459 l_opt2_exists_rec opt2_exists%RowType;
460 l_opt3_exists_rec opt3_exists%RowType;
461 l_opt4_exists_rec opt4_exists%RowType;
462 l_count Number;
463
464
465 begin
466 Open group_opt_exists;
467 Fetch group_opt_exists into l_group_opt_exists_rec;
468 Close group_opt_exists;
469
470 Open actual_opt_exists;
471 Fetch actual_opt_exists into l_actual_opt_exists_rec;
472 Close actual_opt_exists;
473
474 Open actual_plans;
475 Fetch actual_plans into l_actual_plans_rec;
476 Close actual_plans;
477
478 Open group_plan;
482
479 Fetch group_plan into l_group_plan_rec;
480 Close group_plan;
481
483 If (l_group_opt_exists_rec.IdCount <> 0 OR l_actual_opt_exists_rec.IdCount <> 0) Then
484
485 -- If Group Options Exists
486 Begin
487 hr_utility.set_location('Options Exists',50);
488 Open opt1_exists;
489 Fetch opt1_exists into l_opt1_exists_rec;
490 Close opt1_exists;
491
492 Open opt2_exists;
493 Fetch opt2_exists into l_opt2_exists_rec;
494 Close opt2_exists;
495
496 Open opt3_exists;
497 Fetch opt3_exists into l_opt3_exists_rec;
498 Close opt3_exists;
499
500 Open opt4_exists;
501 Fetch opt4_exists into l_opt4_exists_rec;
502 Close opt4_exists;
503
504 ----------------------------- Option 1 ------------------------------------------
505 l_count := l_show_hide_data.count +1 ;
506 If (l_opt1_exists_rec.IdCount <> 0) then
507 l_show_hide_data(l_count).p_type := 'OPT1';
508 l_show_hide_data(l_count).p_opt_defined := 'Y';
509
510 hr_utility.set_location('Opt1 Exists .. Count :'||l_count,100);
511 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_type,105);
512
513
514 If l_opt1_exists_rec.ws_abr_id is not Null Then
515 l_show_hide_data(l_count).p_ws_defined := 'Y';
516 Else
517 l_show_hide_data(l_count).p_ws_defined := 'N';
518 End if;
519
520 If l_opt1_exists_rec.elig_sal_abr_id is not Null Then
521 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
522 Else
523 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
524 End if;
525
526 If l_opt1_exists_rec.ws_nnmntry_uom is not Null Then
527 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
528 Else
529 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
530 End if;
531 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_type,105);
532 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_ws_defined,115);
533 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,120);
534 hr_utility.set_location('OPT1 ...'||l_show_hide_data(l_count).p_nnmntry_uom,125);
535
536 Else
537 l_show_hide_data(l_count).p_type := 'OPT1';
538 l_show_hide_data(l_count).p_opt_defined := 'N';
539 hr_utility.set_location('Opt1 Not Exists',120);
540 End If;
541
542 ----------------------------- Option 2 ------------------------------------------
543 l_count := l_show_hide_data.count +1 ;
544 If (l_opt2_exists_rec.IdCount <> 0) then
545 l_show_hide_data(l_count).p_type := 'OPT2';
546 l_show_hide_data(l_count).p_opt_defined := 'Y';
547
548 hr_utility.set_location('Opt2 Exists... Count :'||l_count,170);
549 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_type,175);
550
551 If l_opt2_exists_rec.ws_abr_id is not Null Then
552 l_show_hide_data(l_count).p_ws_defined := 'Y';
553 Else
554 l_show_hide_data(l_count).p_ws_defined := 'N';
555 End if;
556
557 If l_opt2_exists_rec.elig_sal_abr_id is not Null Then
558 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
559 Else
560 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
561 End if;
562
563 If l_opt2_exists_rec.ws_nnmntry_uom is not Null Then
564 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
565 Else
566 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
567 End if;
568 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_type,250);
569 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_ws_defined,255);
570 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,260);
571 hr_utility.set_location('OPT2 ...'||l_show_hide_data(l_count).p_nnmntry_uom,265);
572
573 Else
574 hr_utility.set_location('Opt2 Not Exists',240);
575 l_show_hide_data(l_count).p_type := 'OPT2';
576 l_show_hide_data(l_count).p_opt_defined := 'N';
577 End If;
578
579
580 ----------------------------- Option 3 ------------------------------------------
581 l_count := l_show_hide_data.count +1 ;
582 If (l_opt3_exists_rec.IdCount <> 0) then
583
584 l_show_hide_data(l_count).p_type := 'OPT3';
585 l_show_hide_data(l_count).p_opt_defined := 'Y';
586
587 hr_utility.set_location('Opt3 Exists...Count :'||l_count,300);
591 Else
588 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_type,305);
589 If l_opt3_exists_rec.ws_abr_id is not Null Then
590 l_show_hide_data(l_count).p_ws_defined := 'Y';
592 l_show_hide_data(l_count).p_ws_defined := 'N';
593 End if;
594
595 If l_opt3_exists_rec.elig_sal_abr_id is not Null Then
596 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
597 Else
598 l_show_hide_data(l_count).p_eligy_sal_defined:= 'N';
599 End if;
600
601 If l_opt3_exists_rec.ws_nnmntry_uom is not Null Then
602 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
603 Else
604 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
605 End if;
606 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_type,355);
607 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_ws_defined,360);
608 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,365);
609 hr_utility.set_location('OPT3 ...'||l_show_hide_data(l_count).p_nnmntry_uom,370);
610
611 Else
612 l_show_hide_data(l_count).p_type := 'OPT3';
613 l_show_hide_data(l_count).p_opt_defined := 'N';
614 hr_utility.set_location('Opt3 Not Exists',350);
615 End If;
616
617 ----------------------------- Option 4 ------------------------------------------
618 l_count := l_show_hide_data.count +1 ;
619 If (l_opt4_exists_rec.IdCount <> 0) then
620
621 l_show_hide_data(l_count).p_type := 'OPT4';
622 l_show_hide_data(l_count).p_opt_defined := 'Y';
623
624 hr_utility.set_location('Opt4 Exists...Count :'||l_count,400);
625 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_type,405);
626 If l_opt4_exists_rec.ws_abr_id is not Null Then
627 l_show_hide_data(l_count).p_ws_defined := 'Y';
628 Else
629 l_show_hide_data(l_count).p_ws_defined := 'N';
630 End if;
631
632 If l_opt4_exists_rec.elig_sal_abr_id is not Null Then
633 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
634 Else
635 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
636 End if;
637
638 If l_opt4_exists_rec.ws_nnmntry_uom is not Null Then
639 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
640 Else
641 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
642 End if;
643 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_type,555);
644 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_ws_defined,560);
645 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_eligy_sal_defined,565);
646 hr_utility.set_location('OPT4 ...'||l_show_hide_data(l_count).p_nnmntry_uom,570);
647
648 Else
649 l_show_hide_data(l_count).p_type := 'OPT4';
650 l_show_hide_data(l_count).p_opt_defined := 'N';
651 hr_utility.set_location('Opt4 Not Exists',500);
652 End If;
653 End;
654
655 Else
656 ----------------------------- Plan Only------------------------------------------
657
658 Begin
659 -- If Group Options not exists
660 l_count := l_show_hide_data.count +1 ;
661 l_show_hide_data(l_count).p_type := 'PLOY';
662 l_show_hide_data(l_count).p_opt_defined := 'N';
663 hr_utility.set_location('Options Not Exists ...Count :'||l_count,560);
664 If (l_actual_plans_rec.ws_abr_id is not Null OR
665 l_group_plan_rec.ws_abr_id is not Null )Then
666 l_show_hide_data(l_count).p_ws_defined := 'Y';
667 Else
668 l_show_hide_data(l_count).p_ws_defined := 'N';
669 End if;
670
671 If ( l_actual_plans_rec.elig_sal_abr_id is not Null OR
672 l_group_plan_rec.elig_sal_abr_id is not Null) Then
673 l_show_hide_data(l_count).p_eligy_sal_defined := 'Y';
674 Else
675 l_show_hide_data(l_count).p_eligy_sal_defined := 'N';
676 End if;
677
678 If (l_actual_plans_rec.ws_nnmntry_uom is not Null OR
679 l_group_plan_rec.ws_nnmntry_uom is not Null) Then
680 l_show_hide_data(l_count).p_nnmntry_uom := 'Y';
681 Else
682 l_show_hide_data(l_count).p_nnmntry_uom := 'N';
683 End if;
684
685 If (l_actual_plans_rec.ws_sub_acty_typ_cd = 'ICM7' OR
686 l_group_plan_rec.ws_sub_acty_typ_cd = 'ICM7') Then
687 l_show_hide_data(l_count).p_ws_sub_acty_typ_cd := 'Y';
688 Else
689 l_show_hide_data(l_count).p_ws_sub_acty_typ_cd := 'N';
690 End if;
691
692
696
693 End;
694 End If;
695 hr_utility.set_location('Final count'||l_count, 500);
697 For i in l_show_hide_data.first..l_show_hide_data.last
698 Loop
699 hr_utility.set_location('p_type('||i||') :'||l_show_hide_data(i).p_type, 500);
700 hr_utility.set_location('p_opt_defined('||i||') :'||l_show_hide_data(i).p_opt_defined, 510);
701 hr_utility.set_location('p_ws_defined('||i||') :'||l_show_hide_data(i).p_ws_defined, 520);
702 hr_utility.set_location('p_eligy_sal_defined('||i||') :'||l_show_hide_data(i).p_eligy_sal_defined, 530);
703 hr_utility.set_location('p_nnmntry_uom('||i||') :'||l_show_hide_data(i).p_nnmntry_uom, 540);
704 hr_utility.set_location('ws_sub_acty_typ_cd('||i||') :'||l_show_hide_data(i).p_ws_sub_acty_typ_cd, 541);
705
706 End Loop;
707 p_show_hide_data := l_show_hide_data;
708 Exception
709 when others then
710 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,1,50), 100);
711 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,51,100), 101);
712 hr_utility.set_location('sqlerrm:'||substr(sqlerrm,101,150), 102);
713
714 end check_hidden_worksheet_columns;
715 --
716 ------------------------ decide_insert_rec-----------------------
717 --
718 Function decide_insert_rec (p_inf_seq IN NUMBER
719 ,p_group_pl_id IN NUMBER
720 ,p_lf_evt_ocrd_dt IN DATE )
721 Return Varchar IS
722
723 Cursor csr_is_options IS
724 Select '1'
725 From bne_interface_cols_b
726 where interface_code ='BEN_CWB_WRK_SHT_INTF'
727 And INTERFACE_COL_NAME like '%OPT%'
728 And application_id = 800
729 And SEQUENCE_NUM = p_inf_seq;
730
731 Cursor csr_opt1 IS
732 Select '1'
733 From bne_interface_cols_b
734 where interface_code ='BEN_CWB_WRK_SHT_INTF'
735 And INTERFACE_COL_NAME like '%OPT1%'
736 And application_id = 800
737 And SEQUENCE_NUM = p_inf_seq;
738
739 Cursor csr_opt2 IS
740 Select '1'
741 From bne_interface_cols_b
742 where interface_code ='BEN_CWB_WRK_SHT_INTF'
743 And INTERFACE_COL_NAME like '%OPT2%'
744 And application_id = 800
745 And SEQUENCE_NUM = p_inf_seq;
746
747 Cursor csr_opt3 IS
748 Select '1'
749 From bne_interface_cols_b
750 where interface_code ='BEN_CWB_WRK_SHT_INTF'
751 And INTERFACE_COL_NAME like '%OPT3%'
752 And application_id = 800
753 And SEQUENCE_NUM = p_inf_seq;
754
755 Cursor csr_opt4 IS
756 Select '1'
757 From bne_interface_cols_b
758 where interface_code ='BEN_CWB_WRK_SHT_INTF'
759 And INTERFACE_COL_NAME like '%OPT4%'
760 And application_id = 800
761 And SEQUENCE_NUM = p_inf_seq;
762
763
764 l_insert_rec Varchar(1):= 'Y';
765 l_option VARCHAR2(1);
766 l_show_hide_data ben_cwb_webadi_utils.p_show_hide_data;
767 l_PLOY_index Number := 0;
768 l_OPT1_index Number := 0;
769 l_OPT2_index Number := 0;
770 l_OPT3_index Number := 0;
771 l_OPT4_index Number := 0;
772 l_proc Varchar2(72) := g_package||'decide_insert_rec';
773
774
775 Begin
776
777 hr_utility.set_location('Entering :'||l_proc,10);
778 hr_utility.set_location('** p_inf_seq :'||p_inf_seq,20);
779
780 check_hidden_worksheet_columns( p_group_pl_id => p_group_pl_id
781 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
782 ,p_show_hide_data => l_show_hide_data);
783
784
785
786 hr_utility.set_location('l_show_hide_data.count :'||l_show_hide_data.count,785);
787 hr_utility.set_location('l_show_hide_data.first :'||l_show_hide_data.first,786);
788 hr_utility.set_location('l_show_hide_data.last :'||l_show_hide_data.last,787);
789 For i in l_show_hide_data.first..l_show_hide_data.last
790 Loop
791 hr_utility.set_location('p_type('||i||') :'||l_show_hide_data(i).p_type, 4900);
792 hr_utility.set_location('p_opt_defined('||i||') :'||l_show_hide_data(i).p_opt_defined, 500);
793 hr_utility.set_location('p_ws_defined('||i||') :'||l_show_hide_data(i).p_ws_defined, 510);
794 hr_utility.set_location('p_eligy_sal_defined('||i||') :'||l_show_hide_data(i).p_eligy_sal_defined, 520);
795 hr_utility.set_location('p_nnmntry_uom('||i||') :'||l_show_hide_data(i).p_nnmntry_uom, 530);
796
797 if (l_show_hide_data(i).p_type = 'PLOY') then
798 l_PLOY_index := i;
799 end if;
800
801 if (l_show_hide_data(i).p_type = 'OPT1') then
802 l_OPT1_index := i;
803 end if;
804
805 if (l_show_hide_data(i).p_type = 'OPT2') then
806 l_OPT2_index := i;
807 end if;
808
809 if (l_show_hide_data(i).p_type = 'OPT3') then
810 l_OPT3_index := i;
811 end if;
812
813 if (l_show_hide_data(i).p_type = 'OPT4') then
814 l_OPT4_index := i;
815 end if;
816 End Loop;
817 hr_utility.set_location('l_PLOY_index :'||l_PLOY_index,540);
818 hr_utility.set_location('l_OPT1_index :'||l_OPT1_index,541);
819 hr_utility.set_location('l_OPT2_index :'||l_OPT2_index,542);
820 hr_utility.set_location('l_OPT3_index :'||l_OPT3_index,543);
821 hr_utility.set_location('l_OPT4_index :'||l_OPT4_index,544);
822
823
827 hr_utility.set_location('Start of Plan Only',545);
824 --------------------------------Start of Plan Only--------------------------------
825 IF (l_PLOY_index <> 0 and l_show_hide_data(l_PLOY_index).p_type = 'PLOY' ) THEN
826 Begin
828 -- If No Options Exists then disable all Option Columns
829 Open csr_is_options;
830 Fetch csr_is_options into l_option;
831 Close csr_is_options;
832 If l_option is not null then
833 l_insert_rec := 'N';
834 End if;
835
836 -- Plan Level Checks
837 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
838 hr_utility.set_location('Plan WS Aount Not defined....',800);
839 hr_utility.set_location('p_inf_seq : (12,7,159,165,9,5,164)'||p_inf_seq,820);
840 l_insert_rec := 'N';
841 End if;
842
843 If l_show_hide_data(l_PLOY_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (165,9,5,164) ) then
844 hr_utility.set_location( 'Plan Eligible Sal Not Defined...',820);
845 hr_utility.set_location('p_inf_seq : (165,9,5,164)'||p_inf_seq,850);
846 l_insert_rec := 'N';
847 End if;
848
849 /* If l_show_hide_data(l_PLOY_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (7,159,165,9,5,164)) then
850 hr_utility.set_location( 'Plan is Non monetory Unit...',860);
851 hr_utility.set_location('p_inf_seq : (7,159,165,9,5,164)'||p_inf_seq,850);
852 l_insert_rec := 'N';
853 End if;
854 */
855 ----------------------------------
856 -- If Plan is Non-Monetory unit and Eligible Salary is defined then
857 -- enable Eligible Salary and
858 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Base Salary, New Salary
859
860 -- If Plan is Non-Monetory unit and Eligible Salary is not defined then
861 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary Base Salary, New Salary
862 If l_show_hide_data(l_PLOY_index).p_nnmntry_uom = 'Y' Then
863
864 If l_show_hide_data(l_PLOY_index).p_eligy_sal_defined = 'Y' Then
865 -- mvankada
866 -- Bug : 3751069
867 -- Modified the if condition to show the % of Eligible Salary
868 If p_inf_seq In (9,165) Then
869 l_insert_rec := 'Y';
870 elsif p_inf_seq in (7,159,5,164) Then
871 l_insert_rec := 'N';
872 end if;
873 Else
874 If p_inf_seq in (7,159,165,9,5,164) then
875 l_insert_rec := 'N';
876 End if;
877
878 end if;
879 End If;
880 ----------------------------------
881 -- Bug : 3576954
882 -- Author : mvankada
883 -- Display Base Salary
884 If p_inf_seq = 5 then
885 l_insert_rec := 'Y';
886 End if;
887
888 ----------------------------------
889
890 If (l_show_hide_data(l_PLOY_index).p_ws_sub_acty_typ_cd = 'N' And p_inf_seq = 164) then
891 hr_utility.set_location( 'Plan is Salary ...',870);
892 hr_utility.set_location('(p_inf_seq = 164) :'||p_inf_seq,890);
893 l_insert_rec := 'N';
894 End if;
895
896 End;
897 End If;
898 --------------------------------End of Plan Only--------------------------------
899
900
901 --------------------------------Start of Option1 Only--------------------------------
902
903 -- Option1
904 IF (l_OPT1_index <> 0 And l_show_hide_data(l_OPT1_index).p_type = 'OPT1' ) THEN
905 Begin
906 If l_show_hide_data(l_OPT1_index).p_opt_defined = 'N' then
907 hr_utility.set_location( 'Option 1 is not defined...',900);
908 -- Disable all Option1 Columns
909 Open csr_opt1;
910 Fetch csr_opt1 into l_option;
911 Close csr_opt1;
912 If l_option is not null then
913 l_insert_rec := 'N';
914 End if;
915 Else
916 hr_utility.set_location( 'Option 1 is defined...',910);
917 -- Option1 Level Checks
918 If l_show_hide_data(l_OPT1_index).p_ws_defined = 'N' And (p_inf_seq in (30,25,160,166,27) ) then
919 hr_utility.set_location( 'Option 1 worksheet Amount is not defined...',920);
920 hr_utility.set_location('p_inf_seq : (30,25,160,166,27)'||p_inf_seq,950);
921 l_insert_rec := 'N';
922 End if;
923
924 If l_show_hide_data(l_OPT1_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (166,27) ) then
925 hr_utility.set_location( 'Option 1 Eligibile Sal is not defined...',970);
926 hr_utility.set_location('p_inf_seq : (166,27)'||p_inf_seq,1000);
927 l_insert_rec := 'N';
928 End if;
929
930 /* If l_show_hide_data(l_OPT1_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (25,160,166,27)) then
931 hr_utility.set_location( 'Option 1 is Non Monetory Unit...',1010);
935 */
932 hr_utility.set_location('p_inf_seq : (25,160,166,27)'||p_inf_seq,1020);
933 l_insert_rec := 'N';
934 End if;
936 ----------------------------------
937 -- If Option1 is Non-Monetory unit and Eligible Salary is defined then
938 -- enable Eligible Salary and
939 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
940
941 -- If Option1 is Non-Monetory unit and Eligible Salary is not defined then
942 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
943
944 If l_show_hide_data(l_OPT1_index).p_nnmntry_uom = 'Y' Then
945
946 If l_show_hide_data(l_OPT1_index).p_eligy_sal_defined = 'Y' Then
947 If p_inf_seq = 27 Then
948 l_insert_rec := 'Y';
949 elsif p_inf_seq in (25,160,166) Then
950 l_insert_rec := 'N';
951 end if;
952 Else
953 If p_inf_seq in (25,160,166,27) then
954 l_insert_rec := 'N';
955 End if;
956
957 end if;
958 End If;
959 ----------------------------------
960 End If;
961 End;
962 End If;
963 -------------------------------End of Option1 Only--------------------------------
964
965 -- Option2
966
967 -------------------------------Start of Option2 Only--------------------------------
968 IF (l_OPT2_index <> 0 And l_show_hide_data(l_OPT2_index).p_type = 'OPT2' ) THEN
969 Begin
970 If l_show_hide_data(l_OPT2_index).p_opt_defined = 'N' then
971 -- Disable all Option1 Columns
972 Open csr_opt2;
973 Fetch csr_opt2 into l_option;
974 Close csr_opt2;
975 If l_option is not null then
976 l_insert_rec := 'N';
977 End if;
978 Else
979 -- Option1 Level Checks
980 If l_show_hide_data(l_OPT2_index).p_ws_defined = 'N' And (p_inf_seq in (48,43,161,167,45) ) then
981 l_insert_rec := 'N';
982 End if;
983
984 If l_show_hide_data(l_OPT2_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (167,45) ) then
985 l_insert_rec := 'N';
986 End if;
987
988 /* If l_show_hide_data(l_OPT2_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (43,161,167,45)) then
989 l_insert_rec := 'N';
990 End if;
991 */
992 ----------------------------------
993 -- If Option2 is Non-Monetory unit and Eligible Salary is defined then
994 -- enable Eligible Salary and
995 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
996
997 -- If Option2 is Non-Monetory unit and Eligible Salary is not defined then
998 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
999
1000 If l_show_hide_data(l_OPT2_index).p_nnmntry_uom = 'Y' Then
1001
1002 If l_show_hide_data(l_OPT2_index).p_eligy_sal_defined = 'Y' Then
1003 If p_inf_seq = 45 Then
1004 l_insert_rec := 'Y';
1005 elsif p_inf_seq in (43,161,167) Then
1006 l_insert_rec := 'N';
1007 end if;
1008 Else
1009 If p_inf_seq in (43,161,167,45) then
1010 l_insert_rec := 'N';
1011 End if;
1012
1013 end if;
1014 End If;
1015 ----------------------------------
1016 End If;
1017 end;
1018 End If;
1019 -------------------------------End of Option2 Only--------------------------------
1020 -- Option3
1021
1022 -------------------------------Start of Option3 Only--------------------------------
1023 IF (l_OPT3_index <> 0 And l_show_hide_data(l_OPT3_index).p_type = 'OPT3' ) THEN
1024 Begin
1025 If l_show_hide_data(l_OPT3_index).p_opt_defined = 'N' then
1026 -- Disable all Option1 Columns
1027 Open csr_opt3;
1028 Fetch csr_opt3 into l_option;
1029 Close csr_opt3;
1030 If l_option is not null then
1031 l_insert_rec := 'N';
1032 End if;
1033 Else
1034 -- Option1 Level Checks
1035 If l_show_hide_data(l_OPT3_index).p_ws_defined = 'N' And (p_inf_seq in (66,61,162,168,63) ) then
1036 l_insert_rec := 'N';
1037 End if;
1038
1039 If l_show_hide_data(l_OPT3_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (168,63) ) then
1040 l_insert_rec := 'N';
1041 End if;
1042
1043 /* If l_show_hide_data(l_OPT3_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (61,162,168,63)) then
1044 l_insert_rec := 'N';
1045 End if;
1046 */
1047 ----------------------------------
1048 -- If Option3 is Non-Monetory unit and Eligible Salary is defined then
1049 -- enable Eligible Salary and
1050 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
1051
1052 -- If Option3 is Non-Monetory unit and Eligible Salary is not defined then
1056
1053 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
1054
1055 If l_show_hide_data(l_OPT3_index).p_nnmntry_uom = 'Y' Then
1057 If l_show_hide_data(l_OPT3_index).p_eligy_sal_defined = 'Y' Then
1058 If p_inf_seq = 63 Then
1059 l_insert_rec := 'Y';
1060 elsif p_inf_seq in (61,162,168) Then
1061 l_insert_rec := 'N';
1062 end if;
1063 Else
1064 If p_inf_seq in (61,162,168,63) then
1065 l_insert_rec := 'N';
1066 End if;
1067
1068 end if;
1069 End If;
1070 ----------------------------------
1071 End If;
1072 end;
1073 End if;
1074
1075 -------------------------------End of Option3 Only--------------------------------
1076
1077
1078 -------------------------------Start of Option4 Only--------------------------------
1079
1080 IF (l_OPT4_index <> 0 And l_show_hide_data(l_OPT4_index).p_type = 'OPT4' ) THEN
1081 Begin
1082 -- Option4
1083 If l_show_hide_data(l_OPT4_index).p_opt_defined = 'N' then
1084 -- Disable all Option1 Columns
1085 Open csr_opt4;
1086 Fetch csr_opt4 into l_option;
1087 Close csr_opt4;
1088 If l_option is not null then
1089 l_insert_rec := 'N';
1090 End if;
1091 Else
1092 -- Option1 Level Checks
1093 If l_show_hide_data(l_OPT4_index).p_ws_defined = 'N' And (p_inf_seq in (84,79,163,169,81) ) then
1094 l_insert_rec := 'N';
1095 End if;
1096
1097 If l_show_hide_data(l_OPT4_index).p_eligy_sal_defined = 'N' And (p_inf_seq in (169,81) ) then
1098 l_insert_rec := 'N';
1099 End if;
1100
1101 /* If l_show_hide_data(l_OPT4_index).p_nnmntry_uom = 'Y' And( p_inf_seq in (79,163,169,81)) then
1102 l_insert_rec := 'N';
1103 End if;
1104 */
1105 ----------------------------------
1106 -- If Option4 is Non-Monetory unit and Eligible Salary is defined then
1107 -- enable Eligible Salary and
1108 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry
1109
1110 -- If Option4 is Non-Monetory unit and Eligible Salary is not defined then
1111 -- disable Exchange Rate, Common Currency Rate,% Eligible Salry, Eligible Salary
1112
1113 If l_show_hide_data(l_OPT4_index).p_nnmntry_uom = 'Y' Then
1114
1115 If l_show_hide_data(l_OPT4_index).p_eligy_sal_defined = 'Y' Then
1116 If p_inf_seq = 81 Then
1117 l_insert_rec := 'Y';
1118 elsif p_inf_seq in (79,163,169,81) Then
1119 l_insert_rec := 'N';
1120 end if;
1121 Else
1122 If p_inf_seq in (79,163,169,81) then
1123 l_insert_rec := 'N';
1124 End if;
1125
1126 end if;
1127 End If;
1128 ----------------------------------
1129 End If;
1130
1131 End;
1132 End if;
1133 -------------------------------End of Option4 Only--------------------------------
1134
1135 -- If Options Exists then don't display Plan Group Columns
1136 Begin
1137 IF ( (l_OPT1_index <> 0 And l_show_hide_data(l_OPT1_index).p_type = 'OPT1') OR
1138 (l_OPT2_index <> 0 And l_show_hide_data(l_OPT2_index).p_type = 'OPT2') OR
1139 (l_OPT3_index <> 0 And l_show_hide_data(l_OPT3_index).p_type = 'OPT3') OR
1140 (l_OPT4_index <> 0 And l_show_hide_data(l_OPT4_index).p_type = 'OPT4')
1141 ) THEN
1142 If p_inf_seq = 130 then
1143 l_insert_rec := 'N';
1144 end if;
1145
1146 End if;
1147 End;
1148
1149 return l_insert_rec;
1150 End decide_insert_rec ;
1151
1152 --
1153 --------------------- insert_cwb_layout_cols_row---------------------
1154 --
1155 Procedure insert_cwb_layout_cols_row(p_application_id In Number,
1156 p_base_layout_code In Varchar2,
1157 p_act_layout_code In Varchar2,
1158 p_inf_seq In Number,
1159 p_dis_seq In Number,
1160 p_group_pl_id In Number Default Null,
1161 p_lf_evt_ocrd_dt In Date Default Null
1162 )
1163 IS
1164 CURSOR csr_layout_cols_row
1165 IS
1166 SELECT blc.application_id
1167 ,blc.layout_code
1168 ,blc.block_id
1169 ,blc.interface_app_id
1170 ,blc.interface_code
1171 ,blc.interface_seq_num
1172 ,blc.sequence_num
1173 ,blc.style
1174 ,blc.style_class
1175 ,blc.hint_style
1176 ,blc.hint_style_class
1177 ,blc.prompt_style
1178 ,blc.prompt_style_class
1179 ,blc.default_type
1180 ,blc.DEFAULT_VALUE
1181 ,blc.created_by
1182 ,blc.last_updated_by
1183 ,blc.last_update_login
1184 ,blc.read_only_flag
1185 FROM bne_layout_cols blc
1186 WHERE blc.application_id = p_application_id
1187 AND blc.layout_code = p_base_layout_code
1188 AND blc.interface_seq_num = p_inf_seq;
1192 l_layout_cols_row csr_layout_cols_row%RowType;
1189
1190
1191
1193
1194 l_proc Varchar2(72) := 'create_cwb_layout_cols_row';
1195 l_rowid VARCHAR2(200);
1196 l_option VARCHAR2(1);
1197
1198 l_insert_rec VARCHAR2(1) := 'Y';
1199 l_show_hide_data p_show_hide_data;
1200 l_read_only_flag VARCHAR2(1) := NULL;
1201 l_display_width NUMBER := NULL;
1202
1203 Begin
1204
1205 hr_utility.set_location('Entering '||l_proc,10);
1206 hr_utility.set_location('p_group_pl_id :'||p_group_pl_id,20);
1207 hr_utility.set_location('p_lf_evt_ocrd_dt :'||p_lf_evt_ocrd_dt,30);
1208
1209 If p_base_layout_code <> 'BEN_CWB_WRK_SHT_BASE_LYT' then
1210 l_insert_rec := 'Y';
1211 Else
1212 l_insert_rec := decide_insert_rec (p_inf_seq => p_inf_seq
1213 ,p_group_pl_id => p_group_pl_id
1214 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1215 End If;
1216 hr_utility.set_location('p_inf_seq #: '||p_inf_seq||' l_insert_rec #:'||l_insert_rec,60);
1217
1218 OPEN csr_layout_cols_row;
1219 FETCH csr_layout_cols_row INTO l_layout_cols_row;
1220 if csr_layout_cols_row%NOTFOUND then
1221 l_insert_rec := 'N';
1222 hr_utility.set_location('Base Layout Does Not Have Item Checked:'||l_proc, 70);
1223 end if;
1224 CLOSE csr_layout_cols_row;
1225
1226 If l_insert_rec = 'Y' then
1227
1228 IF (p_base_layout_code = 'BEN_CWB_WRK_SHT_BASE_LYT') THEN
1229 l_read_only_flag := 'Y';
1230 --Hide the contextual security keys
1231 IF (l_layout_cols_row.interface_seq_num IN (158, 189, 194, 195, 196, 198)) then
1232 l_display_width := 0;
1233 --Hide the security keys in lines
1234 ELSIF (l_layout_cols_row.interface_seq_num IN (130, 131, 132, 133, 134)) then
1235 l_display_width := 0;
1236 l_read_only_flag := 'N';
1237 --Make non-updateable columns as read-only
1238 ELSIF (l_layout_cols_row.interface_seq_num IN (3,12,30,48,66,84,151) OR
1239 l_layout_cols_row.interface_seq_num BETWEEN 200 AND 229
1240 --changed by KMG included the below condition
1241 OR l_layout_cols_row.interface_seq_num BETWEEN 240 and 244) THEN
1242 l_read_only_flag := 'N';
1243 END IF;
1244 IF(l_read_only_flag = 'N') THEN
1245 l_read_only_flag := l_layout_cols_row.read_only_flag;
1246 END IF;
1247 END IF;
1248
1249 bne_layout_cols_pkg.insert_row
1250 (x_rowid => l_rowid
1251 ,x_application_id => l_layout_cols_row.application_id
1252 ,x_layout_code => p_act_layout_code
1253 ,x_block_id => l_layout_cols_row.block_id
1254 ,x_sequence_num => p_dis_seq
1255 ,x_object_version_number => 1
1256 ,x_interface_app_id => l_layout_cols_row.interface_app_id
1257 ,x_interface_code => l_layout_cols_row.interface_code
1258 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
1259 ,x_style_class => l_layout_cols_row.style_class
1260 ,x_hint_style => l_layout_cols_row.hint_style
1261 ,x_hint_style_class => l_layout_cols_row.hint_style_class
1262 ,x_prompt_style => l_layout_cols_row.prompt_style
1263 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
1264 ,x_default_type => l_layout_cols_row.default_type
1265 ,x_default_value => l_layout_cols_row.DEFAULT_VALUE
1266 ,x_style => l_layout_cols_row.style
1267 ,x_creation_date => SYSDATE
1268 ,x_created_by => l_layout_cols_row.created_by
1269 ,x_last_update_date => SYSDATE
1270 ,x_last_updated_by => l_layout_cols_row.last_updated_by
1271 ,x_last_update_login => l_layout_cols_row.last_update_login
1272 ,x_read_only_flag => l_read_only_flag
1273 ,x_display_width => l_display_width);
1274 end if;
1275
1276 hr_utility.set_location('Leaving '||l_proc,100);
1277 End insert_cwb_layout_cols_row;
1278
1279 --
1280 --------------------------update_cwb_layout----------------------
1281 --
1282
1283 PROCEDURE update_cwb_layout(
1284 p_layout_code IN VARCHAR2
1285 ,p_base_layout IN VARCHAR2
1286 ,p_interface_seq IN VARCHAR2
1287 ,p_rendered_seq IN VARCHAR2
1288 ,p_group_pl_id IN NUMBER Default Null
1289 ,p_lf_evt_ocrd_dt IN DATE Default Null
1290 ,p_download_switch OUT NOCOPY VARCHAR2
1291 ) IS
1292
1293 LIST column_list := column_list();
1294 list_rec column_rec;
1295 l_application_id NUMBER(3) := 800;
1296 l_temp_layout_code VARCHAR2(100) := p_base_layout;
1297 l_params VARCHAR2(1000) := p_rendered_seq;
1298 l_interface_seq VARCHAR2(1000) := p_interface_seq;
1299 l_rowid VARCHAR2(200);
1300 param_loc NUMBER(3);
1301 interface_seq_loc NUMBER(3);
1302 param_len NUMBER(3) := LENGTH(l_params);
1306 idx NUMBER := 1;
1303 interface_seq_len NUMBER(3) := LENGTH(l_interface_seq);
1304 param_val VARCHAR2(1000);
1305 interface_seq_val VARCHAR2(100);
1307 l_pl_ws_amt_switch VARCHAR(1) := '1';
1308 l_opt1_ws_amt_switch VARCHAR(1) := '1';
1309 l_opt2_ws_amt_switch VARCHAR(1) := '1';
1310 l_opt3_ws_amt_switch VARCHAR(1) := '1';
1311 l_opt4_ws_amt_switch VARCHAR(1) := '1';
1312 l_perf_switch VARCHAR(1) := '1';
1313 l_rank_switch VARCHAR(1) := '1';
1314 l_cpi_attribute1_switch VARCHAR(1) := '1';
1315 l_cpi_attribute2_switch VARCHAR(1) := '1';
1316 l_cpi_attribute3_switch VARCHAR(1) := '1';
1317 l_cpi_attribute4_switch VARCHAR(1) := '1';
1318 l_cpi_attribute5_switch VARCHAR(1) := '1';
1319 l_cpi_attribute6_switch VARCHAR(1) := '1';
1320 l_cpi_attribute7_switch VARCHAR(1) := '1';
1321 l_cpi_attribute8_switch VARCHAR(1) := '1';
1322 l_cpi_attribute9_switch VARCHAR(1) := '1';
1323 l_cpi_attribute10_switch VARCHAR(1) := '1';
1324 l_cpi_attribute11_switch VARCHAR(1) := '1';
1325 l_cpi_attribute12_switch VARCHAR(1) := '1';
1326 l_cpi_attribute13_switch VARCHAR(1) := '1';
1327 l_cpi_attribute14_switch VARCHAR(1) := '1';
1328 l_cpi_attribute15_switch VARCHAR(1) := '1';
1329 l_cpi_attribute16_switch VARCHAR(1) := '1';
1330 l_cpi_attribute17_switch VARCHAR(1) := '1';
1331 l_cpi_attribute18_switch VARCHAR(1) := '1';
1332 l_cpi_attribute19_switch VARCHAR(1) := '1';
1333 l_cpi_attribute20_switch VARCHAR(1) := '1';
1334 l_cpi_attribute21_switch VARCHAR(1) := '1';
1335 l_cpi_attribute22_switch VARCHAR(1) := '1';
1336 l_cpi_attribute23_switch VARCHAR(1) := '1';
1337 l_cpi_attribute24_switch VARCHAR(1) := '1';
1338 l_cpi_attribute25_switch VARCHAR(1) := '1';
1339 l_cpi_attribute26_switch VARCHAR(1) := '1';
1340 l_cpi_attribute27_switch VARCHAR(1) := '1';
1341 l_cpi_attribute28_switch VARCHAR(1) := '1';
1342 l_cpi_attribute29_switch VARCHAR(1) := '1';
1343 l_cpi_attribute30_switch VARCHAR(1) := '1';
1344
1345 CURSOR c_layout_cols( v_application_id IN NUMBER
1346 ,v_layout_code IN VARCHAR2)
1347 IS
1348 SELECT blc.application_id
1349 ,blc.layout_code
1350 ,blc.block_id
1351 ,blc.sequence_num
1352 FROM bne_layout_cols blc
1353 WHERE blc.application_id = v_application_id
1354 AND blc.layout_code = v_layout_code;
1355
1356 CURSOR c_layout_cols_row(
1357 v_interface_seq IN NUMBER
1358 ,v_application_id IN NUMBER
1359 ,v_layout_code IN VARCHAR2) IS
1360 SELECT blc.application_id
1361 ,blc.layout_code
1362 ,blc.block_id
1363 ,blc.interface_app_id
1364 ,blc.interface_code
1365 ,blc.interface_seq_num
1366 ,blc.sequence_num
1367 ,blc.style
1368 ,blc.style_class
1369 ,blc.hint_style
1370 ,blc.hint_style_class
1371 ,blc.prompt_style
1372 ,blc.prompt_style_class
1373 ,blc.default_type
1374 ,blc.DEFAULT_VALUE
1375 ,blc.created_by
1376 ,blc.last_updated_by
1377 ,blc.last_update_login
1378 FROM bne_layout_cols blc
1379 WHERE blc.application_id = v_application_id
1380 AND blc.layout_code = v_layout_code
1381 AND blc.interface_seq_num = v_interface_seq;
1382
1383 l_layout_cols_row c_layout_cols_row%ROWTYPE;
1384 l_layout_col_rec c_layout_cols%ROWTYPE;
1385 BEGIN
1386
1387 LOOP
1388 param_loc := INSTR(l_params, '+');
1389 interface_seq_loc := INSTR(l_interface_seq, '+');
1390 param_val := SUBSTR(l_params, 1, param_loc - 1);
1391 interface_seq_val := SUBSTR(l_interface_seq, 1, interface_seq_loc - 1);
1392
1393 IF param_loc = 0 THEN
1394 param_val := l_params;
1395 END IF;
1396
1397 IF interface_seq_loc = 0 THEN
1398 interface_seq_val := l_interface_seq;
1399 END IF;
1400
1401 l_params := SUBSTR(l_params, param_loc + 1, param_len);
1402 l_interface_seq := SUBSTR(l_interface_seq, interface_seq_loc + 1, interface_seq_len);
1403 If g_debug then
1404 hr_utility.set_location('parameter : '||param_val||' '||interface_seq_val,20);
1405 END IF;
1406 LIST.EXTEND;
1407 list_rec.p_sequence := TO_NUMBER(param_val);
1408 list_rec.p_interface_seq := TO_NUMBER(interface_seq_val);
1409 LIST(idx) := list_rec;
1410
1411 EXIT WHEN param_loc = 0;
1412 idx := idx + 1;
1413 END LOOP;
1414
1415 delete_cwb_layout_cols(p_layout_code => p_layout_code
1416 ,p_application_id => l_application_id);
1417
1418 /* BEGIN
1419 OPEN c_layout_cols(l_application_id, p_layout_code);
1420
1421 LOOP
1422 FETCH c_layout_cols INTO l_layout_col_rec;
1423 EXIT WHEN c_layout_cols%NOTFOUND;
1424
1425 -- DBMS_OUTPUT.put_line('## Seq Num :'||l_layout_col_rec.sequence_num);
1426 hr_utility.set_location('Seq Num :'||l_layout_col_rec.sequence_num,25);
1427 bne_layout_cols_pkg.delete_row
1428 (x_application_id => l_layout_col_rec.application_id
1429 ,x_layout_code => l_layout_col_rec.layout_code
1430 ,x_block_id => l_layout_col_rec.block_id
1434 CLOSE c_layout_cols;
1431 ,x_sequence_num => l_layout_col_rec.sequence_num);
1432 END LOOP;
1433
1435 EXCEPTION
1436 WHEN OTHERS
1437 THEN
1438 Null;
1439 hr_utility.set_location('ERROR occured',30);
1440 -- DBMS_OUTPUT.put_line('ERROR WHILE DELETING');
1441 END;
1442 */
1443
1444
1445 FOR k IN LIST.FIRST .. LIST.LAST
1446 LOOP
1447 IF (LIST(k).p_sequence <> 0) THEN
1448
1449 IF(LIST(k).p_interface_seq = 12) THEN
1450 l_pl_ws_amt_switch := '2';
1451 ELSIF(LIST(k).p_interface_seq = 30) THEN
1452 l_opt1_ws_amt_switch := '2';
1453 ELSIF(LIST(k).p_interface_seq = 48) THEN
1454 l_opt2_ws_amt_switch := '2';
1455 ELSIF(LIST(k).p_interface_seq = 66) THEN
1456 l_opt3_ws_amt_switch := '2';
1457 ELSIF(LIST(k).p_interface_seq = 84) THEN
1458 l_opt4_ws_amt_switch := '2';
1459 ELSIF(LIST(k).p_interface_seq = 151) THEN
1460 l_perf_switch := '2';
1461 ELSIF(LIST(k).p_interface_seq = 3) THEN
1462 l_rank_switch := '2';
1463 ELSIF(LIST(k).p_interface_seq = 200) THEN
1464 l_cpi_attribute1_switch := '2';
1465 ELSIF(LIST(k).p_interface_seq = 201) THEN
1466 l_cpi_attribute2_switch := '2';
1467 ELSIF(LIST(k).p_interface_seq = 202) THEN
1468 l_cpi_attribute3_switch := '2';
1469 ELSIF(LIST(k).p_interface_seq = 203) THEN
1470 l_cpi_attribute4_switch := '2';
1471 ELSIF(LIST(k).p_interface_seq = 204) THEN
1472 l_cpi_attribute5_switch := '2';
1473 ELSIF(LIST(k).p_interface_seq = 205) THEN
1474 l_cpi_attribute6_switch := '2';
1475 ELSIF(LIST(k).p_interface_seq = 206) THEN
1476 l_cpi_attribute7_switch := '2';
1477 ELSIF(LIST(k).p_interface_seq = 207) THEN
1478 l_cpi_attribute8_switch := '2';
1479 ELSIF(LIST(k).p_interface_seq = 208) THEN
1480 l_cpi_attribute9_switch := '2';
1481 ELSIF(LIST(k).p_interface_seq = 209) THEN
1482 l_cpi_attribute10_switch := '2';
1483 ELSIF(LIST(k).p_interface_seq = 210) THEN
1484 l_cpi_attribute11_switch := '2';
1485 ELSIF(LIST(k).p_interface_seq = 211) THEN
1486 l_cpi_attribute12_switch := '2';
1487 ELSIF(LIST(k).p_interface_seq = 212) THEN
1488 l_cpi_attribute13_switch := '2';
1489 ELSIF(LIST(k).p_interface_seq = 213) THEN
1490 l_cpi_attribute14_switch := '2';
1491 ELSIF(LIST(k).p_interface_seq = 214) THEN
1492 l_cpi_attribute15_switch := '2';
1493 ELSIF(LIST(k).p_interface_seq = 215) THEN
1494 l_cpi_attribute16_switch := '2';
1495 ELSIF(LIST(k).p_interface_seq = 216) THEN
1496 l_cpi_attribute17_switch := '2';
1497 ELSIF(LIST(k).p_interface_seq = 217) THEN
1498 l_cpi_attribute18_switch := '2';
1499 ELSIF(LIST(k).p_interface_seq = 218) THEN
1500 l_cpi_attribute19_switch := '2';
1501 ELSIF(LIST(k).p_interface_seq = 219) THEN
1502 l_cpi_attribute20_switch := '2';
1503 ELSIF(LIST(k).p_interface_seq = 220) THEN
1504 l_cpi_attribute21_switch := '2';
1505 ELSIF(LIST(k).p_interface_seq = 221) THEN
1506 l_cpi_attribute22_switch := '2';
1507 ELSIF(LIST(k).p_interface_seq = 222) THEN
1508 l_cpi_attribute23_switch := '2';
1509 ELSIF(LIST(k).p_interface_seq = 223) THEN
1510 l_cpi_attribute24_switch := '2';
1511 ELSIF(LIST(k).p_interface_seq = 224) THEN
1512 l_cpi_attribute25_switch := '2';
1513 ELSIF(LIST(k).p_interface_seq = 225) THEN
1514 l_cpi_attribute26_switch := '2';
1515 ELSIF(LIST(k).p_interface_seq = 226) THEN
1516 l_cpi_attribute27_switch := '2';
1517 ELSIF(LIST(k).p_interface_seq = 227) THEN
1518 l_cpi_attribute28_switch := '2';
1519 ELSIF(LIST(k).p_interface_seq = 228) THEN
1520 l_cpi_attribute29_switch := '2';
1521 ELSIF(LIST(k).p_interface_seq = 229) THEN
1522 l_cpi_attribute30_switch := '2';
1523 END IF;
1524
1525 insert_cwb_layout_cols_row( p_application_id => l_application_id
1526 ,p_base_layout_code => p_base_layout
1527 ,p_act_layout_code => p_layout_code
1528 ,p_inf_seq => LIST(k).p_interface_seq
1529 ,p_dis_seq => LIST(k).p_sequence
1530 ,p_group_pl_id => p_group_pl_id
1531 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
1532 /* OPEN c_layout_cols_row(LIST(k).p_interface_seq
1533 ,l_application_id
1534 ,l_temp_layout_code);
1535
1536 FETCH c_layout_cols_row INTO l_layout_cols_row;
1537 CLOSE c_layout_cols_row;
1538
1539 -- DBMS_OUTPUT.put_line('Sequence :'||LIST(k).p_sequence ||'Inf Seq :'|| l_layout_cols_row.interface_seq_num);
1540
1544 ,x_application_id => l_layout_cols_row.application_id
1541 hr_utility.set_location('Sequence :'||LIST(k).p_sequence||'Inf Seq : '||l_layout_cols_row.interface_seq_num ,50);
1542 bne_layout_cols_pkg.insert_row
1543 (x_rowid => l_rowid
1545 ,x_layout_code => p_layout_code
1546 ,x_block_id => l_layout_cols_row.block_id
1547 ,x_sequence_num => LIST(k).p_sequence
1548 ,x_object_version_number => 1
1549 ,x_interface_app_id => l_layout_cols_row.interface_app_id
1550 ,x_interface_code => l_layout_cols_row.interface_code
1551 ,x_interface_seq_num => l_layout_cols_row.interface_seq_num
1552 ,x_style_class => l_layout_cols_row.style_class
1553 ,x_hint_style => l_layout_cols_row.hint_style
1554 ,x_hint_style_class => l_layout_cols_row.hint_style_class
1555 ,x_prompt_style => l_layout_cols_row.prompt_style
1556 ,x_prompt_style_class => l_layout_cols_row.prompt_style_class
1557 ,x_default_type => l_layout_cols_row.default_type
1558 ,x_default_value => l_layout_cols_row.DEFAULT_VALUE
1559 ,x_style => l_layout_cols_row.style
1560 ,x_creation_date => SYSDATE
1561 ,x_created_by => l_layout_cols_row.created_by
1562 ,x_last_update_date => SYSDATE
1563 ,x_last_updated_by => l_layout_cols_row.last_updated_by
1564 ,x_last_update_login => l_layout_cols_row.last_update_login);
1565 */
1566 END IF;
1567 END LOOP;
1568 p_download_switch := l_pl_ws_amt_switch||
1569 l_opt1_ws_amt_switch||
1570 l_opt2_ws_amt_switch||
1571 l_opt3_ws_amt_switch||
1572 l_opt4_ws_amt_switch||
1573 l_perf_switch||
1574 l_rank_switch||
1575 l_cpi_attribute1_switch||
1576 l_cpi_attribute2_switch||
1577 l_cpi_attribute3_switch||
1578 l_cpi_attribute4_switch||
1579 l_cpi_attribute5_switch||
1580 l_cpi_attribute6_switch||
1581 l_cpi_attribute7_switch||
1582 l_cpi_attribute8_switch||
1583 l_cpi_attribute9_switch||
1584 l_cpi_attribute10_switch||
1585 l_cpi_attribute11_switch||
1586 l_cpi_attribute12_switch||
1587 l_cpi_attribute13_switch||
1588 l_cpi_attribute14_switch||
1589 l_cpi_attribute15_switch||
1590 l_cpi_attribute16_switch||
1591 l_cpi_attribute17_switch||
1592 l_cpi_attribute18_switch||
1593 l_cpi_attribute19_switch||
1594 l_cpi_attribute20_switch||
1595 l_cpi_attribute21_switch||
1596 l_cpi_attribute22_switch||
1597 l_cpi_attribute23_switch||
1598 l_cpi_attribute24_switch||
1599 l_cpi_attribute25_switch||
1600 l_cpi_attribute26_switch||
1601 l_cpi_attribute27_switch||
1602 l_cpi_attribute28_switch||
1603 l_cpi_attribute29_switch||
1604 l_cpi_attribute30_switch;
1605
1606 p_download_switch := REPLACE(REPLACE(p_download_switch,'1','0'),'2','1');
1607 p_download_switch := nvl(lpad(int2hex(bin2int(substr(p_download_switch,1,28))),7,0)||
1608 int2hex(bin2int(rpad(substr(p_download_switch,29),28,0))),'0000000');
1609 END update_cwb_layout;
1610
1611 --
1612 --------------------------encrypt----------------------
1613 --
1614
1615 FUNCTION encrypt(
1616 input_string IN VARCHAR2)
1617 RETURN VARCHAR2
1618 IS
1619 l_encrypted_string VARCHAR2(2048);
1620 BEGIN
1621 l_encrypted_string := NULL;
1622 DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => input_string
1623 ,key_string => key_string
1624 ,encrypted_string => l_encrypted_string);
1625
1626 RETURN rawtohex(UTL_RAW.CAST_TO_RAW(l_encrypted_string));
1627 END;
1628
1629 --
1630 --------------------------decrypt----------------------
1631 --
1632 FUNCTION decrypt( input_string IN VARCHAR2) RETURN VARCHAR2
1633 IS
1634 l_decrypted_string VARCHAR2(2048);
1635 l_convert_string VARCHAR2(2048);
1636 BEGIN
1637 l_convert_string := hextoraw(input_string);
1638 l_convert_string := UTL_RAW.CAST_TO_VARCHAR2(input_string);
1639
1640 l_decrypted_string := NULL;
1641 DBMS_OBFUSCATION_TOOLKIT.des3decrypt
1642 (input_string => l_convert_string
1646 END;
1643 ,key_string => key_string
1644 ,decrypted_string => l_decrypted_string);
1645 RETURN l_decrypted_string;
1647 --
1648 --------------------------lock_cwb_layout----------------------
1649 --
1650 FUNCTION lock_cwb_layout(p_integrator_code IN Varchar2
1651 ,p_base_layout_code IN VARCHAR2)
1652 RETURN VARCHAR2
1653 IS
1654 CURSOR c_layout IS
1655 SELECT layout_code
1656 FROM bne_layouts_b
1657 WHERE integrator_code = p_integrator_code
1658 AND integrator_app_id = 800
1659 AND application_id = 800
1660 AND layout_code <> p_base_layout_code
1661 AND layout_code NOT IN (SELECT attribute1
1662 FROM ben_transaction
1663 WHERE transaction_type = 'CWBWEBADI'
1664 AND attribute2 IS NOT NULL
1665 AND attribute1 IS NOT NULL
1666 AND DECODE(transaction_type, 'CWBWEBADI', SYSDATE - (to_number(l_layout_lock_time) /(24 * 60))
1667 - TO_DATE(attribute2, 'yyyy/mm/dd:hh:mi'),0) < 0);
1668
1669 l_layout VARCHAR2(200);
1670 l_new_layout_user_name Varchar2(4000);
1671 l_date Varchar2(100);
1672 BEGIN
1673 OPEN c_layout;
1674 FETCH c_layout INTO l_layout;
1675 CLOSE c_layout;
1676
1677 IF l_layout IS NULL THEN
1678 BEGIN
1679 l_date := to_char(sysdate,'yyyymmddhhmi');
1680 l_layout := '__'||to_char(sysdate,'yyyymmddhhmi')||'__';
1681 l_new_layout_user_name := '__'||to_char(sysdate,'yyyymmddhhmi')||'__';
1682 create_cwb_layout( p_layout_code => l_layout
1683 ,p_user_name => l_new_layout_user_name
1684 ,p_base_layout_code => p_base_layout_code);
1685
1686 END;
1687 ELSE
1688 BEGIN
1689 DELETE ben_transaction
1690 WHERE transaction_type = 'CWBWEBADI'
1691 AND attribute1 = l_layout;
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 NULL;
1695 END;
1696
1697 INSERT INTO ben_transaction
1698 (transaction_id
1699 ,transaction_type
1700 ,attribute1
1701 ,attribute2)
1702 VALUES (ben_transaction_s.NEXTVAL
1703 ,'CWBWEBADI'
1704 ,l_layout
1705 ,TO_CHAR(SYSDATE, 'yyyy/mm/dd:hh:mi'));
1706 END IF;
1707
1708
1709
1710 RETURN l_layout;
1711
1712 END;
1713 --
1714 --------------------------unlock_cwb_layout----------------------
1715 --
1716
1717 PROCEDURE unlock_cwb_layout( p_layout_code IN VARCHAR2)
1718 IS
1719 BEGIN
1720 DELETE ben_transaction
1721 WHERE transaction_type = 'CWBWEBADI'
1722 AND attribute1 = p_layout_code;
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 NULL;
1726 END;
1727
1728 --
1729 -------------------------- create_custom_row ----------------------
1730 --
1731 Procedure create_custom_row ( p_key IN VARCHAR2
1732 ,p_integrator_code IN VARCHAR2
1733 ,p_interface_code IN VARCHAR2
1734 ,p_interface_col_code IN VARCHAR2
1735 ,p_display_seq IN Number)
1736 IS
1737
1738 Cursor csr_col_prompt
1739 IS
1740 Select InfColsTl.prompt_left
1741 From bne_interface_cols_b InfCols,
1742 bne_interface_cols_tl InfColsTl
1743 Where InfCols.interface_code = p_interface_code
1744 And InfCols.INTERFACE_COL_NAME = p_interface_col_code
1745 And InfCols.application_id = 800
1746 And InfCols.interface_code = InfColsTl.interface_code
1747 And InfCols.sequence_num = InfColsTl.sequence_num
1748 And InfColsTl.application_id = 800
1749 And InfColsTl.Language = Userenv('LANG');
1750
1751 l_proc Varchar2(72) := g_package||'create_custom_row';
1752 l_col_prompt bne_interface_cols_tl.prompt_left%Type;
1753
1754 Begin
1755 if g_debug then
1756 hr_utility.set_location('Entering '||l_proc,10);
1757 hr_utility.set_location('p_key :'||p_key,30);
1758 hr_utility.set_location('p_integrator_code :'||p_integrator_code,40);
1759 hr_utility.set_location('p_interface_code :'||p_interface_code,50);
1760 end if;
1761
1762 Open csr_col_prompt;
1763 Fetch csr_col_prompt into l_col_prompt;
1764 Close csr_col_prompt;
1765
1766
1767 Insert Into BEN_CUSTOM_REGION_ITEMS
1768 (
1769 REGION_CODE -- task code / integrator code
1770 ,CUSTOM_KEY -- mgr_per_in_ler_id
1771 ,CUSTOM_TYPE -- integrator code
1772 ,ITEM_NAME -- interface_col_name
1773 ,DISPLAY_FLAG -- Y/N
1774 ,LABEL -- interface_prompt_above
1775 ,ORDR_NUM -- Display Order
1776 )
1777 Values
1778 (
1779 p_integrator_code -- task code / integrator code
1780 ,p_key -- mgr_per_in_ler_id
1781 ,p_integrator_code -- integrator code
1785 ,p_display_seq -- Display Order
1782 ,p_interface_col_code -- interface_col_name
1783 ,'N'
1784 ,l_col_prompt -- interface_prompt_above
1786 );
1787
1788 hr_utility.set_location('Entering '||l_proc,10);
1789 Exception
1790 When Others then
1791 hr_utility.set_location('Error :'||substr(sqlerrm,1,50),100);
1792 hr_utility.set_location('Error :'||substr(sqlerrm,51,100),110);
1793 raise;
1794
1795 End create_custom_row;
1796
1797 --
1798 --------------------------chk_entry_in_custom_table----------------------
1799 --
1800 /* Purpose :
1801 This function checks whether data exists in table BEN_CUSTOM_REGION_ITEMS or not.
1802 If data exists in the table then returns 'Y' else 'N'
1803 */
1804
1805 Function chk_entry_in_custom_table( p_key IN VARCHAR2
1806 ,p_integrator_code IN VARCHAR2
1807 ) Return Varchar
1808 IS
1809 Cursor Csr_entry
1810 IS
1811 Select '1'
1812 From BEN_CUSTOM_REGION_ITEMS
1813 Where CUSTOM_TYPE = p_integrator_code
1814 And REGION_CODE = p_integrator_code
1815 And CUSTOM_KEY = p_key;
1816
1817 l_exists Varchar2(1);
1818 l_return_val Varchar2(1) := 'N';
1819 l_proc Varchar2(72) := g_package||'chk_entry_in_custom_table';
1820
1821 BEGIN
1822 hr_utility.set_location('Entering '||l_proc,10);
1823
1824 Open Csr_entry;
1825 Fetch Csr_entry into l_exists;
1826 Close Csr_entry;
1827
1828 -- If entires are not there insert data into table BEN_CUSTOM_REGION_ITEMS
1829 If l_exists is null then
1830 l_return_val := 'N';
1831 Else
1832 l_return_val := 'Y';
1833 End if;
1834
1835 hr_utility.set_location('Entry Exists (Y/N) : '||l_return_val,100);
1836 hr_utility.set_location('Leaving '||l_proc,200);
1837
1838 return l_return_val;
1839
1840 EXCEPTION
1841 WHEN OTHERS THEN
1842 hr_utility.set_location('Error :'||substr(sqlerrm,1,50),100);
1843 hr_utility.set_location('Error :'||substr(sqlerrm,51,100),110);
1844 return 'N';
1845
1846 END chk_entry_in_custom_table;
1847
1848 --
1849 --------------------------manipulate_seleted_data----------------------
1850 --
1851
1852
1853 Procedure manipulate_selected_data( p_key IN VARCHAR2
1854 ,p_integrator_code IN VARCHAR2
1855 ,p_interface_code IN VARCHAR2
1856 ,p_interface_col_code IN VARCHAR2
1857 ,p_display_seq IN Number )
1858 IS
1859
1860
1861 l_proc Varchar2(72) := g_package||'chk_entry_in_custom_table';
1862
1863
1864 Begin
1865 hr_utility.set_location('Entering '||l_proc,10);
1866
1867 -- If data not exists in the Custom Table insert data
1868
1869
1870 create_custom_row( p_key => p_key
1871 ,p_integrator_code => p_integrator_code
1872 ,p_interface_code => p_interface_code
1873 ,p_interface_col_code => p_interface_col_code
1874 ,p_display_seq => p_display_seq);
1875
1876 hr_utility.set_location('Leaving '||l_proc,200);
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 hr_utility.set_location('Error :'||substr(sqlerrm,1,50),100);
1880 hr_utility.set_location('Error :'||substr(sqlerrm,51,100),110);
1881 raise;
1882 End manipulate_selected_data;
1883
1884 --
1885 ----------------------delete_custom_data ---------------------------
1886 --
1887 Procedure delete_custom_data(p_key IN VARCHAR2,
1888 p_integrator_code IN VARCHAR2)
1889 IS
1890 l_exist_in_table Varchar2(1);
1891 BEGIN
1892 l_exist_in_table := chk_entry_in_custom_table( p_key => p_key
1893 ,p_integrator_code => p_integrator_code);
1894
1895 hr_utility.set_location('l_exist_in_table :'||l_exist_in_table,20);
1896
1897
1898 If l_exist_in_table = 'Y' Then
1899 hr_utility.set_location('Data Not exists in custom Table ',25);
1900 Delete From BEN_CUSTOM_REGION_ITEMS
1901 Where CUSTOM_KEY = p_key
1902 And REGION_CODE = p_integrator_code
1903 And CUSTOM_TYPE = p_integrator_code;
1904 End If;
1905 END;
1906
1907 --
1908 --------------------- update_cwb_custom_layout ---------------------
1909 --
1910
1911 Procedure update_cwb_custom_layout( p_key IN VARCHAR2
1912 ,p_integrator_code IN VARCHAR2
1913 ,p_interface_code IN VARCHAR2
1914 ,p_act_layout_code IN VARCHAR2
1915 ,p_base_layout_code IN VARCHAR2
1916 ,p_group_pl_id IN NUMBER Default Null
1917 ,p_lf_evt_ocrd_dt IN DATE Default Null
1918 ,p_download_switch OUT NOCOPY VARCHAR2
1919 )
1920 IS
1921 l_num NUMBER := 0;
1922 l_pl_ws_amt_switch VARCHAR(1) := '1';
1926 l_opt4_ws_amt_switch VARCHAR(1) := '1';
1923 l_opt1_ws_amt_switch VARCHAR(1) := '1';
1924 l_opt2_ws_amt_switch VARCHAR(1) := '1';
1925 l_opt3_ws_amt_switch VARCHAR(1) := '1';
1927 l_perf_switch VARCHAR(1) := '1';
1928 l_rank_switch VARCHAR(1) := '1';
1929 l_cpi_attribute1_switch VARCHAR(1) := '1';
1930 l_cpi_attribute2_switch VARCHAR(1) := '1';
1931 l_cpi_attribute3_switch VARCHAR(1) := '1';
1932 l_cpi_attribute4_switch VARCHAR(1) := '1';
1933 l_cpi_attribute5_switch VARCHAR(1) := '1';
1934 l_cpi_attribute6_switch VARCHAR(1) := '1';
1935 l_cpi_attribute7_switch VARCHAR(1) := '1';
1936 l_cpi_attribute8_switch VARCHAR(1) := '1';
1937 l_cpi_attribute9_switch VARCHAR(1) := '1';
1938 l_cpi_attribute10_switch VARCHAR(1) := '1';
1939 l_cpi_attribute11_switch VARCHAR(1) := '1';
1940 l_cpi_attribute12_switch VARCHAR(1) := '1';
1941 l_cpi_attribute13_switch VARCHAR(1) := '1';
1942 l_cpi_attribute14_switch VARCHAR(1) := '1';
1943 l_cpi_attribute15_switch VARCHAR(1) := '1';
1944 l_cpi_attribute16_switch VARCHAR(1) := '1';
1945 l_cpi_attribute17_switch VARCHAR(1) := '1';
1946 l_cpi_attribute18_switch VARCHAR(1) := '1';
1947 l_cpi_attribute19_switch VARCHAR(1) := '1';
1948 l_cpi_attribute20_switch VARCHAR(1) := '1';
1949 l_cpi_attribute21_switch VARCHAR(1) := '1';
1950 l_cpi_attribute22_switch VARCHAR(1) := '1';
1951 l_cpi_attribute23_switch VARCHAR(1) := '1';
1952 l_cpi_attribute24_switch VARCHAR(1) := '1';
1953 l_cpi_attribute25_switch VARCHAR(1) := '1';
1954 l_cpi_attribute26_switch VARCHAR(1) := '1';
1955 l_cpi_attribute27_switch VARCHAR(1) := '1';
1956 l_cpi_attribute28_switch VARCHAR(1) := '1';
1957 l_cpi_attribute29_switch VARCHAR(1) := '1';
1958 l_cpi_attribute30_switch VARCHAR(1) := '1';
1959
1960 Cursor csr_bne_data
1961 IS
1962 Select infCols.interface_col_name inf_col_name
1963 ,infCols.sequence_num inf_Seq_Num
1964 ,layCols.SEQUENCE_NUM Dis_Seq_Num
1965 From bne_interfaces_b inf,
1966 bne_interface_cols_b infCols,
1967 bne_layout_cols layCols,
1968 bne_layout_blocks_b layBlk
1969 Where inf.integrator_code = p_integrator_code
1970 And inf.integrator_app_id = 800
1971 And inf.interface_code = p_interface_code
1972 And inf.application_id = 800
1973 And inf.interface_code = infCols.interface_code
1974 And infCols.application_id = 800
1975 And infCols.sequence_num = layCols.INTERFACE_SEQ_NUM
1976 And layCols.layout_code = p_base_layout_code
1977 And layCols.application_id = 800
1978 And layCols.layout_code = layBlk.layout_code
1979 And layCols.block_id = layBlk.block_id
1980 And layBlk.STYLE_CLASS = 'BNE_LINES'
1981 And layBlk.application_id = 800;
1982
1983 Cursor csr_cust_data
1984 IS
1985 Select cust.ITEM_NAME inf_col_name
1986 ,infCols.SEQUENCE_NUM inf_Seq_Num
1987 ,cust.ORDR_NUM Dis_Seq_Num
1988 From BEN_CUSTOM_REGION_ITEMS cust,
1989 Bne_interface_cols_b infCols
1990 Where cust.CUSTOM_KEY = p_key
1991 And cust.REGION_CODE = p_integrator_code
1992 And cust.CUSTOM_TYPE = p_integrator_code
1993 And cust.ITEM_NAME = infCols.INTERFACE_COL_NAME
1994 And infCols.interface_code = p_interface_code
1995 And infCols.application_id = 800;
1996
1997
1998
1999 cursor do_not_disturb is
2000 select interface_seq_num inf_Seq_Num,
2001 2000+rownum Dis_Seq_Num
2002 from bne_layout_cols
2003 where interface_seq_num in (130,131,132,133,134)
2004 and layout_code = p_base_layout_code
2005 and interface_code = p_interface_code
2006 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2007 and application_id = 800;
2008
2009 cursor group_pl_key is
2010 select decode (col1.interface_seq_num, 7, 1,
2011 159,2,
2012 165,3,
2013 9,4,
2014 5,5,
2015 164,6
2016 ),
2017 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2018 from bne_layout_cols col1,
2019 bne_layout_cols col2
2020 where col1.interface_seq_num in (7,159,165,9,5,164)
2021 and col1.layout_code = p_base_layout_code
2022 and col1.interface_code = p_interface_code
2023 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2024 and col1.application_id = 800
2025 and col1.interface_code = col2.interface_code
2026 and col2.application_id = 800
2027 and col2.interface_seq_num = 12
2028 and col2.layout_code = p_act_layout_code
2029 order by 1;
2030
2031 cursor group_opt1_key is
2032 select decode (col1.interface_seq_num, 25, 1,
2033 160,2,
2034 166,3,
2035 27,4
2036 ),
2037 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2038 from bne_layout_cols col1,
2039 bne_layout_cols col2
2040 where col1.interface_seq_num in (25,160,166,27)
2041 and col1.layout_code = p_base_layout_code
2042 and col1.interface_code = col2.interface_code
2043 and col2.interface_seq_num = 30
2044 and col2.interface_code = p_interface_code
2045 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2046 and col2.layout_code = p_act_layout_code
2047 and col1.application_id = 800
2048 and col2.application_id = 800
2049 order by 1;
2050
2051 cursor group_opt2_key is
2052 select decode (col1.interface_seq_num, 43, 1,
2056 ),
2053 161,2,
2054 167,3,
2055 45,4
2057 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2058 from bne_layout_cols col1,
2059 bne_layout_cols col2
2060 where col1.interface_seq_num in (43,161,167,45)
2061 and col1.layout_code = p_base_layout_code
2062 and col1.interface_code = col2.interface_code
2063 and col2.interface_seq_num = 48
2064 and col2.interface_code = p_interface_code
2065 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2066 and col2.layout_code = p_act_layout_code
2067 and col1.application_id = 800
2068 and col2.application_id = 800
2069 order by 1;
2070
2071 cursor group_opt3_key is
2072 select decode (col1.interface_seq_num, 61, 1,
2073 162,2,
2074 168,3,
2075 63,4
2076 ),
2077 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2078 from bne_layout_cols col1,
2079 bne_layout_cols col2
2080 where col1.interface_seq_num in (61,162,168,63)
2081 and col1.layout_code = p_base_layout_code
2082 and col1.interface_code = col2.interface_code
2083 and col2.interface_seq_num = 66
2084 and col2.interface_code = p_interface_code
2085 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2086 and col2.layout_code = p_act_layout_code
2087 and col1.application_id = 800
2088 and col2.application_id = 800
2089 order by 1;
2090
2091 cursor group_opt4_key is
2092 select decode (col1.interface_seq_num, 79, 1,
2093 163,2,
2094 169,3,
2095 81,4
2096 ),
2097 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2098 from bne_layout_cols col1,
2099 bne_layout_cols col2
2100 where col1.interface_seq_num in (79,163,169,81)
2101 and col1.layout_code = p_base_layout_code
2102 and col1.interface_code = col2.interface_code
2103 and col2.interface_seq_num = 84
2104 and col2.interface_code = p_interface_code
2105 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2106 and col2.layout_code = p_act_layout_code
2107 and col1.application_id = 800
2108 and col2.application_id = 800
2109 order by 1;
2110
2111 cursor group_rank_key is
2112 select decode (col1.interface_seq_num, 125, 1,
2113 197,2
2114 ),
2115 col1.interface_seq_num inf_Seq_Num, col2.sequence_num Dis_Seq_Num
2116 from bne_layout_cols col1,
2117 bne_layout_cols col2
2118 where col1.interface_seq_num in (125,197)
2119 and col1.layout_code = p_base_layout_code
2120 and col1.interface_code = col2.interface_code
2121 and col2.interface_seq_num = 3
2122 and col2.interface_code = p_interface_code
2123 and p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2124 and col2.layout_code = p_act_layout_code
2125 and col1.application_id = 800
2126 and col2.application_id = 800
2127 order by 1;
2128
2129 Cursor add_cols IS
2130 (Select interface_seq_num inf_Seq_Num
2131 , sequence_num Dis_Seq_Num
2132 , decode (interface_seq_num, 170, 1,
2133 171,2,
2134 172,3,
2135 173,4,
2136 174,5,
2137 175,6,
2138 176,7,
2139 177,8,
2140 178,9,
2141 179,10,
2142 180,11,
2143 181,12,
2144 182,13,
2145 189,14,
2146 158,15,
2147 194,16,
2148 195,17,
2149 196,18,
2150 198,19,
2151 188,20,
2152 190,21
2153 ) order_in_layout
2154 From bne_layout_cols
2155 Where interface_code = p_interface_code
2156 And p_interface_code = 'BEN_CWB_WRK_SHT_INTF'
2157 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)
2158 And layout_code = p_base_layout_code
2159 And application_id = 800)
2160 union
2161 (Select interface_seq_num inf_Seq_Num
2162 ,sequence_num Dis_Seq_Num
2163 , interface_seq_num order_in_layout
2164 From bne_layout_cols
2165 Where interface_code = p_interface_code
2166 And p_interface_code = 'BEN_CWB_BGT_SHT_INTF'
2167 And interface_seq_num in (37,40,41,42,43,44,45,46,47,48,49,50,51,52,53)
2168 And layout_code = p_base_layout_code
2169 And application_id = 800
2170 )
2171 union
2172 (Select interface_seq_num inf_Seq_Num
2173 ,sequence_num Dis_Seq_Num
2174 , interface_seq_num order_in_layout
2175 From bne_layout_cols
2176 Where interface_code = p_interface_code
2177 And p_interface_code = 'BEN_CWB_SUMM_DIR_REP_INTF'
2178 And interface_seq_num in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)
2179 And layout_code = p_base_layout_code
2180 And application_id = 800
2181 ) Order by 3 ;
2182 l_application_id Number := 800;
2183 l_exist_in_table Varchar2(1);
2184 l_proc Varchar2(72) := g_package||'update_cwb_custom_layout';
2185
2186 BEGIN
2187 If g_debug then
2188 hr_utility.set_location('Entering '||l_proc,10);
2189 End if;
2190
2191 delete_cwb_layout_cols(p_layout_code => p_act_layout_code
2192 ,p_application_id => 800);
2193
2197 hr_utility.set_location('l_exist_in_table :'||l_exist_in_table,20);
2194 l_exist_in_table := chk_entry_in_custom_table( p_key => p_key
2195 ,p_integrator_code => p_integrator_code);
2196 If g_debug then
2198 End if;
2199
2200 -- If data not exists in the custom table
2201 If l_exist_in_table = 'N' Then
2202 For l_bne_row In csr_bne_data
2203 Loop
2204
2205 IF(l_bne_row.inf_Seq_Num = 12) THEN
2206 l_pl_ws_amt_switch := '2';
2207 ELSIF(l_bne_row.inf_Seq_Num = 30) THEN
2208 l_opt1_ws_amt_switch := '2';
2209 ELSIF(l_bne_row.inf_Seq_Num = 48) THEN
2210 l_opt2_ws_amt_switch := '2';
2211 ELSIF(l_bne_row.inf_Seq_Num = 66) THEN
2212 l_opt3_ws_amt_switch := '2';
2213 ELSIF(l_bne_row.inf_Seq_Num = 84) THEN
2214 l_opt4_ws_amt_switch := '2';
2215 ELSIF(l_bne_row.inf_Seq_Num = 151) THEN
2216 l_perf_switch := '2';
2217 ELSIF(l_bne_row.inf_Seq_Num = 3) THEN
2218 l_rank_switch := '2';
2219 ELSIF(l_bne_row.inf_Seq_Num = 200) THEN
2220 l_cpi_attribute1_switch := '2';
2221 ELSIF(l_bne_row.inf_Seq_Num = 201) THEN
2222 l_cpi_attribute2_switch := '2';
2223 ELSIF(l_bne_row.inf_Seq_Num = 202) THEN
2224 l_cpi_attribute3_switch := '2';
2225 ELSIF(l_bne_row.inf_Seq_Num = 203) THEN
2226 l_cpi_attribute4_switch := '2';
2227 ELSIF(l_bne_row.inf_Seq_Num = 204) THEN
2228 l_cpi_attribute5_switch := '2';
2229 ELSIF(l_bne_row.inf_Seq_Num = 205) THEN
2230 l_cpi_attribute6_switch := '2';
2231 ELSIF(l_bne_row.inf_Seq_Num = 206) THEN
2232 l_cpi_attribute7_switch := '2';
2233 ELSIF(l_bne_row.inf_Seq_Num = 207) THEN
2234 l_cpi_attribute8_switch := '2';
2235 ELSIF(l_bne_row.inf_Seq_Num = 208) THEN
2236 l_cpi_attribute9_switch := '2';
2237 ELSIF(l_bne_row.inf_Seq_Num = 209) THEN
2238 l_cpi_attribute10_switch := '2';
2239 ELSIF(l_bne_row.inf_Seq_Num = 210) THEN
2240 l_cpi_attribute11_switch := '2';
2241 ELSIF(l_bne_row.inf_Seq_Num = 211) THEN
2242 l_cpi_attribute12_switch := '2';
2243 ELSIF(l_bne_row.inf_Seq_Num = 212) THEN
2244 l_cpi_attribute13_switch := '2';
2245 ELSIF(l_bne_row.inf_Seq_Num = 213) THEN
2246 l_cpi_attribute14_switch := '2';
2247 ELSIF(l_bne_row.inf_Seq_Num = 214) THEN
2248 l_cpi_attribute15_switch := '2';
2249 ELSIF(l_bne_row.inf_Seq_Num = 215) THEN
2250 l_cpi_attribute16_switch := '2';
2251 ELSIF(l_bne_row.inf_Seq_Num = 216) THEN
2252 l_cpi_attribute17_switch := '2';
2253 ELSIF(l_bne_row.inf_Seq_Num = 217) THEN
2254 l_cpi_attribute18_switch := '2';
2255 ELSIF(l_bne_row.inf_Seq_Num = 218) THEN
2256 l_cpi_attribute19_switch := '2';
2257 ELSIF(l_bne_row.inf_Seq_Num = 219) THEN
2258 l_cpi_attribute20_switch := '2';
2259 ELSIF(l_bne_row.inf_Seq_Num = 220) THEN
2260 l_cpi_attribute21_switch := '2';
2261 ELSIF(l_bne_row.inf_Seq_Num = 221) THEN
2262 l_cpi_attribute22_switch := '2';
2263 ELSIF(l_bne_row.inf_Seq_Num = 222) THEN
2264 l_cpi_attribute23_switch := '2';
2265 ELSIF(l_bne_row.inf_Seq_Num = 223) THEN
2266 l_cpi_attribute24_switch := '2';
2267 ELSIF(l_bne_row.inf_Seq_Num = 224) THEN
2268 l_cpi_attribute25_switch := '2';
2269 ELSIF(l_bne_row.inf_Seq_Num = 225) THEN
2270 l_cpi_attribute26_switch := '2';
2271 ELSIF(l_bne_row.inf_Seq_Num = 226) THEN
2272 l_cpi_attribute27_switch := '2';
2273 ELSIF(l_bne_row.inf_Seq_Num = 227) THEN
2274 l_cpi_attribute28_switch := '2';
2275 ELSIF(l_bne_row.inf_Seq_Num = 228) THEN
2276 l_cpi_attribute29_switch := '2';
2277 ELSIF(l_bne_row.inf_Seq_Num = 229) THEN
2278 l_cpi_attribute30_switch := '2';
2279 END IF;
2280
2281
2282 insert_cwb_layout_cols_row( p_application_id => 800
2283 ,p_base_layout_code => p_base_layout_code
2284 ,p_act_layout_code => p_act_layout_code
2285 ,p_inf_seq => l_bne_row.inf_Seq_Num
2286 ,p_dis_seq => l_bne_row.Dis_Seq_Num * 10
2287 ,p_group_pl_id => p_group_pl_id
2288 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2289 End Loop;
2290 Else
2291 For l_cust_row In csr_cust_data
2292 Loop
2293 IF(l_cust_row.inf_Seq_Num = 12) THEN
2294 l_pl_ws_amt_switch := '2';
2295 ELSIF(l_cust_row.inf_Seq_Num = 30) THEN
2296 l_opt1_ws_amt_switch := '2';
2297 ELSIF(l_cust_row.inf_Seq_Num = 48) THEN
2298 l_opt2_ws_amt_switch := '2';
2299 ELSIF(l_cust_row.inf_Seq_Num = 66) THEN
2300 l_opt3_ws_amt_switch := '2';
2301 ELSIF(l_cust_row.inf_Seq_Num = 84) THEN
2302 l_opt4_ws_amt_switch := '2';
2303 ELSIF(l_cust_row.inf_Seq_Num = 151) THEN
2307 ELSIF(l_cust_row.inf_Seq_Num = 200) THEN
2304 l_perf_switch := '2';
2305 ELSIF(l_cust_row.inf_Seq_Num = 3) THEN
2306 l_rank_switch := '2';
2308 l_cpi_attribute1_switch := '2';
2309 ELSIF(l_cust_row.inf_Seq_Num = 201) THEN
2310 l_cpi_attribute2_switch := '2';
2311 ELSIF(l_cust_row.inf_Seq_Num = 202) THEN
2312 l_cpi_attribute3_switch := '2';
2313 ELSIF(l_cust_row.inf_Seq_Num = 203) THEN
2314 l_cpi_attribute4_switch := '2';
2315 ELSIF(l_cust_row.inf_Seq_Num = 204) THEN
2316 l_cpi_attribute5_switch := '2';
2317 ELSIF(l_cust_row.inf_Seq_Num = 205) THEN
2318 l_cpi_attribute6_switch := '2';
2319 ELSIF(l_cust_row.inf_Seq_Num = 206) THEN
2320 l_cpi_attribute7_switch := '2';
2321 ELSIF(l_cust_row.inf_Seq_Num = 207) THEN
2322 l_cpi_attribute8_switch := '2';
2323 ELSIF(l_cust_row.inf_Seq_Num = 208) THEN
2324 l_cpi_attribute9_switch := '2';
2325 ELSIF(l_cust_row.inf_Seq_Num = 209) THEN
2326 l_cpi_attribute10_switch := '2';
2327 ELSIF(l_cust_row.inf_Seq_Num = 210) THEN
2328 l_cpi_attribute11_switch := '2';
2329 ELSIF(l_cust_row.inf_Seq_Num = 211) THEN
2330 l_cpi_attribute12_switch := '2';
2331 ELSIF(l_cust_row.inf_Seq_Num = 212) THEN
2332 l_cpi_attribute13_switch := '2';
2333 ELSIF(l_cust_row.inf_Seq_Num = 213) THEN
2334 l_cpi_attribute14_switch := '2';
2335 ELSIF(l_cust_row.inf_Seq_Num = 214) THEN
2336 l_cpi_attribute15_switch := '2';
2337 ELSIF(l_cust_row.inf_Seq_Num = 215) THEN
2338 l_cpi_attribute16_switch := '2';
2339 ELSIF(l_cust_row.inf_Seq_Num = 216) THEN
2340 l_cpi_attribute17_switch := '2';
2341 ELSIF(l_cust_row.inf_Seq_Num = 217) THEN
2342 l_cpi_attribute18_switch := '2';
2343 ELSIF(l_cust_row.inf_Seq_Num = 218) THEN
2344 l_cpi_attribute19_switch := '2';
2345 ELSIF(l_cust_row.inf_Seq_Num = 219) THEN
2346 l_cpi_attribute20_switch := '2';
2347 ELSIF(l_cust_row.inf_Seq_Num = 220) THEN
2348 l_cpi_attribute21_switch := '2';
2349 ELSIF(l_cust_row.inf_Seq_Num = 221) THEN
2350 l_cpi_attribute22_switch := '2';
2351 ELSIF(l_cust_row.inf_Seq_Num = 222) THEN
2352 l_cpi_attribute23_switch := '2';
2353 ELSIF(l_cust_row.inf_Seq_Num = 223) THEN
2354 l_cpi_attribute24_switch := '2';
2355 ELSIF(l_cust_row.inf_Seq_Num = 224) THEN
2356 l_cpi_attribute25_switch := '2';
2357 ELSIF(l_cust_row.inf_Seq_Num = 225) THEN
2358 l_cpi_attribute26_switch := '2';
2359 ELSIF(l_cust_row.inf_Seq_Num = 226) THEN
2360 l_cpi_attribute27_switch := '2';
2361 ELSIF(l_cust_row.inf_Seq_Num = 227) THEN
2362 l_cpi_attribute28_switch := '2';
2363 ELSIF(l_cust_row.inf_Seq_Num = 228) THEN
2364 l_cpi_attribute29_switch := '2';
2365 ELSIF(l_cust_row.inf_Seq_Num = 229) THEN
2366 l_cpi_attribute30_switch := '2';
2367 END IF;
2368
2369
2370 insert_cwb_layout_cols_row( p_application_id => 800
2371 ,p_base_layout_code => p_base_layout_code
2372 ,p_act_layout_code => p_act_layout_code
2373 ,p_inf_seq => l_cust_row.inf_Seq_Num
2374 ,p_dis_seq => l_cust_row.dis_Seq_Num * 10
2375 ,p_group_pl_id => p_group_pl_id
2376 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2377
2378 End Loop;
2379 End if;
2380
2381 l_num := 1;
2382 -- Plan Group Cols
2383 For l_row In group_pl_key
2384 Loop
2385 insert_cwb_layout_cols_row( p_application_id => 800
2386 ,p_base_layout_code => p_base_layout_code
2387 ,p_act_layout_code => p_act_layout_code
2388 ,p_inf_seq => l_row.inf_Seq_Num
2389 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2390 ,p_group_pl_id => p_group_pl_id
2391 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2392
2393 l_num := l_num + 1;
2394 End Loop;
2395
2396 l_num := 1;
2397 -- Option1 Group Cols
2398 For l_row In group_opt1_key
2399 Loop
2400 insert_cwb_layout_cols_row( p_application_id => 800
2401 ,p_base_layout_code => p_base_layout_code
2402 ,p_act_layout_code => p_act_layout_code
2403 ,p_inf_seq => l_row.inf_Seq_Num
2404 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2405 ,p_group_pl_id => p_group_pl_id
2406 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
2407
2408 l_num := l_num + 1;
2409 End Loop;
2410 -- Option2 Group Cols
2411 For l_row In group_opt2_key
2412 Loop
2413 insert_cwb_layout_cols_row( p_application_id => 800
2414 ,p_base_layout_code => p_base_layout_code
2415 ,p_act_layout_code => p_act_layout_code
2419 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2416 ,p_inf_seq => l_row.inf_Seq_Num
2417 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2418 ,p_group_pl_id => p_group_pl_id
2420
2421 l_num := l_num + 1;
2422 End Loop;
2423 -- Option3 Group Cols
2424 l_num := 1;
2425 For l_row In group_opt3_key
2426 Loop
2427 insert_cwb_layout_cols_row( p_application_id => 800
2428 ,p_base_layout_code => p_base_layout_code
2429 ,p_act_layout_code => p_act_layout_code
2430 ,p_inf_seq => l_row.inf_Seq_Num
2431 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2432 ,p_group_pl_id => p_group_pl_id
2433 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2434
2435 l_num := l_num + 1;
2436 End Loop;
2437 l_num := 1;
2438 -- Option4 Group Cols
2439 For l_row In group_opt4_key
2440 Loop
2441 insert_cwb_layout_cols_row( p_application_id => 800
2442 ,p_base_layout_code => p_base_layout_code
2443 ,p_act_layout_code => p_act_layout_code
2444 ,p_inf_seq => l_row.inf_Seq_Num
2445 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2446 ,p_group_pl_id => p_group_pl_id
2447 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2448 l_num := l_num + 1;
2449
2450 End Loop;
2451
2452
2453 l_num := 1;
2454 -- Rank Group Cols
2455 For l_row In group_rank_key
2456 Loop
2457 insert_cwb_layout_cols_row( p_application_id => 800
2458 ,p_base_layout_code => p_base_layout_code
2459 ,p_act_layout_code => p_act_layout_code
2460 ,p_inf_seq => l_row.inf_Seq_Num
2461 ,p_dis_seq => l_row.dis_Seq_Num + l_num
2462 ,p_group_pl_id => p_group_pl_id
2463 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2464 l_num := l_num + 1;
2465
2466 End Loop;
2467
2468
2469 l_num := 10;
2470
2471 For l_row In add_cols
2472 Loop
2473 insert_cwb_layout_cols_row( p_application_id => 800
2474 ,p_base_layout_code => p_base_layout_code
2475 ,p_act_layout_code => p_act_layout_code
2476 ,p_inf_seq => l_row.inf_Seq_Num
2477 ,p_dis_seq => l_num
2478 ,p_group_pl_id => p_group_pl_id
2479 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2480
2481 l_num := l_num + 10;
2482
2483 END Loop;
2484
2485 -- Don't distrub Cols
2486 For l_row In do_not_disturb
2487 Loop
2488 insert_cwb_layout_cols_row( p_application_id => 800
2489 ,p_base_layout_code => p_base_layout_code
2490 ,p_act_layout_code => p_act_layout_code
2491 ,p_inf_seq => l_row.inf_Seq_Num
2492 ,p_dis_seq => l_row.dis_Seq_Num
2493 ,p_group_pl_id => p_group_pl_id
2494 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
2495
2496
2497 End Loop;
2498
2499 p_download_switch := l_pl_ws_amt_switch||
2500 l_opt1_ws_amt_switch||
2501 l_opt2_ws_amt_switch||
2502 l_opt3_ws_amt_switch||
2503 l_opt4_ws_amt_switch||
2504 l_perf_switch||
2505 l_rank_switch||
2506 l_cpi_attribute1_switch||
2507 l_cpi_attribute2_switch||
2508 l_cpi_attribute3_switch||
2509 l_cpi_attribute4_switch||
2510 l_cpi_attribute5_switch||
2511 l_cpi_attribute6_switch||
2512 l_cpi_attribute7_switch||
2513 l_cpi_attribute8_switch||
2514 l_cpi_attribute9_switch||
2515 l_cpi_attribute10_switch||
2516 l_cpi_attribute11_switch||
2517 l_cpi_attribute12_switch||
2518 l_cpi_attribute13_switch||
2519 l_cpi_attribute14_switch||
2520 l_cpi_attribute15_switch||
2521 l_cpi_attribute16_switch||
2522 l_cpi_attribute17_switch||
2523 l_cpi_attribute18_switch||
2524 l_cpi_attribute19_switch||
2525 l_cpi_attribute20_switch||
2526 l_cpi_attribute21_switch||
2527 l_cpi_attribute22_switch||
2528 l_cpi_attribute23_switch||
2529 l_cpi_attribute24_switch||
2530 l_cpi_attribute25_switch||
2531 l_cpi_attribute26_switch||
2532 l_cpi_attribute27_switch||
2533 l_cpi_attribute28_switch||
2534 l_cpi_attribute29_switch||
2535 l_cpi_attribute30_switch;
2536
2537 p_download_switch := REPLACE(REPLACE(p_download_switch,'1','0'),'2','1');
2538 p_download_switch := nvl(lpad(int2hex(bin2int(substr(p_download_switch,1,28))),7,0)||
2539 int2hex(bin2int(rpad(substr(p_download_switch,29),28,0))),'0000000');
2540 If g_debug then
2541 hr_utility.set_location('Leaving '||l_proc,10);
2542 END IF;
2543 Exception
2544 When others then
2545 Null;
2546 End update_cwb_custom_layout;
2547
2548 --
2549 ------------------- upsert_webadi_download_records ------------------------
2550 --
2551
2552 Procedure upsert_webadi_download_records(p_session_id IN Varchar2,
2553 p_download_type IN Varchar2,
2554 p_param1 IN Varchar2 default null,
2555 p_param2 IN Varchar2 default null,
2556 p_param3 IN Varchar2 default null,
2557 p_param4 IN Varchar2 default null,
2558 p_param5 IN Varchar2 default null,
2559 p_param6 IN Varchar2 default null,
2560 p_param7 IN Varchar2 default null,
2561 p_param8 IN Varchar2 default null,
2562 p_param9 IN Varchar2 default null,
2563 p_param10 IN Varchar2 default null)
2564 Is
2565 begin
2566 --
2567 --
2568 -- Store the time of download.
2569 --
2570 icx_sec.putSessionAttributeValue(p_session_id => p_session_id,
2571 p_name => p_download_type||'_TIME',
2572 p_value => to_char(sysdate,'YYYYMMDDHH24MISS')
2573 );
2574 --
2575 -- Store the parameter.
2576 --
2577 icx_sec.putSessionAttributeValue(p_session_id => p_session_id,
2578 p_name => p_download_type,
2579 p_value => p_param1
2580 );
2581
2582 --
2583 --
2584 End upsert_webadi_download_records;
2585 --
2586 --
2587
2588
2589 --
2590 ---------------Utility functions for number conversions-------------
2591 --
2592 FUNCTION bin2int (bin VARCHAR2)
2593 RETURN PLS_INTEGER IS
2594 len PLS_INTEGER := LENGTH(bin);
2595 BEGIN
2596 IF NVL(len,1) = 1 THEN
2597 RETURN bin;
2598 ELSE RETURN
2599 2 * bin2int(SUBSTR(bin,1,len-1)) + SUBSTR(bin,-1);
2600 END IF;
2601 END bin2int;
2602
2603 FUNCTION int2bin(int PLS_INTEGER)
2604 RETURN VARCHAR2 IS
2605 BEGIN
2606 hr_utility.set_location('int2bin:'||int, 300);
2607 IF int > 0 THEN
2608 RETURN int2bin(TRUNC(int/2))||
2609 SUBSTR('01',MOD(int,2)+1,1);
2610 ELSE
2611 RETURN NULL;
2612 END IF;
2613 END int2bin;
2614
2615 FUNCTION hex2int(hex VARCHAR2)
2616 RETURN PLS_INTEGER IS
2617 len PLS_INTEGER := LENGTH(hex);
2618 BEGIN
2619 hr_utility.set_location('hex2int:'||hex, 300);
2620 IF NVL(len,1) = 1 THEN
2621 RETURN INSTR('0123456789ABCDEF',hex) - 1;
2622 ELSE
2623 hr_utility.set_location('hex2int length:'||len, 300);
2624 RETURN 16 * hex2int(SUBSTR(hex,1,len-1)) +
2625 INSTR('0123456789ABCDEF',SUBSTR(hex,-1)) - 1;
2626 END IF;
2627 END hex2int;
2628
2629 FUNCTION int2hex(n PLS_INTEGER)
2630 RETURN VARCHAR2 IS
2631 BEGIN
2632 IF n > 0 THEN
2633 RETURN int2hex(TRUNC(n/16))||
2634 SUBSTR('0123456789ABCDEF',MOD(n,16)+1,1);
2635 ELSE
2636 RETURN NULL;
2637 END IF;
2638 END int2hex;
2639
2640 FUNCTION int2base(int PLS_INTEGER,base PLS_INTEGER)
2641 RETURN VARCHAR2 IS
2642 BEGIN
2643 IF int > 0 THEN
2644 RETURN int2base(TRUNC(int/base),base)||
2645 SUBSTR('0123456789ABCDEF',MOD(int,base)+1,1);
2646 ELSE
2647 RETURN NULL;
2648 END IF;
2649 END int2base;
2650
2651 FUNCTION base2int(num VARCHAR2,base PLS_INTEGER)
2652 RETURN PLS_INTEGER IS
2653 len PLS_INTEGER := LENGTH(num);
2654 BEGIN
2655 IF NVL(len,1) = 1 THEN
2656 RETURN INSTR('0123456789ABCDEF',num) - 1;
2657 ELSE
2658 RETURN base * base2int(SUBSTR(num,1,len-1),base) +
2659 INSTR('0123456789ABCDEF',SUBSTR(num,-1)) - 1;
2660 END IF;
2661 END base2int;
2662 --
2663 --
2664 END ben_cwb_webadi_utils;
2665