1 package body ad_version_util as
2 -- $Header: aduverb.pls 120.2 2006/08/25 18:33:12 vlim noship $
3
4 --
5 -- constant values used when comparing two patchset names
6 --
7
8 PATCH_LEVEL_SAME CONSTANT integer := 0;
9 PATCH_LEVEL1_HIGHER CONSTANT integer := -1;
10 PATCH_LEVEL2_HIGHER CONSTANT integer := 1;
11
12 VERCOMP_SEPARATOR CONSTANT varchar2(1) := '.';
13
14 --
15 -- Function to get base release name. This name will be used when
16 -- contructing patch level name for a product.
17 --
18
19 function get_base_release
20 return varchar2
21 is
22 begin
23 return('R12');
24 end;
25
26 --
27 -- Return product abbreviation given a product shortname
28 --
29 -- Raises an exception if the product shortname is not found in
30 -- FND_APPLICATION table
31 --
32
33 function get_appl_abbr
34 (p_appl_shortname in varchar2)
35 return varchar2
36 is
37 l_dummy varchar2(1);
38 begin
39
40 --
41 -- first check if this is a valid application
42 --
43
44 begin
45 select null
46 into l_dummy
47 from fnd_application
48 where application_short_name = upper(p_appl_shortname);
49 exception
50 when NO_DATA_FOUND then
51 raise_application_error(-20001,
52 'Invalid application shortname : '||p_appl_shortname);
53 end;
54
55 if (p_appl_shortname = 'SQLAP') then
56 return('AP');
57 elsif (p_appl_shortname = 'SQLGL') then
58 return('GL');
59 elsif (p_appl_shortname = 'OFA') then
60 return('FA');
61 else
62 return(p_appl_shortname);
63 end if;
64 end;
65
66 function get_appl_id
67 (p_appl_sn in varchar2) return number
68 is
69 l_appl_id number;
70 begin
71 select application_id
72 into l_appl_id
73 from fnd_application
74 where application_short_name = upper(p_appl_sn);
75
76 return(l_appl_id);
77 end;
78
79 --
80 -- function to check the validity of a patch level
81 --
82 -- The patch level is of the form
83 -- [<BASE RELEASE>.<PRODUCT>.<PATCHSET NAME>]{.<REVISION>}
84 --
85
86 function validate_patch_level
87 (p_patch_level in varchar2,
88 p_product_shortname in varchar2,
89 p_error_msg out nocopy varchar2)
90 return boolean
91 is
92 l_patch_base varchar2(30);
93 l_patch_appl_shortname varchar2(30);
94 l_patch_patchset_name varchar2(30);
95 l_patch_patchset_revision varchar2(30);
96 l_patchset_revision number;
97
98 l_base_release varchar2(30);
99 l_appl_shortname varchar2(30);
100 begin
101 get_patch_level_details(p_patch_level,
102 l_patch_base,
103 l_patch_appl_shortname,
104 l_patch_patchset_name,
105 l_patch_patchset_revision);
106
107 l_base_release := get_base_release;
108
109 if (nvl(l_patch_base, 'UNKNOWN') <> l_base_release) then
110 p_error_msg := 'Incorrect base release "'||l_patch_base||'" found '||
111 'when expecting "'||l_base_release||'"';
112 return(FALSE);
113 end if;
114
115 l_appl_shortname := get_appl_abbr(p_product_shortname);
116
117 if (l_appl_shortname <> nvl(l_patch_appl_shortname, 'UNKNOWN')) then
118 p_error_msg := 'Incorrect product abbreviation "'||
119 l_patch_appl_shortname||'" found '||
120 'when expecting "'||l_appl_shortname||'"';
121 return(FALSE);
122 end if;
123
124 if (length(l_patch_patchset_name) <> 1
125 or
126 l_patch_patchset_name not between 'A' and 'Z') then
127 p_error_msg := 'Incorrect patchset name "'||l_patch_patchset_name||
128 '". A patchset name must be a character between A to Z.';
129 return(FALSE);
130 end if;
131
132 if (l_patch_patchset_revision is not null)
133 then
134 if (replace(translate(l_patch_patchset_revision,
135 '0123456789', ' '),
136 ' ', '') is not null)
137 then
138 p_error_msg := 'Incorrect patchset revision format "'||
139 l_patch_patchset_revision||
140 '". A patchset revision must be a number from 1 to 99.';
141 return(FALSE);
142 end if;
143
144 if (not(to_number(l_patch_patchset_revision) between 1 and 99)) then
145 p_error_msg := 'Incorrect patchset revision "'||
146 l_patch_patchset_revision||
147 '". A patchset revision must be a number from 1 to 99.';
148 return(FALSE);
149 end if;
150 end if;
151
152 return(TRUE);
153 end;
154
155
156 --
157 -- Compares two patch levels
158 -- returns 0 : patch levels are same
159 -- 1 : patch level 2 is higher than patch level 1
160 -- or
161 -- patch level 2 is not null and patch level 1 is null
162 -- -1 : patch level 1 is higher than patch level 2
163 -- or
164 -- patch level 1 is not null and patch level 2 is null
165 --
166
167 function compare_patch_levels
168 (p_level1 in varchar2,
169 p_level2 in varchar2) return number
170 is
171
172 l_base1 varchar2(30);
173 l_appl_shortname1 varchar2(30);
174 l_patchset_name1 varchar2(30);
175 l_patchset_rev1 varchar2(30);
176 l_base2 varchar2(30);
177 l_appl_shortname2 varchar2(30);
178 l_patchset_name2 varchar2(30);
179 l_patchset_rev2 varchar2(30);
180
181 begin
182
183 if (upper(p_level1) = upper(p_level2)) then
184 return(PATCH_LEVEL_SAME);
185 end if;
186
187 if (p_level1 is not null and p_level2 is null) then
188 return(PATCH_LEVEL1_HIGHER);
189 end if;
190
191 if (p_level1 is null and p_level2 is not null) then
192 return(PATCH_LEVEL2_HIGHER);
193 end if;
194
195 get_patch_level_details(p_level1,
196 l_base1, l_appl_shortname1, l_patchset_name1,
197 l_patchset_rev1);
198 get_patch_level_details(p_level2,
199 l_base2, l_appl_shortname2, l_patchset_name2,
200 l_patchset_rev2);
201
202 if (l_base1 <> l_base2) then
203 raise_application_error(-20001,
204 'The base release should be same when comparing patchset versions '||
205 l_base1||' vs '||l_base2);
206 end if;
207
208 if (l_appl_shortname1 <> l_appl_shortname2) then
209 raise_application_error(-20001,
210 'The product should be same when comparing patchset versions '||
211 l_appl_shortname1||' vs '||l_appl_shortname2);
212 end if;
213
214 if (l_patchset_name1 <> l_patchset_name2) then
215 if (l_patchset_name1 > l_patchset_name2) then
216 return(PATCH_LEVEL1_HIGHER);
217 else
218 return(PATCH_LEVEL2_HIGHER);
219 end if;
220 end if;
221
222 if (nvl(l_patchset_rev1, -1) = nvl(l_patchset_rev2, -1)) then
223 return(PATCH_LEVEL_SAME);
224 end if;
225
226 if (nvl(to_number(l_patchset_rev1), -1) >
227 nvl(to_number(l_patchset_rev2), -1)) then
228 return(PATCH_LEVEL1_HIGHER);
229 else
230 return(PATCH_LEVEL2_HIGHER);
231 end if;
232
233 end;
234
235 --
236 -- get current patch level from the database
237 --
238 -- this procedure returns NULL for patch levels
239 -- that do not belong to the current base release;
240 -- this procedure is only called within this package.
241 -- (created to fix bugs 5376688 and 5484176)
242 --
243
244 procedure get_product_patch_level_priv
245 (p_appl_id in number,
246 p_patch_level out nocopy varchar2)
247 is
248 l_patch_level varchar2(30);
249 begin
250 select patch_level
251 into l_patch_level
252 from fnd_product_installations
253 where application_id = p_appl_id
254 and install_group_num in (0, 1)
255 and rownum = 1;
256
257 p_patch_level := l_patch_level;
258
259 --
260 -- return NULL if earlier releases
261 --
262 if (l_patch_level like '10.7%'
263 or
264 l_patch_level like '11.0%'
265 or
266 l_patch_level like '11i%')
267 then
268 p_patch_level := null;
269 end if;
270 end;
271
272 --
273 -- get current patch level from the database
274 --
275 -- this procedure returns NULL for patch levels
276 -- that do not belong to the current base release;
277 -- this procedure is only called within this package.
278 -- (created to fix bugs 5376688 and 5484176)
279 --
280
281 procedure get_product_patch_level_priv
282 (p_appl_shortname in varchar2,
283 p_patch_level out nocopy varchar2)
284 is
285 l_appl_id number;
286 l_patch_level varchar2(30);
287 begin
288
289 l_appl_id := get_appl_id(p_appl_shortname);
290
291 get_product_patch_level_priv(p_appl_id=>l_appl_id,
292 p_patch_level=>p_patch_level);
293 end;
294
295
296 --
297 -- get current patch level from the database
298 --
299
300 procedure get_product_patch_level
301 (p_appl_id in number,
302 p_patch_level out nocopy varchar2)
303 is
304 l_patch_level varchar2(30);
305 begin
306 select patch_level
307 into l_patch_level
308 from fnd_product_installations
309 where application_id = p_appl_id
310 and install_group_num in (0, 1)
311 and rownum = 1;
312
313 p_patch_level := l_patch_level;
314
315 --
316 -- return NULL if earlier releases
317 --
318 if (l_patch_level like '10.7%'
319 or
320 l_patch_level like '11.0%')
321 then
322 p_patch_level := null;
323 end if;
324 end;
325
326 --
327 -- get current patch level from the database
328 --
329
330 procedure get_product_patch_level
331 (p_appl_shortname in varchar2,
332 p_patch_level out nocopy varchar2)
333 is
334 l_appl_id number;
335 l_patch_level varchar2(30);
336 begin
337
338 l_appl_id := get_appl_id(p_appl_shortname);
339
340 get_product_patch_level(p_appl_id=>l_appl_id,
341 p_patch_level=>p_patch_level);
342 end;
343
344 --
345 -- construct patch level for a product
346 --
347
348 procedure build_patch_level
349 (p_appl_shortname in varchar2,
350 p_patchset_name in varchar2,
351 p_patchset_revision in varchar2,
352 p_patch_level out nocopy varchar2)
353 is
354 begin
355 p_patch_level := get_base_release||VERCOMP_SEPARATOR||
356 get_appl_abbr(upper(p_appl_shortname))||VERCOMP_SEPARATOR||
357 upper(p_patchset_name);
358
359 if (p_patchset_revision is not null) then
360 p_patch_level := p_patch_level||VERCOMP_SEPARATOR||p_patchset_revision;
361 end if;
362 end;
363
364 --
365 -- update product row with patch level information, if the new
366 -- patch level is higher
367 --
368
369 -- Bug 3611969 : FIXED FILE.SQL.35 GSCC WARNINGS
370 -- sraghuve (07/05/2004)
371
372 procedure set_product_patch_level
373 (p_appl_shortname in varchar2,
374 p_patchset_name in varchar2)
375 is
376 begin
377 set_product_patch_level
378 (p_appl_shortname => p_appl_shortname,
379 p_patchset_name => p_patchset_name ,
380 p_force_flag => 'N');
381 end;
382
383 procedure set_product_patch_level
384 (p_appl_shortname in varchar2,
385 p_patchset_name in varchar2,
386 p_force_flag in varchar2)
387 is
388 l_new_patch_level varchar2(30);
389 l_old_patch_level varchar2(30);
390 l_error_msg varchar2(200);
391 l_patchset_revision varchar2(30);
392 l_patchset_name varchar2(30);
393 l_pos_first number;
394 begin
395
396 --
397 -- check to see if revision is specified along with the patchset name.
398 -- if so, split it into individual components
399 --
400 l_pos_first := instr(p_patchset_name, VERCOMP_SEPARATOR);
401 if (l_pos_first > 0) then
402 l_patchset_name := substr(p_patchset_name,
403 1, (l_pos_first - 1));
404 l_patchset_revision := substr(p_patchset_name,
405 (l_pos_first+1));
406 else
407 l_patchset_name := p_patchset_name;
408 l_patchset_revision := null;
409 end if;
410
411 build_patch_level(p_appl_shortname, l_patchset_name,
412 l_patchset_revision, l_new_patch_level);
413
414 if (validate_patch_level(l_new_patch_level,
415 p_appl_shortname, l_error_msg) = FALSE)
416 then
417 raise_application_error(-20001,
418 'Unable to build and validate patch version '||
419 'information. ('||l_error_msg||')');
420 end if;
421
422 get_product_patch_level_priv(p_appl_shortname, l_old_patch_level);
423
424 if (l_old_patch_level is not null
425 and
426 validate_patch_level(l_old_patch_level,
427 p_appl_shortname, l_error_msg) = FALSE)
428 then
429 raise_application_error(-20001,
430 'Incorrect patch version "'||l_old_patch_level||
431 '" in database. ('||l_error_msg||')');
432 end if;
433
434 if (upper(p_force_flag) = 'Y'
435 or
436 (compare_patch_levels(l_old_patch_level,
437 l_new_patch_level) = PATCH_LEVEL2_HIGHER))
438 then
439 update fnd_product_installations
440 set patch_level = l_new_patch_level
441 where application_id in (
442 select application_id
443 from fnd_application
444 where application_short_name = upper(p_appl_shortname));
445 end if;
446 end;
447
448 --
449 -- get individual components of a patch level namely
450 -- o base release
451 -- o application abbreviation
452 -- o patchset name
453 --
454
455 procedure get_patch_level_details
456 (p_patch_level in varchar2,
460 p_patchset_revision out nocopy varchar2)
457 p_base_release out nocopy varchar2,
458 p_appl_shortname out nocopy varchar2,
459 p_patchset_name out nocopy varchar2,
461 is
462 l_pos_first number;
463 l_pos_second number;
464 l_pos_third number;
465 begin
466
467 p_base_release := null;
468 p_appl_shortname := null;
469 p_patchset_name := null;
470 p_patchset_revision := null;
471
472 if (p_patch_level is null) then
473 return;
474 end if;
475
476 l_pos_first := instr(p_patch_level, VERCOMP_SEPARATOR, 1, 1);
477 l_pos_second := instr(p_patch_level, VERCOMP_SEPARATOR, 1, 2);
478 l_pos_third := instr(p_patch_level, VERCOMP_SEPARATOR, 1, 3);
479
480 if (l_pos_first = 0) then
481 --
482 -- first seperator not found, use entire string as the
483 -- first component
484 --
485 p_base_release := p_patch_level;
486 return;
487 else
488 p_base_release := substr(p_patch_level, 1, l_pos_first - 1);
489 end if;
490
491 if (l_pos_second = 0) then
492 --
493 -- second seperator not found, use remaining string as the
494 -- second component
495 --
496 p_appl_shortname := substr(p_patch_level, l_pos_first +1);
497 return;
498 else
499 p_appl_shortname := substr(p_patch_level,
500 l_pos_first +1,
501 ((l_pos_second) - (l_pos_first+1)));
502 end if;
503
504 if (l_pos_third = 0) then
505 --
506 -- third seperator not found, use remaining string as the
507 -- third component (patchset name)
508 --
509 p_patchset_name := substr(p_patch_level,
510 l_pos_second+1);
511 else
512 p_patchset_name := substr(p_patch_level,
513 l_pos_second +1,
514 ((l_pos_third) - (l_pos_second+1)));
515 p_patchset_revision := substr(p_patch_level,
516 l_pos_third+1);
517
518 end if;
519
520 end;
521
522 procedure get_patch_level_details
523 (p_patch_level in varchar2,
524 p_base_release out nocopy varchar2,
525 p_appl_shortname out nocopy varchar2,
526 p_patchset_name out nocopy varchar2)
527 is
528 l_patchset_revision varchar2(30);
529 begin
530 get_patch_level_details(p_patch_level,
531 p_base_release,
532 p_appl_shortname,
533 p_patchset_name,
534 l_patchset_revision);
535 end;
536 end;
[Home] [Help]