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