1 PACKAGE fnd_flex_key_api AUTHID CURRENT_USER AS
2 /* $Header: AFFFKAIS.pls 120.2.12010000.1 2008/07/25 14:14:05 appldev ship $ */
3
4
5 TYPE awc_element_type IS RECORD
6 (tag varchar2(30),
7 clause varchar2(4000));
8
9 TYPE awc_elements_type IS TABLE OF awc_element_type INDEX BY BINARY_INTEGER;
10
11
12 -- Turn debug mode on. enables some extra output.
13 PROCEDURE debug_on ;
14
18 -- Turn validation on or off.
15 -- Turn debug mode off. disables some extra output.
16 PROCEDURE debug_off ;
17
19 -- @param v_in validation on or off.
20 PROCEDURE set_validation(v_in BOOLEAN);
21
22 -- Specify the session mode.
23 -- @param session_mode the session mode; either seed_data or
24 -- 'customer_data'
25 PROCEDURE set_session_mode(session_mode IN VARCHAR2);
26
27 -- Returns the RCS header information for the package.
28 FUNCTION version RETURN VARCHAR2;
29
30 -- Return the current error message string.
31 FUNCTION message RETURN VARCHAR2;
32
33 bad_parameter EXCEPTION;
34 PRAGMA EXCEPTION_INIT(bad_parameter, -06501);
35
36 TYPE flexfield_type IS RECORD
37 (instantiated VARCHAR2(1),
38 appl_short_name fnd_application.application_short_name%TYPE,
39 flex_code fnd_id_flexs.id_flex_code%TYPE,
40
41 flex_title fnd_id_flexs.id_flex_name%TYPE,
42 description fnd_id_flexs.description%TYPE,
43 table_appl_short_name fnd_application.application_short_name%TYPE,
44 table_name fnd_tables.table_name%TYPE,
45 concatenated_segs_view_name fnd_id_flexs.concatenated_segs_view_name%TYPE,
46 unique_id_column fnd_id_flexs.unique_id_column_name%TYPE,
47 structure_column fnd_id_flexs.set_defining_column_name%TYPE,
48 dynamic_inserts fnd_id_flexs.dynamic_inserts_feasible_flag%TYPE,
49 allow_id_value_sets fnd_id_flexs.allow_id_valuesets%TYPE,
50 index_flag fnd_id_flexs.index_flag%TYPE,
51 concat_seg_len_max fnd_id_flexs.maximum_concatenation_len%TYPE,
52 concat_len_warning fnd_id_flexs.concatenation_len_warning%TYPE,
53
54 application_id fnd_application.application_id%TYPE,
55 table_application_id fnd_application.application_id%TYPE,
56 table_id fnd_tables.table_id%TYPE);
57
58 -- We would normally have a reference to the flexfield here,
59 -- but pl/sql doesn't support it, so require the flexfield_type
60 -- to be passed all the time.
61 TYPE structure_type IS RECORD
62 (instantiated VARCHAR2(1),
63 structure_number fnd_id_flex_structures_vl.id_flex_num%TYPE,
64 structure_code fnd_id_flex_structures_vl.id_flex_structure_code%TYPE,
65 structure_name fnd_id_flex_structures_vl.id_flex_structure_name%TYPE,
66 description fnd_id_flex_structures_vl.description%TYPE,
67 view_name fnd_id_flex_structures_vl.structure_view_name%TYPE,
68 freeze_flag fnd_id_flex_structures_vl.freeze_flex_definition_flag%TYPE,
69 enabled_flag fnd_id_flex_structures_vl.enabled_flag%TYPE,
70 segment_separator fnd_id_flex_structures_vl.concatenated_segment_delimiter%TYPE,
71 cross_val_flag fnd_id_flex_structures_vl.cross_segment_validation_flag%TYPE,
72 freeze_rollup_flag fnd_id_flex_structures_vl.freeze_structured_hier_flag%TYPE,
73 dynamic_insert_flag fnd_id_flex_structures_vl.dynamic_inserts_allowed_flag%TYPE,
74 shorthand_enabled_flag fnd_id_flex_structures_vl.shorthand_enabled_flag%TYPE,
75 shorthand_prompt fnd_id_flex_structures_vl.shorthand_prompt%TYPE,
76 shorthand_length fnd_id_flex_structures_vl.shorthand_length%TYPE);
77
78 TYPE segment_type IS RECORD
79 (instantiated VARCHAR2(1),
80 segment_name fnd_id_flex_segments_vl.segment_name%TYPE,
81 description fnd_id_flex_segments_vl.description%TYPE,
82 column_name fnd_id_flex_segments_vl.application_column_name%TYPE,
83 segment_number fnd_id_flex_segments_vl.segment_num%TYPE,
84 enabled_flag fnd_id_flex_segments_vl.enabled_flag%TYPE,
85 displayed_flag fnd_id_flex_segments_vl.display_flag%TYPE,
86 indexed_flag fnd_id_flex_segments_vl.application_column_index_flag%TYPE,
87 value_set_id fnd_id_flex_segments_vl.flex_value_set_id%TYPE,
88 value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE,
89 default_type fnd_id_flex_segments_vl.default_type%TYPE,
90 default_value fnd_id_flex_segments_vl.default_value%TYPE,
91 runtime_property_function fnd_id_flex_segments_vl.runtime_property_function%TYPE,
92 additional_where_clause fnd_id_flex_segments_vl.additional_where_clause%TYPE,
93 required_flag fnd_id_flex_segments_vl.required_flag%TYPE,
94 security_flag fnd_id_flex_segments_vl.security_enabled_flag%TYPE,
95 range_code fnd_id_flex_segments_vl.range_code%TYPE,
96
97 display_size fnd_id_flex_segments_vl.display_size%TYPE,
98 description_size fnd_id_flex_segments_vl.maximum_description_len%TYPE,
99 concat_size fnd_id_flex_segments_vl.concatenation_description_len%TYPE,
100 lov_prompt fnd_id_flex_segments_vl.form_above_prompt%TYPE,
101 window_prompt fnd_id_flex_segments_vl.form_left_prompt%TYPE);
102
103 TYPE structure_list IS TABLE OF fnd_id_flex_structures.id_flex_num%TYPE
104 INDEX BY BINARY_INTEGER;
105
106 TYPE segment_list IS TABLE OF fnd_id_flex_segments.segment_name%TYPE
107 INDEX BY BINARY_INTEGER;
108
109
110 -- Last created (via find or new/add) versions
111 last_flexfield flexfield_type;
112 last_structure structure_type;
113 last_segment segment_type;
114
115
116 --
117 -- Check for existance by either set of criteria
118 -- (short name and code or short name and title)
119 -- @param appl_short_name the application short name of the flexfield
120 -- @param flex_code the flexfield code of the flexfield
121 -- @param flex_title the title of the flexfield
122 -- @return true if the flexfield exists in the database
123 FUNCTION flexfield_exists
124 (appl_short_name IN VARCHAR2,
125 flex_code IN VARCHAR2 DEFAULT NULL,
126 flex_title IN VARCHAR2 DEFAULT NULL)
127 RETURN BOOLEAN;
128
129
130 --
131 -- Create a new key flexfield
132 -- @return a handle to the flexfield
133 --
134 FUNCTION new_flexfield
135 (appl_short_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
136 flex_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
137 flex_title IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
138 description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
139 table_appl_short_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
140 table_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
141 unique_id_column IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
142 structure_column IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
143 dynamic_inserts IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
144 allow_id_value_sets IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
145 index_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
146 concat_seg_len_max IN NUMBER DEFAULT fnd_api.g_miss_num,
147 concat_len_warning IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
148 concatenated_segs_view_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char)
149 RETURN flexfield_type;
150
151
152 -- find a flexfield
153 -- @param appl_short_name the application short name of the flexfield
154 -- being searched for
155 -- @param flex_code the id_flex_code for the flexfield being searched for.
156 -- @return a handle to the fieldfield.
157 FUNCTION find_flexfield
158 (appl_short_name IN VARCHAR2,
159 flex_code IN VARCHAR2)
160 RETURN flexfield_type;
161
162
163 -- register a flexfield
164 -- @param flexfield the flexfield to register
165 -- @param enable_columns determies whether to enable the columns
166 -- that are normally enabled when a flexfield is registers
167 -- in the form.
168 PROCEDURE register
169 (flexfield IN OUT nocopy flexfield_type,
170 enable_columns IN VARCHAR2 DEFAULT 'Y');
171
172
173 -- delete a flexfield
174 -- @param flexfield the flexfield to delete
175 PROCEDURE delete_flexfield
176 (flexfield IN flexfield_type);
177
178 -- delete a flexfield
179 -- @param appl_short_name the application short name of the application
180 -- the flexfield belongs to.
181 -- @param flex_code the id flexc code for the flexfield.
182 PROCEDURE delete_flexfield
183 (appl_short_name IN VARCHAR2,
184 flex_code IN VARCHAR2);
185
186
187 --
188 -- enable (or disable) columns for the flexfield.
189 --
190 PROCEDURE enable_column
191 (flexfield IN flexfield_type,
192 column_name IN VARCHAR2,
193 enable_flag IN VARCHAR2 DEFAULT 'Y');
194
195 -- update fnd_columns
196 --
197 -- enable (or disable) a batch of columns at the same time.
198 -- @param pattern the pattern to match for column name. uses
199 -- the sql LIKE match.
200 -- @param enable_fleg whetther we are enabling or disabling. Y/N
201 PROCEDURE enable_columns_like
202 (flexfield IN flexfield_type,
203 pattern IN VARCHAR2,
204 enable_flag IN VARCHAR2 DEFAULT 'Y');
205
206
207 --
208 -- create a new flexfield qualifier
209 --
210 PROCEDURE add_flex_qualifier
211 (flexfield IN flexfield_type,
212 qualifier_name IN VARCHAR2,
213 prompt IN VARCHAR2,
214 description IN VARCHAR2,
215 global_flag IN VARCHAR2 DEFAULT 'N',
216 required_flag IN VARCHAR2 DEFAULT 'N',
217 unique_flag IN VARCHAR2 DEFAULT 'N');
218
219
220 --
221 -- delete flexfield qualifier.
222 -- If recursive_delete is TRUE then all flexfield qualifier
223 -- related data will also be deleted. (Segment qualifiers, and
224 -- associations between segments and flexfield qualifier.)
225 -- if recursive_delete is FALSE but there is related data
226 -- then this function will not delete the flexfield qualifier.
227 -- Returns -1 in case of error,
228 -- 0 if nothing to delete,
229 -- # number of deletes for successful operation.
230 --
231 FUNCTION delete_flex_qualifier
232 (flexfield IN flexfield_type,
233 qualifier_name IN VARCHAR2,
234 recursive_delete IN BOOLEAN DEFAULT TRUE)
235 RETURN NUMBER;
236
237
238 --
239 -- fill in cross product table between flexfield qualifier and
240 -- segments.
241 -- This is mostly used when a qualifier is created in seed database.
242 -- In this case upgrading customers will not get fnd_segment_attribute_values
243 -- table populated properly. So this function should be called in
244 -- post-DataMerge phase.
245 --
246 -- Returns -1 in case of error,
247 -- 0 if nothing to assign
248 -- # number of assigns for successful operation.
249 --
250 FUNCTION fill_segment_attribute_values
251 RETURN NUMBER;
252
253
254 --
255 -- create a new segment qualifier
256 --
257 PROCEDURE add_seg_qualifier
258 (flexfield IN flexfield_type,
259 flex_qualifier IN VARCHAR2,
260
261 qualifier_name IN VARCHAR2,
262 prompt IN VARCHAR2,
263 description IN VARCHAR2,
264 derived_column IN VARCHAR2,
265 quickcode_type IN VARCHAR2,
266 default_value IN VARCHAR2);
267
268
269 --
270 -- delete segment qualifier.
271 -- Warning : Flex team do not suggest deleting a segment qualifier.
272 -- If some values are created with segment qualifier values,
273 -- then you may get inconcistent behavior for those values.
274 -- Since segment qualifier values are parsed according to their assignment
275 -- dates, deleting them may cause inconsistent data.
276 --
277 -- Returns -1 in case of error,
278 -- 0 if nothing to delete,
279 -- # number of deletes for successful operation.
280 --
281 FUNCTION delete_seg_qualifier
282 (flexfield IN flexfield_type,
283 flex_qualifier IN VARCHAR2,
284 qualifier_name IN VARCHAR2) RETURN NUMBER;
285
286
287 -- create a new flexfield structure.
288 FUNCTION new_structure
289 (flexfield IN flexfield_type,
290
291 structure_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
292 structure_title IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
293 description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
294 view_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
295 freeze_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
296 enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
297 segment_separator IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
298 cross_val_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
299 freeze_rollup_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
300 dynamic_insert_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
301 shorthand_enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
302 shorthand_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
303 shorthand_length IN NUMBER DEFAULT fnd_api.g_miss_num)
304 RETURN structure_type;
305
306
307 -- find a flexfield structure
308 -- @param flexfield the flexfield the structure belongs to
309 -- @param structure_code the code of the structure
310 FUNCTION find_structure
311 (flexfield IN flexfield_type,
312 structure_code IN VARCHAR2)
313 RETURN structure_type;
314
315
316 -- locate a structure by its structure number
317 -- @param flexfield the flexfield the structure belongs to
318 -- @param structure_number the structure number being searched for
319 -- @return the structure handle
320 -- @see find_structure
321 FUNCTION find_structure
322 (flexfield IN flexfield_type,
323 structure_number IN NUMBER)
324 RETURN structure_type;
325
326
327 --
328 -- add a structure to a flexfield
329 --
330 PROCEDURE add_structure
331 (flexfield IN flexfield_type DEFAULT last_flexfield,
332 structure IN OUT nocopy structure_type);
333
334
335 -- delete a structure
336 PROCEDURE delete_structure
337 (flexfield IN flexfield_type,
338 structure IN structure_type);
339
340
341 -- create a new segment
342 -- @return the segment handle
343 FUNCTION new_segment
344 (flexfield IN flexfield_type,
345 structure IN structure_type,
346
347 segment_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
348 description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
349 column_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
350 segment_number IN NUMBER DEFAULT fnd_api.g_miss_num,
351 enabled_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
352 displayed_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
353 indexed_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
354
355 /* validation */
356 value_set IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
357 default_type IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
358 default_value IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
359 required_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
360 security_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
361 range_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
362
363 /* sizes */
364 display_size IN NUMBER DEFAULT fnd_api.g_miss_num,
365 description_size IN NUMBER DEFAULT fnd_api.g_miss_num,
366 concat_size IN NUMBER DEFAULT fnd_api.g_miss_num,
367
368 /* prompts */
369 lov_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
370 window_prompt IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
371 runtime_property_function IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
372 additional_where_clause IN VARCHAR2 DEFAULT fnd_api.g_miss_char)
373 RETURN segment_type;
374
375
376 -- locate a segment by its name.
377 -- @param flexfield the flexfield
378 -- @param structure the structure
379 -- @param segment_name the segment name
380 -- @return the segment handle
381 FUNCTION find_segment
382 (flexfield IN flexfield_type,
383 structure IN structure_type,
384 segment_name IN VARCHAR2)
385 RETURN segment_type;
386
387
388 --
389 -- add a segment to a structure
390 --
391 PROCEDURE add_segment
392 (flexfield IN flexfield_type,
393 structure IN structure_type,
394 segment IN OUT nocopy segment_type);
395 -- insert into fnd_id_flex_segments
396 -- insert into fnd_segment_attribute_values
397 -- insert into fnd_flex_validation_qualifiers
398
399
400 -- delete a segment
401 -- @param flexfield the flexfield
402 -- @param structure the structure
403 -- @param segment the segment
404 PROCEDURE delete_segment
405 (flexfield IN flexfield_type,
406 structure IN structure_type,
407 segment IN segment_type);
408
409
410 -- assign a qualifier.
411 -- qualifiers are automatiicaly assigned as disabled when the segment
412 -- is created.
413 -- @param flexfield the flexfield
414 -- @param structure the structure
415 -- @param segment the segment
416 -- @param flexfield_qualifier the flexfield qualifier
417 -- @param enable_flag enable if 'Y', disable if 'N'
418 PROCEDURE assign_qualifier
419 (flexfield IN flexfield_type,
420 structure IN structure_type,
421 segment IN segment_type,
422 flexfield_qualifier IN VARCHAR2,
423 enable_flag IN VARCHAR2 DEFAULT 'Y');
424 -- update fnd_segment_attribute_values
425
426
427 -- Modify the specified flexfield based on the values specified in
428 -- the new flexfield.
429 -- @param original the flexfield to be modified
430 -- @param modified the new flexfield information to be changed to
431 PROCEDURE modify_flexfield
432 (original IN flexfield_type,
433 modified IN flexfield_type);
434
435
436 -- Modify the specified flexfield structure based on the values specified in
437 -- the new structure.
438 -- @param flexfield the flexfield
439 -- @param original the structure to be modified
440 -- @param modified the new structure information to be changed to
441 PROCEDURE modify_structure
442 (flexfield IN flexfield_type,
443 original IN structure_type,
444 modified IN structure_type);
445
446
447 -- Modify the specified segment based on the values specified in
448 -- the new segment.
449 -- @param flexfield the flexfield
450 -- @param structure the structure
451 -- @param original the segment to be modified
452 -- @param modified the new segment information to be changed to
453 PROCEDURE modify_segment
454 (flexfield IN flexfield_type,
455 structure IN structure_type,
456 original IN segment_type,
457 modified IN segment_type);
458
459
460 -- a test function. not maintained.
461 PROCEDURE test(name IN VARCHAR2);
462
463
464 -- Print out a program that will create this flexfield.
465 -- @param flexfield the flexfield to print out
466 -- @param recurse also create all dependent
467 -- structures, segments and qualifiers.
468 PROCEDURE dump_flexfield(flexfield IN flexfield_type,
469 recurse IN BOOLEAN DEFAULT TRUE);
470
471
472 PROCEDURE dump_all_flex_qualifiers(flexfield IN flexfield_type,
473 recurse IN BOOLEAN DEFAULT TRUE);
474
475 PROCEDURE dump_all_seg_qualifiers(flexfield IN flexfield_type,
476 flex_qualifier IN VARCHAR2);
477
478
479 PROCEDURE dump_structure(flexfield IN flexfield_type,
480 structure IN structure_type,
481 recurse IN BOOLEAN DEFAULT TRUE);
482
483
484 PROCEDURE dump_all_structures(flexfield IN flexfield_type,
485 recurse IN BOOLEAN DEFAULT TRUE);
486
487
488 PROCEDURE dump_segment(flexfield IN flexfield_type,
489 structure IN structure_type,
490 segment IN segment_type);
491
492 PROCEDURE dump_all_segments(flexfield IN flexfield_type,
493 structure IN structure_type);
494
495
496 --
497 -- Return a list of structures for a flexfield.
498 -- @param flexfield the flexfield
499 -- @param enabled_only only return enabled segments if true
500 -- @param nsegments the number of segments returned
501 -- @param nsegments the segment names
502 PROCEDURE get_structures
503 (flexfield IN flexfield_type,
504 enabled_only IN BOOLEAN DEFAULT TRUE,
505 nstructures OUT nocopy NUMBER,
506 structures OUT nocopy structure_list);
507
508 --
509 -- Return a list of the segments for a flexfield structure.
510 -- @param flexfield the flexfield
511 -- @param structure the structure to list segments for
512 -- @param enabled_only only return enabled segments if true
513 -- @param nsegments the number of segments returned
514 -- @param nsegments the segment names
515 PROCEDURE get_segments
516 (flexfield IN flexfield_type,
517 structure IN structure_type,
518 enabled_only IN BOOLEAN DEFAULT TRUE,
519 nsegments OUT nocopy NUMBER,
520 segments OUT nocopy segment_list);
521
522 FUNCTION is_table_used(p_application_id IN fnd_tables.application_id%TYPE,
523 p_table_name IN fnd_tables.table_name%TYPE,
524 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN;
525
526 FUNCTION is_column_used(p_application_id IN fnd_tables.application_id%TYPE,
527 p_table_name IN fnd_tables.table_name%TYPE,
528 p_column_name IN fnd_columns.column_name%TYPE,
529 x_message OUT nocopy VARCHAR2) RETURN BOOLEAN;
530
531 --
532 -- Get the segment display order given the qualifier name.
533 --
534 FUNCTION get_seg_order_by_qual_name(p_application_id IN NUMBER,
535 p_id_flex_code IN VARCHAR2,
536 p_id_flex_num IN NUMBER,
537 p_segment_attribute_type IN VARCHAR2,
538 x_segment_order OUT nocopy NUMBER)
539 RETURN BOOLEAN;
540
541 PROCEDURE get_awc_elements
542 (p_flexfield IN flexfield_type,
543 p_structure IN structure_type,
544 p_segment IN segment_type,
545 x_numof_awc_elements OUT nocopy number,
546 x_awc_elements OUT nocopy awc_elements_type);
547
548 PROCEDURE add_awc(p_flexfield IN flexfield_type,
549 p_structure IN structure_type,
550 p_segment IN segment_type,
551 p_tag IN varchar2,
552 p_clause IN varchar2);
553
554 PROCEDURE delete_awc(p_flexfield IN flexfield_type,
555 p_structure IN structure_type,
556 p_segment IN segment_type,
557 p_tag IN varchar2);
558
559 FUNCTION awc_exists(p_flexfield IN flexfield_type,
560 p_structure IN structure_type,
561 p_segment IN segment_type,
562 p_tag IN varchar2)
563 RETURN BOOLEAN;
564
565 END fnd_flex_key_api;
[Home] [Help]