eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIS_OBJECT_EXTENSIONS_PVT

Source


1 PACKAGE BODY BIS_OBJECT_EXTENSIONS_PVT AS
2 /* $Header: BISVEXTB.pls 120.2 2005/12/12 12:35:45 hengliu noship $ */
3 /*
4 REM +=======================================================================+
5 REM |    Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA     |
6 REM |                         All rights reserved.                          |
7 REM +=======================================================================+
8 REM | FILENAME                                                              |
9 REM |     BISVEXTB.pls                                                      |
10 REM |                                                                       |
11 REM | DESCRIPTION                                                           |
12 REM |     Module: Wrapper for populating the extension tables               |
13 REM |             - BIS_MEASURES_EXTENSION_TL                               |
14 REM |             - BIS_FORM_FUNCTION_EXTENSION_TL                          |
15 REM | NOTES                                                                 |
16 REM | 24-NOV-2004 Krishna  Created.                                         |
17 REM | 31-Jan-2005 rpenneru Modified for #4153331, BIS_MEASURES_EXTENSION_TL |
18 REM |             Name and Description should not be updated, if the values |
19 REM |             are BIS_COMMON_UTILS.G_DEF_CHAR                           |
20 REM |       19-MAY-2005  visuri   GSCC Issues bug 4363854                   |
21 REM |       24-Aug-2005  hengliu  bug#4572274: issue in loading seed data   |
22 REM +=======================================================================+
23 */
24 
25 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_OBJECT_EXTENSIONS_PVT';
26 
27 /*********************************************************************************************
28         FUNCTION Name :- Create_Form_Func_Extension
29         PARAMETERS    :-
30             p_Form_Func_Extn_Rec :- The Details of form function sent from UI for create
31         DESCRIPTION   :- Creates the new form function
32         AUTHOR        :- KRISHNA
33 *********************************************************************************************/
34 PROCEDURE Create_Form_Func_Extension(
35     p_Api_Version         IN          NUMBER
36  ,  p_Commit              IN          VARCHAR2
37  ,  p_Form_Func_Extn_Rec  IN          BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type
38  ,  x_Return_Status       OUT NOCOPY  VARCHAR2
39  ,  x_Msg_Count           OUT NOCOPY  NUMBER
40  ,  x_Msg_Data            OUT NOCOPY  VARCHAR2
41 )IS
42     l_Commit        VARCHAR2(30);
43     l_Form_Func_Extn_Rec BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
44 BEGIN
45     SAVEPOINT  CreateFormFuncSP_Pvt;
46     FND_MSG_PUB.Initialize;
47     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
48     IF( p_Commit IS NULL) THEN
49         l_Commit    := FND_API.G_FALSE;
50     ELSE
51         l_Commit := p_Commit;
52     END IF;
53     --dbms_output.put_line('i am calling validate from create ');
54     l_Form_Func_Extn_Rec := p_Form_Func_Extn_Rec;
55 
56     IF ( l_Form_Func_Extn_Rec.Name = BIS_COMMON_UTILS.G_DEF_CHAR ) THEN
57       l_Form_Func_Extn_Rec.Name := NULL;
58     END IF;
59 
60     IF ( l_Form_Func_Extn_Rec.Description =  BIS_COMMON_UTILS.G_DEF_CHAR) THEN
61       l_Form_Func_Extn_Rec.Description := NULL;
62     END IF;
63 
64     -- Insert into base tables
65     INSERT INTO BIS_FORM_FUNCTION_EXTENSION
66     (
67           OBJECT_TYPE
68         , OBJECT_NAME
69         , APPLICATION_ID
70         , FUNCTIONAL_AREA_ID
71         , CREATED_BY
72         , CREATION_DATE
73         , LAST_UPDATED_BY
74         , LAST_UPDATE_DATE
75         , LAST_UPDATE_LOGIN
76     )
77     VALUES
78     (
79         l_Form_Func_Extn_Rec.Object_Type
80       , TRIM(l_Form_Func_Extn_Rec.Object_Name)
81       , l_Form_Func_Extn_Rec.Application_Id
82       , l_Form_Func_Extn_Rec.Func_Area_Id
83       , NVL(l_Form_Func_Extn_Rec.Created_By,FND_GLOBAL.USER_ID)
84       , NVL(l_Form_Func_Extn_Rec.Last_Update_Date,SYSDATE)
85       , NVL(l_Form_Func_Extn_Rec.Last_Updated_By,FND_GLOBAL.USER_ID)
86       , NVL(l_Form_Func_Extn_Rec.Last_Update_Date,SYSDATE)
87       , NVL(l_Form_Func_Extn_Rec.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
88     );
89 
90     INSERT INTO BIS_FORM_FUNCTION_EXTENSION_TL
91     (
92           OBJECT_NAME
93         , NAME
94         , DESCRIPTION
95         , LANGUAGE
96         , SOURCE_LANG
97         , CREATED_BY
98         , CREATION_DATE
99         , LAST_UPDATED_BY
100         , LAST_UPDATE_DATE
101         , LAST_UPDATE_LOGIN
102     )
103     SELECT
104         l_Form_Func_Extn_Rec.Object_Name
105       , l_Form_Func_Extn_Rec.Name
106       , l_Form_Func_Extn_Rec.Description
107       , L.LANGUAGE_CODE
108       , USERENV('LANG')
109       , NVL(l_Form_Func_Extn_Rec.Created_By,FND_GLOBAL.USER_ID)
110       , NVL(l_Form_Func_Extn_Rec.Last_Update_Date,SYSDATE)
111       , NVL(l_Form_Func_Extn_Rec.Last_Updated_By,FND_GLOBAL.USER_ID)
112       , NVL(l_Form_Func_Extn_Rec.Last_Update_Date,SYSDATE)
113       , NVL(l_Form_Func_Extn_Rec.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
114     FROM FND_LANGUAGES L
115     WHERE L.INSTALLED_FLAG IN ('I', 'B')
116     AND NOT EXISTS
117         (
118           SELECT NULL
119           FROM   BIS_FORM_FUNCTION_EXTENSION_TL T
120           WHERE  T.OBJECT_NAME = l_Form_Func_Extn_Rec.Object_Name
121           AND    T.LANGUAGE    = L.LANGUAGE_CODE
122         );
123 
124   IF (l_Commit = FND_API.G_TRUE) THEN
125       COMMIT;
126     END IF;
127 
128 EXCEPTION
129     WHEN FND_API.G_EXC_ERROR THEN
130         ROLLBACK TO CreateFormFuncSP_Pvt;
131         IF (x_msg_data IS NULL) THEN
132             FND_MSG_PUB.Count_And_Get
133             (      p_encoded   =>  FND_API.G_FALSE
134                ,   p_count     =>  x_msg_count
135                ,   p_data      =>  x_msg_data
136             );
137         END IF;
138         x_return_status :=  FND_API.G_RET_STS_ERROR;
139     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
140         ROLLBACK TO CreateFormFuncSP_Pvt;
141         IF (x_msg_data IS NULL) THEN
142             FND_MSG_PUB.Count_And_Get
143             (      p_encoded   =>  FND_API.G_FALSE
144                ,   p_count     =>  x_msg_count
145                ,   p_data      =>  x_msg_data
146             );
147         END IF;
148         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149     WHEN NO_DATA_FOUND THEN
150         ROLLBACK TO CreateFormFuncSP_Pvt;
151         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152         IF (x_msg_data IS NOT NULL) THEN
153             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Create_Form_Func_Extension ';
154         ELSE
155             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Create_Form_Func_Extension ';
156         END IF;
157     WHEN OTHERS THEN
158         ROLLBACK TO CreateFormFuncSP_Pvt;
159         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160         IF (x_msg_data IS NOT NULL) THEN
161             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Create_Form_Func_Extension ';
162         ELSE
163             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Create_Form_Func_Extension ';
164         END IF;
165 END Create_Form_Func_Extension;
166 
167 
168 
169 /*********************************************************************************************
170         FUNCTION Name :- Update_Form_Func_Extension
171         PARAMETERS    :-
172             p_Form_Func_Extn_Rec :- The Details of form function sent from UI for update
173         DESCRIPTION   :- This basically updates the properties for form functions
174                          This can be called from UI also
175         AUTHOR        :- KRISHNA
176 *********************************************************************************************/
177 PROCEDURE Update_Form_Func_Extension(
178   p_Api_Version         IN          NUMBER
179  ,p_Commit              IN          VARCHAR2
180  ,p_Form_Func_Extn_Rec  IN          BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type
181  ,x_Return_Status       OUT NOCOPY  VARCHAR2
182  ,x_Msg_Count           OUT NOCOPY  NUMBER
183  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
184 )IS
185     l_Commit                VARCHAR2(30);
186     l_Form_Func_Extn_Rec    BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
187 BEGIN
188     SAVEPOINT  UpdateFormFuncSP;
189     FND_MSG_PUB.Initialize;
190     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
191     IF( p_Commit IS NULL) THEN
192         l_Commit    := FND_API.G_FALSE;
193     ELSE
194         l_Commit := p_Commit;
195     END IF;
196 
197     BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Form_Func_Extension(
198         p_Form_Func_Extn_Rec  =>  p_Form_Func_Extn_Rec
199      ,  x_Form_Func_Extn_Rec  =>  l_Form_Func_Extn_Rec
200      ,  x_Return_Status       =>  x_Return_Status
201      ,  x_Msg_Count           =>  x_Msg_Count
202      ,  x_Msg_Data            =>  x_Msg_Data
203     );
204     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
205         RAISE  FND_API.G_EXC_ERROR;
206     END IF;
207 
208     IF(p_Form_Func_Extn_Rec.Application_Id IS NOT NULL) THEN
209         l_Form_Func_Extn_Rec.Application_Id := p_Form_Func_Extn_Rec.Application_Id;
210     END IF;
211     IF(p_Form_Func_Extn_Rec.Func_Area_Id IS NOT NULL) THEN
212         l_Form_Func_Extn_Rec.Func_Area_Id:= p_Form_Func_Extn_Rec.Func_Area_Id;
213     END IF;
214 
215     IF(p_Form_Func_Extn_Rec.Last_Update_Date IS NULL) THEN
216         l_Form_Func_Extn_Rec.Last_Update_Date := SYSDATE;
217     ELSE
218         l_Form_Func_Extn_Rec.Last_Update_Date := p_Form_Func_Extn_Rec.Last_Update_Date;
219     END IF;
220 
221     IF (p_Form_Func_Extn_Rec.Last_Updated_By IS NULL) THEN
222       l_Form_Func_Extn_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
223     ELSE
224       l_Form_Func_Extn_Rec.Last_Updated_By := p_Form_Func_Extn_Rec.Last_Updated_By;
225     END IF;
226 
227     IF (p_Form_Func_Extn_Rec.Last_Update_Login IS NULL) THEN
228       l_Form_Func_Extn_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
229     ELSE
230       l_Form_Func_Extn_Rec.Last_Update_Login := p_Form_Func_Extn_Rec.Last_Update_Login;
231     END IF;
232 
233     UPDATE BIS_FORM_FUNCTION_EXTENSION
234     SET
235         APPLICATION_ID     = l_Form_Func_Extn_Rec.Application_id
236       , FUNCTIONAL_AREA_ID = l_Form_Func_Extn_Rec.Func_Area_Id
237       , LAST_UPDATED_BY    = l_Form_Func_Extn_Rec.Last_Updated_By
238       , LAST_UPDATE_DATE   = l_Form_Func_Extn_Rec.Last_Update_Date
239       , LAST_UPDATE_LOGIN  = l_Form_Func_Extn_Rec.Last_Update_Login
240     WHERE OBJECT_NAME      = l_Form_Func_Extn_Rec.Object_Name;
241 
242 
243     BIS_OBJECT_EXTENSIONS_PVT.Translate_Form_Func_Extension(
244         p_Api_Version         =>  p_Api_Version
245      ,  p_Commit              =>  p_Commit
246      ,  p_Form_Func_Extn_Rec  =>  p_Form_Func_Extn_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_ERROR;
253     END IF;
254     IF (l_Commit = FND_API.G_TRUE) THEN
255       COMMIT;
256     END IF;
257 
258 EXCEPTION
259     WHEN FND_API.G_EXC_ERROR THEN
260         ROLLBACK TO UpdateFormFuncSP;
261         IF (x_msg_data IS NULL) THEN
262             FND_MSG_PUB.Count_And_Get
263             (      p_encoded   =>  FND_API.G_FALSE
264                ,   p_count     =>  x_msg_count
265                ,   p_data      =>  x_msg_data
266             );
267         END IF;
268         x_return_status :=  FND_API.G_RET_STS_ERROR;
269     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
270         ROLLBACK TO UpdateFormFuncSP;
271         IF (x_msg_data IS NULL) THEN
272             FND_MSG_PUB.Count_And_Get
273             (      p_encoded   =>  FND_API.G_FALSE
274                ,   p_count     =>  x_msg_count
275                ,   p_data      =>  x_msg_data
276             );
277         END IF;
278         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279     WHEN NO_DATA_FOUND THEN
280         ROLLBACK TO UpdateFormFuncSP;
281         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282         IF (x_msg_data IS NOT NULL) THEN
283             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Update_Form_Func_Extension ';
284         ELSE
285             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Update_Form_Func_Extension ';
286         END IF;
287     WHEN OTHERS THEN
288         ROLLBACK TO UpdateFormFuncSP;
289         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290         IF (x_msg_data IS NOT NULL) THEN
291             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Update_Form_Func_Extension ';
292         ELSE
293             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Update_Form_Func_Extension ';
294         END IF;
295 END Update_Form_Func_Extension;
296 
297 /*********************************************************************************************
298         FUNCTION Name :- Translate_Form_Func_Extension
299         PARAMETERS    :-
300             p_Form_Func_Extn_Rec :- The Details of form function sent from UI
301         DESCRIPTION   :- This basically updates the properties for form functions
302                          This can be called from UI directly also
303         AUTHOR        :- KRISHNA
304 *********************************************************************************************/
305 
306 PROCEDURE Translate_Form_Func_Extension(
307   p_Api_Version         IN          NUMBER
308  ,p_Commit              IN          VARCHAR2
309  ,p_Form_Func_Extn_Rec  IN          BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type
310  ,x_Return_Status       OUT NOCOPY  VARCHAR2
311  ,x_Msg_Count           OUT NOCOPY  NUMBER
312  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
313 )IS
314     l_Commit                VARCHAR2(30);
315     l_Form_Func_Extn_Rec    BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
316     l_valid_func_ext_rec    BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type;
317 BEGIN
318     SAVEPOINT  TransalteFormFuncSP;
319     FND_MSG_PUB.Initialize;
320     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
321     IF( p_Commit IS NULL) THEN
322         l_Commit    := FND_API.G_FALSE;
323     ELSE
324         l_Commit := p_Commit;
325     END IF;
326     --dbms_output.put_line('calling validate from trans');
327 
328     --dbms_output.put_line('calling validate from trans after');
329     BIS_OBJECT_EXTENSIONS_PUB.Retrieve_Form_Func_Extension(
330         p_Form_Func_Extn_Rec  =>  p_Form_Func_Extn_Rec
331      ,  x_Form_Func_Extn_Rec  =>  l_Form_Func_Extn_Rec
332      ,  x_Return_Status       =>  x_Return_Status
333      ,  x_Msg_Count           =>  x_Msg_Count
334      ,  x_Msg_Data            =>  x_Msg_Data
335     );
336     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
337         RAISE  FND_API.G_EXC_ERROR;
338     END IF;
339 
340     IF ( p_Form_Func_Extn_Rec.Name IS NULL ) THEN
341       l_Form_Func_Extn_Rec.Name := NULL;
342     ELSIF ((p_Form_Func_Extn_Rec.Name <> BIS_COMMON_UTILS.G_DEF_CHAR)
343 		  AND ((l_Form_Func_Extn_Rec.Name IS NULL) OR (l_Form_Func_Extn_Rec.Name <> p_Form_Func_Extn_Rec.Name))) THEN
344       l_Form_Func_Extn_Rec.Name := p_Form_Func_Extn_Rec.Name;
345     END IF;
346 
347     IF ( p_Form_Func_Extn_Rec.Description IS NULL ) THEN
348       l_Form_Func_Extn_Rec.Description := NULL;
349     ELSIF ( (p_Form_Func_Extn_Rec.Description <>  BIS_COMMON_UTILS.G_DEF_CHAR)
350 	   AND ((l_Form_Func_Extn_Rec.Description IS NULL) OR (l_Form_Func_Extn_Rec.Description <> p_Form_Func_Extn_Rec.Description))) THEN
351       l_Form_Func_Extn_Rec.Description := p_Form_Func_Extn_Rec.Description;
352     END IF;
353 
354     IF(p_Form_Func_Extn_Rec.Last_Update_Date IS NULL) THEN
355         l_Form_Func_Extn_Rec.Last_Update_Date := SYSDATE;
356     ELSE
357         l_Form_Func_Extn_Rec.Last_Update_Date := p_Form_Func_Extn_Rec.Last_Update_Date;
358     END IF;
359 
360     IF (p_Form_Func_Extn_Rec.Last_Updated_By IS NULL) THEN
361       l_Form_Func_Extn_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
362     ELSE
363       l_Form_Func_Extn_Rec.Last_Updated_By := p_Form_Func_Extn_Rec.Last_Updated_By;
364     END IF;
365 
366     IF (p_Form_Func_Extn_Rec.Last_Update_Login IS NULL) THEN
367       l_Form_Func_Extn_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
368     ELSE
369       l_Form_Func_Extn_Rec.Last_Update_Login := p_Form_Func_Extn_Rec.Last_Update_Login;
370     END IF;
371 
372     UPDATE BIS_FORM_FUNCTION_EXTENSION_TL
373     SET
374         NAME               = l_Form_Func_Extn_Rec.Name
375       , DESCRIPTION        = l_Form_Func_Extn_Rec.Description
376       , LAST_UPDATED_BY    = l_Form_Func_Extn_Rec.Last_Updated_By
377       , LAST_UPDATE_DATE   = l_Form_Func_Extn_Rec.Last_Update_Date
378       , LAST_UPDATE_LOGIN  = l_Form_Func_Extn_Rec.Last_Update_Login
379       , SOURCE_LANG        = USERENV('LANG')
380     WHERE OBJECT_NAME      = l_Form_Func_Extn_Rec.Object_Name
381     AND USERENV('LANG')      IN (LANGUAGE, SOURCE_LANG);
382 ----dbms_output.put_line('after updating    BIS_FORM_FUNCTION_EXTENSION_TL in tras');
383 
384     IF (l_Commit = FND_API.G_TRUE) THEN
385       COMMIT;
386     END IF;
387 EXCEPTION
388     WHEN FND_API.G_EXC_ERROR THEN
389         ROLLBACK TO TransalteFormFuncSP;
390         IF (x_msg_data IS NULL) THEN
391             FND_MSG_PUB.Count_And_Get
392             (      p_encoded   =>  FND_API.G_FALSE
393                ,   p_count     =>  x_msg_count
394                ,   p_data      =>  x_msg_data
395             );
396         END IF;
397         x_return_status :=  FND_API.G_RET_STS_ERROR;
398     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
399         ROLLBACK TO TransalteFormFuncSP;
400         IF (x_msg_data IS NULL) THEN
401             FND_MSG_PUB.Count_And_Get
402             (      p_encoded   =>  FND_API.G_FALSE
403                ,   p_count     =>  x_msg_count
404                ,   p_data      =>  x_msg_data
405             );
406         END IF;
407         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408     WHEN NO_DATA_FOUND THEN
409         ROLLBACK TO TransalteFormFuncSP;
410         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411         IF (x_msg_data IS NOT NULL) THEN
412             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Translate_Form_Func_Extension ';
413         ELSE
414             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Translate_Form_Func_Extension ';
415         END IF;
416     WHEN OTHERS THEN
417         ROLLBACK TO TransalteFormFuncSP;
418         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419         IF (x_msg_data IS NOT NULL) THEN
420             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Translate_Form_Func_Extension ';
421         ELSE
422             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Translate_Form_Func_Extension ';
423         END IF;
424 END Translate_Form_Func_Extension;
425 
426 
427 /*********************************************************************************************
428         FUNCTION Name :- Retrieve_Form_Func_Extension
429         PARAMETERS    :-
430             p_Form_Func_Extn_Rec :- This record details sent from UI
431             x_Form_Func_Extn_Rec :- This record sends the details for form given form function to caller
432         DESCRIPTION   :- This retrieves the details of give form function name
433         AUTHOR        :- KRISHNA
434 *********************************************************************************************/
435 PROCEDURE Retrieve_Form_Func_Extension(
436     p_Form_Func_Extn_Rec      IN          BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type
437  ,  x_Form_Func_Extn_Rec      OUT NOCOPY  BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type
438  ,  x_Return_Status           OUT NOCOPY  VARCHAR2
439  ,  x_Msg_Count               OUT NOCOPY  NUMBER
440  ,  x_Msg_Data                OUT NOCOPY  VARCHAR2
441 )IS
442 
443 BEGIN
444     FND_MSG_PUB.Initialize;
445     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
446 
447 ----dbms_output.put_line('before retrieve');
448 
449     SELECT  F.OBJECT_TYPE
450            ,F.OBJECT_NAME
451            ,F.NAME
452            ,F.DESCRIPTION
453            ,F.APPLICATION_ID
454            ,F.FUNCTIONAL_AREA_ID
455            ,F.CREATED_BY
456            ,F.CREATION_DATE
457            ,F.LAST_UPDATED_BY
458            ,F.LAST_UPDATE_DATE
459            ,F.LAST_UPDATE_LOGIN
460     INTO    x_Form_Func_Extn_Rec.Object_Type
461            ,x_Form_Func_Extn_Rec.Object_Name
462            ,x_Form_Func_Extn_Rec.Name
463            ,x_Form_Func_Extn_Rec.Description
464            ,x_Form_Func_Extn_Rec.Application_Id
465            ,x_Form_Func_Extn_Rec.Func_Area_Id
466            ,x_Form_Func_Extn_Rec.Created_By
467            ,x_Form_Func_Extn_Rec.Creation_Date
468            ,x_Form_Func_Extn_Rec.Last_Updated_By
469            ,x_Form_Func_Extn_Rec.Last_Update_Date
470            ,x_Form_Func_Extn_Rec.Last_Update_Login
471     FROM   BIS_FORM_FUNCTION_EXTENSION_VL F
472     WHERE  F.OBJECT_NAME = trim(p_Form_Func_Extn_Rec.Object_Name);
473 ----dbms_output.put_line('after retrieve');
474 EXCEPTION
475     WHEN FND_API.G_EXC_ERROR THEN
476         IF (x_msg_data IS NULL) THEN
477             FND_MSG_PUB.Count_And_Get
478             (      p_encoded   =>  FND_API.G_FALSE
479                ,   p_count     =>  x_msg_count
480                ,   p_data      =>  x_msg_data
481             );
482         END IF;
483         x_return_status :=  FND_API.G_RET_STS_ERROR;
484     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485         IF (x_msg_data IS NULL) THEN
486             FND_MSG_PUB.Count_And_Get
487             (      p_encoded   =>  FND_API.G_FALSE
488                ,   p_count     =>  x_msg_count
489                ,   p_data      =>  x_msg_data
490             );
491         END IF;
492         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493     WHEN NO_DATA_FOUND THEN
494         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495         IF (x_msg_data IS NOT NULL) THEN
496             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Form_Func_Extension ';
497         ELSE
498             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Form_Func_Extension ';
499         END IF;
500     WHEN OTHERS THEN
501         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
502         IF (x_msg_data IS NOT NULL) THEN
503             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Form_Func_Extension ';
504         ELSE
505             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Form_Func_Extension ';
506         END IF;
507 END Retrieve_Form_Func_Extension;
508 
509 /*********************************************************************************************
510         FUNCTION Name :- Retrieve_Form_Func_Extension
511         PARAMETERS    :-
512             p_Form_Func_Extn_Rec :- This record details sent from UI
513             DESCRIPTION   :- This delete the record in BIS_FORM_FUNCTION_EXTENSION table
514         AUTHOR        :- KRISHNA
515 *********************************************************************************************/
516 PROCEDURE Delete_Form_Func_Extension(
517     p_Api_Version         IN          NUMBER
518  ,  p_Commit              IN          VARCHAR2
519  ,  p_Form_Func_Extn_Rec  IN          BIS_OBJECT_EXTENSIONS_PUB.Form_Function_Extension_Type
520  ,  x_Return_Status       OUT NOCOPY  VARCHAR2
521  ,  x_Msg_Count           OUT NOCOPY  NUMBER
522  ,  x_Msg_Data            OUT NOCOPY  VARCHAR2
523 )IS
524 BEGIN
525     SAVEPOINT  DeleteFormFuncSP_Pvt;
526     FND_MSG_PUB.Initialize;
527     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
528     -- Delete from base table
529     DELETE FROM  BIS_FORM_FUNCTION_EXTENSION
530     WHERE  OBJECT_NAME =   TRIM(p_Form_Func_Extn_Rec.Object_Name);
531 
532     DELETE FROM  BIS_FORM_FUNCTION_EXTENSION_TL
533     WHERE  OBJECT_NAME =   TRIM(p_Form_Func_Extn_Rec.Object_Name);
534 
535     IF (p_Commit = FND_API.G_TRUE) THEN
536         COMMIT;
537     END IF;
538 
539 EXCEPTION
540     WHEN FND_API.G_EXC_ERROR THEN
541         ROLLBACK TO DeleteFormFuncSP_Pvt;
542         IF (x_msg_data IS NULL) THEN
543             FND_MSG_PUB.Count_And_Get
544             (      p_encoded   =>  FND_API.G_FALSE
545                ,   p_count     =>  x_msg_count
546                ,   p_data      =>  x_msg_data
547             );
548         END IF;
549         x_return_status :=  FND_API.G_RET_STS_ERROR;
550     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
551         ROLLBACK TO DeleteFormFuncSP_Pvt;
552         IF (x_msg_data IS NULL) THEN
553             FND_MSG_PUB.Count_And_Get
554             (      p_encoded   =>  FND_API.G_FALSE
555                ,   p_count     =>  x_msg_count
556                ,   p_data      =>  x_msg_data
557             );
558         END IF;
559         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
560     WHEN NO_DATA_FOUND THEN
561         ROLLBACK TO DeleteFormFuncSP_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_OBJECT_EXTENSIONS_PVT.Delete_Form_Func_Extension ';
565         ELSE
566             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Delete_Form_Func_Extension ';
567         END IF;
568     WHEN OTHERS THEN
569         ROLLBACK TO DeleteFormFuncSP_Pvt;
570         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571         IF (x_msg_data IS NOT NULL) THEN
572             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Delete_Form_Func_Extension ';
573         ELSE
574             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Delete_Form_Func_Extension ';
575         END IF;
576 END Delete_Form_Func_Extension;
577 
578 
579 
580 PROCEDURE Create_Measure_Extension(
581   p_Api_Version         IN          NUMBER
582  ,p_Commit              IN          VARCHAR2
583  ,p_Meas_Extn_Rec       IN          BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type
584  ,x_Return_Status       OUT NOCOPY  VARCHAR2
585  ,x_Msg_Count           OUT NOCOPY  NUMBER
586  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
587 ) IS
588     l_Count           NUMBER;
589     l_Meas_Extn_Rec   BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type;
590 BEGIN
591     SAVEPOINT CreateMeasExtnSP;
592     FND_MSG_PUB.Initialize;
593 
594     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
595     l_Meas_Extn_Rec := p_Meas_Extn_Rec;
596 
597     l_Count := 0;
598 
599     -- rpenneru bug#4153331
600     -- Create_Measures_Extension will be called from BISPMFLD.lct (Uploading Measures)
601     -- in the case Name and Description fields should be inserted as NULL values.
602     -- The value of the Name and Description from BISPMFLD.lct will be BIS_COMMON_UTILS.G_DEF_CHAR
603 
604     IF ( l_Meas_Extn_Rec.Name = BIS_COMMON_UTILS.G_DEF_CHAR ) THEN
605       l_Meas_Extn_Rec.Name := NULL;
606     END IF;
607 
608     IF ( l_Meas_Extn_Rec.Description =  BIS_COMMON_UTILS.G_DEF_CHAR) THEN
609       l_Meas_Extn_Rec.Description := NULL;
610     END IF;
611 
612 
613     INSERT INTO BIS_MEASURES_EXTENSION
614     (
615        MEASURE_SHORT_NAME
616       ,FUNCTIONAL_AREA_ID
617       ,CREATED_BY
618       ,CREATION_DATE
619       ,LAST_UPDATED_BY
620       ,LAST_UPDATE_DATE
621       ,LAST_UPDATE_LOGIN
622     )
623     VALUES
624     (
625        TRIM(l_Meas_Extn_Rec.Measure_Short_Name)
626       ,l_Meas_Extn_Rec.Functional_Area_Id
627       ,NVL(l_Meas_Extn_Rec.Created_By, FND_GLOBAL.USER_ID)
628       ,NVL(l_Meas_Extn_Rec.Last_Update_Date,SYSDATE)
629       ,NVL(l_Meas_Extn_Rec.Created_By, FND_GLOBAL.USER_ID)
630       ,NVL(l_Meas_Extn_Rec.Last_Update_Date,SYSDATE)
631       ,NVL(l_Meas_Extn_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
632     );
633 
634     INSERT INTO BIS_MEASURES_EXTENSION_TL
635     (
636        MEASURE_SHORT_NAME
637       ,NAME
638       ,DESCRIPTION
639       ,LANGUAGE
640       ,SOURCE_LANG
641       ,CREATED_BY
642       ,CREATION_DATE
643       ,LAST_UPDATED_BY
644       ,LAST_UPDATE_DATE
645       ,LAST_UPDATE_LOGIN
646     )
647     SELECT
648        l_Meas_Extn_Rec.Measure_Short_Name
649       ,l_Meas_Extn_Rec.Name
650       ,l_Meas_Extn_Rec.Description
651       ,L.LANGUAGE_CODE
652       ,USERENV('LANG')
653       ,NVL(l_Meas_Extn_Rec.Created_By, FND_GLOBAL.USER_ID)
654       ,NVL(l_Meas_Extn_Rec.Last_Update_Date,SYSDATE)
655       ,NVL(l_Meas_Extn_Rec.Created_By, FND_GLOBAL.USER_ID)
656       ,NVL(l_Meas_Extn_Rec.Last_Update_Date,SYSDATE)
657       ,NVL(l_Meas_Extn_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
658     FROM FND_LANGUAGES L
659     WHERE L.INSTALLED_FLAG IN ('I', 'B')
660     AND NOT EXISTS
661         (
662           SELECT NULL
663           FROM   BIS_MEASURES_EXTENSION_TL T
664           WHERE  T.MEASURE_SHORT_NAME = l_Meas_Extn_Rec.Measure_Short_Name
665           AND    T.LANGUAGE           = L.LANGUAGE_CODE
666         );
667   -- Commit if required
668   IF (p_Commit = FND_API.G_TRUE) THEN
669     COMMIT;
670   END IF;
671 
672 EXCEPTION
673     WHEN FND_API.G_EXC_ERROR THEN
674         ROLLBACK TO CreateMeasExtnSP;
675         IF (x_msg_data IS NULL) THEN
676             FND_MSG_PUB.Count_And_Get
677             (      p_encoded   =>  FND_API.G_FALSE
678                ,   p_count     =>  x_msg_count
679                ,   p_data      =>  x_msg_data
680             );
681         END IF;
682         x_return_status :=  FND_API.G_RET_STS_ERROR;
683     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
684         ROLLBACK TO CreateMeasExtnSP;
685         IF (x_msg_data IS NULL) THEN
686             FND_MSG_PUB.Count_And_Get
687             (      p_encoded   =>  FND_API.G_FALSE
688                ,   p_count     =>  x_msg_count
689                ,   p_data      =>  x_msg_data
690             );
691         END IF;
692         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693     WHEN NO_DATA_FOUND THEN
694         ROLLBACK TO CreateMeasExtnSP;
695         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
696         IF (x_msg_data IS NOT NULL) THEN
697             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Create_Measure_Extension ';
698         ELSE
699             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Create_Measure_Extension ';
700         END IF;
701     WHEN OTHERS THEN
702         ROLLBACK TO CreateMeasExtnSP;
703         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704         IF (x_msg_data IS NOT NULL) THEN
705             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Create_Measure_Extension ';
706         ELSE
707             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Create_Measure_Extension ';
708         END IF;
709 
710 END Create_Measure_Extension;
711 
712 
713 PROCEDURE Retrieve_Measure_Extension(
714   p_Meas_Extn_Rec       IN          BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type
715  ,x_Meas_Extn_Rec       OUT NOCOPY  BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type
716  ,x_Return_Status       OUT NOCOPY  VARCHAR2
717  ,x_Msg_Count           OUT NOCOPY  NUMBER
718  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
719 ) IS
720 
721     l_Flag  BOOLEAN;
722 
723     CURSOR cMeasurExtension IS
724      SELECT  B.MEASURE_SHORT_NAME
725             ,B.NAME
726             ,B.DESCRIPTION
727             ,B.FUNCTIONAL_AREA_ID
728             ,B.CREATED_BY
729             ,B.CREATION_DATE
730             ,B.LAST_UPDATED_BY
731             ,B.LAST_UPDATE_DATE
732             ,B.LAST_UPDATE_LOGIN
733      FROM   BIS_MEASURES_EXTENSION_VL B
734      WHERE  B.MEASURE_SHORT_NAME = TRIM(p_Meas_Extn_Rec.Measure_Short_Name);
735 
736 BEGIN
737     FND_MSG_PUB.Initialize;
738     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
739 
740     l_Flag := FALSE;
741 
742     FOR cME IN cMeasurExtension LOOP
743         x_Meas_Extn_Rec.Measure_Short_Name := cME.MEASURE_SHORT_NAME;
744         x_Meas_Extn_Rec.Name               := cME.NAME;
745         x_Meas_Extn_Rec.Description        := cME.DESCRIPTION;
746         x_Meas_Extn_Rec.Functional_Area_Id := cME.FUNCTIONAL_AREA_ID;
747         x_Meas_Extn_Rec.Created_By         := cME.CREATED_BY;
748         x_Meas_Extn_Rec.Creation_Date      := cME.CREATION_DATE;
749         x_Meas_Extn_Rec.Last_Updated_By    := cME.LAST_UPDATED_BY;
750         x_Meas_Extn_Rec.Last_Update_Date   := cME.LAST_UPDATE_DATE;
751         x_Meas_Extn_Rec.Last_Update_Login  := cME.LAST_UPDATE_LOGIN;
752         l_Flag := TRUE;
753     END LOOP;
754 
755     IF(l_Flag = FALSE) THEN
756         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
757     END IF;
758 
759 EXCEPTION
760     WHEN FND_API.G_EXC_ERROR THEN
761         IF (x_msg_data IS NULL) THEN
762             FND_MSG_PUB.Count_And_Get
763             (      p_encoded   =>  FND_API.G_FALSE
764                ,   p_count     =>  x_msg_count
765                ,   p_data      =>  x_msg_data
766             );
767         END IF;
768         x_return_status :=  FND_API.G_RET_STS_ERROR;
769     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770         IF (x_msg_data IS NULL) THEN
771             FND_MSG_PUB.Count_And_Get
772             (      p_encoded   =>  FND_API.G_FALSE
773                ,   p_count     =>  x_msg_count
774                ,   p_data      =>  x_msg_data
775             );
776         END IF;
777         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778     WHEN NO_DATA_FOUND THEN
779         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780         IF (x_msg_data IS NOT NULL) THEN
781             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Measure_Extension ';
782         ELSE
783             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Measure_Extension ';
784         END IF;
785     WHEN OTHERS THEN
786         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787         IF (x_msg_data IS NOT NULL) THEN
788             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Measure_Extension ';
789         ELSE
790             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Measure_Extension ';
791         END IF;
792 END Retrieve_Measure_Extension;
793 
794 PROCEDURE Translate_Measure_Extension(
795   p_Api_Version         IN          NUMBER
796  ,p_Commit              IN          VARCHAR2
797  ,p_Meas_Extn_Rec       IN          BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type
798  ,x_Return_Status       OUT NOCOPY  VARCHAR2
799  ,x_Msg_Count           OUT NOCOPY  NUMBER
800  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
801 ) IS
802     l_Meas_Extn_Rec   BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type;
803 BEGIN
804 
805     SAVEPOINT TranslateMeasExtnSP;
806     FND_MSG_PUB.Initialize;
807 
808     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
809 
810   ------dbms_output.put_line ('l_Meas_Extn_Rec.Measure_Short_Name - ' || l_Meas_Extn_Rec.Measure_Short_Name);
811 
812 
813     BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Measure_Extension(
814       p_Meas_Extn_Rec  => p_Meas_Extn_Rec
815      ,x_Meas_Extn_Rec  => l_Meas_Extn_Rec
816      ,x_Return_Status  => x_Return_Status
817      ,x_Msg_Count      => x_Msg_Count
818      ,x_Msg_Data       => x_Msg_Data
819     );
820     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
821         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
822     END IF;
823 
824     -- rpenneru bug#4153331
825     -- Translate_Measures_Extension will be called from BISPMFLD.lct (Uploading Measures)
826     -- in the case Name and Description fields should not be updated.
827     -- The value of the Name and Description from BISPMFLD.lct will be BIS_COMMON_UTILS.G_DEF_CHAR
828     IF ( p_Meas_Extn_Rec.Name IS NULL ) THEN
829       l_Meas_Extn_Rec.Name := NULL;
830     ELSIF ((p_Meas_Extn_Rec.Name <> BIS_COMMON_UTILS.G_DEF_CHAR)
831 		  AND ((l_Meas_Extn_Rec.Name IS NULL) OR (l_Meas_Extn_Rec.Name <> p_Meas_Extn_Rec.Name))) THEN
832       l_Meas_Extn_Rec.Name := p_Meas_Extn_Rec.Name;
833     END IF;
834 
835     IF ( p_Meas_Extn_Rec.Description IS NULL ) THEN
836       l_Meas_Extn_Rec.Description := NULL;
837     ELSIF ( (p_Meas_Extn_Rec.Description <>  BIS_COMMON_UTILS.G_DEF_CHAR)
838 	   AND ((l_Meas_Extn_Rec.Description IS NULL) OR (l_Meas_Extn_Rec.Description <> p_Meas_Extn_Rec.Description))) THEN
839       l_Meas_Extn_Rec.Description := p_Meas_Extn_Rec.Description;
840     END IF;
841 
842     IF(p_Meas_Extn_Rec.Last_Update_Date IS NULL ) THEN
843         l_Meas_Extn_Rec.Last_Update_Date := SYSDATE;
844     ELSE
845         l_Meas_Extn_Rec.Last_Update_Date := p_Meas_Extn_Rec.Last_Update_Date;
846     END IF;
847 
848     IF (p_Meas_Extn_Rec.Last_Updated_By IS NULL) THEN
849       l_Meas_Extn_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
850     ELSE
851       l_Meas_Extn_Rec.Last_Updated_By := p_Meas_Extn_Rec.Last_Updated_By;
852     END IF;
853 
854     IF (p_Meas_Extn_Rec.Last_Update_Login IS NULL) THEN
855       l_Meas_Extn_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
856     ELSE
857       l_Meas_Extn_Rec.Last_Update_Login := p_Meas_Extn_Rec.Last_Update_Login;
858     END IF;
859 
860     -- Update the trans table
861     UPDATE BIS_MEASURES_EXTENSION_TL B
862     SET
863         B.NAME               = l_Meas_Extn_Rec.Name
864       , B.DESCRIPTION        = l_Meas_Extn_Rec.Description
865       , B.LAST_UPDATED_BY    = l_Meas_Extn_Rec.Last_Updated_By
866       , B.LAST_UPDATE_DATE   = l_Meas_Extn_Rec.Last_Update_Date
867       , B.LAST_UPDATE_LOGIN  = l_Meas_Extn_Rec.Last_Update_Login
868       , B.SOURCE_LANG        = USERENV('LANG')
869     WHERE
870         B.MEASURE_SHORT_NAME = l_Meas_Extn_Rec.Measure_Short_Name
871     AND USERENV('LANG')    IN (B.LANGUAGE, B.SOURCE_LANG);
872 
873     -- Commit if required
874     IF (p_Commit = FND_API.G_TRUE) THEN
875       COMMIT;
876     END IF;
877 
878 EXCEPTION
879     WHEN FND_API.G_EXC_ERROR THEN
880         ROLLBACK TO TranslateMeasExtnSP;
881         IF (x_msg_data IS NULL) THEN
882             FND_MSG_PUB.Count_And_Get
883             (      p_encoded   =>  FND_API.G_FALSE
884                ,   p_count     =>  x_msg_count
885                ,   p_data      =>  x_msg_data
886             );
887         END IF;
888         x_return_status :=  FND_API.G_RET_STS_ERROR;
889     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
890         ROLLBACK TO TranslateMeasExtnSP;
891         IF (x_msg_data IS NULL) THEN
892             FND_MSG_PUB.Count_And_Get
893             (      p_encoded   =>  FND_API.G_FALSE
894                ,   p_count     =>  x_msg_count
895                ,   p_data      =>  x_msg_data
896             );
897         END IF;
898         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899     WHEN NO_DATA_FOUND THEN
900         ROLLBACK TO TranslateMeasExtnSP;
901         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902         IF (x_msg_data IS NOT NULL) THEN
903             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Translate_Measure_Extension ';
904         ELSE
905             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Translate_Measure_Extension ';
906         END IF;
907     WHEN OTHERS THEN
908         ROLLBACK TO TranslateMeasExtnSP;
909         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910         IF (x_msg_data IS NOT NULL) THEN
911             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Translate_Measure_Extension ';
912         ELSE
913             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Translate_Measure_Extension ';
914         END IF;
915 
916 END Translate_Measure_Extension;
917 
918 -- Update the Measure Extensions
919 PROCEDURE Update_Measure_Extension(
920   p_Api_Version         IN          NUMBER
921  ,p_Commit              IN          VARCHAR2
922  ,p_Meas_Extn_Rec       IN          BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type
923  ,x_Return_Status       OUT NOCOPY  VARCHAR2
924  ,x_Msg_Count           OUT NOCOPY  NUMBER
925  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
926 ) IS
927     l_Meas_Extn_Rec   BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type;
928 
929 BEGIN
930     SAVEPOINT UpdateMeasExtnSP;
931     FND_MSG_PUB.Initialize;
932 
933     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
934 
935     -- Since there is no base table, a direct transalation
936     -- should suffice.
937 
938     BIS_OBJECT_EXTENSIONS_PVT.Retrieve_Measure_Extension(
939       p_Meas_Extn_Rec  => p_Meas_Extn_Rec
940      ,x_Meas_Extn_Rec  => l_Meas_Extn_Rec
941      ,x_Return_Status  => x_Return_Status
942      ,x_Msg_Count      => x_Msg_Count
943      ,x_Msg_Data       => x_Msg_Data
944     );
945     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
946         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
947     END IF;
948 
949     IF((p_Meas_Extn_Rec.Functional_Area_Id  IS NOT NULL)
950            AND (p_Meas_Extn_Rec.Functional_Area_Id <> l_Meas_Extn_Rec.Functional_Area_Id)) THEN
951         l_Meas_Extn_Rec.Functional_Area_Id:= p_Meas_Extn_Rec.Functional_Area_Id;
952     END IF;
953 
954 
955     IF(p_Meas_Extn_Rec.Last_Update_Date IS NULL ) THEN
956         l_Meas_Extn_Rec.Last_Update_Date := SYSDATE;
957     ELSE
958         l_Meas_Extn_Rec.Last_Update_Date := p_Meas_Extn_Rec.Last_Update_Date;
959     END IF;
960 
961     IF (p_Meas_Extn_Rec.Last_Updated_By IS NULL) THEN
962       l_Meas_Extn_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
963     ELSE
964       l_Meas_Extn_Rec.Last_Updated_By := p_Meas_Extn_Rec.Last_Updated_By;
965     END IF;
966 
967     IF (p_Meas_Extn_Rec.Last_Update_Login IS NULL) THEN
968       l_Meas_Extn_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
969     ELSE
970       l_Meas_Extn_Rec.Last_Update_Login := p_Meas_Extn_Rec.Last_Update_Login;
971     END IF;
972 
973     UPDATE BIS_MEASURES_EXTENSION
974     SET
975         FUNCTIONAL_AREA_ID = l_Meas_Extn_Rec.Functional_Area_Id
976       , LAST_UPDATED_BY    = l_Meas_Extn_Rec.Last_Updated_By
977       , LAST_UPDATE_DATE   = l_Meas_Extn_Rec.Last_Update_Date
978       , LAST_UPDATE_LOGIN  = l_Meas_Extn_Rec.Last_Update_Login
979     WHERE
980         MEASURE_SHORT_NAME = l_Meas_Extn_Rec.Measure_Short_Name;
981 
982     BIS_OBJECT_EXTENSIONS_PVT.Translate_Measure_Extension(
983       p_Api_Version     => p_Api_Version
984      ,p_Commit          => p_Commit
985      ,p_Meas_Extn_Rec   => p_Meas_Extn_Rec
986      ,x_Return_Status   => x_Return_Status
987      ,x_Msg_Count       => x_Msg_Count
988      ,x_Msg_Data        => x_Msg_Data
989     );
990     IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
991         RAISE  FND_API.G_EXC_UNEXPECTED_ERROR;
992     END IF;
993     -- Commit if required
994     IF (p_Commit = FND_API.G_TRUE) THEN
995       COMMIT;
996     END IF;
997 
998 EXCEPTION
999     WHEN FND_API.G_EXC_ERROR THEN
1000         ROLLBACK TO UpdateMeasExtnSP;
1001         IF (x_msg_data IS NULL) THEN
1002             FND_MSG_PUB.Count_And_Get
1003             (      p_encoded   =>  FND_API.G_FALSE
1004                ,   p_count     =>  x_msg_count
1005                ,   p_data      =>  x_msg_data
1006             );
1007         END IF;
1008         x_return_status :=  FND_API.G_RET_STS_ERROR;
1009     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1010         ROLLBACK TO UpdateMeasExtnSP;
1011         IF (x_msg_data IS NULL) THEN
1012             FND_MSG_PUB.Count_And_Get
1013             (      p_encoded   =>  FND_API.G_FALSE
1014                ,   p_count     =>  x_msg_count
1015                ,   p_data      =>  x_msg_data
1016             );
1017         END IF;
1018         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019     WHEN NO_DATA_FOUND THEN
1020         ROLLBACK TO UpdateMeasExtnSP;
1021         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1022         IF (x_msg_data IS NOT NULL) THEN
1023             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Update_Measure_Extension ';
1024         ELSE
1025             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Update_Measure_Extension ';
1026         END IF;
1027     WHEN OTHERS THEN
1028         ROLLBACK TO UpdateMeasExtnSP;
1029         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1030         IF (x_msg_data IS NOT NULL) THEN
1031             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Update_Measure_Extension ';
1032         ELSE
1033             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Update_Measure_Extension ';
1034         END IF;
1035 END Update_Measure_Extension;
1036 /*********************************************************************************************
1037         FUNCTION Name :- Retrieve_Form_Func_Extension
1038         PARAMETERS    :-
1039             p_Meas_Extn_Rec :- This record details sent from UI
1040             DESCRIPTION   :- This delete the record in BIS_MEASURES_EXTENSION table
1041         AUTHOR        :- KRISHNA
1042 *********************************************************************************************/
1043 PROCEDURE Delete_Measure_Extension(
1044   p_Api_Version         IN          NUMBER
1045  ,p_Commit              IN          VARCHAR2
1046  ,p_Meas_Extn_Rec       IN          BIS_OBJECT_EXTENSIONS_PUB.Measure_Extension_Type
1047  ,x_Return_Status       OUT NOCOPY  VARCHAR2
1048  ,x_Msg_Count           OUT NOCOPY  NUMBER
1049  ,x_Msg_Data            OUT NOCOPY  VARCHAR2
1050 ) IS
1051 
1052 BEGIN
1053     SAVEPOINT DeleteMeasExtnSP;
1054     FND_MSG_PUB.Initialize;
1055     x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1056 
1057     DELETE FROM BIS_MEASURES_EXTENSION
1058     WHERE MEASURE_SHORT_NAME = TRIM(p_Meas_Extn_Rec.Measure_Short_Name);
1059 
1060     DELETE FROM BIS_MEASURES_EXTENSION_TL
1061     WHERE MEASURE_SHORT_NAME = TRIM(p_Meas_Extn_Rec.Measure_Short_Name);
1062 
1063 
1064   -- Commit if required
1065     IF (p_Commit = FND_API.G_TRUE) THEN
1066         COMMIT;
1067     END IF;
1068 
1069 EXCEPTION
1070     WHEN FND_API.G_EXC_ERROR THEN
1071         ROLLBACK TO DeleteMeasExtnSP;
1072         IF (x_msg_data IS NULL) THEN
1073             FND_MSG_PUB.Count_And_Get
1074             (      p_encoded   =>  FND_API.G_FALSE
1075                ,   p_count     =>  x_msg_count
1076                ,   p_data      =>  x_msg_data
1077             );
1078         END IF;
1079         x_return_status :=  FND_API.G_RET_STS_ERROR;
1080     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1081         ROLLBACK TO DeleteMeasExtnSP;
1082         IF (x_msg_data IS NULL) THEN
1083             FND_MSG_PUB.Count_And_Get
1084             (      p_encoded   =>  FND_API.G_FALSE
1085                ,   p_count     =>  x_msg_count
1086                ,   p_data      =>  x_msg_data
1087             );
1088         END IF;
1089         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090     WHEN NO_DATA_FOUND THEN
1091         ROLLBACK TO DeleteMeasExtnSP;
1092         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093         IF (x_msg_data IS NOT NULL) THEN
1094             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Delete_Measure_Extension ';
1095         ELSE
1096             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Delete_Measure_Extension ';
1097         END IF;
1098     WHEN OTHERS THEN
1099         ROLLBACK TO DeleteMeasExtnSP;
1100         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101         IF (x_msg_data IS NOT NULL) THEN
1102             x_msg_data      :=  x_msg_data||' -> BIS_OBJECT_EXTENSIONS_PVT.Delete_Measure_Extension ';
1103         ELSE
1104             x_msg_data      :=  SQLERRM||' at BIS_OBJECT_EXTENSIONS_PVT.Delete_Measure_Extension ';
1105         END IF;
1106 
1107 END Delete_Measure_Extension;
1108 
1109 -- Get the Functional Area ID from Functional Short_Name
1110 FUNCTION Get_FA_Id_By_Short_Name (
1111   p_Functional_Area_Short_Name IN VARCHAR2
1112 )RETURN NUMBER IS
1113   l_FA_Id   NUMBER;
1114 BEGIN
1115   SELECT FUNCTIONAL_AREA_ID
1116   INTO   l_FA_Id
1117   FROM   BIS_FUNCTIONAL_AREAS
1118   WHERE  UPPER(SHORT_NAME) = UPPER(Trim(p_Functional_Area_Short_Name));
1119 
1120   RETURN l_FA_Id;
1121 
1122 EXCEPTION
1123   WHEN OTHERS THEN
1124      RETURN BIS_OBJECT_EXTENSIONS_PUB.C_INVALID;
1125 END Get_FA_Id_By_Short_Name;
1126 
1127 -- procedure to add a language.
1128 PROCEDURE Add_Language IS
1129 BEGIN
1130 
1131 
1132     -- Add language for the BIS_MEASURES_EXTENSION_TL
1133    BEGIN
1134         DELETE FROM BIS_MEASURES_EXTENSION_TL T
1135         WHERE NOT EXISTS
1136         (
1137           SELECT NULL
1138           FROM   BIS_MEASURES_EXTENSION B
1139           WHERE  B.MEASURE_SHORT_NAME = T.MEASURE_SHORT_NAME
1140         );
1141 
1142         UPDATE BIS_MEASURES_EXTENSION_TL T SET (
1143             NAME,
1144             DESCRIPTION
1145         ) = (SELECT
1146                 B.NAME,
1147                 B.DESCRIPTION
1148              FROM  BIS_MEASURES_EXTENSION_TL B
1149              WHERE B.MEASURE_SHORT_NAME = T.MEASURE_SHORT_NAME
1150              AND   B.LANGUAGE           = T.SOURCE_LANG)
1151              WHERE (
1152                 T.MEASURE_SHORT_NAME,
1153                 T.LANGUAGE
1154              ) IN (SELECT
1155                     SUBT.MEASURE_SHORT_NAME,
1156                     SUBT.LANGUAGE
1157                     FROM  BIS_MEASURES_EXTENSION_TL SUBB, BIS_MEASURES_EXTENSION_TL SUBT
1158                     WHERE SUBB.MEASURE_SHORT_NAME = SUBT.MEASURE_SHORT_NAME
1159                     AND   SUBB.LANGUAGE           = SUBT.SOURCE_LANG
1160                     AND (
1161                          SUBB.NAME              <> SUBT.NAME
1162                          OR SUBB.DESCRIPTION    <> SUBT.DESCRIPTION
1163                         )
1164                     );
1165 
1166         INSERT INTO BIS_MEASURES_EXTENSION_TL
1167         (
1168            MEASURE_SHORT_NAME
1169           ,NAME
1170           ,DESCRIPTION
1171           ,LANGUAGE
1172           ,SOURCE_LANG
1173           ,CREATED_BY
1174           ,CREATION_DATE
1175           ,LAST_UPDATED_BY
1176           ,LAST_UPDATE_DATE
1177           ,LAST_UPDATE_LOGIN
1178         )
1179         SELECT
1180             B.MEASURE_SHORT_NAME
1181           , B.NAME
1182           , B.DESCRIPTION
1183           , L.LANGUAGE_CODE
1184           , B.SOURCE_LANG
1185           , B.CREATED_BY
1186           , B.CREATION_DATE
1187           , B.LAST_UPDATED_BY
1188           , B.LAST_UPDATE_DATE
1189           , B.LAST_UPDATE_LOGIN
1190        FROM  BIS_MEASURES_EXTENSION_TL B, FND_LANGUAGES L
1191        WHERE L.INSTALLED_FLAG IN ('I', 'B')
1192        AND   B.LANGUAGE = USERENV('LANG')
1193        AND   NOT EXISTS
1194             (
1195               SELECT NULL
1196               FROM   BIS_MEASURES_EXTENSION_TL T
1197               WHERE  UPPER(T.MEASURE_SHORT_NAME) = UPPER(B.MEASURE_SHORT_NAME)
1198               AND    T.LANGUAGE           = L.LANGUAGE_CODE
1199             );
1200    EXCEPTION
1201       WHEN OTHERS THEN
1202          NULL;
1203    END;
1204 
1205     -- Add language for the BIS_MEASURES_EXTENSION_TL
1206 
1207    BEGIN
1208         DELETE FROM BIS_FORM_FUNCTION_EXTENSION_TL T
1209         WHERE NOT EXISTS
1210         (
1211           SELECT NULL
1212           FROM   BIS_FORM_FUNCTION_EXTENSION B
1213           WHERE  B.OBJECT_NAME = T.OBJECT_NAME
1214         );
1215 
1216         UPDATE BIS_FORM_FUNCTION_EXTENSION_TL T SET (
1217             NAME,
1218             DESCRIPTION
1219         ) = (SELECT
1220                 B.NAME,
1221                 B.DESCRIPTION
1222              FROM  BIS_FORM_FUNCTION_EXTENSION_TL B
1223              WHERE B.OBJECT_NAME  = T.OBJECT_NAME
1224              AND   B.LANGUAGE     = T.SOURCE_LANG)
1225              WHERE (
1226                 T.OBJECT_NAME,
1227                 T.LANGUAGE
1228              ) IN (SELECT
1229                     SUBT.OBJECT_NAME,
1230                     SUBT.LANGUAGE
1231                     FROM  BIS_FORM_FUNCTION_EXTENSION_TL SUBB, BIS_FORM_FUNCTION_EXTENSION_TL SUBT
1232                     WHERE SUBB.OBJECT_NAME = SUBT.OBJECT_NAME
1233                     AND   SUBB.LANGUAGE    = SUBT.SOURCE_LANG
1234                     AND (
1235                          SUBB.NAME              <> SUBT.NAME
1236                          OR SUBB.DESCRIPTION    <> SUBT.DESCRIPTION
1237                         )
1238                     );
1239 
1240         INSERT INTO BIS_FORM_FUNCTION_EXTENSION_TL
1241         (
1242            OBJECT_NAME
1243           ,NAME
1244           ,DESCRIPTION
1245           ,LANGUAGE
1246           ,SOURCE_LANG
1247           ,CREATED_BY
1248           ,CREATION_DATE
1249           ,LAST_UPDATED_BY
1250           ,LAST_UPDATE_DATE
1251           ,LAST_UPDATE_LOGIN
1252         )
1253         SELECT
1254             B.OBJECT_NAME
1255           , B.NAME
1256           , B.DESCRIPTION
1257           , L.LANGUAGE_CODE
1258           , B.SOURCE_LANG
1259           , B.CREATED_BY
1260           , B.CREATION_DATE
1261           , B.LAST_UPDATED_BY
1262           , B.LAST_UPDATE_DATE
1263           , B.LAST_UPDATE_LOGIN
1264        FROM  BIS_FORM_FUNCTION_EXTENSION_TL B, FND_LANGUAGES L
1265        WHERE L.INSTALLED_FLAG IN ('I', 'B')
1266        AND   B.LANGUAGE = USERENV('LANG')
1267        AND   NOT EXISTS
1268             (
1269               SELECT NULL
1270               FROM   BIS_FORM_FUNCTION_EXTENSION_TL T
1271               WHERE  T.OBJECT_NAME = B.OBJECT_NAME
1272               AND    T.LANGUAGE    = L.LANGUAGE_CODE
1273             );
1274    EXCEPTION
1275       WHEN OTHERS THEN
1276          NULL;
1277    END;
1278 END Add_Language;
1279 
1280 END BIS_OBJECT_EXTENSIONS_PVT;