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