[Home] [Help]
PACKAGE BODY: APPS.EDW_BIS_VIEW_GEN
Source
1 Package Body EDW_BIS_VIEW_GEN AS
2 /* $Header: EDWBISVB.pls 115.10 2003/04/15 21:45:49 arsantha ship $ */
3
4 Procedure generateOneView(p_view_name in varchar2) IS
5 temp varchar2(1000);
6 ret number;
7 l_count number;
8 l_missing_view EXCEPTION;
9 BEGIN
10
11 -- for bug 2378092: check if the view exists and is valid
12 select count(*) into l_count
13 from user_objects
14 where OBJECT_NAME = upper(p_view_name)
15 and OBJECT_TYPE = 'VIEW'
16 and STATUS = 'VALID';
17
18 if l_count = 0 then
19 raise l_missing_view;
20 end if;
21 ------------------------------------
22
23 bis_view_generator_pvt.set_mode(2);
24 bis_view_generator_pvt.generate_views(
25 x_error_buf => temp,
26 x_ret_code => ret,
27 p_all_flag => NULL,
28 p_App_Short_Name => NULL,
29 p_kf_appl_short_name => NULL,
30 p_key_flex_code => NULL,
31 p_df_appl_short_name => NULL,
32 p_desc_flex_name => NULL,
33 p_lookup_table_name => NULL,
34 p_lookup_type => NULL,
35 p_view_name => p_view_name);
36
37 -- for bug 2391331: need to commit due to autonomous transaction and DBlinks
38 commit;
39 ----------
40
41 EXCEPTION
42 WHEN l_missing_view THEN
43 fnd_file.put_line(fnd_file.log, 'Source view ' || p_view_name || ' is missing or invalid');
44 raise;
45
46 WHEN OTHERS THEN
47 fnd_file.put_line(fnd_file.log, 'Error in calling generate_views for '
48 || p_view_name || ': ' || temp);
49 raise;
50
51 END;
52
53 Procedure generateAllViews(Errbuf in out NOCOPY Varchar2,
54 Retcode in out NOCOPY Varchar2,
55 p_object_long_name in varchar2 )
56 IS
57 stmt varchar2(300);
58 v_col DBMS_SQL.VARCHAR2_TABLE;
59 l_viewname VARCHAR2(100);
60 cid NUMBER;
61 l_dummy NUMBER;
62 nViewCount NUMBER := 0;
63 nCount NUMBER := 0;
64 l_version varchar2(10):='11i';
65 l_obj_short_name varchar2(30);
66 l_dir varchar2(400);
67 l_newline VARCHAR2(10):='
68 ';
69 l_generate_status varchar2(60);
70 l_error_message varchar2(3000);
71 l_source_link VARCHAR2(128);
72 l_target_link VARCHAR2(128);
73
74 BEGIN
75
76 stmt := 'alter session set global_names=false';
77 execute immediate stmt;
78
79 -- get databaselink
80 EDW_COLLECTION_UTIL.get_dblink_names(l_source_link, l_target_link);
81
82 IF (p_object_long_name is not null) THEN
83 stmt:= 'SELECT relation_name from EDW_RELATIONS_MD_V@' || l_target_link ||
84 ' where relation_long_name = :longname and relation_type in (:fact, :dimension)';
85 cid := DBMS_SQL.OPEN_CURSOR;
86 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
87 DBMS_SQL.BIND_VARIABLE(cid, ':longname', p_object_long_name);
88 DBMS_SQL.BIND_VARIABLE(cid, ':fact', 'CMPWBCube');
89 DBMS_SQL.BIND_VARIABLE(cid, ':dimension', 'CMPWBDimension');
90 DBMS_SQL.DEFINE_COLUMN(cid, 1, l_obj_short_name, 100);
91 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
92 DBMS_SQL.COLUMN_VALUE(cid, 1, l_obj_short_name);
93 DBMS_SQL.close_cursor(cid);
94 END IF;
95
96 l_dir:=fnd_profile.value('UTL_FILE_DIR');
97
98 IF l_dir is null THEN
99 l_dir:='/sqlcom/log';
100 END IF;
101
102 fnd_file.put_names(l_obj_short_name||'gen_bg.log',l_obj_short_name||'gen_bg.out',l_dir);
103
104
105 fnd_file.put_line(fnd_file.log,'Object physical name is '||l_obj_short_name);
106 stmt := 'SELECT count(distinct FLEX_VIEW_NAME) FROM edw_source_views@' || l_target_link ||
107 ' where version = :version and GENERATED_VIEW_NAME <>''NULL'' ';
108
109 IF l_obj_short_name is not null THEN
110 stmt := stmt ||' and object_name = :b_object_name';
111 END IF;
112
113 fnd_file.put_line(fnd_file.log,l_newline||'Going to Execute : '||stmt);
114 cid := DBMS_SQL.OPEN_CURSOR;
115 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
116 DBMS_SQL.BIND_VARIABLE(cid, ':version', l_version);
117
118 IF l_obj_short_name is not null THEN
119 DBMS_SQL.BIND_VARIABLE(cid, ':b_object_name', l_obj_short_name);
120 END IF;
121
122 DBMS_SQL.DEFINE_COLUMN(cid, 1, nViewCount);
123 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
124 DBMS_SQL.COLUMN_VALUE(cid, 1, nViewCount);
125 DBMS_SQL.close_cursor(cid);
126
127 fnd_file.put_line(fnd_file.log,l_newline||'Number of source views found :'|| nViewCount);
128
129 stmt := 'SELECT distinct FLEX_VIEW_NAME FROM edw_source_views@' || l_target_link ||
130 ' where version = :version and GENERATED_VIEW_NAME <>''NULL'' ';
131
132 IF l_obj_short_name is not null THEN
133 stmt := stmt ||' and object_name = :b_object_name ';
134 END IF;
135
136 cid := DBMS_SQL.OPEN_CURSOR;
137 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
138 DBMS_SQL.BIND_VARIABLE(cid, ':version', l_version);
139
140 IF l_obj_short_name is not null THEN
141 DBMS_SQL.BIND_VARIABLE(cid, ':b_object_name', l_obj_short_name);
142 END IF;
143 DBMS_SQL.DEFINE_ARRAY(cid, 1, v_col, nViewCount, 1);
144 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(cid);
145 DBMS_SQL.COLUMN_VALUE(cid, 1, v_col);
146 DBMS_SQL.close_cursor(cid);
147
148 WHILE (nCount < nViewCount )LOOP
149 nCount := nCount + 1;
150 fnd_file.put_line(fnd_file.log, nCount||'. Going to generate '||v_col(nCount));
151 generateOneView(v_col(nCount));
152
153 BEGIN
154
155 SELECT generate_status, error_message into l_generate_status, l_error_message
156 FROM EDW_LOCAL_GENERATION_STATUS
157 WHERE flex_view_name = v_col(nCount);
158
159 IF l_generate_status = 'GENERATED_ALL' THEN
160
161 fnd_file.put_line(fnd_file.log,v_col(nCount)|| ' Generated.'||l_newline);
162 ELSE
163 fnd_file.put_line(fnd_file.log,v_col(nCount)|| ' Generation failed.');
164 fnd_file.put_line(fnd_file.log, ' Error Message is : '||l_error_message||l_newline);
165 END IF;
166
167 EXCEPTION WHEN no_data_found THEN
168 null;
169 WHEN OTHERS THEN
170 RAISE;
171 END;
172
173 END LOOP;
174
175 exception when others then
176 fnd_file.put_line(fnd_file.log,sqlerrm);
177 raise;
178
179 END;
180 END EDW_BIS_VIEW_GEN;