1 PACKAGE BODY FND_INSTALLATION AS
2 /* $Header: AFINSTLB.pls 120.2 2006/10/11 21:15:58 sdstratt ship $ */
3
4
5 --
6 -- Private Functions
7 --
8 FUNCTION private_get (appl_short_name in varchar2,
9 install_group_num in number,
10 status out nocopy varchar2,
11 industry out nocopy varchar2,
12 product_version out nocopy varchar2,
13 oracle_schema out nocopy varchar2,
14 tablespace out nocopy varchar2,
15 index_tablespace out nocopy varchar2,
16 temporary_tablespace out nocopy varchar2,
17 sizing_factor out nocopy number)
18 RETURN boolean;
19 --
20 -- Public Functions
21 --
22
23 FUNCTION get (appl_id IN INTEGER,
24 dep_appl_id IN INTEGER,
25 status OUT NOCOPY VARCHAR2,
26 industry OUT NOCOPY VARCHAR2)
27 RETURN boolean IS
28 l_appl_short_name varchar2(50);
29
30 l_status varchar2(1);
31 l_industry varchar2(1);
32 l_product_version varchar2(30);
33 l_oracle_schema varchar2(30);
34 l_tablespace varchar2(30);
35 l_index_tablespace varchar2(30);
36 l_temporary_tablespace varchar2(30);
37 l_sizing_factor number;
38 l_return boolean;
39 BEGIN
40 --
41 -- The get() function no longer uses the appl_id argument
42 --
43 -- It calls private_get(), which gets the information for you
44 -- based solely on the dep_appl_id and the current schema
45 --
46 -- get() may return different information with the same arguments
47 -- if you connect to a different schema
48 --
49 status := 'N';
50 industry := 'N';
51
52 select application_short_name
53 into l_appl_short_name
54 from fnd_application
55 where application_id = dep_appl_id;
56
57 l_return := private_get(l_appl_short_name, null, l_status, l_industry,
58 l_product_version, l_oracle_schema, l_tablespace,
59 l_index_tablespace, l_temporary_tablespace,
60 l_sizing_factor);
61 status := l_status;
62 industry := l_industry;
63 return(l_return);
64 EXCEPTION
65 -- This should only execute if an invalid dep_appl_id was passed
66 when others then
67 return(FALSE);
68 END;
69
70 FUNCTION get_app_info (application_short_name in varchar2,
71 status out nocopy varchar2,
72 industry out nocopy varchar2,
73 oracle_schema out nocopy varchar2)
74 RETURN boolean IS
75 l_status varchar2(1);
76 l_industry varchar2(1);
77 l_product_version varchar2(30);
78 l_oracle_schema varchar2(30);
79 l_tablespace varchar2(30);
80 l_index_tablespace varchar2(30);
81 l_temporary_tablespace varchar2(30);
82 l_sizing_factor number;
83 l_return boolean;
84 BEGIN
85 --
86 -- get_app_info() may return different information if you call it
87 -- from a different schema
88 -- See notes on get() above
89 --
90 l_return := private_get(application_short_name, null, l_status, l_industry,
91 l_product_version, l_oracle_schema, l_tablespace,
92 l_index_tablespace, l_temporary_tablespace,
93 l_sizing_factor);
94 status := l_status;
95 industry := l_industry;
96 oracle_schema := l_oracle_schema;
97 return(l_return);
98 END;
99
100 FUNCTION get_app_info_other (application_short_name in varchar2,
101 target_schema in varchar2,
102 status out nocopy varchar2,
103 industry out nocopy varchar2,
104 oracle_schema out nocopy varchar2)
105 RETURN boolean IS
106 l_install_group_num number;
107 l_status varchar2(1);
108 l_industry varchar2(1);
109 l_product_version varchar2(30);
110 l_oracle_schema varchar2(30);
111 l_tablespace varchar2(30);
112 l_index_tablespace varchar2(30);
113 l_temporary_tablespace varchar2(30);
114 l_sizing_factor number;
115 l_return boolean;
116 BEGIN
117 --
118 -- get_app_info_other() will return consistent information every time
119 -- you call it, because it ignores the current schema and uses
120 -- the target_schema argument instead
121 --
122 status := 'N';
123 industry := 'N';
124 oracle_schema := null;
125
126 -- Derive install_group_num from the parameter target_schema
127
128 select min(install_group_num) into l_install_group_num
129 from fnd_oracle_userid
130 where oracle_username = target_schema;
131
132 l_return := private_get(application_short_name, l_install_group_num,
133 l_status, l_industry,
134 l_product_version, l_oracle_schema, l_tablespace,
135 l_index_tablespace, l_temporary_tablespace,
136 l_sizing_factor);
137 status := l_status;
138 industry := l_industry;
139 oracle_schema := l_oracle_schema;
140 return(l_return);
141 EXCEPTION
142 -- This should only execute if the target schema was not registered in
143 -- FND_ORACLE_USERID
144 when others then
145 return(FALSE);
146 END;
147
148
149 FUNCTION private_get (appl_short_name in varchar2,
150 install_group_num in number,
151 status out nocopy varchar2,
152 industry out nocopy varchar2,
153 product_version out nocopy varchar2,
154 oracle_schema out nocopy varchar2,
155 tablespace out nocopy varchar2,
156 index_tablespace out nocopy varchar2,
157 temporary_tablespace out nocopy varchar2,
158 sizing_factor out nocopy number)
159 RETURN boolean IS
160
161 BEGIN
162
163 /*
164 The plan:
165
166 Set default values: status=N, industry=N, others null
167
168 First, try to get exactly one row from FND_PRODUCT_INSTALLATIONS
169 for the product
170
171 if found exactly one row,
172 return TRUE with values from that row
173 if no rows,
174 return TRUE with default values (status=N, industry=N, others null)
175 if more than one row,
176 go on to next step
177
178 At this point we know we have multiple rows for the product
179 in FND_PRODUCT_INSTALLATIONS.
180
181 NOTE: In R11 this condition should never
182 occur and therefore the remaining logic should be removed then.
183 Also note that in R11, the function get_app_info_other is obsolete
184 because it is only necessary with multiple installs. Also note that
185 the argument installation_group_num to private_get is no longer
186 needed as well.
187
188 Next, if install_group_num is null then get the install_group_num from
189 fnd_oracle_userid for the current user.
190
191 Next, try to get exactly one row from FND_PRODUCT_INSTALLATIONS
192 for the product, install_group_num pair
193
194 if found exactly one row,
195 return TRUE with values from that row
196 if no rows,
197 return TRUE with default values (status=N, industry=N, others null)
198 if more than one row,
199 return FALSE with default values (status=N, industry=N, others null)
200 this case shouldn't happen
201
202 Finally if an exception is raised return FALSE
203
204
205 NOTES:
206 Handles all cases in Single Oracle Accounts installation,
207 since the select from FPI will always return either one row or no rows
208 See note above as well as note below in the code.
209
210 BUGS:
211 If you do not pass install_group_num and the user account has a
212 null install_group_num in fnd_oracle_userid and you have multiple installs
213 of the product you will not be able to determine the information about
214 the product.
215
216 If you do not pass the install_group_num and the user account has a
217 an install_group_num of 0 and you have multiple installs of the
218 product and the product is a multiple install product (ie not
219 install_group_num 0) you will not be able to determine the information
220 about the product. (eg. asking from applsys info about AP when
221 multiple APs exist)
222
223 Both of these problems go away in R11 when no more multiple installs.
224 */
225
226 -- dbms_output.put_line( 'entering private_get()' );
227
228 status := 'N';
229 industry := 'N';
230 product_version := null;
231 oracle_schema := null;
232 tablespace := null;
233 index_tablespace := null;
234 temporary_tablespace := null;
235 sizing_factor := null;
236
237 /*------------------------------------------------------------+
238 | Get info regarding installs |
239 +------------------------------------------------------------*/
240 declare
241
242 cursor FPI_CURSOR is
243 select fpi.status,
244 fpi.industry,
245 fpi.product_version,
246 fou.oracle_username,
247 fpi.tablespace,
248 fpi.index_tablespace,
249 fpi.temporary_tablespace,
250 fpi.sizing_factor
251 from FND_PRODUCT_INSTALLATIONS FPI,
252 FND_ORACLE_USERID FOU,
253 FND_APPLICATION FA
254 where fpi.application_id = fa.application_id
255 and fpi.oracle_id = fou.oracle_id
256 and fa.application_short_name = private_get.appl_short_name;
257
258 l_status varchar2(1);
259 l_industry varchar2(1);
260 l_product_version varchar2(30);
261 l_oracle_schema varchar2(30);
262 l_tablespace varchar2(30);
263 l_index_tablespace varchar2(30);
264 l_temporary_tablespace varchar2(30);
265 l_sizing_factor number;
266
267 begin
268
269 open FPI_CURSOR;
270
271 fetch FPI_CURSOR
272 into l_status, l_industry, l_product_version, l_oracle_schema,
273 l_tablespace, l_index_tablespace, l_temporary_tablespace,
274 l_sizing_factor;
275
276 if FPI_CURSOR%NOTFOUND then
277 -- dbms_output.put( 'exiting private_get(): ' );
278 -- dbms_output.put_line( 'product not in FPI' );
279 close FPI_CURSOR;
280 return(TRUE);
281 end if;
282
283 -- save results of fetch
284
285 status := l_status;
286 industry := l_industry;
287 product_version := l_product_version;
288 oracle_schema := l_oracle_schema;
289 tablespace := l_tablespace;
290 index_tablespace := l_index_tablespace;
291 temporary_tablespace := l_temporary_tablespace;
292 sizing_factor := l_sizing_factor;
293
294 fetch FPI_CURSOR
295 into l_status, l_industry, l_product_version, l_oracle_schema,
296 l_tablespace, l_index_tablespace, l_temporary_tablespace,
297 l_sizing_factor;
298
299 if FPI_CURSOR%NOTFOUND then
300 -- exactly one row. return values from that row
301 -- dbms_output.put_line( 'exiting private_get(): only one row in FPI');
302 close FPI_CURSOR;
303 return(TRUE);
304 else
305 -- more than one row. go on to next block
306 status := 'N';
307 industry := 'N';
308 product_version := null;
309 oracle_schema := null;
310 tablespace := null;
311 index_tablespace := null;
312 temporary_tablespace := null;
313 sizing_factor := null;
314 close FPI_CURSOR;
315 end if;
316
317 end; -- look for single row in FPI
318
319 /*------------------------------------------------------------+
320 | Get info for application and install_group_num |
321 | NOTE: All of the following code can be removed in R11 |
322 | as there will only be one install of the products. |
323 +------------------------------------------------------------*/
324
325 --
326 -- If we got here, we must be looking for information for a MOA product
327 -- that has been installed in more than one schema.
328 --
329 -- If we had been looking for a SOA product, we would have found it
330 -- already, because SOA products (install_group_num = 0) never have
331 -- more than one row in FND_PRODUCT_INSTALLATIONS.
332 --
333 -- If our reference schema is another MOA product account, or some other
334 -- schema registered with install_group_num != 0, we should find
335 -- exactly one row (or no rows) using the query below. In this case,
336 -- we exit with success.
337 --
338 -- If our reference schema is a SOA account (install_group_num = 0),
339 -- we cannot accurately determine the information.
340 -- In this case we exit with failure.
341 --
342
343 --
344 -- Why can't we determine the information for a SOA schema?
345 --
346 -- Consider this example:
347 -- o Multiple Sets of Books install, with two sets of books
348 -- o AP fully-installed in the first set of books, shared in second
349 --
350 -- Which AP should GL point to?
351 --
352 -- Since GL handles both sets of books in one schema, neither AP is
353 -- really correct.
354 -- The AP in the first set of books only contains information for the
355 -- first set of books, and the AP in the second set of books only
356 -- contains information for the second set of books.
357 --
358
359 declare
360
361 cursor FPI2_CURSOR (c_install_group_num number) is
362 select fpi.status,
363 fpi.industry,
364 fpi.product_version,
365 fou.oracle_username,
366 fpi.tablespace,
367 fpi.index_tablespace,
368 fpi.temporary_tablespace,
369 fpi.sizing_factor
370 from FND_PRODUCT_INSTALLATIONS FPI,
371 FND_ORACLE_USERID FOU,
372 FND_APPLICATION FA
373 where fpi.application_id = fa.application_id
374 and fpi.oracle_id = fou.oracle_id
375 and fa.application_short_name = private_get.appl_short_name
376 and fpi.install_group_num = c_install_group_num;
377
378 l_status varchar2(1);
379 l_industry varchar2(1);
380 l_product_version varchar2(30);
381 l_oracle_schema varchar2(30);
382 l_tablespace varchar2(30);
383 l_index_tablespace varchar2(30);
384 l_temporary_tablespace varchar2(30);
385 l_sizing_factor number;
386 l_install_group_num number;
387
388 begin
389
390 -- If no install_group_num was passed, then derive it from
391 -- the current user schema
392
393 if private_get.install_group_num is null then
394
395 select install_group_num
396 into l_install_group_num
397 from fnd_oracle_userid
398 where oracle_username = user
399 and install_group_num is not null;
400
401 else
402
403 l_install_group_num := private_get.install_group_num;
404
405 end if;
406
407 --
408 -- If l_install_group_num is zero, return false
409 -- See long comment above
410 --
411
412 if l_install_group_num = 0 then
413 -- dbms_output.put('exiting private_get(): ');
414 -- dbms_output.put_line('trying to get MOA info from SOA schema');
415 return(FALSE);
416 end if;
417
418 open FPI2_CURSOR(l_install_group_num);
419
420 fetch FPI2_CURSOR
421 into l_status, l_industry, l_product_version, l_oracle_schema,
422 l_tablespace, l_index_tablespace, l_temporary_tablespace,
423 l_sizing_factor;
424
425 if FPI2_CURSOR%NOTFOUND then
426 -- dbms_output.put('exiting private_get(): ');
427 -- dbms_output.put_line('app not in this install group?');
428 close FPI2_CURSOR;
429 return(TRUE);
430 end if;
431
432 -- save returned values
433
434 status := l_status;
438 tablespace := l_tablespace;
435 industry := l_industry;
436 product_version := l_product_version;
437 oracle_schema := l_oracle_schema;
439 index_tablespace := l_index_tablespace;
440 temporary_tablespace := l_temporary_tablespace;
441 sizing_factor := l_sizing_factor;
442
443 fetch FPI2_CURSOR
444 into l_status, l_industry, l_product_version, l_oracle_schema,
445 l_tablespace, l_index_tablespace, l_temporary_tablespace,
446 l_sizing_factor;
447
448 if FPI2_CURSOR%NOTFOUND then
449 -- exactly one row. return values from that row
450 -- dbms_output.put('exiting private_get(): MOA product - ');
451 -- dbms_output.put_line('exactly one row found');
452 close FPI2_CURSOR;
453 return(TRUE);
454 else
455 -- more than one row. data corruption?
456 -- dbms_output.put('exiting private_get: ');
457 -- dbms_output.put_line('bad data in FND_ORACLE_USERID?');
458 status := 'N';
459 industry := 'N';
460 product_version := null;
461 oracle_schema := null;
462 tablespace := null;
463 index_tablespace := null;
464 temporary_tablespace := null;
465 sizing_factor := null;
466 return(FALSE);
467 end if;
468
469 end; -- multiple FPI rows
470
471 -- dbms_output.put_line( 'exiting private_get(): should not reach here' );
472 return(FALSE);
473
474 EXCEPTION
475 when others then
476 -- dbms_output.put_line( 'exiting private_get() with following error:' );
477 -- dbms_output.put_line( sqlerrm );
478 return(FALSE);
479
480 END private_get;
481
482 END FND_INSTALLATION;
[Home] [Help]