[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_KEY_API
Source
1 PACKAGE BODY fnd_flex_key_api AS
2 /* $Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $ */
3
4 -- ==================================================
5 -- CACHING
6 -- ==================================================
7 g_cache_return_code VARCHAR2(30);
8 g_cache_key VARCHAR2(2000);
9 g_cache_value fnd_plsql_cache.generic_cache_value_type;
10
11 -- --------------------------------------------------
12 -- soq : Segment Order by Qualifier Name Cache
13 -- --------------------------------------------------
14 soq_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
15 soq_cache_storage fnd_plsql_cache.generic_cache_values_type;
16
17 value_too_large EXCEPTION;
18 PRAGMA EXCEPTION_INIT(value_too_large, -01401);
19
20 debug_mode_on BOOLEAN := FALSE;
21 do_validation BOOLEAN := TRUE;
22 internal_messages VARCHAR2(10000);
23 chr_newline VARCHAR2(8) := fnd_global.newline;
24
25
26 /* ---------- WHO INFORMATION ---------- */
27
28 who_mode VARCHAR2(1000) := NULL; /* whether customer_data or seed_data */
29 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE;
30 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE;
31 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE;
32 creation_date_i fnd_flex_value_sets.creation_date%TYPE;
33 created_by_i fnd_flex_value_sets.created_by%TYPE;
34
35 --
36 -- ERROR constants
37 --
38 error_others CONSTANT NUMBER := -20100;
39 error_no_data_found CONSTANT NUMBER := -20101;
40 error_tag_white_space CONSTANT NUMBER := -20102;
41 error_tag_max_length CONSTANT NUMBER := -20103;
42 error_tag_exists CONSTANT NUMBER := -20104;
43 error_tag_not_exists CONSTANT NUMBER := -20105;
44 error_clause_comments CONSTANT NUMBER := -20106;
45 error_awc_null CONSTANT NUMBER := -20107;
46 error_clause_exists CONSTANT NUMBER := -20108;
47
48 CURSOR structure_c(flexfield IN flexfield_type,
49 enabled IN VARCHAR2 DEFAULT NULL) IS
50 SELECT id_flex_structure_name structure_name,
51 id_flex_num structure_number
52 FROM fnd_id_flex_structures_vl
53 WHERE application_id = flexfield.application_id
54 AND id_flex_code = flexfield.flex_code
55 AND (structure_c.enabled IS NULL OR enabled_flag = 'Y')
56 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL
57 ORDER BY id_flex_code;
58
59 CURSOR segment_c(flexfield IN flexfield_type,
60 structure IN structure_type,
61 enabled IN VARCHAR2 DEFAULT NULL) IS
62 SELECT segment_name,
63 application_column_name
64 FROM fnd_id_flex_segments_vl
65 WHERE application_id = flexfield.application_id
66 AND id_flex_code = flexfield.flex_code
67 AND id_flex_num = structure.structure_number
68 AND (enabled IS NULL or enabled_flag = 'Y')
69 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL
70 ORDER BY segment_num;
71
72
73 --
74 -- TYPEs
75 --
76 SUBTYPE app_type IS fnd_application%ROWTYPE;
77
78 --
79 -- Global Variables
80 --
81 g_unused_argument VARCHAR2(100);
82
83 --
84 -- Forward declerations;
85 --
86
87 FUNCTION customer_mode RETURN BOOLEAN;
88
89 /* ============================================================ */
90 /* MESSAGING */
91 /* ============================================================ */
92 PROCEDURE debug_on IS
93 BEGIN
94 debug_mode_on := TRUE;
95 END;
96
97 PROCEDURE debug_off IS
98 BEGIN
99 debug_mode_on := FALSE;
100 END;
101
102 PROCEDURE set_validation(v_in IN BOOLEAN) IS
103 BEGIN
104 do_validation := v_in;
105 END;
106
107 PROCEDURE message(msg VARCHAR2) IS
108 BEGIN
109 internal_messages := internal_messages || msg || chr_newline;
110 -- internal_messages := internal_messages || Sqlerrm; /* error stamp */
111 END;
112
113 PROCEDURE message_init IS
114 BEGIN
115 internal_messages := '';
116 IF (customer_mode) THEN
117 internal_messages := 'CUSTOMER_DATA:' || chr_newline;
118 ELSE
119 internal_messages := 'SEED_DATA:' || chr_newline;
120 END IF;
121 END;
122
123 FUNCTION version RETURN VARCHAR2 IS
124 BEGIN
125 RETURN('$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $');
126 END;
127
128
129 FUNCTION message RETURN VARCHAR2 IS
130 BEGIN
131 RETURN internal_messages;
132 END;
133
134 /* only used in testing */
135 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
136 IS
137 i INTEGER;
138 m INTEGER;
139 c INTEGER := 75;
140 BEGIN
141 execute immediate ('begin dbms' ||
142 '_output' ||
143 '.enable(1000000); end;');
144 m := Ceil(Length(p_debug)/c);
145 FOR i IN 1..m LOOP
146 execute immediate ('begin dbms' ||
147 '_output' ||
148 '.put_line(''' ||
149 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
150 '''); end;');
151 END LOOP;
152 EXCEPTION
153 WHEN OTHERS THEN
154 NULL;
155 END dbms_debug;
156
157 PROCEDURE println(msg IN VARCHAR2) IS
158 BEGIN
159 IF(debug_mode_on) THEN
160 dbms_debug(msg);
161 END IF;
162 END;
163
167 dbms_debug(' ');
164 PROCEDURE flush IS
165 BEGIN
166 IF(debug_mode_on) THEN
168 END IF;
169 END;
170
171 PROCEDURE printbuf(msg IN VARCHAR2) IS
172 i NUMBER;
173 len NUMBER;
174 BEGIN
175 len := Length(msg);
176 IF(len > 240) THEN
177 FOR i IN 1..len LOOP
178 IF(MOD(i, 70) = 0) THEN
179 dbms_debug(''' ||');
180 dbms_debug('''');
181 END IF;
182 dbms_debug(Substr(msg, i, 1));
183 END LOOP;
184 dbms_debug(' ');
185 ELSE
186 dbms_debug(msg);
187 END IF;
188 EXCEPTION
189 WHEN OTHERS THEN
190 message('printbuf: ' || Sqlerrm);
191 message('while printing: ' || msg);
192 RAISE;
193 END;
194
195 /* ------------------------------------------------------------ */
196 /* who information */
197 /* ------------------------------------------------------------ */
198 FUNCTION customer_mode RETURN BOOLEAN IS
199 BEGIN
200 IF (who_mode = 'customer_data') THEN
201 RETURN TRUE;
202 ELSIF(who_mode = 'seed_data') THEN
203 RETURN FALSE;
204 ELSE
205 message('bad session mode:' || who_mode);
206 message('use set_session_mode to specify');
207 RAISE bad_parameter;
208 END IF;
209 END;
210
211 FUNCTION created_by_f RETURN NUMBER IS
212 BEGIN
213 IF (customer_mode) THEN
214 RETURN 0;
215 ELSE
216 RETURN 1;
217 END IF;
218 END;
219
220 FUNCTION creation_date_f RETURN DATE IS
221 BEGIN
222 IF (customer_mode) THEN
223 RETURN Sysdate;
224 ELSE
225 RETURN To_date('01011980', 'MMDDYYYY');
226 END IF;
227 END;
228
229 FUNCTION last_update_date_f RETURN DATE IS
230 BEGIN
231 RETURN creation_date_f;
232 END;
233
234 FUNCTION last_updated_by_f RETURN NUMBER IS
235 BEGIN
236 RETURN created_by_f;
237 END;
238
239 FUNCTION last_update_login_f RETURN NUMBER IS
240 BEGIN
241 RETURN 0;
242 END;
243
244 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
245 BEGIN
246 IF (session_mode NOT IN ('customer_data', 'seed_data')) THEN
247 message('bad mode:'|| session_mode);
248 message('valid values are: customer_data, seed_data');
249 RAISE bad_parameter;
250 END IF;
251 who_mode := session_mode;
252
253 last_update_login_i := last_update_login_f;
254 last_update_date_i := last_update_date_f;
255 last_updated_by_i := last_updated_by_f;
256 creation_date_i := creation_date_f;
257 created_by_i := created_by_f;
258 END;
259
260 /* ------------------------------------------------- */
261 /* to_string Functions */
262 /* ------------------------------------------------- */
263 FUNCTION to_string(flexfield IN flexfield_type)
264 RETURN VARCHAR2
265 IS
266 sbuf VARCHAR2(2000);
267 BEGIN
268 sbuf := '[Flexfield:' ||
269 ' APP=' || flexfield.application_id ||
270 ' CODE=' || flexfield.flex_code ||
271 ']';
272 RETURN sbuf;
273 END;
274
275 FUNCTION to_string(flexfield IN flexfield_type,
276 structure IN structure_type)
277 RETURN VARCHAR2
278 IS
279 sbuf VARCHAR2(2000);
280 BEGIN
281 sbuf := '[Structure:' ||
282 ' ' || to_string(flexfield) ||
283 ' STRUCT=' || structure.structure_name ||
284 ' SNUM=' || structure.structure_number ||
285 ']';
286 RETURN sbuf;
287 END;
288
289 FUNCTION to_string(flexfield IN flexfield_type,
290 structure IN structure_type,
291 segment IN segment_type)
292 RETURN VARCHAR2
293 IS
294 sbuf VARCHAR2(2000);
295 BEGIN
296 sbuf := '[Segment:' ||
297 ' ' || to_string(flexfield, structure) ||
298 ' SEG=' || segment.segment_name ||
299 ' COL=' || segment.column_name ||
300 ']';
301 RETURN sbuf;
302 END;
303
304 /* -------------------------------------------------------- */
305 /* default check Functions */
306 /* -------------------------------------------------------- */
307
308 FUNCTION is_default(val IN VARCHAR2)
309 RETURN BOOLEAN
310 IS
311 BEGIN
312 IF(val = fnd_api.g_miss_char) THEN
313 RETURN TRUE;
314 ELSE
315 RETURN FALSE;
316 END IF;
317 END;
318
319 FUNCTION is_default(val IN NUMBER) RETURN BOOLEAN
320 IS
321 BEGIN
322 IF(val = fnd_api.g_miss_num) THEN
323 RETURN TRUE;
324 ELSE
325 RETURN FALSE;
326 END IF;
327 END;
328
329 PROCEDURE make_default(val IN OUT nocopy VARCHAR2)
330 IS
331 BEGIN
332 val := fnd_api.g_miss_char;
333 END;
334
335 PROCEDURE make_default(val IN OUT nocopy NUMBER)
336 IS
337 BEGIN
338 val := fnd_api.g_miss_num;
339 END;
340
341 PROCEDURE set_value(val IN VARCHAR2, var OUT nocopy VARCHAR2)
342 IS
343 BEGIN
344 if ( val <> fnd_api.g_miss_char ) then
345 var := val;
346 else
347 var := NULL;
348 end if;
349 END;
350
351 PROCEDURE set_value(val IN NUMBER, var OUT nocopy NUMBER)
352 IS
353 BEGIN
354 if ( val <> fnd_api.g_miss_num ) then
355 var := val;
356 else
357 var := NULL;
358 end if;
359 END;
360
361 /* ---------------------------------------------------- */
362 /* Validate Functions */
366 BEGIN
363 /* ---------------------------------------------------- */
364 PROCEDURE check_instantiated(flexfield IN flexfield_type)
365 IS
367 IF(flexfield.instantiated IS NULL) THEN
368 message('cannot perform operation on uninstantiated flexfield');
369 message('use new_flexfield or find_flexfield to describe a flexfield');
370 RAISE bad_parameter;
371 ELSIF(flexfield.instantiated = 'N') THEN
372 message('cannot perform operation on uninstantiated flexfield');
373 message('use register to instantiate a flexfield');
374 RAISE bad_parameter;
375 ELSIF(flexfield.instantiated <> 'Y') THEN
376 message('inconsistent internal state: instantiated=' ||
377 flexfield.instantiated);
378 RAISE bad_parameter;
379 END IF;
380 END;
381
382 /* ---------------------------------------------------------------------- */
383 PROCEDURE check_instantiated(structure IN structure_type)
384 IS
385 BEGIN
386 IF(structure.instantiated IS NULL) THEN
387 message('cannot perform operation on uninstantiated structure');
388 message('use new_structure or find_structure to describe a structure');
389 RAISE bad_parameter;
390 ELSIF(structure.instantiated = 'N') THEN
391 message('cannot perform operation on uninstantiated strucuture');
392 message('use add_structure to instantiate a structure');
393 RAISE bad_parameter;
394 ELSIF(structure.instantiated <> 'Y') THEN
395 message('inconsistent internal state: instantiated=' ||
396 structure.instantiated);
397 RAISE bad_parameter;
398 END IF;
399 END;
400
401 /* ---------------------------------------------------------------------- */
402 PROCEDURE check_instantiated(segment IN segment_type)
403 IS
404 BEGIN
405 IF(segment.instantiated IS NULL) THEN
406 message('cannot perform operation on uninstantiated segment');
407 message('use new_segment or find_segment to describe a segment');
408 RAISE bad_parameter;
409 ELSIF(segment.instantiated = 'N') THEN
410 message('cannot perform operation on uninstantiated strucuture');
411 message('use add_segment to instantiate a segment');
412 RAISE bad_parameter;
413 ELSIF(segment.instantiated <> 'Y') THEN
414 message('inconsistent internal state: instantiated=' ||
415 segment.instantiated);
416 RAISE bad_parameter;
417 END IF;
418 END;
419
420 /* ------------------------------------------------------------ */
421 PROCEDURE validate_column_name(flexfield IN flexfield_type,
422 structure IN structure_type,
423 segment IN segment_type,
424 column_name_in IN VARCHAR2)
425 IS
426 dummy NUMBER;
427 BEGIN
428 IF(NOT do_validation) THEN
429 RETURN;
430 END IF;
431
432 -- check column name in table
433 SELECT NULL
434 INTO dummy
435 FROM fnd_columns c --, fnd_lookups ct
436 WHERE c.application_id = flexfield.table_application_id
437 AND c.table_id = flexfield.table_id
438 AND c.column_name = column_name_in
439 -- AND c.flexfield_application_id = flexfield.application_id
440 -- AND c.flexfield_name = flexfield.flex_code
441 AND c.flexfield_usage_code = 'K'
442 -- AND ct.lookup_type = 'COLUMN_TYPE'
443 -- AND ct.lookup_code = column_type
444 -- check that it is not already in use
445 AND NOT EXISTS (SELECT NULL FROM fnd_id_flex_segments
446 WHERE application_id = flexfield.application_id
447 AND id_flex_code = flexfield.flex_code
448 AND id_flex_num = structure.structure_number
449 AND application_column_name = c.column_name)
450 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 message('validate column name: ' || column_name_in);
455 message(to_string(flexfield, structure, segment));
456 RAISE;
457 END;
458
459 /* ------------------------------------------------------------ */
460 PROCEDURE validate_valueset(flexfield IN flexfield_type,
461 structure IN structure_type,
462 segment IN segment_type,
463 vset IN NUMBER)
464 IS
465 application_column_size_i fnd_columns.width%TYPE;
466 application_column_type_i fnd_columns.column_type%TYPE;
467 dummy NUMBER;
468 BEGIN
469 IF(NOT do_validation) THEN
470 RETURN;
471 END IF;
472
473 BEGIN
474 SELECT width, column_type
475 INTO application_column_size_i,
476 application_column_type_i
477 FROM fnd_columns
478 WHERE application_id = flexfield.table_application_id
479 AND table_id = flexfield.table_id
480 AND column_name = segment.column_name
481 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
482 EXCEPTION
483 WHEN no_data_found THEN
484 message('error looking up table information');
485 RAISE;
486 END;
487
488 SELECT NULL
489 INTO dummy
490 FROM fnd_flex_value_sets v,
491 fnd_flex_validation_tables t
492 WHERE v.flex_value_set_id = vset
493 AND v.flex_value_set_id = t.flex_value_set_id (+)
494 AND (flexfield.allow_id_value_sets = 'Y'
495 OR (flexfield.allow_id_value_sets = 'N' AND t.id_column_name IS NULL))
496 AND v.flex_value_set_name NOT LIKE '$FLEX$.%'
497 AND ((application_column_type_i IN ('C', 'V')
498 OR v.validation_type = 'U'
499 OR application_column_type_i = Nvl(t.id_column_type,
500 Decode(v.format_type,
501 'M', 'N', 'T', 'D', 't', 'D', 'X', 'D', 'Y', 'D', 'Z', 'D',
502 v.format_type))))
503 AND (application_column_type_i = 'D'
507 WHERE application_id = flexfield.application_id
504 OR application_column_size_i >= Nvl(id_column_size, maximum_size))
505 AND (validation_type <> 'D' OR EXISTS
506 (SELECT NULL FROM fnd_id_flex_segments s
508 AND id_flex_code = flexfield.flex_code
509 AND id_flex_num = structure.structure_number
510 AND s.flex_value_set_id = v.parent_flex_value_set_id
511 AND segment_num < segment.segment_number))
512 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
513 EXCEPTION
514 WHEN OTHERS THEN
515 message('validate_valueset: ' || vset);
516 message('segment num: ' || segment.segment_number);
517 RAISE;
518 END;
519
520
521 /* ------------------------------------------------------------ */
522 PROCEDURE validate_flexfield(
523 flexfield IN OUT nocopy flexfield_type)
524 IS
525 BEGIN
526 fnd_flex_types.validate_yes_no_flag(flexfield.dynamic_inserts);
527 fnd_flex_types.validate_yes_no_flag(flexfield.allow_id_value_sets);
528 fnd_flex_types.validate_yes_no_flag(flexfield.index_flag);
529 EXCEPTION
530 WHEN OTHERS THEN
531 message('invalid parameter in flexfield');
532 message('validate flexfield : Error ' ||Sqlerrm);
533 message(to_string(flexfield));
534 RAISE bad_parameter;
535 END;
536
537
538
539 /* ------------------------------------------------------------ */
540 PROCEDURE validate_structure(
541 flexfield IN flexfield_type,
542 structure IN OUT nocopy structure_type)
543 IS
544 BEGIN
545 IF (is_default(structure.structure_number))
546 OR (structure.structure_number IS NULL) THEN
547 message('validate structure : you must pass a structure number');
548 RAISE bad_parameter;
549 END IF;
550
551 IF (is_default(structure.structure_name))
552 OR (structure.structure_name IS NULL) THEN
553 message('validate structure : you must pass a structure name');
554 RAISE bad_parameter;
555 END IF;
556
557 IF is_default(structure.description) THEN
558 structure.description := NULL;
559 END IF;
560
561 IF is_default(structure.view_name) THEN
562 structure.view_name := NULL;
563 END IF;
564
565 fnd_flex_types.validate_yes_no_flag(structure.freeze_flag);
566
567 fnd_flex_types.validate_yes_no_flag(structure.enabled_flag);
568
569 IF (is_default(structure.segment_separator))
570 OR (structure.segment_separator IS NULL) THEN
571 message('validate structure : you must pass a segment_separator');
572 RAISE bad_parameter;
573 END IF;
574
575 fnd_flex_types.validate_yes_no_flag(structure.cross_val_flag);
576
577 fnd_flex_types.validate_yes_no_flag(structure.freeze_rollup_flag);
578
579 fnd_flex_types.validate_yes_no_flag(structure.dynamic_insert_flag);
580
581 fnd_flex_types.validate_yes_no_flag(structure.shorthand_enabled_flag);
582
583 IF is_default(structure.shorthand_prompt) THEN
584 structure.shorthand_prompt := NULL;
585 END IF;
586
587 IF is_default(structure.shorthand_length) THEN
588 structure.shorthand_length := NULL;
589 END IF;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 message('invalid parameter in structure');
594 message('validate structure : Error ' ||Sqlerrm);
595 message(to_string(flexfield,structure));
596 RAISE bad_parameter;
597 END;
598
599 /* ------------------------------------------------------------ */
600 PROCEDURE validate_segment(
601 flexfield IN flexfield_type,
602 structure IN structure_type,
603 segment IN OUT nocopy segment_type)
604 IS
605 BEGIN
606 IF (is_default(segment.segment_name))
607 OR (segment.segment_name IS NULL) THEN
608 message('validate segment : you must pass a segment name');
609 RAISE bad_parameter;
610 END IF;
611
612 IF is_default(segment.description) THEN
613 segment.description := NULL;
614 END IF;
615
616 validate_column_name(flexfield,structure,segment,segment.column_name);
617
618 IF (is_default(segment.segment_number))
619 OR (segment.segment_number IS NULL) THEN
620 message('validate segment : you must pass a segment number');
621 RAISE bad_parameter;
622 END IF;
623
624 fnd_flex_types.validate_yes_no_flag(segment.enabled_flag);
625
626 fnd_flex_types.validate_yes_no_flag(segment.displayed_flag);
627
628 fnd_flex_types.validate_yes_no_flag(segment.indexed_flag);
629
630 IF is_default(segment.value_set_id) THEN
631 segment.value_set_id := NULL;
632 segment.value_set_name := NULL;
633 ELSIF (segment.value_set_id IS NOT NULL) THEN
634 validate_valueset(flexfield,structure,segment,segment.value_set_id);
635 END IF;
636
637 IF is_default(segment.default_type) THEN
638 segment.default_type := NULL;
639 ELSIF segment.default_type IS NOT NULL THEN
640 fnd_flex_types.validate_default_type(segment.default_type);
641 END IF;
642
643 IF is_default(segment.default_value) THEN
644 segment.default_value := NULL;
645 END IF;
646
647 fnd_flex_types.validate_yes_no_flag(segment.required_flag);
648
649 fnd_flex_types.validate_yes_no_flag(segment.security_flag);
650
651 IF is_default(segment.range_code) THEN
652 segment.range_code := NULL;
653 ELSIF segment.range_code IS NOT NULL THEN
654 fnd_flex_types.validate_range_code(segment.range_code);
655 END IF;
656
657 IF (is_default(segment.display_size))
661 END IF;
658 OR (segment.display_size IS NULL) THEN
659 message('validate segment : you must pass a display_size');
660 RAISE bad_parameter;
662
663 IF (is_default(segment.description_size))
664 OR (segment.description_size IS NULL) THEN
665 message('validate segment : you must pass a description_size');
666 RAISE bad_parameter;
667 END IF;
668
669 IF (is_default(segment.concat_size))
670 OR (segment.concat_size IS NULL) THEN
671 message('validate segment : you must pass a concat_size');
672 RAISE bad_parameter;
673 END IF;
674
675 IF (is_default(segment.lov_prompt))
676 OR (segment.lov_prompt IS NULL) THEN
677 message('validate segment : you must pass a lov_prompt');
678 RAISE bad_parameter;
679 END IF;
680
681 IF (is_default(segment.window_prompt))
682 OR (segment.window_prompt IS NULL) THEN
683 message('validate segment : you must pass a window_prompt');
684 RAISE bad_parameter;
685 END IF;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 message('invalid parameter in segment');
690 message('validate segment : Error ' ||Sqlerrm);
691 message(to_string(flexfield,structure,segment));
692 RAISE bad_parameter;
693 END;
694
695 /* ------------------------------------------------------------ */
696 FUNCTION check_duplicate_structure(
697 flexfield IN flexfield_type,
698 structure_name IN fnd_id_flex_structures_vl.id_flex_structure_name%TYPE)
699 RETURN NUMBER
700 IS
701 row_count NUMBER;
702 BEGIN
703 --
704 -- Check for duplicate structure name.
705 --
706 SELECT count(*)
707 INTO row_count
708 FROM fnd_id_flex_structures_vl
709 WHERE application_id = flexfield.application_id
710 AND id_flex_code = flexfield.flex_code
711 AND id_flex_structure_name = structure_name
712 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
713 RETURN row_count;
714 END;
715
716 /* ------------------------------------------------------------ */
717 FUNCTION check_duplicate_segment(
718 flexfield IN flexfield_type,
719 structure IN structure_type,
720 segment_name IN fnd_id_flex_segments_vl.segment_name%TYPE)
721 RETURN NUMBER
722 IS
723 row_count NUMBER;
724 BEGIN
725 --
726 -- Check for duplicate segment name.
727 --
728 SELECT count(*)
729 INTO row_count
730 FROM fnd_id_flex_segments_vl v
731 WHERE application_id = flexfield.application_id
732 AND id_flex_code = flexfield.flex_code
733 AND id_flex_num = structure.structure_number
734 AND v.segment_name = check_duplicate_segment.segment_name
735 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
736 RETURN row_count;
737 END;
738
739 /* ---------------------------------------------------------------------- */
740 /* Name to Id conversion functions */
741 /* ---------------------------------------------------------------------- */
742 FUNCTION application_id_f(application_short_name_in IN VARCHAR2)
743 RETURN fnd_application.application_id%TYPE
744 IS
745 application_id_ret fnd_application.application_id%TYPE;
746 BEGIN
747 SELECT application_id
748 INTO application_id_ret
749 FROM fnd_application
750 WHERE application_short_name = application_short_name_in
751 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
752
753 RETURN application_id_ret;
754 EXCEPTION
755 WHEN OTHERS THEN
756 IF(application_short_name_in IS NULL) THEN
757 message('must specify appl_short_name');
758 ELSE
759 message('error locating application id');
760 message('appl_short_name:' || application_short_name_in);
761 END IF;
762 RAISE bad_parameter;
763 END;
764
765 /* ------------------------------------------------------------ */
766 FUNCTION table_id_f(application_id_in IN fnd_tables.application_id%TYPE,
767 table_name_in IN VARCHAR2)
768 RETURN fnd_tables.table_id%TYPE
769 IS
770 table_id_ret fnd_tables.table_id%TYPE;
771 BEGIN
772 SELECT table_id
773 INTO table_id_ret
774 FROM fnd_tables
775 WHERE table_name = table_name_in
776 AND application_id = application_id_in
777 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
778 RETURN table_id_ret;
779 EXCEPTION
780 WHEN no_data_found THEN
781 message('bad table name:' || table_name_in);
782 RAISE;
783 END;
784
785 /* ------------------------------------------------------------ */
786 FUNCTION value_set_id_f(value_set_name IN VARCHAR2)
787 RETURN fnd_flex_value_sets.flex_value_set_id%TYPE
788 IS
789 value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
790 BEGIN
791 IF(value_set_name IS NULL) THEN
792 RETURN NULL;
793 END IF;
794
795 SELECT flex_value_set_id
796 INTO value_set_id
797 FROM fnd_flex_value_sets
798 WHERE flex_value_set_name = value_set_name
799 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
800 RETURN value_set_id;
801 EXCEPTION
802 WHEN no_data_found THEN
803 message('bad valueset name:' || value_set_name);
804 RAISE;
805 END;
806
807
808
809 /* ---------------------------------------------------------------------- */
810 /* Set - Unset Column Functions */
814 IS
811 /* ---------------------------------------------------------------------- */
812 PROCEDURE set_structure_column(flexfield IN flexfield_type,
813 structure_column IN VARCHAR2)
815 BEGIN
816 IF(structure_column IS NOT NULL) THEN
817 UPDATE fnd_columns SET
818 flexfield_usage_code = 'S',
819 -- flexfield_application_id = flexfield.application_id,
820 -- flexfield_name = flexfield.flex_code,
821 last_update_date = last_update_date_i,
822 last_updated_by = last_updated_by_i,
823 last_update_login = last_update_login_i
824 WHERE application_id = flexfield.table_application_id
825 AND table_id = flexfield.table_id
826 AND column_name = structure_column
827 AND flexfield_usage_code = 'N'
828 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
829 IF(SQL%notfound) THEN
830 message('could not acquire column');
831 message('structure column=' || structure_column);
832 RAISE no_data_found;
833 END IF;
834 END IF;
835 END;
836
837 /* --------------------------------------------------------------- */
838 PROCEDURE unset_structure_column(flexfield IN flexfield_type)
839 IS
840 BEGIN
841 IF(flexfield.structure_column IS NOT NULL) THEN
842 UPDATE fnd_columns SET
843 flexfield_usage_code = 'N',
844 -- flexfield_application_id = flexfield.application_id,
845 -- flexfield_name = flexfield.flex_code,
846 last_update_date = last_update_date_i,
847 last_updated_by = last_updated_by_i,
848 last_update_login = last_update_login_i
849 WHERE application_id = flexfield.table_application_id
850 AND table_id = flexfield.table_id
851 AND column_name = flexfield.structure_column
852 AND flexfield_usage_code = 'S'
853 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
854 IF(SQL%notfound) THEN
855 message('could not acquire column');
856 message('structure column=' || flexfield.structure_column);
857 RAISE no_data_found;
858 END IF;
859 END IF;
860 END;
861
862 /* ---------------------------------------------------------------------- */
863 PROCEDURE set_id_column(flexfield IN flexfield_type,
864 id_column IN VARCHAR2)
865 IS
866 BEGIN
867 UPDATE fnd_columns SET
868 flexfield_usage_code = 'I',
869 -- flexfield_application_id = flexfield.application_id,
870 -- flexfield_name = flexfield.flex_code,
871 last_update_date = last_update_date_i,
872 last_updated_by = last_updated_by_i,
873 last_update_login = last_update_login_i
874 WHERE application_id = flexfield.table_application_id
875 AND table_id = flexfield.table_id
876 AND column_name = id_column
877 AND flexfield_usage_code = 'N'
878 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
879 IF(SQL%notfound) THEN
880 message('could not acquire column');
881 message('unique id column=' || id_column);
882 RAISE no_data_found;
883 END IF;
884 END;
885
886 /* ---------------------------------------------------------------------- */
887 PROCEDURE unset_id_column(flexfield IN flexfield_type)
888 IS
889 BEGIN
890 UPDATE fnd_columns SET
891 flexfield_usage_code = 'N',
892 -- flexfield_application_id = flexfield.application_id,
893 -- flexfield_name = flexfield.flex_code,
894 last_update_date = last_update_date_i,
895 last_updated_by = last_updated_by_i,
896 last_update_login = last_update_login_i
897 WHERE application_id = flexfield.table_application_id
898 AND table_id = flexfield.table_id
899 AND column_name = flexfield.unique_id_column
900 AND flexfield_usage_code = 'I'
901 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
902 IF(SQL%notfound) THEN
903 message('could not acquire column');
904 message('unique id column=' || flexfield.unique_id_column);
905 RAISE no_data_found;
906 END IF;
907 END;
908
909
910 /* ---------------------------------------------------------------------- */
911 /* Compose Functions */
912 /* ---------------------------------------------------------------------- */
913 -- compose(a,b) = ( b ? b : a )
914 -- dcnt is count of number of defaults used
915 FUNCTION compose(orig IN VARCHAR2,
916 chng IN VARCHAR2,
917 dcnt IN OUT nocopy NUMBER) RETURN VARCHAR2
918 IS
919 BEGIN
920 IF(chng = fnd_api.g_miss_char) THEN
921 dcnt := dcnt + 1;
922 RETURN orig;
923 ELSE
924 RETURN chng;
925 END IF;
926 END;
927
928 /* ---------------------------------------------------------------------- */
929 -- dcnt is count of number of defaults used
930 FUNCTION compose(orig IN NUMBER,
931 chng IN NUMBER,
932 dcnt IN OUT nocopy NUMBER) RETURN NUMBER
933 IS
934 BEGIN
935 IF(chng = fnd_api.g_miss_num) THEN
936 dcnt := dcnt + 1;
937 RETURN orig;
938 ELSE
939 RETURN chng;
940 END IF;
941 END;
942
943 /* ---------------------------------------------------------------------- */
944 -- for (almost) every x in F: F.x = compose(F1.x, F2.x)
945
946 FUNCTION compose(f1 flexfield_type,
947 f2 flexfield_type,
948 cnt OUT nocopy NUMBER) RETURN flexfield_type
949 IS
950 f flexfield_type;
951 dcnt NUMBER := 0;
952 BEGIN
953 f.instantiated := f1.instantiated;
954 -- PK
955 f.application_id := f1.application_id;
959 f.flex_title := compose(f1.flex_title, f2.flex_title, dcnt);
956 f.appl_short_name := f1.appl_short_name;
957 f.flex_code := f1.flex_code;
958
960 f.description := compose(f1.description, f2.description, dcnt);
961 f.table_appl_short_name := compose(f1.table_appl_short_name, f2.table_appl_short_name, dcnt);
962 f.table_name := compose(f1.table_name, f2.table_name, dcnt);
963 f.concatenated_segs_view_name := compose(f1.concatenated_segs_view_name, f2.concatenated_segs_view_name, dcnt);
964 f.unique_id_column := compose(f1.unique_id_column, f2.unique_id_column, dcnt);
965 f.structure_column := compose(f1.structure_column, f2.structure_column, dcnt);
966 f.dynamic_inserts := compose(f1.dynamic_inserts, f2.dynamic_inserts, dcnt);
967 f.allow_id_value_sets := compose(f1.allow_id_value_sets, f2.allow_id_value_sets, dcnt);
968 f.index_flag := compose(f1.index_flag, f2.index_flag, dcnt);
969 f.concat_seg_len_max := compose(f1.concat_seg_len_max, f2.concat_seg_len_max, dcnt);
970 f.concat_len_warning := compose(f1.concat_len_warning, f2.concat_len_warning, dcnt);
971
972 f.table_application_id := compose(f1.table_application_id, f2.table_application_id, dcnt);
973 f.table_id := compose(f1.table_id, f2.table_id, dcnt);
974
975 cnt := dcnt;
976 RETURN f;
977 END;
978
979 /* ---------------------------------------------------------------------- */
980 FUNCTION compose(f1 flexfield_type,
981 f2 flexfield_type) RETURN flexfield_type
982 IS
983 cnt NUMBER := 0;
984 BEGIN
985 RETURN compose(f1, f2, cnt);
986 END;
987
988 /* ---------------------------------------------------------------------- */
989 FUNCTION compose(s1 structure_type,
990 s2 structure_type,
991 cnt OUT nocopy NUMBER) RETURN structure_type
992 IS
993 s structure_type;
994 dcnt NUMBER := 0;
995 BEGIN
996 s.instantiated := s1.instantiated;
997 -- PK
998 s.structure_number := s1.structure_number;
999
1000 s.structure_code := compose(s1.structure_code, s2.structure_code, dcnt);
1001 s.structure_name := compose(s1.structure_name, s2.structure_name, dcnt);
1002 s.description := compose(s1.description, s2.description, dcnt);
1003 s.view_name := compose(s1.view_name, s2.view_name, dcnt);
1004 s.freeze_flag := compose(s1.freeze_flag, s2.freeze_flag, dcnt);
1005 s.enabled_flag := compose(s1.enabled_flag, s2.enabled_flag, dcnt);
1006 s.segment_separator := compose(s1.segment_separator, s2.segment_separator, dcnt);
1007 s.cross_val_flag := compose(s1.cross_val_flag, s2.cross_val_flag, dcnt);
1008 s.freeze_rollup_flag := compose(s1.freeze_rollup_flag, s2.freeze_rollup_flag, dcnt);
1009 s.dynamic_insert_flag := compose(s1.dynamic_insert_flag, s2.dynamic_insert_flag, dcnt);
1010 s.shorthand_enabled_flag := compose(s1.shorthand_enabled_flag, s2.shorthand_enabled_flag, dcnt);
1011 s.shorthand_prompt := compose(s1.shorthand_prompt, s2.shorthand_prompt, dcnt);
1012 s.shorthand_length := compose(s1.shorthand_length, s2.shorthand_length, dcnt);
1013
1014 cnt := dcnt;
1015 RETURN s;
1016 END;
1017
1018 /* ---------------------------------------------------------------------- */
1019 FUNCTION compose(s1 structure_type,
1020 s2 structure_type) RETURN structure_type
1021 IS
1022 cnt NUMBER := 0;
1023 BEGIN
1024 RETURN compose(s1, s2, cnt);
1025 END;
1026
1027
1028 /* ---------------------------------------------------------------------- */
1029 FUNCTION compose(s1 segment_type,
1030 s2 segment_type,
1031 cnt OUT nocopy NUMBER) RETURN segment_type
1032 IS
1033 s segment_type;
1034 dcnt NUMBER := 0;
1035 BEGIN
1036 s.instantiated := s1.instantiated;
1037 -- PK
1038 s.column_name := s1.column_name;
1039
1040 s.segment_name := compose(s1.segment_name, s2.segment_name, dcnt);
1041 s.description := compose(s1.description, s2.description, dcnt);
1042 s.segment_number := compose(s1.segment_number, s2.segment_number, dcnt);
1043 s.enabled_flag := compose(s1.enabled_flag, s2.enabled_flag, dcnt);
1044 s.displayed_flag := compose(s1.displayed_flag, s2.displayed_flag, dcnt);
1045 s.indexed_flag := compose(s1.indexed_flag, s2.indexed_flag, dcnt);
1046 s.value_set_id := compose(s1.value_set_id, s2.value_set_id, dcnt);
1047 s.value_set_name := compose(s1.value_set_name, s2.value_set_name, dcnt);
1048 s.default_type := compose(s1.default_type, s2.default_type, dcnt);
1049 s.default_value := compose(s1.default_value, s2.default_value, dcnt);
1050 s.required_flag := compose(s1.required_flag, s2.required_flag, dcnt);
1051 s.security_flag := compose(s1.security_flag, s2.security_flag, dcnt);
1052
1053 s.range_code := compose(s1.range_code, s2.range_code, dcnt);
1054 s.display_size := compose(s1.display_size, s2.display_size, dcnt);
1055 s.description_size := compose(s1.description_size, s2.description_size, dcnt);
1056 s.concat_size := compose(s1.concat_size, s2.concat_size, dcnt);
1057 s.lov_prompt := compose(s1.lov_prompt, s2.lov_prompt, dcnt);
1058 s.window_prompt := compose(s1.window_prompt, s2.window_prompt, dcnt);
1059 s.runtime_property_function := compose(s1.runtime_property_function,
1060 s2.runtime_property_function, dcnt);
1061 s.additional_where_clause := compose(s1.additional_where_clause,
1062 s2.additional_where_clause, dcnt);
1063 cnt := dcnt;
1064 RETURN s;
1065 END;
1066
1070 IS
1067 /* ---------------------------------------------------------------------- */
1068 FUNCTION compose(s1 segment_type,
1069 s2 segment_type) RETURN segment_type
1071 cnt NUMBER := 0;
1072 BEGIN
1073 RETURN compose(s1, s2, cnt);
1074 END;
1075
1076 /* ---------------------------------------------------------------------- */
1077 /* Internal Structure Add */
1078 /* ---------------------------------------------------------------------- */
1079 PROCEDURE add_structure_internal
1080 (appl_short_name IN VARCHAR2,
1081 flex_code IN VARCHAR2,
1082
1083 structure_code IN VARCHAR2,
1084 structure_title IN VARCHAR2,
1085 description IN VARCHAR2,
1086 view_name IN VARCHAR2,
1087 freeze_flag IN VARCHAR2 DEFAULT 'N',
1088 enabled_flag IN VARCHAR2 DEFAULT 'N',
1089 segment_separator IN VARCHAR2,
1090 cross_val_flag IN VARCHAR2,
1091 freeze_rollup_flag IN VARCHAR2 DEFAULT 'N',
1092 dynamic_insert_flag IN VARCHAR2 DEFAULT 'N',
1093 shorthand_enabled_flag IN VARCHAR2 DEFAULT 'N',
1094 shorthand_prompt IN VARCHAR2,
1095 shorthand_length IN NUMBER,
1096 flex_num IN NUMBER)
1097 IS
1098 application_id_i fnd_id_flexs.application_id%TYPE;
1099 rowid_i VARCHAR2(64);
1100 BEGIN
1101 message_init;
1102 application_id_i := application_id_f(appl_short_name);
1103
1104 fnd_id_flex_structures_pkg.insert_row(
1105 X_ROWID => rowid_i,
1106 X_APPLICATION_ID => application_id_i,
1107 X_ID_FLEX_CODE => flex_code,
1108 X_ID_FLEX_NUM => flex_num,
1109 X_ID_FLEX_STRUCTURE_CODE => structure_code,
1110 X_CONCATENATED_SEGMENT_DELIMIT => segment_separator,
1111 X_CROSS_SEGMENT_VALIDATION_FLA => cross_val_flag,
1112 X_DYNAMIC_INSERTS_ALLOWED_FLAG => dynamic_insert_flag,
1113 X_ENABLED_FLAG => enabled_flag,
1114 X_FREEZE_FLEX_DEFINITION_FLAG => freeze_flag,
1115 X_FREEZE_STRUCTURED_HIER_FLAG => freeze_rollup_flag,
1116 X_SHORTHAND_ENABLED_FLAG => shorthand_enabled_flag,
1117 X_SHORTHAND_LENGTH => shorthand_length,
1118 X_STRUCTURE_VIEW_NAME => view_name,
1119 X_ID_FLEX_STRUCTURE_NAME => structure_title,
1120 X_DESCRIPTION => description,
1121 X_SHORTHAND_PROMPT => shorthand_prompt,
1122 X_CREATION_DATE => creation_date_i,
1123 X_CREATED_BY => created_by_i,
1124 X_LAST_UPDATE_DATE => last_update_date_i,
1125 X_LAST_UPDATED_BY => last_updated_by_i,
1126 X_LAST_UPDATE_LOGIN => last_update_login_i);
1127
1128 --
1129 -- Copied from FNDFFMIS.STRUCT_PRIVATE.populate_workflow_processes.
1130 --
1131 IF (application_id_i <> 101 OR
1132 flex_code <> 'GL#' OR
1133 flex_num = 101) THEN
1134 NULL;
1135 ELSE
1136 INSERT INTO FND_FLEX_WORKFLOW_PROCESSES
1137 (APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, WF_ITEM_TYPE,
1138 WF_PROCESS_NAME, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1139 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN)
1140 SELECT application_id_i, flex_code,
1141 flex_num, FWP.WF_ITEM_TYPE,
1142 'DEFAULT_ACCOUNT_GENERATION',
1143 last_update_date_i, last_updated_by_i, creation_date_i,
1144 created_by_i, last_update_login_i
1145 FROM FND_FLEX_WORKFLOW_PROCESSES FWP
1146 WHERE FWP.APPLICATION_ID = application_id_i
1147 AND FWP.ID_FLEX_CODE = flex_code
1148 AND FWP.ID_FLEX_NUM = 101
1149 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1150 END IF;
1151
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 message('add structure: ' || Sqlerrm);
1155 RAISE;
1156 END;
1157
1158
1159 /* ---------------------------------------------------------------------- */
1160 FUNCTION has_defaults(flexfield IN flexfield_type)
1161 RETURN BOOLEAN
1162 IS
1163 cnt NUMBER;
1164 f flexfield_type;
1165 BEGIN
1166 f := compose(flexfield, flexfield, cnt);
1167 IF(cnt > 0) THEN
1168 RETURN TRUE;
1169 ELSE
1170 RETURN FALSE;
1171 END IF;
1172 END;
1173
1174 /* ------------------------------------------------------------ */
1175 /* public functions */
1176 /* ------------------------------------------------------------ */
1177 /* ------------------------------------------------------------ */
1178 /* FLEXFIELD RELATED FUNCTIONS */
1179 /* ------------------------------------------------------------ */
1180 FUNCTION flexfield_exists(appl_short_name IN VARCHAR2,
1181 flex_code IN VARCHAR2 DEFAULT NULL,
1182 flex_title IN VARCHAR2 DEFAULT NULL)
1183 RETURN BOOLEAN
1184 IS
1185 cnt NUMBER;
1186 application_id_i fnd_id_flexs.application_id%TYPE;
1187 BEGIN
1188 message_init;
1189 application_id_i := application_id_f(appl_short_name);
1190 SELECT COUNT(*)
1191 INTO cnt
1192 FROM fnd_id_flexs
1193 WHERE application_id = application_id_i
1194 AND (id_flex_code = flex_code
1195 OR id_flex_name = flex_title)
1196 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1197 IF(cnt > 0) THEN
1198 RETURN TRUE;
1199 ELSE
1200 RETURN FALSE;
1201 END IF;
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 message('flexfield_exists: ' || Sqlerrm);
1205 RAISE;
1206 END;
1207
1208 /* ---------------------------------------------------------------------- */
1209 FUNCTION new_flexfield
1210 (appl_short_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1211 flex_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1215 table_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1212 flex_title IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1213 description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1214 table_appl_short_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1216 unique_id_column IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1217 structure_column IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1218 dynamic_inserts IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1219 allow_id_value_sets IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1220 index_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1221 concat_seg_len_max IN NUMBER DEFAULT fnd_api.g_miss_num,
1222 concat_len_warning IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1223 concatenated_segs_view_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char)
1224 RETURN flexfield_type
1225 IS
1226 flexfield flexfield_type;
1227 BEGIN
1228 message_init;
1229 flexfield.instantiated := 'N';
1230 set_value(appl_short_name, flexfield.appl_short_name);
1231 flexfield.application_id := application_id_f(appl_short_name);
1232 set_value(flex_code, flexfield.flex_code);
1233 set_value(flex_title, flexfield.flex_title);
1234 set_value(description, flexfield.description);
1235 set_value(table_appl_short_name, flexfield.table_appl_short_name);
1236 set_value(table_name, flexfield.table_name);
1237 set_value(unique_id_column, flexfield.unique_id_column);
1238 set_value(structure_column, flexfield.structure_column);
1239 set_value(dynamic_inserts, flexfield.dynamic_inserts);
1240 set_value(allow_id_value_sets, flexfield.allow_id_value_sets);
1241 set_value(index_flag, flexfield.index_flag);
1242 set_value(concat_seg_len_max, flexfield.concat_seg_len_max);
1243 set_value(concat_len_warning, flexfield.concat_len_warning);
1244 set_value(concatenated_segs_view_name, flexfield.concatenated_segs_view_name);
1245
1246 IF (NOT is_default(table_appl_short_name)) THEN
1247 flexfield.table_application_id := application_id_f(table_appl_short_name);
1248 IF (NOT is_default(table_name)) THEN
1249 flexfield.table_id := table_id_f(flexfield.table_application_id,
1250 table_name);
1251 ELSE
1252 make_default(flexfield.table_id);
1253 END IF;
1254 ELSE
1255 make_default(flexfield.table_application_id);
1256 END IF;
1257 last_flexfield := flexfield;
1258 println('created flexfield: ' || to_string(flexfield));
1259 RETURN flexfield;
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 message('new flexfield: ' || Sqlerrm);
1263 RAISE;
1264 END;
1265
1266 /* ---------------------------------------------------------------------- */
1267 FUNCTION find_flexfield(appl_short_name IN VARCHAR2,
1268 flex_code IN VARCHAR2)
1269 RETURN flexfield_type
1270 IS
1271 flexfield flexfield_type;
1272 BEGIN
1273 message_init;
1274 flexfield.application_id := application_id_f(appl_short_name);
1275 flexfield.flex_code := flex_code;
1276
1277 SELECT 'Y',
1278 find_flexfield.appl_short_name,
1279 id_flex_code,
1280 id_flex_name,
1281 idf.description,
1282 tap.application_short_name,
1283 application_table_name,
1284 concatenated_segs_view_name,
1285 unique_id_column_name,
1286 set_defining_column_name structure_column,
1287 dynamic_inserts_feasible_flag,
1288 allow_id_valuesets,
1289 index_flag,
1290 maximum_concatenation_len,
1291 concatenation_len_warning,
1292 idf.application_id,
1293 tap.application_id,
1294 tab.table_id
1295 INTO flexfield
1296 FROM fnd_id_flexs idf, fnd_application tap, fnd_tables tab
1297 WHERE idf.application_id = flexfield.application_id
1298 AND id_flex_code = flexfield.flex_code
1299 AND idf.table_application_id = tap.application_id
1300 AND tab.application_id = table_application_id
1301 AND tab.table_name = application_table_name
1302 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1303
1304 last_flexfield := flexfield;
1305 RETURN flexfield;
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 message('find_flexfield: ' || Sqlerrm);
1309 RAISE;
1310 END;
1311
1312
1313 /* ---------------------------------------------------------------------- */
1314 --
1315 -- create a new key flex
1316 --
1317 PROCEDURE register(flexfield IN OUT nocopy flexfield_type,
1318 enable_columns IN VARCHAR2 DEFAULT 'Y')
1319 IS
1320 BEGIN
1321 message_init;
1322
1323 IF(has_defaults(flexfield)) THEN
1324 message('some data missing in flexfield');
1325 RAISE bad_parameter;
1326 END IF;
1327
1328 validate_flexfield(flexfield);
1329 INSERT
1330 INTO fnd_id_flexs (application_id,
1331 id_flex_code,
1332 id_flex_name,
1333
1334 table_application_id,
1335 application_table_name,
1336 concatenated_segs_view_name,
1337 allow_id_valuesets,
1338 dynamic_inserts_feasible_flag,
1339 index_flag,
1340 unique_id_column_name,
1341 description,
1342 application_table_type,
1343 set_defining_column_name,
1344 maximum_concatenation_len,
1345 concatenation_len_warning,
1346
1347 last_update_date,
1348 last_updated_by,
1349 creation_date,
1350 created_by,
1351 last_update_login)
1352 VALUES (flexfield.application_id,
1353 flexfield.flex_code,
1354 flexfield.flex_title,
1355
1356 flexfield.table_application_id,
1357 flexfield.table_name,
1358 flexfield.concatenated_segs_view_name,
1359 flexfield.allow_id_value_sets,
1360 flexfield.dynamic_inserts,
1361 flexfield.index_flag,
1362 flexfield.unique_id_column,
1363 flexfield.description,
1364 NULL,
1365 flexfield.structure_column,
1366 flexfield.concat_seg_len_max,
1367 flexfield.concat_len_warning,
1368
1369 last_update_date_i,
1370 last_updated_by_i,
1371 creation_date_i,
1372 created_by_i,
1373 last_update_login_i);
1374
1375 BEGIN
1376 --
1377 -- If the user has specified a set defining column
1378 -- mark the column.
1379 --
1380 set_structure_column(flexfield, flexfield.structure_column);
1381 --
1382 -- Mark the unique ID column
1383 --
1384 set_id_column(flexfield, flexfield.unique_id_column);
1385
1386 --
1387 -- Mark all unmarked "SEGMENT" columns as potential
1388 -- Key flexfield segment columns. These can later be
1389 -- changed in the COLUMN block.
1390 --
1391 IF(enable_columns = 'Y') THEN
1392 UPDATE fnd_columns SET
1393 flexfield_usage_code = 'K',
1394 -- flexfield_application_id = flexfield.application_id,
1395 -- flexfield_name = flexfield.flex_code,
1396 last_update_date = last_update_date_i,
1397 last_updated_by = last_updated_by_i,
1398 last_update_login = last_update_login_i
1399 WHERE application_id = flexfield.table_application_id
1400 AND table_id = flexfield.table_id
1401 AND column_name like 'SEGMENT%'
1402 AND rtrim(column_name, '0123456789') = 'SEGMENT'
1403 AND flexfield_usage_code = 'N'
1404 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1405 END IF;
1406 --
1407 -- Create a default structure (# 101) for the key
1408 -- flexfield.
1409 --
1410 add_structure_internal(appl_short_name => flexfield.appl_short_name,
1411 flex_code => flexfield.flex_code,
1412 structure_code => REPLACE(Upper(flexfield.flex_title),
1413 ' ','_'),
1414 structure_title => flexfield.flex_title,
1415 description => NULL,
1416 view_name => NULL,
1417 freeze_flag => 'Y',
1418 enabled_flag => 'Y',
1419 segment_separator => '.',
1420 cross_val_flag => 'N',
1421 freeze_rollup_flag => 'N',
1422 dynamic_insert_flag => 'N',
1423 shorthand_enabled_flag => 'N',
1424 shorthand_prompt => NULL,
1425 shorthand_length => NULL,
1426 flex_num => 101);
1427 END;
1428 flexfield.instantiated := 'Y';
1429
1430 last_flexfield := flexfield;
1431 println('added flexfield: ' || to_string(flexfield));
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 message('register: ' || Sqlerrm);
1435 message(to_string(flexfield));
1436 RAISE;
1437 END register;
1438
1439 /* ---------------------------------------------------------------------- */
1440 PROCEDURE enable_column(flexfield IN flexfield_type,
1441 column_name IN VARCHAR2,
1442 enable_flag IN VARCHAR2 DEFAULT 'Y')
1443 IS
1444 BEGIN
1445 message_init;
1446 check_instantiated(flexfield);
1447
1448 IF(enable_flag = 'Y') THEN
1449 UPDATE fnd_columns SET
1450 flexfield_usage_code = 'K',
1451 -- flexfield_application_id = flexfield.application_id,
1452 -- flexfield_name = flexfield.flex_code,
1453 last_update_date = last_update_date_i,
1454 last_updated_by = last_updated_by_i,
1455 last_update_login = last_update_login_i
1456 WHERE application_id = flexfield.table_application_id
1457 AND table_id = flexfield.table_id
1458 AND fnd_columns.column_name = enable_column.column_name
1459 AND flexfield_usage_code = 'N'
1460 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1461 IF(SQL%notfound) THEN
1462 message('could not acquire column');
1463 message('column name=' || column_name);
1464 message(to_string(flexfield));
1465 RAISE no_data_found;
1466 END IF;
1467 ELSE
1468 UPDATE fnd_columns SET
1469 flexfield_usage_code = 'N',
1470 -- flexfield_application_id = NULL,
1471 -- flexfield_name = NULL,
1472 last_update_date = last_update_date_i,
1473 last_updated_by = last_updated_by_i,
1474 last_update_login = last_update_login_i
1475 WHERE application_id = flexfield.table_application_id
1476 AND table_id = flexfield.table_id
1477 AND fnd_columns.column_name = enable_column.column_name
1478 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1479 -- AND flexfield_application_id = flexfield.application_id
1480 -- AND flexfield_name = flexfield.flex_code
1481 IF(SQL%notfound) THEN
1482 message('could not release column');
1483 message('column=' || column_name);
1484 RAISE no_data_found;
1485 END IF;
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 message('enable_column: ' || Sqlerrm);
1491 message(to_string(flexfield));
1492 message('column name=' || column_name);
1493 RAISE;
1494 END;
1495
1496 /* ---------------------------------------------------------------------- */
1497 PROCEDURE enable_columns_like(flexfield IN flexfield_type,
1498 pattern IN VARCHAR2,
1499 enable_flag IN VARCHAR2 DEFAULT 'Y')
1500 IS
1501 BEGIN
1502 message_init;
1503 check_instantiated(flexfield);
1504
1505 RAISE bad_parameter;
1506 END;
1507
1508 /* ---------------------------------------------------------------------- */
1509 --
1510 -- create a new flexfield qualifier
1511 --
1512 PROCEDURE add_flex_qualifier(flexfield IN flexfield_type,
1513
1514 qualifier_name IN VARCHAR2,
1515 prompt IN VARCHAR2,
1516 description IN VARCHAR2,
1517 global_flag IN VARCHAR2 DEFAULT 'N',
1518 required_flag IN VARCHAR2 DEFAULT 'N',
1519 unique_flag IN VARCHAR2 DEFAULT 'N')
1520 IS
1521 BEGIN
1522 message_init;
1523 check_instantiated(flexfield);
1524
1525 INSERT
1526 INTO fnd_segment_attribute_types(application_id,
1527 id_flex_code,
1528 segment_attribute_type,
1529 global_flag,
1530 required_flag,
1531 unique_flag,
1532 segment_prompt,
1533 description,
1534
1535 creation_date,
1536 created_by,
1537 last_update_date,
1538 last_updated_by,
1539 last_update_login)
1540 VALUES(flexfield.application_id,
1541 flexfield.flex_code,
1542 qualifier_name,
1543 global_flag,
1544 required_flag,
1545 unique_flag,
1546 prompt,
1547 description,
1548
1549 creation_date_i,
1550 created_by_i,
1551 last_update_date_i,
1552 last_updated_by_i,
1553 last_update_login_i);
1554
1555 -- If there are any segments defined
1556 -- populate fnd_segment_attribute_values table.
1557 -- Similar code exists in FNDFFIIF.SEG.other_inserts.
1558
1559 INSERT INTO fnd_segment_attribute_values
1560 (application_id,
1561 id_flex_code,
1562 id_flex_num,
1563 application_column_name,
1564 segment_attribute_type,
1565 attribute_value,
1566
1567 creation_date,
1568 created_by,
1569 last_update_date,
1570 last_updated_by,
1571 last_update_login)
1572 SELECT ifsg.application_id,
1573 ifsg.id_flex_code,
1574 ifsg.id_flex_num,
1575 ifsg.application_column_name,
1576 add_flex_qualifier.qualifier_name,
1577 add_flex_qualifier.global_flag,
1578
1579 creation_date_i,
1580 created_by_i,
1581 last_update_date_i,
1582 last_updated_by_i,
1583 last_update_login_i
1584 FROM fnd_id_flex_segments ifsg
1585 WHERE application_id = flexfield.application_id
1586 AND id_flex_code = flexfield.flex_code
1587 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1588
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 message('add_flex_qualifier: ' || Sqlerrm);
1592 message(to_string(flexfield));
1593 message('flex_qualifier=' || qualifier_name);
1594 RAISE;
1595 END;
1596
1597 FUNCTION delete_flex_qualifier(flexfield IN flexfield_type,
1598 qualifier_name IN VARCHAR2,
1599 recursive_delete IN BOOLEAN DEFAULT TRUE)
1600 RETURN NUMBER
1601 IS
1602 CURSOR vat_cur(p_application_id IN NUMBER,
1603 p_id_flex_code IN VARCHAR2,
1604 p_segment_attribute_type IN VARCHAR2)
1605 IS
1606 SELECT value_attribute_type
1607 FROM fnd_value_attribute_types vat
1608 WHERE vat.application_id = p_application_id
1609 AND vat.id_flex_code = p_id_flex_code
1610 AND vat.segment_attribute_type = p_segment_attribute_type
1611 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1612 l_return NUMBER := 0;
1613 l_number NUMBER := 0;
1614 l_recursive_delete BOOLEAN := Nvl(recursive_delete, FALSE);
1615 l_vc2 varchar2(32000);
1616 BEGIN
1617 message_init;
1618 check_instantiated(flexfield);
1619
1620 --
1621 -- Check flexfield qualifier exists, otherwise return 0.
1622 --
1623 BEGIN
1624 SELECT 0
1625 INTO l_return
1626 FROM fnd_segment_attribute_types sat
1627 WHERE sat.application_id = flexfield.application_id
1628 AND sat.id_flex_code = flexfield.flex_code
1629 AND sat.segment_attribute_type = qualifier_name
1630 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1631 EXCEPTION
1632 WHEN no_data_found THEN
1633 message('Flexfield qualifier does not exist.');
1634 RETURN(0);
1635 WHEN OTHERS THEN
1636 message('SELECT FROM SAT is failed ' || chr_newline ||
1637 'SQLERRM : ' || Sqlerrm);
1638 RETURN(-1);
1639 END;
1640
1641 --
1642 -- Check flexfield qualifier is not used by some segment, otherwise return -1.
1643 --
1644 BEGIN
1645 SELECT s.application_id || '/' ||
1646 s.id_flex_code || '/' ||
1647 s.id_flex_num || '/' ||
1648 s.application_column_name || '/' ||
1649 s.segment_name
1650 INTO l_vc2
1651 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
1652 WHERE s.application_id = sav.application_id
1653 AND s.id_flex_code = sav.id_flex_code
1654 AND s.id_flex_num = sav.id_flex_num
1655 AND s.application_column_name = sav.application_column_name
1656 AND s.enabled_flag = 'Y'
1657 AND sav.application_id = flexfield.application_id
1658 AND sav.id_flex_code = flexfield.flex_code
1659 AND sav.attribute_value = 'Y'
1660 AND sav.segment_attribute_type = qualifier_name
1661 AND ROWNUM < 2
1662 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1663
1664 message('Delete is not allowed. Flexfield qualifier <' || qualifier_name || '> is used by segment: <' || l_vc2 || '>');
1665 RETURN(-1);
1666 EXCEPTION
1667 WHEN no_data_found THEN
1668 null;
1669 END;
1670
1671 l_number := 0;
1672 FOR vat_rec IN vat_cur(flexfield.application_id, flexfield.flex_code, qualifier_name) LOOP
1676 IF ((NOT l_recursive_delete) AND (l_number > 0)) THEN
1673 l_number := l_number + 1;
1674 END LOOP;
1675
1677 message('There are segment qualifiers for this flexfield ' ||
1678 'qualifier, and you passed recursive_delete => FALSE.');
1679 RETURN(-1);
1680 END IF;
1681
1682 --
1683 -- Now delete segment qualifiers.
1684 --
1685 FOR vat_rec IN vat_cur(flexfield.application_id, flexfield.flex_code, qualifier_name) LOOP
1686 l_number := delete_seg_qualifier(flexfield,
1687 qualifier_name,
1688 vat_rec.value_attribute_type);
1689 IF (l_number = -1) THEN
1690 RETURN (-1);
1691 END IF;
1692 l_return := l_return + l_number;
1693 END LOOP;
1694
1695 --
1696 -- Delete from SAV
1697 --
1698 DELETE FROM fnd_segment_attribute_values sav
1699 WHERE sav.application_id = flexfield.application_id
1700 AND sav.id_flex_code = flexfield.flex_code
1701 AND sav.segment_attribute_type = qualifier_name
1702 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1703
1704 l_return := l_return + SQL%rowcount;
1705
1706 --
1707 -- Delete from SAT
1708 --
1709 DELETE FROM fnd_segment_attribute_types sat
1710 WHERE sat.application_id = flexfield.application_id
1711 AND sat.id_flex_code = flexfield.flex_code
1712 AND sat.segment_attribute_type = qualifier_name
1713 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1714
1715 l_return := l_return + SQL%rowcount;
1716 RETURN(l_return);
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 message('Top level error : ' || Sqlerrm);
1720 RETURN(-1);
1721 END delete_flex_qualifier;
1722
1723 FUNCTION fill_segment_attribute_values
1724 RETURN NUMBER
1725 IS
1726 l_return NUMBER := 0;
1727 BEGIN
1728 message_init;
1729
1730 INSERT INTO fnd_segment_attribute_values
1731 (application_id,
1732 id_flex_code,
1733 id_flex_num,
1734 application_column_name,
1735 segment_attribute_type,
1736 attribute_value,
1737
1738 creation_date,
1739 created_by,
1740 last_update_date,
1741 last_updated_by,
1742 last_update_login)
1743 SELECT ifsg.application_id,
1744 ifsg.id_flex_code,
1745 ifsg.id_flex_num,
1746 ifsg.application_column_name,
1747 sat.segment_attribute_type,
1748 sat.global_flag,
1749
1750 creation_date_i,
1751 created_by_i,
1752 last_update_date_i,
1753 last_updated_by_i,
1754 last_update_login_i
1755 FROM fnd_id_flex_segments ifsg,
1756 fnd_segment_attribute_types sat
1757 WHERE sat.application_id = ifsg.application_id
1758 AND sat.id_flex_code = ifsg.id_flex_code
1759 AND NOT exists
1760 (SELECT NULL
1761 FROM fnd_segment_attribute_values sav
1762 WHERE sav.application_id = ifsg.application_id
1763 AND sav.id_flex_code = ifsg.id_flex_code
1764 AND sav.id_flex_num = ifsg.id_flex_num
1765 AND sav.application_column_name = ifsg.application_column_name
1766 AND sav.segment_attribute_type = sat.segment_attribute_type)
1767 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1768 l_return := SQL%rowcount;
1769 RETURN(l_return);
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 message('Top level error : ' || Sqlerrm);
1773 RETURN(-1);
1774 END fill_segment_attribute_values;
1775
1776
1777 /* ---------------------------------------------------------------------- */
1778 --
1779 -- create a new segment qualifier
1780 --
1781 PROCEDURE add_seg_qualifier(flexfield IN flexfield_type,
1782 flex_qualifier IN VARCHAR2,
1783
1784 qualifier_name IN VARCHAR2,
1785 prompt IN VARCHAR2,
1786 description IN VARCHAR2,
1787 derived_column IN VARCHAR2,
1788 quickcode_type IN VARCHAR2,
1789 default_value IN VARCHAR2)
1790 IS
1791 dummy NUMBER;
1792 l_rowid VARCHAR2(64);
1793 BEGIN
1794 message_init;
1795 check_instantiated(flexfield);
1796
1797 -- Check flex_qualifier is a valid qualifier.
1798 BEGIN
1799 SELECT 1
1800 INTO dummy
1801 FROM dual
1802 WHERE EXISTS
1803 (SELECT 1
1804 FROM fnd_segment_attribute_types sat
1805 WHERE sat.application_id = flexfield.application_id
1806 AND sat.id_flex_code = flexfield.flex_code
1807 AND sat.segment_attribute_type = flex_qualifier)
1808 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1809 EXCEPTION
1810 WHEN no_data_found THEN
1811 message('flexfield qualifier is not valid');
1812 RAISE;
1813 END;
1814
1815
1816 -- Check derived_column is registered in fnd_columns and has usage code 'N'
1817 BEGIN
1818 SELECT 1
1819 INTO dummy
1820 FROM dual
1821 WHERE EXISTS
1822 (SELECT 1
1823 FROM fnd_columns c
1824 WHERE c.application_id = flexfield.table_application_id
1825 AND c.table_id = flexfield.table_id
1826 AND c.column_name = derived_column
1827 AND c.flexfield_usage_code = 'N')
1828 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1829 EXCEPTION
1830 WHEN no_data_found THEN
1834 END;
1831 message('derived column is either not registered ' ||
1832 'or already in use.');
1833 RAISE;
1835
1836 -- Check that quickcode_type and default_value are valid.
1837 BEGIN
1838 SELECT 1
1839 INTO dummy
1840 FROM dual
1841 WHERE EXISTS
1842 (SELECT 1
1843 FROM fnd_lookups
1844 WHERE lookup_type = quickcode_type
1845 AND lookup_code = default_value
1846 AND enabled_flag = 'Y'
1847 AND ( (start_date_active IS NULL)
1848 OR (start_date_active <= sysdate))
1849 AND ( (end_date_active IS NULL)
1850 OR (end_date_active >= sysdate)))
1851 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1852 EXCEPTION
1853 WHEN no_data_found THEN
1854 message('quickcode_type, default_value ' ||
1855 'pair either is not valid, disabled, or expired.');
1856 RAISE;
1857 END;
1858
1859 -- Use fnd_val_attribute_types_pkg to make insert.
1860 FND_VAL_ATTRIBUTE_TYPES_PKG.INSERT_ROW(
1861 X_ROWID => l_rowid,
1862 X_APPLICATION_ID => flexfield.application_id,
1863 X_ID_FLEX_CODE => flexfield.flex_code,
1864 X_SEGMENT_ATTRIBUTE_TYPE => flex_qualifier,
1865 X_VALUE_ATTRIBUTE_TYPE => qualifier_name,
1866 X_REQUIRED_FLAG => 'Y',
1867 X_APPLICATION_COLUMN_NAME => derived_column,
1868 X_DESCRIPTION => description,
1869 X_DEFAULT_VALUE => default_value,
1870 X_LOOKUP_TYPE => quickcode_type,
1871 X_DERIVATION_RULE_CODE => 'G12',
1872 X_DERIVATION_RULE_VALUE1 => 'N',
1873 X_DERIVATION_RULE_VALUE2 => 'Y',
1874 X_PROMPT => prompt,
1875 X_CREATION_DATE => creation_date_i,
1876 X_CREATED_BY => created_by_i,
1877 X_LAST_UPDATE_DATE => last_update_date_i,
1878 X_LAST_UPDATED_BY => last_updated_by_i,
1879 X_LAST_UPDATE_LOGIN => last_update_login_i);
1880
1881 -- Mark the qualifier column in fnd_columns.
1882 UPDATE fnd_columns
1883 SET flexfield_usage_code = 'Q',
1884 last_update_date = last_update_date_i,
1885 last_updated_by = last_updated_by_i,
1886 last_update_login = last_update_login_i
1887 WHERE application_id = flexfield.table_application_id
1888 AND table_id = flexfield.table_id
1889 AND column_name = derived_column
1890 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1891
1892 --
1893 -- Insert into FND_FLEX_VALIDATION_QUALIFIERS
1894 --
1895 INSERT INTO fnd_flex_validation_qualifiers (flex_value_set_id,
1896 id_flex_application_id,
1897 id_flex_code,
1898 segment_attribute_type,
1899 value_attribute_type,
1900 assignment_date)
1901 SELECT DISTINCT
1902 ifsg.flex_value_set_id,
1903 flexfield.application_id,
1904 flexfield.flex_code,
1905 flex_qualifier,
1906 qualifier_name,
1907 SYSDATE
1908 FROM fnd_segment_attribute_values sav,
1909 fnd_id_flex_segments ifsg
1910 WHERE sav.application_id = flexfield.application_id
1911 AND sav.id_flex_code = flexfield.flex_code
1912 AND sav.segment_attribute_type = flex_qualifier
1913 AND sav.attribute_value = 'Y'
1914 AND ifsg.application_id = sav.application_id
1915 AND ifsg.id_flex_code = sav.id_flex_code
1916 AND ifsg.id_flex_num = sav.id_flex_num
1917 AND ifsg.application_column_name = sav.application_column_name
1918 AND ifsg.flex_value_set_id IS NOT NULL
1919 AND ifsg.enabled_flag = 'Y'
1920 AND NOT EXISTS
1921 (SELECT NULL
1922 FROM fnd_flex_validation_qualifiers q
1923 WHERE q.flex_value_set_id = ifsg.flex_value_set_id
1924 AND q.id_flex_application_id = flexfield.application_id
1925 AND q.id_flex_code = flexfield.flex_code
1926 AND q.segment_attribute_type = flex_qualifier
1927 AND q.value_attribute_type = qualifier_name)
1928 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1929
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932 message('add_seg_qualifier: ' || Sqlerrm);
1933 message(to_string(flexfield));
1934 message('flex_qualifier=' || flex_qualifier);
1935 message('segment_qualifier=' || qualifier_name);
1936 RAISE;
1937 END;
1938
1939
1940 FUNCTION delete_seg_qualifier(flexfield IN flexfield_type,
1941 flex_qualifier IN VARCHAR2,
1942 qualifier_name IN VARCHAR2) RETURN NUMBER
1943 IS
1944 l_return NUMBER := 0;
1945 BEGIN
1946 message_init;
1947 check_instantiated(flexfield);
1948 --
1949 -- Check flexfield qualifier exists, otherwise return 0.
1950 --
1951 BEGIN
1952 SELECT 0
1953 INTO l_return
1954 FROM fnd_segment_attribute_types sat
1955 WHERE sat.application_id = flexfield.application_id
1956 AND sat.id_flex_code = flexfield.flex_code
1957 AND sat.segment_attribute_type = flex_qualifier
1958 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1959 EXCEPTION
1960 WHEN no_data_found THEN
1961 message('Flexfield qualifier does not exist.');
1962 RETURN(0);
1963 WHEN OTHERS THEN
1964 message('SELECT FROM SAT is failed ' || chr_newline ||
1965 'SQLERRM : ' || Sqlerrm);
1966 RETURN(-1);
1967 END;
1968 --
1972 SELECT 0
1969 -- Check segment qualifier exists, otherwise return 0.
1970 --
1971 BEGIN
1973 INTO l_return
1974 FROM fnd_value_attribute_types vat
1975 WHERE vat.application_id = flexfield.application_id
1976 AND vat.id_flex_code = flexfield.flex_code
1977 AND vat.segment_attribute_type = flex_qualifier
1978 AND vat.value_attribute_type = qualifier_name
1979 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1980 EXCEPTION
1981 WHEN no_data_found THEN
1982 message('Segment qualifier does not exist.');
1983 RETURN(0);
1984 WHEN OTHERS THEN
1985 message('SELECT FROM VAT is failed ' || chr_newline ||
1986 'SQLERRM : ' || Sqlerrm);
1987 RETURN(-1);
1988 END;
1989 --
1990 -- Delete from fnd_flex_validation_qualifiers
1991 --
1992 DELETE FROM fnd_flex_validation_qualifiers fvq
1993 WHERE fvq.id_flex_application_id = flexfield.application_id
1994 AND fvq.id_flex_code = flexfield.flex_code
1995 AND fvq.segment_attribute_type = flex_qualifier
1996 AND fvq.value_attribute_type = qualifier_name
1997 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
1998 l_return := SQL%rowcount;
1999
2000 --
2001 -- Delete From VAT_TL table
2002 --
2003 DELETE FROM fnd_val_attribute_types_tl vat
2004 WHERE vat.application_id = flexfield.application_id
2005 AND vat.id_flex_code = flexfield.flex_code
2006 AND vat.segment_attribute_type = flex_qualifier
2007 AND vat.value_attribute_type = qualifier_name
2008 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2009 l_return := l_return + SQL%rowcount;
2010
2011 --
2012 -- Delete from VAT table
2013 --
2014 DELETE FROM fnd_value_attribute_types vat
2015 WHERE vat.application_id = flexfield.application_id
2016 AND vat.id_flex_code = flexfield.flex_code
2017 AND vat.segment_attribute_type = flex_qualifier
2018 AND vat.value_attribute_type = qualifier_name
2019 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2020 l_return := l_return + SQL%rowcount;
2021
2022 RETURN(l_return);
2023 EXCEPTION
2024 WHEN OTHERS THEN
2025 message('Top level error : ' || Sqlerrm);
2026 RETURN(-1);
2027 END delete_seg_qualifier;
2028
2029
2030 /* ------------------------------------------------------------ */
2031 PROCEDURE modify_flexfield(original IN flexfield_type,
2032 modified IN flexfield_type)
2033 IS
2034 flexfield flexfield_type;
2035 BEGIN
2036 message_init;
2037 check_instantiated(original);
2038
2039 flexfield := compose(original, modified);
2040
2041 UPDATE fnd_id_flexs SET
2042 id_flex_name = flexfield.flex_title,
2043
2044 table_application_id = flexfield.table_application_id,
2045 application_table_name = flexfield.table_name,
2046 concatenated_segs_view_name = flexfield.concatenated_segs_view_name,
2047 allow_id_valuesets = flexfield.allow_id_value_sets,
2048 dynamic_inserts_feasible_flag = flexfield.dynamic_inserts,
2049 index_flag = flexfield.index_flag,
2050 unique_id_column_name = flexfield.unique_id_column,
2051 description = flexfield.description,
2052 application_table_type = NULL,
2053 set_defining_column_name = flexfield.structure_column,
2054 maximum_concatenation_len = flexfield.concat_seg_len_max,
2055 concatenation_len_warning = flexfield.concat_len_warning,
2056
2057 last_update_date = last_update_date_i,
2058 last_updated_by = last_updated_by_i,
2059 last_update_login = last_update_login_i
2060 WHERE application_id = original.application_id
2061 AND id_flex_code = original.flex_code
2062 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2063
2064 unset_structure_column(original);
2065 set_structure_column(flexfield, flexfield.structure_column);
2066
2067 unset_id_column(original);
2068 set_id_column(flexfield, flexfield.unique_id_column);
2069
2070 last_flexfield := flexfield;
2071 println('modified ' || to_string(original));
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 message(': ' || Sqlerrm);
2076 message(to_string(original));
2077 RAISE;
2078 END;
2079
2080 /* ------------------------------------------------------------ */
2081 PROCEDURE delete_flexfield(appl_short_name IN VARCHAR2,
2082 flex_code IN VARCHAR2)
2083 IS
2084 flexfield flexfield_type;
2085 BEGIN
2086 message_init;
2087 flexfield := find_flexfield(appl_short_name => appl_short_name,
2088 flex_code => flex_code);
2089 delete_flexfield(flexfield);
2090 EXCEPTION
2091 WHEN no_data_found THEN
2092 message('delete_flexfield: Either this flexfield is already deleted');
2093 message('or fnd_tables information is missing.');
2094 RAISE;
2095 WHEN OTHERS THEN
2096 message('delete_flexfield: ' || Sqlerrm);
2097 RAISE;
2098 END;
2099
2100 /* ---------------------------------------------------------------------- */
2101 PROCEDURE delete_flexfield(flexfield IN flexfield_type)
2102 IS
2103 structure structure_type;
2104 BEGIN
2105 message_init;
2106
2107 -- delete the structures
2108 FOR structure_r IN structure_c(flexfield) LOOP
2109 BEGIN
2110 structure := find_structure(flexfield => flexfield,
2114 message('error locating structure');
2111 structure_number => structure_r.structure_number);
2112 EXCEPTION
2113 WHEN OTHERS THEN
2115 message(to_string(flexfield));
2116 message('structure number = ' || structure_r.structure_number);
2117 RAISE;
2118 END;
2119 BEGIN
2120 delete_structure(flexfield => flexfield,
2121 structure => structure);
2122 EXCEPTION
2123 WHEN OTHERS THEN
2124 message('error deleting structure');
2125 message(to_string(flexfield, structure));
2126 RAISE;
2127 END;
2128 END LOOP;
2129
2130 DELETE FROM fnd_val_attribute_types_tl
2131 WHERE application_id = flexfield.application_id
2132 AND id_flex_code = flexfield.flex_code
2133 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2134 DELETE FROM fnd_value_attribute_types
2135 WHERE application_id = flexfield.application_id
2136 AND id_flex_code = flexfield.flex_code
2137 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2138
2139 DELETE FROM fnd_segment_attribute_types
2140 WHERE application_id = flexfield.application_id
2141 AND id_flex_code = flexfield.flex_code
2142 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2143
2144 BEGIN
2145 UPDATE fnd_columns SET
2146 flexfield_usage_code = 'N',
2147 -- flexfield_application_id = NULL,
2148 -- flexfield_name = NULL,
2149 last_update_date = last_update_date_i,
2150 last_updated_by = last_updated_by_i,
2151 last_update_login = last_update_login_i
2152 WHERE application_id = flexfield.table_application_id
2153 AND table_id = flexfield.table_id
2154 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2155 -- AND flexfield_name = flexfield.flex_code
2156 -- AND flexfield_application_id = flexfield.application_id
2157 EXCEPTION
2158 WHEN OTHERS THEN
2159 message('error updating fnd_columns');
2160 RAISE;
2161 END;
2162
2163 DELETE FROM fnd_id_flexs
2164 WHERE application_id = flexfield.application_id
2165 AND id_flex_code = flexfield.flex_code
2166 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2167
2168 DELETE FROM fnd_compiled_id_flexs
2169 WHERE application_id = flexfield.application_id
2170 AND id_flex_code = flexfield.flex_code
2171 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2172
2173 println('deleted flexfield: ' || to_string(flexfield));
2174 EXCEPTION
2175 WHEN no_data_found THEN
2176 NULL;
2177 WHEN OTHERS THEN
2178 message('delete_flexfield: ' || Sqlerrm);
2179 message(to_string(flexfield));
2180 RAISE;
2181 END;
2182
2183 /* ------------------------------------------------------------ */
2184 /* STRUCTURE RELATED FUNCTIONS */
2185 /* ------------------------------------------------------------ */
2186 FUNCTION new_structure(flexfield IN flexfield_type,
2187 structure_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2188 structure_title IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2189 description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2190 view_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2191 freeze_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2192 enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2193 segment_separator IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2194 cross_val_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2195 freeze_rollup_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2196 dynamic_insert_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2197 shorthand_enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2198 shorthand_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2199 shorthand_length IN NUMBER DEFAULT fnd_api.g_miss_num)
2200 RETURN structure_type
2201 IS
2202 structure structure_type;
2203 BEGIN
2204 message_init;
2205 check_instantiated(flexfield);
2206
2207 IF customer_mode THEN
2208 SELECT fnd_id_flex_structures_s.NEXTVAL
2209 INTO structure.structure_number
2210 FROM dual;
2211 ELSE
2212 SELECT NVL(MAX(ifs.id_flex_num),0) + 1
2213 INTO structure.structure_number
2214 FROM fnd_id_flex_structures ifs
2215 WHERE ifs.application_id = flexfield.application_id
2216 AND ifs.id_flex_code = flexfield.flex_code
2217 AND ifs.id_flex_num < 101
2218 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2219 IF structure.structure_number = 101 THEN
2220 message('new structure : Developer defined key flexfield structure number'||
2221 ' cannot be greater than 100. Structure number limit exceeded');
2222 message(to_string(flexfield));
2223 RAISE value_too_large;
2224 END IF;
2225 END IF;
2226
2227 set_value(structure_code, structure.structure_code);
2228 set_value(structure_title, structure.structure_name);
2229 set_value(description, structure.description);
2230 set_value(view_name, structure.view_name);
2231 set_value(freeze_flag, structure.freeze_flag);
2232 set_value(enabled_flag, structure.enabled_flag);
2233 set_value(segment_separator, structure.segment_separator);
2234 set_value(cross_val_flag, structure.cross_val_flag);
2235 set_value(freeze_rollup_flag, structure.freeze_rollup_flag);
2236 set_value(dynamic_insert_flag, structure.dynamic_insert_flag);
2237 set_value(shorthand_enabled_flag, structure.shorthand_enabled_flag);
2238 set_value(shorthand_prompt, structure.shorthand_prompt);
2242
2239 set_value(shorthand_length, structure.shorthand_length);
2240
2241 last_structure := structure;
2243 RETURN structure;
2244
2245 EXCEPTION
2246 WHEN OTHERS THEN
2247 message('new_structure: ' || Sqlerrm);
2248 RAISE;
2249 END;
2250
2251 /* ---------------------------------------------------------------------- */
2252 FUNCTION find_structure(flexfield IN flexfield_type,
2253 structure_code IN VARCHAR2)
2254 RETURN structure_type
2255 IS
2256 structure structure_type;
2257 BEGIN
2258 message_init;
2259 check_instantiated(flexfield);
2260
2261 SELECT 'Y',
2262 id_flex_num,
2263 id_flex_structure_code,
2264 id_flex_structure_name,
2265 description,
2266 structure_view_name,
2267 freeze_flex_definition_flag,
2268 enabled_flag,
2269 concatenated_segment_delimiter,
2270 cross_segment_validation_flag,
2271 freeze_structured_hier_flag,
2272 dynamic_inserts_allowed_flag,
2273 shorthand_enabled_flag,
2274 shorthand_prompt,
2275 shorthand_length
2276 INTO structure
2277 FROM fnd_id_flex_structures_vl
2278 WHERE application_id = flexfield.application_id
2279 AND id_flex_code = flexfield.flex_code
2280 AND id_flex_structure_code = structure_code
2281 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2282 -- find_structure.structure title will give strange errors
2283
2284 last_structure := structure;
2285
2286 RETURN structure;
2287
2288 EXCEPTION
2289 WHEN OTHERS THEN
2290 message('find_structure/title: ' || Sqlerrm);
2291 message(to_string(flexfield));
2292 RAISE;
2293 END;
2294
2295 /* ---------------------------------------------------------------------- */
2296 FUNCTION find_structure(flexfield IN flexfield_type,
2297 structure_number IN NUMBER)
2298 RETURN structure_type
2299 IS
2300 structure structure_type;
2301 BEGIN
2302 message_init;
2303 check_instantiated(flexfield);
2304
2305 SELECT 'Y',
2306 id_flex_num,
2307 id_flex_structure_code,
2308 id_flex_structure_name,
2309 description,
2310 structure_view_name,
2311 freeze_flex_definition_flag,
2312 enabled_flag,
2313 concatenated_segment_delimiter,
2314 cross_segment_validation_flag,
2315 freeze_structured_hier_flag,
2316 dynamic_inserts_allowed_flag,
2317 shorthand_enabled_flag,
2318 shorthand_prompt,
2319 shorthand_length
2320 INTO structure
2321 FROM fnd_id_flex_structures_vl
2322 WHERE application_id = flexfield.application_id
2323 AND id_flex_code = flexfield.flex_code
2324 AND id_flex_num = find_structure.structure_number
2325 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2326
2327 last_structure := structure;
2328
2329 RETURN structure;
2330
2331 EXCEPTION
2332 WHEN OTHERS THEN
2333 message('find_structure/number: ' || Sqlerrm);
2334 message(to_string(flexfield));
2335 RAISE;
2336 END;
2337
2338 /* ---------------------------------------------------------------------- */
2339 FUNCTION add_structure(flexfield IN flexfield_type,
2340 structure_code IN VARCHAR2,
2341 structure_title IN VARCHAR2,
2342 description IN VARCHAR2,
2343 view_name IN VARCHAR2,
2344 freeze_flag IN VARCHAR2 DEFAULT 'N',
2345 enabled_flag IN VARCHAR2 DEFAULT 'N',
2346 segment_separator IN VARCHAR2,
2347 cross_val_flag IN VARCHAR2,
2348 freeze_rollup_flag IN VARCHAR2 DEFAULT 'N',
2349 dynamic_insert_flag IN VARCHAR2 DEFAULT 'N',
2350 shorthand_enabled_flag IN VARCHAR2 DEFAULT 'N',
2351 shorthand_prompt IN VARCHAR2 DEFAULT NULL,
2352 shorthand_length IN NUMBER DEFAULT NULL)
2353 RETURN structure_type
2354 IS
2355 structure structure_type;
2356 BEGIN
2357 message_init;
2358 check_instantiated(flexfield);
2359 structure := new_structure(flexfield => flexfield,
2360 structure_code => structure_code,
2361 structure_title => structure_title,
2362 description => description,
2363 view_name => view_name,
2364 freeze_flag => freeze_flag,
2365 enabled_flag => enabled_flag,
2366 segment_separator => segment_separator,
2367 cross_val_flag => cross_val_flag,
2368 freeze_rollup_flag => freeze_rollup_flag,
2369 dynamic_insert_flag => dynamic_insert_flag,
2370 shorthand_enabled_flag => shorthand_enabled_flag,
2371 shorthand_prompt => shorthand_prompt,
2372 shorthand_length => shorthand_length);
2373 add_structure(flexfield => flexfield,
2374 structure => structure);
2375
2376 RETURN structure;
2377 EXCEPTION
2378 WHEN OTHERS THEN
2379 message('add_structure: ' || Sqlerrm);
2380 message('structure name=' || structure_title);
2381 RAISE;
2382 END;
2383
2384 /* ---------------------------------------------------------------------- */
2385 PROCEDURE add_structure(flexfield IN flexfield_type DEFAULT last_flexfield,
2386 structure IN OUT nocopy structure_type)
2387 IS
2388 BEGIN
2389 message_init;
2390 check_instantiated(flexfield);
2391
2392 IF check_duplicate_structure(flexfield => flexfield,
2393 structure_name => structure.structure_name) <> 0 THEN
2394 message('add_structure : This structure name already exists');
2395 message(to_string(flexfield,structure));
2399 validate_structure(flexfield,structure);
2396 RAISE bad_parameter;
2397 END IF;
2398
2400
2401 add_structure_internal(appl_short_name => flexfield.appl_short_name,
2402 flex_code => flexfield.flex_code,
2403 structure_code => structure.structure_code,
2404 structure_title => structure.structure_name,
2405 description => structure.description,
2406 view_name => structure.view_name,
2407 freeze_flag => structure.freeze_flag,
2408 enabled_flag => structure.enabled_flag,
2409 segment_separator => structure.segment_separator,
2410 cross_val_flag => structure.cross_val_flag,
2411 freeze_rollup_flag => structure.freeze_rollup_flag,
2412 dynamic_insert_flag => structure.dynamic_insert_flag,
2413 shorthand_enabled_flag => structure.shorthand_enabled_flag,
2414 shorthand_prompt => structure.shorthand_prompt,
2415 shorthand_length => structure.shorthand_length,
2416 flex_num => structure.structure_number);
2417
2418 structure.instantiated := 'Y';
2419
2420 last_structure := structure;
2421
2422 println('added structure: ' || to_string(flexfield, structure));
2423 EXCEPTION
2424 WHEN OTHERS THEN
2425 message('add_structure: ' || Sqlerrm);
2426 message(to_string(flexfield, structure));
2427 message('structure name=' || structure.structure_name);
2428 RAISE;
2429 END;
2430
2431 /* ------------------------------------------------------------ */
2432 PROCEDURE modify_structure(flexfield IN flexfield_type,
2433 original IN structure_type,
2434 modified IN structure_type)
2435 IS
2436 structure structure_type;
2437 BEGIN
2438 message_init;
2439 check_instantiated(flexfield);
2440 check_instantiated(original);
2441
2442 IF (check_duplicate_structure(flexfield => flexfield,
2443 structure_name => modified.structure_name) <> 0)
2444 AND (modified.structure_name <> original.structure_name) THEN
2445 message('modify_structure : This structure name already exists');
2446 message(to_string(flexfield,modified));
2447 RAISE bad_parameter;
2448 END IF;
2449
2450 structure := compose(original, modified);
2451 validate_structure(flexfield,structure);
2452
2453 fnd_id_flex_structures_pkg.update_row
2454 (X_APPLICATION_ID => flexfield.application_id,
2455 X_ID_FLEX_CODE => flexfield.flex_code,
2456 X_ID_FLEX_NUM => structure.structure_number,
2457 X_ID_FLEX_STRUCTURE_CODE => structure.structure_code,
2458 X_CONCATENATED_SEGMENT_DELIMIT => structure.segment_separator,
2459 X_CROSS_SEGMENT_VALIDATION_FLA => structure.cross_val_flag,
2460 X_DYNAMIC_INSERTS_ALLOWED_FLAG => structure.dynamic_insert_flag,
2461 X_ENABLED_FLAG => structure.enabled_flag,
2462 X_FREEZE_FLEX_DEFINITION_FLAG => structure.freeze_flag,
2463 X_FREEZE_STRUCTURED_HIER_FLAG => structure.freeze_rollup_flag,
2464 X_SHORTHAND_ENABLED_FLAG => structure.shorthand_enabled_flag,
2465 X_SHORTHAND_LENGTH => structure.shorthand_length,
2466 X_STRUCTURE_VIEW_NAME => structure.view_name,
2467 X_ID_FLEX_STRUCTURE_NAME => structure.structure_name,
2468 X_DESCRIPTION => structure.description,
2469 X_SHORTHAND_PROMPT => structure.shorthand_prompt,
2470 X_LAST_UPDATE_DATE => last_update_date_i,
2471 X_LAST_UPDATED_BY => last_updated_by_i,
2472 X_LAST_UPDATE_LOGIN => last_update_login_i);
2473
2474 last_structure := structure;
2475 println('modified ' || to_string(flexfield, structure));
2476
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479 message('modify_structure: ' || Sqlerrm);
2480 message(to_string(flexfield, original));
2481 message(to_string(flexfield, modified));
2482 RAISE;
2483 END;
2484
2485
2486 /* ---------------------------------------------------------------------- */
2487 PROCEDURE delete_structure(flexfield IN flexfield_type,
2488 structure IN structure_type)
2489 IS
2490 segment segment_type;
2491 BEGIN
2492 message_init;
2493
2494 -- delete the segments
2495 FOR segment_r IN segment_c(flexfield, structure) LOOP
2496 BEGIN
2497 segment := find_segment(flexfield => flexfield,
2498 structure => structure,
2499 segment_name => segment_r.segment_name);
2500 EXCEPTION
2501 WHEN OTHERS THEN
2502 message('error locating segment');
2503 message(to_string(flexfield, structure));
2504 message('segment = ' || segment_r.segment_name);
2505 RAISE;
2506 END;
2507 BEGIN
2508 delete_segment(flexfield => flexfield,
2509 structure => structure,
2510 segment => segment);
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513 message('error deleting segment');
2514 message(to_string(flexfield, structure, segment));
2515 RAISE;
2516 END;
2517 END LOOP;
2518
2519 DELETE FROM fnd_shorthand_flex_aliases
2520 WHERE application_id = flexfield.application_id
2521 AND id_flex_code = flexfield.flex_code
2522 AND id_flex_num = structure.structure_number
2523 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2524
2525 --
2526 -- flexbuilder assignments ??
2527 --
2528
2529 --
2530 -- cross validation stuff
2531 --
2532 DELETE FROM fnd_flex_validation_rules
2533 WHERE application_id = flexfield.application_id
2534 AND id_flex_code = flexfield.flex_code
2538 WHERE application_id = flexfield.application_id
2535 AND id_flex_num = structure.structure_number
2536 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2537 DELETE FROM fnd_flex_vdation_rules_tl
2539 AND id_flex_code = flexfield.flex_code
2540 AND id_flex_num = structure.structure_number
2541 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2542 DELETE FROM fnd_flex_validation_rule_lines
2543 WHERE application_id = flexfield.application_id
2544 AND id_flex_code = flexfield.flex_code
2545 AND id_flex_num = structure.structure_number
2546 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2547 DELETE FROM fnd_flex_include_rule_lines
2548 WHERE application_id = flexfield.application_id
2549 AND id_flex_code = flexfield.flex_code
2550 AND id_flex_num = structure.structure_number
2551 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2552 DELETE FROM fnd_flex_exclude_rule_lines
2553 WHERE application_id = flexfield.application_id
2554 AND id_flex_code = flexfield.flex_code
2555 AND id_flex_num = structure.structure_number
2556 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2557 DELETE FROM fnd_flex_validation_rule_stats
2558 WHERE application_id = flexfield.application_id
2559 AND id_flex_code = flexfield.flex_code
2560 AND id_flex_num = structure.structure_number
2561 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2562
2563 DELETE FROM fnd_id_flex_structures_tl
2564 WHERE application_id = flexfield.application_id
2565 AND id_flex_code = flexfield.flex_code
2566 AND id_flex_num = structure.structure_number
2567 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2568
2569 DELETE FROM fnd_id_flex_structures
2570 WHERE application_id = flexfield.application_id
2571 AND id_flex_code = flexfield.flex_code
2572 AND id_flex_num = structure.structure_number
2573 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2574
2575 DELETE FROM fnd_compiled_id_flex_structs
2576 WHERE application_id = flexfield.application_id
2577 AND id_flex_code = flexfield.flex_code
2578 AND id_flex_num = structure.structure_number
2579 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2580
2581 println('deleted structure: ' || to_string(flexfield, structure));
2582 EXCEPTION
2583 WHEN OTHERS THEN
2584 message('delete_structure: ' || Sqlerrm);
2585 message(to_string(flexfield, structure));
2586 RAISE;
2587 END;
2588
2589 /* ---------------------------------------------------------------------- */
2590 /* SEGMENT RELATED FUNCTIONS */
2591 /* ---------------------------------------------------------------------- */
2592 FUNCTION new_segment
2593 (flexfield IN flexfield_type,
2594 structure IN structure_type,
2595 segment_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2596 description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2597 column_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2598 segment_number IN NUMBER DEFAULT fnd_api.g_miss_num,
2599 enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2600 displayed_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2601 indexed_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2602
2603 /* validation */
2604 value_set IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2605 default_type IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2606 default_value IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2607 required_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2608 security_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2609 range_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2610
2611 /* sizes */
2612 display_size IN NUMBER DEFAULT fnd_api.g_miss_num,
2613 description_size IN NUMBER DEFAULT fnd_api.g_miss_num,
2614 concat_size IN NUMBER DEFAULT fnd_api.g_miss_num,
2615
2616 /* prompts */
2617 lov_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2618 window_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2619 runtime_property_function IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
2620 additional_where_clause IN VARCHAR2 DEFAULT fnd_api.g_miss_char)
2621 RETURN segment_type
2622 IS
2623 segment segment_type;
2624 BEGIN
2625 message_init;
2626 check_instantiated(flexfield);
2627 check_instantiated(structure);
2628
2629
2630 set_value(segment_name, segment.segment_name);
2631 set_value(description, segment.description);
2632 set_value(column_name, segment.column_name);
2633 set_value(segment_number, segment.segment_number);
2634 set_value(enabled_flag, segment.enabled_flag);
2635 set_value(displayed_flag, segment.displayed_flag);
2636 set_value(indexed_flag, segment.indexed_flag);
2637
2638 set_value(value_set, segment.value_set_name);
2639 IF(NOT is_default(value_set)) THEN
2640 segment.value_set_id := value_set_id_f(value_set);
2641 ELSE
2642 make_default(segment.value_set_id);
2643 END IF;
2644 set_value(default_type, segment.default_type);
2645 set_value(default_value, segment.default_value);
2646 set_value(required_flag, segment.required_flag);
2647 set_value(security_flag, segment.security_flag);
2648
2649 set_value(range_code, segment.range_code);
2650 set_value(display_size, segment.display_size);
2651 set_value(description_size, segment.description_size);
2652 set_value(concat_size, segment.concat_size);
2653
2654 set_value(lov_prompt, segment.lov_prompt);
2655 set_value(window_prompt, segment.window_prompt);
2656 set_value(runtime_property_function, segment.runtime_property_function);
2657
2658 set_value(additional_where_clause, segment.additional_where_clause);
2659
2660 last_segment := segment;
2661
2662 RETURN segment;
2663 EXCEPTION
2664 WHEN OTHERS THEN
2665 message('new_segment: ' || Sqlerrm);
2666 message(to_string(flexfield, structure));
2667 message('segment name=' || segment_name);
2668 RAISE;
2669 END;
2670
2671 /* ------------------------------------------------------------ */
2672 FUNCTION find_segment(flexfield IN flexfield_type,
2673 structure IN structure_type,
2674 segment_name IN VARCHAR2)
2675 RETURN segment_type
2676 IS
2677 segment segment_type;
2678 BEGIN
2679 check_instantiated(flexfield);
2680 check_instantiated(structure);
2681
2682 SELECT 'Y' instantiated,
2683 seg.segment_name,
2684 seg.description,
2685 seg.application_column_name,
2686 seg.segment_num,
2687 seg.enabled_flag,
2688 seg.display_flag,
2689 seg.application_column_index_flag,
2690 seg.flex_value_set_id,
2691 NULL,
2692 seg.default_type,
2693 seg.default_value,
2694 seg.runtime_property_function,
2695 seg.additional_where_clause,
2696 seg.required_flag,
2697 seg.security_enabled_flag,
2698 seg.range_code,
2699
2700 seg.display_size,
2701 seg.maximum_description_len,
2702 seg.concatenation_description_len,
2703 seg.form_above_prompt,
2704 seg.form_left_prompt
2705 INTO segment
2706 FROM fnd_id_flex_segments_vl seg
2707 WHERE seg.application_id = flexfield.application_id
2708 AND seg.id_flex_code = flexfield.flex_code
2709 AND seg.id_flex_num = structure.structure_number
2710 AND seg.segment_name = find_segment.segment_name
2711 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2712
2713 last_segment := segment;
2714 RETURN segment;
2715
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 message('find_segment: ' || Sqlerrm);
2719 message('segment_name=' || segment_name);
2720 message(to_string(flexfield, structure));
2721 RAISE;
2722 END;
2723
2724 /* ---------------------------------------------------------------------- */
2725 --
2726 -- add a segment to a structure
2727 --
2728 PROCEDURE add_segment(flexfield IN flexfield_type,
2729 structure IN structure_type,
2730 segment IN OUT nocopy segment_type)
2731 IS
2732 BEGIN
2733 message_init;
2734 check_instantiated(flexfield);
2735 check_instantiated(structure);
2736
2737 IF check_duplicate_segment(flexfield => flexfield,
2738 structure => structure,
2739 segment_name => segment.segment_name) <> 0 THEN
2740 message('add_segment : This segment name already exists');
2741 message(to_string(flexfield,structure,segment));
2742 RAISE bad_parameter;
2743 END IF;
2744
2745 validate_segment(flexfield, structure, segment);
2746
2747 INSERT
2748 INTO fnd_id_flex_segments (application_id,
2749 id_flex_code,
2750 id_flex_num,
2751
2752 application_column_name,
2753 segment_name,
2754 segment_num,
2755 application_column_index_flag,
2756 enabled_flag,
2757 required_flag,
2758 display_flag,
2759 display_size,
2760 security_enabled_flag,
2761 maximum_description_len,
2762 concatenation_description_len,
2763 -- form_left_prompt,
2764 -- form_above_prompt,
2765 -- description,
2766
2767 flex_value_set_id,
2768 range_code,
2769 default_type,
2770 default_value,
2771 runtime_property_function,
2772 additional_where_clause,
2773
2774 creation_date,
2775 created_by,
2776 last_update_date,
2777 last_updated_by,
2778 last_update_login)
2779 VALUES (flexfield.application_id,
2780 flexfield.flex_code,
2781 structure.structure_number,
2782
2783 segment.column_name,
2784 segment.segment_name,
2785 segment.segment_number,
2786 segment.indexed_flag,
2787 segment.enabled_flag,
2788 segment.required_flag,
2789 segment.displayed_flag,
2790 segment.display_size,
2791 segment.security_flag,
2792 segment.description_size,
2793 segment.concat_size,
2794 -- segment.window_prompt,
2795 -- segment.lov_prompt,
2796 -- segment.description,
2797
2798 segment.value_set_id,
2799 segment.range_code,
2800 segment.default_type,
2801 segment.default_value,
2802 segment.runtime_property_function,
2803 segment.additional_where_clause,
2804
2805 creation_date_i,
2806 created_by_i,
2807 last_update_date_i,
2808 last_updated_by_i,
2809 last_update_login_i);
2810
2811
2812 BEGIN
2813 INSERT INTO fnd_segment_attribute_values(id_flex_code,
2814 id_flex_num,
2815 application_column_name,
2816 segment_attribute_type,
2817
2818 creation_date,
2819 created_by,
2820 last_update_date,
2821 last_updated_by,
2822 last_update_login,
2823
2824 attribute_value,
2825 application_id)
2826 SELECT
2827 s.id_flex_code,
2828 s.id_flex_num,
2829 s.application_column_name,
2830 segment_attribute_type,
2831
2832 creation_date_i,
2833 created_by_i,
2834 last_update_date_i,
2835 last_updated_by_i,
2836 last_update_login_i,
2837
2838 t.global_flag,
2839 s.application_id
2840 FROM fnd_id_flex_segments s, fnd_segment_attribute_types t
2841 WHERE s.application_id = flexfield.application_id
2842 AND s.application_column_name = segment.column_name
2843 AND s.id_flex_code = flexfield.flex_code
2844 AND s.id_flex_num = structure.structure_number
2845 AND t.application_id = s.application_id
2846 AND t.id_flex_code = s.id_flex_code
2847 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2848
2849 INSERT INTO fnd_flex_validation_qualifiers (flex_value_set_id,
2850 id_flex_application_id,
2851 id_flex_code,
2852 segment_attribute_type,
2853 value_attribute_type,
2854 assignment_date)
2855 SELECT
2856 segment.value_set_id,
2857 flexfield.application_id,
2858 flexfield.flex_code,
2859 sav.segment_attribute_type,
2860 vat.value_attribute_type,
2861 sysdate
2862 FROM fnd_segment_attribute_values sav,
2863 fnd_value_attribute_types vat
2864 WHERE segment.value_set_id IS NOT NULL
2865 AND segment.enabled_flag = 'Y'
2866 AND sav.application_id = flexfield.application_id
2867 AND sav.id_flex_code = flexfield.flex_code
2868 AND sav.id_flex_num = structure.structure_number
2869 AND sav.application_column_name = segment.column_name
2870 AND sav.attribute_value = 'Y'
2871 AND sav.application_id = vat.application_id
2872 AND sav.id_flex_code = vat.id_flex_code
2873 AND sav.segment_attribute_type = vat.segment_attribute_type
2874 AND NOT EXISTS
2875 (SELECT NULL
2876 FROM fnd_flex_validation_qualifiers q
2877 WHERE q.flex_value_set_id = segment.value_set_id
2878 AND q.id_flex_application_id = flexfield.application_id
2879 AND q.id_flex_code = flexfield.flex_code
2880 AND q.segment_attribute_type = sav.segment_attribute_type
2881 AND q.value_attribute_type = vat.value_attribute_type)
2882 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2883 END;
2884
2885 INSERT INTO fnd_id_flex_segments_tl(application_id,
2886 id_flex_code,
2887 id_flex_num,
2888 application_column_name,
2889 language,
2890 form_above_prompt,
2891 form_left_prompt,
2892 description,
2893
2894 creation_date,
2895 created_by,
2896 last_update_date,
2897 last_updated_by,
2898 last_update_login,
2899
2900 source_lang)
2901 SELECT
2902 flexfield.application_id,
2903 flexfield.flex_code,
2904 structure.structure_number,
2905 segment.column_name,
2906 l.language_code,
2907 segment.lov_prompt,
2908 segment.window_prompt,
2909 segment.description,
2910
2911 creation_date_i,
2912 created_by_i,
2913 last_update_date_i,
2914 last_updated_by_i,
2915 last_update_login_i,
2916 userenv('LANG')
2917 FROM fnd_languages l
2918 WHERE l.installed_flag IN ('I', 'B')
2919 AND NOT EXISTS
2920 (SELECT NULL
2921 FROM fnd_id_flex_segments_tl t
2922 WHERE t.application_id = flexfield.application_id
2923 AND t.id_flex_code = flexfield.flex_code
2924 AND t.id_flex_num = structure.structure_number
2925 AND t.application_column_name = segment.column_name
2926 AND t.language = l.language_code)
2927 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2928
2929
2930
2931 segment.instantiated := 'Y';
2932
2933 last_segment := segment;
2934
2935 println('added segment: ' || to_string(flexfield, structure, segment));
2936 EXCEPTION
2937 WHEN OTHERS THEN
2938 message('add_segment: ' || Sqlerrm);
2939 message(to_string(flexfield, structure, segment));
2940 RAISE;
2941 END add_segment;
2942
2943 /* ---------------------------------------------------------------------- */
2944 --
2945 -- qualifiers are automatiicaly assigned as disabled when the segment
2946 -- is created.
2947 --
2948 PROCEDURE assign_qualifier(flexfield IN flexfield_type,
2949 structure IN structure_type,
2950 segment IN segment_type,
2951 flexfield_qualifier IN VARCHAR2,
2955 message_init;
2952 enable_flag IN VARCHAR2 DEFAULT 'Y')
2953 IS
2954 BEGIN
2956 check_instantiated(flexfield);
2957 check_instantiated(structure);
2958 check_instantiated(segment);
2959
2960 UPDATE fnd_segment_attribute_values SET
2961 attribute_value = enable_flag,
2962 last_update_date = last_update_date_i,
2963 last_updated_by = last_updated_by_i,
2964 last_update_login = last_update_login_i
2965 WHERE application_id = flexfield.application_id
2966 AND id_flex_code = flexfield.flex_code
2967 AND id_flex_num = structure.structure_number
2968 AND application_column_name = segment.column_name
2969 AND segment_attribute_type = flexfield_qualifier
2970 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
2971
2972 INSERT INTO fnd_flex_validation_qualifiers (flex_value_set_id,
2973 id_flex_application_id,
2974 id_flex_code,
2975 segment_attribute_type,
2976 value_attribute_type,
2977 assignment_date)
2978 SELECT
2979 segment.value_set_id,
2980 flexfield.application_id,
2981 flexfield.flex_code,
2982 sav.segment_attribute_type,
2983 vat.value_attribute_type,
2984 sysdate
2985 FROM fnd_segment_attribute_values sav,
2986 fnd_value_attribute_types vat
2987 WHERE segment.value_set_id IS NOT NULL
2988 AND segment.enabled_flag = 'Y'
2989 AND sav.application_id = flexfield.application_id
2990 AND sav.id_flex_code = flexfield.flex_code
2991 AND sav.id_flex_num = structure.structure_number
2992 AND sav.application_column_name = segment.column_name
2993 AND sav.attribute_value = 'Y'
2994 AND sav.application_id = vat.application_id
2995 AND sav.id_flex_code = vat.id_flex_code
2996 AND sav.segment_attribute_type = vat.segment_attribute_type
2997 AND sav.segment_attribute_type = flexfield_qualifier
2998 AND NOT EXISTS
2999 (SELECT NULL
3000 FROM fnd_flex_validation_qualifiers q
3001 WHERE q.flex_value_set_id = segment.value_set_id
3002 AND q.id_flex_application_id = flexfield.application_id
3003 AND q.id_flex_code = flexfield.flex_code
3004 AND q.segment_attribute_type = sav.segment_attribute_type
3005 AND q.value_attribute_type = vat.value_attribute_type)
3006 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3007
3008 EXCEPTION
3009 WHEN OTHERS THEN
3010 message('assign_qualifier: ' || Sqlerrm);
3011 message(to_string(flexfield, structure, segment));
3012 message('flexfield_qualifier=' || flexfield_qualifier);
3013 RAISE;
3014 END;
3015
3016 /* ---------------------------------------------------------------------- */
3017 PROCEDURE modify_segment(flexfield IN flexfield_type,
3018 structure IN structure_type,
3019 original IN segment_type,
3020 modified IN segment_type)
3021 IS
3022 segment segment_type;
3023 BEGIN
3024 check_instantiated(flexfield);
3025 check_instantiated(structure);
3026 check_instantiated(original);
3027
3028 IF (check_duplicate_segment(flexfield => flexfield,
3029 structure => structure,
3030 segment_name => modified.segment_name) <> 0)
3031 AND (modified.segment_name <> original.segment_name) THEN
3032 message('modify_segment : This segment name already exists');
3033 message(to_string(flexfield,structure,modified));
3034 RAISE bad_parameter;
3035 END IF;
3036
3037 segment := compose(original, modified);
3038
3039 -- set flag so that validate segment does not call validate_column_name
3040 -- which will return an error as column_name already exists on a modify - dag
3041
3042 do_validation := FALSE;
3043 validate_segment(flexfield, structure, segment);
3044 do_validation := TRUE;
3045
3046 UPDATE fnd_id_flex_segments SET
3047 -- application_column_name = segment.column_name,
3048 segment_name = segment.segment_name,
3049 segment_num = segment.segment_number,
3050 application_column_index_flag = segment.indexed_flag,
3051 enabled_flag = segment.enabled_flag,
3052 required_flag = segment.required_flag,
3053 display_flag = segment.displayed_flag,
3054 display_size = segment.display_size,
3055 security_enabled_flag = segment.security_flag,
3056 maximum_description_len = segment.description_size,
3057 concatenation_description_len = segment.concat_size,
3058
3059 flex_value_set_id = segment.value_set_id,
3060 range_code = segment.range_code,
3061 default_type = segment.default_type,
3062 default_value = segment.default_value,
3063 runtime_property_function = segment.runtime_property_function,
3064 additional_where_clause = segment.additional_where_clause,
3065
3066 last_update_date = last_update_date_i,
3067 last_updated_by = last_updated_by_i,
3068 last_update_login = last_update_login_i
3069 WHERE application_id = flexfield.application_id
3070 AND id_flex_code = flexfield.flex_code
3071 AND id_flex_num = structure.structure_number
3072 AND application_column_name = original.column_name
3073 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3074
3075 IF(segment.column_name <> original.column_name) THEN
3076 UPDATE fnd_segment_attribute_values SET
3077 application_column_name = segment.column_name,
3078 last_update_date = last_update_date_i,
3079 last_updated_by = last_updated_by_i,
3080 last_update_login = last_update_login_i
3081 WHERE application_id = flexfield.application_id
3082 AND id_flex_code = flexfield.flex_code
3083 AND id_flex_num = structure.structure_number
3084 AND application_column_name = original.column_name
3085 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3086 END IF;
3087
3088 BEGIN
3089 INSERT INTO fnd_flex_validation_qualifiers
3090 (flex_value_set_id,
3091 id_flex_application_id,
3092 id_flex_code,
3093 segment_attribute_type,
3094 value_attribute_type,
3095 assignment_date)
3096 SELECT
3097 segment.value_set_id,
3098 flexfield.application_id,
3099 flexfield.flex_code,
3100 sav.segment_attribute_type,
3101 vat.value_attribute_type,
3102 Sysdate
3103 FROM fnd_segment_attribute_values sav,
3104 fnd_value_attribute_types vat
3105 WHERE segment.value_set_id IS NOT NULL
3106 AND segment.enabled_flag = 'Y'
3107 AND sav.application_id = flexfield.application_id
3108 AND sav.id_flex_code = flexfield.flex_code
3109 AND sav.id_flex_num = structure.structure_number
3110 AND sav.application_column_name = segment.column_name
3111 AND sav.attribute_value = 'Y'
3112 AND sav.application_id = vat.application_id
3113 AND sav.id_flex_code = vat.id_flex_code
3114 AND sav.segment_attribute_type = vat.segment_attribute_type
3115 AND NOT EXISTS
3116 (SELECT NULL FROM fnd_flex_validation_qualifiers q
3117 WHERE q.flex_value_set_id = segment.value_set_id
3118 AND q.id_flex_application_id = flexfield.application_id
3119 AND q.id_flex_code = flexfield.flex_code
3120 AND q.segment_attribute_type = sav.segment_attribute_type
3121 AND q.value_attribute_type = vat.value_attribute_type)
3122 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3123 END;
3124
3125
3126 UPDATE fnd_id_flex_segments_tl SET
3127 application_column_name = segment.column_name,
3128 form_left_prompt = segment.window_prompt,
3129 form_above_prompt = segment.lov_prompt,
3130 description = segment.description,
3131
3132 source_lang = userenv('LANG'),
3133
3134 last_update_date = last_update_date_i,
3135 last_updated_by = last_updated_by_i,
3136 last_update_login = last_update_login_i
3137 WHERE application_id = flexfield.application_id
3138 AND id_flex_code = flexfield.flex_code
3139 AND id_flex_num = structure.structure_number
3140 AND application_column_name = original.column_name
3141 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3142
3143 println('modified ' || to_string(flexfield, structure, original));
3144 println('to ' || to_string(flexfield, structure, segment));
3145
3146 EXCEPTION
3147 WHEN OTHERS THEN
3148 message('modify_segment: ' || Sqlerrm);
3149 message(to_string(flexfield, structure, original));
3150 message(to_string(flexfield, structure, modified));
3151 RAISE;
3152 END;
3153
3154 /* ---------------------------------------------------------------------- */
3155 PROCEDURE delete_segment(flexfield IN flexfield_type,
3156 structure IN structure_type,
3157 segment IN segment_type)
3158 IS
3159 BEGIN
3160 message_init;
3161 --
3162 -- Delete "Flexfield Qualifier - Segment" assignments for this segment.
3163 --
3164 DELETE FROM fnd_segment_attribute_values
3165 WHERE application_id = flexfield.application_id
3166 AND id_flex_code = flexfield.flex_code
3167 AND id_flex_num = structure.structure_number
3168 AND application_column_name = segment.column_name
3169 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3170
3171 --
3172 -- Delete from translation table.
3173 --
3174 DELETE FROM fnd_id_flex_segments_tl
3175 WHERE application_id = flexfield.application_id
3176 AND id_flex_code = flexfield.flex_code
3177 AND id_flex_num = structure.structure_number
3178 AND application_column_name = segment.column_name
3179 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3180
3181 --
3182 -- Delete from base key flexfield segment table.
3183 --
3184 DELETE FROM fnd_id_flex_segments
3185 WHERE application_id = flexfield.application_id
3186 AND id_flex_code = flexfield.flex_code
3187 AND id_flex_num = structure.structure_number
3188 AND segment_name = segment.segment_name
3189 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3190
3191 println('deleted segment: ' || to_string(flexfield, structure, segment));
3192 EXCEPTION
3193 WHEN OTHERS THEN
3194 message('delete_segment: ' || Sqlerrm);
3195 RAISE;
3196 END;
3197
3198
3199 /* ---------------------------------------------------------------------- */
3200 /* Testing */
3201 /* ---------------------------------------------------------------------- */
3202 PROCEDURE test(name IN VARCHAR2)
3203 IS
3204 flexfield flexfield_type;
3205 structure structure_type;
3206 sname VARCHAR2(1000);
3207 BEGIN
3208 println('starting test');
3209 IF(name = 'newflex') THEN
3210 flexfield := new_flexfield(appl_short_name => 'FND',
3211 flex_code => 'RW2',
3212 flex_title => 'Rajiv*s test flex',
3213 description => 'testing key flex API',
3214 table_appl_short_name => 'FND',
3215 table_name => 'AF_FLEX_TEST',
3216 unique_id_column => 'UNIQUE_ID_COLUMN',
3217 structure_column => 'SET_DEFINING_COLUMN',
3218 dynamic_inserts => 'Y',
3219 allow_id_value_sets => 'Y',
3220 concat_seg_len_max => '81',
3221 concat_len_warning => 'len overflow warning',
3222 concatenated_segs_view_name => NULL);
3223 register(flexfield);
3224 println('created new flexfield');
3225 ELSE
3226 flexfield := find_flexfield(appl_short_name => 'FND',
3227 flex_code => 'RW2');
3228
3229 IF(name = 'newstruct') THEN
3230 SELECT 'test' || To_char(MAX(To_number(Substr(id_flex_structure_name, 4))) + 1)
3231 INTO sname
3232 FROM fnd_id_flex_structures_vl
3233 WHERE application_id = 0
3234 AND id_flex_code = 'RW2'
3235 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3236
3237 structure := new_structure(flexfield => flexfield,
3238 structure_title => sname,
3239 description => 'a test structure',
3240 view_name => NULL,
3241 freeze_flag => 'N',
3242 enabled_flag => 'N',
3243 segment_separator => '-',
3244 cross_val_flag => 'N',
3245 shorthand_enabled_flag => 'N');
3246 add_structure(flexfield, structure);
3247 println('added structure');
3248 ELSE
3249 structure := find_structure(flexfield => flexfield,
3250 structure_code => 'test struct1');
3251 IF(name = 'newseg') THEN
3252 NULL;
3253 println('added segment');
3254 ELSE
3255 println('doing nothing');
3256 END IF;
3257 END IF;
3258 END IF;
3259
3260 println('test complete');
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 println(message);
3264 RAISE;
3265 END;
3266
3267 /* ---------------------------------------------------------------------- */
3268 FUNCTION quot(val IN NUMBER) RETURN VARCHAR2 IS
3269 BEGIN
3270 IF(val IS NULL) THEN
3271 RETURN 'NULL';
3272 ELSE
3273 RETURN To_char(val);
3274 END IF;
3275 EXCEPTION
3276 WHEN OTHERS THEN
3277 message('quot/num: ' || Sqlerrm);
3278 RAISE;
3279 END;
3280
3281 /* ---------------------------------------------------------------------- */
3282 FUNCTION quot(str IN VARCHAR2) RETURN VARCHAR2
3283 IS
3284 sout VARCHAR2(2000);
3285 BEGIN
3286 IF(Length(str) > 1500) THEN
3287 message('string overflow');
3288 RAISE bad_parameter;
3289 END IF;
3290
3291 IF(str IS NULL) THEN
3292 sout := 'NULL';
3293 ELSE
3294 -- escape quotes
3295 sout := REPLACE(str, '''', '''''');
3296 -- add surrounding quotes
3297 sout := '''' || sout || '''';
3298 END IF;
3299 RETURN sout;
3300 EXCEPTION
3301 WHEN OTHERS THEN
3302 message('quot/str: ' || Sqlerrm);
3303 RAISE;
3304 END;
3305
3306
3307 /* ---------------------------------------------------------------------- */
3308 /* DUMP FUNCTIONS */
3309 /* ---------------------------------------------------------------------- */
3310 PROCEDURE dump_flexfield(flexfield IN flexfield_type,
3311 recurse IN BOOLEAN DEFAULT TRUE)
3312 IS
3313 CURSOR column_c IS
3314 SELECT column_name
3315 FROM fnd_columns
3316 WHERE application_id = flexfield.table_application_id
3317 AND table_id = flexfield.table_id
3318 -- AND flexfield_application_id = flexfield.application_id
3319 -- AND flexfield_name = flexfield.flex_code
3320 AND flexfield_usage_code = 'K'
3321 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3322 BEGIN
3323 message_init;
3324
3325 printbuf('/* FLEXFIELD */');
3326 printbuf('flexfield := fnd_flex_key_api.new_flexfield(');
3327 printbuf('appl_short_name => ' || quot(flexfield.appl_short_name) || ',');
3328 printbuf('flex_code => ' || quot(flexfield.flex_code) || ',');
3329 printbuf('flex_title => ' || quot(flexfield.flex_title) || ',');
3330 printbuf('description => ' || quot(flexfield.description) || ',');
3331 printbuf('table_appl_short_name => ' || quot(flexfield.table_appl_short_name) || ',');
3332 printbuf('table_name => ' || quot(flexfield.table_name) || ',');
3333 printbuf('unique_id_column => ' || quot(flexfield.unique_id_column) || ',');
3334 printbuf('structure_column => ' || quot(flexfield.structure_column) || ',');
3335 printbuf('dynamic_inserts => ' || quot(flexfield.dynamic_inserts) || ',');
3336 printbuf('allow_id_value_sets => ' || quot(flexfield.allow_id_value_sets) || ',');
3337 printbuf('index_flag => ' || quot(flexfield.index_flag) || ',');
3338 printbuf('concat_seg_len_max => ' || quot(flexfield.concat_seg_len_max) || ',');
3339 printbuf('concat_len_warning => ' || quot(flexfield.concat_len_warning) || ',');
3340 printbuf('concatenated_segs_view_name => ' || quot(flexfield.concatenated_segs_view_name) || ');');
3341
3342 printbuf('fnd_flex_key_api.register(flexfield,');
3343 printbuf('enable_columns => ''N'');');
3344
3345 FOR column_r IN column_c LOOP
3346 printbuf('/* ENABLE COLUMN */');
3347 printbuf('fnd_flex_key_api.enable_column(flexfield => flexfield,');
3348 printbuf('column_name => ' || quot(column_r.column_name) || ');');
3349 END LOOP;
3350
3351 IF(recurse) THEN
3352 dump_all_flex_qualifiers(flexfield => flexfield,
3353 recurse => recurse);
3354 dump_all_structures(flexfield => flexfield,
3355 recurse => recurse);
3356 END IF;
3357
3358 EXCEPTION
3359 WHEN OTHERS THEN
3360 message('dump_flexfield: ' || Sqlerrm);
3361 RAISE;
3362 END;
3363
3364 /* ---------------------------------------------------------------------- */
3365 PROCEDURE dump_all_flex_qualifiers(flexfield IN flexfield_type,
3366 recurse IN BOOLEAN DEFAULT TRUE)
3367 IS
3368 CURSOR flex_qualifier_c IS
3369 SELECT *
3370 FROM fnd_segment_attribute_types
3371 WHERE application_id = flexfield.application_id
3372 AND id_flex_code = flexfield.flex_code
3373 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3374 BEGIN
3375 FOR flex_qualifier_r IN flex_qualifier_c LOOP
3376 printbuf('/* FLEX QUALIFIER */');
3377 printbuf('fnd_flex_key_api.add_flex_qualifier(flexfield => flexfield,');
3378 printbuf('qualifier_name => ' || quot(flex_qualifier_r.segment_attribute_type) || ',');
3379 printbuf('prompt => ' || quot(flex_qualifier_r.segment_prompt) || ',');
3380 printbuf('description => ' || quot(flex_qualifier_r.description) || ',');
3381 printbuf('global_flag => ' || quot(flex_qualifier_r.global_flag) || ',');
3382 printbuf('required_flag => ' || quot(flex_qualifier_r.required_flag) || ',');
3383 printbuf('unique_flag => ' || quot(flex_qualifier_r.unique_flag) || ');');
3384
3385 IF(recurse) THEN
3386 dump_all_seg_qualifiers(flexfield => flexfield,
3387 flex_qualifier => flex_qualifier_r.segment_attribute_type);
3388 END IF;
3389 END LOOP;
3390 EXCEPTION
3391 WHEN OTHERS THEN
3392 message('dump_all_flex_qualifiers: ' || Sqlerrm);
3393 RAISE;
3394 END;
3395
3396
3397 /* ---------------------------------------------------------------------- */
3398 PROCEDURE dump_all_seg_qualifiers(flexfield IN flexfield_type,
3399 flex_qualifier IN VARCHAR2)
3400 IS
3401 CURSOR seg_qualifier_c IS
3402 SELECT *
3403 FROM fnd_val_attribute_types_vl
3404 WHERE application_id = flexfield.application_id
3405 AND id_flex_code = flexfield.flex_code
3406 AND segment_attribute_type = flex_qualifier
3407 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3408 BEGIN
3409 FOR seg_qualifier_r IN seg_qualifier_c LOOP
3410 printbuf('/* SEGMENT QUALIFIER */');
3411 printbuf('fnd_flex_key_api.add_seg_qualifier(flexfield => flexfield,');
3412 printbuf('flex_qualifier => ' || quot(flex_qualifier) || ',');
3413 printbuf('qualifier_name => ' || quot(seg_qualifier_r.value_attribute_type) || ',');
3414 printbuf('prompt => ' || quot(seg_qualifier_r.prompt) || ',');
3415 printbuf('description => ' || quot(seg_qualifier_r.description) || ',');
3419 END LOOP;
3416 printbuf('derived_column => ' || quot(seg_qualifier_r.application_column_name) || ',');
3417 printbuf('quickcode_type => ' || quot(seg_qualifier_r.lookup_type) || ',');
3418 printbuf('default_value => ' || quot(seg_qualifier_r.default_value) || ');');
3420
3421 EXCEPTION
3422 WHEN OTHERS THEN
3423 message('dump_all_seg_qualifiers: ' || Sqlerrm);
3424 RAISE;
3425 END;
3426
3427 /* ---------------------------------------------------------------------- */
3428 PROCEDURE dump_all_structures(flexfield IN flexfield_type,
3429 recurse IN BOOLEAN DEFAULT TRUE)
3430 IS
3431 structure structure_type;
3432 BEGIN
3433 message_init;
3434 FOR structure_r IN structure_c(flexfield) LOOP
3435 structure := find_structure(flexfield => flexfield,
3436 structure_number => structure_r.structure_number);
3437 dump_structure(flexfield => flexfield,
3438 structure => structure,
3439 recurse => recurse);
3440 END LOOP;
3441
3442 EXCEPTION
3443 WHEN OTHERS THEN
3444 message('dump_all_structures: ' || Sqlerrm);
3445 RAISE;
3446 END;
3447
3448 /* ---------------------------------------------------------------------- */
3449 PROCEDURE dump_structure(flexfield IN flexfield_type,
3450 structure IN structure_type,
3451 recurse IN BOOLEAN DEFAULT TRUE)
3452 IS
3453 BEGIN
3454 message_init;
3455
3456 printbuf('/* STRUCTURE */');
3457 printbuf('structure := fnd_flex_key_api.new_structure(flexfield => flexfield,');
3458 printbuf('structure_code => ' || quot(structure.structure_code) || ',');
3459 printbuf('structure_title => ' || quot(structure.structure_name) || ',');
3460 printbuf('description => ' || quot(structure.description) || ',');
3461 printbuf('view_name => ' || quot(structure.view_name) || ',');
3462 printbuf('freeze_flag => ' || quot(structure.freeze_flag) || ',');
3463 printbuf('enabled_flag => ' || quot(structure.enabled_flag) || ',');
3464 printbuf('segment_separator => ' || quot(structure.segment_separator) || ',');
3465 printbuf ('cross_val_flag => ' || quot(structure.cross_val_flag) || ',');
3466 printbuf('freeze_rollup_flag => ' || quot(structure.freeze_rollup_flag) || ',');
3467 printbuf('dynamic_insert_flag => ' || quot(structure.dynamic_insert_flag) || ',');
3468 printbuf('shorthand_enabled_flag => ' || quot(structure.shorthand_enabled_flag) || ',');
3469 printbuf('shorthand_prompt => ' || quot(structure.shorthand_prompt) || ',');
3470 printbuf('shorthand_length => ' || quot(structure.shorthand_length) || ');');
3471
3472 IF(structure.structure_number = 101) THEN
3473 printbuf('structure2 := fnd_flex_key_api.find_structure(');
3474 printbuf('flexfield => flexfield,');
3475 printbuf('structure_number => 101);');
3476 printbuf('fnd_flex_key_api.modify_structure(flexfield => flexfield,');
3477 printbuf('original => structure2,');
3478 printbuf('modified => structure);');
3479 ELSE
3480 printbuf('fnd_flex_key_api.add_structure(flexfield => flexfield,');
3481 printbuf('structure => structure);');
3482 END IF;
3483
3484 IF(recurse) THEN
3485 dump_all_segments(flexfield => flexfield,
3486 structure => structure);
3487 END IF;
3488
3489 flush;
3490
3491 EXCEPTION
3492 WHEN OTHERS THEN
3493 message('dump_structure: ' || Sqlerrm);
3494 RAISE;
3495 END;
3496
3497 /* ---------------------------------------------------------------------- */
3498 PROCEDURE dump_qualifier_assignments(flexfield IN flexfield_type,
3499 structure IN structure_type,
3500 segment IN segment_type)
3501 IS
3502 CURSOR assignments_c IS
3503 SELECT sav.segment_attribute_type
3504 FROM fnd_segment_attribute_values sav,
3505 fnd_segment_attribute_types sat
3506 WHERE sav.application_id = flexfield.application_id
3507 AND sav.id_flex_code = flexfield.flex_code
3508 AND sav.id_flex_num = structure.structure_number
3509 AND sav.application_column_name = segment.column_name
3510 AND sav.attribute_value = 'Y'
3511 -- and not global
3512 AND sat.application_id = sav.application_id
3513 AND sat.id_flex_code = sav.id_flex_code
3514 AND sat.segment_attribute_type = sav.segment_attribute_type
3515 AND sat.global_flag = 'N'
3516 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3517 BEGIN
3518 FOR assignments_r IN assignments_c LOOP
3519 printbuf('/* QUALIFIER ASSIGNMENT */');
3520 printbuf('fnd_flex_key_api.assign_qualifier(');
3521 printbuf('flexfield => flexfield,' );
3522 printbuf('structure => structure,' );
3523 printbuf('segment => segment,');
3524 printbuf('flexfield_qualifier => ' ||
3525 quot(assignments_r.segment_attribute_type) || ',');
3526 printbuf('enable_flag => ''Y'');');
3527 END LOOP;
3528 EXCEPTION
3529 WHEN OTHERS THEN
3530 message('dump_qualifier_assignments: ' || Sqlerrm);
3531 RAISE;
3532 END;
3533
3534 /* ---------------------------------------------------------------------- */
3535 PROCEDURE dump_segment(flexfield IN flexfield_type,
3536 structure IN structure_type,
3537 segment IN segment_type)
3538 IS
3539 BEGIN
3540 message_init;
3541
3542 printbuf('/* SEGMENT */');
3543 printbuf('segment := fnd_flex_key_api.new_segment(');
3544 printbuf('flexfield => flexfield,');
3545 printbuf('structure => structure,');
3546 printbuf('segment_name => ' || quot(segment.segment_name) || ',');
3547 printbuf('description => ' || quot(segment.description) || ',');
3548 printbuf('column_name => ' || quot(segment.column_name) || ',');
3549 printbuf('segment_number => ' || quot(segment.segment_number) || ',');
3550 printbuf('enabled_flag => ' || quot(segment.enabled_flag) || ',');
3551 printbuf('displayed_flag => ' || quot(segment.displayed_flag) || ',');
3552 printbuf('indexed_flag => ' || quot(segment.indexed_flag) || ',');
3553 printbuf('value_set => ' || quot(segment.value_set_name) || ',');
3554 printbuf('default_type => ' || quot(segment.default_type) || ',');
3555 printbuf('default_value => ' || quot(segment.default_value) || ',');
3556 printbuf('required_flag => ' || quot(segment.required_flag) || ',');
3557 printbuf('security_flag => ' || quot(segment.security_flag) || ',');
3558 printbuf('range_code => ' || quot(segment.range_code) || ',');
3559 printbuf('display_size => ' || quot(segment.display_size) || ',');
3560 printbuf('description_size => ' || quot(segment.description_size) || ',');
3561 printbuf('concat_size => ' || quot(segment.concat_size) || ',');
3562 printbuf('lov_prompt => ' || quot(segment.lov_prompt) || ',');
3563 printbuf('window_prompt => ' || quot(segment.window_prompt) || ',');
3564 printbuf('runtime_property_function => ' || quot(segment.runtime_property_function) || ',');
3565 printbuf('additional_where_clause => ' || quot(segment.additional_where_clause) || ');');
3566
3567 printbuf('fnd_flex_key_api.add_segment(flexfield => flexfield,');
3568 printbuf('structure => structure,');
3569 printbuf('segment => segment);');
3570
3571 dump_qualifier_assignments(flexfield => flexfield,
3572 structure => structure,
3573 segment => segment);
3574 flush;
3575
3576 EXCEPTION
3577 WHEN OTHERS THEN
3578 message('dump_segment: ' || Sqlerrm);
3579 RAISE;
3580 END;
3581
3582 /* ---------------------------------------------------------------------- */
3583 PROCEDURE dump_all_segments(flexfield IN flexfield_type,
3584 structure IN structure_type)
3585 IS
3586 segment segment_type;
3587 BEGIN
3588 message_init;
3589
3590 FOR segment_r IN segment_c(flexfield, structure) LOOP
3591 segment :=
3592 find_segment(flexfield => flexfield,
3593 structure => structure,
3594 segment_name => segment_r.segment_name);
3595 dump_segment(flexfield => flexfield,
3596 structure => structure,
3597 segment => segment);
3598 END LOOP;
3599
3600 EXCEPTION
3601 WHEN OTHERS THEN
3602 message('dump_all_segments: ' || Sqlerrm);
3603 RAISE;
3604 END;
3605
3606 /* ---------------------------------------------------------------------- */
3607 PROCEDURE get_structures(flexfield IN flexfield_type,
3608 enabled_only IN BOOLEAN DEFAULT TRUE,
3609 nstructures OUT nocopy NUMBER,
3610 structures OUT nocopy structure_list)
3611 IS
3612 rv structure_list;
3613 i NUMBER;
3614 en_flag VARCHAR2(1);
3615 BEGIN
3616 i := 0;
3617 IF(enabled_only) THEN
3618 en_flag := 'Y';
3619 END IF;
3620 FOR structure_r IN structure_c(flexfield, en_flag) LOOP
3621 i := i + 1;
3622 rv(i) := structure_r.structure_number;
3623 END LOOP;
3624 nstructures := i;
3625 structures := rv;
3626 EXCEPTION
3627 WHEN OTHERS THEN
3628 message('get_structures: ' || Sqlerrm);
3629 RAISE;
3630 END;
3631
3632 /* ---------------------------------------------------------------------- */
3633 PROCEDURE get_segments(flexfield IN flexfield_type,
3634 structure IN structure_type,
3635 enabled_only IN BOOLEAN DEFAULT TRUE,
3636 nsegments OUT nocopy NUMBER,
3637 segments OUT nocopy segment_list)
3638 IS
3639 rv segment_list;
3640 i NUMBER;
3641 en_flag VARCHAR2(1);
3642 BEGIN
3643 i := 0;
3644 IF(enabled_only) THEN
3645 en_flag := 'Y';
3646 END IF;
3647 FOR segment_r IN segment_c(flexfield, structure, en_flag) LOOP
3648 i := i + 1;
3649 rv(i) := segment_r.segment_name;
3650 END LOOP;
3651 nsegments := i;
3652 segments := rv;
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655 message('get_segments: ' || Sqlerrm);
3656 RAISE;
3657 END;
3658
3659 FUNCTION is_table_used(p_application_id IN fnd_tables.application_id%TYPE,
3660 p_table_name IN fnd_tables.table_name%TYPE,
3661 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN
3662 IS
3663 up_table_name fnd_tables.table_name%TYPE := Upper(p_table_name);
3664 l_a_id fnd_id_flexs.application_id%TYPE;
3668 x_message := 'This table is not used by Key Flexfields.';
3665 l_flex_code fnd_id_flexs.id_flex_code%TYPE;
3666 l_flex_name fnd_id_flexs.id_flex_name%TYPE;
3667 BEGIN
3669 BEGIN
3670 SELECT application_id, id_flex_code, id_flex_name
3671 INTO l_a_id, l_flex_code, l_flex_name
3672 FROM fnd_id_flexs
3673 WHERE table_application_id = p_application_id
3674 AND Upper(application_table_name) = up_table_name
3675 AND ROWNUM = 1
3676 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3677 x_message :=
3678 'This table is used by ' || chr_newline ||
3679 'APPLICATION_ID : ' || l_a_id || chr_newline ||
3680 'ID_FLEX_CODE : ' || l_flex_code || chr_newline ||
3681 'ID_FLEX_NAME : ' || l_flex_name;
3682 RETURN(TRUE);
3683 EXCEPTION
3684 WHEN no_data_found THEN
3685 NULL;
3686 WHEN OTHERS THEN
3687 x_message :=
3688 'SELECT FROM FND_ID_FLEXS is failed. ' || chr_newline ||
3689 'SQLERRM : ' || Sqlerrm;
3690 RETURN(TRUE);
3691 END;
3692 RETURN(FALSE);
3693 EXCEPTION
3694 WHEN OTHERS THEN
3695 x_message :=
3696 'FND_FLEX_KEY_API.IS_TABLE_USED is failed. ' || chr_newline ||
3697 'SQLERRM : ' || Sqlerrm;
3698 RETURN(TRUE);
3699 END is_table_used;
3700
3701
3702 FUNCTION is_column_used(p_application_id IN fnd_tables.application_id%TYPE,
3703 p_table_name IN fnd_tables.table_name%TYPE,
3704 p_column_name IN fnd_columns.column_name%TYPE,
3705 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN
3706 IS
3707 up_table_name fnd_tables.table_name%TYPE := Upper(p_table_name);
3708 up_column_name fnd_columns.column_name%TYPE := Upper(p_column_name);
3709 l_a_id fnd_id_flexs.application_id%TYPE;
3710 l_flex_code fnd_id_flexs.id_flex_code%TYPE;
3711 l_flex_name fnd_id_flexs.id_flex_name%TYPE;
3712 l_flex_num fnd_id_flex_segments.id_flex_num%TYPE;
3713 l_segment fnd_id_flex_segments.segment_name%TYPE;
3714 l_seg_att fnd_value_attribute_types.segment_attribute_type%TYPE;
3715 l_val_att fnd_value_attribute_types.value_attribute_type%TYPE;
3716 l_id_col fnd_id_flexs.unique_id_column_name%TYPE;
3717 l_set_col fnd_id_flexs.set_defining_column_name%TYPE;
3718 BEGIN
3719 x_message := 'This column is not used by Key Flexfields.';
3720 BEGIN
3721 SELECT application_id, id_flex_code, id_flex_name,
3722 unique_id_column_name
3723 INTO l_a_id, l_flex_code, l_flex_name, l_id_col
3724 FROM fnd_id_flexs
3725 WHERE table_application_id = p_application_id
3726 AND Upper(application_table_name) = up_table_name
3727 AND Upper(unique_id_column_name) = up_column_name
3728 AND ROWNUM = 1
3729 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3730 x_message :=
3731 'This column is used by ' || chr_newline ||
3732 'APPLICATION_ID : ' || l_a_id || chr_newline ||
3733 'ID_FLEX_CODE : ' || l_flex_code || chr_newline ||
3734 'ID_FLEX_NAME : ' || l_flex_name || chr_newline ||
3735 'UNIQUE_ID_COLUMN_NAME : ' || l_id_col;
3736 RETURN(TRUE);
3737 EXCEPTION
3738 WHEN no_data_found THEN
3739 NULL;
3740 WHEN OTHERS THEN
3741 x_message :=
3742 'SELECT FROM FND_ID_FLEXS is failed. ' || chr_newline ||
3743 'SQLERRM : ' || Sqlerrm;
3744 RETURN(TRUE);
3745 END;
3746 BEGIN
3747 SELECT application_id, id_flex_code, id_flex_name,
3748 set_defining_column_name
3749 INTO l_a_id, l_flex_code, l_flex_name, l_set_col
3750 FROM fnd_id_flexs
3751 WHERE table_application_id = p_application_id
3752 AND Upper(application_table_name) = up_table_name
3753 AND set_defining_column_name IS NOT NULL
3754 AND Upper(set_defining_column_name) = up_column_name
3755 AND ROWNUM = 1
3756 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3757 x_message :=
3758 'This column is used by ' || chr_newline ||
3759 'APPLICATION_ID : ' || l_a_id || chr_newline ||
3760 'ID_FLEX_CODE : ' || l_flex_code || chr_newline ||
3761 'ID_FLEX_NAME : ' || l_flex_name || chr_newline ||
3762 'SET_DEFINING_COLUMN_NAME : ' || l_set_col;
3763 RETURN(TRUE);
3764 EXCEPTION
3765 WHEN no_data_found THEN
3766 NULL;
3767 WHEN OTHERS THEN
3768 x_message :=
3769 'SELECT FROM FND_ID_FLEXS is failed. ' || chr_newline ||
3770 'SQLERRM : ' || Sqlerrm;
3771 RETURN(TRUE);
3772 END;
3773
3774 BEGIN
3775 SELECT idf.application_id, idf.id_flex_code,
3776 idf.id_flex_name, ifst.id_flex_num, ifst.segment_name
3777 INTO l_a_id, l_flex_code, l_flex_name, l_flex_num, l_segment
3778 FROM fnd_id_flexs idf, fnd_id_flex_segments ifst
3779 WHERE idf.application_id = ifst.application_id
3780 AND idf.id_flex_code = ifst.id_flex_code
3781 AND idf.table_application_id = p_application_id
3782 AND Upper(idf.application_table_name) = up_table_name
3783 AND Upper(ifst.application_column_name) = up_column_name
3784 AND ROWNUM = 1
3785 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3786 x_message :=
3787 'This column is used by ' || chr_newline ||
3788 'APPLICATION_ID : ' || l_a_id || chr_newline ||
3789 'ID_FLEX_CODE : ' || l_flex_code || chr_newline ||
3790 'ID_FLEX_NAME : ' || l_flex_name || chr_newline ||
3791 'IF_FLEX_NUM : ' || l_flex_num || chr_newline ||
3792 'SEGMENT_NAME : ' || l_segment;
3793 RETURN(TRUE);
3794 EXCEPTION
3795 WHEN no_data_found THEN
3796 NULL;
3797 WHEN OTHERS THEN
3798 x_message :=
3799 'SELECT FROM FND_ID_FLEX_SEGMENTS is failed. ' || chr_newline ||
3800 'SQLERRM : ' || Sqlerrm;
3804 BEGIN
3801 RETURN(TRUE);
3802 END;
3803
3805 SELECT idf.application_id, idf.id_flex_code,
3806 idf.id_flex_name, vat.segment_attribute_type, vat.value_attribute_type
3807 INTO l_a_id, l_flex_code, l_flex_name, l_seg_att, l_val_att
3808 FROM fnd_id_flexs idf, fnd_value_attribute_types vat
3809 WHERE idf.application_id = vat.application_id
3810 AND idf.id_flex_code = vat.id_flex_code
3811 AND idf.table_application_id = p_application_id
3812 AND Upper(idf.application_table_name) = up_table_name
3813 AND Upper(vat.application_column_name) = up_column_name
3814 AND ROWNUM = 1
3815 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3816 x_message :=
3817 'This column is used by ' || chr_newline ||
3818 'APPLICATION_ID : ' || l_a_id || chr_newline ||
3819 'ID_FLEX_CODE : ' || l_flex_code || chr_newline ||
3820 'ID_FLEX_NAME : ' || l_flex_name || chr_newline ||
3821 'FLEXFIELD_QUALIFER : ' || l_seg_att || chr_newline ||
3822 'SEGMENT_QUALIFIER : ' || l_val_att;
3823 RETURN(TRUE);
3824 EXCEPTION
3825 WHEN no_data_found THEN
3826 NULL;
3827 WHEN OTHERS THEN
3828 x_message :=
3829 'SELECT FROM FND_VALUE_ATTRIBUTE_TYPES is failed. ' ||chr_newline||
3830 'SQLERRM : ' || Sqlerrm;
3831 RETURN(TRUE);
3832 END;
3833 RETURN(FALSE);
3834 EXCEPTION
3835 WHEN OTHERS THEN
3836 x_message :=
3837 'FND_FLEX_KEY_API.IS_COLUMN_USED is failed. ' || chr_newline ||
3838 'SQLERRM : ' || Sqlerrm;
3839 RETURN(TRUE);
3840 END is_column_used;
3841
3842 --
3843 -- Get the segment display order given the qualifier name.
3844 --
3845 FUNCTION get_seg_order_by_qual_name(p_application_id IN NUMBER,
3846 p_id_flex_code IN VARCHAR2,
3847 p_id_flex_num IN NUMBER,
3848 p_segment_attribute_type IN VARCHAR2,
3849 x_segment_order OUT nocopy NUMBER)
3850 RETURN BOOLEAN IS
3851 l_segment_num NUMBER;
3852 l_segment_order NUMBER;
3853 BEGIN
3854 g_cache_key := (p_application_id || '.' || p_id_flex_code || '.' ||
3855 p_id_flex_num || '.' || p_segment_attribute_type);
3856
3857 fnd_plsql_cache.generic_1to1_get_value(soq_cache_controller,
3858 soq_cache_storage,
3859 g_cache_key,
3860 g_cache_value,
3861 g_cache_return_code);
3862
3863 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3864 l_segment_order := g_cache_value.number_1;
3865 ELSE
3866 SELECT s.segment_num
3867 INTO l_segment_num
3868 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
3869 fnd_segment_attribute_types sat
3870 WHERE s.application_id = p_application_id
3871 AND s.id_flex_code = p_id_flex_code
3872 AND s.id_flex_num = p_id_flex_num
3873 AND s.enabled_flag = 'Y'
3874 AND s.application_column_name = sav.application_column_name
3875 AND sav.application_id = p_application_id
3876 AND sav.id_flex_code = p_id_flex_code
3877 AND sav.id_flex_num = p_id_flex_num
3878 AND sav.attribute_value = 'Y'
3879 AND sav.segment_attribute_type = sat.segment_attribute_type
3880 AND sat.application_id = p_application_id
3881 AND sat.id_flex_code = p_id_flex_code
3882 AND sat.unique_flag = 'Y'
3883 AND sat.segment_attribute_type = p_segment_attribute_type
3884 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3885
3886 SELECT count(segment_num)
3887 INTO l_segment_order
3888 FROM fnd_id_flex_segments
3889 WHERE application_id = p_application_id
3890 AND id_flex_code = p_id_flex_code
3891 AND id_flex_num = p_id_flex_num
3892 AND enabled_flag = 'Y'
3893 AND segment_num <= l_segment_num
3894 AND '$Header: AFFFKAIB.pls 120.10.12010000.1 2008/07/25 14:14:04 appldev ship $' IS NOT NULL;
3895
3896 fnd_plsql_cache.generic_cache_new_value
3897 (x_value => g_cache_value,
3898 p_number_1 => l_segment_order);
3899
3900 fnd_plsql_cache.generic_1to1_put_value(soq_cache_controller,
3901 soq_cache_storage,
3902 g_cache_key,
3903 g_cache_value);
3904 END IF;
3905
3906 x_segment_order := l_segment_order;
3907 return(TRUE);
3908
3909 EXCEPTION
3910 WHEN OTHERS then
3911 return(FALSE);
3912 END get_seg_order_by_qual_name;
3913
3914 --------------------------------------------------------------------------------
3915 -- Wrapper for raise_application_error(<code>, <error>, TRUE);
3916 --------------------------------------------------------------------------------
3917 PROCEDURE raise_error
3918 (p_code IN NUMBER,
3919 p_error IN VARCHAR2)
3920 IS
3921 BEGIN
3922 raise_application_error(p_code, p_error, TRUE);
3923
3924 -- No exception handling here
3925 END raise_error;
3926
3927
3928
3929 --------------------------------------------------------------------------------
3930 -- Raises exception for 'when others then' block
3931 --------------------------------------------------------------------------------
3932 PROCEDURE raise_others
3933 (p_method IN VARCHAR2,
3934 p_arg1 IN VARCHAR2 DEFAULT g_unused_argument,
3935 p_arg2 IN VARCHAR2 DEFAULT g_unused_argument,
3936 p_arg3 IN VARCHAR2 DEFAULT g_unused_argument,
3937 p_arg4 IN VARCHAR2 DEFAULT g_unused_argument,
3938 p_arg5 IN VARCHAR2 DEFAULT g_unused_argument,
3939 p_arg6 IN VARCHAR2 DEFAULT g_unused_argument,
3940 p_arg7 IN VARCHAR2 DEFAULT g_unused_argument,
3941 p_arg8 IN VARCHAR2 DEFAULT g_unused_argument,
3942 p_arg9 IN VARCHAR2 DEFAULT g_unused_argument)
3943 IS
3944 l_error VARCHAR2(32000);
3948 if (p_arg1 <> g_unused_argument) then
3945 BEGIN
3946 l_error := p_method || '(';
3947
3949 l_error := l_error || p_arg1;
3950 end if;
3951
3952 if (p_arg2 <> g_unused_argument) then
3953 l_error := l_error || ', ' || p_arg2;
3954 end if;
3955
3956 if (p_arg3 <> g_unused_argument) then
3957 l_error := l_error || ', ' || p_arg3;
3958 end if;
3959
3960 if (p_arg4 <> g_unused_argument) then
3961 l_error := l_error || ', ' || p_arg4;
3962 end if;
3963
3964 if (p_arg5 <> g_unused_argument) then
3965 l_error := l_error || ', ' || p_arg5;
3966 end if;
3967
3968 if (p_arg6 <> g_unused_argument) then
3969 l_error := l_error || ', ' || p_arg6;
3970 end if;
3971
3972 if (p_arg7 <> g_unused_argument) then
3973 l_error := l_error || ', ' || p_arg7;
3974 end if;
3975
3976 if (p_arg8 <> g_unused_argument) then
3977 l_error := l_error || ', ' || p_arg8;
3978 end if;
3979
3980 if (p_arg9 <> g_unused_argument) then
3981 l_error := l_error || ', ' || p_arg9;
3982 end if;
3983
3984 l_error := l_error || ') raised exception.';
3985
3986 raise_error(error_others, l_error);
3987
3988 -- No exception handling here
3989 END raise_others;
3990
3991
3992 --------------------------------------------------------------------------------
3993 -- Raises exception for 'when no_data_found then' block
3994 --------------------------------------------------------------------------------
3995 PROCEDURE raise_no_data_found
3996 (p_entity IN VARCHAR2,
3997 p_key1 IN VARCHAR2,
3998 p_value1 IN VARCHAR2,
3999 p_key2 IN VARCHAR2 DEFAULT NULL,
4000 p_value2 IN VARCHAR2 DEFAULT NULL,
4001 p_key3 IN VARCHAR2 DEFAULT NULL,
4002 p_value3 IN VARCHAR2 DEFAULT NULL,
4003 p_key4 IN VARCHAR2 DEFAULT NULL,
4004 p_value4 IN VARCHAR2 DEFAULT NULL,
4005 p_key5 IN VARCHAR2 DEFAULT NULL,
4006 p_value5 IN VARCHAR2 DEFAULT NULL,
4007 p_key6 IN VARCHAR2 DEFAULT NULL,
4008 p_value6 IN VARCHAR2 DEFAULT NULL,
4009 p_key7 IN VARCHAR2 DEFAULT NULL,
4010 p_value7 IN VARCHAR2 DEFAULT NULL)
4011 IS
4012 l_error VARCHAR2(32000);
4013 BEGIN
4014 l_error := ('<' || p_entity || '> does not exist. Primary Key: ' ||
4015 Upper(p_key1) || ':''' || p_value1 || '''');
4016 IF (p_key2 IS NOT NULL) THEN
4017 l_error := l_error || ', ' || Upper(p_key2) || ':''' || p_value2 || '''';
4018 END IF;
4019 IF (p_key3 IS NOT NULL) THEN
4020 l_error := l_error || ', ' || Upper(p_key3) || ':''' || p_value3 || '''';
4021 END IF;
4022 IF (p_key4 IS NOT NULL) THEN
4023 l_error := l_error || ', ' || Upper(p_key4) || ':''' || p_value4 || '''';
4024 END IF;
4025 IF (p_key5 IS NOT NULL) THEN
4026 l_error := l_error || ', ' || Upper(p_key5) || ':''' || p_value5 || '''';
4027 END IF;
4028 IF (p_key6 IS NOT NULL) THEN
4029 l_error := l_error || ', ' || Upper(p_key6) || ':''' || p_value6 || '''';
4030 END IF;
4031 IF (p_key7 IS NOT NULL) THEN
4032 l_error := l_error || ', ' || Upper(p_key7) || ':''' || p_value7 || '''';
4033 END IF;
4034
4035 raise_error(error_no_data_found, l_error);
4036
4037 -- No exception handling here.
4038 END raise_no_data_found;
4039
4040
4041 --------------------------------------------------------------------------------
4042 -- Returns Application details.
4043 --------------------------------------------------------------------------------
4044 PROCEDURE get_app
4045 (p_application_short_name IN fnd_application.application_short_name%TYPE,
4046 x_app OUT nocopy app_type)
4047 IS
4048 BEGIN
4049 BEGIN
4050 SELECT fa.*
4051 INTO x_app
4052 FROM fnd_application fa
4053 WHERE fa.application_short_name = p_application_short_name;
4054 EXCEPTION
4055 WHEN no_data_found THEN
4056 raise_no_data_found
4057 ('Application',
4058 'application_short_name', p_application_short_name);
4059 END;
4060 EXCEPTION
4061 WHEN OTHERS THEN
4062 raise_others('get_app',
4063 p_application_short_name);
4064 END get_app;
4065
4066
4067 -- Deletes compiled definition from fnd_compiled_id_flexs and fnd_compiled_id_flex_structs tables.
4068
4069 PROCEDURE delete_compiled_definition
4070 (p_application_id IN fnd_application.application_id%TYPE,
4071 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
4072 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE)
4073 IS
4074 BEGIN
4075
4076 DELETE FROM fnd_compiled_id_flexs
4077 WHERE application_id = p_application_id
4078 AND id_flex_code = p_id_flex_code;
4079
4080 DELETE FROM fnd_compiled_id_flex_structs
4081 WHERE application_id = p_application_id
4082 AND id_flex_code = p_id_flex_code
4083 AND id_flex_num = p_id_flex_num;
4084
4085 EXCEPTION
4086 WHEN NO_DATA_FOUND THEN
4087 NULL;
4088 END delete_compiled_definition;
4089
4090
4091 -- Parses the AWC elements.
4092 -- Returns an array of AWC elements.
4093 -- Example of awc string:
4094 -- ((1=1)
4095 -- AND /* VALUE_GT_101 */
4096 -- (FLEX_VALUE > '101')
4097 -- AND /* VALUE_LT_999 */
4098 -- (FLEX_VALUE < '999'))
4099
4100 PROCEDURE get_awc_elements
4101 (p_flexfield IN flexfield_type,
4102 p_structure IN structure_type,
4103 p_segment IN segment_type,
4104 x_numof_awc_elements OUT nocopy number,
4105 x_awc_elements OUT nocopy awc_elements_type)
4106 IS
4107
4108 i NUMBER := 0;
4109 l_str_length NUMBER := 0;
4110 l_tag_length NUMBER := 0;
4111 l_awc_length NUMBER := 0;
4112 l_current_loc NUMBER := 1;
4113 l_first_occur NUMBER := 1;
4114 l_last_occur NUMBER := 1;
4115 l_numof_awc_elements NUMBER := 0;
4116 l_awc_elements awc_elements_type;
4117 l_additional_where_clause fnd_id_flex_segments.additional_where_clause%TYPE;
4118 l_app app_type;
4119
4120
4121 BEGIN
4122
4123 get_app(p_flexfield.appl_short_name, l_app);
4124
4125 SELECT additional_where_clause
4126 INTO l_additional_where_clause
4127 FROM fnd_id_flex_segments
4128 WHERE application_id = l_app.application_id
4129 AND id_flex_code = p_flexfield.flex_code
4130 AND id_flex_num = p_structure.structure_number
4131 AND application_column_name = p_segment.column_name;
4132
4133
4134 -- Get the length of the whole awc string
4135 l_str_length := length(l_additional_where_clause);
4136
4137 IF (l_str_length > 0) THEN
4138
4139 LOOP
4140
4141 -- Exit loop when no more '/*' is found which means
4142 -- there are no more TAGS which also means there are
4143 -- no more WHERE CLAUSES
4144 IF(instr(l_additional_where_clause, '/*', l_current_loc) = 0) THEN
4145 EXIT;
4146 END IF;
4147
4148 i := i+1; -- Count the number of awc
4149
4150 -- Parse string to get the where clause TAG
4151 -- The TAG is in between the characters /* */
4152 l_current_loc := instr(l_additional_where_clause, '/*', l_current_loc);
4153 l_first_occur := l_current_loc + 3;
4154 l_current_loc := instr(l_additional_where_clause, '*/', l_current_loc);
4155 l_last_occur := l_current_loc - 1;
4156 l_tag_length := l_last_occur - l_first_occur;
4157
4158 -- Copy the TAG into the awc array
4159 l_awc_elements(i).tag :=
4160 substr(l_additional_where_clause, l_first_occur, l_tag_length);
4161
4162 --------------
4163
4164 -- Parse string to get the ADDITIONAL WHERE CLAUSE
4165 -- The WHERE CLAUSE is in between the characters ( )
4166 -- We need to be careful because there could be nested parens
4167 -- within the main () which holds the awc i.e. ( col1 < (col3 - col2) )
4168
4169 -- Get the first open paren which comes right after
4170 -- The closing '*/' for the Tag
4171 l_current_loc := instr(l_additional_where_clause, '(', l_current_loc);
4172 l_first_occur := l_current_loc + 1;
4173
4174 -- To find the closing paren we search for the TAG of the
4175 -- next where clause, we then search backwards for the
4176 -- first occurance of the closig paren. This will give us
4177 -- the closing paren. We must do this because a WHERE
4178 -- clause could have nested parens so we cannot just
4179 -- look for the frst open paren and the next closing paren
4180 l_current_loc := instr(l_additional_where_clause, '/*', l_current_loc);
4181
4182
4183 -- If there are no more occurences of a Tag then that means we
4184 -- are at the last WHERE clause. To find the last ) we need to
4185 -- search backwards from the end. We need to find the second
4186 -- closing paren from the end because the very last ) is the closing
4187 -- of the whole string and not the last ) of the WHERE clause
4188 if(l_current_loc = 0) then
4189 l_current_loc := 1;
4190 -- Find the last closing paren
4191 l_current_loc:=instr(l_additional_where_clause, ')', -l_current_loc);
4192 -- Move the current loc past the last closing paren
4193 -- so we can find the second to the last closing paren
4194 l_current_loc := (l_awc_length - (l_current_loc -2));
4195
4196 else
4197 -- When instr searches backwards it searches from location
4198 -- specified by l_current_loc reading from R2L
4199 -- This gives the position of the next '/*' counting from end R2L
4200 l_current_loc := l_str_length - l_current_loc;
4201 end if;
4202
4203 l_current_loc := instr(l_additional_where_clause, ')', -l_current_loc);
4204 l_last_occur := l_current_loc - 0;
4205 l_awc_length := l_last_occur - l_first_occur;
4206
4207
4208 -- Copy the WHERE CLAUSE into the awc array
4209 l_awc_elements(i).clause :=
4210 substr(l_additional_where_clause, l_first_occur, l_awc_length);
4211
4212 -- Update the Current location pointer
4213 l_current_loc := l_last_occur;
4214
4215
4216 END LOOP;
4217
4218 x_numof_awc_elements := i;
4219 x_awc_elements := l_awc_elements;
4220
4221 ELSE
4222 l_awc_elements(1).tag := NULL;
4223 l_awc_elements(1).clause := NULL;
4224 x_numof_awc_elements := 0;
4225 x_awc_elements := l_awc_elements;
4226 END IF;
4227
4228
4229 EXCEPTION
4230 WHEN no_data_found THEN
4231 raise_no_data_found
4232 ('Get AWC Elements',
4233 'application_id', l_app.application_id,
4234 'id_flex_code', p_flexfield.flex_code,
4235 'id_flex_num', p_structure.structure_number,
4236 'application_column_name', p_segment.column_name);
4237
4238 END get_awc_elements;
4239
4240
4241 PROCEDURE add_awc(p_flexfield IN flexfield_type,
4242 p_structure IN structure_type,
4243 p_segment IN segment_type,
4244 p_tag IN varchar2,
4245 p_clause IN varchar2)
4246 IS
4247 l_numof_awc_elements NUMBER := 0;
4248 l_awc_elements awc_elements_type;
4249 l_additional_where_clause fnd_id_flex_segments.additional_where_clause%TYPE;
4250 l_application_id fnd_id_flex_segments.application_id%TYPE;
4251 l_app app_type;
4252
4253 BEGIN
4254
4255 -- Check for white space in tag.
4256
4257 IF ((instr(p_tag, ' ') > 0) OR
4258 (instr(p_tag, chr_newline) > 0) OR
4259 (instr(p_tag, fnd_global.tab) > 0)) THEN
4260 raise_application_error(error_tag_white_space, 'The tag cannot have white space in it.', TRUE);
4261 END IF;
4262
4263 -- Check for maximum length of TAG.
4264
4265 IF (length(p_tag) > 30) THEN
4266 raise_application_error(error_tag_max_length, 'Maximum length of tag is 30.', TRUE);
4267 END IF;
4268
4269 -- Check for comments (/*, */) in CLAUSE.
4270
4271 IF ((instr(p_clause, '/*') > 0) OR
4272 (instr(p_clause, '*') > 0)) THEN
4273 raise_application_error(error_clause_comments, 'The clause contains /* or */ string.', TRUE);
4274 END IF;
4275
4276 -- Get the existing tags and clauses.
4277
4278 get_awc_elements
4279 (p_flexfield => p_flexfield,
4280 p_structure => p_structure,
4281 p_segment => p_segment,
4282 x_numof_awc_elements => l_numof_awc_elements,
4283 x_awc_elements => l_awc_elements);
4284
4285 get_app(p_flexfield.appl_short_name, l_app);
4286
4287 l_additional_where_clause := chr_newline;
4288 l_additional_where_clause := l_additional_where_clause||'((1=1)';
4289 l_additional_where_clause := l_additional_where_clause||chr_newline;
4290
4291 FOR i in 1 .. l_numof_awc_elements LOOP
4292 IF (l_awc_elements(i).tag = p_tag) THEN
4293 raise_application_error(error_tag_exists, 'Tag already present', TRUE);
4294 ELSIF (l_awc_elements(i).clause = p_clause) THEN
4295 raise_application_error(error_clause_exists, 'Clause already present for TAG '||l_awc_elements(i).tag, TRUE);
4296 ELSE
4297 l_additional_where_clause := l_additional_where_clause||'AND /* ';
4298 l_additional_where_clause := l_additional_where_clause||l_awc_elements(i).tag;
4299 l_additional_where_clause := l_additional_where_clause||' */';
4300 l_additional_where_clause := l_additional_where_clause||chr_newline;
4301 l_additional_where_clause := l_additional_where_clause||'(';
4302 l_additional_where_clause := l_additional_where_clause||l_awc_elements(i).clause;
4303 l_additional_where_clause := l_additional_where_clause||')';
4304 l_additional_where_clause := l_additional_where_clause||chr_newline;
4305 END IF;
4306 END LOOP;
4307
4308 l_additional_where_clause := l_additional_where_clause||'AND /* ';
4309 l_additional_where_clause := l_additional_where_clause||p_tag;
4310 l_additional_where_clause := l_additional_where_clause||' */';
4311 l_additional_where_clause := l_additional_where_clause||chr_newline;
4312 l_additional_where_clause := l_additional_where_clause||'(';
4313 l_additional_where_clause := l_additional_where_clause||p_clause;
4314 l_additional_where_clause := l_additional_where_clause||')';
4315 l_additional_where_clause := l_additional_where_clause||chr_newline;
4316
4317 l_additional_where_clause := l_additional_where_clause||')';
4318 l_additional_where_clause := l_additional_where_clause||chr_newline;
4319
4320 -- Update the segments' additional_where_clause column.
4321
4322 UPDATE fnd_id_flex_segments
4323 SET additional_where_clause = l_additional_where_clause
4324 WHERE application_id = l_app.application_id
4325 AND id_flex_code = p_flexfield.flex_code
4326 AND id_flex_num = p_structure.structure_number
4327 AND application_column_name = p_segment.column_name;
4328
4329 -- Delete compiled definition.
4330
4331 delete_compiled_definition(l_app.application_id, p_flexfield.flex_code, p_structure.structure_number);
4332
4333 END add_awc;
4334
4335
4336 PROCEDURE delete_awc(p_flexfield IN flexfield_type,
4337 p_structure IN structure_type,
4338 p_segment IN segment_type,
4339 p_tag IN varchar2)
4340 IS
4341 l_numof_awc_elements NUMBER := 0;
4342 l_awc_elements awc_elements_type;
4343 l_additional_where_clause fnd_id_flex_segments.additional_where_clause%TYPE;
4344 l_application_id fnd_id_flex_segments.application_id%TYPE;
4345 l_found boolean;
4346 l_app app_type;
4347
4348 BEGIN
4349
4350 -- Get the existing tags and clauses.
4351
4352 get_awc_elements
4353 (p_flexfield => p_flexfield,
4354 p_structure => p_structure,
4355 p_segment => p_segment,
4356 x_numof_awc_elements => l_numof_awc_elements,
4357 x_awc_elements => l_awc_elements);
4358
4359 get_app(p_flexfield.appl_short_name, l_app);
4360
4361 l_additional_where_clause := chr_newline;
4362 l_additional_where_clause := l_additional_where_clause||'((1=1)';
4363 l_additional_where_clause := l_additional_where_clause||chr_newline;
4364
4365 l_found := FALSE;
4366 FOR i in 1 .. l_numof_awc_elements LOOP
4367 IF (l_awc_elements(i).tag = p_tag) THEN
4368 l_found := TRUE;
4369 ELSE
4370 l_additional_where_clause := l_additional_where_clause||'AND /* ';
4371 l_additional_where_clause := l_additional_where_clause||l_awc_elements(i).tag;
4372 l_additional_where_clause := l_additional_where_clause||' */';
4373 l_additional_where_clause := l_additional_where_clause||chr_newline;
4374 l_additional_where_clause := l_additional_where_clause||'(';
4375 l_additional_where_clause := l_additional_where_clause||l_awc_elements(i).clause;
4376 l_additional_where_clause := l_additional_where_clause||')';
4377 l_additional_where_clause := l_additional_where_clause||chr_newline;
4378 END IF;
4379 END LOOP;
4380
4381 IF (NOT l_found) then
4382 raise_application_error(error_tag_not_exists, 'Tag not found', TRUE);
4383 END IF;
4384
4385 l_additional_where_clause := l_additional_where_clause||')';
4386 l_additional_where_clause := l_additional_where_clause||chr_newline;
4387
4388 -- Update the segments' additional_where_clause column.
4389
4390 UPDATE fnd_id_flex_segments
4391 SET additional_where_clause = l_additional_where_clause
4392 WHERE application_id = l_app.application_id
4393 AND id_flex_code = p_flexfield.flex_code
4394 AND id_flex_num = p_structure.structure_number
4395 AND application_column_name = p_segment.column_name;
4396
4397 -- Delete compiled definition.
4398
4399 delete_compiled_definition(l_app.application_id, p_flexfield.flex_code, p_structure.structure_number);
4400
4401 END delete_awc;
4402
4403
4404 FUNCTION awc_exists(p_flexfield IN flexfield_type,
4405 p_structure IN structure_type,
4406 p_segment IN segment_type,
4407 p_tag IN varchar2)
4408 RETURN BOOLEAN IS
4409 l_numof_awc_elements NUMBER := 0;
4410 l_awc_elements awc_elements_type;
4411
4412 BEGIN
4413
4414 -- Get the existing tags and clauses.
4415
4416 get_awc_elements
4417 (p_flexfield => p_flexfield,
4418 p_structure => p_structure,
4419 p_segment => p_segment,
4420 x_numof_awc_elements => l_numof_awc_elements,
4421 x_awc_elements => l_awc_elements);
4422
4423 FOR i in 1 .. l_numof_awc_elements LOOP
4424 IF (l_awc_elements(i).tag = p_tag) THEN
4425 RETURN TRUE;
4426 END IF;
4427 END LOOP;
4428
4429 RETURN FALSE;
4430
4431 END awc_exists;
4432
4433 BEGIN
4434 fnd_plsql_cache.generic_1to1_init('KAI.SOQ',
4435 soq_cache_controller,
4436 soq_cache_storage);
4437
4438 END fnd_flex_key_api;