[Home] [Help]
PACKAGE BODY: APPS.OE_INLINE_FLEX
Source
1 package body oe_inline_flex as
2 /* $Header: OEXFILFB.pls 120.3 2006/02/21 12:06:41 aycui ship $ */
3
4 TYPE tab_id_type is table of number index by binary_integer;
5 TYPE tab_date_type is table of date index by binary_integer;
6 TYPE tab_varchar2_type is table of varchar2(240) index by binary_integer;
7 TYPE tab_char_type is table of char index by binary_integer;
8
9 flex_name tab_varchar2_type;
10 flex_num tab_id_type;
11 flex_column_name tab_varchar2_type;
12 flex_qualifier tab_varchar2_type;
13 flex_prompt tab_varchar2_type;
14 flex_pos tab_id_type;
15 flex_start number;
16 flex_end number;
17 flex_id number;
18 flex_short_name varchar(240);
19 flex_separator char;
20 list_segs tab_id_type;
21
22
23 flex_segments number := 0 ;
24
25 no_qualifier EXCEPTION;
26
27
28 procedure setup_flexfield( flex_code in varchar2,
29 structure_id in number ) is
30
31 cursor sel_flex_structure(appl_id in number,
32 flex_code in varchar2,
33 structure_id in number) is
34 select struct.concatenated_segment_delimiter separator,
35 vs.flex_value_set_name name,
36 seg.segment_num num,
37 seg.application_column_name column_name,
38 qual.segment_attribute_type qualifier,
39 seg.form_left_prompt prompt,
40 rownum pos,
41 'LOCATION_ID_SEGMENT_'||to_char(seg.segment_num) column_num
42 from fnd_flex_value_sets vs,
43 fnd_id_flex_segments_vl seg,
44 fnd_segment_attribute_values qual,
45 fnd_id_flex_structures struct
46 where seg.application_id = appl_id
47 and seg.id_flex_code = flex_code
48 and seg.flex_value_set_id = vs.flex_value_set_id
49 and seg.id_flex_num = structure_id
50 and qual.id_flex_code(+) = flex_code
51 and qual.id_flex_num(+) = structure_id
52 and qual.application_id(+) = appl_id
53 and qual.application_column_name(+) = seg.application_column_name
54 and qual.attribute_value(+) = 'Y'
55 and seg.enabled_flag = 'Y'
56 and struct.id_flex_code = seg.id_flex_code
57 and struct.id_flex_num = seg.id_flex_num
58 and struct.application_id=seg.application_id
59 order by seg.segment_num, qual.segment_attribute_type;
60
61 segment number;
62 prior_segment number;
63
64 --
65 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
66 --
67 begin
68
69 IF l_debug_level > 0 THEN
70 oe_debug_pub.add( 'INSIDE SETUP_FLEXFIELD' ) ;
71 END IF;
72 flex_start := flex_segments + 1;
73 segment := 0;
74 prior_segment := 0;
75
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'FLEX_CODE:'||FLEX_CODE ) ;
78 END IF;
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add( 'STRUCTURE_ID:'||TO_CHAR ( STRUCTURE_ID ) ) ;
81 END IF;
82
83 for location in sel_flex_structure( 222, flex_code, structure_id)
84 loop
85
86 IF l_debug_level > 0 THEN
87 oe_debug_pub.add( 'LOCATION.NAME='||LOCATION.NAME ) ;
88 END IF;
89 IF l_debug_level > 0 THEN
90 oe_debug_pub.add( 'LOCATION.NUM='||TO_CHAR ( LOCATION.NUM ) ) ;
91 END IF;
92 /*------------------------------------------------------------------+
93 | The Flex Preprocessor assumes that the Line Number Matches the |
94 | Column Number in Key Flexfield Definition |
95 | Eg. Line# Column# Column Name |
96 | 1 1 LOCATION_SEGMENT_ID_1 |
97 | 2 2 LOCATION_SEGMENT_ID_2 |
98 | So we need to check that this assumption is true |
99 +------------------------------------------------------------------*/
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'LOCATION.NAME : '||LOCATION.NAME ) ;
102 END IF;
103 IF l_debug_level > 0 THEN
104 oe_debug_pub.add( 'LOCATION.NUM : '||LOCATION.NUM ) ;
105 END IF;
106 IF l_debug_level > 0 THEN
107 oe_debug_pub.add( 'LOCATION.COLUMN_NAME : '||LOCATION.COLUMN_NAME ) ;
108 END IF;
109 IF l_debug_level > 0 THEN
110 oe_debug_pub.add( 'LOCATION.QUALIFIER : '||LOCATION.QUALIFIER ) ;
111 END IF;
112 IF l_debug_level > 0 THEN
113 oe_debug_pub.add( 'LOCATION.PROMPT : '||LOCATION.PROMPT ) ;
114 END IF;
115 IF l_debug_level > 0 THEN
116 oe_debug_pub.add( 'LOCATION.POS : '||LOCATION.POS ) ;
117 END IF;
118 IF l_debug_level > 0 THEN
119 oe_debug_pub.add( 'LOCATION.COLUMN_NUM :'||LOCATION.COLUMN_NUM ) ;
120 END IF;
121
122 if location.column_name like 'LOCATION_ID_SEGMENT_%' and
123 location.column_name <> location.column_num then
124 IF l_debug_level > 0 THEN
125 oe_debug_pub.add( 'THE LINE NUMBER: '||TO_CHAR ( LOCATION.NUM ) ||' AND COLUMN SEGMENT NUMBER FOR '||LOCATION.COLUMN_NAME ||' MUST BE EQUAL.' ) ;
126 END IF;
127 fnd_message.set_name('AR','GENERIC_MESSAGE');
128 fnd_message.set_token('GENERIC_TEXT','The Line Number '
129 ||to_char(location.num)
130 ||' and Column Segment Number for '||location.column_name
131 ||' must be equal.');
132 app_exception.raise_exception;
133 end if;
134
135 -- if the segment number is not the prior segment number then that means
136 -- total segment is not more.we then store the sequenc of location,
137 -- value_set name,application_column_name,its position in the sequence
138 -- and the qualifier
139
140 if ( location.num <> prior_segment ) then
141
142 flex_segments := flex_segments + 1;
143 flex_num( flex_segments ) := location.num;
144 flex_name( flex_segments ) := location.name;
145 flex_column_name( flex_segments ) := location.column_name;
146 flex_pos( flex_segments ) := location.pos;
147 flex_qualifier( flex_segments ) := ' ' || location.qualifier || ' ' ;
148 flex_prompt( flex_segments ) := location.prompt;
149 flex_separator := location.separator;
150 prior_segment := location.num;
151 IF l_debug_level > 0 THEN
152 oe_debug_pub.add( 'LOCATION_QUALIFIER='||LOCATION.QUALIFIER ) ;
153 END IF;
154 else
155
156 /*------------------------------------------------------------------+
157 | Multiple Qualifiers per segment are supported, each qualifier |
158 | will be space separated. |
159 | Example: GL_ACCOUNT GL_GLOBAL |
160 +------------------------------------------------------------------*/
161
162 flex_qualifier( flex_segments ) := flex_qualifier( flex_segments ) ||
163 location.qualifier || ' ' ;
164
165 end if;
166
167 end loop;
168
169 flex_end := flex_segments ;
170 flex_short_name := flex_code;
171 flex_id := structure_id;
172
173 IF l_debug_level > 0 THEN
174 oe_debug_pub.add( 'START POS='||TO_CHAR ( FLEX_START ) ) ;
175 END IF;
176 IF l_debug_level > 0 THEN
177 oe_debug_pub.add( 'END POS='||TO_CHAR ( FLEX_END ) ) ;
178 END IF;
179
180
181 end setup_flexfield;
182
183
184 function token_expand( word in varchar2,
185 i in number ) return varchar2 is
186 str varchar2(1000);
187
188 --
189 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
190 --
191 begin
192
193 /*----------------------------------------------------------------------+
194 | Expand segment tokens if requested |
195 *----------------------------------------------------------------------*/
196
197 str := replace( word, '%COUNTER%', i );
198 str := replace( str, '%COLUMN%', flex_column_name(i) );
199 str := replace( str, '%POSITION%', flex_pos(i));
200 str := replace( str, '%QUALIFIER%', ltrim(rtrim(flex_qualifier(i))));
201 str := replace( str, '%NUMBER%', flex_num(i));
202 str := replace( str, '%SEPARATOR%', flex_separator);
203 str := replace( str, '%PROMPT%', flex_prompt(i));
204
205 /*----------------------------------------------------------------------+
206 | Expand Next segment tokens if requested |
207 | Next is defined to be the next physical segment in the structure |
208 *----------------------------------------------------------------------*/
209
210 if i < flex_end then
211 str := replace( str, '%NEXT_COUNTER%', i+1 - flex_start );
212 str := replace( str, '%NEXT_COLUMN%', flex_column_name(i+1) );
213 str := replace( str, '%NEXT_POSITION%', flex_pos(i+1));
214 str := replace( str, '%NEXT_QUALIFIER%', ltrim(rtrim(flex_qualifier(i+1))));
215 str := replace( str, '%NEXT_NUMBER%', flex_num(i+1));
216 str := replace( str, '%NEXT_PROMPT%', flex_prompt(i+1));
217 else
218 str := replace( str, '%NEXT_COUNTER%', 0 );
219 str := replace( str, '%NEXT_COLUMN%', '''''' );
220 str := replace( str, '%NEXT_POSITION%', 0 );
221 str := replace( str, '%NEXT_QUALIFIER%', '''''' );
222 str := replace( str, '%NEXT_NUMBER%', 0 );
223 str := replace( str, '%NEXT_PROMPT%', '''''' );
224 end if;
225
226 /*----------------------------------------------------------------------+
227 | Expand previous segment tokens if requested |
228 *----------------------------------------------------------------------*/
229
230 if i > flex_start then
231 str := replace( str, '%PREVIOUS_COUNTER%', i-1 - flex_start);
232 str := replace( str, '%PREVIOUS_COLUMN%', flex_column_name(i-1) );
233 str := replace( str, '%PREVIOUS_POSITION%', flex_pos(i-1));
234 str := replace( str, '%PREVIOUS_QUALIFIER%',ltrim(rtrim(flex_qualifier(i-1))));
235 str := replace( str, '%PREVIOUS_NUMBER%', flex_num(i-1));
236 str := replace( str, '%PREVIOUS_PROMPT%', ltrim(rtrim(flex_prompt(i-1))));
237 end if;
238
239
240 /* Previous number of element i is 0 */
241 if i = flex_start
242 then
243 str := replace( str, '%PREVIOUS_NUMBER%', 0);
244 end if;
245
246 return( str );
247
248 exception
249 when no_data_found then
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add( 'WHEN NO DATA FOUND TOKEN_EXPAN' ) ;
252 END IF;
253
254 when others then
255 IF l_debug_level > 0 THEN
256 oe_debug_pub.add( 'WHEN OTHERS IN TOKEN_EXPAND' ) ;
257 END IF;
258
259 end token_expand;
260
261
262 function delimit( word in varchar2 ) return varchar2 is
263 --
264 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
265 --
266 begin
267 return( ' ' || word || ' ' );
268 end delimit;
269
270
271 procedure add_to_list( list in out NOCOPY /* file.sql.39 change */ varchar2, value in varchar2 ) is
272 --
273 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
274 --
275 begin
276 IF l_debug_level > 0 THEN
277 oe_debug_pub.add( '>> ADD_TO_LIST ( ' || LIST || ' , ' || VALUE || ' ) ' ) ;
278 END IF;
279
280 /* skip, the instr only searches for a character string(value) in list */
281 if instr( list, value ) <> 0
282 then
283 list := replace( list, value , null );
284 else
285 /* No Changes - so add to the end of the list */
286 list := list || value;
287 end if;
288
289 IF l_debug_level > 0 THEN
290 oe_debug_pub.add( '<< ADD_TO_LIST ( ' || LIST || ' , ' || VALUE || ' ) ' ) ;
291 END IF;
292 end add_to_list;
293
294
295
296 function qualifier_list( qualifiers in varchar2 ) return varchar2 is
297 i number;
298 k number;
299 step number;
300 segment number;
301 quals varchar2( 240 );
302 qual varchar2( 30 );
303 list varchar2( 1000 );
304 qual_found boolean;
305 startpos number;
306 endpos number;
307
308 --
309 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
310 --
311 begin
312
313 IF l_debug_level > 0 THEN
314 oe_debug_pub.add( 'INSIDE QUALIFIER_LIST' ) ;
315 END IF;
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add( 'QUALIFIERS='||QUALIFIERS ) ;
318 END IF;
319
320 IF l_debug_level > 0 THEN
321 oe_debug_pub.add( '>> QUALIFIER_LIST ( ' || ' , ' || QUALIFIERS || ' ) ' ) ;
322 END IF;
323
324 quals := qualifiers;
325 list := null;
326
327
328 if quals = 'ALLREV' then
329 IF l_debug_level > 0 THEN
330 oe_debug_pub.add( 'IF ALLREV' ) ;
331 END IF;
332 startpos := flex_end - flex_start;
333 endpos := 0;
334 step:=-1;
335 IF l_debug_level > 0 THEN
336 oe_debug_pub.add( 'STARTPOS='||TO_CHAR ( STARTPOS ) ) ;
337 END IF;
338 end if;
339
340 i := startpos;
341 for k in 0 .. flex_end - flex_start
342 loop
343 IF l_debug_level > 0 THEN
344 oe_debug_pub.add( 'INSIDE LOOP' ) ;
345 END IF;
346 IF l_debug_level > 0 THEN
347 oe_debug_pub.add( 'FLEX_START='||TO_CHAR ( FLEX_START ) ) ;
348 END IF;
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( 'I='||TO_CHAR ( I ) ) ;
351 END IF;
352 segment := flex_start + i;
353 IF l_debug_level > 0 THEN
354 oe_debug_pub.add( 'SEGMENT='||TO_CHAR ( SEGMENT ) ) ;
355 END IF;
356 IF l_debug_level > 0 THEN
357 oe_debug_pub.add( 'I=' || I || ' ' || LIST || ' --- ' || SEGMENT ) ;
358 END IF;
359
360 if list is not null then
361 list := list || delimit( segment );
362 else
363 list := delimit( segment ) ;
364 end if;
365 IF l_debug_level > 0 THEN
366 oe_debug_pub.add( 'LIST='||LIST ) ;
367 END IF;
368
369 i := i + step;
370 end loop;
371
372 IF l_debug_level > 0 THEN
373 oe_debug_pub.add( '<< QUALIFIER_LIST: ' || LIST ) ;
374 END IF;
375 return( list );
376
377 end qualifier_list;
378
379
380 procedure expand( qualifiers in varchar2,
381 separator in varchar2,
382 word in varchar2,
383 structure out nocopy varchar2) IS
384
385
386 str varchar2(20000);
387 segments varchar2(1000);
388 segment number;
389 l_segments varchar2(1000);
390 l_segment varchar2(1000);
391 --
392 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
393 --
394 begin
395
396 IF l_debug_level > 0 THEN
397 oe_debug_pub.add( 'INSIDE EXPAND' ) ;
398 END IF;
399 initialize;
400 IF l_debug_level > 0 THEN
401 oe_debug_pub.add( 'AFTER INITIALIZE ' ) ;
402 END IF;
403
404 IF l_debug_level > 0 THEN
405 oe_debug_pub.add( '>> EXPAND ( ' || ' , ' || QUALIFIERS || ' , ' || SEPARATOR || ' , ' || WORD || ' ) ' ) ;
406 END IF;
407
408 segment := flex_start;
409
410 /*-------------------------------------------------------------------------+
411 | Generate a list of segments, index values based on the qualifier |
412 | description string passed into the function. |
413 | e,g 1.2.3.4 is the value inside segments returned by qualifier_list |
414 +-------------------------------------------------------------------------*/
415
416 segments := qualifier_list(qualifiers );
417
418 IF l_debug_level > 0 THEN
419 oe_debug_pub.add( 'SEGMENTS='||SEGMENTS ) ;
420 END IF;
421 str := null;
422
423 /*-------------------------------------------------------------------------+
424 | Loop over each selected segment, expanding any tokens as requested |
425 +-------------------------------------------------------------------------*/
426
427 /* The following is added as a workaround for the Initialization issue in
428 arp_standard */
429 begin
430
431 if arp_standard.get_next_word( l_segments, l_segment ) then
432 oe_debug_pub.add( '<< LOOP** DUMMY TRUE: ' ) ;
433 else
434 oe_debug_pub.add( '<< LOOP** DUMMY FALSE: ' ) ;
435 end if;
436 exception
437 when others then
438 oe_debug_pub.add( '<< LOOP** DUMMY OTHERS : '||sqlerrm ) ;
439 end;
440
441
442 while arp_standard.get_next_word( segments, segment )
443 -- segment is the next word in the list
444 loop
445 if str is not null
446 then
447 str := str || token_expand( separator, segment );
448 end if;
449 str := str || token_expand( word, segment );
450 end loop;
451
452
453 IF l_debug_level > 0 THEN
454 oe_debug_pub.add( '<< EXPAND: ' || STR ) ;
455 END IF;
456 structure := str;
457
458 EXCEPTION
459 When others then
460 IF l_debug_level > 0 THEN
461 oe_debug_pub.add( 'WHEN OTHERS EXPAND' ) ;
462 END IF;
463
464 end expand;
465
466
467 function active_segments return number is
468 segments number;
469 --
470 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
471 --
472 begin
473
474 segments := flex_end - flex_start + 1;
475 return( segments );
476
477 END active_segments;
478
479
480 PROCEDURE initialize IS
481
482 cursor c_sel is
483 select location_structure_id
484 from ar_system_parameters;
485
486 l_location_structure_id ar_system_parameters.location_structure_id%TYPE;
487
488 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
489
490 --
491 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
492 --
493 begin -- Initialisation Section
494
495 flex_segments := 0; -- Count of number of active segment records.
496
497 IF oe_code_control.code_release_level < '110510' THEN
498 OPEN c_sel;
499 FETCH c_sel
500 INTO l_location_structure_id;
501 CLOSE c_sel;
502 ELSE
503 l_AR_Sys_Param_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params;
504 l_location_structure_id := l_AR_Sys_Param_Rec.location_structure_id;
505 END IF;
506
507 IF l_debug_level > 0 THEN
508 oe_debug_pub.add( 'STRUCUTRE_ID='||TO_CHAR ( L_LOCATION_STRUCTURE_ID ) ) ;
509 END IF;
510 setup_flexfield(
511 'RLOC',
512 l_location_structure_id
513 );
514
515
516 if active_segments = 0
517 then
518 arp_standard.fnd_message( 'AR_FLEX_MANDATORY_STRUCTURE', 'FNAME', 'Sales Tax Location');
519 end if;
520
521 end initialize;
522
523 end oe_inline_flex;
524