1 PACKAGE BODY fnd_flex_val_api AS
2 /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
3
4
5 chr_newline CONSTANT VARCHAR2(8) := fnd_global.newline;
6
7 SUBTYPE vset_type IS fnd_flex_value_sets%ROWTYPE;
8 SUBTYPE value_type IS fnd_flex_values_vl%ROWTYPE;
9
10 TYPE bind_record_type IS RECORD
11 (pos number,
12 type VARCHAR2(1));
13
14 TYPE bind_array_type IS TABLE OF bind_record_type INDEX BY BINARY_INTEGER;
15
16 TYPE varchar2_array_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
17
18 --
19 -- Error Constants
20 --
21 ERROR_UNABLE_TO_FIND_VSET_NAME constant number := -20001;
22 ERROR_UNABLE_TO_FIND_VSET_ID constant number := -20002;
23 ERROR_FLEX_CODE_ERROR constant number := -20003;
24 ERROR_EXCEPTION_OTHERS constant number := -20004;
25 ERROR_VSET_IS_NOT_INDEPENDENT constant number := -20005;
26 ERROR_VSET_IS_NOT_DEPENDENT constant number := -20006;
27 ERROR_UNABLE_TO_FIND_HIER_CODE constant number := -20007;
28 ERROR_UNSUP_VALIDATION_TYPE constant number := -20008;
29 ERROR_VALUE_ALREADY_EXISTS constant number := -20009;
30 ERROR_UNABLE_TO_FIND_VALUE constant number := -20010;
31 ERROR_INVALID_ENABLED_FLAG constant number := -20011;
32 ERROR_INVALID_END_DATE constant number := -20012;
33 ERROR_INVALID_SUMMARY_FLAG constant number := -20013;
34 ERROR_INVALID_STR_HIER_LEVEL constant number := -20014;
35 ERROR_UNABLE_TO_FIND_STH_LEVEL constant number := -20015;
36 ERROR_UNABLE_TO_SET_WHO constant number := -20016;
37 ERROR_UNABLE_TO_LOAD_ROW constant number := -20017;
38 ERROR_VALUE_VALIDATION_FAILED constant number := -20018;
39 ERROR_UNABLE_TO_GET_PARENT_VST constant number := -20019;
40 ERROR_UNABLE_TO_GET_PARENT_VAL constant number := -20020;
41 ERROR_NOT_A_PARENT_VALUE constant number := -20021;
42 ERROR_INVALID_RANGE_ATTRIBUTE constant number := -20022;
43 ERROR_INVALID_HIGH_VALUE constant number := -20023;
44 ERROR_HIERARCHY_ALREADY_EXISTS constant number := -20024;
45 ERROR_UNABLE_TO_INSERT_ROW constant number := -20025;
46 ERROR_UNABLE_TO_SUBMIT_FDFCHY constant number := -20026;
47 ERROR_INVALID_TABLE_VSET constant number := -20027;
48
49 /* ------------------------------------------------------------ */
50 /* globals */
51 /* ------------------------------------------------------------ */
52
53 /* START_PUBLIC */
54 bad_parameter EXCEPTION;
55 PRAGMA EXCEPTION_INIT(bad_parameter, -06501);
56 /* END_PUBLIC */
57
58 value_too_large EXCEPTION;
59 PRAGMA EXCEPTION_INIT(value_too_large, -01401);
60
61 who_mode VARCHAR2(1000) := NULL; /* whether customer_data or seed_data */
62 internal_messages VARCHAR2(10000);
63 debug_mode_on BOOLEAN := FALSE;
64
65 /* ------------------------------------------------------------ */
66
67 /* START_PUBLIC */
68 PROCEDURE debug_on IS
69 BEGIN
70 debug_mode_on := TRUE;
71 END;
72
73 PROCEDURE debug_off IS
74 BEGIN
75 debug_mode_on := FALSE;
76 END;
77 /* END_PUBLIC */
78
79 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
80 IS
81 i INTEGER;
82 m INTEGER;
83 c INTEGER := 75;
84 BEGIN
85 execute immediate ('begin dbms' ||
86 '_output' ||
87 '.enable(1000000); end;');
88 m := Ceil(Length(p_debug)/c);
89 FOR i IN 1..m LOOP
90 execute immediate ('begin dbms' ||
91 '_output' ||
92 '.put_line(''' ||
93 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
94 '''); end;');
95 END LOOP;
96 EXCEPTION
97 WHEN OTHERS THEN
98 NULL;
99 END dbms_debug;
100
101 PROCEDURE println(msg IN VARCHAR2) IS
102 BEGIN
103 IF(debug_mode_on) THEN
104 dbms_debug(msg);
105 END IF;
106 END;
107
108 /* ------------------------------------------------------------ */
109 /* messaging */
110 /* ------------------------------------------------------------ */
111
112 PROCEDURE message(msg VARCHAR2) IS
113 BEGIN
114 internal_messages := internal_messages || msg ||
115 fnd_flex_val_api.chr_newline; /* hack to add LF */
116 END;
117
118 PROCEDURE message_init IS
119 BEGIN
120 internal_messages := '';
121 END;
122
123
124 /* ------------------------------------------------------------ */
125 /* who information */
126 /* ------------------------------------------------------------ */
127
128 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
129 BEGIN
130 IF(session_mode NOT IN ('customer_data', 'seed_data')) THEN
131 message('bad mode:'|| session_mode);
132 message('valid values are: customer_data, seed_data');
133 RAISE bad_parameter;
134 END IF;
135 who_mode := session_mode;
136 END;
137
138 FUNCTION customer_mode RETURN BOOLEAN IS
139 BEGIN
140 IF(who_mode = 'customer_data') THEN
141 RETURN TRUE;
142 ELSIF(who_mode = 'seed_data') THEN
143 RETURN FALSE;
144 ELSE
145 message('bad session mode:' || who_mode);
146 message('use set_session_mode to specify');
147 RAISE bad_parameter;
148 END IF;
149 END;
150
151
152 FUNCTION created_by_f RETURN NUMBER IS
153 BEGIN
154 IF(customer_mode) THEN
155 RETURN 0;
156 ELSE
157 RETURN 1;
158 END IF;
159 END;
160
161 FUNCTION creation_date_f RETURN DATE IS
162 BEGIN
163 IF(customer_mode) THEN
164 RETURN Sysdate;
165 ELSE
166 RETURN To_date('01011980', 'MMDDYYYY');
167 END IF;
168 END;
169
170
171 FUNCTION last_update_date_f RETURN DATE IS
172 BEGIN
173 RETURN creation_date_f;
174 END;
175
176 FUNCTION last_updated_by_f RETURN NUMBER IS
177 BEGIN
178 RETURN created_by_f;
179 END;
180
181 FUNCTION last_update_login_f RETURN NUMBER IS
182 BEGIN
183 RETURN 0;
184 END;
185
186
187 /* ------------------------------------------------------------ */
188 /* defaults */
189 /* ------------------------------------------------------------ */
190
191 PROCEDURE pre_insert(flex_value_set_id IN NUMBER,
192 summary_allowed_flag IN VARCHAR2,
193 id_column_name IN VARCHAR2,
194 application_table_name IN VARCHAR2,
195 table_application_id IN NUMBER,
196
197 enabled_column_name OUT nocopy VARCHAR2,
198 hierarchy_level_column_name OUT nocopy VARCHAR2,
199 start_date_column_name OUT nocopy VARCHAR2,
200 end_date_column_name OUT nocopy VARCHAR2,
201 summary_column_name OUT nocopy VARCHAR2,
202 compiled_attribute_column_name OUT nocopy VARCHAR2)
203 IS
204 BEGIN
205 IF((summary_allowed_flag = 'Y') AND (id_column_name IS NOT NULL)) THEN
206 message('allow summary values must be N to specify id column names');
207 RAISE bad_parameter;
208 END IF;
209
210 BEGIN
211 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
212 column_name INTO hierarchy_level_column_name
213 FROM fnd_columns c, fnd_tables t
214 WHERE c.column_name = 'STRUCTURED_HIERARCHY_LEVEL'
215 AND t.table_name = application_table_name
216 AND t.application_id= table_application_id
217 AND t.table_id = c.table_id
218 AND t.application_id = c.application_id
219 GROUP BY column_name;
220 EXCEPTION
221 WHEN no_data_found THEN
222 hierarchy_level_column_name := 'NULL';
223 END;
224
225 BEGIN
226 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
227 column_name INTO enabled_column_name
228 FROM fnd_columns c, fnd_tables t
229 WHERE c.column_name = 'ENABLED_FLAG'
230 AND c.column_type IN ('C', 'V')
231 AND t.table_name = application_table_name
232 AND t.application_id= table_application_id
233 AND t.table_id = c.table_id
234 AND t.application_id = c.application_id
235 GROUP BY column_name;
236 EXCEPTION
237 WHEN no_data_found THEN
238 enabled_column_name := '''Y''';
239 END;
240
241 BEGIN
242 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
243 column_name INTO start_date_column_name
244 FROM fnd_columns c, fnd_tables t
245 WHERE c.column_name = 'START_DATE_ACTIVE'
246 AND t.table_name = application_table_name
247 AND t.application_id= table_application_id
248 AND t.table_id = c.table_id
249 AND t.application_id = c.application_id
250 GROUP BY column_name;
251 EXCEPTION
252 WHEN no_data_found THEN
253 start_date_column_name := 'TO_DATE(NULL)';
254 END;
255
256 BEGIN
257 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
258 column_name INTO end_date_column_name
259 FROM fnd_columns c, fnd_tables t
260 WHERE c.column_name = 'END_DATE_ACTIVE'
261 AND t.table_name = application_table_name
262 AND t.application_id= table_application_id
263 AND t.table_id = c.table_id
264 AND t.application_id = c.application_id
265 GROUP BY column_name;
266 EXCEPTION
267 WHEN no_data_found THEN
268 end_date_column_name := 'TO_DATE(NULL)';
269 END;
270
271 BEGIN
272 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
273 column_name INTO summary_column_name
274 FROM fnd_columns c, fnd_tables t
275 WHERE c.column_name = 'SUMMARY_FLAG'
276 AND t.table_name = application_table_name
277 AND t.application_id= table_application_id
278 AND t.table_id = c.table_id
279 AND t.application_id = c.application_id
280 GROUP BY column_name;
281 EXCEPTION
282 WHEN no_data_found THEN
283 summary_column_name := '''N''';
284 END;
285
286 BEGIN
287 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
288 column_name INTO compiled_attribute_column_name
289 FROM fnd_columns c, fnd_tables t
290 WHERE c.column_name = 'COMPILED_VALUE_ATTRIBUTES'
291 AND t.table_name = application_table_name
292 AND t.application_id= table_application_id
293 AND t.table_id = c.table_id
294 AND t.application_id = c.application_id
295 GROUP BY column_name;
296 EXCEPTION
297 WHEN no_data_found THEN
298 compiled_attribute_column_name := 'NULL';
299 END;
300
301 END;
302
303
304
305 /* ------------------------------------------------------------ */
306 /* lookups */
307 /* ------------------------------------------------------------ */
308
309 FUNCTION application_id_f(application_name_in IN VARCHAR2,
310 application_short_name_in IN VARCHAR2)
311 RETURN fnd_application.application_id%TYPE
312 IS
313 application_id_ret fnd_application.application_id%TYPE;
314 BEGIN
315 BEGIN
316 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
317 application_id
318 INTO application_id_ret
319 FROM fnd_application
320 WHERE application_short_name = application_short_name_in;
321 EXCEPTION
322 WHEN no_data_found THEN
323 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
324 application_id
325 INTO application_id_ret
326 FROM fnd_application_tl
327 WHERE application_name = application_name_in
328 AND rownum =1;
329 END;
330 RETURN application_id_ret;
331 EXCEPTION
332 WHEN OTHERS THEN
333 IF(application_name_in IS NULL
334 AND application_short_name_in IS NULL) THEN
335 message('must specify appl_short_name');
336 ELSE
337 message('error locating application id');
338 message('appl_short_name:' || application_short_name_in);
339 message('application_name:' || application_name_in);
340 message(Sqlerrm);
341 END IF;
342 RAISE bad_parameter;
343 END;
344
345
346 FUNCTION value_set_id_f(value_set_name_in IN VARCHAR2)
347 RETURN fnd_flex_value_sets.flex_value_set_id%TYPE
348 IS
349 value_set_id_i fnd_flex_value_sets.flex_value_set_id%TYPE;
350 BEGIN
351 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
352 flex_value_set_id
353 INTO value_set_id_i
354 FROM fnd_flex_value_sets
355 WHERE flex_value_set_name = value_set_name_in;
356 RETURN value_set_id_i;
357 EXCEPTION
358 WHEN no_data_found THEN
359 message('bad valueset name:' || value_set_name_in);
360 RAISE bad_parameter;
361 END;
362
363
364 FUNCTION event_code_f(event_in IN VARCHAR2)
365 RETURN fnd_flex_validation_events.event_code%TYPE
366 IS
367 event_code_i fnd_flex_validation_events.event_code%TYPE;
368 BEGIN
369 BEGIN
370 fnd_flex_types.validate_event_type(event_in);
371 event_code_i := event_in;
372 EXCEPTION
373 -- maybe it's still old style
374 WHEN no_data_found THEN
375 println('WARNING: old style parameter: event_code');
376 event_code_i :=
377 fnd_flex_types.get_code(typ => 'FLEX_VALIDATION_EVENTS',
378 descr => event_in);
379 END;
380 RETURN event_code_i;
381 EXCEPTION
382 WHEN no_data_found THEN
383 message('bad event name:' || event_in);
384 RAISE bad_parameter;
385 END;
386
387
388 FUNCTION field_type_f(type_value_in IN VARCHAR2)
389 RETURN fnd_lookups.lookup_code%TYPE
390 IS
391 type_i fnd_lookups.lookup_code%TYPE := NULL;
392 BEGIN
393 IF(type_value_in IS NOT NULL) THEN
394 BEGIN
395 fnd_flex_types.validate_field_type(type_value_in);
396 type_i := type_value_in;
397 EXCEPTION
398 -- maybe it's still old style
399 WHEN no_data_found THEN
400 println('WARNING: old style parameter: field_type');
401 type_i :=
402 fnd_flex_types.get_code(typ => 'FIELD_TYPE',
403 descr => type_value_in);
404 END;
405 END IF;
406 RETURN type_i;
407 EXCEPTION
408 WHEN no_data_found THEN
409 message('bad field type:' || type_value_in);
410 RAISE bad_parameter;
411 END;
412
413
414 FUNCTION column_type_f(type_value_in IN VARCHAR2)
415 RETURN fnd_lookups.lookup_code%TYPE
416 IS
417 type_i fnd_lookups.lookup_code%TYPE := NULL;
418 BEGIN
419 IF(type_value_in IS NOT NULL) THEN
420 BEGIN
421 fnd_flex_types.validate_column_type(type_value_in);
422 type_i := type_value_in;
423 EXCEPTION
424 -- maybe it's still old style
425 WHEN no_data_found THEN
426 println('WARNING: old style parameter: column_type');
427 type_i :=
428 fnd_flex_types.get_code(typ => 'COLUMN_TYPE',
429 descr => type_value_in);
430 END;
431 END IF;
432 RETURN type_i;
433 EXCEPTION
434 WHEN no_data_found THEN
435 message('bad column type:' || type_value_in);
436 RAISE bad_parameter;
437 END;
438
439
440
441
442 FUNCTION invert_flag_f(flag IN VARCHAR2)
443 RETURN VARCHAR2
444 IS
445 BEGIN
446 IF(flag = 'Y') THEN
447 RETURN 'N';
448 ELSIF(flag = 'N') THEN
449 RETURN 'Y';
450 ELSE
451 message('bad Y/N value:' || flag);
452 RAISE bad_parameter;
453 END IF;
454 END;
455
456 /* ------------------------------------------------------------ */
460 PROCEDURE check_yesno(val IN VARCHAR2) IS
457 /* validation */
458 /* ------------------------------------------------------------ */
459
461 BEGIN
462 IF(val NOT IN ('Y', 'N')) then
463 message('Y/N value contained invalid value:' || val);
464 RAISE bad_parameter;
465 END IF;
466 END;
467
468
469 -- check the type for the id column and for the meaning column.
470 -- these can be null
471 PROCEDURE check_type(table_application_id_in IN NUMBER,
472 application_table_name_in IN VARCHAR2,
473 column_name_in IN VARCHAR2)
474 IS
475 dummy NUMBER;
476 BEGIN
477 IF(column_name_in IS NOT NULL) THEN
478 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
479 NULL INTO dummy
480 FROM fnd_tables t, fnd_lookup_values l, fnd_columns c
481 WHERE t.application_id = table_application_id_in
482 AND t.table_name = application_table_name_in
483 AND c.application_id = t.application_id
484 AND c.column_name = column_name_in
485 AND c.table_id = t.table_id
486 AND l.lookup_type = 'COLUMN_TYPE'
487 AND c.column_type = l.lookup_code
488 AND rownum = 1;
489 -- AND c.column_type IN ('C', 'D', 'N', 'V');
490 END IF;
491 EXCEPTION
492 WHEN no_data_found THEN
493 message('possible type mismatch with column:'||
494 column_name_in);
495 RAISE bad_parameter;
496 END;
497
498 -- check the type for the value column (which is required)
499 PROCEDURE check_type(table_application_id_in IN NUMBER,
500 application_table_name_in IN VARCHAR2,
501 column_name_in IN VARCHAR2,
502 format_type_in IN VARCHAR2)
503 IS
504 column_type_i fnd_columns.column_type%TYPE;
505 dummy NUMBER;
506 BEGIN
507 BEGIN
508 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
509 column_type
510 INTO column_type_i
511 FROM fnd_tables t, fnd_lookup_values l, fnd_columns c
512 WHERE t.application_id = table_application_id_in
513 AND t.table_name = application_table_name_in
514 AND c.application_id = t.application_id
515 AND c.table_id = t.table_id
516 AND c.column_name = column_name_in
517 AND l.lookup_type = 'COLUMN_TYPE'
518 AND l.lookup_code = c.column_type
519 AND rownum = 1;
520 EXCEPTION
521 WHEN OTHERS THEN
522 message('error looking up column:'|| column_name_in);
523 message('application table name:'||application_table_name_in);
524 message(Sqlerrm);
525 RAISE bad_parameter;
526 END;
527 IF(NOT ((format_type_in IN ('D','T','t') AND column_type_i = 'D')
528 OR (format_type_in = 'N' AND column_type_i = 'N')
529 OR column_type_i IN ('C', 'V'))) THEN
530 message('possible type mismatch with value column:'||
531 column_name_in);
532 message('format type:' || format_type_in);
533 message('column_type:' || column_type_i);
534 RAISE bad_parameter;
535 END IF;
536 END;
537
538 /* ------------------------------------------------------------ */
539 /* insertion functions */
540 /* ------------------------------------------------------------ */
541
542 FUNCTION insert_flex_value_sets(
543 /* basic parameters */
544 value_set_name IN varchar2,
545 description IN varchar2,
546 security_enabled_flag IN varchar2,
547 longlist_flag IN varchar2,
548 format_type IN varchar2,
549 maximum_size IN number,
550 number_precision IN number,
551 alphanumeric_allowed_flag IN varchar2,
552 uppercase_only_flag IN varchar2,
553 numeric_mode_enabled_flag IN varchar2,
554 minimum_value IN varchar2,
555 maximum_value IN varchar2,
556 validation_type IN varchar2,
557
558 /* when creating a dependent value set: */
559 dependent_default_value IN varchar2 DEFAULT null,
560 dependent_default_meaning IN varchar2 DEFAULT null,
561 parent_flex_value_set_id IN number DEFAULT NULL)
562 RETURN number
563 IS
564 last_update_login fnd_flex_value_sets.last_update_login%TYPE;
565 last_update_date fnd_flex_value_sets.last_update_date%TYPE;
566 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE;
567 creation_date fnd_flex_value_sets.creation_date%TYPE;
568 created_by fnd_flex_value_sets.created_by%TYPE;
569 rv NUMBER;
570 BEGIN
571 last_update_login := last_update_login_f();
572 last_update_date := last_update_date_f();
573 last_updated_by := last_updated_by_f();
574 creation_date := creation_date_f();
575 created_by := created_by_f();
576
577 check_yesno(security_enabled_flag);
578 check_yesno(alphanumeric_allowed_flag);
579 check_yesno(numeric_mode_enabled_flag);
580 insert /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
581 INTO
582 fnd_flex_value_sets(flex_value_set_id,
583 flex_value_set_name,
584 validation_type,
585 security_enabled_flag,
586 longlist_flag,
587 format_type,
588 maximum_size,
589 alphanumeric_allowed_flag,
590 uppercase_only_flag,
591 numeric_mode_enabled_flag,
592 description,
593 minimum_value,
594 maximum_value,
598 last_update_date,
595 number_precision,
596 protected_flag,
597 last_update_login,
599 last_updated_by,
600 creation_date,
601 created_by,
602
603 dependant_default_value,/* note spelling */
604 dependant_default_meaning,/* note spelling */
605 parent_flex_value_set_id)
606 VALUES(fnd_flex_value_sets_s.nextval,
607 value_set_name,
608 validation_type,
609 security_enabled_flag,
610 longlist_flag,
611 format_type,
612 maximum_size,
613 alphanumeric_allowed_flag,
614 uppercase_only_flag,
615 numeric_mode_enabled_flag,
616 description,
617 minimum_value,
618 maximum_value,
619 number_precision,
620 'N',
621 last_update_login,
622 last_update_date,
623 last_updated_by,
624 creation_date,
625 created_by,
626 dependent_default_value,
627 dependent_default_meaning,
628 parent_flex_value_set_id);
629
630
631 println('created value set (type ' || validation_type || ') '
632 || value_set_name);
633
634 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
635 fnd_flex_value_sets_s.CURRVAL INTO rv
636 FROM dual;
637 RETURN rv;
638 EXCEPTION
639 when dup_val_on_index THEN
640 message('insert failed - duplicate value set name or id');
641 RAISE bad_parameter;
642 when VALUE_TOO_LARGE then
643 message('insert value_sets failed - value too large');
644 RAISE bad_parameter;
645 END; /* function */
646
647
648 /* ------------------------------------------------------------ */
649
650 PROCEDURE insert_flex_validation_events(
651 flex_value_set_id IN NUMBER,
652 event_code IN varchar2,
653 user_exit IN long)
654 IS
655 last_update_login fnd_flex_value_sets.last_update_login%TYPE;
656 last_update_date fnd_flex_value_sets.last_update_date%TYPE;
657 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE;
658 creation_date fnd_flex_value_sets.creation_date%TYPE;
659 created_by fnd_flex_value_sets.created_by%TYPE;
660 BEGIN
661 last_update_login := last_update_login_f();
662 last_update_date := last_update_date_f();
663 last_updated_by := last_updated_by_f();
664 creation_date := creation_date_f();
665 created_by := created_by_f();
666
667 insert /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
668 INTO
669 fnd_flex_validation_events(flex_value_set_id,
670 event_code,
671 user_exit,
672 last_update_login,
673 last_update_date,
674 last_updated_by,
675 creation_date,
676 created_by)
677 values(flex_value_set_id,
678 event_code,
679 user_exit,
680 last_update_login,
681 last_update_date,
682 last_updated_by,
683 creation_date,
684 created_by);
685
686 println('created validation_events entry ' || event_code);
687 EXCEPTION
688 when dup_val_on_index THEN
689 message('insert failed - duplicate value on index');
690 RAISE bad_parameter;
691 when VALUE_TOO_LARGE then
692 message('insert validation_events failed - value too large');
693 RAISE bad_parameter;
694 END; /* proc */
695
696
697 /* ------------------------------------------------------------ */
698
699 PROCEDURE insert_flex_validation_tables(
700 flex_value_set_id IN number,
701 application_table_name IN varchar2,
702 value_column_name IN varchar2,
703 value_column_type IN varchar2,
704 value_column_size IN number,
705 id_column_name IN varchar2,
706 id_column_type IN varchar2,
707 id_column_size IN number,
708 meaning_column_name IN varchar2,
709 meaning_column_type IN varchar2,
710 meaning_column_size IN number,
711 summary_allowed_flag IN varchar2,
712 table_application_id IN NUMBER,
713 additional_where_clause IN long,
714 additional_quickpick_columns IN VARCHAR2)
715 IS
716 last_update_login fnd_flex_value_sets.last_update_login%TYPE;
717 last_update_date fnd_flex_value_sets.last_update_date%TYPE;
718 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE;
719 creation_date fnd_flex_value_sets.creation_date%TYPE;
720 created_by fnd_flex_value_sets.created_by%TYPE;
721
722 enabled_column_name
723 fnd_flex_validation_tables.enabled_column_name%TYPE;
724 hierarchy_level_column_name
725 fnd_flex_validation_tables.hierarchy_level_column_name%TYPE;
726 start_date_column_name
727 fnd_flex_validation_tables.start_date_column_name%TYPE;
728 end_date_column_name
729 fnd_flex_validation_tables.end_date_column_name%TYPE;
730 summary_column_name
731 fnd_flex_validation_tables.summary_column_name%TYPE;
732 compiled_attribute_column_name
733 fnd_flex_validation_tables.compiled_attribute_column_name%TYPE;
734 BEGIN
735 last_update_login := last_update_login_f();
736 last_update_date := last_update_date_f();
737 last_updated_by := last_updated_by_f();
738 creation_date := creation_date_f();
739 created_by := created_by_f();
740
744 summary_allowed_flag => summary_allowed_flag,
741 check_yesno(summary_allowed_flag);
742 pre_insert(
743 flex_value_set_id => flex_value_set_id,
745 id_column_name => id_column_name,
746 application_table_name => application_table_name,
747 table_application_id => table_application_id,
748
749 enabled_column_name => enabled_column_name,
750 hierarchy_level_column_name => hierarchy_level_column_name,
751 start_date_column_name => start_date_column_name,
752 end_date_column_name => end_date_column_name,
753 summary_column_name => summary_column_name,
754 compiled_attribute_column_name => compiled_attribute_column_name);
755 INSERT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
756 INTO
757 fnd_flex_validation_tables(flex_value_set_id,
758 application_table_name,
759 value_column_name,
760 value_column_type,
761 value_column_size,
762 id_column_name,
763 id_column_type,
764 id_column_size,
765 meaning_column_name,
766 meaning_column_type,
767 meaning_column_size,
768 summary_allowed_flag,
769 table_application_id,
770 additional_where_clause,
771 additional_quickpick_columns,
772
773 compiled_attribute_column_name,
774 enabled_column_name,
775 hierarchy_level_column_name,
776 start_date_column_name,
777 end_date_column_name,
778 summary_column_name,
779
780 last_update_login,
781 last_update_date,
782 last_updated_by,
783 creation_date,
784 created_by)
785 VALUES(flex_value_set_id,
786 application_table_name,
787 value_column_name,
788 value_column_type,
789 value_column_size,
790 id_column_name,
791 id_column_type,
792 id_column_size,
793 meaning_column_name,
794 meaning_column_type,
795 meaning_column_size,
796 summary_allowed_flag,
797 table_application_id,
798 additional_where_clause,
799 additional_quickpick_columns,
800
801 compiled_attribute_column_name,
802 enabled_column_name,
803 hierarchy_level_column_name,
804 start_date_column_name,
805 end_date_column_name,
806 summary_column_name,
807
808 last_update_login,
809 last_update_date,
810 last_updated_by,
811 creation_date,
812 created_by);
813 println('created validation_tables entry ');
814 EXCEPTION
815 when dup_val_on_index THEN
816 message('insert failed - duplicate value on index');
817 RAISE bad_parameter;
818 when VALUE_TOO_LARGE then
819 message('insert validation_tables failed - value too large');
820 RAISE bad_parameter;
821 END; /* function */
822
823
824
825
826
827 /* START_PUBLIC */
828
829
830 /* ------------------------------------------------------------ */
831 /* public function definitions */
832 /* ------------------------------------------------------------ */
833
834 FUNCTION version RETURN VARCHAR2 IS
835 BEGIN
836 RETURN('$Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $');
837 END;
838
839 FUNCTION message RETURN VARCHAR2 IS
840 BEGIN
841 RETURN internal_messages;
842 END;
843
844 PROCEDURE raise_error(p_error_code IN NUMBER,
845 p_error_text IN VARCHAR2)
846 IS
847 l_error_text varchar2(32000);
848 BEGIN
849 l_error_text := p_error_text || chr_newline ||
850 dbms_utility.format_error_stack();
851
852 raise_application_error(p_error_code, l_error_text);
853
854 -- no exception handling here
855
856 END raise_error;
857
858 PROCEDURE create_valueset_none(
859 /* basic parameters */
860 value_set_name IN varchar2,
861 description IN varchar2,
862 security_available IN varchar2,
863 enable_longlist IN varchar2,
864 format_type IN varchar2,
865 maximum_size IN number,
866 precision IN number DEFAULT null,
867 numbers_only IN varchar2,
868 uppercase_only IN varchar2,
869 right_justify_zero_fill IN varchar2,
870 min_value IN varchar2,
871 max_value IN VARCHAR2)
872 IS
873 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
874 validation_type fnd_flex_value_sets.validation_type%TYPE;
875 format_code fnd_flex_value_sets.format_type%TYPE;
876 alphanumeric_allowed fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE;
877 BEGIN
878 validation_type := 'N';
879
880 message_init;
881 format_code := field_type_f(format_type);
882 alphanumeric_allowed := invert_flag_f(numbers_only);
883 value_set_id := insert_flex_value_sets(
884 value_set_name => value_set_name,
885 description => description,
886 security_enabled_flag => security_available,
887 longlist_flag => enable_longlist,
888 format_type => format_code,
889 maximum_size => maximum_size,
890 number_precision => precision,
891 alphanumeric_allowed_flag => alphanumeric_allowed,
895 maximum_value => max_value,
892 uppercase_only_flag => uppercase_only,
893 numeric_mode_enabled_flag => right_justify_zero_fill,
894 minimum_value => min_value,
896 validation_type => validation_type);
897 EXCEPTION
898 WHEN OTHERS THEN
899 message('error in create valueset none');
900 RAISE bad_parameter;
901 END; /* procedure */
902
903
904
905 PROCEDURE create_valueset_independent(
906 /* basic parameters */
907 value_set_name IN varchar2,
908 description IN varchar2,
909 security_available IN varchar2,
910 enable_longlist IN varchar2,
911 format_type IN varchar2,
912 maximum_size IN number,
913 precision IN number DEFAULT null,
914 numbers_only IN varchar2,
915 uppercase_only IN varchar2,
916 right_justify_zero_fill IN varchar2,
917 min_value IN varchar2,
918 max_value IN VARCHAR2)
919 IS
920 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
921 validation_type fnd_flex_value_sets.validation_type%TYPE;
922 format_code fnd_flex_value_sets.format_type%TYPE;
923 alphanumeric_allowed fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE;
924 BEGIN
925 validation_type := 'I';
926
927 message_init;
928 format_code := field_type_f(format_type);
929 alphanumeric_allowed := invert_flag_f(numbers_only);
930 value_set_id := insert_flex_value_sets(
931 value_set_name => value_set_name,
932 description => description,
933 security_enabled_flag => security_available,
934 longlist_flag => enable_longlist,
935 format_type => format_code,
936 maximum_size => maximum_size,
937 number_precision => precision,
938 alphanumeric_allowed_flag => alphanumeric_allowed,
939 uppercase_only_flag => uppercase_only,
940 numeric_mode_enabled_flag => right_justify_zero_fill,
941 minimum_value => min_value,
942 maximum_value => max_value,
943 validation_type => validation_type);
944 EXCEPTION
945 WHEN OTHERS THEN
946 message('error in create valueset independent');
947 RAISE bad_parameter;
948 END; /* procedure */
949
950
951
952 PROCEDURE create_valueset_dependent(
953 /* basic parameters */
954 value_set_name IN varchar2,
955 description IN varchar2,
956 security_available IN varchar2,
957 enable_longlist IN varchar2,
958 format_type IN varchar2,
959 maximum_size IN number,
960 precision IN number DEFAULT null,
961 numbers_only IN varchar2,
962 uppercase_only IN varchar2,
963 right_justify_zero_fill IN varchar2,
964 min_value IN varchar2,
965 max_value IN varchar2,
966
967 parent_flex_value_set IN VARCHAR2,
968 dependent_default_value IN varchar2,
969 dependent_default_meaning IN VARCHAR2)
970 IS
971 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
972 parent_id fnd_flex_value_sets.parent_flex_value_set_id%TYPE;
973 validation_type fnd_flex_value_sets.validation_type%TYPE;
974 format_code fnd_flex_value_sets.format_type%TYPE;
975 alphanumeric_allowed fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE;
976 BEGIN
977 validation_type := 'D';
978
979 message_init;
980 format_code := field_type_f(format_type);
981 alphanumeric_allowed := invert_flag_f(numbers_only);
982 BEGIN
983 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
984 flex_value_set_id INTO parent_id
985 FROM fnd_flex_value_sets
986 WHERE flex_value_set_name = parent_flex_value_set;
987 /* the where clause is on a unique key, so 0 or 1 hits expected. */
988 EXCEPTION
989 WHEN no_data_found THEN
990 message('could not find value set ' || parent_flex_value_set);
991 RAISE bad_parameter;
992 END;
993
994 IF (dependent_default_value IS NULL)
995 OR (dependent_default_meaning IS NULL) THEN
996 message('Dependent Value Set must have dependent_default_value and' ||
997 ' dependent_default_meaning');
998 RAISE bad_parameter;
999 END IF;
1000
1001 value_set_id := insert_flex_value_sets(
1002 value_set_name => value_set_name,
1003 description => description,
1004 security_enabled_flag => security_available,
1005 longlist_flag => enable_longlist,
1006 format_type => format_code,
1007 maximum_size => maximum_size,
1008 number_precision => precision,
1009 alphanumeric_allowed_flag => alphanumeric_allowed,
1010 uppercase_only_flag => uppercase_only,
1011 numeric_mode_enabled_flag => right_justify_zero_fill,
1012 minimum_value => min_value,
1013 maximum_value => max_value,
1014 validation_type => validation_type,
1015
1016 dependent_default_value => dependent_default_value,
1017 dependent_default_meaning => dependent_default_meaning,
1018 parent_flex_value_set_id => parent_id);
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 message('error in create valueset dependent');
1022 RAISE bad_parameter;
1023 END; /* procedure */
1024
1025
1026
1027 PROCEDURE create_valueset_table(
1028 /* basic parameters */
1032 enable_longlist IN varchar2,
1029 value_set_name IN varchar2,
1030 description IN varchar2,
1031 security_available IN varchar2,
1033 format_type IN varchar2,
1034 maximum_size IN number,
1035 precision IN number DEFAULT null,
1036 numbers_only IN varchar2,
1037 uppercase_only IN varchar2,
1038 right_justify_zero_fill IN varchar2,
1039 min_value IN varchar2,
1040 max_value IN varchar2,
1041
1042 table_application IN VARCHAR2 DEFAULT NULL,
1043 table_appl_short_name IN VARCHAR2 DEFAULT NULL,
1044 table_name IN varchar2,
1045 allow_parent_values IN varchar2,
1046 value_column_name IN VARCHAR2,
1047 value_column_type IN varchar2,
1048 value_column_size IN NUMBER,
1049 meaning_column_name IN varchar2 DEFAULT NULL,
1050 meaning_column_type IN varchar2 DEFAULT NULL,
1051 meaning_column_size IN NUMBER DEFAULT NULL,
1052 id_column_name IN varchar2 DEFAULT NULL,
1053 id_column_type IN varchar2 DEFAULT NULL,
1054 id_column_size IN number DEFAULT NULL,
1055 where_order_by IN varchar2 DEFAULT NULL,
1056 additional_columns IN VARCHAR2 DEFAULT NULL)
1057 IS
1058 validation_type fnd_flex_value_sets.validation_type%TYPE;
1059 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
1060 table_application_id_i fnd_application.application_id%TYPE;
1061 format_code fnd_flex_value_sets.format_type%TYPE;
1062 value_column_type_code fnd_flex_value_sets.format_type%TYPE;
1063 meaning_column_type_code fnd_flex_value_sets.format_type%TYPE;
1064 id_column_type_code fnd_flex_value_sets.format_type%TYPE;
1065 alphanumeric_allowed fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE;
1066 l_result VARCHAR2(10);
1067 l_message VARCHAR2(32000);
1068 BEGIN
1069 validation_type := 'F';
1070
1071 message_init;
1072 table_application_id_i := application_id_f(table_application,
1073 table_appl_short_name);
1074 alphanumeric_allowed := invert_flag_f(numbers_only);
1075
1076 -- get the format code from the type
1077 format_code := field_type_f(format_type);
1078 value_column_type_code := column_type_f(value_column_type);
1079 meaning_column_type_code := column_type_f(meaning_column_type);
1080 id_column_type_code := column_type_f(id_column_type);
1081
1082 /*
1083 -- check id column's type
1084 check_type(table_application_id_i,
1085 table_name,
1086 id_column_name);
1087 -- check meaning column's type
1088 check_type(table_application_id_i,
1089 table_name,
1090 meaning_column_name);
1091 -- check the value column
1092 check_type(table_application_id_i,
1093 table_name,
1094 value_column_name,
1095 format_code);
1096 */
1097
1098 validate_table_vset(p_flex_value_set_name => value_set_name,
1099 p_id_column_name => id_column_name,
1100 p_value_column_name => value_column_name,
1101 p_meaning_column_name => meaning_column_name,
1102 p_additional_quickpick_columns => additional_columns,
1103 p_application_table_name => table_name,
1104 p_additional_where_clause => where_order_by,
1105 x_result => l_result,
1106 x_message => l_message);
1107
1108 IF (l_result = 'Failure') THEN
1109 raise_error(ERROR_INVALID_TABLE_VSET,l_message);
1110 END IF;
1111
1112 value_set_id := insert_flex_value_sets(
1113 value_set_name => value_set_name,
1114 description => description,
1115 security_enabled_flag => security_available,
1116 longlist_flag => enable_longlist,
1117 format_type => format_code,
1118 maximum_size => maximum_size,
1119 number_precision => precision,
1120 alphanumeric_allowed_flag => alphanumeric_allowed,
1121 uppercase_only_flag => uppercase_only,
1122 numeric_mode_enabled_flag => right_justify_zero_fill,
1123 minimum_value => min_value,
1124 maximum_value => max_value,
1125 validation_type => validation_type);
1126 insert_flex_validation_tables(
1127 flex_value_set_id => value_set_id,
1128 application_table_name => table_name,
1129 value_column_name => value_column_name,
1130 value_column_type => value_column_type_code,
1131 value_column_size => value_column_size,
1132 id_column_name => id_column_name,
1133 id_column_type => id_column_type_code,
1134 id_column_size => id_column_size,
1135 meaning_column_name => meaning_column_name,
1136 meaning_column_type => meaning_column_type_code,
1137 meaning_column_size => meaning_column_size,
1138 summary_allowed_flag => allow_parent_values,
1139 table_application_id => table_application_id_i,
1140 additional_where_clause => where_order_by,
1141 additional_quickpick_columns => additional_columns);
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144 message('error in create valueset table');
1145 RAISE bad_parameter;
1146 END; /* procedure */
1147
1148
1149
1150
1151 PROCEDURE create_valueset_special(
1152 /* basic parameters */
1153 value_set_name IN varchar2,
1154 description IN varchar2,
1155 security_available IN varchar2,
1159 precision IN number DEFAULT null,
1156 enable_longlist IN varchar2,
1157 format_type IN varchar2,
1158 maximum_size IN number,
1160 numbers_only IN varchar2,
1161 uppercase_only IN varchar2,
1162 right_justify_zero_fill IN varchar2,
1163 min_value IN varchar2,
1164 max_value IN VARCHAR2)
1165 IS
1166 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
1167 validation_type fnd_flex_value_sets.validation_type%TYPE;
1168 format_code fnd_flex_value_sets.format_type%TYPE;
1169 alphanumeric_allowed fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE;
1170 BEGIN
1171 validation_type := 'U';
1172
1173 message_init;
1174 -- get the format code from the type
1175 format_code := field_type_f(format_type);
1176 alphanumeric_allowed := invert_flag_f(numbers_only);
1177 value_set_id := insert_flex_value_sets(
1178 value_set_name => value_set_name,
1179 description => description,
1180 security_enabled_flag => security_available,
1181 longlist_flag => enable_longlist,
1182 format_type => format_code,
1183 maximum_size => maximum_size,
1184 number_precision => precision,
1185 alphanumeric_allowed_flag => alphanumeric_allowed,
1186 uppercase_only_flag => uppercase_only,
1187 numeric_mode_enabled_flag => right_justify_zero_fill,
1188 minimum_value => min_value,
1189 maximum_value => max_value,
1190 validation_type => validation_type);
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 message('error in create valueset special');
1194 RAISE bad_parameter;
1195 END; /* procedure */
1196
1197
1198
1199
1200 PROCEDURE create_valueset_pair(
1201 /* basic parameters */
1202 value_set_name IN varchar2,
1203 description IN varchar2,
1204 security_available IN varchar2,
1205 enable_longlist IN varchar2,
1206 format_type IN varchar2,
1207 maximum_size IN number,
1208 precision IN number DEFAULT null,
1209 numbers_only IN varchar2,
1210 uppercase_only IN varchar2,
1211 right_justify_zero_fill IN varchar2,
1212 min_value IN varchar2,
1213 max_value IN VARCHAR2)
1214 IS
1215 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
1216 validation_type fnd_flex_value_sets.validation_type%TYPE;
1217 format_code fnd_flex_value_sets.format_type%TYPE;
1218 alphanumeric_allowed fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE;
1219 BEGIN
1220 validation_type := 'P';
1221
1222 message_init;
1223 -- get the format code from the type
1224 format_code := field_type_f(format_type);
1225 alphanumeric_allowed := invert_flag_f(numbers_only);
1226 value_set_id := insert_flex_value_sets(
1227 value_set_name => value_set_name,
1228 description => description,
1229 security_enabled_flag => security_available,
1230 longlist_flag => enable_longlist,
1231 format_type => format_code,
1232 maximum_size => maximum_size,
1233 number_precision => precision,
1234 alphanumeric_allowed_flag => alphanumeric_allowed,
1235 uppercase_only_flag => uppercase_only,
1236 numeric_mode_enabled_flag => right_justify_zero_fill,
1237 minimum_value => min_value,
1238 maximum_value => max_value,
1239 validation_type => validation_type);
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 message('error in create valueset pair');
1243 RAISE bad_parameter;
1244 END; /* procedure */
1245
1246
1247
1248 PROCEDURE add_event(value_set_name IN VARCHAR2,
1249 event IN VARCHAR2,
1250 function_text IN long)
1251 IS
1252 event_code fnd_flex_validation_events.event_code%TYPE;
1253 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
1254 BEGIN
1255 message_init;
1256 value_set_id := value_set_id_f(value_set_name);
1257 -- get the event code from the event name
1258 event_code := event_code_f(event);
1259 insert_flex_validation_events(flex_value_set_id => value_set_id,
1260 event_code => event_code,
1261 user_exit => function_text);
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 message('error in add event');
1265 RAISE bad_parameter;
1266 END; /* procedure */
1267
1268
1269 PROCEDURE private_delete_valueset(p_value_set IN VARCHAR2,
1270 p_force_delete IN BOOLEAN DEFAULT FALSE)
1271 IS
1272 l_value_set_id NUMBER(10);
1273 l_row_count NUMBER;
1274 l_dummy_vc2 VARCHAR2(90) := NULL;
1275 BEGIN
1276 --
1277 -- Value set existance check
1278 --
1279 BEGIN
1280 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1281 flex_value_set_id
1282 INTO l_value_set_id
1283 FROM fnd_flex_value_sets
1284 WHERE flex_value_set_name = p_value_set;
1285 EXCEPTION
1286 WHEN no_data_found THEN
1287 message('could not find value set: ' || p_value_set);
1288 message('delete aborted');
1289 RAISE bad_parameter;
1290 END;
1291
1292 --
1293 -- This parameter is used in destructive_rename.
1294 --
1295 IF (p_force_delete) THEN
1296 GOTO label_start_delete;
1297 END IF;
1298
1299 --
1303
1300 -- Check whether this value set is used in somewhere...
1301 -- Following code is taken from FNDFFMVS Form.
1302 --
1304 --
1305 -- Is this value set a parent value set?
1306 --
1307 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1308 count(*) INTO l_row_count
1309 FROM fnd_flex_value_sets
1310 WHERE validation_type = fnd_flex_types.val_dependent
1311 AND parent_flex_value_set_id = l_value_set_id;
1312
1313 IF (l_row_count <> 0) THEN
1314 message('This value set is used as a parent value set');
1315 message('You cannot delete independent value set of an ');
1316 message('independent-dependent value set pair. Delete aborted.');
1317 RAISE bad_parameter;
1318 END IF;
1319
1320 BEGIN
1321 --
1322 -- Is this value set used by a key flexfield segment.
1323 --
1324 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1325 count(*) INTO l_row_count
1326 FROM fnd_id_flex_segments
1327 WHERE flex_value_set_id = l_value_set_id;
1328
1329 -- IF (l_row_count <> 0) THEN
1330 -- message('This value set is used in at least one of the key flexfield ');
1331 -- message('segments. You cannot delete a used value set.');
1332 -- message('Delete aborted.');
1333 -- RAISE bad_parameter;
1334 -- END IF;
1335
1336 IF (l_row_count <> 0) THEN
1337 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1338 g.application_id || ':' || g.id_flex_code || ':' ||
1339 c.id_flex_structure_name || ':' || g.application_column_name
1340 INTO l_dummy_vc2
1341 FROM fnd_id_flex_segments g, fnd_id_flex_structures_tl c
1342 WHERE g.flex_value_set_id = l_value_set_id
1343 AND c.id_flex_code = g.id_flex_code
1344 AND c.id_flex_num = g.id_flex_num
1345 AND c.application_id = g.application_id
1346 AND ROWNUM = 1;
1347 END IF;
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 l_dummy_vc2 := NULL;
1351 END;
1352
1353 IF (l_dummy_vc2 IS NOT NULL) THEN
1354 message('This value set is used by KFF segment : ' || l_dummy_vc2);
1355 message(' and ' || (l_row_count - 1) || ' other KFF segments.');
1356 message('You cannot delete a used value set.');
1357 message('Delete aborted.');
1358 RAISE bad_parameter;
1359 END IF;
1360
1361 BEGIN
1362 --
1363 -- Is this value set used by a descriptive flexfield segment.
1364 --
1365 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1366 count(*) INTO l_row_count
1367 FROM fnd_descr_flex_column_usages
1368 WHERE flex_value_set_id = l_value_set_id;
1369
1370 -- IF (l_row_count <> 0) THEN
1371 -- message('This value set is used in at least one of the descriptive ');
1372 -- message('flexfield segments. You cannot delete a used value set.');
1373 -- message('Delete aborted.');
1374 -- RAISE bad_parameter;
1375 -- END IF;
1376
1377 IF (l_row_count <> 0) THEN
1378 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1379 application_id || ':' || descriptive_flexfield_name || ':' ||
1380 end_user_column_name
1381 INTO l_dummy_vc2
1382 FROM fnd_descr_flex_column_usages
1383 WHERE flex_value_set_id = l_value_set_id
1384 AND ROWNUM = 1;
1385 END IF;
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 l_dummy_vc2 := NULL;
1389 END;
1390
1391 IF (l_dummy_vc2 IS NOT NULL) THEN
1392 message('This value set is used by DFF segment : ' || l_dummy_vc2);
1393 message(' and ' || (l_row_count - 1) || ' other DFF segments.');
1394 message('You cannot delete a used value set.');
1395 message('Delete aborted.');
1396 RAISE bad_parameter;
1397 END IF;
1398
1399 --
1400 -- Is this value set used by flexbuilder?
1401 -- Flexbuilde is not included in Release 11.0
1402 -- this part is commented out.
1403 --
1404 -- SELECT count(*) INTO row_count FROM fnd_flexbuilder_parameters
1405 -- WHERE flex_value_set_id = l_value_set_id;
1406 --
1407 -- if (row_count <> 0) then
1408 -- message('This value set is used by one of the flexbuilder rules.');
1409 -- message('You cannot delete a used value set.');
1410 -- message('Delete aborted.');
1411 -- RAISE bad_parameter;
1412 -- end if;
1413 --
1414
1415 -- If we reached to this point, it is OK to delete the value set.
1416
1417 --
1418 -- Note : DELETE statement doesn't raise an exception.
1419 --
1420 --
1421 -- Start with deleting values for this value set.
1422 --
1423
1424 <<label_start_delete>>
1425 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1426 FROM fnd_flex_values_tl
1427 WHERE flex_value_id IN
1428 (SELECT flex_value_id FROM fnd_flex_values
1429 WHERE flex_value_set_id = l_value_set_id);
1430
1431 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1432 FROM fnd_flex_values
1433 WHERE flex_value_set_id = l_value_set_id;
1434
1435 --
1436 -- Delete Value rules.
1437 --
1438 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1439 FROM fnd_flex_value_rules_tl
1443
1440 WHERE flex_value_rule_id IN
1441 (SELECT flex_value_rule_id FROM fnd_flex_value_rules
1442 WHERE flex_value_set_id = l_value_set_id);
1444 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1445 FROM fnd_flex_value_rules
1446 WHERE flex_value_set_id = l_value_set_id;
1447
1448 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1449 FROM fnd_flex_value_rule_lines
1450 WHERE flex_value_set_id = l_value_set_id;
1451
1452 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1453 FROM fnd_flex_value_rule_usages
1454 WHERE flex_value_set_id = l_value_set_id;
1455
1456 --
1457 -- Delete Value hierarchies.
1458 --
1459 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1460 FROM fnd_flex_hierarchies_TL
1461 WHERE flex_value_set_id = l_value_set_id;
1462
1463 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1464 FROM fnd_flex_hierarchies
1465 WHERE flex_value_set_id = l_value_set_id;
1466
1467 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1468 FROM fnd_flex_value_hierarchies
1469 WHERE flex_value_set_id = l_value_set_id;
1470
1471 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1472 FROM fnd_flex_value_norm_hierarchy
1473 WHERE flex_value_set_id = l_value_set_id;
1474
1475 --
1476 -- Delete Table Validated Value set.
1477 --
1478 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1479 FROM fnd_flex_validation_tables
1480 WHERE flex_value_set_id = l_value_set_id;
1481
1482 --
1483 -- Delete Special Value set.
1484 --
1485 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1486 FROM fnd_flex_validation_events
1487 WHERE flex_value_set_id = l_value_set_id;
1488
1489 --
1490 -- Delete Value Set - Qualifier Assignments.
1491 --
1492 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1493 FROM fnd_flex_validation_qualifiers
1494 WHERE flex_value_set_id = l_value_set_id;
1495
1496 --
1497 -- Delete the value set from main table.
1498 --
1499 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1500 FROM fnd_flex_value_sets
1501 WHERE flex_value_set_id = l_value_set_id;
1502
1503 --
1504 -- Delete the FND_FLEX_VALUES Descriptive Flexfield context associated
1505 -- to this value set.
1506 --
1507 -- At this point it is better to call
1508 -- fnd_flex_dsc_api.delete_context('FND',value_set);
1509 -- But for compatibilty with Form, I am keeping following code.
1510 --
1511 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1512 FROM fnd_descr_flex_contexts
1513 WHERE application_id = 0
1514 AND descriptive_flexfield_name = 'FND_FLEX_VALUES'
1515 AND descriptive_flex_context_code = p_value_set;
1516
1517 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1518 FROM fnd_descr_flex_contexts_TL
1519 WHERE application_id = 0
1520 AND descriptive_flexfield_name = 'FND_FLEX_VALUES'
1521 AND descriptive_flex_context_code = p_value_set;
1522
1523 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1524 FROM fnd_descr_flex_column_usages
1525 WHERE application_id = 0
1526 AND descriptive_flexfield_name = 'FND_FLEX_VALUES'
1527 AND descriptive_flex_context_code = p_value_set;
1528
1529 DELETE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1530 FROM fnd_descr_flex_col_usage_TL
1531 WHERE application_id = 0
1532 AND descriptive_flexfield_name = 'FND_FLEX_VALUES'
1533 AND descriptive_flex_context_code = p_value_set;
1534
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 message('error occured in private_delete_valueset: ' || p_value_set);
1538 RAISE bad_parameter;
1539 END private_delete_valueset;
1540
1541 PROCEDURE delete_valueset(value_set IN VARCHAR2)
1542 IS
1543 BEGIN
1544 message_init;
1545 private_delete_valueset(p_value_set => value_set,
1546 p_force_delete => FALSE);
1547 END delete_valueset;
1548
1549
1550 PROCEDURE destructive_rename(old_value_set IN VARCHAR2,
1551 new_value_set IN VARCHAR2)
1552 IS
1553 old_value_set_id NUMBER(10);
1554 new_value_set_id NUMBER(10);
1555 BEGIN
1556 message_init;
1557 IF(old_value_set = new_value_set) THEN
1558 message('cannot replace self (old=new)');
1559 RAISE bad_parameter;
1560 END IF;
1561
1562 /* get the original value set id */
1563 BEGIN
1564 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1565 flex_value_set_id
1566 INTO old_value_set_id
1567 FROM fnd_flex_value_sets
1568 WHERE flex_value_set_name = old_value_set;
1569 EXCEPTION
1570 WHEN no_data_found THEN
1571 message('could not find original value set: ' || old_value_set);
1572 message('operation aborted');
1573 RAISE bad_parameter;
1574 END;
1575
1579 flex_value_set_id
1576 /* get the new value set id */
1577 BEGIN
1578 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1580 INTO new_value_set_id
1581 FROM fnd_flex_value_sets
1582 WHERE flex_value_set_name = new_value_set;
1583 EXCEPTION
1584 WHEN no_data_found THEN
1585 message('could not find new value set: ' || new_value_set);
1586 message('operation aborted');
1587 RAISE bad_parameter;
1588 END;
1589
1590 /* delete the old value set, and all associated entries */
1591 BEGIN
1592 private_delete_valueset(p_value_set => old_value_set,
1593 p_force_delete => TRUE);
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 message('error deleting old valueset - possible data corruption');
1597 RAISE bad_parameter;
1598 END;
1599
1600 /* rename the new value set, and change the id */
1601 BEGIN
1602 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1603 fnd_flex_value_sets SET
1604 flex_value_set_id = old_value_set_id,
1605 flex_value_set_name = old_value_set
1606 WHERE flex_value_set_id = new_value_set_id;
1607 END;
1608
1609 /* update fk references */
1610 BEGIN
1611 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1612 fnd_flex_validation_tables SET
1613 flex_value_set_id = old_value_set_id
1614 WHERE flex_value_set_id = new_value_set_id;
1615 EXCEPTION
1616 WHEN no_data_found THEN NULL;
1617 END;
1618 BEGIN
1619 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1620 fnd_flex_validation_events SET
1621 flex_value_set_id = old_value_set_id
1622 WHERE flex_value_set_id = new_value_set_id;
1623 EXCEPTION
1624 WHEN no_data_found THEN NULL;
1625 END;
1626 BEGIN
1627 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1628 fnd_flex_value_sets SET
1629 parent_flex_value_set_id = old_value_set_id
1630 WHERE parent_flex_value_set_id = new_value_set_id;
1631 EXCEPTION
1632 WHEN no_data_found THEN NULL;
1633 END;
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 message('error occured in destructive_rename.');
1637 message('SQLERRM : ' || Sqlerrm);
1638 RAISE;
1639 END;
1640
1641 /* return true if the named value set exists */
1642 FUNCTION valueset_exists(value_set IN VARCHAR2) RETURN BOOLEAN
1643 IS
1644 cnt NUMBER;
1645 BEGIN
1646 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1647 COUNT(*)
1648 INTO cnt
1649 FROM fnd_flex_value_sets
1650 WHERE flex_value_set_name = value_set;
1651 IF(cnt > 0) THEN
1652 RETURN TRUE;
1653 ELSE
1654 RETURN FALSE;
1655 END IF;
1656 END;
1657
1658
1659 PROCEDURE crosscheck_size(valueset_r IN fnd_flex_value_sets%ROWTYPE);
1660 PROCEDURE check_precision(valueset_r IN OUT nocopy fnd_flex_value_sets%ROWTYPE);
1661
1662
1663 PROCEDURE update_maxsize(
1664 value_set_name IN VARCHAR2,
1665 maxsize IN fnd_flex_value_sets.maximum_size%TYPE)
1666 IS
1667 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE;
1668 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE;
1669 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE;
1670
1671 valueset_r fnd_flex_value_sets%ROWTYPE;
1672 maxsize_old NUMBER;
1673 BEGIN
1674 last_update_login_i := last_update_login_f();
1675 last_update_date_i := last_update_date_f();
1676 last_updated_by_i := last_updated_by_f();
1677
1678 message_init;
1679 BEGIN
1680 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1681 *
1682 INTO valueset_r
1683 FROM fnd_flex_value_sets
1684 WHERE flex_value_set_name = value_set_name;
1685 EXCEPTION
1686 WHEN no_data_found THEN
1687 message('could not find valueset ' || value_set_name);
1688 RAISE bad_parameter;
1689 END;
1690
1691 maxsize_old := valueset_r.maximum_size;
1692 valueset_r.maximum_size := maxsize;
1693
1694 --
1695 -- maxsize cannot be decreased for a database record.
1696 --
1697 IF (maxsize < maxsize_old) THEN
1698 fnd_message.set_name('FND', 'FLEX-CANNOT REDUCE MAX SIZE');
1699 app_exception.raise_exception;
1700 END IF;
1701
1702 --
1703 -- Maximum_size cannot be changed if right justify is on
1704 --
1705 IF (valueset_r.numeric_mode_enabled_flag = 'Y') THEN
1706 fnd_message.set_name('FND', 'FLEX-NO SIZE CHANGE WITH NUM');
1707 app_exception.raise_exception;
1708 END IF;
1709
1710 --
1711 -- maxsize must be positive
1712 --
1713 IF (maxsize < 1) THEN
1714 fnd_message.set_name('FND','FLEX-Max size must be positive');
1715 app_exception.raise_exception;
1716 END IF;
1717
1718 --
1719 -- if format_type is date, datetime, or time, make sure maxsize is an
1720 -- acceptable value.
1721 --
1722 IF(valueset_r.format_type = 'D') THEN
1723 IF (maxsize NOT IN ('9','11')) THEN
1727 ELSIF(valueset_r.format_type = 'T') THEN
1724 fnd_message.set_name('FND','FLEX-Bad Date Length');
1725 app_exception.raise_exception;
1726 END IF;
1728 IF (maxsize NOT IN ('15','17','18','20')) THEN
1729 fnd_message.set_name('FND','FLEX-Bad DateTime Length');
1730 app_exception.raise_exception;
1731 END IF;
1732 ELSIF(valueset_r.format_type = 't') THEN
1733 IF (maxsize NOT IN (5,8)) THEN
1734 fnd_message.set_name('FND','FLEX-Bad Time Length');
1735 app_exception.raise_exception;
1736 END IF;
1737 ELSIF(valueset_r.format_type = 'N') THEN
1738 IF (maxsize > 38) THEN
1739 fnd_message.set_name('FND','FLEX-Bad Num Length');
1740 app_exception.raise_exception;
1741 END IF;
1742 END IF;
1743
1744 crosscheck_size(valueset_r);
1745 check_precision(valueset_r);
1746
1747 println('about to do update');
1748
1749 BEGIN
1750 IF(customer_mode) THEN
1751 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1752 fnd_flex_value_sets SET
1753 maximum_size = valueset_r.maximum_size,
1754 number_precision = valueset_r.number_precision,
1755 last_update_date = last_update_date_i,
1756 last_updated_by = last_updated_by_i,
1757 last_update_login = last_update_login_i
1758 WHERE flex_value_set_id = valueset_r.flex_value_set_id;
1759 ELSE
1760 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1761 fnd_flex_value_sets SET
1762 maximum_size = valueset_r.maximum_size,
1763 number_precision = valueset_r.number_precision
1764 WHERE flex_value_set_id = valueset_r.flex_value_set_id;
1765 END IF;
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 message('error updating fnd_flex_value_sets: ' || Sqlerrm);
1769 RAISE;
1770 END;
1771 EXCEPTION
1772 WHEN OTHERS THEN
1773 message('error occured in update_maxsize while processing value set ' ||
1774 value_set_name);
1775 message(Sqlerrm);
1776 RAISE bad_parameter;
1777 END update_maxsize;
1778
1779
1780
1781 -- Cross check maximum size against table value column size, reducing
1782 -- maximum size if needed. Do not do cross check if not table-validated
1783 -- or if type is date or if either size is null.
1784
1785 PROCEDURE crosscheck_size(valueset_r IN fnd_flex_value_sets%ROWTYPE)
1786 IS
1787 table_r fnd_flex_validation_tables%ROWTYPE;
1788 BEGIN
1789 IF(valueset_r.validation_type = 'F') THEN
1790 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1791 *
1792 INTO table_r
1793 FROM fnd_flex_validation_tables
1794 WHERE flex_value_set_id = valueset_r.flex_value_set_id;
1795
1796 IF((valueset_r.validation_type = 'F') AND
1797 (table_r.value_column_type <> 'D') AND
1798 (valueset_r.maximum_size IS NOT NULL) AND
1799 (table_r.value_column_size IS NOT NULL) AND
1800 (valueset_r.maximum_size > table_r.value_column_size))
1801 THEN
1802 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1803 fnd_flex_validation_tables SET
1804 value_column_size = valueset_r.maximum_size
1805 WHERE flex_value_set_id = valueset_r.flex_value_set_id;
1806 println('increasing value column size');
1807 END IF;
1808 END IF;
1809 EXCEPTION
1810 WHEN no_data_found THEN
1811 message('internal inconsistency - could not locate table' ||
1812 ' validated valueset info for #' ||
1813 valueset_r.flex_value_set_id);
1814 RAISE bad_parameter;
1815 END crosscheck_size;
1816
1817
1818
1819 -- Make sure precision < Max size (if both are non-null)
1820
1821 PROCEDURE check_precision(valueset_r IN OUT nocopy fnd_flex_value_sets%ROWTYPE)
1822 IS
1823 BEGIN
1824 IF((valueset_r.maximum_size IS NOT NULL) AND
1825 (valueset_r.number_precision IS NOT NULL) AND
1826 (valueset_r.maximum_size <= valueset_r.number_precision)) THEN
1827 valueset_r.number_precision := valueset_r.maximum_size - 1;
1828 END IF;
1829 END check_precision;
1830
1831
1832 PROCEDURE check_id_size(valueset_r IN fnd_flex_value_sets%ROWTYPE,
1833 table_r IN fnd_flex_validation_tables%ROWTYPE);
1834
1835 PROCEDURE check_meaning_size(valueset_r IN fnd_flex_value_sets%ROWTYPE,
1836 table_r IN fnd_flex_validation_tables%ROWTYPE);
1837
1838
1839 PROCEDURE update_table_sizes(
1840 value_set_name IN VARCHAR2,
1841 id_size IN fnd_flex_validation_tables.id_column_size%TYPE
1842 DEFAULT NULL,
1843 value_size IN fnd_flex_validation_tables.value_column_size%TYPE
1844 DEFAULT NULL,
1845 meaning_size IN fnd_flex_validation_tables.meaning_column_size%TYPE
1846 DEFAULT NULL)
1847 IS
1848 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE;
1849 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE;
1850 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE;
1851
1852 valueset_r fnd_flex_value_sets%ROWTYPE;
1853 table_r fnd_flex_validation_tables%ROWTYPE;
1854 BEGIN
1855 last_update_login_i := last_update_login_f();
1859 message_init;
1856 last_update_date_i := last_update_date_f();
1857 last_updated_by_i := last_updated_by_f();
1858
1860 BEGIN
1861 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1862 *
1863 INTO valueset_r
1864 FROM fnd_flex_value_sets
1865 WHERE flex_value_set_name = value_set_name;
1866 EXCEPTION
1867 WHEN no_data_found THEN
1868 message('could not find valueset ' || value_set_name);
1869 RAISE bad_parameter;
1870 END;
1871 IF(valueset_r.validation_type <> 'F') THEN
1872 message('this valueset does not appear to be table validated');
1873 RAISE bad_parameter;
1874 END IF;
1875 BEGIN
1876 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1877 *
1878 INTO table_r
1879 FROM fnd_flex_validation_tables
1880 WHERE flex_value_set_id = valueset_r.flex_value_set_id;
1881 EXCEPTION
1882 WHEN no_data_found THEN
1883 message('internal inconsistency - could not locate table' ||
1884 ' validated valueset info for #' ||
1885 valueset_r.flex_value_set_id);
1886 RAISE bad_parameter;
1887 END;
1888
1889
1890 IF(id_size IS NOT NULL) THEN
1891 table_r.id_column_size := id_size;
1892 check_id_size(valueset_r, table_r);
1893 END IF;
1894
1895 IF(value_size IS NOT NULL) THEN
1896 table_r.value_column_size := value_size;
1897 update_maxsize(value_set_name, value_size);
1898 END IF;
1899
1900 IF(meaning_size IS NOT NULL) THEN
1901 table_r.meaning_column_size := meaning_size;
1902 check_meaning_size(valueset_r, table_r);
1903 END IF;
1904
1905 BEGIN
1906 IF(customer_mode) THEN
1907 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1908 fnd_flex_validation_tables SET
1909 id_column_size = table_r.id_column_size,
1910 -- value_column_size = table_r.value_column_size,
1911 meaning_column_size = table_r.meaning_column_size,
1912 last_update_date = last_update_date_i,
1913 last_updated_by = last_updated_by_i,
1914 last_update_login = last_update_login_i
1915 WHERE flex_value_set_id = table_r.flex_value_set_id;
1916 ELSE
1917 UPDATE /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1918 fnd_flex_validation_tables SET
1919 id_column_size = table_r.id_column_size,
1920 -- value_column_size = table_r.value_column_size,
1921 meaning_column_size = table_r.meaning_column_size
1922 WHERE flex_value_set_id = table_r.flex_value_set_id;
1923 END IF;
1924 EXCEPTION
1925 WHEN OTHERS THEN
1926 message('error updating fnd_flex_validation_tables: ' || Sqlerrm);
1927 RAISE;
1928 END;
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 message('error in update_table_sizes: ' || Sqlerrm);
1932 RAISE bad_parameter;
1933 END update_table_sizes;
1934
1935
1936 PROCEDURE check_id_size(valueset_r IN fnd_flex_value_sets%ROWTYPE,
1937 table_r IN fnd_flex_validation_tables%ROWTYPE)
1938 IS
1939 width_i NUMBER;
1940 BEGIN
1941 BEGIN
1942 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1943 width
1944 INTO width_i
1945 FROM fnd_columns c, fnd_tables t
1946 WHERE (t.application_id = table_r.table_application_id
1947 OR table_r.table_application_id IS NULL)
1948 AND t.table_id = c.table_id
1949 AND t.application_id = c.application_id
1950 AND c.column_name = table_r.id_column_name
1951 AND t.table_name = table_r.application_table_name;
1952 EXCEPTION
1953 WHEN no_data_found THEN
1954 -- it is possible for the table to not be specified properly
1955 NULL;
1956 END;
1957 IF (table_r.id_column_size > width_i) THEN
1958 fnd_message.set_name('FND','FLEX-COLUMN WIDTH ERROR');
1959 fnd_message.set_token('SIZE',To_char(width_i));
1960 app_exception.raise_exception;
1961 END IF;
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 message('check id size failed: ' || Sqlerrm);
1965 RAISE;
1966 END;
1967
1968 PROCEDURE check_meaning_size(valueset_r IN fnd_flex_value_sets%ROWTYPE,
1969 table_r IN fnd_flex_validation_tables%ROWTYPE)
1970 IS
1971 width_i NUMBER;
1972 BEGIN
1973 BEGIN
1974 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
1975 width
1976 INTO width_i
1977 FROM fnd_columns c, fnd_tables t
1978 WHERE (t.application_id = table_r.table_application_id
1979 OR table_r.table_application_id IS NULL)
1980 AND t.table_id = c.table_id
1981 AND t.application_id = c.application_id
1982 AND c.column_name = table_r.meaning_column_name
1983 AND t.table_name = table_r.application_table_name;
1984 EXCEPTION
1985 WHEN no_data_found THEN
1986 -- it is possible for the table to not be specified properly
1987 NULL;
1988 END;
1989 IF(table_r.meaning_column_size > width_i) THEN
1990 fnd_message.set_name('FND','FLEX-COLUMN WIDTH ERROR');
1991 fnd_message.set_token('SIZE',To_char(width_i));
1992 app_exception.raise_exception;
1993 END IF;
1994 EXCEPTION
1995 WHEN OTHERS THEN
1999
1996 message('check meaning size failed: ' || Sqlerrm);
1997 RAISE;
1998 END check_meaning_size;
2000 --------------------------------------------------------------
2001 -- Getting Select Statements For Value Sets.
2002 --------------------------------------------------------------
2003
2004 PROCEDURE add_to_select(p_inc_col IN VARCHAR2,
2005 p_col_name IN VARCHAR2,
2006 p_map_code IN VARCHAR2,
2007 x_select IN OUT nocopy VARCHAR2,
2008 x_mapping_code IN OUT nocopy VARCHAR2)
2009 IS
2010 BEGIN
2011 x_mapping_code := x_mapping_code || p_map_code;
2012 IF (p_inc_col = 'Y' AND p_col_name IS NOT NULL) THEN
2013 x_select := x_select || ',' || chr_newline || p_col_name ;
2014 x_mapping_code := x_mapping_code || '1';
2015 ELSE
2016 x_mapping_code := x_mapping_code || '0';
2017 END IF;
2018 END add_to_select;
2019
2020 PROCEDURE get_valueset_select
2021 (p_validation_type IN VARCHAR2,
2022 p_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
2023 DEFAULT fnd_api.g_miss_char,
2024 p_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
2025 DEFAULT fnd_api.g_miss_num,
2026 p_independent_value IN VARCHAR2 DEFAULT NULL,
2027 --
2028 -- Do you want to include these columns in SELECT statement?
2029 -- VALUE column is always included.
2030 -- ID and MEANING columns are included by default.
2031 --
2032 p_inc_id_col IN VARCHAR2 DEFAULT 'Y',
2033 p_inc_meaning_col IN VARCHAR2 DEFAULT 'Y',
2034 p_inc_enabled_col IN VARCHAR2 DEFAULT 'N',
2035 p_inc_start_date_col IN VARCHAR2 DEFAULT 'N',
2036 p_inc_end_date_col IN VARCHAR2 DEFAULT 'N',
2037 p_inc_summary_col IN VARCHAR2 DEFAULT 'N',
2038 p_inc_compiled_attribute_col IN VARCHAR2 DEFAULT 'N',
2039 p_inc_hierarchy_level_col IN VARCHAR2 DEFAULT 'N',
2040 p_inc_addtl_user_columns IN VARCHAR2 DEFAULT 'N',
2041 p_additional_user_columns IN VARCHAR2 DEFAULT NULL,
2042 p_inc_addtl_quickpick_cols IN VARCHAR2 DEFAULT 'N',
2043 --
2044 -- Do you want to add extra checks in SELECT?
2045 --
2046 p_check_enabled_flag IN VARCHAR2 DEFAULT 'Y',
2047 p_check_validation_date IN VARCHAR2 DEFAULT 'Y',
2048 p_validation_date_char IN VARCHAR2 DEFAULT 'SYSDATE',
2049 p_inc_user_where_clause IN VARCHAR2 DEFAULT 'N',
2050 p_user_where_clause IN VARCHAR2 DEFAULT NULL,
2051 p_inc_addtl_where_clause IN VARCHAR2 DEFAULT 'Y',
2052
2053 x_select OUT nocopy VARCHAR2,
2054 x_mapping_code OUT nocopy VARCHAR2,
2055 x_success OUT nocopy NUMBER)
2056 IS
2057 l_func_name VARCHAR2(100);
2058 l_vset fnd_flex_value_sets%ROWTYPE;
2059 l_tvset fnd_flex_validation_tables%ROWTYPE;
2060
2061 l_and VARCHAR2(10);
2062 l_select VARCHAR2(32000);
2063 l_inc_addtl_where_clause VARCHAR2(1);
2064 l_inc_addtl_quickpick_cols VARCHAR2(1);
2065 l_mapping_code VARCHAR2(100);
2066 l_number NUMBER;
2067 BEGIN
2068 l_func_name := 'get_valueset_select() : ';
2069 l_and := chr_newline || 'AND (';
2070
2071 l_inc_addtl_where_clause := p_inc_addtl_where_clause;
2072 l_inc_addtl_quickpick_cols := p_inc_addtl_quickpick_cols;
2073 x_select := NULL;
2074 x_success := g_ret_no_error;
2075 x_mapping_code := NULL;
2076 --
2077 -- We really need XOR.
2078 --
2079 IF(p_value_set_name = fnd_api.g_miss_char AND
2080 p_value_set_id = fnd_api.g_miss_num)
2081 OR
2082 (p_value_set_name <> fnd_api.g_miss_char AND
2083 p_value_set_id <> fnd_api.g_miss_num)
2084 OR
2085 (p_value_set_id IS NULL OR p_value_set_name IS NULL) THEN
2086 message(l_func_name || 'Invalid value set name or id is passed.');
2087 x_success := g_ret_invalid_parameter;
2088 RETURN;
2089 END IF;
2090
2091 --
2092 -- At this point either value_set_[id or name] is passed not both.
2093 --
2094 BEGIN
2095 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
2096 *
2097 INTO l_vset
2098 FROM fnd_flex_value_sets
2099 WHERE ( (p_value_set_id = fnd_api.g_miss_num
2100 AND flex_value_set_name = p_value_set_name)
2101 OR (p_value_set_name = fnd_api.g_miss_char
2102 AND flex_value_set_id = p_value_set_id));
2103 EXCEPTION
2104 WHEN no_data_found THEN
2105 message(l_func_name || 'Value set does not exist.');
2106 x_success := g_ret_no_value_set;
2107 RETURN;
2108 WHEN OTHERS THEN
2109 message(l_func_name || 'SELECT FROM fnd_flex_value_sets is failed.' ||
2110 chr_newline || 'Error : ' || Sqlerrm);
2111 x_success := g_ret_others;
2112 RETURN;
2113 END;
2114 IF (l_vset.validation_type <> p_validation_type) THEN
2115 message(l_func_name || 'Validation Type Mismatch.');
2116 x_success := g_ret_vtype_mismatch;
2117 RETURN;
2118 END IF;
2119
2120 IF (l_vset.validation_type = 'F') THEN
2121 BEGIN
2122 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
2123 *
2124 INTO l_tvset
2125 FROM fnd_flex_validation_tables
2129 message(l_func_name || 'Table Info does not exist.');
2126 WHERE flex_value_set_id = l_vset.flex_value_set_id;
2127 EXCEPTION
2128 WHEN no_data_found THEN
2130 x_success := g_ret_no_table_info;
2131 RETURN;
2132 WHEN OTHERS THEN
2133 message(l_func_name || 'SELECT FROM fnd_flex_validation_tables '||
2134 'is failed.' || chr_newline || 'Error : ' || Sqlerrm);
2135 x_success := g_ret_others;
2136 RETURN;
2137 END;
2138 ELSIF (l_vset.validation_type IN ('I','D')) THEN
2139 l_tvset.application_table_name := 'FND_FLEX_VALUES_VL';
2140
2141 l_tvset.value_column_name := 'FLEX_VALUE';
2142 l_tvset.value_column_type := 'V';
2143 l_tvset.value_column_size := 150;
2144
2145 l_tvset.id_column_name := 'FLEX_VALUE';
2146 l_tvset.id_column_type := 'V';
2147 l_tvset.id_column_size := 150;
2148
2149 l_tvset.meaning_column_name := 'DESCRIPTION';
2150 l_tvset.meaning_column_type := 'V';
2151 l_tvset.meaning_column_size := 240;
2152
2153 l_tvset.compiled_attribute_column_name := 'COMPILED_VALUE_ATTRIBUTES';
2154 l_tvset.enabled_column_name := 'ENABLED_FLAG';
2155 l_tvset.hierarchy_level_column_name := 'STRUCTURED_HIERARCHY_LEVEL';
2156 l_tvset.start_date_column_name := 'START_DATE_ACTIVE';
2157 l_tvset.end_date_column_name := 'END_DATE_ACTIVE';
2158 l_tvset.summary_column_name := 'SUMMARY_FLAG';
2159
2160 l_tvset.additional_quickpick_columns := NULL;
2161 l_inc_addtl_where_clause := 'Y';
2162 IF (l_vset.validation_type = 'I') THEN
2163 l_tvset.additional_where_clause := 'FLEX_VALUE_SET_ID = ' ||
2164 To_char(l_vset.flex_value_set_id);
2165 ELSIF (l_vset.validation_type = 'D') THEN
2166 IF (p_independent_value IS NULL) THEN
2167 message(l_func_name || 'NULL is not a valid independent value.');
2168 x_success := g_ret_no_parent_value;
2169 RETURN;
2170 END IF;
2171 l_tvset.additional_where_clause := 'FLEX_VALUE_SET_ID = ' ||
2172 To_char(l_vset.flex_value_set_id) || chr_newline ||
2173 'AND PARENT_FLEX_VALUE_LOW = ''' || p_independent_value || '''';
2174 END IF;
2175 ELSE
2176 message(l_func_name || 'Unknown validation type is passed.');
2177 x_success := g_ret_vtype_not_supported;
2178 RETURN;
2179 END IF;
2180
2181 --
2182 -- Construct SELECT upto WHERE clause.
2183 --
2184 l_select := 'SELECT ' || l_tvset.value_column_name;
2185 l_mapping_code := 'S:VA1';
2186 add_to_select(p_inc_id_col,
2187 l_tvset.id_column_name,'ID',
2188 l_select, l_mapping_code);
2189 add_to_select(p_inc_meaning_col,
2190 l_tvset.meaning_column_name,'ME',
2191 l_select, l_mapping_code);
2192 add_to_select(p_inc_enabled_col,
2193 l_tvset.enabled_column_name,'EN',
2194 l_select, l_mapping_code);
2195 add_to_select(p_inc_start_date_col,
2196 l_tvset.start_date_column_name,'SD',
2197 l_select, l_mapping_code);
2198 add_to_select(p_inc_end_date_col,
2199 l_tvset.end_date_column_name,'ED',
2200 l_select, l_mapping_code);
2201 add_to_select(p_inc_summary_col,
2202 l_tvset.summary_column_name,'SM',
2203 l_select, l_mapping_code);
2204 add_to_select(p_inc_compiled_attribute_col,
2205 l_tvset.compiled_attribute_column_name,'CA',
2206 l_select, l_mapping_code);
2207 add_to_select(p_inc_hierarchy_level_col,
2208 l_tvset.hierarchy_level_column_name,'HL',
2209 l_select, l_mapping_code);
2210 add_to_select(p_inc_addtl_user_columns,
2211 p_additional_user_columns,'AU',
2212 l_select, l_mapping_code);
2213 --
2214 -- Additional Quickpick columns may contain INTO statement
2215 --
2216 l_number := Instr(Upper(l_tvset.additional_quickpick_columns),' INTO ');
2217 IF (l_number > 0) THEN
2218 l_inc_addtl_quickpick_cols := 'N';
2219 END IF;
2220 --
2221 -- Additional columns may also have (*) or ([123456789]) in it.
2222 -- However PL/SQL doesn't have regular expression search functionality.
2223 --
2224 add_to_select(l_inc_addtl_quickpick_cols,
2225 l_tvset.additional_quickpick_columns,'AQ',
2226 l_select, l_mapping_code);
2227
2228 l_select := (l_select || chr_newline || 'FROM ' ||
2229 l_tvset.application_table_name || chr_newline);
2230
2231 --
2232 -- Now WHERE clause.
2233 --
2234 l_select := l_select || 'WHERE (1 = 1)';
2235 l_mapping_code := l_mapping_code || 'W:WW1';
2236 IF (p_check_enabled_flag = 'Y' AND
2237 l_tvset.enabled_column_name IS NOT NULL) THEN
2238 l_select := (l_select || l_and ||
2239 l_tvset.enabled_column_name || ' = ''Y'')');
2240 l_mapping_code := l_mapping_code || 'EF1';
2241 ELSE
2242 l_mapping_code := l_mapping_code || 'EF0';
2243 END IF;
2244 l_number := 0;
2245 IF (p_check_validation_date = 'Y') THEN
2246 IF (l_tvset.start_date_column_name IS NOT NULL) THEN
2247 l_select := (l_select || l_and || l_tvset.start_date_column_name ||
2248 ' IS NULL OR ' ||
2249 l_tvset.start_date_column_name || ' <= ' ||
2250 p_validation_date_char || ')');
2251 l_number := l_number + 1;
2252 END IF;
2253 IF (l_tvset.end_date_column_name IS NOT NULL) THEN
2254 l_select := (l_select || l_and || l_tvset.end_date_column_name ||
2255 ' IS NULL OR ' ||
2259 END IF;
2256 l_tvset.end_date_column_name || ' >= ' ||
2257 p_validation_date_char || ')');
2258 l_number := l_number + 2;
2260 END IF;
2261 l_mapping_code := l_mapping_code || 'VD' || To_char(l_number);
2262 IF (p_inc_user_where_clause = 'Y' AND
2263 p_user_where_clause IS NOT NULL) THEN
2264 l_select := l_select || l_and || p_user_where_clause || ')';
2265 l_mapping_code := l_mapping_code || 'UW1';
2266 ELSE
2267 l_mapping_code := l_mapping_code || 'UW0';
2268 END IF;
2269 IF (l_inc_addtl_where_clause = 'Y' AND
2270 l_tvset.additional_where_clause IS NOT NULL) THEN
2271 l_tvset.additional_where_clause :=
2272 Ltrim(l_tvset.additional_where_clause);
2273 l_number := Instr(Upper(l_tvset.additional_where_clause),'WHERE ');
2274 IF (l_number = 1) THEN
2275 l_tvset.additional_where_clause :=
2276 Substr(l_tvset.additional_where_clause,7);
2277 END IF;
2278 --
2279 -- It may be only ORDER BY clause.
2280 --
2281 l_number := Instr(Upper(l_tvset.additional_where_clause),'ORDER BY ');
2282 IF (l_number = 1) THEN
2283 l_select := l_select || chr_newline ||
2284 l_tvset.additional_where_clause;
2285 ELSE
2286 l_select := l_select || chr_newline || 'AND ' ||
2287 l_tvset.additional_where_clause;
2288 END IF;
2289 l_mapping_code := l_mapping_code || 'AW1';
2290 ELSE
2291 l_mapping_code := l_mapping_code || 'AW0';
2292 END IF;
2293
2294 x_select := l_select;
2295 x_mapping_code := l_mapping_code;
2296 x_success := g_ret_no_error;
2297 EXCEPTION
2298 WHEN OTHERS THEN
2299 message(l_func_name || ' is failed.' || chr_newline ||
2300 'Error : ' || Sqlerrm);
2301 x_success := g_ret_others;
2302 END get_valueset_select;
2303
2304 PROCEDURE get_table_vset_select
2305 (p_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
2306 DEFAULT fnd_api.g_miss_char,
2307 p_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
2308 DEFAULT fnd_api.g_miss_num,
2309 --
2310 -- Do you want to include these columns in SELECT statement?
2311 -- VALUE column is always included.
2312 -- ID and MEANING columns are included by default.
2313 --
2314 p_inc_id_col IN VARCHAR2 DEFAULT 'Y',
2315 p_inc_meaning_col IN VARCHAR2 DEFAULT 'Y',
2316 p_inc_enabled_col IN VARCHAR2 DEFAULT 'N',
2317 p_inc_start_date_col IN VARCHAR2 DEFAULT 'N',
2318 p_inc_end_date_col IN VARCHAR2 DEFAULT 'N',
2319 p_inc_summary_col IN VARCHAR2 DEFAULT 'N',
2320 p_inc_compiled_attribute_col IN VARCHAR2 DEFAULT 'N',
2321 p_inc_hierarchy_level_col IN VARCHAR2 DEFAULT 'N',
2322 p_inc_addtl_user_columns IN VARCHAR2 DEFAULT 'N',
2323 p_additional_user_columns IN VARCHAR2 DEFAULT NULL,
2324 p_inc_addtl_quickpick_cols IN VARCHAR2 DEFAULT 'N',
2325 --
2326 -- Do you want to add extra checks in SELECT?
2327 --
2328 p_check_enabled_flag IN VARCHAR2 DEFAULT 'Y',
2329 p_check_validation_date IN VARCHAR2 DEFAULT 'Y',
2330 p_validation_date_char IN VARCHAR2 DEFAULT 'SYSDATE',
2331 p_inc_user_where_clause IN VARCHAR2 DEFAULT 'N',
2332 p_user_where_clause IN VARCHAR2 DEFAULT NULL,
2333 p_inc_addtl_where_clause IN VARCHAR2 DEFAULT 'Y',
2334
2335 x_select OUT NOCOPY VARCHAR2,
2336 x_mapping_code OUT NOCOPY VARCHAR2,
2337 x_success OUT NOCOPY NUMBER)
2338 IS
2339 l_success NUMBER;
2340 l_func_name VARCHAR2(100);
2341 BEGIN
2342 l_func_name := 'get_table_vset_select() : ';
2343
2344 message_init;
2345 get_valueset_select
2346 (p_validation_type => 'F',
2347 p_value_set_name => p_value_set_name,
2348 p_value_set_id => p_value_set_id,
2349 p_independent_value => NULL,
2350 p_inc_id_col => p_inc_id_col,
2351 p_inc_meaning_col => p_inc_meaning_col,
2352 p_inc_enabled_col => p_inc_enabled_col,
2353 p_inc_start_date_col => p_inc_start_date_col,
2354 p_inc_end_date_col => p_inc_end_date_col,
2355 p_inc_summary_col => p_inc_summary_col,
2356 p_inc_compiled_attribute_col => p_inc_compiled_attribute_col,
2357 p_inc_hierarchy_level_col => p_inc_hierarchy_level_col,
2358 p_inc_addtl_user_columns => p_inc_addtl_user_columns,
2359 p_additional_user_columns => p_additional_user_columns,
2360 p_inc_addtl_quickpick_cols => p_inc_addtl_quickpick_cols,
2361 p_check_enabled_flag => p_check_enabled_flag,
2362 p_check_validation_date => p_check_validation_date,
2363 p_validation_date_char => p_validation_date_char,
2364 p_inc_user_where_clause => p_inc_user_where_clause,
2365 p_user_where_clause => p_user_where_clause,
2366 p_inc_addtl_where_clause => p_inc_addtl_where_clause,
2367 x_select => x_select,
2368 x_mapping_code => x_mapping_code,
2369 x_success => l_success);
2370 x_success := l_success;
2371 IF (l_success = fnd_flex_val_api.g_ret_vtype_mismatch) THEN
2372 message(l_func_name || 'Value set is not table validated.');
2373 x_success := g_ret_not_table_validated;
2374 END IF;
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 message(l_func_name || ' is failed.' || chr_newline ||
2381
2378 'Error : ' || Sqlerrm);
2379 x_success := g_ret_others;
2380 END get_table_vset_select;
2382 PROCEDURE get_independent_vset_select
2383 (p_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
2384 DEFAULT fnd_api.g_miss_char,
2385 p_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
2386 DEFAULT fnd_api.g_miss_num,
2387 --
2388 -- Do you want to include these columns in SELECT statement?
2389 -- VALUE column is always included.
2390 -- ID and MEANING columns are included by default.
2391 --
2392 p_inc_id_col IN VARCHAR2 DEFAULT 'Y',
2393 p_inc_meaning_col IN VARCHAR2 DEFAULT 'Y',
2394 p_inc_enabled_col IN VARCHAR2 DEFAULT 'N',
2395 p_inc_start_date_col IN VARCHAR2 DEFAULT 'N',
2396 p_inc_end_date_col IN VARCHAR2 DEFAULT 'N',
2397 p_inc_summary_col IN VARCHAR2 DEFAULT 'N',
2398 p_inc_compiled_attribute_col IN VARCHAR2 DEFAULT 'N',
2399 p_inc_hierarchy_level_col IN VARCHAR2 DEFAULT 'N',
2400 --
2401 -- Do you want to add extra checks in SELECT?
2402 --
2403 p_check_enabled_flag IN VARCHAR2 DEFAULT 'Y',
2404 p_check_validation_date IN VARCHAR2 DEFAULT 'Y',
2405 p_validation_date_char IN VARCHAR2 DEFAULT 'SYSDATE',
2406 p_inc_user_where_clause IN VARCHAR2 DEFAULT 'N',
2407 p_user_where_clause IN VARCHAR2 DEFAULT NULL,
2408
2409 x_select OUT NOCOPY VARCHAR2,
2410 x_mapping_code OUT NOCOPY VARCHAR2,
2411 x_success OUT NOCOPY NUMBER)
2412 IS
2413 l_success NUMBER;
2414 l_func_name VARCHAR2(100);
2415 BEGIN
2416 l_func_name := 'get_independent_vset_select() : ';
2417
2418 message_init;
2419 get_valueset_select
2420 (p_validation_type => 'I',
2421 p_value_set_name => p_value_set_name,
2422 p_value_set_id => p_value_set_id,
2423 p_independent_value => NULL,
2424 p_inc_id_col => p_inc_id_col,
2425 p_inc_meaning_col => p_inc_meaning_col,
2426 p_inc_enabled_col => p_inc_enabled_col,
2427 p_inc_start_date_col => p_inc_start_date_col,
2428 p_inc_end_date_col => p_inc_end_date_col,
2429 p_inc_summary_col => p_inc_summary_col,
2430 p_inc_compiled_attribute_col => p_inc_compiled_attribute_col,
2431 p_inc_hierarchy_level_col => p_inc_hierarchy_level_col,
2432 p_inc_addtl_user_columns => 'N',
2433 p_additional_user_columns => NULL,
2434 p_inc_addtl_quickpick_cols => 'N',
2435 p_check_enabled_flag => p_check_enabled_flag,
2436 p_check_validation_date => p_check_validation_date,
2437 p_validation_date_char => p_validation_date_char,
2438 p_inc_user_where_clause => p_inc_user_where_clause,
2439 p_user_where_clause => p_user_where_clause,
2440 p_inc_addtl_where_clause => 'N',
2441 x_select => x_select,
2442 x_mapping_code => x_mapping_code,
2443 x_success => l_success);
2444 x_success := l_success;
2445 IF (l_success = fnd_flex_val_api.g_ret_vtype_mismatch) THEN
2446 message(l_func_name || 'Value set is not independent.');
2447 l_success := g_ret_not_indep_validated;
2448 END IF;
2449 EXCEPTION
2450 WHEN OTHERS THEN
2451 message(l_func_name || ' is failed.' || chr_newline ||
2452 'Error : ' || Sqlerrm);
2453 x_success := g_ret_others;
2454 END get_independent_vset_select;
2455
2456 PROCEDURE get_dependent_vset_select
2457 (p_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
2458 DEFAULT fnd_api.g_miss_char,
2459 p_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
2460 DEFAULT fnd_api.g_miss_num,
2461 p_independent_value IN VARCHAR2 DEFAULT NULL,
2462 --
2463 -- Do you want to include these columns in SELECT statement?
2464 -- VALUE column is always included.
2465 -- ID and MEANING columns are included by default.
2466 --
2467 p_inc_id_col IN VARCHAR2 DEFAULT 'Y',
2468 p_inc_meaning_col IN VARCHAR2 DEFAULT 'Y',
2469 p_inc_enabled_col IN VARCHAR2 DEFAULT 'N',
2470 p_inc_start_date_col IN VARCHAR2 DEFAULT 'N',
2471 p_inc_end_date_col IN VARCHAR2 DEFAULT 'N',
2472 p_inc_summary_col IN VARCHAR2 DEFAULT 'N',
2473 p_inc_compiled_attribute_col IN VARCHAR2 DEFAULT 'N',
2474 p_inc_hierarchy_level_col IN VARCHAR2 DEFAULT 'N',
2475 --
2476 -- Do you want to add extra checks in SELECT?
2477 --
2478 p_check_enabled_flag IN VARCHAR2 DEFAULT 'Y',
2479 p_check_validation_date IN VARCHAR2 DEFAULT 'Y',
2480 p_validation_date_char IN VARCHAR2 DEFAULT 'SYSDATE',
2481 p_inc_user_where_clause IN VARCHAR2 DEFAULT 'N',
2482 p_user_where_clause IN VARCHAR2 DEFAULT NULL,
2483
2484 x_select OUT NOCOPY VARCHAR2,
2485 x_mapping_code OUT NOCOPY VARCHAR2,
2486 x_success OUT NOCOPY NUMBER)
2487 IS
2488 l_success NUMBER;
2489 l_func_name VARCHAR2(100);
2490 BEGIN
2491 l_func_name := 'get_dependent_vset_select() : ';
2492
2493 message_init;
2494 get_valueset_select
2495 (p_validation_type => 'D',
2499 p_inc_id_col => p_inc_id_col,
2496 p_value_set_name => p_value_set_name,
2497 p_value_set_id => p_value_set_id,
2498 p_independent_value => p_independent_value,
2500 p_inc_meaning_col => p_inc_meaning_col,
2501 p_inc_enabled_col => p_inc_enabled_col,
2502 p_inc_start_date_col => p_inc_start_date_col,
2503 p_inc_end_date_col => p_inc_end_date_col,
2504 p_inc_summary_col => p_inc_summary_col,
2505 p_inc_compiled_attribute_col => p_inc_compiled_attribute_col,
2506 p_inc_hierarchy_level_col => p_inc_hierarchy_level_col,
2507 p_inc_addtl_user_columns => 'N',
2508 p_additional_user_columns => NULL,
2509 p_inc_addtl_quickpick_cols => 'N',
2510 p_check_enabled_flag => p_check_enabled_flag,
2511 p_check_validation_date => p_check_validation_date,
2512 p_validation_date_char => p_validation_date_char,
2513 p_inc_user_where_clause => p_inc_user_where_clause,
2514 p_user_where_clause => p_user_where_clause,
2515 p_inc_addtl_where_clause => 'N',
2516 x_select => x_select,
2517 x_mapping_code => x_mapping_code,
2518 x_success => l_success);
2519 x_success := l_success;
2520 IF (l_success = fnd_flex_val_api.g_ret_vtype_mismatch) THEN
2521 message(l_func_name || 'Value set is not dependent.');
2522 x_success := g_ret_not_dep_validated;
2523 END IF;
2524 EXCEPTION
2525 WHEN OTHERS THEN
2526 message(l_func_name || ' is failed.' || chr_newline ||
2527 'Error : ' || Sqlerrm);
2528 x_success := g_ret_others;
2529 END get_dependent_vset_select;
2530
2531 FUNCTION is_table_used(p_application_id IN fnd_tables.application_id%TYPE,
2532 p_table_name IN fnd_tables.table_name%TYPE,
2533 x_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN
2534 IS
2535 up_table_name fnd_tables.table_name%TYPE;
2536 l_vset_name fnd_flex_value_sets.flex_value_set_name%TYPE;
2537 BEGIN
2538 up_table_name := Upper(p_table_name);
2539
2540 x_message := 'This table is not used by Flexfield Value Sets';
2541 BEGIN
2542 SELECT fvs.flex_value_set_name
2543 INTO l_vset_name
2544 FROM fnd_flex_value_sets fvs, fnd_flex_validation_tables fvt
2545 WHERE fvs.validation_type = 'F'
2546 AND fvs.flex_value_set_id = fvt.flex_value_set_id
2547 AND Nvl(fvt.table_application_id, p_application_id) = p_application_id
2548 AND (Upper(fvt.application_table_name) = up_table_name OR
2549 Upper(fvt.application_table_name) LIKE '% ' || up_table_name OR
2550 Upper(fvt.application_table_name) LIKE up_table_name||' %' OR
2551 Upper(fvt.application_table_name) LIKE '% '||up_table_name||' %')
2552 AND ROWNUM = 1;
2553 x_message :=
2554 'This table is used by ' || chr_newline ||
2555 'VALUE_SET : ' || l_vset_name;
2556 RETURN(TRUE);
2557 EXCEPTION
2558 WHEN no_data_found THEN
2559 NULL;
2560 WHEN OTHERS THEN
2561 x_message :=
2562 'SELECT FROM FND_FLEX_VALIDATION_TABLES is failed. '||chr_newline||
2563 'SQLERRM : ' || Sqlerrm;
2564 RETURN(TRUE);
2565 END;
2566 RETURN(FALSE);
2567 EXCEPTION
2568 WHEN OTHERS THEN
2569 x_message :=
2570 'FND_FLEX_VAL_API.IS_TABLE_USED is failed. ' || chr_newline ||
2571 'SQLERRM : ' || Sqlerrm;
2572 RETURN(TRUE);
2573 END is_table_used;
2574
2575
2576 FUNCTION is_column_used(p_application_id IN fnd_tables.application_id%TYPE,
2577 p_table_name IN fnd_tables.table_name%TYPE,
2578 p_column_name IN fnd_columns.column_name%TYPE,
2579 x_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN
2580 IS
2581 up_table_name fnd_tables.table_name%TYPE;
2582 up_column_name fnd_columns.column_name%TYPE;
2583 l_vset_name fnd_flex_value_sets.flex_value_set_name%TYPE;
2584 BEGIN
2585 up_table_name := Upper(p_table_name);
2586 up_column_name := Upper(p_column_name);
2587
2588 x_message := 'This column is not used by Flexfield Value Sets';
2589 BEGIN
2590 SELECT /* $Header: AFFFVAIB.pls 120.25.12010000.1 2008/07/25 14:14:42 appldev ship $ */
2591 fvs.flex_value_set_name
2592 INTO l_vset_name
2593 FROM fnd_flex_value_sets fvs, fnd_flex_validation_tables fvt
2594 WHERE fvs.validation_type = 'F'
2595 AND fvs.flex_value_set_id = fvt.flex_value_set_id
2596 AND Nvl(fvt.table_application_id, p_application_id) = p_application_id
2597 AND (Upper(fvt.application_table_name) = up_table_name OR
2598 Upper(fvt.application_table_name) LIKE '% ' || up_table_name OR
2599 Upper(fvt.application_table_name) LIKE up_table_name||' %' OR
2600 Upper(fvt.application_table_name) LIKE '% '||up_table_name||' %')
2601 AND (Nvl(Upper(fvt.value_column_name), 'X'||up_column_name)
2602 = up_column_name OR
2603 Nvl(Upper(fvt.compiled_attribute_column_name),'X'||up_column_name)
2604 = up_column_name OR
2605 Nvl(Upper(fvt.enabled_column_name), 'X'||up_column_name)
2606 = up_column_name OR
2607 Nvl(Upper(fvt.hierarchy_level_column_name), 'X'||up_column_name)
2608 = up_column_name OR
2609 Nvl(Upper(fvt.start_date_column_name), 'X'||up_column_name)
2610 = up_column_name OR
2611 Nvl(Upper(fvt.end_date_column_name), 'X'||up_column_name)
2612 = up_column_name OR
2616 = up_column_name OR
2613 Nvl(Upper(fvt.summary_column_name), 'X'||up_column_name)
2614 = up_column_name OR
2615 Nvl(Upper(fvt.id_column_name), 'X'||up_column_name)
2617 Nvl(Upper(fvt.meaning_column_name), 'X'||up_column_name)
2618 = up_column_name )
2619 AND ROWNUM = 1;
2620 x_message :=
2621 'This column is used by ' || chr_newline ||
2622 'VALUE_SET : ' || l_vset_name;
2623 RETURN(TRUE);
2624 EXCEPTION
2625 WHEN no_data_found THEN
2626 NULL;
2627 WHEN OTHERS THEN
2628 x_message :=
2629 'SELECT FROM FND_FLEX_VALIDATION_TABLES is failed. '||chr_newline||
2630 'SQLERRM : ' || Sqlerrm;
2631 RETURN(TRUE);
2632 END;
2633 RETURN(FALSE);
2634 EXCEPTION
2635 WHEN OTHERS THEN
2636 x_message :=
2637 'FND_FLEX_VAL_API.IS_COLUMN_USED is failed. ' || chr_newline ||
2638 'SQLERRM : ' || Sqlerrm;
2639 RETURN(TRUE);
2640 END is_column_used;
2641
2642 -- ---------------------------------------------------------------------------
2643 PROCEDURE get_vset(p_flex_value_set_name IN VARCHAR2 DEFAULT NULL,
2644 p_flex_value_set_id IN NUMBER DEFAULT NULL,
2645 x_vset OUT nocopy vset_type)
2646 IS
2647 BEGIN
2648 IF (p_flex_value_set_name IS NOT NULL) THEN
2649 BEGIN
2650 SELECT *
2651 INTO x_vset
2652 FROM fnd_flex_value_sets
2653 WHERE flex_value_set_name = p_flex_value_set_name;
2654 EXCEPTION
2655 WHEN OTHERS THEN
2656 raise_error(ERROR_UNABLE_TO_FIND_VSET_NAME,
2657 'Unable to find flex value set (name): ' ||
2658 p_flex_value_set_name);
2659 END;
2660 ELSIF (p_flex_value_set_id IS NOT NULL) THEN
2661 BEGIN
2662 SELECT *
2663 INTO x_vset
2664 FROM fnd_flex_value_sets
2665 WHERE flex_value_set_id = p_flex_value_set_id;
2666 EXCEPTION
2667 WHEN OTHERS THEN
2668 raise_error(ERROR_UNABLE_TO_FIND_VSET_ID,
2669 'Unable to find flex value set (id): ' ||
2670 p_flex_value_set_id);
2671 END;
2672 ELSE
2673 raise_error(ERROR_FLEX_CODE_ERROR,
2674 'Flex Code Error: One of the arguments must be not null.');
2675 END IF;
2676 EXCEPTION
2677 WHEN OTHERS THEN
2678 raise_error(ERROR_EXCEPTION_OTHERS,
2679 'Failure in get_vset().');
2680 END get_vset;
2681
2682 -- ---------------------------------------------------------------------------
2683 PROCEDURE get_ind_vset(p_flex_value_set_name IN VARCHAR2 DEFAULT NULL,
2684 p_flex_value_set_id IN NUMBER DEFAULT NULL,
2685 px_vset IN OUT nocopy vset_type)
2686 IS
2687 BEGIN
2688 get_vset(p_flex_value_set_name, p_flex_value_set_id, px_vset);
2689
2690 -- Make sure it is an independent value set
2691 IF (px_vset.validation_type <> 'I') THEN
2692 raise_error(ERROR_VSET_IS_NOT_INDEPENDENT,
2693 'Value set (' || px_vset.flex_value_set_name ||
2694 ') is not an independent value set.');
2695 END IF;
2696 EXCEPTION
2697 WHEN OTHERS THEN
2698 raise_error(ERROR_EXCEPTION_OTHERS,
2699 'Failure in get_ind_vset().');
2700 END get_ind_vset;
2701
2702 -- ---------------------------------------------------------------------------
2703 PROCEDURE get_dep_vset(p_flex_value_set_name IN VARCHAR2 DEFAULT NULL,
2704 p_flex_value_set_id IN NUMBER DEFAULT NULL,
2705 px_vset IN OUT nocopy vset_type)
2706 IS
2707 BEGIN
2708 get_vset(p_flex_value_set_name, p_flex_value_set_id, px_vset);
2709
2710 -- Make sure it is a dependent value set
2711 IF (px_vset.validation_type <> 'D') THEN
2712 raise_error(ERROR_VSET_IS_NOT_DEPENDENT,
2713 'Value set (' || px_vset.flex_value_set_name ||
2714 ') is not a dependent value set.');
2715 END IF;
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 raise_error(ERROR_EXCEPTION_OTHERS,
2719 'Failure in get_dep_vset().');
2720 END get_dep_vset;
2721
2722 -- ---------------------------------------------------------------------------
2723 PROCEDURE get_hierarchy_id(p_flex_value_set_name IN VARCHAR2,
2724 p_hierarchy_code IN VARCHAR2,
2725 x_hierarchy_id OUT NOCOPY NUMBER)
2726 IS
2727 l_vset vset_type;
2728 BEGIN
2729 get_vset(p_flex_value_set_name, NULL, l_vset);
2730
2731 BEGIN
2732 SELECT hierarchy_id
2733 INTO x_hierarchy_id
2734 FROM fnd_flex_hierarchies
2735 WHERE flex_value_set_id = l_vset.flex_value_set_id
2736 AND hierarchy_code = p_hierarchy_code;
2737 EXCEPTION
2738 WHEN OTHERS THEN
2739 raise_error(ERROR_UNABLE_TO_FIND_HIER_CODE,
2740 'Unable to find hierarchy code: ' ||
2741 p_hierarchy_code);
2742 END;
2743
2744 EXCEPTION
2745 WHEN OTHERS THEN
2746 raise_error(ERROR_EXCEPTION_OTHERS,
2747 'Failure in get_hierarchy_id().');
2748 END get_hierarchy_id;
2749
2750 -- ---------------------------------------------------------------------------
2751 PROCEDURE check_value_existance(p_vset IN vset_type,
2752 p_parent_value IN VARCHAR2,
2753 p_flex_value IN VARCHAR2)
2754 IS
2755 l_count NUMBER;
2756 BEGIN
2757 IF (p_vset.validation_type = 'I') THEN
2758 SELECT COUNT(*)
2759 INTO l_count
2760 FROM fnd_flex_values
2761 WHERE flex_value_set_id = p_vset.flex_value_set_id
2765 ELSIF (p_vset.validation_type = 'D') THEN
2762 AND flex_value = p_flex_value
2763 AND ROWNUM = 1;
2764
2766 SELECT COUNT(*)
2767 INTO l_count
2768 FROM fnd_flex_values
2769 WHERE flex_value_set_id = p_vset.flex_value_set_id
2770 AND parent_flex_value_low = p_parent_value
2771 AND flex_value = p_flex_value
2772 AND ROWNUM = 1;
2773
2774 ELSE
2775 raise_error(ERROR_UNSUP_VALIDATION_TYPE,
2776 'Flex Code Error: Unsupported validation type: ' ||
2777 p_vset.validation_type);
2778 END IF;
2779
2780 IF (l_count > 0) THEN
2781 raise_error(ERROR_VALUE_ALREADY_EXISTS,
2782 'Value (' || p_flex_value || ') already exists.');
2783 END IF;
2784
2785 -- No exception handling here.
2786
2787 END check_value_existance;
2788
2789 -- ---------------------------------------------------------------------------
2790 PROCEDURE get_value(p_vset IN vset_type,
2791 p_parent_value IN VARCHAR2,
2792 p_flex_value IN VARCHAR2,
2793 px_value IN OUT nocopy value_type)
2794 IS
2795 BEGIN
2796 IF (p_vset.validation_type = 'I') THEN
2797 BEGIN
2798 SELECT *
2799 INTO px_value
2800 FROM fnd_flex_values_vl
2801 WHERE flex_value_set_id = p_vset.flex_value_set_id
2802 AND flex_value = p_flex_value;
2803 EXCEPTION
2804 WHEN OTHERS THEN
2805 raise_error(ERROR_UNABLE_TO_FIND_VALUE,
2806 'Unable to find value: ' || p_flex_value);
2807 END;
2808
2809 ELSIF (p_vset.validation_type = 'D') THEN
2810 BEGIN
2811 SELECT *
2812 INTO px_value
2813 FROM fnd_flex_values_vl
2814 WHERE flex_value_set_id = p_vset.flex_value_set_id
2815 AND parent_flex_value_low = p_parent_value
2816 AND flex_value = p_flex_value;
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 raise_error(ERROR_UNABLE_TO_FIND_VALUE,
2820 'Unable to find value: ' || p_flex_value);
2821 END;
2822
2823 ELSE
2824 raise_error(ERROR_UNSUP_VALIDATION_TYPE,
2825 'Flex Code Error: Unsupported validation type: ' ||
2826 p_vset.validation_type);
2827 END IF;
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 raise_error(ERROR_EXCEPTION_OTHERS,
2832 'Failure in get_value().');
2833 END get_value;
2834
2835 -- ---------------------------------------------------------------------------
2836 PROCEDURE check_flags_etc(p_vset IN vset_type,
2837 p_value IN value_type)
2838 IS
2839 BEGIN
2840 -- Validate enabled_flag
2841
2842 IF (Nvl(p_value.enabled_flag, 'X') NOT IN ('Y', 'N')) THEN
2843 raise_error(ERROR_INVALID_ENABLED_FLAG,
2844 'Enabled flag should be Y or N');
2845 END IF;
2846
2847 -- Validate start_date_active and end_date_active
2848
2849 IF ((p_value.start_date_active IS NOT NULL) AND
2850 (p_value.end_date_active IS NOT NULL) AND
2851 (p_value.start_date_active > p_value.end_date_active)) THEN
2852 raise_error(ERROR_INVALID_END_DATE,
2853 'End date should be later than the start date.');
2854 END IF;
2855
2856 -- Validate summary_flag
2857
2858 IF (Nvl(p_value.summary_flag, 'X') NOT IN ('Y', 'N')) THEN
2859 raise_error(ERROR_INVALID_SUMMARY_FLAG,
2860 'Summary flag should be Y or N');
2861 END IF;
2862
2863 -- Validate structured_hierarchy_level
2864
2865 IF (p_value.structured_hierarchy_level IS NOT NULL) THEN
2866
2867 IF (p_value.summary_flag <> 'Y') THEN
2868 raise_error(ERROR_INVALID_STR_HIER_LEVEL,
2869 'Structured Hierarchy Level can only be specified for Parent Values.');
2870 END IF;
2871
2872 DECLARE
2873 l_hierarchy_code fnd_flex_hierarchies.hierarchy_code%TYPE;
2874 BEGIN
2875 SELECT hierarchy_code
2876 INTO l_hierarchy_code
2877 FROM fnd_flex_hierarchies
2878 WHERE flex_value_set_id = p_vset.flex_value_set_id
2879 AND hierarchy_id = p_value.structured_hierarchy_level;
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 raise_error(ERROR_UNABLE_TO_FIND_STH_LEVEL,
2883 'Unable to find structured hierarchy level: ' ||
2884 p_value.structured_hierarchy_level);
2885 END;
2886 END IF;
2887
2888 -- No exception handling here.
2889
2890 END check_flags_etc;
2891
2892 -- ---------------------------------------------------------------------------
2893 PROCEDURE set_who(px_who IN OUT nocopy fnd_flex_loader_apis.who_type)
2894 IS
2895 BEGIN
2896 px_who.created_by := fnd_global.user_id();
2897 px_who.creation_date := Sysdate;
2898 px_who.last_updated_by := fnd_global.user_id();
2899 px_who.last_update_date := Sysdate;
2900 px_who.last_update_login := fnd_global.login_id();
2901 EXCEPTION
2902 WHEN OTHERS THEN
2903 raise_error(ERROR_UNABLE_TO_SET_WHO,
2904 'Unable to set WHO Information.');
2905 END set_who;
2906
2907 -- ---------------------------------------------------------------------------
2908 PROCEDURE call_load_row(p_vset IN vset_type,
2909 p_value IN value_type)
2910 IS
2911 l_who fnd_flex_loader_apis.who_type;
2912 BEGIN
2913
2914 -- Set WHO Info
2915
2916 set_who(l_who);
2917
2918 -- Ready to insert/update
2919
2920 BEGIN
2924 x_flex_value => p_value.flex_value,
2921 fnd_flex_values_pkg.load_row
2922 (x_flex_value_set_name => p_vset.flex_value_set_name,
2923 x_parent_flex_value_low => p_value.parent_flex_value_low,
2925 x_who => l_who,
2926 x_enabled_flag => p_value.enabled_flag,
2927 x_summary_flag => p_value.summary_flag,
2928 x_start_date_active => p_value.start_date_active,
2929 x_end_date_active => p_value.end_date_active,
2930 x_parent_flex_value_high => p_value.parent_flex_value_high,
2931 x_structured_hierarchy_level => p_value.structured_hierarchy_level,
2932 x_hierarchy_level => p_value.hierarchy_level,
2933 x_compiled_value_attributes => p_value.compiled_value_attributes,
2934 x_value_category => p_value.value_category,
2935 x_attribute1 => p_value.attribute1,
2936 x_attribute2 => p_value.attribute2,
2937 x_attribute3 => p_value.attribute3,
2938 x_attribute4 => p_value.attribute4,
2939 x_attribute5 => p_value.attribute5,
2940 x_attribute6 => p_value.attribute6,
2941 x_attribute7 => p_value.attribute7,
2942 x_attribute8 => p_value.attribute8,
2943 x_attribute9 => p_value.attribute9,
2944 x_attribute10 => p_value.attribute10,
2945 x_attribute11 => p_value.attribute11,
2946 x_attribute12 => p_value.attribute12,
2947 x_attribute13 => p_value.attribute13,
2948 x_attribute14 => p_value.attribute14,
2949 x_attribute15 => p_value.attribute15,
2950 x_attribute16 => p_value.attribute16,
2951 x_attribute17 => p_value.attribute17,
2952 x_attribute18 => p_value.attribute18,
2953 x_attribute19 => p_value.attribute19,
2954 x_attribute20 => p_value.attribute20,
2955 x_attribute21 => p_value.attribute21,
2956 x_attribute22 => p_value.attribute22,
2957 x_attribute23 => p_value.attribute23,
2958 x_attribute24 => p_value.attribute24,
2959 x_attribute25 => p_value.attribute25,
2960 x_attribute26 => p_value.attribute26,
2961 x_attribute27 => p_value.attribute27,
2962 x_attribute28 => p_value.attribute28,
2963 x_attribute29 => p_value.attribute29,
2964 x_attribute30 => p_value.attribute30,
2965 x_attribute31 => p_value.attribute31,
2966 x_attribute32 => p_value.attribute32,
2967 x_attribute33 => p_value.attribute33,
2968 x_attribute34 => p_value.attribute34,
2969 x_attribute35 => p_value.attribute35,
2970 x_attribute36 => p_value.attribute36,
2971 x_attribute37 => p_value.attribute37,
2972 x_attribute38 => p_value.attribute38,
2973 x_attribute39 => p_value.attribute39,
2974 x_attribute40 => p_value.attribute40,
2975 x_attribute41 => p_value.attribute41,
2976 x_attribute42 => p_value.attribute42,
2977 x_attribute43 => p_value.attribute43,
2978 x_attribute44 => p_value.attribute44,
2979 x_attribute45 => p_value.attribute45,
2980 x_attribute46 => p_value.attribute46,
2981 x_attribute47 => p_value.attribute47,
2982 x_attribute48 => p_value.attribute48,
2983 x_attribute49 => p_value.attribute49,
2984 x_attribute50 => p_value.attribute50,
2985 x_attribute_sort_order => p_value.attribute_sort_order,
2986 x_flex_value_meaning => p_value.flex_value_meaning,
2987 x_description => p_value.description);
2988
2989 EXCEPTION
2990 WHEN OTHERS THEN
2991 raise_error(ERROR_UNABLE_TO_LOAD_ROW,
2992 'Unable to load row.');
2993 END;
2994 EXCEPTION
2995 WHEN OTHERS THEN
2996 raise_error(ERROR_EXCEPTION_OTHERS,
2997 'Failure in call_load_row().');
2998 END call_load_row;
2999
3000 -- ---------------------------------------------------------------------------
3001 PROCEDURE create_independent_vset_value
3002 (p_flex_value_set_name IN VARCHAR2,
3003 p_flex_value IN VARCHAR2,
3004 p_description IN VARCHAR2 DEFAULT NULL,
3005 p_enabled_flag IN VARCHAR2 DEFAULT 'Y',
3006 p_start_date_active IN DATE DEFAULT NULL,
3007 p_end_date_active IN DATE DEFAULT NULL,
3008 p_summary_flag IN VARCHAR2 DEFAULT 'N',
3009 p_structured_hierarchy_level IN NUMBER DEFAULT NULL,
3010 p_hierarchy_level IN VARCHAR2 DEFAULT NULL,
3011 x_storage_value OUT NOCOPY VARCHAR2)
3012 IS
3013 l_vset vset_type;
3014 l_value value_type;
3015
3016 l_storage_value VARCHAR2(32000);
3017 l_display_value VARCHAR2(32000);
3018 l_success BOOLEAN;
3019
3020 BEGIN
3021 -- Get the value set.
3022 get_ind_vset(p_flex_value_set_name, NULL, l_vset);
3023
3024 -- Format validate the value
3025 fnd_flex_val_util.validate_value
3026 (p_value => p_flex_value,
3027 p_is_displayed => TRUE,
3031 p_precision => l_vset.number_precision,
3028 p_vset_name => l_vset.flex_value_set_name,
3029 p_vset_format => l_vset.format_type,
3030 p_max_length => l_vset.maximum_size,
3032 p_alpha_allowed => l_vset.alphanumeric_allowed_flag,
3033 p_uppercase_only => l_vset.uppercase_only_flag,
3034 p_zero_fill => l_vset.numeric_mode_enabled_flag,
3035 p_min_value => l_vset.minimum_value,
3036 p_max_value => l_vset.maximum_value,
3037 x_storage_value => l_storage_value,
3038 x_display_value => l_display_value,
3039 x_success => l_success);
3040
3041 IF (NOT l_success) THEN
3042 raise_error(ERROR_VALUE_VALIDATION_FAILED,
3043 'Value validation failed with the error message: ' ||
3044 fnd_message.get());
3045 END IF;
3046
3047 -- Populate the l_value, by default everything is NULL.
3048
3049 l_value.parent_flex_value_low := NULL;
3050 l_value.flex_value := l_storage_value;
3051 l_value.enabled_flag := p_enabled_flag;
3052 l_value.summary_flag := p_summary_flag;
3053 l_value.start_date_active := p_start_date_active;
3054 l_value.end_date_active := p_end_date_active;
3055 l_value.parent_flex_value_high := NULL;
3056 l_value.structured_hierarchy_level := p_structured_hierarchy_level;
3057 l_value.hierarchy_level := p_hierarchy_level;
3058 l_value.compiled_value_attributes := NULL;
3059 l_value.flex_value_meaning := l_storage_value;
3060 l_value.description := p_description;
3061
3062 -- Check the flags etc.
3063
3064 check_flags_etc(l_vset, l_value);
3065
3066 -- Check if this value already exists
3067
3068 check_value_existance(l_vset, NULL, l_storage_value);
3069
3070 -- Ready to insert
3071
3072 call_load_row(l_vset, l_value);
3073
3074 -- Value is created successfully, return the storage value
3075
3076 x_storage_value := l_storage_value;
3077
3078 EXCEPTION
3079 WHEN OTHERS THEN
3080 raise_error(ERROR_EXCEPTION_OTHERS,
3081 'Failure in create_independent_vset_value().');
3082 END create_independent_vset_value;
3083
3084 -- ---------------------------------------------------------------------------
3085 PROCEDURE create_dependent_vset_value
3086 (p_flex_value_set_name IN VARCHAR2,
3087 p_parent_flex_value IN VARCHAR2,
3088 p_flex_value IN VARCHAR2,
3089 p_description IN VARCHAR2 DEFAULT NULL,
3090 p_enabled_flag IN VARCHAR2 DEFAULT 'Y',
3091 p_start_date_active IN DATE DEFAULT NULL,
3092 p_end_date_active IN DATE DEFAULT NULL,
3093 p_hierarchy_level IN VARCHAR2 DEFAULT NULL,
3094 x_storage_value OUT NOCOPY VARCHAR2)
3095 IS
3096 l_vset vset_type;
3097 l_value value_type;
3098
3099 l_parent_vset vset_type;
3100 l_parent_value value_type;
3101
3102 l_storage_value VARCHAR2(32000);
3103 l_display_value VARCHAR2(32000);
3104 l_success BOOLEAN;
3105
3106 BEGIN
3107 -- Get the value set.
3108 get_dep_vset(p_flex_value_set_name, NULL, l_vset);
3109
3110 -- Get the parent value set
3111 BEGIN
3112 get_ind_vset(NULL, l_vset.parent_flex_value_set_id, l_parent_vset);
3113 EXCEPTION
3114 WHEN OTHERS THEN
3115 raise_error(ERROR_UNABLE_TO_GET_PARENT_VST,
3116 'Unable to get parent value set of flex value set: ' ||
3117 p_flex_value_set_name);
3118 END;
3119
3120 -- Get the parent value
3121 BEGIN
3122 get_value(l_parent_vset, NULL, p_parent_flex_value, l_parent_value);
3123 EXCEPTION
3124 WHEN OTHERS THEN
3125 raise_error(ERROR_UNABLE_TO_GET_PARENT_VAL,
3126 'Unable to get parent value: ' ||
3127 p_parent_flex_value);
3128 END;
3129
3130 -- Format validate the value
3131 fnd_flex_val_util.validate_value
3132 (p_value => p_flex_value,
3133 p_is_displayed => TRUE,
3134 p_vset_name => l_vset.flex_value_set_name,
3135 p_vset_format => l_vset.format_type,
3136 p_max_length => l_vset.maximum_size,
3137 p_precision => l_vset.number_precision,
3138 p_alpha_allowed => l_vset.alphanumeric_allowed_flag,
3139 p_uppercase_only => l_vset.uppercase_only_flag,
3140 p_zero_fill => l_vset.numeric_mode_enabled_flag,
3141 p_min_value => l_vset.minimum_value,
3142 p_max_value => l_vset.maximum_value,
3143 x_storage_value => l_storage_value,
3144 x_display_value => l_display_value,
3145 x_success => l_success);
3146
3147 IF (NOT l_success) THEN
3148 raise_error(ERROR_VALUE_VALIDATION_FAILED,
3149 'Value validation failed with the error message: ' ||
3150 fnd_message.get());
3151 END IF;
3152
3153 -- Populate the l_value, by default everything is NULL.
3154
3155 l_value.parent_flex_value_low := l_parent_value.flex_value;
3156 l_value.flex_value := l_storage_value;
3157 l_value.enabled_flag := p_enabled_flag;
3158 l_value.summary_flag := 'N';
3162 l_value.structured_hierarchy_level := NULL;
3159 l_value.start_date_active := p_start_date_active;
3160 l_value.end_date_active := p_end_date_active;
3161 l_value.parent_flex_value_high := NULL;
3163 l_value.hierarchy_level := p_hierarchy_level;
3164 l_value.compiled_value_attributes := NULL;
3165 l_value.flex_value_meaning := l_storage_value;
3166 l_value.description := p_description;
3167
3168 -- Check flags etc.
3169
3170 check_flags_etc(l_vset, l_value);
3171
3172 -- Check if this value already exists
3173
3174 check_value_existance(l_vset, l_parent_value.flex_value, l_storage_value);
3175
3176 -- Ready to insert
3177
3178 call_load_row(l_vset, l_value);
3179
3180 -- Value is created successfully, return the storage value
3181
3182 x_storage_value := l_storage_value;
3183
3184 EXCEPTION
3185 WHEN OTHERS THEN
3186 raise_error(ERROR_EXCEPTION_OTHERS,
3187 'Failure in create_dependent_vset_value().');
3188 END create_dependent_vset_value;
3189
3190 -- ---------------------------------------------------------------------------
3191 PROCEDURE update_field(px_field IN OUT nocopy VARCHAR2,
3192 p_value IN VARCHAR2)
3193 IS
3194 BEGIN
3195 IF (p_value IS NOT NULL) THEN
3196 IF (p_value = g_null_varchar2) THEN
3197 px_field := NULL;
3198 ELSE
3199 px_field := p_value;
3200 END IF;
3201 END IF;
3202 END update_field;
3203
3204 -- ---------------------------------------------------------------------------
3205 PROCEDURE update_field(px_field IN OUT nocopy NUMBER,
3206 p_value IN NUMBER)
3207 IS
3208 BEGIN
3209 IF (p_value IS NOT NULL) THEN
3210 IF (p_value = g_null_number) THEN
3211 px_field := NULL;
3212 ELSE
3213 px_field := p_value;
3214 END IF;
3215 END IF;
3216 END update_field;
3217
3218 -- ---------------------------------------------------------------------------
3219 PROCEDURE update_field(px_field IN OUT nocopy DATE,
3220 p_value IN DATE)
3221 IS
3222 BEGIN
3223 IF (p_value IS NOT NULL) THEN
3224 IF (p_value = g_null_date) THEN
3225 px_field := NULL;
3226 ELSE
3227 px_field := p_value;
3228 END IF;
3229 END IF;
3230 END update_field;
3231
3232 -- ---------------------------------------------------------------------------
3233 PROCEDURE update_independent_vset_value
3234 (p_flex_value_set_name IN VARCHAR2,
3235 p_flex_value IN VARCHAR2,
3236 p_description IN VARCHAR2 DEFAULT NULL,
3237 p_enabled_flag IN VARCHAR2 DEFAULT NULL,
3238 p_start_date_active IN DATE DEFAULT NULL,
3239 p_end_date_active IN DATE DEFAULT NULL,
3240 p_summary_flag IN VARCHAR2 DEFAULT NULL,
3241 p_structured_hierarchy_level IN NUMBER DEFAULT NULL,
3242 p_hierarchy_level IN VARCHAR2 DEFAULT NULL,
3243 x_storage_value OUT NOCOPY VARCHAR2)
3244 IS
3245 l_vset vset_type;
3246 l_value value_type;
3247
3248 l_storage_value VARCHAR2(32000);
3249 l_display_value VARCHAR2(32000);
3250 l_success BOOLEAN;
3251
3252 l_tmp VARCHAR2(32000);
3253
3254 BEGIN
3255 -- Get the value set.
3256
3257 get_ind_vset(p_flex_value_set_name, NULL, l_vset);
3258
3259 -- Get the value
3260
3261 get_value(l_vset, NULL, p_flex_value, l_value);
3262
3263 -- Format validate the value
3264
3265 fnd_flex_val_util.validate_value
3266 (p_value => l_value.flex_value,
3267 p_is_displayed => FALSE,
3268 p_vset_name => l_vset.flex_value_set_name,
3269 p_vset_format => l_vset.format_type,
3270 p_max_length => l_vset.maximum_size,
3271 p_precision => l_vset.number_precision,
3272 p_alpha_allowed => l_vset.alphanumeric_allowed_flag,
3273 p_uppercase_only => l_vset.uppercase_only_flag,
3274 p_zero_fill => l_vset.numeric_mode_enabled_flag,
3275 p_min_value => l_vset.minimum_value,
3276 p_max_value => l_vset.maximum_value,
3277 x_storage_value => l_storage_value,
3278 x_display_value => l_display_value,
3279 x_success => l_success);
3280
3281 IF (NOT l_success) THEN
3282 raise_error(ERROR_VALUE_VALIDATION_FAILED,
3283 'Value validation failed with the error message: ' ||
3284 fnd_message.get());
3285 END IF;
3286
3287 -- Update fields
3288
3289 update_field(l_value.description, p_description);
3290 update_field(l_value.enabled_flag, p_enabled_flag);
3291 update_field(l_value.start_date_active, p_start_date_active);
3292 update_field(l_value.end_date_active, p_end_date_active);
3293 update_field(l_value.summary_flag, p_summary_flag);
3294 update_field(l_value.structured_hierarchy_level, p_structured_hierarchy_level);
3295 update_field(l_value.hierarchy_level, p_hierarchy_level);
3296
3297 IF (l_value.summary_flag = 'N') THEN
3298 l_value.structured_hierarchy_level := NULL;
3299
3300 DELETE FROM fnd_flex_value_norm_hierarchy
3304
3301 WHERE flex_value_set_id = l_vset.flex_value_set_id
3302 AND parent_flex_value = l_value.flex_value;
3303 END IF;
3305 -- Check the flags etc.
3306
3307 check_flags_etc(l_vset, l_value);
3308
3309 -- Ready to update
3310
3311 call_load_row(l_vset, l_value);
3312
3313 -- Value is updated successfully, return the storage value
3314
3315 x_storage_value := l_storage_value;
3316
3317 EXCEPTION
3318 WHEN OTHERS THEN
3319 raise_error(ERROR_EXCEPTION_OTHERS,
3320 'Failure in update_independent_vset_value().');
3321 END update_independent_vset_value;
3322
3323 -- ---------------------------------------------------------------------------
3324 PROCEDURE update_dependent_vset_value
3325 (p_flex_value_set_name IN VARCHAR2,
3326 p_parent_flex_value IN VARCHAR2,
3327 p_flex_value IN VARCHAR2,
3328 p_description IN VARCHAR2 DEFAULT NULL,
3329 p_enabled_flag IN VARCHAR2 DEFAULT NULL,
3330 p_start_date_active IN DATE DEFAULT NULL,
3331 p_end_date_active IN DATE DEFAULT NULL,
3332 p_hierarchy_level IN VARCHAR2 DEFAULT NULL,
3333 x_storage_value OUT NOCOPY VARCHAR2)
3334 IS
3335 l_vset vset_type;
3336 l_value value_type;
3337
3338 l_parent_vset vset_type;
3339 l_parent_value value_type;
3340
3341 l_storage_value VARCHAR2(32000);
3342 l_display_value VARCHAR2(32000);
3343 l_success BOOLEAN;
3344
3345 BEGIN
3346 -- Get the value set.
3347 get_dep_vset(p_flex_value_set_name, NULL, l_vset);
3348
3349 -- Get the parent value set
3350 BEGIN
3351 get_ind_vset(NULL, l_vset.parent_flex_value_set_id, l_parent_vset);
3352 EXCEPTION
3353 WHEN OTHERS THEN
3354 raise_error(ERROR_UNABLE_TO_GET_PARENT_VST,
3355 'Unable to get parent value set of flex value set: ' ||
3356 p_flex_value_set_name);
3357 END;
3358
3359 -- Get the parent value
3360 BEGIN
3361 get_value(l_parent_vset, NULL, p_parent_flex_value, l_parent_value);
3362 EXCEPTION
3363 WHEN OTHERS THEN
3364 raise_error(ERROR_UNABLE_TO_GET_PARENT_VAL,
3365 'Unable to get parent value: ' ||
3366 p_parent_flex_value);
3367 END;
3368
3369 -- Get the value
3370 get_value(l_vset, l_parent_value.flex_value, p_flex_value, l_value);
3371
3372 -- Format validate the value
3373
3374 fnd_flex_val_util.validate_value
3375 (p_value => l_value.flex_value,
3376 p_is_displayed => FALSE,
3377 p_vset_name => l_vset.flex_value_set_name,
3378 p_vset_format => l_vset.format_type,
3379 p_max_length => l_vset.maximum_size,
3380 p_precision => l_vset.number_precision,
3381 p_alpha_allowed => l_vset.alphanumeric_allowed_flag,
3382 p_uppercase_only => l_vset.uppercase_only_flag,
3383 p_zero_fill => l_vset.numeric_mode_enabled_flag,
3384 p_min_value => l_vset.minimum_value,
3385 p_max_value => l_vset.maximum_value,
3386 x_storage_value => l_storage_value,
3387 x_display_value => l_display_value,
3388 x_success => l_success);
3389
3390 IF (NOT l_success) THEN
3391 raise_error(ERROR_VALUE_VALIDATION_FAILED,
3392 'Value validation failed with the error message: ' ||
3393 fnd_message.get());
3394 END IF;
3395
3396 -- Update fields
3397
3398 update_field(l_value.description, p_description);
3399 update_field(l_value.enabled_flag, p_enabled_flag);
3400 update_field(l_value.start_date_active, p_start_date_active);
3401 update_field(l_value.end_date_active, p_end_date_active);
3402 update_field(l_value.hierarchy_level, p_hierarchy_level);
3403
3404 -- Check flags etc.
3405
3406 check_flags_etc(l_vset, l_value);
3407
3408 -- Ready to update
3409
3410 call_load_row(l_vset, l_value);
3411
3412 -- Value is updated successfully, return the storage value
3413
3414 x_storage_value := l_storage_value;
3415
3416 EXCEPTION
3417 WHEN OTHERS THEN
3418 raise_error(ERROR_EXCEPTION_OTHERS,
3419 'Failure in update_dependent_vset_value().');
3420 END update_dependent_vset_value;
3421
3422 -- ---------------------------------------------------------------------------
3423 PROCEDURE create_value_hierarchy
3424 (p_flex_value_set_name IN VARCHAR2,
3425 p_parent_flex_value IN VARCHAR2,
3426 p_range_attribute IN VARCHAR2,
3427 p_child_flex_value_low IN VARCHAR2,
3428 p_child_flex_value_high IN VARCHAR2)
3429 IS
3430 l_vset vset_type;
3431 l_value value_type;
3432
3433 l_storage_value_low VARCHAR2(32000);
3434 l_storage_value_high VARCHAR2(32000);
3435
3436 l_display_value VARCHAR2(32000);
3437 l_success BOOLEAN;
3438
3439 l_who fnd_flex_loader_apis.who_type;
3440 BEGIN
3441 -- Get the value set.
3442 get_ind_vset(p_flex_value_set_name, NULL, l_vset);
3443
3444 -- Get the value
3445 get_value(l_vset, NULL, p_parent_flex_value, l_value);
3446
3450 'Summary Flag of the value is not Y.');
3447 -- Make sure it is a Parent Value
3448 IF (l_value.summary_flag <> 'Y') THEN
3449 raise_error(ERROR_NOT_A_PARENT_VALUE,
3451 END IF;
3452
3453 -- Validate the range attribute
3454 IF (Nvl(p_range_attribute, 'X') NOT IN ('C', 'P')) THEN
3455 raise_error(ERROR_INVALID_RANGE_ATTRIBUTE,
3456 'Range Attribute should be C or P');
3457 END IF;
3458
3459 -- Validate the child value low
3460
3461 fnd_flex_val_util.validate_value
3462 (p_value => p_child_flex_value_low,
3463 p_is_displayed => TRUE,
3464 p_vset_name => l_vset.flex_value_set_name,
3465 p_vset_format => l_vset.format_type,
3466 p_max_length => l_vset.maximum_size,
3467 p_precision => l_vset.number_precision,
3468 p_alpha_allowed => l_vset.alphanumeric_allowed_flag,
3469 p_uppercase_only => l_vset.uppercase_only_flag,
3470 p_zero_fill => l_vset.numeric_mode_enabled_flag,
3471 p_min_value => l_vset.minimum_value,
3472 p_max_value => l_vset.maximum_value,
3473 x_storage_value => l_storage_value_low,
3474 x_display_value => l_display_value,
3475 x_success => l_success);
3476
3477 IF (NOT l_success) THEN
3478 raise_error(ERROR_VALUE_VALIDATION_FAILED,
3479 'Low Value validation failed with the error message: ' ||
3480 fnd_message.get());
3481 END IF;
3482
3483
3484 -- Validate the child value high
3485
3486 fnd_flex_val_util.validate_value
3487 (p_value => p_child_flex_value_high,
3488 p_is_displayed => TRUE,
3489 p_vset_name => l_vset.flex_value_set_name,
3490 p_vset_format => l_vset.format_type,
3491 p_max_length => l_vset.maximum_size,
3492 p_precision => l_vset.number_precision,
3493 p_alpha_allowed => l_vset.alphanumeric_allowed_flag,
3494 p_uppercase_only => l_vset.uppercase_only_flag,
3495 p_zero_fill => l_vset.numeric_mode_enabled_flag,
3496 p_min_value => l_vset.minimum_value,
3497 p_max_value => l_vset.maximum_value,
3498 x_storage_value => l_storage_value_high,
3499 x_display_value => l_display_value,
3500 x_success => l_success);
3501
3502 IF (NOT l_success) THEN
3503 raise_error(ERROR_VALUE_VALIDATION_FAILED,
3504 'High Value validation failed with the error message: ' ||
3505 fnd_message.get());
3506 END IF;
3507
3508 -- Make sure the order in Low and High Values
3509 IF (l_storage_value_low > l_storage_value_high) THEN
3510 raise_error(ERROR_INVALID_HIGH_VALUE,
3511 'High value cannot be lower than Low value.');
3512 END IF;
3513
3514 -- Check if this hierarchy already exists
3515
3516 DECLARE
3517 l_count NUMBER;
3518 BEGIN
3519 SELECT COUNT(*)
3520 INTO l_count
3521 FROM fnd_flex_value_norm_hierarchy
3522 WHERE flex_value_set_id = l_vset.flex_value_set_id
3523 AND parent_flex_value = l_value.flex_value
3524 AND range_attribute = p_range_attribute
3525 AND child_flex_value_low = l_storage_value_low
3526 AND child_flex_value_high = l_storage_value_high
3527 AND ROWNUM = 1;
3528
3529 IF (l_count > 0) THEN
3530 raise_error(ERROR_HIERARCHY_ALREADY_EXISTS,
3531 'Hierarchy (' || l_value.flex_value ||
3532 ' (' || p_range_attribute ||
3533 ') : ' || l_storage_value_low ||
3534 ' - ' || l_storage_value_high || ') already exists.');
3535 END IF;
3536 END;
3537
3538 -- Set WHO Info
3539
3540 set_who(l_who);
3541
3542 -- Ready to insert
3543
3544 BEGIN
3545 INSERT INTO fnd_flex_value_norm_hierarchy
3546 (
3547 flex_value_set_id,
3548 parent_flex_value,
3549 range_attribute,
3550 child_flex_value_low,
3551 child_flex_value_high,
3552
3553 created_by,
3554 creation_date,
3555 last_updated_by,
3556 last_update_date,
3557 last_update_login,
3558
3559 start_date_active,
3560 end_date_active
3561 )
3562 VALUES
3563 (
3564 l_vset.flex_value_set_id,
3565 l_value.flex_value,
3566 p_range_attribute,
3567 l_storage_value_low,
3568 l_storage_value_high,
3569
3570 l_who.created_by,
3571 l_who.creation_date,
3572 l_who.last_updated_by,
3573 l_who.last_update_date,
3574 l_who.last_update_login,
3575
3576 NULL,
3577 NULL
3578 );
3579
3580 EXCEPTION
3581 WHEN OTHERS THEN
3582 raise_error(ERROR_UNABLE_TO_INSERT_ROW,
3583 'Unable to insert row.');
3584 END;
3585
3586 -- Hierarchy is created successfully
3587
3588 END create_value_hierarchy;
3589
3590 -- ---------------------------------------------------------------------------
3591 PROCEDURE submit_vset_hierarchy_compiler
3592 (p_flex_value_set_name IN VARCHAR2,
3593 x_request_id OUT NOCOPY NUMBER)
3594 IS
3595 l_vset vset_type;
3596 l_request_id NUMBER;
3597 BEGIN
3601 BEGIN
3598 -- Get the value set.
3599 get_ind_vset(p_flex_value_set_name, NULL, l_vset);
3600
3602 l_request_id := fnd_request.submit_request
3603 (application => 'FND',
3604 program => 'FDFCHY',
3605 description => 'VAI.submit_vset_hierarchy_compiler',
3606 start_time => NULL,
3607 sub_request => FALSE,
3608 argument1 => l_vset.flex_value_set_id);
3609
3610 EXCEPTION
3611 WHEN OTHERS THEN
3612 raise_error(ERROR_UNABLE_TO_SUBMIT_FDFCHY,
3613 'Unable to submit FDFCHY request for flex value set: ' ||
3614 p_flex_value_set_name || '. Error: ' ||
3615 fnd_message.get());
3616 END;
3617
3618 IF (l_request_id = 0) THEN
3619 raise_error(ERROR_UNABLE_TO_SUBMIT_FDFCHY,
3620 'Unable to submit FDFCHY request for flex value set: ' ||
3621 p_flex_value_set_name || '. Error: ' ||
3622 fnd_message.get());
3623 END IF;
3624
3625 x_request_id := l_request_id;
3626
3627 END submit_vset_hierarchy_compiler;
3628
3629
3630 -- Purpose
3631 -- Validate a valuset for a given segment, based on
3632 -- critera commented below, and give a error message if the
3633 -- valueset is not valid for that segment. This gives
3634 -- the user a chance to see why he cannot attach a vset to a
3635 -- certain segment. We use the same procedure to validate all
3636 -- FF which include KFF, DFF, and SRS.
3637 --
3638 -- Arguments
3639 -- All the arguments for the procedure is_valueset_allowed()
3640 -- are not needed. Which arguments are needed will depend
3641 -- on which FF is being valiated. If an argument is
3642 -- not needed, then just set it to null in the calling function.
3643 --
3644 -- flex_field For all FF
3645 -- value_set_id For all FF
3646 -- allow_id_valuesets For all FF
3647 -- segment_name For all FF
3648 -- id_flex_num For KFF only
3649 -- segment_num For KFF only
3650 -- desc_flex_context_code For DFF and SRS
3651 -- column_seq_num For DFF and SRS
3652 -- application_column_type For KFF and DFF
3653 -- application_column_size For KFF and DFF
3654
3655 PROCEDURE is_valueset_allowed(p_flex_field in VARCHAR2,
3656 p_value_set_id in NUMBER,
3657 p_allow_id_valuesets in VARCHAR2,
3658 p_segment_name in VARCHAR2,
3659 p_id_flex_num in NUMBER,
3660 p_segment_num in NUMBER,
3661 p_desc_flex_context_code in VARCHAR2,
3662 p_column_seq_num in NUMBER,
3663 p_application_column_type in VARCHAR2,
3664 p_application_column_size in NUMBER)
3665 IS
3666
3667 l_validation_type VARCHAR2(1);
3668 l_format_type VARCHAR2(1);
3669 l_maximum_size NUMBER(3);
3670 l_id_column_name VARCHAR2(240);
3671 l_id_column_type VARCHAR2(1);
3672 l_id_column_size NUMBER(3);
3673 l_appl_col_size NUMBER(15);
3674 l_segment_num NUMBER(3);
3675 l_column_seq_num NUMBER(3);
3676 l_column_type VARCHAR2(1);
3677 l_number_precision NUMBER(2);
3678 l_application_column_type VARCHAR2(1);
3679 l_application_column_size NUMBER(15);
3680
3681 l_flex_value_set_name VARCHAR2(60);
3682 l_format_type_name VARCHAR2(80);
3683 l_appl_column_type_name VARCHAR2(80);
3684 l_parent_value_set_name VARCHAR2(60);
3685 l_parent_value_set_id NUMBER(10);
3686
3687
3688 BEGIN
3689 BEGIN
3690
3691 -- If no vset id is passed in, assume no valueset to valdate.
3692 IF (p_value_set_id IS NULL)
3693 THEN
3694 RETURN;
3695 END IF;
3696
3697 -- Initialize all local variables needed for validation.
3698
3699 l_application_column_type := p_application_column_type;
3700 l_application_column_size := p_application_column_size;
3701
3702 SELECT
3703 v.validation_type, v.format_type, v.maximum_size,
3704 v.number_precision, v.flex_value_set_name,
3705 t.id_column_name, t.id_column_type, t.id_column_size
3706 INTO
3707 l_validation_type, l_format_type, l_maximum_size,
3708 l_number_precision, l_flex_value_set_name,
3709 l_id_column_name, l_id_column_type, l_id_column_size
3710 FROM
3711 fnd_flex_value_sets v,
3712 fnd_flex_validation_tables t
3713 WHERE
3714 v.flex_value_set_id = p_value_set_id and
3715 v.flex_value_set_id = t.flex_value_set_id(+);
3716
3717
3718 IF(p_flex_field = 'SRS') THEN
3719
3720 SELECT
3721 c.column_type, c.width
3722 INTO
3723 l_column_type, l_appl_col_size
3724 FROM
3725 fnd_flex_value_sets v,
3726 fnd_flex_validation_tables t,
3727 fnd_columns c, fnd_tables tb
3728 WHERE
3729 v.flex_value_set_id = p_value_set_id and
3730 v.flex_value_set_id = t.flex_value_set_id(+) and
3731 tb.application_id = 0 and
3732 tb.table_name = 'FND_SRS_MASTER' and
3736 -- The SRS form does not need to save the Parameter values entered
3733 tb.application_id = c.application_id and
3734 tb.table_id = c.table_id and c.column_name = 'ATTRIBUTE1';
3735
3737 -- in the FF. Because of this, there is no base table to validate
3738 -- against. Instead of validating against a base table we use
3739 -- column attributes from fnd_column and fnd_tables.
3740 -- We assign the values returned to what normally we would
3741 -- get from the base table. Now normal validation can continue
3742 -- since we have values for p_application_column_type and
3743 -- p_application_column_size. They are passed in as null from
3744 -- the SRS form.
3745
3746 l_application_column_type := l_column_type;
3747 l_application_column_size := l_appl_col_size;
3748
3749 END IF;
3750
3751
3752 -- Please note the order in which the rules are checked is based
3753 -- which ones are more serious. A value set may have more than
3754 -- one rule that it violates. The one we display first is the most
3755 -- serious. For example, if a vset format type is obsolete, I don't
3756 -- want to first get an error message saying that my format type does
3757 -- not match the database column data type. The first thing I want to
3758 -- know is the the vset is obsolete.
3759
3760
3761
3762
3763
3764 /*
3765 For KFF and DFF and SRS
3766 This rule is to make sure Date and DateTime value sets
3767 format types are not used since they are now obsolete and
3768 are replaced by Standard Date(X) and Standard DateTime(Y) format types.
3769 */
3770 IF(l_format_type='D' or l_format_type='T')
3771 THEN
3772 fnd_message.set_name('FND','FLEX-VSET TYPE OBSOLETE');
3773 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3774 fnd_message.set_token('SEG_NAME',p_segment_name);
3775 app_exception.raise_exception;
3776 END IF;
3777
3778
3779 IF(p_flex_field = 'KFF') THEN
3780 /*
3781 For KFF Only
3782 Table Validate value sets with hidden ID columns may
3783 not be used with KFF's defiend with 'ID VS not allowed'.
3784 Check to see if the FF is allowed to have an ID VS.
3785 If so, then the VS can be used. If the FF
3786 is not allowed to have an ID VS, then we need to
3787 check if the value set is defined as an ID VS.
3788 if the id_column_name is null, then it is not an
3789 ID VS and it can be displayed.
3790 */
3791 IF(NOT(p_allow_id_valuesets = 'Y' OR
3792 (p_allow_id_valuesets = 'N' AND l_id_column_name is NULL)))
3793 THEN
3794 fnd_message.set_name('FND','FLEX-VSET IDVSET RESTRICTED');
3795 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3796 fnd_message.set_token('SEG_NAME',p_segment_name);
3797 app_exception.raise_exception;
3798 END IF;
3799
3800
3801 /*
3802 For KFF Only
3803 Table Validate value sets with hidden ID columns may
3804 not be used with KFF's defiend with 'ID VS not allowed'.
3805 Check to see if the FF is allowed to have an ID VS.
3806 If so, then the VS can be displayed. If the FF
3807 is not allowed to have an ID VS, then we need to
3808 check if the Value Set is defined as an ID VS.
3809 If the VS has a Validation Type of Translatable
3810 Independent/Dependent, then it cannot be used
3811 because those validation types are internally
3812 ID value sets.
3813 */
3814 IF(NOT(p_allow_id_valuesets = 'Y' OR
3815 (p_allow_id_valuesets = 'N' AND
3816 l_validation_type <> 'X' AND l_validation_type <> 'Y')))
3817 THEN
3818 fnd_message.set_name('FND','FLEX-VSET IDVSET TR RESTRICTED');
3819 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3820 fnd_message.set_token('SEG_NAME',p_segment_name);
3821 app_exception.raise_exception;
3822 END IF;
3823
3824
3825 /*
3826 'Date', 'DateTime', 'Time' and 'Number with Precision'
3827 vsets are a type of ID vsets.
3828 Some KFF's do not allow ID vsets. If ID vsets are not
3829 allowed then we must make sure these vsets are not used.
3830 */
3831 IF(NOT(p_allow_id_valuesets = 'Y' OR
3832 ( p_allow_id_valuesets = 'N' AND
3833 (l_format_type <> 'X' AND
3834 l_format_type <> 'Y' AND
3835 l_format_type <> 'I' AND
3836 l_format_type <> 'N') OR
3837 ((l_format_type = 'N' AND
3838 l_number_precision IS NOT NULL AND
3839 l_number_precision = 0)))))
3840 THEN
3841 fnd_message.set_name('FND','FLEX-VSET IDVSET DN RESTRICTED');
3842 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3843 fnd_message.set_token('SEG_NAME',p_segment_name);
3844 app_exception.raise_exception;
3845 END IF;
3846 END IF; /* KFF Check */
3847
3848
3849 /*
3850 For KFF and DFF and SRS
3851 This rule is to make sure that the base table underlying column
3852 can store values from a VS based on its format type or id_column_type.
3853 If the underlying column type is a C=Char or V=Varchar2 then that
3854 column can store any VS value format. If the VS Validation Type
3858 compatible with the underlying column. First we check the VS
3855 is U=Special then it does not store any values in the base table
3856 so that can be used. If the underlying column is anything other
3857 than C=Char or V=Varchar2 then we need to make sure the VS will be
3859 Table ID Type. If it is not null then we can use that Type to compare
3860 to the underlying column and they must match. If the VS Table ID
3861 Type is null, then we use the VS Format Type to compare to the
3862 underlying column. If the underlying column is of type
3863 Date(D), then only VS Format Types T=DateTime, I=Time, X=StandardDate,
3864 Y=StandardDateTime, Z=StandardTime can be used.
3865 */
3866 IF(NOT((l_application_column_type = 'C' OR l_application_column_type = 'V')
3867 OR l_validation_type = 'U' OR
3868 ((l_application_column_type = l_id_column_type and
3869 l_id_column_type is not null) or
3870 (l_format_type in ('T','I','X','Y','Z') and
3871 l_application_column_type = 'D' and
3872 l_id_column_type is null) or
3873 (l_format_type not in ('T','I','X','Y','Z') and
3874 l_application_column_type = l_format_type and
3875 l_id_column_type is null))))
3876 THEN
3877 -- Get description of codes for the error message --
3878
3879 select meaning
3880 into l_appl_column_type_name
3881 from fnd_lookups
3882 where lookup_code=l_application_column_type and
3883 lookup_type='COLUMN_TYPE';
3884
3885 IF( l_id_column_type is NULL )
3886 THEN
3887 select meaning
3888 into l_format_type_name
3889 from fnd_lookups
3890 where lookup_code=l_format_type and lookup_type='FIELD_TYPE';
3891 fnd_message.set_name('FND','FLEX-VSET FORMAT TYPE CONFLICT');
3892 ELSE
3893 select meaning
3894 into l_format_type_name
3895 from fnd_lookups
3896 where lookup_code=l_id_column_type and lookup_type='COLUMN_TYPE';
3897 fnd_message.set_name('FND','FLEX-VSET ID COL TYPE CONFLICT');
3898 END IF;
3899
3900 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3901 fnd_message.set_token('SEG_NAME',p_segment_name);
3902 fnd_message.set_token('FORMAT_TYPE',l_format_type_name);
3903 fnd_message.set_token('DATA_TYPE',l_appl_column_type_name);
3904 app_exception.raise_exception;
3905
3906 END IF;
3907
3908
3909 /*
3910 For KFF and DFF and SRS
3911 This rule is to make sure that the max value set size
3912 or the ID column size is not bigger than the underlying column.
3913 If the underlying table column is a date type then no need to
3914 check column size since a date column can store any date type.
3915 */
3916 IF(NOT( (l_application_column_type = 'D')
3917 OR (l_application_column_size >= l_maximum_size
3918 AND l_id_column_size IS NULL)
3919 OR (l_application_column_size >= l_id_column_size
3920 AND l_id_column_size IS NOT NULL)))
3921 THEN
3922
3923 IF( l_id_column_type is NULL )
3924 THEN
3925 fnd_message.set_name('FND','FLEX-VSET MAX SIZE');
3926 fnd_message.set_token('VSET_MAXSIZE',l_maximum_size);
3927 ELSE
3928 fnd_message.set_name('FND','FLEX-VSET ID COL SIZE');
3929 fnd_message.set_token('ID_COL_SIZE',l_id_column_size);
3930 END IF;
3931
3932 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3933 fnd_message.set_token('SEG_NAME',p_segment_name);
3934 fnd_message.set_token('COL_SIZE',l_application_column_size);
3935 app_exception.raise_exception;
3936 END IF;
3937
3938
3939 IF(p_flex_field = 'KFF') THEN
3940 /*
3941 For KFF Only
3942 This rule is to make sure that a Dependent VS has it's
3943 Indpendent VS to reference in the previous segment
3944 If it is not a Dependent VS then no need to check child
3945 parent relationship. If it is a Dependent value set then
3946 make sure it has it's parent.
3947 */
3948 IF(l_validation_type ='D' or l_validation_type ='Y') THEN
3949 BEGIN
3950 SELECT
3951 s.segment_num, v.parent_flex_value_set_id
3952 INTO
3953 l_segment_num, l_parent_value_set_id
3954 FROM
3955 fnd_flex_value_sets v, fnd_id_flex_segments s
3956 WHERE
3957 v.flex_value_set_id = p_value_set_id
3958 AND s.id_flex_num = p_id_flex_num
3959 AND v.parent_flex_value_set_id = s.flex_value_set_id;
3960
3961 IF(NOT(l_segment_num < p_segment_num)) THEN
3962
3963 select flex_value_set_name
3964 into l_parent_value_set_name
3965 from fnd_flex_value_sets
3966 where
3967 flex_value_set_id=l_parent_value_set_id;
3968
3969 fnd_message.set_name('FND','FLEX-VSET IND DEP VSET ORDER');
3970 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3971 fnd_message.set_token('SEG_NAME',p_segment_name);
3972 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
3973 app_exception.raise_exception;
3974 END IF;
3978
3975
3976 EXCEPTION
3977 WHEN NO_DATA_FOUND THEN
3979 select flex_value_set_name
3980 into l_parent_value_set_name
3981 from fnd_flex_value_sets
3982 where
3983 flex_value_set_id in (select parent_flex_value_set_id
3984 from fnd_flex_value_sets
3985 where flex_value_set_id=p_value_set_id);
3986
3987 fnd_message.set_name('FND','FLEX-VSET PARENT VSET MISSING');
3988 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
3989 fnd_message.set_token('SEG_NAME',p_segment_name);
3990 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
3991 app_exception.raise_exception;
3992 WHEN OTHERS THEN RAISE;
3993 END;
3994 END IF;
3995 END IF; /* KFF Check */
3996
3997
3998 IF(p_flex_field = 'DFF' OR p_flex_field = 'SRS') THEN
3999 /*
4000 For DFF and SRS
4001 This rule is to make sure that a Dependent VS has it's
4002 Indpendent VS to reference in the previous segment
4003 If it is not a Dependent VS then no need to check child
4004 parent relationship. If it is a Dependent value set then
4005 make sure it has it's parent.
4006 */
4007 IF(l_validation_type ='D' or l_validation_type ='Y') THEN
4008 BEGIN
4009 SELECT
4010 u.column_seq_num, v.parent_flex_value_set_id
4011 INTO
4012 l_column_seq_num, l_parent_value_set_id
4013 FROM
4014 fnd_flex_value_sets v, fnd_descr_flex_column_usages u
4015 WHERE
4016 v.flex_value_set_id = p_value_set_id and
4017 -- Bug#4410208, In SRS agruments form there is no context code
4018 (p_desc_flex_context_code is null or
4019 u.descriptive_flex_context_code = p_desc_flex_context_code) and
4020 v.parent_flex_value_set_id = u.flex_value_set_id;
4021
4022 IF(NOT(l_column_seq_num < p_column_seq_num)) THEN
4023
4024 select flex_value_set_name
4025 into l_parent_value_set_name
4026 from fnd_flex_value_sets
4027 where
4028 flex_value_set_id=l_parent_value_set_id;
4029
4030 fnd_message.set_name('FND','FLEX-VSET IND DEP VSET ORDER');
4031 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4032 fnd_message.set_token('SEG_NAME',p_segment_name);
4033 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
4034 app_exception.raise_exception;
4035 END IF;
4036 EXCEPTION
4037 WHEN NO_DATA_FOUND THEN
4038
4039 select flex_value_set_name
4040 into l_parent_value_set_name
4041 from fnd_flex_value_sets
4042 where
4043 flex_value_set_id in (select parent_flex_value_set_id
4044 from fnd_flex_value_sets
4045 where flex_value_set_id=p_value_set_id);
4046
4047 fnd_message.set_name('FND','FLEX-VSET PARENT VSET MISSING');
4048 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4049 fnd_message.set_token('SEG_NAME',p_segment_name);
4050 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
4051 app_exception.raise_exception;
4052 WHEN OTHERS THEN RAISE;
4053 END;
4054 END IF;
4055 END IF; /* DFF Check */
4056
4057 EXCEPTION
4058 WHEN NO_DATA_FOUND THEN
4059 RAISE;
4060 WHEN OTHERS THEN RAISE;
4061 END;
4062
4063 END is_valueset_allowed;
4064
4065 PROCEDURE check_base_table_column(
4066 p_application_column_type IN fnd_columns.column_type%TYPE,
4067 p_application_column_size IN fnd_columns.width%TYPE,
4068 p_id_column_type IN fnd_flex_validation_tables.id_column_type%TYPE,
4069 p_id_column_size IN fnd_flex_validation_tables.id_column_size%TYPE,
4070 p_validation_type IN fnd_flex_value_sets.validation_type%TYPE,
4071 p_format_type IN fnd_flex_value_sets.format_type%TYPE,
4072 p_maximum_size IN fnd_flex_value_sets.maximum_size%TYPE,
4073 p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE,
4074 p_segment_name IN fnd_id_flex_segments.segment_name%TYPE)
4075 IS
4076
4077 l_format_type_name VARCHAR2(80);
4078 l_appl_column_type_name VARCHAR2(80);
4079
4080 BEGIN
4081
4082 /*
4083 This rule is to make sure that the base table underlying column
4084 can store values from a VS based on its format type or id_column_type.
4085 If the underlying column type is a C=Char or V=Varchar2 then that
4086 column can store any VS value format. If the VS Validation Type
4087 is U=Special then it does not store any values in the base table
4088 so that can be used. If the underlying column is anything other
4089 than C=Char or V=Varchar2 then we need to make sure the VS will be
4090 compatible with the underlying column. First we check the VS
4091 Table ID Type. If it is not null then we can use that Type to compare
4095 Date(D), then only VS Format Types T=DateTime, I=Time, X=StandardDate,
4092 to the underlying column and they must match. If the VS Table ID
4093 Type is null, then we use the VS Format Type to compare to the
4094 underlying column. If the underlying column is of type
4096 Y=StandardDateTime, Z=StandardTime can be used.
4097 */
4098 IF(NOT((p_application_column_type = 'C' OR p_application_column_type = 'V')
4099 OR p_validation_type = 'U' OR
4100 ((p_application_column_type = p_id_column_type and
4101 p_id_column_type is not null) or
4102 (p_format_type in ('T','I','X','Y','Z') and
4103 p_application_column_type = 'D' and
4104 p_id_column_type is null) or
4105 (p_format_type not in ('T','I','X','Y','Z') and
4106 p_application_column_type = p_format_type and
4107 p_id_column_type is null))))
4108 THEN
4109 -- Get description of codes for the error message --
4110
4111 select meaning
4112 into l_appl_column_type_name
4113 from fnd_lookups
4114 where lookup_code=p_application_column_type and
4115 lookup_type='COLUMN_TYPE';
4116
4117 IF( p_id_column_type is NULL )
4118 THEN
4119 select meaning
4120 into l_format_type_name
4121 from fnd_lookups
4122 where lookup_code=p_format_type and lookup_type='FIELD_TYPE';
4123 fnd_message.set_name('FND','FLEX-VSET FORMAT TYPE CONFLICT');
4124 ELSE
4125 select meaning
4126 into l_format_type_name
4127 from fnd_lookups
4128 where lookup_code=p_id_column_type and lookup_type='COLUMN_TYPE';
4129 fnd_message.set_name('FND','FLEX-VSET ID COL TYPE CONFLICT');
4130 END IF;
4131
4132 fnd_message.set_token('VSET_NAME',p_flex_value_set_name);
4133 fnd_message.set_token('SEG_NAME',p_segment_name);
4134 fnd_message.set_token('FORMAT_TYPE',l_format_type_name);
4135 fnd_message.set_token('DATA_TYPE',l_appl_column_type_name);
4136 app_exception.raise_exception;
4137
4138 END IF;
4139
4140
4141 /*
4142 This rule is to make sure that the max value set size
4143 or the ID column size is not bigger than the underlying column.
4144 If the underlying table column is a date type then no need to
4145 check column size since a date column can store any date type.
4146 */
4147 IF(NOT( (p_application_column_type = 'D')
4148 OR (p_application_column_size >= p_maximum_size
4149 AND p_id_column_size IS NULL)
4150 OR (p_application_column_size >= p_id_column_size
4151 AND p_id_column_size IS NOT NULL)))
4152 THEN
4153
4154 IF( p_id_column_type is NULL )
4155 THEN
4156 fnd_message.set_name('FND','FLEX-VSET MAX SIZE');
4157 fnd_message.set_token('VSET_MAXSIZE',p_maximum_size);
4158 ELSE
4159 fnd_message.set_name('FND','FLEX-VSET ID COL SIZE');
4160 fnd_message.set_token('ID_COL_SIZE',p_id_column_size);
4161 END IF;
4162
4163 fnd_message.set_token('VSET_NAME',p_flex_value_set_name);
4164 fnd_message.set_token('SEG_NAME',p_segment_name);
4165 fnd_message.set_token('COL_SIZE',p_application_column_size);
4166 app_exception.raise_exception;
4167 END IF;
4168
4169 END check_base_table_column;
4170
4171 PROCEDURE is_vset_obsolete(
4172 p_format_type IN fnd_flex_value_sets.format_type%TYPE,
4173 p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE,
4174 p_segment_name IN fnd_id_flex_segments.segment_name%TYPE)
4175 IS
4176 BEGIN
4177
4178 /*
4179 This rule is to make sure Date and DateTime value sets
4180 format types are not used since they are now obsolete and
4181 are replaced by Standard Date(X) and Standard DateTime(Y) format types.
4182 */
4183 IF(p_format_type='D' or p_format_type='T')
4184 THEN
4185 fnd_message.set_name('FND','FLEX-VSET TYPE OBSOLETE');
4186 fnd_message.set_token('VSET_NAME',p_flex_value_set_name);
4187 fnd_message.set_token('SEG_NAME',p_segment_name);
4188 app_exception.raise_exception;
4189 END IF;
4190
4191 END is_vset_obsolete;
4192
4193
4194 -- Purpose
4195 -- Validate a valuset for a given segment, based on
4196 -- critera commented below, and give a error message if the
4197 -- valueset is not valid for that segment. This gives
4198 -- the user a chance to see why he cannot attach a vset to a
4199 -- certain segment. Now we have a separate procedure for DFF/SRS.
4200
4201 PROCEDURE is_value_set_allowed_dff
4202 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE,
4203 p_application_id IN fnd_descr_flex_column_usages.application_id%TYPE,
4204 p_descriptive_flexfield_name IN fnd_descr_flex_column_usages.descriptive_flexfield_name%TYPE,
4205 p_desc_flex_context_code IN fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE,
4206 p_application_column_name IN fnd_descr_flex_column_usages.application_column_name%TYPE,
4207 p_end_user_column_name IN fnd_descr_flex_column_usages.end_user_column_name%TYPE,
4211 l_format_type VARCHAR2(1);
4208 p_column_seq_num IN fnd_descr_flex_column_usages.column_seq_num%TYPE)
4209 IS
4210 l_validation_type VARCHAR2(1);
4212 l_maximum_size NUMBER(3);
4213 l_column_seq_num NUMBER(3);
4214 l_id_column_type VARCHAR2(1);
4215 l_id_column_size NUMBER(3);
4216 l_application_column_name VARCHAR2(30);
4217 l_application_column_type VARCHAR2(1);
4218 l_application_column_size NUMBER(15);
4219
4220 l_flex_value_set_name VARCHAR2(60);
4221 l_parent_value_set_name VARCHAR2(60);
4222 l_parent_value_set_id NUMBER(10);
4223
4224 BEGIN
4225 BEGIN
4226
4227 -- If no vset id is passed in, assume no valueset to valdate.
4228 IF (p_flex_value_set_id IS NULL)
4229 THEN
4230 RETURN;
4231 END IF;
4232
4233 -- Initialize all local variables needed for validation.
4234 l_application_column_name := p_application_column_name;
4235
4236 -- Bug 4657356.
4237 IF (instr(p_descriptive_flexfield_name,'$SRS$') > 0) THEN
4238 -- The SRS form does not need to save the Parameter values entered
4239 -- in the FF. Because of this, there is no base table to validate
4240 -- against. Instead of validating against a base table we use
4241 -- column attributes from fnd_column and fnd_tables.
4242 -- We assign the values returned to what normally we would
4243 -- get from the base table. Now normal validation can continue
4244 -- since we have values for p_application_column_type and
4245 -- p_application_column_size. They are passed in as null from
4246 -- the SRS form.
4247
4248 SELECT
4249 c.column_type, c.width
4250 INTO
4251 l_application_column_type, l_application_column_size
4252 FROM
4253 fnd_columns c, fnd_tables tb
4254 WHERE
4255 tb.application_id = 0 and
4256 tb.table_name = 'FND_SRS_MASTER' and
4257 tb.application_id = c.application_id and
4258 tb.table_id = c.table_id and c.column_name = 'ATTRIBUTE1';
4259 ELSE
4260 SELECT
4261 c.column_type, c.width
4262 INTO
4263 l_application_column_type, l_application_column_size
4264 FROM
4265 fnd_descriptive_flexs dff,
4266 fnd_tables tb,
4267 fnd_columns c
4268 WHERE
4269 c.application_id = tb.application_id and
4270 c.table_id = tb.table_id and
4271 c.application_id = dff.table_application_id and
4272 tb.table_name = dff.application_table_name and
4273 dff.application_id = p_application_id and
4274 dff.descriptive_flexfield_name = p_descriptive_flexfield_name
4275 and c.column_name = l_application_column_name;
4276 END IF;
4277
4278 SELECT
4279 v.validation_type, v.format_type, v.maximum_size,
4280 v.flex_value_set_name, t.id_column_type,
4281 t.id_column_size
4282 INTO
4283 l_validation_type, l_format_type, l_maximum_size,
4284 l_flex_value_set_name, l_id_column_type,
4285 l_id_column_size
4286 FROM
4287 fnd_flex_value_sets v,
4288 fnd_flex_validation_tables t
4289 WHERE
4290 v.flex_value_set_id = p_flex_value_set_id and
4291 v.flex_value_set_id = t.flex_value_set_id(+);
4292
4293
4294 -- Please note the order in which the rules are checked is based
4295 -- which ones are more serious. A value set may have more than
4296 -- one rule that it violates. The one we display first is the most
4297 -- serious. For example, if a vset format type is obsolete, I don't
4298 -- want to first get an error message saying that my format type does
4299 -- not match the database column data type. The first thing I want to
4300 -- know is the the vset is obsolete.
4301
4302 is_vset_obsolete(l_format_type,
4303 l_flex_value_set_name,
4304 p_end_user_column_name);
4305
4306 check_base_table_column(l_application_column_type,
4307 l_application_column_size,
4308 l_id_column_type,
4309 l_id_column_size,
4310 l_validation_type,
4311 l_format_type,
4312 l_maximum_size,
4313 l_flex_value_set_name,
4314 p_end_user_column_name);
4315
4316 /*
4317 This rule is to make sure that a Dependent VS has it's
4318 Indpendent VS to reference in the previous segment
4319 If it is not a Dependent VS then no need to check child
4320 parent relationship. If it is a Dependent value set then
4321 make sure it has it's parent.
4322 */
4323 IF(l_validation_type ='D' or l_validation_type ='Y') THEN
4324 BEGIN
4325 SELECT
4326 min(u.column_seq_num), v.parent_flex_value_set_id
4327 INTO
4328 l_column_seq_num, l_parent_value_set_id
4329 FROM
4330 fnd_flex_value_sets v, fnd_descr_flex_column_usages u
4331 WHERE
4332 v.flex_value_set_id = p_flex_value_set_id
4333 -- Bug#4564981
4334 AND u.application_id = p_application_id
4335 AND u.descriptive_flexfield_name = p_descriptive_flexfield_name
4339 AND v.parent_flex_value_set_id = u.flex_value_set_id
4336 -- Bug#4410208, In SRS agruments form,the context is null when a parameter is defined
4337 AND (p_desc_flex_context_code is null or
4338 u.descriptive_flex_context_code = p_desc_flex_context_code)
4340 group by v.parent_flex_value_set_id;
4341
4342 IF(NOT(l_column_seq_num < p_column_seq_num)) THEN
4343
4344 select flex_value_set_name
4345 into l_parent_value_set_name
4346 from fnd_flex_value_sets
4347 where
4348 flex_value_set_id=l_parent_value_set_id;
4349
4350 fnd_message.set_name('FND','FLEX-VSET IND DEP VSET ORDER');
4351 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4352 fnd_message.set_token('SEG_NAME',p_end_user_column_name);
4353 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
4354 app_exception.raise_exception;
4355 END IF;
4356 EXCEPTION
4357 WHEN NO_DATA_FOUND THEN
4358
4359 select flex_value_set_name
4360 into l_parent_value_set_name
4361 from fnd_flex_value_sets
4362 where
4363 flex_value_set_id in (select parent_flex_value_set_id
4364 from fnd_flex_value_sets
4365 where flex_value_set_id=p_flex_value_set_id);
4366
4367 fnd_message.set_name('FND','FLEX-VSET PARENT VSET MISSING');
4368 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4369 fnd_message.set_token('SEG_NAME',p_end_user_column_name);
4370 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
4371 app_exception.raise_exception;
4372 WHEN OTHERS THEN RAISE;
4373 END;
4374 END IF;
4375
4376 EXCEPTION
4377 WHEN NO_DATA_FOUND THEN
4378 RAISE;
4379 WHEN OTHERS THEN RAISE;
4380 END;
4381
4382 END is_value_set_allowed_dff;
4383
4384
4385 -- Purpose
4386 -- Validate a valuset for a given segment, based on
4387 -- critera commented below, and give a error message if the
4388 -- valueset is not valid for that segment. This gives
4389 -- the user a chance to see why he cannot attach a vset to a
4390 -- certain segment. Now we have a separate procedure for KFF.
4391
4392 PROCEDURE is_value_set_allowed_kff
4393 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE,
4394 p_application_id IN fnd_id_flex_segments.application_id%TYPE,
4395 p_id_flex_code IN fnd_id_flex_segments.id_flex_code%TYPE,
4396 p_id_flex_num IN fnd_id_flex_segments.id_flex_num%TYPE,
4397 p_application_column_name IN fnd_id_flex_segments.application_column_name%TYPE,
4398 p_segment_name IN fnd_id_flex_segments.segment_name%TYPE,
4399 p_segment_num IN fnd_id_flex_segments.segment_num%TYPE)
4400 IS
4401
4402 l_validation_type VARCHAR2(1);
4403 l_format_type VARCHAR2(1);
4404 l_maximum_size NUMBER(3);
4405 l_allow_id_valuesets VARCHAR2(1);
4406 l_id_column_name VARCHAR2(240);
4407 l_id_column_type VARCHAR2(1);
4408 l_id_column_size NUMBER(3);
4409 l_segment_num NUMBER(3);
4410 l_number_precision NUMBER(2);
4411 l_application_column_type VARCHAR2(1);
4412 l_application_column_size NUMBER(15);
4413
4414 l_flex_value_set_name VARCHAR2(60);
4415 l_parent_value_set_name VARCHAR2(60);
4416 l_parent_value_set_id NUMBER(10);
4417
4418 BEGIN
4419 BEGIN
4420
4421 -- If no vset id is passed in, assume no valueset to valdate.
4422 IF (p_flex_value_set_id IS NULL)
4423 THEN
4424 RETURN;
4425 END IF;
4426
4427 -- Initialize all local variables needed for validation.
4428
4429 SELECT
4430 c.column_type, c.width, kff.allow_id_valuesets
4431 INTO
4432 l_application_column_type, l_application_column_size, l_allow_id_valuesets
4433 FROM
4434 fnd_id_flexs kff,
4435 fnd_tables tb,
4436 fnd_columns c
4437 WHERE
4438 c.application_id = tb.application_id and
4439 c.table_id = tb.table_id and
4440 c.application_id = kff.table_application_id and
4441 tb.table_name = kff.application_table_name and
4442 kff.application_id = p_application_id and
4443 kff.id_flex_code = p_id_flex_code
4444 and c.column_name = p_application_column_name;
4445
4446 SELECT
4447 v.validation_type, v.format_type, v.maximum_size,
4448 v.number_precision, v.flex_value_set_name,
4449 t.id_column_name, t.id_column_type, t.id_column_size
4450 INTO
4451 l_validation_type, l_format_type, l_maximum_size,
4452 l_number_precision, l_flex_value_set_name,
4453 l_id_column_name, l_id_column_type, l_id_column_size
4454 FROM
4455 fnd_flex_value_sets v,
4456 fnd_flex_validation_tables t
4457 WHERE
4458 v.flex_value_set_id = p_flex_value_set_id and
4459 v.flex_value_set_id = t.flex_value_set_id(+);
4460
4461
4462 -- Please note the order in which the rules are checked is based
4466 -- want to first get an error message saying that my format type does
4463 -- which ones are more serious. A value set may have more than
4464 -- one rule that it violates. The one we display first is the most
4465 -- serious. For example, if a vset format type is obsolete, I don't
4467 -- not match the database column data type. The first thing I want to
4468 -- know is the the vset is obsolete.
4469
4470 is_vset_obsolete(l_format_type,
4471 l_flex_value_set_name,
4472 p_segment_name);
4473
4474 /*
4475 Table Validate value sets with hidden ID columns may
4476 not be used with KFF's defiend with 'ID VS not allowed'.
4477 Check to see if the FF is allowed to have an ID VS.
4478 If so, then the VS can be used. If the FF
4479 is not allowed to have an ID VS, then we need to
4480 check if the value set is defined as an ID VS.
4481 if the id_column_name is null, then it is not an
4482 ID VS and it can be displayed.
4483 */
4484 IF(NOT(l_allow_id_valuesets = 'Y' OR
4485 (l_allow_id_valuesets = 'N' AND l_id_column_name is NULL)))
4486 THEN
4487 fnd_message.set_name('FND','FLEX-VSET IDVSET RESTRICTED');
4488 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4489 fnd_message.set_token('SEG_NAME',p_segment_name);
4490 app_exception.raise_exception;
4491 END IF;
4492
4493
4494 /*
4495 Table Validate value sets with hidden ID columns may
4496 not be used with KFF's defiend with 'ID VS not allowed'.
4497 Check to see if the FF is allowed to have an ID VS.
4498 If so, then the VS can be displayed. If the FF
4499 is not allowed to have an ID VS, then we need to
4500 check if the Value Set is defined as an ID VS.
4501 If the VS has a Validation Type of Translatable
4502 Independent/Dependent, then it cannot be used
4503 because those validation types are internally
4504 ID value sets.
4505 */
4506 IF(NOT(l_allow_id_valuesets = 'Y' OR
4507 (l_allow_id_valuesets = 'N' AND
4508 l_validation_type <> 'X' AND l_validation_type <> 'Y')))
4509 THEN
4510 fnd_message.set_name('FND','FLEX-VSET IDVSET TR RESTRICTED');
4511 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4512 fnd_message.set_token('SEG_NAME',p_segment_name);
4513 app_exception.raise_exception;
4514 END IF;
4515
4516
4517 /*
4518 'Date', 'DateTime', 'Time' and 'Number with Precision'
4519 vsets are a type of ID vsets.
4520 Some KFF's do not allow ID vsets. If ID vsets are not
4521 allowed then we must make sure these vsets are not used.
4522 */
4523 IF(NOT(l_allow_id_valuesets = 'Y' OR
4524 (l_allow_id_valuesets = 'N' AND
4525 (l_format_type <> 'X' AND
4526 l_format_type <> 'Y' AND
4527 l_format_type <> 'I' AND
4528 l_format_type <> 'N') OR
4529 ((l_format_type = 'N' AND
4530 l_number_precision IS NOT NULL AND
4531 l_number_precision = 0)))))
4532 THEN
4533 fnd_message.set_name('FND','FLEX-VSET IDVSET DN RESTRICTED');
4534 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4535 fnd_message.set_token('SEG_NAME',p_segment_name);
4536 app_exception.raise_exception;
4537 END IF;
4538
4539
4540 check_base_table_column(l_application_column_type,
4541 l_application_column_size,
4542 l_id_column_type,
4543 l_id_column_size,
4544 l_validation_type,
4545 l_format_type,
4546 l_maximum_size,
4547 l_flex_value_set_name,
4548 p_segment_name);
4549
4550
4551 /*
4552 This rule is to make sure that a Dependent VS has it's
4553 Indpendent VS to reference in the previous segment
4554 If it is not a Dependent VS then no need to check child
4555 parent relationship. If it is a Dependent value set then
4556 make sure it has it's parent.
4557 */
4558 IF(l_validation_type ='D' or l_validation_type ='Y') THEN
4559 BEGIN
4560 SELECT
4561 min(s.segment_num), v.parent_flex_value_set_id
4562 INTO
4563 l_segment_num, l_parent_value_set_id
4564 FROM
4565 fnd_flex_value_sets v, fnd_id_flex_segments s
4566 WHERE
4567 v.flex_value_set_id = p_flex_value_set_id
4568 -- Bug#4564981
4569 AND s.application_id = p_application_id
4570 AND s.id_flex_code = p_id_flex_code
4571 AND s.id_flex_num = p_id_flex_num
4572 AND v.parent_flex_value_set_id = s.flex_value_set_id
4573 group by v.parent_flex_value_set_id;
4574
4575 IF(NOT(l_segment_num < p_segment_num)) THEN
4576
4577 select flex_value_set_name
4578 into l_parent_value_set_name
4579 from fnd_flex_value_sets
4580 where
4581 flex_value_set_id=l_parent_value_set_id;
4582
4583 fnd_message.set_name('FND','FLEX-VSET IND DEP VSET ORDER');
4584 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4585 fnd_message.set_token('SEG_NAME',p_segment_name);
4589
4586 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
4587 app_exception.raise_exception;
4588 END IF;
4590 EXCEPTION
4591 WHEN NO_DATA_FOUND THEN
4592
4593 select flex_value_set_name
4594 into l_parent_value_set_name
4595 from fnd_flex_value_sets
4596 where
4597 flex_value_set_id in (select parent_flex_value_set_id
4598 from fnd_flex_value_sets
4599 where flex_value_set_id=p_flex_value_set_id);
4600
4601 fnd_message.set_name('FND','FLEX-VSET PARENT VSET MISSING');
4602 fnd_message.set_token('VSET_NAME',l_flex_value_set_name);
4603 fnd_message.set_token('SEG_NAME',p_segment_name);
4604 fnd_message.set_token('PARENT_VSET_NAME',l_parent_value_set_name);
4605 app_exception.raise_exception;
4606 WHEN OTHERS THEN RAISE;
4607 END;
4608 END IF;
4609
4610 EXCEPTION
4611 WHEN NO_DATA_FOUND THEN
4612 RAISE;
4613 WHEN OTHERS THEN RAISE;
4614 END;
4615
4616 END is_value_set_allowed_kff;
4617
4618
4619 /**********************************************************************
4620 This procedure will take a string and give you the
4621 location (begining and end postion), of the first bind variable it
4622 finds. It starts it's search from the location of position p_sbegin.
4623 p_sql_clause is the input string to be parsed.
4624 p_sbegin is the location the search will start from.
4625 x_bbegin is the location of the begining of the bind variable.
4626 x_bend is the location of the end of the bind variable.
4627 **********************************************************************/
4628
4629 PROCEDURE find_bind(
4630 p_sql_clause IN VARCHAR2,
4631 p_sbegin IN NUMBER,
4632 x_bbegin OUT NOCOPY NUMBER,
4633 x_bend OUT NOCOPY NUMBER)
4634 IS
4635
4636 l_marker bind_array_type;
4637 l_sbegin NUMBER;
4638 l_bbegin NUMBER;
4639 l_bend NUMBER;
4640 temppos NUMBER;
4641 temptype VARCHAR2(1);
4642 WHITESPACE VARCHAR2(1);
4643 SINGLEQ VARCHAR2(1);
4644 PAREN VARCHAR2(1);
4645 COMMA VARCHAR2(1);
4646 EQUALTO VARCHAR2(1);
4647
4648 BEGIN
4649
4650 WHITESPACE := ' ';
4651 SINGLEQ := '''';
4652 PAREN := ')';
4653 COMMA := ',';
4654 EQUALTO := '=';
4655
4656 l_sbegin := p_sbegin;
4657
4658 LOOP
4659 -- Find the colon. The colon signifies a possible bind variable.
4660 -- We say possible because the colon could be inside single quotes.
4661 l_bbegin := INSTR(p_sql_clause, ':', l_sbegin);
4662 if(l_bbegin = 0) then
4663 -- No more colons found, return 0 for begin
4664 -- position of the bind variable.
4665 x_bbegin := l_bbegin;
4666 x_bend := 0;
4667 EXIT;
4668 end if;
4669
4670 -- After the colon look for these ending markers. The one that
4671 -- comes first, is the marker that indicates the end of the bind variable.
4672 -- The exception is the single quote. A single quote signifies
4673 -- No bind because the : appears within single quotes.
4674 -- The markers are White Space, Parenthesis, and Comma.
4675 l_marker(1).pos:=INSTR (p_sql_clause, WHITESPACE, l_bbegin);
4676 l_marker(1).type:=WHITESPACE;
4677 l_marker(2).pos := INSTR (p_sql_clause, SINGLEQ, l_bbegin);
4678 l_marker(2).type:=SINGLEQ;
4679 l_marker(3).pos := INSTR(p_sql_clause, PAREN, l_bbegin);
4680 l_marker(3).type:=PAREN;
4681 l_marker(4).pos := INSTR(p_sql_clause, COMMA, l_bbegin);
4682 l_marker(4).type:=COMMA;
4683 l_marker(5).pos := INSTR(p_sql_clause, EQUALTO, l_bbegin);
4684 l_marker(5).type:=EQUALTO;
4685
4686
4687 -- Sort the markers to see which one comes first.
4688 -- Sort the markers in order of first occurance
4689 -- The l_marker will equal 0 if a marker is not found.
4690 -- Place all 0 values at the end of sort order. All numbers
4691 -- will be sorted in ascending order except for 0. 0's will
4692 -- be always at the end since it means not found.
4693 FOR i IN 1..4 LOOP
4694 FOR j IN (i+1)..5 LOOP
4695 IF (((l_marker(i).pos > l_marker(j).pos) AND
4696 (l_marker(j).pos<>0)) OR
4697 (l_marker(i).pos=0 AND l_marker(j).pos<>0)) THEN
4698 temppos := l_marker(j).pos;
4699 temptype := l_marker(j).type;
4700 l_marker(j).pos := l_marker(i).pos;
4701 l_marker(j).type := l_marker(i).type;
4702 l_marker(i).pos := temppos;
4703 l_marker(i).type := temptype;
4704 END IF;
4705 END LOOP;
4706 END LOOP;
4707
4708 IF (l_marker(1).pos <> 0 AND l_marker(1).type <> SINGLEQ) THEN
4709 -- Bind found, return begin and end position of the bind variable.
4710 x_bbegin := l_bbegin;
4711 x_bend := l_marker(1).pos;
4712 EXIT;
4713 ELSIF (l_marker(1).pos = 0 AND l_marker(1).type <> SINGLEQ) THEN
4714 -- Bind found, return begin and end position of the bind variable.
4718 EXIT;
4715 -- Assume Bind is from : to end of string, since no markers were found.
4716 x_bbegin := l_bbegin;
4717 x_bend := length(p_sql_clause) + 1;
4719 ELSE
4720 -- No bind found, look for next colon.
4721 l_sbegin := l_bbegin + 1;
4722 END IF;
4723
4724 END LOOP;
4725
4726 END find_bind;
4727
4728
4729 /**********************************************************************
4730 This function takes a string and replaces any bind variables
4731 (such as :block.field, :$FLEX$.<vset> and :$PROFILE$.<profile>)
4732 with a 'null'. It then returns the new string to the calling function
4733 **********************************************************************/
4734 FUNCTION replace_binds(p_sql_clause IN VARCHAR2)
4735 RETURN VARCHAR2
4736 IS
4737
4738 l_sql_clause VARCHAR2(32000);
4739 l_build_nobind_clause VARCHAR2(32000);
4740 l_nobinds_clause VARCHAR2(32000);
4741 l_bind_exists NUMBER;
4742 l_sbegin NUMBER;
4743 l_bbegin NUMBER;
4744 l_bend NUMBER;
4745 NEWLINE VARCHAR2(4);
4746 TAB VARCHAR2(4);
4747 WHITESPACE VARCHAR2(1);
4748
4749 BEGIN
4750
4751 NEWLINE := fnd_global.newline;
4752 TAB := fnd_global.tab;
4753 WHITESPACE := ' ';
4754
4755 if(p_sql_clause is not NULL) then
4756
4757 -- Replace ALL NEWLINES and TABS with a WHITESPACE
4758 l_sql_clause := replace(p_sql_clause,NEWLINE, WHITESPACE);
4759 l_sql_clause := replace(l_sql_clause,TAB, WHITESPACE);
4760
4761 -- Check to see if any binds vars exist in the sql clause
4762 l_bind_exists := instr(l_sql_clause, ':');
4763
4764 -- If there are no bind vars then the sql clause can be tested as is
4765 if(l_bind_exists = 0) then
4766 l_nobinds_clause := l_sql_clause;
4767 else
4768 -- If binds exist then they need to be removed and replaced
4769 -- with the value null. We cannot resolve binds at this point
4770 -- so we just replace them with null and test the rest of
4771 -- the awc for valid column names and syntax.
4772
4773 l_build_nobind_clause := '';
4774 -- Start search from the begining of the string.
4775 l_sbegin := 1;
4776
4777 LOOP
4778
4779 find_bind(l_sql_clause, l_sbegin, l_bbegin, l_bend);
4780
4781 if(l_bbegin=0) then
4782 -- No more bind variables found.
4783 EXIT;
4784 end if;
4785
4786 -- Build string without binds.
4787 -- Grab the text from the end of last bind to the
4788 -- beginning of the next bind.
4789 l_build_nobind_clause := l_build_nobind_clause ||
4790 substr(l_sql_clause, l_sbegin, l_bbegin - l_sbegin );
4791
4792 -- Insert null to replace the bind variable
4793 l_build_nobind_clause := l_build_nobind_clause||'null';
4794
4795 -- Begin next search at the end of the bind variable in the string.
4796 l_sbegin := l_bend;
4797
4798 END LOOP;
4799
4800 -- No more binds found, concatenate the rest of the string.
4801 l_build_nobind_clause := l_build_nobind_clause ||
4802 substr(l_sql_clause, l_sbegin,
4803 length(l_sql_clause) - l_sbegin + 1);
4804
4805
4806 l_nobinds_clause := l_build_nobind_clause;
4807
4808 end if;
4809 else
4810 l_nobinds_clause := p_sql_clause;
4811 end if;
4812
4813 RETURN l_nobinds_clause;
4814
4815
4816 END replace_binds;
4817
4818
4819
4820 PROCEDURE validate_table_vset(
4821 p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE,
4822 p_id_column_name IN fnd_flex_validation_tables.id_column_name%TYPE,
4823 p_value_column_name IN fnd_flex_validation_tables.value_column_name%TYPE,
4824 p_meaning_column_name IN fnd_flex_validation_tables.meaning_column_name%TYPE,
4825 p_additional_quickpick_columns IN fnd_flex_validation_tables.additional_quickpick_columns%TYPE,
4826 p_application_table_name IN fnd_flex_validation_tables.application_table_name%TYPE,
4827 p_additional_where_clause IN fnd_flex_validation_tables.additional_where_clause%TYPE,
4828 x_result OUT NOCOPY VARCHAR2,
4829 x_message OUT NOCOPY VARCHAR2)
4830 IS
4831 l_stmt VARCHAR2(32000);
4832 l_stmt1 VARCHAR2(32000);
4833 l_message VARCHAR(32000);
4834 l_cur_hdl INTEGER;
4835 l_tmpaddlclmn fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
4836 l_tmpaddlclmnfrmt fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
4837 l_double_quote varchar2(3);
4838 l_appl_string varchar2(10);
4839 l_star varchar2(2);
4840 l_name_string varchar2(10);
4841 l_open_bracket varchar2(3);
4842 l_close_bracket varchar2(3);
4843 l_additional_column_width1 fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
4844 l_additional_column_width2 fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
4848 l_additional_column_width3 varchar2(20);
4845 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
4846 l_id_column_name fnd_flex_validation_tables.id_column_name%TYPE;
4847 l_value_column_name fnd_flex_validation_tables.value_column_name%TYPE;
4849 l_position1_double_quote number;
4850 l_position2_double_quote number;
4851 l_position_open_bracket number;
4852 l_position_close_bracket number;
4853 l_position_appl_string number;
4854 l_position_name_string number;
4855 l_starting_position number;
4856 NEWLINE VARCHAR2(4);
4857 WHITESPACE VARCHAR2(4);
4858 COMMA VARCHAR2(1);
4859 l_into varchar2_array_type;
4860 l_tmpaddlclmn1 fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
4861 l_tmpaddlclmn_no_into fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
4862 l_char varchar2(4);
4863 l_position number;
4864 l_into_begin number;
4865 l_into_end number;
4866 l_where varchar2(10);
4867 l_orderby varchar2(10);
4868 l_ltrim_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
4869
4870 BEGIN
4871
4872 l_double_quote := '"';
4873 l_appl_string := 'APPL=';
4874 l_star := '*';
4875 l_name_string := 'NAME=';
4876 l_open_bracket := '(';
4877 l_close_bracket := ')';
4878 l_starting_position := 1;
4879 NEWLINE := fnd_global.newline;
4880 WHITESPACE := fnd_global.local_chr(32);
4881 l_into(0) := WHITESPACE||'into'||WHITESPACE;
4882 l_into(1) := WHITESPACE||'into'||NEWLINE;
4883 l_into(2) := NEWLINE||'into'||WHITESPACE;
4884 l_into(3) := NEWLINE||'into'||NEWLINE;
4885 COMMA := ',';
4886
4887 l_stmt := NULL; -- Initialize
4888
4889 l_stmt := p_value_column_name || ' VALUE ';
4890
4891 IF (p_meaning_column_name is not NULL) THEN
4892 l_stmt := l_stmt||', '||p_meaning_column_name || ' DESCRIPTION ';
4893 END IF;
4894
4895 IF (p_id_column_name is not NULL) THEN
4896 l_stmt := l_stmt||', '||p_id_column_name;
4897 END IF;
4898
4899 IF (p_additional_quickpick_columns is not NULL) THEN
4900
4901 l_tmpaddlclmn := p_additional_quickpick_columns;
4902 l_tmpaddlclmnfrmt := p_additional_quickpick_columns;
4903
4904 /* Following section replaces message name with DUMMY string */
4905 LOOP
4906 l_position1_double_quote := instr(l_tmpaddlclmn,l_double_quote,l_starting_position);
4907 l_position2_double_quote := instr(l_tmpaddlclmn,l_double_quote,l_position1_double_quote+1);
4908
4909 EXIT WHEN (l_position1_double_quote=0 or l_position2_double_quote=0);
4910
4911 /* Checking for message name starting with APPL= in quotations */
4912 l_additional_column_width2 := substr(l_tmpaddlclmn,l_position1_double_quote+1,l_position2_double_quote-l_position1_double_quote-1);
4913 l_position_appl_string := instr(l_additional_column_width2,l_appl_string);
4914
4915 if (l_position_appl_string>0)
4916 then
4917 /* Check if NAME= is present */
4918 l_position_name_string := instr(l_additional_column_width2,l_name_string);
4919 if (l_position_name_string>0)
4920 then
4921 l_tmpaddlclmn := replace(l_tmpaddlclmn,l_additional_column_width2,'DUMMY');
4922 l_position2_double_quote := instr(l_tmpaddlclmn,l_double_quote,l_position1_double_quote+1);
4923 end if;
4924 end if;
4925 l_starting_position := l_position2_double_quote + 1;
4926 END LOOP;
4927
4928 /* Following section removes (width) from addtl. columns */
4929 l_starting_position := 1;
4930 LOOP
4931 l_position_open_bracket := instr(l_tmpaddlclmn,l_open_bracket,l_starting_position);
4932 l_position_close_bracket := instr(l_tmpaddlclmn,l_close_bracket,l_position_open_bracket+1);
4933
4934 EXIT WHEN (l_position_open_bracket=0 or l_position_close_bracket=0);
4935
4936 l_char := substr(l_tmpaddlclmn,l_position_open_bracket-1,1);
4937 if ((l_char = WHITESPACE) or (l_char = NEWLINE) or (l_char = l_double_quote))
4938 then
4939 l_additional_column_width1 := substr(l_tmpaddlclmn,l_position_open_bracket+1,l_position_close_bracket-l_position_open_bracket-1);
4940 /* Bug 4586657 - Added check for verifying the column width value */
4941 l_additional_column_width2 := replace(translate(l_additional_column_width1,'1234567890*','00000000000'),'0','');
4942 if (l_additional_column_width2 is not NULL) then
4943 x_result := 'Failure';
4944 x_message := 'Invalid Width specified for Additional Column';
4945 return;
4946 end if;
4947
4948 l_tmpaddlclmnfrmt := substr(l_tmpaddlclmn,1,l_position_open_bracket-1)||substr(l_tmpaddlclmn,l_position_close_bracket+1);
4949 l_tmpaddlclmn := l_tmpaddlclmnfrmt;
4950 l_starting_position := l_position_open_bracket + 1;
4951 else
4952 l_starting_position := l_position_close_bracket + 1;
4953 end if;
4954 END LOOP;
4955
4956 /* Bug 4908763 problem 1. Remove all " into field " from
4957 Additional Columns.
4958 select application_id "ap_id' into ap_id from fnd_application;
4959 becomes
4963 for i in 0..3
4960 select application_id "ap_id" from fnd_application; */
4961
4962 l_tmpaddlclmn_no_into := l_tmpaddlclmn;
4964 loop
4965 l_into_begin := instr(lower(l_tmpaddlclmn_no_into), l_into(i));
4966 while (l_into_begin > 0)
4967 loop
4968 l_tmpaddlclmn1 := substr(l_tmpaddlclmn_no_into, 0, l_into_begin);
4969 l_into_end := l_into_begin + 5;
4970 l_position := 0;
4971
4972 l_char := substr(l_tmpaddlclmn_no_into, l_into_end, 1);
4973 while ((l_char = WHITESPACE) or (l_char = NEWLINE))
4974 loop
4975 l_position := l_position + 1;
4976 l_char := substr(l_tmpaddlclmn_no_into, l_into_end + l_position, 1);
4977 end loop; /* Located beginning of field name after INTO */
4978
4979 while ((l_char <> WHITESPACE) and (l_char <> NEWLINE) and (l_char <> COMMA) and (l_char is not NULL))
4980 loop
4981 l_position := l_position + 1;
4982 l_char := substr(l_tmpaddlclmn_no_into, l_into_end + l_position, 1);
4983 end loop; /* Located end of field name after INTO */
4984
4985 l_tmpaddlclmn_no_into := l_tmpaddlclmn1||WHITESPACE||substr(l_tmpaddlclmn_no_into, l_into_end + l_position);
4986 l_into_begin := instr(lower(l_tmpaddlclmn_no_into), l_into(i));
4987 end loop;
4988 end loop;
4989
4990 l_tmpaddlclmn := l_tmpaddlclmn_no_into;
4991 l_stmt := l_stmt||', '||l_tmpaddlclmn;
4992
4993 END IF;
4994
4995 l_cur_hdl := dbms_sql.open_cursor;
4996
4997 IF (l_stmt is not NULL) THEN
4998
4999 -- Remove bind variables, if any, and replace with null
5000 l_stmt := replace_binds(p_sql_clause => l_stmt);
5001
5002 -- Test Value Column, ID Column and Additional Columns
5003 l_stmt1 := 'select '||l_stmt||
5004 ' from '||p_application_table_name||
5005 ' where rownum=1';
5006 dbms_sql.parse(l_cur_hdl, l_stmt1, dbms_sql.native);
5007
5008 END IF;
5009
5010 -- Test the ID Column
5011 IF (p_id_column_name is not NULL) THEN
5012
5013 l_id_column_name := replace_binds(p_sql_clause => p_id_column_name);
5014
5015 l_stmt1 := 'select '||l_stmt||
5016 ' from '||p_application_table_name||
5017 ' where rownum=1 and '||
5018 l_id_column_name||' is NULL';
5019 dbms_sql.parse(l_cur_hdl, l_stmt1, dbms_sql.native);
5020 END IF;
5021
5022 -- Test the Value Column
5023 IF (p_value_column_name is not NULL) THEN
5024
5025 l_value_column_name :=replace_binds(p_sql_clause => p_value_column_name);
5026
5027 l_stmt1 := 'select '||l_stmt||
5028 ' from '||p_application_table_name||
5029 ' where rownum=1 and '||
5030 l_value_column_name||' is NULL';
5031 dbms_sql.parse(l_cur_hdl, l_stmt1, dbms_sql.native);
5032
5033 END IF;
5034
5035 -- Test the Additional Where clause
5036 IF(p_additional_where_clause is not NULL) THEN
5037
5038 -- Limit the Addt'l Where clause to 32K
5039 l_additional_where_clause := substr(p_additional_where_clause,0,32000);
5040
5041 l_additional_where_clause :=
5042 replace_binds(p_sql_clause => l_additional_where_clause);
5043
5044 /*
5045 Bug 4908763 problems 3 and 7. Add " where " if "where" or "order by"
5046 is not there in Where/Order By clause.
5047 */
5048 l_ltrim_where_clause := lower(ltrim(ltrim(l_additional_where_clause, WHITESPACE), NEWLINE));
5049 l_where := substr(l_ltrim_where_clause, 0, 5);
5050 l_orderby := substr(l_ltrim_where_clause, 0, 8);
5051 if ((l_where='where') or (l_orderby='order by'))
5052 then
5053 l_stmt1 := 'select '||l_stmt||' from '||p_application_table_name||' '||
5054 l_additional_where_clause;
5055 else
5056 l_stmt1 := 'select '||l_stmt||' from '||p_application_table_name
5057 || ' where ' ||l_additional_where_clause;
5058 end if;
5059 dbms_sql.parse(l_cur_hdl, l_stmt1, dbms_sql.native);
5060
5061 END IF;
5062
5063 dbms_sql.close_cursor(l_cur_hdl);
5064
5065 x_result := 'Success';
5066
5067 EXCEPTION
5068 WHEN OTHERS THEN
5069 if dbms_sql.is_open(l_cur_hdl) then
5070 dbms_sql.close_cursor(l_cur_hdl);
5071 end if;
5072 x_result := 'Failure';
5073 l_message := substr(l_stmt1,1,32000);
5074 x_message := substr(SQLERRM || NEWLINE || l_message,1,32000);
5075
5076 END validate_table_vset; /* procedure */
5077
5078
5079
5080
5081 /* END_PUBLIC */
5082
5083 END fnd_flex_val_api; /* package body*/
[Home] [Help]