[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SET_LINKS_PKG
Source
1 PACKAGE BODY CS_KB_SET_LINKS_PKG AS
2 /* $Header: cskbslb.pls 120.1 2005/07/27 19:05:29 appldev ship $ */
3
4 --
5 -- Clone_link is to duplicate all data with previous verion set_id to new set_id
6 -- @param ( previous set id, new set id)
7 -- return (OK_STATUS OR ERROR_STATUS)
8 --
9 function Clone_Link(
10 P_SET_SOURCE_ID in NUMBER,
11 P_SET_TARGET_ID in NUMBER
12 )return number IS
13 l_count number;
14 l_id number;
15 l_rowid varchar2(30);
16 cursor l_link_csr is
17 select * from cs_kb_set_links
18 where set_id = p_set_source_id;
19 BEGIN
20 -- Validate Set Id
21 select count(*) into l_count
22 from cs_kb_sets_b
23 where set_id = p_set_source_id;
24 if(l_count <= 0) then
25 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_ID');
26 fnd_msg_pub.Add;
27 goto error_found;
28 end if;
29
30 -- Copying data with previous set id to new set_id.
31 for rec_link in l_link_csr loop
32 -- dtian: use table handler instead
33 select cs_kb_set_links_s.nextval into l_id from dual;
34
35 CS_KB_SET_LINKS_PKG.Insert_Row(
36 X_Rowid => l_rowid,
37 X_Link_Id => l_id,
38 X_Link_type => REC_LINK.LINK_TYPE,
39 X_Object_Code => REC_LINK.OBJECT_CODE,
40 X_Set_Id => P_SET_TARGET_ID,
41 X_Other_Id => REC_LINK.OTHER_ID,
42 X_Creation_Date => sysdate,
43 X_Created_By => fnd_global.user_id,
44 X_Last_Update_Date => sysdate,
45 X_Last_Updated_By => fnd_global.user_id,
46 X_Last_Update_Login => fnd_global.login_id,
47 X_ATTRIBUTE_CATEGORY => REC_LINK.ATTRIBUTE_CATEGORY,
48 X_ATTRIBUTE1 => REC_LINK.ATTRIBUTE1,
49 X_ATTRIBUTE2 => REC_LINK.ATTRIBUTE2,
50 X_ATTRIBUTE3 => REC_LINK.ATTRIBUTE3,
51 X_ATTRIBUTE4 => REC_LINK.ATTRIBUTE4,
52 X_ATTRIBUTE5 => REC_LINK.ATTRIBUTE5,
53 X_ATTRIBUTE6 => REC_LINK.ATTRIBUTE6,
54 X_ATTRIBUTE7 => REC_LINK.ATTRIBUTE7,
55 X_ATTRIBUTE8 => REC_LINK.ATTRIBUTE8,
56 X_ATTRIBUTE9 => REC_LINK.ATTRIBUTE9,
57 X_ATTRIBUTE10 => REC_LINK.ATTRIBUTE10,
58 X_ATTRIBUTE11 => REC_LINK.ATTRIBUTE11,
59 X_ATTRIBUTE12 => REC_LINK.ATTRIBUTE12,
60 X_ATTRIBUTE13 => REC_LINK.ATTRIBUTE13,
61 X_ATTRIBUTE14 => REC_LINK.ATTRIBUTE14,
62 X_ATTRIBUTE15 => REC_LINK.ATTRIBUTE15
63 );
64 -- end dtian: use table handler instead
65 end loop;
66
67 return OKAY_STATUS;
68
69 <<error_found>>
70 return ERROR_STATUS;
71 END Clone_Link;
72
73 -- create a set link entry (new, with error message stack called from OA)
74 -- This is the api to use as of 11.5.10
75 -- As of 3407999, this one become a private method. See wrappers after this.
76 PROCEDURE Create_Set_Link(
77 P_LINK_TYPE in VARCHAR,
78 P_OBJECT_CODE in VARCHAR,
79 P_SET_ID in NUMBER,
80 P_OTHER_ID in NUMBER,
81 P_ATTRIBUTE_CATEGORY in VARCHAR2,
82 P_ATTRIBUTE1 in VARCHAR2,
83 P_ATTRIBUTE2 in VARCHAR2,
84 P_ATTRIBUTE3 in VARCHAR2,
85 P_ATTRIBUTE4 in VARCHAR2,
86 P_ATTRIBUTE5 in VARCHAR2,
87 P_ATTRIBUTE6 in VARCHAR2,
88 P_ATTRIBUTE7 in VARCHAR2,
89 P_ATTRIBUTE8 in VARCHAR2,
90 P_ATTRIBUTE9 in VARCHAR2,
91 P_ATTRIBUTE10 in VARCHAR2,
92 P_ATTRIBUTE11 in VARCHAR2,
93 P_ATTRIBUTE12 in VARCHAR2,
94 P_ATTRIBUTE13 in VARCHAR2,
95 P_ATTRIBUTE14 in VARCHAR2,
96 P_ATTRIBUTE15 in VARCHAR2,
97 P_UPDATE_EXTRA_VERSION in VARCHAR2,
98 x_link_id in OUT NOCOPY NUMBER,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_data OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER
102 ) IS
103 l_date date;
104 l_created_by number;
105 l_login number;
106 l_id number;
107 l_rowid varchar2(30);
108 l_other_link_id number;
109 l_other_rowid varchar2(30);
110 -- Validation Cursors
111 CURSOR Validate_Solution IS
112 SELECT set_number, status
113 FROM CS_KB_SETS_B
114 WHERE set_id = P_SET_ID
115 AND ( status = 'PUB' or latest_version_flag = 'Y' );
116
117 l_set_number NUMBER;
118 l_set_status VARCHAR2(30);
119
120 CURSOR Get_Object_Query IS
121 SELECT 'SELECT count(*) FROM '||
122 v.From_Table||
123 ' WHERE '||v.select_id||' = :1 '||
124 decode( v.where_clause, null, ' ', ' AND ' || v.where_clause )
125 FROM jtf_objects_vl v, jtf_object_usages u
126 WHERE v.object_code = P_OBJECT_CODE
127 AND v.object_code = u.object_code
128 AND u.object_user_code='CS_KB_SET'
129 and ( v.end_date_active is NULL or v.end_date_active > sysdate );
130
131 l_cursor NUMBER;
132 l_return NUMBER;
133 l_query varchar2(5000);
134 l_ext_obj_count NUMBER;
135
136 CURSOR Check_Duplicate IS
137 SELECT count(*)
138 FROM CS_KB_SET_LINKS
139 WHERE set_id = P_SET_ID
140 AND other_id = P_OTHER_ID
141 AND object_code = P_OBJECT_CODE;
142
143 l_dup_count NUMBER;
144
145 -- Cursor to fetch id of the latest version of a solution
146 CURSOR Get_Link_To_Latest_Ver(c_soln_number VARCHAR2) IS
147 SELECT sb.set_id
148 FROM CS_KB_SETS_B sb
149 WHERE sb.set_number = c_soln_number
150 AND sb.latest_version_flag = 'Y';
151
152 l_latest_soln_ver_id NUMBER;
153
154 -- Cursor to fetch id of the published version of a solution
155 CURSOR Get_Link_To_Published_Ver(c_soln_number VARCHAR2) IS
156 SELECT sb.set_id
157 FROM CS_KB_SETS_B sb
158 WHERE sb.set_number = c_soln_number
159 AND sb.status = 'PUB';
160
161 l_published_soln_ver_id NUMBER;
162
163 l_other_soln_ver_id NUMBER;
164
165 -- Cursor to detect whether there is already a link to a solution version
166 -- from a particular object
167 CURSOR Count_Links_to_Soln_Ver(c_soln_id NUMBER, c_object_code VARCHAR2,
168 c_other_id NUMBER) IS
169 SELECT count(*)
170 FROM CS_KB_SET_LINKS
171 WHERE set_id = c_soln_id
172 AND object_code = c_object_code
173 AND other_id = c_other_id;
174
175 l_links_to_soln_ver_ct NUMBER;
176
177 l_found_other_version varchar2(1);
178
179 BEGIN
180 SAVEPOINT CREATE_LINK_SP;
181
182 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
183
184 -- Begin Validation
185 -- validate params
186 if( P_OBJECT_CODE is null
187 OR P_SET_ID is NULL
188 OR P_OTHER_ID is null
189 OR p_link_type is null ) THEN
190 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
191 fnd_msg_pub.Add;
192 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
193 p_count => X_MSG_COUNT,
194 p_data => X_MSG_DATA);
195 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
196 return;
197 end if;
198
199 OPEN Validate_Solution;
200 FETCH Validate_Solution INTO l_set_number, l_set_status;
201 IF ( Validate_Solution%NOTFOUND ) THEN
202 CLOSE Validate_Solution;
203 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_SET_ID');
204 FND_MSG_PUB.ADD;
205 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
206 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
207 p_count => X_MSG_COUNT,
208 p_data => X_MSG_DATA);
209 return;
210 END IF;
211 CLOSE Validate_Solution;
212
213 BEGIN
214 OPEN Get_Object_Query;
215 FETCH Get_Object_Query INTO l_query;
216 CLOSE Get_Object_Query;
217
218 --dbms_output.put_line('Query :'||l_query);
219 l_cursor := DBMS_SQL.OPEN_CURSOR;
220 DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
221 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_ext_obj_count);
222 DBMS_SQL.BIND_VARIABLE(l_cursor, ':1',P_OTHER_ID);
223 l_return := DBMS_SQL.EXECUTE(l_cursor);
224
225 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
226 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_ext_obj_count);
227 END IF;
228 DBMS_SQL.CLOSE_CURSOR(l_cursor);
229
230 --dbms_output.put_line('Obj count: '||l_ext_obj_count);
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 --dbms_output.put_line('Caught invalid obj exception !!! ');
235 l_ext_obj_count := 0;
236 END;
237
238 IF l_ext_obj_count = 0 THEN
239
240 --dbms_output.put_line('Invalid Ext Object ');
241 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_EXT_OBJ');
242 FND_MSG_PUB.ADD;
243 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
244 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
245 p_count => X_MSG_COUNT,
246 p_data => X_MSG_DATA);
247 return;
248 END IF;
249
250 OPEN Check_Duplicate;
251 FETCH Check_Duplicate INTO l_dup_count;
252 CLOSE Check_Duplicate;
253
254 IF l_dup_count > 0 THEN
255 -- Bug fix 3350231: Used to return error.
256 -- Now just return success if the link is already there.
257 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
258 return;
259 END IF;
260
261 IF ( P_LINK_TYPE not in ('PS', 'S', 'NS') ) THEN
262 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_LINK_TYPE');
263 FND_MSG_PUB.ADD;
264 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
265 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
266 p_count => X_MSG_COUNT,
267 p_data => X_MSG_DATA);
268 return;
269 END IF;
270 -- End of Validation
271
272 --prepare data, then insert new set link
273 IF( x_link_id is null ) THEN
274 select cs_kb_set_links_s.nextval into l_id from dual;
275 ELSE
276 l_id := x_link_id;
277 END IF;
278
279 l_date := sysdate;
280 l_created_by := fnd_global.user_id;
281 l_login := fnd_global.login_id;
282
283 CS_KB_SET_LINKS_PKG.Insert_Row(
284 X_Rowid => l_rowid,
285 X_Link_Id => l_id,
286 X_Link_type => p_link_type,
287 X_Object_Code => p_object_code,
288 X_Set_Id => p_set_id,
289 X_Other_Id => p_other_id,
290 X_Creation_Date => l_date,
291 X_Created_By => l_created_by,
292 X_Last_Update_Date => l_date,
293 X_Last_Updated_By => l_created_by,
294 X_Last_Update_Login => l_login,
295 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
296 X_ATTRIBUTE1 => P_ATTRIBUTE1,
297 X_ATTRIBUTE2 => P_ATTRIBUTE2,
298 X_ATTRIBUTE3 => P_ATTRIBUTE3,
299 X_ATTRIBUTE4 => P_ATTRIBUTE4,
300 X_ATTRIBUTE5 => P_ATTRIBUTE5,
301 X_ATTRIBUTE6 => P_ATTRIBUTE6,
302 X_ATTRIBUTE7 => P_ATTRIBUTE7,
303 X_ATTRIBUTE8 => P_ATTRIBUTE8,
304 X_ATTRIBUTE9 => P_ATTRIBUTE9,
305 X_ATTRIBUTE10 => P_ATTRIBUTE10,
306 X_ATTRIBUTE11 => P_ATTRIBUTE11,
307 X_ATTRIBUTE12 => P_ATTRIBUTE12,
308 X_ATTRIBUTE13 => P_ATTRIBUTE13,
309 X_ATTRIBUTE14 => P_ATTRIBUTE14,
310 X_ATTRIBUTE15 => P_ATTRIBUTE15
311 );
312
313 -- Raise Business Event when a solution is linked
314 CS_KB_SET_LINKS_PKG.Raise_Solution_Linked_Event(
315 p_set_id => p_set_id,
316 p_object_code => p_object_code,
317 p_object_id => p_other_id,
318 p_link_id => l_id,
319 p_link_type => p_link_type,
320 p_event_date => l_date );
321
322 IF (P_UPDATE_EXTRA_VERSION = 'Y') THEN
323 -- If the link being created is to a published solution version, see if
324 -- there is also an in-progress version of the same solution. If there is,
325 -- we need to also create a link between the object and the latest
326 -- in-progess solution version.
327 -- Due to 3407999, if the link being created is to an in progress version,
328 -- see if there is also an published version of the same solution. If
329 -- there is, we need to also create a link between the object and the
330 -- published one.
331 l_found_other_version := 'N';
332 if (l_set_status = 'PUB') then
333 OPEN Get_Link_To_Latest_Ver(l_set_number);
334 FETCH Get_Link_To_Latest_Ver INTO l_latest_soln_ver_id;
335 CLOSE Get_Link_To_Latest_Ver;
336 IF( (l_latest_soln_ver_id is not null)
337 and l_latest_soln_ver_id <> p_set_id) THEN
338 l_found_other_version := 'Y';
339 l_other_soln_ver_id := l_latest_soln_ver_id;
340 END IF;
341 else
342 OPEN Get_Link_To_Published_Ver(l_set_number);
343 FETCH Get_Link_To_Published_Ver INTO l_published_soln_ver_id;
344 CLOSE Get_Link_To_Published_Ver;
345 IF( (l_published_soln_ver_id is not null)
349 END IF;
346 and l_published_soln_ver_id <> p_set_id) THEN
347 l_found_other_version := 'Y';
348 l_other_soln_ver_id := l_published_soln_ver_id;
350 end if;
351
352 IF (l_found_other_version = 'Y') THEN
353 -- Create a link to the latest version if one doesn't already exist
354 OPEN Count_Links_to_Soln_Ver( l_other_soln_ver_id, p_object_code,
355 p_other_id);
356 FETCH Count_Links_to_Soln_Ver into l_links_to_soln_ver_ct;
357 CLOSE Count_Links_to_Soln_Ver;
358 IF ( l_links_to_soln_ver_ct = 0 ) THEN
359 select cs_kb_set_links_s.nextval into l_other_link_id from dual;
360 CS_KB_SET_LINKS_PKG.Insert_Row(
361 X_Rowid => l_other_rowid,
362 X_Link_Id => l_other_link_id,
363 X_Link_type => p_link_type,
364 X_Object_Code => p_object_code,
365 X_Set_Id => l_other_soln_ver_id,
366 X_Other_Id => p_other_id,
367 X_Creation_Date => l_date,
368 X_Created_By => l_created_by,
369 X_Last_Update_Date => l_date,
370 X_Last_Updated_By => l_created_by,
371 X_Last_Update_Login => l_login,
372 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
373 X_ATTRIBUTE1 => P_ATTRIBUTE1,
374 X_ATTRIBUTE2 => P_ATTRIBUTE2,
375 X_ATTRIBUTE3 => P_ATTRIBUTE3,
376 X_ATTRIBUTE4 => P_ATTRIBUTE4,
377 X_ATTRIBUTE5 => P_ATTRIBUTE5,
378 X_ATTRIBUTE6 => P_ATTRIBUTE6,
379 X_ATTRIBUTE7 => P_ATTRIBUTE7,
380 X_ATTRIBUTE8 => P_ATTRIBUTE8,
381 X_ATTRIBUTE9 => P_ATTRIBUTE9,
382 X_ATTRIBUTE10 => P_ATTRIBUTE10,
383 X_ATTRIBUTE11 => P_ATTRIBUTE11,
384 X_ATTRIBUTE12 => P_ATTRIBUTE12,
385 X_ATTRIBUTE13 => P_ATTRIBUTE13,
386 X_ATTRIBUTE14 => P_ATTRIBUTE14,
387 X_ATTRIBUTE15 => P_ATTRIBUTE15
388 );
389 END IF;
390 END IF;
391 END IF;
392 x_link_id := l_id;
393 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
394
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 --dbms_output.put_line('in csl when others ');
399 ROLLBACK TO Create_Link_SP;
400 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
401 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, 'cs.plsql.cs_kb_set_links.create_set_link', SQLERRM );
402 end if;
403 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
404 FND_MSG_PUB.Add;
405 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
406 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
407 p_count => x_msg_count,
408 p_data => x_msg_data);
409 end Create_Set_Link;
410
411 -- As of bug 3407999, I split the creation into two functions, which will call
412 -- the same base function.
413 -- Create_Set_Link will be used for feed back purpose, will update the latest
414 -- verion if it is different. Group APIs using Create_Set_Link all use it for
415 -- feed back purpose. So I let this function keep the name.
416 -- Create_set_Ext_Link will be used for external object purpose. Only update
417 -- the current version.
418 -- The original Create_Set_Link accept one more param P_UPDATE_EXTRA_VERSIONSION.
419 --
420 PROCEDURE Create_Set_Link(
421 P_LINK_TYPE in VARCHAR,
422 P_OBJECT_CODE in VARCHAR,
423 P_SET_ID in NUMBER,
424 P_OTHER_ID in NUMBER,
425 P_ATTRIBUTE_CATEGORY in VARCHAR2,
426 P_ATTRIBUTE1 in VARCHAR2,
427 P_ATTRIBUTE2 in VARCHAR2,
428 P_ATTRIBUTE3 in VARCHAR2,
429 P_ATTRIBUTE4 in VARCHAR2,
430 P_ATTRIBUTE5 in VARCHAR2,
431 P_ATTRIBUTE6 in VARCHAR2,
432 P_ATTRIBUTE7 in VARCHAR2,
433 P_ATTRIBUTE8 in VARCHAR2,
434 P_ATTRIBUTE9 in VARCHAR2,
435 P_ATTRIBUTE10 in VARCHAR2,
436 P_ATTRIBUTE11 in VARCHAR2,
437 P_ATTRIBUTE12 in VARCHAR2,
438 P_ATTRIBUTE13 in VARCHAR2,
439 P_ATTRIBUTE14 in VARCHAR2,
440 P_ATTRIBUTE15 in VARCHAR2,
441 x_link_id in OUT NOCOPY NUMBER,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_data OUT NOCOPY VARCHAR2,
444 x_msg_count OUT NOCOPY NUMBER
445 ) IS
446 BEGIN
447 Create_Set_Link(
448 P_LINK_TYPE => P_LINK_TYPE ,
449 P_OBJECT_CODE => P_OBJECT_CODE ,
450 P_SET_ID => P_SET_ID ,
451 P_OTHER_ID => P_OTHER_ID ,
452 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
453 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
454 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
455 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
456 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
457 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
458 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
459 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
460 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
461 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
462 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
463 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
464 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
468 P_UPDATE_EXTRA_VERSION => 'Y' ,
465 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
466 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
467 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
469 x_link_id => x_link_id ,
470 x_return_status => x_return_status ,
471 x_msg_data => x_msg_data ,
472 x_msg_count => x_msg_count);
473 END;
474
475 --
476 -- Create a set link entry (old, called by jtt, wrapper of new api)
477 --
478 function Create_Set_Link(
479 P_LINK_TYPE in VARCHAR,
480 P_OBJECT_CODE in VARCHAR,
481 P_SET_ID in NUMBER,
482 P_OTHER_ID in NUMBER,
483 P_ATTRIBUTE_CATEGORY in VARCHAR2,
484 P_ATTRIBUTE1 in VARCHAR2,
485 P_ATTRIBUTE2 in VARCHAR2,
486 P_ATTRIBUTE3 in VARCHAR2,
487 P_ATTRIBUTE4 in VARCHAR2,
488 P_ATTRIBUTE5 in VARCHAR2,
489 P_ATTRIBUTE6 in VARCHAR2,
490 P_ATTRIBUTE7 in VARCHAR2,
491 P_ATTRIBUTE8 in VARCHAR2,
492 P_ATTRIBUTE9 in VARCHAR2,
493 P_ATTRIBUTE10 in VARCHAR2,
494 P_ATTRIBUTE11 in VARCHAR2,
495 P_ATTRIBUTE12 in VARCHAR2,
496 P_ATTRIBUTE13 in VARCHAR2,
497 P_ATTRIBUTE14 in VARCHAR2,
498 P_ATTRIBUTE15 in VARCHAR2
499 ) return number IS
500 l_link_id NUMBER;
501 l_return_status VARCHAR2(1);
502 l_msg_data VARCHAR2(2000);
503 l_msg_count NUMBER;
504 begin
505 Create_Set_Link(
506 P_LINK_TYPE => P_LINK_TYPE ,
507 P_OBJECT_CODE => P_OBJECT_CODE ,
508 P_SET_ID => P_SET_ID ,
509 P_OTHER_ID => P_OTHER_ID ,
510 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
511 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
512 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
513 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
514 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
515 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
516 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
517 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
518 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
519 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
520 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
521 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
522 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
523 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
524 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
525 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
526 P_UPDATE_EXTRA_VERSION => 'Y' ,
527 x_link_id => l_link_id ,
528 x_return_status => l_return_status ,
529 x_msg_data => l_msg_data ,
530 x_msg_count => l_msg_count);
531 if( l_return_status = FND_API.G_RET_STS_SUCCESS ) then
532 return l_link_id;
533 else
534 return ERROR_STATUS;
535 end if;
536 END Create_Set_Link;
537
538 -- create a set link entry
539 -- this function is same as the previous one, except, it defaults LINK_TYPE to PS
540 function Create_Set_Link(
541 P_OBJECT_CODE in VARCHAR,
542 P_SET_ID in NUMBER,
543 P_OTHER_ID in NUMBER,
544 P_ATTRIBUTE_CATEGORY in VARCHAR2,
545 P_ATTRIBUTE1 in VARCHAR2,
546 P_ATTRIBUTE2 in VARCHAR2,
547 P_ATTRIBUTE3 in VARCHAR2,
548 P_ATTRIBUTE4 in VARCHAR2,
549 P_ATTRIBUTE5 in VARCHAR2,
550 P_ATTRIBUTE6 in VARCHAR2,
551 P_ATTRIBUTE7 in VARCHAR2,
552 P_ATTRIBUTE8 in VARCHAR2,
553 P_ATTRIBUTE9 in VARCHAR2,
554 P_ATTRIBUTE10 in VARCHAR2,
555 P_ATTRIBUTE11 in VARCHAR2,
556 P_ATTRIBUTE12 in VARCHAR2,
557 P_ATTRIBUTE13 in VARCHAR2,
558 P_ATTRIBUTE14 in VARCHAR2,
559 P_ATTRIBUTE15 in VARCHAR2
560 ) return number IS
561 l_link_id NUMBER;
562 l_return_status VARCHAR2(1);
563 l_msg_data VARCHAR2(2000);
564 l_msg_count NUMBER;
565 begin
566 Create_Set_Link(
567 P_LINK_TYPE => 'PS' ,
568 P_OBJECT_CODE => P_OBJECT_CODE ,
569 P_SET_ID => P_SET_ID ,
570 P_OTHER_ID => P_OTHER_ID ,
571 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
572 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
573 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
574 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
575 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
576 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
577 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
578 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
579 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
580 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
581 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
582 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
583 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
584 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
585 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
586 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
587 P_UPDATE_EXTRA_VERSION => 'Y' ,
588 x_link_id => l_link_id ,
589 x_return_status => l_return_status ,
590 x_msg_data => l_msg_data ,
591 x_msg_count => l_msg_count);
592 if( l_return_status = FND_API.G_RET_STS_SUCCESS ) then
596 end if;
593 return l_link_id;
594 else
595 return ERROR_STATUS;
597 END;
598
599 PROCEDURE Create_Set_Ext_Link(
600 P_LINK_TYPE in VARCHAR,
601 P_OBJECT_CODE in VARCHAR,
602 P_SET_ID in NUMBER,
603 P_OTHER_ID in NUMBER,
604 P_ATTRIBUTE_CATEGORY in VARCHAR2,
605 P_ATTRIBUTE1 in VARCHAR2,
606 P_ATTRIBUTE2 in VARCHAR2,
607 P_ATTRIBUTE3 in VARCHAR2,
608 P_ATTRIBUTE4 in VARCHAR2,
609 P_ATTRIBUTE5 in VARCHAR2,
610 P_ATTRIBUTE6 in VARCHAR2,
611 P_ATTRIBUTE7 in VARCHAR2,
612 P_ATTRIBUTE8 in VARCHAR2,
613 P_ATTRIBUTE9 in VARCHAR2,
614 P_ATTRIBUTE10 in VARCHAR2,
615 P_ATTRIBUTE11 in VARCHAR2,
616 P_ATTRIBUTE12 in VARCHAR2,
617 P_ATTRIBUTE13 in VARCHAR2,
618 P_ATTRIBUTE14 in VARCHAR2,
619 P_ATTRIBUTE15 in VARCHAR2,
620 x_link_id in OUT NOCOPY NUMBER,
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_msg_data OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY NUMBER
624 ) IS
625 BEGIN
626 Create_Set_Link(
627 P_LINK_TYPE => P_LINK_TYPE ,
628 P_OBJECT_CODE => P_OBJECT_CODE ,
629 P_SET_ID => P_SET_ID ,
630 P_OTHER_ID => P_OTHER_ID ,
631 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
632 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
633 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
634 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
635 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
636 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
637 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
638 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
639 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
640 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
641 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
642 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
643 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
644 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
645 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
646 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
647 P_UPDATE_EXTRA_VERSION => 'N' ,
648 x_link_id => x_link_id ,
649 x_return_status => x_return_status ,
650 x_msg_data => x_msg_data ,
651 x_msg_count => x_msg_count);
652 END;
653
654
655 -- Update a set link entry
656 -- This is the api to use as of 11.5.10
657 procedure Update_Set_Link(
658 P_LINK_ID in NUMBER,
659 P_LINK_TYPE in VARCHAR,
660 P_OBJECT_CODE in VARCHAR,
661 P_SET_ID in NUMBER,
662 P_OTHER_ID in NUMBER,
663 P_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
664 P_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
665 P_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
666 P_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
667 P_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
668 P_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
669 P_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
670 P_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
671 P_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
672 P_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
673 P_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
674 P_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
675 P_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
676 P_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
677 P_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
678 P_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_data OUT NOCOPY VARCHAR2,
681 x_msg_count OUT NOCOPY NUMBER
682 ) IS
683
684 -- Validation Cursors
685 CURSOR Validate_Link IS
686 SELECT sl.set_id, sl.object_code, sl.other_id, sb.set_number, sb.status
687 FROM CS_KB_SET_LINKS sl, CS_KB_SETS_B sb
688 WHERE sl.link_id = P_LINK_ID
689 AND sl.set_id = sb.set_id
690 AND ( sb.status = 'PUB' OR sb.latest_version_flag = 'Y' );
691
692 -- Cursor to fetch link to latest in-progress version of a solution
693 CURSOR Get_Link_To_Latest_Ver(c_soln_number VARCHAR2, c_object_code VARCHAR2,
694 c_other_id NUMBER) IS
695 SELECT sl.link_id, sl.set_id
696 FROM CS_KB_SET_LINKS sl, CS_KB_SETS_B sb
697 WHERE sb.set_number = c_soln_number
698 AND sb.latest_version_flag = 'Y'
699 AND sb.set_id = sl.set_id
700 AND sl.object_code = c_object_code
701 AND sl.other_id = c_other_id;
702
703 -- Cursor to fetch link to published version of a solution
704 CURSOR Get_Link_To_Published_Ver(c_soln_number VARCHAR2, c_object_code VARCHAR2,
705 c_other_id NUMBER) IS
706 SELECT sl.link_id, sl.set_id
707 FROM CS_KB_SET_LINKS sl, CS_KB_SETS_B sb
708 WHERE sb.set_number = c_soln_number
709 AND sb.status = 'PUB'
710 AND sb.set_id = sl.set_id
711 AND sl.object_code = c_object_code
712 AND sl.other_id = c_other_id;
713
714 l_orig_set_id NUMBER;
715 l_orig_object_code VARCHAR2(30);
716 l_orig_other_id NUMBER;
717 l_orig_soln_number VARCHAR2(30);
718 l_orig_soln_status VARCHAR2(30);
719 l_other_link_id NUMBER;
720 l_other_soln_id NUMBER;
721
722 l_date date;
723 l_updated_by number;
724 l_login number;
725 BEGIN
726 SAVEPOINT Update_Link_SP;
727
731 OR P_OBJECT_CODE is null
728 -- Begin Validation
729 -- validate params
730 if( P_LINK_ID is null
732 OR P_SET_ID is NULL
733 OR P_OTHER_ID is null
734 OR p_link_type is null ) THEN
735 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
736 fnd_msg_pub.Add;
737 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
738 p_count => X_MSG_COUNT,
739 p_data => X_MSG_DATA);
740 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
741 return;
742 end if;
743
744 -- validate the link type
745 IF ( P_LINK_TYPE not in ('PS', 'S', 'NS') ) THEN
746 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_LINK_TYPE');
747 FND_MSG_PUB.ADD;
748 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
749 p_count => X_MSG_COUNT,
750 p_data => X_MSG_DATA);
751 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
752 return;
753 END IF;
754
755 -- validate that the link being updated exists and the link being
756 -- updated is to either a published or latest solution version.
757 OPEN Validate_Link;
758 FETCH Validate_Link INTO l_orig_set_id, l_orig_object_code, l_orig_other_id,
759 l_orig_soln_number, l_orig_soln_status;
760 IF ( Validate_Link%NOTFOUND ) THEN
761 CLOSE Validate_Link;
762 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_SOLN_LINK_ID');
763 FND_MSG_PUB.ADD;
764 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
765 p_count => X_MSG_COUNT,
766 p_data => X_MSG_DATA);
767 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
768 return;
769 ELSE
770 CLOSE Validate_Link;
771 END IF;
772
773 -- Validate that the update doesn't change the solution version
774 -- to which the link is pointing to and also doesn't change the
775 -- object code and id of the object it's pointing to. You can only
776 -- update the link type for an existing link.
777 IF ( l_orig_set_id <> P_SET_ID
778 OR l_orig_object_code <> P_OBJECT_CODE
779 OR l_orig_other_id <> P_OTHER_ID ) THEN
780 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_SOLN_LINK_UPDATE');
781 FND_MSG_PUB.ADD;
782 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
783 p_count => X_MSG_COUNT,
784 p_data => X_MSG_DATA);
785 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
786 return;
787 END IF;
788 -- End Validation
789
790 --prepare data, then update the solution link
791 l_date := sysdate;
792 l_updated_by := fnd_global.user_id;
793 l_login := fnd_global.login_id;
794
795 CS_KB_SET_LINKS_PKG.Update_Row(
796 X_Link_Id => p_link_id,
797 X_Link_type => p_link_type,
798 X_Object_Code => p_object_code,
799 X_Set_Id => p_set_id,
800 X_Other_Id => p_other_id,
801 X_Last_Update_Date => l_date,
802 X_Last_Updated_By => l_updated_by,
803 X_Last_Update_Login => l_login,
804 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
805 X_ATTRIBUTE1 => P_ATTRIBUTE1,
806 X_ATTRIBUTE2 => P_ATTRIBUTE2,
807 X_ATTRIBUTE3 => P_ATTRIBUTE3,
808 X_ATTRIBUTE4 => P_ATTRIBUTE4,
809 X_ATTRIBUTE5 => P_ATTRIBUTE5,
810 X_ATTRIBUTE6 => P_ATTRIBUTE6,
811 X_ATTRIBUTE7 => P_ATTRIBUTE7,
812 X_ATTRIBUTE8 => P_ATTRIBUTE8,
813 X_ATTRIBUTE9 => P_ATTRIBUTE9,
814 X_ATTRIBUTE10 => P_ATTRIBUTE10,
815 X_ATTRIBUTE11 => P_ATTRIBUTE11,
816 X_ATTRIBUTE12 => P_ATTRIBUTE12,
817 X_ATTRIBUTE13 => P_ATTRIBUTE13,
818 X_ATTRIBUTE14 => P_ATTRIBUTE14,
819 X_ATTRIBUTE15 => P_ATTRIBUTE15);
820
821 -- Raise Business Event when a solution link is updated
822 CS_KB_SET_LINKS_PKG.Raise_Soln_Link_Updated_Event(
823 p_set_id => p_set_id,
824 p_object_code => p_object_code,
825 p_object_id => p_other_id,
826 p_link_id => p_link_id,
827 p_link_type => p_link_type,
828 p_event_date => l_date );
829
830 -- If the link being updated is to a published version of a solution,
831 -- the same update needs to be made to the same link to the latest
832 -- version of the solution.
833 -- If the link being updated is to a WIP version, the same should be
834 -- propergated to the published version.
835 IF ( l_orig_soln_status = 'PUB') THEN
836 OPEN Get_Link_To_Latest_Ver(l_orig_soln_number, p_object_code, p_other_id);
837 FETCH Get_Link_To_Latest_Ver into l_other_link_id, l_other_soln_id;
838 IF ( Get_Link_To_Latest_Ver%NOTFOUND ) THEN
839 l_other_link_id := null;
840 l_other_soln_id := null;
841 END IF;
842 CLOSE Get_Link_To_Latest_Ver;
843 ELSE
844 OPEN Get_Link_To_Published_Ver(l_orig_soln_number, p_object_code, p_other_id);
845 FETCH Get_Link_To_Published_Ver into l_other_link_id, l_other_soln_id;
846 IF ( Get_Link_To_Published_Ver%NOTFOUND ) THEN
847 l_other_link_id := null;
848 l_other_soln_id := null;
849 END IF;
850 CLOSE Get_Link_To_Published_Ver;
851 END IF;
852 IF ( l_other_link_id is not null
853 and l_other_link_id <> p_link_id ) THEN
854 CS_KB_SET_LINKS_PKG.Update_Row(
855 X_Link_Id => l_other_link_id,
859 X_Other_Id => p_other_id,
856 X_Link_type => p_link_type,
857 X_Object_Code => p_object_code,
858 X_Set_Id => l_other_soln_id,
860 X_Last_Update_Date => l_date,
861 X_Last_Updated_By => l_updated_by,
862 X_Last_Update_Login => l_login,
863 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
864 X_ATTRIBUTE1 => P_ATTRIBUTE1,
865 X_ATTRIBUTE2 => P_ATTRIBUTE2,
866 X_ATTRIBUTE3 => P_ATTRIBUTE3,
867 X_ATTRIBUTE4 => P_ATTRIBUTE4,
868 X_ATTRIBUTE5 => P_ATTRIBUTE5,
869 X_ATTRIBUTE6 => P_ATTRIBUTE6,
870 X_ATTRIBUTE7 => P_ATTRIBUTE7,
871 X_ATTRIBUTE8 => P_ATTRIBUTE8,
872 X_ATTRIBUTE9 => P_ATTRIBUTE9,
873 X_ATTRIBUTE10 => P_ATTRIBUTE10,
874 X_ATTRIBUTE11 => P_ATTRIBUTE11,
875 X_ATTRIBUTE12 => P_ATTRIBUTE12,
876 X_ATTRIBUTE13 => P_ATTRIBUTE13,
877 X_ATTRIBUTE14 => P_ATTRIBUTE14,
878 X_ATTRIBUTE15 => P_ATTRIBUTE15);
879 END IF;
880 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
881
882 exception
883 when others then
884 ROLLBACK TO Update_Link_SP;
885 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
886 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, 'cs.plsql.cs_kb_set_links.update_set_link', SQLERRM );
887 end if;
888 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
889 FND_MSG_PUB.Add;
890 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
891
892 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
893 p_count => x_msg_count,
894 p_data => x_msg_data);
895 END Update_Set_Link;
896
897 -- Deprecated as of 11.5.10. Call new Update_Set_link spec.
898 function Update_Set_Link(
899 P_LINK_ID in NUMBER,
900 P_LINK_TYPE in VARCHAR,
901 P_OBJECT_CODE in VARCHAR,
902 P_SET_ID in NUMBER,
903 P_OTHER_ID in NUMBER,
904 P_ATTRIBUTE_CATEGORY in VARCHAR2,
905 P_ATTRIBUTE1 in VARCHAR2,
906 P_ATTRIBUTE2 in VARCHAR2,
907 P_ATTRIBUTE3 in VARCHAR2,
908 P_ATTRIBUTE4 in VARCHAR2,
909 P_ATTRIBUTE5 in VARCHAR2,
910 P_ATTRIBUTE6 in VARCHAR2,
911 P_ATTRIBUTE7 in VARCHAR2,
912 P_ATTRIBUTE8 in VARCHAR2,
913 P_ATTRIBUTE9 in VARCHAR2,
914 P_ATTRIBUTE10 in VARCHAR2,
915 P_ATTRIBUTE11 in VARCHAR2,
916 P_ATTRIBUTE12 in VARCHAR2,
917 P_ATTRIBUTE13 in VARCHAR2,
918 P_ATTRIBUTE14 in VARCHAR2,
919 P_ATTRIBUTE15 in VARCHAR2
920 ) return number is
921 l_return_status VARCHAR2(1);
922 l_msg_data VARCHAR2(2000);
923 l_msg_count NUMBER;
924 begin
925 Update_Set_Link(
926 P_LINK_ID => P_LINK_ID ,
927 P_LINK_TYPE => P_LINK_TYPE ,
928 P_OBJECT_CODE => P_OBJECT_CODE ,
929 P_SET_ID => P_SET_ID ,
930 P_OTHER_ID => P_OTHER_ID ,
931 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
932 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
933 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
934 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
935 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
936 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
937 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
938 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
939 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
940 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
941 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
942 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
943 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
944 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
945 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
946 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
947 x_return_status => l_return_status ,
948 x_msg_data => l_msg_data ,
949 x_msg_count => l_msg_count);
950 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
951 return ERROR_STATUS;
952 end if;
953 return OKAY_STATUS;
954 end Update_Set_Link;
955
956 function Delete_Set_Link_W_Obj_Code(
957 p_set_id in Number,
958 p_object_code in Varchar2,
959 p_other_id in Number
960 ) return number is
961 cursor get_link_id( c_set_id NUMBER, c_object_code VARCHAR2,
962 c_other_id NUMBER )IS
963 select link_id
964 from cs_kb_set_links
965 where set_id = c_set_id
966 and object_code = c_object_code
967 and other_id = c_other_id;
968
969 l_link_id NUMBER;
970 l_ret_val NUMBER;
971 begin
972 if(P_SET_ID is null or
973 P_OBJECT_CODE is null OR P_OTHER_ID is NULL ) then
974 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
975 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
976 fnd_msg_pub.Add;
977 end if;
978 return ERROR_STATUS;
979 end if;
980
981 OPEN get_link_id( p_set_id, p_object_code, p_other_id );
982 FETCH get_link_id INTO l_link_id;
983 IF ( get_link_id%NOTFOUND ) THEN
984 CLOSE get_link_id;
985 return ERROR_STATUS;
986 ELSE
987 CLOSE get_link_id;
988 l_ret_val := Delete_Set_Link( l_link_id );
989 return l_ret_val;
990 END IF;
991 EXCEPTION
992 WHEN OTHERS THEN
996
993 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
994 return ERROR_STATUS;
995 end Delete_Set_Link_W_Obj_Code;
997
998 function Delete_Set_Link(
999 P_LINK_ID in NUMBER
1000 ) return number is
1001 -- Cursor to validate link exists and is to either a published solution
1002 -- version or an in-progress version.
1003 cursor Valid_Link IS
1004 select sb.set_number, sb.status, sl.object_code, sl.other_id
1005 from cs_kb_set_links sl, cs_kb_sets_b sb
1006 where sl.link_id = P_LINK_ID
1007 and sl.set_id = sb.set_id
1008 and ( sb.status = 'PUB' or sb.latest_version_flag = 'Y' );
1009
1010 -- Cursor to fetch link to latest in-progress version of a solution
1011 CURSOR Get_Link_To_Other_Ver(c_soln_number VARCHAR2, c_object_code VARCHAR2,
1012 c_other_id NUMBER) IS
1013 SELECT sl.link_id
1014 FROM CS_KB_SET_LINKS sl, CS_KB_SETS_B sb
1015 WHERE sb.set_number = c_soln_number
1016 AND (sb.latest_version_flag = 'Y' or sb.status = 'PUB')
1017 AND sb.set_id = sl.set_id
1018 AND sl.object_code = c_object_code
1019 AND sl.other_id = c_other_id;
1020
1021 l_soln_number VARCHAR2(30);
1022 l_soln_status VARCHAR2(30);
1023 l_link_object_code VARCHAR2(30);
1024 l_link_other_id NUMBER;
1025 l_other_link_id NUMBER;
1026 begin
1027 SAVEPOINT Delete_Link_SP;
1028
1029 -- Begin Validation
1030
1031 -- validate parameter
1032 if (P_LINK_ID is null ) then
1033 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1034 fnd_message.set_name('CS', 'CS_KB_C_MISS_PARAM');
1035 fnd_msg_pub.Add;
1036 end if;
1037 return ERROR_STATUS;
1038 end if;
1039
1040 -- make sure the link exists and is to either a published solution version
1041 -- or the latest in progress version
1042 OPEN Valid_Link;
1043 FETCH Valid_Link into l_soln_number, l_soln_status, l_link_object_code,
1044 l_link_other_id;
1045 IF( Valid_Link%NOTFOUND ) THEN
1046 CLOSE Valid_Link;
1047 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1048 fnd_message.set_name('CS', 'CS_KB_INV_API_SOLN_LINK_ID');
1049 fnd_msg_pub.Add;
1050 end if;
1051 return ERROR_STATUS;
1052 END IF;
1053 CLOSE Valid_Link;
1054
1055 -- End Validation
1056
1057 delete from CS_KB_SET_LINKS
1058 where LINK_ID = P_LINK_ID;
1059
1060 -- If the link being deleted is to the published version of a solution,
1061 -- see if there is a corresponding link to the latest in-progress version.
1062 -- If so, then delete
1063 -- If the link being deleted is to a WIP version, check if there is a link
1064 -- to the published version, if yes. Delete it.
1065 OPEN Get_Link_To_Other_Ver(l_soln_number, l_link_object_code,
1066 l_link_other_id);
1067 FETCH Get_Link_To_Other_Ver into l_other_link_id;
1068 IF ( Get_Link_To_Other_Ver%NOTFOUND ) THEN
1069 l_other_link_id := null;
1070 END IF;
1071 CLOSE Get_Link_To_Other_Ver;
1072
1073 -- The link which link id is P_LINK_ID is already deleted. l_link_other_id is
1074 -- not null, then it must be the extra verion to be deleted.
1075 IF ( l_other_link_id is not null) THEN
1076 delete from CS_KB_SET_LINKS
1077 where LINK_ID = l_other_link_id;
1078 END IF;
1079
1080 return OKAY_STATUS;
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 ROLLBACK TO Delete_Link_SP;
1084 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1085 fnd_log.string(fnd_log.LEVEL_UNEXPECTED, 'cs.plsql.cs_kb_set_links.delete_set_link', SQLERRM );
1086 end if;
1087 if fnd_msg_pub.Check_Msg_Level( fnd_msg_pub.G_MSG_LVL_ERROR) then
1088 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1089 FND_MSG_PUB.Add;
1090 END IF;
1091 return ERROR_STATUS;
1092 end Delete_Set_Link;
1093
1094 procedure INSERT_ROW (
1095 X_ROWID in OUT NOCOPY VARCHAR2,
1096 X_LINK_ID in NUMBER,
1097 X_LINK_TYPE in VARCHAR2,
1098 X_OBJECT_CODE in VARCHAR2,
1099 X_SET_ID in NUMBER,
1100 X_OTHER_ID in NUMBER,
1101 --X_OTHER_CODE in VARCHAR2,
1102 X_CREATION_DATE in DATE,
1103 X_CREATED_BY in NUMBER,
1104 X_LAST_UPDATE_DATE in DATE,
1105 X_LAST_UPDATED_BY in NUMBER,
1106 X_LAST_UPDATE_LOGIN in NUMBER,
1107 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1108 X_ATTRIBUTE1 in VARCHAR2,
1109 X_ATTRIBUTE2 in VARCHAR2,
1110 X_ATTRIBUTE3 in VARCHAR2,
1111 X_ATTRIBUTE4 in VARCHAR2,
1112 X_ATTRIBUTE5 in VARCHAR2,
1113 X_ATTRIBUTE6 in VARCHAR2,
1114 X_ATTRIBUTE7 in VARCHAR2,
1115 X_ATTRIBUTE8 in VARCHAR2,
1116 X_ATTRIBUTE9 in VARCHAR2,
1117 X_ATTRIBUTE10 in VARCHAR2,
1118 X_ATTRIBUTE11 in VARCHAR2,
1119 X_ATTRIBUTE12 in VARCHAR2,
1120 X_ATTRIBUTE13 in VARCHAR2,
1121 X_ATTRIBUTE14 in VARCHAR2,
1122 X_ATTRIBUTE15 in VARCHAR2) IS
1123
1124 cursor C is select ROWID from CS_KB_SET_LINKS where LINK_ID = X_LINK_ID;
1125
1126
1127 BEGIN
1128
1129
1130 insert into CS_KB_SET_LINKS (
1131 LINK_ID,
1132 LINK_TYPE,
1136 --OTHER_CODE,
1133 OBJECT_CODE,
1134 SET_ID,
1135 OTHER_ID,
1137 CREATION_DATE,
1138 CREATED_BY,
1139 LAST_UPDATE_DATE,
1140 LAST_UPDATED_BY,
1141 LAST_UPDATE_LOGIN,
1142 ATTRIBUTE_CATEGORY,
1143 ATTRIBUTE1,
1144 ATTRIBUTE2,
1145 ATTRIBUTE3,
1146 ATTRIBUTE4,
1147 ATTRIBUTE5,
1148 ATTRIBUTE6,
1149 ATTRIBUTE7,
1150 ATTRIBUTE8,
1151 ATTRIBUTE9,
1152 ATTRIBUTE10,
1153 ATTRIBUTE11,
1154 ATTRIBUTE12,
1155 ATTRIBUTE13,
1156 ATTRIBUTE14,
1157 ATTRIBUTE15
1158 ) values (
1159 X_LINK_ID,
1160 X_LINK_TYPE,
1161 X_OBJECT_CODE,
1162 X_SET_ID,
1163 X_OTHER_ID,
1164 --X_OTHER_CODE,
1165 X_CREATION_DATE,
1166 X_CREATED_BY,
1167 X_LAST_UPDATE_DATE,
1168 X_LAST_UPDATED_BY,
1169 X_LAST_UPDATE_LOGIN,
1170 X_ATTRIBUTE_CATEGORY,
1171 X_ATTRIBUTE1,
1172 X_ATTRIBUTE2,
1173 X_ATTRIBUTE3,
1174 X_ATTRIBUTE4,
1175 X_ATTRIBUTE5,
1176 X_ATTRIBUTE6,
1177 X_ATTRIBUTE7,
1178 X_ATTRIBUTE8,
1179 X_ATTRIBUTE9,
1180 X_ATTRIBUTE10,
1181 X_ATTRIBUTE11,
1182 X_ATTRIBUTE12,
1183 X_ATTRIBUTE13,
1184 X_ATTRIBUTE14,
1185 X_ATTRIBUTE15
1186 );
1187
1188 open c;
1189 fetch c into X_ROWID;
1190
1191 if (c%notfound) then
1192 close c;
1193 raise no_data_found;
1194 end if;
1195
1196 close c;
1197
1198 END INSERT_ROW;
1199
1200 procedure UPDATE_ROW (
1201 X_LINK_ID in NUMBER,
1202 X_LINK_TYPE in VARCHAR2,
1203 X_OBJECT_CODE in VARCHAR2,
1204 X_SET_ID in NUMBER,
1205 X_OTHER_ID in NUMBER,
1206 --X_OTHER_CODE in VARCHAR2,
1207 X_LAST_UPDATE_DATE in DATE,
1208 X_LAST_UPDATED_BY in NUMBER,
1209 X_LAST_UPDATE_LOGIN in NUMBER,
1210 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1211 X_ATTRIBUTE1 in VARCHAR2,
1212 X_ATTRIBUTE2 in VARCHAR2,
1213 X_ATTRIBUTE3 in VARCHAR2,
1214 X_ATTRIBUTE4 in VARCHAR2,
1215 X_ATTRIBUTE5 in VARCHAR2,
1216 X_ATTRIBUTE6 in VARCHAR2,
1217 X_ATTRIBUTE7 in VARCHAR2,
1218 X_ATTRIBUTE8 in VARCHAR2,
1219 X_ATTRIBUTE9 in VARCHAR2,
1220 X_ATTRIBUTE10 in VARCHAR2,
1221 X_ATTRIBUTE11 in VARCHAR2,
1222 X_ATTRIBUTE12 in VARCHAR2,
1223 X_ATTRIBUTE13 in VARCHAR2,
1224 X_ATTRIBUTE14 in VARCHAR2,
1225 X_ATTRIBUTE15 in VARCHAR2
1226 ) IS
1227
1228 BEGIN
1229
1230 update CS_KB_SET_LINKS set
1231
1232 LINK_TYPE = X_LINK_TYPE,
1233 OBJECT_CODE = X_OBJECT_CODE,
1234 SET_ID = X_SET_ID,
1235 OTHER_ID = X_OTHER_ID,
1236 --OTHER_CODE = X_OTHER_CODE,
1237 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1238 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1239 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1240 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
1241 ATTRIBUTE1 = X_ATTRIBUTE1,
1242 ATTRIBUTE2 = X_ATTRIBUTE2,
1243 ATTRIBUTE3 = X_ATTRIBUTE3,
1244 ATTRIBUTE4 = X_ATTRIBUTE4,
1245 ATTRIBUTE5 = X_ATTRIBUTE5,
1246 ATTRIBUTE6 = X_ATTRIBUTE6,
1247 ATTRIBUTE7 = X_ATTRIBUTE7,
1248 ATTRIBUTE8 = X_ATTRIBUTE8,
1249 ATTRIBUTE9 = X_ATTRIBUTE9,
1250 ATTRIBUTE10 = X_ATTRIBUTE10,
1251 ATTRIBUTE11 = X_ATTRIBUTE11,
1252 ATTRIBUTE12 = X_ATTRIBUTE12,
1253 ATTRIBUTE13 = X_ATTRIBUTE13,
1254 ATTRIBUTE14 = X_ATTRIBUTE14,
1255 ATTRIBUTE15 = X_ATTRIBUTE15
1256 where LINK_ID = X_LINK_ID;
1257
1258 if (sql%notfound) then
1259 raise no_data_found;
1260 end if;
1261
1262 END UPDATE_ROW;
1263
1264 procedure Raise_Solution_Linked_Event(
1265 p_set_id NUMBER,
1266 p_object_code VARCHAR2,
1267 p_object_id VARCHAR2,
1268 p_link_id NUMBER,
1269 p_link_type VARCHAR2,
1270 p_event_date DATE
1271 ) is
1272 l_param_list wf_parameter_list_t;
1273 l_set_number VARCHAR2(30);
1274 l_status VARCHAR2(30);
1275 l_access_level NUMBER;
1276 begin
1277 -- NOTE: access level for solutions has no meaning for 11510 and beyond.
1278 select set_number, access_level, status
1279 into l_set_number, l_access_level, l_status
1280 from CS_KB_SETS_B
1281 where set_id = p_set_id;
1282
1283 /*** Set up the parameter list ***/
1284 wf_event.AddParameterToList(
1285 p_name => 'SOLUTION_NUMBER',
1286 p_value => l_set_number,
1287 p_parameterlist => l_param_list
1288 );
1289
1290 wf_event.AddParameterToList(
1291 p_name => 'OBJECT_CODE',
1292 p_value => p_object_code,
1293 p_parameterlist => l_param_list
1294 );
1295
1296 wf_event.AddParameterToList(
1297 p_name => 'OBJECT_ID',
1298 p_value => p_object_id,
1299 p_parameterlist => l_param_list
1300 );
1301
1302 wf_event.AddParameterToList(
1303 p_name => 'LINK_ID',
1304 p_value => p_link_id,
1305 p_parameterlist => l_param_list
1306 );
1307
1308 wf_event.AddParameterToList(
1309 p_name => 'LINK_TYPE',
1310 p_value => p_link_type,
1311 p_parameterlist => l_param_list
1312 );
1313
1314 wf_event.AddParameterToList(
1315 p_name => 'STATUS',
1316 p_value => l_status,
1317 p_parameterlist => l_param_list
1318 );
1319
1320 wf_event.AddParameterToList(
1321 p_name => 'ACCESS_LEVEL',
1322 p_value => l_access_level,
1323 p_parameterlist => l_param_list
1324 );
1325
1326 wf_event.AddParameterToList(
1327 p_name => 'USER_ID',
1328 p_value => fnd_global.user_id,
1329 p_parameterlist => l_param_list
1330 );
1331
1332 wf_event.AddParameterToList(
1333 p_name => 'RESP_ID',
1334 p_value => fnd_global.resp_id,
1335 p_parameterlist => l_param_list
1336 );
1337
1338 wf_event.AddParameterToList(
1339 p_name => 'RESP_APPL_ID',
1340 p_value => fnd_global.resp_appl_id,
1341 p_parameterlist => l_param_list
1342 );
1343
1344 wf_event.AddParameterToList(
1345 p_name => 'EVENT_DATE',
1346 p_value => to_char(p_event_date),
1347 p_parameterlist => l_param_list
1348 );
1349
1350 /*** Raise SolutionLinked event ***/
1351 wf_event.raise(
1352 p_event_name => 'oracle.apps.cs.knowledge.SolutionLinked',
1353 p_event_key => to_char( sysdate, 'YYYYMMDD HH24MISS') ,
1354 p_parameters => l_param_list
1355 );
1356
1357 l_param_list.DELETE;
1358
1359 end Raise_Solution_Linked_Event;
1360
1361 procedure Raise_Soln_Link_Updated_Event(
1362 p_set_id NUMBER,
1363 p_object_code VARCHAR2,
1364 p_object_id VARCHAR2,
1365 p_link_id NUMBER,
1366 p_link_type VARCHAR2,
1367 p_event_date DATE
1368 ) is
1369 l_param_list wf_parameter_list_t;
1370 l_set_number VARCHAR2(30);
1371 l_status VARCHAR2(30);
1372 begin
1373 select set_number, status
1374 into l_set_number, l_status
1375 from CS_KB_SETS_B
1376 where set_id = p_set_id;
1377
1378 /*** Set up the parameter list ***/
1379 wf_event.AddParameterToList(
1380 p_name => 'LINK_ID',
1381 p_value => p_link_id,
1382 p_parameterlist => l_param_list
1383 );
1384
1385 wf_event.AddParameterToList(
1386 p_name => 'SOLUTION_NUMBER',
1387 p_value => l_set_number,
1388 p_parameterlist => l_param_list
1389 );
1390
1391 wf_event.AddParameterToList(
1392 p_name => 'OBJECT_CODE',
1393 p_value => p_object_code,
1394 p_parameterlist => l_param_list
1395 );
1396
1397 wf_event.AddParameterToList(
1398 p_name => 'OBJECT_ID',
1399 p_value => p_object_id,
1400 p_parameterlist => l_param_list
1401 );
1402
1403 wf_event.AddParameterToList(
1404 p_name => 'LINK_TYPE',
1405 p_value => p_link_type,
1406 p_parameterlist => l_param_list
1407 );
1408
1409 wf_event.AddParameterToList(
1410 p_name => 'STATUS',
1411 p_value => l_status,
1412 p_parameterlist => l_param_list
1413 );
1414
1415 wf_event.AddParameterToList(
1416 p_name => 'USER_ID',
1417 p_value => fnd_global.user_id,
1418 p_parameterlist => l_param_list
1419 );
1420
1421 wf_event.AddParameterToList(
1422 p_name => 'RESP_ID',
1423 p_value => fnd_global.resp_id,
1424 p_parameterlist => l_param_list
1425 );
1426
1427 wf_event.AddParameterToList(
1428 p_name => 'RESP_APPL_ID',
1429 p_value => fnd_global.resp_appl_id,
1430 p_parameterlist => l_param_list
1431 );
1432
1433 wf_event.AddParameterToList(
1434 p_name => 'EVENT_DATE',
1435 p_value => to_char(p_event_date),
1436 p_parameterlist => l_param_list
1437 );
1438
1439 /*** Raise SolutionLinkUpdated event ***/
1440 wf_event.raise(
1441 p_event_name => 'oracle.apps.cs.knowledge.SolutionLink.Updated',
1442 p_event_key => to_char( sysdate, 'YYYYMMDD HH24MISS') ,
1443 p_parameters => l_param_list
1444 );
1445
1446 l_param_list.DELETE;
1447
1448 end Raise_Soln_Link_Updated_Event;
1449
1450
1451 end CS_KB_SET_LINKS_PKG;