[Home] [Help]
PACKAGE BODY: APPS.IMC_RECENT_OBJECT_ACCESS_PUB
Source
1 PACKAGE BODY IMC_RECENT_OBJECT_ACCESS_PUB AS
2 /* $Header: imcroab.pls 115.8 2002/11/12 21:53:25 tsli noship $ */
3
4 /*=======================================================================*/
5
6 FUNCTION Record_Exists (
7 p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
8 p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
9 p_object_id IN IMC_RECENT_ACCESSED_OBJ.object_id%TYPE,
10 p_object_name IN IMC_RECENT_ACCESSED_OBJ.object_name%TYPE
11 ) RETURN VARCHAR2 AS
12
13 l_dummy NUMBER;
14
15 BEGIN
16
17 /* Required Params Validation */
18 IF p_user_id IS NULL THEN
19 /* user id is invalid */
20 FND_MESSAGE.SET_NAME('IMC', g_invalid_user_id);
21 FND_MSG_PUB.ADD;
22 RAISE FND_API.G_EXC_ERROR;
23 ELSIF p_object_type IS NULL THEN
24 /* object type is invalid */
25 FND_MESSAGE.SET_NAME('IMC', g_invalid_object_type);
26 FND_MSG_PUB.ADD;
27 RAISE FND_API.G_EXC_ERROR;
28 ELSIF p_object_id IS NULL THEN
29 /* object id is invalid */
30 FND_MESSAGE.SET_NAME('IMC', g_invalid_object_id);
31 FND_MSG_PUB.ADD;
32 RAISE FND_API.G_EXC_ERROR;
33 ELSIF p_object_name IS NULL THEN
34 /* object name is invalid */
35 FND_MESSAGE.SET_NAME('IMC', g_invalid_object_name);
36 FND_MSG_PUB.ADD;
37 RAISE FND_API.G_EXC_ERROR;
38 END IF;
39
40 -- dbms_output.put_line('Inside record_exists...');
41
42 l_dummy := 0;
43
44 SELECT 1
45 INTO l_dummy
46 FROM IMC_RECENT_ACCESSED_OBJ
47 WHERE USER_ID = p_user_id
48 -- AND OBJECT_TYPE = p_object_type
49 AND OBJECT_ID = p_object_id
50 -- AND OBJECT_NAME = p_object_name
51 AND ROWNUM = 1;
52
53 -- dbms_output.put_line('l_dummy = ' || l_dummy);
54
55 RETURN 'Y';
56
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 RETURN 'N';
60
61 WHEN FND_API.G_EXC_ERROR THEN
62 RETURN FND_API.G_RET_STS_ERROR;
63
64 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
65 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
66
67 WHEN OTHERS THEN
68 FND_MESSAGE.SET_NAME('IMC', g_recent_api_others_ex);
69 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
70 FND_MSG_PUB.ADD;
71 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
72
73 END Record_Exists;
74
75 /*=======================================================================*/
76
77 PROCEDURE Update_Record (
78 p_old_access_id IN IMC_RECENT_ACCESSED_OBJ.access_id%TYPE,
79 p_new_access_id IN IMC_RECENT_ACCESSED_OBJ.access_id%TYPE,
80 p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
81 p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
82 p_object_id IN IMC_RECENT_ACCESSED_OBJ.object_id%TYPE,
83 p_object_name IN IMC_RECENT_ACCESSED_OBJ.object_name%TYPE,
84 p_application_id IN IMC_RECENT_ACCESSED_OBJ.application_id%TYPE,
85 p_date_created IN IMC_RECENT_ACCESSED_OBJ.date_created%TYPE,
86 p_additional_value1 IN IMC_RECENT_ACCESSED_OBJ.additional_value1%TYPE,
87 p_additional_value2 IN IMC_RECENT_ACCESSED_OBJ.additional_value2%TYPE,
88 p_additional_value3 IN IMC_RECENT_ACCESSED_OBJ.additional_value3%TYPE,
89 p_additional_value4 IN IMC_RECENT_ACCESSED_OBJ.additional_value4%TYPE,
90 p_additional_value5 IN IMC_RECENT_ACCESSED_OBJ.additional_value5%TYPE,
91 p_object_version_number IN IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE,
92 p_created_by IN IMC_RECENT_ACCESSED_OBJ.created_by%TYPE,
93 p_creation_date IN IMC_RECENT_ACCESSED_OBJ.creation_date%TYPE,
94 p_last_updated_by IN IMC_RECENT_ACCESSED_OBJ.last_updated_by%TYPE,
95 p_last_update_date IN IMC_RECENT_ACCESSED_OBJ.last_update_date%TYPE,
96 p_last_update_login IN IMC_RECENT_ACCESSED_OBJ.last_update_login%TYPE
97 ) AS
98
99 BEGIN
100
101 UPDATE IMC_RECENT_ACCESSED_OBJ SET
102 ACCESS_ID = p_new_access_id,
103 OBJECT_TYPE = p_object_type,
104 OBJECT_ID = p_object_id,
105 OBJECT_NAME = DECODE(p_object_name, FND_API.G_MISS_CHAR, NULL, p_object_name),
106 APPLICATION_ID = DECODE(p_application_id, FND_API.G_MISS_NUM, NULL, p_application_id),
107 DATE_CREATED = p_date_created,
108 ADDITIONAL_VALUE1 = DECODE(p_additional_value1, FND_API.G_MISS_CHAR, NULL, p_additional_value1),
109 ADDITIONAL_VALUE2 = DECODE(p_additional_value2, FND_API.G_MISS_CHAR, NULL, p_additional_value2),
110 ADDITIONAL_VALUE3 = DECODE(p_additional_value3, FND_API.G_MISS_CHAR, NULL, p_additional_value3),
111 ADDITIONAL_VALUE4 = DECODE(p_additional_value4, FND_API.G_MISS_CHAR, NULL, p_additional_value4),
112 ADDITIONAL_VALUE5 = DECODE(p_additional_value5, FND_API.G_MISS_CHAR, NULL, p_additional_value5),
113 OBJECT_VERSION_NUMBER = p_object_version_number,
114 CREATED_BY = p_created_by,
115 CREATION_DATE = p_creation_date,
116 LAST_UPDATED_BY = p_last_updated_by,
117 LAST_UPDATE_DATE = p_last_update_date,
118 LAST_UPDATE_LOGIN = p_last_update_login
119 WHERE ACCESS_ID = p_old_access_id;
120
121 IF SQL%NOTFOUND THEN
122 RAISE NO_DATA_FOUND;
123 END IF;
124
125 COMMIT;
126
127 END Update_Record;
128
129 /*=======================================================================*/
130
131 PROCEDURE Insert_Record (
132 p_access_id IN IMC_RECENT_ACCESSED_OBJ.access_id%TYPE,
133 p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
134 p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
135 p_object_id IN IMC_RECENT_ACCESSED_OBJ.object_id%TYPE,
136 p_object_name IN IMC_RECENT_ACCESSED_OBJ.object_name%TYPE,
137 p_application_id IN IMC_RECENT_ACCESSED_OBJ.application_id%TYPE,
138 p_date_created IN IMC_RECENT_ACCESSED_OBJ.date_created%TYPE,
139 p_additional_value1 IN IMC_RECENT_ACCESSED_OBJ.additional_value1%TYPE,
140 p_additional_value2 IN IMC_RECENT_ACCESSED_OBJ.additional_value2%TYPE,
141 p_additional_value3 IN IMC_RECENT_ACCESSED_OBJ.additional_value3%TYPE,
142 p_additional_value4 IN IMC_RECENT_ACCESSED_OBJ.additional_value4%TYPE,
143 p_additional_value5 IN IMC_RECENT_ACCESSED_OBJ.additional_value5%TYPE,
144 p_object_version_number IN IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE,
145 p_created_by IN IMC_RECENT_ACCESSED_OBJ.created_by%TYPE,
146 p_creation_date IN IMC_RECENT_ACCESSED_OBJ.creation_date%TYPE,
147 p_last_updated_by IN IMC_RECENT_ACCESSED_OBJ.last_updated_by%TYPE,
148 p_last_update_date IN IMC_RECENT_ACCESSED_OBJ.last_update_date%TYPE,
149 p_last_update_login IN IMC_RECENT_ACCESSED_OBJ.last_update_login%TYPE
150 ) AS
151
152 BEGIN
153
154 INSERT INTO IMC_RECENT_ACCESSED_OBJ (
155 ACCESS_ID,
156 USER_ID,
157 OBJECT_TYPE,
158 OBJECT_ID,
159 OBJECT_NAME,
160 APPLICATION_ID,
161 DATE_CREATED,
162 ADDITIONAL_VALUE1,
163 ADDITIONAL_VALUE2,
164 ADDITIONAL_VALUE3,
165 ADDITIONAL_VALUE4,
166 ADDITIONAL_VALUE5,
167 OBJECT_VERSION_NUMBER,
168 CREATED_BY,
169 CREATION_DATE,
170 LAST_UPDATED_BY,
171 LAST_UPDATE_DATE,
172 LAST_UPDATE_LOGIN
173 ) VALUES (
174 p_access_id,
175 p_user_id,
176 p_object_type,
177 p_object_id,
178 DECODE(p_object_name, FND_API.G_MISS_CHAR, NULL, p_object_name),
179 DECODE(p_application_id, FND_API.G_MISS_NUM, NULL, p_application_id),
180 p_date_created,
181 DECODE(p_additional_value1, FND_API.G_MISS_CHAR, NULL, p_additional_value1),
182 DECODE(p_additional_value2, FND_API.G_MISS_CHAR, NULL, p_additional_value2),
183 DECODE(p_additional_value3, FND_API.G_MISS_CHAR, NULL, p_additional_value3),
184 DECODE(p_additional_value4, FND_API.G_MISS_CHAR, NULL, p_additional_value4),
185 DECODE(p_additional_value5, FND_API.G_MISS_CHAR, NULL, p_additional_value5),
186 p_object_version_number,
187 p_created_by,
188 p_creation_date,
189 p_last_updated_by,
190 p_last_update_date,
191 p_last_update_login
192 );
193
194 COMMIT;
195
196 END Insert_Record;
197
198 /*=======================================================================*/
199
200 PROCEDURE Add_Recently_Accessed_Object (
201 p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
202 p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
203 p_object_id IN IMC_RECENT_ACCESSED_OBJ.object_id%TYPE,
204 p_object_name IN IMC_RECENT_ACCESSED_OBJ.object_name%TYPE,
205 p_application_id IN IMC_RECENT_ACCESSED_OBJ.application_id%TYPE,
206 p_additional_value1 IN IMC_RECENT_ACCESSED_OBJ.additional_value1%TYPE,
207 p_additional_value2 IN IMC_RECENT_ACCESSED_OBJ.additional_value2%TYPE,
208 p_additional_value3 IN IMC_RECENT_ACCESSED_OBJ.additional_value3%TYPE,
209 p_additional_value4 IN IMC_RECENT_ACCESSED_OBJ.additional_value4%TYPE,
210 p_additional_value5 IN IMC_RECENT_ACCESSED_OBJ.additional_value5%TYPE,
211 p_object_version_number IN IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY VARCHAR2,
214 x_msg_data OUT NOCOPY VARCHAR2
215 ) AS
216
217 CURSOR records_for_this_user IS
218 SELECT *
219 FROM IMC_RECENT_ACCESSED_OBJ
220 WHERE USER_ID = p_user_id
221 ORDER BY access_id;
222
223 l_user_record records_for_this_user%ROWTYPE;
224 l_exists VARCHAR2(1);
225 l_maintain VARCHAR2(30);
226 l_curr_count NUMBER;
227 l_max_records NUMBER;
228 l_old_access_id IMC_RECENT_ACCESSED_OBJ.access_id%TYPE;
229 l_new_access_id IMC_RECENT_ACCESSED_OBJ.access_id%TYPE;
230 l_object_version_number IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE;
231 l_last_update_login IMC_RECENT_ACCESSED_OBJ.last_update_login%TYPE;
232
233 BEGIN
234
235 l_exists := Record_Exists(p_user_id, p_object_type, p_object_id, p_object_name);
236
237 -- dbms_output.put_line('l_exists = ' || l_exists);
238
239 IF (l_exists = 'Y') THEN
240 /* Record exists; Update details of access. */
241 SELECT access_id
242 INTO l_old_access_id
243 FROM IMC_RECENT_ACCESSED_OBJ
244 WHERE USER_ID = p_user_id
245 -- AND OBJECT_TYPE = p_object_type
246 AND OBJECT_ID = p_object_id
247 AND ROWNUM = 1;
248
249 /* init access_id */
250 SELECT IMC_RECENT_ACCESSED_OBJ_S.NEXTVAL INTO l_new_access_id FROM DUAL;
251
252 /* init object version number */
253 l_object_version_number := NVL(p_object_version_number, g_object_version_number);
254
255 /* init last_update_login */
256 IF (FND_GLOBAL.conc_login_id = -1) OR (FND_GLOBAL.conc_login_id IS NULL) THEN
257 l_last_update_login := FND_GLOBAL.login_id;
258 ELSE
259 l_last_update_login := FND_GLOBAL.conc_login_id;
260 END IF;
261
262 -- dbms_output.put_line('l_old_access_id = ' || l_old_access_id);
263 -- dbms_output.put_line('l_new_access_id = ' || l_new_access_id);
264 -- dbms_output.put_line('l_object_version_number = ' || l_object_version_number);
265 -- dbms_output.put_line('l_last_update_login = ' || l_last_update_login);
266
267 -- dbms_output.put_line('Going to update record...');
268
269 Update_Record (
270 l_old_access_id,
271 l_new_access_id,
272 p_user_id,
273 p_object_type,
274 p_object_id,
275 p_object_name,
276 p_application_id,
277 SYSDATE,
278 p_additional_value1,
279 p_additional_value2,
280 p_additional_value3,
281 p_additional_value4,
282 p_additional_value5,
283 l_object_version_number,
284 nvl(FND_GLOBAL.user_id, -1), /* Created by */
285 SYSDATE, /* Creation date */
286 nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
287 SYSDATE, /* Last update date */
288 l_last_update_login /* Last update login */
289 );
290
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292 ELSIF (l_exists = 'N') THEN
293 /* Record doesn't exist; Create */
294
295 /* init access_id */
296 SELECT IMC_RECENT_ACCESSED_OBJ_S.NEXTVAL INTO l_new_access_id FROM DUAL;
297
298 /* init object version number */
299 l_object_version_number := NVL(p_object_version_number, g_object_version_number);
300
301 /* init last_update_login */
302 IF (FND_GLOBAL.conc_login_id = -1) OR (FND_GLOBAL.conc_login_id IS NULL) THEN
303 l_last_update_login := FND_GLOBAL.login_id;
304 ELSE
305 l_last_update_login := FND_GLOBAL.conc_login_id;
306 END IF;
307
308 -- dbms_output.put_line('l_new_access_id = ' || l_new_access_id);
309 -- dbms_output.put_line('l_object_version_number = ' || l_object_version_number);
310 -- dbms_output.put_line('l_last_update_login = ' || l_last_update_login);
311
312 -- l_max_records := NVL(FND_PROFILE.value(g_store_max_profile), g_default_max_store);
313 l_max_records := NVL(FND_PROFILE.value(g_display_max_profile), g_default_max_display);
314 -- dbms_output.put_line('l_max_records = ' || l_max_records);
315
316 -- l_maintain := NVL(FND_PROFILE.value(g_maintenance_profile), g_default_maintenance);
317 -- Table will now be maintained, by default.
318 l_maintain := 'Y';
319 -- dbms_output.put_line('l_maintain = ' || l_maintain);
320
321 OPEN records_for_this_user;
322
323 IF l_maintain = 'Y' OR l_maintain = 'Yes' THEN
324 SELECT count(*)
325 INTO l_curr_count
326 FROM IMC_RECENT_ACCESSED_OBJ
327 WHERE USER_ID = p_user_id;
328
329 -- dbms_output.put_line('l_curr_count = ' || l_curr_count);
330
331 IF l_curr_count < l_max_records THEN
332 -- dbms_output.put_line('Going to insert record...');
333
334 Insert_Record (
335 l_new_access_id,
336 p_user_id,
337 p_object_type,
338 p_object_id,
339 p_object_name,
340 p_application_id,
341 SYSDATE,
342 p_additional_value1,
343 p_additional_value2,
344 p_additional_value3,
345 p_additional_value4,
346 p_additional_value5,
347 l_object_version_number,
348 nvl(FND_GLOBAL.user_id, -1), /* Created by */
349 SYSDATE, /* Creation date */
350 nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
351 SYSDATE, /* Last update date */
352 l_last_update_login /* Last update login */
353 );
354 ELSE
358 -- dbms_output.put_line('l_old_access_id = ' || l_old_access_id);
355 FETCH records_for_this_user INTO l_user_record;
356
357 l_old_access_id := l_user_record.access_id;
359
360 -- dbms_output.put_line('Going to insert record...');
361
362 Update_Record (
363 l_old_access_id,
364 l_new_access_id,
365 p_user_id,
366 p_object_type,
367 p_object_id,
368 p_object_name,
369 p_application_id,
370 SYSDATE,
371 p_additional_value1,
372 p_additional_value2,
373 p_additional_value3,
374 p_additional_value4,
375 p_additional_value5,
376 l_object_version_number,
377 nvl(FND_GLOBAL.user_id, -1), /* Created by */
378 SYSDATE, /* Creation date */
379 nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
380 SYSDATE, /* Last update date */
381 l_last_update_login /* Last update login */
382 );
383
384 END IF;
385
386 CLOSE records_for_this_user;
387
388 x_return_status := FND_API.G_RET_STS_SUCCESS;
389 ELSE
390 -- dbms_output.put_line('Going to insert record...');
391
392 Insert_Record (
393 l_new_access_id,
394 p_user_id,
395 p_object_type,
396 p_object_id,
397 p_object_name,
398 p_application_id,
399 SYSDATE,
400 p_additional_value1,
401 p_additional_value2,
402 p_additional_value3,
403 p_additional_value4,
404 p_additional_value5,
405 l_object_version_number,
406 nvl(FND_GLOBAL.user_id, -1), /* Created by */
407 SYSDATE, /* Creation date */
408 nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
409 SYSDATE, /* Last update date */
410 l_last_update_login /* Last update login */
411 );
412 END IF;
413
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415 ELSIF (l_exists = FND_API.G_RET_STS_ERROR) THEN
416 /* Error */
417 RAISE FND_API.G_EXC_ERROR;
418 ELSIF (l_exists = FND_API.G_RET_STS_UNEXP_ERROR) THEN
419 /* Unexpected error */
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END IF;
422
423 EXCEPTION
424 WHEN FND_API.G_EXC_ERROR THEN
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 FND_MSG_PUB.Count_And_Get (
427 p_encoded => FND_API.G_FALSE,
428 p_count => x_msg_count,
429 p_data => x_msg_data
430 );
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
433 FND_MSG_PUB.Count_And_Get (
434 p_encoded => FND_API.G_FALSE,
435 p_count => x_msg_count,
436 p_data => x_msg_data
437 );
438 WHEN OTHERS THEN
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 FND_MSG_PUB.Count_And_Get (
441 p_encoded => FND_API.G_FALSE,
442 p_count => x_msg_count,
443 p_data => x_msg_data
444 );
445
446 END Add_Recently_Accessed_Object;
447
448 /*=======================================================================*/
449
450 PROCEDURE Get_Recently_Accessed_Objects (
451 p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
452 p_object_type IN IMC_RECENT_ACCESSED_OBJ.object_type%TYPE,
453 p_application_id IN IMC_RECENT_ACCESSED_OBJ.application_id%TYPE,
454 p_object_version_number IN IMC_RECENT_ACCESSED_OBJ.object_version_number%TYPE,
455 x_object_info OUT NOCOPY ref_cursor_rec_obj_acc,
456 x_return_status OUT NOCOPY VARCHAR2,
457 x_msg_count OUT NOCOPY VARCHAR2,
458 x_msg_data OUT NOCOPY VARCHAR2
459 ) AS
460
461 l_query VARCHAR2(1000);
462 l_where_clause VARCHAR2(500);
463 l_order_by_clause VARCHAR2(100);
464 l_max_records NUMBER;
465
466 BEGIN
467
468 /* Required Params Validation */
469 IF p_user_id IS NULL THEN
470 /* user id is invalid */
471 FND_MESSAGE.SET_NAME('IMC', g_invalid_user_id);
472 FND_MSG_PUB.ADD;
473 RAISE FND_API.G_EXC_ERROR;
474 ELSE
475 l_query := 'SELECT object_type, object_id, object_name, date_created ' ||
476 'FROM IMC_RECENT_ACCESSED_OBJ ';
477
478 l_where_clause := 'WHERE user_id = ' || p_user_id || ' ';
479
480 IF p_object_type IS NOT NULL THEN
481 l_where_clause := l_where_clause ||
482 'AND object_type = ''' || p_object_type || ''' ';
483 END IF;
484
485 IF p_application_id IS NOT NULL THEN
486 l_where_clause := l_where_clause ||
487 'AND application_id = ' || p_application_id || ' ';
488 END IF;
489
490 IF p_object_version_number IS NOT NULL THEN
491 l_where_clause := l_where_clause ||
492 'AND object_version_number = ' || p_object_version_number || ' ';
493 END IF;
494
495 /* Only return the number of records specified in the profile */
496 l_max_records := NVL(FND_PROFILE.value(g_display_max_profile), g_default_max_display);
497
498 l_where_clause := l_where_clause ||
502
499 'AND ROWNUM <= ' || l_max_records || ' ';
500
501 l_order_by_clause := 'ORDER BY access_id DESC';
503 l_query := l_query || l_where_clause || l_order_by_clause;
504
505 OPEN x_object_info FOR l_query;
506
507 x_return_status := FND_API.G_RET_STS_SUCCESS;
508 END IF;
509
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 x_return_status := FND_API.G_RET_STS_SUCCESS;
513 FND_MESSAGE.SET_NAME('IMC', g_no_objs_recently_accessed);
514 FND_MSG_PUB.ADD;
515 FND_MSG_PUB.Count_And_Get (
516 p_encoded => FND_API.G_FALSE,
517 p_count => x_msg_count,
518 p_data => x_msg_data
519 );
520 WHEN FND_API.G_EXC_ERROR THEN
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 FND_MSG_PUB.Count_And_Get (
523 p_encoded => FND_API.G_FALSE,
524 p_count => x_msg_count,
525 p_data => x_msg_data
526 );
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 FND_MSG_PUB.Count_And_Get (
530 p_encoded => FND_API.G_FALSE,
531 p_count => x_msg_count,
532 p_data => x_msg_data
533 );
534 WHEN OTHERS THEN
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 FND_MSG_PUB.Count_And_Get (
537 p_encoded => FND_API.G_FALSE,
538 p_count => x_msg_count,
539 p_data => x_msg_data
540 );
541
542 END Get_Recently_Accessed_Objects;
543
544 /*=======================================================================*/
545
546 PROCEDURE Flush (
547 p_user_id IN IMC_RECENT_ACCESSED_OBJ.user_id%TYPE,
548 x_flush_count OUT NOCOPY NUMBER,
549 x_return_status OUT NOCOPY VARCHAR2,
550 x_msg_count OUT NOCOPY VARCHAR2,
551 x_msg_data OUT NOCOPY VARCHAR2
552 ) AS
553
554 CURSOR records_for_this_user IS
555 SELECT *
556 FROM IMC_RECENT_ACCESSED_OBJ
557 WHERE USER_ID = p_user_id
558 ORDER BY access_id;
559
560 l_user_record records_for_this_user%ROWTYPE;
561 l_curr_count NUMBER;
562 l_max_records NUMBER;
563 l_num_to_delete NUMBER;
564
565 BEGIN
566
567 /* Required Params Validation */
568 IF p_user_id IS NULL THEN
569 /* user id is invalid */
570 FND_MESSAGE.SET_NAME('IMC', g_invalid_user_id);
571 FND_MSG_PUB.ADD;
572 RAISE FND_API.G_EXC_ERROR;
573 ELSE
574 -- l_max_records := NVL(FND_PROFILE.value(g_store_max_profile), g_default_max_store);
575 l_max_records := NVL(FND_PROFILE.value(g_display_max_profile), g_default_max_display);
576 x_flush_count := 0;
577
578 OPEN records_for_this_user;
579
580 SELECT count(*)
581 INTO l_curr_count
582 FROM IMC_RECENT_ACCESSED_OBJ
583 WHERE USER_ID = p_user_id;
584
585 IF l_curr_count <= l_max_records THEN
586 /* Nothing to do */
587 x_return_status := FND_API.G_RET_STS_SUCCESS;
588 ELSE
589 l_num_to_delete := l_curr_count - l_max_records;
590
591 WHILE x_flush_count < l_num_to_delete LOOP
592 FETCH records_for_this_user INTO l_user_record;
593
594 DELETE FROM IMC_RECENT_ACCESSED_OBJ
595 WHERE ACCESS_ID = l_user_record.access_id;
596
597 IF SQL%NOTFOUND THEN
598 RAISE NO_DATA_FOUND;
599 END IF;
600
601 x_flush_count := x_flush_count + 1;
602 END LOOP;
603
604 COMMIT;
605
606 END IF;
607
608 CLOSE records_for_this_user;
609
610 END IF;
611
612 EXCEPTION
613 WHEN NO_DATA_FOUND THEN
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 FND_MESSAGE.SET_NAME('IMC', g_could_not_delete_entry);
616 FND_MSG_PUB.ADD;
617 FND_MSG_PUB.Count_And_Get (
618 p_encoded => FND_API.G_FALSE,
619 p_count => x_msg_count,
620 p_data => x_msg_data
621 );
622 WHEN FND_API.G_EXC_ERROR THEN
626 p_count => x_msg_count,
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 FND_MSG_PUB.Count_And_Get (
625 p_encoded => FND_API.G_FALSE,
627 p_data => x_msg_data
628 );
629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 FND_MSG_PUB.Count_And_Get (
632 p_encoded => FND_API.G_FALSE,
633 p_count => x_msg_count,
634 p_data => x_msg_data
635 );
636 WHEN OTHERS THEN
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
638 FND_MSG_PUB.Count_And_Get (
639 p_encoded => FND_API.G_FALSE,
640 p_count => x_msg_count,
641 p_data => x_msg_data
642 );
643
644 END Flush;
645
646 END IMC_RECENT_OBJECT_ACCESS_PUB;