[Home] [Help]
PACKAGE BODY: APPS.BIS_FUNCTIONAL_AREA_PVT
Source
1 PACKAGE BODY BIS_FUNCTIONAL_AREA_PVT AS
2 /* $Header: BISVFASB.pls 120.0 2005/06/01 16:05:07 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 | BISVFASB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Private 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_PVT';
20
21 /*
22 Private CRUD APIs
23 */
24
25
26 PROCEDURE Create_Functional_Area(
27 p_Api_Version IN NUMBER
28 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
29 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
30 ,x_Return_Status OUT NOCOPY VARCHAR2
31 ,x_Msg_Count OUT NOCOPY NUMBER
32 ,x_Msg_Data OUT NOCOPY VARCHAR2
33 ) IS
34
35 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
36 l_Count NUMBER;
37
38 BEGIN
39 SAVEPOINT CreateFuncAreaSP_Pvt;
40 FND_MSG_PUB.Initialize;
41
42 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
43 l_Func_Area_Rec := p_Func_Area_Rec;
44
45 IF(l_Func_Area_Rec.Created_By IS NULL) THEN
46 l_Func_Area_Rec.Created_By := FND_GLOBAL.USER_ID;
47 END IF;
48
49 IF(l_Func_Area_Rec.Last_Updated_By IS NULL) THEN
50 l_Func_Area_Rec.Last_Updated_By := l_Func_Area_Rec.Created_By;
51 END IF;
52
53 IF(l_Func_Area_Rec.Last_Update_Login IS NULL) THEN
54 l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
55 END IF;
56
57 IF(l_Func_Area_Rec.Last_Update_Date IS NULL) THEN
58 l_Func_Area_Rec.Creation_Date := SYSDATE;
59 l_Func_Area_Rec.Last_Update_Date := SYSDATE;
60 ELSE
61 l_Func_Area_Rec.Creation_Date := l_Func_Area_Rec.Last_Update_Date;
62 END IF;
63
64 -- INSERT THE ACTUAL VALUES INTO BASE TABLES
65 INSERT INTO BIS_FUNCTIONAL_AREAS
66 (
67 FUNCTIONAL_AREA_ID
68 , SHORT_NAME
69 , CREATED_BY
70 , CREATION_DATE
71 , LAST_UPDATED_BY
72 , LAST_UPDATE_DATE
73 , LAST_UPDATE_LOGIN
74 )
75 VALUES
76 (
77 l_Func_Area_Rec.Functional_Area_Id
78 , l_Func_Area_Rec.Short_Name
79 , l_Func_Area_Rec.Created_By
80 , l_Func_Area_Rec.Creation_Date
81 , l_Func_Area_Rec.Last_Updated_By
82 , l_Func_Area_Rec.Last_Update_Date
83 , l_Func_Area_Rec.Last_Update_Login
84 );
85
86 -- INSERT THE ACTUAL VALUES INTO TRANSLATABLE TABLES
87 INSERT INTO BIS_FUNCTIONAL_AREAS_TL
88 (
89 FUNCTIONAL_AREA_ID
90 , NAME
91 , DESCRIPTION
92 , LANGUAGE
93 , SOURCE_LANG
94 , CREATED_BY
95 , CREATION_DATE
96 , LAST_UPDATED_BY
97 , LAST_UPDATE_DATE
98 , LAST_UPDATE_LOGIN
99 )
100 SELECT
101 l_Func_Area_Rec.Functional_Area_Id
102 , l_Func_Area_Rec.Name
103 , l_Func_Area_Rec.Description
104 , L.LANGUAGE_CODE
105 , USERENV('LANG')
106 , l_Func_Area_Rec.Created_By
107 , l_Func_Area_Rec.Creation_Date
108 , l_Func_Area_Rec.Last_Updated_By
109 , l_Func_Area_Rec.Last_Update_Date
110 , l_Func_Area_Rec.Last_Update_Login
111 FROM FND_LANGUAGES L
112 WHERE L.INSTALLED_FLAG IN ('I', 'B')
113 AND NOT EXISTS
114 (
115 SELECT NULL
116 FROM BIS_FUNCTIONAL_AREAS_TL T
117 WHERE T.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
118 AND T.LANGUAGE = L.LANGUAGE_CODE
119 );
120
121 -- Commit if required
122 IF (p_Commit = FND_API.G_TRUE) THEN
123 COMMIT;
124 END IF;
125
126 EXCEPTION
127 WHEN FND_API.G_EXC_ERROR THEN
128 ROLLBACK TO CreateFuncAreaSP_Pvt;
129 IF (x_msg_data IS NULL) THEN
130 FND_MSG_PUB.Count_And_Get
131 ( p_encoded => FND_API.G_FALSE
132 , p_count => x_msg_count
133 , p_data => x_msg_data
134 );
135 END IF;
136 x_return_status := FND_API.G_RET_STS_ERROR;
137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
138 ROLLBACK TO CreateFuncAreaSP_Pvt;
139 IF (x_msg_data IS NULL) THEN
140 FND_MSG_PUB.Count_And_Get
141 ( p_encoded => FND_API.G_FALSE
142 , p_count => x_msg_count
143 , p_data => x_msg_data
144 );
145 END IF;
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 WHEN NO_DATA_FOUND THEN
148 ROLLBACK TO CreateFuncAreaSP_Pvt;
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 IF (x_msg_data IS NOT NULL) THEN
151 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Create_Functional_Area ';
152 ELSE
153 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Create_Functional_Area ';
154 END IF;
155 WHEN OTHERS THEN
156 ROLLBACK TO CreateFuncAreaSP_Pvt;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 IF (x_msg_data IS NOT NULL) THEN
159 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Create_Functional_Area ';
160 ELSE
161 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Create_Functional_Area ';
162 END IF;
163 END Create_Functional_Area;
164
165
166
167 -- Update Functional Area API
168 PROCEDURE Update_Functional_Area(
169 p_Api_Version IN NUMBER
170 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
171 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
172 ,x_Return_Status OUT NOCOPY VARCHAR2
173 ,x_Msg_Count OUT NOCOPY NUMBER
174 ,x_Msg_Data OUT NOCOPY VARCHAR2
175 ) IS
176 l_Count NUMBER;
177 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
178 BEGIN
179 SAVEPOINT UpdateFuncAreaSP_Pvt;
180 FND_MSG_PUB.Initialize;
181 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
182
183 BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area
184 (
185 p_Func_Area_Rec => p_Func_Area_Rec
186 ,x_Func_Area_Rec => l_Func_Area_Rec
187 ,x_Return_Status => x_Return_Status
188 ,x_Msg_Count => x_Msg_Count
189 ,x_Msg_Data => x_Msg_Data
190 );
191 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
193 END IF;
194
195 --DBMS_OUTPUT.PUT_LINE ('l_Func_Area_Rec.Short_Name - ' || l_Func_Area_Rec.Short_Name);
196 -- Name can never be null
197 IF (l_Func_Area_Rec.Name <> p_Func_Area_Rec.Name) THEN
198 l_Func_Area_Rec.Name := p_Func_Area_Rec.Name;
199 END IF;
200 --DBMS_OUTPUT.PUT_LINE ('l_Func_Area_Rec.Name - ' || l_Func_Area_Rec.Name);
201
202 IF ((p_Func_Area_Rec.Description IS NULL) OR (l_Func_Area_Rec.Description <> p_Func_Area_Rec.Description)) THEN
203 l_Func_Area_Rec.Description := p_Func_Area_Rec.Description;
204 END IF;
205
206 IF(p_Func_Area_Rec.Last_Update_Date IS NULL) THEN
207 l_Func_Area_Rec.Last_Update_Date := SYSDATE;
208 ELSE
209 l_Func_Area_Rec.Last_Update_Date := p_Func_Area_Rec.Last_Update_Date ;
210 END IF;
211
212 IF (p_Func_Area_Rec.Last_Updated_By IS NULL) THEN
213 l_Func_Area_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
214 ELSE
215 l_Func_Area_Rec.Last_Updated_By := p_Func_Area_Rec.Last_Updated_By;
216 END IF;
217
218 IF (p_Func_Area_Rec.Last_Update_Login IS NULL) THEN
219 l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
220 ELSE
221 l_Func_Area_Rec.Last_Update_Login := p_Func_Area_Rec.Last_Update_Login;
222 END IF;
223
224 -- Update the base table
225 UPDATE BIS_FUNCTIONAL_AREAS
226 SET
227 LAST_UPDATED_BY = l_Func_Area_Rec.Last_Updated_By
228 , LAST_UPDATE_DATE = l_Func_Area_Rec.Last_Update_Date
229 , LAST_UPDATE_LOGIN = l_Func_Area_Rec.Last_Update_Login
230 WHERE
231 SHORT_NAME = l_Func_Area_Rec.Short_Name;
232
233 -- Translate the Measures
234 BIS_FUNCTIONAL_AREA_PVT.Translate_Functional_Area(
235 p_Api_Version => 1.0
236 ,p_Commit => p_Commit
237 ,p_Func_Area_Rec => p_Func_Area_Rec
238 ,x_Return_Status => x_Return_Status
239 ,x_Msg_Count => x_Msg_Count
240 ,x_Msg_Data => x_Msg_Data
241 );
242 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 -- Commit if required
247 IF (p_Commit = FND_API.G_TRUE) THEN
248 COMMIT;
249 END IF;
250
251 EXCEPTION
252 WHEN FND_API.G_EXC_ERROR THEN
253 ROLLBACK TO UpdateFuncAreaSP_Pvt;
254 IF (x_msg_data IS NULL) THEN
255 FND_MSG_PUB.Count_And_Get
256 ( p_encoded => FND_API.G_FALSE
257 , p_count => x_msg_count
258 , p_data => x_msg_data
259 );
260 END IF;
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
263 ROLLBACK TO UpdateFuncAreaSP_Pvt;
264 IF (x_msg_data IS NULL) THEN
265 FND_MSG_PUB.Count_And_Get
266 ( p_encoded => FND_API.G_FALSE
267 , p_count => x_msg_count
268 , p_data => x_msg_data
269 );
270 END IF;
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 WHEN NO_DATA_FOUND THEN
273 ROLLBACK TO UpdateFuncAreaSP_Pvt;
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275 IF (x_msg_data IS NOT NULL) THEN
276 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
277 ELSE
278 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
279 END IF;
280 WHEN OTHERS THEN
281 ROLLBACK TO UpdateFuncAreaSP_Pvt;
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 IF (x_msg_data IS NOT NULL) THEN
284 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
285 ELSE
286 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
287 END IF;
288
289 END Update_Functional_Area;
290
291 -- Translate the Functional Area Name/Description
292 PROCEDURE Translate_Functional_Area(
293 p_Api_Version IN NUMBER
297 ,x_Msg_Count OUT NOCOPY NUMBER
294 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
295 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
296 ,x_Return_Status OUT NOCOPY VARCHAR2
298 ,x_Msg_Data OUT NOCOPY VARCHAR2
299 ) IS
300 l_Count NUMBER;
301 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
302
303 BEGIN
304 SAVEPOINT TransFuncAreaSP_Pvt;
305 FND_MSG_PUB.Initialize;
306 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
307
308 BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area
309 (
310 p_Func_Area_Rec => p_Func_Area_Rec
311 ,x_Func_Area_Rec => l_Func_Area_Rec
312 ,x_Return_Status => x_Return_Status
313 ,x_Msg_Count => x_Msg_Count
314 ,x_Msg_Data => x_Msg_Data
315 );
316 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319
320 -- Name can never be null
321 IF (l_Func_Area_Rec.Name <> p_Func_Area_Rec.Name) THEN
322 l_Func_Area_Rec.Name := p_Func_Area_Rec.Name;
323 END IF;
324
325 l_Func_Area_Rec.Description := p_Func_Area_Rec.Description;
326
327 IF (p_Func_Area_Rec.Last_Update_Date IS NULL) THEN
328 l_Func_Area_Rec.Last_Update_Date := SYSDATE;
329 ELSE
330 l_Func_Area_Rec.Last_Update_Date := p_Func_Area_Rec.Last_Update_Date;
331 END IF;
332
333 IF (p_Func_Area_Rec.Last_Updated_By IS NULL) THEN
334 l_Func_Area_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
335 ELSE
336 l_Func_Area_Rec.Last_Updated_By := p_Func_Area_Rec.Last_Updated_By;
337 END IF;
338
339 IF (p_Func_Area_Rec.Last_Update_Login IS NULL) THEN
340 l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
341 ELSE
342 l_Func_Area_Rec.Last_Update_Login := p_Func_Area_Rec.Last_Update_Login;
343 END IF;
344
345 --DBMS_OUTPUT.PUT_LINE ('T l_Func_Area_Rec.Name - ' || l_Func_Area_Rec.Name );
346 --DBMS_OUTPUT.PUT_LINE ('T p_Func_Area_Rec.Name - ' || p_Func_Area_Rec.Name);
347 --DBMS_OUTPUT.PUT_LINE ('T l_Func_Area_Rec.Description - ' || l_Func_Area_Rec.Description );
348 --DBMS_OUTPUT.PUT_LINE ('T p_Func_Area_Rec.Description - ' || p_Func_Area_Rec.Description);
349 --DBMS_OUTPUT.PUT_LINE ('T l_Func_Area_Rec.Last_Update_Date - ' || l_Func_Area_Rec.Last_Update_Date);
350 --DBMS_OUTPUT.PUT_LINE ('T p_Func_Area_Rec.Last_Updated_By - ' || p_Func_Area_Rec.Last_Updated_By);
351 --DBMS_OUTPUT.PUT_LINE ('T l_Func_Area_Rec.Last_Update_Date - ' || l_Func_Area_Rec.Last_Update_Date);
352
353 -- Update the base table
354 UPDATE BIS_FUNCTIONAL_AREAS_TL
355 SET
356 NAME = l_Func_Area_Rec.Name
357 , DESCRIPTION = l_Func_Area_Rec.Description
358 , LAST_UPDATED_BY = l_Func_Area_Rec.Last_Updated_By
359 , LAST_UPDATE_DATE = l_Func_Area_Rec.Last_Update_Date
360 , LAST_UPDATE_LOGIN = l_Func_Area_Rec.Last_Update_Login
361 , SOURCE_LANG = USERENV('LANG')
362 WHERE
363 FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
364 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
365
366 -- Translate the Measures
367
368 -- Commit if required
369 IF (p_Commit = FND_API.G_TRUE) THEN
370 COMMIT;
371 END IF;
372
373 EXCEPTION
374 WHEN FND_API.G_EXC_ERROR THEN
375 ROLLBACK TO TransFuncAreaSP_Pvt;
376 IF (x_msg_data IS NULL) THEN
377 FND_MSG_PUB.Count_And_Get
378 ( p_encoded => FND_API.G_FALSE
379 , p_count => x_msg_count
380 , p_data => x_msg_data
381 );
382 END IF;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 ROLLBACK TO TransFuncAreaSP_Pvt;
386 IF (x_msg_data IS NULL) THEN
387 FND_MSG_PUB.Count_And_Get
388 ( p_encoded => FND_API.G_FALSE
389 , p_count => x_msg_count
390 , p_data => x_msg_data
391 );
392 END IF;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 WHEN NO_DATA_FOUND THEN
395 ROLLBACK TO TransFuncAreaSP_Pvt;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 IF (x_msg_data IS NOT NULL) THEN
398 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Translate_Functional_Area ';
399 ELSE
400 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Translate_Functional_Area ';
401 END IF;
402 WHEN OTHERS THEN
403 ROLLBACK TO TransFuncAreaSP_Pvt;
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 IF (x_msg_data IS NOT NULL) THEN
406 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Translate_Functional_Area ';
407 ELSE
408 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Translate_Functional_Area ';
409 END IF;
410 END Translate_Functional_Area;
411
412 PROCEDURE Retrieve_Functional_Area(
413 p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
414 ,x_Func_Area_Rec OUT NOCOPY BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
415 ,x_Return_Status OUT NOCOPY VARCHAR2
416 ,x_Msg_Count OUT NOCOPY NUMBER
417 ,x_Msg_Data OUT NOCOPY VARCHAR2
418 ) IS
419
420 l_Count NUMBER;
421
422 CURSOR cRetrieveFuncArea IS
423 SELECT F.FUNCTIONAL_AREA_ID
424 , F.SHORT_NAME
425 , F.NAME
426 , F.DESCRIPTION
427 , F.CREATED_BY
428 , F.CREATION_DATE
429 , F.LAST_UPDATED_BY
430 , F.LAST_UPDATE_DATE
431 , F.LAST_UPDATE_LOGIN
432 FROM BIS_FUNCTIONAL_AREAS_VL F
433 WHERE F.SHORT_NAME = p_Func_Area_Rec.Short_Name;
434
435 BEGIN
436 FND_MSG_PUB.Initialize;
437 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
438 l_Count := 0;
439
440 FOR cRFA IN cRetrieveFuncArea LOOP
441 x_Func_Area_Rec.Functional_Area_Id := cRFA.FUNCTIONAL_AREA_ID;
442 x_Func_Area_Rec.Short_Name := cRFA.SHORT_NAME;
443 x_Func_Area_Rec.Name := cRFA.NAME;
444 x_Func_Area_Rec.Description := cRFA.DESCRIPTION;
445 x_Func_Area_Rec.Created_By := cRFA.CREATED_BY;
446 x_Func_Area_Rec.Creation_Date := cRFA.CREATION_DATE;
447 x_Func_Area_Rec.Last_Updated_By := cRFA.LAST_UPDATED_BY;
448 x_Func_Area_Rec.Last_Update_Date := cRFA.LAST_UPDATE_DATE;
449 x_Func_Area_Rec.Last_Update_Login := cRFA.LAST_UPDATE_LOGIN;
450 l_Count := 1;
451 END LOOP;
452
453 IF (l_Count = 0) THEN
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
456
457 EXCEPTION
458 WHEN FND_API.G_EXC_ERROR THEN
459 IF (x_msg_data IS NULL) THEN
460 FND_MSG_PUB.Count_And_Get
461 ( p_encoded => FND_API.G_FALSE
462 , p_count => x_msg_count
466 x_return_status := FND_API.G_RET_STS_ERROR;
463 , p_data => x_msg_data
464 );
465 END IF;
467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
468 IF (x_msg_data IS NULL) THEN
469 FND_MSG_PUB.Count_And_Get
470 ( p_encoded => FND_API.G_FALSE
471 , p_count => x_msg_count
472 , p_data => x_msg_data
473 );
474 END IF;
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 WHEN NO_DATA_FOUND THEN
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 IF (x_msg_data IS NOT NULL) THEN
479 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area ';
480 ELSE
481 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area ';
482 END IF;
483 WHEN OTHERS THEN
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 IF (x_msg_data IS NOT NULL) THEN
486 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area ';
487 ELSE
488 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Retrieve_Functional_Area ';
489 END IF;
490 END Retrieve_Functional_Area;
491
492
493
494 -- This is the Functional Area/Application ID dependency table
495
496 PROCEDURE Create_Func_Area_Apps_Dep (
497 p_Api_Version IN NUMBER
498 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
499 ,p_Functional_Area_Id IN NUMBER
500 ,p_Application_Id IN NUMBER
501 ,x_Return_Status OUT NOCOPY VARCHAR2
502 ,x_Msg_Count OUT NOCOPY NUMBER
503 ,x_Msg_Data OUT NOCOPY VARCHAR2
504 ) IS
505
506 l_Count NUMBER;
507 --l_Default_Flag VARCHAR2(1);
508
509 BEGIN
510 SAVEPOINT CreateFuncAreaAppDepSP_Pvt;
511 FND_MSG_PUB.Initialize;
512
513 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
514
515 INSERT INTO BIS_FUNC_AREA_APP_DEPENDENCY
516 (
517 FUNCTIONAL_AREA_ID
518 ,APPLICATION_ID
519 )
520 VALUES
521 (
522 p_Functional_Area_Id
523 ,p_Application_Id
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_Pvt;
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_Pvt;
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_Pvt;
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_PVT.Create_Func_Area_Apps_Dep ';
557 ELSE
558 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Create_Func_Area_Apps_Dep ';
559 END IF;
560 WHEN OTHERS THEN
561 ROLLBACK TO CreateFuncAreaAppDepSP_Pvt;
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_PVT.Create_Func_Area_Apps_Dep ';
565 ELSE
566 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.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_Pvt;
586 FND_MSG_PUB.Initialize;
587
588 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
589
590 -- currently we do not have the default flag to update,
591 -- this API is for future implementations.
592
593 -- Commit if required
594 IF (p_Commit = FND_API.G_TRUE) THEN
595 COMMIT;
596 END IF;
597
598 EXCEPTION
599 WHEN FND_API.G_EXC_ERROR THEN
600 ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
601 IF (x_msg_data IS NULL) THEN
602 FND_MSG_PUB.Count_And_Get
603 ( p_encoded => FND_API.G_FALSE
604 , p_count => x_msg_count
605 , p_data => x_msg_data
606 );
607 END IF;
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
613 ( p_encoded => FND_API.G_FALSE
610 ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
611 IF (x_msg_data IS NULL) THEN
612 FND_MSG_PUB.Count_And_Get
614 , p_count => x_msg_count
615 , p_data => x_msg_data
616 );
617 END IF;
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619 WHEN NO_DATA_FOUND THEN
620 ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
622 IF (x_msg_data IS NOT NULL) THEN
623 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
624 ELSE
625 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
626 END IF;
627 WHEN OTHERS THEN
628 ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
630 IF (x_msg_data IS NOT NULL) THEN
631 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
632 ELSE
633 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
634 END IF;
635
636 END Update_Func_Area_Apps_Dep;
637
638
639 -- Delete the Functional Area
640 PROCEDURE Delete_Functional_Area(
641 p_Api_Version IN NUMBER
642 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
643 ,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
644 ,x_Return_Status OUT NOCOPY VARCHAR2
645 ,x_Msg_Count OUT NOCOPY NUMBER
646 ,x_Msg_Data OUT NOCOPY VARCHAR2
647 ) IS
648 l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
649 l_Count NUMBER;
650
651 CURSOR c_FuncArea_Dep IS
652 SELECT B.FUNCTIONAL_AREA_ID,
653 B.APPLICATION_ID
654 FROM BIS_FUNC_AREA_APP_DEPENDENCY B
655 WHERE B.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
656 BEGIN
657 SAVEPOINT DeleteFuncAreaSP_Pvt;
658 FND_MSG_PUB.Initialize;
659 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
660 l_Func_Area_Rec := p_Func_Area_Rec;
661
662 FOR cFAD IN c_FuncArea_Dep LOOP
663 BIS_FUNCTIONAL_AREA_PVT.Remove_Func_Area_Apps_Dep (
664 p_Api_Version => 1.0
665 ,p_Commit => p_Commit
666 ,p_Functional_Area_Id => cFAD.FUNCTIONAL_AREA_ID
667 ,p_Application_Id => cFAD.APPLICATION_ID
668 ,x_Return_Status => x_Return_Status
669 ,x_Msg_Count => x_Msg_Count
670 ,x_Msg_Data => x_Msg_Data
671 );
672 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676 END LOOP;
677
678 DELETE BIS_FUNCTIONAL_AREAS
679 WHERE FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
680 AND SHORT_NAME = l_Func_Area_Rec.Short_Name;
681
682 DELETE BIS_FUNCTIONAL_AREAS_TL
683 WHERE FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
684 -- Commit if required
685 IF (p_Commit = FND_API.G_TRUE) THEN
686 COMMIT;
687 END IF;
688
689 EXCEPTION
690 WHEN FND_API.G_EXC_ERROR THEN
691 ROLLBACK TO DeleteFuncAreaSP_Pvt;
692 IF (x_msg_data IS NULL) THEN
693 FND_MSG_PUB.Count_And_Get
694 ( p_encoded => FND_API.G_FALSE
695 , p_count => x_msg_count
696 , p_data => x_msg_data
697 );
698 END IF;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
701 ROLLBACK TO DeleteFuncAreaSP_Pvt;
702 IF (x_msg_data IS NULL) THEN
703 FND_MSG_PUB.Count_And_Get
704 ( p_encoded => FND_API.G_FALSE
705 , p_count => x_msg_count
706 , p_data => x_msg_data
707 );
708 END IF;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710 WHEN NO_DATA_FOUND THEN
711 ROLLBACK TO DeleteFuncAreaSP_Pvt;
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
713 IF (x_msg_data IS NOT NULL) THEN
714 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
715 ELSE
716 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
717 END IF;
718 WHEN OTHERS THEN
719 ROLLBACK TO DeleteFuncAreaSP_Pvt;
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 IF (x_msg_data IS NOT NULL) THEN
722 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
723 ELSE
724 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
725 END IF;
726 END Delete_Functional_Area;
727
728 -- remove Functional Area/Application ID dependency
729 PROCEDURE Remove_Func_Area_Apps_Dep (
730 p_Api_Version IN NUMBER
731 ,p_Commit IN VARCHAR2 := FND_API.G_FALSE
732 ,p_Functional_Area_Id IN NUMBER
733 ,p_Application_Id IN NUMBER
734 ,x_Return_Status OUT NOCOPY VARCHAR2
735 ,x_Msg_Count OUT NOCOPY NUMBER
736 ,x_Msg_Data OUT NOCOPY VARCHAR2
737 ) IS
738 l_Count NUMBER;
739
740 BEGIN
741 SAVEPOINT RemoveFuncAreaDependencySP_Pvt;
742 FND_MSG_PUB.Initialize;
743 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
744
745 DELETE BIS_FUNC_AREA_APP_DEPENDENCY B
746 WHERE B.FUNCTIONAL_AREA_ID = p_Functional_Area_Id
747 AND B.APPLICATION_ID = p_Application_Id;
748
749 -- Commit if required
750 IF (p_Commit = FND_API.G_TRUE) THEN
751 COMMIT;
752 END IF;
753
754 EXCEPTION
755 WHEN FND_API.G_EXC_ERROR THEN
756 ROLLBACK TO RemoveFuncAreaDependencySP_Pvt;
757 IF (x_msg_data IS NULL) THEN
758 FND_MSG_PUB.Count_And_Get
759 ( p_encoded => FND_API.G_FALSE
760 , p_count => x_msg_count
761 , p_data => x_msg_data
762 );
763 END IF;
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
766 ROLLBACK TO RemoveFuncAreaDependencySP_Pvt;
767 IF (x_msg_data IS NULL) THEN
768 FND_MSG_PUB.Count_And_Get
769 ( p_encoded => FND_API.G_FALSE
770 , p_count => x_msg_count
771 , p_data => x_msg_data
772 );
773 END IF;
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 WHEN NO_DATA_FOUND THEN
776 ROLLBACK TO RemoveFuncAreaDependencySP_Pvt;
777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778 IF (x_msg_data IS NOT NULL) THEN
779 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Remove_Func_Area_Apps_Dep ';
780 ELSE
781 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Remove_Func_Area_Apps_Dep ';
782 END IF;
783 WHEN OTHERS THEN
784 ROLLBACK TO RemoveFuncAreaDependencySP_Pvt;
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786 IF (x_msg_data IS NOT NULL) THEN
787 x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Remove_Func_Area_Apps_Dep ';
788 ELSE
789 x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Remove_Func_Area_Apps_Dep ';
790 END IF;
791 END Remove_Func_Area_Apps_Dep;
792
793
794 -- procedure to add a language.
795 PROCEDURE Add_Language IS
796 BEGIN
797
798 DELETE FROM BIS_FUNCTIONAL_AREAS_TL T
799 WHERE NOT EXISTS
800 (
801 SELECT NULL
802 FROM BIS_FUNCTIONAL_AREAS B
803 WHERE B.FUNCTIONAL_AREA_ID = T.FUNCTIONAL_AREA_ID
804 );
805
806 UPDATE BIS_FUNCTIONAL_AREAS_TL T SET (
807 NAME,
808 DESCRIPTION
809 ) = (SELECT
810 B.NAME,
811 B.DESCRIPTION
812 FROM BIS_FUNCTIONAL_AREAS_TL B
813 WHERE B.FUNCTIONAL_AREA_ID = T.FUNCTIONAL_AREA_ID
814 AND B.LANGUAGE = T.SOURCE_LANG)
815 WHERE (
816 T.FUNCTIONAL_AREA_ID,
817 T.LANGUAGE
818 ) IN (SELECT
819 SUBT.FUNCTIONAL_AREA_ID,
820 SUBT.LANGUAGE
821 FROM BIS_FUNCTIONAL_AREAS_TL SUBB, BIS_FUNCTIONAL_AREAS_TL SUBT
822 WHERE SUBB.FUNCTIONAL_AREA_ID = SUBT.FUNCTIONAL_AREA_ID
823 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
824 AND (
825 SUBB.NAME <> SUBT.NAME
826 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
827 )
828 );
829
830 INSERT INTO BIS_FUNCTIONAL_AREAS_TL
831 (
832 FUNCTIONAL_AREA_ID
833 , NAME
834 , DESCRIPTION
835 , LANGUAGE
836 , SOURCE_LANG
837 , CREATED_BY
838 , CREATION_DATE
839 , LAST_UPDATED_BY
840 , LAST_UPDATE_DATE
841 , LAST_UPDATE_LOGIN
842 )
843 SELECT
844 B.FUNCTIONAL_AREA_ID
845 , B.NAME
846 , B.DESCRIPTION
847 , L.LANGUAGE_CODE
848 , B.SOURCE_LANG
849 , B.CREATED_BY
850 , B.CREATION_DATE
851 , B.LAST_UPDATED_BY
852 , B.LAST_UPDATE_DATE
853 , B.LAST_UPDATE_LOGIN
854 FROM BIS_FUNCTIONAL_AREAS_TL B, FND_LANGUAGES L
855 WHERE L.INSTALLED_FLAG IN ('I', 'B')
856 AND B.LANGUAGE = USERENV('LANG')
857 AND NOT EXISTS
858 (
859 SELECT NULL
860 FROM BIS_FUNCTIONAL_AREAS_TL T
861 WHERE T.FUNCTIONAL_AREA_ID = B.FUNCTIONAL_AREA_ID
862 AND T.LANGUAGE = L.LANGUAGE_CODE
863 );
864
865 END Add_Language;
866
867 END BIS_FUNCTIONAL_AREA_PVT;