1 package body ad_util as
2 /* $Header: adutilb.pls 115.7 2004/06/04 14:31:35 sallamse ship $ */
3 --------------------------------------------------
4 --
5 -- PUBLIC FUNCTION UPDATE_COLUMN
6 --
7 -- This is public in case the list in up_oid
8 -- is not complete.
9 --
10 -- update one number column for one table
11 --
12 --------------------------------------------------
13 procedure update_column
14 (p_old_oid in number,
15 p_new_oid in number,
16 p_tab_name in varchar2,
17 p_col_name in varchar2,
18 p_option in varchar2)
19 is
20 p_cnt number;
21 p_cursor integer;
22 statement varchar2(500);
23 rows_processed integer;
24 p_tab_owner varchar2(30);
25 p_apps_username varchar2(30);
26 begin
27
28 begin
29 select upper(oracle_username) into p_apps_username
30 from fnd_oracle_userid
31 where oracle_id between 900 and 999
32 and read_only_flag = 'U';
33 exception
34 when others then
35 dbms_output.put_line('-- Unable to get APPS username.');
36 return;
37 end;
38
39 begin
40 select upper(table_owner) into p_tab_owner
41 from dba_synonyms
42 where synonym_name = upper(p_tab_name)
43 and table_name = upper(p_tab_name)
44 and owner = upper(p_apps_username);
45 exception
46 when others then
47 dbms_output.put_line('-- Unable to determine owner for table ' ||
48 p_tab_name ||'.');
49 return;
50 end;
51
52 begin
53 select 1 into p_cnt
54 from all_tables
55 where table_name = upper(p_tab_name)
56 and owner = upper(p_tab_owner);
57
58 exception
59 when no_data_found then
60 dbms_output.put_line('-- Table '||p_tab_name||' does not exist.');
61 return;
62 end;
63
64 begin
65 select 1 into p_cnt
66 from all_tab_columns
67 where table_name = upper(p_tab_name)
68 and column_name = upper(p_col_name)
69 and owner = upper(p_tab_owner);
70
71 exception
72 when no_data_found then
73 dbms_output.put_line('-- Column '||p_col_name||
74 ' is not a column of table '||
75 p_tab_name||'.');
76 return;
77 end;
78
79 statement := 'update '||p_tab_name||' set '||p_col_name||' = '||
80 p_new_oid||' where '||p_col_name||' = '||p_old_oid;
81 dbms_output.put_line(statement||';');
82
83 if upper(p_option) = 'N' then
84 return;
85 end if;
86
87 p_cursor := dbms_sql.open_cursor;
88 dbms_sql.parse(p_cursor, statement, dbms_sql.native);
89 rows_processed := dbms_sql.execute(p_cursor);
90 dbms_output.put_line(' '||rows_processed||' rows updated.');
91 dbms_sql.close_cursor(p_cursor);
92
93 exception
94 when others then
95 dbms_sql.close_cursor(p_cursor);
96 raise;
97
98 end update_column;
99
100 --------------------------------------------------
101 --
102 -- PRIVATE FUNCTION UP_OID
103 --
104 -- update all known tables related to oracle_id
105 --
106 -- This is a private function. This is called by
107 -- update_oracle_id, which is overloaded.
108 --
109 --------------------------------------------------
110 procedure up_oid
111 (p_release in varchar2, /* for future use */
112 p_old_oid in number,
113 p_new_oid in number,
114 p_option in varchar2)
115 is
116 begin
117
118 --
119 -- 03/12/98
120 -- The list below is currently in sync with the corresponding lists in
121 -- $ad/src/database/aidafo.lpc
122 -- If you change anything in the list below, you must also make the same
123 -- change in $ad/src/database/aidafo.lpc
124 --
125
126 update_column(p_old_oid, p_new_oid, 'AD_MERGED_TABLES',
127 'ORACLE_ID', p_option);
128 update_column(p_old_oid, p_new_oid, 'ALR_ACTION_SET_CHECKS',
129 'ORACLE_ID', p_option);
130 update_column(p_old_oid, p_new_oid, 'ALR_ALERT_INSTALLATIONS',
131 'ORACLE_ID', p_option);
132 update_column(p_old_oid, p_new_oid, 'ALR_ORACLE_MAIL_ACCOUNTS',
133 'ORACLE_ID', p_option);
134 update_column(p_old_oid, p_new_oid, 'ALR_RESPONSE_ACTION_HISTORY',
135 'ORACLE_ID', p_option);
136 update_column(p_old_oid, p_new_oid, 'FND_AUDIT_SCHEMAS',
137 'ORACLE_ID', p_option);
138 update_column(p_old_oid, p_new_oid, 'FND_CONCURRENT_COMPLEX_LINES',
139 'TYPE_ID', p_option);
140 update_column(p_old_oid, p_new_oid, 'FND_CONCURRENT_QUEUE_CONTENT',
141 'TYPE_ID', p_option);
142 -----------------------------------------
143 -- for release 9 columns
144 -- in release 9, type_id was oracle_id
145 --------------------------------------------------------------------
146 update_column(p_old_oid, p_new_oid, 'FND_CONCURRENT_COMPLEX_LINES',
147 'ORACLE_ID', p_option);
148 update_column(p_old_oid, p_new_oid, 'FND_CONCURRENT_QUEUE_CONTENT',
149 'ORACLE_ID', p_option);
150 update_column(p_old_oid, p_new_oid, 'FND_RESPONSIBILITY',
151 'ORACLE_ID', p_option);
152 update_column(p_old_oid, p_new_oid, 'FND_RESPONSIBILITY',
153 'READ_ONLY_ORACLE_ID', p_option);
154 ----------------- end of release 9 columns --------------------------
155
156 update_column(p_old_oid, p_new_oid, 'FND_CONCURRENT_REQUESTS',
157 'ORACLE_ID', p_option);
158 update_column(p_old_oid, p_new_oid, 'FND_DATA_GROUP_UNITS',
159 'ORACLE_ID', p_option);
160 update_column(p_old_oid, p_new_oid, 'FND_DATA_GROUP_UNITS',
161 'READ_ONLY_ORACLE_ID', p_option);
162 update_column(p_old_oid, p_new_oid, 'FND_DOC_SEQUENCE_ACCESS',
163 'ORACLE_ID', p_option);
164 update_column(p_old_oid, p_new_oid, 'FND_ORACLE_USERID',
165 'READ_ONLY_ORACLE_ID', p_option);
166 update_column(p_old_oid, p_new_oid, 'FND_REGRESSION_SUITES',
167 'ORACLE_ID', p_option);
168 update_column(p_old_oid, p_new_oid, 'FND_REGRESSION_TESTS',
169 'ORACLE_ID', p_option);
170 ----------------------------------------------------------------------------
171 -- FND_PRODUCT% and FND_MODULE% tables are updated separately by AutoInstall
172 -- but we update them here in the PL/SQL API plus we also need to update
173 -- ORACLE_ID in FND_ORACLE_USERID itself.
174 ----------------------------------------------------------------------------
175 update_column(p_old_oid, p_new_oid, 'FND_MODULE_INSTALLATIONS',
176 'ORACLE_ID', p_option);
177 update_column(p_old_oid, p_new_oid, 'FND_PRODUCT_INSTALLATIONS',
178 'ORACLE_ID', p_option);
179 update_column(p_old_oid, p_new_oid, 'FND_PRODUCT_DEPENDENCIES',
180 'ORACLE_ID', p_option);
181 update_column(p_old_oid, p_new_oid, 'FND_PRODUCT_DEPENDENCIES',
182 'REQUIRED_ORACLE_ID', p_option);
183 update_column(p_old_oid, p_new_oid, 'FND_ORACLE_USERID',
184 'ORACLE_ID', p_option);
185
186 end up_oid;
187
188 --------------------------------------------------
189 --
190 -- OVERLOADED PUBLIC FUNCTION UPDATE_ORACLE_ID
191 --
192 -- user can specify the new oracle_id he wants to
193 -- use in case he wants to consolidate schemas.
194 --
195 --------------------------------------------------
196 procedure update_oracle_id
197 (p_release in varchar2,
198 p_old_oid in number,
199 p_new_oid in number,
200 p_option in varchar2)
201 is
202 begin
203
204 up_oid(p_release, p_old_oid, p_new_oid, p_option);
205
206 end update_oracle_id;
207
208 procedure update_oracle_id
209 (p_release in varchar2,
210 p_old_oid in number,
211 p_new_oid in number)
212 is
213 begin
214
215 update_oracle_id(
216 p_release => p_release,
217 p_old_oid => p_old_oid,
218 p_new_oid => p_new_oid,
219 p_option => 'N');
220
221 end update_oracle_id;
222
223
224 --------------------------------------------------
225 --
226 -- OVERLOADED PUBLIC FUNCTION UPDATE_ORACLE_ID
227 --
228 -- here the new oracle_id is generated from the
229 -- sequence FND_ORACLE_USERID_S.nextval
230 --
231 --------------------------------------------------
232 procedure update_oracle_id
233 (p_release in varchar2,
234 p_old_oid in number,
235 p_option in varchar2)
236 is
237 p_new_oid number;
238 begin
239
240 dbms_output.put_line('-- select fnd_oracle_userid_s.nextval from dual;');
241
242 select fnd_oracle_userid_s.nextval into p_new_oid
243 from dual;
244
245 up_oid(p_release, p_old_oid, p_new_oid, p_option);
246
247 end update_oracle_id;
248
249 procedure update_oracle_id
250 (p_release in varchar2,
251 p_old_oid in number)
252 is
253 begin
254
255 update_oracle_id(
256 p_release => p_release,
257 p_old_oid => p_old_oid,
258 p_option => 'N');
259
260 end update_oracle_id;
261
262 --------------------------------------------------
263 --
264 -- PRIVATE FUNCTION : IS_VALID_APPL_SHORT_NAME
265 --
266 --------------------------------------------------
267 procedure is_valid_appl_short_name
268 (p_apps_short_name varchar2,
269 p_apps_id in out nocopy number)
270 is
271 begin
272
273 select application_id into p_apps_id
274 from fnd_application
275 where application_short_name = upper(p_apps_short_name);
276
277 exception
278 when no_data_found then
279 raise_application_error(-20000, 'Invalid application_short_name: "'||
280 p_apps_short_name||'"');
281
282 end is_valid_appl_short_name;
283
284 --------------------------------------------------
285 --
286 -- PUBLIC FUNCTION SET_PROD_TO_SHARED
287 --
288 -- This function changes a product's status in fnd_product_installations
289 -- to be 'S' if the db_status is 'I', and inserts a row into
290 -- fnd_data_group_units if it's not already there.
291 -- If product's status is already 'I' or 'S', doesn't do anything
292 --
293 --
294 --
295 --------------------------------------------------
296 procedure set_prod_to_shared
297 (p_release in varchar2,
298 p_apps_short_name in varchar2)
299 is
300 x_oracle_id fnd_product_installations.oracle_id%TYPE;
301 x_db_status fnd_product_installations.db_status%TYPE;
302 x_status fnd_product_installations.status%TYPE;
303 x_ign fnd_product_installations.install_group_num%TYPE;
304
305 appid number; /* application id for the product */
306 errmsg varchar2(240);
307
308 cursor x_cursor (appl_id in number) is
309 select oracle_id, db_status, status, install_group_num
310 from fnd_product_installations
311 where application_id = appl_id;
312
313 begin
314 --
315 -- check the application short name and get application_id if valid
316 --
317 is_valid_appl_short_name(p_apps_short_name, appid);
318
319 FOR c in x_cursor(appid) LOOP
320
321 x_oracle_id := c.oracle_id;
322 x_db_status := c.db_status;
323 x_status := c.status;
324 x_ign := c.install_group_num;
325
326 -- dbms_output.put_line(x_oracle_id||x_db_status||x_ign);
327
328 if x_db_status = 'I' then
329 if x_status is null or (x_status <> 'I' and x_status <> 'S') then
330 --
331 -- set the status to be licensed
332 --
333 errmsg := 'update fnd_product_installations for oracle_id '||
337 set status = 'S',
334 x_oracle_id;
335 dbms_output.put_line(errmsg);
336 update fnd_product_installations
338 last_update_date = sysdate
339 where application_id = appid
340 and oracle_id = x_oracle_id;
341
342 if SQL%ROWCOUNT = 1 then
343 dbms_output.put_line(SQL%ROWCOUNT||
344 ' row updated in fnd_product_installations.');
345 else
346 dbms_output.put_line(SQL%ROWCOUNT||
347 ' rows updated in fnd_product_installations.');
348 end if;
349
350 --
351 -- For application id (0, 1, 3, 50, 160) the application_id
352 -- to use in 10.6 is 0. For everything else is the oracle_id
353 -- of the corresponding apps schema.
354 --
355
356 -- if x_ign = 0 ; then we need to insert into every set of books
357
358 if x_ign = 0 then
359
360 -- insert a row of this product for every apps schema
361 -- decode is used to fulfill the requirement above.
362
363 insert into fnd_data_group_units
364 (application_id,
365 data_group_id,
366 last_update_date,
367 last_updated_by,
368 creation_date,
369 created_by,
370 last_update_login,
371 oracle_id
372 )
373 select distinct appid,
374 dg.data_group_id,
375 sysdate,
376 1,
377 sysdate,
378 1,
379 0,
380 decode(substr(p_release, 1, 4), '10.6', decode(appid,
381 0, 0,
382 1, 0,
383 3, 0,
384 50, 0,
385 160,0,
386 u.oracle_id),
387 u.oracle_id)
388 from fnd_data_groups dg,
389 fnd_data_group_units du,
390 fnd_oracle_userid u
391 where du.data_group_id = dg.data_group_id
392 and du.created_by = 1
393 and dg.created_by = 1
394 and du.oracle_id = u.oracle_id
395 and u.oracle_id between 900 and 999
396 and not exists
397 ( select 'x'
398 from fnd_data_group_units u2
399 where u2.data_group_id = dg.data_group_id
400 and u2.application_id = appid
401 );
402
403 if SQL%ROWCOUNT = 1 then
404 dbms_output.put_line(SQL%ROWCOUNT||
405 ' row inserted into fnd_data_group_units.');
406 else
407 dbms_output.put_line(SQL%ROWCOUNT||
408 ' rows inserted into fnd_data_group_units.');
409 end if;
410
411 exit; -- exit the LOOP
412 end if;
413
414 --
415 -- If here, we have an MOA product, and x_ign should > 0
416 --
417 --
418 -- insert a row into fnd_data_group_units if not already exists.
419 --
420 errmsg := 'insert into fnd_data_group_units';
421
422 insert into fnd_data_group_units
423 (application_id, data_group_id, last_update_date, last_updated_by,
424 creation_date, created_by, last_update_login, oracle_id)
425 select distinct appid,
426 dg.data_group_id,
427 sysdate,
428 1,
429 sysdate,
430 1,
431 0,
432 u.oracle_id
433 from fnd_data_groups dg,
434 fnd_data_group_units du,
435 fnd_oracle_userid u
436 where du.data_group_id = dg.data_group_id
437 and du.created_by = 1
438 and dg.created_by = 1
439 and du.oracle_id = u.oracle_id
440 and u.install_group_num = x_ign
441 and u.oracle_id between 900 and 999
442 and not exists
443 ( select 'x'
444 from fnd_data_group_units u2
445 where u2.data_group_id = dg.data_group_id
446 and u2.application_id = appid
447 );
448
449 if SQL%ROWCOUNT = 1 then
450 dbms_output.put_line(SQL%ROWCOUNT||
451 ' row inserted into fnd_data_group_units.');
452 else
453 dbms_output.put_line(SQL%ROWCOUNT||
454 ' rows inserted into fnd_data_group_units.');
455 end if;
456
457
458 end if;
459 end if;
460 END LOOP;
461
462 end set_prod_to_shared;
463
464 end ad_util;
[Home] [Help]