[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_KEY_FLEX
Source
1 PACKAGE BODY bis_vg_key_flex AS
2 /* $Header: BISTKFXB.pls 120.2 2005/11/16 10:39:27 dbowles ship $ */
3
4 --- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 --- All rights reserved.
6 ---
7 --- FILENAME
8 ---
9 --- BISTKFXB.pls
10 ---
11 --- DESCRIPTION
12 ---
13 --- body of package which handles key flexfield tags
14 ---
15 --- NOTES
16 ---
17 --- HISTORY
18 ---
19 --- 29-JUL-98 Created
20 --- 21-Apr-99 Edited by WNASRALL@US to correct parsing behavior
21 --- 10-NOV-00 Edited by WNASRALL@US to add new function generate_pruned_view
22 --- 22-JAN-01 Edited by WNASRALL@US to fix problem with generate_pruned_view
23 --- 06-APR-01 Edited by dbowles. Modified add_key_flexfield_segments,
24 --- update_Key_Flex_Tables and add_Key_Flex_Info procedures
25 --- adding new parameter x_Column_Comment_Table. This PL/SQL
26 --- table is used to hold flex information for flex derived
27 --- columns.
28 --- 01-Jun-01 Edited by ILI fix bug1802137
29 --- 19-JUL-01 Edited by Walid.Nasrallah : surrounded previous fix by an
30 --- IF statement to limit its effect to EDW views only.
31 --- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
32 --- 19-MAY-03 Modified update_Key_Flex_Tables not name a column over 30 bytes
33 --- while still preserving the structure number.
34 ---
35 ---
36 -- ============================================================================
37
38
39 -- =====================
40 -- GLOBAL CONSTANTS
41 -- =====================
42
43 G_PKG_NAME CONSTANT VARCHAR(30) := 'bis_vg_key_flex';
44
45
46 -- =====================
47 -- PRIVATE PROCEDURES
48 -- =====================
49 --
50 -- PROCEDURE : parse_KF_Column_Line
51 -- PARAMETERS: 1. p_View_Column_Table table of varchars to hold columns of
52 -- view text
53 -- 2. p_Column_Pointer pointer to the key flex column in
54 -- column table (IN)
55 -- 3. x_Column_Pointer pointer to the char after the delimiter
56 -- in column table (OUT)
57 -- 4. x_Concat_Seg_Name concatenated segment name
58 -- 5. l_concat_segment_flag flag to indicate if only concatenated
59 -- segments reqd.
60 -- 6. x_return_status error or normal
61 -- 7. x_error_Tbl table of error messages
62 -- COMMENT : Call this procedure to parse the KF view column tag.
63 -- --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
64 --- FND_API.G_EXC_ERROR;
65 -- ============================================================================
66 PROCEDURE parse_KF_Column_Line
67 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
68 , p_Column_Pointer IN bis_vg_types.View_Character_Pointer_Type
69 , x_Column_Pointer OUT bis_vg_types.View_Character_Pointer_Type
70 , x_Concat_Seg_Name OUT VARCHAR2
71 , x_concat_segment_flag OUT BOOLEAN
72 , x_decode_on_segments OUT BOOLEAN
73 , x_EDW_flag OUT BOOLEAN
74 , x_prefix OUT VARCHAR2
75 , x_return_status OUT VARCHAR2
76 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
77 )
78 IS
79 --
80 l_token VARCHAR2(100);
81 l_string bis_vg_types.view_text_table_rec_type;
82 l_pos NUMBER;
83 l_message_token VARCHAR2(2000);
84 --
85 BEGIN
86 bis_debug_pub.Add('> parse_KF_Column_Line');
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88 -- get row of text from table
89 l_string := bis_vg_util.get_row ( p_View_Column_Table
90 , p_Column_Pointer
91 , x_return_status
92 , x_error_Tbl
93 );
94
95 l_message_token := l_string;
96 -- get "_KF"
97 l_token := bis_vg_parser.get_string_token
98 ( l_string
99 , 1
100 , ':'
101 , l_pos
102 , x_return_status
103 , x_error_Tbl
104 );
105 bis_debug_pub.Add('l_token = ' || l_token);
106
107 -- get concatenated segment
108 x_Concat_Seg_Name := bis_vg_parser.get_string_token
109 ( l_string
110 , l_pos
111 , ':'
112 , l_pos
113 , x_return_status
114 , x_error_Tbl
115 );
116 bis_debug_pub.Add('x_Concat_Seg_Name = ' || x_Concat_Seg_Name);
117 --
118 IF (x_concat_seg_name IS NULL) THEN
119 BIS_VG_UTIL.Add_Error_message
120 ( p_error_msg_name => BIS_VG_KEY_FLEX.KFX_COL_TAG_EXP_NO_SEG_MSG
121 , p_error_proc_name => G_PKG_NAME||'.parse_KF_Column_Line'
122 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
123 , p_token1 => 'tag'
124 , p_value1 => l_string
125 , p_error_table => x_error_tbl
126 , x_error_table => x_error_tbl
127 );
128 bis_vg_log.update_failure_log( x_error_tbl
129 , x_return_status
130 , x_error_Tbl
131 );
132 RAISE FND_API.G_EXC_ERROR;
133 END IF;
134 --
135 IF(l_pos IS NOT NULL) THEN
136 -- get next token, if any
137 l_token := bis_vg_parser.get_string_token
138 ( l_string
139 , l_pos
140 , ':'
141 , l_pos
142 , x_return_status
143 , x_error_Tbl
144 );
145 bis_debug_pub.Add('l_token = ' || l_token);
146
147 IF( UPPER(l_token) = '_CO' ) THEN
148 x_concat_segment_flag := TRUE;
149 bis_debug_pub.Add('x_concat_segment_flag = TRUE');
150 ELSIF ( UPPER(l_token) = '_BS') THEN
151 x_decode_on_segments := TRUE;
152 bis_debug_pub.Add('x_decode_on_segements = TRUE');
153 ELSIF ( UPPER(l_token) = '_EDW') THEN --EDW flag change
154 x_EDW_flag:=true;
155 bis_debug_pub.Add('x_EDW_flag = TRUE'); --EDW flag change
156 ELSE
157 IF(SUBSTR(l_token, 1, 1) = '_') THEN
158 --
159 BIS_VG_UTIL.Add_Error_message
160 ( p_error_msg_name => BIS_VG_KEY_FLEX.KFX_COL_TAG_PREF_CO_MSG
161 , p_error_proc_name => G_PKG_NAME||'.parse_KF_Column_Line'
162 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
163 , p_token1 => 'tag'
164 , p_value1 => l_message_token
165 , p_token2 => 'flag'
166 , p_value2 => l_token
167 , p_error_table => x_error_tbl
168 , x_error_table => x_error_tbl
169 );
170 bis_vg_log.update_failure_log( x_error_tbl
171 , x_return_status
172 , x_error_Tbl
173 );
174 RAISE FND_API.G_EXC_ERROR;
175
176 END IF;
177
178 --
179 x_prefix := l_token;
180 bis_debug_pub.Add('x_prefix = ' || x_prefix);
181 IF(l_pos IS NOT NULL) THEN
182 l_token := bis_vg_parser.get_string_token
183 ( l_string
184 , l_pos
185 , ':'
186 , l_pos
187 , x_return_status
188 , x_error_Tbl
189 );
190 bis_debug_pub.Add('l_token = ' || l_token);
191
192 IF( UPPER(l_token) = '_CO' ) THEN
193 x_concat_segment_flag := TRUE;
194 bis_debug_pub.Add('x_concat_segment_flag = TRUE');
195 ELSIF ( UPPER(l_token) = '_BS') THEN
196 x_decode_on_segments := TRUE;
197 bis_debug_pub.Add('x_decode_on_segements = TRUE');
198 ELSIF ( UPPER(l_token) ='_EDW') THEN --EDW flag change
199 x_EDW_flag := TRUE;
200 bis_debug_pub.Add('x_EDW_flag = TRUE'); --EDW flag change
201 ELSE
202 --
203 BIS_VG_UTIL.Add_Error_message
204 ( p_error_msg_name => BIS_VG_KEY_FLEX.KFX_COL_TAG_EXP_BAD_FLAG_MSG
205 , p_error_proc_name => G_PKG_NAME||'.parse_KF_Column_Line'
206 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
207 , p_token1 => 'tag'
208 , p_value1 => l_message_token
209 , p_token2 => 'flag'
210 , p_value2 => l_token
211 , p_error_table => x_error_tbl
212 , x_error_table => x_error_tbl
213 );
214 bis_vg_log.update_failure_log( x_error_tbl
215 , x_return_status
216 , x_error_Tbl
217 );
218 RAISE FND_API.G_EXC_ERROR;
219 --
220 END IF;
221 END IF;
222 END IF;
223 END IF;
224 x_Column_Pointer := bis_vg_util.increment_pointer_by_row
225 ( p_View_Column_Table
226 , p_Column_Pointer
227 , x_return_status
228 , x_error_Tbl
229 );
230 bis_debug_pub.Add('< parse_KF_Column_Line');
231 --
232 EXCEPTION
233 when FND_API.G_EXC_ERROR then
234 x_return_status := FND_API.G_RET_STS_ERROR ;
235 RAISE FND_API.G_EXC_ERROR;
236 when FND_API.G_EXC_UNEXPECTED_ERROR then
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
239 when others then
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
241 BIS_VG_UTIL.Add_Error_Message
242 ( p_error_msg_id => SQLCODE
243 , p_error_description => SQLERRM
244 , p_error_proc_name => G_PKG_NAME||'.parse_KF_Column_Line'
245 , p_error_table => x_error_tbl
246 , x_error_table => x_error_tbl
247 );
248 bis_vg_log.update_failure_log( x_error_tbl
249 , x_return_status
250 , x_error_Tbl
251 );
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253
254 END parse_KF_Column_Line;
255 --
256 -- ============================================================================
257 -- FUNCTION: CHECK_APPLICATION_VALIDITY (PRIVATE FUNCTION)
258 -- RETURNS: boolean - true if application short name p[assed is defined
259 -- 1. p_app short name of application
260 --
261 -- COMMENT : Checks against the FND_APPLICATION_ALL_VIEW.
262 -- Called from parse_DF_Select_Line.
263 -- --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
264 --- FND_API.G_EXC_ERROR;
265 -- ==========================================================================
266 FUNCTION CHECK_APPLICATION_VALIDITY
267 ( p_app IN VARCHAR2
268 )
269 return boolean
270 is
271 l_return_value boolean ;
272 l_dummy number;
273 cursor l_cursor is
274 select 1
275 from fnd_application_all_view
276 where application_short_name = p_app;
277 begin
278 BIS_DEBUG_PUB.Add('> check_application_validity');
279 open l_cursor ;
280 fetch l_cursor into l_dummy ;
281 l_return_value := l_cursor%found ;
282 close l_cursor ;
283 BIS_DEBUG_PUB.Add('< check_application_validity');
284 return(l_return_value);
285
286 END CHECK_APPLICATION_VALIDITY;
287
288
289 -- =============================================================================
290 -- PROCEDURE : parse_KF_Select_Line
291 -- PARAMETERS: 1. p_View_Select_Table table of varchars to hold select clause
292 -- of view text
293 -- 2. p_Select_Pointer pointer to the key flex column in select
294 -- table (IN)
295 -- 3. x_Select_Pointer pointer to the char after the delimiter in
296 -- select table (OUT)
297 -- 4. x_PLSQL_Expression PL/SQL expression
298 -- 5. x_Application_Name Application Name
299 -- 6. x_Key_Flex_Code Key Flexfield code
300 -- 7. x_Table_Alias Table alias
301 -- 8. x_Structure_Column Structure Column Name
302 -- 9. x_return_status error or normal
303 -- 10. x_error_Tbl table of error messages
304 -- COMMENT : Call this procedure to parse the KF selected tag.
305 -- --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
306 --- FND_API.G_EXC_ERROR;
307 -- =============================================================================
308 PROCEDURE parse_KF_Select_Line
309 ( p_View_Select_Table IN bis_vg_types.View_Text_Table_Type
310 , p_Select_Pointer IN bis_vg_types.View_Character_Pointer_Type
311 , x_Select_Pointer OUT bis_vg_types.View_Character_Pointer_Type
312 , x_PLSQL_Expression OUT VARCHAR2
313 , x_Application_Name OUT VARCHAR2
314 , x_Key_Flex_Code OUT VARCHAR2
315 , x_Table_Alias OUT VARCHAR2
316 , x_Structure_Column OUT VARCHAR2
317 , x_DUMMY_flag OUT BOOLEAN --EDW flag change
318 , x_return_status OUT VARCHAR2
319 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
320 )
321 IS
322 --
323 l_token VARCHAR2(100);
324 l_message_token VARCHAR2(2000);
325 l_tmp_pointer bis_vg_types.View_Character_Pointer_Type;
326 --
327 BEGIN
328 bis_debug_pub.Add('> parse_KF_Select_Line');
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330 l_message_token := bis_vg_parser.get_expression( p_View_Select_Table
331 , p_Select_Pointer
332 , l_tmp_pointer
333 , x_return_status
334 , x_error_Tbl
335 );
336 l_tmp_pointer := bis_vg_util.increment_pointer
337 ( p_View_Select_Table
338 , l_tmp_pointer
339 , x_return_status
340 , x_error_Tbl
341 );
342
343 -- get '_KF'
344
345 l_token := bis_vg_parser.get_token_increment_pointer
346 ( p_View_Select_Table
347 , p_Select_Pointer
348 , ':'''
349 , x_Select_Pointer
350 , x_return_status
351 , x_error_Tbl
352 );
353 IF bis_vg_util.equal_pointers(
354 l_tmp_pointer
355 , x_select_pointer
356 , x_return_status
357 , x_error_Tbl
358 )
359 THEN
360 raise MALFORMED_KFX_SEL_TAG_NO_FIELD;
361 END IF;
362
363 l_token := bis_vg_parser.get_token_increment_pointer
364 ( p_View_Select_Table
365 , x_Select_Pointer
366 , ':'''
367 , x_Select_Pointer
368 , x_return_status
369 , x_error_Tbl
370 );
371
372 IF (l_token IS NULL
373 OR
374 bis_vg_util.equal_pointers(
375 l_tmp_pointer
376 , x_select_pointer
377 , x_return_status
378 , x_error_Tbl
379 )
380 ) THEN
381 raise MALFORMED_KFX_SEL_TAG_NO_FIELD;
382 END IF;
383 --
384
385 -- check for dummy tag
386 --EDW flag change
387 IF (l_token = '_DUMMY') THEN
388 x_DUMMY_flag := TRUE;
389 l_token := bis_vg_parser.get_token_increment_pointer
390 ( p_View_Select_Table
391 , x_Select_Pointer
392 , ':'
393 , x_Select_Pointer
394 , x_return_status
395 , x_error_Tbl
396 );
397
398 END IF;
399 --EDW flag change
400
401 -- check for SQL epression
402 IF (l_token = '_EX') THEN
403 x_PLSQL_Expression := bis_vg_parser.get_expression
404 ( p_View_Select_Table
405 , x_Select_Pointer
406 , x_Select_Pointer
407 , x_return_status
408 , x_error_Tbl
409 );
410 --
411 IF (x_plsql_expression IS NULL) THEN
412 raise MALFORMED_KFX_SEL_TAG_NO_FIELD;
413 END IF;
414 --
415 -- replace escaped single quotes with a single single quote
416 x_PLSQL_Expression := REPLACE(x_PLSQL_Expression, '''''', '''');
417 --
418 ELSE
419 x_Application_Name := l_token;
420 IF NOT check_application_validity(x_Application_Name)
421 THEN
422 BIS_VG_UTIL.Add_Error_message
423 ( p_error_msg_name => BIS_VG_KEY_FLEX.KFX_SEL_TAG_EXP_INVALID_APP
424 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
425 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
426 , p_token1 => 'tag'
427 , p_value1 => l_message_token
428 , p_token2 => 'app'
429 , p_value2 => x_Application_Name
430 , p_error_table => x_error_tbl
431 , x_error_table => x_error_tbl
432 );
433 bis_vg_log.update_failure_log( x_error_tbl
434 , x_return_status
435 , x_error_Tbl
436 );
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439 x_Key_Flex_Code := bis_vg_parser.get_token_increment_pointer
440 ( p_View_Select_Table
441 , x_Select_Pointer
442 , ':'''
443 , x_Select_Pointer
444 , x_return_status
445 , x_error_Tbl
446 );
447 IF(
448 x_Key_Flex_Code IS NULL
449 OR
450 bis_vg_util.equal_pointers(
451 l_tmp_pointer
452 , x_select_pointer
453 , x_return_status
454 , x_error_Tbl
455 )
456 ) THEN
457 --
458 raise MALFORMED_KFX_SEL_TAG_NO_FIELD;
459 END IF;
460
461 x_Table_Alias := bis_vg_parser.get_token
462 ( p_View_Select_Table
463 , x_Select_Pointer
464 , ':'''
465 , x_Select_Pointer
466 , x_return_status
467 , x_error_Tbl
468 );
469
470 IF (x_Table_Alias IS NULL) THEN
471 raise MALFORMED_KFX_SEL_TAG_NO_FIELD;
472 END IF;
473 --
474 IF(bis_vg_util.get_char( p_View_Select_Table
475 , x_Select_Pointer
476 , x_return_status
477 , x_error_Tbl
478 ) = ':') THEN
479 x_Select_Pointer := bis_vg_util.increment_pointer
480 ( p_View_Select_Table
481 , x_Select_Pointer
482 , x_return_status
483 , x_error_Tbl
484 );
485 x_Structure_Column := bis_vg_parser.get_token
486 ( p_View_Select_Table
487 , x_Select_Pointer
488 , ':'''
489 , x_Select_Pointer
490 , x_return_status
491 , x_error_Tbl
492 );
493 END IF;
494 END IF;
495 x_Select_Pointer := bis_vg_util.increment_pointer
496 ( p_View_Select_Table
497 , x_Select_Pointer
498 , x_return_status
499 , x_error_Tbl
500 );
501 bis_debug_pub.Add('< parse_KF_Select_Line');
502 --
503
504 EXCEPTION
505 when MALFORMED_KFX_SEL_TAG_NO_FIELD
506 THEN
507 x_return_status := FND_API.G_RET_STS_ERROR ;
508 BIS_VG_UTIL.Add_Error_message
509 ( p_error_msg_name => BIS_VG_KEY_FLEX.KFX_SEL_TAG_EXP_NO_FIELD_MSG
510 , p_error_proc_name => G_PKG_NAME||'.parse_KF_Select_Line'
511 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
512 , p_token1 => 'tag'
513 , p_value1 => l_message_token
514 , p_error_table => x_error_tbl
515 , x_error_table => x_error_tbl
516 );
517 bis_vg_log.update_failure_log( x_error_tbl
518 , x_return_status
519 , x_error_Tbl
520 );
521 RAISE FND_API.G_EXC_ERROR;
522
523 when FND_API.G_EXC_ERROR then
524 x_return_status := FND_API.G_RET_STS_ERROR ;
525 RAISE FND_API.G_EXC_ERROR;
526 when FND_API.G_EXC_UNEXPECTED_ERROR then
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529 when others then
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
531 BIS_VG_UTIL.Add_Error_Message
532 ( p_error_msg_id => SQLCODE
533 , p_error_description => SQLERRM
534 , p_error_proc_name => G_PKG_NAME||'.parse_KF_Select_Line'
535 , p_error_table => x_error_tbl
536 , x_error_table => x_error_tbl
537 );
538 bis_vg_log.update_failure_log( x_error_tbl
539 , x_return_status
540 , x_error_Tbl
541 );
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543
544
545 END parse_KF_Select_Line;
546 --
547 -- ============================================================================
548 -- PROCEDURE : add_key_flexfield_segments
549 -- PARAMETERS: 1. p_dummy_flag indicates that a NULL is to be inserted
550 -- in the select
551 -- 2. p_Structure_Num number of the structure ie., 1, 2, 3
552 -- 3. p_nStructures total number of structures present
553 -- 4. p_Flexfield flexfield
554 -- 5. p_Structure structure
555 -- 6. p_Concat_Seg_Name concatenated segment name
556 -- 7. p_Prefix prefix for segments
557 -- 8. p_Table_Alias Table alias
558 -- 9. x_Column_Table table of varchars to hold select clause
559 -- of view text
560 -- 10. x_Select_Table table of varchars to hold select clause
561 -- of view text
562 -- 11. x_Column_Comment_Table table of records that is used
563 -- to hold flex info for flex derived
564 -- columns.
565 -- 12. x_return_status error or normal
566 -- 13. x_error_Tbl table of error messages
567 -- COMMENT : Call this procedure to build the column and select tables for
568 -- key flexfields.
569 -- --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
570 --- FND_API.G_EXC_ERROR;
571 -- ============================================================================
572 PROCEDURE add_key_flexfield_segments
573 ( p_dummy_flag IN BOOLEAN
574 , p_Structure_Num IN NUMBER
575 , p_suffix IN VARCHAR2
576 , p_Flexfield IN FND_FLEX_KEY_API.FLEXFIELD_TYPE
577 , p_Structure IN FND_FLEX_KEY_API.STRUCTURE_TYPE
578 , p_Concat_Seg_Name IN VARCHAR2
579 , p_decode_on_segments IN BOOLEAN
580 , p_Prefix IN VARCHAR2
581 , p_Table_Alias IN VARCHAR2
582 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
583 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
584 , x_Column_Comment_Table OUT BIS_VG_TYPES.Flex_Column_Comment_Table_Type
585 , x_return_status OUT VARCHAR2
586 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
587 )
588 IS
589 --
590 l_Segment_List FND_FLEX_KEY_API.SEGMENT_LIST;
591 l_Segment FND_FLEX_KEY_API.SEGMENT_TYPE;
592 --
593 l_nSegments NUMBER;
594 l_Concat_Segment_Flag BOOLEAN; -- mirrors p_Concat_Segment_Flag
595 -- to circumvent possible PL/SQL bug
596 l_prefix VARCHAR2(100) := NULL;
597 l_prefix_Len NUMBER := 0;
598 l_Concat_Seg_Name VARCHAR2(100) := NULL;
599 l_Segment_Name NUMBER := 0;
600 --
601 BEGIN
602 bis_debug_pub.Add('> add_key_flexfield_segments');
603 x_return_status := FND_API.G_RET_STS_SUCCESS;
604 l_Concat_Segment_Flag := FALSE;
605 --
606 FND_FLEX_KEY_API.GET_SEGMENTS( flexfield => p_Flexfield
607 , structure => p_Structure
608 , enabled_only => TRUE
609 , nsegments => l_nSegments
610 , segments => l_Segment_List
611 );
612
613 IF( l_nSegments > 0 ) THEN
614 IF(p_prefix IS NOT NULL) THEN
615 l_prefix := p_prefix || '_';
616 END IF;
617 bis_debug_pub.Add('p_Concat_Seg_Name = ' || p_Concat_Seg_Name);
618 --
619
620 x_Select_Table(1) := ' ';
621 FOR i IN 1 .. l_nSegments LOOP
622 l_Segment := FND_FLEX_KEY_API.FIND_SEGMENT
623 ( flexfield => p_Flexfield
624 , structure => p_Structure
625 , segment_name => l_Segment_List(i)
626 );
627 --
628 x_Column_Table(i) := l_Prefix || l_Segment.segment_name
629 || p_Suffix;
630
631 --- Remove the following two lines. They overwrite the correct
632 --- x_Column_Table value at i-th slot.
633 ---- This causes the key flexfield column name to not be appended
634 --- with the structure number.
635 --- ili, 06/01/01.
636 ---
637
638 --- x_Column_Table(i) := l_Prefix || l_Segment.segment_name
639 --- || l_Struct_Num;
640
641 x_Column_Comment_Table(i).column_name := l_Prefix
642 || l_Segment.segment_name
643 || p_suffix;
644 x_Column_Comment_Table(i).flex_type := 'KEY';
645
646 --- Populate the column_comments column with application_id, flex_code,
647 --- stucture_code,segment_name, application_column_name
648 x_Column_Comment_Table(i).column_comments
649 := p_Flexfield.table_application_id||','||
650 p_Flexfield.flex_code||','||
651 p_structure.structure_number||','||
652 l_Segment.segment_name||','||l_Segment.column_name;
653
654
655 IF p_dummy_flag
656 THEN
657 x_Select_Table(i) := ', NULL' ;
658
659 ELSIF p_decode_on_segments
660 THEN
661 x_Select_Table(i) := ', '
662 || 'DECODE( ' || p_Table_Alias
663 || '.'
664 || p_flexfield.structure_column
665 || ', '
666 || p_structure.structure_number
667 || ','
668 || p_Table_Alias || '.' ||l_Segment.column_name
669 || ', NULL' || ')';
670
671
672 ELSE
673 x_Select_Table(i) := ', ' || p_Table_Alias
674 || '.' || l_Segment.column_name;
675 END IF;
676
677 END LOOP;
678 END IF;
679
680 bis_debug_pub.Add('< add_key_flexfield_segments');
681
682
683 EXCEPTION
684 when FND_API.G_EXC_ERROR then
685 x_return_status := FND_API.G_RET_STS_ERROR ;
686 RAISE FND_API.G_EXC_ERROR;
687 when FND_API.G_EXC_UNEXPECTED_ERROR then
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
690 when others then
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 BIS_VG_UTIL.Add_Error_Message
693 ( p_error_msg_id => SQLCODE
694 , p_error_description => SQLERRM
695 , p_error_proc_name => G_PKG_NAME||'.add_key_flexfield_segments'
696 , p_error_table => x_error_tbl
697 , x_error_table => x_error_tbl
698 );
699 bis_vg_log.update_failure_log( x_error_tbl
700 , x_return_status
701 , x_error_Tbl
702 );
703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
704
705 END add_key_flexfield_segments;
706
707 -- =============================================================================
708 -- PROCEDURE : add_kfx_segments_concat
709 -- PARAMETERS: 1. p_nStructures total number of structures present
710 -- 2. p_Flexfield flexfield
711 -- 3. p_Structure structure
712 -- 4. p_Table_Alias Table alias
713 -- 5. x_Select_Table table of varchars to hold select clause
714 -- of view text
715 -- 6. x_return_status error or normal
716 -- 7. x_error_Tbl table of error messages
717 -- COMMENT : Call this procedure to build the concatenated segments for a structure
718 -- --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
719 --- FND_API.G_EXC_ERROR;
720 -- =============================================================================
721 PROCEDURE add_kfx_segments_concat
722 ( p_nStructures IN NUMBER
723 , p_Flexfield IN FND_FLEX_KEY_API.FLEXFIELD_TYPE
724 , p_Structure IN FND_FLEX_KEY_API.STRUCTURE_TYPE
725 , p_Table_Alias IN VARCHAR2
726 , p_pad_count IN NUMBER
727 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
728 , x_return_status OUT VARCHAR2
729 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
730 )
731 IS
732 --
733 l_Segment_List FND_FLEX_KEY_API.SEGMENT_LIST;
734 l_Segment FND_FLEX_KEY_API.SEGMENT_TYPE;
735 --
736 l_nSegments NUMBER;
737 l_prefix VARCHAR2(100) := NULL;
738 l_prefix_Len NUMBER := 0;
739 l_Concat_Seg_Name VARCHAR2(100) := NULL;
740 l_Segment_Name NUMBER := 0;
741 --
742 BEGIN
743 bis_debug_pub.Add('> add_kfx_segments_concat');
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745 FND_FLEX_KEY_API.GET_SEGMENTS( flexfield => p_Flexfield
746 , structure => p_Structure
747 , enabled_only => TRUE
748 , nsegments => l_nSegments
749 , segments => l_Segment_List
750 );
751 IF( l_nSegments > 0 ) THEN
752 --
753 FOR i IN 1 .. l_nSegments LOOP
754 l_Segment := FND_FLEX_KEY_API.FIND_SEGMENT
755 ( flexfield => p_Flexfield
756 , structure => p_Structure
757 , segment_name => l_Segment_List(i)
758 );
759 IF (i = 1) THEN
760 IF(p_nStructures > 1) THEN
761 x_Select_Table(x_select_table.COUNT +1 ) :=
762 lpad(' ',p_pad_count)|| p_Structure.structure_number || ', ';
763 END IF;
764 x_Select_Table(x_select_table.COUNT +1 ) := lpad(' ',p_pad_count) || p_Table_Alias || '.' || l_Segment.column_name;
765 ELSE
766 -- increment the table pointer for every 5 segments added
767 IF MOD(i,5)=0
768 THEN
769 x_select_table(x_select_table.COUNT+1) := Lpad(' ',p_pad_count+2);
770 END IF;
771
772 x_Select_Table(x_select_table.COUNT) :=
773 x_Select_Table(x_select_table.COUNT)
774 ||' || '''
775 || p_Structure.segment_separator
776 || ''' || '
777 || p_Table_Alias || '.'
778 || l_Segment.column_name;
779 END IF;
780 END LOOP;
781 END IF;
782 bis_debug_pub.Add('< add_kfx_segments_concat');
783 --
784
785
786 EXCEPTION
787 when FND_API.G_EXC_ERROR then
788 x_return_status := FND_API.G_RET_STS_ERROR ;
789 RAISE FND_API.G_EXC_ERROR;
790 when FND_API.G_EXC_UNEXPECTED_ERROR then
791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793 when others then
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
795 BIS_VG_UTIL.Add_Error_Message
796 ( p_error_msg_id => SQLCODE
797 , p_error_description => SQLERRM
798 , p_error_proc_name => G_PKG_NAME||'.add_kfx_segments_concat'
799 , p_error_table => x_error_tbl
800 , x_error_table => x_error_tbl
801 );
802 bis_vg_log.update_failure_log( x_error_tbl
803 , x_return_status
804 , x_error_Tbl
805 );
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807
808 END add_kfx_segments_concat;
809 --
810 -- ============================================================================
811 -- PROCEDURE : update_Key_Flex_Tables
812 -- PARAMETERS: 1. p_Concat_Seg_Name concatenated segment name
813 -- 2. p_Concat_Segment_Flag flag to indicate if only
814 -- concatenated segments desired
815 -- 3. p_decode_on_segments flag to indicate if select
816 -- statement should contain a decode
817 -- or always fetch data even when
818 -- meaningless
819 -- 4. p_EDW_Flag flag to add context column
820 -- 5. p_dummy_flag flag to indicate a flexfield
821 -- which is not valid in this branch
822 -- of a union, hence filled with
823 -- NULLs to keep number of columns.
824 -- 6. p_column_table PLSQL table of columns to prune by
825 -- if present, else expand all.
826 -- 7. p_Prefix prefix for segments
827 -- 8. p_PLSQL_Expression PL/SQL expression if any
828 -- 9. p_Application_Name Application Name
829 -- 10. p_Key_Flex_Code Key Flexfield code
830 -- 11. p_Table_Alias Table alias
831 -- 12. p_Structure_Column Name of structure column
832 -- 13. x_Column_Table table of varchars to hold
833 -- view columns
834 -- 14. x_Select_Table table of varchars to hold select
835 -- clause of view text
836 -- 15. X_Column_Comment_Table table to hold flex info for
837 -- flex derived columns
838 -- 16. x_return_status error or normal (not used)
839 -- 17. x_error_Tbl table of error messages
840 -- of view text
841 -- COMMENT : Call this procedure to build the column and select
842 -- tables for key flexfields.
843 -- --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
844 --- FND_API.G_EXC_ERROR;
845 -- ============================================================================
846 PROCEDURE update_Key_Flex_Tables
847 ( p_Concat_Seg_Name IN VARCHAR2
848 , p_Concat_Segment_Flag IN BOOLEAN
849 , p_decode_on_segments IN BOOLEAN
850 , p_EDW_Flag IN BOOLEAN
851 , p_dummy_flag IN BOOLEAN
852 , p_column_table IN BIS_VG_TYPES.flexfield_column_table_type
853 , p_Prefix IN VARCHAR2
854 , p_PLSQL_Expression IN VARCHAR2
855 , p_Application_Name IN VARCHAR2
856 , p_Key_Flex_Code IN VARCHAR2
857 , p_Table_Alias IN VARCHAR2
858 , p_Structure_Column IN VARCHAR2
859 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
860 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
861 , x_Column_Comment_Table OUT BIS_VG_TYPES.Flex_Column_Comment_Table_Type
862 , x_return_status OUT VARCHAR2
863 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
864 )
865 IS
866 --
867
868 --
869 l_Flexfield FND_FLEX_KEY_API.FLEXFIELD_TYPE;
870 l_Structure_List FND_FLEX_KEY_API.STRUCTURE_LIST;
871 l_Structure FND_FLEX_KEY_API.STRUCTURE_TYPE;
872 --
873 l_nStructures NUMBER;
874 l_count NUMBER;
875 l_Column_Table bis_vg_types.View_Text_Table_Type;
876 l_Select_Table bis_vg_types.View_Text_Table_Type;
877 l_Column_Comment_Table BIS_VG_TYPES.Flex_Column_Comment_Table_Type;
878 --
879 l_prefix VARCHAR2(100) := NULL;
880 l_suffix VARCHAR2(100) := NULL;
881 l_delimiter VARCHAR2(10) := ' ';
882 l_DUMMY_Flag BOOLEAN; ---EDW flag change
883 l_decode_Counter NUMBER := 1; --- change for bug 1752739
884 l_decode_max NUMBER := 127;
885 l_prefix_len NUMBER;
886 --
887
888 BEGIN
889 --
890 bis_debug_pub.debug_on;
891 bis_debug_pub.Add('> update_Key_Flex_Tables');
892 x_return_status := FND_API.G_RET_STS_SUCCESS;
893 bis_debug_pub.Add('p_Table_Alias = ' || p_Table_Alias);
894 IF(p_prefix IS NOT NULL) THEN
895 l_prefix := p_prefix || '_';
896 END IF;
897
898 --- What to do in case of PLSQL expression:
899 IF(p_PLSQL_Expression IS NOT NULL) THEN
900 IF (p_Concat_Segment_Flag ) THEN
901
902 --- -1- column table
903 x_Column_Table(1) := l_Prefix || p_Concat_Seg_Name;
904 --- -2- select table
905 IF p_dummy_flag THEN
906 bis_vg_util.create_Text_Table('NULL'
907 , x_Select_Table
908 , x_return_status
909 , x_error_tbl
910 );
911 ELSE
912
913 bis_vg_util.create_Text_Table(p_PLSQL_Expression
914 , x_Select_Table
915 , x_return_status
916 , x_error_Tbl
917 );
918 END IF; --- dummy flag
919
920 ELSE --- plsql expression with no concat segment clause: Should not occur
921 bis_debug_pub.add('flag error');
922 END IF; --- concat segment flag
923
924 --- not a plsql expression - must be a flexfield
925 ELSIF (p_column_table IS NULL)
926 --- regular BVG behavior (not pruned)
927 THEN
928 bis_debug_pub.Add('regular BVG behavior (not pruned)');
929 FND_FLEX_KEY_API.SET_SESSION_MODE(session_mode => 'customer_data');
930 l_Flexfield := FND_FLEX_KEY_API.FIND_FLEXFIELD
931 ( appl_short_name => p_Application_Name
932 , flex_code => p_Key_Flex_Code
933 );
934 FND_FLEX_KEY_API.GET_STRUCTURES( flexfield => l_Flexfield
935 , enabled_only => TRUE
936 , nstructures => l_nStructures
937 , structures => l_Structure_List
938 );
939
940 ---EDW flag change
941 IF(l_nStructures = 0) THEN
942 --- If the flexfield is not defined then we do not want to do anything.
943 bis_debug_pub.add('No Structures');
944 l_dummy_flag := TRUE;
945 ELSE
946 l_dummy_flag := p_dummy_flag;
947 --- bis_debug_pub.Add('< update_Key_Flex_Tables');
948 --- return;
949 END IF;
950
951 IF (p_EDW_Flag ) THEN
952 x_Column_Table(x_column_table.COUNT+1):= l_Prefix || 'context';
953
954 IF l_dummy_flag OR l_Flexfield.structure_column IS NULL THEN
955 x_Select_Table(x_Select_Table.COUNT + 1) := 'TO_NUMBER(NULL),';
956 ELSE
957 x_Select_Table(x_Select_Table.COUNT + 1) := p_Table_Alias
958 || '.'
959 || l_Flexfield.structure_column
960 || ', ';
961 END IF; --- dummy flag
962 END IF; --- EDW flag
963
964
965 --
966 -- set the column table
967
968 x_Column_Table(x_column_table.COUNT+1):= l_Prefix || p_Concat_Seg_Name;
969 IF l_dummy_flag THEN
970 x_Select_Table(x_Select_Table.COUNT + 1) := 'NULL';
971 ELSE
972 IF(l_nStructures > 1) THEN
973 --- add the decode statement only first
974 x_Select_Table(x_Select_Table.COUNT + 1) := ' DECODE( ' || p_Table_Alias
975 || '.'
976 || l_Flexfield.structure_column;
977 END IF;
978
979 bis_debug_pub.Add('l_nStructures = ' || l_nstructures);
980 --
981 FOR i IN 1 .. l_nStructures LOOP
982 -- nest a DECODE statement if we have more then 127 structures in the outer DECODE
983 -- as we nest DECODE statements inside DECODE statements, the max number of
984 -- values in the DECODE statement will decrement
985 IF (MOD(i, l_decode_max) = 0) THEN
986 x_Select_Table(x_Select_Table.COUNT + 1) := lpad(' ',7*l_decode_counter)
987 ||', DECODE( ' || p_Table_Alias
988 || '.'
989 || l_Flexfield.structure_column;
990 l_decode_Counter := l_decode_Counter +1;
991 l_decode_max := l_decode_max - 1;
992 END IF;
993
994 bis_debug_pub.ADD('before calling fnd');
995 l_Structure := FND_FLEX_KEY_API.FIND_STRUCTURE( flexfield => l_Flexfield
996 , structure_number => l_Structure_List(i)
997 );
998 bis_debug_pub.ADD('after calling fnd');
999 bis_debug_pub.Add('l_Structure.structure_name = ' ||l_Structure.structure_name);
1000 bis_debug_pub.Add('l_Structure.structure_number = ' ||l_Structure.structure_number);
1001 bis_debug_pub.Add('l_Structure.segment_separator = ' ||l_Structure.segment_separator);
1002 --
1003
1004 add_kfx_segments_concat(l_nStructures
1005 , l_Flexfield
1006 , l_Structure
1007 , p_table_alias
1008 , 1 + (l_decode_counter * 8)
1009 , l_Select_Table
1010 , x_return_status
1011 , x_error_Tbl
1012 );
1013 IF l_select_table.COUNT > 0 THEN
1014 IF (l_nstructures > 1 OR i > 1)THEN
1015 x_Select_Table(x_Select_Table.COUNT + 1) :=
1016 lpad(', ', 1 + (l_decode_counter * 8));
1017 END IF;
1018
1019 bis_vg_util.concatenate_Tables( x_Select_Table
1020 , l_Select_Table
1021 , x_Select_Table
1022 , x_return_status
1023 , x_error_Tbl
1024 );
1025 END IF;
1026
1027
1028
1029 END LOOP;
1030 --
1031 bis_debug_pub.ADD('after the loop');
1032 IF(l_nStructures > 1) THEN
1033 IF(x_Select_Table.COUNT = 1) THEN
1034 -- seems like none of the structures had any segments defined
1035 -- hence add a NULL pair to make decode compile
1036 x_Select_Table(2) := ' , NULL, NULL';
1037 END IF;
1038 -- need to check to see if any nested DECODE statements
1039 x_Select_Table(x_Select_Table.COUNT+1) := lpad(', NULL'
1040 ,l_decode_counter*8 + 5
1041 );
1042 FOR i IN REVERSE 1.. l_decode_Counter LOOP
1043 x_Select_Table(x_Select_Table.COUNT+1) := lpad(')'
1044 ,i*8
1045 );
1046 END LOOP;
1047 -- add the closing parentheses
1048 END IF; --- l_nstructures > 1
1049
1050 --
1051 END IF; --- dummy flag
1052
1053 IF (p_concat_segment_flag) THEN
1054 bis_debug_pub.Add('p_Concat_Segment_Flag = TRUE');
1055 --- we need to put in the columns as well
1056 NULL;
1057 ELSE
1058 bis_debug_pub.Add('p_Concat_Segment_Flag = FALSE');
1059 --
1060 --- we need to put in the columns as well
1061
1062 bis_debug_pub.ADD('adding columns to the table');
1063
1064 FOR i IN 1 .. l_nStructures LOOP
1065 bis_debug_pub.ADD('before calling fnd');
1066 l_Structure := FND_FLEX_KEY_API.FIND_STRUCTURE( flexfield => l_Flexfield
1067 , structure_number => l_Structure_List(i)
1068 );
1069 bis_debug_pub.ADD('after calling fnd');
1070 bis_debug_pub.Add('l_Structure.structure_name = '||l_Structure.structure_name);
1071 bis_debug_pub.Add('l_Structure.structure_number = '||l_Structure.structure_number);
1072 bis_debug_pub.Add('l_Structure.segment_separator = '||l_Structure.segment_separator);
1073 --
1074 IF(l_nStructures > 1 OR p_edw_flag) THEN
1075 -- we should use '^' rather than '_' (bug 2259939)
1076 l_Suffix := '^' || TO_CHAR(l_Structure.structure_number);
1077 END IF;
1078
1079 add_key_flexfield_segments(p_dummy_flag
1080 , i
1081 , l_Suffix
1082 , l_Flexfield
1083 , l_Structure
1084 , p_Concat_Seg_Name
1085 , p_decode_on_segments
1086 , p_Prefix
1087 , p_Table_Alias
1088 , l_Column_Table
1089 , l_Select_Table
1090 , l_Column_Comment_Table
1091 , x_return_status
1092 , x_error_Tbl
1093 );
1094 --
1095 bis_vg_util.concatenate_Tables(x_Column_Table
1096 , l_Column_Table
1097 , x_Column_Table
1098 , x_return_status
1099 , x_error_Tbl
1100 );
1101
1102 bis_vg_util.concatenate_Tables(x_Select_Table
1103 , l_Select_Table
1104 , x_Select_Table
1105 , x_return_status
1106 , x_error_Tbl
1107 );
1108
1109 bis_vg_util.concatenate_Tables( x_column_comment_table
1110 , l_column_comment_table
1111 , x_column_comment_table
1112 , x_return_status
1113 , x_error_Tbl
1114 );
1115 END LOOP;
1116 --
1117 END IF; --- Concat_segment_flag false
1118
1119 ELSE --- The pruned case - no need for concatenated segments column.
1120 bis_debug_pub.Add('The pruned case');
1121 FND_FLEX_KEY_API.SET_SESSION_MODE(session_mode => 'customer_data');
1122 l_Flexfield := FND_FLEX_KEY_API.FIND_FLEXFIELD
1123 (appl_short_name => p_Application_Name
1124 , flex_code => p_Key_Flex_Code
1125 );
1126
1127 --- Add selected columns
1128 l_count := 2;
1129 FOR i in p_column_table.first..p_column_table.last LOOP
1130 IF ( p_column_table(i).flex_field_type = 'K'
1131 --- AND p_column_table(i).flexfield_prefix = p_prefix
1132 AND p_column_table(i).id_flex_code = p_key_flex_code) THEN
1133 --- make sure that the structure number is maintained in the column name.
1134 x_column_table(l_count):= l_prefix
1135 || substrb(p_column_table(i).segment_name, 1
1136 , (30 - lengthb(l_prefix)- lengthb(p_column_table(i).structure_num)- 1))
1137 || '_'
1138 ||p_column_table(i).structure_num;
1139
1140 IF p_dummy_flag OR p_column_table(i).application_column_name IS NULL THEN
1141 x_select_table(l_count) := ', NULL';
1142 ELSE
1143 x_select_table(l_count) := ' , '
1144 || p_table_alias
1145 || '.'
1146 || p_column_table(i).application_column_name;
1147 END IF; --- dummy_flag
1148 l_count := l_count + 1;
1149 END IF; --- p_column_table(i) matches criteria
1150 END LOOP;
1151 --- Add context to all flexfields which have segments selected
1152 IF l_count > 2 THEN
1153
1154 x_Column_Table(1):= l_Prefix || 'context';
1155
1156 IF p_dummy_flag OR l_Flexfield.structure_column IS NULL THEN
1157 x_Select_Table(1) := 'TO_NUMBER(NULL)';
1158 ELSE
1159 x_Select_Table(1) := p_Table_Alias
1160 || '.'
1161 || l_Flexfield.structure_column;
1162 END IF; --- dummy flag
1163 END IF; --- l_count > 2
1164 END IF; ---PLSQL_expression ELSE flexfield full lookup ELSE pruned lookup
1165
1166 bis_vg_util.print_View_Text
1167 ( x_Column_Table
1168 , x_return_status
1169 , x_error_Tbl
1170 );
1171
1172 bis_vg_util.print_View_Text
1173 ( x_Select_Table
1174 , x_return_status
1175 , x_error_Tbl
1176 );
1177
1178 --
1179 bis_debug_pub.Add('< update_Key_Flex_Tables');
1180 bis_debug_pub.debug_off;
1181 --- ====== ====== ====== ====== ====== ======
1182 --- OBSOLETE FUNCTIONALITY: we no longer care if there are no segments in
1183 --- the flexfield - leave a blank in the view
1184 --- ====== ====== ====== ====== ====== ======
1185 --- IF (x_select_table.COUNT = 0) THEN
1186 --- -- no segments in the key flex.
1187 --- --
1188 --- BIS_VG_UTIL.Add_Error_message
1189 --- ( p_error_msg_name => BIS_VG_KEY_FLEX.NO_SEGMENTS_IN_KEY_FLEX_MSG
1190 --- , p_error_proc_name => G_PKG_NAME||'.update_Key_Flex_Tables'
1191 --- , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1192 --- , p_error_table => x_error_tbl
1193 --- , x_error_table => x_error_tbl
1194 --- );
1195 --- bis_vg_log.update_failure_log( x_error_tbl
1196 --- , x_return_status
1197 --- , x_error_Tbl
1198 --- );
1199 --- RAISE FND_API.G_EXC_ERROR;
1200 ---
1201 ---
1202 --- END IF;
1203
1204 -- if no segments defined for flexfield use NULL for the column
1205 IF (x_select_table.COUNT = 0) THEN
1206 x_select_table(1) := 'NULL';
1207 END IF;
1208
1209 EXCEPTION
1210 when FND_API.G_EXC_ERROR then
1211 x_return_status := FND_API.G_RET_STS_ERROR ;
1212 RAISE FND_API.G_EXC_ERROR;
1213 when FND_API.G_EXC_UNEXPECTED_ERROR then
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1216 when others then
1217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1218 BIS_VG_UTIL.Add_Error_Message
1219 ( p_error_msg_id => SQLCODE
1220 , p_error_description => SQLERRM
1221 , p_error_proc_name => G_PKG_NAME||'.update_Key_Flex_Tables'
1222 , p_error_table => x_error_tbl
1223 , x_error_table => x_error_tbl
1224 );
1225 bis_vg_log.update_failure_log( x_error_tbl
1226 , x_return_status
1227 , x_error_Tbl
1228 );
1229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230
1231 END update_Key_Flex_Tables;
1232
1233 ---
1234 ---
1235 --- =====================
1236 --- PUBLIC PROCEDURES
1237 --- =====================
1238 ---
1239 --- =====================================================================================
1240 --- PROCEDURE : add_Key_Flex_Info
1241 --- PARAMETERS: 1. p_View_Column_Table table of varchars to hold columns of view text
1242 --- 2. p_View_Select_Table table of varchars to hold select clause of view
1243 --- 3. p_Mode mode of the program
1244 --- 4. p_column_table List of columns for calls from generate_pruned_view
1245 --- 5. p_Column_Pointer pointer to the key flex column in column table
1246 --- 6. p_Select_Pointer pointer to the select clause
1247 --- 7. p_From_Pointer pointer to the corresponding from clause
1248 --- 8. x_Column_Table table of varchars to hold additional columns
1249 --- 9. x_Select_Table table of varchars to hold additional columns
1250 --- 10. x_Column_Comment_Table table of records used to hold flex info
1251 --- for flex derived columns
1252 --- 11. x_Column_Pointer pointer to the character after the delimiter
1253 --- (column table)
1254 --- 12. x_Select_Pointer pointer to the character after the delimiter
1255 --- (select table)
1256 --- 13. x_return_status error or normal
1257 --- 14. x_error_Tbl table of error messages
1258 ---
1259 --- COMMENT : Call this procedure to add particular key flexfield information to a view.
1260 --- EXCEPTION : FND_API.G_EXC_UNEXPECTED_ERROR
1261 --- FND_API.G_EXC_ERROR;
1262 --- ==================================================================================== */
1263
1264 PROCEDURE add_Key_Flex_Info
1265 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
1266 , p_View_Select_Table IN bis_vg_types.View_Text_Table_Type
1267 , p_Mode IN NUMBER
1268 , p_column_table IN BIS_VG_TYPES.flexfield_column_table_type
1269 , p_Column_Pointer IN bis_vg_types.View_Character_Pointer_Type
1270 , p_Select_Pointer IN bis_vg_types.View_Character_Pointer_Type
1271 , p_From_Pointer IN bis_vg_types.View_Character_Pointer_Type
1272 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
1273 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
1274 , x_Column_Comment_Table OUT BIS_VG_TYPES.Flex_Column_Comment_Table_Type
1275 , x_Column_Pointer OUT bis_vg_types.View_Character_Pointer_Type
1276 , x_Select_Pointer OUT bis_vg_types.View_Character_Pointer_Type
1277 , x_return_status OUT VARCHAR2
1278 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1279 )
1280 IS
1281 --
1282 l_Concat_Seg_Name VARCHAR2(100);
1283 l_Prefix VARCHAR2(100);
1284 l_EDW_Flag BOOLEAN; ---EDW flag change
1285 l_DUMMY_Flag BOOLEAN; ---EDW flag change
1286 l_Concat_Segment_Flag BOOLEAN;
1287 l_decode_on_segments BOOLEAN;
1288 --
1289 l_PLSQL_Expression VARCHAR2(2000);
1290 l_Application_Name VARCHAR2(10);
1291 l_Key_Flex_Code VARCHAR2(100);
1292 l_Table_Alias VARCHAR2(100);
1293 l_Table_Name VARCHAR2(100);
1294 l_Structure_Column VARCHAR2(100);
1295 --
1296 BEGIN
1297 --
1298 bis_debug_pub.Add('> add_Key_Flex_Info');
1299 x_return_status := FND_API.G_RET_STS_SUCCESS;
1300 parse_KF_Column_Line( p_View_Column_Table
1301 , p_Column_Pointer
1302 , x_Column_Pointer
1303 , l_Concat_Seg_Name
1304 , l_Concat_Segment_Flag
1305 , l_decode_on_segments
1306 , l_EDW_Flag ---EDW flag change
1307 , l_Prefix
1308 , x_return_status
1309 , x_error_Tbl
1310 );
1311
1312 --- --- DEBUG ---
1313 --- bis_debug_pub.Add('l_Concat_Seg_Name = ' || l_Concat_Seg_Name);
1314 ---
1315 ---
1316 --- IF(l_Concat_Segment_Flag = TRUE) THEN
1317 --- bis_debug_pub.Add('l_Concat_Segment_Flag = TRUE');
1318 --- ELSE
1319 --- bis_debug_pub.Add('l_Concat_Segment_Flag = FALSE');
1320 --- END IF;
1321 --- --- -- -
1322
1323 --- This clause catches flexfield tags that do not have the
1324 --- _EDW tags when the generator is called via generate_pruned_view
1325 IF (l_edw_flag = FALSE AND p_column_table IS NOT NULL)
1326 THEN
1327 RAISE bis_view_generator_pvt.CANNOT_PRUNE_NON_EDW_VIEW;
1328 END IF;
1329
1330 parse_KF_Select_Line( p_View_Select_Table
1331 , p_Select_Pointer
1332 , x_Select_Pointer
1333 , l_PLSQL_Expression
1334 , l_Application_Name
1335 , l_Key_Flex_Code
1336 , l_Table_Alias
1337 , l_Structure_Column
1338 , l_DUMMY_Flag ---EDW flag change
1339 , x_return_status
1340 , x_error_Tbl
1341 );
1342 bis_debug_pub.Add('l_PLSQL_Expression = ' || l_PLSQL_Expression);
1343 bis_debug_pub.Add('l_Application_Name = ' || l_Application_Name);
1344 bis_debug_pub.Add('l_Key_Flex_Code = ' || l_Key_Flex_Code);
1345 bis_debug_pub.Add('l_Table_Alias = ' || l_Table_Alias);
1346 bis_debug_pub.Add('l_Structure_Column = ' || l_Structure_Column);
1347
1348 IF(
1349 (p_Mode <> bis_vg_types.remove_tags_mode)
1350 AND
1351 (p_column_table IS NULL
1352 OR
1353 p_column_table.COUNT > 0
1354 )
1355 )
1356 THEN
1357
1358 --- IF(l_Prefix IS NULL) THEN
1359 --- x_Column_Table(1) := l_Concat_Seg_Name;
1360 --- ELSE
1361 --- x_Column_Table(1) := l_Prefix || '_' || l_Concat_Seg_Name;
1362 --- END IF;
1363 --- x_Select_Table(1) := 'TO_CHAR(NULL)';
1364 --- ELSE
1365 update_Key_Flex_Tables(l_Concat_Seg_Name
1366 , l_Concat_Segment_Flag
1367 , l_decode_on_segments
1368 , l_EDW_flag
1369 , l_dummy_flag
1370 , p_column_table
1371 , l_prefix
1372 , l_PLSQL_Expression
1373 , l_Application_Name
1374 , l_Key_Flex_Code
1375 , l_Table_Alias
1376 , l_Structure_Column
1377 , x_Column_Table
1378 , x_Select_Table
1379 , x_Column_Comment_Table
1380 , x_return_status
1381 , x_error_Tbl
1382 );
1383
1384 END IF;
1385 bis_debug_pub.Add('COLUMN POINTER');
1386 bis_vg_util.print_View_Pointer( x_Column_Pointer
1387 , x_return_status
1388 , x_error_Tbl
1389 );
1390 bis_debug_pub.Add('SELECT POINTER');
1391 bis_vg_util.print_View_Pointer( x_Select_Pointer
1392 , x_return_status
1393 , x_error_Tbl
1394 );
1395 --- bis_debug_pub.debug_on;
1396 bis_vg_util.print_View_Text(x_Column_Table, x_return_status, x_error_Tbl);
1397 bis_vg_util.print_View_Text(x_Select_Table, x_return_status, x_error_Tbl);
1398 --
1399 bis_debug_pub.Add('< add_Key_Flex_Info');
1400 --- bis_debug_pub.debug_off;
1401 --
1402
1403
1404 EXCEPTION
1405 when bis_view_generator_pvt.cannot_prune_non_edw_view THEN
1406 RAISE; -- same exception
1407 when FND_API.G_EXC_ERROR then
1408 x_return_status := FND_API.G_RET_STS_ERROR ;
1409 RAISE; -- same exception
1410 when FND_API.G_EXC_UNEXPECTED_ERROR then
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1412 RAISE; -- same exception
1413 when others then
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1415 BIS_VG_UTIL.Add_Error_Message
1416 ( p_error_msg_id => SQLCODE
1417 , p_error_description => SQLERRM
1418 , p_error_proc_name => G_PKG_NAME||'.add_Key_Flex_Info'
1419 , p_error_table => x_error_tbl
1420 , x_error_table => x_error_tbl
1421 );
1422 bis_vg_log.update_failure_log( x_error_tbl
1423 , x_return_status
1424 , x_error_Tbl
1425 );
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427
1428 END add_Key_Flex_Info;
1429 --
1430 --
1431 END bis_vg_key_flex;