eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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,
410   if (sql%notfound) then
407     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
408   where SR_TYPE_ATTR_MAP_ID = X_SR_TYPE_ATTR_MAP_ID;
409 
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
424     raise no_data_found;
425   end if;
426 end UPDATE_ROW;
427 
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,
518   X_START_DATE_ACTIVE in DATE,
519   X_END_DATE_ACTIVE in DATE,
520   X_SR_ATTR_DEFAULT_VALUE in VARCHAR2,
521   X_TEMPLATE_ID  in NUMBER,
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 ,
576 	X_REQD_FOR_CLOSE_FLAG => X_REQD_FOR_CLOSE_FLAG  ,
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   ,
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;