[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_SERVER
Source
1 PACKAGE BODY fnd_flex_server AS
2 /* $Header: AFFFSSVB.pls 120.13.12010000.3 2008/10/10 18:35:41 hgeorgi 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 BEGIN
1706 l_dinsert := Nvl(dinsert, 'N');
1707 IF (fnd_flex_server1.g_debug_level > 0) THEN
1708 fnd_flex_server1.add_debug('BEGIN SSV.validation_engine()');
1709 END IF;
1710
1711 -- Initialize all output variables so that returning from any point
1712 -- results in a valid state.
1713 --
1714 nvalidated := 0;
1715 segment_types.nsegs := 0;
1716 displayed_segs.n_segflags := 0;
1717 derived_quals.nquals := 0;
1718 table_quals.nquals := 0;
1719 n_column_vals := 0;
1720 new_combination := FALSE;
1721 v_status := FND_FLEX_SERVER1.VV_ERROR;
1722
1723 -- Initialize everything which affects returned information. This way we
1724 -- can process all returned information before returning when exiting from
1725 -- any point in this code even if there is an error.
1726 -- Dont worry about initializing strings to null.
1727
1728 nvals := 0;
1729 nsegs := 0;
1730 segtypes.nsegs := 0;
1731 disp_segs.n_segflags := 0;
1732 drv_quals.nquals := 0;
1733 tbl_quals.nquals := 0;
1734 n_xcols := 0;
1735 new_comb := FALSE;
1736 errcode := FND_FLEX_SERVER1.VV_ERROR;
1737
1738 if((concat_segs is null) and (ccid_in is null)) then
1739 entered := 'N';
1740 else
1741 entered := 'Y';
1742 end if;
1743
1744 -- Set cc_struct to the structure number to be used when interacting
1745 -- with the combinations table
1746 --
1747 if((data_set is null) or (data_set = -1)) then
1748 cc_struct := flex_num;
1749 else
1750 cc_struct := data_set;
1751 end if;
1752
1753 -- Get all required info about the desired flexfield structure.
1754 --
1755 if(FND_FLEX_SERVER2.get_keystruct(flex_app_sname, flex_code,
1756 select_comb_from_view, flex_num,
1757 kff_id, kff_info, kff_cc) = FALSE) then
1758 goto return_error;
1759 end if;
1760
1761 --
1762 -- If key flex is multi lingual then no dinsert.
1763 --
1764 IF (Upper(kff_cc.application_table_name) <>
1765 Upper(kff_cc.select_comb_from)) THEN
1766 l_dinsert := 'N';
1767 END IF;
1768
1769 -- Check maximum lengths of input strings
1770 --
1771 if(LENGTHB(display_segstr) > MAX_ARG_LEN) then
1772 big_arg := 'DISPLAYABLE';
1773 elsif(LENGTHB(where_clause) > MAX_ARG_LEN) then
1774 big_arg := 'WHERE_CLAUSE';
1775 elsif(LENGTHB(vrulestr) > MAX_ARG_LEN) then
1776 big_arg := 'VRULE';
1777 elsif(LENGTHB(get_extra_cols) > MAX_ARG_LEN) then
1778 big_arg := 'COLUMN';
1779 else
1780 big_arg := NULL;
1781 end if;
1782
1783 if(big_arg is not null) then
1784 FND_MESSAGE.set_name('FND', 'FLEX-ARGUMENT TOO LONG');
1785 FND_MESSAGE.set_token('ARG', big_arg);
1786 FND_MESSAGE.set_token('MAXLEN', to_char(MAX_ARG_LEN));
1787 goto return_error;
1788 end if;
1789
1790 -- Limit concatenated segment length for compatibility with client
1791 --
1792 if(LENGTHB(concat_segs) > MAX_CATSEG_LEN) then
1793 FND_MESSAGE.set_name('FND', 'FLEX-CONCAT LEN > IAPFLEN');
1794 FND_MESSAGE.set_token('MAXFLDLEN', to_char(MAX_CATSEG_LEN));
1795 goto return_error;
1796 end if;
1797
1798 -- First check that operation makes sense
1799 --
1800 if((invoking_mode is null) or
1801 (invoking_mode NOT IN ('V', 'P', 'L'))) then
1802 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD INVOKE');
1803 goto return_error;
1804 end if;
1805
1806 if((validate_mode is null) or
1807 (validate_mode not in
1808 ('FULL', 'PARTIAL', 'PARTIAL_IF_POSSIBLE', 'FOR_INSERT'))) then
1809 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD VALMODE');
1810 goto return_error;
1811 end if;
1812
1813 if((invoking_mode in ('V', 'P')) and
1814 ((vals_or_ids is null) or
1815 (vals_or_ids not in ('V', 'I')))) then
1816 FND_MESSAGE.set_name('FND', 'FLEX-SSV BAD FLAG');
1817 goto return_error;
1818 end if;
1819
1820 -- Set validation flags.
1821 --
1822 vv_flags.default_all_displayed := TRUE;
1823 vv_flags.values_not_ids := (vals_or_ids = 'V') AND (invoking_mode <> 'L');
1824 vv_flags.default_all_required := ((invoking_mode = 'V') and
1825 (required = 'Y') and (entered = 'N'));
1826 vv_flags.default_non_displayed := (invoking_mode in ('P', 'V'));
1827 vv_flags.allow_nulls := ((allow_nulls = 'Y') and
1828 (validate_mode in ('PARTIAL', 'PARTIAL_IF_POSSIBLE')));
1829 vv_flags.message_on_null := TRUE;
1830 vv_flags.all_orphans_valid := (validate_mode = 'PARTIAL_IF_POSSIBLE');
1831 vv_flags.ignore_security := ((invoking_mode = 'L') AND (qsecurity = 'N'));
1832 vv_flags.ignore_expired := (invoking_mode = 'L');
1833 vv_flags.ignore_disabled := (invoking_mode = 'L');
1834 vv_flags.message_on_security := ((invoking_mode <> 'L') OR
1835 ((invoking_mode = 'L') AND (qsecurity = 'Y')));
1836 vv_flags.stop_on_value_error := (invoking_mode <> 'P');
1837 vv_flags.exact_nsegs_required := ((invoking_mode = 'L') or
1838 ((invoking_mode = 'V') and not vv_flags.default_all_required));
1839 vv_flags.stop_on_security := ((invoking_mode = 'V') OR
1840 ((invoking_mode = 'L') AND (qsecurity = 'Y')));
1841
1842 /* invoking_mode is added for bug872437. */
1843
1844 vv_flags.invoking_mode := invoking_mode;
1845
1846 -- Parse inputs
1847 --
1848 if((parse_vrules(vrulestr, v_rules) < 0) OR
1849 (parse_displayed(kff_id, display_segstr, disp_segs) = FALSE)) then
1850 goto return_error;
1851 end if;
1852
1853 IF (get_extra_cols IS NOT NULL) THEN
1854 n_xcols := FND_FLEX_SERVER1.to_stringarray2(get_extra_cols,
1855 FND_FLEX_SERVER1.TERMINATOR, rq_xcols);
1856 ELSE
1857 n_xcols := 0;
1858 END IF;
1859
1860 -- Initialize extra columns to null
1861 -- to prevent no data found error if combination not looked up.
1862 --
1863 for i in 1..n_xcols loop
1864 xcol_vals(i) := NULL;
1865 end loop;
1866
1867 -- List the parsed v-rules if any
1868 --
1869 IF (fnd_flex_server1.g_debug_level > 0) THEN
1870 if(v_rules.nvrules > 0) then
1871 FND_FLEX_SERVER1.add_debug(to_char(v_rules.nvrules) || '{Vrules: ');
1872 for i in 1..v_rules.nvrules loop
1873 FND_FLEX_SERVER1.add_debug('(' || v_rules.fq_names(i) || ', ');
1874 FND_FLEX_SERVER1.add_debug(v_rules.sq_names(i) || ', ');
1875 FND_FLEX_SERVER1.add_debug(v_rules.ie_flags(i) || ', ');
1876 FND_FLEX_SERVER1.add_debug(v_rules.cat_vals(i) || ', ');
1877 FND_FLEX_SERVER1.add_debug(v_rules.app_names(i) || ', ');
1878 FND_FLEX_SERVER1.add_debug(v_rules.err_names(i) || ') ');
1879 end loop;
1880 FND_FLEX_SERVER1.add_debug('} ');
1881 end if;
1882
1883 -- Set up some debug information to return
1884 --
1885 FND_FLEX_SERVER1.add_debug('User AppId = ' || to_char(user_apid) ||
1886 ', User Resp = ' || to_char(user_resp) ||
1887 ', User Id = ' || to_char(userid));
1888 FND_FLEX_SERVER1.add_debug('Flex Appl Id = ' || kff_id.application_id ||
1889 ', Flex Code = ' || flex_code ||
1890 ', Structure Number = '|| flex_num ||
1891 ', Dinsert = ' || l_dinsert || '. ');
1892 END IF;
1893
1894 -- If dinsert = 'D' we do everything except insert the combination.
1895 --
1896 defer_insrt := ((l_dinsert is not null) and (l_dinsert = 'D'));
1897
1898 -- Bug 1531345
1899
1900 no_at := ((l_dinsert is not null) and (l_dinsert = 'O'));
1901
1902 -- Dynamic inserts always allowed in FOR_INSERT mode
1903 --
1904 dynam_insrt := (validate_mode = 'FOR_INSERT') or
1905 ((l_dinsert is not null) and (l_dinsert in ('Y', 'D', 'O')) and
1906 ((kff_cc.application_table_type is null) or
1907 (kff_cc.application_table_type = 'G')) and
1908 (kff_info.dynamic_inserts_feasible_flag = 'Y') and
1909 (kff_info.dynamic_inserts_allowed_flag = 'Y'));
1910
1911 -- LOADID in FOR_INSERT mode returns ccid_in as the combination id.
1912
1913 if((invoking_mode = 'L') and (validate_mode = 'FOR_INSERT')) then
1914 comb_id := ccid_in;
1915 end if;
1916
1917 -- Concatenated segments are input for VALID() or POPID() modes, and for
1918 -- LOADID() mode for invoking_modes other than FULL.
1919 -- For other modes look up the segments by CCID first and check the table
1920 -- qualifiers against the vrules. If we need to look up by CCID, we will
1921 -- first have to get the names of the segment columns and qualifier columns.
1922 --
1923 if((invoking_mode IN ('V', 'P')) or ((invoking_mode = 'L') and
1924 (validate_mode in ('PARTIAL','PARTIAL_IF_POSSIBLE','FOR_INSERT')))) then
1925
1926 -- Convert concatenated segments to array and check there are not too many
1927 --
1928 if(FND_FLEX_SERVER2.breakup_catsegs(concat_segs,
1929 kff_info.concatenated_segment_delimiter,
1930 vv_flags.values_not_ids, disp_segs, nsegs, segs) = FALSE) then
1931 goto return_error;
1932 end if;
1933
1934 IF (fnd_flex_server1.g_debug_level > 0) THEN
1935 IF (nsegs > 0) THEN
1936 catsegs := FND_FLEX_SERVER1.from_stringarray(nsegs, segs, '*');
1937 ELSE
1938 catsegs := '';
1939 END IF;
1940 FND_FLEX_SERVER1.add_debug(catsegs);
1941 END IF;
1942
1943 else
1944
1945 -- LOADID() in FULL mode.
1946 -- Look up the segment id's from the table before validating them.
1947 --
1948 if((ccid_in is null) or (ccid_in < 0)) then
1949 FND_MESSAGE.set_name('FND', 'FLEX-BAD CCID INPUT');
1950 FND_MESSAGE.set_token('CCID', to_char(ccid_in));
1951 goto return_error;
1952 end if;
1953
1954 -- Get segment mapping to code combinations table
1955 --
1956 if(FND_FLEX_SERVER2.get_struct_cols(kff_id, kff_cc.table_application_id,
1957 kff_cc.combination_table_id, nsegs, cc_cols,
1958 cc_coltypes, segtypes) = FALSE) then
1959 goto return_error;
1960 end if;
1961 IF (fnd_flex_server1.g_debug_level > 0) THEN
1962 FND_FLEX_SERVER1.add_debug(' LOADID() found segment mapping: [');
1963 for i in 1..nsegs loop
1964 FND_FLEX_SERVER1.add_debug(cc_cols(i) || ' ');
1965 --FND_FLEX_SERVER1.add_debug(cc_cols(i)||':'||cc_coltypes(i)||' ');
1966 end loop;
1967 FND_FLEX_SERVER1.add_debug('] ');
1968 END IF;
1969
1970 -- In LOADID() we have to look up the qualifier names, columns, and
1971 -- default values prior to looking for the combination. Therefore we
1972 -- have to hit the tables now to get this information. However, in
1973 -- POPID() and VALID() we can wait until after value validation and get
1974 -- the qualifier names and table columns from the returned qualifier info
1975 -- thus avoiding the extra table hit below.
1976 --
1977 if(FND_FLEX_SERVER2.get_all_segquals(kff_id, tbl_quals) = FALSE) then
1978 goto return_error;
1979 end if;
1980
1981 IF (fnd_flex_server1.g_debug_level > 0) THEN
1982 FND_FLEX_SERVER1.add_debug(' LOADID() found qualifier cols: [');
1983 for i in 1..tbl_quals.nquals loop
1984 FND_FLEX_SERVER1.add_debug(tbl_quals.derived_cols(i) || ' ');
1985 end loop;
1986 FND_FLEX_SERVER1.add_debug('] ');
1987 END IF;
1988
1989 -- Next find combination by CCID
1990 --
1991 comb_id := ccid_in;
1992
1993 nfound := find_combination(cc_struct, kff_cc, nsegs, cc_cols,
1994 cc_coltypes, segtypes, tbl_quals.nquals,
1995 tbl_quals.derived_cols, n_xcols, rq_xcols, NULL,
1996 comb_id, value_ids, tbl_derv, tbl_quals.sq_values,
1997 xcol_vals);
1998 if(nfound = 0) then
1999 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION NOT FOUND');
2000 FND_MESSAGE.set_token('CCID', ccid_in);
2001 FND_MESSAGE.set_token('APNM', flex_app_sname);
2002 FND_MESSAGE.set_token('CODE', flex_code);
2003 FND_MESSAGE.set_token('NUM', flex_num);
2004 end if;
2005 if(nfound <> 1) then
2006 goto return_error;
2007 end if;
2008
2009 -- Assign found ids to segment array in.
2010 --
2011 for i in 1..nsegs loop
2012 segs(i) := value_ids(i);
2013 end loop;
2014
2015 IF (fnd_flex_server1.g_debug_level > 0) THEN
2016 FND_FLEX_SERVER1.add_debug(' LOADID() found combination. Segids: ');
2017 IF (nsegs > 0) THEN
2018 catsegs := FND_FLEX_SERVER1.from_stringarray(nsegs, segs, '*');
2019 ELSE
2020 catsegs := '';
2021 END IF;
2022 FND_FLEX_SERVER1.add_debug(catsegs);
2023 END IF;
2024
2025 -- Next check qualifiers in the table against the vrules.
2026 -- This is done to mimic client behavior, but I think LOADID() shouldnt care.
2027 --
2028 /* bug872437. No vrule check in loadid().
2029 errcode := check_table_comb(tbl_derv,tbl_quals,v_rules,val_date,FALSE);
2030 if(errcode <> FND_FLEX_SERVER1.VV_VALID) then
2031 goto return_outvars;
2032 end if;
2033 */
2034 -- Finally, look up all the displayed values and descriptions and
2035 -- derive the qualifiers. Also check value validation against vrules
2036 -- and optionally against security rules.
2037 --
2038 -- Let this fall through to the validate_struct call and then exit.
2039
2040 end if;
2041
2042 -- LOADID() will validate ids.
2043 -- POPID() and VALID() require full value validation.
2044 -- Side effect is to fill cc_cols array with the names of the columns into
2045 -- which the segments will be inserted.
2046 --
2047 errcode := FND_FLEX_SERVER1.validate_struct(kff_id,
2048 kff_cc.table_application_id, kff_cc.combination_table_id,
2049 nsegs, segs, disp_segs, vv_flags, val_date, v_rules, user_apid,
2050 user_resp, nvals, segtypes, segcodes, cc_cols, cc_coltypes,
2051 value_dvals, value_vals, value_ids, value_descs, desc_lens,
2052 derv, drv_quals, errsegnum);
2053
2054 IF (fnd_flex_server1.g_debug_level > 0) THEN
2055 FND_FLEX_SERVER1.add_debug('SV1.validate_struct() returns errcode ' ||
2056 to_char(errcode) ||' and '|| to_char(nvals) ||
2057 ' values. SegCodes: ' || segcodes || '.' ||
2058 ' ErrSeg: ' || to_char(errsegnum));
2059 FND_FLEX_SERVER1.add_debug(' Returned arrays:');
2060 for i in 1..nvals loop
2061 FND_FLEX_SERVER1.add_debug('"' || segtypes.vs_format(i) ||
2062 to_char(segtypes.vs_maxsize(i), 'S099') ||
2063 '*' || value_dvals(i) || '*' ||
2064 cc_cols(i) || ':' || cc_coltypes(i) ||'" ');
2065 end loop;
2066 FND_FLEX_SERVER1.add_debug('Derived values: Start Date:' ||
2067 to_char(derv.start_valid, DATE_DEBUG_FMT) ||
2068 ' End Date:' ||
2069 to_char(derv.end_valid, DATE_DEBUG_FMT) ||
2070 ' Enabled=' || derv.enabled_flag ||
2071 ' Summary=' || derv.summary_flag || '.');
2072
2073 -- Print derived qualifiers to debug string
2074 --
2075 FND_FLEX_SERVER1.add_debug('Derived Qualifiers=');
2076 for i in 1..drv_quals.nquals LOOP
2077 FND_FLEX_SERVER1.add_debug('(' || drv_quals.fq_names(i) || ', '||
2078 drv_quals.sq_names(i) || ', ' ||
2079 drv_quals.sq_values(i) || ', ' ||
2080 drv_quals.derived_cols(i) || ')');
2081 end loop;
2082 END IF;
2083 -- We are done if this is LOADID() or if PARTIAL or PARTIAL_IF_POSSIBLE
2084 -- or if any errors whatsoever.
2085
2086 if((invoking_mode = 'L') or
2087 (validate_mode in ('PARTIAL', 'PARTIAL_IF_POSSIBLE')) or
2088 (errcode <> FND_FLEX_SERVER1.VV_VALID)) then
2089 goto return_outvars;
2090 end if;
2091
2092
2093 -- If this is not LOADID(), then we still have to get the names and colums
2094 -- for all the qualifiers stored in the table. We can get this without an
2095 -- extra table hit, by copying the qualifiers returned by validate_struct().
2096 -- Note that the values may change if the combination is in the table.
2097 --
2098 for i in 1..drv_quals.nquals loop
2099 tbl_quals.fq_names(i) := drv_quals.fq_names(i);
2100 tbl_quals.sq_names(i) := drv_quals.sq_names(i);
2101 tbl_quals.sq_values(i) := drv_quals.sq_values(i);
2102 tbl_quals.derived_cols(i) := drv_quals.derived_cols(i);
2103 end loop;
2104 tbl_quals.nquals := drv_quals.nquals;
2105 tbl_derv := derv;
2106
2107 -- Set for_insert flag if in for_insert mode
2108 --
2109 for_insert := (validate_mode = 'FOR_INSERT');
2110
2111 -- We also have to substitute the values for $PROFILES$ in the where clause
2112 -- before using it.
2113 -- Keep nice_where_cl null if in for insert mode.
2114 --
2115 if(not for_insert) then
2116 errcode:=FND_FLEX_SERVER1.parse_where_token(where_clause,nice_where_cl);
2117 if(errcode <> FND_FLEX_SERVER1.VV_VALID) then
2118 goto return_outvars;
2119 end if;
2120 end if;
2121
2122 -- In FOR_INSERT mode, it is an error if the combination exists even without
2123 -- the where clause, unless the ccid of the found combination is ccid_in
2124 -- (in which case we have re-queried an existing combination).
2125 --
2126 -- Otherwise check to see if the combination is there. Set comb_id to null
2127 -- to make sure we search by segment ids rather than by CCID.
2128 --
2129
2130 comb_id := NULL;
2131 nfound := find_combination(cc_struct, kff_cc, nvals, cc_cols,
2132 cc_coltypes, segtypes, tbl_quals.nquals,
2133 tbl_quals.derived_cols, n_xcols, rq_xcols,
2134 nice_where_cl, comb_id, value_ids, tbl_derv,
2135 tbl_quals.sq_values, xcol_vals);
2136
2137 if(for_insert) then
2138
2139 -- In for insert mode,
2140 -- if combination exists and ccid matches ccid_in we are done.
2141 -- If it exists and ccid does not match its an error.
2142 -- If combination does not exist continue on.
2143 --
2144 if((nfound = 1) and (ccid_in is not null) and (ccid_in <> 0) and
2145 (ccid_in <> -1) and (comb_id = ccid_in)) then
2146 errcode := FND_FLEX_SERVER1.VV_VALID;
2147 goto return_outvars;
2148 elsif(nfound >= 1) then
2149 FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
2150 errcode := FND_FLEX_SERVER1.VV_COMBEXISTS;
2151 goto return_outvars;
2152 else
2153 if(nfound <> 0) then
2154 goto return_error;
2155 end if;
2156 end if;
2157
2158 else
2159
2160 if(nfound > 0) then
2161 new_comb := FALSE;
2162 IF (fnd_flex_server1.g_debug_level > 0) THEN
2163 FND_FLEX_SERVER1.add_debug(' Combination already exists. CCID = ');
2164 FND_FLEX_SERVER1.add_debug(to_char(comb_id));
2165 END IF;
2166 end if;
2167
2168 -- If found, Check qualifiers in the table against the vrules and return.
2169 -- This is done to mimic client behavior.
2170 --
2171 if(nfound = 1) then
2172 errcode := check_table_comb(tbl_derv, tbl_quals,v_rules,val_date,TRUE);
2173 goto return_outvars;
2174 elsif((nfound > 1) or (nfound < 0)) then
2175 goto return_error;
2176 else
2177 null;
2178 end if;
2179
2180 end if;
2181
2182 -- If dynamic insert requested, and where clause is not null,
2183 -- then check without the where clause.
2184 --
2185 -- if((dynam_insrt) and (where_clause is not null)) then
2186 -- check where_clause all the time.
2187 --
2188 if(where_clause is not null) then
2189 comb_id := NULL;
2190 nfound := find_combination(cc_struct, kff_cc, nvals, cc_cols,
2191 cc_coltypes, segtypes, tbl_quals.nquals,
2192 tbl_quals.derived_cols, n_xcols, rq_xcols, NULL,
2193 comb_id, value_ids, tbl_derv, tbl_quals.sq_values,
2194 xcol_vals);
2195 if(nfound = 1) THEN
2196 IF (NOT parse_set_msg(where_clause_msg)) THEN
2197 FND_MESSAGE.set_name('FND', 'FLEX-WHERE CLAUSE FAILURE');
2198 FND_MESSAGE.set_token('WHERE', where_clause);
2199 END IF;
2200 errcode := FND_FLEX_SERVER1.VV_WHEREFAILURE;
2201 goto return_outvars;
2202 elsif((nfound > 1) or (nfound < 0)) then
2203 goto return_error;
2204 else
2205 null;
2206 end if;
2207 end if;
2208
2209 -- If we get to here, combination is not found.
2210 -- If dynamic insert not requested, return error.
2211 --
2212 -- This part was before the WHERE_CLAUSE check.
2213 --
2214 if(not dynam_insrt) THEN
2215 IF (NOT parse_set_msg(no_combmsg)) THEN
2216 FND_MESSAGE.set_name('FND', 'FLEX-NO DYNAMIC INSERTS');
2217 END IF;
2218 errcode := FND_FLEX_SERVER1.VV_COMBNOTFOUND;
2219 goto return_outvars;
2220 end if;
2221
2222
2223 -- Next do the cross validation if flag is set.
2224 --
2225 if(kff_info.cross_segment_validation_flag = 'Y') then
2226 errcode := FND_FLEX_SERVER2.cross_validate(nvals, value_vals, segtypes,
2227 val_date, kff_id, error_col);
2228 if(errcode <> FND_FLEX_SERVER1.VV_VALID) then
2229 errsegnum := find_column_index(cc_cols, nvals, error_col);
2230 IF (fnd_flex_server1.g_debug_level > 0) THEN
2231 FND_FLEX_SERVER1.add_debug(' CROSS-VALIDATION-INVALID ON SEG ' ||
2232 to_char(errsegnum) || '. ');
2233 END IF;
2234 goto return_outvars;
2235 end if;
2236 end if;
2237
2238
2239 -- Finally, Insert the combination. Feasibility already checked above.
2240 -- If combination is new, call FDFGLI on accounting flexfield.
2241 -- Else, check if combination existing in the combinations table is
2242 -- disabled, expired or violates vrules.
2243 -- Set savepoint and rollback if insert or fdfgli fails.
2244 -- Once savepoint has been set we need to go to rollback_error rather
2245 -- than return_error to make sure changes are rolled back on error.
2246 -- Also need to rollback if combination already exists to remove hash lock.
2247 -- Each savepoint outdates the previous one with the same name.
2248 --
2249 -- Bug 1531345 - Commit in AT causing problems, so allow for original insert
2250 -- if no_at is true.
2251 IF ((invoking_mode IN ('V')) and (defer_insrt = FALSE) AND
2252 ((for_insert) OR (no_at))) THEN
2253 --
2254 -- We are in Maintenance Form.
2255 -- We will not do the real insert, we will just lock the
2256 -- hash number, and let Maintenenace Form do the insert.
2257 -- So no need to call AutoTrans function.
2258 --
2259 IF (fnd_flex_server1.g_debug_level > 0) THEN
2260 fnd_flex_server1.add_debug('FOR_INSERT:Maintenance Form');
2261 END IF;
2262 SAVEPOINT pre_insert_comb;
2263
2264
2265 if(insert_combination(kff_id, cc_struct, for_insert, val_date,
2266 kff_info.concatenated_segment_delimiter,
2267 ccid_in, kff_cc, cc_cols, cc_coltypes,
2268 userid, nvals, value_ids, segtypes,
2269 derv, drv_quals, n_xcols, rq_xcols, xcol_vals,
2270 tbl_quals.sq_values, tbl_derv, new_comb,
2271 comb_id) = FALSE) then
2272 ROLLBACK TO SAVEPOINT pre_insert_comb;
2273 goto return_error;
2274 end if;
2275
2276 if(FND_FLEX_SERVER2.x_drop_cached_cv_result(kff_id, nvals, value_vals)
2277 = FALSE) then
2278 ROLLBACK TO SAVEPOINT pre_insert_comb;
2279 goto return_error;
2280 end if;
2281
2282 if(new_comb) then
2283 if((flex_app_sname ='SQLGL') and (flex_code ='GL#')
2284 and (NOT for_insert)) then
2285 if(call_fdfgli(comb_id) = FALSE) then
2286 ROLLBACK TO SAVEPOINT pre_insert_comb;
2287 goto return_error;
2288 end if;
2289 end if;
2290 errcode := FND_FLEX_SERVER1.VV_VALID;
2291 else
2292 ROLLBACK TO SAVEPOINT pre_insert_comb;
2293 if(for_insert and ((ccid_in is null) or (ccid_in = 0) or
2294 (ccid_in = -1) or (comb_id <> ccid_in))) then
2295 FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
2296 errcode := FND_FLEX_SERVER1.VV_COMBEXISTS;
2297 goto return_error;
2298 end if;
2299 errcode:=check_table_comb(tbl_derv,tbl_quals,v_rules,
2300 val_date,TRUE);
2301 end if;
2302 ELSIF ((invoking_mode IN ('V','P')) and (defer_insrt = FALSE) AND
2303 (NOT for_insert)) THEN
2304 --
2305 -- We are called from a foreign key form.
2306 -- In this case use the AutoTrans.
2307 --
2308 IF (fnd_flex_server1.g_debug_level > 0) THEN
2309 fnd_flex_server1.add_debug('Autonomous Transaction');
2310 END IF;
2311 if(insert_combination_at
2312 (kff_id, cc_struct, for_insert, val_date,
2313 kff_info.concatenated_segment_delimiter,
2314 ccid_in, kff_cc, cc_cols, cc_coltypes,
2315 userid, nvals, value_ids, value_vals, segtypes,
2316 derv, drv_quals, n_xcols, rq_xcols, xcol_vals,
2317 tbl_quals.sq_values, tbl_derv, new_comb,
2318 comb_id) = FALSE) then
2319 goto return_error;
2320 end if;
2321 IF (new_comb) THEN
2322 errcode := FND_FLEX_SERVER1.VV_VALID;
2323 ELSE
2324 if(for_insert and ((ccid_in is null) or (ccid_in = 0) or
2325 (ccid_in = -1) or (comb_id <> ccid_in))) then
2326 FND_MESSAGE.set_name('FND', 'FLEX-COMB. ALREADY EXISTS');
2327 errcode := FND_FLEX_SERVER1.VV_COMBEXISTS;
2328 goto return_error;
2329 end if;
2330 errcode:=check_table_comb(tbl_derv,tbl_quals,v_rules,
2331 val_date,TRUE);
2332 END IF;
2333 elsif(invoking_mode = 'P') then
2334 if(for_insert and (ccid_in is not null) and (ccid_in <> 0)) then
2335 comb_id := ccid_in;
2336 else
2337 comb_id := -1;
2338 end if;
2339 errcode := FND_FLEX_SERVER1.VV_VALID;
2340 elsif((invoking_mode = 'V') and (defer_insrt = TRUE)) then
2341 comb_id := -1;
2342 errcode := FND_FLEX_SERVER1.VV_VALID;
2343 else
2344 errcode := FND_FLEX_SERVER1.VV_ERROR;
2345 end if;
2346
2347 <<return_outvars>>
2348 displayed_vals := value_dvals;
2349 stored_vals := value_vals;
2350 segment_ids := value_ids;
2351 descriptions := value_descs;
2352 desc_lengths := desc_lens;
2353 seg_colnames := cc_cols;
2354 seg_coltypes := cc_coltypes;
2355 nvalidated := nvals;
2356 segment_types := segtypes;
2357 displayed_segs := disp_segs;
2358 derived_eff := derv;
2359 table_eff := tbl_derv;
2360 table_quals := tbl_quals;
2361 derived_quals := drv_quals;
2362 column_vals := xcol_vals;
2363 n_column_vals := n_xcols;
2364 seg_delimiter := kff_info.concatenated_segment_delimiter;
2365 ccid_out := comb_id;
2366 new_combination := new_comb;
2367 seg_codes := segcodes;
2368 err_segnum := errsegnum;
2369 v_status := errcode;
2370 GOTO goto_return;
2371
2372 <<return_error>>
2373 v_status := FND_FLEX_SERVER1.VV_ERROR;
2374 GOTO goto_return;
2375
2376 <<goto_return>>
2377 IF (fnd_flex_server1.g_debug_level > 0) THEN
2378 fnd_flex_server1.add_debug('END SSV.validation_engine()');
2379 END IF;
2380 RETURN;
2381
2382 EXCEPTION
2383 WHEN OTHERS then
2384 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2385 FND_MESSAGE.set_token('MSG','SSV.validation_engine() exception: '||SQLERRM);
2386 v_status := FND_FLEX_SERVER1.VV_ERROR;
2387 IF (fnd_flex_server1.g_debug_level > 0) THEN
2388 fnd_flex_server1.add_debug('EXCEPTION others SSV.validation_engine()');
2389 END IF;
2390 return;
2391 END validation_engine;
2392
2393
2394 FUNCTION x_bind_additional_where_clause(p_additional_where_clause IN VARCHAR2)
2395 RETURN BOOLEAN
2396 IS
2397 l_awc VARCHAR2(32000);
2398 l_awc_len NUMBER;
2399 l_pos1 NUMBER;
2400 l_pos2 NUMBER;
2401 l_posq NUMBER;
2402 l_bind_value VARCHAR2(32000);
2403 BEGIN
2404 l_awc := p_additional_where_clause;
2405 l_awc_len := Length(l_awc);
2406 IF (l_awc IS NULL) THEN
2407 RETURN(TRUE);
2408 END IF;
2409
2410 fnd_dsql.add_text(' and (');
2411
2412 l_pos2 := 0;
2413 l_pos1 := Instr(l_awc, '''', l_pos2 + 1, 1);
2414 WHILE (l_pos1 > 0) LOOP
2415 --
2416 -- Copy upto single quote.
2417 --
2418 fnd_dsql.add_text(Substr(l_awc, l_pos2 + 1, (l_pos1 - l_pos2) - 1));
2419
2420 --
2421 -- Find the closing quote. Handle single quote escaping.
2422 --
2423 l_posq := Instr(l_awc, '''''', l_pos1 + 1, 1);
2424 l_pos2 := Instr(l_awc, '''', l_pos1 + 1, 1);
2425
2426 WHILE (l_pos2 = l_posq) LOOP
2427 l_pos2 := Instr(l_awc, '''', l_posq + 2, 1);
2428 l_posq := Instr(l_awc, '''''', l_posq + 2, 1);
2429 END LOOP;
2430
2431 IF (l_pos2 = 0) THEN
2432 fnd_message.set_name('FND', 'FLEX-SQL MISSING QUOTE');
2433 fnd_message.set_token('CLAUSE', Substr(l_awc, 1, 1000));
2434 RETURN(FALSE);
2435 END IF;
2436
2437 fnd_dsql.add_bind(REPLACE(Substr(l_awc, l_pos1 + 1, (l_pos2-l_pos1)- 1),
2438 '''''', ''''));
2439
2440 l_pos1 := Instr(l_awc, '''', l_pos2 + 1, 1);
2441 END LOOP;
2442
2443 fnd_dsql.add_text(Substr(l_awc, l_pos2 + 1, l_awc_len - l_pos2));
2444 fnd_dsql.add_text(')');
2445
2446 RETURN(TRUE);
2447 EXCEPTION
2448 WHEN OTHERS THEN
2449 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2450 FND_MESSAGE.set_token('MSG','x_bind_additional_where_clause() exception: '||SQLERRM);
2451 RETURN(FALSE);
2452 END x_bind_additional_where_clause;
2453
2454
2455 /* ----------------------------------------------------------------------- */
2456 /* Finds ccid, segment ids, qualifier values, enabled and expiration */
2457 /* info and extra column values for a segment combination. */
2458 /* If the CCID input is not null, looks for the combination by CCID */
2459 /* otherwise looks for combination whose segment id values match */
2460 /* those input in the SEGS parameter. */
2461 /* If segment passed in is null, then find_combination() looks for */
2462 /* a combination with NULL in that column. */
2463 /* Returns number of combinations found or < 0 if error. */
2464 /* If combination not found, all output variables are null. */
2465 /* ----------------------------------------------------------------------- */
2466 FUNCTION find_combination(structnum IN NUMBER,
2467 combtbl IN FND_FLEX_SERVER1.CombTblInfo,
2468 nsegs IN NUMBER,
2469 combcols IN FND_FLEX_SERVER1.TabColArray,
2470 combtypes IN FND_FLEX_SERVER1.CharArray,
2471 segfmts IN FND_FLEX_SERVER1.SegFormats,
2472 nquals IN NUMBER,
2473 qualcols IN FND_FLEX_SERVER1.TabColArray,
2474 nxcols IN NUMBER,
2475 xcolnames IN FND_FLEX_SERVER1.StringArray,
2476 where_cl IN VARCHAR2,
2477 ccid IN OUT nocopy NUMBER,
2478 segids IN OUT nocopy FND_FLEX_SERVER1.ValueIdArray,
2479 tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
2480 qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
2481 xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray)
2482 RETURN NUMBER IS
2483 offset BINARY_INTEGER;
2484 nrecords NUMBER;
2485 colvals FND_FLEX_SERVER1.StringArray;
2486 l_vc2 VARCHAR2(100);
2487
2488 BEGIN
2489
2490 -- Assumes all segment columns in combinations table are CHAR or VARCHAR2
2491 --
2492
2493 -- Build SQL statement to select ccid, enabled information, segment columns,
2494 -- qualifiers, and extra cols in that order.
2495 --
2496 fnd_dsql.init;
2497
2498 sqls := 'select to_char(' || combtbl.unique_id_column_name || '), ';
2499 sqls := sqls || 'nvl(ENABLED_FLAG, ''Y''), nvl(SUMMARY_FLAG, ''N''), ';
2500 sqls := sqls || 'to_char(START_DATE_ACTIVE, ''' ||
2501 FND_FLEX_SERVER1.DATETIME_FMT || '''), ';
2502 sqls := sqls || 'to_char(END_DATE_ACTIVE, ''' ||
2503 FND_FLEX_SERVER1.DATETIME_FMT || ''')';
2504 for i in 1..nsegs loop
2505 sqls := sqls || ', ' || FND_FLEX_SERVER1.select_clause(combcols(i),
2506 combtypes(i), FND_FLEX_SERVER1.VC_ID,
2507 segfmts.vs_format(i), segfmts.vs_maxsize(i));
2508 end loop;
2509 for i in 1..nquals loop
2510 sqls := sqls || ', ' || qualcols(i);
2511 end loop;
2512 for i in 1..nxcols loop
2513 sqls := sqls || ', ' || xcolnames(i);
2514 end loop;
2515
2516 -- If no structure column, Client only finds combinations for struct 101.
2517 sqls := sqls || ' from ' || combtbl.select_comb_from || ' where ';
2518 if(combtbl.set_defining_column_name is not null) then
2519 sqls := sqls || combtbl.set_defining_column_name;
2520 else
2521 sqls := sqls || '101';
2522 end if;
2523
2524 sqls := sqls || ' = ';
2525
2526 fnd_dsql.add_text(sqls);
2527 fnd_dsql.add_bind(structnum);
2528
2529 -- If CCID input select by CCID, otherwise select by segment ids.
2530 --
2531 if(ccid is not null) THEN
2532 fnd_dsql.add_text(' and ' || combtbl.unique_id_column_name || ' = ');
2533 fnd_dsql.add_bind(ccid);
2534 ELSE
2535 for i in 1..nsegs LOOP
2536 fnd_dsql.add_text(' and (' || combcols(i));
2537 if(segids(i) is null) THEN
2538 fnd_dsql.add_text(' is null)');
2539 else
2540 fnd_dsql.add_text(' = ');
2541 --
2542 -- This will call fnd_dsql.add_bind
2543 --
2544 fnd_flex_server1.x_compare_clause
2545 (combtypes(i),
2546 combcols(i), segids(i), FND_FLEX_SERVER1.VC_ID,
2547 segfmts.vs_format(i), segfmts.vs_maxsize(i));
2548
2549 fnd_dsql.add_text(')');
2550 end if;
2551 end loop;
2552 if(where_cl is not null) THEN
2553 --
2554 -- Parse the literals out and bind them.
2555 --
2556 IF (NOT x_bind_additional_where_clause(where_cl)) THEN
2557 RETURN(-5);
2558 END IF;
2559 end if;
2560 end if;
2561
2562 -- Do the lookup
2563 --
2564
2565 --
2566 -- This will use the sql string stored in fnd_dsql package.
2567 --
2568 nrecords := fnd_flex_server1.x_dsql_select(nsegs + nquals + nxcols + 5,
2569 colvals);
2570
2571 -- Return output information.
2572 --
2573 if(nrecords > 0) then
2574
2575 -- Copy ccid, enabled flag and dates and summary flag values to output.
2576 --
2577 ccid := to_number(colvals(1));
2578 tblderv.enabled_flag := colvals(2);
2579 tblderv.summary_flag := colvals(3);
2580 tblderv.start_valid := to_date(colvals(4),FND_FLEX_SERVER1.DATETIME_FMT);
2581 tblderv.end_valid := to_date(colvals(5), FND_FLEX_SERVER1.DATETIME_FMT);
2582
2583 -- Copy segment column values to output
2584 --
2585 offset := 5;
2586 for i in 1..nsegs loop
2587 segids(i) := colvals(i + offset);
2588 end loop;
2589
2590 -- Copy table qualifier values to output
2591 --
2592 offset := nsegs + 5;
2593 for i in 1..nquals loop
2594 qualvals(i) := colvals(i + offset);
2595 end loop;
2596
2597 -- Copy extra column values to output
2598 --
2599 offset := nsegs + nquals + 5;
2600 for i in 1..nxcols loop
2601 xcolvals(i) := colvals(i + offset);
2602 end loop;
2603
2604 else
2605
2606 -- Null out returned extra column and qualifier value arrays to avoid
2607 -- no data found error when accessing them.
2608 --
2609 for i in 1..nquals loop
2610 qualvals(i) := NULL;
2611 end loop;
2612 for i in 1..nxcols loop
2613 xcolvals(i) := NULL;
2614 end loop;
2615
2616 end if;
2617
2618 if(nrecords > 1) then
2619 FND_MESSAGE.set_name('FND', 'FLEX-DUPLICATE CCID');
2620 FND_MESSAGE.set_token('CCID', to_char(ccid));
2621 end if;
2622
2623 return(nrecords);
2624
2625 EXCEPTION
2626 WHEN OTHERS then
2627 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2628 FND_MESSAGE.set_token('MSG','SSV.find_combination() exception: '||SQLERRM);
2629 return(-3);
2630
2631 END find_combination;
2632
2633 /* ----------------------------------------------------------------------- */
2634 /* Inserts combination of segment ids into code combinations table. */
2635 /* */
2636 /* If combination has been created since last checked, get it from */
2637 /* the combinations table along with all the other table values for */
2638 /* the qualifiers, extra columns, and enabled and effective dates. */
2639 /* Determine names of qualifier columns from the derived qualifiers */
2640 /* input to this function. Returns table qualifier values = derived */
2641 /* qualifier values, table effecitivity information = derived */
2642 /* effectivity information, and null for extra columns for new combs. */
2643 /* returns newcomb = TRUE if just created this combination. */
2644 /* If segment column is of type number, then does the default */
2645 /* conversion on the character representation of the segment id. */
2646 /* If segment column is of type date, does default to_date() */
2647 /* conversion for non-translatable date, time and date-time value */
2648 /* sets, but does correctly-formatted conversions for translatable */
2649 /* dates, times and date times. This should emulate client behavior. */
2650 /* */
2651 /* If maintmode = TRUE, then user has called this in FOR_INSERT mode. */
2652 /* In that case do not insert combination if it does not already */
2653 /* exist, just return the ccid. If editing an existing combination */
2654 /* the ccid_inp will be the ccid of the combination being edited. */
2655 /* In that case, do not create a new ccid, but just return ccid_inp. */
2656 /* If the ccid_inp is not null, 0 or -1 consider it to be ok to use. */
2657 /* */
2658 /* Calls user PLSQL validation function after locking the */
2659 /* combination, getting a new CCID, and double-checking to make */
2660 /* sure nobody else has created the combination since we last checked.*/
2661 /* This is done at this time to maintain exact backward compatibility */
2662 /* with the client c-code. It would make more sense to do the user */
2663 /* validation before calling insert_combination(), but then more than */
2664 /* one user might call the user validation function with the same */
2665 /* combination, and somebody might be relying on this corner-case */
2666 /* functionality. */
2667 /* If the user PLSQL validation function returns FALSE, */
2668 /* insert_combination() returns FALSE indicating a fatal error */
2669 /* condition. In this case the error message is already loaded */
2670 /* in FND_MESSAGE. */
2671 /* A SAVEPOINT must be issued externally to this function and */
2672 /* a rollback must occur if insert_combination returns an error */
2673 /* of if it returns new_comb = FALSE to unlock the hash number. */
2674 /* */
2675 /* Returns TRUE on success or FALSE and sets message on error. */
2676 /* ----------------------------------------------------------------------- */
2677 FUNCTION insert_combination(fstruct IN FND_FLEX_SERVER1.FlexStructId,
2678 structnum IN NUMBER,
2679 maintmode IN BOOLEAN,
2680 v_date IN DATE,
2681 seg_delim IN VARCHAR2,
2682 ccid_inp IN NUMBER,
2683 combtbl IN FND_FLEX_SERVER1.CombTblInfo,
2684 combcols IN FND_FLEX_SERVER1.TabColArray,
2685 combtypes IN FND_FLEX_SERVER1.CharArray,
2686 user_id IN NUMBER,
2687 nsegs IN NUMBER,
2688 segids_in IN FND_FLEX_SERVER1.ValueIdArray,
2689 segfmts IN FND_FLEX_SERVER1.SegFormats,
2690 dvalues IN FND_FLEX_SERVER1.DerivedVals,
2691 dquals IN FND_FLEX_SERVER1.Qualifiers,
2692 nxcols IN NUMBER,
2693 xcolnames IN FND_FLEX_SERVER1.StringArray,
2694 xcolvals OUT nocopy FND_FLEX_SERVER1.StringArray,
2695 qualvals OUT nocopy FND_FLEX_SERVER1.ValAttribArray,
2696 tblderv OUT nocopy FND_FLEX_SERVER1.DerivedVals,
2697 newcomb OUT nocopy BOOLEAN,
2698 ccid_out OUT nocopy NUMBER) RETURN BOOLEAN IS
2699
2700 deadlock EXCEPTION;
2701 ccid NUMBER;
2702 nfound NUMBER;
2703 hash_num NUMBER;
2704 hash_number NUMBER;
2705 ccid_string VARCHAR2(50);
2706 segids FND_FLEX_SERVER1.ValueIdArray;
2707
2708 PRAGMA EXCEPTION_INIT(deadlock, -60);
2709
2710 BEGIN
2711
2712 -- For debugging...
2713 --
2714
2715 IF (fnd_flex_server1.g_debug_level > 0) THEN
2716 FND_FLEX_SERVER1.add_debug(to_char(nsegs) ||
2717 ' segs passed to insert_combination().');
2718 END IF;
2719
2720 -- First lock this combination and prevent other users from inserting.
2721 -- Generate an almost unique hash number from the segments ids.
2722 -- and lock that row in the hash table to prevent other users from
2723 -- inserting the same combination. The commit will drop the locks.-
2724 -- GL requires row share lock on combinations table to prevent them from
2725 -- getting an exclusive lock for their processing.
2726
2727 segids := segids_in;
2728
2729 sqls := 'lock table ' || combtbl.application_table_name;
2730 sqls := sqls || ' in row share mode';
2731
2732 fnd_dsql.init;
2733 fnd_dsql.add_text(sqls);
2734 if(FND_FLEX_SERVER1.x_dsql_execute < 0) then
2735 return(FALSE);
2736 end if;
2737
2738 -- Next compute the hash number that is to be locked.
2739 --
2740 hash_number := hash_segs(nsegs, segids);
2741 if(hash_number < 0) then
2742 return(FALSE);
2743 end if;
2744
2745 IF (fnd_flex_server1.g_debug_level > 0) THEN
2746 FND_FLEX_SERVER1.add_debug('Hash value = '||to_char(hash_number)||'.');
2747 END IF;
2748 SELECT hash_value INTO hash_num FROM fnd_flex_hash
2749 WHERE hash_value = hash_number FOR UPDATE;
2750
2751 -- Double-check to see if it has been created. No where clause this time.
2752 --
2753 nfound := find_combination(structnum, combtbl, nsegs, combcols,
2754 combtypes, segfmts, dquals.nquals,
2755 dquals.derived_cols, nxcols, xcolnames, NULL,
2756 ccid, segids, tblderv, qualvals, xcolvals);
2757 if(nfound <> 0) then
2758 if(nfound >= 1) then
2759 newcomb := FALSE;
2760 ccid_out := ccid;
2761 return(TRUE);
2762 end if;
2763 return(FALSE);
2764 end if;
2765
2766 -- Get unique code combination ID from a sequence if we dont already have it.
2767 -- If ccid_inp is 0 or null or -1 we need to generate a new ccid.
2768 -- Must use dynamic SQL here since ccid comes from the application table
2769 -- with a '_S' suffix. Could do without dynamic sql if we had a fixed
2770 -- sequence name.
2771 --
2772 if(maintmode and (ccid_inp is not null) and (ccid_inp <> 0) and
2773 (ccid_inp <> -1)) then
2774 ccid := ccid_inp;
2775 else
2776 sqls := 'select to_char(' || combtbl.application_table_name;
2777 sqls := sqls || '_S.NEXTVAL) from dual';
2778 fnd_dsql.init;
2779 fnd_dsql.add_text(sqls);
2780 if(FND_FLEX_SERVER1.x_dsql_select_one(ccid_string) <> 1) then
2781 return(FALSE);
2782 end if;
2783 ccid := to_number(ccid_string);
2784 if(ccid > MAX_CCID) then
2785 FND_MESSAGE.set_name('FND', 'FLEX-CCID TOO BIG');
2786 FND_MESSAGE.set_token('CCIDLIMIT', to_char(MAX_CCID));
2787 FND_MESSAGE.set_token('SEQNAME', combtbl.application_table_name||'_S');
2788 return(FALSE);
2789 end if;
2790 end if;
2791
2792 -- Call user validation function now if desired. Bail if error.
2793 --
2794 if(userval_on) then
2795 if(NOT call_userval(fstruct, v_date, nsegs, seg_delim, segids_in)) then
2796 return(FALSE);
2797 end if;
2798 end if;
2799
2800 -- If not in maintainence mode do the insert, otherwise skip to the end.
2801 --
2802 if(NOT maintmode) then
2803
2804 -- Build a SQL statement to do the insert.
2805 --
2806 fnd_dsql.init;
2807 sqls := 'insert into ' || combtbl.application_table_name || ' (';
2808 sqls := sqls || combtbl.unique_id_column_name;
2809 if(combtbl.set_defining_column_name is not null) then
2810 sqls := sqls || ', ' || combtbl.set_defining_column_name;
2811 end if;
2812 sqls := sqls || ', ENABLED_FLAG, SUMMARY_FLAG, ';
2813 sqls := sqls || 'START_DATE_ACTIVE, END_DATE_ACTIVE, ';
2814 sqls := sqls || 'LAST_UPDATE_DATE, LAST_UPDATED_BY';
2815 for i in 1..dquals.nquals loop
2816 sqls := sqls || ', ' || dquals.derived_cols(i);
2817 end loop;
2818 for i in 1..nsegs loop
2819 if(segids(i) is not null) then
2820 sqls := sqls || ', ' || combcols(i);
2821 end if;
2822 end loop;
2823 sqls := sqls || ') values (';
2824
2825 -- So far the table name and the column names.
2826 fnd_dsql.add_text(sqls);
2827
2828 fnd_dsql.add_bind(ccid);
2829
2830 if(combtbl.set_defining_column_name is not null) THEN
2831 fnd_dsql.add_text(',');
2832 fnd_dsql.add_bind(structnum);
2833 end if;
2834
2835 fnd_dsql.add_text(',');
2836 fnd_dsql.add_bind(dvalues.enabled_flag);
2837
2838 fnd_dsql.add_text(',');
2839 fnd_dsql.add_bind(dvalues.summary_flag);
2840
2841 fnd_dsql.add_text(',');
2842 fnd_dsql.add_bind(dvalues.start_valid);
2843
2844 fnd_dsql.add_text(',');
2845 fnd_dsql.add_bind(dvalues.end_valid);
2846
2847 fnd_dsql.add_text(',sysdate,');
2848 fnd_dsql.add_bind(user_id);
2849
2850 for i in 1..dquals.nquals LOOP
2851 fnd_dsql.add_text(',');
2852 fnd_dsql.add_bind(dquals.sq_values(i));
2853 end loop;
2854
2855 for i in 1..nsegs loop
2856 if(segids(i) is not null) THEN
2857 fnd_dsql.add_text(',');
2858 --
2859 -- This will call fnd_dsql.add_bind
2860 --
2861 fnd_flex_server1.x_compare_clause
2862 (combtypes(i),
2863 combcols(i), segids(i), FND_FLEX_SERVER1.VC_ID,
2864 segfmts.vs_format(i), segfmts.vs_maxsize(i));
2865 end if;
2866 end loop;
2867 fnd_dsql.add_text(')');
2868
2869 --
2870 -- Finally do the insert
2871 --
2872 if(FND_FLEX_SERVER1.x_dsql_execute < 0) then
2873 return(FALSE);
2874 end if;
2875 end if;
2876
2877 -- Return all out variables. If comb was found in table these were set
2878 -- above.
2879 --
2880 IF (fnd_flex_server1.g_debug_level > 0) THEN
2881 FND_FLEX_SERVER1.add_debug(' Returning ccid = '||to_char(ccid) || '. ');
2882 END IF;
2883 ccid_out := ccid;
2884 newcomb := TRUE;
2885 tblderv := dvalues;
2886 for i in 1..nxcols loop
2887 xcolvals(i) := NULL;
2888 end loop;
2889 for i in 1..dquals.nquals loop
2890 qualvals(i) := dquals.sq_values(i);
2891 end loop;
2892 return(TRUE);
2893
2894 EXCEPTION
2895 WHEN NO_DATA_FOUND then
2896 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2897 FND_MESSAGE.set_token('MSG', 'Hash value ' ||
2898 to_char(hash_number) || ' not found.');
2899 return(FALSE);
2900 WHEN TOO_MANY_ROWS then
2901 FND_MESSAGE.set_token('MSG', 'Hash value ' ||
2902 to_char(hash_number) || ' not unique.');
2903 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2904 return(FALSE);
2905 WHEN TIMEOUT_ON_RESOURCE then
2906 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2907 FND_MESSAGE.set_token('MSG', 'Timeout waiting for lock on hash table.');
2908 return(FALSE);
2909 WHEN deadlock then
2910 FND_MESSAGE.set_name('FND', 'FLEX-HASH DEADLOCK');
2911 return(FALSE);
2912 WHEN OTHERS then
2913 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2914 FND_MESSAGE.set_token('MSG','insert_combination() exception: '||SQLERRM);
2915 return(FALSE);
2916
2917 END insert_combination;
2918
2919
2920 /* ----------------------------------------------------------------------- */
2921 /* Sums ASCII values of all characters mod NHASH. ASCII returns code */
2922 /* up to 64K for multi-byte characters. Multiply code for each char */
2923 /* by the characters position to make non-commutative */
2924 /* ----------------------------------------------------------------------- */
2925
2926 FUNCTION hash_segs(n IN NUMBER, segs IN FND_FLEX_SERVER1.ValueIdArray)
2927 RETURN NUMBER IS
2928
2929 hval NUMBER;
2930 cval NUMBER;
2931 seglen NUMBER;
2932 chr_count NUMBER;
2933
2934 BEGIN
2935
2936 hval := 0;
2937 chr_count := 1;
2938 for segnum in 1..n loop
2939 if(segs(segnum) is not null) then
2940 seglen := LENGTH(segs(segnum));
2941 for i in 1..seglen loop
2942 cval := ASCII(SUBSTR(segs(segnum), i, 1));
2943 hval := hval + cval*chr_count;
2944 chr_count := chr_count + 1;
2945 end loop;
2946 end if;
2947 end loop;
2948 return(MOD(hval, NHASH));
2949
2950 EXCEPTION
2951 WHEN OTHERS then
2952 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
2953 FND_MESSAGE.set_token('MSG', 'hash_segs() exception: ' || SQLERRM);
2954 return(-1);
2955
2956 END hash_segs;
2957
2958 /* ----------------------------------------------------------------------- */
2959 /* Checks the expiration date, enabled flag and vrules against the */
2960 /* combination returned from the combinations tables. This must be */
2961 /* done in case user has updated the combinations table to differ */
2962 /* from the derived values. Only checks the expiration and enabled */
2963 /* flags if check_effective flag is TRUE. */
2964 /* Error code indicating result of validation. VV_VALID means all ok */
2965 /* ----------------------------------------------------------------------- */
2966
2967 FUNCTION check_table_comb(t_dval IN FND_FLEX_SERVER1.DerivedVals,
2968 t_quals IN FND_FLEX_SERVER1.Qualifiers,
2969 v_rules IN FND_FLEX_SERVER1.Vrules,
2970 v_date IN DATE,
2971 check_effective IN BOOLEAN) RETURN NUMBER IS
2972 BEGIN
2973
2974 -- Print table segments, qualifiers, extra columns and effectivity info.
2975 --
2976 IF (fnd_flex_server1.g_debug_level > 0) THEN
2977 FND_FLEX_SERVER1.add_debug('Check expiration/vrules on table quals.');
2978
2979 FND_FLEX_SERVER1.add_debug('Qualifiers: ');
2980 for i in 1..t_quals.nquals loop
2981 FND_FLEX_SERVER1.add_debug('(' || t_quals.sq_names(i) || ' = ');
2982 FND_FLEX_SERVER1.add_debug(t_quals.sq_values(i) || ') ');
2983 end loop;
2984
2985 FND_FLEX_SERVER1.add_debug('Enabled: ' || t_dval.enabled_flag);
2986 FND_FLEX_SERVER1.add_debug
2987 (' Starts: ' || to_char(t_dval.start_valid,
2988 FND_FLEX_SERVER1.DATETIME_FMT));
2989 FND_FLEX_SERVER1.add_debug
2990 (' Ends: ' || to_char(t_dval.end_valid,
2991 FND_FLEX_SERVER1.DATETIME_FMT));
2992 FND_FLEX_SERVER1.add_debug(' Summary Flag: ' || t_dval.summary_flag);
2993 END IF;
2994 -- Check if combination turned on
2995 --
2996 if(check_effective) then
2997 if(t_dval.enabled_flag <> 'Y') then
2998 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION DISABLED');
2999 return(FND_FLEX_SERVER1.VV_VALUES);
3000 end if;
3001 if((v_date is not null) and
3002 ((Trunc(v_date) < Trunc(nvl(t_dval.start_valid, v_date))) or
3003 (Trunc(v_date) > Trunc(nvl(t_dval.end_valid, v_date))))) then
3004 FND_MESSAGE.set_name('FND', 'FLEX-COMBINATION HAS EXPIRED');
3005 return(FND_FLEX_SERVER1.VV_VALUES);
3006 end if;
3007 end if;
3008
3009 -- Check vrules.
3010 --
3011 return(FND_FLEX_SERVER1.check_comb_vrules(v_rules, t_quals,
3012 t_dval.summary_flag));
3013 EXCEPTION
3014 WHEN OTHERS then
3015 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3016 FND_MESSAGE.set_token('MSG','check_table_comb() exception: '||SQLERRM);
3017 return(FND_FLEX_SERVER1.VV_ERROR);
3018
3019 END check_table_comb;
3020
3021 /* ----------------------------------------------------------------------- */
3022 /* Determines the segment number of the given column name. */
3023 /* Returns null without erroring if the name is not on the list. */
3024 /* ----------------------------------------------------------------------- */
3025
3026 FUNCTION find_column_index(column_list IN FND_FLEX_SERVER1.TabColArray,
3027 column_count IN NUMBER,
3028 colname IN VARCHAR2) RETURN NUMBER IS
3029 colnum NUMBER;
3030
3031 BEGIN
3032 -- Set colnum only if name found.
3033 --
3034 for i in 1..column_count loop
3035 if(UPPER(colname) = UPPER(column_list(i))) then
3036 colnum := i;
3037 exit;
3038 end if;
3039 end loop;
3040 return(colnum);
3041
3042 END find_column_index;
3043
3044 /* ----------------------------------------------------------------------- */
3045 /* Concatenate Values into a string for return to the client. */
3046 /* If only one value displayed does not substitute CR for delimiter. */
3047 /* Concatenates only displayed values. */
3048 /* ----------------------------------------------------------------------- */
3049
3050 FUNCTION concatenate_values(nvals IN NUMBER,
3051 vals IN FND_FLEX_SERVER1.ValueArray,
3052 displ FND_FLEX_SERVER1.DisplayedSegs,
3053 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3054 n_displayed NUMBER;
3055 str FND_FLEX_SERVER1.StringArray;
3056 BEGIN
3057 IF (fnd_flex_server1.g_debug_level > 0) THEN
3058 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_values()');
3059 END IF;
3060 n_displayed := 0;
3061 for i in 1..nvals loop
3062 if((i <= displ.n_segflags) and displ.segflags(i)) then
3063 n_displayed := n_displayed + 1;
3064 str(n_displayed) := vals(i);
3065 end if;
3066 end loop;
3067
3068 IF (n_displayed > 1) THEN
3069 return(FND_FLEX_SERVER1.from_stringarray(n_displayed, str, delimiter));
3070 ELSIF (n_displayed = 1) then
3071 return(str(1));
3072 ELSE
3073 RETURN (NULL);
3074 end if;
3075 END concatenate_values;
3076
3077 /* ----------------------------------------------------------------------- */
3078 /* Concatenate Value ids into a string for return to the client. */
3079 /* If only one id input does not substitute CR for delimiter. */
3080 /* Concatenates all ids whether or not their segments are displayed. */
3081 /* ----------------------------------------------------------------------- */
3082
3083 FUNCTION concatenate_ids(nids IN NUMBER,
3084 ids IN FND_FLEX_SERVER1.ValueIdArray,
3085 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3086 str FND_FLEX_SERVER1.StringArray;
3087 BEGIN
3088 IF (fnd_flex_server1.g_debug_level > 0) THEN
3089 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_ids()');
3090 END IF;
3091 if(nids = 1) then
3092 return(ids(1));
3093 else
3094 for i in 1..nids loop
3095 str(i) := ids(i);
3096 end loop;
3097 IF (nids > 1) THEN
3098 return(FND_FLEX_SERVER1.from_stringarray(nids, str, delimiter));
3099 ELSIF (nids = 1) THEN
3100 RETURN(str(1));
3101 ELSE
3102 RETURN NULL;
3103 END IF;
3104 end if;
3105 END concatenate_ids;
3106
3107 /* ----------------------------------------------------------------------- */
3108 /* Concatenate Value descriptions to string for return to the client. */
3109 /* Only returns descriptions for displayed segments. */
3110 /* Truncates descriptions to lengths specified by flex structure. */
3111 /* NOTE: Lengths are all in BYTES not characters. */
3112 /* If only one value displayed does not substitute CR for delimiter. */
3113 /* ----------------------------------------------------------------------- */
3114
3115 FUNCTION concatenate_descriptions(ndescs IN NUMBER,
3116 descs IN FND_FLEX_SERVER1.ValueDescArray,
3117 displ IN FND_FLEX_SERVER1.DisplayedSegs,
3118 lengths IN FND_FLEX_SERVER1.NumberArray,
3119 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3120 n_displayed NUMBER;
3121 str FND_FLEX_SERVER1.StringArray;
3122 BEGIN
3123 IF (fnd_flex_server1.g_debug_level > 0) THEN
3124 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_descriptions()');
3125 END IF;
3126 n_displayed := 0;
3127 for i in 1..ndescs loop
3128 if((i <= displ.n_segflags) and displ.segflags(i)) then
3129 n_displayed := n_displayed + 1;
3130 str(n_displayed) := SUBSTRB(descs(i), 1, lengths(i));
3131 end if;
3132 end loop;
3133
3134 IF (n_displayed > 1) THEN
3135 return(FND_FLEX_SERVER1.from_stringarray(n_displayed, str, delimiter));
3136 ELSIF (n_displayed = 1) then
3137 return(str(1));
3138 ELSE
3139 RETURN (NULL);
3140 end if;
3141 END concatenate_descriptions;
3142
3143 /* ----------------------------------------------------------------------- */
3144 /* Concatenate Value descriptions to string for return to the client. */
3145 /* Only returns descriptions for displayed segments. */
3146 /* Does not truncate descriptions. */
3147 /* If only one value displayed does not substitute CR for delimiter. */
3148 /* ----------------------------------------------------------------------- */
3149
3150 FUNCTION concatenate_fulldescs(ndescs IN NUMBER,
3151 descs IN FND_FLEX_SERVER1.ValueDescArray,
3152 displ IN FND_FLEX_SERVER1.DisplayedSegs,
3153 delimiter IN VARCHAR2) RETURN VARCHAR2 IS
3154 n_displayed NUMBER;
3155 str FND_FLEX_SERVER1.StringArray;
3156 BEGIN
3157 IF (fnd_flex_server1.g_debug_level > 0) THEN
3158 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_fulldesc()');
3159 END IF;
3160 n_displayed := 0;
3161 for i in 1..ndescs loop
3162 if((i <= displ.n_segflags) and displ.segflags(i)) then
3163 n_displayed := n_displayed + 1;
3164 str(n_displayed) := descs(i);
3165 end if;
3166 end loop;
3167
3168 IF (n_displayed > 1) THEN
3169 return(FND_FLEX_SERVER1.from_stringarray(n_displayed, str, delimiter));
3170 ELSIF (n_displayed = 1) then
3171 return(str(1));
3172 ELSE
3173 RETURN (NULL);
3174 end if;
3175 END concatenate_fulldescs;
3176
3177 /* ----------------------------------------------------------------------- */
3178 /* Concatenate segment formats to string for return to the client. */
3179 /* ----------------------------------------------------------------------- */
3180
3181 FUNCTION concatenate_segment_formats(segfmts IN FND_FLEX_SERVER1.SegFormats)
3182 RETURN VARCHAR2 IS
3183 catfmts VARCHAR2(200);
3184
3185 BEGIN
3186 for i in 1..segfmts.nsegs loop
3187 catfmts := catfmts || segfmts.vs_format(i);
3188 catfmts := catfmts || to_char(segfmts.vs_maxsize(i), 'S099') || ' ';
3189 end loop;
3190 return(catfmts);
3191 END concatenate_segment_formats;
3192
3193 /* ----------------------------------------------------------------------- */
3194 /* Returns derived values requested as a concatenated string. */
3195 /* Can request SUMMARY_FLAG, START_DATE_ACTIVE and END_DATE_ACTIVE, */
3196 /* but not ENABLED_FLAG. */
3197 /* */
3198 /* Any qualifiers not found in the list of derived qualifiers */
3199 /* will result in a NULL being returned in the appropriate place. */
3200 /* Return string has qualifier values separated by TERMINATOR. */
3201 /* ----------------------------------------------------------------------- */
3202
3203 FUNCTION ret_derived(d_quals IN FND_FLEX_SERVER1.Qualifiers,
3204 drv IN FND_FLEX_SERVER1.DerivedVals,
3205 d_rqst IN DerivedRqst) RETURN VARCHAR2 IS
3206
3207 str VARCHAR2(2000);
3208
3209 BEGIN
3210
3211 str := NULL;
3212 for i in 1..d_rqst.nrqstd loop
3213 if(d_rqst.sq_names(i) = 'SUMMARY_FLAG') then
3214 str := str || drv.summary_flag;
3215 elsif(d_rqst.sq_names(i) = 'START_DATE_ACTIVE') then
3216 str := str || to_char(drv.start_valid, DRV_DATE_FMT);
3217 elsif(d_rqst.sq_names(i) = 'END_DATE_ACTIVE') then
3218 str := str || to_char(drv.end_valid, DRV_DATE_FMT);
3219 else
3220 for j in 1..d_quals.nquals loop
3221 if(d_quals.sq_names(j) = d_rqst.sq_names(i)) then
3222 str := str || d_quals.sq_values(j);
3223 exit;
3224 end if;
3225 end loop;
3226 end if;
3227 str := str || FND_FLEX_SERVER1.TERMINATOR;
3228 end loop;
3229 if(str is not null) then
3230 str := SUBSTR(str, 1, LENGTH(str) - LENGTH(FND_FLEX_SERVER1.TERMINATOR));
3231 end if;
3232 return(str);
3233
3234 END ret_derived;
3235
3236 /* ----------------------------------------------------------------------- */
3237 /* Returns value attributes requested as a concatenated string. */
3238 /* Implemented the same as ret_derived() except checks flexfield */
3239 /* qualifier names too. */
3240 /* Can request SUMMARY_FLAG, START_DATE_ACTIVE and END_DATE_ACTIVE, */
3241 /* but not ENABLED_FLAG. */
3242 /* */
3243 /* Any qualifiers not found in the list of derived qualifiers */
3244 /* will result in a NULL being returned in the appropriate place. */
3245 /* Return string has qualifier values separated by TERMINATOR. */
3246 /* ----------------------------------------------------------------------- */
3247 FUNCTION ret_valatts(d_quals IN FND_FLEX_SERVER1.Qualifiers,
3248 drv IN FND_FLEX_SERVER1.DerivedVals,
3249 v_rqst IN ValattRqst) RETURN VARCHAR2 IS
3250
3251 str VARCHAR2(2000);
3252
3253 BEGIN
3254
3255 str := NULL;
3256 for i in 1..v_rqst.nrqstd loop
3257 if(v_rqst.fq_names(i) is null) then
3258 if(v_rqst.sq_names(i) = 'SUMMARY_FLAG') then
3259 str := str || drv.summary_flag;
3260 elsif(v_rqst.sq_names(i) = 'START_DATE_ACTIVE') then
3261 str := str || to_char(drv.start_valid, DRV_DATE_FMT);
3262 elsif(v_rqst.sq_names(i) = 'END_DATE_ACTIVE') then
3263 str := str || to_char(drv.end_valid, DRV_DATE_FMT);
3264 end if;
3265 else
3266 for j in 1..d_quals.nquals loop
3267 if((d_quals.sq_names(j) = v_rqst.sq_names(i)) and
3268 (d_quals.fq_names(j) = v_rqst.fq_names(i))) then
3269 str := str || d_quals.sq_values(j);
3270 exit;
3271 end if;
3272 end loop;
3273 end if;
3274 str := str || FND_FLEX_SERVER1.TERMINATOR;
3275 end loop;
3276 if(str is not null) then
3277 str := SUBSTR(str, 1, LENGTH(str) - LENGTH(FND_FLEX_SERVER1.TERMINATOR));
3278 end if;
3279 return(str);
3280
3281 END ret_valatts;
3282
3283 /* ----------------------------------------------------------------------- */
3284 /* Parses string which requests value attribue values. */
3285 /* Input string of the form: */
3286 /* 'flexfield qualifier1\nsegment qualifier1\0flexfield qualifier2...'*/
3287 /* Requested segment qualifier names converted to upper case. */
3288 /* Returns number of non-null value attributes requested or < 0 if err*/
3289 /* ----------------------------------------------------------------------- */
3290 FUNCTION parse_va_rqst(s IN VARCHAR2, var OUT nocopy ValattRqst) RETURN NUMBER IS
3291
3292 nsegs NUMBER;
3293 fqname_end NUMBER;
3294 fqsq_names FND_FLEX_SERVER1.StringArray;
3295 sq_name VARCHAR2(30);
3296
3297 BEGIN
3298
3299 -- Make sure it is not too big
3300 --
3301 if(LENGTHB(s) > MAX_ARG_LEN) then
3302 FND_MESSAGE.set_name('FND', 'FLEX-ARGUMENT TOO LONG');
3303 FND_MESSAGE.set_token('ARG', 'VALATT');
3304 FND_MESSAGE.set_token('MAXLEN', to_char(MAX_ARG_LEN));
3305 return(-6);
3306 end if;
3307
3308 IF (s IS NOT NULL) THEN
3309 nsegs := FND_FLEX_SERVER1.to_stringarray2(s, FND_FLEX_SERVER1.TERMINATOR,
3310 fqsq_names);
3311 ELSE
3312 nsegs := 0;
3313 END IF;
3314
3315 for i in 1..nsegs loop
3316 if(fqsq_names(i) is not null) then
3317 fqname_end := INSTR(fqsq_names(i), FND_FLEX_SERVER1.SEPARATOR);
3318 if(fqname_end <= 0) then
3319 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VALATT NOSEP');
3320 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3321 FND_MESSAGE.set_token('NAME', fqsq_names(i));
3322 return(-1);
3323 elsif(fqname_end > 31) then
3324 FND_MESSAGE.set_name('FND', 'FLEX-QUALIFIER TOO LONG');
3325 FND_MESSAGE.set_token('TOKNAME', 'VALATT');
3326 FND_MESSAGE.set_token('NAME', SUBSTR(fqsq_names(i), 1, 30));
3327 return(-2);
3328 else
3329 var.fq_names(i) := UPPER(SUBSTR(fqsq_names(i), 1, fqname_end - 1));
3330 sq_name := UPPER(SUBSTR(fqsq_names(i),
3331 fqname_end + LENGTH(FND_FLEX_SERVER1.SEPARATOR), 30));
3332 if(sq_name is null) then
3333 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3334 FND_MESSAGE.set_token('TOKNAME', 'VALATT');
3335 return(-3);
3336 end if;
3337 var.sq_names(i) := sq_name;
3338 end if;
3339 else
3340 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3341 FND_MESSAGE.set_token('TOKNAME', 'VALATT');
3342 return(-4);
3343 end if;
3344 end loop;
3345 var.nrqstd := nsegs;
3346 return(nsegs);
3347
3348 EXCEPTION
3349 WHEN OTHERS then
3350 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3351 FND_MESSAGE.set_token('MSG', 'parse_va_rqst() exception: ' || SQLERRM);
3352 return(-5);
3353
3354 END parse_va_rqst;
3355
3356 /* ----------------------------------------------------------------------- */
3357 /* Parses string which requests derived segment qualifier values. */
3358 /* Input string of the form: 'seg qual name1\0seg qual name2...' */
3359 /* Requested segment qualifier names converted to upper case. */
3360 /* Returns number of non-null qualifier names requested or < 0 if err */
3361 /* ----------------------------------------------------------------------- */
3362 FUNCTION parse_drv_rqst(s IN VARCHAR2, dr OUT nocopy DerivedRqst) RETURN NUMBER IS
3363
3364 nsegs NUMBER;
3365 sqnames FND_FLEX_SERVER1.StringArray;
3366
3367 BEGIN
3368
3369 -- Make sure it is not too big
3370 --
3371 if(LENGTHB(s) > MAX_ARG_LEN) then
3372 FND_MESSAGE.set_name('FND', 'FLEX-ARGUMENT TOO LONG');
3373 FND_MESSAGE.set_token('ARG', 'DERIVED');
3374 FND_MESSAGE.set_token('MAXLEN', to_char(MAX_ARG_LEN));
3375 return(-6);
3376 end if;
3377
3378 IF (s IS NOT NULL) THEN
3379 nsegs := FND_FLEX_SERVER1.to_stringarray2(s, FND_FLEX_SERVER1.TERMINATOR,
3380 sqnames);
3381 ELSE
3382 nsegs := 0;
3383 END IF;
3384
3385 for i in 1..nsegs loop
3386 if(sqnames(i) is not null) then
3387 dr.sq_names(i) := UPPER(SUBSTR(sqnames(i), 1, 30));
3388 else
3389 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3390 FND_MESSAGE.set_token('TOKNAME', 'DERIVED');
3391 return(-1);
3392 end if;
3393 end loop;
3394 dr.nrqstd := nsegs;
3395 return(nsegs);
3396
3397 EXCEPTION
3398 WHEN OTHERS then
3399 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3400 FND_MESSAGE.set_token('MSG', 'parse_drv_rqst() exception: '||SQLERRM);
3401 return(-2);
3402
3403 END parse_drv_rqst;
3404
3405 /* ----------------------------------------------------------------------- */
3406 /* Parses vrule string. Returns number of vrules found or sets error */
3407 /* message string and returns < 0 if error. */
3408 /* */
3409 /* Vrule string format: */
3410 /* 'FLEXFIELD QUALIFIER NAME\nSEGMENT QUALIFIER NAME\n */
3411 /* {I[nclude] | E[xclude]}\nAPPL=appl short name;NAME=Message name\n */
3412 /* value1\nvalue2...\0' */
3413 /* */
3414 /* SEPARATOR = '\n', TERMINATOR = '\0'. */
3415 /* */
3416 /* Multiple vrules can be separated by TERMINATOR. TERMINATOR is not */
3417 /* required after last vrule. If 'NAME=' is missing the entire */
3418 /* message string is considered the error message. If 'APPL=' or */
3419 /* the ';' is missing the application short name 'FND' is used. */
3420 /* */
3421 /* Eliminates whitespace from around component names. */
3422 /* Limits vrule string length to < MAX_VRULE_LEN bytes. */
3423 /* ----------------------------------------------------------------------- */
3424 FUNCTION parse_vrules(s IN VARCHAR2,
3425 vr OUT nocopy FND_FLEX_SERVER1.Vrules) RETURN NUMBER IS
3426
3427 n NUMBER;
3428 bgn NUMBER;
3429 endp NUMBER;
3430 seplen NUMBER;
3431 vrulstr_len NUMBER;
3432 msg_begin NUMBER;
3433 msg_end NUMBER;
3434 tokn_len NUMBER;
3435 endtok NUMBER;
3436 ieval VARCHAR2(240);
3437 tokn VARCHAR2(2000);
3438 sq_name VARCHAR2(30);
3439 ie_flag VARCHAR2(4);
3440
3441 BEGIN
3442
3443 n := 0;
3444 bgn := 1;
3445 if(s is null) then
3446 vr.nvrules := 0;
3447 return(0);
3448 end if;
3449
3450 vrulstr_len := LENGTH(s);
3451 seplen := LENGTH(FND_FLEX_SERVER1.SEPARATOR);
3452
3453 while (bgn <= vrulstr_len) loop
3454 n := n + 1;
3455
3456 -- Flexfield qualifier name
3457 --
3458 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3459 if(endp <= 0) then
3460 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3461 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3462 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3463 return(-1);
3464 else
3465 tokn := UPPER(LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS));
3466 if((tokn is not null) and (LENGTHB(tokn) > 30)) then
3467 FND_MESSAGE.set_name('FND', 'FLEX-QUALIFIER TOO LONG');
3468 FND_MESSAGE.set_token('TOKNAME', 'VRULE');
3469 FND_MESSAGE.set_token('NAME', SUBSTR(s, bgn, 30));
3470 return(-1);
3471 end if;
3472 vr.fq_names(n) := tokn;
3473 bgn := endp + seplen;
3474 end if;
3475
3476 -- Segment qualifier name
3477 --
3478 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3479 if(endp <= 0) then
3480 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3481 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3482 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3483 return(-1);
3484 else
3485 tokn := UPPER(LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS));
3486 if(tokn is null) then
3487 FND_MESSAGE.set_name('FND', 'FLEX-MISSING SQNAME');
3488 FND_MESSAGE.set_token('TOKNAME', 'VRULE');
3489 return(-1);
3490 elsif(LENGTHB(tokn) > 30) then
3491 FND_MESSAGE.set_name('FND', 'FLEX-QUALIFIER TOO LONG');
3492 FND_MESSAGE.set_token('TOKNAME', 'VRULE');
3493 FND_MESSAGE.set_token('NAME', SUBSTR(s, bgn, 30));
3494 return(-1);
3495 else
3496 sq_name := tokn;
3497 vr.sq_names(n) := tokn;
3498 end if;
3499 bgn := endp + seplen;
3500 end if;
3501
3502 -- Include/Exclude indicator
3503 --
3504 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3505 if(endp <= 0) then
3506 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3507 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3508 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3509 return(-1);
3510 end if;
3511 tokn := LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS);
3512 if(tokn is null) then
3513 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO IE');
3514 FND_MESSAGE.set_token('NAME', sq_name);
3515 return(-1);
3516 end if;
3517 ie_flag := SUBSTR(tokn, 1, 1);
3518 vr.ie_flags(n) := ie_flag;
3519 if(ie_flag not in ('I', 'E')) then
3520 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BAD IE');
3521 FND_MESSAGE.set_token('NAME', sq_name);
3522 return(-1);
3523 end if;
3524 bgn := endp + seplen;
3525
3526 -- Error Message and Application short name
3527 --
3528 endp := INSTR(s, FND_FLEX_SERVER1.SEPARATOR, bgn);
3529 if(endp <= 0) then
3530 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NOSEP');
3531 FND_MESSAGE.set_token('TOKNUM', to_char(n));
3532 FND_MESSAGE.set_token('SEP', FND_FLEX_SERVER1.SEPARATOR);
3533 return(-1);
3534 end if;
3535 tokn := LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS);
3536 if(tokn is null) then
3537 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO MSG');
3538 FND_MESSAGE.set_token('NAME', sq_name);
3539 return(-1);
3540 elsif(LENGTHB(tokn) > 100) then
3541 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BIG MSG');
3542 FND_MESSAGE.set_token('NAME', sq_name);
3543 return(-1);
3544 else
3545 msg_begin := INSTR(tokn, 'NAME=');
3546 if(msg_begin <= 0) then
3547 vr.app_names(n) := NULL;
3548 vr.err_names(n) := tokn;
3549 else
3550 msg_begin := msg_begin + 5;
3551 if(LENGTH(tokn) < msg_begin) then
3552 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO MSGNAME');
3553 FND_MESSAGE.set_token('NAME', sq_name);
3554 return(-1);
3555 end if;
3556 vr.err_names(n) := SUBSTR(tokn, msg_begin);
3557 msg_begin := INSTR(tokn, 'APPL=');
3558 msg_end := INSTR(tokn, ';');
3559 if((msg_begin > 0) and (msg_end > 0) and
3560 (msg_end - msg_begin > 5)) then
3561 msg_begin := msg_begin + 5;
3562 if(msg_end - msg_begin > 50) then
3563 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BIG APNAME');
3564 FND_MESSAGE.set_token('NAME', sq_name);
3565 return(-1);
3566 end if;
3567 vr.app_names(n) := SUBSTR(tokn, msg_begin, msg_end - msg_begin);
3568 else
3569 vr.app_names(n) := 'FND';
3570 end if;
3571 end if;
3572 bgn := endp + seplen;
3573 end if;
3574
3575 -- Values to include or exclude.
3576 -- Parsed into format where each value is surrounded by the SEPARATOR.
3577 -- First put everything to the terminator into tokn, then parse tokn.
3578 --
3579 endp := INSTR(s, FND_FLEX_SERVER1.TERMINATOR, bgn);
3580 if(endp <= 0) then
3581 endp := vrulstr_len + 1;
3582 end if;
3583 tokn := LTRIM(RTRIM(SUBSTR(s, bgn, endp-bgn), BLANKS), BLANKS);
3584 if(tokn is null) then
3585 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE NO VALS');
3586 FND_MESSAGE.set_token('NAME', sq_name);
3587 return(-1);
3588 end if;
3589 if(LENGTHB(tokn) > 236) then
3590 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN VRULE BIG VALSTR');
3591 FND_MESSAGE.set_token('NAME', sq_name);
3592 return(-1);
3593 end if;
3594 bgn := 1;
3595 ieval := FND_FLEX_SERVER1.SEPARATOR;
3596 tokn_len := LENGTH(tokn);
3597 while(bgn <= tokn_len) loop
3598 endtok := INSTR(tokn, FND_FLEX_SERVER1.SEPARATOR, bgn);
3599 if(endtok <= 0) then
3600 endtok := tokn_len + 1;
3601 end if;
3602 ieval := ieval || LTRIM(RTRIM(SUBSTR(tokn, bgn, endtok-bgn),
3603 BLANKS), BLANKS);
3604 ieval := ieval || FND_FLEX_SERVER1.SEPARATOR;
3605 bgn := endtok + seplen;
3606 end loop;
3607 vr.cat_vals(n) := ieval;
3608 bgn := endp + seplen;
3609
3610 end loop;
3611
3612 vr.nvrules := n;
3613
3614 return(n);
3615
3616 EXCEPTION
3617 WHEN OTHERS then
3618 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3619 FND_MESSAGE.set_token('MSG', 'parse_vrules() exception: ' || SQLERRM);
3620 return(-2);
3621
3622 END parse_vrules;
3623
3624
3625 /*
3626 * Parse and set the custom error message.
3627 * Synatx is 'APPL=<application_short_name>NAME=<message_name>'
3628 */
3629 FUNCTION parse_set_msg(p_msg IN VARCHAR2) RETURN BOOLEAN
3630 IS
3631 l_appl VARCHAR2(2000);
3632 l_name VARCHAR2(2000);
3633 l_msg VARCHAR2(2000);
3634 l_pos_beg NUMBER;
3635 l_pos_end NUMBER;
3636 BEGIN
3637 l_msg := p_msg;
3638 IF (l_msg IS NULL) THEN
3639 RETURN FALSE;
3640 END IF;
3641
3642 l_pos_beg := Instr(l_msg, 'APPL=');
3643 IF (l_pos_beg = 0) THEN
3644 --
3645 -- No APPL token.
3646 --
3647 RETURN FALSE;
3648 END IF;
3649 l_pos_beg := l_pos_beg + Length('APPL=');
3650
3651 l_pos_end := Instr(l_msg, ';NAME=');
3652 IF (l_pos_end = 0) THEN
3653 --
3654 -- No NAME token.
3655 --
3656 RETURN FALSE;
3657 END IF;
3658
3659 IF (l_pos_end = l_pos_beg) THEN
3660 --
3661 -- No APPL value.
3662 --
3663 RETURN FALSE;
3664 END IF;
3665
3666 --
3667 -- Application Short Name
3668 --
3669 l_appl := Substr(l_msg, l_pos_beg, l_pos_end - l_pos_beg);
3670
3671 l_pos_beg := l_pos_end + Length(';NAME=');
3672 l_pos_end := Length(l_msg) + 1;
3673
3674 IF (l_pos_end = l_pos_beg) THEN
3675 --
3676 -- No NAME value.
3677 --
3678 RETURN FALSE;
3679 END IF;
3680
3681 --
3682 -- Message Name.
3683 --
3684 l_name := Substr(l_msg, l_pos_beg, l_pos_end - l_pos_beg);
3685
3686 fnd_message.set_name(l_appl, l_name);
3687
3688 RETURN TRUE;
3689 EXCEPTION
3690 WHEN OTHERS THEN
3691 RETURN FALSE;
3692 END parse_set_msg;
3693
3694
3695
3696
3697 /* ----------------------------------------------------------------------- */
3698 /* Function to interpret DISPLAYED token using the approach of */
3699 /* selecting all segments and their associated flexfield qualifiers */
3700 /* in a single outer join and then interpreting the tokens for all */
3701 /* segments at once. This requires the fewest possible database rows */
3702 /* retrieved and only a single select statement. */
3703 /* ----------------------------------------------------------------------- */
3704
3705 FUNCTION parse_displayed(fstruct IN FND_FLEX_SERVER1.FlexStructId,
3706 token_str IN VARCHAR2,
3707 dispsegs OUT nocopy FND_FLEX_SERVER1.DisplayedSegs)
3708 RETURN BOOLEAN IS
3709
3710 n_segs NUMBER;
3711 fq_table FND_FLEX_SERVER1.FlexQualTable;
3712 seg_disp FND_FLEX_SERVER1.CharArray;
3713 seg_rqd FND_FLEX_SERVER1.CharArray;
3714 tokenmap FND_FLEX_SERVER1.BooleanArray;
3715
3716 BEGIN
3717
3718 -- Initialize returned segment display map.
3719 --
3720 dispsegs.n_segflags := 0;
3721
3722 -- Get flexfield qualifier mapping to segments.
3723 --
3724 if(FND_FLEX_SERVER2.get_qualsegs(fstruct, n_segs, seg_disp,
3725 seg_rqd, fq_table) = FALSE) then
3726 return(FALSE);
3727 end if;
3728
3729
3730 if(evaluate_token(token_str, n_segs, fq_table, tokenmap) = FALSE) then
3731 return(FALSE);
3732 end if;
3733
3734 -- Still need to merge the displayed map obtained from the DISPLAYED token
3735 -- alone (n_tokappl) with the display map from the flex structure and
3736 -- return the completed map.
3737 --
3738 IF (fnd_flex_server1.g_debug_level > 0) THEN
3739 g_debug_text := 'Displayed Map=';
3740 END IF;
3741 for i in 1..n_segs loop
3742 if((seg_disp(i) = 'Y') and tokenmap(i)) then
3743 dispsegs.segflags(i) := TRUE;
3744 IF (fnd_flex_server1.g_debug_level > 0) THEN
3745 g_debug_text := g_debug_text || 'Y';
3746 END IF;
3747 else
3748 dispsegs.segflags(i) := FALSE;
3749 IF (fnd_flex_server1.g_debug_level > 0) THEN
3750 g_debug_text := g_debug_text || 'N';
3751 END IF;
3752 end if;
3753 end loop;
3754 IF (fnd_flex_server1.g_debug_level > 0) THEN
3755 FND_FLEX_SERVER1.add_debug(g_debug_text || '.');
3756 END IF;
3757 dispsegs.n_segflags := n_segs;
3758 return(TRUE);
3759
3760 EXCEPTION
3761 WHEN OTHERS then
3762 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3763 FND_MESSAGE.set_token('MSG', 'parse_displayed() exception: '||SQLERRM);
3764 return(FALSE);
3765
3766 END parse_displayed;
3767
3768 /* ------------------------------------------------------------------------- */
3769 /* Interpret the string token passed into FND_KEY_FLEX.DEFINE() call */
3770 /* on the client using the table of flexfield qualifiers for each */
3771 /* segment which is returned from get_segquals(). */
3772 /* Returns an array which has one element for each enabled segment */
3773 /* where the entry in the array is TRUE if and only if an odd number */
3774 /* of token elements apply to the segment. */
3775 /* Returns TRUE on success or FALSE on error. */
3776 /* ------------------------------------------------------------------------- */
3777
3778 FUNCTION evaluate_token(token_str IN VARCHAR2,
3779 n_segs IN NUMBER,
3780 fq_tab IN FND_FLEX_SERVER1.FlexQualTable,
3781 token_map OUT nocopy FND_FLEX_SERVER1.BooleanArray)
3782 RETURN BOOLEAN IS
3783
3784 n_toks NUMBER;
3785 segindex NUMBER;
3786 toks FND_FLEX_SERVER1.StringArray;
3787 s_ntokappl FND_FLEX_SERVER1.NumberArray;
3788
3789 BEGIN
3790
3791 -- Break up token into individual components
3792 --
3793 IF (token_str IS NOT NULL) THEN
3794 n_toks := FND_FLEX_SERVER1.to_stringarray2(token_str,
3795 FND_FLEX_SERVER1.TERMINATOR, toks);
3796 ELSE
3797 n_toks := 0;
3798 END IF;
3799
3800 -- Save some debug info
3801 --
3802 IF (fnd_flex_server1.g_debug_level > 0) THEN
3803 g_debug_text := 'Tokens:';
3804 for i in 1..n_toks loop
3805 g_debug_text := g_debug_text || toks(i) || ' ';
3806 end loop;
3807 FND_FLEX_SERVER1.add_debug(g_debug_text);
3808 END IF;
3809
3810 -- Initialize number of applicable tokens for each segment to 0.
3811 --
3812 for i in 1..n_segs loop
3813 s_ntokappl(i) := 0;
3814 end loop;
3815
3816 IF (fnd_flex_server1.g_debug_level > 0) THEN
3817 FND_FLEX_SERVER1.add_debug('Init ' || to_char(n_segs) || ' segs. ');
3818 END IF;
3819
3820 -- Now interpret each token to create the displayed map.
3821 -- If token is 'ALL' then just toggle the displayed bit for all segments.
3822 -- If token is a number then toggle the displayed bit for that seg number.
3823 -- Otherwise, toggle the displayed bits for all segments for which that
3824 -- qualifier applies. Exit if any errors.
3825 --
3826 for i in 1..n_toks loop
3827 if(toks(i) is null) then
3828 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN DUI NULL');
3829 return(FALSE);
3830 end if;
3831 if(toks(i) = 'ALL') then
3832 for j in 1..n_segs loop
3833 s_ntokappl(j) := s_ntokappl(j) + 1;
3834 end loop;
3835 elsif(FND_FLEX_SERVER1.isa_number(toks(i), segindex)) then
3836 if((segindex < 1) or (segindex > n_segs)) then
3837 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN DUI BAD SEGNUM');
3838 FND_MESSAGE.set_token('SEGNUM', to_char(segindex));
3839 return(FALSE);
3840 end if;
3841 s_ntokappl(segindex) := s_ntokappl(segindex) + 1;
3842 else
3843 segindex := 0;
3844 for k in 1..fq_tab.nentries loop
3845 if((fq_tab.fq_names(k) is not null) and
3846 (toks(i) = fq_tab.fq_names(k))) then
3847 segindex := fq_tab.seg_indexes(k);
3848 s_ntokappl(segindex) := s_ntokappl(segindex) + 1;
3849 end if;
3850 end loop;
3851 if(segindex = 0) then
3852 FND_MESSAGE.set_name('FND', 'FLEX-TOKEN DUI BAD QUAL');
3853 FND_MESSAGE.set_token('QTOKEN', toks(i));
3854 return(FALSE);
3855 end if;
3856 end if;
3857 end loop;
3858
3859 for i in 1..n_segs loop
3860 token_map(i) := (MOD(s_ntokappl(i), 2) = 1);
3861 end loop;
3862 return(TRUE);
3863
3864 EXCEPTION
3865 WHEN OTHERS then
3866 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3867 FND_MESSAGE.set_token('MSG', 'evaluate_token() exception: ' ||SQLERRM);
3868 return(FALSE);
3869
3870 END evaluate_token;
3871
3872 /* ------------------------------------------------------------------------- */
3873 /* Turns on or off calling of user PLSQL validation just before insert */
3874 /* of new combination. */
3875 /* ------------------------------------------------------------------------- */
3876 PROCEDURE enable_user_validation(Y_or_N IN VARCHAR2) IS
3877 BEGIN
3878 if(Y_or_N = 'Y') then
3879 userval_on := TRUE;
3880 else
3881 userval_on := FALSE;
3882 end if;
3883 END enable_user_validation;
3884
3885 /* ------------------------------------------------------------------------- */
3886 /* Turns on or off fdfgli calling after insert of new combination */
3887 /* in accounting flexfield. */
3888 /* ----------------------------------------------------------------------- */
3889 PROCEDURE enable_fdfgli(Y_or_N IN VARCHAR2) IS
3890 BEGIN
3891 if(Y_or_N = 'Y') then
3892 fdfgli_on := TRUE;
3893 else
3894 fdfgli_on := FALSE;
3895 end if;
3896 END enable_fdfgli;
3897
3898 /* ----------------------------------------------------------------------- */
3899 /* Calls FDFGLI if enabled. */
3900 /* Processes error messages returned by FDFGLI and handles any */
3901 /* possible exceptions. Returns TRUE on success or sets error */
3902 /* and returns FALSE on error. */
3903 /* */
3904 /* Note: FDFGLI called using dynamic SQL so no error at compile time */
3905 /* if GL_FLEX_INSERT_PKG is not there. */
3906 /* ----------------------------------------------------------------------- */
3907 FUNCTION call_fdfgli(ccid IN NUMBER) RETURN BOOLEAN IS
3908
3909 cursornum INTEGER;
3910 nprocessed INTEGER;
3911 sqlstr VARCHAR2(500);
3912 yes_or_no VARCHAR2(1);
3913 i_status VARCHAR2(1);
3914 i_industry VARCHAR2(1);
3915
3916 BEGIN
3917
3918 IF (fnd_flex_server1.g_debug_level > 0) THEN
3919 FND_FLEX_SERVER1.add_debug('Entering call_fdfgli() ');
3920 END IF;
3921
3922 if(fdfgli_on) then
3923
3924 -- Do not call FDFGLI just exit with TRUE if GL is not fully installed
3925 --
3926 if(FND_INSTALLATION.get(101, 101, i_status, i_industry) = FALSE) then
3927 FND_MESSAGE.set_name('FND', 'FLEX-CANT_GET_INSTALL');
3928 return(FALSE);
3929 end if;
3930 if((i_status is null) or (i_status <> 'I')) then
3931 return(TRUE);
3932 end if;
3933
3934 sqlstr := 'BEGIN if(gl_flex_insert_pkg.fdfgli(:n)) then :r := ''Y'';';
3935 sqlstr := sqlstr || ' else :r := ''N''; end if; END;';
3936 IF (fnd_flex_server1.g_debug_level > 0) THEN
3937 FND_FLEX_SERVER1.add_debug(sqlstr);
3938 END IF;
3939 cursornum := dbms_sql.open_cursor;
3940 dbms_sql.parse(cursornum, sqlstr, dbms_sql.v7);
3941 dbms_sql.bind_variable(cursornum, ':n', ccid);
3942 dbms_sql.bind_variable(cursornum, ':r', yes_or_no, 1);
3943 nprocessed := dbms_sql.execute(cursornum);
3944 dbms_sql.variable_value(cursornum, ':r', yes_or_no);
3945 IF (fnd_flex_server1.g_debug_level > 0) THEN
3946 FND_FLEX_SERVER1.add_debug('Dynamic SQL called FDFGLI and returned '||
3947 yes_or_no || '. ');
3948 END IF;
3949 dbms_sql.close_cursor(cursornum);
3950 return(yes_or_no = 'Y');
3951 end if;
3952
3953 return(TRUE);
3954
3955 EXCEPTION
3956 WHEN OTHERS then
3957 -- bug#4072642 -- maximum open cursors exceeded
3958 if dbms_sql.is_open(cursornum) then
3959 dbms_sql.close_cursor(cursornum);
3960 end if;
3961 if((SQLCODE = -6550) and (INSTR(SQLERRM, 'PLS-00201') > 0)) then
3962 FND_MESSAGE.set_name('FND', 'FLEX-FDFGLI MISSING');
3963 FND_MESSAGE.set_token('MSG', SQLERRM);
3964 else
3965 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
3966 FND_MESSAGE.set_token('MSG', 'fdfgli() exception: '||SQLERRM);
3967 end if;
3968 return(FALSE);
3969
3970 END call_fdfgli;
3971
3972 /* ----------------------------------------------------------------------- */
3973 /* Calls user PLSQL validation function FND_FLEX_PLSQL.validate(). */
3974 /* Requires FND_FLEX_PLSQL stub package for compilation. */
3975 /* The validate() function is called with both concatenated and */
3976 /* individual segment ids in order they are defined in the flexfield */
3977 /* structure. It will return TRUE if combination passes all user */
3978 /* defined validation rules, or FALSE on error or if it does not */
3979 /* pass user-defined validation. */
3980 /* Saves args input to user validation in uvdbg string. */
3981 /* ----------------------------------------------------------------------- */
3982 FUNCTION call_userval(fstruct IN FND_FLEX_SERVER1.FlexStructId,
3983 vdate IN DATE,
3984 nids IN NUMBER,
3985 delim IN VARCHAR2,
3986 segids IN FND_FLEX_SERVER1.ValueIdArray)
3987 RETURN BOOLEAN IS
3988 errmsg VARCHAR2(2000);
3989 catids VARCHAR2(2000);
3990 ids FND_FLEX_SERVER1.ValueIdArray;
3991
3992 BEGIN
3993
3994 -- Only do this for key flexfields
3995 --
3996 if(not fstruct.isa_key_flexfield) then
3997 return(TRUE);
3998 end if;
3999
4000 -- First concatenate ids, and populate the ids() array with ids or NULL
4001 -- while adding the input args to the debug string.
4002 --
4003 catids := concatenate_ids(nids, segids, delim);
4004 for i in 1..30 loop
4005 if(i <= nids) then
4006 ids(i) := segids(i);
4007 else
4008 ids(i) := NULL;
4009 end if;
4010 end loop;
4011
4012 -- Next save the debugging info to the standard server debug string.
4013 -- Note this is different from the client which will save it to a
4014 -- file fdfplv.log. Use errmsg as temporary string to store debug info.
4015 --
4016 errmsg := ' Calling FND_FLEX_PLSQL.validate(';
4017 errmsg := errmsg || to_char(fstruct.application_id) || ', ';
4018 errmsg := errmsg || fstruct.id_flex_code || ', ';
4019 errmsg := errmsg || to_char(fstruct.id_flex_num) || ', ';
4020 errmsg := errmsg || to_char(vdate, FND_FLEX_SERVER1.DATETIME_FMT) || ', ';
4021 errmsg := errmsg || delim || ', ';
4022
4023 IF (fnd_flex_server1.g_debug_level > 0) THEN
4024 FND_FLEX_SERVER1.add_debug(errmsg);
4025 FND_FLEX_SERVER1.add_debug(catids);
4026 FND_FLEX_SERVER1.add_debug(') ');
4027 END IF;
4028 errmsg := NULL;
4029
4030 -- Now call the function
4031 --
4032 if(FND_FLEX_PLSQL.validate(fstruct.application_id, fstruct.id_flex_code,
4033 fstruct.id_flex_num, vdate, delim, catids, nids, ids(1), ids(2),
4034 ids(3), ids(4), ids(5), ids(6), ids(7), ids(8), ids(9),
4035 ids(10), ids(11), ids(12), ids(13), ids(14), ids(15), ids(16),
4036 ids(17), ids(18), ids(19), ids(20), ids(21), ids(22), ids(23),
4037 ids(24), ids(25), ids(26), ids(27), ids(28), ids(29), ids(30),
4038 errmsg) = FALSE) then
4039 FND_MESSAGE.set_name('FND', 'FLEX-PLSQL VALIDATION ERROR');
4040 FND_MESSAGE.set_token('ERROR_MSG', errmsg);
4041 return(FALSE);
4042 end if;
4043 return(TRUE);
4044
4045 EXCEPTION
4046 WHEN OTHERS then
4047 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4048 FND_MESSAGE.set_token('MSG', 'call_userval() exception: '||SQLERRM);
4049 return(FALSE);
4050
4051 END call_userval;
4052
4053 /* ----------------------------------------------------------------------- */
4054 /* Returns number of SQL statements created during last call */
4055 /* to validate(). Use in conjunction with get_sql(). */
4056 /* ----------------------------------------------------------------------- */
4057 FUNCTION get_nsql RETURN NUMBER IS
4058 BEGIN
4059 return(FND_FLEX_SERVER1.get_nsql_internal);
4060 END get_nsql;
4061
4062 /* ----------------------------------------------------------------------- */
4063 /* Returns SQL statements created during last call */
4064 /* to validate(). Use in conjunction with get_sql(). */
4065 /* ----------------------------------------------------------------------- */
4066 FUNCTION get_sql(statement_num IN NUMBER,
4067 statement_piece IN NUMBER DEFAULT 1) RETURN VARCHAR2 IS
4068 BEGIN
4069 return(FND_FLEX_SERVER1.get_sql_internal(statement_num, statement_piece));
4070 EXCEPTION
4071 WHEN OTHERS then
4072 return('get_sql() exception: ' || SQLERRM);
4073 END get_sql;
4074
4075 /* ----------------------------------------------------------------------- */
4076 /* Returns the debug string. */
4077 /* ----------------------------------------------------------------------- */
4078 FUNCTION get_debug(stringnum IN NUMBER) RETURN VARCHAR2 IS
4079 BEGIN
4080 return(FND_FLEX_SERVER1.get_debug_internal(stringnum));
4081 EXCEPTION
4082 WHEN OTHERS then
4083 return('get_debug() exception: ' || SQLERRM);
4084 END get_debug;
4085
4086 /* ----------------------------------------------------------------------- */
4087
4088 -- ==================================================
4089 PROCEDURE do_dynamic_insert_for_java(p_application_id IN NUMBER,
4090 p_id_flex_code IN VARCHAR2,
4091 p_id_flex_num IN NUMBER,
4092 p_application_table_name IN VARCHAR2,
4093 p_segment_delimiter IN VARCHAR2,
4094 p_segment_count IN NUMBER,
4095 p_validation_date IN DATE,
4096 p_start_date_active IN DATE,
4097 p_end_date_active IN DATE,
4098 p_insert_sql IN VARCHAR2,
4099 p_insert_sql_binds IN VARCHAR2,
4100 p_select_sql IN VARCHAR2,
4101 p_select_sql_binds IN VARCHAR2,
4102 x_ccid OUT nocopy NUMBER,
4103 x_encoded_error OUT nocopy VARCHAR2)
4104 IS
4105 l_is_new VARCHAR2(100);
4106 BEGIN
4107 do_dynamic_insert_for_java
4108 (p_application_id => p_application_id,
4109 p_id_flex_code => p_id_flex_code,
4110 p_id_flex_num => p_id_flex_num,
4111 p_application_table_name => p_application_table_name,
4112 p_segment_delimiter => p_segment_delimiter,
4113 p_segment_count => p_segment_count,
4114 p_validation_date => p_validation_date,
4115 p_start_date_active => p_start_date_active,
4116 p_end_date_active => p_end_date_active,
4117 p_insert_sql => p_insert_sql,
4118 p_insert_sql_binds => p_insert_sql_binds,
4119 p_select_sql => p_select_sql,
4120 p_select_sql_binds => p_select_sql_binds,
4121 x_ccid => x_ccid,
4122 x_is_new => l_is_new,
4123 x_encoded_error => x_encoded_error);
4124 END do_dynamic_insert_for_java;
4125
4126 PROCEDURE do_dynamic_insert_for_java(p_application_id IN NUMBER,
4127 p_id_flex_code IN VARCHAR2,
4128 p_id_flex_num IN NUMBER,
4129 p_application_table_name IN VARCHAR2,
4130 p_segment_delimiter IN VARCHAR2,
4131 p_segment_count IN NUMBER,
4132 p_validation_date IN DATE,
4133 p_start_date_active IN DATE,
4134 p_end_date_active IN DATE,
4135 p_insert_sql IN VARCHAR2,
4136 p_insert_sql_binds IN VARCHAR2,
4137 p_select_sql IN VARCHAR2,
4138 p_select_sql_binds IN VARCHAR2,
4139 x_ccid OUT nocopy NUMBER,
4140 x_is_new OUT nocopy VARCHAR2,
4141 x_encoded_error OUT nocopy VARCHAR2)
4142 IS
4143 PRAGMA AUTONOMOUS_TRANSACTION;
4144
4145 l_func_name VARCHAR2(100);
4146 l_ff_structure FND_FLEX_SERVER1.flexstructid;
4147 l_bind_count NUMBER;
4148 l_binds fnd_flex_server1.valuearray;
4149 l_newline VARCHAR2(10);
4150 l_ccid NUMBER;
4151 l_cursor NUMBER;
4152 l_hash_value NUMBER;
4153 l_segment_ids fnd_flex_server1.valueidarray;
4154
4155 --
4156 -- Temporary number and varchar2 buffers.
4157 --
4158 l_num NUMBER;
4159 l_vc2 VARCHAR2(32000);
4160 BEGIN
4161 l_func_name := 'SSV.do_dynamic_insert_for_java()';
4162 l_newline := fnd_global.newline;
4163 x_is_new := 'U';
4164 --
4165 -- Lock the combination table.
4166 --
4167 BEGIN
4168 EXECUTE IMMEDIATE ('LOCK TABLE ' || p_application_table_name ||
4169 ' IN ROW SHARE MODE');
4170 EXCEPTION
4171 WHEN OTHERS THEN
4172 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4173 fnd_message.set_token('MSG', l_func_name ||
4174 ' is unable to lock table ' ||
4175 p_application_table_name || '. ' || l_newline ||
4176 'SQLERRM: ' || Sqlerrm);
4177 GOTO return_failure;
4178 END;
4179
4180 --
4181 -- Get the concatenated combination.
4182 -- End of Insert SQL Binds is concatenated combination.
4183 -- Result is something like '10\n20\nA\n'
4184 --
4185 l_vc2 := Ltrim(Substr(p_insert_sql_binds,
4186 Instr(p_insert_sql_binds,
4187 l_newline, -1, p_segment_count + 1)),
4188 l_newline);
4189
4190 --
4191 -- Parse the segment ids.
4192 --
4193 FOR i IN 1..p_segment_count LOOP
4194 l_num := Instr(l_vc2, l_newline, 1, 1);
4195 l_segment_ids(i) := Substr(l_vc2, 1, l_num - 1);
4196 l_vc2 := Substr(l_vc2, l_num + 1);
4197 END LOOP;
4198
4199 --
4200 -- Generate a hash value.
4201 --
4202 l_hash_value := hash_segs(p_segment_count, l_segment_ids);
4203
4204 --
4205 -- Lock the hash table.
4206 --
4207 BEGIN
4208 SELECT hash_value
4209 INTO l_num
4210 FROM fnd_flex_hash
4211 WHERE hash_value = l_hash_value
4212 FOR UPDATE;
4213 EXCEPTION
4214 WHEN OTHERS THEN
4215 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4216 fnd_message.set_token('MSG', l_func_name ||
4217 ' is unable to lock FND_FLEX_HASH. ' ||
4218 'Hash Value: ' || l_hash_value || l_newline ||
4219 'SQLERRM: ' || Sqlerrm);
4220 GOTO return_failure;
4221 END;
4222
4223 --
4224 -- We locked Comb. Table and the Hash table, now let's check the
4225 -- Comb. Table one more time.
4226 --
4227
4228 --
4229 -- See KeyFlexfield.getCCIDfromDynamicInsertion() for a sample SELECT SQL.
4230 --
4231
4232 --
4233 -- Parse SELECT SQL bind values.
4234 --
4235 l_bind_count := 0;
4236 l_vc2 := p_select_sql_binds;
4237 WHILE (l_vc2 IS NOT NULL) LOOP
4238 l_bind_count := l_bind_count + 1;
4239 l_num := Instr(l_vc2, l_newline, 1, 1);
4240 l_binds(l_bind_count) := Substr(l_vc2, 1, l_num - 1);
4241 l_vc2 := Substr(l_vc2, l_num + 1);
4242 END LOOP;
4243
4244 --
4245 -- Check if the combination is already in the table.
4246 --
4247 BEGIN
4248 l_cursor := dbms_sql.open_cursor;
4249 dbms_sql.parse(l_cursor, p_select_sql, dbms_sql.native);
4250 FOR i IN 1..l_bind_count LOOP
4251 dbms_sql.bind_variable(l_cursor, 'S' || i , l_binds(i));
4252 END LOOP;
4253 dbms_sql.define_column(l_cursor, 1, l_ccid);
4254 l_num := dbms_sql.execute_and_fetch(l_cursor, TRUE);
4255
4256 --
4257 -- Combination already exists.
4258 --
4259 dbms_sql.column_value(l_cursor, 1, l_ccid);
4260 dbms_sql.close_cursor(l_cursor);
4261 x_is_new := 'N';
4262 GOTO return_success;
4263 EXCEPTION
4264 WHEN no_data_found THEN
4265 --
4266 -- Combination doesn't exist, continue to INSERT.
4267 --
4268 dbms_sql.close_cursor(l_cursor);
4269 WHEN OTHERS THEN
4270 dbms_sql.close_cursor(l_cursor);
4271 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4272 fnd_message.set_token('MSG', l_func_name ||
4273 ' is unable to select from table ' ||
4274 p_application_table_name || '. ' || l_newline ||
4275 'SQLERRM: ' || Sqlerrm);
4276 GOTO return_failure;
4277 END;
4278
4279 --
4280 -- Call user validation function.
4281 --
4282 IF (userval_on) THEN
4283 l_ff_structure.isa_key_flexfield := TRUE;
4284 l_ff_structure.application_id := p_application_id;
4285 l_ff_structure.id_flex_code := p_id_flex_code;
4286 l_ff_structure.id_flex_num := p_id_flex_num;
4287
4288 IF (NOT call_userval(l_ff_structure,
4289 p_validation_date,
4290 p_segment_count,
4291 p_segment_delimiter,
4292 l_segment_ids)) THEN
4293 GOTO return_failure;
4294 END IF;
4295 END IF;
4296
4297
4298 --
4299 -- Now we are ready to insert.
4300 --
4301
4302 --
4303 -- See KeyFlexfield.getCCIDfromDynamicInsertion() for a sample INSERT SQL.
4304 --
4305
4306 --
4307 -- Parse INSERT SQL bind values.
4308 --
4309 l_bind_count := 0;
4310 l_vc2 := p_insert_sql_binds;
4311 WHILE (l_vc2 IS NOT NULL) LOOP
4312 l_bind_count := l_bind_count + 1;
4313 l_num := Instr(l_vc2, l_newline, 1, 1);
4314 l_binds(l_bind_count) := Substr(l_vc2, 1, l_num - 1);
4315 l_vc2 := Substr(l_vc2, l_num + 1);
4316 END LOOP;
4317
4318 --
4319 -- Get the next CCID.
4320 --
4321 BEGIN
4322 EXECUTE IMMEDIATE ('SELECT ' || p_application_table_name ||
4323 '_S.NEXTVAL FROM dual')
4324 INTO l_ccid;
4325 EXCEPTION
4326 WHEN OTHERS THEN
4327 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4328 fnd_message.set_token('MSG', l_func_name ||
4329 ' is unable to get next value from sequence ' ||
4330 p_application_table_name || '_S. ' ||l_newline||
4331 'SQLERRM: ' || Sqlerrm);
4332 GOTO return_failure;
4333 END;
4334
4335 --
4336 -- INSERT the combination.
4337 --
4338 BEGIN
4339 l_cursor := dbms_sql.open_cursor;
4340 dbms_sql.parse(l_cursor, p_insert_sql, dbms_sql.native);
4341
4342 --
4343 -- First bind is the CCID.
4344 --
4345 dbms_sql.bind_variable(l_cursor, 'CCID', l_ccid);
4346
4347 --
4348 -- Bind Start and End Dates.
4349 --
4350 dbms_sql.bind_variable(l_cursor, 'START_DATE_ACTIVE', p_start_date_active);
4351 dbms_sql.bind_variable(l_cursor, 'END_DATE_ACTIVE', p_end_date_active);
4352
4353 --
4354 -- Bind the rest.
4355 --
4356 FOR i IN 1..l_bind_count LOOP
4357 dbms_sql.bind_variable(l_cursor, 'I' || i, l_binds(i));
4358 END LOOP;
4359 l_num := dbms_sql.execute(l_cursor);
4360 dbms_sql.close_cursor(l_cursor);
4361 IF (l_num <> 1) THEN
4362 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4363 fnd_message.set_token('MSG', l_func_name ||
4364 ' is unable to insert new combination. ' ||
4365 ' Dynamic INSERT SQL returned ' || l_num ||
4366 ' rows, it was expected to return 1 row.');
4367 GOTO return_failure;
4368 END IF;
4369 x_is_new := 'Y';
4370 EXCEPTION
4371 WHEN OTHERS THEN
4372 dbms_sql.close_cursor(l_cursor);
4373 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4374 fnd_message.set_token('MSG', l_func_name ||
4375 ' is unable to insert new combination. ' ||
4376 ' into table ' || p_application_table_name ||
4377 '. ' || l_newline ||
4378 'SQLERRM: ' || Sqlerrm);
4379 GOTO return_failure;
4380 END;
4381
4382 --
4383 -- Now FDFGLI.
4384 --
4385 IF ((p_application_id = 101) AND (p_id_flex_code ='GL#')) THEN
4386 IF (NOT call_fdfgli(l_ccid)) THEN
4387 GOTO return_failure;
4388 END IF;
4389 END IF;
4390
4391 <<return_success>>
4392 x_encoded_error := NULL;
4393 x_ccid := l_ccid;
4394 COMMIT;
4395 RETURN;
4396
4397 <<return_failure>>
4398 x_encoded_error := fnd_message.get_encoded;
4399 x_ccid := -1;
4400 x_is_new := 'U';
4401 ROLLBACK;
4402 RETURN;
4403 EXCEPTION
4404 WHEN OTHERS THEN
4405 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
4406 fnd_message.set_token('MSG', 'Top level exception in ' || l_func_name ||
4407 l_newline || 'SQLERRM: ' || Sqlerrm);
4408 x_encoded_error := fnd_message.get_encoded;
4409 x_ccid := -1;
4410 ROLLBACK;
4411 RETURN;
4412 END do_dynamic_insert_for_java;
4413
4414
4415 -- ======================================================================
4416 -- Local Cache Functions
4417 -- ======================================================================
4418 FUNCTION check_vsc(p_application_id IN NUMBER,
4419 p_responsibility_id IN NUMBER,
4420 p_value_set_id IN NUMBER,
4421 p_parent_value IN VARCHAR2,
4422 p_value IN VARCHAR2,
4423 px_security_status IN OUT nocopy VARCHAR2,
4424 px_error_message IN OUT nocopy VARCHAR2)
4425 RETURN VARCHAR2
4426 IS
4427 BEGIN
4428 --
4429 -- seperate p_parent_value and p_value to get rid of ambiguity.
4430 --
4431 g_cache_key := (p_parent_value || '.' ||
4432 p_application_id || '.' ||
4433 p_responsibility_id || '.' ||
4434 p_value_set_id || '.' ||
4435 p_value);
4436
4437 fnd_plsql_cache.generic_1to1_get_value(vsc_cache_controller,
4438 vsc_cache_storage,
4439 g_cache_key,
4440 g_cache_value,
4441 g_cache_return_code);
4442
4443 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
4444 px_security_status := g_cache_value.varchar2_1;
4445 px_error_message := g_cache_value.varchar2_2;
4446 END IF;
4447
4448 RETURN(g_cache_return_code);
4449 EXCEPTION
4450 WHEN OTHERS THEN
4451 RETURN(fnd_plsql_cache.CACHE_NOTFOUND);
4452 END check_vsc;
4453
4454 PROCEDURE update_vsc(p_application_id IN NUMBER,
4455 p_responsibility_id IN NUMBER,
4456 p_value_set_id IN NUMBER,
4457 p_parent_value IN VARCHAR2,
4458 p_value IN VARCHAR2,
4459 p_security_status IN VARCHAR2,
4460 p_error_message IN VARCHAR2)
4461 IS
4462 BEGIN
4463 --
4464 -- seperate p_parent_value and p_value to get rid of ambiguity.
4465 --
4466 g_cache_key := (p_parent_value || '.' ||
4467 p_application_id || '.' ||
4468 p_responsibility_id || '.' ||
4469 p_value_set_id || '.' ||
4470 p_value);
4471
4472 fnd_plsql_cache.generic_cache_new_value
4473 (x_value => g_cache_value,
4474 p_varchar2_1 => p_security_status,
4475 p_varchar2_2 => p_error_message);
4476
4477 fnd_plsql_cache.generic_1to1_put_value(vsc_cache_controller,
4478 vsc_cache_storage,
4479 g_cache_key,
4480 g_cache_value);
4481 EXCEPTION
4482 WHEN OTHERS THEN
4483 RETURN;
4484 END update_vsc;
4485
4486 -- ======================================
4487 -- PROCEDURE : check_value_security
4488 -- ======================================
4489 -- Checks if a value is secured.
4490 --
4491 PROCEDURE check_value_security(p_security_check_mode IN VARCHAR2,
4492 p_flex_value_set_id IN NUMBER,
4493 p_parent_flex_value IN VARCHAR2,
4494 p_flex_value IN VARCHAR2,
4495 p_resp_application_id IN NUMBER,
4496 p_responsibility_id IN NUMBER,
4497 x_security_status OUT nocopy VARCHAR2,
4498 x_error_message OUT nocopy VARCHAR2)
4499 IS
4500 l_x_security_status VARCHAR2(100);
4501 l_x_error_message VARCHAR2(32000);
4502
4503 l_error_message fnd_flex_value_rules_tl.error_message%TYPE;
4504
4505 l_sql VARCHAR2(32000);
4506 l_sql_dependent VARCHAR2(2000);
4507
4508 l_vset fnd_flex_value_sets%ROWTYPE;
4509 l_lock_handle VARCHAR2(128) := NULL;
4510 l_vsc_code VARCHAR2(10);
4511
4512 --
4513 -- Returns '(f(p_column) BETWEEN f(p_min_column) AND f(p_max_column)) '
4514 -- f() depends on p_format_type and p_apply_nvl.
4515 --
4516 FUNCTION get_between_sql(p_format_type IN VARCHAR2,
4517 p_column IN VARCHAR2,
4518 p_min_column IN VARCHAR2,
4519 p_max_column IN VARCHAR2,
4520 p_apply_nvl IN BOOLEAN)
4521 RETURN VARCHAR2
4522 IS
4523 l_fbegin VARCHAR2(200);
4524 l_fend VARCHAR2(200);
4525 BEGIN
4526 IF (p_format_type = 'N') THEN
4527 l_fbegin := 'fnd_number.canonical_to_number(';
4528 l_fend := ')';
4529 ELSIF (p_format_type IN ('D', 'T')) THEN
4530 l_fbegin := 'To_date(';
4531 l_fend := ', ''DD-MON-RRRR HH24:MI:SS'')';
4532 ELSE
4533 l_fbegin := '';
4534 l_fend := '';
4535 END IF;
4536
4537 IF (p_apply_nvl) THEN
4538 RETURN('(' || l_fbegin || p_column || l_fend ||
4539 ' BETWEEN ' || l_fbegin || 'Nvl(' || p_min_column || ', ' || p_column || ')' || l_fend ||
4540 ' AND ' || l_fbegin || 'Nvl(' || p_max_column || ', ' || p_column || ')' || l_fend ||
4541 ') ');
4542 ELSE
4543 RETURN('(' || l_fbegin || p_column || l_fend ||
4544 ' BETWEEN ' || l_fbegin || p_min_column || l_fend ||
4545 ' AND ' || l_fbegin || p_max_column || l_fend ||
4546 ') ');
4547 END IF;
4548 END get_between_sql;
4549 BEGIN
4550 l_x_security_status := 'NOT-SECURED';
4551 l_x_error_message := NULL;
4552
4553 --
4554 -- Get the value set.
4555 --
4556 g_cache_key := p_flex_value_set_id;
4557 fnd_plsql_cache.custom_1to1_get_get_index(vst_cache_controller,
4558 g_cache_key,
4559 g_cache_index,
4560 g_cache_return_code);
4561 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
4562 l_vset := vst_cache_storage(g_cache_index);
4563 ELSE
4564 BEGIN
4565 SELECT *
4566 INTO l_vset
4567 FROM fnd_flex_value_sets
4568 WHERE 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : check_value_security' IS NOT NULL
4569 AND flex_value_set_id = p_flex_value_set_id;
4570 EXCEPTION
4571 WHEN OTHERS THEN
4572 fnd_message.set_name('FND', 'FLEX-ERROR LOADING VALUE SET');
4573 fnd_message.set_token('VALUE_SET_NAME', p_flex_value_set_id);
4574 l_x_security_status := 'VSET-NOTFOUND';
4575 l_x_error_message := fnd_message.get;
4576 GOTO goto_return;
4577 END;
4578 fnd_plsql_cache.custom_1to1_get_put_index(vst_cache_controller,
4579 g_cache_key,
4580 g_cache_index);
4581
4582 vst_cache_storage(g_cache_index) := l_vset;
4583 END IF;
4584
4585 --
4586 -- p_security_check_mode: Not used anymore. Kept for backward compatibility.
4587 --
4588 -- Y : Normal Check Only
4589 -- H : Hierarchy Check Only
4590 -- YH : Both Normal and Hierarchy Checks.
4591 --
4592 IF (p_security_check_mode NOT IN ('Y', 'H', 'YH')) THEN
4593 l_x_security_status := 'WRONG-ARG';
4594 l_x_error_message := 'Developer Error: p_check_security_mode must be Y, H, or YH.';
4595 GOTO goto_return;
4596 END IF;
4597
4598 IF (l_vset.security_enabled_flag NOT IN ('Y', 'H')) THEN
4599 GOTO goto_return;
4600 END IF;
4601
4602 IF (l_vset.validation_type NOT IN ('I', 'D', 'F')) THEN
4603 GOTO goto_return;
4604 END IF;
4605
4606 IF (p_flex_value IS NULL) THEN
4607 GOTO goto_return;
4608 END IF;
4609
4610 --
4611 -- First check the VSC
4612 --
4613 l_vsc_code := check_vsc(p_application_id => p_resp_application_id,
4614 p_responsibility_id => p_responsibility_id,
4615 p_value_set_id => p_flex_value_set_id,
4616 p_parent_value => p_parent_flex_value,
4617 p_value => p_flex_value,
4618 px_security_status => l_x_security_status,
4619 px_error_message => l_x_error_message);
4620
4621 IF (l_vsc_code = fnd_plsql_cache.CACHE_FOUND) THEN
4622 IF (fnd_flex_server1.g_debug_level > 0) THEN
4623 fnd_flex_server1.add_debug('Found in SSV.VSC.');
4624 END IF;
4625 GOTO goto_return;
4626 END IF;
4627
4628 --
4629 -- Not in the cache, continue on security check.
4630 --
4631 IF (l_vset.validation_type = 'D') THEN
4632 l_sql_dependent := (' AND (r.parent_flex_value_low = :b_parent_flex_value) ');
4633 ELSE
4634 l_sql_dependent := NULL;
4635 END IF;
4636
4637 --
4638 -- Check for value being directly excluded by a security rule...
4639 --
4640 l_sql :=
4641 'SELECT r.error_message' ||
4642 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u, fnd_flex_value_rule_lines l' ||
4643 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4644 ' AND u.application_id = :b_resp_application_id' ||
4645 ' AND u.responsibility_id = :b_responsibility_id' ||
4646 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4647 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4648 ' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
4649 ' AND l.include_exclude_indicator = ''E''' ||
4650 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4651 ' AND ROWNUM < 2';
4652
4653 BEGIN
4654 IF (l_vset.validation_type = 'D') THEN
4655 l_sql := l_sql || l_sql_dependent;
4656 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4657 p_flex_value_set_id,
4658 p_resp_application_id,
4659 p_responsibility_id,
4660 p_flex_value, p_flex_value, p_flex_value,
4661 p_parent_flex_value;
4662 ELSE
4663 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4664 p_flex_value_set_id,
4665 p_resp_application_id,
4666 p_responsibility_id,
4667 p_flex_value, p_flex_value, p_flex_value;
4668 END IF;
4669
4670 fnd_message.set_name('FND', 'FLEX-EXCLUDED BY SEC. RULE');
4671 fnd_message.set_token('MESSAGE', l_error_message);
4672
4673 l_x_security_status := 'EXCLUDED';
4674 l_x_error_message := fnd_message.get;
4675 GOTO goto_cache_the_result;
4676 EXCEPTION
4677 WHEN NO_DATA_FOUND THEN
4678 --
4679 -- NOT directly excluded, keep checking...
4680 --
4681 NULL;
4682 END;
4683
4684 --
4685 -- Check for value being hierarchically excluded by a security rule...
4686 --
4687 IF ((l_vset.security_enabled_flag = 'H') AND
4688 (l_vset.validation_type <> 'D')) THEN
4689
4690 fnd_flex_hierarchy_compiler.request_lock(l_vset.flex_value_set_name, l_lock_handle);
4691
4692 l_sql :=
4693 'SELECT r.error_message' ||
4694 ' 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' ||
4695 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4696 ' AND u.application_id = :b_resp_application_id' ||
4697 ' AND u.responsibility_id = :b_responsibility_id' ||
4698 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4699 ' AND h.flex_value_set_id = r.flex_value_set_id' ||
4700 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4701 ' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
4702 ' AND l.include_exclude_indicator = ''E''' ||
4703 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'h.child_flex_value_low', 'h.child_flex_value_high', FALSE) ||
4704 ' AND ' || get_between_sql(l_vset.format_type, 'h.parent_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4705 ' AND ROWNUM < 2 ';
4706
4707 BEGIN
4708 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4709 p_flex_value_set_id,
4710 p_resp_application_id,
4711 p_responsibility_id,
4712 p_flex_value;
4713
4714 fnd_message.set_name('FND', 'FLEX-EXCLUDED BY SEC. RULE');
4715 fnd_message.set_token('MESSAGE', l_error_message);
4716
4717 l_x_security_status := 'HIER-EXCLUDED';
4718 l_x_error_message := fnd_message.get;
4719 GOTO goto_cache_the_result;
4720 EXCEPTION
4721 WHEN NO_DATA_FOUND THEN
4722 --
4723 -- Not hierarchically excluded, keep checking...
4724 --
4725 NULL;
4726 END;
4727 END IF;
4728
4729 --
4730 -- Check for value NOT being directly included by a security rule...
4731 --
4732 l_sql :=
4733 'SELECT r.error_message' ||
4734 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u' ||
4735 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4736 ' AND u.application_id = :b_resp_application_id' ||
4737 ' AND u.responsibility_id = :b_responsibility_id' ||
4738 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4739 ' AND NOT exists (SELECT NULL' ||
4740 ' FROM fnd_flex_value_rule_lines l' ||
4741 ' WHERE l.flex_value_rule_id = r.flex_value_rule_id' ||
4742 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4743 ' AND l.include_exclude_indicator = ''I''' ||
4744 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4745 ' )' ||
4746 ' AND ROWNUM < 2 ';
4747
4748 BEGIN
4749 IF (l_vset.validation_type = 'D') THEN
4750 l_sql := l_sql || l_sql_dependent;
4751 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4752 p_flex_value_set_id,
4753 p_resp_application_id,
4754 p_responsibility_id,
4755 p_flex_value, p_flex_value, p_flex_value,
4756 p_parent_flex_value;
4757 ELSE
4758 EXECUTE IMMEDIATE l_sql INTO l_error_message USING
4759 p_flex_value_set_id,
4760 p_resp_application_id,
4761 p_responsibility_id,
4762 p_flex_value, p_flex_value, p_flex_value;
4763 END IF;
4764
4765 fnd_message.set_name('FND', 'FLEX-NOT INCL. BY SEC RULE');
4766 fnd_message.set_token('MESSAGE', l_error_message);
4767
4768 l_x_security_status := 'NOT-INCLUDED';
4769 l_x_error_message := fnd_message.get;
4770
4771 --
4772 -- NOT directly included, it might be hierarchically included...
4773 --
4774 EXCEPTION
4775 WHEN NO_DATA_FOUND THEN
4776 --
4777 -- Directly included, we are done.
4778 --
4779 GOTO goto_cache_the_result;
4780 END;
4781
4782 --
4783 -- Check for value NOT being hierarchically included by a security rule...
4784 --
4785 IF ((l_vset.security_enabled_flag = 'H') AND
4786 (l_vset.validation_type <> 'D')) THEN
4787
4788 l_sql :=
4789 'SELECT r.error_message' ||
4790 ' FROM fnd_flex_value_rules_vl r, fnd_flex_value_rule_usages u' ||
4791 ' WHERE r.flex_value_set_id = :b_flex_value_set_id' ||
4792 ' AND u.application_id = :b_resp_application_id' ||
4793 ' AND u.responsibility_id = :b_responsibility_id' ||
4794 ' AND u.flex_value_rule_id = r.flex_value_rule_id' ||
4795 ' AND NOT exists (SELECT NULL' ||
4796 ' FROM fnd_flex_value_hier_all h, fnd_flex_value_rule_lines l' ||
4797 ' WHERE h.flex_value_set_id = r.flex_value_set_id' ||
4798 ' AND l.flex_value_set_id = r.flex_value_set_id' ||
4799 ' AND l.flex_value_rule_id = r.flex_value_rule_id' ||
4800 ' AND l.include_exclude_indicator = ''I''' ||
4801 ' AND ' || get_between_sql(l_vset.format_type, ':b_flex_value', 'h.child_flex_value_low', 'h.child_flex_value_high', FALSE) ||
4802 ' AND ' || get_between_sql(l_vset.format_type, 'h.parent_flex_value', 'l.flex_value_low', 'l.flex_value_high', TRUE) ||
4803 ' )' ||
4804 ' AND ROWNUM < 2';
4805
4806 BEGIN
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;
4812
4813 --
4814 -- NOT hierarchically included, Error message is already set above.
4815 --
4816 GOTO goto_cache_the_result;
4817 EXCEPTION
4818 WHEN NO_DATA_FOUND THEN
4819 --
4820 -- Hierarchically included. Clear the error message set above.
4821 --
4822 l_x_security_status := 'NOT-SECURED';
4823 l_x_error_message := NULL;
4824
4825 GOTO goto_cache_the_result;
4826 END;
4827 END IF;
4828
4829 <<goto_cache_the_result>>
4830
4831 update_vsc(p_application_id => p_resp_application_id,
4832 p_responsibility_id => p_responsibility_id,
4833 p_value_set_id => p_flex_value_set_id,
4834 p_parent_value => p_parent_flex_value,
4835 p_value => p_flex_value,
4836 p_security_status => l_x_security_status,
4837 p_error_message => l_x_error_message);
4838
4839
4840 <<goto_return>>
4841
4842 IF (l_lock_handle IS NOT NULL) THEN
4843 fnd_flex_hierarchy_compiler.release_lock(l_vset.flex_value_set_name, l_lock_handle);
4844 END IF;
4845
4846 x_security_status := l_x_security_status;
4847 x_error_message := Substrb(l_x_error_message, 1, 1950);
4848
4849 RETURN;
4850 EXCEPTION
4851 WHEN OTHERS THEN
4852 IF (l_lock_handle IS NOT NULL) THEN
4853 fnd_flex_hierarchy_compiler.release_lock(l_vset.flex_value_set_name, l_lock_handle);
4854 END IF;
4855 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
4856 FND_MESSAGE.set_token('MSG', 'SSV.check_value_security(): ' || SQLERRM);
4857 x_security_status := 'EXCEPTION';
4858 x_error_message := Substrb(fnd_message.get, 1, 1950);
4859 END check_value_security;
4860
4861 -- ==================================================
4862 -- PROCEDURE : parse_flex_values
4863 -- ==================================================
4864 PROCEDURE parse_flex_values(p_concatenated_flex_values IN VARCHAR2,
4865 p_delimiter IN VARCHAR2,
4866 p_numof_flex_values IN NUMBER DEFAULT NULL,
4867 x_flex_values OUT nocopy fnd_flex_server1.stringarray,
4868 x_numof_flex_values OUT nocopy NUMBER)
4869 IS
4870 BEGIN
4871 --
4872 -- If only one segment is expected then no parsing, and no un-escaping.
4873 --
4874 IF ((Nvl(p_numof_flex_values, 0) = 1) OR
4875 (p_concatenated_flex_values IS NULL)) THEN
4876 x_numof_flex_values := 1;
4877 x_flex_values(1) := p_concatenated_flex_values;
4878 ELSE
4879 x_numof_flex_values :=
4880 fnd_flex_server1.to_stringarray(p_concatenated_flex_values,
4881 p_delimiter,
4882 x_flex_values);
4883 END IF;
4884 EXCEPTION
4885 WHEN OTHERS THEN
4886 RAISE;
4887 END parse_flex_values;
4888
4889 -- ==================================================
4890 -- PROCEDURE : concatenate_flex_values
4891 -- ==================================================
4892 PROCEDURE concatenate_flex_values(p_flex_values IN fnd_flex_server1.stringarray,
4893 p_numof_flex_values IN NUMBER,
4894 p_delimiter IN VARCHAR2,
4895 x_concatenated_flex_values OUT nocopy VARCHAR2)
4896 IS
4897 BEGIN
4898 IF (fnd_flex_server1.g_debug_level > 0) THEN
4899 fnd_flex_server1.add_debug('BEGIN SSV.concatenate_flex_values()');
4900 END IF;
4901 IF (p_numof_flex_values > 0) THEN
4902 x_concatenated_flex_values :=
4903 fnd_flex_server1.from_stringarray(p_numof_flex_values,
4904 p_flex_values,
4905 p_delimiter);
4906 ELSE
4907 x_concatenated_flex_values := '';
4908 END IF;
4909 EXCEPTION
4910 WHEN OTHERS THEN
4911 raise_application_error(-20001, 'SSV.concatenate_flex_values. SQLERRM : ' || Sqlerrm);
4912 END concatenate_flex_values;
4913
4914 -- ==================================================
4915 FUNCTION get_concatenated_value(p_delimiter IN VARCHAR2,
4916 p_segment_count IN NUMBER,
4917 p_segment1 IN VARCHAR2 DEFAULT NULL,
4918 p_segment2 IN VARCHAR2 DEFAULT NULL,
4919 p_segment3 IN VARCHAR2 DEFAULT NULL,
4920 p_segment4 IN VARCHAR2 DEFAULT NULL,
4921 p_segment5 IN VARCHAR2 DEFAULT NULL,
4922 p_segment6 IN VARCHAR2 DEFAULT NULL,
4923 p_segment7 IN VARCHAR2 DEFAULT NULL,
4924 p_segment8 IN VARCHAR2 DEFAULT NULL,
4925 p_segment9 IN VARCHAR2 DEFAULT NULL,
4926 p_segment10 IN VARCHAR2 DEFAULT NULL,
4927 p_segment11 IN VARCHAR2 DEFAULT NULL,
4928 p_segment12 IN VARCHAR2 DEFAULT NULL,
4929 p_segment13 IN VARCHAR2 DEFAULT NULL,
4930 p_segment14 IN VARCHAR2 DEFAULT NULL,
4931 p_segment15 IN VARCHAR2 DEFAULT NULL,
4932 p_segment16 IN VARCHAR2 DEFAULT NULL,
4933 p_segment17 IN VARCHAR2 DEFAULT NULL,
4934 p_segment18 IN VARCHAR2 DEFAULT NULL,
4935 p_segment19 IN VARCHAR2 DEFAULT NULL,
4936 p_segment20 IN VARCHAR2 DEFAULT NULL,
4937 p_segment21 IN VARCHAR2 DEFAULT NULL,
4938 p_segment22 IN VARCHAR2 DEFAULT NULL,
4939 p_segment23 IN VARCHAR2 DEFAULT NULL,
4940 p_segment24 IN VARCHAR2 DEFAULT NULL,
4941 p_segment25 IN VARCHAR2 DEFAULT NULL,
4942 p_segment26 IN VARCHAR2 DEFAULT NULL,
4943 p_segment27 IN VARCHAR2 DEFAULT NULL,
4944 p_segment28 IN VARCHAR2 DEFAULT NULL,
4945 p_segment29 IN VARCHAR2 DEFAULT NULL,
4946 p_segment30 IN VARCHAR2 DEFAULT NULL)
4947 RETURN VARCHAR2
4948 IS
4949 l_flex_values fnd_flex_server1.stringarray;
4950 l_concatenated_value VARCHAR2(32000);
4951 BEGIN
4952 l_flex_values(1) := p_segment1;
4953 l_flex_values(2) := p_segment2;
4954 l_flex_values(3) := p_segment3;
4955 l_flex_values(4) := p_segment4;
4956 l_flex_values(5) := p_segment5;
4957 l_flex_values(6) := p_segment6;
4958 l_flex_values(7) := p_segment7;
4959 l_flex_values(8) := p_segment8;
4960 l_flex_values(9) := p_segment9;
4961 l_flex_values(10) := p_segment10;
4962 l_flex_values(11) := p_segment11;
4963 l_flex_values(12) := p_segment12;
4964 l_flex_values(13) := p_segment13;
4965 l_flex_values(14) := p_segment14;
4966 l_flex_values(15) := p_segment15;
4967 l_flex_values(16) := p_segment16;
4968 l_flex_values(17) := p_segment17;
4969 l_flex_values(18) := p_segment18;
4970 l_flex_values(19) := p_segment19;
4971 l_flex_values(20) := p_segment20;
4972 l_flex_values(21) := p_segment21;
4973 l_flex_values(22) := p_segment22;
4974 l_flex_values(23) := p_segment23;
4975 l_flex_values(24) := p_segment24;
4976 l_flex_values(25) := p_segment25;
4977 l_flex_values(26) := p_segment26;
4978 l_flex_values(27) := p_segment27;
4979 l_flex_values(28) := p_segment28;
4980 l_flex_values(29) := p_segment29;
4981 l_flex_values(30) := p_segment30;
4982
4983 concatenate_flex_values(l_flex_values,
4984 p_segment_count,
4985 p_delimiter,
4986 l_concatenated_value);
4987
4988 RETURN(l_concatenated_value);
4989 END get_concatenated_value;
4990
4991 -- ==================================================
4992 PROCEDURE delete_dff_compiled(p_application_id IN NUMBER,
4993 p_descriptive_flexfield_name IN VARCHAR2)
4994 IS
4995 PRAGMA AUTONOMOUS_TRANSACTION;
4996 BEGIN
4997 DELETE FROM fnd_compiled_descriptive_flexs
4998 WHERE application_id = p_application_id
4999 AND descriptive_flexfield_name = p_descriptive_flexfield_name;
5000
5001 COMMIT;
5002 EXCEPTION
5003 WHEN OTHERS THEN
5004 ROLLBACK;
5005 RAISE;
5006 END delete_dff_compiled;
5007
5008 -- ==================================================
5009 PROCEDURE raise_dff_compiled(p_application_id IN NUMBER,
5010 p_descriptive_flexfield_name IN VARCHAR2)
5011 IS
5012 l_parameters wf_parameter_list_t := wf_parameter_list_t();
5013
5014 l_application_short_name fnd_application.application_short_name%TYPE;
5015 BEGIN
5016 SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : raise_dff_compiled' arcs_revision,
5017 application_short_name
5018 INTO g_arcs_revision,
5019 l_application_short_name
5020 FROM fnd_application
5021 WHERE application_id = p_application_id;
5022
5023 wf_event.addparametertolist(p_name => 'APPLICATION_SHORT_NAME',
5024 p_value => l_application_short_name,
5025 p_parameterlist => l_parameters);
5026
5027 wf_event.addparametertolist(p_name => 'APPLICATION_ID',
5028 p_value => p_application_id,
5029 p_parameterlist => l_parameters);
5030
5031 wf_event.addparametertolist(p_name => 'DESCRIPTIVE_FLEXFIELD_NAME',
5032 p_value => p_descriptive_flexfield_name,
5033 p_parameterlist => l_parameters);
5034
5035 BEGIN
5036 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.dff.compiled',
5037 p_event_key => (l_application_short_name || '.' ||
5038 p_descriptive_flexfield_name),
5039 p_event_data => NULL,
5040 p_parameters => l_parameters,
5041 p_send_date => Sysdate);
5042 EXCEPTION
5043 WHEN OTHERS THEN
5044 --
5045 -- If event fails, then remove the compiled data.
5046 -- This event is raised after the data were inserted into
5047 -- the compiled table.
5048 --
5049 -- Bug 5367119. Commenting following delete as we now raise the event
5050 -- before the data is inserted to compiled table.
5051
5052 /* delete_dff_compiled(p_application_id,
5053 p_descriptive_flexfield_name); */
5054 --
5055 -- Raise the exception.
5056 --
5057 FND_MESSAGE.set_name('FND', 'FLEX-COMPILE-WF_EVENT-ERROR');
5058 FND_MESSAGE.set_token('EVENT_NAME', 'oracle.apps.fnd.flex.dff.compiled');
5059 FND_MESSAGE.set_token('EVENT_KEY', l_application_short_name || '.' || p_descriptive_flexfield_name);
5060 FND_MESSAGE.set_token('ERROR', SQLERRM);
5061 FND_MESSAGE.RAISE_ERROR();
5062 END;
5063
5064 -- No Exception handling here, let it go up to caller.
5065
5066 END raise_dff_compiled;
5067
5068 -- ==================================================
5069 PROCEDURE delete_kff_structure_compiled(p_application_id IN NUMBER,
5070 p_id_flex_code IN VARCHAR2,
5071 p_id_flex_num IN NUMBER)
5072 IS
5073 PRAGMA AUTONOMOUS_TRANSACTION;
5074 BEGIN
5075 DELETE FROM fnd_compiled_id_flex_structs
5076 WHERE application_id = p_application_id
5077 AND id_flex_code = p_id_flex_code
5078 AND id_flex_num = p_id_flex_num;
5079
5080 COMMIT;
5081 EXCEPTION
5082 WHEN OTHERS THEN
5083 ROLLBACK;
5084 RAISE;
5085 END delete_kff_structure_compiled;
5086
5087 -- ==================================================
5088 PROCEDURE raise_kff_structure_compiled(p_application_id IN NUMBER,
5089 p_id_flex_code IN VARCHAR2,
5090 p_id_flex_num IN NUMBER)
5091 IS
5092 l_parameters wf_parameter_list_t := wf_parameter_list_t();
5093
5094 l_application_short_name fnd_application.application_short_name%TYPE;
5095 l_id_flex_structure_code fnd_id_flex_structures.id_flex_structure_code%TYPE;
5096 BEGIN
5097 SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : raise_kff_structure_compiled' arcs_revision,
5098 application_short_name
5099 INTO g_arcs_revision,
5100 l_application_short_name
5101 FROM fnd_application
5102 WHERE application_id = p_application_id;
5103
5104 SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : raise_kff_structure_compiled' arcs_revision,
5105 id_flex_structure_code
5106 INTO g_arcs_revision,
5107 l_id_flex_structure_code
5108 FROM fnd_id_flex_structures
5109 WHERE application_id = p_application_id
5110 AND id_flex_code = p_id_flex_code
5111 AND id_flex_num = p_id_flex_num;
5112
5113 wf_event.addparametertolist(p_name => 'APPLICATION_SHORT_NAME',
5114 p_value => l_application_short_name,
5115 p_parameterlist => l_parameters);
5116
5117 wf_event.addparametertolist(p_name => 'APPLICATION_ID',
5118 p_value => p_application_id,
5119 p_parameterlist => l_parameters);
5120
5121 wf_event.addparametertolist(p_name => 'ID_FLEX_CODE',
5122 p_value => p_id_flex_code,
5123 p_parameterlist => l_parameters);
5124
5125 wf_event.addparametertolist(p_name => 'ID_FLEX_STRUCTURE_CODE',
5126 p_value => l_id_flex_structure_code,
5127 p_parameterlist => l_parameters);
5128
5129 wf_event.addparametertolist(p_name => 'ID_FLEX_NUM',
5130 p_value => p_id_flex_num,
5131 p_parameterlist => l_parameters);
5132
5133 BEGIN
5134 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.kff.structure.compiled',
5135 p_event_key => (l_application_short_name || '.' ||
5136 p_id_flex_code || '.' ||
5137 l_id_flex_structure_code),
5138 p_event_data => NULL,
5139 p_parameters => l_parameters,
5140 p_send_date => Sysdate);
5141 EXCEPTION
5142 WHEN OTHERS THEN
5143 --
5144 -- If event fails, then remove the compiled data.
5145 -- This event is raised after the data were inserted into
5146 -- the compiled table.
5147 --
5148 -- Bug 5367119. Commenting following delete as we now raise the event
5149 -- before the data is inserted to compiled table.
5150
5151 /* delete_kff_structure_compiled(p_application_id,
5152 p_id_flex_code,
5153 p_id_flex_num); */
5154 --
5155 -- Raise the exception.
5156 --
5157 FND_MESSAGE.set_name('FND', 'FLEX-COMPILE-WF_EVENT-ERROR');
5158 FND_MESSAGE.set_token('EVENT_NAME', 'oracle.apps.fnd.flex.kff.structure.compiled');
5159 FND_MESSAGE.set_token('EVENT_KEY', l_application_short_name || '.' || p_id_flex_code || '.' || l_id_flex_structure_code);
5160 FND_MESSAGE.set_token('ERROR', SQLERRM);
5161 FND_MESSAGE.RAISE_ERROR();
5162
5163 END;
5164
5165 -- No Exception handling here, let it go up to caller.
5166
5167 END raise_kff_structure_compiled;
5168
5169 -- ==================================================
5170 PROCEDURE raise_vst_updated(p_flex_value_set_id IN NUMBER)
5171 IS
5172 l_parameters wf_parameter_list_t := wf_parameter_list_t();
5173
5174 l_flex_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
5175 BEGIN
5176 SELECT flex_value_set_name
5177 INTO l_flex_value_set_name
5178 FROM fnd_flex_value_sets
5179 WHERE flex_value_set_id = p_flex_value_set_id;
5180
5181 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_NAME',
5182 p_value => l_flex_value_set_name,
5183 p_parameterlist => l_parameters);
5184
5185 wf_event.addparametertolist(p_name => 'FLEX_VALUE_SET_ID',
5186 p_value => p_flex_value_set_id,
5187 p_parameterlist => l_parameters);
5188
5189 wf_event.raise(p_event_name => 'oracle.apps.fnd.flex.vst.updated',
5190 p_event_key => l_flex_value_set_name,
5191 p_event_data => NULL,
5192 p_parameters => l_parameters,
5193 p_send_date => Sysdate);
5194
5195 -- No Exception handling here, let it go up to caller.
5196
5197 END raise_vst_updated;
5198
5199 -- ==================================================
5200 PROCEDURE get_kfvcct_record(p_application_id IN NUMBER,
5201 p_id_flex_code IN VARCHAR2,
5202 px_kfvcct_record IN OUT nocopy kfvcct_record_type)
5203 IS
5204 l_kfvcct_key VARCHAR2(2000);
5205 BEGIN
5206 l_kfvcct_key := (p_application_id || g_newline ||
5207 p_id_flex_code);
5208
5209 fnd_plsql_cache.custom_1to1_get_get_index(kfvcct_cache_controller,
5210 l_kfvcct_key,
5211 g_cache_index,
5212 g_cache_return_code);
5213
5214 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
5215 px_kfvcct_record := kfvcct_cache_storage(g_cache_index);
5216
5217 ELSE
5218
5219 SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfvcct_record' arcs_revision,
5220 fif.table_application_id,
5221 fif.application_table_name,
5222 ft.table_id,
5223 fif.set_defining_column_name,
5224 fif.unique_id_column_name
5225 INTO g_arcs_revision,
5226 px_kfvcct_record.table_application_id,
5227 px_kfvcct_record.application_table_name,
5228 px_kfvcct_record.table_id,
5229 px_kfvcct_record.set_defining_column_name,
5230 px_kfvcct_record.unique_id_column_name
5231 FROM fnd_id_flexs fif, fnd_tables ft
5232 WHERE fif.application_id = p_application_id
5233 AND fif.id_flex_code = p_id_flex_code
5234 AND ft.application_id = fif.table_application_id
5235 AND ft.table_name = fif.application_table_name;
5236
5237 fnd_plsql_cache.custom_1to1_get_put_index(kfvcct_cache_controller,
5238 l_kfvcct_key,
5239 g_cache_index);
5240
5241 kfvcct_cache_storage(g_cache_index) := px_kfvcct_record;
5242 END IF;
5243 END get_kfvcct_record;
5244
5245 -- ==================================================
5246 PROCEDURE create_kfvssc_record(p_application_id IN NUMBER,
5247 p_id_flex_code IN VARCHAR2,
5248 p_id_flex_num IN NUMBER,
5249 px_kfvssc_record IN OUT nocopy kfvssc_record_type)
5250 IS
5251 l_kfvcct_record kfvcct_record_type;
5252
5253 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
5254
5255 l_compact_concat VARCHAR2(32000);
5256 l_padded_concat VARCHAR2(32000);
5257 l_padding_type VARCHAR2(30);
5258 l_padding_size NUMBER;
5259
5260 CURSOR kff_segments_cursor(p_application_id IN NUMBER,
5261 p_id_flex_code IN VARCHAR2,
5262 p_id_flex_num IN NUMBER,
5263 p_table_application_id IN NUMBER,
5264 p_table_id IN NUMBER)
5265 IS
5266 SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : create_kfvssc_record' arcs_revision,
5267 fifsg.application_column_name,
5268 ffvs.format_type vset_format_type,
5269 ffvs.maximum_size vset_maximum_size,
5270 ffvt.id_column_type vset_id_column_type,
5271 ffvt.id_column_size vset_id_column_size,
5272 fc.column_type column_type,
5273 fc.width column_size
5274 FROM fnd_id_flex_segments fifsg, fnd_flex_value_sets ffvs,
5275 fnd_columns fc, fnd_flex_validation_tables ffvt
5276 WHERE fifsg.application_id = p_application_id
5277 AND fifsg.id_flex_code = p_id_flex_code
5278 AND fifsg.id_flex_num = p_id_flex_num
5279 AND fifsg.enabled_flag = 'Y'
5280 AND fc.application_id = p_table_application_id
5281 AND fc.table_id = p_table_id
5282 AND fc.column_name = fifsg.application_column_name
5283 AND fifsg.flex_value_set_id = ffvs.flex_value_set_id(+)
5284 AND fifsg.flex_value_set_id = ffvt.flex_value_set_id(+)
5285 ORDER BY fifsg.segment_num, fifsg.segment_name;
5286
5287 BEGIN
5288 get_kfvcct_record(p_application_id,
5289 p_id_flex_code,
5290 l_kfvcct_record);
5291
5292 SELECT 'AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfv_sql' arcs_revision,
5293 fifst.concatenated_segment_delimiter
5294 INTO g_arcs_revision,
5295 l_delimiter
5296 FROM fnd_id_flex_structures fifst
5297 WHERE fifst.application_id = p_application_id
5298 AND fifst.id_flex_code = p_id_flex_code
5299 AND fifst.id_flex_num = p_id_flex_num;
5300
5301 l_compact_concat := NULL;
5302 l_padded_concat := NULL;
5303
5304 FOR kff_segment IN kff_segments_cursor(p_application_id,
5305 p_id_flex_code,
5306 p_id_flex_num,
5307 l_kfvcct_record.table_application_id,
5308 l_kfvcct_record.table_id)
5309 LOOP
5310 -- Compact
5311 IF (l_compact_concat IS NOT NULL) THEN
5312 l_compact_concat := (l_compact_concat ||
5313 ' || ''' ||
5314 l_delimiter ||
5315 ''' || ');
5316 END IF;
5317
5318 l_compact_concat := l_compact_concat || kff_segment.application_column_name;
5319
5320
5321 -- Padded
5322 IF (l_padded_concat IS NOT NULL) THEN
5323 l_padded_concat := (l_padded_concat ||
5324 ' || ''' ||
5325 l_delimiter ||
5326 ''' || ');
5327 END IF;
5328
5329 IF (kff_segment.vset_format_type IS NULL) THEN
5330 -- There is no value set attached to segment, use column type and size
5331 IF (kff_segment.column_type = 'N') THEN
5332 l_padding_type := 'LPAD';
5333 ELSE
5334 l_padding_type := 'RPAD';
5335 END IF;
5336 l_padding_size := kff_segment.column_size;
5337
5338 ELSE
5339 -- There is a value set attached to the segment
5340 IF (kff_segment.vset_id_column_type IS NULL) THEN
5341 -- This is a non-id value set (Not a table validated value set)
5342 IF (kff_segment.vset_format_type IN ('X', 'Y')) THEN
5343 l_padding_type := 'RPAD';
5344 l_padding_size := 20; -- ?? Should be 19.
5345
5346 ELSIF (kff_segment.vset_format_type = 'N') THEN
5347 l_padding_type := 'LPAD';
5348 l_padding_size := kff_segment.vset_maximum_size ;
5349
5350 ELSE
5351 l_padding_type := 'RPAD';
5352 l_padding_size := kff_segment.vset_maximum_size ;
5353
5354 END IF;
5355
5356 ELSE
5357 -- This is a id value set (A table validated value set)
5358 IF (kff_segment.vset_id_column_type = 'N') THEN
5359 l_padding_type := 'LPAD';
5360 ELSE
5361 l_padding_type := 'RPAD';
5362 END IF;
5363 l_padding_size := kff_segment.vset_id_column_size;
5364 END IF;
5365 END IF;
5366
5367 l_padded_concat := (l_padded_concat || l_padding_type ||
5368 '(NVL(' || kff_segment.application_column_name ||
5369 ','' ''), ' || l_padding_size || ')');
5370
5371 END LOOP;
5372
5373 px_kfvssc_record.compact_sql := ('SELECT /* AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfv_concat_segs */ ' ||
5374 l_compact_concat ||
5375 ' FROM ' || l_kfvcct_record.application_table_name);
5376
5377 px_kfvssc_record.padded_sql := ('SELECT /* AFFFSSVB.pls : $Revision: 120.13.12010000.3 $ : get_kfv_concat_segs */ ' ||
5378 l_padded_concat ||
5379 ' FROM ' || l_kfvcct_record.application_table_name);
5380
5381 px_kfvssc_record.set_defining_column_name := l_kfvcct_record.set_defining_column_name;
5382 px_kfvssc_record.unique_id_column_name := l_kfvcct_record.unique_id_column_name;
5383
5384 END create_kfvssc_record;
5385
5386 -- ==================================================
5387 PROCEDURE get_kfvssc_record(p_application_id IN NUMBER,
5388 p_id_flex_code IN VARCHAR2,
5389 p_id_flex_num IN NUMBER,
5390 px_kfvssc_record IN OUT nocopy kfvssc_record_type)
5391 IS
5392 l_kfvssc_key VARCHAR2(2000);
5393 BEGIN
5394 l_kfvssc_key := (p_application_id || g_newline ||
5395 p_id_flex_code || g_newline ||
5396 p_id_flex_num);
5397
5398 fnd_plsql_cache.custom_1to1_get_get_index(kfvssc_cache_controller,
5399 l_kfvssc_key,
5400 g_cache_index,
5401 g_cache_return_code);
5402
5403 IF (g_cache_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
5404 px_kfvssc_record := kfvssc_cache_storage(g_cache_index);
5405
5406 ELSE
5407 create_kfvssc_record(p_application_id,
5408 p_id_flex_code,
5409 p_id_flex_num,
5410 px_kfvssc_record);
5411
5412 fnd_plsql_cache.custom_1to1_get_put_index(kfvssc_cache_controller,
5413 l_kfvssc_key,
5414 g_cache_index);
5415
5416 kfvssc_cache_storage(g_cache_index) := px_kfvssc_record;
5417 END IF;
5418
5419 END get_kfvssc_record;
5420
5421 -- ==================================================
5422 FUNCTION get_kfv_concat_segs_by_ccid(p_concat_mode IN VARCHAR2,
5423 p_application_id IN NUMBER,
5424 p_id_flex_code IN VARCHAR2,
5425 p_id_flex_num IN NUMBER,
5426 p_ccid IN NUMBER,
5427 p_data_set IN NUMBER DEFAULT NULL)
5428 RETURN VARCHAR2
5429 IS
5430 l_kfvssc_record kfvssc_record_type;
5431
5432 l_data_set NUMBER;
5433 l_sql VARCHAR2(32000);
5434 l_result VARCHAR2(32000);
5435 BEGIN
5436 get_kfvssc_record(p_application_id,
5437 p_id_flex_code,
5438 p_id_flex_num,
5439 l_kfvssc_record);
5440
5441 IF (p_concat_mode = fnd_flex_server.CONCAT_MODE_PADDED) THEN
5442 l_sql := l_kfvssc_record.padded_sql;
5443 ELSE
5444 l_sql := l_kfvssc_record.compact_sql;
5445 END IF;
5446
5447 IF (p_data_set IS NULL) THEN
5448 l_data_set := p_id_flex_num;
5449 ELSE
5450 l_data_set := p_data_set;
5451 END IF;
5452
5453 l_sql := l_sql || ' WHERE ' || l_kfvssc_record.unique_id_column_name || ' = :b_unique_id_column';
5454
5455
5456 IF (l_kfvssc_record.set_defining_column_name IS NOT NULL) THEN
5457 l_sql := l_sql || ' AND ' || l_kfvssc_record.set_defining_column_name || ' = :b_set_defining_column';
5458
5459 execute immediate l_sql INTO l_result using p_ccid, l_data_set;
5460
5461 ELSE
5462 execute immediate l_sql INTO l_result using p_ccid;
5463
5464 END IF;
5465
5466 RETURN l_result;
5467 END get_kfv_concat_segs_by_ccid;
5468
5469 -- ==================================================
5470 FUNCTION get_kfv_concat_segs_by_rowid(p_concat_mode IN VARCHAR2,
5471 p_application_id IN NUMBER,
5472 p_id_flex_code IN VARCHAR2,
5473 p_id_flex_num IN NUMBER,
5474 p_rowid IN VARCHAR2)
5475 RETURN VARCHAR2
5476 IS
5477 l_kfvssc_record kfvssc_record_type;
5478
5479 l_sql VARCHAR2(32000);
5480 l_result VARCHAR2(32000);
5481 BEGIN
5482 get_kfvssc_record(p_application_id,
5483 p_id_flex_code,
5484 p_id_flex_num,
5485 l_kfvssc_record);
5486
5487 IF (p_concat_mode = fnd_flex_server.CONCAT_MODE_PADDED) THEN
5488 l_sql := l_kfvssc_record.padded_sql;
5489 ELSE
5490 l_sql := l_kfvssc_record.compact_sql;
5491 END IF;
5492
5493 l_sql := l_sql || ' WHERE ROWID = :b_rowid';
5494
5495 execute immediate l_sql INTO l_result using p_rowid;
5496
5497 RETURN l_result;
5498 END get_kfv_concat_segs_by_rowid;
5499
5500
5501 -- ==============================================================
5502 -- Bug 4725016 dbms_lock.allocate_unique issues a commit, this
5503 -- is a problem during transactionis that are opening cursors
5504 -- with 'select on update'. A commit issued at this point
5505 -- can invalidate those types of cursors. We created a private
5506 -- package that uses autonomous transaction that then calls
5507 -- dbms_lock.allocate_unique. In this way the commit will not
5508 -- affect the cursor.
5509 -- ==============================================================
5510 PROCEDURE autonomous_allocate_unique(p_lock_name IN VARCHAR2,
5511 px_lock_handle IN OUT nocopy VARCHAR2)
5512 IS
5513 PRAGMA AUTONOMOUS_TRANSACTION;
5514 BEGIN
5515
5516 dbms_lock.allocate_unique(lockname => p_lock_name,
5517 lockhandle => px_lock_handle,
5518 expiration_secs => 1*24*60*60); -- 1 day.
5519
5520 COMMIT;
5521
5522 END autonomous_allocate_unique;
5523 -- ==================================================
5524
5525 PROCEDURE request_lock(p_lock_name IN VARCHAR2,
5526 px_lock_handle IN OUT nocopy VARCHAR2)
5527 IS
5528 l_lock_status INTEGER;
5529 BEGIN
5530
5531 autonomous_allocate_unique(p_lock_name, px_lock_handle);
5532
5533 l_lock_status := dbms_lock.request(lockhandle => px_lock_handle,
5534 lockmode => dbms_lock.x_mode,
5535 timeout => dbms_lock.maxwait,
5536 release_on_commit => FALSE);
5537
5538 IF (l_lock_status <> 0) THEN
5539 raise_application_error(-20001,
5540 'Unable to request lock ' || p_lock_name ||
5541 '. dbms_lock.request() returned : ' ||
5542 l_lock_status);
5543 END IF;
5544 END request_lock;
5545
5546 -- ==================================================
5547 PROCEDURE release_lock(p_lock_name IN VARCHAR2,
5548 p_lock_handle IN VARCHAR2)
5549 IS
5550 l_lock_status INTEGER;
5551 BEGIN
5552 l_lock_status := dbms_lock.release(lockhandle => p_lock_handle);
5553
5554 IF (l_lock_status <> 0) THEN
5555 raise_application_error(-20001,
5556 'Unable to release lock ' || p_lock_name ||
5557 '. dbms_lock.release() returned : ' ||
5558 l_lock_status);
5559 END IF;
5560 END release_lock;
5561
5562
5563 -- ==================================================
5564
5565 /*
5566 Function
5567 isFlexBindChar
5568 Returns
5569 Type : Boolean
5570 Desc : Returns TRUE if the char is a valid Flex Bind char
5571 else returns FALSE
5572 Parameters
5573 p_char : Input character
5574 Scope
5575 Flex Internal
5576 Summary
5577 This function will accept a single character and test whether it is
5578 a valid Flex bind character or not
5579
5580 */
5581
5582 Function isFlexBindChar(p_char in Varchar2) return Boolean is
5583 Begin
5584 if instr(FLEX_BIND_CHARS, p_char) > 0 then
5585 return(TRUE);
5586 end if;
5587 return(FALSE);
5588 End;
5589
5590 /*
5591 Procedure
5592 queue_non_forms_warnings
5593 Parameters
5594 p_context : I : The context information for which the
5595 warning is being queued up
5596 p_bf_name : I : The BLOCK.FIELD name, which is to be
5597 displayed in the warning
5598 Scope
5599 Flex Internal
5600 Summary
5601 This procedure will put the warning messages in the global array.
5602 Those would be later retrieved by get_nonforms_warnings()
5603 */
5604 procedure queue_non_forms_warnings (
5605 p_warning_msg IN VARCHAR2
5606 ) is
5607 Begin
5608 g_non_forms_warnings_count := g_non_forms_warnings_count + 1;
5609 g_non_forms_warnings(g_non_forms_warnings_count) := substr(p_warning_msg, 1, 32000);
5610 End;
5611
5612 /*
5613 Function
5614 get_bind_name
5615 Returns :
5616 Type : Varchar2
5617 Description : Returns the actual Bind variable name
5618 without the prefix or :NULL suffix
5619 Parameters
5620 p_string : I : The input string to search for the bind
5621 variale name. Assumes the string starts
5622 with the bind variable
5623 p_bind_type : I : Bind variable type to search for. Valid values are :
5624 FF_SEGMENT - $FLEX$
5625 FF_PROFILE - $PROFILE$
5626 FF_FIELD - BLOCK.FIELD
5627 p_length : O : OUT parameter. Returns the length of
5628 the entire bind variable i.e. like
5629 length of $FLEX$.ValueSetName:NULL,
5630 which the caller might skip for further
5631 processing.
5632 Scope
5633 Flex internal
5634 Summary
5635 This function returns the bind variable name. Assumes the input string
5636 to start with the bind variable. This function is invoked from
5637 parse_bf_binds. If you need to invoke this make sure the string starts
5638 with the bind variable.
5639
5640 The following scenarios are handled:
5641
5642 1. $FLEX$
5643 - :$FLEX$.ValueSetName
5644 - :$FLEX$.ValueSetName [.{ID} | {MEANING} | {DESCRIPTION} ][ :NULL ]
5645 2. $PROFILES$
5646 - :$PROFILES$.ProfileName
5647 - :$PROFILES$.ProfileName [ :NULL ]
5648 3. BLOCK.FIELD
5649 - :BLOCK.FIELD
5650 - :BLOCK.FIELD:NULL
5651 Test case link
5652 -- to be included
5653 */
5654
5655 Function get_bind_name (
5656 p_string IN VARCHAR2,
5657 p_bind_type IN VARCHAR2,
5658 p_bind_length OUT nocopy NUMBER
5659 ) return VARCHAR2 is
5660 in_squote Boolean := FALSE;
5661 in_dquote Boolean := FALSE;
5662 v_bind_name Varchar2(32000) := NULL;
5663 v_bind_length Number := 0;
5664 v_indx Number := 0;
5665 v_str_len Number := 0;
5666 this_char Varchar2(10) := NULL;
5667 v_found_flag Boolean := FALSE;
5668 Begin
5669
5670 v_str_len := length(p_string);
5671
5672 if p_bind_type = FF_SEGMENT then -- Segment type i.e. $FLEX$ binds
5673
5674 if substr(p_string, 1, FLEX_PREFIX_LEN) <> FLEX_PREFIX then
5675
5676 v_bind_name := NULL;
5677 v_bind_length := -1;
5678
5679 goto finish;
5680
5681 end if;
5682
5683 v_indx := FLEX_PREFIX_LEN + 1;
5684
5685 elsif p_bind_type = FF_PROFILE then
5686
5687 if substr(p_string, 1, PROFILE_PREFIX_LEN) <> PROFILE_PREFIX then
5688
5689 v_bind_name := NULL;
5690 v_bind_length := -1;
5691
5692 goto finish;
5693
5694 end if;
5695
5696 v_indx := PROFILE_PREFIX_LEN + 1;
5697
5698 elsif p_bind_type = FF_FIELD then
5699
5700 v_indx := 1;
5701
5702 else
5703
5704 v_bind_name := NULL;
5705 v_bind_length := -1;
5706
5707 goto finish;
5708
5709 end if;
5710
5711 while ((NOT v_found_flag) AND (v_indx <= v_str_len))
5712 loop
5713
5714 this_char := substr(p_string, v_indx, 1);
5715
5716 /*
5717 This assumes the only legal characters in a bind
5718 variable name are A-Z, a-z, 0-9, '_', '.', ':', '$',
5719 '#'. This is covers legal names allowed by the
5720 database, forms and in c code. A '.' is needed
5721 for :block.field references. A ':' is need because
5722 it is allowable in profile names
5723 */
5724
5725 if NOT isFlexBindChar(this_char) then
5726
5727 v_found_flag := TRUE;
5728 v_indx := v_indx - 1; -- Bind var ends a char before
5729
5730 end if;
5731
5732 v_indx := v_indx + 1; -- Increment current index
5733
5734 end loop;
5735
5736 v_indx := v_indx - 1; -- Point v_indx to the end of bind var
5737
5738 v_bind_length := v_indx;
5739
5740 if p_bind_type = FF_SEGMENT then
5741
5742 v_bind_name := substr(p_string, FLEX_PREFIX_LEN + 1, v_indx - FLEX_PREFIX_LEN);
5743
5744 elsif p_bind_type = FF_PROFILE then
5745
5746 v_bind_name := substr(p_string, PROFILE_PREFIX_LEN + 1, v_indx - PROFILE_PREFIX_LEN);
5747
5748 else
5749
5750 v_bind_name := substr(p_string, 1, v_indx);
5751
5752 end if;
5753
5754 if instr(upper(v_bind_name), ':NULL') > 0 then
5755
5756 v_bind_name := substr(v_bind_name, 1, length(v_bind_name) - 5);
5757
5758 end if;
5759
5760 <<finish>>
5761
5762 p_bind_length := v_bind_length;
5763
5764 return(v_bind_name);
5765
5766 End get_bind_name;
5767
5768 /*
5769 Procedure
5770 parse_bind_names
5771 Parameters
5772 p_string : I : Input string to parse
5773 queued up. Those will be retrieved by
5774 get_nonforms_warnings()
5775 p_bind_names : O : An Array of B.F bind names found in p_string
5776 p_num_binds : O : Out parameter. Contains number of
5777 BLOCK.FIELD references found in p_string
5778 Scope
5779 Flex Internal
5780 Summary
5781 This procedure parses an input string for BLOCK.FIELD references
5782 and queues warning messages in Global Array. Returns number of
5783 such references through p_num_binds
5784 */
5785 procedure parse_bind_names (
5786 p_string IN VARCHAR2,
5787 p_bind_names OUT nocopy table_of_varchar2_32000,
5788 p_num_binds OUT nocopy NUMBER
5789 ) is
5790
5791 v_bind_name Varchar2(32000);
5792 v_bind_len Number := 0;
5793 v_flag Boolean := FALSE;
5794 v_indx Number;
5795 v_str_len Number;
5796 v_bind_count Number := 0;
5797 v_bind_names table_of_varchar2_32000;
5798 is_squote Boolean := FALSE;
5799 is_dquote Boolean := FALSE;
5800 is_bind Boolean := FALSE;
5801 this_char Varchar2(10);
5802 region_start Number;
5803 squote_ptr Number;
5804 squote_ptr_end Number;
5805 dquote_ptr Number;
5806 dquote_ptr_end Number;
5807 bind_ptr Number;
5808
5809 Begin
5810
5811 v_str_len := nvl(length(p_string), 0);
5812 if v_str_len = 0 then
5813 v_bind_count := 0;
5814 goto finish;
5815 end if;
5816 region_start := 1;
5817
5818 while (TRUE)
5819 loop
5820
5821 is_squote := FALSE;
5822 is_dquote := FALSE;
5823 is_bind := FALSE;
5824
5825 squote_ptr := INSTR(p_string, '''', region_start, 1);
5826 dquote_ptr := INSTR(p_string, '"', region_start, 1);
5827 bind_ptr := INSTR(p_string, ':', region_start, 1);
5828
5829 if bind_ptr = 0 then
5830 goto finish; -- No more binds ! Finish processing.
5831 end if;
5832
5833 if squote_ptr = dquote_ptr then -- i.e squote_ptr = dquote_ptr = 0
5834 is_bind := TRUE;
5835 else
5836 if squote_ptr = 0 then
5837 if bind_ptr < dquote_ptr then
5838 is_bind := TRUE;
5839 else
5840 is_dquote := TRUE;
5841 end if;
5842 elsif dquote_ptr = 0 then
5843 if bind_ptr < squote_ptr then
5844 is_bind := TRUE;
5845 else
5846 is_squote := TRUE;
5847 end if;
5848 else
5849 if squote_ptr = least(squote_ptr, dquote_ptr, bind_ptr) then
5850 is_squote := TRUE;
5851 elsif dquote_ptr = least(squote_ptr, dquote_ptr, bind_ptr) then
5852 is_dquote := TRUE;
5853 else
5854 is_bind := TRUE;
5855 end if;
5856 end if;
5857 end if;
5858
5859 if is_squote then
5860 squote_ptr_end := INSTR(p_string, '''', squote_ptr + 1);
5861 if squote_ptr_end = 0 then -- Unterminated quotes
5862 goto finish;
5863 else
5864 region_start := squote_ptr_end + 1;
5865 is_squote := FALSE;
5866 end if;
5867 end if;
5868
5869 if is_dquote then
5870 dquote_ptr_end := INSTR(p_string, '"', dquote_ptr + 1);
5871 if dquote_ptr_end = 0 then -- Unterminated quotes
5872 goto finish;
5873 else
5874 region_start := dquote_ptr_end + 1;
5875 is_dquote := FALSE;
5876 end if;
5877 end if;
5878
5879 if is_bind then
5880 v_indx := bind_ptr + 1;
5881 v_bind_len := 0;
5882 if substr(p_string, v_indx, FLEX_PREFIX_LEN) = FLEX_PREFIX then
5883 v_bind_name := get_bind_name (
5884 p_string => substr(p_string, v_indx, v_str_len - v_indx + 1),
5885 p_bind_type => FF_SEGMENT,
5886 p_bind_length => v_bind_len
5887 );
5888 elsif substr(p_string, v_indx, PROFILE_PREFIX_LEN) = PROFILE_PREFIX then
5889 v_bind_name := get_bind_name (
5890 p_string => substr(p_string, v_indx, v_str_len - v_indx + 1),
5891 p_bind_type => FF_PROFILE,
5892 p_bind_length => v_bind_len
5893 );
5894 else -- Hmm a B.F reference ... process it ...
5895 v_bind_name := get_bind_name (
5896 p_string => substr(p_string, v_indx, v_str_len - v_indx + 1),
5897 p_bind_type => FF_FIELD,
5898 p_bind_length => v_bind_len
5899 );
5900 v_bind_count := v_bind_count + 1;
5901 v_bind_names(v_bind_count) := v_bind_name;
5902 end if;
5903 if v_bind_len > 0 then
5904 region_start := v_indx + v_bind_len;
5905 else
5906 region_start := v_indx + 1;
5907 end if;
5908 end if;
5909
5910 end loop;
5911
5912 <<finish>>
5913 p_num_binds := v_bind_count;
5914 p_bind_names := v_bind_names;
5915 End parse_bind_names;
5916
5917 function get_non_forms_warnings return Varchar2 is
5918 l_msg_string Varchar2(32000);
5919 Begin
5920 return(l_msg_string);
5921 End get_non_forms_warnings;
5922
5923 -- ==================================================
5924
5925 PROCEDURE compute_non_forms_warnings_dff(p_application_id IN NUMBER,
5926 p_descriptive_flexfield_name IN VARCHAR2,
5927 x_warning_count OUT nocopy NUMBER)
5928 IS
5929 l_non_forms_warn_table_type table_of_varchar2_32000;
5930 l_application_short_name fnd_application.application_short_name%TYPE;
5931 l_application_name fnd_application_tl.application_name%TYPE;
5932 l_title fnd_descriptive_flexs_tl.title%TYPE;
5933 l_meaning fnd_lookup_values_vl.meaning%TYPE;
5934 l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
5935 l_srs_flag BOOLEAN;
5936
5937 PROCEDURE compute_context_segment_warn(p_application_id in NUMBER)
5938 IS
5939 e_DefaultTypeField EXCEPTION;
5940 e_DefaultTypeSQL EXCEPTION;
5941 l_reference_field fnd_descriptive_flexs.default_context_field_name%TYPE;
5942 l_validation_type fnd_flex_value_sets.validation_type%TYPE;
5943 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
5944 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
5945 l_num_binds NUMBER;
5946 l_segement_name fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
5947 BEGIN
5948
5949
5950 SELECT fnd_flex_value_sets.validation_type,fnd_flex_validation_tables.additional_where_clause,
5951 fnd_flex_validation_tables.additional_quickpick_columns,
5952 fnd_descriptive_flexs.default_context_field_name,fnd_flex_value_sets.flex_value_set_name
5953 INTO l_validation_type,l_additional_where_clause,l_additional_quickpick_columns,
5954 l_reference_field,l_value_set_name
5955 FROM fnd_flex_value_sets,fnd_descriptive_flexs,fnd_flex_validation_tables
5956 WHERE fnd_flex_value_sets.flex_value_set_id (+)= fnd_descriptive_flexs.context_override_value_set_id
5957 AND fnd_flex_value_sets.flex_value_set_id = fnd_flex_validation_tables.flex_value_set_id(+)
5958 AND fnd_descriptive_flexs.application_id = p_application_id
5959 AND fnd_descriptive_flexs.descriptive_flexfield_name = p_descriptive_flexfield_name;
5960
5961 BEGIN -- Check context segment's value set's warnings
5962
5963 IF l_validation_type = 'F' THEN -- If table validated valueset
5964
5965 IF (l_additional_quickpick_columns is not null) THEN
5966 parse_bind_names(l_additional_quickpick_columns, l_non_forms_warn_table_type, l_num_binds);
5967 IF (l_num_binds > 0) THEN
5968 FOR i IN 1 .. l_num_binds
5969 LOOP
5970 fnd_message.set_name('FND','FLEX-BLK_FLD_CTX_VSET_WARN_DFF');
5971 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
5972 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
5973 queue_non_forms_warnings(fnd_message.get());
5974 END LOOP;
5975 END IF;
5976 END IF;
5977
5978 IF (l_additional_where_clause is not null) THEN
5979 parse_bind_names(l_additional_where_clause, l_non_forms_warn_table_type, l_num_binds);
5980 IF (l_num_binds > 0) THEN
5981 FOR i IN 1 .. l_num_binds
5982 LOOP
5983 fnd_message.set_name('FND','FLEX-BLK_FLD_CTX_VSET_WARN_DFF');
5984 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
5985 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
5986 queue_non_forms_warnings(fnd_message.get());
5987 END LOOP;
5988 END IF;
5989 END IF;
5990 END IF;
5991 IF l_reference_field IS NOT NULL THEN
5992 IF (INSTR(l_reference_field,':$PROFILES$.') <> 1) THEN
5993 fnd_message.set_name('FND','FLEX-CTX_REF_FIELD_WARN_DFF');
5994 fnd_message.set_token('REFERENCE_FIELD',SUBSTR(l_reference_field,INSTR(l_reference_field,':')+1));
5995 queue_non_forms_warnings(fnd_message.get());
5996 END IF;
5997 END IF;
5998 EXCEPTION
5999 WHEN NO_DATA_FOUND THEN
6000 NULL;
6001 END; -- Check context segment's value set's warnings
6002 END compute_context_segment_warn;
6003
6004 PROCEDURE compute_context_warnings(p_context_code IN VARCHAR2)
6005 IS
6006 PROCEDURE compute_segment_warnings(p_application_column_name IN VARCHAR2)
6007 IS
6008 l_num_binds NUMBER;
6009 i NUMBER;
6010 l_format_type fnd_flex_value_sets.format_type%TYPE;
6011 l_validation_type fnd_flex_value_sets.validation_type%TYPE;
6012 l_default_type fnd_descr_flex_column_usages.default_type%TYPE;
6013 l_default_value fnd_descr_flex_column_usages.default_value%TYPE;
6014 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
6015 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
6016 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
6017 l_segment_name fnd_descr_flex_column_usages.end_user_column_name%TYPE;
6018 e_DateTime EXCEPTION;
6019 e_PairOrSpecial EXCEPTION;
6020 e_TableValidated EXCEPTION;
6021 e_DefaultTypeField EXCEPTION;
6022 e_DEfaultTypeSQL EXCEPTION;
6023 BEGIN
6024
6025 SELECT fnd_flex_value_sets.validation_type,fnd_flex_value_sets.format_type,
6026 fnd_descr_flex_column_usages.default_type,
6027 fnd_descr_flex_column_usages.default_value,fnd_flex_value_sets.flex_value_set_id,
6028 fnd_flex_value_sets.flex_value_set_name,fnd_descr_flex_column_usages.end_user_column_name
6029 INTO l_validation_type,l_format_type,l_default_type,l_default_value,l_value_set_id,
6030 l_value_set_name,l_segment_name
6031 FROM fnd_descr_flex_column_usages,fnd_flex_value_sets
6032 WHERE fnd_descr_flex_column_usages.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id (+)
6033 AND fnd_descr_flex_column_usages.application_column_name = p_application_column_name
6034 AND fnd_descr_flex_column_usages.APPLICATION_ID = p_application_id
6035 AND fnd_descr_flex_column_usages.DESCRIPTIVE_FLEXFIELD_NAME = p_descriptive_flexfield_name
6036 AND fnd_descr_flex_column_usages.ENABLED_FLAG = 'Y'
6037 AND fnd_descr_flex_column_usages.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code;
6038
6039 BEGIN -- Check segment's defaulting logic
6040 IF l_default_type = 'F' THEN -- If default type is field
6041 RAISE e_DefaultTypeField;
6042 ELSIF l_default_type = 'S' THEN -- If default type is SQL
6043 RAISE e_DefaultTypeSQL;
6044 END IF;
6045 EXCEPTION
6046 WHEN e_DefaultTypeField THEN
6047 IF l_srs_flag = FALSE THEN
6048 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_DFF');
6049 fnd_message.set_token('CONTEXT_CODE', p_context_code);
6050 ELSE
6051 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_SRS');
6052 END IF;
6053 fnd_message.set_token('SEGMENT_NAME', l_segment_name);
6054 fnd_message.set_token('BLOCK_FIELD', substr(l_default_value, instr(l_default_value, ':')+1));
6055 queue_non_forms_warnings(fnd_message.get());
6056 WHEN e_DefaultTypeSQL THEN
6057 parse_bind_names(l_default_value, l_non_forms_warn_table_type, l_num_binds);
6058 IF (l_num_binds > 0) then
6059 FOR i in 1 .. l_num_binds
6060 LOOP
6061 IF l_srs_flag = FALSE THEN
6062 fnd_message.set_name('FND','FLEX-BLK_FLD_DEF_WARN_DFF');
6063 fnd_message.set_token('CONTEXT_CODE', p_context_code);
6064 ELSE
6065 fnd_message.set_name('FND','FLEX-BLK_FLD_DEF_WARN_SRS');
6066 END IF;
6067 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6068 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6069 queue_non_forms_warnings(fnd_message.get());
6070 END LOOP;
6071 END IF;
6072 END; -- Check segment's defaulting logic
6073
6074 BEGIN -- Check segment's value set's warnings
6075 IF l_validation_type IN ('P','U') THEN -- If Pair or Special Validation Valueset
6076 RAISE e_PairOrSpecial;
6077 ELSIF l_validation_type = 'F' THEN
6078 RAISE e_TableValidated;
6079 END IF;
6080 IF l_format_type IN ('D','T') THEN
6081 RAISE e_DateTime;
6082 END IF;
6083 EXCEPTION
6084 WHEN e_PairOrSpecial THEN
6085 IF l_srs_flag = FALSE THEN
6086 fnd_message.set_name('FND','FLEX-USER_EXIT_VSET_WARN_DFF');
6087 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6088 ELSE
6089 fnd_message.set_name('FND','FLEX-USER_EXIT_VSET_WARN_SRS');
6090 END IF;
6091 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6092 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6093 SELECT meaning
6094 INTO l_meaning
6095 FROM fnd_lookup_values_vl
6096 WHERE lookup_type='SEG_VAL_TYPES'
6097 AND lookup_code = l_validation_type;
6098 fnd_message.set_token('VALIDATION_TYPE',l_meaning);
6099 queue_non_forms_warnings(fnd_message.get());
6100 WHEN e_TableValidated THEN
6101 SELECT additional_where_clause,additional_quickpick_columns
6102 INTO l_additional_where_clause,l_additional_quickpick_columns
6103 FROM fnd_flex_validation_tables
6104 WHERE flex_value_set_id = l_value_set_id;
6105 IF (l_additional_quickpick_columns is not null) THEN
6106 parse_bind_names(l_additional_quickpick_columns, l_non_forms_warn_table_type, l_num_binds);
6107 IF (l_num_binds > 0) THEN
6108 FOR i IN 1 .. l_num_binds
6109 LOOP
6110 IF l_srs_flag = FALSE THEN
6111 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_DFF');
6112 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6113 ELSE
6114 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_SRS');
6115 END IF;
6116 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6117 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6118 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6119 queue_non_forms_warnings(fnd_message.get());
6120 END LOOP;
6121 END IF;
6122 END IF;
6123
6124 IF (l_additional_where_clause is not null) THEN
6125 parse_bind_names(l_additional_where_clause, l_non_forms_warn_table_type, l_num_binds);
6126
6127 IF (l_num_binds > 0) THEN
6128 FOR i IN 1 .. l_num_binds
6129 LOOP
6130 IF l_srs_flag = FALSE THEN
6131 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_DFF');
6132 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6133 ELSE
6134 fnd_message.set_name('FND','FLEX-BLK_FLD_VSET_WARN_SRS');
6135 END IF;
6136 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6137 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6138 fnd_message.set_token('BLOCK_FIELD',l_non_forms_warn_table_type(i));
6139 queue_non_forms_warnings(fnd_message.get());
6140 END LOOP;
6141 END IF;
6142 END IF;
6143 WHEN e_DateTime THEN
6144 IF l_srs_flag = FALSE THEN
6145 fnd_message.set_name('FND','FLEX-DATE_VSET_WARN_DFF');
6146 fnd_message.set_token('CONTEXT_CODE',p_context_code);
6147 ELSE
6148 fnd_message.set_name('FND','FLEX-DATE_VSET_WARN_SRS');
6149 END IF;
6150 fnd_message.set_token('SEGMENT_NAME',l_segment_name);
6151 fnd_message.set_token('VALUE_SET_NAME',l_value_set_name);
6152 SELECT meaning
6153 INTO l_meaning
6154 FROM fnd_lookup_values_vl
6155 WHERE lookup_type='FIELD_TYPE'
6156 AND lookup_code = l_format_type;
6157 fnd_message.set_token('FORMAT_TYPE',l_meaning);
6158 queue_non_forms_warnings(fnd_message.get());
6159 END; -- Check segment's value set's warnings
6160 EXCEPTION
6161 WHEN NO_DATA_FOUND THEN
6162 NULL;
6163
6164 END compute_segment_warnings;
6165 BEGIN
6166 FOR v_EnabledSegments IN
6167 (SELECT application_column_name
6168 FROM fnd_descr_flex_column_usages
6169 WHERE application_id = p_application_id
6170 AND descriptive_flexfield_name = p_descriptive_flexfield_name
6171 AND descriptive_flex_context_code = p_context_code
6172 AND enabled_flag = 'Y')
6173 LOOP
6174 compute_segment_warnings(v_EnabledSegments.application_column_name);
6175 END LOOP;
6176
6177 END compute_context_warnings;
6178
6179 BEGIN
6180
6181 g_non_forms_warnings_count := 0;
6182
6183 --
6184 -- Compute non_forms warnings for DFF
6185 --
6186
6187 SELECT application_short_name, application_name
6188 INTO l_application_short_name, l_application_name
6189 FROM fnd_application_vl
6190 WHERE application_id = p_application_id;
6191
6192 SELECT title
6193 INTO l_title
6194 FROM fnd_descriptive_flexs_vl
6195 WHERE application_id = p_application_id
6196 AND descriptive_flexfield_name=p_descriptive_flexfield_name;
6197
6198 IF p_descriptive_flexfield_name LIKE '$SRS$.%' THEN
6199 l_srs_flag := TRUE;
6200 ELSE
6201 l_srs_flag := FALSE;
6202 END IF;
6203
6204 compute_context_segment_warn(p_application_id);
6205
6206 FOR v_EnabledContext IN
6207 (SELECT descriptive_flex_context_code
6208 FROM fnd_descr_flex_contexts
6209 WHERE application_id = p_application_id
6210 AND descriptive_flexfield_name = p_descriptive_flexfield_name
6211 AND enabled_flag = 'Y')
6212 LOOP
6213 compute_context_warnings(v_EnabledContext.descriptive_flex_context_code);
6214 END LOOP;
6215
6216 --
6217 -- Top Warning Message
6218 --
6219
6220 IF (g_non_forms_warnings_count > 0) THEN
6221 IF (p_descriptive_flexfield_name LIKE '$SRS$.%') THEN
6222 fnd_message.set_name('FND', 'FLEX-FORMS_ONLY_WARN_SRS');
6223 fnd_message.set_token('PROGRAM_SHORT_NAME', Substr(p_descriptive_flexfield_name, Length('$SRS$.') + 1));
6224 ELSE
6225 fnd_message.set_name('FND', 'FLEX-FORMS_ONLY_WARN_DFF');
6226 fnd_message.set_token('TITLE', l_title);
6227 END IF;
6228 fnd_message.set_token('APPLICATION_NAME', l_application_name);
6229 fnd_message.set_token('WARNING_COUNT', g_non_forms_warnings_count);
6230 g_non_forms_warnings(0) := fnd_message.get();
6231 END IF;
6232 x_warning_count := g_non_forms_warnings_count;
6233
6234 END compute_non_forms_warnings_dff;
6235
6236 -- ==================================================
6237
6238 PROCEDURE compute_non_forms_warnings_kff(p_application_id IN NUMBER,
6239 p_id_flex_code IN VARCHAR2,
6240 p_id_flex_num IN NUMBER,
6241 x_warning_count OUT nocopy NUMBER)
6242 IS
6243
6244 --
6245 -- Compute non_forms warnings for KFF
6246 --
6247
6248 l_application_short_name fnd_application.application_short_name%TYPE;
6249 l_application_name fnd_application_tl.application_name%TYPE;
6250 l_id_flex_structure_code fnd_id_flex_structures.id_flex_structure_code%TYPE;
6251 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE;
6252 l_nsegments NUMBER;
6253 l_message VARCHAR2(1000);
6254 l_validation_type VARCHAR2(1000);
6255 l_format_type VARCHAR2(1000);
6256 l_flextype FND_FLEX_KEY_API.FLEXFIELD_TYPE;
6257 l_strctype FND_FLEX_KEY_API.STRUCTURE_TYPE;
6258 l_segtype FND_FLEX_KEY_API.SEGMENT_TYPE;
6259 l_seglist FND_FLEX_KEY_API.SEGMENT_LIST;
6260 l_vset_r FND_VSET.VALUESET_R;
6261 l_vset_dr FND_VSET.VALUESET_DR;
6262
6263 PROCEDURE compute_segment_warnings (p_application_column_name IN VARCHAR2)
6264 IS
6265 l_table_of_varchar2_32000 table_of_varchar2_32000;
6266 l_num_binds NUMBER;
6267 i NUMBER;
6268 BEGIN
6269
6270 l_segtype := fnd_flex_key_api.find_segment(l_flextype, l_strctype, p_application_column_name);
6271
6272 if (l_segtype.value_set_id is not null) then
6273
6274 fnd_vset.get_valueset(l_segtype.value_set_id, l_vset_r, l_vset_dr);
6275 if (l_vset_r.validation_type in ('P', 'U')) then
6276 fnd_message.set_name('FND', 'FLEX-USER_EXIT_VSET_WARN_KFF');
6277 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6278 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6279 select meaning into l_validation_type
6280 from fnd_lookup_values_vl
6281 where lookup_type='SEG_VAL_TYPES'
6282 and lookup_code=l_vset_r.validation_type;
6283 fnd_message.set_token('VALIDATION_TYPE', l_validation_type);
6284 l_message := FND_MESSAGE.get;
6285 queue_non_forms_warnings(l_message);
6286 end if;
6287
6288 if (l_vset_dr.format_type in ('D', 'T')) then
6289 fnd_message.set_name('FND', 'FLEX-DATE_VSET_WARN_KFF');
6290 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6291 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6292 select meaning into l_format_type
6293 from fnd_lookup_values_vl
6294 where lookup_type='FIELD_TYPE'
6295 and lookup_code=l_vset_dr.format_type;
6296 fnd_message.set_token('FORMAT_TYPE', l_format_type);
6297 l_message := FND_MESSAGE.get;
6298 queue_non_forms_warnings(l_message);
6299 end if;
6300
6301 if (l_vset_r.validation_type = 'F') then
6302
6303 select additional_quickpick_columns into l_additional_quickpick_columns from fnd_flex_validation_tables where flex_value_set_id=l_segtype.value_set_id;
6304
6305 if (l_additional_quickpick_columns is not null) then
6306 parse_bind_names(l_additional_quickpick_columns, l_table_of_varchar2_32000, l_num_binds);
6307 if (l_num_binds > 0) then
6308 for i in 1 .. l_num_binds
6309 loop
6310 fnd_message.set_name('FND', 'FLEX-BLK_FLD_VSET_WARN_KFF');
6311 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6312 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6313 fnd_message.set_token('BLOCK_FIELD', l_table_of_varchar2_32000(i));
6314 l_message := fnd_message.get;
6315 queue_non_forms_warnings(l_message);
6316 end loop;
6317 end if;
6318 end if;
6319
6320 if (l_vset_r.table_info.where_clause is not null) then
6321 parse_bind_names(l_vset_r.table_info.where_clause, l_table_of_varchar2_32000, l_num_binds);
6322 if (l_num_binds > 0) then
6323 for i in 1 .. l_num_binds
6324 loop
6325 fnd_message.set_name('FND', 'FLEX-BLK_FLD_VSET_WARN_KFF');
6326 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6327 fnd_message.set_token('VALUE_SET_NAME', l_vset_r.name);
6328 fnd_message.set_token('BLOCK_FIELD', l_table_of_varchar2_32000(i));
6329 l_message := fnd_message.get;
6330 queue_non_forms_warnings(l_message);
6331 end loop;
6332 end if;
6333 end if;
6334
6335 end if;
6336
6337 end if;
6338
6339 if (l_segtype.default_type = 'S') then
6340 parse_bind_names(l_segtype.default_value, l_table_of_varchar2_32000, l_num_binds);
6341 if (l_num_binds > 0) then
6342 for i in 1 .. l_num_binds
6343 loop
6344 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_KFF');
6345 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6346 fnd_message.set_token('BLOCK_FIELD', l_table_of_varchar2_32000(i));
6347 l_message := fnd_message.get;
6348 queue_non_forms_warnings(l_message);
6349 end loop;
6350 end if;
6351 end if;
6352
6353 if (l_segtype.default_type = 'F') then
6354 fnd_message.set_name('FND', 'FLEX-BLK_FLD_DEF_WARN_KFF');
6355 fnd_message.set_token('SEGMENT_NAME', l_segtype.segment_name);
6356 fnd_message.set_token('BLOCK_FIELD', substr(l_segtype.default_value, instr(l_segtype.default_value, ':')+1));
6357 l_message := fnd_message.get;
6358 queue_non_forms_warnings(l_message);
6359 end if;
6360
6361 EXCEPTION
6362 when others then
6363 null;
6364
6365 END compute_segment_warnings;
6366
6367 BEGIN
6368
6369 g_non_forms_warnings_count := 0;
6370
6371 fnd_flex_key_api.set_session_mode(session_mode => 'customer_data');
6372
6373 select application_short_name, application_name
6374 into l_application_short_name, l_application_name
6375 from fnd_application_vl
6376 where application_id=p_application_id;
6377
6378 select id_flex_structure_code into l_id_flex_structure_code
6379 from fnd_id_flex_structures
6380 where application_id=p_application_id
6381 and id_flex_code=p_id_flex_code
6382 and id_flex_num=p_id_flex_num;
6383
6384 l_flextype := fnd_flex_key_api.find_flexfield(appl_short_name => l_application_short_name, flex_code => p_id_flex_code);
6385
6386 l_strctype := fnd_flex_key_api.find_structure(l_flextype, l_id_flex_structure_code);
6387
6388 fnd_flex_key_api.get_segments(l_flextype, l_strctype, TRUE, l_nsegments, l_seglist);
6389
6390 for i in 1..l_nsegments loop
6391
6392 compute_segment_warnings(l_seglist(i));
6393
6394 end loop;
6395
6396 --
6397 -- Top Warning Message
6398 --
6399 IF (g_non_forms_warnings_count > 0) THEN
6400 fnd_message.set_name('FND', 'FLEX-FORMS_ONLY_WARN_KFF');
6401 fnd_message.set_token('STRUCTURE_CODE', l_id_flex_structure_code);
6402 fnd_message.set_token('TITLE', l_flextype.flex_title);
6403 fnd_message.set_token('APPLICATION_NAME', l_application_name);
6404 fnd_message.set_token('WARNING_COUNT', g_non_forms_warnings_count);
6405 g_non_forms_warnings(0) := fnd_message.get();
6406 END IF;
6407 x_warning_count := g_non_forms_warnings_count;
6408
6409 END compute_non_forms_warnings_kff;
6410
6411 -- ==================================================
6412
6413 FUNCTION get_non_forms_warning(p_warning_index IN NUMBER)
6414 RETURN VARCHAR2
6415 IS
6416 BEGIN
6417 IF (p_warning_index >= 0 AND
6418 p_warning_index <= g_non_forms_warnings_count) THEN
6419 RETURN g_non_forms_warnings(p_warning_index);
6420 ELSE
6421 RETURN NULL;
6422 END IF;
6423 END get_non_forms_warning;
6424
6425 BEGIN
6426 --
6427 -- Function calls for global initializations
6428 --
6429 g_newline := fnd_global.newline;
6430 blanks := ' ' || fnd_global.tab || fnd_global.newline;
6431 g_non_forms_warnings_count := 0;
6432
6433 -- Initialize Caches
6434
6435 fnd_plsql_cache.custom_1to1_init('SSV.VST',
6436 vst_cache_controller);
6437 vst_cache_storage.DELETE;
6438
6439 fnd_plsql_cache.custom_1to1_init('SSV.KFVSSC',
6440 kfvssc_cache_controller);
6441 kfvssc_cache_storage.DELETE;
6442
6443 fnd_plsql_cache.custom_1to1_init('SSV.KFVCCT',
6444 kfvcct_cache_controller);
6445
6446 kfvcct_cache_storage.DELETE;
6447
6448 fnd_plsql_cache.generic_1to1_init('SSV.VSC',
6449 vsc_cache_controller,
6450 vsc_cache_storage);
6451
6452 END fnd_flex_server;