[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SECURITY_PVT
Source
1 PACKAGE BODY CS_KB_SECURITY_PVT AS
2 /* $Header: cskvksb.pls 120.1 2005/10/05 15:08:34 mkettle noship $ */
3
4 /*=======================================================================+
5 | Private Security Apis |
6 | ***** Visibility ***** |
7 | - Does_Visibility_Name_Exist |
8 | - Create_Visibility |
9 | - Update_Visibility |
10 | - Delete_Visibility |
11 +=======================================================================*/
12
13 -- Start of comments
14 -- API name : DOES_VISIBILITY_NAME_EXIST
15 -- Type : Private Function
16 -- Function : Validates if the Visibility Name is duplicate
17 -- Pre-reqs : None.
18 -- Parameters :
19 -- IN : P_VISIBILITY_ID NUMBER Required
20 -- P_NAME VARCHAR Required
21 -- OUT :
22 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
23 --
24 -- History:
25 -- 07-Jul-03 Matt Kettle Created
26 -- 24-Sep-03 Matt Kettle Changed Check_Name_Exists to check dates
27 --
28 --
29 --
30 -- Notes :
31 -- 1) We only validate duplicate Name NOT description as well
32 --
33 -- End of comments
34
35 FUNCTION DOES_VISIBILITY_NAME_EXIST (
36 P_VISIBILITY_ID NUMBER,
37 P_NAME VARCHAR2
38 ) RETURN VARCHAR2 IS
39
40 CURSOR Check_Name_Exists IS
41 SELECT count(*)
42 FROM CS_KB_VISIBILITIES_VL
43 WHERE name = P_NAME
44 AND sysdate BETWEEN nvl(Start_Date_Active, SYSDATE-1)
45 AND nvl(End_Date_Active, SYSDATE+1)
46 AND visibility_id <> P_VISIBILITY_ID;
47
48 l_count NUMBER :=0;
49 l_return VARCHAR2(10) := 'TRUE';
50
51 BEGIN
52 OPEN Check_Name_Exists;
53 FETCH Check_Name_Exists INTO l_count;
54 CLOSE Check_Name_Exists;
55 --dbms_output.put_line('Dup Count ='||l_count);
56
57 IF l_count <> 0 THEN
58 l_return := 'TRUE';
59 ELSE
60 l_return := 'FALSE';
61 END IF;
62 --dbms_output.put_line('Return ='||l_return);
63 RETURN l_return;
64
65 END DOES_VISIBILITY_NAME_EXIST;
66
67 ---------------------------------------------------------------------
68 -- Visibilities are setup as follows:
69 --
70 -- Id Name Position
71 ---------------------------------------------------------------------
72 -- 2 External 3000 --> Least Restrictive (Public)
73 -- 4 Limited 2000
74 -- 1 Internal 1000 --> Most Restrictive (Private)
75 --
76 -- Therefore the lower the Position the More Restrictive (Sensitive)
77 ---------------------------------------------------------------------
78 -- Start of comments
79 -- API name : CREATE_VISIBILITY
80 -- Type : Private
81 -- Function : Create New Visibility Levels
82 -- Pre-reqs : None.
83 -- Parameters :
84 -- IN : P_VISIBILITY_ID NUMBER Optional
85 -- P_ADD_BEFORE_POSITION NUMBER Optional
86 -- P_ADD_AFTER_POSITION NUMBER Required
87 -- P_START_DATE_ACTIVE DATE Optional
88 -- P_END_DATE_ACTIVE DATE Optional
89 -- P_NAME VARCHAR Required
90 -- P_DESCRIPTION VARCHAR Optional
91 --
92 -- OUT : x_return_status VARCHAR2(1)
93 -- x_msg_count NUMBER
94 -- x_msg_data VARCHAR2(2000)
95 --
96 -- History:
97 -- 07-Jul-03 Matt Kettle Created
98 -- 15-Aug-03 Matt Kettle added call to CS_KB_SYNC_INDEX_PKG
99 -- to request_mark_idx_on_sec_change
100 -- 18-Sep-03 Matt Kettle Visibility UI now shows most public
101 -- at the top. There I have switched the add before
102 -- and after logic
103 --
104 -- Notes :
105 -- 1) If P_VISIBILITY_ID is passed as null it will be
106 -- generated via the sequence
107 -- 2) Pass the visibility_id into P_ADD_BEFORE_VISIBILITY or
108 -- P_ADD_AFTER_VISIBILITY to enable creating the Visibility
109 -- in the correct place in the linear scale. If both these
110 -- params are null, the visibility will be added at the end.
111 --
112 -- Validations :
113 -- 1) Required Parameters
114 -- 2) Check Duplicate Visibility Name
115 -- End of comments
116
117 PROCEDURE CREATE_VISIBILITY (
118 P_VISIBILITY_ID IN NUMBER,
119 P_ADD_BEFORE_VISIBILITY IN NUMBER,
120 P_ADD_AFTER_VISIBILITY IN NUMBER,
121 P_START_DATE_ACTIVE IN DATE,
122 P_END_DATE_ACTIVE IN DATE,
123 P_NAME IN VARCHAR2,
124 P_DESCRIPTION IN VARCHAR2,
125 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
126 P_ATTRIBUTE1 IN VARCHAR2,
127 P_ATTRIBUTE2 IN VARCHAR2,
128 P_ATTRIBUTE3 IN VARCHAR2,
129 P_ATTRIBUTE4 IN VARCHAR2,
130 P_ATTRIBUTE5 IN VARCHAR2,
131 P_ATTRIBUTE6 IN VARCHAR2,
132 P_ATTRIBUTE7 IN VARCHAR2,
133 P_ATTRIBUTE8 IN VARCHAR2,
134 P_ATTRIBUTE9 IN VARCHAR2,
135 P_ATTRIBUTE10 IN VARCHAR2,
136 P_ATTRIBUTE11 IN VARCHAR2,
137 P_ATTRIBUTE12 IN VARCHAR2,
138 P_ATTRIBUTE13 IN VARCHAR2,
139 P_ATTRIBUTE14 IN VARCHAR2,
140 P_ATTRIBUTE15 IN VARCHAR2,
141 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
142 X_MSG_DATA OUT NOCOPY VARCHAR2,
143 X_MSG_COUNT OUT NOCOPY NUMBER
144 ) IS
145
146 l_check NUMBER := 0;
147 l_current_position NUMBER;
148 l_current_user NUMBER := FND_GLOBAL.user_id;
149 l_date DATE := SYSDATE;
150 l_login NUMBER := FND_GLOBAL.login_id;
151 l_new_position NUMBER;
152 l_position NUMBER;
153 l_position_check NUMBER :=0;
154 l_rowid VARCHAR2(30) := null;
155 l_seq NUMBER;
156 l_request_id NUMBER;
157 l_return_status VARCHAR2(1);
158
159 CURSOR Check_Insert (v_vis_id NUMBER) IS
160 SELECT COUNT(*)
161 FROM CS_KB_VISIBILITIES_B
162 WHERE VISIBILITY_ID = v_vis_id;
163
164 CURSOR Get_Max_Position IS
165 SELECT nvl(max(position),0)+1
166 FROM CS_KB_VISIBILITIES_B;
167
168 CURSOR Check_Position (v_position NUMBER) IS
169 SELECT COUNT(*)
170 FROM CS_KB_VISIBILITIES_B
171 WHERE position = v_position;
172
173 CURSOR Get_Position (v_vis NUMBER) IS
174 SELECT position
175 FROM CS_KB_VISIBILITIES_B
176 WHERE visibility_id = v_vis;
177
178 BEGIN
179
180 SAVEPOINT Create_Visibility_PVT;
181 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
182
183 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
184 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.begin',
185 'User='||l_current_user);
186 END IF;
187
188 -- Validate Required Parameters have been passed into api
189 IF P_NAME is null OR
190 P_ADD_BEFORE_VISIBILITY IS NOT NULL AND
191 P_ADD_AFTER_VISIBILITY IS NOT NULL THEN
192
193 RAISE INVALID_IN_PARAMETERS;
194
195 ELSE
196
197 -- If Visibility is null (Not called from OA) then generate from
198 -- the sequence.
199 IF P_VISIBILITY_ID IS NULL THEN
200 SELECT CS_KB_VISIBILITIES_B_S.nextval INTO l_seq from dual;
201 ELSE
202 l_seq := P_VISIBILITY_ID;
203 END IF;
204 --dbms_output.put_line('Sequence is ='||l_seq);
205 -- Validate that the Visibility Name is not duplicate
206 IF (DOES_VISIBILITY_NAME_EXIST(l_seq, P_NAME) = 'TRUE') THEN
207 -- Visibility Name is Duplicate
208 RAISE DUPLICATE_VISIBILITY;
209 ELSE
210 --dbms_output.put_line('Before Vis:='||P_ADD_BEFORE_VISIBILITY);
211
212 IF P_ADD_BEFORE_VISIBILITY IS NULL AND
213 P_ADD_AFTER_VISIBILITY IS NULL THEN
214 -- If No Position Specified, add at the End i.e. Highest Visibility
215 OPEN Get_Max_Position;
216 FETCH Get_Max_Position INTO l_position;
217 CLOSE Get_Max_Position;
218 ELSE
219 -- Set new Position
220 IF P_ADD_BEFORE_VISIBILITY IS NOT NULL THEN
221
222 OPEN Get_Position (P_ADD_BEFORE_VISIBILITY);
223 FETCH Get_Position INTO l_current_position;
224 CLOSE Get_Position;
225
226 l_new_position := l_current_position+1;
227 IF l_new_position is null THEN
228 RAISE INVALID_IN_PARAMETERS;
229 END IF;
230
231 ELSE
232 OPEN Get_Position (P_ADD_AFTER_VISIBILITY);
233 FETCH Get_Position INTO l_current_position;
234 CLOSE Get_Position;
235
236 l_new_position := l_current_position;
237 IF l_new_position is null THEN
238 RAISE INVALID_IN_PARAMETERS;
239 END IF;
240 END IF;
241 --dbms_output.put_line('New proposed position='||l_new_position);
242 -- Check if requested position is used yet or not
243 OPEN Check_Position (l_new_position);
244 FETCH Check_Position INTO l_position_check;
245 CLOSE Check_Position;
246 --dbms_output.put_line('Check Position='||l_position_check);
247 -- If position not used then Use it
248 IF l_position_check = 0 THEN
249 l_position := l_new_position;
250 ELSE
251 -- If position is used already, Move all Visibilities
252 -- from that position onwards down the list by one.
253 -- i.e. Update their positions by Adding 1
254 --dbms_output.put_line('Else Upd');
255
256 UPDATE CS_KB_VISIBILITIES_B
257 SET Position = Position + 1
258 WHERE Position >= l_new_position;
259
260 UPDATE CS_KB_CAT_GROUP_DENORM
261 SET Visibility_Position = Visibility_Position + 1
262 WHERE Visibility_Position >= l_new_position;
263
264 l_position := l_new_position;
265 END IF; --Check if Position already used
266
267 END IF; -- Check new VL positioning
268
269 --dbms_output.put_line('New Sequence-'||l_seq);
270 CS_KB_VISIBILITIES_PKG.INSERT_ROW(
271 X_ROWID => l_rowid,
272 X_VISIBILITY_ID => l_seq ,
273 X_POSITION => l_position,
274 X_START_DATE_ACTIVE => p_start_date_active,
275 X_END_DATE_ACTIVE => p_end_date_active,
276 X_NAME => p_name,
277 X_DESCRIPTION => p_description,
278 X_CREATION_DATE => l_date,
279 X_CREATED_BY => l_current_user,
280 X_LAST_UPDATE_DATE => l_date,
281 X_LAST_UPDATED_BY => l_current_user,
282 X_LAST_UPDATE_LOGIN => l_login,
283 X_ATTRIBUTE_CATEGORY => p_attribute_category,
284 X_ATTRIBUTE1 => p_attribute1,
285 X_ATTRIBUTE2 => p_attribute2,
286 X_ATTRIBUTE3 => p_attribute3,
287 X_ATTRIBUTE4 => p_attribute4,
288 X_ATTRIBUTE5 => p_attribute5,
289 X_ATTRIBUTE6 => p_attribute6,
290 X_ATTRIBUTE7 => p_attribute7,
291 X_ATTRIBUTE8 => p_attribute8,
292 X_ATTRIBUTE9 => p_attribute9,
293 X_ATTRIBUTE10 => p_attribute10,
294 X_ATTRIBUTE11 => p_attribute11,
295 X_ATTRIBUTE12 => p_attribute12,
296 X_ATTRIBUTE13 => p_attribute13,
297 X_ATTRIBUTE14 => p_attribute14,
298 X_ATTRIBUTE15 => p_attribute15 );
299
300 -- Query DB to check the Insert was Successful
301 OPEN Check_Insert (l_seq);
302 FETCH Check_Insert INTO l_check;
303 CLOSE Check_Insert;
304 --dbms_output.put_line('Insert Check-'||l_check);
305 IF l_check <> 1 THEN
306 RAISE INSERT_FAILED;
307 ELSE
308 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
309 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.insert',
310 'Visibility Insert Successfull='||l_seq);
311 END IF;
312 --X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
313
314 CS_KB_SYNC_INDEX_PKG.request_mark_idx_on_sec_change
315 ( 'ADD_VIS',
316 l_position,
317 null,
318 l_request_id,
319 l_return_status );
320
321 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
322 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
323 ELSE
324 RAISE INDEX_SYNC_FAILED;
325 END IF;
326
327
328 END IF; -- Insert Successful Check
329
330 END IF; --Validate duplicate Visibility Name
331
332 END IF; -- Validate Required Parameters Passed
333
334 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
335 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.end',
336 'Status='||X_RETURN_STATUS);
337 END IF;
338
339
340 EXCEPTION
341 WHEN INVALID_IN_PARAMETERS THEN
342 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
343 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.invparam',
344 'P_NAME='||P_NAME||
345 'P_ADD_BEFORE_POSITION='||P_ADD_BEFORE_VISIBILITY||
346 'P_ADD_AFTER_POSITION='||P_ADD_AFTER_VISIBILITY);
347 END IF;
348 FND_MSG_PUB.initialize;
349 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
350 FND_MSG_PUB.ADD;
351 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
352 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
353 p_count => X_MSG_COUNT,
354 p_data => X_MSG_DATA);
355
356 WHEN INSERT_FAILED THEN
357 ROLLBACK TO Create_Visibility_PVT;
358 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
359 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.insertcheck',
360 'Insert Row has failed='||l_check);
361 END IF;
362 FND_MSG_PUB.initialize;
363 FND_MESSAGE.set_name('CS', 'CS_KB_C_CREATE_ERR');
364 FND_MSG_PUB.ADD;
365 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
366 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
367 p_count => X_MSG_COUNT,
368 p_data => X_MSG_DATA);
369
370 WHEN DUPLICATE_VISIBILITY THEN
371 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
372 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.dupcheck',
373 'Visibility Name is a Duplicate='||P_NAME);
374 END IF;
375 FND_MSG_PUB.initialize;
376 FND_MESSAGE.set_name('CS', 'CS_KB_VIS_DUP_ERROR');
377 FND_MSG_PUB.ADD;
378 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
379 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
380 p_count => X_MSG_COUNT,
381 p_data => X_MSG_DATA);
382
383 WHEN INDEX_SYNC_FAILED THEN
384 ROLLBACK TO Create_Visibility_PVT;
385 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
386 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.indexsync',
387 'Index Sync failed='||l_request_id);
388 END IF;
389 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
390 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
391 p_count => X_MSG_COUNT,
392 p_data => X_MSG_DATA);
393
394 WHEN OTHERS THEN
395 ROLLBACK TO Create_Visibility_PVT;
396 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
397 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.UNEXPECTED',
398 ' Error= '||sqlerrm);
399 END IF;
400 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
403 p_data => x_msg_data);
404 END CREATE_VISIBILITY;
405
406
407 -- Start of comments
408 -- API name : UPDATE_VISIBILITY
409 -- Type : Private
410 -- Function : Update Existing Visibility Levels
411 -- Pre-reqs : None.
412 -- Parameters :
413 -- IN : P_VISIBILITY_ID NUMBER Required
414 -- P_POSITION NUMBER Required
415 -- P_START_DATE_ACTIVE DATE Optional
416 -- P_END_DATE_ACTIVE DATE Optional
417 -- P_NAME VARCHAR2 Required
418 -- P_DESCRIPTION VARCHAR2 Optional
419
420 --
421 -- OUT : x_return_status VARCHAR2(1)
422 -- x_msg_count NUMBER
423 -- x_msg_data VARCHAR2(2000)
424 --
425 -- History:
426 -- 07-Jul-03 Matt Kettle Created
427 --
428 --
429 --
430 -- Notes :
431 --
432 -- Validations :
433 -- 1) Required Parameters
434 -- 2) Check not updating Visibility Name to a Duplicate
435 -- End of comments
436
437 PROCEDURE UPDATE_VISIBILITY (
438 P_VISIBILITY_ID IN NUMBER,
439 P_POSITION IN NUMBER,
440 P_START_DATE_ACTIVE IN DATE,
441 P_END_DATE_ACTIVE IN DATE,
442 P_NAME IN VARCHAR2,
443 P_DESCRIPTION IN VARCHAR2,
444 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
445 P_ATTRIBUTE1 IN VARCHAR2,
446 P_ATTRIBUTE2 IN VARCHAR2,
447 P_ATTRIBUTE3 IN VARCHAR2,
448 P_ATTRIBUTE4 IN VARCHAR2,
449 P_ATTRIBUTE5 IN VARCHAR2,
450 P_ATTRIBUTE6 IN VARCHAR2,
451 P_ATTRIBUTE7 IN VARCHAR2,
452 P_ATTRIBUTE8 IN VARCHAR2,
453 P_ATTRIBUTE9 IN VARCHAR2,
454 P_ATTRIBUTE10 IN VARCHAR2,
455 P_ATTRIBUTE11 IN VARCHAR2,
456 P_ATTRIBUTE12 IN VARCHAR2,
457 P_ATTRIBUTE13 IN VARCHAR2,
458 P_ATTRIBUTE14 IN VARCHAR2,
459 P_ATTRIBUTE15 IN VARCHAR2,
460 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
461 X_MSG_DATA OUT NOCOPY VARCHAR2,
462 X_MSG_COUNT OUT NOCOPY NUMBER
463 ) IS
464
465 l_check NUMBER := 0;
466 l_current_user NUMBER := FND_GLOBAL.user_id;
467 l_date DATE := SYSDATE;
468 l_login NUMBER := FND_GLOBAL.login_id;
469
470 CURSOR Check_Update (v_vis_id NUMBER,
471 v_name VARCHAR2,
472 v_desc VARCHAR2 ) IS
473 SELECT COUNT(*)
474 FROM CS_KB_VISIBILITIES_VL
475 WHERE visibility_id = v_vis_id
476 AND name = v_name;
477
478 BEGIN
479
480 SAVEPOINT Update_Visibility_PVT;
481 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
482
483 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
484 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.begin',
485 'User='||l_current_user);
486 END IF;
487
488 -- Validate Required Parameters have been passed into api
489 IF P_NAME is null OR
490 P_VISIBILITY_ID is null OR
491 P_POSITION IS NULL THEN
492
493 RAISE INVALID_IN_PARAMETERS;
494
495 ELSE
496
497 -- Validate that the Visibility Name is not being updated to a duplicate
498 IF (DOES_VISIBILITY_NAME_EXIST(P_VISIBILITY_ID, P_NAME) = 'TRUE') THEN
499 -- Visibility Name is Duplicate
500 RAISE DUPLICATE_VISIBILITY;
501 ELSE
502 --dbms_output.put_line('New Sequence-'||l_seq);
503 CS_KB_VISIBILITIES_PKG.UPDATE_ROW(
504 X_VISIBILITY_ID => P_VISIBILITY_ID ,
505 X_POSITION => P_POSITION,
506 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
507 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
508 X_NAME => P_NAME,
509 X_DESCRIPTION => P_DESCRIPTION,
510 X_LAST_UPDATE_DATE => l_date,
511 X_LAST_UPDATED_BY => l_current_user,
512 X_LAST_UPDATE_LOGIN => l_login,
513 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
514 X_ATTRIBUTE1 => P_ATTRIBUTE1,
515 X_ATTRIBUTE2 => P_ATTRIBUTE2,
516 X_ATTRIBUTE3 => P_ATTRIBUTE3,
517 X_ATTRIBUTE4 => P_ATTRIBUTE4,
518 X_ATTRIBUTE5 => P_ATTRIBUTE5,
519 X_ATTRIBUTE6 => P_ATTRIBUTE6,
520 X_ATTRIBUTE7 => P_ATTRIBUTE7,
521 X_ATTRIBUTE8 => P_ATTRIBUTE8,
522 X_ATTRIBUTE9 => P_ATTRIBUTE9,
523 X_ATTRIBUTE10 => P_ATTRIBUTE10,
524 X_ATTRIBUTE11 => P_ATTRIBUTE11,
525 X_ATTRIBUTE12 => P_ATTRIBUTE12,
526 X_ATTRIBUTE13 => P_ATTRIBUTE13,
527 X_ATTRIBUTE14 => P_ATTRIBUTE14,
528 X_ATTRIBUTE15 => P_ATTRIBUTE15
529 );
530
531 -- Query DB to check the Insert was Successful
532 OPEN Check_Update (P_VISIBILITY_ID, P_NAME, P_DESCRIPTION);
533 FETCH Check_Update INTO l_check;
534 CLOSE Check_Update;
535 --dbms_output.put_line('Insert Check-'||l_check);
536
537 IF l_check <> 1 THEN
538 RAISE UPDATE_FAILED;
539 ELSE
540 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
541 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.update',
542 'Visibility Update Successfull='||P_VISIBILITY_ID);
543 END IF;
544
545 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
546 END IF; -- Update Successful Check
547
548 END IF; --Validate duplicate Visibility Name
549
550 END IF; -- Validate Required Parameters Passed
551
552 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
553 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.end',
554 'Status='||X_RETURN_STATUS);
555 END IF;
556
557
558 EXCEPTION
559 WHEN INVALID_IN_PARAMETERS THEN
560 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
561 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.invparam',
562 'P_NAME='||P_NAME||
563 'P_VISIBILITY='||P_VISIBILITY_ID);
564 END IF;
565 FND_MSG_PUB.initialize;
566 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
567 FND_MSG_PUB.ADD;
568 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
569 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
570 p_count => X_MSG_COUNT,
571 p_data => X_MSG_DATA);
572
573 WHEN UPDATE_FAILED THEN
574 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
575 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.updcheck',
576 'Update Row has failed='||l_check);
577 END IF;
578 FND_MSG_PUB.initialize;
579 FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
580 FND_MSG_PUB.ADD;
581 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
582 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
583 p_count => X_MSG_COUNT,
584 p_data => X_MSG_DATA);
585
586 WHEN DUPLICATE_VISIBILITY THEN
587 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
588 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.dupcheck',
589 'Visibility Name is a Duplicate='||P_NAME);
590 END IF;
591 FND_MSG_PUB.initialize;
592 FND_MESSAGE.set_name('CS', 'CS_KB_VIS_DUP_ERROR');
593 FND_MSG_PUB.ADD;
594 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
595 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
596 p_count => X_MSG_COUNT,
597 p_data => X_MSG_DATA);
598
599 WHEN OTHERS THEN
600 ROLLBACK TO Update_Visibility_PVT;
601 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
602 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.UNEXPECTED',
603 ' Error= '||sqlerrm);
604 END IF;
605 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
607 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
608 p_data => x_msg_data);
609 END UPDATE_VISIBILITY;
610
611 -- Start of comments
612 -- API name : DELETE_VISIBILITY
613 -- Type : Private
614 -- Function : Delete Existing Visibility Levels
615 -- Pre-reqs : None.
616 -- Parameters :
617 -- IN : P_VISIBILITY_ID NUMBER Required
618 --
619 --
620 -- OUT : x_return_status VARCHAR2(1)
621 -- x_msg_count NUMBER
622 -- x_msg_data VARCHAR2(2000)
623 --
624 -- History:
625 -- 07-Jul-03 Matt Kettle Created
626 -- 15-Aug-03 Matt Kettle added call to CS_KB_SYNC_INDEX_PKG
627 -- to request_mark_idx_on_sec_change
628 --
629 --
630 --
631 -- Notes :
632 -- 1) This api performs a 'Soft' Delete - i.e The Visibility is End-Dated
633 --
634 -- Validations:
635 -- 1) Required Parameters
636 -- 2) Check if the Visibility is used by a Category
637 -- 3) Check if the Visibility is used by a Solution
638 -- End of comments
639 PROCEDURE DELETE_VISIBILITY (
640 P_VISIBILITY_ID IN NUMBER,
641 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
642 X_MSG_DATA OUT NOCOPY VARCHAR2,
643 X_MSG_COUNT OUT NOCOPY NUMBER
644 ) IS
645
646 CURSOR USED_IN_SOLUTION IS
647 SELECT count(*)
648 FROM CS_KB_SETS_B
649 WHERE visibility_id = P_VISIBILITY_ID
650 AND STATUS <> 'OBS'
651 AND LATEST_VERSION_FLAG = 'Y';
652
653
654 CURSOR USED_IN_CATEGORY IS
655 SELECT count(*)
656 FROM CS_KB_SOLN_CATEGORIES_B
657 WHERE visibility_id = P_VISIBILITY_ID;
658
659 CURSOR CHECK_VISIBILITY IS
660 SELECT count(*)
661 FROM CS_KB_VISIBILITIES_B
662 WHERE visibility_id = P_VISIBILITY_ID
663 AND sysdate between nvl(Start_Date_Active, sysdate -1)
664 and nvl(End_Date_Active, sysdate +1);
665
666 CURSOR GET_POSITION IS
667 SELECT Position
668 FROM CS_KB_VISIBILITIES_B
669 WHERE visibility_id = P_VISIBILITY_ID;
670
671
672 l_cat_count NUMBER;
673 l_soln_count NUMBER;
674 l_vis_count NUMBER;
675 l_position NUMBER;
676 l_request_id NUMBER;
677 l_return_status VARCHAR2(1);
678 l_current_user NUMBER := FND_GLOBAL.user_id;
679
680 BEGIN
681 SAVEPOINT Delete_Visibility_PVT;
682 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
683
684 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
685 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.begin',
686 'User='||l_current_user);
687 END IF;
688
689 OPEN CHECK_VISIBILITY;
690 FETCH CHECK_VISIBILITY INTO l_vis_count;
691 CLOSE CHECK_VISIBILITY;
692
693 -- Validate Required Parameters have been passed into api
694 IF P_VISIBILITY_ID is null OR
695 l_vis_count = 0 THEN
696 RAISE INVALID_IN_PARAMETERS;
697 ELSE
698 -- Validate if Visibility is used in Categories
699 OPEN USED_IN_CATEGORY;
700 FETCH USED_IN_CATEGORY INTO l_cat_count;
701 CLOSE USED_IN_CATEGORY;
702 -- Validate if Visibility is used in Solutions
703 OPEN USED_IN_SOLUTION;
704 FETCH USED_IN_SOLUTION INTO l_soln_count;
705 CLOSE USED_IN_SOLUTION;
706
707 IF l_cat_count <> 0 OR l_soln_count <> 0 THEN
708 RAISE UNABLE_TO_DELETE_VIS;
709 ELSE
710
711 -- We will nolonger Delete the Visibility - We will End Date it instead
712 UPDATE CS_KB_VISIBILITIES_B
713 SET End_Date_Active = sysdate - 0.001,
714 Last_Update_date = sysdate,
715 Last_updated_By = FND_GLOBAL.User_id
716 WHERE Visibility_Id = P_VISIBILITY_ID;
717 --dbms_output.put_line('before vis count = '||l_vis_count);
718 OPEN CHECK_VISIBILITY;
719 FETCH CHECK_VISIBILITY INTO l_vis_count;
720 CLOSE CHECK_VISIBILITY;
721 --dbms_output.put_line('after vis count = '||l_vis_count);
722 IF l_vis_count = 0 THEN
723 OPEN GET_POSITION;
724 FETCH GET_POSITION INTO l_position;
725 CLOSE GET_POSITION;
726
727 CS_KB_SYNC_INDEX_PKG.request_mark_idx_on_sec_change
728 ( 'REM_VIS',
729 l_position,
730 null,
731 l_request_id,
732 l_return_status );
733
734 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
735 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
736 ELSE
737 RAISE INDEX_SYNC_FAILED;
738 END IF;
739
740
741 ELSE
742 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
743 RAISE DELETE_FAILED;
744 END IF;
745 END IF;
746
747 END IF; -- Valid Param Check
748
749 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
750 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.end',
751 'Status='||X_RETURN_STATUS);
752 END IF;
753
754 EXCEPTION
755 WHEN INVALID_IN_PARAMETERS THEN
756 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
757 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.invparam',
758 'P_VISIBILITY_ID='||P_VISIBILITY_ID);
759 END IF;
760 FND_MSG_PUB.initialize;
761 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
762 FND_MSG_PUB.ADD;
763 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
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 WHEN UNABLE_TO_DELETE_VIS THEN
768 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
769 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.invdel',
770 'P_VISIBILITY_ID='||P_VISIBILITY_ID);
771 END IF;
772 FND_MSG_PUB.initialize;
773 FND_MESSAGE.set_name('CS', 'CS_KB_VIS_INV_DEL');
774 FND_MSG_PUB.ADD;
775 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
776 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
777 p_count => X_MSG_COUNT,
778 p_data => X_MSG_DATA);
779 WHEN DELETE_FAILED THEN
780 ROLLBACK TO Delete_Visibility_PVT;
781 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
782 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.deletefail',
783 'Delete Row has failed='||l_vis_count);
784 END IF;
785 FND_MSG_PUB.initialize;
786 FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
787 FND_MSG_PUB.ADD;
788 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
789 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
790 p_count => X_MSG_COUNT,
791 p_data => X_MSG_DATA);
792 WHEN INDEX_SYNC_FAILED THEN
793 ROLLBACK TO Delete_Visibility_PVT;
794 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
795 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.indexsync',
796 'Index Sync failed='||l_request_id);
797 END IF;
798 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
799 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
800 p_count => X_MSG_COUNT,
801 p_data => X_MSG_DATA);
802
803 WHEN OTHERS THEN
804 ROLLBACK TO Delete_Visibility_PVT;
805 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
806 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.UNEXPECTED',
807 ' Error= '||sqlerrm);
808 END IF;
809 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
811 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
812 p_data => x_msg_data);
813 END DELETE_VISIBILITY;
814
815
816 /*=======================================================================+
817 | Private Security Apis |
818 | ***** Category Group ***** |
819 | - Does_Category_Group_Name_Exist |
820 | - Create_Category_Group |
821 | - Update_Category_Group |
822 | - Delete_Category_Group |
823 +=======================================================================*/
824
825 -- Start of comments
826 -- API name : DOES_CATEGORY_GROUP_NAME_EXIST
827 -- Type : Private Function
828 -- Function : Validates if the Category Group Name is duplicate
829 -- Pre-reqs : None.
830 -- Parameters :
831 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
832 -- P_NAME VARCHAR Required
833 -- OUT :
834 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
835 --
836 -- History:
837 -- 07-Jul-03 Matt Kettle Created
838 --
839 --
840 --
841 -- Notes :
842 -- 1) We only validate duplicate Name NOT description as well
843 --
844 -- End of comments
845 FUNCTION DOES_CATEGORY_GROUP_NAME_EXIST (
846 P_CATEGORY_GROUP_ID NUMBER,
847 P_NAME VARCHAR2
848 ) RETURN VARCHAR2 IS
849
850 CURSOR Check_Name_Exists IS
851 SELECT count(*)
852 FROM CS_KB_CATEGORY_GROUPS_VL
853 WHERE name = P_NAME
854 AND category_group_id <> P_CATEGORY_GROUP_ID;
855
856 l_count NUMBER :=0;
857 l_return VARCHAR2(10) := 'TRUE';
858
859 BEGIN
860
861 OPEN Check_Name_Exists;
862 FETCH Check_Name_Exists INTO l_count;
863 CLOSE Check_Name_Exists;
864 --dbms_output.put_line('Dup Count ='||l_count);
865 IF l_count <> 0 THEN
866 l_return := 'TRUE';
867 ELSE
868 l_return := 'FALSE';
869 END IF;
870 --dbms_output.put_line('Return ='||l_return);
871 RETURN l_return;
872
873 END DOES_CATEGORY_GROUP_NAME_EXIST;
874
875 -- Start of comments
876 -- API name : CREATE_CATEGORY_GROUP
877 -- Type : Private
878 -- Function : Create new Category Groups
879 -- Pre-reqs : None.
880 -- Parameters :
881 -- IN : P_CATEGORY_GROUP_ID NUMBER Optional
882 -- P_START_DATE_ACTIVE DATE Optional
883 -- P_END_DATE_ACTIVE DATE Optional
884 -- P_NAME VARCHAR Required
885 -- P_DESCRIPTION VARCHAR Optional
886 --
887 --
888 -- OUT : x_return_status VARCHAR2(1)
889 -- x_msg_count NUMBER
890 -- x_msg_data VARCHAR2(2000)
891 --
892 -- History:
893 -- 07-Jul-03 Matt Kettle Created
894 --
895 --
896 --
897 -- Notes :
898 -- 1) If P_CATEGORY_GROUP_ID is passed as null it will be
899 -- generated via the sequence
900 --
901 -- Validations:
902 -- 1) Required Parameters
903 -- 2) Checks that the Category Group Name is not duplicate
904 -- End of comments
905 PROCEDURE CREATE_CATEGORY_GROUP (
906 P_CATEGORY_GROUP_ID IN NUMBER,
907 P_START_DATE_ACTIVE IN DATE,
908 P_END_DATE_ACTIVE IN DATE,
909 P_NAME IN VARCHAR,
910 P_DESCRIPTION IN VARCHAR,
911 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
912 P_ATTRIBUTE1 IN VARCHAR2,
913 P_ATTRIBUTE2 IN VARCHAR2,
914 P_ATTRIBUTE3 IN VARCHAR2,
915 P_ATTRIBUTE4 IN VARCHAR2,
916 P_ATTRIBUTE5 IN VARCHAR2,
917 P_ATTRIBUTE6 IN VARCHAR2,
918 P_ATTRIBUTE7 IN VARCHAR2,
919 P_ATTRIBUTE8 IN VARCHAR2,
920 P_ATTRIBUTE9 IN VARCHAR2,
921 P_ATTRIBUTE10 IN VARCHAR2,
922 P_ATTRIBUTE11 IN VARCHAR2,
923 P_ATTRIBUTE12 IN VARCHAR2,
924 P_ATTRIBUTE13 IN VARCHAR2,
925 P_ATTRIBUTE14 IN VARCHAR2,
926 P_ATTRIBUTE15 IN VARCHAR2,
927 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
928 X_MSG_DATA OUT NOCOPY VARCHAR2,
929 X_MSG_COUNT OUT NOCOPY NUMBER
930 ) IS
931
932 l_check NUMBER;
933 l_current_user NUMBER := FND_GLOBAL.user_id;
934 l_date DATE := SYSDATE;
935 l_login NUMBER := FND_GLOBAL.login_id;
936 l_rowid VARCHAR2(30) := null;
937 l_seq NUMBER;
938
939 CURSOR Check_Insert (v_cg_id NUMBER) IS
940 SELECT COUNT(*)
941 FROM CS_KB_CATEGORY_GROUPS_B
942 WHERE CATEGORY_GROUP_ID = v_cg_id;
943
944 BEGIN
945
946 SAVEPOINT Create_Category_Group_PVT;
947 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
948
949 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
950 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.begin',
951 'User='||l_current_user);
952 END IF;
953
954 -- Validate Required Parameters have been passed into api
955 IF P_NAME IS NULL THEN
956 RAISE INVALID_IN_PARAMETERS;
957 ELSE
958
959 IF P_CATEGORY_GROUP_ID IS NULL THEN
960 SELECT CS_KB_CATEGORY_GROUPS_B_S.nextval INTO l_seq from dual;
961 ELSE
962 l_seq := P_CATEGORY_GROUP_ID;
963 END IF;
964
965 -- Validate that the Category Group Name is not duplicate
966 IF (DOES_CATEGORY_GROUP_NAME_EXIST(l_seq, P_NAME) = 'TRUE') THEN
967 -- Visibility Name is Duplicate
968 RAISE DUPLICATE_CATEGORY_GROUP;
969 ELSE
970
971 CS_KB_CATEGORY_GROUPS_PKG.INSERT_ROW (
972 X_ROWID => l_rowid,
973 X_CATEGORY_GROUP_ID => l_seq ,
974 X_START_DATE_ACTIVE => p_start_date_active,
975 X_END_DATE_ACTIVE => p_end_date_active,
976 X_NAME => p_name,
977 X_DESCRIPTION => p_description,
978 X_CREATION_DATE => l_date,
979 X_CREATED_BY => l_current_user,
980 X_LAST_UPDATE_DATE => l_date,
981 X_LAST_UPDATED_BY => l_current_user,
982 X_LAST_UPDATE_LOGIN => l_login,
983 X_ATTRIBUTE_CATEGORY => p_attribute_category,
984 X_ATTRIBUTE1 => p_attribute1,
985 X_ATTRIBUTE2 => p_attribute2,
986 X_ATTRIBUTE3 => p_attribute3,
987 X_ATTRIBUTE4 => p_attribute4,
988 X_ATTRIBUTE5 => p_attribute5,
989 X_ATTRIBUTE6 => p_attribute6,
990 X_ATTRIBUTE7 => p_attribute7,
991 X_ATTRIBUTE8 => p_attribute8,
992 X_ATTRIBUTE9 => p_attribute9,
993 X_ATTRIBUTE10 => p_attribute10,
994 X_ATTRIBUTE11 => p_attribute11,
995 X_ATTRIBUTE12 => p_attribute12,
996 X_ATTRIBUTE13 => p_attribute13,
997 X_ATTRIBUTE14 => p_attribute14,
998 X_ATTRIBUTE15 => p_attribute15);
999
1000 -- Query DB to check the Insert was Successful
1001 OPEN Check_Insert (l_seq);
1002 FETCH Check_Insert INTO l_check;
1003 CLOSE Check_Insert;
1004 --dbms_output.put_line('Insert Check-'||l_check);
1005 IF l_check <> 1 THEN
1006 RAISE INSERT_FAILED;
1007 ELSE
1008 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1009 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.insert',
1010 'Visibility Insert Successfull='||l_seq);
1011 END IF;
1012
1013 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1014 END IF; -- Insert Successful Check
1015 END IF; -- Check Name Dup
1016 END IF;
1017
1018 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1019 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.end',
1020 'Status='||X_RETURN_STATUS);
1021 END IF;
1022
1023 EXCEPTION
1024 WHEN INVALID_IN_PARAMETERS THEN
1025 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1026 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.invparam',
1027 'P_NAME='||P_NAME||
1028 'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
1029 END IF;
1030 FND_MSG_PUB.initialize;
1031 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
1032 FND_MSG_PUB.ADD;
1033 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1034 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1035 p_count => X_MSG_COUNT,
1036 p_data => X_MSG_DATA);
1037
1038 WHEN INSERT_FAILED THEN
1039 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1040 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.insertcheck',
1041 'Insert Row has failed='||l_check);
1042 END IF;
1043 FND_MSG_PUB.initialize;
1044 FND_MESSAGE.set_name('CS', 'CS_KB_C_CREATE_ERR');
1045 FND_MSG_PUB.ADD;
1046 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1047 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1048 p_count => X_MSG_COUNT,
1049 p_data => X_MSG_DATA);
1050
1051 WHEN DUPLICATE_CATEGORY_GROUP THEN
1052 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1053 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.dupcheck',
1054 'Category Group Name is a Duplicate='||P_NAME);
1055 END IF;
1056 FND_MSG_PUB.initialize;
1057 FND_MESSAGE.set_name('CS', 'CS_KB_CG_DUP_ERROR');
1058 FND_MSG_PUB.ADD;
1059 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1060 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1061 p_count => X_MSG_COUNT,
1062 p_data => X_MSG_DATA);
1063
1064 WHEN OTHERS THEN
1065 ROLLBACK TO Create_Category_Group_PVT;
1066 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1067 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.UNEXPECTED',
1068 ' Error= '||sqlerrm);
1069 END IF;
1070 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1072 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1073 p_data => x_msg_data);
1074
1075 END CREATE_CATEGORY_GROUP;
1076
1077 -- Start of comments
1078 -- API name : UPDATE_CATEGORY_GROUP
1079 -- Type : Private
1080 -- Function : Update Existing Category Groups
1081 -- Pre-reqs : None.
1082 -- Parameters :
1083 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
1084 -- P_START_DATE_ACTIVE DATE Optional
1085 -- P_END_DATE_ACTIVE DATE Optional
1086 -- P_NAME VARCHAR Required
1087 -- P_DESCRIPTION VARCHAR Optional
1088 --
1089 --
1090 -- OUT : x_return_status VARCHAR2(1)
1091 -- x_msg_count NUMBER
1092 -- x_msg_data VARCHAR2(2000)
1093 --
1094 -- History:
1095 -- 07-Jul-03 Matt Kettle Created
1096 --
1097 --
1098 --
1099 -- Notes :
1100 --
1101 -- Validations:
1102 -- 1) Required Parameters
1103 -- 2) Checks that the Category Group Name is not being updated duplicate
1104 -- End of comments
1105 PROCEDURE UPDATE_CATEGORY_GROUP (
1106 P_CATEGORY_GROUP_ID IN NUMBER,
1107 P_START_DATE_ACTIVE IN DATE,
1108 P_END_DATE_ACTIVE IN DATE,
1109 P_NAME IN VARCHAR,
1110 P_DESCRIPTION IN VARCHAR,
1111 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
1112 P_ATTRIBUTE1 IN VARCHAR2,
1113 P_ATTRIBUTE2 IN VARCHAR2,
1114 P_ATTRIBUTE3 IN VARCHAR2,
1115 P_ATTRIBUTE4 IN VARCHAR2,
1116 P_ATTRIBUTE5 IN VARCHAR2,
1117 P_ATTRIBUTE6 IN VARCHAR2,
1118 P_ATTRIBUTE7 IN VARCHAR2,
1119 P_ATTRIBUTE8 IN VARCHAR2,
1120 P_ATTRIBUTE9 IN VARCHAR2,
1121 P_ATTRIBUTE10 IN VARCHAR2,
1122 P_ATTRIBUTE11 IN VARCHAR2,
1123 P_ATTRIBUTE12 IN VARCHAR2,
1124 P_ATTRIBUTE13 IN VARCHAR2,
1125 P_ATTRIBUTE14 IN VARCHAR2,
1126 P_ATTRIBUTE15 IN VARCHAR2,
1127 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1128 X_MSG_DATA OUT NOCOPY VARCHAR2,
1129 X_MSG_COUNT OUT NOCOPY NUMBER
1130 ) IS
1131
1132 l_check NUMBER;
1133 l_current_user NUMBER := FND_GLOBAL.user_id;
1134 l_date DATE := SYSDATE;
1135 l_login NUMBER := FND_GLOBAL.login_id;
1136
1137 CURSOR Check_Update IS
1138 SELECT COUNT(*)
1139 FROM CS_KB_CATEGORY_GROUPS_VL
1140 WHERE CATEGORY_GROUP_ID = P_CATEGORY_GROUP_ID
1141 AND NAME = P_NAME;
1142
1143 BEGIN
1144 SAVEPOINT Update_Category_Group_PVT;
1145 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1146
1147 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1148 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.begin',
1149 'User='||l_current_user);
1150 END IF;
1151
1152 -- Validate Required Parameters have been passed into api
1153 IF P_NAME IS NULL OR P_CATEGORY_GROUP_ID IS NULL THEN
1154 RAISE INVALID_IN_PARAMETERS;
1155 ELSE
1156
1157 -- Validate that the Category Group Name is not duplicate
1158 IF (DOES_CATEGORY_GROUP_NAME_EXIST(P_CATEGORY_GROUP_ID, P_NAME) = 'TRUE') THEN
1159 -- Visibility Name is Duplicate
1160 RAISE DUPLICATE_CATEGORY_GROUP;
1161 ELSE
1162
1163 CS_KB_CATEGORY_GROUPS_PKG.UPDATE_ROW (
1164 X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID ,
1165 X_START_DATE_ACTIVE => p_start_date_active,
1166 X_END_DATE_ACTIVE => p_end_date_active,
1167 X_NAME => p_name,
1168 X_DESCRIPTION => p_description,
1169 X_LAST_UPDATE_DATE => l_date,
1170 X_LAST_UPDATED_BY => l_current_user,
1171 X_LAST_UPDATE_LOGIN => l_login,
1172 X_ATTRIBUTE_CATEGORY => p_attribute_category,
1173 X_ATTRIBUTE1 => p_attribute1,
1174 X_ATTRIBUTE2 => p_attribute2,
1175 X_ATTRIBUTE3 => p_attribute3,
1176 X_ATTRIBUTE4 => p_attribute4,
1177 X_ATTRIBUTE5 => p_attribute5,
1178 X_ATTRIBUTE6 => p_attribute6,
1179 X_ATTRIBUTE7 => p_attribute7,
1180 X_ATTRIBUTE8 => p_attribute8,
1181 X_ATTRIBUTE9 => p_attribute9,
1182 X_ATTRIBUTE10 => p_attribute10,
1183 X_ATTRIBUTE11 => p_attribute11,
1184 X_ATTRIBUTE12 => p_attribute12,
1185 X_ATTRIBUTE13 => p_attribute13,
1186 X_ATTRIBUTE14 => p_attribute14,
1187 X_ATTRIBUTE15 => p_attribute15);
1188
1189 -- Query DB to check the Update was Successful
1190 OPEN Check_Update;
1191 FETCH Check_Update INTO l_check;
1192 CLOSE Check_Update;
1193 --dbms_output.put_line('Update Check-'||l_check);
1194 IF l_check <> 1 THEN
1195 RAISE UPDATE_FAILED;
1196 ELSE
1197 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1198 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.update',
1199 'Cat Grp Update Successfull='||P_CATEGORY_GROUP_ID);
1200 END IF;
1201
1202 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1203 END IF; -- Update Successful Check
1204 END IF; -- Check Name Dup
1205
1206 END IF; -- Valid Params Passed
1207
1208 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1209 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.end',
1210 'Status='||X_RETURN_STATUS);
1211 END IF;
1212 EXCEPTION
1213 WHEN INVALID_IN_PARAMETERS THEN
1214 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1215 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.invparam',
1216 'P_NAME='||P_NAME||
1217 'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
1218 END IF;
1219 FND_MSG_PUB.initialize;
1220 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
1221 FND_MSG_PUB.ADD;
1222 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1223 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1224 p_count => X_MSG_COUNT,
1225 p_data => X_MSG_DATA);
1226
1227 WHEN DUPLICATE_CATEGORY_GROUP THEN
1228 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1229 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.dupcheck',
1230 'Update is Duplicate='||p_name);
1231 END IF;
1232 FND_MSG_PUB.initialize;
1233 FND_MESSAGE.set_name('CS', 'CS_KB_CG_DUP_ERROR');
1234 FND_MSG_PUB.ADD;
1235 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1236 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1237 p_count => X_MSG_COUNT,
1238 p_data => X_MSG_DATA);
1239
1240 WHEN UPDATE_FAILED THEN
1241 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1242 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.updatecheck',
1243 'Update Row has failed='||l_check);
1244 END IF;
1245 FND_MSG_PUB.initialize;
1246 FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
1247 FND_MSG_PUB.ADD;
1248 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1249 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1250 p_count => X_MSG_COUNT,
1251 p_data => X_MSG_DATA);
1252 WHEN OTHERS THEN
1253 ROLLBACK TO Update_Category_Group_PVT;
1254 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1255 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.UNEXPECTED',
1256 ' Error= '||sqlerrm);
1257 END IF;
1258 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1260 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1261 p_data => x_msg_data);
1262 END UPDATE_CATEGORY_GROUP;
1263
1264 -- Start of comments
1265 -- API name : DELETE_CATEGORY_GROUP
1266 -- Type : Private
1267 -- Function : Delete an Existing Category Group
1268 -- Pre-reqs : None.
1269 -- Parameters :
1270 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
1271 --
1272 --
1273 -- OUT : x_return_status VARCHAR2(1)
1274 -- x_msg_count NUMBER
1275 -- x_msg_data VARCHAR2(2000)
1276 --
1277 -- History:
1278 -- 07-Jul-03 Matt Kettle Created
1279 -- 07-Apr-04 Matt Kettle Added valdn to check flows (3559443)
1280 --
1281 --
1282 -- Notes :
1283 -- 1) This performs a 'Hard' Delete i.e. Row is removed
1284 --
1285 -- Validations:
1286 -- 1) Required Parameters
1287 -- 2) Checks if the Category Group has any Members
1288 -- 3) Checks if the Category Group has any Flows
1289 -- End of comments
1290 PROCEDURE DELETE_CATEGORY_GROUP (
1291 P_CATEGORY_GROUP_ID IN NUMBER,
1292 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1293 X_MSG_DATA OUT NOCOPY VARCHAR2,
1294 X_MSG_COUNT OUT NOCOPY NUMBER
1295 ) IS
1296
1297
1298 CURSOR CHECK_DELETE IS
1299 SELECT count(*)
1300 FROM CS_KB_CATEGORY_GROUPS_VL
1301 WHERE category_group_id = P_CATEGORY_GROUP_ID;
1302
1303 CURSOR CHECK_MEMBERS IS
1304 SELECT count(*)
1305 FROM CS_KB_CAT_GROUP_MEMBERS
1306 WHERE category_group_id = P_CATEGORY_GROUP_ID;
1307
1308 CURSOR CHECK_FLOWS IS
1309 SELECT count(*)
1310 FROM CS_KB_CAT_GROUP_FLOWS
1311 WHERE category_group_id = P_CATEGORY_GROUP_ID;
1312
1313 l_current_user NUMBER := FND_GLOBAL.user_id;
1314 l_member_count NUMBER;
1315 l_flow_count NUMBER;
1316 l_valid NUMBER;
1317
1318 BEGIN
1319 SAVEPOINT Delete_Category_Group_PVT;
1320 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1321
1322 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1323 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.begin',
1324 'User='||l_current_user);
1325 END IF;
1326
1327 OPEN CHECK_DELETE;
1328 FETCH CHECK_DELETE INTO l_valid;
1329 CLOSE CHECK_DELETE;
1330
1331 -- Validate Required Parameters have been passed into api
1332 IF P_CATEGORY_GROUP_ID is null OR
1333 l_valid = 0 THEN
1334 RAISE CG_NOT_FOUND;
1335 ELSE
1336 OPEN CHECK_MEMBERS;
1337 FETCH CHECK_MEMBERS INTO l_member_count;
1338 CLOSE CHECK_MEMBERS;
1339
1340 IF l_member_count <> 0 THEN
1341 RAISE CG_MEMBERS_EXIST;
1342 ELSE
1343
1344 OPEN CHECK_FLOWS;
1345 FETCH CHECK_FLOWS INTO l_flow_count;
1346 CLOSE CHECK_FLOWS;
1347
1348 IF l_flow_count <> 0 THEN
1349 RAISE CG_FLOWS_EXIST;
1350 ELSE
1351
1352 -- No Members or Flows exist so proceed with Delete
1353 CS_KB_CATEGORY_GROUPS_PKG.DELETE_ROW (X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID);
1354
1355 OPEN CHECK_DELETE;
1356 FETCH CHECK_DELETE INTO l_valid;
1357 CLOSE CHECK_DELETE;
1358 IF l_valid = 0 THEN
1359 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1360 ELSE
1361 RAISE DELETE_FAILED;
1362 END IF;
1363
1364 END IF;
1365
1366 END IF;
1367
1368 END IF; -- Valid Param Check
1369
1370 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1371 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.end',
1372 'Status='||X_RETURN_STATUS);
1373 END IF;
1374
1375 EXCEPTION
1376 WHEN INVALID_IN_PARAMETERS THEN
1377 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1378 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.invparam',
1379 'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
1380 END IF;
1381 FND_MSG_PUB.initialize;
1382 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
1383 FND_MSG_PUB.ADD;
1384 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1385 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1386 p_count => X_MSG_COUNT,
1387 p_data => X_MSG_DATA);
1388
1389 WHEN CG_NOT_FOUND THEN
1390 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1391 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.invdel',
1392 'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
1393 END IF;
1394 FND_MSG_PUB.initialize;
1395 FND_MESSAGE.set_name('CS', 'CS_KB_CG_NOT_FOUND');
1396 FND_MSG_PUB.ADD;
1397 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1398 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1399 p_count => X_MSG_COUNT,
1400 p_data => X_MSG_DATA);
1401
1402 WHEN DELETE_FAILED THEN
1403 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1404 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.deletefail',
1405 'Delete Row has failed='||l_valid);
1406 END IF;
1407 FND_MSG_PUB.initialize;
1408 FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
1409 FND_MSG_PUB.ADD;
1410 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1411 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1412 p_count => X_MSG_COUNT,
1413 p_data => X_MSG_DATA);
1414
1415 WHEN CG_MEMBERS_EXIST THEN
1416 ROLLBACK TO Delete_Category_Group_PVT;
1417 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1418 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.memexist',
1419 'Members Exist for Cat Grp='||l_member_count);
1420 END IF;
1421 FND_MSG_PUB.initialize;
1422 FND_MESSAGE.set_name('CS', 'CS_KB_INV_DEL_CG');
1423 FND_MSG_PUB.ADD;
1424 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1425 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1426 p_count => X_MSG_COUNT,
1427 p_data => X_MSG_DATA);
1428
1429 WHEN CG_FLOWS_EXIST THEN
1430 ROLLBACK TO Delete_Category_Group_PVT;
1431 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1432 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.flowexist',
1433 'Flows Exist for Cat Grp='||l_flow_count);
1434 END IF;
1435 FND_MSG_PUB.initialize;
1436 FND_MESSAGE.set_name('CS', 'CS_KB_INV_DEL_CG');
1437 FND_MSG_PUB.ADD;
1438 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1439 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1440 p_count => X_MSG_COUNT,
1441 p_data => X_MSG_DATA);
1442
1443 WHEN OTHERS THEN
1444 ROLLBACK TO Delete_Category_Group_PVT;
1445 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1446 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.UNEXPECTED',
1447 ' Error= '||sqlerrm);
1448 END IF;
1449 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1451 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1452 p_data => x_msg_data);
1453
1454 END DELETE_CATEGORY_GROUP;
1455
1456
1457 /*=======================================================================+
1458 | Private Security Apis |
1459 | ***** Category Group Member ***** |
1460 | - Does_Cat_Group_Member_Exist |
1461 | - Create_Category_Group_Member |
1462 | - Delete_Category_Group_Member |
1463 +=======================================================================*/
1464
1465
1466
1467 -- Start of comments
1468 -- API name : DOES_CAT_GROUP_MEMBER_EXIST
1469 -- Type : Private Function
1470 -- Function : Validates if a CG Member exists already
1471 -- Pre-reqs : None.
1472 -- Parameters :
1473 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
1474 -- P_CATEGORY_ID NUMBER Required
1475 --
1476 -- History:
1477 -- 07-Jul-03 Matt Kettle Created
1478 --
1479 --
1480 --
1481 -- Notes :
1482 -- 1) Validates if a CG Member exists already - ie if the
1483 -- Category Exists or if the Category is included already
1484 -- by a Parent
1485 -- Validations:
1486 --
1487 -- End of comments
1488 FUNCTION DOES_CAT_GROUP_MEMBER_EXIST (
1489 P_CATEGORY_GROUP_ID NUMBER,
1490 P_CATEGORY_ID NUMBER
1491 ) RETURN VARCHAR2 IS
1492
1493 CURSOR CHECK_PARENT IS
1494 SELECT count(m.category_id)
1495 FROM CS_KB_CAT_GROUP_MEMBERS m
1496 WHERE m.category_group_id = P_CATEGORY_GROUP_ID
1497 AND category_id in (
1498 SELECT b.category_id
1499 FROM cs_kb_soln_categories_b b
1500 START WITH b.category_id = P_CATEGORY_ID
1501 CONNECT BY PRIOR b.parent_category_id = b.category_id);
1502
1503 l_count NUMBER;
1504 l_return VARCHAR2(10) := 'FALSE';
1505
1506 BEGIN
1507 OPEN CHECK_PARENT;
1508 FETCH CHECK_PARENT INTO l_count;
1509 CLOSE CHECK_PARENT;
1510 --dbms_output.put_line('count-'||l_count);
1511 IF l_count = 0 THEN
1512 l_return := 'FALSE';
1513 ELSE
1514 l_return := 'TRUE';
1515 END IF;
1516 --dbms_output.put_line('return-'||l_return);
1517 RETURN l_return;
1518 END DOES_CAT_GROUP_MEMBER_EXIST;
1519
1520
1521
1522 -- Start of comments
1523 -- API name : CREATE_CATEGORY_GROUP_MEMBER
1524 -- Type : Private
1525 -- Function : Creaye a New Category Group Member
1526 -- Pre-reqs : None.
1527 -- Parameters :
1528 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
1529 -- P_CATEGORY_ID NUMBER Required
1530 --
1531 -- OUT : x_return_status VARCHAR2(1)
1532 -- x_msg_count NUMBER
1533 -- x_msg_data VARCHAR2(2000)
1534 --
1535 -- History:
1536 -- 07-Jul-03 Matt Kettle Created
1537 -- 15-Aug-03 Matt Kettle added call to CS_KB_SYNC_INDEX_PKG
1538 -- to request_mark_idx_on_sec_change
1539 --
1540 --
1541 --
1542 -- Notes :
1543 -- 1)
1544 --
1545 -- Validations:
1546 -- 1) Required Parameters
1547 -- 2) Checks if the Category Group is a Valid Identifier
1548 -- 3) Checks if the Category is a Valid Identifier
1549 -- 4) Validate that the Record we are trying to insert is not a duplicate
1550 -- End of comments
1551 PROCEDURE CREATE_CATEGORY_GROUP_MEMBER (
1552 P_CATEGORY_GROUP_ID IN NUMBER,
1553 P_CATEGORY_ID IN NUMBER,
1554 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
1555 P_ATTRIBUTE1 IN VARCHAR2,
1556 P_ATTRIBUTE2 IN VARCHAR2,
1557 P_ATTRIBUTE3 IN VARCHAR2,
1558 P_ATTRIBUTE4 IN VARCHAR2,
1559 P_ATTRIBUTE5 IN VARCHAR2,
1560 P_ATTRIBUTE6 IN VARCHAR2,
1561 P_ATTRIBUTE7 IN VARCHAR2,
1562 P_ATTRIBUTE8 IN VARCHAR2,
1563 P_ATTRIBUTE9 IN VARCHAR2,
1564 P_ATTRIBUTE10 IN VARCHAR2,
1565 P_ATTRIBUTE11 IN VARCHAR2,
1566 P_ATTRIBUTE12 IN VARCHAR2,
1567 P_ATTRIBUTE13 IN VARCHAR2,
1568 P_ATTRIBUTE14 IN VARCHAR2,
1569 P_ATTRIBUTE15 IN VARCHAR2,
1570 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1571 X_MSG_DATA OUT NOCOPY VARCHAR2,
1572 X_MSG_COUNT OUT NOCOPY NUMBER
1573 ) IS
1574
1575 l_check NUMBER;
1576 l_current_user NUMBER := FND_GLOBAL.user_id;
1577 l_date DATE := SYSDATE;
1578 l_login NUMBER := FND_GLOBAL.login_id;
1579 l_rowid VARCHAR2(30) := null;
1580 l_valid_cg NUMBER;
1581 l_valid_cat NUMBER;
1582 l_request_id NUMBER;
1583 l_return_status VARCHAR2(1);
1584
1585 CURSOR Check_Insert (v_cg_id NUMBER, v_cat_id NUMBER) IS
1586 SELECT COUNT(*)
1587 FROM CS_KB_CAT_GROUP_MEMBERS
1588 WHERE CATEGORY_GROUP_ID = v_cg_id
1589 AND CATEGORY_ID = v_cat_id;
1590
1591 CURSOR Validate_Category_Group IS
1592 SELECT count(*)
1593 FROM CS_KB_CATEGORY_GROUPS_B
1594 WHERE category_group_id = P_CATEGORY_GROUP_ID;
1595
1596 CURSOR Validate_Category IS
1597 SELECT count(*)
1598 FROM CS_KB_SOLN_CATEGORIES_B
1599 WHERE category_id = P_CATEGORY_ID;
1600
1601 BEGIN
1602 --dbms_output.put_line('In api-'||P_CATEGORY_GROUP_ID||' - '||P_CATEGORY_ID);
1603 SAVEPOINT Create_Cat_Group_Member_PVT;
1604 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1605
1606 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1607 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP_MEMBER.begin',
1608 'User='||l_current_user);
1609 END IF;
1610
1611 -- Validate Required Parameters have been passed into api
1612 IF P_CATEGORY_GROUP_ID IS NULL OR
1613 P_CATEGORY_ID IS NULL THEN
1614 RAISE INVALID_IN_PARAMETERS;
1615 ELSE
1616 --dbms_output.put_line('params passed-');
1617 -- check valid params
1618 OPEN Validate_Category_Group;
1619 FETCH Validate_Category_Group INTO l_valid_cg;
1620 CLOSE Validate_Category_Group;
1621
1622 OPEN Validate_Category;
1623 FETCH Validate_Category INTO l_valid_cat;
1624 CLOSE Validate_Category;
1625
1626 IF l_valid_cg = 0 OR
1627 l_valid_cat = 0 THEN
1628 RAISE INVALID_IN_PARAMETERS;
1629 ELSE
1630
1631 -- Validate that the Record we are trying to insert is not a duplicate
1632 IF (DOES_CAT_GROUP_MEMBER_EXIST(P_CATEGORY_GROUP_ID, P_CATEGORY_ID) = 'TRUE') THEN
1633 -- Category Group Member already exists
1634 RAISE DUP_CATEGORY_GROUP_MEMBER;
1635 ELSE
1636 --dbms_output.put_line('before insert-');
1637 CS_KB_CAT_GROUP_MEMBERS_PKG.INSERT_ROW (
1638 X_ROWID => l_rowid,
1639 X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID ,
1640 X_CATEGORY_ID => P_CATEGORY_ID ,
1641 X_CREATION_DATE => l_date,
1642 X_CREATED_BY => l_current_user,
1643 X_LAST_UPDATE_DATE => l_date,
1644 X_LAST_UPDATED_BY => l_current_user,
1645 X_LAST_UPDATE_LOGIN => l_login,
1646 X_ATTRIBUTE_CATEGORY => p_attribute_category,
1647 X_ATTRIBUTE1 => p_attribute1,
1648 X_ATTRIBUTE2 => p_attribute2,
1649 X_ATTRIBUTE3 => p_attribute3,
1650 X_ATTRIBUTE4 => p_attribute4,
1651 X_ATTRIBUTE5 => p_attribute5,
1652 X_ATTRIBUTE6 => p_attribute6,
1653 X_ATTRIBUTE7 => p_attribute7,
1654 X_ATTRIBUTE8 => p_attribute8,
1655 X_ATTRIBUTE9 => p_attribute9,
1656 X_ATTRIBUTE10 => p_attribute10,
1657 X_ATTRIBUTE11 => p_attribute11,
1658 X_ATTRIBUTE12 => p_attribute12,
1659 X_ATTRIBUTE13 => p_attribute13,
1660 X_ATTRIBUTE14 => p_attribute14,
1661 X_ATTRIBUTE15 => p_attribute15);
1662
1663 -- Query DB to check the Insert was Successful
1664 OPEN Check_Insert (P_CATEGORY_GROUP_ID, P_CATEGORY_ID);
1665 FETCH Check_Insert INTO l_check;
1666 CLOSE Check_Insert;
1667 --dbms_output.put_line('Insert Check-'||l_check);
1668 IF l_check <> 1 THEN
1669 RAISE INSERT_FAILED;
1670 ELSE
1671
1672 --> Validate if any children already exist - if yes then remove !!!!
1673 DELETE FROM CS_KB_CAT_GROUP_MEMBERS
1674 WHERE CATEGORY_GROUP_ID = P_CATEGORY_GROUP_ID
1675 AND CATEGORY_ID IN (SELECT b.category_id
1676 FROM cs_kb_soln_categories_b b
1677 START WITH b.parent_category_id = P_CATEGORY_ID
1678 CONNECT BY PRIOR b.category_id = b.parent_category_id);
1679
1680
1681 --dbms_output.put_line('insert success-');
1682
1683 -- Populate the New Category Member and its children to the Denorm table
1684 ADD_CAT_GRP_MEMBER_TO_DENORM(P_CATEGORY_GROUP_ID, P_CATEGORY_ID);
1685
1686 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1687 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP_MEMBER.insert',
1688 'CG Member Insert Successfull='||P_CATEGORY_GROUP_ID||'-'||P_CATEGORY_ID);
1689 END IF;
1690
1691 CS_KB_SYNC_INDEX_PKG.request_mark_idx_on_sec_change
1692 ( 'ADD_CAT_TO_CAT_GRP',
1693 P_CATEGORY_GROUP_ID,
1694 P_CATEGORY_ID,
1695 l_request_id,
1696 l_return_status );
1697 --dbms_output.put_line('sync success-'||l_return_status||l_request_id);
1698
1699
1700 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1701 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1702 ELSE
1703 RAISE INDEX_SYNC_FAILED;
1704 END IF;
1705
1706 END IF; -- Insert Successful Check
1707 END IF; -- Check Name Dup
1708
1709 END IF;-- valid params check
1710
1711 END IF; --Params passed in
1712
1713 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1714 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP_MEMBER.end',
1715 'Status='||X_RETURN_STATUS);
1716 END IF;
1717
1718 EXCEPTION
1719 WHEN INVALID_IN_PARAMETERS THEN
1720
1721 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1722 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP_MEMBER.invparam',
1723 'P_CATEGORY_ID='||P_CATEGORY_ID||
1724 '+P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
1725 END IF;
1726 FND_MSG_PUB.initialize;
1727 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
1728 FND_MSG_PUB.ADD;
1729 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1730 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1731 p_count => X_MSG_COUNT,
1732 p_data => X_MSG_DATA);
1733
1734 WHEN INSERT_FAILED THEN
1735 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1736 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.insertcheck',
1737 'Insert Row has failed='||l_check);
1738 END IF;
1739 ROLLBACK TO Create_Cat_Group_Member_PVT;
1740 FND_MSG_PUB.initialize;
1741 FND_MESSAGE.set_name('CS', 'CS_KB_C_CREATE_ERR');
1742 FND_MSG_PUB.ADD;
1743 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1744 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1745 p_count => X_MSG_COUNT,
1746 p_data => X_MSG_DATA);
1747
1748 WHEN DUP_CATEGORY_GROUP_MEMBER THEN
1749
1750 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1751 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.dupcheck',
1752 'Member already included: P_CATEGORY_ID='||P_CATEGORY_ID||
1753 '+P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
1754 END IF;
1755 FND_MSG_PUB.initialize;
1756 FND_MESSAGE.set_name('CS', 'CS_KB_CGM_DUP_ERROR');
1757 FND_MSG_PUB.ADD;
1758 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1759 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1760 p_count => X_MSG_COUNT,
1761 p_data => X_MSG_DATA);
1762
1763 WHEN INDEX_SYNC_FAILED THEN
1764 ROLLBACK TO Create_Cat_Group_Member_PVT;
1765 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1766 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.indexsync',
1767 'Index Sync failed='||l_request_id);
1768 END IF;
1769 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1770 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1771 p_count => X_MSG_COUNT,
1772 p_data => X_MSG_DATA);
1773
1774 WHEN OTHERS THEN
1775 ROLLBACK TO Create_Cat_Group_Member_PVT;
1776 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1777 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP_MEMBER.UNEXPECTED',
1778 ' Error= '||sqlerrm);
1779 END IF;
1780 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1782 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1783 p_data => x_msg_data);
1784
1785 END CREATE_CATEGORY_GROUP_MEMBER;
1786
1787 -- Start of comments
1788 -- API name : DELETE_CATEGORY_GROUP_MEMBER
1789 -- Type : Private
1790 -- Function : Delete an Existing Category Group Member
1791 -- Pre-reqs : None.
1792 -- Parameters :
1793 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
1794 -- P_CATEGORY_ID NUMBER Required
1795 --
1796 -- OUT : x_return_status VARCHAR2(1)
1797 -- x_msg_count NUMBER
1798 -- x_msg_data VARCHAR2(2000)
1799 --
1800 -- History:
1801 -- 07-Jul-03 Matt Kettle Created
1802 -- 15-Aug-03 Matt Kettle added call to CS_KB_SYNC_INDEX_PKG
1803 -- to request_mark_idx_on_sec_change
1804 --
1805 --
1806 --
1807 -- Notes :
1808 -- 1)
1809 --
1810 -- Validations:
1811 -- End of comments
1812 PROCEDURE DELETE_CATEGORY_GROUP_MEMBER (
1813 P_CATEGORY_GROUP_ID IN NUMBER,
1814 P_CATEGORY_ID IN NUMBER,
1815 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1816 X_MSG_DATA OUT NOCOPY VARCHAR2,
1817 X_MSG_COUNT OUT NOCOPY NUMBER
1818 ) IS
1819
1820
1821 CURSOR CHECK_DELETE IS
1822 SELECT count(*)
1823 FROM CS_KB_CAT_GROUP_MEMBERS
1824 WHERE category_group_id = P_CATEGORY_GROUP_ID
1825 AND category_id = P_CATEGORY_ID;
1826
1827
1828 l_current_user NUMBER := FND_GLOBAL.user_id;
1829 l_valid NUMBER;
1830 l_request_id NUMBER;
1831 l_return_status VARCHAR2(1);
1832
1833 BEGIN
1834 SAVEPOINT Delete_Cat_Group_Member_PVT;
1835 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1836
1837 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1838 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.begin',
1839 'User='||l_current_user);
1840 END IF;
1841
1842 -- Validate Required Parameters have been passed into api
1843 IF P_CATEGORY_GROUP_ID is null OR P_CATEGORY_ID is null THEN
1844 RAISE INVALID_IN_PARAMETERS;
1845 ELSE
1846
1847 OPEN CHECK_DELETE;
1848 FETCH CHECK_DELETE INTO l_valid;
1849 CLOSE CHECK_DELETE;
1850
1851 IF l_valid <> 0 THEN
1852 CS_KB_CAT_GROUP_MEMBERS_PKG.DELETE_ROW (X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID,
1853 X_CATEGORY_ID => P_CATEGORY_ID);
1854
1855 OPEN CHECK_DELETE;
1856 FETCH CHECK_DELETE INTO l_valid;
1857 CLOSE CHECK_DELETE;
1858 IF l_valid = 0 THEN
1859 --Remove FK records for Member + Children from CS_KB_CAT_GROUP_DENORM
1860 REMOVE_CG_MEMBER_FROM_DENORM ( P_CATEGORY_GROUP_ID, P_CATEGORY_ID);
1861
1862 --X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1863 CS_KB_SYNC_INDEX_PKG.request_mark_idx_on_sec_change
1864 ( 'REM_CAT_FROM_CAT_GRP',
1865 P_CATEGORY_GROUP_ID,
1866 P_CATEGORY_ID,
1867 l_request_id,
1868 l_return_status );
1869
1870 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1871 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1872 ELSE
1873 RAISE INDEX_SYNC_FAILED;
1874 END IF;
1875
1876
1877
1878 ELSE
1879 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1880 RAISE DELETE_FAILED;
1881 END IF;
1882
1883 ELSE
1884 -- Else Row already Deleted - ignore and return Successful
1885 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1886 END IF;
1887
1888 END IF; -- Valid Param Check
1889
1890 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1891 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.end',
1892 'Status='||X_RETURN_STATUS);
1893 END IF;
1894
1895 EXCEPTION
1896 WHEN INVALID_IN_PARAMETERS THEN
1897 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1898 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.invparam',
1899 'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID||
1900 ' P_CATEGORY_ID='||P_CATEGORY_ID);
1901 END IF;
1902 FND_MSG_PUB.initialize;
1903 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
1904 FND_MSG_PUB.ADD;
1905 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1906 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1907 p_count => X_MSG_COUNT,
1908 p_data => X_MSG_DATA);
1909 WHEN DELETE_FAILED THEN
1910 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1911 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.deletefail',
1912 'Delete Row has failed='||l_valid);
1913 END IF;
1914 FND_MSG_PUB.initialize;
1915 FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
1916 FND_MSG_PUB.ADD;
1917 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1918 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1919 p_count => X_MSG_COUNT,
1920 p_data => X_MSG_DATA);
1921
1922 WHEN INDEX_SYNC_FAILED THEN
1923 ROLLBACK TO Delete_Cat_Group_Member_PVT;
1924 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1925 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.indexsync',
1926 'Index Sync failed='||l_request_id);
1927 END IF;
1928 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1929 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
1930 p_count => X_MSG_COUNT,
1931 p_data => X_MSG_DATA);
1932
1933 WHEN OTHERS THEN
1934 ROLLBACK TO Delete_Cat_Group_Member_PVT;
1935 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1936 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP_MEMBER.UNEXPECTED',
1937 ' Error= '||sqlerrm);
1938 END IF;
1939 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
1940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1941 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1942 p_data => x_msg_data);
1943
1944 END DELETE_CATEGORY_GROUP_MEMBER;
1945
1946 /*=======================================================================+
1947 | Private Security Apis |
1948 | ***** Maintain Denorm Table ***** |
1949 | - Add_Cat_Grp_Member_To_Denorm |
1950 | - Remove_CG_Member_From_Denorm |
1951 | - Update_Denorm_Vis_Position |
1952 | - Add_Category_To_Denorm |
1953 | - Update_Category_To_Denorm |
1954 | - Remove_Category_From_Cat_Group |
1955 | - Populate_Cat_Grp_Denorm |
1956 +=======================================================================*/
1957
1958 -- Start of comments
1959 -- API name : ADD_CAT_GRP_MEMBER_TO_DENORM
1960 -- Type : Private
1961 -- Function : Populates Denorm Table with new Member + Children
1962 -- Pre-reqs : None.
1963 -- Parameters :
1964 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
1965 -- P_CATEGORY_ID NUMBER Required
1966 -- OUT :
1967 --
1968 -- History:
1969 -- 08-Jul-03 Matt Kettle Created
1970 --
1971 --
1972 --
1973 -- Notes :
1974 -- 1) For every Category_Id added to the CS_KB_CAT_GROUP_MEMBERS table
1975 -- this api populates the Denorm table with the Member Category_id
1976 -- and all of its Children
1977 --
1978 -- Used By:
1979 -- 1) CREATE_CATEGORY_GROUP_MEMBER
1980 -- End of comments
1981 PROCEDURE ADD_CAT_GRP_MEMBER_TO_DENORM ( P_CATEGORY_GROUP_ID NUMBER,
1982 P_CATEGORY_ID NUMBER
1983 ) IS
1984
1985 CURSOR GET_CHILD_CATEGORIES IS
1986 SELECT New_Members.category_id, New_Members.visibility_id, v.position
1987 FROM (
1988 SELECT b.category_id, b.visibility_id
1989 FROM cs_kb_soln_categories_b b
1990 START WITH b.parent_category_id = P_CATEGORY_ID
1991 CONNECT BY PRIOR b.category_id = b.parent_category_id
1992 UNION
1993 SELECT b.category_id, b.visibility_id
1994 FROM cs_kb_soln_categories_b b
1995 WHERE b.category_id = P_CATEGORY_ID
1996 ) New_Members ,
1997 CS_KB_VISIBILITIES_b v
1998 WHERE New_Members.visibility_id = v.visibility_id
1999 AND NOT EXISTS (SELECT 'x'
2000 FROM CS_KB_CAT_GROUP_DENORM Denorm
2001 WHERE Denorm.Category_Group_id = P_CATEGORY_GROUP_ID
2002 AND Denorm.Child_Category_Id = New_Members.Category_Id);
2003
2004 --Type NumTabType is VARRAY(10000) of NUMBER;
2005 Type NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2006 child_cat_id_list NumTabType;
2007 vis_id_list NumTabType;
2008 position_list NumTabType;
2009
2010 l_current_user NUMBER := FND_GLOBAL.user_id;
2011 l_date DATE := SYSDATE;
2012 l_login NUMBER := FND_GLOBAL.login_id;
2013
2014 BEGIN
2015 OPEN GET_CHILD_CATEGORIES;
2016 FETCH GET_CHILD_CATEGORIES BULK COLLECT INTO child_cat_id_list, vis_id_list, position_list;
2017 CLOSE GET_CHILD_CATEGORIES;
2018 --dbms_output.put_line('Before Insert dn-'||P_CATEGORY_GROUP_ID);
2019 --dbms_output.put_line ('Cat count:'||child_cat_id_list.count );
2020 --dbms_output.put_line ('Cat count:'||vis_id_list.count );
2021 --dbms_output.put_line ('Cat count:'||position_list.count );
2022
2023 FORALL i in 1..child_cat_id_list.count
2024
2025 INSERT INTO CS_KB_CAT_GROUP_DENORM (
2026 CATEGORY_GROUP_ID,
2027 CHILD_CATEGORY_ID,
2028 VISIBILITY_ID,
2029 VISIBILITY_POSITION,
2030 CREATION_DATE,
2031 CREATED_BY,
2032 LAST_UPDATE_DATE,
2033 LAST_UPDATED_BY,
2034 LAST_UPDATE_LOGIN
2035 )
2036 Values (
2037 P_CATEGORY_GROUP_ID,
2038 child_cat_id_list(i),
2039 vis_id_list(i),
2040 position_list(i),
2041 l_date,
2042 l_current_user,
2043 l_date,
2044 l_current_user,
2045 l_login
2046 );
2047
2048 END ADD_CAT_GRP_MEMBER_TO_DENORM;
2049
2050 -- Start of comments
2051 -- API name : REMOVE_CG_MEMBER_FROM_DENORM
2052 -- Type : Private
2053 -- Function : Deletes Categories (+Children) from Denorm Table
2054 -- Pre-reqs : None.
2055 -- Parameters :
2056 -- IN : P_CATEGORY_GROUP_ID NUMBER Required
2057 -- P_CATEGORY_ID NUMBER Required
2058 -- OUT :
2059 --
2060 -- History:
2061 -- 08-Jul-03 Matt Kettle Created
2062 --
2063 --
2064 --
2065 -- Notes :
2066 -- 1) For every Category_Id deleted from the CS_KB_CAT_GROUP_MEMBERS table
2067 -- this api deleted the corresponding rows in the Denorm table (Member + Children)
2068 --
2069 -- Used By:
2070 -- 1) DELETE_CATEGORY_GROUP_MEMBER
2071 -- End of comments
2072 PROCEDURE REMOVE_CG_MEMBER_FROM_DENORM ( P_CATEGORY_GROUP_ID NUMBER,
2073 P_CATEGORY_ID NUMBER
2074 ) IS
2075
2076 CURSOR GET_CHILD_CATEGORIES IS
2077 SELECT b.category_id
2078 FROM cs_kb_soln_categories_b b
2079 START WITH b.parent_category_id = P_CATEGORY_ID
2080 CONNECT BY PRIOR b.category_id = b.parent_category_id
2081 UNION
2082 SELECT b.category_id
2083 FROM cs_kb_soln_categories_b b
2084 WHERE b.category_id = P_CATEGORY_ID;
2085
2086
2087 --Type NumTabType is VARRAY(10000) of NUMBER;
2088 Type NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2089 child_cat_id_list NumTabType;
2090
2091 BEGIN
2092
2093
2094 OPEN GET_CHILD_CATEGORIES;
2095 FETCH GET_CHILD_CATEGORIES BULK COLLECT INTO child_cat_id_list;
2096 CLOSE GET_CHILD_CATEGORIES;
2097 --dbms_output.put_line('Before delete dn-'||P_CATEGORY_GROUP_ID);
2098 --dbms_output.put_line ('Cat count:'||child_cat_id_list.count );
2099
2100 FORALL i in 1..child_cat_id_list.count
2101
2102 DELETE FROM CS_KB_CAT_GROUP_DENORM
2103 WHERE Category_Group_id = P_CATEGORY_GROUP_ID
2104 AND Child_Category_Id = child_cat_id_list(i);
2105
2106 END REMOVE_CG_MEMBER_FROM_DENORM;
2107
2108 -- Start of comments
2109 -- API name : ADD_CATEGORY_TO_DENORM
2110 -- Type : Private
2111 -- Function : Adds new Categories to the Denorm Table
2112 -- Pre-reqs : None.
2113 -- Parameters :
2114 -- IN : P_CATEGORY_ID NUMBER Required
2115 -- P_PARENT_CATEGORY_ID NUMBER Required
2116 -- P_VISIBILITY_ID NUMBER Required
2117 --
2118 -- OUT : X_RETURN_STATUS VARCHAR2
2119 -- X_MSG_DATA VARCHAR2
2120 -- X_MSG_COUNT NUMBER
2121 --
2122 -- History:
2123 -- 08-Jul-03 Matt Kettle Created
2124 --
2125 --
2126 --
2127 -- Notes :
2128 -- 1) If a New Category is added to CS_KB_SOLN_CATEGORIES_B then this api
2129 -- is used to update the Denorm table with this new Category_Id if
2130 -- any Category Groups already include its parent
2131 --
2132 -- Used By:
2133 -- 1) CS_KB_SOLN_CATEGORIES_PVT.createCategory
2134 -- End of comments
2135 PROCEDURE ADD_CATEGORY_TO_DENORM (
2136 P_CATEGORY_ID IN NUMBER,
2137 P_PARENT_CATEGORY_ID IN NUMBER,
2138 P_VISIBILITY_ID IN NUMBER,
2139 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2140 X_MSG_DATA OUT NOCOPY VARCHAR2,
2141 X_MSG_COUNT OUT NOCOPY NUMBER
2142 ) IS
2143
2144 CURSOR Validate_Category IS
2145 SELECT Category_id
2146 FROM CS_KB_SOLN_CATEGORIES_B
2147 WHERE Category_id = P_CATEGORY_ID
2148 AND Parent_Category_Id = P_PARENT_CATEGORY_ID;
2149
2150 CURSOR Get_Visibility_Position IS
2151 SELECT Position
2152 FROM CS_KB_VISIBILITIES_B
2153 WHERE Visibility_Id = P_VISIBILITY_ID
2154 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
2155 AND nvl(End_Date_Active, sysdate+1);
2156
2157 CURSOR GET_GROUPS IS
2158 SELECT Distinct Category_Group_Id
2159 FROM CS_KB_CAT_GROUP_DENORM
2160 WHERE CHILD_CATEGORY_ID = P_PARENT_CATEGORY_ID;
2161
2162 --Type NumTabType is VARRAY(10000) of NUMBER;
2163 Type NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2164 cat_grp_id_list NumTabType;
2165
2166 l_cat_valid NUMBER;
2167 l_position NUMBER;
2168 l_current_user NUMBER := FND_GLOBAL.user_id;
2169 l_date DATE := SYSDATE;
2170 l_login NUMBER := FND_GLOBAL.login_id;
2171
2172 BEGIN
2173 SAVEPOINT Add_Cat_To_Denorm_PVT;
2174 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2175
2176 -- Validate Category
2177 OPEN Validate_Category;
2178 FETCH Validate_Category INTO l_cat_valid;
2179 CLOSE Validate_Category;
2180
2181 IF l_cat_valid IS NOT NULL THEN
2182
2183 -- Retrieve Position
2184 OPEN Get_Visibility_Position;
2185 FETCH Get_Visibility_Position INTO l_position;
2186 CLOSE Get_Visibility_Position;
2187
2188 IF l_position IS NOT NULL THEN
2189 -- Add Category to Denorm Table where appropriate
2190
2191
2192 OPEN Get_Groups;
2193 FETCH Get_Groups BULK COLLECT INTO cat_grp_id_list;
2194 CLOSE Get_Groups;
2195
2196 --FOR x IN Get_Groups LOOP
2197 FORALL i in 1..cat_grp_id_list.count
2198
2199 INSERT INTO CS_KB_CAT_GROUP_DENORM
2200 ( CATEGORY_GROUP_ID,
2201 CHILD_CATEGORY_ID,
2202 VISIBILITY_ID,
2203 VISIBILITY_POSITION,
2204 CREATION_DATE,
2205 CREATED_BY,
2206 LAST_UPDATE_DATE,
2207 LAST_UPDATED_BY,
2208 LAST_UPDATE_LOGIN
2209 )
2210 Values ( cat_grp_id_list(i), --x.Category_Group_id,
2211 P_CATEGORY_ID,
2212 P_VISIBILITY_ID,
2213 l_position,
2214 l_date,
2215 l_current_user,
2216 l_date,
2217 l_current_user,
2218 l_login
2219 );
2220 --END LOOP;
2221
2222 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2223
2224 ELSE
2225 RAISE INVALID_IN_PARAMETERS;
2226 END IF;
2227
2228 ELSE
2229 RAISE INVALID_IN_PARAMETERS;
2230 END IF;
2231
2232 EXCEPTION
2233 WHEN INVALID_IN_PARAMETERS THEN
2234 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2235 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.ADD_CATEGORY_TO_DENORM.invparam',
2236 'P_CATEGORY_ID='||P_CATEGORY_ID||
2237 'P_PARENT_CATEGORY_ID='||P_PARENT_CATEGORY_ID||
2238 'P_VISIBILITY_ID='||P_VISIBILITY_ID);
2239 END IF;
2240 FND_MSG_PUB.initialize;
2241 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
2242 FND_MSG_PUB.ADD;
2243 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2244 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2245 p_count => X_MSG_COUNT,
2246 p_data => X_MSG_DATA);
2247
2248 WHEN OTHERS THEN
2249 ROLLBACK TO Add_Cat_To_Denorm_PVT;
2250 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2251 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.ADD_CATEGORY_TO_DENORM.UNEXPECTED',
2252 ' Error= '||sqlerrm);
2253 END IF;
2254 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
2255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2256 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2257 p_data => x_msg_data);
2258 END ADD_CATEGORY_TO_DENORM;
2259
2260 -- Start of comments
2261 -- API name : UPDATE_CATEGORY_TO_DENORM
2262 -- Type : Private
2263 -- Function : Updates Categories in the Denorm Table
2264 -- Pre-reqs : None.
2265 -- Parameters :
2266 -- IN : P_CATEGORY_ID NUMBER Required
2267 -- P_VISIBILITY_ID NUMBER Required
2268 --
2269 -- OUT : X_RETURN_STATUS VARCHAR2
2270 -- X_MSG_DATA VARCHAR2
2271 -- X_MSG_COUNT NUMBER
2272 --
2273 -- History:
2274 -- 08-Jul-03 Matt Kettle Created
2275 --
2276 --
2277 --
2278 -- Notes :
2279 -- 1) This api is required to update the denormalized Visibility
2280 -- information in CS_KB_CAT_GROUP_DENORM if a Categories associated
2281 -- Visibility changes
2282 --
2283 -- Used By:
2284 -- 1) CS_KB_SOLN_CATEGORIES_PVT.updateCategory
2285 -- End of comments
2286 PROCEDURE UPDATE_CATEGORY_TO_DENORM (
2287 P_CATEGORY_ID IN NUMBER,
2288 P_VISIBILITY_ID IN NUMBER,
2289 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2290 X_MSG_DATA OUT NOCOPY VARCHAR2,
2291 X_MSG_COUNT OUT NOCOPY NUMBER
2292 ) IS
2293
2294 CURSOR Validate_Category IS
2295 SELECT Category_id, Visibility_id
2296 FROM CS_KB_SOLN_CATEGORIES_B
2297 WHERE Category_id = P_CATEGORY_ID;
2298
2299 CURSOR Get_Position IS
2300 SELECT Position
2301 FROM CS_KB_VISIBILITIES_B
2302 WHERE Visibility_id = P_VISIBILITY_ID;
2303
2304 l_cat_id NUMBER;
2305 l_vis_id NUMBER;
2306 l_position NUMBER;
2307
2308 l_current_user NUMBER := FND_GLOBAL.user_id;
2309 l_date DATE := SYSDATE;
2310 l_login NUMBER := FND_GLOBAL.login_id;
2311
2312 BEGIN
2313 SAVEPOINT Upd_Cat_To_Denorm_PVT;
2314 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2315
2316 OPEN Validate_Category;
2317 FETCH Validate_Category INTO l_cat_id, l_vis_id;
2318 CLOSE Validate_Category;
2319
2320 IF l_cat_id IS NOT NULL THEN
2321
2322 OPEN Get_Position;
2323 FETCH Get_Position INTO l_position;
2324 CLOSE Get_Position;
2325
2326 IF l_position IS NOT NULL THEN
2327
2328 UPDATE CS_KB_CAT_GROUP_DENORM
2329 SET Visibility_id = P_VISIBILITY_ID,
2330 Visibility_Position = l_position,
2331 Last_Update_Date = l_date,
2332 Last_Updated_By = l_current_user,
2333 Last_Update_Login = l_login
2334 WHERE Child_Category_Id = P_CATEGORY_ID
2335 AND Visibility_id <> P_VISIBILITY_ID;
2336
2337 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2338
2339 ELSE
2340 RAISE INVALID_IN_PARAMETERS;
2341 END IF;
2342
2343 ELSE
2344 RAISE INVALID_IN_PARAMETERS;
2345 END IF;
2346
2347 EXCEPTION
2348 WHEN INVALID_IN_PARAMETERS THEN
2349 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2350 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_TO_DENORM.invparam',
2351 'P_CATEGORY_ID='||P_CATEGORY_ID||
2352 'P_VISIBILITY_ID='||P_VISIBILITY_ID);
2353 END IF;
2354 FND_MSG_PUB.initialize;
2355 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
2356 FND_MSG_PUB.ADD;
2357 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2358 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2359 p_count => X_MSG_COUNT,
2360 p_data => X_MSG_DATA);
2361
2362 WHEN OTHERS THEN
2363 ROLLBACK TO Upd_Cat_To_Denorm_PVT;
2364 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2365 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_TO_DENORM.UNEXPECTED',
2366 ' Error= '||sqlerrm);
2367 END IF;
2368 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
2369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2370 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2371 p_data => x_msg_data);
2372 END UPDATE_CATEGORY_TO_DENORM;
2373
2374 -- Start of comments
2375 -- API name : REMOVE_CATEGORY_FROM_CAT_GROUP
2376 -- Type : Private
2377 -- Function : This removes FK links to a Deleted Category
2378 -- Pre-reqs : None.
2379 -- Parameters :
2380 -- IN : P_CATEGORY_ID NUMBER Required
2381 --
2382 -- OUT : X_RETURN_STATUS VARCHAR2
2383 -- X_MSG_DATA VARCHAR2
2384 -- X_MSG_COUNT NUMBER
2385 --
2386 -- History:
2387 -- 08-Jul-03 Matt Kettle Created
2388 --
2389 --
2390 --
2391 -- Notes :
2392 -- 1) If a Category is deleted this api will remove the FK links in
2393 -- CS_KB_CAT_GROUP_MEMBERS and CS_KB_CAT_GROUP_DENORM
2394 --
2395 --
2396 -- Used By:
2397 -- 1) CS_KB_SOLN_CATEGORIES_PVT.removeCategory
2398 -- End of comments
2399 PROCEDURE REMOVE_CATEGORY_FROM_CAT_GROUP (
2400 P_CATEGORY_ID IN NUMBER,
2401 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2402 X_MSG_DATA OUT NOCOPY VARCHAR2,
2403 X_MSG_COUNT OUT NOCOPY NUMBER
2404 ) IS
2405
2406 CURSOR Check_Delete_Member IS
2407 SELECT count(*)
2408 FROM CS_KB_CAT_GROUP_MEMBERS
2409 WHERE Category_id = P_CATEGORY_ID;
2410
2411 CURSOR Check_Delete_Denorm IS
2412 SELECT count(*)
2413 FROM CS_KB_CAT_GROUP_DENORM
2414 WHERE Child_Category_id = P_CATEGORY_ID;
2415
2416 l_denorm_count NUMBER;
2417 l_member_count NUMBER;
2418
2419 BEGIN
2420 SAVEPOINT Remove_Cat_From_Cat_Grp_PVT;
2421 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2422
2423 IF P_CATEGORY_ID IS NOT NULL THEN
2424 -- 1. Removes Category from Members table if the Category Exists
2425 DELETE FROM CS_KB_CAT_GROUP_MEMBERS
2426 WHERE Category_Id = P_CATEGORY_ID;
2427 -- 2. Removes Category from Denorm table if the Category Exists
2428 DELETE FROM CS_KB_CAT_GROUP_DENORM
2429 WHERE Child_Category_Id = P_CATEGORY_ID;
2430
2431 OPEN Check_Delete_Member;
2432 FETCH Check_Delete_Member INTO l_member_count;
2433 CLOSE Check_Delete_Member;
2434
2435 OPEN Check_Delete_Denorm;
2436 FETCH Check_Delete_Denorm INTO l_denorm_count;
2437 CLOSE Check_Delete_Denorm;
2438
2439 IF l_member_count = 0 AND l_denorm_count = 0 THEN
2440 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2441 ELSE
2442 RAISE DELETE_FAILED;
2443 END IF;
2444
2445 ELSE
2446 RAISE INVALID_IN_PARAMETERS;
2447 END IF;
2448
2449 EXCEPTION
2450 WHEN INVALID_IN_PARAMETERS THEN
2451 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2452 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.REMOVE_CATEGORY_FROM_CAT_GROUP.invparam',
2453 'P_CATEGORY_ID='||P_CATEGORY_ID);
2454 END IF;
2455 FND_MSG_PUB.initialize;
2456 FND_MESSAGE.set_name('CS', 'CS_KB_INV_API_PARAM');
2457 FND_MSG_PUB.ADD;
2458 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2459 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2460 p_count => X_MSG_COUNT,
2461 p_data => X_MSG_DATA);
2462
2463 WHEN DELETE_FAILED THEN
2464 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2465 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.REMOVE_CATEGORY_FROM_CAT_GROUP.delfail',
2466 'Member Count='||l_member_count||
2467 'Denorm Count='||l_denorm_count);
2468 END IF;
2469 FND_MSG_PUB.initialize;
2470 FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
2471 FND_MSG_PUB.ADD;
2472 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2473 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2474 p_count => X_MSG_COUNT,
2475 p_data => X_MSG_DATA);
2476
2477 WHEN OTHERS THEN
2478 ROLLBACK TO Remove_Cat_From_Cat_Grp_PVT;
2479 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2480 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.REMOVE_CATEGORY_FROM_CAT_GROUP.UNEXPECTED',
2481 ' Error= '||sqlerrm);
2482 END IF;
2483 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2485 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2486 p_data => x_msg_data);
2487 END REMOVE_CATEGORY_FROM_CAT_GROUP;
2488
2489 -- Not Used
2490 FUNCTION POPULATE_CAT_GRP_DENORM (
2491 P_CATEGORY_GROUP_ID NUMBER
2492 ) RETURN VARCHAR2 IS
2493
2494 CURSOR GET_CHILD_CATEGORIES ( v_cg NUMBER) IS
2495 SELECT b.category_id, b.visibility_id , v.position
2496 FROM cs_kb_soln_categories_b b , CS_KB_VISIBILITIES_b v
2497 WHERE b.visibility_id = v.visibility_id
2498 START WITH b.parent_category_id in (select m.category_id
2499 FROM CS_KB_CAT_GROUP_MEMBERS m
2500 where m.category_group_id = v_cg)
2501 CONNECT BY PRIOR b.category_id = b.parent_category_id
2502 UNION
2503 SELECT b.category_id, b.visibility_id, v.position
2504 FROM cs_kb_soln_categories_b b, CS_KB_VISIBILITIES_b v
2505 WHERE b.category_id in (select m.category_id
2506 FROM CS_KB_CAT_GROUP_MEMBERS m
2507 where m.category_group_id = v_cg)
2508 AND b.visibility_id = v.visibility_id;
2509
2510 --Type NumTabType is VARRAY(10000) of NUMBER;
2511 Type NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2512 child_cat_id_list NumTabType;
2513 vis_id_list NumTabType;
2514 position_list NumTabType;
2515
2516
2517 l_current_user NUMBER := FND_GLOBAL.user_id;
2518 l_date DATE := SYSDATE;
2519 l_login NUMBER := FND_GLOBAL.login_id;
2520
2521 -- l_count NUMBER :=0;
2522 l_return VARCHAR2(10) := 'TRUE';
2523
2524 BEGIN
2525 --dbms_output.put_line('In pop denorm');
2526 DELETE FROM CS_KB_CAT_GROUP_DENORM
2527 WHERE category_group_id = P_CATEGORY_GROUP_ID;
2528 --dbms_output.put_line('After Delete');
2529
2530
2531
2532 OPEN GET_CHILD_CATEGORIES (P_CATEGORY_GROUP_ID);
2533 FETCH GET_CHILD_CATEGORIES BULK COLLECT INTO child_cat_id_list, vis_id_list, position_list;
2534 CLOSE GET_CHILD_CATEGORIES;
2535 --dbms_output.put_line('Before Insert dn-'||P_CATEGORY_GROUP_ID);
2536 --dbms_output.put_line ('Cat count:'||child_cat_id_list.count );
2537 --dbms_output.put_line ('Cat count:'||vis_id_list.count );
2538 --dbms_output.put_line ('Cat count:'||position_list.count );
2539
2540 FORALL i in 1..child_cat_id_list.count --LOOP
2541
2542 INSERT INTO CS_KB_CAT_GROUP_DENORM (
2543 CATEGORY_GROUP_ID,
2544 CHILD_CATEGORY_ID,
2545 VISIBILITY_ID,
2546 VISIBILITY_POSITION,
2547 CREATION_DATE,
2548 CREATED_BY,
2549 LAST_UPDATE_DATE,
2550 LAST_UPDATED_BY,
2551 LAST_UPDATE_LOGIN
2552 )
2553 Values (
2554 P_CATEGORY_GROUP_ID, --x.category_group_id,
2555 child_cat_id_list(i),
2556 vis_id_list(i),
2557 position_list(i), --x.position --position_list(i) --,
2558 l_date,
2559 l_current_user,
2560 l_date,
2561 l_current_user,
2562 l_login
2563 );
2564
2565 l_return := 'TRUE';
2566 --dbms_output.put_line('Return ='||l_return);
2567
2568 RETURN l_return;
2569
2570 END POPULATE_CAT_GRP_DENORM;
2571
2572 FUNCTION REPOPULATE_CAT_GRP_DENORM
2573 RETURN VARCHAR2 IS
2574
2575 CURSOR GET_CHILD_CATEGORIES ( v_cg NUMBER) IS
2576 SELECT b.category_id, b.visibility_id , v.position
2577 FROM cs_kb_soln_categories_b b , CS_KB_VISIBILITIES_b v
2578 WHERE b.visibility_id = v.visibility_id
2579 START WITH b.parent_category_id in (select m.category_id
2580 FROM CS_KB_CAT_GROUP_MEMBERS m
2581 where m.category_group_id = v_cg)
2582 CONNECT BY PRIOR b.category_id = b.parent_category_id
2583 UNION
2584 SELECT b.category_id, b.visibility_id, v.position
2585 FROM cs_kb_soln_categories_b b, CS_KB_VISIBILITIES_b v
2586 WHERE b.category_id in (select m.category_id
2587 FROM CS_KB_CAT_GROUP_MEMBERS m
2588 where m.category_group_id = v_cg)
2589 AND b.visibility_id = v.visibility_id;
2590
2591 CURSOR GET_CATEGORY_GROUPS IS
2592 SELECT Category_Group_Id
2593 FROM CS_KB_CATEGORY_GROUPS_B
2594 WHERE sysdate BETWEEN nvl(start_date_active, sysdate-1)
2595 AND nvl(end_date_active, sysdate+1);
2596
2597 --Type NumTabType is VARRAY(10000) of NUMBER;
2598 Type NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2599 child_cat_id_list NumTabType;
2600 vis_id_list NumTabType;
2601 position_list NumTabType;
2602 child_grp_id_list NumTabType;
2603
2604 l_current_user NUMBER := FND_GLOBAL.user_id;
2605 l_date DATE := SYSDATE;
2606 l_login NUMBER := FND_GLOBAL.login_id;
2607
2608 -- l_count NUMBER :=0;
2609 l_return VARCHAR2(10) := 'TRUE';
2610
2611 BEGIN
2612 --dbms_output.put_line('In pop denorm');
2613 DELETE FROM CS_KB_CAT_GROUP_DENORM;
2614 --dbms_output.put_line('After Delete');
2615
2616 FOR x IN GET_CATEGORY_GROUPS LOOP
2617
2618
2619 OPEN GET_CHILD_CATEGORIES (x.Category_Group_id);
2620 FETCH GET_CHILD_CATEGORIES BULK COLLECT INTO child_cat_id_list, vis_id_list, position_list;
2621 CLOSE GET_CHILD_CATEGORIES;
2622 --dbms_output.put_line('Before Insert dn-'||P_CATEGORY_GROUP_ID);
2623 --dbms_output.put_line ('Cat count:'||child_cat_id_list.count );
2624 --dbms_output.put_line ('Cat count:'||vis_id_list.count );
2625 --dbms_output.put_line ('Cat count:'||position_list.count );
2626
2627 FORALL i in 1..child_cat_id_list.count --LOOP
2628
2629 INSERT INTO CS_KB_CAT_GROUP_DENORM (
2630 CATEGORY_GROUP_ID,
2631 CHILD_CATEGORY_ID,
2632 VISIBILITY_ID,
2633 VISIBILITY_POSITION,
2634 CREATION_DATE,
2635 CREATED_BY,
2636 LAST_UPDATE_DATE,
2637 LAST_UPDATED_BY,
2638 LAST_UPDATE_LOGIN
2639 )
2640 Values (
2641 x.category_group_id,
2642 child_cat_id_list(i),
2643 vis_id_list(i),
2644 position_list(i), --x.position --position_list(i) --,
2645 l_date,
2646 l_current_user,
2647 l_date,
2648 l_current_user,
2649 l_login
2650 );
2651
2652 END LOOP;
2653
2654 l_return := 'TRUE';
2655 --dbms_output.put_line('Return ='||l_return);
2656
2657 RETURN l_return;
2658
2659 END REPOPULATE_CAT_GRP_DENORM;
2660
2661
2662
2663 /*=======================================================================+
2664 | Private Security Apis |
2665 | ***** Utility Functions ***** |
2666 | - Get_Category_Group_id |
2667 | - Get_Soln_Visibility_Position |
2668 | - Get_Stmt_Visibility_Position |
2669 | - Get_Security_Profiles |
2670 +=======================================================================*/
2671
2672 -- Start of comments
2673 -- API name : Get_Category_Group_Id
2674 -- Type : Private Function
2675 -- Function : Function to return the Current Users Category Group
2676 -- Pre-reqs : None.
2677 -- Parameters :
2678 -- IN : P_VISIBILITY_ID NUMBER Required
2679 -- P_NAME VARCHAR Required
2680 -- OUT :
2681 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
2682 --
2683 -- History:
2684 -- 10-Jul-03 Matt Kettle Created
2685 --
2686 --
2687 --
2688 -- Notes :
2689 -- 1) The Category Group Id will only be returned if the User has an
2690 -- active (not end-dated) Category Group set in his Profile
2691 --
2692 -- End of comments
2693 FUNCTION Get_Category_Group_Id
2694 RETURN NUMBER IS
2695
2696 CURSOR GET_CATEGORY_GROUP IS
2697 SELECT Category_Group_Id
2698 FROM CS_KB_CATEGORY_GROUPS_B
2699 WHERE Category_Group_id = fnd_profile.value('CS_KB_ASSIGNED_CATEGORY_GROUP')
2700 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
2701 AND nvl(End_Date_Active, sysdate+1);
2702
2703 id NUMBER ;
2704 BEGIN
2705
2706 OPEN GET_CATEGORY_GROUP;
2707 FETCH GET_CATEGORY_GROUP INTO id;
2708 CLOSE GET_CATEGORY_GROUP;
2709
2710 RETURN id;
2711 END Get_Category_Group_Id;
2712
2713 -- Start of comments
2714 -- API name : Get_Soln_Visibility_Position
2715 -- Type : Private Function
2716 -- Function : Function to return the Current Users Solution Visibility Position
2717 -- Pre-reqs : None.
2718 -- Parameters :
2719 -- IN : P_VISIBILITY_ID NUMBER Required
2720 -- P_NAME VARCHAR Required
2721 -- OUT :
2722 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
2723 --
2724 -- History:
2725 -- 10-Jul-03 Matt Kettle Created
2726 --
2727 --
2728 --
2729 -- Notes :
2730 -- 1) The Solution Visibility Position will only be returned if the User has an
2731 -- active (not end-dated) Visibility set in his Profile
2732 --
2733 -- End of comments
2734 FUNCTION Get_Soln_Visibility_Position
2735 RETURN NUMBER IS
2736
2737 CURSOR GET_POSITION IS
2738 SELECT Position
2739 FROM CS_KB_VISIBILITIES_B
2740 WHERE VISIBILITY_ID = fnd_profile.value('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL')
2741 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
2742 AND nvl(End_Date_Active, sysdate+1);
2743
2744 id number;
2745 BEGIN
2746 OPEN GET_POSITION;
2747 FETCH GET_POSITION INTO id;
2748 CLOSE GET_POSITION;
2749
2750 RETURN id;
2751
2752 END Get_Soln_Visibility_Position;
2753
2754 -- Start of comments
2755 -- API name : Get_Stmt_Visibility_Position
2756 -- Type : Private Function
2757 -- Function : Function to return the Current Users Statement Visibility Position
2758 -- Pre-reqs : None.
2759 -- Parameters :
2760 -- IN : P_VISIBILITY_ID NUMBER Required
2761 -- P_NAME VARCHAR Required
2762 -- OUT :
2763 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
2764 --
2765 -- History:
2766 -- 10-Jul-03 Matt Kettle Created
2767 --
2768 --
2769 --
2770 -- Notes :
2771 -- 1) The Statement Visibility Position will only be returned if the User has an
2772 -- active (not end-dated) Visibility set in his Profile
2773 --
2774 -- End of comments
2775 FUNCTION Get_Stmt_Visibility_Position
2776 RETURN NUMBER IS
2777
2778 CURSOR GET_STMT_VISIBILITY IS
2779 SELECT lookup_code
2780 FROM cs_lookups
2781 WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
2782 AND lookup_code = fnd_profile.value('CS_KB_ASSIGNED_STATEMENT_VISIBILITY_LEVEL')
2783 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
2784 AND nvl(End_Date_Active, sysdate+1);
2785
2786 id number;
2787 BEGIN
2788
2789 OPEN GET_STMT_VISIBILITY;
2790 FETCH GET_STMT_VISIBILITY INTO id;
2791 CLOSE GET_STMT_VISIBILITY;
2792
2793 RETURN id;
2794
2795 END Get_Stmt_Visibility_Position;
2796
2797 -- Start of comments
2798 -- API name : GET_SECURITY_PROFILES
2799 -- Type : Private Function
2800 -- Function : Api to return all the Security Profiles
2801 -- Pre-reqs : None.
2802 -- Parameters :
2803 -- IN : P_VISIBILITY_ID NUMBER Required
2804 -- P_NAME VARCHAR Required
2805 -- OUT :
2806 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
2807 --
2808 -- History:
2809 -- 10-Jul-03 Matt Kettle Created
2810 --
2811 --
2812 --
2813 -- Notes :
2814 -- 1) This api returns all the Valid Security Profiles for a User.
2815 -- If any are set incorrectly an Exception will be thrown.
2816 --
2817 -- End of comments
2818 PROCEDURE GET_SECURITY_PROFILES (
2819 X_CATEGORY_GROUP_ID OUT NOCOPY NUMBER,
2820 X_SOLN_VISIBILITY_POSITION OUT NOCOPY NUMBER,
2821 X_STMT_VISIBILITY_POSITION OUT NOCOPY NUMBER,
2822 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2823 X_MSG_DATA OUT NOCOPY VARCHAR2,
2824 X_MSG_COUNT OUT NOCOPY NUMBER
2825 ) IS
2826
2827 l_cat_group_id NUMBER;
2828 l_Soln_Vis_Position NUMBER;
2829 l_Stmt_Vis_Position NUMBER;
2830
2831 BEGIN
2832 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2833
2834 l_cat_group_id := Get_Category_Group_id;
2835
2836 IF l_cat_group_id is null THEN
2837 RAISE INV_SEC_CAT_GRP;
2838 ELSE
2839
2840 l_Soln_Vis_Position := Get_Soln_Visibility_Position;
2841
2842 IF l_Soln_Vis_Position is null THEN
2843 RAISE INV_SEC_SOLN_VIS;
2844 ELSE
2845
2846 l_Stmt_Vis_Position := Get_Stmt_Visibility_Position;
2847
2848 IF l_Stmt_Vis_Position is null THEN
2849 RAISE INV_SEC_STMT_VIS;
2850 ELSE
2851 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2852 X_CATEGORY_GROUP_ID := l_cat_group_id;
2853 X_SOLN_VISIBILITY_POSITION := l_Soln_Vis_Position;
2854 X_STMT_VISIBILITY_POSITION := l_Stmt_Vis_Position;
2855 END IF;
2856
2857 END IF;
2858
2859 END IF;
2860
2861 EXCEPTION
2862 WHEN INV_SEC_CAT_GRP THEN
2863 FND_MSG_PUB.initialize;
2864 FND_MESSAGE.set_name('CS', 'CS_KB_INV_SEC_CG');
2865 FND_MSG_PUB.ADD;
2866 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2867 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2868 p_count => X_MSG_COUNT,
2869 p_data => X_MSG_DATA);
2870 WHEN INV_SEC_SOLN_VIS THEN
2871 FND_MSG_PUB.initialize;
2872 FND_MESSAGE.set_name('CS', 'CS_KB_INV_SEC_SOL_VIS');
2873 FND_MSG_PUB.ADD;
2874 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2875 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2876 p_count => X_MSG_COUNT,
2877 p_data => X_MSG_DATA);
2878 WHEN INV_SEC_STMT_VIS THEN
2879 FND_MSG_PUB.initialize;
2880 FND_MESSAGE.set_name('CS', 'CS_KB_INV_SEC_STM_VIS');
2881 FND_MSG_PUB.ADD;
2882 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2883 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,
2884 p_count => X_MSG_COUNT,
2885 p_data => X_MSG_DATA);
2886
2887 WHEN OTHERS THEN
2888 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
2889 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2890 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.GET_SECURITY_PROFILES.UNEXPECTED',
2891 ' Error= '||sqlerrm);
2892 END IF;
2893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2894 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2895 p_data => x_msg_data);
2896
2897 END GET_SECURITY_PROFILES;
2898
2899 -- Start of comments
2900 -- API name : IS_COMPLETE_SOLUTION_VISIBLE
2901 -- Type : Private Function
2902 -- Function : Function to determine whether the user can access
2903 -- the complete Solution
2904 -- Pre-reqs : None.
2905 -- Parameters :
2906 -- IN : P_USER_ID NUMBER Required
2907 -- P_SET_ID NUMBER Required
2908 -- OUT :
2909 -- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
2910 --
2911 -- History:
2912 -- 16-Jul-03 Matt Kettle Created
2913 -- 15-Sep-03 Matt Kettle Changed cursor IS_COMPLETE_SOLN_VIEWABLE
2914 -- to pass P_SET_ID to last subquery instead of join. Joined
2915 -- value is currently lost ? DB issue?
2916 --
2917 --
2918 -- Notes :
2919 -- 1) A 'Complete' Solution is as follows:
2920 -- -------------------------------------
2921 -- --> The Solution is associated to the Users Category Group
2922 -- --> The Category is Visible to the User
2923 -- --> The Solution is within the users Solution Visibility
2924 -- --> The users Statement Visibility allows access to all of the Solutions
2925 -- Statements
2926 --
2927 -- 2) If P_USER_ID is passed as -1 then the api will use the current User and
2928 -- Responsibility retrieved from FND_GLOBAL
2929 -- Used By:
2930 -- 1) Sending WF Notifications --> CS_KB_WF_PKG.Start_wf_processing
2931 -- 2) Check-Out button Logic on View Solution -->
2932 -- End of comments
2933 FUNCTION IS_COMPLETE_SOLUTION_VISIBLE ( P_USER_ID NUMBER,
2934 P_SET_ID NUMBER) RETURN VARCHAR2 IS
2935
2936 CURSOR GET_USER_RESPONSIBILITIES IS
2937 SELECT u.User_id, rg.responsibility_id, rg.responsibility_application_id
2938 FROM fnd_user_resp_groups rg,
2939 fnd_user u
2940 WHERE u.user_id = decode(P_USER_ID, -1, FND_GLOBAL.USER_ID, P_USER_ID)
2941 AND u.user_id = rg.user_id
2942 AND rg.responsibility_id = decode(P_USER_ID, -1, FND_GLOBAL.RESP_ID, rg.responsibility_id)
2943 AND sysdate between nvl(rg.start_date, sysdate) and nvl(rg.end_date, sysdate);
2944
2945 CURSOR GET_USER_SECURITY_PROFILES (v_user NUMBER, v_resp NUMBER, v_app NUMBER) IS
2946 SELECT TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC('CS_KB_ASSIGNED_CATEGORY_GROUP',
2947 v_user,
2948 v_resp,
2949 v_app ) ),
2950 TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL',
2951 v_user,
2952 v_resp,
2953 v_app ) ),
2954 TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC('CS_KB_ASSIGNED_STATEMENT_VISIBILITY_LEVEL',
2955 v_user,
2956 v_resp,
2957 v_app ))
2958 FROM dual;
2959
2960 CURSOR IS_COMPLETE_SOLN_VIEWABLE (v_cat_grp NUMBER,
2961 v_soln_vis NUMBER) IS
2962 --v_stmt_vis NUMBER) IS
2963 SELECT count(*)
2964 FROM CS_KB_SETS_B S,
2965 CS_KB_VISIBILITIES_B V
2966 WHERE S.Set_Id = P_SET_ID
2967 AND S.Visibility_id = V.Visibility_id
2968 AND V.Position >= (SELECT Vis.Position
2969 FROM CS_KB_VISIBILITIES_B Vis
2970 WHere Vis.Visibility_id = v_soln_vis
2971 AND sysdate BETWEEN nvl(Vis.Start_Date_Active, sysdate-1)
2972 AND nvl(Vis.End_Date_Active , sysdate+1)
2973 )
2974 AND EXISTS (SELECT 'x'
2975 FROM CS_KB_SET_CATEGORIES SC,
2976 CS_KB_CAT_GROUP_DENORM D
2977 WHERE SC.Set_id = S.Set_id
2978 AND D.Category_Group_Id = v_cat_grp
2979 AND SC.Category_id = D.Child_Category_id
2980 AND D.Visibility_Position >= (SELECT Vis.Position
2981 FROM CS_KB_VISIBILITIES_B Vis
2982 WHERE Vis.Visibility_id = v_soln_vis
2983 AND sysdate BETWEEN nvl(Vis.Start_Date_Active, sysdate-1)
2984 AND nvl(Vis.End_Date_Active , sysdate+1)
2985 )
2986
2987 );
2988 -- 02-Dec-2003 Commented as sql not 8.1.7 compliant
2989 -- AND (SELECT count(*) FROM CS_KB_SET_ELES SE WHERE SE.Set_id = S.Set_Id) =
2990 -- (SELECT count(*)
2991 -- FROM CS_KB_SET_ELES SE,
2992 -- CS_KB_ELEMENTS_B E
2993 -- WHERE SE.Set_id = P_SET_ID --S.Set_Id
2994 -- AND SE.Element_id = E.Element_Id
2995 -- AND E.Access_Level >= (SELECT lookup_code
2996 -- FROM cs_lookups
2997 -- WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
2998 -- AND lookup_code = v_stmt_vis
2999 -- AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
3000 -- AND nvl(End_Date_Active, sysdate+1)
3001 -- )
3002 -- );
3003
3004 CURSOR Get_Total_Soln_Statements IS
3005 SELECT count(*)
3006 FROM CS_KB_SET_ELES SE
3007 WHERE SE.Set_id = P_SET_ID;
3008
3009 CURSOR Get_Total_Visible_Statements ( v_stmt_vis NUMBER) IS
3010 SELECT count(*)
3011 FROM CS_KB_SET_ELES SE,
3012 CS_KB_ELEMENTS_B E
3013 WHERE SE.Set_id = P_SET_ID --S.Set_Id
3014 AND SE.Element_id = E.Element_Id
3015 AND E.Access_Level >= (SELECT lookup_code
3016 FROM cs_lookups
3017 WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
3018 AND lookup_code = v_stmt_vis
3019 AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
3020 AND nvl(End_Date_Active, sysdate+1)
3021 );
3022
3023 l_cat_grp NUMBER;
3024 l_soln_vis NUMBER;
3025 l_stmt_vis NUMBER;
3026 l_solution_count NUMBER;
3027 l_viewable VARCHAR2(10) := 'FALSE';
3028 l_stmt_count NUMBER;
3029 l_vis_stmt_count NUMBER;
3030
3031 BEGIN
3032
3033 -- Firstly retrieve the Users Responsibilities
3034 FOR UserResps IN Get_User_Responsibilities LOOP
3035
3036 -- Retrieve the Security Profiles for the User
3037 OPEN Get_User_Security_Profiles (UserResps.User_id,
3038 UserResps.Responsibility_Id,
3039 UserResps.Responsibility_Application_id);
3040 FETCH Get_User_Security_Profiles INTO l_cat_grp, l_soln_vis, l_stmt_vis;
3041 CLOSE Get_User_Security_Profiles;
3042
3043 --dbms_output.put_line('Profs->>> '||l_cat_grp||'<-->'||l_soln_vis||'<-->'||l_stmt_vis);
3044
3045 -- Call api to check complete Solution is Viewable by the User
3046 OPEN IS_COMPLETE_SOLN_VIEWABLE (l_cat_grp, l_soln_vis); --, l_stmt_vis);
3047 FETCH IS_COMPLETE_SOLN_VIEWABLE INTO l_solution_count;
3048 CLOSE IS_COMPLETE_SOLN_VIEWABLE;
3049
3050 OPEN Get_Total_Soln_Statements;
3051 FETCH Get_Total_Soln_Statements INTO l_stmt_count;
3052 CLOSE Get_Total_Soln_Statements;
3053
3054 OPEN Get_Total_Visible_Statements (l_stmt_vis);
3055 FETCH Get_Total_Visible_Statements INTO l_vis_stmt_count;
3056 CLOSE Get_Total_Visible_Statements;
3057
3058
3059
3060 --dbms_output.put_line('Solution Count: '||l_solution_count);
3061 -- If the Solution is Viewable return TRUE
3062 IF l_solution_count <> 0 AND
3063 l_stmt_count = l_vis_stmt_count THEN
3064 l_viewable := 'TRUE';
3065 EXIT;
3066 END IF;
3067
3068 END LOOP;
3069
3070 RETURN l_viewable;
3071
3072 END IS_COMPLETE_SOLUTION_VISIBLE;
3073
3074 PROCEDURE MOVE_CATEGORY_IN_DENORM (
3075 P_CATEGORY_ID IN NUMBER,
3076 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3077 X_MSG_DATA OUT NOCOPY VARCHAR2,
3078 X_MSG_COUNT OUT NOCOPY NUMBER
3079 ) IS
3080
3081
3082 CURSOR Get_Moved_Categories IS
3083 SELECT c.category_id, c.parent_category_id, c.visibility_id
3084 FROM CS_KB_SOLN_CATEGORIES_B c
3085 START WITH c.category_id = P_CATEGORY_ID
3086 CONNECT BY PRIOR c.category_id = c.parent_category_id
3087 ORDER BY level asc;
3088
3089 l_ret_status VARCHAR2(1);
3090 l_msg_data VARCHAR2(2000);
3091 l_msg_count NUMBER;
3092
3093 BEGIN
3094
3095 SAVEPOINT Move_Cat_In_Denorm_PVT;
3096
3097 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3098
3099
3100 -- Firstly Delete all the Records in the Denorm Table For the Moved
3101 -- Catageory and its Children except where this Category has been directly
3102 -- associated to a Category Group (via a link in CS_KB_CAT_GROUP_MEMBERS)
3103 DELETE FROM cs_kb_cat_group_denorm d
3104 WHERE d.child_category_id IN (SELECT b.category_id
3105 FROM cs_kb_soln_categories_b b
3106 START WITH b.category_id = P_CATEGORY_ID
3107 CONNECT BY PRIOR b.category_id =
3108 b.parent_category_id)
3109
3110 AND NOT EXISTS (SELECT 'x'
3111 FROM cs_kb_cat_group_members m
3112 WHERE m.category_id IN ( SELECT b.category_id
3113 FROM cs_kb_soln_categories_b b
3114 START WITH b.category_id =
3115 P_CATEGORY_ID
3116 CONNECT BY PRIOR
3117 b.category_id = b.parent_category_id)
3118 AND m.category_id = d.child_category_id
3119 AND m.category_group_id = d.category_group_id );
3120
3121
3122 -- Validate Category
3123
3124 FOR x IN Get_Moved_Categories LOOP
3125
3126
3127 ADD_CATEGORY_TO_DENORM ( P_CATEGORY_ID => x.category_id,
3128 P_PARENT_CATEGORY_ID => x.parent_category_id,
3129 P_VISIBILITY_ID => x.visibility_id,
3130 X_RETURN_STATUS => l_ret_status,
3131 X_MSG_DATA => l_msg_data,
3132 X_MSG_COUNT => l_msg_count );
3133
3134 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
3135 RAISE UPDATE_FAILED;
3136 END IF;
3137
3138 END LOOP;
3139
3140 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3141
3142 EXCEPTION
3143 WHEN UPDATE_FAILED THEN
3144 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3145 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
3146 'cs.plsql.CS_KB_SECURITY_PVT.MOVE_CAT_IN_DENORM.update',
3147 'P_CATEGORY_ID='||P_CATEGORY_ID --||
3148 --'P_PARENT_CATEGORY_ID='||P_PARENT_CATEGORY_ID||
3149 --'P_VISIBILITY_ID='||P_VISIBILITY_ID
3150 );
3151 END IF;
3152 ROLLBACK TO Move_Cat_In_Denorm_PVT;
3153 X_RETURN_STATUS := l_ret_status;
3154 X_MSG_COUNT := l_msg_count;
3155 X_MSG_DATA := l_msg_data;
3156
3157 WHEN OTHERS THEN
3158 ROLLBACK TO Move_Cat_In_Denorm_PVT;
3159 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3160 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.MOVE_CATEGORY_IN_DENORM.UNEXPECTED',
3161 ' Error= '||sqlerrm);
3162 END IF;
3163 FND_MESSAGE.set_name('CS', 'CS_KB_C_UNEXP_ERR');
3164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3165 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3166 p_data => x_msg_data);
3167 END MOVE_CATEGORY_IN_DENORM;
3168
3169 END CS_KB_SECURITY_PVT;