[Home] [Help]
PACKAGE BODY: APPS.ARP_FLEX
Source
1 package body ARP_FLEX as
2 /* $Header: ARPLFLXB.pls 120.4 2005/09/02 02:28:10 sachandr 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 tab_id_type;
16 flex_end tab_id_type;
17 flex_id tab_id_type;
18 flex_short_name tab_varchar2_type;
19 flex_separator tab_char_type;
20 list_segs tab_id_type;
21
22
23 flex_segments number := 0 ;
24 flex_instance number := 0 ;
25
26 no_qualifier EXCEPTION;
27
28 /*---------------------------------------------------------------------------+
29 | PUBLIC HANDLES |
30 | These handles are automatically initialised during package startup |
31 | |
32 +---------------------------------------------------------------------------*/
33
34 gl_handle number; -- General Ledger Accounts Structure for current SOB
35 location_handle number; -- Sales Tax Location Flexfield for current SOB
36
37
38 function setup_flexfield( application_id in number,
39 flex_code in varchar2,
40 structure_id in number ) return number is
41
42 cursor sel_flex_structure(appl_id in number,
43 flex_code in varchar2,
44 structure_id in number) is
45 select struct.concatenated_segment_delimiter separator,
46 vs.flex_value_set_name name,
47 seg.segment_num num,
48 seg.application_column_name column_name,
49 qual.segment_attribute_type qualifier,
50 seg.form_left_prompt prompt,
51 rownum pos,
52 'LOCATION_ID_SEGMENT_'||to_char(seg.segment_num) column_num
53 from fnd_flex_value_sets vs,
54 fnd_id_flex_segments_vl seg,
55 fnd_segment_attribute_values qual,
56 fnd_id_flex_structures struct
57 where seg.application_id = appl_id
58 and seg.id_flex_code = flex_code
59 and seg.flex_value_set_id = vs.flex_value_set_id
60 and seg.id_flex_num = structure_id
61 and qual.id_flex_code(+) = flex_code
62 and qual.id_flex_num(+) = structure_id
63 and qual.application_id(+) = appl_id
64 and qual.application_column_name(+) = seg.application_column_name
65 and qual.attribute_value(+) = 'Y'
66 and seg.enabled_flag = 'Y'
67 and struct.id_flex_code = seg.id_flex_code
68 and struct.id_flex_num = seg.id_flex_num
69 order by seg.segment_num, qual.segment_attribute_type;
70
71 segment number;
72 prior_segment number;
73
74 -- Define Debug Variable and Assign the Profile
75 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
76 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
77
78 begin
79
80 flex_instance := flex_instance + 1;
81 flex_start( flex_instance ) := flex_segments + 1;
82 segment := 0;
83 prior_segment := 0;
84
85 if PG_DEBUG = 'Y' then
86 arp_util_tax.debug('application_id:'||to_char(application_id));
87 arp_util_tax.debug('flex_code:'||flex_code);
88 arp_util_tax.debug('structure_id:'||to_char(structure_id));
89 end if;
90
91 for location in sel_flex_structure( application_id, flex_code, structure_id)
92 loop
93
94 /*------------------------------------------------------------------+
95 | The Flex Preprocessor assumes that the Line Number Matches the |
96 | Column Number in Key Flexfield Definition |
97 | Eg. Line# Column# Column Name |
98 | 1 1 LOCATION_SEGMENT_ID_1 |
99 | 2 2 LOCATION_SEGMENT_ID_2 |
100 | So we need to check that this assumption is true |
101 +------------------------------------------------------------------*/
102 if PG_DEBUG = 'Y' then
103 arp_util_tax.debug('location.name : '||location.name);
104 arp_util_tax.debug('location.num : '||location.num);
105 arp_util_tax.debug('location.column_name : '||location.column_name);
106 arp_util_tax.debug('location.qualifier : '||location.qualifier);
107 arp_util_tax.debug('location.prompt : '||location.prompt);
108 arp_util_tax.debug('location.pos : '||location.pos);
109 arp_util_tax.debug('location.column_num :'||location.column_num);
110 end if;
111
112 if location.column_name like 'LOCATION_ID_SEGMENT_%' and
113 location.column_name <> location.column_num then
114 if PG_DEBUG = 'Y' then
115 arp_util_tax.debug('The Line Number: '||to_char(location.num)
116 ||' and Column Segment Number for '||location.column_name
117 ||' must be equal.');
118 end if;
119 fnd_message.set_name('AR','GENERIC_MESSAGE');
120 fnd_message.set_token('GENERIC_TEXT','The Line Number '
121 ||to_char(location.num)
122 ||' and Column Segment Number for '||location.column_name
123 ||' must be equal.');
124 app_exception.raise_exception;
125 end if;
126
127 if ( location.num <> prior_segment )
128 then
129
130 flex_segments := flex_segments + 1;
131 flex_num( flex_segments ) := location.num;
132 flex_name( flex_segments ) := location.name;
133 flex_column_name( flex_segments ) := location.column_name;
134 flex_pos( flex_segments ) := location.pos;
135 flex_qualifier( flex_segments ) := ' ' || location.qualifier || ' ' ;
136 flex_prompt( flex_segments ) := location.prompt;
137
138 flex_separator( flex_instance ) := location.separator;
139
140 prior_segment := location.num;
141
142 else
143
144 /*------------------------------------------------------------------+
145 | Multiple Qualifiers per segment are supported, each qualifier |
146 | will be space separated. |
147 | Example: GL_ACCOUNT GL_GLOBAL |
148 +------------------------------------------------------------------*/
149
150 flex_qualifier( flex_segments ) := flex_qualifier( flex_segments ) ||
151 location.qualifier || ' ' ;
152
153 end if;
154
155 end loop;
156 flex_end( flex_instance ) := flex_segments ;
157 flex_short_name( flex_instance ) := flex_code;
158 flex_id( flex_instance ) := structure_id;
159 return( flex_instance );
160 end;
161
162
163 function token_expand( flex_handle in number,
164 word in varchar2,
165 i in number ) return varchar2 is
166 str varchar2(1000);
167
168 begin
169
170 /*----------------------------------------------------------------------+
171 | Expand segment tokens if requested |
172 *----------------------------------------------------------------------*/
173
174 str := replace( word, '%COUNTER%', i );
175 str := replace( str, '%COLUMN%', flex_column_name(i) );
176 str := replace( str, '%POSITION%', flex_pos(i));
177 str := replace( str, '%QUALIFIER%', ltrim(rtrim(flex_qualifier(i))));
178 str := replace( str, '%NUMBER%', flex_num(i));
179 str := replace( str, '%SEPARATOR%', flex_separator(flex_handle));
180 str := replace( str, '%PROMPT%', flex_prompt(i));
181
182 /*----------------------------------------------------------------------+
183 | Expand Next segment tokens if requested |
184 | Next is defined to be the next physical segment in the structure |
185 *----------------------------------------------------------------------*/
186
187 if i < flex_end( flex_handle )
188 then
189 str := replace( str, '%NEXT_COUNTER%', i+1 - flex_start(flex_handle) );
190 str := replace( str, '%NEXT_COLUMN%', flex_column_name(i+1) );
191 str := replace( str, '%NEXT_POSITION%', flex_pos(i+1));
192 str := replace( str, '%NEXT_QUALIFIER%', ltrim(rtrim(flex_qualifier(i+1))));
193 str := replace( str, '%NEXT_NUMBER%', flex_num(i+1));
194 str := replace( str, '%NEXT_PROMPT%', flex_prompt(i+1));
195 else
196 str := replace( str, '%NEXT_COUNTER%', 0 );
197 str := replace( str, '%NEXT_COLUMN%', '''''' );
198 str := replace( str, '%NEXT_POSITION%', 0 );
199 str := replace( str, '%NEXT_QUALIFIER%', '''''' );
200 str := replace( str, '%NEXT_NUMBER%', 0 );
201 str := replace( str, '%NEXT_PROMPT%', '''''' );
202 end if;
203
204 /*----------------------------------------------------------------------+
205 | Expand previous segment tokens if requested |
206 *----------------------------------------------------------------------*/
207
208 if i > flex_start( flex_handle )
209 then
210 str := replace( str, '%PREVIOUS_COUNTER%', i-1 - flex_start( flex_handle ) );
211 str := replace( str, '%PREVIOUS_COLUMN%', flex_column_name(i-1) );
212 str := replace( str, '%PREVIOUS_POSITION%', flex_pos(i-1));
213 str := replace( str, '%PREVIOUS_QUALIFIER%', ltrim(rtrim(flex_qualifier(i-1))));
214 str := replace( str, '%PREVIOUS_NUMBER%', flex_num(i-1));
215 str := replace( str, '%PREVIOUS_PROMPT%', ltrim(rtrim(flex_prompt(i-1))));
216 end if;
217
218
219 /* Previous number of element i is 0 */
220 if i = flex_start( flex_handle )
221 then
222 str := replace( str, '%PREVIOUS_NUMBER%', 0);
223 end if;
224
225 return( str );
226
227 end;
228
229 function delimit( word in varchar2 ) return varchar2 is
230 begin
231 return( ' ' || word || ' ' );
232 end;
233
234 procedure add_to_list( list in out nocopy varchar2, value in varchar2 ) is
235
236 -- Define Debug Variable and Assign the Profile
237 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
238 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
239
240 begin
241 if PG_DEBUG = 'Y' then
242 arp_util_tax.debug( '>> add_to_list( ' || list || ', ' || value || ' )' );
243 end if;
244
245 /*** MB skip, the instr only searches for a character string(value) ***/
246 /*** in list ***/
247 if instrb( list, value ) <> 0
248 then
249 list := replace( list, value , null );
250 else
251 /* No Changes - so add to the end of the list */
252 list := list || value;
253 end if;
254
255 if PG_DEBUG = 'Y' then
256 arp_util_tax.debug( '<< add_to_list( ' || list || ', ' || value || ' )' );
257 end if;
258 end add_to_list;
259
260
261
262 function qualifier_list( flex_handle in number, qualifiers in varchar2 ) return varchar2 is
263 i number;
264 k number;
265 step number;
266 segment number;
267 quals varchar2( 240 );
268 qual varchar2( 30 );
269 list varchar2( 1000 );
270 qual_found boolean;
271 list_start number;
272 startpos number;
273 endpos number;
274
275 -- Define Debug Variable and Assign the Profile
276 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
277 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
278
279 begin
280
281 if PG_DEBUG = 'Y' then
282 arp_util_tax.debug( '>> qualifier_list( ' || flex_handle || ', ' || qualifiers || ')' );
283 end if;
284
285 quals := qualifiers;
286 list := null;
287 list_start := flex_start( flex_handle );
288
289
290 while arp_standard.get_next_word( quals, qual )
291 loop
292
293 qual_found := qual in ( 'ALL', 'FIRST', 'LAST', 'ALLREV' );
294
295
296 if qual = 'ALLREV'
297 then
298 /***************************************/
299 /* If ALL in Reverse order is required */
300 /***************************************/
301 startpos := flex_end( flex_handle ) - list_start;
302 endpos := 0;
303 step:=-1;
304 else
305 /*********************************************************/
306 /* Normally, walk forward through the flexfield segments */
307 /*********************************************************/
308 startpos := 0;
309 endpos := flex_end( flex_handle ) - list_start;
310 step:=1;
311 end if;
312
313 i := startpos;
314 for k in 0 .. flex_end(flex_handle) - list_start
315 loop
316
317 segment := list_start + i;
318
319 if PG_DEBUG = 'Y' then
320 arp_util_tax.debug( 'i=' || i || ' ' || list || ' --- ' || segment );
321 end if;
322
323 if ( qual in ( 'ALL', 'ALLREV' ))
324 then
325 if list is not null
326 then
327 list := list || delimit( segment );
328 else
329 list := delimit( segment ) ;
330 end if;
331 elsif ( qual = 'FIRST' and i = 0 )
332 or ( qual = 'LAST' and ( i = flex_end( flex_handle ) - list_start ))
333 or ( qual = ltrim( to_char( i+1, '99999' ) ))
334 then
335 add_to_list( list, delimit(segment) ) ;
336 qual_found := true ;
337 /*** MB skip, the instr only searches for a character string ***/
338 /*** in flex_qualifier( segment ) ***/
339 elsif instrb( flex_qualifier( segment ), delimit( qual ) ) <> 0
340 then
341 add_to_list( list, delimit(segment) );
342 qual_found := true;
343 end if;
344
345 i:=i+step;
346 end loop;
347
348 if not qual_found
349 then
350
351 if PG_DEBUG = 'Y' then
352 arp_util_tax.debug(
353 arp_standard.fnd_message( 'AR_FLEX_NO_QUALIFIER', 'QUALIFIER', qual,
354 'STRING', qualifiers,
355 'FLEXCODE', flex_short_name( flex_handle ),
356 'STRUCTURE', flex_id( flex_handle ) ));
357 end if;
358
359 raise no_qualifier;
360
361 end if;
362
363 end loop;
364
365 if PG_DEBUG = 'Y' then
366 arp_util_tax.debug( '<< qualifier_list: ' || list );
367 end if;
368 return( list );
369
370 end;
371
372
373 function expand( flex_handle in number,
374 qualifiers in varchar2,
375 separator in varchar2,
376 word in varchar2 ) return varchar2 is
377
378 str varchar2(20000);
379 segments varchar2(1000);
380 segment number;
381
382 -- Define Debug Variable and Assign the Profile
383 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
384 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
385
386 begin
387
388 if PG_DEBUG = 'Y' then
389 arp_util_tax.debug( '>> expand( ' || flex_handle || ', ' ||
390 qualifiers || ', ' || separator || ', ' ||
391 word || ' )' );
392 end if;
396
393 /*-------------------------------------------------------------------------+
394 | Confirm that the flex handle passed is valid |
395 +-------------------------------------------------------------------------*/
397 BEGIN
398 segment := flex_start(flex_handle );
399 EXCEPTION
400 WHEN NO_DATA_FOUND
401 THEN arp_standard.fnd_message( 'AR_FLEX_BAD_HANDLE', 'HANDLE', to_char(flex_handle));
402 END;
403
404 /*-------------------------------------------------------------------------+
405 | Generate a list of segments, index values based on the qualifier |
406 | description string passed into the function. |
407 +-------------------------------------------------------------------------*/
408
409 BEGIN
410 segments := qualifier_list( flex_handle, qualifiers );
411 EXCEPTION
412 WHEN no_qualifier
413 THEN NULL;
414 END;
415
416 str := null;
417
418 /*-------------------------------------------------------------------------+
419 | Loop over each selected segment, expanding any tokens as requested |
420 +-------------------------------------------------------------------------*/
421
422
423 while arp_standard.get_next_word( segments, segment )
424 loop
425 if str is not null
426 then
427 str := str || token_expand( flex_handle, separator, segment );
428 end if;
429 str := str || token_expand( flex_handle, word, segment );
430 end loop;
431
432 if PG_DEBUG = 'Y' then
433 arp_util_tax.debug( '<< expand: ' || str );
434 end if;
435 return( str );
436
437 end expand;
438
439 function expand( flex_handle in number,
440 separator in varchar2,
441 word in varchar2 ) return varchar2 is
442 begin
443 return( expand( flex_handle, 'ALL', separator, word ));
444 end expand;
445
446
447 function location return number is
448 begin
449 return( location_handle );
450 end;
451
452 function gl return number is
453 begin
454 return( gl_handle );
455 end;
456
457
458 function active_segments( flex_handle in number ) return number is
459 segments number;
460 -- Define Debug Variable and Assign the Profile
461 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
462 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
463 begin
464
465 if PG_DEBUG = 'Y' then
466 arp_util_tax.debug( '>> active_segments( ' || flex_handle || ')' );
467 end if;
468
469 /*-------------------------------------------------------------------------+
470 | Confirm that the flex handle passed is valid |
471 +-------------------------------------------------------------------------*/
472
473 BEGIN
474 segments := flex_end(flex_handle) - flex_start(flex_handle )+1;
475 EXCEPTION
476 WHEN NO_DATA_FOUND
477 THEN arp_standard.fnd_message( 'AR_FLEX_BAD_HANDLE', 'HANDLE', to_char(flex_handle) );
478 END;
479
480 if PG_DEBUG = 'Y' then
481 arp_util_tax.debug( '<< active_segments: ' || to_char(segments) );
482 end if;
483 return( segments );
484
485 END;
486
487
488 begin -- Initialisation Section
489
490 flex_instance := 0; -- Count of number of open flexfield structures.
491 flex_segments := 0; -- Count of number of active segment records.
492
493 /*------------------------------------------------------------------------+
494 | Setup standard Flexfield handles. |
495 | |
496 | These handles are expected to be used frequently and so are publically |
497 | declared for everyone to use. |
498 | |
499 +------------------------------------------------------------------------*/
500
501 IF arp_standard.sysparm.location_structure_id <> -99 THEN
502 location_handle := setup_flexfield( arp_standard.application_id, 'RLOC',
503 arp_standard.sysparm.location_structure_id );
504 END IF;
505
506 gl_handle := setup_flexfield( arp_standard.gl_application_id, 'GL#',
507 arp_standard.gl_chart_of_accounts_id );
508
509 ----- Commented out for eBTax uptake
510 /*
511 if active_segments( location_handle ) = 0
512 then
513 arp_standard.fnd_message( 'AR_FLEX_MANDATORY_STRUCTURE', 'FNAME', 'Sales Tax Location');
514 end if;
515 */
516
517 end;