1 PACKAGE BODY fnd_flex_ext AS
2 /* $Header: AFFFEXTB.pls 120.5.12020000.2 2012/10/08 19:41:03 tebarnes 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
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
355 AND a.application_short_name = get_delimiter.application_short_name;
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));
386 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
383 set_failed;
384 return(NULL);
385 WHEN OTHERS then
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,
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,
463 NULL, NULL, nvalidated, value_dvals, value_vals, value_ids,
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 --
508 if((sepchar is not null) and
505 init_message;
506 sepchar := get_delimiter(application_short_name, key_flex_code,
507 structure_number);
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)
569 RETURN BOOLEAN IS
570 n_segs_out NUMBER;
571 segs_out SegmentArray;
572 catvals_out VARCHAR2(2000);
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;
626 BEGIN
623 allow_id_vset BOOLEAN;
624 l_idc_code VARCHAR2(10);
625 l_newline_comb VARCHAR2(32000);
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
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.
693 */
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,
758 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION NOT FOUND');
755 value_ids, tbl_derv, drv_quals.sq_values, xcol_vals);
756
757 if (nfound = 0) then
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 --
817 l_newline_comb := Substr(l_newline_comb, 1,
818 Length(l_newline_comb)-Length(chr_newline));
819 n_segments := n_dispsegs;
820 GOTO label_success;
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 /* Finds combination_id for given segment values. */
863 /* If validation date is NULL checks all cross-validation rules. */
864 /* If combination doesn't exist, inserts combination, even if dynamic */
865 /* insert is disabled */
866 /* Returns TRUE if combination valid, or FALSE and sets error message */
867 /* on server using FND_MESSAGE if invalid. */
868 /* ------------------------------------------------------------------------ */
869 FUNCTION get_comb_id_allow_insert(application_short_name IN VARCHAR2,
870 key_flex_code IN VARCHAR2,
871 structure_number IN NUMBER,
872 validation_date IN DATE,
873 n_segments IN NUMBER,
874 segments IN SegmentArray,
875 combination_id OUT nocopy NUMBER,
876 data_set IN NUMBER DEFAULT -1)
877 RETURN BOOLEAN IS
878 sepchar VARCHAR2(1);
879 catsegs VARCHAR2(2000);
880
881 BEGIN
882
883 -- Concatenate the input segments, then send them to the validation engine.
884 --
885 init_message;
886 sepchar := get_delimiter(application_short_name, key_flex_code,
887 structure_number);
888 if ((sepchar is not null) and
889 (concat_segs(n_segments, segments, sepchar, catsegs) = TRUE)) then
890
891 ext_globals_valid := FALSE;
895 end if;
892 if(FND_FLEX_SERVER1.init_globals = FALSE) THEN
893 set_failed;
894 return(FALSE);
896
897 FND_FLEX_SERVER.validation_engine(FND_GLOBAL.RESP_APPL_ID,
898 FND_GLOBAL.RESP_ID, FND_GLOBAL.USER_ID,
899 application_short_name, key_flex_code, NULL, structure_number,
900 validation_date, NULL, data_set, 'V', 'FULL', 'F', 'Y',
901 'N', 'N', 'ALL', catsegs, 'V', NULL, NULL, NULL,
902 NULL, NULL, nvalidated, value_dvals, value_vals, value_ids,
903 value_descs, value_desclens, cc_cols, cc_coltypes, segtypes,
904 disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
905 n_xcol_vals, xcol_vals, delim, ccid_o, new_comb, valid_stat,
906 segcodes, err_segn);
907 if(valid_stat = FND_FLEX_SERVER1.VV_VALID) then
908 combination_id := ccid_o;
909 ext_globals_valid := TRUE;
910 return(TRUE);
911 end if;
912
913 end if;
914 set_failed;
915 return(FALSE);
916
917 EXCEPTION
918 WHEN OTHERS then
919 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
920 FND_MESSAGE.set_token('MSG','get_combination_id() exception: '||SQLERRM);
921 set_failed;
922 return(FALSE);
923
924 END get_comb_id_allow_insert;
925
926
927
928 /* ------------------------------------------------------------------------ */
929 /* PRIVATE FUNCTIONS */
930 /* ------------------------------------------------------------------------ */
931
932 /* ------------------------------------------------------------------------ */
933 /* This function is copied from SV2[S|B].get_struct_cols() */
934 /* Gets the displayed information. */
935 /* ------------------------------------------------------------------------ */
936 FUNCTION read_displayedsegs(fstruct IN FND_FLEX_SERVER1.FlexStructId,
937 disp_segs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs)
938 RETURN BOOLEAN IS
939 ncols NUMBER;
940
941 CURSOR Key_column_cursor(keystruct IN FND_FLEX_SERVER1.FlexStructId) IS
942 SELECT g.display_flag
943 FROM fnd_id_flex_segments g
944 WHERE g.application_id = keystruct.application_id
945 AND g.id_flex_code = keystruct.id_flex_code
946 AND g.id_flex_num = keystruct.id_flex_num
947 AND g.enabled_flag = 'Y'
948 ORDER BY g.segment_num;
949
950 BEGIN
951 ncols := 0;
952
953 -- Assumes we are looking at a key flexfield
954 --
955 for seg in Key_column_cursor(fstruct) loop
956 ncols := ncols + 1;
957 disp_segs.segflags(ncols) := (seg.display_flag = 'Y');
958 end loop;
959
960 if(ncols < 1) then
961 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
962 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.read_displayedsegs()');
963 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
964 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
965 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
966 return(FALSE);
967 end if;
968
969 disp_segs.n_segflags := ncols;
970
971 return(TRUE);
972
973 EXCEPTION
974 WHEN OTHERS then
975 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
976 FND_MESSAGE.set_token('MSG', 'read_displayedsegs() exception: '||SQLERRM);
977 return(FALSE);
978
979 END read_displayedsegs;
980
981 /* ------------------------------------------------------------------------ */
982 /* Checks whether key flexfield allows id value sets or not. */
983 /* Designed to improve get_segments() performance. */
984 /* ------------------------------------------------------------------------ */
985
986 FUNCTION is_allow_id_valuesets(i_application_short_name IN VARCHAR2,
987 i_id_flex_code IN VARCHAR2,
988 o_allow_id_value_sets OUT nocopy BOOLEAN)
989 RETURN BOOLEAN IS
990 temp VARCHAR2(1);
991
992 BEGIN
993
994 SELECT allow_id_valuesets INTO temp
995 FROM fnd_id_flexs idf, fnd_application a
996 WHERE a.application_short_name = i_application_short_name
997 AND a.application_id = idf.application_id
998 AND idf.id_flex_code = i_id_flex_code;
999
1000 if(temp = 'Y') then
1001 o_allow_id_value_sets := true;
1002 else
1003 o_allow_id_value_sets := false;
1004 end if;
1005
1006 RETURN(true);
1007
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND then
1010 FND_MESSAGE.set_name('FND', 'FLEX-CANNOT FIND STRUCT DEF');
1011 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.is_allow_id_value_sets');
1012 FND_MESSAGE.set_token('APPL', i_application_short_name);
1013 FND_MESSAGE.set_token('CODE', i_id_flex_code);
1014 FND_MESSAGE.set_token('NUM', null);
1015 return(FALSE);
1016 WHEN TOO_MANY_ROWS then
1017 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE STRUCT DEF');
1018 FND_MESSAGE.set_token('ROUTINE', 'FND_FLEX_EXT.is_allow_id_value_sets');
1019 FND_MESSAGE.set_token('APPL', i_application_short_name);
1020 FND_MESSAGE.set_token('CODE', i_id_flex_code);
1021 FND_MESSAGE.set_token('NUM', null);
1022 return(FALSE);
1023 WHEN others THEN
1024 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1025 FND_MESSAGE.set_token('MSG','is_allow_valuesets() exception: '||SQLERRM);
1026 return(false);
1027
1028 END is_allow_id_valuesets;
1029
1033 /* ------------------------------------------------------------------------ */
1030 /* ------------------------------------------------------------------------ */
1031 /* Converts text format of validation date to a date. */
1032 /* Sets error message and returns FALSE if format error. */
1034
1035 FUNCTION convert_vdate(date_string IN VARCHAR2,
1036 date_value OUT nocopy DATE) RETURN BOOLEAN IS
1037 BEGIN
1038
1039 if(LENGTH(date_string) = OLD_DATE_LEN) then
1040 date_value := to_date(date_string, OLD_DATE_FMT);
1041 else
1042 date_value := to_date(date_string, AOL_DATE_FMT);
1043 end if;
1044 return(TRUE);
1045
1046 EXCEPTION
1047 WHEN OTHERS then
1048 FND_MESSAGE.set_name('FND', 'FLEX-BAD VDATE STRING');
1049 return(FALSE);
1050
1051 END convert_vdate;
1052
1053
1054 /* ------------------------------------------------------------------------ */
1055 /* Concatenates segments input by user. */
1056 /* Returns FALSE and sets error message if user input array is bad. */
1057 /* ------------------------------------------------------------------------ */
1058
1059 FUNCTION concat_segs(n_segs IN NUMBER,
1060 segment_array IN SegmentArray,
1061 delimiter IN VARCHAR2,
1062 cat_segs OUT nocopy VARCHAR2) RETURN BOOLEAN IS
1063
1064 BEGIN
1065
1066 if(n_segs = 1) then
1067 cat_segs := segment_array(1);
1068 else
1069 cat_segs := from_segmentarray(n_segs, segment_array, delimiter);
1070 end if;
1071 return(TRUE);
1072
1073 EXCEPTION
1074 WHEN OTHERS then
1075 FND_MESSAGE.set_name('FND', 'FLEX-BAD SEGMENT ARRAY');
1076 return(FALSE);
1077
1078 END concat_segs;
1079
1080 /* ------------------------------------------------------------------------ */
1081 /* Copies varchar2 string to output and traps exception raised if */
1082 /* the user's output string buffer is not big enough to hold the */
1083 /* input string. */
1084 /* Returns FALSE and sets FND_MESSAGE on error. */
1085 /* ------------------------------------------------------------------------ */
1086 FUNCTION output_string(s IN VARCHAR2,
1087 s_out OUT nocopy VARCHAR2) RETURN BOOLEAN IS
1088 BEGIN
1089 s_out := s;
1090 return(TRUE);
1091 EXCEPTION
1092 WHEN OTHERS then
1093 FND_MESSAGE.set_name(NULL, 'FLEX-BUFFER TOO SMALL');
1094 FND_MESSAGE.set_token('EXCEPTION', SQLERRM);
1095 return(FALSE);
1096 END output_string;
1097
1098 /* ----------------------------------------------------------------------- */
1099 /* Converts concatenated segments to segment array */
1100 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
1101 /* Copied from FND_FLEX_SERVER1.to_stringarray. Uses SegmentArray */
1102 /* instead of StringArray. */
1103 /* ----------------------------------------------------------------------- */
1104
1105 FUNCTION to_segmentarray(catsegs IN VARCHAR2,
1106 sepchar IN VARCHAR2,
1107 segs OUT nocopy SegmentArray)
1108 RETURN NUMBER
1109 IS
1110 l_wc VARCHAR2(10);
1111 l_flex_value VARCHAR2(2000);
1112 i NUMBER;
1113 l_segnum PLS_INTEGER;
1114 l_delimiter VARCHAR2(10);
1115 l_tmp_str VARCHAR2(32000);
1116 l_delim_pos PLS_INTEGER;
1117 l_old_delim_pos PLS_INTEGER;
1118
1119 BEGIN
1120 l_delimiter := Substr(sepchar, 1, 1);
1121
1122 --
1123 -- Make sure delimiter is valid.
1124 --
1125 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
1126 raise_application_error(-20001,
1127 'EXT.to_segmentarray. Invalid delimiter:''' ||
1128 Nvl(sepchar, '<NULL>') || '''');
1129 END IF;
1130
1131 --
1132 -- If catsegs is NULL then assume there is only one segment.
1133 --
1134 IF (catsegs IS NULL) THEN
1135 l_segnum := 1;
1136 segs(1) := catsegs;
1137 GOTO return_success;
1138 END IF;
1139
1140 l_segnum := 0;
1141 i := 1;
1142
1143 -- We need to go through un-escaping logic only if
1144 -- there is an ESCAPE character in the string.
1145 -- Bug 4501279.
1146
1147 IF (instr(catsegs, FLEX_DELIMITER_ESCAPE) > 0) THEN
1148
1149 --
1150 -- Loop for each segment.
1151 --
1152 LOOP
1153 l_flex_value := NULL;
1154
1155 --
1156 -- Un-escaping loop.
1157 --
1158 LOOP
1159
1160 l_wc := Substr(catsegs, i, 1);
1161 i := i + 1;
1162
1163 IF (l_wc IS NULL) THEN
1164 EXIT;
1165 ELSIF (l_wc = l_delimiter) THEN
1166 EXIT;
1167 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
1168
1169 l_wc := Substr(catsegs, i, 1);
1170 i := i + 1;
1171
1172 IF (l_wc IS NULL) THEN
1173 EXIT;
1174 END IF;
1175
1176 END IF;
1177
1178 l_flex_value := l_flex_value || l_wc;
1179
1180 END LOOP;
1181
1182 l_segnum := l_segnum + 1;
1186 END IF;
1183 segs(l_segnum) := l_flex_value;
1184 IF (l_wc IS NULL) THEN
1185 EXIT;
1187 END LOOP;
1188
1189 /* Bug 8679638. Put a condition so that Un-escaping
1190 logic is not done for single segment flexfields */
1191 IF (l_segnum = 1) THEN
1192 segs(1) := catsegs;
1193 END IF;
1194 ELSE
1195
1196 -- No un-escaping logic required here.
1197
1198 l_tmp_str := catsegs;
1199 l_delim_pos := 0;
1200 l_old_delim_pos := 0;
1201
1202 LOOP
1203
1204 l_delim_pos := instr(l_tmp_str, l_delimiter, l_delim_pos+1);
1205
1206 IF (l_delim_pos <> 0) THEN
1207 l_segnum := l_segnum + 1;
1208 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1, l_delim_pos-l_old_delim_pos-1);
1209 l_old_delim_pos := l_delim_pos;
1210 ELSE
1211 l_segnum := l_segnum + 1;
1212 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1);
1213 EXIT;
1214 END IF;
1215
1216 END LOOP;
1217
1218 END IF;
1219
1220 <<return_success>>
1221 RETURN(l_segnum);
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 raise_application_error(-20001, 'EXT.to_segmentarray. SQLERRM : ' ||
1226 Sqlerrm);
1227 END to_segmentarray;
1228
1229 /* ----------------------------------------------------------------------- */
1230 /* Converts segment array to concatenated segments */
1231 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
1232 /* Copied from FND_FLEX_SERVER1.from_stringarray. Uses SegmentArray */
1233 /* instead of StringArray. */
1234 /* ----------------------------------------------------------------------- */
1235 FUNCTION from_segmentarray(nsegs IN NUMBER,
1236 segs IN SegmentArray,
1237 sepchar IN VARCHAR2) RETURN VARCHAR2
1238 IS
1239 l_wc VARCHAR2(10);
1240 l_return VARCHAR2(32000) := NULL;
1241 i pls_integer;
1242 l_segnum pls_integer;
1243 l_delimiter VARCHAR2(10);
1244 BEGIN
1245 l_delimiter := Substr(sepchar, 1, 1);
1246 IF (fnd_flex_server1.g_debug_level > 0) THEN
1247 fnd_flex_server1.add_debug('BEGIN EXT.from_segmentarray()');
1248 END IF;
1249 --
1250 -- Make sure delimiter is valid.
1251 --
1252 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
1253 raise_application_error(-20001,
1254 'EXT.from_segmentarray. Invalid delimiter:''' ||
1255 Nvl(sepchar, '<NULL>') || '''');
1256 END IF;
1257
1258 --
1259 -- Make sure array size is valid.
1260 --
1261 IF ((nsegs IS NULL) OR (nsegs < 1)) THEN
1262 raise_application_error(-20001,
1263 'EXT.from_segmentarray. For specified context there are ''' ||
1264 Nvl(to_char(nsegs), '<NULL>') || '''' || ' displayed segments');
1265 END IF;
1266
1267 --
1268 -- If only one segment then no need for concatenating or escaping.
1269 --
1270 IF (nsegs = 1) THEN
1271 l_return := segs(1);
1272 GOTO return_success;
1273 END IF;
1274
1275 --
1276 -- Loop for each segment
1277 --
1278 FOR l_segnum IN 1..nsegs LOOP
1279
1280 i := 1;
1281
1282 --
1283 -- Escaping loop.
1284 --
1285 LOOP
1286
1287 l_wc := Substr(segs(l_segnum), i, 1);
1288 i := i + 1;
1289
1290 IF (l_wc IS NULL) THEN
1291 EXIT;
1292 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
1293 l_return := l_return || FLEX_DELIMITER_ESCAPE;
1294 l_return := l_return || FLEX_DELIMITER_ESCAPE;
1295 ELSIF (l_wc = l_delimiter) THEN
1296 l_return := l_return || FLEX_DELIMITER_ESCAPE;
1297 l_return := l_return || l_delimiter;
1298 ELSE
1299 l_return := l_return || l_wc;
1300 END IF;
1301
1302 END LOOP;
1303
1304 --
1305 -- No delimiter after the last value.
1306 --
1307 IF (l_segnum < nsegs) THEN
1308 l_return := l_return || l_delimiter;
1309 END IF;
1310 END LOOP;
1311
1312 <<return_success>>
1313 IF (fnd_flex_server1.g_debug_level > 0) THEN
1314 fnd_flex_server1.add_debug('END EXT.from_segmentarray()');
1315 END IF;
1316 RETURN(l_return);
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 IF (fnd_flex_server1.g_debug_level > 0) THEN
1320 fnd_flex_server1.add_debug('EXCEPTION EXT.from_segmentarray()');
1321 END IF;
1322 raise_application_error(-20001, 'EXT.from_segmentarray. SQLERRM : ' || Sqlerrm);
1323 END from_segmentarray;
1324
1325 /* ------------------------------------------------------------------------ */
1326
1327 BEGIN
1328 chr_newline := fnd_global.newline;
1329
1330 fnd_plsql_cache.generic_1to1_init('EXT.IDC',
1331 idc_cache_controller,
1332 idc_cache_storage);
1333
1334 fnd_plsql_cache.generic_1to1_init('EXT.GDL',
1335 gdl_cache_controller,
1336 gdl_cache_storage);
1337
1338 END fnd_flex_ext;
[Home] [Help]