[Home] [Help]
PACKAGE BODY: APPS.CS_KB_SET_TYPES_PKG
Source
1 PACKAGE BODY CS_KB_SET_TYPES_PKG AS
2 /* $Header: cskbstb.pls 115.16 2003/11/19 23:39:10 mkettle ship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 | HISTORY |
9 | 18-OCT-1999 A. WONG Created |
10 | 01-DEC-1999 ALAM Added parameter name to create/update func. |
11 | 01-JAN-2000 ALAM Added translate_row and load_row procedure. |
12 | 28-JAN-2000 ALAM Modified delete_set_type function. |
13 | 25-APR-2001 SKLEONG Modified the delete_set_type function. |
14 | 14-AUG-2002 KLOU (SEDATE) |
15 | 1. Add logic to handle new columns start_date_active and|
16 | end_date_active. |
17 +======================================================================*/
18
19 FUNCTION Create_Set_Type(
20 p_name in varchar2,
21 p_desc in varchar2,
22 P_ATTRIBUTE_CATEGORY in VARCHAR2,
23 P_ATTRIBUTE1 in VARCHAR2,
24 P_ATTRIBUTE2 in VARCHAR2,
25 P_ATTRIBUTE3 in VARCHAR2,
26 P_ATTRIBUTE4 in VARCHAR2,
27 P_ATTRIBUTE5 in VARCHAR2,
28 P_ATTRIBUTE6 in VARCHAR2,
29 P_ATTRIBUTE7 in VARCHAR2,
30 P_ATTRIBUTE8 in VARCHAR2,
31 P_ATTRIBUTE9 in VARCHAR2,
32 P_ATTRIBUTE10 in VARCHAR2,
33 P_ATTRIBUTE11 in VARCHAR2,
34 P_ATTRIBUTE12 in VARCHAR2,
35 P_ATTRIBUTE13 in VARCHAR2,
36 P_ATTRIBUTE14 in VARCHAR2,
37 P_ATTRIBUTE15 in VARCHAR2,
38 P_START_DATE in DATE,
39 P_END_DATE in DATE
40 ) return number is
41 l_date date;
42 l_created_by number;
43 l_login number;
44 l_count pls_integer;
45 l_id number;
46 l_rowid varchar2(30);
47 begin
48
49 -- Check params
50 if(p_desc is null or p_name is null) then
51 goto error_found;
52 end if;
53
54
55 --prepare data, then insert new set_type
56 select cs_kb_set_types_s.nextval into l_id from dual;
57 l_date := sysdate;
58 l_created_by := fnd_global.user_id;
59 l_login := fnd_global.login_id;
60
61 CS_KB_SET_TYPES_PKG.Insert_Row(
62 X_Rowid => l_rowid,
63 X_Set_Type_Id => l_id,
64 X_Name => p_name,
65 X_Description => p_desc,
66 X_Creation_Date => l_date,
67 X_Created_By => l_created_by,
68 X_Last_Update_Date => l_date,
69 X_Last_Updated_By => l_created_by,
70 X_Last_Update_Login => l_login,
71 X_Set_Type_Name => null,
72 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
73 X_ATTRIBUTE1 => P_ATTRIBUTE1,
74 X_ATTRIBUTE2 => P_ATTRIBUTE2,
75 X_ATTRIBUTE3 => P_ATTRIBUTE3,
76 X_ATTRIBUTE4 => P_ATTRIBUTE4,
77 X_ATTRIBUTE5 => P_ATTRIBUTE5,
78 X_ATTRIBUTE6 => P_ATTRIBUTE6,
79 X_ATTRIBUTE7 => P_ATTRIBUTE7,
80 X_ATTRIBUTE8 => P_ATTRIBUTE8,
81 X_ATTRIBUTE9 => P_ATTRIBUTE9,
82 X_ATTRIBUTE10 => P_ATTRIBUTE10,
83 X_ATTRIBUTE11 => P_ATTRIBUTE11,
84 X_ATTRIBUTE12 => P_ATTRIBUTE12,
85 X_ATTRIBUTE13 => P_ATTRIBUTE13,
86 X_ATTRIBUTE14 => P_ATTRIBUTE14,
87 X_ATTRIBUTE15 => P_ATTRIBUTE15,
88 X_START_DATE => P_START_DATE,
89 X_END_DATE => P_END_DATE
90
91 );
92 return l_id;
93
94 <<error_found>>
95 return ERROR_STATUS;
96 end Create_Set_Type;
97
98
99
100 -- Update Set_Type data
101 --
102 FUNCTION Update_Set_Type(
103 p_set_type_id in number,
104 p_name in varchar2,
105 p_desc in varchar2,
106 P_ATTRIBUTE_CATEGORY in VARCHAR2,
107 P_ATTRIBUTE1 in VARCHAR2,
108 P_ATTRIBUTE2 in VARCHAR2,
109 P_ATTRIBUTE3 in VARCHAR2,
110 P_ATTRIBUTE4 in VARCHAR2,
111 P_ATTRIBUTE5 in VARCHAR2,
112 P_ATTRIBUTE6 in VARCHAR2,
113 P_ATTRIBUTE7 in VARCHAR2,
114 P_ATTRIBUTE8 in VARCHAR2,
115 P_ATTRIBUTE9 in VARCHAR2,
116 P_ATTRIBUTE10 in VARCHAR2,
117 P_ATTRIBUTE11 in VARCHAR2,
118 P_ATTRIBUTE12 in VARCHAR2,
119 P_ATTRIBUTE13 in VARCHAR2,
120 P_ATTRIBUTE14 in VARCHAR2,
121 P_ATTRIBUTE15 in VARCHAR2,
122 P_START_DATE in DATE,
123 P_END_DATE in DATE
124 ) return number is
125 l_ret number;
126 l_date date;
127 l_updated_by number;
128 l_login number;
129 l_count pls_integer;
130 begin
131
132 -- validate params
133 if(p_set_type_id is null) then
134 goto error_found;
135 end if;
136
137 --prepare data, then insert new set_type
138 l_date := sysdate;
139 l_updated_by := fnd_global.user_id;
140 l_login := fnd_global.login_id;
141
142 CS_KB_SET_TYPES_PKG.Update_Row(
143 X_Set_Type_Id => p_set_type_id,
144 X_Name => p_name,
145 X_Description => p_desc,
146 X_Last_Update_Date => l_date,
147 X_Last_Updated_By => l_updated_by,
148 X_Last_Update_Login => l_login,
149 X_Set_Type_Name => null,
150 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
151 X_ATTRIBUTE1 => P_ATTRIBUTE1,
152 X_ATTRIBUTE2 => P_ATTRIBUTE2,
153 X_ATTRIBUTE3 => P_ATTRIBUTE3,
154 X_ATTRIBUTE4 => P_ATTRIBUTE4,
155 X_ATTRIBUTE5 => P_ATTRIBUTE5,
156 X_ATTRIBUTE6 => P_ATTRIBUTE6,
157 X_ATTRIBUTE7 => P_ATTRIBUTE7,
158 X_ATTRIBUTE8 => P_ATTRIBUTE8,
159 X_ATTRIBUTE9 => P_ATTRIBUTE9,
160 X_ATTRIBUTE10 => P_ATTRIBUTE10,
161 X_ATTRIBUTE11 => P_ATTRIBUTE11,
162 X_ATTRIBUTE12 => P_ATTRIBUTE12,
163 X_ATTRIBUTE13 => P_ATTRIBUTE13,
164 X_ATTRIBUTE14 => P_ATTRIBUTE14,
165 X_ATTRIBUTE15 => P_ATTRIBUTE15,
166 X_START_DATE => P_START_DATE,
167 X_END_DATE => P_END_DATE);
168
169 return OKAY_STATUS;
170 <<error_found>>
171 return ERROR_STATUS;
172 exception
173 when others then
174 return ERROR_STATUS;
175 end Update_Set_Type;
176
177 --
178 -- Delete set type
179
180 FUNCTION Delete_Set_Type(
181 p_set_type_id in number
182 ) return number is
183 l_ret number;
184 l_count pls_integer;
185 begin
186 if p_set_type_id is null or p_set_type_id <= 0 then
187 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_TYPE_ID');
188 return ERROR_STATUS;
189 end if;
190
191 --
192 --
193 select count(*) into l_count
194 from cs_kb_set_types_b
195 where set_type_id = p_set_type_id;
196 if(l_count <= 0) then
197 fnd_message.set_name('CS', 'CS_KB_C_INVALID_SET_TYPE_ID');
198 return ERROR_STATUS;
199 end if;
200
201 -- Check element links
202 --
203 select count(*) into l_count
204 from cs_kb_set_ele_types
205 where set_type_id = p_set_type_id;
206 if(l_count > 0) then
207 fnd_message.set_name('CS', 'CS_KB_C_SET_TYPE_WITH_ELE_TYPE');
208 return ERROR_STATUS; end if;
209
210 -- Check external links
211 --
212 select count(*) into l_count
213 from cs_kb_set_type_links
214 where set_type_id = p_set_type_id;
215 if(l_count > 0) then
216 fnd_message.set_name('CS', 'CS_KB_C_SET_TYPE_WITH_LINK');
217 return ERROR_STATUS; end if;
218
219 -- Check sets with defined set type
220 --
221 -- Commented 19-Nov-2003 - Duplicate code with below
222 -- select count(*) into l_count
223 -- from cs_kb_sets_vl
224 -- where set_type_id = p_set_type_id;
225 -- if(l_count > 0) then
226 -- fnd_message.set_name('CS', 'CS_KB_C_SET_TYPE_WITH_SET');
227 -- return ERROR_STATUS; end if;
228
229 select /*+ INDEX(s) */ count(*) into l_count
230 from cs_kb_sets_b s
231 where s.set_type_id = p_set_type_id
232 and (s.latest_version_flag = 'Y' OR s.viewable_version_flag = 'Y')
233 and s.status <> 'OBS';
234 if(l_count > 0) then
235 fnd_message.set_name('CS', 'CS_KB_C_SET_TYPE_WITH_SET');
236 return ERROR_STATUS; end if;
237
238
239 CS_KB_SET_TYPES_PKG.Delete_Row(
240 X_Set_Type_Id => p_set_type_id);
241
242 return OKAY_STATUS;
243 <<error_found>>
244 return ERROR_STATUS;
245
246 exception
247 WHEN OTHERS THEN
248 return ERROR_STATUS;
249 end Delete_Set_Type;
250
251
252 procedure INSERT_ROW (
253 X_ROWID in OUT NOCOPY VARCHAR2,
254 X_SET_TYPE_ID in NUMBER,
255 X_SET_TYPE_NAME in VARCHAR2,
256 X_NAME in VARCHAR2,
257 X_DESCRIPTION in VARCHAR2,
258 X_CREATION_DATE in DATE,
259 X_CREATED_BY in NUMBER,
260 X_LAST_UPDATE_DATE in DATE,
261 X_LAST_UPDATED_BY in NUMBER,
262 X_LAST_UPDATE_LOGIN in NUMBER,
263 X_ATTRIBUTE_CATEGORY in VARCHAR2,
264 X_ATTRIBUTE1 in VARCHAR2,
265 X_ATTRIBUTE2 in VARCHAR2,
266 X_ATTRIBUTE3 in VARCHAR2,
267 X_ATTRIBUTE4 in VARCHAR2,
268 X_ATTRIBUTE5 in VARCHAR2,
269 X_ATTRIBUTE6 in VARCHAR2,
270 X_ATTRIBUTE7 in VARCHAR2,
271 X_ATTRIBUTE8 in VARCHAR2,
272 X_ATTRIBUTE9 in VARCHAR2,
273 X_ATTRIBUTE10 in VARCHAR2,
274 X_ATTRIBUTE11 in VARCHAR2,
275 X_ATTRIBUTE12 in VARCHAR2,
276 X_ATTRIBUTE13 in VARCHAR2,
277 X_ATTRIBUTE14 in VARCHAR2,
278 X_ATTRIBUTE15 in VARCHAR2,
279 X_START_DATE in DATE,
280 X_END_DATE in DATE
281 ) is
282 cursor C is select ROWID from CS_KB_SET_TYPES_B
283 where SET_TYPE_ID = X_SET_TYPE_ID
284 ;
285 begin
286 insert into CS_KB_SET_TYPES_B (
287 SET_TYPE_ID,
288 SET_TYPE_NAME,
289 CREATION_DATE,
290 CREATED_BY,
291 LAST_UPDATE_DATE,
292 LAST_UPDATED_BY,
293 LAST_UPDATE_LOGIN,
294 ATTRIBUTE_CATEGORY,
295 ATTRIBUTE1,
296 ATTRIBUTE2,
297 ATTRIBUTE3,
298 ATTRIBUTE4,
299 ATTRIBUTE5,
300 ATTRIBUTE6,
301 ATTRIBUTE7,
302 ATTRIBUTE8,
303 ATTRIBUTE9,
304 ATTRIBUTE10,
305 ATTRIBUTE11,
306 ATTRIBUTE12,
307 ATTRIBUTE13,
308 ATTRIBUTE14,
309 ATTRIBUTE15,
310 START_DATE_ACTIVE,
311 END_DATE_ACTIVE
312 ) values (
313 X_SET_TYPE_ID,
314 X_SET_TYPE_NAME,
315 X_CREATION_DATE,
316 X_CREATED_BY,
317 X_LAST_UPDATE_DATE,
318 X_LAST_UPDATED_BY,
319 X_LAST_UPDATE_LOGIN,
320 X_ATTRIBUTE_CATEGORY,
321 X_ATTRIBUTE1,
322 X_ATTRIBUTE2,
323 X_ATTRIBUTE3,
324 X_ATTRIBUTE4,
325 X_ATTRIBUTE5,
326 X_ATTRIBUTE6,
327 X_ATTRIBUTE7,
328 X_ATTRIBUTE8,
329 X_ATTRIBUTE9,
330 X_ATTRIBUTE10,
331 X_ATTRIBUTE11,
332 X_ATTRIBUTE12,
333 X_ATTRIBUTE13,
334 X_ATTRIBUTE14,
335 X_ATTRIBUTE15,
336 X_START_DATE,
337 X_END_DATE
338 );
339
340 insert into CS_KB_SET_TYPES_TL (
341 SET_TYPE_ID,
342 NAME,
343 DESCRIPTION,
344 CREATION_DATE,
345 CREATED_BY,
346 LAST_UPDATE_DATE,
347 LAST_UPDATED_BY,
348 LAST_UPDATE_LOGIN,
349 LANGUAGE,
350 SOURCE_LANG
351 ) select
352 X_SET_TYPE_ID,
353 X_NAME,
354 X_DESCRIPTION,
355 X_CREATION_DATE,
356 X_CREATED_BY,
357 X_LAST_UPDATE_DATE,
358 X_LAST_UPDATED_BY,
359 X_LAST_UPDATE_LOGIN,
360 L.LANGUAGE_CODE,
361 userenv('LANG')
362 from FND_LANGUAGES L
363 where L.INSTALLED_FLAG in ('I', 'B')
364 and not exists
365 (select NULL
366 from CS_KB_SET_TYPES_TL T
367 where T.SET_TYPE_ID = X_SET_TYPE_ID
368 and T.LANGUAGE = L.LANGUAGE_CODE);
369
370 open c;
371 fetch c into X_ROWID;
372 if (c%notfound) then
373 close c;
374 raise no_data_found;
375 end if;
376 close c;
377
378 end INSERT_ROW;
379
380 procedure LOCK_ROW (
381 X_SET_TYPE_ID in NUMBER,
382 X_SET_TYPE_NAME in VARCHAR2,
383 X_NAME in VARCHAR2,
384 X_DESCRIPTION in VARCHAR2,
385 X_ATTRIBUTE_CATEGORY in VARCHAR2,
386 X_ATTRIBUTE1 in VARCHAR2,
387 X_ATTRIBUTE2 in VARCHAR2,
388 X_ATTRIBUTE3 in VARCHAR2,
389 X_ATTRIBUTE4 in VARCHAR2,
390 X_ATTRIBUTE5 in VARCHAR2,
391 X_ATTRIBUTE6 in VARCHAR2,
392 X_ATTRIBUTE7 in VARCHAR2,
393 X_ATTRIBUTE8 in VARCHAR2,
394 X_ATTRIBUTE9 in VARCHAR2,
395 X_ATTRIBUTE10 in VARCHAR2,
396 X_ATTRIBUTE11 in VARCHAR2,
397 X_ATTRIBUTE12 in VARCHAR2,
398 X_ATTRIBUTE13 in VARCHAR2,
399 X_ATTRIBUTE14 in VARCHAR2,
400 X_ATTRIBUTE15 in VARCHAR2,
401 X_START_DATE in DATE,
402 X_END_DATE in DATE
403 ) is
404 cursor c is select
405 SET_TYPE_ID,
406 SET_TYPE_NAME,
407 ATTRIBUTE_CATEGORY,
408 ATTRIBUTE1,
409 ATTRIBUTE2,
410 ATTRIBUTE3,
411 ATTRIBUTE4,
412 ATTRIBUTE5,
413 ATTRIBUTE6,
414 ATTRIBUTE7,
415 ATTRIBUTE8,
416 ATTRIBUTE9,
417 ATTRIBUTE10,
418 ATTRIBUTE11,
419 ATTRIBUTE12,
420 ATTRIBUTE13,
421 ATTRIBUTE14,
422 ATTRIBUTE15,
423 START_DATE_ACTIVE,
424 END_DATE_ACTIVE
425 from CS_KB_SET_TYPES_B
426 where SET_TYPE_ID = X_SET_TYPE_ID
427 for update of SET_TYPE_ID nowait;
428 recinfo c%rowtype;
429
430 cursor c1 is select
434 from CS_KB_SET_TYPES_TL
431 NAME,
432 DESCRIPTION,
433 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
435 where SET_TYPE_ID = X_SET_TYPE_ID
436 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
437 for update of SET_TYPE_ID nowait;
438 begin
439 open c;
440 fetch c into recinfo;
441 if (c%notfound) then
442 close c;
443 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
444 app_exception.raise_exception;
445 end if;
446 close c;
447 if (
448 ((recinfo.SET_TYPE_ID = X_SET_TYPE_ID)
449 OR ((recinfo.SET_TYPE_ID is null) AND (X_SET_TYPE_ID is null)))
450 AND ((recinfo.SET_TYPE_NAME = X_SET_TYPE_NAME)
451 OR ((recinfo.SET_TYPE_NAME is null) AND (X_SET_TYPE_NAME is null)))
452 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
453 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
454 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
455 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
456 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
457 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
458 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
459 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
460 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
461 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
462 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
463 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
464 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
465 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
466 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
467 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
468 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
469 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
470 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
471 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
472 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
473 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
474 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
475 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
476 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
477 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
478 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
479 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
480 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
481 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
482 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
483 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
484 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE)
485 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE is null)))
486 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE)
487 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE is null)))
488 ) then
489 null;
490 else
491 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
492 app_exception.raise_exception;
493 end if;
494
495 for tlinfo in c1 loop
496 if (tlinfo.BASELANG = 'Y') then
497 if ( ((tlinfo.NAME = X_NAME)
498 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
499 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
500 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
501 ) then
502 null;
503 else
504 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
505 app_exception.raise_exception;
506 end if;
507 end if;
508 end loop;
509 return;
510 end LOCK_ROW;
511
512 procedure UPDATE_ROW (
513 X_SET_TYPE_ID in NUMBER,
514 X_SET_TYPE_NAME in VARCHAR2,
515 X_NAME in VARCHAR2,
516 X_DESCRIPTION in VARCHAR2,
517 X_LAST_UPDATE_DATE in DATE,
518 X_LAST_UPDATED_BY in NUMBER,
519 X_LAST_UPDATE_LOGIN in NUMBER,
520 X_ATTRIBUTE_CATEGORY in VARCHAR2,
521 X_ATTRIBUTE1 in VARCHAR2,
522 X_ATTRIBUTE2 in VARCHAR2,
523 X_ATTRIBUTE3 in VARCHAR2,
524 X_ATTRIBUTE4 in VARCHAR2,
525 X_ATTRIBUTE5 in VARCHAR2,
526 X_ATTRIBUTE6 in VARCHAR2,
527 X_ATTRIBUTE7 in VARCHAR2,
528 X_ATTRIBUTE8 in VARCHAR2,
529 X_ATTRIBUTE9 in VARCHAR2,
530 X_ATTRIBUTE10 in VARCHAR2,
531 X_ATTRIBUTE11 in VARCHAR2,
532 X_ATTRIBUTE12 in VARCHAR2,
533 X_ATTRIBUTE13 in VARCHAR2,
534 X_ATTRIBUTE14 in VARCHAR2,
535 X_ATTRIBUTE15 in VARCHAR2,
536 X_START_DATE in DATE,
537 X_END_DATE in DATE
538 ) is
539 begin
540 update CS_KB_SET_TYPES_B set
541 SET_TYPE_NAME = X_SET_TYPE_NAME,
542 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
543 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
544 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
545 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
546 ATTRIBUTE1 = X_ATTRIBUTE1,
547 ATTRIBUTE2 = X_ATTRIBUTE2,
548 ATTRIBUTE3 = X_ATTRIBUTE3,
549 ATTRIBUTE4 = X_ATTRIBUTE4,
550 ATTRIBUTE5 = X_ATTRIBUTE5,
551 ATTRIBUTE6 = X_ATTRIBUTE6,
552 ATTRIBUTE7 = X_ATTRIBUTE7,
556 ATTRIBUTE11 = X_ATTRIBUTE11,
553 ATTRIBUTE8 = X_ATTRIBUTE8,
554 ATTRIBUTE9 = X_ATTRIBUTE9,
555 ATTRIBUTE10 = X_ATTRIBUTE10,
557 ATTRIBUTE12 = X_ATTRIBUTE12,
558 ATTRIBUTE13 = X_ATTRIBUTE13,
559 ATTRIBUTE14 = X_ATTRIBUTE14,
560 ATTRIBUTE15 = X_ATTRIBUTE15,
561 START_DATE_ACTIVE = X_START_DATE,
562 END_DATE_ACTIVE = X_END_DATE
563 where SET_TYPE_ID = X_SET_TYPE_ID;
564
565 if (sql%notfound) then
566 raise no_data_found;
567 end if;
568
569 update CS_KB_SET_TYPES_TL set
570 NAME = X_NAME,
571 DESCRIPTION = X_DESCRIPTION,
572 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
573 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
574 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
575 SOURCE_LANG = userenv('LANG')
576 where SET_TYPE_ID = X_SET_TYPE_ID
577 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
578
579 if (sql%notfound) then
580 raise no_data_found;
581 end if;
582 end UPDATE_ROW;
583
584 procedure DELETE_ROW (
585 X_SET_TYPE_ID in NUMBER
586 ) is
587 begin
588 delete from CS_KB_SET_TYPES_TL
589 where SET_TYPE_ID = X_SET_TYPE_ID;
590
591 if (sql%notfound) then
592 raise no_data_found;
593 end if;
594
595 delete from CS_KB_SET_TYPES_B
596 where SET_TYPE_ID = X_SET_TYPE_ID;
597
598 if (sql%notfound) then
599 raise no_data_found;
600 end if;
601 end DELETE_ROW;
602
603 procedure ADD_LANGUAGE
604 is
605 begin
606 delete from CS_KB_SET_TYPES_TL T
607 where not exists
608 (select NULL
609 from CS_KB_SET_TYPES_B B
610 where B.SET_TYPE_ID = T.SET_TYPE_ID
611 );
612
613 update CS_KB_SET_TYPES_TL T set (
614 NAME,
615 DESCRIPTION
616 ) = (select
617 B.NAME,
618 B.DESCRIPTION
619 from CS_KB_SET_TYPES_TL B
620 where B.SET_TYPE_ID = T.SET_TYPE_ID
621 and B.LANGUAGE = T.SOURCE_LANG)
622 where (
623 T.SET_TYPE_ID,
624 T.LANGUAGE
625 ) in (select
626 SUBT.SET_TYPE_ID,
627 SUBT.LANGUAGE
628 from CS_KB_SET_TYPES_TL SUBB, CS_KB_SET_TYPES_TL SUBT
629 where SUBB.SET_TYPE_ID = SUBT.SET_TYPE_ID
630 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
631 and (SUBB.NAME <> SUBT.NAME
632 or (SUBB.NAME is null and SUBT.NAME is not null)
633 or (SUBB.NAME is not null and SUBT.NAME is null)
634 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
635 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
636 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
637 ));
638
639 insert into CS_KB_SET_TYPES_TL (
640 SET_TYPE_ID,
641 NAME,
642 DESCRIPTION,
643 CREATION_DATE,
644 CREATED_BY,
645 LAST_UPDATE_DATE,
646 LAST_UPDATED_BY,
647 LAST_UPDATE_LOGIN,
648 LANGUAGE,
649 SOURCE_LANG
650 ) select
651 B.SET_TYPE_ID,
652 B.NAME,
653 B.DESCRIPTION,
654 B.CREATION_DATE,
655 B.CREATED_BY,
656 B.LAST_UPDATE_DATE,
657 B.LAST_UPDATED_BY,
658 B.LAST_UPDATE_LOGIN,
659 L.LANGUAGE_CODE,
660 B.SOURCE_LANG
661 from CS_KB_SET_TYPES_TL B, FND_LANGUAGES L
662 where L.INSTALLED_FLAG in ('I', 'B')
663 and B.LANGUAGE = userenv('LANG')
664 and not exists
665 (select NULL
666 from CS_KB_SET_TYPES_TL T
667 where T.SET_TYPE_ID = B.SET_TYPE_ID
668 and T.LANGUAGE = L.LANGUAGE_CODE);
669 end ADD_LANGUAGE;
670
671
672 PROCEDURE TRANSLATE_ROW(
673 x_set_type_id in number,
674 x_owner in varchar2,
675 x_name in varchar2,
676 x_description in varchar2) is
677 l_user_id number;
678
679 begin
680
681 update CS_KB_SET_TYPES_TL set
682 NAME = X_NAME,
683 DESCRIPTION=X_DESCRIPTION,
684 last_update_date = sysdate,
685 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
686 last_update_login = 0,
687 source_lang = userenv('LANG')
688 where SET_TYPE_ID = to_number(X_SET_TYPE_ID)
689 and userenv('LANG') in (language, source_lang);
690
691 end;
692
693
694
695 PROCEDURE LOAD_ROW(
696 x_set_type_id in number,
697 x_owner in varchar2,
698 x_name in varchar2,
699 x_description in varchar2) is
700 l_user_id number;
701 l_rowid varchar2(100);
702 begin
703
704 if (x_owner = 'SEED') then
705 l_user_id := 1;
706 else
707 l_user_id := 0;
708 end if;
709
710 CS_KB_SET_TYPES_PKG.Update_Row(
711 X_Set_Type_Id => x_set_type_id,
712 X_Name => x_name,
713 X_Description => x_description,
714 X_Last_Update_Date => sysdate,
715 X_Last_Updated_By => l_user_id,
716 X_Last_Update_Login => 0,
717 X_Set_Type_Name => null);
718 exception
719 when no_data_found then
720 CS_KB_SET_TYPES_PKG.Insert_Row(
721 X_Rowid => l_rowid,
722 X_Set_Type_Id => x_set_type_id,
723 X_Name => x_name,
724 X_Description => x_description,
725 X_Creation_Date => sysdate,
726 X_Created_By => l_user_id,
727 X_Last_Update_Date => sysdate,
728 X_Last_Updated_By => l_user_id,
729 X_Last_Update_Login => 0,
730 X_Set_Type_Name => null);
731
732 end;
733
734
735
736 end CS_KB_SET_TYPES_PKG;