[Home] [Help]
PACKAGE BODY: APPS.IEM_CLASSIFICATIONS_PVT
Source
1 PACKAGE BODY IEM_CLASSIFICATIONS_PVT as
2 /* $Header: iemvclab.pls 115.16 2003/08/26 23:42:12 sboorela shipped $*/
3
4 /* Fixed Bug 1339176 rtripath on 11/27/00 Do the cascading delete */
5 /* 08/14/01 chtang added create_item_wrap_sss() for 11.5.6 */
6 /* 06/05/02 chtang fixed 2403484
7 /* 11/20/02 chtang removed SQL%NOTFOUND in delete_item_wrap_sss
8 /*****************************************************************/
9 G_PKG_NAME CONSTANT varchar2(30) :='IEM_CLASSIFICATIONS_PVT';
10
11 -- Start of Comments
12 -- API name : create_item
13 -- Type : Private
14 -- Function : This procedure create a record in the table IEM_CLASSIFICATIONS
15 -- Pre-reqs : None.
16 -- Parameters :
17 -- IN
18 -- p_api_version_number IN NUMBER Required
19 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
20 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
21 -- p_account_name IN VARCHAR2,
22 -- p_email_user IN VARCHAR2,
23 -- p_domain IN VARCHAR2,
24 -- p_email_password IN VARCHAR2,
25 -- p_account_profile IN VARCHAR2:=FND_API.G_MISS_CHAR,
26 -- p_db_server_id IN NUMBER,
27 -- p_server_group_id IN NUMBER,
28 --
29 -- OUT
30 -- x_return_status OUT VARCHAR2
31 -- x_msg_count OUT NUMBER
32 -- x_msg_data OUT VARCHAR2
33 --
34 -- Version : 1.0
35 -- Notes :
36 --
37 -- End of comments
38 -- **********************************************************
39
40
41 PROCEDURE create_item (p_api_version_number IN NUMBER,
42 p_init_msg_list IN VARCHAR2 ,
43 p_commit IN VARCHAR2 ,
44 p_email_account_id IN NUMBER,
45 p_classification IN VARCHAR2,
46 p_CREATED_BY NUMBER,
47 p_CREATION_DATE DATE,
48 p_LAST_UPDATED_BY NUMBER,
49 p_LAST_UPDATE_DATE DATE,
50 p_LAST_UPDATE_LOGIN NUMBER,
51 p_ATTRIBUTE1 VARCHAR2,
52 p_ATTRIBUTE2 VARCHAR2,
53 p_ATTRIBUTE3 VARCHAR2,
54 p_ATTRIBUTE4 VARCHAR2,
55 p_ATTRIBUTE5 VARCHAR2,
56 p_ATTRIBUTE6 VARCHAR2,
57 p_ATTRIBUTE7 VARCHAR2,
58 p_ATTRIBUTE8 VARCHAR2,
59 p_ATTRIBUTE9 VARCHAR2,
60 p_ATTRIBUTE10 VARCHAR2,
61 p_ATTRIBUTE11 VARCHAR2,
62 p_ATTRIBUTE12 VARCHAR2,
63 p_ATTRIBUTE13 VARCHAR2,
64 p_ATTRIBUTE14 VARCHAR2,
65 p_ATTRIBUTE15 VARCHAR2,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2
69 )is
70
71
72 l_api_name VARCHAR2(255):='create_item';
73 l_api_version_number NUMBER:=1.0;
74 l_seq number;
75 l_cnt number;
76
77
78 BEGIN
79
80 -- Standard Start of API savepoint
81 SAVEPOINT create_item_PVT;
82
83 -- Standard call to check for call compatibility.
84 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
85 p_api_version_number,
86 l_api_name,
87 G_PKG_NAME)
88 THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91
92 -- Initialize message list if p_init_msg_list is set to TRUE.
93 IF FND_API.to_Boolean( p_init_msg_list )
94 THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97
98 -- Initialize API return status to SUCCESS
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 -- Take this out. Handle duplicates in exception handling.
102 SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
103 CLASSIFICATION=p_classification AND rownum=1;
104
105 IF l_cnt=0 THEN
106 select iem_classifications_s1.nextval into l_seq from dual;
107 INSERT INTO IEM_CLASSIFICATIONS
108 (
109 CLASSIFICATION_ID,
110 EMAIL_ACCOUNT_ID ,
111 CLASSIFICATION ,
112 CREATED_BY,
113 CREATION_DATE,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_DATE,
116 LAST_UPDATE_LOGIN,
117 ATTRIBUTE1,
118 ATTRIBUTE2,
119 ATTRIBUTE3,
120 ATTRIBUTE4,
121 ATTRIBUTE5,
122 ATTRIBUTE6,
123 ATTRIBUTE7,
124 ATTRIBUTE8,
125 ATTRIBUTE9,
126 ATTRIBUTE10,
127 ATTRIBUTE11,
128 ATTRIBUTE12,
129 ATTRIBUTE13,
130 ATTRIBUTE14,
131 ATTRIBUTE15
132 )
133 VALUES
134 (l_seq,
135 p_email_account_id,
136 p_classification,
137 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
138 sysdate,
139 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
140 sysdate,
141 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
142 p_ATTRIBUTE1,
143 p_ATTRIBUTE2,
144 p_ATTRIBUTE3,
145 p_ATTRIBUTE4,
146 p_ATTRIBUTE5,
147 p_ATTRIBUTE6,
148 p_ATTRIBUTE7,
149 p_ATTRIBUTE8,
150 p_ATTRIBUTE9,
151 p_ATTRIBUTE10,
152 p_ATTRIBUTE11,
153 p_ATTRIBUTE12,
154 p_ATTRIBUTE13,
155 p_ATTRIBUTE14,
156 p_ATTRIBUTE15);
157 ELSE
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159 END IF;
160
161 -- Standard Check Of p_commit.
162 IF FND_API.To_Boolean(p_commit) THEN
163 COMMIT WORK;
164 END IF;
165 -- Standard callto get message count and if count is 1, get message info.
166 FND_MSG_PUB.Count_And_Get
167 ( p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 ROLLBACK TO create_item_PVT;
173 x_return_status := FND_API.G_RET_STS_ERROR ;
174 FND_MSG_PUB.Count_And_Get
175 ( p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179 ROLLBACK TO update_item_PVT;
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181 FND_MSG_PUB.Count_And_Get
182 ( p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185 WHEN OTHERS THEN
186 ROLLBACK TO create_item_PVT;
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 IF FND_MSG_PUB.Check_Msg_Level
189 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
190 THEN
191 FND_MSG_PUB.Add_Exc_Msg
192 ( G_PKG_NAME ,
193 l_api_name
194 );
195 END IF;
196 FND_MSG_PUB.Count_And_Get
197 ( p_count => x_msg_count ,
198 p_data => x_msg_data
199 );
200
201 end;
202
203
204 -- Start of Comments
205 -- API name : delete_item
206 -- Type : Private
207 -- Function : This procedure delete a record in the table IEM_CLASSIFICATIONS
208 -- Pre-reqs : None.
209 -- Parameters :
210 -- IN
211 -- p_api_version_number IN NUMBER Required
212 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
213 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
214 -- p_email_account_id in number:=FND_API.G_MISS_NUM,
215 -- p_account_name IN VARCHAR2 :=FND_API.G_MISS_CHAR,
216
217 -- OUT
218 -- x_return_status OUT VARCHAR2
219 -- x_msg_count OUT NUMBER
220 -- x_msg_data OUT VARCHAR2
221 --
222 -- Version : 1.0
223 -- Notes :
224 --
225 -- End of comments
226 -- **********************************************************
227
228 PROCEDURE delete_item (p_api_version_number IN NUMBER,
229 p_init_msg_list IN VARCHAR2 ,
230 p_commit IN VARCHAR2 ,
231 p_email_account_id in number,
232 p_classification IN VARCHAR2 ,
233 p_classification_id in number,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2
237 )is
238 l_api_name VARCHAR2(255):='delete_item';
239 l_api_version_number NUMBER:=1.0;
240
241 BEGIN
242 -- Standard Start of API savepoint
243 SAVEPOINT delete_item_PVT;
244 -- Standard call to check for call compatibility.
245 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
246 p_api_version_number,
247 l_api_name,
248 G_PKG_NAME)
249 THEN
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252 -- Initialize message list if p_init_msg_list is set to TRUE.
253 IF FND_API.to_Boolean( p_init_msg_list )
254 THEN
255 FND_MSG_PUB.initialize;
256 END IF;
257 -- Initialize API return status to SUCCESS
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259 IF p_classification_id IS NOT NULL THEN
260 DELETE FROM IEM_THEMES
261 WHERE CLASSIFICATION_ID=p_classification_id;
262 DELETE FROM IEM_CLASSIFICATIONS
263 WHERE CLASSIFICATION_ID=p_classification_id;
264 ELSE
265 DELETE FROM IEM_CLASSIFICATIONS
266 WHERE EMAIL_ACCOUNT_ID=P_EMAIL_ACCOUNT_ID
267 AND CLASSIFICATION=P_CLASSIFICATION;
268 END IF;
269
270 -- Standard Check Of p_commit.
271 IF FND_API.To_Boolean(p_commit) THEN
272 COMMIT WORK;
273 END IF;
274 -- Standard callto get message count and if count is 1, get message info.
275 FND_MSG_PUB.Count_And_Get
276 ( p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 ROLLBACK TO delete_item_PVT;
282 x_return_status := FND_API.G_RET_STS_ERROR ;
283 FND_MSG_PUB.Count_And_Get
284 ( p_count => x_msg_count,
285 p_data => x_msg_data
286 );
287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
288 ROLLBACK TO delete_item_PVT;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
290 FND_MSG_PUB.Count_And_Get
291 ( p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294 WHEN OTHERS THEN
295 ROLLBACK TO delete_item_PVT;
296 x_return_status := FND_API.G_RET_STS_ERROR;
297 IF FND_MSG_PUB.Check_Msg_Level
298 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
299 THEN
300 FND_MSG_PUB.Add_Exc_Msg
301 ( G_PKG_NAME ,
302 l_api_name
303 );
304 END IF;
305 FND_MSG_PUB.Count_And_Get
306 ( p_count => x_msg_count ,
307 p_data => x_msg_data
308 );
309
310 END;
311
312 -- Start of Comments
313 -- API name : update_item
314 -- Type : Private
315 -- Function : This procedure update a record in the table IEM_CLASSIFICATIONS
316 -- Pre-reqs : None.
317 -- Parameters :
318 -- IN
319 -- p_api_version_number IN NUMBER Required
320 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
321 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
322 -- p_classification_id IN NUMBER:=FND_API.G_MISS_NUM,
323 -- p_email_account_id IN NUMBER:=FND_API.G_MISS_NUM,
324 -- p_classification IN VARCHAR2:=FND_API.G_MISS_CHAR,
325 --
326 -- OUT
327 -- x_return_status OUT VARCHAR2
328 -- x_msg_count OUT NUMBER
329 -- x_msg_data OUT VARCHAR2
330 --
331 -- Version : 1.0
332 -- Notes :
333 --
334 -- End of comments
335 -- **********************************************************
336
337 PROCEDURE update_item (p_api_version_number IN NUMBER,
338 p_init_msg_list IN VARCHAR2 ,
339 p_commit IN VARCHAR2 ,
340 p_classification_id IN NUMBER,
341 p_email_account_id IN NUMBER,
342 p_classification IN VARCHAR2,
343 p_ATTRIBUTE1 VARCHAR2,
344 p_ATTRIBUTE2 VARCHAR2,
345 p_ATTRIBUTE3 VARCHAR2,
346 p_ATTRIBUTE4 VARCHAR2,
347 p_ATTRIBUTE5 VARCHAR2,
348 p_ATTRIBUTE6 VARCHAR2,
349 p_ATTRIBUTE7 VARCHAR2,
350 p_ATTRIBUTE8 VARCHAR2,
351 p_ATTRIBUTE9 VARCHAR2,
352 p_ATTRIBUTE10 VARCHAR2,
353 p_ATTRIBUTE11 VARCHAR2,
357 p_ATTRIBUTE15 VARCHAR2,
354 p_ATTRIBUTE12 VARCHAR2,
355 p_ATTRIBUTE13 VARCHAR2,
356 p_ATTRIBUTE14 VARCHAR2,
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_msg_count OUT NOCOPY NUMBER,
360 x_msg_data OUT NOCOPY VARCHAR2
361 )is
362
363 l_api_name VARCHAR2(255):='update_item';
364 l_api_version_number NUMBER:=1.0;
365
366 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
367 l_LAST_UPDATE_DATE DATE:=SYSDATE;
368 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
369
370 IEM_DUPLICATE_CLASS EXCEPTION;
371 PRAGMA EXCEPTION_INIT(IEM_DUPLICATE_CLASS, -00001);
372
373 BEGIN
374 -- Standard Start of API savepoint
375 SAVEPOINT update_item_PVT;
376
377 -- Standard call to check for call compatibility.
378 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
379 p_api_version_number,
380 l_api_name,
381 G_PKG_NAME)
382 THEN
383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384 END IF;
385 -- Initialize message list if p_init_msg_list is set to TRUE.
386 IF FND_API.to_Boolean( p_init_msg_list )
387 THEN
388 FND_MSG_PUB.initialize;
389 END IF;
390 -- Initialize API return status to SUCCESS
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392 UPDATE IEM_CLASSIFICATIONS
393 SET CLASSIFICATION=decode(p_classification,FND_API.G_MISS_CHAR,null,null,CLASSIFICATION,p_CLASSIFICATION),
394 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
395 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
396 LAST_UPDATE_LOGIN =decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
397 EMAIL_ACCOUNT_ID =decode(p_email_account_id,FND_API.G_MISS_NUM,EMAIL_ACCOUNT_ID,p_email_account_id),
398 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE1, p_ATTRIBUTE1),
399 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE2, p_ATTRIBUTE2),
400 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE3, p_ATTRIBUTE3),
401 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE4, p_ATTRIBUTE4),
402 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, null,null,ATTRIBUTE5, p_ATTRIBUTE5),
403 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE6, p_ATTRIBUTE6),
404 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE7, p_ATTRIBUTE7),
405 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE8, p_ATTRIBUTE8),
406 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE9, p_ATTRIBUTE9),
407 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE10, p_ATTRIBUTE10),
408 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE11, p_ATTRIBUTE11),
409 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE12, p_ATTRIBUTE12),
410 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE13, p_ATTRIBUTE13),
411 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE14, p_ATTRIBUTE14),
412 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE15, p_ATTRIBUTE15)
413 WHERE CLASSIFICATION_ID=p_classification_id;
414
415 -- Standard Check Of p_commit.
416 IF FND_API.To_Boolean(p_commit) THEN
417 COMMIT WORK;
418 END IF;
419 -- Standard callto get message count and if count is 1, get message info.
420 FND_MSG_PUB.Count_And_Get
421 ( p_count => x_msg_count,
422 p_data => x_msg_data
423 );
424 EXCEPTION
425 WHEN FND_API.G_EXC_ERROR THEN
426 ROLLBACK TO update_item_PVT;
427 x_return_status := FND_API.G_RET_STS_ERROR ;
428 FND_MSG_PUB.Count_And_Get
429 ( p_count => x_msg_count,
430 p_data => x_msg_data
431 );
432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
433 ROLLBACK TO update_item_PVT;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
435 FND_MSG_PUB.Count_And_Get
436 ( p_count => x_msg_count,
437 p_data => x_msg_data
438 );
439 WHEN IEM_DUPLICATE_CLASS THEN
440 ROLLBACK TO update_item_PVT;
441 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_CLASSIFICATION');
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
444 THEN
445 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
446 END IF;
447 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
448 WHEN OTHERS THEN
449 ROLLBACK TO update_item_PVT;
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 END;
464
465 /**************WRPR******************/
466
467 PROCEDURE create_item_wrap (p_api_version_number IN NUMBER,
468 p_init_msg_list IN VARCHAR2 ,
469 p_commit IN VARCHAR2 ,
470 p_email_account_id IN NUMBER,
471 p_classification IN VARCHAR2,
472 p_CREATED_BY NUMBER,
473 x_return_status OUT NOCOPY VARCHAR2,
474 x_msg_count OUT NOCOPY NUMBER,
475 x_msg_data OUT NOCOPY VARCHAR2
476 )is
480 l_CREATED_BY NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
477 l_api_name VARCHAR2(255):='create_item';
478 l_api_version_number NUMBER:=1.0;
479 l_cnt number;
481 l_creation_date date := SYSDATE;
482 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
483 l_LAST_UPDATE_DATE DATE:=SYSDATE;
484 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
485 l_classification VARCHAR2(100);
486 l_classification2 VARCHAR2(100);
487 IEM_DUP_CLASS EXCEPTION;
488
489 BEGIN
490 SAVEPOINT create_item_jsp;
491 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
492 p_api_version_number,
493 l_api_name,
494 G_PKG_NAME)
495 THEN
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498
499 IF FND_API.to_Boolean( p_init_msg_list )
500 THEN
501 FND_MSG_PUB.initialize;
502 END IF;
503
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- Take this out when create_item procedure handles duplicates in the exception block.
507 SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
508 CLASSIFICATION=p_classification AND rownum=1;
509
510 IF (l_cnt > 0 ) then
511 raise IEM_DUP_CLASS;
512 -- FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_CLASSIFICATION');
513 -- APP_EXCEPTION.RAISE_EXCEPTION;
514 end if;
515
516 select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
517 from dual;
518
519 /*
520 select replace (replace ( replace (p_classification, '<', '<'), '>', '>'), '"', '''')
521 from dual;
522 */
523
524 l_classification2 := rtrim(ltrim(l_classification, ' '), ' ');
525
526
527 IEM_CLASSIFICATIONS_PVT.create_item(
528 p_api_version_number =>p_api_version_number,
529 p_init_msg_list => p_init_msg_list,
530 p_commit => p_commit,
531 p_email_account_id =>p_email_account_id,
532 p_classification => l_classification,
533 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
534 p_CREATION_DATE =>SYSDATE,
535 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
536 p_LAST_UPDATE_DATE =>SYSDATE,
537 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
538 p_ATTRIBUTE1 =>null,
539 p_ATTRIBUTE2 =>null,
540 p_ATTRIBUTE3 =>null,
541 p_ATTRIBUTE4 =>null,
542 p_ATTRIBUTE5 =>null,
543 p_ATTRIBUTE6 =>null,
544 p_ATTRIBUTE7 =>null,
545 p_ATTRIBUTE8 =>null,
546 p_ATTRIBUTE9 =>null,
547 p_ATTRIBUTE10 =>null,
548 p_ATTRIBUTE11 =>null,
549 p_ATTRIBUTE12 =>null,
550 p_ATTRIBUTE13 =>null,
551 p_ATTRIBUTE14 =>null,
552 p_ATTRIBUTE15 =>null,
553 x_return_status =>x_return_status,
554 x_msg_count => x_msg_count,
555 x_msg_data => x_msg_data);
556
557 IF FND_API.To_Boolean(p_commit) THEN
558 COMMIT WORK;
559 END IF;
560
561 FND_MSG_PUB.Count_And_Get
562 ( p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565 EXCEPTION
566 WHEN IEM_DUP_CLASS THEN
567 ROLLBACK TO create_item_jsp;
568 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_CLASSIFICATION');
569 FND_MSG_PUB.Add;
570 x_return_status := FND_API.G_RET_STS_ERROR ;
571 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
572
573 WHEN FND_API.G_EXC_ERROR THEN
574 ROLLBACK TO create_item_jsp;
575 x_return_status := FND_API.G_RET_STS_ERROR ;
576 FND_MSG_PUB.Count_And_Get
577 ( p_count => x_msg_count,
578 p_data => x_msg_data
579 );
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 ROLLBACK TO create_item_jsp;
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
583 FND_MSG_PUB.Count_And_Get
584 ( p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587 WHEN OTHERS THEN
588 ROLLBACK TO create_item_jsp;
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 IF FND_MSG_PUB.Check_Msg_Level
591 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
592 THEN
593 FND_MSG_PUB.Add_Exc_Msg
594 ( G_PKG_NAME ,
595 l_api_name
596 );
597 END IF;
598 FND_MSG_PUB.Count_And_Get
599 ( p_count => x_msg_count ,
600 p_data => x_msg_data
601 );
602
603 end;
604
605
606 PROCEDURE create_item_wrap_sss (p_api_version_number IN NUMBER,
607 p_init_msg_list IN VARCHAR2 ,
608 p_commit IN VARCHAR2 ,
609 p_email_account_id IN NUMBER,
610 p_classification IN VARCHAR2,
611 p_CREATED_BY NUMBER,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER,
614 x_msg_data OUT NOCOPY VARCHAR2
615 )is
616 l_api_name VARCHAR2(255):='create_item';
617 l_api_version_number NUMBER:=1.0;
618 l_cnt number;
619 l_CREATED_BY NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
620 l_creation_date date := SYSDATE;
621 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
622 l_LAST_UPDATE_DATE DATE:=SYSDATE;
623 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
627
624 l_classification VARCHAR2(100);
625 l_classification2 VARCHAR2(100);
626 IEM_DUP_CLASS EXCEPTION;
628 BEGIN
629 SAVEPOINT create_item_jsp;
630 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
631 p_api_version_number,
632 l_api_name,
633 G_PKG_NAME)
634 THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END IF;
637
638 IF FND_API.to_Boolean( p_init_msg_list )
639 THEN
640 FND_MSG_PUB.initialize;
641 END IF;
642
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645 -- Take this out when create_item procedure handles duplicates in the exception block.
646 SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
647 CLASSIFICATION=p_classification AND rownum=1;
648
649 IF (l_cnt > 0 ) then
650 raise IEM_DUP_CLASS;
651 -- FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_CLASSIFICATION');
652 -- APP_EXCEPTION.RAISE_EXCEPTION;
653 end if;
654
655 select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
656 from dual;
657
658 /*
659 select replace (replace ( replace (p_classification, '<', '<'), '>', '>'), '"', '''')
660 from dual;
661 */
662
663 l_classification2 := rtrim(ltrim(l_classification, ' '), ' ');
664
665
666 IEM_CLASSIFICATIONS_PVT.create_item(
667 p_api_version_number =>p_api_version_number,
668 p_init_msg_list => p_init_msg_list,
669 p_commit => p_commit,
670 p_email_account_id =>p_email_account_id,
671 p_classification => l_classification,
672 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
673 p_CREATION_DATE =>SYSDATE,
674 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
675 p_LAST_UPDATE_DATE =>SYSDATE,
676 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
677 p_ATTRIBUTE1 =>null,
678 p_ATTRIBUTE2 =>null,
679 p_ATTRIBUTE3 =>null,
680 p_ATTRIBUTE4 =>null,
681 p_ATTRIBUTE5 =>null,
682 p_ATTRIBUTE6 =>null,
683 p_ATTRIBUTE7 =>null,
684 p_ATTRIBUTE8 =>null,
685 p_ATTRIBUTE9 =>null,
686 p_ATTRIBUTE10 =>null,
687 p_ATTRIBUTE11 =>null,
688 p_ATTRIBUTE12 =>null,
689 p_ATTRIBUTE13 =>null,
690 p_ATTRIBUTE14 =>null,
691 p_ATTRIBUTE15 =>null,
692 x_return_status =>x_return_status,
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data);
695
696 IF FND_API.To_Boolean(p_commit) THEN
697 COMMIT WORK;
698 END IF;
699
700 FND_MSG_PUB.Count_And_Get
701 ( p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704 EXCEPTION
705 WHEN IEM_DUP_CLASS THEN
706 ROLLBACK TO create_item_jsp;
707 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_INTENT');
708 FND_MSG_PUB.Add;
709 x_return_status := FND_API.G_RET_STS_ERROR ;
710 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
711
712 WHEN FND_API.G_EXC_ERROR THEN
713 ROLLBACK TO create_item_jsp;
714 x_return_status := FND_API.G_RET_STS_ERROR ;
715 FND_MSG_PUB.Count_And_Get
716 ( p_count => x_msg_count,
717 p_data => x_msg_data
718 );
719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
720 ROLLBACK TO create_item_jsp;
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
722 FND_MSG_PUB.Count_And_Get
723 ( p_count => x_msg_count,
724 p_data => x_msg_data
725 );
726 WHEN OTHERS THEN
727 ROLLBACK TO create_item_jsp;
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 IF FND_MSG_PUB.Check_Msg_Level
730 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
731 THEN
732 FND_MSG_PUB.Add_Exc_Msg
733 ( G_PKG_NAME ,
734 l_api_name
735 );
736 END IF;
737 FND_MSG_PUB.Count_And_Get
738 ( p_count => x_msg_count ,
739 p_data => x_msg_data
740 );
741
742 end;
743
744 PROCEDURE delete_item_wrap
745 (p_api_version_number IN NUMBER,
746 P_init_msg_list IN VARCHAR2 ,
747 p_commit IN VARCHAR2 ,
748 p_clas_ids_tbl IN jtf_varchar2_Table_100,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2)
752 IS
753 i INTEGER;
754 l_api_name varchar2(30):='delete_item_batch';
755 l_api_version_number number:=1.0;
756 BEGIN
757
758 --Standard Savepoint
759 SAVEPOINT delete_item_wrap;
760 -- Standard call to check for call compatibility.
761 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
762 p_api_version_number,
763 l_api_name,
764 G_PKG_NAME)
765 THEN
766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
767 END IF;
768
769 --Initialize the message list if p_init_msg_list is set to TRUE
770 If FND_API.to_Boolean(p_init_msg_list) THEN
771 FND_MSG_PUB.initialize;
772 END IF;
773
774 --Initialize API status return
775 x_return_status := FND_API.G_RET_STS_SUCCESS;
776
777 --Actual API starts here
778 FOR i IN p_clas_ids_tbl.FIRST..p_clas_ids_tbl.LAST
779 LOOP
780 DELETE
781 FROM IEM_THEMES
785 WHERE classification_id = p_clas_ids_tbl(i);
782 WHERE classification_id = p_clas_ids_tbl(i);
783 DELETE
784 FROM IEM_CLASSIFICATIONS
786 END LOOP;
787
788 IF SQL%NOTFOUND THEN
789 FND_MESSAGE.SET_NAME('IEM', 'IEM_EXP_INVALID_ACCOUNT');
790 FND_MSG_PUB.ADD;
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793
794 --Standard check of p_commit
795 IF FND_API.to_Boolean(p_commit) THEN
796 COMMIT WORK;
797 END IF;
798
799 -- Standard callto get message count and if count is 1, get message info.
800 FND_MSG_PUB.Count_And_Get
801 ( p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804 EXCEPTION
805 WHEN FND_API.G_EXC_ERROR THEN
806 ROLLBACK TO delete_item_wrap;
807 x_return_status := FND_API.G_RET_STS_ERROR ;
808 FND_MSG_PUB.Count_And_Get
809 ( p_count => x_msg_count,
810 p_data => x_msg_data
811 );
812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
813 ROLLBACK TO delete_item_wrap;
814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
815 FND_MSG_PUB.Count_And_Get
816 ( p_count => x_msg_count,
817 p_data => x_msg_data
818 );
819 WHEN OTHERS THEN
820 ROLLBACK TO delete_item_wrap;
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 IF FND_MSG_PUB.Check_Msg_Level
823 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
824 THEN
825 FND_MSG_PUB.Add_Exc_Msg
826 ( G_PKG_NAME ,
827 l_api_name
828 );
829 END IF;
830 FND_MSG_PUB.Count_And_Get
831 ( p_count => x_msg_count ,
832 p_data => x_msg_data
833 );
834 --Standard call to get message count and message info
835 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
836 p_data => x_msg_data);
837 END delete_item_wrap;
838 PROCEDURE delete_item_wrap_sss
839 (p_api_version_number IN NUMBER,
840 P_init_msg_list IN VARCHAR2 ,
841 p_commit IN VARCHAR2 ,
842 p_clas_ids_tbl IN jtf_varchar2_Table_100,
843 x_return_status OUT NOCOPY VARCHAR2,
844 x_msg_count OUT NOCOPY NUMBER,
845 x_msg_data OUT NOCOPY VARCHAR2)
846 IS
847 i INTEGER;
848 l_api_name varchar2(30):='delete_item_batch_sss';
849 l_api_version_number number:=1.0;
850 l_status varchar2(10);
851 l_class_id number;
852 l_email_account_id number;
853 BEGIN
854
855 --Standard Savepoint
856 SAVEPOINT delete_item_wrap;
857 -- Standard call to check for call compatibility.
858 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
859 p_api_version_number,
860 l_api_name,
861 G_PKG_NAME)
862 THEN
863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
864 END IF;
865
866 --Initialize the message list if p_init_msg_list is set to TRUE
867 If FND_API.to_Boolean(p_init_msg_list) THEN
868 FND_MSG_PUB.initialize;
869 END IF;
870
871 --Initialize API status return
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873
874 --Actual API starts here
875 FOR j in p_clas_ids_tbl.FIRST..p_clas_ids_tbl.LAST LOOP
876 l_class_id:=p_clas_ids_tbl(j);
877 EXIT;
878 END LOOP;
879 select email_account_id into l_email_account_id
880 from iem_classifications
881 where classification_id=l_class_id;
882 FOR i IN p_clas_ids_tbl.FIRST..p_clas_ids_tbl.LAST
883 LOOP
884 DELETE
885 FROM IEM_THEMES
886 WHERE classification_id = p_clas_ids_tbl(i);
887 DELETE
888 FROM IEM_CLASSIFICATIONS
889 WHERE classification_id = p_clas_ids_tbl(i);
890 END LOOP;
891 delete from iem_account_intent_docs
892 where classification_id not in
893 (select classification_id from iem_classifications);
894 delete from iem_theme_docs
895 where account_intent_doc_id not in
896 (select account_intent_doc_id from iem_account_intent_docs);
897 iem_themes_pvt.calculate_weight (l_email_account_id,
898 'Q' ,
899 l_status );
900 iem_themes_pvt.calculate_weight (l_email_account_id,
901 'R' ,
902 l_status );
903
904 --Standard check of p_commit
905 IF FND_API.to_Boolean(p_commit) THEN
906 COMMIT WORK;
907 END IF;
908
909 -- Standard callto get message count and if count is 1, get message info.
910 FND_MSG_PUB.Count_And_Get
911 ( p_count => x_msg_count,
912 p_data => x_msg_data
913 );
914 EXCEPTION
915 WHEN FND_API.G_EXC_ERROR THEN
916 ROLLBACK TO delete_item_wrap;
917 x_return_status := FND_API.G_RET_STS_ERROR ;
918 FND_MSG_PUB.Count_And_Get
919 ( p_count => x_msg_count,
920 p_data => x_msg_data
921 );
922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
923 ROLLBACK TO delete_item_wrap;
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
925 FND_MSG_PUB.Count_And_Get
926 ( p_count => x_msg_count,
927 p_data => x_msg_data
928 );
929 WHEN OTHERS THEN
930 ROLLBACK TO delete_item_wrap;
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 IF FND_MSG_PUB.Check_Msg_Level
933 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
934 THEN
935 FND_MSG_PUB.Add_Exc_Msg
936 ( G_PKG_NAME ,
937 l_api_name
938 );
939 END IF;
940 FND_MSG_PUB.Count_And_Get
944 --Standard call to get message count and message info
941 ( p_count => x_msg_count ,
942 p_data => x_msg_data
943 );
945 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
946 p_data => x_msg_data);
947 END delete_item_wrap_sss;
948
949 PROCEDURE update_item_wrap (p_api_version_number IN NUMBER,
950 p_init_msg_list IN VARCHAR2 ,
951 p_commit IN VARCHAR2 ,
952 p_classification_id IN NUMBER,
953 p_email_account_id IN NUMBER,
954 p_classification IN VARCHAR2,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2
958 )is
959 l_api_name VARCHAR2(255):='update_item';
960 l_api_version_number NUMBER:=1.0;
961 l_CREATED_BY NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
962 l_creation_date date := SYSDATE;
963 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
964 l_LAST_UPDATE_DATE DATE:=SYSDATE;
965 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
966 l_classification VARCHAR2(100);
967 l_classification2 VARCHAR2(100);
968 l_cnt NUMBER := 0;
969
970 BEGIN
971 -- Standard Start of API savepoint
972 SAVEPOINT update_item_PVT;
973 -- Standard call to check for call compatibility.
974 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
975 p_api_version_number,
976 l_api_name,
977 G_PKG_NAME)
978 THEN
979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 END IF;
981 -- Initialize message list if p_init_msg_list is set to TRUE.
982 IF FND_API.to_Boolean( p_init_msg_list )
983 THEN
984 FND_MSG_PUB.initialize;
985 END IF;
986 -- Initialize API return status to SUCCESS
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
990 from dual;
991
992 l_classification2 := rtrim(ltrim(l_classification, ' '), ' ');
993
994 -- Take this out when create_item procedure handles duplicates in the exception block.
995 SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
996 CLASSIFICATION=l_classification2 AND rownum=1;
997
998 IF (l_cnt > 0 ) then
999 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_CLASSIFICATION');
1000 APP_EXCEPTION.RAISE_EXCEPTION;
1001 end if;
1002
1003
1004 IEM_CLASSIFICATIONS_PVT.update_item(
1005 p_api_version_number =>p_api_version_number,
1006 p_init_msg_list => p_init_msg_list,
1007 p_commit => p_commit,
1008 p_classification_id =>p_classification_id,
1009 p_email_account_id =>p_email_account_id,
1010 p_classification => l_classification,
1011 p_ATTRIBUTE1 =>null,
1012 p_ATTRIBUTE2 =>null,
1013 p_ATTRIBUTE3 =>null,
1014 p_ATTRIBUTE4 =>null,
1015 p_ATTRIBUTE5 =>null,
1016 p_ATTRIBUTE6 =>null,
1017 p_ATTRIBUTE7 =>null,
1018 p_ATTRIBUTE8 =>null,
1019 p_ATTRIBUTE9 =>null,
1020 p_ATTRIBUTE10 =>null,
1021 p_ATTRIBUTE11 =>null,
1022 p_ATTRIBUTE12 =>null,
1023 p_ATTRIBUTE13 =>null,
1024 p_ATTRIBUTE14 =>null,
1025 p_ATTRIBUTE15 =>null,
1026 x_return_status =>x_return_status,
1027 x_msg_count => x_msg_count,
1028 x_msg_data => x_msg_data);
1029
1030
1031 -- Standard Check Of p_commit.
1032 IF FND_API.To_Boolean(p_commit) THEN
1033 COMMIT WORK;
1034 END IF;
1035 -- Standard callto get message count and if count is 1, get message info.
1036 FND_MSG_PUB.Count_And_Get
1037 ( p_count => x_msg_count,
1038 p_data => x_msg_data
1039 );
1040 EXCEPTION
1041 WHEN FND_API.G_EXC_ERROR THEN
1042 ROLLBACK TO update_item_PVT;
1043 x_return_status := FND_API.G_RET_STS_ERROR ;
1044 FND_MSG_PUB.Count_And_Get
1045 ( p_count => x_msg_count,
1046 p_data => x_msg_data
1047 );
1048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1049 ROLLBACK TO update_item_PVT;
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1051 FND_MSG_PUB.Count_And_Get
1052 ( p_count => x_msg_count,
1053 p_data => x_msg_data
1054 );
1055 WHEN OTHERS THEN
1056 ROLLBACK TO update_item_PVT;
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 IF FND_MSG_PUB.Check_Msg_Level
1059 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1060 THEN
1061 FND_MSG_PUB.Add_Exc_Msg
1062 ( G_PKG_NAME ,
1063 l_api_name
1064 );
1065 END IF;
1066 FND_MSG_PUB.Count_And_Get
1067 ( p_count => x_msg_count ,
1068 p_data => x_msg_data
1069 );
1070 END;
1071 PROCEDURE update_item_wrap_sss (p_api_version_number IN NUMBER,
1072 p_init_msg_list IN VARCHAR2 ,
1073 p_commit IN VARCHAR2 ,
1074 p_classification_id IN NUMBER,
1075 p_email_account_id IN NUMBER,
1076 p_classification IN VARCHAR2,
1077 x_return_status OUT NOCOPY VARCHAR2,
1078 x_msg_count OUT NOCOPY NUMBER,
1079 x_msg_data OUT NOCOPY VARCHAR2
1080 )is
1081 l_api_name VARCHAR2(255):='update_item';
1082 l_api_version_number NUMBER:=1.0;
1083 l_CREATED_BY NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
1084 l_creation_date date := SYSDATE;
1088 l_classification VARCHAR2(100);
1085 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
1086 l_LAST_UPDATE_DATE DATE:=SYSDATE;
1087 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
1089 l_classification2 VARCHAR2(100);
1090 l_status varchar2(10);
1091 l_cnt NUMBER := 0;
1092 l_email_account_id number;
1093 t_email_account_id number;
1094 l_classification_id number;
1095 DUPLICATE_INTENT EXCEPTION;
1096
1097 BEGIN
1098 -- Standard Start of API savepoint
1099 SAVEPOINT update_item_PVT;
1100 -- Standard call to check for call compatibility.
1101 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
1102 p_api_version_number,
1103 l_api_name,
1104 G_PKG_NAME)
1105 THEN
1106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107 END IF;
1108 -- Initialize message list if p_init_msg_list is set to TRUE.
1109 IF FND_API.to_Boolean( p_init_msg_list )
1110 THEN
1111 FND_MSG_PUB.initialize;
1112 END IF;
1113 -- Initialize API return status to SUCCESS
1114 x_return_status := FND_API.G_RET_STS_SUCCESS;
1115
1116 select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
1117 from dual;
1118
1119 l_classification2 := rtrim(ltrim(l_classification, ' '), ' ');
1120
1121 -- Take this out when create_item procedure handles duplicates in the exception block.
1122 SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
1123 upper(CLASSIFICATION)=upper(l_classification2) AND rownum=1;
1124
1125 IF (l_cnt > 0 ) then
1126 raise DUPLICATE_INTENT;
1127 end if;
1128 select email_account_id into l_email_account_id
1129 from iem_classifications
1130 where classification_id=p_classification_id;
1131
1132 IEM_CLASSIFICATIONS_PVT.update_item(
1133 p_api_version_number =>p_api_version_number,
1134 p_init_msg_list => p_init_msg_list,
1135 p_commit => p_commit,
1136 p_classification_id =>p_classification_id,
1137 p_email_account_id =>p_email_account_id,
1138 p_classification => l_classification,
1139 p_ATTRIBUTE1 =>null,
1140 p_ATTRIBUTE2 =>null,
1141 p_ATTRIBUTE3 =>null,
1142 p_ATTRIBUTE4 =>null,
1143 p_ATTRIBUTE5 =>null,
1144 p_ATTRIBUTE6 =>null,
1145 p_ATTRIBUTE7 =>null,
1146 p_ATTRIBUTE8 =>null,
1147 p_ATTRIBUTE9 =>null,
1148 p_ATTRIBUTE10 =>null,
1149 p_ATTRIBUTE11 =>null,
1150 p_ATTRIBUTE12 =>null,
1151 p_ATTRIBUTE13 =>null,
1152 p_ATTRIBUTE14 =>null,
1153 p_ATTRIBUTE15 =>null,
1154 x_return_status =>x_return_status,
1155 x_msg_count => x_msg_count,
1156 x_msg_data => x_msg_data);
1157 IF l_email_account_id <>p_email_account_id THEN
1158
1159 -- In this case need to recalculate the score for both email accounts.
1160
1161 update iem_account_intent_docs
1162 set email_account_id=p_email_account_id
1163 where classification_id=p_classification_id;
1164 t_email_account_id:=l_email_account_id;
1165 iem_themes_pvt.calculate_weight (t_email_account_id ,
1166 'Q' ,
1167 l_status );
1168 iem_themes_pvt.calculate_weight (t_email_account_id ,
1169 'R' ,
1170 l_status );
1171 t_email_account_id:=p_email_account_id;
1172 iem_themes_pvt.calculate_weight (p_email_account_id ,
1173 'Q',
1174 l_status );
1175 iem_themes_pvt.calculate_weight (p_email_account_id ,
1176 'R',
1177 l_status );
1178 END IF;
1179
1180 -- Standard Check Of p_commit.
1181 IF FND_API.To_Boolean(p_commit) THEN
1182 COMMIT WORK;
1183 END IF;
1184 -- Standard callto get message count and if count is 1, get message info.
1185 FND_MSG_PUB.Count_And_Get
1186 ( p_count => x_msg_count,
1187 p_data => x_msg_data
1188 );
1189 EXCEPTION
1190 WHEN DUPLICATE_INTENT THEN
1191 FND_MESSAGE.SET_NAME('IEM','IEM_DUPLICATE_INTENT');
1192 FND_MSG_PUB.Add;
1193 x_return_status := FND_API.G_RET_STS_ERROR ;
1194 FND_MSG_PUB.Count_And_Get
1195 ( p_count => x_msg_count,
1196 p_data => x_msg_data
1197 );
1198
1199 WHEN FND_API.G_EXC_ERROR THEN
1200 ROLLBACK TO update_item_PVT;
1201 x_return_status := FND_API.G_RET_STS_ERROR ;
1202 FND_MSG_PUB.Count_And_Get
1203 ( p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1207 ROLLBACK TO update_item_PVT;
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1209 FND_MSG_PUB.Count_And_Get
1210 ( p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213 WHEN OTHERS THEN
1214 ROLLBACK TO update_item_PVT;
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216 IF FND_MSG_PUB.Check_Msg_Level
1217 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1218 THEN
1219 FND_MSG_PUB.Add_Exc_Msg
1220 ( G_PKG_NAME ,
1221 l_api_name
1222 );
1223 END IF;
1224 FND_MSG_PUB.Count_And_Get
1225 ( p_count => x_msg_count ,
1226 p_data => x_msg_data
1227 );
1228 END;
1229 -- This API is called by the postman process while creating classifications
1230 -- This is incorporated in 11.5.6 New Flow Arch.
1231
1232 PROCEDURE create_item_pm (p_api_version_number IN NUMBER,
1233 p_email_account_id IN NUMBER,
1237 p_CREATED_BY NUMBER,
1234 p_classification IN VARCHAR2,
1235 p_query_response IN VARCHAR2,
1236 x_doc_seq_num OUT NOCOPY NUMBER,
1238 p_CREATION_DATE DATE,
1239 p_LAST_UPDATED_BY NUMBER ,
1240 p_LAST_UPDATE_DATE DATE,
1241 p_LAST_UPDATE_LOGIN NUMBER,
1242 x_return_status OUT NOCOPY VARCHAR2,
1243 x_msg_count OUT NOCOPY NUMBER,
1244 x_msg_data OUT NOCOPY VARCHAR2) IS
1245 l_cnt number;
1246 l_seq number;
1247 l_class_id number;
1248 l_classification_id number;
1249 l_doc_count number;
1250 l_doc_seq_no number;
1251 l_status varchar2(10);
1252 DOC_EXCEP EXCEPTION;
1253 CLASS_EXCEP EXCEPTION;
1254 BEGIN
1255 x_return_status:='S';
1256 BEGIN
1257 SELECT classification_id into l_class_id
1258 from iem_classifications
1259 WHERE EMAIL_ACCOUNT_ID=p_email_account_id
1260 AND upper(CLASSIFICATION)=upper(p_classification) ;
1261 IEM_INTENT_DOCS_PVT.create_item(
1262 p_classification_id=>l_class_id,
1263 p_email_account_id =>p_email_account_id,
1264 p_query_response =>p_query_response,
1265 x_doc_seq_no =>l_doc_seq_no,
1266 x_return_status=>l_status);
1267 IF l_status='E' THEN
1268 raise DOC_EXCEP;
1269 END IF;
1270 EXCEPTION WHEN NO_DATA_FOUND THEN
1271 select iem_classifications_s1.nextval into l_seq from dual;
1272 INSERT INTO IEM_CLASSIFICATIONS
1273 (
1274 CLASSIFICATION_ID,
1275 EMAIL_ACCOUNT_ID ,
1276 CLASSIFICATION ,
1277 CREATED_BY,
1278 CREATION_DATE,
1279 LAST_UPDATED_BY,
1280 LAST_UPDATE_DATE,
1281 LAST_UPDATE_LOGIN)
1282 VALUES
1283 (l_seq,
1284 p_email_account_id,
1285 p_classification,
1286 p_CREATED_BY,
1287 p_creation_date,
1288 p_LAST_UPDATED_BY,
1289 p_LAST_UPDATE_DATE,
1290 p_LAST_UPDATE_LOGIN);
1291 IF l_status='E' THEN
1292 raise CLASS_EXCEP;
1293 END IF;
1294 IEM_INTENT_DOCS_PVT.create_item(
1295 p_classification_id=>l_seq,
1296 p_email_account_id =>p_email_account_id,
1297 p_query_response =>p_query_response,
1298 x_doc_seq_no =>l_doc_seq_no,
1299 x_return_status=>l_status);
1300 IF l_status='E' THEN
1301 raise DOC_EXCEP;
1302 END IF;
1303 END;
1304 x_doc_seq_num:=l_doc_seq_no;
1305 EXCEPTION WHEN DOC_EXCEP THEN
1306 x_return_Status:='E';
1307 WHEN CLASS_EXCEP THEN
1308 x_return_Status:='E';
1309 WHEN OTHERS THEN
1310 x_return_Status:='E';
1311
1312 END;
1313
1314 END IEM_CLASSIFICATIONS_PVT;