[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_KEYVAL
Source
1 PACKAGE BODY fnd_flex_keyval AS
2 /* $Header: AFFFKVLB.pls 120.2.12010000.1 2008/07/25 14:14:06 appldev ship $ */
3
4
5 -- PRIVATE CONSTANTS
6 --
7
8 -- PRIVATE FUNCTIONS
9 --
10
11 FUNCTION init_all(p_resp_appl_id IN NUMBER,
12 p_resp_id IN NUMBER,
13 p_user_id IN NUMBER,
14 x_resp_appl_id OUT nocopy NUMBER,
15 x_resp_id OUT nocopy NUMBER,
16 x_user_id OUT nocopy NUMBER)
17 RETURN BOOLEAN;
18
19 PROCEDURE clear_combination_globals;
20
21 PROCEDURE clear_all_but_error;
22
23
24 -- PRIVATE GLOBAL VARIABLES
25 --
26
27 nvalidated NUMBER;
28 value_vals FND_FLEX_SERVER1.ValueArray;
29 value_svals FND_FLEX_SERVER1.ValueArray;
30 value_ids FND_FLEX_SERVER1.ValueIdArray;
31 value_descs FND_FLEX_SERVER1.ValueDescArray;
32 value_desclens FND_FLEX_SERVER1.NumberArray;
33 cc_cols FND_FLEX_SERVER1.TabColArray;
34 cc_coltypes FND_FLEX_SERVER1.CharArray;
35 segtypes FND_FLEX_SERVER1.SegFormats;
36 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
37 derv FND_FLEX_SERVER1.DerivedVals;
38 tbl_derv FND_FLEX_SERVER1.DerivedVals;
39 drv_quals FND_FLEX_SERVER1.Qualifiers;
40 tbl_quals FND_FLEX_SERVER1.Qualifiers;
41 n_xcol_vals NUMBER;
42 xcol_vals FND_FLEX_SERVER1.StringArray;
43 ccid NUMBER;
44 delim VARCHAR2(1);
45 err_segn NUMBER;
46 err_msg VARCHAR2(2000);
47 err_text VARCHAR2(2000);
48 segcodes VARCHAR2(30);
49 new_comb BOOLEAN;
50
51 -- Return statuses
52 --
53 sta_valid BOOLEAN;
54 sta_secured BOOLEAN;
55 sta_value_err BOOLEAN;
56 sta_unsupported_err BOOLEAN;
57 sta_serious_err BOOLEAN;
58
59 /* ----------------------------------------------------------------------- */
60 /* Please see package specification for public function documentation.*/
61 /* ----------------------------------------------------------------------- */
62
63 FUNCTION validate_segs(operation IN VARCHAR2,
64 appl_short_name IN VARCHAR2,
65 key_flex_code IN VARCHAR2,
66 structure_number IN NUMBER,
67 concat_segments IN VARCHAR2,
68 values_or_ids IN VARCHAR2 DEFAULT 'V',
69 validation_date IN DATE DEFAULT SYSDATE,
70 displayable IN VARCHAR2 DEFAULT 'ALL',
71 data_set IN NUMBER DEFAULT NULL,
72 vrule IN VARCHAR2 DEFAULT NULL,
73 where_clause IN VARCHAR2 DEFAULT NULL,
74 get_columns IN VARCHAR2 DEFAULT NULL,
75 allow_nulls IN BOOLEAN DEFAULT FALSE,
76 allow_orphans IN BOOLEAN DEFAULT FALSE,
77 resp_appl_id IN NUMBER DEFAULT NULL,
78 resp_id IN NUMBER DEFAULT NULL,
79 user_id IN NUMBER DEFAULT NULL,
80 select_comb_from_view IN VARCHAR2 DEFAULT NULL,
81 no_combmsg IN VARCHAR2 DEFAULT NULL,
82 where_clause_msg IN VARCHAR2 DEFAULT NULL)
83 RETURN BOOLEAN IS
84 resp_apid NUMBER;
85 uresp_id NUMBER;
86 userid NUMBER;
87 valid_stat NUMBER;
88 dins_flag VARCHAR2(1);
89 nulls_ok VARCHAR2(1);
90 required_flag VARCHAR2(1);
91 invoking_mode VARCHAR2(1);
92 validate_mode VARCHAR2(30);
93 catsegs_in VARCHAR2(2000);
94
95 BEGIN
96
97 -- Initialize everything including all global variables and set user
98 --
99 if(init_all(resp_appl_id, resp_id, user_id,
100 resp_apid, uresp_id, userid) = FALSE) then
101 goto cleanup_and_return;
102 end if;
103
104 -- Set up flags and optional inputs
105 --
106 nulls_ok := 'N';
107 required_flag := 'N';
108 invoking_mode := 'V';
109 validate_mode := 'FULL';
110 catsegs_in := SUBSTRB(concat_segments, 1, 2000);
111
112 -- Set rest of parameters based on the requested operation
113 --
114 if(operation = 'FIND_COMBINATION') then
115 dins_flag := 'N';
116 elsif(operation = 'CREATE_COMBINATION') then
117 dins_flag := 'Y';
118 required_flag := 'Y'; -- Bug 1526918
119 -- Bug 1531345
120 elsif(operation = 'CREATE_COMB_NO_AT') then
121 dins_flag := 'O';
122 required_flag := 'Y';
123 elsif(operation = 'CHECK_COMBINATION') then
124 invoking_mode := 'P';
125 -- Bug 1414119 - Change dins_flag from 'Y' to 'D'
126 dins_flag := 'D';
127 -- elsif(operation = 'DEFAULT_SEGMENTS') then
128 -- catsegs_in := NULL;
129 -- invoking_mode := 'P';
130 -- dins_flag := 'Y';
131 -- required_flag := 'Y';
132 elsif(operation = 'CHECK_SEGMENTS') then
133 invoking_mode := 'P';
134 if(allow_nulls) then
135 nulls_ok := 'Y';
136 end if;
137 if(allow_orphans) then
138 validate_mode := 'PARTIAL_IF_POSSIBLE';
139 else
140 validate_mode := 'PARTIAL';
141 end if;
142 dins_flag := 'N';
143 else
144 FND_MESSAGE.set_name('FND', 'FLEX-BAD OPERATION');
145 FND_MESSAGE.set_token('FUNCTNAME', operation);
146 goto cleanup_and_return;
147 end if;
148
149 FND_FLEX_SERVER.validation_engine(resp_apid, uresp_id, userid,
150 appl_short_name, key_flex_code,
151 select_comb_from_view, structure_number,
152 validation_date, vrule, data_set, invoking_mode,
153 validate_mode, dins_flag, 'Y', required_flag, nulls_ok,
154 displayable, catsegs_in, values_or_ids, where_clause,
155 no_combmsg, where_clause_msg,
156 get_columns, NULL, nvalidated, value_vals, value_svals, value_ids,
157 value_descs, value_desclens, cc_cols, cc_coltypes, segtypes,
158 disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
159 n_xcol_vals, xcol_vals, delim, ccid, new_comb, valid_stat,
160 segcodes, err_segn);
161
162 -- Set status flags. Secured also set if any segment is secured and there
163 -- is some other error.
164 --
165 sta_valid := (valid_stat = FND_FLEX_SERVER1.VV_VALID);
166 sta_secured := ((valid_stat = FND_FLEX_SERVER1.VV_SECURED) or
167 (INSTR(segcodes, FND_FLEX_SERVER1.FF_VSECURED) > 0));
168 sta_value_err := (valid_stat = FND_FLEX_SERVER1.VV_VALUES);
169 sta_unsupported_err := (valid_stat = FND_FLEX_SERVER1.VV_UNSUPPORTED);
170 sta_serious_err := (valid_stat = FND_FLEX_SERVER1.VV_ERROR);
171
172 -- Get message if not valid.
173 -- Erase only the combination_id, and table columns if just checking
174 -- segments, otherwise erase everything but the status and error message.
175 --
176 <<cleanup_and_return>>
177 if(not sta_valid) then
178 err_msg := FND_MESSAGE.get_encoded;
179 if(sta_unsupported_err or sta_serious_err or
180 (operation not in ('DEFAULT_SEGMENTS', 'CHECK_SEGMENTS'))) then
181 clear_all_but_error;
182 end if;
183 end if;
184 return(sta_valid);
185
186 EXCEPTION
187 WHEN OTHERS then
188 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
189 FND_MESSAGE.set_token('MSG','validate_segs() exception: '||SQLERRM);
190 err_msg := FND_MESSAGE.get_encoded;
191 return(FALSE);
192
193 END validate_segs;
194
195 /* ------------------------------------------------------------------------ */
196
197 FUNCTION validate_ccid(appl_short_name IN VARCHAR2,
198 key_flex_code IN VARCHAR2,
199 structure_number IN NUMBER,
200 combination_id IN NUMBER,
201 displayable IN VARCHAR2 DEFAULT 'ALL',
202 data_set IN NUMBER DEFAULT NULL,
203 vrule IN VARCHAR2 DEFAULT NULL,
204 security IN VARCHAR2 DEFAULT 'IGNORE',
205 get_columns IN VARCHAR2 DEFAULT NULL,
206 resp_appl_id IN NUMBER DEFAULT NULL,
207 resp_id IN NUMBER DEFAULT NULL,
208 user_id IN NUMBER DEFAULT NULL,
209 select_comb_from_view IN VARCHAR2 DEFAULT NULL)
210 RETURN BOOLEAN IS
211 resp_apid NUMBER;
212 uresp_id NUMBER;
213 userid NUMBER;
214 valid_stat NUMBER;
215 n_dispsegs NUMBER;
216 q_security VARCHAR2(1);
217 catvals VARCHAR2(2000);
218
219 BEGIN
220
221 -- Initialize everything including all global variables and set user
222 -- Isvalid is initialized to FALSE, serious_error initialized to TRUE.
223 --
224 if(init_all(resp_appl_id, resp_id, user_id,
225 resp_apid, uresp_id, userid) = FALSE) then
226 goto cleanup_and_return;
227 end if;
228
229 -- Set q_security based on security mode.
230 --
231 if(security = 'IGNORE') then
232 q_security := 'N';
233 elsif (security = 'CHECK') then
234 q_security := 'X';
235 elsif (security = 'ENFORCE') then
236 q_security := 'Y';
237 else
238 FND_MESSAGE.set_name('FND', 'FLEX-BAD SECURITY');
239 goto cleanup_and_return;
240 end if;
241
242 FND_FLEX_SERVER.validation_engine(resp_apid, uresp_id, userid,
243 appl_short_name, key_flex_code,
244 select_comb_from_view, structure_number,
245 NULL, vrule, data_set, 'L', 'FULL', 'N', q_security, 'N', 'N',
246 displayable, NULL, 'V', NULL, NULL, NULL, get_columns,
247 combination_id, nvalidated, value_vals, value_svals, value_ids,
248 value_descs, value_desclens, cc_cols, cc_coltypes, segtypes,
249 disp_segs, derv, tbl_derv, drv_quals, tbl_quals,
250 n_xcol_vals, xcol_vals, delim, ccid, new_comb, valid_stat,
251 segcodes, err_segn);
252
253 -- Set validation status flags.
254 -- Also valid if secured, but not enforcing it.
255 --
256 sta_valid := ((valid_stat = FND_FLEX_SERVER1.VV_VALID) or
257 ((valid_stat = FND_FLEX_SERVER1.VV_SECURED) and
258 (q_security <> 'Y')));
259 sta_secured := ((valid_stat = FND_FLEX_SERVER1.VV_SECURED) or
260 (INSTR(segcodes, FND_FLEX_SERVER1.FF_VSECURED) > 0));
261 sta_value_err := (valid_stat = FND_FLEX_SERVER1.VV_VALUES);
262 sta_unsupported_err := (valid_stat = FND_FLEX_SERVER1.VV_UNSUPPORTED);
263 sta_serious_err := (valid_stat = FND_FLEX_SERVER1.VV_ERROR);
264
265 -- Get message and erase all but error if not valid
266 --
267 <<cleanup_and_return>>
268 if(not sta_valid) then
269 err_msg := FND_MESSAGE.get_encoded;
270 clear_all_but_error;
271 end if;
272 return(sta_valid);
273
274 EXCEPTION
275 WHEN OTHERS then
276 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
277 FND_MESSAGE.set_token('MSG', 'validate_ccid() exception: ' || SQLERRM);
278 err_msg := FND_MESSAGE.get_encoded;
279 return(FALSE);
280
281 END validate_ccid;
282
283 /* ------------------------------------------------------------------------ */
284
285 FUNCTION is_valid RETURN BOOLEAN IS
286 BEGIN
287 return(sta_valid);
288 END is_valid;
289
290 FUNCTION is_secured RETURN BOOLEAN IS
291 BEGIN
292 return(sta_secured);
293 END is_secured;
294
295 FUNCTION value_error RETURN BOOLEAN IS
296 BEGIN
297 return(sta_value_err);
298 END value_error;
299
300 FUNCTION unsupported_error RETURN BOOLEAN IS
301 BEGIN
302 return(sta_unsupported_err);
303 END unsupported_error;
304
305 FUNCTION serious_error RETURN BOOLEAN IS
306 BEGIN
307 return(sta_serious_err);
308 END serious_error;
309
310 FUNCTION new_combination RETURN BOOLEAN IS
311 BEGIN
312 return(new_comb);
313 END new_combination;
314
315 FUNCTION error_segment RETURN NUMBER IS
316 BEGIN
317 return(err_segn);
318 END error_segment;
319
320 FUNCTION error_message RETURN VARCHAR2 IS
321 BEGIN
322 if((err_text is null) and (err_msg is not null)) then
323 FND_MESSAGE.set_encoded(err_msg);
324 err_text := FND_MESSAGE.get;
325 end if;
326 return(err_text);
327 END error_message;
328
329 FUNCTION encoded_error_message RETURN VARCHAR2 IS
330 BEGIN
331 return(err_msg);
332 END encoded_error_message;
333
334
335 FUNCTION combination_id RETURN NUMBER IS
336 BEGIN
337 return(ccid);
338 END combination_id;
339
340 FUNCTION segment_delimiter RETURN VARCHAR2 IS
341 BEGIN
342 return(delim);
343 END segment_delimiter;
344
345
346 FUNCTION concatenated_values RETURN VARCHAR2 IS
347 BEGIN
348 IF (sta_valid) THEN
349 return(FND_FLEX_SERVER.concatenate_values(nvalidated, value_vals,
350 disp_segs, delim));
351 ELSE
352 raise_application_error
353 (-20001, ('Developer Error: KVL.concatenated_values should not ' ||
354 'be called if validation fails.'));
355 END IF;
356 END concatenated_values;
357
358
359 FUNCTION concatenated_ids RETURN VARCHAR2 IS
360 BEGIN
361 IF (sta_valid) THEN
362 return(FND_FLEX_SERVER.concatenate_ids(nvalidated, value_ids, delim));
363 ELSE
364 raise_application_error
365 (-20001, ('Developer Error: KVL.concatenated_ids should not ' ||
366 'be called if validation fails.'));
367 END IF;
368 END concatenated_ids;
369
370
371 FUNCTION concatenated_descriptions RETURN VARCHAR2 IS
372 BEGIN
373 IF (sta_valid) THEN
374 return(FND_FLEX_SERVER.concatenate_descriptions(nvalidated,
375 value_descs, disp_segs, value_desclens, delim));
376 ELSE
377 raise_application_error
378 (-20001, ('Developer Error: KVL.concatenated_descriptions should not '||
379 'be called if validation fails.'));
380 END IF;
381 END concatenated_descriptions;
382
383
384 -- Implement derived_enabled_flag() if needed.
385
386 FUNCTION enabled_flag RETURN BOOLEAN IS
387 BEGIN
388 return((tbl_derv.enabled_flag = 'Y'));
389 END enabled_flag;
390
391
392 FUNCTION summary_flag RETURN BOOLEAN IS
393 BEGIN
394 return((tbl_derv.summary_flag = 'Y'));
395 END summary_flag;
396
397
398 FUNCTION start_date RETURN DATE IS
399 BEGIN
400 return(tbl_derv.start_valid);
401 END start_date;
402
403
404 FUNCTION end_date RETURN DATE IS
405 BEGIN
406 return(tbl_derv.end_valid);
407 END end_date;
408
409
410 FUNCTION segment_count RETURN NUMBER IS
411 BEGIN
415
412 return(nvalidated);
413 END segment_count;
414
416 FUNCTION segment_value(segnum IN NUMBER) RETURN VARCHAR2 IS
417 BEGIN
418 if(segnum between 1 and nvalidated) then
419 return(value_vals(segnum));
420 end if;
421 return(NULL);
422 END segment_value;
423
424
425 FUNCTION segment_id(segnum IN NUMBER) RETURN VARCHAR2 IS
426 BEGIN
427 if(segnum between 1 and nvalidated) then
428 return(value_ids(segnum));
429 end if;
430 return(NULL);
431 END segment_id;
432
433
434 FUNCTION segment_description(segnum IN NUMBER) RETURN VARCHAR2 IS
435 BEGIN
436 if(segnum between 1 and nvalidated) then
437 return(value_descs(segnum));
438 end if;
439 return(NULL);
440 END segment_description;
441
442
443 FUNCTION segment_concat_desc_length(segnum IN NUMBER) RETURN NUMBER IS
444 BEGIN
445 if(segnum between 1 and nvalidated) then
446 return(value_desclens(segnum));
447 end if;
448 return(0);
449 END segment_concat_desc_length;
450
451
452 FUNCTION segment_displayed(segnum IN NUMBER) RETURN BOOLEAN IS
453 BEGIN
454 if(segnum between 1 and disp_segs.n_segflags) then
455 return(disp_segs.segflags(segnum));
456 end if;
457 return(FALSE);
458 END segment_displayed;
459
460
461 FUNCTION segment_valid(segnum IN NUMBER) RETURN BOOLEAN IS
462 BEGIN
463 if((segcodes is not null) and (segnum between 1 and LENGTH(segcodes))) then
464 return(SUBSTR(segcodes, segnum, 1) = FND_FLEX_SERVER1.FF_VVALID);
465 end if;
466 return(FALSE);
467 END segment_valid;
468
469
470 FUNCTION segment_column_name(segnum IN NUMBER) RETURN VARCHAR2 IS
471 BEGIN
472 if(segnum between 1 and nvalidated) then
473 return(cc_cols(segnum));
474 end if;
475 return(NULL);
476 END segment_column_name;
477
478 -- Returns segment column type as 'VARCHAR2', 'NUMBER' or 'DATE'
479 -- or returns NULL if unknown type of segment index out of range.
480 --
481 FUNCTION segment_column_type(segnum IN NUMBER) RETURN VARCHAR2 IS
482 type_code VARCHAR2(1);
483 BEGIN
484 if(segnum between 1 and nvalidated) then
485 type_code := cc_coltypes(segnum);
486 if(type_code = 'V') then
487 return('VARCHAR2');
488 elsif(type_code = 'N') then
489 return('NUMBER');
490 elsif(type_code = 'D') then
491 return('DATE');
492 else
493 return(NULL);
494 end if;
495 end if;
496 return(NULL);
497 END segment_column_type;
498
499 FUNCTION column_count RETURN NUMBER IS
500 BEGIN
501 return(n_xcol_vals);
502 END column_count;
503
504
505 FUNCTION column_value(colnum IN NUMBER) RETURN VARCHAR2 IS
506 BEGIN
507 if(colnum between 1 and n_xcol_vals) then
508 return(xcol_vals(colnum));
509 end if;
510 return(NULL);
511 END column_value;
512
513
514 FUNCTION qualifier_value(segqual_name IN VARCHAR2,
515 table_or_derived IN VARCHAR2 DEFAULT 'D')
516 RETURN VARCHAR2 IS
517 qual_val VARCHAR2(2000);
518 sq_name VARCHAR2(30);
519 BEGIN
520 sq_name := SUBSTRB(UPPER(segqual_name), 1, 30);
521 if(table_or_derived = 'T') then
522 for i in 1..tbl_quals.nquals loop
523 if(tbl_quals.sq_names(i) = sq_name) then
524 qual_val := tbl_quals.sq_values(i);
525 exit;
526 end if;
527 end loop;
528 else
529 for i in 1..drv_quals.nquals loop
530 if(drv_quals.sq_names(i) = sq_name) then
531 qual_val := drv_quals.sq_values(i);
532 exit;
533 end if;
534 end loop;
535 end if;
536 return(qual_val);
537 END qualifier_value;
538
539 /* ------------------------------------------------------------------------ */
540 /* PRIVATE FUNCTIONS */
541 /* ------------------------------------------------------------------------ */
542
543 /* ------------------------------------------------------------------------ */
544 /* Initializes global variables, status, and determines user. */
545 /* Returns TRUE on success or FALSE and sets error message on failure. */
546 /* ------------------------------------------------------------------------ */
547
548 FUNCTION init_all(p_resp_appl_id IN NUMBER,
549 p_resp_id IN NUMBER,
550 p_user_id IN NUMBER,
551 x_resp_appl_id OUT nocopy NUMBER,
552 x_resp_id OUT nocopy NUMBER,
553 x_user_id OUT nocopy NUMBER)
554 RETURN BOOLEAN
555 IS
556 BEGIN
557 --
558 -- Initialize messages, debugging, and number of sql strings
559 --
560 if(FND_FLEX_SERVER1.init_globals = FALSE) then
561 return(FALSE);
562 end if;
563
564 --
565 -- Default security settings, if null.
566 --
567 x_resp_appl_id := Nvl(p_resp_appl_id, fnd_global.resp_appl_id());
568 x_resp_id := Nvl(p_resp_id, fnd_global.resp_id());
569 x_user_id := Nvl(p_user_id, fnd_global.user_id());
570
571 --
572 -- Initialize status codes
573 --
574 sta_valid := FALSE;
575 sta_secured := FALSE;
576 sta_value_err := FALSE;
577 sta_unsupported_err := FALSE;
578 sta_serious_err := TRUE;
579 new_comb := FALSE;
580
581 --
582 -- Initialize other globals
583 --
584 err_segn := NULL;
585 err_msg := NULL;
586 err_text := NULL;
587 clear_all_but_error;
588
589 return(TRUE);
590
591 EXCEPTION
592 WHEN OTHERS then
593 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
594 FND_MESSAGE.set_token('MSG','init_all() exception: ' || SQLERRM);
595 return(FALSE);
596
597 END init_all;
598
599 /* ------------------------------------------------------------------------ */
600 /* Clears everything associated with a combination that is not */
601 /* associated with individual segments. */
602 /* ------------------------------------------------------------------------ */
603
604 PROCEDURE clear_combination_globals IS
605 BEGIN
606 n_xcol_vals := 0;
607 ccid := NULL;
608 tbl_quals.nquals := 0;
609
610 END clear_combination_globals;
611
612 /* ------------------------------------------------------------------------ */
613 /* Clears everything except for the status codes and error messages. */
614 /* ------------------------------------------------------------------------ */
615
616 PROCEDURE clear_all_but_error IS
617 BEGIN
618
619 -- Setting array counts to 0 initializes arrays
620 --
621 nvalidated := 0;
622 segtypes.nsegs := 0;
623 disp_segs.n_segflags := 0;
624 derv.enabled_flag := NULL;
625 derv.summary_flag := NULL;
626 derv.start_valid := NULL;
627 derv.end_valid := NULL;
628 tbl_derv := derv;
629 drv_quals.nquals := 0;
630 tbl_quals.nquals := 0;
631 n_xcol_vals := 0;
632 ccid := NULL;
633 delim := NULL;
634 segcodes := NULL;
635
636 END clear_all_but_error;
637
638 /* ------------------------------------------------------------------------ */
639
640 END fnd_flex_keyval;