[Home] [Help]
PACKAGE BODY: APPS.IEM_SUGG_DOC_RANK_PVT
Source
1 PACKAGE BODY IEM_SUGG_DOC_RANK_PVT as
2 /* $Header: iemvsugb.pls 115.1 2004/03/10 00:10:06 rtripath noship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_SUGG_DOC_RANK_PVT ';
5
6 PROCEDURE create_item (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 ,
8 p_commit IN VARCHAR2 ,
9 p_outbound_msg_stats_id in number,
10 p_message_id IN number,
11 p_CREATED_BY IN NUMBER,
12 p_CREATION_DATE IN DATE,
13 p_LAST_UPDATED_BY IN NUMBER ,
14 p_LAST_UPDATE_DATE IN DATE,
15 p_LAST_UPDATE_LOGIN IN NUMBER ,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 ) is
20 l_api_name VARCHAR2(255):='create_item';
21 l_api_version_number NUMBER:=1.0;
22 l_class_id number;
23 l_rank number;
24 l_sugg_id number;
25 cursor c_top5 is
26 select * from (select document_id,document_title,kb_repository_name from iem_kb_results
27 where message_id=p_message_id and classification_id=l_class_id order by to_number(score) desc)
28 where rownum<=5;
29 cursor c_rest_doc is
30 select document_id,document_title,kb_repository_name from iem_kb_results
31 where message_id=p_message_id and
32 (document_id,document_title,kb_repository_name) not in
33 (
34 select * from (select document_id,document_title,kb_repository_name from iem_kb_results
35 where message_id=p_message_id and classification_id=l_class_id order by to_number(score) desc)
36 where rownum<=5);
37
38 BEGIN
39 -- Standard call to check for call compatibility.
40 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
41 p_api_version_number,
42 l_api_name,
43 G_PKG_NAME)
44 THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48 SAVEPOINT IEM_SUGG_PVT;
49 select * into l_class_id
50 from (select classification_id from iem_email_classifications
51 where message_id=p_message_id order by score desc)
52 where rownum=1;
53 l_rank:=0;
54 FOR v1 in c_top5 LOOP
55 l_rank:=l_rank+1;
56 select IEM_SUGG_DOC_DTLS_S1.nextval into l_sugg_id
57 from dual;
58 INSERT INTO IEM_SUGG_DOC_DTLS
59 (DOC_SUGG_ID ,
60 OUTBOUND_MSG_STATS_ID ,
61 KB_DOC_ID ,
62 KB_DOC_RANK ,
63 KB_DOC_TITLE,
64 REPOSITORY,
65 CREATED_BY,
66 CREATION_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATE_LOGIN)
70 VALUES
71 (l_sugg_id,
72 p_outbound_msg_stats_id,
73 v1.document_id,
74 l_rank,
75 v1.document_title,
76 v1.kb_repository_name,
77 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
78 sysdate,
79 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
80 sysdate,
81 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN));
82 END LOOP;
83 --Next Create the Rest records with rank 5+
84 l_rank:=6;
85 FOR v2 in c_rest_doc LOOP
86 select IEM_SUGG_DOC_DTLS_S1.nextval into l_sugg_id
87 from dual;
88 INSERT INTO IEM_SUGG_DOC_DTLS
89 (DOC_SUGG_ID ,
90 OUTBOUND_MSG_STATS_ID ,
91 KB_DOC_ID ,
92 KB_DOC_RANK ,
93 KB_DOC_TITLE,
94 REPOSITORY,
95 CREATED_BY,
96 CREATION_DATE,
97 LAST_UPDATED_BY,
98 LAST_UPDATE_DATE,
99 LAST_UPDATE_LOGIN)
100 VALUES
101 (l_sugg_id,
102 p_outbound_msg_stats_id,
103 v2.document_id,
104 l_rank,
105 v2.document_title,
106 v2.kb_repository_name,
107 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
108 sysdate,
109 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
110 sysdate,
111 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN));
112 END LOOP;
113 IF p_commit='T' THEN
114 COMMIT WORK;
115 END IF;
116 -- Standard callto get message count and if count is 1, get message info.
117 FND_MSG_PUB.Count_And_Get
118 ( p_count => x_msg_count,
119 p_data => x_msg_data
120 );
121 EXCEPTION
122 WHEN FND_API.G_EXC_ERROR THEN
123 x_return_status := FND_API.G_RET_STS_ERROR ;
124 rollback to IEM_SUGG_PVT;
125 FND_MSG_PUB.Count_And_Get
126 ( p_count => x_msg_count,
127 p_data => x_msg_data
128 );
129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
131 rollback to IEM_SUGG_PVT;
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data
135 );
136 WHEN OTHERS THEN
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 rollback to IEM_SUGG_PVT;
139 IF FND_MSG_PUB.Check_Msg_Level
140 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
141 THEN
142 FND_MSG_PUB.Add_Exc_Msg
143 ( G_PKG_NAME ,
144 l_api_name
145 );
146 END IF;
147 FND_MSG_PUB.Count_And_Get
148 ( p_count => x_msg_count ,
149 p_data => x_msg_data
150 );
151
152 END create_item;
153 END IEM_SUGG_DOC_RANK_PVT ;