[Home] [Help]
PACKAGE BODY: APPS.EDW_GEN_VIEW
Source
1 Package Body EDW_GEN_VIEW AS
2 /* $Header: EDWVGENB.pls 115.41 2003/11/06 11:48:14 smulye ship $ */
3 ---+==========================================================================+
4 ---| Copyright (c) 1995 Oracle Corporation Belmont, California, USA |
5 ---| All rights reserved |
6 ---+==========================================================================+
7 ---| |
8 ---| FILENAME |
9 ---| EDWVGENB.pls |
10 ---| |
11 ---| DESCRIPTION |
12 ---| |
13 ---| PUBLIC PROCEDURES |
14 ---| writeLog |
15 ---| writeOut |
16 ---| writeOutLine |
17 ---| buildViewStmt |
18 ---| createView |
19 ---| createLongView |
20 ---| getColumnMappings |
21 ---| update_generation_status |
22 ---| |
23 ---| PUBLIC FUNCTIONS |
24 ---| |
25 ---| PRIVATE PROCEDURES |
26 ---| |
27 ---| PRIVATE FUNCTIONS |
28 ---| |
29 ---| HISTORY |
30 ---| 15-01-2000 Walid.Nasrallah started logging changes per management |
31 ---| directive. Removed restriction to 11i version to allow |
32 ---| back-porting. |
33 ---| |
34 ---| 16-sep-03 smulye |
35 ---| Changed API getUtlFileDir to use alias for view v$parameter |
36 ---| Bug 2860354 |
37 ---| 06-Nov-03 smulye |
38 ---| Changed API checkWhereClause to handle the situation when where |
39 ---| cluase is null. |
40 ---| Bug 3194751 |
41 ---+==========================================================================*
42
43 cid number;
44
45 newline varchar2(10) := '
46 ';
47 g_generated_view_name varchar2(40) := null;
48 g_log boolean := false;
49
50
51 Procedure indentBegin IS
52
53 BEGIN
54
55 g_indenting := g_indenting || g_spacing;
56
57
58 END;
59
60 Procedure indentEnd IS
61
62 BEGIN
63 g_indenting := substr(g_indenting, 0, length(g_indenting)-length(g_spacing)) ;
64 END;
65
66
67 /*---------------------------------------------------------------------
68 Get the actual schema name for the 'APPS' schema as it could be different
69 in different implementations.
70 Have asked infoad if its ok to hardcode 900...
71
72 ---------------------------------------------------------------------*/
73
74
75 Function getAppsSchema RETURN VARCHAR2 IS
76 l_schema varchar2(100);
77 stmt varchar2(1000);
78 l_count number;
79 l_dummy integer;
80 l_cid number;
81 BEGIN
82 IF (g_log) THEN
83 indentBegin;
84 writelog('Inside getAppsSchema for dblink:'||g_source_db_link);
85 END IF;
86
87
88 stmt := 'SELECT ORACLE_USERNAME from fnd_oracle_userid@'||g_source_db_link|| ' where oracle_id=900';
89
90 IF (g_log) THEN
91 writelog('Executing following SQL to get the APPS schema :');
92 writelog(stmt);
93 END IF;
94
95 cid := DBMS_SQL.open_cursor;
96
97 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
98 DBMS_SQL.DEFINE_COLUMN(cid, 1, l_schema, 100);
99
100 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
101
102 DBMS_SQL.COLUMN_VALUE(cid, 1, l_schema);
103 DBMS_SQL.close_cursor(cid);
104
105
106 IF (g_log) THEN
107 writelog('Completed getAppsSchema, returning:'||l_schema);
108 writelog('');
109 indentEnd;
110 END IF;
111
112 return l_schema;
113
114 END;
115
116 /*---------------------------------------------------------------------
117
118 In case the log directory isnt passed and the EDW_LOGFILE_DIR profile
119 option has also not been set, where do I write the log file ?
120
121 Doing so by parsing the 'utl_file_dir' init.ora parameter and scanning
122 for the word log and getting that string out.
123
124 ---------------------------------------------------------------------*/
125
126
127 Function getUtlFileDir return VARCHAR2 IS
128 l_dir VARCHAR2(1000);
129 l_utl_dir VARCHAR2(100);
130 l_count NUMBER := 0;
131 l_log_begin NUMBER := 0;
132 l_log_end NUMBER := 0;
133 l_comma_pos NUMBER := 0;
134 stmt VARCHAR2(200);
135 cid NUMBER;
136 l_dummy NUMBER;
137
138 BEGIN
139 SELECT param.value into l_dir
140 FROM v$parameter param where upper(param.name) = 'UTL_FILE_DIR';
141
142 l_log_begin := INSTR(l_dir, '/log');
143
144 IF (l_log_begin = 0) THEN /* then get the first string */
145 l_utl_dir := substr(l_dir, 1, INSTR(l_dir, ',') - 1);
146 return l_utl_dir;
147 END IF;
148 l_log_end := INSTR(l_dir, ',', l_log_begin) - 1;
149 IF (l_log_end <= 0) THEN
150 l_log_end := length(l_dir);
151 END IF;
152
153 --have now determined the first occurrence of '/log' and the end pos
154 -- now to determine the start position of the log directory
155
156 l_dir := substr(l_dir, 0, l_log_end);
157
158 LOOP
159 l_comma_pos := INSTR(l_dir, ',', l_comma_pos+1);
160 IF (l_comma_pos <> 0) THEN
161 l_count := l_comma_pos + 1;
162 END IF;
163
164 EXIT WHEN l_comma_pos = 0;
165 END LOOP;
166 l_utl_dir := substr(l_dir, l_count+1, l_log_end);
167
168
169 RETURN l_utl_dir;
170
171 END;
172
173 /*---------------------------------------------------------------------
174
175 Used to be checking for upper(characters) to be between 'A' to 'Z'
176 or 0 to 9 but thats not NLS compliant. So following exact BIS
177 view gen logic in generating column names.
178
179 ---------------------------------------------------------------------*/
180
181 Function formSegmentName(p_prefix IN VARCHAR2,
182 p_segment_name IN VARCHAR2,
183 p_struct_num IN NUMBER,
184 p_Id_Flex_Code IN VARCHAR2,
185 p_flex_type IN VARCHAR2) RETURN VARCHAR2 IS
186
187 l_newstring varchar2(100);
188 l_length number := 0;
189 l_string varchar2(100);
190 l_trunc_segment varchar2(30);
191 v_segment_list DBMS_SQL.VARCHAR2_TABLE;
192
193 CURSOR C_Key_Segment_List(p_id_flex_code VARCHAR2, p_struct_num NUMBER, p_trunc_segment VARCHAR2) IS
194 select segment_name
195 from fnd_id_flex_segments_vl
196 where upper(id_flex_code) = p_id_flex_code
197 and id_flex_num = p_struct_num
198 and upper(segment_name) like p_trunc_segment
199 order by creation_date;
200
201 l_orig_length NUMBER := 0;
202
203 l_segment_name VARCHAR2(100);
204 l_count INTEGER :=0;
205 l_list_count INTEGER :=0;
206 l_last_index Integer := 0;
207 l_seg_length Integer :=0;
208
209 CURSOR C_Application_Id(p_obj_name VARCHAR2) IS
210 select distinct application_id
211 from edw_attribute_mappings attr, edw_flex_attribute_mappings flex
212 where attr.attr_mapping_pk = flex.attr_mapping_fk
213 and object_short_name = p_obj_name;
214
215 CURSOR C_Context_Code(p_id_flex_code VARCHAR2, p_application_id NUMBER, p_struct_num NUMBER) IS
216 select descriptive_flex_context_code
217 FROM
218 (select descriptive_flex_context_code
219 from fnd_descr_flex_contexts_vl
220 where upper(descriptive_flexfield_name) = p_id_flex_code
221 and application_id = p_application_id
222 order by creation_date )
223 where rownum < p_struct_num
224 order by rownum desc;
225
226 CURSOR C_Desc_Segment_List(p_id_flex_code VARCHAR2, p_context_code VARCHAR2, p_trunc_segment VARCHAR2) IS
227 select end_user_column_name
228 from fnd_descr_flex_col_usage_vl
229 where descriptive_flexfield_name = p_id_flex_code
230 and descriptive_flex_context_code = p_context_code
231 and end_user_column_name like p_trunc_segment
232 order by creation_date;
233
234 l_application_id NUMBER;
235 l_context_code VARCHAR2(100);
236 BEGIN
237
238 IF (g_log) THEN
239 indentBegin;
240
241 writelog('Inside formSegmentName');
242 writelog(' Parameter p_prefix:'||p_prefix);
243 writelog(' Parameter p_segment_name:'||p_segment_name);
244 writelog(' Parameter p_struct_num:'||p_struct_num);
245 writelog(' Parameter p_Id_Flex_Code:'||p_Id_Flex_Code);
246 writelog(' Parameter p_flex_type:'||p_flex_type);
247 END IF;
248
249
250 l_string := convertString(p_segment_name);
251 l_length := length(l_string);
252
253 IF (g_log) THEN
254 writelog('l_length is:'||l_length);
255 END IF;
256
257 IF (p_segment_name is null) THEN
258 return null;
259 END IF;
260
261 l_newstring := l_string;
262
263 IF (g_log) THEN
264 writelog('l_newstring is:'||l_newstring);
265 END IF;
266
267 IF (p_struct_num <> -1) then
268
269
270 /* this is done because we store -1 if there is only 1 context
271 in the df if there is only 1 context in the desc. flex, then it
272 does not appenda suffix to the generated column */
273
274 l_length := length(l_newstring)+ length(p_prefix)+1 +length(p_struct_num) + 1;
275
276 ELSE
277 l_length := length(l_newstring)+ length(p_prefix)+1 ;
278 END IF;
279
280 IF (g_log) THEN
281 writelog('l_length is:'||l_length);
282 END IF;
283
284 l_newstring := p_prefix||'_'||l_newstring;
285 l_orig_length := l_length;
286
287
288 -- need truncation
289 IF (l_length > 30 ) then
290
291 IF( g_log) THEN
292 writelog('length is greater than 30 : '||l_length);
293 END IF;
294 IF (p_struct_num <> -1) THEN
295 l_length := 30-length(p_prefix)-1-length(p_struct_num)-1;
296 l_trunc_segment := substr(l_string, 0, l_length);
297 else
298 l_length := 30-length(p_prefix)-1;
299 l_trunc_segment := substr(l_string, 0, l_length);
300 end if;
301
302 IF (g_log) THEN
303 writelog('l_length = '||l_length || ', l_trunc_segment = '|| l_trunc_segment);
304 END IF;
305
306 -- we need to consider accounting flex field as well (bug 2245373)
307 IF (p_flex_type = 'K' OR p_flex_type = 'A') then
308 -- get list of segments for key flex fields
309 OPEN C_Key_Segment_List(p_id_flex_code,
310 p_struct_num, substr(p_segment_name, 0, l_length)||'%');
311 l_count := 0;
312 loop
313 fetch C_key_Segment_List into l_segment_name;
314 exit when C_Key_Segment_List%NOTFOUND;
315 l_count := l_count + 1;
316 v_segment_list (l_count) := l_segment_name;
317 end loop;
318 CLOSE C_key_Segment_List;
319 l_list_count := l_count;
320 ELSE -- descriptive flex field
321 -- get list of segments for desc flex fields
322
323 OPEN C_Application_Id(g_obj_name);
324 FETCH C_Application_Id INTO l_application_id;
325
326
327 if C_Application_Id%FOUND then
328 OPEN C_Context_Code(p_id_flex_code, l_application_id, p_struct_num);
329 FETCH C_Context_Code INTO l_context_code;
330
331
332 IF C_Context_Code%FOUND then
333 OPEN C_Desc_Segment_List(p_id_flex_code, l_context_code, substr(p_segment_name, 0, l_length)||'%');
334 l_count := 0;
335 loop
336 fetch C_Desc_Segment_List into l_segment_name;
337 exit when C_Desc_Segment_List%NOTFOUND;
338 l_count := l_count + 1;
339 v_segment_list (l_count) := convertString(l_segment_name);
340
341 end loop;
342 CLOSE C_Desc_Segment_List;
343 l_list_count := l_count;
344 END IF; -- C_Context_Code%FOUND
345 CLOSE C_Context_Code;
346 END IF; -- C_Application_Id%FOUND
347 CLOSE C_Application_Id;
348 end if;
349
350 -- construct segment name
351
352 if (l_list_count = 1) then
353 -- no naming clashes.
354
355 IF (p_struct_num <> -1) THEN
356 l_newstring := p_prefix || '_' || l_trunc_segment ||'_'||p_struct_num;
357 else
358 l_newstring := p_prefix || '_' || l_trunc_segment;
359 end if;
360
361 elsif (l_list_count > 1) then -- naming clashes
362 l_last_index := 0;
363 l_count := 0;
364 loop
365 l_count := l_count + 1;
366
367 if (l_count = 1) then
368
369 IF (p_struct_num <> -1) THEN
370 l_newstring := p_prefix || '_' || l_trunc_segment ||'_'||p_struct_num;
371 else
372 l_newstring := p_prefix || '_' || l_trunc_segment;
373 end if;
374
375 else
376
377 if (l_last_index >= 0 and l_last_index <= 8) then
378 l_last_index := l_last_index + 1;
379 l_seg_length := l_length - 2;
380 l_trunc_segment := substr(l_string, 0, l_seg_length);
381 IF (p_struct_num <> -1) THEN
382 l_newstring := p_prefix || '_' || l_trunc_segment ||'_'||p_struct_num || '_' || l_last_index;
383 else
384 l_newstring := p_prefix || '_' || l_trunc_segment || '_' || l_last_index;
385 end if;
386
387 else
388
389 l_last_index := l_last_index + 1;
390 l_seg_length := l_length - 3;
391 l_trunc_segment := substr(l_string, 0, l_seg_length);
392 IF (p_struct_num <> -1) THEN
393 l_newstring := p_prefix || '_' || l_trunc_segment ||'_'||p_struct_num || '_' || l_last_index;
394 else
395 l_newstring := p_prefix || '_' || l_trunc_segment || '_' || l_last_index;
396 end if;
397
398 end if; -- if l_last_index
399
400
401 end if; -- if l_count
402
403 exit when (v_segment_list(l_count) = l_string);
404 end loop;
405 else
406 IF (p_struct_num <> -1) THEN
407 l_newstring := p_prefix || '_' || l_trunc_segment ||'_'||p_struct_num ;
408 else
409 l_newstring := p_prefix || '_' || l_trunc_segment;
410 end if;
411
412
413 end if; -- if l_list_count
414
415 END if; -- if (l_length>30)
416
417
418
419 IF (l_orig_length <= 30 ) THEN
420 -- for bug 2270960 (when p_struct_num = -1, should not append it to the name)
421 if (p_struct_num <> -1) then
422 l_newstring := l_newstring ||'_'||p_struct_num;
423 end if;
424 END IF;
425
426
427
428
429 IF (g_log) THEN
430 writelog(g_spacing||'Segment Name formed is :'|| l_newstring);
431
432 writelog('Completed formSegmentName, returning:'||l_newstring);
433 indentEnd;
434 END IF;
435
436
437 return l_newstring;
438
439
440 END;
441
442 /*---------------------------------------------------------------------
443
444 Write to the log file using utl_file. Write only if the logging
445 flag is true.
446
447 ---------------------------------------------------------------------*/
448
449
450
451 Procedure writeLog(p_message IN VARCHAR2) IS
452 BEGIN
453 IF (g_log) THEN
454 IF (p_message like 'Inside%') THEN
455 utl_file.put_line(l_file, newline);
456 utl_file.put_line(l_file, g_indenting||p_message);
457 ELSIF ( p_message like 'Completed%') THEN
458 utl_file.put_line(l_file, g_indenting||p_message);
459 utl_file.put_line(l_file, newline);
460 ELSE
461 utl_file.put_line(l_file, g_indenting||' '||p_message);
462 END IF;
463 ELSE
464 return;
465 END IF;
466 END;
467
468 Procedure writeOut(p_message IN VARCHAR2) IS
469 BEGIN
470 IF (g_log) THEN
471 utl_file.put(l_out_file, p_message);
472 ELSE
473 return;
474 END IF;
475 END;
476
477 Procedure writeOutLine(p_message IN VARCHAR2) IS
478 BEGIN
479 IF (g_log) THEN
480 utl_file.put_line(l_out_file, p_message);
481 ELSE
482 return;
483 END IF;
484 END;
485
486 /*---------------------------------------------------------------------
487
488 Get the # of columns given a view name. This will query for
489 the owner APPS as the APPS and APPS_MRC schema may sometimes
490 be out of sync and may return extra columns which are
491 in APPS_MRC but not in APPS.
492
493 ---------------------------------------------------------------------*/
494
495
496 Function getColumnCountForView(view_name in varchar2) RETURN INTEGER IS
497 stmt varchar2(1000);
498 l_count number;
499 l_dummy integer;
500 BEGIN
501 IF (g_log) THEN
502 indentBegin;
503 writelog('Inside getColumnCountForView for :'||view_name);
504 END IF;
505
506
507 stmt := ' SELECT count(distinct(column_name)) FROM all_tab_columns@'||g_source_db_link;
508 stmt := stmt||' WHERE table_name = :view_name AND owner = :owner';
509 cid := DBMS_SQL.open_cursor;
510 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
511
512 DBMS_SQL.BIND_VARIABLE(cid, ':view_name', upper(view_name), 50);
513 DBMS_SQL.BIND_VARIABLE(cid, ':owner', g_apps_schema, 50);
514
515 DBMS_SQL.DEFINE_COLUMN(cid, 1, l_count);
516 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
517 DBMS_SQL.COLUMN_VALUE(cid, 1, l_count);
518 DBMS_SQL.close_cursor(cid);
519
520 IF (g_log) THEN
521 writelog('Completed getColumnCountForView, Column count is : '||l_count);
522 indentEnd;
523
524 END IF;
525 RETURN l_count;
526 END;
527
528 function getApplsysSchema return varchar2 is
529 dummy1 VARCHAR2(32) := null;
530 dummy2 VARCHAR2(32) := null;
531 l_applsys_schema VARCHAR2(32) := null;
532 apiClause varchar2(1000) ;
533 l_dummy number;
534 begin
535 apiClause := 'BEGIN
536 IF (FND_INSTALLATION.GET_APP_INFO@'||edw_gen_view.g_source_db_link||'(''FND'', :dummy1, :dummy2, :l_applsys_schema)) THEN NULL; END IF; END;';
537 cid := DBMS_SQL.OPEN_CURSOR;
538 DBMS_SQL.PARSE(cid, apiClause, DBMS_SQL.NATIVE);
539 DBMS_SQL.BIND_VARIABLE(cid, ':dummy1', dummy1, 32);
540 DBMS_SQL.BIND_VARIABLE(cid, ':dummy2', dummy2, 32);
541 DBMS_SQL.BIND_VARIABLE(cid, ':l_applsys_schema', l_applsys_schema, 32);
542
543 l_dummy := DBMS_SQL.EXECUTE(cid);
544 DBMS_SQL.VARIABLE_VALUE(cid, ':l_applsys_schema', l_applsys_schema);
545 DBMS_SQL.CLOSE_CURSOR(cid);
546 return l_applsys_schema;
547 exception
548 when others then
549 if g_log then
550 writelog('Error : inside getApplsysSchema');
551 END IF;
552
553 end getApplsysSchema;
554
555 Procedure BuildViewStmt(p_view_text in varchar2, p_line_num in number) is
556 begin
557
558 execute IMMEDIATE
559 'begin ad_ddl.build_statement@'||g_source_db_link||' (:s1,:s2) ; end; '
560 using p_view_text,p_line_num;
561
562 exception
563 when others then
564 if g_log then
565 writelog('Error : Inside BuildView_stmt');
566 writelog('view text is : '|| p_view_text ||'. Line number is : '|| p_line_num);
567 END IF;
568 end;
569
570
571 FUNCTION getViewStatus(view_name IN VARCHAR2) return VARCHAR2 IS
572 status VARCHAR2(4000);
573 TYPE CurTyp IS REF CURSOR;
574 cv CurTyp;
575 stmt varchar2(1000);
576 l_schema varchar2(100);
577 BEGIN
578 IF (g_log) THEN
579 writelog('Inside getViewStatus for '||view_name);
580 END IF;
581
582 status := null;
583 l_schema := getAppsSchema;
584 stmt := 'SELECT text FROM all_errors@'||g_source_db_link||' WHERE NAME = :s1 and type = :s2 and owner = :s3';
585 open cv for stmt using upper(view_name), 'VIEW', l_schema;
586 fetch cv into status;
587 close cv;
588
589 IF (g_log) THEN
590 writelog('stmt is');
591 writelog(stmt);
592 END IF;
593
594 IF (status is null) THEN
595 status := 'VALID';
596 END IF;
597 IF (g_log) THEN
598 writelog('Status is : '||status);
599 writelog('Completed getViewStatus');
600 indentEnd;
601 END IF;
602 return status;
603 END;
604
605 /*---------------------------------------------------------------------
606
607 Given a source view, deploy this on the Apps Schema on the
608 remote database thru the db link g_source_db_link.
609
610 ---------------------------------------------------------------------*/
611
612
613 PROCEDURE createView(src_view IN VARCHAR2, view_name IN VARCHAR2) IS
614 apiClause VARCHAR2(3000) := null;
615 l_applsys_schema VARCHAR2(32) := null;
616 l_dummy NUMBER := 0;
617 status VARCHAR2(4000);
618 BEGIN
619 IF (g_log) THEN
620
621 indentBegin;
622 writelog('Inside createView ');
623 writelog('View Text is : ');
624 writelog(src_view);
625 writelog(newline);
626 END IF;
627
628 l_applsys_schema:= getApplsysSchema;
629
630 IF (g_log) THEN
631 writelog('l_applsys_schema is : '|| l_applsys_schema);
632 writelog('Calling ad_ddl.do_ddl to deploy view on the source');
633 END IF;
634
635 apiClause := 'BEGIN
636 ad_ddl.do_ddl@'||g_source_db_link;
637 apiClause := apiClause ||'(APPLSYS_SCHEMA => '''||l_applsys_schema||'''';
638 apiClause := apiClause|| ', APPLICATION_SHORT_NAME => ''FND''';
639 apiClause := apiClause||newline||', STATEMENT_TYPE => 2, STATEMENT => :source_view ';
640 apiClause := apiClause||' , OBJECT_NAME => '''||view_name||'''); END;';
641
642 cid := DBMS_SQL.open_cursor;
643 dbms_sql.parse(cid, apiClause, dbms_sql.native);
644 DBMS_SQL.BIND_VARIABLE(cid, ':source_view', src_view, 32760);
645 l_dummy := DBMS_SQL.EXECUTE(cid);
646 DBMS_SQL.close_cursor(cid);
647
648 IF (g_log) THEN
649 writelog('Going to check view status of deployed view');
650 END IF;
651
652 status := getViewStatus(view_name);
653 IF (status <> 'VALID') THEN
654
655 IF (g_log) THEN
656 writelog(newline||newline);
657 writelog('View creation Failed...');
658 writelog('Error is '||status||newline);
659 END IF;
660 g_error := status;
661 raise viewgen_exception;
662
663 ELSE
664 IF (g_log) THEN
665 writelog(newline);
666 writelog('View status is valid.');
667 END IF;
668 END IF;
669
670 IF (g_log) THEN
671 writelog(g_spacing||'View created!');
672
673 writelog(g_spacing||'Completed createView');
674 indentEnd;
675
676
677 END IF;
678 END;
679
680 PROCEDURE createLongView(view_name IN VARCHAR2, p_first_line_num IN NUMBER, p_last_line_num IN NUMBER) IS
681 apiClause VARCHAR2(3000) := null;
682 l_applsys_schema VARCHAR2(32) := null;
683 l_dummy NUMBER := 0;
684 status VARCHAR2(4000) := null;
685 BEGIN
686 IF (g_log) THEN
687
688 indentBegin;
689 writelog(newline||newline);
690 writelog('Inside create Long View '||newline);
691 END IF;
692
693 l_applsys_schema:= getApplsysSchema;
694
695 IF (g_log) THEN
696 writelog(g_spacing||'l_applsys_schema is : '|| l_applsys_schema);
697 writelog(g_spacing||'Calling ad_ddl.do_ddl to deploy view on the source');
698
699 END IF;
700
701 apiClause := 'BEGIN
702 ad_ddl.do_array_ddl@'||g_source_db_link;
703 apiClause := apiClause ||'(APPLSYS_SCHEMA => '''||l_applsys_schema||'''';
704 apiClause := apiClause|| ', APPLICATION_SHORT_NAME => ''FND''';
705 apiClause := apiClause||newline||', STATEMENT_TYPE => 2, lb => :first_line , ub=>:last_line';
706 apiClause := apiClause||' , OBJECT_NAME => :object_name); END;';
707
708 cid := DBMS_SQL.open_cursor;
709 dbms_sql.parse(cid, apiClause, dbms_sql.native);
710 DBMS_SQL.BIND_VARIABLE(cid, ':first_line', p_first_line_num);
711 DBMS_SQL.BIND_VARIABLE(cid, ':last_line', p_last_line_num);
712 DBMS_SQL.BIND_VARIABLE(cid, ':object_name',view_name ,30);
713
714 l_dummy := DBMS_SQL.EXECUTE(cid);
715 DBMS_SQL.close_cursor(cid);
716
717 status := getViewStatus(view_name);
718 IF (status <> 'VALID') THEN
719
720 IF (g_log) THEN
721 writelog(newline||newline||'View creation Failed...');
722 writelog(newline||'Error is '||status||newline);
723 END IF;
724 g_error := status;
725 raise viewgen_exception;
726
727 ELSE
728 IF (g_log) THEN
729 writelog(newline||newline||'View status is valid.');
730 END IF;
731
732 END IF;
733
734
735
736 IF (g_log) THEN
737 writelog(g_spacing||'View created.');
738 writelog(g_spacing||'Completed createLongView');
739 indentEnd;
740
741 END IF;
742
743 END;
744
745 /* ------------------------------------------------------------------------
746
747 Create the column map vectors for a given object. Will split it into
748 counts for the following :
749
750 1. Simple Attribute Mappings
751 2. Multiple Attributes mapped to a single target column (OPI req.)
752 3. Attributes mapped to Flexfields
753 4. Foreign Keys to Flexfield dimensions
754
755 ------------------------------------------------------------------------ */
756
757 PROCEDURE getColumnMaps(object_name IN VARCHAR2, attMaps OUT NOCOPY tab_att_maps, multiAttList OUT NOCOPY tab_multi_att_list, flexMaps OUT NOCOPY tab_flex_att_maps, fkMaps OUT NOCOPY tab_fact_flex_fk_maps, p_level IN VARCHAR2 default null) IS
758
759 colList varchar2(500);
760 temp varchar2(50);
761 flex c_getFlexAttributeMappings%ROWTYPE;
762 att c_getAttributeMappings%ROWTYPE;
763 multiatt c_getMultiAttributeList%ROWTYPE;
764 fk c_getFactFlexFKMaps%ROWTYPE;
765 l_count number;
766
767 BEGIN
768 /* access edw_attribute mappings and get the list */
769
770 IF (g_log) THEN
771
772 indentBegin;
773 writelog(newline||newline);
774 writelog('Inside getColumnMaps');
775 END IF;
776
777 /* -------------------------------------------------
778
779 Get the straight attribute mappings
780
781 ----------------------------------------------------*/
782
783
784 l_count := 1;
785 IF (g_log) THEN
786 writelog(g_spacing||'Opening cursor c_getAttributeMappings with object_name = '||
787 object_name||', g_instance = '||g_instance||', p_level = '||p_level);
788 END IF;
789
790
791 open c_getAttributeMappings(object_name, g_instance, p_level);
792 LOOP
793 FETCH c_getAttributeMappings INTO att;
794 EXIT WHEN c_getAttributeMappings%NOTFOUND;
795 IF (g_log) THEN
796 writelog(g_spacing||'Target attribute: '||att.attribute_name ||
797 ' <= Source Attribute: '||att.source_attribute);
798 END IF;
799
800 attMaps(l_count).attribute_name := att.attribute_name;
801 attMaps(l_count).source_attribute := att.source_attribute;
802 attMaps(l_count).datatype := att.datatype;
803 l_count := l_count+1;
804 END LOOP;
805 close c_getAttributeMappings;
806
807 IF (g_log) THEN
808 writelog(g_spacing||'Attributes with single column mappings : '||(l_count-1));
809 END IF;
810
811 /* -------------------------------------------------
812
813 Get attributes with multiple mappings
814
815 ----------------------------------------------------*/
816
817
818 l_count := 1;
819 IF (g_log) THEN
820 writelog(g_spacing||'Opening cursor c_getMultiAttributeList with object_name = '||
821 object_name||', g_instance = '||g_instance||', p_level = '||p_level);
822 END IF;
823
824 open c_getMultiAttributeList(object_name, g_instance, p_level);
825
826
827 LOOP
828 FETCH c_getMultiAttributeList into multiatt;
829 EXIT WHEN c_getMultiAttributeList%NOTFOUND;
830 multiAttList(l_count).attribute_name := multiatt.attribute_name;
831 l_count := l_count + 1;
832
833 END LOOP;
834
835 close c_getMultiAttributeList;
836
837 IF (g_log) THEN
838 writelog(g_spacing||'Attributes with multiple column mappings : '||(l_count-1));
839 END IF;
840
841 /* -------------------------------------------------
842
843 Get attributes with flexfield mappings
844
845 ----------------------------------------------------*/
846
847
848 l_count := 1;
849
850 IF (g_log) THEN
851 writelog(g_spacing||'Opening cursor c_getFlexAttributeMappings with object_name = '||
852 object_name||', g_instance = '||g_instance||', p_level = '||p_level);
853 END IF;
854
855 open c_getFlexAttributeMappings(object_name, g_instance, p_level);
856 LOOP
857 FETCH c_getFlexAttributeMappings INTO flex;
858 EXIT WHEN c_getFlexAttributeMappings%NOTFOUND;
859
860 flexMaps(l_count).attribute_name := flex.attribute_name;
861 flexMaps(l_count).source_view := flex.source_view;
862 flexMaps(l_count).id_flex_code := flex.id_flex_code;
863 flexMaps(l_count).flex_field_type := flex.flex_field_type;
864
865 l_count := l_count+1;
866 END LOOP;
867 close c_getFlexAttributeMappings;
868
869 IF (g_log) THEN
870 writelog(g_spacing||'Attributes with flexfield mappings : '||(l_count-1));
871 END IF;
872
873
874 /* -------------------------------------------------
875
876 Get FKs mapped to Flex Dimensions
877
878 ----------------------------------------------------*/
879
880 l_count := 1;
881 IF (g_log) THEN
882 writelog(g_spacing||'Opening cursor c_getFactFlexFKMaps with object_name = '||
883 object_name||', g_instance = '||g_instance||', p_level = '||p_level);
884 END IF;
885
886 open c_getFactFlexFKMaps(object_name, g_instance);
887 LOOP
888 FETCH c_getFactFlexFKMaps INTO fk;
889 EXIT WHEN c_getFactFlexFKMaps%NOTFOUND;
890
891 fkMaps(l_count).fk_physical_name := fk.fk_physical_name;
892 l_count := l_count+1;
893 END LOOP;
894 close c_getFactFlexFKMaps;
895 IF (g_log) THEN
896 writelog(g_spacing||'Attributes with Foreign Key mappings : '||(l_count-1));
897 END IF;
898
899
900 IF (g_log) THEN
901 writelog( 'Completed getColumnMaps');
902 indentEnd;
903
904 END IF;
905
906 EXCEPTION WHEN OTHERS THEN
907 attMaps.delete;
908 multiAttList.delete;
909 flexMaps.delete;
910 fkMaps.delete;
911
912
913 END;
914
915 /*---------------------------------------------------------------------
916
917 If multiple columns are mapped to the same attribute, then we
918 need to do a NVL and get the columns out.
919 eg. if ColA, ColB and ColC are mapped this is what is generated
920
921 NVL(ColA, NVL(ColB, NVL(ColC,null)))
922
923 ---------------------------------------------------------------------*/
924
925 FUNCTION getNvlClause(p_object IN VARCHAR2, p_level IN VARCHAR2, p_instance IN VARCHAR2,
926 p_column IN VARCHAR2) return VARCHAR2 IS
927
928 endOfCol VARCHAR2(200);
929 nvlColumn VARCHAR2(2000);
930 srcCol VARCHAR2(200);
931 l_count NUMBER := 0;
932
933 BEGIN
934 IF (g_log) THEN
935
936 indentBegin;
937 writelog('Inside getNvlClause');
938 END IF;
939
940
941 endOfCol := 'NULL';
942 open c_getMultiAttributeMappings(p_object, p_instance, p_level, p_column);
943
944 LOOP
945 FETCH c_getMultiAttributeMappings INTO srcCol;
946 EXIT WHEN c_getMultiAttributeMappings%NOTFOUND;
947
948 nvlColumn:= nvlColumn||' NVL('||srcCol ||', ';
949 endOfCol := endOfCol || ')';
950 END LOOP;
951
952 close c_getMultiAttributeMappings;
953
954 IF (g_log) THEN
955 writelog('Completed getNvlClause');
956 indentEnd;
957
958 END IF;
959
960 RETURN nvlColumn||endOfCol;
961
962 END;
963
964
965 /*---------------------------------------------------------------------
966
967 Given a view name and a flex, get the flex prefix from the seed data
968
969 ---------------------------------------------------------------------*/
970
971 Function getFlexPrefix( pViewName IN VARCHAR2, pIdFlexCode IN VARCHAR2) RETURN VARCHAR2 IS
972
973 sPrefix VARCHAR2(20) := NULL;
974
975 CURSOR c1(pViewName VARCHAR2, pIdFlexCode VARCHAR2) IS
976 SELECT flex_field_prefix FROM edw_sv_flex_assignments
977 WHERE upper(flex_field_code) = upper(pIdFlexCode)
978 AND upper(flex_view_name) = upper(pViewName) ;
979
980 BEGIN
981
982 IF (g_log) THEN
983
984 indentBegin;
985 writelog('Inside getFlexPrefix');
986 writelog('Parameter pViewName:'||pViewName);
987 writelog('Parameter pIdFlexCode:'||pIdFlexCode );
988 END IF;
989
990 OPEN c1(pViewName , pIdFlexCode);
991 FETCH c1 into sPrefix;
992 CLOSE c1;
993
994 IF (g_log) THEN
995 writelog(g_spacing||'Prefix is :'||sPrefix);
996 writelog( 'Completed getFlexPrefix ');
997 indentEnd;
998
999 END IF;
1000
1001 IF (sPrefix is null) THEN
1002 g_error := 'Prefix is null. Data Seeded Incorrectly';
1003 IF(g_log) THEN
1004 writelog(g_spacing||'Data Seeded Incorrectly... Quitting');
1005 END IF;
1006 g_success := false;
1007 raise viewgen_exception;
1008 END IF;
1009
1010 RETURN sPrefix;
1011 END;
1012
1013
1014 Function getAppsVersion return VARCHAR2 is
1015 l_schema varchar2(100);
1016
1017 l_count number;
1018 l_dummy integer;
1019 l_cid number;
1020
1021 -- for bug 2228532
1022 stmt VARCHAR2(500) := 'select substr(RELEASE_NAME, 1,8) from fnd_product_groups@'
1023 || g_source_db_link;
1024
1025 BEGIN
1026
1027 IF (g_log) THEN
1028 indentBegin;
1029 writelog('Inside getAppsVersion');
1030 END IF;
1031
1032 cid := DBMS_SQL.open_cursor;
1033
1034 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
1035 DBMS_SQL.DEFINE_COLUMN(cid, 1, g_version, 100);
1036
1037 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
1038
1039 DBMS_SQL.COLUMN_VALUE(cid, 1, g_version);
1040 DBMS_SQL.close_cursor(cid);
1041
1042 IF (g_version like '10.7%') THEN
1043 g_version := '10.7';
1044 ELSIF (g_version like '11.0%') THEN
1045 g_version := '11.0';
1046 ELSE
1047 g_version := '11i';
1048 END IF;
1049
1050 IF (g_log) THEN
1051 writelog ('returning from getAppsVersion, version is :'||g_version);
1052
1053 writelog('Completed getAppsVersion');
1054 indentEnd;
1055
1056 END IF;
1057
1058
1059 return g_version;
1060
1061 END;
1062
1063 /*Function getAppsVersion(p_instance IN VARCHAR2) return VARCHAR2 is
1064
1065 l_count number;
1066 l_dummy integer;
1067 l_cid number;
1068
1069 -- for bug 2228532
1070 stmt VARCHAR2(500) ;:= 'select substr(RELEASE_NAME, 1,8) from fnd_product_groups@'
1071 || g_source_db_link;
1072 TYPE CurTyp IS REF CURSOR;
1073 cv CurTyp;
1074
1075 l_db_link varchar2(200);
1076
1077 BEGIN
1078
1079 SELECT warehouse_to_instance_link into l_db_link
1080 from EDW_SOURCE_INSTANCES_VL
1081 WHERE instance_code = p_instance;
1082
1083 stmt := := 'select substr(RELEASE_NAME, 1,8) from fnd_product_groups@|| l_db_link;
1084 IF (g_log) THEN
1085 indentBegin;
1086 writelog('Inside getAppsVersion with instance='||p_instance);
1087 END IF;
1088
1089 cid := DBMS_SQL.open_cursor;
1090
1091 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
1092 DBMS_SQL.DEFINE_COLUMN(cid, 1, g_version, 100);
1093
1094 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
1095
1096 DBMS_SQL.COLUMN_VALUE(cid, 1, g_version);
1097 DBMS_SQL.close_cursor(cid);
1098
1099 IF (g_version like '10.7%') THEN
1100 g_version := '10.7';
1101 ELSIF (g_version like '11.0%') THEN
1102 g_version := '11.0';
1103 ELSE
1104 g_version := '11i';
1105 END IF;
1106
1107 IF (g_log) THEN
1108 writelog ('returning from getAppsVersion, version is :'||g_version);
1109
1110 writelog('Completed getAppsVersion');
1111 indentEnd;
1112
1113 END IF;
1114
1115
1116 return g_version;
1117
1118 END;
1119
1120 */
1121
1122 Function getContextColForFlex(p_flex in varchar2, p_flex_type IN VARCHAR2) RETURN VARCHAR2 IS
1123 TYPE CurTyp IS REF CURSOR;
1124 cv CurTyp;
1125 stmt varchar2(200) := 'select context_column_name from fnd_descriptive_flexs_vl@'||edw_gen_view.g_source_db_link||
1126 ' where descriptive_flexfield_name =:s1 ';
1127 l_structure_col varchar2(100);
1128 BEGIN
1129 IF (p_flex_type = 'D') THEN
1130 stmt := 'select context_column_name from fnd_descriptive_flexs_vl@'||edw_gen_view.g_source_db_link||
1131 ' where descriptive_flexfield_name =:s1 ';
1132 ELSE
1133 stmt := 'select set_defining_column_name from fnd_id_flexs@'||edw_gen_view.g_source_db_link||
1134 ' where id_Flex_code =:s1 ';
1135 END IF;
1136 open cv for stmt using p_flex ;
1137 fetch cv into l_structure_col;
1138 close cv;
1139 return l_structure_col;
1140 END;
1141
1142 /*---------------------------------------------------------------------
1143
1144 For columns mapped to flexfields, get the decode clause.
1145
1146 ---------------------------------------------------------------------*/
1147
1148
1149 FUNCTION getDecodeClauseForFlexCol( pSourceView IN VARCHAR2,
1150 pAttributeName IN VARCHAR2, pIdFlexCode IN VARCHAR2,
1151 pFlexType IN VARCHAR2) RETURN VARCHAR2 IS
1152
1153 CURSOR c1(pView IN varchar2, pAttr IN varchar2) IS
1154 SELECT distinct structure_num, segment_name, structure_name, VALUE_SET_DATATYPE
1155 from edw_attribute_mappings a, edw_flex_attribute_mappings b
1156 where upper(a.source_view) = upper(pView)
1157 and upper(a.attribute_name) = upper(pAttr)
1158 and a.attr_mapping_pk = b.attr_mapping_fk;
1159
1160 sPrefix VARCHAR2(100) := NULL;
1161 sDecodeClause VARCHAR2(3000) := NULL;
1162 cRow c1%ROWTYPE;
1163 l_length NUMBER:=0;
1164 tempvar VARCHAR2(100) := NULL;
1165 nCount NUMBER := 1;
1166 stmt1 VARCHAR2(1500) := 'select replace(SEGMENT_NAME, '||''' '||
1167 ''','||'''_'||''''||')||'||''''||'_'||''''||'||
1168 a.id_flex_num FROM fnd_id_flex_structures_vl@'||g_source_db_link
1169 ||' b, fnd_id_flex_segments_vl@'||g_source_db_link||
1170 ' a WHERE a.segment_name = :seg_name';
1171
1172 stmt2 VARCHAR2(500) := ' AND a.id_flex_code = b.id_flex_code '||
1173 ' AND a.id_flex_num = b.id_flex_num';
1174
1175 descStmt VARCHAR2(1500) ;
1176 l_gen_seg_name VARCHAR2(100):= NULL;
1177 cid NUMBER := 0;
1178 l_dummy NUMBER := 0;
1179
1180 cursor C_Replace_Apos(p_name VARCHAR2) is
1181 select replace(p_name, '''', '''''') from dual;
1182 l_structure_name VARCHAR2(1000);
1183
1184 BEGIN
1185
1186 IF (g_log) THEN
1187
1188 writelog(newline);
1189 indentBegin;
1190 writelog('Inside getDecodeClauseForFlexCol');
1191 writelog('Parameter pSourceView:'||pSourceView);
1192 writelog('Parameter pAttributeName:'||pAttributeName);
1193 writelog('Parameter pIdFlexCode:'|| pIdFlexCode);
1194 writelog('Parameter pFlexType:'||pFlexType);
1195
1196 END IF;
1197
1198
1199 sPrefix := getFlexPrefix(pSourceView, pIdFlexCode);
1200
1201 sDecodeClause := ' DECODE('||sPrefix||'_CONTEXT, ';
1202 open c1(pSourceView, pAttributeName);
1203
1204
1205 LOOP
1206 fetch c1 into cRow;
1207 EXIT WHEN c1%NOTFOUND;
1208 IF(nCount > 1) THEN
1209 sDecodeClause := sDecodeClause ||','||newline||' ';
1210 END IF;
1211
1212
1213 ------------------------------------------------
1214
1215 IF (g_log) THEN
1216 writelog(g_spacing||'l_gen_seg_name is :'|| l_gen_seg_name);
1217 writelog(g_spacing|| 'calling formSegmentName');
1218 END IF;
1219
1220 l_gen_seg_name := formSegmentName(sPrefix, cRow.segment_name, cRow.structure_num, pIdFlexCode, pFlexType);
1221
1222
1223 -------------------------------------------------
1224
1225
1226 -- we need to consider accounting flex field as well (bug 2245373)
1227 IF (pFlexType = 'K' OR pFlexType = 'A') THEN
1228 sDecodeClause := sDecodeClause||''''||cRow.structure_num||''''||', ';
1229
1230 ELSE /* Descr Flex */
1231 /*
1232 open C_Replace_Apos(cRow.structure_name);
1233 fetch C_Replace_Apos into l_structure_name;
1234 close C_Replace_Apos;
1235 IF (g_log) THEN
1236 writelog('cRow.structure_name = '||cRow.structure_name);
1237 writelog('l_structure_name = '||l_structure_name);
1238 END IF;
1239 */
1240 IF (g_log) THEN
1241 writelog('in descr flex condition');
1242 END IF;
1243 tempvar := replace(cRow.structure_name, '''', '''''') ;
1244
1245 IF (g_log) THEN
1246 writelog('temp var is : '||tempvar);
1247 END IF;
1248 sDecodeClause := sDecodeClause||''''||tempvar ||''', ';
1249 IF (g_log) THEN
1250 writelog('decode clause is : '||sDecodeClause);
1251 END IF;
1252
1253 END IF;
1254
1255
1256 IF (pAttributeName like 'USER_ATTRIBUTE%' and cRow.VALUE_SET_DATATYPE='N') THEN
1257
1258 /* ----------------------------------------------------------------------------
1259
1260 For bug 1723461, need to work around as the OWB UI will assume any
1261 segment is a VARCHAR2
1262 Hence a column which is actually a number is mapped to a USER_ATTRIBUTE
1263 and so we need to do a type cast
1264
1265 ------------------------------------------------------------------------------*/
1266
1267 sDecodeClause := sDecodeClause|| 'TO_CHAR("'||l_gen_seg_name||'")';
1268
1269 ELSE
1270 sDecodeClause := sDecodeClause|| '"'||l_gen_seg_name||'"';
1271 END IF;
1272
1273 nCount := nCount + 1;
1274 END LOOP;
1275
1276
1277 sDecodeClause := sDecodeClause||', NULL)';
1278
1279 /* --------------------------------------------------------------------------------------
1280 If the flexfield is one of the following, then we should NOT generate
1281 a decode clause(bug 2340462)
1282
1283 MSTK : 401/INV - System Items.
1284 MTLL : 401/INV - Stock Locators
1285 MICG : 401/INV - Item Catalogs
1286 MDSP : 401/INV - Account Aliases
1287 ----------------------------------------------------------------------------------------*/
1288
1289 IF (pIdFlexCode in ('MSTK', 'MTLL', 'MICG', 'MDSP')) THEN
1290 sDecodeClause := '"'||l_gen_seg_name||'"';
1291 END IF;
1292
1293 /* if the context is global data elements then we should NOT generate
1294 a decode clause (bug 1463064) as this context is never stored in
1295 the attribute_category field as it is a global context and will
1296 exist even if other contexts are present */
1297
1298
1299 IF (upper(cRow.structure_name)='GLOBAL DATA ELEMENTS') THEN
1300 IF (pAttributeName like 'USER_ATTRIBUTE%' and cRow.VALUE_SET_DATATYPE='N') THEN
1301 sDecodeClause := 'TO_CHAR("'||l_gen_seg_name||'")';
1302 ELSE
1303 sDecodeClause := '"'||l_gen_seg_name||'"';
1304 END IF;
1305 END IF;
1306
1307
1308 close c1;
1309
1310
1311 IF (g_log) THEN
1312 writelog(g_spacing||'Decode Clause is : ' || sDecodeClause);
1313 writelog('Completed getDecodeClauseForFlexCol');
1314 indentEnd;
1315 END IF;
1316
1317 return sDecodeClause;
1318
1319 END;
1320
1321 /*---------------------------------------------------------
1322 generate_pruned_view
1323 -----------------------------------------------------------*/
1324 PROCEDURE GeneratePrunedBisView(p_obj_name in varchar2) IS
1325 l_generated_view_name varchar2(30);
1326 l_flex_view_name varchar2(30);
1327 l_stmt varchar2(1000);
1328 l_cursor_id number;
1329 l_dummy number;
1330 l_status varchar2(30);
1331 l_errmesg varchar2(1000);
1332 type curType is ref cursor;
1333 c_failed_bg_view curType;
1334 c_failed_pruned_view curType;
1335
1336 BEGIN
1337 l_stmt:='select distinct a.flex_view_name from edw_source_views a, edw_local_generation_status@'||g_source_db_link
1338 ||' b where a.object_name=:s1 and a.version= :s2 and b.generate_status =:s3 and a.flex_view_name = b.flex_view_name';
1339
1340 IF g_log THEN
1341 writelog('going to execute :'||l_stmt);
1342 END IF;
1343
1344 OPEN c_failed_bg_view FOR l_stmt USING p_obj_name, g_version, g_status_failed_all;
1345 LOOP
1346
1347 FETCH c_failed_bg_view INTO l_flex_view_name;
1348 EXIT WHEN c_failed_bg_view%NOTFOUND;
1349
1350 IF g_log THEN
1351 writelog('Needs to generate pruned '|| l_flex_view_name||' in '|| g_instance);
1352 writelog('Parameters passed to bis_view_generator_pvt.generate_pruned_view:'||l_flex_view_name||':'|| p_obj_name);
1353 END IF;
1354
1355
1356 l_stmt:='begin bis_view_generator_pvt.generate_pruned_view@'||g_source_db_link||' (:s1,:s2) ; end; ';
1357
1358 IF g_log THEN
1359 writelog('going to execute :'||l_stmt);
1360 END IF;
1361
1362 EXECUTE IMMEDIATE l_stmt USING l_flex_view_name, p_obj_name;
1363
1364 l_stmt:= 'select generate_status, error_message from edw_local_generation_status@'|| g_source_db_link ||' where flex_view_name=:s1';
1365
1366 OPEN c_failed_pruned_view FOR l_stmt USING l_flex_view_name;
1367
1368 FETCH c_failed_pruned_view INTO l_status, l_errmesg;
1369
1370 CLOSE c_failed_pruned_view ;
1371
1372 IF (g_log) THEN
1373 writelog('l_status:'||l_status||' g_status:'|| g_status_generated_pruned);
1374 END IF;
1375
1376 IF l_status<> g_status_generated_pruned THEN
1377 IF g_log THEN
1378 writelog('Failed to generate pruned '|| l_flex_view_name||' in '|| g_instance);
1379 writelog('Error Message: ' || l_errmesg);
1380 END IF;
1381 g_error := l_errmesg;
1382 g_success := false;
1383 raise viewgen_exception;
1384
1385 IF (g_log) THEN
1386 writelog('Generated pruned '|| l_flex_view_name||' in '|| g_instance);
1387 END IF;
1388 END IF;
1389 END LOOP;
1390 CLOSE c_failed_bg_view;
1391
1392 exception
1393 when others then
1394 IF (g_log) THEN
1395 writelog('Exception!!! '|| sqlerrm);
1396 writelog('Inside GeneratePrunedBisView');
1397 END IF;
1398 g_success := false;
1399 raise viewgen_exception;
1400 end;
1401
1402 Procedure Generate(p_obj_name in varchar2,
1403 p_obj_type in varchar2,
1404 p_instance in varchar2,
1405 p_db_link in varchar2,
1406 p_log_dir in varchar2 default null
1407 ) IS
1408
1409 l_counter NUMBER:=-1;
1410 stmt varchar2(3000);
1411 l_dummy integer;
1412 l_dir VARCHAR2(100);
1413 l_view_name varchar2(30);
1414
1415 TYPE CurTyp IS REF CURSOR;
1416 cv CurTyp;
1417 l_count integer;
1418 l_validated_obj_type VARCHAR2(100);
1419
1420 BEGIN
1421
1422 g_success := true;
1423 g_source_db_link := p_db_link;
1424 g_instance := p_instance;
1425 g_indenting := '';
1426 g_obj_name := p_obj_name;
1427 edw_misc_util.globalNamesOff;
1428 BEGIN
1429 SELECT WAREHOUSE_TO_INSTANCE_LINK INTO g_source_db_link
1430 FROM edw_source_instances_vl
1431 WHERE instance_code = p_instance;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 IF (g_log) THEN
1435 writelog('Exception in Generate!!! ' || sqlerrm);
1436 writelog(' *** ' || p_instance || ' is not defined as a source instance ***');
1437 END IF;
1438 RAISE;
1439 END;
1440
1441
1442 IF (p_log_dir IS NOT NULL ) THEN
1443 l_dir := p_log_dir;
1444 l_file := utl_file.fopen(p_log_dir, p_instance||'_'||p_obj_name||'.log' ,'w');
1445 l_out_file:=utl_file.fopen(p_log_dir, p_instance||'_'|| p_obj_name||'.sql' ,'w');
1446 g_log := true;
1447 ELSE
1448 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
1449 END IF;
1450
1451 IF (l_dir IS NULL) THEN
1452 l_dir := getUtlFileDir;
1453 END IF;
1454
1455 IF (l_dir IS NOT NULL ) THEN
1456 l_file := utl_file.fopen(l_dir, p_instance||'_'||p_obj_name||'.log' ,'w');
1457 l_out_file := utl_file.fopen(l_dir, p_instance||'_'|| p_obj_name ||'.sql' ,'w');
1458 g_log := true;
1459 ELSE
1460 g_log := false;
1461 END IF;
1462 EDW_DIM_SV.g_log := g_log;
1463 EDW_FACT_SV.g_log:= g_log;
1464
1465
1466 IF (g_log) THEN
1467 writelog('---------------------------------------------------------'||newline);
1468 writeLog('System date at the time of view generation is '||
1469 to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1470 writelog('View Generation for '||p_obj_type|| ' '||p_obj_name||newline);
1471 writelog( 'To be deployed on '||p_instance||' through db link '||p_db_link);
1472 writelog('---------------------------------------------------------'||newline);
1473 writelog(newline);
1474
1475 writelog('Inside Generate procedure');
1476
1477 END IF;
1478
1479
1480
1481 g_apps_schema := getAppsSchema;
1482 IF g_log THEN
1483 writelog('Apps Schema is : '||g_apps_schema);
1484 END IF;
1485
1486 g_version := getAppsVersion;
1487
1488 IF g_log THEN
1489 writelog('Apps Version is : '||g_version);
1490 END IF;
1491
1492 IF (p_obj_name LIKE 'EDW_FLEX_DIM%_M' OR p_obj_type LIKE 'EDW_GL_ACCT%_M') THEN
1493 null;
1494 ELSE
1495 IF (g_log) THEN
1496 writelog('Going to call generatePrunedBisView to check view generation status');
1497 END IF;
1498 generatePrunedBisView(p_obj_name);
1499 END IF;
1500
1501 /* Validate Object Type */
1502
1503 SELECT count(1) into l_dummy from user_views where view_name='EDW_DIMENSIONS_MD_V';
1504
1505 IF (l_dummy > 0) THEN /* EDW_DIMENSIONS_MD_V exists */
1506 open cv for 'select count(1) from edw_dimensions_md_v where dim_name =:s1' using p_obj_name;
1507 fetch cv into l_count;
1508 close cv;
1509 IF (l_count > 0) THEN
1510 l_validated_obj_type := 'DIMENSION';
1511 ELSE
1512 l_validated_obj_type := 'FACT';
1513 END IF;
1514 END IF;
1515
1516 IF (g_log) THEN
1517 writelog('Object type after validation : '||l_validated_obj_type);
1518 END IF;
1519
1520 writelog('');
1521 IF (l_validated_obj_type = 'DIMENSION') THEN
1522 edw_dim_sv.generateViewForDimension(p_obj_name);
1523 ELSE
1524 edw_fact_sv.generateViewForFact(p_obj_name);
1525 END IF;
1526
1527 IF (NOT g_success) THEN
1528 IF (g_log) THEN
1529 writelog(g_error);
1530 utl_file.fclose(l_file);
1531 utl_file.fclose(l_out_file);
1532
1533 END IF;
1534 END IF;
1535
1536
1537 stmt := 'delete from edw_object_deployments where
1538 object_short_name = :obj and instance_code = :src';
1539
1540 cid := DBMS_SQL.open_cursor;
1541 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
1542 DBMS_SQL.BIND_VARIABLE(cid, ':obj', p_obj_name, 50);
1543 DBMS_SQL.BIND_VARIABLE(cid, ':src', p_instance, 50);
1544
1545 l_dummy := DBMS_SQL.EXECUTE(cid);
1546 DBMS_SQL.close_cursor(cid);
1547
1548 cid := DBMS_SQL.open_cursor;
1549 stmt := 'INSERT INTO edw_object_deployments(object_short_name ,
1550 dim_flag ,
1551 instance_code ,
1552 deployment_date ,
1553 change_flag ,
1554 last_update_date ,
1555 last_updated_by ,
1556 last_update_login ,
1557 created_by ,
1558 creation_date ) VALUES
1559 (:obj, :dim_flag, :source, sysdate, ''N'', sysdate, 1, 1, 1, sysdate)';
1560 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
1561 DBMS_SQL.BIND_VARIABLE(cid, ':obj', p_obj_name, 50);
1562
1563 IF (p_obj_type = 'DIMENSION') THEN
1564 DBMS_SQL.BIND_VARIABLE(cid, ':dim_flag', 'Y', 1);
1565 ELSE
1566 DBMS_SQL.BIND_VARIABLE(cid, ':dim_flag', 'N', 1);
1567 END IF;
1568 DBMS_SQL.BIND_VARIABLE(cid, ':source', p_instance, 50);
1569 l_dummy := DBMS_SQL.EXECUTE(cid);
1570 DBMS_SQL.close_cursor(cid);
1571 commit;
1572
1573
1574 IF (NOT g_success) THEN
1575 raise viewgen_exception;
1576 END IF;
1577
1578 IF (g_log) THEN
1579 writelog(newline);
1580 writelog('Updated entries in edw_object_deployments');
1581 writelog('Completed view generation successfully.');
1582 utl_file.fclose(l_file);
1583 utl_file.fclose(l_out_file);
1584
1585 END IF;
1586
1587 EXCEPTION
1588 WHEN viewgen_exception THEN
1589
1590 IF (g_log) THEN
1591 writelog('Exception!!! '||g_error);
1592 utl_file.fflush(l_file);
1593 utl_file.fclose(l_file);
1594 utl_file.fflush(l_out_file);
1595 utl_file.fclose(l_out_file);
1596 END IF;
1597
1598 fnd_message.set_name('BIS', 'EDW_APPS_INT_GENERAL_ERROR');
1599 fnd_message.set_token('MESSAGE', g_error);
1600 fnd_message.set_token('LOGFILE',
1601 l_dir||'/'|| p_instance||'_'||p_obj_name||'.log' , FALSE);
1602 app_exception.raise_exception;
1603
1604 RAISE;
1605 WHEN OTHERS THEN
1606
1607 IF (g_log) THEN
1608 writelog('Exception!!! '||sqlerrm);
1609 utl_file.fflush(l_file);
1610 utl_file.fclose(l_file);
1611 utl_file.fflush(l_out_file);
1612 utl_file.fclose(l_out_file);
1613
1614 END IF;
1615 RAISE;
1616 END;
1617
1618 FUNCTION convertString(p_string IN VARCHAR2) RETURN VARCHAR2 IS
1619 l_newstring VARCHAR2(100) := null;
1620 l_length INTEGER := 0;
1621 i INTEGER := 0;
1622 l_char VARCHAR2(10);
1623
1624 begin
1625 if (p_string is NULL) then return NULL; end if;
1626
1627 l_length := length(p_string);
1628 for i in 1..l_length loop
1629 l_char := upper(substr(p_string, i, 1));
1630 if (l_char = ' ' or l_char = '-' or l_char='&' or l_char='^') then
1631 l_newstring := l_newstring||'_';
1632 else
1633 l_newstring := l_newstring||l_char;
1634 END IF;
1635 end loop;
1636 return l_newstring;
1637 end;
1638
1639
1640 Function checkWhereClause(p_value_set_id in NUMBER, p_link in varchar2) return boolean IS
1641
1642 TYPE CurTyp IS REF CURSOR;
1643 cv CurTyp;
1644
1645 l_cursor number;
1646 l_chunk_size number:=30000;
1647 l_chunk_size_returned number;
1648 l_stmt varchar2(2000);
1649 l_cur_pos number:=0;
1650 l_rows number;
1651 l_dummy number;
1652 l_data_chunk varchar2(30000);
1653 l_count number := 0;
1654 l_where_exists boolean := false;
1655
1656 BEGIN
1657
1658 IF (g_log) THEN
1659 edw_gen_view.indentBegin;
1660 edw_gen_view.writelog('Inside checkWhereClause for VS '||p_value_set_id);
1661 END IF;
1662
1663 l_stmt := 'select additional_where_clause from fnd_flex_validation_tables@'||
1664 p_link||' where flex_value_set_id = :vsid';
1665
1666 IF (g_log) THEN
1667 edw_gen_view.writelog('Querying where clause : '|| l_stmt);
1668 END IF;
1669
1670 l_cursor:=dbms_sql.open_cursor;
1671 dbms_sql.parse(l_cursor, l_stmt, dbms_sql.native);
1672 dbms_sql.bind_variable(l_cursor, ':vsid', p_value_set_id);
1673 dbms_sql.define_column_long(l_cursor,1);
1674
1675 l_dummy:=dbms_sql.execute(l_cursor);
1676
1677 LOOP
1678 -- fetch 'chunks' of the long until we have got the lot
1679 edw_gen_view.writelog('Checking for bind variables '||l_count);
1680 IF (dbms_sql.fetch_rows(l_cursor) = 0) THEN
1681 EXIT;
1682 END IF;
1683
1684 dbms_sql.column_value_long(l_cursor,1,l_chunk_size,l_cur_pos,l_data_chunk,l_chunk_size_returned);
1685
1686 IF (upper(l_data_chunk) like '%$FLEX$' OR l_data_chunk like '%:%') THEN
1687 return false;
1688 END IF;
1689
1690 edw_gen_view.writelog('Chunk size returned is '||l_chunk_size_returned);
1691 l_cur_pos:=l_cur_pos+l_chunk_size;
1692 exit when l_chunk_size_returned=0;
1693
1694 g_where_clause(l_count):=l_data_chunk;
1695 l_where_exists := true;
1696 l_count:= l_count +1;
1697 END LOOP;
1698
1699 dbms_sql.close_cursor(l_cursor);
1700
1701 IF (l_where_exists ) THEN
1702 g_where_clause(0) := ltrim(g_where_clause(0));
1703 IF (lower(substr(g_where_clause(0), 0, 5) ) = 'where') THEN
1704 null;
1705 ELSE
1706 g_where_clause(0) := ' where '||g_where_clause(0);
1707 END IF;
1708 END IF;
1709
1710 edw_gen_view.writelog('Completed checkWhereClause');
1711 edw_gen_view.indentEnd;
1712 return true;
1713
1714 END;
1715
1716 END EDW_GEN_VIEW;