[Home] [Help]
PACKAGE BODY: APPS.JTFB_DCF
Source
1 PACKAGE BODY jtfb_dcf AS
2 /* $Header: jtfbdcfb.pls 115.23 2002/02/28 13:10:55 pkm ship $ */
3
4 unexpected_error EXCEPTION;
5
6 PROCEDURE security_update IS
7 ------------------------------------------------------------------------
8 --Created by : Varun Puri
9 --Date created: 12-FEB-2002
10 --
11 --Purpose:
12 -- 1. Removes the orphan DCF menus and their associated menu entries
13 --
14 --Known limitations/enhancements and/or remarks:
15 --
16 --Change History: (who, when, what: NO CREATION RECORDS HERE!)
17 --Who When What
18 ------------------------------------------------------------------------
19
20 l_entry_sequence fnd_menu_entries.entry_sequence%TYPE;
21 l_menu_id fnd_menu_entries.menu_id%TYPE;
22 l_fm_count NUMBER := 0;
23 l_fme_count NUMBER := 0;
24
25 CURSOR c_entry(cp_sub_menu_id fnd_menu_entries.sub_menu_id%TYPE) IS
26 SELECT menu_id,
27 entry_sequence
28 FROM fnd_menu_entries
29 WHERE sub_menu_id = cp_sub_menu_id;
30
31 CURSOR c_menus IS
32 SELECT fm.menu_id menu_id
33 FROM fnd_menus fm
34 WHERE fm.menu_name like 'DCF_%' AND
35 NOT EXISTS (
36 SELECT 1 FROM fnd_menu_entries
37 WHERE menu_id = fm.menu_id );
38
39 BEGIN
40
41 -- 1. Delete the DCF Menus with no entries
42 FOR mrec IN c_menus LOOP
43 -- Delete the menu entries referencing this menu
44 OPEN c_entry(mrec.menu_id);
45 LOOP
46 FETCH c_entry INTO l_menu_id, l_entry_sequence;
47 EXIT WHEN c_entry%NOTFOUND;
48 fnd_menu_entries_pkg.delete_row(x_menu_id => l_menu_id,
49 x_entry_sequence => l_entry_sequence);
50 l_fme_count := l_fme_count + 1;
51 END LOOP;
52 CLOSE c_entry;
53 -- Delete the DCF Menu
54 fnd_menus_pkg.delete_row(x_menu_id => mrec.menu_id);
55 l_fm_count := l_fm_count + 1;
56 END LOOP;
57
58 -- dbms_output.put_line(to_char(l_fm_count)||' '||'DCF Menus Deleted');
59 -- dbms_output.put_line(to_char(l_fme_count)||' '||'DCF Menus Entries Deleted');
60
61 COMMIT;
62
63 EXCEPTION
64 WHEN OTHERS THEN
65 IF c_entry%ISOPEN THEN
66 CLOSE c_entry;
67 END IF;
68 RAISE unexpected_error;
69 END security_update;
70
71 ---------------------------------------------------------------------------------
72 -- History:
73 -- 15-AUG-2001 Varun Puri CREATED
74 --
75 -- This function returns the ICX Session ID
76 --
77 -- Returns: ICX Session ID
78 ---------------------------------------------------------------------------------
79 FUNCTION get_icx_session_id RETURN NUMBER IS
80 BEGIN
81 return icx_sec.g_session_id;
82 END get_icx_session_id;
83
84 ---------------------------------------------------------------------------------
85 -- History:
86 -- 28-JUN-2001 Varun Puri CREATED
87 --
88 -- This function returns the parameter value of a parameter from parameter string.
89 -- It takes parameter string and parameter name as input. Parameter separator and
90 -- value separator too could be passed to this function. Default values are '&'
91 -- and '='.
92 --
93 -- Sample Usage:
94 -- SELECT jtfb_util.get_parameter_value('p1=v1',p1) from dual
95 -- Returns: v1
96 ---------------------------------------------------------------------------------
97 function get_parameter_value(p_param_str varchar2,
98 p_param_name varchar2,
99 p_param_sep varchar2 default '&',
100 p_value_sep varchar2 default '=')
101 return varchar2 is
102 x_name_end number;
103 x_value_end number;
104 x_param_str_len number;
105 x_value_sep_len number;
106 x_param_sep_len number;
107 x_param_name varchar2(80);
108 x_param_val varchar2(80);
109 begin
110
111 IF (p_param_str IS NULL) THEN
112 return NULL;
113 END IF;
114
115 x_param_str_len := length(p_param_str);
116 x_value_sep_len := length(p_value_sep);
117 x_param_sep_len := length(p_param_sep);
118 x_param_val := null;
119
120 x_name_end := instr(p_param_str,p_value_sep);
121 x_param_name := substr(p_param_str,1, x_name_end-1);
122 x_value_end := instr(p_param_str,p_param_sep);
123
124 if ( x_param_name = p_param_name AND x_value_end=0) then
125 x_param_val := substr(p_param_str,x_name_end+x_value_sep_len);
126 return(x_param_val);
127 elsif ( x_param_name <> p_param_name AND x_value_end=0) then
128 return('NOT_FOUND');
129 elsif ( x_param_name = p_param_name AND x_value_end<>0) then
130 x_param_val := substr(p_param_str,x_name_end+x_value_sep_len,
131 x_value_end-x_name_end-x_value_sep_len);
132 return(x_param_val);
133 else
134 return(get_parameter_value(substr(p_param_str,x_value_end+1), p_param_name,
135 p_param_sep,
136 p_value_sep));
137 end if;
138 end get_parameter_value;
139
140
141 -------------------------------------------------------------------------
142 -- History:
143 -- 28-JUN-2001 Varun Puri CREATED
144 --
145 -- Returns the count of tokens, given the delimiter string
146 -- This function is used by get_multiselect_value
147 --
148 -- Sample Usage:
149 -- SELECT jtfb_util.get_multiselect_count('AB~~CD~~EF~~GH','~~') from dual
150 -- Returns: 4
151 --------------------------------------------------------------------------
152 FUNCTION get_multiselect_count(p_param_str VARCHAR2,
153 p_multi_sep VARCHAR2 default '~~') RETURN NUMBER IS
154 l_param_str_len NUMBER;
155 l_multi_sep_len NUMBER;
156 l_sep_pos NUMBER;
157 BEGIN
158 l_param_str_len := LENGTH(p_param_str);
159 l_multi_sep_len := LENGTH(p_multi_sep);
160 l_sep_pos := INSTR(p_param_str,p_multi_sep);
161 IF (l_sep_pos = 0) THEN
162 return 1;
163 ELSE
164 return 1+get_multiselect_count(SUBSTR(p_param_str,l_sep_pos+l_multi_sep_len),p_multi_sep);
165 END IF;
166
167 END get_multiselect_count;
168
169 -------------------------------------------------------------------------
170 -- History:
171 -- 28-JUN-2001 Varun Puri CREATED
172 --
173 -- Returns the nth value of a delimiter seperated string
174 --
175 -- Sample Usage:
176 -- SELECT jtfb_util.get_multiselect_value('AB~~CD~~EF~~GH',2,'~~') from dual
177 -- Returns: CD
178 --------------------------------------------------------------------------
179 FUNCTION get_multiselect_value(p_param_str VARCHAR2,
180 pos NUMBER,
181 p_multi_sep VARCHAR2 default '~~') RETURN VARCHAR2 IS
182 l_param_str_len NUMBER;
183 l_multi_sep_len NUMBER;
184 l_sep_pos1 NUMBER;
185 l_sep_pos2 NUMBER;
186 l_count NUMBER;
187 BEGIN
188 l_param_str_len := LENGTH(p_param_str);
189 l_multi_sep_len := LENGTH(p_multi_sep);
190 l_count := get_multiselect_count(p_param_str, p_multi_sep);
191
192 IF (pos=1) THEN
193 l_sep_pos1 := INSTR(p_param_str,p_multi_sep);
194 IF (l_sep_pos1=0) THEN
195 -- No multi seperator found, return the original string as value
196 return (p_param_str);
197 ELSE
198 return SUBSTR(p_param_str,1,l_sep_pos1-1);
199 END IF;
200 ELSIF (pos=l_count) THEN
201 l_sep_pos1 := INSTR(p_param_str,p_multi_sep,1,l_count-1);
202 return SUBSTR(p_param_str,l_sep_pos1+l_multi_sep_len);
203 ELSE
204 l_sep_pos1 := INSTR(p_param_str,p_multi_sep,1,pos-1);
205 l_sep_pos2 := INSTR(p_param_str,p_multi_sep,1,pos);
206 return SUBSTR(p_param_str,l_sep_pos1+l_multi_sep_len,l_sep_pos2-(l_sep_pos1+l_multi_sep_len));
207 END IF;
208
209 END get_multiselect_value;
210
211
212 procedure copy(
213 source_region_code in varchar2
214 , target_region_code in varchar2
215 ) is
216
217 cursor c_regions is
218 select * from ak_regions_vl
219 where region_code = source_region_code;
220
221 cursor c_region_items is
222 select * from ak_region_items_vl
223 where region_code = source_region_code;
224
225 l_rowid varchar2(200);
226 l_region_rec c_regions%rowtype;
227 l_found boolean;
228 e_invalid_region_code exception;
229
230 begin
231
232 if (source_region_code = target_region_code
233 or source_region_code is null
234 or target_region_code is null)
235 then
236 raise e_invalid_region_code;
237 end if;
238
239 l_found := false;
240 open c_regions;
241 fetch c_regions into l_region_rec;
242 l_found := c_regions%found;
243 close c_regions;
244
245 if (l_found)
246 then
247 jtfb_ak_regions_pkg.insert_row (
248 x_rowid => l_rowid
249 , x_region_application_id => l_region_rec.region_application_id
250 , x_region_code => target_region_code
251 , x_database_object_name => l_region_rec.database_object_name
252 , x_region_style => l_region_rec.region_style
253 , x_num_columns => l_region_rec.num_columns
254 , x_icx_custom_call => l_region_rec.icx_custom_call
255 , x_name => target_region_code
256 , x_description => l_region_rec.description
257 , x_region_defaulting_api_pkg => l_region_rec.region_defaulting_api_pkg
258 , x_region_defaulting_api_proc => l_region_rec.region_defaulting_api_proc
259 , x_region_validation_api_pkg => l_region_rec.region_validation_api_pkg
260 , x_region_validation_api_proc => l_region_rec.region_validation_api_proc
261 , x_appl_module_object_type => null
262 , x_num_rows_display => l_region_rec.num_rows_display
263 , x_region_object_type => l_region_rec.region_object_type
264 , x_image_file_name => l_region_rec.image_file_name
265 , x_isform_flag => l_region_rec.isform_flag
266 , x_help_target => l_region_rec.help_target
267 , x_style_sheet_filename => l_region_rec.style_sheet_filename
268 , x_version => l_region_rec.version
269 , x_applicationmodule_usage_name => l_region_rec.applicationmodule_usage_name
270 , x_add_indexed_children => l_region_rec.add_indexed_children
271 , x_stateful_flag => l_region_rec.stateful_flag
272 , x_function_name => target_region_code || '_' ||
273 to_char(l_region_rec.region_application_id)
274 , x_children_view_usage_name => l_region_rec.children_view_usage_name
275 , x_creation_date => l_region_rec.creation_date
276 , x_created_by => l_region_rec.created_by
277 , x_last_update_date => l_region_rec.last_update_date
278 , x_last_updated_by => l_region_rec.last_updated_by
279 , x_last_update_login => l_region_rec.last_update_login
280 , x_attribute_category => l_region_rec.attribute_category
281 , x_attribute1 => l_region_rec.attribute1
282 , x_attribute2 => l_region_rec.attribute2
283 , x_attribute3 => l_region_rec.attribute3
284 , x_attribute4 => l_region_rec.attribute4
285 , x_attribute5 => l_region_rec.attribute5
286 , x_attribute6 => l_region_rec.attribute6
287 , x_attribute7 => l_region_rec.attribute7
288 , x_attribute8 => l_region_rec.attribute8
289 , x_attribute9 => l_region_rec.attribute9
290 , x_attribute10 => l_region_rec.attribute10
291 , x_attribute11 => l_region_rec.attribute11
292 , x_attribute12 => l_region_rec.attribute12
293 , x_attribute13 => l_region_rec.attribute13
294 , x_attribute14 => l_region_rec.attribute14
295 , x_attribute15 => l_region_rec.attribute15
296 );
297
298 -- dbms_output.put_line('Created Region');
299
300 for l_rec in c_region_items
301 loop
302 jtfb_ak_region_items_pkg.insert_row (
303 x_rowid => l_rowid
304 , x_region_application_id => l_rec.region_application_id
305 , x_region_code => target_region_code
306 , x_attribute_application_id => l_rec.attribute_application_id
307 , x_attribute_code => l_rec.attribute_code
308 , x_display_sequence => l_rec.display_sequence
309 , x_node_display_flag => l_rec.node_display_flag
310 , x_node_query_flag => l_rec.node_query_flag
311 , x_attribute_label_length => l_rec.attribute_label_length
312 , x_bold => l_rec.bold
313 , x_italic => l_rec.italic
314 , x_vertical_alignment => l_rec.vertical_alignment
315 , x_horizontal_alignment => l_rec.horizontal_alignment
316 , x_item_style => l_rec.item_style
317 , x_object_attribute_flag => l_rec.object_attribute_flag
318 , x_attribute_label_long => l_rec.attribute_label_long
319 , x_description => l_rec.description
320 , x_security_code => l_rec.security_code
321 , x_update_flag => l_rec.update_flag
322 , x_required_flag => l_rec.required_flag
323 , x_display_value_length => l_rec.display_value_length
324 , x_lov_region_application_id => l_rec.lov_region_application_id
325 , x_lov_region_code => l_rec.lov_region_code
326 , x_lov_foreign_key_name => l_rec.lov_foreign_key_name
327 , x_lov_attribute_application_id => l_rec.lov_attribute_application_id
328 , x_lov_attribute_code => l_rec.lov_attribute_code
329 , x_lov_default_flag => l_rec.lov_default_flag
330 , x_region_defaulting_api_pkg => l_rec.region_defaulting_api_pkg
331 , x_region_defaulting_api_proc => l_rec.region_defaulting_api_proc
332 , x_region_validation_api_pkg => l_rec.region_validation_api_pkg
333 , x_region_validation_api_proc => l_rec.region_validation_api_proc
334 , x_order_sequence => l_rec.order_sequence
335 , x_order_direction => l_rec.order_direction
336 , x_default_value_varchar2 => l_rec.default_value_varchar2
337 , x_default_value_number => l_rec.default_value_number
338 , x_default_value_date => l_rec.default_value_date
339 , x_item_name => l_rec.item_name
340 , x_display_height => l_rec.display_height
341 , x_submit => l_rec.submit
342 , x_encrypt => l_rec.encrypt
343 , x_view_usage_name => l_rec.view_usage_name
344 , x_view_attribute_name => l_rec.view_attribute_name
345 , x_css_class_name => l_rec.css_class_name
346 , x_css_label_class_name => l_rec.css_label_class_name
347 , x_url => l_rec.url
348 , x_poplist_viewobject => l_rec.poplist_viewobject
349 , x_poplist_display_attribute => l_rec.poplist_display_attribute
350 , x_poplist_value_attribute => l_rec.poplist_value_attribute
351 , x_image_file_name => l_rec.image_file_name
352 , x_nested_region_code => l_rec.nested_region_code
353 , x_nested_region_appl_id => null
354 , x_menu_name => l_rec.menu_name
355 , x_flexfield_name => l_rec.flexfield_name
356 , x_flexfield_application_id => l_rec.flexfield_application_id
357 , x_tabular_function_code => l_rec.tabular_function_code
358 , x_tip_type => l_rec.tip_type
359 , x_tip_message_name => l_rec.tip_message_name
360 , x_tip_message_application_id => l_rec.tip_message_application_id
361 , x_flex_segment_list => l_rec.flex_segment_list
362 , x_entity_id => l_rec.entity_id
363 , x_anchor => l_rec.anchor
364 , x_poplist_view_usage_name => l_rec.poplist_view_usage_name
365 , x_sortby_view_attribute_name => l_rec.sortby_view_attribute_name
366 , x_creation_date => l_rec.creation_date
367 , x_created_by => l_rec.created_by
368 , x_last_update_date => l_rec.last_update_date
369 , x_last_updated_by => l_rec.last_updated_by
370 , x_last_update_login => l_rec.last_update_login
371 , x_attribute_category => l_rec.attribute_category
372 , x_attribute1 => l_rec.attribute1
373 , x_attribute2 => l_rec.attribute2
374 , x_attribute3 => l_rec.attribute3
375 , x_attribute4 => l_rec.attribute4
376 , x_attribute5 => l_rec.attribute5
377 , x_attribute6 => l_rec.attribute6
378 , x_attribute7 => l_rec.attribute7
379 , x_attribute8 => l_rec.attribute8
380 , x_attribute9 => l_rec.attribute9
381 , x_attribute10 => l_rec.attribute10
382 , x_attribute11 => l_rec.attribute11
383 , x_attribute12 => l_rec.attribute12
384 , x_attribute13 => l_rec.attribute13
385 , x_attribute14 => l_rec.attribute14
386 , x_attribute15 => l_rec.attribute15
387 );
388 -- dbms_output.put_line(to_char(l_rec.display_sequence));
389 end loop;
390 -- dbms_output.put_line('Created Region Items');
391
392 end if;
393
394 -- dbms_output.put_line('Done... Please commit.');
395
396 exception
397 when e_invalid_region_code then
398 null;
399 -- dbms_output.put_line('Jtfb_Dcf.Copy.e_invalid_region_code: Please enter valid region codes');
400 when others then
401 RAISE;
402 -- dbms_output.put_line('Jtfb_Dcf.Copy.Others: ' || sqlerrm);
403 end copy;
404 --
405 --
406 procedure Lov_Upgrade is
407 cursor c_region_items is
408 select rit.rowid
409 , rit.region_code
410 , rit.attribute_code
411 , rit.lov_region_application_id
412 , rit.attribute7
413 , rit.lov_region_code
414 , rit.attribute8
415 , rit.lov_foreign_key_name
416 , rit.attribute9
417 , rit.lov_attribute_code
418 , rit.attribute10
419 , rit.flex_segment_list
420 from ak_region_items rit, ak_regions rgn
421 where rgn.attribute_category in
422 ('BIN', 'REPORT', 'GRAPH', 'GRAPH_REPORT')
423 and rit.region_code = rgn.region_code
424 and rit.region_application_id = rgn.region_application_id
425 and rit.attribute_category = 'PARAMETER'
426 for update nowait;
427
428 l_flex_segment_list ak_region_items.flex_segment_list%type;
429 l_upgraded boolean := false;
430
431 begin
432
433 -- dbms_output.put_line('Jtfb_Dcf.Lov_Upgrade: Start ...');
434 savepoint Lov_Upgrade;
435
436 for rec in c_region_items
437 loop
438
439 -- dbms_output.put_line(rec.region_code || ', ' ||
440 -- rec.attribute_code || ' ... Done.');
441
442 l_flex_segment_list := rec.flex_segment_list;
443 l_flex_segment_list := replace(l_flex_segment_list, 'lov_foreign_key_name', 'attribute9');
444 l_flex_segment_list := replace(l_flex_segment_list, 'lov_attribute_code', 'attribute10');
445
446 update ak_region_items
447 set lov_region_application_id = null
448 , attribute7 = nvl(attribute7, rec.lov_region_application_id)
449 , lov_region_code = null
450 , attribute8 = nvl(attribute8, rec.lov_region_code)
451 , lov_foreign_key_name = null
452 , attribute9 = nvl(attribute9, rec.lov_foreign_key_name)
453 , lov_attribute_code = null
454 , attribute10 = nvl(attribute10, rec.lov_attribute_code)
455 , flex_segment_list = l_flex_segment_list
456 where rowid = rec.rowid;
457
458 l_upgraded := true;
459
460 end loop;
461
462 -- dbms_output.put_line('Jtfb_Dcf.Lov_Upgrade: End.');
463
464 if (l_upgraded)
465 then
466 -- dbms_output.put_line('Lov_Upgrade completed successfully.');
467 -- dbms_output.put_line('* * * * * * * ');
468 -- dbms_output.put_line('Please Commit.');
469 -- dbms_output.put_line('* * * * * * * ');
470 commit;
471 else
472 -- dbms_output.put_line('No Upgrade Needed.');
473 rollback to Lov_Upgrade;
474 end if;
475
476 exception
477 when others then
478 -- dbms_output.put_line('Jtfb_Dcf.Lov_Upgrade.Others: ' || sqlerrm);
479 rollback to Lov_Upgrade;
480 raise unexpected_error;
481 end Lov_Upgrade;
482 --
483 --
484 procedure Graph_Upgrade is
485
486 l_min_sequence constant number := 401;
487 l_max_sequence constant number := 600;
488 l_attribute_category constant varchar2(150) := 'GRAPH_COLUMN';
489
490 cursor cur_region_items is
491 select rit.rowid
492 , rit.region_code
493 , rit.attribute_code
494 , rit.lov_region_application_id
495 , rit.display_sequence
496 from ak_region_items rit, ak_regions rgn
497 where rgn.attribute_category in
498 ('GRAPH', 'GRAPH_REPORT')
499 and rit.region_code = rgn.region_code
500 and rit.region_application_id = rgn.region_application_id
501 and rit.attribute_category = l_attribute_category
502 and rit.display_sequence in (1, 2, 3, 4)
503 order by rit.region_code, rit.display_sequence
504 for update nowait;
505
506 cursor cur_sequence(c_region_code in varchar2) is
507 select nvl((max(display_sequence) + 1), l_min_sequence) new_sequence
508 from ak_region_items
509 where region_code = c_region_code
510 and attribute_category = l_attribute_category
511 and display_sequence between l_min_sequence and l_max_sequence;
512
513 l_found boolean;
514 sequence_rec cur_sequence%rowtype;
515 e_cur_sequence exception;
516 l_upgraded boolean := false;
517
518 begin
519
520 -- dbms_output.put_line('Jtfb_Dcf.Graph_Upgrade: Start ...');
521
522 savepoint Graph_Upgrade;
523
524 for rec in cur_region_items
525 loop
526
527 l_found := false;
528 open cur_sequence(rec.region_code);
529 fetch cur_sequence into sequence_rec;
530 l_found := cur_sequence%found;
531 close cur_sequence;
532
533 if (not l_found)
534 then
535 raise e_cur_sequence;
536 end if;
537
538 -- dbms_output.put_line(rec.region_code || ', ' ||
539 -- rec.attribute_code || ' ... Done.');
540
541 update ak_region_items
542 set display_sequence = sequence_rec.new_sequence
543 where rowid = rec.rowid;
544 l_upgraded := true;
545
546 end loop;
547
548 -- dbms_output.put_line('Jtfb_Dcf.Graph_Upgrade: End.');
549
550 if (l_upgraded)
551 then
552 -- dbms_output.put_line('Graph_Upgrade completed successfully.');
553 -- dbms_output.put_line('* * * * * * * ');
554 -- dbms_output.put_line('Please Commit.');
555 -- dbms_output.put_line('* * * * * * * ');
556 commit;
557 else
558 -- dbms_output.put_line('No Upgrade Needed.');
559 rollback to Graph_Upgrade;
560 end if;
561
562 exception
563 when e_cur_sequence then
564 if (cur_sequence%isopen)
565 then
566 close cur_sequence;
567 end if;
568 -- dbms_output.put_line('Jtfb_Dcf.Graph_Upgrade.e_cur_sequence: ' ||
569 -- 'Unable to get next Sequence');
570 rollback to Graph_Upgrade;
571 raise unexpected_error;
572
573 when others then
574 if (cur_sequence%isopen)
575 then
576 close cur_sequence;
577 end if;
578 -- dbms_output.put_line('Jtfb_Dcf.Graph_Upgrade.Others: ' || sqlerrm);
579 rollback to Graph_Upgrade;
580 raise unexpected_error;
581 end Graph_Upgrade;
582 --
583 --
584 procedure Multiselect_Upgrade is
585
586 l_attribute_category constant
587 ak_region_items.attribute_category%type := 'PARAMETER';
588 l_item_style constant ak_region_items.item_style%type := 'MULTI_SELECT';
589 l_new_item_style constant ak_region_items.item_style%type := 'DATA';
590 l_upgraded boolean := false;
591
592 cursor cur_region_items is
593 select rit.rowid
594 , rit.attribute_code
595 from ak_region_items rit, ak_regions rgn
596 where rgn.attribute_category in
597 ('BIN', 'REPORT', 'GRAPH', 'GRAPH_REPORT')
598 and rit.region_code = rgn.region_code
599 and rit.region_application_id = rgn.region_application_id
600 and rit.attribute_category = l_attribute_category
601 and rit.item_style = l_item_style
602 for update nowait;
603
604 cursor cur_attributes is
605 select att.rowid
606 , att.attribute_code
607 from ak_region_items rit, ak_regions rgn, ak_attributes att
608 where att.item_style = l_item_style
609 and rit.attribute_code = att.attribute_code
610 and rit.attribute_application_id = rit.attribute_application_id
611 and rit.attribute_category = l_attribute_category
612 and rgn.region_code = rit.region_code
613 and rgn.region_application_id = rit.region_application_id
614 and rgn.attribute_category in
615 ('BIN', 'REPORT', 'GRAPH', 'GRAPH_REPORT')
616 for update nowait;
617
618 begin
619
620 -- dbms_output.put_line('Jtfb_Dcf.Multiselect_Upgrade: Start ...');
621
622 savepoint Multiselect_Upgrade;
623
624 for rit_rec in cur_region_items
625 loop
626 -- dbms_output.put_line('Ak_Region_Items: ' || rit_rec.attribute_code);
627 update ak_region_items
628 set item_style = l_new_item_style
629 where rowid = rit_rec.rowid;
630 l_upgraded := true;
631 end loop;
632
633 for att_rec in cur_attributes
634 loop
635 -- dbms_output.put_line('Ak_Attributes: ' || att_rec.attribute_code);
636 update ak_attributes
637 set item_style = l_new_item_style
638 where rowid = att_rec.rowid;
639 l_upgraded := true;
640 end loop;
641
642 -- dbms_output.put_line('Jtfb_Dcf.Multiselect_Upgrade: End.');
643
644 if (l_upgraded)
645 then
646 -- dbms_output.put_line('Multiselect_Upgrade completed successfully.');
647 -- dbms_output.put_line('* * * * * * * ');
648 -- dbms_output.put_line('Please Commit.');
649 -- dbms_output.put_line('* * * * * * * ');
650 commit;
651 else
652 -- dbms_output.put_line('No Upgrade Needed.');
653 rollback to Multiselect_Upgrade;
654 end if;
655
656 exception
657 when others then
658 -- dbms_output.put_line('Jtfb_Dcf.Multiselect_Upgrade.Others: ' || sqlerrm);
659 rollback to Multiselect_Upgrade;
660 raise unexpected_error;
661 end Multiselect_Upgrade;
662 --
663 --
664 procedure Audit_Columns_Patch(p_param_str varchar2) is
665
666 cursor c_regions is
667 select ar.created_by
668 , ar.last_updated_by
669 , ar.last_update_login
670 from ak_regions ar
671 where ar.attribute_category in ('BIN', 'REPORT', 'GRAPH'
672 , 'GRAPH_REPORT', 'POPLIST')
673 for update of
674 ar.created_by
675 , ar.last_updated_by
676 , ar.last_update_login
677 nowait;
678
679 cursor c_regions_tl is
680 select art.created_by
681 , art.last_updated_by
682 , art.last_update_login
683 from ak_regions_tl art, ak_regions ar
684 where ar.attribute_category in ('BIN', 'REPORT', 'GRAPH'
685 , 'GRAPH_REPORT', 'POPLIST')
686 and art.region_code = ar.region_code
687 and art.region_application_id = ar.region_application_id
688 for update of
689 art.created_by
690 , art.last_updated_by
691 , art.last_update_login
692 nowait;
693
694 cursor c_region_items is
695 select ara.created_by
696 , ara.last_updated_by
697 , ara.last_update_login
698 from ak_region_items ara
699 , ak_regions ar
700 where ar.attribute_category in ('BIN', 'REPORT', 'GRAPH'
701 , 'GRAPH_REPORT', 'POPLIST')
702 and ara.region_code = ar.region_code
703 and ara.region_application_id = ar.region_application_id
704 for update of
705 ara.created_by
706 , ara.last_updated_by
707 , ara.last_update_login
708 nowait;
709
710 cursor c_region_items_tl is
711 select arat.created_by
712 , arat.last_updated_by
713 , arat.last_update_login
714 from ak_region_items_tl arat
715 , ak_region_items ara
716 , ak_regions ar
717 where ar.attribute_category in ('BIN', 'REPORT', 'GRAPH'
718 , 'GRAPH_REPORT', 'POPLIST')
719 and ara.region_code = ar.region_code
720 and ara.region_application_id = ar.region_application_id
721 and arat.region_code = ara.region_code
722 and arat.region_application_id = ara.region_application_id
723 and arat.attribute_code = ara.attribute_code
724 and arat.attribute_application_id = ara.attribute_application_id
725 for update of
726 arat.created_by
727 , arat.last_updated_by
728 , arat.last_update_login
729 nowait;
730
731 l_created_by ak_regions.created_by%type;
732 l_last_updated_by ak_regions.last_updated_by%type;
733 l_last_update_login ak_regions.last_update_login%type;
734 l_patch_applied boolean := false;
735
736 begin
737 fnd_global.apps_initialize(
738 fnd_profile.value('USER_ID')
739 , fnd_profile.value('RESP_ID')
740 , fnd_profile.value('RESP_APPL_ID'));
741
742 l_created_by := fnd_global.user_id;
743 l_last_updated_by := fnd_global.user_id;
744 l_last_update_login := fnd_global.login_id;
745
746 -- dbms_output.put_line('Audit_Columns_Patch: Begin');
747
748 -- Updating ak_regions
749 for art_rec in c_regions
750 loop
751 update ak_regions
752 set created_by = l_created_by
753 , last_updated_by = l_last_updated_by
754 , last_update_login = l_last_update_login
755 where current of c_regions;
756
757 l_patch_applied := true;
758 end loop;
759 commit;
760
761 -- Updating ak_regions_tl
762 for art_rec in c_regions_tl
763 loop
764 update ak_regions_tl
765 set created_by = l_created_by
766 , last_updated_by = l_last_updated_by
767 , last_update_login = l_last_update_login
768 where current of c_regions_tl;
769
770 l_patch_applied := true;
771 end loop;
772 commit;
773
774 -- Updating ak_region_items
775 for rec in c_region_items
776 loop
777 update ak_region_items
778 set created_by = l_created_by
779 , last_updated_by = l_last_updated_by
780 , last_update_login = l_last_update_login
781 where current of c_region_items;
782
783 l_patch_applied := true;
784 end loop;
785 commit;
786
787 -- Updating ak_region_items_tl
788 for rec in c_region_items_tl
789 loop
790 update ak_region_items_tl
791 set created_by = l_created_by
792 , last_updated_by = l_last_updated_by
793 , last_update_login = l_last_update_login
794 where current of c_region_items_tl;
795
796 l_patch_applied := true;
797 end loop;
798 commit;
799
800 /*
801 if (l_patch_applied)
802 then
803 -- dbms_output.put_line('Audit_Columns_Patch: Patch applied successfully');
804 else
805 -- dbms_output.put_line('Audit_Columns_Patch: Patch not needed');
806 end if;
807 -- dbms_output.put_line('Audit_Columns_Patch: Done');
808 */
809
810 exception
811 when others then
812 -- dbms_output.put_line('Others: ' || sqlerrm);
813 raise unexpected_error;
814 end Audit_Columns_Patch;
815 --
816 --
817 end jtfb_dcf;