1 PACKAGE BODY fnd_flex_server1 AS
2 /* $Header: AFFFSV1B.pls 120.29.12020000.2 2012/07/19 00:32:43 hgeorgi ship $ */
3
4 --------
5 -- PRIVATE TYPES
6 --
7 --
8 -- Segment array is 1-based containing entries for i <= i <= nsegs
9
10 TYPE SqlStringArray IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
11 TYPE VsNameArray IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
12 TYPE SegNameArray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
13
14 -- Stored value is value stored in the database.
15 -- Displayed value is value displayed to the user. This depends on the
16 -- users NLS parameters at runtime.
17 --
18 TYPE FlexValue IS RECORD
19 (displayed_value VARCHAR2(1000),
20 stored_value VARCHAR2(1000),
21 hidden_id VARCHAR2(1000),
22 description VARCHAR2(1000),
23 format VARCHAR2(4),
24 compiled_attributes VARCHAR2(2000),
25 summary_flag VARCHAR2(1),
26 enabled_flag VARCHAR2(1),
27 start_valid DATE,
28 end_valid DATE);
29
30 TYPE SegmentInfo IS RECORD
31 (segname fnd_id_flex_segments.segment_name%TYPE,
32 colname fnd_id_flex_segments.application_column_name%TYPE,
33 coltype fnd_columns.column_type%TYPE,
34 collen fnd_columns.width%TYPE,
35 required fnd_id_flex_segments.required_flag%TYPE,
36 segsecure fnd_id_flex_segments.security_enabled_flag%TYPE,
37 catdesclen fnd_id_flex_segments.concatenation_description_len%TYPE,
38 dflt_type fnd_id_flex_segments.default_type%TYPE,
39 dflt_val fnd_id_flex_segments.default_value%TYPE,
40 vsid fnd_id_flex_segments.flex_value_set_id%TYPE,
41 runtime_property_function fnd_id_flex_segments.runtime_property_function%TYPE,
42 additional_where_clause fnd_id_flex_segments.additional_where_clause%TYPE);
43
44 TYPE ValueSetInfo IS RECORD
45 (vsid fnd_flex_value_sets.flex_value_set_id%TYPE,
46 parent_vsid fnd_flex_value_sets.parent_flex_value_set_id%TYPE,
47 valsecure fnd_flex_value_sets.security_enabled_flag%TYPE,
48 valtype fnd_flex_value_sets.validation_type%TYPE,
49 vsformat fnd_flex_value_sets.format_type%TYPE,
50 maxsize fnd_flex_value_sets.maximum_size%TYPE,
51 lettersok fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE,
52 capsonly fnd_flex_value_sets.uppercase_only_flag%TYPE,
53 zfill fnd_flex_value_sets.numeric_mode_enabled_flag%TYPE,
54 precis fnd_flex_value_sets.number_precision%TYPE,
55 minval fnd_flex_value_sets.minimum_value%TYPE,
56 maxval fnd_flex_value_sets.maximum_value%TYPE,
57 vsname fnd_flex_value_sets.flex_value_set_name%TYPE);
58
59 ------------
60 -- PRIVATE CONSTANTS
61
62 --
63 -- Character-set independent NEWLINE, TAB and WHITESPACE
64 --
65 NEWLINE VARCHAR2(4);
66 TAB VARCHAR2(4);
67 WHITESPACE VARCHAR2(12);
68
69 DATE_STORAGE_FMT CONSTANT VARCHAR2(21) := 'YYYY/MM/DD HH24:MI:SS';
70 TIME_STORAGE_FMT CONSTANT VARCHAR2(10) := 'HH24:MI:SS';
71 TIME_DISPLAY_FMT CONSTANT VARCHAR2(10) := 'HH24:MI:SS';
72 FLEX_DELIMITER_ESCAPE CONSTANT VARCHAR2(1) := '\';
73
74 -- Characters allowed in segment, value set profile and :block.field names
75 -- for interpreting $FLEX$, $PROFILE$, and :BLOCK.FIELD references
76 --
77 FLEX_BIND_CHARS CONSTANT VARCHAR2(150) := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_$#.:*';
78
79
80 --
81 -- SQL String Parser: Constants
82 --
83 SSP_QUOTE constant varchar2(1) := '''';
84 SSP_QUOTE2 constant varchar2(2) := SSP_QUOTE || SSP_QUOTE;
85 SSP_COLON constant varchar2(1) := ':';
86 SSP_BIND_CHARS constant varchar2(150) := FLEX_BIND_CHARS;
87
88 --
89 -- SQL String Parser: Piece Types
90 --
91 SSP_PIECE_TYPE_QUOTED constant varchar2(1) := 'Q';
92 SSP_PIECE_TYPE_BIND constant varchar2(1) := 'B';
93 SSP_PIECE_TYPE_SQL constant varchar2(1) := 'S';
94
95 TYPE sql_piece_rec_type IS RECORD
96 (piece_type varchar2(1),
97 piece_text varchar2(32000),
98
99 bind_value varchar2(32000));
100
101 TYPE sql_pieces_tab_type IS TABLE OF
102 sql_piece_rec_type INDEX BY binary_integer;
103
104 security_begin CONSTANT VARCHAR2(180) :=
105 'select M.ERROR_MESSAGE ' ||
106 ' from FND_FLEX_VALUE_RULE_USAGES U, FND_FLEX_VALUE_RULES_VL M ' ||
107 ' where U.FLEX_VALUE_RULE_ID = M.FLEX_VALUE_RULE_ID ';
108
109 security_mid CONSTANT VARCHAR2(180) :=
110 'exists (select null ' ||
111 ' from FND_FLEX_VALUE_RULE_LINES L ' ||
112 ' where 1 = 1 ';
113
114 security_end CONSTANT VARCHAR2(180) :=
115 'and L.FLEX_VALUE_RULE_ID = U.FLEX_VALUE_RULE_ID ' ||
116 'and L.INCLUDE_EXCLUDE_INDICATOR = ';
117
118
119 -------------
120 -- GLOBAL VARIABLES
121 --
122 chr_newline VARCHAR2(8);
123
124 n_sqlstrings BINARY_INTEGER;
125 sqlstrings SqlStringArray;
126
127 -- Canonical, Display, and Database decimal separators.
128 tmp_varchar2 VARCHAR2(1000);
129 m_nc VARCHAR2(1);
130 m_nd VARCHAR2(1);
131 m_nb VARCHAR2(1);
132
133 -- Debugging information
134 TYPE debug_array_type IS TABLE OF VARCHAR2(1500)
135 INDEX BY BINARY_INTEGER;
136
137 MAX_RETSTR_LEN CONSTANT NUMBER := 1500;
138 g_debug_array debug_array_type;
139 g_debug_array_size NUMBER := 0;
140 g_debug_text VARCHAR2(2000);
141
142 -- Error message information for value error message handling.
143 -- Value errors are generated by incorrect user input.
144 -- Only set first value error message in FND_MESSAGE
145 -- All other error messages override value errors.
146 value_error_set BOOLEAN;
147 entering_new_message BOOLEAN;
148
149 /* -------------------------------------------------------------------- */
150 /* Private definitions for value validation */
151 /* -------------------------------------------------------------------- */
152
153 FUNCTION string_clause(char_string IN VARCHAR2) RETURN VARCHAR2;
154
155 FUNCTION validate_seg(seg_in IN VARCHAR2,
156 displayed IN BOOLEAN,
157 kseg IN SegmentInfo,
158 vsinf IN ValueSetInfo,
159 vflags IN ValueValidationFlags,
160 fstruct IN FlexStructId,
161 v_date IN DATE,
162 v_ruls IN Vrules,
163 uappid IN NUMBER,
164 respid IN NUMBER,
165 nprev IN NUMBER,
166 prev_dispvals IN ValueArray,
167 prev_vals IN ValueArray,
168 prev_ids IN ValueIdArray,
169 prev_descs IN ValueDescArray,
170 prev_vsids IN NumberArray,
171 prev_segnames IN SegNameArray,
172 prev_vsnames IN VsNameArray,
173 x_flexvalue OUT nocopy FlexValue,
174 squals OUT nocopy Qualifiers) RETURN VARCHAR2;
175
176 FUNCTION coerce_format(user_value IN VARCHAR2,
177 p_is_displayed IN BOOLEAN,
178 vs_format IN VARCHAR2,
179 vs_name IN VARCHAR2,
180 max_length IN NUMBER,
181 letters_ok IN VARCHAR2,
182 caps_only IN VARCHAR2,
183 zero_fill IN VARCHAR2,
184 precision IN VARCHAR2,
185 min_value IN VARCHAR2,
186 max_value IN VARCHAR2,
187 x_storage_value OUT nocopy VARCHAR2,
188 x_display_value OUT nocopy VARCHAR2) RETURN VARCHAR2;
189
190 FUNCTION find_value(p_str_info IN FlexStructId,
191 p_seg_info IN SegmentInfo,
192 p_vs_info IN ValueSetInfo,
193 p_vdate IN DATE,
194 p_char_val IN VARCHAR2,
195 p_is_value IN BOOLEAN,
196 p_orphans_ok IN BOOLEAN,
197 p_this_segname IN VARCHAR2,
198 p_n_prev IN NUMBER,
199 p_prev_vsids IN NumberArray,
200 p_prev_dispvals IN ValueArray,
201 p_prev_vals IN ValueArray,
202 p_prev_ids IN ValueIdArray,
203 p_prev_descs IN ValueDescArray,
204 p_prev_segnames IN SegNameArray,
205 p_prev_vsnames IN VsNameArray,
206 p_parent_val IN VARCHAR2,
207 x_this_val_out OUT nocopy FlexValue) RETURN VARCHAR2;
208
209 FUNCTION table_validate(p_str_info IN FlexStructId,
210 p_seg_info IN SegmentInfo,
211 p_vs_info IN ValueSetInfo,
212 p_vdate IN DATE,
213 p_parent_value IN VARCHAR2,
214 p_charval IN VARCHAR2,
215 p_is_value IN BOOLEAN,
216 p_nprev IN NUMBER,
217 p_prev_dispvals IN ValueArray,
218 p_prev_vals IN ValueArray,
219 p_prev_ids IN ValueIdArray,
220 p_prev_descs IN ValueDescArray,
221 p_prev_segnames IN SegNameArray,
222 p_prev_vsnames IN VsNameArray,
223 x_check_valtab OUT nocopy BOOLEAN,
224 x_found_value OUT nocopy FlexValue)
225 RETURN VARCHAR2;
226
227 FUNCTION default_val(def_type IN VARCHAR2,
228 def_text IN VARCHAR2,
229 valset_fmt IN VARCHAR2,
230 valset_len IN NUMBER,
231 valset_precis IN NUMBER,
232 valset_lettersok IN VARCHAR2,
233 seg_name IN VARCHAR2,
234 nprev IN NUMBER,
235 prev_dispvals IN ValueArray,
236 prev_vals IN ValueArray,
237 prev_ids IN ValueIdArray,
238 prev_descs IN ValueDescArray,
239 prev_segnames IN SegNameArray,
240 prev_vsnames IN VsNameArray,
241 displayed_val OUT nocopy VARCHAR2) RETURN VARCHAR2;
242
243 PROCEDURE parse_sql_string(p_sql_string in varchar2,
244 px_sql_pieces in out nocopy sql_pieces_tab_type);
245
246 FUNCTION substitute_flex_binds3(p_string_in IN VARCHAR2,
247 p_nprev IN NUMBER,
248 p_prev_dispvals IN ValueArray,
249 p_prev_vals IN ValueArray,
250 p_prev_ids IN ValueIdArray,
251 p_prev_descs IN ValueDescArray,
252 p_prev_segnames IN SegNameArray,
253 p_prev_vsnames IN VsNameArray,
254 px_sql_pieces in out nocopy sql_pieces_tab_type)
255 RETURN VARCHAR2;
256
257 FUNCTION substitute_flex_binds5(p_string_in IN VARCHAR2,
258 p_str_info IN FlexStructId,
259 p_seg_info IN SegmentInfo,
260 p_vdate IN DATE,
261 p_parent_value IN VARCHAR2,
262 p_nprev IN NUMBER,
263 p_prev_dispvals IN ValueArray,
264 p_prev_vals IN ValueArray,
265 p_prev_ids IN ValueIdArray,
266 p_prev_descs IN ValueDescArray,
267 p_prev_segnames IN SegNameArray,
268 p_prev_vsnames IN VsNameArray,
269 px_sql_pieces in out nocopy sql_pieces_tab_type)
270 RETURN VARCHAR2;
271
272 FUNCTION convert_bind_token(bind_token IN VARCHAR2,
273 nprev IN NUMBER,
274 prev_dispvals IN ValueArray,
275 prev_vals IN ValueArray,
276 prev_ids IN ValueIdArray,
277 prev_descs IN ValueDescArray,
278 prev_segnames IN SegNameArray,
279 prev_vsnames IN VsNameArray,
280 bind_value OUT nocopy VARCHAR2) RETURN VARCHAR2;
281
282 FUNCTION convert_bind_token2(p_bind_token IN VARCHAR2,
283 p_str_info IN FlexStructId,
284 p_seg_info IN SegmentInfo,
285 p_vdate IN DATE,
286 p_parent_value IN VARCHAR2,
287 p_nprev IN NUMBER,
288 p_prev_dispvals IN ValueArray,
289 p_prev_vals IN ValueArray,
290 p_prev_ids IN ValueIdArray,
291 p_prev_descs IN ValueDescArray,
292 p_prev_segnames IN SegNameArray,
293 p_prev_vsnames IN VsNameArray,
294 x_bind_value OUT nocopy VARCHAR2) RETURN VARCHAR2;
295
296 FUNCTION get_value_set(value_set_id IN NUMBER,
297 segment_name IN VARCHAR2,
298 vs_info OUT nocopy ValueSetInfo) RETURN BOOLEAN;
299
300 FUNCTION virtual_value_set(column_type IN VARCHAR2,
301 column_width IN NUMBER,
302 segment_name IN VARCHAR2,
303 vs_info OUT nocopy ValueSetInfo) RETURN BOOLEAN;
304
305 FUNCTION get_qualifiers(ffstruct IN FlexStructId,
306 seg_colname IN VARCHAR2,
307 seg_quals OUT nocopy Qualifiers) RETURN NUMBER;
308
309
310 FUNCTION qualifier_values(ffstruct IN FlexStructId,
311 valset_id IN NUMBER,
312 cvas IN VARCHAR2,
313 nqualifs IN NUMBER,
314 fqnames IN QualNameArray,
315 sqnames IN QualNameArray,
316 sqvals IN OUT nocopy ValAttribArray) RETURN NUMBER;
317
318 FUNCTION derive_values(new_dvals IN DerivedVals,
319 new_quals IN Qualifiers,
320 drv_dvals IN OUT nocopy DerivedVals,
321 drv_quals IN OUT nocopy Qualifiers) RETURN BOOLEAN;
322
323 FUNCTION check_security(val IN VARCHAR2,
324 valfmt IN VARCHAR2,
325 parentval IN VARCHAR2,
326 user_apid IN NUMBER,
327 user_respid IN NUMBER,
328 vsinfo IN ValueSetInfo,
329 set_message IN BOOLEAN) RETURN VARCHAR2;
330
331 FUNCTION check_vrules(vrs IN Vrules,
332 sqs IN Qualifiers,
333 sumflg IN VARCHAR2) RETURN VARCHAR2;
334
335 FUNCTION check_displayed(segindex IN NUMBER,
336 disp_tokmap IN DisplayedSegs,
337 d_flag OUT nocopy BOOLEAN) RETURN BOOLEAN;
338
339 PROCEDURE value_error_init;
340
341 PROCEDURE value_error_name(appl_sname IN VARCHAR2,
342 errmsg_name IN VARCHAR2);
343
344 PROCEDURE value_error_token(token_name IN VARCHAR2,
345 token_value IN VARCHAR2);
346
347 FUNCTION msg_val(valset_fmt IN VARCHAR2,
348 valset_len IN NUMBER,
349 valset_prec IN NUMBER,
350 valset_lettersok IN VARCHAR2,
351 stored_val IN VARCHAR2) RETURN VARCHAR2;
352
353 FUNCTION stored_to_displayed(valset_fmt IN VARCHAR2,
354 valset_len IN NUMBER,
355 valset_prec IN NUMBER,
356 valset_lettersok IN VARCHAR2,
357 stored_val IN VARCHAR2,
358 disp_val OUT nocopy VARCHAR2) RETURN VARCHAR2;
359
360 FUNCTION isa_stored_date(teststr IN VARCHAR2,
361 flexfmt IN VARCHAR2,
362 outdate OUT nocopy DATE) RETURN BOOLEAN;
363
364 FUNCTION isa_displayed_date(teststr IN VARCHAR2,
365 flexfmt IN VARCHAR2,
366 outdate OUT nocopy DATE) RETURN BOOLEAN;
367
368 FUNCTION isa_date(teststr IN VARCHAR2,
369 datefmt IN VARCHAR2,
370 outdate OUT nocopy DATE) RETURN BOOLEAN;
371
372 FUNCTION displayed_date_format(flex_data_type IN VARCHAR2,
373 string_length IN NUMBER) RETURN VARCHAR2;
374
375 -- ======================================================================
376 -- Caching.
377 -- ======================================================================
378 g_cache_return_code VARCHAR2(30);
379 g_cache_key VARCHAR2(2000);
380 g_cache_value fnd_plsql_cache.generic_cache_value_type;
381 g_cache_values fnd_plsql_cache.generic_cache_values_type;
382 g_cache_indexes fnd_plsql_cache.custom_cache_indexes_type;
383 g_cache_numof_indexes NUMBER;
384 g_cache_numof_values NUMBER;
385
386 -- ======================================================================
387 -- Value Set Cache : VST
388 --
389 -- PK: <flex_value_set_id>
390 --
391 -- ======================================================================
392 vst_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
393 vst_cache_storage fnd_plsql_cache.generic_cache_values_type;
394
395 -- ======================================================================
396 -- Value Security Cache : VSC
397 --
398 -- PK: <application_id> || NEWLINE || <responsibility_id> || NEWLINE ||
399 -- <value_set_id> || NEWLINE || <parent_value> || NEWLINE ||
400 -- <value>
401 --
402 -- ======================================================================
403 vsc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
404 vsc_cache_storage fnd_plsql_cache.generic_cache_values_type;
405
406 -- ======================================================================
407 -- Flex Value Cache : FVC
408 --
409 -- PK: <value_set_id> || NEWLINE || <parent_value> || NEWLINE || <value>
410 --
411 -- ======================================================================
412 fvc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
413 fvc_cache_storage fnd_plsql_cache.generic_cache_values_type;
414
415 -- ======================================================================
416 -- Flex Segment Qualifiers Cache : FSQ
417 --
418 -- PK: <application_id> || NEWLINE || <id_flex_code> || NEWLINE ||
419 -- <id_flex_num> || NEWLINE || <application_column_name>
420 --
421 -- ======================================================================
422 fsq_cache_controller fnd_plsql_cache.cache_1tom_controller_type;
423 fsq_cache_storage fnd_plsql_cache.generic_cache_values_type;
424
425 -- ======================================================================
426 -- Value Set Qualifiers Cache : VSQ
427 --
428 -- PK: <flex_value_set_id>
429 --
430 -- ======================================================================
431 vsq_cache_controller fnd_plsql_cache.cache_1tom_controller_type;
432 vsq_cache_storage fnd_plsql_cache.generic_cache_values_type;
433
434 -- ======================================================================
435 -- Validate Structue Segments Cache : str
436 -- ======================================================================
437 TYPE str_cache_storage_type IS TABLE OF segmentinfo
438 INDEX BY BINARY_INTEGER;
439
440 str_cache_controller fnd_plsql_cache.cache_1tom_controller_type;
441 str_cache_storage str_cache_storage_type;
442
443 -- ======================================================================
444 -- Local Cache Functions
445 -- ======================================================================
446 FUNCTION check_vsc(p_application_id IN NUMBER,
447 p_responsibility_id IN NUMBER,
448 p_value_set_id IN NUMBER,
449 p_parent_value IN VARCHAR2,
450 p_value IN VARCHAR2,
451 px_error_code IN OUT nocopy VARCHAR2)
452 RETURN VARCHAR2
453 IS
454 BEGIN
455 --
456 -- seperate p_parent_value and p_value to get rid of ambiguity.
457 --
458 g_cache_key := (p_parent_value || '.' ||
459 p_application_id || '.' ||
460 p_responsibility_id || '.' ||
461 p_value_set_id || '.' ||
462 p_value);
463
464 fnd_plsql_cache.generic_1to1_get_value(vsc_cache_controller,
465 vsc_cache_storage,
466 g_cache_key,
467 g_cache_value,
468 g_cache_return_code);
469
470 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
471 px_error_code := g_cache_value.varchar2_1;
472 IF (px_error_code = FF_VVALID) THEN
473 RETURN(fnd_plsql_cache.CACHE_VALID);
474 ELSE
475 fnd_message.set_encoded(g_cache_value.varchar2_2);
476 RETURN(fnd_plsql_cache.CACHE_INVALID);
477 END IF;
478 ELSE
479 RETURN(g_cache_return_code);
480 END IF;
481 EXCEPTION
482 WHEN OTHERS THEN
483 RETURN(fnd_plsql_cache.CACHE_NOTFOUND);
484 END check_vsc;
485
486 PROCEDURE update_vsc(p_application_id IN NUMBER,
487 p_responsibility_id IN NUMBER,
488 p_value_set_id IN NUMBER,
489 p_parent_value IN VARCHAR2,
490 p_value IN VARCHAR2,
491 p_error_code IN VARCHAR2)
492 IS
493 l_enc_err_msg VARCHAR2(2000) := NULL;
494 BEGIN
495 --
496 -- seperate p_parent_value and p_value to get rid of ambiguity.
497 --
498 g_cache_key := (p_parent_value || '.' ||
499 p_application_id || '.' ||
500 p_responsibility_id || '.' ||
501 p_value_set_id || '.' ||
502 p_value);
503
504 IF (p_error_code <> FF_VVALID) THEN
505 l_enc_err_msg := fnd_message.get_encoded;
506 fnd_message.set_encoded(l_enc_err_msg);
507 END IF;
508
509 fnd_plsql_cache.generic_cache_new_value
510 (x_value => g_cache_value,
511 p_varchar2_1 => p_error_code,
512 p_varchar2_2 => l_enc_err_msg);
513
514 fnd_plsql_cache.generic_1to1_put_value(vsc_cache_controller,
515 vsc_cache_storage,
516 g_cache_key,
517 g_cache_value);
518 EXCEPTION
519 WHEN OTHERS THEN
520 RETURN;
521 END update_vsc;
522
523 -- ----------------------------------------------------------------------
524 FUNCTION check_fvc(p_value_set_id IN NUMBER,
525 p_parent_value IN VARCHAR2,
526 p_value IN VARCHAR2,
527 px_flexvalue IN OUT nocopy flexvalue)
528
529 RETURN VARCHAR2
530 IS
531 BEGIN
532 --
533 -- seperate p_parent_value and p_value to get rid of ambiguity.
534 --
535 g_cache_key := (p_parent_value || '.' ||
536 p_value_set_id || '.' ||
537 p_value);
538
539 fnd_plsql_cache.generic_1to1_get_value(fvc_cache_controller,
540 fvc_cache_storage,
541 g_cache_key,
542 g_cache_value,
543 g_cache_return_code);
544
545 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
546 px_flexvalue.displayed_value := g_cache_value.varchar2_1;
547 px_flexvalue.stored_value := g_cache_value.varchar2_2;
548 px_flexvalue.hidden_id := g_cache_value.varchar2_3;
549 px_flexvalue.description := g_cache_value.varchar2_4;
550 px_flexvalue.format := g_cache_value.varchar2_5;
551 px_flexvalue.compiled_attributes := g_cache_value.varchar2_6;
552 px_flexvalue.summary_flag := g_cache_value.varchar2_7;
553 px_flexvalue.enabled_flag := g_cache_value.varchar2_8;
554 px_flexvalue.start_valid := g_cache_value.date_1;
555 px_flexvalue.end_valid := g_cache_value.date_2;
556 END IF;
557 RETURN(g_cache_return_code);
558 EXCEPTION
559 WHEN OTHERS THEN
560 RETURN(fnd_plsql_cache.CACHE_NOTFOUND);
561 END check_fvc;
562
563 PROCEDURE update_fvc(p_value_set_id IN NUMBER,
564 p_parent_value IN VARCHAR2,
565 p_value IN VARCHAR2,
566 p_flexvalue IN flexvalue)
567 IS
568 BEGIN
569 --
570 -- seperate p_parent_value and p_value to get rid of ambiguity.
571 --
572 g_cache_key := (p_parent_value || '.' ||
573 p_value_set_id || '.' ||
574 p_value);
575
576 fnd_plsql_cache.generic_cache_new_value
577 (x_value => g_cache_value,
578 p_varchar2_1 => p_flexvalue.displayed_value,
579 p_varchar2_2 => p_flexvalue.stored_value,
580 p_varchar2_3 => p_flexvalue.hidden_id,
581 p_varchar2_4 => p_flexvalue.description,
582 p_varchar2_5 => p_flexvalue.format,
583 p_varchar2_6 => p_flexvalue.compiled_attributes,
584 p_varchar2_7 => p_flexvalue.summary_flag,
585 p_varchar2_8 => p_flexvalue.enabled_flag,
586 p_date_1 => p_flexvalue.start_valid,
587 p_date_2 => p_flexvalue.end_valid);
588
589 fnd_plsql_cache.generic_1to1_put_value(fvc_cache_controller,
590 fvc_cache_storage,
591 g_cache_key,
592 g_cache_value);
593 EXCEPTION
594 WHEN OTHERS THEN
595 RETURN;
596 END update_fvc;
597
598 /* ----------------------------------------------------------------------- */
599 /* Checks each segment input to see if it is secured against the */
600 /* current user. Returns index of first secured segment or 0 if */
601 /* no segments are secured or < 0 if error. */
602 /* Segment index does not take displayed flag into account. */
603 /* Does not look up the values. Stops at first secured value. */
604 /* Sets error message if security violated or on error. */
605 /* Uses only the non-coerced values in the segments field. */
606 /* Do not worry about orphans for dependent values. */
607 /* ----------------------------------------------------------------------- */
608 FUNCTION vals_secured(fstruct IN FlexStructId,
609 nsegs IN NUMBER,
610 segs IN StringArray,
611 displ IN DisplayedSegs,
612 uappid IN NUMBER,
613 respid IN NUMBER)
614 RETURN NUMBER
615 IS
616 segcounter BINARY_INTEGER;
617 dispsegcounter BINARY_INTEGER;
618 seg_displayed BOOLEAN;
619 thisval VARCHAR2(1000);
620 parentval VARCHAR2(60);
621 parentindex BINARY_INTEGER;
622 prior_vals ValueArray;
623 prior_vsids NumberArray;
624 segname VARCHAR2(30);
625 segvsid NUMBER;
626 segsecure VARCHAR2(1);
627 vsinfo ValueSetInfo;
628 l_return_code NUMBER;
629
630 CURSOR KeySegC(keystruct in FlexStructId) IS
631 SELECT segment_name, flex_value_set_id, security_enabled_flag
632 FROM fnd_id_flex_segments
633 WHERE application_id = keystruct.application_id
634 AND id_flex_code = keystruct.id_flex_code
635 AND id_flex_num = keystruct.id_flex_num
636 AND enabled_flag = 'Y'
637 ORDER BY segment_num;
638
639 CURSOR DescSegC(descstruct in FlexStructId) IS
640 SELECT end_user_column_name, flex_value_set_id, security_enabled_flag
641 FROM fnd_descr_flex_column_usages
642 WHERE application_id = descstruct.application_id
643 AND descriptive_flexfield_name = descstruct.desc_flex_name
644 AND descriptive_flex_context_code = descstruct.desc_flex_context
645 AND enabled_flag = 'Y'
646 ORDER BY column_seq_num;
647
648 BEGIN
649 IF (fnd_flex_server1.g_debug_level > 0) THEN
650 add_debug('BEGIN SV1.vals_secured()');
651 END IF;
652 segcounter := 0;
653 dispsegcounter := 0;
654 value_error_init;
655
656 if(fstruct.isa_key_flexfield) then
657 open KeySegC(fstruct);
658 else
659 open DescSegC(fstruct);
660 end if;
661
662 -- Begin main loop
663
664 loop
665
666 if(fstruct.isa_key_flexfield) then
667 fetch KeySegC into segname, segvsid, segsecure;
668 exit when (KeySegC%NOTFOUND or (KeySegC%NOTFOUND is null));
669 else
670 fetch DescSegC into segname, segvsid, segsecure;
671 exit when (DescSegC%NOTFOUND or (DescSegC%NOTFOUND is null));
672 end if;
673
674 segcounter := segcounter + 1;
675
676 -- Exit with error if exception occurred during check.
677 --
678 if(check_displayed(segcounter, displ, seg_displayed) = FALSE) THEN
679 l_return_code := -6;
680 GOTO goto_return;
681 end if;
682
683 -- Just ignore non-displayed segments.
684 --
685 if(seg_displayed) then
686 dispsegcounter := dispsegcounter + 1;
687 else
688 IF (fnd_flex_server1.g_debug_level > 0) THEN
689 add_debug('Segment ' || to_char(segcounter) || ' not displayed ');
690 END IF;
691 thisval := NULL;
692 goto next_value;
693 end if;
694
695 -- Treat remaining segments as NULL and quit if too few segments entered.
696 --
697 if(dispsegcounter > nsegs) then
698 IF (fnd_flex_server1.g_debug_level > 0) THEN
699 add_debug('No more values-exiting. ');
700 END IF;
701 l_return_code := 0;
702 GOTO goto_return;
703 end if;
704
705 -- Initialize value. Strip spaces.
706 --
707 thisval := RTRIM(LTRIM(segs(dispsegcounter)));
708
709 IF (fnd_flex_server1.g_debug_level > 0) THEN
710 add_debug('(' || thisval || ') ');
711 END IF;
712
713 -- If null, its not secured.
714 --
715 if(thisval is null) then
716 goto next_value;
717 end if;
718
719 -- Get value set info. If no value set, then it's not secured.
720 --
721 if(segvsid is null) then
722 goto next_value;
723 else
724 if(get_value_set(segvsid, segname, vsinfo) <> TRUE) THEN
725 l_return_code := -7;
726 GOTO goto_return;
727 end if;
728 end if;
729
730 parentval := NULL;
731
732 -- If dependent value set find parent value.
733 -- Orphans do not generate error, but lack of a parent value set does.
734 --
735 if(vsinfo.valtype = 'D') then
736 parentindex := 0;
737 for i in reverse 1..(segcounter - 1) loop
738 if((prior_vsids(i) is not null) and
739 (vsinfo.parent_vsid = prior_vsids(i))) then
740 parentindex := i;
741 parentval := prior_vals(i);
742 exit;
743 end if;
744 end loop;
745 if(parentindex = 0) then
746 FND_MESSAGE.set_name('FND', 'FLEX-NO PARENT SEGMENT');
747 FND_MESSAGE.set_token('CHILD', to_char(segcounter));
748 l_return_code := -3;
749 GOTO goto_return;
750 end if;
751 end if;
752
753 -- Check security rules. Stop if any value is secured.
754 --
755 if((vsinfo.valtype in ('I', 'D', 'F')) and
756 (vsinfo.valsecure in ('Y', 'H')) and (segsecure = 'Y')) then
757 if(check_security(thisval, vsinfo.vsformat, parentval,
758 uappid, respid, vsinfo, TRUE) <> FF_VVALID) then
759 l_return_code := segcounter;
760 GOTO goto_return;
761 end if;
762 end if;
763
764 <<next_value>>
765
766 -- Record previous value set id's and values for parent value search
767 -- Add to table column, value_set_id, segment type, and seg_num arrays
768 --
769 prior_vsids(segcounter) := segvsid;
770 prior_vals(segcounter) := thisval;
771
772 end loop;
773
774 -- Close cursor.
775 --
776 if(fstruct.isa_key_flexfield) then
777 close KeySegC;
778 else
779 close DescSegC;
780 end if;
781
782 -- Note error if no enabled segments for this flexfield.
783 --
784 if(fstruct.isa_key_flexfield and (segcounter <= 0)) then
785 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
786 FND_MESSAGE.set_token('ROUTINE', 'Validate Values');
787 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
788 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
789 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
790 l_return_code := -1;
791 GOTO goto_return;
792 end if;
793
794 l_return_code := 0;
795
796 <<goto_return>>
797 IF (fnd_flex_server1.g_debug_level > 0) THEN
798 add_debug('END SV1.vals_secured()');
799 END IF;
800 return(l_return_code);
801
802 EXCEPTION
803 WHEN NO_DATA_FOUND then
804 IF (fnd_flex_server1.g_debug_level > 0) THEN
805 add_debug('EXCEPTION no_data_found SV1.vals_secured()');
806 END IF;
807 if(fstruct.isa_key_flexfield) then
808 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
809 FND_MESSAGE.set_token('ROUTINE', 'Validate Values');
810 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
811 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
812 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
813 return(-1);
814 end if;
815 return(0);
816 WHEN OTHERS then
817 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
818 FND_MESSAGE.set_token('MSG', 'vals_secured() exception: ' || SQLERRM);
819 IF (fnd_flex_server1.g_debug_level > 0) THEN
820 add_debug('EXCEPTION others SV1.vals_secured()');
821 END IF;
822 return(-2);
823 END vals_secured;
824
825 /* ----------------------------------------------------------------------- */
826 /* Performes independent, dependent, and table value validation. */
827 /* Returns value status number in priority order: */
828 /* VV_VALID If all values valid. No errors at all. */
829 /* VV_SECURED Only error is value security violation. */
830 /* VV_VALUES A non-security value error was found. */
831 /* VV_UNSUPPORTED Validation type not suppoted on server. */
832 /* VV_ERROR Error worse than incorrect value entered. */
833 /* */
834 /* Value validation affected by vflags. See package spec. for */
835 /* description. If no flags explicitly call for stopping validation */
836 /* this function tries to validate all segments even if there is an */
837 /* error and returns a message and the number of the first segment */
838 /* which caused the error. If there is a value error in one segment */
839 /* and a subsequent segment has a more serious error, then the error */
840 /* segment number and message is that of the more serious error. See */
841 /* value_error_name() and token() for description. This function */
842 /* carefully coordinates the setting of the error segment number with */
843 /* the behavior of the value_error functions to ensure the message */
844 /* corresponds to the segment indicated. The error segment number */
845 /* is the number of enabled segments with segment_num's <= the */
846 /* segment which caused the error. */
847 /* */
848 /* If the validation flags indicate to not stop at value errors, */
849 /* then invalid segments will return with the displayed value set */
850 /* to the value the user typed in and the id and description null. */
851 /* */
852 /* Returns the number of segment values and ids output. */
853 /* */
854 /* Outputs several arrays of cached data on each segment: */
855 /* 1) Column where to put segments in cross-validation table. */
856 /* 2) Segment data types: 'C' = Char, 'N' = Number, */
857 /* 'D' = Date "DD-MON-RR" or "DD-MON-YYYY" formats */
858 /* 't' or 'I' = Time "HH24:MI" or "HH24:MI:SS" formats */
859 /* 'T' = Date-Time in 'D' + space + 't' formats (4 in all) */
860 /* 3) Numeric representation for numbers and date type data. */
861 /* 4-6) Array of values, value_id's and value_meanings */
862 /* 7) Compiled value attribute strings for each value. */
863 /* 8) Individual segment value status codes in a char string */
864 /* where one character represents the status for each value. */
865 /* */
866 /* Notes: Checks format validation on ALL validation types. */
867 /* If format = "Numbers only" disallows non-numbers */
868 /* (present user exit allows any combination of */
869 /* 0-9, +, -, and . even if it is not a number */
870 /* ----------------------------------------------------------------------- */
871
872 FUNCTION validate_struct(fstruct IN FlexStructId,
873 tbl_apid IN NUMBER,
874 tbl_id IN NUMBER,
875 nsegs_in IN NUMBER,
876 segs IN StringArray,
877 dispsegs IN DisplayedSegs,
878 vflags IN ValueValidationFlags,
879 v_date IN DATE,
880 v_ruls IN Vrules,
881 uappid IN NUMBER,
882 respid IN NUMBER,
883 nsegs_out OUT nocopy NUMBER,
884 segfmts OUT nocopy SegFormats,
885 segstats OUT nocopy VARCHAR2,
886 tabcols OUT nocopy TabColArray,
887 tabcoltypes OUT nocopy CharArray,
888 v_dispvals OUT nocopy ValueArray,
889 v_vals OUT nocopy ValueArray,
890 v_ids OUT nocopy ValueIdArray,
891 v_descs OUT nocopy ValueDescArray,
892 desc_lens OUT nocopy NumberArray,
893 dvals OUT nocopy DerivedVals,
894 dquals OUT nocopy Qualifiers,
895 errsegn OUT nocopy NUMBER) RETURN NUMBER IS
896
897 segcount NUMBER;
898 dispsegcount NUMBER;
899 errseg_num NUMBER;
900 return_code NUMBER;
901 isvalu BOOLEAN;
902 seg_displayed BOOLEAN;
903 secured_flag BOOLEAN;
904 val_err_flag BOOLEAN;
905 this_seg VARCHAR2(1000);
906 segerrs VARCHAR2(201);
907 v_status VARCHAR2(1);
908 seg_quals Qualifiers;
909 this_dval DerivedVals;
910 drvd_quals Qualifiers;
911 drvals DerivedVals;
912 l_flexvalue FlexValue;
913 prior_dispvals ValueArray;
914 prior_vals ValueArray;
915 prior_ids ValueIdArray;
916 prior_descs ValueDescArray;
917 prior_vsids NumberArray;
918 prior_segnames SegNameArray;
919 prior_vsnames VsNameArray;
920 catdesc_lens NumberArray;
921 seginfo SegmentInfo;
922 l_str_cache_segs str_cache_storage_type;
923 i NUMBER;
924 vsinfo ValueSetInfo;
925
926 CURSOR KeyC(keystruct in FlexStructId, t_apid in NUMBER, t_id in NUMBER) IS
927 SELECT g.segment_name, g.application_column_name, c.column_type,
928 c.width, g.required_flag, g.security_enabled_flag,
929 g.concatenation_description_len,
930 g.default_type, g.default_value, g.flex_value_set_id,
931 g.runtime_property_function,
932 g.additional_where_clause
933 FROM fnd_id_flex_segments g, fnd_columns c
934 WHERE g.application_id = keystruct.application_id
935 AND g.id_flex_code = keystruct.id_flex_code
936 AND g.id_flex_num = keystruct.id_flex_num
937 AND g.enabled_flag = 'Y'
938 AND c.application_id = t_apid
939 AND c.table_id = t_id
940 AND c.column_name = g.application_column_name
941 ORDER BY g.segment_num;
942
943 CURSOR DescC(descstruct in FlexStructId, t_apid in NUMBER, t_id in NUMBER)
944 IS SELECT g.end_user_column_name, g.application_column_name,
945 c.column_type, c.width, g.required_flag,
946 g.security_enabled_flag, g.concatenation_description_len,
947 g.default_type, g.default_value, g.flex_value_set_id,
948 g.runtime_property_function,
949 NULL
950 FROM fnd_descr_flex_column_usages g, fnd_columns c
951 WHERE g.application_id = descstruct.application_id
952 AND g.descriptive_flexfield_name = descstruct.desc_flex_name
953 AND g.descriptive_flex_context_code = descstruct.desc_flex_context
954 AND g.enabled_flag = 'Y'
955 AND c.application_id = t_apid
956 AND c.table_id = t_id
957 AND c.column_name = g.application_column_name
958 ORDER BY g.column_seq_num;
959
960 BEGIN
961 IF (fnd_flex_server1.g_debug_level > 0) THEN
962 FND_FLEX_SERVER1.add_debug('BEGIN SV1.validate_struct() ');
963 END IF;
964
965 value_error_init;
966 secured_flag := FALSE;
967 val_err_flag := FALSE;
968
969 -- Initialize all returned values and all derived values with defaults
970 --
971 segcount := 0;
972 dispsegcount := 0;
973 nsegs_out := 0;
974 segfmts.nsegs := 0;
975 return_code := VV_ERROR;
976
977 drvd_quals.nquals := 0;
978 dquals.nquals := 0;
979
980 drvals.start_valid := NULL;
981 drvals.end_valid := NULL;
982 drvals.enabled_flag := 'Y';
983 drvals.summary_flag := 'N';
984 dvals := drvals;
985
986 IF (fstruct.isa_key_flexfield) THEN
987 g_cache_key := ('KFF' || '.' ||
988 fstruct.application_id || '.' ||
989 fstruct.id_flex_code || '.' ||
990 fstruct.id_flex_num || '.' ||
991 tbl_apid || '.' ||
992 tbl_id);
993 ELSE
994 g_cache_key := ('DFF' || '.' ||
995 fstruct.application_id || '.' ||
996 fstruct.desc_flex_name || '.' ||
997 fstruct.desc_flex_context || '.' ||
998 tbl_apid || '.' ||
999 tbl_id);
1000 END IF;
1001
1002 fnd_plsql_cache.custom_1tom_get_get_indexes(str_cache_controller,
1003 g_cache_key,
1004 g_cache_numof_indexes,
1005 g_cache_indexes,
1006 g_cache_return_code);
1007
1008 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1009 --
1010 -- Copy cached values to local array.
1011 --
1012 FOR ii IN 1..g_cache_numof_indexes LOOP
1013 l_str_cache_segs(ii):= str_cache_storage(g_cache_indexes(ii));
1014 END LOOP;
1015 ELSE
1016 if(fstruct.isa_key_flexfield) THEN
1017 open KeyC(fstruct, tbl_apid, tbl_id);
1018 else
1019 open DescC(fstruct, tbl_apid, tbl_id);
1020 end if;
1021
1022 i := 0;
1023 LOOP
1024 IF (fstruct.isa_key_flexfield) then
1025 FETCH KeyC INTO seginfo;
1026 EXIT WHEN (KeyC%NOTFOUND OR (KeyC%NOTFOUND is null));
1027 ELSE
1028 FETCH DescC INTO seginfo;
1029 EXIT WHEN (DescC%NOTFOUND OR (DescC%NOTFOUND is null));
1030 end if;
1031 i := i + 1;
1032
1033 l_str_cache_segs(i) := seginfo;
1034 END LOOP;
1035 g_cache_numof_indexes := i;
1036
1037 if(fstruct.isa_key_flexfield) THEN
1038 close keyc;
1039 else
1040 close descc;
1041 end if;
1042
1043 fnd_plsql_cache.custom_1tom_get_put_indexes(str_cache_controller,
1044 g_cache_key,
1045 g_cache_numof_indexes,
1046 g_cache_indexes,
1047 g_cache_return_code);
1048 IF (g_cache_return_code = fnd_plsql_cache.CACHE_PUT_IS_SUCCESSFUL) THEN
1049 FOR ii IN 1..g_cache_numof_indexes LOOP
1050 str_cache_storage(g_cache_indexes(ii)) := l_str_cache_segs(ii);
1051 END LOOP;
1052 END IF;
1053 END IF;
1054
1055 IF (fstruct.isa_key_flexfield) then
1056 IF (fnd_flex_server1.g_debug_level > 0) THEN
1057 add_debug('key segments: ');
1058 END IF;
1059 else
1060 IF (fnd_flex_server1.g_debug_level > 0) THEN
1061 add_debug('desc segments: ');
1062 END IF;
1063 end if;
1064
1065 --
1066 -- Begin main loop
1067 --
1068 segcount := 0;
1069 WHILE (segcount < g_cache_numof_indexes) LOOP
1070 segcount := segcount + 1;
1071 seginfo := l_str_cache_segs(segcount);
1072
1073 --
1074 -- Get value set information or make a virtual value set if vsid is null
1075 --
1076 if(seginfo.vsid is not null) then
1077 if(get_value_set(seginfo.vsid, seginfo.segname, vsinfo) <> TRUE) then
1078 errseg_num := segcount;
1079 segcount := segcount - 1;
1080 return_code := VV_ERROR;
1081 goto return_values;
1082 end if;
1083 else
1084 if(virtual_value_set(seginfo.coltype, seginfo.collen, seginfo.segname,
1085 vsinfo) <> TRUE) then
1086 errseg_num := segcount;
1087 segcount := segcount - 1;
1088 return_code := VV_ERROR;
1089 goto return_values;
1090 end if;
1091 end if;
1092
1093 --
1094 -- Check if segment displayed. By definition all segments are
1095 -- displayed if inputs are ID's.
1096 --
1097 if(vflags.values_not_ids) then
1098 if(check_displayed(segcount, dispsegs, seg_displayed) = FALSE) then
1099 errseg_num := segcount;
1100 segcount := segcount - 1;
1101 return_code := VV_ERROR;
1102 goto return_values;
1103 end if;
1104 else
1105 seg_displayed := TRUE;
1106 end if;
1107
1108 --
1109 -- If segment not displayed treat it as null. If it is displayed then
1110 -- increment displayed count. If displayed and exact number of segments
1111 -- not required, then treat missing segs as null.
1112 -- If nsegs_in = 0, could be that nsegs_in = 1 and that seg is null.
1113 --
1114 -- bug 1459072 values for all segs should be validated whether they are
1115 -- displayed or not. Also code was not validating right segment if a
1116 -- non-displayed segment was processed before a displayed segment.
1117
1118 --
1119 -- Above comment is not quite true (bug 1459072), I need to do
1120 -- more research on that problem. Now I am backing up the changes.
1121 -- and adding comments to following code. golgun. 01/15/01
1122 --
1123
1124 --
1125 -- If values are passed, DISPLAY mapping was used to parse concat string.
1126 -- So segs array has only displayed segment values.
1127 -- If Ids are passed, even if there is restriction in DISPLAY mapping,
1128 -- all segment values are assumed to be displayed, and segs array
1129 -- has all the segment values.
1130 --
1131 if(seg_displayed) then
1132 dispsegcount := dispsegcount + 1;
1133 if(dispsegcount > nsegs_in) THEN
1134 --
1135 -- Out of boundary.
1136 --
1137 if(vflags.exact_nsegs_required and
1138 not ((nsegs_in = 0) and (dispsegcount = 1))) then
1139 --
1140 -- Not enough segment values are passed and this is not OK.
1141 --
1142 FND_MESSAGE.set_name('FND', 'FLEX-MISSING CONCAT VALUES');
1143 errseg_num := segcount;
1144 segcount := segcount - 1;
1145 return_code := VV_ERROR;
1146 goto return_values;
1147 else
1148 --
1149 -- Not enough segment values are passed and this is OK.
1150 --
1151 this_seg := NULL;
1152 end if;
1153 else
1154 --
1155 -- seg is displayed (either (displayed 'V'), or 'I'),
1156 -- and the value is in the array.
1157 --
1158 this_seg := segs(dispsegcount);
1159 end if;
1160 else
1161 --
1162 -- seg is not displayed, only possible in 'V' case.
1163 --
1164 this_seg := NULL;
1165 end if;
1166
1167 v_status := validate_seg(this_seg,
1168 seg_displayed,
1169 seginfo,
1170 vsinfo,
1171 vflags,
1172 fstruct,
1173 v_date,
1174 v_ruls,
1175 uappid,
1176 respid,
1177 segcount - 1,
1178 prior_dispvals,
1179 prior_vals,
1180 prior_ids,
1181 prior_descs,
1182 prior_vsids,
1183 prior_segnames,
1184 prior_vsnames,
1185 l_flexvalue,
1186 seg_quals);
1187
1188 --
1189 -- Record value and structure information
1190 -- Add value validation status to segment error string.
1191 --
1192 tabcols(segcount) := seginfo.colname;
1193 tabcoltypes(segcount) := seginfo.coltype;
1194 prior_vsids(segcount) := seginfo.vsid;
1195 prior_dispvals(segcount) := l_flexvalue.displayed_value;
1196 prior_vals(segcount) := l_flexvalue.stored_value;
1197 prior_ids(segcount) := l_flexvalue.hidden_id;
1198 prior_descs(segcount) := l_flexvalue.description;
1199 prior_segnames(segcount) := seginfo.segname;
1200 prior_vsnames(segcount) := vsinfo.vsname;
1201 catdesc_lens(segcount) := seginfo.catdesclen;
1202 segfmts.vs_format(segcount) := vsinfo.vsformat;
1203 segfmts.vs_maxsize(segcount) := vsinfo.maxsize;
1204 segerrs := segerrs || v_status;
1205
1206 --
1207 -- If values ok, then derive qualifiers and enabled dates.
1208 -- If serious error validating values, or if value validation is unsupported
1209 -- on the server, then set errseg_num to current segment since this error has
1210 -- overwritten any prior value errors, and then return.
1211 -- If value is secured or other value error note the error in the local flag
1212 -- and optionally stop depending on the vflags. In these cases the value
1213 -- error message will only have been put into FND_MESSAGE if it is the
1214 -- first error, so set errseg_num only if it's null.
1215 --
1216 IF (v_status = FF_VVALID) then
1217 this_dval.start_valid := l_flexvalue.start_valid;
1218 this_dval.end_valid := l_flexvalue.end_valid;
1219 this_dval.enabled_flag := l_flexvalue.enabled_flag;
1220 this_dval.summary_flag := l_flexvalue.summary_flag;
1221 if(derive_values(this_dval, seg_quals, drvals,drvd_quals) = FALSE) then
1222 return_code := VV_ERROR;
1223 goto return_values;
1224 end if;
1225 elsif(v_status = FF_VERROR) then
1226 errseg_num := segcount;
1227 return_code := VV_ERROR;
1228 goto return_values;
1229 elsif(v_status = FF_VUNKNOWN) then
1230 errseg_num := segcount;
1231 return_code := VV_UNSUPPORTED;
1232 goto return_values;
1233 elsif(v_status = FF_VSECURED) then
1234 secured_flag := TRUE;
1235 if((errseg_num is null) and vflags.message_on_security) then
1236 errseg_num := segcount;
1237 end if;
1238 if(vflags.stop_on_security) then
1239 return_code := VV_SECURED;
1240 goto return_values;
1241 end if;
1242 else
1243 val_err_flag := TRUE;
1244 if(errseg_num is null) then
1245 if((v_status <> FF_VREQUIRED) or (vflags.message_on_null)) then
1246 errseg_num := segcount;
1247 end if;
1248 end if;
1249 if(vflags.stop_on_value_error) then
1250 return_code := VV_VALUES;
1251 goto return_values;
1252 end if;
1253 end if;
1254
1255 end loop;
1256
1257 if(fstruct.isa_key_flexfield) then
1258 if(segcount <= 0) then
1259 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
1260 FND_MESSAGE.set_token('ROUTINE', 'Validate Values');
1261 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
1262 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
1263 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
1264 return_code := VV_ERROR;
1265 goto return_values;
1266 end if;
1267 ELSE
1268 NULL;
1269 end if;
1270
1271 --
1272 -- If exact number of segments required, then check for too many.
1273 -- Otherwise just stop.
1274 -- If nsegs_in = 0, could be that nsegs_in = 1 and that seg is null.
1275 -- Do not complain if dispsegcount is 1 in that case.
1276 --
1277 if(vflags.exact_nsegs_required and (dispsegcount <> nsegs_in) and
1278 not ((nsegs_in = 0) and (dispsegcount = 1))) then
1279 FND_MESSAGE.set_name('FND', 'FLEX-TOO MANY SEGS');
1280 FND_MESSAGE.set_token('NSEGS', to_char(dispsegcount));
1281 return_code := VV_ERROR;
1282 goto return_values;
1283 end if;
1284
1285 IF (fnd_flex_server1.g_debug_level > 0) THEN
1286 add_debug(to_char(segcount) || ' segments found.');
1287 END IF;
1288
1289 --
1290 -- Return most serious error or none if there are none.
1291 --
1292 if(val_err_flag) then
1293 return_code := VV_VALUES;
1294 elsif(secured_flag) then
1295 return_code := VV_SECURED;
1296 else
1297 return_code := VV_VALID;
1298 end if;
1299
1300 <<return_values>>
1301 --
1302 -- Return values, derived values, qualifiers, segment format.
1303 --
1304 nsegs_out := segcount;
1305 segfmts.nsegs := segcount;
1306 segstats := segerrs;
1307 v_dispvals := prior_dispvals;
1308 v_vals := prior_vals;
1309 v_ids := prior_ids;
1310 v_descs := prior_descs;
1311 desc_lens := catdesc_lens;
1312 dvals := drvals;
1313 dquals := drvd_quals;
1314 errsegn := errseg_num;
1315
1316 IF (fnd_flex_server1.g_debug_level > 0) THEN
1317 FND_FLEX_SERVER1.add_debug('END SV1.validate_struct()');
1318 END IF;
1319
1320 return(return_code);
1321
1322 EXCEPTION
1323 WHEN NO_DATA_FOUND then
1324 IF (fnd_flex_server1.g_debug_level > 0) THEN
1325 FND_FLEX_SERVER1.add_debug('EXCEPTION no_data_found SV1.validate_struct() ');
1326 END IF;
1327 if(fstruct.isa_key_flexfield) then
1328 FND_MESSAGE.set_name('FND', 'FLEX-CANT FIND SEGMENTS');
1329 FND_MESSAGE.set_token('ROUTINE', 'Validate Values');
1330 FND_MESSAGE.set_token('APPID', to_char(fstruct.application_id));
1331 FND_MESSAGE.set_token('CODE', fstruct.id_flex_code);
1332 FND_MESSAGE.set_token('NUM', to_char(fstruct.id_flex_num));
1333 return(VV_ERROR);
1334 end if;
1335 return(VV_VALID);
1336 WHEN OTHERS then
1337 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1338 FND_MESSAGE.set_token('MSG', 'validate_struct() exception: '||SQLERRM);
1339 IF (fnd_flex_server1.g_debug_level > 0) THEN
1340 FND_FLEX_SERVER1.add_debug('EXCEPTION others SV1.validate_struct() ');
1341 END IF;
1342
1343 return(VV_ERROR);
1344
1345 END validate_struct;
1346
1347 /* ----------------------------------------------------------------------- */
1348 /* Validates value returing status code indicating if the segment is */
1349 /* valid or if what if any errors are present. Sets error message */
1350 /* using error prioritization functions if any errors in user-entered */
1351 /* values. If value invalid because of a value error rather than */
1352 /* an error in the data structures etc, then this program tries to */
1353 /* return as much of the value as possible. If value cannot be */
1354 /* found, this program returns what the user typed in for the value */
1355 /* and null ids and descrs. If the value is found, the whole value */
1356 /* is returned even if there is some other validation problem such */
1357 /* as a security violation or value is expired or disabled. */
1358 /* Outputs actual value, format of segment, and segment qualifiers. */
1359 /* ----------------------------------------------------------------------- */
1360
1361 FUNCTION validate_seg(seg_in IN VARCHAR2,
1362 displayed IN BOOLEAN,
1363 kseg IN SegmentInfo,
1364 vsinf IN ValueSetInfo,
1365 vflags IN ValueValidationFlags,
1366 fstruct IN FlexStructId,
1367 v_date IN DATE,
1368 v_ruls IN Vrules,
1369 uappid IN NUMBER,
1370 respid IN NUMBER,
1371 nprev IN NUMBER,
1372 prev_dispvals IN ValueArray,
1373 prev_vals IN ValueArray,
1374 prev_ids IN ValueIdArray,
1375 prev_descs IN ValueDescArray,
1376 prev_vsids IN NumberArray,
1377 prev_segnames IN SegNameArray,
1378 prev_vsnames IN VsNameArray,
1379 x_flexvalue OUT nocopy FlexValue,
1380 squals OUT nocopy Qualifiers)
1381 RETURN VARCHAR2
1382 IS
1383 isvalu BOOLEAN;
1384 defaulted BOOLEAN;
1385 orphansok BOOLEAN;
1386 vcode VARCHAR2(1);
1387 thisseg VARCHAR2(1000);
1388 parentval VARCHAR2(150);
1389 segquals Qualifiers;
1390 l_flexvalue FlexValue;
1391 l_fvc_code VARCHAR2(100);
1392 l_parent_index BINARY_INTEGER;
1393 l_storage_value VARCHAR2(2000);
1394 l_display_value VARCHAR2(2000);
1395 BEGIN
1396 IF (fnd_flex_server1.g_debug_level > 0) THEN
1397 FND_FLEX_SERVER1.add_debug('BEGIN SV1.validate_seg() ');
1398 END IF;
1399
1400 --
1401 -- Initialize all returned values
1402 --
1403 vcode := FF_VERROR;
1404 segquals.nquals := 0;
1405 l_flexvalue.enabled_flag := 'Y';
1406 l_flexvalue.summary_flag := 'N';
1407
1408 isvalu := vflags.values_not_ids;
1409
1410 -- Initialize value. Strip spaces if user entered value rather than ID
1411 --
1412 IF (isvalu) then
1413 thisseg := RTRIM(LTRIM(seg_in));
1414 l_flexvalue.displayed_value := thisseg;
1415 l_flexvalue.stored_value := thisseg;
1416 else
1417 thisseg := seg_in;
1418 l_flexvalue.hidden_id := thisseg;
1419 end if;
1420
1421 -- Get all flexfield and segment qualifiers enabled for this segment.
1422 --
1423 IF (get_qualifiers(fstruct, kseg.colname, segquals) < 0) then
1424 vcode := FF_VERROR;
1425 goto return_val;
1426 end if;
1427
1428 -- Add segment qualifier list to debug string
1429 --
1430 IF (fnd_flex_server1.g_debug_level > 0) THEN
1431 if(segquals.nquals > 0) then
1432 add_debug('DefaultQuals=');
1433 for i in 1..segquals.nquals loop
1434 add_debug('(' || segquals.fq_names(i) || ', ' ||
1435 segquals.sq_names(i) || ', ' ||
1436 segquals.sq_values(i) || ', ' ||
1437 segquals.derived_cols(i) || ')');
1438 end loop;
1439 end if;
1440 END IF;
1441
1442 -- Attempt to default null values.
1443 -- Do not default if id or if value not null.
1444 -- Default only displayed value.
1445 -- Bug 1539718 added check for vflags.allow_nulls
1446 -- Bug 2221725 Need to default id if invoking_mode = 'D' and seg is null.
1447
1448 if vflags.invoking_mode = 'D' then
1449 if thisseg is null then
1450 isvalu := TRUE;
1451 else
1452 isvalu := FALSE; -- needed in case previous seg was null.
1453 end if;
1454 end if;
1455
1456 defaulted := FALSE;
1457
1458 --Bug 6074421, Modified IF statment to not override null value with
1459 --defalut value, if segment is not required. Added (kseg.required = 'Y')
1460 --Bug 7028502, Modified IF statment to allow non-req segments to default
1461 --if the invoking_mode = 'D'.
1462
1463 IF (((isvalu) and (thisseg is null) and
1464 (kseg.required = 'Y' or vflags.invoking_mode = 'D')) and
1465 ((displayed and vflags.default_all_displayed and not vflags.allow_nulls) or
1466 ((kseg.required = 'Y') and vflags.default_all_required) or
1467 ((kseg.required = 'Y') and (not displayed) and
1468 vflags.default_non_displayed))) then
1469 vcode := default_val(kseg.dflt_type, kseg.dflt_val, vsinf.vsformat,
1470 vsinf.maxsize, vsinf.precis, vsinf.lettersok,
1471 kseg.segname, nprev,
1472 prev_dispvals, prev_vals, prev_ids, prev_descs,
1473 prev_segnames, prev_vsnames, thisseg);
1474 defaulted := TRUE;
1475 if(vcode <> FF_VVALID) then
1476
1477 -- Bug 2778034-Flexfield is set to use defaulting from a form block
1478 -- and field, but in processing batch jobs this call is made to do
1479 -- defaulting, and the form block and field are not available, so
1480 -- ignore the error and set the field to NULL. This is only
1481 -- implementedfor invoking_mode 'D', which is for defaulting IDs.
1482 -- We are ONLY handling this case because we can be assured that
1483 -- the source is a batch job, and the :block.field cannot be
1484 -- expected to be available. We will NOT change this for any
1485 -- other invoking mode since we cannot be sure of the source.
1486 --
1487
1488 if ((vcode = FF_VUNKNOWN) and (vflags.invoking_mode = 'D')) then
1489 thisseg := NULL;
1490 vcode := FF_VVALID;
1491 IF (fnd_flex_server1.g_debug_level > 0) THEN
1492 add_debug('Segment NOT defaulted because of :block.field bind '
1493 || to_char(nprev + 1));
1494 END IF;
1495 else
1496 goto return_val;
1497 end if;
1498
1499 end if;
1500 l_flexvalue.displayed_value := thisseg;
1501 IF (fnd_flex_server1.g_debug_level > 0) THEN
1502 add_debug('Defaulted segment ' || to_char(nprev + 1));
1503 add_debug(' to ' || thisseg);
1504 END IF;
1505 end if;
1506
1507 -- Check for nulls. If null and this is OK then set values, ids and
1508 -- description to null and move on.
1509 --
1510 -- Bug 1879889 - added check for DFF null dependent segment
1511 -- We need to check the parent segment before issuing error.
1512 -- If parent seg is null then child can be null also.
1513 -- Bug 5560451 The same is true for Key Flexfields. If a parent
1514 -- is not required and is NULL, then dependent seg does not need value
1515 -- even if it is required. Removing the statment
1516 -- fstruct.isa_key_flexfield = FALSE
1517
1518 -- Bug 13394636 - when a required segment is queried and the data is a null
1519 -- value for that segment, the client and server validation code differ in
1520 -- behavior. The client code gives no error, however the server code does.
1521 -- We are syncing the behaviour in favor of the client code by adding the
1522 -- check for invoking_mode = 'L'
1523
1524
1525 if(thisseg is null) then
1526 if vsinf.parent_vsid is not NULL and vsinf.valtype = 'D'
1527 then
1528 NULL; -- we will check again for DFF nulls after getting parent.
1529 else
1530 if((kseg.required = 'N') or (vflags.allow_nulls) or (vflags.invoking_mode = 'L'))
1531 then
1532 vcode := FF_VVALID;
1533 goto return_val;
1534 else
1535 vcode := FF_VREQUIRED;
1536 if(vflags.message_on_null) then
1537 value_error_name('FND', 'FLEX-NULL REQUIRED SEGMENT');
1538 value_error_token('SEGMENT_NAME', kseg.segname);
1539 /* Bug 9006077 value_error_name('FND', 'FLEX-NULL SEGMENT');*/
1540 end if;
1541 goto return_val;
1542 end if;
1543 end if;
1544 end if;
1545
1546 -- Coerces user input from being a rough approximation of the displayed
1547 -- value format, to the value storage format.
1548 -- Also checks length and that value is in min-max range.
1549 -- Side effect: change thisseg to stored format appropriate to the value set.
1550 -- Added "and thisseg is not null" in conjuction with changes for bug 1879889
1551 -- as it is now possible to be here with a null segment.
1552
1553 -- Validate None validated value sets too.
1554 IF ((isvalu) OR
1555 (vsinf.valtype = 'N')) and thisseg is not null then
1556 vcode := coerce_format(thisseg, isvalu, vsinf.vsformat, vsinf.vsname,
1557 vsinf.maxsize, vsinf.lettersok, vsinf.capsonly,
1558 vsinf.zfill, vsinf.precis, vsinf.minval,
1559 vsinf.maxval,l_storage_value, l_display_value);
1560 thisseg := l_storage_value;
1561 if(vcode <> FF_VVALID) then
1562 goto return_val;
1563 end if;
1564 end if;
1565
1566 -- Now look up value
1567
1568 -- GL RELIES ON THE FOLLOWING STRANGE BEHAVIOR:
1569 -- To be compatible with client, orphans are ok if the child
1570 -- value was defaulted and nulls are allowed.
1571 --
1572 orphansok := (vflags.all_orphans_valid or
1573 (vflags.allow_nulls and defaulted));
1574
1575 -- ==
1576 --
1577 -- Find parent value: took out from find_value.
1578 --
1579 -- ==
1580 l_fvc_code := fnd_plsql_cache.CACHE_NOTFOUND;
1581 IF (vsinf.valtype IN ('I','D','X','Y')) THEN
1582 IF (isvalu) then
1583 l_flexvalue.displayed_value := thisseg;
1584 l_flexvalue.stored_value := thisseg;
1585 else
1586 l_flexvalue.hidden_id := thisseg;
1587 end if;
1588 l_flexvalue.enabled_flag := 'Y';
1589 l_flexvalue.summary_flag := 'N';
1590 l_flexvalue.format := vsinf.vsformat;
1591
1592 parentval := NULL;
1593
1594 IF (vsinf.valtype in ('D','Y')) then
1595 IF (fnd_flex_server1.g_debug_level > 0) THEN
1596 add_debug(vsinf.valtype);
1597 for i in 1..nprev loop
1598 add_debug(to_char(prev_vsids(i)) || '.');
1599 end loop;
1600 END IF;
1601
1602 --
1603 -- Find index to parent value set
1604 l_parent_index := 0;
1605 for i in reverse 1..nprev loop
1606 IF ((prev_vsids(i) is not null) and
1607 (vsinf.parent_vsid = prev_vsids(i))) then
1608 l_parent_index := i;
1609 IF (vsinf.valtype = 'D') THEN
1610 parentval := prev_vals(i);
1611 ELSE
1612 parentval := prev_ids(i);
1613 END IF;
1614 exit;
1615 end if;
1616 end loop;
1617
1618 --
1619 -- Bug 1879889
1620 -- Check for DFF null dependent segment now that we have parent value.
1621 -- same if condition as used above to insure that we only process
1622 -- DFF dependent null segments.
1623 -- Bug 2933236
1624 -- Check dependent segment required flag. If a dependent segment
1625 -- has a parent, but is not is not required, then it is not
1626 -- requried to have a value. It only must have a value if the
1627 -- segment is required.
1628 -- Bug 5560451 The same is true for Key Flexfields.
1629 -- Removing the statment fstruct.isa_key_flexfield = FALSE
1630 -- Bug 8579560
1631 -- Do additional check whether allow_nulls is set to FALSE before
1632 -- issuing error
1633 -- Note-Dual checkin was broken for 12.1 at time of last arcs in
1634 if thisseg is null and vsinf.parent_vsid is not NULL
1635 and vsinf.valtype = 'D' then
1636 if parentval is null then
1637 vcode := FF_VVALID;
1638 else
1639 if ((kseg.required = 'Y') AND (not vflags.allow_nulls))then /* Bug 2933236 */
1640 vcode := FF_VREQUIRED;
1641 if(vflags.message_on_null) then
1642 value_error_name('FND', 'FLEX-NULL REQUIRED SEGMENT');
1643 value_error_token('SEGMENT_NAME', kseg.segname);
1644 /* Bug 9006077 value_error_name('FND', 'FLEX-NULL SEGMENT');*/
1645 end if;
1646 else
1647 vcode := FF_VVALID;
1648 end if;
1649 end if;
1650 goto return_val;
1651 end if;
1652
1653 IF (l_parent_index = 0) then
1654 FND_MESSAGE.set_name('FND', 'FLEX-NO PARENT SEGMENT');
1655 FND_MESSAGE.set_token('CHILD', kseg.segname);
1656 vcode := FF_VERROR;
1657 goto return_val;
1658 end if;
1659
1660 IF ((parentval is null) and (not orphansok))then
1661 --
1662 -- Use FLEX-ORPHAN but need segment names of previous segments
1663 --
1664 value_error_name('FND', 'FLEX-ORPHAN');
1665 value_error_token('PARENT', prev_segnames(l_parent_index));
1666 value_error_token('SEGMENT', kseg.segname);
1667 vcode := FF_VORPHAN;
1668 goto return_val;
1669 end if;
1670 else
1671 IF (fnd_flex_server1.g_debug_level > 0) THEN
1672 add_debug(vsinf.valtype);
1673 END IF;
1674 end if;
1675
1676 IF (kseg.additional_where_clause IS NULL) THEN
1677 IF (vsinf.valtype IN ('I', 'D')) THEN
1678 l_fvc_code := check_fvc(vsinf.vsid,
1679 parentval,
1680 thisseg,
1681 l_flexvalue);
1682 END IF;
1683 END IF;
1684 END IF;
1685
1686 IF (l_fvc_code IN (fnd_plsql_cache.CACHE_FOUND)) THEN
1687 vcode := FF_VVALID;
1688 ELSE
1689 vcode := find_value(fstruct, kseg, vsinf, v_date,
1690 thisseg,
1691 isvalu, orphansok,
1692 kseg.segname, nprev, prev_vsids,
1693 prev_dispvals, prev_vals, prev_ids, prev_descs,
1694 prev_segnames, prev_vsnames, parentval, l_flexvalue);
1695
1696 --
1697 -- If value exists then cache it.
1698 --
1699 IF (kseg.additional_where_clause IS NULL) THEN
1700 IF ((vcode = FF_VVALID) AND
1701 (vsinf.valtype IN ('I', 'D'))) THEN
1702 update_fvc(vsinf.vsid, parentval, thisseg, l_flexvalue);
1703 END IF;
1704 END IF;
1705 END IF;
1706
1707 IF (vcode <> FF_VVALID) then
1708 goto return_val;
1709 end if;
1710
1711 -- Determine qualifier values for this segment from
1712 -- compiled_value_attributes (This function replaces the sq_values
1713 -- which differ from the defaults)
1714 if(l_flexvalue.compiled_attributes is not null) then
1715 if(qualifier_values(fstruct, vsinf.vsid, l_flexvalue.compiled_attributes,
1716 segquals.nquals, segquals.fq_names,
1717 segquals.sq_names,
1718 segquals.sq_values) < 0) then
1719 vcode := FF_VERROR;
1720 goto return_val;
1721 end if;
1722 end if;
1723
1724 -- Add modified segment qualifier values to debug string
1725 --
1726 IF (fnd_flex_server1.g_debug_level > 0) THEN
1727 if(segquals.nquals > 0) THEN
1728 g_debug_text := 'SegQualVals=';
1729 for i in 1..segquals.nquals loop
1730 g_debug_text := g_debug_text || '(' || segquals.sq_values(i) || ')';
1731 end loop;
1732 add_debug(g_debug_text);
1733 end if;
1734 END IF;
1735
1736 -- Always check vrules. They should not be checked in LOADID(), but
1737 -- the client does it anyway.
1738 --
1739 /* bug872437. Don't check vrules in loadid. */
1740
1741 IF ((vflags.invoking_mode <> 'L') AND
1742 (vflags.invoking_mode <> 'G')) THEN
1743 vcode := check_vrules(v_ruls, segquals, l_flexvalue.summary_flag);
1744 if(vcode <> FF_VVALID) then
1745 goto return_val;
1746 end if;
1747 END IF;
1748
1749 -- Check security rules
1750 if(NOT vflags.ignore_security) then
1751 if((vsinf.valtype in ('I', 'D', 'F')) and
1752 (vsinf.valsecure in ('Y', 'H')) and (kseg.segsecure = 'Y')) THEN
1753 IF (fnd_flex_server1.g_debug_level > 0) THEN
1754 add_debug('Calling Security');
1755 END IF;
1756 vcode := check_security(l_flexvalue.stored_value, vsinf.vsformat,
1757 parentval, uappid, respid, vsinf,
1758 vflags.message_on_security);
1759 IF (vcode <> FF_VVALID) THEN
1760 IF (fnd_flex_server1.g_debug_level > 0) THEN
1761 add_debug('Security Failure Code: ' || vcode);
1762 END IF;
1763 goto return_val;
1764 end if;
1765 IF (fnd_flex_server1.g_debug_level > 0) THEN
1766 add_debug('NOT SECURED');
1767 END IF;
1768 end if;
1769 end if;
1770
1771 -- Check to make sure value is enabled for the given validation date.
1772 --
1773 if(NOT vflags.ignore_disabled) then
1774 if(l_flexvalue.enabled_flag <> 'Y') then
1775 value_error_name('FND', 'FLEX-VALUE IS DISABLED');
1776 value_error_token('VALUE', l_flexvalue.displayed_value);
1777 vcode := FF_VDISABLED;
1778 goto return_val;
1779 end if;
1780 end if;
1781
1782 if((NOT vflags.ignore_expired) and (v_date is not null)) then
1783 if((Trunc(v_date) < Trunc(nvl(l_flexvalue.start_valid, v_date))) or
1784 (Trunc(v_date) > Trunc(nvl(l_flexvalue.end_valid, v_date)))) then
1785 value_error_name('FND', 'FLEX-VALUE IS EXPIRED');
1786 value_error_token('VALUE', l_flexvalue.displayed_value);
1787 vcode := FF_VEXPIRED;
1788 goto return_val;
1789 end if;
1790 end if;
1791
1792 <<return_val>>
1793 x_flexvalue := l_flexvalue;
1794 squals := segquals;
1795 IF (fnd_flex_server1.g_debug_level > 0) THEN
1796 FND_FLEX_SERVER1.add_debug('END SV1.validate_seg() ');
1797 END IF;
1798
1799 return(vcode);
1800
1801 EXCEPTION
1802 WHEN OTHERS then
1803 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1804 FND_MESSAGE.set_token('MSG', 'validate_seg() exception: ' || SQLERRM);
1805 IF (fnd_flex_server1.g_debug_level > 0) THEN
1806 FND_FLEX_SERVER1.add_debug('EXCEPTION others SV1.validate_seg() ');
1807 END IF;
1808 return(FF_VERROR);
1809 END validate_seg;
1810
1811 /* ----------------------------------------------------------------------- */
1812 /* Given the dvals and qualifiers for the current segment and the */
1813 /* existing derived dvals and qualifiers, compute the new dvals and */
1814 /* qualifiers. Presently only handles one set of derivation rules. */
1815 /* Returns TRUE if OK or FALSE and sets error message if errors. */
1816 /* ----------------------------------------------------------------------- */
1817
1818 FUNCTION derive_values(new_dvals IN DerivedVals,
1819 new_quals IN Qualifiers,
1820 drv_dvals IN OUT nocopy DerivedVals,
1821 drv_quals IN OUT nocopy Qualifiers) RETURN BOOLEAN IS
1822
1823 dqi BINARY_INTEGER;
1824
1825 BEGIN
1826
1827 -- Derive values for the enabled parameters and summary flag
1828 --
1829 if((new_dvals.start_valid is not null) and
1830 ((drv_dvals.start_valid is null) or
1831 (new_dvals.start_valid > drv_dvals.start_valid))) then
1832 drv_dvals.start_valid := new_dvals.start_valid;
1833 end if;
1834 if((new_dvals.end_valid is not null) and
1835 ((drv_dvals.end_valid is null) or
1836 (new_dvals.end_valid < drv_dvals.end_valid))) then
1837 drv_dvals.end_valid := new_dvals.end_valid;
1838 end if;
1839 if(new_dvals.enabled_flag = 'N') then
1840 drv_dvals.enabled_flag := 'N';
1841 end if;
1842 if(new_dvals.summary_flag = 'Y') then
1843 drv_dvals.summary_flag := 'Y';
1844 end if;
1845
1846 -- Derive the qualifiers.
1847 -- Add each segment qualifier to the accumulated derived qualifiers.
1848 -- If it's a new qualifier add it to the array of derived qualifier names.
1849 -- Otherwise, compute the derived value using the Derivation rules:
1850 -- "If any segment qualifier value is 'N' the derived value is 'N'
1851 --
1852 -- Algorithm: For each seg qual, find dqi = index to derived qual with
1853 -- the same fq and sq names. If found, derive the value using the new seg
1854 -- qualifier value. If not found, add a new qualifier to the list of
1855 -- derived qualifiers.
1856 --
1857 for i in 1..new_quals.nquals loop
1858 dqi := NULL;
1859 for j in 1..drv_quals.nquals loop
1860 if((drv_quals.fq_names(j) = new_quals.fq_names(i)) and
1861 (drv_quals.sq_names(j) = new_quals.sq_names(i))) then
1862 dqi := j;
1863 exit;
1864 end if;
1865 end loop;
1866 if(dqi is not null) then
1867 if(new_quals.sq_values(i) = 'N') then
1868 drv_quals.sq_values(dqi) := 'N';
1869 end if;
1870 else
1871 drv_quals.nquals := drv_quals.nquals + 1;
1872 dqi := drv_quals.nquals;
1873 drv_quals.fq_names(dqi) := new_quals.fq_names(i);
1874 drv_quals.sq_names(dqi) := new_quals.sq_names(i);
1875 drv_quals.sq_values(dqi) := new_quals.sq_values(i);
1876 drv_quals.derived_cols(dqi) := new_quals.derived_cols(i);
1877 end if;
1878 end loop;
1879
1880 return(TRUE);
1881
1882 EXCEPTION
1883 WHEN OTHERS then
1884 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1885 FND_MESSAGE.set_token('MSG', 'derive_values() exception: ' || SQLERRM);
1886 return(FALSE);
1887
1888 END derive_values;
1889
1890 /* ----------------------------------------------------------------------- */
1891 /* Coerces entered segment value into the value storage format */
1892 /* appropriate for the value set, */
1893 /* and checks to make sure value is not too long and is within the */
1894 /* the min-max range specified in the value set. */
1895 /* */
1896 /* Returns the value in stored format and TRUE on success or sets */
1897 /* error and returns FALSE on error. */
1898 /* ----------------------------------------------------------------------- */
1899
1900 FUNCTION coerce_format(user_value IN VARCHAR2,
1901 p_is_displayed IN BOOLEAN,
1902 vs_format IN VARCHAR2,
1903 vs_name IN VARCHAR2,
1904 max_length IN NUMBER,
1905 letters_ok IN VARCHAR2,
1906 caps_only IN VARCHAR2,
1907 zero_fill IN VARCHAR2,
1908 precision IN VARCHAR2,
1909 min_value IN VARCHAR2,
1910 max_value IN VARCHAR2,
1911 x_storage_value OUT nocopy VARCHAR2,
1912 x_display_value OUT nocopy VARCHAR2) RETURN VARCHAR2 IS
1913
1914 l_return VARCHAR2(1);
1915 l_success NUMBER;
1916 l_storage_value VARCHAR2(2000);
1917 l_display_value VARCHAR2(2000);
1918 l_utv_enc_message VARCHAR2(32000) := NULL;
1919 l_ssv_enc_message VARCHAR2(32000) := NULL;
1920 BEGIN
1921 IF (fnd_flex_server1.g_debug_level > 0) THEN
1922 FND_FLEX_SERVER1.add_debug('BEGIN SV1.coerce_format() ');
1923 END IF;
1924
1925 x_storage_value := user_value;
1926 x_display_value := user_value;
1927
1928 --
1929 -- Since validation utility is setting messages by using
1930 -- fnd_message, we will do a stacking trick here.
1931 --
1932 -- For different result, different style of messaging is used.
1933 -- If the error is serious, always overwrite the existing message.
1934 --
1935 IF (value_error_set) THEN
1936 l_ssv_enc_message := fnd_message.get_encoded;
1937 END IF;
1938
1939 fnd_flex_val_util.validate_value_ssv
1940 (p_value => user_value,
1941 p_is_displayed => p_is_displayed,
1942 p_vset_name => vs_name,
1943 p_vset_format => vs_format,
1944 p_max_length => max_length,
1945 p_precision => To_number(precision),
1946 p_alpha_allowed => letters_ok,
1947 p_uppercase_only => caps_only,
1948 p_zero_fill => zero_fill,
1949 p_min_value => min_value,
1950 p_max_value => max_value,
1951 x_storage_value => l_storage_value,
1952 x_display_value => l_display_value,
1953 x_success => l_success);
1954
1955 IF (fnd_flex_server1.g_debug_level > 0) THEN
1956 add_debug(Rtrim(Substr(fnd_flex_val_util.get_debug,1,2000), chr_newline));
1957 END IF;
1958 IF (l_success = fnd_flex_val_util.g_ret_no_error) THEN
1959 --
1960 -- No error case.
1961 --
1962 l_return := FF_VVALID;
1963 x_storage_value := l_storage_value;
1964 x_display_value := l_display_value;
1965 IF (value_error_set) THEN
1966 fnd_message.set_encoded(l_ssv_enc_message);
1967 END IF;
1968 ELSE
1969 --
1970 -- Since this is an error case, get the message.
1971 --
1972 l_utv_enc_message := fnd_message.get_encoded;
1973
1974 IF ((l_success = fnd_flex_val_util.g_ret_value_too_long) OR
1975 (l_success = fnd_flex_val_util.g_ret_invalid_number) OR
1976 (l_success = fnd_flex_val_util.g_ret_invalid_date)) THEN
1977 --
1978 -- Format Errors.
1979 --
1980 l_return := FF_VFORMAT;
1981 IF (value_error_set) THEN
1982 fnd_message.set_encoded(l_ssv_enc_message);
1983 ELSE
1984 fnd_message.set_encoded(l_utv_enc_message);
1985 END IF;
1986 ELSIF ((l_success = fnd_flex_val_util.g_ret_vs_bad_precision) OR
1987 (l_success = fnd_flex_val_util.g_ret_vs_bad_format) OR
1988 (l_success = fnd_flex_val_util.g_ret_vs_bad_numrange) OR
1989 (l_success = fnd_flex_val_util.g_ret_vs_bad_daterange) OR
1990 (l_success = fnd_flex_val_util.g_ret_vs_bad_date) OR
1991 (l_success = fnd_flex_val_util.g_ret_exception_others)) THEN
1992 --
1993 -- Serious errors.
1994 --
1995 l_return := FF_VERROR;
1996 fnd_message.set_encoded(l_utv_enc_message);
1997
1998 ELSIF (l_success = fnd_flex_val_util.g_ret_val_out_of_range) THEN
1999 --
2000 -- Bound Check Error.
2001 --
2002 l_return := FF_VBOUNDS;
2003 IF (value_error_set) THEN
2004 fnd_message.set_encoded(l_ssv_enc_message);
2005 ELSE
2006 fnd_message.set_encoded(l_utv_enc_message);
2007 END IF;
2008 ELSE
2009 --
2010 -- Other errors. This part is added, in case there will be changes
2011 -- in UTV package. As of 26-APR-99 code should not enter here.
2012 --
2013 l_return := FF_VERROR;
2014 fnd_message.set_encoded(l_utv_enc_message);
2015 END IF;
2016 --
2017 -- We are in error case, set messaging globals.
2018 --
2019 value_error_set := TRUE;
2020 entering_new_message := FALSE;
2021 END IF;
2022
2023 IF (fnd_flex_server1.g_debug_level > 0) THEN
2024 FND_FLEX_SERVER1.add_debug('END SV1.coerce_format() ');
2025 END IF;
2026
2027 RETURN(l_return);
2028
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 IF (fnd_flex_server1.g_debug_level > 0) THEN
2032 FND_FLEX_SERVER1.add_debug('EXCEPTION SV1.coerce_format() ');
2033 END IF;
2034
2035 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2036 FND_MESSAGE.set_token('MSG', 'coerce_format() exception: ' || SQLERRM);
2037 return(FF_VERROR);
2038 END coerce_format;
2039
2040 /* ----------------------------------------------------------------------- */
2041 /* Looks up value in appropriate validation tables. Input the */
2042 /* character representation of the stored value or the id. Returns */
2043 /* all information about the value in the FlexValue structure. Also */
2044 /* returns the parent value in stored format if there is a parent. */
2045 /* This function needs to know about all previous segments to */
2046 /* handle dependent and table validated value sets. */
2047 /* Returns TRUE if value is found or FALSE and sets error if not. */
2048 /* ----------------------------------------------------------------------- */
2049
2050 FUNCTION find_value(p_str_info IN FlexStructId,
2051 p_seg_info IN SegmentInfo,
2052 p_vs_info IN ValueSetInfo,
2053 p_vdate IN DATE,
2054 p_char_val IN VARCHAR2,
2055 p_is_value IN BOOLEAN,
2056 p_orphans_ok IN BOOLEAN,
2057 p_this_segname IN VARCHAR2,
2058 p_n_prev IN NUMBER,
2059 p_prev_vsids IN NumberArray,
2060 p_prev_dispvals IN ValueArray,
2061 p_prev_vals IN ValueArray,
2062 p_prev_ids IN ValueIdArray,
2063 p_prev_descs IN ValueDescArray,
2064 p_prev_segnames IN SegNameArray,
2065 p_prev_vsnames IN VsNameArray,
2066 p_parent_val IN VARCHAR2,
2067 x_this_val_out OUT nocopy FlexValue)
2068 RETURN VARCHAR2
2069 IS
2070 v_code VARCHAR2(1);
2071 l_is_value VARCHAR2(1);
2072 check_value_table BOOLEAN;
2073 this_value FlexValue;
2074
2075 CURSOR IND_cursor(vsid IN NUMBER, val IN VARCHAR2) IS
2076 SELECT enabled_flag, start_date_active, end_date_active,
2077 summary_flag, compiled_value_attributes, description
2078 FROM fnd_flex_values_vl
2079 WHERE flex_value_set_id = vsid
2080 AND flex_value = val;
2081
2082 CURSOR DEP_cursor(vsid IN NUMBER, val IN VARCHAR2, parnt IN VARCHAR2) IS
2083 SELECT enabled_flag, start_date_active, end_date_active,
2084 summary_flag, compiled_value_attributes, description
2085 FROM fnd_flex_values_vl
2086 WHERE flex_value_set_id = vsid
2087 AND flex_value = val
2088 AND parent_flex_value_low = parnt;
2089
2090 CURSOR INDTL_cursor(vsid IN NUMBER,
2091 val IN VARCHAR2,
2092 p_is_value IN VARCHAR2) IS
2093 SELECT enabled_flag, start_date_active, end_date_active,
2094 summary_flag, compiled_value_attributes,
2095 flex_value, flex_value_meaning, description
2096 FROM fnd_flex_values_vl
2097 WHERE flex_value_set_id = vsid
2098 AND (((p_is_value = 'V') AND (flex_value_meaning = val)) OR
2099 ((p_is_value = 'I') AND (flex_value = val)));
2100
2101 CURSOR DEPTL_cursor(vsid IN NUMBER,
2102 val IN VARCHAR2,
2103 parnt IN VARCHAR2,
2104 p_is_value IN VARCHAR2) IS
2105 SELECT enabled_flag, start_date_active, end_date_active,
2106 summary_flag, compiled_value_attributes,
2107 flex_value, flex_value_meaning, description
2108 FROM fnd_flex_values_vl
2109 WHERE flex_value_set_id = vsid
2110 AND parent_flex_value_low = parnt
2111 AND (((p_is_value = 'V') AND (flex_value_meaning = val)) OR
2112 ((p_is_value = 'I') AND (flex_value = val)));
2113
2114 BEGIN
2115 IF (fnd_flex_server1.g_debug_level > 0) THEN
2116 FND_FLEX_SERVER1.add_debug('BEGIN SV1.find_value()');
2117 END IF;
2118
2119 -- Default the return value
2120
2121 v_code := FF_VERROR;
2122
2123
2124 IF (p_is_value) THEN
2125 l_is_value := 'V';
2126 --
2127 -- R11.5 NLS: following is not true any more. char_val is stored value.
2128 -- However at the end of this function there is a conversion to
2129 -- displayed value.
2130 --
2131 this_value.displayed_value := p_char_val;
2132 this_value.stored_value := p_char_val;
2133 ELSE
2134 l_is_value := 'I';
2135 this_value.hidden_id := p_char_val;
2136 end if;
2137 this_value.enabled_flag := 'Y';
2138 this_value.summary_flag := 'N';
2139 this_value.format := p_vs_info.vsformat;
2140
2141 -- Validation = None, do nothing.
2142 --
2143 if(p_vs_info.valtype = 'N') then
2144 IF (fnd_flex_server1.g_debug_level > 0) THEN
2145 add_debug('N');
2146 END IF;
2147 this_value.stored_value := p_char_val;
2148 this_value.hidden_id := p_char_val;
2149 v_code := FF_VVALID;
2150
2151 -- Validation = Independent, or dependent look it up
2152 --
2153 ELSIF ((p_vs_info.valtype in ('I', 'D')) AND
2154 (p_seg_info.additional_where_clause IS NULL)) then
2155
2156 -- Takes care of both dependent and independent VS
2157 -- Use the independent cursor if this is an independent value set
2158 -- or if this is a dependent value set and the parent_val is null.
2159 -- Both of these conditions will set parent_val to null
2160 --
2161 if(p_parent_val is null) then
2162 open IND_cursor(p_vs_info.vsid, p_char_val);
2163 fetch IND_cursor into this_value.enabled_flag, this_value.start_valid,
2164 this_value.end_valid, this_value.summary_flag,
2165 this_value.compiled_attributes, this_value.description;
2166 if(IND_cursor%NOTFOUND) then
2167 v_code := FF_VNOTFOUND;
2168 else
2169 v_code := FF_VVALID;
2170 end if;
2171 close IND_cursor;
2172 else
2173 open DEP_cursor(p_vs_info.vsid, p_char_val, p_parent_val);
2174 fetch DEP_cursor into this_value.enabled_flag, this_value.start_valid,
2175 this_value.end_valid, this_value.summary_flag,
2176 this_value.compiled_attributes, this_value.description;
2177 if(DEP_cursor%NOTFOUND) then
2178 v_code := FF_VNOTFOUND;
2179 else
2180 v_code := FF_VVALID;
2181 end if;
2182 close DEP_cursor;
2183 end if;
2184
2185 -- Set error if value not found or set the returned value if found.
2186 -- Assumes v_code was either FF_VNOTFOUND or FF_VVALID.
2187 --
2188 if(v_code = FF_VNOTFOUND) then
2189 -- if(p_is_value) then
2190 if(TRUE) then -- Better error message for user
2191 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2192
2193 value_error_token('VALUE', msg_val(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis, p_vs_info.lettersok, p_char_val));
2194 value_error_token('SEGMENT', p_this_segname);
2195 value_error_token('VALUESET', p_vs_info.vsname);
2196 else
2197 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2198
2199 value_error_token('ID', p_char_val);
2200 value_error_token('SEGMENT', p_this_segname);
2201 value_error_token('VALUESET', p_vs_info.vsname);
2202 end if;
2203 goto return_found_value;
2204 else
2205 this_value.stored_value := p_char_val;
2206 this_value.hidden_id := p_char_val;
2207 end if;
2208
2209 -- Validation = TL Independent, or TL dependent look it up
2210 --
2211 ELSIF ((p_vs_info.valtype in ('X', 'Y')) AND
2212 (p_seg_info.additional_where_clause IS NULL)) then
2213
2214 -- Takes care of both TL dependent and TL independent VS
2215 -- Use the independent cursor if this is an independent value set
2216 -- or if this is a dependent value set and the parent_val is null.
2217 -- Both of these conditions will set parent_val to null
2218 --
2219 if(p_parent_val is null) then
2220 open INDTL_cursor(p_vs_info.vsid, p_char_val,l_is_value);
2221 fetch INDTL_cursor into this_value.enabled_flag,
2222 this_value.start_valid,
2223 this_value.end_valid, this_value.summary_flag,
2224 this_value.compiled_attributes,
2225 this_value.hidden_id, this_value.stored_value,
2226 this_value.description;
2227 if(INDTL_cursor%NOTFOUND) then
2228 v_code := FF_VNOTFOUND;
2229 else
2230 v_code := FF_VVALID;
2231 end if;
2232 close INDTL_cursor;
2233 else
2234 open DEPTL_cursor(p_vs_info.vsid, p_char_val, p_parent_val, l_is_value);
2235 fetch DEPTL_cursor into this_value.enabled_flag,
2236 this_value.start_valid,
2237 this_value.end_valid, this_value.summary_flag,
2238 this_value.compiled_attributes,
2239 this_value.hidden_id, this_value.stored_value,
2240 this_value.description;
2241 if(DEPTL_cursor%NOTFOUND) then
2242 v_code := FF_VNOTFOUND;
2243 else
2244 v_code := FF_VVALID;
2245 end if;
2246 close DEPTL_cursor;
2247 end if;
2248
2249 -- Set error if value not found or set the returned value if found.
2250 -- Assumes v_code was either FF_VNOTFOUND or FF_VVALID.
2251 --
2252 IF (v_code = FF_VNOTFOUND) then
2253 -- if(p_is_value) then
2254 if(TRUE) then -- Better error message for user
2255 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2256
2257 value_error_token('VALUE', msg_val(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis, p_vs_info.lettersok, p_char_val));
2258 value_error_token('SEGMENT', p_this_segname);
2259 value_error_token('VALUESET', p_vs_info.vsname);
2260 else
2261 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2262
2263 value_error_token('ID', p_char_val);
2264 value_error_token('SEGMENT', p_this_segname);
2265 value_error_token('VALUESET', p_vs_info.vsname);
2266 end if;
2267 goto return_found_value;
2268 else
2269 NULL;
2270 -- this_value.stored_value := p_char_val;
2271 -- this_value.hidden_id := p_char_val;
2272 end if;
2273
2274 ELSIF ((p_vs_info.valtype in ('I', 'D')) AND
2275 (p_seg_info.additional_where_clause IS NOT NULL)) then
2276 --
2277 -- Similar to Table validation.
2278 --
2279
2280 IF (fnd_flex_server1.g_debug_level > 0) THEN
2281 add_debug('~F(' || p_vs_info.valtype || ')');
2282 END IF;
2283
2284 v_code := table_validate(p_str_info, p_seg_info, p_vs_info,
2285 p_vdate, p_parent_val,
2286 p_char_val, p_is_value,
2287 p_n_prev, p_prev_dispvals, p_prev_vals,
2288 p_prev_ids, p_prev_descs, p_prev_segnames,
2289 p_prev_vsnames, check_value_table, this_value);
2290
2291 -- table_validate() does not set error message if the problem is
2292 -- that the value is not found because we might have to look up
2293 -- the value in the values table. Therefore we need to set the
2294 -- error if value was not found.
2295 if(v_code = FF_VNOTFOUND) then
2296 -- if(p_is_value) then
2297 if(TRUE) then -- Better error message for user
2298 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2299
2300 value_error_token('VALUE', msg_val(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis, p_vs_info.lettersok, p_char_val));
2301 value_error_token('SEGMENT', p_this_segname);
2302 value_error_token('VALUESET', p_vs_info.vsname);
2303 else
2304 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2305
2306 value_error_token('ID', p_char_val);
2307 value_error_token('SEGMENT', p_this_segname);
2308 value_error_token('VALUESET', p_vs_info.vsname);
2309 end if;
2310 end if;
2311
2312 ELSIF ((p_vs_info.valtype in ('X', 'Y')) AND
2313 (p_seg_info.additional_where_clause IS NOT NULL)) THEN
2314
2315 --
2316 -- Similar to Table validation.
2317 --
2318
2319 IF (fnd_flex_server1.g_debug_level > 0) THEN
2320 add_debug('~F(' || p_vs_info.valtype || ')');
2321 END IF;
2322
2323 v_code := table_validate(p_str_info, p_seg_info, p_vs_info,
2324 p_vdate, p_parent_val,
2325 p_char_val, p_is_value,
2326 p_n_prev, p_prev_dispvals, p_prev_vals,
2327 p_prev_ids, p_prev_descs, p_prev_segnames,
2328 p_prev_vsnames, check_value_table, this_value);
2329
2330 -- table_validate() does not set error message if the problem is
2331 -- that the value is not found because we might have to look up
2332 -- the value in the values table. Therefore we need to set the
2333 -- error if value was not found.
2334 if(v_code = FF_VNOTFOUND) then
2335 -- if(p_is_value) then
2336 if(TRUE) then -- Better error message for user
2337 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2338
2339 value_error_token('VALUE', msg_val(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis, p_vs_info.lettersok, p_char_val));
2340 value_error_token('SEGMENT', p_this_segname);
2341 value_error_token('VALUESET', p_vs_info.vsname);
2342 else
2343 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2344
2345 value_error_token('ID', p_char_val);
2346 value_error_token('SEGMENT', p_this_segname);
2347 value_error_token('VALUESET', p_vs_info.vsname);
2348 end if;
2349 end if;
2350
2351 -- Table validation.
2352 --
2353 ELSIF (p_vs_info.valtype = 'F') then
2354
2355 IF (fnd_flex_server1.g_debug_level > 0) THEN
2356 add_debug('F');
2357 END IF;
2358
2359 v_code := table_validate(p_str_info, p_seg_info, p_vs_info,
2360 p_vdate, p_parent_val,
2361 p_char_val, p_is_value,
2362 p_n_prev, p_prev_dispvals, p_prev_vals,
2363 p_prev_ids, p_prev_descs, p_prev_segnames,
2364 p_prev_vsnames, check_value_table, this_value);
2365
2366 if((v_code = FF_VNOTFOUND) and (check_value_table = TRUE)) then
2367 open IND_cursor(p_vs_info.vsid, p_char_val);
2368 fetch IND_cursor into this_value.enabled_flag, this_value.start_valid,
2369 this_value.end_valid, this_value.summary_flag,
2370 this_value.compiled_attributes, this_value.description;
2371 if(IND_cursor%FOUND) then
2372 this_value.stored_value := p_char_val;
2373 this_value.hidden_id := p_char_val;
2374 v_code := FF_VVALID;
2375 end if;
2376 close IND_cursor;
2377 end if;
2378
2379 -- table_validate() does not set error message if the problem is
2380 -- that the value is not found because we might have to look up
2381 -- the value in the values table. Therefore we need to set the
2382 -- error if value was not found.
2383 if(v_code = FF_VNOTFOUND) then
2384 -- if(p_is_value) then
2385 if(TRUE) then -- Better error message for user
2386 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2387
2388 value_error_token('VALUE', msg_val(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis, p_vs_info.lettersok, p_char_val));
2389 value_error_token('SEGMENT', p_this_segname);
2390 value_error_token('VALUESET', p_vs_info.vsname);
2391 else
2392 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2393
2394 value_error_token('ID', p_char_val);
2395 value_error_token('SEGMENT', p_this_segname);
2396 value_error_token('VALUESET', p_vs_info.vsname);
2397 end if;
2398 end if;
2399
2400 -- Pair or Special validation unsupported.
2401 --
2402 ELSIF (p_vs_info.valtype in ('P', 'U')) then
2403 FND_MESSAGE.set_name('FND', 'FLEX-SSV UNSUPPORTED VDATION');
2404 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2405 v_code := FF_VUNKNOWN;
2406
2407 -- Undefined validation type
2408 --
2409 else
2410 FND_MESSAGE.set_name('FND', 'FLEX-VS BAD VDATION TYPE');
2411 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2412 FND_MESSAGE.set_token('VTYPE', p_vs_info.valtype);
2413 v_code := FF_VERROR;
2414 end if;
2415
2416 <<return_found_value>>
2417 if(v_code = FF_VVALID) THEN
2418 v_code := stored_to_displayed(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis,
2419 p_vs_info.lettersok,
2420 this_value.stored_value,
2421 this_value.displayed_value);
2422 end if;
2423 x_this_val_out := this_value;
2424 IF (fnd_flex_server1.g_debug_level > 0) THEN
2425 FND_FLEX_SERVER1.add_debug('END SV1.find_value(returns:' ||
2426 v_code || ')');
2427 END IF;
2428
2429 return(v_code);
2430 EXCEPTION
2431 WHEN OTHERS then
2432 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2433 FND_MESSAGE.set_token('MSG', 'find_value() exception: ' || SQLERRM);
2434 IF (fnd_flex_server1.g_debug_level > 0) THEN
2435 FND_FLEX_SERVER1.add_debug('EXCEPTION others SV1.find_value()');
2436 END IF;
2437
2438 return(FF_VERROR);
2439 END find_value;
2440
2441 /* ----------------------------------------------------------------------- */
2442 /* Finds value from table validated value set. */
2443 /* Returns value error code and sets message on error. */
2444 /* However, if value is not found, does not set the error message */
2445 /* because we may have to look it up in the values table if */
2446 /* check_valtab is returned as 'Y' */
2447 /* NOTE: where_clause declared as VARCHAR2(30000) for implicit */
2448 /* conversion from LONG column type in the table. */
2449 /* Generic exception raised if SQL fragment in where_clause is bad. */
2450 /* ----------------------------------------------------------------------- */
2451 FUNCTION table_validate(p_str_info IN FlexStructId,
2452 p_seg_info IN SegmentInfo,
2453 p_vs_info IN ValueSetInfo,
2454 p_vdate IN DATE,
2455 p_parent_value IN VARCHAR2,
2456 p_charval IN VARCHAR2,
2457 p_is_value IN BOOLEAN,
2458 p_nprev IN NUMBER,
2459 p_prev_dispvals IN ValueArray,
2460 p_prev_vals IN ValueArray,
2461 p_prev_ids IN ValueIdArray,
2462 p_prev_descs IN ValueDescArray,
2463 p_prev_segnames IN SegNameArray,
2464 p_prev_vsnames IN VsNameArray,
2465 x_check_valtab OUT nocopy BOOLEAN,
2466 x_found_value OUT nocopy FlexValue)
2467 RETURN VARCHAR2
2468 IS
2469 l_where_clause VARCHAR2(32000);
2470 l_tmp_where_clause VARCHAR2(32000);
2471 l_order_by_pos NUMBER;
2472
2473 l_vcode VARCHAR2(1);
2474 l_sql VARCHAR2(32000);
2475 l_nrecords NUMBER;
2476 l_results StringArray;
2477
2478 CURSOR tbl_cursor(p_flex_value_set_id IN NUMBER) IS
2479 SELECT application_table_name, value_column_name, value_column_type,
2480 id_column_name, id_column_type, meaning_column_name,
2481 meaning_column_type, enabled_column_name, start_date_column_name,
2482 end_date_column_name, summary_column_name,
2483 compiled_attribute_column_name, additional_quickpick_columns,
2484 summary_allowed_flag, additional_where_clause
2485 FROM fnd_flex_validation_tables
2486 WHERE flex_value_set_id = p_flex_value_set_id;
2487
2488 l_tbl_rec tbl_cursor%ROWTYPE;
2489 l_sql_pieces sql_pieces_tab_type;
2490 BEGIN
2491 IF (p_vs_info.valtype IN ('I', 'D', 'X', 'Y')) THEN
2492 l_tbl_rec.application_table_name := 'FND_FLEX_VALUES_VL FND_FLEX_VALUES_VL';
2493 l_tbl_rec.value_column_name := 'FND_FLEX_VALUES_VL.FLEX_VALUE';
2494 l_tbl_rec.value_column_type := 'C';
2495 l_tbl_rec.id_column_name := NULL;
2496 l_tbl_rec.id_column_type := NULL;
2497 l_tbl_rec.meaning_column_name := 'FND_FLEX_VALUES_VL.DESCRIPTION';
2498 l_tbl_rec.meaning_column_type := 'C';
2499 l_tbl_rec.enabled_column_name := 'FND_FLEX_VALUES_VL.ENABLED_FLAG';
2500 l_tbl_rec.start_date_column_name := 'FND_FLEX_VALUES_VL.START_DATE_ACTIVE';
2501 l_tbl_rec.end_date_column_name := 'FND_FLEX_VALUES_VL.END_DATE_ACTIVE';
2502 l_tbl_rec.summary_column_name := 'FND_FLEX_VALUES_VL.SUMMARY_FLAG';
2503 l_tbl_rec.compiled_attribute_column_name := 'FND_FLEX_VALUES_VL.COMPILED_VALUE_ATTRIBUTES';
2504 l_tbl_rec.additional_quickpick_columns := NULL;
2505 l_tbl_rec.summary_allowed_flag := 'N';
2506
2507 IF (p_parent_value IS NULL) THEN
2508 l_tbl_rec.additional_where_clause := 'WHERE FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = :$FLEX$.$VALUE_SET_ID$';
2509 ELSE
2510 l_tbl_rec.additional_where_clause := 'WHERE FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = :$FLEX$.$VALUE_SET_ID$' ||
2511 ' AND FND_FLEX_VALUES_VL.PARENT_FLEX_VALUE_LOW = :$FLEX$.$PARENT_VALUE$';
2512 END IF;
2513
2514 ELSE
2515 open TBL_cursor(p_vs_info.vsid);
2516 FETCH tbl_cursor INTO l_tbl_rec;
2517 IF (TBL_cursor%NOTFOUND) THEN
2518 close TBL_cursor;
2519 FND_MESSAGE.set_name('FND', 'FLEX-SSV MISSING TBLVS');
2520 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2521 return(FF_VERROR);
2522 end if;
2523 close TBL_cursor;
2524 END IF;
2525
2526 IF (l_tbl_rec.summary_allowed_flag = 'Y') then
2527 x_check_valtab := TRUE;
2528 else
2529 x_check_valtab := FALSE;
2530 end if;
2531
2532 -- Return error if unsupported validation.
2533 --
2534 if((l_tbl_rec.additional_quickpick_columns is not null) and (INSTR(UPPER(l_tbl_rec.additional_quickpick_columns),'INTO') > 0)) then
2535 FND_MESSAGE.set_name('FND', 'FLEX-SSV NO TBL VS');
2536 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2537 return(FF_VUNKNOWN);
2538 end if;
2539
2540 -- Default column names if null
2541 --
2542 if(l_tbl_rec.id_column_name is null) then
2543 l_tbl_rec.id_column_name := l_tbl_rec.value_column_name;
2544 l_tbl_rec.id_column_type := l_tbl_rec.value_column_type;
2545 end if;
2546
2547 if(l_tbl_rec.meaning_column_name is null) then
2548 l_tbl_rec.meaning_column_name := 'NULL';
2549 l_tbl_rec.meaning_column_type := 'V';
2550 end if;
2551
2552 -- Now need to parse where/order by clause. Clause is of the form:
2553 -- [where <condition>] [order by <ordering>]
2554 -- The order by portion is not relevant here and is dropped.
2555 -- "where" is optional, hence the first word in the string need not be
2556 -- "where".
2557 -- Note there can only be 1 space between "order" and "by".
2558 -- "where" and "order by" are found using case-insensitive comparisons.
2559 -- Case-insensitive comparison requires upper case conversion.
2560 -- Assume SQL strings in single-byte characters only.
2561 -- Substitute $FLEX$ and $PROFILES$ in where clause if its not null.
2562 --
2563 l_where_clause := ltrim(l_tbl_rec.additional_where_clause, WHITESPACE);
2564
2565 l_sql_pieces.DELETE;
2566 if (l_where_clause is not null) then
2567 -- there is where clause
2568 l_tmp_where_clause := Upper(l_where_clause);
2569
2570 if (l_tmp_where_clause LIKE 'WHERE%') then
2571 -- It starts with WHERE keyword
2572 -- Skip the leading WHERE keyword
2573 l_where_clause := Substr(l_where_clause, 6);
2574 l_tmp_where_clause := Upper(l_where_clause);
2575 end if;
2576
2577 l_order_by_pos := Instr(l_tmp_where_clause, 'ORDER BY');
2578 if (l_order_by_pos > 0) then
2579 l_where_clause := Substr(l_where_clause, 1, l_order_by_pos - 1);
2580 end if;
2581
2582 IF (p_seg_info.additional_where_clause IS NOT NULL) THEN
2583 l_where_clause := '(' || l_where_clause || ') AND (' ||
2584 p_seg_info.additional_where_clause || ')';
2585 END IF;
2586
2587 l_vcode := substitute_flex_binds5(l_where_clause,
2588 p_str_info, p_seg_info,
2589 p_vdate, p_parent_value,
2590 p_nprev, p_prev_dispvals,
2591 p_prev_vals, p_prev_ids, p_prev_descs,
2592 p_prev_segnames, p_prev_vsnames,
2593 l_sql_pieces);
2594
2595 if (l_vcode <> FF_VVALID) then
2596 return(l_vcode);
2597 end if;
2598
2599 IF (fnd_flex_server1.g_debug_level > 0) THEN
2600 l_tmp_where_clause := NULL;
2601 IF (l_vcode = FF_VVALID) THEN
2602 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2603 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2604 l_tmp_where_clause := l_tmp_where_clause ||
2605 string_clause(l_sql_pieces(i).bind_value);
2606
2607 ELSE
2608 l_tmp_where_clause := l_tmp_where_clause ||
2609 l_sql_pieces(i).piece_text;
2610 END IF;
2611 END LOOP;
2612 END IF;
2613
2614 l_where_clause := l_tmp_where_clause;
2615
2616 if(l_where_clause is not null) then
2617 add_debug(' (where ' || SUBSTRB(l_where_clause, 1, 1000) || ') ');
2618 end if;
2619 END IF;
2620 end if;
2621
2622 -- Build SQL statement to do the select from the table specified
2623 -- by APPLICATION_TABLE_NAME.
2624 --
2625 -- The columns SUMMARY_COLUMN_NAME, COMPILED_ATTRIBUTE_COLUMN_NAME,
2626 -- ENABLED_COLUMN_NAME, and START and END _DATE_COLUMN_NAME in the
2627 -- FND_FLEX_VALIDATION_TABLES table either contain the name of the
2628 -- column in the "application table" which contains those parameters,
2629 -- or they contain text strings which are part of a sql statement.
2630 -- For example, the COMPILED_ATTRIBUTES_COLUMN_NAME contains either the
2631 -- name of a column, or a SQL fragment of the form: "'Y\nN\nA'" which
2632 -- includes the single quotes.
2633 --
2634 -- In either case the contents of these fields should be blindly
2635 -- inserted into the SQL statement.
2636 --
2637 -- BUG: To be compatible with client code behavior, we must surround
2638 -- non-character columns with the default to_char() conversion in the
2639 -- select statement. This causes seconds to be lost from date columns.
2640 -- It also means that numbers are stored and displayed in their default
2641 -- format rather than the value set format.
2642 --
2643 -- This functionality is now in the select_clause() function.
2644 --
2645 l_sql := 'select ' ||
2646 select_clause(l_tbl_rec.value_column_name, l_tbl_rec.value_column_type,
2647 VC_VALUE, p_vs_info.vsformat, p_vs_info.maxsize);
2648 l_sql := l_sql || ', ' ||
2649 select_clause(l_tbl_rec.id_column_name, l_tbl_rec.id_column_type,
2650 VC_ID, p_vs_info.vsformat, p_vs_info.maxsize);
2651 l_sql := l_sql || ', ' ||
2652 select_clause(l_tbl_rec.meaning_column_name, l_tbl_rec.meaning_column_type,
2653 VC_DESCRIPTION, p_vs_info.vsformat, p_vs_info.maxsize);
2654 l_sql := l_sql || ', ' || l_tbl_rec.enabled_column_name || ', ';
2655 l_sql := l_sql || 'to_char(' || l_tbl_rec.start_date_column_name ||',''YYYY/MM/DD HH24:MI:SS'')' || ', ';
2656 l_sql := l_sql || 'to_char(' || l_tbl_rec.end_date_column_name ||',''YYYY/MM/DD HH24:MI:SS'')' || ', ';
2657 l_sql := l_sql || l_tbl_rec.summary_column_name || ', ';
2658 l_sql := l_sql || l_tbl_rec.compiled_attribute_column_name;
2659 l_sql := l_sql || ' from ' || l_tbl_rec.application_table_name || ' where ';
2660
2661 fnd_dsql.init;
2662 fnd_dsql.add_text(l_sql);
2663
2664 --
2665 -- The where clause needs to be surrounded by parentheses so we can
2666 -- add additional restrictions using AND without overriding ORs inside it.
2667 --
2668 IF (l_sql_pieces.COUNT > 0) THEN
2669 fnd_dsql.add_text('(');
2670
2671 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2672 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2673 fnd_dsql.add_bind(l_sql_pieces(i).bind_value);
2674 ELSE
2675 fnd_dsql.add_text(l_sql_pieces(i).piece_text);
2676 END IF;
2677 END LOOP;
2678
2679 fnd_dsql.add_text(') and ');
2680 END IF;
2681
2682 -- Build comparison appropriate for data type of value or id column
2683 --
2684 if(p_is_value) THEN
2685 fnd_dsql.add_text(l_tbl_rec.value_column_name || ' = ');
2686 x_compare_clause(l_tbl_rec.value_column_type, l_tbl_rec.value_column_name, p_charval,
2687 VC_VALUE, p_vs_info.vsformat, p_vs_info.maxsize);
2688 else
2689 fnd_dsql.add_text(l_tbl_rec.id_column_name || ' = ');
2690 x_compare_clause(l_tbl_rec.id_column_type, l_tbl_rec.id_column_name, p_charval,
2691 VC_ID, p_vs_info.vsformat, p_vs_info.maxsize);
2692 end if;
2693
2694
2695 -- Select the value from the user's table.
2696 --
2697 l_nrecords := x_dsql_select(8, l_results);
2698
2699 if(l_nrecords > 0) then
2700 x_found_value.format := p_vs_info.vsformat;
2701 x_found_value.stored_value := l_results(1);
2702 x_found_value.hidden_id := l_results(2);
2703 x_found_value.description := l_results(3);
2704 x_found_value.enabled_flag := l_results(4);
2705 x_found_value.start_valid := to_date(l_results(5),'YYYY/MM/DD HH24:MI:SS');
2706 x_found_value.end_valid := to_date(l_results(6),'YYYY/MM/DD HH24:MI:SS');
2707 x_found_value.summary_flag := l_results(7);
2708 x_found_value.compiled_attributes := l_results(8);
2709 return(FF_VVALID);
2710 elsif(l_nrecords = 0) then
2711 return(FF_VNOTFOUND);
2712 else
2713 return(FF_VERROR);
2714 end if;
2715
2716 return(FF_VERROR);
2717
2718 EXCEPTION
2719 WHEN OTHERS then
2720 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2721 FND_MESSAGE.set_token('MSG', 'table_validate() exception: ' || SQLERRM);
2722 return(FF_VERROR);
2723
2724 END table_validate;
2725
2726 /* ----------------------------------------------------------------------- */
2727 /* Returns the default value for this segment in displayed format. */
2728 /* For defaulting current date and current time into non-translatable */
2729 /* date value sets, use the format based on the maximum size of the */
2730 /* value set field alone. */
2731 /* Let ordinary value validation catch problems with the format */
2732 /* or length of the defaulted value. Returns FF_VVALID if default */
2733 /* succeeded, FF_VUNKNOWN if unsupported or FF_VERROR. */
2734 /* ----------------------------------------------------------------------- */
2735 FUNCTION default_val(def_type IN VARCHAR2,
2736 def_text IN VARCHAR2,
2737 valset_fmt IN VARCHAR2,
2738 valset_len IN NUMBER,
2739 valset_precis IN NUMBER,
2740 valset_lettersok IN VARCHAR2,
2741 seg_name IN VARCHAR2,
2742 nprev IN NUMBER,
2743 prev_dispvals IN ValueArray,
2744 prev_vals IN ValueArray,
2745 prev_ids IN ValueIdArray,
2746 prev_descs IN ValueDescArray,
2747 prev_segnames IN SegNameArray,
2748 prev_vsnames IN VsNameArray,
2749 displayed_val OUT nocopy VARCHAR2)
2750 RETURN VARCHAR2 IS
2751 nfound NUMBER;
2752 v_code VARCHAR2(1);
2753 datefmt VARCHAR2(30);
2754 stored_val VARCHAR2(1000);
2755 sql_string VARCHAR2(2000);
2756 l_sql_pieces sql_pieces_tab_type;
2757 BEGIN
2758 v_code := FF_VERROR;
2759
2760 IF (def_type is null) then
2761 v_code := FF_VVALID;
2762 ELSIF (def_type = 'C') then
2763 -- Constant
2764 stored_val := SUBSTRB(def_text, 1, 1000);
2765 v_code := FF_VVALID;
2766
2767 ELSIF (def_type = 'D') then
2768 -- Current date.
2769 IF (valset_fmt in ('X', 'Y')) then
2770 datefmt := stored_date_format(valset_fmt, valset_len);
2771 ELSIF (valset_len >= 11) then
2772 datefmt := stored_date_format('D', 11);
2773 else
2774 datefmt := stored_date_format('D', 9);
2775 end if;
2776 stored_val := to_char(sysdate, datefmt);
2777 v_code := FF_VVALID;
2778
2779 ELSIF (def_type = 'T') then
2780 -- Current time or date-time depending on the size
2781 IF (valset_fmt in ('Y', 'Z')) then
2782 datefmt := stored_date_format(valset_fmt, valset_len);
2783 ELSIF (valset_len < 8) then
2784 datefmt := stored_date_format('I', 5);
2785 ELSIF (valset_len between 8 and 14) then
2786 datefmt := stored_date_format('I', 8);
2787 ELSIF (valset_len between 15 and 16) then
2788 datefmt := stored_date_format('T', 15);
2789 ELSIF (valset_len = 17) then
2790 datefmt := stored_date_format('T', 17);
2791 ELSIF (valset_len between 18 and 19) then
2792 datefmt := stored_date_format('T', 18);
2793 else
2794 datefmt := stored_date_format('T', 20);
2795 end if;
2796 stored_val := to_char(sysdate, datefmt);
2797 v_code := FF_VVALID;
2798
2799 ELSIF (def_type = 'P') then
2800 sql_string := ':$PROFILES$.' || def_text;
2801 v_code := convert_bind_token(sql_string, nprev,
2802 prev_dispvals, prev_vals,
2803 prev_ids, prev_descs,
2804 prev_segnames, prev_vsnames,
2805 stored_val);
2806
2807 ELSIF ((def_type = 's') OR (def_type = 'A')) then
2808 sql_string := ':$FLEX$.' || def_text;
2809 v_code := convert_bind_token(sql_string, nprev,
2810 prev_dispvals, prev_vals,
2811 prev_ids, prev_descs,
2812 prev_segnames, prev_vsnames,
2813 stored_val);
2814
2815 ELSIF (def_type = 'S') then
2816 -- SQL statement
2817 v_code := substitute_flex_binds3(def_text, nprev,
2818 prev_dispvals, prev_vals,
2819 prev_ids, prev_descs,
2820 prev_segnames, prev_vsnames,
2821 l_sql_pieces);
2822 IF (v_code = FF_VVALID) THEN
2823 fnd_dsql.init;
2824
2825 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2826 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2827 fnd_dsql.add_bind(l_sql_pieces(i).bind_value);
2828 ELSE
2829 fnd_dsql.add_text(l_sql_pieces(i).piece_text);
2830 END IF;
2831 END LOOP;
2832
2833 nfound := x_dsql_select_one(stored_val);
2834 IF (nfound > 1) then
2835 FND_MESSAGE.set_name('FND', 'FLEX-DFLT MULTIPLE SQL ROWS');
2836 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(fnd_dsql.get_text(FALSE), 1, 1000));
2837 v_code := FF_VERROR;
2838 ELSIF (nfound < 0) then
2839 v_code := FF_VERROR;
2840 else
2841 v_code := FF_VVALID;
2842 end if;
2843 end if;
2844
2845 ELSIF (def_type = 'F') then
2846 -- 'F' => :block.field (the colon may or may not be in the def_text string)
2847 FND_MESSAGE.set_name('FND', 'FLEX-SSV UNSUPPORTED DEFAULT');
2848 v_code := FF_VUNKNOWN;
2849
2850 ELSIF (def_type = 'E') then
2851 -- Environment variable
2852 FND_MESSAGE.set_name('FND', 'FLEX-SSV UNSUPPORTED DEFAULT');
2853 v_code := FF_VUNKNOWN;
2854
2855 ELSE
2856 -- Unknown type.
2857 FND_MESSAGE.set_name('FND', 'FLEX-INVALID DEFAULT TYPE');
2858 FND_MESSAGE.set_token('SEGNAME', seg_name);
2859 v_code := FF_VERROR;
2860
2861 END IF;
2862
2863 IF (v_code = FF_VVALID) then
2864 v_code := stored_to_displayed(valset_fmt, valset_len, valset_precis,
2865 valset_lettersok,
2866 stored_val, displayed_val);
2867 end if;
2868 return(v_code);
2869 EXCEPTION
2870 WHEN OTHERS then
2871 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2872 FND_MESSAGE.set_token('MSG', 'default_val() exception: ' || SQLERRM);
2873 return(FF_VERROR);
2874 END default_val;
2875
2876 /* ------------------------------------------------------------------------- */
2877 /* Interprets $PROFILES$ in where token passed into FND_KEY_FLEX.DEFINE */
2878 /* This cannot take $FLEX$, and :block.field is unsupported. */
2879 /* Returns Validataion error codes VV_VALID if ok, VV_UNSUPPORTED */
2880 /* if unsupported, or VV_ERROR if other error. */
2881 /* ------------------------------------------------------------------------- */
2882
2883 FUNCTION parse_where_token(clause_in IN VARCHAR2,
2884 clause_out OUT nocopy VARCHAR2) RETURN NUMBER IS
2885 vcode VARCHAR2(1);
2886 dummy_dispvls ValueArray;
2887 dummy_vals ValueArray;
2888 dummy_ids ValueIdArray;
2889 dummy_descs ValueDescArray;
2890 dummy_segname SegNameArray;
2891 dummy_vsnames VsNameArray;
2892 l_sql_pieces sql_pieces_tab_type;
2893
2894 BEGIN
2895
2896 if(clause_in is not null) then
2897 if(INSTR(clause_in, ':$FLEX$') > 0) then
2898 FND_MESSAGE.set_name('FND', 'FLEX-NO FLEX IN WHERE TOKEN');
2899 return(VV_ERROR);
2900 end if;
2901 vcode := substitute_flex_binds3(clause_in, 0,
2902 dummy_dispvls, dummy_vals,
2903 dummy_ids, dummy_descs,
2904 dummy_segname, dummy_vsnames,
2905 l_sql_pieces);
2906
2907 clause_out := NULL;
2908 IF (vcode = FF_VVALID) THEN
2909 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2910 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2911 clause_out := clause_out ||
2912 string_clause(l_sql_pieces(i).bind_value);
2913
2914 ELSE
2915 clause_out := clause_out ||
2916 l_sql_pieces(i).piece_text;
2917 END IF;
2918 END LOOP;
2919 END IF;
2920
2921 if(vcode = FF_VVALID) then
2922 return(VV_VALID);
2923 elsif(vcode = FF_VUNKNOWN) then
2924 return(VV_UNSUPPORTED);
2925 else
2926 return(VV_ERROR);
2927 end if;
2928 end if;
2929 return(VV_VALID);
2930
2931 EXCEPTION
2932 WHEN OTHERS then
2933 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2934 FND_MESSAGE.set_token('MSG', 'parse_where_token() exception: '||SQLERRM);
2935 return(VV_ERROR);
2936
2937 END parse_where_token;
2938
2939 /* ------------------------------------------------------------------------ */
2940 /* Takes input string possibly containing special flex bind variables */
2941 /* of :$PROFILES$.<profile>, :$FLEX$.<name>[.<output>] or :block.field */
2942 /* and substitutes the flex values for them. Some substitutions such */
2943 /* as :block.field cannot be handled on the server and results in an */
2944 /* FF_VUNKNOWN being returned. */
2945 /* */
2946 /* Input string can contain quoted regions, within which no */
2947 /* substitutions should be performed. The regions are marked by */
2948 /* single quotes. Double quotes within these regions represent */
2949 /* the quote character. */
2950 /* */
2951 /* Limitations: string_in, string_out up to 30,000 bytes. */
2952 /* bind_token and bind_value up to 2000 bytes. */
2953 /* */
2954 /* Returns FF_VVALID if default succeeded, FF_VUNKNOWN if unsupported */
2955 /* or FF_VERROR on error. */
2956 /* ------------------------------------------------------------------------ */
2957 FUNCTION substitute_flex_binds3(p_string_in IN VARCHAR2,
2958 p_nprev IN NUMBER,
2959 p_prev_dispvals IN ValueArray,
2960 p_prev_vals IN ValueArray,
2961 p_prev_ids IN ValueIdArray,
2962 p_prev_descs IN ValueDescArray,
2963 p_prev_segnames IN SegNameArray,
2964 p_prev_vsnames IN VsNameArray,
2965 px_sql_pieces in out nocopy sql_pieces_tab_type)
2966 RETURN VARCHAR2
2967 IS
2968 v_code VARCHAR2(1);
2969 BEGIN
2970 IF (fnd_flex_server1.g_debug_level > 0) THEN
2971 add_debug('BEGIN SV1.substitute_flex_binds3('''||
2972 SUBSTRB(p_string_in, 1, 500) || ''' ');
2973 END IF;
2974
2975 BEGIN
2976 parse_sql_string(p_string_in, px_sql_pieces);
2977 EXCEPTION
2978 WHEN OTHERS THEN
2979 RETURN(FF_VERROR);
2980 END;
2981
2982 --
2983 -- Derive bind values
2984 --
2985 FOR i IN 1 .. px_sql_pieces.COUNT LOOP
2986 IF (px_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2987 v_code := convert_bind_token(px_sql_pieces(i).piece_text,
2988 p_nprev, p_prev_dispvals,
2989 p_prev_vals, p_prev_ids, p_prev_descs,
2990 p_prev_segnames, p_prev_vsnames,
2991 px_sql_pieces(i).bind_value);
2992
2993 IF (v_code <> FF_VVALID) then
2994 return(v_code);
2995 end if;
2996 END IF;
2997 END LOOP;
2998
2999 return(FF_VVALID);
3000
3001 EXCEPTION
3002 WHEN OTHERS then
3003 IF (fnd_flex_server1.g_debug_level > 0) THEN
3004 add_debug('EXCEPTION SV1.substitute_flex_binds3()');
3005 END IF;
3006 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3007 FND_MESSAGE.set_token('MSG', 'substitute_flex_binds3() exception: '
3008 || SQLERRM);
3009 return(FF_VERROR);
3010 END substitute_flex_binds3;
3011
3012 --
3013 -- The one with extension support.
3014 --
3015 /* ------------------------------------------------------------------------ */
3016 FUNCTION substitute_flex_binds5(p_string_in IN VARCHAR2,
3017 p_str_info IN FlexStructId,
3018 p_seg_info IN SegmentInfo,
3019 p_vdate IN DATE,
3020 p_parent_value IN VARCHAR2,
3021 p_nprev IN NUMBER,
3022 p_prev_dispvals IN ValueArray,
3023 p_prev_vals IN ValueArray,
3024 p_prev_ids IN ValueIdArray,
3025 p_prev_descs IN ValueDescArray,
3026 p_prev_segnames IN SegNameArray,
3027 p_prev_vsnames IN VsNameArray,
3028 px_sql_pieces in out nocopy sql_pieces_tab_type)
3029 RETURN VARCHAR2
3030 IS
3031 v_code VARCHAR2(1);
3032 BEGIN
3033 IF (fnd_flex_server1.g_debug_level > 0) THEN
3034 add_debug('BEGIN SV1.substitute_flex_binds5('''||
3035 SUBSTRB(p_string_in, 1, 500) || ''' ');
3036 END IF;
3037
3038 BEGIN
3039 parse_sql_string(p_string_in, px_sql_pieces);
3040 EXCEPTION
3041 WHEN OTHERS THEN
3042 RETURN(FF_VERROR);
3043 END;
3044
3045 --
3046 -- Derive bind values
3047 --
3048 FOR i IN 1 .. px_sql_pieces.COUNT LOOP
3049 IF (px_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
3050 v_code := convert_bind_token2(px_sql_pieces(i).piece_text,
3051 p_str_info, p_seg_info,
3052 p_vdate, p_parent_value,
3053 p_nprev, p_prev_dispvals,
3054 p_prev_vals, p_prev_ids, p_prev_descs,
3055 p_prev_segnames, p_prev_vsnames,
3056 px_sql_pieces(i).bind_value);
3057
3058 IF (v_code <> FF_VVALID) then
3059 return(v_code);
3060 end if;
3061 END IF;
3062 END LOOP;
3063
3064 return(FF_VVALID);
3065
3066 EXCEPTION
3067 WHEN OTHERS then
3068 IF (fnd_flex_server1.g_debug_level > 0) THEN
3069 add_debug('EXCEPTION SV1.substitute_flex_binds5()');
3070 END IF;
3071 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3072 FND_MESSAGE.set_token('MSG', 'substitute_flex_binds5() exception: '
3073 || SQLERRM);
3074 return(FF_VERROR);
3075 END substitute_flex_binds5;
3076
3077 /* ----------------------------------------------------------------------- */
3078 /* Converts a flex bind token to its character string value. */
3079 /* Bind tokens begin with a colon and are of the form: */
3080 /* :$FLEX$.<vsname>[.<portion>] or :$PROFILES$.<profile_name> */
3081 /* If [.<portion>] not specified uses the ID not the VALUE. */
3082 /* Note server can never handle :BLOCK.FIELD */
3083 /* Returns FF_VVALID if substitution succeeded, FF_VUNKNOWN if */
3084 /* unsupported or FF_VERROR on error. */
3085 /* ----------------------------------------------------------------------- */
3086 FUNCTION convert_bind_token(bind_token IN VARCHAR2,
3087 nprev IN NUMBER,
3088 prev_dispvals IN ValueArray,
3089 prev_vals IN ValueArray,
3090 prev_ids IN ValueIdArray,
3091 prev_descs IN ValueDescArray,
3092 prev_segnames IN SegNameArray,
3093 prev_vsnames IN VsNameArray,
3094 bind_value OUT nocopy VARCHAR2)
3095 RETURN VARCHAR2
3096 IS
3097 bind_val VARCHAR2(2000);
3098 seg_name VARCHAR2(60);
3099 seg_portion VARCHAR2(30);
3100 dot_pointer NUMBER;
3101 col_pointer NUMBER;
3102 l_length NUMBER;
3103 s_index BINARY_INTEGER;
3104 l_return_code VARCHAR2(100);
3105
3106 BEGIN
3107 -- Determine character string value of bind token.
3108 -- :$PROFILES$.<name> where <name> is case-insensitive profile option name.
3109 -- :$FLEX$.<name>[.<portion>] where name is case-sensitive, but portion not.
3110 -- :BLOCK.FIELD causes return of FF_VUNKNOWN error.
3111 --
3112 IF (fnd_flex_server1.g_debug_level > 0) THEN
3113 add_debug('BEGIN SV1.convert_bind_token(token=' || bind_token || ')');
3114 END IF;
3115
3116 IF (INSTR(bind_token, ':$PROFILES$.') = 1) then
3117 FND_PROFILE.get(UPPER(SUBSTR(bind_token, 13)), bind_val);
3118
3119 ELSIF (INSTR(bind_token, ':$FLEX$.') = 1) then
3120
3121 -- If default segment name is of the form <name>.ID, <name>.VALUE
3122 -- or <name>.MEANING, then break it into seg_name and seg_portion.
3123 -- Note ID, VALUE and MEANING are all case insensitive
3124 --
3125 -- Bug 1783461: Flexfield API does not handle :NULL suffix.
3126 -- the original code did not strip off the variables after the value set/
3127 -- or segment name and when used resulted in a "ORA-20001 The data that
3128 -- defines the flexfield may be inconsistent".
3129 -- The syntax for using bind variables in the default where clause of a
3130 -- value set is as follows (for $FLEX$);
3131 --
3132 -- :$FLEX$.[valuesetname|segmentname][.ID|.VALUE|.MEANING][:NULL]
3133
3134 dot_pointer := INSTR(bind_token, '.', 9);
3135 col_pointer := INSTR(bind_token,':NULL',9);
3136 l_length := length(bind_token);
3137 if (dot_pointer > 0) then
3138 if (col_pointer > 0) then
3139 null;
3140 else
3141 col_pointer := l_length + 1;
3142 end if;
3143 else
3144 if (col_pointer > 0) then
3145 dot_pointer := col_pointer;
3146 else
3147 dot_pointer := l_length + 1;
3148 col_pointer := l_length + 1;
3149 end if;
3150 end if;
3151
3152 if (dot_pointer > col_pointer) then
3153 FND_MESSAGE.set_name('FND','FLEX-INVALID PORTION');
3154 FND_MESSAGE.set_token('BTOKEN',bind_token);
3155 l_return_code := FF_VERROR;
3156 GOTO goto_return;
3157 end if;
3158
3159 seg_name := SUBSTR(bind_token,9,dot_pointer-9);
3160 seg_portion := SUBSTR(bind_token,dot_pointer+1,col_pointer-dot_pointer-1);
3161
3162 if (seg_portion is null) then
3163 seg_portion := 'ID';
3164 end if;
3165
3166 -- Find index to previous segment or 0 if previous segment not found.
3167 -- Previous segment value set name or segment name.
3168 --
3169 s_index := 0;
3170 for i in reverse 1..nprev loop
3171 if((prev_segnames(i) = seg_name) or (prev_vsnames(i) = seg_name)) then
3172 s_index := i;
3173 exit;
3174 end if;
3175 end loop;
3176
3177 -- Copy value, id or meaning to output if found, otherwise error.
3178 --
3179 if(s_index > 0) then
3180 if(seg_portion = 'VALUE') then
3181 bind_val := prev_vals(s_index);
3182 elsif(seg_portion = 'ID') then
3183 bind_val := prev_ids(s_index);
3184 elsif(seg_portion = 'MEANING') then
3185 bind_val := prev_descs(s_index);
3186 else
3187 FND_MESSAGE.set_name('FND', 'FLEX-INVALID PORTION');
3188 FND_MESSAGE.set_token('BTOKEN', bind_token);
3189 l_return_code := FF_VERROR;
3190 GOTO goto_return;
3191 end if;
3192 else
3193 FND_MESSAGE.set_name('FND', 'FLEX-PRIOR SEG NOTFOUND');
3194 FND_MESSAGE.set_token('BTOKEN', bind_token);
3195 l_return_code := FF_VERROR;
3196 GOTO goto_return;
3197 end if;
3198
3199 else
3200
3201 -- :BLOCK.FIELD cannot be handled here
3202 --
3203 FND_MESSAGE.set_name('FND', 'FLEX-UNSUPPORTED FLEX BIND');
3204 l_return_code := FF_VUNKNOWN;
3205 GOTO goto_return;
3206
3207 end if;
3208
3209 bind_value := bind_val;
3210 l_return_code := FF_VVALID;
3211
3212 <<goto_return>>
3213 IF (fnd_flex_server1.g_debug_level > 0) THEN
3214 add_debug('END SV1.convert_bind_token(value=' || bind_val || ')');
3215 END IF;
3216 RETURN(l_return_code);
3217 EXCEPTION
3218 WHEN OTHERS then
3219 IF (fnd_flex_server1.g_debug_level > 0) THEN
3220 add_debug('EXCEPTION others SV1.convert_bind_token()');
3221 END IF;
3222 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3223 FND_MESSAGE.set_token('MSG','convert_bind_token() exception: '||SQLERRM);
3224 return(FF_VERROR);
3225 END convert_bind_token;
3226
3227 --
3228 -- The one with extension support.
3229 --
3230 FUNCTION convert_bind_token2(p_bind_token IN VARCHAR2,
3231 p_str_info IN FlexStructId,
3232 p_seg_info IN SegmentInfo,
3233 p_vdate IN DATE,
3234 p_parent_value IN VARCHAR2,
3235 p_nprev IN NUMBER,
3236 p_prev_dispvals IN ValueArray,
3237 p_prev_vals IN ValueArray,
3238 p_prev_ids IN ValueIdArray,
3239 p_prev_descs IN ValueDescArray,
3240 p_prev_segnames IN SegNameArray,
3241 p_prev_vsnames IN VsNameArray,
3242 x_bind_value OUT nocopy VARCHAR2)
3243 RETURN VARCHAR2
3244 IS
3245 l_bind_value VARCHAR2(2000);
3246 l_return_code VARCHAR2(100);
3247
3248 BEGIN
3249 IF (fnd_flex_server1.g_debug_level > 0) THEN
3250 add_debug('BEGIN SV1.convert_bind_token2(bind=' || p_bind_token || ')');
3251 END IF;
3252
3253 l_bind_value := NULL;
3254 IF (p_bind_token = ':$FLEX$.$VDATE$') THEN
3255 IF (p_vdate IS NULL) THEN
3256 l_bind_value := NULL;
3257 ELSE
3258 l_bind_value := To_char(p_vdate, 'YYYY/MM/DD HH24:MI:SS');
3259 END IF;
3260
3261 ELSIF (p_bind_token = ':$FLEX$.$APPLICATION_ID$') THEN
3262 l_bind_value := p_str_info.application_id;
3263
3264 ELSIF (p_bind_token = ':$FLEX$.$ID_FLEX_CODE$') THEN
3265 IF (p_str_info.isa_key_flexfield) THEN
3266 l_bind_value := p_str_info.id_flex_code;
3267 END IF;
3268
3269 ELSIF (p_bind_token = ':$FLEX$.$ID_FLEX_NUM$') THEN
3270 IF (p_str_info.isa_key_flexfield) THEN
3271 l_bind_value := p_str_info.id_flex_num;
3272 END IF;
3273
3274 ELSIF (p_bind_token = ':$FLEX$.$APPLICATION_COLUMN_NAME$') THEN
3275 l_bind_value := p_seg_info.colname;
3276
3277 ELSIF (p_bind_token = ':$FLEX$.$VALUE_SET_ID$') THEN
3278 l_bind_value := p_seg_info.vsid;
3279
3280 ELSIF (p_bind_token = ':$FLEX$.$PARENT_VALUE$') THEN
3281 l_bind_value := p_parent_value;
3282
3283 ELSE
3284 l_return_code := convert_bind_token(p_bind_token, p_nprev,
3285 p_prev_dispvals, p_prev_vals,
3286 p_prev_ids, p_prev_descs,
3287 p_prev_segnames, p_prev_vsnames,
3288 x_bind_value);
3289 GOTO goto_return;
3290 END IF;
3291
3292 x_bind_value := l_bind_value;
3293 l_return_code := FF_VVALID;
3294
3295 <<goto_return>>
3296 IF (fnd_flex_server1.g_debug_level > 0) THEN
3297 add_debug('END SV1.convert_bind_token2(value=' || l_bind_value || ')');
3298 END IF;
3299 RETURN(l_return_code);
3300 EXCEPTION
3301 WHEN OTHERS then
3302 IF (fnd_flex_server1.g_debug_level > 0) THEN
3303 add_debug('EXCEPTION others SV1.convert_bind_token2()');
3304 END IF;
3305 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3306 FND_MESSAGE.set_token('MSG','convert_bind_token2() exception: '||SQLERRM);
3307 return(FF_VERROR);
3308 END convert_bind_token2;
3309
3310 /* ----------------------------------------------------------------------- */
3311 /* Checks vrules. Returns TRUE and sets error message if any are */
3312 /* violated. Checks rules in forward order for compatibility with */
3313 /* C code. Also checks SUMMARY_FLAG. */
3314 /* ----------------------------------------------------------------------- */
3315 FUNCTION check_vrules(vrs IN Vrules,
3316 sqs IN Qualifiers,
3317 sumflg IN VARCHAR2) RETURN VARCHAR2 IS
3318
3319 testval VARCHAR2(245);
3320
3321 BEGIN
3322
3323 -- Check vrules in forward order for compatibility with C code.
3324 --
3325 for i in 1..vrs.nvrules loop
3326 if((vrs.fq_names(i) is null) and (vrs.sq_names(i) = 'SUMMARY_FLAG')) then
3327 testval := SEPARATOR || sumflg || SEPARATOR;
3328 if(INSTR(vrs.cat_vals(i), testval) > 0) then
3329 if(vrs.ie_flags(i) = 'E') then
3330 value_error_name(vrs.app_names(i), vrs.err_names(i));
3331 return(FF_VVRULE);
3332 end if;
3333 else
3334 if(vrs.ie_flags(i) = 'I') then
3335 value_error_name(vrs.app_names(i), vrs.err_names(i));
3336 return(FF_VVRULE);
3337 end if;
3338 end if;
3339 else
3340 for j in 1..sqs.nquals loop
3341 if((vrs.sq_names(i) = sqs.sq_names(j)) and
3342 (vrs.fq_names(i) = sqs.fq_names(j))) then
3343 testval := SEPARATOR || sqs.sq_values(j) || SEPARATOR;
3344 if((vrs.cat_vals(i) is not null) and
3345 (INSTR(vrs.cat_vals(i), testval) > 0)) then
3346 if(vrs.ie_flags(i) = 'E') then
3347 value_error_name(vrs.app_names(i), vrs.err_names(i));
3348 return(FF_VVRULE);
3349 end if;
3350 else
3351 if(vrs.ie_flags(i) = 'I') then
3352 value_error_name(vrs.app_names(i), vrs.err_names(i));
3353 return(FF_VVRULE);
3354 end if;
3355 end if;
3356 end if;
3357 end loop;
3358 end if;
3359 end loop;
3360
3361 return(FF_VVALID);
3362
3363 EXCEPTION
3364 WHEN OTHERS then
3365 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3366 FND_MESSAGE.set_token('MSG', 'check_vrules() exception: ' || SQLERRM);
3367 return(FF_VERROR);
3368
3369 END check_vrules;
3370
3371 /* ----------------------------------------------------------------------- */
3372 /* Checks vrules on combinations based on the qualifiers in the */
3373 /* combinations table. This function is external to this file being */
3374 /* called from AFFFSSVB.pls. */
3375 /* Returns VV_VALID if all rules pass, or VV_VALUES or VV_ERROR and */
3376 /* sets message name if any rules violated. */
3377 /* ----------------------------------------------------------------------- */
3378
3379 FUNCTION check_comb_vrules(vrs IN Vrules,
3380 sqs IN Qualifiers,
3381 sumflg IN VARCHAR2) RETURN NUMBER IS
3382 v_code VARCHAR2(1);
3383 retcode NUMBER;
3384
3385 BEGIN
3386
3387 -- First must initialize the value errors since we always want to display
3388 -- the error if any rule violated.
3389 --
3390 value_error_init;
3391
3392 -- Now call function above to check the rules.
3393 --
3394 v_code := check_vrules(vrs, sqs, sumflg);
3395 if(v_code = FF_VVALID) then
3396 IF (fnd_flex_server1.g_debug_level > 0) THEN
3397 add_debug(' Combination passed vrules. ');
3398 END IF;
3399 retcode := VV_VALID;
3400 elsif(v_code = FF_VVRULE) then
3401 IF (fnd_flex_server1.g_debug_level > 0) THEN
3402 add_debug(' Combination failed vrules. ');
3403 END IF;
3404 retcode := VV_VALUES;
3405 else
3406 retcode := VV_ERROR;
3407 end if;
3408
3409 return(retcode);
3410
3411 EXCEPTION
3412 WHEN OTHERS then
3413 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3414 FND_MESSAGE.set_token('MSG','check_comb_vrules() exception: '||SQLERRM);
3415 return(VV_ERROR);
3416
3417 END check_comb_vrules;
3418
3419 /* ----------------------------------------------------------------------- */
3420 /* Checks value security rules for any which are violated. */
3421 /* Returns status codes of FF_VVALID if no security rule violated, */
3422 /* FF_VSECURED if security violated, or FF_VERROR if some error */
3423 /* checking security. */
3424 /* If security is violated a value error message is added */
3425 /* only if set_message is TRUE. */
3426 /* ----------------------------------------------------------------------- */
3427
3428 FUNCTION check_security(val IN VARCHAR2,
3429 valfmt IN VARCHAR2,
3430 parentval IN VARCHAR2,
3431 user_apid IN NUMBER,
3432 user_respid IN NUMBER,
3433 vsinfo IN ValueSetInfo,
3434 set_message IN BOOLEAN) RETURN VARCHAR2 IS
3435
3436 bufstr VARCHAR2(500);
3437 rulemsgbuf VARCHAR2(240);
3438 nfound NUMBER;
3439 l_vsc_code VARCHAR2(10);
3440 l_return_code VARCHAR2(10);
3441 l_security_status VARCHAR2(2000);
3442 l_error_message VARCHAR2(2000);
3443 BEGIN
3444 IF (fnd_flex_server1.g_debug_level > 0) THEN
3445 add_debug('BEGIN SV1.check_security()');
3446 END IF;
3447
3448 --
3449 -- Return immediately if user info is not set.
3450 --
3451 IF ((user_apid = -1) AND (user_respid = -1)) THEN
3452 RETURN(FF_VVALID);
3453 END IF;
3454 --
3455 -- First check the VSC
3456 --
3457 l_vsc_code := check_vsc(user_apid, user_respid, vsinfo.vsid,
3458 parentval, val, l_return_code);
3459 IF (l_vsc_code IN (fnd_plsql_cache.CACHE_VALID,
3460 fnd_plsql_cache.CACHE_INVALID)) THEN
3461 IF (fnd_flex_server1.g_debug_level > 0) THEN
3462 add_debug('Found in vsc.');
3463 END IF;
3464 GOTO label_return;
3465 END IF;
3466
3467 --
3468 -- l_vsc_code is either CACHE_NOTFOUND.
3469 -- Continue on security check.
3470 --
3471 l_return_code := FF_VVALID;
3472
3473 fnd_flex_server.check_value_security
3474 (p_security_check_mode => 'YH',
3475 p_flex_value_set_id => vsinfo.vsid,
3476 p_parent_flex_value => parentval,
3477 p_flex_value => val,
3478 p_resp_application_id => user_apid,
3479 p_responsibility_id => user_respid,
3480 x_security_status => l_security_status,
3481 x_error_message => l_error_message);
3482 IF (fnd_flex_server1.g_debug_level > 0) THEN
3483 add_debug('Hierarchy security: status :'||l_security_status || ' message : ' || l_error_message);
3484 END IF;
3485
3486 IF (l_security_status <> 'NOT-SECURED') THEN
3487 IF (set_message) THEN
3488 value_error_name('FND', 'FLEX-USER DEFINED ERROR');
3489 value_error_token('MSG', l_error_message);
3490 END IF;
3491 l_return_code := FF_VSECURED;
3492 GOTO label_return;
3493 END IF;
3494
3495 GOTO label_return;
3496
3497
3498 <<label_return>>
3499 IF (l_vsc_code IN (fnd_plsql_cache.CACHE_NOTFOUND)) THEN
3500 update_vsc(user_apid, user_respid, vsinfo.vsid,
3501 parentval, val, l_return_code);
3502 END IF;
3503 IF (fnd_flex_server1.g_debug_level > 0) THEN
3504 add_debug('END SV1.check_security()');
3505 END IF;
3506 RETURN(l_return_code);
3507 EXCEPTION
3508 WHEN OTHERS then
3509 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3510 FND_MESSAGE.set_token('MSG','check_security() exception: ' || SQLERRM);
3511 IF (fnd_flex_server1.g_debug_level > 0) THEN
3512 add_debug('EXCEPTION SV1.check_security()');
3513 END IF;
3514
3515 return(FF_VERROR);
3516
3517 END check_security;
3518
3519 /* ----------------------------------------------------------------------- */
3520 /* Checks to see whether this segment is displayed. */
3521 /* Returns displayed_flag as output. */
3522 /* Returns TRUE on success or FALSE if error or exception. */
3523 /* ----------------------------------------------------------------------- */
3524
3525 FUNCTION check_displayed(segindex IN NUMBER,
3526 disp_tokmap IN DisplayedSegs,
3527 d_flag OUT nocopy BOOLEAN) RETURN BOOLEAN IS
3528
3529 BEGIN
3530
3531 d_flag := disp_tokmap.segflags(segindex);
3532 return(TRUE);
3533
3534 EXCEPTION
3535 WHEN OTHERS then
3536 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3537 FND_MESSAGE.set_token('MSG','check_displayed() exception: ' ||SQLERRM);
3538 return(FALSE);
3539
3540 END check_displayed;
3541
3542 /* ----------------------------------------------------------------------- */
3543 /* Interprets the compiled value attribute (CVA) string and returns */
3544 /* segment qualifier values. From the value set id looks up all */
3545 /* qualifiers which might be associated with this value set and */
3546 /* orders them in the same order as they are stored in the CVA */
3547 /* string. Then interprets the CVA string. For each qualifier */
3548 /* associated with this flexfield segment, the default qualifier value*/
3549 /* is overwritten by the value from the CVA string if the CVA string */
3550 /* contains a non-null value. Returns the updated qualifier values */
3551 /* array and number of qualifier values found or < 0 if error. */
3552 /* No qualifier values for descriptive flexfields. */
3553 /* */
3554 /* Note: Do not order by segment_attribute_type as this causes */
3555 /* GL_ACCOUNT/GL_ACCOUNT_TYPE to appear before GL_GLOBAL/DETAIL... */
3556 /* instead of after it as is the case in the client code. */
3557 /* ----------------------------------------------------------------------- */
3558 FUNCTION qualifier_values(ffstruct IN FlexStructId,
3559 valset_id IN NUMBER,
3560 cvas IN VARCHAR2,
3561 nqualifs IN NUMBER,
3562 fqnames IN QualNameArray,
3563 sqnames IN QualNameArray,
3564 sqvals IN OUT nocopy ValAttribArray) RETURN NUMBER IS
3565
3566 cva_index NUMBER;
3567 n_value_attributes NUMBER;
3568 value_attributes StringArray;
3569 l_vsq_code VARCHAR2(200);
3570 i NUMBER;
3571
3572 CURSOR CVA_Cursor(vs_id IN NUMBER) IS
3573 SELECT id_flex_application_id fapid, id_flex_code fcode,
3574 segment_attribute_type fqname, value_attribute_type sqname
3575 FROM fnd_flex_validation_qualifiers
3576 WHERE flex_value_set_id = vs_id
3577 ORDER BY assignment_date, id_flex_application_id, id_flex_code,
3578 value_attribute_type;
3579 vq cva_cursor%ROWTYPE;
3580 BEGIN
3581 -- Quickly handle case where there are no qualifiers. (eg Descr flex)
3582 --
3583 if((not ffstruct.isa_key_flexfield) or (nqualifs = 0)) then
3584 return(0);
3585 end if;
3586
3587 -- Convert compiled value attributes to array.
3588 --
3589 IF (cvas IS NOT NULL) THEN
3590 n_value_attributes := to_stringarray2(cvas, NEWLINE, value_attributes);
3591 ELSE
3592 n_value_attributes := 0;
3593 END IF;
3594
3595 -- Set returned segment qualifier values to defaults
3596 --
3597 -- for i in 1..nqualifs loop
3598 -- sqvals(i) := default_sqvals(i);
3599 -- end loop;
3600
3601 -- cva_index is position of the value attribute in the CVA string.
3602 --
3603 cva_index := 0;
3604
3605 -- CVA_Cursor returns the flexfields and names of the attibutes associated
3606 -- with this value set in the order that they are stored in the cva string.
3607 --
3608
3609 g_cache_key := valset_id;
3610
3611 fnd_plsql_cache.generic_1tom_get_values(vsq_cache_controller,
3612 vsq_cache_storage,
3613 g_cache_key,
3614 g_cache_numof_values,
3615 g_cache_values,
3616 g_cache_return_code);
3617
3618 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3619 NULL;
3620 ELSE
3621 i := 0;
3622 FOR vq IN cva_cursor(valset_id) LOOP
3623 i := i + 1;
3624 fnd_plsql_cache.generic_cache_new_value
3625 (x_value => g_cache_value,
3626 p_number_1 => vq.fapid,
3627 p_varchar2_1 => vq.fcode,
3628 p_varchar2_2 => vq.fqname,
3629 p_varchar2_3 => vq.sqname);
3630 g_cache_values(i) := g_cache_value;
3631 END LOOP;
3632 g_cache_numof_values := i;
3633
3634 fnd_plsql_cache.generic_1tom_put_values(vsq_cache_controller,
3635 vsq_cache_storage,
3636 g_cache_key,
3637 g_cache_numof_values,
3638 g_cache_values);
3639 END IF;
3640
3641 FOR ii IN 1..g_cache_numof_values LOOP
3642 vq.fapid := g_cache_values(ii).number_1;
3643 vq.fcode := g_cache_values(ii).varchar2_1;
3644 vq.fqname := g_cache_values(ii).varchar2_2;
3645 vq.sqname := g_cache_values(ii).varchar2_3;
3646
3647 -- for vq in CVA_Cursor(valset_id) loop
3648 cva_index := cva_index + 1;
3649
3650 -- If the selected attribute applies to the flexfied under consideration
3651 -- then find the attribute by name in the list of those which apply to this
3652 -- segment and replace its default value with the appropriate value from
3653 -- the cva string.
3654 --
3655 if((vq.fapid = ffstruct.application_id) and
3656 (vq.fcode = ffstruct.id_flex_code)) then
3657 for i in 1..nqualifs loop
3658 if((vq.fqname = fqnames(i)) and (vq.sqname = sqnames(i)) and
3659 (cva_index <= n_value_attributes)) then
3660 sqvals(i) := value_attributes(cva_index);
3661 end if;
3662 end loop;
3663 end if;
3664 end loop;
3665
3666 return(cva_index);
3667
3668 EXCEPTION
3669 WHEN OTHERS then
3670 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3671 FND_MESSAGE.set_token('MSG', 'qualifier_values() exception: '||SQLERRM);
3672 return(-1);
3673
3674 END qualifier_values;
3675
3676 /* ----------------------------------------------------------------------- */
3677 /* Gets the **enabled** flexfield and segment qualifiers for this */
3678 /* segment. Segment is uniquely determined by combination of */
3679 /* flex apid, code, struct num, with the name of the column in the */
3680 /* code combinations table which will hold the segment value. */
3681 /* No qualifiers enabled for descriptive flexs. */
3682 /* */
3683 /* Returns the number of qualifiers found or < 0 if error. */
3684 /* */
3685 /* Also returns FQ name, SQ name, derived_column, and default value */
3686 /* of the segment qualifiers for this segment. */
3687 /* ----------------------------------------------------------------------- */
3688 FUNCTION get_qualifiers(ffstruct IN FlexStructId,
3689 seg_colname IN VARCHAR2,
3690 seg_quals OUT nocopy Qualifiers)
3691 RETURN NUMBER
3692 IS
3693 sqcount NUMBER;
3694
3695 CURSOR Qual_Cursor(keystruct IN FlexStructId, colname IN VARCHAR2) IS
3696 SELECT v.segment_attribute_type fq_name,
3697 v.value_attribute_type sq_name,
3698 v.application_column_name drv_colname,
3699 v.default_value dflt_val
3700 FROM fnd_value_attribute_types v, fnd_segment_attribute_values s
3701 WHERE v.application_id = s.application_id
3702 AND v.id_flex_code = s.id_flex_code
3703 AND v.segment_attribute_type = s.segment_attribute_type
3704 AND s.application_id = keystruct.application_id
3705 AND s.id_flex_code = keystruct.id_flex_code
3706 AND s.id_flex_num = keystruct.id_flex_num
3707 AND s.application_column_name = colname
3708 AND s.attribute_value = 'Y';
3709 BEGIN
3710 IF (fnd_flex_server1.g_debug_level > 0) THEN
3711 fnd_flex_server1.add_debug('BEGIN SV1.get_qualifiers()');
3712 END IF;
3713
3714 sqcount := 0;
3715 IF (ffstruct.isa_key_flexfield) THEN
3716 g_cache_key := (ffstruct.application_id || '.' ||
3717 ffstruct.id_flex_code || '.' ||
3718 ffstruct.id_flex_num || '.' ||
3719 seg_colname);
3720
3721 fnd_plsql_cache.generic_1tom_get_values(fsq_cache_controller,
3722 fsq_cache_storage,
3723 g_cache_key,
3724 g_cache_numof_values,
3725 g_cache_values,
3726 g_cache_return_code);
3727
3728 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3729 sqcount := g_cache_numof_values;
3730 ELSE
3731 FOR squal IN qual_cursor(ffstruct, seg_colname) LOOP
3732 sqcount := sqcount + 1;
3733
3734 fnd_plsql_cache.generic_cache_new_value
3735 (x_value => g_cache_value,
3736 p_varchar2_1 => squal.fq_name,
3737 p_varchar2_2 => squal.sq_name,
3738 p_varchar2_3 => squal.dflt_val,
3739 p_varchar2_4 => squal.drv_colname);
3740 g_cache_values(sqcount) := g_cache_value;
3741 END LOOP;
3742 g_cache_numof_values := sqcount;
3743
3744 fnd_plsql_cache.generic_1tom_put_values(fsq_cache_controller,
3745 fsq_cache_storage,
3746 g_cache_key,
3747 g_cache_numof_values,
3748 g_cache_values);
3749 END IF;
3750
3751 FOR ii IN 1..sqcount LOOP
3752 seg_quals.fq_names(ii) := g_cache_values(ii).varchar2_1;
3753 seg_quals.sq_names(ii) := g_cache_values(ii).varchar2_2;
3754 seg_quals.sq_values(ii) := g_cache_values(ii).varchar2_3;
3755 seg_quals.derived_cols(ii) := g_cache_values(ii).varchar2_4;
3756 END LOOP;
3757 END IF;
3758
3759 seg_quals.nquals := sqcount;
3760
3761 IF (fnd_flex_server1.g_debug_level > 0) THEN
3762 fnd_flex_server1.add_debug('END SV1.get_qualifiers()');
3763 END IF;
3764
3765 RETURN(sqcount);
3766 EXCEPTION
3767 WHEN OTHERS then
3768 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3769 FND_MESSAGE.set_token('MSG', 'get_qualifiers() exception: ' || SQLERRM);
3770 IF (fnd_flex_server1.g_debug_level > 0) THEN
3771 fnd_flex_server1.add_debug('EXCEPTION SV1.get_qualifiers()');
3772 END IF;
3773
3774 return(-1);
3775
3776 END get_qualifiers;
3777
3778 /* ----------------------------------------------------------------------- */
3779 /* Gets the value set information for a given value set id. */
3780 /* Returns TRUE if found or FALSE and sets error message on error. */
3781 /* ----------------------------------------------------------------------- */
3782 FUNCTION get_value_set(value_set_id IN NUMBER,
3783 segment_name IN VARCHAR2,
3784 vs_info OUT nocopy ValueSetInfo)
3785 RETURN BOOLEAN
3786 IS
3787 l_vsi valuesetinfo;
3788 BEGIN
3789 IF (fnd_flex_server1.g_debug_level > 0) THEN
3790 fnd_flex_server1.add_debug('CALL SV1.get_value_set(' ||
3791 'vsid:' || value_set_id || ')');
3792 END IF;
3793
3794 g_cache_key := value_set_id;
3795 fnd_plsql_cache.generic_1to1_get_value(vst_cache_controller,
3796 vst_cache_storage,
3797 g_cache_key,
3798 g_cache_value,
3799 g_cache_return_code);
3800
3801 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3802 vs_info.vsid := value_set_id;
3803 vs_info.parent_vsid := g_cache_value.number_1;
3804 vs_info.valsecure := g_cache_value.varchar2_1;
3805 vs_info.valtype := g_cache_value.varchar2_2;
3806 vs_info.vsformat := g_cache_value.varchar2_3;
3807 vs_info.maxsize := g_cache_value.number_2;
3808 vs_info.lettersok := g_cache_value.varchar2_4;
3809 vs_info.capsonly := g_cache_value.varchar2_5;
3810 vs_info.zfill := g_cache_value.varchar2_6;
3811 vs_info.precis := g_cache_value.number_3;
3812 vs_info.minval := g_cache_value.varchar2_7;
3813 vs_info.maxval := g_cache_value.varchar2_8;
3814 vs_info.vsname := g_cache_value.varchar2_9;
3815 ELSE
3816 SELECT flex_value_set_id, parent_flex_value_set_id,
3817 security_enabled_flag,
3818 validation_type, format_type, maximum_size,
3819 alphanumeric_allowed_flag, uppercase_only_flag,
3820 numeric_mode_enabled_flag, number_precision, minimum_value,
3821 maximum_value, flex_value_set_name
3822 INTO l_vsi
3823 FROM fnd_flex_value_sets
3824 WHERE flex_value_set_id = value_set_id;
3825
3826 fnd_plsql_cache.generic_cache_new_value
3827 (x_value => g_cache_value,
3828 p_number_1 => l_vsi.parent_vsid,
3829 p_varchar2_1 => l_vsi.valsecure,
3830 p_varchar2_2 => l_vsi.valtype,
3831 p_varchar2_3 => l_vsi.vsformat,
3832 p_number_2 => l_vsi.maxsize,
3833 p_varchar2_4 => l_vsi.lettersok,
3834 p_varchar2_5 => l_vsi.capsonly,
3835 p_varchar2_6 => l_vsi.zfill,
3836 p_number_3 => l_vsi.precis,
3837 p_varchar2_7 => l_vsi.minval,
3838 p_varchar2_8 => l_vsi.maxval,
3839 p_varchar2_9 => l_vsi.vsname);
3840
3841 fnd_plsql_cache.generic_1to1_put_value(vst_cache_controller,
3842 vst_cache_storage,
3843 g_cache_key,
3844 g_cache_value);
3845 vs_info := l_vsi;
3846 END IF;
3847 return(TRUE);
3848 EXCEPTION
3849 WHEN NO_DATA_FOUND then
3850 FND_MESSAGE.set_name('FND', 'FLEX-VALUE SET NOT FOUND');
3851 FND_MESSAGE.set_token('SEGMENT', segment_name);
3852 return(FALSE);
3853 WHEN OTHERS then
3854 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3855 FND_MESSAGE.set_token('MSG', 'get_value_set() exception: ' || SQLERRM);
3856 return(FALSE);
3857 END get_value_set;
3858
3859 /* ----------------------------------------------------------------------- */
3860 /* Creates value set information for segment without a value set */
3861 /* based on the specification of the column into which it will go. */
3862 /* We document that this only works for CHAR or VARCHAR2 type columns.*/
3863 /* Returns TRUE if ok or FALSE and sets error message on error. */
3864 /* ----------------------------------------------------------------------- */
3865 FUNCTION virtual_value_set(column_type IN VARCHAR2,
3866 column_width IN NUMBER,
3867 segment_name IN VARCHAR2,
3868 vs_info OUT nocopy ValueSetInfo) RETURN BOOLEAN IS
3869 BEGIN
3870
3871 -- Assumes all components of vs_info are initially null
3872 --
3873 if(column_type in ('C', 'V')) then
3874 vs_info.valsecure := 'N';
3875 vs_info.valtype := 'N';
3876 vs_info.vsformat := 'C';
3877 vs_info.maxsize := column_width;
3878 vs_info.lettersok := 'Y';
3879 vs_info.capsonly := 'N';
3880 vs_info.zfill := 'N';
3881 return(TRUE);
3882 end if;
3883 FND_MESSAGE.set_name('FND', 'FLEX-VALUE SET REQUIRED');
3884 FND_MESSAGE.set_token('SEGMENT', segment_name);
3885 return(FALSE);
3886
3887 EXCEPTION
3888 WHEN OTHERS then
3889 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3890 FND_MESSAGE.set_token('MSG','virtual_value_set() exception: '||SQLERRM);
3891 return(FALSE);
3892
3893 END virtual_value_set;
3894
3895 /* ----------------------------------------------------------------------- */
3896 /* Message handler functions for value validation errors. Needed */
3897 /* because value validation does not always stop at the first */
3898 /* error encountered. This handler makes sure that FND_MESSAGE */
3899 /* error is set to the first value error if there are no other */
3900 /* errors. For more serious errors, we set the message directly */
3901 /* using the FND_MESSAGE package. Note this assumes the main */
3902 /* validate_struct() function knows to stop on the first serious */
3903 /* error otherwise we risk overwriting a more serious error with */
3904 /* a value error. */
3905 /* If the application short name is null, then we use the */
3906 /* FLEX-USER DEFINED ERROR to display the message name as text. */
3907 /* This is used in check_vrules(). */
3908 /* ----------------------------------------------------------------------- */
3909
3910 /* ----------------------------------------------------------------------- */
3911 /* Initializes value error message global variables. */
3912 /* ----------------------------------------------------------------------- */
3913
3914 PROCEDURE value_error_init IS
3915 BEGIN
3916 value_error_set := FALSE;
3917 entering_new_message := FALSE;
3918 EXCEPTION
3919 WHEN OTHERS then
3920 IF (fnd_flex_server1.g_debug_level > 0) THEN
3921 add_debug('value_error_init() exception: ' || SQLERRM);
3922 END IF;
3923 END value_error_init;
3924
3925 /* ------------------------------------------------------------------------ */
3926 /* Sets error message name if no other value errors have been set. */
3927 /* Also sets entering_new_message flag to indicate new message being */
3928 /* entered if this is the first value error, otherwise resets it. */
3929 /* ------------------------------------------------------------------------ */
3930 PROCEDURE value_error_name(appl_sname IN VARCHAR2,
3931 errmsg_name IN VARCHAR2) IS
3932 BEGIN
3933 if(value_error_set = FALSE) then
3934 if(appl_sname is null) then
3935 FND_MESSAGE.set_name('FND', 'FLEX-USER DEFINED ERROR');
3936 FND_MESSAGE.set_token('MSG', errmsg_name);
3937 else
3938 FND_MESSAGE.set_name(appl_sname, errmsg_name);
3939 end if;
3940 value_error_set := TRUE;
3941 entering_new_message := TRUE;
3942 else
3943 entering_new_message := FALSE;
3944 end if;
3945 EXCEPTION
3946 WHEN OTHERS then
3947 IF (fnd_flex_server1.g_debug_level > 0) THEN
3948 add_debug('value_error_name() exception: ' || SQLERRM);
3949 END IF;
3950 END value_error_name;
3951
3952 /* ----------------------------------------------------------------------- */
3953 /* Sets error message token if currently entering a new message. */
3954 /* Otherwise just ignores the token. Call only after setting */
3955 /* value error name with value_error_name() above. */
3956 /* ----------------------------------------------------------------------- */
3957 PROCEDURE value_error_token(token_name IN VARCHAR2,
3958 token_value IN VARCHAR2) IS
3959 BEGIN
3960 if(entering_new_message) then
3961 FND_MESSAGE.set_token(token_name, token_value);
3962 end if;
3963 EXCEPTION
3964 WHEN OTHERS then
3965 IF (fnd_flex_server1.g_debug_level > 0) THEN
3966 add_debug('value_error_token() exception: ' || SQLERRM);
3967 END IF;
3968 END value_error_token;
3969
3970 /* ----------------------------------------------------------------------- */
3971 /* Converts the value to be output in an error message from stored */
3972 /* format to the displayed format appropriate for the given value set.*/
3973 /* Returns the value in stored format if any errors encountered. */
3974 /* ----------------------------------------------------------------------- */
3975 FUNCTION msg_val(valset_fmt IN VARCHAR2,
3976 valset_len IN NUMBER,
3977 valset_prec IN NUMBER,
3978 valset_lettersok IN VARCHAR2,
3979 stored_val IN VARCHAR2) RETURN VARCHAR2 IS
3980 d_val VARCHAR2(1000);
3981 BEGIN
3982 if(stored_to_displayed(valset_fmt, valset_len,
3983 valset_prec, valset_lettersok,
3984 stored_val, d_val) <> FF_VVALID) then
3985 d_val := stored_val;
3986 end if;
3987 return(d_val);
3988 EXCEPTION
3989 WHEN OTHERS then
3990 return(stored_val);
3991 END msg_val;
3992
3993 /* ----------------------------------------------------------------------- */
3994 /* Converts value from stored format to displayed format. */
3995 /* Returns FF_VVALID on success, or FF_VERROR otherwise. */
3996 /* ----------------------------------------------------------------------- */
3997 FUNCTION stored_to_displayed(valset_fmt IN VARCHAR2,
3998 valset_len IN NUMBER,
3999 valset_prec IN NUMBER,
4000 valset_lettersok IN VARCHAR2,
4001 stored_val IN VARCHAR2,
4002 disp_val OUT nocopy VARCHAR2) RETURN VARCHAR2 IS
4003 datebuf DATE;
4004 BEGIN
4005 if(valset_fmt in ('X', 'Y', 'Z')) then
4006 if(isa_stored_date(stored_val, valset_fmt, datebuf)) then
4007 disp_val := to_char(datebuf,displayed_date_format(valset_fmt,valset_len));
4008 else
4009 return(FF_VERROR);
4010 end if;
4011 ELSIF ((valset_fmt = 'N') OR
4012 ((valset_fmt = 'C') AND
4013 (valset_lettersok = 'N'))) THEN
4014 disp_val := REPLACE(stored_val,m_nc,m_nd);
4015 else
4016 disp_val := stored_val;
4017 end if;
4018
4019 return(FF_VVALID);
4020
4021 EXCEPTION
4022 WHEN OTHERS then
4023 return(FF_VERROR);
4024 END stored_to_displayed;
4025
4026 /* ----------------------------------------------------------------------- */
4027 /* Converts character representation of a number to a number. */
4028 /* Returns TRUE if it's a valid number, and FALSE otherwise. */
4029 /* ----------------------------------------------------------------------- */
4030 FUNCTION isa_number(teststr IN VARCHAR2,
4031 outnum OUT nocopy NUMBER) RETURN BOOLEAN IS
4032 BEGIN
4033 outnum := to_number(teststr);
4034 return(TRUE);
4035 EXCEPTION
4036 WHEN OTHERS then
4037 return(FALSE);
4038 END isa_number;
4039
4040 /* ----------------------------------------------------------------------- */
4041 /* Converts character representation of a stored date to a date. */
4042 /* Returns TRUE if it's a valid date, and FALSE otherwise. */
4043 /* ----------------------------------------------------------------------- */
4044 FUNCTION isa_stored_date(teststr IN VARCHAR2,
4045 flexfmt IN VARCHAR2,
4046 outdate OUT nocopy DATE) RETURN BOOLEAN IS
4047 BEGIN
4048 if(teststr is null) then
4049 return(TRUE);
4050 end if;
4051 return(isa_date(teststr, stored_date_format(flexfmt, LENGTH(teststr)),
4052 outdate));
4053 EXCEPTION
4054 WHEN OTHERS then
4055 IF (fnd_flex_server1.g_debug_level > 0) THEN
4056 add_debug('isa_stored_date() exception: ' || SQLERRM);
4057 END IF;
4058 return(FALSE);
4059 END isa_stored_date;
4060
4061
4062 /* ----------------------------------------------------------------------- */
4063 /* Converts character representation of a displayed date to a date. */
4064 /* Returns TRUE if it's a valid date, and FALSE otherwise. */
4065 /* ----------------------------------------------------------------------- */
4066 FUNCTION isa_displayed_date(teststr IN VARCHAR2,
4067 flexfmt IN VARCHAR2,
4068 outdate OUT nocopy DATE) RETURN BOOLEAN IS
4069 BEGIN
4070 if(teststr is null) then
4071 return(TRUE);
4072 end if;
4073 return(isa_date(teststr, displayed_date_format(flexfmt, LENGTH(teststr)),
4074 outdate));
4075 EXCEPTION
4076 WHEN OTHERS then
4077 IF (fnd_flex_server1.g_debug_level > 0) THEN
4078 add_debug('isa_displayed_date() exception: ' || SQLERRM);
4079 END IF;
4080 return(FALSE);
4081 END isa_displayed_date;
4082
4083 /* ----------------------------------------------------------------------- */
4084 /* Converts test string to date using supplied format. */
4085 /* If date conversion fails or supplied format is null, returns FALSE */
4086 /* otherwise returns TRUE. */
4087 /* This function is a wrapper around to_date() to make it always work */
4088 /* even if the supplied format is 'DD-MON-YY' (See bug) */
4089 /* ----------------------------------------------------------------------- */
4090 FUNCTION isa_date(teststr IN VARCHAR2,
4091 datefmt IN VARCHAR2,
4092 outdate OUT nocopy DATE) RETURN BOOLEAN IS
4093 BEGIN
4094 if(teststr is null) then
4095 return(TRUE);
4096 end if;
4097 if(datefmt is null) then
4098 return(FALSE);
4099 elsif(datefmt = 'DD-MON-YY') then
4100 outdate := to_date(teststr, datefmt);
4101 elsif(datefmt = 'DD-MON-RR') then
4102 outdate := to_date(teststr, datefmt);
4103 else
4104 outdate := to_date(teststr, datefmt);
4105 end if;
4106 return(TRUE);
4107 EXCEPTION
4108 WHEN OTHERS then
4109 IF (fnd_flex_server1.g_debug_level > 0) THEN
4110 add_debug('isa_date() exception: ' || SQLERRM);
4111 END IF;
4112 return(FALSE);
4113 END isa_date;
4114
4115 /* ----------------------------------------------------------------------- */
4116 /* Returns format string for converting a date to a character string */
4117 /* that represents the displayed value of a flex date, time, or */
4118 /* date-time. */
4119 /* Returns NULL if not valid flex date format. */
4120 /* ----------------------------------------------------------------------- */
4121 FUNCTION displayed_date_format(flex_data_type IN VARCHAR2,
4122 string_length IN NUMBER) RETURN VARCHAR2 IS
4123
4124 l_format_in VARCHAR2(500);
4125 l_format_out VARCHAR2(500);
4126 BEGIN
4127 IF (fnd_flex_val_util.get_display_format
4128 (p_vset_format => flex_data_type,
4129 p_max_length => string_length,
4130 p_precision => NULL,
4131 x_format_in => l_format_in,
4132 x_format_out => l_format_out)) THEN
4133 RETURN(l_format_out);
4134 ELSE
4135 RETURN(NULL);
4136 END IF;
4137
4138 EXCEPTION
4139 WHEN OTHERS then
4140 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4141 FND_MESSAGE.set_token('MSG',
4142 'displayed_date_format() exception: ' || SQLERRM);
4143 return(NULL);
4144
4145 END displayed_date_format;
4146
4147 /* ----------------------------------------------------------------------- */
4148 /* Returns format string for converting character representation */
4149 /* of stored flex date, time, or date-time data to a date datatype */
4150 /* using the to_date() conversion utility. */
4151 /* Returns NULL if not valid flex date format. */
4152 /* Modified for DD-MON-RR conversion in Prod 16 12-26-96 */
4153 /* ----------------------------------------------------------------------- */
4154 FUNCTION stored_date_format(flex_data_type IN VARCHAR2,
4155 string_length IN NUMBER) RETURN VARCHAR2 IS
4156
4157 l_format VARCHAR2(500);
4158 BEGIN
4159 IF (fnd_flex_val_util.get_storage_format(p_vset_format => flex_data_type,
4160 p_max_length => string_length,
4161 p_precision => NULL,
4162 x_format => l_format)) THEN
4163 RETURN(l_format);
4164 ELSE
4165 RETURN(NULL);
4166 END IF;
4167
4168 EXCEPTION
4169 WHEN OTHERS then
4170 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4171 FND_MESSAGE.set_token('MSG',
4172 'stored_date_format() exception: ' || SQLERRM);
4173 return(NULL);
4174
4175 END stored_date_format;
4176
4177 /* ----------------------------------------------------------------------- */
4178 /* Generally useful utilities - Externalized */
4179 /* ----------------------------------------------------------------------- */
4180
4181 /* ----------------------------------------------------------------------- */
4182 /* Initializes all global variables */
4183 /* ----------------------------------------------------------------------- */
4184 FUNCTION init_globals RETURN BOOLEAN IS
4185
4186 BEGIN
4187
4188 n_sqlstrings := 0;
4189 g_debug_array_size := 0;
4190 return(TRUE);
4191
4192 EXCEPTION
4193 WHEN OTHERS then
4194 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4195 FND_MESSAGE.set_token('MSG', 'init_globals() exception: ' || SQLERRM);
4196 return(FALSE);
4197
4198 END init_globals;
4199
4200 /* ----------------------------------------------------------------------- */
4201 /* Creates an "and clause" of a SQL select statement for determining */
4202 /* if the value passed in is in between the values in the columns */
4203 /* whose names are in mincol and maxcol. Returns NULL if no */
4204 /* statement required for this column */
4205 /* */
4206 /* By isolating all in range clauses to this routine we can ensure */
4207 /* uniform range behavior. */
4208 /* */
4209 /* Null handling: If val is NULL, requires either mincol or maxcol */
4210 /* to be null. If the value contained in mincol (maxcol) is NULL it */
4211 /* means there is no lower(upper) limit on the range of val */
4212 /* */
4213 /* Clause is of the form: */
4214 /* 'and (10 between nvl(to_number(MINCOL), 10) */
4215 /* and nvl(to_number(MAXCOL), 10)) ' */
4216 /* */
4217 /* ----------------------------------------------------------------------- */
4218 PROCEDURE x_inrange_clause(valstr IN VARCHAR2,
4219 valtype IN VARCHAR2,
4220 mincol IN VARCHAR2,
4221 maxcol IN VARCHAR2) IS
4222
4223 clause VARCHAR2(500);
4224 val VARCHAR2(200);
4225 collo VARCHAR2(200);
4226 colhi VARCHAR2(200);
4227 datefmt VARCHAR2(24);
4228
4229 BEGIN
4230 fnd_dsql.add_text('and (');
4231 IF (valstr IS NULL) THEN
4232 fnd_dsql.add_text(mincol || ' is null or ' ||
4233 maxcol || ' is null) ');
4234 ELSE
4235 IF (valtype = 'N') THEN
4236 fnd_dsql.add_bind(fnd_number.canonical_to_number(valstr));
4237
4238 fnd_dsql.add_text(' between nvl(fnd_number.canonical_to_number('
4239 || mincol || '),');
4240 fnd_dsql.add_bind(fnd_number.canonical_to_number(valstr));
4241
4242 fnd_dsql.add_text(') and nvl(fnd_number.canonical_to_number('
4243 || maxcol || '),');
4244 fnd_dsql.add_bind(fnd_number.canonical_to_number(valstr));
4245
4246 ELSIF (valtype in ('D', 'T', 't', 'I', 'X', 'Y', 'Z')) THEN
4247 datefmt := stored_date_format(valtype, LENGTH(valstr));
4248 fnd_dsql.add_bind(To_date(valstr, datefmt));
4249
4250 fnd_dsql.add_text(' between nvl(to_date(' ||
4251 mincol || ',''' || datefmt || '''),');
4252 fnd_dsql.add_bind(To_date(valstr, datefmt));
4253
4254 fnd_dsql.add_text(') and nvl(to_date(' ||
4255 maxcol || ',''' || datefmt || '''),');
4256 fnd_dsql.add_bind(To_date(valstr, datefmt));
4257
4258 ELSE
4259 fnd_dsql.add_bind(valstr);
4260
4261 fnd_dsql.add_text(' between nvl(' || mincol || ',');
4262 fnd_dsql.add_bind(valstr);
4263
4264 fnd_dsql.add_text(') and nvl(' || maxcol || ',');
4265 fnd_dsql.add_bind(valstr);
4266
4267 END IF;
4268 fnd_dsql.add_text(')) ');
4269 END IF;
4270 END x_inrange_clause;
4271
4272 /* ----------------------------------------------------------------------- */
4273 /* Function to convert a column name into a SQL clause for selecting */
4274 /* a value, id, or description from that column into the correct */
4275 /* character format for the given value set. */
4276 /* */
4277 /* This function does the default to_char() conversion for */
4278 /* non-translatable date, time, date-time, or number value sets */
4279 /* in order to maintain backward compatibility with old client code. */
4280 /* For translatable date, time, and date-time value sets this code */
4281 /* converts the data stored in the date column to date storage format */
4282 /* */
4283 /* Does not check for compatibility of column type and value set. */
4284 /* ----------------------------------------------------------------------- */
4285
4286 FUNCTION select_clause(colname IN VARCHAR2,
4287 coltype IN VARCHAR2,
4288 v_component IN BINARY_INTEGER,
4289 vs_fmt IN VARCHAR2,
4290 vs_len IN NUMBER) RETURN VARCHAR2 IS
4291
4292 clause VARCHAR2(2000);
4293
4294 BEGIN
4295
4296 if(coltype not in ('C', 'V')) then
4297 clause := 'to_char(' || colname;
4298 if(vs_fmt in ('X', 'Y', 'Z')) then
4299 clause := clause || ', ''';
4300 if((v_component = VC_ID) OR (v_component = VC_VALUE)) then
4301 clause := clause || stored_date_format(vs_fmt, vs_len) || ''')';
4302 else
4303 clause := clause || displayed_date_format(vs_fmt, vs_len) || ''')';
4304 end if;
4305 ELSIF (vs_fmt = 'N') THEN
4306 clause := clause || ')';
4307 clause := 'replace(' || clause || ',''' || m_nb || ''','''
4308 || m_nc || ''')';
4309 else
4310 clause := clause || ')';
4311 end if;
4312 else
4313 clause := colname;
4314 end if;
4315 return(clause);
4316
4317 END select_clause;
4318
4319 /* ----------------------------------------------------------------------- */
4320 /* Function to convert a value into a SQL clause for comparing that */
4321 /* value into a table column of the given type, or for inserting that */
4322 /* value to a value stored in a table column of the given type. */
4323 /* */
4324 /* If the table column is a number assume the value input is a number */
4325 /* and just use the text of the number. If the table column is */
4326 /* CHAR or VARCHAR2, then substitute all single quotes in the value */
4327 /* with double quotes and surround the value with single quotes. */
4328 /* If table column is DATE, do to_date() conversion using the format */
4329 /* appropriate for the value set. */
4330 /* */
4331 /* To maintin backward compatibility with existing client code must */
4332 /* build in the BUG that if value set format type is old-fashioned */
4333 /* date, time or date-time (D, T or t), then default to_date() */
4334 /* conversions are done. This means non-validated Date-time values */
4335 /* cannot be inserted into the combinations table. */
4336 /* ----------------------------------------------------------------------- */
4337
4338 PROCEDURE x_compare_clause(coltype IN VARCHAR2,
4339 colname IN VARCHAR2,
4340 char_val IN VARCHAR2,
4341 v_component IN BINARY_INTEGER,
4342 vs_fmt IN VARCHAR2,
4343 vs_len IN NUMBER)
4344 IS
4345 datefmt VARCHAR2(30);
4346 BEGIN
4347 IF (coltype = 'N') THEN
4348 fnd_dsql.add_bind(fnd_number.canonical_to_number(char_val));
4349 ELSIF (coltype = 'D') then
4350 IF (vs_fmt in ('X', 'Y', 'Z', 'D', 'T', 'I', 't')) then
4351 datefmt := stored_date_format(vs_fmt, vs_len);
4352 fnd_dsql.add_bind(To_date(char_val, datefmt));
4353 ELSE
4354 fnd_dsql.add_bind(char_val);
4355 end if;
4356 else
4357 fnd_dsql.add_bind(char_val);
4358 end if;
4359
4360 END x_compare_clause;
4361
4362 /* ----------------------------------------------------------------------- */
4363 /* Creates a SQL clause for binding a character string. */
4364 /* Replaces single quotes with double quotes and surrounds the */
4365 /* string with quotes. */
4366 /* ----------------------------------------------------------------------- */
4367
4368 FUNCTION string_clause(char_string IN VARCHAR2) RETURN VARCHAR2 IS
4369 BEGIN
4370 return('''' || REPLACE(char_string, '''', '''''') || '''');
4371 END string_clause;
4372
4373 -- /* ----------------------------------------------------------------------- */
4374 -- /* MADE NULL TO MAKE MORE ROOM FOR USEFUL CODE. */
4375 -- /* Breaks concatenated segments in rule lines table into separate */
4376 -- /* columns for each segment. Returns number of segments or < 0 */
4377 -- /* and sets FND_MESSAGE if error. This is called only from trigger */
4378 -- /* FND_FLEX_VALIDATION_RULE_LINES_T1. The trigger should use */
4379 -- /* FND_MESSAGE.raise_exception if this function returns error. */
4380 -- /* ----------------------------------------------------------------------- */
4381 -- FUNCTION breakup_segs(appid IN NUMBER,
4382 -- flex_code IN VARCHAR2, flex_num IN NUMBER,
4383 -- catsegs IN VARCHAR2, nsegs OUT NUMBER,
4384 -- seg1 OUT VARCHAR2, seg2 OUT VARCHAR2,
4385 -- seg3 OUT VARCHAR2, seg4 OUT VARCHAR2,
4386 -- seg5 OUT VARCHAR2, seg6 OUT VARCHAR2,
4387 -- seg7 OUT VARCHAR2, seg8 OUT VARCHAR2,
4388 -- seg9 OUT VARCHAR2, seg10 OUT VARCHAR2,
4389 -- seg11 OUT VARCHAR2, seg12 OUT VARCHAR2,
4390 -- seg13 OUT VARCHAR2, seg14 OUT VARCHAR2,
4391 -- seg15 OUT VARCHAR2, seg16 OUT VARCHAR2,
4392 -- seg17 OUT VARCHAR2, seg18 OUT VARCHAR2,
4393 -- seg19 OUT VARCHAR2, seg20 OUT VARCHAR2,
4394 -- seg21 OUT VARCHAR2, seg22 OUT VARCHAR2,
4395 -- seg23 OUT VARCHAR2, seg24 OUT VARCHAR2,
4396 -- seg25 OUT VARCHAR2, seg26 OUT VARCHAR2,
4397 -- seg27 OUT VARCHAR2, seg28 OUT VARCHAR2,
4398 -- seg29 OUT VARCHAR2, seg30 OUT VARCHAR2) RETURN NUMBER IS
4399 --
4400 -- n_segs NUMBER;
4401 -- sg StringArray;
4402 -- sepchar VARCHAR2(1);
4403 --
4404 -- BEGIN
4405 -- return(-1);
4406 --
4407 -- EXCEPTION
4408 -- WHEN OTHERS then
4409 -- FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4410 -- FND_MESSAGE.set_token('MSG', 'breakup_segs() exception: ' || SQLERRM);
4411 -- return(-2);
4412 --
4413 -- END breakup_segs;
4414
4415 /* ----------------------------------------------------------------------- */
4416 /* Executes a dynamic SQL statement. */
4417 /* Returns number of rows processed or -1 if error (add_message) */
4418 /* ----------------------------------------------------------------------- */
4419 FUNCTION x_dsql_execute RETURN NUMBER IS
4420
4421 cursornum INTEGER;
4422 nprocessed INTEGER;
4423 sql_statement VARCHAR2(32000);
4424
4425 BEGIN
4426
4427 -- Copy SQL string to array for debugging purposes
4428 --
4429 add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
4430
4431 cursornum := dbms_sql.open_cursor;
4432 fnd_dsql.set_cursor(cursornum);
4433
4434 sql_statement := fnd_dsql.get_text(p_with_debug => FALSE);
4435 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
4436
4437 fnd_dsql.do_binds;
4438
4439 nprocessed := dbms_sql.execute(cursornum);
4440 IF (fnd_flex_server1.g_debug_level > 0) THEN
4441 add_debug('(DSQL_execute processed ' || to_char(nprocessed));
4442 add_debug(' rows.)');
4443 END IF;
4444 dbms_sql.close_cursor(cursornum);
4445 return(nprocessed);
4446
4447 EXCEPTION
4448 WHEN OTHERS then
4449 if(dbms_sql.is_open(cursornum)) then
4450 dbms_sql.close_cursor(cursornum);
4451 end if;
4452 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
4453 FND_MESSAGE.set_token('MSG', SQLERRM);
4454 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_statement, 1, 1000));
4455 return(-1);
4456
4457 END x_dsql_execute;
4458
4459 /* ----------------------------------------------------------------------- */
4460 /* Uses dynamic SQL to select up to one varchar2 valued column from */
4461 /* a table using the select statement passed in. Returns 0, NULL */
4462 /* if no rows found, or 1 and the column value if 1 row found, or */
4463 /* 2 and the column value of the first row found if more than 1 row */
4464 /* matches selection criteria, or < 0 if other errors. */
4465 /* Invalid rowid exception mapped back to no data found. */
4466 /* ----------------------------------------------------------------------- */
4467 FUNCTION x_dsql_select_one(returned_column OUT nocopy VARCHAR2) RETURN NUMBER IS
4468 cursornum INTEGER;
4469 num_returned INTEGER;
4470 selected_col VARCHAR2(2000);
4471 invalid_rowid EXCEPTION;
4472 sql_statement VARCHAR2(32000);
4473
4474 PRAGMA EXCEPTION_INIT(invalid_rowid, -1410);
4475
4476 BEGIN
4477
4478 -- Copy SQL string to array for debugging purposes
4479 --
4480 add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
4481
4482 selected_col := NULL;
4483 cursornum := dbms_sql.open_cursor;
4484 fnd_dsql.set_cursor(cursornum);
4485
4486 sql_statement := fnd_dsql.get_text(p_with_debug => FALSE);
4487 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
4488
4489 fnd_dsql.do_binds;
4490
4491 dbms_sql.define_column(cursornum, 1, selected_col, 2000);
4492 num_returned := dbms_sql.execute_and_fetch(cursornum, TRUE);
4493 if(num_returned = 1) then
4494 dbms_sql.column_value(cursornum, 1, selected_col);
4495 returned_column := selected_col;
4496 IF (fnd_flex_server1.g_debug_level > 0) THEN
4497 add_debug('(DSQL returned ' || selected_col || ')');
4498 END IF;
4499 else
4500 num_returned := -1;
4501 returned_column := NULL;
4502 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4503 FND_MESSAGE.set_token('MSG', 'x_dsql_select_one() could not fetch rows');
4504 end if;
4505 dbms_sql.close_cursor(cursornum);
4506 return(num_returned);
4507
4508 EXCEPTION
4509 WHEN NO_DATA_FOUND or invalid_rowid then
4510 returned_column := NULL;
4511 IF (fnd_flex_server1.g_debug_level > 0) THEN
4512 add_debug('(DSQL returned: NULL)');
4513 END IF;
4514 dbms_sql.close_cursor(cursornum);
4515 return(0);
4516 WHEN TOO_MANY_ROWS then
4517 dbms_sql.column_value(cursornum, 1, selected_col);
4518 returned_column := selected_col;
4519 IF (fnd_flex_server1.g_debug_level > 0) THEN
4520 add_debug('(DSQL returned: TOO MANY ROWS)');
4521 END IF;
4522 dbms_sql.close_cursor(cursornum);
4523 return(2);
4524 WHEN OTHERS then
4525 if(dbms_sql.is_open(cursornum)) then
4526 dbms_sql.close_cursor(cursornum);
4527 end if;
4528 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
4529 FND_MESSAGE.set_token('MSG', SQLERRM);
4530 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_statement, 1, 1000));
4531 return(-2);
4532
4533 END x_dsql_select_one;
4534
4535 /* ----------------------------------------------------------------------- */
4536 /* Uses dynamic SQL to select n_selected_cols of VARCHAR2 type from */
4537 /* a table using the select statement passed in. Returns number */
4538 /* of rows found, or sets error and returns < 0 if error. */
4539 /* Invalid rowid exception mapped back to no data found. */
4540 /* ----------------------------------------------------------------------- */
4541 FUNCTION x_dsql_select(n_selected_cols IN NUMBER,
4542 returned_columns OUT nocopy StringArray) RETURN NUMBER
4543 IS
4544 cursornum INTEGER;
4545 num_returned INTEGER;
4546 selected_cols StringArray;
4547 invalid_rowid EXCEPTION;
4548 sql_statement VARCHAR2(32000);
4549
4550 PRAGMA EXCEPTION_INIT(invalid_rowid, -1410);
4551 BEGIN
4552
4553 -- Copy SQL string to array for debugging purposes
4554 --
4555 add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
4556
4557 cursornum := dbms_sql.open_cursor;
4558 fnd_dsql.set_cursor(cursornum);
4559
4560 sql_statement := fnd_dsql.get_text(p_with_debug => FALSE);
4561 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
4562
4563 fnd_dsql.do_binds;
4564
4565 for i in 1..n_selected_cols loop
4566 -- The following prevents NO-DATA-FOUND exception...
4567 selected_cols(i) := NULL;
4568 dbms_sql.define_column(cursornum, i, selected_cols(i), 2000);
4569 end loop;
4570 num_returned := dbms_sql.execute_and_fetch(cursornum, TRUE);
4571 if(num_returned = 1) then
4572 IF (fnd_flex_server1.g_debug_level > 0) THEN
4573 add_debug('(DSQL returned');
4574 END IF;
4575 for i in 1..n_selected_cols loop
4576 dbms_sql.column_value(cursornum, i, selected_cols(i));
4577 IF (fnd_flex_server1.g_debug_level > 0) THEN
4578 add_debug(' ''' || selected_cols(i) || '''');
4579 END IF;
4580 end loop;
4581 IF (fnd_flex_server1.g_debug_level > 0) THEN
4582 add_debug(')');
4583 END IF;
4584 else
4585 num_returned := -1;
4586 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4587 FND_MESSAGE.set_token('MSG', 'x_dsql_select() could not fetch rows');
4588 end if;
4589 dbms_sql.close_cursor(cursornum);
4590 returned_columns := selected_cols;
4591 return(num_returned);
4592
4593 EXCEPTION
4594 WHEN NO_DATA_FOUND OR invalid_rowid then
4595 IF (fnd_flex_server1.g_debug_level > 0) THEN
4596 add_debug('(DSQL returned: NULL)');
4597 END IF;
4598 dbms_sql.close_cursor(cursornum);
4599 return(0);
4600 WHEN TOO_MANY_ROWS then
4601 IF (fnd_flex_server1.g_debug_level > 0) THEN
4602 add_debug('(DSQL returned');
4603 END IF;
4604 for i in 1..n_selected_cols loop
4605 dbms_sql.column_value(cursornum, i, selected_cols(i));
4606 IF (fnd_flex_server1.g_debug_level > 0) THEN
4607 add_debug(' ''' || selected_cols(i) || '''');
4608 END IF;
4609 end loop;
4610 IF (fnd_flex_server1.g_debug_level > 0) THEN
4611 add_debug(')');
4612 add_debug('(DSQL returned: TOO MANY ROWS)');
4613 END IF;
4614 dbms_sql.close_cursor(cursornum);
4615 returned_columns := selected_cols;
4616 return(2);
4617 WHEN OTHERS then
4618 if(dbms_sql.is_open(cursornum)) then
4619 dbms_sql.close_cursor(cursornum);
4620 end if;
4621 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
4622 FND_MESSAGE.set_token('MSG', SQLERRM);
4623 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_statement, 1, 1000));
4624 return(-2);
4625
4626 END x_dsql_select;
4627
4628 /* ----------------------------------------------------------------------- */
4629 /* Adds string to array of dynamic sql strings. */
4630 /* The buffer is zeroed when init_globals is called. */
4631 /* ----------------------------------------------------------------------- */
4632 PROCEDURE add_sql_string(sql_statement IN VARCHAR2) IS
4633 BEGIN
4634 n_sqlstrings := n_sqlstrings + 1;
4635 sqlstrings(n_sqlstrings) := sql_statement;
4636 END add_sql_string;
4637
4638 /* ----------------------------------------------------------------------- */
4639 /* For debugging. */
4640 /* Returns number of SQL statements created during last call */
4641 /* to validate(). Use in conjunction with get_sql(). */
4642 /* ----------------------------------------------------------------------- */
4643 FUNCTION get_nsql_internal RETURN NUMBER IS
4644 BEGIN
4645 return(nvl(to_number(n_sqlstrings), 0));
4646 END get_nsql_internal;
4647
4648 /* ----------------------------------------------------------------------- */
4649 /* For debugging. */
4650 /* Returns SQL statements created during last call */
4651 /* to validate(). Use in conjunction with get_sql(). */
4652 /* ----------------------------------------------------------------------- */
4653 FUNCTION get_sql_internal(statement_number IN NUMBER,
4654 statement_portion IN NUMBER DEFAULT 1) RETURN VARCHAR2 IS
4655
4656 dsql_start NUMBER;
4657
4658 BEGIN
4659
4660 if((statement_number is null) or (statement_number < 1) or
4661 (statement_number > nvl(to_number(n_sqlstrings), 0)) or
4662 (statement_portion is null) or (statement_portion < 1) or
4663 (sqlstrings(statement_number) is null)) then
4664 return(NULL);
4665 end if;
4666
4667 dsql_start := 1 + ((statement_portion - 1) * MAX_RETSTR_LEN);
4668 if(dsql_start > LENGTH(sqlstrings(statement_number))) then
4669 return(NULL);
4670 end if;
4671
4672 return(SUBSTR(sqlstrings(statement_number), dsql_start, MAX_RETSTR_LEN));
4673
4674 EXCEPTION
4675 WHEN NO_DATA_FOUND then
4676 return('get_sql_internal('||to_char(statement_number)||') statement not found');
4677 WHEN OTHERS then
4678 return('get_sql_internal() exception: ' || SQLERRM);
4679
4680 END get_sql_internal;
4681
4682 /* ----------------------------------------------------------------------- */
4683 /* Adds to debug string */
4684 /* ----------------------------------------------------------------------- */
4685 PROCEDURE set_debugging(p_debug_mode IN VARCHAR2)
4686 IS
4687 BEGIN
4688 IF (p_debug_mode = 'OFF') THEN
4689 fnd_flex_server1.g_debug_level := 0;
4690 ELSIF (p_debug_mode = 'ERROR') THEN
4691 fnd_flex_server1.g_debug_level := 1;
4692 ELSIF (p_debug_mode = 'EXCEPTION') THEN
4693 fnd_flex_server1.g_debug_level := 2;
4694 ELSIF (p_debug_mode = 'EVENT') THEN
4695 fnd_flex_server1.g_debug_level := 3;
4696 ELSIF (p_debug_mode = 'PROCEDURE') THEN
4697 fnd_flex_server1.g_debug_level := 4;
4698 ELSIF (p_debug_mode = 'STATEMENT') THEN
4699 fnd_flex_server1.g_debug_level := 5;
4700 ELSIF (p_debug_mode = 'ALL') THEN
4701 fnd_flex_server1.g_debug_level := 6;
4702 fnd_flex_val_util.set_debugging(TRUE);
4703 ELSE
4704 fnd_flex_server1.g_debug_level := 0;
4705 END IF;
4706 EXCEPTION
4707 WHEN OTHERS THEN
4708 fnd_flex_server1.g_debug_level := 0;
4709 END set_debugging;
4710
4711 /* ----------------------------------------------------------------------- */
4712 /* Adds to debug string */
4713 /* ----------------------------------------------------------------------- */
4714 PROCEDURE add_debug(p_debug_string IN VARCHAR2,
4715 p_debug_mode IN VARCHAR2 DEFAULT 'STATEMENT')
4716 IS
4717 l_code_level NUMBER;
4718 l_debug_string VARCHAR2(32000);
4719 l_newline_pos NUMBER;
4720 l_debug_line VARCHAR2(32000);
4721 BEGIN
4722 IF (fnd_flex_server1.g_debug_level = 0) THEN
4723 RETURN;
4724 END IF;
4725 IF (p_debug_mode = 'OFF') THEN
4726 l_code_level := 0;
4727 ELSIF (p_debug_mode = 'ERROR') THEN
4728 l_code_level := 1;
4729 ELSIF (p_debug_mode = 'EXCEPTION') THEN
4730 l_code_level := 2;
4731 ELSIF (p_debug_mode = 'EVENT') THEN
4732 l_code_level := 3;
4733 ELSIF (p_debug_mode = 'PROCEDURE') THEN
4734 l_code_level := 4;
4735 ELSIF (p_debug_mode = 'STATEMENT') THEN
4736 l_code_level := 5;
4737 ELSIF (p_debug_mode = 'ALL') THEN
4738 l_code_level := 6;
4739 ELSE
4740 l_code_level := 0;
4741 END IF;
4742
4743 IF (l_code_level <= fnd_flex_server1.g_debug_level) THEN
4744 IF (g_debug_array_size = 0) THEN
4745 g_debug_array_size := 1;
4746 g_debug_array(g_debug_array_size) :=
4747 ('DEBUG LEVEL:'||To_char(fnd_flex_server1.g_debug_level) ||
4748 chr_newline);
4749 END IF;
4750
4751 l_debug_string := (Substr(Rtrim(Ltrim(p_debug_string)), 1, MAX_RETSTR_LEN-10) ||
4752 chr_newline);
4753
4754 WHILE (l_debug_string IS NOT NULL) LOOP
4755 l_newline_pos := Instr(l_debug_string, chr_newline, 1, 1);
4756 IF (l_newline_pos +
4757 Nvl(Length(g_debug_array(g_debug_array_size)), 0) < MAX_RETSTR_LEN) THEN
4758 l_debug_line := Substr(l_debug_string, 1, l_newline_pos);
4759 l_debug_string := Substr(l_debug_string, l_newline_pos + 1);
4760 IF (l_debug_line LIKE 'BEGIN %' OR
4761 l_debug_line LIKE 'END %' OR
4762 l_debug_line LIKE 'CALL %' OR
4763 l_debug_line LIKE 'EXCEPTION %') THEN
4764 g_debug_array(g_debug_array_size) :=
4765 g_debug_array(g_debug_array_size) || l_debug_line;
4766 ELSE
4767 g_debug_array(g_debug_array_size) :=
4768 g_debug_array(g_debug_array_size) || ' ' || l_debug_line;
4769 END IF;
4770 ELSE
4771 g_debug_array_size := g_debug_array_size + 1;
4772 g_debug_array(g_debug_array_size) := NULL;
4773 END IF;
4774 END LOOP;
4775 END IF;
4776 EXCEPTION
4777 WHEN OTHERS THEN
4778 NULL;
4779 END add_debug;
4780
4781 /* ----------------------------------------------------------------------- */
4782 /* Returns the debug string. */
4783 /* ----------------------------------------------------------------------- */
4784 FUNCTION get_debug_internal(string_n IN NUMBER) RETURN VARCHAR2 IS
4785 BEGIN
4786 IF ((string_n is not null) and
4787 (string_n >= 1) AND
4788 (string_n) <= g_debug_array_size) THEN
4789 RETURN(g_debug_array(string_n));
4790 end if;
4791 return(NULL);
4792
4793 EXCEPTION
4794 WHEN OTHERS then
4795 return('get_debug_internal() exception: ' || SQLERRM);
4796 END get_debug_internal;
4797
4798 /* -------------------------------------------------- */
4799 /* New client side debug functions */
4800 /* -------------------------------------------------- */
4801 PROCEDURE x_get_nsql(x_nsql OUT nocopy NUMBER)
4802 IS
4803 BEGIN
4804 x_nsql := fnd_flex_server1.get_nsql_internal;
4805 EXCEPTION
4806 WHEN OTHERS THEN
4807 x_nsql := 0;
4808 END;
4809
4810 PROCEDURE x_get_sql_npiece(p_sql_num IN NUMBER,
4811 x_npiece OUT nocopy NUMBER)
4812 IS
4813 l_sql_num NUMBER;
4814 BEGIN
4815 l_sql_num := Nvl(p_sql_num, 0);
4816 x_npiece := 0;
4817 IF ((l_sql_num > 0) AND
4818 (l_sql_num <= fnd_flex_server1.get_nsql_internal)) THEN
4819 x_npiece :=Ceil(Nvl(Lengthb(sqlstrings(l_sql_num)),0)/MAX_RETSTR_LEN);
4820 END IF;
4821 EXCEPTION
4822 WHEN OTHERS THEN
4823 x_npiece := 0;
4824 END;
4825
4826 PROCEDURE x_get_sql_piece(p_sql_num IN NUMBER,
4827 p_piece_num IN NUMBER,
4828 x_sql_piece OUT nocopy VARCHAR2)
4829 IS
4830 l_sql_num NUMBER;
4831 l_piece_num NUMBER;
4832 BEGIN
4833 l_sql_num := Nvl(p_sql_num, 0);
4834 l_piece_num := Nvl(p_piece_num, 0);
4835 x_sql_piece := NULL;
4836 IF ((l_sql_num > 0) AND
4837 (l_sql_num <= fnd_flex_server1.get_nsql_internal) AND
4838 (l_piece_num > 0)) THEN
4839 x_sql_piece := substrb(sqlstrings(l_sql_num),
4840 1 + (l_piece_num - 1) * MAX_RETSTR_LEN,
4841 MAX_RETSTR_LEN);
4842 END IF;
4843 EXCEPTION
4844 WHEN OTHERS THEN
4845 x_sql_piece := substrb('EXCEPTION:' || Sqlerrm,1,MAX_RETSTR_LEN);
4846 END;
4847
4848 PROCEDURE x_get_ndebug(x_ndebug OUT nocopy NUMBER)
4849 IS
4850 BEGIN
4851 x_ndebug := g_debug_array_size;
4852 EXCEPTION
4853 WHEN OTHERS THEN
4854 x_ndebug := 0;
4855 END;
4856
4857 PROCEDURE x_get_debug(p_debug_num IN NUMBER,
4858 x_debug OUT nocopy VARCHAR2)
4859 IS
4860 l_debug_num NUMBER;
4861 BEGIN
4862 l_debug_num := Nvl(p_debug_num, 0);
4863 IF (l_debug_num >= 1 AND
4864 l_debug_num <= g_debug_array_size) THEN
4865 x_debug := g_debug_array(l_debug_num);
4866 ELSE
4867 x_debug := 'INDEX OUT OF RANGE [1..' || g_debug_array_size || '].';
4868 END IF;
4869 EXCEPTION
4870 WHEN OTHERS THEN
4871 x_debug := substrb('EXCEPTION:' || Sqlerrm,1,MAX_RETSTR_LEN);
4872 END;
4873
4874 /* ----------------------------------------------------------------------- */
4875 /* Converts concatenated segments to segment array */
4876 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
4877 /* ----------------------------------------------------------------------- */
4878 FUNCTION to_stringarray(catsegs IN VARCHAR2,
4879 sepchar in VARCHAR2,
4880 segs OUT nocopy StringArray)
4881 RETURN NUMBER
4882 IS
4883 l_wc VARCHAR2(10);
4884 l_flex_value VARCHAR2(2000);
4885 i NUMBER;
4886 l_segnum PLS_INTEGER;
4887 l_delimiter VARCHAR2(10);
4888 l_tmp_str VARCHAR2(32000);
4889 l_delim_pos PLS_INTEGER;
4890 l_old_delim_pos PLS_INTEGER;
4891 BEGIN
4892 l_delimiter := Substr(sepchar, 1, 1);
4893
4894 --
4895 -- Make sure delimiter is valid.
4896 --
4897 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
4898 raise_application_error(-20001,
4899 'SV2.to_stringarray. Invalid delimiter:''' ||
4900 Nvl(sepchar, '<NULL>') || '''');
4901 END IF;
4902
4903 --
4904 -- If catsegs is NULL then assume there is only one segment.
4905 --
4906 IF (catsegs IS NULL) THEN
4907 l_segnum := 1;
4908 segs(1) := catsegs;
4909 GOTO return_success;
4910 END IF;
4911
4912 l_segnum := 0;
4913 i := 1;
4914
4915 -- We need to go through un-escaping logic only if
4916 -- there is an ESCAPE character in the string.
4917 -- Bug 4501279.
4918
4919 IF (instr(catsegs, FLEX_DELIMITER_ESCAPE) > 0) THEN
4920
4921 --
4922 -- Loop for each segment.
4923 --
4924 LOOP
4925 l_flex_value := NULL;
4926
4927 --
4928 -- Un-escaping loop.
4929 --
4930 LOOP
4931
4932 l_wc := Substr(catsegs, i, 1);
4933 i := i + 1;
4934
4935 IF (l_wc IS NULL) THEN
4936 EXIT;
4937 ELSIF (l_wc = l_delimiter) THEN
4938 EXIT;
4939 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
4940
4941 l_wc := Substr(catsegs, i, 1);
4942 i := i + 1;
4943
4944 IF (l_wc IS NULL) THEN
4945 EXIT;
4946 END IF;
4947
4948 END IF;
4949
4950 l_flex_value := l_flex_value || l_wc;
4951
4952 END LOOP;
4953
4954 l_segnum := l_segnum + 1;
4955 segs(l_segnum) := l_flex_value;
4956 IF (l_wc IS NULL) THEN
4957 EXIT;
4958 END IF;
4959 END LOOP;
4960
4961 ELSE
4962
4963 -- No un-escaping logic required here.
4964
4965 l_tmp_str := catsegs;
4966 l_delim_pos := 0;
4967 l_old_delim_pos := 0;
4968
4969 LOOP
4970
4971 l_delim_pos := instr(l_tmp_str, l_delimiter, l_delim_pos+1);
4972
4973 IF (l_delim_pos <> 0) THEN
4974 l_segnum := l_segnum + 1;
4975 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1, l_delim_pos-l_old_delim_pos-1);
4976 l_old_delim_pos := l_delim_pos;
4977 ELSE
4978 l_segnum := l_segnum + 1;
4979 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1);
4980 EXIT;
4981 END IF;
4982
4983 END LOOP;
4984
4985 END IF;
4986
4987 <<return_success>>
4988 RETURN(l_segnum);
4989
4990 EXCEPTION
4991 WHEN OTHERS THEN
4992 raise_application_error(-20001, 'SV2.to_stringarray. SQLERRM : ' ||
4993 Sqlerrm);
4994 END to_stringarray;
4995
4996 /* ----------------------------------------------------------------------- */
4997 /* Converts segment array to concatenated segments */
4998 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
4999 /* ----------------------------------------------------------------------- */
5000 FUNCTION from_stringarray(nsegs IN NUMBER,
5001 segs IN StringArray,
5002 sepchar IN VARCHAR2) RETURN VARCHAR2
5003 IS
5004 l_wc VARCHAR2(10);
5005 l_return VARCHAR2(32000) := NULL;
5006 i pls_integer;
5007 l_segnum pls_integer;
5008 l_delimiter VARCHAR2(10);
5009 BEGIN
5010 l_delimiter := Substr(sepchar, 1, 1);
5011 IF (fnd_flex_server1.g_debug_level > 0) THEN
5012 fnd_flex_server1.add_debug('BEGIN SV1.from_stringarray()');
5013 END IF;
5014 --
5015 -- Make sure delimiter is valid.
5016 --
5017 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
5018 raise_application_error(-20001,
5019 'SV1.from_stringarray. Invalid delimiter:''' ||
5020 Nvl(sepchar, '<NULL>') || '''');
5021 END IF;
5022
5023 --
5024 -- Make sure array size is valid.
5025 --
5026 IF ((nsegs IS NULL) OR (nsegs < 1)) THEN
5027 raise_application_error(-20001,
5028 'SV1.from_stringarray. For specified context there are ''' ||
5029 Nvl(to_char(nsegs), '<NULL>') || '''' || ' displayed segments');
5030 END IF;
5031
5032 --
5033 -- If only one segment then no need for concatenating or escaping.
5034 --
5035 IF (nsegs = 1) THEN
5036 l_return := segs(1);
5037 GOTO return_success;
5038 END IF;
5039
5040 --
5041 -- Loop for each segment
5042 --
5043 FOR l_segnum IN 1..nsegs LOOP
5044
5045 i := 1;
5046
5047 --
5048 -- Escaping loop.
5049 --
5050 LOOP
5051
5052 l_wc := Substr(segs(l_segnum), i, 1);
5053 i := i + 1;
5054
5055 IF (l_wc IS NULL) THEN
5056 EXIT;
5057 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
5058 l_return := l_return || FLEX_DELIMITER_ESCAPE;
5059 l_return := l_return || FLEX_DELIMITER_ESCAPE;
5060 ELSIF (l_wc = l_delimiter) THEN
5061 l_return := l_return || FLEX_DELIMITER_ESCAPE;
5062 l_return := l_return || l_delimiter;
5063 ELSE
5064 l_return := l_return || l_wc;
5065 END IF;
5066
5067 END LOOP;
5068
5069 --
5070 -- No delimiter after the last value.
5071 --
5072 IF (l_segnum < nsegs) THEN
5073 l_return := l_return || l_delimiter;
5074 END IF;
5075 END LOOP;
5076
5077 <<return_success>>
5078 IF (fnd_flex_server1.g_debug_level > 0) THEN
5079 fnd_flex_server1.add_debug('END SV1.from_stringarray()');
5080 END IF;
5081 RETURN(l_return);
5082 EXCEPTION
5083 WHEN OTHERS THEN
5084 IF (fnd_flex_server1.g_debug_level > 0) THEN
5085 fnd_flex_server1.add_debug('EXCEPTION SV1.from_stringarray()');
5086 END IF;
5087 raise_application_error(-20001, 'SV1.from_stringarray. SQLERRM : ' || Sqlerrm);
5088 END from_stringarray;
5089
5090 /* ----------------------------------------------------------------------- */
5091 /* Converts concatenated segments to segment array */
5092 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
5093 /* Bug 1375146 added elsif statement to allow for only 1 segment */
5094 /* which is null. */
5095 /* ----------------------------------------------------------------------- */
5096 FUNCTION to_stringarray2(catsegs IN VARCHAR2,
5097 sepchar in VARCHAR2,
5098 segs OUT nocopy StringArray)
5099 RETURN NUMBER
5100 IS
5101 seg_start NUMBER;
5102 seg_end NUMBER;
5103 seg_len NUMBER;
5104 catseg_len NUMBER;
5105 sepchar_len NUMBER;
5106 seg_index BINARY_INTEGER;
5107 keep_going BOOLEAN;
5108 BEGIN
5109 seg_index := 1;
5110 seg_start := 1;
5111 keep_going := TRUE;
5112 IF ((catsegs IS NOT NULL) AND (sepchar IS NOT NULL)) THEN
5113 catseg_len := LENGTH(catsegs);
5114 sepchar_len := LENGTH(sepchar);
5115 WHILE (keep_going = TRUE) LOOP
5116 IF (seg_start > catseg_len) THEN
5117 segs(seg_index) := NULL;
5118 keep_going := FALSE;
5119 ELSE
5120 seg_end := INSTR(catsegs, sepchar, seg_start);
5121 IF (seg_end = 0) THEN
5122 seg_end := catseg_len + 1;
5123 keep_going := FALSE;
5124 END IF;
5125 seg_len := seg_end - seg_start;
5126 IF (seg_len = 0) THEN
5127 segs(seg_index) := NULL;
5128 ELSE
5129 segs(seg_index) := REPLACE(SUBSTR(catsegs, seg_start, seg_len),
5130 NEWLINE, sepchar);
5131 END IF;
5132 END IF;
5133 seg_index := seg_index + 1;
5134 seg_start := seg_end + sepchar_len;
5135 END LOOP;
5136 ELSIF ((catsegs IS NULL) AND (sepchar IS NULL)) THEN
5137 seg_index :=1;
5138 ELSIF ((catsegs IS NULL) AND (sepchar IS NOT NULL)) THEN
5139 segs(1) := NULL;
5140 seg_index := 2;
5141 ELSIF ((catsegs IS NOT NULL) AND (sepchar IS NULL)) THEN
5142 seg_index := 1;
5143 END IF;
5144 RETURN(To_number(seg_index - 1));
5145 END to_stringarray2;
5146
5147 /* ----------------------------------------------------------------------- */
5148 /* Converts segment array to concatenated segments */
5149 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
5150 /* ----------------------------------------------------------------------- */
5151 FUNCTION from_stringarray2(nsegs IN NUMBER,
5152 segs IN StringArray,
5153 sepchar IN VARCHAR2) RETURN VARCHAR2
5154 IS
5155 l_return VARCHAR2(2000) := NULL;
5156 BEGIN
5157 IF (fnd_flex_server1.g_debug_level > 0) THEN
5158 fnd_flex_server1.add_debug('CALL SV1.from_stringarray2()');
5159 END IF;
5160 --
5161 -- Concatenate the segment values. No separator after the last value.
5162 --
5163 IF (nsegs > 0) THEN
5164 FOR i IN 1..(nsegs-1) LOOP
5165 l_return := l_return || REPLACE(segs(i), sepchar, NEWLINE) || sepchar;
5166 END LOOP;
5167 l_return := l_return || REPLACE(segs(nsegs), sepchar, NEWLINE);
5168 END IF;
5169 RETURN(l_return);
5170 END from_stringarray2;
5171
5172
5173 --------------------------------------------------------------------------------
5174 -- This procedure parses a SQL string into following pieces:
5175 -- - bind (i.e. :bind portion of a SQL statement)
5176 -- - single quoted (i.e. string literal portion of a SQL statement)
5177 -- - sql (i.e. rest of the sql statement)
5178 --
5179 --------------------------------------------------------------------------------
5180 procedure parse_sql_string(p_sql_string in varchar2,
5181 px_sql_pieces in out nocopy sql_pieces_tab_type)
5182 IS
5183 l_sql_string varchar2(32000);
5184 l_sql_piece_count BINARY_INTEGER;
5185 l_first_chr varchar2(1);
5186 l_tmp_string varchar2(32000);
5187
5188 l_quote_pos number;
5189 l_quote2_pos number;
5190 l_colon_pos number;
5191 l_end_pos number;
5192 begin
5193 l_sql_string := p_sql_string;
5194 l_sql_piece_count := 0;
5195 px_sql_pieces.DELETE;
5196
5197 while (l_sql_string is not null) loop
5198
5199 l_first_chr := substr(l_sql_string, 1, 1);
5200
5201 if (l_first_chr = SSP_QUOTE) then
5202 --
5203 -- single quoted section, find the end
5204 --
5205 l_quote2_pos := 0;
5206 loop
5207 --
5208 -- Skip double single quotes
5209 --
5210 l_quote_pos := instr(l_sql_string, SSP_QUOTE, l_quote2_pos + 2);
5211 if (l_quote_pos = 0) then
5212 --
5213 -- Error, not terminated properly
5214 --
5215 FND_MESSAGE.set_name('FND', 'FLEX-SQL MISSING QUOTE');
5216 FND_MESSAGE.set_token('CLAUSE', p_sql_string);
5217
5218 raise_application_error
5219 (-20001,
5220 'Error: Single Quote is not terminated properly. ' ||
5221 'SQL: ' || p_sql_string);
5222 else
5223 l_quote2_pos := instr(l_sql_string, SSP_QUOTE2, l_quote_pos);
5224 if (l_quote2_pos = l_quote_pos) then
5225 --
5226 -- double single quote, skip it
5227 --
5228 null;
5229 else
5230 --
5231 -- end of single quoted section
5232 --
5233 exit;
5234 end if;
5235 end if;
5236 end loop;
5237
5238 l_sql_piece_count := l_sql_piece_count + 1;
5239 px_sql_pieces(l_sql_piece_count).piece_type := SSP_PIECE_TYPE_QUOTED;
5240 px_sql_pieces(l_sql_piece_count).piece_text := substr(l_sql_string, 1, l_quote_pos);
5241
5242 l_sql_string := substr(l_sql_string, l_quote_pos + 1);
5243
5244 elsif (l_first_chr = SSP_COLON) then
5245 --
5246 -- bind section
5247 --
5248 l_tmp_string := ltrim(l_sql_string, SSP_BIND_CHARS);
5249
5250 l_sql_piece_count := l_sql_piece_count + 1;
5251 px_sql_pieces(l_sql_piece_count).piece_type := SSP_PIECE_TYPE_BIND;
5252 px_sql_pieces(l_sql_piece_count).piece_text := substr(l_sql_string, 1, length(l_sql_string) - nvl(length(l_tmp_string), 0));
5253
5254 l_sql_string := l_tmp_string;
5255
5256 else
5257 --
5258 -- sql section, find the end
5259 --
5260 l_quote_pos := instr(l_sql_string, SSP_QUOTE);
5261 l_colon_pos := instr(l_sql_string, SSP_COLON);
5262
5263 if ((l_quote_pos = 0) and (l_colon_pos = 0)) then
5264 --
5265 -- no quotes, no binds, the remaining section is just sql
5266 --
5267 l_end_pos := length(l_sql_string);
5268
5269 elsif (l_quote_pos = 0) then
5270 --
5271 -- no quotes
5272 --
5273 l_end_pos := l_colon_pos - 1;
5274
5275 elsif (l_colon_pos = 0) then
5276 --
5277 -- no binds
5278 --
5279 l_end_pos := l_quote_pos - 1;
5280
5281 else
5282 --
5283 -- both quotes, and binds
5284 --
5285 l_end_pos := least(l_quote_pos, l_colon_pos) - 1;
5286
5287 end if;
5288
5289 l_sql_piece_count := l_sql_piece_count + 1;
5290 px_sql_pieces(l_sql_piece_count).piece_type := SSP_PIECE_TYPE_SQL;
5291 px_sql_pieces(l_sql_piece_count).piece_text := substr(l_sql_string, 1, l_end_pos);
5292
5293 l_sql_string := substr(l_sql_string, l_end_pos + 1);
5294
5295 end if;
5296 end loop;
5297 end parse_sql_string;
5298
5299 BEGIN
5300 chr_newline := fnd_global.newline;
5301 NEWLINE := fnd_global.newline;
5302 TAB := fnd_global.tab;
5303 WHITESPACE := ' ' || TAB || NEWLINE;
5304
5305 fnd_plsql_cache.generic_1to1_init('SV1.VST',
5306 vst_cache_controller,
5307 vst_cache_storage);
5308
5309 fnd_plsql_cache.generic_1to1_init('SV1.VSC',
5310 vsc_cache_controller,
5311 vsc_cache_storage);
5312
5313 fnd_plsql_cache.generic_1to1_init('SV1.FVC',
5314 fvc_cache_controller,
5315 fvc_cache_storage);
5316
5317 fnd_plsql_cache.generic_1tom_init('SV1.FSQ',
5318 fsq_cache_controller,
5319 fsq_cache_storage);
5320
5321 fnd_plsql_cache.generic_1tom_init('SV1.VSQ',
5322 vsq_cache_controller,
5323 vsq_cache_storage);
5324
5325 fnd_plsql_cache.custom_1tom_init('SV1.STR',
5326 str_cache_controller);
5327 str_cache_storage.DELETE;
5328
5329 --
5330 -- Decimal separators.
5331 --
5332 IF (fnd_flex_val_util.get_mask('CANONICAL_NUMERIC_CHARS',tmp_varchar2)) THEN
5333 m_nc := Substr(tmp_varchar2,1,1);
5334 ELSE
5335 m_nc := '.';
5336 END IF;
5337
5338 IF (fnd_flex_val_util.get_mask('DB_NUMERIC_CHARS',tmp_varchar2)) THEN
5339 m_nb := Substr(tmp_varchar2,1,1);
5340 ELSE
5341 m_nb := '.';
5342 END IF;
5343
5344 IF (fnd_flex_val_util.get_mask('NLS_NUMERIC_CHARS_OUT',tmp_varchar2)) THEN
5345 m_nd := Substr(tmp_varchar2,1,1);
5346 ELSE
5347 m_nd := '.';
5348 END IF;
5349 END fnd_flex_server1;
[Home] [Help]