[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_UPGRADE_UTILITIES
Source
1 PACKAGE BODY fnd_flex_upgrade_utilities AS
2 /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
3
4
5 g_package_name VARCHAR2(10) := 'UPU.';
6 g_internal_message VARCHAR2(32000);
7 g_flag_messaging BOOLEAN := TRUE;
8
9 g_mask_new VARCHAR2(80) := 'YYYY/MM/DD HH24:MI:SS';
10
11
12 g_mask_5 VARCHAR2(80) := 'HH24:MI';
13 g_mask_8 VARCHAR2(80) := 'HH24:MI:SS';
14 g_mask_9_8 VARCHAR2(80) := 'DD-MM-RR';
15 g_mask_9 VARCHAR2(80) := 'DD-MON-RR';
16 g_mask_11_10 VARCHAR2(80) := 'DD-MM-YYYY';
17 g_mask_11 VARCHAR2(80) := 'DD-MON-YYYY';
18 g_mask_15_14 VARCHAR2(80) := 'DD-MM-RR HH24:MI';
19 g_mask_15 VARCHAR2(80) := 'DD-MON-RR HH24:MI';
20 g_mask_17_16 VARCHAR2(80) := 'DD-MM-YYYY HH24:MI';
21 g_mask_17 VARCHAR2(80) := 'DD-MON-YYYY HH24:MI';
22 g_mask_18_17 VARCHAR2(80) := 'DD-MM-RR HH24:MI:SS';
23 g_mask_18 VARCHAR2(80) := 'DD-MON-RR HH24:MI:SS';
24 g_mask_20_19 VARCHAR2(80) := 'DD-MM-YYYY HH24:MI:SS';
25 g_mask_19 VARCHAR2(80) := 'YYYY/MM/DD HH24:MI:SS';
26 g_mask_20 VARCHAR2(80) := 'DD-MON-YYYY HH24:MI:SS';
27
28 chr_newline VARCHAR2(8) := fnd_global.newline;
29
30 g_block_size INTEGER := 100000; -- used in app table upgrades.
31
32 -- Concanical Decimal Separator
33 g_cs VARCHAR2(5) := '.';
34
35 -- Decimal Separator;
36 g_ds VARCHAR2(5) := '.';
37
38 -- Group Separator;
39 g_gs VARCHAR2(5) := ',';
40
41 -- nls_numeric_characters;
42 g_nls_chars VARCHAR2(10) := g_ds || g_gs;
43
44 -- CP Log File Indentation.
45 g_cp_numof_errors NUMBER := 0; /* Number of errors in one vset upgrade. */
46 g_cp_indent NUMBER := 1; /* Indentation in Log File. */
47 g_line_size NUMBER := 240; /* Maximum line size. */
48 g_cp_max_indent NUMBER := 80; /* Maximum Indentation. Used in headers. */
49
50 -- Error message length
51 g_error_length NUMBER := 2000;
52
53 -- Maximum Number of Errors for a given TABLE.COLUMN update.
54 g_max_numof_errors NUMBER := 1000;
55
56 -- Upgrade modes.
57 g_number_mode VARCHAR2(10) := 'N';
58 g_date_mode VARCHAR2(10) := 'X';
59 g_datetime_mode VARCHAR2(10) := 'Y';
60 g_session_mode VARCHAR2(100) := 'NOT_SET';
61
62 TYPE who_rec_type IS RECORD
63 (session_mode VARCHAR2(100),
64 creation_date fnd_id_flexs.creation_date%TYPE,
65 created_by fnd_id_flexs.created_by%TYPE,
66 last_update_date fnd_id_flexs.last_update_date%TYPE,
67 last_updated_by fnd_id_flexs.last_updated_by%TYPE,
68 last_update_login fnd_id_flexs.last_update_login%TYPE);
69
70 SUBTYPE vset_rec_type IS fnd_flex_value_sets%ROWTYPE;
71
72 TYPE cursor_type IS REF CURSOR;
73
74
75 -- ======================================================================
76 -- Utility Functions used in both Date and Number Upgrades.
77 -- ======================================================================
78 -- --------------------------------------------------
79 FUNCTION set_error(p_func_name IN VARCHAR2,
80 p_message IN VARCHAR2,
81 p_sqlerrm IN VARCHAR2)
82 RETURN VARCHAR2
83 IS
84 BEGIN
85 RETURN(Substr(p_func_name || ' failed.' || chr_newline ||
86 'ERROR :' || REPLACE(Nvl(p_message, 'None'),
87 chr_newline,
88 Rpad(chr_newline, 9, ' ')) ||
89 chr_newline ||
90 'SQLERRM:' || Nvl(p_sqlerrm, 'None'),
91 1, g_error_length));
92 EXCEPTION
93 WHEN OTHERS THEN
94 RETURN(Substr('Set_error : ' || Sqlerrm, 1, g_error_length));
95 END set_error;
96
97
98 -- --------------------------------------------------
99 FUNCTION set_who(p_session_mode IN VARCHAR2 DEFAULT 'seed_data',
100 x_error OUT nocopy VARCHAR2)
101 RETURN BOOLEAN
102 IS
103 l_func_name VARCHAR2(80) := g_package_name || 'set_who';
104 BEGIN
105 x_error := NULL;
106 IF (p_session_mode IN ('seed_data', 'customer_data')) THEN
107 g_session_mode := p_session_mode;
108 ELSE
109 x_error := set_error
110 (l_func_name,
111 'Session Mode must be seed_data or customer_data.' || chr_newline ||
112 'p_session_mode : ' || p_session_mode, NULL);
113 RETURN(FALSE);
114 END IF;
115 RETURN(TRUE);
116 EXCEPTION
117 WHEN OTHERS THEN
118 x_error := set_error(l_func_name, 'Top Level Exception.', Sqlerrm);
119 RETURN(FALSE);
120 END set_who;
121
122 -- --------------------------------------------------
123 FUNCTION get_who(x_who_rec OUT nocopy who_rec_type,
124 x_error OUT nocopy VARCHAR2)
125 RETURN BOOLEAN
126 IS
127 l_func_name VARCHAR2(100) := g_package_name || 'get_who';
128 l_who_rec who_rec_type;
129 BEGIN
130 x_error := NULL;
131 IF (g_session_mode = 'seed_data') THEN
132 l_who_rec.created_by := 1;
133 ELSIF (g_session_mode = 'customer_data') THEN
134 l_who_rec.created_by := 0;
135 ELSE
136 x_error := set_error
137 (l_func_name,
138 'Session Mode must be seed_data or customer_data.' || chr_newline ||
139 'g_session_mode : ' || g_session_mode, NULL);
140 RETURN(FALSE);
141 END IF;
142 l_who_rec.session_mode := g_session_mode;
143 l_who_rec.creation_date := Sysdate;
144 l_who_rec.last_update_login := 0;
145 l_who_rec.last_update_date := l_who_rec.creation_date;
146 l_who_rec.last_updated_by := l_who_rec.created_by;
147 x_who_rec := l_who_rec;
148 RETURN(TRUE);
149 EXCEPTION
150 WHEN OTHERS THEN
151 x_error := set_error(l_func_name, 'Top Level Exception.', Sqlerrm);
152 RETURN(FALSE);
153 END get_who;
154
155 -- --------------------------------------------------
156 FUNCTION set_get_who(p_session_mode IN VARCHAR2 DEFAULT 'seed_data',
157 x_who_rec OUT nocopy who_rec_type,
158 x_error OUT nocopy VARCHAR2)
159 RETURN BOOLEAN
160 IS
161 l_func_name VARCHAR2(100) := g_package_name || 'set_who';
162 BEGIN
163 IF (NOT set_who(p_session_mode, x_error)) THEN
164 RETURN(FALSE);
165 END IF;
166
167 IF (NOT get_who(x_who_rec, x_error)) THEN
168 RETURN(FALSE);
169 END IF;
170 RETURN(TRUE);
171 EXCEPTION
172 WHEN OTHERS THEN
173 x_error := set_error(l_func_name, 'Top Level Exception.', Sqlerrm);
174 RETURN(FALSE);
175 END set_get_who;
176
177
178 -- --------------------------------------------------
179 FUNCTION column_exists(p_application_id IN VARCHAR2,
180 p_table_name IN VARCHAR2,
181 p_column_name IN VARCHAR2)
182 RETURN BOOLEAN
183 IS
184 l_rows_count NUMBER;
185 l_value BOOLEAN;
186 l_out_status VARCHAR2(30);
187 l_out_industry VARCHAR2(30);
188 l_out_oracle_schema VARCHAR2(30);
189 BEGIN
190
191 /* Bug 3434427 Fixed warning for GSCC Standard. */
192 /* USed the function below to retrieve oracle_schema */
193 /* and then used it in the Where clause below. */
194 l_value :=FND_INSTALLATION.GET_APP_INFO (p_application_id,
195 l_out_status,
196 l_out_industry,
197 l_out_oracle_schema);
198
199 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
200 COUNT(*)
201 INTO l_rows_count
202 FROM user_synonyms syn, all_tab_columns col
203 where syn.synonym_name = p_table_name
204 and col.owner = syn.table_owner
205 and col.table_name = syn.table_name
206 and col.column_name = p_column_name
207 and col.owner = l_out_oracle_schema;
208
209 IF (l_rows_count = 1) THEN
210 RETURN(TRUE);
211 ELSE
212 RETURN(FALSE);
213 END IF;
214 EXCEPTION
215 WHEN OTHERS THEN
216 RETURN(FALSE);
217 END column_exists;
218
219 -- --------------------------------------------------
220 FUNCTION is_fake_table(p_application_id IN fnd_tables.application_id%TYPE,
221 p_table_name IN fnd_tables.table_name%TYPE)
222 RETURN BOOLEAN
223 IS
224 l_table_type fnd_tables.table_type%TYPE;
225 BEGIN
226 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
227 ft.table_type
228 INTO l_table_type
229 FROM fnd_tables ft
230 WHERE ft.application_id = p_application_id
231 AND ft.table_name = p_table_name;
232
233 IF (l_table_type = 'F') THEN
234 RETURN(TRUE);
235 ELSE
236 RETURN(FALSE);
237 END IF;
238 EXCEPTION
239 WHEN OTHERS THEN
240 RETURN(TRUE);
241 END is_fake_table;
242
243 -- --------------------------------------------------
244 FUNCTION get_application_id
245 (p_appl_short_name IN VARCHAR2,
246 x_application_id OUT nocopy NUMBER,
247 x_error OUT nocopy VARCHAR2)
248 RETURN BOOLEAN
249 IS
250 l_func_name VARCHAR2(80) := g_package_name || 'get_application_id';
251 BEGIN
252 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
253 application_id
254 INTO x_application_id
255 FROM fnd_application
256 WHERE application_short_name = p_appl_short_name;
257 RETURN(TRUE);
258 EXCEPTION
259 WHEN no_data_found THEN
260 x_error :=
261 set_error(l_func_name,
262 p_appl_short_name || ' application does not exist.',
263 Sqlerrm);
264 RETURN(FALSE);
265 WHEN OTHERS THEN
266 x_error := set_error(l_func_name, 'Top Level Exception.', Sqlerrm);
267 RETURN(FALSE);
268 END get_application_id;
269
270 -- --------------------------------------------------
271 FUNCTION get_value_set
272 (p_flex_value_set_name IN VARCHAR2,
273 x_vset_rec OUT nocopy vset_rec_type,
274 x_error OUT nocopy VARCHAR2)
275 RETURN BOOLEAN
276 IS
277 l_func_name VARCHAR2(80) := g_package_name || 'get_value_set';
278 BEGIN
279 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
280 *
281 INTO x_vset_rec
282 FROM fnd_flex_value_sets
283 WHERE flex_value_set_name = p_flex_value_set_name;
284 RETURN(TRUE);
285 EXCEPTION
286 WHEN no_data_found THEN
287 x_error :=
288 set_error(l_func_name,
289 p_flex_value_set_name || ' value set does not exist.',
290 Sqlerrm);
291 RETURN(FALSE);
292 WHEN OTHERS THEN
293 x_error :=
294 set_error(l_func_name,
295 'Value Set : ' || p_flex_value_set_name,
296 Sqlerrm);
297 RETURN(FALSE);
298 END get_value_set;
299
300 -- --------------------------------------------------
301 FUNCTION is_id_value_set_success
302 (p_vset_rec IN vset_rec_type,
303 x_is_id_value_set OUT nocopy BOOLEAN,
304 x_error OUT nocopy VARCHAR2)
305 RETURN BOOLEAN
306 IS
307 l_func_name VARCHAR2(80) := g_package_name || 'is_id_value_set_success';
308 l_vc2 VARCHAR2(2000);
309 BEGIN
310 --
311 -- Is it id type value set?
312 -- User exits and table validated value sets with id column
313 -- are id type value sets. For these value sets we store id's in
314 -- ATTRIBUTE/SEGMENT columns so we don't need to do any conversion.
315 --
316 IF (p_vset_rec.validation_type IN ('U','P')) THEN
317 x_is_id_value_set := TRUE;
318 ELSIF (p_vset_rec.validation_type = 'F') THEN
319 BEGIN
320 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
321 id_column_name
322 INTO l_vc2
323 FROM fnd_flex_validation_tables
324 WHERE flex_value_set_id = p_vset_rec.flex_value_set_id;
325 EXCEPTION
326 WHEN OTHERS THEN
327 x_error := set_error(l_func_name,
328 'Error in get table info.', Sqlerrm);
329 RETURN(FALSE);
330 END;
331 IF (l_vc2 IS NOT NULL) THEN
332 x_is_id_value_set := TRUE;
333 END IF;
334 ELSE
335 x_is_id_value_set := FALSE;
336 END IF;
337 RETURN(TRUE);
338 EXCEPTION
339 WHEN OTHERS THEN
340 x_error := set_error(l_func_name, 'Top Level Exception.', Sqlerrm);
341 RETURN(FALSE);
342 END is_id_value_set_success;
343
344 -- --------------------------------------------------
345 FUNCTION get_sql_update
346 (p_application_id IN VARCHAR2,
347 p_table_name IN VARCHAR2,
348 p_column_name IN VARCHAR2,
349 p_who_rec IN who_rec_type)
350 RETURN VARCHAR2
351 IS
352 l_sql_update VARCHAR2(2000);
353 BEGIN
354 l_sql_update :=
355 'UPDATE ' || p_table_name ||
356 ' SET ' || p_column_name || ' = :l_value_new';
357
358 IF (column_exists(p_application_id, p_table_name, 'LAST_UPDATE_DATE')) THEN
359 l_sql_update := l_sql_update ||
360 ', LAST_UPDATE_DATE = to_date(''' ||
361 To_char(p_who_rec.last_update_date,'RRRR/MM/DD') ||
362 ''',''RRRR/MM/DD'')';
363 END IF;
364
365 IF (column_exists(p_application_id, p_table_name, 'LAST_UPDATED_BY')) THEN
366 l_sql_update := l_sql_update ||
367 ', LAST_UPDATED_BY = ' || p_who_rec.last_updated_by;
368 END IF;
369 l_sql_update := l_sql_update || ' WHERE ROWID = :l_rowid';
370 RETURN(l_sql_update);
371 EXCEPTION
372 WHEN OTHERS THEN
373 RETURN(NULL);
374 END get_sql_update;
375
376 -- --------------------------------------------------
377 -- p_nls_numeric_characters :
378 -- NLS setting for the customer site. In general for US customers
379 -- this is '.,', and for EU customers this is ',.'.
380 -- Number converters will use this value to convert existing values.
381 -- If NULL then get it from v$nls_parameters table.
382 --
383 FUNCTION set_nls_numeric_characters
384 (p_nls_numeric_characters IN VARCHAR2 DEFAULT NULL,
385 x_error OUT nocopy VARCHAR2)
386 RETURN BOOLEAN
387 IS
388 l_func_name VARCHAR2(80) := g_package_name||'set_nls_numeric_characters';
389 l_invalid_set VARCHAR2(100) := '<>+-0123456789';
390 l_nls_numeric_characters VARCHAR2(100) := p_nls_numeric_characters;
391 BEGIN
392 --
393 -- If NULL get from v$nls_parameters table.
394 --
395 IF (l_nls_numeric_characters IS NULL) THEN
396 BEGIN
397 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
398 value INTO l_nls_numeric_characters
399 FROM v$nls_parameters
400 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
401 EXCEPTION
402 WHEN OTHERS THEN
403 x_error := set_error(l_func_name,
404 'SELECT FROM V$NLS_PARAMETERS failed.',
405 Sqlerrm);
406 RETURN(FALSE);
407 END;
408 END IF;
409
410 --
411 -- At least two bytes.
412 --
413 IF (Nvl(Lengthb(l_nls_numeric_characters),0) < 2) THEN
414 GOTO lbl_return_error;
415 END IF;
416
417 --
418 -- First two bytes should not be in above invalid set.
419 --
420 IF (Ltrim(Substrb(l_nls_numeric_characters, 1, 2),
421 l_invalid_set) IS NULL) THEN
422 GOTO lbl_return_error;
423 END IF;
424
425 --
426 -- First two bytes should not be same.
427 --
428 IF (Substrb(l_nls_numeric_characters, 1, 1) =
429 Substrb(l_nls_numeric_characters, 2, 1)) THEN
430 GOTO lbl_return_error;
431 END IF;
432
433 --
434 -- Now we have a valid nls_numeric_characters.
435 --
436 g_ds := Substrb(l_nls_numeric_characters, 1, 1);
437 g_gs := Substrb(l_nls_numeric_characters, 2, 1);
438 g_nls_chars := g_ds || g_gs;
439
440 RETURN(TRUE);
441
442 <<lbl_return_error>>
443 --
444 -- ORA-12705: invalid or unknown NLS parameter value specified
445 --
446 x_error := set_error(l_func_name, NULL, Sqlerrm(-12705));
447 RETURN(FALSE);
448 EXCEPTION
449 WHEN OTHERS THEN
450 x_error := set_error(l_func_name, 'Top Level Exception.', Sqlerrm);
451 RETURN(FALSE);
452 END set_nls_numeric_characters;
453
454 -- ======================================================================
455 -- Function : is_to_standard_number
456 -- ======================================================================
457 -- Will be used to convert numbers. (Replace ',' with '.')
458 --
459 -- Error Cases :
460 -- If there are more than one <D>.
461 -- If group separators are not separated by 3 digits.
462 -- If characters are not in '<G><D>-0123456789'.
463 --
464 -- Examples:
465 -- <D> : decimal separator := Substr(p_nls_numeric_characters, 2, 1);
466 -- <G> : group separator := Substr(p_nls_numeric_characters, 1, 1);
467 --
468 -- existing value converted value
469 -- ---------------------- --------------------
470 -- no <G>, no <D> value stays same
471 -- no <G>, only one <D> <D> is replaced with '.'.
472 -- multiple <D> ERROR : value stays same
473 -- 'X<G>XXX<D>XX' 'XXXX.XX'
474 --
475 --
476 FUNCTION is_to_standard_number(p_char_in IN VARCHAR2,
477 x_char_out OUT nocopy VARCHAR2,
478 x_error OUT nocopy VARCHAR2)
479 RETURN BOOLEAN
480 IS
481 l_char VARCHAR2(2000) := p_char_in;
482 l_vc2 VARCHAR2(2000);
483 l_length NUMBER := Nvl(Length(l_char),0);
484 l_d_pos NUMBER;
485 l_g_pos NUMBER;
486 l_num_g NUMBER := 0;
487 l_valid_set VARCHAR2(100) := g_ds || g_gs || '-0123456789';
488 l_g_exists BOOLEAN := FALSE;
489 BEGIN
490 x_char_out := l_char;
491 x_error := NULL;
492 --
493 -- Null is a valid value.
494 --
495 IF (l_length = 0) THEN
496 RETURN TRUE;
497 END IF;
498
499 --
500 -- Check against valid characters.
501 --
502 l_vc2 := Ltrim(Rtrim(l_char, l_valid_set),l_valid_set);
503 IF (l_vc2 IS NOT NULL) THEN
504 x_error := 'Value contains invalid characters :'''||l_vc2||'''.';
505 RETURN FALSE;
506 END IF;
507
508 --
509 -- There can be only 0 or 1 <D>.
510 --
511 IF (Instr(l_char, g_ds, 1, 2) > 0) THEN
512 x_error := 'More than one <D>.';
513 RETURN FALSE;
514 END IF;
515
516 --
517 -- There must be digits after <D>.
518 --
519 l_d_pos := Instr(l_char, g_ds, 1, 1);
520 IF (l_d_pos = l_length) THEN
521 x_error := 'There are no digits after <D>.';
522 RETURN FALSE;
523 END IF;
524
525 --
526 -- If there is no <D> add one at the end.
527 -- This will help us to parse <G>'s.
528 --
529 IF (l_d_pos = 0) THEN
530 l_char := l_char || g_ds;
531 l_length := Length(l_char);
532 l_d_pos := Instr(l_char, g_ds, 1, 1);
533 END IF;
534
535 --
536 -- At this point we have only one <D>.
537 --
538 --
539 -- Find the right-most group separator.
540 --
541 l_g_pos := Instr(l_char, g_gs, -1, 1);
542 IF (l_g_pos > 0) THEN
543 l_g_exists := TRUE;
544 END IF;
545
546 --
547 -- If value has <G> in it, they must be in correct positions.
548 --
549 l_num_g := 0;
550 IF (l_g_exists) THEN
551
552 IF (l_g_pos > l_d_pos) THEN
553 x_error := '<G> cannot be after <D>.';
554 RETURN FALSE;
555 END IF;
556
557 IF ((Substr(l_char,1,1) = g_gs) OR
558 (Substr(l_char,1,2) = '-' || g_gs)) THEN
559 x_error := '<G> cannot be at the beginning.';
560 RETURN FALSE;
561 END IF;
562
563 WHILE (l_g_pos > 0) LOOP
564 l_num_g := l_num_g + 1;
565
566 IF ((l_g_pos + 4 * l_num_g) <> l_d_pos) THEN
567 x_error := '<G>''s are not in correct positions.';
568 RETURN FALSE;
569 END IF;
570
571 --
572 -- Find the next <G>.
573 --
574 l_g_pos := Instr(l_char, g_gs, -1, l_num_g + 1);
575 END LOOP;
576 END IF;
577
578 --
579 -- Remove <D> from the tail, if it is there.
580 --
581 l_char := Rtrim(l_char, g_ds);
582
583 --
584 -- If <G> is '.' and there is only one '.' then
585 -- it is possible that the value was already upgraded.
586 -- if this is the case report it.
587 -- For example, say NLS is ',.' and the value is '1.000'
588 -- Now is this unconverted '1000' or converted '1.000'?
589 --
590 IF (g_gs = g_cs AND
591 l_num_g = 1 AND
592 Instr(l_char, g_ds, 1, 1) = 0) THEN
593 x_error := 'Do not know if ''.'' is <G> or canonical separator.';
594 RETURN FALSE;
595 END IF;
596
597 --
598 -- Now we have a legal number.
599 --
600
601 --
602 -- Remove all <G>'s.
603 --
604 l_char := REPLACE(l_char, g_gs, NULL);
605
606 --
607 -- Replace <D> with '.'.
608 --
609 l_char := REPLACE(l_char, g_ds, g_cs);
610
611 x_char_out := Rtrim(l_char, g_cs);
612 RETURN TRUE;
613 EXCEPTION
614 WHEN OTHERS THEN
615 x_char_out := p_char_in;
616 x_error := 'is_to_standard_number:Exception : ' || Sqlerrm;
617 RETURN FALSE;
618 END is_to_standard_number;
619
620
621 -- ======================================================================
622 -- Function : is_to_standard_date
623 -- ======================================================================
624 -- Will be used to convert dates.
625 --
626 FUNCTION is_to_standard_date(p_char_in IN VARCHAR2,
627 x_char_out OUT nocopy VARCHAR2,
628 x_error OUT nocopy VARCHAR2)
629 RETURN BOOLEAN
630 IS
631 l_length NUMBER;
632 l_func_name VARCHAR2(80) := g_package_name || 'is_to_standard_date';
633 l_date DATE;
634 BEGIN
635 x_char_out := p_char_in;
636 l_length := Nvl(Length(p_char_in), 0);
637 IF (l_length = 0) THEN
638 RETURN(TRUE);
639 END IF;
640
641 --
642 -- This is only for Date upgrades, do not try Time masks.
643 --
644 IF (l_length = 8) THEN
645 l_date := fnd_date.string_to_date(p_char_in, g_mask_9_8);
646 ELSIF (l_length = 9) THEN
647 l_date := fnd_date.string_to_date(p_char_in, g_mask_9);
648 ELSIF (l_length = 10) THEN
649 l_date := fnd_date.string_to_date(p_char_in, g_mask_11_10);
650 ELSIF (l_length = 11) THEN
651 l_date := fnd_date.string_to_date(p_char_in, g_mask_11);
652 ELSIF (l_length = 14) THEN
653 l_date := fnd_date.string_to_date(p_char_in, g_mask_15_14);
654 ELSIF (l_length = 15) THEN
655 l_date := fnd_date.string_to_date(p_char_in, g_mask_15);
656 ELSIF (l_length = 16) THEN
657 l_date := fnd_date.string_to_date(p_char_in, g_mask_17_16);
658 ELSIF (l_length = 17) THEN
659 l_date := fnd_date.string_to_date(p_char_in, g_mask_17);
660 IF (l_date IS NULL) THEN
661 l_date := fnd_date.string_to_date(p_char_in, g_mask_18_17);
662 END IF;
663 ELSIF (l_length = 18) THEN
664 l_date := fnd_date.string_to_date(p_char_in, g_mask_18);
665 ELSIF (l_length = 19) THEN
666 l_date := fnd_date.string_to_date(p_char_in, g_mask_19);
667 IF (l_date IS NULL) THEN
668 l_date := fnd_date.string_to_date(p_char_in, g_mask_20_19);
669 END IF;
670 ELSIF (l_length = 20) THEN
671 l_date := fnd_date.string_to_date(p_char_in, g_mask_20);
672 ELSE
673 x_error :=
674 set_error(l_func_name,
675 'Unknown length. Old Value : ''' ||
676 p_char_in || '''', NULL);
677 RETURN(FALSE);
678 END IF;
679
680 IF (l_date IS NULL) THEN
681 x_error := set_error(l_func_name,
682 'Unable to convert to date.',
683 Sqlerrm);
684 RETURN(FALSE);
685 END IF;
686
687 x_char_out := To_char(l_date, g_mask_new);
688 RETURN(TRUE);
689 EXCEPTION
690 WHEN OTHERS THEN
691 x_error :=
692 set_error(l_func_name,
693 'Top Level Exception. Old value : ''' ||
694 p_char_in || '''', Sqlerrm);
695 RETURN(FALSE);
696 END is_to_standard_date;
697
698 -- --------------------------------------------------
699 FUNCTION is_to_standard(p_mode IN VARCHAR2,
700 p_char_in IN VARCHAR2,
701 x_char_out OUT nocopy VARCHAR2,
702 x_error OUT nocopy VARCHAR2)
703 RETURN BOOLEAN
704 IS
705 l_func_name VARCHAR2(80) := g_package_name || 'is_to_standard';
706 BEGIN
707 IF (p_mode = g_number_mode) THEN
708 RETURN(is_to_standard_number(p_char_in, x_char_out, x_error));
709 ELSIF (p_mode = g_date_mode OR
710 p_mode = g_datetime_mode) THEN
711 RETURN(is_to_standard_date(p_char_in, x_char_out, x_error));
712 ELSE
713 x_error := set_error(l_func_name,
714 'Unknown Mode : ' || p_mode ,
715 NULL);
716 RETURN(FALSE);
717 END IF;
718 EXCEPTION
719 WHEN OTHERS THEN
720 x_error := set_error(l_func_name,
721 'Top Level Exception.',
722 Sqlerrm);
723 RETURN(FALSE);
724 END is_to_standard;
725
726
727 -- ======================================================================
728 -- MESSAGE
729 -- ======================================================================
730 PROCEDURE init_message
731 IS
732 BEGIN
733 IF g_flag_messaging THEN
734 g_internal_message := g_package_name || ':' || chr_newline;
735 ELSE
736 g_internal_message := 'Messaging is turned OFF. ' || chr_newline ||
737 'Please call set_messaging(TRUE) to turn it ON.';
738 END IF;
739 END init_message;
740
741 FUNCTION get_message RETURN VARCHAR2
742 IS
743 BEGIN
744 RETURN(g_internal_message);
745 END get_message;
746
747 PROCEDURE message(p_msg IN VARCHAR2)
748 IS
749 BEGIN
750 IF g_flag_messaging THEN
751 IF (Length(g_internal_message) < 31000) THEN
752 g_internal_message := g_internal_message || p_msg || chr_newline;
753 ELSE
754 g_internal_message := g_internal_message ||
755 'Maximum size is reached for message string. ' || chr_newline ||
756 'Messaging is turned OFF.';
757 g_flag_messaging := FALSE;
758 END IF;
759 END IF;
760 END message;
761
762 PROCEDURE set_messaging(p_flag IN BOOLEAN)
763 IS
764 BEGIN
765 g_flag_messaging := Nvl(p_flag,FALSE);
766 END set_messaging;
767
768 PROCEDURE internal_init
769 IS
770 BEGIN
771 init_message;
772 g_cp_indent := 1;
773 g_cp_numof_errors := 0;
774 END internal_init;
775
776 PROCEDURE debug(p_debug IN VARCHAR2)
777 IS
778 BEGIN
779 message('DEBUG:' || p_debug);
780 END debug;
781
782 PROCEDURE report_success(p_func_name IN VARCHAR2,
783 p_message IN VARCHAR2)
784 IS
785 BEGIN
786 message(p_func_name || ' successfully completed.');
787 message('Message:' || Nvl(p_message, 'None'));
788 END report_success;
789
790 PROCEDURE report_error(p_func_name IN VARCHAR2,
791 p_message IN VARCHAR2,
792 p_sqlerrm IN VARCHAR2)
793 IS
794 BEGIN
795 message(set_error(p_func_name,
796 p_message,
797 p_sqlerrm));
798 END report_error;
799
800
801 -- ======================================================================
802 -- Public Functions
803 -- ======================================================================
804
805 -- ======================================================================
806 -- Date Upgrades
807 -- ======================================================================
808
809 FUNCTION clone_date_vset
810 (p_old_value_set_name IN VARCHAR2,
811 p_new_value_set_name IN VARCHAR2,
812 p_session_mode IN VARCHAR2 DEFAULT 'customer_data')
813 RETURN NUMBER
814 IS
815 l_func_name VARCHAR2(80) := g_package_name || 'clone_date_vset';
816 l_format_type VARCHAR2(1);
817 l_maximum_size NUMBER;
818 l_ret_code NUMBER := g_ret_no_error;
819
820 l_minimum_value fnd_flex_value_sets.minimum_value%TYPE;
821 l_maximum_value fnd_flex_value_sets.maximum_value%TYPE;
822 l_dependant_default_value fnd_flex_value_sets.dependant_default_value%TYPE;
823 l_dependant_default_meaning fnd_flex_value_sets.dependant_default_meaning%TYPE;
824 l_who_rec who_rec_type;
825 l_old_vset_rec vset_rec_type;
826 l_new_vset_rec vset_rec_type;
827 l_long VARCHAR2(32000);
828 l_count NUMBER;
829 l_event_code fnd_flex_validation_events.event_code%TYPE;
830 l_error VARCHAR2(2000);
831 BEGIN
832 internal_init;
833 debug('Starting to clone...');
834 debug('Old Value Set Name : ' || p_old_value_set_name);
835 debug('New Value Set Name : ' || p_new_value_set_name);
836
837 SAVEPOINT sp_clone_date_vset;
838 --
839 -- WHO columns
840 --
841 IF (NOT set_get_who(p_session_mode, l_who_rec, l_error)) THEN
842 message(l_error);
843 l_ret_code := g_ret_critical_error;
844 GOTO lbl_return;
845 END IF;
846
847 --
848 -- Make sure old value set exists.
849 --
850 IF (NOT get_value_set(p_old_value_set_name, l_old_vset_rec, l_error)) THEN
851 message(l_error);
852 l_ret_code := g_ret_critical_error;
853 GOTO lbl_return;
854 END IF;
855 debug('Old value set id : ' || l_old_vset_rec.flex_value_set_id);
856
857 --
858 -- Make sure old value set is in old date format.
859 --
860 IF (NOT (l_old_vset_rec.format_type IN ('D', 'T'))) THEN
861 report_error(l_func_name,
862 'No need to clone this value set' || chr_newline ||
863 'Value Set Name : '||p_old_value_set_name||chr_newline ||
864 'Format Type : '||l_old_vset_rec.format_type,
865 NULL);
866 l_ret_code := g_ret_no_need_to_clone;
867 GOTO lbl_return;
868 END IF;
869
870 --
871 -- New format_type and maximum_size.
872 --
873 IF (l_old_vset_rec.format_type = 'D') THEN
874 l_format_type := 'X';
875 l_maximum_size := 11;
876 ELSE
877 l_format_type := 'Y';
878 l_maximum_size := 20;
879 END IF;
880 debug('New value set format type : ' || l_format_type);
881 debug('New value set maximum size : ' || l_maximum_size);
882 --
883 -- Make sure new value set does not exist or
884 -- if it exists it was already cloned.
885 --
886 BEGIN
887 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
888 *
889 INTO l_new_vset_rec
890 FROM fnd_flex_value_sets
891 WHERE flex_value_set_name = p_new_value_set_name;
892 --
893 -- New value set exists check the format and maximum size.
894 --
895 IF ((l_new_vset_rec.format_type = l_format_type) AND
896 (l_new_vset_rec.maximum_size = l_maximum_size)) THEN
897 --
898 -- It was already cloned.
899 --
900 report_error(l_func_name,
901 p_old_value_set_name || ' was already cloned.',
902 NULL);
903 l_ret_code := g_ret_already_cloned;
904 GOTO lbl_return;
905 ELSE
906 report_error(l_func_name,
907 p_new_value_set_name || ' value set already exists ' ||
908 'with a different format_type or maximum_size.' ||
909 chr_newline ||
910 'Format Type : ' || l_new_vset_rec.format_type ||
911 chr_newline ||
912 'Maximum Size : ' || l_new_vset_rec.maximum_size, NULL);
913 l_ret_code := g_ret_new_vset_exists;
914 GOTO lbl_return;
915 END IF;
916 EXCEPTION
917 WHEN no_data_found THEN
918 --
919 -- New value set does not exist.
920 --
921 debug('New value set does not exist, which is OK...');
922 NULL;
923 WHEN OTHERS THEN
924 report_error(l_func_name,
925 'Value Set : ' || p_new_value_set_name, Sqlerrm);
926 l_ret_code := g_ret_critical_error;
927 GOTO lbl_return;
928 END;
929
930 --
931 -- New minimum_value, maximum_value and dependant_default_value.
932 --
933 IF (NOT is_to_standard_date(l_old_vset_rec.minimum_value,
934 l_minimum_value, l_error)) THEN
935 message(l_error);
936 message(l_func_name || chr_newline ||
937 'Minimum Value is not in proper format.' || chr_newline ||
938 'Minimum Value : '||l_old_vset_rec.minimum_value||chr_newline ||
939 'It is set to NULL');
940 l_minimum_value := NULL;
941 END IF;
942 IF (NOT is_to_standard_date(l_old_vset_rec.maximum_value,
943 l_maximum_value, l_error)) THEN
944 message(l_error);
945 message(l_func_name || chr_newline ||
946 'Maximum Value is not in proper format.' || chr_newline ||
947 'Maximum Value : '||l_old_vset_rec.maximum_value||chr_newline ||
948 'It is set to NULL');
949 l_maximum_value := NULL;
950 END IF;
951
952 l_dependant_default_value := l_old_vset_rec.dependant_default_value;
953 l_dependant_default_meaning := l_old_vset_rec.dependant_default_meaning;
954 IF (l_old_vset_rec.validation_type = 'D') THEN
955 IF (NOT is_to_standard_date(l_old_vset_rec.dependant_default_value,
956 l_dependant_default_value, l_error)) THEN
957 --
958 -- dependant_default_value is required, set it to something.
959 --
960 message(l_error);
961 message(l_func_name || chr_newline ||
962 'Dependent Default Value is not in proper format.' ||
963 chr_newline ||
964 'Dependent Default Value : ' ||
965 l_old_vset_rec.dependant_default_value || chr_newline ||
966 'It is set to 1000/01/01 00:00:00');
967 l_dependant_default_value := '1000/01/01 00:00:00';
968 l_dependant_default_meaning := 'Set to 1000/01/01 00:00:00 by upgrade utility.';
969 END IF;
970 END IF;
971
972 --
973 -- Clone the row in FND_FLEX_VALUE_SETS table.
974 --
975 BEGIN
976 INSERT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
977 INTO fnd_flex_value_sets
978 (flex_value_set_id,
979 flex_value_set_name,
980 validation_type,
981 security_enabled_flag,
982 longlist_flag,
983 format_type,
984 maximum_size,
985 alphanumeric_allowed_flag,
986 uppercase_only_flag,
987 numeric_mode_enabled_flag,
988 description,
989 minimum_value,
990 maximum_value,
991 number_precision,
992 protected_flag,
993 last_update_login,
994 last_update_date,
995 last_updated_by,
996 creation_date,
997 created_by,
998
999 dependant_default_value,/* note spelling */
1000 dependant_default_meaning,/* note spelling */
1001 parent_flex_value_set_id)
1002 SELECT
1003 fnd_flex_value_sets_s.NEXTVAL,
1004 p_new_value_set_name,
1005 validation_type,
1006 security_enabled_flag,
1007 longlist_flag,
1008 l_format_type,
1009 l_maximum_size,
1010 'Y', -- alphanumeric_allowed_flag
1011 'Y', -- uppercase_only_flag
1012 'N', -- numeric_mode_enabled_flag
1013 description,
1014 l_minimum_value,
1015 l_maximum_value,
1016 number_precision,
1017 protected_flag,
1018 l_who_rec.last_update_login,
1019 l_who_rec.last_update_date,
1020 l_who_rec.last_updated_by,
1021 l_who_rec.creation_date,
1022 l_who_rec.created_by,
1023 l_dependant_default_value,
1024 l_dependant_default_meaning,
1025 parent_flex_value_set_id
1026 FROM fnd_flex_value_sets
1027 WHERE flex_value_set_name = p_old_value_set_name;
1028
1029 IF (SQL%rowcount = 1) THEN
1030 debug('FND_FLEX_VALUE_SETS entry cloned successfully.');
1031 ELSE
1032 report_error(l_func_name,
1033 'SQL%ROWCOUNT : ' || SQL%rowcount,NULL);
1034 l_ret_code := g_ret_critical_error;
1035 GOTO lbl_return;
1036 END IF;
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 report_error(l_func_name,
1040 'INSERT INTO fnd_flex_value_sets failed.', Sqlerrm);
1041 l_ret_code := g_ret_critical_error;
1042 GOTO lbl_return;
1043 END;
1044
1045 --
1046 -- Reselect New Value Set.
1047 --
1048 IF (NOT get_value_set(p_new_value_set_name, l_new_vset_rec, l_error)) THEN
1049 message(l_error);
1050 l_ret_code := g_ret_critical_error;
1051 GOTO lbl_return;
1052 END IF;
1053 debug('New value set id : ' || l_new_vset_rec.flex_value_set_id);
1054 debug('New value set validation type : ' || l_new_vset_rec.validation_type);
1055
1056 IF (l_new_vset_rec.validation_type = 'F') THEN
1057 --
1058 -- Clone the row in FND_FLEX_VALIDATION_TABLES table.
1059 --
1060 -- Since we have a long column we have to select it first.
1061 --
1062 BEGIN
1063 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1064 additional_where_clause
1065 INTO l_long
1066 FROM fnd_flex_validation_tables
1067 WHERE flex_value_set_id = l_old_vset_rec.flex_value_set_id;
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 report_error(l_func_name,
1071 'SELECT FROM fnd_flex_validation_tables failed.',
1072 Sqlerrm);
1073 l_ret_code := g_ret_critical_error;
1074 GOTO lbl_return;
1075 END;
1076 BEGIN
1077 INSERT INTO fnd_flex_validation_tables
1078 (flex_value_set_id,
1079 application_table_name,
1080 value_column_name,
1081 value_column_type,
1082 value_column_size,
1083 id_column_name,
1084 id_column_type,
1085 id_column_size,
1086 meaning_column_name,
1087 meaning_column_type,
1088 meaning_column_size,
1089 summary_allowed_flag,
1090 table_application_id,
1091 additional_where_clause,
1092 additional_quickpick_columns,
1093
1094 compiled_attribute_column_name,
1095 enabled_column_name,
1096 hierarchy_level_column_name,
1097 start_date_column_name,
1098 end_date_column_name,
1099 summary_column_name,
1100
1101 last_update_login,
1102 last_update_date,
1103 last_updated_by,
1104 creation_date,
1105 created_by)
1106 SELECT
1107 l_new_vset_rec.flex_value_set_id,
1108 application_table_name,
1109 value_column_name,
1110 value_column_type,
1111 value_column_size,
1112 id_column_name,
1113 id_column_type,
1114 id_column_size,
1115 meaning_column_name,
1116 meaning_column_type,
1117 meaning_column_size,
1118 summary_allowed_flag,
1119 table_application_id,
1120 l_long, -- additional_where_clause,
1121 additional_quickpick_columns,
1122
1123 compiled_attribute_column_name,
1124 enabled_column_name,
1125 hierarchy_level_column_name,
1126 start_date_column_name,
1127 end_date_column_name,
1128 summary_column_name,
1129
1130 l_who_rec.last_update_login,
1131 l_who_rec.last_update_date,
1132 l_who_rec.last_updated_by,
1133 l_who_rec.creation_date,
1134 l_who_rec.created_by
1135 FROM fnd_flex_validation_tables
1136 WHERE flex_value_set_id = l_old_vset_rec.flex_value_set_id;
1137 IF (SQL%rowcount = 1) THEN
1138 debug('FND_FLEX_VALIDATION_TABLES entry is succesfully cloned.');
1139 ELSE
1140 report_error(l_func_name,
1141 'SQL%ROWCOUNT : ' || SQL%rowcount,NULL);
1142 l_ret_code := g_ret_critical_error;
1143 GOTO lbl_return;
1144 END IF;
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 report_error(l_func_name,
1148 'INSERT INTO fnd_flex_validation_tables failed.',
1149 Sqlerrm);
1150 l_ret_code := g_ret_critical_error;
1151 GOTO lbl_return;
1152 END;
1153
1154
1155 ELSIF (l_new_vset_rec.validation_type IN ('U','P')) THEN
1156 --
1157 -- Clone the rows in FND_FLEX_VALIDATION_EVENTS table.
1158 --
1159 BEGIN
1160 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1161 COUNT(*)
1162 INTO l_count
1163 FROM fnd_flex_validation_events
1164 WHERE flex_value_set_id = l_old_vset_rec.flex_value_set_id;
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 report_error(l_func_name,
1168 'SELECT COUNT fnd_flex_validation_events failed.',
1169 Sqlerrm);
1170 l_ret_code := g_ret_critical_error;
1171 GOTO lbl_return;
1172 END;
1173 debug('Number of events : ' || l_count);
1174
1175 FOR i IN 1..l_count LOOP
1176 BEGIN
1177 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1178 user_exit, event_code
1179 INTO l_long, l_event_code
1180 FROM fnd_flex_validation_events told
1181 WHERE told.flex_value_set_id = l_old_vset_rec.flex_value_set_id
1182 AND ROWNUM = 1
1183 AND NOT exists
1184 (SELECT NULL
1185 FROM fnd_flex_validation_events tnew
1186 WHERE tnew.flex_value_set_id = l_new_vset_rec.flex_value_set_id
1187 AND tnew.event_code = told.event_code);
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 report_error(l_func_name,
1191 'SELECT FROM fnd_flex_validation_events ' ||
1192 'failed.', Sqlerrm);
1193 l_ret_code := g_ret_critical_error;
1194 GOTO lbl_return;
1195 END;
1196 BEGIN
1197 INSERT INTO fnd_flex_validation_events
1198 (flex_value_set_id,
1199 event_code,
1200 user_exit,
1201 last_update_login,
1202 last_update_date,
1203 last_updated_by,
1204 creation_date,
1205 created_by)
1206 VALUES (l_new_vset_rec.flex_value_set_id,
1207 l_event_code,
1208 l_long,
1209 l_who_rec.last_update_login,
1210 l_who_rec.last_update_date,
1211 l_who_rec.last_updated_by,
1212 l_who_rec.creation_date,
1213 l_who_rec.created_by);
1214 IF (SQL%rowcount = 1) THEN
1215 debug('FND_FLEX_VALIDATION_EVENTS is succesfully cloned.');
1216 debug('event code : ' || l_event_code);
1217 ELSE
1218 report_error(l_func_name,
1219 'SQL%ROWCOUNT : ' || SQL%rowcount,NULL);
1220 l_ret_code := g_ret_critical_error;
1221 GOTO lbl_return;
1222 END IF;
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 report_error(l_func_name,
1226 'INSERT INTO fnd_flex_validation_events failed.',
1227 Sqlerrm);
1228 l_ret_code := g_ret_critical_error;
1229 GOTO lbl_return;
1230 END;
1231 END LOOP;
1232 END IF;
1233
1234 <<lbl_return>>
1235 IF (l_ret_code = g_ret_no_error) THEN
1236 message('Successful operation calling !!!COMMIT!!! ...');
1237 COMMIT;
1238 ELSE
1239 message('Unsuccessful operation calling !!!ROLLBACK!!! ...');
1240 ROLLBACK TO SAVEPOINT sp_clone_date_vset;
1241 END IF;
1242 RETURN(l_ret_code);
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
1246 ROLLBACK TO SAVEPOINT sp_clone_date_vset;
1247 message('Unsuccessful operation calling !!!ROLLBACK!!! ...');
1248 RETURN(g_ret_critical_error);
1249 END clone_date_vset;
1250
1251
1252
1253 FUNCTION upgrade_date_report_parameters
1254 (p_appl_short_name IN VARCHAR2,
1255 p_value_set_from IN VARCHAR2,
1256 p_value_set_to IN VARCHAR2,
1257 p_session_mode IN VARCHAR2 DEFAULT 'customer_data',
1258 p_report_name_like IN VARCHAR2 DEFAULT '%')
1259 RETURN NUMBER
1260 IS
1261 l_func_name VARCHAR2(80) := (g_package_name ||
1262 'upgrade_date_report_parameters');
1263 l_who_rec who_rec_type;
1264 l_application_id NUMBER;
1265 l_old_vset_rec vset_rec_type;
1266 l_new_vset_rec vset_rec_type;
1267 l_report_name_like VARCHAR2(200) :='$SRS$.'||Nvl(p_report_name_like,'%');
1268 l_ret_code NUMBER := g_ret_no_error;
1269 l_segs_count NUMBER := 0;
1270 l_default_value fnd_descr_flex_column_usages.default_value%TYPE;
1271
1272 CURSOR srs_cur(p_application_id IN NUMBER,
1273 p_flex_value_set_id IN NUMBER,
1274 p_report_name_like IN VARCHAR2)
1275 IS
1276 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1277 application_id, descriptive_flexfield_name,
1278 descriptive_flex_context_code,
1279 application_column_name, end_user_column_name,
1280 default_type, default_value
1281 FROM fnd_descr_flex_column_usages
1282 WHERE application_id = p_application_id
1283 AND flex_value_set_id = p_flex_value_set_id
1284 AND descriptive_flexfield_name LIKE p_report_name_like
1285 AND enabled_flag = 'Y'
1286 AND descriptive_flex_context_code = 'Global Data Elements'
1287 ORDER BY application_id, descriptive_flexfield_name,
1288 descriptive_flex_context_code, application_column_name;
1289
1290 srs_rec srs_cur%ROWTYPE;
1291 l_error VARCHAR2(2000);
1292 BEGIN
1293 internal_init;
1294 --
1295 -- WHO columns
1296 --
1297 IF (NOT set_get_who(p_session_mode, l_who_rec, l_error)) THEN
1298 message(l_error);
1299 RETURN(g_ret_critical_error);
1300 END IF;
1301
1302 --
1303 -- Get the application_id.
1304 --
1305 IF (NOT get_application_id(p_appl_short_name,
1306 l_application_id, l_error)) THEN
1307 message(l_error);
1308 RETURN(g_ret_critical_error);
1309 END IF;
1310 debug('application_id : ' || To_char(l_application_id));
1311
1312 --
1313 -- Get the old value set.
1314 --
1315 IF (NOT get_value_set(p_value_set_from, l_old_vset_rec, l_error)) THEN
1316 message(l_error);
1317 RETURN(g_ret_critical_error);
1318 END IF;
1319 debug('from_value_set_id : ' || To_char(l_old_vset_rec.flex_value_set_id));
1320
1321 IF (NOT (l_old_vset_rec.format_type IN ('D', 'T'))) THEN
1322 message('From value set must be regular type Date or Date/Time.');
1323 RETURN(g_ret_critical_error);
1324 END IF;
1325
1326 --
1327 -- Get the new value set.
1328 --
1329 IF (NOT get_value_set(p_value_set_to, l_new_vset_rec, l_error)) THEN
1330 message(l_error);
1331 RETURN(g_ret_critical_error);
1332 END IF;
1333 debug('to_value_set_id : ' || To_char(l_new_vset_rec.flex_value_set_id));
1334
1335 IF (NOT (l_new_vset_rec.format_type IN ('X', 'Y'))) THEN
1336 message('To value set must be standard type Date or Date/Time.');
1337 RETURN(g_ret_critical_error);
1338 END IF;
1339
1340 --
1341 -- Count the segments
1342 --
1343 l_segs_count := 0;
1344 FOR srs_rec IN srs_cur(l_application_id,
1345 l_old_vset_rec.flex_value_set_id,
1346 l_report_name_like) LOOP
1347 l_segs_count := l_segs_count + 1;
1348 END LOOP;
1349 debug('Number of parameters to upgrade : ' || To_char(l_segs_count));
1350
1351 -- go one-by-one
1352 FOR i IN 1..l_segs_count LOOP
1353 --
1354 -- Since cursor data is changed every time, we will fetch one row
1355 -- at a time.
1356 --
1357 OPEN srs_cur(l_application_id, l_old_vset_rec.flex_value_set_id,
1358 l_report_name_like);
1359 FETCH srs_cur INTO srs_rec;
1360 CLOSE srs_cur;
1361 debug('Modifying:' || p_appl_short_name || ':' ||
1362 srs_rec.descriptive_flexfield_name || ':' ||
1363 srs_rec.descriptive_flex_context_code || ':' ||
1364 srs_rec.end_user_column_name);
1365 l_default_value := srs_rec.default_value;
1366 IF (srs_rec.default_type = 'C') THEN
1367 IF (NOT is_to_standard_date(srs_rec.default_value,
1368 l_default_value, l_error)) THEN
1369 --
1370 -- If type is constant then default_value is required,
1371 -- set it to something.
1372 --
1373 message(l_error);
1374 message(l_func_name || chr_newline ||
1375 'Default Value is not in proper format.' ||chr_newline||
1376 'Default Value : ' ||
1377 srs_rec.default_value || chr_newline ||
1378 'It is set to 1000/01/01 00:00:00');
1379 l_default_value := '1000/01/01 00:00:00';
1380 l_ret_code := g_ret_ignored_errors;
1381 END IF;
1382 END IF;
1383
1384 BEGIN
1385 UPDATE fnd_descr_flex_column_usages
1386 SET flex_value_set_id = l_new_vset_rec.flex_value_set_id,
1387 default_value = l_default_value,
1388 last_update_date = l_who_rec.last_update_date,
1389 last_updated_by = l_who_rec.last_updated_by
1390 WHERE application_id = srs_rec.application_id
1391 AND descriptive_flexfield_name = srs_rec.descriptive_flexfield_name
1392 AND descriptive_flex_context_code = srs_rec.descriptive_flex_context_code
1393 AND application_column_name = srs_rec.application_column_name;
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396 report_error(l_func_name,
1397 'Failure in UPDATE FND_DESCR_FLEX_COLUMN_USAGES.',
1398 Sqlerrm);
1399 l_ret_code := g_ret_ignored_errors;
1400 END;
1401 COMMIT;
1402 END LOOP;
1403
1404 report_success(l_func_name,
1405 'Parameters upgraded for : ' || p_appl_short_name);
1406 message('Calling !!!COMMIT!!! ...');
1407 COMMIT;
1408 RETURN(l_ret_code);
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
1412 RETURN(g_ret_critical_error);
1413 END upgrade_date_report_parameters;
1414
1415
1416 FUNCTION upgrade_date_dff_segments
1417 (p_appl_short_name IN VARCHAR2,
1418 p_value_set_from IN VARCHAR2,
1419 p_value_set_to IN VARCHAR2,
1420 p_session_mode IN VARCHAR2 DEFAULT 'customer_data',
1421 p_dff_name_like IN VARCHAR2 DEFAULT '%',
1422 p_context_code_like IN VARCHAR2 DEFAULT '%')
1423 RETURN NUMBER
1424 IS
1425 l_func_name VARCHAR2(80) := (g_package_name ||
1426 'upgrade_date_dff_segments');
1427 l_application_id NUMBER;
1428 l_ret_code NUMBER := g_ret_no_error;
1429 l_segs_count NUMBER := 0;
1430 l_default_value fnd_descr_flex_column_usages.default_value%TYPE;
1431 l_old_vset_rec vset_rec_type;
1432 l_new_vset_rec vset_rec_type;
1433 l_dff_name_like VARCHAR2(500) := Nvl(p_dff_name_like, '%');
1434 l_context_code_like VARCHAR2(500) := Nvl(p_context_code_like, '%');
1435
1436 l_who_rec who_rec_type;
1437 l_error VARCHAR2(2000);
1438
1439 CURSOR dff_cur(p_application_id IN NUMBER,
1440 p_flex_value_set_id IN NUMBER,
1441 p_dff_name_like IN VARCHAR2,
1442 p_context_code_like IN VARCHAR2)
1443 IS
1444 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1445 df.application_id, df.descriptive_flexfield_name,
1446 df.table_application_id, df.application_table_name,
1447 df.context_column_name,
1448 dfc.descriptive_flex_context_code, dfc.global_flag,
1449 dfcu.application_column_name, dfcu.end_user_column_name,
1450 dfcu.default_type, dfcu.default_value,
1451 fc.column_type, fc.width
1452 FROM fnd_descriptive_flexs df, fnd_descr_flex_contexts dfc,
1453 fnd_descr_flex_column_usages dfcu, fnd_columns fc
1454 WHERE df.application_id = dfc.application_id
1455 AND df.descriptive_flexfield_name = dfc.descriptive_flexfield_name
1456 AND dfc.application_id = dfcu.application_id
1457 AND dfc.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
1458 AND dfc.descriptive_flex_context_code = dfcu.descriptive_flex_context_code
1459 AND ((fc.application_id, fc.table_id) =
1460 (SELECT ft.application_id, ft.table_id
1461 FROM fnd_tables ft
1462 WHERE ft.application_id = df.table_application_id
1463 AND ft.table_name = df.application_table_name))
1464 AND fc.column_name = dfcu.application_column_name
1465 AND fc.flexfield_usage_code = 'D'
1466 AND dfcu.flex_value_set_id = p_flex_value_set_id
1467 AND dfcu.enabled_flag = 'Y'
1468 AND dfc.enabled_flag = 'Y'
1469 AND df.application_id = p_application_id
1470 AND df.descriptive_flexfield_name LIKE p_dff_name_like
1471 AND dfc.descriptive_flex_context_code LIKE p_context_code_like
1472 ORDER BY df.application_id, df.descriptive_flexfield_name,
1473 dfc.descriptive_flex_context_code, dfcu.application_column_name;
1474
1475 dff_rec dff_cur%ROWTYPE;
1476 BEGIN
1477 internal_init;
1478 --
1479 -- WHO columns
1480 --
1481 IF (NOT set_get_who(p_session_mode, l_who_rec, l_error)) THEN
1482 message(l_error);
1483 RETURN(g_ret_critical_error);
1484 END IF;
1485
1486 --
1487 -- Get the application_id.
1488 --
1489 IF (NOT get_application_id(p_appl_short_name,
1490 l_application_id, l_error)) THEN
1491 message(l_error);
1492 RETURN(g_ret_critical_error);
1493 END IF;
1494 debug('application_id : ' || To_char(l_application_id));
1495
1496 --
1497 -- Get the old value set.
1498 --
1499 IF (NOT get_value_set(p_value_set_from, l_old_vset_rec, l_error)) THEN
1500 message(l_error);
1501 RETURN(g_ret_critical_error);
1502 END IF;
1503 debug('from_value_set_id : ' || To_char(l_old_vset_rec.flex_value_set_id));
1504
1505 IF (NOT (l_old_vset_rec.format_type IN ('D', 'T'))) THEN
1506 message('From value set must be regular type Date or Date/Time.');
1507 RETURN(g_ret_critical_error);
1508 END IF;
1509
1510 --
1511 -- Get the new value set.
1512 --
1513 IF (NOT get_value_set(p_value_set_to, l_new_vset_rec, l_error)) THEN
1514 message(l_error);
1515 RETURN(g_ret_critical_error);
1516 END IF;
1517 debug('to_value_set_id : ' || To_char(l_new_vset_rec.flex_value_set_id));
1518
1519 IF (NOT (l_new_vset_rec.format_type IN ('X', 'Y'))) THEN
1520 message('To value set must be standard type Date or Date/Time.');
1521 RETURN(g_ret_critical_error);
1522 END IF;
1523
1524 --
1525 -- Count the old segments
1526 --
1527 l_segs_count := 0;
1528 FOR dff_rec IN dff_cur(l_application_id,
1529 l_old_vset_rec.flex_value_set_id,
1530 l_dff_name_like,
1531 l_context_code_like) LOOP
1532 l_segs_count := l_segs_count + 1;
1533 END LOOP;
1534 debug('Number of DFF segments to upgrade : ' || To_char(l_segs_count));
1535
1536 -- go one-by-one
1537 FOR i IN 1..l_segs_count LOOP
1538 --
1539 -- Since cursor data is changed every time, we will fetch one row
1540 -- at a time.
1541 --
1542 OPEN dff_cur(l_application_id, l_old_vset_rec.flex_value_set_id,
1543 l_dff_name_like, l_context_code_like);
1544 FETCH dff_cur INTO dff_rec;
1545 CLOSE dff_cur;
1546 debug('Modifying:' || p_appl_short_name || ':' ||
1547 dff_rec.descriptive_flexfield_name || ':' ||
1548 dff_rec.descriptive_flex_context_code || ':' ||
1549 dff_rec.end_user_column_name);
1550 l_default_value := dff_rec.default_value;
1551 IF (dff_rec.default_type = 'C') THEN
1552 IF (NOT is_to_standard_date(dff_rec.default_value,
1553 l_default_value, l_error)) THEN
1554 --
1555 -- If type is constant then default_value is required,
1556 -- set it to something.
1557 --
1558 message(l_error);
1559 message(l_func_name || chr_newline ||
1560 'Default Value is not in proper format.' ||chr_newline||
1561 'Default Value : ' ||
1562 dff_rec.default_value || chr_newline ||
1563 'It is set to 1000/01/01 00:00:00');
1564 l_default_value := '1000/01/01 00:00:00';
1565 l_ret_code := g_ret_ignored_errors;
1566 END IF;
1567 END IF;
1568
1569 BEGIN
1570 UPDATE fnd_descr_flex_column_usages
1571 SET flex_value_set_id = l_new_vset_rec.flex_value_set_id,
1572 default_value = l_default_value,
1573 last_update_date = l_who_rec.last_update_date,
1574 last_updated_by = l_who_rec.last_updated_by
1575 WHERE application_id = dff_rec.application_id
1576 AND descriptive_flexfield_name = dff_rec.descriptive_flexfield_name
1577 AND descriptive_flex_context_code = dff_rec.descriptive_flex_context_code
1578 AND application_column_name = dff_rec.application_column_name;
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 report_error(l_func_name,
1582 'Failure in UPDATE FND_DESCR_FLEX_COLUMN_USAGES.',
1583 Sqlerrm);
1584 l_ret_code := g_ret_ignored_errors;
1585 END;
1586 COMMIT;
1587 END LOOP; -- FOR i IN 1..l_segs_count
1588
1589 report_success(l_func_name,
1590 'DFF segments upgraded for : ' || p_appl_short_name);
1591 message('Calling !!!COMMIT!!! ...');
1592 COMMIT;
1593 RETURN(l_ret_code);
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
1597 RETURN(g_ret_critical_error);
1598 END upgrade_date_dff_segments;
1599
1600
1601 FUNCTION upgrade_date_kff_segments
1602 (p_appl_short_name IN VARCHAR2,
1603 p_id_flex_code IN VARCHAR2,
1604 p_value_set_from IN VARCHAR2,
1605 p_value_set_to IN VARCHAR2,
1606 p_session_mode IN VARCHAR2 DEFAULT 'customer_data',
1607 p_struct_num_like IN VARCHAR2 DEFAULT '%',
1608 p_struct_name_like IN VARCHAR2 DEFAULT '%')
1609 RETURN NUMBER
1610 IS
1611 l_func_name VARCHAR2(80) := (g_package_name ||
1612 'upgrade_date_kff_segments');
1613 l_application_id NUMBER;
1614 l_ret_code NUMBER := g_ret_no_error;
1615 l_segs_count NUMBER := 0;
1616 l_default_value fnd_id_flex_segments.default_value%TYPE;
1617 l_old_vset_rec vset_rec_type;
1618 l_new_vset_rec vset_rec_type;
1619 l_struct_num_like VARCHAR2(500) := Nvl(p_struct_num_like, '%');
1620 l_struct_name_like VARCHAR2(500) := Nvl(p_struct_name_like, '%');
1621
1622 l_who_rec who_rec_type;
1623 l_error VARCHAR2(2000);
1624
1625 CURSOR kff_cur(p_application_id IN NUMBER,
1626 p_id_flex_code IN VARCHAR2,
1627 p_flex_value_set_id IN NUMBER,
1628 p_struct_num_like IN VARCHAR2,
1629 p_struct_name_like IN VARCHAR2)
1630 IS
1631 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1632 idf.application_id, idf.id_flex_code,
1633 idf.table_application_id, idf.application_table_name,
1634 idf.set_defining_column_name, idf.unique_id_column_name,
1635 ifst.id_flex_num, ifst.id_flex_structure_name,
1636 ifsg.segment_name, ifsg.application_column_name,
1637 ifsg.default_type, ifsg.default_value,
1638 fc.column_type, fc.width
1639 FROM fnd_id_flexs idf, fnd_id_flex_structures_vl ifst,
1640 fnd_id_flex_segments ifsg, fnd_columns fc
1641 WHERE idf.application_id = ifst.application_id
1642 AND idf.id_flex_code = ifst.id_flex_code
1643 AND ifst.application_id = ifsg.application_id
1644 AND ifst.id_flex_code = ifsg.id_flex_code
1645 AND ifst.id_flex_num = ifsg.id_flex_num
1646 AND ((fc.application_id, fc.table_id) =
1647 (SELECT ft.application_id, ft.table_id
1648 FROM fnd_tables ft
1649 WHERE ft.application_id = idf.table_application_id
1650 AND ft.table_name = idf.application_table_name))
1651 AND fc.column_name = ifsg.application_column_name
1652 AND fc.flexfield_usage_code = 'K'
1653 AND ifsg.flex_value_set_id = p_flex_value_set_id
1654 AND ifst.enabled_flag = 'Y'
1655 AND ifsg.enabled_flag = 'Y'
1656 AND idf.application_id = p_application_id
1657 AND idf.id_flex_code = p_id_flex_code
1658 AND ifst.id_flex_structure_name LIKE p_struct_name_like
1659 AND To_char(ifst.id_flex_num) LIKE p_struct_num_like
1660 ORDER BY idf.application_id, idf.id_flex_code,
1661 ifst.id_flex_num, ifsg.application_column_name;
1662
1663 kff_rec kff_cur%ROWTYPE;
1664 BEGIN
1665 internal_init;
1666 --
1667 -- WHO columns
1668 --
1669 IF (NOT set_get_who(p_session_mode, l_who_rec, l_error)) THEN
1670 message(l_error);
1671 RETURN(g_ret_critical_error);
1672 END IF;
1673
1674 --
1675 -- Get the application_id.
1676 --
1677 IF (NOT get_application_id(p_appl_short_name,
1678 l_application_id, l_error)) THEN
1679 message(l_error);
1680 RETURN(g_ret_critical_error);
1681 END IF;
1682 debug('application_id : ' || To_char(l_application_id));
1683
1684 --
1685 -- Get the old value set.
1686 --
1687 IF (NOT get_value_set(p_value_set_from, l_old_vset_rec, l_error)) THEN
1688 message(l_error);
1689 RETURN(g_ret_critical_error);
1690 END IF;
1691 debug('from_value_set_id : ' || To_char(l_old_vset_rec.flex_value_set_id));
1692
1693 IF (NOT (l_old_vset_rec.format_type IN ('D', 'T'))) THEN
1694 message('From value set must be regular type Date or Date/Time.');
1695 RETURN(g_ret_critical_error);
1696 END IF;
1697
1698 --
1699 -- Get the new value set.
1700 --
1701 IF (NOT get_value_set(p_value_set_to, l_new_vset_rec, l_error)) THEN
1702 message(l_error);
1703 RETURN(g_ret_critical_error);
1704 END IF;
1705 debug('to_value_set_id : ' || To_char(l_new_vset_rec.flex_value_set_id));
1706
1707 IF (NOT (l_new_vset_rec.format_type IN ('X', 'Y'))) THEN
1708 message('To value set must be standard type Date or Date/Time.');
1709 RETURN(g_ret_critical_error);
1710 END IF;
1711
1712
1713 --
1714 -- Count the old segments
1715 --
1716 l_segs_count := 0;
1717 FOR kff_rec IN kff_cur(l_application_id,
1718 p_id_flex_code,
1719 l_old_vset_rec.flex_value_set_id,
1720 l_struct_num_like,
1721 l_struct_name_like) LOOP
1722 l_segs_count := l_segs_count + 1;
1723 END LOOP;
1724 debug('Number of KFF segments to upgrade : ' || To_char(l_segs_count));
1725
1726 -- go one-by-one
1727 FOR i IN 1..l_segs_count LOOP
1728 --
1729 -- Since cursor data is changed every time, we will fetch one row
1730 -- at a time.
1731 --
1732 OPEN kff_cur(l_application_id, p_id_flex_code,
1733 l_old_vset_rec.flex_value_set_id,
1734 l_struct_num_like, l_struct_name_like);
1735 FETCH kff_cur INTO kff_rec;
1736 CLOSE kff_cur;
1737 debug('Modifying:' || p_appl_short_name || ':' ||
1738 kff_rec.id_flex_code || ':' ||
1739 kff_rec.id_flex_structure_name || ':' ||
1740 kff_rec.segment_name);
1741 l_default_value := kff_rec.default_value;
1742 IF (kff_rec.default_type = 'C') THEN
1743 IF (NOT is_to_standard_date(kff_rec.default_value,
1744 l_default_value, l_error)) THEN
1745 --
1746 -- If type is constant then default_value is required,
1747 -- set it to something.
1748 --
1749 message(l_error);
1750 message(l_func_name || chr_newline ||
1751 'Default Value is not in proper format.' ||chr_newline||
1752 'Default Value : ' ||
1753 kff_rec.default_value || chr_newline ||
1754 'It is set to 1000/01/01 00:00:00');
1755 l_default_value := '1000/01/01 00:00:00';
1756 l_ret_code := g_ret_ignored_errors;
1757 END IF;
1758 END IF;
1759
1760 BEGIN
1761 UPDATE fnd_id_flex_segments
1762 SET flex_value_set_id = l_new_vset_rec.flex_value_set_id,
1763 default_value = l_default_value,
1764 last_update_date = l_who_rec.last_update_date,
1765 last_updated_by = l_who_rec.last_updated_by
1766 WHERE application_id = kff_rec.application_id
1767 AND id_flex_code = kff_rec.id_flex_code
1768 AND id_flex_num = kff_rec.id_flex_num
1769 AND application_column_name = kff_rec.application_column_name;
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 report_error(l_func_name,
1773 'Failure in UPDATE fnd_id_flex_segments.', Sqlerrm);
1774 l_ret_code := g_ret_ignored_errors;
1775 END;
1776
1777 COMMIT;
1778 END LOOP; -- FOR i IN 1..l_segs_count
1779
1780 report_success(l_func_name,
1781 'KFF segments upgraded for : ' || p_appl_short_name);
1782 message('Calling !!!COMMIT!!! ...');
1783 COMMIT;
1784 RETURN(l_ret_code);
1785 EXCEPTION
1786 WHEN OTHERS THEN
1787 report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
1788 RETURN(g_ret_critical_error);
1789 END upgrade_date_kff_segments;
1790
1791 -- --------------------------------------------------
1792 FUNCTION upgrade_vset_to_translatable
1793 (p_vset_name IN VARCHAR2,
1794 p_session_mode IN VARCHAR2 DEFAULT 'customer_data')
1795 RETURN NUMBER
1796 IS
1797 l_func_name VARCHAR2(80) := (g_package_name ||
1798 'upgrade_vset_to_translatable');
1799 l_ret_code NUMBER := g_ret_no_error;
1800 l_ind_vset_rec vset_rec_type; -- Parent
1801 l_dep_vset_rec vset_rec_type; -- Child
1802 l_who_rec who_rec_type;
1803 l_error VARCHAR2(2000);
1804 l_trans_msg VARCHAR2(32000);
1805 l_num_segs NUMBER;
1806
1807 CURSOR dep_vset_cur(p_ind_vset_id IN NUMBER) IS
1808 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
1809 *
1810 FROM fnd_flex_value_sets
1811 WHERE validation_type = 'D'
1812 AND parent_flex_value_set_id = p_ind_vset_id;
1813
1814 PROCEDURE debug_vset_details(p_vset_rec IN vset_rec_type)
1815 IS
1816 BEGIN
1817 debug('Value Set Details : ');
1818 debug('Value Set Name : ' || p_vset_rec.flex_value_set_name);
1819 debug('Value Set Id : ' || p_vset_rec.flex_value_set_id);
1820 debug('Validation Type : ' || p_vset_rec.validation_type);
1821 debug('Format Type : ' || p_vset_rec.format_type);
1822 debug('Maximum Size : ' || p_vset_rec.maximum_size);
1823 debug('Minimum Value : ' || p_vset_rec.minimum_value);
1824 debug('Maximum Value : ' || p_vset_rec.maximum_value);
1825 END;
1826 BEGIN
1827 internal_init;
1828 --
1829 -- WHO columns
1830 --
1831 IF (NOT set_get_who(p_session_mode, l_who_rec, l_error)) THEN
1832 message(l_error);
1833 RETURN(g_ret_critical_error);
1834 END IF;
1835
1836 --
1837 -- Get the Independent value set.
1838 --
1839 IF (NOT get_value_set(p_vset_name, l_ind_vset_rec, l_error)) THEN
1840 message(l_error);
1841 RETURN(g_ret_critical_error);
1842 END IF;
1843 debug_vset_details(l_ind_vset_rec);
1844
1845 --
1846 -- Must be independent.
1847 --
1848 IF (l_ind_vset_rec.validation_type <> 'I') THEN
1849 message('ERROR: Not an Independent value set.');
1850 RETURN(g_ret_critical_error);
1851 END IF;
1852
1853 --
1854 -- Must be Character format type.
1855 --
1856 IF (l_ind_vset_rec.format_type <> 'C') THEN
1857 message('ERROR: Not a Character type value set.');
1858 RETURN(g_ret_critical_error);
1859 END IF;
1860
1861 --
1862 -- Must be non-secured value set.
1863 --
1864 IF (l_ind_vset_rec.security_enabled_flag = 'Y') THEN
1865 message('ERROR: This value set is a secured value set. ' ||
1866 'Secured value sets cannot be translatable. ');
1867 RETURN(g_ret_critical_error);
1868 END IF;
1869
1870 --
1871 -- Must be alphanumeric allowed.
1872 --
1873 IF (l_ind_vset_rec.alphanumeric_allowed_flag = 'N') THEN
1874 message('ERROR: This value set is a Numbers Only value set. ' ||
1875 'Numbers only value sets cannot be translatable. ');
1876 RETURN(g_ret_critical_error);
1877 END IF;
1878
1879 --
1880 -- Numeric mode must be disabled.
1881 --
1882 IF (l_ind_vset_rec.numeric_mode_enabled_flag = 'Y') THEN
1883 message('ERROR: This value set is a Right Justify Zero Fill value ' ||
1884 'set. RJZF value sets cannot be translatable. ');
1885 RETURN(g_ret_critical_error);
1886 END IF;
1887
1888 --
1889 -- Must not be Upper Case only.
1890 --
1891 IF (l_ind_vset_rec.uppercase_only_flag = 'Y') THEN
1892 message('ERROR: This value set is an Uppercase Only value set. ' ||
1893 'Uppercase Only value sets cannot be Translatable. ');
1894 RETURN(g_ret_critical_error);
1895 END IF;
1896
1897 --
1898 -- Do not allow conversion if the vset is attached
1899 -- to a KFF that does not allow id vsets.
1900 --
1901 BEGIN
1902
1903 SELECT
1904 count(*)
1905 INTO
1906 l_num_segs
1907 FROM
1908 fnd_id_flexs f,
1909 fnd_id_flex_segments s
1910 WHERE
1911 s.flex_value_set_id=l_ind_vset_rec.flex_value_set_id AND
1912 f.application_id=s.application_id AND
1913 f.id_flex_code=s.id_flex_code AND
1914 f.allow_id_valuesets='N';
1915
1916 IF (l_num_segs > 0) THEN
1917
1918 SELECT
1919 ' Segment ''' || fifsg.segment_name ||
1920 ''' Structure ''' || fifst.id_flex_structure_code ||
1921 ''' Key Flex Code ''' || fif.id_flex_code ||
1922 ''' Application Id ''' || fif.application_id
1923 INTO
1924 l_trans_msg
1925 FROM
1926 fnd_id_flexs fif, fnd_id_flex_structures fifst,
1927 fnd_id_flex_segments fifsg
1928 WHERE
1929 fifst.application_id = fif.application_id
1930 and fifst.id_flex_code = fif.id_flex_code
1931 and fifsg.application_id = fifst.application_id
1932 and fifsg.id_flex_code = fifst.id_flex_code
1933 and fifsg.id_flex_num = fifst.id_flex_num
1934 and fifsg.flex_value_set_id = l_ind_vset_rec.flex_value_set_id
1935 and fif.allow_id_valuesets = 'N'
1936 and rownum = 1;
1937
1938 message('ERROR: This value set cannot be converted to a ' ||
1939 'translatable value set because it is attached to ' ||
1940 l_trans_msg || ' This KFF does not allow id validated value sets.');
1941 RETURN(g_ret_critical_error);
1942 END IF;
1943
1944 EXCEPTION
1945 WHEN NO_DATA_FOUND THEN
1946 null;
1947
1948 END;
1949
1950
1951 --
1952 -- Make sure Dependent value sets are OK.
1953 --
1954 FOR dep_vset_rec IN dep_vset_cur(l_ind_vset_rec.flex_value_set_id) LOOP
1955 IF (NOT get_value_set(dep_vset_rec.flex_value_set_name,
1956 l_dep_vset_rec, l_error)) THEN
1957 message(l_error);
1958 RETURN(g_ret_critical_error);
1959 END IF;
1960 debug('');
1961 debug_vset_details(l_dep_vset_rec);
1962 --
1963 -- Must be Character format type.
1964 --
1965 IF (dep_vset_rec.format_type <> 'C') THEN
1966 debug('ERROR: ' || dep_vset_rec.flex_value_set_name ||
1967 ' dependent value set is not Character type.');
1968 RETURN(g_ret_critical_error);
1969 END IF;
1970
1971 --
1972 -- Must be non-secured value set.
1973 --
1974 IF (dep_vset_rec.security_enabled_flag = 'Y') THEN
1975 message('ERROR: This value set is a secured value set. ' ||
1976 'Secured value sets cannot be translatable. ');
1977 RETURN(g_ret_critical_error);
1978 END IF;
1979
1980 --
1981 -- Must be alphanumeric allowed.
1982 --
1983 IF (dep_vset_rec.alphanumeric_allowed_flag = 'N') THEN
1984 message('ERROR: This value set is a Numbers Only value set. ' ||
1985 'Numbers only value sets cannot be translatable. ');
1986 RETURN(g_ret_critical_error);
1987 END IF;
1988
1989 --
1990 -- Numeric mode must be disabled.
1991 --
1992 IF (dep_vset_rec.numeric_mode_enabled_flag = 'Y') THEN
1993 message('ERROR: This value set is a Right Justify Zero Fill value ' ||
1994 'set. RJZF value sets cannot be translatable. ');
1995 RETURN(g_ret_critical_error);
1996 END IF;
1997
1998 --
1999 -- Must not be Upper Case only.
2000 --
2001 IF (dep_vset_rec.uppercase_only_flag = 'Y') THEN
2002 message('ERROR: This value set is an Uppercase Only value set. ' ||
2003 'Uppercase Only value sets cannot be translatable. ');
2004 RETURN(g_ret_critical_error);
2005 END IF;
2006
2007 END LOOP;
2008
2009 --
2010 -- Now we can upgrade. First upgrade the dependent vsets.
2011 --
2012 FOR dep_vset_rec IN dep_vset_cur(l_ind_vset_rec.flex_value_set_id) LOOP
2013 UPDATE fnd_flex_value_sets
2014 SET
2015 validation_type = 'Y',
2016 last_update_date = l_who_rec.last_update_date,
2017 last_updated_by = l_who_rec.last_updated_by
2018 WHERE flex_value_set_id = dep_vset_rec.flex_value_set_id;
2019 END LOOP;
2020
2021 --
2022 -- Upgrade the independent vset.
2023 --
2024 UPDATE fnd_flex_value_sets
2025 SET
2026 validation_type = 'X',
2027 last_update_date = l_who_rec.last_update_date,
2028 last_updated_by = l_who_rec.last_updated_by
2029 WHERE flex_value_set_id = l_ind_vset_rec.flex_value_set_id;
2030
2031 report_success(l_func_name, p_vset_name || ' value set is upgraded' ||
2032 ' to Translatable Independent.');
2033 message('Calling !!!COMMIT!!! ...');
2034 COMMIT;
2035 RETURN(l_ret_code);
2036 EXCEPTION
2037 WHEN OTHERS THEN
2038 report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
2039 RETURN(g_ret_critical_error);
2040 END upgrade_vset_to_translatable;
2041
2042
2043 -- ======================================================================
2044 -- ======================================================================
2045 -- CONCURRENT PROGRAM FUNCTIONS
2046 -- ======================================================================
2047 -- ======================================================================
2048 -- --------------------------------------------------
2049 PROCEDURE cp_debug(p_debug IN VARCHAR2)
2050 IS
2051 l_debug VARCHAR2(32000) := p_debug;
2052 l_len NUMBER := Nvl(Length(l_debug),0);
2053 l_pos NUMBER;
2054 BEGIN
2055 IF (p_debug LIKE 'ERROR%') THEN
2056 g_cp_numof_errors := g_cp_numof_errors + 1;
2057 END IF;
2058
2059 WHILE l_len > 0 LOOP
2060 l_pos := Instr(l_debug, chr_newline, 1, 1);
2061 IF ((l_pos + g_cp_indent > g_line_size) OR (l_pos = 0)) THEN
2062 l_pos := g_line_size - g_cp_indent;
2063 fnd_file.put_line(FND_FILE.LOG,
2064 Lpad(' ',g_cp_indent-1,' ') ||
2065 Substr(l_debug, 1, l_pos));
2066 ELSE
2067 fnd_file.put(FND_FILE.LOG,
2068 Lpad(' ',g_cp_indent-1,' ') ||
2069 Substr(l_debug, 1, l_pos));
2070 END IF;
2071
2072 l_debug := Substr(l_debug, l_pos + 1);
2073 l_len := Nvl(Length(l_debug),0);
2074 END LOOP;
2075 EXCEPTION
2076 WHEN OTHERS THEN
2077 NULL;
2078 END cp_debug;
2079
2080 -- --------------------------------------------------
2081 PROCEDURE cp_report_success(p_func_name IN VARCHAR2,
2082 p_message IN VARCHAR2)
2083 IS
2084 BEGIN
2085 cp_debug(p_func_name || ' successfully completed.');
2086 cp_debug('Message:' || Nvl(p_message, 'None'));
2087 EXCEPTION
2088 WHEN OTHERS THEN
2089 NULL;
2090 END cp_report_success;
2091
2092 -- --------------------------------------------------
2093 PROCEDURE cp_report_error(p_func_name IN VARCHAR2,
2094 p_message IN VARCHAR2,
2095 p_sqlerrm IN VARCHAR2)
2096 IS
2097 BEGIN
2098 cp_debug('ERROR: ' || set_error(p_func_name, p_message, p_sqlerrm));
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 NULL;
2102 END cp_report_error;
2103
2104 -- --------------------------------------------------
2105 PROCEDURE cp_upgrade_table_column
2106 (p_mode IN VARCHAR2,
2107 p_rows_count IN NUMBER,
2108 p_table_name IN VARCHAR2,
2109 p_column_name IN VARCHAR2,
2110 p_use_bind IN BOOLEAN,
2111 p_bind_value IN VARCHAR2,
2112 p_sql_select IN VARCHAR2,
2113 p_sql_update IN VARCHAR2,
2114 x_upg_count OUT nocopy NUMBER)
2115 IS
2116 l_func_name VARCHAR2(80) := (g_package_name ||
2117 'cp_upgrade_table_column');
2118 select_cursor cursor_type;
2119
2120 l_upg_count NUMBER := 0;
2121 j NUMBER;
2122 l_last_rowid ROWID;
2123 l_rowid ROWID;
2124
2125 l_value_old VARCHAR2(2000);
2126 l_value_new VARCHAR2(2000);
2127 l_error VARCHAR2(2000);
2128 l_numof_errors NUMBER;
2129 l_many_errors BOOLEAN;
2130 BEGIN
2131 --
2132 -- p_sql_select is something like
2133 --
2134 -- SELECT rowid, my_column
2135 -- FROM (SELECT rowid, my_column
2136 -- FROM my_table
2137 -- WHERE (my_column IS NOT NULL)
2138 -- AND ((:l_last_rowid IS NULL) OR (ROWID > :l_last_rowid))
2139 -- AND (<my_where_clause>) -- might have bind value
2140 -- ORDER BY ROWID)
2141 -- WHERE (ROWNUM <= :b_block_size)
2142 --
2143 -- p_sql_update is something like
2144 --
2145 -- UPDATE my_table
2146 -- SET my_column = :l_value_new,
2147 -- WHO = <WHO>
2148 -- WHERE ROWID = :l_rowid
2149 --
2150
2151 -- go g_block_size-by-g_block_size.
2152
2153 cp_debug('TABLE.COLUMN:' ||
2154 p_table_name || '.' ||
2155 p_column_name||
2156 ': Rowcount:' || p_rows_count);
2157 l_numof_errors := 0;
2158 l_many_errors := FALSE;
2159 l_upg_count := 0;
2160 j := 1;
2161 l_last_rowid := NULL; -- For the first set of fetch.
2162 WHILE ((j <= p_rows_count) AND (NOT l_many_errors)) LOOP
2163 IF (p_use_bind) THEN
2164 OPEN select_cursor FOR p_sql_select USING l_last_rowid, l_last_rowid, p_bind_value, g_block_size;
2165 ELSE
2166 OPEN select_cursor FOR p_sql_select USING l_last_rowid, l_last_rowid, g_block_size;
2167 END IF;
2168
2169 LOOP
2170 FETCH select_cursor INTO l_rowid, l_value_old;
2171 EXIT WHEN select_cursor%NOTFOUND;
2172
2173 IF (NOT is_to_standard(p_mode, l_value_old,
2174 l_value_new, l_error)) THEN
2175 cp_debug('ERROR: ' || l_error || chr_newline ||
2176 'ROWID: ' || l_rowid || chr_newline ||
2177 'VALUE: ''' || l_value_old || '''');
2178 l_numof_errors := l_numof_errors + 1;
2179 END IF;
2180 IF (l_value_new <> l_value_old) THEN
2181 EXECUTE IMMEDIATE p_sql_update USING l_value_new, l_rowid;
2182 l_upg_count := l_upg_count + 1;
2183 END IF;
2184
2185 END LOOP;
2186 CLOSE select_cursor;
2187 COMMIT;
2188 j := j + g_block_size;
2189 l_last_rowid := l_rowid;
2190 IF (l_numof_errors > g_max_numof_errors) THEN
2191 l_many_errors := TRUE;
2192 END IF;
2193 END LOOP; -- WHILE (j ...
2194 IF (l_many_errors) THEN
2195 cp_debug('ERROR:Too many errors (' || To_char(l_numof_errors) ||
2196 '), upgrade is aborted.');
2197 END IF;
2198 x_upg_count := l_upg_count;
2199 cp_debug('TABLE.COLUMN:' ||
2200 p_table_name || '.' ||
2201 p_column_name ||
2202 ': Upgcount:' || l_upg_count);
2203 EXCEPTION
2204 WHEN OTHERS THEN
2205 x_upg_count := l_upg_count;
2206 cp_report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
2207 END cp_upgrade_table_column;
2208
2209
2210 -- --------------------------------------------------
2211 PROCEDURE cp_upgrade_value_set_private
2212 (p_mode IN VARCHAR2,
2213 p_vset_rec IN vset_rec_type,
2214 p_who_rec IN who_rec_type)
2215 IS
2216 l_func_name VARCHAR2(80) := (g_package_name ||
2217 'cp_upgrade_value_set_private');
2218
2219 l_segs_count NUMBER;
2220 l_ff_last_rowid ROWID;
2221 l_ff_rowid ROWID;
2222
2223 l_rows_count NUMBER;
2224 j NUMBER;
2225
2226 l_sql_select VARCHAR2(2000);
2227 l_sql_update VARCHAR2(2000);
2228 l_addtl_where VARCHAR2(2000);
2229
2230 vset_rec vset_rec_type := p_vset_rec;
2231 l_upg_count NUMBER;
2232
2233 l_vc2_tmp1 VARCHAR2(2000);
2234 l_vc2_tmp2 VARCHAR2(2000);
2235 l_vc2_tmp3 VARCHAR2(2000);
2236 l_error VARCHAR2(2000);
2237
2238 l_use_bind BOOLEAN;
2239 l_bind_value VARCHAR2(2000);
2240
2241 CURSOR dff_cur(p_flex_value_set_id IN NUMBER,
2242 p_ff_last_rowid IN ROWID)
2243 IS
2244 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
2245 df.application_id, df.descriptive_flexfield_name,
2246 df.table_application_id, df.application_table_name,
2247 df.context_column_name,
2248 dfc.descriptive_flex_context_code, dfc.global_flag,
2249 dfcu.application_column_name, dfcu.end_user_column_name,
2250 dfcu.default_type, dfcu.default_value, dfcu.ROWID,
2251 fc.column_type, fc.width
2252 FROM fnd_descriptive_flexs df, fnd_descr_flex_contexts dfc,
2253 fnd_descr_flex_column_usages dfcu, fnd_columns fc
2254 WHERE df.application_id = dfc.application_id
2255 AND df.descriptive_flexfield_name = dfc.descriptive_flexfield_name
2256 AND dfc.application_id = dfcu.application_id
2257 AND dfc.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
2258 AND dfc.descriptive_flex_context_code = dfcu.descriptive_flex_context_code
2259 AND ((fc.application_id, fc.table_id) =
2260 (SELECT ft.application_id, ft.table_id
2261 FROM fnd_tables ft
2262 WHERE ft.application_id = df.table_application_id
2263 AND ft.table_name = df.application_table_name))
2264 AND fc.column_name = dfcu.application_column_name
2265 AND fc.flexfield_usage_code = 'D'
2266 AND dfcu.flex_value_set_id = p_flex_value_set_id
2267 AND dfcu.enabled_flag = 'Y'
2268 AND dfc.enabled_flag = 'Y'
2269 AND (p_ff_last_rowid IS NULL OR p_ff_last_rowid < dfcu.ROWID)
2270 ORDER BY dfcu.ROWID;
2271
2272 dff_rec dff_cur%ROWTYPE;
2273
2274 CURSOR kff_cur(p_flex_value_set_id IN NUMBER,
2275 p_ff_last_rowid IN ROWID)
2276 IS
2277 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
2278 idf.application_id, idf.id_flex_code,
2279 idf.table_application_id, idf.application_table_name,
2280 idf.set_defining_column_name, idf.unique_id_column_name,
2281 ifst.id_flex_num, ifst.id_flex_structure_name,
2282 ifsg.segment_name, ifsg.application_column_name,
2283 ifsg.default_type, ifsg.default_value, ifsg.ROWID,
2284 fc.column_type, fc.width
2285 FROM fnd_id_flexs idf, fnd_id_flex_structures_vl ifst,
2286 fnd_id_flex_segments ifsg, fnd_columns fc
2287 WHERE idf.application_id = ifst.application_id
2288 AND idf.id_flex_code = ifst.id_flex_code
2289 AND ifst.application_id = ifsg.application_id
2290 AND ifst.id_flex_code = ifsg.id_flex_code
2291 AND ifst.id_flex_num = ifsg.id_flex_num
2292 AND ((fc.application_id, fc.table_id) =
2293 (SELECT ft.application_id, ft.table_id
2294 FROM fnd_tables ft
2295 WHERE ft.application_id = idf.table_application_id
2296 AND ft.table_name = idf.application_table_name))
2297 AND fc.column_name = ifsg.application_column_name
2298 AND fc.flexfield_usage_code = 'K'
2299 AND ifsg.flex_value_set_id = p_flex_value_set_id
2300 AND ifst.enabled_flag = 'Y'
2301 AND ifsg.enabled_flag = 'Y'
2302 AND (p_ff_last_rowid IS NULL OR p_ff_last_rowid < ifsg.ROWID)
2303 ORDER BY ifsg.ROWID;
2304
2305 kff_rec kff_cur%ROWTYPE;
2306
2307 CURSOR val_cur(p_flex_value_set_id IN NUMBER)
2308 IS
2309 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
2310 ROWID, flex_value
2311 FROM fnd_flex_values
2312 WHERE flex_value_set_id = p_flex_value_set_id
2313 ORDER BY flex_value;
2314
2315 val_rec val_cur%ROWTYPE;
2316
2317 CURSOR nhier_cur(p_flex_value_set_id IN NUMBER)
2318 IS
2319 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
2320 ROWID, parent_flex_value,
2321 child_flex_value_low, child_flex_value_high
2322 FROM fnd_flex_value_norm_hierarchy
2323 WHERE flex_value_set_id = p_flex_value_set_id;
2324
2325 nhier_rec nhier_cur%ROWTYPE;
2326
2327 CURSOR hier_cur(p_flex_value_set_id IN NUMBER)
2328 IS
2329 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
2330 ROWID, parent_flex_value,
2331 child_flex_value_low, child_flex_value_high
2332 FROM fnd_flex_value_hierarchies
2333 WHERE flex_value_set_id = p_flex_value_set_id;
2334
2335 hier_rec hier_cur%ROWTYPE;
2336
2337 CURSOR par_cur(p_flex_value_set_id IN NUMBER)
2338 IS
2339 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
2340 ROWID, parent_flex_value_low
2341 FROM fnd_flex_values
2342 WHERE flex_value_set_id IN
2343 (SELECT flex_value_set_id
2344 FROM fnd_flex_value_sets
2345 WHERE parent_flex_value_set_id = p_flex_value_set_id
2346 AND validation_type = 'D');
2347
2348 par_rec par_cur%ROWTYPE;
2349
2350 is_id_value_set BOOLEAN := FALSE;
2351 BEGIN
2352
2353 EXECUTE IMMEDIATE
2354 'alter session set sort_area_size = 10000000';
2355
2356 cp_debug(' ');
2357 cp_debug('VALUE SET:<id>:<name>:<validation type>:<format type>');
2358 cp_debug('VALUE SET:' || vset_rec.flex_value_set_id || ':' ||
2359 vset_rec.flex_value_set_name || ':' ||
2360 vset_rec.validation_type || ':' ||
2361 vset_rec.format_type);
2362 cp_debug('SYSDATE :' || To_char(Sysdate, g_mask_new));
2363 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
2364
2365 --
2366 -- For ID type value sets we store id's in application columns.
2367 --
2368 IF (NOT is_id_value_set_success(vset_rec, is_id_value_set, l_error)) THEN
2369 cp_debug('ERROR: ' || l_error);
2370 is_id_value_set := TRUE;
2371 END IF;
2372 IF (is_id_value_set) THEN
2373 cp_debug('ID VALUE SET.');
2374 END IF;
2375
2376 g_cp_indent := g_cp_indent + 5;
2377 --
2378 -- Update minimum, maximum, and dependant_default_value
2379 --
2380 IF (NOT is_to_standard(p_mode, vset_rec.minimum_value,
2381 l_vc2_tmp1, l_error)) THEN
2382 cp_debug('ERROR: VSET.MINIMUM_VALUE :' ||
2383 vset_rec.minimum_value || ': ' || l_error);
2384 END IF;
2385
2386 IF (NOT is_to_standard(p_mode, vset_rec.maximum_value,
2387 l_vc2_tmp2, l_error)) THEN
2388 cp_debug('ERROR: VSET.MAXIMUM_VALUE :' ||
2389 vset_rec.maximum_value || ': ' || l_error);
2390 END IF;
2391
2392 IF (NOT is_to_standard(p_mode, vset_rec.dependant_default_value,
2393 l_vc2_tmp3, l_error)) THEN
2394 cp_debug('ERROR: VSET.DEPENDANT_DEFAULT_VALUE :' ||
2395 vset_rec.dependant_default_value || ': ' || l_error);
2396 END IF;
2397
2398 l_upg_count := 0;
2399 IF (p_mode = g_number_mode) THEN
2400 --
2401 -- For number value sets alphanumeric_allowed_flag should be set to 'N'.
2402 -- If not fix it.
2403 --
2404 IF (vset_rec.minimum_value <> l_vc2_tmp1 OR
2405 vset_rec.maximum_value <> l_vc2_tmp2 OR
2406 vset_rec.dependant_default_value <> l_vc2_tmp3 OR
2407 vset_rec.alphanumeric_allowed_flag <> 'N') THEN
2408 BEGIN
2409 UPDATE fnd_flex_value_sets
2410 SET
2411 alphanumeric_allowed_flag = 'N',
2412 minimum_value = l_vc2_tmp1,
2413 maximum_value = l_vc2_tmp2,
2414 dependant_default_value = l_vc2_tmp3,
2415 last_update_date = p_who_rec.last_update_date,
2416 last_updated_by = p_who_rec.last_updated_by
2417 WHERE flex_value_set_id = vset_rec.flex_value_set_id;
2418 l_upg_count := SQL%rowcount;
2419 EXCEPTION
2420 WHEN OTHERS THEN
2421 cp_report_error(l_func_name,
2422 'Failure in UPDATE FND_FLEX_VALUE_SETS.',
2423 Sqlerrm);
2424 END;
2425 END IF;
2426 ELSIF (p_mode = g_date_mode) THEN
2427 --
2428 -- For std date value sets, set the flags and maximum size.
2429 --
2430 IF (vset_rec.minimum_value <> l_vc2_tmp1 OR
2431 vset_rec.maximum_value <> l_vc2_tmp2 OR
2432 vset_rec.dependant_default_value <> l_vc2_tmp3 OR
2433 vset_rec.maximum_size <> 11 OR
2434 vset_rec.alphanumeric_allowed_flag <> 'Y' OR
2435 vset_rec.uppercase_only_flag <> 'Y' OR
2436 vset_rec.numeric_mode_enabled_flag <> 'N') THEN
2437 BEGIN
2438 UPDATE fnd_flex_value_sets
2439 SET
2440 maximum_size = 11,
2441 alphanumeric_allowed_flag = 'Y',
2442 uppercase_only_flag = 'Y',
2443 numeric_mode_enabled_flag = 'N',
2444 minimum_value = l_vc2_tmp1,
2445 maximum_value = l_vc2_tmp2,
2446 dependant_default_value = l_vc2_tmp3,
2447 last_update_date = p_who_rec.last_update_date,
2448 last_updated_by = p_who_rec.last_updated_by
2449 WHERE flex_value_set_id = vset_rec.flex_value_set_id;
2450 l_upg_count := SQL%rowcount;
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453 cp_report_error(l_func_name,
2454 'Failure in UPDATE FND_FLEX_VALUE_SETS.',
2455 Sqlerrm);
2456 END;
2457 END IF;
2458 ELSIF (p_mode = g_datetime_mode) THEN
2459 --
2460 -- For std datetime value sets, set the flags and maximum size.
2461 --
2462 IF (vset_rec.minimum_value <> l_vc2_tmp1 OR
2463 vset_rec.maximum_value <> l_vc2_tmp2 OR
2464 vset_rec.dependant_default_value <> l_vc2_tmp3 OR
2465 vset_rec.maximum_size <> 20 OR
2466 vset_rec.alphanumeric_allowed_flag <> 'Y' OR
2467 vset_rec.uppercase_only_flag <> 'Y' OR
2468 vset_rec.numeric_mode_enabled_flag <> 'N') THEN
2469 BEGIN
2470 UPDATE fnd_flex_value_sets
2471 SET
2472 maximum_size = 20,
2473 alphanumeric_allowed_flag = 'Y',
2474 uppercase_only_flag = 'Y',
2475 numeric_mode_enabled_flag = 'N',
2476 minimum_value = l_vc2_tmp1,
2477 maximum_value = l_vc2_tmp2,
2478 dependant_default_value = l_vc2_tmp3,
2479 last_update_date = p_who_rec.last_update_date,
2480 last_updated_by = p_who_rec.last_updated_by
2481 WHERE flex_value_set_id = vset_rec.flex_value_set_id;
2482 l_upg_count := SQL%rowcount;
2483 EXCEPTION
2484 WHEN OTHERS THEN
2485 cp_report_error(l_func_name,
2486 'Failure in UPDATE FND_FLEX_VALUE_SETS.',
2487 Sqlerrm);
2488 END;
2489 END IF;
2490 END IF;
2491 cp_debug('FND_FLEX_VALUE_SETS:Upg/Count:'||l_upg_count||'/1');
2492 IF (l_upg_count > 0) THEN
2493 COMMIT;
2494 END IF;
2495
2496
2497 IF (vset_rec.validation_type IN ('I','D','F')) THEN
2498 --
2499 -- Update fnd_flex_values table.
2500 -- Vset must be Ind, Dep, or Table (We store parent values of
2501 -- Table Vsets in fnd_flex_values table.
2502 --
2503 l_rows_count := 0;
2504 l_upg_count := 0;
2505 FOR val_rec IN val_cur(vset_rec.flex_value_set_id) LOOP
2506 l_rows_count := l_rows_count + 1;
2507 IF (NOT is_to_standard(p_mode, val_rec.flex_value,
2508 l_vc2_tmp1, l_error)) THEN
2509 cp_debug('ERROR: VAL.FLEX_VALUE :' ||
2510 val_rec.flex_value || ': ' || l_error);
2511 END IF;
2512 IF (val_rec.flex_value <> l_vc2_tmp1) THEN
2513 BEGIN
2514 UPDATE fnd_flex_values
2515 SET
2516 flex_value = l_vc2_tmp1,
2517 last_update_date = p_who_rec.last_update_date,
2518 last_updated_by = p_who_rec.last_updated_by
2519 WHERE ROWID = val_rec.ROWID;
2520 l_upg_count := l_upg_count + SQL%rowcount;
2521 EXCEPTION
2522 WHEN OTHERS THEN
2523 cp_report_error(l_func_name,
2524 'Failure in UPDATE FND_FLEX_VALUES.',
2525 Sqlerrm);
2526 END;
2527 END IF;
2528 END LOOP;
2529 IF (l_rows_count > 0) THEN
2530 cp_debug('FND_FLEX_VALUES : Upg/Count : ' ||
2531 l_upg_count || '/' || l_rows_count);
2532 IF (l_upg_count > 0) THEN
2533 COMMIT;
2534 END IF;
2535 END IF;
2536
2537 l_rows_count := 0;
2538 l_upg_count := 0;
2539 FOR nhier_rec IN nhier_cur(vset_rec.flex_value_set_id) LOOP
2540 l_rows_count := l_rows_count + 1;
2541 IF (NOT is_to_standard(p_mode, nhier_rec.parent_flex_value,
2542 l_vc2_tmp1, l_error)) THEN
2543 cp_debug('ERROR: NHIER.PARENT_FLEX_VALUE :' ||
2544 nhier_rec.parent_flex_value || ': ' || l_error);
2545 END IF;
2546 IF (NOT is_to_standard(p_mode, nhier_rec.child_flex_value_low,
2547 l_vc2_tmp2, l_error)) THEN
2548 cp_debug('ERROR: NHIER.CHILD_FLEX_VALUE_LOW :' ||
2549 nhier_rec.child_flex_value_low || ': ' || l_error);
2550 END IF;
2551 IF (NOT is_to_standard(p_mode, nhier_rec.child_flex_value_high,
2552 l_vc2_tmp3, l_error)) THEN
2553 cp_debug('ERROR: NHIER.CHILD_FLEX_VALUE_HIGH :' ||
2554 nhier_rec.child_flex_value_high || ': ' || l_error);
2555 END IF;
2556
2557 IF (nhier_rec.parent_flex_value <> l_vc2_tmp1 OR
2558 nhier_rec.child_flex_value_low <> l_vc2_tmp2 OR
2559 nhier_rec.child_flex_value_high <> l_vc2_tmp3) THEN
2560 BEGIN
2561 UPDATE fnd_flex_value_norm_hierarchy
2562 SET
2563 parent_flex_value = l_vc2_tmp1,
2564 child_flex_value_low = l_vc2_tmp2,
2565 child_flex_value_high = l_vc2_tmp3,
2566 last_update_date = p_who_rec.last_update_date,
2567 last_updated_by = p_who_rec.last_updated_by
2568 WHERE ROWID = nhier_rec.ROWID;
2569 l_upg_count := l_upg_count + SQL%rowcount;
2570 EXCEPTION
2571 WHEN OTHERS THEN
2572 cp_report_error(l_func_name,
2573 'Failure in UPDATE FND_FLEX_VALUE_NORM_HIERARCHY.',
2574 Sqlerrm);
2575 END;
2576 END IF;
2577 END LOOP;
2578 IF (l_rows_count > 0) THEN
2579 cp_debug('FND_FLEX_VALUE_NORM_HIERARCHY : Upg/Count : ' ||
2580 l_upg_count || '/' || l_rows_count);
2581 IF (l_upg_count > 0) THEN
2582 COMMIT;
2583 END IF;
2584 END IF;
2585
2586 l_rows_count := 0;
2587 l_upg_count := 0;
2588 FOR hier_rec IN hier_cur(vset_rec.flex_value_set_id) LOOP
2589 l_rows_count := l_rows_count + 1;
2590 IF (NOT is_to_standard(p_mode, hier_rec.parent_flex_value,
2591 l_vc2_tmp1, l_error)) THEN
2592 cp_debug('ERROR: HIER.PARENT_FLEX_VALUE :' ||
2593 hier_rec.parent_flex_value || ': ' || l_error);
2594 END IF;
2595 IF (NOT is_to_standard(p_mode, hier_rec.child_flex_value_low,
2596 l_vc2_tmp2, l_error)) THEN
2597 cp_debug('ERROR: HIER.CHILD_FLEX_VALUE_LOW :' ||
2598 hier_rec.child_flex_value_low || ': ' || l_error);
2599 END IF;
2600 IF (NOT is_to_standard(p_mode, hier_rec.child_flex_value_high,
2601 l_vc2_tmp3, l_error)) THEN
2602 cp_debug('ERROR: HIER.CHILD_FLEX_VALUE_HIGH :' ||
2603 hier_rec.child_flex_value_high || ': ' || l_error);
2604 END IF;
2605
2606 IF (hier_rec.parent_flex_value <> l_vc2_tmp1 OR
2607 hier_rec.child_flex_value_low <> l_vc2_tmp2 OR
2608 hier_rec.child_flex_value_high <> l_vc2_tmp3) THEN
2609 BEGIN
2610 UPDATE fnd_flex_value_hierarchies
2611 SET
2612 parent_flex_value = l_vc2_tmp1,
2613 child_flex_value_low = l_vc2_tmp2,
2614 child_flex_value_high = l_vc2_tmp3,
2615 last_update_date = p_who_rec.last_update_date,
2616 last_updated_by = p_who_rec.last_updated_by
2617 WHERE ROWID = hier_rec.ROWID;
2618 l_upg_count := l_upg_count + SQL%rowcount;
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 cp_report_error(l_func_name,
2622 'Failure in UPDATE FND_FLEX_VALUE_HIERARCHIES.',
2623 Sqlerrm);
2624 END;
2625 END IF;
2626 END LOOP;
2627 IF (l_rows_count > 0) THEN
2628 cp_debug('FND_FLEX_VALUE_HIERARCHIES : Upg/Count : ' ||
2629 l_upg_count || '/' || l_rows_count);
2630 IF (l_upg_count > 0) THEN
2631 COMMIT;
2632 END IF;
2633 END IF;
2634
2635 END IF;
2636
2637 --
2638 -- Is this value set used as an independent value set by another
2639 -- dependent value set?
2640 -- If so, update parent_flex_value_low column.
2641 --
2642 IF (vset_rec.validation_type = 'I') THEN
2643 l_rows_count := 0;
2644 l_upg_count := 0;
2645 FOR par_rec IN par_cur(vset_rec.flex_value_set_id) LOOP
2646 l_rows_count := l_rows_count + 1;
2647 IF (NOT is_to_standard(p_mode, par_rec.parent_flex_value_low,
2648 l_vc2_tmp1, l_error)) THEN
2649 cp_debug('ERROR: PAR.PARENT_FLEX_VALUE_LOW :' ||
2650 par_rec.parent_flex_value_low || ': ' || l_error);
2651 END IF;
2652 IF (par_rec.parent_flex_value_low <> l_vc2_tmp1) THEN
2653 BEGIN
2654 UPDATE fnd_flex_values
2655 SET
2656 parent_flex_value_low = l_vc2_tmp1,
2657 last_update_date = p_who_rec.last_update_date,
2658 last_updated_by = p_who_rec.last_updated_by
2659 WHERE ROWID = par_rec.ROWID;
2660 l_upg_count := l_upg_count + SQL%rowcount;
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 cp_report_error(l_func_name,
2664 'Failure in UPDATE PARENT FND_FLEX_VALUES.',
2665 Sqlerrm);
2666 END;
2667 END IF;
2668 END LOOP;
2669 IF (l_rows_count > 0) THEN
2670 cp_debug('PARENT FND_FLEX_VALUES : Upg/Count : ' ||
2671 l_upg_count || '/' || l_rows_count);
2672 IF (l_upg_count > 0) THEN
2673 COMMIT;
2674 END IF;
2675 END IF;
2676
2677 END IF;
2678
2679 --
2680 -- Fix DFF.
2681 --
2682 --
2683 -- Count the segments, this includes $SRS$ DFFs.
2684 --
2685 l_segs_count := 0;
2686 FOR dff_rec IN dff_cur(vset_rec.flex_value_set_id, NULL) LOOP
2687 l_segs_count := l_segs_count + 1;
2688 END LOOP;
2689 IF (l_segs_count > 0) THEN
2690 cp_debug('Upgrading Descriptive Flexfields.');
2691 cp_debug('Number of DFF segments:'|| To_char(l_segs_count));
2692 cp_debug('DFF:<app id>:<name>:<context code>:<column name>:<user name>');
2693 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
2694 END IF;
2695 g_cp_indent := g_cp_indent + 10;
2696
2697 -- go one-by-one
2698 l_ff_last_rowid := NULL;
2699 FOR i IN 1..l_segs_count LOOP
2700 --
2701 -- Since cursor data is changed every time, we will fetch one row
2702 -- at a time.
2703 --
2704 OPEN dff_cur(vset_rec.flex_value_set_id, l_ff_last_rowid);
2705 FETCH dff_cur INTO dff_rec;
2706 CLOSE dff_cur;
2707 l_ff_last_rowid := dff_rec.ROWID;
2708
2709 g_cp_indent := g_cp_indent - 5;
2710 cp_debug('DFF:' || dff_rec.application_id || ':' ||
2711 dff_rec.descriptive_flexfield_name || ':' ||
2712 dff_rec.descriptive_flex_context_code || ':' ||
2713 dff_rec.application_column_name || ':' ||
2714 dff_rec.end_user_column_name);
2715 g_cp_indent := g_cp_indent + 5;
2716
2717 IF (dff_rec.default_type = 'C') THEN
2718 IF (NOT is_to_standard(p_mode, dff_rec.default_value,
2719 l_vc2_tmp1, l_error)) THEN
2720 cp_debug('ERROR: DFF.DEFAULT_VALUE :' ||
2721 dff_rec.default_value || ': ' || l_error);
2722 END IF;
2723 IF (dff_rec.default_value <> l_vc2_tmp1) THEN
2724 BEGIN
2725 UPDATE fnd_descr_flex_column_usages
2726 SET
2727 default_value = l_vc2_tmp1,
2728 last_update_date = p_who_rec.last_update_date,
2729 last_updated_by = p_who_rec.last_updated_by
2730 WHERE application_id = dff_rec.application_id
2731 AND descriptive_flexfield_name = dff_rec.descriptive_flexfield_name
2732 AND descriptive_flex_context_code = dff_rec.descriptive_flex_context_code
2733 AND application_column_name = dff_rec.application_column_name;
2734 COMMIT;
2735 EXCEPTION
2736 WHEN OTHERS THEN
2737 cp_report_error
2738 (l_func_name,
2739 'Failure in UPDATE FND_DESCR_FLEX_COLUMN_USAGES.',
2740 Sqlerrm);
2741 END;
2742 END IF;
2743 END IF;
2744 --
2745 -- Now we need to upgrade underlying application table.
2746 --
2747 -- FND_SRS_MASTER is a fake table.
2748 --
2749 -- C: Char, U: Varchar, V: Varchar2
2750 --
2751 IF ((Substr(dff_rec.descriptive_flexfield_name,1,6) <> '$SRS$.') AND
2752 (dff_rec.column_type IN ('C', 'U', 'V')) AND
2753 ((p_mode IN (g_date_mode, g_datetime_mode) AND
2754 dff_rec.width >= 20) OR
2755 (p_mode NOT IN (g_date_mode, g_datetime_mode))) AND
2756 (NOT is_id_value_set) AND
2757 (NOT is_fake_table(dff_rec.table_application_id, dff_rec.application_table_name))) THEN
2758 --
2759 -- First count the rows.
2760 --
2761 l_rows_count := 0;
2762 l_sql_select := ('SELECT COUNT(*)' ||
2763 ' FROM ' || dff_rec.application_table_name ||
2764 ' WHERE (''/* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */'' IS NOT NULL)');
2765
2766 l_addtl_where := (' AND (' || dff_rec.application_column_name ||
2767 ' IS NOT NULL)');
2768
2769 IF (p_mode = g_number_mode) THEN
2770 l_addtl_where := l_addtl_where ||
2771 ' AND (RTRIM(' || dff_rec.application_column_name ||
2772 ', ''0123456789'') IS NOT NULL)';
2773 END IF;
2774
2775 --
2776 -- Non-Global Contexts.
2777 --
2778 IF (dff_rec.global_flag = 'N') THEN
2779 l_use_bind := TRUE;
2780 l_bind_value := dff_rec.descriptive_flex_context_code;
2781 l_addtl_where := l_addtl_where ||
2782 ' AND (' || dff_rec.context_column_name || ' = :b_bind_value)';
2783 ELSE
2784 l_use_bind := FALSE;
2785 END IF;
2786
2787 l_sql_select := l_sql_select || l_addtl_where;
2788
2789 BEGIN
2790 IF (l_use_bind) THEN
2791 EXECUTE IMMEDIATE l_sql_select INTO l_rows_count USING l_bind_value;
2792 ELSE
2793 EXECUTE IMMEDIATE l_sql_select INTO l_rows_count;
2794 END IF;
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 l_rows_count := 0;
2798 cp_report_error(l_func_name, 'DFF:Count(*) failed. ' ||
2799 'TABLE.COL:'|| dff_rec.application_table_name ||
2800 '.' || dff_rec.application_column_name,
2801 Sqlerrm);
2802 END;
2803
2804 --
2805 -- Construct the SELECT and UPDATE statements.
2806 --
2807 IF (l_rows_count > 0) THEN
2808 l_sql_select :=
2809 'SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
2810 ' ROWID, ' || dff_rec.application_column_name ||
2811 ' FROM ' ||
2812 '(SELECT ROWID, ' || dff_rec.application_column_name ||
2813 ' FROM ' || dff_rec.application_table_name ||
2814 ' WHERE ((:l_last_rowid IS NULL) OR (ROWID > :l_last_rowid))';
2815
2816 l_sql_select := l_sql_select || l_addtl_where;
2817 l_sql_select := l_sql_select || ' ORDER BY ROWID)';
2818 l_sql_select := l_sql_select || ' WHERE (ROWNUM <= :b_block_size)';
2819
2820 l_sql_update := get_sql_update(dff_rec.application_id,
2821 dff_rec.application_table_name,
2822 dff_rec.application_column_name,
2823 p_who_rec);
2824
2825 cp_upgrade_table_column(p_mode, l_rows_count,
2826 dff_rec.application_table_name,
2827 dff_rec.application_column_name,
2828 l_use_bind, l_bind_value,
2829 l_sql_select, l_sql_update, l_upg_count);
2830
2831 END IF; -- (l_rows_count > 0)
2832 END IF; -- Upgrade table.
2833 END LOOP; -- FOR i IN 1..l_segs_count
2834
2835 --
2836 -- Fix KFF
2837 --
2838 --
2839 -- Count the segments.
2840 --
2841 l_segs_count := 0;
2842 FOR kff_rec IN kff_cur(vset_rec.flex_value_set_id, NULL) LOOP
2843 l_segs_count := l_segs_count + 1;
2844 END LOOP;
2845
2846 g_cp_indent := g_cp_indent - 10;
2847 IF (l_segs_count > 0) THEN
2848 cp_debug('Upgrading Key Flexfields.');
2849 cp_debug('Number of KFF segments:' || To_char(l_segs_count));
2850 cp_debug('KFF:<app id>:<code>:<str num>:<str name>:<column name>:<segment name>');
2851 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
2852 END IF;
2853 g_cp_indent := g_cp_indent + 10;
2854
2855
2856 -- go one-by-one
2857 l_ff_last_rowid := NULL;
2858 FOR i IN 1..l_segs_count LOOP
2859 --
2860 -- Since cursor data is changed every time, we will fetch one row
2861 -- at a time.
2862 --
2863 OPEN kff_cur(vset_rec.flex_value_set_id, l_ff_last_rowid);
2864 FETCH kff_cur INTO kff_rec;
2865 CLOSE kff_cur;
2866 l_ff_last_rowid := kff_rec.ROWID;
2867
2868 g_cp_indent := g_cp_indent - 5;
2869 cp_debug('KFF:' || kff_rec.application_id || ':' ||
2870 kff_rec.id_flex_code || ':' ||
2871 kff_rec.id_flex_num || ':' ||
2872 kff_rec.id_flex_structure_name || ':' ||
2873 kff_rec.application_column_name || ':' ||
2874 kff_rec.segment_name);
2875 g_cp_indent := g_cp_indent + 5;
2876
2877 IF (kff_rec.default_type = 'C') THEN
2878 IF (NOT is_to_standard(p_mode, kff_rec.default_value,
2879 l_vc2_tmp1, l_error)) THEN
2880 cp_debug('ERROR: KFF.DEFAULT_VALUE :' ||
2881 kff_rec.default_value || ': ' || l_error);
2882 END IF;
2883 IF (dff_rec.default_value <> l_vc2_tmp1) THEN
2884 BEGIN
2885 UPDATE fnd_id_flex_segments
2886 SET
2887 default_value = l_vc2_tmp1,
2888 last_update_date = p_who_rec.last_update_date,
2889 last_updated_by = p_who_rec.last_updated_by
2890 WHERE application_id = kff_rec.application_id
2891 AND id_flex_code = kff_rec.id_flex_code
2892 AND id_flex_num = kff_rec.id_flex_num
2893 AND application_column_name = kff_rec.application_column_name;
2894 COMMIT;
2895 EXCEPTION
2896 WHEN OTHERS THEN
2897 cp_report_error(l_func_name,
2898 'Failure in UPDATE FND_ID_FLEX_SEGMENTS.',
2899 Sqlerrm);
2900 END;
2901 END IF;
2902 END IF;
2903
2904 --
2905 -- Now we need to upgrade underlying application table.
2906 --
2907 -- C: Char, U: Varchar, V: Varchar2
2908 --
2909 -- KFF tables cannot be fake tables.
2910 --
2911 IF ((kff_rec.column_type IN ('C', 'U', 'V')) AND
2912 ((p_mode IN (g_date_mode, g_datetime_mode) AND
2913 kff_rec.width >= 20) OR
2914 (p_mode NOT IN (g_date_mode, g_datetime_mode))) AND
2915 (NOT is_id_value_set)) THEN
2916 --
2917 -- First count the rows.
2918 --
2919 l_rows_count := 0;
2920 l_sql_select := ('SELECT COUNT(*)' ||
2921 ' FROM ' || kff_rec.application_table_name ||
2922 ' WHERE (''/* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */'' IS NOT NULL)');
2923
2924 l_addtl_where := (' AND (' || kff_rec.application_column_name ||
2925 ' IS NOT NULL)');
2926
2927 IF (p_mode = g_number_mode) THEN
2928 l_addtl_where := l_addtl_where ||
2929 ' AND (RTRIM(' || kff_rec.application_column_name ||
2930 ', ''0123456789'') IS NOT NULL)';
2931 END IF;
2932
2933 --
2934 -- These are single structure key flexfields.
2935 --
2936 IF ((kff_rec.set_defining_column_name IS NULL) OR
2937 ((kff_rec.application_id = 401) AND (kff_rec.id_flex_code = 'MSTK')) OR
2938 ((kff_rec.application_id = 401) AND (kff_rec.id_flex_code = 'MTLL')) OR
2939 ((kff_rec.application_id = 401) AND (kff_rec.id_flex_code = 'MICG')) OR
2940 ((kff_rec.application_id = 401) AND (kff_rec.id_flex_code = 'MDSP')) OR
2941 ((kff_rec.application_id = 401) AND (kff_rec.id_flex_code = 'SERV'))) THEN
2942 l_use_bind := FALSE;
2943 ELSE
2944 l_use_bind := TRUE;
2945 l_bind_value := To_char(kff_rec.id_flex_num);
2946 l_addtl_where := l_addtl_where ||
2947 ' AND (' || kff_rec.set_defining_column_name ||
2948 ' = to_number(:b_bind_value))';
2949 END IF;
2950
2951 l_sql_select := l_sql_select || l_addtl_where;
2952
2953 BEGIN
2954 IF (l_use_bind) THEN
2955 EXECUTE IMMEDIATE l_sql_select INTO l_rows_count USING l_bind_value;
2956 ELSE
2957 EXECUTE IMMEDIATE l_sql_select INTO l_rows_count;
2958 END IF;
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 l_rows_count := 0;
2962 cp_report_error(l_func_name,'KFF:Count(*) failed. ' ||
2963 'TABLE.COL:'|| kff_rec.application_table_name ||
2964 '.' || kff_rec.application_column_name,
2965 Sqlerrm);
2966 END;
2967
2968 --
2969 -- Construct the SELECT and UPDATE statements.
2970 --
2971 IF (l_rows_count > 0) THEN
2972 l_sql_select :=
2973 'SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
2974 ' ROWID, ' || kff_rec.application_column_name ||
2975 ' FROM ' ||
2976 '(SELECT ROWID, ' || kff_rec.application_column_name ||
2977 ' FROM ' || kff_rec.application_table_name ||
2978 ' WHERE ((:l_last_rowid IS NULL) OR (ROWID > :l_last_rowid))';
2979
2980 l_sql_select := l_sql_select || l_addtl_where;
2981 l_sql_select := l_sql_select || ' ORDER BY ROWID)';
2982 l_sql_select := l_sql_select || ' WHERE (ROWNUM <= :b_block_size)';
2983
2984 l_sql_update := get_sql_update(kff_rec.application_id,
2985 kff_rec.application_table_name,
2986 kff_rec.application_column_name,
2987 p_who_rec);
2988
2989 cp_upgrade_table_column(p_mode, l_rows_count,
2990 kff_rec.application_table_name,
2991 kff_rec.application_column_name,
2992 l_use_bind, l_bind_value,
2993 l_sql_select, l_sql_update, l_upg_count);
2994 END IF;
2995
2996 END IF; -- Upgrade table.
2997 END LOOP; -- FOR i IN 1..l_segs_count
2998 EXCEPTION
2999 WHEN OTHERS THEN
3000 cp_report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
3001 RETURN;
3002 END cp_upgrade_value_set_private;
3003
3004
3005 -- ----------------------------------------------------------
3006 -- FNDFFUPG CP Public Procedures.
3007 -- ----------------------------------------------------------
3008 ------------------------------------------------------------
3009 PROCEDURE cp_init(p_param_name IN VARCHAR2,
3010 p_param_value IN VARCHAR2)
3011 IS
3012 l_func_name VARCHAR2(80) := g_package_name || 'cp_init';
3013 l_param_name VARCHAR2(100) := Upper(p_param_name);
3014 l_error VARCHAR2(2000);
3015 BEGIN
3016 IF (l_param_name IS NULL) THEN
3017 cp_report_error(l_func_name, 'Parameter name cannot be NULL.', NULL);
3018 RAISE bad_parameter;
3019 ELSIF (l_param_name = 'SESSION_MODE') THEN
3020 IF (NOT set_who(Nvl(p_param_value, 'customer_data'), l_error)) THEN
3021 cp_report_error(l_func_name, l_error, NULL);
3022 RAISE bad_parameter;
3023 END IF;
3024 cp_debug('DEBUG: SESSION_MODE : ' || g_session_mode);
3025 ELSIF (l_param_name = 'NLS_NUMERIC_CHARACTERS') THEN
3026 IF (NOT set_nls_numeric_characters(p_param_value, l_error)) THEN
3027 cp_report_error(l_func_name, l_error, NULL);
3028 RAISE bad_parameter;
3029 END IF;
3030 cp_debug('DEBUG: NLS_NUMERIC_CHARACTERS : ' || g_nls_chars);
3031 ELSE
3032 cp_report_error(l_func_name,
3033 'Unknown parameter : ' || p_param_name,
3034 NULL);
3035 RAISE bad_parameter;
3036 END IF;
3037 RETURN;
3038 EXCEPTION
3039 WHEN OTHERS THEN
3040 cp_report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
3041 RAISE;
3042 END cp_init;
3043
3044 ------------------------------------------------------------
3045 PROCEDURE cp_upgrade_value_set(p_flex_value_set_type IN VARCHAR2,
3046 p_flex_value_set_name IN VARCHAR2)
3047 IS
3048 l_func_name VARCHAR2(80) := g_package_name || 'cp_upgrade_value_set';
3049 l_who_rec who_rec_type;
3050 l_vset_rec vset_rec_type;
3051 l_error VARCHAR2(2000);
3052 BEGIN
3053 internal_init;
3054 --
3055 -- WHO columns.
3056 --
3057 IF (NOT get_who(l_who_rec, l_error)) THEN
3058 cp_debug('ERROR: ' || l_error);
3059 RETURN;
3060 END IF;
3061
3062 --
3063 -- Get value set.
3064 --
3065 IF (NOT get_value_set(p_flex_value_set_name, l_vset_rec, l_error)) THEN
3066 cp_debug('ERROR: ' || l_error);
3067 RETURN;
3068 END IF;
3069
3070 IF (p_flex_value_set_type = 'DATE') THEN
3071 IF (l_vset_rec.format_type = 'X') THEN
3072 cp_upgrade_value_set_private(g_date_mode, l_vset_rec, l_who_rec);
3073 ELSIF (l_vset_rec.format_type = 'Y') THEN
3074 cp_upgrade_value_set_private(g_datetime_mode, l_vset_rec, l_who_rec);
3075 ELSE
3076 cp_report_error(l_func_name,
3077 'Not a Date or DateTime value set.', NULL);
3078 END IF;
3079 ELSIF (p_flex_value_set_type = 'NUMBER') THEN
3080 IF (l_vset_rec.format_type = 'N') THEN
3081 cp_upgrade_value_set_private(g_number_mode, l_vset_rec, l_who_rec);
3082 ELSIF (l_vset_rec.format_type = 'C' AND
3083 l_vset_rec.alphanumeric_allowed_flag = 'N') THEN
3084 cp_upgrade_value_set_private(g_number_mode, l_vset_rec, l_who_rec);
3085 ELSE
3086 cp_report_error(l_func_name,
3087 'Not a Number value set.', NULL);
3088 END IF;
3089 ELSE
3090 cp_report_error(l_func_name,
3091 'Unknown value set type : ' || p_flex_value_set_type,
3092 NULL);
3093 END IF;
3094 COMMIT;
3095 RETURN;
3096 EXCEPTION
3097 WHEN OTHERS THEN
3098 cp_report_error(l_func_name, 'Top Level Exception.', Sqlerrm);
3099 RETURN;
3100 END cp_upgrade_value_set;
3101
3102 -- ----------------------------------------------------------
3103 -- FNDFFUPG CP SRS Procedures.
3104 -- ----------------------------------------------------------
3105
3106 -- ======================================================================
3107 PROCEDURE cp_srs_upgrade_all_private(errbuf OUT nocopy VARCHAR2,
3108 retcode OUT nocopy VARCHAR2,
3109 p_sub_program IN VARCHAR2,
3110 p_sub_description IN VARCHAR2,
3111 p_sql_select IN VARCHAR2)
3112 IS
3113 l_flex_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
3114 l_request_id NUMBER;
3115 l_sub_request_id NUMBER;
3116 l_request_count NUMBER;
3117 i NUMBER;
3118 l_request_data VARCHAR2(100);
3119 l_sub_requests fnd_concurrent.requests_tab_type;
3120 l_action_message VARCHAR2(200);
3121 l_normal_count NUMBER := 0;
3122 l_warning_count NUMBER := 0;
3123 l_error_count NUMBER := 0;
3124 l_vset_cur cursor_type;
3125 BEGIN
3126 l_request_id := fnd_global.conc_request_id;
3127 l_request_data := fnd_conc_global.request_data;
3128
3129 cp_debug('DEBUG: Request Id : ' || l_request_id);
3130 cp_debug('DEBUG: Request Data : ' || l_request_data);
3131 cp_debug(' ');
3132
3133 IF (l_request_data IS NULL) THEN
3134 --
3135 -- Print the header.
3136 --
3137 cp_debug(Lpad('Request ID', 10) || ' ' ||
3138 Rpad('Value Set Name', 60));
3139 cp_debug(Lpad('-',10, '-') || ' ' ||
3140 Rpad('-',60, '-'));
3141
3142 BEGIN
3143 OPEN l_vset_cur FOR p_sql_select;
3144 l_request_count := 0;
3145 LOOP
3146 FETCH l_vset_cur INTO l_flex_value_set_name;
3147 EXIT WHEN l_vset_cur%NOTFOUND;
3148 l_request_count := l_request_count + 1;
3149
3150 IF (p_sub_program = 'FNDFFUPG_DATE_ONE') THEN
3151 l_sub_request_id := fnd_request.submit_request
3152 (application => 'FND',
3153 program => p_sub_program,
3154 description => p_sub_description ||' : '|| l_flex_value_set_name,
3155 start_time => NULL,
3156 sub_request => TRUE,
3157 argument1 => l_flex_value_set_name);
3158 ELSIF (p_sub_program = 'FNDFFUPG_NUMBER_ONE') THEN
3159 l_sub_request_id := fnd_request.submit_request
3160 (application => 'FND',
3161 program => p_sub_program,
3162 description => p_sub_description ||' : '|| l_flex_value_set_name,
3163 start_time => NULL,
3164 sub_request => TRUE,
3165 argument1 => l_flex_value_set_name,
3166 argument2 => g_nls_chars);
3167 END IF;
3168
3169 cp_debug(Lpad(l_sub_request_id, 10) || ' ' ||
3170 Rpad(l_flex_value_set_name, 60));
3171
3172 IF (l_sub_request_id = 0) THEN
3173 cp_debug('ERROR : Unable to submit sub request.');
3174 cp_debug('MESSAGE : ' || fnd_message.get);
3175 END IF;
3176 END LOOP;
3177 CLOSE l_vset_cur;
3178 EXCEPTION
3179 WHEN OTHERS THEN
3180 IF (l_vset_cur%isopen) THEN
3181 CLOSE l_vset_cur;
3182 END IF;
3183 RAISE;
3184 END;
3185
3186 l_request_count := Nvl(l_request_count, 0);
3187
3188 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
3189 request_data => To_char(l_request_count));
3190
3191 errbuf := l_request_count || ' sub request(s) submitted.';
3192 cp_debug(' ');
3193 cp_debug(errbuf);
3194 cp_debug(' ');
3195 retcode := 0;
3196 RETURN;
3197 ELSE
3198 l_request_count := To_number(l_request_data);
3199
3200 cp_debug(l_request_count || ' sub request(s) completed.');
3201 --
3202 -- Print the header.
3203 --
3204 cp_debug(' ');
3205 cp_debug('Status Report');
3206 cp_debug(Rpad('-',72,'-'));
3207 cp_debug(Lpad('Request ID', 10) || ' ' ||
3208 Rpad('Status', 10) || ' ' ||
3209 Rpad('Action', 50));
3210 cp_debug(Lpad('-',10, '-') || ' ' ||
3211 Lpad('-',10, '-') || ' ' ||
3212 Lpad('-',50, '-'));
3213
3214 l_sub_requests := fnd_concurrent.get_sub_requests(l_request_id);
3215 i := l_sub_requests.first;
3216 WHILE i IS NOT NULL LOOP
3217 IF (l_sub_requests(i).dev_status = 'NORMAL') THEN
3218 l_normal_count := l_normal_count + 1;
3219 l_action_message := 'Completed successfully.';
3220 ELSIF (l_sub_requests(i).dev_status = 'WARNING') THEN
3221 l_warning_count := l_warning_count + 1;
3222 l_action_message := 'Warnings reported, please see the sub-request log file.';
3223 ELSIF (l_sub_requests(i).dev_status = 'ERROR') THEN
3224 l_error_count := l_error_count + 1;
3225 l_action_message := 'Errors reported, please see the sub-request log file.';
3226 ELSE
3227 l_error_count := l_error_count + 1;
3228 l_action_message := 'Unknown status reported, please see the sub-request log file.';
3229 END IF;
3230 cp_debug(Lpad(l_sub_requests(i).request_id, 10) || ' ' ||
3231 Rpad(l_sub_requests(i).dev_status, 10) || ' ' ||
3232 l_action_message);
3233 i := l_sub_requests.next(i);
3234 END LOOP;
3235 cp_debug(' ');
3236 cp_debug('Summary Report');
3237 cp_debug(Rpad('-',72,'-'));
3238 cp_debug(Rpad('Status', 20) || ' ' ||
3239 Rpad('Count', 10));
3240 cp_debug(Rpad('-', 20, '-') || ' ' ||
3241 Rpad('-', 10, '-'));
3242 cp_debug(Rpad('Normal', 20) || ' ' ||
3243 Rpad(l_normal_count, 10));
3244 cp_debug(Rpad('Warning', 20) || ' ' ||
3245 Rpad(l_warning_count, 10));
3246 cp_debug(Rpad('Error', 20) || ' ' ||
3247 Rpad(l_error_count, 10));
3248 cp_debug(Rpad('-', 20, '-') || ' ' ||
3249 Rpad('-', 10, '-'));
3250 cp_debug(Rpad('Total', 20) || ' ' ||
3251 Rpad(l_sub_requests.COUNT, 10));
3252 cp_debug(' ');
3253 errbuf := l_sub_requests.COUNT || ' sub request(s) completed.';
3254 IF (l_error_count > 0) THEN
3255 retcode := 2;
3256 ELSIF (l_warning_count > 0) THEN
3257 retcode := 1;
3258 ELSE
3259 retcode := 0;
3260 END IF;
3261 RETURN;
3262 END IF;
3263 EXCEPTION
3264 WHEN OTHERS THEN
3265 retcode := 2;
3266 errbuf := Substr('cp_srs_upgrade_all_private:SQLERRM: ' || Sqlerrm, 1, 240);
3267 END cp_srs_upgrade_all_private;
3268
3269 -- ======================================================================
3270 -- Procedure : cp_srs_upgrade_date_all
3271 -- ======================================================================
3272 -- Upgrades Date Value Sets. (called from SRS form.)
3273 --
3274 PROCEDURE cp_srs_upgrade_date_all(errbuf OUT nocopy VARCHAR2,
3275 retcode OUT nocopy VARCHAR2)
3276 IS
3277 l_sql VARCHAR2(1000) :=
3278 ('SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
3279 ' flex_value_set_name ' ||
3280 ' FROM fnd_flex_value_sets ' ||
3281 ' WHERE format_type IN (''X'', ''Y'')');
3282 BEGIN
3283 internal_init;
3284 cp_debug('Upgrading All Standard Date and Standard DateTime Value Sets.');
3285 cp_debug(' ');
3286
3287 cp_init('SESSION_MODE','customer_data');
3288
3289 cp_srs_upgrade_all_private
3290 (errbuf => errbuf,
3291 retcode => retcode,
3292 p_sub_program => 'FNDFFUPG_DATE_ONE',
3293 p_sub_description => 'Flexfields Upgrade One Standard Date or Standard Date Time Value Set.',
3294 p_sql_select => l_sql );
3295 EXCEPTION
3296 WHEN OTHERS THEN
3297 retcode := 2;
3298 errbuf := Substr('cp_srs_upgrade_date_all:SQLERRM: ' || Sqlerrm, 1, 240);
3299 END cp_srs_upgrade_date_all;
3300
3301 -- ======================================================================
3302 -- Procedure : cp_srs_upgrade_number_all
3303 -- ======================================================================
3304 -- Upgrades All Number Value Sets. (called from SRS.)
3305 --
3306 PROCEDURE cp_srs_upgrade_number_all(errbuf OUT nocopy VARCHAR2,
3307 retcode OUT nocopy VARCHAR2,
3308 p_nls_numeric_characters IN VARCHAR2)
3309 IS
3310 l_sql VARCHAR2(1000) :=
3311 ('SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */ ' ||
3312 ' flex_value_set_name ' ||
3313 ' FROM fnd_flex_value_sets ' ||
3314 ' WHERE (format_type = ''N'' OR ' ||
3315 ' (format_type = ''C'' AND ' ||
3316 ' alphanumeric_allowed_flag = ''N''))');
3317 BEGIN
3318 internal_init;
3319 cp_debug('Upgrading All Number Value Sets.');
3320 cp_debug(' ');
3321
3322 cp_init('SESSION_MODE','customer_data');
3323 cp_init('NLS_NUMERIC_CHARACTERS', p_nls_numeric_characters);
3324
3325 cp_srs_upgrade_all_private
3326 (errbuf => errbuf,
3327 retcode => retcode,
3328 p_sub_program => 'FNDFFUPG_NUMBER_ONE',
3329 p_sub_description => 'Flexfields Upgrade One Number Value Set.',
3330 p_sql_select => l_sql);
3331 EXCEPTION
3332 WHEN OTHERS THEN
3333 retcode := 2;
3334 errbuf := Substr('cp_srs_upgrade_number_all:SQLERRM: ' || Sqlerrm, 1, 240);
3335 END cp_srs_upgrade_number_all;
3336
3337 -- ======================================================================
3338 PROCEDURE cp_srs_upgrade_one_private(errbuf OUT nocopy VARCHAR2,
3339 retcode OUT nocopy VARCHAR2,
3340 p_flex_value_set_type IN VARCHAR2,
3341 p_flex_value_set_name IN VARCHAR2)
3342 IS
3343 BEGIN
3344 cp_debug('DEBUG: Value Set : ' || p_flex_value_set_name);
3345 cp_debug('DEBUG: Request Id : ' || fnd_global.conc_request_id);
3346
3347 cp_upgrade_value_set(p_flex_value_set_type, p_flex_value_set_name);
3348
3349 g_cp_indent := 1;
3350 cp_debug(' ');
3351 cp_debug('Total ' || g_cp_numof_errors || ' error(s) reported.');
3352 cp_debug(' ');
3353
3354 IF (g_cp_numof_errors > 0) THEN
3355 retcode := 2;
3356 errbuf := 'FNDFFUPG_' || p_flex_value_set_type || '_ONE failed. Please see the log file for details.';
3357 ELSE
3358 retcode := 0;
3359 errbuf := 'FNDFFUPG_' || p_flex_value_set_type || '_ONE completed successfully.';
3360 END IF;
3361 EXCEPTION
3362 WHEN OTHERS THEN
3363 retcode := 2;
3364 errbuf := Substr('cp_srs_upgrade_one_private:SQLERRM: ' || Sqlerrm, 1, 240);
3365 END cp_srs_upgrade_one_private;
3366
3367 -- ======================================================================
3368 -- Procedure : cp_srs_upgrade_date_one
3369 -- ======================================================================
3370 -- Upgrades One Standard Date Value Set. (called from SRS.)
3371 --
3372 PROCEDURE cp_srs_upgrade_date_one(errbuf OUT nocopy VARCHAR2,
3373 retcode OUT nocopy VARCHAR2,
3374 p_flex_value_set_name IN VARCHAR2)
3375 IS
3376 BEGIN
3377 internal_init;
3378 cp_debug('Upgrading One Standard Date or Standard DateTime Value Set.');
3379 cp_debug(' ');
3380
3381 cp_init('SESSION_MODE','customer_data');
3382
3383 cp_srs_upgrade_one_private(errbuf => errbuf,
3384 retcode => retcode,
3385 p_flex_value_set_type => 'DATE',
3386 p_flex_value_set_name => p_flex_value_set_name);
3387 EXCEPTION
3388 WHEN OTHERS THEN
3389 retcode := 2;
3390 errbuf := Substr('cp_srs_upgrade_date_one:SQLERRM: ' || Sqlerrm, 1, 240);
3391 END cp_srs_upgrade_date_one;
3392
3393 -- ======================================================================
3394 -- Procedure : cp_srs_upgrade_number_one
3395 -- ======================================================================
3396 -- Upgrades One Number Value Set. (called from SRS.)
3397 --
3398 PROCEDURE cp_srs_upgrade_number_one(errbuf OUT nocopy VARCHAR2,
3399 retcode OUT nocopy VARCHAR2,
3400 p_flex_value_set_name IN VARCHAR2,
3401 p_nls_numeric_characters IN VARCHAR2)
3402 IS
3403 BEGIN
3404 internal_init;
3405 cp_debug('Upgrading One Number Value Set.');
3406 cp_debug(' ');
3407
3408 cp_init('SESSION_MODE','customer_data');
3409 cp_init('NLS_NUMERIC_CHARACTERS', p_nls_numeric_characters);
3410
3411 cp_srs_upgrade_one_private(errbuf => errbuf,
3412 retcode => retcode,
3413 p_flex_value_set_type => 'NUMBER',
3414 p_flex_value_set_name => p_flex_value_set_name);
3415 EXCEPTION
3416 WHEN OTHERS THEN
3417 retcode := 2;
3418 errbuf := Substr('cp_srs_upgrade_number_one:SQLERRM: ' || Sqlerrm, 1, 240);
3419 END cp_srs_upgrade_number_one;
3420
3421 -- ======================================================================
3422 -- Procedure : cp_srs_list_date_usages
3423 -- ======================================================================
3424 -- Lists Date and DateTime Value Set Usages. (called from SRS.)
3425 --
3426 PROCEDURE cp_srs_list_date_usages(errbuf OUT nocopy VARCHAR2,
3427 retcode OUT nocopy VARCHAR2)
3428 IS
3429
3430 CURSOR dff_cur(p_srs_or_dff IN VARCHAR2)
3431 IS
3432 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
3433 fa.application_id,
3434 fa.application_short_name,
3435 fa.application_name,
3436
3437 df.descriptive_flexfield_name,
3438 df.title,
3439
3440 dfc.descriptive_flex_context_code,
3441 dfc.descriptive_flex_context_name,
3442 fl1.meaning context_enabled_flag_lookup,
3443
3444 dfcu.application_column_name,
3445 dfcu.end_user_column_name,
3446 dfcu.form_left_prompt,
3447 fl2.meaning segment_enabled_flag_lookup,
3448
3449 fvs.flex_value_set_id,
3450 fvs.flex_value_set_name,
3451 fvs.maximum_size,
3452 fl3.meaning format_type_lookup
3453
3454 FROM fnd_descr_flex_col_usage_vl dfcu, fnd_descr_flex_contexts_vl dfc,
3455 fnd_descriptive_flexs_vl df, fnd_application_vl fa,
3456 fnd_lookups fl1, fnd_lookups fl2, fnd_lookups fl3,
3457 fnd_flex_value_sets fvs
3458 WHERE df.application_id = fa.application_id
3459 AND dfc.application_id = df.application_id
3460 AND dfc.descriptive_flexfield_name = df.descriptive_flexfield_name
3461 AND dfc.enabled_flag = fl1.lookup_code
3462 AND fl1.lookup_type = 'YES_NO'
3463 AND dfcu.application_id = dfc.application_id
3464 AND dfcu.descriptive_flexfield_name = dfc.descriptive_flexfield_name
3465 AND dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
3466 AND dfcu.enabled_flag = fl2.lookup_code
3467 AND fl2.lookup_type = 'YES_NO'
3468 AND dfcu.flex_value_set_id = fvs.flex_value_set_id
3469 AND fvs.format_type in ('D', 'T')
3470 AND fvs.format_type = fl3.lookup_code
3471 AND fl3.lookup_type = 'FIELD_TYPE'
3472 AND ((p_srs_or_dff = 'SRS' AND
3473 df.descriptive_flexfield_name LIKE '$SRS$.%') OR
3474 (p_srs_or_dff = 'DFF' AND
3475 df.descriptive_flexfield_name NOT LIKE '$SRS$.%'));
3476
3477 CURSOR kff_cur
3478 IS
3479 SELECT /* $Header: AFFFUPUB.pls 120.4.12020000.2 2012/11/30 18:19:14 hgeorgi ship $ */
3480 fa.application_id,
3481 fa.application_short_name,
3482 fa.application_name,
3483
3484 idf.id_flex_code,
3485 idf.id_flex_name,
3486
3487 ifst.id_flex_num,
3488 ifst.id_flex_structure_code,
3489 ifst.id_flex_structure_name,
3490 fl1.meaning structure_enabled_flag_lookup,
3491
3492 ifsg.application_column_name,
3493 ifsg.segment_name,
3494 ifsg.form_left_prompt,
3495 fl2.meaning segment_enabled_flag_lookup,
3496
3497 fvs.flex_value_set_id,
3498 fvs.flex_value_set_name,
3499 fvs.maximum_size,
3500 fl3.meaning format_type_lookup
3501
3502 FROM fnd_id_flex_segments_vl ifsg, fnd_id_flex_structures_vl ifst,
3503 fnd_id_flexs idf, fnd_application_vl fa,
3504 fnd_lookups fl1, fnd_lookups fl2, fnd_lookups fl3,
3505 fnd_flex_value_sets fvs
3506 WHERE idf.application_id = fa.application_id
3507 AND ifst.application_id = idf.application_id
3508 AND ifst.id_flex_code = idf.id_flex_code
3509 AND ifst.enabled_flag = fl1.lookup_code
3510 AND fl1.lookup_type = 'YES_NO'
3511 AND ifsg.application_id = ifst.application_id
3512 AND ifsg.id_flex_code = ifst.id_flex_code
3513 AND ifsg.id_flex_num = ifst.id_flex_num
3514 AND ifsg.flex_value_set_id = fvs.flex_value_set_id
3515 AND ifsg.enabled_flag = fl2.lookup_code
3516 AND fl2.lookup_type = 'YES_NO'
3517 AND fvs.format_type in ('D', 'T')
3518 AND fvs.format_type = fl3.lookup_code
3519 AND fl3.lookup_type = 'FIELD_TYPE';
3520
3521 srs_rec dff_cur%ROWTYPE;
3522 dff_rec dff_cur%ROWTYPE;
3523 kff_rec kff_cur%ROWTYPE;
3524 l_srs_count NUMBER;
3525 l_dff_count NUMBER;
3526 l_kff_count NUMBER;
3527
3528 PROCEDURE cp_print_srs(p_srs_rec IN dff_cur%ROWTYPE)
3529 IS
3530 BEGIN
3531 cp_debug(' ');
3532 cp_debug('Application Id : ' || p_srs_rec.application_id);
3533 cp_debug('Application Short Name : ' || p_srs_rec.application_short_name);
3534 cp_debug('Application Name : ' || p_srs_rec.application_name);
3535
3536 cp_debug('Program Short Name : ' || Substr(p_srs_rec.descriptive_flexfield_name, Length('$SRS$..')));
3537
3538 cp_debug('Parameter Name : ' || p_srs_rec.end_user_column_name);
3539 cp_debug('Parameter Prompt : ' || p_srs_rec.form_left_prompt);
3540 cp_debug('Parameter Enabled? : ' || p_srs_rec.segment_enabled_flag_lookup);
3541
3542 cp_debug('Value Set Id : ' || p_srs_rec.flex_value_set_id);
3543 cp_debug('Value Set Name : ' || p_srs_rec.flex_value_set_name);
3544 cp_debug('Value Set Format Type : ' || p_srs_rec.format_type_lookup);
3545 cp_debug('Value Set Maximum Size : ' || p_srs_rec.maximum_size);
3546 END cp_print_srs;
3547
3548 PROCEDURE cp_print_dff(p_dff_rec IN dff_cur%ROWTYPE)
3549 IS
3550 BEGIN
3551 cp_debug(' ');
3552 cp_debug('Application Id : ' || p_dff_rec.application_id);
3553 cp_debug('Application Short Name : ' || p_dff_rec.application_short_name);
3554 cp_debug('Application Name : ' || p_dff_rec.application_name);
3555
3556 cp_debug('DFF Name : ' || p_dff_rec.descriptive_flexfield_name);
3557 cp_debug('DFF Title : ' || p_dff_rec.title);
3558
3559 cp_debug('Context Code : ' || p_dff_rec.descriptive_flex_context_code);
3560 cp_debug('Context Name : ' || p_dff_rec.descriptive_flex_context_name);
3561 cp_debug('Context Enabled? : ' || p_dff_rec.context_enabled_flag_lookup);
3562
3563 cp_debug('Segment Column Name : ' || p_dff_rec.application_column_name);
3564 cp_debug('Segment Name : ' || p_dff_rec.end_user_column_name);
3565 cp_debug('Segment Prompt : ' || p_dff_rec.form_left_prompt);
3566 cp_debug('Segment Enabled? : ' || p_dff_rec.segment_enabled_flag_lookup);
3567
3568 cp_debug('Value Set Id : ' || p_dff_rec.flex_value_set_id);
3569 cp_debug('Value Set Name : ' || p_dff_rec.flex_value_set_name);
3570 cp_debug('Value Set Format Type : ' || p_dff_rec.format_type_lookup);
3571 cp_debug('Value Set Maximum Size : ' || p_dff_rec.maximum_size);
3572 END cp_print_dff;
3573
3574 PROCEDURE cp_print_kff(p_kff_rec IN kff_cur%ROWTYPE)
3575 IS
3576 BEGIN
3577 cp_debug(' ');
3578 cp_debug('Application Id : ' || p_kff_rec.application_id);
3579 cp_debug('Application Short Name : ' || p_kff_rec.application_short_name);
3580 cp_debug('Application Name : ' || p_kff_rec.application_name);
3581
3582 cp_debug('KFF Code : ' || p_kff_rec.id_flex_code);
3583 cp_debug('KFF Name : ' || p_kff_rec.id_flex_name);
3584
3585 cp_debug('Structure Number : ' || p_kff_rec.id_flex_num);
3586 cp_debug('Structure Code : ' || p_kff_rec.id_flex_structure_code);
3587 cp_debug('Structure Name : ' || p_kff_rec.id_flex_structure_name);
3588 cp_debug('Structure Enabled? : ' || p_kff_rec.structure_enabled_flag_lookup);
3589
3590 cp_debug('Segment Column Name : ' || p_kff_rec.application_column_name);
3591 cp_debug('Segment Name : ' || p_kff_rec.segment_name);
3592 cp_debug('Segment Prompt : ' || p_kff_rec.form_left_prompt);
3593 cp_debug('Segment Enabled? : ' || p_kff_rec.segment_enabled_flag_lookup);
3594
3595 cp_debug('Value Set Id : ' || p_kff_rec.flex_value_set_id);
3596 cp_debug('Value Set Name : ' || p_kff_rec.flex_value_set_name);
3597 cp_debug('Value Set Format Type : ' || p_kff_rec.format_type_lookup);
3598 cp_debug('Value Set Maximum Size : ' || p_kff_rec.maximum_size);
3599 END cp_print_kff;
3600
3601 BEGIN
3602 internal_init;
3603
3604 cp_debug(' ');
3605 cp_debug('Listing the Report Parameters that use Date or DateTime Value Sets.');
3606 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
3607
3608 l_srs_count := 0;
3609 FOR srs_rec IN dff_cur('SRS') LOOP
3610 l_srs_count := l_srs_count + 1;
3611 g_cp_indent := g_cp_indent + 5;
3612 cp_print_srs(srs_rec);
3613 g_cp_indent := g_cp_indent - 5;
3614 END LOOP;
3615
3616 cp_debug(' ');
3617 cp_debug(l_srs_count || ' Report Parameter(s) listed.');
3618
3619 cp_debug(' ');
3620 cp_debug(' ');
3621 cp_debug('Listing the Descriptive Flexfield Segments that use Date or DateTime Value Sets.');
3622 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
3623
3624 l_dff_count := 0;
3625 FOR dff_rec IN dff_cur('DFF') LOOP
3626 l_dff_count := l_dff_count + 1;
3627 g_cp_indent := g_cp_indent + 5;
3628 cp_print_dff(dff_rec);
3629 g_cp_indent := g_cp_indent - 5;
3630 END LOOP;
3631
3632 cp_debug(' ');
3633 cp_debug(l_dff_count || ' Descriptive Flexfield Segment(s) listed.');
3634
3635 cp_debug(' ');
3636 cp_debug(' ');
3637 cp_debug('Listing the Key Flexfield Segments that use Date or DateTime Value Sets.');
3638 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
3639
3640 l_kff_count := 0;
3641 FOR kff_rec IN kff_cur LOOP
3642 l_kff_count := l_kff_count + 1;
3643 g_cp_indent := g_cp_indent + 5;
3644 cp_print_kff(kff_rec);
3645 g_cp_indent := g_cp_indent - 5;
3646 END LOOP;
3647
3648 cp_debug(' ');
3649 cp_debug(l_kff_count || ' Key Flexfield Segment(s) listed.');
3650
3651 retcode := 0;
3652 errbuf := 'FNDFFUPG_LIST_DATE completed successfully.';
3653
3654 cp_debug(' ');
3655 cp_debug(errbuf);
3656 cp_debug(' ');
3657 EXCEPTION
3658 WHEN OTHERS THEN
3659 retcode := 2;
3660 errbuf := Substr('cp_srs_list_date_usages:SQLERRM: ' || Sqlerrm, 1, 240);
3661 END cp_srs_list_date_usages;
3662
3663 -- ======================================================================
3664 -- Procedure : cp_srs_clone_date_vset
3665 -- ======================================================================
3666 -- Clones a Date or DateTime Value Set. (called from SRS.)
3667 --
3668 PROCEDURE cp_srs_clone_date_vset(errbuf OUT nocopy VARCHAR2,
3669 retcode OUT nocopy VARCHAR2,
3670 p_old_value_set_name IN VARCHAR2,
3671 p_new_value_set_name IN VARCHAR2)
3672 IS
3673 l_return_code NUMBER;
3674 BEGIN
3675 internal_init;
3676 set_messaging(TRUE);
3677
3678 l_return_code := clone_date_vset
3679 (p_old_value_set_name => p_old_value_set_name,
3680 p_new_value_set_name => p_new_value_set_name,
3681 p_session_mode => 'customer_data');
3682
3683 cp_debug('Debug messages from Internal Clone Function.');
3684 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
3685 cp_debug(get_message);
3686 cp_debug(Rpad('=',g_cp_max_indent-g_cp_indent,'='));
3687
3688 IF (l_return_code = g_ret_no_error) THEN
3689 retcode := 0;
3690 errbuf := 'FNDFFUPG_CLONE_DATE completed successfully.';
3691 ELSE
3692 retcode := 2;
3693 errbuf := 'FNDFFUPG_CLONE_DATE failed, please see the log file.';
3694 END IF;
3695
3696 cp_debug(' ');
3697 cp_debug(errbuf);
3698 cp_debug(' ');
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 retcode := 2;
3702 errbuf := Substr('cp_srs_clone_date_vset:SQLERRM: ' || Sqlerrm, 1, 240);
3703 END cp_srs_clone_date_vset;
3704
3705
3706 -- ======================================================================
3707 -- Procedure : afffupg1_get_prompt
3708 -- ======================================================================
3709 -- From $FND_TOP/sql/afffupg1.sql.
3710 --
3711 PROCEDURE afffupg1_get_prompt
3712 (p_menu_choice IN NUMBER,
3713 p_step IN NUMBER,
3714 x_prompt OUT nocopy VARCHAR2)
3715 IS
3716 l_prompt VARCHAR2(2000);
3717 BEGIN
3718 l_prompt := NULL;
3719 SELECT Decode
3720 (p_menu_choice,
3721 1, Decode
3722 (p_step,
3723 0, ('-- List Report Parameters: ' ||
3724 'You will be asked to enter 6 inputs. ' ||
3725 'Please ignore the last 4 of them.'),
3726 1, 'Please enter the application short name [% for all] :',
3727 2, 'Please enter the report name like [% for all] : ',
3728 NULL),
3729 2, Decode
3730 (p_step,
3731 0, ('-- List Descriptive Flexfield Segments: '||
3732 'You will be asked to enter 6 inputs. ' ||
3733 'Please ignore the last 3 of them.'),
3734 1, 'Please enter the application short name [% for all] :',
3735 2, 'Please enter the descriptive flexfield name like [% for all] : ',
3736 3, 'Please enter the context code like [% for all] : ',
3737 NULL),
3738 3, Decode
3739 (p_step,
3740 0, ('-- List Key Flexfield Segments: ' ||
3741 'You will be asked to enter 6 inputs. ' ||
3742 'Please ignore the last 2 of them.'),
3743 1, 'Please enter the application short name [% for all] :',
3744 2, 'Please enter the key flexfield code [% for all] : ',
3745 3, 'Please enter the structure number like [% for all] :',
3746 4, 'Please enter the structure name like [% for all] :',
3747 NULL),
3748 4, Decode
3749 (p_step,
3750 0, ('-- Clone a value set: ' ||
3751 'You are about to clone one of the Date or DateTime value ' ||
3752 'sets to Standard Date or Standard DateTime value set. ' ||
3753 'We recommend you use <old_value_set_name>_STANDARD as a ' ||
3754 'new name for your value set. ' ||
3755 'You will be asked to enter 6 inputs. ' ||
3756 'Please ignore the last 4 of them.'),
3757 1, 'Please enter the old value set name :',
3758 2, 'Please enter the new value set name :',
3759 NULL),
3760 5, Decode
3761 (p_step,
3762 0, ('-- Upgrade Report Parameters: ' ||
3763 'You are about to upgrade report parameters which use ' ||
3764 'Date or DateTime value sets, and these value sets will be ' ||
3765 'replaced with Standard Date or Standard DateTime value sets. ' ||
3766 'By using a % sign in the report name, you can upgrade multiple ' ||
3767 'report parameters. ' ||
3768 'You will be asked to enter 6 inputs. ' ||
3769 'Please ignore the last 2 of them.'),
3770 1, 'Please enter the application short name :',
3771 2, 'Please enter the old value set name :',
3772 3, 'Please enter the new value set name :',
3773 4, 'Please enter the report name like [% for all] :',
3774 NULL),
3775 6, Decode
3776 (p_step,
3777 0, ('-- Upgrade Descriptive Flexfield Segments: ' ||
3778 'You are about to upgrade descriptive flexfield segments which ' ||
3779 'use Date or DateTime value sets, and these value sets will be ' ||
3780 'replaced with Standard Date or Standard DateTime value sets. ' ||
3781 'By using a % sign in the descriptive flexfield name, or context ' ||
3782 'code, you can upgrade multiple descriptive flexfields and/or ' ||
3783 'contexts. ' ||
3784 'You will be asked to enter 6 inputs. ' ||
3785 'Please ignore the last one.'),
3786 1, 'Please enter the application short name :',
3787 2, 'Please enter the old value set name :',
3788 3, 'Please enter the new value set name :',
3789 4, 'Please enter the descriptive flexfield name like [% for all] :',
3790 5, 'Please enter the context code like [% for all] :',
3791 NULL),
3792 7, Decode
3793 (p_step,
3794 0, ('-- Upgrade Key Flexfield Segments: ' ||
3795 'You are about to upgrade key flexfield segments which use ' ||
3796 'Date or DateTime value sets, and these value sets will be ' ||
3797 'replaced with Standard Date or Standard DateTime value sets. ' ||
3798 'By using a % sign in the structure number or structure name ' ||
3799 'you can upgrade multiple key flexfield structures. ' ||
3800 'You will be asked to enter 6 inputs.'),
3801 1, 'Please enter the application short name :',
3802 2, 'Please enter the key flexfield code :',
3803 3, 'Please enter the old value set name :',
3804 4, 'Please enter the new value set name :',
3805 5, 'Please enter the structure number like [% for all] :',
3806 6, 'Please enter the structure name like [% for all] :',
3807 NULL),
3808 8, Decode
3809 (p_step,
3810 0, ('-- Upgrade to Translatable Independent/Dependent value set: ' ||
3811 'You are about to upgrade an Independent/Dependent Value set to a ' ||
3812 'Translatable Independent/Dependent Value set. ' ||
3813 'You will be asked to enter the Independent value set name. ' ||
3814 'This script will try to upgrade this value set to a Translatable ' ||
3815 'Independent value set and it will also try to upgrade all ' ||
3816 'dependent value sets (which depend on the given independent ' ||
3817 'value set) to Translatable Dependent value sets. ' ||
3818 'You will be asked to enter 6 inputs. ' ||
3819 'Please ignore the last 5 of them.'),
3820 1, 'Please enter the independent value set name :',
3821 NULL),
3822 Decode
3823 (p_step,
3824 0, 'Invalid menu choice.',
3825 NULL))
3826 INTO l_prompt
3827 FROM dual;
3828
3829 l_prompt := Nvl(l_prompt,
3830 'Please ignore this line and type RETURN to continue :');
3831 IF (p_step = 0) THEN
3832 x_prompt := l_prompt;
3833 ELSE
3834 x_prompt := 'Input ' || p_step || ': ' || l_prompt;
3835 END IF;
3836 END afffupg1_get_prompt;
3837
3838
3839 PROCEDURE afffupg1_data_upgrade
3840 (p_menu_choice IN NUMBER,
3841 p_param1 IN VARCHAR2,
3842 p_param2 IN VARCHAR2,
3843 p_param3 IN VARCHAR2,
3844 p_param4 IN VARCHAR2,
3845 p_param5 IN VARCHAR2,
3846 p_param6 IN VARCHAR2,
3847 x_prompt IN OUT nocopy VARCHAR2)
3848 IS
3849 l_number NUMBER := fnd_flex_upgrade_utilities.g_ret_no_error;
3850 BEGIN
3851 x_prompt := NULL;
3852
3853 SAVEPOINT afffupg1_savepoint;
3854 IF (p_menu_choice = 4) THEN
3855 l_number := fnd_flex_upgrade_utilities.clone_date_vset
3856 (p_old_value_set_name => p_param1,
3857 p_new_value_set_name => p_param2,
3858 p_session_mode => 'customer_data');
3859 ELSIF (p_menu_choice = 5) THEN
3860 l_number := fnd_flex_upgrade_utilities.upgrade_date_report_parameters
3861 (p_appl_short_name => p_param1,
3862 p_value_set_from => p_param2,
3863 p_value_set_to => p_param3,
3864 p_session_mode => 'customer_data',
3865 p_report_name_like => p_param4);
3866 ELSIF (p_menu_choice = 6) THEN
3867 l_number := fnd_flex_upgrade_utilities.upgrade_date_dff_segments
3868 (p_appl_short_name => p_param1,
3869 p_value_set_from => p_param2,
3870 p_value_set_to => p_param3,
3871 p_session_mode => 'customer_data',
3872 p_dff_name_like => p_param4,
3873 p_context_code_like => p_param5);
3874 ELSIF (p_menu_choice = 7) THEN
3875 l_number := fnd_flex_upgrade_utilities.upgrade_date_kff_segments
3876 (p_appl_short_name => p_param1,
3877 p_id_flex_code => p_param2,
3878 p_value_set_from => p_param3,
3879 p_value_set_to => p_param4,
3880 p_session_mode => 'customer_data',
3881 p_struct_num_like => p_param5,
3882 p_struct_name_like => p_param6);
3883 ELSIF (p_menu_choice = 8) THEN
3884 l_number := fnd_flex_upgrade_utilities.upgrade_vset_to_translatable
3885 (p_vset_name => p_param1,
3886 p_session_mode => 'customer_data');
3887 ELSIF (p_menu_choice IN (1,2,3)) THEN
3888 --
3889 -- List the segments.
3890 --
3891 x_prompt := NULL;
3892 RETURN;
3893 ELSE
3894 x_prompt := 'Invalid menu choice.';
3895 RETURN;
3896 END IF;
3897 IF (l_number = fnd_flex_upgrade_utilities.g_ret_no_error) THEN
3898 x_prompt := 'Successful operation, calling COMMIT.';
3899 COMMIT;
3900 ELSE
3901 x_prompt := 'Unsuccessful operation, calling ROLLBACK.';
3902 ROLLBACK TO afffupg1_savepoint;
3903 END IF;
3904 END;
3905
3906 END fnd_flex_upgrade_utilities;