[Home] [Help]
PACKAGE BODY: APPS.IEM_EML_CLASSIFICATIONS_PVT
Source
1 PACKAGE BODY IEM_EML_CLASSIFICATIONS_PVT as
2 /* $Header: iemveclb.pls 120.0 2005/06/02 14:04:19 appldev noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EML_CLASSIFICATIONS_PVT ';
5 -- Start of Comments
6 -- API name : create_item
7 -- Type : Private
8 -- Function : This procedure create a record in the table IEM_EMAIL_CLASSIFICATIONS
9 -- Pre-reqs : None.
10 -- Parameters :
11 -- IN
12 -- p_api_version_number IN NUMBER Required
13 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
14 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
15 -- p_email_account_id IN NUMBER,
16 -- p_classification_id IN NUMBER,
17 -- p_score IN NUMBER,
18 -- p_message_id IN NUMBER,
19 -- p_class_string IN varchar2,
20 --
21 -- OUT
22 -- x_return_status OUT VARCHAR2
23 -- x_msg_count OUT NUMBER
24 -- x_msg_data OUT VARCHAR2
25 --
26 -- Version : 1.0
27 -- Notes :
28 --
29 -- End of comments
30 -- **********************************************************
31
32 PROCEDURE create_item (p_api_version_number IN NUMBER,
33 p_init_msg_list IN VARCHAR2 ,
34 p_commit IN VARCHAR2 ,
35 p_email_account_id IN NUMBER,
36 p_classification_id IN NUMBER,
37 p_score IN NUMBER,
38 p_message_id IN NUMBER,
39 p_CREATED_BY NUMBER,
40 p_CREATION_DATE DATE,
41 p_LAST_UPDATED_BY NUMBER,
42 p_LAST_UPDATE_DATE DATE,
43 p_LAST_UPDATE_LOGIN NUMBER ,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2)
47 IS
48 l_api_name VARCHAR2(255):='create_item';
49 l_api_version_number NUMBER:=1.0;
50 l_count NUMBER;
51
52 BEGIN
53 -- Standard Start of API savepoint
54 -- SAVEPOINT create_item_PVT;
55 -- Standard call to check for call compatibility.
56 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
57 p_api_version_number,
58 l_api_name,
59 G_PKG_NAME)
60 THEN
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63 -- Initialize message list if p_init_msg_list is set to TRUE.
64 IF FND_API.to_Boolean( p_init_msg_list )
65 THEN
66 FND_MSG_PUB.initialize;
67 END IF;
68 -- Initialize API return status to SUCCESS
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70 select count(*) into l_count from iem_email_classifications
71 where message_id=p_message_id
72 and classification_id=p_classification_id;
73 IF l_count=0 then
74 INSERT INTO IEM_EMAIL_CLASSIFICATIONS
75 (
76 MESSAGE_ID,
77 EMAIL_ACCOUNT_ID ,
78 CLASSIFICATION_ID ,
79 SCORE ,
80 CREATED_BY ,
81 CREATION_DATE ,
82 LAST_UPDATED_BY ,
83 LAST_UPDATE_DATE ,
84 LAST_UPDATE_LOGIN
85 )
86 VALUES
87 (
88 p_message_id,
89 p_email_account_id,
90 p_classification_id,
91 p_score,
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 );
98 END IF;
99 -- Standard Check Of p_commit.
100 IF FND_API.To_Boolean(p_commit) THEN
101 COMMIT WORK;
102 END IF;
103 -- Standard callto get message count and if count is 1, get message info.
104 FND_MSG_PUB.Count_And_Get
105 ( p_count => x_msg_count,
106 p_data => x_msg_data
107 );
108 EXCEPTION
109 WHEN FND_API.G_EXC_ERROR THEN
110 -- ROLLBACK TO update_item_PVT;
111 x_return_status := FND_API.G_RET_STS_ERROR ;
112 FND_MSG_PUB.Count_And_Get
113 ( p_count => x_msg_count,
114 p_data => x_msg_data
115 );
116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
117 -- ROLLBACK TO update_item_PVT;
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
119 FND_MSG_PUB.Count_And_Get
120 ( p_count => x_msg_count,
121 p_data => x_msg_data
122 );
123 WHEN OTHERS THEN
124 -- ROLLBACK TO update_item_PVT;
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 IF FND_MSG_PUB.Check_Msg_Level
127 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
128 THEN
129 FND_MSG_PUB.Add_Exc_Msg
130 ( G_PKG_NAME ,
131 l_api_name
132 );
133 END IF;
134 FND_MSG_PUB.Count_And_Get
135 ( p_count => x_msg_count ,
136 p_data => x_msg_data
137 );
138
139 END;
140
141
142 -- Start of Comments
143 -- API name : delete_item
144 -- Type : Private
145 -- Function : This procedure delete a record in the table IEM_EMAIL_CLASSIFICATIONS
146 -- Pre-reqs : None.
147 -- Parameters :
148 -- IN
149 -- p_api_version_number IN NUMBER Required
150 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
151 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
152 -- p_email_account_id in number:=FND_API.G_MISS_NUM,
153 -- p_classification_id IN NUMBER:=FND_API.G_MISS_NUM,
154
155 -- OUT
156 -- x_return_status OUT VARCHAR2
157 -- x_msg_count OUT NUMBER
158 -- x_msg_data OUT VARCHAR2
159 --
160 -- Version : 1.0
161 -- Notes :
162 --
163 -- End of comments
164 -- **********************************************************
165
166 PROCEDURE delete_item (p_api_version_number IN NUMBER,
167 p_init_msg_list IN VARCHAR2 ,
168 p_commit IN VARCHAR2 ,
169 p_email_account_id IN NUMBER,
170 p_classification_id IN NUMBER,
171 p_message_id IN NUMBER,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2
175 ) is
176 l_api_name VARCHAR2(255):='delete_item';
177 l_api_version_number NUMBER:=1.0;
178 l_grp_cnt NUMBER;
179
180 BEGIN
181 -- Standard Start of API savepoint
182 --SAVEPOINT delete_item_PVT;
183 -- Standard call to check for call compatibility.
184 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
185 p_api_version_number,
186 l_api_name,
187 G_PKG_NAME)
188 THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191 -- Initialize message list if p_init_msg_list is set to TRUE.
192 IF FND_API.to_Boolean( p_init_msg_list )
193 THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196 -- Initialize API return status to SUCCESS
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198 DELETE FROM IEM_EMAIL_CLASSIFICATIONS
199 where message_id=p_message_id
200 and classification_id=p_classification_id
201 and email_account_id=p_email_account_id;
202 -- Standard Check Of p_commit.
203 IF FND_API.To_Boolean(p_commit) THEN
204 COMMIT WORK;
205 END IF;
206 -- Standard callto get message count and if count is 1, get message info.
207 FND_MSG_PUB.Count_And_Get
208 ( p_count => x_msg_count,
209 p_data => x_msg_data
210 );
211 EXCEPTION
212 WHEN FND_API.G_EXC_ERROR THEN
213 -- ROLLBACK TO delete_item_PVT;
214 x_return_status := FND_API.G_RET_STS_ERROR ;
215 FND_MSG_PUB.Count_And_Get
216 ( p_count => x_msg_count,
217 p_data => x_msg_data
218 );
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 --ROLLBACK TO delete_item_PVT;
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
222 FND_MSG_PUB.Count_And_Get
223 ( p_count => x_msg_count,
224 p_data => x_msg_data
225 );
226 WHEN OTHERS THEN
227 --ROLLBACK TO delete_item_PVT;
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 IF FND_MSG_PUB.Check_Msg_Level
230 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
231 THEN
232 FND_MSG_PUB.Add_Exc_Msg
233 ( G_PKG_NAME ,
234 l_api_name
235 );
236 END IF;
237 FND_MSG_PUB.Count_And_Get
238 ( p_count => x_msg_count ,
239 p_data => x_msg_data
240 );
241
242 END;
243
244 PROCEDURE getClassification (p_api_version_number IN NUMBER,
245 p_init_msg_list IN VARCHAR2 ,
246 p_commit IN VARCHAR2 ,
247 p_email_account_id IN NUMBER,
248 p_message_id IN NUMBER,
249 x_Email_Classn_tbl OUT NOCOPY EMCLASS_tbl_type,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_msg_count OUT NOCOPY NUMBER,
252 x_msg_data OUT NOCOPY VARCHAR2
253 ) is
254 l_api_name VARCHAR2(255):='getClassification';
255 l_api_version_number NUMBER:=1.0;
256 l_cnt NUMBER:=1;
257
258 CURSOR class_scr IS
259 SELECT a.classification_id,b.intent,a.score
260 FROM IEM_EMAIL_CLASSIFICATIONS a,IEM_INTENTS b
261 WHERE a.classification_id=b.intent_id
262 AND a.message_id = p_message_id
263 AND a.email_account_id = p_email_account_id;
264
265 BEGIN
266 -- Standard Start of API savepoint
267 --SAVEPOINT getClassification_pvt;
268 -- Standard call to check for call compatibility.
269 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
270 p_api_version_number,
271 l_api_name,
272 G_PKG_NAME)
273 THEN
274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
275 END IF;
276 -- Initialize message list if p_init_msg_list is set to TRUE.
277 IF FND_API.to_Boolean( p_init_msg_list )
278 THEN
279 FND_MSG_PUB.initialize;
280 END IF;
281 -- Initialize API return status to SUCCESS
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283
284
285 FOR class_rec in class_scr
286 LOOP
287 x_Email_Classn_tbl(l_cnt).CLASSIFICATION_ID := class_rec.CLASSIFICATION_ID;
288 x_Email_Classn_tbl(l_cnt).CLASSIFICATION := class_rec.intent;
289 x_Email_Classn_tbl(l_cnt).SCORE := class_rec.SCORE;
290
291 l_cnt:=l_cnt+1;
292
293 END LOOP;
294
295 -- Standard Check Of p_commit.
296 IF FND_API.To_Boolean(p_commit) THEN
297 COMMIT WORK;
298 END IF;
299 -- Standard callto get message count and if count is 1, get message info.
300 FND_MSG_PUB.Count_And_Get
301 ( p_count => x_msg_count,
302 p_data => x_msg_data
303 );
304
305
306 EXCEPTION
307 WHEN FND_API.G_EXC_ERROR THEN
308 --ROLLBACK TO getclassification_pvt;
309 x_return_status := FND_API.G_RET_STS_ERROR ;
310 FND_MSG_PUB.Count_And_Get
311 ( p_count => x_msg_count,
312 p_data => x_msg_data
313 );
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 --ROLLBACK TO getclassification_pvt;
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data
320 );
321 WHEN OTHERS THEN
322 --ROLLBACK TO getclassification_pvt;
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 IF FND_MSG_PUB.Check_Msg_Level
325 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
326 THEN
327 FND_MSG_PUB.Add_Exc_Msg
328 ( G_PKG_NAME ,
329 l_api_name
330 );
331 END IF;
332 FND_MSG_PUB.Count_And_Get
333 ( p_count => x_msg_count ,
334 p_data => x_msg_data
335 );
336
337 END;
338
339 PROCEDURE getClassification (p_api_version_number IN NUMBER,
340 p_init_msg_list IN VARCHAR2 ,
341 p_commit IN VARCHAR2 ,
342 p_email_account_id IN NUMBER,
343 p_message_id IN NUMBER,
344 x_category_id OUT NOCOPY NUMBER,
345 x_Email_Classn_tbl OUT NOCOPY EMCLASS_tbl_type,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2
349 ) IS
350
351 l_api_name VARCHAR2(255):='getClassification';
352 l_api_version_number NUMBER:=1.0;
353 l_cnt NUMBER:=1;
354 l_category_map_id number;
355
356 CURSOR class_scr IS
357 SELECT a.classification_id,b.intent,a.score
358 FROM IEM_EMAIL_CLASSIFICATIONS a,IEM_INTENTS b
359 WHERE a.classification_id=b.intent_id
360 AND a.message_id = p_message_id
361 AND a.email_account_id = p_email_account_id;
362
363 BEGIN
364 -- Standard Start of API savepoint
365 --SAVEPOINT getClassification_pvt;
366 -- Standard call to check for call compatibility.
367 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
368 p_api_version_number,
369 l_api_name,
370 G_PKG_NAME)
371 THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374 -- Initialize message list if p_init_msg_list is set to TRUE.
375 IF FND_API.to_Boolean( p_init_msg_list )
376 THEN
377 FND_MSG_PUB.initialize;
378 END IF;
379 -- Initialize API return status to SUCCESS
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381 BEGIN
382 select category_map_id into l_category_map_id
383 FROM IEM_RT_PROC_EMAILS
384 WHERE MESSAGE_ID=p_message_id;
385 x_category_id:=l_category_map_id;
386 EXCEPTION WHEN OTHERS THEN
387 null;
388 END;
389 IF l_category_map_id is null THEN
390 FOR class_rec in class_scr
391 LOOP
392
393 x_Email_Classn_tbl(l_cnt).CLASSIFICATION_ID := class_rec.CLASSIFICATION_ID;
394 x_Email_Classn_tbl(l_cnt).CLASSIFICATION := class_rec.INTENT;
395 x_Email_Classn_tbl(l_cnt).SCORE := class_rec.SCORE;
396
397 l_cnt:=l_cnt+1;
398
399 END LOOP;
400 END IF;
401
402 -- Standard Check Of p_commit.
403 IF FND_API.To_Boolean(p_commit) THEN
404 COMMIT WORK;
405 END IF;
406 -- Standard callto get message count and if count is 1, get message info.
407 FND_MSG_PUB.Count_And_Get
408 ( p_count => x_msg_count,
409 p_data => x_msg_data
410 );
411
412
413 EXCEPTION
414 WHEN FND_API.G_EXC_ERROR THEN
415 --ROLLBACK TO getclassification_pvt;
416 x_return_status := FND_API.G_RET_STS_ERROR ;
417 FND_MSG_PUB.Count_And_Get
418 ( p_count => x_msg_count,
419 p_data => x_msg_data
420 );
421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
422 --ROLLBACK TO getclassification_pvt;
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
424 FND_MSG_PUB.Count_And_Get
425 ( p_count => x_msg_count,
426 p_data => x_msg_data
427 );
428 WHEN OTHERS THEN
429 --ROLLBACK TO getclassification_pvt;
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 IF FND_MSG_PUB.Check_Msg_Level
432 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
433 THEN
434 FND_MSG_PUB.Add_Exc_Msg
435 ( G_PKG_NAME ,
436 l_api_name
437 );
438 END IF;
439 FND_MSG_PUB.Count_And_Get
440 ( p_count => x_msg_count ,
441 p_data => x_msg_data
442 );
443
447 -- API name : create_item
444 END;
445
446 -- Start of Comments
448 -- Type : Private
449 -- Function : This procedure create a record in the table IEM_EMAIL_CLASSIFICATIONS
450 -- Pre-reqs : None.
451 -- Parameters :
452 -- IN
453 -- p_api_version_number IN NUMBER Required
454 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
455 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
456 -- p_email_account_id IN NUMBER,
457 -- p_classification_id IN NUMBER,
458 -- p_score IN NUMBER,
459 -- p_message_id IN NUMBER,
460 -- p_class_string IN varchar2,
461 --
462 -- OUT
463 -- x_return_status OUT VARCHAR2
464 -- x_msg_count OUT NUMBER
465 -- x_msg_data OUT VARCHAR2
466 --
467 -- Version : 1.0
468 -- Notes :
469 --
470 -- End of comments
471 -- **********************************************************
472
473 PROCEDURE create_item (p_api_version_number IN NUMBER,
474 p_init_msg_list IN VARCHAR2 ,
475 p_commit IN VARCHAR2 ,
476 p_email_account_id IN NUMBER,
477 p_classification_id IN NUMBER,
478 p_score IN NUMBER,
479 p_message_id IN NUMBER,
480 p_class_string IN varchar2,
481 p_CREATED_BY NUMBER,
482 p_CREATION_DATE DATE,
483 p_LAST_UPDATED_BY NUMBER ,
484 p_LAST_UPDATE_DATE DATE,
485 p_LAST_UPDATE_LOGIN NUMBER ,
486 x_return_status OUT NOCOPY VARCHAR2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2
489 ) is
490 l_api_name VARCHAR2(255):='create_item';
491 l_api_version_number NUMBER:=1.0;
492 l_count NUMBER;
493
494 BEGIN
495 -- Standard Start of API savepoint
496 -- SAVEPOINT create_item_PVT;
497 -- Standard call to check for call compatibility.
498 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
499 p_api_version_number,
500 l_api_name,
501 G_PKG_NAME)
502 THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505 -- Initialize message list if p_init_msg_list is set to TRUE.
506 IF FND_API.to_Boolean( p_init_msg_list )
507 THEN
508 FND_MSG_PUB.initialize;
509 END IF;
510 -- Initialize API return status to SUCCESS
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512 select count(*) into l_count from iem_email_classifications
513 where message_id=p_message_id
514 and classification_id=p_classification_id;
515 If l_count=0 then
516 INSERT INTO IEM_EMAIL_CLASSIFICATIONS
517 (
518 MESSAGE_ID,
519 EMAIL_ACCOUNT_ID ,
520 CLASSIFICATION_ID ,
521 SCORE ,
522 CLASSIFICATION_STRING,
523 CREATED_BY ,
524 CREATION_DATE ,
525 LAST_UPDATED_BY ,
526 LAST_UPDATE_DATE ,
527 LAST_UPDATE_LOGIN
528 )
529 VALUES
530 (
531 p_message_id,
532 p_email_account_id,
533 p_classification_id,
534 p_score,
535 p_class_string,
536 DECODE(p_created_by,null,-1,p_created_by),
537 sysdate,
538 DECODE(p_LAST_UPDATED_BY,null,-1,p_last_updated_by),
539 sysdate,
540 DECODE(p_LAST_UPDATE_LOGIN,null,-1,p_last_update_login)
541 );
542 END IF;
543 -- Standard Check Of p_commit.
544 IF FND_API.To_Boolean(p_commit) THEN
545 COMMIT WORK;
546 END IF;
547 -- Standard callto get message count and if count is 1, get message info.
548 FND_MSG_PUB.Count_And_Get
549 ( p_count => x_msg_count,
550 p_data => x_msg_data
551 );
552 EXCEPTION
553 WHEN FND_API.G_EXC_ERROR THEN
554 x_return_status := FND_API.G_RET_STS_ERROR ;
555 FND_MSG_PUB.Count_And_Get
556 ( p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
561 FND_MSG_PUB.Count_And_Get
562 ( p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565 WHEN OTHERS THEN
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 IF FND_MSG_PUB.Check_Msg_Level
568 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
569 THEN
570 FND_MSG_PUB.Add_Exc_Msg
571 ( G_PKG_NAME ,
572 l_api_name
573 );
574 END IF;
575 FND_MSG_PUB.Count_And_Get
576 ( p_count => x_msg_count ,
577 p_data => x_msg_data
578 );
579
580 END;
581 END IEM_EML_CLASSIFICATIONS_PVT;