[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_SERVER
Source
1 PACKAGE BODY fnd_flex_server AS
2 /* $Header: AFFFSSVB.pls 120.28.12020000.2 2012/10/08 19:37:09 tebarnes ship $ */
3
4 --------
5 -- PRIVATE TYPES
6 --
7
8 TYPE ValattRqst IS RECORD
9 (nrqstd NUMBER,
10 fq_names FND_FLEX_SERVER1.QualNameArray,
11 sq_names FND_FLEX_SERVER1.QualNameArray);
12
13 TYPE DerivedRqst IS RECORD
14 (nrqstd NUMBER,
15 sq_names FND_FLEX_SERVER1.QualNameArray);
16
17
18 -- ======================================================================
19 -- Caching.
20 -- ======================================================================
21 g_cache_return_code VARCHAR2(30);
22 g_cache_key VARCHAR2(2000);
23 g_cache_index BINARY_INTEGER;
24 g_cache_value fnd_plsql_cache.generic_cache_value_type;
25 g_newline VARCHAR2(8);
26
27 -- --------------------------------------------------
28 -- vst : Value set cache.
29 -- --------------------------------------------------
30 TYPE vst_cache_storage_table_type IS TABLE OF fnd_flex_value_sets%ROWTYPE
31 INDEX BY BINARY_INTEGER;
32
33 vst_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
34 vst_cache_storage vst_cache_storage_table_type;
35
36 -- ======================================================================
37 -- Value Security Cache : VSC
38 --
39 -- PK: <application_id> || NEWLINE || <responsibility_id> || NEWLINE ||
40 -- <value_set_id> || NEWLINE || <parent_value> || NEWLINE ||
41 -- <value>
42 --
43 -- ======================================================================
44 vsc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
45 vsc_cache_storage fnd_plsql_cache.generic_cache_values_type;
46
47 -- --------------------------------------------------
48 -- Get KFV Concat Segs: Select Statement Cache
49 -- --------------------------------------------------
50 TYPE kfvssc_record_type IS RECORD
51 (compact_sql VARCHAR2(32000),
52 padded_sql VARCHAR2(32000),
53 set_defining_column_name fnd_id_flexs.set_defining_column_name%TYPE,
54 unique_id_column_name fnd_id_flexs.unique_id_column_name%TYPE);
55
56 TYPE kfvssc_table_type IS TABLE OF kfvssc_record_type INDEX BY BINARY_INTEGER;
57
58 kfvssc_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
59 kfvssc_cache_storage kfvssc_table_type;
60
61 -- --------------------------------------------------
62 -- Get KFV Concat Segs: Code Combination Table Info Cache
63 -- --------------------------------------------------
64 TYPE kfvcct_record_type IS RECORD
65 (table_application_id fnd_id_flexs.table_application_id%TYPE,
66 application_table_name fnd_id_flexs.application_table_name%TYPE,
67 table_id fnd_tables.table_id%TYPE,
68 set_defining_column_name fnd_id_flexs.set_defining_column_name%TYPE,
69 unique_id_column_name fnd_id_flexs.unique_id_column_name%TYPE);
70
71 TYPE kfvcct_table_type IS TABLE OF kfvcct_record_type INDEX BY BINARY_INTEGER;
72
73 kfvcct_cache_controller fnd_plsql_cache.cache_1to1_controller_type;
74 kfvcct_cache_storage kfvcct_table_type;
75
76 TYPE table_of_varchar2_32000 IS TABLE OF VARCHAR2(32000)
77 INDEX BY BINARY_INTEGER;
78
79 g_non_forms_warnings table_of_varchar2_32000;
80 g_non_forms_warnings_count NUMBER;
81
82 FLEX_PREFIX CONSTANT VARCHAR2(7) := '$FLEX$.';
83 FLEX_PREFIX_LEN CONSTANT NUMBER := 7;
84
85 PROFILE_PREFIX CONSTANT VARCHAR2(11) := '$PROFILES$.';
86 PROFILE_PREFIX_LEN CONSTANT NUMBER := 11;
87
88 FF_SEGMENT CONSTANT VARCHAR2(1) := 'S';
89 FF_PROFILE CONSTANT VARCHAR2(1) := 'P';
90 FF_FIELD CONSTANT VARCHAR2(1) := 'F';
91
92 FLEX_BIND_CHARS CONSTANT VARCHAR2(150) := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_$#.:*';
93
94 ------------
95 -- PRIVATE CONSTANTS
96 --
97 MAX_NSEGS CONSTANT NUMBER := 30;
98 MAX_CCID CONSTANT NUMBER := 2000000000;
99 NHASH CONSTANT NUMBER := 997;
100 DATE_PASS_FORMAT CONSTANT VARCHAR2(1) := 'J';
101 DRV_DATE_FMT CONSTANT VARCHAR2(9) := 'DD-MON-RR';
102 DATE_TEST_FMT CONSTANT VARCHAR2(11) := 'DD-MON-YYYY';
103 DATE_DEBUG_FMT CONSTANT VARCHAR2(9) := 'DD-MON-RR';
104 MAX_ARG_LEN CONSTANT NUMBER := 1950;
105 MAX_CATSEG_LEN CONSTANT NUMBER := 700;
106 BLANKS VARCHAR2(4); -- := FND_FLEX_SERVER1.WHITESPACE;
107 -------------
108 -- EXCEPTIONS
109 --
110
111 /* -------------------------------------------------------------------- */
112 /* Private global variables */
113 /* -------------------------------------------------------------------- */
114
115 -- SQL string for dynamic SQL processing
116 sqls VARCHAR2(30000);
117 g_debug_text VARCHAR2(2000);
118
119 -- Determines whether or not to call FDFGLI after inserting
120 -- new combination into accounting flexfield.
121 --
122 fdfgli_on BOOLEAN := TRUE;
123
124 -- Determines whether or not to call user validation function
125 -- FND_FLEX_SERVER.VALIDATE just before inserting a new combination.
126 --
127 userval_on BOOLEAN := TRUE;
128
129 --
130 -- ARCS Revision identifier for SQL statements
131 --
132 g_arcs_revision VARCHAR2(32000);
133
134 /* -------------------------------------------------------------------- */
135 /* Private definitions */
136 /* -------------------------------------------------------------------- */
137 FUNCTION insert_combination(fstruct IN FND_FLEX_SERVER1.FlexStructId,
138 structnum IN NUMBER,
139 maintmode IN BOOLEAN,
140 v_date IN DATE,
141 seg_delim IN VARCHAR2,
142 ccid_inp IN NUMBER,
143 combtbl IN FND_FLEX_SERVER1.CombTblInfo,
144 combcols IN FND_FLEX_SERVER1.TabColArray,
145 combtypes IN FND_FLEX_SERVER1.CharArray,
146 user_id IN NUMBER,
147 nsegs IN NUMBER,
148 segids_in IN FND_FLEX_SERVER1.ValueIdArray,
149 segfmts IN FND_FLEX_SERVER1.SegFormats,
150 dvalues IN FND_FLEX_SERVER1.DerivedVals,
151 dquals IN FND_FLEX_SERVER1.Qualifiers,
152 nxcols IN NUMBER,
153 xcolnames IN FND_FLEX_SERVER1.StringArray,
154 xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
155 qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
156 tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
157 newcomb OUT nocopy BOOLEAN,
158 ccid_out OUT nocopy NUMBER) RETURN BOOLEAN;
159
160 FUNCTION hash_segs(n IN NUMBER,
161 segs IN FND_FLEX_SERVER1.ValueIdArray) RETURN NUMBER;
162
163
164 FUNCTION call_userval(fstruct IN FND_FLEX_SERVER1.FlexStructId,
165 vdate IN DATE,
166 nids IN NUMBER,
167 delim IN VARCHAR2,
168 segids IN FND_FLEX_SERVER1.ValueIdArray)
169 RETURN BOOLEAN;
170
171 FUNCTION check_table_comb(t_dval IN FND_FLEX_SERVER1.DerivedVals,
172 t_quals IN FND_FLEX_SERVER1.Qualifiers,
173 v_rules IN FND_FLEX_SERVER1.Vrules,
174 v_date IN DATE,
175 check_effective IN BOOLEAN) RETURN NUMBER;
176
177
178 FUNCTION find_column_index(column_list IN FND_FLEX_SERVER1.TabColArray,
179 column_count IN NUMBER,
180 colname IN VARCHAR2) RETURN NUMBER;
181
182 FUNCTION concatenate_fulldescs(ndescs IN NUMBER,
183 descs IN FND_FLEX_SERVER1.ValueDescArray,
184 displ IN FND_FLEX_SERVER1.DisplayedSegs,
185 delimiter IN VARCHAR2) RETURN VARCHAR2;
186
187 FUNCTION concatenate_segment_formats(segfmts IN FND_FLEX_SERVER1.SegFormats)
188 RETURN VARCHAR2;
189
190 FUNCTION ret_derived(d_quals IN FND_FLEX_SERVER1.Qualifiers,
191 drv IN FND_FLEX_SERVER1.DerivedVals,
192 d_rqst IN DerivedRqst) RETURN VARCHAR2;
193
194 FUNCTION ret_valatts(d_quals IN FND_FLEX_SERVER1.Qualifiers,
195 drv IN FND_FLEX_SERVER1.DerivedVals,
196 v_rqst IN ValattRqst) RETURN VARCHAR2;
197
198 FUNCTION parse_va_rqst(s IN VARCHAR2, var OUT nocopy ValattRqst) RETURN NUMBER;
199
200 FUNCTION parse_drv_rqst(s IN VARCHAR2, dr OUT nocopy DerivedRqst) RETURN NUMBER;
201
202 FUNCTION parse_vrules(s IN VARCHAR2,
203 vr OUT nocopy FND_FLEX_SERVER1.Vrules) RETURN NUMBER;
204
205 FUNCTION parse_set_msg(p_msg IN VARCHAR2) RETURN BOOLEAN;
206
207 FUNCTION evaluate_token(token_str IN VARCHAR2,
208 n_segs IN NUMBER,
209 fq_tab IN FND_FLEX_SERVER1.FlexQualTable,
210 token_map OUT nocopy FND_FLEX_SERVER1.BooleanArray)
211 RETURN BOOLEAN;
212
213 FUNCTION call_fdfgli(ccid IN NUMBER) RETURN BOOLEAN;
214
215 /* ----------------------------------------------------------------------- */
216 /* Private Functions */
217 /* ----------------------------------------------------------------------- */
218
219 /* ----------------------------------------------------------------------- */
220 /* Validates flexfield concatenated segment array */
221 /* Returns 0 if invalid, 1 if valid. If valid ccid is id of */
222 /* code combination and message is null. If invalid ccid is 0 */
223 /* and message says why its invalid. */
224 /* ----------------------------------------------------------------------- */
225
226 FUNCTION v_comb(user_apid IN NUMBER,
227 user_resp IN NUMBER,
228 userid IN NUMBER,
229 flex_app_sname IN VARCHAR2,
230 flex_code IN VARCHAR2,
231 flex_num IN NUMBER,
232 vdate IN VARCHAR2,
233 vrulestr IN VARCHAR2,
234 data_set IN NUMBER,
235 invoking_mode IN VARCHAR2,
236 validate_mode IN VARCHAR2,
237 dinsert IN VARCHAR2,
238 qsecurity IN VARCHAR2,
239 required IN VARCHAR2,
240 allow_nulls IN VARCHAR2,
241 display_segs IN VARCHAR2,
242 concat_segs IN VARCHAR2,
243 vals_or_ids IN VARCHAR2,
244 where_clause IN VARCHAR2,
245 extra_cols IN VARCHAR2,
246 get_valatts IN VARCHAR2,
247 get_derived IN VARCHAR2,
248 ccid IN NUMBER) RETURN VARCHAR2 IS
249
250
251 cat_vals VARCHAR2(2000);
252 cat_ids VARCHAR2(2000);
253 cat_desc VARCHAR2(2000);
254 xtra_cols VARCHAR2(2000);
255 valatts VARCHAR2(2000);
256 derived VARCHAR2(2000);
257 valid_d VARCHAR2(30);
258 start_d VARCHAR2(30);
259 end_d VARCHAR2(30);
260 enab_flag VARCHAR2(1);
261 sum_flag VARCHAR2(1);
262 ccid_o NUMBER;
263 v_stat NUMBER;
264 s_stat VARCHAR2(30);
265 errseg NUMBER;
266 dlim VARCHAR2(1);
267 retmsg VARCHAR2(2000);
268 retstr VARCHAR2(15000);
269
270 BEGIN
271
272 valid_d := to_char(to_date(vdate, DATE_TEST_FMT), DATE_PASS_FORMAT);
273 validate_combination(user_apid, user_resp, userid, flex_app_sname,flex_code,
274 flex_num, valid_d, vrulestr, data_set, invoking_mode,
275 validate_mode, dinsert, qsecurity, required,
276 allow_nulls, display_segs, concat_segs, vals_or_ids,
277 cat_vals, cat_ids, cat_desc, where_clause,
278 extra_cols, xtra_cols, get_valatts, valatts,
279 get_derived, derived, start_d, end_d, enab_flag,
280 sum_flag, dlim, ccid, ccid_o, v_stat,
281 s_stat, errseg, retmsg,NULL,NULL,NULL,NULL);
282
283 start_d := to_char(to_date(start_d, DATE_PASS_FORMAT), DATE_TEST_FMT);
284 end_d := to_char(to_date(end_d, DATE_PASS_FORMAT), DATE_TEST_FMT);
285
286 -- Translate returned encoded message
287 --
288 FND_MESSAGE.set_encoded(retmsg);
289 retmsg := FND_MESSAGE.get;
290
291 retstr := 'VStatus: ' || to_char(v_stat);
292 retstr := retstr || ' CCID: ' || to_char(ccid_o);
293 retstr := retstr || ' Vals: ' || cat_vals;
294 retstr := retstr || ' Ids: ' || cat_ids;
295 retstr := retstr || ' Descs: ' || cat_desc;
296 retstr := retstr || ' ExtraCols: ' || xtra_cols;
297 retstr := retstr || ' ValAtts: ' || valatts;
298 retstr := retstr || ' Derived: ' || derived;
299 retstr := retstr || ' StartDate: ' || start_d;
300 retstr := retstr || ' EndDate: ' || end_d;
301 retstr := retstr || ' Enab: ' || enab_flag;
302 retstr := retstr || ' Summary: ' || sum_flag;
303 retstr := retstr || ' Delimiter: ' || dlim;
304 retstr := retstr || ' SegCodes: ' || s_stat;
305 retstr := retstr || ' ErrSeg: ' || to_char(errseg);
306 retstr := retstr || ' Msg: ' || retmsg;
307 retstr := retstr || ' Debug: ' || get_debug(1);
308
309 return(SUBSTRB(retstr, 1, 1950));
310
311 END v_comb;
312
313 /* ----------------------------------------------------------------------- */
314 /* Stub for testing validate_descflex() */
315 /* ----------------------------------------------------------------------- */
316
317 FUNCTION v_desc(user_apid IN NUMBER,
318 user_resp IN NUMBER,
319 userid IN NUMBER,
320 flex_app_sname IN VARCHAR2,
321 desc_flex_name IN VARCHAR2,
322 vdate IN VARCHAR2,
323 invoking_mode IN VARCHAR2,
324 allow_nulls IN VARCHAR2,
325 update_table IN VARCHAR2,
326 eff_activation IN VARCHAR2,
327 concat_segs IN VARCHAR2,
328 vals_or_ids IN VARCHAR2,
329 c_rowid IN VARCHAR2,
330 alternate_table IN VARCHAR2,
331 data_field IN VARCHAR2) RETURN VARCHAR2 IS
332
333 valid_d VARCHAR2(30);
334 cat_vals VARCHAR2(2000);
335 cat_ids VARCHAR2(2000);
336 cat_desc VARCHAR2(2000);
337 v_stat NUMBER;
338 s_stat VARCHAR2(30);
339 errseg NUMBER;
340 dlim VARCHAR2(1);
341 retmsg VARCHAR2(2000);
342 retstr VARCHAR2(15000);
343
344 BEGIN
345
346 valid_d := to_char(to_date(vdate, DATE_TEST_FMT), DATE_PASS_FORMAT);
347
348 validate_descflex(user_apid, user_resp, userid, flex_app_sname,
349 desc_flex_name, valid_d, invoking_mode, allow_nulls,
350 update_table, eff_activation, concat_segs, vals_or_ids,
351 c_rowid, alternate_table, data_field, cat_vals,
352 cat_ids, cat_desc, dlim, v_stat, s_stat, errseg, retmsg);
353
354 -- Translate returned encoded message
355 --
356 FND_MESSAGE.set_encoded(retmsg);
357 retmsg := FND_MESSAGE.get;
358
359 retstr := 'VStatus: ' || to_char(v_stat);
360 retstr := retstr || ' Vals: ' || cat_vals;
361 retstr := retstr || ' Ids: ' || cat_ids;
362 retstr := retstr || ' Descs: ' || cat_desc;
363 retstr := retstr || ' Delimiter: ' || dlim;
364 retstr := retstr || ' SegCodes: ' || s_stat;
365 retstr := retstr || ' ErrSeg: ' || to_char(errseg);
366 retstr := retstr || ' Msg: ' || retmsg;
367 retstr := retstr || ' Debug: ' || get_debug(1);
368
369 return(SUBSTRB(retstr, 1, 1950));
370
371 END v_desc;
372
373 /* ----------------------------------------------------------------------- */
374 /* Test function for debugging pre_window() */
375 /* ----------------------------------------------------------------------- */
376
377 FUNCTION p_win(user_apid IN NUMBER,
378 user_resp IN NUMBER,
379 flex_app_sname IN VARCHAR2,
380 flex_code IN VARCHAR2,
381 flex_num IN NUMBER,
382 vdate IN VARCHAR2,
383 vrulestr IN VARCHAR2,
384 display_segs IN VARCHAR2,
385 concat_segs IN VARCHAR2) RETURN VARCHAR2 IS
386
387 n_segs NUMBER;
388 errsegn NUMBER;
389 v_stat NUMBER;
390 valid_d VARCHAR2(30);
391 cat_vals VARCHAR2(2000);
392 cat_ids VARCHAR2(2000);
393 cat_desc VARCHAR2(2000);
394 retmsg VARCHAR2(2000);
395 retstr VARCHAR2(15000);
396 dlim VARCHAR2(1);
397 seg_fmts VARCHAR2(180);
398 seg_codes VARCHAR2(30);
399
400 BEGIN
401
402 valid_d := to_char(to_date(vdate, DATE_TEST_FMT), DATE_PASS_FORMAT);
403 pre_window(user_apid, user_resp, flex_app_sname, flex_code, flex_num,
404 valid_d, vrulestr, display_segs, concat_segs, cat_vals, cat_ids,
405 cat_desc, dlim, seg_fmts, seg_codes, n_segs, v_stat, errsegn,
406 retmsg);
407
408 -- Translate returned encoded message
409 --
410 FND_MESSAGE.set_encoded(retmsg);
411 retmsg := FND_MESSAGE.get;
412
413 retstr := 'VStatus: ' || to_char(v_stat);
414 retstr := retstr || ' Nsegs: ' || to_char(n_segs);
415 retstr := retstr || ' Vals: ' || cat_vals;
416 retstr := retstr || ' Ids: ' || cat_ids;
417 retstr := retstr || ' Descs: ' || cat_desc;
418 retstr := retstr || ' Delimiter: ' || dlim;
419 retstr := retstr || ' Formats: ' || seg_fmts;
420 retstr := retstr || ' SegCodes: ' || seg_codes;
421 retstr := retstr || ' ErrSeg: ' || to_char(errsegn);
422 retstr := retstr || ' Msg: ' || retmsg;
423 retstr := retstr || ' Debug: ' || get_debug(1);
424
425 return(SUBSTRB(retstr, 1, 1950));
426
427 END p_win;
428
429 /* ----------------------------------------------------------------------- */
430 /* Test function for debugging segment_maps() */
431 /* ----------------------------------------------------------------------- */
432
433 FUNCTION s_maps(flex_app_sname IN VARCHAR2,
434 flex_code IN VARCHAR2,
435 flex_num IN NUMBER,
436 insert_tok IN VARCHAR2,
437 update_tok IN VARCHAR2,
438 display_tok IN VARCHAR2) RETURN VARCHAR2 IS
439
440 n_segs NUMBER;
441 insrt_map VARCHAR2(30);
442 updat_map VARCHAR2(30);
443 dspl_map VARCHAR2(30);
444 reqd_map VARCHAR2(30);
445 retmsg VARCHAR2(2000);
446 retstr VARCHAR2(5000);
447
448 BEGIN
449
450 segment_maps(flex_app_sname, flex_code, flex_num, insert_tok, update_tok,
451 display_tok, insrt_map, updat_map, dspl_map, reqd_map,
452 n_segs, retmsg);
453
454 -- Translate returned encoded message
455 --
456 FND_MESSAGE.set_encoded(retmsg);
457 retmsg := FND_MESSAGE.get;
458
459 retstr := retstr || ' Insertable: ' || insrt_map;
460 retstr := retstr || ' Updatable: ' || updat_map;
461 retstr := retstr || ' Displayable: ' || dspl_map;
462 retstr := retstr || ' Required: ' || reqd_map;
463 retstr := retstr || ' Nsegs: ' || to_char(n_segs);
464 retstr := retstr || ' Msg: ' || retmsg;
465 retstr := retstr || ' Debug: ' || get_debug(1);
466
467 return(SUBSTRB(retstr, 1, 1950));
468
469 END s_maps;
470
471 /* ----------------------------------------------------------------------- */
472 /* Test function for debugging segs_secured() */
473 /* ----------------------------------------------------------------------- */
474
475 FUNCTION s_sec(resp_apid IN NUMBER,
476 resp_id IN NUMBER,
477 flex_app_sname IN VARCHAR2,
478 flex_code IN VARCHAR2,
479 flex_num IN NUMBER,
480 display_segs IN VARCHAR2,
481 concat_segs IN VARCHAR2) RETURN VARCHAR2 IS
482
483 segnum NUMBER;
484 retmsg VARCHAR2(2000);
485 retstr VARCHAR2(15000);
486
487 BEGIN
488
489 segs_secured(resp_apid, resp_id, flex_app_sname, flex_code, flex_num,
490 display_segs, concat_segs, segnum, retmsg);
491
492 -- Translate returned encoded message
493 --
494 FND_MESSAGE.set_encoded(retmsg);
495 retmsg := FND_MESSAGE.get;
496
497 retstr := retstr || 'Secured_segment: ' || to_char(segnum);
498 retstr := retstr || ' Msg: ' || retmsg;
499 retstr := retstr || ' Debug: ' || get_debug(1);
500
501 return(SUBSTRB(retstr, 1, 1950));
502
503 END s_sec;
504
505 /* ------------------------------------------------------------------------ */
506 /* Clear all flexfield server validation caches. */
507 /* ------------------------------------------------------------------------ */
508
509 PROCEDURE clear_cache IS
510 BEGIN
511 FND_FLEX_SERVER2.x_clear_cv_cache;
512 END clear_cache;
513
514 /* ------------------------------------------------------------------------ */
515 /* Externalized function so client can use hash-lock mechanism. */
516 /* Computes and locks hash value from ids passed in. */
517 /* Returns hash number 0-999 or sets FND_MESSAGE and returns < 0 */
518 /* if error. */
519 /* ------------------------------------------------------------------------ */
520
521 FUNCTION hash_lock(application_id IN NUMBER,
522 id_flex_code IN VARCHAR2,
523 id_flex_num IN NUMBER,
524 delimiter IN VARCHAR2,
525 concat_ids IN VARCHAR2) RETURN NUMBER IS
526
527 deadlock EXCEPTION;
528 nsegs NUMBER;
529 hash_num NUMBER;
530 hash_number NUMBER;
531 idsegs FND_FLEX_SERVER1.StringArray;
532 segids FND_FLEX_SERVER1.ValueIdArray;
533 kff_id FND_FLEX_SERVER1.FlexStructId;
534 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
535
536 PRAGMA EXCEPTION_INIT(deadlock, -60);
537
538 BEGIN
539 -- Find out how many segments are displayed and
540 -- convert concatenated segments to array and check there are not too many.
541 --
542 kff_id.application_id := application_id;
543 kff_id.id_flex_code := id_flex_code;
544 kff_id.id_flex_num := id_flex_num;
545 /* Bug 4772388. Explicitly set kff_id.isa_key_flexfield to TRUE
546 as otherwise we were getting hash_number of 0 always. */
547 kff_id.isa_key_flexfield := TRUE;
548
549 if((parse_displayed(kff_id, 'ALL', disp_segs) = FALSE) or
550 (FND_FLEX_SERVER2.breakup_catsegs(concat_ids, delimiter, FALSE,
551 disp_segs, nsegs, idsegs) = FALSE)) then
552 return(-20);
553 end if;
554
555 for i in 1..nsegs loop
556 segids(i) := idsegs(i);
557 end loop;
558
559 hash_number := hash_segs(nsegs, segids);
560 if(hash_number >= 0) then
561 SELECT hash_value INTO hash_num FROM fnd_flex_hash
562 WHERE hash_value = hash_number FOR UPDATE;
563 end if;
564 return(hash_number);
565
566 EXCEPTION
567 WHEN deadlock then
568 FND_MESSAGE.set_name('FND', 'FLEX-HASH DEADLOCK');
569 return(-60);
570 WHEN OTHERS then
571 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
572 FND_MESSAGE.set_token('MSG','hash_lock() exception: ' || SQLERRM);
573 return(-10);
574
575 END hash_lock;
576
577
578 FUNCTION client_hash_lock(application_id IN NUMBER,
579 id_flex_code IN VARCHAR2,
580 id_flex_num IN NUMBER,
581 delimiter IN VARCHAR2,
582 concat_ids IN VARCHAR2,
583 x_message OUT nocopy VARCHAR2)
584 RETURN NUMBER
585 IS
586 l_message VARCHAR2(2000);
587 l_encoded_message VARCHAR2(2000);
588 l_number NUMBER;
589 BEGIN
590 l_message := '';
591 l_encoded_message := '';
592 l_number := hash_lock(application_id,
593 id_flex_code,
594 id_flex_num,
595 delimiter,
596 concat_ids);
597 IF (l_number < 0) THEN
598 l_encoded_message := fnd_message.get_encoded;
599 fnd_message.set_encoded(l_encoded_message);
600 l_message := fnd_message.get;
601 fnd_message.set_encoded(l_encoded_message);
602 END IF;
603 x_message := l_message;
604 RETURN(l_number);
605 EXCEPTION
606 WHEN OTHERS THEN
607 --
608 -- This part is to take care of fnd_message exceptions.
609 -- I cannot call fnd_message here. Just return hard coded
610 -- error message.
611 --
612 x_message := 'FLEX : client_hash_lock() exception:' ||Sqlerrm;
613 return(-99);
614 END client_hash_lock;
615
616
617 /* ------------------------------------------------------------------------ */
618 /* The general purpose interface to the client c-code */
619 /* ------------------------------------------------------------------------ */
620
621 PROCEDURE validate_combination
622 (user_apid IN NUMBER,
623 user_resp IN NUMBER,
624 userid IN NUMBER,
625 flex_app_sname IN VARCHAR2,
626 flex_code IN VARCHAR2,
627 flex_num IN NUMBER,
628 vdate IN VARCHAR2,
629 vrulestr IN VARCHAR2,
630 data_set IN NUMBER,
631 invoking_mode IN VARCHAR2,
632 validate_mode IN VARCHAR2,
633 dinsert IN VARCHAR2,
634 qsecurity IN VARCHAR2,
635 required IN VARCHAR2,
636 allow_nulls IN VARCHAR2,
637 display_segs IN VARCHAR2,
638 concat_segs IN VARCHAR2,
639 vals_or_ids IN VARCHAR2,
640 concat_vals_out OUT nocopy VARCHAR2,
641 concat_ids_out OUT nocopy VARCHAR2,
642 concat_desc OUT nocopy VARCHAR2,
643 where_clause IN VARCHAR2,
644 get_extra_cols IN VARCHAR2,
645 extra_cols OUT nocopy VARCHAR2,
646 get_valatts IN VARCHAR2,
647 valatts OUT nocopy VARCHAR2,
648 get_derived IN VARCHAR2,
649 derived_vals OUT nocopy VARCHAR2,
650 start_date OUT nocopy VARCHAR2,
651 end_date OUT nocopy VARCHAR2,
652 enabled_flag OUT nocopy VARCHAR2,
653 summary_flag OUT nocopy VARCHAR2,
654 seg_delimiter OUT nocopy VARCHAR2,
655 ccid_in IN NUMBER,
656 ccid_out OUT nocopy NUMBER,
657 vstatus OUT nocopy NUMBER,
658 segcodes OUT nocopy VARCHAR2,
659 error_seg OUT nocopy NUMBER,
660 message OUT nocopy VARCHAR2,
661 select_comb_from_view IN VARCHAR2,
662 no_combmsg IN VARCHAR2,
663 where_clause_msg IN VARCHAR2,
664 server_debug_mode IN VARCHAR2)
665 IS
666 v_date DATE;
667 rq_dqual DerivedRqst;
668 rq_valat ValattRqst;
669 nvalidated NUMBER;
670 value_dvals FND_FLEX_SERVER1.ValueArray;
671 value_vals FND_FLEX_SERVER1.ValueArray;
672 value_ids FND_FLEX_SERVER1.ValueIdArray;
673 value_descs FND_FLEX_SERVER1.ValueDescArray;
674 value_desclens FND_FLEX_SERVER1.NumberArray;
675 cc_cols FND_FLEX_SERVER1.TabColArray;
676 cc_coltypes FND_FLEX_SERVER1.CharArray;
677 segtypes FND_FLEX_SERVER1.SegFormats;
678 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
679 derv FND_FLEX_SERVER1.DerivedVals;
680 tbl_derv FND_FLEX_SERVER1.DerivedVals;
681 drv_quals FND_FLEX_SERVER1.Qualifiers;
682 tbl_quals FND_FLEX_SERVER1.Qualifiers;
683 n_xcol_vals NUMBER;
684 xcol_vals FND_FLEX_SERVER1.StringArray;
685 delim VARCHAR2(1);
686 new_comb BOOLEAN;
687 BEGIN
688 fnd_flex_server1.set_debugging(server_debug_mode);
689
690 IF (fnd_flex_server1.g_debug_level > 0) THEN
691 fnd_flex_server1.add_debug('BEGIN SSV.validate_combination()');
692 END IF;
693
694 -- Initialize globals, parse qualifier requests.
695 -- If ok, then validate and return results.
696 -- Otherwise return error and message.
697 --
698
699 if((FND_FLEX_SERVER1.init_globals) and
700 (parse_drv_rqst(get_derived, rq_dqual) >= 0) and
701 (parse_va_rqst(get_valatts, rq_valat) >= 0)) then
702
703 -- Client passes in julian date of 0 to mean null vdate
704 --
705 if(vdate = '0') then
706 v_date := NULL;
707 else
708 v_date := to_date(vdate, DATE_PASS_FORMAT);
709 end if;
710
711 validation_engine
712 (user_apid, user_resp, userid, flex_app_sname,
713 flex_code, select_comb_from_view,
714 flex_num, v_date, vrulestr, data_set, invoking_mode,
715 validate_mode, dinsert, qsecurity, required, allow_nulls,
716 display_segs, concat_segs, vals_or_ids, where_clause,
717 no_combmsg, where_clause_msg,
718 get_extra_cols, ccid_in, nvalidated, value_dvals, value_vals,
719 value_ids, value_descs, value_desclens, cc_cols, cc_coltypes,
720 segtypes, disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
721 n_xcol_vals, xcol_vals, delim, ccid_out, new_comb, vstatus,
722 segcodes, error_seg);
723
724 -- Return requested outputs as concatenated strings.
725 --
726 IF (vstatus <> FND_FLEX_SERVER1.vv_error) THEN
727 concat_vals_out := concatenate_values(nvalidated, value_dvals,
728 disp_segs, delim);
729 concat_ids_out := concatenate_ids(nvalidated, value_ids, delim);
730 concat_desc := concatenate_fulldescs(nvalidated, value_descs,
731 disp_segs, delim);
732 derived_vals := ret_derived(drv_quals, derv, rq_dqual);
733 valatts := ret_valatts(drv_quals, derv, rq_valat);
734 extra_cols := FND_FLEX_SERVER1.from_stringarray2
735 (n_xcol_vals, xcol_vals,
736 FND_FLEX_SERVER1.TERMINATOR);
737 start_date := to_char(derv.start_valid, DATE_PASS_FORMAT);
738 end_date := to_char(derv.end_valid, DATE_PASS_FORMAT);
739 enabled_flag := derv.enabled_flag;
740 summary_flag := derv.summary_flag;
741 seg_delimiter := delim;
742 ELSE
743 concat_vals_out := NULL;
744 concat_ids_out := NULL;
745 concat_desc := NULL;
746 extra_cols := NULL;
747 valatts := NULL;
748 derived_vals := NULL;
749 start_date := NULL;
750 end_date := NULL;
751 enabled_flag := NULL;
752 summary_flag := NULL;
753 seg_delimiter := NULL;
754 END IF;
755 GOTO goto_return;
756 end if;
757
758 vstatus := FND_FLEX_SERVER1.VV_ERROR;
759
760 <<goto_return>>
761
762 message := FND_MESSAGE.get_encoded;
763 IF (fnd_flex_server1.g_debug_level > 0) THEN
764 fnd_flex_server1.add_debug('END SSV.validate_combination()');
765 END IF;
766 return;
767
768 EXCEPTION
769 WHEN OTHERS then
770 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
771 FND_MESSAGE.set_token('MSG','SV1.validate_combination() exception: '||SQLERRM);
772 message := FND_MESSAGE.get_encoded;
773 vstatus := FND_FLEX_SERVER1.VV_ERROR;
774 IF (fnd_flex_server1.g_debug_level > 0) THEN
775 fnd_flex_server1.add_debug
776 ('EXCEPTION others SSV.validate_combination()');
777 END IF;
778 return;
779 END validate_combination;
780
781 /* ------------------------------------------------------------------------ */
782 /* General purpose interface to the client c-code for descr flexs */
783 /* ------------------------------------------------------------------------ */
784
785 PROCEDURE
786 validate_descflex(user_apid IN NUMBER,
787 user_resp IN NUMBER,
788 userid IN NUMBER,
789 flex_app_sname IN VARCHAR2,
790 desc_flex_name IN VARCHAR2,
791 vdate IN VARCHAR2,
792 invoking_mode IN VARCHAR2,
793 allow_nulls IN VARCHAR2,
794 update_table IN VARCHAR2,
795 effective_activ IN VARCHAR2,
796 concat_segs IN VARCHAR2,
797 vals_or_ids IN VARCHAR2,
798 c_rowid IN VARCHAR2,
799 alternate_table IN VARCHAR2,
800 data_field IN VARCHAR2,
801 concat_vals_out OUT nocopy VARCHAR2,
802 concat_ids_out OUT nocopy VARCHAR2,
803 concat_desc OUT nocopy VARCHAR2,
804 seg_delimiter OUT nocopy VARCHAR2,
805 vstatus OUT nocopy NUMBER,
806 segcodes OUT nocopy VARCHAR2,
807 error_seg OUT nocopy NUMBER,
808 message OUT nocopy VARCHAR2) IS
809
810 v_date DATE;
811 rowid_in ROWID;
812 nvalidated NUMBER;
813 value_dvals FND_FLEX_SERVER1.ValueArray;
814 value_vals FND_FLEX_SERVER1.ValueArray;
815 value_ids FND_FLEX_SERVER1.ValueIdArray;
816 value_descs FND_FLEX_SERVER1.ValueDescArray;
817 value_desclens FND_FLEX_SERVER1.NumberArray;
818 cc_cols FND_FLEX_SERVER1.TabColArray;
819 cc_coltypes FND_FLEX_SERVER1.CharArray;
820 segtypes FND_FLEX_SERVER1.SegFormats;
821 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
822 delim VARCHAR2(1);
823 omit_activation BOOLEAN;
824 dummy_coldef FND_FLEX_SERVER1.ColumnDefinitions;
825
826 BEGIN
827
828 -- Initialize globals.
829 -- If ok, then validate and return results.
830 -- Otherwise return error and message.
831 --
832 if(FND_FLEX_SERVER1.init_globals) then
833
834 -- Client passes in julian date of 0 to mean null vdate
835 --
836 if(vdate = '0') then
837 v_date := NULL;
838 else
839 v_date := to_date(vdate, DATE_PASS_FORMAT);
840 end if;
841
842 -- Client passes in rowid as a VC2. Convert to a real ROWID
843 --
844 rowid_in := CHARTOROWID(c_rowid);
845
846 -- Only check disabled and expired values if effective_activ = 'N' or 'n'
847 --
848 omit_activation := FALSE;
849 if((effective_activ is not null) and
850 (effective_activ in ('n', 'N'))) then
851 omit_activation := TRUE;
852 end if;
853
854 -- Client passes in allow_nulls, update_table, and vals_or_ids as
855 -- chars. Change them to boolean.
856
857 FND_FLEX_SERVER4.descval_engine(user_apid, user_resp, userid,
858 flex_app_sname, desc_flex_name, v_date, invoking_mode,
859 (allow_nulls = 'Y'), (update_table = 'Y'),
860 omit_activation, concat_segs, (vals_or_ids = 'V'),
861 FALSE, dummy_coldef, rowid_in, alternate_table,
862 data_field, nvalidated, value_dvals, value_vals,
863 value_ids, value_descs, value_desclens, cc_cols,
864 cc_coltypes, segtypes, disp_segs, delim, vstatus,
865 segcodes, error_seg);
866
867 -- Return requested outputs as concatenated strings.
868 --
869 concat_vals_out := concatenate_values(nvalidated, value_dvals,
870 disp_segs, delim);
871 concat_ids_out := concatenate_ids(nvalidated, value_ids, delim);
872 concat_desc := concatenate_fulldescs(nvalidated, value_descs,
873 disp_segs, delim);
874 seg_delimiter := delim;
875 message := FND_MESSAGE.get_encoded;
876 return;
877 end if;
878
879 message := FND_MESSAGE.get_encoded;
880 vstatus := FND_FLEX_SERVER1.VV_ERROR;
881 return;
882
883 EXCEPTION
884 WHEN OTHERS then
885 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
886 FND_MESSAGE.set_token('MSG','validate_descflex() exception: '||SQLERRM);
887 message := FND_MESSAGE.get_encoded;
888 vstatus := FND_FLEX_SERVER1.VV_ERROR;
889 return;
890 END validate_descflex;
891
892 /* ------------------------------------------------------------------------ */
893 /* Call just prior to opening edit window in client popid(). */
894 /* */
895 /* Validates and gets segment descriptions for the concatenated */
896 /* segment values which were directly entered. If concatenated */
897 /* segment values string is null, then defaults all displayed values. */
898 /* Ignores value errors, continuing to validate values to the end. */
899 /* Returns number of segments found and concatenated values, ids */
900 /* and descriptions, segment formats, and segment valid codes. */
901 /* Returns v_status = Value validation status which is the same as */
902 /* that returned by validate_combination. */
903 /* Returns err_segnum = segment number of first value error or of */
904 /* first more serious error if any occurred. Error message always */
905 /* applies to the segment specified by err_segnum. Put cursor there. */
906 /* ------------------------------------------------------------------------ */
907
908 PROCEDURE
909 pre_window(user_apid IN NUMBER,
910 user_resp IN NUMBER,
911 flex_app_sname IN VARCHAR2,
912 flex_code IN VARCHAR2,
913 flex_num IN NUMBER,
914 vdate IN VARCHAR2,
915 vrulestr IN VARCHAR2,
916 display_segs IN VARCHAR2,
917 concat_segs IN VARCHAR2,
918 concat_vals_out OUT nocopy VARCHAR2,
919 concat_ids_out OUT nocopy VARCHAR2,
920 concat_desc OUT nocopy VARCHAR2,
921 seg_delimiter OUT nocopy VARCHAR2,
922 seg_formats OUT nocopy VARCHAR2,
923 seg_codes OUT nocopy VARCHAR2,
924 n_segments OUT nocopy NUMBER,
925 v_status OUT nocopy NUMBER,
926 err_segnum OUT nocopy NUMBER,
927 message OUT nocopy VARCHAR2) IS
928
929 nsegs NUMBER;
930 nvals NUMBER;
931 segs FND_FLEX_SERVER1.StringArray;
932 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
933 errsegnum NUMBER;
934 errcode NUMBER;
935 val_date DATE;
936 segtypes FND_FLEX_SERVER1.SegFormats;
937 segcodes VARCHAR2(30);
938 kff_cc FND_FLEX_SERVER1.CombTblInfo;
939 kff_id FND_FLEX_SERVER1.FlexStructId;
940 kff_info FND_FLEX_SERVER1.FlexStructInfo;
941 vv_flags FND_FLEX_SERVER1.ValueValidationFlags;
942 value_dvals FND_FLEX_SERVER1.ValueArray;
943 value_vals FND_FLEX_SERVER1.ValueArray;
944 value_ids FND_FLEX_SERVER1.ValueIdArray;
945 value_descs FND_FLEX_SERVER1.ValueDescArray;
946 desc_lens FND_FLEX_SERVER1.NumberArray;
947 cc_cols FND_FLEX_SERVER1.TabColArray;
948 cc_coltypes FND_FLEX_SERVER1.CharArray;
949 derv FND_FLEX_SERVER1.DerivedVals;
950 drv_quals FND_FLEX_SERVER1.Qualifiers;
951 v_rules FND_FLEX_SERVER1.Vrules;
952
953 BEGIN
954
955 -- Initialize messages, debugging, and number of sql strings
956 --
957 if(FND_FLEX_SERVER1.init_globals = FALSE) then
958 goto return_error;
959 end if;
960
961 -- Convert the validation date to date format. Client may pass 0 to mean null.
962 --
963 if(vdate = '0') then
964 val_date := NULL;
965 else
966 val_date := to_date(vdate, DATE_PASS_FORMAT);
967 end IF;
968
969 -- Get all required info about the desired flexfield structure.
970 --
971 if(FND_FLEX_SERVER2.get_keystruct(flex_app_sname, flex_code,
972 NULL, flex_num,
973 kff_id, kff_info, kff_cc) = FALSE) then
974 goto return_error;
975 end if;
976
977 -- Set validation flags.
978 --
979 vv_flags.values_not_ids := TRUE;
980 vv_flags.default_all_displayed := (concat_segs is null);
981 vv_flags.default_all_required := FALSE;
982 vv_flags.default_non_displayed := TRUE;
983 vv_flags.allow_nulls := FALSE;
984 vv_flags.message_on_null := (concat_segs is not null);
985 vv_flags.all_orphans_valid := FALSE;
986 vv_flags.ignore_security := FALSE;
987 vv_flags.ignore_expired := FALSE;
988 vv_flags.ignore_disabled := FALSE;
989 vv_flags.message_on_security := TRUE;
990 vv_flags.stop_on_value_error := FALSE;
991 vv_flags.exact_nsegs_required := FALSE;
992 vv_flags.stop_on_security := FALSE;
993
994 /* invoking_mode is added for bug872437. */
995
996 vv_flags.invoking_mode := 'P';
997
998 -- Parse vrule string and displayed token string
999 --
1000 if((parse_vrules(vrulestr, v_rules) < 0) OR
1001 (parse_displayed(kff_id, display_segs, disp_segs) = FALSE)) then
1002 goto return_error;
1003 end if;
1004
1005 -- Set up some debug information to return
1006 --
1007 IF (fnd_flex_server1.g_debug_level > 0) THEN
1008 FND_FLEX_SERVER1.add_debug('Entering pre_window(). ');
1009 FND_FLEX_SERVER1.add_debug('Flex Appl Id = ');
1010 FND_FLEX_SERVER1.add_debug(to_char(kff_id.application_id));
1011 FND_FLEX_SERVER1.add_debug(', Flex Code = ' || flex_code);
1012 FND_FLEX_SERVER1.add_debug(', Structure Number = '||to_char(flex_num));
1013 FND_FLEX_SERVER1.add_debug(', display_segs = ' || display_segs);
1014 END IF;
1015
1016 -- Convert concatenated segments to array and check there are not too many
1017 --
1018 if(FND_FLEX_SERVER2.breakup_catsegs(concat_segs,
1019 kff_info.concatenated_segment_delimiter,
1020 vv_flags.values_not_ids, disp_segs, nsegs, segs) = FALSE) then
1021 goto return_error;
1022 end if;
1023
1024 -- Look up the descriptions and ids.
1025 --
1026 errcode := FND_FLEX_SERVER1.validate_struct(kff_id,
1027 kff_cc.table_application_id, kff_cc.combination_table_id,
1028 nsegs, segs, disp_segs, vv_flags, val_date, v_rules, user_apid,
1029 user_resp, nvals, segtypes, segcodes, cc_cols, cc_coltypes,
1030 value_dvals, value_vals, value_ids, value_descs, desc_lens,
1031 derv, drv_quals, errsegnum);
1032
1033 IF (fnd_flex_server1.g_debug_level > 0) THEN
1034 FND_FLEX_SERVER1.add_debug('SV1.validate_struct() returns errcode ');
1035 FND_FLEX_SERVER1.add_debug(to_char(errcode) ||' and '|| to_char(nvals));
1036 FND_FLEX_SERVER1.add_debug(' values. SegCodes: ' || segcodes);
1037 FND_FLEX_SERVER1.add_debug(' ErrSeg: ' || to_char(errsegnum));
1038 FND_FLEX_SERVER1.add_debug(' Returned arrays:');
1039 for i in 1..nvals loop
1040 FND_FLEX_SERVER1.add_debug('"' || segtypes.vs_format(i));
1041 FND_FLEX_SERVER1.add_debug(to_char(segtypes.vs_maxsize(i), 'S099'));
1042 FND_FLEX_SERVER1.add_debug('*' || value_dvals(i) || '*');
1043 FND_FLEX_SERVER1.add_debug(cc_cols(i) || '" ');
1044 --FND_FLEX_SERVER1.add_debug(cc_cols(i)||':'||cc_coltypes(i)||'" ');
1045 end loop;
1046 END IF;
1047
1048 concat_vals_out := concatenate_values(nvals, value_dvals, disp_segs,
1049 kff_info.concatenated_segment_delimiter);
1050 concat_ids_out := concatenate_ids(nvals, value_ids,
1051 kff_info.concatenated_segment_delimiter);
1052 concat_desc := concatenate_fulldescs(nvals, value_descs, disp_segs,
1053 kff_info.concatenated_segment_delimiter);
1054 seg_delimiter := kff_info.concatenated_segment_delimiter;
1055 seg_formats := concatenate_segment_formats(segtypes);
1056 seg_codes := segcodes;
1057 n_segments := nvals;
1058 err_segnum := errsegnum;
1059 v_status := errcode;
1060 message := FND_MESSAGE.get_encoded;
1061 return;
1062
1063 <<return_error>>
1064 message := FND_MESSAGE.get_encoded;
1065 v_status := FND_FLEX_SERVER1.VV_ERROR;
1066 return;
1067
1068 EXCEPTION
1069 WHEN OTHERS then
1070 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1071 FND_MESSAGE.set_token('MSG','pre_window() exception: '||SQLERRM);
1072 message := FND_MESSAGE.get_encoded;
1073 n_segments := 0;
1074 return;
1075 END pre_window;
1076
1077 /* ------------------------------------------------------------------------ */
1078 /* Interprets INSERTABLE, UPDATABLE and DISPLAYED tokens returning */
1079 /* character strings consisting of Y's and N's which are segment by */
1080 /* segment maps indicating which segments are insertable, updatable */
1081 /* required and displayed. */
1082 /* Returns n_segments > 0 on success or = 0 and message if failure. */
1083 /* ------------------------------------------------------------------------ */
1084
1085 PROCEDURE segment_maps(flex_app_sname IN VARCHAR2,
1086 flex_code IN VARCHAR2,
1087 flex_num IN NUMBER,
1088 insert_token IN VARCHAR2,
1089 update_token IN VARCHAR2,
1090 display_token IN VARCHAR2,
1091 insert_map OUT nocopy VARCHAR2,
1092 update_map OUT nocopy VARCHAR2,
1093 display_map OUT nocopy VARCHAR2,
1094 required_map OUT nocopy VARCHAR2,
1095 n_segments OUT nocopy NUMBER,
1096 message OUT nocopy VARCHAR2) IS
1097
1098 n_segs NUMBER;
1099 kff_cc FND_FLEX_SERVER1.CombTblInfo;
1100 kff_id FND_FLEX_SERVER1.FlexStructId;
1101 kff_info FND_FLEX_SERVER1.FlexStructInfo;
1102 fq_table FND_FLEX_SERVER1.FlexQualTable;
1103 seg_disp FND_FLEX_SERVER1.CharArray;
1104 seg_rqd FND_FLEX_SERVER1.CharArray;
1105 s_required FND_FLEX_SERVER1.BooleanArray;
1106 s_insertable FND_FLEX_SERVER1.BooleanArray;
1107 s_updatable FND_FLEX_SERVER1.BooleanArray;
1108 s_displayed FND_FLEX_SERVER1.BooleanArray;
1109 ins_map VARCHAR2(30);
1110 upd_map VARCHAR2(30);
1111 disp_map VARCHAR2(30);
1112 rqd_map VARCHAR2(30);
1113
1114 BEGIN
1115
1116 -- Initialize messages, debugging, and number of sql strings
1117 --
1118 if(FND_FLEX_SERVER1.init_globals = FALSE) then
1119 goto return_error;
1120 end if;
1121
1122 -- Get all required info about the desired flexfield structure.
1123 --
1124 if(FND_FLEX_SERVER2.get_keystruct(flex_app_sname, flex_code,
1125 NULL, flex_num,
1126 kff_id, kff_info, kff_cc) = FALSE) then
1127 goto return_error;
1128 end if;
1129
1130 -- Set up some debug information to return
1131 --
1132 IF (fnd_flex_server1.g_debug_level > 0) THEN
1133 FND_FLEX_SERVER1.add_debug('Starting segment_maps() ');
1134 FND_FLEX_SERVER1.add_debug('Flex Appl Id = ');
1135 FND_FLEX_SERVER1.add_debug(to_char(kff_id.application_id));
1136 FND_FLEX_SERVER1.add_debug(', Flex Code = ' || flex_code);
1137 FND_FLEX_SERVER1.add_debug(', Structure Number = '||to_char(flex_num));
1138 END IF;
1139 if(FND_FLEX_SERVER2.get_qualsegs(kff_id, n_segs, seg_disp,
1140 seg_rqd, fq_table) = FALSE) then
1141 goto return_error;
1142 end if;
1143
1144 if((NOT evaluate_token(display_token, n_segs, fq_table, s_displayed)) OR
1145 (NOT evaluate_token(update_token, n_segs, fq_table, s_updatable)) OR
1146 (NOT evaluate_token(insert_token, n_segs, fq_table, s_insertable))) then
1147 goto return_error;
1148 end if;
1149
1150 -- Need to merge the displayed map obtained from the DISPLAYED token
1151 -- alone with the display map from the flex structure.
1152 -- Also need to turn required into boolean.
1153 --
1154 for i in 1..n_segs loop
1155 s_required(i) := (seg_rqd(i) = 'Y');
1156 s_displayed(i) := ((seg_disp(i) = 'Y') AND s_displayed(i));
1157 end loop;
1158
1159 -- Return the completed maps
1160 --
1161 for i in 1..n_segs loop
1162 if(s_insertable(i)) then
1163 ins_map := ins_map || 'Y';
1164 else
1165 ins_map := ins_map || 'N';
1166 end if;
1167 if(s_updatable(i)) then
1168 upd_map := upd_map || 'Y';
1169 else
1170 upd_map := upd_map || 'N';
1171 end if;
1172 if(s_required(i)) then
1173 rqd_map := rqd_map || 'Y';
1174 else
1175 rqd_map := rqd_map || 'N';
1176 end if;
1177 if(s_displayed(i)) then
1178 disp_map := disp_map || 'Y';
1179 else
1180 disp_map := disp_map || 'N';
1181 end if;
1182 end loop;
1183
1184 display_map := disp_map;
1185 insert_map := ins_map;
1186 update_map := upd_map;
1187 required_map := rqd_map;
1188 n_segments := n_segs;
1189 FND_MESSAGE.clear;
1190 return;
1191
1192 <<return_error>>
1193 message := FND_MESSAGE.get_encoded;
1194 n_segments := 0;
1195 return;
1196
1197 EXCEPTION
1198 WHEN OTHERS then
1199 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1200 FND_MESSAGE.set_token('MSG', 'segment_maps() exception: ' || SQLERRM);
1201 message := FND_MESSAGE.get_encoded;
1202 n_segments := 0;
1203 return;
1204 END segment_maps;
1205
1206 /* ------------------------------------------------------------------------ */
1207 /* Determines if any segments in the combination passed in are secured. */
1208 /* Returns SEGNUM = the number of the first segment which violates */
1209 /* security, 0 if no segment values are secured, or < 0 on error. */
1210 /* MESSAGE is the security violation message of the first secured */
1211 /* segment or the error message if an error occurred, or NULL if */
1212 /* no segments are secured. */
1213 /* ------------------------------------------------------------------------ */
1214
1215 PROCEDURE segs_secured(resp_apid IN NUMBER,
1216 resp_id IN NUMBER,
1217 flex_app_sname IN VARCHAR2,
1218 flex_code IN VARCHAR2,
1219 flex_num IN NUMBER,
1220 display_segs IN VARCHAR2,
1221 concat_segs IN VARCHAR2,
1222 segnum OUT nocopy NUMBER,
1223 message OUT nocopy VARCHAR2) IS
1224
1225 secseg NUMBER;
1226 nsegs NUMBER;
1227 segs FND_FLEX_SERVER1.StringArray;
1228 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
1229 kff_cc FND_FLEX_SERVER1.CombTblInfo;
1230 kff_id FND_FLEX_SERVER1.FlexStructId;
1231 kff_info FND_FLEX_SERVER1.FlexStructInfo;
1232
1233 BEGIN
1234
1235 -- Check for null segments
1236 --
1237 if(concat_segs is null) then
1238 segnum := 0;
1239 return;
1240 end if;
1241
1242 -- Initialize messages, debugging, and number of sql strings
1243 --
1244 if(FND_FLEX_SERVER1.init_globals = FALSE) then
1245 goto return_error;
1246 end if;
1247
1248 -- Get all required info about the desired flexfield structure.
1249 --
1250 if(FND_FLEX_SERVER2.get_keystruct(flex_app_sname, flex_code,
1251 NULL, flex_num,
1252 kff_id, kff_info, kff_cc) = FALSE) then
1253 goto return_error;
1254 end if;
1255
1256 -- Set up some debug information to return
1257 --
1258 IF (fnd_flex_server1.g_debug_level > 0) THEN
1259 FND_FLEX_SERVER1.add_debug('Flex Appl Id = ');
1260 FND_FLEX_SERVER1.add_debug(to_char(kff_id.application_id));
1261 FND_FLEX_SERVER1.add_debug(', Flex Code = ' || flex_code);
1262 FND_FLEX_SERVER1.add_debug(', Structure Number = '|| to_char(flex_num));
1263 END IF;
1264 if(parse_displayed(kff_id, display_segs, disp_segs) = FALSE) then
1265 goto return_error;
1266 end if;
1267
1268 -- Convert concatenated segments to array and check there are not too many
1269 --
1270 if(FND_FLEX_SERVER2.breakup_catsegs(concat_segs,
1271 kff_info.concatenated_segment_delimiter,
1272 TRUE, disp_segs, nsegs, segs) = FALSE) then
1273 goto return_error;
1274 end if;
1275
1276 -- See if any segment values are secured.
1277 --
1278 secseg := FND_FLEX_SERVER1.vals_secured(kff_id, nsegs, segs, disp_segs,
1279 resp_apid, resp_id);
1280 segnum := secseg;
1281 if(secseg = 0) then
1282 FND_MESSAGE.clear;
1283 else
1284 message := FND_MESSAGE.get_encoded;
1285 end if;
1286 return;
1287
1288 <<return_error>>
1289 message := FND_MESSAGE.get_encoded;
1290 segnum := -5;
1291 return;
1292
1293 EXCEPTION
1294 WHEN OTHERS then
1295 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1296 FND_MESSAGE.set_token('MSG','segs_secured() exception: '||SQLERRM);
1297 message := FND_MESSAGE.get_encoded;
1298 segnum := -4;
1299 return;
1300 END segs_secured;
1301
1302 --
1303 -- Autonomous Transaction Version of Insert Combination.
1304 -- copied from insert_combination().
1305 --
1306 FUNCTION insert_combination_at
1307 (fstruct IN FND_FLEX_SERVER1.FlexStructId,
1308 structnum IN NUMBER,
1309 maintmode IN BOOLEAN,
1310 v_date IN DATE,
1311 seg_delim IN VARCHAR2,
1312 ccid_inp IN NUMBER,
1313 combtbl IN FND_FLEX_SERVER1.CombTblInfo,
1314 combcols IN FND_FLEX_SERVER1.TabColArray,
1315 combtypes IN FND_FLEX_SERVER1.CharArray,
1316 user_id IN NUMBER,
1317 nsegs IN NUMBER,
1318 segids_in IN FND_FLEX_SERVER1.ValueIdArray,
1319 segvals_in IN FND_FLEX_SERVER1.valuearray,
1320 segfmts IN FND_FLEX_SERVER1.SegFormats,
1321 dvalues IN FND_FLEX_SERVER1.DerivedVals,
1322 dquals IN FND_FLEX_SERVER1.Qualifiers,
1323 nxcols IN NUMBER,
1324 xcolnames IN FND_FLEX_SERVER1.StringArray,
1325 xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
1326 qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
1327 tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
1328 newcomb OUT nocopy BOOLEAN,
1329 ccid_out OUT nocopy NUMBER) RETURN BOOLEAN
1330 IS
1331 PRAGMA AUTONOMOUS_TRANSACTION;
1332 deadlock EXCEPTION;
1333 ccid NUMBER;
1334 nfound NUMBER;
1335 hash_num NUMBER;
1336 hash_number NUMBER;
1337 ccid_string VARCHAR2(50);
1338 segids FND_FLEX_SERVER1.ValueIdArray;
1339
1340 PRAGMA EXCEPTION_INIT(deadlock, -60);
1341
1342 BEGIN
1343 SAVEPOINT pre_insert_comb_at;
1344
1345 -- For debugging...
1346 --
1347 IF (fnd_flex_server1.g_debug_level > 0) THEN
1348 FND_FLEX_SERVER1.add_debug(to_char(nsegs) ||
1349 ' segs passed to insert_combination().');
1350 END IF;
1351
1352 -- First lock this combination and prevent other users from inserting.
1353 -- Generate an almost unique hash number from the segments ids.
1354 -- and lock that row in the hash table to prevent other users from
1355 -- inserting the same combination. The commit will drop the locks.-
1356 -- GL requires row share lock on combinations table to prevent them from
1357 -- getting an exclusive lock for their processing.
1358
1359 segids := segids_in;
1360
1361 sqls := 'lock table ' || combtbl.application_table_name;
1362 sqls := sqls || ' in row share mode';
1363
1364 fnd_dsql.init;
1365 fnd_dsql.add_text(sqls);
1366 if(FND_FLEX_SERVER1.x_dsql_execute < 0) THEN
1367 GOTO return_false;
1368 end if;
1369
1370 -- Next compute the hash number that is to be locked.
1371 --
1372 hash_number := hash_segs(nsegs, segids);
1373 if(hash_number < 0) THEN
1374 GOTO return_false;
1375 end if;
1376
1377 IF (fnd_flex_server1.g_debug_level > 0) THEN
1378 FND_FLEX_SERVER1.add_debug('Hash value = '||to_char(hash_number)||'.');
1379 END IF;
1380 SELECT hash_value INTO hash_num FROM fnd_flex_hash
1381 WHERE hash_value = hash_number FOR UPDATE;
1382
1383 -- Double-check to see if it has been created.
1384 -- No where clause this time.
1385 --
1386 nfound := find_combination(structnum, combtbl, nsegs, combcols,
1387 combtypes, segfmts, dquals.nquals,
1388 dquals.derived_cols, nxcols, xcolnames, NULL,
1389 ccid, segids, tblderv, qualvals, xcolvals);
1390 if(nfound <> 0) then
1391 if(nfound >= 1) then
1392 newcomb := FALSE;
1393 ccid_out := ccid;
1394 GOTO return_true;
1395 end if;
1396 GOTO return_false;
1397 end if;
1398
1399 -- Get unique code combination ID from a sequence if we dont
1400 -- already have it.
1401 -- If ccid_inp is 0 or null or -1 we need to generate a new ccid.
1402 -- Must use dynamic SQL here since ccid comes from the application table
1403 -- with a '_S' suffix. Could do without dynamic sql if we had a fixed
1404 -- sequence name.
1405 --
1406 if(maintmode and (ccid_inp is not null) and (ccid_inp <> 0) and
1407 (ccid_inp <> -1)) then
1408 ccid := ccid_inp;
1409 else
1410 sqls := 'select to_char(' || combtbl.application_table_name;
1411 sqls := sqls || '_S.NEXTVAL) from dual';
1412 fnd_dsql.init;
1413 fnd_dsql.add_text(sqls);
1414 if(FND_FLEX_SERVER1.x_dsql_select_one(ccid_string) <> 1) THEN
1415 GOTO return_false;
1416 end if;
1417 ccid := to_number(ccid_string);
1418 if(ccid > MAX_CCID) then
1419 FND_MESSAGE.set_name('FND', 'FLEX-CCID TOO BIG');
1420 FND_MESSAGE.set_token('CCIDLIMIT', to_char(MAX_CCID));
1421 FND_MESSAGE.set_token('SEQNAME', combtbl.application_table_name||'_S');
1422 GOTO return_false;
1423 end if;
1424 end if;
1425
1426 -- Call user validation function now if desired. Bail if error.
1427 --
1428 if(userval_on) then
1429 if(NOT call_userval(fstruct, v_date, nsegs, seg_delim, segids_in)) then
1430 GOTO return_false;
1431 end if;
1432 end if;
1433
1434 -- If not in maintainence mode do the insert, otherwise skip to the end.
1435 --
1436 if(NOT maintmode) then
1437
1438 -- Build a SQL statement to do the insert.
1439 --
1440 fnd_dsql.init;
1441 sqls := 'insert into ' || combtbl.application_table_name || ' (';
1442 sqls := sqls || combtbl.unique_id_column_name;
1443 if(combtbl.set_defining_column_name is not null) then
1444 sqls := sqls || ', ' || combtbl.set_defining_column_name;
1445 end if;
1446 sqls := sqls || ', ENABLED_FLAG, SUMMARY_FLAG, ';
1447 sqls := sqls || 'START_DATE_ACTIVE, END_DATE_ACTIVE, ';
1448 sqls := sqls || 'LAST_UPDATE_DATE, LAST_UPDATED_BY';
1449 for i in 1..dquals.nquals loop
1450 sqls := sqls || ', ' || dquals.derived_cols(i);
1451 end loop;
1452 for i in 1..nsegs loop
1453 if(segids(i) is not null) then
1454 sqls := sqls || ', ' || combcols(i);
1455 end if;
1456 end loop;
1457 sqls := sqls || ') values (';
1458
1459 -- So far the table name and the column names.
1460 fnd_dsql.add_text(sqls);
1461
1462 fnd_dsql.add_bind(ccid);
1463
1464 if(combtbl.set_defining_column_name is not null) THEN
1465 fnd_dsql.add_text(',');
1466 fnd_dsql.add_bind(structnum);
1467 end if;
1468
1469 fnd_dsql.add_text(',');
1470 fnd_dsql.add_bind(dvalues.enabled_flag);
1471
1472 fnd_dsql.add_text(',');
1473 fnd_dsql.add_bind(dvalues.summary_flag);
1474
1475 fnd_dsql.add_text(',');
1476 fnd_dsql.add_bind(dvalues.start_valid);
1477
1478 fnd_dsql.add_text(',');
1479 fnd_dsql.add_bind(dvalues.end_valid);
1480
1481 fnd_dsql.add_text(',sysdate,');
1482 fnd_dsql.add_bind(user_id);
1483
1484 for i in 1..dquals.nquals LOOP
1485 fnd_dsql.add_text(',');
1486 fnd_dsql.add_bind(dquals.sq_values(i));
1487 end loop;
1488
1489 for i in 1..nsegs loop
1490 if(segids(i) is not null) THEN
1491 fnd_dsql.add_text(',');
1492 --
1493 -- This will call fnd_dsql.add_bind
1494 --
1495 fnd_flex_server1.x_compare_clause
1496 (combtypes(i),
1497 combcols(i), segids(i), FND_FLEX_SERVER1.VC_ID,
1498 segfmts.vs_format(i), segfmts.vs_maxsize(i));
1499 end if;
1500 end loop;
1501 fnd_dsql.add_text(')');
1502
1503 --
1504 -- Finally do the insert
1505 --
1506 if(FND_FLEX_SERVER1.x_dsql_execute < 0) then
1507 GOTO return_false;
1508 end if;
1509 if((fstruct.application_id = 101) and (fstruct.id_flex_code ='GL#')) then
1510 if(call_fdfgli(ccid) = FALSE) THEN
1511 GOTO return_false;
1512 end if;
1513 end if;
1514 end if;
1515
1516 -- Return all out variables. If comb was found in table these were set
1517 -- above.
1518 --
1519 IF (fnd_flex_server1.g_debug_level > 0) THEN
1520 FND_FLEX_SERVER1.add_debug(' Returning ccid = '||to_char(ccid)|| '. ');
1521 END IF;
1522 ccid_out := ccid;
1523 newcomb := TRUE;
1524 tblderv := dvalues;
1525 for i in 1..nxcols loop
1526 xcolvals(i) := NULL;
1527 end loop;
1528 for i in 1..dquals.nquals loop
1529 qualvals(i) := dquals.sq_values(i);
1530 end loop;
1531
1532 --
1533 -- return(TRUE);
1534 --
1535 -- This point was the end of regular insert_combination.
1536 --
1537 -- However following stuff should be done in AT.
1538 -- copied from validation_engine() function.
1539 --
1540 if(FND_FLEX_SERVER2.x_drop_cached_cv_result(fstruct, nsegs, segvals_in)
1541 = FALSE) then
1542 GOTO return_false;
1543 end if;
1544
1545 GOTO return_true;
1546
1547 <<return_true>>
1548 COMMIT;
1549 RETURN(TRUE);
1550
1551 <<return_false>>
1552 ROLLBACK TO SAVEPOINT pre_insert_comb_at;
1553 ROLLBACK; -- required by AT.
1554 RETURN(FALSE);
1555
1556 EXCEPTION
1557 WHEN NO_DATA_FOUND then
1558 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1559 FND_MESSAGE.set_token('MSG', 'Hash value ' ||
1560 to_char(hash_number) || ' not found.');
1561 ROLLBACK TO SAVEPOINT pre_insert_comb_at;
1562 ROLLBACK; -- required by AT.
1563 return(FALSE);
1564 WHEN TOO_MANY_ROWS then
1565 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1566 FND_MESSAGE.set_token('MSG', 'Hash value ' ||
1567 to_char(hash_number) || ' not unique.');
1568 ROLLBACK TO SAVEPOINT pre_insert_comb_at;
1569 ROLLBACK; -- required by AT.
1570 return(FALSE);
1571 WHEN TIMEOUT_ON_RESOURCE then
1572 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1573 FND_MESSAGE.set_token('MSG', 'Timeout waiting for lock on hash table.');
1574 ROLLBACK TO SAVEPOINT pre_insert_comb_at;
1575 ROLLBACK; -- required by AT.
1576 return(FALSE);
1577 WHEN deadlock then
1578 FND_MESSAGE.set_name('FND', 'FLEX-HASH DEADLOCK');
1579 ROLLBACK TO SAVEPOINT pre_insert_comb_at;
1580 ROLLBACK; -- required by AT.
1581 return(FALSE);
1582 WHEN OTHERS then
1583 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
1584 FND_MESSAGE.set_token('MSG','insert_combination() exception: '||SQLERRM);
1585 ROLLBACK TO SAVEPOINT pre_insert_comb_at;
1586 ROLLBACK; -- required by AT.
1587 return(FALSE);
1588 END insert_combination_AT;
1589
1590
1591
1592 /* ------------------------------------------------------------------------- */
1593 /* The general purpose engine for popid(), loadid() and valid() */
1594 /* */
1595 /* Takes either concatenated segments or segment array as input. */
1596 /* assume segment array input unless nsegs = 0. */
1597 /* */
1598 /* This function returns output arrays that may or may not be */
1599 /* populated depending on the point at which the validation stopped. */
1600 /* The number of output array elements populated is specified by */
1601 /* an array count for each type of data. For disp_segs, drv_quals, */
1602 /* and tbl_quals the array count is specified within the returned */
1603 /* data structure. The number of extra columns output is specified */
1604 /* by n_xcols. For all other output arrays (including the segcodes */
1605 /* string) the number of output values is specified by nvalidated. */
1606 /* Nvalidated is the number of enabled segments that were validated */
1607 /* before validation stopped. */
1608 /* Many error conditions return no array information at all. This */
1609 /* condition is indicated by setting the array counts to 0 for all */
1610 /* empty arrays. */
1611 /* */
1612 /* NOTE: Make sure to call FND_FLEX_SERVER1.init_globals before */
1613 /* calling this function, to initialize debugging and messages. */
1614 /* ------------------------------------------------------------------------ */
1615
1616 PROCEDURE
1617 validation_engine(user_apid IN NUMBER,
1618 user_resp IN NUMBER,
1619 userid IN NUMBER,
1620 flex_app_sname IN VARCHAR2,
1621 flex_code IN VARCHAR2,
1622 select_comb_from_view IN VARCHAR2,
1623 flex_num IN NUMBER,
1624 val_date IN DATE,
1625 vrulestr IN VARCHAR2,
1626 data_set IN NUMBER,
1627 invoking_mode IN VARCHAR2,
1628 validate_mode IN VARCHAR2,
1629 dinsert IN VARCHAR2,
1630 qsecurity IN VARCHAR2,
1631 required IN VARCHAR2,
1632 allow_nulls IN VARCHAR2,
1633 display_segstr IN VARCHAR2,
1634 concat_segs IN VARCHAR2,
1635 vals_or_ids IN VARCHAR2,
1636 where_clause IN VARCHAR2,
1637 no_combmsg IN VARCHAR2,
1638 where_clause_msg IN VARCHAR2,
1639 get_extra_cols IN VARCHAR2,
1640 ccid_in IN NUMBER,
1641 nvalidated OUT nocopy NUMBER,
1642 displayed_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
1643 stored_vals OUT nocopy FND_FLEX_SERVER1.ValueArray,
1644 segment_ids OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
1645 descriptions OUT nocopy FND_FLEX_SERVER1.ValueDescArray,
1646 desc_lengths OUT nocopy FND_FLEX_SERVER1.NumberArray,
1647 seg_colnames OUT nocopy FND_FLEX_SERVER1.TabColArray,
1648 seg_coltypes OUT nocopy FND_FLEX_SERVER1.CharArray,
1649 segment_types OUT nocopy FND_FLEX_SERVER1.SegFormats,
1650 displayed_segs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs,
1651 derived_eff OUT nocopy FND_FLEX_SERVER1.DerivedVals,
1652 table_eff OUT nocopy FND_FLEX_SERVER1.DerivedVals,
1653 derived_quals OUT nocopy FND_FLEX_SERVER1.Qualifiers,
1654 table_quals OUT nocopy FND_FLEX_SERVER1.Qualifiers,
1655 n_column_vals OUT nocopy NUMBER,
1656 column_vals OUT nocopy FND_FLEX_SERVER1.StringArray,
1657 seg_delimiter OUT nocopy VARCHAR2,
1658 ccid_out OUT nocopy NUMBER,
1659 new_combination OUT nocopy BOOLEAN,
1660 v_status OUT nocopy NUMBER,
1661 seg_codes OUT nocopy VARCHAR2,
1662 err_segnum OUT nocopy NUMBER) IS
1663
1664 big_arg VARCHAR2(40);
1665 catsegs VARCHAR2(2000);
1666 segtypes FND_FLEX_SERVER1.SegFormats;
1667 segcodes VARCHAR2(30);
1668 error_col VARCHAR2(30);
1669 nfound NUMBER;
1670 comb_id NUMBER;
1671 errcode NUMBER;
1672 errsegnum NUMBER;
1673 cc_struct NUMBER;
1674 for_insert BOOLEAN;
1675 dynam_insrt BOOLEAN;
1676 defer_insrt BOOLEAN;
1677 new_comb BOOLEAN;
1678 kff_cc FND_FLEX_SERVER1.CombTblInfo;
1679 kff_id FND_FLEX_SERVER1.FlexStructId;
1680 kff_info FND_FLEX_SERVER1.FlexStructInfo;
1681 nvals NUMBER;
1682 nsegs NUMBER;
1683 segs FND_FLEX_SERVER1.StringArray;
1684 value_dvals FND_FLEX_SERVER1.ValueArray;
1685 value_vals FND_FLEX_SERVER1.ValueArray;
1686 value_ids FND_FLEX_SERVER1.ValueIdArray;
1687 value_descs FND_FLEX_SERVER1.ValueDescArray;
1688 cc_cols FND_FLEX_SERVER1.TabColArray;
1689 cc_coltypes FND_FLEX_SERVER1.CharArray;
1690 desc_lens FND_FLEX_SERVER1.NumberArray;
1691 derv FND_FLEX_SERVER1.DerivedVals;
1692 tbl_derv FND_FLEX_SERVER1.DerivedVals;
1693 drv_quals FND_FLEX_SERVER1.Qualifiers;
1694 tbl_quals FND_FLEX_SERVER1.Qualifiers;
1695 v_rules FND_FLEX_SERVER1.Vrules;
1696 n_xcols NUMBER;
1697 rq_xcols FND_FLEX_SERVER1.StringArray;
1698 xcol_vals FND_FLEX_SERVER1.StringArray;
1699 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
1700 vv_flags FND_FLEX_SERVER1.ValueValidationFlags;
1701 entered VARCHAR2(1);
1702 nice_where_cl VARCHAR2(2000);
1703 l_dinsert VARCHAR2(10);
1704 no_at BOOLEAN := FALSE;
1705 validate_off BOOLEAN := FALSE;
1706 invoking_mode_l VARCHAR2(1);
1707 userid_l NUMBER;
1708 BEGIN
1709 l_dinsert := Nvl(dinsert, 'N');
1710 IF (fnd_flex_server1.g_debug_level > 0) THEN
1711 fnd_flex_server1.add_debug('BEGIN SSV.validation_engine()');
1712 END IF;
1713
1714 -- Initialize all output variables so that returning from any point
1715 -- results in a valid state.
1716 --
1717 nvalidated := 0;
1718 segment_types.nsegs := 0;
1719 displayed_segs.n_segflags := 0;
1720 derived_quals.nquals := 0;
1721 table_quals.nquals := 0;
1722 n_column_vals := 0;
1723 new_combination := FALSE;
1724 v_status := FND_FLEX_SERVER1.VV_ERROR;
1725
1726 -- Initialize everything which affects returned information. This way we
1727 -- can process all returned information before returning when exiting from
1728 -- any point in this code even if there is an error.
1729 -- Dont worry about initializing strings to null.
1730
1731 nvals := 0;
1732 nsegs := 0;
1733 segtypes.nsegs := 0;
1734 disp_segs.n_segflags := 0;
1735 drv_quals.nquals := 0;
1736 tbl_quals.nquals := 0;
1737 n_xcols := 0;
1738 new_comb := FALSE;
1739 errcode := FND_FLEX_SERVER1.VV_ERROR;
1740 userid_l := userid;
1741
1742 if((concat_segs is null) and (ccid_in is null)) then
1743 entered := 'N';
1744 else
1745 entered := 'Y';
1746 end if;
1747
1748 -- Set cc_struct to the structure number to be used when interacting
1749 -- with the combinations table
1750 --
1751 if((data_set is null) or (data_set = -1)) then
1752 cc_struct := flex_num;
1753 else
1754 cc_struct := data_set;
1755 end if;
1756
1757 -- Get all required info about the desired flexfield structure.
1758 --
1759 if(FND_FLEX_SERVER2.get_keystruct(flex_app_sname, flex_code,
1760 select_comb_from_view, flex_num,
1761 kff_id, kff_info, kff_cc) = FALSE) then
1762 goto return_error;
1763 end if;
1764
1765 --
1766 -- If key flex is multi lingual then no dinsert.
1767 --
1768 IF (Upper(kff_cc.application_table_name) <>
1769 Upper(kff_cc.select_comb_from)) THEN
1770 l_dinsert := 'N';
1771 END IF;
1772
1773 -- Check maximum lengths of input strings
1774 --
1775 if(LENGTHB(display_segstr) > MAX_ARG_LEN) then
1776 big_arg := 'DISPLAYABLE';
1777 elsif(LENGTHB(where_clause) > MAX_ARG_LEN) then
1778 big_arg := 'WHERE_CLAUSE';
1779 elsif(LENGTHB(vrulestr) > MAX_ARG_LEN) then
1780 big_arg := 'VRULE';
1781 elsif(LENGTHB(get_extra_cols) > MAX_ARG_LEN) then
1782 big_arg := 'COLUMN';
1783 else
1784 big_arg := NULL;
1785 end if;
1786
1787 if(big_arg is not null) then
1788 FND_MESSAGE.set_name('FND', 'FLEX-ARGUMENT TOO LONG');
1789 FND_MESSAGE.set_token('ARG', big_arg);
1790 FND_MESSAGE.set_token('MAXLEN', to_char(MAX_ARG_LEN));
1791 goto return_error;
1792 end if;
1793
1794 -- Limit concatenated segment length for compatibility with client
1795 --
1796 if(LENGTHB(concat_segs) > MAX_CATSEG_LEN) then
1797 FND_MESSAGE.set_name('FND', 'FLEX-CONCAT LEN > IAPFLEN');
1798 FND_MESSAGE.set_token('MAXFLDLEN', to_char(MAX_CATSEG_LEN));
1799 goto return_error;
1800 end if;
1801
1802 -- First check that operation makes sense
1803 --
1804 invoking_mode_l := invoking_mode;
1805 if (invoking_mode_l = 'Z') then
1806 validate_off := TRUE;
1807 invoking_mode_l := 'V';
1808 end if;
1809
1810 if((invoking_mode_l is null) or
1811 (invoking_mode_l NOT IN ('V', 'P', 'L', 'G'))) then
1812 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD INVOKE');
1813 goto return_error;
1814 end if;
1815
1816 if((validate_mode is null) or
1817 (validate_mode not in
1818 ('FULL', 'PARTIAL', 'PARTIAL_IF_POSSIBLE', 'FOR_INSERT'))) then
1819 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD VALMODE');
1820 goto return_error;
1821 end if;
1822
1823 if((invoking_mode_l in ('V', 'P', 'G')) and
1824 ((vals_or_ids is null) or
1825 (vals_or_ids not in ('V', 'I')))) then
1826 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD FLAG');
1827 goto return_error;
1828 end if;
1829
1830 -- Set validation flags.
1831 --
1832 vv_flags.default_all_displayed := TRUE;
1833 vv_flags.values_not_ids := (vals_or_ids = 'V') AND (invoking_mode_l <> 'L');
1834 vv_flags.default_all_required := ((invoking_mode_l = 'V') and
1835 (required = 'Y') and (entered = 'N'));
1836 vv_flags.default_non_displayed := (invoking_mode_l in ('P', 'G', 'V'));
1837 vv_flags.allow_nulls := ((allow_nulls = 'Y') and
1838 (validate_mode in ('PARTIAL', 'PARTIAL_IF_POSSIBLE')));
1839 vv_flags.message_on_null := TRUE;
1840 vv_flags.all_orphans_valid := (validate_mode = 'PARTIAL_IF_POSSIBLE');
1841 vv_flags.ignore_security := ((invoking_mode_l = 'L') AND (qsecurity = 'N'));
1842 vv_flags.ignore_expired := (invoking_mode_l in ('L', 'G'));
1843 vv_flags.ignore_disabled := (invoking_mode_l in ('L', 'G'));
1844 if(validate_off) then
1845 vv_flags.ignore_security := TRUE;
1846 vv_flags.ignore_expired := TRUE;
1847 vv_flags.ignore_disabled := TRUE;
1848 end if;
1849 vv_flags.message_on_security := ((invoking_mode_l <> 'L') OR
1850 ((invoking_mode_l = 'L') AND (qsecurity = 'Y')));
1851 vv_flags.stop_on_value_error := ((invoking_mode_l <> 'P') AND
1852 (invoking_mode_l <> 'G'));
1853 vv_flags.exact_nsegs_required := ((invoking_mode_l = 'L') or
1854 ((invoking_mode_l = 'V') and not vv_flags.default_all_required));
1855 vv_flags.stop_on_security := ((invoking_mode_l = 'V') OR
1856 ((invoking_mode_l = 'L') AND (qsecurity = 'Y')));
1857
1858 /* invoking_mode is added for bug872437. */
1859
1860 vv_flags.invoking_mode := invoking_mode_l;
1861
1862 -- Parse inputs
1863 --
1864 if((parse_vrules(vrulestr, v_rules) < 0) OR
1865 (parse_displayed(kff_id, display_segstr, disp_segs) = FALSE)) then
1866 goto return_error;
1867 end if;
1868
1869 IF (get_extra_cols IS NOT NULL) THEN
1870 n_xcols := FND_FLEX_SERVER1.to_stringarray2(get_extra_cols,
1871 FND_FLEX_SERVER1.TERMINATOR, rq_xcols);
1872 ELSE
1873 n_xcols := 0;
1874 END IF;
1875
1876 -- Initialize extra columns to null
1877 -- to prevent no data found error if combination not looked up.
1878 --
1879 for i in 1..n_xcols loop
1880 xcol_vals(i) := NULL;
1881 end loop;
1882
1883 -- List the parsed v-rules if any
1884 --
1885 IF (fnd_flex_server1.g_debug_level > 0) THEN
1886 if(v_rules.nvrules > 0) then
1887 FND_FLEX_SERVER1.add_debug(to_char(v_rules.nvrules) || '{Vrules: ');
1888 for i in 1..v_rules.nvrules loop
1889 FND_FLEX_SERVER1.add_debug('(' || v_rules.fq_names(i) || ', ');
1890 FND_FLEX_SERVER1.add_debug(v_rules.sq_names(i) || ', ');
1891 FND_FLEX_SERVER1.add_debug(v_rules.ie_flags(i) || ', ');
1892 FND_FLEX_SERVER1.add_debug(v_rules.cat_vals(i) || ', ');
1893 FND_FLEX_SERVER1.add_debug(v_rules.app_names(i) || ', ');
1894 FND_FLEX_SERVER1.add_debug(v_rules.err_names(i) || ') ');
1895 end loop;
1896 FND_FLEX_SERVER1.add_debug('} ');
1897 end if;
1898
1899 -- Set up some debug information to return
1900 --
1901 FND_FLEX_SERVER1.add_debug('User AppId = ' || to_char(user_apid) ||
1902 ', User Resp = ' || to_char(user_resp) ||
1903 ', User Id = ' || to_char(userid_l));
1904 FND_FLEX_SERVER1.add_debug('Flex Appl Id = ' || kff_id.application_id ||
1905 ', Flex Code = ' || flex_code ||
1906 ', Structure Number = '|| flex_num ||
1907 ', Dinsert = ' || l_dinsert || '. ');
1908 END IF;
1909
1910 -- If dinsert = 'D' we do everything except insert the combination.
1911 --
1912 defer_insrt := ((l_dinsert is not null) and (l_dinsert = 'D'));
1913
1914 -- Bug 1531345
1915
1916 no_at := ((l_dinsert is not null) and (l_dinsert = 'O'));
1917
1918 -- Dynamic inserts always allowed in FOR_INSERT mode
1919 --
1920 dynam_insrt := (validate_mode = 'FOR_INSERT') or
1921 ((l_dinsert is not null) and (l_dinsert = 'F')) or
1922 ((l_dinsert is not null) and (l_dinsert in ('Y', 'D', 'O')) and
1923 ((kff_cc.application_table_type is null) or
1924 (kff_cc.application_table_type = 'G')) and
1925 (kff_info.dynamic_inserts_feasible_flag = 'Y') and
1926 (kff_info.dynamic_inserts_allowed_flag = 'Y'));
1927
1928 -- LOADID in FOR_INSERT mode returns ccid_in as the combination id.
1929
1930 if((invoking_mode_l = 'L') and (validate_mode = 'FOR_INSERT')) then
1931 comb_id := ccid_in;
1932 end if;
1933
1934 -- Concatenated segments are input for VALID() or POPID() modes, and for
1935 -- LOADID() mode for invoking_modes other than FULL.
1936 -- For other modes look up the segments by CCID first and check the table
1937 -- qualifiers against the vrules. If we need to look up by CCID, we will
1938 -- first have to get the names of the segment columns and qualifier columns.
1939 --
1940 if((invoking_mode_l IN ('V', 'P', 'G')) or ((invoking_mode_l = 'L') and
1941 (validate_mode in ('PARTIAL','PARTIAL_IF_POSSIBLE','FOR_INSERT')))) then
1942
1943 -- Convert concatenated segments to array and check there are not too many
1944 --
1945 if(FND_FLEX_SERVER2.breakup_catsegs(concat_segs,
1946 kff_info.concatenated_segment_delimiter,
1947 vv_flags.values_not_ids, disp_segs, nsegs, segs) = FALSE) then
1948 goto return_error;
1949 end if;
1950
1951 IF (fnd_flex_server1.g_debug_level > 0) THEN
1952 IF (nsegs > 0) THEN
1953 catsegs := FND_FLEX_SERVER1.from_stringarray(nsegs, segs, '*');
1954 ELSE
1955 catsegs := '';
1956 END IF;
1957 FND_FLEX_SERVER1.add_debug(catsegs);
1958 END IF;
1959
1960 else
1961
1962 -- LOADID() in FULL mode.
1963 -- Look up the segment id's from the table before validating them.
1964 --
1965 if((ccid_in is null) or (ccid_in < 0)) then
1966 FND_MESSAGE.set_name('FND', 'FLEX-BAD CCID INPUT');
1967 FND_MESSAGE.set_token('CCID', to_char(ccid_in));
1968 goto return_error;
1969 end if;
1970
1971 -- Get segment mapping to code combinations table
1972 --
1973 if(FND_FLEX_SERVER2.get_struct_cols(kff_id, kff_cc.table_application_id,
1974 kff_cc.combination_table_id, nsegs, cc_cols,
1975 cc_coltypes, segtypes) = FALSE) then
1976 goto return_error;
1977 end if;
1978 IF (fnd_flex_server1.g_debug_level > 0) THEN
1979 FND_FLEX_SERVER1.add_debug(' LOADID() found segment mapping: [');
1980 for i in 1..nsegs loop
1981 FND_FLEX_SERVER1.add_debug(cc_cols(i) || ' ');
1982 --FND_FLEX_SERVER1.add_debug(cc_cols(i)||':'||cc_coltypes(i)||' ');
1983 end loop;
1984 FND_FLEX_SERVER1.add_debug('] ');
1985 END IF;
1986
1987 -- In LOADID() we have to look up the qualifier names, columns, and
1988 -- default values prior to looking for the combination. Therefore we
1989 -- have to hit the tables now to get this information. However, in
1990 -- POPID() and VALID() we can wait until after value validation and get
1991 -- the qualifier names and table columns from the returned qualifier info
1992 -- thus avoiding the extra table hit below.
1993 --
1994 if(FND_FLEX_SERVER2.get_all_segquals(kff_id, tbl_quals) = FALSE) then
1995 goto return_error;
1996 end if;
1997
1998 IF (fnd_flex_server1.g_debug_level > 0) THEN
1999 FND_FLEX_SERVER1.add_debug(' LOADID() found qualifier cols: [');
2000 for i in 1..tbl_quals.nquals loop
2001 FND_FLEX_SERVER1.add_debug(tbl_quals.derived_cols(i) || ' ');
2002 end loop;
2003 FND_FLEX_SERVER1.add_debug('] ');
2004 END IF;
2005
2006 -- Next find combination by CCID
2007 --
2008 comb_id := ccid_in;
2009
2010 nfound := find_combination(cc_struct, kff_cc, nsegs, cc_cols,
2011 cc_coltypes, segtypes, tbl_quals.nquals,
2012 tbl_quals.derived_cols, n_xcols, rq_xcols, NULL,
2013 comb_id, value_ids, tbl_derv, tbl_quals.sq_values,
2014 xcol_vals);
2015 if(nfound = 0) then
2016 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION NOT FOUND');
2017 FND_MESSAGE.set_token('CCID', ccid_in);
2018 FND_MESSAGE.set_token('APNM', flex_app_sname);
2019 FND_MESSAGE.set_token('CODE', flex_code);
2020 FND_MESSAGE.set_token('NUM', flex_num);
2021 end if;
2022 if(nfound <> 1) then
2023 goto return_error;
2024 end if;
2025
2026 -- Assign found ids to segment array in.
2027 --
2028 for i in 1..nsegs loop
2029 segs(i) := value_ids(i);
2030 end loop;
2031
2032 IF (fnd_flex_server1.g_debug_level > 0) THEN
2033 FND_FLEX_SERVER1.add_debug(' LOADID() found combination. Segids: ');
2034 IF (nsegs > 0) THEN
2035 catsegs := FND_FLEX_SERVER1.from_stringarray(nsegs, segs, '*');
2036 ELSE
2037 catsegs := '';
2038 END IF;
2039 FND_FLEX_SERVER1.add_debug(catsegs);
2040 END IF;
2041
2042 -- Next check qualifiers in the table against the vrules.
2043 -- This is done to mimic client behavior, but I think LOADID() shouldnt care.
2044 --
2045 /* bug872437. No vrule check in loadid().
2046 errcode := check_table_comb(tbl_derv,tbl_quals,v_rules,val_date,FALSE);
2047 if(errcode <> FND_FLEX_SERVER1.VV_VALID) then
2048 goto return_outvars;
2049 end if;
2050 */
2051 -- Finally, look up all the displayed values and descriptions and
2052 -- derive the qualifiers. Also check value validation against vrules
2053 -- and optionally against security rules.
2054 --
2055 -- Let this fall through to the validate_struct call and then exit.
2056
2057 end if;
2058
2059 -- LOADID() will validate ids.
2060 -- POPID() and VALID() require full value validation.
2061 -- Side effect is to fill cc_cols array with the names of the columns into
2062 -- which the segments will be inserted.
2063 --
2064 errcode := FND_FLEX_SERVER1.validate_struct(kff_id,
2065 kff_cc.table_application_id, kff_cc.combination_table_id,
2066 nsegs, segs, disp_segs, vv_flags, val_date, v_rules, user_apid,
2067 user_resp, nvals, segtypes, segcodes, cc_cols, cc_coltypes,
2068 value_dvals, value_vals, value_ids, value_descs, desc_lens,
2069 derv, drv_quals, errsegnum);
2070
2071 IF (fnd_flex_server1.g_debug_level > 0) THEN
2072 FND_FLEX_SERVER1.add_debug('SV1.validate_struct() returns errcode ' ||
2073 to_char(errcode) ||' and '|| to_char(nvals) ||
2074 ' values. SegCodes: ' || segcodes || '.' ||
2075 ' ErrSeg: ' || to_char(errsegnum));
2076 FND_FLEX_SERVER1.add_debug(' Returned arrays:');
2077 for i in 1..nvals loop
2078 FND_FLEX_SERVER1.add_debug('"' || segtypes.vs_format(i) ||
2079 to_char(segtypes.vs_maxsize(i), 'S099') ||
2080 '*' || value_dvals(i) || '*' ||
2081 cc_cols(i) || ':' || cc_coltypes(i) ||'" ');
2082 end loop;
2083 FND_FLEX_SERVER1.add_debug('Derived values: Start Date:' ||
2084 to_char(derv.start_valid, DATE_DEBUG_FMT) ||
2085 ' End Date:' ||
2086 to_char(derv.end_valid, DATE_DEBUG_FMT) ||
2087 ' Enabled=' || derv.enabled_flag ||
2088 ' Summary=' || derv.summary_flag || '.');
2089
2090 -- Print derived qualifiers to debug string
2091 --
2092 FND_FLEX_SERVER1.add_debug('Derived Qualifiers=');
2093 for i in 1..drv_quals.nquals LOOP
2094 FND_FLEX_SERVER1.add_debug('(' || drv_quals.fq_names(i) || ', '||
2095 drv_quals.sq_names(i) || ', ' ||
2096 drv_quals.sq_values(i) || ', ' ||
2097 drv_quals.derived_cols(i) || ')');
2098 end loop;
2099 END IF;
2100
2101 if (validate_off) then
2102 derv.enabled_flag := 'Y';
2103 derv.start_valid := NULL;
2104 derv.end_valid := NULL;
2105 userid_l := 2;
2106 end if;
2107
2108 -- We are done if this is LOADID() or if PARTIAL or PARTIAL_IF_POSSIBLE
2109 -- or if any errors whatsoever.
2110
2111 if((invoking_mode_l = 'L') or
2112 (validate_mode in ('PARTIAL', 'PARTIAL_IF_POSSIBLE')) or
2113 (errcode <> FND_FLEX_SERVER1.VV_VALID)) then
2114 goto return_outvars;
2115 end if;
2116
2117
2118 -- If this is not LOADID(), then we still have to get the names and colums
2119 -- for all the qualifiers stored in the table. We can get this without an
2120 -- extra table hit, by copying the qualifiers returned by validate_struct().
2121 -- Note that the values may change if the combination is in the table.
2122 --
2123 for i in 1..drv_quals.nquals loop
2124 tbl_quals.fq_names(i) := drv_quals.fq_names(i);
2125 tbl_quals.sq_names(i) := drv_quals.sq_names(i);
2126 tbl_quals.sq_values(i) := drv_quals.sq_values(i);
2127 tbl_quals.derived_cols(i) := drv_quals.derived_cols(i);
2128 end loop;
2129 tbl_quals.nquals := drv_quals.nquals;
2130 tbl_derv := derv;
2131
2132 -- Set for_insert flag if in for_insert mode
2133 --
2134 for_insert := (validate_mode = 'FOR_INSERT');
2135
2136 -- We also have to substitute the values for $PROFILES$ in the where clause
2137 -- before using it.
2138 -- Keep nice_where_cl null if in for insert mode.
2139 --
2140 if(not for_insert) then
2141 errcode:=FND_FLEX_SERVER1.parse_where_token(where_clause,nice_where_cl);
2142 if(errcode <> FND_FLEX_SERVER1.VV_VALID) then
2143 goto return_outvars;
2144 end if;
2145 end if;
2146
2147 -- In FOR_INSERT mode, it is an error if the combination exists even without
2148 -- the where clause, unless the ccid of the found combination is ccid_in
2149 -- (in which case we have re-queried an existing combination).
2150 --
2151 -- Otherwise check to see if the combination is there. Set comb_id to null
2152 -- to make sure we search by segment ids rather than by CCID.
2153 --
2154
2155 comb_id := NULL;
2156 nfound := find_combination(cc_struct, kff_cc, nvals, cc_cols,
2157 cc_coltypes, segtypes, tbl_quals.nquals,
2158 tbl_quals.derived_cols, n_xcols, rq_xcols,
2159 nice_where_cl, comb_id, value_ids, tbl_derv,
2160 tbl_quals.sq_values, xcol_vals);
2161
2162 if(for_insert) then
2163
2164 -- In for insert mode,
2165 -- if combination exists and ccid matches ccid_in we are done.
2166 -- If it exists and ccid does not match its an error.
2167 -- If combination does not exist continue on.
2168 --
2169 if((nfound = 1) and (ccid_in is not null) and (ccid_in <> 0) and
2170 (ccid_in <> -1) and (comb_id = ccid_in)) then
2171 errcode := FND_FLEX_SERVER1.VV_VALID;
2172 goto return_outvars;
2173 elsif(nfound >= 1) then
2174 FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
2175 errcode := FND_FLEX_SERVER1.VV_COMBEXISTS;
2176 goto return_outvars;
2177 else
2178 if(nfound <> 0) then
2179 goto return_error;
2180 end if;
2181 end if;
2182
2183 else
2184
2185 if(nfound > 0) then
2186 new_comb := FALSE;
2187 IF (fnd_flex_server1.g_debug_level > 0) THEN
2188 FND_FLEX_SERVER1.add_debug(' Combination already exists. CCID = ');
2189 FND_FLEX_SERVER1.add_debug(to_char(comb_id));
2190 END IF;
2191 end if;
2192
2193 -- If found, Check qualifiers in the table against the vrules and return.
2194 -- This is done to mimic client behavior.
2195 --
2196 if(nfound = 1) then
2197 errcode := check_table_comb(tbl_derv, tbl_quals,v_rules,val_date,TRUE);
2198 goto return_outvars;
2199 elsif((nfound > 1) or (nfound < 0)) then
2200 goto return_error;
2201 else
2202 null;
2203 end if;
2204
2205 end if;
2206
2207 -- If dynamic insert requested, and where clause is not null,
2208 -- then check without the where clause.
2209 --
2210 -- if((dynam_insrt) and (where_clause is not null)) then
2211 -- check where_clause all the time.
2212 --
2213 if(where_clause is not null) then
2214 comb_id := NULL;
2215 nfound := find_combination(cc_struct, kff_cc, nvals, cc_cols,
2216 cc_coltypes, segtypes, tbl_quals.nquals,
2217 tbl_quals.derived_cols, n_xcols, rq_xcols, NULL,
2218 comb_id, value_ids, tbl_derv, tbl_quals.sq_values,
2219 xcol_vals);
2220 if(nfound = 1) THEN
2221 IF (NOT parse_set_msg(where_clause_msg)) THEN
2222 FND_MESSAGE.set_name('FND', 'FLEX-WHERE CLAUSE FAILURE');
2223 FND_MESSAGE.set_token('WHERE', where_clause);
2224 END IF;
2225 errcode := FND_FLEX_SERVER1.VV_WHEREFAILURE;
2226 goto return_outvars;
2227 elsif((nfound > 1) or (nfound < 0)) then
2228 goto return_error;
2229 else
2230 null;
2231 end if;
2232 end if;
2233
2234 -- If we get to here, combination is not found.
2235 -- If dynamic insert not requested, return error.
2236 --
2237 -- This part was before the WHERE_CLAUSE check.
2238 --
2239 if(not dynam_insrt) THEN
2240 IF (NOT parse_set_msg(no_combmsg)) THEN
2241 FND_MESSAGE.set_name('FND', 'FLEX-NO DYNAMIC INSERTS');
2242 END IF;
2243 errcode := FND_FLEX_SERVER1.VV_COMBNOTFOUND;
2244 goto return_outvars;
2245 end if;
2246
2247
2248 -- Next do the cross validation if flag is set.
2249 --
2250 if(kff_info.cross_segment_validation_flag = 'Y' AND NOT validate_off) then
2251 errcode := FND_FLEX_SERVER2.cross_validate(nvals, value_vals, segtypes,
2252 val_date, kff_id, error_col);
2253 if(errcode <> FND_FLEX_SERVER1.VV_VALID) then
2254 errsegnum := find_column_index(cc_cols, nvals, error_col);
2255 IF (fnd_flex_server1.g_debug_level > 0) THEN
2256 FND_FLEX_SERVER1.add_debug(' CROSS-VALIDATION-INVALID ON SEG ' ||
2257 to_char(errsegnum) || '. ');
2258 END IF;
2259 goto return_outvars;
2260 end if;
2261 end if;
2262
2263
2264 -- Finally, Insert the combination. Feasibility already checked above.
2265 -- If combination is new, call FDFGLI on accounting flexfield.
2266 -- Else, check if combination existing in the combinations table is
2267 -- disabled, expired or violates vrules.
2268 -- Set savepoint and rollback if insert or fdfgli fails.
2269 -- Once savepoint has been set we need to go to rollback_error rather
2270 -- than return_error to make sure changes are rolled back on error.
2271 -- Also need to rollback if combination already exists to remove hash lock.
2272 -- Each savepoint outdates the previous one with the same name.
2273 --
2274 -- Bug 1531345 - Commit in AT causing problems, so allow for original insert
2275 -- if no_at is true.
2276 IF ((invoking_mode_l IN ('V')) and (defer_insrt = FALSE) AND
2277 ((for_insert) OR (no_at))) THEN
2278 --
2279 -- We are in Maintenance Form.
2280 -- We will not do the real insert, we will just lock the
2281 -- hash number, and let Maintenenace Form do the insert.
2282 -- So no need to call AutoTrans function.
2283 --
2284 IF (fnd_flex_server1.g_debug_level > 0) THEN
2285 fnd_flex_server1.add_debug('FOR_INSERT:Maintenance Form');
2286 END IF;
2287 SAVEPOINT pre_insert_comb;
2288
2289
2290 if(insert_combination(kff_id, cc_struct, for_insert, val_date,
2291 kff_info.concatenated_segment_delimiter,
2292 ccid_in, kff_cc, cc_cols, cc_coltypes,
2293 userid_l, nvals, value_ids, segtypes,
2294 derv, drv_quals, n_xcols, rq_xcols, xcol_vals,
2295 tbl_quals.sq_values, tbl_derv, new_comb,
2296 comb_id) = FALSE) then
2297 ROLLBACK TO SAVEPOINT pre_insert_comb;
2298 goto return_error;
2299 end if;
2300
2301 if(FND_FLEX_SERVER2.x_drop_cached_cv_result(kff_id, nvals, value_vals)
2302 = FALSE) then
2303 ROLLBACK TO SAVEPOINT pre_insert_comb;
2304 goto return_error;
2305 end if;
2306
2307 if(new_comb) then
2308 if((flex_app_sname ='SQLGL') and (flex_code ='GL#')
2309 and (NOT for_insert)) then
2310 if(call_fdfgli(comb_id) = FALSE) then
2311 ROLLBACK TO SAVEPOINT pre_insert_comb;
2312 goto return_error;
2313 end if;
2314 end if;
2315 errcode := FND_FLEX_SERVER1.VV_VALID;
2316 else
2317 ROLLBACK TO SAVEPOINT pre_insert_comb;
2318 if(for_insert and ((ccid_in is null) or (ccid_in = 0) or
2319 (ccid_in = -1) or (comb_id <> ccid_in))) then
2320 FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
2321 errcode := FND_FLEX_SERVER1.VV_COMBEXISTS;
2322 goto return_error;
2323 end if;
2324 errcode:=check_table_comb(tbl_derv,tbl_quals,v_rules,
2325 val_date,TRUE);
2326 end if;
2327 ELSIF ((invoking_mode_l IN ('V','P', 'G')) and (defer_insrt = FALSE) AND
2328 (NOT for_insert)) THEN
2329 --
2330 -- We are called from a foreign key form.
2331 -- In this case use the AutoTrans.
2332 --
2333 IF (fnd_flex_server1.g_debug_level > 0) THEN
2334 fnd_flex_server1.add_debug('Autonomous Transaction');
2335 END IF;
2336 if(insert_combination_at
2337 (kff_id, cc_struct, for_insert, val_date,
2338 kff_info.concatenated_segment_delimiter,
2339 ccid_in, kff_cc, cc_cols, cc_coltypes,
2340 userid_l, nvals, value_ids, value_vals, segtypes,
2341 derv, drv_quals, n_xcols, rq_xcols, xcol_vals,
2342 tbl_quals.sq_values, tbl_derv, new_comb,
2343 comb_id) = FALSE) then
2344 goto return_error;
2345 end if;
2346 IF (new_comb) THEN
2347 errcode := FND_FLEX_SERVER1.VV_VALID;
2348 ELSE
2349 if(for_insert and ((ccid_in is null) or (ccid_in = 0) or
2350 (ccid_in = -1) or (comb_id <> ccid_in))) then
2351 FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
2352 errcode := FND_FLEX_SERVER1.VV_COMBEXISTS;
2353 goto return_error;
2354 end if;
2355 errcode:=check_table_comb(tbl_derv,tbl_quals,v_rules,
2356 val_date,TRUE);
2357 END IF;
2358 elsif(invoking_mode_l in ('P', 'G')) then
2359 if(for_insert and (ccid_in is not null) and (ccid_in <> 0)) then
2360 comb_id := ccid_in;
2361 else
2362 comb_id := -1;
2363 end if;
2364 errcode := FND_FLEX_SERVER1.VV_VALID;
2365 elsif((invoking_mode_l = 'V') and (defer_insrt = TRUE)) then
2366 comb_id := -1;
2367 errcode := FND_FLEX_SERVER1.VV_VALID;
2368 else
2369 errcode := FND_FLEX_SERVER1.VV_ERROR;
2370 end if;
2371
2372 <<return_outvars>>
2373 displayed_vals := value_dvals;
2374 stored_vals := value_vals;
2375 segment_ids := value_ids;
2376 descriptions := value_descs;
2377 desc_lengths := desc_lens;
2378 seg_colnames := cc_cols;
2379 seg_coltypes := cc_coltypes;
2380 nvalidated := nvals;
2381 segment_types := segtypes;
2382 displayed_segs := disp_segs;
2383 derived_eff := derv;
2384 table_eff := tbl_derv;
2385 table_quals := tbl_quals;
2386 derived_quals := drv_quals;
2387 column_vals := xcol_vals;
2388 n_column_vals := n_xcols;
2389 seg_delimiter := kff_info.concatenated_segment_delimiter;
2390 ccid_out := comb_id;
2391 new_combination := new_comb;
2392 seg_codes := segcodes;
2393 err_segnum := errsegnum;
2394 v_status := errcode;
2395 GOTO goto_return;
2396
2397 <<return_error>>
2398 v_status := FND_FLEX_SERVER1.VV_ERROR;
2399 GOTO goto_return;
2400
2401 <<goto_return>>
2402 IF (fnd_flex_server1.g_debug_level > 0) THEN
2403 fnd_flex_server1.add_debug('END SSV.validation_engine()');
2404 END IF;
2405 RETURN;
2406
2407 EXCEPTION
2408 WHEN OTHERS then
2409 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2410 FND_MESSAGE.set_token('MSG','SSV.validation_engine() exception: '||SQLERRM);
2411 v_status := FND_FLEX_SERVER1.VV_ERROR;
2412 IF (fnd_flex_server1.g_debug_level > 0) THEN
2413 fnd_flex_server1.add_debug('EXCEPTION others SSV.validation_engine()');
2414 END IF;
2415 return;
2416 END validation_engine;
2417
2418
2419 FUNCTION x_bind_additional_where_clause(p_additional_where_clause IN VARCHAR2)
2420 RETURN BOOLEAN
2421 IS
2422 l_awc VARCHAR2(32000);
2423 l_awc_len NUMBER;
2424 l_pos1 NUMBER;
2425 l_pos2 NUMBER;
2426 l_posq NUMBER;
2427 l_bind_value VARCHAR2(32000);
2428 BEGIN
2429 l_awc := p_additional_where_clause;
2430 l_awc_len := Length(l_awc);
2431 IF (l_awc IS NULL) THEN
2432 RETURN(TRUE);
2433 END IF;
2434
2435 fnd_dsql.add_text(' and (');
2436
2437 l_pos2 := 0;
2438 l_pos1 := Instr(l_awc, '''', l_pos2 + 1, 1);
2439 WHILE (l_pos1 > 0) LOOP
2440 --
2441 -- Copy upto single quote.
2442 --
2443 fnd_dsql.add_text(Substr(l_awc, l_pos2 + 1, (l_pos1 - l_pos2) - 1));
2444
2445 --
2446 -- Find the closing quote. Handle single quote escaping.
2447 --
2448 l_posq := Instr(l_awc, '''''', l_pos1 + 1, 1);
2449 l_pos2 := Instr(l_awc, '''', l_pos1 + 1, 1);
2450
2451 WHILE (l_pos2 = l_posq) LOOP
2452 l_pos2 := Instr(l_awc, '''', l_posq + 2, 1);
2453 l_posq := Instr(l_awc, '''''', l_posq + 2, 1);
2454 END LOOP;
2455
2456 IF (l_pos2 = 0) THEN
2457 fnd_message.set_name('FND', 'FLEX-SQL MISSING QUOTE');
2458 fnd_message.set_token('CLAUSE', Substr(l_awc, 1, 1000));
2459 RETURN(FALSE);
2460 END IF;
2461
2462 fnd_dsql.add_bind(REPLACE(Substr(l_awc, l_pos1 + 1, (l_pos2-l_pos1)- 1),
2463 '''''', ''''));
2464
2465 l_pos1 := Instr(l_awc, '''', l_pos2 + 1, 1);
2466 END LOOP;
2467
2468 fnd_dsql.add_text(Substr(l_awc, l_pos2 + 1, l_awc_len - l_pos2));
2469 fnd_dsql.add_text(')');
2470
2471 RETURN(TRUE);
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2475 FND_MESSAGE.set_token('MSG','x_bind_additional_where_clause() exception: '||SQLERRM);
2476 RETURN(FALSE);
2477 END x_bind_additional_where_clause;
2478
2479
2480 /* ----------------------------------------------------------------------- */
2481 /* Finds ccid, segment ids, qualifier values, enabled and expiration */
2482 /* info and extra column values for a segment combination. */
2483 /* If the CCID input is not null, looks for the combination by CCID */
2484 /* otherwise looks for combination whose segment id values match */
2485 /* those input in the SEGS parameter. */
2486 /* If segment passed in is null, then find_combination() looks for */
2487 /* a combination with NULL in that column. */
2488 /* Returns number of combinations found or < 0 if error. */
2489 /* If combination not found, all output variables are null. */
2490 /* ----------------------------------------------------------------------- */
2491 FUNCTION find_combination(structnum IN NUMBER,
2492 combtbl IN FND_FLEX_SERVER1.CombTblInfo,
2493 nsegs IN NUMBER,
2494 combcols IN FND_FLEX_SERVER1.TabColArray,
2495 combtypes IN FND_FLEX_SERVER1.CharArray,
2496 segfmts IN FND_FLEX_SERVER1.SegFormats,
2497 nquals IN NUMBER,
2498 qualcols IN FND_FLEX_SERVER1.TabColArray,
2499 nxcols IN NUMBER,
2500 xcolnames IN FND_FLEX_SERVER1.StringArray,
2501 where_cl IN VARCHAR2,
2502 ccid IN OUT nocopy NUMBER,
2503 segids IN OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
2504 tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
2505 qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
2506 xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray)
2507 RETURN NUMBER IS
2508 offset BINARY_INTEGER;
2509 nrecords NUMBER;
2510 colvals FND_FLEX_SERVER1.StringArray;
2511 l_vc2 VARCHAR2(100);
2512
2513 BEGIN
2514
2515 -- Assumes all segment columns in combinations table are CHAR or VARCHAR2
2516 --
2517
2518 -- Build SQL statement to select ccid, enabled information, segment columns,
2519 -- qualifiers, and extra cols in that order.
2520 --
2521 fnd_dsql.init;
2522
2523 sqls := 'select to_char(' || combtbl.unique_id_column_name || '), ';
2524 sqls := sqls || 'nvl(ENABLED_FLAG, ''Y''), nvl(SUMMARY_FLAG, ''N''), ';
2525 sqls := sqls || 'to_char(START_DATE_ACTIVE, ''' ||
2526 FND_FLEX_SERVER1.DATETIME_FMT || '''), ';
2527 sqls := sqls || 'to_char(END_DATE_ACTIVE, ''' ||
2528 FND_FLEX_SERVER1.DATETIME_FMT || ''')';
2529 for i in 1..nsegs loop
2530 sqls := sqls || ', ' || FND_FLEX_SERVER1.select_clause(combcols(i),
2531 combtypes(i), FND_FLEX_SERVER1.VC_ID,
2532 segfmts.vs_format(i), segfmts.vs_maxsize(i));
2533 end loop;
2534 for i in 1..nquals loop
2535 sqls := sqls || ', ' || qualcols(i);
2536 end loop;
2537 for i in 1..nxcols loop
2538 sqls := sqls || ', ' || xcolnames(i);
2539 end loop;
2540
2541 -- If no structure column, Client only finds combinations for struct 101.
2542 sqls := sqls || ' from ' || combtbl.select_comb_from || ' where ';
2543 if(combtbl.set_defining_column_name is not null) then
2544 sqls := sqls || combtbl.set_defining_column_name;
2545 else
2546 sqls := sqls || '101';
2547 end if;
2548
2549 sqls := sqls || ' = ';
2550
2551 fnd_dsql.add_text(sqls);
2552 fnd_dsql.add_bind(structnum);
2553
2554 -- If CCID input select by CCID, otherwise select by segment ids.
2555 --
2556 if(ccid is not null) THEN
2557 fnd_dsql.add_text(' and ' || combtbl.unique_id_column_name || ' = ');
2558 fnd_dsql.add_bind(ccid);
2559 ELSE
2560 for i in 1..nsegs LOOP
2561 fnd_dsql.add_text(' and (' || combcols(i));
2562 if(segids(i) is null) THEN
2563 fnd_dsql.add_text(' is null)');
2564 else
2565 fnd_dsql.add_text(' = ');
2566 --
2567 -- This will call fnd_dsql.add_bind
2568 --
2569 fnd_flex_server1.x_compare_clause
2570 (combtypes(i),
2571 combcols(i), segids(i), FND_FLEX_SERVER1.VC_ID,
2572 segfmts.vs_format(i), segfmts.vs_maxsize(i));
2573
2574 fnd_dsql.add_text(')');
2575 end if;
2576 end loop;
2577 if(where_cl is not null) THEN
2578 --
2579 -- Parse the literals out and bind them.
2580 --
2581 IF (NOT x_bind_additional_where_clause(where_cl)) THEN
2582 RETURN(-5);
2583 END IF;
2584 end if;
2585 end if;
2586
2587 -- Do the lookup
2588 --
2589
2590 --
2591 -- This will use the sql string stored in fnd_dsql package.
2592 --
2593 nrecords := fnd_flex_server1.x_dsql_select(nsegs + nquals + nxcols + 5,
2594 colvals);
2595
2596 -- Return output information.
2597 --
2598 if(nrecords > 0) then
2599
2600 -- Copy ccid, enabled flag and dates and summary flag values to output.
2601 --
2602 ccid := to_number(colvals(1));
2603 tblderv.enabled_flag := colvals(2);
2604 tblderv.summary_flag := colvals(3);
2605 tblderv.start_valid := to_date(colvals(4),FND_FLEX_SERVER1.DATETIME_FMT);
2606 tblderv.end_valid := to_date(colvals(5), FND_FLEX_SERVER1.DATETIME_FMT);
2607
2608 -- Copy segment column values to output
2609 --
2610 offset := 5;
2611 for i in 1..nsegs loop
2612 segids(i) := colvals(i + offset);
2613 end loop;
2614
2615 -- Copy table qualifier values to output
2616 --
2617 offset := nsegs + 5;
2618 for i in 1..nquals loop
2619 qualvals(i) := colvals(i + offset);
2620 end loop;
2621
2622 -- Copy extra column values to output
2623 --
2624 offset := nsegs + nquals + 5;
2625 for i in 1..nxcols loop
2626 xcolvals(i) := colvals(i + offset);
2627 end loop;
2628
2629 else
2630
2631 -- Null out returned extra column and qualifier value arrays to avoid
2632 -- no data found error when accessing them.
2633 --
2634 for i in 1..nquals loop
2635 qualvals(i) := NULL;
2636 end loop;
2637 for i in 1..nxcols loop
2638 xcolvals(i) := NULL;
2639 end loop;
2640
2641 end if;
2642
2643 if(nrecords > 1) then
2644 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE CCID');
2645 FND_MESSAGE.set_token('CCID', to_char(ccid));
2646 end if;
2647
2648 return(nrecords);
2649
2650 EXCEPTION
2651 WHEN OTHERS then
2652 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2653 FND_MESSAGE.set_token('MSG','SSV.find_combination() exception: '||SQLERRM);
2654 return(-3);
2655
2656 END find_combination;
2657
2658 /* ----------------------------------------------------------------------- */
2659 /* Inserts combination of segment ids into code combinations table. */
2660 /* */
2661 /* If combination has been created since last checked, get it from */
2662 /* the combinations table along with all the other table values for */
2663 /* the qualifiers, extra columns, and enabled and effective dates. */
2664 /* Determine names of qualifier columns from the derived qualifiers */
2665 /* input to this function. Returns table qualifier values = derived */
2666 /* qualifier values, table effecitivity information = derived */
2667 /* effectivity information, and null for extra columns for new combs. */
2668 /* returns newcomb = TRUE if just created this combination. */
2669 /* If segment column is of type number, then does the default */
2670 /* conversion on the character representation of the segment id. */
2671 /* If segment column is of type date, does default to_date() */
2672 /* conversion for non-translatable date, time and date-time value */
2673 /* sets, but does correctly-formatted conversions for translatable */
2674 /* dates, times and date times. This should emulate client behavior. */
2675 /* */
2676 /* If maintmode = TRUE, then user has called this in FOR_INSERT mode. */
2677 /* In that case do not insert combination if it does not already */
2678 /* exist, just return the ccid. If editing an existing combination */
2679 /* the ccid_inp will be the ccid of the combination being edited. */
2680 /* In that case, do not create a new ccid, but just return ccid_inp. */
2681 /* If the ccid_inp is not null, 0 or -1 consider it to be ok to use. */
2682 /* */
2683 /* Calls user PLSQL validation function after locking the */
2684 /* combination, getting a new CCID, and double-checking to make */
2685 /* sure nobody else has created the combination since we last checked.*/
2686 /* This is done at this time to maintain exact backward compatibility */
2687 /* with the client c-code. It would make more sense to do the user */
2688 /* validation before calling insert_combination(), but then more than */
2689 /* one user might call the user validation function with the same */
2690 /* combination, and somebody might be relying on this corner-case */
2691 /* functionality. */
2692 /* If the user PLSQL validation function returns FALSE, */
2693 /* insert_combination() returns FALSE indicating a fatal error */
2694 /* condition. In this case the error message is already loaded */
2695 /* in FND_MESSAGE. */
2696 /* A SAVEPOINT must be issued externally to this function and */
2697 /* a rollback must occur if insert_combination returns an error */
2698 /* of if it returns new_comb = FALSE to unlock the hash number. */
2699 /* */
2700 /* Returns TRUE on success or FALSE and sets message on error. */
2701 /* ----------------------------------------------------------------------- */
2702 FUNCTION insert_combination(fstruct IN FND_FLEX_SERVER1.FlexStructId,
2703 structnum IN NUMBER,
2704 maintmode IN BOOLEAN,
2705 v_date IN DATE,
2706 seg_delim IN VARCHAR2,
2707 ccid_inp IN NUMBER,
2708 combtbl IN FND_FLEX_SERVER1.CombTblInfo,
2709 combcols IN FND_FLEX_SERVER1.TabColArray,
2710 combtypes IN FND_FLEX_SERVER1.CharArray,
2711 user_id IN NUMBER,
2712 nsegs IN NUMBER,
2713 segids_in IN FND_FLEX_SERVER1.ValueIdArray,
2714 segfmts IN FND_FLEX_SERVER1.SegFormats,
2715 dvalues IN FND_FLEX_SERVER1.DerivedVals,
2716 dquals IN FND_FLEX_SERVER1.Qualifiers,
2717 nxcols IN NUMBER,
2718 xcolnames IN FND_FLEX_SERVER1.StringArray,
2719 xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
2720 qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
2721 tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
2722 newcomb OUT nocopy BOOLEAN,
2723 ccid_out OUT nocopy NUMBER) RETURN BOOLEAN IS
2724
2725 deadlock EXCEPTION;
2726 ccid NUMBER;
2727 nfound NUMBER;
2728 hash_num NUMBER;
2729 hash_number NUMBER;
2730 ccid_string VARCHAR2(50);
2731 segids FND_FLEX_SERVER1.ValueIdArray;
2732
2733 PRAGMA EXCEPTION_INIT(deadlock, -60);
2734
2735 BEGIN
2736
2737 -- For debugging...
2738 --
2739
2740 IF (fnd_flex_server1.g_debug_level > 0) THEN
2741 FND_FLEX_SERVER1.add_debug(to_char(nsegs) ||
2742 ' segs passed to insert_combination().');
2743 END IF;
2744
2745 -- First lock this combination and prevent other users from inserting.
2746 -- Generate an almost unique hash number from the segments ids.
2747 -- and lock that row in the hash table to prevent other users from
2748 -- inserting the same combination. The commit will drop the locks.-
2749 -- GL requires row share lock on combinations table to prevent them from
2750 -- getting an exclusive lock for their processing.
2751
2752 segids := segids_in;
2753
2754 sqls := 'lock table ' || combtbl.application_table_name;
2755 sqls := sqls || ' in row share mode';
2756
2757 fnd_dsql.init;
2758 fnd_dsql.add_text(sqls);
2759 if(FND_FLEX_SERVER1.x_dsql_execute < 0) then
2760 return(FALSE);
2761 end if;
2762
2763 -- Next compute the hash number that is to be locked.
2764 --
2765 hash_number := hash_segs(nsegs, segids);
2766 if(hash_number < 0) then
2767 return(FALSE);
2768 end if;
2769
2770 IF (fnd_flex_server1.g_debug_level > 0) THEN
2771 FND_FLEX_SERVER1.add_debug('Hash value = '||to_char(hash_number)||'.');
2772 END IF;
2773 SELECT hash_value INTO hash_num FROM fnd_flex_hash
2774 WHERE hash_value = hash_number FOR UPDATE;
2775
2776 -- Double-check to see if it has been created. No where clause this time.
2777 --
2778 nfound := find_combination(structnum, combtbl, nsegs, combcols,
2779 combtypes, segfmts, dquals.nquals,
2780 dquals.derived_cols, nxcols, xcolnames, NULL,
2781 ccid, segids, tblderv, qualvals, xcolvals);
2782 if(nfound <> 0) then
2783 if(nfound >= 1) then
2784 newcomb := FALSE;
2785 ccid_out := ccid;
2786 return(TRUE);
2787 end if;
2788 return(FALSE);
2789 end if;
2790
2791 -- Get unique code combination ID from a sequence if we dont already have it.
2792 -- If ccid_inp is 0 or null or -1 we need to generate a new ccid.
2793 -- Must use dynamic SQL here since ccid comes from the application table
2794 -- with a '_S' suffix. Could do without dynamic sql if we had a fixed
2795 -- sequence name.
2796 --
2797 if(maintmode and (ccid_inp is not null) and (ccid_inp <> 0) and
2798 (ccid_inp <> -1)) then
2799 ccid := ccid_inp;
2800 else
2801 sqls := 'select to_char(' || combtbl.application_table_name;
2802 sqls := sqls || '_S.NEXTVAL) from dual';
2803 fnd_dsql.init;
2804 fnd_dsql.add_text(sqls);
2805 if(FND_FLEX_SERVER1.x_dsql_select_one(ccid_string) <> 1) then
2806 return(FALSE);
2807 end if;
2808 ccid := to_number(ccid_string);
2809 if(ccid > MAX_CCID) then
2810 FND_MESSAGE.set_name('FND', 'FLEX-CCID TOO BIG');
2811 FND_MESSAGE.set_token('CCIDLIMIT', to_char(MAX_CCID));
2812 FND_MESSAGE.set_token('SEQNAME', combtbl.application_table_name||'_S');
2813 return(FALSE);
2814 end if;
2815 end if;
2816
2817 -- Call user validation function now if desired. Bail if error.
2818 --
2819 if(userval_on) then
2820 if(NOT call_userval(fstruct, v_date, nsegs, seg_delim, segids_in)) then
2821 return(FALSE);
2822 end if;
2823 end if;
2824
2825 -- If not in maintainence mode do the insert, otherwise skip to the end.
2826 --
2827 if(NOT maintmode) then
2828
2829 -- Build a SQL statement to do the insert.
2830 --
2831 fnd_dsql.init;
2832 sqls := 'insert into ' || combtbl.application_table_name || ' (';
2833 sqls := sqls || combtbl.unique_id_column_name;
2834 if(combtbl.set_defining_column_name is not null) then
2835 sqls := sqls || ', ' || combtbl.set_defining_column_name;
2836 end if;
2837 sqls := sqls || ', ENABLED_FLAG, SUMMARY_FLAG, ';
2838 sqls := sqls || 'START_DATE_ACTIVE, END_DATE_ACTIVE, ';
2839 sqls := sqls || 'LAST_UPDATE_DATE, LAST_UPDATED_BY';
2840 for i in 1..dquals.nquals loop
2841 sqls := sqls || ', ' || dquals.derived_cols(i);
2842 end loop;
2843 for i in 1..nsegs loop
2844 if(segids(i) is not null) then
2845 sqls := sqls || ', ' || combcols(i);
2846 end if;
2847 end loop;
2848 sqls := sqls || ') values (';
2849
2850 -- So far the table name and the column names.
2851 fnd_dsql.add_text(sqls);
2852
2853 fnd_dsql.add_bind(ccid);
2854
2855 if(combtbl.set_defining_column_name is not null) THEN
2856 fnd_dsql.add_text(',');
2857 fnd_dsql.add_bind(structnum);
2858 end if;
2859
2860 fnd_dsql.add_text(',');
2861 fnd_dsql.add_bind(dvalues.enabled_flag);
2862
2863 fnd_dsql.add_text(',');
2864 fnd_dsql.add_bind(dvalues.summary_flag);
2865
2866 fnd_dsql.add_text(',');
2867 fnd_dsql.add_bind(dvalues.start_valid);
2868
2869 fnd_dsql.add_text(',');
2870 fnd_dsql.add_bind(dvalues.end_valid);
2871
2872 fnd_dsql.add_text(',sysdate,');
2873 fnd_dsql.add_bind(user_id);
2874
2875 for i in 1..dquals.nquals LOOP
2876 fnd_dsql.add_text(',');
2877 fnd_dsql.add_bind(dquals.sq_values(i));
2878 end loop;
2879
2880 for i in 1..nsegs loop
2881 if(segids(i) is not null) THEN
2882 fnd_dsql.add_text(',');
2883 --
2884 -- This will call fnd_dsql.add_bind
2885 --
2886 fnd_flex_server1.x_compare_clause
2887 (combtypes(i),
2888 combcols(i), segids(i), FND_FLEX_SERVER1.VC_ID,
2889 segfmts.vs_format(i), segfmts.vs_maxsize(i));
2890 end if;
2891 end loop;
2892 fnd_dsql.add_text(')');
2893
2894 --
2895 -- Finally do the insert
2896 --
2897 if(FND_FLEX_SERVER1.x_dsql_execute < 0) then
2898 return(FALSE);
2899 end if;
2900 end if;
2901
2902 -- Return all out variables. If comb was found in table these were set
2903 -- above.
2904 --
2905 IF (fnd_flex_server1.g_debug_level > 0) THEN
2906 FND_FLEX_SERVER1.add_debug(' Returning ccid = '||to_char(ccid) || '. ');
2907 END IF;
2908 ccid_out := ccid;
2909 newcomb := TRUE;
2910 tblderv := dvalues;
2911 for i in 1..nxcols loop
2912 xcolvals(i) := NULL;
2913 end loop;
2914 for i in 1..dquals.nquals loop
2915 qualvals(i) := dquals.sq_values(i);
2916 end loop;
2917 return(TRUE);
2918
2919 EXCEPTION
2920 WHEN NO_DATA_FOUND then
2921 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2922 FND_MESSAGE.set_token('MSG', 'Hash value ' ||
2923 to_char(hash_number) || ' not found.');
2924 return(FALSE);
2925 WHEN TOO_MANY_ROWS then
2926 FND_MESSAGE.set_token('MSG', 'Hash value ' ||
2927 to_char(hash_number) || ' not unique.');
2928 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2929 return(FALSE);
2930 WHEN TIMEOUT_ON_RESOURCE then
2931 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2932 FND_MESSAGE.set_token('MSG', 'Timeout waiting for lock on hash table.');
2933 return(FALSE);
2934 WHEN deadlock then
2935 FND_MESSAGE.set_name('FND', 'FLEX-HASH DEADLOCK');
2936 return(FALSE);
2937 WHEN OTHERS then
2938 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2939 FND_MESSAGE.set_token('MSG','insert_combination() exception: '||SQLERRM);
2940 return(FALSE);
2941
2942 END insert_combination;
2943
2944
2945 /* ----------------------------------------------------------------------- */
2946 /* Sums ASCII values of all characters mod NHASH. ASCII returns code */
2947 /* up to 64K for multi-byte characters. Multiply code for each char */
2948 /* by the characters position to make non-commutative */
2949 /* ----------------------------------------------------------------------- */
2950
2951 FUNCTION hash_segs(n IN NUMBER, segs IN FND_FLEX_SERVER1.ValueIdArray)
2952 RETURN NUMBER IS
2953
2954 hval NUMBER;
2955 cval NUMBER;
2956 seglen NUMBER;
2957 chr_count NUMBER;
2958
2959 BEGIN
2960
2961 hval := 0;
2962 chr_count := 1;
2963 for segnum in 1..n loop
2964 if(segs(segnum) is not null) then
2965 seglen := LENGTH(segs(segnum));
2966 for i in 1..seglen loop
2967 cval := ASCII(SUBSTR(segs(segnum), i, 1));
2968 hval := hval + cval*chr_count;
2969 chr_count := chr_count + 1;
2970 end loop;
2971 end if;
2972 end loop;
2973 return(MOD(hval, NHASH));
2974
2975 EXCEPTION
2976 WHEN OTHERS then
2977 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2978 FND_MESSAGE.set_token('MSG', 'hash_segs() exception: ' || SQLERRM);
2979 return(-1);
2980
2981 END hash_segs;
2982
2983 /* ----------------------------------------------------------------------- */
2984 /* Checks the expiration date, enabled flag and vrules against the */
2985 /* combination returned from the combinations tables. This must be */
2986 /* done in case user has updated the combinations table to differ */
2987 /* from the derived values. Only checks the expiration and enabled */
2988 /* flags if check_effective flag is TRUE. */
2989 /* Error code indicating result of validation. VV_VALID means all ok */
2990 /* ----------------------------------------------------------------------- */
2991
2992 FUNCTION check_table_comb(t_dval IN FND_FLEX_SERVER1.DerivedVals,
2993 t_quals IN FND_FLEX_SERVER1.Qualifiers,
2994 v_rules IN FND_FLEX_SERVER1.Vrules,
2995 v_date IN DATE,
2996 check_effective IN BOOLEAN) RETURN NUMBER IS
2997 BEGIN
2998
2999 -- Print table segments, qualifiers, extra columns and effectivity info.
3000 --
3001 IF (fnd_flex_server1.g_debug_level > 0) THEN
3002 FND_FLEX_SERVER1.add_debug('Check expiration/vrules on table quals.');
3003
3004 FND_FLEX_SERVER1.add_debug('Qualifiers: ');
3005 for i in 1..t_quals.nquals loop
3006 FND_FLEX_SERVER1.add_debug('(' || t_quals.sq_names(i) || ' = ');
3007 FND_FLEX_SERVER1.add_debug(t_quals.sq_values(i) || ') ');
3008 end loop;
3009
3010 FND_FLEX_SERVER1.add_debug('Enabled: ' || t_dval.enabled_flag);
3011 FND_FLEX_SERVER1.add_debug
3012 (' Starts: ' || to_char(t_dval.start_valid,
3013 FND_FLEX_SERVER1.DATETIME_FMT));
3014 FND_FLEX_SERVER1.add_debug
3015 (' Ends: ' || to_char(t_dval.end_valid,
3016 FND_FLEX_SERVER1.DATETIME_FMT));
3017 FND_FLEX_SERVER1.add_debug(' Summary Flag: ' || t_dval.summary_flag);
3018 END IF;
3019 -- Check if combination turned on
3020 --
3021 if(check_effective) then
3022 if(t_dval.enabled_flag <> 'Y') then
3023 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION DISABLED');
3024 return(FND_FLEX_SERVER1.VV_VALUES);
3025 end if;
3026 if((v_date is not null) and
3027 ((Trunc(v_date) < Trunc(nvl(t_dval.start_valid, v_date))) or
3028 (Trunc(v_date) > Trunc(nvl(t_dval.end_valid, v_date))))) then
3029 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION HAS EXPIRED');
3030 return(FND_FLEX_SERVER1.VV_VALUES);
3031 end if;
3032 end if;
3033
3034 -- Check vrules.
3035 --
3036 return(FND_FLEX_SERVER1.check_comb_vrules(v_rules, t_quals,
3037 t_dval.summary_flag));
3038 EXCEPTION
3039 WHEN OTHERS then
3040 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3041 FND_MESSAGE.set_token('MSG','check_table_comb() exception: '||SQLERRM);
3042 return(FND_FLEX_SERVER1.VV_ERROR);
3043
3044 END check_table_comb;
3045
3046 /* ----------------------------------------------------------------------- */
3047 /* Determines the segment number of the given column name. */
3048 /* Returns null without erroring if the name is not on the list. */
3049 /* ----------------------------------------------------------------------- */
3050
3051 FUNCTION find_column_index(column_list IN FND_FLEX_SERVER1.TabColArray,
3052 column_count IN NUMBER,
3053 colname IN VARCHAR2) RETURN NUMBER IS
3054 colnum NUMBER;
3055
3056 BEGIN
3057 -- Set colnum only if name found.
3058 --
3059 for i in 1..column_count loop
3060 if(UPPER(colname) = UPPER(column_list(i))) then
3061 colnum := i;
3062 exit;
3063 end if;
3064 end loop;
3065 return(colnum);
3066
3067 END find_column_index;
3068
3069 /* ----------------------------------------------------------------------- */
3070 /* Concatenate Values into a string for return to the client. */
3071 /* If only one value displayed does not substitute CR for delimiter. */
3072 /* Concatenates only displayed values. */
3073 /* ----------------------------------------------------------------------- */
3074
3075 FUNCTION concatenate_values(nvals IN NUMBER,
3076 vals IN FND_FLEX_SERVER1.ValueArray,
3077 displ FND_FLEX_SERVER1.DisplayedSegs,
3078 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3079 n_displayed NUMBER;
3080 str FND_FLEX_SERVER1.StringArray;
3081 BEGIN
3082 IF (fnd_flex_server1.g_debug_level > 0) THEN
3083 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_values()');
3084 END IF;
3085 n_displayed := 0;
3086 for i in 1..nvals loop
3087 if((i <= displ.n_segflags) and displ.segflags(i)) then
3088 n_displayed := n_displayed + 1;
3089 str(n_displayed) := vals(i);
3090 end if;
3091 end loop;
3092
3093 IF (n_displayed > 1) THEN
3094 return(FND_FLEX_SERVER1.from_stringarray(n_displayed, str, delimiter));
3095 ELSIF (n_displayed = 1) then
3096 return(str(1));
3097 ELSE
3098 RETURN (NULL);
3099 end if;
3100 END concatenate_values;
3101
3102 /* ----------------------------------------------------------------------- */
3103 /* Concatenate Value ids into a string for return to the client. */
3104 /* If only one id input does not substitute CR for delimiter. */
3105 /* Concatenates all ids whether or not their segments are displayed. */
3106 /* ----------------------------------------------------------------------- */
3107
3108 FUNCTION concatenate_ids(nids IN NUMBER,
3109 ids IN FND_FLEX_SERVER1.ValueIdArray,
3110 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3111 str FND_FLEX_SERVER1.StringArray;
3112 BEGIN
3113 IF (fnd_flex_server1.g_debug_level > 0) THEN
3114 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_ids()');
3115 END IF;
3116 if(nids = 1) then
3117 return(ids(1));
3118 else
3119 for i in 1..nids loop
3120 str(i) := ids(i);
3121 end loop;
3122 IF (nids > 1) THEN
3123 return(FND_FLEX_SERVER1.from_stringarray(nids, str, delimiter));
3124 ELSIF (nids = 1) THEN
3125 RETURN(str(1));
3126 ELSE
3127 RETURN NULL;
3128 END IF;
3129 end if;
3130 END concatenate_ids;
3131
3132 /* ----------------------------------------------------------------------- */
3133 /* Concatenate Value descriptions to string for return to the client. */
3134 /* Only returns descriptions for displayed segments. */
3135 /* Truncates descriptions to lengths specified by flex structure. */
3136 /* NOTE: Lengths are all in BYTES not characters. */
3137 /* If only one value displayed does not substitute CR for delimiter. */
3138 /* ----------------------------------------------------------------------- */
3139
3140 FUNCTION concatenate_descriptions(ndescs IN NUMBER,
3141 descs IN FND_FLEX_SERVER1.ValueDescArray,
3142 displ IN FND_FLEX_SERVER1.DisplayedSegs,
3143 lengths IN FND_FLEX_SERVER1.NumberArray,
3144 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3145 n_displayed NUMBER;
3146 str FND_FLEX_SERVER1.StringArray;
3147 BEGIN
3148 IF (fnd_flex_server1.g_debug_level > 0) THEN
3149 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_descriptions()');
3150 END IF;
3151 n_displayed := 0;
3152 for i in 1..ndescs loop
3153 if((i <= displ.n_segflags) and displ.segflags(i)) then
3154 n_displayed := n_displayed + 1;
3155 str(n_displayed) := SUBSTRB(descs(i), 1, lengths(i));
3156 end if;
3157 end loop;
3158
3159 IF (n_displayed > 1) THEN
3160 return(FND_FLEX_SERVER1.from_stringarray(n_displayed, str, delimiter));
3161 ELSIF (n_displayed = 1) then
3162 return(str(1));
3163 ELSE
3164 RETURN (NULL);
3165 end if;
3166 END concatenate_descriptions;
3167
3168 /* ----------------------------------------------------------------------- */
3169 /* Concatenate Value descriptions to string for return to the client. */
3170 /* Only returns descriptions for displayed segments. */
3171 /* Does not truncate descriptions. */
3172 /* If only one value displayed does not substitute CR for delimiter. */
3173 /* ----------------------------------------------------------------------- */
3174
3175 FUNCTION concatenate_fulldescs(ndescs IN NUMBER,
3176 descs IN FND_FLEX_SERVER1.ValueDescArray,
3177 displ IN FND_FLEX_SERVER1.DisplayedSegs,
3178 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3179 n_displayed NUMBER;
3180 str FND_FLEX_SERVER1.StringArray;
3181 BEGIN
3182 IF (fnd_flex_server1.g_debug_level > 0) THEN
3183 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_fulldesc()');
3184 END IF;
3185 n_displayed := 0;
3186 for i in 1..ndescs loop
3187 if((i <= displ.n_segflags) and displ.segflags(i)) then
3188 n_displayed := n_displayed + 1;
3189 str(n_displayed) := descs(i);
3190 end if;
3191 end loop;
3192
3193 IF (n_displayed > 1) THEN
3194 return(FND_FLEX_SERVER1.from_stringarray(n_displayed, str, delimiter));
3195 ELSIF (n_displayed = 1) then
3196 return(str(1));
3197 ELSE
3198 RETURN (NULL);
3199 end if;
3200 END concatenate_fulldescs;
3201
3202 /* ----------------------------------------------------------------------- */
3203 /* Concatenate segment formats to string for return to the client. */
3204 /* ----------------------------------------------------------------------- */
3205
3206 FUNCTION concatenate_segment_formats(segfmts IN FND_FLEX_SERVER1.SegFormats)
3207 RETURN VARCHAR2 IS
3208 catfmts VARCHAR2(200);
3209
3210 BEGIN
3211 for i in 1..segfmts.nsegs loop
3212 catfmts := catfmts || segfmts.vs_format(i);
3213 catfmts := catfmts || to_char(segfmts.vs_maxsize(i), 'S099') || ' ';
3214 end loop;
3215 return(catfmts);
3216 END concatenate_segment_formats;
3217
3218 /* ----------------------------------------------------------------------- */
3219 /* Returns derived values requested as a concatenated string. */
3220 /* Can request SUMMARY_FLAG, START_DATE_ACTIVE and END_DATE_ACTIVE, */
3221 /* but not ENABLED_FLAG. */
3222 /* */
3223 /* Any qualifiers not found in the list of derived qualifiers */
3224 /* will result in a NULL being returned in the appropriate place. */
3225 /* Return string has qualifier values separated by TERMINATOR. */
3226 /* ----------------------------------------------------------------------- */
3227
3228 FUNCTION ret_derived(d_quals IN FND_FLEX_SERVER1.Qualifiers,
3229 drv IN FND_FLEX_SERVER1.DerivedVals,
3230 d_rqst IN DerivedRqst) RETURN VARCHAR2 IS
3231
3232 str VARCHAR2(2000);
3233
3234 BEGIN
3235
3236 str := NULL;
3237 for i in 1..d_rqst.nrqstd loop
3238 if(d_rqst.sq_names(i) = 'SUMMARY_FLAG') then
3239 str := str || drv.summary_flag;
3240 elsif(d_rqst.sq_names(i) = 'START_DATE_ACTIVE') then
3241 str := str || to_char(drv.start_valid, DRV_DATE_FMT);
3242 elsif(d_rqst.sq_names(i) = 'END_DATE_ACTIVE') then
3243 str := str || to_char(drv.end_valid, DRV_DATE_FMT);
3244 else
3245 for j in 1..d_quals.nquals loop
3246 if(d_quals.sq_names(j) = d_rqst.sq_names(i)) then
3247 str := str || d_quals.sq_values(j);
3248 exit;
3249 end if;
3250 end loop;
3251 end if;
3252 str := str || FND_FLEX_SERVER1.TERMINATOR;
3253 end loop;
3254 if(str is not null) then
3255 str := SUBSTR(str, 1, LENGTH(str) - LENGTH(FND_FLEX_SERVER1.TERMINATOR));
3256 end if;
3257 return(str);
3258
3259 END ret_derived;
3260
3261 /* ----------------------------------------------------------------------- */
3262 /* Returns value attributes requested as a concatenated string. */
3263 /* Implemented the same as ret_derived() except checks flexfield */
3264 /* qualifier names too. */
3265 /* Can request SUMMARY_FLAG, START_DATE_ACTIVE and END_DATE_ACTIVE, */
3266 /* but not ENABLED_FLAG. */
3267 /* */
3268 /* Any qualifiers not found in the list of derived qualifiers */
3269 /* will result in a NULL being returned in the appropriate place. */
3270 /* Return string has qualifier values separated by TERMINATOR. */
3271 /* ----------------------------------------------------------------------- */
3272 FUNCTION ret_valatts(d_quals IN FND_FLEX_SERVER1.Qualifiers,
3273 drv IN FND_FLEX_SERVER1.DerivedVals,
3274 v_rqst IN ValattRqst) RETURN VARCHAR2 IS
3275
3276 str VARCHAR2(2000);
3277
3278 BEGIN
3279
3280 str := NULL;
3281 for i in 1..v_rqst.nrqstd loop
3282 if(v_rqst.fq_names(i) is null) then
3283 if(v_rqst.sq_names(i) = 'SUMMARY_FLAG') then
3284 str := str || drv.summary_flag;
3285 elsif(v_rqst.sq_names(i) = 'START_DATE_ACTIVE') then
3286 str := str || to_char(drv.start_valid, DRV_DATE_FMT);
3287 elsif(v_rqst.sq_names(i) = 'END_DATE_ACTIVE') then
3288 str := str || to_char(drv.end_valid, DRV_DATE_FMT);
3289 end if;
3290 else
3291 for j in 1..d_quals.nquals loop
3292 if((d_quals.sq_names(j) = v_rqst.sq_names(i)) and
3293 (d_quals.fq_names(j) = v_rqst.fq_names(i))) then
3294 str := str || d_quals.sq_values(j);
3295 exit;
3296 end if;
3297 end loop;
3298 end if;
3299 str := str || FND_FLEX_SERVER1.TERMINATOR;
3300 end loop;
3301 if(str is not null) then
3302 str := SUBSTR(str, 1, LENGTH(str) - LENGTH(FND_FLEX_SERVER1.TERMINATOR));
3303 end if;
3304 return(str);
3305
3306 END ret_valatts;
3307
3308 /* ----------------------------------------------------------------------- */
3309 /* Parses string which requests value attribue values. */
3310 /* Input string of the form: */
3311 /* 'flexfield qualifier1\nsegment qualifier1\0flexfield qualifier2...'*/
3312 /* Requested segment qualifier names converted to upper case. */
3313 /* Returns number of non-null value attributes requested or < 0 if err*/
3314 /* ----------------------------------------------------------------------- */
3315 FUNCTION parse_va_rqst(s IN VARCHAR2, var OUT nocopy ValattRqst) RETURN NUMBER IS
3316
3317 nsegs NUMBER;
3318 fqname_end NUMBER;
3319 fqsq_names FND_FLEX_SERVER1.StringArray;
3320 sq_name VARCHAR2(30);
3321
3322 BEGIN
3323
3324 -- Make sure it is not too big
3325 --
3326 if(LENGTHB(s) > MAX_ARG_LEN) then
3327 FND_MESSAGE.set_name('FND', 'FLEX-ARGUMENT TOO LONG');
3328 FND_MESSAGE.set_token('ARG', 'VALATT');
3329 FND_MESSAGE.set_token('MAXLEN', to_char(MAX_ARG_LEN));
3330 return(-6);
3331 end if;
3332
3333 IF (s IS NOT NULL) THEN
3334 nsegs := FND_FLEX_SERVER1.to_stringarray2(s, FND_FLEX_SERVER1.TERMINATOR,
3335 fqsq_names);
3336 ELSE
3337 nsegs := 0;
3338 END IF;
3339
3340 for i in 1..nsegs loop
3341 if(fqsq_names(i) is not null) then
3342 fqname_end := INSTR(fqsq_names(i), FND_FLEX_SERVER1.SEPARATOR);
3343 if(fqname_end <= 0) then
3344 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VALATT NOSEP');
3345 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3346 FND_MESSAGE.set_token('NAME', fqsq_names(i));
3347 return(-1);
3348 elsif(fqname_end > 31) then
3349 FND_MESSAGE.set_name('FND', 'FLEX-QUALIFIER TOO LONG');
3350 FND_MESSAGE.set_token('TOKNAME', 'VALATT');
3351 FND_MESSAGE.set_token('NAME', SUBSTR(fqsq_names(i), 1, 30));
3352 return(-2);
3353 else
3354 var.fq_names(i) := UPPER(SUBSTR(fqsq_names(i), 1, fqname_end - 1));
3355 sq_name := UPPER(SUBSTR(fqsq_names(i),
3356 fqname_end + LENGTH(FND_FLEX_SERVER1.SEPARATOR), 30));
3357 if(sq_name is null) then
3358 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3359 FND_MESSAGE.set_token('TOKNAME', 'VALATT');
3360 return(-3);
3361 end if;
3362 var.sq_names(i) := sq_name;
3363 end if;
3364 else
3365 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3366 FND_MESSAGE.set_token('TOKNAME', 'VALATT');
3367 return(-4);
3368 end if;
3369 end loop;
3370 var.nrqstd := nsegs;
3371 return(nsegs);
3372
3373 EXCEPTION
3374 WHEN OTHERS then
3375 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3376 FND_MESSAGE.set_token('MSG', 'parse_va_rqst() exception: ' || SQLERRM);
3377 return(-5);
3378
3379 END parse_va_rqst;
3380
3381 /* ----------------------------------------------------------------------- */
3382 /* Parses string which requests derived segment qualifier values. */
3383 /* Input string of the form: 'seg qual name1\0seg qual name2...' */
3384 /* Requested segment qualifier names converted to upper case. */
3385 /* Returns number of non-null qualifier names requested or < 0 if err */
3386 /* ----------------------------------------------------------------------- */
3387 FUNCTION parse_drv_rqst(s IN VARCHAR2, dr OUT nocopy DerivedRqst) RETURN NUMBER IS
3388
3389 nsegs NUMBER;
3390 sqnames FND_FLEX_SERVER1.StringArray;
3391
3392 BEGIN
3393
3394 -- Make sure it is not too big
3395 --
3396 if(LENGTHB(s) > MAX_ARG_LEN) then
3397 FND_MESSAGE.set_name('FND', 'FLEX-ARGUMENT TOO LONG');
3398 FND_MESSAGE.set_token('ARG', 'DERIVED');
3399 FND_MESSAGE.set_token('MAXLEN', to_char(MAX_ARG_LEN));
3400 return(-6);
3401 end if;
3402
3403 IF (s IS NOT NULL) THEN
3404 nsegs := FND_FLEX_SERVER1.to_stringarray2(s, FND_FLEX_SERVER1.TERMINATOR,
3405 sqnames);
3406 ELSE
3407 nsegs := 0;
3408 END IF;
3409
3410 for i in 1..nsegs loop
3411 if(sqnames(i) is not null) then
3412 dr.sq_names(i) := UPPER(SUBSTR(sqnames(i), 1, 30));
3413 else
3414 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3415 FND_MESSAGE.set_token('TOKNAME', 'DERIVED');
3416 return(-1);
3417 end if;
3418 end loop;
3419 dr.nrqstd := nsegs;
3420 return(nsegs);
3421
3422 EXCEPTION
3423 WHEN OTHERS then
3424 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3425 FND_MESSAGE.set_token('MSG', 'parse_drv_rqst() exception: '||SQLERRM);
3426 return(-2);
3427
3428 END parse_drv_rqst;
3429
3430 /* ----------------------------------------------------------------------- */
3431 /* Parses vrule string. Returns number of vrules found or sets error */
3432 /* message string and returns < 0 if error. */
3433 /* */
3434 /* Vrule string format: */
3435 /* 'FLEXFIELD QUALIFIER NAME\nSEGMENT QUALIFIER NAME\n */
3436 /* {I[nclude] | E[xclude]}\nAPPL=appl short name;NAME=Message name\n */
3437 /* value1\nvalue2...\0' */
3438 /* */
3439 /* SEPARATOR = '\n', TERMINATOR = '\0'. */
3440 /* */
3441 /* Multiple vrules can be separated by TERMINATOR. TERMINATOR is not */
3442 /* required after last vrule. If 'NAME=' is missing the entire */
3443 /* message string is considered the error message. If 'APPL=' or */
3444 /* the ';' is missing the application short name 'FND' is used. */
3445 /* */
3446 /* Eliminates whitespace from around component names. */
3447 /* Limits vrule string length to < MAX_VRULE_LEN bytes. */
3448 /* ----------------------------------------------------------------------- */
3449 FUNCTION parse_vrules(s IN VARCHAR2,
3450 vr OUT nocopy FND_FLEX_SERVER1.Vrules) RETURN NUMBER IS
3451
3452 n NUMBER;
3453 bgn NUMBER;
3454 endp NUMBER;
3455 seplen NUMBER;
3456 vrulstr_len NUMBER;
3457 msg_begin NUMBER;
3458 msg_end NUMBER;
3459 tokn_len NUMBER;
3460 endtok NUMBER;
3461 ieval VARCHAR2(240);
3462 tokn VARCHAR2(2000);
3463 sq_name VARCHAR2(30);
3464 ie_flag VARCHAR2(4);
3465
3466 BEGIN
3467
3468 n := 0;
3469 bgn := 1;
3470 if(s is null) then
3471 vr.nvrules := 0;
3472 return(0);
3473 end if;
3474
3475 vrulstr_len := LENGTH(s);
3476 seplen := LENGTH(FND_FLEX_SERVER1.SEPARATOR);
3477
3478 while (bgn <= vrulstr_len) loop
3479 n := n + 1;
3480
3481 -- Flexfield qualifier name
3482 --
3483 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3484 if(endp <= 0) then
3485 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3486 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3487 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3488 return(-1);
3489 else
3490 tokn := UPPER(LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS));
3491 if((tokn is not null) and (LENGTHB(tokn) > 30)) then
3492 FND_MESSAGE.set_name('FND', 'FLEX-QUALIFIER TOO LONG');
3493 FND_MESSAGE.set_token('TOKNAME', 'VRULE');
3494 FND_MESSAGE.set_token('NAME', SUBSTR(s, bgn, 30));
3495 return(-1);
3496 end if;
3497 vr.fq_names(n) := tokn;
3498 bgn := endp + seplen;
3499 end if;
3500
3501 -- Segment qualifier name
3502 --
3503 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3504 if(endp <= 0) then
3505 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3506 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3507 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3508 return(-1);
3509 else
3510 tokn := UPPER(LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS));
3511 if(tokn is null) then
3512 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3513 FND_MESSAGE.set_token('TOKNAME', 'VRULE');
3514 return(-1);
3515 elsif(LENGTHB(tokn) > 30) then
3516 FND_MESSAGE.set_name('FND', 'FLEX-QUALIFIER TOO LONG');
3517 FND_MESSAGE.set_token('TOKNAME', 'VRULE');
3518 FND_MESSAGE.set_token('NAME', SUBSTR(s, bgn, 30));
3519 return(-1);
3520 else
3521 sq_name := tokn;
3522 vr.sq_names(n) := tokn;
3523 end if;
3524 bgn := endp + seplen;
3525 end if;
3526
3527 -- Include/Exclude indicator
3528 --
3529 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3530 if(endp <= 0) then
3531 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3532 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3533 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3534 return(-1);
3535 end if;
3536 tokn := LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS);
3537 if(tokn is null) then
3538 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO IE');
3539 FND_MESSAGE.set_token('NAME', sq_name);
3540 return(-1);
3541 end if;
3542 ie_flag := SUBSTR(tokn, 1, 1);
3543 vr.ie_flags(n) := ie_flag;
3544 if(ie_flag not in ('I', 'E')) then
3545 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BAD IE');
3546 FND_MESSAGE.set_token('NAME', sq_name);
3547 return(-1);
3548 end if;
3549 bgn := endp + seplen;
3550
3551 -- Error Message and Application short name
3552 --
3553 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3554 if(endp <= 0) then
3555 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3556 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3557 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3558 return(-1);
3559 end if;
3560 tokn := LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS);
3561 if(tokn is null) then
3562 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO MSG');
3563 FND_MESSAGE.set_token('NAME', sq_name);
3564 return(-1);
3565 elsif(LENGTHB(tokn) > 100) then
3566 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BIG MSG');
3567 FND_MESSAGE.set_token('NAME', sq_name);
3568 return(-1);
3569 else
3570 msg_begin := INSTR(tokn, 'NAME=');
3571 if(msg_begin <= 0) then
3572 vr.app_names(n) := NULL;
3573 vr.err_names(n) := tokn;
3574 else
3575 msg_begin := msg_begin + 5;
3576 if(LENGTH(tokn) < msg_begin) then
3577 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO MSGNAME');
3578 FND_MESSAGE.set_token('NAME', sq_name);
3579 return(-1);
3580 end if;
3581 vr.err_names(n) := SUBSTR(tokn, msg_begin);
3582 msg_begin := INSTR(tokn, 'APPL=');
3583 msg_end := INSTR(tokn, ';');
3584 if((msg_begin > 0) and (msg_end > 0) and
3585 (msg_end - msg_begin > 5)) then
3586 msg_begin := msg_begin + 5;
3587 if(msg_end - msg_begin > 50) then
3588 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BIG APNAME');
3589 FND_MESSAGE.set_token('NAME', sq_name);
3590 return(-1);
3591 end if;
3592 vr.app_names(n) := SUBSTR(tokn, msg_begin, msg_end - msg_begin);
3593 else
3594 vr.app_names(n) := 'FND';
3595 end if;
3596 end if;
3597 bgn := endp + seplen;
3598 end if;
3599
3600 -- Values to include or exclude.
3601 -- Parsed into format where each value is surrounded by the SEPARATOR.
3602 -- First put everything to the terminator into tokn, then parse tokn.
3603 --
3604 endp := INSTR(s, FND_FLEX_SERVER1.TERMINATOR, bgn);
3605 if(endp <= 0) then
3606 endp := vrulstr_len + 1;
3607 end if;
3608 tokn := LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS);
3609 if(tokn is null) then
3610 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO VALS');
3611 FND_MESSAGE.set_token('NAME', sq_name);
3612 return(-1);
3613 end if;
3614 if(LENGTHB(tokn) > 236) then
3615 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BIG VALSTR');
3616 FND_MESSAGE.set_token('NAME', sq_name);
3617 return(-1);
3618 end if;
3619 bgn := 1;
3620 ieval := FND_FLEX_SERVER1.SEPARATOR;
3621 tokn_len := LENGTH(tokn);
3622 while(bgn <= tokn_len) loop
3623 endtok := INSTR(tokn, FND_FLEX_SERVER1.SEPARATOR, bgn);
3624 if(endtok <= 0) then
3625 endtok := tokn_len + 1;
3626 end if;
3627 ieval := ieval || LTRIM(RTRIM(SUBSTR(tokn, bgn, endtok-bgn),
3628 BLANKS), BLANKS);
3629 ieval := ieval || FND_FLEX_SERVER1.SEPARATOR;
3630 bgn := endtok + seplen;
3631 end loop;
3632 vr.cat_vals(n) := ieval;
3633 bgn := endp + seplen;
3634
3635 end loop;
3636
3637 vr.nvrules := n;
3638
3639 return(n);
3640
3641 EXCEPTION
3642 WHEN OTHERS then
3643 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3644 FND_MESSAGE.set_token('MSG', 'parse_vrules() exception: ' || SQLERRM);
3645 return(-2);
3646
3647 END parse_vrules;
3648
3649
3650 /*
3651 * Parse and set the custom error message.
3652 * Synatx is 'APPL=<application_short_name>NAME=<message_name>'
3653 */
3654 FUNCTION parse_set_msg(p_msg IN VARCHAR2) RETURN BOOLEAN
3655 IS
3656 l_appl VARCHAR2(2000);
3657 l_name VARCHAR2(2000);
3658 l_msg VARCHAR2(2000);
3659 l_pos_beg NUMBER;
3660 l_pos_end NUMBER;
3661 BEGIN
3662 l_msg := p_msg;
3663 IF (l_msg IS NULL) THEN
3664 RETURN FALSE;
3665 END IF;
3666
3667 l_pos_beg := Instr(l_msg, 'APPL=');
3668 IF (l_pos_beg = 0) THEN
3669 --
3670 -- No APPL token.
3671 --
3672 RETURN FALSE;
3673 END IF;
3674 l_pos_beg := l_pos_beg + Length('APPL=');
3675
3676 l_pos_end := Instr(l_msg, ';NAME=');
3677 IF (l_pos_end = 0) THEN
3678 --
3679 -- No NAME token.
3680 --
3681 RETURN FALSE;
3682 END IF;
3683
3684 IF (l_pos_end = l_pos_beg) THEN
3685 --
3686 -- No APPL value.
3687 --
3688 RETURN FALSE;
3689 END IF;
3690
3691 --
3692 -- Application Short Name
3693 --
3694 l_appl := Substr(l_msg, l_pos_beg, l_pos_end - l_pos_beg);
3695
3696 l_pos_beg := l_pos_end + Length(';NAME=');
3697 l_pos_end := Length(l_msg) + 1;
3698
3699 IF (l_pos_end = l_pos_beg) THEN
3700 --
3701 -- No NAME value.
3702 --
3703 RETURN FALSE;
3704 END IF;
3705
3706 --
3707 -- Message Name.
3708 --
3709 l_name := Substr(l_msg, l_pos_beg, l_pos_end - l_pos_beg);
3710
3711 fnd_message.set_name(l_appl, l_name);
3712
3713 RETURN TRUE;
3714 EXCEPTION
3715 WHEN OTHERS THEN
3716 RETURN FALSE;
3717 END parse_set_msg;
3718
3719
3720
3721
3722 /* ----------------------------------------------------------------------- */
3723 /* Function to interpret DISPLAYED token using the approach of */
3724 /* selecting all segments and their associated flexfield qualifiers */
3725 /* in a single outer join and then interpreting the tokens for all */
3726 /* segments at once. This requires the fewest possible database rows */
3727 /* retrieved and only a single select statement. */
3728 /* ----------------------------------------------------------------------- */
3729
3730 FUNCTION parse_displayed(fstruct IN FND_FLEX_SERVER1.FlexStructId,
3731 token_str IN VARCHAR2,
3732 dispsegs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs)
3733 RETURN BOOLEAN IS
3734
3735 n_segs NUMBER;
3736 fq_table FND_FLEX_SERVER1.FlexQualTable;
3737 seg_disp FND_FLEX_SERVER1.CharArray;
3738 seg_rqd FND_FLEX_SERVER1.CharArray;
3739 tokenmap FND_FLEX_SERVER1.BooleanArray;
3740
3741 BEGIN
3742
3743 -- Initialize returned segment display map.
3744 --
3745 dispsegs.n_segflags := 0;
3746
3747 -- Get flexfield qualifier mapping to segments.
3748 --
3749 if(FND_FLEX_SERVER2.get_qualsegs(fstruct, n_segs, seg_disp,
3750 seg_rqd, fq_table) = FALSE) then
3751 return(FALSE);
3752 end if;
3753
3754
3755 if(evaluate_token(token_str, n_segs, fq_table, tokenmap) = FALSE) then
3756 return(FALSE);
3757 end if;
3758
3759 -- Still need to merge the displayed map obtained from the DISPLAYED token
3760 -- alone (n_tokappl) with the display map from the flex structure and
3761 -- return the completed map.
3762 --
3763 IF (fnd_flex_server1.g_debug_level > 0) THEN
3764 g_debug_text := 'Displayed Map=';
3765 END IF;
3766 for i in 1..n_segs loop
3767 if((seg_disp(i) = 'Y') and tokenmap(i)) then
3768 dispsegs.segflags(i) := TRUE;
3769 IF (fnd_flex_server1.g_debug_level > 0) THEN
3770 g_debug_text := g_debug_text || 'Y';
3771 END IF;
3772 else
3773 dispsegs.segflags(i) := FALSE;
3774 IF (fnd_flex_server1.g_debug_level > 0) THEN
3775 g_debug_text := g_debug_text || 'N';
3776 END IF;
3777 end if;
3778 end loop;
3779 IF (fnd_flex_server1.g_debug_level > 0) THEN
3780 FND_FLEX_SERVER1.add_debug(g_debug_text || '.');
3781 END IF;
3782 dispsegs.n_segflags := n_segs;
3783 return(TRUE);
3784
3785 EXCEPTION
3786 WHEN OTHERS then
3787 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3788 FND_MESSAGE.set_token('MSG', 'parse_displayed() exception: '||SQLERRM);
3789 return(FALSE);
3790
3791 END parse_displayed;
3792
3793 /* ------------------------------------------------------------------------- */
3794 /* Interpret the string token passed into FND_KEY_FLEX.DEFINE() call */
3795 /* on the client using the table of flexfield qualifiers for each */
3796 /* segment which is returned from get_segquals(). */
3797 /* Returns an array which has one element for each enabled segment */
3798 /* where the entry in the array is TRUE if and only if an odd number */
3799 /* of token elements apply to the segment. */
3800 /* Returns TRUE on success or FALSE on error. */
3801 /* ------------------------------------------------------------------------- */
3802
3803 FUNCTION evaluate_token(token_str IN VARCHAR2,
3804 n_segs IN NUMBER,
3805 fq_tab IN FND_FLEX_SERVER1.FlexQualTable,
3806 token_map OUT nocopy FND_FLEX_SERVER1.BooleanArray)
3807 RETURN BOOLEAN IS
3808
3809 n_toks NUMBER;
3810 segindex NUMBER;
3811 toks FND_FLEX_SERVER1.StringArray;
3812 s_ntokappl FND_FLEX_SERVER1.NumberArray;
3813
3814 BEGIN
3815
3816 -- Break up token into individual components
3817 --
3818 IF (token_str IS NOT NULL) THEN
3819 n_toks := FND_FLEX_SERVER1.to_stringarray2(token_str,
3820 FND_FLEX_SERVER1.TERMINATOR, toks);
3821 ELSE
3822 n_toks := 0;
3823 END IF;
3824
3825 -- Save some debug info
3826 --
3827 IF (fnd_flex_server1.g_debug_level > 0) THEN
3828 g_debug_text := 'Tokens:';
3829 for i in 1..n_toks loop
3830 g_debug_text := g_debug_text || toks(i) || ' ';
3831 end loop;
3832 FND_FLEX_SERVER1.add_debug(g_debug_text);
3833 END IF;
3834
3835 -- Initialize number of applicable tokens for each segment to 0.
3836 --
3837 for i in 1..n_segs loop
3838 s_ntokappl(i) := 0;
3839 end loop;
3840
3841 IF (fnd_flex_server1.g_debug_level > 0) THEN
3842 FND_FLEX_SERVER1.add_debug('Init ' || to_char(n_segs) || ' segs. ');
3843 END IF;
3844
3845 -- Now interpret each token to create the displayed map.
3846 -- If token is 'ALL' then just toggle the displayed bit for all segments.
3847 -- If token is a number then toggle the displayed bit for that seg number.
3848 -- Otherwise, toggle the displayed bits for all segments for which that
3849 -- qualifier applies. Exit if any errors.
3850 --
3851 for i in 1..n_toks loop
3852 if(toks(i) is null) then
3853 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN DUI NULL');
3854 return(FALSE);
3855 end if;
3856 if(toks(i) = 'ALL') then
3857 for j in 1..n_segs loop
3858 s_ntokappl(j) := s_ntokappl(j) + 1;
3859 end loop;
3860 elsif(FND_FLEX_SERVER1.isa_number(toks(i), segindex)) then
3861 if((segindex < 1) or (segindex > n_segs)) then
3862 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN DUI BAD SEGNUM');
3863 FND_MESSAGE.set_token('SEGNUM', to_char(segindex));
3864 return(FALSE);
3865 end if;
3866 s_ntokappl(segindex) := s_ntokappl(segindex) + 1;
3867 else
3868 segindex := 0;
3869 for k in 1..fq_tab.nentries loop
3870 if((fq_tab.fq_names(k) is not null) and
3871 (toks(i) = fq_tab.fq_names(k))) then
3872 segindex := fq_tab.seg_indexes(k);
3873 s_ntokappl(segindex) := s_ntokappl(segindex) + 1;
3874 end if;
3875 end loop;
3876 if(segindex = 0) then
3877 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN DUI BAD QUAL');
3878 FND_MESSAGE.set_token('QTOKEN', toks(i));
3879 return(FALSE);
3880 end if;
3881 end if;
3882 end loop;
3883
3884 for i in 1..n_segs loop
3885 token_map(i) := (MOD(s_ntokappl(i), 2) = 1);
3886 end loop;
3887 return(TRUE);
3888
3889 EXCEPTION
3890 WHEN OTHERS then
3891 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3892 FND_MESSAGE.set_token('MSG', 'evaluate_token() exception: ' ||SQLERRM);
3893 return(FALSE);
3894
3895 END evaluate_token;
3896
3897 /* ------------------------------------------------------------------------- */
3898 /* Turns on or off calling of user PLSQL validation just before insert */
3899 /* of new combination. */
3900 /* ------------------------------------------------------------------------- */
3901 PROCEDURE enable_user_validation(Y_or_N IN VARCHAR2) IS
3902 BEGIN
3903 if(Y_or_N = 'Y') then
3904 userval_on := TRUE;
3905 else
3906 userval_on := FALSE;
3907 end if;
3908 END enable_user_validation;
3909
3910 /* ------------------------------------------------------------------------- */
3911 /* Turns on or off fdfgli calling after insert of new combination */
3912 /* in accounting flexfield. */
3913 /* ----------------------------------------------------------------------- */
3914 PROCEDURE enable_fdfgli(Y_or_N IN VARCHAR2) IS
3915 BEGIN
3916 if(Y_or_N = 'Y') then
3917 fdfgli_on := TRUE;
3918 else
3919 fdfgli_on := FALSE;
3920 end if;
3921 END enable_fdfgli;
3922
3923 /* ----------------------------------------------------------------------- */
3924 /* Calls FDFGLI if enabled. */
3925 /* Processes error messages returned by FDFGLI and handles any */
3926 /* possible exceptions. Returns TRUE on success or sets error */
3927 /* and returns FALSE on error. */
3928 /* */
3929 /* Note: FDFGLI called using dynamic SQL so no error at compile time */
3930 /* if GL_FLEX_INSERT_PKG is not there. */
3931 /* ----------------------------------------------------------------------- */
3932 FUNCTION call_fdfgli(ccid IN NUMBER) RETURN BOOLEAN IS
3933
3934 cursornum INTEGER;
3935 nprocessed INTEGER;
3936 sqlstr VARCHAR2(500);
3937 yes_or_no VARCHAR2(1);
3938 i_status VARCHAR2(1);
3939 i_industry VARCHAR2(1);
3940
3941 BEGIN
3942
3943 IF (fnd_flex_server1.g_debug_level > 0) THEN
3944 FND_FLEX_SERVER1.add_debug('Entering call_fdfgli() ');
3945 END IF;
3946
3947 if(fdfgli_on) then
3948
3949 -- Do not call FDFGLI just exit with TRUE if GL is not fully installed
3950 --
3951 if(FND_INSTALLATION.get(101, 101, i_status, i_industry) = FALSE) then
3952 FND_MESSAGE.set_name('FND', 'FLEX-CANT_GET_INSTALL');
3953 return(FALSE);
3954 end if;
3955 if((i_status is null) or (i_status <> 'I')) then
3956 return(TRUE);
3957 end if;
3958
3959 sqlstr := 'BEGIN if(gl_flex_insert_pkg.fdfgli(:n)) then :r := ''Y'';';
3960 sqlstr := sqlstr || ' else :r := ''N''; end if; END;';
3961 IF (fnd_flex_server1.g_debug_level > 0) THEN
3962 FND_FLEX_SERVER1.add_debug(sqlstr);
3963 END IF;
3964 cursornum := dbms_sql.open_cursor;
3965 dbms_sql.parse(cursornum, sqlstr, dbms_sql.v7);
3966 dbms_sql.bind_variable(cursornum, ':n', ccid);
3967 dbms_sql.bind_variable(cursornum, ':r', yes_or_no, 1);
3968 nprocessed := dbms_sql.execute(cursornum);
3969 dbms_sql.variable_value(cursornum, ':r', yes_or_no);
3970 IF (fnd_flex_server1.g_debug_level > 0) THEN
3971 FND_FLEX_SERVER1.add_debug('Dynamic SQL called FDFGLI and returned '||
3972 yes_or_no || '. ');
3973 END IF;
3974 dbms_sql.close_cursor(cursornum);
3975 return(yes_or_no = 'Y');
3976 end if;
3977
3978 return(TRUE);
3979
3980 EXCEPTION
3981 WHEN OTHERS then
3982 -- bug#4072642 -- maximum open cursors exceeded
3983 if dbms_sql.is_open(cursornum) then
3984 dbms_sql.close_cursor(cursornum);
3985 end if;
3986 if((SQLCODE = -6550) and (INSTR(SQLERRM, 'PLS-00201') > 0)) then
3987 FND_MESSAGE.set_name('FND', 'FLEX-FDFGLI MISSING');
3988 FND_MESSAGE.set_token('MSG', SQLERRM);
3989 else
3990 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3991 FND_MESSAGE.set_token('MSG', 'fdfgli() exception: '||SQLERRM);
3992 end if;
3993 return(FALSE);
3994
3995 END call_fdfgli;
3996
3997 /* ----------------------------------------------------------------------- */
3998 /* Calls user PLSQL validation function FND_FLEX_PLSQL.validate(). */
3999 /* Requires FND_FLEX_PLSQL stub package for compilation. */
4000 /* The validate() function is called with both concatenated and */
4001 /* individual segment ids in order they are defined in the flexfield */
4002 /* structure. It will return TRUE if combination passes all user */
4003 /* defined validation rules, or FALSE on error or if it does not */
4004 /* pass user-defined validation. */
4005 /* Saves args input to user validation in uvdbg string. */
4006 /* ----------------------------------------------------------------------- */
4007 FUNCTION call_userval(fstruct IN FND_FLEX_SERVER1.FlexStructId,
4008 vdate IN DATE,
4009 nids IN NUMBER,
4010 delim IN VARCHAR2,
4011 segids IN FND_FLEX_SERVER1.ValueIdArray)
4012 RETURN BOOLEAN IS
4013 errmsg VARCHAR2(2000);
4014 catids VARCHAR2(2000);
4015 ids FND_FLEX_SERVER1.ValueIdArray;
4016
4017 BEGIN
4018
4019 -- Only do this for key flexfields
4020 --
4021 if(not fstruct.isa_key_flexfield) then
4022 return(TRUE);
4023 end if;
4024
4025 -- First concatenate ids, and populate the ids() array with ids or NULL
4026 -- while adding the input args to the debug string.
4027 --
4028 catids := concatenate_ids(nids, segids, delim);
4029 for i in 1..30 loop
4030 if(i <= nids) then
4031 ids(i) := segids(i);
4032 else
4033 ids(i) := NULL;
4034 end if;
4035 end loop;
4036
4037 -- Next save the debugging info to the standard server debug string.
4038 -- Note this is different from the client which will save it to a
4039 -- file fdfplv.log. Use errmsg as temporary string to store debug info.
4040 --
4041 errmsg := ' Calling FND_FLEX_PLSQL.validate(';
4042 errmsg := errmsg || to_char(fstruct.application_id) || ', ';
4043 errmsg := errmsg || fstruct.id_flex_code || ', ';
4044 errmsg := errmsg || to_char(fstruct.id_flex_num) || ', ';
4045 errmsg := errmsg || to_char(vdate, FND_FLEX_SERVER1.DATETIME_FMT) || ', ';
4046 errmsg := errmsg || delim || ', ';
4047
4048 IF (fnd_flex_server1.g_debug_level > 0) THEN
4049 FND_FLEX_SERVER1.add_debug(errmsg);
4050 FND_FLEX_SERVER1.add_debug(catids);
4051 FND_FLEX_SERVER1.add_debug(') ');
4052 END IF;
4053 errmsg := NULL;
4054
4055 -- Now call the function
4056 --
4057 if(FND_FLEX_PLSQL.validate(fstruct.application_id, fstruct.id_flex_code,
4058 fstruct.id_flex_num, vdate, delim, catids, nids, ids(1), ids(2),
4059 ids(3), ids(4), ids(5), ids(6), ids(7), ids(8), ids(9),
4060 ids(10), ids(11), ids(12), ids(13), ids(14), ids(15), ids(16),
4061 ids(17), ids(18), ids(19), ids(20), ids(21), ids(22), ids(23),
4062 ids(24), ids(25), ids(26), ids(27), ids(28), ids(29), ids(30),
4063 errmsg) = FALSE) then
4064 FND_MESSAGE.set_name('FND', 'FLEX-PLSQL VALIDATION ERROR');
4065 FND_MESSAGE.set_token('ERROR_MSG', errmsg);
4066 return(FALSE);
4067 end if;
4068 return(TRUE);
4069
4070 EXCEPTION
4071 WHEN OTHERS then
4072 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4073 FND_MESSAGE.set_token('MSG', 'call_userval() exception: '||SQLERRM);
4074 return(FALSE);
4075
4076 END call_userval;
4077
4078 /* ----------------------------------------------------------------------- */
4079 /* Returns number of SQL statements created during last call */
4080 /* to validate(). Use in conjunction with get_sql(). */
4081 /* ----------------------------------------------------------------------- */
4082 FUNCTION get_nsql RETURN NUMBER IS
4083 BEGIN
4084 return(FND_FLEX_SERVER1.get_nsql_internal);
4085 END get_nsql;
4086
4087 /* ----------------------------------------------------------------------- */
4088 /* Returns SQL statements created during last call */
4089 /* to validate(). Use in conjunction with get_sql(). */
4090 /* ----------------------------------------------------------------------- */
4091 FUNCTION get_sql(statement_num IN NUMBER,
4092 statement_piece IN NUMBER DEFAULT 1) RETURN VARCHAR2 IS
4093 BEGIN
4094 return(FND_FLEX_SERVER1.get_sql_internal(statement_num, statement_piece));
4095 EXCEPTION
4096 WHEN OTHERS then
4097 return('get_sql() exception: ' || SQLERRM);
4098 END get_sql;
4099
4100 /* ----------------------------------------------------------------------- */
4101 /* Returns the debug string. */
4102 /* ----------------------------------------------------------------------- */
4103 FUNCTION get_debug(stringnum IN NUMBER) RETURN VARCHAR2 IS
4104 BEGIN
4105 return(FND_FLEX_SERVER1.get_debug_internal(stringnum));
4106 EXCEPTION
4107 WHEN OTHERS then
4108 return('get_debug() exception: ' || SQLERRM);
4109 END get_debug;
4110
4111 /* ----------------------------------------------------------------------- */
4112
4113 -- ==================================================
4114 PROCEDURE do_dynamic_insert_for_java(p_application_id IN NUMBER,
4115 p_id_flex_code IN VARCHAR2,
4116 p_id_flex_num IN NUMBER,
4117 p_application_table_name IN VARCHAR2,
4118 p_segment_delimiter IN VARCHAR2,
4119 p_segment_count IN NUMBER,
4120 p_validation_date IN DATE,
4121 p_start_date_active IN DATE,
4122 p_end_date_active IN DATE,
4123 p_insert_sql IN VARCHAR2,
4124 p_insert_sql_binds IN VARCHAR2,
4125 p_select_sql IN VARCHAR2,
4126 p_select_sql_binds IN VARCHAR2,
4127 x_ccid OUT nocopy NUMBER,
4128 x_encoded_error OUT nocopy VARCHAR2)
4129 IS
4130 l_is_new VARCHAR2(100);
4131 BEGIN
4132 do_dynamic_insert_for_java
4133 (p_application_id => p_application_id,
4134 p_id_flex_code => p_id_flex_code,
4135 p_id_flex_num => p_id_flex_num,
4136 p_application_table_name => p_application_table_name,
4137 p_segment_delimiter => p_segment_delimiter,
4138 p_segment_count => p_segment_count,
4139 p_validation_date => p_validation_date,
4140 p_start_date_active => p_start_date_active,
4141 p_end_date_active => p_end_date_active,
4142 p_insert_sql => p_insert_sql,
4143 p_insert_sql_binds => p_insert_sql_binds,
4144 p_select_sql => p_select_sql,
4145 p_select_sql_binds => p_select_sql_binds,
4146 x_ccid => x_ccid,
4147 x_is_new => l_is_new,
4148 x_encoded_error => x_encoded_error);
4149 END do_dynamic_insert_for_java;
4150
4151 PROCEDURE do_dynamic_insert_for_java(p_application_id IN NUMBER,
4152 p_id_flex_code IN VARCHAR2,
4153 p_id_flex_num IN NUMBER,
4154 p_application_table_name IN VARCHAR2,
4155 p_segment_delimiter IN VARCHAR2,
4156 p_segment_count IN NUMBER,
4157 p_validation_date IN DATE,
4158 p_start_date_active IN DATE,
4159 p_end_date_active IN DATE,
4160 p_insert_sql IN VARCHAR2,
4161 p_insert_sql_binds IN VARCHAR2,
4162 p_select_sql IN VARCHAR2,
4163 p_select_sql_binds IN VARCHAR2,
4164 x_ccid OUT nocopy NUMBER,
4165 x_is_new OUT nocopy VARCHAR2,
4166 x_encoded_error OUT nocopy VARCHAR2)
4167 IS
4168 PRAGMA AUTONOMOUS_TRANSACTION;
4169
4170 l_func_name VARCHAR2(100);
4171 l_ff_structure FND_FLEX_SERVER1.flexstructid;
4172 l_bind_count NUMBER;
4173 l_binds fnd_flex_server1.valuearray;
4174 l_newline VARCHAR2(10);
4175 l_ccid NUMBER;
4176 l_cursor NUMBER;
4177 l_hash_value NUMBER;
4178 l_segment_ids fnd_flex_server1.valueidarray;
4179
4180 --
4181 -- Temporary number and varchar2 buffers.
4182 --
4183 l_num NUMBER;
4184 l_vc2 VARCHAR2(32000);
4185 temp VARCHAR2(32000);
4186 BEGIN
4187 l_func_name := 'SSV.do_dynamic_insert_for_java()';
4188 l_newline := fnd_global.newline;
4189 x_is_new := 'U';
4190 --
4191 -- Lock the combination table.
4192 --
4193 BEGIN
4194 EXECUTE IMMEDIATE ('LOCK TABLE ' || p_application_table_name ||
4195 ' IN ROW SHARE MODE');
4196 EXCEPTION
4197 WHEN OTHERS THEN
4198 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4199 fnd_message.set_token('MSG', l_func_name ||
4200 ' is unable to lock table ' ||
4201 p_application_table_name || '. ' || l_newline ||
4202 'SQLERRM: ' || Sqlerrm);
4203 GOTO return_failure;
4204 END;
4205
4206 --
4207 -- Get the concatenated combination.
4208 -- End of Insert SQL Binds is concatenated combination.
4209 -- Final result is something like '10\n20\nA\n'
4210 --
4211 -- First step gets concat comb with beginning newline character
4212 l_vc2 := Substr(p_insert_sql_binds,
4213 Instr(p_insert_sql_binds,
4214 l_newline, -1, p_segment_count + 1));
4215 -- Second/final step removes initial newline and puts concat comb in l_vc2
4216 temp := Substr(l_vc2, 2);
4217 l_vc2 := temp;
4218
4219 --
4220 -- Parse the segment ids.
4221 --
4222 FOR i IN 1..p_segment_count LOOP
4223 l_num := Instr(l_vc2, l_newline, 1, 1);
4224 l_segment_ids(i) := Substr(l_vc2, 1, l_num - 1);
4225 l_vc2 := Substr(l_vc2, l_num + 1);
4226 END LOOP;
4227
4228 --
4229 -- Generate a hash value.
4230 --
4231 l_hash_value := hash_segs(p_segment_count, l_segment_ids);
4232
4233 --
4234 -- Lock the hash table.
4235 --
4236 BEGIN
4237 SELECT hash_value
4238 INTO l_num
4239 FROM fnd_flex_hash
4240 WHERE hash_value = l_hash_value
4241 FOR UPDATE;
4242 EXCEPTION
4243 WHEN OTHERS THEN
4244 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4245 fnd_message.set_token('MSG', l_func_name ||
4246 ' is unable to lock FND_FLEX_HASH. ' ||
4247 'Hash Value: ' || l_hash_value || l_newline ||
4248 'SQLERRM: ' || Sqlerrm);
4249 GOTO return_failure;
4250 END;
4251
4252 --
4253 -- We locked Comb. Table and the Hash table, now let's check the
4254 -- Comb. Table one more time.
4255 --
4256
4257 --
4258 -- See KeyFlexfield.getCCIDfromDynamicInsertion() for a sample SELECT SQL.
4259 --
4260
4261 --
4262 -- Parse SELECT SQL bind values.
4263 --
4264 l_bind_count := 0;
4265 l_vc2 := p_select_sql_binds;
4266 WHILE (l_vc2 IS NOT NULL) LOOP
4267 l_bind_count := l_bind_count + 1;
4268 l_num := Instr(l_vc2, l_newline, 1, 1);
4269 l_binds(l_bind_count) := Substr(l_vc2, 1, l_num - 1);
4270 l_vc2 := Substr(l_vc2, l_num + 1);
4271 END LOOP;
4272
4273 --
4274 -- Check if the combination is already in the table.
4275 --
4276 BEGIN
4277 l_cursor := dbms_sql.open_cursor;
4278 dbms_sql.parse(l_cursor, p_select_sql, dbms_sql.native);
4279 FOR i IN 1..l_bind_count LOOP
4280 dbms_sql.bind_variable(l_cursor, 'S' || i , l_binds(i));
4281 END LOOP;
4282 dbms_sql.define_column(l_cursor, 1, l_ccid);
4283 l_num := dbms_sql.execute_and_fetch(l_cursor, TRUE);
4284
4285 --
4286 -- Combination already exists.
4287 --
4288 dbms_sql.column_value(l_cursor, 1, l_ccid);
4289 dbms_sql.close_cursor(l_cursor);
4290 x_is_new := 'N';
4291 GOTO return_success;
4292 EXCEPTION
4293 WHEN no_data_found THEN
4294 --
4295 -- Combination doesn't exist, continue to INSERT.
4296 --
4297 dbms_sql.close_cursor(l_cursor);
4298 WHEN OTHERS THEN
4299 dbms_sql.close_cursor(l_cursor);
4300 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4301 fnd_message.set_token('MSG', l_func_name ||
4302 ' is unable to select from table ' ||
4303 p_application_table_name || '. ' || l_newline ||
4304 'SQLERRM: ' || Sqlerrm);
4305 GOTO return_failure;
4306 END;
4307
4308 --
4309 -- Call user validation function.
4310 --
4311 IF (userval_on) THEN
4312 l_ff_structure.isa_key_flexfield := TRUE;
4313 l_ff_structure.application_id := p_application_id;
4314 l_ff_structure.id_flex_code := p_id_flex_code;
4315 l_ff_structure.id_flex_num := p_id_flex_num;
4316
4317 IF (NOT call_userval(l_ff_structure,
4318 p_validation_date,
4319 p_segment_count,
4320 p_segment_delimiter,
4321 l_segment_ids)) THEN
4322 GOTO return_failure;
4323 END IF;
4324 END IF;
4325
4326
4327 --
4328 -- Now we are ready to insert.
4329 --
4330
4331 --
4332 -- See KeyFlexfield.getCCIDfromDynamicInsertion() for a sample INSERT SQL.
4333 --
4334
4335 --
4336 -- Parse INSERT SQL bind values.
4337 --
4338 l_bind_count := 0;
4339 l_vc2 := p_insert_sql_binds;
4340 WHILE (l_vc2 IS NOT NULL) LOOP
4341 l_bind_count := l_bind_count + 1;
4342 l_num := Instr(l_vc2, l_newline, 1, 1);
4343 l_binds(l_bind_count) := Substr(l_vc2, 1, l_num - 1);
4344 l_vc2 := Substr(l_vc2, l_num + 1);
4345 END LOOP;
4346
4347 --
4348 -- Get the next CCID.
4349 --
4350 BEGIN
4351 EXECUTE IMMEDIATE ('SELECT ' || p_application_table_name ||
4352 '_S.NEXTVAL FROM dual')
4353 INTO l_ccid;
4354 EXCEPTION
4355 WHEN OTHERS THEN
4356 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4357 fnd_message.set_token('MSG', l_func_name ||
4358 ' is unable to get next value from sequence ' ||
4359 p_application_table_name || '_S. ' ||l_newline||
4360 'SQLERRM: ' || Sqlerrm);
4361 GOTO return_failure;
4362 END;
4363
4364 --
4365 -- INSERT the combination.
4366 --
4367 BEGIN
4368 l_cursor := dbms_sql.open_cursor;
4369 dbms_sql.parse(l_cursor, p_insert_sql, dbms_sql.native);
4370
4371 --
4372 -- First bind is the CCID.
4373 --
4374 dbms_sql.bind_variable(l_cursor, 'CCID', l_ccid);
4375
4376 --
4377 -- Bind Start and End Dates.
4378 --
4379 dbms_sql.bind_variable(l_cursor, 'START_DATE_ACTIVE', p_start_date_active);
4380 dbms_sql.bind_variable(l_cursor, 'END_DATE_ACTIVE', p_end_date_active);
4381
4382 --
4383 -- Bind the rest.
4384 --
4385 FOR i IN 1..l_bind_count LOOP
4386 dbms_sql.bind_variable(l_cursor, 'I' || i, l_binds(i));
4387 END LOOP;
4388 l_num := dbms_sql.execute(l_cursor);
4389 dbms_sql.close_cursor(l_cursor);
4390 IF (l_num <> 1) THEN
4391 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4392 fnd_message.set_token('MSG', l_func_name ||
4393 ' is unable to insert new combination. ' ||
4394 ' Dynamic INSERT SQL returned ' || l_num ||
4395 ' rows, it was expected to return 1 row.');
4396 GOTO return_failure;
4397 END IF;
4398 x_is_new := 'Y';
4399 EXCEPTION
4400 WHEN OTHERS THEN
4401 dbms_sql.close_cursor(l_cursor);
4402 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4403 fnd_message.set_token('MSG', l_func_name ||
4404 ' is unable to insert new combination. ' ||
4405 ' into table ' || p_application_table_name ||
4406 '. ' || l_newline ||
4407 'SQLERRM: ' || Sqlerrm);
4408 GOTO return_failure;
4409 END;
4410
4411 --
4412 -- Now FDFGLI.
4413 --
4414 IF ((p_application_id = 101) AND (p_id_flex_code ='GL#')) THEN
4415 IF (NOT call_fdfgli(l_ccid)) THEN
4416 GOTO return_failure;
4417 END IF;
4418 END IF;
4419
4420 <<return_success>>
4421 x_encoded_error := NULL;
4422 x_ccid := l_ccid;
4423 COMMIT;
4424 RETURN;
4425
4426 <<return_failure>>
4427 x_encoded_error := fnd_message.get_encoded;
4428 x_ccid := -1;
4429 x_is_new := 'U';
4430 ROLLBACK;
4431 RETURN;
4432 EXCEPTION
4433 WHEN OTHERS THEN
4434 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4435 fnd_message.set_token('MSG', 'Top level exception in ' || l_func_name ||
4436 l_newline || 'SQLERRM: ' || Sqlerrm);
4437 x_encoded_error := fnd_message.get_encoded;
4438 x_ccid := -1;
4439 ROLLBACK;
4440 RETURN;
4441 END do_dynamic_insert_for_java;
4442
4443
4444 -- ======================================================================
4445 -- Local Cache Functions
4446 -- ======================================================================
4447 FUNCTION check_vsc(p_application_id IN NUMBER,
4448 p_responsibility_id IN NUMBER,
4449 p_value_set_id IN NUMBER,
4450 p_parent_value IN VARCHAR2,
4451 p_value IN VARCHAR2,
4452 px_security_status IN OUT nocopy VARCHAR2,
4453 px_error_message IN OUT nocopy VARCHAR2)
4454 RETURN VARCHAR2
4455 IS
4456 BEGIN
4457 --
4458 -- seperate p_parent_value and p_value to get rid of ambiguity.
4459 --
4460 g_cache_key := (p_parent_value || '.' ||
4461 p_application_id || '.' ||
4462 p_responsibility_id || '.' ||
4463 p_value_set_id || '.' ||
4464 p_value);
4465
4466 fnd_plsql_cache.generic_1to1_get_value(vsc_cache_controller,
4467 vsc_cache_storage,
4468 g_cache_key,
4469 g_cache_value,
4470 g_cache_return_code);
4471
4472 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
4473 px_security_status := g_cache_value.varchar2_1;
4474 px_error_message := g_cache_value.varchar2_2;
4475 END IF;
4476
4477 RETURN(g_cache_return_code);
4478 EXCEPTION
4479 WHEN OTHERS THEN
4480 RETURN(fnd_plsql_cache.CACHE_NOTFOUND);
4481 END check_vsc;
4482
4483 PROCEDURE update_vsc(p_application_id IN NUMBER,
4484 p_responsibility_id IN NUMBER,
4485 p_value_set_id IN NUMBER,
4486 p_parent_value IN VARCHAR2,
4487 p_value IN VARCHAR2,
4488 p_security_status IN VARCHAR2,
4489 p_error_message IN VARCHAR2)
4490 IS
4491 BEGIN
4492 --
4493 -- seperate p_parent_value and p_value to get rid of ambiguity.
4494 --
4495 g_cache_key := (p_parent_value || '.' ||
4496 p_application_id || '.' ||
4497 p_responsibility_id || '.' ||
4498 p_value_set_id || '.' ||
4499 p_value);
4500
4501 fnd_plsql_cache.generic_cache_new_value
4502 (x_value => g_cache_value,
4503 p_varchar2_1 => p_security_status,
4504 p_varchar2_2 => p_error_message);
4505
4506 fnd_plsql_cache.generic_1to1_put_value(vsc_cache_controller,
4507 vsc_cache_storage,
4508 g_cache_key,
4509 g_cache_value);
4510 EXCEPTION
4511 WHEN OTHERS THEN
4512 RETURN;
4513 END update_vsc;
4514
4515 -- ======================================
4516 -- PROCEDURE : check_value_security
4517 -- ======================================
4518 -- Checks if a value is secured.
4519 --
4520 PROCEDURE check_value_security(p_security_check_mode IN VARCHAR2,
4521 p_flex_value_set_id IN NUMBER,
4522 p_parent_flex_value IN VARCHAR2,
4523 p_flex_value IN VARCHAR2,
4524 p_resp_application_id IN NUMBER,
4525 p_responsibility_id IN NUMBER,
4526 x_security_status OUT nocopy VARCHAR2,
4527 x_error_message OUT nocopy VARCHAR2)
4528 IS PRAGMA AUTONOMOUS_TRANSACTION;
4529
4530 l_x_security_status VARCHAR2(100);
4531 l_x_error_message VARCHAR2(32000);
4532
4533 l_error_message fnd_flex_value_rules_tl.error_message%TYPE;
4534
4535 l_sql VARCHAR2(32000);
4536
4537 l_vset fnd_flex_value_sets%ROWTYPE;
4538 l_lock_handle VARCHAR2(128) := NULL;
4539 l_vsc_code VARCHAR2(10);
4540
4541 --
4542 -- Returns '(f(p_column) BETWEEN f(p_min_column) AND f(p_max_column)) '
4543 -- f() depends on p_format_type and p_apply_nvl.
4544 --
4545 /**********************************************************************************
4546 Also note, Dependent vsets do not support Hier security, but Regular sec is supported.
4547 Translated Ind and Dep vsets do not suport any security.
4548 None, Special and Pair also do not support any seucrity.
4549 Table vset does support Hier and Non-Hier security
4550 **********************************************************************************
4551 For bug 13101244 the code had to be rewritten. the original code was a bug
4552 waiting to happen. When a number type vset was used, the code added
4553 fnd_number.canonical_to_number() around the column in the WHERE clause.
4554 The columns that stores the vset values is of type VARCHAR2, so it can
4555 store numbers or chars. For number vsets we must convert the 'Num' chars to
4556 number to be compared in the between clause as numbers not Chars.
4557 For a Number Type vset we can grantee that all the value are numbers.
4558 The problem is that the explain plan of the DB does not guarantee that it
4559 first restricts the values by value set. It can first parse the between
4560 comparison before it filters the vset. Being that the case, the values
4561 in the column can be of type Char or number. So when a number conversion
4562 is attempted on a char, sql throws an error. So the WHERE clause had
4563 to be written with a decode statement.
4564 ***********************************************************************************/
4565
4566 FUNCTION get_between_sql(p_format_type IN VARCHAR2,
4567 p_column IN VARCHAR2,
4568 p_min_column IN VARCHAR2,
4569 p_max_column IN VARCHAR2,
4570 p_apply_nvl IN BOOLEAN)
4571 RETURN VARCHAR2
4572 IS
4573 BEGIN
4574
4575 IF (p_format_type = 'N') THEN -- Number
4576 IF (p_apply_nvl) THEN
4577 RETURN(' fnd_number.canonical_to_number(' || p_column || ') ' ||
4578 ' BETWEEN fnd_number.canonical_to_number(DECODE(RTRIM(TRANSLATE(Nvl(' ||
4579 p_min_column || ', ' || p_column || '),''0123456789'',''0''),''0''), NULL, ' ||
4580 'Nvl(' || p_min_column || ', ' || p_column || '), -99999))' ||
4581 ' AND fnd_number.canonical_to_number(DECODE(RTRIM(TRANSLATE(Nvl(' ||
4582 p_max_column || ', ' || p_column || '),''0123456789'',''0''),''0''), NULL, ' ||
4583 'Nvl(' || p_max_column || ', ' || p_column || '), -99999))');
4584 ELSE -- No NVL()
4585 RETURN(' fnd_number.canonical_to_number(' || p_column || ') ' ||
4586 ' BETWEEN fnd_number.canonical_to_number(DECODE(RTRIM(TRANSLATE(' ||
4587 p_min_column || ',''0123456789'',''0''),''0''), NULL, ' ||
4588 p_min_column || ', -99999))' ||
4589 ' AND fnd_number.canonical_to_number(DECODE(RTRIM(TRANSLATE(' ||
4590 p_max_column || ',''0123456789'',''0''),''0''), NULL, ' ||
4591 p_max_column || ', -99999))');
4592 END IF;
4593 /************************************************************************************************************
4594 Date, DateTime and Time value sets are not supported in vset Security Logic
4595 At this time it is not feasible to make the code work using Decodes
4596 ELSIF (p_format_type in ('X', 'Y')) THEN -- Standard Date and Time
4597 IF (p_apply_nvl) THEN
4598 RETURN(' fnd_date.canonical_to_date(' || p_column || ') ' ||
4599 ' BETWEEN fnd_date.canonical_to_date(DECODE(RTRIM(TRANSLATE(Nvl(' ||
4600 p_min_column || ', ' || p_column || '),''0123456789'',''0''),''0''), NULL, ' ||
4601 'Nvl(' || p_min_column || ', ' || p_column || '), -99999))' ||
4602 ' AND fnd_date.canonical_to_date(DECODE(RTRIM(TRANSLATE(Nvl(' ||
4603 p_max_column || ', ' || p_column || '),''0123456789'',''0''),''0''), NULL, ' ||
4604 'Nvl(' || p_max_column || ', ' || p_column || '), -99999))');
4605 ELSE
4606 RETURN(' fnd_date.canonical_to_date(' || p_column || ') ' ||
4607 ' BETWEEN fnd_date.canonical_to_date(DECODE(RTRIM(TRANSLATE(' ||
4608 p_min_column || ',''0123456789'',''0''),''0''), NULL, ' ||
4609 p_min_column || ', -99999))' ||
4610 ' AND fnd_date.canonical_to_date(DECODE(RTRIM(TRANSLATE(' ||
4611 p_max_column || ',''0123456789'',''0''),''0''), NULL, ' ||
4612 p_max_column || ', -99999))');
4613 END IF;
4614 ELSIF (p_format_type = 'I') THEN -- Time
4615 IF (p_apply_nvl) THEN
4616 RETURN('to_date(' || p_column || ', ''HH24:MI:SS'') ' ||
4617 ' BETWEEN ' || 'to_date(DECODE(RTRIM(TRANSLATE(Nvl(' ||
4618 p_min_column || ', ' || p_column || '), ''0123456789'',''0''),''0''), NULL, ' ||
4619 'Nvl(' || p_min_column || ', ' || p_column || '), -99999), ''HH24:MI:SS'') ' ||
4620 ' AND to_date(DECODE(RTRIM(TRANSLATE(Nvl(' ||
4621 p_max_column || ', ' || p_column || '), ''0123456789'',''0''),''0''), NULL, ' ||
4622 'Nvl(' || p_max_column || ', ' || p_column || '), -99999), ''HH24:MI:SS'')');
4623 ELSE
4624 RETURN('to_date(' || p_column || ', ''HH24:MI:SS'') BETWEEN ' ||
4625 'to_date(DECODE(RTRIM(TRANSLATE(' ||
4626 p_min_column || ',''0123456789'',''0''),''0''), NULL, ' ||
4627 p_min_column || ', -99999), ''HH24:MI:SS'') AND ' ||
4628 'to_date(DECODE(RTRIM(TRANSLATE(' ||
4629 p_max_column || ',''0123456789'',''0''),''0''), NULL, ' ||
4630 p_max_column || ', -99999), ''HH24:MI:SS'')');
4631 END IF;
4632 ******************************************************************************************************************/
4633 ELSE -- Character, The default code will be Char type.
4634 IF (p_apply_nvl) THEN
4635 RETURN(p_column || ' BETWEEN ' ||
4636 'Nvl(' || p_min_column || ', ' || p_column ||') AND ' ||
4637 'Nvl(' || p_max_column || ', ' || p_column || ') ');
4638 ELSE
4639 RETURN(p_column || ' BETWEEN ' ||
4640 p_min_column || ' AND ' ||
4641 p_max_column || ' ');
4642 END IF;
4643 END IF;
4644 END get_between_sql;
4645
4646
4647
4648 FUNCTION get_dependent_sql(p_format_type IN VARCHAR2,
4649 p_parent_column IN VARCHAR2)
4650 RETURN VARCHAR2
4651 IS
4652 BEGIN
4653
4654 IF (p_format_type = 'N') THEN -- Number
4655 RETURN(' AND r.parent_flex_value_low = ' ||
4656 'fnd_number.canonical_to_number(DECODE(RTRIM(TRANSLATE(' || p_parent_column ||
4657 ', ''0123456789'',''0''),''0''), NULL, ' || p_parent_column || ', -99999))' );
4658 /*
4659 ELSIF (p_format_type in ('X', 'Y')) THEN -- Standard Date and Time
4660 RETURN(' AND r.parent_flex_value_low = ' ||
4661 'fnd_date.canonical_to_date(DECODE(RTRIM(TRANSLATE(' || p_parent_column ||
4662 ', ''0123456789'',''0''),''0''), NULL, ' || p_parent_column || ', -99999))' );
4663 ELSIF (p_format_type = 'I') THEN -- Time
4664 RETURN(' AND r.parent_flex_value_low = ' ||
4665 'to_date(DECODE(RTRIM(TRANSLATE(' || p_parent_column || '''0123456789'',''0''),''0''), NULL,' ||
4666 p_parent_column || ', -99999), ''HH24:MI:SS'')');
4667 */
4668 ELSE
4669 RETURN(' AND (r.parent_flex_value_low = ' || p_parent_column || ') ');
4670 END IF;
4671
4672 END get_dependent_sql;
4673
4674
4675
4676 BEGIN
4677
4678 l_x_security_status := 'NOT-SECURED';
4679 l_x_error_message := NULL;
4680
4681 --
4682 -- Get the value set.
4683 --
4684 g_cache_key := p_flex_value_set_id;
4685 fnd_plsql_cache.custom_1to1_get_get_index(vst_cache_controller,
4686 g_cache_key,
4687 g_cache_index,
4688 g_cache_return_code);
4689 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
4690 l_vset := vst_cache_storage(g_cache_index);
4691 ELSE
4692 BEGIN
4693 SELECT *
4694 INTO l_vset
4695 FROM fnd_flex_value_sets
4696 WHERE 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : check_value_security' IS NOT NULL
4697 AND flex_value_set_id = p_flex_value_set_id;
4698 EXCEPTION
4699 WHEN OTHERS THEN
4700 fnd_message.set_name('FND', 'FLEX-ERROR LOADING VALUE SET');
4701 fnd_message.set_token('VALUE_SET_NAME', p_flex_value_set_id);
4702 l_x_security_status := 'VSET-NOTFOUND';
4703 l_x_error_message := fnd_message.get;
4704 GOTO goto_return;
4705 END;
4706 fnd_plsql_cache.custom_1to1_get_put_index(vst_cache_controller,
4707 g_cache_key,
4708 g_cache_index);
4709
4710 vst_cache_storage(g_cache_index) := l_vset;
4711 END IF;
4712
4713 --
4714 -- p_security_check_mode: Not used anymore. Kept for backward compatibility.
4715 --
4716 -- Y : Normal Check Only
4717 -- H : Hierarchy Check Only
4718 -- YH : Both Normal and Hierarchy Checks.
4719 --
4720 IF (p_security_check_mode NOT IN ('Y', 'H', 'YH')) THEN
4721 l_x_security_status := 'WRONG-ARG';
4722 l_x_error_message := 'Developer Error: p_check_security_mode must be Y, H, or YH.';
4723 GOTO goto_return;
4724 END IF;
4725
4726 IF (l_vset.security_enabled_flag NOT IN ('Y', 'H')) THEN
4727 GOTO goto_return;
4728 END IF;
4729
4730 IF (l_vset.validation_type NOT IN ('I', 'D', 'F')) THEN
4731 GOTO goto_return;
4732 END IF;
4733
4734 IF (p_flex_value IS NULL) THEN
4735 GOTO goto_return;
4736 END IF;
4737
4738 --
4739 -- First check the VSC
4740 --
4741 l_vsc_code := check_vsc(p_application_id => p_resp_application_id,
4742 p_responsibility_id => p_responsibility_id,
4743 p_value_set_id => p_flex_value_set_id,
4744 p_parent_value => p_parent_flex_value,
4745 p_value => p_flex_value,
4746 px_security_status => l_x_security_status,
4747 px_error_message => l_x_error_message);
4748
4749 IF (l_vsc_code = fnd_plsql_cache.CACHE_FOUND) THEN
4750 IF (fnd_flex_server1.g_debug_level > 0) THEN
4751 fnd_flex_server1.add_debug('Found in SSV.VSC.');
4752 END IF;
4753 GOTO goto_return;
4754 END IF;
4755
4756 --
4757 -- Not in the cache, continue on security check.
4758 --
4759
4760 IF ((l_vset.security_enabled_flag = 'H') AND
4761 (l_vset.validation_type <> 'D')) THEN
4762
4763 -- Bug 8996310, If hierarchical security is being used then
4764 -- request a lock to make sure hier comp is not running.
4765 -- If the hier comp is running, this request_lock will wait until
4766 -- the lock is rleased by the hier comp signifying it is complete.
4767 -- Once he hier is complete we then can continue processing the hier
4768 -- security check. If the hier comp is not running, then this
4769 -- request lock will get a lock signifying the hier comp not running
4770 -- at this moment in time. Once locked, we know the hier comp is
4771 -- not running and then we can release the lock and continue processing.
4772 -- We know we can release the lock because this PROCEDURE is defined
4773 -- to be serializable. Serializable allows the process to take a snap
4774 -- shot of the data. Eventhough the data maybe be deleted by the hier
4775 -- comp, this process will still see the snap shot of data before it
4776 -- was deleted. In this way security is not compromised, if hier comp
4777 -- starts running in the middle of this process. By releasing the
4778 -- lock, we will not get process contentions.
4779 fnd_flex_hierarchy_compiler.request_lock(l_vset.flex_value_set_name, l_lock_handle);
4780 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
4781 IF (l_lock_handle IS NOT NULL) THEN
4782 fnd_flex_hierarchy_compiler.release_lock(l_vset.flex_value_set_name, l_lock_handle);
4783 END IF;
4784 END IF;
4785
4786 --
4787 -- Check for value being directly excluded by a security rule...
4788 --
4789 l_sql :=
4790 'SELECT r.error_message' ||
4791 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u, fnd_flex_value_rule_lines l' ||
4792 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4793 ' AND u.application_id = :b_resp_application_id' ||
4794 ' AND u.responsibility_id = :b_responsibility_id' ||
4795 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4796 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4797 ' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
4798 ' AND l.include_exclude_indicator = ''E''' ||
4799 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4800 ' AND ROWNUM < 2';
4801
4802 BEGIN
4803 IF (l_vset.validation_type = 'D') THEN
4804 l_sql := l_sql || get_dependent_sql(l_vset.format_type, ':b_parent_flex_value');
4805 -- IF (l_vset.format_type in ('N','X','Y','I')) THEN -- More bind variables are need in these cases.
4806 IF (l_vset.format_type in ('N')) THEN -- More bind variables are need in these cases.
4807 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4808 p_flex_value_set_id,
4809 p_resp_application_id,
4810 p_responsibility_id,
4811 p_flex_value, p_flex_value, p_flex_value, p_flex_value, p_flex_value,
4812 p_parent_flex_value, p_parent_flex_value;
4813 ELSE
4814 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4815 p_flex_value_set_id,
4816 p_resp_application_id,
4817 p_responsibility_id,
4818 p_flex_value, p_flex_value, p_flex_value,
4819 p_parent_flex_value;
4820 END IF;
4821 ELSE
4822 -- IF (l_vset.format_type in ('N','X','Y','I')) THEN
4823 IF (l_vset.format_type in ('N')) THEN
4824 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4825 p_flex_value_set_id,
4826 p_resp_application_id,
4827 p_responsibility_id,
4828 p_flex_value, p_flex_value, p_flex_value, p_flex_value, p_flex_value;
4829 ELSE
4830 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4831 p_flex_value_set_id,
4832 p_resp_application_id,
4833 p_responsibility_id,
4834 p_flex_value, p_flex_value, p_flex_value;
4835 END IF;
4836 END IF;
4837
4838 fnd_message.set_name('FND', 'FLEX-EXCLUDED BY SEC. RULE');
4839 fnd_message.set_token('MESSAGE', l_error_message);
4840
4841 l_x_security_status := 'EXCLUDED';
4842 l_x_error_message := fnd_message.get;
4843 GOTO goto_cache_the_result;
4844 EXCEPTION
4845 WHEN NO_DATA_FOUND THEN
4846 --
4847 -- NOT directly excluded, keep checking...
4848 --
4849 NULL;
4850 END;
4851
4852 --
4853 -- Check for value being hierarchically excluded by a security rule...
4854 --
4855 IF ((l_vset.security_enabled_flag = 'H') AND
4856 (l_vset.validation_type <> 'D')) THEN
4857
4858 l_sql :=
4859 'SELECT /*+ LEADING (h) */ r.error_message' ||
4860 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u, fnd_flex_value_hier_all h, fnd_flex_value_rule_lines l' ||
4861 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4862 ' AND u.application_id = :b_resp_application_id' ||
4863 ' AND u.responsibility_id = :b_responsibility_id' ||
4864 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4865 ' AND h.flex_value_set_id = r.flex_value_set_id' ||
4866 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4867 ' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
4868 ' AND l.include_exclude_indicator = ''E''' ||
4869 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'h.child_flex_value_low', 'h.child_flex_value_high', FALSE) ||
4870 ' AND ' || get_between_sql(l_vset.format_type, 'h.parent_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4871 ' AND ROWNUM < 2 ';
4872
4873 BEGIN
4874 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4875 p_flex_value_set_id,
4876 p_resp_application_id,
4877 p_responsibility_id,
4878 p_flex_value;
4879
4880 fnd_message.set_name('FND', 'FLEX-EXCLUDED BY SEC. RULE');
4881 fnd_message.set_token('MESSAGE', l_error_message);
4882
4883 l_x_security_status := 'HIER-EXCLUDED';
4884 l_x_error_message := fnd_message.get;
4885 GOTO goto_cache_the_result;
4886 EXCEPTION
4887 WHEN NO_DATA_FOUND THEN
4888 --
4889 -- Not hierarchically excluded, keep checking...
4890 --
4891 NULL;
4892 END;
4893 END IF;
4894
4895 --
4896 -- Check for value NOT being directly included by a security rule...
4897 --
4898 l_sql :=
4899 'SELECT r.error_message' ||
4900 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u' ||
4901 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4902 ' AND u.application_id = :b_resp_application_id' ||
4903 ' AND u.responsibility_id = :b_responsibility_id' ||
4904 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4905 ' AND NOT exists (SELECT NULL' ||
4906 ' FROM fnd_flex_value_rule_lines l' ||
4907 ' WHERE l.flex_value_rule_id = r.flex_value_rule_id' ||
4908 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4909 ' AND l.include_exclude_indicator = ''I''' ||
4910 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4911 ' )' ||
4912 ' AND ROWNUM < 2 ';
4913
4914 BEGIN
4915 IF (l_vset.validation_type = 'D') THEN
4916 l_sql := l_sql || get_dependent_sql(l_vset.format_type, ':b_parent_flex_value');
4917 -- IF (l_vset.format_type in ('N','X','Y','I')) THEN
4918 IF (l_vset.format_type in ('N')) THEN
4919 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4920 p_flex_value_set_id,
4921 p_resp_application_id,
4922 p_responsibility_id,
4923 p_flex_value, p_flex_value, p_flex_value, p_flex_value, p_flex_value,
4924 p_parent_flex_value, p_parent_flex_value;
4925 ELSE
4926 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4927 p_flex_value_set_id,
4928 p_resp_application_id,
4929 p_responsibility_id,
4930 p_flex_value, p_flex_value, p_flex_value,
4931 p_parent_flex_value;
4932 END IF;
4933 ELSE
4934 -- IF (l_vset.format_type in ('N','X','Y','I')) THEN
4935 IF (l_vset.format_type in ('N')) THEN
4936 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4937 p_flex_value_set_id,
4938 p_resp_application_id,
4939 p_responsibility_id,
4940 p_flex_value, p_flex_value, p_flex_value, p_flex_value, p_flex_value;
4941 ELSE
4942 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4943 p_flex_value_set_id,
4944 p_resp_application_id,
4945 p_responsibility_id,
4946 p_flex_value, p_flex_value, p_flex_value;
4947 END IF;
4948 END IF;
4949
4950 fnd_message.set_name('FND', 'FLEX-NOT INCL. BY SEC RULE');
4951 fnd_message.set_token('MESSAGE', l_error_message);
4952
4953 l_x_security_status := 'NOT-INCLUDED';
4954 l_x_error_message := fnd_message.get;
4955
4956 --
4957 -- NOT directly included, it might be hierarchically included...
4958 --
4959 EXCEPTION
4960 WHEN NO_DATA_FOUND THEN
4961 --
4962 -- Directly included, we are done.
4963 --
4964 GOTO goto_cache_the_result;
4965 END;
4966
4967 --
4968 -- Check for value NOT being hierarchically included by a security rule...
4969 --
4970 IF ((l_vset.security_enabled_flag = 'H') AND
4971 (l_vset.validation_type <> 'D')) THEN
4972
4973 l_sql :=
4974 'SELECT r.error_message' ||
4975 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u' ||
4976 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4977 ' AND u.application_id = :b_resp_application_id' ||
4978 ' AND u.responsibility_id = :b_responsibility_id' ||
4979 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4980 ' AND NOT exists (SELECT NULL' ||
4981 ' FROM fnd_flex_value_hier_all h, fnd_flex_value_rule_lines l' ||
4982 ' WHERE h.flex_value_set_id = r.flex_value_set_id' ||
4983 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4984 ' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
4985 ' AND l.include_exclude_indicator = ''I''' ||
4986 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'h.child_flex_value_low', 'h.child_flex_value_high', FALSE) ||
4987 ' AND ' || get_between_sql(l_vset.format_type, 'h.parent_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4988 ' )' ||
4989 ' AND ROWNUM < 2';
4990
4991 BEGIN
4992 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4993 p_flex_value_set_id,
4994 p_resp_application_id,
4995 p_responsibility_id,
4996 p_flex_value;
4997
4998 --
4999 -- NOT hierarchically included, Error message is already set above.
5000 --
5001 -- Bug 9746618 Setting message from previous check does not
5002 -- give accurate message when a violation is found from
5003 -- this security check. Need to set message from this check.
5004 fnd_message.set_name('FND', 'FLEX-NOT INCL. BY SEC RULE');
5005 fnd_message.set_token('MESSAGE', l_error_message);
5006
5007 l_x_security_status := 'NOT-HIER-INCLUDED';
5008 l_x_error_message := fnd_message.get;
5009
5010
5011 GOTO goto_cache_the_result;
5012 EXCEPTION
5013 WHEN NO_DATA_FOUND THEN
5014 --
5015 -- Hierarchically included. Clear the error message set above.
5016 --
5017 l_x_security_status := 'NOT-SECURED';
5018 l_x_error_message := NULL;
5019
5020 GOTO goto_cache_the_result;
5021 END;
5022 END IF;
5023
5024 <<goto_cache_the_result>>
5025
5026 update_vsc(p_application_id => p_resp_application_id,
5027 p_responsibility_id => p_responsibility_id,
5028 p_value_set_id => p_flex_value_set_id,
5029 p_parent_value => p_parent_flex_value,
5030 p_value => p_flex_value,
5031 p_security_status => l_x_security_status,
5032 p_error_message => l_x_error_message);
5033
5034
5035 <<goto_return>>
5036
5037 x_security_status := l_x_security_status;
5038 x_error_message := Substrb(l_x_error_message, 1, 1950);
5039 COMMIT; -- To end snap shot of Serializable.
5040
5041 RETURN;
5042 EXCEPTION
5043 WHEN OTHERS THEN
5044 IF (l_lock_handle IS NOT NULL) THEN
5045 fnd_flex_hierarchy_compiler.release_lock(l_vset.flex_value_set_name, l_lock_handle);
5046 END IF;
5047 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
5048 FND_MESSAGE.set_token('MSG', 'SSV.check_value_security(): ' || SQLERRM);
5049 x_security_status := 'EXCEPTION';
5050 x_error_message := Substrb(fnd_message.get, 1, 1950);
5051 COMMIT; -- To end snap shot of Serializable.
5052 END check_value_security;
5053
5054 -- ==================================================
5055 -- PROCEDURE : parse_flex_values
5056 -- ==================================================
5057 PROCEDURE parse_flex_values(p_concatenated_flex_values IN VARCHAR2,
5058 p_delimiter IN VARCHAR2,
5059 p_numof_flex_values IN NUMBER DEFAULT NULL,
5060 x_flex_values OUT nocopy fnd_flex_server1.stringarray,
5061 x_numof_flex_values OUT nocopy NUMBER)
5062 IS
5063 BEGIN
5064 --
5065 -- If only one segment is expected then no parsing, and no un-escaping.
5066 --
5067 IF ((Nvl(p_numof_flex_values, 0) = 1) OR
5068 (p_concatenated_flex_values IS NULL)) THEN
5069 x_numof_flex_values := 1;
5070 x_flex_values(1) := p_concatenated_flex_values;
5071 ELSE
5072 x_numof_flex_values :=
5073 fnd_flex_server1.to_stringarray(p_concatenated_flex_values,
5074 p_delimiter,
5075 x_flex_values);
5076 END IF;
5077 EXCEPTION
5078 WHEN OTHERS THEN
5079 RAISE;
5080 END parse_flex_values;
5081
5082 -- ==================================================
5083 -- PROCEDURE : concatenate_flex_values
5084 -- ==================================================
5085 PROCEDURE concatenate_flex_values(p_flex_values IN fnd_flex_server1.stringarray,
5086 p_numof_flex_values IN NUMBER,
5087 p_delimiter IN VARCHAR2,
5088 x_concatenated_flex_values OUT nocopy VARCHAR2)
5089 IS
5090 BEGIN
5091 IF (fnd_flex_server1.g_debug_level > 0) THEN
5092 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_flex_values()');
5093 END IF;
5094 IF (p_numof_flex_values > 0) THEN
5095 x_concatenated_flex_values :=
5096 fnd_flex_server1.from_stringarray(p_numof_flex_values,
5097 p_flex_values,
5098 p_delimiter);
5099 ELSE
5100 x_concatenated_flex_values := '';
5101 END IF;
5102 EXCEPTION
5103 WHEN OTHERS THEN
5104 raise_application_error(-20001, 'SSV.concatenate_flex_values. SQLERRM : ' || Sqlerrm);
5105 END concatenate_flex_values;
5106
5107 -- ==================================================
5108 FUNCTION get_concatenated_value(p_delimiter IN VARCHAR2,
5109 p_segment_count IN NUMBER,
5110 p_segment1 IN VARCHAR2 DEFAULT NULL,
5111 p_segment2 IN VARCHAR2 DEFAULT NULL,
5112 p_segment3 IN VARCHAR2 DEFAULT NULL,
5113 p_segment4 IN VARCHAR2 DEFAULT NULL,
5114 p_segment5 IN VARCHAR2 DEFAULT NULL,
5115 p_segment6 IN VARCHAR2 DEFAULT NULL,
5116 p_segment7 IN VARCHAR2 DEFAULT NULL,
5117 p_segment8 IN VARCHAR2 DEFAULT NULL,
5118 p_segment9 IN VARCHAR2 DEFAULT NULL,
5119 p_segment10 IN VARCHAR2 DEFAULT NULL,
5120 p_segment11 IN VARCHAR2 DEFAULT NULL,
5121 p_segment12 IN VARCHAR2 DEFAULT NULL,
5122 p_segment13 IN VARCHAR2 DEFAULT NULL,
5123 p_segment14 IN VARCHAR2 DEFAULT NULL,
5124 p_segment15 IN VARCHAR2 DEFAULT NULL,
5125 p_segment16 IN VARCHAR2 DEFAULT NULL,
5126 p_segment17 IN VARCHAR2 DEFAULT NULL,
5127 p_segment18 IN VARCHAR2 DEFAULT NULL,
5128 p_segment19 IN VARCHAR2 DEFAULT NULL,
5129 p_segment20 IN VARCHAR2 DEFAULT NULL,
5130 p_segment21 IN VARCHAR2 DEFAULT NULL,
5131 p_segment22 IN VARCHAR2 DEFAULT NULL,
5132 p_segment23 IN VARCHAR2 DEFAULT NULL,
5133 p_segment24 IN VARCHAR2 DEFAULT NULL,
5134 p_segment25 IN VARCHAR2 DEFAULT NULL,
5135 p_segment26 IN VARCHAR2 DEFAULT NULL,
5136 p_segment27 IN VARCHAR2 DEFAULT NULL,
5137 p_segment28 IN VARCHAR2 DEFAULT NULL,
5138 p_segment29 IN VARCHAR2 DEFAULT NULL,
5139 p_segment30 IN VARCHAR2 DEFAULT NULL)
5140 RETURN VARCHAR2
5141 IS
5142 l_flex_values fnd_flex_server1.stringarray;
5143 l_concatenated_value VARCHAR2(32000);
5144 BEGIN
5145 l_flex_values(1) := p_segment1;
5146 l_flex_values(2) := p_segment2;
5147 l_flex_values(3) := p_segment3;
5148 l_flex_values(4) := p_segment4;
5149 l_flex_values(5) := p_segment5;
5150 l_flex_values(6) := p_segment6;
5151 l_flex_values(7) := p_segment7;
5152 l_flex_values(8) := p_segment8;
5153 l_flex_values(9) := p_segment9;
5154 l_flex_values(10) := p_segment10;
5155 l_flex_values(11) := p_segment11;
5156 l_flex_values(12) := p_segment12;
5157 l_flex_values(13) := p_segment13;
5158 l_flex_values(14) := p_segment14;
5159 l_flex_values(15) := p_segment15;
5160 l_flex_values(16) := p_segment16;
5161 l_flex_values(17) := p_segment17;
5162 l_flex_values(18) := p_segment18;
5163 l_flex_values(19) := p_segment19;
5164 l_flex_values(20) := p_segment20;
5165 l_flex_values(21) := p_segment21;
5166 l_flex_values(22) := p_segment22;
5167 l_flex_values(23) := p_segment23;
5168 l_flex_values(24) := p_segment24;
5169 l_flex_values(25) := p_segment25;
5170 l_flex_values(26) := p_segment26;
5171 l_flex_values(27) := p_segment27;
5172 l_flex_values(28) := p_segment28;
5173 l_flex_values(29) := p_segment29;
5174 l_flex_values(30) := p_segment30;
5175
5176 concatenate_flex_values(l_flex_values,
5177 p_segment_count,
5178 p_delimiter,
5179 l_concatenated_value);
5180
5181 RETURN(l_concatenated_value);
5182 END get_concatenated_value;
5183
5184 -- ==================================================
5185 PROCEDURE delete_dff_compiled(p_application_id IN NUMBER,
5186 p_descriptive_flexfield_name IN VARCHAR2)
5187 IS
5188 PRAGMA AUTONOMOUS_TRANSACTION;
5189 BEGIN
5190 DELETE FROM fnd_compiled_descriptive_flexs
5191 WHERE application_id = p_application_id
5192 AND descriptive_flexfield_name = p_descriptive_flexfield_name;
5193
5194 COMMIT;
5195 EXCEPTION
5196 WHEN OTHERS THEN
5197 ROLLBACK;
5198 RAISE;
5199 END delete_dff_compiled;
5200
5201 -- ==================================================
5202 PROCEDURE raise_dff_compiled(p_application_id IN NUMBER,
5203 p_descriptive_flexfield_name IN VARCHAR2)
5204 IS
5205 l_parameters wf_parameter_list_t := wf_parameter_list_t();
5206
5207 l_application_short_name fnd_application.application_short_name%TYPE;
5208 BEGIN
5209 SELECT 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : raise_dff_compiled' arcs_revision,
5210 application_short_name
5211 INTO g_arcs_revision,
5212 l_application_short_name
5213 FROM fnd_application
5214 WHERE application_id = p_application_id;
5215
5216 wf_event.addparametertolist(p_name => 'APPLICATION_SHORT_NAME',
5217 p_value => l_application_short_name,
5218 p_parameterlist => l_parameters);
5219
5220 wf_event.addparametertolist(p_name => 'APPLICATION_ID',
5221 p_value => p_application_id,
5222 p_parameterlist => l_parameters);
5223
5224 wf_event.addparametertolist(p_name => 'DESCRIPTIVE_FLEXFIELD_NAME',
5225 p_value => p_descriptive_flexfield_name,
5226 p_parameterlist => l_parameters);
5227
5228 BEGIN
5229 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.dff.compiled',
5230 p_event_key => (l_application_short_name || '.' ||
5231 p_descriptive_flexfield_name),
5232 p_event_data => NULL,
5233 p_parameters => l_parameters,
5234 p_send_date => Sysdate);
5235 EXCEPTION
5236 WHEN OTHERS THEN
5237 --
5238 -- If event fails, then remove the compiled data.
5239 -- This event is raised after the data were inserted into
5240 -- the compiled table.
5241 --
5242 -- Bug 5367119. Commenting following delete as we now raise the event
5243 -- before the data is inserted to compiled table.
5244
5245 /* delete_dff_compiled(p_application_id,
5246 p_descriptive_flexfield_name); */
5247 --
5248 -- Raise the exception.
5249 --
5250 FND_MESSAGE.set_name('FND', 'FLEX-COMPILE-WF_EVENT-ERROR');
5251 FND_MESSAGE.set_token('EVENT_NAME', 'oracle.apps.fnd.flex.dff.compiled');
5252 FND_MESSAGE.set_token('EVENT_KEY', l_application_short_name || '.' || p_descriptive_flexfield_name);
5253 FND_MESSAGE.set_token('ERROR', SQLERRM);
5254 FND_MESSAGE.RAISE_ERROR();
5255 END;
5256
5257 -- No Exception handling here, let it go up to caller.
5258
5259 END raise_dff_compiled;
5260
5261 -- ==================================================
5262 PROCEDURE delete_kff_structure_compiled(p_application_id IN NUMBER,
5263 p_id_flex_code IN VARCHAR2,
5264 p_id_flex_num IN NUMBER)
5265 IS
5266 PRAGMA AUTONOMOUS_TRANSACTION;
5267 BEGIN
5268 DELETE FROM fnd_compiled_id_flex_structs
5269 WHERE application_id = p_application_id
5270 AND id_flex_code = p_id_flex_code
5271 AND id_flex_num = p_id_flex_num;
5272
5273 COMMIT;
5274 EXCEPTION
5275 WHEN OTHERS THEN
5276 ROLLBACK;
5277 RAISE;
5278 END delete_kff_structure_compiled;
5279
5280 -- ==================================================
5281 PROCEDURE raise_kff_structure_compiled(p_application_id IN NUMBER,
5282 p_id_flex_code IN VARCHAR2,
5283 p_id_flex_num IN NUMBER)
5284 IS
5285 l_parameters wf_parameter_list_t := wf_parameter_list_t();
5286
5287 l_application_short_name fnd_application.application_short_name%TYPE;
5288 l_id_flex_structure_code fnd_id_flex_structures.id_flex_structure_code%TYPE;
5289 BEGIN
5290 SELECT 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : raise_kff_structure_compiled' arcs_revision,
5291 application_short_name
5292 INTO g_arcs_revision,
5293 l_application_short_name
5294 FROM fnd_application
5295 WHERE application_id = p_application_id;
5296
5297 SELECT 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : raise_kff_structure_compiled' arcs_revision,
5298 id_flex_structure_code
5299 INTO g_arcs_revision,
5300 l_id_flex_structure_code
5301 FROM fnd_id_flex_structures
5302 WHERE application_id = p_application_id
5303 AND id_flex_code = p_id_flex_code
5304 AND id_flex_num = p_id_flex_num;
5305
5306 wf_event.addparametertolist(p_name => 'APPLICATION_SHORT_NAME',
5307 p_value => l_application_short_name,
5308 p_parameterlist => l_parameters);
5309
5310 wf_event.addparametertolist(p_name => 'APPLICATION_ID',
5311 p_value => p_application_id,
5312 p_parameterlist => l_parameters);
5313
5314 wf_event.addparametertolist(p_name => 'ID_FLEX_CODE',
5315 p_value => p_id_flex_code,
5316 p_parameterlist => l_parameters);
5317
5318 wf_event.addparametertolist(p_name => 'ID_FLEX_STRUCTURE_CODE',
5319 p_value => l_id_flex_structure_code,
5320 p_parameterlist => l_parameters);
5321
5322 wf_event.addparametertolist(p_name => 'ID_FLEX_NUM',
5323 p_value => p_id_flex_num,
5324 p_parameterlist => l_parameters);
5325
5326 BEGIN
5327 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.kff.structure.compiled',
5328 p_event_key => (l_application_short_name || '.' ||
5329 p_id_flex_code || '.' ||
5330 l_id_flex_structure_code),
5331 p_event_data => NULL,
5332 p_parameters => l_parameters,
5333 p_send_date => Sysdate);
5334 EXCEPTION
5335 WHEN OTHERS THEN
5336 --
5337 -- If event fails, then remove the compiled data.
5338 -- This event is raised after the data were inserted into
5339 -- the compiled table.
5340 --
5341 -- Bug 5367119. Commenting following delete as we now raise the event
5342 -- before the data is inserted to compiled table.
5343
5344 /* delete_kff_structure_compiled(p_application_id,
5345 p_id_flex_code,
5346 p_id_flex_num); */
5347 --
5348 -- Raise the exception.
5349 --
5350 FND_MESSAGE.set_name('FND', 'FLEX-COMPILE-WF_EVENT-ERROR');
5351 FND_MESSAGE.set_token('EVENT_NAME', 'oracle.apps.fnd.flex.kff.structure.compiled');
5352 FND_MESSAGE.set_token('EVENT_KEY', l_application_short_name || '.' || p_id_flex_code || '.' || l_id_flex_structure_code);
5353 FND_MESSAGE.set_token('ERROR', SQLERRM);
5354 FND_MESSAGE.RAISE_ERROR();
5355
5356 END;
5357
5358 -- No Exception handling here, let it go up to caller.
5359
5360 END raise_kff_structure_compiled;
5361
5362 -- ==================================================
5363 PROCEDURE raise_vst_updated(p_flex_value_set_id IN NUMBER)
5364 IS
5365 l_parameters wf_parameter_list_t := wf_parameter_list_t();
5366
5367 l_flex_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
5368 BEGIN
5369 SELECT flex_value_set_name
5370 INTO l_flex_value_set_name
5371 FROM fnd_flex_value_sets
5372 WHERE flex_value_set_id = p_flex_value_set_id;
5373
5374 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_NAME',
5375 p_value => l_flex_value_set_name,
5376 p_parameterlist => l_parameters);
5377
5378 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_ID',
5379 p_value => p_flex_value_set_id,
5380 p_parameterlist => l_parameters);
5381
5382 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.vst.updated',
5383 p_event_key => l_flex_value_set_name,
5384 p_event_data => NULL,
5385 p_parameters => l_parameters,
5386 p_send_date => Sysdate);
5387
5388 -- No Exception handling here, let it go up to caller.
5389
5390 END raise_vst_updated;
5391
5392 -- ==================================================
5393 PROCEDURE get_kfvcct_record(p_application_id IN NUMBER,
5394 p_id_flex_code IN VARCHAR2,
5395 px_kfvcct_record IN OUT nocopy kfvcct_record_type)
5396 IS
5397 l_kfvcct_key VARCHAR2(2000);
5398 BEGIN
5399 l_kfvcct_key := (p_application_id || g_newline ||
5400 p_id_flex_code);
5401
5402 fnd_plsql_cache.custom_1to1_get_get_index(kfvcct_cache_controller,
5403 l_kfvcct_key,
5404 g_cache_index,
5405 g_cache_return_code);
5406
5407 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
5408 px_kfvcct_record := kfvcct_cache_storage(g_cache_index);
5409
5410 ELSE
5411
5412 SELECT 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : get_kfvcct_record' arcs_revision,
5413 fif.table_application_id,
5414 fif.application_table_name,
5415 ft.table_id,
5416 fif.set_defining_column_name,
5417 fif.unique_id_column_name
5418 INTO g_arcs_revision,
5419 px_kfvcct_record.table_application_id,
5420 px_kfvcct_record.application_table_name,
5421 px_kfvcct_record.table_id,
5422 px_kfvcct_record.set_defining_column_name,
5423 px_kfvcct_record.unique_id_column_name
5424 FROM fnd_id_flexs fif, fnd_tables ft
5425 WHERE fif.application_id = p_application_id
5426 AND fif.id_flex_code = p_id_flex_code
5427 AND ft.application_id = fif.table_application_id
5428 AND ft.table_name = fif.application_table_name;
5429
5430 fnd_plsql_cache.custom_1to1_get_put_index(kfvcct_cache_controller,
5431 l_kfvcct_key,
5432 g_cache_index);
5433
5434 kfvcct_cache_storage(g_cache_index) := px_kfvcct_record;
5435 END IF;
5436 END get_kfvcct_record;
5437
5438 -- ==================================================
5439 PROCEDURE create_kfvssc_record(p_application_id IN NUMBER,
5440 p_id_flex_code IN VARCHAR2,
5441 p_id_flex_num IN NUMBER,
5442 px_kfvssc_record IN OUT nocopy kfvssc_record_type)
5443 IS
5444 l_kfvcct_record kfvcct_record_type;
5445
5446 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
5447
5448 l_compact_concat VARCHAR2(32000);
5449 l_padded_concat VARCHAR2(32000);
5450 l_padding_type VARCHAR2(30);
5451 l_padding_size NUMBER;
5452
5453 CURSOR kff_segments_cursor(p_application_id IN NUMBER,
5454 p_id_flex_code IN VARCHAR2,
5455 p_id_flex_num IN NUMBER,
5456 p_table_application_id IN NUMBER,
5457 p_table_id IN NUMBER)
5458 IS
5459 SELECT 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : create_kfvssc_record' arcs_revision,
5460 fifsg.application_column_name,
5461 ffvs.format_type vset_format_type,
5462 ffvs.maximum_size vset_maximum_size,
5463 ffvt.id_column_type vset_id_column_type,
5464 ffvt.id_column_size vset_id_column_size,
5465 fc.column_type column_type,
5466 fc.width column_size
5467 FROM fnd_id_flex_segments fifsg, fnd_flex_value_sets ffvs,
5468 fnd_columns fc, fnd_flex_validation_tables ffvt
5469 WHERE fifsg.application_id = p_application_id
5470 AND fifsg.id_flex_code = p_id_flex_code
5471 AND fifsg.id_flex_num = p_id_flex_num
5472 AND fifsg.enabled_flag = 'Y'
5473 AND fc.application_id = p_table_application_id
5474 AND fc.table_id = p_table_id
5475 AND fc.column_name = fifsg.application_column_name
5476 AND fifsg.flex_value_set_id = ffvs.flex_value_set_id(+)
5477 AND fifsg.flex_value_set_id = ffvt.flex_value_set_id(+)
5478 ORDER BY fifsg.segment_num, fifsg.segment_name;
5479
5480 BEGIN
5481 get_kfvcct_record(p_application_id,
5482 p_id_flex_code,
5483 l_kfvcct_record);
5484
5485 SELECT 'AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : get_kfv_sql' arcs_revision,
5486 fifst.concatenated_segment_delimiter
5487 INTO g_arcs_revision,
5488 l_delimiter
5489 FROM fnd_id_flex_structures fifst
5490 WHERE fifst.application_id = p_application_id
5491 AND fifst.id_flex_code = p_id_flex_code
5492 AND fifst.id_flex_num = p_id_flex_num;
5493
5494 l_compact_concat := NULL;
5495 l_padded_concat := NULL;
5496
5497 FOR kff_segment IN kff_segments_cursor(p_application_id,
5498 p_id_flex_code,
5499 p_id_flex_num,
5500 l_kfvcct_record.table_application_id,
5501 l_kfvcct_record.table_id)
5502 LOOP
5503 -- Compact
5504 IF (l_compact_concat IS NOT NULL) THEN
5505 l_compact_concat := (l_compact_concat ||
5506 ' || ''' ||
5507 l_delimiter ||
5508 ''' || ');
5509 END IF;
5510
5511 l_compact_concat := l_compact_concat || kff_segment.application_column_name;
5512
5513
5514 -- Padded
5515 IF (l_padded_concat IS NOT NULL) THEN
5516 l_padded_concat := (l_padded_concat ||
5517 ' || ''' ||
5518 l_delimiter ||
5519 ''' || ');
5520 END IF;
5521
5522 IF (kff_segment.vset_format_type IS NULL) THEN
5523 -- There is no value set attached to segment, use column type and size
5524 IF (kff_segment.column_type = 'N') THEN
5525 l_padding_type := 'LPAD';
5526 ELSE
5527 l_padding_type := 'RPAD';
5528 END IF;
5529 l_padding_size := kff_segment.column_size;
5530
5531 ELSE
5532 -- There is a value set attached to the segment
5533 IF (kff_segment.vset_id_column_type IS NULL) THEN
5534 -- This is a non-id value set (Not a table validated value set)
5535 IF (kff_segment.vset_format_type IN ('X', 'Y')) THEN
5536 l_padding_type := 'RPAD';
5537 l_padding_size := 20; -- ?? Should be 19.
5538
5539 ELSIF (kff_segment.vset_format_type = 'N') THEN
5540 l_padding_type := 'LPAD';
5541 l_padding_size := kff_segment.vset_maximum_size ;
5542
5543 ELSE
5544 l_padding_type := 'RPAD';
5545 l_padding_size := kff_segment.vset_maximum_size ;
5546
5547 END IF;
5548
5549 ELSE
5550 -- This is a id value set (A table validated value set)
5551 IF (kff_segment.vset_id_column_type = 'N') THEN
5552 l_padding_type := 'LPAD';
5553 ELSE
5554 l_padding_type := 'RPAD';
5555 END IF;
5556 l_padding_size := kff_segment.vset_id_column_size;
5557 END IF;
5558 END IF;
5559
5560 l_padded_concat := (l_padded_concat || l_padding_type ||
5561 '(NVL(' || kff_segment.application_column_name ||
5562 ','' ''), ' || l_padding_size || ')');
5563
5564 END LOOP;
5565
5566 px_kfvssc_record.compact_sql := ('SELECT /* AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : get_kfv_concat_segs */ ' ||
5567 l_compact_concat ||
5568 ' FROM ' || l_kfvcct_record.application_table_name);
5569
5570 px_kfvssc_record.padded_sql := ('SELECT /* AFFFSSVB.pls : $Revision: 120.28.12020000.2 $ : get_kfv_concat_segs */ ' ||
5571 l_padded_concat ||
5572 ' FROM ' || l_kfvcct_record.application_table_name);
5573
5574 px_kfvssc_record.set_defining_column_name := l_kfvcct_record.set_defining_column_name;
5575 px_kfvssc_record.unique_id_column_name := l_kfvcct_record.unique_id_column_name;
5576
5577 END create_kfvssc_record;
5578
5579 -- ==================================================
5580 PROCEDURE get_kfvssc_record(p_application_id IN NUMBER,
5581 p_id_flex_code IN VARCHAR2,
5582 p_id_flex_num IN NUMBER,
5583 px_kfvssc_record IN OUT nocopy kfvssc_record_type)
5584 IS
5585 l_kfvssc_key VARCHAR2(2000);
5586 BEGIN
5587 l_kfvssc_key := (p_application_id || g_newline ||
5588 p_id_flex_code || g_newline ||
5589 p_id_flex_num);
5590
5591 fnd_plsql_cache.custom_1to1_get_get_index(kfvssc_cache_controller,
5592 l_kfvssc_key,
5593 g_cache_index,
5594 g_cache_return_code);
5595
5596 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
5597 px_kfvssc_record := kfvssc_cache_storage(g_cache_index);
5598
5599 ELSE
5600 create_kfvssc_record(p_application_id,
5601 p_id_flex_code,
5602 p_id_flex_num,
5603 px_kfvssc_record);
5604
5605 fnd_plsql_cache.custom_1to1_get_put_index(kfvssc_cache_controller,
5606 l_kfvssc_key,
5607 g_cache_index);
5608
5609 kfvssc_cache_storage(g_cache_index) := px_kfvssc_record;
5610 END IF;
5611
5612 END get_kfvssc_record;
5613
5614 -- ==================================================
5615 FUNCTION get_kfv_concat_segs_by_ccid(p_concat_mode IN VARCHAR2,
5616 p_application_id IN NUMBER,
5617 p_id_flex_code IN VARCHAR2,
5618 p_id_flex_num IN NUMBER,
5619 p_ccid IN NUMBER,
5620 p_data_set IN NUMBER DEFAULT NULL)
5621 RETURN VARCHAR2
5622 IS
5623 l_kfvssc_record kfvssc_record_type;
5624
5625 l_data_set NUMBER;
5626 l_sql VARCHAR2(32000);
5627 l_result VARCHAR2(32000);
5628 BEGIN
5629 get_kfvssc_record(p_application_id,
5630 p_id_flex_code,
5631 p_id_flex_num,
5632 l_kfvssc_record);
5633
5634 IF (p_concat_mode = fnd_flex_server.CONCAT_MODE_PADDED) THEN
5635 l_sql := l_kfvssc_record.padded_sql;
5636 ELSE
5637 l_sql := l_kfvssc_record.compact_sql;
5638 END IF;
5639
5640 IF (p_data_set IS NULL) THEN
5641 l_data_set := p_id_flex_num;
5642 ELSE
5643 l_data_set := p_data_set;
5644 END IF;
5645
5646 l_sql := l_sql || ' WHERE ' || l_kfvssc_record.unique_id_column_name || ' = :b_unique_id_column';
5647
5648
5649 IF (l_kfvssc_record.set_defining_column_name IS NOT NULL) THEN
5650 l_sql := l_sql || ' AND ' || l_kfvssc_record.set_defining_column_name || ' = :b_set_defining_column';
5651
5652 execute immediate l_sql INTO l_result using p_ccid, l_data_set;
5653
5654 ELSE
5655 execute immediate l_sql INTO l_result using p_ccid;
5656
5657 END IF;
5658
5659 RETURN l_result;
5660 END get_kfv_concat_segs_by_ccid;
5661
5662 -- ==================================================
5663 FUNCTION get_kfv_concat_segs_by_rowid(p_concat_mode IN VARCHAR2,
5664 p_application_id IN NUMBER,
5665 p_id_flex_code IN VARCHAR2,
5666 p_id_flex_num IN NUMBER,
5667 p_rowid IN VARCHAR2)
5668 RETURN VARCHAR2
5669 IS
5670 l_kfvssc_record kfvssc_record_type;
5671
5672 l_sql VARCHAR2(32000);
5673 l_result VARCHAR2(32000);
5674 BEGIN
5675 get_kfvssc_record(p_application_id,
5676 p_id_flex_code,
5677 p_id_flex_num,
5678 l_kfvssc_record);
5679
5680 IF (p_concat_mode = fnd_flex_server.CONCAT_MODE_PADDED) THEN
5681 l_sql := l_kfvssc_record.padded_sql;
5682 ELSE
5683 l_sql := l_kfvssc_record.compact_sql;
5684 END IF;
5685
5686 l_sql := l_sql || ' WHERE ROWID = :b_rowid';
5687
5688 execute immediate l_sql INTO l_result using p_rowid;
5689
5690 RETURN l_result;
5691 END get_kfv_concat_segs_by_rowid;
5692
5693
5694 -- ==============================================================
5695 -- Bug 4725016 dbms_lock.allocate_unique issues a commit, this
5696 -- is a problem during transactionis that are opening cursors
5697 -- with 'select on update'. A commit issued at this point
5698 -- can invalidate those types of cursors. We created a private
5699 -- package that uses autonomous transaction that then calls
5700 -- dbms_lock.allocate_unique. In this way the commit will not
5701 -- affect the cursor.
5702 -- ==============================================================
5703 PROCEDURE autonomous_allocate_unique(p_lock_name IN VARCHAR2,
5704 px_lock_handle IN OUT nocopy VARCHAR2)
5705 IS
5706 PRAGMA AUTONOMOUS_TRANSACTION;
5707 BEGIN
5708
5709 dbms_lock.allocate_unique(lockname => p_lock_name,
5710 lockhandle => px_lock_handle,
5711 expiration_secs => 1*24*60*60); -- 1 day.
5712
5713 COMMIT;
5714
5715 END autonomous_allocate_unique;
5716 -- ==================================================
5717
5718 PROCEDURE request_lock(p_lock_name IN VARCHAR2,
5719 px_lock_handle IN OUT nocopy VARCHAR2)
5720 IS
5721 l_lock_status INTEGER;
5722 BEGIN
5723
5724 autonomous_allocate_unique(p_lock_name, px_lock_handle);
5725
5726 l_lock_status := dbms_lock.request(lockhandle => px_lock_handle,
5727 lockmode => dbms_lock.x_mode,
5728 timeout => dbms_lock.maxwait,
5729 release_on_commit => FALSE);
5730
5731 IF (l_lock_status <> 0) THEN
5732 raise_application_error(-20001,
5733 'Unable to request lock ' || p_lock_name ||
5734 '. dbms_lock.request() returned : ' ||
5735 l_lock_status);
5736 END IF;
5737 END request_lock;
5738
5739 -- ==================================================
5740 PROCEDURE release_lock(p_lock_name IN VARCHAR2,
5741 p_lock_handle IN VARCHAR2)
5742 IS
5743 l_lock_status INTEGER;
5744 BEGIN
5745 l_lock_status := dbms_lock.release(lockhandle => p_lock_handle);
5746
5747 IF (l_lock_status <> 0) THEN
5748 raise_application_error(-20001,
5749 'Unable to release lock ' || p_lock_name ||
5750 '. dbms_lock.release() returned : ' ||
5751 l_lock_status);
5752 END IF;
5753 END release_lock;
5754
5755
5756 -- ==================================================
5757
5758 /*
5759 Function
5760 isFlexBindChar
5761 Returns
5762 Type : Boolean
5763 Desc : Returns TRUE if the char is a valid Flex Bind char
5764 else returns FALSE
5765 Parameters
5766 p_char : Input character
5767 Scope
5768 Flex Internal
5769 Summary
5770 This function will accept a single character and test whether it is
5771 a valid Flex bind character or not
5772
5773 */
5774
5775 Function isFlexBindChar(p_char in Varchar2) return Boolean is
5776 Begin
5777 if instr(FLEX_BIND_CHARS, p_char) > 0 then
5778 return(TRUE);
5779 end if;
5780 return(FALSE);
5781 End;
5782
5783 /*
5784 Procedure
5785 queue_non_forms_warnings
5786 Parameters
5787 p_context : I : The context information for which the
5788 warning is being queued up
5789 p_bf_name : I : The BLOCK.FIELD name, which is to be
5790 displayed in the warning
5791 Scope
5792 Flex Internal
5793 Summary
5794 This procedure will put the warning messages in the global array.
5795 Those would be later retrieved by get_nonforms_warnings()
5796 */
5797 procedure queue_non_forms_warnings (
5798 p_warning_msg IN VARCHAR2
5799 ) is
5800 Begin
5801 g_non_forms_warnings_count := g_non_forms_warnings_count + 1;
5802 g_non_forms_warnings(g_non_forms_warnings_count) := substr(p_warning_msg, 1, 32000);
5803 End;
5804
5805 /*
5806 Function
5807 get_bind_name
5808 Returns :
5809 Type : Varchar2
5810 Description : Returns the actual Bind variable name
5811 without the prefix or :NULL suffix
5812 Parameters
5813 p_string : I : The input string to search for the bind
5814 variale name. Assumes the string starts
5815 with the bind variable
5816 p_bind_type : I : Bind variable type to search for. Valid values are :
5817 FF_SEGMENT - $FLEX$
5818 FF_PROFILE - $PROFILE$
5819 FF_FIELD - BLOCK.FIELD
5820 p_length : O : OUT parameter. Returns the length of
5821 the entire bind variable i.e. like
5822 length of $FLEX$.ValueSetName:NULL,
5823 which the caller might skip for further
5824 processing.
5825 Scope
5826 Flex internal
5827 Summary
5828 This function returns the bind variable name. Assumes the input string
5829 to start with the bind variable. This function is invoked from
5830 parse_bf_binds. If you need to invoke this make sure the string starts
5831 with the bind variable.
5832
5833 The following scenarios are handled:
5834
5835 1. $FLEX$
5836 - :$FLEX$.ValueSetName
5837 - :$FLEX$.ValueSetName [.{ID} | {MEANING} | {DESCRIPTION} ][ :NULL ]
5838 2. $PROFILES$
5839 - :$PROFILES$.ProfileName
5840 - :$PROFILES$.ProfileName [ :NULL ]
5841 3. BLOCK.FIELD
5842 - :BLOCK.FIELD
5843 - :BLOCK.FIELD:NULL
5844 Test case link
5845 -- to be included
5846 */
5847
5848 Function get_bind_name (
5849 p_string IN VARCHAR2,
5850 p_bind_type IN VARCHAR2,
5851 p_bind_length OUT nocopy NUMBER
5852 ) return VARCHAR2 is
5853 in_squote Boolean := FALSE;
5854 in_dquote Boolean := FALSE;
5855 v_bind_name Varchar2(32000) := NULL;
5856 v_bind_length Number := 0;
5857 v_indx Number := 0;
5858 v_str_len Number := 0;
5859 this_char Varchar2(10) := NULL;
5860 v_found_flag Boolean := FALSE;
5861 Begin
5862
5863 v_str_len := length(p_string);
5864
5865 if p_bind_type = FF_SEGMENT then -- Segment type i.e. $FLEX$ binds
5866
5867 if substr(p_string, 1, FLEX_PREFIX_LEN) <> FLEX_PREFIX then
5868
5869 v_bind_name := NULL;
5870 v_bind_length := -1;
5871
5872 goto finish;
5873
5874 end if;
5875
5876 v_indx := FLEX_PREFIX_LEN + 1;
5877
5878 elsif p_bind_type = FF_PROFILE then
5879
5880 if substr(p_string, 1, PROFILE_PREFIX_LEN) <> PROFILE_PREFIX then
5881
5882 v_bind_name := NULL;
5883 v_bind_length := -1;
5884
5885 goto finish;
5886
5887 end if;
5888
5889 v_indx := PROFILE_PREFIX_LEN + 1;
5890
5891 elsif p_bind_type = FF_FIELD then
5892
5893 v_indx := 1;
5894
5895 else
5896
5897 v_bind_name := NULL;
5898 v_bind_length := -1;
5899
5900 goto finish;
5901
5902 end if;
5903
5904 while ((NOT v_found_flag) AND (v_indx <= v_str_len))
5905 loop
5906
5907 this_char := substr(p_string, v_indx, 1);
5908
5909 /*
5910 This assumes the only legal characters in a bind
5911 variable name are A-Z, a-z, 0-9, '_', '.', ':', '$',
5912 '#'. This is covers legal names allowed by the
5913 database, forms and in c code. A '.' is needed
5914 for :block.field references. A ':' is need because
5915 it is allowable in profile names
5916 */
5917
5918 if NOT isFlexBindChar(this_char) then
5919
5920 v_found_flag := TRUE;
5921 v_indx := v_indx - 1; -- Bind var ends a char before
5922
5923 end if;
5924
5925 v_indx := v_indx + 1; -- Increment current index
5926
5927 end loop;
5928
5929 v_indx := v_indx - 1; -- Point v_indx to the end of bind var
5930
5931 v_bind_length := v_indx;
5932
5933 if p_bind_type = FF_SEGMENT then
5934
5935 v_bind_name := substr(p_string, FLEX_PREFIX_LEN + 1, v_indx - FLEX_PREFIX_LEN);
5936
5937 elsif p_bind_type = FF_PROFILE then
5938
5939 v_bind_name := substr(p_string, PROFILE_PREFIX_LEN + 1, v_indx - PROFILE_PREFIX_LEN);
5940
5941 else
5942
5943 v_bind_name := substr(p_string, 1, v_indx);
5944
5945 end if;
5946
5947 if instr(upper(v_bind_name), ':NULL') > 0 then
5948
5949 v_bind_name := substr(v_bind_name, 1, length(v_bind_name) - 5);
5950
5951 end if;
5952
5953 <<finish>>
5954
5955 p_bind_length := v_bind_length;
5956
5957 return(v_bind_name);
5958
5959 End get_bind_name;
5960
5961 /*
5962 Procedure
5963 parse_bind_names
5964 Parameters
5965 p_string : I : Input string to parse
5966 queued up. Those will be retrieved by
5967 get_nonforms_warnings()
5968 p_bind_names : O : An Array of B.F bind names found in p_string
5969 p_num_binds : O : Out parameter. Contains number of
5970 BLOCK.FIELD references found in p_string
5971 Scope
5972 Flex Internal
5973 Summary
5974 This procedure parses an input string for BLOCK.FIELD references
5975 and queues warning messages in Global Array. Returns number of
5976 such references through p_num_binds
5977 */
5978 procedure parse_bind_names (
5979 p_string IN VARCHAR2,
5980 p_bind_names OUT nocopy table_of_varchar2_32000,
5981 p_num_binds OUT nocopy NUMBER
5982 ) is
5983
5984 v_bind_name Varchar2(32000);
5985 v_bind_len Number := 0;
5986 v_flag Boolean := FALSE;
5987 v_indx Number;
5988 v_str_len Number;
5989 v_bind_count Number := 0;
5990 v_bind_names table_of_varchar2_32000;
5991 is_squote Boolean := FALSE;
5992 is_dquote Boolean := FALSE;
5993 is_bind Boolean := FALSE;
5994 this_char Varchar2(10);
5995 region_start Number;
5996 squote_ptr Number;
5997 squote_ptr_end Number;
5998 dquote_ptr Number;
5999 dquote_ptr_end Number;
6000 bind_ptr Number;
6001
6002 Begin
6003
6004 v_str_len := nvl(length(p_string), 0);
6005 if v_str_len = 0 then
6006 v_bind_count := 0;
6007 goto finish;
6008 end if;
6009 region_start := 1;
6010
6011 while (TRUE)
6012 loop
6013
6014 is_squote := FALSE;
6015 is_dquote := FALSE;
6016 is_bind := FALSE;
6017
6018 squote_ptr := INSTR(p_string, '''', region_start, 1);
6019 dquote_ptr := INSTR(p_string, '"', region_start, 1);
6020 bind_ptr := INSTR(p_string, ':', region_start, 1);
6021
6022 if bind_ptr = 0 then
6023 goto finish; -- No more binds ! Finish processing.
6024 end if;
6025
6026 if squote_ptr = dquote_ptr then -- i.e squote_ptr = dquote_ptr = 0
6027 is_bind := TRUE;
6028 else
6029 if squote_ptr = 0 then
6030 if bind_ptr < dquote_ptr then
6031 is_bind := TRUE;
6032 else
6033 is_dquote := TRUE;
6034 end if;
6035 elsif dquote_ptr = 0 then
6036 if bind_ptr < squote_ptr then
6037 is_bind := TRUE;
6038 else
6039 is_squote := TRUE;
6040 end if;
6041 else
6042 if squote_ptr = least(squote_ptr, dquote_ptr, bind_ptr) then
6043 is_squote := TRUE;
6044 elsif dquote_ptr = least(squote_ptr, dquote_ptr, bind_ptr) then
6045 is_dquote := TRUE;
6046 else
6047 is_bind := TRUE;
6048 end if;
6049 end if;
6050 end if;
6051
6052 if is_squote then
6053 squote_ptr_end := INSTR(p_string, '''', squote_ptr + 1);
6054 if squote_ptr_end = 0 then -- Unterminated quotes
6055 goto finish;
6056 else
6057 region_start := squote_ptr_end + 1;
6058 is_squote := FALSE;
6059 end if;
6060 end if;
6061
6062 if is_dquote then
6063 dquote_ptr_end := INSTR(p_string, '"', dquote_ptr + 1);
6064 if dquote_ptr_end = 0 then -- Unterminated quotes
6065 goto finish;
6066 else
6067 region_start := dquote_ptr_end + 1;
6068 is_dquote := FALSE;
6069 end if;
6070 end if;
6071
6072 if is_bind then
6073 v_indx := bind_ptr + 1;
6074 v_bind_len := 0;
6075 if substr(p_string, v_indx, FLEX_PREFIX_LEN) = FLEX_PREFIX then
6076 v_bind_name := get_bind_name (
6077 p_string => substr(p_string, v_indx, v_str_len - v_indx + 1),
6078 p_bind_type => FF_SEGMENT,
6079 p_bind_length => v_bind_len
6080 );
6081 elsif substr(p_string, v_indx, PROFILE_PREFIX_LEN) = PROFILE_PREFIX then
6082 v_bind_name := get_bind_name (
6083 p_string => substr(p_string, v_indx, v_str_len - v_indx + 1),
6084 p_bind_type => FF_PROFILE,
6085 p_bind_length => v_bind_len
6086 );
6087 else -- Hmm a B.F reference ... process it ...
6088 v_bind_name := get_bind_name (
6089 p_string => substr(p_string, v_indx, v_str_len - v_indx + 1),
6090 p_bind_type => FF_FIELD,
6091 p_bind_length => v_bind_len
6092 );
6093 v_bind_count := v_bind_count + 1;
6094 v_bind_names(v_bind_count) := v_bind_name;
6095 end if;
6096 if v_bind_len > 0 then
6097 region_start := v_indx + v_bind_len;
6098 else
6099 region_start := v_indx + 1;
6100 end if;
6101 end if;
6102
6103 end loop;
6104
6105 <<finish>>
6106 p_num_binds := v_bind_count;
6107 p_bind_names := v_bind_names;
6108 End parse_bind_names;
6109
6110 function get_non_forms_warnings return Varchar2 is
6111 l_msg_string Varchar2(32000);
6112 Begin
6113 return(l_msg_string);
6114 End get_non_forms_warnings;
6115
6116 -- ==================================================
6117
6118 PROCEDURE compute_non_forms_warnings_dff(p_application_id IN NUMBER,
6119 p_descriptive_flexfield_name IN VARCHAR2,
6120 x_warning_count OUT nocopy NUMBER)
6121 IS
6122 l_non_forms_warn_table_type table_of_varchar2_32000;
6123 l_application_short_name fnd_application.application_short_name%TYPE;
6124 l_application_name fnd_application_tl.application_name%TYPE;
6125 l_title fnd_descriptive_flexs_tl.title%TYPE;
6126 l_meaning fnd_lookup_values_vl.meaning%TYPE;
6127 l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
6128 l_srs_flag BOOLEAN;
6129
6130 PROCEDURE compute_context_segment_warn(p_application_id in NUMBER)
6131 IS
6132 e_DefaultTypeField EXCEPTION;
6133 e_DefaultTypeSQL EXCEPTION;
6134 l_reference_field fnd_descriptive_flexs.default_context_field_name%TYPE;
6135 l_validation_type fnd_flex_value_sets.validation_type%TYPE;
6136 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
6137 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
6138 l_num_binds NUMBER;
6139 l_segement_name fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
6140 BEGIN
6141
6142
6143 SELECT fnd_flex_value_sets.validation_type,fnd_flex_validation_tables.additional_where_clause,
6144 fnd_flex_validation_tables.additional_quickpick_columns,
6145 fnd_descriptive_flexs.default_context_field_name,fnd_flex_value_sets.flex_value_set_name
6146 INTO l_validation_type,l_additional_where_clause,l_additional_quickpick_columns,
6147 l_reference_field,l_value_set_name
6148 FROM fnd_flex_value_sets,fnd_descriptive_flexs,fnd_flex_validation_tables
6149 WHERE fnd_flex_value_sets.flex_value_set_id (+)= fnd_descriptive_flexs.context_override_value_set_id
6150 AND fnd_flex_value_sets.flex_value_set_id = fnd_flex_validation_tables.flex_value_set_id(+)
6151 AND fnd_descriptive_flexs.application_id = p_application_id
6152 AND fnd_descriptive_flexs.descriptive_flexfield_name = p_descriptive_flexfield_name;
6153
6154 BEGIN -- Check context segment's value set's warnings
6155
6156 IF l_validation_type = 'F' THEN -- If table validated valueset
6157
6158 IF (l_additional_quickpick_columns is not null) THEN
6159 parse_bind_names(l_additional_quickpick_columns, l_non_forms_warn_table_type, l_num_binds);
6160 IF (l_num_binds > 0) THEN
6161 FOR i IN 1 .. l_num_binds
6162 LOOP
6163 fnd_message.set_name('FND','FLEX-BLK_FLD_CTX_VSET_WARN_DFF');
6164 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6165 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6166 queue_non_forms_warnings(fnd_message.get());
6167 END LOOP;
6168 END IF;
6169 END IF;
6170
6171 IF (l_additional_where_clause is not null) THEN
6172 parse_bind_names(l_additional_where_clause, l_non_forms_warn_table_type, l_num_binds);
6173 IF (l_num_binds > 0) THEN
6174 FOR i IN 1 .. l_num_binds
6175 LOOP
6176 fnd_message.set_name('FND','FLEX-BLK_FLD_CTX_VSET_WARN_DFF');
6177 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6178 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6179 queue_non_forms_warnings(fnd_message.get());
6180 END LOOP;
6181 END IF;
6182 END IF;
6183 END IF;
6184 IF l_reference_field IS NOT NULL THEN
6185 IF (INSTR(l_reference_field,':$PROFILES$.') <> 1) THEN
6186 fnd_message.set_name('FND','FLEX-CTX_REF_FIELD_WARN_DFF');
6187 fnd_message.set_token('REFERENCE_FIELD',SUBSTR(l_reference_field,INSTR(l_reference_field,':')+1));
6188 queue_non_forms_warnings(fnd_message.get());
6189 END IF;
6190 END IF;
6191 EXCEPTION
6192 WHEN NO_DATA_FOUND THEN
6193 NULL;
6194 END; -- Check context segment's value set's warnings
6195 END compute_context_segment_warn;
6196
6197 PROCEDURE compute_context_warnings(p_context_code IN VARCHAR2)
6198 IS
6199 PROCEDURE compute_segment_warnings(p_application_column_name IN VARCHAR2)
6200 IS
6201 l_num_binds NUMBER;
6202 i NUMBER;
6203 l_format_type fnd_flex_value_sets.format_type%TYPE;
6204 l_validation_type fnd_flex_value_sets.validation_type%TYPE;
6205 l_default_type fnd_descr_flex_column_usages.default_type%TYPE;
6206 l_default_value fnd_descr_flex_column_usages.default_value%TYPE;
6207 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
6208 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
6209 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
6210 l_segment_name fnd_descr_flex_column_usages.end_user_column_name%TYPE;
6211 e_DateTime EXCEPTION;
6212 e_PairOrSpecial EXCEPTION;
6213 e_TableValidated EXCEPTION;
6214 e_DefaultTypeField EXCEPTION;
6215 e_DEfaultTypeSQL EXCEPTION;
6216 BEGIN
6217
6218 SELECT fnd_flex_value_sets.validation_type,fnd_flex_value_sets.format_type,
6219 fnd_descr_flex_column_usages.default_type,
6220 fnd_descr_flex_column_usages.default_value,fnd_flex_value_sets.flex_value_set_id,
6221 fnd_flex_value_sets.flex_value_set_name,fnd_descr_flex_column_usages.end_user_column_name
6222 INTO l_validation_type,l_format_type,l_default_type,l_default_value,l_value_set_id,
6223 l_value_set_name,l_segment_name
6224 FROM fnd_descr_flex_column_usages,fnd_flex_value_sets
6225 WHERE fnd_descr_flex_column_usages.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id (+)
6226 AND fnd_descr_flex_column_usages.application_column_name = p_application_column_name
6227 AND fnd_descr_flex_column_usages.APPLICATION_ID = p_application_id
6228 AND fnd_descr_flex_column_usages.DESCRIPTIVE_FLEXFIELD_NAME = p_descriptive_flexfield_name
6229 AND fnd_descr_flex_column_usages.ENABLED_FLAG = 'Y'
6230 AND fnd_descr_flex_column_usages.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code;
6231
6232 BEGIN -- Check segment's defaulting logic
6233 IF l_default_type = 'F' THEN -- If default type is field
6234 RAISE e_DefaultTypeField;
6235 ELSIF l_default_type = 'S' THEN -- If default type is SQL
6236 RAISE e_DefaultTypeSQL;
6237 END IF;
6238 EXCEPTION
6239 WHEN e_DefaultTypeField THEN
6240 IF l_srs_flag = FALSE THEN
6241 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_DFF');
6242 fnd_message.set_token('CONTEXT_CODE', p_context_code);
6243 ELSE
6244 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_SRS');
6245 END IF;
6246 fnd_message.set_token('SEGMENT_NAME', l_segment_name);
6247 fnd_message.set_token('BLOCK_FIELD', substr(l_default_value, instr(l_default_value, ':')+1));
6248 queue_non_forms_warnings(fnd_message.get());
6249 WHEN e_DefaultTypeSQL THEN
6250 parse_bind_names(l_default_value, l_non_forms_warn_table_type, l_num_binds);
6251 IF (l_num_binds > 0) then
6252 FOR i in 1 .. l_num_binds
6253 LOOP
6254 IF l_srs_flag = FALSE THEN
6255 fnd_message.set_name('FND','FLEX-BLK_FLD_DEF_WARN_DFF');
6256 fnd_message.set_token('CONTEXT_CODE', p_context_code);
6257 ELSE
6258 fnd_message.set_name('FND','FLEX-BLK_FLD_DEF_WARN_SRS');
6259 END IF;
6260 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6261 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6262 queue_non_forms_warnings(fnd_message.get());
6263 END LOOP;
6264 END IF;
6265 END; -- Check segment's defaulting logic
6266
6267 BEGIN -- Check segment's value set's warnings
6268 IF l_validation_type IN ('P','U') THEN -- If Pair or Special Validation Valueset
6269 RAISE e_PairOrSpecial;
6270 ELSIF l_validation_type = 'F' THEN
6271 RAISE e_TableValidated;
6272 END IF;
6273 IF l_format_type IN ('D','T') THEN
6274 RAISE e_DateTime;
6275 END IF;
6276 EXCEPTION
6277 WHEN e_PairOrSpecial THEN
6278 IF l_srs_flag = FALSE THEN
6279 fnd_message.set_name('FND','FLEX-USER_EXIT_VSET_WARN_DFF');
6280 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6281 ELSE
6282 fnd_message.set_name('FND','FLEX-USER_EXIT_VSET_WARN_SRS');
6283 END IF;
6284 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6285 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6286 SELECT meaning
6287 INTO l_meaning
6288 FROM fnd_lookup_values_vl
6289 WHERE lookup_type='SEG_VAL_TYPES'
6290 AND lookup_code = l_validation_type;
6291 fnd_message.set_token('VALIDATION_TYPE',l_meaning);
6292 queue_non_forms_warnings(fnd_message.get());
6293 WHEN e_TableValidated THEN
6294 SELECT additional_where_clause,additional_quickpick_columns
6295 INTO l_additional_where_clause,l_additional_quickpick_columns
6296 FROM fnd_flex_validation_tables
6297 WHERE flex_value_set_id = l_value_set_id;
6298 IF (l_additional_quickpick_columns is not null) THEN
6299 parse_bind_names(l_additional_quickpick_columns, l_non_forms_warn_table_type, l_num_binds);
6300 IF (l_num_binds > 0) THEN
6301 FOR i IN 1 .. l_num_binds
6302 LOOP
6303 IF l_srs_flag = FALSE THEN
6304 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_DFF');
6305 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6306 ELSE
6307 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_SRS');
6308 END IF;
6309 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6310 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6311 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6312 queue_non_forms_warnings(fnd_message.get());
6313 END LOOP;
6314 END IF;
6315 END IF;
6316
6317 IF (l_additional_where_clause is not null) THEN
6318 parse_bind_names(l_additional_where_clause, l_non_forms_warn_table_type, l_num_binds);
6319
6320 IF (l_num_binds > 0) THEN
6321 FOR i IN 1 .. l_num_binds
6322 LOOP
6323 IF l_srs_flag = FALSE THEN
6324 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_DFF');
6325 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6326 ELSE
6327 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_SRS');
6328 END IF;
6329 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6330 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6331 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6332 queue_non_forms_warnings(fnd_message.get());
6333 END LOOP;
6334 END IF;
6335 END IF;
6336 WHEN e_DateTime THEN
6337 IF l_srs_flag = FALSE THEN
6338 fnd_message.set_name('FND','FLEX-DATE_VSET_WARN_DFF');
6339 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6340 ELSE
6341 fnd_message.set_name('FND','FLEX-DATE_VSET_WARN_SRS');
6342 END IF;
6343 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6344 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6345 SELECT meaning
6346 INTO l_meaning
6347 FROM fnd_lookup_values_vl
6348 WHERE lookup_type='FIELD_TYPE'
6349 AND lookup_code = l_format_type;
6350 fnd_message.set_token('FORMAT_TYPE',l_meaning);
6351 queue_non_forms_warnings(fnd_message.get());
6352 END; -- Check segment's value set's warnings
6353 EXCEPTION
6354 WHEN NO_DATA_FOUND THEN
6355 NULL;
6356
6357 END compute_segment_warnings;
6358 BEGIN
6359 FOR v_EnabledSegments IN
6360 (SELECT application_column_name
6361 FROM fnd_descr_flex_column_usages
6362 WHERE application_id = p_application_id
6363 AND descriptive_flexfield_name = p_descriptive_flexfield_name
6364 AND descriptive_flex_context_code = p_context_code
6365 AND enabled_flag = 'Y')
6366 LOOP
6367 compute_segment_warnings(v_EnabledSegments.application_column_name);
6368 END LOOP;
6369
6370 END compute_context_warnings;
6371
6372 BEGIN
6373
6374 g_non_forms_warnings_count := 0;
6375
6376 --
6377 -- Compute non_forms warnings for DFF
6378 --
6379
6380 SELECT application_short_name, application_name
6381 INTO l_application_short_name, l_application_name
6382 FROM fnd_application_vl
6383 WHERE application_id = p_application_id;
6384
6385 SELECT title
6386 INTO l_title
6387 FROM fnd_descriptive_flexs_vl
6388 WHERE application_id = p_application_id
6389 AND descriptive_flexfield_name=p_descriptive_flexfield_name;
6390
6391 IF p_descriptive_flexfield_name LIKE '$SRS$.%' THEN
6392 l_srs_flag := TRUE;
6393 ELSE
6394 l_srs_flag := FALSE;
6395 END IF;
6396
6397 compute_context_segment_warn(p_application_id);
6398
6399 FOR v_EnabledContext IN
6400 (SELECT descriptive_flex_context_code
6401 FROM fnd_descr_flex_contexts
6402 WHERE application_id = p_application_id
6403 AND descriptive_flexfield_name = p_descriptive_flexfield_name
6404 AND enabled_flag = 'Y')
6405 LOOP
6406 compute_context_warnings(v_EnabledContext.descriptive_flex_context_code);
6407 END LOOP;
6408
6409 --
6410 -- Top Warning Message
6411 --
6412
6413 IF (g_non_forms_warnings_count > 0) THEN
6414 IF (p_descriptive_flexfield_name LIKE '$SRS$.%') THEN
6415 fnd_message.set_name('FND', 'FLEX-FORMS_ONLY_WARN_SRS');
6416 fnd_message.set_token('PROGRAM_SHORT_NAME', Substr(p_descriptive_flexfield_name, Length('$SRS$.') + 1));
6417 ELSE
6418 fnd_message.set_name('FND', 'FLEX-FORMS_ONLY_WARN_DFF');
6419 fnd_message.set_token('TITLE', l_title);
6420 END IF;
6421 fnd_message.set_token('APPLICATION_NAME', l_application_name);
6422 fnd_message.set_token('WARNING_COUNT', g_non_forms_warnings_count);
6423 g_non_forms_warnings(0) := fnd_message.get();
6424 END IF;
6425 x_warning_count := g_non_forms_warnings_count;
6426
6427 END compute_non_forms_warnings_dff;
6428
6429 -- ==================================================
6430
6431 PROCEDURE compute_non_forms_warnings_kff(p_application_id IN NUMBER,
6432 p_id_flex_code IN VARCHAR2,
6433 p_id_flex_num IN NUMBER,
6434 x_warning_count OUT nocopy NUMBER)
6435 IS
6436
6437 --
6438 -- Compute non_forms warnings for KFF
6439 --
6440
6441 l_application_short_name fnd_application.application_short_name%TYPE;
6442 l_application_name fnd_application_tl.application_name%TYPE;
6443 l_id_flex_structure_code fnd_id_flex_structures.id_flex_structure_code%TYPE;
6444 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
6445 l_nsegments NUMBER;
6446 l_message VARCHAR2(1000);
6447 l_validation_type VARCHAR2(1000);
6448 l_format_type VARCHAR2(1000);
6449 l_flextype FND_FLEX_KEY_API.FLEXFIELD_TYPE;
6450 l_strctype FND_FLEX_KEY_API.STRUCTURE_TYPE;
6451 l_segtype FND_FLEX_KEY_API.SEGMENT_TYPE;
6452 l_seglist FND_FLEX_KEY_API.SEGMENT_LIST;
6453 l_vset_r FND_VSET.VALUESET_R;
6454 l_vset_dr FND_VSET.VALUESET_DR;
6455
6456 PROCEDURE compute_segment_warnings (p_application_column_name IN VARCHAR2)
6457 IS
6458 l_table_of_varchar2_32000 table_of_varchar2_32000;
6459 l_num_binds NUMBER;
6460 i NUMBER;
6461 BEGIN
6462
6463 l_segtype := fnd_flex_key_api.find_segment(l_flextype, l_strctype, p_application_column_name);
6464
6465 if (l_segtype.value_set_id is not null) then
6466
6467 fnd_vset.get_valueset(l_segtype.value_set_id, l_vset_r, l_vset_dr);
6468 if (l_vset_r.validation_type in ('P', 'U')) then
6469 fnd_message.set_name('FND', 'FLEX-USER_EXIT_VSET_WARN_KFF');
6470 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6471 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6472 select meaning into l_validation_type
6473 from fnd_lookup_values_vl
6474 where lookup_type='SEG_VAL_TYPES'
6475 and lookup_code=l_vset_r.validation_type;
6476 fnd_message.set_token('VALIDATION_TYPE', l_validation_type);
6477 l_message := FND_MESSAGE.get;
6478 queue_non_forms_warnings(l_message);
6479 end if;
6480
6481 if (l_vset_dr.format_type in ('D', 'T')) then
6482 fnd_message.set_name('FND', 'FLEX-DATE_VSET_WARN_KFF');
6483 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6484 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6485 select meaning into l_format_type
6486 from fnd_lookup_values_vl
6487 where lookup_type='FIELD_TYPE'
6488 and lookup_code=l_vset_dr.format_type;
6489 fnd_message.set_token('FORMAT_TYPE', l_format_type);
6490 l_message := FND_MESSAGE.get;
6491 queue_non_forms_warnings(l_message);
6492 end if;
6493
6494 if (l_vset_r.validation_type = 'F') then
6495
6496 select additional_quickpick_columns into l_additional_quickpick_columns from fnd_flex_validation_tables where flex_value_set_id=l_segtype.value_set_id;
6497
6498 if (l_additional_quickpick_columns is not null) then
6499 parse_bind_names(l_additional_quickpick_columns, l_table_of_varchar2_32000, l_num_binds);
6500 if (l_num_binds > 0) then
6501 for i in 1 .. l_num_binds
6502 loop
6503 fnd_message.set_name('FND', 'FLEX-BLK_FLD_VSET_WARN_KFF');
6504 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6505 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6506 fnd_message.set_token('BLOCK_FIELD', l_table_of_varchar2_32000(i));
6507 l_message := fnd_message.get;
6508 queue_non_forms_warnings(l_message);
6509 end loop;
6510 end if;
6511 end if;
6512
6513 if (l_vset_r.table_info.where_clause is not null) then
6514 parse_bind_names(l_vset_r.table_info.where_clause, l_table_of_varchar2_32000, l_num_binds);
6515 if (l_num_binds > 0) then
6516 for i in 1 .. l_num_binds
6517 loop
6518 fnd_message.set_name('FND', 'FLEX-BLK_FLD_VSET_WARN_KFF');
6519 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6520 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6521 fnd_message.set_token('BLOCK_FIELD', l_table_of_varchar2_32000(i));
6522 l_message := fnd_message.get;
6523 queue_non_forms_warnings(l_message);
6524 end loop;
6525 end if;
6526 end if;
6527
6528 end if;
6529
6530 end if;
6531
6532 if (l_segtype.default_type = 'S') then
6533 parse_bind_names(l_segtype.default_value, l_table_of_varchar2_32000, l_num_binds);
6534 if (l_num_binds > 0) then
6535 for i in 1 .. l_num_binds
6536 loop
6537 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_KFF');
6538 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6539 fnd_message.set_token('BLOCK_FIELD', l_table_of_varchar2_32000(i));
6540 l_message := fnd_message.get;
6541 queue_non_forms_warnings(l_message);
6542 end loop;
6543 end if;
6544 end if;
6545
6546 if (l_segtype.default_type = 'F') then
6547 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_KFF');
6548 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6549 fnd_message.set_token('BLOCK_FIELD', substr(l_segtype.default_value, instr(l_segtype.default_value, ':')+1));
6550 l_message := fnd_message.get;
6551 queue_non_forms_warnings(l_message);
6552 end if;
6553
6554 EXCEPTION
6555 when others then
6556 null;
6557
6558 END compute_segment_warnings;
6559
6560 BEGIN
6561
6562 g_non_forms_warnings_count := 0;
6563
6564 fnd_flex_key_api.set_session_mode(session_mode => 'customer_data');
6565
6566 select application_short_name, application_name
6567 into l_application_short_name, l_application_name
6568 from fnd_application_vl
6569 where application_id=p_application_id;
6570
6571 select id_flex_structure_code into l_id_flex_structure_code
6572 from fnd_id_flex_structures
6573 where application_id=p_application_id
6574 and id_flex_code=p_id_flex_code
6575 and id_flex_num=p_id_flex_num;
6576
6577 l_flextype := fnd_flex_key_api.find_flexfield(appl_short_name => l_application_short_name, flex_code => p_id_flex_code);
6578
6579 l_strctype := fnd_flex_key_api.find_structure(l_flextype, l_id_flex_structure_code);
6580
6581 fnd_flex_key_api.get_segments(l_flextype, l_strctype, TRUE, l_nsegments, l_seglist);
6582
6583 for i in 1..l_nsegments loop
6584
6585 compute_segment_warnings(l_seglist(i));
6586
6587 end loop;
6588
6589 --
6590 -- Top Warning Message
6591 --
6592 IF (g_non_forms_warnings_count > 0) THEN
6593 fnd_message.set_name('FND', 'FLEX-FORMS_ONLY_WARN_KFF');
6594 fnd_message.set_token('STRUCTURE_CODE', l_id_flex_structure_code);
6595 fnd_message.set_token('TITLE', l_flextype.flex_title);
6596 fnd_message.set_token('APPLICATION_NAME', l_application_name);
6597 fnd_message.set_token('WARNING_COUNT', g_non_forms_warnings_count);
6598 g_non_forms_warnings(0) := fnd_message.get();
6599 END IF;
6600 x_warning_count := g_non_forms_warnings_count;
6601
6602 END compute_non_forms_warnings_kff;
6603
6604 -- ==================================================
6605
6606 FUNCTION get_non_forms_warning(p_warning_index IN NUMBER)
6607 RETURN VARCHAR2
6608 IS
6609 BEGIN
6610 IF (p_warning_index >= 0 AND
6611 p_warning_index <= g_non_forms_warnings_count) THEN
6612 RETURN g_non_forms_warnings(p_warning_index);
6613 ELSE
6614 RETURN NULL;
6615 END IF;
6616 END get_non_forms_warning;
6617
6618 BEGIN
6619 --
6620 -- Function calls for global initializations
6621 --
6622 g_newline := fnd_global.newline;
6623 blanks := ' ' || fnd_global.tab || fnd_global.newline;
6624 g_non_forms_warnings_count := 0;
6625
6626 -- Initialize Caches
6627
6628 fnd_plsql_cache.custom_1to1_init('SSV.VST',
6629 vst_cache_controller);
6630 vst_cache_storage.DELETE;
6631
6632 fnd_plsql_cache.custom_1to1_init('SSV.KFVSSC',
6633 kfvssc_cache_controller);
6634 kfvssc_cache_storage.DELETE;
6635
6636 fnd_plsql_cache.custom_1to1_init('SSV.KFVCCT',
6637 kfvcct_cache_controller);
6638
6639 kfvcct_cache_storage.DELETE;
6640
6641 fnd_plsql_cache.generic_1to1_init('SSV.VSC',
6642 vsc_cache_controller,
6643 vsc_cache_storage);
6644
6645 END fnd_flex_server;