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.ASF_USER_PREFERENCE_PUB

Source


1 PACKAGE BODY ASF_USER_PREFERENCE_PUB AS
2 /* $Header: asfuprfb.pls 115.4 2003/02/14 09:17:00 vjayamoh ship $ */
3 -- Start of Comments
4 -- Package name     : ASF_USER_PREFERENCE_PUB
5 -- Purpose          :
6 -- History          :
7 -- NOTE             :
8 -- End of Comments
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASF_USER_PREFERENCE_PUB';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asfuprfb.pls';
11 
12 G_OWNER_TABLE_NAME  CONSTANT  VARCHAR2(30) :=  'HZ_PARTIES';
13 
14 -- Start of Comments
15 --
16 --    API name    : Create_Preference
17 --    Type        : Public.
18 --
19 -- End of Comments
20 PROCEDURE CREATE_PREFERENCE(
21     p_user_preference_rec           IN    USER_PREFERENCE_REC_TYPE := G_MISS_USER_PREFERENCE_REC,
22     x_return_status                OUT NOCOPY    VARCHAR2,
23     x_msg_count	                   OUT NOCOPY    NUMBER,
24     x_msg_data	                   OUT NOCOPY    VARCHAR2)
25  IS
26 l_debug BOOLEAN;
27 l_api_name                CONSTANT VARCHAR2(30) := 'CREATE_PREFERENCE';
28 l_user_preference_rec     USER_PREFERENCE_REC_TYPE   := p_user_preference_rec;
29 l_warning_msg		      VARCHAR2(2000)     := '';
30 BEGIN
31 
32       -- Standard Start of API savepoint
33       SAVEPOINT CREATE_PREFERENCE_PUB;
34 
35 
36       -- Debug Message
37       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
38       --		'Public API: ' || l_api_name || ' start');
39 
40       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
41       --		'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
42 
43       l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
44       IF l_debug THEN
45          AS_UTILITY_PVT.Debug_Message(NULL,'Public API: ' || l_api_name || ' start');
46          AS_UTILITY_PVT.Debug_Message(NULL,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
47       END IF;
48 
49       -- Initialize API return status to SUCCESS
50       x_return_status := FND_API.G_RET_STS_SUCCESS;
51 
52    BEGIN
53        INSERT INTO ASF_USER_PREFERENCE(
54           preference_id,
55           user_id,
56           created_by,
57           creation_date,
58           last_updated_by,
59           last_update_date,
60           last_update_login,
61           owner_table_name,
62           owner_table_id,
63           category,
64           preference_code,
65           preference_value,
66           attribute_category,
67           attribute1,
68           attribute2,
69           attribute3,
70           attribute4,
71           attribute5,
72           attribute6,
73           attribute7,
74           attribute8,
75           attribute9,
76           attribute10,
77           attribute11,
78           attribute12,
79           attribute13,
80           attribute14,
81           attribute15
82           ) VALUES (
83            ASF_USER_PREFERENCE_S.NEXTVAL,
84            decode( l_user_preference_rec.user_id, FND_API.G_MISS_NUM, NULL, l_user_preference_rec.user_id),
85 
86            decode( l_user_preference_rec.created_by, FND_API.G_MISS_NUM, NULL, l_user_preference_rec.created_by),
87            decode( l_user_preference_rec.creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), l_user_preference_rec.creation_date),
88            decode( l_user_preference_rec.last_updated_by, FND_API.G_MISS_NUM, NULL, l_user_preference_rec.last_updated_by),
89            decode( l_user_preference_rec.last_update_date, FND_API.G_MISS_DATE, TO_DATE(NULL), l_user_preference_rec.last_update_date),
90            decode( l_user_preference_rec.last_update_login, FND_API.G_MISS_NUM, NULL, l_user_preference_rec.last_update_login),
91 
92            decode( l_user_preference_rec.owner_table_name, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.owner_table_name),
93            decode( l_user_preference_rec.owner_table_id, FND_API.G_MISS_NUM, NULL, l_user_preference_rec.owner_table_id),
94            decode( l_user_preference_rec.category, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.category),
95            decode( l_user_preference_rec.preference_code, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.preference_code),
96            decode( l_user_preference_rec.preference_value, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.preference_value),
97            decode( l_user_preference_rec.attribute_category, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute_category),
98            decode( l_user_preference_rec.attribute1, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute1),
99            decode( l_user_preference_rec.attribute2, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute2),
100            decode( l_user_preference_rec.attribute3, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute3),
101            decode( l_user_preference_rec.attribute4, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute4),
102            decode( l_user_preference_rec.attribute5, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute5),
103            decode( l_user_preference_rec.attribute6, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute6),
104            decode( l_user_preference_rec.attribute7, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute7),
105            decode( l_user_preference_rec.attribute8, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute8),
106            decode( l_user_preference_rec.attribute9, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute9),
107            decode( l_user_preference_rec.attribute10, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute10),
108            decode( l_user_preference_rec.attribute11, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute11),
109            decode( l_user_preference_rec.attribute12, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute12),
110            decode( l_user_preference_rec.attribute13, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute13),
111            decode( l_user_preference_rec.attribute14, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute14),
112            decode( l_user_preference_rec.attribute15, FND_API.G_MISS_CHAR, NULL, l_user_preference_rec.attribute15)
113            );
114 
115        IF (SQL%NOTFOUND) THEN
116             RAISE NO_DATA_FOUND;
117 --       ELSE
118  --           COMMIT;
119        END IF;
120        EXCEPTION
121        WHEN OTHERS THEN
122           x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123      END;
124 
125       IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
126 	     l_warning_msg := X_Msg_Data;
127       END IF;
128 
129       -- Check return status from the above procedure call
130       IF x_return_status = FND_API.G_RET_STS_ERROR then
131           raise FND_API.G_EXC_ERROR;
132       elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
133           raise FND_API.G_EXC_UNEXPECTED_ERROR;
134       END IF;
135 
136 
137       -- Debug Message
138       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
139       --		'Public API: ' || l_api_name || ' end');
140 
141       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
142       --		'End time:   ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
143 
144       IF l_debug THEN
145          AS_UTILITY_PVT.Debug_Message(NULL,'Public API: ' || l_api_name || ' end');
146          AS_UTILITY_PVT.Debug_Message(NULL,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
147       END IF;
148 
149       -- Standard call to get message count and if count is 1, get message info.
150 
151       FND_MSG_PUB.Count_And_Get
152       (  p_count          =>   x_msg_count,
153          p_data           =>   x_msg_data
154       );
155 
156       IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
157 	     X_Msg_Data := l_warning_msg;
158       END IF;
159 
160 EXCEPTION
161      WHEN FND_API.G_EXC_ERROR THEN
162               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
163                    P_API_NAME => L_API_NAME
164                   ,P_PKG_NAME => G_PKG_NAME
165                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
166                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
167                   ,X_MSG_COUNT => X_MSG_COUNT
168                   ,X_MSG_DATA => X_MSG_DATA
169                   ,X_RETURN_STATUS => X_RETURN_STATUS);
170 
171       WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
172               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
173                    P_API_NAME => L_API_NAME
174                   ,P_PKG_NAME => G_PKG_NAME
175                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
176                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
177                   ,X_MSG_COUNT => X_MSG_COUNT
178                   ,X_MSG_DATA => X_MSG_DATA
179                   ,X_RETURN_STATUS => X_RETURN_STATUS);
180 
181       WHEN OTHERS THEN
182               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
183                    P_API_NAME => L_API_NAME
184                   ,P_PKG_NAME => G_PKG_NAME
185                   ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
186                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
187                   ,X_MSG_COUNT => X_MSG_COUNT
188                   ,X_MSG_DATA => X_MSG_DATA
189                   ,X_RETURN_STATUS => X_RETURN_STATUS);
190 
191 END CREATE_PREFERENCE;
192 PROCEDURE UPDATE_PREFERENCE(
193     p_user_preference_rec           IN    USER_PREFERENCE_REC_TYPE := G_MISS_USER_PREFERENCE_REC,
194     x_return_status                OUT NOCOPY    VARCHAR2,
195     x_msg_count	                   OUT NOCOPY    NUMBER,
196     x_msg_data	                   OUT NOCOPY    VARCHAR2)
197 IS
198 l_debug BOOLEAN;
199 l_api_name                CONSTANT VARCHAR2(30) := 'UPDATE_PREFERENCE';
200 l_user_preference_rec     USER_PREFERENCE_REC_TYPE   := p_user_preference_rec;
201 l_warning_msg		      VARCHAR2(2000)     := '';
202 l_preference_id           NUMBER    := 0;
203 BEGIN
204 
205       -- Standard Start of API savepoint
206       SAVEPOINT UPDATE_PREFERENCE_PUB;
207 
208 
209       -- Debug Message
210       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
211       --		'Public API: ' || l_api_name || ' start');
212 
213       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
214       --              'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
215 
216       l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
217       IF l_debug THEN
218          AS_UTILITY_PVT.Debug_Message(NULL,'Public API: ' || l_api_name || ' start');
219          AS_UTILITY_PVT.Debug_Message(NULL,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
220       END IF;
221 
222       -- Initialize API return status to SUCCESS
223       x_return_status := FND_API.G_RET_STS_SUCCESS;
224 
225 
226      -- first to check if the passing preference_id is null
227      -- if it is not null, will use it as the condition to update related record
228      -- if it is null, will use other four conditions to locate
229      -- the preference_id, if preference_id found, then use it
230      -- to update the record, otherwise to create a new record
231 
232      --dbms_output.put_line('preference_id'|| ' ' || l_user_preference_rec.preference_id);
233      IF NVL(l_user_preference_rec.preference_id,0) = 0 THEN
234         --dbms_output.put_line('l_user_preference_rec.preference_id is null');
235           BEGIN
236              SELECT preference_id
237              INTO l_preference_id
238              FROM  ASF_USER_PREFERENCE
239              WHERE USER_ID = l_user_preference_rec.user_id
240                AND OWNER_TABLE_NAME = G_OWNER_TABLE_NAME
241                AND PREFERENCE_CODE = l_user_preference_rec.preference_code
242                AND OWNER_TABLE_ID= l_user_preference_rec.owner_table_id;
243           EXCEPTION
244           WHEN NO_DATA_FOUND THEN
245                BEGIN
246                    ASF_USER_PREFERENCE_PUB.CREATE_PREFERENCE(
247       	               P_user_preference_rec  	  => l_user_preference_rec ,
248       	               X_Return_Status              => x_return_status,
249       	               X_Msg_Count                  => x_msg_count,
250       	               X_Msg_Data                   => x_msg_data);
251 
252                    -- Debug Message
253                    IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
254                       AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
255                       'Create_Preference fail');
256                    END IF;
257                    -- Check return status from the above procedure call
258                    IF x_return_status = FND_API.G_RET_STS_ERROR THEN
259                       RAISE FND_API.G_EXC_ERROR;
260                    ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
261                       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262                    END IF;
263                END;
264           WHEN OTHERS THEN
265                x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266           END;
267           IF l_preference_id <> 0 THEN
268              BEGIN
269                 UPDATE ASF_USER_PREFERENCE
270                 SET
271                   last_updated_by = decode( l_user_preference_rec.last_updated_by, FND_API.G_MISS_NUM, last_updated_by, l_user_preference_rec.last_updated_by),
272                   last_update_date = decode( l_user_preference_rec.last_update_date, FND_API.G_MISS_DATE, SYSDATE, l_user_preference_rec.last_update_date),
273                   last_update_login = decode( l_user_preference_rec.last_update_login, FND_API.G_MISS_NUM, last_update_login, l_user_preference_rec.last_update_login),
274                   category = decode( l_user_preference_rec.category, FND_API.G_MISS_CHAR, category, l_user_preference_rec.category),
275                   preference_code = decode( l_user_preference_rec.preference_code, FND_API.G_MISS_CHAR, preference_code, l_user_preference_rec.preference_code),
276                   preference_value = decode( l_user_preference_rec.preference_value, FND_API.G_MISS_CHAR, preference_value, l_user_preference_rec.preference_value),
277                   attribute_category = decode( l_user_preference_rec.attribute_category, FND_API.G_MISS_CHAR, attribute_category, l_user_preference_rec.attribute_category),
278                   attribute1 = decode( l_user_preference_rec.attribute1, FND_API.G_MISS_CHAR, attribute1, l_user_preference_rec.attribute1),
279                   attribute2 = decode( l_user_preference_rec.attribute2, FND_API.G_MISS_CHAR, attribute2, l_user_preference_rec.attribute2),
280                   attribute3 = decode( l_user_preference_rec.attribute3, FND_API.G_MISS_CHAR, attribute3, l_user_preference_rec.attribute3),
281                   attribute4 = decode( l_user_preference_rec.attribute4, FND_API.G_MISS_CHAR, attribute4, l_user_preference_rec.attribute4),
282                   attribute5 = decode( l_user_preference_rec.attribute5, FND_API.G_MISS_CHAR, attribute5, l_user_preference_rec.attribute5),
283                   attribute6 = decode( l_user_preference_rec.attribute6, FND_API.G_MISS_CHAR, attribute6, l_user_preference_rec.attribute6),
284                   attribute7 = decode( l_user_preference_rec.attribute7, FND_API.G_MISS_CHAR, attribute7, l_user_preference_rec.attribute7),
285                   attribute8 = decode( l_user_preference_rec.attribute8, FND_API.G_MISS_CHAR, attribute8, l_user_preference_rec.attribute8),
286                   attribute9 = decode( l_user_preference_rec.attribute9, FND_API.G_MISS_CHAR, attribute9, l_user_preference_rec.attribute9),
287                   attribute10 = decode( l_user_preference_rec.attribute10, FND_API.G_MISS_CHAR, attribute10, l_user_preference_rec.attribute10),
288                   attribute11 = decode( l_user_preference_rec.attribute11, FND_API.G_MISS_CHAR, attribute11, l_user_preference_rec.attribute11),
289                   attribute12 = decode( l_user_preference_rec.attribute12, FND_API.G_MISS_CHAR, attribute12, l_user_preference_rec.attribute12),
290                   attribute13 = decode( l_user_preference_rec.attribute13, FND_API.G_MISS_CHAR, attribute13, l_user_preference_rec.attribute13),
291                   attribute14 = decode( l_user_preference_rec.attribute14, FND_API.G_MISS_CHAR, attribute14, l_user_preference_rec.attribute14),
292                   attribute15 = decode( l_user_preference_rec.attribute15, FND_API.G_MISS_CHAR, attribute15, l_user_preference_rec.attribute15)
293                 WHERE PREFERENCE_ID = l_preference_id;
294 
295                IF (SQL%NOTFOUND) THEN
296                    RAISE NO_DATA_FOUND;
297 --               ELSE
298 --                  COMMIT;
299                END IF;
300             EXCEPTION
301                WHEN OTHERS THEN
302                     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303             END;
304          END IF;
305      ELSIF NVL(l_user_preference_rec.preference_id,0) > 0  THEN
306         --dbms_output.put_line('l_user_preference_rec.preference_id is not null');
307         BEGIN
308         UPDATE ASF_USER_PREFERENCE
312               last_update_login = decode( l_user_preference_rec.last_update_login, FND_API.G_MISS_NUM, last_update_login, l_user_preference_rec.last_update_login),
309            SET
310               last_updated_by = decode( l_user_preference_rec.last_updated_by, FND_API.G_MISS_NUM, last_updated_by, l_user_preference_rec.last_updated_by),
311               last_update_date = decode( l_user_preference_rec.last_update_date, FND_API.G_MISS_DATE, SYSDATE, l_user_preference_rec.last_update_date),
313               category = decode( l_user_preference_rec.category, FND_API.G_MISS_CHAR, category, l_user_preference_rec.category),
314               preference_code = decode( l_user_preference_rec.preference_code, FND_API.G_MISS_CHAR, preference_code, l_user_preference_rec.preference_code),
315               preference_value = decode( l_user_preference_rec.preference_value, FND_API.G_MISS_CHAR, preference_value, l_user_preference_rec.preference_value),
316               attribute_category = decode( l_user_preference_rec.attribute_category, FND_API.G_MISS_CHAR, attribute_category, l_user_preference_rec.attribute_category),
317               attribute1 = decode( l_user_preference_rec.attribute1, FND_API.G_MISS_CHAR, attribute1, l_user_preference_rec.attribute1),
318               attribute2 = decode( l_user_preference_rec.attribute2, FND_API.G_MISS_CHAR, attribute2, l_user_preference_rec.attribute2),
319               attribute3 = decode( l_user_preference_rec.attribute3, FND_API.G_MISS_CHAR, attribute3, l_user_preference_rec.attribute3),
320               attribute4 = decode( l_user_preference_rec.attribute4, FND_API.G_MISS_CHAR, attribute4, l_user_preference_rec.attribute4),
321               attribute5 = decode( l_user_preference_rec.attribute5, FND_API.G_MISS_CHAR, attribute5, l_user_preference_rec.attribute5),
322               attribute6 = decode( l_user_preference_rec.attribute6, FND_API.G_MISS_CHAR, attribute6, l_user_preference_rec.attribute6),
323               attribute7 = decode( l_user_preference_rec.attribute7, FND_API.G_MISS_CHAR, attribute7, l_user_preference_rec.attribute7),
324               attribute8 = decode( l_user_preference_rec.attribute8, FND_API.G_MISS_CHAR, attribute8, l_user_preference_rec.attribute8),
325               attribute9 = decode( l_user_preference_rec.attribute9, FND_API.G_MISS_CHAR, attribute9, l_user_preference_rec.attribute9),
326               attribute10 = decode( l_user_preference_rec.attribute10, FND_API.G_MISS_CHAR, attribute10, l_user_preference_rec.attribute10),
327               attribute11 = decode( l_user_preference_rec.attribute11, FND_API.G_MISS_CHAR, attribute11, l_user_preference_rec.attribute11),
328               attribute12 = decode( l_user_preference_rec.attribute12, FND_API.G_MISS_CHAR, attribute12, l_user_preference_rec.attribute12),
329               attribute13 = decode( l_user_preference_rec.attribute13, FND_API.G_MISS_CHAR, attribute13, l_user_preference_rec.attribute13),
330               attribute14 = decode( l_user_preference_rec.attribute14, FND_API.G_MISS_CHAR, attribute14, l_user_preference_rec.attribute14),
331               attribute15 = decode( l_user_preference_rec.attribute15, FND_API.G_MISS_CHAR, attribute15, l_user_preference_rec.attribute15)
332            WHERE PREFERENCE_ID = l_user_preference_rec.preference_id;
333 
334            IF (SQL%NOTFOUND) THEN
335                RAISE NO_DATA_FOUND;
336 --           ELSE
337 --              COMMIT;
338            END IF;
339            EXCEPTION
340            WHEN OTHERS THEN
341                x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342                --APP_EXCEPTION.RAISE_EXCEPTION;
343          END;
344       END IF;
345 
346 
347 
348       -- Debug Message
349       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
350       --				'Public API: ' || l_api_name || ' end');
351 
352       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
353       --				'End time:   ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
354 
355       IF l_debug THEN
356          AS_UTILITY_PVT.Debug_Message(NULL,'Public API: ' || l_api_name || ' end');
357          AS_UTILITY_PVT.Debug_Message(NULL,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
358       END IF;
359 
360       -- Standard call to get message count and if count is 1, get message info.
361 
362       FND_MSG_PUB.Count_And_Get
363       (  p_count          =>   x_msg_count,
364          p_data           =>   x_msg_data
365       );
366 
367       IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
368 	     X_Msg_Data := l_warning_msg;
369       END IF;
370 
371 EXCEPTION
372      WHEN FND_API.G_EXC_ERROR THEN
373               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
374                    P_API_NAME => L_API_NAME
375                   ,P_PKG_NAME => G_PKG_NAME
376                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
377                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
378                   ,X_MSG_COUNT => X_MSG_COUNT
379                   ,X_MSG_DATA => X_MSG_DATA
380                   ,X_RETURN_STATUS => X_RETURN_STATUS);
381 
382     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
384                    P_API_NAME => L_API_NAME
385                   ,P_PKG_NAME => G_PKG_NAME
386                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
387                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
388                   ,X_MSG_COUNT => X_MSG_COUNT
389                   ,X_MSG_DATA => X_MSG_DATA
390                   ,X_RETURN_STATUS => X_RETURN_STATUS);
391 
392      WHEN OTHERS THEN
393               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
394                    P_API_NAME => L_API_NAME
395                   ,P_PKG_NAME => G_PKG_NAME
399                   ,X_MSG_DATA => X_MSG_DATA
396                   ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
397                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
398                   ,X_MSG_COUNT => X_MSG_COUNT
400                   ,X_RETURN_STATUS => X_RETURN_STATUS);
401 
402 
403 END UPDATE_PREFERENCE;
404 PROCEDURE DELETE_PREFERENCE(
405     p_user_preference_rec           IN    USER_PREFERENCE_REC_TYPE := G_MISS_USER_PREFERENCE_REC,
406     x_return_status                OUT NOCOPY    VARCHAR2,
407     x_msg_count	                   OUT NOCOPY    NUMBER,
408     x_msg_data	                   OUT NOCOPY    VARCHAR2)
409 IS
410 l_debug BOOLEAN;
411 l_api_name                CONSTANT VARCHAR2(30) := 'DELETE_PREFERENCE';
412 l_user_preference_rec     USER_PREFERENCE_REC_TYPE   := p_user_preference_rec;
413 l_warning_msg		      VARCHAR2(2000)     := '';
414 l_preference_id            NUMBER    := 0;
415 BEGIN
416       -- Standard Start of API savepoint
417       SAVEPOINT DELETE_PREFERENCE_PUB;
418 
419       -- Debug Message
420       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
421       --				'Public API: ' || l_api_name || ' start');
422 
423       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
424       --				'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
425 
426       l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
427       IF l_debug THEN
428          AS_UTILITY_PVT.Debug_Message(NULL,'Public API: ' || l_api_name || ' start');
429          AS_UTILITY_PVT.Debug_Message(NULL,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
430       END IF;
431 
432       -- Initialize API return status to SUCCESS
433       x_return_status := FND_API.G_RET_STS_SUCCESS;
434 
435      -- first to check if the passing preference_id is null
436      -- if it is not null, will use it as the condition to delete related record
437      -- if it is null, will use other four conditions to locate
438      -- the preference_id, if preference_id found, then use it to delete the record
439 
440      --dbms_output.put_line('Testing Delete Procedure');
441 	--dbms_output.put_line('preference_id'|| ' ' || l_user_preference_rec.preference_id);
442 
443      IF NVL(l_user_preference_rec.preference_id,0) = 0 THEN
444 	  --dbms_output.put_line('l_user_preference_rec.preference_id is null');
445           BEGIN
446              SELECT preference_id
447              INTO l_preference_id
448              FROM  ASF_USER_PREFERENCE
449              WHERE USER_ID = l_user_preference_rec.user_id
450                AND OWNER_TABLE_NAME = G_OWNER_TABLE_NAME
451                AND PREFERENCE_CODE = l_user_preference_rec.preference_code
452                AND OWNER_TABLE_ID= l_user_preference_rec.owner_table_id;
453           EXCEPTION
454           WHEN NO_DATA_FOUND THEN
458                --APP_EXCEPTION.RAISE_EXCEPTION;
455                l_preference_id := 0;
456           WHEN OTHERS THEN
457                x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459           END;
460           IF NVL(l_preference_id,0) <> 0 THEN
461                 DELETE FROM ASF_USER_PREFERENCE
462                 WHERE PREFERENCE_ID = l_preference_id;
463                IF (SQL%NOTFOUND) THEN
464                    RAISE NO_DATA_FOUND;
465 --               ELSE
466 --                   COMMIT;
467                END IF;
468           END IF;
469      ELSIF NVL(l_user_preference_rec.preference_id,0) > 0  THEN
470 		 --dbms_output.put_line('l_user_preference_rec.preference_id is not null');
471                 DELETE FROM ASF_USER_PREFERENCE
472                 WHERE PREFERENCE_ID = l_user_preference_rec.preference_id;
473                IF (SQL%NOTFOUND) THEN
474                    RAISE NO_DATA_FOUND;
475 --               ELSE
476 --                   COMMIT;
477                END IF;
478      END IF;
479 
480       -- Check return status from the above procedure call
481       IF x_return_status = FND_API.G_RET_STS_ERROR then
482           raise FND_API.G_EXC_ERROR;
483       elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
484           raise FND_API.G_EXC_UNEXPECTED_ERROR;
485       END IF;
486 
487       -- Debug Message
488       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
489       --				'Public API: ' || l_api_name || ' end');
490 
491       --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
492       --				'End time:   ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
493 
494       IF l_debug THEN
495          AS_UTILITY_PVT.Debug_Message(NULL,'Public API: ' || l_api_name || ' end');
496          AS_UTILITY_PVT.Debug_Message(NULL,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
497       END IF;
498 
499       -- Standard call to get message count and if count is 1, get message info.
500       FND_MSG_PUB.Count_And_Get
501       (  p_count          =>   x_msg_count,
502          p_data           =>   x_msg_data
503       );
504 
505 EXCEPTION
506      WHEN FND_API.G_EXC_ERROR THEN
507               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
508                    P_API_NAME => L_API_NAME
509                   ,P_PKG_NAME => G_PKG_NAME
510                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
511                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
512                   ,X_MSG_COUNT => X_MSG_COUNT
513                   ,X_MSG_DATA => X_MSG_DATA
514                   ,X_RETURN_STATUS => X_RETURN_STATUS);
515 
516      WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
517               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
518                    P_API_NAME => L_API_NAME
519                   ,P_PKG_NAME => G_PKG_NAME
520                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
521                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
522                   ,X_MSG_COUNT => X_MSG_COUNT
523                   ,X_MSG_DATA => X_MSG_DATA
524                   ,X_RETURN_STATUS => X_RETURN_STATUS);
525 
526      WHEN OTHERS THEN
527               AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
528                    P_API_NAME => L_API_NAME
529                   ,P_PKG_NAME => G_PKG_NAME
530                   ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
531                   ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
532                   ,X_MSG_COUNT => X_MSG_COUNT
533                   ,X_MSG_DATA => X_MSG_DATA
534                   ,X_RETURN_STATUS => X_RETURN_STATUS);
535 
536 
537 END DELETE_PREFERENCE;
538 
539 End ASF_USER_PREFERENCE_PUB;