[Home] [Help]
PACKAGE BODY: APPS.EDW_SYSTEM_PARAMS_PKG
Source
1 PACKAGE BODY EDW_SYSTEM_PARAMS_PKG as
2 /* $Header: edwparmb.pls 120.1 2006/03/28 01:46:54 rkumar noship $ */
3 l_db_link VARCHAR2(240);
4
5 CURSOR instances IS
6 SELECT warehouse_to_instance_link
7 FROM edw_source_instances_vl
8 WHERE enabled_flag = 'Y';
9 CURSOR cols IS
10 SELECT column_name
11 FROM all_tab_columns
12 WHERE table_name = 'EDW_SYSTEM_PARAMETERS'
13 AND owner=edw_owb_collection_util.get_db_user('BIS')
14 AND column_name NOT IN ('LAST_UPDATE_DATE', 'CREATION_DATE'); --removed Upper for bug#4905343
15 CURSOR sources IS
16 SELECT instance_code, warehouse_to_instance_link
17 FROM edw_source_instances_vl
18 WHERE enabled_flag = 'Y';
19
20 Function isInstanceRunning(p_mode IN NUMBER, p_db_link IN VARCHAR2, p_instance IN VARCHAR2) RETURN BOOLEAN IS
21 cid NUMBER := 0;
22 bRunning BOOLEAN := TRUE;
23 BEGIN
24
25
26 BEGIN
27 edw_misc_util.globalNamesOff;
28
29 cid := DBMS_SQL.open_cursor;
30
31 dbms_sql.parse(cid, 'SELECT 1 FROM dual@'||p_db_link, dbms_sql.native);
32 dbms_sql.close_cursor(cid);
33
34 Exception
35 WHEN OTHERS THEN
36 bRunning := FALSE;
37 dbms_sql.close_cursor(cid);
38
39 END;
40
41
42 IF (p_mode = 1) THEN
43 IF (bRunning = FALSE) THEN
44 fnd_message.set_name('BIS', 'EDW_BAD_DBLINK');
45 fnd_message.set_token('DBLINK', p_instance, FALSE);
46 app_exception.raise_exception;
47 END IF;
48 END IF;
49 RETURN bRunning;
50 END;
51
52 PROCEDURE pushToSource(inst_down OUT NOCOPY varchar2) IS
53 l_temp VARCHAR2(200):=NULL;
54 l_colList VARCHAR2(2000):=NULL;
55 l_stmt VARCHAR2(5000):=NULL;
56 cid NUMBER:=0;
57 l_dummy NUMBER:=0;
58 l_progress varchar2(10);
59 l_count NUMBER := 0;
60 remote_date DATE;
61 TYPE CurTyp IS REF CURSOR;
62 cv CurTyp;
63 BEGIN
64
65 -- We need to push EDW_SYSTEM_PARAMETERS to source OLTP's for the push programs to use
66 SAVEPOINT start_push_to_source;
67 l_progress := '010';
68
69
70 /* Turn Off global names for the session */
71
72 edw_misc_util.globalNamesOff;
73
74
75 /* IF (cols%ISOPEN) THEN
76 CLOSE cols;
77 END IF;
78
79
80 OPEN cols;
81 LOOP
82 FETCH cols INTO l_temp;
83 EXIT WHEN cols%NOTFOUND;
84 if (l_count = 0) then
85 l_colList := l_colList||l_temp;
86 l_count := l_count + 1;
87 else
88 l_colList := l_colList||', '||l_temp;
89 end if;
90 END LOOP;
91 close cols; */
92 l_progress := '015';
93 --open instances;
94
95 IF (sources%isopen) THEN
96 close sources;
97 END IF;
98 open sources;
99 LOOP
100 l_progress := '020';
101 --FETCH instances INTO l_db_link;
102 fetch sources into l_temp, l_db_link;
103 EXIT WHEN sources%NOTFOUND;
104
105 IF isInstanceRunning(0, l_db_link, l_temp) THEN
106
107 -- get sydate from remote db
108 cid := DBMS_SQL.open_cursor;
109 DBMS_SQL.PARSE(cid, 'SELECT sysdate FROM dual@'||l_db_link, dbms_sql.native);
110 dbms_sql.define_column(cid, 1, remote_date);
111 l_dummy:=dbms_sql.execute(cid);
112 if dbms_sql.fetch_rows(cid)<>0 then
113 dbms_sql.column_value(cid, 1, remote_date);
114 end if;
115 DBMS_SQL.close_cursor(cid);
116
117 /*
118 l_stmt:='SELECT distinct column_name
119 FROM all_tab_columns@' ||l_db_link ||
120 ' WHERE table_name = ''EDW_LOCAL_SYSTEM_PARAMETERS''
121 AND upper(column_name) not in ( ''LAST_UPDATE_DATE'',''CREATION_DATE'')' ||
122 ' INTERSECT SELECT distinct column_name from all_tab_columns '||
123 ' WHERE table_name = ''EDW_SYSTEM_PARAMETERS''
124 AND upper(column_name) not in ( ''LAST_UPDATE_DATE'',''CREATION_DATE'')' ;
125 */
126
127
128 l_stmt:= 'SELECT distinct tab.column_name FROM all_tab_columns@'
129 ||l_db_link || ' tab ,user_synonyms@' ||l_db_link ||
130 ' syn WHERE tab.table_name = ''EDW_LOCAL_SYSTEM_PARAMETERS''' ||
131 ' and syn.table_name = tab.table_name and tab.owner=syn.table_owner ' ||
132 ' AND upper(tab.column_name) not in ( ''LAST_UPDATE_DATE'',''CREATION_DATE'')' ||
133 ' INTERSECT SELECT distinct tab.column_name from all_tab_columns tab ,'||
134 ' user_synonyms syn WHERE tab.table_name = ''EDW_LOCAL_SYSTEM_PARAMETERS'''||
135 ' and syn.table_name =tab.table_name and tab.owner=syn.table_owner '||
136 'AND upper(tab.column_name) not in ( ''LAST_UPDATE_DATE'',''CREATION_DATE'')';
137
138 l_count := 0;
139 l_colList :=NULL;
140 open cv for l_stmt;
141 loop
142 FETCH cv INTO l_temp;
143 EXIT WHEN cv%NOTFOUND;
144 if (l_count = 0) then
145 l_colList := l_colList||l_temp;
146 l_count := l_count + 1;
147 else
148 l_colList := l_colList||', '||l_temp;
149 end if;
150 end loop;
151
152 -- First delete existing date from the source db
153 cid := DBMS_SQL.open_cursor;
154 l_progress := '030';
155 DBMS_SQL.PARSE(cid, 'DELETE EDW_LOCAL_SYSTEM_PARAMETERS@'||l_db_link, dbms_sql.native);
156 l_dummy := dbms_sql.execute(cid);
157 l_stmt := 'INSERT INTO EDW_LOCAL_SYSTEM_PARAMETERS@'||l_db_link||' ( last_update_date, creation_date, ';
158 l_stmt := l_stmt ||l_colList||') SELECT :x1, :x1, '||l_colList||' FROM EDW_SYSTEM_PARAMETERS';
159 -- Now we can insert into these tables
160 l_progress := '060';
161
162
163 DBMS_SQL.PARSE(cid, l_stmt, dbms_sql.native);
164 DBMS_SQL.BIND_VARIABLE(cid, ':x1', remote_date);
165
166 l_dummy := dbms_sql.execute(cid);
167 l_progress := '070';
168
169 ELSE
170 inst_down := inst_down ||l_temp;
171 END IF;
172
173
174 END LOOP;
175
176 DBMS_SQL.close_cursor(cid);
177
178 CLOSE sources;
179 COMMIT;
180
181 EXCEPTION
182 when others then
183
184 ROLLBACK TO start_push_to_source;
185 CLOSE instances;
186 DBMS_SQL.close_cursor(cid);
187 edw_message_s.sql_error('push_to_source',l_progress,SQLCODE);
188 inst_down:=null;
189 raise;
190
191 END pushToSource;
192
193 function count_item_flex_segments(l_db_link varchar2) return number is
194 l_stmt varchar2(1000);
195 result number;
196 Type CurTyp is Ref Cursor;
197 cv CurTyp;
198 begin
199 edw_misc_util.globalnamesoff;
200 l_stmt:='select count(*) from fnd_id_flex_segments@'||l_db_link ||
201 ' where application_id=''401'' '||
202 ' and id_flex_code= ''MCAT'' '||
203 ' and enabled_flag=''Y'' '||
204 ' and id_flex_num= ' ||
205 ' ( select structure_id '||
206 ' from mtl_category_sets_b@'|| l_db_link ||
207 ' where category_set_id=''1000000006'' )';
208
209 open cv for l_stmt;
210 fetch cv into result;
211 close cv;
212 return result;
213 end count_item_flex_segments;
214
215 function is_vbh_available (l_db_link varchar2) return varchar2 is
216 l_stmt varchar2(1000);
217 result varchar2(5):=null;
218 Type CurTyp is Ref Cursor;
219 cv CurTyp;
220 begin
221 edw_misc_util.globalnamesoff;
222
223 l_stmt:='select ''YES'' VBH_INSTALLED from mtl_category_sets_b@'||l_db_link ||
224 ' where category_set_id = ''1000000006'' ';
225 open cv for l_stmt;
226 fetch cv into result;
227 close cv;
228 return result;
229 end is_vbh_available;
230
231 function is_eni_pkg_exist (l_db_link varchar2) return varchar2
232 is
233 l_child_supported varchar2(10);
234 e_not_supported exception;
235 PRAGMA exception_init(e_not_supported, -904);
236 l_stmt varchar2(1000);
237
238 begin
239 l_stmt := 'SELECT ENI_EDW_UTILS.IS_CHILD_ORG_SUPPORTED FROM DUAL@' ||l_db_link;
240 EXECUTE IMMEDIATE l_stmt INTO l_child_supported;
241 If upper(l_child_supported) = 'TRUE' then
242 RETURN 'TRUE';
243 else
244 RETURN 'FALSE';
245 end if;
246
247 EXCEPTION
248 WHEN e_not_supported THEN
249 RETURN 'FALSE';
250 WHEN OTHERS THEN
251 Raise;
252 end is_eni_pkg_exist;
253
254 End EDW_SYSTEM_PARAMS_PKG;
255