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