[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_XML_PUBLISHER_APIS
Source
1 PACKAGE BODY fnd_flex_xml_publisher_apis AS
2 /* $Header: AFFFXPAB.pls 120.8.12000000.1 2007/01/18 13:19:33 appldev ship $ */
3
4 --
5 -- Qualifier Segment Number cache, generic version. One value per key
6 --
7 g_snum_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
8 g_snum_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
9 --
10 -- Application cache, generic version. One value per key
11 --
12 g_app_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
13 g_app_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
14
15 --
16 -- Key Flexfield cache, generic version. One value per key
17 --
18 g_kflx_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
19 g_kflx_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
20
21 --
22 -- Key Flexfield Structure Number cache, generic version. Many values per key
23 --
24 g_stno_generic_1tom_controller fnd_plsql_cache.cache_1tom_controller_type;
25 g_stno_generic_1tom_storage fnd_plsql_cache.generic_cache_values_type;
26 --
27 -- Key Flexfield Structure cache, generic version. One value per key
28 --
29 g_str_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
30 g_str_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
31 --
32 --
33 -- Key Flexfield Segment cache, generic version. One value per key
34 --
35 g_seg_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
36 g_seg_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
37 --
38 -- Key Flexfield Segment TL cache, generic version. One value per key
39 --
40 g_segt_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
41 g_segt_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
42 --
43 -- Parent Value Set Id cache, generic version. One value per key
44 --
45 p_vsid_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
46 p_vsid_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
47 --
48 -- Key Flexfield Segment Info, generic version. Many values per key
49 --
50 seginf_generic_1tom_controller fnd_plsql_cache.cache_1tom_controller_type;
51 seginf_generic_1tom_storage fnd_plsql_cache.generic_cache_values_type;
52 --
53 -- Process KFF Combination, generic version. One value per key
54 --
55 prcomb_generic_1to1_controller fnd_plsql_cache.cache_1to1_controller_type;
56 prcomb_generic_1to1_storage fnd_plsql_cache.generic_cache_values_type;
57
58 -- ERROR constants
59 --
60 error_others CONSTANT NUMBER := -20100;
61 error_no_data_found CONSTANT NUMBER := -20101;
62 error_not_supported_yet CONSTANT NUMBER := -20102;
63
64 --
65 -- Invalid Argument Errors
66 --
67 error_arg_arg_name_invalid CONSTANT NUMBER := -20200;
68
69 error_arg_lexical_name_null CONSTANT NUMBER := -20210;
70 error_arg_lexical_name_space CONSTANT NUMBER := -20211;
71 error_arg_lexical_name_long CONSTANT NUMBER := -20212;
72
73 error_arg_mdata_type_null CONSTANT NUMBER := -20220;
74 error_arg_mdata_type_invalid CONSTANT NUMBER := -20221;
75
76 error_arg_multi_num_null CONSTANT NUMBER := -20230;
77 error_arg_multi_num_invalid CONSTANT NUMBER := -20231;
78 error_arg_multi_num_misuse CONSTANT NUMBER := -20232;
79
80 error_arg_segments_null CONSTANT NUMBER := -20240;
81
82 error_arg_show_p_segs_null CONSTANT NUMBER := -20250;
83 error_arg_show_p_segs_invalid CONSTANT NUMBER := -20251;
84
85 error_arg_cct_alias_null CONSTANT NUMBER := -20260;
86 error_arg_cct_alias_space CONSTANT NUMBER := -20261;
87 error_arg_cct_alias_long CONSTANT NUMBER := -20262;
88
89 error_arg_output_type_null CONSTANT NUMBER := -20270;
90 error_arg_output_type_invalid CONSTANT NUMBER := -20271;
91
92 error_arg_operator_null CONSTANT NUMBER := -20280;
93 error_arg_operator_invalid CONSTANT NUMBER := -20281;
94
95 error_arg_debug_mode_null CONSTANT NUMBER := -20295;
96 error_arg_debug_mode_invalid CONSTANT NUMBER := -20296;
97
98 --
99 -- Flex Metadata State Errors
100 --
101 error_no_enabled_frozen_str CONSTANT NUMBER := -20301;
102 error_str_not_enabled CONSTANT NUMBER := -20302;
103 error_str_not_frozen CONSTANT NUMBER := -20303;
104
105 --
106 -- Runtime State Errors
107 --
108 error_invalid_seg_num CONSTANT NUMBER := -20401;
109 error_invalid_seg_qual CONSTANT NUMBER := -20402;
110
111
112 --
113 -- ARGUMENT Name Constants
114 --
115 arg_argument_name CONSTANT VARCHAR2(30) := 'ARGUMENT_NAME';
116 arg_debug_mode CONSTANT VARCHAR2(30) := 'DEBUG_MODE';
117 arg_lexical_name CONSTANT VARCHAR2(30) := 'LEXICAL_NAME';
118 arg_metadata_type CONSTANT VARCHAR2(30) := 'METADATA_TYPE';
119 arg_multiple_id_flex_num CONSTANT VARCHAR2(30) := 'MULTIPLE_ID_FLEX_NUM';
120 arg_id_flex_num CONSTANT VARCHAR2(30) := 'ID_FLEX_NUM';
121 arg_segments CONSTANT VARCHAR2(30) := 'SEGMENTS';
122 arg_show_parent_segments CONSTANT VARCHAR2(30) := 'SHOW_PARENT_SEGMENTS';
123 arg_cct_alias CONSTANT VARCHAR2(30) := 'CODE_COMBINATION_TABLE_ALIAS';
124 arg_output_type CONSTANT VARCHAR2(30) := 'OUTPUT_TYPE';
125 arg_operator CONSTANT VARCHAR2(30) := 'OPERATOR';
126 arg_operand1 CONSTANT VARCHAR2(30) := 'OPERAND1';
127 arg_operand2 CONSTANT VARCHAR2(30) := 'OPERAND2';
128
129
130 --
131 -- <SEGMENTS> parser mode constants
132 --
133 segments_mode_all_enabled CONSTANT VARCHAR2(30) := 'ALL_ENABLED';
134 segments_mode_displayed_only CONSTANT VARCHAR2(30) := 'DISPLAYED_ONLY';
135
136
137 --
138 -- TYPEs
139 --
140 SUBTYPE app_type IS fnd_application%ROWTYPE;
141 SUBTYPE kff_flx_type IS fnd_id_flexs%ROWTYPE;
142 SUBTYPE kff_str_type IS fnd_id_flex_structures%ROWTYPE;
143 SUBTYPE kff_seg_type IS fnd_id_flex_segments%ROWTYPE;
144 SUBTYPE kff_seg_tl_type IS fnd_id_flex_segments_tl%ROWTYPE;
145
146 TYPE varchar2_30_array_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
147 TYPE number_array_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
148
149 TYPE decode_element_type IS RECORD
150 (search NUMBER,
151 result VARCHAR2(32000));
152
153 TYPE decode_elements_type IS TABLE OF decode_element_type INDEX BY BINARY_INTEGER;
154
155 --
156 -- Global Constants
157 --
158 max_numof_decode_elements CONSTANT NUMBER := 126;
159
160 --
161 -- Global Variables
162 --
163 g_newline VARCHAR2(100);
164 g_unused_argument VARCHAR2(100);
165 g_debug_enabled BOOLEAN;
166 g_debug VARCHAR2(32000);
167
168 --
169 -- Private APIs
170 --
171
172 --------------------------------------------------------------------------------
173 -- Wrapper for raise_application_error(<code>, <error>, TRUE);
174 --------------------------------------------------------------------------------
175 PROCEDURE raise_error
176 (p_code IN NUMBER,
177 p_error IN VARCHAR2)
178 IS
179 BEGIN
180 raise_application_error(p_code, p_error, TRUE);
181
182 -- No exception handling here
183 END raise_error;
184
185 --------------------------------------------------------------------------------
186 -- Raises exception for 'when others then' block
187 --------------------------------------------------------------------------------
188 PROCEDURE raise_others
189 (p_method IN VARCHAR2,
190 p_arg1 IN VARCHAR2 DEFAULT g_unused_argument,
191 p_arg2 IN VARCHAR2 DEFAULT g_unused_argument,
192 p_arg3 IN VARCHAR2 DEFAULT g_unused_argument,
193 p_arg4 IN VARCHAR2 DEFAULT g_unused_argument,
194 p_arg5 IN VARCHAR2 DEFAULT g_unused_argument,
195 p_arg6 IN VARCHAR2 DEFAULT g_unused_argument,
196 p_arg7 IN VARCHAR2 DEFAULT g_unused_argument,
197 p_arg8 IN VARCHAR2 DEFAULT g_unused_argument,
198 p_arg9 IN VARCHAR2 DEFAULT g_unused_argument)
199 IS
200 l_error VARCHAR2(32000);
201 BEGIN
202 l_error := p_method || '(';
203
204 if (p_arg1 <> g_unused_argument) then
205 l_error := l_error || p_arg1;
206 end if;
207
208 if (p_arg2 <> g_unused_argument) then
209 l_error := l_error || ', ' || p_arg2;
210 end if;
211
212 if (p_arg3 <> g_unused_argument) then
213 l_error := l_error || ', ' || p_arg3;
214 end if;
215
216 if (p_arg4 <> g_unused_argument) then
217 l_error := l_error || ', ' || p_arg4;
218 end if;
219
220 if (p_arg5 <> g_unused_argument) then
221 l_error := l_error || ', ' || p_arg5;
222 end if;
223
224 if (p_arg6 <> g_unused_argument) then
225 l_error := l_error || ', ' || p_arg6;
226 end if;
227
228 if (p_arg7 <> g_unused_argument) then
229 l_error := l_error || ', ' || p_arg7;
230 end if;
231
232 if (p_arg8 <> g_unused_argument) then
233 l_error := l_error || ', ' || p_arg8;
234 end if;
235
236 if (p_arg9 <> g_unused_argument) then
237 l_error := l_error || ', ' || p_arg9;
238 end if;
239
240 l_error := l_error || ') raised exception.';
241
242 raise_error(error_others, l_error);
243
244 -- No exception handling here
245 END raise_others;
246
247 --------------------------------------------------------------------------------
248 -- Initializes Debugger
249 --------------------------------------------------------------------------------
250 PROCEDURE init_debug
251 IS
252 BEGIN
253 IF (g_debug_enabled) THEN
254 g_debug := 'Flexfield Debugger is ON.' || g_newline;
255
256 ELSE
257 g_debug := 'Flexfield Debugger is OFF.' || g_newline;
258
259 END IF;
260
261 g_debug := g_debug ||
262 'Package : FND_FLEX_XML_PUBLISHER_APIS' || g_newline ||
263 'Version : $Header: AFFFXPAB.pls 120.8.12000000.1 2007/01/18 13:19:33 appldev ship $' || g_newline ||
264 'Sysdate : ' || To_char(Sysdate, 'YYYY/MM/DD HH24:MI:SS') || g_newline;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 raise_others('init_debug');
269 END init_debug;
270
271 --------------------------------------------------------------------------------
272 -- Adds Debug Messages
273 --------------------------------------------------------------------------------
274 PROCEDURE add_debug
275 (p_method IN VARCHAR2,
276 p_debug IN VARCHAR2)
277 IS
278 BEGIN
279 IF (g_debug_enabled) THEN
280 g_debug := g_debug ||
281 p_method || ': ' || p_debug || g_newline;
282 END IF;
283 EXCEPTION
284 WHEN OTHERS THEN
285 raise_others('add_debug',
286 p_method,
287 p_debug);
288 END add_debug;
289
290 --------------------------------------------------------------------------------
291 -- Raises exception for 'when no_data_found then' block
292 --------------------------------------------------------------------------------
293 PROCEDURE raise_no_data_found
294 (p_entity IN VARCHAR2,
295 p_key1 IN VARCHAR2,
296 p_value1 IN VARCHAR2,
297 p_key2 IN VARCHAR2 DEFAULT NULL,
298 p_value2 IN VARCHAR2 DEFAULT NULL,
299 p_key3 IN VARCHAR2 DEFAULT NULL,
300 p_value3 IN VARCHAR2 DEFAULT NULL,
301 p_key4 IN VARCHAR2 DEFAULT NULL,
302 p_value4 IN VARCHAR2 DEFAULT NULL,
303 p_key5 IN VARCHAR2 DEFAULT NULL,
304 p_value5 IN VARCHAR2 DEFAULT NULL,
305 p_key6 IN VARCHAR2 DEFAULT NULL,
306 p_value6 IN VARCHAR2 DEFAULT NULL,
307 p_key7 IN VARCHAR2 DEFAULT NULL,
308 p_value7 IN VARCHAR2 DEFAULT NULL)
309 IS
310 l_error VARCHAR2(32000);
311 BEGIN
312 l_error := ('<' || p_entity || '> does not exist. Primary Key: ' ||
313 Upper(p_key1) || ':''' || p_value1 || '''');
314 IF (p_key2 IS NOT NULL) THEN
315 l_error := l_error || ', ' || Upper(p_key2) || ':''' || p_value2 || '''';
316 END IF;
317 IF (p_key3 IS NOT NULL) THEN
318 l_error := l_error || ', ' || Upper(p_key3) || ':''' || p_value3 || '''';
319 END IF;
320 IF (p_key4 IS NOT NULL) THEN
321 l_error := l_error || ', ' || Upper(p_key4) || ':''' || p_value4 || '''';
322 END IF;
323 IF (p_key5 IS NOT NULL) THEN
324 l_error := l_error || ', ' || Upper(p_key5) || ':''' || p_value5 || '''';
325 END IF;
326 IF (p_key6 IS NOT NULL) THEN
327 l_error := l_error || ', ' || Upper(p_key6) || ':''' || p_value6 || '''';
328 END IF;
329 IF (p_key7 IS NOT NULL) THEN
330 l_error := l_error || ', ' || Upper(p_key7) || ':''' || p_value7 || '''';
331 END IF;
332
333 raise_error(error_no_data_found, l_error);
334
335 -- No exception handling here.
336 END raise_no_data_found;
337
338
339 --------------------------------------------------------------------------------
340 -- Raises exception for invalid arguments.
341 --------------------------------------------------------------------------------
342 PROCEDURE raise_invalid_argument
343 (p_error_code IN NUMBER,
344 p_argument_name IN VARCHAR2,
345 p_argument_value IN VARCHAR2,
346 p_problem IN VARCHAR2 DEFAULT NULL,
347 p_solution IN VARCHAR2 DEFAULT NULL,
348 p_valid_values IN VARCHAR2 DEFAULT NULL)
349 IS
350 l_error VARCHAR2(32000);
351 BEGIN
352 l_error :=
353 'The value ''' || p_argument_value || ''' is not a valid ' ||
354 'value for the argument ''' || p_argument_name || '''. ' ||
355 g_newline;
356
357 IF (p_problem IS NOT NULL) THEN
358 l_error := l_error ||
359 'Error: ' || p_problem || '. ' || g_newline;
360
361 IF (p_solution IS NOT NULL) THEN
362 l_error := l_error ||
363 'Solution: ' || p_solution || '. ' || g_newline;
364 END IF;
365 END IF;
366
367 IF (p_valid_values IS NOT NULL) THEN
368 l_error := l_error ||
369 'The valid values are ''' || p_valid_values || '''. ' ||
370 'Please see $FND_TOP/patch/115/sql/AFFFXPAS.pls file ' ||
371 'for more information. ';
372 ELSE
373 l_error := l_error ||
374 'Please see $FND_TOP/patch/115/sql/AFFFXPAS.pls file ' ||
375 'for valid values and more information. ';
376 END IF;
377
378 raise_error(p_error_code, l_error);
379
380 -- No exception handling here
381 END raise_invalid_argument;
382
383 --------------------------------------------------------------------------------
384 -- Raises Error for NOT NULL Arguments
385 --------------------------------------------------------------------------------
386 PROCEDURE raise_argument_null(p_argument_name IN VARCHAR2,
387 p_error_code IN NUMBER)
388 IS
389 BEGIN
390 raise_invalid_argument
391 (p_error_code => p_error_code,
392 p_argument_name => p_argument_name,
393 p_argument_value => NULL,
394 p_problem => 'NULL is not a valid value for ' || p_argument_name,
395 p_solution => 'Please use a NOT NULL value for ' || p_argument_name,
396 p_valid_values => NULL);
397
398 -- No exception handling here.
399 END raise_argument_null;
400
401 --------------------------------------------------------------------------------
402 -- Validates Arguments
403 --------------------------------------------------------------------------------
404 PROCEDURE validate_argument(p_argument_name IN VARCHAR2,
405 p_argument_value IN VARCHAR2)
406 IS
407 l_error_code NUMBER;
408 l_problem VARCHAR2(32000);
409 l_solution VARCHAR2(32000);
410 l_valid_values VARCHAR2(32000);
411 BEGIN
412 l_error_code := NULL;
413 l_problem := NULL;
414 l_solution := NULL;
415 l_valid_values := NULL;
416
417 IF (p_argument_name = arg_lexical_name) THEN
418 IF (p_argument_value IS NULL) THEN
419 raise_argument_null(p_argument_name, error_arg_lexical_name_null);
420
421 ELSIF (Instr(p_argument_value, ' ') > 0) THEN
422 l_error_code := error_arg_lexical_name_space;
423 l_problem := p_argument_name || ' cannot contain space character';
424 l_solution := 'Please remove space character from ' || p_argument_name;
425
426 ELSIF (Length(p_argument_value) > 25) THEN
427 l_error_code := error_arg_lexical_name_long;
428 l_problem := p_argument_name || ' cannot be longer than 25 characters';
429 l_solution := 'Please use a shorter ' || p_argument_name;
430
431 END IF;
432
433 ELSIF (p_argument_name = arg_debug_mode) THEN
434 IF (p_argument_value IS NULL) THEN
435 raise_argument_null(p_argument_name, error_arg_debug_mode_null);
436
437 ELSIF (p_argument_value NOT IN (debug_mode_on,
438 debug_mode_off)) THEN
439 l_error_code := error_arg_debug_mode_invalid;
440 l_valid_values :=
441 debug_mode_on || ', ' ||
442 debug_mode_off;
443
444 END IF;
445
446 ELSIF (p_argument_name = arg_metadata_type) THEN
447 IF (p_argument_value IS NULL) THEN
448 raise_argument_null(p_argument_name, error_arg_mdata_type_null);
449
450 ELSIF (p_argument_value NOT IN (metadata_segments_above_prompt,
451 metadata_segments_left_prompt)) THEN
452 l_error_code := error_arg_mdata_type_invalid;
453 l_valid_values :=
454 metadata_segments_above_prompt || ', ' ||
455 metadata_segments_left_prompt;
456
457 END IF;
458
459 ELSIF (p_argument_name = arg_multiple_id_flex_num) THEN
460 IF (p_argument_value IS NULL) THEN
461 raise_argument_null(p_argument_name, error_arg_multi_num_null);
462
463 ELSIF (p_argument_value NOT IN ('Y', 'N')) THEN
464 l_error_code := error_arg_multi_num_invalid;
465 l_valid_values := 'Y, N';
466
467 END IF;
468
469 ELSIF (p_argument_name = arg_segments) THEN
470 IF (p_argument_value IS NULL) THEN
471 raise_argument_null(p_argument_name, error_arg_segments_null);
472
473 END IF;
474
475 ELSIF (p_argument_name = arg_show_parent_segments) THEN
476 IF (p_argument_value IS NULL) THEN
477 raise_argument_null(p_argument_name, error_arg_show_p_segs_null);
478
479 ELSIF (p_argument_value NOT IN ('Y', 'N')) THEN
480 l_error_code := error_arg_show_p_segs_invalid;
481 l_valid_values := 'Y, N';
482
483 END IF;
484
485 ELSIF (p_argument_name = arg_cct_alias) THEN
486 IF (p_argument_value IS NULL) THEN
487 --
488 -- NULL value is OK for CODE_COMBINATION_TABLE_ALIAS
489 --
490 NULL;
491
492 ELSIF (Instr(p_argument_value, ' ') > 0) THEN
493 l_error_code := error_arg_cct_alias_space;
494 l_problem := p_argument_name || ' cannot contain space character';
495 l_solution := 'Please remove space character from ' || p_argument_name;
496
497 ELSIF (Length(p_argument_value) > 30) THEN
498 l_error_code := error_arg_cct_alias_long;
499 l_problem := p_argument_name || ' cannot be longer than 30 characters';
500 l_solution := 'Please use a shorter ' || p_argument_name;
501
502 END IF;
503
504 ELSIF (p_argument_name = arg_output_type) THEN
505 IF (p_argument_value IS NULL) THEN
506 raise_argument_null(p_argument_name, error_arg_output_type_null);
507
508 ELSIF (p_argument_value NOT IN (output_type_value,
509 output_type_padded_value,
510 output_type_description,
511 output_type_full_description,
512 output_type_security)) THEN
513 l_error_code := error_arg_output_type_invalid;
514 l_valid_values :=
515 output_type_value || ', ' ||
516 output_type_padded_value || ', ' ||
517 output_type_description || ', ' ||
518 output_type_full_description || ', ' ||
519 output_type_security;
520 END IF;
521
522 ELSIF (p_argument_name = arg_operator) THEN
523 IF (p_argument_value IS NULL) THEN
524 raise_argument_null(p_argument_name, error_arg_operator_null);
525
526 ELSIF (p_argument_value NOT IN (operator_equal,
527 operator_less_than,
528 operator_greater_than,
529 operator_less_than_or_equal,
530 operator_greater_than_or_equal,
531 operator_not_equal,
532 operator_concatenate,
533 operator_between,
534 operator_qbe,
535 operator_like)) THEN
536 l_error_code := error_arg_operator_invalid;
537 l_valid_values :=
538 operator_equal || ', ' ||
539 operator_less_than || ', ' ||
540 operator_greater_than || ', ' ||
541 operator_less_than_or_equal || ', ' ||
542 operator_greater_than_or_equal || ', ' ||
543 operator_not_equal || ', ' ||
544 operator_concatenate || ', ' ||
545 operator_between || ', ' ||
546 operator_qbe || ', ' ||
547 operator_like;
548
549 END IF;
550
551 ELSE
552 l_error_code := error_arg_arg_name_invalid;
553 l_problem := 'Flex Developer Error: Argument Name is not known';
554 l_solution := 'Please open a bug against 510/FLEXFIELDS';
555
556 END IF;
557
558 IF (l_error_code IS NOT NULL) THEN
559 raise_invalid_argument(p_error_code => l_error_code,
560 p_argument_name => p_argument_name,
561 p_argument_value => p_argument_value,
562 p_problem => l_problem,
563 p_solution => l_solution,
564 p_valid_values => l_valid_values);
565 END IF;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 raise_others('validate_argument',
570 p_argument_name,
571 p_argument_value);
572 END validate_argument;
573
574 --------------------------------------------------------------------------------
575 -- Cross Validates Arguments
576 --------------------------------------------------------------------------------
577 PROCEDURE cross_validate_arguments(p_argument1_name IN VARCHAR2,
578 p_argument1_value IN VARCHAR2,
579 p_argument2_name IN VARCHAR2,
580 p_argument2_value IN VARCHAR2)
581 IS
582 l_error_code NUMBER;
583 l_error VARCHAR2(32000);
584 BEGIN
585 l_error_code := NULL;
586 l_error := NULL;
587
588 IF (p_argument1_name = arg_multiple_id_flex_num) THEN
589 IF (p_argument2_name = arg_id_flex_num) THEN
590 --
591 -- multiple_id_flex_num
592 -- | id_flex_num result
593 -- --- ----------- -----------
594 -- N NULL ERROR
595 -- N NOT NULL OK
596 -- Y NULL OK
597 -- Y NOT NULL ERROR BUT IGNORE, assume id_flex_num = NULL
598 --
599 IF (p_argument1_value = 'N' AND p_argument2_value IS NULL) THEN
600 l_error_code := error_arg_multi_num_misuse;
601 l_error :=
602 'MULTIPLE_ID_FLEX_NUM and ID_FLEX_NUM arguments are not ' ||
603 'passed properly. Valid combinations are : ' ||
604 '(MULTIPLE_ID_FLEX_NUM = Y) OR ' ||
605 '(MULTIPLE_ID_FLEX_NUM = N AND ID_FLEX_NUM = NOT NULL).';
606 END IF;
607 END IF;
608 END IF;
609
610 IF (l_error_code IS NOT NULL) THEN
611 raise_error(l_error_code, l_error);
612 END IF;
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 raise_others('cross_validate_arguments',
617 p_argument1_name,
618 p_argument1_value,
619 p_argument2_name,
620 p_argument2_value);
621 END cross_validate_arguments;
622
623 --------------------------------------------------------------------------------
624 -- Returns Application details.
625 --------------------------------------------------------------------------------
626 PROCEDURE get_app
627 (p_application_short_name IN fnd_application.application_short_name%TYPE,
628 x_app OUT nocopy app_type)
629 IS
630 l_application app_type;
631 l_key VARCHAR2(2000);
632 l_value fnd_plsql_cache.generic_cache_value_type;
633 l_return_code VARCHAR2(1);
634 BEGIN
635 --
636 -- Create the key. If you have a composite key then concatenate
637 -- them with a delimiter. i.e. p_key1 || '.' || p_key2 || ...
638 --
639 l_key := p_application_short_name;
640 --
641 -- First check the cache.
642 --
643 fnd_plsql_cache.generic_1to1_get_value(g_app_generic_1to1_controller,
644 g_app_generic_1to1_storage,
645 l_key,
646 l_value,
647 l_return_code);
648 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
649 --
650 -- Entity is in the cache, populate the return value.
651 --
652 l_application.application_id := l_value.number_1;
653 l_application.application_short_name := l_value.varchar2_1;
654
655 ELSE
656 --
657 -- Entity is not in the cache, get it from DB.
658 --
659 BEGIN
660 SELECT fa.*
661 INTO l_application
662 FROM fnd_application fa
663 WHERE fa.application_short_name = p_application_short_name;
664 --
665 -- Create the cache value, and populate it with values came from DB.
666 --
667 fnd_plsql_cache.generic_cache_new_value
668 (x_value => l_value,
669 p_number_1 => l_application.application_id,
670 p_varchar2_1 => l_application.application_short_name);
671
672 --
673 -- Put the value in cache.
674 --
675 fnd_plsql_cache.generic_1to1_put_value(g_app_generic_1to1_controller,
676 g_app_generic_1to1_storage,
677 l_key,
678 l_value);
679 EXCEPTION
680 WHEN no_data_found THEN
681 raise_no_data_found
682 ('Application',
683 'application_short_name', p_application_short_name);
684 END;
685 END IF;
686 --
687 -- Return the output value.
688 --
689 x_app := l_application;
690 EXCEPTION
691 WHEN OTHERS THEN
692 raise_others('get_app',
693 p_application_short_name);
694 END get_app;
695
696
697 --------------------------------------------------------------------------------
698 -- Returns Key Flexfield details.
699 --------------------------------------------------------------------------------
700 PROCEDURE get_kff_flx
701 (p_application_short_name IN fnd_application.application_short_name%TYPE,
702 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
703 x_kff_flx OUT nocopy kff_flx_type)
704 IS
705 l_application app_type;
706 l_kff_flx kff_flx_type;
707 l_key VARCHAR2(2000);
708 l_value fnd_plsql_cache.generic_cache_value_type;
709 l_return_code VARCHAR2(1);
710 BEGIN
711 get_app(p_application_short_name, l_application);
712
713 --
714 -- Create the key.
715 --
716 l_key := p_application_short_name || '.' || p_id_flex_code;
717 --
718 -- First check the cache.
719 --
720 fnd_plsql_cache.generic_1to1_get_value(g_kflx_generic_1to1_controller,
721 g_kflx_generic_1to1_storage,
722 l_key,
723 l_value,
724 l_return_code);
725 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
726 --
727 -- Entity is in the cache, populate the return value.
728 --
729 l_kff_flx.application_id := l_value.number_1;
730 l_kff_flx.id_flex_code := l_value.varchar2_1;
731 l_kff_flx.set_defining_column_name := l_value.varchar2_2;
732 l_kff_flx.unique_id_column_name := l_value.varchar2_3;
733
734 ELSE
735 --
736 -- Entity is not in the cache, get it from DB.
737 --
738 BEGIN
739 SELECT fif.*
740 INTO l_kff_flx
741 FROM fnd_id_flexs fif
742 WHERE fif.application_id = l_application.application_id
743 AND fif.id_flex_code = p_id_flex_code;
744 --
745 -- Create the cache value, and populate it with values came from DB.
746 --
747 fnd_plsql_cache.generic_cache_new_value
748 (x_value => l_value,
749 p_number_1 => l_kff_flx.application_id,
750 p_varchar2_1 => l_kff_flx.id_flex_code,
751 p_varchar2_2 => l_kff_flx.set_defining_column_name,
752 p_varchar2_3 => l_kff_flx.unique_id_column_name);
753
754 --
755 -- Put the value in cache.
756 --
757 fnd_plsql_cache.generic_1to1_put_value(g_kflx_generic_1to1_controller,
758 g_kflx_generic_1to1_storage,
759 l_key,
760 l_value);
761 EXCEPTION
762 WHEN no_data_found THEN
763 raise_no_data_found
764 ('Application',
765 'application_short_name', p_application_short_name);
766 END;
767 END IF;
768 --
769 -- Return the output value.
770 --
771 x_kff_flx := l_kff_flx;
772 EXCEPTION
773 WHEN OTHERS THEN
774 raise_others('get_kff_flx',
775 p_application_short_name,
776 p_id_flex_code);
777 END get_kff_flx;
778
779
780 --------------------------------------------------------------------------------
781 -- Returns Structure Numbers of enabled and frozen KFF Structures.
782 --------------------------------------------------------------------------------
783 PROCEDURE get_kff_str_numbers
784 (p_kff_flx IN kff_flx_type,
785 x_numof_structures OUT nocopy NUMBER,
786 x_structure_numbers OUT nocopy number_array_type)
787 IS
788 l_key VARCHAR2(2000);
789 l_values fnd_plsql_cache.generic_cache_values_type;
790 l_numof_values NUMBER;
791 l_return_code VARCHAR2(1);
792 i NUMBER;
793 l_numof_structures NUMBER;
794 l_structure_numbers number_array_type;
795 BEGIN
796 --
797 -- Create the key.
798 --
799 l_key := p_kff_flx.application_id || '.' || p_kff_flx.id_flex_code;
800
801 --
802 -- First check the cache.
803 --
804 fnd_plsql_cache.generic_1tom_get_values(g_stno_generic_1tom_controller,
805 g_stno_generic_1tom_storage,
806 l_key,
807 l_numof_values,
808 l_values,
809 l_return_code);
810
811 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
812 --
813 -- Entity is in the cache, populate the return values.
814 --
815 FOR i IN 1..l_numof_values LOOP
816 l_structure_numbers(i) := l_values(i).number_1;
817 END LOOP;
818 l_numof_structures := l_numof_values;
819 ELSE
820 SELECT fifst.id_flex_num
821 BULK COLLECT INTO l_structure_numbers
822 FROM fnd_id_flex_structures fifst
823 WHERE fifst.application_id = p_kff_flx.application_id
824 AND fifst.id_flex_code = p_kff_flx.id_flex_code
825 AND fifst.enabled_flag = 'Y'
826 AND fifst.freeze_flex_definition_flag = 'Y'
827 ORDER BY fifst.id_flex_num;
828
829 l_numof_structures := SQL%ROWCOUNT;
830 FOR i IN 1..l_structure_numbers.COUNT LOOP
831 --
832 -- Create the cache value, and populate it with values came from DB.
833 --
834 fnd_plsql_cache.generic_cache_new_value
835 (x_value => l_values(i),
836 p_number_1 => l_structure_numbers(i));
837 END LOOP;
838 l_numof_values := l_numof_structures;
839 --
840 -- Put the values in cache.
841 --
842 fnd_plsql_cache.generic_1tom_put_values(g_stno_generic_1tom_controller,
843 g_stno_generic_1tom_storage,
844 l_key,
845 l_numof_values,
846 l_values);
847 END IF;
848
849
850
851 IF (l_numof_structures = 0) THEN
852 raise_error(error_no_enabled_frozen_str,
853 'There are no ENABLED and FROZEN structures.');
854
855 END IF;
856 --
857 -- Return the values
858 --
859 x_numof_structures := l_numof_structures;
860 x_structure_numbers := l_structure_numbers;
861 EXCEPTION
862 WHEN OTHERS THEN
863 raise_others('get_kff_str_numbers',
864 p_kff_flx.application_id,
865 p_kff_flx.id_flex_code);
866 END get_kff_str_numbers;
867
868
869 --------------------------------------------------------------------------------
870 -- Returns KFF Structure
871 --------------------------------------------------------------------------------
872 PROCEDURE get_kff_str
873 (p_kff_flx IN kff_flx_type,
874 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE,
875 x_kff_str OUT nocopy kff_str_type)
876 IS
877 l_key VARCHAR2(2000);
878 l_return_code VARCHAR2(1);
879 l_value fnd_plsql_cache.generic_cache_value_type;
880 l_kff_str kff_str_type;
881 BEGIN
882 --
883 -- Create the key.
884 --
885 l_key := p_kff_flx.application_id || '.' || p_kff_flx.id_flex_code || '.' || p_id_flex_num;
886
887 --
888 -- First check the cache.
889 --
890 fnd_plsql_cache.generic_1to1_get_value(g_str_generic_1to1_controller,
891 g_str_generic_1to1_storage,
892 l_key,
893 l_value,
894 l_return_code);
895
896 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
897 --
898 -- Entity is in the cache, populate the return value.
899 --
900 l_kff_str.application_id := l_value.number_1;
901 l_kff_str.id_flex_code := l_value.varchar2_1;
902 l_kff_str.id_flex_num := l_value.number_2;
903 l_kff_str.concatenated_segment_delimiter := l_value.varchar2_2;
904
905 ELSE
906 --
907 -- Entity is not in the cache, get it from DB.
908 --
909 BEGIN
910 SELECT fifst.*
911 INTO l_kff_str
912 FROM fnd_id_flex_structures fifst
913 WHERE fifst.application_id = p_kff_flx.application_id
914 AND fifst.id_flex_code = p_kff_flx.id_flex_code
915 AND fifst.id_flex_num = p_id_flex_num;
916 --
917 -- Create the cache value, and populate it with values came from DB.
918 --
919 fnd_plsql_cache.generic_cache_new_value
920 (x_value => l_value,
921 p_number_1 => l_kff_str.application_id,
922 p_varchar2_1 => l_kff_str.id_flex_code,
923 p_number_2 => l_kff_str.id_flex_num,
924 p_varchar2_2 => l_kff_str.concatenated_segment_delimiter);
925
926 --
927 -- Put the value in cache.
928 --
929 fnd_plsql_cache.generic_1to1_put_value(g_str_generic_1to1_controller,
930 g_str_generic_1to1_storage,
931 l_key,
932 l_value);
933 EXCEPTION
934 WHEN no_data_found THEN
935 raise_no_data_found
936 ('Key Flexfield Structure',
937 'application_id', p_kff_flx.application_id,
938 'id_flex_code', p_kff_flx.id_flex_code,
939 'id_flex_num', p_id_flex_num);
940 END;
941 END IF;
942
943 IF (l_kff_str.enabled_flag <> 'Y') THEN
944 raise_error(error_str_not_enabled,
945 'KFF Structure is not enabled.');
946
947 ELSIF (l_kff_str.freeze_flex_definition_flag <> 'Y') THEN
948 raise_error(error_str_not_frozen,
949 'KFF Structure is not frozen.');
950
951 END IF;
952 --
953 -- Return the output value
954 --
955 x_kff_str := l_kff_str;
956 EXCEPTION
957 WHEN OTHERS THEN
958 raise_others('get_kff_str',
959 p_kff_flx.application_id,
960 p_kff_flx.id_flex_code,
961 p_id_flex_num);
962 END get_kff_str;
963
964 --------------------------------------------------------------------------------
965 -- Returns Column Names
966 --
967 -- Please note the output given by get_kff_seg_column_names
968 -- for some cases using following example.
969 -- (segment1 (s1) is parent of segment2 (s2) and both are enabled).
970 -- p_s_m => p_segments_mode; DO => DISPLAYED_ONLY; AE => ALL_ENABLED.
971 -- p_s => p_segments
972 -- p_s_p_s => p_show_parent_segments
973 -- s1d, s2d => segment1, segment2 displayed in segments form
974 -- x_c_n => x_column_names (segment number given for illustration)
975 --
976 -- p_s_m p_s p_s_p_s s1d s2d x_c_n
977 --
978 -- DO 2 Y Y Y 1,2
979 -- DO 2 Y Y N 1
980 -- DO 2 Y N Y 2
981 -- DO 2 N Y Y 2
982 -- AE 2 Y Y/N Y/N 1,2
983 -- AE 2 N Y/N Y/N 2
984 --------------------------------------------------------------------------------
985 PROCEDURE get_kff_seg_column_names
986 (p_kff_str IN kff_str_type,
987 p_segments_mode IN VARCHAR2,
988 p_segments IN VARCHAR2,
989 p_show_parent_segments IN VARCHAR2,
990 x_numof_column_names OUT nocopy NUMBER,
991 x_column_names OUT nocopy varchar2_30_array_type)
992 IS
993 TYPE segment_info IS RECORD
994 (application_column_name fnd_id_flex_segments.application_column_name%TYPE,
995 display_flag fnd_id_flex_segments.display_flag%TYPE,
996 enabled_flag fnd_id_flex_segments.enabled_flag%TYPE,
997 flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE,
998 to_include VARCHAR2(1),
999 is_parent VARCHAR2(1),
1000 child_segment_num NUMBER);
1001
1002 TYPE segment_info_array IS TABLE of segment_info INDEX BY BINARY_INTEGER;
1003
1004 l_include varchar2_30_array_type;
1005 l_exclude varchar2_30_array_type;
1006 l_column_names varchar2_30_array_type;
1007 l_nsegments NUMBER;
1008 l_tmp_number NUMBER;
1009 l_display_index NUMBER;
1010 l_parent_value_set_id fnd_flex_value_sets.parent_flex_value_set_id%TYPE;
1011 l_value fnd_plsql_cache.generic_cache_value_type;
1012 l_key VARCHAR2(2000);
1013 l_values fnd_plsql_cache.generic_cache_values_type;
1014 l_numof_values NUMBER;
1015 l_return_code VARCHAR2(1);
1016 l_segment_info_array segment_info_array;
1017
1018 CURSOR c_segment_info(c_application_id fnd_id_flex_segments.application_id%TYPE,
1019 c_id_flex_code fnd_id_flex_segments.id_flex_code%TYPE,
1020 c_id_flex_num fnd_id_flex_segments.id_flex_num%TYPE)
1021 IS
1022 SELECT application_column_name, enabled_flag, display_flag, flex_value_set_id
1023 FROM fnd_id_flex_segments
1024 WHERE application_id = c_application_id
1025 AND id_flex_code = c_id_flex_code
1026 AND id_flex_num = c_id_flex_num
1027 ORDER BY segment_num, segment_name;
1028
1029 -- Procedures/functions private to get_kff_seg_column_names start here.
1030
1031 -- ------------------------------------------------------------------------
1032 -- Takes p_segments and tokenises into l_include and l_exclude array.
1033 -- Eg. p_segments => ALL\01, l_include(0)=ALL, l_exclude(0)=1
1034 -- p_segments => ALL\0GL_ACCOUNT, l_include(0)=ALL, l_exclude(0)=GL_ACCOUNT
1035 -- p_segments => GL_BALANCING, l_include(0)=GL_BALANCING
1036 -- ------------------------------------------------------------------------
1037 PROCEDURE kff_parse_string
1038 (p_segments IN VARCHAR2,
1039 x_include OUT nocopy varchar2_30_array_type,
1040 x_exclude OUT nocopy varchar2_30_array_type)
1041 IS
1042 l_delimiter VARCHAR2(2);
1043 l_tmpsegments VARCHAR2(1000);
1044 l_char VARCHAR2(1000);
1045 l_include_index NUMBER;
1046 l_exclude_index NUMBER;
1047 BEGIN
1048 l_delimiter := '\0';
1049 l_include_index := 0;
1050 l_exclude_index := 0;
1051 l_tmpsegments := p_segments;
1052
1053 l_char := instr(p_segments, '\0');
1054 IF (l_char = 0)
1055 THEN
1056 x_include(l_include_index) := p_segments;
1057 l_include_index := l_include_index + 1;
1058 ELSE
1059 x_include(l_include_index) := substr(p_segments, 1, l_char-1);
1060 l_include_index := l_include_index + 1;
1061 l_tmpsegments := substr(p_segments, l_char+2);
1062
1063 LOOP
1064 l_char := instr(l_tmpsegments, '\0');
1065 IF (l_char = 0)
1066 THEN
1067 x_exclude(l_exclude_index) := l_tmpsegments;
1068 exit;
1069 END IF;
1070 x_exclude(l_exclude_index) := substr(l_tmpsegments, 1, l_char-1);
1071 l_exclude_index := l_exclude_index + 1;
1072 l_tmpsegments := substr(l_tmpsegments, l_char+2);
1073 END LOOP;
1074
1075 END IF;
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 raise_others('kff_parse_string',
1080 p_segments);
1081 END kff_parse_string;
1082
1083 FUNCTION is_flexfield_qualifier_valid(p_application_id IN NUMBER,
1084 p_id_flex_code IN VARCHAR2,
1085 p_segment_attribute_type IN VARCHAR2)
1086 RETURN BOOLEAN
1087 IS
1088 l_cnt NUMBER;
1089 BEGIN
1090 SELECT count(*)
1091 INTO l_cnt
1092 FROM fnd_segment_attribute_types
1093 WHERE application_id = p_application_id
1094 AND id_flex_code = p_id_flex_code
1095 AND segment_attribute_type = p_segment_attribute_type;
1096
1097 IF (l_cnt > 0)
1098 THEN
1099 return(TRUE);
1100 ELSE
1101 return(FALSE);
1102 END IF;
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 return(FALSE);
1107 END is_flexfield_qualifier_valid;
1108
1109 -- ------------------------------------------------------------------------
1110 -- Gets the segment number corresponding to the qualifier
1111 -- name entered. Segment number is the display order of the segment
1112 -- not to be confused with the SEGMENT_NUM column of the
1113 -- FND_ID_FLEX_SEGMENTS table. Returns TRUE segment_number if ok,
1114 -- or FALSE and sets error using FND_MESSAGES on error.
1115 -- If the qualifier is non-unique, it gives the first segment with this
1116 -- qualifier that appears in flexfield window. (Ref: FF guide page 295).
1117 -- ------------------------------------------------------------------------
1118 FUNCTION get_qualifier_segnum(appl_id IN NUMBER,
1119 key_flex_code IN VARCHAR2,
1120 structure_number IN NUMBER,
1121 flex_qual_name IN VARCHAR2,
1122 segment_number OUT nocopy NUMBER)
1123 RETURN BOOLEAN
1124 IS
1125 l_key VARCHAR2(2000);
1126 l_return_code VARCHAR2(1);
1127 l_value fnd_plsql_cache.generic_cache_value_type;
1128 l_segment_number NUMBER;
1129 this_segment_num NUMBER;
1130 BEGIN
1131 --
1132 -- Create the key.
1133 --
1134 l_key := appl_id || '.' || key_flex_code || '.' || structure_number || '.' || flex_qual_name;
1135
1136 --
1137 -- First check the cache.
1138 --
1139 fnd_plsql_cache.generic_1to1_get_value(g_snum_generic_1to1_controller,
1140 g_snum_generic_1to1_storage,
1141 l_key,
1142 l_value,
1143 l_return_code);
1144 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1145 --
1146 -- Entity is in the cache, populate the return value.
1147 --
1148 l_segment_number := l_value.number_1;
1149 ELSE
1150 --
1151 -- Entity is not in the cache, get it from DB.
1152 --
1153 SELECT s.segment_num
1154 INTO this_segment_num
1155 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
1156 fnd_segment_attribute_types sat
1157 WHERE s.application_id = appl_id
1158 AND s.id_flex_code = key_flex_code
1159 AND s.id_flex_num = structure_number
1160 AND s.enabled_flag = 'Y'
1161 AND s.application_column_name = sav.application_column_name
1162 AND sav.application_id = appl_id
1163 AND sav.id_flex_code = key_flex_code
1164 AND sav.id_flex_num = structure_number
1165 AND sav.attribute_value = 'Y'
1166 AND sav.segment_attribute_type = sat.segment_attribute_type
1167 AND sat.application_id = appl_id
1168 AND sat.id_flex_code = key_flex_code
1169 --AND sat.unique_flag = 'Y' -- We need to look for non-unique qual. also.
1170 AND sat.segment_attribute_type = flex_qual_name
1171 AND ROWNUM < 2
1172 AND '$Header: AFFFXPAB.pls 120.8.12000000.1 2007/01/18 13:19:33 appldev ship $' IS NOT NULL
1173 ORDER BY s.segment_num; -- Order By and rownum < 2 ensures we get first segment.
1174
1175 SELECT count(segment_num)
1176 INTO l_segment_number
1177 FROM fnd_id_flex_segments
1178 WHERE application_id = appl_id
1179 AND id_flex_code = key_flex_code
1180 AND id_flex_num = structure_number
1181 AND enabled_flag = 'Y'
1182 AND segment_num <= this_segment_num
1183 AND '$Header: AFFFXPAB.pls 120.8.12000000.1 2007/01/18 13:19:33 appldev ship $' IS NOT NULL;
1184
1185 --
1186 -- Create the cache value, and populate it with values came from DB.
1187 --
1188 fnd_plsql_cache.generic_cache_new_value
1189 (x_value => l_value,
1190 p_number_1 => l_segment_number);
1191 --
1192 -- Put the value in cache.
1193 --
1194 fnd_plsql_cache.generic_1to1_put_value(g_snum_generic_1to1_controller,
1195 g_snum_generic_1to1_storage,
1196 l_key,
1197 l_value);
1198 END IF;
1199 segment_number := l_segment_number;
1200
1201 return(TRUE);
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 return(FALSE);
1206 END get_qualifier_segnum;
1207
1208 /* ----------------------------------------------------------------------- */
1209 /* Converts character representation of a number to a number. */
1210 /* Returns TRUE if it's a valid number, and FALSE otherwise. */
1211 FUNCTION isa_number(teststr IN VARCHAR2,
1212 outnum OUT nocopy NUMBER) RETURN BOOLEAN IS
1213 BEGIN
1214 outnum := to_number(teststr);
1215 return(TRUE);
1216 EXCEPTION
1217 WHEN OTHERS then
1218 return(FALSE);
1219 END isa_number;
1220
1221 -- Procedures/functions private to get_kff_seg_column_names end here.
1222
1223
1224 BEGIN
1225
1226 kff_parse_string(p_segments, l_include, l_exclude);
1227
1228 l_nsegments := 0;
1229
1230 -- Get segment name, display flag and value set id for the KFF structure.
1231
1232 --
1233 -- Create the key.
1234 --
1235 l_key := p_kff_str.application_id||'.'||p_kff_str.id_flex_code||'.'||p_kff_str.id_flex_num;
1236 --
1237 -- First check the cache.
1238 --
1239 fnd_plsql_cache.generic_1tom_get_values(seginf_generic_1tom_controller,
1240 seginf_generic_1tom_storage,
1241 l_key,
1242 l_numof_values,
1243 l_values,
1244 l_return_code);
1245 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1246 --
1247 -- Entity is in the cache, populate the return values.
1248 --
1249 FOR i IN 1..l_numof_values LOOP
1250 l_nsegments := l_nsegments + 1;
1251 l_segment_info_array(l_nsegments).application_column_name := l_values(i).varchar2_1;
1252 l_segment_info_array(l_nsegments).enabled_flag := l_values(i).varchar2_2;
1253 l_segment_info_array(l_nsegments).display_flag := l_values(i).varchar2_3;
1254 l_segment_info_array(l_nsegments).flex_value_set_id := l_values(i).number_1;
1255 END LOOP;
1256 ELSE
1257 FOR l_segment_info IN c_segment_info(p_kff_str.application_id, p_kff_str.id_flex_code, p_kff_str.id_flex_num) LOOP
1258 l_nsegments := l_nsegments + 1;
1259 l_segment_info_array(l_nsegments).application_column_name := l_segment_info.application_column_name;
1260 l_segment_info_array(l_nsegments).enabled_flag := l_segment_info.enabled_flag;
1261 l_segment_info_array(l_nsegments).display_flag := l_segment_info.display_flag;
1262 l_segment_info_array(l_nsegments).flex_value_set_id := l_segment_info.flex_value_set_id;
1263 --
1264 -- Create the cache value, and populate it with values came from DB.
1265 --
1266 fnd_plsql_cache.generic_cache_new_value
1267 (x_value => l_values(l_nsegments),
1268 p_varchar2_1 => l_segment_info.application_column_name,
1269 p_varchar2_2 => l_segment_info.enabled_flag,
1270 p_varchar2_3 => l_segment_info.display_flag,
1271 p_number_1 => l_segment_info.flex_value_set_id);
1272 END LOOP;
1273 --
1274 -- Put the values in cache.
1275 --
1276 fnd_plsql_cache.generic_1tom_put_values(seginf_generic_1tom_controller,
1277 seginf_generic_1tom_storage,
1278 l_key,
1279 l_nsegments,
1280 l_values);
1281 END IF;
1282
1283 -- Determine if a segment is to be included or excluded.
1284
1285 IF (l_include(0) = 'ALL')
1286 THEN
1287 FOR i IN 1..l_nsegments LOOP
1288 l_segment_info_array(i).to_include := 'Y';
1289 END LOOP;
1290 ELSE
1291 FOR i IN 1..l_nsegments LOOP
1292 l_segment_info_array(i).to_include := 'N';
1293 END LOOP;
1294 IF (isa_number(l_include(0), l_tmp_number))
1295 THEN
1296 IF (l_tmp_number between 1 and l_nsegments)
1297 THEN
1298 l_segment_info_array(l_tmp_number).to_include := 'Y';
1299 END IF;
1300 ELSE
1301 IF (is_flexfield_qualifier_valid(p_kff_str.application_id, p_kff_str.id_flex_code, l_include(0)))
1302 THEN
1303 IF (get_qualifier_segnum(p_kff_str.application_id, p_kff_str.id_flex_code, p_kff_str.id_flex_num, l_include(0), l_tmp_number))
1304 THEN
1305 l_segment_info_array(l_tmp_number).to_include := 'Y';
1306 END IF;
1307 ELSE
1308 raise_error(error_invalid_seg_qual, 'Invalid Qualifier: '||l_include(0));
1309 END IF;
1310 END IF;
1311 END IF;
1312
1313 IF l_exclude.count <> 0
1314 THEN
1315 FOR i IN l_exclude.first .. l_exclude.last
1316 LOOP
1317 IF (isa_number(l_exclude(i), l_tmp_number))
1318 THEN
1319 IF (l_tmp_number between 1 and l_nsegments)
1320 THEN
1321 l_segment_info_array(l_tmp_number).to_include := 'N';
1322 END IF;
1323 ELSE
1324 IF (is_flexfield_qualifier_valid(p_kff_str.application_id, p_kff_str.id_flex_code, l_exclude(i)))
1325 THEN
1326 IF (get_qualifier_segnum(p_kff_str.application_id, p_kff_str.id_flex_code, p_kff_str.id_flex_num, l_exclude(i), l_tmp_number))
1327 THEN
1328 l_segment_info_array(l_tmp_number).to_include := 'N';
1329 END IF;
1330 END IF;
1331 END IF;
1332 END LOOP;
1333 END IF;
1334
1335 -- See if a segment is a parent and if yes, assign child segment number
1336 -- and set is_parent flag to 'Y'.
1337
1338 FOR i IN 1..l_nsegments
1339 LOOP
1340 l_segment_info_array(i).is_parent := 'N';
1341
1342 IF (l_segment_info_array(i).flex_value_set_id IS NOT NULL) THEN
1343 --
1344 -- Create the key.
1345 --
1346 l_key := l_segment_info_array(i).flex_value_set_id;
1347 --
1348 -- First check the cache.
1349 --
1350 fnd_plsql_cache.generic_1to1_get_value(p_vsid_generic_1to1_controller,
1351 p_vsid_generic_1to1_storage,
1352 l_key,
1353 l_value,
1354 l_return_code);
1355 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1356 --
1357 -- Entity is in the cache, populate the return value.
1358 --
1359 l_parent_value_set_id := l_value.number_1;
1360 ELSE
1361 --
1362 -- Entity is not in the cache, get it from DB.
1363 --
1364 SELECT parent_flex_value_set_id INTO l_parent_value_set_id
1365 FROM fnd_flex_value_sets
1366 WHERE flex_value_set_id = l_segment_info_array(i).flex_value_set_id;
1367 --
1368 -- Create the cache value, and populate it with values came from DB.
1369 --
1370 fnd_plsql_cache.generic_cache_new_value
1371 (x_value => l_value,
1372 p_number_1 => l_parent_value_set_id);
1373 --
1374 -- Put the value in cache.
1375 --
1376 fnd_plsql_cache.generic_1to1_put_value(p_vsid_generic_1to1_controller, p_vsid_generic_1to1_storage,
1377 l_key,
1378 l_value);
1379 END IF;
1380
1381 END IF;
1382
1383 IF (l_parent_value_set_id is NOT NULL) THEN
1384 FOR j IN reverse 1..i
1385 LOOP
1386 IF (l_segment_info_array(j).flex_value_set_id = l_parent_value_set_id)
1387 THEN
1388 l_segment_info_array(j).is_parent := 'Y';
1389 l_segment_info_array(j).child_segment_num := i;
1390 exit;
1391 END IF;
1392 END LOOP;
1393 END IF;
1394
1395 END LOOP;
1396
1397 l_display_index := 0;
1398
1399 -- Decide which all segments are to be displayed. Display a segment if:
1400 -- 1) If a segment is enabled AND
1401 -- 2) If p_segments_mode is ALL_ENABLED OR p_segments_mode is DISPLAYED_ONLY
1402 -- and segment is displayed AND
1403 -- 3) A segment's include flag is 'Y' OR
1404 -- 4) p_show_parent_segments is 'Y', a segment is a parent and child
1405 -- segment's include flag is 'Y'.
1406 -- ie, it should be 1 AND 2 AND (3 OR 4).
1407 -- The basic idea is to find out whether segments are to be displayed or not
1408 -- using the input p_segments irrespective of the enabled/displayed flag
1409 -- in segments form and then finally combine both.
1410
1411 FOR i IN 1..l_nsegments
1412 LOOP
1413
1414 IF (l_segment_info_array(i).enabled_flag = 'Y')
1415 THEN
1416 IF ((p_segments_mode = 'ALL_ENABLED') OR
1417 ((p_segments_mode = 'DISPLAYED_ONLY') AND l_segment_info_array(i).display_flag = 'Y'))
1418 THEN
1419 IF ((l_segment_info_array(i).to_include = 'Y') OR
1420 (p_show_parent_segments = 'Y' AND l_segment_info_array(i).is_parent = 'Y' AND l_segment_info_array(l_segment_info_array(i).child_segment_num).to_include = 'Y'))
1421 THEN
1422 l_display_index := l_display_index + 1;
1423 l_column_names(l_display_index) := l_segment_info_array(i).application_column_name;
1424 END IF;
1425 END IF;
1426 END IF;
1427
1428 END LOOP;
1429
1430 x_column_names := l_column_names;
1431 x_numof_column_names := l_display_index;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 raise_others('get_kff_seg_column_names',
1435 p_kff_str.application_id,
1436 p_kff_str.id_flex_code,
1437 p_kff_str.id_flex_num,
1438 p_segments_mode,
1439 p_segments,
1440 p_show_parent_segments);
1441 END get_kff_seg_column_names;
1442
1443 --------------------------------------------------------------------------------
1444 -- Returns KFF Segment
1445 --------------------------------------------------------------------------------
1446 PROCEDURE get_kff_seg
1447 (p_kff_str IN kff_str_type,
1448 p_application_column_name IN fnd_id_flex_segments.application_column_name%TYPE,
1449 x_kff_seg OUT nocopy kff_seg_type)
1450 IS
1451 l_key VARCHAR2(2000);
1452 l_value fnd_plsql_cache.generic_cache_value_type;
1453 l_return_code VARCHAR2(1);
1454 l_kff_seg kff_seg_type;
1455 BEGIN
1456 --
1457 -- Create the key.
1458 --
1459 l_key := p_kff_str.application_id || '.' || p_kff_str.id_flex_code || '.' || p_kff_str.id_flex_num || '.' || p_application_column_name;
1460 --
1461 -- First check the cache.
1462 --
1463 fnd_plsql_cache.generic_1to1_get_value(g_seg_generic_1to1_controller,
1464 g_seg_generic_1to1_storage,
1465 l_key,
1466 l_value,
1467 l_return_code);
1468 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1469 --
1470 -- Entity is in the cache, populate the return value.
1471 --
1472 l_kff_seg.application_id := l_value.number_1;
1473 l_kff_seg.id_flex_code := l_value.varchar2_1;
1474 l_kff_seg.id_flex_num := l_value.number_2;
1475 l_kff_seg.application_column_name := l_value.varchar2_2;
1476 ELSE
1477 --
1478 -- Entity is not in the cache, get it from DB.
1479 --
1480 BEGIN
1481 SELECT fifsg.*
1482 INTO l_kff_seg
1483 FROM fnd_id_flex_segments fifsg
1484 WHERE fifsg.application_id = p_kff_str.application_id
1485 AND fifsg.id_flex_code = p_kff_str.id_flex_code
1486 AND fifsg.id_flex_num = p_kff_str.id_flex_num
1487 AND fifsg.application_column_name = p_application_column_name;
1488 --
1489 -- Create the cache value, and populate it with values came from DB.
1490 --
1491 fnd_plsql_cache.generic_cache_new_value
1492 (x_value => l_value,
1493 p_number_1 => l_kff_seg.application_id,
1494 p_varchar2_1 => l_kff_seg.id_flex_code,
1495 p_number_2 => l_kff_seg.id_flex_num,
1496 p_varchar2_2 => l_kff_seg.application_column_name);
1497
1498 --
1499 -- Put the value in cache.
1500 --
1501 fnd_plsql_cache.generic_1to1_put_value(g_seg_generic_1to1_controller,
1502 g_seg_generic_1to1_storage,
1503 l_key,
1504 l_value);
1505 EXCEPTION
1506 WHEN no_data_found THEN
1507 raise_no_data_found
1508 ('Key Flexfield Segment',
1509 'application_id', p_kff_str.application_id,
1510 'id_flex_code', p_kff_str.id_flex_code,
1511 'id_flex_num', p_kff_str.id_flex_num,
1512 'application_column_name', p_application_column_name);
1513 END;
1514 END IF;
1515 --
1516 -- Return the value
1517 --
1518 x_kff_seg := l_kff_seg;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 raise_others('get_kff_seg',
1522 p_kff_str.application_id,
1523 p_kff_str.id_flex_code,
1524 p_kff_str.id_flex_num,
1525 p_application_column_name);
1526 END get_kff_seg;
1527
1528 --------------------------------------------------------------------------------
1529 -- Returns KFF Segment TL
1530 --------------------------------------------------------------------------------
1531 PROCEDURE get_kff_seg_tl
1532 (p_kff_seg IN kff_seg_type,
1533 x_kff_seg_tl OUT nocopy kff_seg_tl_type)
1534 IS
1535 l_key VARCHAR2(2000);
1536 l_value fnd_plsql_cache.generic_cache_value_type;
1537 l_return_code VARCHAR2(1);
1538 l_kff_seg_tl kff_seg_tl_type;
1539 BEGIN
1540 --
1541 -- Create the key.
1542 --
1543 l_key := p_kff_seg.application_id || '.' || p_kff_seg.id_flex_code || '.' || p_kff_seg.id_flex_num || '.' || p_kff_seg.application_column_name || '.' || userenv('LANG');
1544 --
1545 -- First check the cache.
1546 --
1547 fnd_plsql_cache.generic_1to1_get_value(g_segt_generic_1to1_controller,
1548 g_segt_generic_1to1_storage,
1549 l_key,
1550 l_value,
1551 l_return_code);
1552 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
1553 --
1554 -- Entity is in the cache, populate the return value.
1555 --
1556 l_kff_seg_tl.form_above_prompt := l_value.varchar2_1;
1557 l_kff_seg_tl.form_left_prompt := l_value.varchar2_2;
1558 ELSE
1559 --
1560 -- Entity is not in the cache, get it from DB.
1561 --
1562 BEGIN
1563 SELECT fifsgtl.*
1564 INTO l_kff_seg_tl
1565 FROM fnd_id_flex_segments_tl fifsgtl
1566 WHERE fifsgtl.application_id = p_kff_seg.application_id
1567 AND fifsgtl.id_flex_code = p_kff_seg.id_flex_code
1568 AND fifsgtl.id_flex_num = p_kff_seg.id_flex_num
1569 AND fifsgtl.application_column_name = p_kff_seg.application_column_name
1570 AND fifsgtl.language = userenv('LANG');
1571
1572 --
1573 -- Create the cache value, and populate it with values came from DB.
1574 --
1575 fnd_plsql_cache.generic_cache_new_value
1576 (x_value => l_value,
1577 p_varchar2_1 => l_kff_seg_tl.form_above_prompt,
1578 p_varchar2_2 => l_kff_seg_tl.form_left_prompt);
1579
1580 --
1581 -- Put the value in cache.
1582 --
1583 fnd_plsql_cache.generic_1to1_put_value(g_segt_generic_1to1_controller,
1584 g_segt_generic_1to1_storage,
1585 l_key,
1586 l_value);
1587 EXCEPTION
1588 WHEN no_data_found THEN
1589 raise_no_data_found
1590 ('Key Flexfield Segment TL',
1591 'application_id', p_kff_seg.application_id,
1592 'id_flex_code', p_kff_seg.id_flex_code,
1593 'id_flex_num', p_kff_seg.id_flex_num,
1594 'application_column_name', p_kff_seg.application_column_name,
1595 'language', userenv('LANG'));
1596 END;
1597 END IF;
1598 --
1599 -- Return the value
1600 --
1601 x_kff_seg_tl := l_kff_seg_tl;
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 raise_others('get_kff_seg_tl',
1605 p_kff_seg.application_id,
1606 p_kff_seg.id_flex_code,
1607 p_kff_seg.id_flex_num,
1608 p_kff_seg.application_column_name);
1609 END get_kff_seg_tl;
1610
1611
1612 --------------------------------------------------------------------------------
1613 -- Builds a DECODE clause
1614 --
1615 -- if p_numof_decode_elements = 1
1616 -- --------------------------------------------------
1617 -- result1
1618 --
1619 -- if p_numof_decode_elements between 2 and 126
1620 -- --------------------------------------------------
1621 -- DECODE(expression,
1622 -- search1, result1,
1623 -- search2, result2,
1624 -- ...
1625 -- default);
1626 --
1627 --
1628 -- if p_numof_decode_elements > 126
1629 -- --------------------------------------------------
1630 -- DECODE(expression,
1631 -- search1, result1,
1632 -- search2, result2,
1633 -- ...
1634 -- search126, result126,
1635 -- DECODE(expression,
1636 -- search127, result127,
1637 -- search128, result128,
1638 -- ...
1639 -- search252, result252,
1640 -- DECODE(expression,
1641 -- search253, result253,
1642 -- search254, result254,
1643 -- ...
1644 -- default))...));
1645 --
1646 --
1647 -- EXAMPLES
1648 --
1649 -- SELECT:
1650 --
1651 -- l_decode_elements(1).search := 101;
1652 -- l_decode_elements(1).result := SEGMENT1
1653 --
1654 -- l_decode_elements(2).search := 102;
1655 -- l_decode_elements(2).result := SEGMENT2 || '-' || SEGMENT3
1656 --
1657 -- l_decode_elements(3).search := 103;
1658 -- l_decode_elements(3).result := my_api.function_call(some_inputs...)
1659 --
1660 --
1661 -- Decode(chart_of_accounts_id,
1662 -- 101, SEGMENT1,
1663 -- 102, SEGMENT2 || '-' || SEGMENT3,
1664 -- 103, my_api.function_call(some_inputs...),
1665 -- NULL)
1666 --
1667 --
1668 -- ORDER BY
1669 --
1670 -- l_decode_elements(1).search := 101;
1671 -- l_decode_elements(1).result := SEGMENT1
1672 --
1673 -- l_decode_elements(2).search := 102;
1674 -- l_decode_elements(2).result := SEGMENT2 || ',' || SEGMENT3
1675 --
1676 -- l_decode_elements(3).search := 103;
1677 -- l_decode_elements(3).result := SEGMENT2
1678 --
1679 --
1680 -- Decode(chart_of_accounts_id,
1681 -- 101, SEGMENT1,
1682 -- 102, SEGMENT2 || ',' || SEGMENT3,
1683 -- 103, SEGMENT2,
1684 -- NULL)
1685 --
1686 --------------------------------------------------------------------------------
1687 FUNCTION get_decode_recursive
1688 (p_expression IN VARCHAR2,
1689 p_numof_decode_elements IN NUMBER,
1690 p_decode_elements IN decode_elements_type,
1691 p_begin_index IN NUMBER,
1692 p_default IN VARCHAR2)
1693 RETURN VARCHAR2
1694 IS
1695 l_end_index NUMBER;
1696 l_decode_clause VARCHAR2(32000);
1697 BEGIN
1698 --
1699 -- Compute the end index
1700 --
1701 IF (p_begin_index + max_numof_decode_elements - 1 < p_numof_decode_elements) THEN
1702 l_end_index := p_begin_index + max_numof_decode_elements - 1;
1703 ELSE
1704 l_end_index := p_numof_decode_elements;
1705 END IF;
1706
1707 --
1708 -- Begin DECODE
1709 --
1710 l_decode_clause := 'DECODE(' || p_expression || ', ';
1711
1712 FOR i IN p_begin_index .. l_end_index LOOP
1713 --
1714 -- Append <search>, <result>,
1715 --
1716 l_decode_clause := l_decode_clause ||
1717 p_decode_elements(i).search || ', ' ||
1718 p_decode_elements(i).result || ', ';
1719 END LOOP;
1720
1721 --
1722 -- If there are more elements then do a recursive call
1723 --
1724 IF (l_end_index < p_numof_decode_elements) THEN
1725 --
1726 -- Append nested DECODE
1727 --
1728 l_decode_clause := l_decode_clause ||
1729 get_decode_recursive(p_expression,
1730 p_numof_decode_elements,
1731 p_decode_elements,
1732 l_end_index + 1,
1733 p_default);
1734 ELSE
1735 --
1736 -- Append <default>
1737 --
1738 l_decode_clause := l_decode_clause || p_default;
1739 END IF;
1740
1741 --
1742 -- End DECODE
1743 --
1744 l_decode_clause := l_decode_clause || ')';
1745
1746 RETURN (l_decode_clause);
1747
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 raise_others('get_decode_recursive',
1751 p_expression,
1752 p_numof_decode_elements,
1753 p_begin_index,
1754 p_default);
1755 END get_decode_recursive;
1756
1757 PROCEDURE get_decode_clause
1758 (p_expression IN VARCHAR2,
1759 p_numof_decode_elements IN NUMBER,
1760 p_decode_elements IN decode_elements_type,
1761 p_default IN VARCHAR2,
1762 x_decode_clause OUT nocopy VARCHAR2)
1763 IS
1764 BEGIN
1765 --
1766 -- Check to see if we need a decode statement.
1767 --
1768 IF (p_numof_decode_elements = 1) THEN
1769 --
1770 -- Only one element no decode needed
1771 --
1772 x_decode_clause := p_decode_elements(1).result;
1773
1774 ELSE
1775 --
1776 -- Call the recursive API
1777 --
1778 x_decode_clause := get_decode_recursive(p_expression,
1779 p_numof_decode_elements,
1780 p_decode_elements,
1781 1,
1782 p_default);
1783 END IF;
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 raise_others('get_decode_clause',
1787 p_expression,
1788 p_numof_decode_elements,
1789 p_default);
1790 END get_decode_clause;
1791
1792 --------------------------------------------------------------------------------
1793 -- Concatenates Column Names
1794 --
1795 -- alias_prefix || column1 || <delimiter> || alias_prefix || column2 ...
1796 --
1797 --------------------------------------------------------------------------------
1798 PROCEDURE get_concat_column_names_clause
1799 (p_alias_prefix IN VARCHAR2,
1800 p_numof_column_names IN VARCHAR2,
1801 p_column_names IN varchar2_30_array_type,
1802 p_delimiter IN VARCHAR2,
1803 x_concat_column_names_clause OUT nocopy VARCHAR2)
1804 IS
1805 l_concat_column_names_clause VARCHAR2(32000);
1806 BEGIN
1807 l_concat_column_names_clause := NULL;
1808 FOR i IN 1 .. p_numof_column_names LOOP
1809 --
1810 -- If there are more than 1 column names then append delimiter
1811 --
1812 IF (i > 1) THEN
1813 l_concat_column_names_clause := l_concat_column_names_clause ||
1814 p_delimiter;
1815 END IF;
1816
1817 --
1818 -- Append <prefix || column>
1819 --
1820 l_concat_column_names_clause := l_concat_column_names_clause ||
1821 p_alias_prefix || p_column_names(i);
1822 END LOOP;
1823
1824 x_concat_column_names_clause := l_concat_column_names_clause;
1825 EXCEPTION
1826 WHEN OTHERS THEN
1827 raise_others('get_concat_column_names_clause',
1828 p_alias_prefix,
1829 p_numof_column_names,
1830 p_delimiter);
1831 END get_concat_column_names_clause;
1832
1833
1834 --
1835 -- Public APIs
1836 --
1837
1838 -- ======================================================================
1839 PROCEDURE set_debug_mode
1840 (p_debug_mode IN VARCHAR2)
1841 IS
1842 BEGIN
1843 validate_argument(arg_debug_mode, p_debug_mode);
1844
1845 IF (p_debug_mode = debug_mode_off) THEN
1846 g_debug_enabled := FALSE;
1847
1848 ELSIF (p_debug_mode = debug_mode_on) THEN
1849 g_debug_enabled := TRUE;
1850
1851 END IF;
1852
1853 init_debug();
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 raise_others('set_debug_mode',
1857 p_debug_mode);
1858 END set_debug_mode;
1859
1860 -- ======================================================================
1861 PROCEDURE get_debug
1862 (x_debug OUT nocopy VARCHAR2)
1863 IS
1864 BEGIN
1865 x_debug := g_debug;
1866
1867 init_debug();
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 raise_others('get_debug');
1871 END get_debug;
1872
1873 -- ======================================================================
1874 PROCEDURE kff_flexfield_metadata
1875 (p_lexical_name IN VARCHAR2,
1876 p_application_short_name IN fnd_application.application_short_name%TYPE,
1877 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
1878 p_metadata_type IN VARCHAR2,
1879 x_metadata OUT nocopy VARCHAR2)
1880 IS
1881 BEGIN
1882 x_metadata := 'For future use, not implemented yet.';
1883 END kff_flexfield_metadata;
1884
1885 -- ======================================================================
1886 PROCEDURE kff_structure_metadata
1887 (p_lexical_name IN VARCHAR2,
1888 p_application_short_name IN fnd_application.application_short_name%TYPE,
1889 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
1890 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE,
1891 p_metadata_type IN VARCHAR2,
1892 x_metadata OUT nocopy VARCHAR2)
1893 IS
1894 BEGIN
1895 x_metadata := 'For future use, not implemented yet.';
1896 END kff_structure_metadata;
1897
1898 -- ======================================================================
1899 PROCEDURE kff_segment_metadata
1900 (p_lexical_name IN VARCHAR2,
1901 p_application_short_name IN fnd_application.application_short_name%TYPE,
1902 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
1903 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE,
1904 p_application_column_name IN fnd_id_flex_segments.application_column_name%TYPE,
1905 p_metadata_type IN VARCHAR2,
1906 x_metadata OUT nocopy VARCHAR2)
1907 IS
1908 BEGIN
1909 x_metadata := 'For future use, not implemented yet.';
1910 END kff_segment_metadata;
1911
1912 -- ======================================================================
1913 PROCEDURE kff_segments_metadata
1914 (p_lexical_name IN VARCHAR2,
1915 p_application_short_name IN fnd_application.application_short_name%TYPE,
1916 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
1917 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE,
1918 p_segments IN VARCHAR2,
1919 p_show_parent_segments IN VARCHAR2,
1920 p_metadata_type IN VARCHAR2,
1921 x_metadata OUT nocopy VARCHAR2)
1922 IS
1923 l_kff_flx kff_flx_type;
1924 l_kff_str kff_str_type;
1925 l_kff_seg kff_seg_type;
1926 l_kff_seg_tl kff_seg_tl_type;
1927
1928 l_column_names varchar2_30_array_type;
1929 l_numof_column_names NUMBER;
1930
1931 l_metadata VARCHAR2(32000);
1932 BEGIN
1933 --
1934 -- Validate Input Arguments
1935 --
1936 validate_argument(arg_lexical_name, p_lexical_name);
1937 validate_argument(arg_segments, p_segments);
1938 validate_argument(arg_show_parent_segments, p_show_parent_segments);
1939 validate_argument(arg_metadata_type, p_metadata_type);
1940
1941 --
1942 -- Get the Key Flexfield
1943 --
1944 get_kff_flx(p_application_short_name => p_application_short_name,
1945 p_id_flex_code => p_id_flex_code,
1946 x_kff_flx => l_kff_flx);
1947
1948 --
1949 -- Get the Structure
1950 --
1951 get_kff_str(p_kff_flx => l_kff_flx,
1952 p_id_flex_num => p_id_flex_num,
1953 x_kff_str => l_kff_str);
1954
1955 --
1956 -- Get the Column Names
1957 --
1958 get_kff_seg_column_names(p_kff_str => l_kff_str,
1959 p_segments_mode => segments_mode_displayed_only,
1960 p_segments => p_segments,
1961 p_show_parent_segments => p_show_parent_segments,
1962 x_numof_column_names => l_numof_column_names,
1963 x_column_names => l_column_names);
1964
1965 --
1966 -- Loop Through Column Names
1967 --
1968 l_metadata := NULL;
1969 FOR i IN 1 .. l_numof_column_names LOOP
1970 --
1971 -- Use delimiter to concatenate
1972 --
1973 IF (i > 1) THEN
1974 l_metadata := l_metadata || l_kff_str.concatenated_segment_delimiter;
1975 END IF;
1976
1977 --
1978 -- Get the Segment
1979 --
1980 get_kff_seg(p_kff_str => l_kff_str,
1981 p_application_column_name => l_column_names(i),
1982 x_kff_seg => l_kff_seg);
1983
1984 --
1985 -- Get the Segment TL
1986 --
1987 get_kff_seg_tl(p_kff_seg => l_kff_seg,
1988 x_kff_seg_tl => l_kff_seg_tl);
1989
1990 --
1991 -- Build the metadata
1992 --
1993 IF (p_metadata_type = metadata_segments_above_prompt) THEN
1994 l_metadata := l_metadata || l_kff_seg_tl.form_above_prompt;
1995
1996 ELSIF (p_metadata_type = metadata_segments_left_prompt) THEN
1997 l_metadata := l_metadata || l_kff_seg_tl.form_left_prompt;
1998
1999 END IF;
2000 END LOOP;
2001
2002 x_metadata := l_metadata;
2003
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006 raise_others('kff_segments_metadata',
2007 p_lexical_name,
2008 p_application_short_name,
2009 p_id_flex_code,
2010 p_id_flex_num,
2011 p_segments,
2012 p_show_parent_segments,
2013 p_metadata_type);
2014 END kff_segments_metadata;
2015
2016
2017 -- ======================================================================
2018 -- KFF Combination Process API, version #1
2019 -- ======================================================================
2020 FUNCTION process_kff_combination_1
2021 (p_lexical_name IN VARCHAR2,
2022 p_application_short_name IN fnd_application.application_short_name%TYPE,
2023 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
2024 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE,
2025 p_data_set IN fnd_id_flex_structures.id_flex_num%TYPE,
2026 p_ccid IN NUMBER,
2027 p_segments IN VARCHAR2,
2028 p_show_parent_segments IN VARCHAR2,
2029 p_output_type IN VARCHAR2)
2030 RETURN VARCHAR2
2031 IS
2032 l_return VARCHAR2(32000);
2033 l_is_ccid_valid BOOLEAN;
2034 l_is_secured BOOLEAN;
2035 l_kff_flx kff_flx_type;
2036 l_kff_str kff_str_type;
2037
2038 l_column_names varchar2_30_array_type;
2039 l_numof_column_names NUMBER;
2040
2041 l_segment_numbers number_array_type;
2042 l_segment_count NUMBER;
2043
2044 l_values fnd_flex_server1.stringarray;
2045
2046 l_flex_values fnd_flex_server1.stringarray;
2047 n_segments NUMBER;
2048
2049 l_key VARCHAR2(2000);
2050 l_value fnd_plsql_cache.generic_cache_value_type;
2051 l_return_code VARCHAR2(1);
2052 BEGIN
2053 --
2054 -- For performance reasons, this API assumes that all of the input
2055 -- arguments are valid.
2056 --
2057
2058 --
2059 -- Create the Key.
2060 --
2061 l_key := p_application_short_name||'.'||p_id_flex_code||'.'||p_id_flex_num||'.'||p_data_set||'.'||p_ccid||'.'||p_segments||'.'||p_show_parent_segments||'.'||p_output_type;
2062
2063 --
2064 -- First check the cache.
2065 --
2066 fnd_plsql_cache.generic_1to1_get_value(prcomb_generic_1to1_controller,
2067 prcomb_generic_1to1_storage,
2068 l_key,
2069 l_value,
2070 l_return_code);
2071
2072 IF (l_return_code = fnd_plsql_cache.CACHE_FOUND) THEN
2073 --
2074 -- Entity is in the cache, populate the return value.
2075 --
2076 l_return := l_value.varchar2_1;
2077 GOTO goto_return;
2078
2079 ELSE
2080 --
2081 -- Entity is not in the cache, get it from DB.
2082 --
2083
2084 --
2085 -- Validate the CCID
2086 --
2087 l_is_ccid_valid := fnd_flex_keyval.validate_ccid
2088 (appl_short_name => p_application_short_name,
2089 key_flex_code => p_id_flex_code,
2090 structure_number => p_id_flex_num,
2091 combination_id => p_ccid,
2092 displayable => 'ALL',
2093 data_set => p_data_set,
2094 vrule => NULL,
2095 security => 'CHECK',
2096 get_columns => NULL,
2097 resp_appl_id => fnd_global.resp_appl_id,
2098 resp_id => fnd_global.resp_id,
2099 user_id => fnd_global.user_id,
2100 select_comb_from_view => NULL);
2101
2102 IF (NOT l_is_ccid_valid) THEN
2103 l_return := fnd_flex_keyval.error_message();
2104 GOTO goto_cache_and_return;
2105 END IF;
2106
2107 --
2108 -- At this point CCID is valid
2109 --
2110
2111
2112 --
2113 -- Handle single value output types first.
2114 --
2115
2116 IF (p_output_type = output_type_security) THEN
2117 l_is_secured := fnd_flex_keyval.is_secured();
2118
2119 IF (l_is_secured) THEN
2120 l_return := 'Y';
2121 ELSE
2122 l_return := 'N';
2123 END IF;
2124 GOTO goto_cache_and_return;
2125 END IF;
2126
2127
2128 --
2129 -- At this point we have multi value output type.
2130 --
2131
2132 --
2133 -- !!! Performance Problem : This section should be improved.
2134 --
2135
2136 --
2137 -- Get the Key Flexfield
2138 --
2139 get_kff_flx(p_application_short_name => p_application_short_name,
2140 p_id_flex_code => p_id_flex_code,
2141 x_kff_flx => l_kff_flx);
2142
2143 --
2144 -- Get the Structure
2145 --
2146 get_kff_str(p_kff_flx => l_kff_flx,
2147 p_id_flex_num => p_id_flex_num,
2148 x_kff_str => l_kff_str);
2149
2150 --
2151 -- Get the Column Names
2152 --
2153 get_kff_seg_column_names(p_kff_str => l_kff_str,
2154 p_segments_mode => segments_mode_displayed_only,
2155 p_segments => p_segments,
2156 p_show_parent_segments => p_show_parent_segments,
2157 x_numof_column_names => l_numof_column_names,
2158 x_column_names => l_column_names);
2159
2160
2161 IF (l_numof_column_names = 0)
2162 THEN
2163 l_return := NULL;
2164 GOTO goto_cache_and_return;
2165 END IF;
2166 --
2167 -- Convert Column Names to Segment Numbers
2168 --
2169 l_segment_count := fnd_flex_keyval.segment_count();
2170
2171 IF (p_output_type = output_type_padded_value) THEN
2172 --
2173 -- Get Concatenated Segments from the code combinations table in PADDED mode
2174 --
2175 l_return := fnd_flex_server.get_kfv_concat_segs_by_ccid(
2176 p_concat_mode => 'PADDED',
2177 p_application_id => l_kff_flx.application_id,
2178 p_id_flex_code => p_id_flex_code,
2179 p_id_flex_num => p_id_flex_num,
2180 p_ccid => p_ccid,
2181 p_data_set => p_data_set);
2182 IF (p_segments = 'ALL') THEN
2183 GOTO goto_cache_and_return;
2184 ELSE
2185 n_segments := fnd_flex_server1.to_stringarray(l_return,
2186 fnd_flex_keyval.segment_delimiter,
2187 l_flex_values);
2188 END IF;
2189 END IF;
2190
2191 <<loop_column_names>>
2192 FOR i IN 1 .. l_numof_column_names LOOP
2193
2194 <<loop_segment_numbers>>
2195 FOR j IN 1 .. l_segment_count LOOP
2196 IF (fnd_flex_keyval.segment_column_name(j) = l_column_names(i)) THEN
2197 l_segment_numbers(i) := j;
2198 EXIT loop_segment_numbers;
2199 END IF;
2200 END LOOP;
2201 END LOOP;
2202
2203
2204 --
2205 -- Now, get the values
2206 --
2207 FOR i IN 1 .. l_numof_column_names LOOP
2208
2209 IF (p_output_type = output_type_value) THEN
2210 l_values(i) := fnd_flex_keyval.segment_value(l_segment_numbers(i));
2211
2212 ELSIF (p_output_type = output_type_padded_value) THEN
2213 l_values(i) := l_flex_values(l_segment_numbers(i));
2214
2215 ELSIF (p_output_type = output_type_description) THEN
2216 l_values(i) := Substr(fnd_flex_keyval.segment_description(l_segment_numbers(i)), 1, fnd_flex_keyval.segment_concat_desc_length(l_segment_numbers(i)));
2217
2218 ELSIF (p_output_type = output_type_full_description) THEN
2219 l_values(i) := fnd_flex_keyval.segment_description(l_segment_numbers(i));
2220 END IF;
2221 END LOOP;
2222
2223 --
2224 -- Now concatenate the values. (do not forget potential ESCAPING logic.)
2225 --
2226 l_return := fnd_flex_server1.from_stringarray
2227 (l_numof_column_names,
2228 l_values,
2229 fnd_flex_keyval.segment_delimiter);
2230 END IF;
2231
2232 <<goto_cache_and_return>>
2233 --
2234 -- Create the cache value, and populate it with values came from DB.
2235 --
2236 fnd_plsql_cache.generic_cache_new_value
2237 (x_value => l_value,
2238 p_varchar2_1 => l_return);
2239
2240 --
2241 -- Put the value in cache.
2242 --
2243 fnd_plsql_cache.generic_1to1_put_value(prcomb_generic_1to1_controller,
2244 prcomb_generic_1to1_storage,
2245 l_key,
2246 l_value);
2247 RETURN (l_return);
2248
2249 <<goto_return>>
2250 RETURN (l_return);
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 --
2254 -- This API cannot raise exception. If it does, database will stop
2255 -- executing the SELECT statement.
2256 -- Return the error message as output.
2257 --
2258 BEGIN
2259 raise_others('process_kff_combination_1',
2260 p_lexical_name,
2261 p_application_short_name,
2262 p_id_flex_code,
2263 p_id_flex_num,
2264 p_data_set,
2265 p_segments,
2266 p_show_parent_segments,
2267 p_output_type);
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 --
2271 -- For safety, return the first 2000 characters.
2272 --
2273 RETURN (Substr(Sqlerrm, 1, 2000));
2274 END;
2275 END process_kff_combination_1;
2276
2277
2278 -- ======================================================================
2279 PROCEDURE kff_select
2280 (p_lexical_name IN VARCHAR2,
2281 p_application_short_name IN fnd_application.application_short_name%TYPE,
2282 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
2283 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE DEFAULT 101,
2284 p_multiple_id_flex_num IN VARCHAR2 DEFAULT 'N',
2285 p_code_combination_table_alias IN VARCHAR2 DEFAULT NULL,
2286 p_segments IN VARCHAR2 DEFAULT 'ALL',
2287 p_show_parent_segments IN VARCHAR2 DEFAULT 'Y',
2288 p_output_type IN VARCHAR2,
2289 x_select_expression OUT nocopy VARCHAR2)
2290 IS
2291 l_kff_flx kff_flx_type;
2292 l_alias_prefix VARCHAR2(100);
2293 l_structure_column_name VARCHAR2(100);
2294 l_data_set_column_name VARCHAR2(100);
2295 BEGIN
2296 --
2297 -- Validate Input Arguments
2298 --
2299 validate_argument(arg_lexical_name, p_lexical_name);
2300 validate_argument(arg_multiple_id_flex_num, p_multiple_id_flex_num);
2301 validate_argument(arg_cct_alias, p_code_combination_table_alias);
2302 validate_argument(arg_segments, p_segments);
2303 validate_argument(arg_show_parent_segments, p_show_parent_segments);
2304 validate_argument(arg_output_type, p_output_type);
2305 cross_validate_arguments(arg_multiple_id_flex_num, p_multiple_id_flex_num,
2306 arg_id_flex_num, p_id_flex_num);
2307
2308 --
2309 -- Get the Key Flexfield
2310 --
2311 get_kff_flx(p_application_short_name => p_application_short_name,
2312 p_id_flex_code => p_id_flex_code,
2313 x_kff_flx => l_kff_flx);
2314
2315 --
2316 -- Compute the Alias Prefix
2317 --
2318 IF (p_code_combination_table_alias IS NULL) THEN
2319 l_alias_prefix := NULL;
2320 ELSE
2321 l_alias_prefix := p_code_combination_table_alias || '.';
2322 END IF;
2323
2324 --
2325 -- Build the SELECT clause
2326 --
2327 -- Please see $fnd/doc/flex/kff_multi_structure.txt for more information.
2328 --
2329 -- Multiple Structure KFFs:
2330 -- - set_defining_column_name is not null
2331 -- - DATA_SET argument is not used
2332 --
2333 -- fnd_flex_xml_publisher_apis.process_kff_combination_1(
2334 -- 'SELECT_LEXICAL', 'SQLGL', 'GL#',
2335 -- cct.CHART_OF_ACCOUNTS_ID, NULL,
2336 -- cct.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
2337 --
2338 -- Single Structure KFFs:
2339 -- - set_defining_column_name is null
2340 -- - Structure Number is always 101
2341 -- - DATA_SET argument is not used
2342 --
2343 -- fnd_flex_xml_publisher_apis.process_kff_combination_1(
2344 -- 'SELECT_LEXICAL', 'OFA', 'CAT#',
2345 -- 101, NULL,
2346 -- cct.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
2347 --
2348 --
2349 -- Special Single Structure KFFs: (401/MDSP,MICG,MSTK,MTLL,SERV)
2350 -- - set_defining_column_name may or may not be null
2351 -- - Structure Number is always 101
2352 -- - DATA_SET argument is used
2353 --
2354 -- fnd_flex_xml_publisher_apis.process_kff_combination_1(
2355 -- 'SELECT_LEXICAL', 'INV', 'MSTK',
2356 -- 101, cct.ORGANIZATION_ID,
2357 -- cct.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')
2358 --
2359 IF (l_kff_flx.set_defining_column_name IS NULL) THEN
2360 l_structure_column_name := '101';
2361 ELSE
2362 l_structure_column_name := l_alias_prefix || l_kff_flx.set_defining_column_name;
2363 END IF;
2364
2365 IF ((p_application_short_name = 'INV' AND p_id_flex_code = 'MDSP') OR
2366 (p_application_short_name = 'INV' AND p_id_flex_code = 'MICG') OR
2367 (p_application_short_name = 'INV' AND p_id_flex_code = 'MSTK') OR
2368 (p_application_short_name = 'INV' AND p_id_flex_code = 'MTLL') OR
2369 (p_application_short_name = 'INV' AND p_id_flex_code = 'SERV')) THEN
2370 l_structure_column_name := '101';
2371 l_data_set_column_name := l_alias_prefix || l_kff_flx.set_defining_column_name;
2372 ELSE
2373 l_data_set_column_name := 'NULL';
2374 END IF;
2375
2376 x_select_expression := 'fnd_flex_xml_publisher_apis.process_kff_combination_1(' ||
2377 '''' || p_lexical_name || ''', ' ||
2378 '''' || p_application_short_name || ''', ' ||
2379 '''' || p_id_flex_code || ''', ' ||
2380 l_structure_column_name || ', ' ||
2381 l_data_set_column_name || ', ' ||
2382 l_alias_prefix || l_kff_flx.unique_id_column_name || ', ' ||
2383 '''' || p_segments || ''', ' ||
2384 '''' || p_show_parent_segments || ''', ' ||
2385 '''' || p_output_type || ''')';
2386
2387 EXCEPTION
2388 WHEN OTHERS THEN
2389 raise_others('kff_select',
2390 p_lexical_name,
2391 p_application_short_name,
2392 p_id_flex_code,
2393 p_id_flex_num,
2394 p_multiple_id_flex_num,
2395 p_code_combination_table_alias,
2396 p_segments,
2397 p_show_parent_segments,
2398 p_output_type);
2399 END kff_select;
2400
2401 -- ======================================================================
2402 PROCEDURE kff_where
2403 (p_lexical_name IN VARCHAR2,
2404 p_application_short_name IN fnd_application.application_short_name%TYPE,
2405 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
2406 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE,
2407 p_code_combination_table_alias IN VARCHAR2 DEFAULT NULL,
2408 p_segments IN VARCHAR2 DEFAULT 'ALL',
2409 p_operator IN VARCHAR2,
2410 p_operand1 IN VARCHAR2,
2411 p_operand2 IN VARCHAR2 DEFAULT NULL,
2412 x_where_expression OUT nocopy VARCHAR2,
2413 x_numof_bind_variables OUT nocopy NUMBER,
2414 x_bind_variables OUT nocopy bind_variables)
2415 IS
2416 l_kff_flx kff_flx_type;
2417 l_kff_str kff_str_type;
2418
2419 l_column_names varchar2_30_array_type;
2420 l_numof_column_names NUMBER;
2421
2422 l_where_expression VARCHAR2(32000);
2423
2424 l_bind_variables bind_variables;
2425 l_numof_bind_variables NUMBER;
2426
2427 l_operand1_array fnd_flex_server1.StringArray;
2428 l_operand2_array fnd_flex_server1.StringArray;
2429
2430 l_array_index PLS_INTEGER;
2431 l_numof_segs NUMBER;
2432
2433 PROCEDURE assign_bind_values
2434 (p_bind_variable_num IN NUMBER,
2435 p_bind_value IN VARCHAR2,
2436 p_column_name IN VARCHAR2,
2437 p_bind_variable OUT nocopy bind_variable)
2438 IS
2439 l_datatype VARCHAR2(1);
2440 l_format_type VARCHAR(1);
2441 l_maximum_size NUMBER;
2442 l_flex_value_set_id NUMBER;
2443 l_format_mask VARCHAR2(30);
2444 BEGIN
2445 SELECT fnd_columns.column_type
2446 INTO l_datatype
2447 FROM fnd_columns,fnd_tables,fnd_id_flexs
2448 WHERE fnd_columns.column_name = p_column_name
2449 AND fnd_columns.table_id = fnd_tables.table_id
2450 AND fnd_columns.application_id = fnd_tables.application_id
2451 AND fnd_tables.table_name = fnd_id_flexs.application_table_name
2452 AND fnd_tables.application_id = fnd_id_flexs.table_application_id
2453 AND fnd_id_flexs.id_flex_code = l_kff_flx.id_flex_code
2454 AND fnd_id_flexs.application_id = l_kff_flx.application_id;
2455
2456 p_bind_variable.name := p_lexical_name || p_bind_variable_num;
2457
2458 SELECT flex_value_set_id
2459 INTO l_flex_value_set_id
2460 FROM fnd_id_flex_segments fifs
2461 WHERE fifs.application_id = l_kff_flx.application_id
2462 AND fifs.id_flex_code = l_kff_flx.id_flex_code
2463 AND fifs.id_flex_num = p_id_flex_num
2464 AND fifs.application_column_name = p_column_name;
2465
2466 IF l_datatype = 'N' THEN
2467 p_bind_variable.data_type := bind_data_type_number;
2468 p_bind_variable.number_value := TO_NUMBER(p_bind_value);
2469 p_bind_variable.canonical_value := fnd_number.number_to_canonical(p_bind_value);
2470
2471 ELSIF (l_datatype = 'D' AND l_flex_value_set_id is NOT NULL) THEN
2472 SELECT format_type, maximum_size
2473 INTO l_format_type, l_maximum_size
2474 FROM fnd_flex_value_sets ffvs
2475 WHERE ffvs.flex_value_set_id = l_flex_value_set_id;
2476
2477 IF (l_format_type = 'D') THEN -- Date type
2478
2479 IF (l_maximum_size = 9 ) THEN
2480 l_format_mask := 'DD-MON-RR';
2481 ELSIF (l_maximum_size = 11) THEN
2482 l_format_mask := 'DD-MON-YYYY';
2483 END IF;
2484
2485 ELSIF (l_format_type = 'T') THEN -- DateTime type
2486
2487 IF (l_maximum_size = 15) THEN
2488 l_format_mask := 'DD-MON-RR HH24:MI';
2489 ELSIF (l_maximum_size = 17) THEN
2490 l_format_mask := 'DD-MON-YYYY HH24:MI';
2491 ELSIF (l_maximum_size = 18) THEN
2492 l_format_mask := 'DD-MON-RR HH24:MI:SS';
2493 ELSIF (l_maximum_size = 20) THEN
2494 l_format_mask := 'DD-MON-YYYY HH24:MI:SS';
2495 END IF;
2496
2497 ELSE -- Std. Date/DateTime type
2498 l_format_mask := 'YYYY/MM/DD HH24:MI:SS';
2499 END IF;
2500
2501 p_bind_variable.data_type := bind_data_type_date;
2502 p_bind_variable.date_value := TO_DATE(p_bind_value, l_format_mask);
2503 p_bind_variable.canonical_value := fnd_date.string_to_canonical(p_bind_value, l_format_type);
2504
2505 ELSE
2506 p_bind_variable.data_type := bind_data_type_varchar2;
2507 p_bind_variable.varchar2_value := p_bind_value;
2508 p_bind_variable.canonical_value := p_bind_value;
2509
2510 END IF; -- END IF of l_datatype = 'N'
2511
2512 EXCEPTION
2513 WHEN NO_DATA_FOUND THEN
2514 raise_no_data_found('Column','column_name',p_column_name);
2515 END assign_bind_values;
2516
2517 BEGIN
2518 --
2519 -- Validate Input Arguments
2520 --
2521 validate_argument(arg_lexical_name, p_lexical_name);
2522 validate_argument(arg_cct_alias, p_code_combination_table_alias);
2523 validate_argument(arg_segments, p_segments);
2524 validate_argument(arg_operator, p_operator);
2525
2526 if ((p_operator <> operator_between and p_operand1 is null) or
2527 (p_operator = operator_between and p_operand1 is null and p_operand2 is null))
2528 then
2529 x_where_expression := '1 = 1';
2530 x_numof_bind_variables := 0;
2531 return;
2532 end if;
2533 --
2534 -- Get the Key Flexfield
2535 --
2536 get_kff_flx(p_application_short_name => p_application_short_name,
2537 p_id_flex_code => p_id_flex_code,
2538 x_kff_flx => l_kff_flx);
2539
2540 --
2541 -- Get the Structure
2542 --
2543 get_kff_str(p_kff_flx => l_kff_flx,
2544 p_id_flex_num => p_id_flex_num,
2545 x_kff_str => l_kff_str);
2546
2547 --
2548 -- Get the Column Names
2549 --
2550 get_kff_seg_column_names(p_kff_str => l_kff_str,
2551 p_segments_mode => segments_mode_all_enabled,
2552 p_segments => p_segments,
2553 p_show_parent_segments => 'Y',
2554 x_numof_column_names => l_numof_column_names,
2555 x_column_names => l_column_names);
2556 --
2557 -- Split the operand values by the segment separators
2558 --
2559 -- for first operand
2560 l_numof_segs := fnd_flex_server1.to_stringarray(p_operand1,l_kff_str.concatenated_segment_delimiter,l_operand1_array);
2561
2562 -- for second operand
2563 l_numof_segs := fnd_flex_server1.to_stringarray(p_operand2,l_kff_str.concatenated_segment_delimiter,l_operand2_array);
2564
2565 l_where_expression := NULL;
2566 l_numof_bind_variables := 0;
2567
2568 -- If the number of operands does not equals the number of segments then raise exception
2569 IF (l_numof_column_names > l_operand1_array.COUNT) OR
2570 (l_numof_column_names > l_operand2_array.COUNT AND
2571 p_operator = operator_between) THEN
2572
2573 FOR l_array_index IN (l_operand1_array.COUNT + 1)..l_numof_column_names LOOP
2574 l_operand1_array(l_array_index) := NULL;
2575 END LOOP;
2576
2577 IF p_operator = operator_between THEN
2578 FOR l_array_index IN (l_operand2_array.COUNT + 1)..l_numof_column_names LOOP
2579 l_operand2_array(l_array_index) := NULL;
2580 END LOOP;
2581 END IF;
2582 END IF;
2583
2584 /* Bug 5140265. Use l_numof_column_names and not l_numof_segs. */
2585 FOR i IN 1 .. l_numof_column_names LOOP
2586 IF l_where_expression IS NOT NULL THEN
2587 l_where_expression := l_where_expression || ' AND ';
2588 END IF;
2589
2590 IF p_code_combination_table_alias IS NOT NULL THEN
2591 l_where_expression := l_where_expression || p_code_combination_table_alias || '.' || l_column_names(i);
2592 ELSE
2593 l_where_expression := l_where_expression || ' ' || l_column_names(i);
2594 END IF;
2595
2596 IF p_operator <> operator_between THEN
2597 l_numof_bind_variables := l_numof_bind_variables + 1;
2598 l_where_expression := l_where_expression || ' ' ||
2599 UPPER(p_operator) || ' :' || p_lexical_name || l_numof_bind_variables;
2600
2601 assign_bind_values(l_numof_bind_variables,l_operand1_array(i),l_column_names(i),l_bind_variables(l_numof_bind_variables));
2602
2603 ELSE
2604 l_numof_bind_variables := l_numof_bind_variables + 1;
2605 l_where_expression := l_where_expression || ' ' || UPPER(p_operator)
2606 || ' :' || p_lexical_name || l_numof_bind_variables;
2607 assign_bind_values(l_numof_bind_variables,l_operand1_array(i),l_column_names(i),l_bind_variables(l_numof_bind_variables));
2608
2609 l_numof_bind_variables := l_numof_bind_variables + 1;
2610
2611 l_where_expression := l_where_expression || ' AND :' || p_lexical_name || l_numof_bind_variables;
2612
2613 assign_bind_values(l_numof_bind_variables,l_operand2_array(i),l_column_names(i),l_bind_variables(l_numof_bind_variables));
2614
2615 END IF;
2616 END LOOP;
2617
2618 --
2619 -- Assign the local variables to OUT variables
2620 --
2621 x_where_expression := l_where_expression;
2622 x_bind_variables := l_bind_variables;
2623 x_numof_bind_variables := l_numof_bind_variables;
2624 EXCEPTION
2625 WHEN OTHERS THEN
2626 raise_others('kff_where',
2627 p_lexical_name,
2628 p_application_short_name,
2629 p_id_flex_code,
2630 p_id_flex_num,
2631 p_code_combination_table_alias,
2632 p_segments,
2633 p_operator,
2634 p_operand1,
2635 p_operand2);
2636 END kff_where;
2637
2638 -- ======================================================================
2639 PROCEDURE kff_order_by
2640 (p_lexical_name IN VARCHAR2,
2641 p_application_short_name IN fnd_application.application_short_name%TYPE,
2642 p_id_flex_code IN fnd_id_flexs.id_flex_code%TYPE,
2643 p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE DEFAULT 101,
2644 p_multiple_id_flex_num IN VARCHAR2 DEFAULT 'N',
2645 p_code_combination_table_alias IN VARCHAR2 DEFAULT NULL,
2646 p_segments IN VARCHAR2 DEFAULT 'ALL',
2647 p_show_parent_segments IN VARCHAR2 DEFAULT 'Y',
2648 x_order_by_expression OUT nocopy VARCHAR2)
2649 IS
2650 l_alias_prefix VARCHAR2(100);
2651
2652 l_kff_flx kff_flx_type;
2653 l_kff_str kff_str_type;
2654
2655 l_structure_numbers number_array_type;
2656 l_numof_structures NUMBER;
2657
2658 l_column_names varchar2_30_array_type;
2659 l_numof_column_names NUMBER;
2660
2661 l_decode_elements decode_elements_type;
2662 l_numof_decode_elements NUMBER;
2663
2664 l_concat_column_names_clause VARCHAR2(32000);
2665 BEGIN
2666 --
2667 -- Validate Input Arguments
2668 --
2669 validate_argument(arg_lexical_name, p_lexical_name);
2670 validate_argument(arg_multiple_id_flex_num, p_multiple_id_flex_num);
2671 validate_argument(arg_cct_alias, p_code_combination_table_alias);
2672 validate_argument(arg_segments, p_segments);
2673 validate_argument(arg_show_parent_segments, p_show_parent_segments);
2674 cross_validate_arguments(arg_multiple_id_flex_num, p_multiple_id_flex_num,
2675 arg_id_flex_num, p_id_flex_num);
2676 --
2677 -- Compute the Alias Prefix
2678 --
2679 IF (p_code_combination_table_alias IS NULL) THEN
2680 l_alias_prefix := NULL;
2681 ELSE
2682 l_alias_prefix := p_code_combination_table_alias || '.';
2683 END IF;
2684
2685 --
2686 -- Get the Key Flexfield
2687 --
2688 get_kff_flx(p_application_short_name => p_application_short_name,
2689 p_id_flex_code => p_id_flex_code,
2690 x_kff_flx => l_kff_flx);
2691
2692 --
2693 -- Get the structure numbers
2694 --
2695 IF (p_multiple_id_flex_num = 'N') THEN
2696 --
2697 -- Single Structure
2698 --
2699 l_numof_structures := 1;
2700 l_structure_numbers(1) := p_id_flex_num;
2701
2702 ELSIF (p_multiple_id_flex_num = 'Y') THEN
2703 --
2704 -- Multiple Structures
2705 --
2706 get_kff_str_numbers(p_kff_flx => l_kff_flx,
2707 x_numof_structures => l_numof_structures,
2708 x_structure_numbers => l_structure_numbers);
2709 END IF;
2710
2711 --
2712 -- Process the structures and load results into decode elements array
2713 -- The .search will contain the struct number
2714 -- The .result will contain the orderby column names
2715 --
2716 l_numof_decode_elements := l_numof_structures;
2717 FOR i IN 1 .. l_numof_decode_elements LOOP
2718
2719 --
2720 -- Get the Structure
2721 --
2722 get_kff_str(p_kff_flx => l_kff_flx,
2723 p_id_flex_num => l_structure_numbers(i),
2724 x_kff_str => l_kff_str);
2725
2726 --
2727 -- Get the Segment Column Names
2728 --
2729 get_kff_seg_column_names
2730 (p_kff_str => l_kff_str,
2731 p_segments_mode => segments_mode_displayed_only,
2732 p_segments => p_segments,
2733 p_show_parent_segments => p_show_parent_segments,
2734 x_numof_column_names => l_numof_column_names,
2735 x_column_names => l_column_names);
2736
2737 --
2738 -- Assign the structure numbers to .search
2739 -- i.e. l_decode_elements(i).search := 101
2740 --
2741 l_decode_elements(i).search := l_structure_numbers(i);
2742
2743 --
2744 -- Concatenate the columns
2745 --
2746 get_concat_column_names_clause
2747 (p_alias_prefix => l_alias_prefix,
2748 p_numof_column_names => l_numof_column_names,
2749 p_column_names => l_column_names,
2750 p_delimiter => ' || '','' || ',
2751 x_concat_column_names_clause => l_concat_column_names_clause);
2752
2753 --
2754 -- Assign the concatenated column names to .result
2755 -- i.e. l_decode_elements(i).result := SEGMENT1 || ', ' || SEGMENT2
2756 --
2757 l_decode_elements(i).result := l_concat_column_names_clause;
2758 END LOOP;
2759
2760 --
2761 -- Build final order by expression
2762 --
2763 get_decode_clause
2764 (p_expression => l_alias_prefix || l_kff_flx.set_defining_column_name,
2765 p_numof_decode_elements => l_numof_decode_elements,
2766 p_decode_elements => l_decode_elements,
2767 p_default => 'NULL',
2768 x_decode_clause => x_order_by_expression);
2769
2770 EXCEPTION
2771 WHEN OTHERS THEN
2772 raise_others('kff_order_by',
2773 p_lexical_name,
2774 p_application_short_name,
2775 p_id_flex_code,
2776 p_id_flex_num,
2777 p_multiple_id_flex_num,
2778 p_code_combination_table_alias,
2779 p_segments,
2780 p_show_parent_segments);
2781 END kff_order_by;
2782
2783
2784 BEGIN
2785 --
2786 --
2787 -- Qualifier Segment Number Generic 1to1 Cache.
2788 --
2789 fnd_plsql_cache.generic_1to1_init('Ql.Segnum Generic 1to1 Cache',
2790 g_snum_generic_1to1_controller,
2791 g_snum_generic_1to1_storage);
2792 -- Application Generic 1to1 Cache.
2793 --
2794 fnd_plsql_cache.generic_1to1_init('Application Generic 1to1 Cache',
2795 g_app_generic_1to1_controller,
2796 g_app_generic_1to1_storage);
2797 --
2798 -- Key Flexfield Generic 1to1 Cache.
2799 --
2800 fnd_plsql_cache.generic_1to1_init('KFF Generic 1to1 Cache',
2801 g_kflx_generic_1to1_controller,
2802 g_kflx_generic_1to1_storage);
2803 --
2804 -- Key Flexfield Structure Numbers Generic 1toM Cache
2805 --
2806 fnd_plsql_cache.generic_1tom_init('KFF Str No. Generic 1toM Cache',
2807 g_stno_generic_1tom_controller,
2808 g_stno_generic_1tom_storage);
2809
2810 --
2811 -- Key Flexfield Structure Generic 1to1 Cache.
2812 --
2813 fnd_plsql_cache.generic_1to1_init('KFF Structure 1to1 Cache',
2814 g_str_generic_1to1_controller,
2815 g_str_generic_1to1_storage);
2816 --
2817 -- Key Flexfield Segment Generic 1to1 Cache.
2818 --
2819 fnd_plsql_cache.generic_1to1_init('KFF Segment 1to1 Cache',
2820 g_seg_generic_1to1_controller,
2821 g_seg_generic_1to1_storage);
2822 --
2823 -- Key Flexfield Segment TL Generic 1to1 Cache.
2824 --
2825 fnd_plsql_cache.generic_1to1_init('KFF Seg TL 1to1 Cache',
2826 g_segt_generic_1to1_controller,
2827 g_segt_generic_1to1_storage);
2828 --
2829 -- Parent Value Set Id Generic 1to1 Cache.
2830 --
2831 fnd_plsql_cache.generic_1to1_init('Parent VSet Id 1to1 Cache',
2832 p_vsid_generic_1to1_controller,
2833 p_vsid_generic_1to1_storage);
2834 --
2835 -- Key Flexfield Segment Info Generic 1toM Cache
2836 --
2837 fnd_plsql_cache.generic_1tom_init('KFF SegInfo Generic 1toM Cache',
2838 seginf_generic_1tom_controller,
2839 seginf_generic_1tom_storage);
2840 --
2841 -- Process KFF Combination Generic 1to1 Cache.
2842 --
2843 fnd_plsql_cache.generic_1to1_init('Process KFF Comb. 1to1 Cache',
2844 prcomb_generic_1to1_controller,
2845 prcomb_generic_1to1_storage);
2846
2847 g_newline := fnd_global.newline();
2848 g_unused_argument := fnd_global.local_chr(0);
2849 g_debug_enabled := FALSE;
2850 init_debug();
2851 END fnd_flex_xml_publisher_apis;