4
1 PACKAGE BODY fnd_flex_server4 AS
2 /* $Header: AFFFSV4B.pls 120.8.12020000.2 2012/07/19 00:33:43 hgeorgi ship $ */
3
5
6 --------
7 -- PRIVATE TYPES
8 --
9
10 TYPE ValidatedSegmentArray IS RECORD
11 (nvalidated NUMBER,
12 segstats VARCHAR2(201),
13 segfmts FND_FLEX_SERVER1.SegFormats,
14 segcols FND_FLEX_SERVER1.TabColArray,
15 segcoltypes FND_FLEX_SERVER1.CharArray,
16 dispvals FND_FLEX_SERVER1.ValueArray,
17 vals FND_FLEX_SERVER1.ValueArray,
18 ids FND_FLEX_SERVER1.ValueIdArray,
19 descs FND_FLEX_SERVER1.ValueDescArray,
20 catdesclens FND_FLEX_SERVER1.NumberArray,
21 dispsegs FND_FLEX_SERVER1.DisplayedSegs);
22
23 ------------
24 -- PRIVATE CONSTANTS
25 --
26
27 MAX_NSEGS CONSTANT NUMBER := 200;
28 MAX_CATSEG_LEN CONSTANT NUMBER := 700;
29
30 -- ==================================================
31 -- CACHING
32 -- ==================================================
33
34 g_cache_return_code VARCHAR2(30);
35 g_cache_key VARCHAR2(2000);
36 g_cache_value fnd_plsql_cache.generic_cache_value_type;
37
38 -- --------------------------------------------------
39 -- cxc : Context Cache
40 -- --------------------------------------------------
41 cxc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
42 cxc_cache_storage fnd_plsql_cache.generic_cache_values_type;
43
44 -- --------------------------------------------------
45 -- gcc : Global Context Cache
46 -- --------------------------------------------------
47 gcc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
48 gcc_cache_storage fnd_plsql_cache.generic_cache_values_type;
49
50 /* -------------------------------------------------------------------- */
51 /* Private global variables */
52 /* -------------------------------------------------------------------- */
53
54 /* -------------------------------------------------------------------- */
55 /* Private definitions */
56 /* -------------------------------------------------------------------- */
57
58 FUNCTION find_descsegs(dflex_info IN FND_FLEX_SERVER1.DescFlexInfo,
59 row_id IN ROWID,
60 alt_table IN VARCHAR2,
61 data_field IN VARCHAR2,
62 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
63 nsegs_out OUT nocopy NUMBER,
64 segs_out OUT nocopy FND_FLEX_SERVER1.StringArray)
65 RETURN BOOLEAN;
66
67 FUNCTION read_datafield(dflex_info IN FND_FLEX_SERVER1.DescFlexInfo,
68 row_id IN ROWID,
69 table_name IN VARCHAR2,
70 datafield IN VARCHAR2,
71 nsegs OUT nocopy NUMBER,
72 segs OUT nocopy FND_FLEX_SERVER1.StringArray)
73 RETURN BOOLEAN;
74
75 FUNCTION read_segment_cols(dflex_info IN FND_FLEX_SERVER1.DescFlexInfo,
76 row_id IN ROWID,
77 table_name IN VARCHAR2,
78 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
79 nsegs OUT nocopy NUMBER,
80 segs OUT nocopy FND_FLEX_SERVER1.StringArray)
81 RETURN BOOLEAN;
82
83 FUNCTION get_desc_cols(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
84 context IN VARCHAR2,
85 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
86 contextn OUT nocopy NUMBER,
87 ncols OUT nocopy NUMBER,
88 cols OUT nocopy FND_FLEX_SERVER1.TabColArray,
89 coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
90 segfmts OUT nocopy FND_FLEX_SERVER1.SegFormats)
91 RETURN BOOLEAN;
92
93 FUNCTION get_descsegs(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
94 coldef IN FND_FLEX_SERVER1.ColumnDefinitions,
95 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
96 nsegs_out OUT nocopy NUMBER,
97 segs_out OUT nocopy FND_FLEX_SERVER1.stringarray,
98 x_context_segment_included OUT nocopy BOOLEAN)
99 RETURN BOOLEAN;
100
101 FUNCTION get_column_value(colvals IN FND_FLEX_SERVER1.ColumnValues,
102 colname IN VARCHAR2,
103 coltype IN VARCHAR2,
104 seg_fmt IN VARCHAR2,
105 seg_len IN NUMBER,
106 val OUT nocopy VARCHAR2) RETURN BOOLEAN;
107
108 FUNCTION
109 validate_descsegs(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
110 nsegs_in IN NUMBER,
111 segs IN FND_FLEX_SERVER1.StringArray,
112 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
113 v_date IN DATE,
114 uappid IN NUMBER,
115 respid IN NUMBER,
116 nsegs_out OUT nocopy NUMBER,
117 segfmts OUT nocopy FND_FLEX_SERVER1.SegFormats,
118 segstats OUT nocopy VARCHAR2,
119 cols OUT nocopy FND_FLEX_SERVER1.TabColArray,
120 coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
121 v_dispvals OUT nocopy FND_FLEX_SERVER1.ValueArray,
122 v_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
123 v_ids OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
124 v_descs OUT nocopy FND_FLEX_SERVER1.ValueDescArray,
125 desc_lens OUT nocopy FND_FLEX_SERVER1.NumberArray,
126 dispsegs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs,
127 errsegn OUT nocopy NUMBER) RETURN NUMBER;
128
129 FUNCTION
130 validate_context_segs(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
131 contxt_name IN VARCHAR2,
132 nsegs IN NUMBER,
133 segs IN FND_FLEX_SERVER1.StringArray,
137 respid IN NUMBER,
134 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
135 vdate IN DATE,
136 uappid IN NUMBER,
138 vsa OUT nocopy ValidatedSegmentArray,
139 errsegnum OUT nocopy NUMBER) RETURN NUMBER;
140
141 FUNCTION
142 validate_context(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
143 context_sval IN VARCHAR2,
144 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
145 vsa OUT nocopy ValidatedSegmentArray) RETURN NUMBER;
146
147 FUNCTION find_context_value(appl_id IN VARCHAR2,
148 dflex_name IN VARCHAR2,
149 p_id_or_value IN VARCHAR2,
150 seg_in IN VARCHAR2,
151 context_id OUT nocopy VARCHAR2,
152 context_val OUT nocopy VARCHAR2,
153 context_desc OUT nocopy VARCHAR2,
154 p_global_flag OUT nocopy VARCHAR2) RETURN VARCHAR2;
155
156 FUNCTION get_global_context(appl_id IN NUMBER,
157 dflex_name IN VARCHAR2,
158 glob_context OUT nocopy VARCHAR2) RETURN BOOLEAN;
159
160 FUNCTION append_vsegarray(destvsa IN OUT nocopy ValidatedSegmentArray,
161 sourcevsa IN ValidatedSegmentArray)
162 RETURN BOOLEAN;
163
164 PROCEDURE initialize_vsegarray(v_seg_array OUT nocopy ValidatedSegmentArray);
165
166 /* -------------------------------------------------------------------- */
167 /* Functions and procedures */
168 /* -------------------------------------------------------------------- */
169
170
171 /* ------------------------------------------------------------------------- */
172 /* The general purpose engine for descriptive flexfield validation. */
173 /* */
174 /* Takes concatenated segments or rowid as input. */
175 /* */
176 /* This function returns output arrays that may or may not be */
177 /* populated depending on the point at which the validation stopped. */
178 /* The number of output array elements populated is specified by */
179 /* nvalidated. Nvalidated is the number of enabled segments that */
180 /* were validated before validation stopped. */
181 /* Many error conditions return no array information at all. In */
182 /* this case nvalidated = 0 is returned. */
183 /* */
184 /* NOTE: Make sure to call FND_FLEX_SERVER1.init_globals before */
185 /* calling this function, to initialize debugging and messages. */
186 /* ------------------------------------------------------------------------ */
187
188 PROCEDURE descval_engine
189 (user_apid IN NUMBER,
190 user_resp IN NUMBER,
191 userid IN NUMBER,
192 flex_app_sname IN VARCHAR2,
193 desc_flex_name IN VARCHAR2,
194 val_date IN DATE,
195 invoking_mode IN VARCHAR2,
196 allow_nulls IN BOOLEAN,
197 update_table IN BOOLEAN,
198 ignore_active IN BOOLEAN,
199 concat_segs IN VARCHAR2,
200 vals_not_ids IN BOOLEAN,
201 use_column_def IN BOOLEAN,
202 column_def IN FND_FLEX_SERVER1.ColumnDefinitions,
203 rowid_in IN ROWID,
204 alt_tbl_name IN VARCHAR2,
205 data_field_name IN VARCHAR2,
206 nvalidated OUT nocopy NUMBER,
207 displayed_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
208 stored_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
209 segment_ids OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
210 descriptions OUT nocopy FND_FLEX_SERVER1.ValueDescArray,
211 desc_lengths OUT nocopy FND_FLEX_SERVER1.NumberArray,
212 seg_colnames OUT nocopy FND_FLEX_SERVER1.TabColArray,
213 seg_coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
214 segment_types OUT nocopy FND_FLEX_SERVER1.SegFormats,
215 displayed_segs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs,
216 seg_delimiter OUT nocopy VARCHAR2,
217 v_status OUT nocopy NUMBER,
218 seg_codes OUT nocopy VARCHAR2,
219 err_segnum OUT nocopy NUMBER) IS
220
221 nvals NUMBER;
222 nsegs NUMBER;
223 entered VARCHAR2(1);
224 dff_info FND_FLEX_SERVER1.DescFlexInfo;
225 dff_id FND_FLEX_SERVER1.FlexStructId;
226 segs FND_FLEX_SERVER1.StringArray;
227 value_dvals FND_FLEX_SERVER1.ValueArray;
228 value_vals FND_FLEX_SERVER1.ValueArray;
229 value_ids FND_FLEX_SERVER1.ValueIdArray;
230 value_descs FND_FLEX_SERVER1.ValueDescArray;
231 cc_cols FND_FLEX_SERVER1.TabColArray;
232 cc_coltypes FND_FLEX_SERVER1.CharArray;
233 desc_lens FND_FLEX_SERVER1.NumberArray;
234 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
235 vv_flags FND_FLEX_SERVER1.ValueValidationFlags;
236 segtypes FND_FLEX_SERVER1.SegFormats;
237 segcodes VARCHAR2(201);
238 errcode NUMBER;
239 errsegnum NUMBER;
240 catsegs VARCHAR2(32000);
241 l_context_segment_included BOOLEAN;
242
243 BEGIN
244 IF (fnd_flex_server1.g_debug_level > 0) THEN
245 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
246 'BEGIN SV4.descval_engine() ');
247 END IF;
248
249 -- Initialize all output variables so that returning from any point
250 -- results in a valid state.
251 --
252 nvalidated := 0;
253 segment_types.nsegs := 0;
254 displayed_segs.n_segflags := 0;
255 v_status := FND_FLEX_SERVER1.VV_ERROR;
256 l_context_segment_included := FALSE;
257
258 -- Initialize everything which affects returned information. This way we
259 -- can process all returned information before returning when exiting from
260 -- any point in this code even if there is an error.
261 -- Dont worry about initializing strings to null.
262
263 nvals := 0;
264 nsegs := 0;
265 segtypes.nsegs := 0;
266 disp_segs.n_segflags := 0;
267 errcode := FND_FLEX_SERVER1.VV_ERROR;
268
269 if((concat_segs is null) and (rowid_in is null)) then
270 entered := 'N';
271 else
272 entered := 'Y';
273 end if;
274
275 -- Get all required info about the desired flexfield structure.
276 --
277 if(FND_FLEX_SERVER2.get_descstruct(flex_app_sname, desc_flex_name,
278 dff_info) = FALSE) then
279 goto return_error;
280 end if;
281
282 -- Limit concatenated segment length for compatibility with client
283 --
284 if(LENGTHB(concat_segs) > MAX_CATSEG_LEN) then
285 FND_MESSAGE.set_name('FND', 'FLEX-CONCAT LEN > IAPFLEN');
286 FND_MESSAGE.set_token('MAXFLDLEN', to_char(MAX_CATSEG_LEN));
287 goto return_error;
288 end if;
289
290 -- First check that operation makes sense
291 --
292 if((invoking_mode is null) or
293 (invoking_mode NOT IN ('V', 'P', 'L', 'C', 'D'))) then
294 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD INVOKE');
295 goto return_error;
296 end if;
297
298 -- Set validation flags.
299 -- Descriptive flexfields similar to key flexfields except:
300 -- Required token is always Yes for Descriptive flexfields.
301 -- Descriptive flexfields always in PARTIAL validation mode (never PIP).
302 --
303 vv_flags.default_all_displayed := TRUE;
304 vv_flags.values_not_ids := vals_not_ids and
305 (invoking_mode not in ('L', 'C'));
306 vv_flags.default_all_required := ((invoking_mode in ('V', 'D')) and (entered='N'));
307 vv_flags.default_non_displayed := (invoking_mode in ('P', 'V', 'D'));
308 vv_flags.allow_nulls := allow_nulls;
309 vv_flags.message_on_null := TRUE;
310 vv_flags.all_orphans_valid := FALSE;
311 vv_flags.ignore_security := (invoking_mode = 'L');
312 vv_flags.ignore_expired := (invoking_mode = 'L') or ignore_active;
313 vv_flags.ignore_disabled := (invoking_mode = 'L') or ignore_active;
314 vv_flags.message_on_security := (invoking_mode <> 'L');
315 vv_flags.stop_on_value_error := (invoking_mode <> 'P');
319
316 vv_flags.exact_nsegs_required := ((invoking_mode in ('L', 'C')) or
317 ((invoking_mode in ('V', 'D')) and not vv_flags.default_all_required));
318 vv_flags.stop_on_security := (invoking_mode in ('V', 'C', 'D'));
320 /* invoking_mode is added for bug872437. */
321 vv_flags.invoking_mode := invoking_mode;
322
323 -- Add input parameters to the debug information
324 --
325 IF (fnd_flex_server1.g_debug_level > 0) THEN
326 FND_FLEX_SERVER1.add_debug('User AppId = ' || to_char(user_apid));
327 FND_FLEX_SERVER1.add_debug(', User Resp = ' || to_char(user_resp));
328 FND_FLEX_SERVER1.add_debug(', User Id = ' || to_char(userid));
329 FND_FLEX_SERVER1.add_debug(', Ap Short Name = ' || flex_app_sname);
330 FND_FLEX_SERVER1.add_debug(', Desc Flex Name = '||desc_flex_name||' ');
331 FND_FLEX_SERVER1.add_debug(', Val Date = ' ||
332 to_char(val_date, 'YYYY/MM/DD HH24:MI:SS'));
333 FND_FLEX_SERVER1.add_debug(', Invoke = ' || invoking_mode);
334 if(ignore_active) then
335 FND_FLEX_SERVER1.add_debug(', Ignore disabled/expired');
336 end if;
337 if(allow_nulls) then
338 FND_FLEX_SERVER1.add_debug(', Allow Nulls');
339 end if;
340 if(update_table) then
341 FND_FLEX_SERVER1.add_debug(', Update Table');
342 end if;
343 FND_FLEX_SERVER1.add_debug(', Concat Segs = ' || concat_segs);
344 if(vals_not_ids) then
345 FND_FLEX_SERVER1.add_debug(', Vals');
346 else
347 FND_FLEX_SERVER1.add_debug(', Ids');
348 end if;
349 FND_FLEX_SERVER1.add_debug(', Rowid = ' || ROWIDTOCHAR(rowid_in));
350 FND_FLEX_SERVER1.add_debug(', Alt Table = ' || alt_tbl_name);
351 FND_FLEX_SERVER1.add_debug(', Data Field = ' || data_field_name||'. ');
352 if(use_column_def) then
353 FND_FLEX_SERVER1.add_debug(', ColDefs: ');
354 if(column_def.context_value_set) then
355 FND_FLEX_SERVER1.add_debug('*Context* = ('
356 || column_def.context_value || ') ');
357 end if;
358 for i in 1..column_def.colvals.ncolumns loop
359 FND_FLEX_SERVER1.add_debug
360 (column_def.colvals.column_names(i) || ':' ||
361 column_def.colvals.column_types(i) || ' = (' ||
362 column_def.colvals.column_values(i) || ') ');
363 end loop;
364 end if;
365 END IF;
366
367 -- If LOADDESC or CHECKDESC modes get the ids from the row in the table
368 -- if rowid not null. If LOADDESC, CHECKDESC or VALDESC and rowid is null
369 -- then get inputs from the column definitions if use_column_def is TRUE.
370 -- Otherwise break up the concatenated segments.
371 --
372 if((invoking_mode in ('L', 'C')) and (rowid_in is not null)) THEN
373 IF (fnd_flex_server1.g_debug_level > 0) THEN
374 fnd_flex_server1.add_debug('calling find_descsegs()');
375 END IF;
376 if(find_descsegs(dff_info, rowid_in, alt_tbl_name, data_field_name,
377 vv_flags, nsegs, segs) = FALSE) then
378 goto return_error;
379 end if;
380 elsif((invoking_mode in ('L', 'C', 'V', 'D')) and use_column_def) THEN
381 IF (fnd_flex_server1.g_debug_level > 0) THEN
382 fnd_flex_server1.add_debug('calling get_descsegs()');
383 END IF;
384
385 if(get_descsegs(dff_info, column_def, vv_flags, nsegs, segs, l_context_segment_included)=FALSE) then
386 goto return_error;
387 end if;
388 else
389 IF (fnd_flex_server1.g_debug_level > 0) THEN
390 fnd_flex_server1.add_debug('calling to_stringarray()');
391 END IF;
392 nsegs := FND_FLEX_SERVER1.to_stringarray(concat_segs,
393 dff_info.segment_delimiter, segs);
394 end if;
395
396 -- Check to make sure there are not too many segments.
397 --
398 -- Bug 9929658 added OR (use_column_def = FALSE)
399 -- use_column_def is always set to FALSE except in one case
400 -- when called by validate_desccols(). Only when use_column_def=TRUE
401 -- is l_context_Segment_included set by get_descsegs(). So for all
402 -- other fnd_flex_descval calls, l_context_Segment_included always was
403 -- false. So for this case I added OR (use_column_def = FALSE).
404 -- This whole if statment seems to be worthless, if you 30 segs to
405 -- api and you don't even have 30 segs defined it will give error.
406 -- Instead of just removing it, I added this OR stmnt so that I do
407 -- not undo prvious fix. If use_column_def = TRUE, the code will work
408 -- as before this change.
409 if(nsegs > MAX_NSEGS) then
410 if ((l_context_Segment_included and (nsegs = MAX_NSEGS + 1)) OR
411 (use_column_def = FALSE)) then
412 NULL;
413 else
414 FND_MESSAGE.set_name('FND', 'FLEX-TOO MANY SEGS');
415 FND_MESSAGE.set_token('NSEGS', MAX_NSEGS);
416 goto return_error;
417 end if;
418 END IF;
419
420
421 if (nsegs = 0) then
422 if (column_def.context_value_set) then
423 errcode := FND_FLEX_SERVER1.VV_VALID;
424 goto return_outvars;
425 end if;
426 end if;
427
428
429 IF (fnd_flex_server1.g_debug_level > 0) THEN
430 catsegs := substrb(FND_FLEX_SERVER1.from_stringarray(nsegs, segs, '*'), 1, 32000);
431 FND_FLEX_SERVER1.add_debug(catsegs);
432 END IF;
433
434
435 -- Validate segments.
436 --
437 errcode := validate_descsegs(dff_info, nsegs, segs, vv_flags, val_date,
438 user_apid, user_resp, nvals, segtypes, segcodes, cc_cols,
442 IF (fnd_flex_server1.g_debug_level > 0) THEN
439 cc_coltypes, value_dvals, value_vals, value_ids, value_descs,
440 desc_lens, disp_segs, errsegnum);
441
443 FND_FLEX_SERVER1.add_debug(' validate_descsegs() returns errcode ');
444 FND_FLEX_SERVER1.add_debug(to_char(errcode) ||' and '|| to_char(nvals));
445 FND_FLEX_SERVER1.add_debug(' values. SegCodes: ' || segcodes);
446 FND_FLEX_SERVER1.add_debug(' ErrSeg: ' || to_char(errsegnum));
447 FND_FLEX_SERVER1.add_debug(' Returned arrays:');
448 for i in 1..nvals loop
449 FND_FLEX_SERVER1.add_debug('"' || segtypes.vs_format(i));
450 FND_FLEX_SERVER1.add_debug(to_char(segtypes.vs_maxsize(i), 'S099'));
451 FND_FLEX_SERVER1.add_debug('*' || value_dvals(i) || '*');
452 FND_FLEX_SERVER1.add_debug(cc_cols(i) || ':' ||cc_coltypes(i)||'" ');
453 end loop;
454 END IF;
455 <<return_outvars>>
456 displayed_vals := value_dvals;
457 stored_vals := value_vals;
458 segment_ids := value_ids;
459 descriptions := value_descs;
460 desc_lengths := desc_lens;
461 seg_colnames := cc_cols;
462 seg_coltypes := cc_coltypes;
463 nvalidated := nvals;
464 segment_types := segtypes;
465 displayed_segs := disp_segs;
466 seg_delimiter := dff_info.segment_delimiter;
467 seg_codes := segcodes;
468 err_segnum := errsegnum;
469 v_status := errcode;
470 return;
471
472 <<return_error>>
473 v_status := FND_FLEX_SERVER1.VV_ERROR;
474 return;
475
476 EXCEPTION
477 WHEN OTHERS then
478 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
479 FND_MESSAGE.set_token('MSG','descval_engine() exception: ' || SQLERRM);
480 v_status := FND_FLEX_SERVER1.VV_ERROR;
481 return;
482 END descval_engine;
483
484 /* ------------------------------------------------------------------------ */
485 /* Finds descriptive flexfield segment ids from existing row in table. */
486 /* If alt_table is not null looks for the row in that table rather */
487 /* than in the table on which the descriptive flexfield is defined. */
488 /* Note special error if columns do not match up. */
489 /* If data_field is not null, uses this field as source of */
490 /* concatenated ids rather than the individual segment fields. */
491 /* Returns segment ids for all enabled segments whether or not they */
492 /* are displayed in the order that they are displayed within each */
493 /* context. */
494 /* Returns TRUE if all ok, or FALSE and sets FND_MESSAGE on error. */
495 /* ------------------------------------------------------------------------ */
496
497 FUNCTION find_descsegs(dflex_info IN FND_FLEX_SERVER1.DescFlexInfo,
498 row_id IN ROWID,
499 alt_table IN VARCHAR2,
500 data_field IN VARCHAR2,
501 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
502 nsegs_out OUT nocopy NUMBER,
503 segs_out OUT nocopy FND_FLEX_SERVER1.StringArray)
504 RETURN BOOLEAN IS
505 effective_table VARCHAR2(30);
506
507 BEGIN
508 IF (fnd_flex_server1.g_debug_level > 0) THEN
509 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
510 'BEGIN SV4.find_descsegs() ');
511 END IF;
512
513 nsegs_out := 0;
514
515 if(alt_table is not null) then
516 effective_table := SUBSTRB(alt_table, 1, 30);
517 else
518 effective_table := dflex_info.table_name;
519 end if;
520
521 -- If use data field just select that column and break up segment ids.
522 -- Otherwise must get all columns.
523
524 if(data_field is not null) then
525 return(read_datafield(dflex_info, row_id, effective_table,
526 data_field, nsegs_out, segs_out));
527 end if;
528 return(read_segment_cols(dflex_info, row_id, effective_table,
529 vflags, nsegs_out, segs_out));
530 EXCEPTION
531 WHEN OTHERS then
532 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
533 FND_MESSAGE.set_token('MSG','find_descsegs() exception: ' || SQLERRM);
534 return(FALSE);
535
536 END find_descsegs;
537
538 /* ------------------------------------------------------------------------ */
539 /* Reads concatenated segment ids from data field in the particular */
540 /* row of the specified table. Breaks up concatenated segments into */
541 /* a string array for return. */
542 /* Returns TRUE on success or FALSE and sets FND_MESSAGE on error. */
543 /* ------------------------------------------------------------------------ */
544
545 FUNCTION read_datafield(dflex_info IN FND_FLEX_SERVER1.DescFlexInfo,
546 row_id IN ROWID,
547 table_name IN VARCHAR2,
548 datafield IN VARCHAR2,
549 nsegs OUT nocopy NUMBER,
550 segs OUT nocopy FND_FLEX_SERVER1.StringArray)
551 RETURN BOOLEAN IS
552 concat_segids VARCHAR2(2000);
553 rstat NUMBER;
554
555 BEGIN
556 IF (fnd_flex_server1.g_debug_level > 0) THEN
557 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
561 fnd_dsql.init;
558 'BEGIN SV4.read_datafield() ');
559 END IF;
560
562 fnd_dsql.add_text('select SUBSTRB(' || datafield || ', 1, 2000)' ||
563 ' from ' || table_name ||
564 ' where rowid = CHARTOROWID(');
565 fnd_dsql.add_bind(ROWIDTOCHAR(row_id));
566 fnd_dsql.add_text(')');
567
568 -- Look up the segment values or ids.
569 --
570 rstat := FND_FLEX_SERVER1.x_dsql_select_one(concat_segids);
571 if(rstat <> 1) then
572 if(rstat = 0) then
573 FND_MESSAGE.set_name('FND', 'FLEX-DFF ROW NOT FOUND');
574 FND_MESSAGE.set_token('TABLE', table_name);
575 FND_MESSAGE.set_token('ROWID', ROWIDTOCHAR(row_id));
576 elsif(rstat = -2) then
577 FND_MESSAGE.set_name('FND', 'FLEX-DFF BAD DATAFIELD');
578 FND_MESSAGE.set_token('TABLE', table_name);
579 FND_MESSAGE.set_token('DATAFIELD', datafield);
580 else
581 null;
582 end if;
583 return(FALSE);
584 end if;
585
586 nsegs := FND_FLEX_SERVER1.to_stringarray(concat_segids,
587 dflex_info.segment_delimiter, segs);
588 return(TRUE);
589
590 EXCEPTION
591 WHEN OTHERS then
592 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
593 FND_MESSAGE.set_token('MSG','read_datafield() exception: '||SQLERRM);
594 return(FALSE);
595
596 END read_datafield;
597
598 /* ------------------------------------------------------------------------ */
599 /* Reads segment ids from individual attribute columns in the row */
600 /* of the effective table specified. Validates the context in the */
601 /* process to determine which columns to use for context-sensitive */
602 /* segments. Looks up the context value from table. */
603 /* Returns TRUE on success or FALSE and sets FND_MESSAGE on error. */
604 /* ------------------------------------------------------------------------ */
605
606 FUNCTION read_segment_cols(dflex_info IN FND_FLEX_SERVER1.DescFlexInfo,
607 row_id IN ROWID,
608 table_name IN VARCHAR2,
609 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
610 nsegs OUT nocopy NUMBER,
611 segs OUT nocopy FND_FLEX_SERVER1.StringArray)
612 RETURN BOOLEAN IS
613 ncols NUMBER;
614 cols FND_FLEX_SERVER1.TabColArray;
615 coltypes FND_FLEX_SERVER1.CharArray;
616 segfmts FND_FLEX_SERVER1.SegFormats;
617 value_component NUMBER;
618 rstat NUMBER;
619 context VARCHAR2(80);
620 context_number NUMBER;
621
622 BEGIN
623 IF (fnd_flex_server1.g_debug_level > 0) THEN
624 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
625 'BEGIN SV4.read_segment_cols() ');
626 END IF;
627
628 nsegs := 0;
629
630 -- Determine if values or ids are stored in the table columns.
631 -- Generally only expect ids, but in some cases we may want to
632 -- allow users to input values into the segment columns and have
633 -- us turn them into ids.
634 --
635 if(vflags.values_not_ids) then
636 value_component := FND_FLEX_SERVER1.VC_VALUE;
637 else
638 value_component := FND_FLEX_SERVER1.VC_ID;
639 end if;
640
641 -- Get context value stored in the table
642 -- Assume context column is CHAR or VARCHAR2
643 --
644 IF (fnd_flex_server1.g_debug_level > 0) THEN
645 FND_FLEX_SERVER1.add_debug(' Context col: '||dflex_info.context_column);
646 END IF;
647
648 fnd_dsql.init;
649 fnd_dsql.add_text('select SUBSTRB(' || dflex_info.context_column || ', 1, 80)' ||
650 ' from ' || table_name ||
651 ' where rowid = CHARTOROWID(');
652 fnd_dsql.add_bind(ROWIDTOCHAR(row_id));
653 fnd_dsql.add_text(')');
654
655 rstat := FND_FLEX_SERVER1.x_dsql_select_one(context);
656 if(rstat <> 1) then
657 if(rstat = 0) then
658 FND_MESSAGE.set_name('FND', 'FLEX-DFF ROW NOT FOUND');
659 FND_MESSAGE.set_token('TABLE', table_name);
660 FND_MESSAGE.set_token('ROWID', ROWIDTOCHAR(row_id));
661 elsif(rstat = -2) then
662 FND_MESSAGE.set_name('FND', 'FLEX-DFF BAD SEGCOLS');
663 FND_MESSAGE.set_token('TABLE', table_name);
664 else
665 null;
666 end if;
667 return(FALSE);
668 end if;
669
670 -- Get names of columns used. Must validate context to do this.
671 --
672 if(get_desc_cols(dflex_info, context, vflags, context_number,
673 ncols, cols, coltypes, segfmts) = FALSE) then
674 return(FALSE);
675 end if;
676
677 IF (fnd_flex_server1.g_debug_level > 0) THEN
678 FND_FLEX_SERVER1.add_debug('Selecting all columns ');
679 END IF;
680
681 -- Build SQL statement to select segment columns in order for
682 -- global segments, the context segment, and context-sensitive segments.
683 --
684
685 fnd_dsql.init;
686 fnd_dsql.add_text('select ');
687 for i in 1..ncols loop
688 if(i > 1) then
689 fnd_dsql.add_text(', ');
690 end if;
691 fnd_dsql.add_text(FND_FLEX_SERVER1.select_clause
692 (cols(i), coltypes(i),
693 value_component, segfmts.vs_format(i), segfmts.vs_maxsize(i)));
694 end loop;
695 fnd_dsql.add_text(' from ' || table_name ||
699
696 ' where rowid = CHARTOROWID(');
697 fnd_dsql.add_bind(ROWIDTOCHAR(row_id));
698 fnd_dsql.add_text(')');
700 -- Look up the segment values or ids.
701 --
702 rstat := FND_FLEX_SERVER1.x_dsql_select(ncols, segs);
703 if(rstat <> 1) then
704 if(rstat = 0) then
705 FND_MESSAGE.set_name('FND', 'FLEX-DFF ROW NOT FOUND');
706 FND_MESSAGE.set_token('TABLE', table_name);
707 FND_MESSAGE.set_token('ROWID', ROWIDTOCHAR(row_id));
708 elsif(rstat = -2) then
709 FND_MESSAGE.set_name('FND', 'FLEX-DFF BAD SEGCOLS');
710 FND_MESSAGE.set_token('TABLE', table_name);
711 else
712 null;
713 end if;
714 return(FALSE);
715 end if;
716
717 nsegs := ncols;
718 return(TRUE);
719
720 EXCEPTION
721 WHEN OTHERS then
722 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
723 FND_MESSAGE.set_token('MSG','read_segment_cols() exception: '||SQLERRM);
724 return(FALSE);
725
726 END read_segment_cols;
727
728 /* ------------------------------------------------------------------------ */
729 /* Gets the names of the columns corresponding to the segment ids */
730 /* for the specified descriptive flexfield. Validates the context */
731 /* value in the process to determine which context segment columns */
732 /* to use. Returns the columns in display order within each context */
733 /* for all enabled segments even if they are not displayed. Returns */
734 /* global context segment columns first, then the context segment */
735 /* column, then the columns of the context-sensitive segments. */
736 /* Also returns a number indicating the context segment number. */
737 /* Returns TRUE if all ok, or FALSE and sets FND_MESSAGE on error. */
738 /* ------------------------------------------------------------------------ */
739
740 FUNCTION get_desc_cols(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
741 context IN VARCHAR2,
742 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
743 contextn OUT nocopy NUMBER,
744 ncols OUT nocopy NUMBER,
745 cols OUT nocopy FND_FLEX_SERVER1.TabColArray,
746 coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
747 segfmts OUT nocopy FND_FLEX_SERVER1.SegFormats)
748 RETURN BOOLEAN IS
749
750 fstruct FND_FLEX_SERVER1.FlexStructId;
751
752 n_global NUMBER;
753 global_cols FND_FLEX_SERVER1.TabColArray;
754 global_coltypes FND_FLEX_SERVER1.CharArray;
755 global_segfmts FND_FLEX_SERVER1.SegFormats;
756
757 n_context NUMBER;
758 context_cols FND_FLEX_SERVER1.TabColArray;
759 context_coltypes FND_FLEX_SERVER1.CharArray;
760 context_segfmts FND_FLEX_SERVER1.SegFormats;
761
762 context_vsa ValidatedSegmentArray;
763 colcount NUMBER;
764 rstat NUMBER;
765 vc_return NUMBER;
766
767 BEGIN
768 IF (fnd_flex_server1.g_debug_level > 0) THEN
769 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
770 'BEGIN SV4.get_desc_cols() ');
771 END IF;
772
773 -- Initialize outputs to return nothing in case of error.
774 --
775 ncols := 0;
776 segfmts.nsegs := 0;
777
778 fstruct.isa_key_flexfield := FALSE;
779 fstruct.application_id := dff_info.application_id;
780 fstruct.desc_flex_name := dff_info.name;
781
782 -- Return name of global context.
783 --
784 if(get_global_context(dff_info.application_id, dff_info.name,
785 fstruct.desc_flex_context) = FALSE) then
786 return(FALSE);
787 end if;
788
789 -- Get segment mapping for global segments
790 --
791 if(FND_FLEX_SERVER2.get_struct_cols(fstruct, dff_info.table_appl_id,
792 dff_info.table_id, n_global, global_cols,
793 global_coltypes, global_segfmts) = FALSE) then
794 return(FALSE);
795 end if;
796 IF (fnd_flex_server1.g_debug_level > 0) THEN
797 FND_FLEX_SERVER1.add_debug(' Found global segment mapping: [');
798 for i in 1..n_global loop
799 FND_FLEX_SERVER1.add_debug(global_cols(i) || ':' ||
800 global_coltypes(i) ||' ');
801 end loop;
802 FND_FLEX_SERVER1.add_debug('] ');
803 END IF;
804
805 -- Validate the context value
806 --
807 vc_return := validate_context(dff_info, context, vflags, context_vsa);
808 IF (vc_return <> FND_FLEX_SERVER1.VV_VALID) then
809 IF (vc_return <> FND_FLEX_SERVER1.VV_CTXTNOSEG) then
810 return(FALSE);
811 END IF;
812 END IF;
813 -- return(FALSE);
814 -- end if;
815
816
817 IF (fnd_flex_server1.g_debug_level > 0) THEN
818 FND_FLEX_SERVER1.add_debug('Context value (' || context_vsa.ids(1) ||
819 ') valid. ');
820 END IF;
821
822 -- Get segment mapping for context-sensitive segments
823 --
824 fstruct.desc_flex_context := context_vsa.ids(1);
825 IF (fstruct.desc_flex_context IS NOT NULL) AND
826 (vc_return <> FND_FLEX_SERVER1.VV_CTXTNOSEG) THEN
827 if(FND_FLEX_SERVER2.get_struct_cols
828 (fstruct, dff_info.table_appl_id,
829 dff_info.table_id, n_context, context_cols,
830 context_coltypes, context_segfmts) = FALSE) then
831 return(FALSE);
832 end if;
833
834 IF (fnd_flex_server1.g_debug_level > 0) THEN
835 FND_FLEX_SERVER1.add_debug(' Found context segment mapping: [');
836 for i in 1..n_context loop
837 FND_FLEX_SERVER1.add_debug(context_cols(i) || ':' ||
838 context_coltypes(i) ||' ');
839 end loop;
840 FND_FLEX_SERVER1.add_debug('] ');
841 END IF;
842 ELSE
843 n_context := 0;
844 context_segfmts.nsegs := 0;
845 END IF;
846
847 -- Now concatenate the semgment columns for global,
848 -- context, and context-sensitive contexts.
849
850 colcount := 0;
851
852 -- Global columns
853 --
854 for i in 1..n_global loop
855 colcount := colcount + 1;
856 cols(colcount) := global_cols(i);
857 coltypes(colcount) := global_coltypes(i);
858 segfmts.vs_format(colcount) := global_segfmts.vs_format(i);
859 segfmts.vs_maxsize(colcount) := global_segfmts.vs_maxsize(i);
860 end loop;
861
862 -- Context column. Also note which it is.
863 --
864 colcount := colcount + 1;
865 cols(colcount) := context_vsa.segcols(1);
866 coltypes(colcount) := context_vsa.segcoltypes(1);
867 segfmts.vs_format(colcount) := context_vsa.segfmts.vs_format(1);
868 segfmts.vs_maxsize(colcount) := context_vsa.segfmts.vs_maxsize(1);
869 contextn := colcount;
870
871 -- Context-sensitive columns
872 --
873 for i in 1..n_context loop
874 colcount := colcount + 1;
875 cols(colcount) := context_cols(i);
876 coltypes(colcount) := context_coltypes(i);
877 segfmts.vs_format(colcount) := context_segfmts.vs_format(i);
878 segfmts.vs_maxsize(colcount) := context_segfmts.vs_maxsize(i);
879 end loop;
880
881 segfmts.nsegs := colcount;
882 ncols := colcount;
883 return(TRUE);
884
885 EXCEPTION
886 WHEN OTHERS then
887 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
888 FND_MESSAGE.set_token('MSG','get_desc_cols() exception: ' || SQLERRM);
889 return(FALSE);
890
891 END get_desc_cols;
892
893 /* ------------------------------------------------------------------------ */
894 /* Gets descriptive flexfield segment ids from the column definitions. */
895 /* Error if some required columns are not defined or if the column */
896 /* data types do not match those expected from the segment columns. */
897 /* Returns segment ids for all enabled segments whether or not they */
898 /* are displayed in the order that they are displayed within each */
899 /* context. */
900 /* Returns TRUE if all ok, or FALSE and sets FND_MESSAGE on error. */
901 /* ------------------------------------------------------------------------ */
902
903 FUNCTION get_descsegs(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
904 coldef IN FND_FLEX_SERVER1.ColumnDefinitions,
905 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
906 nsegs_out OUT nocopy NUMBER,
907 segs_out OUT nocopy FND_FLEX_SERVER1.StringArray,
908 x_context_segment_included OUT nocopy BOOLEAN)
909 RETURN BOOLEAN IS
910 thisval VARCHAR2(1000);
911 fstruct FND_FLEX_SERVER1.FlexStructId;
912
913 n_segs NUMBER;
914 seg_cols FND_FLEX_SERVER1.TabColArray;
915 seg_coltypes FND_FLEX_SERVER1.CharArray;
916 seg_fmts FND_FLEX_SERVER1.SegFormats;
917 context_seg NUMBER;
918
919 BEGIN
920 IF (fnd_flex_server1.g_debug_level > 0) THEN
921 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
922 'BEGIN SV4.get_descsegs() ');
923 END IF;
924
925 -- Initialize outputs in case of error.
926 -- Start with no defined segments
927 --
928 nsegs_out := 0;
929 x_context_segment_included := FALSE;
930
931 -- Get the context value. It might already be set in the coldef.
932 -- If not, then look it up from the column values.
933 --
934
935 if(coldef.context_value_set) then
936 thisval := coldef.context_value;
937 IF (fnd_flex_server1.g_debug_level > 0) THEN
938 FND_FLEX_SERVER1.add_debug('*Context* = (' || thisval || ') ');
939 END IF;
940 else
941 if(get_column_value(coldef.colvals, dff_info.context_column,
942 'V', 'C', 30, thisval) = FALSE) then
943 return(FALSE);
944 end if;
945 end if;
946
947 -- Get names of columns used. Must validate context to do this.
948 --
949 if(get_desc_cols(dff_info, thisval, vflags, context_seg,
950 n_segs, seg_cols, seg_coltypes, seg_fmts) = FALSE) then
951 return(FALSE);
952 end if;
953
957 --
954 -- Loop through the segment columns and get the corresponding values
955 -- in the desired order. Use the context value passed in for the context
956 -- segment if it is defined.
958 for i in 1..n_segs loop
959 if((i = context_seg) and (coldef.context_value_set)) then
960 segs_out(i) := coldef.context_value;
961 x_context_segment_included := TRUE;
962 IF (fnd_flex_server1.g_debug_level > 0) THEN
963 FND_FLEX_SERVER1.add_debug('*Context* = (' ||
964 coldef.context_value || ') ');
965 END IF;
966 else
967 if(get_column_value(coldef.colvals, seg_cols(i), seg_coltypes(i),
968 seg_fmts.vs_format(i), seg_fmts.vs_maxsize(i), thisval)=FALSE) then
969 return(FALSE);
970 end if;
971 segs_out(i) := thisval;
972 end if;
973 end loop;
974
975 -- Return the segments out
976 --
977 nsegs_out := n_segs;
978 return(TRUE);
979
980 EXCEPTION
981 WHEN OTHERS then
982 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
983 FND_MESSAGE.set_token('MSG','get_descsegs() exception: ' || SQLERRM);
984 return(FALSE);
985
986 END get_descsegs;
987
988 /* ----------------------------------------------------------------------- */
989 /* Gets the value associated with a given column name from the */
990 /* pre-defined ColumnValues structure. A column with the same name */
991 /* and data type must be found to consider the column found. */
992 /* Converts the column value stored in the generic character */
993 /* representation into the representation required for a segment */
994 /* with the indicated value set format and size. */
995 /* Performs case-insensitive column name comparison. */
996 /* Returns TRUE if all ok, or FALSE and sets FND_MESSAGE on error. */
997 /* ----------------------------------------------------------------------- */
998
999 FUNCTION get_column_value(colvals IN FND_FLEX_SERVER1.ColumnValues,
1000 colname IN VARCHAR2,
1001 coltype IN VARCHAR2,
1002 seg_fmt IN VARCHAR2,
1003 seg_len IN NUMBER,
1004 val OUT nocopy VARCHAR2) RETURN BOOLEAN IS
1005 d DATE;
1006 dfmt VARCHAR2(40);
1007 valindex NUMBER;
1008 niceval VARCHAR2(1000);
1009
1010 BEGIN
1011 IF (fnd_flex_server1.g_debug_level > 0) THEN
1012 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1013 'BEGIN SV4.get_column_value() ');
1014 END IF;
1015
1016 -- Find index to value in column value table.
1017 --
1018 valindex := 0;
1019 for i in 1..colvals.ncolumns loop
1020 if((colname = colvals.column_names(i)) and
1021 ((coltype = colvals.column_types(i)) or
1022 (coltype in ('C', 'V') and
1023 colvals.column_types(i) in ('C', 'V')))) then
1024 valindex := i;
1025 exit;
1026 end if;
1027 end loop;
1028
1029 -- Error if column not defined
1030 --
1031 if(valindex = 0) then
1032 FND_MESSAGE.set_name('FND', 'FLEX-DFF COLUMN UNDEFINED');
1033 FND_MESSAGE.set_token('COLNAME', colname);
1034 FND_MESSAGE.set_token('COLTYPE', coltype);
1035 return(FALSE);
1036 end if;
1037
1038 -- Convert format of data to that desired for the value set.
1039 -- Assume numbers are already in the default to_char(n) format, and
1040 -- that dates are in the to_char(d, FND_FLEX_SERVER1.DATETIME_FMT) format.
1041 --
1042 -- Flex expects numbers to be in the default to_char(n) format.
1043 -- Flex expects most dates to be in the default to_char(d) format,
1044 -- except translatable dates which are in FND_FLEX_SERVER1.stored_date_format
1045 -- if ids or FND_FLEX_SERVER1.displayed_date_format if values. So we need
1046 -- to convert only if column is of date type.
1047 -- Dates input are the stored not displayed formats.
1048 -- See FND_FLEX_SERVER1.select_clause().
1049 --
1050 if(coltype = 'D') then
1051 d := to_date(colvals.column_values(valindex),
1052 FND_FLEX_SERVER1.DATETIME_FMT);
1053 if(seg_fmt in ('X', 'Y', 'Z')) then
1054 dfmt := FND_FLEX_SERVER1.stored_date_format(seg_fmt, seg_len);
1055 niceval := to_char(d, dfmt);
1056 else
1057 niceval := to_char(d);
1058 end if;
1059 else
1060 niceval := colvals.column_values(valindex);
1061 end if;
1062
1063 IF (fnd_flex_server1.g_debug_level > 0) THEN
1064 FND_FLEX_SERVER1.add_debug('Column ' || colname || ':' || coltype ||
1065 ' = (' || niceval || ') ');
1066 END IF;
1067 val := niceval;
1068
1069 <<done_return>>
1070
1071 return(TRUE);
1072
1073 EXCEPTION
1074 WHEN OTHERS then
1075 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1076 FND_MESSAGE.set_token('MSG','get_column_value() exception: ' || SQLERRM);
1077 return(FALSE);
1078
1079 END get_column_value;
1080
1081 /* ----------------------------------------------------------------------- */
1082 /* Validates all segments for a given descriptive flexfield. */
1083 /* Includes segments from the global context, the context segment */
1084 /* and the segments for the particular context. */
1085 /* Input all segments in an array. */
1086 /* Returns error code and sets FND_MESSAGE on error, or returns */
1090 FUNCTION
1087 /* VV_VALID if all ok. */
1088 /* ----------------------------------------------------------------------- */
1089
1091 validate_descsegs(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
1092 nsegs_in IN NUMBER,
1093 segs IN FND_FLEX_SERVER1.StringArray,
1094 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
1095 v_date IN DATE,
1096 uappid IN NUMBER,
1097 respid IN NUMBER,
1098 nsegs_out OUT nocopy NUMBER,
1099 segfmts OUT nocopy FND_FLEX_SERVER1.SegFormats,
1100 segstats OUT nocopy VARCHAR2,
1101 cols OUT nocopy FND_FLEX_SERVER1.TabColArray,
1102 coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
1103 v_dispvals OUT nocopy FND_FLEX_SERVER1.ValueArray,
1104 v_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
1105 v_ids OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
1106 v_descs OUT nocopy FND_FLEX_SERVER1.ValueDescArray,
1107 desc_lens OUT nocopy FND_FLEX_SERVER1.NumberArray,
1108 dispsegs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs,
1109 errsegn OUT nocopy NUMBER) RETURN NUMBER IS
1110
1111 -- Remember to check all sizes!
1112 --
1113
1114 global_context_name VARCHAR2(30);
1115
1116 context_segnum NUMBER;
1117 context_seg VARCHAR2(80);
1118
1119 context_segs_in FND_FLEX_SERVER1.StringArray;
1120 ncontext_segs_in NUMBER;
1121
1122 global_segs ValidatedSegmentArray;
1123 context_segment ValidatedSegmentArray;
1124 context_segs ValidatedSegmentArray;
1125
1126 global_vflags FND_FLEX_SERVER1.ValueValidationFlags;
1127 global_error_segnum NUMBER;
1128 global_error_msg VARCHAR2(2000);
1129 global_return_code NUMBER;
1130 error_segnum NUMBER;
1131 return_code NUMBER;
1132
1133 BEGIN
1134 IF (fnd_flex_server1.g_debug_level > 0) THEN
1135 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1136 'BEGIN SV4.validate_descsegs() ');
1137 END IF;
1138
1139 -- Initialize all returned values and all derived values with defaults
1140 --
1141 nsegs_out := 0;
1142 dispsegs.n_segflags := 0;
1143 segfmts.nsegs := 0;
1144
1145 -- Initialize segment arrays to 0 segments in case of error.
1146 --
1147 initialize_vsegarray(global_segs);
1148 initialize_vsegarray(context_segment);
1149 initialize_vsegarray(context_segs);
1150
1151 -- Return name of global context.
1152 --
1153 if(get_global_context(dff_info.application_id, dff_info.name,
1154 global_context_name) = FALSE) then
1155 return_code := FND_FLEX_SERVER1.VV_ERROR;
1156 goto return_values;
1157 end if;
1158
1159 -- Global context cannot require exact segs because we dont know
1160 -- how many segments are in the global structure beforehand.
1161 -- Make up a set of value validation flags like those input but which
1162 -- allow extra segments.
1163 --
1164 global_vflags := vflags;
1165 global_vflags.exact_nsegs_required := FALSE;
1166
1167 -- Validate global context segments
1168 --
1169 IF (fnd_flex_server1.g_debug_level > 0) THEN
1170 FND_FLEX_SERVER1.add_debug(' Global segments: ');
1171 END IF;
1172 global_return_code := validate_context_segs(dff_info, global_context_name,
1173 nsegs_in, segs, global_vflags, v_date, uappid,
1174 respid, global_segs, global_error_segnum);
1175
1176 -- Quit only if invalid and the error requires stopping. If error does
1177 -- not require stopping, save error message so it doesn't get
1178 -- overwritten by context validation.
1179 --
1180 if(global_return_code <> FND_FLEX_SERVER1.VV_VALID) then
1181 if(global_error_segnum is not null) then
1182 global_error_msg := FND_MESSAGE.GET_ENCODED;
1183 end if;
1184 if(not (((global_return_code = FND_FLEX_SERVER1.VV_SECURED) and
1185 (not global_vflags.stop_on_security)) OR
1186 ((global_return_code = FND_FLEX_SERVER1.VV_VALUES) and
1187 (not global_vflags.stop_on_value_error)))) then
1188 goto return_values;
1189 end if;
1190 end if;
1191
1192 -- Determine which segment is the context segment.
1193 -- If IDs input all segments are displayed so the context segment is
1194 -- just global_segs.nvalidated + 1. However, if VALUES are input,
1195 -- then input segs are only the displayed segments, so have to
1196 -- count the number of displayed segments in the global context
1197 -- to determine the number of the context segment.
1198 --
1199 -- Bug 1459072: There is no need to validate the context segment if one
1200 -- does not exist.
1201
1202 IF dff_info.context_override = 'N'
1203 AND dff_info.context_required = 'N'
1204 AND dff_info.default_context IS NULL
1205 AND dff_info.reference_field IS NULL
1206 -- Bug#4220582, to enforce validation checking even when ids are passed and not values.
1207 AND vflags.values_not_ids = TRUE then
1208 context_seg := NULL;
1209 return_code := FND_FLEX_SERVER1.VV_VALID;
1210 ELSE
1211 if(vflags.values_not_ids) then
1212 context_segnum := 1;
1213 for i in 1..global_segs.dispsegs.n_segflags loop
1217 end loop;
1214 if(global_segs.dispsegs.segflags(i)) then
1215 context_segnum := context_segnum + 1;
1216 end if;
1218 else
1219 context_segnum := global_segs.nvalidated + 1;
1220 end if;
1221
1222 -- If ids passed in, or if context field displayed, then get context
1223 -- from input segment array. Otherwise treat it as null and let it
1224 -- get defaulted if necessary.
1225 -- Set context_segnum to the first context-sensitive segment.
1226 --
1227 -- PROBLEM: IF CONTEXT field not displayed, but defaulted using the
1228 -- reference field mechanism, then the context value won't get passed
1229 -- in and the default value will not be available on the server.
1230 --
1231 -- if the context segment is set, then use it.
1232 if((not vflags.values_not_ids) or
1233 (dff_info.context_override = 'Y') OR
1234 (context_segnum <= nsegs_in AND
1235 segs(context_segnum) IS NOT NULL)) then
1236 if(nsegs_in < context_segnum) then
1237 if(vflags.exact_nsegs_required and
1238 not ((nsegs_in = 0) and (context_segnum = 1))) then
1239 FND_MESSAGE.set_name('FND', 'FLEX-MISSING CONCAT VALUES');
1240 error_segnum := global_segs.nvalidated + 1;
1241 return_code := FND_FLEX_SERVER1.VV_ERROR;
1242 goto return_values;
1243 else
1244 context_seg := NULL;
1245 end if;
1246 else
1247 context_seg := SUBSTRB(segs(context_segnum), 1, 80);
1248 end if;
1249 IF (fnd_flex_server1.g_debug_level > 0) THEN
1250 FND_FLEX_SERVER1.add_debug(' Context seg '||to_char(context_segnum));
1251 FND_FLEX_SERVER1.add_debug(' = (' || context_seg || ') ');
1252 END IF;
1253 --
1254 -- add 1. Beginning index for context sens. segs.
1255 --
1256 context_segnum := context_segnum + 1;
1257 end if;
1258
1259 IF (fnd_flex_server1.g_debug_level > 0) THEN
1260 FND_FLEX_SERVER1.add_debug('Context SEG#:' || To_char(context_segnum));
1261 END IF;
1262 -- Validate context segment. Default it if it's null and defaults
1263 -- are required.
1264 --
1265 return_code := validate_context(dff_info, context_seg, vflags,
1266 context_segment);
1267
1268 -- If context field is valid, validate context. Pass in only context segs.
1269 --
1270 if((return_code <> FND_FLEX_SERVER1.VV_VALID) and
1271 (return_code <> FND_FLEX_SERVER1.VV_CTXTNOSEG)) then
1272 error_segnum := global_segs.nvalidated + 1;
1273 else
1274 ncontext_segs_in := 0;
1275 for i in context_segnum..nsegs_in loop
1276 ncontext_segs_in := ncontext_segs_in + 1;
1277 context_segs_in(ncontext_segs_in) := segs(i);
1278 end loop;
1279 IF (fnd_flex_server1.g_debug_level > 0) THEN
1280 FND_FLEX_SERVER1.add_debug(' Context-sensitive segments: ');
1281 END IF;
1282 IF (context_segment.ids(1) is NOT NULL) THEN
1283 return_code := validate_context_segs(dff_info, context_segment.ids(1),
1284 ncontext_segs_in, context_segs_in, vflags, v_date,
1285 uappid, respid, context_segs, error_segnum);
1286 END IF;
1287 if(error_segnum is not null) then
1288 error_segnum := error_segnum + global_segs.nvalidated + 1;
1289 end if;
1290 end if;
1291 END IF;
1292
1293 <<return_values>>
1294
1295 -- Join global segments, context segment and context-senstive segments
1296 -- for output.
1297 --
1298 if((append_vsegarray(global_segs, context_segment) = FALSE) or
1299 (append_vsegarray(global_segs, context_segs) = FALSE)) then
1300 return(FND_FLEX_SERVER1.VV_ERROR);
1301 end if;
1302
1303 -- Return all the segment info
1304 --
1305 nsegs_out := global_segs.nvalidated;
1306 segfmts := global_segs.segfmts;
1307 segstats := global_segs.segstats;
1308 cols := global_segs.segcols;
1309 coltypes := global_segs.segcoltypes;
1310 v_dispvals := global_segs.dispvals;
1311 v_vals := global_segs.vals;
1312 v_ids := global_segs.ids;
1313 v_descs := global_segs.descs;
1314 desc_lens := global_segs.catdesclens;
1315 dispsegs := global_segs.dispsegs;
1316
1317 -- Prioritize errors and return code.
1318 -- If error in global segments worse than that in context or
1319 -- context-sensitive segments then use global error code, segnum and message.
1320
1321 -- Return context return code, error message and error segment by default.
1322 --
1323 errsegn := error_segnum;
1324
1325 if(global_return_code is not null) then
1326 if(global_return_code = FND_FLEX_SERVER1.VV_VALID) then
1327 null;
1328 elsif(global_return_code = FND_FLEX_SERVER1.VV_SECURED) then
1329 if((return_code is null) or
1330 (return_code = FND_FLEX_SERVER1.VV_VALID) or
1331 (return_code = FND_FLEX_SERVER1.VV_SECURED)) then
1332 goto return_global_error;
1333 end if;
1334 elsif(global_return_code = FND_FLEX_SERVER1.VV_VALUES) then
1335 if((return_code is null) or
1336 (return_code = FND_FLEX_SERVER1.VV_VALID) or
1337 (return_code = FND_FLEX_SERVER1.VV_SECURED) or
1338 (return_code = FND_FLEX_SERVER1.VV_VALUES)) then
1339 goto return_global_error;
1340 end if;
1341 else
1342 goto return_global_error;
1343 end if;
1344 end if;
1345
1346 if(return_code is null) then
1350
1347 return_code := FND_FLEX_SERVER1.VV_ERROR;
1348 end if;
1349 return(return_code);
1351 <<return_global_error>>
1352 errsegn := global_error_segnum;
1353 FND_MESSAGE.SET_ENCODED(global_error_msg);
1354 return(global_return_code);
1355
1356 EXCEPTION
1357 WHEN OTHERS then
1358 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1359 FND_MESSAGE.set_token('MSG','validate_descsegs() exception: '||SQLERRM);
1360 return(FND_FLEX_SERVER1.VV_ERROR);
1361
1362 END validate_descsegs;
1363
1364 /* ----------------------------------------------------------------------- */
1365 /* Validates the context-sensitive segments of the descriptive */
1366 /* for the context of the given name returning a validated context */
1367 /* structure and error segment number relative to this context */
1368 /* as output. If context name is NULL, returns a valid context with */
1369 /* 0 segments. Returns error code and sets FND_MESSAGE with error */
1370 /* or returns FND_FLEX_SERVER1.VV_VALID if ok. */
1371 /* ----------------------------------------------------------------------- */
1372
1373 FUNCTION
1374 validate_context_segs(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
1375 contxt_name IN VARCHAR2,
1376 nsegs IN NUMBER,
1377 segs IN FND_FLEX_SERVER1.StringArray,
1378 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
1379 vdate IN DATE,
1380 uappid IN NUMBER,
1381 respid IN NUMBER,
1382 vsa OUT nocopy ValidatedSegmentArray,
1383 errsegnum OUT nocopy NUMBER) RETURN NUMBER IS
1384
1385 f_struct FND_FLEX_SERVER1.FlexStructId;
1386 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
1387 no_vrules FND_FLEX_SERVER1.Vrules;
1388 no_dvals FND_FLEX_SERVER1.DerivedVals;
1389 no_dquals FND_FLEX_SERVER1.Qualifiers;
1390
1391 l_nsegs_out NUMBER;
1392 l_segfmts FND_FLEX_SERVER1.segformats;
1393 l_segstats VARCHAR2(201);
1394 l_tabcols FND_FLEX_SERVER1.tabcolarray;
1395 l_tabcoltypes FND_FLEX_SERVER1.chararray;
1396 l_v_dispvals FND_FLEX_SERVER1.valuearray;
1397 l_v_vals FND_FLEX_SERVER1.valuearray;
1398 l_v_ids FND_FLEX_SERVER1.valueidarray;
1399 l_v_descs FND_FLEX_SERVER1.valuedescarray;
1400 l_desc_lens FND_FLEX_SERVER1.numberarray;
1401 l_errsegn NUMBER;
1402 l_ret_code NUMBER;
1403 BEGIN
1404 IF (fnd_flex_server1.g_debug_level > 0) THEN
1405 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1406 'BEGIN SV4.validate_context_segs() ');
1407 END IF;
1408
1409
1410 -- Initialize returned ValidatedSegmentArray to no segments.
1411 --
1412 initialize_vsegarray(vsa);
1413
1414 -- Return a null ValidatedSegmentArray if no context name.
1415 --
1416 if(contxt_name is null) then
1417 return(FND_FLEX_SERVER1.VV_VALID);
1418 end if;
1419
1420 -- Set up flex structure
1421 --
1422 f_struct.isa_key_flexfield := FALSE;
1423 f_struct.application_id := dff_info.application_id;
1424 f_struct.desc_flex_name := dff_info.name;
1425 f_struct.desc_flex_context := contxt_name;
1426
1427 -- Set up dummy vrules. No vrules for descriptive flexfields
1428 --
1429 no_vrules.nvrules := 0;
1430
1431 -- Determine displayed segments for this context
1432 --
1433 if(FND_FLEX_SERVER.parse_displayed(f_struct, 'ALL', disp_segs)) then
1434 l_ret_code := FND_FLEX_SERVER1.validate_struct
1435 (f_struct, dff_info.table_appl_id,
1436 dff_info.table_id, nsegs, segs, disp_segs, vflags,
1437 vdate, no_vrules, uappid, respid,
1438 l_nsegs_out, l_segfmts, l_segstats, l_tabcols,
1439 l_tabcoltypes, l_v_dispvals, l_v_vals,
1440 l_v_ids, l_v_descs, l_desc_lens,
1441 no_dvals, no_dquals, l_errsegn);
1442
1443 vsa.nvalidated := l_nsegs_out;
1444 vsa.segfmts := l_segfmts;
1445 vsa.segstats := l_segstats;
1446 vsa.segcols := l_tabcols;
1447 vsa.segcoltypes := l_tabcoltypes;
1448 vsa.dispvals := l_v_dispvals;
1449 vsa.vals := l_v_vals;
1450 vsa.ids := l_v_ids;
1451 vsa.descs := l_v_descs;
1452 vsa.catdesclens := l_desc_lens;
1453 errsegnum := l_errsegn;
1454 vsa.dispsegs := disp_segs;
1455 RETURN(l_ret_code);
1456 end if;
1457 return(FND_FLEX_SERVER1.VV_ERROR);
1458
1459 EXCEPTION
1460 WHEN OTHERS then
1461 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1462 FND_MESSAGE.set_token('MSG', 'validate_context_segs() exception: '
1463 || SQLERRM);
1464 return(FND_FLEX_SERVER1.VV_ERROR);
1465
1466 END validate_context_segs;
1467
1468 /* ----------------------------------------------------------------------- */
1469 /* Validates the context value for the specified descriptive */
1470 /* flexfield. Returns an error code and sets FND_MESSAGE on error */
1471 /* or returns FND_FLEX_SERVER1.VV_VALID and a ValidatedSegmentArray */
1472 /* with only one segment if valid. */
1473 /* Note: Unlike regular value validation, validation must always */
1474 /* stop if the context segment is invalid because the remaining */
1475 /* segments all depend on it. */
1476 /* ----------------------------------------------------------------------- */
1477
1478 FUNCTION
1482 vsa OUT nocopy ValidatedSegmentArray)
1479 validate_context(dff_info IN FND_FLEX_SERVER1.DescFlexInfo,
1480 context_sval IN VARCHAR2,
1481 vflags IN FND_FLEX_SERVER1.ValueValidationFlags,
1483 RETURN NUMBER IS
1484
1485 context_segval VARCHAR2(80);
1486 context_id VARCHAR2(30);
1487 context_val VARCHAR2(80);
1488 context_description VARCHAR2(240);
1489 context_displayed BOOLEAN;
1490 vcode VARCHAR2(1);
1491 l_id_or_value VARCHAR2(10);
1492 l_max_lengthb NUMBER;
1493 l_global_flag VARCHAR2(10);
1494 vset FND_VSET.valueset_r;
1495 fmt FND_VSET.valueset_dr;
1496 c_found BOOLEAN;
1497 c_row NUMBER;
1498 c_value FND_VSET.value_dr;
1499
1500 BEGIN
1501 IF (fnd_flex_server1.g_debug_level > 0) THEN
1502 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1503 'BEGIN SV4.validate_context() ');
1504 END IF;
1505
1506
1507 -- Prepare to return error in case of exception
1508 --
1509 context_displayed := (dff_info.context_override = 'Y');
1510
1511 vsa.nvalidated := 1;
1512 vsa.segstats := FND_FLEX_SERVER1.FF_VERROR;
1513 vsa.segfmts.nsegs := 1;
1514 vsa.segfmts.vs_format(1) := 'C';
1515 vsa.segfmts.vs_maxsize(1) := 30;
1516 vsa.segcols(1) := dff_info.context_column;
1517 vsa.segcoltypes(1) := 'V';
1518 vsa.dispvals(1) := context_sval;
1519 vsa.vals(1) := context_sval;
1520 vsa.ids(1) := context_sval;
1521 vsa.descs(1) := NULL;
1522 vsa.catdesclens(1) := 240;
1523 vsa.dispsegs.n_segflags := 1;
1524 vsa.dispsegs.segflags(1) := context_displayed;
1525
1526 IF (fnd_flex_server1.g_debug_level > 0) THEN
1527 FND_FLEX_SERVER1.add_debug('CONTEXT:'||context_sval);
1528 END IF;
1529
1530 -- Strip whitespace from around context value only if its a value
1531 --
1532 if(vflags.values_not_ids) THEN
1533 l_id_or_value := 'V';
1534 l_max_lengthb := 80;
1535 context_segval := SUBSTRB(LTRIM(RTRIM(context_sval)), 1, l_max_lengthb);
1536 ELSE
1537 l_id_or_value := 'I';
1538 l_max_lengthb := 30;
1539 context_segval := SUBSTRB(context_sval, 1, l_max_lengthb);
1540 end if;
1541
1542 if((context_segval is not null) and
1543 (LENGTHB(context_segval) > l_max_lengthb)) then
1544 FND_MESSAGE.set_name('FND', 'FLEX-VALUE TOO LONG');
1545 FND_MESSAGE.set_token('VALUE', context_segval || '...');
1546 FND_MESSAGE.set_token('LENGTH', to_char(l_max_lengthb));
1547 vcode := FND_FLEX_SERVER1.FF_VFORMAT;
1548 goto return_status;
1549 end if;
1550
1551 -- Default the context if necessary
1552 --
1553 if((context_segval is null) and
1554 ((context_displayed and vflags.default_all_displayed) or
1555 ((dff_info.context_required = 'Y') and vflags.default_all_required) or
1556 ((dff_info.context_required = 'Y') and (not context_displayed) and
1557 vflags.default_non_displayed))) then
1558 context_segval := dff_info.default_context;
1559 IF (fnd_flex_server1.g_debug_level > 0) THEN
1560 FND_FLEX_SERVER1.add_debug('Defaulted context segment to '
1561 || context_segval);
1562 END IF;
1563 end if;
1564
1565 IF (context_segval IS NOT NULL) THEN
1566 vcode := find_context_value(dff_info.application_id, dff_info.name,
1567 l_id_or_value, context_segval, context_id,
1568 context_val, context_description, l_global_flag);
1569 if (vcode = FND_FLEX_SERVER1.FF_CTXTNOSEG) THEN
1570 IF (dff_info.context_override_value_set_id IS NOT NULL) THEN
1571 fnd_vset.get_valueset(dff_info.context_override_value_set_id, vset, fmt);
1572 fnd_vset.get_value_init(vset, TRUE);
1573 fnd_vset.get_value(vset, c_row, c_found, c_value);
1574 WHILE(c_found) LOOP
1575 IF (context_segval = c_value.value) THEN
1576 vcode := FND_FLEX_SERVER1.FF_VVALID;
1577 EXIT;
1578 END IF;
1579 fnd_vset.get_value(vset, c_row, c_found, c_value);
1580 END LOOP;
1581 fnd_vset.get_value_end(vset);
1582 IF (c_found = FALSE) THEN
1583 vcode:= FND_FLEX_SERVER1.FF_VNOTFOUND;
1584 END IF;
1585 END IF;
1586
1587 END IF;
1588 --
1589 -- Some developers set Global as regular context.
1590 --
1591 IF (l_global_flag = 'Y') THEN
1592 IF (fnd_flex_server1.g_debug_level > 0) THEN
1593 FND_FLEX_SERVER1.add_debug('Setting context to NULL, Global Data Elements is not a context.');
1594 END IF;
1595 context_id := NULL;
1596 context_val := NULL;
1597 context_description := NULL;
1598 END IF;
1599 END IF;
1600
1601 -- If value still null its an error if required, or valid if not.
1602 --
1603
1604 if(context_segval is null) then
1605 if((dff_info.context_required = 'N') or (vflags.allow_nulls)) then
1606 vcode := FND_FLEX_SERVER1.FF_VVALID;
1607 else
1608 vcode := FND_FLEX_SERVER1.FF_VREQUIRED;
1609 if(vflags.message_on_null) then
1610 FND_MESSAGE.set_name('FND', 'FLEX-MISSING CONTEXT VALUE');
1611 FND_MESSAGE.set_token('FLEXFIELD', dff_info.name);
1612 -- FND_MESSAGE.set_name('FND', 'FLEX-NULL SEGMENT');
1613 end if;
1614 end if;
1615 end if;
1616
1620 vsa.segstats := vcode;
1617 <<return_status>>
1618 -- Return vcode as the segment status
1619 --
1621
1622 -- Pretend context field is a non-validated character value set
1623 -- I assume context field must be on a VARCHAR2 type column.
1624 --
1625 if(vcode = FND_FLEX_SERVER1.FF_VVALID) then
1626 vsa.dispvals(1) := context_val;
1627 vsa.vals(1) := context_val;
1628 vsa.ids(1) := context_id;
1629 vsa.descs(1) := context_description;
1630 return(FND_FLEX_SERVER1.VV_VALID);
1631 elsif(vcode = FND_FLEX_SERVER1.FF_VERROR) then
1632 return(FND_FLEX_SERVER1.VV_ERROR);
1633 else
1634 FND_MESSAGE.set_name('FND', 'FLEX-CONTEXT NOT FOUND');
1635 FND_MESSAGE.set_token('VALUE', context_segval);
1636 FND_MESSAGE.set_token('DFF', dff_info.name);
1637 return(FND_FLEX_SERVER1.VV_VALUES);
1638 end if;
1639
1640 return(FND_FLEX_SERVER1.VV_ERROR);
1641
1642 EXCEPTION
1643 WHEN OTHERS then
1644 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1645 FND_MESSAGE.set_token('MSG', 'validate_context() exception: '||SQLERRM);
1646 return(FND_FLEX_SERVER1.VV_ERROR);
1647
1648 END validate_context;
1649
1650 /* ----------------------------------------------------------------------- */
1651 /* Appends segments from one ValidatedSegmentArray to another. */
1652 /* Returns TRUE if OK, or FALSE if any errors. */
1653 /* ----------------------------------------------------------------------- */
1654
1655 FUNCTION append_vsegarray(destvsa IN OUT nocopy ValidatedSegmentArray,
1656 sourcevsa IN ValidatedSegmentArray)
1657 RETURN BOOLEAN IS
1658 n NUMBER;
1659
1660 BEGIN
1661 IF (fnd_flex_server1.g_debug_level > 0) THEN
1662 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1663 'BEGIN SV4.append_vsegarray() ');
1664 END IF;
1665
1666 n := destvsa.nvalidated;
1667 for i in 1..sourcevsa.nvalidated loop
1668 n := n + 1;
1669 destvsa.segfmts.vs_format(n) := sourcevsa.segfmts.vs_format(i);
1670 destvsa.segfmts.vs_maxsize(n) := sourcevsa.segfmts.vs_maxsize(i);
1671 destvsa.segcols(n) := sourcevsa.segcols(i);
1672 destvsa.segcoltypes(n) := sourcevsa.segcoltypes(i);
1673 destvsa.dispvals(n) := sourcevsa.dispvals(i);
1674 destvsa.vals(n) := sourcevsa.vals(i);
1675 destvsa.ids(n) := sourcevsa.ids(i);
1676 destvsa.descs(n) := sourcevsa.descs(i);
1677 destvsa.catdesclens(n) := sourcevsa.catdesclens(i);
1678 destvsa.dispsegs.segflags(n) := sourcevsa.dispsegs.segflags(i);
1679 end loop;
1680
1681 destvsa.nvalidated := n;
1682 destvsa.segfmts.nsegs := n;
1683 destvsa.dispsegs.n_segflags := n;
1684 destvsa.segstats := destvsa.segstats || sourcevsa.segstats;
1685
1686 return(TRUE);
1687
1688 EXCEPTION
1689 WHEN OTHERS then
1690 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1691 FND_MESSAGE.set_token('MSG','append_vsegarray() exception: '||SQLERRM);
1692 return(FALSE);
1693
1694 END append_vsegarray;
1695
1696 /* ----------------------------------------------------------------------- */
1697 /* Initializes ValidatedSegmentArray. */
1698 /* ----------------------------------------------------------------------- */
1699
1700 PROCEDURE initialize_vsegarray(v_seg_array OUT nocopy ValidatedSegmentArray) IS
1701 BEGIN
1702 v_seg_array.nvalidated := 0;
1703 v_seg_array.segfmts.nsegs := 0;
1704 v_seg_array.dispsegs.n_segflags := 0;
1705 END initialize_vsegarray;
1706
1707 /* ----------------------------------------------------------------------- */
1708 /* Initializes ColumnDefinitions. */
1709 /* ----------------------------------------------------------------------- */
1710
1711 PROCEDURE init_coldef(column_defn OUT nocopy FND_FLEX_SERVER1.ColumnDefinitions) IS
1712 BEGIN
1713 column_defn.context_value_set := FALSE;
1714 column_defn.context_value := NULL;
1715 init_colvals(column_defn.colvals);
1716 END init_coldef;
1717
1718 /* ----------------------------------------------------------------------- */
1719 /* Initializes ColumnValues. */
1720 /* ----------------------------------------------------------------------- */
1721
1722 PROCEDURE init_colvals(column_vals OUT nocopy FND_FLEX_SERVER1.ColumnValues) IS
1723 BEGIN
1724 column_vals.ncolumns := 0;
1725 END init_colvals;
1726
1727 /* ----------------------------------------------------------------------- */
1728 /* Finds enabled context value and description from context segment. */
1729 /* Only considers enabled contexts. */
1730 /* */
1731 /* In future we may support: */
1732 /* If no context is found that exactly matches the context segment */
1733 /* input, then a case-insensitive match is done on values that start */
1734 /* with the context segment input. If only one value matches that, */
1735 /* that context is returned. Otherwise the context is not found. */
1736 /* */
1737 /* Returns value validation code FND_FLEX_SERVER1.FF_VALID if ok. */
1738 /* Otherwise sets error message in FND_MESSAGE and returns */
1739 /* FF_VVALUES if not found or FF_VERROR on error. */
1740 /* ----------------------------------------------------------------------- */
1741
1742 FUNCTION find_context_value(appl_id IN VARCHAR2,
1746 context_id OUT nocopy VARCHAR2,
1743 dflex_name IN VARCHAR2,
1744 p_id_or_value IN VARCHAR2,
1745 seg_in IN VARCHAR2,
1747 context_val OUT nocopy VARCHAR2,
1748 context_desc OUT nocopy VARCHAR2,
1749 p_global_flag OUT nocopy VARCHAR2) RETURN VARCHAR2 IS
1750
1751 vcode VARCHAR2(1);
1752
1753 BEGIN
1754 IF (fnd_flex_server1.g_debug_level > 0) THEN
1755 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1756 'BEGIN SV4.find_context_value(' ||
1757 p_id_or_value || ',' || seg_in ||') ');
1758 END IF;
1759
1760 g_cache_key := (appl_id || '.' || dflex_name || '.' ||
1761 p_id_or_value || '.' || seg_in);
1762 fnd_plsql_cache.generic_1to1_get_value(cxc_cache_controller,
1763 cxc_cache_storage,
1764 g_cache_key,
1765 g_cache_value,
1766 g_cache_return_code);
1767 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1768 NULL;
1769 ELSE
1770 SELECT
1771 descriptive_flex_context_code,
1772 descriptive_flex_context_name,
1773 description,
1774 global_flag
1775 INTO
1776 g_cache_value.varchar2_1,
1777 g_cache_value.varchar2_2,
1778 g_cache_value.varchar2_3,
1779 g_cache_value.varchar2_4
1780 FROM fnd_descr_flex_contexts_vl
1781 WHERE application_id = appl_id
1782 AND descriptive_flexfield_name = dflex_name
1783 AND ((p_id_or_value = 'I' AND
1784 descriptive_flex_context_code = seg_in) OR
1785 (p_id_or_value = 'V' AND
1786 descriptive_flex_context_name = seg_in))
1787 AND enabled_flag = 'Y';
1788
1789 fnd_plsql_cache.generic_1to1_put_value(cxc_cache_controller,
1790 cxc_cache_storage,
1791 g_cache_key,
1792 g_cache_value);
1793 END IF;
1794
1795 context_id := g_cache_value.varchar2_1;
1796 context_val := g_cache_value.varchar2_2;
1797 context_desc := g_cache_value.varchar2_3;
1798 p_global_flag := g_cache_value.varchar2_4;
1799
1800 return(FND_FLEX_SERVER1.FF_VVALID);
1801
1802 EXCEPTION
1803 WHEN NO_DATA_FOUND then
1804 ---- vcode := context_vs_validation();
1805 -- context_id := seg_in;
1806 -- context_val := seg_in;
1807 -- context_desc := 'Dummy';
1808 -- p_global_flag := 'X';
1809 vcode := FND_FLEX_SERVER1.FF_CTXTNOSEG;
1810 -- IF(vcode = FND_FLEX_SERVER1.FF_CTXTNOSEG) THEN
1811 return(FND_FLEX_SERVER1.FF_CTXTNOSEG);
1812 -- ELSE
1813 -- FND_MESSAGE.set_name('FND', 'FLEX-CONTEXT NOT FOUND');
1814 -- FND_MESSAGE.set_token('VALUE', seg_in);
1815 -- FND_MESSAGE.set_token('DFF', dflex_name);
1816 -- return(FND_FLEX_SERVER1.FF_VNOTFOUND);
1817 -- END IF;
1818 WHEN OTHERS then
1819 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1820 FND_MESSAGE.set_token('MSG','find_context_value() exception: '||SQLERRM);
1821 return(FND_FLEX_SERVER1.FF_VERROR);
1822
1823 END find_context_value;
1824
1825 /* ----------------------------------------------------------------------- */
1826 /* Gets the name of the global context for the specified flexfield. */
1827 /* Error if the global context name is not enabled. */
1828 /* Returns TRUE if OK or FALSE and sets FND_MESSAGE if error. */
1829 /* ----------------------------------------------------------------------- */
1830
1831 FUNCTION get_global_context(appl_id IN NUMBER,
1832 dflex_name IN VARCHAR2,
1833 glob_context OUT nocopy VARCHAR2) RETURN BOOLEAN IS
1834 BEGIN
1835 IF (fnd_flex_server1.g_debug_level > 0) THEN
1836 FND_FLEX_SERVER1.add_debug(fnd_global.newline ||
1837 'BEGIN SV4.get_global_context() ');
1838 END IF;
1839
1840 g_cache_key := appl_id || '.' || dflex_name;
1841 fnd_plsql_cache.generic_1to1_get_value(gcc_cache_controller,
1842 gcc_cache_storage,
1843 g_cache_key,
1844 g_cache_value,
1845 g_cache_return_code);
1846 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1847 NULL;
1848 ELSE
1849 SELECT
1850 descriptive_flex_context_code
1851 INTO
1852 g_cache_value.varchar2_1
1853 FROM fnd_descr_flex_contexts
1854 WHERE application_id = appl_id
1855 AND descriptive_flexfield_name = dflex_name
1856 AND enabled_flag = 'Y'
1857 AND global_flag = 'Y';
1858
1859 fnd_plsql_cache.generic_1to1_put_value(gcc_cache_controller,
1860 gcc_cache_storage,
1861 g_cache_key,
1862 g_cache_value);
1863 END IF;
1864
1865 glob_context := g_cache_value.varchar2_1;
1866
1867 return(TRUE);
1868
1869 EXCEPTION
1870 WHEN NO_DATA_FOUND then
1871 FND_MESSAGE.set_name('FND', 'FLEX-NO ENABLED GLOBAL CONTEXT');
1872 return(FALSE);
1873 WHEN TOO_MANY_ROWS then
1874 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE GLOBAL CONTEXTS');
1875 FND_MESSAGE.set_token('APID', appl_id);
1876 FND_MESSAGE.set_token('NAME', dflex_name);
1877 return(FALSE);
1878 WHEN OTHERS then
1879 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1880 FND_MESSAGE.set_token('MSG','get_global_context() exception: '||SQLERRM);
1881 return(FALSE);
1882 END get_global_context;
1883
1884 /* ----------------------------------------------------------------------- */
1885
1886 BEGIN
1887 fnd_plsql_cache.generic_1to1_init('SV4.CXC',
1888 cxc_cache_controller,
1889 cxc_cache_storage);
1890
1891 fnd_plsql_cache.generic_1to1_init('SV4.GCC',
1892 gcc_cache_controller,
1893 gcc_cache_storage);
1894 END fnd_flex_server4;