[Home] [Help]
PACKAGE BODY: APPS.IEM_KB_RESULTS_PVT
Source
1 PACKAGE BODY IEM_KB_RESULTS_PVT as
2 /* $Header: iemvkbrb.pls 120.0 2005/06/02 13:47:36 appldev noship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_KB_RESULTS_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_message_id IN NUMBER,
10 p_classification_id IN NUMBER,
11 p_email_account_id IN NUMBER,
12 p_document_id IN VARCHAR2,
13 p_kb_repository_name IN VARCHAR2,
14 p_kb_category_name IN VARCHAR2,
15 p_document_title IN VARCHAR2,
16 p_doc_last_modified_date IN DATE,
17 p_score IN VARCHAR2,
18 p_url IN VARCHAR2,
19 p_kb_delete IN VARCHAR2,
20 p_CREATED_BY NUMBER,
21 p_CREATION_DATE DATE,
22 p_LAST_UPDATED_BY NUMBER,
23 p_LAST_UPDATE_DATE DATE,
24 p_LAST_UPDATE_LOGIN NUMBER,
25 p_ATTRIBUTE1 VARCHAR2,
26 p_ATTRIBUTE2 VARCHAR2,
27 p_ATTRIBUTE3 VARCHAR2,
28 p_ATTRIBUTE4 VARCHAR2,
29 p_ATTRIBUTE5 VARCHAR2,
30 p_ATTRIBUTE6 VARCHAR2,
31 p_ATTRIBUTE7 VARCHAR2,
32 p_ATTRIBUTE8 VARCHAR2,
33 p_ATTRIBUTE9 VARCHAR2,
34 p_ATTRIBUTE10 VARCHAR2,
35 p_ATTRIBUTE11 VARCHAR2,
36 p_ATTRIBUTE12 VARCHAR2,
37 p_ATTRIBUTE13 VARCHAR2,
38 p_ATTRIBUTE14 VARCHAR2,
39 p_ATTRIBUTE15 VARCHAR2,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2
43 ) is
44 l_api_name VARCHAR2(255):='create_item';
45 l_api_version_number NUMBER:=1.0;
46 l_seq_id number;
47 l_grp_cnt number;
48
49 BEGIN
50 -- Standard Start of API savepoint
51 SAVEPOINT create_item_PVT;
52 -- Standard call to check for call compatibility.
53 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
54 p_api_version_number,
55 l_api_name,
56 G_PKG_NAME)
57 THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list )
62 THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65 -- Initialize API return status to SUCCESS
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67 SELECT IEM_KB_RESULTS_s1.nextval
68 INTO l_seq_id
69 FROM dual;
70
71 Select count(*) into l_grp_cnt from iem_mstemail_accounts
72 where email_account_id=p_email_account_id
73 and rownum=1;
74 IF l_grp_cnt = 0 then
75 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_EMAIL_ACCT_ID');
76 APP_EXCEPTION.RAISE_EXCEPTION;
77 END IF;
78
79 INSERT INTO IEM_KB_RESULTS
80 (
81 KB_RESULT_ID,
82 MESSAGE_ID ,
83 CLASSIFICATION_ID,
84 EMAIL_ACCOUNT_ID,
85 DOCUMENT_ID ,
86 KB_REPOSITORY_NAME,
87 KB_CATEGORY_NAME ,
88 DOCUMENT_TITLE ,
89 DOC_LAST_MODIFIED_DATE,
90 SCORE ,
91 URL,
92 kb_delete,
93 CREATED_BY,
94 CREATION_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_DATE,
97 LAST_UPDATE_LOGIN,
98 ATTRIBUTE1,
99 ATTRIBUTE2,
100 ATTRIBUTE3,
101 ATTRIBUTE4,
102 ATTRIBUTE5,
103 ATTRIBUTE6,
104 ATTRIBUTE7,
105 ATTRIBUTE8,
106 ATTRIBUTE9,
107 ATTRIBUTE10,
108 ATTRIBUTE11,
109 ATTRIBUTE12,
110 ATTRIBUTE13,
111 ATTRIBUTE14,
112 ATTRIBUTE15
113 )
114 VALUES
115 (
116 l_seq_id,
117 p_message_id,
118 p_classification_id,
119 p_email_account_id,
120 p_document_id,
121 p_kb_repository_name,
122 p_kb_category_name,
123 p_document_title,
124 p_doc_last_modified_date,
125 p_score,
126 p_url,
127 p_kb_delete,
128 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
129 sysdate,
130 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
131 sysdate,
132 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
133 p_ATTRIBUTE1,
134 p_ATTRIBUTE2,
135 p_ATTRIBUTE3,
136 p_ATTRIBUTE4,
137 p_ATTRIBUTE5,
138 p_ATTRIBUTE6,
139 p_ATTRIBUTE7,
140 p_ATTRIBUTE8,
141 p_ATTRIBUTE9,
142 p_ATTRIBUTE10,
143 p_ATTRIBUTE11,
144 p_ATTRIBUTE12,
145 p_ATTRIBUTE13,
146 p_ATTRIBUTE14,
147 p_ATTRIBUTE15
148 );
149
150 -- Standard Check Of p_commit.
151 IF FND_API.To_Boolean(p_commit) THEN
152 COMMIT WORK;
153 END IF;
154 -- Standard callto get message count and if count is 1, get message info.
155 FND_MSG_PUB.Count_And_Get
156 ( p_count => x_msg_count,
157 p_data => x_msg_data
158 );
159 EXCEPTION
160 WHEN FND_API.G_EXC_ERROR THEN
161 ROLLBACK TO create_item_PVT;
162 x_return_status := FND_API.G_RET_STS_ERROR ;
163 FND_MSG_PUB.Count_And_Get
164 ( p_count => x_msg_count,
165 p_data => x_msg_data
166 );
167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
168 ROLLBACK TO create_item_PVT;
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
170 FND_MSG_PUB.Count_And_Get
171 ( p_count => x_msg_count,
172 p_data => x_msg_data
173 );
174 WHEN OTHERS THEN
175 ROLLBACK TO create_item_PVT;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 IF FND_MSG_PUB.Check_Msg_Level
178 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
179 THEN
180 FND_MSG_PUB.Add_Exc_Msg
181 ( G_PKG_NAME ,
182 l_api_name
183 );
184 END IF;
185 FND_MSG_PUB.Count_And_Get
186 ( p_count => x_msg_count ,
187 p_data => x_msg_data
188 );
189
190 END create_item;
191
192 PROCEDURE delete_item (p_api_version_number IN NUMBER,
193 p_init_msg_list IN VARCHAR2 ,
194 p_commit IN VARCHAR2 ,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2
198 ) is
199 l_api_name VARCHAR2(255):='delete_item';
200 l_api_version_number NUMBER:=1.0;
201
202 BEGIN
203 -- Standard Start of API savepoint
204 SAVEPOINT delete_item_PVT;
205 -- Standard call to check for call compatibility.
206 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
207 p_api_version_number,
208 l_api_name,
209 G_PKG_NAME)
210 THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213 -- Initialize message list if p_init_msg_list is set to TRUE.
214 IF FND_API.to_Boolean( p_init_msg_list )
215 THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218 -- Initialize API return status to SUCCESS
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220
221 DELETE FROM IEM_KB_RESULTS
222 where kb_delete='Y';
223
224 -- Standard Check Of p_commit.
225 IF FND_API.To_Boolean(p_commit) THEN
226 COMMIT WORK;
227 END IF;
228 -- Standard callto get message count and if count is 1, get message info.
229 FND_MSG_PUB.Count_And_Get
230 ( p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233 EXCEPTION
234 WHEN FND_API.G_EXC_ERROR THEN
235 ROLLBACK TO delete_item_PVT;
236 x_return_status := FND_API.G_RET_STS_ERROR ;
237 FND_MSG_PUB.Count_And_Get
238 ( p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
242 ROLLBACK TO delete_item_PVT;
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
244 FND_MSG_PUB.Count_And_Get
245 ( p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248 WHEN OTHERS THEN
249 ROLLBACK TO delete_item_PVT;
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 IF FND_MSG_PUB.Check_Msg_Level
252 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 FND_MSG_PUB.Add_Exc_Msg
255 ( G_PKG_NAME ,
256 l_api_name
257 );
258 END IF;
259 FND_MSG_PUB.Count_And_Get
260 ( p_count => x_msg_count ,
261 p_data => x_msg_data
262 );
263
264 END delete_item;
265
266 PROCEDURE update_item (p_api_version_number IN NUMBER,
267 p_init_msg_list IN VARCHAR2 ,
268 p_commit IN VARCHAR2 ,
269 p_kb_result_id IN NUMBER,
270 p_kb_delete IN VARCHAR2,
271 p_CREATED_BY NUMBER,
272 p_CREATION_DATE DATE,
273 p_LAST_UPDATED_BY NUMBER,
274 p_LAST_UPDATE_DATE DATE,
275 p_LAST_UPDATE_LOGIN NUMBER,
276 p_ATTRIBUTE1 VARCHAR2,
277 p_ATTRIBUTE2 VARCHAR2,
278 p_ATTRIBUTE3 VARCHAR2,
279 p_ATTRIBUTE4 VARCHAR2,
280 p_ATTRIBUTE5 VARCHAR2,
281 p_ATTRIBUTE6 VARCHAR2,
282 p_ATTRIBUTE7 VARCHAR2,
283 p_ATTRIBUTE8 VARCHAR2,
284 p_ATTRIBUTE9 VARCHAR2,
285 p_ATTRIBUTE10 VARCHAR2,
286 p_ATTRIBUTE11 VARCHAR2,
287 p_ATTRIBUTE12 VARCHAR2,
288 p_ATTRIBUTE13 VARCHAR2,
289 p_ATTRIBUTE14 VARCHAR2,
290 p_ATTRIBUTE15 VARCHAR2,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2
294 ) is
295 l_api_name VARCHAR2(255):='update_item';
296 l_api_version_number NUMBER:=1.0;
297 l_grp_cnt NUMBER;
298 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
299 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
300
301 BEGIN
302 -- Standard Start of API savepoint
303 SAVEPOINT update_item_PVT;
304 -- Standard call to check for call compatibility.
305 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
306 p_api_version_number,
307 l_api_name,
308 G_PKG_NAME)
309 THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311 END IF;
312 -- Initialize message list if p_init_msg_list is set to TRUE.
313 IF FND_API.to_Boolean( p_init_msg_list )
314 THEN
315 FND_MSG_PUB.initialize;
316 END IF;
317 -- Initialize API return status to SUCCESS
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319 UPDATE IEM_KB_RESULTS
320 set kb_delete='Y'
321 where kb_result_id=p_kb_result_id ;
322
323 -- Standard Check Of p_commit.
324 IF FND_API.To_Boolean(p_commit) THEN
325 COMMIT WORK;
326 END IF;
327 -- Standard callto get message count and if count is 1, get message info.
328 FND_MSG_PUB.Count_And_Get
329 ( p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 EXCEPTION
333 WHEN FND_API.G_EXC_ERROR THEN
334 ROLLBACK TO update_item_PVT;
335 x_return_status := FND_API.G_RET_STS_ERROR ;
336 FND_MSG_PUB.Count_And_Get
337 ( p_count => x_msg_count,
338 p_data => x_msg_data
339 );
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 ROLLBACK TO update_item_PVT;
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
343 FND_MSG_PUB.Count_And_Get
344 ( p_count => x_msg_count,
345 p_data => x_msg_data
346 );
347 WHEN OTHERS THEN
348 ROLLBACK TO update_item_PVT;
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 IF FND_MSG_PUB.Check_Msg_Level
351 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
352 THEN
353 FND_MSG_PUB.Add_Exc_Msg
354 ( G_PKG_NAME ,
355 l_api_name
356 );
357 END IF;
358 FND_MSG_PUB.Count_And_Get
359 ( p_count => x_msg_count ,
360 p_data => x_msg_data
361 );
362
363 END update_item;
364 END IEM_KB_RESULTS_PVT ;