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