[Home] [Help]
1: PACKAGE BODY CS_KB_SET_RECS_PKG AS
2: /* $Header: cskbsrb.pls 120.0 2005/06/01 14:28:24 appldev noship $ */
3:
4: -- **********************************
5: -- * Private procedure declarations *
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;
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
65: where sr.set_number = sv.set_number
66: and sv.viewable_version_flag = 'Y'
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
65: where sr.set_number = sv.set_number
66: and sv.viewable_version_flag = 'Y'
67: and sv.status = 'PUB'
68: and sr.set_order < l_order );
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;
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'
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 );
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;
215:
216: -- Make sure the id sequence is always higher than the passed
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:
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
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');
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;
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;
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,
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,
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;
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;
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
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;
524: l_user := fnd_global.user_id;
525: l_login := fnd_global.login_id;
526:
527: -- We swap the order number of the solution recommendations.
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
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
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
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;