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