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