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