1 package body fnd_release as
2 /* $Header: AFINRELB.pls 120.3.12000000.1 2007/01/18 13:20:27 appldev ship $ */
3
4
5 -- cached result
6 --
7 z_result boolean := false;
8
9 -- cached release_name
10 --
11 z_release_name fnd_product_groups.release_name%type;
12
13 -- cached release_info
14 --
15 z_release_info fnd_product_groups.release_name%type;
16
17 -- cached major version number
18 -- e.g. release '11.5.10', the point release is 11
19 --
20 z_major_version integer;
21
22 -- cached minor version number
23 -- e.g. release '11.5.10', the minor release is 5
24 --
25 z_minor_version integer;
26
27 -- cached point version number
28 -- e.g. release '11.5.10', the point release is 10
29 --
30 z_point_version integer;
31
32
33 UNKNOWN constant varchar2(7) := 'Unknown';
34
35 --
36 -- Public Functions
37 --
38
39 function get_release (release_name out nocopy varchar2,
40 other_release_info out nocopy varchar2)
41 return boolean is
42
43 cursor get_rel_cursor is
44 select release_name
45 from fnd_product_groups
46 order by product_group_id;
47
48 first_space number;
49
50 l_name fnd_product_groups.release_name%type := null;
51 l_info fnd_product_groups.release_name%type := null;
52
53 begin
54
55 -- return cached values if available
56 if z_result then
57 release_name := z_release_name;
58 other_release_info := z_release_info;
59 return z_result;
60 end if;
61
62 --
63 -- get_release() will usually return TRUE
64 -- with RELEASE_NAME =
65 -- contents of RELEASE_NAME column in FND_PRODUCT_GROUPS
66 -- and OTHER_RELEASE_INFO = null
67 --
68 -- If FND_PRODUCT_GROUPS.RELEASE_NAME contains imbedded spaces:
69 --
70 -- get_release() will return TRUE
71 -- with RELEASE_NAME = FND_PRODUCT_GROUPS.RELEASE_NAME up to but
72 -- not including the first imbedded space
73 -- and OTHER_RELEASE_INFO = FND_PRODUCT_GROUPS.RELEASE_NAME
74 -- starting with the first non-space character after the first
75 -- imbedded space
76 --
77 -- On failure, get_release() returns FALSE. This will be a performance issue.
78 -- Both RELEASE_NAME and OTHER_RELEASE_INFO will be set to 'Unknown'.
79 -- This indicates that either:
80 -- 1) there are no rows in fnd_product_groups
81 -- - this can be resolved by populating the row and it will
82 -- be queried on the next call.
83 -- 2) there is more than one row in fnd_product_groups
84 -- - delete all but the one correct row from fnd_product_groups and it
85 -- will be queried on the next call. It's possible that the values
86 -- returned by release_* and *_version routines are still correct if
87 -- the first row in fnd_product_groups, ordered by product_group_id,
88 -- if the currect row, but this will still be a performance problem.
89
90 release_name := UNKNOWN;
91 other_release_info := UNKNOWN;
92
93 open get_rel_cursor;
94 fetch get_rel_cursor into l_name;
95 z_result := get_rel_cursor%found;
96
97 if z_result then
98
99 -- If we got this far, we got at least one row from FPG
100 -- split returned value into release name and other info
101
102 -- remove leading and trailing blanks, if any
103
104 l_name := rtrim(ltrim(l_name, ' '),' ');
105
106 -- Find first space
107
108 first_space := instr(l_name,' ');
109
110 if first_space > 0 then
111 -- There is extra info
112 l_info := ltrim(substr(l_name, first_space + 1),' ');
113 l_name := substr(l_name, 1, first_space - 1);
114 end if;
115
116 -- success: return values computed above
117 z_release_name := l_name;
118 z_release_info := l_info;
119
120 -- Check for multiple rows in FPG
121
122 declare
123 l_temp fnd_product_groups.release_name%type := null;
124 begin
125 fetch get_rel_cursor into l_temp;
126 -- return false if fetched another row
127 z_result := get_rel_cursor%notfound;
128 end;
129
130 -- only populate the out parameters if one row exists
131 if z_result then
132 release_name := l_name;
133 other_release_info := l_info;
134 end if;
135
136 end if;
137 close get_rel_cursor;
138
139 -- dbms_output.put_line( 'exiting get_release(): should not reach here');
140 return(z_result);
141
142 exception
143 when others then
144 -- dbms_output.put_line( 'exiting get_release() with following error:');
145 -- dbms_output.put_line( sqlerrm );
146 return(false);
147
148 end get_release;
149
150 --
151 -- private initializer
152 --
153 procedure initialize is
154 l_temp boolean;
155 l_name fnd_product_groups.release_name%type := null;
156 l_info fnd_product_groups.release_name%type := null;
157 begin
158 l_temp := get_release(l_name,l_info);
159 end initialize;
160
161 --
162 -- returns cached result
163 --
164 function result
165 return boolean is
166 begin
167 if not z_result then
168 initialize;
169 end if;
170 return z_result;
171 end result;
172
173 --
174 -- returns the release_name returned by get_release
175 --
176 function release_name
177 return varchar2 is
178 begin
179 if not z_result then
180 initialize;
181 end if;
182 return z_release_name;
183 end release_name;
184
185 --
186 -- returns the release_info returned by get_release
187 --
188 function release_info
189 return varchar2 is
190 begin
191 if not z_result then
192 initialize;
193 end if;
194 return z_release_info;
195 end release_info;
196
197 --
198 -- private routine to parse the release_name into major, minor and point
199 -- always popuates major, minor and point releases.
200 -- e.g.
201 -- 11 results in in major=11, minor=0, point=0
202 -- 11.5 results in in major=11, minor=5, point=0
203 -- 11.5.10 results in in major=11, minor=5, point=10
204 -- parse error results in major=0, minor=0, point=sqlcode
205 --
206
207 procedure initialize_versions
208 is
209 first integer;
210 second integer;
211 third integer;
212 t_release fnd_product_groups.release_name%type := z_release_name;
213 begin
214 -- initialize if needed
215 if t_release is null then
216 -- needs to be initialized
217 t_release := release_name;
218 if t_release is null then
219 return;
220 end if;
221 end if;
222
223 -- pad just in case there aren't enough dots.
224 -- avoids checking return of instr and defaults
225 -- missing fields to zero.
226 t_release := t_release||'.0.0.0';
227
228 -- find major.minor
229 first := instr(t_release,'.',1,1);
230
231 -- find minor.point
232 second := instr(t_release,'.',1,2);
233
234 -- find point
235 third := instr(t_release,'.',1,3);
236
237 z_major_version := to_number(substr(t_release,1,first-1));
238 z_minor_version := to_number(substr(t_release,first+1,second-first-1));
239 z_point_version := to_number(substr(t_release,second+1,third-second-1));
240
241 exception
242 when others then
243 z_point_version := sqlcode;
244 z_major_version := 0;
245 z_minor_version := 0;
246 end initialize_versions;
247
248 --
249 -- returns the major version number of the release_name
250 --
251 function major_version
252 return integer is
253 begin
254 if z_major_version is null then
255 initialize_versions;
256 end if;
257 return z_major_version;
258 end major_version;
259
260 --
261 -- returns the minor version number of the release_name
262 --
263 function minor_version
264 return integer is
265 begin
266 if z_minor_version is null then
267 initialize_versions;
268 end if;
269 return z_minor_version;
270 end minor_version;
271
272 --
273 -- returns the point version number of the release_name
274 --
275 function point_version
276 return integer is
277 begin
278 if z_point_version is null then
279 initialize_versions;
280 end if;
281 return z_point_version;
282 end point_version;
283
284
285 end fnd_release;