1 package body CS_INCIDENT_SEVERITIES_PKG as
2 /* $Header: csvidisb.pls 115.6 2003/09/01 12:43:19 anmukher ship $ */
3 procedure INSERT_ROW(
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INCIDENT_SEVERITY_ID in NUMBER,
6 X_DISPLAY_COLOR in VARCHAR2,
7 X_ATTRIBUTE6 in VARCHAR2,
8 X_INCIDENT_SUBTYPE in VARCHAR2,
9 X_IMPORTANCE_LEVEL in NUMBER,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_ATTRIBUTE1 in VARCHAR2,
13 X_ATTRIBUTE2 in VARCHAR2,
14 X_ATTRIBUTE3 in VARCHAR2,
15 X_ATTRIBUTE4 in VARCHAR2,
16 X_ATTRIBUTE5 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_CONTEXT in VARCHAR2,
27 X_OBJECT_VERSION_NUMBER in NUMBER,
28 X_RGB_COLOR in VARCHAR2,
29 X_NAME in VARCHAR2,
30 X_DESCRIPTION in VARCHAR2,
31 X_DEFECT_SEVERITY_ID in NUMBER,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER,
37 --Added priority code for Misc ERs project (11.5.10) --anmukher --09/01/03
38 X_PRIORITY_CODE in VARCHAR2
39 ) is
40 cursor C is select ROWID from CS_INCIDENT_SEVERITIES_B
41 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID
42 ;
43 begin
44 insert into CS_INCIDENT_SEVERITIES_B (
45 INCIDENT_SEVERITY_ID,
46 DISPLAY_COLOR,
47 ATTRIBUTE6,
48 INCIDENT_SUBTYPE,
49 IMPORTANCE_LEVEL,
50 START_DATE_ACTIVE,
51 END_DATE_ACTIVE,
52 ATTRIBUTE1,
53 ATTRIBUTE2,
54 ATTRIBUTE3,
55 ATTRIBUTE4,
56 ATTRIBUTE5,
57 ATTRIBUTE7,
58 ATTRIBUTE8,
59 ATTRIBUTE9,
60 ATTRIBUTE10,
61 ATTRIBUTE11,
62 ATTRIBUTE12,
63 ATTRIBUTE13,
64 ATTRIBUTE14,
65 ATTRIBUTE15,
66 CONTEXT,
67 OBJECT_VERSION_NUMBER,
68 RGB_COLOR,
69 DEFECT_SEVERITY_ID,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN,
75 -- Added priority code --anmukher --09/01/03
76 PRIORITY_CODE
77 ) values (
78 X_INCIDENT_SEVERITY_ID,
79 X_DISPLAY_COLOR,
80 X_ATTRIBUTE6,
81 X_INCIDENT_SUBTYPE,
82 X_IMPORTANCE_LEVEL,
83 X_START_DATE_ACTIVE,
84 X_END_DATE_ACTIVE,
85 X_ATTRIBUTE1,
86 X_ATTRIBUTE2,
87 X_ATTRIBUTE3,
88 X_ATTRIBUTE4,
89 X_ATTRIBUTE5,
90 X_ATTRIBUTE7,
91 X_ATTRIBUTE8,
92 X_ATTRIBUTE9,
93 X_ATTRIBUTE10,
94 X_ATTRIBUTE11,
95 X_ATTRIBUTE12,
96 X_ATTRIBUTE13,
97 X_ATTRIBUTE14,
98 X_ATTRIBUTE15,
99 X_CONTEXT,
100 X_OBJECT_VERSION_NUMBER,
101 X_RGB_COLOR,
102 X_DEFECT_SEVERITY_ID,
103 X_CREATION_DATE,
104 X_CREATED_BY,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATED_BY,
107 X_LAST_UPDATE_LOGIN,
108 -- Added priority code --anmukher --09/01/03
109 X_PRIORITY_CODE
110 );
111
112 insert into CS_INCIDENT_SEVERITIES_TL (
113 INCIDENT_SEVERITY_ID,
114 NAME,
115 DESCRIPTION,
116 CREATED_BY,
117 CREATION_DATE,
118 LAST_UPDATED_BY,
119 LAST_UPDATE_DATE,
120 LAST_UPDATE_LOGIN,
121 LANGUAGE,
122 SOURCE_LANG
123 ) select
124 X_INCIDENT_SEVERITY_ID,
125 X_NAME,
126 X_DESCRIPTION,
127 X_CREATED_BY,
128 X_CREATION_DATE,
129 X_LAST_UPDATED_BY,
130 X_LAST_UPDATE_DATE,
131 X_LAST_UPDATE_LOGIN,
132 L.LANGUAGE_CODE,
133 userenv('LANG')
134 from FND_LANGUAGES L
135 where L.INSTALLED_FLAG in ('I', 'B')
136 and not exists
137 (select NULL
138 from CS_INCIDENT_SEVERITIES_TL T
139 where T.INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID
140 and T.LANGUAGE = L.LANGUAGE_CODE);
141
142 open c;
143 fetch c into X_ROWID;
144 if (c%notfound) then
145 close c;
146 raise no_data_found;
147 end if;
148 close c;
149
150 end INSERT_ROW;
151
152 procedure LOCK_ROW (
153 X_INCIDENT_SEVERITY_ID in NUMBER,
154 X_DISPLAY_COLOR in VARCHAR2,
155 X_ATTRIBUTE6 in VARCHAR2,
156 X_INCIDENT_SUBTYPE in VARCHAR2,
157 X_IMPORTANCE_LEVEL in NUMBER,
158 X_START_DATE_ACTIVE in DATE,
159 X_END_DATE_ACTIVE in DATE,
160 X_ATTRIBUTE1 in VARCHAR2,
161 X_ATTRIBUTE2 in VARCHAR2,
162 X_ATTRIBUTE3 in VARCHAR2,
163 X_ATTRIBUTE4 in VARCHAR2,
164 X_ATTRIBUTE5 in VARCHAR2,
165 X_ATTRIBUTE7 in VARCHAR2,
166 X_ATTRIBUTE8 in VARCHAR2,
167 X_ATTRIBUTE9 in VARCHAR2,
168 X_ATTRIBUTE10 in VARCHAR2,
169 X_ATTRIBUTE11 in VARCHAR2,
170 X_ATTRIBUTE12 in VARCHAR2,
171 X_ATTRIBUTE13 in VARCHAR2,
172 X_ATTRIBUTE14 in VARCHAR2,
173 X_ATTRIBUTE15 in VARCHAR2,
174 X_CONTEXT in VARCHAR2,
175 X_OBJECT_VERSION_NUMBER in NUMBER,
176 X_RGB_COLOR in VARCHAR2,
177 X_NAME in VARCHAR2,
178 X_DESCRIPTION in VARCHAR2,
179 X_DEFECT_SEVERITY_ID in NUMBER,
180 --Added priority code for Misc ERs project (11.5.10) --anmukher --09/01/03
181 X_PRIORITY_CODE in VARCHAR2
182 ) is
183 cursor c is select
184 DISPLAY_COLOR,
185 ATTRIBUTE6,
186 INCIDENT_SUBTYPE,
187 IMPORTANCE_LEVEL,
188 START_DATE_ACTIVE,
189 END_DATE_ACTIVE,
190 --SEEDED_FLAG,
191 ATTRIBUTE1,
192 ATTRIBUTE2,
193 ATTRIBUTE3,
194 ATTRIBUTE4,
195 ATTRIBUTE5,
196 ATTRIBUTE7,
197 ATTRIBUTE8,
198 ATTRIBUTE9,
199 ATTRIBUTE10,
200 ATTRIBUTE11,
201 ATTRIBUTE12,
202 ATTRIBUTE13,
203 ATTRIBUTE14,
204 ATTRIBUTE15,
205 CONTEXT,
206 OBJECT_VERSION_NUMBER,
207 RGB_COLOR,
208 DEFECT_SEVERITY_ID,
209 -- Added priority code --anmukher --09/01/03
210 PRIORITY_CODE
211 from CS_INCIDENT_SEVERITIES_B
212 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID
213 for update of INCIDENT_SEVERITY_ID nowait;
214 recinfo c%rowtype;
215
216 cursor c1 is select
217 NAME,
218 DESCRIPTION,
219 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
220 from CS_INCIDENT_SEVERITIES_TL
221 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID
222 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
223 for update of INCIDENT_SEVERITY_ID nowait;
224 begin
225 open c;
226 fetch c into recinfo;
227 if (c%notfound) then
228 close c;
229 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
230 app_exception.raise_exception;
231 end if;
232 close c;
233 if ( ((recinfo.DISPLAY_COLOR = X_DISPLAY_COLOR)
234 OR ((recinfo.DISPLAY_COLOR is null) AND (X_DISPLAY_COLOR is null)))
235 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
236 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
237 AND (recinfo.INCIDENT_SUBTYPE = X_INCIDENT_SUBTYPE)
238 AND (recinfo.IMPORTANCE_LEVEL = X_IMPORTANCE_LEVEL)
239 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
240 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
241 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
242 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
243 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
244 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
245 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
246 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
247 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
248 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
249 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
250 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
251 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
252 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
253 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
254 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
255 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
256 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
257 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
258 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
259 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
260 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
261 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
262 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
263 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
264 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
265 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
266 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
267 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
268 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
269 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
270 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
271 AND ((recinfo.CONTEXT = X_CONTEXT)
272 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
273 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
274 AND ((recinfo.RGB_COLOR = X_RGB_COLOR)
275 OR ((recinfo.RGB_COLOR is null) AND (X_RGB_COLOR is null)))
276 AND ((recinfo.DEFECT_SEVERITY_ID = X_DEFECT_SEVERITY_ID)
277 OR ((recinfo.DEFECT_SEVERITY_ID is null)
278 AND (X_DEFECT_SEVERITY_ID is null)))
279 -- Added check for priority code --anmukher --09/01/03
280 AND ((recinfo.PRIORITY_CODE = X_PRIORITY_CODE)
281 OR ((recinfo.PRIORITY_CODE is null) AND (X_PRIORITY_CODE is null)))
282 ) then
283 null;
284 else
285 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
286 app_exception.raise_exception;
287 end if;
288
289 for tlinfo in c1 loop
290 if (tlinfo.BASELANG = 'Y') then
291 if ( (tlinfo.NAME = X_NAME)
292 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
293 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
294 ) then
295 null;
296 else
297 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
298 app_exception.raise_exception;
299 end if;
300 end if;
301 end loop;
302 return;
303 end LOCK_ROW;
304
305 procedure UPDATE_ROW (
306 X_INCIDENT_SEVERITY_ID in NUMBER,
307 X_DISPLAY_COLOR in VARCHAR2,
308 X_ATTRIBUTE6 in VARCHAR2,
309 X_INCIDENT_SUBTYPE in VARCHAR2,
310 X_IMPORTANCE_LEVEL in NUMBER,
311 X_START_DATE_ACTIVE in DATE,
312 X_END_DATE_ACTIVE in DATE,
313 X_ATTRIBUTE1 in VARCHAR2,
314 X_ATTRIBUTE2 in VARCHAR2,
315 X_ATTRIBUTE3 in VARCHAR2,
316 X_ATTRIBUTE4 in VARCHAR2,
317 X_ATTRIBUTE5 in VARCHAR2,
318 X_ATTRIBUTE7 in VARCHAR2,
319 X_ATTRIBUTE8 in VARCHAR2,
320 X_ATTRIBUTE9 in VARCHAR2,
321 X_ATTRIBUTE10 in VARCHAR2,
322 X_ATTRIBUTE11 in VARCHAR2,
323 X_ATTRIBUTE12 in VARCHAR2,
324 X_ATTRIBUTE13 in VARCHAR2,
325 X_ATTRIBUTE14 in VARCHAR2,
326 X_ATTRIBUTE15 in VARCHAR2,
327 X_CONTEXT in VARCHAR2,
328 X_OBJECT_VERSION_NUMBER in NUMBER,
329 X_RGB_COLOR in VARCHAR2,
330 X_NAME in VARCHAR2,
331 X_DESCRIPTION in VARCHAR2,
332 X_DEFECT_SEVERITY_ID in NUMBER,
333 X_LAST_UPDATE_DATE in DATE,
334 X_LAST_UPDATED_BY in NUMBER,
335 X_LAST_UPDATE_LOGIN in NUMBER,
336 --Added priority code for Misc ERs project (11.5.10) --anmukher --09/01/03
337 X_PRIORITY_CODE in VARCHAR2
338 ) is
339 begin
340 update CS_INCIDENT_SEVERITIES_B set
341 DISPLAY_COLOR = X_DISPLAY_COLOR,
342 ATTRIBUTE6 = X_ATTRIBUTE6,
343 INCIDENT_SUBTYPE = X_INCIDENT_SUBTYPE,
344 IMPORTANCE_LEVEL = X_IMPORTANCE_LEVEL,
345 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
346 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
347 ATTRIBUTE1 = X_ATTRIBUTE1,
348 ATTRIBUTE2 = X_ATTRIBUTE2,
349 ATTRIBUTE3 = X_ATTRIBUTE3,
350 ATTRIBUTE4 = X_ATTRIBUTE4,
351 ATTRIBUTE5 = X_ATTRIBUTE5,
355 ATTRIBUTE10 = X_ATTRIBUTE10,
352 ATTRIBUTE7 = X_ATTRIBUTE7,
353 ATTRIBUTE8 = X_ATTRIBUTE8,
354 ATTRIBUTE9 = X_ATTRIBUTE9,
356 ATTRIBUTE11 = X_ATTRIBUTE11,
357 ATTRIBUTE12 = X_ATTRIBUTE12,
358 ATTRIBUTE13 = X_ATTRIBUTE13,
359 ATTRIBUTE14 = X_ATTRIBUTE14,
360 ATTRIBUTE15 = X_ATTRIBUTE15,
361 CONTEXT = X_CONTEXT,
362 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
363 RGB_COLOR = X_RGB_COLOR,
364 DEFECT_SEVERITY_ID = X_DEFECT_SEVERITY_ID,
365 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
366 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
367 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
368 -- Added priority code --anmukher --09/01/03
369 PRIORITY_CODE = X_PRIORITY_CODE
370 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID;
371
372 if (sql%notfound) then
373 raise no_data_found;
374 end if;
375
376 update CS_INCIDENT_SEVERITIES_TL set
377 NAME = X_NAME,
378 DESCRIPTION = X_DESCRIPTION,
379 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
380 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
381 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
382 SOURCE_LANG = userenv('LANG')
383 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID
384 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
385
386 if (sql%notfound) then
387 raise no_data_found;
388 end if;
389 end UPDATE_ROW;
390
391 procedure DELETE_ROW (
392 X_INCIDENT_SEVERITY_ID in NUMBER
393 ) is
394 begin
395 delete from CS_INCIDENT_SEVERITIES_TL
396 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID;
397
398 if (sql%notfound) then
399 raise no_data_found;
400 end if;
401
402 delete from CS_INCIDENT_SEVERITIES_B
403 where INCIDENT_SEVERITY_ID = X_INCIDENT_SEVERITY_ID;
404
405 if (sql%notfound) then
406 raise no_data_found;
407 end if;
408 end DELETE_ROW;
409
410 procedure ADD_LANGUAGE
411 is
412 begin
413 delete from CS_INCIDENT_SEVERITIES_TL T
414 where not exists
415 (select NULL
416 from CS_INCIDENT_SEVERITIES_B B
417 where B.INCIDENT_SEVERITY_ID = T.INCIDENT_SEVERITY_ID
418 );
419
420 update CS_INCIDENT_SEVERITIES_TL T set (
421 NAME,
422 DESCRIPTION
423 ) = (select
424 B.NAME,
425 B.DESCRIPTION
426 from CS_INCIDENT_SEVERITIES_TL B
427 where B.INCIDENT_SEVERITY_ID = T.INCIDENT_SEVERITY_ID
428 and B.LANGUAGE = T.SOURCE_LANG)
429 where (
430 T.INCIDENT_SEVERITY_ID,
431 T.LANGUAGE
432 ) in (select
433 SUBT.INCIDENT_SEVERITY_ID,
434 SUBT.LANGUAGE
435 from CS_INCIDENT_SEVERITIES_TL SUBB, CS_INCIDENT_SEVERITIES_TL SUBT
436 where SUBB.INCIDENT_SEVERITY_ID = SUBT.INCIDENT_SEVERITY_ID
437 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
438 and (SUBB.NAME <> SUBT.NAME
439 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
440 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
441 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
442 ));
443
444 insert into CS_INCIDENT_SEVERITIES_TL (
445 INCIDENT_SEVERITY_ID,
446 CREATED_BY,
447 CREATION_DATE,
448 LAST_UPDATED_BY,
449 LAST_UPDATE_DATE,
450 LAST_UPDATE_LOGIN,
451 NAME,
452 DESCRIPTION,
453 LANGUAGE,
454 SOURCE_LANG
455 ) select
456 B.INCIDENT_SEVERITY_ID,
457 B.CREATED_BY,
458 B.CREATION_DATE,
459 B.LAST_UPDATED_BY,
460 B.LAST_UPDATE_DATE,
461 B.LAST_UPDATE_LOGIN,
462 B.NAME,
463 B.DESCRIPTION,
464 L.LANGUAGE_CODE,
465 B.SOURCE_LANG
466 from CS_INCIDENT_SEVERITIES_TL B, FND_LANGUAGES L
467 where L.INSTALLED_FLAG in ('I', 'B')
468 and B.LANGUAGE = userenv('LANG')
469 and not exists
470 (select NULL
471 from CS_INCIDENT_SEVERITIES_TL T
472 where T.INCIDENT_SEVERITY_ID = B.INCIDENT_SEVERITY_ID
473 and T.LANGUAGE = L.LANGUAGE_CODE);
474 end ADD_LANGUAGE;
475
476 end CS_INCIDENT_SEVERITIES_PKG;
[Home] [Help]