[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_DSC_API
Source
1 PACKAGE BODY fnd_flex_dsc_api AS
2 /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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.8.12010000.1 2008/07/25 14:13:39 appldev 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 IF (p_default_type = fnd_api.g_miss_char) THEN
1843 NULL;
1844 ELSE
1845 fnd_flex_types.validate_default_type(p_default_type);
1846 l_default_type := p_default_type;
1847 END IF;
1848
1849 IF (p_default_value = fnd_api.g_miss_char) THEN
1850 NULL;
1851 ELSE
1852 l_default_value := p_default_value;
1853 END IF;
1854
1855 IF (p_required = fnd_api.g_miss_char) THEN
1856 NULL;
1857 ELSE
1858 fnd_flex_types.validate_yes_no_flag(p_required);
1859 l_required := p_required;
1860 END IF;
1861
1862 IF (p_security_enabled = fnd_api.g_miss_char) THEN
1863 NULL;
1864 ELSE
1865 fnd_flex_types.validate_yes_no_flag(p_security_enabled);
1866 l_security_enabled := p_security_enabled;
1867 END IF;
1868
1869 IF (p_display_size = fnd_api.g_miss_num) THEN
1870 NULL;
1871 ELSE
1872 l_display_size := p_display_size;
1873 END IF;
1874
1875 IF (p_description_size = fnd_api.g_miss_num) THEN
1876 NULL;
1877 ELSE
1878 l_description_size := p_description_size;
1879 END IF;
1880
1881 IF (p_concat_desc_size = fnd_api.g_miss_num) THEN
1882 NULL;
1883 ELSE
1884 l_concat_desc_size := p_concat_desc_size;
1885 END IF;
1886
1887 IF (p_lov_prompt = fnd_api.g_miss_char) THEN
1888 NULL;
1889 ELSE
1890 l_lov_prompt := p_lov_prompt;
1891 END IF;
1892
1893 IF (p_window_prompt = fnd_api.g_miss_char) THEN
1894 NULL;
1895 ELSE
1896 l_window_prompt := p_window_prompt;
1897 END IF;
1898
1899 IF (p_runtime_property_function = fnd_api.g_miss_char) THEN
1900 NULL;
1901 ELSE
1902 l_runtime_property_function := p_runtime_property_function;
1903 END IF;
1904
1905 IF (p_range = fnd_api.g_miss_char) THEN
1906 NULL;
1907 ELSE
1908 BEGIN
1909 IF (p_range IS NOT NULL) THEN
1910 fnd_flex_types.validate_range_code(p_range);
1911 l_range := p_range;
1912 ELSIF (l_flex_value_set_id IS NOT NULL) THEN
1913 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
1914 Decode(validation_type, 'P', 'P', NULL)
1915 INTO l_range
1916 FROM fnd_flex_value_sets
1917 WHERE flex_value_set_id = l_flex_value_set_id;
1918 END IF;
1919 EXCEPTION
1920 WHEN no_data_found THEN
1921 message('bad range specified');
1922 RAISE bad_parameter;
1923 END;
1924 IF (l_flex_value_set_id IS NOT NULL) THEN
1925 BEGIN
1926 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
1927 NULL
1928 INTO dummy
1929 FROM fnd_flex_value_sets
1930 WHERE flex_value_set_id = l_flex_value_set_id
1931 AND Decode(validation_type, 'P', 'P', 'A') =
1932 Decode(l_range, 'P', 'P', 'A');
1933 EXCEPTION
1934 WHEN no_data_found THEN
1935 message('range pair required with pair validated value set');
1936 RAISE bad_parameter;
1937 END;
1938 END IF;
1939 END IF;
1940
1941 fnd_descr_flex_col_usage_pkg.update_row
1942 (x_application_id => l_application_id,
1943 x_descriptive_flexfield_name => p_flexfield_name,
1944 x_descriptive_flex_context_cod => p_context_code,
1945 x_application_column_name => l_column_name,
1946 x_end_user_column_name => l_segment_name,
1947 x_column_seq_num => l_sequence_number,
1948 x_enabled_flag => l_enabled,
1949 x_required_flag => l_required,
1950 x_security_enabled_flag => l_security_enabled,
1951 x_display_flag => l_displayed,
1952 x_display_size => l_display_size,
1953 x_maximum_description_len => l_description_size,
1954 x_concatenation_description_le => l_concat_desc_size,
1955 x_flex_value_set_id => l_flex_value_set_id,
1956 x_range_code => l_range,
1957 x_default_type => l_default_type,
1958 x_default_value => l_default_value,
1959 x_runtime_property_function => l_runtime_property_function,
1960 x_srw_param => l_srw_parameter,
1961 x_form_left_prompt => l_window_prompt,
1962 x_form_above_prompt => l_lov_prompt,
1963 x_description => l_description,
1964 x_last_update_date => l_last_update_date,
1965 x_last_updated_by => l_last_updated_by,
1966 x_last_update_login => l_last_update_login);
1967 EXCEPTION
1968 WHEN OTHERS THEN
1969 message('modify_segment exception. SQLERRM : ' || Sqlerrm);
1970 RAISE;
1971 END modify_segment;
1972
1973 PROCEDURE create_reference_field(appl_short_name IN VARCHAR2,
1974 flexfield_name IN VARCHAR2,
1975 context_field_name IN VARCHAR2,
1976 description IN VARCHAR2)
1977 IS
1978 application_id_i fnd_application.application_id%TYPE;
1979 BEGIN
1980 message_init;
1981 application_id_i := application_id_f(appl_short_name);
1982
1983 IF(do_validation) THEN
1984 -- check that the dff exists
1985 NULL;
1986 END IF;
1987 ins_default_context_fields(application_id_in => application_id_i,
1988 flexfield_name_in => flexfield_name,
1989 context_field_name_in => context_field_name,
1990 description_in => description);
1991
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 message('error in create_reference_field: ' || SQLCODE);
1995 message(Sqlerrm);
1996 RAISE bad_parameter;
1997 END;
1998
1999
2000 PROCEDURE delete_reference_field(appl_short_name IN VARCHAR2,
2001 flexfield_name IN VARCHAR2,
2002 context_field_name IN VARCHAR2)
2003 IS
2004 application_id_i fnd_application.application_id%TYPE;
2005 rec_count NUMBER;
2006 BEGIN
2007 message_init;
2008 application_id_i := application_id_f(appl_short_name);
2009 IF(do_validation) THEN
2010 -- A row cannot be deleted if the context field name
2011 -- is used as the default context field name for the
2012 -- descriptive flexfield.
2013 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2014 count('x')
2015 INTO rec_count
2016 FROM fnd_descriptive_flexs
2017 WHERE application_id = application_id_i
2018 AND descriptive_flexfield_name = flexfield_name
2019 AND default_context_field_name = context_field_name;
2020
2021 IF (rec_count > 0) THEN
2022 message('this reference field is in use in a flexfield');
2023 RAISE bad_parameter;
2024 END IF;
2025 END IF;
2026 END;
2027
2028 PROCEDURE disable_columns(appl_short_name IN VARCHAR2,
2029 flexfield_name IN VARCHAR2,
2030 pattern IN VARCHAR2)
2031 IS
2032 last_update_login_i fnd_flex_value_sets.last_update_login%TYPE
2033 := last_update_login_f;
2034 last_update_date_i fnd_flex_value_sets.last_update_date%TYPE
2035 := last_update_date_f;
2036 last_updated_by_i fnd_flex_value_sets.last_updated_by%TYPE
2037 := last_updated_by_f;
2038 application_id_i fnd_application.application_id%TYPE;
2039 table_application_id_i fnd_application.application_id%TYPE;
2040 application_table_id_i fnd_tables.table_id%TYPE;
2041 BEGIN
2042 message_init;
2043 println('disabling columns for:' || flexfield_name);
2044 application_id_i := application_id_f(appl_short_name);
2045 BEGIN
2046 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2047 table_application_id, table_id
2048 INTO table_application_id_i, application_table_id_i
2049 FROM fnd_descriptive_flexs df, fnd_tables t
2050 WHERE df.application_id = application_id_i
2051 AND df.descriptive_flexfield_name = flexfield_name
2052 AND t.application_id = df.table_application_id
2053 AND t.table_name = df.application_table_name;
2054 EXCEPTION
2055 WHEN no_data_found THEN
2056 message('could not lookup table information for: ' ||
2057 flexfield_name);
2058 RAISE bad_parameter;
2059 END;
2060 BEGIN
2061 IF(NOT customer_mode) THEN
2062 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2063 fnd_columns SET
2064 flexfield_usage_code = 'N',
2065 flexfield_application_id = NULL,
2066 flexfield_name = NULL
2067 WHERE application_id = table_application_id_i
2068 AND table_id = application_table_id_i
2069 AND flexfield_usage_code = 'D'
2070 AND column_name LIKE pattern;
2071 ELSE
2072 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2073 fnd_columns SET
2074 flexfield_usage_code = 'N',
2075 flexfield_application_id = application_id_i,
2076 flexfield_name = disable_columns.flexfield_name,
2077 last_update_date = last_update_date_i,
2078 last_updated_by = last_updated_by_i,
2079 last_update_login = last_update_login_i
2080 WHERE application_id = table_application_id_i
2081 AND table_id = application_table_id_i
2082 AND flexfield_usage_code = 'D'
2083 AND column_name LIKE pattern;
2084 END IF;
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 message('could not update fnd_columns:' ||
2088 Sqlerrm);
2089 RAISE bad_parameter;
2090 END;
2091 END;
2092
2093 PROCEDURE delete_flexfield(appl_short_name IN VARCHAR2,
2094 flexfield_name IN VARCHAR2)
2095 IS
2096 application_id_i fnd_application.application_id%TYPE;
2097 table_application_id_i fnd_application.application_id%TYPE;
2098 BEGIN
2099 message_init;
2100 application_id_i := application_id_f(appl_short_name);
2101
2102 BEGIN
2103 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2104 FROM fnd_descr_flex_col_usage_tl
2105 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2106 AND application_id = application_id_i;
2107 println('deleted from fnd_descr_col_usage_tl');
2108 END;
2109
2110 BEGIN
2111 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2112 FROM fnd_descr_flex_column_usages
2113 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2114 AND application_id = application_id_i;
2115 println('deleted from fnd_descr_column_usages');
2116 END;
2117
2118 BEGIN
2119 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2120 FROM fnd_descr_flex_contexts_tl
2121 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2122 AND application_id = application_id_i;
2123 println('deleted from fnd_descr_flex_contexts_tl');
2124 END;
2125
2126 BEGIN
2127 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2128 FROM fnd_descr_flex_contexts
2129 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2130 AND application_id = application_id_i;
2131 println('deleted from fnd_descr_flex_contexts');
2132 END;
2133
2134 BEGIN
2135 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2136 FROM fnd_descriptive_flexs_tl
2137 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2138 AND application_id = application_id_i;
2139 println('deleted from fnd_descriptive_flexs_tl');
2140 END;
2141
2142 BEGIN
2143 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2144 table_application_id
2145 INTO table_application_id_i
2146 FROM fnd_descriptive_flexs
2147 WHERE descriptive_flexfield_name = flexfield_name
2148 AND application_id = application_id_i;
2149
2150 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2151 fnd_columns SET
2152 flexfield_usage_code = 'N',
2153 flexfield_name = NULL
2154 WHERE flexfield_name = delete_flexfield.flexfield_name
2155 AND application_id = table_application_id_i;
2156 println('updated fnd_columns');
2157 EXCEPTION
2158 WHEN no_data_found THEN
2159 NULL;
2160 END;
2161
2162 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2163 FROM fnd_descriptive_flexs
2164 WHERE descriptive_flexfield_name = delete_flexfield.flexfield_name
2165 AND application_id = application_id_i;
2166 println('deleted from fnd_descriptive_flexs');
2167
2168 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2169 FROM fnd_default_context_fields
2170 WHERE application_id = application_id_i
2171 AND descriptive_flexfield_name = flexfield_name;
2172 println('deleted from fnd_default_context_fields');
2173
2174 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2175 FROM fnd_compiled_descriptive_flexs
2176 WHERE application_id = application_id_i
2177 AND descriptive_flexfield_name = flexfield_name;
2178 println('deleted fnd_compiled_descriptive_flexs');
2179
2180
2181 println('deleted flexfield:' || flexfield_name);
2182 END;
2183
2184
2185
2186
2187 PROCEDURE enable_context(appl_short_name IN VARCHAR2,
2188 flexfield_name IN VARCHAR2,
2189 context IN VARCHAR2,
2190 enable IN BOOLEAN DEFAULT TRUE)
2191 IS
2192 application_id_i fnd_application.application_id%TYPE;
2193 enabled_flag_i VARCHAR2(1);
2194 cnt NUMBER;
2195 BEGIN
2196 message_init;
2197 application_id_i := application_id_f(appl_short_name);
2198 IF(enable) THEN
2199 enabled_flag_i := 'Y';
2200 ELSE
2201 enabled_flag_i := 'N';
2202 END IF;
2203 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2204 fnd_descr_flex_contexts
2205 SET enabled_flag = enabled_flag_i
2206 WHERE descriptive_flex_context_code = context
2207 AND descriptive_flexfield_name = flexfield_name
2208 AND application_id = application_id_i;
2209 IF(SQL%rowcount = 0) THEN
2210 message('context ' || context ||
2211 ' in flexfield ' || appl_short_name ||
2212 '/' || flexfield_name || ' not found');
2213 raise_application_error(-20000, message);
2214 END IF;
2215 END;
2216
2217 /* New API added for Bug 4390452 */
2218 PROCEDURE update_context(
2219 p_appl_short_name IN VARCHAR2,
2220 p_flexfield_name IN VARCHAR2,
2221 p_desc_flex_context_code IN VARCHAR2,
2222 p_desc_flex_context_name IN VARCHAR2 DEFAULT NULL,
2223 p_description IN VARCHAR2 DEFAULT NULL,
2224 p_enabled_flag IN VARCHAR2 DEFAULT NULL,
2225 p_language IN VARCHAR2)
2226 IS
2227 l_application_id fnd_application.application_id%TYPE;
2228 l_boolean_flag1 BOOLEAN;
2229 l_lang_exists NUMBER;
2230 l_dflex_contexts_tl fnd_descr_flex_contexts_tl%ROWTYPE;
2231 l_enabled_flag fnd_descr_flex_contexts.enabled_flag%TYPE;
2232 BEGIN
2233 begin
2234 select 1 into l_lang_exists from fnd_languages where
2235 language_code = p_language and installed_flag in ('B','I') and rownum < 2;
2236 exception
2237 when no_data_found then
2238 raise_application_error(-20204, 'FND_FLEX_DSC_API.UPDATE_CONTEXT raised exception: No language exist with language_code ='|| p_language, TRUE);
2239 end;
2240 /* Check if context exists */
2241 l_boolean_flag1 := context_exists(p_appl_short_name,
2242 p_flexfield_name,
2243 p_desc_flex_context_code);
2244 IF (l_boolean_flag1=TRUE) THEN
2245 IF (not(p_desc_flex_context_name is NULL AND p_description is NULL and p_enabled_flag is NULL)) THEN
2246 l_application_id := application_id_f(p_appl_short_name);
2247 IF (not(p_desc_flex_context_name is NULL AND p_description is NULL)) THEN
2248 SELECT * into l_dflex_contexts_tl
2249 from fnd_descr_flex_contexts_tl
2250 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2251 AND descriptive_flexfield_name = p_flexfield_name
2252 AND application_id = l_application_id
2253 AND language = p_language;
2254
2255 UPDATE fnd_descr_flex_contexts_tl
2256 SET descriptive_flex_context_name = nvl(p_desc_flex_context_name, l_dflex_contexts_tl.descriptive_flex_context_name),
2257 description = nvl(p_description, l_dflex_contexts_tl.description)
2258 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2259 AND descriptive_flexfield_name = p_flexfield_name
2260 AND application_id = l_application_id
2261 AND language = p_language;
2262 END IF;
2263
2264 IF (p_enabled_flag is not NULL) THEN
2265 IF (p_enabled_flag in ('Y','N')) THEN
2266 SELECT enabled_flag into l_enabled_flag from fnd_descr_flex_contexts
2267 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2268 AND descriptive_flexfield_name = p_flexfield_name
2269 AND application_id = l_application_id;
2270
2271 UPDATE fnd_descr_flex_contexts
2272 SET enabled_flag = nvl(p_enabled_flag, l_enabled_flag)
2273 WHERE descriptive_flex_context_code = p_desc_flex_context_code
2274 AND descriptive_flexfield_name = p_flexfield_name
2275 AND application_id = l_application_id;
2276 ELSE
2277 raise_application_error(-20205, 'Enabled flag is not set properly. It has to be either Y/N');
2278 END IF;
2279 END IF;
2280 END IF;
2281 ELSE
2282 message('Context ' || p_desc_flex_context_code ||
2283 ' in flexfield ' || p_appl_short_name ||
2284 '/' || p_flexfield_name || ' not found');
2285 raise_application_error(-20000, message);
2286 END IF;
2287 END;
2288
2289
2290
2291
2292 PROCEDURE delete_context(appl_short_name IN VARCHAR2,
2293 flexfield_name IN VARCHAR2,
2294 context IN VARCHAR2)
2295 IS
2296 application_id_i fnd_application.application_id%TYPE;
2297 rec_count NUMBER;
2298 BEGIN
2299 message_init;
2300 application_id_i := application_id_f(appl_short_name);
2301
2302 -- add this later
2303 -- when we have a delete reference field also
2304 IF(FALSE AND do_validation) THEN
2305 -- A row cannot be deleted if the context field name
2306 -- is used as the default context field name for the
2307 -- descriptive flexfield.
2308 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2309 count('x')
2310 INTO rec_count
2311 FROM fnd_descriptive_flexs
2312 WHERE application_id = application_id_i
2313 AND descriptive_flexfield_name = flexfield_name
2314 AND default_context_field_name = context;
2315
2316 IF (rec_count > 0) THEN
2317 message('this context field is a reference field');
2318 RAISE bad_parameter;
2319 END IF;
2320 END IF;
2321
2322 BEGIN
2323 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2324 FROM fnd_descr_flex_col_usage_tl
2325 WHERE descriptive_flex_context_code = context
2326 AND descriptive_flexfield_name = delete_context.flexfield_name
2327 AND application_id = application_id_i;
2328 println('deleted from fnd_descr_col_usage_tl');
2329 EXCEPTION
2330 WHEN no_data_found THEN
2331 NULL;
2332 END;
2333
2334 BEGIN
2335 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2336 FROM fnd_descr_flex_column_usages
2337 WHERE descriptive_flex_context_code = context
2338 AND descriptive_flexfield_name = delete_context.flexfield_name
2339 AND application_id = application_id_i;
2340 println('deleted from fnd_descr_column_usages');
2341 EXCEPTION
2342 WHEN no_data_found THEN
2343 NULL;
2344 END;
2345
2346 BEGIN
2347 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2348 FROM fnd_descr_flex_contexts_tl
2349 WHERE descriptive_flex_context_code = context
2350 AND descriptive_flexfield_name = delete_context.flexfield_name
2351 AND application_id = application_id_i;
2352 println('deleted from fnd_descr_flex_contexts_tl');
2353 EXCEPTION
2354 WHEN no_data_found THEN
2355 NULL;
2356 END;
2357
2358 BEGIN
2359 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2360 FROM fnd_descr_flex_contexts
2361 WHERE descriptive_flex_context_code = context
2362 AND descriptive_flexfield_name = delete_context.flexfield_name
2363 AND application_id = application_id_i;
2364 println('deleted from fnd_descr_flex_contexts');
2365 EXCEPTION
2366 WHEN no_data_found THEN
2367 NULL;
2368 END;
2369
2370 println('deleted flexfield context:' ||
2371 flexfield_name || '.' || context);
2372 END;
2373
2374
2375
2376 PROCEDURE delete_segment(appl_short_name IN VARCHAR2,
2377 flexfield_name IN VARCHAR2,
2378 context IN VARCHAR2,
2379 segment IN VARCHAR2)
2380 IS
2381 application_id_i fnd_application.application_id%TYPE;
2382 BEGIN
2383 message_init;
2384 application_id_i := application_id_f(appl_short_name);
2385
2386 BEGIN
2387 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2388 FROM fnd_descr_flex_col_usage_tl t
2389 WHERE
2390 EXISTS (SELECT *
2391 FROM fnd_descr_flex_column_usages cu
2392 WHERE cu.end_user_column_name = segment
2393 AND cu.descriptive_flex_context_code = context
2394 AND cu.descriptive_flexfield_name = flexfield_name
2395 AND cu.application_id = application_id_i
2396 /* and join cond: */
2397 AND cu.application_column_name = t.application_column_name
2398 AND cu.descriptive_flex_context_code =
2399 t.descriptive_flex_context_code
2400 AND cu.descriptive_flexfield_name = t.descriptive_flexfield_name
2401 AND cu.application_id = t.application_id);
2402 println('deleted from fnd_descr_col_usage_tl');
2403 EXCEPTION
2404 WHEN no_data_found THEN
2405 NULL;
2406 END;
2407
2408 BEGIN
2409 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2410 FROM fnd_descr_flex_column_usages
2411 WHERE end_user_column_name = segment
2412 AND descriptive_flex_context_code = context
2413 AND descriptive_flexfield_name = flexfield_name
2414 AND application_id = application_id_i;
2415 println('deleted from fnd_descr_column_usages');
2416 EXCEPTION
2417 WHEN no_data_found THEN
2418 NULL;
2419 END;
2420
2421 println('deleted flexfield segment:' ||
2422 flexfield_name || '.' || context || '.' || segment);
2423 END;
2424
2425
2426 FUNCTION flexfield_exists(appl_short_name IN VARCHAR2,
2427 flexfield_name IN VARCHAR2) RETURN BOOLEAN
2428 IS
2429 application_id_i fnd_application.application_id%TYPE;
2430 cnt NUMBER;
2431 BEGIN
2432 message_init;
2433 application_id_i := application_id_f(appl_short_name);
2434 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2435 COUNT(*)
2436 INTO cnt
2437 FROM fnd_descriptive_flexs
2438 WHERE application_id = application_id_i
2439 AND descriptive_flexfield_name = flexfield_name;
2440 IF(cnt > 0) THEN
2441 RETURN TRUE;
2442 ELSE
2443 RETURN FALSE;
2444 END IF;
2445 EXCEPTION
2446 WHEN OTHERS THEN
2447 message('exception in flexfield_exists:' || Sqlerrm);
2448 RAISE bad_parameter;
2449 END;
2450
2451 FUNCTION context_exists(p_appl_short_name IN VARCHAR2,
2452 p_flexfield_name IN VARCHAR2,
2453 p_context_code IN VARCHAR2) RETURN BOOLEAN
2454 IS
2455 l_application_id fnd_application.application_id%TYPE;
2456 BEGIN
2457 message_init;
2458 l_application_id := application_id_f(p_appl_short_name);
2459 BEGIN
2460 check_existance(l_application_id, p_flexfield_name);
2461 check_existance(l_application_id, p_flexfield_name, p_context_code);
2462 EXCEPTION
2463 WHEN OTHERS THEN
2464 RETURN(FALSE);
2465 END;
2466 RETURN(TRUE);
2467 EXCEPTION
2468 WHEN OTHERS THEN
2469 NULL;
2470 RAISE;
2471 END context_exists;
2472
2473 FUNCTION segment_exists(p_appl_short_name IN VARCHAR2,
2474 p_flexfield_name IN VARCHAR2,
2475 p_context_code IN VARCHAR2,
2476 p_segment_name IN VARCHAR2 DEFAULT NULL,
2477 p_column_name IN VARCHAR2 DEFAULT NULL)
2478 RETURN BOOLEAN
2479 IS
2480 l_application_id fnd_application.application_id%TYPE;
2481 cnt NUMBER;
2482 BEGIN
2483 message_init;
2484 l_application_id := application_id_f(p_appl_short_name);
2485 BEGIN
2486 check_existance(l_application_id, p_flexfield_name);
2487 check_existance(l_application_id, p_flexfield_name, p_context_code);
2488 EXCEPTION
2489 WHEN OTHERS THEN
2490 RETURN(FALSE);
2491 END;
2492 IF (((p_column_name IS NULL) AND (p_segment_name IS NULL)) OR
2493 ((p_column_name IS NOT NULL) AND (p_segment_name IS NOT NULL))) THEN
2494 message('Please pass either p_column_name or p_segment_name but not both.');
2495 RAISE bad_parameter;
2496 END IF;
2497 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2498 COUNT(*) INTO cnt
2499 FROM fnd_descr_flex_column_usages
2500 WHERE application_id = l_application_id
2501 AND descriptive_flexfield_name = p_flexfield_name
2502 AND descriptive_flex_context_code = p_context_code
2503 AND (((p_column_name IS NOT NULL) AND
2504 (application_column_name = p_column_name)) OR
2505 ((p_segment_name IS NOT NULL) AND
2506 (end_user_column_name = p_segment_name)));
2507 IF (cnt > 0) THEN
2508 RETURN(TRUE);
2509 ELSE
2510 RETURN(FALSE);
2511 END IF;
2512 EXCEPTION
2513 WHEN OTHERS THEN
2514 NULL;
2515 RAISE;
2516 END segment_exists;
2517
2518 FUNCTION is_table_used(p_application_id IN fnd_tables.application_id%TYPE,
2519 p_table_name IN fnd_tables.table_name%TYPE,
2520 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN
2521 IS
2522 up_table_name fnd_tables.table_name%TYPE := Upper(p_table_name);
2523 l_a_id fnd_descriptive_flexs.application_id%TYPE;
2524 l_dff_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2525 BEGIN
2526 x_message := 'This table is not used by Descriptive Flexfields.';
2527 BEGIN
2528 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2529 application_id, descriptive_flexfield_name
2530 INTO l_a_id, l_dff_name
2531 FROM fnd_descriptive_flexs
2532 WHERE table_application_id = p_application_id
2533 AND Upper(application_table_name) = up_table_name
2534 AND ROWNUM = 1;
2535 x_message :=
2536 'This table is used by ' || chr_newline ||
2537 'APPLICATION_ID : ' || l_a_id || chr_newline ||
2538 'DESCRIPTIVE_FLEXFIELD_NAME : ' || l_dff_name;
2539 RETURN(TRUE);
2540 EXCEPTION
2541 WHEN no_data_found THEN
2542 NULL;
2543 WHEN OTHERS THEN
2544 x_message :=
2545 'SELECT FROM FND_DESCRIPTIVE_FLEXS is failed. ' || chr_newline ||
2546 'SQLERRM : ' || Sqlerrm;
2547 RETURN(TRUE);
2548 END;
2549 RETURN(FALSE);
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552 x_message :=
2553 'FND_FLEX_DSC_API.IS_TABLE_USED is failed. ' || chr_newline ||
2554 'SQLERRM : ' || Sqlerrm;
2555 RETURN(TRUE);
2556 END is_table_used;
2557
2558
2559 FUNCTION is_column_used(p_application_id IN fnd_tables.application_id%TYPE,
2560 p_table_name IN fnd_tables.table_name%TYPE,
2561 p_column_name IN fnd_columns.column_name%TYPE,
2562 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN
2563 IS
2564 up_table_name fnd_tables.table_name%TYPE := Upper(p_table_name);
2565 up_column_name fnd_columns.column_name%TYPE := Upper(p_column_name);
2566 l_a_id fnd_descriptive_flexs.application_id%TYPE;
2567 l_dff_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2568 l_context_col fnd_descriptive_flexs.context_column_name%TYPE;
2569 l_context fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE;
2570 l_segment fnd_descr_flex_column_usages.end_user_column_name%TYPE;
2571 BEGIN
2572 x_message := 'This column is not used by Descriptive Flexfields.';
2573 BEGIN
2574 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2575 application_id, descriptive_flexfield_name,
2576 context_column_name
2577 INTO l_a_id, l_dff_name, l_context_col
2578 FROM fnd_descriptive_flexs
2579 WHERE table_application_id = p_application_id
2580 AND Upper(application_table_name) = up_table_name
2581 AND Upper(context_column_name) = up_column_name
2582 AND ROWNUM = 1;
2583 x_message :=
2584 'This column is used by ' || chr_newline ||
2585 'APPLICATION_ID : ' || l_a_id || chr_newline ||
2586 'DESCRIPTIVE_FLEXFIELD_NAME : ' || l_dff_name || chr_newline ||
2587 'CONTEXT_COLUMN_NAME : ' || l_context_col;
2588 RETURN(TRUE);
2589 EXCEPTION
2590 WHEN no_data_found THEN
2591 NULL;
2592 WHEN OTHERS THEN
2593 x_message :=
2594 'SELECT FROM FND_DESCRIPTIVE_FLEXS is failed.'||chr_newline ||
2595 'SQLERRM : ' || Sqlerrm;
2596 RETURN(TRUE);
2597 END;
2598
2599 BEGIN
2600 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2601 dfcu.application_id, dfcu.descriptive_flexfield_name,
2602 dfcu.descriptive_flex_context_code, dfcu.end_user_column_name
2603 INTO l_a_id, l_dff_name, l_context, l_segment
2604 FROM fnd_descr_flex_column_usages dfcu, fnd_descriptive_flexs df
2605 WHERE df.application_id = dfcu.application_id
2606 AND df.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
2607 AND df.table_application_id = p_application_id
2608 AND Upper(df.application_table_name) = up_table_name
2609 AND Upper(dfcu.application_column_name) = up_column_name
2610 AND ROWNUM = 1;
2611 x_message :=
2612 'This column is used by ' || chr_newline ||
2613 'APPLICATION_ID : ' || l_a_id || chr_newline ||
2614 'DESCRIPTIVE_FLEXFIELD_NAME : ' || l_dff_name || chr_newline ||
2615 'CONTEXT_CODE : ' || l_context || chr_newline ||
2616 'SEGMENT_NAME : ' || l_segment;
2617 RETURN(TRUE);
2618 EXCEPTION
2619 WHEN no_data_found THEN
2620 NULL;
2621 WHEN OTHERS THEN
2622 x_message :=
2623 'SELECT FROM FND_DESCR_FLEX_COLUMN_USAGES is failed.'||chr_newline ||
2624 'SQLERRM : ' || Sqlerrm;
2625 RETURN(TRUE);
2626 END;
2627 RETURN(FALSE);
2628 EXCEPTION
2629 WHEN OTHERS THEN
2630 x_message :=
2631 'FND_FLEX_DSC_API.IS_COLUMN_USED is failed. ' || chr_newline ||
2632 'SQLERRM : ' || Sqlerrm;
2633 RETURN(TRUE);
2634 END is_column_used;
2635
2636 -- This API gets the application details
2637 PROCEDURE get_application
2638 (
2639 p_application_short_name IN fnd_application.application_short_name%TYPE,
2640 x_application OUT NOCOPY fnd_app_type
2641 ) IS
2642 BEGIN
2643 SELECT *
2644 INTO x_application
2645 FROM fnd_application fa
2646 WHERE fa.application_short_name = p_application_short_name;
2647 EXCEPTION
2648 WHEN NO_DATA_FOUND THEN
2649 RAISE_APPLICATION_ERROR(-20200,'No Application exists with application_short_name = '
2650 || p_application_short_name,TRUE);
2651 END get_application;
2652
2653 -- This API gets the table details
2654 PROCEDURE get_table
2655 (
2656 p_application_id IN fnd_application.application_id%TYPE,
2657 p_table_name IN fnd_tables.table_name%TYPE,
2658 x_table OUT NOCOPY fnd_tbl_type
2659 ) IS
2660 BEGIN
2661 SELECT *
2662 INTO x_table
2663 FROM fnd_tables ft
2664 WHERE ft.application_id = p_application_id
2665 AND ft.table_name = p_table_name;
2666 EXCEPTION
2667 WHEN NO_DATA_FOUND THEN
2668 RAISE_APPLICATION_ERROR(-20201,'No table exists with application_id = ' || p_application_id
2669 || ' and table_name = ' || p_table_name,TRUE);
2670 END get_table;
2671
2672 -- This API gets the Descriptive Flexfield details
2673 PROCEDURE get_descriptive_flexfield
2674 (
2675 p_application_id IN fnd_application.application_id%TYPE,
2676 p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE,
2677 x_descriptive_flexfield OUT NOCOPY fnd_dff_type
2678 ) IS
2679 BEGIN
2680 SELECT *
2681 INTO x_descriptive_flexfield
2682 FROM fnd_descriptive_flexs fdff
2683 WHERE fdff.application_id = p_application_id
2684 AND fdff.descriptive_flexfield_name = p_descriptive_flexfield_name;
2685 EXCEPTION
2686 WHEN NO_DATA_FOUND THEN
2687 RAISE_APPLICATION_ERROR(-20203,'No descriptive flexfield exists with application_id = ' ||
2688 p_application_id || ' and descriptive_flexfield_name = ' || p_descriptive_flexfield_name,TRUE);
2689 END get_descriptive_flexfield;
2690
2691 -- This API renames an exisisting descriptive flexfield
2692 PROCEDURE rename_dff
2693 (
2694 p_old_application_short_name IN fnd_application.application_short_name%TYPE,
2695 p_old_dff_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE,
2696 p_new_application_short_name IN fnd_application.application_short_name%TYPE,
2697 p_new_dff_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE
2698 ) IS
2699
2700 l_old_application fnd_app_type;
2701 l_new_application fnd_app_type;
2702 l_new_dff fnd_dff_type;
2703 l_old_dff fnd_dff_type;
2704 l_table fnd_tbl_type;
2705 l_dff_name fnd_descriptive_flexs.descriptive_flexfield_name%TYPE;
2706 l_error_message VARCHAR2(4000);
2707 l_illegal_character VARCHAR2(1000);
2708 l_last_update_date fnd_descriptive_flexs.last_update_date%TYPE;
2709 l_last_updated_by fnd_descriptive_flexs.last_updated_by%TYPE;
2710 l_last_update_login fnd_descriptive_flexs.last_update_login%TYPE;
2711 BEGIN
2712 l_last_update_date := SYSDATE;
2713 l_last_updated_by := fnd_global.user_id();
2714 l_last_update_login := fnd_global.login_id();
2715
2716 -- If l_last_updated_by IS NULL or equals to -1 or l_last_update_login IS NULL then ask user to set context
2717 IF ((l_last_updated_by IS NULL) OR (l_last_updated_by = -1)) OR (l_last_update_login IS NULL) THEN
2718 l_error_message := 'Application Security Context is not set.Please set the context using fnd_global.apps_initialize() and try again.';
2719 RAISE_APPLICATION_ERROR(error_context_not_set, l_error_message, TRUE);
2720 END IF;
2721
2722 -- make sure the old and new dff names are different
2723 IF p_old_application_short_name = p_new_application_short_name AND
2724 p_old_dff_name = p_new_dff_name THEN
2725 l_error_message := 'The old and new DFF names are same.';
2726 RAISE_APPLICATION_ERROR(error_same_dff_name, l_error_message, TRUE);
2727 END IF;
2728
2729 l_illegal_character := REPLACE(TRANSLATE(UPPER(p_new_dff_name), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_$. ',
2730 ' '), ' ', '');
2731 IF p_new_dff_name IS NULL THEN
2732 l_error_message := 'The new DFF name is NULL';
2733 RAISE_APPLICATION_ERROR(error_invalid_dff_name, l_error_message, TRUE);
2734 ELSIF LENGTH(p_new_dff_name) > 40 THEN
2735 l_error_message := 'The name of the new DFF is more than 40 characters';
2736 RAISE_APPLICATION_ERROR(error_invalid_dff_name, l_error_message, TRUE);
2737 ELSIF l_illegal_character IS NOT NULL THEN
2738 l_error_message := 'The new DFF name contains illegal characters (' || l_illegal_character || ')';
2739 RAISE_APPLICATION_ERROR(error_invalid_dff_name, l_error_message, TRUE);
2740 END IF;
2741
2742 -- Get the old application Id
2743 get_application(p_application_short_name => p_old_application_short_name,
2744 x_application => l_old_application);
2745
2746 -- Get the new application Id
2747 get_application(p_application_short_name => p_new_application_short_name,
2748 x_application => l_new_application);
2749
2750 --Get the old DFF and make sure the old DFF exists.
2751 get_descriptive_flexfield(p_application_id => l_old_application.application_id,
2752 p_descriptive_flexfield_name => p_old_dff_name,
2753 x_descriptive_flexfield =>l_old_dff);
2754
2755 -- Get the table_id
2756 get_table(p_application_id => l_old_dff.table_application_id,
2757 p_table_name => l_old_dff.application_table_name,
2758 x_table => l_table);
2759
2760 --Make sure the new DFF doesn't exist.
2761 BEGIN
2762 SELECT /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2763 descriptive_flexfield_name
2764 INTO l_dff_name
2765 FROM fnd_descriptive_flexs
2766 WHERE application_id = l_new_application.application_id
2767 AND descriptive_flexfield_name = p_new_dff_name;
2768 l_error_message := 'A DFF with name ' || p_new_dff_name || ' already exists for the application table ' || l_old_dff.application_table_name;
2769 RAISE_APPLICATION_ERROR(error_dff_already_exists, l_error_message, TRUE);
2770 EXCEPTION
2771 WHEN NO_DATA_FOUND THEN
2772 NULL;
2773 END;
2774
2775
2776 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2777 fnd_descriptive_flexs
2778 SET application_id = l_new_application.application_id,
2779 descriptive_flexfield_name = p_new_dff_name,
2780 last_update_date = l_last_update_date,
2781 last_updated_by = l_last_updated_by,
2782 last_update_login = l_last_update_login
2783 WHERE application_id = l_old_application.application_id
2784 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2785
2786 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2787 fnd_descriptive_flexs_tl
2788 SET application_id = l_new_application.application_id,
2789 descriptive_flexfield_name = p_new_dff_name,
2790 last_update_date = l_last_update_date,
2791 last_updated_by = l_last_updated_by,
2792 last_update_login = l_last_update_login
2793 WHERE application_id = l_old_application.application_id
2794 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2795
2796 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2797 fnd_default_context_fields
2798 SET application_id = l_new_application.application_id,
2799 descriptive_flexfield_name = p_new_dff_name,
2800 last_update_date = l_last_update_date,
2801 last_updated_by = l_last_updated_by,
2802 last_update_login = l_last_update_login
2803 WHERE application_id = l_old_application.application_id
2804 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2805
2806 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2807 fnd_descr_flex_contexts
2808 SET application_id = l_new_application.application_id,
2809 descriptive_flexfield_name = p_new_dff_name,
2810 last_update_date = l_last_update_date,
2811 last_updated_by = l_last_updated_by,
2812 last_update_login = l_last_update_login
2813 WHERE application_id = l_old_application.application_id
2814 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2815
2816 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2817 fnd_descr_flex_contexts_tl
2818 SET application_id = l_new_application.application_id,
2819 descriptive_flexfield_name = p_new_dff_name,
2820 last_update_date = l_last_update_date,
2821 last_updated_by = l_last_updated_by,
2822 last_update_login = l_last_update_login
2823 WHERE application_id = l_old_application.application_id
2824 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2825
2826 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2827 fnd_descr_flex_column_usages
2828 SET application_id = l_new_application.application_id,
2829 descriptive_flexfield_name = p_new_dff_name,
2830 last_update_date = l_last_update_date,
2831 last_updated_by = l_last_updated_by,
2832 last_update_login = l_last_update_login
2833 WHERE application_id = l_old_application.application_id
2834 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2835
2836 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2837 fnd_descr_flex_col_usage_tl
2838 SET application_id = l_new_application.application_id,
2839 descriptive_flexfield_name = p_new_dff_name,
2840 last_update_date = l_last_update_date,
2841 last_updated_by = l_last_updated_by,
2842 last_update_login = l_last_update_login
2843 WHERE application_id = l_old_application.application_id
2844 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2845
2846 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2847 fnd_columns
2848 SET flexfield_application_id = l_new_application.application_id,
2849 flexfield_name = p_new_dff_name,
2850 last_update_date = l_last_update_date,
2851 last_updated_by = l_last_updated_by,
2852 last_update_login = l_last_update_login
2853 WHERE application_id = l_table.application_id
2854 AND table_id = l_table.table_id
2855 AND flexfield_application_id = l_old_dff.application_id
2856 AND flexfield_name = l_old_dff.descriptive_flexfield_name
2857 AND flexfield_usage_code IN ('C','D');
2858
2859 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2860 FROM fnd_compiled_descriptive_flexs
2861 WHERE application_id = l_old_application.application_id
2862 AND descriptive_flexfield_name = l_old_dff.descriptive_flexfield_name;
2863
2864 DELETE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
2865 FROM fnd_compiled_descriptive_flexs
2866 WHERE application_id = l_new_application.application_id
2867 AND descriptive_flexfield_name = l_new_dff.descriptive_flexfield_name;
2868 EXCEPTION
2869 WHEN OTHERS THEN
2870 RAISE_APPLICATION_ERROR(error_others, 'FND_FLEX_DSC_API.RENAME_DFF raised exception: ' || sqlerrm || ' errcode : ' || sqlcode, TRUE);
2871 END rename_dff;
2872
2873 -- This API makes the necessary updates to change the base table of a given DFF.
2874 -- The only requirement is that the new table must have the same column names,
2875 -- same column types, and same size or bigger size columns. (For ATTRIBUTE columns).
2876
2877 PROCEDURE migrate_dff
2878 (
2879 p_application_short_name IN fnd_application.application_short_name%TYPE,
2880 p_descriptive_flexfield_name IN fnd_descriptive_flexs.descriptive_flexfield_name%TYPE,
2881 p_new_table_appl_short_name IN fnd_application.application_short_name%TYPE,
2882 p_new_table_name IN fnd_tables.table_name%TYPE
2883 ) IS
2884
2885 l_application fnd_app_type;
2886 l_dff fnd_dff_type;
2887
2888 l_old_table fnd_tbl_type;
2889 l_new_table_app fnd_app_type;
2890 l_new_table fnd_tbl_type;
2891
2892 l_column_name fnd_columns.column_name%TYPE;
2893 l_error_message VARCHAR2(4000);
2894
2895 l_error_msg VARCHAR2(4000) := '';
2896 l_last_update_date fnd_descriptive_flexs.last_update_date%TYPE;
2897 l_last_updated_by fnd_descriptive_flexs.last_updated_by%TYPE;
2898 l_last_update_login fnd_descriptive_flexs.last_update_login%TYPE;
2899
2900 CURSOR cur_columns_reg(p_dff fnd_dff_type,
2901 p_old_table fnd_tbl_type,
2902 p_new_table fnd_tbl_type) IS
2903 SELECT old.column_name old_column_name,
2904 old.column_type old_column_type,
2905 old.width old_width,
2906 old.flexfield_usage_code old_flexfield_usage_code,
2907 old.flexfield_application_id old_flexfield_application_id,
2908 old.flexfield_name old_flexfield_name,
2909 new.column_name new_column_name,
2910 new.column_type new_column_type,
2911 new.width new_width,
2912 new.flexfield_usage_code new_flexfield_usage_code,
2913 new.flexfield_application_id new_flexfield_application_id,
2914 new.flexfield_name new_flexfield_name
2915 FROM fnd_columns old, fnd_columns new
2916 WHERE old.application_id = p_old_table.application_id
2917 AND old.table_id = p_old_table.table_id
2918
2919 AND old.flexfield_usage_code in ('C', 'D')
2920 AND old.flexfield_application_id = p_dff.application_id
2921 AND old.flexfield_name = p_dff.descriptive_flexfield_name
2922
2923 AND new.application_id(+) = p_new_table.application_id
2924 AND new.table_id(+) = p_new_table.table_id
2925
2926 AND new.column_name(+) = old.column_name
2927 ORDER BY old.column_sequence;
2928
2929 BEGIN
2930 l_last_update_date := SYSDATE;
2931 l_last_updated_by := fnd_global.user_id();
2932 l_last_update_login := fnd_global.login_id();
2933
2934 -- If l_last_updated_by IS NULL or equals to -1 or l_last_update_login IS NULL then ask user to set context
2935 IF ((l_last_updated_by IS NULL) OR (l_last_updated_by = -1)) OR (l_last_update_login IS NULL) THEN
2936 l_error_message := 'Application Security Context not set.Please set the context using fnd_global.apps_initialize() and try again.';
2937 RAISE_APPLICATION_ERROR(error_context_not_set, l_error_message, TRUE);
2938 END IF;
2939
2940 IF p_descriptive_flexfield_name LIKE '$SRS$.%' THEN
2941 l_error_message := 'This Descriptive Flexfield is meant for SRS parameters and hence this cannot be migrated to some other table';
2942 RAISE_APPLICATION_ERROR(error_srs_dff, l_error_message, TRUE);
2943 END IF;
2944 -- Get the old application_id
2945 get_application(p_application_short_name => p_application_short_name,
2946 x_application => l_application);
2947
2948 --Get the DFF and make sure that it exists.
2949 get_descriptive_flexfield(
2950 p_application_id => l_application.application_id,
2951 p_descriptive_flexfield_name => p_descriptive_flexfield_name,
2952 x_descriptive_flexfield => l_dff);
2953
2954 -- Get the old table_id
2955 get_table(p_application_id => l_dff.table_application_id,
2956 p_table_name => l_dff.application_table_name,
2957 x_table => l_old_table);
2958
2959
2960 -- Get the new table application
2961 get_application(p_application_short_name => p_new_table_appl_short_name,
2962 x_application => l_new_table_app);
2963
2964 -- Get the new table
2965 get_table(p_application_id => l_new_table_app.application_id,
2966 p_table_name => p_new_table_name,
2967 x_table => l_new_table);
2968
2969 -- Make sure the old and new table info are different
2970 IF l_new_table.application_id = l_old_table.application_id AND
2971 l_new_table.table_id = l_old_table.table_id THEN
2972 l_error_message := 'The old and new tables are same !!!';
2973 RAISE_APPLICATION_ERROR(error_same_table_name, l_error_message, TRUE);
2974 END IF;
2975
2976 FOR rec_columns_reg IN cur_columns_reg(l_dff,
2977 l_old_table,
2978 l_new_table)
2979 LOOP
2980 -- Make sure the column is not registered under any other FF
2981 IF rec_columns_reg.new_column_name IS NULL THEN
2982 l_error_message := 'The new table does not have the column ' || rec_columns_reg.old_column_name || ' as in the old one';
2983 RAISE_APPLICATION_ERROR(error_col_not_registered,l_error_message,TRUE);
2984 ELSIF rec_columns_reg.new_column_type <> rec_columns_reg.old_column_type THEN
2985 l_error_message := 'The column ' || rec_columns_reg.new_column_name || ' is of different type than in the old table';
2986 RAISE_APPLICATION_ERROR(error_col_wrong_type,l_error_message,TRUE);
2987 ELSIF rec_columns_reg.new_width < rec_columns_reg.old_width THEN
2988 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 || ')';
2989 RAISE_APPLICATION_ERROR(error_col_wrong_size,l_error_message,TRUE);
2990 ELSIF rec_columns_reg.new_flexfield_usage_code <> 'N' OR
2991 rec_columns_reg.new_flexfield_application_id IS NOT NULL OR
2992 rec_columns_reg.new_flexfield_name IS NOT NULL THEN
2993 l_error_message := 'The column ' || rec_columns_reg.new_column_name ||
2994 ' is already registered with the flexfield with name = ' || rec_columns_reg.old_flexfield_name ||
2995 ' and application_id = ' || rec_columns_reg.old_flexfield_application_id ||
2996 '.The flexfield_usage_code for this column is : ' || rec_columns_reg.new_flexfield_usage_code;
2997 RAISE_APPLICATION_ERROR(error_col_already_regis, l_error_message, TRUE);
2998 END IF;
2999 END LOOP;
3000
3001 UPDATE /* $Header: AFFFDAIB.pls 120.8.12010000.1 2008/07/25 14:13:39 appldev ship $ */
3002 fnd_descriptive_flexs
3003 SET application_table_name = p_new_table_name,
3004 table_application_id = l_new_table.application_id,
3005 last_update_date = l_last_update_date,
3006 last_updated_by = l_last_updated_by,
3007 last_update_login = l_last_update_login
3008 WHERE application_id = l_dff.application_id
3009 AND descriptive_flexfield_name = l_dff.descriptive_flexfield_name;
3010
3011 FOR rec_columns_reg IN cur_columns_reg(l_dff,
3012 l_old_table,
3013 l_new_table)
3014 LOOP
3015 -- Update for the column definition for the new table
3016 UPDATE fnd_columns
3017 SET flexfield_usage_code = rec_columns_reg.old_flexfield_usage_code,
3018 flexfield_application_id = l_dff.application_id,
3019 flexfield_name = l_dff.descriptive_flexfield_name,
3020 last_update_date = l_last_update_date,
3021 last_updated_by = l_last_updated_by,
3022 last_update_login = l_last_update_login
3023 WHERE application_id = l_new_table.application_id
3024 AND table_id = l_new_table.table_id
3025 AND column_name = rec_columns_reg.new_column_name;
3026
3027 -- Update for the column definition for old table
3028 UPDATE fnd_columns
3029 SET flexfield_usage_code = 'N',
3030 flexfield_application_id = NULL,
3031 flexfield_name = NULL,
3032 last_update_date = l_last_update_date,
3033 last_updated_by = l_last_updated_by,
3034 last_update_login = l_last_update_login
3035 WHERE application_id = l_old_table.application_id
3036 AND table_id = l_old_table.table_id
3037 AND column_name = rec_columns_reg.old_column_name;
3038 END LOOP;
3039
3040 EXCEPTION
3041 WHEN OTHERS THEN
3042 RAISE_APPLICATION_ERROR(error_others, 'FND_FLEX_DSC_API.MIGRATE_DFF raised exception: ' || sqlerrm || ' errcode : ' || sqlcode, TRUE);
3043 END migrate_dff;
3044
3045 END fnd_flex_dsc_api;
3046 /* end package */