1 PACKAGE BODY fnd_flex_ext AS
2 /* $Header: AFFFEXTB.pls 120.4.12010000.1 2008/07/25 14:13:58 appldev ship $ */
3
4
5 -- PRIVATE CONSTANTS
6 --
7
8 AOL_DATE_FMT CONSTANT VARCHAR2(21) := 'YYYY/MM/DD HH24:MI:SS';
9 OLD_DATE_FMT CONSTANT VARCHAR2(11) := 'DD-MON-YYYY';
10 OLD_DATE_LEN CONSTANT NUMBER := 11;
11
12 -- PRIVATE FUNCTIONS
13 --
14 FUNCTION read_displayedsegs(fstruct IN FND_FLEX_SERVER1.FlexStructId,
15 disp_segs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs)
16 RETURN BOOLEAN;
17
18 FUNCTION is_allow_id_valuesets(i_application_short_name IN VARCHAR2,
19 i_id_flex_code IN VARCHAR2,
20 o_allow_id_value_sets OUT nocopy BOOLEAN)
21 RETURN BOOLEAN;
22
23 FUNCTION convert_vdate(date_string IN VARCHAR2,
24 date_value OUT nocopy DATE) RETURN BOOLEAN;
25
26 FUNCTION concat_segs(n_segs IN NUMBER,
27 segment_array IN SegmentArray,
28 delimiter IN VARCHAR2,
29 cat_segs OUT nocopy VARCHAR2) RETURN BOOLEAN;
30
31 FUNCTION output_string(s IN VARCHAR2,
32 s_out OUT nocopy VARCHAR2) RETURN BOOLEAN;
33
34 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
35 key_flex_code IN VARCHAR2,
36 structure_number IN NUMBER,
37 validation_date IN DATE,
38 concat_segments IN VARCHAR2,
39 combination_id OUT nocopy NUMBER,
40 data_set IN NUMBER DEFAULT -1)
41 RETURN BOOLEAN;
42
43 FUNCTION get_segments(application_short_name IN VARCHAR2,
44 key_flex_code IN VARCHAR2,
45 structure_number IN NUMBER,
46 combination_id IN NUMBER,
47 concat_segment_values OUT nocopy VARCHAR2) RETURN BOOLEAN;
48
49 FUNCTION to_segmentarray(catsegs IN VARCHAR2,
50 sepchar IN VARCHAR2,
51 segs OUT nocopy SegmentArray) RETURN NUMBER;
52
53 FUNCTION from_segmentarray(nsegs IN NUMBER,
54 segs IN SegmentArray,
55 sepchar IN VARCHAR2) RETURN VARCHAR2;
56
57 -- PRIVATE GLOBAL VARIABLES
58 --
59 chr_newline VARCHAR2(8); -- := fnd_global.newline;
60
61 ext_globals_valid BOOLEAN := FALSE;
62 nvalidated NUMBER;
63 value_dvals FND_FLEX_SERVER1.ValueArray;
64 value_vals FND_FLEX_SERVER1.ValueArray;
65 value_ids FND_FLEX_SERVER1.ValueIdArray;
66 value_descs FND_FLEX_SERVER1.ValueDescArray;
67 value_desclens FND_FLEX_SERVER1.NumberArray;
68 cc_cols FND_FLEX_SERVER1.TabColArray;
69 cc_coltypes FND_FLEX_SERVER1.CharArray;
70 segtypes FND_FLEX_SERVER1.SegFormats;
71 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
72 derv FND_FLEX_SERVER1.DerivedVals;
73 tbl_derv FND_FLEX_SERVER1.DerivedVals;
74 drv_quals FND_FLEX_SERVER1.Qualifiers;
75 tbl_quals FND_FLEX_SERVER1.Qualifiers;
76 n_xcol_vals NUMBER;
77 xcol_vals FND_FLEX_SERVER1.StringArray;
78 new_comb BOOLEAN;
79 segment_codes VARCHAR2(30);
80 valid_stat NUMBER;
81 ccid_o NUMBER;
82 delim VARCHAR2(1);
83 err_segn NUMBER;
84 segcodes VARCHAR2(30);
85 FLEX_DELIMITER_ESCAPE CONSTANT VARCHAR2(1) := '\';
86
87 -- -----------------------------------------------------------------------
88 -- MESSAGING:
89 -- -----------------------------------------------------------------------
90 g_is_message_get BOOLEAN := FALSE;
91 g_is_failed BOOLEAN := FALSE;
92 g_encoded_message VARCHAR2(2000) := '';
93 g_message VARCHAR2(2000) := '';
94
95 -- ==================================================
96 -- CACHING
97 -- ==================================================
98 g_cache_return_code VARCHAR2(30);
99 g_cache_key VARCHAR2(2000);
100 g_cache_value fnd_plsql_cache.generic_cache_value_type;
101
102 -- ======================================================================
103 -- EXT : get_delimiter cache
104 -- ======================================================================
105 gdl_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
106 gdl_cache_storage fnd_plsql_cache.generic_cache_values_type;
107
108 -- ======================================================================
109 -- EXT ccid cache : IDC
110 --
111 -- Primary Key For IDC
112 -- <application_short_name> || NEWLINE || <id_flex_code> || NEWLINE ||
113 -- <id_flex_num> || NEWLINE || <ccid>
114 --
115 -- ======================================================================
116 idc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
117 idc_cache_storage fnd_plsql_cache.generic_cache_values_type;
118
119 FUNCTION check_idc(p_appl_short_name IN VARCHAR2,
120 p_id_flex_code IN VARCHAR2,
121 p_id_flex_num IN NUMBER,
122 p_ccid IN NUMBER,
123 x_delimiter OUT nocopy VARCHAR2,
124 x_newline_comb OUT nocopy VARCHAR2)
125 RETURN VARCHAR2
126 IS
127 BEGIN
128 g_cache_key := (p_appl_short_name || '.' ||
129 p_id_flex_code || '.' ||
130 p_id_flex_num || '.' ||
131 p_ccid);
132
133 fnd_plsql_cache.generic_1to1_get_value(idc_cache_controller,
134 idc_cache_storage,
135 g_cache_key,
136 g_cache_value,
137 g_cache_return_code);
138
139 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
140 IF (g_cache_value.varchar2_1 IS NULL) THEN
141 --
142 -- No error message.
143 --
144 x_delimiter := g_cache_value.varchar2_2;
145 x_newline_comb := g_cache_value.varchar2_3;
146 RETURN(fnd_plsql_cache.CACHE_VALID);
147 ELSE
148 --
149 -- Error message.
150 --
151 fnd_message.set_encoded(g_cache_value.varchar2_1);
152 RETURN(fnd_plsql_cache.CACHE_INVALID);
153 END IF;
154 END IF;
155 RETURN(g_cache_return_code);
156 EXCEPTION
157 WHEN OTHERS THEN
158 RETURN(fnd_plsql_cache.CACHE_NOTFOUND);
159 END check_idc;
160
161 PROCEDURE update_idc(p_appl_short_name IN VARCHAR2,
162 p_id_flex_code IN VARCHAR2,
163 p_id_flex_num IN NUMBER,
164 p_ccid IN NUMBER,
165 p_delimiter IN VARCHAR2,
166 p_newline_comb IN VARCHAR2,
167 p_is_valid IN BOOLEAN)
168 IS
169 l_enc_err_msg VARCHAR2(2000) := NULL;
170 BEGIN
171 g_cache_key := (p_appl_short_name || '.' ||
172 p_id_flex_code || '.' ||
173 p_id_flex_num || '.' ||
174 p_ccid);
175
176 IF (NOT p_is_valid) THEN
177 l_enc_err_msg := fnd_message.get_encoded;
178 fnd_message.set_encoded(l_enc_err_msg);
179 END IF;
180
181 fnd_plsql_cache.generic_cache_new_value
182 (x_value => g_cache_value,
183 p_varchar2_1 => l_enc_err_msg,
184 p_varchar2_2 => p_delimiter,
185 p_varchar2_3 => p_newline_comb);
186
187 fnd_plsql_cache.generic_1to1_put_value(idc_cache_controller,
188 idc_cache_storage,
189 g_cache_key,
190 g_cache_value);
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 RETURN;
195 END update_idc;
196
197 PROCEDURE clear_ccid_cache
198 IS
199 BEGIN
200 fnd_plsql_cache.generic_1to1_clear(idc_cache_controller,
201 idc_cache_storage);
202 END clear_ccid_cache;
203
204 -- -----------------------------------------------------------------------
205 -- MESSAGING:
206 -- -----------------------------------------------------------------------
207
208 PROCEDURE init_message
209 IS
210 BEGIN
211 g_is_message_get := FALSE;
212 g_is_failed := FALSE;
213 g_encoded_message := '';
214 g_message := '';
215 END init_message;
216
217 PROCEDURE get_from_fnd_message
218 IS
219 BEGIN
220 IF (NOT g_is_message_get) THEN
221 --
222 -- fnd_message.get_* removes message from stack.
223 -- put it back.
224 --
225 g_encoded_message := fnd_message.get_encoded;
226 fnd_message.set_encoded(g_encoded_message);
227
228 g_message := fnd_message.get;
229 fnd_message.set_encoded(g_encoded_message);
230
231 g_is_message_get := TRUE;
232 END IF;
233 EXCEPTION
234 WHEN OTHERS THEN
235 NULL;
236 END get_from_fnd_message;
237
238 FUNCTION get_message RETURN VARCHAR2
239 IS
240 BEGIN
241 IF (g_is_failed) THEN
242 get_from_fnd_message;
243 RETURN g_message;
244 END IF;
245 RETURN('');
246 EXCEPTION
247 WHEN OTHERS THEN
248 NULL;
249 END get_message;
250
251 FUNCTION get_encoded_message RETURN VARCHAR2
252 IS
253 BEGIN
254 IF (g_is_failed) THEN
255 get_from_fnd_message;
256 RETURN g_encoded_message;
257 END IF;
258 RETURN('');
259 EXCEPTION
260 WHEN OTHERS THEN
261 NULL;
262 END get_encoded_message;
263
264 PROCEDURE set_failed
265 IS
266 BEGIN
267 g_is_failed := TRUE;
268 EXCEPTION
269 WHEN OTHERS THEN
270 NULL;
271 END set_failed;
272
273
274 /* ----------------------------------------------------------------------- */
275 /* Public Functions */
276 /* ----------------------------------------------------------------------- */
277
278
279 /* ----------------------------------------------------------------------- */
280 /* Concatenates segments from segment array to a string. */
281 /* Raises unhandled exception if any errors. */
282 /* ----------------------------------------------------------------------- */
283
284 FUNCTION concatenate_segments(n_segments IN NUMBER,
285 segments IN SegmentArray,
286 delimiter IN VARCHAR2) RETURN VARCHAR2
287 IS
288 catsegs VARCHAR2(2000);
289 BEGIN
290 init_message;
291 IF (concat_segs(n_segments, segments, delimiter, catsegs)) then
292 return(catsegs);
293 ELSE
294 set_failed;
295 FND_MESSAGE.raise_error;
296 end if;
297 EXCEPTION
298 WHEN OTHERS THEN
299 set_failed;
300 RAISE;
301 END concatenate_segments;
302
303 /* ----------------------------------------------------------------------- */
304 /* Breaks up concatenated segments into segment array. */
305 /* Returns number of segments found. */
306 /* Truncates segments longer than MAX_SEG_SIZE bytes. */
307 /* Raises unhandled exception if any errors. */
308 /* ----------------------------------------------------------------------- */
309 FUNCTION breakup_segments(concatenated_segs IN VARCHAR2,
310 delimiter IN VARCHAR2,
311 segments OUT nocopy SegmentArray)
312 RETURN NUMBER IS
313 n_segments NUMBER;
314 BEGIN
315 init_message;
316
317 n_segments := to_segmentarray(concatenated_segs, delimiter, segments);
318 return(n_segments);
319 EXCEPTION
320 WHEN OTHERS THEN
321 set_failed;
322 RAISE;
323 END breakup_segments;
324
325 /* ------------------------------------------------------------------------ */
326 /* Gets the character used as the segment delimiter for the */
327 /* specified flexfield structure. */
328 /* Returns NULL and sets error on the server if structure not found. */
329 /* ------------------------------------------------------------------------ */
330 FUNCTION get_delimiter(application_short_name IN VARCHAR2,
331 key_flex_code IN VARCHAR2,
332 structure_number IN NUMBER)
333 RETURN VARCHAR2
334 IS
335 delim VARCHAR2(1);
336 BEGIN
337 init_message;
338
339 g_cache_key := (application_short_name || '.' || key_flex_code || '.' ||
340 structure_number);
341 fnd_plsql_cache.generic_1to1_get_value(gdl_cache_controller,
342 gdl_cache_storage,
343 g_cache_key,
344 g_cache_value,
345 g_cache_return_code);
346 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
347 delim := g_cache_value.varchar2_1;
348 ELSE
349 SELECT s.concatenated_segment_delimiter
350 INTO delim
351 FROM fnd_id_flex_structures s, fnd_application a
355 AND a.application_short_name = get_delimiter.application_short_name;
352 WHERE s.application_id = a.application_id
353 AND s.id_flex_code = key_flex_code
354 AND s.id_flex_num = structure_number
356
357 g_cache_value.varchar2_1 := delim;
358 fnd_plsql_cache.generic_1to1_put_value(gdl_cache_controller,
359 gdl_cache_storage,
360 g_cache_key,
361 g_cache_value);
362 END IF;
363 return(delim);
364 --
365 -- Fixed bug751140. table column name and argument name are same.
366 -- (application_short_name) (We should use p_* style, but it is late.)
367 --
368 EXCEPTION
369 WHEN NO_DATA_FOUND then
370 FND_MESSAGE.set_name('FND', 'FLEX-CANNOT FIND STRUCT DEF');
371 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.GET_DELIMITER');
372 FND_MESSAGE.set_token('APPL', application_short_name);
373 FND_MESSAGE.set_token('CODE', key_flex_code);
374 FND_MESSAGE.set_token('NUM', to_char(structure_number));
375 set_failed;
376 return(NULL);
377 WHEN TOO_MANY_ROWS then
378 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE STRUCT DEF');
379 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.GET_DELIMITER');
380 FND_MESSAGE.set_token('APPL', application_short_name);
381 FND_MESSAGE.set_token('CODE', key_flex_code);
382 FND_MESSAGE.set_token('NUM', to_char(structure_number));
383 set_failed;
384 return(NULL);
385 WHEN OTHERS then
386 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
387 FND_MESSAGE.set_token('MSG', 'EXT.get_delimiter() exception: ' || SQLERRM);
388 set_failed;
389 return(NULL);
390 END get_delimiter;
391
392 /* ------------------------------------------------------------------------ */
393 /* NOTE: This function provided primarily for interfacing to */
394 /* forms 4.5 client which cannot pass arrays or call server functions */
395 /* that have variable numbers of arguments. Please call the */
396 /* get_combination_id() function if calling from the server. */
397 /* */
398 /* Finds combination_id for given concatenated segment values. */
399 /* Pass in validation date in AOL_DATE_FMT format. */
400 /* Combination is automatically created if it does not already exist. */
401 /* Commit the transaction soon after calling this function since */
402 /* if a combination is created it will prevent other users creating */
403 /* similar combinations on any flexfield until a commit is issued. */
404 /* Returns positive combination_id or 0 and sets error if invalid. */
405 /* ------------------------------------------------------------------------ */
406 FUNCTION get_ccid(application_short_name IN VARCHAR2,
407 key_flex_code IN VARCHAR2,
408 structure_number IN NUMBER,
409 validation_date IN VARCHAR2,
410 concatenated_segments IN VARCHAR2) RETURN NUMBER IS
411
412 v_date DATE;
413
414 BEGIN
415 init_message;
416 if(convert_vdate(validation_date, v_date) and
417 get_combination_id(application_short_name, key_flex_code,
418 structure_number, v_date, concatenated_segments, ccid_o)) then
419 return(ccid_o);
420 end if;
421 set_failed;
422 return(0);
423
424 EXCEPTION
425 WHEN OTHERS then
426 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
427 FND_MESSAGE.set_token('MSG', 'get_ccid() exception: ' || SQLERRM);
428 set_failed;
429 return(0);
430
431 END get_ccid;
432
433 /* ------------------------------------------------------------------------ */
434 /* Finds combination_id for given segment values. */
435 /* If validation date is NULL checks all cross-validation rules. */
436 /* Returns TRUE if combination valid, or FALSE and sets error message */
437 /* on server using FND_MESSAGE if invalid. */
438 /* ------------------------------------------------------------------------ */
439 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
440 key_flex_code IN VARCHAR2,
441 structure_number IN NUMBER,
442 validation_date IN DATE,
443 concat_segments IN VARCHAR2,
444 combination_id OUT nocopy NUMBER,
445 data_set IN NUMBER DEFAULT -1)
446 RETURN BOOLEAN IS
447 BEGIN
448
449 -- Initialize messages, debugging, and number of sql strings
450 --
451 init_message;
452 ext_globals_valid := FALSE;
453 if(FND_FLEX_SERVER1.init_globals = FALSE) THEN
454 set_failed;
455 return(FALSE);
456 end if;
457
458 FND_FLEX_SERVER.validation_engine(FND_GLOBAL.RESP_APPL_ID,
459 FND_GLOBAL.RESP_ID, FND_GLOBAL.USER_ID,
463 NULL, NULL, nvalidated, value_dvals, value_vals, value_ids,
460 application_short_name, key_flex_code, NULL, structure_number,
461 validation_date, NULL, data_set, 'V', 'FULL', 'Y', 'Y',
462 'N', 'N', 'ALL', concat_segments, 'V', NULL, NULL, NULL,
464 value_descs, value_desclens, cc_cols, cc_coltypes, segtypes,
465 disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
466 n_xcol_vals, xcol_vals, delim, ccid_o, new_comb, valid_stat,
467 segcodes, err_segn);
468 if(valid_stat = FND_FLEX_SERVER1.VV_VALID) then
469 combination_id := ccid_o;
470 ext_globals_valid := TRUE;
471 return(TRUE);
472 end if;
473 set_failed;
474 return(FALSE);
475
476 EXCEPTION
477 WHEN OTHERS then
478 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
479 FND_MESSAGE.set_token('MSG','get_combination_id() exception: '||SQLERRM);
480 set_failed;
481 return(FALSE);
482
483 END get_combination_id;
484
485 /* ------------------------------------------------------------------------ */
486 /* Overloaded version of above for use with individual segments. */
487 /* ------------------------------------------------------------------------ */
488
489 FUNCTION get_combination_id(application_short_name IN VARCHAR2,
490 key_flex_code IN VARCHAR2,
491 structure_number IN NUMBER,
492 validation_date IN DATE,
493 n_segments IN NUMBER,
494 segments IN SegmentArray,
495 combination_id OUT nocopy NUMBER,
496 data_set IN NUMBER DEFAULT -1)
497 RETURN BOOLEAN IS
498 sepchar VARCHAR2(1);
499 catsegs VARCHAR2(2000);
500
501 BEGIN
502
503 -- Concatenate the input segments, then send them to the other function.
504 --
505 init_message;
506 sepchar := get_delimiter(application_short_name, key_flex_code,
507 structure_number);
508 if((sepchar is not null) and
509 (concat_segs(n_segments, segments, sepchar, catsegs) = TRUE)) then
510 return(get_combination_id(application_short_name, key_flex_code,
511 structure_number, validation_date, catsegs,
512 combination_id, data_set));
513 end if;
514 set_failed;
515 return(FALSE);
516
517 EXCEPTION
518 WHEN OTHERS then
519 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
520 FND_MESSAGE.set_token('MSG','get_combination_id() exception: '||SQLERRM);
521 set_failed;
522 return(FALSE);
523
524 END get_combination_id;
525
526 /* ------------------------------------------------------------------------ */
527 /* NOTE: This function provided primarily for interfacing to */
528 /* forms 4.5 client which cannot pass arrays or call server functions */
529 /* that have variable numbers of arguments. Please call the */
530 /* get_segments() function if calling from the server. */
531 /* */
532 /* Returns concatenated segment values string for the given */
533 /* combination id in the specified flexfield. */
534 /* Caller must provide VARCHAR2(2000) storage for the returned string. */
535 /* Returns NULL and sets error on the server if combination not found. */
536 /* ------------------------------------------------------------------------ */
537 FUNCTION get_segs(application_short_name IN VARCHAR2,
538 key_flex_code IN VARCHAR2,
539 structure_number IN NUMBER,
540 combination_id IN NUMBER) RETURN VARCHAR2 IS
541
542 cat_vals VARCHAR2(2000);
543
544 BEGIN
545 if(get_segments(application_short_name, key_flex_code,
546 structure_number, combination_id, cat_vals)) then
547 return(cat_vals);
548 end if;
549 return(NULL);
550
551 -- Do not handle any exceptions here so that if user does not leave enough
552 -- room for the returned string, it will cause an exception in the user's
553 -- calling program rather than in here.
554 END get_segs;
555
556 /* ------------------------------------------------------------------------ */
557 /* Returns segment values for the given combination id in the */
558 /* specified flexfield. Returns TRUE if combination found, otherwise */
559 /* returns FALSE and sets error using FND_MESSAGE on the server. */
560 /* Does not check value security rules. */
561 /* Concatenated segment string is NULL if error. */
562 /* ------------------------------------------------------------------------ */
563
564 FUNCTION get_segments(application_short_name IN VARCHAR2,
565 key_flex_code IN VARCHAR2,
566 structure_number IN NUMBER,
567 combination_id IN NUMBER,
568 concat_segment_values OUT nocopy VARCHAR2)
572 catvals_out VARCHAR2(2000);
569 RETURN BOOLEAN IS
570 n_segs_out NUMBER;
571 segs_out SegmentArray;
573
574 BEGIN
575 init_message;
576 -- Call version that returns segments in array, then concatenate them.
577 --
578 if(get_segments(application_short_name, key_flex_code, structure_number,
579 combination_id, n_segs_out, segs_out) and
580 concat_segs(n_segs_out, segs_out, delim, catvals_out)) then
581 return(output_string(catvals_out, concat_segment_values));
582 end if;
583 set_failed;
584 return(FALSE);
585
586 EXCEPTION
587 WHEN OTHERS then
588 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
589 FND_MESSAGE.set_token('MSG', 'get_segments() exception: ' || SQLERRM);
590 set_failed;
591 return(FALSE);
592
593 END get_segments;
594
595 /* ------------------------------------------------------------------------ */
596 /* Returns segment values for the given combination id in the */
597 /* specified flexfield. Returns TRUE if combination found, otherwise */
598 /* returns FALSE and sets error using FND_MESSAGE on the server. */
599 /* n_segments is 0 and no elements of segments array are assigned if */
600 /* combination not found or on error. */
601 /* Does not check value security rules. */
602 /* ------------------------------------------------------------------------ */
603
604 FUNCTION get_segments(application_short_name IN VARCHAR2,
605 key_flex_code IN VARCHAR2,
606 structure_number IN NUMBER,
607 combination_id IN NUMBER,
608 n_segments OUT nocopy NUMBER,
609 segments OUT nocopy SegmentArray,
610 data_set IN NUMBER DEFAULT -1)
611 RETURN BOOLEAN IS
612 n_dispsegs NUMBER;
613
614 kff_id FND_FLEX_SERVER1.FlexStructId;
615 kff_info FND_FLEX_SERVER1.FlexStructInfo;
616 kff_cc FND_FLEX_SERVER1.CombTblInfo;
617 tmp_qualcols FND_FLEX_SERVER1.TabColArray;
618 tmp_xcolnames FND_FLEX_SERVER1.StringArray;
619
620 nfound NUMBER;
621 ccid NUMBER;
622 struct_def_val NUMBER;
623 allow_id_vset BOOLEAN;
624 l_idc_code VARCHAR2(10);
625 l_newline_comb VARCHAR2(32000);
626 BEGIN
627 init_message;
628 -- Invalidate EXT globals, initialize no segs returned.
629 -- Initialize messages, debugging, and number of sql strings
630 --
631 n_segments := 0;
632 ext_globals_valid := FALSE;
633 if(FND_FLEX_SERVER1.init_globals = FALSE) THEN
634 GOTO label_failure;
635 end if;
636
637 --
638 -- Check IDC first.
639 --
640 l_idc_code := check_idc(application_short_name,
641 key_flex_code,
642 structure_number,
643 combination_id,
644 delim,
645 l_newline_comb);
646 IF (l_idc_code = fnd_plsql_cache.CACHE_VALID) THEN
647 n_segments := breakup_segments(l_newline_comb,
648 chr_newline,
649 segments);
650 GOTO label_success;
651 ELSIF (l_idc_code = fnd_plsql_cache.CACHE_INVALID) THEN
652 --
653 -- message is set by check_idc;
654 --
655 GOTO label_failure;
656 END IF;
657 --
658 -- l_idc_code is either fnd_plsql_cache.CACHE_NOTFOUND.
659 -- continue on validation.
660 --
661
662 -- Check whether this key flexfield allows id value sets or not.
663 -- If id valuesets are not allowed there is no need to validate everything.
664 -- for non-id value sets id and value are equal. So combination table contains
665 -- actual values.
666 --
667 if(NOT is_allow_id_valuesets(application_short_name, key_flex_code,
668 allow_id_vset)) THEN
669 GOTO label_failure;
670 end if;
671
672
673 IF (NOT allow_id_vset) THEN
674
675 /* This part returns the segment values for a given combination id
676 * by directly reading them from the combinations table. It does not
677 * validate the values individually in order to save time. It can only be
678 * used with flexfields that do not allow "ID" type value sets where the
679 * segment ID is the segment value. Because it does not validate the
680 * segments it does not return segment descriptions and other
681 * segment based information. This means it can only be used in
682 * simplified APIs such as get_ccid() where only the segment values
683 * are returned. This function can not be used with value sets whose
684 * displayed values depend on the user's NLS settings. Presently
685 * all value sets with translatable values (standard date and standard
686 * time) are considered ID type value sets and therefore will be excluded
687 * if the flexfield does not allow ID value sets.
688 *
689 * Also note that this function relies on the fact that retrieving the
693 */
690 * segment values from an existing combination does not check expiration
691 * or disabling on the combination or its values and does not check
692 * value security.
694
695 IF (fnd_flex_server1.g_debug_level > 0) THEN
696 FND_FLEX_SERVER1.add_debug('Non-ID value sets.Skip full validation');
697 END IF;
698
699 -- Check CCID.
700 --
701 IF ((combination_id IS NULL) OR (combination_id < 0)) THEN
702 FND_MESSAGE.set_name('FND','FLEX-BAD CCID INPUT');
703 FND_MESSAGE.set_token('CCID',to_char(combination_id));
704 GOTO label_failure;
705 END IF;
706
707 -- Read structure and comb. table information.
708 --
709 IF (NOT FND_FLEX_SERVER2.get_keystruct
710 (application_short_name, key_flex_code,
711 NULL, structure_number, kff_id, kff_info, kff_cc)) THEN
712 GOTO label_failure;
713 END IF;
714
715 -- Set global variable delim from kff structure.
716 -- used in other procedures.
717 --
718 delim := kff_info.concatenated_segment_delimiter;
719
720 -- Read segments information : column names, types etc.
721 --
722 IF (NOT FND_FLEX_SERVER2.get_struct_cols
723 (kff_id,
724 kff_cc.table_application_id, kff_cc.combination_table_id,
725 nvalidated, cc_cols, cc_coltypes, segtypes)) THEN
726 GOTO label_failure;
727 END IF;
728
729 /* Select from combination table.
730 * No qualifiers, no extra columns, no where clause.
731 * It is supposed to return seg_ids but since we ensured that they
732 * are values no need to convert from id to value.
733 * data_set is the structure_number.
734 *
735 * Only problem:
736 * find_combination returns stored_values not displayed_values
737 * However conversion to displayed values requires calling
738 * validate_structure and we are trying to get rid of it.
739 * Since we are guaranteed that flexfield doesn't use id value sets
740 * this is not a problem at all. Only non-id value sets which have
741 * displayed and stored value different are standard date v.sets.
742 * For now, since client implements them as id-value sets they are
743 * not problem here at all.
744 */
745 ccid := combination_id;
746 /* Bug 1351313 */
747 if (data_set <> -1) THEN
748 struct_def_val := data_set;
749 else
750 struct_def_val := structure_number;
751 end if;
752 nfound := FND_FLEX_SERVER.find_combination(struct_def_val,
753 kff_cc, nvalidated, cc_cols, cc_coltypes,
754 segtypes, 0, tmp_qualcols, 0, tmp_xcolnames, NULL, ccid,
755 value_ids, tbl_derv, drv_quals.sq_values, xcol_vals);
756
757 if (nfound = 0) then
758 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION NOT FOUND');
759 FND_MESSAGE.set_token('CCID', combination_id);
760 FND_MESSAGE.set_token('APNM', application_short_name);
761 FND_MESSAGE.set_token('CODE', key_flex_code);
762 FND_MESSAGE.set_token('NUM', structure_number);
763 end if;
764 if (nfound <> 1) THEN
765 GOTO label_failure;
766 end if;
767
768 -- Convert from id's to displayed values, they are same.
769 --
770 for i in 1..nvalidated LOOP
771 value_dvals(i) := value_ids(i);
772 end loop;
773
774 -- Get displayed segments information.
775 --
776 IF (NOT read_displayedsegs(kff_id, disp_segs)) THEN
777 GOTO label_failure;
778 END IF;
779
780 valid_stat := FND_FLEX_SERVER1.VV_VALID;
781
782 ELSE
783 -- Allow id valuesets is 'Y'; do full validation.
784 --
785 -- Do not check security, qsecuity was X replaced it with N.
786 --
787 FND_FLEX_SERVER.validation_engine(FND_GLOBAL.RESP_APPL_ID,
788 FND_GLOBAL.RESP_ID, FND_GLOBAL.USER_ID,
789 application_short_name, key_flex_code, NULL, structure_number,
790 NULL, NULL, -1, 'L', 'FULL', 'N', 'N', 'N', 'N',
791 'ALL', NULL, 'V', NULL, NULL, NULL, NULL,
792 combination_id, nvalidated, value_dvals, value_vals, value_ids,
793 value_descs, value_desclens, cc_cols, cc_coltypes, segtypes,
794 disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
795 n_xcol_vals, xcol_vals, delim, ccid_o, new_comb, valid_stat,
796 segcodes, err_segn);
797 END IF;
798
799 -- Return only the displayed segments if combination found
800 --
801 l_newline_comb := '';
802 if((valid_stat = FND_FLEX_SERVER1.VV_VALID) or
803 (valid_stat = FND_FLEX_SERVER1.VV_SECURED)) then
804 n_dispsegs := 0;
805 for i in 1..nvalidated loop
806 if(disp_segs.segflags(i)) THEN
807 n_dispsegs := n_dispsegs + 1;
808 segments(n_dispsegs) := SUBSTRB(value_dvals(i), 1, MAX_SEG_SIZE);
809 l_newline_comb := (l_newline_comb ||
810 SUBSTRB(value_dvals(i), 1, MAX_SEG_SIZE) ||
811 chr_newline);
812 end if;
813 end loop;
814 --
815 -- Remove last NEWLINE
816 --
820 GOTO label_success;
817 l_newline_comb := Substr(l_newline_comb, 1,
818 Length(l_newline_comb)-Length(chr_newline));
819 n_segments := n_dispsegs;
821 ELSE
822 -- bug1020410
823 GOTO label_failure;
824 end if;
825
826 <<label_success>>
827 IF (l_idc_code IN (fnd_plsql_cache.CACHE_NOTFOUND)) THEN
828 update_idc(application_short_name,
829 key_flex_code,
830 structure_number,
831 combination_id,
832 delim,
833 l_newline_comb,
834 TRUE);
835 END IF;
836 ext_globals_valid := TRUE;
837 RETURN(TRUE);
838
839 <<label_failure>>
840 IF (l_idc_code IN (fnd_plsql_cache.CACHE_NOTFOUND)) THEN
841 update_idc(application_short_name,
842 key_flex_code,
843 structure_number,
844 combination_id,
845 delim,
846 l_newline_comb,
847 FALSE);
848 END IF;
849 set_failed;
850 RETURN(FALSE);
851
852 EXCEPTION
853 WHEN OTHERS then
854 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
855 FND_MESSAGE.set_token('MSG', 'get_segments() exception: ' || SQLERRM);
856 set_failed;
857 return(FALSE);
858
859 END get_segments;
860
861 /* ------------------------------------------------------------------------ */
862 /* PRIVATE FUNCTIONS */
863 /* ------------------------------------------------------------------------ */
864
865 /* ------------------------------------------------------------------------ */
866 /* This function is copied from SV2[S|B].get_struct_cols() */
867 /* Gets the displayed information. */
868 /* ------------------------------------------------------------------------ */
869 FUNCTION read_displayedsegs(fstruct IN FND_FLEX_SERVER1.FlexStructId,
870 disp_segs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs)
871 RETURN BOOLEAN IS
872 ncols NUMBER;
873
874 CURSOR Key_column_cursor(keystruct IN FND_FLEX_SERVER1.FlexStructId) IS
875 SELECT g.display_flag
876 FROM fnd_id_flex_segments g
877 WHERE g.application_id = keystruct.application_id
878 AND g.id_flex_code = keystruct.id_flex_code
879 AND g.id_flex_num = keystruct.id_flex_num
880 AND g.enabled_flag = 'Y'
881 ORDER BY g.segment_num;
882
883 BEGIN
884 ncols := 0;
885
886 -- Assumes we are looking at a key flexfield
887 --
888 for seg in Key_column_cursor(fstruct) loop
889 ncols := ncols + 1;
890 disp_segs.segflags(ncols) := (seg.display_flag = 'Y');
891 end loop;
892
893 if(ncols < 1) then
894 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
895 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.read_displayedsegs()');
896 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
897 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
898 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
899 return(FALSE);
900 end if;
901
902 disp_segs.n_segflags := ncols;
903
904 return(TRUE);
905
906 EXCEPTION
907 WHEN OTHERS then
908 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
909 FND_MESSAGE.set_token('MSG', 'read_displayedsegs() exception: '||SQLERRM);
910 return(FALSE);
911
912 END read_displayedsegs;
913
914 /* ------------------------------------------------------------------------ */
915 /* Checks whether key flexfield allows id value sets or not. */
916 /* Designed to improve get_segments() performance. */
917 /* ------------------------------------------------------------------------ */
918
919 FUNCTION is_allow_id_valuesets(i_application_short_name IN VARCHAR2,
920 i_id_flex_code IN VARCHAR2,
921 o_allow_id_value_sets OUT nocopy BOOLEAN)
922 RETURN BOOLEAN IS
923 temp VARCHAR2(1);
924
925 BEGIN
926
927 SELECT allow_id_valuesets INTO temp
928 FROM fnd_id_flexs idf, fnd_application a
929 WHERE a.application_short_name = i_application_short_name
930 AND a.application_id = idf.application_id
931 AND idf.id_flex_code = i_id_flex_code;
932
933 if(temp = 'Y') then
934 o_allow_id_value_sets := true;
935 else
936 o_allow_id_value_sets := false;
937 end if;
938
939 RETURN(true);
940
941 EXCEPTION
942 WHEN NO_DATA_FOUND then
943 FND_MESSAGE.set_name('FND', 'FLEX-CANNOT FIND STRUCT DEF');
944 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.is_allow_id_value_sets');
945 FND_MESSAGE.set_token('APPL', i_application_short_name);
946 FND_MESSAGE.set_token('CODE', i_id_flex_code);
947 FND_MESSAGE.set_token('NUM', null);
948 return(FALSE);
949 WHEN TOO_MANY_ROWS then
950 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE STRUCT DEF');
954 FND_MESSAGE.set_token('NUM', null);
951 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.is_allow_id_value_sets');
952 FND_MESSAGE.set_token('APPL', i_application_short_name);
953 FND_MESSAGE.set_token('CODE', i_id_flex_code);
955 return(FALSE);
956 WHEN others THEN
957 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
958 FND_MESSAGE.set_token('MSG','is_allow_valuesets() exception: '||SQLERRM);
959 return(false);
960
961 END is_allow_id_valuesets;
962
963 /* ------------------------------------------------------------------------ */
964 /* Converts text format of validation date to a date. */
965 /* Sets error message and returns FALSE if format error. */
966 /* ------------------------------------------------------------------------ */
967
968 FUNCTION convert_vdate(date_string IN VARCHAR2,
969 date_value OUT nocopy DATE) RETURN BOOLEAN IS
970 BEGIN
971
972 if(LENGTH(date_string) = OLD_DATE_LEN) then
973 date_value := to_date(date_string, OLD_DATE_FMT);
974 else
975 date_value := to_date(date_string, AOL_DATE_FMT);
976 end if;
977 return(TRUE);
978
979 EXCEPTION
980 WHEN OTHERS then
981 FND_MESSAGE.set_name('FND', 'FLEX-BAD VDATE STRING');
982 return(FALSE);
983
984 END convert_vdate;
985
986
987 /* ------------------------------------------------------------------------ */
988 /* Concatenates segments input by user. */
989 /* Returns FALSE and sets error message if user input array is bad. */
990 /* ------------------------------------------------------------------------ */
991
992 FUNCTION concat_segs(n_segs IN NUMBER,
993 segment_array IN SegmentArray,
994 delimiter IN VARCHAR2,
995 cat_segs OUT nocopy VARCHAR2) RETURN BOOLEAN IS
996
997 BEGIN
998
999 if(n_segs = 1) then
1000 cat_segs := segment_array(1);
1001 else
1002 cat_segs := from_segmentarray(n_segs, segment_array, delimiter);
1003 end if;
1004 return(TRUE);
1005
1006 EXCEPTION
1007 WHEN OTHERS then
1008 FND_MESSAGE.set_name('FND', 'FLEX-BAD SEGMENT ARRAY');
1009 return(FALSE);
1010
1011 END concat_segs;
1012
1013 /* ------------------------------------------------------------------------ */
1014 /* Copies varchar2 string to output and traps exception raised if */
1015 /* the user's output string buffer is not big enough to hold the */
1016 /* input string. */
1017 /* Returns FALSE and sets FND_MESSAGE on error. */
1018 /* ------------------------------------------------------------------------ */
1019 FUNCTION output_string(s IN VARCHAR2,
1020 s_out OUT nocopy VARCHAR2) RETURN BOOLEAN IS
1021 BEGIN
1022 s_out := s;
1023 return(TRUE);
1024 EXCEPTION
1025 WHEN OTHERS then
1026 FND_MESSAGE.set_name(NULL, 'FLEX-BUFFER TOO SMALL');
1027 FND_MESSAGE.set_token('EXCEPTION', SQLERRM);
1028 return(FALSE);
1029 END output_string;
1030
1031 /* ----------------------------------------------------------------------- */
1032 /* Converts concatenated segments to segment array */
1033 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
1034 /* Copied from FND_FLEX_SERVER1.to_stringarray. Uses SegmentArray */
1035 /* instead of StringArray. */
1036 /* ----------------------------------------------------------------------- */
1037
1038 FUNCTION to_segmentarray(catsegs IN VARCHAR2,
1039 sepchar IN VARCHAR2,
1040 segs OUT nocopy SegmentArray)
1041 RETURN NUMBER
1042 IS
1043 l_wc VARCHAR2(10);
1044 l_flex_value VARCHAR2(2000);
1045 i NUMBER;
1046 l_segnum PLS_INTEGER;
1047 l_delimiter VARCHAR2(10);
1048 l_tmp_str VARCHAR2(32000);
1049 l_delim_pos PLS_INTEGER;
1050 l_old_delim_pos PLS_INTEGER;
1051
1052 BEGIN
1053 l_delimiter := Substr(sepchar, 1, 1);
1054
1055 --
1056 -- Make sure delimiter is valid.
1057 --
1058 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
1059 raise_application_error(-20001,
1060 'EXT.to_segmentarray. Invalid delimiter:''' ||
1061 Nvl(sepchar, '<NULL>') || '''');
1062 END IF;
1063
1064 --
1065 -- If catsegs is NULL then assume there is only one segment.
1066 --
1067 IF (catsegs IS NULL) THEN
1068 l_segnum := 1;
1069 segs(1) := catsegs;
1070 GOTO return_success;
1071 END IF;
1072
1073 l_segnum := 0;
1074 i := 1;
1075
1076 -- We need to go through un-escaping logic only if
1077 -- there is an ESCAPE character in the string.
1078 -- Bug 4501279.
1079
1080 IF (instr(catsegs, FLEX_DELIMITER_ESCAPE) > 0) THEN
1081
1082 --
1086 l_flex_value := NULL;
1083 -- Loop for each segment.
1084 --
1085 LOOP
1087
1088 --
1089 -- Un-escaping loop.
1090 --
1091 LOOP
1092
1093 l_wc := Substr(catsegs, i, 1);
1094 i := i + 1;
1095
1096 IF (l_wc IS NULL) THEN
1097 EXIT;
1098 ELSIF (l_wc = l_delimiter) THEN
1099 EXIT;
1100 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
1101
1102 l_wc := Substr(catsegs, i, 1);
1103 i := i + 1;
1104
1105 IF (l_wc IS NULL) THEN
1106 EXIT;
1107 END IF;
1108
1109 END IF;
1110
1111 l_flex_value := l_flex_value || l_wc;
1112
1113 END LOOP;
1114
1115 l_segnum := l_segnum + 1;
1116 segs(l_segnum) := l_flex_value;
1117 IF (l_wc IS NULL) THEN
1118 EXIT;
1119 END IF;
1120 END LOOP;
1121
1122 ELSE
1123
1124 -- No un-escaping logic required here.
1125
1126 l_tmp_str := catsegs;
1127 l_delim_pos := 0;
1128 l_old_delim_pos := 0;
1129
1130 LOOP
1131
1132 l_delim_pos := instr(l_tmp_str, l_delimiter, l_delim_pos+1);
1133
1134 IF (l_delim_pos <> 0) THEN
1135 l_segnum := l_segnum + 1;
1136 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1, l_delim_pos-l_old_delim_pos-1);
1137 l_old_delim_pos := l_delim_pos;
1138 ELSE
1139 l_segnum := l_segnum + 1;
1140 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1);
1141 EXIT;
1142 END IF;
1143
1144 END LOOP;
1145
1146 END IF;
1147
1148 <<return_success>>
1149 RETURN(l_segnum);
1150
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 raise_application_error(-20001, 'EXT.to_segmentarray. SQLERRM : ' ||
1154 Sqlerrm);
1155 END to_segmentarray;
1156
1157 /* ----------------------------------------------------------------------- */
1158 /* Converts segment array to concatenated segments */
1159 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
1160 /* Copied from FND_FLEX_SERVER1.from_stringarray. Uses SegmentArray */
1161 /* instead of StringArray. */
1162 /* ----------------------------------------------------------------------- */
1163 FUNCTION from_segmentarray(nsegs IN NUMBER,
1164 segs IN SegmentArray,
1165 sepchar IN VARCHAR2) RETURN VARCHAR2
1166 IS
1167 l_wc VARCHAR2(10);
1168 l_return VARCHAR2(32000) := NULL;
1169 i pls_integer;
1170 l_segnum pls_integer;
1171 l_delimiter VARCHAR2(10);
1172 BEGIN
1173 l_delimiter := Substr(sepchar, 1, 1);
1174 IF (fnd_flex_server1.g_debug_level > 0) THEN
1175 fnd_flex_server1.add_debug('BEGIN EXT.from_segmentarray()');
1176 END IF;
1177 --
1178 -- Make sure delimiter is valid.
1179 --
1180 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
1181 raise_application_error(-20001,
1182 'EXT.from_segmentarray. Invalid delimiter:''' ||
1183 Nvl(sepchar, '<NULL>') || '''');
1184 END IF;
1185
1186 --
1187 -- Make sure array size is valid.
1188 --
1189 IF ((nsegs IS NULL) OR (nsegs < 1)) THEN
1190 raise_application_error(-20001,
1191 'EXT.from_segmentarray. For specified context there are ''' ||
1192 Nvl(to_char(nsegs), '<NULL>') || '''' || ' displayed segments');
1193 END IF;
1194
1195 --
1196 -- If only one segment then no need for concatenating or escaping.
1197 --
1198 IF (nsegs = 1) THEN
1199 l_return := segs(1);
1200 GOTO return_success;
1201 END IF;
1202
1203 --
1204 -- Loop for each segment
1205 --
1206 FOR l_segnum IN 1..nsegs LOOP
1207
1208 i := 1;
1209
1210 --
1211 -- Escaping loop.
1212 --
1213 LOOP
1214
1215 l_wc := Substr(segs(l_segnum), i, 1);
1216 i := i + 1;
1217
1218 IF (l_wc IS NULL) THEN
1219 EXIT;
1220 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
1221 l_return := l_return || FLEX_DELIMITER_ESCAPE;
1222 l_return := l_return || FLEX_DELIMITER_ESCAPE;
1223 ELSIF (l_wc = l_delimiter) THEN
1224 l_return := l_return || FLEX_DELIMITER_ESCAPE;
1225 l_return := l_return || l_delimiter;
1226 ELSE
1227 l_return := l_return || l_wc;
1228 END IF;
1229
1230 END LOOP;
1231
1232 --
1233 -- No delimiter after the last value.
1234 --
1235 IF (l_segnum < nsegs) THEN
1236 l_return := l_return || l_delimiter;
1237 END IF;
1238 END LOOP;
1239
1240 <<return_success>>
1241 IF (fnd_flex_server1.g_debug_level > 0) THEN
1242 fnd_flex_server1.add_debug('END EXT.from_segmentarray()');
1243 END IF;
1244 RETURN(l_return);
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 IF (fnd_flex_server1.g_debug_level > 0) THEN
1248 fnd_flex_server1.add_debug('EXCEPTION EXT.from_segmentarray()');
1249 END IF;
1250 raise_application_error(-20001, 'EXT.from_segmentarray. SQLERRM : ' || Sqlerrm);
1251 END from_segmentarray;
1252
1253 /* ------------------------------------------------------------------------ */
1254
1255 BEGIN
1256 chr_newline := fnd_global.newline;
1257
1258 fnd_plsql_cache.generic_1to1_init('EXT.IDC',
1259 idc_cache_controller,
1260 idc_cache_storage);
1261
1262 fnd_plsql_cache.generic_1to1_init('EXT.GDL',
1263 gdl_cache_controller,
1264 gdl_cache_storage);
1265
1266 END fnd_flex_ext;