[Home] [Help]
PACKAGE BODY: APPS.IEM_THEMES_PVT
Source
1 PACKAGE BODY IEM_THEMES_PVT as
2 /* $Header: iemptheb.pls 115.14 2003/08/26 23:42:00 sboorela shipped $*/
3 /* Fixed Bug 1339163 kbeagle on 11/29/00 Dup theme error when updating score */
4 /* 08/14/01 chtang added create_item_wrap_sss() for 11.5.6 */
5 /* 05/07/02 chtang added update last_update_date of keyword in calculate_weight */
6 /* 10/15/02 chtang added update last_update_date of intent in calculate_weight */
7 /*****************************************************************************/
8 G_PKG_NAME CONSTANT varchar2(30) :='IEM_THEMES_PVT ';
9
10
11 PROCEDURE create_item (p_api_version_number IN NUMBER,
12 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
13 p_commit IN VARCHAR2 := FND_API.G_FALSE,
14 p_score IN NUMBER,
15 p_classification_id IN NUMBER,
16 p_theme IN VARCHAR2,
17 p_query_response IN VARCHAR2,
18 p_CREATED_BY IN NUMBER,
19 p_CREATION_DATE IN DATE,
20 p_LAST_UPDATED_BY IN NUMBER ,
21 p_LAST_UPDATE_DATE IN DATE,
22 p_LAST_UPDATE_LOGIN IN NUMBER ,
23 p_ATTRIBUTE1 IN VARCHAR2,
24 p_ATTRIBUTE2 IN VARCHAR2,
25 p_ATTRIBUTE3 IN VARCHAR2,
26 p_ATTRIBUTE4 IN VARCHAR2,
27 p_ATTRIBUTE5 IN VARCHAR2,
28 p_ATTRIBUTE6 IN VARCHAR2,
29 p_ATTRIBUTE7 IN VARCHAR2,
30 p_ATTRIBUTE8 IN VARCHAR2,
31 p_ATTRIBUTE9 IN VARCHAR2,
32 p_ATTRIBUTE10 IN VARCHAR2,
33 p_ATTRIBUTE11 IN VARCHAR2,
34 p_ATTRIBUTE12 IN VARCHAR2,
35 p_ATTRIBUTE13 IN VARCHAR2,
36 p_ATTRIBUTE14 IN VARCHAR2,
37 p_ATTRIBUTE15 IN VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2
41 ) is
42
43 l_api_name VARCHAR2(255):='create_item';
44 l_api_version_number NUMBER:=1.0;
45
46 l_cnt NUMBER;
47 l_seq_id NUMBER;
48
49 BEGIN
50 -- Initialize API return status to SUCCESS
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 -- Take this out. Handle duplicates in the exception block.
54 SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
55 and theme=p_theme and query_response=p_query_response;
56
57 IF l_cnt=0 THEN
58 SELECT iem_themes_s1.nextval
59 INTO l_seq_id
60 FROM dual;
61 INSERT INTO iem_themes (theme_id,
62 classification_id,
63 theme,
64 score,
65 query_response,
66 created_by,
67 creation_date,
68 last_updated_by,
69 last_update_date,
70 last_update_login,
71 ATTRIBUTE1,
72 ATTRIBUTE2,
73 ATTRIBUTE3,
74 ATTRIBUTE4,
75 ATTRIBUTE5,
76 ATTRIBUTE6,
77 ATTRIBUTE7,
78 ATTRIBUTE8,
79 ATTRIBUTE9,
80 ATTRIBUTE10,
81 ATTRIBUTE11,
82 ATTRIBUTE12,
83 ATTRIBUTE13,
84 ATTRIBUTE14,
85 ATTRIBUTE15
86 )
87 values (l_seq_id,
88 p_classification_id,
89 p_theme,
90 p_score,
91 p_query_response,
92 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
93 sysdate,
94 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
95 sysdate,
96 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
97 p_ATTRIBUTE1,
98 p_ATTRIBUTE2,
99 p_ATTRIBUTE3,
100 p_ATTRIBUTE4,
101 p_ATTRIBUTE5,
102 p_ATTRIBUTE6,
103 p_ATTRIBUTE7,
104 p_ATTRIBUTE8,
105 p_ATTRIBUTE9,
106 p_ATTRIBUTE10,
107 p_ATTRIBUTE11,
108 p_ATTRIBUTE12,
109 p_ATTRIBUTE13,
110 p_ATTRIBUTE14,
111 p_ATTRIBUTE15);
112 END IF;
113 EXCEPTION
114 WHEN OTHERS THEN
115 x_return_status := FND_API.G_RET_STS_ERROR;
116 END;
117
118 PROCEDURE delete_item (p_api_version_number IN NUMBER,
119 p_init_msg_list IN VARCHAR2 ,
120 p_commit IN VARCHAR2 ,
121 p_theme_id IN NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2
125 ) is
126 l_api_name VARCHAR2(255):='delete_item';
127 l_api_version_number NUMBER:=1.0;
128 l_grp_cnt NUMBER;
129
130 BEGIN
131 -- Standard Start of API savepoint
132 SAVEPOINT delete_item_PVT;
133 -- Standard call to check for call compatibility.
134 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
135 p_api_version_number,
136 l_api_name,
137 G_PKG_NAME)
138 THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141 -- Initialize message list if p_init_msg_list is set to TRUE.
142 IF FND_API.to_Boolean( p_init_msg_list )
143 THEN
144 FND_MSG_PUB.initialize;
145 END IF;
146 -- Initialize API return status to SUCCESS
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148
149 DELETE FROM IEM_THEMES WHERE THEME_ID = p_theme_id;
150
151 -- Standard Check Of p_commit.
152 IF FND_API.To_Boolean(p_commit) THEN
153 COMMIT WORK;
154 END IF;
155 -- Standard callto get message count and if count is 1, get message info.
156 FND_MSG_PUB.Count_And_Get
157 ( p_count => x_msg_count,
158 p_data => x_msg_data
159 );
160 EXCEPTION
161 WHEN FND_API.G_EXC_ERROR THEN
162 ROLLBACK TO delete_item_PVT;
163 x_return_status := FND_API.G_RET_STS_ERROR ;
164 FND_MSG_PUB.Count_And_Get
165 ( p_count => x_msg_count,
166 p_data => x_msg_data
167 );
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 ROLLBACK TO delete_item_PVT;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
171 FND_MSG_PUB.Count_And_Get
172 ( p_count => x_msg_count,
173 p_data => x_msg_data
174 );
175 WHEN OTHERS THEN
176 ROLLBACK TO delete_item_PVT;
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 IF FND_MSG_PUB.Check_Msg_Level
179 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
180 THEN
181 FND_MSG_PUB.Add_Exc_Msg
182 ( G_PKG_NAME ,
183 l_api_name
184 );
185 END IF;
186 FND_MSG_PUB.Count_And_Get
187 ( p_count => x_msg_count ,
188 p_data => x_msg_data
189 );
190 END;
191
192 PROCEDURE update_item (p_api_version_number IN NUMBER,
193 p_init_msg_list IN VARCHAR2 ,
194 p_commit IN VARCHAR2 ,
195 p_theme_id IN NUMBER,
196 p_classification_id IN NUMBER,
197 p_theme IN VARCHAR2 ,
198 p_score IN NUMBER,
199 p_query_response IN VARCHAR2,
200 p_LAST_UPDATED_BY IN NUMBER ,
201 p_LAST_UPDATE_DATE IN DATE,
202 p_LAST_UPDATE_LOGIN IN NUMBER ,
203 p_ATTRIBUTE1 IN VARCHAR2,
204 p_ATTRIBUTE2 IN VARCHAR2,
205 p_ATTRIBUTE3 IN VARCHAR2,
206 p_ATTRIBUTE4 IN VARCHAR2,
207 p_ATTRIBUTE5 IN VARCHAR2,
208 p_ATTRIBUTE6 IN VARCHAR2,
209 p_ATTRIBUTE7 IN VARCHAR2,
210 p_ATTRIBUTE8 IN VARCHAR2,
211 p_ATTRIBUTE9 IN VARCHAR2,
212 p_ATTRIBUTE10 IN VARCHAR2,
213 p_ATTRIBUTE11 IN VARCHAR2,
214 p_ATTRIBUTE12 IN VARCHAR2,
215 p_ATTRIBUTE13 IN VARCHAR2,
216 p_ATTRIBUTE14 IN VARCHAR2,
217 p_ATTRIBUTE15 IN VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2
221 ) IS
222
223 l_api_name VARCHAR2(255):='update_item';
224 l_api_version_number NUMBER:=1.0;
225 l_status varchar2(10);
226
227 l_grp_cnt NUMBER;
228
229 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
230 l_LAST_UPDATE_DATE DATE:=SYSDATE;
231 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
232
233 IEM_DUPLICATE_THEME EXCEPTION;
234 PRAGMA EXCEPTION_INIT(IEM_DUPLICATE_THEME, -00001);
235
236 BEGIN
237 -- Standard Start of API savepoint
238 SAVEPOINT update_item_PVT;
239 -- Standard call to check for call compatibility.
240 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
241 p_api_version_number,
242 l_api_name,
243 G_PKG_NAME)
244 THEN
245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
246 END IF;
247 -- Initialize message list if p_init_msg_list is set to TRUE.
248 IF FND_API.to_Boolean( p_init_msg_list )
249 THEN
250 FND_MSG_PUB.initialize;
251 END IF;
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 update IEM_THEMES SET
256 classification_id = decode( p_classification_id, FND_API.G_MISS_NUM,null,null, classification_id, p_classification_id),
257 theme = decode(p_theme,FND_API.G_MISS_CHAR,null,null,theme,p_theme),
258 score = decode(p_score,FND_API.G_MISS_CHAR,null,null,score,p_score),
259 query_response = decode( p_query_response, FND_API.G_MISS_CHAR,null,null, query_response, p_query_response),
260 LAST_UPDATE_DATE = sysdate,
261 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,last_updated_by,l_LAST_UPDATED_BY),
262 LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,last_update_login,l_LAST_UPDATE_LOGIN),
263 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE1, p_ATTRIBUTE1),
264 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE2, p_ATTRIBUTE2),
265 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE3, p_ATTRIBUTE3),
266 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE4, p_ATTRIBUTE4),
267 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, null,null,ATTRIBUTE5, p_ATTRIBUTE5),
268 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE6, p_ATTRIBUTE6),
269 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE7, p_ATTRIBUTE7),
270 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE8, p_ATTRIBUTE8),
271 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE9, p_ATTRIBUTE9),
272 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE10, p_ATTRIBUTE10),
273 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE11, p_ATTRIBUTE11),
274 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE12, p_ATTRIBUTE12),
275 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE13, p_ATTRIBUTE13),
276 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE14, p_ATTRIBUTE14),
277 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE15, p_ATTRIBUTE15)
278 where theme_id = p_theme_id;
279 -- Standard Check Of p_commit.
280 IF FND_API.To_Boolean(p_commit) THEN
281 COMMIT WORK;
282 END IF;
283 -- Standard callto get message count and if count is 1, get message info.
284 FND_MSG_PUB.Count_And_Get
285 ( p_count => x_msg_count,
286 p_data => x_msg_data
287 );
288 EXCEPTION
289 WHEN FND_API.G_EXC_ERROR THEN
290 ROLLBACK TO update_item_PVT;
291 x_return_status := FND_API.G_RET_STS_ERROR ;
292 FND_MSG_PUB.Count_And_Get
293 ( p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 ROLLBACK TO update_item_PVT;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
299 FND_MSG_PUB.Count_And_Get
300 ( p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303 WHEN IEM_DUPLICATE_THEME THEN
304 ROLLBACK TO update_item_PVT;
305 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_THEME');
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 /*
308 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
309 THEN
310 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
311 END IF;
312 */
313 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
314
315 WHEN OTHERS THEN
316 ROLLBACK TO update_item_PVT;
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 IF FND_MSG_PUB.Check_Msg_Level
319 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
320 THEN
321 FND_MSG_PUB.Add_Exc_Msg
322 ( G_PKG_NAME ,
323 l_api_name
324 );
325 END IF;
326 FND_MSG_PUB.Count_And_Get
327 ( p_count => x_msg_count ,
328 p_data => x_msg_data
329 );
330
331 END;
332
333
334 /**************WRPR******************/
335 PROCEDURE create_item_wrap (p_api_version_number IN NUMBER,
336 p_init_msg_list IN VARCHAR2 ,
337 p_commit IN VARCHAR2 ,
338 p_score IN NUMBER,
339 p_classification_id IN NUMBER,
340 p_theme IN VARCHAR2,
341 p_query_response IN VARCHAR2,
342 p_CREATED_BY NUMBER,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2
346 ) is
347 l_api_name VARCHAR2(255):='create_item_jsp';
348 l_api_version_number NUMBER:=1.0;
349 l_grp_cnt NUMBER;
350 l_cnt number;
351 l_theme VARCHAR2(100);
352 l_theme2 VARCHAR2(100);
353
354 IEM_DUPLICATE_THEME EXCEPTION;
355
356 BEGIN
357 SAVEPOINT create_item_jsp;
358 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
359 p_api_version_number,
360 l_api_name,
361 G_PKG_NAME)
362 THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365
366 IF FND_API.to_Boolean( p_init_msg_list )
367 THEN
368 FND_MSG_PUB.initialize;
369 END IF;
370
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 select replace (replace ( replace (p_theme, '<', ''), '>', ''), '"', '''') into l_theme
374 from dual;
375
376 l_theme2 := rtrim(ltrim(l_theme, ' '), ' ');
377
378 -- Take this out. Handle duplicates in the exception block.
379 SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
380 and theme=p_theme and query_response=p_query_response;
381
382 IF (l_cnt > 0 ) then
383 raise IEM_DUPLICATE_THEME;
384 end if;
385
386 IEM_THEMES_PVT.create_item(
387 p_api_version_number =>p_api_version_number,
388 p_init_msg_list => p_init_msg_list,
389 p_commit => p_commit,
390 p_score => p_score,
391 p_classification_id => p_classification_id ,
392 p_theme => l_theme2 ,
393 p_query_response => p_query_response,
394 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
395 p_CREATION_DATE =>SYSDATE,
396 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
397 p_LAST_UPDATE_DATE =>SYSDATE,
398 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
399 p_ATTRIBUTE1 =>null,
400 p_ATTRIBUTE2 =>null,
401 p_ATTRIBUTE3 =>null,
402 p_ATTRIBUTE4 =>null,
403 p_ATTRIBUTE5 =>null,
404 p_ATTRIBUTE6 =>null,
405 p_ATTRIBUTE7 =>null,
406 p_ATTRIBUTE8 =>null,
407 p_ATTRIBUTE9 =>null,
411 p_ATTRIBUTE13 =>null,
408 p_ATTRIBUTE10 =>null,
409 p_ATTRIBUTE11 =>null,
410 p_ATTRIBUTE12 =>null,
412 p_ATTRIBUTE14 =>null,
413 p_ATTRIBUTE15 =>null,
414 x_return_status =>x_return_status,
415 x_msg_count => x_msg_count,
416 x_msg_data => x_msg_data);
417
418 IF FND_API.To_Boolean(p_commit) THEN
419 COMMIT WORK;
420 END IF;
421
422 FND_MSG_PUB.Count_And_Get
423 ( p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426 EXCEPTION
427 WHEN IEM_DUPLICATE_THEME THEN
428 ROLLBACK TO create_item_jsp;
429 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_THEME');
430 FND_MSG_PUB.Add;
431 x_return_status := FND_API.G_RET_STS_ERROR ;
432 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
433
434 WHEN FND_API.G_EXC_ERROR THEN
435 ROLLBACK TO create_item_jsp;
436 x_return_status := FND_API.G_RET_STS_ERROR ;
437 FND_MSG_PUB.Count_And_Get
438 ( p_count => x_msg_count,
439 p_data => x_msg_data
440 );
441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
442 ROLLBACK TO create_item_jsp;
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
444 FND_MSG_PUB.Count_And_Get
445 ( p_count => x_msg_count,
446 p_data => x_msg_data
447 );
448 WHEN OTHERS THEN
449 ROLLBACK TO create_item_jsp;
450 x_return_status := FND_API.G_RET_STS_ERROR;
451 IF FND_MSG_PUB.Check_Msg_Level
452 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
453 THEN
454 FND_MSG_PUB.Add_Exc_Msg
455 ( G_PKG_NAME ,
456 l_api_name
457 );
458 END IF;
459 FND_MSG_PUB.Count_And_Get
460 ( p_count => x_msg_count ,
461 p_data => x_msg_data
462 );
463
464 end;
465
466 PROCEDURE create_item_wrap_sss (p_api_version_number IN NUMBER,
467 p_init_msg_list IN VARCHAR2 ,
468 p_commit IN VARCHAR2 ,
469 p_score IN NUMBER,
470 p_classification_id IN NUMBER,
471 p_theme IN VARCHAR2,
472 p_query_response IN VARCHAR2,
473 p_CREATED_BY NUMBER,
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2
477 ) is
478 l_api_name VARCHAR2(255):='create_item_jsp';
479 l_api_version_number NUMBER:=1.0;
480 l_grp_cnt NUMBER;
481 l_cnt number;
482 l_theme VARCHAR2(100);
483 l_theme2 VARCHAR2(100);
484
485 IEM_DUPLICATE_THEME EXCEPTION;
486
487 BEGIN
488 SAVEPOINT create_item_jsp;
489 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
490 p_api_version_number,
491 l_api_name,
492 G_PKG_NAME)
493 THEN
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END IF;
496
497 IF FND_API.to_Boolean( p_init_msg_list )
498 THEN
499 FND_MSG_PUB.initialize;
500 END IF;
501
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504 select replace (replace ( replace (p_theme, '<', ''), '>', ''), '"', '''') into l_theme
505 from dual;
506
507 l_theme2 := rtrim(ltrim(l_theme, ' '), ' ');
508
509 -- Take this out. Handle duplicates in the exception block.
510 SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
511 and theme=p_theme and query_response=p_query_response;
512
513 IF (l_cnt > 0 ) then
514 raise IEM_DUPLICATE_THEME;
515 end if;
516
517 IEM_THEMES_PVT.create_item(
518 p_api_version_number =>p_api_version_number,
519 p_init_msg_list => p_init_msg_list,
520 p_commit => p_commit,
521 p_score => p_score,
522 p_classification_id => p_classification_id ,
523 p_theme => l_theme2 ,
524 p_query_response => p_query_response,
525 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
526 p_CREATION_DATE =>SYSDATE,
527 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
528 p_LAST_UPDATE_DATE =>SYSDATE,
529 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
530 p_ATTRIBUTE1 =>null,
531 p_ATTRIBUTE2 =>null,
532 p_ATTRIBUTE3 =>null,
533 p_ATTRIBUTE4 =>null,
534 p_ATTRIBUTE5 =>null,
535 p_ATTRIBUTE6 =>null,
536 p_ATTRIBUTE7 =>null,
537 p_ATTRIBUTE8 =>null,
538 p_ATTRIBUTE9 =>null,
539 p_ATTRIBUTE10 =>null,
540 p_ATTRIBUTE11 =>null,
541 p_ATTRIBUTE12 =>null,
542 p_ATTRIBUTE13 =>null,
543 p_ATTRIBUTE14 =>null,
544 p_ATTRIBUTE15 =>null,
545 x_return_status =>x_return_status,
546 x_msg_count => x_msg_count,
547 x_msg_data => x_msg_data);
548
549 IF FND_API.To_Boolean(p_commit) THEN
550 COMMIT WORK;
551 END IF;
552
553 FND_MSG_PUB.Count_And_Get
554 ( p_count => x_msg_count,
555 p_data => x_msg_data
556 );
557 EXCEPTION
558 WHEN IEM_DUPLICATE_THEME THEN
559 ROLLBACK TO create_item_jsp;
560 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_KEYWORD');
561 FND_MSG_PUB.Add;
562 x_return_status := FND_API.G_RET_STS_ERROR ;
566 ROLLBACK TO create_item_jsp;
563 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
564
565 WHEN FND_API.G_EXC_ERROR THEN
567 x_return_status := FND_API.G_RET_STS_ERROR ;
568 FND_MSG_PUB.Count_And_Get
569 ( p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
573 ROLLBACK TO create_item_jsp;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
575 FND_MSG_PUB.Count_And_Get
576 ( p_count => x_msg_count,
577 p_data => x_msg_data
578 );
579 WHEN OTHERS THEN
580 ROLLBACK TO create_item_jsp;
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 IF FND_MSG_PUB.Check_Msg_Level
583 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
584 THEN
585 FND_MSG_PUB.Add_Exc_Msg
586 ( G_PKG_NAME ,
587 l_api_name
588 );
589 END IF;
590 FND_MSG_PUB.Count_And_Get
591 ( p_count => x_msg_count ,
592 p_data => x_msg_data
593 );
594
595 end;
596
597 PROCEDURE delete_item_wrap
598 (p_api_version_number IN NUMBER,
599 P_init_msg_list IN VARCHAR2 ,
600 p_commit IN VARCHAR2 ,
601 p_thes_ids_tbl IN jtf_varchar2_Table_100,
602 x_return_status OUT NOCOPY VARCHAR2,
603 x_msg_count OUT NOCOPY NUMBER,
604 x_msg_data OUT NOCOPY VARCHAR2)
605 IS
606 i INTEGER;
607 l_api_name varchar2(30):='delete_item_batch';
608 l_api_version_number number:=1.0;
609 BEGIN
610
611 --Standard Savepoint
612 SAVEPOINT delete_item_wrap;
613 -- Standard call to check for call compatibility.
614 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
615 p_api_version_number,
616 l_api_name,
617 G_PKG_NAME)
618 THEN
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END IF;
621
622 --Initialize the message list if p_init_msg_list is set to TRUE
623 If FND_API.to_Boolean(p_init_msg_list) THEN
624 FND_MSG_PUB.initialize;
625 END IF;
626
627 --Initialize API status return
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 --Actual API starts here
631 FORALL i IN p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST
632 DELETE
633 FROM IEM_THEMES
634 WHERE theme_id = p_thes_ids_tbl(i);
635
636 IF SQL%NOTFOUND THEN
637 FND_MESSAGE.SET_NAME('IEM', 'IEM_EXP_INVALID_ACCOUNT');
638 FND_MSG_PUB.ADD;
639 RAISE FND_API.G_EXC_ERROR;
640 END IF;
641
642 --Standard check of p_commit
643 IF FND_API.to_Boolean(p_commit) THEN
644 COMMIT WORK;
645 END IF;
646
647 -- Standard callto get message count and if count is 1, get message info.
648 FND_MSG_PUB.Count_And_Get
649 ( p_count => x_msg_count,
650 p_data => x_msg_data
651 );
652 EXCEPTION
653 WHEN FND_API.G_EXC_ERROR THEN
654 ROLLBACK TO delete_item_wrap;
655 x_return_status := FND_API.G_RET_STS_ERROR ;
656 FND_MSG_PUB.Count_And_Get
657 ( p_count => x_msg_count,
658 p_data => x_msg_data
659 );
660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
661 ROLLBACK TO delete_item_wrap;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
663 FND_MSG_PUB.Count_And_Get
664 ( p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667 WHEN OTHERS THEN
668 ROLLBACK TO delete_item_wrap;
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 IF FND_MSG_PUB.Check_Msg_Level
671 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
672 THEN
673 FND_MSG_PUB.Add_Exc_Msg
674 ( G_PKG_NAME ,
675 l_api_name
676 );
677 END IF;
678 FND_MSG_PUB.Count_And_Get
679 ( p_count => x_msg_count ,
680 p_data => x_msg_data
681 );
682 --Standard call to get message count and message info
683 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
684 p_data => x_msg_data);
685 END delete_item_wrap;
686 PROCEDURE delete_item_wrap_sss
687 (p_api_version_number IN NUMBER,
688 P_init_msg_list IN VARCHAR2 ,
689 p_commit IN VARCHAR2 ,
690 p_thes_ids_tbl IN jtf_varchar2_Table_100,
691 x_return_status OUT NOCOPY VARCHAR2,
692 x_msg_count OUT NOCOPY NUMBER,
693 x_msg_data OUT NOCOPY VARCHAR2)
694 IS
695 i INTEGER;
696 l_api_name varchar2(30):='delete_item_batch';
697 l_api_version_number number:=1.0;
698 l_status varchar2(10);
699 l_class_id number;
700 l_email_account_id number;
701 BEGIN
702
703 --Standard Savepoint
704 SAVEPOINT delete_item_wrap;
705 -- Standard call to check for call compatibility.
706 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
707 p_api_version_number,
708 l_api_name,
709 G_PKG_NAME)
710 THEN
711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
712 END IF;
713
714 --Initialize the message list if p_init_msg_list is set to TRUE
715 If FND_API.to_Boolean(p_init_msg_list) THEN
716 FND_MSG_PUB.initialize;
717 END IF;
718
719 --Initialize API status return
720 x_return_status := FND_API.G_RET_STS_SUCCESS;
721
722 --Actual API starts here
723 FOR j in p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST LOOP
727 EXIT;
724 select classification_id into l_class_id
725 from iem_themes
726 where theme_id=p_thes_ids_tbl(j);
728 END LOOP;
729 FORALL i IN p_thes_ids_tbl.FIRST..p_thes_ids_tbl.LAST
730 DELETE
731 FROM IEM_THEMES
732 WHERE theme_id = p_thes_ids_tbl(i);
733 -- Score Readjustment using noise reduction algorithim
734 delete from iem_theme_docs where theme_id not in
735 (select theme_id from iem_themes);
736 delete from iem_account_intent_docs where account_intent_doc_id
737 not in (select account_intent_doc_id from iem_theme_docs);
738 select email_account_id into l_email_account_id
739 from iem_classifications
740 where classification_id=l_class_id;
741 iem_themes_pvt.calculate_weight (l_email_account_id,
742 'Q' ,
743 l_status );
744 iem_themes_pvt.calculate_weight (l_email_account_id,
745 'R' ,
746 l_status );
747
748 IF SQL%NOTFOUND THEN
749 FND_MESSAGE.SET_NAME('IEM', 'IEM_EXP_INVALID_ACCOUNT');
750 FND_MSG_PUB.ADD;
751 RAISE FND_API.G_EXC_ERROR;
752 END IF;
753
754 --Standard check of p_commit
755 IF FND_API.to_Boolean(p_commit) THEN
756 COMMIT WORK;
757 END IF;
758
759 -- Standard callto get message count and if count is 1, get message info.
760 FND_MSG_PUB.Count_And_Get
761 ( p_count => x_msg_count,
762 p_data => x_msg_data
763 );
764 EXCEPTION
765 WHEN FND_API.G_EXC_ERROR THEN
766 ROLLBACK TO delete_item_wrap;
767 x_return_status := FND_API.G_RET_STS_ERROR ;
768 FND_MSG_PUB.Count_And_Get
769 ( p_count => x_msg_count,
770 p_data => x_msg_data
771 );
772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
773 ROLLBACK TO delete_item_wrap;
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
775 FND_MSG_PUB.Count_And_Get
776 ( p_count => x_msg_count,
777 p_data => x_msg_data
778 );
779 WHEN OTHERS THEN
780 ROLLBACK TO delete_item_wrap;
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 IF FND_MSG_PUB.Check_Msg_Level
783 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
784 THEN
785 FND_MSG_PUB.Add_Exc_Msg
786 ( G_PKG_NAME ,
787 l_api_name
788 );
789 END IF;
790 FND_MSG_PUB.Count_And_Get
791 ( p_count => x_msg_count ,
792 p_data => x_msg_data
793 );
794 --Standard call to get message count and message info
795 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
796 p_data => x_msg_data);
797 END delete_item_wrap_sss;
798
799 PROCEDURE update_item_wrap (p_api_version_number IN NUMBER,
800 p_init_msg_list IN VARCHAR2 ,
801 p_commit IN VARCHAR2 ,
802 p_theme_id IN NUMBER,
803 p_classification_id IN NUMBER,
804 p_theme IN VARCHAR2 ,
805 p_score IN NUMBER,
806 p_query_response IN VARCHAR2,
807 x_return_status OUT NOCOPY VARCHAR2,
808 x_msg_count OUT NOCOPY NUMBER,
809 x_msg_data OUT NOCOPY VARCHAR2
810 )is
811 l_api_name VARCHAR2(255):='update_item';
812 l_cnt NUMBER := 0;
813 l_api_version_number NUMBER:=1.0;
814 l_grp_cnt NUMBER;
815 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
816 l_LAST_UPDATE_DATE DATE:=SYSDATE;
817 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
818 l_theme VARCHAR2(100);
819 l_theme2 VARCHAR2(100);
820 IEM_DUPLICATE_THEME EXCEPTION;
821
822 BEGIN
823 -- Standard Start of API savepoint
824 SAVEPOINT update_item_PVT;
825 -- Standard call to check for call compatibility.
826 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
827 p_api_version_number,
828 l_api_name,
829 G_PKG_NAME)
830 THEN
831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
832 END IF;
833 -- Initialize message list if p_init_msg_list is set to TRUE.
834 IF FND_API.to_Boolean( p_init_msg_list )
835 THEN
836 FND_MSG_PUB.initialize;
837 END IF;
838 -- Initialize API return status to SUCCESS
839 x_return_status := FND_API.G_RET_STS_SUCCESS;
840
841 select replace (replace ( replace (p_theme, '<', ''), '>', ''), '"', '''') into l_theme
842 from dual;
843
844 l_theme2 := rtrim(ltrim(l_theme, ' '), ' ');
845
846 -- kbeagle 12-29-00 Added 'and NOT theme_id=p_theme_id' fix for bug 1339163
847
848 SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id and theme=l_theme2 and query_response=p_query_response and NOT theme_id=p_theme_id;
849 IF (l_cnt > 0 ) then
850 raise IEM_DUPLICATE_THEME;
851 end if;
852 IEM_THEMES_PVT.update_item(
853 p_api_version_number =>p_api_version_number,
854 p_init_msg_list => p_init_msg_list,
855 p_commit => p_commit,
856 p_theme_id => p_theme_id,
857 p_classification_id => p_classification_id,
858 p_theme => l_theme2,
859 p_score => p_score,
860 p_query_response => p_query_response,
861 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
862 p_LAST_UPDATE_DATE =>SYSDATE,
863 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
864 p_ATTRIBUTE1 =>null,
865 p_ATTRIBUTE2 =>null,
866 p_ATTRIBUTE3 =>null,
867 p_ATTRIBUTE4 =>null,
868 p_ATTRIBUTE5 =>null,
872 p_ATTRIBUTE9 =>null,
869 p_ATTRIBUTE6 =>null,
870 p_ATTRIBUTE7 =>null,
871 p_ATTRIBUTE8 =>null,
873 p_ATTRIBUTE10 =>null,
874 p_ATTRIBUTE11 =>null,
875 p_ATTRIBUTE12 =>null,
876 p_ATTRIBUTE13 =>null,
877 p_ATTRIBUTE14 =>null,
878 p_ATTRIBUTE15 =>null,
879 x_return_status =>x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data);
882
883
884 -- Standard Check Of p_commit.
885 IF FND_API.To_Boolean(p_commit) THEN
886 COMMIT WORK;
887 END IF;
888 -- Standard callto get message count and if count is 1, get message info.
889 FND_MSG_PUB.Count_And_Get
890 ( p_count => x_msg_count,
891 p_data => x_msg_data
892 );
893 EXCEPTION
894 WHEN IEM_DUPLICATE_THEME THEN
895 ROLLBACK TO update_item_PVT;
896 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_THEME');
897 FND_MSG_PUB.Add;
898 x_return_status := FND_API.G_RET_STS_ERROR ;
899 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
900
901
902 WHEN FND_API.G_EXC_ERROR THEN
903 ROLLBACK TO update_item_PVT;
904 x_return_status := FND_API.G_RET_STS_ERROR ;
905 FND_MSG_PUB.Count_And_Get
906 ( p_count => x_msg_count,
907 p_data => x_msg_data
908 );
909 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
910 ROLLBACK TO update_item_PVT;
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
912 FND_MSG_PUB.Count_And_Get
913 ( p_count => x_msg_count,
914 p_data => x_msg_data
915 );
916 WHEN OTHERS THEN
917 ROLLBACK TO update_item_PVT;
918 x_return_status := FND_API.G_RET_STS_ERROR;
919 IF FND_MSG_PUB.Check_Msg_Level
920 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
921 THEN
922 FND_MSG_PUB.Add_Exc_Msg
923 ( G_PKG_NAME ,
924 l_api_name
925 );
926 END IF;
927 FND_MSG_PUB.Count_And_Get
928 ( p_count => x_msg_count ,
929 p_data => x_msg_data
930 );
931 END;
932 /**************************************************************/
933 PROCEDURE create_item_pm (p_score IN NUMBER,
934 p_classification_id IN NUMBER,
935 p_theme IN VARCHAR2,
936 p_query_response IN VARCHAR2,
937 p_doc_seq_no IN NUMBER,
938 p_CREATED_BY NUMBER,
939 p_CREATION_DATE DATE,
940 p_LAST_UPDATED_BY NUMBER,
941 p_LAST_UPDATE_DATE DATE,
942 p_LAST_UPDATE_LOGIN NUMBER,
943 x_return_status OUT NOCOPY VARCHAR2,
944 x_msg_count OUT NOCOPY NUMBER,
945 x_msg_data OUT NOCOPY VARCHAR2) IS
946
947 l_cnt NUMBER;
948 l_seq_id NUMBER;
949 l_N number; --Total no of document in the system
950 l_R number; -- Total no of document in the bin
951 l_Nt number; -- No of relevant doc in the system
952 l_Rt number; -- No of relevant doc in the bin
953 l_doc_count number;
954 l_weight number;
955 l_temp number;
956 l_email_account_id number;
957 l_status varchar2(10);
958 l_theme_id number;
959 DOC_EXCEP EXCEPTION;
960 BEGIN
961 x_return_status:='S';
962
963 select nvl(sum(doc_count),0)+1 into l_cnt
964 from iem_themes
965 where query_response=p_query_response
966 and classification_id=p_classification_id
967 and theme=p_theme;
968 select email_account_id into l_email_account_id
969 from iem_classifications
970 where classification_id=p_classification_id;
971 IF l_cnt=1 THEN
972 SELECT iem_themes_s1.nextval
973 INTO l_seq_id
974 FROM dual;
975 INSERT INTO iem_themes (theme_id,
976 classification_id,
977 theme,
978 score,
979 query_response,
980 created_by,
981 creation_date,
982 last_updated_by,
983 last_update_date,
984 last_update_login,
985 doc_count)
986 VALUES
987 (l_seq_id,
988 p_classification_id,
989 p_theme,
990 0,
991 p_query_response,
992 p_created_by,
993 p_CREATION_DATE,
994 p_LAST_UPDATED_BY,
995 p_LAST_UPDATE_DATE,
996 p_LAST_UPDATE_LOGIN,
997 l_cnt);
998 IEM_THEME_DOCS_PVT.create_item(p_account_intent_doc_id=>p_doc_seq_no ,
999 p_theme_id=>l_seq_id ,
1000 x_return_status=>l_status);
1001 IF l_status='E' THEN
1002 raise DOC_EXCEP;
1003 END IF;
1004 ELSE
1005 update iem_themes
1006 set doc_count=l_cnt
1007 where query_response=p_query_response
1008 and classification_id=p_classification_id
1009 and theme=p_theme;
1010 select theme_id into l_theme_id
1011 from iem_themes
1012 where query_response=p_query_response
1013 and classification_id=p_classification_id
1014 and theme=p_theme;
1015 IEM_THEME_DOCS_PVT.create_item(p_account_intent_doc_id=>p_doc_seq_no ,
1016 p_theme_id=>l_theme_id ,
1017 x_return_status=>l_status);
1018 IF l_status='E' THEN
1019 raise DOC_EXCEP;
1020 END IF;
1021 END IF;
1022 -- Recalculation of Theme Weight
1023 iem_themes_pvt.calculate_weight (l_email_account_id,
1024 p_query_response ,
1025 l_status );
1026 x_return_status:=l_status;
1027 EXCEPTION WHEN DOC_EXCEP THEN
1028 x_return_status:='E';
1029 WHEN OTHERS THEN
1030 x_return_status:='E';
1031 END;
1032 PROCEDURE calculate_weight (p_email_account_id IN NUMBER,
1033 p_query_response IN VARCHAR2,
1034 x_return_status OUT NOCOPY VARCHAR2) IS
1035
1036 l_N number; --Total no of document in the system
1037 l_R number; -- Total no of document in the bin
1038 l_Nt number; -- No of relevant doc in the system
1039 l_Rt number; -- No of relevant doc in the bin
1040 l_doc_count number;
1041 l_weight number;
1042 l_temp number;
1043 l_rms number;
1044 l_class_id number;
1045 CURSOR c_theme is
1046 select a.theme_id,a.classification_id,
1047 a.theme,a.query_response,a.score
1048 from iem_themes a,iem_classifications b
1049 where a.classification_id=b.classification_id
1050 and b.email_account_id=p_email_account_id
1051 and a.query_response=p_query_response;
1052
1053 cursor c1 is select a.classification_id,sum(power(a.score,2)) score
1054 from iem_themes a,iem_classifications b
1055 where a.classification_id=b.classification_id
1056 and b.email_account_id=p_email_account_id
1057 and a.query_response=p_query_response
1058 group by a.classification_id;
1059
1060 cursor c_calc is select a.theme_id,a.score
1061 from iem_themes a,iem_classifications b
1062 where a.classification_id=b.classification_id
1063 and b.email_account_id=p_email_account_id
1064 and a.query_response=p_query_response
1065 and a.classification_id=l_class_id;
1066 BEGIN
1067 x_return_status:='S';
1068 -- No of document in the system
1069
1070 -- select nvl(sum(document_no),0)
1071 select count(*)
1072 into l_N
1073 from iem_account_intent_docs
1074 where email_account_id=p_email_account_id
1075 and query_response=p_query_response;
1076
1077 for v1 in c_theme LOOP
1078
1079 -- No of documents in the bin
1080
1081 -- select nvl(sum(document_no),0)
1082 select count(*)
1083 into l_R
1084 from iem_account_intent_docs
1085 where classification_id=v1.classification_id
1086 and query_response=v1.query_response;
1087
1088 -- no of document matching the theme in the system
1089
1090 select nvl(sum(a.doc_count),0) into l_Nt
1091 from iem_themes A,iem_classifications B
1092 where A.classification_id=B.classification_id
1093 and B.email_account_id=p_email_account_id
1094 and A.query_response=v1.query_response
1095 and A.theme=v1.theme;
1096
1097 -- no of document matching the theme in the bin
1098
1099 select nvl(sum(doc_count),0) into l_Rt
1100 from iem_themes
1101 where query_response=v1.query_response
1102 and classification_id=v1.classification_id
1103 and theme=v1.theme;
1104
1105 -- l_temp:=((l_Rt+0.5)/(l_Nt-l_Rt+0.5))*((l_N+.5)/(l_R+.5));
1106 l_temp:=((l_Rt+0.5)/(l_Nt-l_Rt+0.5))*((l_N-l_Nt-l_R+l_Rt+0.5)/(l_R-l_Rt+0.5));
1107 l_weight:=round(log(10,l_temp),2);
1108 update iem_themes
1109 set score=l_weight
1110 where theme_id=v1.theme_id;
1111 END LOOP;
1112 -- Normalised the score using RMS
1113 for v1 in c1 LOOP
1114 l_class_id:=v1.classification_id;
1115 update iem_classifications
1116 set last_update_date=sysdate
1117 where classification_id=v1.classification_id;
1118 for v2 in c_calc loop
1119 update iem_themes
1120 set score=round(v2.score/sqrt(v1.score),2),
1121 last_update_date=sysdate
1122 where theme_id=v2.theme_id;
1123 end loop;
1124 end loop;
1125 -- End Of Normalised the score using RMS
1126 EXCEPTION WHEN OTHERS THEN
1127 x_return_status:='E';
1128 END;
1129 END IEM_THEMES_PVT;