1 package body FPA_Utilities_PVT as
2 /* $Header: FPAVUTLB.pls 120.1 2005/08/18 11:03:53 appldev ship $ */
3
4 g_aw_space_name VARCHAR2(30) := fpa_global_pvt.aw_space_name;
5
6
7 /**********************************************************************************
8 **********************************************************************************/
9 -- The attach_AW procedure attaches PJP's AW space in either one of the following
10 -- 3 modes:
11 -- ro - for read only
12 -- rw - for write only.
13 -- This procedure's signature has the standard Oracle APPS parameters. It also
14 -- expects a parameter for the attach mode.
15
16 procedure attach_AW(
17 p_api_version IN number
18 ,p_attach_mode IN varchar2
19 ,x_return_status OUT NOCOPY varchar2
20 ,x_msg_count OUT NOCOPY number
21 ,x_msg_data OUT NOCOPY varchar2
22 ) is
23
24 begin
25
26 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
27 fnd_log.string (FND_LOG.LEVEL_PROCEDURE,
28 'fpa.sql.fpa_utilities_pvt.attach_aw.begin',
29 'Entering fpa_utilities_pvt.attach_aw');
30 END IF;
31
32 -- Attach the AW space read write.
33 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
34 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
35 'fpa.sql.fpa_utilities_pvt.attach_aw',
36 'Attaching OLAP workspace: ' || g_aw_space_name);
37 END IF;
38
39 -- dbms_aw.execute('AW ATTACH ' || g_aw_space_name || ' RW FIRST WAIT');
40 dbms_aw.execute('AW ATTACH ' || g_aw_space_name || ' ' || p_attach_mode || ' FIRST WAIT');
41
42 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
43 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
44 'fpa.sql.fpa_utilities_pvt.attach_aw.end',
45 'Exiting fpa_utilities_pvt.attach_aw');
46 END IF;
47
48 EXCEPTION
49 WHEN FND_API.G_EXC_ERROR THEN
50 x_return_status := FND_API.G_RET_STS_ERROR;
51 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
52 ,p_data => x_msg_data);
53 raise;
54 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
55 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
56 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
57 ,p_data => x_msg_data);
58 raise;
59 WHEN NO_DATA_FOUND THEN
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
62 ,p_data => x_msg_data);
63 raise;
64 WHEN OTHERS THEN
65 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
66 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
67 ,p_data => x_msg_data);
68 raise;
69
70 end attach_AW;
71
72 /**********************************************************************************
73 **********************************************************************************/
74
75 -- The detach_AW procedure detaches PJP's AW space.
76 -- This procedure's signature has the standard Oracle APPS parameters.
77
78 procedure detach_AW(
79 p_api_version IN number
80 ,x_return_status OUT NOCOPY varchar2
81 ,x_msg_count OUT NOCOPY number
82 ,x_msg_data OUT NOCOPY varchar2
83 ) is
84
85 begin
86
87 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
88 fnd_log.string (FND_LOG.LEVEL_PROCEDURE,
89 'fpa.sql.fpa_utilities_pvt.detach_aw.begin',
90 'Entering fpa_utilities_pvt.detach_aw');
91 END IF;
92
93 -- Detach the AW space.
94 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
95 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
96 'fpa.sql.fpa_utilities_pvt.detach_aw',
97 'Detaching OLAP workspace: ' || g_aw_space_name);
98 END IF;
99
100 dbms_aw.execute('AW DETACH ' || g_aw_space_name);
101
102 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
103 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
104 'fpa.sql.fpa_utilities_pvt.detach_aw.end',
105 'Exiting fpa_utilities_pvt.detach_aw');
106 END IF;
107
108
109 EXCEPTION
110 WHEN FND_API.G_EXC_ERROR THEN
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
113 ,p_data => x_msg_data);
114 raise;
115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
118 ,p_data => x_msg_data);
119 raise;
120 WHEN NO_DATA_FOUND THEN
121 x_return_status := FND_API.G_RET_STS_ERROR;
122 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
123 ,p_data => x_msg_data);
124 raise;
125 WHEN OTHERS THEN
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
127 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
128 ,p_data => x_msg_data);
129 raise;
130
131 end detach_AW;
132
133 /**********************************************************************************
134 **********************************************************************************/
135
136 -- The following function returns the AW space for PJP.
137 -- It is used in the DDL for PJPs views.
138 function aw_space_name
139 return varchar2 is
140
141 l_pjp_schema varchar2(15);
142 l_pjp_aw_name varchar2(30) := 'FPAPJP';
143
144 begin
145
146 SELECT application_short_name
147 into l_pjp_schema
148 from FND_APPLICATION
149 WHERE application_id = 440;
150
151 return l_pjp_schema || '.' || l_pjp_aw_name;
152
153 end;
154
155 /**********************************************************************************
156 **********************************************************************************/
157
158 -- function Duplicate_Name determines if the name already exists.
159 -- This function expects: p_table_name ----- Name of table being checked
160 -- p_column_name ---- Name of the column for the shortname
161 -- p_shortname ------ Shortname being checked.
162 -- If the shortname already exists then the function will return a non-zero number,
163 -- if it does not exist it will return a zero.
164
165 function Duplicate_Name(
166 p_table_name IN varchar2
167 ,p_column_name IN varchar2
168 ,p_name IN varchar2
169 ) return number is
170
171 TYPE sn_csr_type IS REF CURSOR; -- cursor to get shortname.
172 sn_csr sn_csr_type;
173
174 l_count number;
175
176 l_sql varchar2(255);
177
178 begin
179
180 l_sql := 'select count(' || p_column_name || ')' ||
181 ' from ' || p_table_name ||
182 ' where upper(' || p_column_name || ') = upper(''' || p_name || ''')';
183
184
185 open sn_csr for l_sql;
186 fetch sn_csr into l_count;
187 close sn_csr;
188
189 return l_count;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 return null;
194
195 end Duplicate_Name;
196
197 /************************************************************************************
198 ************************************************************************************/
199
200 -- This function is used to get the cash needed.
201 function Get_Net_Cash_Needed(
202 p_budget IN number
203 ,p_cash_req IN number
204 ) return number is
205
206 l_net_cash_needed number;
207
208 begin
209
210 if p_cash_req > p_budget then
211 l_net_cash_needed := p_cash_req - p_budget;
212 else
213 l_net_cash_needed := 0;
214 end if;
215
216 return l_net_cash_needed;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 return null;
221
222 end Get_Net_Cash_Needed;
223
224 /************************************************************************************
225 ************************************************************************************/
226
227 function Get_Overtime_Resources(
228 p_req_resources IN number
229 ,p_curr_resources IN number
230 ) return number is
231
232 l_overtime_resources number := 0;
233
234 begin
235
236 if (p_req_resources > p_curr_resources) then
237 l_overtime_resources := p_req_resources - p_curr_resources;
238 end if;
239
240 return l_overtime_resources;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 return null;
245
246 end Get_Overtime_Resources;
247
248 /************************************************************************************
249 ************************************************************************************/
250
251 function Get_Unused_Resources(
252 p_req_resources IN number
253 ,p_curr_resources IN number
254 ) return number is
255
256 l_unused_resources number := 0;
257
258 begin
259
260 if (p_curr_resources > p_req_resources) then
261 l_unused_resources := p_curr_resources - p_req_resources;
262 end if;
263
264 return l_unused_resources;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 return null;
269
270 end Get_Unused_Resources;
271
272 /************************************************************************************
273 ************************************************************************************/
274
275 /****** Section for common API messages, exception handling and logging. *********
276 ******** created: ashariff Dt: 10/29/2004 ****************************************/
277
278
279 -- MESSAGE CONSTANTS
280
281 --G_MSG_LEVEL_THRESHOLD CONSTANT NUMBER := FPA_UTILITIES_PVT.G_MISS_NUM;
282 --------------------------------------------------------------------------------
283 -- PROCEDURE init_msg_list
284 --------------------------------------------------------------------------------
285 PROCEDURE init_msg_list (
286 p_init_msg_list IN VARCHAR2
287 ) IS
288 BEGIN
289 IF (FND_API.to_boolean(p_init_msg_list)) THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292 EXCEPTION
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294 RAISE FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
295 END init_msg_list;
296 --------------------------------------------------------------------------------
297 -- FUNCTION start_activity
298 --------------------------------------------------------------------------------
299 FUNCTION start_activity(
300 p_api_name IN VARCHAR2,
301 p_pkg_name IN VARCHAR2,
302 p_init_msg_list IN VARCHAR2,
303 l_api_version IN NUMBER,
304 p_api_version IN NUMBER,
305 p_api_type IN VARCHAR2,
306 p_msg_log IN VARCHAR2,
307 x_return_status OUT NOCOPY VARCHAR2
308 ) RETURN VARCHAR2 IS
309 BEGIN
310 IF (p_msg_log IS NOT NULL
311 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312 FND_LOG.String(
313 FND_LOG.LEVEL_PROCEDURE,
314 'fpa.sql.'||p_pkg_name||p_api_name,
315 p_msg_log);
316 END IF;
317 FPA_UTILITIES_PVT.init_msg_list(p_init_msg_list);
318 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
319 RETURN(FPA_UTILITIES_PVT.G_RET_STS_SUCCESS);
320 END start_activity;
321
322 PROCEDURE start_activity(
323 p_api_name IN VARCHAR2,
324 p_pkg_name IN VARCHAR2,
325 p_init_msg_list IN VARCHAR2,
326 p_msg_log IN VARCHAR2
327 ) IS
328 BEGIN
329 IF (p_msg_log IS NOT NULL
330 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
331 FND_LOG.String(
332 FND_LOG.LEVEL_PROCEDURE,
333 'fpa.sql.'||p_pkg_name||p_api_name,
334 p_msg_log);
335 END IF;
336 FPA_UTILITIES_PVT.init_msg_list(p_init_msg_list);
337 END start_activity;
338 --------------------------------------------------------------------------------
339 -- FUNCTION handle_exceptions
340 --------------------------------------------------------------------------------
341 FUNCTION handle_exceptions (
342 p_api_name IN VARCHAR2,
343 p_pkg_name IN VARCHAR2,
344 p_exc_name IN VARCHAR2,
345 p_msg_log IN VARCHAR2,
346 x_msg_count OUT NOCOPY NUMBER,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 p_api_type IN VARCHAR2
349 ) RETURN VARCHAR2 IS
350 l_return_value VARCHAR2(200) := FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR;
351 BEGIN
352 IF p_exc_name = 'FPA_UTILITIES_PVT.G_RET_STS_ERROR' THEN
353 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
354 FND_LOG.String(
355 FND_LOG.LEVEL_PROCEDURE,
356 'fpa.sql.'||p_pkg_name||p_api_name,
357 p_exc_name||p_msg_log);
358 END IF;
359 l_return_value := FPA_UTILITIES_PVT.G_RET_STS_ERROR;
360 ELSIF p_exc_name = 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR' THEN
361 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
362 FND_LOG.String(
363 FND_LOG.LEVEL_PROCEDURE,
364 'fpa.sql.'||p_pkg_name||p_api_name,
365 p_exc_name||p_msg_log);
366 END IF;
367 ELSE -- WHEN OTHERS EXCEPTION
368 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
369 FND_MSG_PUB.Add_Exc_Msg(
370 p_pkg_name,
371 p_api_name);
372 END IF;
373 END IF;
374 FND_MSG_PUB.Count_And_Get(
375 p_count => x_msg_count,
376 p_data => x_msg_data);
377 RETURN(l_return_value);
378 END handle_exceptions;
379 --------------------------------------------------------------------------------
380 -- FUNCTION end_activity
381 --------------------------------------------------------------------------------
382 PROCEDURE end_activity (
383 p_api_name IN VARCHAR2,
384 p_pkg_name IN VARCHAR2,
385 p_msg_log IN VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2) IS
388 BEGIN
389 IF (p_msg_log IS NOT NULL
390 AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
391 FND_LOG.String(
392 FND_LOG.LEVEL_PROCEDURE,
393 p_pkg_name||p_api_name,
394 p_msg_log);
395 END IF;
396 --- Standard call to get message count and if count is 1, get message info
397 FND_MSG_PUB.Count_And_Get(
398 p_count => x_msg_count,
399 p_data => x_msg_data);
400 END end_activity;
401 --------------------------------------------------------------------------------
402 -- PROCEDURE set_message
403 --------------------------------------------------------------------------------
404 PROCEDURE set_message (
405 p_app_name IN VARCHAR2,
406 p_msg_name IN VARCHAR2,
407 p_token1 IN VARCHAR2,
408 p_token1_value IN VARCHAR2,
409 p_token2 IN VARCHAR2,
410 p_token2_value IN VARCHAR2,
411 p_token3 IN VARCHAR2,
412 p_token3_value IN VARCHAR2,
413 p_token4 IN VARCHAR2,
414 p_token4_value IN VARCHAR2,
415 p_token5 IN VARCHAR2,
416 p_token5_value IN VARCHAR2,
417 p_token6 IN VARCHAR2,
418 p_token6_value IN VARCHAR2,
419 p_token7 IN VARCHAR2,
420 p_token7_value IN VARCHAR2,
421 p_token8 IN VARCHAR2,
422 p_token8_value IN VARCHAR2,
423 p_token9 IN VARCHAR2,
424 p_token9_value IN VARCHAR2,
425 p_token10 IN VARCHAR2,
426 p_token10_value IN VARCHAR2
427 ) IS
428 BEGIN
429 FND_MESSAGE.SET_NAME( FPA_UTILITIES_PVT.G_APP_NAME, P_MSG_NAME);
430 IF (p_token1 IS NOT NULL) AND (p_token1_value IS NOT NULL) THEN
431 FND_MESSAGE.SET_TOKEN( TOKEN => p_token1,
432 VALUE => p_token1_value);
433 END IF;
434 IF (p_token2 IS NOT NULL) AND (p_token2_value IS NOT NULL) THEN
435 FND_MESSAGE.SET_TOKEN( TOKEN => p_token2,
436 VALUE => p_token2_value);
437 END IF;
438 IF (p_token3 IS NOT NULL) AND (p_token3_value IS NOT NULL) THEN
439 FND_MESSAGE.SET_TOKEN( TOKEN => p_token3,
440 VALUE => p_token3_value);
441 END IF;
442 IF (p_token4 IS NOT NULL) AND (p_token4_value IS NOT NULL) THEN
443 FND_MESSAGE.SET_TOKEN( TOKEN => p_token4,
444 VALUE => p_token4_value);
445 END IF;
446 IF (p_token5 IS NOT NULL) AND (p_token5_value IS NOT NULL) THEN
447 FND_MESSAGE.SET_TOKEN( TOKEN => p_token5,
448 VALUE => p_token5_value);
449 END IF;
450 IF (p_token6 IS NOT NULL) AND (p_token6_value IS NOT NULL) THEN
451 FND_MESSAGE.SET_TOKEN( TOKEN => p_token6,
452 VALUE => p_token6_value);
453 END IF;
454 IF (p_token7 IS NOT NULL) AND (p_token7_value IS NOT NULL) THEN
455 FND_MESSAGE.SET_TOKEN( TOKEN => p_token7,
456 VALUE => p_token7_value);
457 END IF;
458 IF (p_token8 IS NOT NULL) AND (p_token8_value IS NOT NULL) THEN
459 FND_MESSAGE.SET_TOKEN( TOKEN => p_token8,
460 VALUE => p_token8_value);
461 END IF;
462 IF (p_token9 IS NOT NULL) AND (p_token9_value IS NOT NULL) THEN
463 FND_MESSAGE.SET_TOKEN( TOKEN => p_token9,
464 VALUE => p_token9_value);
465 END IF;
466 IF (p_token10 IS NOT NULL) AND (p_token10_value IS NOT NULL) THEN
467 FND_MESSAGE.SET_TOKEN( TOKEN => p_token10,
468 VALUE => p_token10_value);
469 END IF;
470 FND_MSG_PUB.add;
471 END set_message;
472
473 /****END: Section for common API messages, exception handling and logging.******
474 ********************************************************************************/
475
476 end FPA_Utilities_PVT;
[Home] [Help]