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