[Home] [Help]
PACKAGE BODY: APPS.BIS_VG_DESC_FLEX
Source
1 PACKAGE BODY bis_vg_desc_flex AS
2 /* $Header: BISTDFXB.pls 120.2.12020000.2 2012/12/04 17:41:46 dbowles ship $ */
3
4 --- Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA
5 --- All rights reserved.
6 ---
7 --- FILENAME
8 ---
9 --- BISTDFXB.pls
10 ---
11 --- DESCRIPTION
12 ---
13 --- body of package which handles the descriptive flexfield tag
14 ---
15 --- NOTES
16 ---
17 --- HISTORY
18 ---
19 --- 29-JUL-98 Created
20 --- 19-MAR-99 Edited by WNASRALL@US for exception handling
21 --- 21-Apr-99 Edited by WNASRALL@US to correct parsing behavior
22 --- 10-NOV-00 Edited by WNASRALL@US to add new function generate_pruned_view
23 --- 11-DEC-01 Edited by DBOWLES Added dr driver comments.
24 ---
25 ---
26 G_PKG_NAME CONSTANT VARCHAR(30) := 'bis_vg_desc_flex';
27 --- ===================
28 --- PRIVATE FUNCTION
29 --- ===================
30 ---
31 FUNCTION to_boolean(value IN VARCHAR2) RETURN BOOLEAN
32 IS
33 rv BOOLEAN;
34 BEGIN
35 IF(value in ('Y', 'y')) THEN
36 rv := TRUE;
37 ELSE
38 rv := FALSE;
39 END IF;
40 RETURN rv;
41 END;
42
43 -- =====================
44 -- PRIVATE PROCEDURES
45 -- =====================
46 -- ========================================
47 -- Procedure Name: Get_contexts
48 -- returns the contexts in a flexfield ordered by the creation_date
49 -- ========================================
50 PROCEDURE get_contexts(flexfield IN FND_DFLEX.DFLEX_R,
51 contexts OUT FND_DFLEX.CONTEXTS_DR)
52 IS
53 CURSOR context_c IS
54 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
55 description, global_flag, enabled_flag
56 FROM fnd_descr_flex_contexts_vl
57 WHERE application_id = flexfield.application_id
58 AND descriptive_flexfield_name = flexfield.flexfield_name
59 ORDER BY creation_date;
60
61 CURSOR context_c_with_hints IS
62 SELECT /*+ leading(fnd_descr_flex_contexts_vl.t fnd_descr_flex_contexts_vl.b)
63 use_nl(fnd_descr_flex_contexts_vl.b)
64 index(fnd_descr_flex_contexts_vl.t
65 FND_DESCR_FLEX_CONTEXTS_TL_U1)*/
66 descriptive_flex_context_code, descriptive_flex_context_name,
67 description, global_flag, enabled_flag
68 FROM fnd_descr_flex_contexts_vl
69 WHERE application_id = flexfield.application_id
70 AND descriptive_flexfield_name = flexfield.flexfield_name
71 ORDER BY creation_date;
72
73 CURSOR context_c_new IS
74 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
75 description, global_flag, enabled_flag
76 FROM fnd_descr_flex_contexts_vl
77 WHERE application_id = flexfield.application_id
78 AND descriptive_flexfield_name = flexfield.flexfield_name
79 ORDER BY creation_date, descriptive_flex_context_code ;
80
81 i BINARY_INTEGER := 0;
82 rv FND_DFLEX.CONTEXTS_DR;
83
84 BEGIN
85 rv.global_context := 0;
86 -- Bug 6819715
87 IF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = '9.2' THEN
88 FOR context_rec IN context_c_with_hints LOOP
89 i := i + 1;
90 rv.context_code(i) := context_rec.descriptive_flex_context_code;
91 rv.context_name(i) := context_rec.descriptive_flex_context_name;
92 rv.context_description(i) := context_rec.description;
93 rv.is_global(i) := to_boolean(context_rec.global_flag);
94 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
95 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
96 rv.global_context := i;
97 END IF;
98 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
99 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
100 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
101 'context code is '||rv.context_code(i));
102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
103 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
104 'context name is '||rv.context_name(i));
105 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
106 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
107 'context description is '||rv.context_description(i));
108 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
109 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
110 'is_enabled is '||context_rec.enabled_flag);
111 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
112 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
113 'is global is '||context_rec.global_flag);
114 end if;
115 END LOOP;
116 ELSIF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = '9i' THEN
117 FOR context_rec IN context_c LOOP
118 i := i + 1;
119 rv.context_code(i) := context_rec.descriptive_flex_context_code;
120 rv.context_name(i) := context_rec.descriptive_flex_context_name;
121 rv.context_description(i) := context_rec.description;
122 rv.is_global(i) := to_boolean(context_rec.global_flag);
123 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
124 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
125 rv.global_context := i;
126 END IF;
127 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
128 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
132 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
129 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
130 'context code is '||rv.context_code(i));
131 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
133 'context name is '||rv.context_name(i));
134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
135 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
136 'context description is '||rv.context_description(i));
137 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
138 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
139 'is_enabled is '||context_rec.enabled_flag);
140 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
141 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
142 'is global is '||context_rec.global_flag);
143 end if;
144 END LOOP;
145 ELSIF BIS_VIEW_GENERATOR_PVT.g_use_optimizer_hints = 'NEW' THEN
146 FOR context_rec IN context_c_new LOOP
147 i := i + 1;
148 rv.context_code(i) := context_rec.descriptive_flex_context_code;
149 rv.context_name(i) := context_rec.descriptive_flex_context_name;
150 rv.context_description(i) := context_rec.description;
151 rv.is_global(i) := to_boolean(context_rec.global_flag);
152 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
153 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
154 rv.global_context := i;
155 END IF;
156 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
158 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
159 'context code is '||rv.context_code(i));
160 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
161 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
162 'context name is '||rv.context_name(i));
163 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
164 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
165 'context description is '||rv.context_description(i));
166 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
167 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
168 'is_enabled is '||context_rec.enabled_flag);
169 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
170 'fnd.plsql.BIS_VG_DESC_FLEX.GET_CONTENTS',
171 'is global is '||context_rec.global_flag);
172 end if;
173 END LOOP;
174 END IF;
175 rv.ncontexts := i;
176 contexts := rv;
177 END;
178
179
180
181 --
182 -- =============================================================================
183 -- PROCEDURE : parse_DF_Column_Line
184 -- PARAMETERS: 1. p_View_Column_Table table of varchars to hold columns of
185 -- view text
186 -- 2. p_Column_Pointer pointer to the key flex column in
187 -- column table (IN)
188 -- 3. x_Column_Pointer pointer to the char after the
189 -- delimiter in
190 -- column table (OUT)
191 -- 4. x_prefix prefix of descriptive flexfield, if any
192 -- 5. x_return_status error or normal
193 -- 6. x_error_Tbl table of error messages
194 -- COMMENT : Call this procedure to parse the KF view column tag.
195 -- ---
196 --=============================================================================
197 PROCEDURE parse_DF_Column_Line
198 ( p_View_Column_Table IN bis_vg_types.View_Text_Table_Type
199 , p_Column_Pointer IN bis_vg_types.View_Character_Pointer_Type
200 , x_Column_Pointer OUT bis_vg_types.View_Character_Pointer_Type
201 , x_EDW_flag OUT BOOLEAN --EDW flag change
202 , x_prefix OUT VARCHAR2
203 , x_decode OUT BOOLEAN
204 , x_return_status OUT VARCHAR2
205 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
206 )
207 IS
208 --
209 l_token VARCHAR2(100);
210 l_string bis_vg_types.view_text_table_rec_type;
211 l_pos NUMBER;
212 --
213 BEGIN
214
215 bis_debug_pub.Add('> parse_DF_Column_Line');
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217 -- get row of text from table
218 l_string := bis_vg_util.get_row ( p_View_Column_Table
219 , p_Column_Pointer
220 , x_return_status
221 , x_error_Tbl
222 );
223
224 -- get "_DF"
225 x_EDW_flag := FALSE; --EDW flag change
226 l_token := bis_vg_parser.get_string_token
227 ( l_string
228 , 1
229 , ':'
230 , l_pos
231 , x_return_status
232 , x_error_Tbl
233 );
234 bis_debug_pub.Add('l_token = ' || l_token);
235
236 IF(l_pos IS NOT NULL) THEN --- first real token exists
237 -- get next token, if any
238 x_prefix := bis_vg_parser.get_string_token
239 ( l_string
240 , l_pos
241 , ':'
242 , l_pos
243 , x_return_status
244 , x_error_Tbl
245 );
246
247 bis_debug_pub.Add('x_prefix = ' || x_prefix);
248
249 --EDW flag change
250
251 IF(upper(substr(x_prefix, 1, 4)) = '_EDW')
252 --- '_EDW' in forst position
253 THEN
254 bis_vg_util.add_message
255 ( DFX_COL_TAG_EXP_BAD_FLAG_MSG
256 , FND_MSG_PUB.G_MSG_LVL_ERROR
257 , 'MESSAGE_TAG'
258 , l_string
259 , x_return_status
260 , x_error_Tbl
261 );
262 --
263 RAISE MALFORMED_DFX_COL_TAG_BAD_FLAG;
264 --
265 END IF; --- '_EDW' in first position
266
267 IF(l_pos IS NOT NULL) --- Second token exists
268 THEN
269 l_token := bis_vg_parser.get_string_token
270 ( l_string
271 , l_pos
272 , ':'
273 , l_pos
274 , x_return_status
275 , x_error_Tbl
276 );
277
278 bis_debug_pub.Add('l_token = ' || l_token);
279
280
281 IF( UPPER(l_token) = '_EDW' )
282 THEN
283 x_EDW_flag := TRUE;
284 bis_debug_pub.Add('x_EDW_flag = TRUE');
285 ELSIF (UPPER(l_token) = '_BS' ) THEN
286 x_decode := TRUE;
287 bis_debug_pub.Add('x_decode = TRUE');
288 ELSE --- second token is not a valid flag
289 bis_vg_util.add_message
290 ( DFX_COL_TAG_EXP_BAD_FLAG_MSG
291 , FND_MSG_PUB.G_MSG_LVL_ERROR
292 , 'MESSAGE_TAG'
293 , l_string
294 , x_return_status
295 , x_error_Tbl
296 );
297 RAISE MALFORMED_DFX_COL_TAG_BAD_FLAG;
298 END IF; --- ( UPPER(l_token) = '_EDW' )
299
300 if (l_pos IS NOT NULL) THEN --- third token exists
301 l_token := bis_vg_parser.get_string_token
302 ( l_string
303 , l_pos
304 , ':'
305 , l_pos
306 , x_return_status
307 , x_error_Tbl
308 );
309 if ((upper(l_token) = '_BS') AND (x_decode = FALSE))
310 then
311 x_decode := TRUE;
312 elsif (( upper(l_token) = '_EDW') AND (x_EDW_flag = FALSE))
313 then
314 x_EDW_flag := TRUE;
315 bis_debug_pub.Add('x_EDW_flag = TRUE');
316 else --- Third token is not a valid flag
317 bis_vg_util.add_message
318 ( DFX_COL_TAG_EXP_BAD_FLAG_MSG
319 , FND_MSG_PUB.G_MSG_LVL_ERROR
320 , 'MESSAGE_TAG'
321 , l_string
322 , x_return_status
323 , x_error_Tbl
324 );
325 RAISE MALFORMED_DFX_COL_TAG_BAD_FLAG;
326
327 end if; --- third token = _BS or _EDW
328 end if; --- third token exists
329 ELSE --- second token does not exist (only one token)
330 if (x_prefix = '_BS') --- first and only token is '_BS'
331 THEN
332 x_decode := TRUE;
333 x_prefix := NULL;
334 END IF; --- first and only token is '_BS'
335 END IF; --- Second token exists
336 END IF; --- first token exists
337
338
339
340 x_Column_Pointer := bis_vg_util.increment_pointer_by_row
341 ( p_View_Column_Table
342 , p_Column_Pointer
343 , x_return_status
344 , x_error_Tbl
345 );
346 bis_debug_pub.Add('< parse_DF_Column_Line');
347 --
348
349 EXCEPTION
350 when FND_API.G_EXC_ERROR then
351 x_return_status := FND_API.G_RET_STS_ERROR ;
352 RAISE FND_API.G_EXC_ERROR;
353 when FND_API.G_EXC_UNEXPECTED_ERROR then
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 when others then
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 BIS_VG_UTIL.Add_Error_Message
359 ( p_error_msg_id => SQLCODE
360 , p_error_description => SQLERRM
361 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Column_Line'
362 , p_error_table => x_error_tbl
363 , x_error_table => x_error_tbl
364 );
365 bis_vg_log.update_failure_log( x_error_tbl
366 , x_return_status
367 , x_error_Tbl
368 );
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370
371 END parse_DF_Column_Line;
372 --
373 -- ============================================================================
374 -- FUNCTION: CHECK_APPLICATION_VALIDITY (PRIVATE FUNCTION)
375 -- RETURNS: boolean - true if application short name p[assed is defined
376 -- 1. p_app short name of application
377 --
378 -- COMMENT : Checks against the FND_APPLICATION_ALL_VIEW.
379 -- Called from parse_DF_Select_Line.
380 -- ---
381 -- ==========================================================================
382 FUNCTION CHECK_APPLICATION_VALIDITY
383 ( p_app IN VARCHAR2
384 )
385 return boolean
386 is
387 l_return_value boolean ;
388 l_dummy number;
389 cursor l_cursor is
390 select 1
394 BIS_DEBUG_PUB.Add('> check_application_validity');
391 from fnd_application_all_view
392 where application_short_name = p_app;
393 begin
395 open l_cursor ;
396 fetch l_cursor into l_dummy ;
397 l_return_value := l_cursor%found ;
398 close l_cursor ;
399 BIS_DEBUG_PUB.Add('< check_application_validity');
400 return(l_return_value);
401
402 END CHECK_APPLICATION_VALIDITY;
403
404
405 -- =============================================================================
406 -- PROCEDURE : parse_DF_Select_Line
407 -- PARAMETERS: 1. p_View_Select_Table table of varchars to hold select clause
408 -- of view text
409 -- 2. p_Select_Pointer pointer to the key flex column in select
410 -- table (IN)
411 -- 3. x_Select_Pointer pointer to the char after the delimiter in
412 -- select table (OUT)
413 -- 4. x_Application_Name Application Name
414 -- 5. x_Desc_Flex_Name Desc Flexfield name
415 -- 6. x_Table_Alias Table alias
416 -- 7. x_return_status error or normal
417 -- 8. x_error_Tbl table of error messages
418 -- COMMENT : Call this procedure to parse the DF selected tag.
419 -- ---
420 -- =============================================================================
421 PROCEDURE parse_DF_Select_Line
422 ( p_View_Select_Table IN bis_vg_types.View_Text_Table_Type
423 , p_Select_Pointer IN bis_vg_types.View_Character_Pointer_Type
424 , x_Select_Pointer OUT bis_vg_types.View_Character_Pointer_Type
425 , x_Application_Name OUT VARCHAR2
426 , x_Desc_Flex_Name OUT VARCHAR2
427 , x_Table_Alias OUT VARCHAR2
428 , x_DUMMY_flag OUT BOOLEAN
429 , x_return_status OUT VARCHAR2
430 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
431 )
432 IS
433 --
434 l_whole_tag VARCHAR2(2000);
435 l_tmp_pointer bis_vg_types.View_Character_Pointer_Type;
436 --
437 BEGIN
438 bis_debug_pub.Add('> parse_DF_Select_Line');
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440 -- get '_DF'
441 l_whole_tag := bis_vg_parser.get_token_increment_pointer
442 ( p_View_Select_Table
443 , p_Select_Pointer
444 , ':'''
445 , x_Select_Pointer
446 , x_return_status
447 , x_error_Tbl
448 );
449 --Parse the whole tag for error messages
450 l_whole_tag := bis_vg_parser.get_expression( p_View_Select_Table
451 , p_Select_Pointer
452 , l_tmp_pointer
453 , x_return_status
454 , x_error_Tbl
455 );
456 l_tmp_pointer := bis_vg_util.increment_pointer
457 ( p_View_Select_Table
458 , l_tmp_pointer
459 , x_return_status
460 , x_error_Tbl
461 );
462
463
464 IF bis_vg_util.equal_pointers(
465 l_tmp_pointer
466 , x_select_pointer
467 , x_return_status
468 , x_error_Tbl
469 )
470 THEN
471 BIS_VG_UTIL.Add_Error_message
472 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_APP_MSG
473 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
474 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
475 , p_token1 => 'tag'
476 , p_value1 => l_whole_tag
477 , p_error_table => x_error_tbl
478 , x_error_table => x_error_tbl
479 );
480 bis_vg_log.update_failure_log( x_error_tbl
481 , x_return_status
482 , x_error_Tbl
483 );
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486
487 x_Application_Name := bis_vg_parser.get_token_increment_pointer
488 ( p_View_Select_Table
489 , x_Select_Pointer
490 , ':'''
491 , x_Select_Pointer
492 , x_return_status
493 , x_error_Tbl
494 );
495
496 IF (x_Application_Name IS NULL
497 OR
498 bis_vg_util.equal_pointers(
499 l_tmp_pointer
500 , x_select_pointer
501 , x_return_status
502 , x_error_Tbl
503 )
504
505 ) THEN
506 BIS_VG_UTIL.Add_Error_message
507 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_APP_MSG
508 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
509 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
510 , p_token1 => 'tag'
511 , p_value1 => l_whole_tag
512 , p_error_table => x_error_tbl
513 , x_error_table => x_error_tbl
514 );
515 bis_vg_log.update_failure_log( x_error_tbl
516 , x_return_status
517 , x_error_Tbl
518 );
519 RAISE FND_API.G_EXC_ERROR;
520 END IF;
521
522 --EDW flag change
523
524 IF (x_Application_Name = '_DUMMY') THEN
525 x_DUMMY_flag := TRUE;
526
527 x_Application_Name:= bis_vg_parser.get_token_increment_pointer
528 ( p_View_Select_Table
529 , x_Select_Pointer
530 , ':'
531 , x_Select_Pointer
532 , x_return_status
533 , x_error_Tbl
534 );
535
536 END IF;
537 --EDW flag change
538
539 bis_debug_pub.Add('x_Application_Name = ' || x_Application_Name);
540 x_Desc_Flex_Name := bis_vg_parser.get_token_increment_pointer
541 ( p_View_Select_Table
542 , x_Select_Pointer
543 , ':'''
544 , x_Select_Pointer
545 , x_return_status
546 , x_error_Tbl
547 );
548
549 IF (x_desc_flex_name IS NULL
550 OR
551 bis_vg_util.equal_pointers(
552 l_tmp_pointer
553 , x_select_pointer
554 , x_return_status
555 , x_error_Tbl
556 )
557
558 ) THEN
559
560 BIS_VG_UTIL.Add_Error_message
561 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_NAME_MSG
562 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
563 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
564 , p_token1 => 'tag'
565 , p_value1 => l_whole_tag
566 , p_error_table => x_error_tbl
567 , x_error_table => x_error_tbl
568 );
569 bis_vg_log.update_failure_log( x_error_tbl
570 , x_return_status
571 , x_error_Tbl
572 );
573 RAISE FND_API.G_EXC_ERROR;
574
575 END IF;
576
577 bis_debug_pub.Add('x_Desc_Flex_Name = ' || x_Desc_Flex_Name);
578 x_Table_Alias := bis_vg_parser.get_token
579 ( p_View_Select_Table
580 , x_Select_Pointer
581 , ':'''
582 , x_Select_Pointer
583 , x_return_status
584 , x_error_Tbl
585 );
586 bis_debug_pub.Add('x_Table_Alias = ' || x_Table_Alias);
587
588
589 IF (x_table_alias IS NULL
590 OR
591 bis_vg_util.equal_pointers(
592 l_tmp_pointer
593 , x_select_pointer
594 , x_return_status
595 , x_error_Tbl
596 )
597
598 ) THEN
599 BIS_VG_UTIL.Add_Error_message
600 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_NO_TABLE_MSG
601 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
602 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
603 , p_token1 => 'tag'
604 , p_value1 => l_whole_tag
605 , p_error_table => x_error_tbl
606 , x_error_table => x_error_tbl
607 );
608 bis_vg_log.update_failure_log( x_error_tbl
609 , x_return_status
610 , x_error_Tbl
611 );
612 RAISE FND_API.G_EXC_ERROR;
613 END IF;
614
615 IF check_application_validity(x_Application_Name)
616 THEN
617 -- EVERYTHING IS FINE
618 x_Select_Pointer := bis_vg_util.increment_pointer( p_View_Select_Table
619 , x_Select_Pointer
620 , x_return_status
621 , x_error_Tbl
622 );
623 bis_debug_pub.Add('< parse_DF_Select_Line');
624 ELSE
625 BIS_VG_UTIL.Add_Error_message
626 ( p_error_msg_name => bis_vg_desc_flex.DFX_SEL_TAG_EXP_INVALID_APP
627 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
628 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
629 , p_token1 => 'tag'
630 , p_value1 => l_whole_tag
631 , p_token2 => 'app'
632 , p_value2 => x_Application_Name
633 , p_error_table => x_error_tbl
634 , x_error_table => x_error_tbl
635 );
636 bis_vg_log.update_failure_log( x_error_tbl
637 , x_return_status
638 , x_error_Tbl
639 );
640 RAISE FND_API.G_EXC_ERROR;
641
642 END IF;
643
644 --
645 EXCEPTION
646 when
647 FND_API.G_EXC_ERROR then
648 x_return_status := FND_API.G_RET_STS_ERROR ;
649 RAISE FND_API.G_EXC_ERROR;
650 when FND_API.G_EXC_UNEXPECTED_ERROR then
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
653 when others then
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 BIS_VG_UTIL.Add_Error_Message
656 ( p_error_msg_id => SQLCODE
657 , p_error_description => SQLERRM
658 , p_error_proc_name => G_PKG_NAME||'.parse_DF_Select_Line'
659 , p_error_table => x_error_tbl
660 , x_error_table => x_error_tbl
661 );
662 bis_vg_log.update_failure_log( x_error_tbl
663 , x_return_status
664 , x_error_Tbl
665 );
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667
668 END parse_DF_Select_Line;
669 --
670 -- =============================================================================
671 -- PROCEDURE : add_desc_flexfield_segments
672 -- PARAMETERS: 1. p_nContexts_flag is TRUE if only 1 context has been defined
673 -- 2. p_Flexfield desc flexfield dflex_r variable
674 -- 3. p_Flexinfo desc flexfield dflex_dr variable
675 -- 4. p_Context_Code context of the context of the desc flexfield
676 -- 5. p_Context_Num number of the context of the desc flexfield
677 -- 6. p_Prefix Desc Flexfield Name
678 -- 7. p_Table_Alias Table alias
679 -- 8. x_Select_Table table of varchars to hold select clause of
680 -- view text
681 -- 9. x_Column_Comment_Table table to hold flex info
682 --
683 -- 10. x_Column_Table table of varchars to hold select clause of
684 -- view text
685 -- 11. p_attr_categ_flag flag to indicate if ATTRIBUTE_CATEGORY is
686 -- to be added
687 -- 12. x_attr_categ_flag updated flag to indicate if
688 -- ATTRIBUTE_CATEGORY is to be added
689 -- 13. x_return_status error or normal
690 -- 14. x_error_Tbl table of error messages
691 -- 15. p_schema schema name -- schema name
692 -- COMMENT : Call this procedure to add the segments for desc flexfields.
693 -- ---
694 -- ============================================================================
695 PROCEDURE add_desc_flexfield_segments
696 ( p_nContexts_flag IN BOOLEAN
697 , p_Flexfield IN FND_DFLEX.DFLEX_R
698 , p_Flexinfo IN FND_DFLEX.DFLEX_DR
699 , p_Context_Code IN
700 FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE
701 , p_Context_Num IN NUMBER
702 , p_Prefix IN VARCHAR2
703 , p_decode IN BOOLEAN
704 , p_Table_Alias IN VARCHAR2
705 , p_EDW_Flag IN BOOLEAN --EDW flag change
706 , p_DUMMY_Flag IN BOOLEAN --EDW flag change
707 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
708 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
709 , x_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
710 , p_attr_categ_flag IN BOOLEAN
711 , x_attr_categ_flag OUT BOOLEAN
712 , x_return_status OUT VARCHAR2
713 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
714 , p_schema IN VARCHAR2 --schema name
715 )
716 IS
717 --
718 l_segments FND_DFLEX.SEGMENTS_DR;
719 --
720 l_prefix VARCHAR(100);
721 l_count NUMBER;
722 l_context_code VARCHAR2(100) := NULL;
723 l_col_data_type varchar2(106) :=null;
724 type CurType is ref cursor;
725 cv CurType;
726 --
727 BEGIN
728 bis_debug_pub.Add('> add_desc_flexfield_segments');
729 x_return_status := FND_API.G_RET_STS_SUCCESS;
730 IF(p_Prefix IS NOT NULL) THEN
731 l_prefix := p_Prefix || '_';
732 END IF;
733
734 bis_debug_pub.Add('l_prefix = ' || l_prefix);
735
736 x_attr_categ_flag := p_attr_categ_flag;
737 IF(p_Context_Code IS NOT NULL AND p_nContexts_flag = FALSE) THEN
738 -- l_context_code := '^' || p_Context_Code;
739 l_context_code := '^' || TO_CHAR(p_Context_Num);
740 END IF;
741 --
742 FND_DFLEX.GET_SEGMENTS
743 ( context => FND_DFLEX.MAKE_CONTEXT
744 ( flexfield => p_flexfield
745 , context_code => p_Context_Code
746 )
747 , segments => l_segments
748 , enabled_only => TRUE
749 );
750 IF(l_segments.NSEGMENTS > 0) THEN
751 IF(p_attr_categ_flag = TRUE) THEN
752 ---EDW flag change
753 IF (p_EDW_Flag = TRUE) THEN
754 x_Column_Table(1) := l_prefix || 'context';
755 x_Column_Table(1) := l_prefix || 'context';
756 x_Column_Comment_Table(1).column_name:= x_Column_Table(1);
757 x_Column_Comment_Table(1).flex_type := 'DESC CONTEXT';
758 --populate comments with application id, flex name, context code
759 x_Column_Comment_Table(1).column_comments :=p_flexfield.application_id||','||
760 p_flexfield.flexfield_name||','||
761 p_context_code;
762
763 ELSE
764 x_Column_Table(1) := l_prefix
765 || p_Flexinfo.form_context_prompt;
766 x_Column_Comment_Table(1).column_name:= x_Column_Table(1);
767 x_Column_Comment_Table(1).flex_type := 'DESC CONTEXT';
768 --populate comments with application id, flex name, context code
769 x_Column_Comment_Table(1).column_comments :=p_flexfield.application_id||','||
770 p_flexfield.flexfield_name||','||
771 p_context_code;
772 END IF;
773 ---EDW flag change
774
775 bis_debug_pub.Add('x_Column_Table(1) = ' || x_Column_Table(1));
776
777 -- x_Select_Table(1) := ' ' || p_Table_Alias || '.'
778 -- || p_Flexinfo.context_column_name;
779 --EDW flag change
780 IF p_DUMMY_flag
781 THEN
782 x_Select_Table(1) := 'TO_CHAR(NULL)';
783 ELSE
784 x_Select_Table(1) := ' ' || p_Table_Alias ||
785 '.' || p_Flexinfo.context_column_name;
786 END IF;
787 --EDW flag change
788
789 x_attr_categ_flag := FALSE;
790 END IF;
791 l_count := x_Column_Table.COUNT + 1;
792 FOR j IN 1 .. l_segments.NSEGMENTS LOOP
793 --
794 x_Column_Table(l_count) := l_prefix
795 || l_segments.SEGMENT_NAME(j)
796 || l_context_code;
797 x_Column_Comment_Table(l_count).column_name:= x_Column_Table(l_count);
798 x_Column_Comment_Table(l_count).flex_type := 'DESC SEGMENT';
799 --populate comments with application id, flex name, context code, application column
800 x_Column_Comment_Table(l_count).column_comments :=p_flexfield.application_id||','||
801 p_flexfield.flexfield_name||','||
802 p_context_code||','||
803 l_segments.APPLICATION_COLUMN_NAME(j);
804 bis_debug_pub.Add('x_Column_Table('|| l_count||') = '
805 || x_Column_Table(l_count));
806 IF (p_decode = TRUE) THEN
807 x_Select_Table(l_count) := ', '
808 || 'DECODE( '
809 ||p_flexinfo.context_column_name
810 || ', '
811 || ''''
812 ||p_context_code
813 ||''''
814 || ','
815 || p_Table_Alias
816 || '.'
817 || l_segments.APPLICATION_COLUMN_NAME(j)
818 || ', NULL'
819 || ')';
820 ELSE
821 --EDW flag change
822 IF p_DUMMY_flag
823 THEN
824 if l_segments.APPLICATION_COLUMN_NAME(j) not like 'ATTRIBUTE%' then
825 --use schema
826 open cv for
827 select col.DATA_TYPE
831 and syn.table_owner = p_schema
828 from user_synonyms syn, all_tab_columns col
829 where syn.synonym_name = p_flexinfo.table_name
830 and col.column_name = l_segments.APPLICATION_COLUMN_NAME(j)
832 and syn.table_owner = col.owner
833 and col.table_name = syn.table_name
834 UNION
835 select ucol.DATA_TYPE
836 from user_tab_columns ucol
837 where ucol.table_name = p_flexinfo.table_name
838 and ucol.column_name = l_segments.APPLICATION_COLUMN_NAME(j);
839
840 fetch cv into l_col_data_type;
841 close cv;
842 if l_col_data_type like 'NUMBER%'
843 THEN
844 x_Select_Table(l_count) := ', TO_NUMBER(NULL)';
845 ELSIF l_col_data_type like 'DATE%' THEN
846 x_Select_Table(l_count) := ', TO_DATE(NULL)';
847 ELSE
848 x_Select_Table(l_count) := ', TO_CHAR(NULL)';
849 end if;
850 ELSE
851 x_Select_Table(l_count) := ', TO_CHAR(NULL)';
852 end if;
853 ELSE
854 x_Select_Table(l_count) := ', ' || p_Table_Alias || '.'
855 || l_segments.APPLICATION_COLUMN_NAME(j);
856 END IF;
857 --EDW flag change
858 END IF;
859 bis_debug_pub.Add('x_Select_Table('|| l_count||') = ' || x_Select_Table(l_count));
860 --
861 l_count := l_count + 1;
862 END LOOP;
863 END IF;
864 bis_debug_pub.Add('< add_desc_flexfield_segments');
865 --
866 EXCEPTION
867 when FND_API.G_EXC_ERROR then
868 x_return_status := FND_API.G_RET_STS_ERROR ;
869 RAISE FND_API.G_EXC_ERROR;
870 when FND_API.G_EXC_UNEXPECTED_ERROR then
871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
873 when others then
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
875 BIS_VG_UTIL.Add_Error_Message
876 ( p_error_msg_id => SQLCODE
877 , p_error_description => SQLERRM
878 , p_error_proc_name => G_PKG_NAME||'.add_desc_flexfield_segments'
879 , p_error_table => x_error_tbl
880 , x_error_table => x_error_tbl
881 );
882 bis_vg_log.update_failure_log( x_error_tbl
883 , x_return_status
884 , x_error_Tbl
885 );
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887
888 END add_desc_flexfield_segments;
889 --
890 -- =============================================================================
891 -- PROCEDURE : update_Desc_Flex_Tables
892 -- PARAMETERS:
893 -- 1. p_EDW_Flag flag to add context column
894 -- 2. p_dummy_flag flag to indicate a flexfield
895 -- which is not valid in this branch
896 -- of a union, hence filled with
897 -- NULLs to keep number of columns.
898 -- 3. p_Prefix prefix for segments
899 -- 4. p_decode flag to indicate if select
900 -- statement should contain a decode
901 -- or always fetch data even when
902 -- meaningless
903 -- 5. p_column_table PLSQL table of columns to prune by
904 -- if present, else expand all.
905 -- 6. p_Application_Name Application Name
906 -- 7. p_Desc_Flex_Name Desc Flexfield Name
907 -- 8. p_Table_Alias Table alias
908 -- 9. x_Column_Table table of varchars to hold
909 -- view columns
910 -- 10. x_Select_Table table of varchars to hold select
911 -- clause of view text
912 -- 10. x_Select_Table table of varchars to hold select
913 -- clause of view text
914 -- 11. x_Column_Comment_Table table to hold flex info as it is gathered
915 -- 12. x_return_status error or normal (not used)
916 -- 13. x_error_Tbl table of error messages
917
918 -- COMMENT : Call this procedure to build the column and select tables
919 -- for desc flexfields.
920 -- ---
921 -- =============================================================================
922 PROCEDURE update_Desc_Flex_Tables
923 ( p_EDW_Flag IN BOOLEAN --EDW flag change
924 , p_DUMMY_Flag IN BOOLEAN --EDW flag change
925 , p_Prefix IN VARCHAR2
926 , p_decode IN BOOLEAN
927 , p_column_table IN BIS_VG_TYPES.flexfield_column_table_type
928 , p_Application_Name IN VARCHAR2
929 , p_Desc_Flex_Name IN VARCHAR2
930 , p_Table_Alias IN VARCHAR2
931 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
932 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
933 , x_Column_Comment_Table OUT bis_vg_types.Flex_Column_Comment_Table_Type
934 , x_return_status OUT VARCHAR2
935 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
936 )
937 IS
938 --
939 i NUMBER;
940 l_flexfield FND_DFLEX.DFLEX_R;
941 l_flexinfo FND_DFLEX.DFLEX_DR;
942 l_contexts FND_DFLEX.CONTEXTS_DR;
943 l_segments FND_DFLEX.SEGMENTS_DR;
944 --
945 l_ATT_CATEGORY_flag BOOLEAN := TRUE;
946 l_nContexts_flag BOOLEAN;
947 l_count NUMBER := 0;
948 l_Column_Table bis_vg_types.View_Text_Table_Type;
949 l_Select_Table bis_vg_types.View_Text_Table_Type;
950 l_Column_Comment_Table bis_vg_types.Flex_Column_Comment_Table_Type;
951 ---
952
956 l_schema varchar2(400);
953 --to get schema name
954 l_dummy1 varchar2(2000);
955 l_dummy2 varchar2(2000);
957
958 BEGIN
959 --- bis_debug_pub.debug_on;
960 bis_debug_pub.Add('> update_Desc_Flex_Tables');
961
962 --get schema name
963 if FND_INSTALLATION.GET_APP_INFO(p_Application_Name,l_dummy1, l_dummy2,l_schema) = false then
964 bis_debug_pub.Add('FND_INSTALLATION.GET_APP_INFO returned with error');
965 end if;
966
967 x_return_status := FND_API.G_RET_STS_SUCCESS;
968 FND_DFLEX.GET_FLEXFIELD( appl_short_name => p_Application_Name
969 , flexfield_name => p_Desc_Flex_Name
970 , flexfield => l_flexfield
971 , flexinfo => l_flexinfo
972 );
973 bis_debug_pub.Add('l_flexfield.FLEXFIELD_NAME = '
974 || l_flexfield.FLEXFIELD_NAME);
975 bis_debug_pub.Add('l_flexinfo.TITLE = ' || l_flexinfo.TITLE);
976 IF p_column_table IS NULL
977 --- regular BVG behavior (not pruned)
978 THEN
979 GET_CONTEXTS
980 ( flexfield => l_flexfield
981 , contexts => l_contexts
982 );
983 bis_debug_pub.Add('l_contexts.NCONTEXTS = ' || l_contexts.NCONTEXTS);
984 -- set l_nContexts_flag if only one context or only one enabled context
985 IF(l_contexts.NCONTEXTS = 1) THEN
986 l_nContexts_flag := TRUE;
987 ELSE
988 FOR i IN 1 .. l_contexts.NCONTEXTS LOOP
989 IF( l_contexts.IS_ENABLED(i) ) THEN
990 l_count := l_count + 1;
991 END IF;
992 END LOOP;
993 IF(l_count > 1) THEN
994 l_nContexts_flag := FALSE;
995 ELSE
996 if (p_edw_flag = TRUE ) THEN
997 l_nContexts_flag := FALSE;
998 else
999 l_nContexts_flag := TRUE;
1000 end if;
1001 END IF;
1002 END IF;
1003 --
1004 l_count:=1;
1005 FOR i IN 1 .. l_contexts.NCONTEXTS LOOP
1006 IF( l_contexts.IS_ENABLED(i) ) THEN
1007 --EDW flag change
1008 IF (p_EDW_Flag = TRUE)
1009 THEN
1010 l_count := i+1;
1011 ELSE
1012 l_count := i;
1013 END IF;
1014 --EDW flag change
1015 add_desc_flexfield_segments( l_nContexts_flag
1016 , l_flexfield
1017 , l_flexinfo
1018 , l_contexts.CONTEXT_CODE(i)
1019 , l_count
1020 , p_Prefix
1021 , p_decode
1022 , p_Table_Alias
1023 , p_EDW_flag
1024 , p_DUMMY_flag
1025 , l_Column_Table
1026 , l_Select_Table
1027 , l_Column_Comment_Table
1028 , l_ATT_CATEGORY_flag
1029 , l_ATT_CATEGORY_flag
1030 , x_return_status
1031 , x_error_Tbl
1032 ,l_schema --pass schema name
1033
1034 );
1035
1036 --- Append the latest context's segments to the list
1037 bis_vg_util.concatenate_Tables( x_Column_Table
1038 , l_Column_Table
1039 , x_Column_Table
1040 , x_return_status
1041 , x_error_Tbl
1042 );
1043 bis_vg_util.concatenate_Tables( x_Select_Table
1044 , l_Select_Table
1045 , x_Select_Table
1046 , x_return_status
1047 , x_error_Tbl
1048 );
1049 bis_vg_util.concatenate_Tables( x_Column_Comment_Table
1050 , l_Column_Comment_Table
1051 , x_Column_Comment_Table
1052 , x_return_status
1053 , x_error_Tbl
1054 );
1055 END IF; --- Context enabled
1056 END LOOP;
1057 ELSE
1058 --- The pruned case - no need for concatenated segments column.
1059 l_count := 1;
1060 i := p_column_table.first;
1061 WHILE i <= p_column_table.last
1062 LOOP
1063 IF ( p_column_table(i).flex_field_type = 'D'
1064 AND p_column_table(i).id_flex_code = p_desc_flex_name)
1065 THEN
1066 l_count := l_count+1;
1067 bis_debug_pub.ADD ('Processing p_column_table(i).segment_name = '
1068 || p_column_table(i).segment_name);
1069 bis_debug_pub.ADD ('and p_column_table(i).structure_num = '
1070 || p_column_table(i).structure_num);
1071 bis_debug_pub.ADD ('and p_column_table(i).application_column_name = '
1072 || p_column_table(i).application_column_name);
1073 x_column_table(l_count):= p_Prefix
1074 || '_' || p_column_table(i).segment_name;
1075
1076 IF (p_column_table(i).structure_num > 0)
1077 THEN
1078 x_column_table(l_count):= x_column_table(l_count)
1079 ||'^' ||p_column_table(i).structure_num;
1080 END IF;
1081
1082 IF p_dummy_flag OR p_column_table(i).application_column_name IS NULL
1083 THEN
1084 IF p_column_table(i).segment_datatype = 'N'
1085 THEN
1086 x_select_table(l_count) := ', TO_NUMBER(NULL)';
1087 ELSIF p_column_table(i).segment_datatype IN ('D','X')
1088 THEN
1089 x_select_table(l_count) := ', TO_DATE(NULL)';
1090 ELSE
1091 x_select_table(l_count) := ', TO_CHAR(NULL)';
1092 END IF;
1093 ELSE
1094 x_select_table(l_count)
1095 := ' , ' || p_Table_Alias || '.'
1096 || p_column_table(i).application_column_name;
1097 END IF; --- dummy_flag
1098
1099 END IF; --- p_column_table(i) matches criteria
1100 i := p_column_table.next(i);
1101 END LOOP; --- to enumerate p_column_table
1102
1103 IF l_count > 1
1104 THEN
1105 x_Column_Table(1) := p_prefix || '_context';
1106 bis_debug_pub.ADD ('l_Flexinfo.context_column_name = '
1107 || l_Flexinfo.context_column_name);
1108 IF p_dummy_flag OR l_Flexinfo.context_column_name IS NULL
1109 THEN
1113 || p_Table_Alias
1110 x_Select_Table(1) := 'NULL';
1111 ELSE
1112 x_Select_Table(1) := ' '
1114 || '.' || l_Flexinfo.context_column_name;
1115 END IF;
1116
1117 END IF;
1118
1119 END IF; --- Prune or no prune
1120 --- DEBUG
1121 bis_vg_util.print_View_Text
1122 ( x_Column_Table
1123 , x_return_status
1124 , x_error_Tbl
1125 );
1126 bis_debug_pub.Add('< update_Desc_Flex_Tables');
1127 --- bis_debug_pub.debug_off;
1128 --
1129
1130 EXCEPTION
1131 when FND_API.G_EXC_ERROR then
1132 x_return_status := FND_API.G_RET_STS_ERROR ;
1133 RAISE FND_API.G_EXC_ERROR;
1134 when FND_API.G_EXC_UNEXPECTED_ERROR then
1135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1137 when others then
1138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1139 BIS_VG_UTIL.Add_Error_Message
1140 ( p_error_msg_id => SQLCODE
1141 , p_error_description => SQLERRM
1142 , p_error_proc_name => G_PKG_NAME||'.update_Desc_Flex_Tables'
1143 , p_error_table => x_error_tbl
1144 , x_error_table => x_error_tbl
1145 );
1146 bis_vg_log.update_failure_log( x_error_tbl
1147 , x_return_status
1148 , x_error_Tbl
1149 );
1150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1151
1152
1153 END update_Desc_Flex_Tables;
1154 --
1155 --- ============================================================================
1156 --- PROCEDURE : add_Desc_Flex_Info
1157 --- PARAMETERS:
1158 --- 1. p_View_Column_Table table of varchars to hold columns of
1159 --- view text
1160 --- 2. p_View_Select_Table table of varchars to hold select clause
1161 --- of view
1162 --- 3. p_Mode mode of execution of the program
1163 --- 4. p_column_table List of columns for calls from generate_pruned_view
1164 --- 5. p_Column_Pointer pointer to the desc flex column in
1165 --- column table
1166 --- 6. p_Select_Pointer pointer to the select clause
1167 --- 6. p_From_Pointer pointer to the corresponding from clause
1168 --- 8. x_Column_Table table of varchars to hold additional
1169 --- columns
1170 --- 9. x_Select_Table table of varchars to hold additional
1171 --- columns
1172 --- 10 x_Column_Comment_Table table to hold info info as it is gathered.
1173 --- 11. x_Column_Pointer pointer to the character after the
1174 --- delimiter
1175 --- (column table)
1176 --- 12. x_Select_Pointer pointer to the character after the
1177 --- delimiter
1178 --- (select table)
1179 --- 14. x_return_status error or normal
1180 --- 15. x_error_Tbl table of error messages
1181 ---
1182 --- COMMENT : Call this procedure to add a particular desc flexfield
1183 --- information to a view.
1184 --- ---
1185 --- ==========================================================================
1186
1187 PROCEDURE add_Desc_Flex_Info
1188 ( p_View_Column_Table IN BIS_VG_TYPES.View_Text_Table_Type
1189 , p_View_Select_Table IN BIS_VG_TYPES.View_Text_Table_Type
1190 , p_Mode IN NUMBER
1191 , p_column_table IN BIS_VG_TYPES.flexfield_column_table_type
1192 , p_Column_Pointer IN BIS_VG_TYPES.View_Character_Pointer_Type
1193 , p_Select_Pointer IN bis_vg_types.View_Character_Pointer_Type
1194 , p_From_Pointer IN bis_vg_types.View_Character_Pointer_Type
1195 , x_Column_Table OUT bis_vg_types.View_Text_Table_Type
1196 , x_Select_Table OUT bis_vg_types.View_Text_Table_Type
1197 , x_Column_Comment_Table OUT BIS_VG_TYPES.Flex_Column_Comment_Table_Type
1198 , x_Column_Pointer OUT bis_vg_types.View_Character_Pointer_Type
1199 , x_Select_Pointer OUT bis_vg_types.View_Character_Pointer_Type
1200 , x_return_status OUT VARCHAR2
1201 , x_error_Tbl OUT BIS_VG_UTIL.Error_Tbl_Type
1202 )
1203 IS
1204 --
1205 l_Prefix VARCHAR2(100);
1206 --
1207 l_Application_Name VARCHAR2(10);
1208 l_Desc_Flex_Name VARCHAR2(100);
1209 l_Table_Alias VARCHAR2(100);
1210 l_Table_Name VARCHAR2(100);
1211 --
1212 l_decode BOOLEAN;
1213 l_EDW_Flag BOOLEAN; --EDW flag change
1214 l_DUMMY_Flag BOOLEAN; --EDW flag change
1215 BEGIN
1216 bis_debug_pub.Add('> add_Desc_Flex_Info');
1217 x_return_status := FND_API.G_RET_STS_SUCCESS;
1218
1219 parse_DF_Column_Line( p_View_Column_Table
1220 , p_Column_Pointer
1221 , x_Column_Pointer
1222 , l_EDW_Flag --EDW flag change
1223 , l_Prefix
1224 , l_decode
1225 , x_return_status
1226 , x_error_Tbl
1227 );
1228
1229 bis_debug_pub.Add('l_Prefix = ' || l_Prefix);
1230
1231 --- This clause catches flexfield tags that do not have the
1232 --- _EDW tags when the generator is called via generate_pruned_view
1233 IF (p_column_table IS NOT NULL AND l_edw_flag = FALSE )
1234 THEN
1235 RAISE bis_view_generator_pvt.CANNOT_PRUNE_NON_EDW_VIEW;
1236 END IF;
1237
1238
1239 parse_DF_Select_Line( p_View_Select_Table
1240 , p_Select_Pointer
1241 , x_Select_Pointer
1242 , l_Application_Name
1246 , x_return_status
1243 , l_Desc_Flex_Name
1244 , l_Table_Alias
1245 , l_DUMMY_Flag --EDW flag change
1247 , x_error_Tbl
1248 );
1249 bis_debug_pub.Add('l_Application_Name = ' || l_Application_Name);
1250 bis_debug_pub.Add('l_Desc_Flex_Name = ' || l_Desc_Flex_Name);
1251 bis_debug_pub.Add('l_Table_Alias = ' || l_Table_Alias);
1252 bis_debug_pub.Add('l_Prefix = ' || l_Prefix);
1253
1254 IF(
1255 (p_Mode <> bis_vg_types.remove_tags_mode)
1256 AND
1257 (p_column_table IS NULL
1258 OR
1259 p_column_table.COUNT > 0)
1260 )
1261
1262 THEN
1263 --- x_Column_Table(1) := 'DESCRIPTIVE_FLEXFIELD_COLUMN';
1264 --- x_Select_Table(1) := 'TO_CHAR(NULL)';
1265 --- ELSE
1266
1267 update_Desc_Flex_Tables( l_EDW_Flag --EDW flag change
1268 , l_DUMMY_Flag --EDW flag change
1269 , l_Prefix
1270 , l_decode
1271 , p_column_table
1272 , l_Application_Name
1273 , l_Desc_Flex_Name
1274 , l_Table_Alias
1275 , x_Column_Table
1276 , x_Select_Table
1277 , x_Column_Comment_Table
1278 , x_return_status
1279 , x_error_Tbl
1280 );
1281 END IF;
1282 bis_vg_util.print_View_Text(x_Column_Table, x_return_status, x_error_Tbl);
1283 bis_vg_util.print_View_Text(x_Select_Table, x_return_status, x_error_Tbl);
1284 bis_debug_pub.Add('COLUMN POINTER');
1285 bis_vg_util.print_View_Pointer ( x_Column_Pointer
1286 , x_return_status
1287 , x_error_Tbl
1288 );
1289 bis_debug_pub.Add('SELECT POINTER');
1290 bis_vg_util.print_View_Pointer ( x_Select_Pointer
1291 , x_return_status
1292 , x_error_Tbl
1293 );
1294 bis_debug_pub.Add('< add_Desc_Flex_Info');
1295
1296 --
1297
1298
1299 EXCEPTION
1300 when bis_view_generator_pvt.cannot_prune_non_edw_view THEN
1301 RAISE; -- same exception
1302 when FND_API.G_EXC_ERROR then
1303 x_return_status := FND_API.G_RET_STS_ERROR ;
1304 RAISE; -- same exception
1305 when FND_API.G_EXC_UNEXPECTED_ERROR then
1306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1307 RAISE; -- same exception
1308 when others then
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1310 BIS_VG_UTIL.Add_Error_Message
1311 ( p_error_msg_id => SQLCODE
1312 , p_error_description => SQLERRM
1313 , p_error_proc_name => G_PKG_NAME||'.add_Desc_Flex_Info'
1314 , p_error_table => x_error_tbl
1315 , x_error_table => x_error_tbl
1316 );
1317 bis_vg_log.update_failure_log( x_error_tbl
1318 , x_return_status
1319 , x_error_Tbl
1320 );
1321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1322
1323
1324 END add_Desc_Flex_Info;
1325 --
1326 --
1327 END bis_vg_desc_flex;