1 package body fa_rx_flex_pkg as
2 /* $Header: FARXFLXB.pls 120.7.12010000.2 2008/07/31 06:52:03 sbhaskar ship $ */
3
4 ---------------------------------------------
5 -- Some global types, constants and cursors
6 ---------------------------------------------
7 type seg_array is table of FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW%type index by binary_integer;
8 NEWLINE CONSTANT varchar2(3) := '';
9
10
11 --
12 -- The following cursor is used extensively and is the main logic of
13 -- most of the procedures provided by this package.
14 -- Givent the key flexfield and qualifier/segment number,
15 -- this cursor will return the segment number(s), column name(s)
16 -- and the flex value set(s) for all of the segments that
17 -- match the criteria for this key flexfield
18 --
19 cursor cflex(p_application_id in varchar2,
20 p_id_flex_code in varchar2,
21 p_id_flex_num in number,
22 p_qualifier in varchar2,
23 p_segnum in number) is
24 select s.segment_num, s.application_column_name, s.flex_value_set_id
25 from fnd_id_flex_segments s
26 where s.application_id = p_application_id
27 and s.id_flex_code = p_id_flex_code
28 and s.id_flex_num = p_id_flex_num
29 and s.enabled_flag = 'Y'
30 and p_qualifier = 'ALL'
31 and p_segnum is null
32 union all
33 select s.segment_num, s.application_column_name, s.flex_value_set_id
34 from fnd_id_flex_segments s,
35 fnd_segment_attribute_values sav,
36 fnd_segment_attribute_types sat
37 where s.application_id = p_application_id
38 and s.id_flex_code = p_id_flex_code
39 and s.id_flex_num = p_id_flex_num
40 and s.enabled_flag = 'Y'
41 and s.application_column_name = sav.application_column_name
42 and sav.application_id = p_application_id
43 and sav.id_flex_code = p_id_flex_code
44 and sav.id_flex_num = p_id_flex_num
45 and sav.attribute_value = 'Y'
46 and sav.segment_attribute_type = sat.segment_attribute_type
47 and sat.application_id = p_application_id
48 and sat.id_flex_code = p_id_flex_code
49 and sat.unique_flag = 'Y'
50 and sat.segment_attribute_type = p_qualifier
51 and p_qualifier <> 'ALL'
52 and p_segnum is null
53 union all
54 select s.segment_num, s.application_column_name, s.flex_value_set_id
55 from fnd_id_flex_segments s
56 where s.application_id = p_application_id
57 and s.id_flex_code = p_id_flex_code
58 and s.id_flex_num = p_id_flex_num
59 and s.enabled_flag = 'Y'
60 and s.segment_num = p_segnum
61 and p_qualifier is null
62 order by 1;
63
64 cursor par_seg( p_application_id in number,
65 p_id_flex_code in varchar2,
66 p_id_flex_num in number,
67 p_value_set_id in number)
68 Is
69 select c.segment_num parent_seg_num
70 from FND_FLEX_VALUE_SETS a, FND_ID_FLEX_SEGMENTS b, FND_ID_FLEX_SEGMENTS c
71 where b.APPLICATION_ID = p_application_id
72 and b.ID_FLEX_CODE = p_id_flex_code
73 and b.ID_FLEX_NUM = p_id_flex_num
74 and (b.FLEX_VALUE_SET_ID = a.FLEX_VALUE_SET_ID)
75 and b.APPLICATION_ID = c.APPLICATION_ID
76 and b.ID_FLEX_CODE = c.ID_FLEX_CODE
77 and b.ID_FLEX_NUM = c.ID_FLEX_NUM
78 and (a.PARENT_FLEX_VALUE_SET_ID = c.FLEX_VALUE_SET_ID)
79 and a.FLEX_VALUE_SET_ID = p_value_set_id;
80
81 ------------------------------------
82 -- Private Functions/Procedures
83 ------------------------------------
84
85 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
86
87 -------------------------------------------------------------------------
88 --
89 -- PRIVATE FUNCTION Get_Parent_Value
90 --
91 -- Parameters
92 -- p_seg_array Segment Array
93 -- p_application_id Application id
94 -- p_id_flex_code flex code
95 -- p_id_flex_num Flex num default null
96 -- p_child_value_set_id value set id , whose parent value value is to be found out.
97 --
98 -- Returns
99 -- the parent segment value
100 -- Description
101 -- This function returns the parent segments value for a depenant value set.
102 -- takes in application id,
103 --
104 --
105 -- Modification History
106 -- RRAVUNNY Created Bug#2991482
107 --
108 -------------------------------------------------------------------------
109 Function Get_Parent_Value( p_seg_array in seg_array,
110 p_application_id in number,
111 p_id_flex_code in varchar2,
112 p_id_flex_num in number default NULL,
113 p_child_value_set_id in number
114 ) return varchar2
115 Is
116 Lvr_Parent_Value FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW%type Default Null;
117 Lnu_array_counter Number Default Null;
118 err_num Number;
119 Lnu_cache_index Number Default Null;
120 Begin
121 --* dbms_output.put_line('Get_Parent_Value +');
122 --* dbms_output.put_line(p_application_id||' - '||p_id_flex_code||' - '||p_id_flex_num||' - '||p_child_value_set_id);
123 --* Check if the fa_rx_flex_parent_seg_t cache exists
124 Lnu_cache_index := fa_rx_flex_par_seg_t.FIRST;
125 --* dbms_output.put_line('for first , Lnu_cache_index = '||Lnu_cache_index||' total = '||fa_rx_flex_par_seg_t.count);
126 WHILE Lnu_cache_index IS NOT NULL
127 LOOP
128 --* dbms_output.put_line('record content fa_rx_flex_parent_seg_t('||Lnu_cache_index||')');
129 --* dbms_output.put_line('fa_rx_flex_parent_seg_t(Lnu_cache_index).fap_application_id = '||fa_rx_flex_par_seg_t(Lnu_cache_index).fap_application_id);
130 --* dbms_output.put_line('fa_rx_flex_parent_seg_t(Lnu_cache_index).fap_id_flex_code = '||fa_rx_flex_par_seg_t(Lnu_cache_index).fap_id_flex_code);
131 --* dbms_output.put_line('fa_rx_flex_parent_seg_t(Lnu_cache_index).fap_id_flex_num = '||fa_rx_flex_par_seg_t(Lnu_cache_index).fap_id_flex_num);
132 --* dbms_output.put_line('fa_rx_flex_parent_seg_t(Lnu_cache_index).fap_flex_value_set_id = '||fa_rx_flex_par_seg_t(Lnu_cache_index).fap_flex_value_set_id);
133 --* dbms_output.put_line('fa_rx_flex_parent_seg_t(Lnu_cache_index).fap_parent_segment_num = '||fa_rx_flex_par_seg_t(Lnu_cache_index).fap_parent_segment_num);
134 --* Check if the record exists in cache.
135 If ( fa_rx_flex_par_seg_t(Lnu_cache_index).fap_application_id = p_application_id and
136 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_id_flex_code = p_id_flex_code and
137 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_id_flex_num = p_id_flex_num and
138 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_flex_value_set_id = p_child_value_set_id
139 )
140 Then
141 --* dbms_output.put_line('present in cache');
142 Lnu_array_counter := fa_rx_flex_par_seg_t(Lnu_cache_index).fap_parent_segment_num;
143 --* dbms_output.put_line('Lnu_array_counter = '||Lnu_array_counter);
144 Exit;
145 End If;
146
147 Lnu_cache_index := fa_rx_flex_par_seg_t.NEXT(Lnu_cache_index);
148 END LOOP;
149
150 If p_seg_array.FIRST Is Null Then
151 --* dbms_output.put_line('Get_Parent_Value return' ||Lvr_Parent_Value);
152 --* dbms_output.put_line('Get_Parent_Value -');
153 Return(Lvr_Parent_Value);
154 End If;
155
156 --* Not present in cache
157 Lnu_cache_index := Nvl(fa_rx_flex_par_seg_t.count,0) + 1;
158 --* dbms_output.put_line('Lnu_cache_index = '||Lnu_cache_index);
159 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_application_id := p_application_id;
160 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_id_flex_code := p_id_flex_code;
161 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_id_flex_num := p_id_flex_num ;
162 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_flex_value_set_id := p_child_value_set_id ;
163
164 If Lnu_array_counter Is Null Then
165 --* dbms_output.put_line('Get_Parent_Value Lnu_array_counter is null');
166 If par_seg%IsOpen Then
167 Close par_seg;
168 End If;
169 Open par_seg(p_application_id,p_id_flex_code,p_id_flex_num,p_child_value_set_id);
170 Fetch par_seg into Lnu_array_counter;
171 Close par_seg;
172 --* dbms_output.put_line('Get_Parent_Value copy into cache Lnu_array_counter = '||Lnu_array_counter);
173 fa_rx_flex_par_seg_t(Lnu_cache_index).fap_parent_segment_num := Lnu_array_counter;
174 --* dbms_output.put_line('216');
175 --* dbms_output.put_line('218');
176 End If;
177
178 If Lnu_array_counter Is Not Null Then
179 Lvr_Parent_Value := p_seg_array(Lnu_array_counter);
180 End If;
181
182 --* dbms_output.put_line('Get_Parent_Value return' ||Lvr_Parent_Value);
183 --* dbms_output.put_line('Get_Parent_Value -');
184 Return(Lvr_Parent_Value);
185 Exception
186 When Others Then
187 err_num := sqlcode;
188 dbms_output.put_line('Get_Parent_Value exception '||err_num);
189 dbms_output.put_line('Get_Parent_Value -');
190 Return(Lvr_Parent_Value);
191 End Get_Parent_Value;
192 -------------------------------------------------------------------------
193 --
194 -- PRIVATE PROCEDURE separate_segments
195 --
196 -- Parameters
197 -- p_seg_array Segment Array
198 -- p_values Concatenated Segments
199 -- p_sep Segment Delimiter
200 --
201 -- Description
202 -- This function takes the concatenated segments and splits them
203 -- up into individual segments (using the segment delimiter) and
204 -- places them into the segment array.
205 --
206 -- Modification History
207 -- KMIZUTA 02-APR-99 Created.
208 --
209 -------------------------------------------------------------------------
210
211 procedure separate_segments(
212 p_seg_array in out nocopy seg_array,
213 p_values in varchar2,
214 p_sep in varchar2)
215 is
216 i number;
217 next_sep number;
218 l_values varchar2(600);
219 begin
220 IF (g_print_debug) THEN
221 fa_rx_util_pkg.debug('fa_rx_flex_pkg.separate_segments('||p_values||')+');
222 END IF;
223
224 l_values := p_values;
225 i := 1;
226 while (l_values is not null) loop
227 next_sep := instr(l_values, p_sep);
228 if next_sep = 0 then
229 p_seg_array(i) := l_values;
230 l_values := null;
231 else
232 p_seg_array(i) := substr(l_values, 1, next_sep-1);
233 l_values := substr(l_values, next_sep+1);
234 end if;
235
236 i := i+1;
237 end loop;
238
239 fa_rx_util_pkg.debug('fa_rx_flex_pkg.separate_segments('||to_char(i-1)||')-');
240 end separate_segments;
241
242
243 -------------------------------------------------------------------------
244 --
245 -- PRIVATE FUNCTION get_id_flex_num
246 --
247 -- Parameters
248 -- p_application_id Application ID of key flexfield
249 -- p_id_flex_code Flexfield code
250 -- p_id_flex_num Flexfield structure num
251 --
252 -- Returns NUMBER
253 -- Returns the actual id_flex_num to be used
254 --
255 -- Description
256 -- This function takes the p_id_flex_num as input and returns
257 -- the actual id_flex_num to be used. If p_id_flex_num is not NULL,
258 -- it returns p_id_flex_num. If it is NULL, it returns the one structure
259 -- number that exists for this key flexfield. If there are more than
260 -- one, then this function will raise an exception.
261 -- This is to support key flexfield structures like the item flexfield
262 -- which uses a dataset.
263 --
264 -- Modification History
265 -- KMIZUTA 02-APR-99 Created.
266 --
267 -------------------------------------------------------------------------
268 function get_id_flex_num(
269 p_application_id in number,
270 p_id_flex_code in varchar2,
271 p_id_flex_num in number) return number
272 is
273 l_id_flex_num number;
274 begin
275 if p_id_flex_num is not null then
276 return p_id_flex_num;
277 end if;
278
279 select id_flex_num into l_id_flex_num
280 from fnd_id_flex_structures
281 where application_id = p_application_id
282 and id_flex_code = p_id_flex_code;
283
284 return l_id_flex_num;
285 exception
286 when too_many_rows then
287 IF (g_print_debug) THEN
288 fa_rx_util_pkg.debug('get_id_flex_num: ' || 'EXCEPTION ==> Too many structures for APP_ID='||to_char(p_application_id)||', ID_FLEX_CODE='||p_id_flex_code);
289 END IF;
290 raise;
291 end get_id_flex_num;
292
293
294 -------------------------------------------------------------------------
295 --
296 -- PRIVATE FUNCTION get_segment_delimiter
297 --
298 -- Parameters
299 -- p_application_id Application ID of key flexfield
300 -- p_id_flex_code Flexfield code
301 -- p_id_flex_num Flexfield structure num
302 --
303 -- Returns VARCHAR2
304 -- Returns the segment delimiter for the given key flexfield
305 --
306 -- Description
307 -- This function takes the concatenated segments and splits them
308 -- up into individual segments (using the segment delimiter) and
309 -- places them into the segment array.
310 --
311 -- Modification History
312 -- KMIZUTA 02-APR-99 Created.
313 --
314 -------------------------------------------------------------------------
315 function get_segment_delimiter(
316 p_application_id in number,
317 p_id_flex_code in varchar2,
318 p_id_flex_num in number) return varchar2
319 is
320 sep fnd_id_flex_structures.concatenated_segment_delimiter%type;
321 begin
322 select concatenated_segment_delimiter into sep
323 from fnd_id_flex_structures
324 where application_id = p_application_id
325 and id_flex_code = p_id_flex_code
326 and id_flex_num = p_id_flex_num;
327
328 return sep;
329
330 exception
331 when no_data_found then
332 IF (g_print_debug) THEN
333 fa_rx_util_pkg.debug('get_segment_delimiter: ' || 'EXCEPTION ==> Unable to find segment delimiter!');
334 END IF;
335 raise;
336 end get_segment_delimiter;
337
338
339
340 ------------------------------------
341 -- Public Functions/Procedures
342 ------------------------------------
343
344 -------------------------------------------------------------------------
345 --
346 -- FUNCTION flex_sql
347 --
348 -- Parameters
349 -- p_application_id Application ID of key flexfield
350 -- p_id_flex_code Flexfield code
351 -- p_id_flex_num Flexfield structure num
352 -- p_table_alias Table Alias
353 -- p_mode Output mode
354 -- p_qualifier Flexfield qualifier or segment number
355 -- p_function Operator
356 -- p_operand1,2 Operands
357 --
358 -- Returns VARCHAR2
359 -- Returns the required SQL clause
360 --
361 -- Description
362 -- This function mimics the functionality of the userexit FLEXSQL.
363 -- Given the parameters, this function is equivalent to:
364 -- FND FLEXSQL
365 -- CODE=":p_id_flex_code"
366 -- APPL_SHORT_NAME="Short name from :p_application_id"
367 -- OUTPUT=":This is the return value"
368 -- MODE=":p_mode"
369 -- DISPLAY=":p_qualifier"
370 -- NUM=":p_id_flex_num"
371 -- TABLEALIAS=":p_table_alias"
372 -- OPERATOR=":p_function"
373 -- OPERAND1=":p_operand1"
374 -- OPERAND2=":p_operand2"
375 --
376 -- Restrictions
377 -- No support for SHOWDEPSEG parameter
378 -- No support for MULTINUM parameter
379 -- p_qualifier must be 'ALL' or a valid qualifier name or segment number.
380 -- p_function does not support "QBE".
381 --
382 -- Modification History
383 -- KMIZUTA 02-APR-99 Created.
384 --
385 -------------------------------------------------------------------------
386 function flex_sql(
387 p_application_id in number,
388 p_id_flex_code in varchar2,
389 p_id_flex_num in number default null,
390 p_table_alias in varchar2,
391 p_mode in varchar2,
392 p_qualifier in varchar2,
393 p_function in varchar2 default null,
394 p_operand1 in varchar2 default null,
395 p_operand2 in varchar2 default null)
396 return varchar2
397 is
398 segnum fnd_id_flex_segments.segment_num%type;
399 colname fnd_id_flex_segments.application_column_name%type;
400 seg_value_set_id fnd_flex_value_sets.flex_value_set_id%type;
401
402 sep fnd_id_flex_structures.concatenated_segment_delimiter%type;
403 op1 seg_array;
404 op2 seg_array;
405
406 i number;
407
408 table_alias varchar2(40);
409 buffer varchar2(2000);
410
411 l_qualifier fnd_segment_attribute_types.segment_attribute_type%type;
412 l_segnum fnd_id_flex_segments.segment_num%type;
413 l_id_flex_num number;
414 begin
415 IF (g_print_debug) THEN
416 fa_rx_util_pkg.debug('fa_rx_flex_pkg.flex_sql()+');
417 END IF;
418
419 --
420 -- Check the validity of some of the parameters
421 --
422 if p_mode not in ('SELECT', 'WHERE', 'HAVING', 'ORDER BY', 'GROUP BY') or
423 p_function not in ('=', '<', '>', '<=', '>=', '!=', 'BETWEEN') then
424 raise invalid_argument;
425 end if;
426
427 l_id_flex_num := get_id_flex_num(p_application_id, p_id_flex_code, p_id_flex_num);
428
429 -- Delimiter
430 sep := get_segment_delimiter(
431 p_application_id,
432 p_id_flex_code,
433 l_id_flex_num);
434
435 -- Actual table alias used in SQL statement
436 if p_table_alias is null then table_alias := null;
437 else table_alias := p_table_alias ||'.';
438 end if;
439
440
441 --
442 -- Initialize the op1 and op2 seg_arrays
443 --
444 if p_function in ('=', '<', '>', '<=', '>=', '!=') then
445 if p_qualifier = 'ALL' then
446 separate_segments(op1, p_operand1, sep);
447 else
448 op1(1) := p_operand1;
449 end if;
450 elsif p_function in ('BETWEEN') then
451 if p_qualifier = 'ALL' then
452 separate_segments(op1, p_operand1, sep);
453 separate_segments(op2, p_operand2, sep);
454 else
455 op1(1) := p_operand1;
456 op2(1) := p_operand2;
457 end if;
458 end if;
459
460
461 --
462 -- Finally ready for to compile
463 --
464 if cflex%isopen then close cflex;
465 end if;
466 begin
467 l_segnum := to_number(p_qualifier);
468 l_qualifier := null;
469 exception
470 when VALUE_ERROR then
471 l_segnum := null;
472 l_qualifier := p_qualifier;
473 end;
474 open cflex(p_application_id,
475 p_id_flex_code,
476 l_id_flex_num,
477 l_qualifier,
478 l_segnum);
479 i := 1;
480 loop
481 --
482 -- For each row fetched by cflex
483 -- build up the SQL clause
484 fetch cflex into segnum, colname, seg_value_set_id;
485 exit when cflex%notfound;
486
487 if p_mode in ('SELECT', 'GROUP BY') then
488 if buffer is not null then buffer := buffer || '||''' ||sep|| '''||';
489 end if;
490 buffer := buffer||table_alias||colname;
491 elsif p_mode = 'ORDER BY' then
492 if buffer is not null then buffer := buffer || ',';
493 end if;
494 buffer := buffer||table_alias||colname;
495 elsif p_mode in ('WHERE', 'HAVING') then
496 if buffer is not null then buffer := buffer || ' and ';
497 end if;
498 buffer := buffer||table_alias||colname;
499
500 if p_function in ('=', '<', '>', '<=', '>=', '!=') then
501 buffer := buffer || p_function || '''' || op1(i) || '''';
502 elsif p_function in ('BETWEEN') then
503 buffer := buffer || ' BETWEEN '''|| op1(i) || ''' AND '''|| op2(i) ||'''';
504 end if;
505 end if;
506 i := i + 1;
507 end loop;
508 close cflex;
509
510 if buffer is null then
511 --
512 -- If the buffer is null then that means
513 -- that cflex cursor returned no rows.
514 -- One of the arguments MUST be incorrect
515 -- Output the parameters and raise error
516 --
517 IF (g_print_debug) THEN
518 fa_rx_util_pkg.debug('Error in fa_rx_flex_pkg.flex_sql()');
519 fa_rx_util_pkg.debug('flex_sql: ' || 'Application ID = '||to_char(p_application_id));
520 fa_rx_util_pkg.debug('flex_sql: ' || 'ID Flex Code = '||p_id_flex_code);
521 fa_rx_util_pkg.debug('flex_sql: ' || 'ID Flex Num = '||to_char(l_id_flex_num));
522 fa_rx_util_pkg.debug('flex_sql: ' || 'Qualifier = '||p_qualifier);
523 fa_rx_util_pkg.debug('flex_sql: ' || ' **** No rows returned **** ');
524 fa_rx_util_pkg.debug('fa_rx_flex_pkg.flex_sql(EXCEPTION)-');
525 END IF;
526 raise invalid_argument;
527 end if;
528
529 IF (g_print_debug) THEN
530 fa_rx_util_pkg.debug('fa_rx_flex_pkg.flex_sql('||buffer||')-');
531 END IF;
532 return buffer;
533
534 exception
535 when others then
536 if cflex%isopen then
537 close cflex;
538 end if;
539
540 IF (g_print_debug) THEN
541 fa_rx_util_pkg.debug('Exception in fa_rx_flex_pkg.flex_sql()');
542 fa_rx_util_pkg.debug('flex_sql: ' || 'Application ID = '||to_char(p_application_id));
543 fa_rx_util_pkg.debug('flex_sql: ' || 'ID Flex Code = '||p_id_flex_code);
544 fa_rx_util_pkg.debug('flex_sql: ' || 'ID Flex Num = '||to_char(l_id_flex_num));
545 fa_rx_util_pkg.debug('flex_sql: ' || 'Table Alias = '||p_table_alias);
546 fa_rx_util_pkg.debug('flex_sql: ' || 'Mode = '||p_mode);
547 fa_rx_util_pkg.debug('flex_sql: ' || 'Qualifier = '||p_qualifier);
548 fa_rx_util_pkg.debug('flex_sql: ' || 'Function = '||p_function);
549 fa_rx_util_pkg.debug('flex_sql: ' || 'Operand 1 = '||p_operand1);
550 fa_rx_util_pkg.debug('flex_sql: ' || 'Operand 2 = '||p_operand2);
551 fa_rx_util_pkg.debug('fa_rx_flex_pkg.flex_sql(EXCEPTION)-');
552 END IF;
553 raise;
554 end flex_sql;
555
556
557
558 -------------------------------------------------------------------------
559 --
560 -- FUNCTION get_value
561 --
562 -- Parameters
563 -- p_application_id Application ID of key flexfield
564 -- p_id_flex_code Flexfield code
565 -- p_id_flex_num Flexfield structure num
566 -- p_qualifier Flexfield qualifier or segment number
567 -- p_ccid Code combination ID
568 --
569 -- Returns VARCHAR2
570 -- Returns the concatenated segment values of the key flexfield
571 --
572 -- Description
573 -- There is no equivalent for this function. This function takes
574 -- the code combination id for the key flexfield and returns the
575 -- actual segment values. This function can be used within
576 -- the after fetch triggers for RXi reports to retrieve the value.
577 --
578 -- Modification History
579 -- KMIZUTA 02-APR-99 Created.
580
581 -- Bug2951118 LGANDHI 25-MAY-2003 Modified to including Cacheing routine.
582 --
583 -------------------------------------------------------------------------
584 function get_value(
585 p_application_id in number,
586 p_id_flex_code in varchar2,
587 p_id_flex_num in number default NULL,
588 p_qualifier in varchar2,
589 p_ccid in number) return varchar2
590 is
591 sqlstmt varchar2(2000);
592
593 l_table_name varchar2(30);
594 l_unique_id_column_name varchar2(30);
595 l_sep fnd_id_flex_structures.concatenated_segment_delimiter%type;
596 c integer;
597 rows integer;
598 segnum fnd_id_flex_segments.segment_num%type;
599 colname fnd_id_flex_segments.application_column_name%type;
600 seg_value_set_id fnd_flex_value_sets.flex_value_set_id%type;
601 buffer varchar2(2000);
602
603 l_qualifier fnd_segment_attribute_types.segment_attribute_type%type;
604 l_segnum fnd_id_flex_segments.segment_num%type;
605 l_id_flex_num number;
606 l_counter number := 0;
607 begin
608 IF (g_print_debug) THEN
609 fa_rx_util_pkg.debug('fa_rx_flex_pkg.get_value()+');
610 END IF;
611
612 IF(p_application_id IS NOT NULL AND p_id_flex_code IS NOT NULL AND p_id_flex_num
613 IS NOT NULL AND p_qualifier IS NOT NULL AND p_ccid IS NOT NULL ) THEN
614
615 LOOP
616
617 IF (fa_rx_flex_val_t.EXISTS(l_counter)) THEN
618
619
620 IF((fa_rx_flex_val_t(l_counter).application_id = p_application_id)
621 AND (fa_rx_flex_val_t(l_counter).id_flex_code = p_id_flex_code)
622 AND (fa_rx_flex_val_t(l_counter).id_flex_num = p_id_flex_num)
623 AND (fa_rx_flex_val_t(l_counter).qualifier = p_qualifier)
624 AND (fa_rx_flex_val_t(l_counter).ccid = p_ccid))
625 THEN
626
627
628 RETURN fa_rx_flex_val_t(l_counter).buffer;
629
630 ELSE
631
632 l_counter:=l_counter + 1;
633
634 END IF;
635
636
637 END IF;
638
639
640 EXIT WHEN (NOT fa_rx_flex_val_t.EXISTS(l_counter));
641
642 END LOOP;
643
644 END IF;
645
646
647 l_id_flex_num := get_id_flex_num(p_application_id, p_id_flex_code, p_id_flex_num);
648
649 --
650 -- Get the code combination table and the
651 -- Primary key for that table
652 --
653 select application_table_name , unique_id_column_name
654 into l_table_name, l_unique_id_column_name
655 from fnd_id_flexs
656 where application_id = p_application_id
657 and id_flex_code = p_id_flex_code;
658
659 -- Get the segment delimiter
660 l_sep := get_segment_delimiter(
661 p_application_id,
662 p_id_flex_code,
663 l_id_flex_num);
664
665
666 --
667 -- We are going to build the select statment which
668 -- is going to get us the value we want.
669 --
670
671 --
672 -- First build the select statment
673 --
674 sqlstmt := null;
675 if cflex%isopen then close cflex;
676 end if;
677 begin
678 l_segnum := to_number(p_qualifier);
679 l_qualifier := null;
680 exception
681 when VALUE_ERROR then
682 l_segnum := null;
683 l_qualifier := p_qualifier;
684 end;
685 open cflex(p_application_id,
686 p_id_flex_code,
687 l_id_flex_num,
688 l_qualifier,
689 l_segnum);
690
691 loop
692 --
693 -- For each row, get the column name of the
694 -- code combinations table which has the segment value.
695 --
696 fetch cflex into segnum, colname, seg_value_set_id;
697 exit when cflex%notfound;
698
699 if sqlstmt is not null then
700 sqlstmt := sqlstmt || '||''' || l_sep || '''||' ||colname;
701 else
702 sqlstmt := colname;
703 end if;
704 end loop;
705 if sqlstmt is null then
706 --
707 -- If the sqlstmt is null then that means
708 -- that cflex cursor returned no rows.
709 -- One of the arguments MUST be incorrect
710 -- Output the parameters and raise error
711 --
712 fa_rx_util_pkg.log('Error in fa_rx_flex_pkg.get_value()');
713 fa_rx_util_pkg.log('Application ID = '||to_char(p_application_id));
714 fa_rx_util_pkg.log('ID Flex Code = '||p_id_flex_code);
715 fa_rx_util_pkg.log('ID Flex Num = '||to_char(l_id_flex_num));
716 fa_rx_util_pkg.log('Qualifier = '||p_qualifier);
717 fa_rx_util_pkg.log(' **** No rows returned **** ');
718
719 IF (g_print_debug) THEN
720 fa_rx_util_pkg.debug('fa_rx_flex_pkg.get_value(EXCEPTION)-');
721 END IF;
722 raise invalid_argument;
723 end if;
724
725 sqlstmt := 'SELECT '||sqlstmt||' FROM '||l_table_name||' WHERE '||l_unique_id_column_name||' = :ccid';
726 close cflex;
727 IF (g_print_debug) THEN
728 fa_rx_util_pkg.debug('get_value: ' || 'Executing SELECT...');
729 fa_rx_util_pkg.debug('get_value: ' || sqlstmt);
730 END IF;
731
732 --
733 -- We have the SELECT statement.
734 -- Now execute it.
735 c := dbms_sql.open_cursor;
736 dbms_sql.parse(c, sqlstmt, dbms_sql.native);
737 dbms_sql.bind_variable(c, 'ccid', p_ccid);
738 dbms_sql.define_column(c, 1, buffer, 2000);
739 rows := dbms_sql.execute(c);
740 rows := dbms_sql.fetch_rows(c);
741 if rows <= 0 then
742 IF (g_print_debug) THEN
743 fa_rx_util_pkg.debug('Invalid CCID in fa_rx_flex_pkg.get_value()');
744 fa_rx_util_pkg.debug('get_value: ' || 'CCID = '||to_char(p_ccid));
745 fa_rx_util_pkg.debug('fa_rx_flex_pkg.get_value(NO_DATA_FOUND)-');
746 END IF;
747 raise no_data_found;
748 end if;
749 dbms_sql.column_value(c, 1, buffer);
750 dbms_sql.close_cursor(c);
751
752 IF (g_print_debug) THEN
753 fa_rx_util_pkg.debug('fa_rx_flex_pkg.get_value('||buffer||')-');
754 END IF;
755
756
757 IF (p_application_id IS NOT NULL AND p_id_flex_code IS NOT NULL AND p_id_flex_num
758 IS NOT NULL AND p_qualifier IS NOT NULL AND p_ccid IS NOT NULL ) THEN
759
760
761 fa_rx_flex_val_t(l_counter).application_id := p_application_id ;
762 fa_rx_flex_val_t(l_counter).id_flex_code := p_id_flex_code ;
763 fa_rx_flex_val_t(l_counter).id_flex_num := p_id_flex_num ;
764 fa_rx_flex_val_t(l_counter).qualifier := p_qualifier;
765 fa_rx_flex_val_t(l_counter).ccid := p_ccid;
766 fa_rx_flex_val_t(l_counter).buffer := buffer;
767
768
769 RETURN fa_rx_flex_val_t(l_counter).buffer;
770
771 ELSE
772
773 RETURN buffer ;
774
775 END IF;
776
777
778
779
780 exception
781 when others then
782 if cflex%isopen then
783 close cflex;
784 end if;
785 if dbms_sql.is_open(c) then
786 dbms_sql.close_cursor(c);
787 end if;
788
789 fa_rx_util_pkg.log('Exception in fa_rx_flex_pkg.get_value()');
790 fa_rx_util_pkg.log('Application_ID = ' || to_char(p_Application_ID));
791 fa_rx_util_pkg.log('ID Flex Code = ' || p_ID_Flex_Code);
792 fa_rx_util_pkg.log('ID Flex Num = ' || to_char(l_id_flex_num));
793 fa_rx_util_pkg.log('Qualifier = ' || p_qualifier);
794 fa_rx_util_pkg.log('CCID = ' || to_char(p_ccid));
795
796 IF (g_print_debug) THEN
797 fa_rx_util_pkg.debug('fa_rx_flex_pkg.get_value(EXCEPTION)-');
798 END IF;
799 raise;
800 end get_value;
801
802
803
804 -------------------------------------------------------------------------
805 --
806 -- FUNCTION get_description
807 --
808 -- Parameters
809 -- p_application_id Application ID of key flexfield
810 -- p_id_flex_code Flexfield code
811 -- p_id_flex_num Flexfield structure num
812 -- p_qualifier Flexfield qualifier or segment number
813 -- p_data Flexfield Segments
814 --
815 -- Returns VARCHAR2
816 -- Returns the concatenated description of the key flexfield
817 --
818 -- Description
819 -- This function mimics the functionality of the userexit FLEXIDVAL.
820 -- Given the parameters, this function is equivalent to:
821 -- FND FLEXIDVAL
822 -- CODE=":p_id_flex_code"
823 -- APPL_SHORT_NAME="Short name from :p_application_id"
824 -- DATA=":p_data"
825 -- NUM=":p_id_flex_num"
826 -- DISPLAY=":p_qualifier"
827 -- IDISPLAY=":p_qualifier"
828 -- DESCRIPTION=":This is the return value"
829 --
830 -- Restrictions
831 -- No support for SHOWDEPSEG parameter
832 -- No support for VALUE, APROMPT, LPROMPT, PADDED_VALUE, SECURITY parameter
833 -- p_qualifier must be 'ALL' or a valid qualifier name or segment number.
834 -- DISPLAY and IDISPLAY are always the same p_qualifier value.
835 --
836 -- Modification History
837 -- KMIZUTA 02-APR-99 Created.
838 --
839 -- Bug2948619 LGANDHI 25-MAY-2003 Modified to including Cacheing routine.
840 -- Bug2991482 RRAVUNNY Modified the function to accomodate the parent value for
841 -- dependant value sets.
842
843 -------------------------------------------------------------------------
844 function get_description(
845 p_application_id in number,
846 p_id_flex_code in varchar2,
847 p_id_flex_num in number default NULL,
848 p_qualifier in varchar2,
849 p_data in varchar2) return varchar2
850 is
851 segments seg_array;
852 err_num number;
853 sep fnd_id_flex_structures.concatenated_segment_delimiter%type;
854
855 segnum fnd_id_flex_segments.segment_num%type;
856 colname fnd_id_flex_segments.application_column_name%type;
857 seg_value_set_id fnd_flex_value_sets.flex_value_set_id%type;
858
859 seg_value varchar2(50);
860 seg_desc fnd_flex_values_vl.description%type;
861 concatenated_description varchar2(2000);
862 seg_parent_value FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW%type;
863 i number;
864
865 l_qualifier fnd_segment_attribute_types.segment_attribute_type%type;
866 l_segnum fnd_id_flex_segments.segment_num%type;
867 l_id_flex_num number;
868 l_counter number := 0;
869 found boolean;
870 begin
871
872 -- Bug2948619 LGANDHI 25-MAY-2003 Modified to including Cacheing routine.
873
874 IF (p_application_id IS NOT NULL AND p_id_flex_code IS NOT NULL AND p_id_flex_num IS NOT NULL
875 AND p_qualifier IS NOT NULL AND p_data IS NOT NULL ) THEN
876
877
878 LOOP
879
880 IF (fa_rx_flex_desc_t.EXISTS(l_counter)) THEN
881 IF ( (fa_rx_flex_desc_t(l_counter).application_id = p_application_id)
882 AND (fa_rx_flex_desc_t(l_counter).id_flex_code=p_id_flex_code)
883 AND (fa_rx_flex_desc_t(l_counter).id_flex_num = p_id_flex_num)
884 AND (fa_rx_flex_desc_t(l_counter).qualifier = p_qualifier)
885 AND (fa_rx_flex_desc_t(l_counter).data = p_data) )
886 THEN
887 RETURN fa_rx_flex_desc_t(l_counter).concatenated_description;
888 ELSE
889 l_counter := l_counter + 1;
890 END IF;
891 END IF;
892
893 EXIT WHEN (NOT fa_rx_flex_desc_t.EXISTS(l_counter));
894
895 END LOOP;
896
897 END IF;
898
899
900
901 l_id_flex_num := get_id_flex_num(p_application_id, p_id_flex_code, p_id_flex_num);
902 -- Get segment delimiter
903 sep := get_segment_delimiter(
904 p_application_id,
905 p_id_flex_code,
906 l_id_flex_num);
907
908
909 -- Separate out the data
910 if p_qualifier = 'ALL' then
911 separate_segments(segments, p_data, sep);
912 else
913 segments(1) := p_data;
914 end if;
915
916 i := 1;
917 if cflex%isopen then close cflex;
918 end if;
919 begin
920 l_segnum := to_number(p_qualifier);
921 l_qualifier := null;
922 exception
923 when others then
924 err_num := sqlcode;
925 seg_desc := null;
926 l_segnum := null;
927 l_qualifier := p_qualifier;
928 end;
929
930 open cflex(p_application_id,
931 p_id_flex_code,
932 l_id_flex_num,
933 l_qualifier,
934 l_segnum);
935 loop
936 --
937 -- For each row, get its meaning
938 --
939
940 fetch cflex into segnum, colname, seg_value_set_id;
941 exit when cflex%notfound;
942 seg_parent_value := Null;
943 if p_qualifier = 'ALL' then
944 seg_parent_value := Get_Parent_Value(segments,
945 p_application_id ,
946 p_id_flex_code ,
947 l_id_flex_num ,
948 seg_value_set_id);
949 End If;
950 seg_value := segments(i);
951 begin
952 seg_desc := fa_rx_shared_pkg.get_flex_val_meaning(seg_value_set_id, null, seg_value,seg_parent_value);
953 dbms_output.put_line('seg_parent_value = '||seg_parent_value||' , seg_desc = '||seg_desc);
954 exception
955 when no_data_found then
956 seg_desc := null;
957 when value_error then
958 seg_desc := null;
959 when others then
960 err_num := sqlcode;
961 seg_desc := null;
962 end;
963
964 if concatenated_description is not null then
965 concatenated_description := concatenated_description || sep;
966 end if;
967 concatenated_description := concatenated_description ||seg_desc;
968
969 i := i + 1;
970 end loop;
971 close cflex;
972
973 if concatenated_description is null then
974 --
975 -- If the concatenated_description is null then that means
976 -- that cflex cursor returned no rows.
977 -- One of the arguments MUST be incorrect
978 -- Output the parameters and raise error
979 --
980 raise invalid_argument;
981 end if;
982
983
984
985 IF (p_application_id IS NOT NULL AND p_id_flex_code IS NOT NULL AND p_id_flex_num IS NOT NULL
986 AND p_qualifier IS NOT NULL AND p_data IS NOT NULL ) THEN
987
988 fa_rx_flex_desc_t(l_counter).application_id := p_application_id;
989 fa_rx_flex_desc_t(l_counter).id_flex_code := p_id_flex_code;
990 fa_rx_flex_desc_t(l_counter).id_flex_num := p_id_flex_num;
991 fa_rx_flex_desc_t(l_counter).qualifier := p_qualifier;
992 fa_rx_flex_desc_t(l_counter).data := p_data;
993 fa_rx_flex_desc_t(l_counter).concatenated_description :=concatenated_description;
994
995 RETURN fa_rx_flex_desc_t(l_counter).concatenated_description ;
996
997 ELSE
998 RETURN concatenated_description ;
999
1000 END IF;
1001
1002 exception
1003 when no_data_found then
1004 if cflex%isopen then
1005 close cflex;
1006 end if;
1007
1008 raise;
1009
1010 when others then
1011 if cflex%isopen then
1012 close cflex;
1013 end if;
1014
1015 raise;
1016
1017 end get_description;
1018
1019
1020 end fa_rx_flex_pkg;
[Home] [Help]