[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_DSC_API
Source
1 PACKAGE BODY fnd_flex_dsc_api AS
2 /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
3
4
5
6 bad_parameter EXCEPTION;
7 PRAGMA EXCEPTION_INIT(bad_parameter, -06501);
8
9 value_too_large EXCEPTION;
10 PRAGMA EXCEPTION_INIT(value_too_large, -01401);
11
12
13 who_mode VARCHAR2(1000) := NULL; /* whether customer_data or seed_data */
14 debug_mode_on BOOLEAN := FALSE;
15 do_validation BOOLEAN := TRUE;
16 internal_messages VARCHAR2(10000);
17 chr_newline VARCHAR2(8) := fnd_global.newline;
18
19 -- The following subtypes are used in the APIs rename_dff and migrate_dff
20 SUBTYPE fnd_app_type IS fnd_application%ROWTYPE;
21 SUBTYPE fnd_tbl_type IS fnd_tables%ROWTYPE;
22 SUBTYPE fnd_dff_type IS fnd_descriptive_flexs%ROWTYPE;
23
24 -- The following constants are used as error constants
25 error_context_not_set CONSTANT NUMBER := -20001;
26 error_same_dff_name CONSTANT NUMBER := -20002;
27 error_invalid_dff_name CONSTANT NUMBER := -20003;
28 error_dff_already_exists CONSTANT NUMBER := -20004;
29 error_same_table_name CONSTANT NUMBER := -20005;
30 error_col_already_regis CONSTANT NUMBER := -20006;
31 error_col_not_registered CONSTANT NUMBER := -20007;
32 error_col_wrong_type CONSTANT NUMBER := -20008;
33 error_col_wrong_size CONSTANT NUMBER := -20009;
34 error_srs_dff CONSTANT NUMBER := -20009;
35 error_others CONSTANT NUMBER := -20100;
36
37 --cur_lang fnd_languages.nls_language%TYPE := fnd_global.current_language;
38 --cur_lang fnd_languages.language_code%TYPE := userenv('LANG');
39 CURSOR lang_cur IS
40 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
41 language_code
42 FROM fnd_languages
43 WHERE installed_flag IN ('I', 'B');
44
45
46 /* ------------------------------------------------------------ */
47 /* messaging */
48 /* ------------------------------------------------------------ */
49
50
51 PROCEDURE debug_on IS
52 BEGIN
53 debug_mode_on := TRUE;
54 END;
55
56 PROCEDURE debug_off IS
57 BEGIN
58 debug_mode_on := FALSE;
59 END;
60
61 PROCEDURE set_validation(v_in IN BOOLEAN) IS
62 BEGIN
63 do_validation := v_in;
64 END;
65
66
67 PROCEDURE message(msg VARCHAR2) IS
68 BEGIN
69 internal_messages := internal_messages || msg || fnd_global.newline;
70 -- internal_messages := internal_messages || Sqlerrm; /* error stamp */
71 END;
72
73 PROCEDURE message_init IS
74 BEGIN
75 internal_messages := '';
76 END;
77
78
79
80 FUNCTION version RETURN VARCHAR2 IS
81 BEGIN
82 RETURN('$Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $');
83 END;
84
85
86
87 FUNCTION message RETURN VARCHAR2 IS
88 BEGIN
89 RETURN internal_messages;
90 END;
91
92 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
93 IS
94 i INTEGER;
95 m INTEGER;
96 c INTEGER := 75;
97 BEGIN
98 execute immediate ('begin dbms' ||
99 '_output' ||
100 '.enable(1000000); end;');
101 m := Ceil(Length(p_debug)/c);
102 FOR i IN 1..m LOOP
103 execute immediate ('begin dbms' ||
104 '_output' ||
105 '.put_line(''' ||
106 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
107 '''); end;');
108 END LOOP;
109 EXCEPTION
110 WHEN OTHERS THEN
111 NULL;
112 END dbms_debug;
113
114 /* only used in testing */
115 PROCEDURE println(msg IN VARCHAR2) IS
116 BEGIN
117 IF(debug_mode_on) THEN
118 dbms_debug(msg);
119 END IF;
120 END;
121
122 /* ------------------------------------------------------------ */
123 /* who information */
124 /* ------------------------------------------------------------ */
125
126
127 PROCEDURE set_session_mode(session_mode IN VARCHAR2) IS
128 BEGIN
129 IF(session_mode NOT IN ('customer_data', 'seed_data')) THEN
130 message('bad mode:'|| session_mode);
131 message('valid values are: customer_data, seed_data');
132 RAISE bad_parameter;
133 END IF;
134 who_mode := session_mode;
135 END;
136
137
138 FUNCTION customer_mode RETURN BOOLEAN IS
139 BEGIN
140 IF(who_mode = 'customer_data') THEN
141 RETURN TRUE;
142 ELSIF(who_mode = 'seed_data') THEN
143 RETURN FALSE;
144 ELSE
145 message('bad session mode:' || who_mode);
146 message('use set_session_mode to specify');
147 RAISE bad_parameter;
148 END IF;
149 END;
150
151
152 FUNCTION created_by_f RETURN NUMBER IS
153 BEGIN
154 IF(customer_mode) THEN
155 RETURN 0;
156 ELSE
157 RETURN 1;
158 END IF;
159 END;
160
161 FUNCTION creation_date_f RETURN DATE IS
162 BEGIN
163 IF(customer_mode) THEN
164 RETURN Sysdate;
165 ELSE
166 RETURN To_date('01011980', 'MMDDYYYY');
167 END IF;
168 END;
169
170
171 FUNCTION last_update_date_f RETURN DATE IS
172 BEGIN
173 RETURN creation_date_f;
174 END;
175
176 FUNCTION last_updated_by_f RETURN NUMBER IS
177 BEGIN
178 RETURN created_by_f;
179 END;
180
181 FUNCTION last_update_login_f RETURN NUMBER IS
182 BEGIN
183 RETURN 0;
184 END;
185
186
187
188 /* ====================================================================== */
189
190 FUNCTION application_id_f(application_short_name_in IN VARCHAR2)
191 RETURN fnd_application.application_id%TYPE
192 IS
193 application_id_ret fnd_application.application_id%TYPE;
194 BEGIN
195 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
196 application_id
197 INTO application_id_ret
198 FROM fnd_application
199 WHERE application_short_name = application_short_name_in;
200
201 RETURN application_id_ret;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 message('error locating application id');
206 IF application_short_name_in IS NULL THEN
207 message('must specify appl_short_name');
208 ELSE
209 message('appl_short_name:' || application_short_name_in);
210 END IF;
211 RAISE bad_parameter;
212 END;
213
214 /* ---------------------------------------------------------------------- */
215
216 FUNCTION table_id_f(application_id_in IN fnd_tables.application_id%TYPE,
217 table_name_in IN VARCHAR2)
218 RETURN fnd_tables.table_id%TYPE
219 IS
220 table_id_ret fnd_tables.table_id%TYPE;
221 BEGIN
222 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
223 table_id
224 INTO table_id_ret
225 FROM fnd_tables
226 WHERE table_name = table_name_in
227 AND application_id = application_id_in;
228 RETURN table_id_ret;
229 EXCEPTION
230 WHEN no_data_found THEN
231 message('bad table name:' || table_name_in);
232 RAISE bad_parameter;
233 END;
234
235 /* ---------------------------------------------------------------------- */
236
237 PROCEDURE value_set_id_f(
238 value_set_name IN VARCHAR2,
239 value_set_id OUT nocopy fnd_flex_value_sets.flex_value_set_id%TYPE)
240 IS
241 BEGIN
242 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
243 flex_value_set_id
244 INTO value_set_id
245 FROM fnd_flex_value_sets
246 WHERE flex_value_set_name = value_set_name;
247 EXCEPTION
248 WHEN no_data_found THEN
249 message('bad valueset name:' || value_set_name);
250 RAISE bad_parameter;
251 END;
252
253
254 /* ====================================================================== */
255
256
257 /* check whether the named descr ff exists in the specified app */
258 PROCEDURE check_existance(
259 application_id_in IN fnd_application.application_id%TYPE,
260 descriptive_flexfield_name_in IN VARCHAR2)
261 IS
262 dummy NUMBER(1);
263 BEGIN
264 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
265 NULL INTO dummy
266 FROM fnd_descriptive_flexs
267 WHERE application_id = application_id_in
268 AND descriptive_flexfield_name = descriptive_flexfield_name_in;
269 EXCEPTION
270 WHEN no_data_found THEN
271 message('bad descriptive flexfield name:' ||
272 descriptive_flexfield_name_in);
273 RAISE bad_parameter;
274 END;
275
276 /* ---------------------------------------------------------------------- */
277
278 /* check whether the context exists in the
279 named descr ff in the specified app */
280 PROCEDURE check_existance(
281 application_id_in IN fnd_application.application_id%TYPE,
282 descriptive_flexfield_name_in IN VARCHAR2,
283 descr_flex_context_code_in IN VARCHAR2)
284 IS
285 dummy NUMBER(1);
286 BEGIN
287 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
288 NULL INTO dummy
289 FROM fnd_descr_flex_contexts
290 WHERE application_id = application_id_in
291 AND descriptive_flexfield_name = descriptive_flexfield_name_in
292 AND descriptive_flex_context_code = descr_flex_context_code_in;
293 EXCEPTION
294 WHEN no_data_found THEN
295 check_existance(application_id_in, descriptive_flexfield_name_in);
296 message('bad descriptive context name:' ||
297 descr_flex_context_code_in);
298 RAISE bad_parameter;
299 END;
300
301
302 /* ------------------------------------------------------------ */
303 /* insert functions */
304 /* ------------------------------------------------------------ */
305
306
307
308 PROCEDURE ins_descriptive_flexs(
309 application_id_in IN NUMBER,
310 application_table_name IN VARCHAR2,
311 descriptive_flexfield_name IN VARCHAR2,
312 table_application_id IN NUMBER,
313 concatenated_segs_view_name IN VARCHAR2,
314 context_required_flag IN VARCHAR2,
315 context_column_name IN VARCHAR2,
316 context_user_override_flag IN VARCHAR2,
317 concatenated_segment_delimiter IN VARCHAR2,
318 freeze_flex_definition_flag IN VARCHAR2,
319 protected_flag IN VARCHAR2,
320 default_context_field_name IN VARCHAR2,
321 default_context_value IN VARCHAR2)
322 IS
323 last_update_login fnd_flex_value_sets.last_update_login%TYPE
324 := last_update_login_f;
325 last_update_date fnd_flex_value_sets.last_update_date%TYPE
326 := last_update_date_f;
327 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
328 := last_updated_by_f;
329 creation_date fnd_flex_value_sets.creation_date%TYPE
330 := creation_date_f;
331 created_by fnd_flex_value_sets.created_by%TYPE
332 := created_by_f;
333 dummy NUMBER(1);
334 BEGIN
335 /* assume valid application_id, table_application_id */
336 BEGIN
337 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
338 NULL INTO dummy
339 FROM fnd_tables
340 WHERE table_name = application_table_name
341 AND application_id = table_application_id;
342 EXCEPTION
343 WHEN no_data_found THEN
344 message('bad application table name:'||application_table_name);
345 RAISE bad_parameter;
346 END;
347
348 INSERT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
349 INTO fnd_descriptive_flexs(application_id,
350 application_table_name,
351 descriptive_flexfield_name,
352 table_application_id,
353 concatenated_segs_view_name,
354 last_update_date,
355 last_updated_by,
356 creation_date,
357 created_by,
358 last_update_login,
359 context_required_flag,
360 context_synchronization_flag,
361 context_column_name,
362 context_user_override_flag,
363 concatenated_segment_delimiter,
364 freeze_flex_definition_flag,
365 protected_flag,
366 default_context_field_name,
367 default_context_value)
368 VALUES(application_id_in,
369 application_table_name,
370 descriptive_flexfield_name,
371 table_application_id,
372 concatenated_segs_view_name,
373 last_update_date,
374 last_updated_by,
375 creation_date,
376 created_by,
377 last_update_login,
378 context_required_flag,
379 'X',
380 context_column_name,
381 context_user_override_flag,
382 concatenated_segment_delimiter,
383 freeze_flex_definition_flag,
384 protected_flag,
385 default_context_field_name,
386 default_context_value);
387 println('inserted into fnd_descriptive_flexs');
388 EXCEPTION
389 WHEN dup_val_on_index THEN
390 message('insert to fnd_descriptive_flexs failed - ' ||
391 'duplicate flexfield name or application id');
392 RAISE bad_parameter;
393 WHEN value_too_large THEN
394 message('insert to fnd_descriptive_flexs failed - ' ||
395 'value too large');
396 RAISE bad_parameter;
397 END;
398
399
400 /* modify the fields that are normally input in the
401 * segments form rather than the register descriptive
402 * flexfields form. */
403 PROCEDURE upd_descriptive_flexs(
404 application_id_in IN NUMBER,
405 descriptive_flexfield_name_in IN VARCHAR2,
406
407 context_required_flag_in IN VARCHAR2,
408 context_user_override_flag_in IN VARCHAR2,
409 concat_segment_delimiter_in IN VARCHAR2,
410 freeze_flex_definition_flag_in IN VARCHAR2,
411 default_context_field_name_in IN VARCHAR2,
412 default_context_value_in IN VARCHAR2,
413 p_context_default_type IN VARCHAR2,
414 p_context_default_value IN VARCHAR2,
415 p_context_override_value_set_i IN NUMBER,
416 p_context_runtime_property_fun IN VARCHAR2)
417 IS
418 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
419 := last_update_login_f;
420 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
421 := last_update_date_f;
422 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
423 := last_updated_by_f;
424 BEGIN
425 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
426 fnd_descriptive_flexs SET
427 context_required_flag = context_required_flag_in,
428 context_user_override_flag = context_user_override_flag_in,
429 concatenated_segment_delimiter = concat_segment_delimiter_in,
430 freeze_flex_definition_flag = freeze_flex_definition_flag_in,
431 default_context_field_name = default_context_field_name_in,
432 default_context_value = default_context_value_in,
433 context_default_type = p_context_default_type,
434 context_default_value = p_context_default_value,
435 context_override_value_set_id = p_context_override_value_set_i,
436 context_runtime_property_funct = p_context_runtime_property_fun
437 WHERE application_id = application_id_in
438 AND descriptive_flexfield_name = descriptive_flexfield_name_in;
439 IF(customer_mode) THEN
440 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
441 fnd_descriptive_flexs SET
442 last_update_date = last_update_date_i,
443 last_updated_by = last_updated_by_i,
444 last_update_login = last_update_login_i
445 WHERE application_id = application_id_in
446 AND descriptive_flexfield_name = descriptive_flexfield_name_in;
447 END IF;
448 EXCEPTION
449 WHEN OTHERS THEN
450 message('update on fnd_descriptive_flexs failed');
451 RAISE bad_parameter;
452 END;
453
454
455
456 /* insert records, one for each installed language */
457 PROCEDURE insmul_descriptive_flexs_tl(
458 application_id IN NUMBER,
459 descriptive_flexfield_name IN VARCHAR2,
460 title IN VARCHAR2,
461 description IN VARCHAR2,
462 form_context_prompt IN VARCHAR2)
463 IS
464 last_update_login fnd_flex_value_sets.last_update_login%TYPE
465 := last_update_login_f;
466 last_update_date fnd_flex_value_sets.last_update_date%TYPE
467 := last_update_date_f;
468 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
469 := last_updated_by_f;
470 creation_date fnd_flex_value_sets.creation_date%TYPE
471 := creation_date_f;
472 created_by fnd_flex_value_sets.created_by%TYPE
473 := created_by_f;
474 BEGIN
475 FOR lang_rec IN lang_cur
476 LOOP
477 println('inserting into fnd_descriptive flexs tl');
478 println('application_id ='|| application_id);
479 println('descriptive_flexfield_name ='|| descriptive_flexfield_name);
480 println('title ='|| title);
481 println('form_context_prompt ='|| form_context_prompt);
482 println('lang_rec.language_code ='|| lang_rec.language_code);
483 INSERT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
484 INTO fnd_descriptive_flexs_tl(application_id,
485 descriptive_flexfield_name,
486 title,
487 description,
488 form_context_prompt,
489 language,
490 last_update_date,
491 last_updated_by,
492 creation_date,
493 created_by,
494 last_update_login,
495 source_lang)
496 VALUES(application_id,
497 descriptive_flexfield_name,
498 title,
499 description,
500 form_context_prompt,
501 lang_rec.language_code,
502 last_update_date,
503 last_updated_by,
504 creation_date,
505 created_by,
506 last_update_login,
507 userenv('LANG'));
508 -- println('inserted into fnd_descriptive_flexs_tl');
509 END LOOP;
510 EXCEPTION
511 WHEN dup_val_on_index THEN
512 message('insert failed - duplicate language, flexfield name, or application id');
513 RAISE bad_parameter;
514 WHEN value_too_large THEN
515 message('insert failed - value too large');
516 RAISE bad_parameter;
517 WHEN OTHERS THEN
518 message('insmul_descriptive_flexs_tl: ' || Sqlerrm);
519 RAISE;
520 END;
521
522
523 PROCEDURE ins_descr_flex_contexts(
524 application_id IN NUMBER,
525 descriptive_flexfield_name IN VARCHAR2,
526 descriptive_flex_context_code IN VARCHAR2,
527 enabled_flag IN VARCHAR2,
528 global_flag IN VARCHAR2,
529 description IN VARCHAR2)
530 IS
531 last_update_login fnd_flex_value_sets.last_update_login%TYPE
532 := last_update_login_f;
533 last_update_date fnd_flex_value_sets.last_update_date%TYPE
534 := last_update_date_f;
535 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
536 := last_updated_by_f;
537 creation_date fnd_flex_value_sets.creation_date%TYPE
538 := creation_date_f;
539 created_by fnd_flex_value_sets.created_by%TYPE
540 := created_by_f;
541 BEGIN
542 INSERT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
543 INTO fnd_descr_flex_contexts(application_id,
544 descriptive_flexfield_name,
545 descriptive_flex_context_code,
546 last_update_date,
547 last_updated_by,
548 creation_date,
549 created_by,
550 last_update_login,
551 enabled_flag,
552 global_flag)
553 VALUES(application_id,
554 descriptive_flexfield_name,
555 descriptive_flex_context_code,
556 last_update_date,
557 last_updated_by,
558 creation_date,
559 created_by,
560 last_update_login,
561 enabled_flag,
562 global_flag);
563 EXCEPTION
564 WHEN dup_val_on_index THEN
565 message('insert failed - duplicate value on index');
566 RAISE bad_parameter;
567 WHEN value_too_large THEN
568 message('insert failed - value too large');
569 RAISE bad_parameter;
570 END;
571
572
573
574 PROCEDURE insmul_descr_flex_contexts_tl(
575 application_id IN NUMBER,
576 descriptive_flexfield_name IN VARCHAR2,
577 descriptive_flex_context_code IN VARCHAR2,
578 descriptive_flex_context_name IN VARCHAR2,
579 description IN VARCHAR2)
580 IS
581 last_update_login fnd_flex_value_sets.last_update_login%TYPE
582 := last_update_login_f;
583 last_update_date fnd_flex_value_sets.last_update_date%TYPE
584 := last_update_date_f;
585 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
586 := last_updated_by_f;
587 creation_date fnd_flex_value_sets.creation_date%TYPE
588 := creation_date_f;
589 created_by fnd_flex_value_sets.created_by%TYPE
590 := created_by_f;
591 BEGIN
592 FOR lang_rec IN lang_cur
593 LOOP
594 println('application_id=' || application_id);
595 println('descriptive_flexfield_name=' ||
596 descriptive_flexfield_name);
597 println('descriptive_flex_context_code=' ||
598 descriptive_flex_context_code);
599 println('descriptive_flex_context_name=' ||
600 descriptive_flex_context_name);
601 println('lang_rec.language_code=' ||
602 lang_rec.language_code);
603 -- println('userenv('LANG')=' || userenv('LANG'));
604 INSERT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
605 INTO fnd_descr_flex_contexts_tl(application_id,
606 descriptive_flexfield_name,
607 descriptive_flex_context_code,
608 descriptive_flex_context_name,
609 description,
610 language,
611 last_update_date,
612 last_updated_by,
613 creation_date,
614 created_by,
615 last_update_login,
616 source_lang)
617 VALUES(application_id,
618 descriptive_flexfield_name,
619 descriptive_flex_context_code,
620 descriptive_flex_context_name,
621 description,
622 lang_rec.language_code,
623 last_update_date,
624 last_updated_by,
625 creation_date,
626 created_by,
627 last_update_login,
628 userenv('LANG'));
629 END LOOP;
630 EXCEPTION
631 WHEN dup_val_on_index THEN
632 message('insert to fnd_descr_flex_contexts failed - ' ||
633 'duplicate value on index');
634 RAISE bad_parameter;
635 WHEN value_too_large THEN
636 message('insert to fnd_descr_flex_contexts failed - ' ||
637 'value too large');
638 RAISE bad_parameter;
639 WHEN OTHERS THEN
640 message('insmul_descr_flex_contexts_tl: ' || Sqlerrm);
641 RAISE;
642 END;
643
644
645 PROCEDURE ins_descr_flex_column_usages(
646 application_id IN NUMBER,
647 descriptive_flexfield_name IN VARCHAR2,
648 descriptive_flex_context_code IN VARCHAR2,
649 application_column_name IN VARCHAR2,
650 end_user_column_name IN VARCHAR2,
651 column_seq_num IN NUMBER,
652 enabled_flag IN VARCHAR2,
653 required_flag IN VARCHAR2,
654 security_enabled_flag IN VARCHAR2,
655 display_flag IN VARCHAR2,
656 display_size IN NUMBER,
657 maximum_description_len IN NUMBER,
658 concatenation_description_len IN NUMBER,
659 form_left_prompt IN VARCHAR2,
660 form_above_prompt IN VARCHAR2,
661 description IN VARCHAR2,
662 flex_value_set_id IN NUMBER,
663 range_code IN VARCHAR2,
664 default_type IN VARCHAR2,
665 default_value IN VARCHAR2,
666 runtime_property_function IN VARCHAR2,
667 srw_param IN VARCHAR2)
668 IS
669 last_update_login fnd_flex_value_sets.last_update_login%TYPE
670 := last_update_login_f;
671 last_update_date fnd_flex_value_sets.last_update_date%TYPE
672 := last_update_date_f;
673 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
674 := last_updated_by_f;
675 creation_date fnd_flex_value_sets.creation_date%TYPE
676 := creation_date_f;
677 created_by fnd_flex_value_sets.created_by%TYPE
678 := created_by_f;
679 BEGIN
680 INSERT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
681 INTO fnd_descr_flex_column_usages(application_id,
682 descriptive_flexfield_name,
683 descriptive_flex_context_code,
684 application_column_name,
685 end_user_column_name,
686 last_update_date,
687 last_updated_by,
688 creation_date,
689 created_by,
690 last_update_login,
691 column_seq_num,
692 enabled_flag,
693 required_flag,
694 security_enabled_flag,
695 display_flag,
696 display_size,
697 maximum_description_len,
698 concatenation_description_len,
699 flex_value_set_id,
700 range_code,
701 default_type,
702 default_value,
703 runtime_property_function,
704 srw_param)
705 VALUES(application_id,
706 descriptive_flexfield_name,
707 descriptive_flex_context_code,
708 application_column_name,
709 end_user_column_name,
710 last_update_date,
711 last_updated_by,
712 creation_date,
713 created_by,
714 last_update_login,
715 column_seq_num,
716 enabled_flag,
717 required_flag,
718 security_enabled_flag,
719 display_flag,
720 display_size,
721 maximum_description_len,
722 concatenation_description_len,
723 flex_value_set_id,
724 range_code,
725 default_type,
726 default_value,
727 runtime_property_function,
728 srw_param);
729 EXCEPTION
730 WHEN dup_val_on_index THEN
731 message('insert failed - duplicate value on index');
732 RAISE bad_parameter;
733 WHEN value_too_large THEN
734 message('insert failed - value too large');
735 RAISE bad_parameter;
736 END;
737
738
739 PROCEDURE insmul_descr_flex_col_usage_tl(
740 application_id IN NUMBER,
741 descriptive_flexfield_name IN VARCHAR2,
742 descriptive_flex_context_code IN VARCHAR2,
743 application_column_name IN VARCHAR2,
744 form_left_prompt IN VARCHAR2,
745 form_above_prompt IN VARCHAR2,
746 description IN VARCHAR2)
747 IS
748 last_update_login fnd_flex_value_sets.last_update_login%TYPE
749 := last_update_login_f;
750 last_update_date fnd_flex_value_sets.last_update_date%TYPE
751 := last_update_date_f;
752 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
753 := last_updated_by_f;
754 creation_date fnd_flex_value_sets.creation_date%TYPE
755 := creation_date_f;
756 created_by fnd_flex_value_sets.created_by%TYPE
757 := created_by_f;
758 BEGIN
759 FOR lang_rec IN lang_cur
760 LOOP
761 INSERT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
762 INTO fnd_descr_flex_col_usage_tl(application_id,
763 descriptive_flexfield_name,
764 descriptive_flex_context_code,
765 application_column_name,
766 form_left_prompt,
767 form_above_prompt,
768 description,
769 language,
770 last_update_date,
771 last_updated_by,
772 creation_date,
773 created_by,
774 last_update_login,
775 source_lang)
776 VALUES(application_id,
777 descriptive_flexfield_name,
778 descriptive_flex_context_code,
779 application_column_name,
780 form_left_prompt,
781 form_above_prompt,
782 description,
783 lang_rec.language_code,
784 last_update_date,
785 last_updated_by,
786 creation_date,
787 created_by,
788 last_update_login,
789 userenv('LANG'));
790 END LOOP;
791 EXCEPTION
792 WHEN dup_val_on_index THEN
793 message('insert failed - duplicate value on index');
794 RAISE bad_parameter;
795 WHEN value_too_large THEN
796 message('insert failed - value too large');
797 RAISE bad_parameter;
798 END;
799
800
801
802 PROCEDURE ins_default_context_fields(application_id_in IN NUMBER,
803 flexfield_name_in IN VARCHAR2,
804 context_field_name_in IN VARCHAR2,
805 description_in IN VARCHAR2)
806 IS
807 last_update_login fnd_flex_value_sets.last_update_login%TYPE
808 := last_update_login_f;
809 last_update_date fnd_flex_value_sets.last_update_date%TYPE
810 := last_update_date_f;
811 last_updated_by fnd_flex_value_sets.last_updated_by%TYPE
812 := last_updated_by_f;
813 creation_date fnd_flex_value_sets.creation_date%TYPE
814 := creation_date_f;
815 created_by fnd_flex_value_sets.created_by%TYPE
816 := created_by_f;
817 BEGIN
818 INSERT
819 INTO fnd_default_context_fields(application_id,
820 descriptive_flexfield_name,
821 default_context_field_name,
822 last_update_date,
823 last_updated_by,
824 creation_date,
825 created_by,
826 last_update_login,
827 description)
828 VALUES(application_id_in,
829 flexfield_name_in,
830 context_field_name_in,
831 last_update_date,
832 last_updated_by,
833 creation_date,
834 created_by,
835 last_update_login,
836 description_in);
837 END;
838
839
840 /* ------------------------------------------------------------ */
841 /* more validation */
842 /* ------------------------------------------------------------ */
843
844
845 /* figure out whether the column name can be used in this context
846 of this flexfield */
847 PROCEDURE validate_column_name(application_id_in IN NUMBER,
848 descriptive_flexfield_name_in IN VARCHAR2,
849 descr_flex_context_code_in IN VARCHAR2,
850 application_column_name_in IN VARCHAR2)
851 IS
852 global_context_code_i
853 fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
854 srsprefix VARCHAR2(10) := '$SRS$.';
855 dummy NUMBER(1);
856 BEGIN
857 -- check whether the column name is usable with this
858 -- flexfield
859 BEGIN
860 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
861 NULL INTO dummy
862 FROM fnd_columns c, fnd_tables t, fnd_descriptive_flexs df
863 WHERE df.application_id = application_id_in
864 AND df.descriptive_flexfield_name = descriptive_flexfield_name_in
865 AND t.application_id = df.table_application_id
866 AND t.table_name = df.application_table_name
867 AND c.table_id = t.table_id
868 AND c.application_id = t.application_id
869 AND c.column_name = application_column_name_in
870 AND c.flexfield_name = descriptive_flexfield_name_in
871 AND c.flexfield_application_id = application_id_in
872 AND c.flexfield_usage_code = 'D';
873 EXCEPTION
874 WHEN no_data_found THEN
875 -- SRS can get away with this, because we don't have all
876 -- the information to check.
877 IF(Substr(descriptive_flexfield_name_in, 1,
878 (Length(srsprefix))) <> srsprefix) THEN
879 message('The column name is not usable with this flexfield');
880 message('application id:' || application_id_in);
881 message('ff name:' || descriptive_flexfield_name_in);
882 message('ccode:' || descr_flex_context_code_in);
883 message('column name:' || application_column_name_in);
884 RAISE bad_parameter;
885 END IF;
886 END;
887
888 -- get the global context name, for use later
889 BEGIN
890 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
891 descriptive_flex_context_code
892 INTO global_context_code_i
893 FROM fnd_descr_flex_contexts
894 WHERE application_id = application_id_in
895 AND descriptive_flexfield_name = descriptive_flexfield_name_in
896 AND global_flag = 'Y';
897 EXCEPTION
898 WHEN no_data_found THEN
899 message('could not find a global context for this flexfield');
900 RAISE bad_parameter;
901 WHEN too_many_rows THEN
902 message('more than one global context detected');
903 RAISE bad_parameter;
904 END;
905
906 -- make sure the context is not already being used
907 BEGIN
908 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
909 NULL INTO dummy FROM dual
910 WHERE NOT EXISTS
911 (SELECT NULL
912 FROM fnd_descr_flex_column_usages cu
913 WHERE cu.application_id = application_id_in
914 AND cu.descriptive_flexfield_name = descriptive_flexfield_name_in
915 AND cu.application_column_name = application_column_name_in
916 AND ( -- already in use in the current context
917 (descriptive_flex_context_code = descr_flex_context_code_in)
918 OR -- already in the global context
919 (descriptive_flex_context_code = global_context_code_i)
920 OR -- we are in the global context
921 (descr_flex_context_code_in = global_context_code_i)));
922 EXCEPTION
923 WHEN no_data_found THEN
924 message('incompatible parameters detected');
925 RAISE bad_parameter;
926 END;
927 END;
928
929
930 PROCEDURE check_context_field(application_id_in IN NUMBER,
931 descriptive_flexfield_name_in IN VARCHAR2,
932 context_field_in IN VARCHAR2)
933 IS
934 dummy VARCHAR2(1);
935 BEGIN
936 -- null is also allowed for the field name
937 IF(context_field_in IS NOT NULL) THEN
938 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
939 NULL INTO dummy
940 FROM fnd_default_context_fields
941 WHERE application_id = application_id_in
942 AND descriptive_flexfield_name = descriptive_flexfield_name_in
943 AND default_context_field_name = context_field_in;
944 END IF;
945 EXCEPTION
946 WHEN no_data_found THEN
947 message('could not locate context field in fnd_default_context_fields:'
948 || context_field_in);
949 RAISE bad_parameter;
950 END;
951
952
953 /* ------------------------------------------------------------ */
954 /* public functions */
955 /* ------------------------------------------------------------ */
956
957 PROCEDURE register(appl_short_name IN VARCHAR2,
958 flexfield_name IN VARCHAR2,
959 title IN VARCHAR2,
960 description IN VARCHAR2,
961 table_appl_short_name IN VARCHAR2,
962 table_name IN VARCHAR2,
963 structure_column IN VARCHAR2,
964 /* context_prompt overwritten in setup_context */
965 context_prompt IN VARCHAR2 DEFAULT 'Context Value',
966 protected_flag IN VARCHAR2 DEFAULT 'N',
967 enable_columns IN VARCHAR2 DEFAULT NULL,
968 concatenated_segs_view_name IN VARCHAR2 DEFAULT NULL)
969 IS
970 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
971 := last_update_login_f;
972 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
973 := last_update_date_f;
974 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
975 := last_updated_by_f;
976 application_id_i fnd_application.application_id%TYPE;
977 table_application_id_i fnd_application.application_id%TYPE;
978 application_table_id_i fnd_tables.table_id%TYPE;
979 itisSRS BOOLEAN;
980 numcount NUMBER;
981 ffucode fnd_columns.flexfield_usage_code%TYPE;
982 BEGIN
983 itisSRS := (SUBSTR(flexfield_name,1,6) = '$SRS$.');
984 if (itisSRS is NULL) then
985 itisSRS := FALSE;
986 end if;
987 message_init;
988 println('starting registration for:' || flexfield_name);
989 application_id_i := application_id_f(appl_short_name);
990 println('application id:' || To_char(application_id_i));
991 table_application_id_i := application_id_f(table_appl_short_name);
992 println('table application id:' || To_char(table_application_id_i));
993 ins_descriptive_flexs(
994 application_id_in => application_id_i,
995 application_table_name => table_name,
996 descriptive_flexfield_name => flexfield_name,
997 table_application_id => table_application_id_i,
998 concatenated_segs_view_name => concatenated_segs_view_name,
999 context_required_flag => 'N',
1000 context_column_name => structure_column,
1001 context_user_override_flag => 'Y',
1002 concatenated_segment_delimiter => '.',
1003 freeze_flex_definition_flag => 'N',
1004 protected_flag => protected_flag,
1005 default_context_field_name => NULL,
1006 default_context_value => NULL);
1007
1008 insmul_descriptive_flexs_tl(application_id => application_id_i,
1009 descriptive_flexfield_name => flexfield_name,
1010 title => title,
1011 description => description,
1012 form_context_prompt => Nvl(context_prompt, flexfield_name));
1013
1014 application_table_id_i := table_id_f(table_application_id_i,
1015 table_name);
1016 println('table_id:' || To_char(application_table_id_i));
1017 /* fix the columns (enabled) */
1018 /* WARNING: the value set information is not set here */
1019
1020 IF (itisSRS) THEN
1021 BEGIN
1022 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1023 flexfield_usage_code INTO ffucode
1024 FROM fnd_columns
1025 WHERE application_id = register.table_application_id_i
1026 AND table_id = register.application_table_id_i
1027 -- AND flexfield_usage_code = 'N'
1028 AND column_name = register.structure_column;
1029 IF (ffucode = 'N') THEN
1030 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1031 fnd_columns SET
1032 flexfield_usage_code = 'C'
1033 WHERE application_id = register.table_application_id_i
1034 AND table_id = register.application_table_id_i
1035 -- AND flexfield_usage_code = 'N'
1036 AND column_name = register.structure_column;
1037 ELSIF (ffucode <> 'C') THEN
1038 message('column '||structure_column||
1039 ': $SRS$ context column is registered with'||
1040 ' different code. :'||ffucode);
1041 RAISE bad_parameter;
1042 END IF;
1043 EXCEPTION
1044 WHEN no_data_found THEN
1045 message('column '||structure_column||
1046 ': $SRS$ context column is not registered in fnd_columns.');
1047 RAISE no_data_found;
1048 WHEN OTHERS THEN
1049 message('register : Error in $SRS$. structure column');
1050 RAISE;
1051 END;
1052 ELSE
1053 BEGIN
1054 IF(customer_mode) THEN
1055 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1056 fnd_columns SET
1057 last_update_date = last_update_date_i,
1058 last_updated_by = last_updated_by_i,
1059 last_update_login = last_update_login_i
1060 WHERE application_id = register.table_application_id_i
1061 AND table_id = register.application_table_id_i
1062 AND flexfield_usage_code = 'N'
1063 AND column_name = register.structure_column;
1064 IF SQL%ROWCOUNT <> 1 THEN
1065 message('column '||structure_column||
1066 ' could not be assigned as a context column');
1067 RAISE bad_parameter;
1068 END IF;
1069 END IF;
1070
1071 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1072 fnd_columns SET
1073 flexfield_usage_code = 'C',
1074 flexfield_application_id = register.application_id_i,
1075 flexfield_name = register.flexfield_name
1076 WHERE application_id = register.table_application_id_i
1077 AND table_id = register.application_table_id_i
1078 AND flexfield_usage_code = 'N'
1079 AND column_name = register.structure_column;
1080 IF SQL%ROWCOUNT <> 1 THEN
1081 message('column '||structure_column||
1082 ' could not be assigned as a context column');
1083 RAISE bad_parameter;
1084 END IF;
1085 END;
1086 END IF;
1087
1088
1089 -- enable columns named /ATTRIBUTE[0-9]*/
1090 IF (itisSRS) THEN
1091 BEGIN
1092 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1093 fnd_columns SET
1094 flexfield_usage_code = 'D'
1095 WHERE application_id = register.table_application_id_i
1096 AND table_id = register.application_table_id_i
1097 AND flexfield_usage_code = 'N'
1098 AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
1099 AND ((column_name IS NOT NULL)
1100 OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
1101
1102 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1103 COUNT(*) INTO numcount
1104 FROM fnd_columns
1105 WHERE application_id = register.table_application_id_i
1106 AND table_id = register.application_table_id_i
1107 AND flexfield_usage_code = 'D'
1108 AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
1109 AND ((column_name IS NOT NULL)
1110 OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
1111
1112 IF (numcount = 0) THEN
1113 message('register : no attribute columns available for $SRS$.');
1114 RAISE bad_parameter;
1115 END IF;
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 message('register : Error in $SRS$. attribute columns');
1119 RAISE;
1120 END;
1121 ELSE
1122 BEGIN
1123 IF(customer_mode) THEN
1124 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1125 fnd_columns SET
1126 last_update_date = register.last_update_date_i,
1127 last_updated_by = register.last_updated_by_i,
1128 last_update_login = register.last_update_login_i
1129 WHERE application_id = register.table_application_id_i
1130 AND table_id = register.application_table_id_i
1131 AND flexfield_usage_code = 'N'
1132 AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
1133 AND ((column_name IS NOT NULL)
1134 OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
1135
1136 IF SQL%ROWCOUNT = 0 THEN
1137 message('no attribute columns available');
1138 RAISE bad_parameter;
1139 END IF;
1140
1141 END IF;
1142
1143 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1144 fnd_columns SET
1145 flexfield_usage_code = 'D',
1146 flexfield_application_id = register.application_id_i,
1147 flexfield_name = register.flexfield_name
1148 WHERE application_id = register.table_application_id_i
1149 AND table_id = register.application_table_id_i
1150 AND flexfield_usage_code = 'N'
1151 AND column_name LIKE Nvl(enable_columns, 'ATTRIBUTE%')
1152 AND ((column_name IS NOT NULL)
1153 OR (Rtrim(column_name, '0123456789') = 'ATTRIBUTE'));
1154 IF SQL%ROWCOUNT = 0 THEN
1155 message('no attribute columns available');
1156 RAISE bad_parameter;
1157 END IF;
1158
1159 END;
1160 END IF;
1161 /* now create the global context */
1162 println('about to create default context');
1163 create_context(
1164 appl_short_name => appl_short_name,
1165 flexfield_name => flexfield_name,
1166 context_code => 'Global Data Elements',
1167 context_name => 'Global Data Elements',
1168 description => 'Global Data Element Context',
1169 enabled => 'Y',
1170 global_flag => 'Y');
1171 println('registered flexfield:' || flexfield_name);
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 message('register: ' || Sqlerrm);
1175 RAISE;
1176 END;
1177
1178
1179
1180 /* ------------------------------------------------------------ */
1181 PROCEDURE enable_columns(appl_short_name IN VARCHAR2,
1182 flexfield_name IN VARCHAR2,
1183 pattern IN VARCHAR2)
1184 IS
1185 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
1186 := last_update_login_f;
1187 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
1188 := last_update_date_f;
1189 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
1190 := last_updated_by_f;
1191 application_id_i fnd_application.application_id%TYPE;
1192 table_application_id_i fnd_application.application_id%TYPE;
1193 application_table_id_i fnd_tables.table_id%TYPE;
1194 BEGIN
1195 message_init;
1196 println('enabling columns for:' || flexfield_name);
1197 application_id_i := application_id_f(appl_short_name);
1198 BEGIN
1199 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1200 table_application_id, table_id
1201 INTO table_application_id_i, application_table_id_i
1202 FROM fnd_descriptive_flexs df, fnd_tables t
1203 WHERE df.application_id = application_id_i
1204 AND df.descriptive_flexfield_name = flexfield_name
1205 AND t.application_id = df.table_application_id
1206 AND t.table_name = df.application_table_name;
1207 EXCEPTION
1208 WHEN no_data_found THEN
1209 message('could not lookup table information for: ' ||
1210 flexfield_name);
1211 RAISE bad_parameter;
1212 END;
1213 BEGIN
1214 IF(NOT customer_mode) THEN
1215 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1216 fnd_columns SET
1217 flexfield_usage_code = 'D',
1218 flexfield_application_id = application_id_i,
1219 flexfield_name = enable_columns.flexfield_name
1220 WHERE application_id = table_application_id_i
1221 AND table_id = application_table_id_i
1222 AND flexfield_usage_code = 'N'
1223 AND column_name LIKE pattern;
1224 ELSE
1225 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1226 fnd_columns SET
1227 flexfield_usage_code = 'D',
1228 flexfield_application_id = application_id_i,
1229 flexfield_name = enable_columns.flexfield_name,
1230 last_update_date = last_update_date_i,
1231 last_updated_by = last_updated_by_i,
1232 last_update_login = last_update_login_i
1233 WHERE application_id = table_application_id_i
1234 AND table_id = application_table_id_i
1235 AND flexfield_usage_code = 'N'
1236 AND column_name LIKE pattern;
1237 END IF;
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240 message('could not update fnd_columns:' ||
1241 Sqlerrm);
1242 RAISE bad_parameter;
1243 END;
1244 END;
1245
1246 /* ------------------------------------------------------------ */
1247
1248 PROCEDURE setup_context_field(appl_short_name IN VARCHAR2,
1249 flexfield_name IN VARCHAR2,
1250 segment_separator IN VARCHAR2,
1251 prompt IN VARCHAR2 DEFAULT 'Context Value',
1252 default_value IN VARCHAR2,
1253 reference_field IN VARCHAR2,
1254 value_required IN VARCHAR2,
1255 override_allowed IN VARCHAR2,
1256 freeze_flexfield_definition IN VARCHAR2 DEFAULT 'N',
1257 context_default_type IN VARCHAR2 DEFAULT NULL,
1258 context_default_value IN VARCHAR2 DEFAULT NULL,
1259 context_override_value_set_nam IN VARCHAR2 DEFAULT NULL,
1260 context_runtime_property_funct IN VARCHAR2 DEFAULT NULL)
1261 IS
1262 application_id_i fnd_application.application_id%TYPE;
1263 l_context_override_value_set_i NUMBER;
1264 BEGIN
1265 message_init;
1266 application_id_i := application_id_f(appl_short_name);
1267 IF(do_validation) THEN
1268 check_context_field(application_id_i,
1269 flexfield_name,
1270 reference_field);
1271 END IF;
1272
1273 IF (context_override_value_set_nam IS NOT NULL) THEN
1274 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1275 flex_value_set_id
1276 INTO l_context_override_value_set_i
1277 FROM fnd_flex_value_sets
1278 WHERE flex_value_set_name = context_override_value_set_nam;
1279 END IF;
1280
1281 upd_descriptive_flexs(
1282 application_id_in => application_id_i,
1283 descriptive_flexfield_name_in => flexfield_name,
1284 context_required_flag_in => value_required,
1285 context_user_override_flag_in => override_allowed,
1286 concat_segment_delimiter_in => segment_separator,
1287 freeze_flex_definition_flag_in => freeze_flexfield_definition,
1288 default_context_field_name_in => reference_field,
1289 default_context_value_in => default_value,
1290 p_context_default_type => context_default_type,
1291 p_context_default_value => context_default_value,
1292 p_context_override_value_set_i => l_context_override_value_set_i,
1293 p_context_runtime_property_fun => context_runtime_property_funct);
1294 IF(prompt IS NOT NULL) THEN
1295 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1296 fnd_descriptive_flexs_tl
1297 SET
1298 form_context_prompt = prompt
1299 WHERE application_id = application_id_i
1300 AND descriptive_flexfield_name = flexfield_name;
1301 END IF;
1302
1303 println('setup context info for flexfield:' || flexfield_name);
1304 END;
1305
1306
1307 /* ------------------------------------------------------------ */
1308
1309 PROCEDURE freeze(appl_short_name IN VARCHAR2,
1310 flexfield_name IN VARCHAR2)
1311 IS
1312 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
1313 := last_update_login_f;
1314 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
1315 := last_update_date_f;
1316 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
1317 := last_updated_by_f;
1318 appid fnd_application.application_id%TYPE;
1319 BEGIN
1320 message_init;
1321 appid := application_id_f(appl_short_name);
1322 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1323 fnd_descriptive_flexs SET
1324 last_update_date = last_update_date_i,
1325 last_updated_by = last_updated_by_i,
1326 last_update_login = last_update_login_i,
1327 freeze_flex_definition_flag = 'Y'
1328 WHERE application_id = appid
1329 AND flexfield_name = descriptive_flexfield_name;
1330 println('froze ff:'|| flexfield_name);
1331 END;
1332
1333 /* ------------------------------------------------------------ */
1334
1335 PROCEDURE create_context(appl_short_name IN VARCHAR2,
1336 flexfield_name IN VARCHAR2,
1337 /* data */
1338 context_code IN VARCHAR2,
1339 context_name IN VARCHAR2,
1340 description IN VARCHAR2,
1341 enabled IN VARCHAR2,
1342 global_flag IN VARCHAR2 DEFAULT 'N')
1343 IS
1344 application_id_i fnd_application.application_id%TYPE;
1345 BEGIN
1346 message_init;
1347 application_id_i := application_id_f(appl_short_name);
1348 check_existance(application_id_i, flexfield_name);
1349 println('inserting into desc flex contexts');
1350 ins_descr_flex_contexts(
1351 application_id => application_id_i,
1352 descriptive_flexfield_name => flexfield_name,
1353 descriptive_flex_context_code => context_code,
1354 enabled_flag => enabled,
1355 global_flag => global_flag,
1356 description => description);
1357 println('inserting into desc flex contexts tl');
1358 insmul_descr_flex_contexts_tl(
1359 application_id => application_id_i,
1360 descriptive_flexfield_name => flexfield_name,
1361 descriptive_flex_context_code => context_code,
1362 descriptive_flex_context_name => context_name,
1363 description => description);
1364 println('created context:' || context_name);
1365 END;
1366
1367 /* ------------------------------------------------------------ */
1368
1369
1370 PROCEDURE create_segment(appl_short_name IN VARCHAR2,
1371 flexfield_name IN VARCHAR2,
1372 context_name IN VARCHAR2,
1373 /* data */
1374 name IN VARCHAR2,
1375 column IN VARCHAR2,
1376 description IN VARCHAR2,
1377 sequence_number IN NUMBER,
1378 enabled IN VARCHAR2,
1379 displayed IN VARCHAR2,
1380 /* validation */
1381 value_set IN VARCHAR2,
1382 default_type IN VARCHAR2,
1383 default_value IN VARCHAR2,
1384 required IN VARCHAR2,
1385 security_enabled IN VARCHAR2,
1386 /* sizes */
1387 display_size IN NUMBER,
1388 description_size IN NUMBER,
1389 concatenated_description_size IN NUMBER,
1390 list_of_values_prompt IN VARCHAR2,
1391 window_prompt IN VARCHAR2,
1392 RANGE IN VARCHAR2 DEFAULT NULL,
1393 srw_parameter IN VARCHAR2 DEFAULT NULL,
1394 runtime_property_function IN VARCHAR2 DEFAULT NULL)
1395 IS
1396 application_id_i fnd_application.application_id%TYPE;
1397 default_type_i fnd_descr_flex_column_usages.default_type%TYPE;
1398 value_set_id_i fnd_flex_value_sets.flex_value_set_id%TYPE;
1399 range_code_i fnd_descr_flex_column_usages.range_code%TYPE;
1400 dummy NUMBER(1);
1401 BEGIN
1402 message_init;
1403 application_id_i := application_id_f(appl_short_name);
1404
1405 IF(do_validation) THEN
1406 -- check that the context exists
1407 check_existance(application_id_in => application_id_i,
1408 descriptive_flexfield_name_in => flexfield_name,
1409 descr_flex_context_code_in => context_name);
1410
1411 -- make sure it's a valid value set
1412 IF(value_set IS NOT NULL) THEN
1413 value_set_id_f(value_set_name => value_set,
1414 value_set_id => value_set_id_i);
1415 END IF;
1416 END IF;
1417
1418 -- check the range code
1419 BEGIN
1420 IF(range IS NOT NULL) THEN
1421 BEGIN
1422 fnd_flex_types.validate_range_code(range);
1423 range_code_i := range;
1424 EXCEPTION
1425 -- maybe it's still old style
1426 WHEN no_data_found THEN
1427 println('WARNING: old style parameter: range');
1428 range_code_i := fnd_flex_types.get_code(typ => 'RANGE_CODES',
1429 descr => range);
1430 END;
1431 ELSIF(value_set IS NOT NULL) THEN
1432 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1433 Decode(validation_type, 'P', 'P', NULL)
1434 INTO range_code_i
1435 FROM fnd_flex_value_sets v
1436 WHERE v.flex_value_set_id = value_set_id_i;
1437 END IF;
1438 EXCEPTION
1439 WHEN no_data_found THEN
1440 message('bad range specified');
1441 RAISE bad_parameter;
1442 END;
1443
1444 IF(do_validation AND value_set IS NOT NULL) THEN
1445 BEGIN
1446 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1447 NULL
1448 INTO dummy
1449 FROM fnd_flex_value_sets v
1450 WHERE v.flex_value_set_id = value_set_id_i
1451 AND Decode(v.validation_type, 'P', 'P', 'A')
1452 = Decode(range_code_i, 'P', 'P', 'A');
1453 EXCEPTION
1454 WHEN no_data_found THEN
1455 message('range code pair required with pair validated value set');
1456 RAISE bad_parameter;
1457 END;
1458 -- make sure the value set can be used in this segment
1459 DECLARE
1460 application_column_type_i
1461 fnd_columns.column_type%TYPE;
1462 application_column_width_i
1463 fnd_columns.width%TYPE;
1464 application_table_name_i
1465 fnd_descriptive_flexs.application_table_name%TYPE;
1466 BEGIN
1467 -- get information on the table column
1468 BEGIN
1469 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1470 c.column_type, c.width,
1471 df.application_table_name
1472 INTO application_column_type_i, application_column_width_i,
1473 application_table_name_i
1474 FROM fnd_columns c, fnd_tables t, fnd_descriptive_flexs df
1475 WHERE c.application_id = t.application_id
1476 AND c.table_id = t.table_id
1477 AND c.column_name = create_segment.column
1478 AND t.table_name = df.application_table_name
1479 AND t.application_id = df.table_application_id
1480 AND df.application_id = create_segment.application_id_i
1481 AND df.descriptive_flexfield_name = create_segment.flexfield_name;
1482 EXCEPTION
1483 WHEN OTHERS THEN
1484 message('error getting information on the column:'||
1485 column);
1486 RAISE bad_parameter;
1487 END;
1488
1489 -- check the validation type and the format type
1490 -- this should really be in the value sets package
1491 BEGIN
1492 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1493 NULL INTO dummy
1494 FROM fnd_flex_value_sets v, fnd_lookup_values vt,
1495 fnd_lookup_values ft
1496 WHERE v.flex_value_set_id = value_set_id_i
1497 AND vt.lookup_type = 'SEG_VAL_TYPES'
1498 AND vt.lookup_code = v.validation_type
1499 AND ft.lookup_type = 'FIELD_TYPE'
1500 AND ft.lookup_code = v.format_type
1501 AND ROWNUM = 1;
1502 EXCEPTION
1503 WHEN no_data_found THEN
1504 message('bad validation type or format type');
1505 message('value set:' || value_set);
1506 RAISE bad_parameter;
1507 END;
1508 BEGIN
1509 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1510 NULL INTO dummy
1511 FROM fnd_flex_value_sets v, fnd_flex_validation_tables t
1512 WHERE v.flex_value_set_id = value_set_id_i
1513 AND v.flex_value_set_id = t.flex_value_set_id (+)
1514 AND (application_column_type_i IN ('C', 'V')
1515 OR v.validation_type = 'U'
1516 OR application_column_type_i = Nvl(t.id_column_type,
1517 Decode(v.format_type,
1518 'M', 'N',
1519 'T', 'D',
1520 'I', 'D',
1521 'X', 'D',
1522 'Y', 'D',
1523 'Z', 'D',
1524 v.format_type)))
1525 AND (application_column_type_i = 'D'
1526 OR application_column_width_i
1527 >= Nvl(t.id_column_size, v.maximum_size))
1528 AND(v.validation_type <> 'D'
1529 OR EXISTS (SELECT NULL
1530 FROM fnd_descr_flex_column_usages u
1531 WHERE application_id = application_id_i
1532 AND descriptive_flexfield_name
1533 = create_segment.flexfield_name
1534 AND descriptive_flex_context_code
1535 = create_segment.context_name
1536 AND u.flex_value_set_id
1537 = v.parent_flex_value_set_id));
1538 EXCEPTION
1539 WHEN no_data_found THEN
1540 message('this value set cannot be used with this column');
1541 message('value set:' || value_set);
1542 message('column:' || column);
1543 message('table:' || application_table_name_i);
1544 message('application_column_type_i:'||application_column_type_i);
1545 message('application_column_width_i:' ||
1546 application_column_width_i);
1547 RAISE bad_parameter;
1548 WHEN OTHERS THEN
1549 message('possible size or format mismatch');
1550 RAISE bad_parameter;
1551 END;
1552 END;
1553 END IF; /* do_validation and value_set not null */
1554
1555
1556
1557 -- validate default type name
1558 BEGIN
1559 IF(default_type IS NOT NULL) THEN
1560 BEGIN
1561 fnd_flex_types.validate_default_type(default_type);
1562 default_type_i := default_type;
1563 EXCEPTION
1564 -- maybe it's still old style
1565 WHEN no_data_found THEN
1566 println('WARNING: old style parameter: default_type');
1567 default_type_i :=
1568 fnd_flex_types.get_code(typ => 'FLEX_DEFAULT_TYPE',
1569 descr => create_segment.default_type);
1570 END;
1571 END IF;
1572 EXCEPTION
1573 WHEN no_data_found THEN
1574 message('could not create segment - bad type:' || default_type);
1575 RAISE bad_parameter;
1576 END;
1577 IF((default_type IS NULL) AND (default_value IS NOT NULL)) THEN
1578 message('default type required when default value specified');
1579 RAISE bad_parameter;
1580 END IF;
1581 -- check that the column name can be used
1582 validate_column_name(application_id_in => application_id_i,
1583 descriptive_flexfield_name_in => flexfield_name,
1584 descr_flex_context_code_in => context_name,
1585 application_column_name_in => column);
1586
1587 ins_descr_flex_column_usages(
1588 application_id => application_id_i,
1589 descriptive_flexfield_name => flexfield_name,
1590 descriptive_flex_context_code => context_name,
1591 application_column_name => column,
1592 end_user_column_name => name,
1593 column_seq_num => sequence_number,
1594 enabled_flag => enabled,
1595 required_flag => required,
1596 security_enabled_flag => security_enabled,
1597 display_flag => displayed,
1598 display_size => display_size,
1599 maximum_description_len => description_size,
1600 concatenation_description_len => concatenated_description_size,
1601 form_left_prompt => window_prompt,
1602 form_above_prompt => list_of_values_prompt,
1603 description => description,
1604 flex_value_set_id => value_set_id_i,
1605 range_code => range_code_i,
1606 default_type => default_type_i,
1607 default_value => default_value,
1608 runtime_property_function => runtime_property_function,
1609 srw_param => srw_parameter);
1610 insmul_descr_flex_col_usage_tl(
1611 application_id => application_id_i,
1612 descriptive_flexfield_name => flexfield_name,
1613 descriptive_flex_context_code => context_name,
1614 application_column_name => column,
1615 form_left_prompt => window_prompt,
1616 form_above_prompt => list_of_values_prompt,
1617 description => description);
1618 println('created segment:' || context_name || '->'
1619 || column || '(' || window_prompt || ')');
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 message('error in create_segment:' || SQLCODE);
1623 message(Sqlerrm);
1624 message('descriptive flexfield name:'|| flexfield_name);
1625 message('application column name:'|| column);
1626 RAISE bad_parameter;
1627 END;
1628
1629 PROCEDURE modify_segment
1630 (-- PK for segment
1631 p_appl_short_name IN VARCHAR2,
1632 p_flexfield_name IN VARCHAR2,
1633 p_context_code IN VARCHAR2,
1634 p_segment_name IN VARCHAR2 DEFAULT NULL,
1635 p_column_name IN VARCHAR2 DEFAULT NULL,
1636 -- Data
1637 p_description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1638 p_sequence_number IN NUMBER DEFAULT fnd_api.g_miss_num,
1639 p_enabled IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1640 p_displayed IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1641 -- Validation
1642 p_value_set IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1643 p_default_type IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1644 p_default_value IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1645 p_required IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1646 p_security_enabled IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1647 -- Sizes
1648 p_display_size IN NUMBER DEFAULT fnd_api.g_miss_num,
1649 p_description_size IN NUMBER DEFAULT fnd_api.g_miss_num,
1650 p_concat_desc_size IN NUMBER DEFAULT fnd_api.g_miss_num,
1651 p_lov_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1652 p_window_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1653 p_range IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1654 p_srw_parameter IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
1655 p_runtime_property_function IN VARCHAR2 DEFAULT fnd_api.g_miss_char)
1656 IS
1657 l_application_id fnd_descr_flex_col_usage_vl.application_id%TYPE;
1658 l_description fnd_descr_flex_col_usage_vl.description%TYPE;
1659 l_sequence_number fnd_descr_flex_col_usage_vl.column_seq_num%TYPE;
1660 l_enabled fnd_descr_flex_col_usage_vl.enabled_flag%TYPE;
1661 l_displayed fnd_descr_flex_col_usage_vl.display_flag%TYPE;
1662 l_default_type fnd_descr_flex_col_usage_vl.default_type%TYPE;
1663 l_default_value fnd_descr_flex_col_usage_vl.default_value%TYPE;
1664 l_required fnd_descr_flex_col_usage_vl.required_flag%TYPE;
1665 l_security_enabled fnd_descr_flex_col_usage_vl.security_enabled_flag%TYPE;
1666 l_display_size fnd_descr_flex_col_usage_vl.display_size%TYPE;
1667 l_description_size fnd_descr_flex_col_usage_vl.maximum_description_len%TYPE;
1668 l_concat_desc_size fnd_descr_flex_col_usage_vl.concatenation_description_len%TYPE;
1669 l_lov_prompt fnd_descr_flex_col_usage_vl.form_above_prompt%TYPE;
1670 l_window_prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
1671 l_range fnd_descr_flex_col_usage_vl.range_code%TYPE;
1672 l_srw_parameter fnd_descr_flex_col_usage_vl.srw_param%TYPE;
1673 l_runtime_property_function fnd_descr_flex_col_usage_vl.runtime_property_function%TYPE;
1674 l_flex_value_set_id fnd_descr_flex_col_usage_vl.flex_value_set_id%TYPE;
1675 dummy VARCHAR2(100);
1676 l_segment_name fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE;
1677 l_column_name fnd_descr_flex_col_usage_vl.application_column_name%TYPE;
1678 l_column_type fnd_columns.column_type%TYPE;
1679 l_column_width fnd_columns.width%TYPE;
1680 l_table_name fnd_descriptive_flexs.application_table_name%TYPE;
1681
1682 l_last_update_login fnd_descr_flex_col_usage_vl.last_update_login%TYPE
1683 := last_update_login_f;
1684 l_last_update_date fnd_descr_flex_col_usage_vl.last_update_date%TYPE
1685 := last_update_date_f;
1686 l_last_updated_by fnd_descr_flex_col_usage_vl.last_updated_by%TYPE
1687 := last_updated_by_f;
1688 BEGIN
1689 message_init;
1690 l_application_id := application_id_f(p_appl_short_name);
1691 IF (NOT segment_exists(p_appl_short_name,
1692 p_flexfield_name,
1693 p_context_code,
1694 p_segment_name,
1695 p_column_name)) THEN
1696 message('Segment does not exist.');
1697 RAISE bad_parameter;
1698 END IF;
1699 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1700 description, column_seq_num, enabled_flag,
1701 display_flag, flex_value_set_id, default_type,
1702 default_value, required_flag, security_enabled_flag,
1703 display_size, maximum_description_len, concatenation_description_len,
1704 form_above_prompt, form_left_prompt, range_code, srw_param,
1705 runtime_property_function,
1706 application_column_name,
1707 end_user_column_name
1708 INTO l_description, l_sequence_number, l_enabled,
1709 l_displayed, l_flex_value_set_id, l_default_type,
1710 l_default_value, l_required, l_security_enabled,
1711 l_display_size, l_description_size, l_concat_desc_size,
1712 l_lov_prompt, l_window_prompt, l_range, l_srw_parameter,
1713 l_runtime_property_function,
1714 l_column_name,
1715 l_segment_name
1716 FROM fnd_descr_flex_col_usage_vl
1717 WHERE application_id = l_application_id
1718 AND descriptive_flexfield_name = p_flexfield_name
1719 AND descriptive_flex_context_code = p_context_code
1720 AND (((p_column_name IS NOT NULL) AND
1721 (application_column_name = p_column_name)) OR
1722 ((p_segment_name IS NOT NULL) AND
1723 (end_user_column_name = p_segment_name)));
1724
1725 IF (p_description = fnd_api.g_miss_char) THEN
1726 NULL;
1727 ELSE
1728 l_description := p_description;
1729 END IF;
1730
1731 IF (p_sequence_number = fnd_api.g_miss_num) THEN
1732 NULL;
1733 ELSE
1734 l_sequence_number := p_sequence_number;
1735 END IF;
1736
1737 IF (p_enabled = fnd_api.g_miss_char) THEN
1738 NULL;
1739 ELSE
1740 fnd_flex_types.validate_yes_no_flag(p_enabled);
1741 l_enabled := p_enabled;
1742 END IF;
1743
1744 IF (p_displayed = fnd_api.g_miss_char) THEN
1745 NULL;
1746 ELSE
1747 fnd_flex_types.validate_yes_no_flag(p_displayed);
1748 l_displayed := p_displayed;
1749 END IF;
1750
1751 IF (p_value_set = fnd_api.g_miss_char) THEN
1752 NULL;
1753 ELSE
1754 value_set_id_f(p_value_set,
1755 l_flex_value_set_id);
1756 -- check the validation type and the format type
1757 -- this should really be in the value sets package
1758 BEGIN
1759 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1760 NULL INTO dummy
1761 FROM fnd_flex_value_sets v, fnd_lookup_values vt,
1762 fnd_lookup_values ft
1763 WHERE v.flex_value_set_id = l_flex_value_set_id
1764 AND vt.lookup_type = 'SEG_VAL_TYPES'
1765 AND vt.lookup_code = v.validation_type
1766 AND ft.lookup_type = 'FIELD_TYPE'
1767 AND ft.lookup_code = v.format_type
1768 AND ROWNUM = 1;
1769 EXCEPTION
1770 WHEN no_data_found THEN
1771 message('bad validation type or format type');
1772 message('value set:' || p_value_set);
1773 RAISE bad_parameter;
1774 END;
1775
1776 -- Read the column information.
1777 BEGIN
1778 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1779 c.column_type, c.width, df.application_table_name
1780 INTO l_column_type, l_column_width, l_table_name
1781 FROM fnd_columns c, fnd_tables t, fnd_descriptive_flexs df
1782 WHERE c.application_id = t.application_id
1783 AND c.table_id = t.table_id
1784 AND c.column_name = l_column_name
1785 AND t.table_name = df.application_table_name
1786 AND t.application_id = df.table_application_id
1787 AND df.application_id = l_application_id
1788 AND df.descriptive_flexfield_name = p_flexfield_name;
1789 EXCEPTION
1790 WHEN OTHERS THEN
1791 message('error getting information on the column:'||
1792 l_column_name);
1793 RAISE bad_parameter;
1794 END;
1795
1796 -- check column type.
1797 BEGIN
1798 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1799 NULL INTO dummy
1800 FROM fnd_flex_value_sets v, fnd_flex_validation_tables t
1801 WHERE v.flex_value_set_id = l_flex_value_set_id
1802 AND v.flex_value_set_id = t.flex_value_set_id (+)
1803 AND (l_column_type IN ('C', 'V')
1804 OR v.validation_type = 'U'
1805 OR l_column_type = Nvl(t.id_column_type,
1806 Decode(v.format_type,
1807 'M', 'N',
1808 'T', 'D',
1809 'I', 'D',
1810 'X', 'D',
1811 'Y', 'D',
1812 'Z', 'D',
1813 v.format_type)))
1814 AND (l_column_type = 'D'
1815 OR l_column_width
1816 >= Nvl(t.id_column_size, v.maximum_size))
1817 AND(v.validation_type <> 'D'
1818 OR EXISTS (SELECT NULL
1819 FROM fnd_descr_flex_column_usages u
1820 WHERE application_id = l_application_id
1821 AND descriptive_flexfield_name
1822 = p_flexfield_name
1823 AND descriptive_flex_context_code
1824 = p_context_code
1825 AND u.flex_value_set_id
1826 = v.parent_flex_value_set_id));
1827 EXCEPTION
1828 WHEN no_data_found THEN
1829 message('this value set cannot be used with this column');
1830 message('value set:' || p_value_set);
1831 message('column:' || l_column_name);
1832 message('table:' || l_table_name);
1833 message('column type:'|| l_column_type);
1834 message('column width:' || l_column_width);
1835 RAISE bad_parameter;
1836 WHEN OTHERS THEN
1837 message('possible size or format mismatch');
1838 RAISE bad_parameter;
1839 END;
1840 END IF;
1841
1842 -- Bug 13457601 Added "OR p_default_type is NULL"
1843 IF (p_default_type = fnd_api.g_miss_char OR p_default_type is NULL) THEN
1844 NULL;
1845 ELSE
1846 fnd_flex_types.validate_default_type(p_default_type);
1847 l_default_type := p_default_type;
1848 END IF;
1849
1850 IF (p_default_value = fnd_api.g_miss_char) THEN
1851 NULL;
1852 ELSE
1853 l_default_value := p_default_value;
1854 END IF;
1855
1856 IF (p_required = fnd_api.g_miss_char) THEN
1857 NULL;
1858 ELSE
1859 fnd_flex_types.validate_yes_no_flag(p_required);
1860 l_required := p_required;
1861 END IF;
1862
1863 IF (p_security_enabled = fnd_api.g_miss_char) THEN
1864 NULL;
1865 ELSE
1866 fnd_flex_types.validate_yes_no_flag(p_security_enabled);
1867 l_security_enabled := p_security_enabled;
1868 END IF;
1869
1870 IF (p_display_size = fnd_api.g_miss_num) THEN
1871 NULL;
1872 ELSE
1873 l_display_size := p_display_size;
1874 END IF;
1875
1876 IF (p_description_size = fnd_api.g_miss_num) THEN
1877 NULL;
1878 ELSE
1879 l_description_size := p_description_size;
1880 END IF;
1881
1882 IF (p_concat_desc_size = fnd_api.g_miss_num) THEN
1883 NULL;
1884 ELSE
1885 l_concat_desc_size := p_concat_desc_size;
1886 END IF;
1887
1888 IF (p_lov_prompt = fnd_api.g_miss_char) THEN
1889 NULL;
1890 ELSE
1891 l_lov_prompt := p_lov_prompt;
1892 END IF;
1893
1894 IF (p_window_prompt = fnd_api.g_miss_char) THEN
1895 NULL;
1896 ELSE
1897 l_window_prompt := p_window_prompt;
1898 END IF;
1899
1900 IF (p_runtime_property_function = fnd_api.g_miss_char) THEN
1901 NULL;
1902 ELSE
1903 l_runtime_property_function := p_runtime_property_function;
1904 END IF;
1905
1906 IF (p_range = fnd_api.g_miss_char) THEN
1907 NULL;
1908 ELSE
1909 BEGIN
1910 IF (p_range IS NOT NULL) THEN
1911 fnd_flex_types.validate_range_code(p_range);
1912 l_range := p_range;
1913 ELSIF (l_flex_value_set_id IS NOT NULL) THEN
1914 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1915 Decode(validation_type, 'P', 'P', NULL)
1916 INTO l_range
1917 FROM fnd_flex_value_sets
1918 WHERE flex_value_set_id = l_flex_value_set_id;
1919 END IF;
1920 EXCEPTION
1921 WHEN no_data_found THEN
1922 message('bad range specified');
1923 RAISE bad_parameter;
1924 END;
1925 IF (l_flex_value_set_id IS NOT NULL) THEN
1926 BEGIN
1927 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
1928 NULL
1929 INTO dummy
1930 FROM fnd_flex_value_sets
1931 WHERE flex_value_set_id = l_flex_value_set_id
1932 AND Decode(validation_type, 'P', 'P', 'A') =
1933 Decode(l_range, 'P', 'P', 'A');
1934 EXCEPTION
1935 WHEN no_data_found THEN
1936 message('range pair required with pair validated value set');
1937 RAISE bad_parameter;
1938 END;
1939 END IF;
1940 END IF;
1941
1942 fnd_descr_flex_col_usage_pkg.update_row
1943 (x_application_id => l_application_id,
1944 x_descriptive_flexfield_name => p_flexfield_name,
1945 x_descriptive_flex_context_cod => p_context_code,
1946 x_application_column_name => l_column_name,
1947 x_end_user_column_name => l_segment_name,
1948 x_column_seq_num => l_sequence_number,
1949 x_enabled_flag => l_enabled,
1950 x_required_flag => l_required,
1951 x_security_enabled_flag => l_security_enabled,
1952 x_display_flag => l_displayed,
1953 x_display_size => l_display_size,
1954 x_maximum_description_len => l_description_size,
1955 x_concatenation_description_le => l_concat_desc_size,
1956 x_flex_value_set_id => l_flex_value_set_id,
1957 x_range_code => l_range,
1958 x_default_type => l_default_type,
1959 x_default_value => l_default_value,
1960 x_runtime_property_function => l_runtime_property_function,
1961 x_srw_param => l_srw_parameter,
1962 x_form_left_prompt => l_window_prompt,
1963 x_form_above_prompt => l_lov_prompt,
1964 x_description => l_description,
1965 x_last_update_date => l_last_update_date,
1966 x_last_updated_by => l_last_updated_by,
1967 x_last_update_login => l_last_update_login);
1968 EXCEPTION
1969 WHEN OTHERS THEN
1970 message('modify_segment exception. SQLERRM : ' || Sqlerrm);
1971 RAISE;
1972 END modify_segment;
1973
1974 PROCEDURE create_reference_field(appl_short_name IN VARCHAR2,
1975 flexfield_name IN VARCHAR2,
1976 context_field_name IN VARCHAR2,
1977 description IN VARCHAR2)
1978 IS
1979 application_id_i fnd_application.application_id%TYPE;
1980 BEGIN
1981 message_init;
1982 application_id_i := application_id_f(appl_short_name);
1983
1984 IF(do_validation) THEN
1985 -- check that the dff exists
1986 NULL;
1987 END IF;
1988 ins_default_context_fields(application_id_in => application_id_i,
1989 flexfield_name_in => flexfield_name,
1990 context_field_name_in => context_field_name,
1991 description_in => description);
1992
1993 EXCEPTION
1994 WHEN OTHERS THEN
1995 message('error in create_reference_field: ' || SQLCODE);
1996 message(Sqlerrm);
1997 RAISE bad_parameter;
1998 END;
1999
2000
2001 PROCEDURE delete_reference_field(appl_short_name IN VARCHAR2,
2002 flexfield_name IN VARCHAR2,
2003 context_field_name IN VARCHAR2)
2004 IS
2005 application_id_i fnd_application.application_id%TYPE;
2006 rec_count NUMBER;
2007 BEGIN
2008 message_init;
2009 application_id_i := application_id_f(appl_short_name);
2010 IF(do_validation) THEN
2011 -- A row cannot be deleted if the context field name
2012 -- is used as the default context field name for the
2013 -- descriptive flexfield.
2014 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2015 count('x')
2016 INTO rec_count
2017 FROM fnd_descriptive_flexs
2018 WHERE application_id = application_id_i
2019 AND descriptive_flexfield_name = flexfield_name
2020 AND default_context_field_name = context_field_name;
2021
2022 IF (rec_count > 0) THEN
2023 message('this reference field is in use in a flexfield');
2024 RAISE bad_parameter;
2025 END IF;
2026 END IF;
2027 END;
2028
2029 PROCEDURE disable_columns(appl_short_name IN VARCHAR2,
2030 flexfield_name IN VARCHAR2,
2031 pattern IN VARCHAR2)
2032 IS
2033 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
2034 := last_update_login_f;
2035 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
2036 := last_update_date_f;
2037 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
2038 := last_updated_by_f;
2039 application_id_i fnd_application.application_id%TYPE;
2040 table_application_id_i fnd_application.application_id%TYPE;
2041 application_table_id_i fnd_tables.table_id%TYPE;
2042 BEGIN
2043 message_init;
2044 println('disabling columns for:' || flexfield_name);
2045 application_id_i := application_id_f(appl_short_name);
2046 BEGIN
2047 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2048 table_application_id, table_id
2049 INTO table_application_id_i, application_table_id_i
2050 FROM fnd_descriptive_flexs df, fnd_tables t
2051 WHERE df.application_id = application_id_i
2052 AND df.descriptive_flexfield_name = flexfield_name
2053 AND t.application_id = df.table_application_id
2054 AND t.table_name = df.application_table_name;
2055 EXCEPTION
2056 WHEN no_data_found THEN
2057 message('could not lookup table information for: ' ||
2058 flexfield_name);
2059 RAISE bad_parameter;
2060 END;
2061 BEGIN
2062 IF(NOT customer_mode) THEN
2063 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2064 fnd_columns SET
2065 flexfield_usage_code = 'N',
2066 flexfield_application_id = NULL,
2067 flexfield_name = NULL
2068 WHERE application_id = table_application_id_i
2069 AND table_id = application_table_id_i
2070 AND flexfield_usage_code = 'D'
2071 AND column_name LIKE pattern;
2072 ELSE
2073 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2074 fnd_columns SET
2075 flexfield_usage_code = 'N',
2076 flexfield_application_id = application_id_i,
2077 flexfield_name = disable_columns.flexfield_name,
2078 last_update_date = last_update_date_i,
2079 last_updated_by = last_updated_by_i,
2080 last_update_login = last_update_login_i
2081 WHERE application_id = table_application_id_i
2082 AND table_id = application_table_id_i
2083 AND flexfield_usage_code = 'D'
2084 AND column_name LIKE pattern;
2085 END IF;
2086 EXCEPTION
2087 WHEN OTHERS THEN
2088 message('could not update fnd_columns:' ||
2089 Sqlerrm);
2090 RAISE bad_parameter;
2091 END;
2092 END;
2093
2094 PROCEDURE delete_flexfield(appl_short_name IN VARCHAR2,
2095 flexfield_name IN VARCHAR2)
2096 IS
2097 application_id_i fnd_application.application_id%TYPE;
2098 table_application_id_i fnd_application.application_id%TYPE;
2099 BEGIN
2100 message_init;
2101 application_id_i := application_id_f(appl_short_name);
2102
2103 BEGIN
2104 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2105 FROM fnd_descr_flex_col_usage_tl
2106 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2107 AND application_id = application_id_i;
2108 println('deleted from fnd_descr_col_usage_tl');
2109 END;
2110
2111 BEGIN
2112 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2113 FROM fnd_descr_flex_column_usages
2114 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2115 AND application_id = application_id_i;
2116 println('deleted from fnd_descr_column_usages');
2117 END;
2118
2119 BEGIN
2120 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2121 FROM fnd_descr_flex_contexts_tl
2122 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2123 AND application_id = application_id_i;
2124 println('deleted from fnd_descr_flex_contexts_tl');
2125 END;
2126
2127 BEGIN
2128 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2129 FROM fnd_descr_flex_contexts
2130 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2131 AND application_id = application_id_i;
2132 println('deleted from fnd_descr_flex_contexts');
2133 END;
2134
2135 BEGIN
2136 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2137 FROM fnd_descriptive_flexs_tl
2138 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2139 AND application_id = application_id_i;
2140 println('deleted from fnd_descriptive_flexs_tl');
2141 END;
2142
2143 BEGIN
2144 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2145 table_application_id
2146 INTO table_application_id_i
2147 FROM fnd_descriptive_flexs
2148 WHERE descriptive_flexfield_name = flexfield_name
2149 AND application_id = application_id_i;
2150
2151 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2152 fnd_columns SET
2153 flexfield_usage_code = 'N',
2154 flexfield_name = NULL
2155 WHERE flexfield_name = delete_flexfield.flexfield_name
2156 AND application_id = table_application_id_i;
2157 println('updated fnd_columns');
2158 EXCEPTION
2159 WHEN no_data_found THEN
2160 NULL;
2161 END;
2162
2163 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2164 FROM fnd_descriptive_flexs
2165 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2166 AND application_id = application_id_i;
2167 println('deleted from fnd_descriptive_flexs');
2168
2169 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2170 FROM fnd_default_context_fields
2171 WHERE application_id = application_id_i
2172 AND descriptive_flexfield_name = flexfield_name;
2173 println('deleted from fnd_default_context_fields');
2174
2175 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2176 FROM fnd_compiled_descriptive_flexs
2177 WHERE application_id = application_id_i
2178 AND descriptive_flexfield_name = flexfield_name;
2179 println('deleted fnd_compiled_descriptive_flexs');
2180
2181
2182 println('deleted flexfield:' || flexfield_name);
2183 END;
2184
2185
2186
2187
2188 PROCEDURE enable_context(appl_short_name IN VARCHAR2,
2189 flexfield_name IN VARCHAR2,
2190 context IN VARCHAR2,
2191 enable IN BOOLEAN DEFAULT TRUE)
2192 IS
2193 application_id_i fnd_application.application_id%TYPE;
2194 enabled_flag_i VARCHAR2(1);
2195 cnt NUMBER;
2196 BEGIN
2197 message_init;
2198 application_id_i := application_id_f(appl_short_name);
2199 IF(enable) THEN
2200 enabled_flag_i := 'Y';
2201 ELSE
2202 enabled_flag_i := 'N';
2203 END IF;
2204 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2205 fnd_descr_flex_contexts
2206 SET enabled_flag = enabled_flag_i
2207 WHERE descriptive_flex_context_code = context
2208 AND descriptive_flexfield_name = flexfield_name
2209 AND application_id = application_id_i;
2210 IF(SQL%rowcount = 0) THEN
2211 message('context ' || context ||
2212 ' in flexfield ' || appl_short_name ||
2213 '/' || flexfield_name || ' not found');
2214 raise_application_error(-20000, message);
2215 END IF;
2216 END;
2217
2218 /* New API added for Bug 4390452 */
2219 PROCEDURE update_context(
2220 p_appl_short_name IN VARCHAR2,
2221 p_flexfield_name IN VARCHAR2,
2222 p_desc_flex_context_code IN VARCHAR2,
2223 p_desc_flex_context_name IN VARCHAR2 DEFAULT NULL,
2224 p_description IN VARCHAR2 DEFAULT NULL,
2225 p_enabled_flag IN VARCHAR2 DEFAULT NULL,
2226 p_language IN VARCHAR2)
2227 IS
2228 l_application_id fnd_application.application_id%TYPE;
2229 l_boolean_flag1 BOOLEAN;
2230 l_lang_exists NUMBER;
2231 l_dflex_contexts_tl fnd_descr_flex_contexts_tl%ROWTYPE;
2232 l_enabled_flag fnd_descr_flex_contexts.enabled_flag%TYPE;
2233 BEGIN
2234 begin
2235 select 1 into l_lang_exists from fnd_languages where
2236 language_code = p_language and installed_flag in ('B','I') and rownum < 2;
2237 exception
2238 when no_data_found then
2239 raise_application_error(-20204, 'FND_FLEX_DSC_API.UPDATE_CONTEXT raised exception: No language exist with language_code ='|| p_language, TRUE);
2240 end;
2241 /* Check if context exists */
2242 l_boolean_flag1 := context_exists(p_appl_short_name,
2243 p_flexfield_name,
2244 p_desc_flex_context_code);
2245 IF (l_boolean_flag1=TRUE) THEN
2246 IF (not(p_desc_flex_context_name is NULL AND p_description is NULL and p_enabled_flag is NULL)) THEN
2247 l_application_id := application_id_f(p_appl_short_name);
2248 IF (not(p_desc_flex_context_name is NULL AND p_description is NULL)) THEN
2249 SELECT * into l_dflex_contexts_tl
2250 from fnd_descr_flex_contexts_tl
2251 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2252 AND descriptive_flexfield_name = p_flexfield_name
2253 AND application_id = l_application_id
2254 AND language = p_language;
2255
2256 UPDATE fnd_descr_flex_contexts_tl
2257 SET descriptive_flex_context_name = nvl(p_desc_flex_context_name, l_dflex_contexts_tl.descriptive_flex_context_name),
2258 description = nvl(p_description, l_dflex_contexts_tl.description)
2259 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2260 AND descriptive_flexfield_name = p_flexfield_name
2261 AND application_id = l_application_id
2262 AND language = p_language;
2263 END IF;
2264
2265 IF (p_enabled_flag is not NULL) THEN
2266 IF (p_enabled_flag in ('Y','N')) THEN
2267 SELECT enabled_flag into l_enabled_flag from fnd_descr_flex_contexts
2268 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2269 AND descriptive_flexfield_name = p_flexfield_name
2270 AND application_id = l_application_id;
2271
2272 UPDATE fnd_descr_flex_contexts
2273 SET enabled_flag = nvl(p_enabled_flag, l_enabled_flag)
2274 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2275 AND descriptive_flexfield_name = p_flexfield_name
2276 AND application_id = l_application_id;
2277 ELSE
2278 raise_application_error(-20205, 'Enabled flag is not set properly. It has to be either Y/N');
2279 END IF;
2280 END IF;
2281 END IF;
2282 ELSE
2283 message('Context ' || p_desc_flex_context_code ||
2284 ' in flexfield ' || p_appl_short_name ||
2285 '/' || p_flexfield_name || ' not found');
2286 raise_application_error(-20000, message);
2287 END IF;
2288 END;
2289
2290
2291
2292
2293 PROCEDURE delete_context(appl_short_name IN VARCHAR2,
2294 flexfield_name IN VARCHAR2,
2295 context IN VARCHAR2)
2296 IS
2297 application_id_i fnd_application.application_id%TYPE;
2298 rec_count NUMBER;
2299 BEGIN
2300 message_init;
2301 application_id_i := application_id_f(appl_short_name);
2302
2303 -- add this later
2304 -- when we have a delete reference field also
2305 IF(FALSE AND do_validation) THEN
2306 -- A row cannot be deleted if the context field name
2307 -- is used as the default context field name for the
2308 -- descriptive flexfield.
2309 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2310 count('x')
2311 INTO rec_count
2312 FROM fnd_descriptive_flexs
2313 WHERE application_id = application_id_i
2314 AND descriptive_flexfield_name = flexfield_name
2315 AND default_context_field_name = context;
2316
2317 IF (rec_count > 0) THEN
2318 message('this context field is a reference field');
2319 RAISE bad_parameter;
2320 END IF;
2321 END IF;
2322
2323 BEGIN
2324 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2325 FROM fnd_descr_flex_col_usage_tl
2326 WHERE descriptive_flex_context_code = context
2327 AND descriptive_flexfield_name = delete_context.flexfield_name
2328 AND application_id = application_id_i;
2329 println('deleted from fnd_descr_col_usage_tl');
2330 EXCEPTION
2331 WHEN no_data_found THEN
2332 NULL;
2333 END;
2334
2335 BEGIN
2336 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2337 FROM fnd_descr_flex_column_usages
2338 WHERE descriptive_flex_context_code = context
2339 AND descriptive_flexfield_name = delete_context.flexfield_name
2340 AND application_id = application_id_i;
2341 println('deleted from fnd_descr_column_usages');
2342 EXCEPTION
2343 WHEN no_data_found THEN
2344 NULL;
2345 END;
2346
2347 BEGIN
2348 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2349 FROM fnd_descr_flex_contexts_tl
2350 WHERE descriptive_flex_context_code = context
2351 AND descriptive_flexfield_name = delete_context.flexfield_name
2352 AND application_id = application_id_i;
2353 println('deleted from fnd_descr_flex_contexts_tl');
2354 EXCEPTION
2355 WHEN no_data_found THEN
2356 NULL;
2357 END;
2358
2359 BEGIN
2360 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2361 FROM fnd_descr_flex_contexts
2362 WHERE descriptive_flex_context_code = context
2363 AND descriptive_flexfield_name = delete_context.flexfield_name
2364 AND application_id = application_id_i;
2365 println('deleted from fnd_descr_flex_contexts');
2366 EXCEPTION
2367 WHEN no_data_found THEN
2368 NULL;
2369 END;
2370
2371 println('deleted flexfield context:' ||
2372 flexfield_name || '.' || context);
2373 END;
2374
2375
2376
2377 PROCEDURE delete_segment(appl_short_name IN VARCHAR2,
2378 flexfield_name IN VARCHAR2,
2379 context IN VARCHAR2,
2380 segment IN VARCHAR2)
2381 IS
2382 application_id_i fnd_application.application_id%TYPE;
2383 BEGIN
2384 message_init;
2385 application_id_i := application_id_f(appl_short_name);
2386
2387 BEGIN
2388 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2389 FROM fnd_descr_flex_col_usage_tl t
2390 WHERE
2391 EXISTS (SELECT *
2392 FROM fnd_descr_flex_column_usages cu
2393 WHERE cu.end_user_column_name = segment
2394 AND cu.descriptive_flex_context_code = context
2395 AND cu.descriptive_flexfield_name = flexfield_name
2396 AND cu.application_id = application_id_i
2397 /* and join cond: */
2398 AND cu.application_column_name = t.application_column_name
2399 AND cu.descriptive_flex_context_code =
2400 t.descriptive_flex_context_code
2401 AND cu.descriptive_flexfield_name = t.descriptive_flexfield_name
2402 AND cu.application_id = t.application_id);
2403 println('deleted from fnd_descr_col_usage_tl');
2404 EXCEPTION
2405 WHEN no_data_found THEN
2406 NULL;
2407 END;
2408
2409 BEGIN
2410 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2411 FROM fnd_descr_flex_column_usages
2412 WHERE end_user_column_name = segment
2413 AND descriptive_flex_context_code = context
2414 AND descriptive_flexfield_name = flexfield_name
2415 AND application_id = application_id_i;
2416 println('deleted from fnd_descr_column_usages');
2417 EXCEPTION
2418 WHEN no_data_found THEN
2419 NULL;
2420 END;
2421
2422 println('deleted flexfield segment:' ||
2423 flexfield_name || '.' || context || '.' || segment);
2424 END;
2425
2426
2427 FUNCTION flexfield_exists(appl_short_name IN VARCHAR2,
2428 flexfield_name IN VARCHAR2) RETURN BOOLEAN
2429 IS
2430 application_id_i fnd_application.application_id%TYPE;
2431 cnt NUMBER;
2432 BEGIN
2433 message_init;
2434 application_id_i := application_id_f(appl_short_name);
2435 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2436 COUNT(*)
2437 INTO cnt
2438 FROM fnd_descriptive_flexs
2439 WHERE application_id = application_id_i
2440 AND descriptive_flexfield_name = flexfield_name;
2441 IF(cnt > 0) THEN
2442 RETURN TRUE;
2443 ELSE
2444 RETURN FALSE;
2445 END IF;
2446 EXCEPTION
2447 WHEN OTHERS THEN
2448 message('exception in flexfield_exists:' || Sqlerrm);
2449 RAISE bad_parameter;
2450 END;
2451
2452 FUNCTION context_exists(p_appl_short_name IN VARCHAR2,
2453 p_flexfield_name IN VARCHAR2,
2454 p_context_code IN VARCHAR2) RETURN BOOLEAN
2455 IS
2456 l_application_id fnd_application.application_id%TYPE;
2457 BEGIN
2458 message_init;
2459 l_application_id := application_id_f(p_appl_short_name);
2460 BEGIN
2461 check_existance(l_application_id, p_flexfield_name);
2462 check_existance(l_application_id, p_flexfield_name, p_context_code);
2463 EXCEPTION
2464 WHEN OTHERS THEN
2465 RETURN(FALSE);
2466 END;
2467 RETURN(TRUE);
2468 EXCEPTION
2469 WHEN OTHERS THEN
2470 NULL;
2471 RAISE;
2472 END context_exists;
2473
2474 FUNCTION segment_exists(p_appl_short_name IN VARCHAR2,
2475 p_flexfield_name IN VARCHAR2,
2476 p_context_code IN VARCHAR2,
2477 p_segment_name IN VARCHAR2 DEFAULT NULL,
2478 p_column_name IN VARCHAR2 DEFAULT NULL)
2479 RETURN BOOLEAN
2480 IS
2481 l_application_id fnd_application.application_id%TYPE;
2482 cnt NUMBER;
2483 BEGIN
2484 message_init;
2485 l_application_id := application_id_f(p_appl_short_name);
2486 BEGIN
2487 check_existance(l_application_id, p_flexfield_name);
2488 check_existance(l_application_id, p_flexfield_name, p_context_code);
2489 EXCEPTION
2490 WHEN OTHERS THEN
2491 RETURN(FALSE);
2492 END;
2493 IF (((p_column_name IS NULL) AND (p_segment_name IS NULL)) OR
2494 ((p_column_name IS NOT NULL) AND (p_segment_name IS NOT NULL))) THEN
2495 message('Please pass either p_column_name or p_segment_name but not both.');
2496 RAISE bad_parameter;
2497 END IF;
2498 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2499 COUNT(*) INTO cnt
2500 FROM fnd_descr_flex_column_usages
2501 WHERE application_id = l_application_id
2502 AND descriptive_flexfield_name = p_flexfield_name
2503 AND descriptive_flex_context_code = p_context_code
2504 AND (((p_column_name IS NOT NULL) AND
2505 (application_column_name = p_column_name)) OR
2506 ((p_segment_name IS NOT NULL) AND
2507 (end_user_column_name = p_segment_name)));
2508 IF (cnt > 0) THEN
2509 RETURN(TRUE);
2510 ELSE
2511 RETURN(FALSE);
2512 END IF;
2513 EXCEPTION
2514 WHEN OTHERS THEN
2515 NULL;
2516 RAISE;
2517 END segment_exists;
2518
2519 FUNCTION is_table_used(p_application_id IN fnd_tables.application_id%TYPE,
2520 p_table_name IN fnd_tables.table_name%TYPE,
2521 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN
2522 IS
2523 up_table_name fnd_tables.table_name%TYPE := Upper(p_table_name);
2524 l_a_id fnd_descriptive_flexs.application_id%TYPE;
2525 l_dff_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2526 BEGIN
2527 x_message := 'This table is not used by Descriptive Flexfields.';
2528 BEGIN
2529 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2530 application_id, descriptive_flexfield_name
2531 INTO l_a_id, l_dff_name
2532 FROM fnd_descriptive_flexs
2533 WHERE table_application_id = p_application_id
2534 AND Upper(application_table_name) = up_table_name
2535 AND ROWNUM = 1;
2536 x_message :=
2537 'This table is used by ' || chr_newline ||
2538 'APPLICATION_ID : ' || l_a_id || chr_newline ||
2539 'DESCRIPTIVE_FLEXFIELD_NAME : ' || l_dff_name;
2540 RETURN(TRUE);
2541 EXCEPTION
2542 WHEN no_data_found THEN
2543 NULL;
2544 WHEN OTHERS THEN
2545 x_message :=
2546 'SELECT FROM FND_DESCRIPTIVE_FLEXS is failed. ' || chr_newline ||
2547 'SQLERRM : ' || Sqlerrm;
2548 RETURN(TRUE);
2549 END;
2550 RETURN(FALSE);
2551 EXCEPTION
2552 WHEN OTHERS THEN
2553 x_message :=
2554 'FND_FLEX_DSC_API.IS_TABLE_USED is failed. ' || chr_newline ||
2555 'SQLERRM : ' || Sqlerrm;
2556 RETURN(TRUE);
2557 END is_table_used;
2558
2559
2560 FUNCTION is_column_used(p_application_id IN fnd_tables.application_id%TYPE,
2561 p_table_name IN fnd_tables.table_name%TYPE,
2562 p_column_name IN fnd_columns.column_name%TYPE,
2563 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN
2564 IS
2565 up_table_name fnd_tables.table_name%TYPE := Upper(p_table_name);
2566 up_column_name fnd_columns.column_name%TYPE := Upper(p_column_name);
2567 l_a_id fnd_descriptive_flexs.application_id%TYPE;
2568 l_dff_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2569 l_context_col fnd_descriptive_flexs.context_column_name%TYPE;
2570 l_context fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE;
2571 l_segment fnd_descr_flex_column_usages.end_user_column_name%TYPE;
2572 BEGIN
2573 x_message := 'This column is not used by Descriptive Flexfields.';
2574 BEGIN
2575 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2576 application_id, descriptive_flexfield_name,
2577 context_column_name
2578 INTO l_a_id, l_dff_name, l_context_col
2579 FROM fnd_descriptive_flexs
2580 WHERE table_application_id = p_application_id
2581 AND Upper(application_table_name) = up_table_name
2582 AND Upper(context_column_name) = up_column_name
2583 AND ROWNUM = 1;
2584 x_message :=
2585 'This column is used by ' || chr_newline ||
2586 'APPLICATION_ID : ' || l_a_id || chr_newline ||
2587 'DESCRIPTIVE_FLEXFIELD_NAME : ' || l_dff_name || chr_newline ||
2588 'CONTEXT_COLUMN_NAME : ' || l_context_col;
2589 RETURN(TRUE);
2590 EXCEPTION
2591 WHEN no_data_found THEN
2592 NULL;
2593 WHEN OTHERS THEN
2594 x_message :=
2595 'SELECT FROM FND_DESCRIPTIVE_FLEXS is failed.'||chr_newline ||
2596 'SQLERRM : ' || Sqlerrm;
2597 RETURN(TRUE);
2598 END;
2599
2600 BEGIN
2601 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2602 dfcu.application_id, dfcu.descriptive_flexfield_name,
2603 dfcu.descriptive_flex_context_code, dfcu.end_user_column_name
2604 INTO l_a_id, l_dff_name, l_context, l_segment
2605 FROM fnd_descr_flex_column_usages dfcu, fnd_descriptive_flexs df
2606 WHERE df.application_id = dfcu.application_id
2607 AND df.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
2608 AND df.table_application_id = p_application_id
2609 AND Upper(df.application_table_name) = up_table_name
2610 AND Upper(dfcu.application_column_name) = up_column_name
2611 AND ROWNUM = 1;
2612 x_message :=
2613 'This column is used by ' || chr_newline ||
2614 'APPLICATION_ID : ' || l_a_id || chr_newline ||
2615 'DESCRIPTIVE_FLEXFIELD_NAME : ' || l_dff_name || chr_newline ||
2616 'CONTEXT_CODE : ' || l_context || chr_newline ||
2617 'SEGMENT_NAME : ' || l_segment;
2618 RETURN(TRUE);
2619 EXCEPTION
2620 WHEN no_data_found THEN
2621 NULL;
2622 WHEN OTHERS THEN
2623 x_message :=
2624 'SELECT FROM FND_DESCR_FLEX_COLUMN_USAGES is failed.'||chr_newline ||
2625 'SQLERRM : ' || Sqlerrm;
2626 RETURN(TRUE);
2627 END;
2628 RETURN(FALSE);
2629 EXCEPTION
2630 WHEN OTHERS THEN
2631 x_message :=
2632 'FND_FLEX_DSC_API.IS_COLUMN_USED is failed. ' || chr_newline ||
2633 'SQLERRM : ' || Sqlerrm;
2634 RETURN(TRUE);
2635 END is_column_used;
2636
2637 -- This API gets the application details
2638 PROCEDURE get_application
2639 (
2640 p_application_short_name IN fnd_application.application_short_name%TYPE,
2641 x_application OUT NOCOPY fnd_app_type
2642 ) IS
2643 BEGIN
2644 SELECT *
2645 INTO x_application
2646 FROM fnd_application fa
2647 WHERE fa.application_short_name = p_application_short_name;
2648 EXCEPTION
2649 WHEN NO_DATA_FOUND THEN
2650 RAISE_APPLICATION_ERROR(-20200,'No Application exists with application_short_name = '
2651 || p_application_short_name,TRUE);
2652 END get_application;
2653
2654 -- This API gets the table details
2655 PROCEDURE get_table
2656 (
2657 p_application_id IN fnd_application.application_id%TYPE,
2658 p_table_name IN fnd_tables.table_name%TYPE,
2659 x_table OUT NOCOPY fnd_tbl_type
2660 ) IS
2661 BEGIN
2662 SELECT *
2663 INTO x_table
2664 FROM fnd_tables ft
2665 WHERE ft.application_id = p_application_id
2666 AND ft.table_name = p_table_name;
2667 EXCEPTION
2668 WHEN NO_DATA_FOUND THEN
2669 RAISE_APPLICATION_ERROR(-20201,'No table exists with application_id = ' || p_application_id
2670 || ' and table_name = ' || p_table_name,TRUE);
2671 END get_table;
2672
2673 -- This API gets the Descriptive Flexfield details
2674 PROCEDURE get_descriptive_flexfield
2675 (
2676 p_application_id IN fnd_application.application_id%TYPE,
2677 p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE,
2678 x_descriptive_flexfield OUT NOCOPY fnd_dff_type
2679 ) IS
2680 BEGIN
2681 SELECT *
2682 INTO x_descriptive_flexfield
2683 FROM fnd_descriptive_flexs fdff
2684 WHERE fdff.application_id = p_application_id
2685 AND fdff.descriptive_flexfield_name = p_descriptive_flexfield_name;
2686 EXCEPTION
2687 WHEN NO_DATA_FOUND THEN
2688 RAISE_APPLICATION_ERROR(-20203,'No descriptive flexfield exists with application_id = ' ||
2689 p_application_id || ' and descriptive_flexfield_name = ' || p_descriptive_flexfield_name,TRUE);
2690 END get_descriptive_flexfield;
2691
2692 -- This API renames an exisisting descriptive flexfield
2693 PROCEDURE rename_dff
2694 (
2695 p_old_application_short_name IN fnd_application.application_short_name%TYPE,
2696 p_old_dff_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE,
2697 p_new_application_short_name IN fnd_application.application_short_name%TYPE,
2698 p_new_dff_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
2699 ) IS
2700
2701 l_old_application fnd_app_type;
2702 l_new_application fnd_app_type;
2703 l_new_dff fnd_dff_type;
2704 l_old_dff fnd_dff_type;
2705 l_table fnd_tbl_type;
2706 l_dff_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2707 l_error_message VARCHAR2(4000);
2708 l_illegal_character VARCHAR2(1000);
2709 l_last_update_date fnd_descriptive_flexs.last_update_date%TYPE;
2710 l_last_updated_by fnd_descriptive_flexs.last_updated_by%TYPE;
2711 l_last_update_login fnd_descriptive_flexs.last_update_login%TYPE;
2712 BEGIN
2713 l_last_update_date := SYSDATE;
2714 l_last_updated_by := fnd_global.user_id();
2715 l_last_update_login := fnd_global.login_id();
2716
2717 -- If l_last_updated_by IS NULL or equals to -1 or l_last_update_login IS NULL then ask user to set context
2718 IF ((l_last_updated_by IS NULL) OR (l_last_updated_by = -1)) OR (l_last_update_login IS NULL) THEN
2719 l_error_message := 'Application Security Context is not set.Please set the context using fnd_global.apps_initialize() and try again.';
2720 RAISE_APPLICATION_ERROR(error_context_not_set, l_error_message, TRUE);
2721 END IF;
2722
2723 -- make sure the old and new dff names are different
2724 IF p_old_application_short_name = p_new_application_short_name AND
2725 p_old_dff_name = p_new_dff_name THEN
2726 l_error_message := 'The old and new DFF names are same.';
2727 RAISE_APPLICATION_ERROR(error_same_dff_name, l_error_message, TRUE);
2728 END IF;
2729
2730 l_illegal_character := REPLACE(TRANSLATE(UPPER(p_new_dff_name), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_$. ',
2731 ' '), ' ', '');
2732 IF p_new_dff_name IS NULL THEN
2733 l_error_message := 'The new DFF name is NULL';
2734 RAISE_APPLICATION_ERROR(error_invalid_dff_name, l_error_message, TRUE);
2735 ELSIF LENGTH(p_new_dff_name) > 40 THEN
2736 l_error_message := 'The name of the new DFF is more than 40 characters';
2737 RAISE_APPLICATION_ERROR(error_invalid_dff_name, l_error_message, TRUE);
2738 ELSIF l_illegal_character IS NOT NULL THEN
2739 l_error_message := 'The new DFF name contains illegal characters (' || l_illegal_character || ')';
2740 RAISE_APPLICATION_ERROR(error_invalid_dff_name, l_error_message, TRUE);
2741 END IF;
2742
2743 -- Get the old application Id
2744 get_application(p_application_short_name => p_old_application_short_name,
2745 x_application => l_old_application);
2746
2747 -- Get the new application Id
2748 get_application(p_application_short_name => p_new_application_short_name,
2749 x_application => l_new_application);
2750
2751 --Get the old DFF and make sure the old DFF exists.
2752 get_descriptive_flexfield(p_application_id => l_old_application.application_id,
2753 p_descriptive_flexfield_name => p_old_dff_name,
2754 x_descriptive_flexfield =>l_old_dff);
2755
2756 -- Get the table_id
2757 get_table(p_application_id => l_old_dff.table_application_id,
2758 p_table_name => l_old_dff.application_table_name,
2759 x_table => l_table);
2760
2761 --Make sure the new DFF doesn't exist.
2762 BEGIN
2763 SELECT /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2764 descriptive_flexfield_name
2765 INTO l_dff_name
2766 FROM fnd_descriptive_flexs
2767 WHERE application_id = l_new_application.application_id
2768 AND descriptive_flexfield_name = p_new_dff_name;
2769 l_error_message := 'A DFF with name ' || p_new_dff_name || ' already exists for the application table ' || l_old_dff.application_table_name;
2770 RAISE_APPLICATION_ERROR(error_dff_already_exists, l_error_message, TRUE);
2771 EXCEPTION
2772 WHEN NO_DATA_FOUND THEN
2773 NULL;
2774 END;
2775
2776
2777 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2778 fnd_descriptive_flexs
2779 SET application_id = l_new_application.application_id,
2780 descriptive_flexfield_name = p_new_dff_name,
2781 last_update_date = l_last_update_date,
2782 last_updated_by = l_last_updated_by,
2783 last_update_login = l_last_update_login
2784 WHERE application_id = l_old_application.application_id
2785 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2786
2787 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2788 fnd_descriptive_flexs_tl
2789 SET application_id = l_new_application.application_id,
2790 descriptive_flexfield_name = p_new_dff_name,
2791 last_update_date = l_last_update_date,
2792 last_updated_by = l_last_updated_by,
2793 last_update_login = l_last_update_login
2794 WHERE application_id = l_old_application.application_id
2795 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2796
2797 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2798 fnd_default_context_fields
2799 SET application_id = l_new_application.application_id,
2800 descriptive_flexfield_name = p_new_dff_name,
2801 last_update_date = l_last_update_date,
2802 last_updated_by = l_last_updated_by,
2803 last_update_login = l_last_update_login
2804 WHERE application_id = l_old_application.application_id
2805 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2806
2807 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2808 fnd_descr_flex_contexts
2809 SET application_id = l_new_application.application_id,
2810 descriptive_flexfield_name = p_new_dff_name,
2811 last_update_date = l_last_update_date,
2812 last_updated_by = l_last_updated_by,
2813 last_update_login = l_last_update_login
2814 WHERE application_id = l_old_application.application_id
2815 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2816
2817 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2818 fnd_descr_flex_contexts_tl
2819 SET application_id = l_new_application.application_id,
2820 descriptive_flexfield_name = p_new_dff_name,
2821 last_update_date = l_last_update_date,
2822 last_updated_by = l_last_updated_by,
2823 last_update_login = l_last_update_login
2824 WHERE application_id = l_old_application.application_id
2825 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2826
2827 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2828 fnd_descr_flex_column_usages
2829 SET application_id = l_new_application.application_id,
2830 descriptive_flexfield_name = p_new_dff_name,
2831 last_update_date = l_last_update_date,
2832 last_updated_by = l_last_updated_by,
2833 last_update_login = l_last_update_login
2834 WHERE application_id = l_old_application.application_id
2835 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2836
2837 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2838 fnd_descr_flex_col_usage_tl
2839 SET application_id = l_new_application.application_id,
2840 descriptive_flexfield_name = p_new_dff_name,
2841 last_update_date = l_last_update_date,
2842 last_updated_by = l_last_updated_by,
2843 last_update_login = l_last_update_login
2844 WHERE application_id = l_old_application.application_id
2845 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2846
2847 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2848 fnd_columns
2849 SET flexfield_application_id = l_new_application.application_id,
2850 flexfield_name = p_new_dff_name,
2851 last_update_date = l_last_update_date,
2852 last_updated_by = l_last_updated_by,
2853 last_update_login = l_last_update_login
2854 WHERE application_id = l_table.application_id
2855 AND table_id = l_table.table_id
2856 AND flexfield_application_id = l_old_dff.application_id
2857 AND flexfield_name = l_old_dff.descriptive_flexfield_name
2858 AND flexfield_usage_code IN ('C','D');
2859
2860 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2861 FROM fnd_compiled_descriptive_flexs
2862 WHERE application_id = l_old_application.application_id
2863 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2864
2865 DELETE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
2866 FROM fnd_compiled_descriptive_flexs
2867 WHERE application_id = l_new_application.application_id
2868 AND descriptive_flexfield_name = l_new_dff.descriptive_flexfield_name;
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 RAISE_APPLICATION_ERROR(error_others, 'FND_FLEX_DSC_API.RENAME_DFF raised exception: ' || sqlerrm || ' errcode : ' || sqlcode, TRUE);
2872 END rename_dff;
2873
2874 -- This API makes the necessary updates to change the base table of a given DFF.
2875 -- The only requirement is that the new table must have the same column names,
2876 -- same column types, and same size or bigger size columns. (For ATTRIBUTE columns).
2877
2878 PROCEDURE migrate_dff
2879 (
2880 p_application_short_name IN fnd_application.application_short_name%TYPE,
2881 p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE,
2882 p_new_table_appl_short_name IN fnd_application.application_short_name%TYPE,
2883 p_new_table_name IN fnd_tables.table_name%TYPE
2884 ) IS
2885
2886 l_application fnd_app_type;
2887 l_dff fnd_dff_type;
2888
2889 l_old_table fnd_tbl_type;
2890 l_new_table_app fnd_app_type;
2891 l_new_table fnd_tbl_type;
2892
2893 l_column_name fnd_columns.column_name%TYPE;
2894 l_error_message VARCHAR2(4000);
2895
2896 l_error_msg VARCHAR2(4000) := '';
2897 l_last_update_date fnd_descriptive_flexs.last_update_date%TYPE;
2898 l_last_updated_by fnd_descriptive_flexs.last_updated_by%TYPE;
2899 l_last_update_login fnd_descriptive_flexs.last_update_login%TYPE;
2900
2901 CURSOR cur_columns_reg(p_dff fnd_dff_type,
2902 p_old_table fnd_tbl_type,
2903 p_new_table fnd_tbl_type) IS
2904 SELECT old.column_name old_column_name,
2905 old.column_type old_column_type,
2906 old.width old_width,
2907 old.flexfield_usage_code old_flexfield_usage_code,
2908 old.flexfield_application_id old_flexfield_application_id,
2909 old.flexfield_name old_flexfield_name,
2910 new.column_name new_column_name,
2911 new.column_type new_column_type,
2912 new.width new_width,
2913 new.flexfield_usage_code new_flexfield_usage_code,
2914 new.flexfield_application_id new_flexfield_application_id,
2915 new.flexfield_name new_flexfield_name
2916 FROM fnd_columns old, fnd_columns new
2917 WHERE old.application_id = p_old_table.application_id
2918 AND old.table_id = p_old_table.table_id
2919
2920 AND old.flexfield_usage_code in ('C', 'D')
2921 AND old.flexfield_application_id = p_dff.application_id
2922 AND old.flexfield_name = p_dff.descriptive_flexfield_name
2923
2924 AND new.application_id(+) = p_new_table.application_id
2925 AND new.table_id(+) = p_new_table.table_id
2926
2927 AND new.column_name(+) = old.column_name
2928 ORDER BY old.column_sequence;
2929
2930 BEGIN
2931 l_last_update_date := SYSDATE;
2932 l_last_updated_by := fnd_global.user_id();
2933 l_last_update_login := fnd_global.login_id();
2934
2935 -- If l_last_updated_by IS NULL or equals to -1 or l_last_update_login IS NULL then ask user to set context
2936 IF ((l_last_updated_by IS NULL) OR (l_last_updated_by = -1)) OR (l_last_update_login IS NULL) THEN
2937 l_error_message := 'Application Security Context not set.Please set the context using fnd_global.apps_initialize() and try again.';
2938 RAISE_APPLICATION_ERROR(error_context_not_set, l_error_message, TRUE);
2939 END IF;
2940
2941 IF p_descriptive_flexfield_name LIKE '$SRS$.%' THEN
2942 l_error_message := 'This Descriptive Flexfield is meant for SRS parameters and hence this cannot be migrated to some other table';
2943 RAISE_APPLICATION_ERROR(error_srs_dff, l_error_message, TRUE);
2944 END IF;
2945 -- Get the old application_id
2946 get_application(p_application_short_name => p_application_short_name,
2947 x_application => l_application);
2948
2949 --Get the DFF and make sure that it exists.
2950 get_descriptive_flexfield(
2951 p_application_id => l_application.application_id,
2952 p_descriptive_flexfield_name => p_descriptive_flexfield_name,
2953 x_descriptive_flexfield => l_dff);
2954
2955 -- Get the old table_id
2956 get_table(p_application_id => l_dff.table_application_id,
2957 p_table_name => l_dff.application_table_name,
2958 x_table => l_old_table);
2959
2960
2961 -- Get the new table application
2962 get_application(p_application_short_name => p_new_table_appl_short_name,
2963 x_application => l_new_table_app);
2964
2965 -- Get the new table
2966 get_table(p_application_id => l_new_table_app.application_id,
2967 p_table_name => p_new_table_name,
2968 x_table => l_new_table);
2969
2970 -- Make sure the old and new table info are different
2971 IF l_new_table.application_id = l_old_table.application_id AND
2972 l_new_table.table_id = l_old_table.table_id THEN
2973 l_error_message := 'The old and new tables are same !!!';
2974 RAISE_APPLICATION_ERROR(error_same_table_name, l_error_message, TRUE);
2975 END IF;
2976
2977 FOR rec_columns_reg IN cur_columns_reg(l_dff,
2978 l_old_table,
2979 l_new_table)
2980 LOOP
2981 -- Make sure the column is not registered under any other FF
2982 IF rec_columns_reg.new_column_name IS NULL THEN
2983 l_error_message := 'The new table does not have the column ' || rec_columns_reg.old_column_name || ' as in the old one';
2984 RAISE_APPLICATION_ERROR(error_col_not_registered,l_error_message,TRUE);
2985 ELSIF rec_columns_reg.new_column_type <> rec_columns_reg.old_column_type THEN
2986 l_error_message := 'The column ' || rec_columns_reg.new_column_name || ' is of different type than in the old table';
2987 RAISE_APPLICATION_ERROR(error_col_wrong_type,l_error_message,TRUE);
2988 ELSIF rec_columns_reg.new_width < rec_columns_reg.old_width THEN
2989 l_error_message := 'The width of the column ' || rec_columns_reg.new_column_name || '(' || rec_columns_reg.new_width || ') is less than the width of the column in the old table (' || rec_columns_reg.old_width || ')';
2990 RAISE_APPLICATION_ERROR(error_col_wrong_size,l_error_message,TRUE);
2991 ELSIF rec_columns_reg.new_flexfield_usage_code <> 'N' OR
2992 rec_columns_reg.new_flexfield_application_id IS NOT NULL OR
2993 rec_columns_reg.new_flexfield_name IS NOT NULL THEN
2994 l_error_message := 'The column ' || rec_columns_reg.new_column_name ||
2995 ' is already registered with the flexfield with name = ' || rec_columns_reg.old_flexfield_name ||
2996 ' and application_id = ' || rec_columns_reg.old_flexfield_application_id ||
2997 '.The flexfield_usage_code for this column is : ' || rec_columns_reg.new_flexfield_usage_code;
2998 RAISE_APPLICATION_ERROR(error_col_already_regis, l_error_message, TRUE);
2999 END IF;
3000 END LOOP;
3001
3002 UPDATE /* $Header: AFFFDAIB.pls 120.10 2011/12/12 15:31:39 hgeorgi ship $ */
3003 fnd_descriptive_flexs
3004 SET application_table_name = p_new_table_name,
3005 table_application_id = l_new_table.application_id,
3006 last_update_date = l_last_update_date,
3007 last_updated_by = l_last_updated_by,
3008 last_update_login = l_last_update_login
3009 WHERE application_id = l_dff.application_id
3010 AND descriptive_flexfield_name = l_dff.descriptive_flexfield_name;
3011
3012 FOR rec_columns_reg IN cur_columns_reg(l_dff,
3013 l_old_table,
3014 l_new_table)
3015 LOOP
3016 -- Update for the column definition for the new table
3017 UPDATE fnd_columns
3018 SET flexfield_usage_code = rec_columns_reg.old_flexfield_usage_code,
3019 flexfield_application_id = l_dff.application_id,
3020 flexfield_name = l_dff.descriptive_flexfield_name,
3021 last_update_date = l_last_update_date,
3022 last_updated_by = l_last_updated_by,
3023 last_update_login = l_last_update_login
3024 WHERE application_id = l_new_table.application_id
3025 AND table_id = l_new_table.table_id
3026 AND column_name = rec_columns_reg.new_column_name;
3027
3028 -- Update for the column definition for old table
3029 UPDATE fnd_columns
3030 SET flexfield_usage_code = 'N',
3031 flexfield_application_id = NULL,
3032 flexfield_name = NULL,
3033 last_update_date = l_last_update_date,
3034 last_updated_by = l_last_updated_by,
3035 last_update_login = l_last_update_login
3036 WHERE application_id = l_old_table.application_id
3037 AND table_id = l_old_table.table_id
3038 AND column_name = rec_columns_reg.old_column_name;
3039 END LOOP;
3040
3041 EXCEPTION
3042 WHEN OTHERS THEN
3043 RAISE_APPLICATION_ERROR(error_others, 'FND_FLEX_DSC_API.MIGRATE_DFF raised exception: ' || sqlerrm || ' errcode : ' || sqlcode, TRUE);
3044 END migrate_dff;
3045
3046 --
3047 -- Modify_segment_null_default only sets the default_type and default_value
3048 -- to NULL
3049
3050 PROCEDURE modify_segment_null_default
3051 (-- PK for segment
3052 p_appl_short_name IN VARCHAR2,
3053 p_flexfield_name IN VARCHAR2,
3054 p_context_code IN VARCHAR2,
3055 p_segment_name IN VARCHAR2 DEFAULT NULL,
3056 p_column_name IN VARCHAR2 DEFAULT NULL)
3057
3058 IS
3059 l_application_id fnd_descr_flex_col_usage_vl.application_id%TYPE;
3060 l_description fnd_descr_flex_col_usage_vl.description%TYPE;
3061 l_sequence_number fnd_descr_flex_col_usage_vl.column_seq_num%TYPE;
3062 l_enabled fnd_descr_flex_col_usage_vl.enabled_flag%TYPE;
3063 l_displayed fnd_descr_flex_col_usage_vl.display_flag%TYPE;
3064 l_default_type fnd_descr_flex_col_usage_vl.default_type%TYPE;
3065 l_default_value fnd_descr_flex_col_usage_vl.default_value%TYPE;
3066 l_required fnd_descr_flex_col_usage_vl.required_flag%TYPE;
3067 l_security_enabled fnd_descr_flex_col_usage_vl.security_enabled_flag%TYPE;
3068 l_display_size fnd_descr_flex_col_usage_vl.display_size%TYPE;
3069 l_description_size
3070 fnd_descr_flex_col_usage_vl.maximum_description_len%TYPE;
3071 l_concat_desc_size
3072 fnd_descr_flex_col_usage_vl.concatenation_description_len%TYPE;
3073 l_lov_prompt fnd_descr_flex_col_usage_vl.form_above_prompt%TYPE;
3074 l_window_prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
3075 l_range fnd_descr_flex_col_usage_vl.range_code%TYPE;
3076 l_srw_parameter fnd_descr_flex_col_usage_vl.srw_param%TYPE;
3077 l_runtime_property_function
3078 fnd_descr_flex_col_usage_vl.runtime_property_function%TYPE;
3079 l_flex_value_set_id fnd_descr_flex_col_usage_vl.flex_value_set_id%TYPE;
3080 dummy VARCHAR2(100);
3081 l_segment_name fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE;
3082 l_column_name
3083 fnd_descr_flex_col_usage_vl.application_column_name%TYPE;
3084 l_column_type fnd_columns.column_type%TYPE;
3085 l_column_width fnd_columns.width%TYPE;
3086 l_table_name fnd_descriptive_flexs.application_table_name%TYPE;
3087
3088 l_last_update_login fnd_descr_flex_col_usage_vl.last_update_login%TYPE
3089 := last_update_login_f;
3090 l_last_update_date fnd_descr_flex_col_usage_vl.last_update_date%TYPE
3091 := last_update_date_f;
3092 l_last_updated_by fnd_descr_flex_col_usage_vl.last_updated_by%TYPE
3093 := last_updated_by_f;
3094 BEGIN
3095 message_init;
3096 l_application_id := application_id_f(p_appl_short_name);
3097 IF (NOT segment_exists(p_appl_short_name,
3098 p_flexfield_name,
3099 p_context_code,
3100 p_segment_name,
3101 p_column_name)) THEN
3102 message('Segment does not exist.');
3103 RAISE bad_parameter;
3104 END IF;
3105
3106 SELECT
3107 description, column_seq_num, enabled_flag,
3108 display_flag, flex_value_set_id, default_type,
3109 default_value, required_flag, security_enabled_flag,
3110 display_size, maximum_description_len, concatenation_description_len,
3111 form_above_prompt, form_left_prompt, range_code, srw_param,
3112 runtime_property_function,
3113 application_column_name,
3114 end_user_column_name
3115 INTO l_description, l_sequence_number, l_enabled,
3116 l_displayed, l_flex_value_set_id, l_default_type,
3117 l_default_value, l_required, l_security_enabled,
3118 l_display_size, l_description_size, l_concat_desc_size,
3119 l_lov_prompt, l_window_prompt, l_range, l_srw_parameter,
3120 l_runtime_property_function,
3121 l_column_name,
3122 l_segment_name
3123 FROM fnd_descr_flex_col_usage_vl
3124 WHERE application_id = l_application_id
3125 AND descriptive_flexfield_name = p_flexfield_name
3126 AND descriptive_flex_context_code = p_context_code
3127 AND (((p_column_name IS NOT NULL) AND
3128 (application_column_name = p_column_name)) OR
3129 ((p_segment_name IS NOT NULL) AND
3130 (end_user_column_name = p_segment_name)));
3131
3132 -- Set the the default_type and defaul_value to be NULL bug8586864
3133
3134 l_default_type := fnd_api.g_miss_char;
3135 l_default_value := fnd_api.g_miss_char;
3136
3137 fnd_descr_flex_col_usage_pkg.update_row
3138 (x_application_id => l_application_id,
3139 x_descriptive_flexfield_name => p_flexfield_name,
3140 x_descriptive_flex_context_cod => p_context_code,
3141 x_application_column_name => l_column_name,
3142 x_end_user_column_name => l_segment_name,
3143 x_column_seq_num => l_sequence_number,
3144 x_enabled_flag => l_enabled,
3145 x_required_flag => l_required,
3146 x_security_enabled_flag => l_security_enabled,
3147 x_display_flag => l_displayed,
3148 x_display_size => l_display_size,
3149 x_maximum_description_len => l_description_size,
3150 x_concatenation_description_le => l_concat_desc_size,
3151 x_flex_value_set_id => l_flex_value_set_id,
3152 x_range_code => l_range,
3153 x_default_type => l_default_type,
3154 x_default_value => l_default_value,
3155 x_runtime_property_function => l_runtime_property_function,
3156 x_srw_param => l_srw_parameter,
3157 x_form_left_prompt => l_window_prompt,
3158 x_form_above_prompt => l_lov_prompt,
3159 x_description => l_description,
3160 x_last_update_date => l_last_update_date,
3161 x_last_updated_by => l_last_updated_by,
3162 x_last_update_login => l_last_update_login);
3163 EXCEPTION
3164 WHEN OTHERS THEN
3165 message('modify_segment_null_default exception. SQLERRM : ' || Sqlerrm);
3166 RAISE;
3167 END modify_segment_null_default;
3168
3169 END fnd_flex_dsc_api;
3170 /* end package */