[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SET_RECS_PKG
Source
4 -- **********************************
1 PACKAGE BODY CS_KB_SET_RECS_PKG AS
2 /* $Header: cskbsrb.pls 120.0 2005/06/01 14:28:24 appldev noship $ */
3
5 -- * Private procedure declarations *
6 -- **********************************
7
8 procedure Swap_Recommendation_Order
9 ( p_set_rec_id_1 in number,
10 p_set_rec_id_2 in number );
11
12 -- ************************************
13 -- * Public procedure implementations *
14 -- ************************************
15
16 --
17 -- Move a Solution up, on the Recommended Solutions list
18 --
19 PROCEDURE Move_Up_Solution_Rec
20 ( p_set_rec_id in number,
21 x_ret_status out nocopy varchar2,
22 x_msg_count out nocopy number,
23 x_msg_data out nocopy varchar2 )
24 is
25 l_order number;
26 l_prev_order number;
27 l_set_rec_id_prev number;
28 begin
29 -- Validate params
30 if(P_SET_REC_ID is null ) then
31 x_ret_status := FND_API.G_RET_STS_ERROR;
32 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
33 fnd_msg_pub.add;
34 FND_MSG_PUB.Count_And_Get(
35 p_count => x_msg_count,
36 p_data => x_msg_data );
37 return;
38 end if;
39
40 -- Query out the recommendation order of the solution
41 -- we want to move up the recommendation list
42 begin
43 select set_order into l_order from cs_kb_set_recs
44 where set_rec_id = p_set_rec_id;
45 exception
46 when no_data_found then
47 x_ret_status := FND_API.G_RET_STS_ERROR;
48 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_ID');
49 fnd_msg_pub.add;
50 FND_MSG_PUB.Count_And_Get(
51 p_count => x_msg_count,
52 p_data => x_msg_data );
53 return;
54 end;
55
56 -- Query out the order number of the solution
57 -- just above this one.
58 -- Note: we join the recommended solutions list with the
59 -- secure solutions view to filter out any solutions in
60 -- the recommended solutions list the user cannot access.
61 select sr1.set_rec_id, sr1.set_order into l_set_rec_id_prev, l_prev_order
62 from cs_kb_set_recs sr1 where sr1.set_order =
63 (select max(sr.set_order)
64 from cs_kb_set_recs sr, cs_kb_secure_solutions_view sv
68 and sr.set_order < l_order );
65 where sr.set_number = sv.set_number
66 and sv.viewable_version_flag = 'Y'
67 and sv.status = 'PUB'
69 if(l_prev_order is null) then
70 x_ret_status := FND_API.G_RET_STS_ERROR;
71 fnd_message.set_name('CS', 'CS_KB_C_REC_SET_NO_HIGHER');
72 fnd_msg_pub.add;
73 FND_MSG_PUB.Count_And_Get(
74 p_count => x_msg_count,
75 p_data => x_msg_data );
76 return;
77 end if;
78
79 Swap_Recommendation_Order(l_set_rec_id_prev, p_set_rec_id);
80
81 x_ret_status := FND_API.G_RET_STS_SUCCESS;
82 x_msg_count := 0;
83 x_msg_data := null;
84 return;
85 end Move_Up_Solution_Rec;
86
87
88 --
89 -- Move a Solution down, on the Recommended Solutions list
90 --
91 PROCEDURE Move_Down_Solution_Rec
92 ( p_set_rec_id in number,
93 x_ret_status out nocopy varchar2,
94 x_msg_count out nocopy number,
95 x_msg_data out nocopy varchar2 )
96 is
97 l_order number;
98 l_next_order number;
99 l_set_rec_id_next number;
100 begin
101 -- Validate params
102 if(P_SET_REC_ID is null ) then
103 x_ret_status := FND_API.G_RET_STS_ERROR;
104 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
105 fnd_msg_pub.add;
106 FND_MSG_PUB.Count_And_Get(
107 p_count => x_msg_count,
108 p_data => x_msg_data );
109 return;
110 end if;
111
112 -- Query out the recommendation order of the solution
113 -- we want to move down the recommendation list
114 begin
115 select set_order into l_order from cs_kb_set_recs
116 where set_rec_id = p_set_rec_id;
117 exception
118 when no_data_found then
119 x_ret_status := FND_API.G_RET_STS_ERROR;
120 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_ID');
121 fnd_msg_pub.add;
122 FND_MSG_PUB.Count_And_Get(
123 p_count => x_msg_count,
124 p_data => x_msg_data );
125 return;
126 end;
127
128 -- Query out the order number of the solution
129 -- just below this one.
130 -- Note: we join the recommended solutions list with the
131 -- secure solutions view to filter out any solutions in
132 -- the recommended solutions list the user cannot access.
133 select sr1.set_rec_id, sr1.set_order into l_set_rec_id_next, l_next_order
134 from cs_kb_set_recs sr1 where sr1.set_order =
135 (select min(sr.set_order)
136 from cs_kb_set_recs sr, cs_kb_secure_solutions_view sv
137 where sr.set_number = sv.set_number
138 and sv.viewable_version_flag = 'Y'
139 and sv.status = 'PUB'
140 and sr.set_order > l_order );
141 if(l_next_order is null) then
142 x_ret_status := FND_API.G_RET_STS_ERROR;
143 fnd_message.set_name('CS', 'CS_KB_C_REC_SET_NO_LOWER');
144 fnd_msg_pub.add;
145 FND_MSG_PUB.Count_And_Get(
146 p_count => x_msg_count,
147 p_data => x_msg_data );
148 return;
149 end if;
150
151 Swap_Recommendation_Order(l_set_rec_id_next, p_set_rec_id);
152
153 x_ret_status := FND_API.G_RET_STS_SUCCESS;
154 x_msg_count := 0;
155 x_msg_data := null;
156 return;
157 end Move_Down_Solution_Rec;
158
159 /*
160 * Create_Set_Rec
161 * Create a solution recommendation record.
162 */
163 PROCEDURE Create_Set_Rec
164 ( P_SET_REC_ID in NUMBER DEFAULT NULL,
165 P_SET_NUMBER in VARCHAR2,
166 P_SET_ORDER in NUMBER DEFAULT NULL,
167 P_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
168 P_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
169 P_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
170 P_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
171 P_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
172 P_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
173 P_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
174 P_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
175 P_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
176 P_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
177 P_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
178 P_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
179 P_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
180 P_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
181 P_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
182 P_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
183 X_SET_REC_ID out nocopy NUMBER,
184 X_RET_STATUS out nocopy VARCHAR2,
185 X_MSG_COUNT out nocopy NUMBER,
186 X_MSG_DATA out nocopy VARCHAR2 )
187 IS
188 l_set_rec_id number;
189 l_set_id number;
190 l_date date;
191 l_created_by number;
192 l_login number;
193 l_count number;
194 l_set_order number;
195 l_set_rec_id_seq_val number;
196 l_order_seq_val number;
197 BEGIN
198 -- initialize default outputs to error
199 x_set_rec_id := 0;
200 x_ret_status := FND_API.G_RET_STS_ERROR;
201
202 -- Check params
203 if(p_set_rec_id is not null ) then
204 select count(*) into l_count
205 from cs_kb_set_recs
206 where set_rec_id = p_set_rec_id;
207 if( l_count > 0 ) then
208 fnd_message.set_name('CS', 'CS_KB_C_REC_SET_ERR');
209 fnd_msg_pub.add;
210 FND_MSG_PUB.Count_And_Get(
211 p_count => x_msg_count,
212 p_data => x_msg_data );
216 -- Make sure the id sequence is always higher than the passed
213 return;
214 end if;
215
217 -- in set rec id such that there will not be duplicate solution
218 -- recommendation id's created.
219 select cs_kb_set_recs_s.currval into l_set_rec_id_seq_val from dual;
220 while ( l_set_rec_id_seq_val < p_set_rec_id ) loop
221 select cs_kb_set_recs_s.nextval into l_set_rec_id_seq_val from dual;
222 end loop;
223
224 l_set_rec_id := p_set_rec_id;
225 else
226 select cs_kb_set_recs_s.nextval into l_set_rec_id from dual;
227 end if;
228
229 if(P_SET_NUMBER is null) then
230 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
231 fnd_msg_pub.add;
232 FND_MSG_PUB.Count_And_Get(
233 p_count => x_msg_count,
234 p_data => x_msg_data );
235 return;
236 end if;
237
238 -- Find the solution id for latest viewable solution version,
239 -- given the solution number.
240 begin
241 select distinct set_id into l_set_id
242 from cs_kb_secure_solutions_view
243 where set_number = p_set_number
244 and viewable_version_flag = 'Y'
245 and status = 'PUB';
246 exception
247 when no_data_found then
248 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_ID');
249 fnd_msg_pub.add;
250 FND_MSG_PUB.Count_And_Get(
251 p_count => x_msg_count,
252 p_data => x_msg_data );
253 return;
254 end;
255
256
257 --check for duplicate solution recommendation
258 select count(*) into l_count
259 from cs_kb_set_recs
260 where set_number = p_set_number;
261 if(l_count>0) then
262 fnd_message.set_name('CS', 'CS_KB_C_REC_SET_EXIST');
263 fnd_msg_pub.add;
264 FND_MSG_PUB.Count_And_Get(
265 p_count => x_msg_count,
266 p_data => x_msg_data );
267 return;
268 end if;
269
270
271 -- If the solution recommendation order parameter (p_set_order)
272 -- is null, generate a new order number from the sequence.
273 -- Otherwise, validate that the passed order is not a duplicate.
274 if( p_set_order is null ) then
275 select cs_kb_set_rec_order_s.nextval into l_set_order from dual;
276 else
277 l_set_order := p_set_order;
278 select count(*) into l_count from cs_kb_set_recs
279 where set_order = l_set_order;
280 if( l_count > 0 ) then
281 fnd_message.set_name('CS', 'CS_KB_C_REC_SET_INVALID_ORDER');
282 fnd_msg_pub.add;
283 FND_MSG_PUB.Count_And_Get(
284 p_count => x_msg_count,
285 p_data => x_msg_data );
286 return;
287 end if;
288
289 -- Make sure the order sequence is always higher than the passed
290 -- in order number such that newly recommended solutions are always
291 -- at the end of the recommendation list.
292 select cs_kb_set_rec_order_s.currval into l_order_seq_val from dual;
293 while ( l_order_seq_val < l_set_order ) loop
294 select cs_kb_set_rec_order_s.nextval into l_order_seq_val from dual;
295 end loop;
296 end if;
297
298 l_date := sysdate;
299 l_created_by := fnd_global.user_id;
300 l_login := fnd_global.login_id;
301
302 -- Create the solution recommendation row
303 insert into CS_KB_SET_RECS
304 ( SET_REC_ID,
305 SET_ID,
306 SET_ORDER,
307 SET_NUMBER,
308 CREATION_DATE,
309 CREATED_BY,
310 LAST_UPDATE_DATE,
311 LAST_UPDATED_BY,
312 LAST_UPDATE_LOGIN,
313 ATTRIBUTE_CATEGORY,
314 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
315 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
316 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15 )
317 values
318 ( l_set_rec_id,
319 l_set_id,
320 l_set_order,
321 P_SET_NUMBER,
322 l_date,
323 l_created_by,
324 l_date,
325 l_created_by,
326 l_login,
327 P_ATTRIBUTE_CATEGORY,
328 P_ATTRIBUTE1, P_ATTRIBUTE2, P_ATTRIBUTE3, P_ATTRIBUTE4, P_ATTRIBUTE5,
329 P_ATTRIBUTE6, P_ATTRIBUTE7, P_ATTRIBUTE8, P_ATTRIBUTE9, P_ATTRIBUTE10,
330 P_ATTRIBUTE11, P_ATTRIBUTE12, P_ATTRIBUTE13, P_ATTRIBUTE14, P_ATTRIBUTE15);
331
332 -- return success
333 x_set_rec_id := l_set_rec_id;
334 x_ret_status := FND_API.G_RET_STS_SUCCESS;
335 x_msg_count := 0;
336 x_msg_data := null;
337 return;
338 exception
339 when others then
340 x_ret_status := FND_API.G_RET_STS_ERROR;
341 fnd_message.set_name('CS', 'CS_KB_C_REC_SET_ERR');
342 fnd_msg_pub.add;
343 FND_MSG_PUB.Count_And_Get(
344 p_count => x_msg_count,
345 p_data => x_msg_data );
346 return;
347 END Create_Set_Rec;
348
349
350 /*
351 * Update_Set_Rec
352 * This procedure updates a solution recommendation record.
353 * NOTE: !! Currently this procedure is not being used. !!
354 * !! Needs additial review and some cleanup. !!
355 */
356 procedure Update_Set_Rec
357 ( P_SET_REC_ID in NUMBER,
358 P_SET_NUMBER in VARCHAR2,
359 P_SET_ORDER in NUMBER,
360 P_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
364 P_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
361 P_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
362 P_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
363 P_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
365 P_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
366 P_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
367 P_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
368 P_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
369 P_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
370 P_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
371 P_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
372 P_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
373 P_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
374 P_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
375 P_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
376 X_RET_STATUS out nocopy VARCHAR2,
377 X_MSG_COUNT out nocopy NUMBER,
378 X_MSG_DATA out nocopy VARCHAR2 )
379 is
380 l_date date;
381 l_updated_by number;
382 l_login number;
383 begin
384
385 -- validate params
386 if(P_SET_NUMBER is null OR P_SET_ORDER is null) then
387 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
388 fnd_msg_pub.add;
389 FND_MSG_PUB.Count_And_Get(
390 p_count => x_msg_count,
391 p_data => x_msg_data );
392 goto error_found;
393 end if;
394
395 -- The set_id stored in the recs table is not necessarily the latest PUB version
396 -- that is displayed in the ui. Therefore fetch stored set_id.
397 /*
398 OPEN GET_STORED_SET(p_set_id);
399 FETCH GET_STORED_SET INTO l_stored_set_id;
400 CLOSE GET_STORED_SET;
401 */
402
403 l_date := sysdate;
404 l_updated_by := fnd_global.user_id;
405 l_login := fnd_global.login_id;
406
407 update CS_KB_SET_RECS set
408 SET_ORDER = P_SET_ORDER,
409 LAST_UPDATE_DATE = l_date,
410 LAST_UPDATED_BY = l_updated_by,
411 LAST_UPDATE_LOGIN = l_login,
412 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
413 ATTRIBUTE1 = P_ATTRIBUTE1,
414 ATTRIBUTE2 = P_ATTRIBUTE2,
415 ATTRIBUTE3 = P_ATTRIBUTE3,
416 ATTRIBUTE4 = P_ATTRIBUTE4,
417 ATTRIBUTE5 = P_ATTRIBUTE5,
418 ATTRIBUTE6 = P_ATTRIBUTE6,
419 ATTRIBUTE7 = P_ATTRIBUTE7,
420 ATTRIBUTE8 = P_ATTRIBUTE8,
421 ATTRIBUTE9 = P_ATTRIBUTE9,
422 ATTRIBUTE10 = P_ATTRIBUTE10,
423 ATTRIBUTE11 = P_ATTRIBUTE11,
424 ATTRIBUTE12 = P_ATTRIBUTE12,
425 ATTRIBUTE13 = P_ATTRIBUTE13,
426 ATTRIBUTE14 = P_ATTRIBUTE14,
427 ATTRIBUTE15 = P_ATTRIBUTE15
428 WHERE SET_REC_ID = P_SET_REC_ID;
429 --where SET_ID = l_stored_set_id;
430
431 if (sql%notfound) then
432 raise no_data_found;
433 end if;
434
435 return;
436
437 <<error_found>>
438 return;
439
440 exception
441 when others then
442 return;
443
444 end Update_Set_Rec;
445
446 /*
447 * Delete_Set_Rec
448 * Delete a solution recommendation record.
449 */
450 PROCEDURE Delete_Set_Rec
451 ( p_set_rec_id in number,
452 x_ret_status out nocopy varchar2,
453 x_msg_count out nocopy number,
454 x_msg_data out nocopy varchar2 )
455 is
456 l_count number;
457 begin
458 -- initialize default outputs to error
459 x_ret_status := FND_API.G_RET_STS_ERROR;
460
461 -- check params
462 if (p_set_rec_id is null) then
463 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
464 fnd_msg_pub.add;
465 FND_MSG_PUB.Count_And_Get(
466 p_count => x_msg_count,
467 p_data => x_msg_data );
468 return;
469 end if;
470
471 -- validate params
472 select count(*) into l_count from cs_kb_set_recs
473 where set_rec_id = p_set_rec_id;
474 if(l_count = 0 ) then
475 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_ID');
476 fnd_msg_pub.add;
477 FND_MSG_PUB.Count_And_Get(
478 p_count => x_msg_count,
479 p_data => x_msg_data );
480 return;
481 end if;
482
483 -- delete the solution recommendation
484 delete from CS_KB_SET_RECS
485 where SET_REC_ID = p_set_rec_id;
486
487 -- return success
488 x_ret_status := FND_API.G_RET_STS_SUCCESS;
489 x_msg_count := 0;
490 x_msg_data := null;
491 return;
492
493 end Delete_Set_Rec;
494
495 -- *************************************
496 -- * Private procedure implementations *
497 -- *************************************
498
499
500 -- Swap the recommendation order for 2 recommended solutions
501 -- This internal api does not do any validation. Caller must
502 -- perform all validations.
503 procedure Swap_Recommendation_Order
504 ( p_set_rec_id_1 in number,
505 p_set_rec_id_2 in number )
506 is
507 l_order_1 number;
508 l_order_2 number;
509 l_date date;
510 l_user number;
511 l_login number;
512 begin
513 -- Store the order number for both solution recommendation records
514 select set_order into l_order_1
515 from cs_kb_set_recs
516 where set_rec_id = p_set_rec_id_1;
517
518 select set_order into l_order_2
519 from cs_kb_set_recs
520 where set_rec_id = p_set_rec_id_2;
521
522 -- Initialize some who column data
523 l_date := sysdate;
527 -- We swap the order number of the solution recommendations.
524 l_user := fnd_global.user_id;
525 l_login := fnd_global.login_id;
526
528 update cs_kb_set_recs
529 set set_order = -100,
530 last_update_date = l_date,
531 last_updated_by = l_user,
532 last_update_login = l_login
533 where set_rec_id = p_set_rec_id_2;
534
535 update cs_kb_set_recs
536 set set_order = l_order_2,
537 last_update_date = l_date,
538 last_updated_by = l_user,
539 last_update_login = l_login
540 where set_rec_id = p_set_rec_id_1;
541
542 update cs_kb_set_recs
543 set set_order = l_order_1,
544 last_update_date = l_date,
545 last_updated_by = l_user,
546 last_update_login = l_login
547 where set_rec_id = p_set_rec_id_2;
548 end;
549
550 end CS_KB_SET_RECS_PKG;