1 PACKAGE BODY fnd_flex_descval AS
2 /* $Header: AFFFDVLB.pls 120.4.12020000.2 2012/07/19 00:31:39 hgeorgi ship $ */
3
4 --
5 -- PRIVATE CONSTANTS
6 --
7
8 --
9 -- PRIVATE FUNCTIONS
10 --
11
12 FUNCTION init_all(p_resp_appl_id IN NUMBER,
13 p_resp_id IN NUMBER,
14 p_user_id IN NUMBER,
15 x_resp_appl_id OUT nocopy NUMBER,
19
16 x_resp_id OUT nocopy NUMBER,
17 x_user_id OUT nocopy NUMBER)
18 RETURN BOOLEAN;
20 PROCEDURE clear_all_but_error;
21
22 PROCEDURE set_stati(v_stat IN NUMBER);
23
24 FUNCTION return_status
25 RETURN BOOLEAN;
26
27 PROCEDURE add_column_value(column_name IN VARCHAR2,
28 column_value IN VARCHAR2,
29 column_type IN VARCHAR2);
30
31 FUNCTION check_api_mode(values_or_ids IN VARCHAR2)
32 RETURN VARCHAR2;
33
34 FUNCTION get_default_context(p_application_short_name IN VARCHAR2,
35 p_descriptive_flexfield_name IN VARCHAR2)
36 RETURN VARCHAR2;
37
38 FUNCTION get_ref_field_context(p_application_short_name IN VARCHAR2,
39 p_descriptive_flexfield_name IN VARCHAR2)
40 RETURN VARCHAR2;
41
42
43 /* ------------------------------------------------------------------------ */
44 /* The following functions use ROWID and so were not released. The */
45 /* FND_FLEX_SERVER4.descval_engine() supports this functionality and was */
46 /* tested, but these cover functions were not. */
47 /* ------------------------------------------------------------------------ */
48
49 /* ------------------------------------------------------------------------ */
50 /* LOAD_DESC(): */
51 /* Retrieves the descriptive flexfield information in an existing row */
52 /* specified by row_id. If data_field is not null, the descriptive */
53 /* flexfield data is read from that column rather than the individual */
54 /* segment columns. If interface_table is not null, the row is */
55 /* retrieved from that table rather than the table upon which the */
56 /* descriptive flexfield is defined. Note that it is an error if the */
57 /* interface table does not contain segment and context value columns */
58 /* of the same names as those in the table upon which the descriptive */
59 /* flexfield is defined. Also note it is an error if the specified */
60 /* row does not exist, or if the specified data column does not exist */
61 /* in the appropriate table. */
62 /* */
63 /* Load_desc() is designed to retrieve existing valid data. It does */
64 /* not return an error for values that are disabled, expired, or not */
65 /* allowed to be seen by the current user due to value security rules. */
66 /* */
67 /* Returns TRUE if all segments of the descriptive flexfield are */
68 /* valid. Otherwise returns FALSE and sets the validation status */
69 /* codes to indicate the detailed nature of the error. */
70 /* If this function returns TRUE, the segment information for the */
71 /* most recently validated flexfield can be retrieved using the data */
72 /* retrieval functions. If the function returns FALSE, the segment */
73 /* data will be null, but the error message can be read. */
74 /* ------------------------------------------------------------------------ */
75
76 FUNCTION load_desc(appl_short_name IN VARCHAR2,
77 desc_flex_name IN VARCHAR2,
78 row_id IN ROWID,
79 data_field IN VARCHAR2 DEFAULT NULL,
80 interface_table IN VARCHAR2 DEFAULT NULL)
81 RETURN BOOLEAN;
82
83
84 /* ------------------------------------------------------------------------ */
85 /* CHECK_DESC(): */
86 /* Checks that the descriptive flexfield information in an existing */
87 /* row is valid. This function is designed to be used to verify that */
88 /* newly inserted descriptive flexfield information is correct. It */
89 /* will indicate an error if any of the values are invalid, disabled, */
90 /* expired or not available for the current user because of value */
91 /* security rules. */
92 /* */
93 /* Returns TRUE if all segments of the descriptive flexfield are */
94 /* valid. otherwise returns FALSE and sets the validation status */
95 /* codes to indicate the detailed nature of the error. */
96 /* If this function returns TRUE, the segment information for the */
97 /* most recently validated flexfield can be retrieved using the data */
98 /* retrieval functions. If the function returns FALSE, the segment */
99 /* data will be null, but the error message can be read. */
100 /* */
101 /* ARGUMENTS: */
102 /* =========== */
103 /* The descriptive flexfield is identified by appl_short_name and */
104 /* desc_flex_name. Row_id specifies the row that is to be checked. */
105 /* Ordinarily the row is in the table upon which the descriptive */
106 /* flexfield is defined. If data_field is not null, the descriptive */
107 /* flexfield information in the form of concatenated segment ids is */
108 /* read from that column rather than from the individual segment */
109 /* columns. If interface_table is not null, the row is retrieved from */
110 /* that table rather than from the table upon which the descriptive */
111 /* flexfield is defined. This allows the use of this function with */
112 /* an interface table where the descriptive flexfield information is */
113 /* validated on the interface table before being inserted into the */
114 /* production table. The interface table must contain segment and */
115 /* context value columns with the same names as those in the table */
116 /* upon which the descriptive flexfield is defined. Also note it is */
117 /* an error if the specified row does not exist, or if the specified */
118 /* data column does not exist in the appropriate table. */
119 /* The validation date is used to check whether the values are */
120 /* active for that date. The resp_appl_id, and resp_id arguments */
121 /* identify the user for the purposes of value security rules. */
122 /* If these are not specified the values from FND_GLOBAL will */
123 /* be used. The FND_GLOBAL values are set by the form or by */
124 /* the concurrent program that starts this database session. */
125 /* The enabled_activation flag is for internal use only. It is not */
126 /* supported outside of the Application Object Library. */
127 /* ------------------------------------------------------------------------ */
128
129 FUNCTION check_desc(appl_short_name IN VARCHAR2,
130 desc_flex_name IN VARCHAR2,
131 row_id IN ROWID,
132 data_field IN VARCHAR2 DEFAULT NULL,
133 interface_table IN VARCHAR2 DEFAULT NULL,
134 validation_date IN DATE DEFAULT SYSDATE,
135 enabled_activation IN BOOLEAN DEFAULT TRUE,
136 resp_appl_id IN NUMBER DEFAULT NULL,
137 resp_id IN NUMBER DEFAULT NULL)
138 RETURN BOOLEAN;
139
140 /* ------------------------------------------------------------------------ */
141
142 -- PRIVATE GLOBAL VARIABLES
143 --
144
145 nvalidated NUMBER;
146 value_vals FND_FLEX_SERVER1.ValueArray;
147 value_svals FND_FLEX_SERVER1.ValueArray;
148 value_ids FND_FLEX_SERVER1.ValueIdArray;
149 value_descs FND_FLEX_SERVER1.ValueDescArray;
150 value_desclens FND_FLEX_SERVER1.NumberArray;
154 disp_segs FND_FLEX_SERVER1.DisplayedSegs;
151 seg_cols FND_FLEX_SERVER1.TabColArray;
152 seg_coltypes FND_FLEX_SERVER1.CharArray;
153 segtypes FND_FLEX_SERVER1.SegFormats;
155 delim VARCHAR2(1);
156 err_segn NUMBER;
157 err_msg VARCHAR2(2000);
158 err_text VARCHAR2(2000);
159 segcodes VARCHAR2(201);
160
161 -- Return statuses
162 --
163 sta_valid BOOLEAN;
164 sta_secured BOOLEAN;
165 sta_value_err BOOLEAN;
166 sta_unsupported_err BOOLEAN;
167 sta_serious_err BOOLEAN;
168
169 -- Segment column names and values input
170 --
171 g_coldef FND_FLEX_SERVER1.ColumnDefinitions;
172
173 /* ----------------------------------------------------------------------- */
174 /* THIS PACKAGE IS STILL UNDER DEVELOPMENT */
175 /* The functions herein are not be supported in any way and will */
176 /* change without notice. */
177 /* ----------------------------------------------------------------------- */
178
179 /* ------------------------------------------------------------------------ */
180 /* SEE PACKAGE SPECIFICATION FOR DESCRIPTION OF PUBLIC FUNCTIONS. */
181 /* ------------------------------------------------------------------------ */
182
183 FUNCTION load_desc(appl_short_name IN VARCHAR2,
184 desc_flex_name IN VARCHAR2,
185 row_id IN ROWID,
186 data_field IN VARCHAR2 DEFAULT NULL,
187 interface_table IN VARCHAR2 DEFAULT NULL)
188 RETURN BOOLEAN IS
189 resp_apid NUMBER;
190 uresp_id NUMBER;
191 userid NUMBER;
192 valid_stat NUMBER;
193
194 dummy_coldef FND_FLEX_SERVER1.ColumnDefinitions;
195
196 BEGIN
197
198 -- Initialize everything including all global variables and set user
199 -- Isvalid is initialized to FALSE, serious_error initialized to TRUE.
200 --
201 if(init_all(NULL, NULL, NULL, resp_apid, uresp_id, userid)) then
202 FND_FLEX_SERVER4.descval_engine(user_apid => resp_apid,
203 user_resp => uresp_id,
204 userid => userid,
205 flex_app_sname => appl_short_name,
206 desc_flex_name => desc_flex_name,
207 val_date => NULL,
208 invoking_mode => 'L',
209 allow_nulls => FALSE,
210 update_table => FALSE,
211 ignore_active => FALSE,
212 concat_segs => NULL,
213 vals_not_ids => FALSE,
214 use_column_def => FALSE,
215 column_def => dummy_coldef,
216 rowid_in => row_id,
217 alt_tbl_name => interface_table,
218 data_field_name => data_field,
219 nvalidated => nvalidated,
220 displayed_vals => value_vals,
221 stored_vals => value_svals,
222 segment_ids => value_ids,
223 descriptions => value_descs,
224 desc_lengths => value_desclens,
225 seg_colnames => seg_cols,
226 seg_coltypes => seg_coltypes,
227 segment_types => segtypes,
228 displayed_segs => disp_segs,
229 seg_delimiter => delim,
230 v_status => valid_stat,
231 seg_codes => segcodes,
232 err_segnum => err_segn);
233 set_stati(valid_stat);
234 end if;
235 return(return_status);
236
237 EXCEPTION
238 WHEN OTHERS then
239 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
240 FND_MESSAGE.set_token('MSG', 'load_desc() exception: ' || SQLERRM);
241 err_msg := FND_MESSAGE.get_encoded;
242 return(FALSE);
243
244 END load_desc;
245
246 /* ------------------------------------------------------------------------ */
247
248 FUNCTION check_desc(appl_short_name IN VARCHAR2,
249 desc_flex_name IN VARCHAR2,
250 row_id IN ROWID,
251 data_field IN VARCHAR2 DEFAULT NULL,
252 interface_table IN VARCHAR2 DEFAULT NULL,
253 validation_date IN DATE DEFAULT SYSDATE,
254 enabled_activation IN BOOLEAN DEFAULT TRUE,
255 resp_appl_id IN NUMBER DEFAULT NULL,
256 resp_id IN NUMBER DEFAULT NULL)
257 RETURN BOOLEAN IS
258 resp_apid NUMBER;
259 uresp_id NUMBER;
260 userid NUMBER;
261 valid_stat NUMBER;
262
263 dummy_coldef FND_FLEX_SERVER1.ColumnDefinitions;
264
265 BEGIN
266
267 -- Initialize everything including all global variables and set user
268 -- Isvalid is initialized to FALSE, serious_error initialized to TRUE.
269 --
273 user_resp => uresp_id,
270 if(init_all(resp_appl_id, resp_id, NULL,
271 resp_apid, uresp_id, userid)) then
272 FND_FLEX_SERVER4.descval_engine(user_apid => resp_apid,
274 userid => userid,
275 flex_app_sname => appl_short_name,
276 desc_flex_name => desc_flex_name,
277 val_date => validation_date,
278 invoking_mode => 'C',
279 allow_nulls => FALSE,
280 update_table => FALSE,
281 ignore_active => (not enabled_activation),
282 concat_segs => NULL,
283 vals_not_ids => FALSE,
284 use_column_def => FALSE,
285 column_def => dummy_coldef,
286 rowid_in => row_id,
287 alt_tbl_name => interface_table,
288 data_field_name => data_field,
289 nvalidated => nvalidated,
290 displayed_vals => value_vals,
291 stored_vals => value_svals,
292 segment_ids => value_ids,
293 descriptions => value_descs,
294 desc_lengths => value_desclens,
295 seg_colnames => seg_cols,
296 seg_coltypes => seg_coltypes,
297 segment_types => segtypes,
298 displayed_segs => disp_segs,
299 seg_delimiter => delim,
300 v_status => valid_stat,
301 seg_codes => segcodes,
302 err_segnum => err_segn);
303 set_stati(valid_stat);
304 end if;
305 return(return_status);
306
307 EXCEPTION
308 WHEN OTHERS then
309 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
310 FND_MESSAGE.set_token('MSG', 'check_desc() exception: ' || SQLERRM);
311 err_msg := FND_MESSAGE.get_encoded;
312 return(FALSE);
313
314 END check_desc;
315
316 /* ------------------------------------------------------------------------ */
317
318 FUNCTION val_desc(appl_short_name IN VARCHAR2,
319 desc_flex_name IN VARCHAR2,
320 concat_segments IN VARCHAR2,
321 values_or_ids IN VARCHAR2 DEFAULT 'I',
322 validation_date IN DATE DEFAULT SYSDATE,
323 enabled_activation IN BOOLEAN DEFAULT TRUE,
324 resp_appl_id IN NUMBER DEFAULT NULL,
325 resp_id IN NUMBER DEFAULT NULL)
326 RETURN BOOLEAN IS
327 resp_apid NUMBER;
328 uresp_id NUMBER;
329 userid NUMBER;
330 valid_stat NUMBER;
331
332 api_mode VARCHAR2(1);
333
334 dummy_coldef FND_FLEX_SERVER1.ColumnDefinitions;
335
336 BEGIN
337
338 api_mode := check_api_mode(values_or_ids);
339
340 -- Initialize everything including all global variables and set user
341 -- Isvalid is initialized to FALSE, serious_error initialized to TRUE.
342 --
343
344 if(init_all(resp_appl_id, resp_id, NULL,
345 resp_apid, uresp_id, userid)) then
346 FND_FLEX_SERVER4.descval_engine(user_apid => resp_apid,
347 user_resp => uresp_id,
348 userid => userid,
349 flex_app_sname => appl_short_name,
350 desc_flex_name => desc_flex_name,
351 val_date => validation_date,
352 invoking_mode => api_mode,
353 allow_nulls => FALSE,
354 update_table => FALSE,
355 ignore_active => (not enabled_activation),
356 concat_segs => concat_segments,
357 vals_not_ids => (values_or_ids = 'V'),
358 use_column_def => FALSE,
359 column_def => dummy_coldef,
360 rowid_in => NULL,
361 alt_tbl_name => NULL,
362 data_field_name => NULL,
363 nvalidated => nvalidated,
364 displayed_vals => value_vals,
365 stored_vals => value_svals,
366 segment_ids => value_ids,
367 descriptions => value_descs,
368 desc_lengths => value_desclens,
369 seg_colnames => seg_cols,
370 seg_coltypes => seg_coltypes,
371 segment_types => segtypes,
372 displayed_segs => disp_segs,
373 seg_delimiter => delim,
374 v_status => valid_stat,
375 seg_codes => segcodes,
379 return(return_status);
376 err_segnum => err_segn);
377 set_stati(valid_stat);
378 end if;
380
381 EXCEPTION
382 WHEN OTHERS then
383 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
384 FND_MESSAGE.set_token('MSG', 'val_desc() exception: ' || SQLERRM);
385 err_msg := FND_MESSAGE.get_encoded;
386 return(FALSE);
387
388 END val_desc;
389
390 /* ------------------------------------------------------------------------ */
391
392 FUNCTION validate_desccols(appl_short_name IN VARCHAR2,
393 desc_flex_name IN VARCHAR2,
394 values_or_ids IN VARCHAR2 DEFAULT 'I',
395 validation_date IN DATE DEFAULT SYSDATE,
396 enabled_activation IN BOOLEAN DEFAULT TRUE,
397 resp_appl_id IN NUMBER DEFAULT NULL,
398 resp_id IN NUMBER DEFAULT NULL)
399 RETURN BOOLEAN IS
400 resp_apid NUMBER;
401 uresp_id NUMBER;
402 userid NUMBER;
403 valid_stat NUMBER;
404 api_mode VARCHAR2(1);
405 l_default_context_value VARCHAR2(2000);
406
407 BEGIN
408
409 api_mode := check_api_mode(values_or_ids);
410
411 -- Initialize everything including all global variables and set user
412 -- Isvalid is initialized to FALSE, serious_error initialized to TRUE.
413 --
414 if(init_all(resp_appl_id, resp_id, NULL,
415 resp_apid, uresp_id, userid)) then
416
417 if (g_coldef.context_value is null) then
418 if (api_mode = 'D') then
419 l_default_context_value := get_ref_field_context(appl_short_name,
420 desc_flex_name);
421 else
422 l_default_context_value := get_default_context(appl_short_name,
423 desc_flex_name);
424 end if;
425
426 if (l_default_context_value is NOT NULL) then
427 g_coldef.context_value_set := TRUE;
428 g_coldef.context_value := SUBSTRB(l_default_context_value, 1, 30);
429 end if;
430 end if;
431
432 FND_FLEX_SERVER4.descval_engine(user_apid => resp_apid,
433 user_resp => uresp_id,
434 userid => userid,
435 flex_app_sname => appl_short_name,
436 desc_flex_name => desc_flex_name,
437 val_date => validation_date,
438 invoking_mode => api_mode,
439 allow_nulls => FALSE,
440 update_table => FALSE,
441 ignore_active => (not enabled_activation),
442 concat_segs => NULL,
443 vals_not_ids => (values_or_ids = 'V'),
444 use_column_def => TRUE,
445 column_def => g_coldef,
446 rowid_in => NULL,
447 alt_tbl_name => NULL,
448 data_field_name => NULL,
449 nvalidated => nvalidated,
450 displayed_vals => value_vals,
451 stored_vals => value_svals,
452 segment_ids => value_ids,
453 descriptions => value_descs,
454 desc_lengths => value_desclens,
455 seg_colnames => seg_cols,
456 seg_coltypes => seg_coltypes,
457 segment_types => segtypes,
458 displayed_segs => disp_segs,
459 seg_delimiter => delim,
460 v_status => valid_stat,
461 seg_codes => segcodes,
462 err_segnum => err_segn);
463 set_stati(valid_stat);
464
465 end if;
466 return(return_status);
467
468 EXCEPTION
469 WHEN OTHERS then
470 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
471 FND_MESSAGE.set_token('MSG', 'validate_desccols() exception: '||SQLERRM);
472 err_msg := FND_MESSAGE.get_encoded;
473 return(FALSE);
474
475 END validate_desccols;
476
477 /* ------------------------------------------------------------------------ */
478
479 PROCEDURE set_context_value(context_value IN VARCHAR2) IS
480 BEGIN
481 g_coldef.context_value := SUBSTRB(context_value, 1, 80);
482 g_coldef.context_value_set := TRUE;
483 END set_context_value;
484
485 /* ------------------------------------------------------------------------ */
486
487 PROCEDURE set_column_value(column_name IN VARCHAR2,
488 column_value IN VARCHAR2) IS
489 BEGIN
490 add_column_value(column_name, column_value, 'V');
491 END set_column_value;
492
493 /* ------------------------------------------------------------------------ */
494 /* Sets number column value converting the number to the character */
495 /* representation appropriate for flexfields. */
496 /* ------------------------------------------------------------------------ */
497
498 PROCEDURE set_column_value(column_name IN VARCHAR2,
502 END set_column_value;
499 column_value IN NUMBER) IS
500 BEGIN
501 add_column_value(column_name, to_char(column_value), 'N');
503
504 /* ------------------------------------------------------------------------ */
505 /* Sets date column value converting the date to the character */
506 /* representation appropriate for flexfields. */
507 /* ------------------------------------------------------------------------ */
508
509 PROCEDURE set_column_value(column_name IN VARCHAR2,
510 column_value IN DATE) IS
511 BEGIN
512 add_column_value(column_name,
513 to_char(column_value, FND_FLEX_SERVER1.DATETIME_FMT), 'D');
514 END set_column_value;
515
516 /* ------------------------------------------------------------------------ */
517 /* Clears all defined column values. Column values are also cleared */
518 /* after validation by val_desc() or validate_desccols(). */
519 /* ------------------------------------------------------------------------ */
520
521 PROCEDURE clear_column_values IS
522 BEGIN
523 FND_FLEX_SERVER4.init_coldef(g_coldef);
524 END clear_column_values;
525
526 /* ------------------------------------------------------------------------ */
527 /* Functions for getting more details about the most recently */
528 /* validated combination. These typically do not trap errors */
529 /* related to the user not leaving enough room in destination */
530 /* strings to store the result. */
531 /* ------------------------------------------------------------------------ */
532
533 FUNCTION is_valid RETURN BOOLEAN IS
534 BEGIN
535 return(sta_valid);
536 END is_valid;
537
538 FUNCTION is_secured RETURN BOOLEAN IS
539 BEGIN
540 return(sta_secured);
541 END is_secured;
542
543 FUNCTION value_error RETURN BOOLEAN IS
544 BEGIN
545 return(sta_value_err);
546 END value_error;
547
548 FUNCTION unsupported_error RETURN BOOLEAN IS
549 BEGIN
550 return(sta_unsupported_err);
551 END unsupported_error;
552
553 FUNCTION serious_error RETURN BOOLEAN IS
554 BEGIN
555 return(sta_serious_err);
556 END serious_error;
557
558 FUNCTION error_segment RETURN NUMBER IS
559 BEGIN
560 return(err_segn);
561 END error_segment;
562
563 FUNCTION error_message RETURN VARCHAR2 IS
564 BEGIN
565 if((err_text is null) and (err_msg is not null)) then
566 FND_MESSAGE.set_encoded(err_msg);
567 err_text := FND_MESSAGE.get;
568 end if;
569 return(err_text);
570 END error_message;
571
572 FUNCTION encoded_error_message RETURN VARCHAR2 IS
573 BEGIN
574 return(err_msg);
575 END encoded_error_message;
576
577
578 FUNCTION segment_delimiter RETURN VARCHAR2 IS
579 BEGIN
580 return(delim);
581 END segment_delimiter;
582
583
584 FUNCTION concatenated_values RETURN VARCHAR2 IS
585 BEGIN
586 IF (sta_valid) THEN
587 return(FND_FLEX_SERVER.concatenate_values(nvalidated, value_vals,
588 disp_segs, delim));
589 ELSE
590 raise_application_error
591 (-20001, ('Developer Error: DVL.concatenated_values should not ' ||
592 'be called if validation fails.'));
593 END IF;
594 END concatenated_values;
595
596
597 FUNCTION concatenated_ids RETURN VARCHAR2 IS
598 BEGIN
599 IF (sta_valid) THEN
600 return(FND_FLEX_SERVER.concatenate_ids(nvalidated, value_ids, delim));
601 ELSE
602 raise_application_error
603 (-20001, ('Developer Error: DVL.concatenated_ids should not ' ||
604 'be called if validation fails.'));
605 END IF;
606 END concatenated_ids;
607
608
609 FUNCTION concatenated_descriptions RETURN VARCHAR2 IS
610 BEGIN
611 IF (sta_valid) THEN
612 return(FND_FLEX_SERVER.concatenate_descriptions(nvalidated,
613 value_descs, disp_segs, value_desclens, delim));
614 ELSE
615 raise_application_error
616 (-20001, ('Developer Error: DVL.concatenated_descriptions should not '||
617 'be called if validation fails.'));
618 END IF;
619 END concatenated_descriptions;
620
621
622 FUNCTION segment_count RETURN NUMBER IS
623 BEGIN
624 return(nvalidated);
625 END segment_count;
626
627
628 FUNCTION segment_value(segnum IN NUMBER) RETURN VARCHAR2 IS
629 BEGIN
630 if(segnum between 1 and nvalidated) then
631 return(value_vals(segnum));
632 end if;
633 return(NULL);
634 END segment_value;
635
636
637 FUNCTION segment_id(segnum IN NUMBER) RETURN VARCHAR2 IS
638 BEGIN
639 if(segnum between 1 and nvalidated) then
640 return(value_ids(segnum));
641 end if;
642 return(NULL);
643 END segment_id;
644
645
646 FUNCTION segment_description(segnum IN NUMBER) RETURN VARCHAR2 IS
647 BEGIN
648 if(segnum between 1 and nvalidated) then
649 return(value_descs(segnum));
650 end if;
651 return(NULL);
652 END segment_description;
653
654
655 FUNCTION segment_concat_desc_length(segnum IN NUMBER) RETURN NUMBER IS
656 BEGIN
657 if(segnum between 1 and nvalidated) then
658 return(value_desclens(segnum));
659 end if;
660 return(0);
661 END segment_concat_desc_length;
662
663
664 FUNCTION segment_displayed(segnum IN NUMBER) RETURN BOOLEAN IS
665 BEGIN
669 return(FALSE);
666 if(segnum between 1 and disp_segs.n_segflags) then
667 return(disp_segs.segflags(segnum));
668 end if;
670 END segment_displayed;
671
672
673 FUNCTION segment_valid(segnum IN NUMBER) RETURN BOOLEAN IS
674 BEGIN
675 if((segcodes is not null) and (segnum between 1 and LENGTH(segcodes))) then
676 return(SUBSTR(segcodes, segnum, 1) = FND_FLEX_SERVER1.FF_VVALID);
677 end if;
678 return(FALSE);
679 END segment_valid;
680
681
682 FUNCTION segment_column_name(segnum IN NUMBER) RETURN VARCHAR2 IS
683 BEGIN
684 if(segnum between 1 and nvalidated) then
685 return(seg_cols(segnum));
686 end if;
687 return(NULL);
688 END segment_column_name;
689
690 -- Returns segment column type as 'VARCHAR2', 'NUMBER' or 'DATE'
691 -- or returns NULL if unknown type of segment index out of range.
692 --
693 FUNCTION segment_column_type(segnum IN NUMBER) RETURN VARCHAR2 IS
694 type_code VARCHAR2(1);
695 BEGIN
696 if(segnum between 1 and nvalidated) then
697 type_code := seg_coltypes(segnum);
698 if(type_code = 'V') then
699 return('VARCHAR2');
700 elsif(type_code = 'N') then
701 return('NUMBER');
702 elsif(type_code = 'D') then
703 return('DATE');
704 else
705 return(NULL);
706 end if;
707 end if;
708 return(NULL);
709 END segment_column_type;
710
711
712 /* ------------------------------------------------------------------------ */
713 /* PRIVATE FUNCTIONS */
714 /* ------------------------------------------------------------------------ */
715
716 /* ------------------------------------------------------------------------ */
717 /* Initializes global variables, status, and determines user. */
718 /* Returns TRUE on success or FALSE and sets error message on failure. */
719 /* ------------------------------------------------------------------------ */
720
721 FUNCTION init_all(p_resp_appl_id IN NUMBER,
722 p_resp_id IN NUMBER,
723 p_user_id IN NUMBER,
724 x_resp_appl_id OUT nocopy NUMBER,
725 x_resp_id OUT nocopy NUMBER,
726 x_user_id OUT nocopy NUMBER)
727 RETURN BOOLEAN
728 IS
729 BEGIN
730 --
731 -- Initialize messages, debugging, and number of sql strings
732 --
733 if(FND_FLEX_SERVER1.init_globals = FALSE) then
734 return(FALSE);
735 end if;
736
737 --
738 -- Default security settings, if null.
739 --
740 x_resp_appl_id := Nvl(p_resp_appl_id, fnd_global.resp_appl_id());
741 x_resp_id := Nvl(p_resp_id, fnd_global.resp_id());
742 x_user_id := Nvl(p_user_id, fnd_global.user_id());
743
744 --
745 -- Initialize status codes
746 --
747 sta_valid := FALSE;
748 sta_secured := FALSE;
749 sta_value_err := FALSE;
750 sta_unsupported_err := FALSE;
751 sta_serious_err := TRUE;
752
753
754 --
755 -- Initialize other globals
756 --
757 err_segn := NULL;
758 err_msg := NULL;
759 err_text := NULL;
760 clear_all_but_error;
761
762 return(TRUE);
763
764 EXCEPTION
765 WHEN OTHERS then
766 FND_MESSAGE.set_name('FND', 'FLEX-SSV EXCEPTION');
767 FND_MESSAGE.set_token('MSG','init_all() exception: ' || SQLERRM);
768 return(FALSE);
769 END init_all;
770
771 /* ------------------------------------------------------------------------ */
772 /* Clears all output variables except for the status codes and */
773 /* error messages. Does not clear input column definitions. */
774 /* ------------------------------------------------------------------------ */
775
776 PROCEDURE clear_all_but_error IS
777 BEGIN
778
779 -- Setting array counts to 0 initializes arrays
780 --
781 nvalidated := 0;
782 segtypes.nsegs := 0;
783 disp_segs.n_segflags := 0;
784 delim := NULL;
785 segcodes := NULL;
786
787 END clear_all_but_error;
788
789 /* ------------------------------------------------------------------------ */
790 /* Set status flags and clears input arguments. */
791 /* Secured also set if any segment is secured and there is some */
792 /* other error. */
793 /* ------------------------------------------------------------------------ */
794
795 PROCEDURE set_stati(v_stat IN NUMBER) IS
796 BEGIN
797 sta_valid := (v_stat = FND_FLEX_SERVER1.VV_VALID);
798 sta_secured := ((v_stat = FND_FLEX_SERVER1.VV_SECURED) or
799 (INSTR(segcodes, FND_FLEX_SERVER1.FF_VSECURED) > 0));
800 sta_value_err := (v_stat = FND_FLEX_SERVER1.VV_VALUES);
801 sta_unsupported_err := (v_stat = FND_FLEX_SERVER1.VV_UNSUPPORTED);
802 sta_serious_err := (v_stat = FND_FLEX_SERVER1.VV_ERROR);
803
804 -- Clear column definitions
805 --
806 FND_FLEX_SERVER4.init_coldef(g_coldef);
807
808 END set_stati;
809
810 /* ------------------------------------------------------------------------ */
811 /* Gets message and erase all but error if not valid. */
812 /* ------------------------------------------------------------------------ */
813
814 FUNCTION return_status RETURN BOOLEAN IS
815 BEGIN
816 if(not sta_valid) then
817 err_msg := FND_MESSAGE.get_encoded;
818 clear_all_but_error;
819 end if;
820 return(sta_valid);
821 END return_status;
822
826
823 /* ------------------------------------------------------------------------ */
824 /* Converts column type code to a name such as VARCHAR2 etc. */
825 /* ------------------------------------------------------------------------ */
827 -- FUNCTION column_type_name(type_code IN VARCHAR2) RETURN VARCHAR2 IS
828 -- BEGIN
829 -- if(type_code = 'V') then
830 -- return('VARCHAR2');
831 -- else if(type_code = 'C') then
832 -- return('CHAR');
833 -- else if(type_code = 'N') then
834 -- return('NUMBER');
835 -- else if(type_code = 'D') then
836 -- return('DATE');
837 -- else
838 -- return(NULL);
839 -- end if;
840 -- return(NULL);
841 -- END column_type_name;
842
843 /* ------------------------------------------------------------------------ */
844 /* Internal function for setting column value and data type. */
845 /* ------------------------------------------------------------------------ */
846
847 PROCEDURE add_column_value(column_name IN VARCHAR2,
848 column_value IN VARCHAR2,
849 column_type IN VARCHAR2) IS
850 n NUMBER;
851 ndefined NUMBER;
852 colname VARCHAR2(30);
853
854 BEGIN
855 -- Initialize column count if necessary
856 --
857 if(g_coldef.colvals.ncolumns is null) then
858 ndefined := 0;
859 else
860 ndefined := g_coldef.colvals.ncolumns;
861 end if;
862
863 -- Get the column name
864 --
865 colname := UPPER(SUBSTRB(column_name, 1, 30));
866
867 -- Redefine value if column already defined.
868 --
869 for i in 1..ndefined loop
870 if(g_coldef.colvals.column_names(i) = colname) then
871 n := i;
872 exit;
873 end if;
874 end loop;
875
876 -- If column not already defined, add a new one.
877 --
878 if(n is null) then
879 ndefined := ndefined + 1;
880 n := ndefined;
881 end if;
882
883 -- Set the column value
884 --
885 g_coldef.colvals.column_names(n) := colname;
886 g_coldef.colvals.column_values(n) := SUBSTRB(column_value, 1, 1000);
887 g_coldef.colvals.column_types(n) := SUBSTRB(column_type, 1, 1);
888 g_coldef.colvals.ncolumns := ndefined;
889
890 END add_column_value;
891
892 /* ------------------------------------------------------------------------ */
893 /* Added for Bug 2221725 - need to default IDs as well as Values. */
894 /* */
895 /* Previously we always passed invoking_mode => 'V' to the descval_engine. */
896 /* Now we will pass invoking_mode => api_mode which is set by this function */
897 /* based on the value for values_or_ids which is passed by the code calling */
898 /* this api. Checks for invoking mode = 'D' have been added to the engine */
899 /* in AFFFSV4B.pls and AFFFSV1B.pls. When invoking_mode = 'D' and the */
900 /* segment is null we will temporarily switch to "values" mode and get the */
901 /* default value. This will allow the user to retrieve the defaulted ID if */
902 /* descval_engine returns success. */
903 /* */
904 /* Note: no change had to be made to logic involving values_or_ids as */
905 /* we always pass vals_not_ids => (values_or_ids = 'V') to descval_engine, */
906 /* which means vals_not_ids will be FALSE if values_or_ids = 'I' or 'D'. */
907 /* ------------------------------------------------------------------------ */
908
909 FUNCTION check_api_mode(values_or_ids IN VARCHAR2)
910 RETURN VARCHAR2
911 IS
912 l_api_mode VARCHAR2(1);
913 BEGIN
914 IF values_or_ids = 'D' then
915 l_api_mode := 'D';
916 ELSE
917 l_api_mode := 'V';
918 END IF;
919
920 RETURN (l_api_mode);
921
922 END check_api_mode;
923
924 FUNCTION get_default_context(p_application_short_name IN VARCHAR2,
925 p_descriptive_flexfield_name IN VARCHAR2)
926 RETURN VARCHAR2
927 IS
928 l_default_context_value fnd_descriptive_flexs_vl.default_context_value%TYPE;
929 BEGIN
930 SELECT fdfv.default_context_value
931 INTO l_default_context_value
932 FROM fnd_application fa,
933 fnd_descriptive_flexs_vl fdfv
934 WHERE fa.application_short_name = p_application_short_name
935 AND fdfv.application_id = fa.application_id
936 AND fdfv.descriptive_flexfield_name = p_descriptive_flexfield_name;
937
938 RETURN (l_default_context_value);
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 raise_application_error
943 (-20005, 'DVLB.get_default_context() failed. SQLERRM: ' || Sqlerrm,
944 TRUE);
945 END get_default_context;
946
947 FUNCTION get_ref_field_context(p_application_short_name IN VARCHAR2,
948 p_descriptive_flexfield_name IN VARCHAR2)
949 RETURN VARCHAR2
950 IS
951 l_default_context_value fnd_descriptive_flexs_vl.default_context_value%TYPE;
952 l_default_context_field_name fnd_descriptive_flexs_vl.default_context_field_name%TYPE;
953 bind_val VARCHAR2(2000);
954
955 BEGIN
956 SELECT fdfv.default_context_value, fdfv.default_context_field_name
957 INTO l_default_context_value, l_default_context_field_name
958 FROM fnd_application fa,
959 fnd_descriptive_flexs_vl fdfv
960 WHERE fa.application_short_name = p_application_short_name
961 AND fdfv.application_id = fa.application_id
962 AND fdfv.descriptive_flexfield_name = p_descriptive_flexfield_name;
963
964 if ((l_default_context_value is null) and
965 (l_default_context_field_name is not null)) then
966 IF (INSTR(l_default_context_field_name, ':$PROFILES$.') = 1) then
967 FND_PROFILE.get(UPPER(SUBSTR(l_default_context_field_name, 13)), bind_val);
968 BEGIN
969 SELECT ctx.descriptive_flex_context_code
970 INTO l_default_context_value
971 FROM fnd_application fa,
972 fnd_descr_flex_contexts ctx
973 WHERE fa.application_short_name = p_application_short_name
974 AND fa.application_id = ctx.application_id
975 AND ctx.descriptive_flexfield_name = p_descriptive_flexfield_name
976 AND ctx.enabled_flag = 'Y'
977 AND ctx.descriptive_flex_context_code = bind_val;
978
979 EXCEPTION
980 WHEN NO_DATA_FOUND THEN
981 l_default_context_value := NULL;
982 END;
983 END IF;
984 end if;
985
986 RETURN (l_default_context_value);
987
988 EXCEPTION
989 WHEN OTHERS THEN
990 raise_application_error
991 (-20005, 'DVLB.get_ref_field_context() failed. SQLERRM: ' || Sqlerrm,
992 TRUE);
993 END get_ref_field_context;
994
995 END fnd_flex_descval;
[Home] [Help]