1 PACKAGE BODY fnd_flex_server1 AS
2 /* $Header: AFFFSV1B.pls 120.22.12010000.2 2008/08/11 17:47:58 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;
333 sumflg IN VARCHAR2) RETURN VARCHAR2;
330
331 FUNCTION check_vrules(vrs IN Vrules,
332 sqs IN Qualifiers,
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,
450 p_value IN VARCHAR2,
447 p_responsibility_id IN NUMBER,
448 p_value_set_id IN NUMBER,
449 p_parent_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
580 p_varchar2_3 => p_flexvalue.hidden_id,
577 (x_value => g_cache_value,
578 p_varchar2_1 => p_flexvalue.displayed_value,
579 p_varchar2_2 => p_flexvalue.stored_value,
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
712
709 IF (fnd_flex_server1.g_debug_level > 0) THEN
710 add_debug('(' || thisval || ') ');
711 END IF;
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 */
844 /* corresponds to the segment indicated. The error segment number */
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 */
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(30);
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
940 AND c.column_name = g.application_column_name
937 AND g.enabled_flag = 'Y'
938 AND c.application_id = t_apid
939 AND c.table_id = t_id
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
1080 return_code := VV_ERROR;
1077 if(get_value_set(seginfo.vsid, seginfo.segname, vsinfo) <> TRUE) then
1078 errseg_num := segcount;
1079 segcount := segcount - 1;
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 --
1210 -- overwritten any prior value errors, and then return.
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
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() ');
1344
1341 END IF;
1342
1343 return(VV_ERROR);
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
1472 prev_dispvals, prev_vals, prev_ids, prev_descs,
1469 vcode := default_val(kseg.dflt_type, kseg.dflt_val, vsinf.vsformat,
1470 vsinf.maxsize, vsinf.precis, vsinf.lettersok,
1471 kseg.segname, nprev,
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
1519 if(thisseg is null) then
1520 if vsinf.parent_vsid is not NULL and vsinf.valtype = 'D'
1521 then
1522 NULL; -- we will check again for DFF nulls after getting parent.
1523 else
1524 if((kseg.required = 'N') or (vflags.allow_nulls))
1525 then
1526 vcode := FF_VVALID;
1527 goto return_val;
1528 else
1529 vcode := FF_VREQUIRED;
1530 if(vflags.message_on_null) then
1531 value_error_name('FND', 'FLEX-NULL SEGMENT');
1532 end if;
1533 goto return_val;
1534 end if;
1535 end if;
1536 end if;
1537
1538 -- Coerces user input from being a rough approximation of the displayed
1539 -- value format, to the value storage format.
1540 -- Also checks length and that value is in min-max range.
1541 -- Side effect: change thisseg to stored format appropriate to the value set.
1542 -- Added "and thisseg is not null" in conjuction with changes for bug 1879889
1543 -- as it is now possible to be here with a null segment.
1544
1545 -- Validate None validated value sets too.
1546 IF ((isvalu) OR
1547 (vsinf.valtype = 'N')) and thisseg is not null then
1548 vcode := coerce_format(thisseg, isvalu, vsinf.vsformat, vsinf.vsname,
1549 vsinf.maxsize, vsinf.lettersok, vsinf.capsonly,
1550 vsinf.zfill, vsinf.precis, vsinf.minval,
1551 vsinf.maxval,l_storage_value, l_display_value);
1552 thisseg := l_storage_value;
1553 if(vcode <> FF_VVALID) then
1554 goto return_val;
1555 end if;
1556 end if;
1557
1558 -- Now look up value
1559
1560 -- GL RELIES ON THE FOLLOWING STRANGE BEHAVIOR:
1561 -- To be compatible with client, orphans are ok if the child
1562 -- value was defaulted and nulls are allowed.
1563 --
1564 orphansok := (vflags.all_orphans_valid or
1565 (vflags.allow_nulls and defaulted));
1566
1567 -- ==
1568 --
1569 -- Find parent value: took out from find_value.
1570 --
1571 -- ==
1572 l_fvc_code := fnd_plsql_cache.CACHE_NOTFOUND;
1573 IF (vsinf.valtype IN ('I','D','X','Y')) THEN
1574 IF (isvalu) then
1575 l_flexvalue.displayed_value := thisseg;
1576 l_flexvalue.stored_value := thisseg;
1577 else
1578 l_flexvalue.hidden_id := thisseg;
1579 end if;
1580 l_flexvalue.enabled_flag := 'Y';
1581 l_flexvalue.summary_flag := 'N';
1582 l_flexvalue.format := vsinf.vsformat;
1583
1584 parentval := NULL;
1585
1586 IF (vsinf.valtype in ('D','Y')) then
1587 IF (fnd_flex_server1.g_debug_level > 0) THEN
1588 add_debug(vsinf.valtype);
1589 for i in 1..nprev loop
1590 add_debug(to_char(prev_vsids(i)) || '.');
1591 end loop;
1592 END IF;
1593
1594 --
1595 -- Find index to parent value set
1596 l_parent_index := 0;
1597 for i in reverse 1..nprev loop
1598 IF ((prev_vsids(i) is not null) and
1602 parentval := prev_vals(i);
1599 (vsinf.parent_vsid = prev_vsids(i))) then
1600 l_parent_index := i;
1601 IF (vsinf.valtype = 'D') THEN
1603 ELSE
1604 parentval := prev_ids(i);
1605 END IF;
1606 exit;
1607 end if;
1608 end loop;
1609
1610 --
1611 -- Bug 1879889
1612 -- Check for DFF null dependent segment now that we have parent value.
1613 -- same if condition as used above to insure that we only process
1614 -- DFF dependent null segments.
1615 -- Bug 2933236
1616 -- Check dependent segment required flag. If a dependent segment
1617 -- has a parent, but is not is not required, then it is not
1618 -- requried to have a value. It only must have a value if the
1619 -- segment is required.
1620 -- Bug 5560451 The same is true for Key Flexfields.
1621 -- Removing the statment fstruct.isa_key_flexfield = FALSE
1622
1623 if thisseg is null and vsinf.parent_vsid is not NULL
1624 and vsinf.valtype = 'D' then
1625 if parentval is null then
1626 vcode := FF_VVALID;
1627 else
1628 if (kseg.required = 'Y') then /* Bug 2933236 */
1629 vcode := FF_VREQUIRED;
1630 if(vflags.message_on_null) then
1631 value_error_name('FND', 'FLEX-NULL SEGMENT');
1632 end if;
1633 else
1634 vcode := FF_VVALID;
1635 end if;
1636 end if;
1637 goto return_val;
1638 end if;
1639
1640 IF (l_parent_index = 0) then
1641 FND_MESSAGE.set_name('FND', 'FLEX-NO PARENT SEGMENT');
1642 FND_MESSAGE.set_token('CHILD', kseg.segname);
1643 vcode := FF_VERROR;
1644 goto return_val;
1645 end if;
1646
1647 IF ((parentval is null) and (not orphansok))then
1648 --
1649 -- Use FLEX-ORPHAN but need segment names of previous segments
1650 --
1651 value_error_name('FND', 'FLEX-ORPHAN');
1652 value_error_token('PARENT', prev_segnames(l_parent_index));
1653 value_error_token('SEGMENT', kseg.segname);
1654 vcode := FF_VORPHAN;
1655 goto return_val;
1656 end if;
1657 else
1658 IF (fnd_flex_server1.g_debug_level > 0) THEN
1659 add_debug(vsinf.valtype);
1660 END IF;
1661 end if;
1662
1663 IF (kseg.additional_where_clause IS NULL) THEN
1664 IF (vsinf.valtype IN ('I', 'D')) THEN
1665 l_fvc_code := check_fvc(vsinf.vsid,
1666 parentval,
1667 thisseg,
1668 l_flexvalue);
1669 END IF;
1670 END IF;
1671 END IF;
1672
1673 IF (l_fvc_code IN (fnd_plsql_cache.CACHE_FOUND)) THEN
1674 vcode := FF_VVALID;
1675 ELSE
1676 vcode := find_value(fstruct, kseg, vsinf, v_date,
1677 thisseg,
1678 isvalu, orphansok,
1679 kseg.segname, nprev, prev_vsids,
1680 prev_dispvals, prev_vals, prev_ids, prev_descs,
1681 prev_segnames, prev_vsnames, parentval, l_flexvalue);
1682
1683 --
1684 -- If value exists then cache it.
1685 --
1686 IF (kseg.additional_where_clause IS NULL) THEN
1687 IF ((vcode = FF_VVALID) AND
1688 (vsinf.valtype IN ('I', 'D'))) THEN
1689 update_fvc(vsinf.vsid, parentval, thisseg, l_flexvalue);
1690 END IF;
1691 END IF;
1692 END IF;
1693
1694 IF (vcode <> FF_VVALID) then
1695 goto return_val;
1696 end if;
1697
1698 -- Determine qualifier values for this segment from
1699 -- compiled_value_attributes (This function replaces the sq_values
1700 -- which differ from the defaults)
1701 if(l_flexvalue.compiled_attributes is not null) then
1702 if(qualifier_values(fstruct, vsinf.vsid, l_flexvalue.compiled_attributes,
1703 segquals.nquals, segquals.fq_names,
1704 segquals.sq_names,
1705 segquals.sq_values) < 0) then
1706 vcode := FF_VERROR;
1707 goto return_val;
1708 end if;
1709 end if;
1710
1711 -- Add modified segment qualifier values to debug string
1712 --
1713 IF (fnd_flex_server1.g_debug_level > 0) THEN
1714 if(segquals.nquals > 0) THEN
1715 g_debug_text := 'SegQualVals=';
1716 for i in 1..segquals.nquals loop
1717 g_debug_text := g_debug_text || '(' || segquals.sq_values(i) || ')';
1718 end loop;
1719 add_debug(g_debug_text);
1720 end if;
1721 END IF;
1722
1723 -- Always check vrules. They should not be checked in LOADID(), but
1724 -- the client does it anyway.
1725 --
1726 /* bug872437. Don't check vrules in loadid. */
1727
1728 IF (vflags.invoking_mode <> 'L') THEN
1729 vcode := check_vrules(v_ruls, segquals, l_flexvalue.summary_flag);
1730 if(vcode <> FF_VVALID) then
1731 goto return_val;
1732 end if;
1733 END IF;
1734
1735 -- Check security rules
1736 if(NOT vflags.ignore_security) then
1737 if((vsinf.valtype in ('I', 'D', 'F')) and
1741 END IF;
1738 (vsinf.valsecure in ('Y', 'H')) and (kseg.segsecure = 'Y')) THEN
1739 IF (fnd_flex_server1.g_debug_level > 0) THEN
1740 add_debug('Calling Security');
1742 vcode := check_security(l_flexvalue.stored_value, vsinf.vsformat,
1743 parentval, uappid, respid, vsinf,
1744 vflags.message_on_security);
1745 IF (vcode <> FF_VVALID) THEN
1746 IF (fnd_flex_server1.g_debug_level > 0) THEN
1747 add_debug('Security Failure Code: ' || vcode);
1748 END IF;
1749 goto return_val;
1750 end if;
1751 IF (fnd_flex_server1.g_debug_level > 0) THEN
1752 add_debug('NOT SECURED');
1753 END IF;
1754 end if;
1755 end if;
1756
1757 -- Check to make sure value is enabled for the given validation date.
1758 --
1759 if(NOT vflags.ignore_disabled) then
1760 if(l_flexvalue.enabled_flag <> 'Y') then
1761 value_error_name('FND', 'FLEX-VALUE IS DISABLED');
1762 value_error_token('VALUE', l_flexvalue.displayed_value);
1763 vcode := FF_VDISABLED;
1764 goto return_val;
1765 end if;
1766 end if;
1767
1768 if((NOT vflags.ignore_expired) and (v_date is not null)) then
1769 if((Trunc(v_date) < Trunc(nvl(l_flexvalue.start_valid, v_date))) or
1770 (Trunc(v_date) > Trunc(nvl(l_flexvalue.end_valid, v_date)))) then
1771 value_error_name('FND', 'FLEX-VALUE IS EXPIRED');
1772 value_error_token('VALUE', l_flexvalue.displayed_value);
1773 vcode := FF_VEXPIRED;
1774 goto return_val;
1775 end if;
1776 end if;
1777
1778 <<return_val>>
1779 x_flexvalue := l_flexvalue;
1780 squals := segquals;
1781 IF (fnd_flex_server1.g_debug_level > 0) THEN
1782 FND_FLEX_SERVER1.add_debug('END SV1.validate_seg() ');
1783 END IF;
1784
1785 return(vcode);
1786
1787 EXCEPTION
1788 WHEN OTHERS then
1789 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1790 FND_MESSAGE.set_token('MSG', 'validate_seg() exception: ' || SQLERRM);
1791 IF (fnd_flex_server1.g_debug_level > 0) THEN
1792 FND_FLEX_SERVER1.add_debug('EXCEPTION others SV1.validate_seg() ');
1793 END IF;
1794 return(FF_VERROR);
1795 END validate_seg;
1796
1797 /* ----------------------------------------------------------------------- */
1798 /* Given the dvals and qualifiers for the current segment and the */
1799 /* existing derived dvals and qualifiers, compute the new dvals and */
1800 /* qualifiers. Presently only handles one set of derivation rules. */
1801 /* Returns TRUE if OK or FALSE and sets error message if errors. */
1802 /* ----------------------------------------------------------------------- */
1803
1804 FUNCTION derive_values(new_dvals IN DerivedVals,
1805 new_quals IN Qualifiers,
1806 drv_dvals IN OUT nocopy DerivedVals,
1807 drv_quals IN OUT nocopy Qualifiers) RETURN BOOLEAN IS
1808
1809 dqi BINARY_INTEGER;
1810
1811 BEGIN
1812
1813 -- Derive values for the enabled parameters and summary flag
1814 --
1815 if((new_dvals.start_valid is not null) and
1816 ((drv_dvals.start_valid is null) or
1817 (new_dvals.start_valid > drv_dvals.start_valid))) then
1818 drv_dvals.start_valid := new_dvals.start_valid;
1819 end if;
1820 if((new_dvals.end_valid is not null) and
1821 ((drv_dvals.end_valid is null) or
1822 (new_dvals.end_valid < drv_dvals.end_valid))) then
1823 drv_dvals.end_valid := new_dvals.end_valid;
1824 end if;
1825 if(new_dvals.enabled_flag = 'N') then
1826 drv_dvals.enabled_flag := 'N';
1827 end if;
1828 if(new_dvals.summary_flag = 'Y') then
1829 drv_dvals.summary_flag := 'Y';
1830 end if;
1831
1832 -- Derive the qualifiers.
1833 -- Add each segment qualifier to the accumulated derived qualifiers.
1834 -- If it's a new qualifier add it to the array of derived qualifier names.
1835 -- Otherwise, compute the derived value using the Derivation rules:
1836 -- "If any segment qualifier value is 'N' the derived value is 'N'
1837 --
1838 -- Algorithm: For each seg qual, find dqi = index to derived qual with
1839 -- the same fq and sq names. If found, derive the value using the new seg
1840 -- qualifier value. If not found, add a new qualifier to the list of
1841 -- derived qualifiers.
1842 --
1843 for i in 1..new_quals.nquals loop
1844 dqi := NULL;
1845 for j in 1..drv_quals.nquals loop
1846 if((drv_quals.fq_names(j) = new_quals.fq_names(i)) and
1847 (drv_quals.sq_names(j) = new_quals.sq_names(i))) then
1848 dqi := j;
1849 exit;
1850 end if;
1851 end loop;
1852 if(dqi is not null) then
1853 if(new_quals.sq_values(i) = 'N') then
1854 drv_quals.sq_values(dqi) := 'N';
1855 end if;
1856 else
1857 drv_quals.nquals := drv_quals.nquals + 1;
1858 dqi := drv_quals.nquals;
1859 drv_quals.fq_names(dqi) := new_quals.fq_names(i);
1860 drv_quals.sq_names(dqi) := new_quals.sq_names(i);
1861 drv_quals.sq_values(dqi) := new_quals.sq_values(i);
1862 drv_quals.derived_cols(dqi) := new_quals.derived_cols(i);
1863 end if;
1864 end loop;
1865
1866 return(TRUE);
1867
1868 EXCEPTION
1869 WHEN OTHERS then
1873
1870 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1871 FND_MESSAGE.set_token('MSG', 'derive_values() exception: ' || SQLERRM);
1872 return(FALSE);
1874 END derive_values;
1875
1876 /* ----------------------------------------------------------------------- */
1877 /* Coerces entered segment value into the value storage format */
1878 /* appropriate for the value set, */
1879 /* and checks to make sure value is not too long and is within the */
1880 /* the min-max range specified in the value set. */
1881 /* */
1882 /* Returns the value in stored format and TRUE on success or sets */
1883 /* error and returns FALSE on error. */
1884 /* ----------------------------------------------------------------------- */
1885
1886 FUNCTION coerce_format(user_value IN VARCHAR2,
1887 p_is_displayed IN BOOLEAN,
1888 vs_format IN VARCHAR2,
1889 vs_name IN VARCHAR2,
1890 max_length IN NUMBER,
1891 letters_ok IN VARCHAR2,
1892 caps_only IN VARCHAR2,
1893 zero_fill IN VARCHAR2,
1894 precision IN VARCHAR2,
1895 min_value IN VARCHAR2,
1896 max_value IN VARCHAR2,
1897 x_storage_value OUT nocopy VARCHAR2,
1898 x_display_value OUT nocopy VARCHAR2) RETURN VARCHAR2 IS
1899
1900 l_return VARCHAR2(1);
1901 l_success NUMBER;
1902 l_storage_value VARCHAR2(2000);
1903 l_display_value VARCHAR2(2000);
1904 l_utv_enc_message VARCHAR2(32000) := NULL;
1905 l_ssv_enc_message VARCHAR2(32000) := NULL;
1906 BEGIN
1907 IF (fnd_flex_server1.g_debug_level > 0) THEN
1908 FND_FLEX_SERVER1.add_debug('BEGIN SV1.coerce_format() ');
1909 END IF;
1910
1911 x_storage_value := user_value;
1912 x_display_value := user_value;
1913
1914 --
1915 -- Since validation utility is setting messages by using
1916 -- fnd_message, we will do a stacking trick here.
1917 --
1918 -- For different result, different style of messaging is used.
1919 -- If the error is serious, always overwrite the existing message.
1920 --
1921 IF (value_error_set) THEN
1922 l_ssv_enc_message := fnd_message.get_encoded;
1923 END IF;
1924
1925 fnd_flex_val_util.validate_value_ssv
1926 (p_value => user_value,
1927 p_is_displayed => p_is_displayed,
1928 p_vset_name => vs_name,
1929 p_vset_format => vs_format,
1930 p_max_length => max_length,
1931 p_precision => To_number(precision),
1932 p_alpha_allowed => letters_ok,
1933 p_uppercase_only => caps_only,
1934 p_zero_fill => zero_fill,
1935 p_min_value => min_value,
1936 p_max_value => max_value,
1937 x_storage_value => l_storage_value,
1938 x_display_value => l_display_value,
1939 x_success => l_success);
1940
1941 IF (fnd_flex_server1.g_debug_level > 0) THEN
1942 add_debug(Rtrim(Substr(fnd_flex_val_util.get_debug,1,2000), chr_newline));
1943 END IF;
1944 IF (l_success = fnd_flex_val_util.g_ret_no_error) THEN
1945 --
1946 -- No error case.
1947 --
1948 l_return := FF_VVALID;
1949 x_storage_value := l_storage_value;
1950 x_display_value := l_display_value;
1951 IF (value_error_set) THEN
1952 fnd_message.set_encoded(l_ssv_enc_message);
1953 END IF;
1954 ELSE
1955 --
1956 -- Since this is an error case, get the message.
1957 --
1958 l_utv_enc_message := fnd_message.get_encoded;
1959
1960 IF ((l_success = fnd_flex_val_util.g_ret_value_too_long) OR
1961 (l_success = fnd_flex_val_util.g_ret_invalid_number) OR
1962 (l_success = fnd_flex_val_util.g_ret_invalid_date)) THEN
1963 --
1964 -- Format Errors.
1965 --
1966 l_return := FF_VFORMAT;
1967 IF (value_error_set) THEN
1968 fnd_message.set_encoded(l_ssv_enc_message);
1969 ELSE
1970 fnd_message.set_encoded(l_utv_enc_message);
1971 END IF;
1972 ELSIF ((l_success = fnd_flex_val_util.g_ret_vs_bad_precision) OR
1973 (l_success = fnd_flex_val_util.g_ret_vs_bad_format) OR
1974 (l_success = fnd_flex_val_util.g_ret_vs_bad_numrange) OR
1975 (l_success = fnd_flex_val_util.g_ret_vs_bad_daterange) OR
1976 (l_success = fnd_flex_val_util.g_ret_vs_bad_date) OR
1977 (l_success = fnd_flex_val_util.g_ret_exception_others)) THEN
1978 --
1979 -- Serious errors.
1980 --
1981 l_return := FF_VERROR;
1982 fnd_message.set_encoded(l_utv_enc_message);
1983
1984 ELSIF (l_success = fnd_flex_val_util.g_ret_val_out_of_range) THEN
1985 --
1986 -- Bound Check Error.
1987 --
1988 l_return := FF_VBOUNDS;
1989 IF (value_error_set) THEN
1990 fnd_message.set_encoded(l_ssv_enc_message);
1991 ELSE
1992 fnd_message.set_encoded(l_utv_enc_message);
1993 END IF;
1994 ELSE
1995 --
1999 l_return := FF_VERROR;
1996 -- Other errors. This part is added, in case there will be changes
1997 -- in UTV package. As of 26-APR-99 code should not enter here.
1998 --
2000 fnd_message.set_encoded(l_utv_enc_message);
2001 END IF;
2002 --
2003 -- We are in error case, set messaging globals.
2004 --
2005 value_error_set := TRUE;
2006 entering_new_message := FALSE;
2007 END IF;
2008
2009 IF (fnd_flex_server1.g_debug_level > 0) THEN
2010 FND_FLEX_SERVER1.add_debug('END SV1.coerce_format() ');
2011 END IF;
2012
2013 RETURN(l_return);
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 IF (fnd_flex_server1.g_debug_level > 0) THEN
2018 FND_FLEX_SERVER1.add_debug('EXCEPTION SV1.coerce_format() ');
2019 END IF;
2020
2021 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2022 FND_MESSAGE.set_token('MSG', 'coerce_format() exception: ' || SQLERRM);
2023 return(FF_VERROR);
2024 END coerce_format;
2025
2026 /* ----------------------------------------------------------------------- */
2027 /* Looks up value in appropriate validation tables. Input the */
2028 /* character representation of the stored value or the id. Returns */
2029 /* all information about the value in the FlexValue structure. Also */
2030 /* returns the parent value in stored format if there is a parent. */
2031 /* This function needs to know about all previous segments to */
2032 /* handle dependent and table validated value sets. */
2033 /* Returns TRUE if value is found or FALSE and sets error if not. */
2034 /* ----------------------------------------------------------------------- */
2035
2036 FUNCTION find_value(p_str_info IN FlexStructId,
2037 p_seg_info IN SegmentInfo,
2038 p_vs_info IN ValueSetInfo,
2039 p_vdate IN DATE,
2040 p_char_val IN VARCHAR2,
2041 p_is_value IN BOOLEAN,
2042 p_orphans_ok IN BOOLEAN,
2043 p_this_segname IN VARCHAR2,
2044 p_n_prev IN NUMBER,
2045 p_prev_vsids IN NumberArray,
2046 p_prev_dispvals IN ValueArray,
2047 p_prev_vals IN ValueArray,
2048 p_prev_ids IN ValueIdArray,
2049 p_prev_descs IN ValueDescArray,
2050 p_prev_segnames IN SegNameArray,
2051 p_prev_vsnames IN VsNameArray,
2052 p_parent_val IN VARCHAR2,
2053 x_this_val_out OUT nocopy FlexValue)
2054 RETURN VARCHAR2
2055 IS
2056 v_code VARCHAR2(1);
2057 l_is_value VARCHAR2(1);
2058 check_value_table BOOLEAN;
2059 this_value FlexValue;
2060
2061 CURSOR IND_cursor(vsid IN NUMBER, val IN VARCHAR2) IS
2062 SELECT enabled_flag, start_date_active, end_date_active,
2063 summary_flag, compiled_value_attributes, description
2064 FROM fnd_flex_values_vl
2065 WHERE flex_value_set_id = vsid
2066 AND flex_value = val;
2067
2068 CURSOR DEP_cursor(vsid IN NUMBER, val IN VARCHAR2, parnt IN VARCHAR2) IS
2069 SELECT enabled_flag, start_date_active, end_date_active,
2070 summary_flag, compiled_value_attributes, description
2071 FROM fnd_flex_values_vl
2072 WHERE flex_value_set_id = vsid
2073 AND flex_value = val
2074 AND parent_flex_value_low = parnt;
2075
2076 CURSOR INDTL_cursor(vsid IN NUMBER,
2077 val IN VARCHAR2,
2078 p_is_value IN VARCHAR2) IS
2079 SELECT enabled_flag, start_date_active, end_date_active,
2080 summary_flag, compiled_value_attributes,
2081 flex_value, flex_value_meaning, description
2082 FROM fnd_flex_values_vl
2083 WHERE flex_value_set_id = vsid
2084 AND (((p_is_value = 'V') AND (flex_value_meaning = val)) OR
2085 ((p_is_value = 'I') AND (flex_value = val)));
2086
2087 CURSOR DEPTL_cursor(vsid IN NUMBER,
2088 val IN VARCHAR2,
2089 parnt IN VARCHAR2,
2090 p_is_value IN VARCHAR2) IS
2091 SELECT enabled_flag, start_date_active, end_date_active,
2092 summary_flag, compiled_value_attributes,
2093 flex_value, flex_value_meaning, description
2094 FROM fnd_flex_values_vl
2095 WHERE flex_value_set_id = vsid
2096 AND parent_flex_value_low = parnt
2097 AND (((p_is_value = 'V') AND (flex_value_meaning = val)) OR
2098 ((p_is_value = 'I') AND (flex_value = val)));
2099
2100 BEGIN
2101 IF (fnd_flex_server1.g_debug_level > 0) THEN
2102 FND_FLEX_SERVER1.add_debug('BEGIN SV1.find_value()');
2103 END IF;
2104
2105 -- Default the return value
2106
2107 v_code := FF_VERROR;
2108
2109
2110 IF (p_is_value) THEN
2111 l_is_value := 'V';
2112 --
2113 -- R11.5 NLS: following is not true any more. char_val is stored value.
2114 -- However at the end of this function there is a conversion to
2115 -- displayed value.
2116 --
2117 this_value.displayed_value := p_char_val;
2121 this_value.hidden_id := p_char_val;
2118 this_value.stored_value := p_char_val;
2119 ELSE
2120 l_is_value := 'I';
2122 end if;
2123 this_value.enabled_flag := 'Y';
2124 this_value.summary_flag := 'N';
2125 this_value.format := p_vs_info.vsformat;
2126
2127 -- Validation = None, do nothing.
2128 --
2129 if(p_vs_info.valtype = 'N') then
2130 IF (fnd_flex_server1.g_debug_level > 0) THEN
2131 add_debug('N');
2132 END IF;
2133 this_value.stored_value := p_char_val;
2134 this_value.hidden_id := p_char_val;
2135 v_code := FF_VVALID;
2136
2137 -- Validation = Independent, or dependent look it up
2138 --
2139 ELSIF ((p_vs_info.valtype in ('I', 'D')) AND
2140 (p_seg_info.additional_where_clause IS NULL)) then
2141
2142 -- Takes care of both dependent and independent VS
2143 -- Use the independent cursor if this is an independent value set
2144 -- or if this is a dependent value set and the parent_val is null.
2145 -- Both of these conditions will set parent_val to null
2146 --
2147 if(p_parent_val is null) then
2148 open IND_cursor(p_vs_info.vsid, p_char_val);
2149 fetch IND_cursor into this_value.enabled_flag, this_value.start_valid,
2150 this_value.end_valid, this_value.summary_flag,
2151 this_value.compiled_attributes, this_value.description;
2152 if(IND_cursor%NOTFOUND) then
2153 v_code := FF_VNOTFOUND;
2154 else
2155 v_code := FF_VVALID;
2156 end if;
2157 close IND_cursor;
2158 else
2159 open DEP_cursor(p_vs_info.vsid, p_char_val, p_parent_val);
2160 fetch DEP_cursor into this_value.enabled_flag, this_value.start_valid,
2161 this_value.end_valid, this_value.summary_flag,
2162 this_value.compiled_attributes, this_value.description;
2163 if(DEP_cursor%NOTFOUND) then
2164 v_code := FF_VNOTFOUND;
2165 else
2166 v_code := FF_VVALID;
2167 end if;
2168 close DEP_cursor;
2169 end if;
2170
2171 -- Set error if value not found or set the returned value if found.
2172 -- Assumes v_code was either FF_VNOTFOUND or FF_VVALID.
2173 --
2174 if(v_code = FF_VNOTFOUND) then
2175 if(p_is_value) then
2176 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2177
2178 value_error_token('VALUESET', p_vs_info.vsname);
2179 value_error_token('SEGMENT', p_this_segname);
2180 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));
2181 else
2182 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2183
2184 value_error_token('ID', p_char_val);
2185 value_error_token('SEGMENT', p_this_segname);
2186 value_error_token('VALUESET', p_vs_info.vsname);
2187 end if;
2188 goto return_found_value;
2189 else
2190 this_value.stored_value := p_char_val;
2191 this_value.hidden_id := p_char_val;
2192 end if;
2193
2194 -- Validation = TL Independent, or TL dependent look it up
2195 --
2196 ELSIF ((p_vs_info.valtype in ('X', 'Y')) AND
2197 (p_seg_info.additional_where_clause IS NULL)) then
2198
2199 -- Takes care of both TL dependent and TL independent VS
2200 -- Use the independent cursor if this is an independent value set
2201 -- or if this is a dependent value set and the parent_val is null.
2202 -- Both of these conditions will set parent_val to null
2203 --
2204 if(p_parent_val is null) then
2205 open INDTL_cursor(p_vs_info.vsid, p_char_val,l_is_value);
2206 fetch INDTL_cursor into this_value.enabled_flag,
2207 this_value.start_valid,
2208 this_value.end_valid, this_value.summary_flag,
2209 this_value.compiled_attributes,
2210 this_value.hidden_id, this_value.stored_value,
2211 this_value.description;
2212 if(INDTL_cursor%NOTFOUND) then
2213 v_code := FF_VNOTFOUND;
2214 else
2215 v_code := FF_VVALID;
2216 end if;
2217 close INDTL_cursor;
2218 else
2219 open DEPTL_cursor(p_vs_info.vsid, p_char_val, p_parent_val, l_is_value);
2220 fetch DEPTL_cursor into this_value.enabled_flag,
2221 this_value.start_valid,
2222 this_value.end_valid, this_value.summary_flag,
2223 this_value.compiled_attributes,
2224 this_value.hidden_id, this_value.stored_value,
2225 this_value.description;
2226 if(DEPTL_cursor%NOTFOUND) then
2227 v_code := FF_VNOTFOUND;
2228 else
2229 v_code := FF_VVALID;
2230 end if;
2231 close DEPTL_cursor;
2232 end if;
2233
2234 -- Set error if value not found or set the returned value if found.
2235 -- Assumes v_code was either FF_VNOTFOUND or FF_VVALID.
2236 --
2237 IF (v_code = FF_VNOTFOUND) then
2238 if(p_is_value) then
2239 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2240
2241 value_error_token('VALUESET', p_vs_info.vsname);
2242 value_error_token('SEGMENT', p_this_segname);
2246
2243 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));
2244 else
2245 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2247 value_error_token('ID', p_char_val);
2248 value_error_token('SEGMENT', p_this_segname);
2249 value_error_token('VALUESET', p_vs_info.vsname);
2250 end if;
2251 goto return_found_value;
2252 else
2253 NULL;
2254 -- this_value.stored_value := p_char_val;
2255 -- this_value.hidden_id := p_char_val;
2256 end if;
2257
2258 ELSIF ((p_vs_info.valtype in ('I', 'D')) AND
2259 (p_seg_info.additional_where_clause IS NOT NULL)) then
2260 --
2261 -- Similar to Table validation.
2262 --
2263
2264 IF (fnd_flex_server1.g_debug_level > 0) THEN
2265 add_debug('~F(' || p_vs_info.valtype || ')');
2266 END IF;
2267
2268 v_code := table_validate(p_str_info, p_seg_info, p_vs_info,
2269 p_vdate, p_parent_val,
2270 p_char_val, p_is_value,
2271 p_n_prev, p_prev_dispvals, p_prev_vals,
2272 p_prev_ids, p_prev_descs, p_prev_segnames,
2273 p_prev_vsnames, check_value_table, this_value);
2274
2275 -- table_validate() does not set error message if the problem is
2276 -- that the value is not found because we might have to look up
2277 -- the value in the values table. Therefore we need to set the
2278 -- error if value was not found.
2279 if(v_code = FF_VNOTFOUND) then
2280 if(p_is_value) then
2281 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2282
2283 value_error_token('VALUESET', p_vs_info.vsname);
2284 value_error_token('SEGMENT', p_this_segname);
2285 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));
2286 else
2287 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2288
2289 value_error_token('ID', p_char_val);
2290 value_error_token('SEGMENT', p_this_segname);
2291 value_error_token('VALUESET', p_vs_info.vsname);
2292 end if;
2293 end if;
2294
2295 ELSIF ((p_vs_info.valtype in ('X', 'Y')) AND
2296 (p_seg_info.additional_where_clause IS NOT NULL)) THEN
2297
2298 --
2299 -- Similar to Table validation.
2300 --
2301
2302 IF (fnd_flex_server1.g_debug_level > 0) THEN
2303 add_debug('~F(' || p_vs_info.valtype || ')');
2304 END IF;
2305
2306 v_code := table_validate(p_str_info, p_seg_info, p_vs_info,
2307 p_vdate, p_parent_val,
2308 p_char_val, p_is_value,
2309 p_n_prev, p_prev_dispvals, p_prev_vals,
2310 p_prev_ids, p_prev_descs, p_prev_segnames,
2311 p_prev_vsnames, check_value_table, this_value);
2312
2313 -- table_validate() does not set error message if the problem is
2314 -- that the value is not found because we might have to look up
2315 -- the value in the values table. Therefore we need to set the
2316 -- error if value was not found.
2317 if(v_code = FF_VNOTFOUND) then
2318 if(p_is_value) then
2319 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2320
2321 value_error_token('VALUESET', p_vs_info.vsname);
2322 value_error_token('SEGMENT', p_this_segname);
2323 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));
2324 else
2325 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2326
2327 value_error_token('ID', p_char_val);
2328 value_error_token('SEGMENT', p_this_segname);
2329 value_error_token('VALUESET', p_vs_info.vsname);
2330 end if;
2331 end if;
2332
2333 -- Table validation.
2334 --
2335 ELSIF (p_vs_info.valtype = 'F') then
2336
2337 IF (fnd_flex_server1.g_debug_level > 0) THEN
2338 add_debug('F');
2339 END IF;
2340
2341 v_code := table_validate(p_str_info, p_seg_info, p_vs_info,
2342 p_vdate, p_parent_val,
2343 p_char_val, p_is_value,
2344 p_n_prev, p_prev_dispvals, p_prev_vals,
2345 p_prev_ids, p_prev_descs, p_prev_segnames,
2346 p_prev_vsnames, check_value_table, this_value);
2347
2348 if((v_code = FF_VNOTFOUND) and (check_value_table = TRUE)) then
2349 open IND_cursor(p_vs_info.vsid, p_char_val);
2350 fetch IND_cursor into this_value.enabled_flag, this_value.start_valid,
2351 this_value.end_valid, this_value.summary_flag,
2352 this_value.compiled_attributes, this_value.description;
2353 if(IND_cursor%FOUND) then
2354 this_value.stored_value := p_char_val;
2355 this_value.hidden_id := p_char_val;
2356 v_code := FF_VVALID;
2357 end if;
2358 close IND_cursor;
2359 end if;
2360
2361 -- table_validate() does not set error message if the problem is
2365 if(v_code = FF_VNOTFOUND) then
2362 -- that the value is not found because we might have to look up
2363 -- the value in the values table. Therefore we need to set the
2364 -- error if value was not found.
2366 if(p_is_value) then
2367 value_error_name('FND', 'FLEX-VALUE DOES NOT EXIST');
2368
2369 value_error_token('VALUESET', p_vs_info.vsname);
2370 value_error_token('SEGMENT', p_this_segname);
2371 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));
2372 else
2373 value_error_name('FND', 'FLEX-ID DOES NOT EXIST');
2374
2375 value_error_token('ID', p_char_val);
2376 value_error_token('SEGMENT', p_this_segname);
2377 value_error_token('VALUESET', p_vs_info.vsname);
2378 end if;
2379 end if;
2380
2381 -- Pair or Special validation unsupported.
2382 --
2383 ELSIF (p_vs_info.valtype in ('P', 'U')) then
2384 FND_MESSAGE.set_name('FND', 'FLEX-SSV UNSUPPORTED VDATION');
2385 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2386 v_code := FF_VUNKNOWN;
2387
2388 -- Undefined validation type
2389 --
2390 else
2391 FND_MESSAGE.set_name('FND', 'FLEX-VS BAD VDATION TYPE');
2392 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2393 FND_MESSAGE.set_token('VTYPE', p_vs_info.valtype);
2394 v_code := FF_VERROR;
2395 end if;
2396
2397 <<return_found_value>>
2398 if(v_code = FF_VVALID) THEN
2399 v_code := stored_to_displayed(p_vs_info.vsformat, p_vs_info.maxsize, p_vs_info.precis,
2400 p_vs_info.lettersok,
2401 this_value.stored_value,
2402 this_value.displayed_value);
2403 end if;
2404 x_this_val_out := this_value;
2405 IF (fnd_flex_server1.g_debug_level > 0) THEN
2406 FND_FLEX_SERVER1.add_debug('END SV1.find_value(returns:' ||
2407 v_code || ')');
2408 END IF;
2409
2410 return(v_code);
2411 EXCEPTION
2412 WHEN OTHERS then
2413 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2414 FND_MESSAGE.set_token('MSG', 'find_value() exception: ' || SQLERRM);
2415 IF (fnd_flex_server1.g_debug_level > 0) THEN
2416 FND_FLEX_SERVER1.add_debug('EXCEPTION others SV1.find_value()');
2417 END IF;
2418
2419 return(FF_VERROR);
2420 END find_value;
2421
2422 /* ----------------------------------------------------------------------- */
2423 /* Finds value from table validated value set. */
2424 /* Returns value error code and sets message on error. */
2425 /* However, if value is not found, does not set the error message */
2426 /* because we may have to look it up in the values table if */
2427 /* check_valtab is returned as 'Y' */
2428 /* NOTE: where_clause declared as VARCHAR2(30000) for implicit */
2429 /* conversion from LONG column type in the table. */
2430 /* Generic exception raised if SQL fragment in where_clause is bad. */
2431 /* ----------------------------------------------------------------------- */
2432 FUNCTION table_validate(p_str_info IN FlexStructId,
2433 p_seg_info IN SegmentInfo,
2434 p_vs_info IN ValueSetInfo,
2435 p_vdate IN DATE,
2436 p_parent_value IN VARCHAR2,
2437 p_charval IN VARCHAR2,
2438 p_is_value IN BOOLEAN,
2439 p_nprev IN NUMBER,
2440 p_prev_dispvals IN ValueArray,
2441 p_prev_vals IN ValueArray,
2442 p_prev_ids IN ValueIdArray,
2443 p_prev_descs IN ValueDescArray,
2444 p_prev_segnames IN SegNameArray,
2445 p_prev_vsnames IN VsNameArray,
2446 x_check_valtab OUT nocopy BOOLEAN,
2447 x_found_value OUT nocopy FlexValue)
2448 RETURN VARCHAR2
2449 IS
2450 l_where_clause VARCHAR2(32000);
2451 l_tmp_where_clause VARCHAR2(32000);
2452 l_order_by_pos NUMBER;
2453
2454 l_vcode VARCHAR2(1);
2455 l_sql VARCHAR2(32000);
2456 l_nrecords NUMBER;
2457 l_results StringArray;
2458
2459 CURSOR tbl_cursor(p_flex_value_set_id IN NUMBER) IS
2460 SELECT application_table_name, value_column_name, value_column_type,
2461 id_column_name, id_column_type, meaning_column_name,
2462 meaning_column_type, enabled_column_name, start_date_column_name,
2463 end_date_column_name, summary_column_name,
2464 compiled_attribute_column_name, additional_quickpick_columns,
2465 summary_allowed_flag, additional_where_clause
2466 FROM fnd_flex_validation_tables
2467 WHERE flex_value_set_id = p_flex_value_set_id;
2468
2469 l_tbl_rec tbl_cursor%ROWTYPE;
2470 l_sql_pieces sql_pieces_tab_type;
2471 BEGIN
2472 IF (p_vs_info.valtype IN ('I', 'D', 'X', 'Y')) THEN
2476 l_tbl_rec.id_column_name := NULL;
2473 l_tbl_rec.application_table_name := 'FND_FLEX_VALUES_VL FND_FLEX_VALUES_VL';
2474 l_tbl_rec.value_column_name := 'FND_FLEX_VALUES_VL.FLEX_VALUE';
2475 l_tbl_rec.value_column_type := 'C';
2477 l_tbl_rec.id_column_type := NULL;
2478 l_tbl_rec.meaning_column_name := 'FND_FLEX_VALUES_VL.DESCRIPTION';
2479 l_tbl_rec.meaning_column_type := 'C';
2480 l_tbl_rec.enabled_column_name := 'FND_FLEX_VALUES_VL.ENABLED_FLAG';
2481 l_tbl_rec.start_date_column_name := 'FND_FLEX_VALUES_VL.START_DATE_ACTIVE';
2482 l_tbl_rec.end_date_column_name := 'FND_FLEX_VALUES_VL.END_DATE_ACTIVE';
2483 l_tbl_rec.summary_column_name := 'FND_FLEX_VALUES_VL.SUMMARY_FLAG';
2484 l_tbl_rec.compiled_attribute_column_name := 'FND_FLEX_VALUES_VL.COMPILED_VALUE_ATTRIBUTES';
2485 l_tbl_rec.additional_quickpick_columns := NULL;
2486 l_tbl_rec.summary_allowed_flag := 'N';
2487
2488 IF (p_parent_value IS NULL) THEN
2489 l_tbl_rec.additional_where_clause := 'WHERE FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = :$FLEX$.$VALUE_SET_ID$';
2490 ELSE
2491 l_tbl_rec.additional_where_clause := 'WHERE FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = :$FLEX$.$VALUE_SET_ID$' ||
2492 ' AND FND_FLEX_VALUES_VL.PARENT_FLEX_VALUE_LOW = :$FLEX$.$PARENT_VALUE$';
2493 END IF;
2494
2495 ELSE
2496 open TBL_cursor(p_vs_info.vsid);
2497 FETCH tbl_cursor INTO l_tbl_rec;
2498 IF (TBL_cursor%NOTFOUND) THEN
2499 close TBL_cursor;
2500 FND_MESSAGE.set_name('FND', 'FLEX-SSV MISSING TBLVS');
2501 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2502 return(FF_VERROR);
2503 end if;
2504 close TBL_cursor;
2505 END IF;
2506
2507 IF (l_tbl_rec.summary_allowed_flag = 'Y') then
2508 x_check_valtab := TRUE;
2509 else
2510 x_check_valtab := FALSE;
2511 end if;
2512
2513 -- Return error if unsupported validation.
2514 --
2515 if((l_tbl_rec.additional_quickpick_columns is not null) and (INSTR(UPPER(l_tbl_rec.additional_quickpick_columns),'INTO') > 0)) then
2516 FND_MESSAGE.set_name('FND', 'FLEX-SSV NO TBL VS');
2517 FND_MESSAGE.set_token('VSNAME', p_vs_info.vsname);
2518 return(FF_VUNKNOWN);
2519 end if;
2520
2521 -- Default column names if null
2522 --
2523 if(l_tbl_rec.id_column_name is null) then
2524 l_tbl_rec.id_column_name := l_tbl_rec.value_column_name;
2525 l_tbl_rec.id_column_type := l_tbl_rec.value_column_type;
2526 end if;
2527
2528 if(l_tbl_rec.meaning_column_name is null) then
2529 l_tbl_rec.meaning_column_name := 'NULL';
2530 l_tbl_rec.meaning_column_type := 'V';
2531 end if;
2532
2533 -- Now need to parse where/order by clause. Clause is of the form:
2534 -- [where <condition>] [order by <ordering>]
2535 -- The order by portion is not relevant here and is dropped.
2536 -- "where" is optional, hence the first word in the string need not be
2537 -- "where".
2538 -- Note there can only be 1 space between "order" and "by".
2539 -- "where" and "order by" are found using case-insensitive comparisons.
2540 -- Case-insensitive comparison requires upper case conversion.
2541 -- Assume SQL strings in single-byte characters only.
2542 -- Substitute $FLEX$ and $PROFILES$ in where clause if its not null.
2543 --
2544 l_where_clause := ltrim(l_tbl_rec.additional_where_clause, WHITESPACE);
2545
2546 l_sql_pieces.DELETE;
2547 if (l_where_clause is not null) then
2548 -- there is where clause
2549 l_tmp_where_clause := Upper(l_where_clause);
2550
2551 if (l_tmp_where_clause LIKE 'WHERE%') then
2552 -- It starts with WHERE keyword
2553 -- Skip the leading WHERE keyword
2554 l_where_clause := Substr(l_where_clause, 6);
2555 l_tmp_where_clause := Upper(l_where_clause);
2556 end if;
2557
2558 l_order_by_pos := Instr(l_tmp_where_clause, 'ORDER BY');
2559 if (l_order_by_pos > 0) then
2560 l_where_clause := Substr(l_where_clause, 1, l_order_by_pos - 1);
2561 end if;
2562
2563 IF (p_seg_info.additional_where_clause IS NOT NULL) THEN
2564 l_where_clause := '(' || l_where_clause || ') AND (' ||
2565 p_seg_info.additional_where_clause || ')';
2566 END IF;
2567
2568 l_vcode := substitute_flex_binds5(l_where_clause,
2569 p_str_info, p_seg_info,
2570 p_vdate, p_parent_value,
2571 p_nprev, p_prev_dispvals,
2572 p_prev_vals, p_prev_ids, p_prev_descs,
2573 p_prev_segnames, p_prev_vsnames,
2574 l_sql_pieces);
2575
2576 if (l_vcode <> FF_VVALID) then
2577 return(l_vcode);
2578 end if;
2579
2580 IF (fnd_flex_server1.g_debug_level > 0) THEN
2581 l_tmp_where_clause := NULL;
2582 IF (l_vcode = FF_VVALID) THEN
2583 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2584 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2585 l_tmp_where_clause := l_tmp_where_clause ||
2586 string_clause(l_sql_pieces(i).bind_value);
2587
2588 ELSE
2589 l_tmp_where_clause := l_tmp_where_clause ||
2590 l_sql_pieces(i).piece_text;
2594
2591 END IF;
2592 END LOOP;
2593 END IF;
2595 l_where_clause := l_tmp_where_clause;
2596
2597 if(l_where_clause is not null) then
2598 add_debug(' (where ' || SUBSTRB(l_where_clause, 1, 1000) || ') ');
2599 end if;
2600 END IF;
2601 end if;
2602
2603 -- Build SQL statement to do the select from the table specified
2604 -- by APPLICATION_TABLE_NAME.
2605 --
2606 -- The columns SUMMARY_COLUMN_NAME, COMPILED_ATTRIBUTE_COLUMN_NAME,
2607 -- ENABLED_COLUMN_NAME, and START and END _DATE_COLUMN_NAME in the
2608 -- FND_FLEX_VALIDATION_TABLES table either contain the name of the
2609 -- column in the "application table" which contains those parameters,
2610 -- or they contain text strings which are part of a sql statement.
2611 -- For example, the COMPILED_ATTRIBUTES_COLUMN_NAME contains either the
2612 -- name of a column, or a SQL fragment of the form: "'Y\nN\nA'" which
2613 -- includes the single quotes.
2614 --
2615 -- In either case the contents of these fields should be blindly
2616 -- inserted into the SQL statement.
2617 --
2618 -- BUG: To be compatible with client code behavior, we must surround
2619 -- non-character columns with the default to_char() conversion in the
2620 -- select statement. This causes seconds to be lost from date columns.
2621 -- It also means that numbers are stored and displayed in their default
2622 -- format rather than the value set format.
2623 --
2624 -- This functionality is now in the select_clause() function.
2625 --
2626 l_sql := 'select ' ||
2627 select_clause(l_tbl_rec.value_column_name, l_tbl_rec.value_column_type,
2628 VC_VALUE, p_vs_info.vsformat, p_vs_info.maxsize);
2629 l_sql := l_sql || ', ' ||
2630 select_clause(l_tbl_rec.id_column_name, l_tbl_rec.id_column_type,
2631 VC_ID, p_vs_info.vsformat, p_vs_info.maxsize);
2632 l_sql := l_sql || ', ' ||
2633 select_clause(l_tbl_rec.meaning_column_name, l_tbl_rec.meaning_column_type,
2634 VC_DESCRIPTION, p_vs_info.vsformat, p_vs_info.maxsize);
2635 l_sql := l_sql || ', ' || l_tbl_rec.enabled_column_name || ', ';
2636 l_sql := l_sql || 'to_char(' || l_tbl_rec.start_date_column_name ||',''YYYY/MM/DD HH24:MI:SS'')' || ', ';
2637 l_sql := l_sql || 'to_char(' || l_tbl_rec.end_date_column_name ||',''YYYY/MM/DD HH24:MI:SS'')' || ', ';
2638 l_sql := l_sql || l_tbl_rec.summary_column_name || ', ';
2639 l_sql := l_sql || l_tbl_rec.compiled_attribute_column_name;
2640 l_sql := l_sql || ' from ' || l_tbl_rec.application_table_name || ' where ';
2641
2642 fnd_dsql.init;
2643 fnd_dsql.add_text(l_sql);
2644
2645 --
2646 -- The where clause needs to be surrounded by parentheses so we can
2647 -- add additional restrictions using AND without overriding ORs inside it.
2648 --
2649 IF (l_sql_pieces.COUNT > 0) THEN
2650 fnd_dsql.add_text('(');
2651
2652 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2653 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2654 fnd_dsql.add_bind(l_sql_pieces(i).bind_value);
2655 ELSE
2656 fnd_dsql.add_text(l_sql_pieces(i).piece_text);
2657 END IF;
2658 END LOOP;
2659
2660 fnd_dsql.add_text(') and ');
2661 END IF;
2662
2663 -- Build comparison appropriate for data type of value or id column
2664 --
2665 if(p_is_value) THEN
2666 fnd_dsql.add_text(l_tbl_rec.value_column_name || ' = ');
2667 x_compare_clause(l_tbl_rec.value_column_type, l_tbl_rec.value_column_name, p_charval,
2668 VC_VALUE, p_vs_info.vsformat, p_vs_info.maxsize);
2669 else
2670 fnd_dsql.add_text(l_tbl_rec.id_column_name || ' = ');
2671 x_compare_clause(l_tbl_rec.id_column_type, l_tbl_rec.id_column_name, p_charval,
2672 VC_ID, p_vs_info.vsformat, p_vs_info.maxsize);
2673 end if;
2674
2675 -- Select the value from the user's table.
2676 --
2677 l_nrecords := x_dsql_select(8, l_results);
2678
2679 if(l_nrecords > 0) then
2680 x_found_value.format := p_vs_info.vsformat;
2681 x_found_value.stored_value := l_results(1);
2682 x_found_value.hidden_id := l_results(2);
2683 x_found_value.description := l_results(3);
2684 x_found_value.enabled_flag := l_results(4);
2685 x_found_value.start_valid := to_date(l_results(5),'YYYY/MM/DD HH24:MI:SS');
2686 x_found_value.end_valid := to_date(l_results(6),'YYYY/MM/DD HH24:MI:SS');
2687 x_found_value.summary_flag := l_results(7);
2688 x_found_value.compiled_attributes := l_results(8);
2689 return(FF_VVALID);
2690 elsif(l_nrecords = 0) then
2691 return(FF_VNOTFOUND);
2692 else
2693 return(FF_VERROR);
2694 end if;
2695
2696 return(FF_VERROR);
2697
2698 EXCEPTION
2699 WHEN OTHERS then
2700 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2701 FND_MESSAGE.set_token('MSG', 'table_validate() exception: ' || SQLERRM);
2702 return(FF_VERROR);
2703
2704 END table_validate;
2705
2706 /* ----------------------------------------------------------------------- */
2707 /* Returns the default value for this segment in displayed format. */
2708 /* For defaulting current date and current time into non-translatable */
2709 /* date value sets, use the format based on the maximum size of the */
2713 /* succeeded, FF_VUNKNOWN if unsupported or FF_VERROR. */
2710 /* value set field alone. */
2711 /* Let ordinary value validation catch problems with the format */
2712 /* or length of the defaulted value. Returns FF_VVALID if default */
2714 /* ----------------------------------------------------------------------- */
2715 FUNCTION default_val(def_type IN VARCHAR2,
2716 def_text IN VARCHAR2,
2717 valset_fmt IN VARCHAR2,
2718 valset_len IN NUMBER,
2719 valset_precis IN NUMBER,
2720 valset_lettersok IN VARCHAR2,
2721 seg_name IN VARCHAR2,
2722 nprev IN NUMBER,
2723 prev_dispvals IN ValueArray,
2724 prev_vals IN ValueArray,
2725 prev_ids IN ValueIdArray,
2726 prev_descs IN ValueDescArray,
2727 prev_segnames IN SegNameArray,
2728 prev_vsnames IN VsNameArray,
2729 displayed_val OUT nocopy VARCHAR2)
2730 RETURN VARCHAR2 IS
2731 nfound NUMBER;
2732 v_code VARCHAR2(1);
2733 datefmt VARCHAR2(30);
2734 stored_val VARCHAR2(1000);
2735 sql_string VARCHAR2(2000);
2736 l_sql_pieces sql_pieces_tab_type;
2737 BEGIN
2738 v_code := FF_VERROR;
2739
2740 IF (def_type is null) then
2741 v_code := FF_VVALID;
2742 ELSIF (def_type = 'C') then
2743 -- Constant
2744 stored_val := SUBSTRB(def_text, 1, 1000);
2745 v_code := FF_VVALID;
2746
2747 ELSIF (def_type = 'D') then
2748 -- Current date.
2749 IF (valset_fmt in ('X', 'Y')) then
2750 datefmt := stored_date_format(valset_fmt, valset_len);
2751 ELSIF (valset_len >= 11) then
2752 datefmt := stored_date_format('D', 11);
2753 else
2754 datefmt := stored_date_format('D', 9);
2755 end if;
2756 stored_val := to_char(sysdate, datefmt);
2757 v_code := FF_VVALID;
2758
2759 ELSIF (def_type = 'T') then
2760 -- Current time or date-time depending on the size
2761 IF (valset_fmt in ('Y', 'Z')) then
2762 datefmt := stored_date_format(valset_fmt, valset_len);
2763 ELSIF (valset_len < 8) then
2764 datefmt := stored_date_format('I', 5);
2765 ELSIF (valset_len between 8 and 14) then
2766 datefmt := stored_date_format('I', 8);
2767 ELSIF (valset_len between 15 and 16) then
2768 datefmt := stored_date_format('T', 15);
2769 ELSIF (valset_len = 17) then
2770 datefmt := stored_date_format('T', 17);
2771 ELSIF (valset_len between 18 and 19) then
2772 datefmt := stored_date_format('T', 18);
2773 else
2774 datefmt := stored_date_format('T', 20);
2775 end if;
2776 stored_val := to_char(sysdate, datefmt);
2777 v_code := FF_VVALID;
2778
2779 ELSIF (def_type = 'P') then
2780 sql_string := ':$PROFILES$.' || def_text;
2781 v_code := convert_bind_token(sql_string, nprev,
2782 prev_dispvals, prev_vals,
2783 prev_ids, prev_descs,
2784 prev_segnames, prev_vsnames,
2785 stored_val);
2786
2787 ELSIF ((def_type = 's') OR (def_type = 'A')) then
2788 sql_string := ':$FLEX$.' || def_text;
2789 v_code := convert_bind_token(sql_string, nprev,
2790 prev_dispvals, prev_vals,
2791 prev_ids, prev_descs,
2792 prev_segnames, prev_vsnames,
2793 stored_val);
2794
2795 ELSIF (def_type = 'S') then
2796 -- SQL statement
2797 v_code := substitute_flex_binds3(def_text, nprev,
2798 prev_dispvals, prev_vals,
2799 prev_ids, prev_descs,
2800 prev_segnames, prev_vsnames,
2801 l_sql_pieces);
2802 IF (v_code = FF_VVALID) THEN
2803 fnd_dsql.init;
2804
2805 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2806 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2807 fnd_dsql.add_bind(l_sql_pieces(i).bind_value);
2808 ELSE
2809 fnd_dsql.add_text(l_sql_pieces(i).piece_text);
2810 END IF;
2811 END LOOP;
2812
2813 nfound := x_dsql_select_one(stored_val);
2814 IF (nfound > 1) then
2815 FND_MESSAGE.set_name('FND', 'FLEX-DFLT MULTIPLE SQL ROWS');
2816 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(fnd_dsql.get_text(FALSE), 1, 1000));
2817 v_code := FF_VERROR;
2818 ELSIF (nfound < 0) then
2819 v_code := FF_VERROR;
2820 else
2821 v_code := FF_VVALID;
2822 end if;
2823 end if;
2824
2825 ELSIF (def_type = 'F') then
2826 -- 'F' => :block.field (the colon may or may not be in the def_text string)
2827 FND_MESSAGE.set_name('FND', 'FLEX-SSV UNSUPPORTED DEFAULT');
2828 v_code := FF_VUNKNOWN;
2829
2830 ELSIF (def_type = 'E') then
2831 -- Environment variable
2835 ELSE
2832 FND_MESSAGE.set_name('FND', 'FLEX-SSV UNSUPPORTED DEFAULT');
2833 v_code := FF_VUNKNOWN;
2834
2836 -- Unknown type.
2837 FND_MESSAGE.set_name('FND', 'FLEX-INVALID DEFAULT TYPE');
2838 FND_MESSAGE.set_token('SEGNAME', seg_name);
2839 v_code := FF_VERROR;
2840
2841 END IF;
2842
2843 IF (v_code = FF_VVALID) then
2844 v_code := stored_to_displayed(valset_fmt, valset_len, valset_precis,
2845 valset_lettersok,
2846 stored_val, displayed_val);
2847 end if;
2848 return(v_code);
2849 EXCEPTION
2850 WHEN OTHERS then
2851 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2852 FND_MESSAGE.set_token('MSG', 'default_val() exception: ' || SQLERRM);
2853 return(FF_VERROR);
2854 END default_val;
2855
2856 /* ------------------------------------------------------------------------- */
2857 /* Interprets $PROFILES$ in where token passed into FND_KEY_FLEX.DEFINE */
2858 /* This cannot take $FLEX$, and :block.field is unsupported. */
2859 /* Returns Validataion error codes VV_VALID if ok, VV_UNSUPPORTED */
2860 /* if unsupported, or VV_ERROR if other error. */
2861 /* ------------------------------------------------------------------------- */
2862
2863 FUNCTION parse_where_token(clause_in IN VARCHAR2,
2864 clause_out OUT nocopy VARCHAR2) RETURN NUMBER IS
2865 vcode VARCHAR2(1);
2866 dummy_dispvls ValueArray;
2867 dummy_vals ValueArray;
2868 dummy_ids ValueIdArray;
2869 dummy_descs ValueDescArray;
2870 dummy_segname SegNameArray;
2871 dummy_vsnames VsNameArray;
2872 l_sql_pieces sql_pieces_tab_type;
2873
2874 BEGIN
2875
2876 if(clause_in is not null) then
2877 if(INSTR(clause_in, ':$FLEX$') > 0) then
2878 FND_MESSAGE.set_name('FND', 'FLEX-NO FLEX IN WHERE TOKEN');
2879 return(VV_ERROR);
2880 end if;
2881 vcode := substitute_flex_binds3(clause_in, 0,
2882 dummy_dispvls, dummy_vals,
2883 dummy_ids, dummy_descs,
2884 dummy_segname, dummy_vsnames,
2885 l_sql_pieces);
2886
2887 clause_out := NULL;
2888 IF (vcode = FF_VVALID) THEN
2889 FOR i IN 1 .. l_sql_pieces.COUNT LOOP
2890 IF (l_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2891 clause_out := clause_out ||
2892 string_clause(l_sql_pieces(i).bind_value);
2893
2894 ELSE
2895 clause_out := clause_out ||
2896 l_sql_pieces(i).piece_text;
2897 END IF;
2898 END LOOP;
2899 END IF;
2900
2901 if(vcode = FF_VVALID) then
2902 return(VV_VALID);
2903 elsif(vcode = FF_VUNKNOWN) then
2904 return(VV_UNSUPPORTED);
2905 else
2906 return(VV_ERROR);
2907 end if;
2908 end if;
2909 return(VV_VALID);
2910
2911 EXCEPTION
2912 WHEN OTHERS then
2913 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2914 FND_MESSAGE.set_token('MSG', 'parse_where_token() exception: '||SQLERRM);
2915 return(VV_ERROR);
2916
2917 END parse_where_token;
2918
2919 /* ------------------------------------------------------------------------ */
2920 /* Takes input string possibly containing special flex bind variables */
2921 /* of :$PROFILES$.<profile>, :$FLEX$.<name>[.<output>] or :block.field */
2922 /* and substitutes the flex values for them. Some substitutions such */
2923 /* as :block.field cannot be handled on the server and results in an */
2924 /* FF_VUNKNOWN being returned. */
2925 /* */
2926 /* Input string can contain quoted regions, within which no */
2927 /* substitutions should be performed. The regions are marked by */
2928 /* single quotes. Double quotes within these regions represent */
2929 /* the quote character. */
2930 /* */
2931 /* Limitations: string_in, string_out up to 30,000 bytes. */
2932 /* bind_token and bind_value up to 2000 bytes. */
2933 /* */
2934 /* Returns FF_VVALID if default succeeded, FF_VUNKNOWN if unsupported */
2935 /* or FF_VERROR on error. */
2936 /* ------------------------------------------------------------------------ */
2937 FUNCTION substitute_flex_binds3(p_string_in IN VARCHAR2,
2938 p_nprev IN NUMBER,
2939 p_prev_dispvals IN ValueArray,
2940 p_prev_vals IN ValueArray,
2941 p_prev_ids IN ValueIdArray,
2942 p_prev_descs IN ValueDescArray,
2943 p_prev_segnames IN SegNameArray,
2944 p_prev_vsnames IN VsNameArray,
2945 px_sql_pieces in out nocopy sql_pieces_tab_type)
2946 RETURN VARCHAR2
2947 IS
2948 v_code VARCHAR2(1);
2949 BEGIN
2950 IF (fnd_flex_server1.g_debug_level > 0) THEN
2951 add_debug('BEGIN SV1.substitute_flex_binds3('''||
2955 BEGIN
2952 SUBSTRB(p_string_in, 1, 500) || ''' ');
2953 END IF;
2954
2956 parse_sql_string(p_string_in, px_sql_pieces);
2957 EXCEPTION
2958 WHEN OTHERS THEN
2959 RETURN(FF_VERROR);
2960 END;
2961
2962 --
2963 -- Derive bind values
2964 --
2965 FOR i IN 1 .. px_sql_pieces.COUNT LOOP
2966 IF (px_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
2967 v_code := convert_bind_token(px_sql_pieces(i).piece_text,
2968 p_nprev, p_prev_dispvals,
2969 p_prev_vals, p_prev_ids, p_prev_descs,
2970 p_prev_segnames, p_prev_vsnames,
2971 px_sql_pieces(i).bind_value);
2972
2973 IF (v_code <> FF_VVALID) then
2974 return(v_code);
2975 end if;
2976 END IF;
2977 END LOOP;
2978
2979 return(FF_VVALID);
2980
2981 EXCEPTION
2982 WHEN OTHERS then
2983 IF (fnd_flex_server1.g_debug_level > 0) THEN
2984 add_debug('EXCEPTION SV1.substitute_flex_binds3()');
2985 END IF;
2986 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2987 FND_MESSAGE.set_token('MSG', 'substitute_flex_binds3() exception: '
2988 || SQLERRM);
2989 return(FF_VERROR);
2990 END substitute_flex_binds3;
2991
2992 --
2993 -- The one with extension support.
2994 --
2995 /* ------------------------------------------------------------------------ */
2996 FUNCTION substitute_flex_binds5(p_string_in IN VARCHAR2,
2997 p_str_info IN FlexStructId,
2998 p_seg_info IN SegmentInfo,
2999 p_vdate IN DATE,
3000 p_parent_value IN VARCHAR2,
3001 p_nprev IN NUMBER,
3002 p_prev_dispvals IN ValueArray,
3003 p_prev_vals IN ValueArray,
3004 p_prev_ids IN ValueIdArray,
3005 p_prev_descs IN ValueDescArray,
3006 p_prev_segnames IN SegNameArray,
3007 p_prev_vsnames IN VsNameArray,
3008 px_sql_pieces in out nocopy sql_pieces_tab_type)
3009 RETURN VARCHAR2
3010 IS
3011 v_code VARCHAR2(1);
3012 BEGIN
3013 IF (fnd_flex_server1.g_debug_level > 0) THEN
3014 add_debug('BEGIN SV1.substitute_flex_binds5('''||
3015 SUBSTRB(p_string_in, 1, 500) || ''' ');
3016 END IF;
3017
3018 BEGIN
3019 parse_sql_string(p_string_in, px_sql_pieces);
3020 EXCEPTION
3021 WHEN OTHERS THEN
3022 RETURN(FF_VERROR);
3023 END;
3024
3025 --
3026 -- Derive bind values
3027 --
3028 FOR i IN 1 .. px_sql_pieces.COUNT LOOP
3029 IF (px_sql_pieces(i).piece_type = SSP_PIECE_TYPE_BIND) THEN
3030 v_code := convert_bind_token2(px_sql_pieces(i).piece_text,
3031 p_str_info, p_seg_info,
3032 p_vdate, p_parent_value,
3033 p_nprev, p_prev_dispvals,
3034 p_prev_vals, p_prev_ids, p_prev_descs,
3035 p_prev_segnames, p_prev_vsnames,
3036 px_sql_pieces(i).bind_value);
3037
3038 IF (v_code <> FF_VVALID) then
3039 return(v_code);
3040 end if;
3041 END IF;
3042 END LOOP;
3043
3044 return(FF_VVALID);
3045
3046 EXCEPTION
3047 WHEN OTHERS then
3048 IF (fnd_flex_server1.g_debug_level > 0) THEN
3049 add_debug('EXCEPTION SV1.substitute_flex_binds5()');
3050 END IF;
3051 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3052 FND_MESSAGE.set_token('MSG', 'substitute_flex_binds5() exception: '
3053 || SQLERRM);
3054 return(FF_VERROR);
3055 END substitute_flex_binds5;
3056
3057 /* ----------------------------------------------------------------------- */
3058 /* Converts a flex bind token to its character string value. */
3059 /* Bind tokens begin with a colon and are of the form: */
3060 /* :$FLEX$.<vsname>[.<portion>] or :$PROFILES$.<profile_name> */
3061 /* If [.<portion>] not specified uses the ID not the VALUE. */
3062 /* Note server can never handle :BLOCK.FIELD */
3063 /* Returns FF_VVALID if substitution succeeded, FF_VUNKNOWN if */
3064 /* unsupported or FF_VERROR on error. */
3065 /* ----------------------------------------------------------------------- */
3066 FUNCTION convert_bind_token(bind_token IN VARCHAR2,
3067 nprev IN NUMBER,
3068 prev_dispvals IN ValueArray,
3069 prev_vals IN ValueArray,
3070 prev_ids IN ValueIdArray,
3071 prev_descs IN ValueDescArray,
3072 prev_segnames IN SegNameArray,
3073 prev_vsnames IN VsNameArray,
3074 bind_value OUT nocopy VARCHAR2)
3075 RETURN VARCHAR2
3079 seg_portion VARCHAR2(30);
3076 IS
3077 bind_val VARCHAR2(2000);
3078 seg_name VARCHAR2(60);
3080 dot_pointer NUMBER;
3081 col_pointer NUMBER;
3082 l_length NUMBER;
3083 s_index BINARY_INTEGER;
3084 l_return_code VARCHAR2(100);
3085
3086 BEGIN
3087 -- Determine character string value of bind token.
3088 -- :$PROFILES$.<name> where <name> is case-insensitive profile option name.
3089 -- :$FLEX$.<name>[.<portion>] where name is case-sensitive, but portion not.
3090 -- :BLOCK.FIELD causes return of FF_VUNKNOWN error.
3091 --
3092 IF (fnd_flex_server1.g_debug_level > 0) THEN
3093 add_debug('BEGIN SV1.convert_bind_token(token=' || bind_token || ')');
3094 END IF;
3095
3096 IF (INSTR(bind_token, ':$PROFILES$.') = 1) then
3097 FND_PROFILE.get(UPPER(SUBSTR(bind_token, 13)), bind_val);
3098
3099 ELSIF (INSTR(bind_token, ':$FLEX$.') = 1) then
3100
3101 -- If default segment name is of the form <name>.ID, <name>.VALUE
3102 -- or <name>.MEANING, then break it into seg_name and seg_portion.
3103 -- Note ID, VALUE and MEANING are all case insensitive
3104 --
3105 -- Bug 1783461: Flexfield API does not handle :NULL suffix.
3106 -- the original code did not strip off the variables after the value set/
3107 -- or segment name and when used resulted in a "ORA-20001 The data that
3108 -- defines the flexfield may be inconsistent".
3109 -- The syntax for using bind variables in the default where clause of a
3110 -- value set is as follows (for $FLEX$);
3111 --
3112 -- :$FLEX$.[valuesetname|segmentname][.ID|.VALUE|.MEANING][:NULL]
3113
3114 dot_pointer := INSTR(bind_token, '.', 9);
3115 col_pointer := INSTR(bind_token,':NULL',9);
3116 l_length := length(bind_token);
3117 if (dot_pointer > 0) then
3118 if (col_pointer > 0) then
3119 null;
3120 else
3121 col_pointer := l_length + 1;
3122 end if;
3123 else
3124 if (col_pointer > 0) then
3125 dot_pointer := col_pointer;
3126 else
3127 dot_pointer := l_length + 1;
3128 col_pointer := l_length + 1;
3129 end if;
3130 end if;
3131
3132 if (dot_pointer > col_pointer) then
3133 FND_MESSAGE.set_name('FND','FLEX-INVALID PORTION');
3134 FND_MESSAGE.set_token('BTOKEN',bind_token);
3135 l_return_code := FF_VERROR;
3136 GOTO goto_return;
3137 end if;
3138
3139 seg_name := SUBSTR(bind_token,9,dot_pointer-9);
3140 seg_portion := SUBSTR(bind_token,dot_pointer+1,col_pointer-dot_pointer-1);
3141
3142 if (seg_portion is null) then
3143 seg_portion := 'ID';
3144 end if;
3145
3146 -- Find index to previous segment or 0 if previous segment not found.
3147 -- Previous segment value set name or segment name.
3148 --
3149 s_index := 0;
3150 for i in reverse 1..nprev loop
3151 if((prev_segnames(i) = seg_name) or (prev_vsnames(i) = seg_name)) then
3152 s_index := i;
3153 exit;
3154 end if;
3155 end loop;
3156
3157 -- Copy value, id or meaning to output if found, otherwise error.
3158 --
3159 if(s_index > 0) then
3160 if(seg_portion = 'VALUE') then
3161 bind_val := prev_vals(s_index);
3162 elsif(seg_portion = 'ID') then
3163 bind_val := prev_ids(s_index);
3164 elsif(seg_portion = 'MEANING') then
3165 bind_val := prev_descs(s_index);
3166 else
3167 FND_MESSAGE.set_name('FND', 'FLEX-INVALID PORTION');
3168 FND_MESSAGE.set_token('BTOKEN', bind_token);
3169 l_return_code := FF_VERROR;
3170 GOTO goto_return;
3171 end if;
3172 else
3173 FND_MESSAGE.set_name('FND', 'FLEX-PRIOR SEG NOTFOUND');
3174 FND_MESSAGE.set_token('BTOKEN', bind_token);
3175 l_return_code := FF_VERROR;
3176 GOTO goto_return;
3177 end if;
3178
3179 else
3180
3181 -- :BLOCK.FIELD cannot be handled here
3182 --
3183 FND_MESSAGE.set_name('FND', 'FLEX-UNSUPPORTED FLEX BIND');
3184 l_return_code := FF_VUNKNOWN;
3185 GOTO goto_return;
3186
3187 end if;
3188
3189 bind_value := bind_val;
3190 l_return_code := FF_VVALID;
3191
3192 <<goto_return>>
3193 IF (fnd_flex_server1.g_debug_level > 0) THEN
3194 add_debug('END SV1.convert_bind_token(value=' || bind_val || ')');
3195 END IF;
3196 RETURN(l_return_code);
3197 EXCEPTION
3198 WHEN OTHERS then
3199 IF (fnd_flex_server1.g_debug_level > 0) THEN
3200 add_debug('EXCEPTION others SV1.convert_bind_token()');
3201 END IF;
3202 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3203 FND_MESSAGE.set_token('MSG','convert_bind_token() exception: '||SQLERRM);
3204 return(FF_VERROR);
3205 END convert_bind_token;
3206
3207 --
3208 -- The one with extension support.
3209 --
3210 FUNCTION convert_bind_token2(p_bind_token IN VARCHAR2,
3211 p_str_info IN FlexStructId,
3212 p_seg_info IN SegmentInfo,
3216 p_prev_dispvals IN ValueArray,
3213 p_vdate IN DATE,
3214 p_parent_value IN VARCHAR2,
3215 p_nprev IN NUMBER,
3217 p_prev_vals IN ValueArray,
3218 p_prev_ids IN ValueIdArray,
3219 p_prev_descs IN ValueDescArray,
3220 p_prev_segnames IN SegNameArray,
3221 p_prev_vsnames IN VsNameArray,
3222 x_bind_value OUT nocopy VARCHAR2)
3223 RETURN VARCHAR2
3224 IS
3225 l_bind_value VARCHAR2(2000);
3226 l_return_code VARCHAR2(100);
3227
3228 BEGIN
3229 IF (fnd_flex_server1.g_debug_level > 0) THEN
3230 add_debug('BEGIN SV1.convert_bind_token2(bind=' || p_bind_token || ')');
3231 END IF;
3232
3233 l_bind_value := NULL;
3234 IF (p_bind_token = ':$FLEX$.$VDATE$') THEN
3235 IF (p_vdate IS NULL) THEN
3236 l_bind_value := NULL;
3237 ELSE
3238 l_bind_value := To_char(p_vdate, 'YYYY/MM/DD HH24:MI:SS');
3239 END IF;
3240
3241 ELSIF (p_bind_token = ':$FLEX$.$APPLICATION_ID$') THEN
3242 l_bind_value := p_str_info.application_id;
3243
3244 ELSIF (p_bind_token = ':$FLEX$.$ID_FLEX_CODE$') THEN
3245 IF (p_str_info.isa_key_flexfield) THEN
3246 l_bind_value := p_str_info.id_flex_code;
3247 END IF;
3248
3249 ELSIF (p_bind_token = ':$FLEX$.$ID_FLEX_NUM$') THEN
3250 IF (p_str_info.isa_key_flexfield) THEN
3251 l_bind_value := p_str_info.id_flex_num;
3252 END IF;
3253
3254 ELSIF (p_bind_token = ':$FLEX$.$APPLICATION_COLUMN_NAME$') THEN
3255 l_bind_value := p_seg_info.colname;
3256
3257 ELSIF (p_bind_token = ':$FLEX$.$VALUE_SET_ID$') THEN
3258 l_bind_value := p_seg_info.vsid;
3259
3260 ELSIF (p_bind_token = ':$FLEX$.$PARENT_VALUE$') THEN
3261 l_bind_value := p_parent_value;
3262
3263 ELSE
3264 l_return_code := convert_bind_token(p_bind_token, p_nprev,
3265 p_prev_dispvals, p_prev_vals,
3266 p_prev_ids, p_prev_descs,
3267 p_prev_segnames, p_prev_vsnames,
3268 x_bind_value);
3269 GOTO goto_return;
3270 END IF;
3271
3272 x_bind_value := l_bind_value;
3273 l_return_code := FF_VVALID;
3274
3275 <<goto_return>>
3276 IF (fnd_flex_server1.g_debug_level > 0) THEN
3277 add_debug('END SV1.convert_bind_token2(value=' || l_bind_value || ')');
3278 END IF;
3279 RETURN(l_return_code);
3280 EXCEPTION
3281 WHEN OTHERS then
3282 IF (fnd_flex_server1.g_debug_level > 0) THEN
3283 add_debug('EXCEPTION others SV1.convert_bind_token2()');
3284 END IF;
3285 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3286 FND_MESSAGE.set_token('MSG','convert_bind_token2() exception: '||SQLERRM);
3287 return(FF_VERROR);
3288 END convert_bind_token2;
3289
3290 /* ----------------------------------------------------------------------- */
3291 /* Checks vrules. Returns TRUE and sets error message if any are */
3292 /* violated. Checks rules in forward order for compatibility with */
3293 /* C code. Also checks SUMMARY_FLAG. */
3294 /* ----------------------------------------------------------------------- */
3295 FUNCTION check_vrules(vrs IN Vrules,
3296 sqs IN Qualifiers,
3297 sumflg IN VARCHAR2) RETURN VARCHAR2 IS
3298
3299 testval VARCHAR2(245);
3300
3301 BEGIN
3302
3303 -- Check vrules in forward order for compatibility with C code.
3304 --
3305 for i in 1..vrs.nvrules loop
3306 if((vrs.fq_names(i) is null) and (vrs.sq_names(i) = 'SUMMARY_FLAG')) then
3307 testval := SEPARATOR || sumflg || SEPARATOR;
3308 if(INSTR(vrs.cat_vals(i), testval) > 0) then
3309 if(vrs.ie_flags(i) = 'E') then
3310 value_error_name(vrs.app_names(i), vrs.err_names(i));
3311 return(FF_VVRULE);
3312 end if;
3313 else
3314 if(vrs.ie_flags(i) = 'I') then
3315 value_error_name(vrs.app_names(i), vrs.err_names(i));
3316 return(FF_VVRULE);
3317 end if;
3318 end if;
3319 else
3320 for j in 1..sqs.nquals loop
3321 if((vrs.sq_names(i) = sqs.sq_names(j)) and
3322 (vrs.fq_names(i) = sqs.fq_names(j))) then
3323 testval := SEPARATOR || sqs.sq_values(j) || SEPARATOR;
3324 if((vrs.cat_vals(i) is not null) and
3325 (INSTR(vrs.cat_vals(i), testval) > 0)) then
3326 if(vrs.ie_flags(i) = 'E') then
3327 value_error_name(vrs.app_names(i), vrs.err_names(i));
3328 return(FF_VVRULE);
3329 end if;
3330 else
3331 if(vrs.ie_flags(i) = 'I') then
3332 value_error_name(vrs.app_names(i), vrs.err_names(i));
3333 return(FF_VVRULE);
3334 end if;
3335 end if;
3336 end if;
3337 end loop;
3338 end if;
3339 end loop;
3340
3341 return(FF_VVALID);
3342
3343 EXCEPTION
3344 WHEN OTHERS then
3348
3345 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3346 FND_MESSAGE.set_token('MSG', 'check_vrules() exception: ' || SQLERRM);
3347 return(FF_VERROR);
3349 END check_vrules;
3350
3351 /* ----------------------------------------------------------------------- */
3352 /* Checks vrules on combinations based on the qualifiers in the */
3353 /* combinations table. This function is external to this file being */
3354 /* called from AFFFSSVB.pls. */
3355 /* Returns VV_VALID if all rules pass, or VV_VALUES or VV_ERROR and */
3356 /* sets message name if any rules violated. */
3357 /* ----------------------------------------------------------------------- */
3358
3359 FUNCTION check_comb_vrules(vrs IN Vrules,
3360 sqs IN Qualifiers,
3361 sumflg IN VARCHAR2) RETURN NUMBER IS
3362 v_code VARCHAR2(1);
3363 retcode NUMBER;
3364
3365 BEGIN
3366
3367 -- First must initialize the value errors since we always want to display
3368 -- the error if any rule violated.
3369 --
3370 value_error_init;
3371
3372 -- Now call function above to check the rules.
3373 --
3374 v_code := check_vrules(vrs, sqs, sumflg);
3375 if(v_code = FF_VVALID) then
3376 IF (fnd_flex_server1.g_debug_level > 0) THEN
3377 add_debug(' Combination passed vrules. ');
3378 END IF;
3379 retcode := VV_VALID;
3380 elsif(v_code = FF_VVRULE) then
3381 IF (fnd_flex_server1.g_debug_level > 0) THEN
3382 add_debug(' Combination failed vrules. ');
3383 END IF;
3384 retcode := VV_VALUES;
3385 else
3386 retcode := VV_ERROR;
3387 end if;
3388
3389 return(retcode);
3390
3391 EXCEPTION
3392 WHEN OTHERS then
3393 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3394 FND_MESSAGE.set_token('MSG','check_comb_vrules() exception: '||SQLERRM);
3395 return(VV_ERROR);
3396
3397 END check_comb_vrules;
3398
3399 /* ----------------------------------------------------------------------- */
3400 /* Checks value security rules for any which are violated. */
3401 /* Returns status codes of FF_VVALID if no security rule violated, */
3402 /* FF_VSECURED if security violated, or FF_VERROR if some error */
3403 /* checking security. */
3404 /* If security is violated a value error message is added */
3405 /* only if set_message is TRUE. */
3406 /* ----------------------------------------------------------------------- */
3407
3408 FUNCTION check_security(val IN VARCHAR2,
3409 valfmt IN VARCHAR2,
3410 parentval IN VARCHAR2,
3411 user_apid IN NUMBER,
3412 user_respid IN NUMBER,
3413 vsinfo IN ValueSetInfo,
3414 set_message IN BOOLEAN) RETURN VARCHAR2 IS
3415
3416 bufstr VARCHAR2(500);
3417 rulemsgbuf VARCHAR2(240);
3418 nfound NUMBER;
3419 l_vsc_code VARCHAR2(10);
3420 l_return_code VARCHAR2(10);
3421 l_security_status VARCHAR2(2000);
3422 l_error_message VARCHAR2(2000);
3423 BEGIN
3424 IF (fnd_flex_server1.g_debug_level > 0) THEN
3425 add_debug('BEGIN SV1.check_security()');
3426 END IF;
3427
3428 --
3429 -- Return immediately if user info is not set.
3430 --
3431 IF ((user_apid = -1) AND (user_respid = -1)) THEN
3432 RETURN(FF_VVALID);
3433 END IF;
3434 --
3435 -- First check the VSC
3436 --
3437 l_vsc_code := check_vsc(user_apid, user_respid, vsinfo.vsid,
3438 parentval, val, l_return_code);
3439 IF (l_vsc_code IN (fnd_plsql_cache.CACHE_VALID,
3440 fnd_plsql_cache.CACHE_INVALID)) THEN
3441 IF (fnd_flex_server1.g_debug_level > 0) THEN
3442 add_debug('Found in vsc.');
3443 END IF;
3444 GOTO label_return;
3445 END IF;
3446
3447 --
3448 -- l_vsc_code is either CACHE_NOTFOUND.
3449 -- Continue on security check.
3450 --
3451 l_return_code := FF_VVALID;
3452
3453 fnd_flex_server.check_value_security
3454 (p_security_check_mode => 'YH',
3455 p_flex_value_set_id => vsinfo.vsid,
3456 p_parent_flex_value => parentval,
3457 p_flex_value => val,
3458 p_resp_application_id => user_apid,
3459 p_responsibility_id => user_respid,
3460 x_security_status => l_security_status,
3461 x_error_message => l_error_message);
3462 IF (fnd_flex_server1.g_debug_level > 0) THEN
3463 add_debug('Hierarchy security: status :'||l_security_status || ' message : ' || l_error_message);
3464 END IF;
3465
3466 IF (l_security_status <> 'NOT-SECURED') THEN
3467 IF (set_message) THEN
3468 value_error_name('FND', 'FLEX-USER DEFINED ERROR');
3469 value_error_token('MSG', l_error_message);
3470 END IF;
3471 l_return_code := FF_VSECURED;
3472 GOTO label_return;
3473 END IF;
3474
3475 GOTO label_return;
3476
3477
3478 <<label_return>>
3482 END IF;
3479 IF (l_vsc_code IN (fnd_plsql_cache.CACHE_NOTFOUND)) THEN
3480 update_vsc(user_apid, user_respid, vsinfo.vsid,
3481 parentval, val, l_return_code);
3483 IF (fnd_flex_server1.g_debug_level > 0) THEN
3484 add_debug('END SV1.check_security()');
3485 END IF;
3486 RETURN(l_return_code);
3487 EXCEPTION
3488 WHEN OTHERS then
3489 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3490 FND_MESSAGE.set_token('MSG','check_security() exception: ' || SQLERRM);
3491 IF (fnd_flex_server1.g_debug_level > 0) THEN
3492 add_debug('EXCEPTION SV1.check_security()');
3493 END IF;
3494
3495 return(FF_VERROR);
3496
3497 END check_security;
3498
3499 /* ----------------------------------------------------------------------- */
3500 /* Checks to see whether this segment is displayed. */
3501 /* Returns displayed_flag as output. */
3502 /* Returns TRUE on success or FALSE if error or exception. */
3503 /* ----------------------------------------------------------------------- */
3504
3505 FUNCTION check_displayed(segindex IN NUMBER,
3506 disp_tokmap IN DisplayedSegs,
3507 d_flag OUT nocopy BOOLEAN) RETURN BOOLEAN IS
3508
3509 BEGIN
3510
3511 d_flag := disp_tokmap.segflags(segindex);
3512 return(TRUE);
3513
3514 EXCEPTION
3515 WHEN OTHERS then
3516 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3517 FND_MESSAGE.set_token('MSG','check_displayed() exception: ' ||SQLERRM);
3518 return(FALSE);
3519
3520 END check_displayed;
3521
3522 /* ----------------------------------------------------------------------- */
3523 /* Interprets the compiled value attribute (CVA) string and returns */
3524 /* segment qualifier values. From the value set id looks up all */
3525 /* qualifiers which might be associated with this value set and */
3526 /* orders them in the same order as they are stored in the CVA */
3527 /* string. Then interprets the CVA string. For each qualifier */
3528 /* associated with this flexfield segment, the default qualifier value*/
3529 /* is overwritten by the value from the CVA string if the CVA string */
3530 /* contains a non-null value. Returns the updated qualifier values */
3531 /* array and number of qualifier values found or < 0 if error. */
3532 /* No qualifier values for descriptive flexfields. */
3533 /* */
3534 /* Note: Do not order by segment_attribute_type as this causes */
3535 /* GL_ACCOUNT/GL_ACCOUNT_TYPE to appear before GL_GLOBAL/DETAIL... */
3536 /* instead of after it as is the case in the client code. */
3537 /* ----------------------------------------------------------------------- */
3538 FUNCTION qualifier_values(ffstruct IN FlexStructId,
3539 valset_id IN NUMBER,
3540 cvas IN VARCHAR2,
3541 nqualifs IN NUMBER,
3542 fqnames IN QualNameArray,
3543 sqnames IN QualNameArray,
3544 sqvals IN OUT nocopy ValAttribArray) RETURN NUMBER IS
3545
3546 cva_index NUMBER;
3547 n_value_attributes NUMBER;
3548 value_attributes StringArray;
3549 l_vsq_code VARCHAR2(200);
3550 i NUMBER;
3551
3552 CURSOR CVA_Cursor(vs_id IN NUMBER) IS
3553 SELECT id_flex_application_id fapid, id_flex_code fcode,
3554 segment_attribute_type fqname, value_attribute_type sqname
3555 FROM fnd_flex_validation_qualifiers
3556 WHERE flex_value_set_id = vs_id
3557 ORDER BY assignment_date, id_flex_application_id, id_flex_code,
3558 value_attribute_type;
3559 vq cva_cursor%ROWTYPE;
3560 BEGIN
3561 -- Quickly handle case where there are no qualifiers. (eg Descr flex)
3562 --
3563 if((not ffstruct.isa_key_flexfield) or (nqualifs = 0)) then
3564 return(0);
3565 end if;
3566
3567 -- Convert compiled value attributes to array.
3568 --
3569 IF (cvas IS NOT NULL) THEN
3570 n_value_attributes := to_stringarray2(cvas, NEWLINE, value_attributes);
3571 ELSE
3572 n_value_attributes := 0;
3573 END IF;
3574
3575 -- Set returned segment qualifier values to defaults
3576 --
3577 -- for i in 1..nqualifs loop
3578 -- sqvals(i) := default_sqvals(i);
3579 -- end loop;
3580
3581 -- cva_index is position of the value attribute in the CVA string.
3582 --
3583 cva_index := 0;
3584
3585 -- CVA_Cursor returns the flexfields and names of the attibutes associated
3586 -- with this value set in the order that they are stored in the cva string.
3587 --
3588
3589 g_cache_key := valset_id;
3590
3591 fnd_plsql_cache.generic_1tom_get_values(vsq_cache_controller,
3592 vsq_cache_storage,
3593 g_cache_key,
3594 g_cache_numof_values,
3595 g_cache_values,
3596 g_cache_return_code);
3597
3601 i := 0;
3598 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3599 NULL;
3600 ELSE
3602 FOR vq IN cva_cursor(valset_id) LOOP
3603 i := i + 1;
3604 fnd_plsql_cache.generic_cache_new_value
3605 (x_value => g_cache_value,
3606 p_number_1 => vq.fapid,
3607 p_varchar2_1 => vq.fcode,
3608 p_varchar2_2 => vq.fqname,
3609 p_varchar2_3 => vq.sqname);
3610 g_cache_values(i) := g_cache_value;
3611 END LOOP;
3612 g_cache_numof_values := i;
3613
3614 fnd_plsql_cache.generic_1tom_put_values(vsq_cache_controller,
3615 vsq_cache_storage,
3616 g_cache_key,
3617 g_cache_numof_values,
3618 g_cache_values);
3619 END IF;
3620
3621 FOR ii IN 1..g_cache_numof_values LOOP
3622 vq.fapid := g_cache_values(ii).number_1;
3623 vq.fcode := g_cache_values(ii).varchar2_1;
3624 vq.fqname := g_cache_values(ii).varchar2_2;
3625 vq.sqname := g_cache_values(ii).varchar2_3;
3626
3627 -- for vq in CVA_Cursor(valset_id) loop
3628 cva_index := cva_index + 1;
3629
3630 -- If the selected attribute applies to the flexfied under consideration
3631 -- then find the attribute by name in the list of those which apply to this
3632 -- segment and replace its default value with the appropriate value from
3633 -- the cva string.
3634 --
3635 if((vq.fapid = ffstruct.application_id) and
3636 (vq.fcode = ffstruct.id_flex_code)) then
3637 for i in 1..nqualifs loop
3638 if((vq.fqname = fqnames(i)) and (vq.sqname = sqnames(i)) and
3639 (cva_index <= n_value_attributes)) then
3640 sqvals(i) := value_attributes(cva_index);
3641 end if;
3642 end loop;
3643 end if;
3644 end loop;
3645
3646 return(cva_index);
3647
3648 EXCEPTION
3649 WHEN OTHERS then
3650 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3651 FND_MESSAGE.set_token('MSG', 'qualifier_values() exception: '||SQLERRM);
3652 return(-1);
3653
3654 END qualifier_values;
3655
3656 /* ----------------------------------------------------------------------- */
3657 /* Gets the **enabled** flexfield and segment qualifiers for this */
3658 /* segment. Segment is uniquely determined by combination of */
3659 /* flex apid, code, struct num, with the name of the column in the */
3660 /* code combinations table which will hold the segment value. */
3661 /* No qualifiers enabled for descriptive flexs. */
3662 /* */
3663 /* Returns the number of qualifiers found or < 0 if error. */
3664 /* */
3665 /* Also returns FQ name, SQ name, derived_column, and default value */
3666 /* of the segment qualifiers for this segment. */
3667 /* ----------------------------------------------------------------------- */
3668 FUNCTION get_qualifiers(ffstruct IN FlexStructId,
3669 seg_colname IN VARCHAR2,
3670 seg_quals OUT nocopy Qualifiers)
3671 RETURN NUMBER
3672 IS
3673 sqcount NUMBER;
3674
3675 CURSOR Qual_Cursor(keystruct IN FlexStructId, colname IN VARCHAR2) IS
3676 SELECT v.segment_attribute_type fq_name,
3677 v.value_attribute_type sq_name,
3678 v.application_column_name drv_colname,
3679 v.default_value dflt_val
3680 FROM fnd_value_attribute_types v, fnd_segment_attribute_values s
3681 WHERE v.application_id = s.application_id
3682 AND v.id_flex_code = s.id_flex_code
3683 AND v.segment_attribute_type = s.segment_attribute_type
3684 AND s.application_id = keystruct.application_id
3685 AND s.id_flex_code = keystruct.id_flex_code
3686 AND s.id_flex_num = keystruct.id_flex_num
3687 AND s.application_column_name = colname
3688 AND s.attribute_value = 'Y';
3689 BEGIN
3690 IF (fnd_flex_server1.g_debug_level > 0) THEN
3691 fnd_flex_server1.add_debug('BEGIN SV1.get_qualifiers()');
3692 END IF;
3693
3694 sqcount := 0;
3695 IF (ffstruct.isa_key_flexfield) THEN
3696 g_cache_key := (ffstruct.application_id || '.' ||
3697 ffstruct.id_flex_code || '.' ||
3698 ffstruct.id_flex_num || '.' ||
3699 seg_colname);
3700
3701 fnd_plsql_cache.generic_1tom_get_values(fsq_cache_controller,
3702 fsq_cache_storage,
3703 g_cache_key,
3704 g_cache_numof_values,
3705 g_cache_values,
3706 g_cache_return_code);
3707
3708 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3709 sqcount := g_cache_numof_values;
3710 ELSE
3711 FOR squal IN qual_cursor(ffstruct, seg_colname) LOOP
3712 sqcount := sqcount + 1;
3713
3714 fnd_plsql_cache.generic_cache_new_value
3715 (x_value => g_cache_value,
3719 p_varchar2_4 => squal.drv_colname);
3716 p_varchar2_1 => squal.fq_name,
3717 p_varchar2_2 => squal.sq_name,
3718 p_varchar2_3 => squal.dflt_val,
3720 g_cache_values(sqcount) := g_cache_value;
3721 END LOOP;
3722 g_cache_numof_values := sqcount;
3723
3724 fnd_plsql_cache.generic_1tom_put_values(fsq_cache_controller,
3725 fsq_cache_storage,
3726 g_cache_key,
3727 g_cache_numof_values,
3728 g_cache_values);
3729 END IF;
3730
3731 FOR ii IN 1..sqcount LOOP
3732 seg_quals.fq_names(ii) := g_cache_values(ii).varchar2_1;
3733 seg_quals.sq_names(ii) := g_cache_values(ii).varchar2_2;
3734 seg_quals.sq_values(ii) := g_cache_values(ii).varchar2_3;
3735 seg_quals.derived_cols(ii) := g_cache_values(ii).varchar2_4;
3736 END LOOP;
3737 END IF;
3738
3739 seg_quals.nquals := sqcount;
3740
3741 IF (fnd_flex_server1.g_debug_level > 0) THEN
3742 fnd_flex_server1.add_debug('END SV1.get_qualifiers()');
3743 END IF;
3744
3745 RETURN(sqcount);
3746 EXCEPTION
3747 WHEN OTHERS then
3748 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3749 FND_MESSAGE.set_token('MSG', 'get_qualifiers() exception: ' || SQLERRM);
3750 IF (fnd_flex_server1.g_debug_level > 0) THEN
3751 fnd_flex_server1.add_debug('EXCEPTION SV1.get_qualifiers()');
3752 END IF;
3753
3754 return(-1);
3755
3756 END get_qualifiers;
3757
3758 /* ----------------------------------------------------------------------- */
3759 /* Gets the value set information for a given value set id. */
3760 /* Returns TRUE if found or FALSE and sets error message on error. */
3761 /* ----------------------------------------------------------------------- */
3762 FUNCTION get_value_set(value_set_id IN NUMBER,
3763 segment_name IN VARCHAR2,
3764 vs_info OUT nocopy ValueSetInfo)
3765 RETURN BOOLEAN
3766 IS
3767 l_vsi valuesetinfo;
3768 BEGIN
3769 IF (fnd_flex_server1.g_debug_level > 0) THEN
3770 fnd_flex_server1.add_debug('CALL SV1.get_value_set(' ||
3771 'vsid:' || value_set_id || ')');
3772 END IF;
3773
3774 g_cache_key := value_set_id;
3775 fnd_plsql_cache.generic_1to1_get_value(vst_cache_controller,
3776 vst_cache_storage,
3777 g_cache_key,
3778 g_cache_value,
3779 g_cache_return_code);
3780
3781 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
3782 vs_info.vsid := value_set_id;
3783 vs_info.parent_vsid := g_cache_value.number_1;
3784 vs_info.valsecure := g_cache_value.varchar2_1;
3785 vs_info.valtype := g_cache_value.varchar2_2;
3786 vs_info.vsformat := g_cache_value.varchar2_3;
3787 vs_info.maxsize := g_cache_value.number_2;
3788 vs_info.lettersok := g_cache_value.varchar2_4;
3789 vs_info.capsonly := g_cache_value.varchar2_5;
3790 vs_info.zfill := g_cache_value.varchar2_6;
3791 vs_info.precis := g_cache_value.number_3;
3792 vs_info.minval := g_cache_value.varchar2_7;
3793 vs_info.maxval := g_cache_value.varchar2_8;
3794 vs_info.vsname := g_cache_value.varchar2_9;
3795 ELSE
3796 SELECT flex_value_set_id, parent_flex_value_set_id,
3797 security_enabled_flag,
3798 validation_type, format_type, maximum_size,
3799 alphanumeric_allowed_flag, uppercase_only_flag,
3800 numeric_mode_enabled_flag, number_precision, minimum_value,
3801 maximum_value, flex_value_set_name
3802 INTO l_vsi
3803 FROM fnd_flex_value_sets
3804 WHERE flex_value_set_id = value_set_id;
3805
3806 fnd_plsql_cache.generic_cache_new_value
3807 (x_value => g_cache_value,
3808 p_number_1 => l_vsi.parent_vsid,
3809 p_varchar2_1 => l_vsi.valsecure,
3810 p_varchar2_2 => l_vsi.valtype,
3811 p_varchar2_3 => l_vsi.vsformat,
3812 p_number_2 => l_vsi.maxsize,
3813 p_varchar2_4 => l_vsi.lettersok,
3814 p_varchar2_5 => l_vsi.capsonly,
3815 p_varchar2_6 => l_vsi.zfill,
3816 p_number_3 => l_vsi.precis,
3817 p_varchar2_7 => l_vsi.minval,
3818 p_varchar2_8 => l_vsi.maxval,
3819 p_varchar2_9 => l_vsi.vsname);
3820
3821 fnd_plsql_cache.generic_1to1_put_value(vst_cache_controller,
3822 vst_cache_storage,
3823 g_cache_key,
3824 g_cache_value);
3825 vs_info := l_vsi;
3826 END IF;
3827 return(TRUE);
3828 EXCEPTION
3829 WHEN NO_DATA_FOUND then
3830 FND_MESSAGE.set_name('FND', 'FLEX-VALUE SET NOT FOUND');
3831 FND_MESSAGE.set_token('SEGMENT', segment_name);
3832 return(FALSE);
3833 WHEN OTHERS then
3834 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3835 FND_MESSAGE.set_token('MSG', 'get_value_set() exception: ' || SQLERRM);
3836 return(FALSE);
3840 /* Creates value set information for segment without a value set */
3837 END get_value_set;
3838
3839 /* ----------------------------------------------------------------------- */
3841 /* based on the specification of the column into which it will go. */
3842 /* We document that this only works for CHAR or VARCHAR2 type columns.*/
3843 /* Returns TRUE if ok or FALSE and sets error message on error. */
3844 /* ----------------------------------------------------------------------- */
3845 FUNCTION virtual_value_set(column_type IN VARCHAR2,
3846 column_width IN NUMBER,
3847 segment_name IN VARCHAR2,
3848 vs_info OUT nocopy ValueSetInfo) RETURN BOOLEAN IS
3849 BEGIN
3850
3851 -- Assumes all components of vs_info are initially null
3852 --
3853 if(column_type in ('C', 'V')) then
3854 vs_info.valsecure := 'N';
3855 vs_info.valtype := 'N';
3856 vs_info.vsformat := 'C';
3857 vs_info.maxsize := column_width;
3858 vs_info.lettersok := 'Y';
3859 vs_info.capsonly := 'N';
3860 vs_info.zfill := 'N';
3861 return(TRUE);
3862 end if;
3863 FND_MESSAGE.set_name('FND', 'FLEX-VALUE SET REQUIRED');
3864 FND_MESSAGE.set_token('SEGMENT', segment_name);
3865 return(FALSE);
3866
3867 EXCEPTION
3868 WHEN OTHERS then
3869 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3870 FND_MESSAGE.set_token('MSG','virtual_value_set() exception: '||SQLERRM);
3871 return(FALSE);
3872
3873 END virtual_value_set;
3874
3875 /* ----------------------------------------------------------------------- */
3876 /* Message handler functions for value validation errors. Needed */
3877 /* because value validation does not always stop at the first */
3878 /* error encountered. This handler makes sure that FND_MESSAGE */
3879 /* error is set to the first value error if there are no other */
3880 /* errors. For more serious errors, we set the message directly */
3881 /* using the FND_MESSAGE package. Note this assumes the main */
3882 /* validate_struct() function knows to stop on the first serious */
3883 /* error otherwise we risk overwriting a more serious error with */
3884 /* a value error. */
3885 /* If the application short name is null, then we use the */
3886 /* FLEX-USER DEFINED ERROR to display the message name as text. */
3887 /* This is used in check_vrules(). */
3888 /* ----------------------------------------------------------------------- */
3889
3890 /* ----------------------------------------------------------------------- */
3891 /* Initializes value error message global variables. */
3892 /* ----------------------------------------------------------------------- */
3893
3894 PROCEDURE value_error_init IS
3895 BEGIN
3896 value_error_set := FALSE;
3897 entering_new_message := FALSE;
3898 EXCEPTION
3899 WHEN OTHERS then
3900 IF (fnd_flex_server1.g_debug_level > 0) THEN
3901 add_debug('value_error_init() exception: ' || SQLERRM);
3902 END IF;
3903 END value_error_init;
3904
3905 /* ------------------------------------------------------------------------ */
3906 /* Sets error message name if no other value errors have been set. */
3907 /* Also sets entering_new_message flag to indicate new message being */
3908 /* entered if this is the first value error, otherwise resets it. */
3909 /* ------------------------------------------------------------------------ */
3910 PROCEDURE value_error_name(appl_sname IN VARCHAR2,
3911 errmsg_name IN VARCHAR2) IS
3912 BEGIN
3913 if(value_error_set = FALSE) then
3914 if(appl_sname is null) then
3915 FND_MESSAGE.set_name('FND', 'FLEX-USER DEFINED ERROR');
3916 FND_MESSAGE.set_token('MSG', errmsg_name);
3917 else
3918 FND_MESSAGE.set_name(appl_sname, errmsg_name);
3919 end if;
3920 value_error_set := TRUE;
3921 entering_new_message := TRUE;
3922 else
3923 entering_new_message := FALSE;
3924 end if;
3925 EXCEPTION
3926 WHEN OTHERS then
3927 IF (fnd_flex_server1.g_debug_level > 0) THEN
3928 add_debug('value_error_name() exception: ' || SQLERRM);
3929 END IF;
3930 END value_error_name;
3931
3932 /* ----------------------------------------------------------------------- */
3933 /* Sets error message token if currently entering a new message. */
3934 /* Otherwise just ignores the token. Call only after setting */
3935 /* value error name with value_error_name() above. */
3936 /* ----------------------------------------------------------------------- */
3937 PROCEDURE value_error_token(token_name IN VARCHAR2,
3938 token_value IN VARCHAR2) IS
3939 BEGIN
3940 if(entering_new_message) then
3941 FND_MESSAGE.set_token(token_name, token_value);
3942 end if;
3943 EXCEPTION
3944 WHEN OTHERS then
3945 IF (fnd_flex_server1.g_debug_level > 0) THEN
3946 add_debug('value_error_token() exception: ' || SQLERRM);
3947 END IF;
3948 END value_error_token;
3949
3950 /* ----------------------------------------------------------------------- */
3951 /* Converts the value to be output in an error message from stored */
3955 FUNCTION msg_val(valset_fmt IN VARCHAR2,
3952 /* format to the displayed format appropriate for the given value set.*/
3953 /* Returns the value in stored format if any errors encountered. */
3954 /* ----------------------------------------------------------------------- */
3956 valset_len IN NUMBER,
3957 valset_prec IN NUMBER,
3958 valset_lettersok IN VARCHAR2,
3959 stored_val IN VARCHAR2) RETURN VARCHAR2 IS
3960 d_val VARCHAR2(1000);
3961 BEGIN
3962 if(stored_to_displayed(valset_fmt, valset_len,
3963 valset_prec, valset_lettersok,
3964 stored_val, d_val) <> FF_VVALID) then
3965 d_val := stored_val;
3966 end if;
3967 return(d_val);
3968 EXCEPTION
3969 WHEN OTHERS then
3970 return(stored_val);
3971 END msg_val;
3972
3973 /* ----------------------------------------------------------------------- */
3974 /* Converts value from stored format to displayed format. */
3975 /* Returns FF_VVALID on success, or FF_VERROR otherwise. */
3976 /* ----------------------------------------------------------------------- */
3977 FUNCTION stored_to_displayed(valset_fmt IN VARCHAR2,
3978 valset_len IN NUMBER,
3979 valset_prec IN NUMBER,
3980 valset_lettersok IN VARCHAR2,
3981 stored_val IN VARCHAR2,
3982 disp_val OUT nocopy VARCHAR2) RETURN VARCHAR2 IS
3983 datebuf DATE;
3984 BEGIN
3985 if(valset_fmt in ('X', 'Y', 'Z')) then
3986 if(isa_stored_date(stored_val, valset_fmt, datebuf)) then
3987 disp_val := to_char(datebuf,displayed_date_format(valset_fmt,valset_len));
3988 else
3989 return(FF_VERROR);
3990 end if;
3991 ELSIF ((valset_fmt = 'N') OR
3992 ((valset_fmt = 'C') AND
3993 (valset_lettersok = 'N'))) THEN
3994 disp_val := REPLACE(stored_val,m_nc,m_nd);
3995 else
3996 disp_val := stored_val;
3997 end if;
3998
3999 return(FF_VVALID);
4000
4001 EXCEPTION
4002 WHEN OTHERS then
4003 return(FF_VERROR);
4004 END stored_to_displayed;
4005
4006 /* ----------------------------------------------------------------------- */
4007 /* Converts character representation of a number to a number. */
4008 /* Returns TRUE if it's a valid number, and FALSE otherwise. */
4009 /* ----------------------------------------------------------------------- */
4010 FUNCTION isa_number(teststr IN VARCHAR2,
4011 outnum OUT nocopy NUMBER) RETURN BOOLEAN IS
4012 BEGIN
4013 outnum := to_number(teststr);
4014 return(TRUE);
4015 EXCEPTION
4016 WHEN OTHERS then
4017 return(FALSE);
4018 END isa_number;
4019
4020 /* ----------------------------------------------------------------------- */
4021 /* Converts character representation of a stored date to a date. */
4022 /* Returns TRUE if it's a valid date, and FALSE otherwise. */
4023 /* ----------------------------------------------------------------------- */
4024 FUNCTION isa_stored_date(teststr IN VARCHAR2,
4025 flexfmt IN VARCHAR2,
4026 outdate OUT nocopy DATE) RETURN BOOLEAN IS
4027 BEGIN
4028 if(teststr is null) then
4029 return(TRUE);
4030 end if;
4031 return(isa_date(teststr, stored_date_format(flexfmt, LENGTH(teststr)),
4032 outdate));
4033 EXCEPTION
4034 WHEN OTHERS then
4035 IF (fnd_flex_server1.g_debug_level > 0) THEN
4036 add_debug('isa_stored_date() exception: ' || SQLERRM);
4037 END IF;
4038 return(FALSE);
4039 END isa_stored_date;
4040
4041
4042 /* ----------------------------------------------------------------------- */
4043 /* Converts character representation of a displayed date to a date. */
4044 /* Returns TRUE if it's a valid date, and FALSE otherwise. */
4045 /* ----------------------------------------------------------------------- */
4046 FUNCTION isa_displayed_date(teststr IN VARCHAR2,
4047 flexfmt IN VARCHAR2,
4048 outdate OUT nocopy DATE) RETURN BOOLEAN IS
4049 BEGIN
4050 if(teststr is null) then
4051 return(TRUE);
4052 end if;
4053 return(isa_date(teststr, displayed_date_format(flexfmt, LENGTH(teststr)),
4054 outdate));
4055 EXCEPTION
4056 WHEN OTHERS then
4057 IF (fnd_flex_server1.g_debug_level > 0) THEN
4058 add_debug('isa_displayed_date() exception: ' || SQLERRM);
4059 END IF;
4060 return(FALSE);
4061 END isa_displayed_date;
4062
4063 /* ----------------------------------------------------------------------- */
4064 /* Converts test string to date using supplied format. */
4065 /* If date conversion fails or supplied format is null, returns FALSE */
4066 /* otherwise returns TRUE. */
4067 /* This function is a wrapper around to_date() to make it always work */
4068 /* even if the supplied format is 'DD-MON-YY' (See bug) */
4069 /* ----------------------------------------------------------------------- */
4070 FUNCTION isa_date(teststr IN VARCHAR2,
4071 datefmt IN VARCHAR2,
4072 outdate OUT nocopy DATE) RETURN BOOLEAN IS
4073 BEGIN
4074 if(teststr is null) then
4078 return(FALSE);
4075 return(TRUE);
4076 end if;
4077 if(datefmt is null) then
4079 elsif(datefmt = 'DD-MON-YY') then
4080 outdate := to_date(teststr, datefmt);
4081 elsif(datefmt = 'DD-MON-RR') then
4082 outdate := to_date(teststr, datefmt);
4083 else
4084 outdate := to_date(teststr, datefmt);
4085 end if;
4086 return(TRUE);
4087 EXCEPTION
4088 WHEN OTHERS then
4089 IF (fnd_flex_server1.g_debug_level > 0) THEN
4090 add_debug('isa_date() exception: ' || SQLERRM);
4091 END IF;
4092 return(FALSE);
4093 END isa_date;
4094
4095 /* ----------------------------------------------------------------------- */
4096 /* Returns format string for converting a date to a character string */
4097 /* that represents the displayed value of a flex date, time, or */
4098 /* date-time. */
4099 /* Returns NULL if not valid flex date format. */
4100 /* ----------------------------------------------------------------------- */
4101 FUNCTION displayed_date_format(flex_data_type IN VARCHAR2,
4102 string_length IN NUMBER) RETURN VARCHAR2 IS
4103
4104 l_format_in VARCHAR2(500);
4105 l_format_out VARCHAR2(500);
4106 BEGIN
4107 IF (fnd_flex_val_util.get_display_format
4108 (p_vset_format => flex_data_type,
4109 p_max_length => string_length,
4110 p_precision => NULL,
4111 x_format_in => l_format_in,
4112 x_format_out => l_format_out)) THEN
4113 RETURN(l_format_out);
4114 ELSE
4115 RETURN(NULL);
4116 END IF;
4117
4118 EXCEPTION
4119 WHEN OTHERS then
4120 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4121 FND_MESSAGE.set_token('MSG',
4122 'displayed_date_format() exception: ' || SQLERRM);
4123 return(NULL);
4124
4125 END displayed_date_format;
4126
4127 /* ----------------------------------------------------------------------- */
4128 /* Returns format string for converting character representation */
4129 /* of stored flex date, time, or date-time data to a date datatype */
4130 /* using the to_date() conversion utility. */
4131 /* Returns NULL if not valid flex date format. */
4132 /* Modified for DD-MON-RR conversion in Prod 16 12-26-96 */
4133 /* ----------------------------------------------------------------------- */
4134 FUNCTION stored_date_format(flex_data_type IN VARCHAR2,
4135 string_length IN NUMBER) RETURN VARCHAR2 IS
4136
4137 l_format VARCHAR2(500);
4138 BEGIN
4139 IF (fnd_flex_val_util.get_storage_format(p_vset_format => flex_data_type,
4140 p_max_length => string_length,
4141 p_precision => NULL,
4142 x_format => l_format)) THEN
4143 RETURN(l_format);
4144 ELSE
4145 RETURN(NULL);
4146 END IF;
4147
4148 EXCEPTION
4149 WHEN OTHERS then
4150 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4151 FND_MESSAGE.set_token('MSG',
4152 'stored_date_format() exception: ' || SQLERRM);
4153 return(NULL);
4154
4155 END stored_date_format;
4156
4157 /* ----------------------------------------------------------------------- */
4158 /* Generally useful utilities - Externalized */
4159 /* ----------------------------------------------------------------------- */
4160
4161 /* ----------------------------------------------------------------------- */
4162 /* Initializes all global variables */
4163 /* ----------------------------------------------------------------------- */
4164 FUNCTION init_globals RETURN BOOLEAN IS
4165
4166 BEGIN
4167
4168 n_sqlstrings := 0;
4169 g_debug_array_size := 0;
4170 return(TRUE);
4171
4172 EXCEPTION
4173 WHEN OTHERS then
4174 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4175 FND_MESSAGE.set_token('MSG', 'init_globals() exception: ' || SQLERRM);
4176 return(FALSE);
4177
4178 END init_globals;
4179
4180 /* ----------------------------------------------------------------------- */
4181 /* Creates an "and clause" of a SQL select statement for determining */
4182 /* if the value passed in is in between the values in the columns */
4183 /* whose names are in mincol and maxcol. Returns NULL if no */
4184 /* statement required for this column */
4185 /* */
4186 /* By isolating all in range clauses to this routine we can ensure */
4187 /* uniform range behavior. */
4188 /* */
4189 /* Null handling: If val is NULL, requires either mincol or maxcol */
4190 /* to be null. If the value contained in mincol (maxcol) is NULL it */
4191 /* means there is no lower(upper) limit on the range of val */
4192 /* */
4193 /* Clause is of the form: */
4197 /* ----------------------------------------------------------------------- */
4194 /* 'and (10 between nvl(to_number(MINCOL), 10) */
4195 /* and nvl(to_number(MAXCOL), 10)) ' */
4196 /* */
4198 PROCEDURE x_inrange_clause(valstr IN VARCHAR2,
4199 valtype IN VARCHAR2,
4200 mincol IN VARCHAR2,
4201 maxcol IN VARCHAR2) IS
4202
4203 clause VARCHAR2(500);
4204 val VARCHAR2(200);
4205 collo VARCHAR2(200);
4206 colhi VARCHAR2(200);
4207 datefmt VARCHAR2(24);
4208
4209 BEGIN
4210 fnd_dsql.add_text('and (');
4211 IF (valstr IS NULL) THEN
4212 fnd_dsql.add_text(mincol || ' is null or ' ||
4213 maxcol || ' is null) ');
4214 ELSE
4215 IF (valtype = 'N') THEN
4216 fnd_dsql.add_bind(fnd_number.canonical_to_number(valstr));
4217
4218 fnd_dsql.add_text(' between nvl(fnd_number.canonical_to_number('
4219 || mincol || '),');
4220 fnd_dsql.add_bind(fnd_number.canonical_to_number(valstr));
4221
4222 fnd_dsql.add_text(') and nvl(fnd_number.canonical_to_number('
4223 || maxcol || '),');
4224 fnd_dsql.add_bind(fnd_number.canonical_to_number(valstr));
4225
4226 ELSIF (valtype in ('D', 'T', 't', 'I', 'X', 'Y', 'Z')) THEN
4227 datefmt := stored_date_format(valtype, LENGTH(valstr));
4228 fnd_dsql.add_bind(To_date(valstr, datefmt));
4229
4230 fnd_dsql.add_text(' between nvl(to_date(' ||
4231 mincol || ',''' || datefmt || '''),');
4232 fnd_dsql.add_bind(To_date(valstr, datefmt));
4233
4234 fnd_dsql.add_text(') and nvl(to_date(' ||
4235 maxcol || ',''' || datefmt || '''),');
4236 fnd_dsql.add_bind(To_date(valstr, datefmt));
4237
4238 ELSE
4239 fnd_dsql.add_bind(valstr);
4240
4241 fnd_dsql.add_text(' between nvl(' || mincol || ',');
4242 fnd_dsql.add_bind(valstr);
4243
4244 fnd_dsql.add_text(') and nvl(' || maxcol || ',');
4245 fnd_dsql.add_bind(valstr);
4246
4247 END IF;
4248 fnd_dsql.add_text(')) ');
4249 END IF;
4250 END x_inrange_clause;
4251
4252 /* ----------------------------------------------------------------------- */
4253 /* Function to convert a column name into a SQL clause for selecting */
4254 /* a value, id, or description from that column into the correct */
4255 /* character format for the given value set. */
4256 /* */
4257 /* This function does the default to_char() conversion for */
4258 /* non-translatable date, time, date-time, or number value sets */
4259 /* in order to maintain backward compatibility with old client code. */
4260 /* For translatable date, time, and date-time value sets this code */
4261 /* converts the data stored in the date column to date storage format */
4262 /* */
4263 /* Does not check for compatibility of column type and value set. */
4264 /* ----------------------------------------------------------------------- */
4265
4266 FUNCTION select_clause(colname IN VARCHAR2,
4267 coltype IN VARCHAR2,
4268 v_component IN BINARY_INTEGER,
4269 vs_fmt IN VARCHAR2,
4270 vs_len IN NUMBER) RETURN VARCHAR2 IS
4271
4272 clause VARCHAR2(2000);
4273
4274 BEGIN
4275
4276 if(coltype not in ('C', 'V')) then
4277 clause := 'to_char(' || colname;
4278 if(vs_fmt in ('X', 'Y', 'Z')) then
4279 clause := clause || ', ''';
4280 if((v_component = VC_ID) OR (v_component = VC_VALUE)) then
4281 clause := clause || stored_date_format(vs_fmt, vs_len) || ''')';
4282 else
4283 clause := clause || displayed_date_format(vs_fmt, vs_len) || ''')';
4284 end if;
4285 ELSIF (vs_fmt = 'N') THEN
4286 clause := clause || ')';
4287 clause := 'replace(' || clause || ',''' || m_nb || ''','''
4288 || m_nc || ''')';
4289 else
4290 clause := clause || ')';
4291 end if;
4292 else
4293 clause := colname;
4294 end if;
4295 return(clause);
4296
4297 END select_clause;
4298
4299 /* ----------------------------------------------------------------------- */
4300 /* Function to convert a value into a SQL clause for comparing that */
4301 /* value into a table column of the given type, or for inserting that */
4302 /* value to a value stored in a table column of the given type. */
4303 /* */
4304 /* If the table column is a number assume the value input is a number */
4305 /* and just use the text of the number. If the table column is */
4306 /* CHAR or VARCHAR2, then substitute all single quotes in the value */
4307 /* with double quotes and surround the value with single quotes. */
4308 /* If table column is DATE, do to_date() conversion using the format */
4309 /* appropriate for the value set. */
4310 /* */
4314 /* conversions are done. This means non-validated Date-time values */
4311 /* To maintin backward compatibility with existing client code must */
4312 /* build in the BUG that if value set format type is old-fashioned */
4313 /* date, time or date-time (D, T or t), then default to_date() */
4315 /* cannot be inserted into the combinations table. */
4316 /* ----------------------------------------------------------------------- */
4317
4318 PROCEDURE x_compare_clause(coltype IN VARCHAR2,
4319 colname IN VARCHAR2,
4320 char_val IN VARCHAR2,
4321 v_component IN BINARY_INTEGER,
4322 vs_fmt IN VARCHAR2,
4323 vs_len IN NUMBER)
4324 IS
4325 datefmt VARCHAR2(30);
4326 BEGIN
4327 IF (coltype = 'N') THEN
4328 fnd_dsql.add_bind(fnd_number.canonical_to_number(char_val));
4329 ELSIF (coltype = 'D') then
4330 IF (vs_fmt in ('X', 'Y', 'Z', 'D', 'T', 'I', 't')) then
4331 datefmt := stored_date_format(vs_fmt, vs_len);
4332 fnd_dsql.add_bind(To_date(char_val, datefmt));
4333 ELSE
4334 fnd_dsql.add_bind(char_val);
4335 end if;
4336 else
4337 fnd_dsql.add_bind(char_val);
4338 end if;
4339
4340 END x_compare_clause;
4341
4342 /* ----------------------------------------------------------------------- */
4343 /* Creates a SQL clause for binding a character string. */
4344 /* Replaces single quotes with double quotes and surrounds the */
4345 /* string with quotes. */
4346 /* ----------------------------------------------------------------------- */
4347
4348 FUNCTION string_clause(char_string IN VARCHAR2) RETURN VARCHAR2 IS
4349 BEGIN
4350 return('''' || REPLACE(char_string, '''', '''''') || '''');
4351 END string_clause;
4352
4353 -- /* ----------------------------------------------------------------------- */
4354 -- /* MADE NULL TO MAKE MORE ROOM FOR USEFUL CODE. */
4355 -- /* Breaks concatenated segments in rule lines table into separate */
4356 -- /* columns for each segment. Returns number of segments or < 0 */
4357 -- /* and sets FND_MESSAGE if error. This is called only from trigger */
4358 -- /* FND_FLEX_VALIDATION_RULE_LINES_T1. The trigger should use */
4359 -- /* FND_MESSAGE.raise_exception if this function returns error. */
4360 -- /* ----------------------------------------------------------------------- */
4361 -- FUNCTION breakup_segs(appid IN NUMBER,
4362 -- flex_code IN VARCHAR2, flex_num IN NUMBER,
4363 -- catsegs IN VARCHAR2, nsegs OUT NUMBER,
4364 -- seg1 OUT VARCHAR2, seg2 OUT VARCHAR2,
4365 -- seg3 OUT VARCHAR2, seg4 OUT VARCHAR2,
4366 -- seg5 OUT VARCHAR2, seg6 OUT VARCHAR2,
4367 -- seg7 OUT VARCHAR2, seg8 OUT VARCHAR2,
4368 -- seg9 OUT VARCHAR2, seg10 OUT VARCHAR2,
4369 -- seg11 OUT VARCHAR2, seg12 OUT VARCHAR2,
4370 -- seg13 OUT VARCHAR2, seg14 OUT VARCHAR2,
4371 -- seg15 OUT VARCHAR2, seg16 OUT VARCHAR2,
4372 -- seg17 OUT VARCHAR2, seg18 OUT VARCHAR2,
4373 -- seg19 OUT VARCHAR2, seg20 OUT VARCHAR2,
4374 -- seg21 OUT VARCHAR2, seg22 OUT VARCHAR2,
4375 -- seg23 OUT VARCHAR2, seg24 OUT VARCHAR2,
4376 -- seg25 OUT VARCHAR2, seg26 OUT VARCHAR2,
4377 -- seg27 OUT VARCHAR2, seg28 OUT VARCHAR2,
4378 -- seg29 OUT VARCHAR2, seg30 OUT VARCHAR2) RETURN NUMBER IS
4379 --
4380 -- n_segs NUMBER;
4381 -- sg StringArray;
4382 -- sepchar VARCHAR2(1);
4383 --
4384 -- BEGIN
4385 -- return(-1);
4386 --
4387 -- EXCEPTION
4388 -- WHEN OTHERS then
4389 -- FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4390 -- FND_MESSAGE.set_token('MSG', 'breakup_segs() exception: ' || SQLERRM);
4391 -- return(-2);
4392 --
4393 -- END breakup_segs;
4394
4395 /* ----------------------------------------------------------------------- */
4396 /* Executes a dynamic SQL statement. */
4397 /* Returns number of rows processed or -1 if error (add_message) */
4398 /* ----------------------------------------------------------------------- */
4399 FUNCTION x_dsql_execute RETURN NUMBER IS
4400
4401 cursornum INTEGER;
4402 nprocessed INTEGER;
4403 sql_statement VARCHAR2(32000);
4404
4405 BEGIN
4406
4407 -- Copy SQL string to array for debugging purposes
4408 --
4409 add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
4410
4411 cursornum := dbms_sql.open_cursor;
4412 fnd_dsql.set_cursor(cursornum);
4413
4414 sql_statement := fnd_dsql.get_text(p_with_debug => FALSE);
4415 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
4416
4417 fnd_dsql.do_binds;
4418
4419 nprocessed := dbms_sql.execute(cursornum);
4420 IF (fnd_flex_server1.g_debug_level > 0) THEN
4421 add_debug('(DSQL_execute processed ' || to_char(nprocessed));
4422 add_debug(' rows.)');
4423 END IF;
4424 dbms_sql.close_cursor(cursornum);
4425 return(nprocessed);
4426
4427 EXCEPTION
4428 WHEN OTHERS then
4432 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
4429 if(dbms_sql.is_open(cursornum)) then
4430 dbms_sql.close_cursor(cursornum);
4431 end if;
4433 FND_MESSAGE.set_token('MSG', SQLERRM);
4434 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_statement, 1, 1000));
4435 return(-1);
4436
4437 END x_dsql_execute;
4438
4439 /* ----------------------------------------------------------------------- */
4440 /* Uses dynamic SQL to select up to one varchar2 valued column from */
4441 /* a table using the select statement passed in. Returns 0, NULL */
4442 /* if no rows found, or 1 and the column value if 1 row found, or */
4443 /* 2 and the column value of the first row found if more than 1 row */
4444 /* matches selection criteria, or < 0 if other errors. */
4445 /* Invalid rowid exception mapped back to no data found. */
4446 /* ----------------------------------------------------------------------- */
4447 FUNCTION x_dsql_select_one(returned_column OUT nocopy VARCHAR2) RETURN NUMBER IS
4448 cursornum INTEGER;
4449 num_returned INTEGER;
4450 selected_col VARCHAR2(2000);
4451 invalid_rowid EXCEPTION;
4452 sql_statement VARCHAR2(32000);
4453
4454 PRAGMA EXCEPTION_INIT(invalid_rowid, -1410);
4455
4456 BEGIN
4457
4458 -- Copy SQL string to array for debugging purposes
4459 --
4460 add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
4461
4462 selected_col := NULL;
4463 cursornum := dbms_sql.open_cursor;
4464 fnd_dsql.set_cursor(cursornum);
4465
4466 sql_statement := fnd_dsql.get_text(p_with_debug => FALSE);
4467 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
4468
4469 fnd_dsql.do_binds;
4470
4471 dbms_sql.define_column(cursornum, 1, selected_col, 2000);
4472 num_returned := dbms_sql.execute_and_fetch(cursornum, TRUE);
4473 if(num_returned = 1) then
4474 dbms_sql.column_value(cursornum, 1, selected_col);
4475 returned_column := selected_col;
4476 IF (fnd_flex_server1.g_debug_level > 0) THEN
4477 add_debug('(DSQL returned ' || selected_col || ')');
4478 END IF;
4479 else
4480 num_returned := -1;
4481 returned_column := NULL;
4482 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4483 FND_MESSAGE.set_token('MSG', 'x_dsql_select_one() could not fetch rows');
4484 end if;
4485 dbms_sql.close_cursor(cursornum);
4486 return(num_returned);
4487
4488 EXCEPTION
4489 WHEN NO_DATA_FOUND or invalid_rowid then
4490 returned_column := NULL;
4491 IF (fnd_flex_server1.g_debug_level > 0) THEN
4492 add_debug('(DSQL returned: NULL)');
4493 END IF;
4494 dbms_sql.close_cursor(cursornum);
4495 return(0);
4496 WHEN TOO_MANY_ROWS then
4497 dbms_sql.column_value(cursornum, 1, selected_col);
4498 returned_column := selected_col;
4499 IF (fnd_flex_server1.g_debug_level > 0) THEN
4500 add_debug('(DSQL returned: TOO MANY ROWS)');
4501 END IF;
4502 dbms_sql.close_cursor(cursornum);
4503 return(2);
4504 WHEN OTHERS then
4505 if(dbms_sql.is_open(cursornum)) then
4506 dbms_sql.close_cursor(cursornum);
4507 end if;
4508 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
4509 FND_MESSAGE.set_token('MSG', SQLERRM);
4510 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_statement, 1, 1000));
4511 return(-2);
4512
4513 END x_dsql_select_one;
4514
4515 /* ----------------------------------------------------------------------- */
4516 /* Uses dynamic SQL to select n_selected_cols of VARCHAR2 type from */
4517 /* a table using the select statement passed in. Returns number */
4518 /* of rows found, or sets error and returns < 0 if error. */
4519 /* Invalid rowid exception mapped back to no data found. */
4520 /* ----------------------------------------------------------------------- */
4521 FUNCTION x_dsql_select(n_selected_cols IN NUMBER,
4522 returned_columns OUT nocopy StringArray) RETURN NUMBER
4523 IS
4524 cursornum INTEGER;
4525 num_returned INTEGER;
4526 selected_cols StringArray;
4527 invalid_rowid EXCEPTION;
4528 sql_statement VARCHAR2(32000);
4529
4530 PRAGMA EXCEPTION_INIT(invalid_rowid, -1410);
4531 BEGIN
4532
4533 -- Copy SQL string to array for debugging purposes
4534 --
4535 add_sql_string(fnd_dsql.get_text(p_with_debug => TRUE));
4536
4537 cursornum := dbms_sql.open_cursor;
4538 fnd_dsql.set_cursor(cursornum);
4539
4540 sql_statement := fnd_dsql.get_text(p_with_debug => FALSE);
4541 dbms_sql.parse(cursornum, sql_statement, dbms_sql.v7);
4542
4543 fnd_dsql.do_binds;
4544
4545 for i in 1..n_selected_cols loop
4546 -- The following prevents NO-DATA-FOUND exception...
4547 selected_cols(i) := NULL;
4548 dbms_sql.define_column(cursornum, i, selected_cols(i), 2000);
4549 end loop;
4550 num_returned := dbms_sql.execute_and_fetch(cursornum, TRUE);
4551 if(num_returned = 1) then
4552 IF (fnd_flex_server1.g_debug_level > 0) THEN
4553 add_debug('(DSQL returned');
4554 END IF;
4555 for i in 1..n_selected_cols loop
4556 dbms_sql.column_value(cursornum, i, selected_cols(i));
4557 IF (fnd_flex_server1.g_debug_level > 0) THEN
4561 IF (fnd_flex_server1.g_debug_level > 0) THEN
4558 add_debug(' ''' || selected_cols(i) || '''');
4559 END IF;
4560 end loop;
4562 add_debug(')');
4563 END IF;
4564 else
4565 num_returned := -1;
4566 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4567 FND_MESSAGE.set_token('MSG', 'x_dsql_select() could not fetch rows');
4568 end if;
4569 dbms_sql.close_cursor(cursornum);
4570 returned_columns := selected_cols;
4571 return(num_returned);
4572
4573 EXCEPTION
4574 WHEN NO_DATA_FOUND OR invalid_rowid then
4575 IF (fnd_flex_server1.g_debug_level > 0) THEN
4576 add_debug('(DSQL returned: NULL)');
4577 END IF;
4578 dbms_sql.close_cursor(cursornum);
4579 return(0);
4580 WHEN TOO_MANY_ROWS then
4581 IF (fnd_flex_server1.g_debug_level > 0) THEN
4582 add_debug('(DSQL returned');
4583 END IF;
4584 for i in 1..n_selected_cols loop
4585 dbms_sql.column_value(cursornum, i, selected_cols(i));
4586 IF (fnd_flex_server1.g_debug_level > 0) THEN
4587 add_debug(' ''' || selected_cols(i) || '''');
4588 END IF;
4589 end loop;
4590 IF (fnd_flex_server1.g_debug_level > 0) THEN
4591 add_debug(')');
4592 add_debug('(DSQL returned: TOO MANY ROWS)');
4593 END IF;
4594 dbms_sql.close_cursor(cursornum);
4595 returned_columns := selected_cols;
4596 return(2);
4597 WHEN OTHERS then
4598 if(dbms_sql.is_open(cursornum)) then
4599 dbms_sql.close_cursor(cursornum);
4600 end if;
4601 FND_MESSAGE.set_name('FND', 'FLEX-DSQL EXCEPTION');
4602 FND_MESSAGE.set_token('MSG', SQLERRM);
4603 FND_MESSAGE.set_token('SQLSTR', SUBSTRB(sql_statement, 1, 1000));
4604 return(-2);
4605
4606 END x_dsql_select;
4607
4608 /* ----------------------------------------------------------------------- */
4609 /* Adds string to array of dynamic sql strings. */
4610 /* The buffer is zeroed when init_globals is called. */
4611 /* ----------------------------------------------------------------------- */
4612 PROCEDURE add_sql_string(sql_statement IN VARCHAR2) IS
4613 BEGIN
4614 n_sqlstrings := n_sqlstrings + 1;
4615 sqlstrings(n_sqlstrings) := sql_statement;
4616 END add_sql_string;
4617
4618 /* ----------------------------------------------------------------------- */
4619 /* For debugging. */
4620 /* Returns number of SQL statements created during last call */
4621 /* to validate(). Use in conjunction with get_sql(). */
4622 /* ----------------------------------------------------------------------- */
4623 FUNCTION get_nsql_internal RETURN NUMBER IS
4624 BEGIN
4625 return(nvl(to_number(n_sqlstrings), 0));
4626 END get_nsql_internal;
4627
4628 /* ----------------------------------------------------------------------- */
4629 /* For debugging. */
4630 /* Returns SQL statements created during last call */
4631 /* to validate(). Use in conjunction with get_sql(). */
4632 /* ----------------------------------------------------------------------- */
4633 FUNCTION get_sql_internal(statement_number IN NUMBER,
4634 statement_portion IN NUMBER DEFAULT 1) RETURN VARCHAR2 IS
4635
4636 dsql_start NUMBER;
4637
4638 BEGIN
4639
4640 if((statement_number is null) or (statement_number < 1) or
4641 (statement_number > nvl(to_number(n_sqlstrings), 0)) or
4642 (statement_portion is null) or (statement_portion < 1) or
4643 (sqlstrings(statement_number) is null)) then
4644 return(NULL);
4645 end if;
4646
4647 dsql_start := 1 + ((statement_portion - 1) * MAX_RETSTR_LEN);
4648 if(dsql_start > LENGTH(sqlstrings(statement_number))) then
4649 return(NULL);
4650 end if;
4651
4652 return(SUBSTR(sqlstrings(statement_number), dsql_start, MAX_RETSTR_LEN));
4653
4654 EXCEPTION
4655 WHEN NO_DATA_FOUND then
4656 return('get_sql_internal('||to_char(statement_number)||') statement not found');
4657 WHEN OTHERS then
4658 return('get_sql_internal() exception: ' || SQLERRM);
4659
4660 END get_sql_internal;
4661
4662 /* ----------------------------------------------------------------------- */
4663 /* Adds to debug string */
4664 /* ----------------------------------------------------------------------- */
4665 PROCEDURE set_debugging(p_debug_mode IN VARCHAR2)
4666 IS
4667 BEGIN
4668 IF (p_debug_mode = 'OFF') THEN
4669 fnd_flex_server1.g_debug_level := 0;
4670 ELSIF (p_debug_mode = 'ERROR') THEN
4671 fnd_flex_server1.g_debug_level := 1;
4672 ELSIF (p_debug_mode = 'EXCEPTION') THEN
4673 fnd_flex_server1.g_debug_level := 2;
4674 ELSIF (p_debug_mode = 'EVENT') THEN
4675 fnd_flex_server1.g_debug_level := 3;
4676 ELSIF (p_debug_mode = 'PROCEDURE') THEN
4677 fnd_flex_server1.g_debug_level := 4;
4678 ELSIF (p_debug_mode = 'STATEMENT') THEN
4679 fnd_flex_server1.g_debug_level := 5;
4680 ELSIF (p_debug_mode = 'ALL') THEN
4681 fnd_flex_server1.g_debug_level := 6;
4682 fnd_flex_val_util.set_debugging(TRUE);
4683 ELSE
4684 fnd_flex_server1.g_debug_level := 0;
4685 END IF;
4689 END set_debugging;
4686 EXCEPTION
4687 WHEN OTHERS THEN
4688 fnd_flex_server1.g_debug_level := 0;
4690
4691 /* ----------------------------------------------------------------------- */
4692 /* Adds to debug string */
4693 /* ----------------------------------------------------------------------- */
4694 PROCEDURE add_debug(p_debug_string IN VARCHAR2,
4695 p_debug_mode IN VARCHAR2 DEFAULT 'STATEMENT')
4696 IS
4697 l_code_level NUMBER;
4698 l_debug_string VARCHAR2(32000);
4699 l_newline_pos NUMBER;
4700 l_debug_line VARCHAR2(32000);
4701 BEGIN
4702 IF (fnd_flex_server1.g_debug_level = 0) THEN
4703 RETURN;
4704 END IF;
4705 IF (p_debug_mode = 'OFF') THEN
4706 l_code_level := 0;
4707 ELSIF (p_debug_mode = 'ERROR') THEN
4708 l_code_level := 1;
4709 ELSIF (p_debug_mode = 'EXCEPTION') THEN
4710 l_code_level := 2;
4711 ELSIF (p_debug_mode = 'EVENT') THEN
4712 l_code_level := 3;
4713 ELSIF (p_debug_mode = 'PROCEDURE') THEN
4714 l_code_level := 4;
4715 ELSIF (p_debug_mode = 'STATEMENT') THEN
4716 l_code_level := 5;
4717 ELSIF (p_debug_mode = 'ALL') THEN
4718 l_code_level := 6;
4719 ELSE
4720 l_code_level := 0;
4721 END IF;
4722
4723 IF (l_code_level <= fnd_flex_server1.g_debug_level) THEN
4724 IF (g_debug_array_size = 0) THEN
4725 g_debug_array_size := 1;
4726 g_debug_array(g_debug_array_size) :=
4727 ('DEBUG LEVEL:'||To_char(fnd_flex_server1.g_debug_level) ||
4728 chr_newline);
4729 END IF;
4730
4731 l_debug_string := (Substr(Rtrim(Ltrim(p_debug_string)), 1, MAX_RETSTR_LEN-10) ||
4732 chr_newline);
4733
4734 WHILE (l_debug_string IS NOT NULL) LOOP
4735 l_newline_pos := Instr(l_debug_string, chr_newline, 1, 1);
4736 IF (l_newline_pos +
4737 Nvl(Length(g_debug_array(g_debug_array_size)), 0) < MAX_RETSTR_LEN) THEN
4738 l_debug_line := Substr(l_debug_string, 1, l_newline_pos);
4739 l_debug_string := Substr(l_debug_string, l_newline_pos + 1);
4740 IF (l_debug_line LIKE 'BEGIN %' OR
4741 l_debug_line LIKE 'END %' OR
4742 l_debug_line LIKE 'CALL %' OR
4743 l_debug_line LIKE 'EXCEPTION %') THEN
4744 g_debug_array(g_debug_array_size) :=
4745 g_debug_array(g_debug_array_size) || l_debug_line;
4746 ELSE
4747 g_debug_array(g_debug_array_size) :=
4748 g_debug_array(g_debug_array_size) || ' ' || l_debug_line;
4749 END IF;
4750 ELSE
4751 g_debug_array_size := g_debug_array_size + 1;
4752 g_debug_array(g_debug_array_size) := NULL;
4753 END IF;
4754 END LOOP;
4755 END IF;
4756 EXCEPTION
4757 WHEN OTHERS THEN
4758 NULL;
4759 END add_debug;
4760
4761 /* ----------------------------------------------------------------------- */
4762 /* Returns the debug string. */
4763 /* ----------------------------------------------------------------------- */
4764 FUNCTION get_debug_internal(string_n IN NUMBER) RETURN VARCHAR2 IS
4765 BEGIN
4766 IF ((string_n is not null) and
4767 (string_n >= 1) AND
4768 (string_n) <= g_debug_array_size) THEN
4769 RETURN(g_debug_array(string_n));
4770 end if;
4771 return(NULL);
4772
4773 EXCEPTION
4774 WHEN OTHERS then
4775 return('get_debug_internal() exception: ' || SQLERRM);
4776 END get_debug_internal;
4777
4778 /* -------------------------------------------------- */
4779 /* New client side debug functions */
4780 /* -------------------------------------------------- */
4781 PROCEDURE x_get_nsql(x_nsql OUT nocopy NUMBER)
4782 IS
4783 BEGIN
4784 x_nsql := fnd_flex_server1.get_nsql_internal;
4785 EXCEPTION
4786 WHEN OTHERS THEN
4787 x_nsql := 0;
4788 END;
4789
4790 PROCEDURE x_get_sql_npiece(p_sql_num IN NUMBER,
4791 x_npiece OUT nocopy NUMBER)
4792 IS
4793 l_sql_num NUMBER;
4794 BEGIN
4795 l_sql_num := Nvl(p_sql_num, 0);
4796 x_npiece := 0;
4797 IF ((l_sql_num > 0) AND
4798 (l_sql_num <= fnd_flex_server1.get_nsql_internal)) THEN
4799 x_npiece :=Ceil(Nvl(Lengthb(sqlstrings(l_sql_num)),0)/MAX_RETSTR_LEN);
4800 END IF;
4801 EXCEPTION
4802 WHEN OTHERS THEN
4803 x_npiece := 0;
4804 END;
4805
4806 PROCEDURE x_get_sql_piece(p_sql_num IN NUMBER,
4807 p_piece_num IN NUMBER,
4808 x_sql_piece OUT nocopy VARCHAR2)
4809 IS
4810 l_sql_num NUMBER;
4811 l_piece_num NUMBER;
4812 BEGIN
4813 l_sql_num := Nvl(p_sql_num, 0);
4814 l_piece_num := Nvl(p_piece_num, 0);
4815 x_sql_piece := NULL;
4816 IF ((l_sql_num > 0) AND
4817 (l_sql_num <= fnd_flex_server1.get_nsql_internal) AND
4818 (l_piece_num > 0)) THEN
4819 x_sql_piece := substrb(sqlstrings(l_sql_num),
4820 1 + (l_piece_num - 1) * MAX_RETSTR_LEN,
4821 MAX_RETSTR_LEN);
4822 END IF;
4823 EXCEPTION
4824 WHEN OTHERS THEN
4828 PROCEDURE x_get_ndebug(x_ndebug OUT nocopy NUMBER)
4825 x_sql_piece := substrb('EXCEPTION:' || Sqlerrm,1,MAX_RETSTR_LEN);
4826 END;
4827
4829 IS
4830 BEGIN
4831 x_ndebug := g_debug_array_size;
4832 EXCEPTION
4833 WHEN OTHERS THEN
4834 x_ndebug := 0;
4835 END;
4836
4837 PROCEDURE x_get_debug(p_debug_num IN NUMBER,
4838 x_debug OUT nocopy VARCHAR2)
4839 IS
4840 l_debug_num NUMBER;
4841 BEGIN
4842 l_debug_num := Nvl(p_debug_num, 0);
4843 IF (l_debug_num >= 1 AND
4844 l_debug_num <= g_debug_array_size) THEN
4845 x_debug := g_debug_array(l_debug_num);
4846 ELSE
4847 x_debug := 'INDEX OUT OF RANGE [1..' || g_debug_array_size || '].';
4848 END IF;
4849 EXCEPTION
4850 WHEN OTHERS THEN
4851 x_debug := substrb('EXCEPTION:' || Sqlerrm,1,MAX_RETSTR_LEN);
4852 END;
4853
4854 /* ----------------------------------------------------------------------- */
4855 /* Converts concatenated segments to segment array */
4856 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
4857 /* ----------------------------------------------------------------------- */
4858 FUNCTION to_stringarray(catsegs IN VARCHAR2,
4859 sepchar in VARCHAR2,
4860 segs OUT nocopy StringArray)
4861 RETURN NUMBER
4862 IS
4863 l_wc VARCHAR2(10);
4864 l_flex_value VARCHAR2(2000);
4865 i NUMBER;
4866 l_segnum PLS_INTEGER;
4867 l_delimiter VARCHAR2(10);
4868 l_tmp_str VARCHAR2(32000);
4869 l_delim_pos PLS_INTEGER;
4870 l_old_delim_pos PLS_INTEGER;
4871 BEGIN
4872 l_delimiter := Substr(sepchar, 1, 1);
4873
4874 --
4875 -- Make sure delimiter is valid.
4876 --
4877 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
4878 raise_application_error(-20001,
4879 'SV2.to_stringarray. Invalid delimiter:''' ||
4880 Nvl(sepchar, '<NULL>') || '''');
4881 END IF;
4882
4883 --
4884 -- If catsegs is NULL then assume there is only one segment.
4885 --
4886 IF (catsegs IS NULL) THEN
4887 l_segnum := 1;
4888 segs(1) := catsegs;
4889 GOTO return_success;
4890 END IF;
4891
4892 l_segnum := 0;
4893 i := 1;
4894
4895 -- We need to go through un-escaping logic only if
4896 -- there is an ESCAPE character in the string.
4897 -- Bug 4501279.
4898
4899 IF (instr(catsegs, FLEX_DELIMITER_ESCAPE) > 0) THEN
4900
4901 --
4902 -- Loop for each segment.
4903 --
4904 LOOP
4905 l_flex_value := NULL;
4906
4907 --
4908 -- Un-escaping loop.
4909 --
4910 LOOP
4911
4912 l_wc := Substr(catsegs, i, 1);
4913 i := i + 1;
4914
4915 IF (l_wc IS NULL) THEN
4916 EXIT;
4917 ELSIF (l_wc = l_delimiter) THEN
4918 EXIT;
4919 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
4920
4921 l_wc := Substr(catsegs, i, 1);
4922 i := i + 1;
4923
4924 IF (l_wc IS NULL) THEN
4925 EXIT;
4926 END IF;
4927
4928 END IF;
4929
4930 l_flex_value := l_flex_value || l_wc;
4931
4932 END LOOP;
4933
4934 l_segnum := l_segnum + 1;
4935 segs(l_segnum) := l_flex_value;
4936 IF (l_wc IS NULL) THEN
4937 EXIT;
4938 END IF;
4939 END LOOP;
4940
4941 ELSE
4942
4943 -- No un-escaping logic required here.
4944
4945 l_tmp_str := catsegs;
4946 l_delim_pos := 0;
4947 l_old_delim_pos := 0;
4948
4949 LOOP
4950
4951 l_delim_pos := instr(l_tmp_str, l_delimiter, l_delim_pos+1);
4952
4953 IF (l_delim_pos <> 0) THEN
4954 l_segnum := l_segnum + 1;
4955 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1, l_delim_pos-l_old_delim_pos-1);
4956 l_old_delim_pos := l_delim_pos;
4957 ELSE
4958 l_segnum := l_segnum + 1;
4959 segs(l_segnum) := substr(l_tmp_str, l_old_delim_pos+1);
4960 EXIT;
4961 END IF;
4962
4963 END LOOP;
4964
4965 END IF;
4966
4967 <<return_success>>
4968 RETURN(l_segnum);
4969
4970 EXCEPTION
4971 WHEN OTHERS THEN
4972 raise_application_error(-20001, 'SV2.to_stringarray. SQLERRM : ' ||
4973 Sqlerrm);
4974 END to_stringarray;
4975
4976 /* ----------------------------------------------------------------------- */
4977 /* Converts segment array to concatenated segments */
4978 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
4979 /* ----------------------------------------------------------------------- */
4980 FUNCTION from_stringarray(nsegs IN NUMBER,
4981 segs IN StringArray,
4982 sepchar IN VARCHAR2) RETURN VARCHAR2
4983 IS
4984 l_wc VARCHAR2(10);
4985 l_return VARCHAR2(32000) := NULL;
4986 i pls_integer;
4990 l_delimiter := Substr(sepchar, 1, 1);
4987 l_segnum pls_integer;
4988 l_delimiter VARCHAR2(10);
4989 BEGIN
4991 IF (fnd_flex_server1.g_debug_level > 0) THEN
4992 fnd_flex_server1.add_debug('BEGIN SV1.from_stringarray()');
4993 END IF;
4994 --
4995 -- Make sure delimiter is valid.
4996 --
4997 IF ((l_delimiter IS NULL) OR (l_delimiter = FLEX_DELIMITER_ESCAPE)) THEN
4998 raise_application_error(-20001,
4999 'SV1.from_stringarray. Invalid delimiter:''' ||
5000 Nvl(sepchar, '<NULL>') || '''');
5001 END IF;
5002
5003 --
5004 -- Make sure array size is valid.
5005 --
5006 IF ((nsegs IS NULL) OR (nsegs < 1)) THEN
5007 raise_application_error(-20001,
5008 'SV1.from_stringarray. For specified context there are ''' ||
5009 Nvl(to_char(nsegs), '<NULL>') || '''' || ' displayed segments');
5010 END IF;
5011
5012 --
5013 -- If only one segment then no need for concatenating or escaping.
5014 --
5015 IF (nsegs = 1) THEN
5016 l_return := segs(1);
5017 GOTO return_success;
5018 END IF;
5019
5020 --
5021 -- Loop for each segment
5022 --
5023 FOR l_segnum IN 1..nsegs LOOP
5024
5025 i := 1;
5026
5027 --
5028 -- Escaping loop.
5029 --
5030 LOOP
5031
5032 l_wc := Substr(segs(l_segnum), i, 1);
5033 i := i + 1;
5034
5035 IF (l_wc IS NULL) THEN
5036 EXIT;
5037 ELSIF (l_wc = FLEX_DELIMITER_ESCAPE) THEN
5038 l_return := l_return || FLEX_DELIMITER_ESCAPE;
5039 l_return := l_return || FLEX_DELIMITER_ESCAPE;
5040 ELSIF (l_wc = l_delimiter) THEN
5041 l_return := l_return || FLEX_DELIMITER_ESCAPE;
5042 l_return := l_return || l_delimiter;
5043 ELSE
5044 l_return := l_return || l_wc;
5045 END IF;
5046
5047 END LOOP;
5048
5049 --
5050 -- No delimiter after the last value.
5051 --
5052 IF (l_segnum < nsegs) THEN
5053 l_return := l_return || l_delimiter;
5054 END IF;
5055 END LOOP;
5056
5057 <<return_success>>
5058 IF (fnd_flex_server1.g_debug_level > 0) THEN
5059 fnd_flex_server1.add_debug('END SV1.from_stringarray()');
5060 END IF;
5061 RETURN(l_return);
5062 EXCEPTION
5063 WHEN OTHERS THEN
5064 IF (fnd_flex_server1.g_debug_level > 0) THEN
5065 fnd_flex_server1.add_debug('EXCEPTION SV1.from_stringarray()');
5066 END IF;
5067 raise_application_error(-20001, 'SV1.from_stringarray. SQLERRM : ' || Sqlerrm);
5068 END from_stringarray;
5069
5070 /* ----------------------------------------------------------------------- */
5071 /* Converts concatenated segments to segment array */
5072 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
5073 /* Bug 1375146 added elsif statement to allow for only 1 segment */
5074 /* which is null. */
5075 /* ----------------------------------------------------------------------- */
5076 FUNCTION to_stringarray2(catsegs IN VARCHAR2,
5077 sepchar in VARCHAR2,
5078 segs OUT nocopy StringArray)
5079 RETURN NUMBER
5080 IS
5081 seg_start NUMBER;
5082 seg_end NUMBER;
5083 seg_len NUMBER;
5084 catseg_len NUMBER;
5085 sepchar_len NUMBER;
5086 seg_index BINARY_INTEGER;
5087 keep_going BOOLEAN;
5088 BEGIN
5089 seg_index := 1;
5090 seg_start := 1;
5091 keep_going := TRUE;
5092 IF ((catsegs IS NOT NULL) AND (sepchar IS NOT NULL)) THEN
5093 catseg_len := LENGTH(catsegs);
5094 sepchar_len := LENGTH(sepchar);
5095 WHILE (keep_going = TRUE) LOOP
5096 IF (seg_start > catseg_len) THEN
5097 segs(seg_index) := NULL;
5098 keep_going := FALSE;
5099 ELSE
5100 seg_end := INSTR(catsegs, sepchar, seg_start);
5101 IF (seg_end = 0) THEN
5102 seg_end := catseg_len + 1;
5103 keep_going := FALSE;
5104 END IF;
5105 seg_len := seg_end - seg_start;
5106 IF (seg_len = 0) THEN
5107 segs(seg_index) := NULL;
5108 ELSE
5109 segs(seg_index) := REPLACE(SUBSTR(catsegs, seg_start, seg_len),
5110 NEWLINE, sepchar);
5111 END IF;
5112 END IF;
5113 seg_index := seg_index + 1;
5114 seg_start := seg_end + sepchar_len;
5115 END LOOP;
5116 ELSIF ((catsegs IS NULL) AND (sepchar IS NULL)) THEN
5117 seg_index :=1;
5118 ELSIF ((catsegs IS NULL) AND (sepchar IS NOT NULL)) THEN
5119 segs(1) := NULL;
5120 seg_index := 2;
5121 ELSIF ((catsegs IS NOT NULL) AND (sepchar IS NULL)) THEN
5122 seg_index := 1;
5123 END IF;
5124 RETURN(To_number(seg_index - 1));
5125 END to_stringarray2;
5126
5127 /* ----------------------------------------------------------------------- */
5128 /* Converts segment array to concatenated segments */
5132 segs IN StringArray,
5129 /* Segment array is 1-based containing entries for 1 <= i <= nsegs */
5130 /* ----------------------------------------------------------------------- */
5131 FUNCTION from_stringarray2(nsegs IN NUMBER,
5133 sepchar IN VARCHAR2) RETURN VARCHAR2
5134 IS
5135 l_return VARCHAR2(2000) := NULL;
5136 BEGIN
5137 IF (fnd_flex_server1.g_debug_level > 0) THEN
5138 fnd_flex_server1.add_debug('CALL SV1.from_stringarray2()');
5139 END IF;
5140 --
5141 -- Concatenate the segment values. No separator after the last value.
5142 --
5143 IF (nsegs > 0) THEN
5144 FOR i IN 1..(nsegs-1) LOOP
5145 l_return := l_return || REPLACE(segs(i), sepchar, NEWLINE) || sepchar;
5146 END LOOP;
5147 l_return := l_return || REPLACE(segs(nsegs), sepchar, NEWLINE);
5148 END IF;
5149 RETURN(l_return);
5150 END from_stringarray2;
5151
5152
5153 --------------------------------------------------------------------------------
5154 -- This procedure parses a SQL string into following pieces:
5155 -- - bind (i.e. :bind portion of a SQL statement)
5156 -- - single quoted (i.e. string literal portion of a SQL statement)
5157 -- - sql (i.e. rest of the sql statement)
5158 --
5159 --------------------------------------------------------------------------------
5160 procedure parse_sql_string(p_sql_string in varchar2,
5161 px_sql_pieces in out nocopy sql_pieces_tab_type)
5162 IS
5163 l_sql_string varchar2(32000);
5164 l_sql_piece_count BINARY_INTEGER;
5165 l_first_chr varchar2(1);
5166 l_tmp_string varchar2(32000);
5167
5168 l_quote_pos number;
5169 l_quote2_pos number;
5170 l_colon_pos number;
5171 l_end_pos number;
5172 begin
5173 l_sql_string := p_sql_string;
5174 l_sql_piece_count := 0;
5175 px_sql_pieces.DELETE;
5176
5177 while (l_sql_string is not null) loop
5178
5179 l_first_chr := substr(l_sql_string, 1, 1);
5180
5181 if (l_first_chr = SSP_QUOTE) then
5182 --
5183 -- single quoted section, find the end
5184 --
5185 l_quote2_pos := 0;
5186 loop
5187 --
5188 -- Skip double single quotes
5189 --
5190 l_quote_pos := instr(l_sql_string, SSP_QUOTE, l_quote2_pos + 2);
5191 if (l_quote_pos = 0) then
5192 --
5193 -- Error, not terminated properly
5194 --
5195 FND_MESSAGE.set_name('FND', 'FLEX-SQL MISSING QUOTE');
5196 FND_MESSAGE.set_token('CLAUSE', p_sql_string);
5197
5198 raise_application_error
5199 (-20001,
5200 'Error: Single Quote is not terminated properly. ' ||
5201 'SQL: ' || p_sql_string);
5202 else
5203 l_quote2_pos := instr(l_sql_string, SSP_QUOTE2, l_quote_pos);
5204 if (l_quote2_pos = l_quote_pos) then
5205 --
5206 -- double single quote, skip it
5207 --
5208 null;
5209 else
5210 --
5211 -- end of single quoted section
5212 --
5213 exit;
5214 end if;
5215 end if;
5216 end loop;
5217
5218 l_sql_piece_count := l_sql_piece_count + 1;
5219 px_sql_pieces(l_sql_piece_count).piece_type := SSP_PIECE_TYPE_QUOTED;
5220 px_sql_pieces(l_sql_piece_count).piece_text := substr(l_sql_string, 1, l_quote_pos);
5221
5222 l_sql_string := substr(l_sql_string, l_quote_pos + 1);
5223
5224 elsif (l_first_chr = SSP_COLON) then
5225 --
5226 -- bind section
5227 --
5228 l_tmp_string := ltrim(l_sql_string, SSP_BIND_CHARS);
5229
5230 l_sql_piece_count := l_sql_piece_count + 1;
5231 px_sql_pieces(l_sql_piece_count).piece_type := SSP_PIECE_TYPE_BIND;
5232 px_sql_pieces(l_sql_piece_count).piece_text := substr(l_sql_string, 1, length(l_sql_string) - nvl(length(l_tmp_string), 0));
5233
5234 l_sql_string := l_tmp_string;
5235
5236 else
5237 --
5238 -- sql section, find the end
5239 --
5240 l_quote_pos := instr(l_sql_string, SSP_QUOTE);
5241 l_colon_pos := instr(l_sql_string, SSP_COLON);
5242
5243 if ((l_quote_pos = 0) and (l_colon_pos = 0)) then
5244 --
5245 -- no quotes, no binds, the remaining section is just sql
5246 --
5247 l_end_pos := length(l_sql_string);
5248
5249 elsif (l_quote_pos = 0) then
5250 --
5251 -- no quotes
5252 --
5253 l_end_pos := l_colon_pos - 1;
5254
5255 elsif (l_colon_pos = 0) then
5256 --
5257 -- no binds
5258 --
5259 l_end_pos := l_quote_pos - 1;
5260
5261 else
5262 --
5263 -- both quotes, and binds
5264 --
5265 l_end_pos := least(l_quote_pos, l_colon_pos) - 1;
5266
5267 end if;
5268
5269 l_sql_piece_count := l_sql_piece_count + 1;
5270 px_sql_pieces(l_sql_piece_count).piece_type := SSP_PIECE_TYPE_SQL;
5271 px_sql_pieces(l_sql_piece_count).piece_text := substr(l_sql_string, 1, l_end_pos);
5272
5273 l_sql_string := substr(l_sql_string, l_end_pos + 1);
5274
5275 end if;
5276 end loop;
5277 end parse_sql_string;
5278
5279 BEGIN
5280 chr_newline := fnd_global.newline;
5281 NEWLINE := fnd_global.newline;
5282 TAB := fnd_global.tab;
5283 WHITESPACE := ' ' || TAB || NEWLINE;
5284
5285 fnd_plsql_cache.generic_1to1_init('SV1.VST',
5286 vst_cache_controller,
5287 vst_cache_storage);
5288
5289 fnd_plsql_cache.generic_1to1_init('SV1.VSC',
5290 vsc_cache_controller,
5291 vsc_cache_storage);
5292
5293 fnd_plsql_cache.generic_1to1_init('SV1.FVC',
5294 fvc_cache_controller,
5295 fvc_cache_storage);
5296
5297 fnd_plsql_cache.generic_1tom_init('SV1.FSQ',
5298 fsq_cache_controller,
5299 fsq_cache_storage);
5300
5301 fnd_plsql_cache.generic_1tom_init('SV1.VSQ',
5302 vsq_cache_controller,
5303 vsq_cache_storage);
5304
5305 fnd_plsql_cache.custom_1tom_init('SV1.STR',
5306 str_cache_controller);
5307 str_cache_storage.DELETE;
5308
5309 --
5310 -- Decimal separators.
5311 --
5312 IF (fnd_flex_val_util.get_mask('CANONICAL_NUMERIC_CHARS',tmp_varchar2)) THEN
5313 m_nc := Substr(tmp_varchar2,1,1);
5314 ELSE
5315 m_nc := '.';
5316 END IF;
5317
5318 IF (fnd_flex_val_util.get_mask('DB_NUMERIC_CHARS',tmp_varchar2)) THEN
5319 m_nb := Substr(tmp_varchar2,1,1);
5320 ELSE
5321 m_nb := '.';
5322 END IF;
5323
5324 IF (fnd_flex_val_util.get_mask('NLS_NUMERIC_CHARS_OUT',tmp_varchar2)) THEN
5325 m_nd := Substr(tmp_varchar2,1,1);
5326 ELSE
5327 m_nd := '.';
5328 END IF;
5329 END fnd_flex_server1;
[Home] [Help]