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