1 PACKAGE BODY CN_UPGRADE_UTL_PKG as
2 /* $Header: cnuputlb.pls 120.2 2006/02/17 04:27:16 rramakri noship $ */
3
4 FUNCTION get_start_date(p_period_id NUMBER,
5 p_org_id NUMBER) RETURN DATE IS
6
7 l_start_date DATE;
8
9 CURSOR l_start_date_csr IS
10
11 SELECT start_date
12 FROM cn_period_statuses_all
13 WHERE period_id = p_period_id
14 AND ((org_id = p_org_id) OR (org_id IS NULL AND p_org_id IS NULL));
15
16 BEGIN
17
18 IF p_period_id IS NULL THEN
19
20 l_start_date := NULL;
21
22 ELSE
23
24 OPEN l_start_date_csr;
25 FETCH l_start_date_csr INTO l_start_date;
26
27 CLOSE l_start_date_csr;
28
29 END IF;
30
31 RETURN l_start_date;
32
33 END get_start_date;
34
35 FUNCTION get_end_date(p_period_id NUMBER,
36 p_org_id NUMBER) RETURN DATE IS
37
38 l_end_date DATE;
39
40 CURSOR l_end_date_csr IS
41
42 SELECT end_date
43 FROM cn_period_statuses_all
44 WHERE period_id = p_period_id
45 AND ((org_id = p_org_id) OR (org_id IS NULL AND p_org_id IS NULL));
46
47 BEGIN
48
49 IF p_period_id IS NULL THEN
50
51 l_end_date := NULL;
52
53 ELSE
54
55 OPEN l_end_date_csr;
56 FETCH l_end_date_csr INTO l_end_date;
57
58 CLOSE l_end_date_csr;
59
60 END IF;
61
62 RETURN l_end_date;
63
64 END get_end_date;
65
66
67 --| ---------------------------------------------------------------------+
68 --| Function Name : is_release_11510
69 --| Desc : Check if current release is 11.5.10
70 --| Return 1 if current release is 11.5.10
71 --| Return 0 if current release is 10.7, 3i or 11.0, 11.5
72 --| Return -1 : not valid release
73 --| ---------------------------------------------------------------------+
74
75 FUNCTION is_release_11510 RETURN NUMBER IS
76 l_result NUMBER := -1;
77 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
78 CURSOR c1 IS
79 SELECT profile_option_value
80 from fnd_profile_option_values
81 where profile_option_id =
82 (select profile_option_id
83 from fnd_profile_options
84 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
85 and application_id = 283)
86 and level_id = 10001
87 and application_id = 283;
88
89 BEGIN
90
91 OPEN c1;
92 FETCH c1 INTO v_cn_upgrading_profile;
93
94 IF c1%NOTFOUND THEN
95 l_result := -1;
96 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
97 RETURN l_result;
98 END IF;
99
100 IF c1%ISOPEN THEN
101 CLOSE c1;
102 END IF;
103
104 IF v_cn_upgrading_profile = '107' OR
105 v_cn_upgrading_profile = '110' OR
106 v_cn_upgrading_profile = '3I' OR
107 v_cn_upgrading_profile = '115' THEN
108 l_result := 0;
109 ELSIF v_cn_upgrading_profile = '11510' THEN
110 l_result := 1;
111 ELSE
112 l_result := -1;
113 END IF;
114
115 RETURN l_result;
116
117 END is_release_11510;
118
119 --| ---------------------------------------------------------------------+
120 --| Function Name : is_release_115
121 --| Desc : Check if current release is 11.5
122 --| Return 1 if current release is 11.5
123 --| Return 0 if current release is 10.7, 3i or 11.0
124 --| Return -1 : not valid release
125 --| ---------------------------------------------------------------------+
126
127 FUNCTION is_release_115 RETURN NUMBER IS
128 l_result NUMBER := -1;
129 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
130 CURSOR c1 IS
131 SELECT profile_option_value
132 from fnd_profile_option_values
133 where profile_option_id =
134 (select profile_option_id
135 from fnd_profile_options
136 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
137 and application_id = 283)
138 and level_id = 10001
139 and application_id = 283;
140
141 BEGIN
142
143 OPEN c1;
144 FETCH c1 INTO v_cn_upgrading_profile;
145
146 IF c1%NOTFOUND THEN
147 l_result := -1;
148 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
149 RETURN l_result;
150 END IF;
151
152 IF c1%ISOPEN THEN
153 CLOSE c1;
154 END IF;
155
156 IF v_cn_upgrading_profile = '107' OR
157 v_cn_upgrading_profile = '110' OR
158 v_cn_upgrading_profile = '3I' THEN
159 l_result := 0;
160 ELSIF v_cn_upgrading_profile = '115' OR
161 v_cn_upgrading_profile = '11510' THEN
162 l_result := 1;
163 ELSE
164 l_result := -1;
165 END IF;
166
167 RETURN l_result;
168
169 END is_release_115;
170
171 --| ---------------------------------------------------------------------+
172 --| Function Name : is_release_107
173 --| Desc : Check if current release is 10.7
174 --| Return 1 if current release is 10.7
175 --| Return 0 if current release is 11.5,3i or 11.0
176 --| Return -1 : not valid release
177 --| ---------------------------------------------------------------------+
178
179 FUNCTION is_release_107 RETURN NUMBER IS
180 l_result NUMBER := -1;
181 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
182 CURSOR c1 IS
183 SELECT profile_option_value
184 from fnd_profile_option_values
185 where profile_option_id =
186 (select profile_option_id
187 from fnd_profile_options
188 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
189 and application_id = 283)
190 and level_id = 10001
191 and application_id = 283;
192
193 BEGIN
194
195 OPEN c1;
196 FETCH c1 INTO v_cn_upgrading_profile;
197
198 IF c1%NOTFOUND THEN
199 l_result := -1;
200 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
201 RETURN l_result;
202 END IF;
203
204 IF c1%ISOPEN THEN
205 CLOSE c1;
206 END IF;
207
208 IF v_cn_upgrading_profile = '110' OR
209 v_cn_upgrading_profile = '3I' OR
210 v_cn_upgrading_profile = '115' OR
211 v_cn_upgrading_profile = '11510' THEN
212 l_result := 0;
213 ELSIF v_cn_upgrading_profile = '107' THEN
214 l_result := 1;
215 ELSE
216 l_result := -1;
217 END IF;
218
219 RETURN l_result;
220
221 END is_release_107;
222
223 --| ---------------------------------------------------------------------+
224 --| Function Name : is_release_110
225 --| Desc : Check if current release is 11.0
226 --| Return 1 if current release is 11.0
227 --| Return 0 if current release is 10.7,3i or 11.0 or 11.5
228 --| Return -1 : not valid release
229 --| ---------------------------------------------------------------------+
230
231 FUNCTION is_release_110 RETURN NUMBER IS
232 l_result NUMBER := -1;
233 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
234 CURSOR c1 IS
235 SELECT profile_option_value
236 from fnd_profile_option_values
237 where profile_option_id =
238 (select profile_option_id
239 from fnd_profile_options
240 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
241 and application_id = 283)
242 and level_id = 10001
243 and application_id = 283;
244
245 BEGIN
246
247 OPEN c1;
248 FETCH c1 INTO v_cn_upgrading_profile;
249
250 IF c1%NOTFOUND THEN
251 l_result := -1;
252 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
253 RETURN l_result;
254 END IF;
255
256 IF c1%ISOPEN THEN
257 CLOSE c1;
258 END IF;
259
260 IF v_cn_upgrading_profile = '107' OR
261 v_cn_upgrading_profile = '3I' OR
262 v_cn_upgrading_profile = '115' OR
263 v_cn_upgrading_profile = '11510' THEN
264 l_result := 0;
265 ELSIF v_cn_upgrading_profile = '110' THEN
266 l_result := 1;
267 ELSE
268 l_result := -1;
269 END IF;
270
271 RETURN l_result;
272
273 END is_release_110;
274
275 --| ---------------------------------------------------------------------+
276 --| Function Name : is_release_3i
277 --| Desc : Check if current release is 3i
278 --| Return 1 if current release is 3i
279 --| Return 0 if current release is 10.7,11.0 or 11.5
280 --| Return -1 : not valid release
281 --| ---------------------------------------------------------------------+
282
283 FUNCTION is_release_3i RETURN NUMBER IS
284 l_result NUMBER := -1;
285 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
286 CURSOR c1 IS
287 SELECT profile_option_value
288 from fnd_profile_option_values
289 where profile_option_id =
290 (select profile_option_id
291 from fnd_profile_options
292 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
293 and application_id = 283)
294 and level_id = 10001
295 and application_id = 283;
296
297 BEGIN
298
299 OPEN c1;
300 FETCH c1 INTO v_cn_upgrading_profile;
301
302 IF c1%NOTFOUND THEN
303 l_result := -1;
304 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
305 RETURN l_result;
306 END IF;
307
308 IF c1%ISOPEN THEN
309 CLOSE c1;
310 END IF;
311
312 IF v_cn_upgrading_profile = '107' OR
313 v_cn_upgrading_profile = '110' OR
314 v_cn_upgrading_profile = '115' OR
315 v_cn_upgrading_profile = '11510' THEN
316 l_result := 0;
317 ELSIF v_cn_upgrading_profile = '3I' THEN
318 l_result := 1;
319 ELSE
320 l_result := -1;
321 END IF;
322
323 RETURN l_result;
324
325 END is_release_3i;
326
327
328 FUNCTION is_release_120 RETURN NUMBER IS
329 l_result NUMBER := -1;
330 v_cn_upgrading_profile fnd_profile_option_values.profile_option_value%TYPE;
331 CURSOR c1 IS
332 SELECT profile_option_value
333 from fnd_profile_option_values
334 where profile_option_id =
335 (select profile_option_id
336 from fnd_profile_options
337 where upper(profile_option_name) = 'CN_UPGRADING_FROM_RELEASE'
338 and application_id = 283)
339 and level_id = 10001
340 and application_id = 283;
341
342 BEGIN
343
344 OPEN c1;
345 FETCH c1 INTO v_cn_upgrading_profile;
346
347 IF c1%NOTFOUND THEN
348 l_result := -1;
349 raise_application_error(-20000, 'There is no value setup for the profile(CN_UPGRADING_FROM_RELEASE). Cannot continue..');
350 RETURN l_result;
351 END IF;
352
353 IF c1%ISOPEN THEN
354 CLOSE c1;
355 END IF;
356
357 IF v_cn_upgrading_profile = '120'
358 THEN
359 l_result := 1;
360 ELSIF v_cn_upgrading_profile = '11510' THEN
361 l_result := 1;
362 ELSE
363 l_result := -1;
364 END IF;
365
366 RETURN l_result;
367 END is_release_120;
368
369 END cn_upgrade_utl_pkg;