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