[Home] [Help]
PACKAGE BODY: APPS.CUG_SR_TYPE_ATTR_MAPS_PKG
Source
1 package body CUG_SR_TYPE_ATTR_MAPS_PKG as
2 /* $Header: CUGSRATB.pls 115.10 2004/03/29 21:40:44 aneemuch noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SR_TYPE_ATTR_MAP_ID in NUMBER,
6 X_SR_TYPE_ATTR_SEQ_NUM in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_INCIDENT_TYPE_ID in NUMBER,
9 X_SR_ATTRIBUTE_CODE in VARCHAR2,
10 X_SR_ATTR_MANDATORY_FLAG in VARCHAR2,
11 X_SR_ATTR_DISPLAYED_FLAG in VARCHAR2,
12 X_SR_ATTR_DUP_CHECK_FLAG in VARCHAR2,
13 X_SR_ATTRIBUTE_LIST_NAME in VARCHAR2,
14 X_ATTRIBUTE1 in VARCHAR2,
15 X_ATTRIBUTE2 in VARCHAR2,
16 X_ATTRIBUTE3 in VARCHAR2,
17 X_ATTRIBUTE4 in VARCHAR2,
18 X_ATTRIBUTE5 in VARCHAR2,
19 X_ATTRIBUTE6 in VARCHAR2,
20 X_ATTRIBUTE7 in VARCHAR2,
21 X_ATTRIBUTE8 in VARCHAR2,
22 X_ATTRIBUTE9 in VARCHAR2,
23 X_ATTRIBUTE10 in VARCHAR2,
24 X_ATTRIBUTE11 in VARCHAR2,
25 X_ATTRIBUTE12 in VARCHAR2,
26 X_ATTRIBUTE13 in VARCHAR2,
27 X_ATTRIBUTE14 in VARCHAR2,
28 X_ATTRIBUTE15 in VARCHAR2,
29 X_ATTRIBUTE_CATEGORY in VARCHAR2,
30 X_START_DATE_ACTIVE in DATE,
31 X_END_DATE_ACTIVE in DATE,
32 X_SECURITY_GROUP_ID in NUMBER,
33 X_TEMPLATE_ID in NUMBER,
34 X_REQD_FOR_CLOSE_FLAG in VARCHAR2,
35 X_SHOW_ON_UPDATE_FLAG in VARCHAR2,
36 X_UPDATE_ALLOWED_FLAG in VARCHAR2,
37 X_SR_ATTR_DEFAULT_VALUE in VARCHAR2,
38 X_CREATION_DATE in DATE,
39 X_CREATED_BY in NUMBER,
40 X_LAST_UPDATE_DATE in DATE,
41 X_LAST_UPDATED_BY in NUMBER,
42 X_LAST_UPDATE_LOGIN in NUMBER
43 ) is
44 cursor C is select ROWID from CUG_SR_TYPE_ATTR_MAPS_B
45 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID
46 ;
47 begin
48 insert into CUG_SR_TYPE_ATTR_MAPS_B (
49 SR_TYPE_ATTR_SEQ_NUM,
50 SR_TYPE_ATTR_MAP_ID,
51 OBJECT_VERSION_NUMBER,
52 INCIDENT_TYPE_ID,
53 SR_ATTRIBUTE_CODE,
54 SR_ATTR_MANDATORY_FLAG,
55 SR_ATTR_DISPLAYED_FLAG,
56 SR_ATTR_DUP_CHECK_FLAG,
57 SR_ATTRIBUTE_LIST_NAME,
58 ATTRIBUTE1,
59 ATTRIBUTE2,
60 ATTRIBUTE3,
61 ATTRIBUTE4,
62 ATTRIBUTE5,
63 ATTRIBUTE6,
64 ATTRIBUTE7,
65 ATTRIBUTE8,
66 ATTRIBUTE9,
67 ATTRIBUTE10,
68 ATTRIBUTE11,
69 ATTRIBUTE12,
70 ATTRIBUTE13,
71 ATTRIBUTE14,
72 ATTRIBUTE15,
73 ATTRIBUTE_CATEGORY,
74 START_DATE_ACTIVE,
75 END_DATE_ACTIVE,
76 SECURITY_GROUP_ID,
77 TEMPLATE_ID,
78 REQD_FOR_CLOSE_FLAG,
79 SHOW_ON_UPDATE_FLAG,
80 UPDATE_ALLOWED_FLAG,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 LAST_UPDATE_LOGIN
86 ) values (
87 X_SR_TYPE_ATTR_SEQ_NUM,
88 X_SR_TYPE_ATTR_MAP_ID,
89 X_OBJECT_VERSION_NUMBER,
90 X_INCIDENT_TYPE_ID,
91 X_SR_ATTRIBUTE_CODE,
92 X_SR_ATTR_MANDATORY_FLAG,
93 X_SR_ATTR_DISPLAYED_FLAG,
94 X_SR_ATTR_DUP_CHECK_FLAG,
95 X_SR_ATTRIBUTE_LIST_NAME,
96 X_ATTRIBUTE1,
97 X_ATTRIBUTE2,
98 X_ATTRIBUTE3,
99 X_ATTRIBUTE4,
100 X_ATTRIBUTE5,
101 X_ATTRIBUTE6,
102 X_ATTRIBUTE7,
103 X_ATTRIBUTE8,
104 X_ATTRIBUTE9,
105 X_ATTRIBUTE10,
106 X_ATTRIBUTE11,
107 X_ATTRIBUTE12,
108 X_ATTRIBUTE13,
109 X_ATTRIBUTE14,
110 X_ATTRIBUTE15,
111 X_ATTRIBUTE_CATEGORY,
112 X_START_DATE_ACTIVE,
113 X_END_DATE_ACTIVE,
114 X_SECURITY_GROUP_ID,
115 X_TEMPLATE_ID,
116 X_REQD_FOR_CLOSE_FLAG,
117 X_SHOW_ON_UPDATE_FLAG,
118 X_UPDATE_ALLOWED_FLAG,
119 X_CREATION_DATE,
120 X_CREATED_BY,
121 X_LAST_UPDATE_DATE,
122 X_LAST_UPDATED_BY,
123 X_LAST_UPDATE_LOGIN
124 );
125
126 insert into CUG_SR_TYPE_ATTR_MAPS_TL (
127 SR_TYPE_ATTR_MAP_ID,
128 CREATED_BY,
129 CREATION_DATE,
130 LAST_UPDATE_LOGIN,
131 LAST_UPDATE_DATE,
132 SR_ATTR_DEFAULT_VALUE,
133 LAST_UPDATED_BY,
134 SECURITY_GROUP_ID,
135 LANGUAGE,
136 SOURCE_LANG
137 ) select
138 X_SR_TYPE_ATTR_MAP_ID,
139 X_CREATED_BY,
140 X_CREATION_DATE,
141 X_LAST_UPDATE_LOGIN,
142 X_LAST_UPDATE_DATE,
143 X_SR_ATTR_DEFAULT_VALUE,
144 X_LAST_UPDATED_BY,
145 X_SECURITY_GROUP_ID,
146 L.LANGUAGE_CODE,
147 userenv('LANG')
148 from FND_LANGUAGES L
149 where L.INSTALLED_FLAG in ('I', 'B')
150 and not exists
151 (select NULL
152 from CUG_SR_TYPE_ATTR_MAPS_TL T
153 where T.SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID
154 and T.LANGUAGE = L.LANGUAGE_CODE);
155
156 open c;
157 fetch c into X_ROWID;
158 if (c%notfound) then
159 close c;
160 raise no_data_found;
161 end if;
162 close c;
163
164 end INSERT_ROW;
165
166 procedure LOCK_ROW (
167 X_SR_TYPE_ATTR_MAP_ID in NUMBER,
168 X_SR_TYPE_ATTR_SEQ_NUM in NUMBER,
169 X_OBJECT_VERSION_NUMBER in NUMBER,
170 X_INCIDENT_TYPE_ID in NUMBER,
171 X_SR_ATTRIBUTE_CODE in VARCHAR2,
172 X_SR_ATTR_MANDATORY_FLAG in VARCHAR2,
173 X_SR_ATTR_DISPLAYED_FLAG in VARCHAR2,
174 X_SR_ATTR_DUP_CHECK_FLAG in VARCHAR2,
175 X_SR_ATTRIBUTE_LIST_NAME in VARCHAR2,
176 X_ATTRIBUTE1 in VARCHAR2,
177 X_ATTRIBUTE2 in VARCHAR2,
178 X_ATTRIBUTE3 in VARCHAR2,
179 X_ATTRIBUTE4 in VARCHAR2,
180 X_ATTRIBUTE5 in VARCHAR2,
181 X_ATTRIBUTE6 in VARCHAR2,
182 X_ATTRIBUTE7 in VARCHAR2,
183 X_ATTRIBUTE8 in VARCHAR2,
184 X_ATTRIBUTE9 in VARCHAR2,
185 X_ATTRIBUTE10 in VARCHAR2,
186 X_ATTRIBUTE11 in VARCHAR2,
187 X_ATTRIBUTE12 in VARCHAR2,
188 X_ATTRIBUTE13 in VARCHAR2,
189 X_ATTRIBUTE14 in VARCHAR2,
190 X_ATTRIBUTE15 in VARCHAR2,
191 X_ATTRIBUTE_CATEGORY in VARCHAR2,
192 X_START_DATE_ACTIVE in DATE,
193 X_END_DATE_ACTIVE in DATE,
194 X_SECURITY_GROUP_ID in NUMBER,
195 X_TEMPLATE_ID in NUMBER,
196 X_REQD_FOR_CLOSE_FLAG in VARCHAR2,
197 X_SHOW_ON_UPDATE_FLAG in VARCHAR2,
198 X_UPDATE_ALLOWED_FLAG in VARCHAR2,
199 X_SR_ATTR_DEFAULT_VALUE in VARCHAR2
200 ) is
201 cursor c is select
202 SR_TYPE_ATTR_SEQ_NUM,
203 OBJECT_VERSION_NUMBER,
204 INCIDENT_TYPE_ID,
205 SR_ATTRIBUTE_CODE,
206 SR_ATTR_MANDATORY_FLAG,
207 SR_ATTR_DISPLAYED_FLAG,
208 SR_ATTR_DUP_CHECK_FLAG,
209 SR_ATTRIBUTE_LIST_NAME,
210 ATTRIBUTE1,
211 ATTRIBUTE2,
212 ATTRIBUTE3,
213 ATTRIBUTE4,
214 ATTRIBUTE5,
215 ATTRIBUTE6,
216 ATTRIBUTE7,
217 ATTRIBUTE8,
218 ATTRIBUTE9,
219 ATTRIBUTE10,
220 ATTRIBUTE11,
221 ATTRIBUTE12,
222 ATTRIBUTE13,
223 ATTRIBUTE14,
224 ATTRIBUTE15,
225 ATTRIBUTE_CATEGORY,
226 START_DATE_ACTIVE,
227 END_DATE_ACTIVE,
228 SECURITY_GROUP_ID,
229 TEMPLATE_ID,
230 REQD_FOR_CLOSE_FLAG,
231 SHOW_ON_UPDATE_FLAG,
232 UPDATE_ALLOWED_FLAG
233 from CUG_SR_TYPE_ATTR_MAPS_B
234 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID
235 for update of SR_TYPE_ATTR_MAP_ID nowait;
236 recinfo c%rowtype;
237
238 cursor c1 is select
239 SR_ATTR_DEFAULT_VALUE,
240 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
241 from CUG_SR_TYPE_ATTR_MAPS_TL
242 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID
243 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
244 for update of SR_TYPE_ATTR_MAP_ID nowait;
245 begin
246 open c;
247 fetch c into recinfo;
248 if (c%notfound) then
249 close c;
250 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
251 app_exception.raise_exception;
252 end if;
253 close c;
254 if ( ((recinfo.SR_TYPE_ATTR_SEQ_NUM = X_SR_TYPE_ATTR_SEQ_NUM)
255 OR ((recinfo.SR_TYPE_ATTR_SEQ_NUM is null) AND (X_SR_TYPE_ATTR_SEQ_NUM is null)))
256 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
257 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
258 AND ((recinfo.INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID)
259 OR ((recinfo.INCIDENT_TYPE_ID is null) AND (X_INCIDENT_TYPE_ID is null)))
260 AND (recinfo.SR_ATTRIBUTE_CODE = X_SR_ATTRIBUTE_CODE)
261 AND (recinfo.SR_ATTR_MANDATORY_FLAG = X_SR_ATTR_MANDATORY_FLAG)
262 AND (recinfo.SR_ATTR_DISPLAYED_FLAG = X_SR_ATTR_DISPLAYED_FLAG)
263 AND (recinfo.SR_ATTR_DUP_CHECK_FLAG = X_SR_ATTR_DUP_CHECK_FLAG)
264 AND ((recinfo.SR_ATTRIBUTE_LIST_NAME = X_SR_ATTRIBUTE_LIST_NAME)
265 OR ((recinfo.SR_ATTRIBUTE_LIST_NAME is null) AND (X_SR_ATTRIBUTE_LIST_NAME is null)))
266 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
267 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
268 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
269 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
270 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
271 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
272 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
273 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
274 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
275 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
276 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
277 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
278 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
279 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
280 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
281 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
282 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
283 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
284 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
285 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
286 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
287 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
288 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
289 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
290 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
291 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
292 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
293 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
294 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
295 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
296 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
297 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
298 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
299 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
300 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
301 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
302 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
303 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
304 AND ((recinfo.TEMPLATE_ID = X_TEMPLATE_ID)
305 OR ((recinfo.TEMPLATE_ID is null) AND (X_TEMPLATE_ID is null)))
306 AND ((recinfo.REQD_FOR_CLOSE_FLAG = X_REQD_FOR_CLOSE_FLAG)
307 OR ((recinfo.REQD_FOR_CLOSE_FLAG is null) AND (X_REQD_FOR_CLOSE_FLAG is null)))
308 AND ((recinfo.SHOW_ON_UPDATE_FLAG = X_SHOW_ON_UPDATE_FLAG)
309 OR ((recinfo.SHOW_ON_UPDATE_FLAG is null) AND (X_SHOW_ON_UPDATE_FLAG is null)))
310 AND ((recinfo.UPDATE_ALLOWED_FLAG = X_UPDATE_ALLOWED_FLAG)
311 OR ((recinfo.UPDATE_ALLOWED_FLAG is null) AND (X_UPDATE_ALLOWED_FLAG is null)))
312 ) then
313 null;
314 else
315 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
316 app_exception.raise_exception;
317 end if;
318
319 for tlinfo in c1 loop
320 if (tlinfo.BASELANG = 'Y') then
321 if ( ((tlinfo.SR_ATTR_DEFAULT_VALUE = X_SR_ATTR_DEFAULT_VALUE)
322 OR ((tlinfo.SR_ATTR_DEFAULT_VALUE is null) AND (X_SR_ATTR_DEFAULT_VALUE is null)))
323 ) then
324 null;
325 else
326 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
327 app_exception.raise_exception;
328 end if;
329 end if;
330 end loop;
331 return;
332 end LOCK_ROW;
333
334 procedure UPDATE_ROW (
335 X_SR_TYPE_ATTR_MAP_ID in NUMBER,
336 X_SR_TYPE_ATTR_SEQ_NUM in NUMBER,
337 X_OBJECT_VERSION_NUMBER in NUMBER,
338 X_INCIDENT_TYPE_ID in NUMBER,
339 X_SR_ATTRIBUTE_CODE in VARCHAR2,
340 X_SR_ATTR_MANDATORY_FLAG in VARCHAR2,
341 X_SR_ATTR_DISPLAYED_FLAG in VARCHAR2,
342 X_SR_ATTR_DUP_CHECK_FLAG in VARCHAR2,
343 X_SR_ATTRIBUTE_LIST_NAME in VARCHAR2,
344 X_ATTRIBUTE1 in VARCHAR2,
345 X_ATTRIBUTE2 in VARCHAR2,
346 X_ATTRIBUTE3 in VARCHAR2,
347 X_ATTRIBUTE4 in VARCHAR2,
348 X_ATTRIBUTE5 in VARCHAR2,
349 X_ATTRIBUTE6 in VARCHAR2,
350 X_ATTRIBUTE7 in VARCHAR2,
351 X_ATTRIBUTE8 in VARCHAR2,
352 X_ATTRIBUTE9 in VARCHAR2,
353 X_ATTRIBUTE10 in VARCHAR2,
354 X_ATTRIBUTE11 in VARCHAR2,
355 X_ATTRIBUTE12 in VARCHAR2,
356 X_ATTRIBUTE13 in VARCHAR2,
357 X_ATTRIBUTE14 in VARCHAR2,
358 X_ATTRIBUTE15 in VARCHAR2,
359 X_ATTRIBUTE_CATEGORY in VARCHAR2,
360 X_START_DATE_ACTIVE in DATE,
361 X_END_DATE_ACTIVE in DATE,
362 X_SECURITY_GROUP_ID in NUMBER,
363 X_TEMPLATE_ID in NUMBER,
364 X_REQD_FOR_CLOSE_FLAG in VARCHAR2,
365 X_SHOW_ON_UPDATE_FLAG in VARCHAR2,
366 X_UPDATE_ALLOWED_FLAG in VARCHAR2,
367 X_SR_ATTR_DEFAULT_VALUE in VARCHAR2,
368 X_LAST_UPDATE_DATE in DATE,
369 X_LAST_UPDATED_BY in NUMBER,
370 X_LAST_UPDATE_LOGIN in NUMBER
371 ) is
372 begin
373 update CUG_SR_TYPE_ATTR_MAPS_B set
374 SR_TYPE_ATTR_SEQ_NUM = X_SR_TYPE_ATTR_SEQ_NUM,
375 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
376 INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID,
377 SR_ATTRIBUTE_CODE = X_SR_ATTRIBUTE_CODE,
378 SR_ATTR_MANDATORY_FLAG = X_SR_ATTR_MANDATORY_FLAG,
379 SR_ATTR_DISPLAYED_FLAG = X_SR_ATTR_DISPLAYED_FLAG,
380 SR_ATTR_DUP_CHECK_FLAG = X_SR_ATTR_DUP_CHECK_FLAG,
381 SR_ATTRIBUTE_LIST_NAME = X_SR_ATTRIBUTE_LIST_NAME,
382 ATTRIBUTE1 = X_ATTRIBUTE1,
383 ATTRIBUTE2 = X_ATTRIBUTE2,
384 ATTRIBUTE3 = X_ATTRIBUTE3,
385 ATTRIBUTE4 = X_ATTRIBUTE4,
386 ATTRIBUTE5 = X_ATTRIBUTE5,
387 ATTRIBUTE6 = X_ATTRIBUTE6,
388 ATTRIBUTE7 = X_ATTRIBUTE7,
389 ATTRIBUTE8 = X_ATTRIBUTE8,
390 ATTRIBUTE9 = X_ATTRIBUTE9,
391 ATTRIBUTE10 = X_ATTRIBUTE10,
392 ATTRIBUTE11 = X_ATTRIBUTE11,
393 ATTRIBUTE12 = X_ATTRIBUTE12,
394 ATTRIBUTE13 = X_ATTRIBUTE13,
395 ATTRIBUTE14 = X_ATTRIBUTE14,
396 ATTRIBUTE15 = X_ATTRIBUTE15,
397 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
398 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
399 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
400 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
401 TEMPLATE_ID = X_TEMPLATE_ID,
402 REQD_FOR_CLOSE_FLAG = X_REQD_FOR_CLOSE_FLAG,
403 SHOW_ON_UPDATE_FLAG = X_SHOW_ON_UPDATE_FLAG,
404 UPDATE_ALLOWED_FLAG = X_UPDATE_ALLOWED_FLAG,
405 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
406 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
407 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
408 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID;
409
410 if (sql%notfound) then
411 raise no_data_found;
412 end if;
413
414 update CUG_SR_TYPE_ATTR_MAPS_TL set
415 SR_ATTR_DEFAULT_VALUE = X_SR_ATTR_DEFAULT_VALUE,
416 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
417 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
418 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
419 SOURCE_LANG = userenv('LANG')
420 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID
421 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
422
423 if (sql%notfound) then
427
424 raise no_data_found;
425 end if;
426 end UPDATE_ROW;
428 procedure DELETE_ROW (
429 X_SR_TYPE_ATTR_MAP_ID in NUMBER
430 ) is
431 begin
432 delete from CUG_SR_TYPE_ATTR_MAPS_TL
433 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID;
434
435 if (sql%notfound) then
436 raise no_data_found;
437 end if;
438
439 delete from CUG_SR_TYPE_ATTR_MAPS_B
440 where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID;
441
442 if (sql%notfound) then
443 raise no_data_found;
444 end if;
445 end DELETE_ROW;
446
447 procedure ADD_LANGUAGE
448 is
449 begin
450 delete from CUG_SR_TYPE_ATTR_MAPS_TL T
451 where not exists
452 (select NULL
453 from CUG_SR_TYPE_ATTR_MAPS_B B
454 where B.SR_TYPE_ATTR_MAP_ID = T.SR_TYPE_ATTR_MAP_ID
455 );
456
457 update CUG_SR_TYPE_ATTR_MAPS_TL T set (
458 SR_ATTR_DEFAULT_VALUE
459 ) = (select
460 B.SR_ATTR_DEFAULT_VALUE
461 from CUG_SR_TYPE_ATTR_MAPS_TL B
462 where B.SR_TYPE_ATTR_MAP_ID = T.SR_TYPE_ATTR_MAP_ID
463 and B.LANGUAGE = T.SOURCE_LANG)
464 where (
465 T.SR_TYPE_ATTR_MAP_ID,
466 T.LANGUAGE
467 ) in (select
468 SUBT.SR_TYPE_ATTR_MAP_ID,
469 SUBT.LANGUAGE
470 from CUG_SR_TYPE_ATTR_MAPS_TL SUBB, CUG_SR_TYPE_ATTR_MAPS_TL SUBT
471 where SUBB.SR_TYPE_ATTR_MAP_ID = SUBT.SR_TYPE_ATTR_MAP_ID
472 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
473 and (SUBB.SR_ATTR_DEFAULT_VALUE <> SUBT.SR_ATTR_DEFAULT_VALUE
474 or (SUBB.SR_ATTR_DEFAULT_VALUE is null and SUBT.SR_ATTR_DEFAULT_VALUE is not null)
475 or (SUBB.SR_ATTR_DEFAULT_VALUE is not null and SUBT.SR_ATTR_DEFAULT_VALUE is null)
476 ));
477
478 insert into CUG_SR_TYPE_ATTR_MAPS_TL (
479 SR_TYPE_ATTR_MAP_ID,
480 CREATED_BY,
481 CREATION_DATE,
482 LAST_UPDATE_LOGIN,
483 LAST_UPDATE_DATE,
484 SR_ATTR_DEFAULT_VALUE,
485 LAST_UPDATED_BY,
486 SECURITY_GROUP_ID,
487 LANGUAGE,
488 SOURCE_LANG
489 ) select
490 B.SR_TYPE_ATTR_MAP_ID,
491 B.CREATED_BY,
492 B.CREATION_DATE,
493 B.LAST_UPDATE_LOGIN,
494 B.LAST_UPDATE_DATE,
495 B.SR_ATTR_DEFAULT_VALUE,
496 B.LAST_UPDATED_BY,
497 B.SECURITY_GROUP_ID,
498 L.LANGUAGE_CODE,
499 B.SOURCE_LANG
500 from CUG_SR_TYPE_ATTR_MAPS_TL B, FND_LANGUAGES L
501 where L.INSTALLED_FLAG in ('I', 'B')
502 and B.LANGUAGE = userenv('LANG')
503 and not exists
504 (select NULL
505 from CUG_SR_TYPE_ATTR_MAPS_TL T
506 where T.SR_TYPE_ATTR_MAP_ID = B.SR_TYPE_ATTR_MAP_ID
507 and T.LANGUAGE = L.LANGUAGE_CODE);
508 end ADD_LANGUAGE;
509
510 procedure LOAD_ROW (
511 X_SR_TYPE_ATTR_MAP_ID in NUMBER,
512 X_INCIDENT_TYPE_ID in NUMBER,
513 X_SR_ATTRIBUTE_CODE in VARCHAR2,
514 X_SR_ATTR_MANDATORY_FLAG in VARCHAR2,
515 X_SR_ATTR_DISPLAYED_FLAG in VARCHAR2,
516 X_SR_ATTR_DUP_CHECK_FLAG in VARCHAR2,
517 X_SR_ATTRIBUTE_LIST_NAME in VARCHAR2,
521 X_TEMPLATE_ID in NUMBER,
518 X_START_DATE_ACTIVE in DATE,
519 X_END_DATE_ACTIVE in DATE,
520 X_SR_ATTR_DEFAULT_VALUE in VARCHAR2,
522 X_REQD_FOR_CLOSE_FLAG IN VARCHAR2 ,
523 X_SHOW_ON_UPDATE_FLAG IN VARCHAR2 ,
524 X_UPDATE_ALLOWED_FLAG IN VARCHAR2 ,
525 X_SR_TYPE_ATTR_SEQ_NUM in NUMBER,
526 X_SECURITY_GROUP_ID in NUMBER,
527 x_CREATION_DATE IN VARCHAR2,
528 x_CREATED_BY IN NUMBER,
529 x_LAST_UPDATE_DATE IN VARCHAR2,
530 x_LAST_UPDATED_BY IN NUMBER,
531 x_LAST_UPDATE_LOGIN IN NUMBER,
532 X_OWNER IN VARCHAR2 ) IS
533
534 BEGIN
535
536 DECLARE
537 user_id NUMBER := 0;
538 row_id VARCHAR2(64);
539
540 BEGIN
541
542 IF (X_OWNER = 'SEED') THEN
543 user_id := 1;
544 END IF;
545
546 CUG_SR_TYPE_ATTR_MAPS_PKG.UPDATE_ROW (
547 X_SR_TYPE_ATTR_MAP_ID => X_SR_TYPE_ATTR_MAP_ID ,
548 X_OBJECT_VERSION_NUMBER => null,
549 X_INCIDENT_TYPE_ID => X_INCIDENT_TYPE_ID ,
550 X_SR_TYPE_ATTR_SEQ_NUM => X_SR_TYPE_ATTR_SEQ_NUM,
551 X_SR_ATTRIBUTE_CODE => X_SR_ATTRIBUTE_CODE ,
552 X_SR_ATTR_MANDATORY_FLAG => X_SR_ATTR_MANDATORY_FLAG,
553 X_SR_ATTR_DISPLAYED_FLAG => X_SR_ATTR_DISPLAYED_FLAG ,
554 X_SR_ATTR_DUP_CHECK_FLAG => X_SR_ATTR_DUP_CHECK_FLAG,
555 X_SR_ATTRIBUTE_LIST_NAME => X_SR_ATTRIBUTE_LIST_NAME ,
556 X_ATTRIBUTE1 => null,
557 X_ATTRIBUTE2 => null ,
558 X_ATTRIBUTE3=> null ,
559 X_ATTRIBUTE4 => null ,
560 X_ATTRIBUTE5 => null ,
561 X_ATTRIBUTE6 => null ,
562 X_ATTRIBUTE7 => null ,
563 X_ATTRIBUTE8 => null ,
564 X_ATTRIBUTE9 => null ,
565 X_ATTRIBUTE10 => null ,
566 X_ATTRIBUTE11 => null ,
567 X_ATTRIBUTE12 => null ,
568 X_ATTRIBUTE13 => null ,
569 X_ATTRIBUTE14 => null ,
570 X_ATTRIBUTE15 => null ,
571 X_ATTRIBUTE_CATEGORY => null ,
572 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE ,
573 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE ,
574 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
575 X_TEMPLATE_ID => X_TEMPLATE_ID ,
576 X_REQD_FOR_CLOSE_FLAG => X_REQD_FOR_CLOSE_FLAG ,
577 X_SHOW_ON_UPDATE_FLAG => X_SHOW_ON_UPDATE_FLAG ,
578 X_UPDATE_ALLOWED_FLAG => X_UPDATE_ALLOWED_FLAG ,
579 X_SR_ATTR_DEFAULT_VALUE => X_SR_ATTR_DEFAULT_VALUE ,
580 X_LAST_UPDATE_DATE => nvl(to_date(x_last_update_date, 'DD-MM-YYYY'), sysdate),
581 X_LAST_UPDATED_BY => user_id ,
582 X_LAST_UPDATE_LOGIN => 0);
583
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 CUG_SR_TYPE_ATTR_MAPS_PKG.INSERT_ROW (
587 X_ROWID => row_id ,
588 X_SR_TYPE_ATTR_MAP_ID => X_SR_TYPE_ATTR_MAP_ID ,
589 X_SR_TYPE_ATTR_SEQ_NUM => X_SR_TYPE_ATTR_SEQ_NUM,
590 X_OBJECT_VERSION_NUMBER => null,
591 X_INCIDENT_TYPE_ID => X_INCIDENT_TYPE_ID ,
592 X_SR_ATTRIBUTE_CODE => X_SR_ATTRIBUTE_CODE ,
593 X_SR_ATTR_MANDATORY_FLAG => X_SR_ATTR_MANDATORY_FLAG,
594 X_SR_ATTR_DISPLAYED_FLAG => X_SR_ATTR_DISPLAYED_FLAG ,
595 X_SR_ATTR_DUP_CHECK_FLAG => X_SR_ATTR_DUP_CHECK_FLAG,
596 X_SR_ATTRIBUTE_LIST_NAME => X_SR_ATTRIBUTE_LIST_NAME ,
597 X_ATTRIBUTE1 => null,
598 X_ATTRIBUTE2 => null ,
599 X_ATTRIBUTE3=> null ,
600 X_ATTRIBUTE4 => null ,
601 X_ATTRIBUTE5 => null ,
602 X_ATTRIBUTE6 => null ,
603 X_ATTRIBUTE7 => null ,
604 X_ATTRIBUTE8 => null ,
605 X_ATTRIBUTE9 => null ,
606 X_ATTRIBUTE10 => null ,
607 X_ATTRIBUTE11 => null ,
608 X_ATTRIBUTE12 => null ,
609 X_ATTRIBUTE13 => null ,
610 X_ATTRIBUTE14 => null ,
611 X_ATTRIBUTE15 => null ,
612 X_ATTRIBUTE_CATEGORY => null ,
613 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE ,
614 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE ,
615 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
616 X_SR_ATTR_DEFAULT_VALUE => X_SR_ATTR_DEFAULT_VALUE ,
617 X_TEMPLATE_ID => X_TEMPLATE_ID ,
618 X_REQD_FOR_CLOSE_FLAG => X_REQD_FOR_CLOSE_FLAG ,
619 X_SHOW_ON_UPDATE_FLAG => X_SHOW_ON_UPDATE_FLAG ,
620 X_UPDATE_ALLOWED_FLAG => X_UPDATE_ALLOWED_FLAG ,
621 X_CREATION_DATE => nvl(to_date(x_creation_date, 'DD-MM-YYYY'), sysdate),
622 X_CREATED_BY => user_id,
623 X_LAST_UPDATE_DATE => nvl(to_date(x_last_update_date, 'DD-MM-YYYY'), sysdate),
624 X_LAST_UPDATED_BY => user_id,
625 X_LAST_UPDATE_LOGIN => 0);
626 END;
627
628 END LOAD_ROW;
629
630 PROCEDURE TRANSLATE_ROW (
631 X_SR_TYPE_ATTR_MAP_ID in NUMBER,
632 X_SR_ATTR_DEFAULT_VALUE in VARCHAR2,
633 X_OWNER in VARCHAR2) IS
634 BEGIN
635
636 UPDATE CUG_SR_TYPE_ATTR_MAPS_TL
637 SET SR_ATTR_DEFAULT_VALUE = X_SR_ATTR_DEFAULT_VALUE ,
638 source_lang = userenv('LANG'),
639 last_update_date = sysdate,
640 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
641 last_update_login = 0
642 where sr_type_attr_map_id = X_SR_TYPE_ATTR_MAP_ID
643 and userenv('LANG') in (language, source_lang);
644
645 END TRANSLATE_ROW ;
646
647 end CUG_SR_TYPE_ATTR_MAPS_PKG;