[Home] [Help]
PACKAGE BODY: APPS.BIS_FUNCTIONAL_AREA_PUB
Source
1 PACKAGE BODY BIS_FUNCTIONAL_AREA_PUB AS
2 /* $Header: BISPFASB.pls 120.0 2005/06/01 16:38:23 appldev noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISPFASB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper for populating the table BIS_FUNCTIONAL_ARES_TL |
13 REM | |
14 REM | NOTES |
15 REM | 24-NOV-2004 Aditya Rao Created. |
16 REM +=======================================================================+
17 */
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_FUNCTIONAL_AREA_PUB';
20
21 /*
22 PRIVATE Validation APIs
23 */
24
25 PROCEDURE Validate_Functional_Area(
26 p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
27 ,p_Action_Type IN VARCHAR2
28 ,x_Return_Status OUT NOCOPY VARCHAR2
29 ,x_Msg_Count OUT NOCOPY NUMBER
30 ,x_Msg_Data OUT NOCOPY VARCHAR2
31 );
32
33
34 PROCEDURE Validate_Func_Area_Apps_Dep (
35 p_Functional_Area_Id IN NUMBER
36 ,p_Application_Id IN NUMBER
37 ,p_Action_Type IN VARCHAR2
38 ,x_Return_Status OUT NOCOPY VARCHAR2
39 ,x_Msg_Count OUT NOCOPY NUMBER
40 ,x_Msg_Data OUT NOCOPY VARCHAR2
41 );
42
43 FUNCTION Get_Next_Functional_Area_Id RETURN NUMBER;
44 FUNCTION Get_FA_Id_By_Short_Name (p_Functional_Area_Short_Name IN VARCHAR2)RETURN NUMBER;
45
46 /*
47 PUBLIC accessible CRUD APIs
48 */
49
50
51 PROCEDURE Create_Functional_Area(
52 p_Api_Version IN NUMBER
53 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
54 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
55 ,x_Return_Status OUT NOCOPY VARCHAR2
56 ,x_Msg_Count OUT NOCOPY NUMBER
57 ,x_Msg_Data OUT NOCOPY VARCHAR2
58 ) IS
59
60 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
61 l_Count NUMBER;
62
63 BEGIN
64 SAVEPOINT CreateFuncAreaSP;
65 FND_MSG_PUB.Initialize;
66
67 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
68 l_Func_Area_Rec := p_Func_Area_Rec;
69
70 l_Count := 0;
71 l_Func_Area_Rec.Functional_Area_Id := Get_Next_Functional_Area_Id;
72
73 Validate_Functional_Area(
74 p_Func_Area_Rec => l_Func_Area_Rec
75 ,p_Action_Type => C_CREATE
76 ,x_Return_Status => x_Return_Status
77 ,x_Msg_Count => x_Msg_Count
78 ,x_Msg_Data => x_Msg_Data
79 );
80 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83
84 -- Call the private PL/SQL API
85 BIS_FUNCTIONAL_AREA_PVT.Create_Functional_Area(
86 p_Api_Version => 1.0
87 ,p_Commit => p_Commit
88 ,p_Func_Area_Rec => l_Func_Area_Rec
89 ,x_Return_Status => x_Return_Status
90 ,x_Msg_Count => x_Msg_Count
91 ,x_Msg_Data => x_Msg_Data
92 );
93 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96
97 -- Commit if required
98 IF (p_Commit = FND_API.G_TRUE) THEN
99 COMMIT;
100 END IF;
101
102 EXCEPTION
103 WHEN FND_API.G_EXC_ERROR THEN
104 ROLLBACK TO CreateFuncAreaSP;
105 IF (x_msg_data IS NULL) THEN
106 FND_MSG_PUB.Count_And_Get
107 ( p_encoded => FND_API.G_FALSE
108 , p_count => x_msg_count
109 , p_data => x_msg_data
110 );
111 END IF;
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
114 ROLLBACK TO CreateFuncAreaSP;
115 IF (x_msg_data IS NULL) THEN
116 FND_MSG_PUB.Count_And_Get
117 ( p_encoded => FND_API.G_FALSE
118 , p_count => x_msg_count
119 , p_data => x_msg_data
120 );
121 END IF;
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 WHEN NO_DATA_FOUND THEN
124 ROLLBACK TO CreateFuncAreaSP;
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
126 IF (x_msg_data IS NOT NULL) THEN
127 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Create_Functional_Area ';
128 ELSE
129 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Create_Functional_Area ';
130 END IF;
131 WHEN OTHERS THEN
132 ROLLBACK TO CreateFuncAreaSP;
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 IF (x_msg_data IS NOT NULL) THEN
135 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Create_Functional_Area ';
136 ELSE
137 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Create_Functional_Area ';
138 END IF;
139 END Create_Functional_Area;
140
141
142
143 -- Update Functional Area API
144 PROCEDURE Update_Functional_Area(
145 p_Api_Version IN NUMBER
146 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
147 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
148 ,x_Return_Status OUT NOCOPY VARCHAR2
149 ,x_Msg_Count OUT NOCOPY NUMBER
150 ,x_Msg_Data OUT NOCOPY VARCHAR2
151 ) IS
152 l_Count NUMBER;
153 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
154 BEGIN
155 SAVEPOINT UpdateFuncAreaSP;
156 FND_MSG_PUB.Initialize;
157 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
158
159 Validate_Functional_Area(
160 p_Func_Area_Rec => p_Func_Area_Rec
161 ,p_Action_Type => C_UPDATE
162 ,x_Return_Status => x_Return_Status
163 ,x_Msg_Count => x_Msg_Count
164 ,x_Msg_Data => x_Msg_Data
165 );
166 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 -- *** PUT IT HERE ***
171 BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area(
172 p_Api_Version => 1.0
173 ,p_Commit => p_Commit
174 ,p_Func_Area_Rec => p_Func_Area_Rec
175 ,x_Return_Status => x_Return_Status
176 ,x_Msg_Count => x_Msg_Count
177 ,x_Msg_Data => x_Msg_Data
178 );
179 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182
183 -- Commit if required
184 IF (p_Commit = FND_API.G_TRUE) THEN
185 COMMIT;
186 END IF;
187
188
189 EXCEPTION
190 WHEN FND_API.G_EXC_ERROR THEN
191 ROLLBACK TO UpdateFuncAreaSP;
192 IF (x_msg_data IS NULL) THEN
193 FND_MSG_PUB.Count_And_Get
194 ( p_encoded => FND_API.G_FALSE
195 , p_count => x_msg_count
196 , p_data => x_msg_data
197 );
198 END IF;
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 ROLLBACK TO UpdateFuncAreaSP;
202 IF (x_msg_data IS NULL) THEN
203 FND_MSG_PUB.Count_And_Get
204 ( p_encoded => FND_API.G_FALSE
205 , p_count => x_msg_count
206 , p_data => x_msg_data
207 );
208 END IF;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 WHEN NO_DATA_FOUND THEN
211 ROLLBACK TO UpdateFuncAreaSP;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 IF (x_msg_data IS NOT NULL) THEN
214 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Update_Functional_Area ';
215 ELSE
216 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Update_Functional_Area ';
217 END IF;
218 WHEN OTHERS THEN
219 ROLLBACK TO UpdateFuncAreaSP;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 IF (x_msg_data IS NOT NULL) THEN
222 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Update_Functional_Area ';
223 ELSE
224 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Update_Functional_Area ';
225 END IF;
226
227 END Update_Functional_Area;
228
229 -- Retrieve Functional Area API
230
231 PROCEDURE Retrieve_Functional_Area(
232 p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
233 ,x_Func_Area_Rec OUT NOCOPY BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
234 ,x_Return_Status OUT NOCOPY VARCHAR2
235 ,x_Msg_Count OUT NOCOPY NUMBER
236 ,x_Msg_Data OUT NOCOPY VARCHAR2
237 ) IS
238
239 BEGIN
240 FND_MSG_PUB.Initialize;
241 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
242
243
244 BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area(
245 p_Func_Area_Rec => p_Func_Area_Rec
246 ,x_Func_Area_Rec => x_Func_Area_Rec
247 ,x_Return_Status => x_Return_Status
248 ,x_Msg_Count => x_Msg_Count
249 ,x_Msg_Data => x_Msg_Data
250 );
251 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR THEN
258 IF (x_msg_data IS NULL) THEN
259 FND_MSG_PUB.Count_And_Get
260 ( p_encoded => FND_API.G_FALSE
261 , p_count => x_msg_count
262 , p_data => x_msg_data
263 );
264 END IF;
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
267 IF (x_msg_data IS NULL) THEN
268 FND_MSG_PUB.Count_And_Get
269 ( p_encoded => FND_API.G_FALSE
270 , p_count => x_msg_count
271 , p_data => x_msg_data
272 );
273 END IF;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 WHEN NO_DATA_FOUND THEN
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 IF (x_msg_data IS NOT NULL) THEN
278 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Retrieve_Functional_Area ';
279 ELSE
280 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Retrieve_Functional_Area ';
281 END IF;
282 WHEN OTHERS THEN
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 IF (x_msg_data IS NOT NULL) THEN
285 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Retrieve_Functional_Area ';
286 ELSE
287 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Retrieve_Functional_Area ';
288 END IF;
289 END Retrieve_Functional_Area;
290
291
292 -- Translate the Functional Area Name/Description
293 PROCEDURE Translate_Functional_Area(
294 p_Api_Version IN NUMBER
295 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
296 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
297 ,x_Return_Status OUT NOCOPY VARCHAR2
298 ,x_Msg_Count OUT NOCOPY NUMBER
299 ,x_Msg_Data OUT NOCOPY VARCHAR2
300 ) IS
301 l_Count NUMBER;
302 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
303
304 BEGIN
305 SAVEPOINT TransFuncAreaSP;
306 FND_MSG_PUB.Initialize;
307 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
308
309 -- Modularize this into a single API (Update and Retrieve)
310
311 Validate_Functional_Area(
312 p_Func_Area_Rec => p_Func_Area_Rec
313 ,p_Action_Type => C_UPDATE
314 ,x_Return_Status => x_Return_Status
315 ,x_Msg_Count => x_Msg_Count
316 ,x_Msg_Data => x_Msg_Data
317 );
318 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 END IF;
321
322 BIS_FUNCTIONAL_AREA_PVT.Translate_Functional_Area(
326 ,x_Return_Status => x_Return_Status
323 p_Api_Version => 1.0
324 ,p_Commit => p_Commit
325 ,p_Func_Area_Rec => p_Func_Area_Rec
327 ,x_Msg_Count => x_Msg_Count
328 ,x_Msg_Data => x_Msg_Data
329 );
330 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334 -- Commit if required
335 IF (p_Commit = FND_API.G_TRUE) THEN
336 COMMIT;
337 END IF;
338
339 EXCEPTION
340 WHEN FND_API.G_EXC_ERROR THEN
341 ROLLBACK TO TransFuncAreaSP;
342 IF (x_msg_data IS NULL) THEN
343 FND_MSG_PUB.Count_And_Get
344 ( p_encoded => FND_API.G_FALSE
345 , p_count => x_msg_count
346 , p_data => x_msg_data
347 );
348 END IF;
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
351 ROLLBACK TO TransFuncAreaSP;
352 IF (x_msg_data IS NULL) THEN
353 FND_MSG_PUB.Count_And_Get
354 ( p_encoded => FND_API.G_FALSE
355 , p_count => x_msg_count
356 , p_data => x_msg_data
357 );
358 END IF;
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 WHEN NO_DATA_FOUND THEN
361 ROLLBACK TO TransFuncAreaSP;
362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
363 IF (x_msg_data IS NOT NULL) THEN
364 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Translate_Functional_Area ';
365 ELSE
366 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Translate_Functional_Area ';
367 END IF;
368 WHEN OTHERS THEN
369 ROLLBACK TO TransFuncAreaSP;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 IF (x_msg_data IS NOT NULL) THEN
372 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Translate_Functional_Area ';
373 ELSE
374 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Translate_Functional_Area ';
375 END IF;
376 END Translate_Functional_Area;
377
378
379 -- Load Functional Areas for LCT
380
381 PROCEDURE Load_Functional_Area(
382 p_Commit IN VARCHAR2 := FND_API.G_FALSE
383 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
384 ,x_Return_Status OUT NOCOPY VARCHAR2
385 ,x_Msg_Count OUT NOCOPY NUMBER
386 ,x_Msg_Data OUT NOCOPY VARCHAR2
387 ) IS
388
389 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
390
391 BEGIN
392 SAVEPOINT LoadFuncAreaSP;
393 FND_MSG_PUB.Initialize;
394 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
395
396 BIS_FUNCTIONAL_AREA_PUB.Retrieve_Functional_Area
397 (
398 p_Func_Area_Rec => p_Func_Area_Rec
399 ,x_Func_Area_Rec => l_Func_Area_Rec
400 ,x_Return_Status => x_Return_Status
401 ,x_Msg_Count => x_Msg_Count
402 ,x_Msg_Data => x_Msg_Data
403 );
404 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
405 BIS_FUNCTIONAL_AREA_PUB.Create_Functional_Area(
406 p_Api_Version => 1.0
407 ,p_Commit => p_Commit
408 ,p_Func_Area_Rec => p_Func_Area_Rec
409 ,x_Return_Status => x_Return_Status
410 ,x_Msg_Count => x_Msg_Count
411 ,x_Msg_Data => x_Msg_Data
412 );
413 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 END IF;
416 ELSE
417 IF (FND_LOAD_UTIL.UPLOAD_TEST(p_Func_Area_Rec.Last_Updated_By
418 , p_Func_Area_Rec.Last_Update_Date
419 , l_Func_Area_Rec.Last_Updated_By
420 , l_Func_Area_Rec.Last_Update_Date
421 , NULL)) THEN
422
423 BIS_FUNCTIONAL_AREA_PUB.Update_Functional_Area(
424 p_Api_Version => 1.0
425 ,p_Commit => p_Commit
426 ,p_Func_Area_Rec => p_Func_Area_Rec
427 ,x_Return_Status => x_Return_Status
428 ,x_Msg_Count => x_Msg_Count
429 ,x_Msg_Data => x_Msg_Data
430 );
431 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433 END IF;
434 END IF;
435 END IF;
436
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 ROLLBACK TO LoadFuncAreaSP;
440 IF (x_msg_data IS NULL) THEN
441 FND_MSG_PUB.Count_And_Get
442 ( p_encoded => FND_API.G_FALSE
443 , p_count => x_msg_count
444 , p_data => x_msg_data
445 );
446 END IF;
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 RAISE;
449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
450 ROLLBACK TO LoadFuncAreaSP;
451 IF (x_msg_data IS NULL) THEN
452 FND_MSG_PUB.Count_And_Get
453 ( p_encoded => FND_API.G_FALSE
454 , p_count => x_msg_count
455 , p_data => x_msg_data
456 );
457 END IF;
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459 RAISE;
460 WHEN NO_DATA_FOUND THEN
461 ROLLBACK TO LoadFuncAreaSP;
465 ELSE
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
463 IF (x_msg_data IS NOT NULL) THEN
464 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Load_Functional_Area ';
466 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Load_Functional_Area ';
467 END IF;
468 RAISE;
469 WHEN OTHERS THEN
470 ROLLBACK TO LoadFuncAreaSP;
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 IF (x_msg_data IS NOT NULL) THEN
473 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Load_Functional_Area ';
474 ELSE
475 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Load_Functional_Area ';
476 END IF;
477 RAISE;
478
479 END Load_Functional_Area;
480
481
482 -- This is the Functional Area/Application ID dependency table
483
484 PROCEDURE Create_Func_Area_Apps_Dep (
485 p_Api_Version IN NUMBER
486 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
487 ,p_Functional_Area_Id IN NUMBER
488 ,p_Application_Id IN NUMBER
489 ,x_Return_Status OUT NOCOPY VARCHAR2
490 ,x_Msg_Count OUT NOCOPY NUMBER
491 ,x_Msg_Data OUT NOCOPY VARCHAR2
492 ) IS
493
494 l_Count NUMBER;
495 --l_Default_Flag VARCHAR2(1);
496
497 BEGIN
498 SAVEPOINT CreateFuncAreaAppDepSP;
499 FND_MSG_PUB.Initialize;
500
501 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
502
503 Validate_Func_Area_Apps_Dep (
504 p_Functional_Area_Id => p_Functional_Area_Id
505 ,p_Application_Id => p_Application_Id
506 ,p_Action_Type => C_CREATE
507 ,x_Return_Status => x_Return_Status
508 ,x_Msg_Count => x_Msg_Count
509 ,x_Msg_Data => x_Msg_Data
510 );
511 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513 END IF;
514
515
516 BIS_FUNCTIONAL_AREA_PVT.Create_Func_Area_Apps_Dep (
517 p_Api_Version => 1.0
518 ,p_Commit => p_Commit
519 ,p_Functional_Area_Id => p_Functional_Area_Id
520 ,p_Application_Id => p_Application_Id
521 ,x_Return_Status => x_Return_Status
522 ,x_Msg_Count => x_Msg_Count
523 ,x_Msg_Data => x_Msg_Data
524 );
525
526 -- Commit if required
527 IF (p_Commit = FND_API.G_TRUE) THEN
528 COMMIT;
529 END IF;
530
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 ROLLBACK TO CreateFuncAreaAppDepSP;
534 IF (x_msg_data IS NULL) THEN
535 FND_MSG_PUB.Count_And_Get
536 ( p_encoded => FND_API.G_FALSE
537 , p_count => x_msg_count
538 , p_data => x_msg_data
539 );
540 END IF;
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
543 ROLLBACK TO CreateFuncAreaAppDepSP;
544 IF (x_msg_data IS NULL) THEN
545 FND_MSG_PUB.Count_And_Get
546 ( p_encoded => FND_API.G_FALSE
547 , p_count => x_msg_count
548 , p_data => x_msg_data
549 );
550 END IF;
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 WHEN NO_DATA_FOUND THEN
553 ROLLBACK TO CreateFuncAreaAppDepSP;
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555 IF (x_msg_data IS NOT NULL) THEN
556 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Create_Func_Area_Apps_Dep ';
557 ELSE
558 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Create_Func_Area_Apps_Dep ';
559 END IF;
560 WHEN OTHERS THEN
561 ROLLBACK TO CreateFuncAreaAppDepSP;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 IF (x_msg_data IS NOT NULL) THEN
564 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Create_Func_Area_Apps_Dep ';
565 ELSE
566 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Create_Func_Area_Apps_Dep ';
567 END IF;
568 END Create_Func_Area_Apps_Dep;
569
570 -- Update Functional Area dependency with Application ID
571 PROCEDURE Update_Func_Area_Apps_Dep (
572 p_Api_Version IN NUMBER
573 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
574 ,p_Functional_Area_Id IN NUMBER
575 ,p_Application_Id IN NUMBER
576 ,x_Return_Status OUT NOCOPY VARCHAR2
577 ,x_Msg_Count OUT NOCOPY NUMBER
578 ,x_Msg_Data OUT NOCOPY VARCHAR2
579 ) IS
580 l_Count NUMBER;
581 --l_Default_Flag VARCHAR2(1);
582 --l_Temp_Default_Flag VARCHAR2(1);
583
584 BEGIN
585 SAVEPOINT UpdateFuncAreaAppDepSP;
586 FND_MSG_PUB.Initialize;
587
588 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
589
590 Validate_Func_Area_Apps_Dep (
591 p_Functional_Area_Id => p_Functional_Area_Id
592 ,p_Application_Id => p_Application_Id
593 ,p_Action_Type => C_UPDATE
594 ,x_Return_Status => x_Return_Status
595 ,x_Msg_Count => x_Msg_Count
596 ,x_Msg_Data => x_Msg_Data
597 );
598 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 END IF;
601
602 BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep (
603 p_Api_Version => 1.0
604 ,p_Commit => p_Commit
608 ,x_Msg_Count => x_Msg_Count
605 ,p_Functional_Area_Id => p_Functional_Area_Id
606 ,p_Application_Id => p_Application_Id
607 ,x_Return_Status => x_Return_Status
609 ,x_Msg_Data => x_Msg_Data
610 );
611 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615 -- Commit if required
616 IF (p_Commit = FND_API.G_TRUE) THEN
617 COMMIT;
618 END IF;
619
620 EXCEPTION
621 WHEN FND_API.G_EXC_ERROR THEN
622 ROLLBACK TO UpdateFuncAreaAppDepSP;
623 IF (x_msg_data IS NULL) THEN
624 FND_MSG_PUB.Count_And_Get
625 ( p_encoded => FND_API.G_FALSE
626 , p_count => x_msg_count
627 , p_data => x_msg_data
628 );
629 END IF;
630 x_return_status := FND_API.G_RET_STS_ERROR;
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 ROLLBACK TO UpdateFuncAreaAppDepSP;
633 IF (x_msg_data IS NULL) THEN
634 FND_MSG_PUB.Count_And_Get
635 ( p_encoded => FND_API.G_FALSE
636 , p_count => x_msg_count
637 , p_data => x_msg_data
638 );
639 END IF;
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 WHEN NO_DATA_FOUND THEN
642 ROLLBACK TO UpdateFuncAreaAppDepSP;
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 IF (x_msg_data IS NOT NULL) THEN
645 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Update_Func_Area_Apps_Dep ';
646 ELSE
647 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Update_Func_Area_Apps_Dep ';
648 END IF;
649 WHEN OTHERS THEN
650 ROLLBACK TO UpdateFuncAreaAppDepSP;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 IF (x_msg_data IS NOT NULL) THEN
653 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Update_Func_Area_Apps_Dep ';
654 ELSE
655 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Update_Func_Area_Apps_Dep ';
656 END IF;
657
658 END Update_Func_Area_Apps_Dep;
659
660 -- Load API for loading Functional Area/Application ID dependnecy
661 PROCEDURE Load_Func_Area_Apps_Dep (
662 p_Commit IN VARCHAR2 := FND_API.G_FALSE
663 ,p_Func_Area_App_Dep_Rec IN BIS_FUNCTIONAL_AREA_PUB.Func_Area_Apps_Depend_Rec_Type
664 ,x_Return_Status OUT NOCOPY VARCHAR2
665 ,x_Msg_Count OUT NOCOPY NUMBER
666 ,x_Msg_Data OUT NOCOPY VARCHAR2
667 ) IS
668 l_Count NUMBER;
669 l_Func_Area_App_Dep_Rec BIS_FUNCTIONAL_AREA_PUB.Func_Area_Apps_Depend_Rec_Type;
670 BEGIN
671 SAVEPOINT LoadFuncAreaAppDepSP;
672 FND_MSG_PUB.Initialize;
673 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
674
675 l_Func_Area_App_Dep_Rec := p_Func_Area_App_Dep_Rec;
676
677 l_Func_Area_App_Dep_Rec.Functional_Area_Id
678 := Get_FA_Id_By_Short_Name (l_Func_Area_App_Dep_Rec.Func_Area_Short_Name);
679
680 IF ((l_Func_Area_App_Dep_Rec.Application_Id IS NULL) AND (l_Func_Area_App_Dep_Rec.Apps_Short_Name IS NOT NULL)) THEN
681 l_Func_Area_App_Dep_Rec.Application_Id
682 := BIS_UTIL.Get_Apps_Id_By_Short_Name (l_Func_Area_App_Dep_Rec.Apps_Short_Name);
683 END IF;
684
685 SELECT COUNT(1) INTO l_Count
686 FROM BIS_FUNC_AREA_APP_DEPENDENCY
687 WHERE FUNCTIONAL_AREA_ID = l_Func_Area_App_Dep_Rec.Functional_Area_Id
688 AND APPLICATION_ID = l_Func_Area_App_Dep_Rec.Application_Id;
689
690 IF (l_Count = 0) THEN
691 BIS_FUNCTIONAL_AREA_PUB.Create_Func_Area_Apps_Dep (
692 p_Api_Version => 1.0
693 ,p_Commit => p_Commit
694 ,p_Functional_Area_Id => l_Func_Area_App_Dep_Rec.Functional_Area_Id
695 ,p_Application_Id => l_Func_Area_App_Dep_Rec.Application_Id
696 ,x_Return_Status => x_Return_Status
697 ,x_Msg_Count => x_Msg_Count
698 ,x_Msg_Data => x_Msg_Data
699 );
700 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703 ELSE
704 BIS_FUNCTIONAL_AREA_PUB.Update_Func_Area_Apps_Dep (
705 p_Api_Version => 1.0
706 ,p_Commit => p_Commit
707 ,p_Functional_Area_Id => l_Func_Area_App_Dep_Rec.Functional_Area_Id
708 ,p_Application_Id => l_Func_Area_App_Dep_Rec.Application_Id
709 ,x_Return_Status => x_Return_Status
710 ,x_Msg_Count => x_Msg_Count
711 ,x_Msg_Data => x_Msg_Data
712 );
713 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 END IF;
716 END IF;
717
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 ROLLBACK TO LoadFuncAreaAppDepSP;
721 IF (x_msg_data IS NULL) THEN
722 FND_MSG_PUB.Count_And_Get
723 ( p_encoded => FND_API.G_FALSE
724 , p_count => x_msg_count
725 , p_data => x_msg_data
726 );
727 END IF;
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 RAISE;
730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
731 ROLLBACK TO LoadFuncAreaAppDepSP;
732 IF (x_msg_data IS NULL) THEN
733 FND_MSG_PUB.Count_And_Get
734 ( p_encoded => FND_API.G_FALSE
735 , p_count => x_msg_count
736 , p_data => x_msg_data
737 );
738 END IF;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 RAISE;
741 WHEN NO_DATA_FOUND THEN
742 ROLLBACK TO LoadFuncAreaAppDepSP;
743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
744 IF (x_msg_data IS NOT NULL) THEN
745 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Load_Func_Area_Apps_Dep ';
746 ELSE
747 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Load_Func_Area_Apps_Dep ';
748 END IF;
749 RAISE;
750 WHEN OTHERS THEN
751 ROLLBACK TO LoadFuncAreaAppDepSP;
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 IF (x_msg_data IS NOT NULL) THEN
754 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Load_Func_Area_Apps_Dep ';
755 ELSE
756 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Load_Func_Area_Apps_Dep ';
757 END IF;
758 RAISE;
759 END Load_Func_Area_Apps_Dep;
760
761 -- Validation API for Functional Area
762 PROCEDURE Validate_Functional_Area(
763 p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
764 ,p_Action_Type IN VARCHAR2
765 ,x_Return_Status OUT NOCOPY VARCHAR2
766 ,x_Msg_Count OUT NOCOPY NUMBER
767 ,x_Msg_Data OUT NOCOPY VARCHAR2
768 ) IS
769 l_Count NUMBER;
770 BEGIN
771 FND_MSG_PUB.Initialize;
772 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
773
774 IF (BIS_UTILITIES_PVT.Value_Missing_Or_Null(LTRIM(RTRIM(p_Func_Area_Rec.Short_Name))) = FND_API.G_TRUE) THEN
775 FND_MESSAGE.SET_NAME('BIS','BIS_FA_SHORT_NAME_IS_NULL');
776 FND_MSG_PUB.ADD;
777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778 END IF;
779
780
781 IF(p_Action_Type = C_CREATE) THEN
782
783
784 SELECT COUNT(1) INTO l_Count
785 FROM BIS_FUNCTIONAL_AREAS
786 WHERE UPPER(SHORT_NAME) = UPPER(p_Func_Area_Rec.Short_Name);
787
788 IF (l_Count <> 0) THEN
789 FND_MESSAGE.SET_NAME('BIS','BIS_FA_SHORT_NAME_EXISTS');
790 FND_MESSAGE.SET_TOKEN('SHORT_NAME', p_Func_Area_Rec.Short_Name);
791 FND_MSG_PUB.ADD;
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793 END IF;
794
795 IF (p_Func_Area_Rec.Functional_Area_Id IS NULL) THEN
796 FND_MESSAGE.SET_NAME('BIS','BIS_FUNC_ID_NOT_ENTERED');
797 FND_MSG_PUB.ADD;
798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
799 END IF;
800
801
802 IF (BIS_UTILITIES_PVT.Value_Missing_Or_Null(LTRIM(RTRIM(p_Func_Area_Rec.Name))) = FND_API.G_TRUE) THEN
803 FND_MESSAGE.SET_NAME('BIS','BIS_FUNC_NAME_NOT_ENTERED');
804 FND_MSG_PUB.ADD;
805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END IF;
807
808 ELSIF(p_Action_Type = C_UPDATE OR p_Action_Type = C_RETRIEVE OR p_Action_Type = C_DELETE) THEN
809
810 /*
811 IF (p_Func_Area_Rec.Functional_Area_Id IS NULL) THEN
812 FND_MESSAGE.SET_NAME('BIS','BIS_FUNC_ID_NOT_ENTERED');
813 FND_MSG_PUB.ADD;
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 END IF;
816 */
817
818 SELECT COUNT(1) INTO l_Count
819 FROM BIS_FUNCTIONAL_AREAS_VL
820 WHERE SHORT_NAME = p_Func_Area_Rec.Short_Name;
821
822 IF (l_Count = 0) THEN
823 FND_MESSAGE.SET_NAME('BIS','BIS_FA_SHORT_NAME_NOT_EXISTS');
824 FND_MESSAGE.SET_TOKEN('SHORT_NAME', p_Func_Area_Rec.Short_Name);
825 FND_MSG_PUB.ADD;
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 END IF;
830
831 EXCEPTION
832 WHEN FND_API.G_EXC_ERROR THEN
833 IF (x_msg_data IS NULL) THEN
834 FND_MSG_PUB.Count_And_Get
835 ( p_encoded => FND_API.G_FALSE
836 , p_count => x_msg_count
837 , p_data => x_msg_data
838 );
839 END IF;
840 x_return_status := FND_API.G_RET_STS_ERROR;
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842 IF (x_msg_data IS NULL) THEN
843 FND_MSG_PUB.Count_And_Get
844 ( p_encoded => FND_API.G_FALSE
845 , p_count => x_msg_count
846 , p_data => x_msg_data
847 );
848 END IF;
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
850 WHEN NO_DATA_FOUND THEN
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 IF (x_msg_data IS NOT NULL) THEN
853 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Validate_Functional_Area ';
854 ELSE
855 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Validate_Functional_Area ';
856 END IF;
857 WHEN OTHERS THEN
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
859 IF (x_msg_data IS NOT NULL) THEN
860 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Validate_Functional_Area ';
861 ELSE
862 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Validate_Functional_Area ';
863 END IF;
864
865 END Validate_Functional_Area;
866
867
868 -- Validate Functional area and Application Dependency.
869 PROCEDURE Validate_Func_Area_Apps_Dep (
870 p_Functional_Area_Id IN NUMBER
871 ,p_Application_Id IN NUMBER
872 ,p_Action_Type IN VARCHAR2
873 ,x_Return_Status OUT NOCOPY VARCHAR2
874 ,x_Msg_Count OUT NOCOPY NUMBER
875 ,x_Msg_Data OUT NOCOPY VARCHAR2
876 ) IS
877 l_Count NUMBER;
878 BEGIN
879 FND_MSG_PUB.Initialize;
880 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
881
882 IF (p_Functional_Area_Id IS NULL) THEN
883 FND_MESSAGE.SET_NAME('BIS','BIS_FUNC_ID_NOT_ENTERED');
884 FND_MSG_PUB.ADD;
885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887
888 IF (p_Application_Id IS NULL) THEN
889 FND_MESSAGE.SET_NAME('BIS','BIS_APP_ID_NOT_ENTERED');
890 FND_MSG_PUB.ADD;
891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
892 END IF;
893
894 SELECT COUNT(1) INTO l_Count
895 FROM BIS_FUNCTIONAL_AREAS
896 WHERE FUNCTIONAL_AREA_ID = p_Functional_Area_Id;
897
898 IF (l_Count = 0) THEN
899 FND_MESSAGE.SET_NAME('BIS','BIS_FA_ID_NOT_EXISTS');
900 FND_MESSAGE.SET_TOKEN('FA_ID', p_Functional_Area_Id);
901 FND_MSG_PUB.ADD;
902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903 END IF;
904
905
906
907 IF(p_Action_Type = C_CREATE) THEN
908 -- Internally -1 Application ID would mean a default application id
909 IF(p_Application_Id <> -1) THEN
910 SELECT COUNT(1) INTO l_Count
911 FROM FND_APPLICATION
912 WHERE APPLICATION_ID = p_Application_Id;
913
914 IF (l_Count = 0) THEN
915 FND_MESSAGE.SET_NAME('BIS','BIS_APPS_ID_NOT_EXISTS');
916 FND_MESSAGE.SET_TOKEN('APPS_ID', p_Application_Id);
917 FND_MSG_PUB.ADD;
918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
919 END IF;
920 END IF;
921
925 AND APPLICATION_ID = p_Application_Id;
922 SELECT COUNT(1) INTO l_Count
923 FROM BIS_FUNC_AREA_APP_DEPENDENCY
924 WHERE FUNCTIONAL_AREA_ID = p_Functional_Area_Id
926
927 IF l_Count <> 0 THEN
928 FND_MESSAGE.SET_NAME('BIS','BIS_FA_APPS_ID_EXIST');
929 FND_MESSAGE.SET_TOKEN('FA_ID', p_Functional_Area_Id);
930 FND_MESSAGE.SET_TOKEN('APPS_ID', p_Application_Id);
931 FND_MSG_PUB.ADD;
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934
935
936 SELECT COUNT(1) INTO l_Count
937 FROM BIS_FUNC_AREA_APP_DEPENDENCY
938 WHERE APPLICATION_ID = p_Application_Id;
939
940 IF l_Count > 0 THEN
941 FND_MESSAGE.SET_NAME('BIS','BIS_FA_APPS_ID_ALREADY_EXIST');
942 FND_MESSAGE.SET_TOKEN('APPS_ID', p_Application_Id);
943 FND_MSG_PUB.ADD;
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END IF;
946
947
948
949
950 ELSIF(p_Action_Type = C_UPDATE OR p_Action_Type = C_DELETE) THEN
951
952 SELECT COUNT(1) INTO l_Count
953 FROM BIS_FUNC_AREA_APP_DEPENDENCY
954 WHERE FUNCTIONAL_AREA_ID = p_Functional_Area_Id
955 AND APPLICATION_ID = p_Application_Id;
956
957 IF (l_Count = 0) THEN
958 FND_MESSAGE.SET_NAME('BIS','BIS_FA_APPS_ID_NOT_EXIST');
959 FND_MESSAGE.SET_TOKEN('APPS_ID', p_Application_Id);
960 FND_MESSAGE.SET_TOKEN('FA_ID', p_Functional_Area_Id);
961 FND_MSG_PUB.ADD;
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963 END IF;
964
965
966 END IF;
967
968 EXCEPTION
969
970 WHEN FND_API.G_EXC_ERROR THEN
971 IF (x_msg_data IS NULL) THEN
972 FND_MSG_PUB.Count_And_Get
973 ( p_encoded => FND_API.G_FALSE
974 , p_count => x_msg_count
975 , p_data => x_msg_data
976 );
977 END IF;
978 x_return_status := FND_API.G_RET_STS_ERROR;
979 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
980 IF (x_msg_data IS NULL) THEN
981 FND_MSG_PUB.Count_And_Get
982 ( p_encoded => FND_API.G_FALSE
983 , p_count => x_msg_count
984 , p_data => x_msg_data
985 );
986 END IF;
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 WHEN NO_DATA_FOUND THEN
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990 IF (x_msg_data IS NOT NULL) THEN
991 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Validate_Func_Area_Apps_Dep ';
992 ELSE
993 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Validate_Func_Area_Apps_Dep ';
994 END IF;
995 WHEN OTHERS THEN
996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 IF (x_msg_data IS NOT NULL) THEN
998 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Validate_Func_Area_Apps_Dep ';
999 ELSE
1000 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Validate_Func_Area_Apps_Dep ';
1001 END IF;
1002 END Validate_Func_Area_Apps_Dep;
1003
1004 -- Returns the next functional area id, this may not be multiuser friendly
1005
1006 FUNCTION Get_Next_Functional_Area_Id
1007 RETURN NUMBER IS
1008 l_next NUMBER;
1009 --l_Max_Id NUMBER;
1010 BEGIN
1011 l_next := 0;
1012
1013 /*SELECT (NVL(MAX(FUNCTIONAL_AREA_ID), 0)+1)
1014 INTO l_Max_Id
1015 FROM BIS_FUNCTIONAL_AREAS;
1016
1017 RETURN l_Max_Id;
1018 */
1019
1020 SELECT BIS_FUNC_AREA_ID_S.NEXTVAL
1021 INTO l_next
1022 FROM DUAL;
1023
1024 return l_next;
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 RETURN 0;
1029 END Get_Next_Functional_Area_Id;
1030
1031 -- Get the Functional Area ID from Functional Short_Name
1032 FUNCTION Get_FA_Id_By_Short_Name (
1033 p_Functional_Area_Short_Name IN VARCHAR2
1034 )RETURN NUMBER IS
1035 l_FA_Id NUMBER;
1036 BEGIN
1037 SELECT FUNCTIONAL_AREA_ID
1038 INTO l_FA_Id
1039 FROM BIS_FUNCTIONAL_AREAS
1040 WHERE UPPER(SHORT_NAME) = UPPER(p_Functional_Area_Short_Name);
1041
1042 RETURN l_FA_Id;
1043
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 RETURN -1;
1047 END Get_FA_Id_By_Short_Name;
1048
1049 -- Delete the Functional Area
1050 PROCEDURE Delete_Functional_Area(
1051 p_Api_Version IN NUMBER
1052 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
1053 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
1054 ,x_Return_Status OUT NOCOPY VARCHAR2
1055 ,x_Msg_Count OUT NOCOPY NUMBER
1056 ,x_Msg_Data OUT NOCOPY VARCHAR2
1057 ) IS
1058 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
1059 l_Count NUMBER;
1060
1061 CURSOR c_FuncArea_Dep IS
1062 SELECT B.FUNCTIONAL_AREA_ID,
1063 B.APPLICATION_ID
1064 FROM BIS_FUNC_AREA_APP_DEPENDENCY B
1065 WHERE B.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
1066 BEGIN
1067 SAVEPOINT DeleteFuncAreaSP;
1068 FND_MSG_PUB.Initialize;
1069 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1070 l_Func_Area_Rec := p_Func_Area_Rec;
1071
1072 Validate_Functional_Area(
1073 p_Func_Area_Rec => p_Func_Area_Rec
1074 ,p_Action_Type => C_DELETE
1075 ,x_Return_Status => x_Return_Status
1076 ,x_Msg_Count => x_Msg_Count
1077 ,x_Msg_Data => x_Msg_Data
1081 END IF;
1078 );
1079 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082
1083
1084 BIS_FUNCTIONAL_AREA_PUB.Retrieve_Functional_Area
1085 (
1086 p_Func_Area_Rec => p_Func_Area_Rec
1087 ,x_Func_Area_Rec => l_Func_Area_Rec
1088 ,x_Return_Status => x_Return_Status
1089 ,x_Msg_Count => x_Msg_Count
1090 ,x_Msg_Data => x_Msg_Data
1091 );
1092 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1094 END IF;
1095
1096 BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area(
1097 p_Api_Version => 1.0
1098 ,p_Commit => p_Commit
1099 ,p_Func_Area_Rec => l_Func_Area_Rec
1100 ,x_Return_Status => x_Return_Status
1101 ,x_Msg_Count => x_Msg_Count
1102 ,x_Msg_Data => x_Msg_Data
1103 );
1104 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1106 END IF;
1107
1108 -- Commit if required
1109 IF (p_Commit = FND_API.G_TRUE) THEN
1110 COMMIT;
1111 END IF;
1112
1113 EXCEPTION
1114 WHEN FND_API.G_EXC_ERROR THEN
1115 ROLLBACK TO DeleteFuncAreaSP;
1116 IF (x_msg_data IS NULL) THEN
1117 FND_MSG_PUB.Count_And_Get
1118 ( p_encoded => FND_API.G_FALSE
1119 , p_count => x_msg_count
1120 , p_data => x_msg_data
1121 );
1122 END IF;
1123 x_return_status := FND_API.G_RET_STS_ERROR;
1124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1125 ROLLBACK TO DeleteFuncAreaSP;
1126 IF (x_msg_data IS NULL) THEN
1127 FND_MSG_PUB.Count_And_Get
1128 ( p_encoded => FND_API.G_FALSE
1129 , p_count => x_msg_count
1130 , p_data => x_msg_data
1131 );
1132 END IF;
1133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 WHEN NO_DATA_FOUND THEN
1135 ROLLBACK TO DeleteFuncAreaSP;
1136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1137 IF (x_msg_data IS NOT NULL) THEN
1138 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Delete_Functional_Area ';
1139 ELSE
1140 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Delete_Functional_Area ';
1141 END IF;
1142 WHEN OTHERS THEN
1143 ROLLBACK TO DeleteFuncAreaSP;
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1145 IF (x_msg_data IS NOT NULL) THEN
1146 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Delete_Functional_Area ';
1147 ELSE
1148 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Delete_Functional_Area ';
1149 END IF;
1150 END Delete_Functional_Area;
1151
1152 -- remove Functional Area/Application ID dependency
1153 PROCEDURE Remove_Func_Area_Apps_Dep (
1154 p_Api_Version IN NUMBER
1155 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
1156 ,p_Functional_Area_Id IN NUMBER
1157 ,p_Application_Id IN NUMBER
1158 ,x_Return_Status OUT NOCOPY VARCHAR2
1159 ,x_Msg_Count OUT NOCOPY NUMBER
1160 ,x_Msg_Data OUT NOCOPY VARCHAR2
1161 ) IS
1162 l_Count NUMBER;
1163
1164 BEGIN
1165 SAVEPOINT RemoveFuncAreaDependencySP;
1166 FND_MSG_PUB.Initialize;
1167 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1168
1169 Validate_Func_Area_Apps_Dep (
1170 p_Functional_Area_Id => p_Functional_Area_Id
1171 ,p_Application_Id => p_Application_Id
1172 ,p_Action_Type => C_DELETE
1173 ,x_Return_Status => x_Return_Status
1174 ,x_Msg_Count => x_Msg_Count
1175 ,x_Msg_Data => x_Msg_Data
1176 );
1177 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1179 END IF;
1180
1181 BIS_FUNCTIONAL_AREA_PVT.Remove_Func_Area_Apps_Dep (
1182 p_Api_Version => 1.0
1183 ,p_Commit => p_Commit
1184 ,p_Functional_Area_Id => p_Functional_Area_Id
1185 ,p_Application_Id => p_Application_Id
1186 ,x_Return_Status => x_Return_Status
1187 ,x_Msg_Count => x_Msg_Count
1188 ,x_Msg_Data => x_Msg_Data
1189 );
1190 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1192 END IF;
1193
1194 -- Commit if required
1195 IF (p_Commit = FND_API.G_TRUE) THEN
1196 COMMIT;
1197 END IF;
1198
1199 EXCEPTION
1200 WHEN FND_API.G_EXC_ERROR THEN
1201 ROLLBACK TO RemoveFuncAreaDependencySP;
1202 IF (x_msg_data IS NULL) THEN
1203 FND_MSG_PUB.Count_And_Get
1204 ( p_encoded => FND_API.G_FALSE
1205 , p_count => x_msg_count
1206 , p_data => x_msg_data
1207 );
1208 END IF;
1209 x_return_status := FND_API.G_RET_STS_ERROR;
1210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1211 ROLLBACK TO RemoveFuncAreaDependencySP;
1212 IF (x_msg_data IS NULL) THEN
1213 FND_MSG_PUB.Count_And_Get
1214 ( p_encoded => FND_API.G_FALSE
1215 , p_count => x_msg_count
1216 , p_data => x_msg_data
1217 );
1218 END IF;
1219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1220 WHEN NO_DATA_FOUND THEN
1221 ROLLBACK TO RemoveFuncAreaDependencySP;
1222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1223 IF (x_msg_data IS NOT NULL) THEN
1224 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Remove_Func_Area_Apps_Dep ';
1225 ELSE
1226 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Remove_Func_Area_Apps_Dep ';
1227 END IF;
1228 WHEN OTHERS THEN
1229 ROLLBACK TO RemoveFuncAreaDependencySP;
1230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231 IF (x_msg_data IS NOT NULL) THEN
1232 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PUB.Remove_Func_Area_Apps_Dep ';
1233 ELSE
1234 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PUB.Remove_Func_Area_Apps_Dep ';
1235 END IF;
1236 END Remove_Func_Area_Apps_Dep;
1237
1238 -- Add language API
1239 PROCEDURE ADD_LANGUAGE
1240 IS
1241 BEGIN
1242 BIS_FUNCTIONAL_AREA_PVT.ADD_LANGUAGE;
1243 END ADD_LANGUAGE;
1244
1245
1246 END BIS_FUNCTIONAL_AREA_PUB;