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.IEU_UWQ_RES_CATS_PKG

Source


1 package body IEU_UWQ_RES_CATS_PKG as
2 /* $Header: IEURCATB.pls 120.2 2005/06/20 08:12:00 appldev ship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID in out nocopy VARCHAR2,
6   X_RES_CAT_ID in NUMBER,
7   X_OBJECT_VERSION_NUMBER in NUMBER,
8   X_APPLICATION_ID in NUMBER,
9   X_WHERE_CLAUSE in VARCHAR2,
10   X_NAME in VARCHAR2,
11   X_DESCRIPTION in VARCHAR2,
12   X_CREATION_DATE in DATE,
13   X_CREATED_BY in NUMBER,
14   X_LAST_UPDATE_DATE in DATE,
15   X_LAST_UPDATED_BY in NUMBER,
16   X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18   cursor C is select ROWID from IEU_UWQ_RES_CATS_B
19     where RES_CAT_ID = X_RES_CAT_ID
20     ;
21 begin
22 
23   insert into IEU_UWQ_RES_CATS_B (
24     RES_CAT_ID,
25     OBJECT_VERSION_NUMBER,
26     APPLICATION_ID,
27     WHERE_CLAUSE,
28     CREATION_DATE,
29     CREATED_BY,
30     LAST_UPDATE_DATE,
31     LAST_UPDATED_BY,
32     LAST_UPDATE_LOGIN
33   ) values (
34     X_RES_CAT_ID,
35     X_OBJECT_VERSION_NUMBER,
36     X_APPLICATION_ID,
37     X_WHERE_CLAUSE,
38     X_CREATION_DATE,
39     X_CREATED_BY,
40     X_LAST_UPDATE_DATE,
41     X_LAST_UPDATED_BY,
42     X_LAST_UPDATE_LOGIN
43   );
44 
45   insert into IEU_UWQ_RES_CATS_TL (
46     RES_CAT_ID,
47     OBJECT_VERSION_NUMBER,
48     CREATED_BY,
49     CREATION_DATE,
50     LAST_UPDATED_BY,
51     LAST_UPDATE_DATE,
52     LAST_UPDATE_LOGIN,
53     NAME,
54     DESCRIPTION,
55     LANGUAGE,
56     SOURCE_LANG
57   ) select
58     X_RES_CAT_ID,
59     X_OBJECT_VERSION_NUMBER,
60     X_CREATED_BY,
61     X_CREATION_DATE,
62     X_LAST_UPDATED_BY,
63     X_LAST_UPDATE_DATE,
64     X_LAST_UPDATE_LOGIN,
65     X_NAME,
66     X_DESCRIPTION,
67     L.LANGUAGE_CODE,
68     userenv('LANG')
69   from FND_LANGUAGES L
70   where L.INSTALLED_FLAG in ('I', 'B')
71   and not exists
72     (select NULL
73     from IEU_UWQ_RES_CATS_TL T
74     where T.RES_CAT_ID = X_RES_CAT_ID
75     and T.LANGUAGE = L.LANGUAGE_CODE);
76 
77   open c;
78   fetch c into X_ROWID;
79   if (c%notfound) then
80     close c;
81     raise no_data_found;
82   end if;
83   close c;
84 
85 end INSERT_ROW;
86 
87 procedure LOCK_ROW (
88   X_RES_CAT_ID in NUMBER,
89   X_OBJECT_VERSION_NUMBER in NUMBER,
90   X_APPLICATION_ID in NUMBER,
91   X_WHERE_CLAUSE in VARCHAR2,
92   X_NAME in VARCHAR2,
93   X_DESCRIPTION in VARCHAR2
94 ) is
95   cursor c is select
96       OBJECT_VERSION_NUMBER,
97       APPLICATION_ID,
98       WHERE_CLAUSE
99     from IEU_UWQ_RES_CATS_B
100     where RES_CAT_ID = X_RES_CAT_ID
101     for update of RES_CAT_ID nowait;
102   recinfo c%rowtype;
103 
104   cursor c1 is select
105       NAME,
106       DESCRIPTION,
107       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
108     from IEU_UWQ_RES_CATS_TL
109     where RES_CAT_ID = X_RES_CAT_ID
110     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
111     for update of RES_CAT_ID nowait;
112 begin
113   open c;
114   fetch c into recinfo;
115   if (c%notfound) then
116     close c;
117     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
118     app_exception.raise_exception;
119   end if;
120   close c;
121   if (    (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
122       AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
123       AND (recinfo.WHERE_CLAUSE = X_WHERE_CLAUSE)
124   ) then
125     null;
126   else
127     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128     app_exception.raise_exception;
129   end if;
130 
131   for tlinfo in c1 loop
132     if (tlinfo.BASELANG = 'Y') then
133       if (    (tlinfo.NAME = X_NAME)
134           AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
135       ) then
136         null;
137       else
138         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
139         app_exception.raise_exception;
140       end if;
141     end if;
142   end loop;
143   return;
144 end LOCK_ROW;
145 
146 procedure UPDATE_ROW (
147   X_RES_CAT_ID in NUMBER,
148   X_OBJECT_VERSION_NUMBER in NUMBER,
149   X_APPLICATION_ID in NUMBER,
150   X_WHERE_CLAUSE in VARCHAR2,
151   X_NAME in VARCHAR2,
152   X_DESCRIPTION in VARCHAR2,
153   X_LAST_UPDATE_DATE in DATE,
154   X_LAST_UPDATED_BY in NUMBER,
155   X_LAST_UPDATE_LOGIN in NUMBER
156 ) is
157 begin
158   update IEU_UWQ_RES_CATS_B set
159     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
160     APPLICATION_ID = X_APPLICATION_ID,
161     WHERE_CLAUSE = X_WHERE_CLAUSE,
162     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
163     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
164     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
165   where RES_CAT_ID = X_RES_CAT_ID;
166 
167   if (sql%notfound) then
168     raise no_data_found;
169   end if;
170 
171   update IEU_UWQ_RES_CATS_TL set
172     NAME = X_NAME,
173     DESCRIPTION = X_DESCRIPTION,
174     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
175     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
176     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
177     SOURCE_LANG = userenv('LANG')
178   where RES_CAT_ID = X_RES_CAT_ID
179   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
180 
181   if (sql%notfound) then
182     raise no_data_found;
183   end if;
184 end UPDATE_ROW;
185 
186 procedure DELETE_ROW (
187   X_RES_CAT_ID in NUMBER
188 ) is
189 begin
190   delete from IEU_UWQ_RES_CATS_TL
191   where RES_CAT_ID = X_RES_CAT_ID;
192 
193   if (sql%notfound) then
194     raise no_data_found;
195   end if;
196 
197   delete from IEU_UWQ_RES_CATS_B
198   where RES_CAT_ID = X_RES_CAT_ID;
199 
200   if (sql%notfound) then
201     raise no_data_found;
202   end if;
203 end DELETE_ROW;
204 
205 procedure ADD_LANGUAGE
206 is
207 begin
208   delete from IEU_UWQ_RES_CATS_TL T
209   where not exists
210     (select NULL
211     from IEU_UWQ_RES_CATS_B B
212     where B.RES_CAT_ID = T.RES_CAT_ID
213     );
214 
215   update IEU_UWQ_RES_CATS_TL T set (
216       NAME,
217       DESCRIPTION
218     ) = (select
219       B.NAME,
220       B.DESCRIPTION
221     from IEU_UWQ_RES_CATS_TL B
222     where B.RES_CAT_ID = T.RES_CAT_ID
223     and B.LANGUAGE = T.SOURCE_LANG)
224   where (
225       T.RES_CAT_ID,
226       T.LANGUAGE
227   ) in (select
228       SUBT.RES_CAT_ID,
229       SUBT.LANGUAGE
230     from IEU_UWQ_RES_CATS_TL SUBB, IEU_UWQ_RES_CATS_TL SUBT
231     where SUBB.RES_CAT_ID = SUBT.RES_CAT_ID
232     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
233     and (SUBB.NAME <> SUBT.NAME
234       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
235   ));
236 
237   insert into IEU_UWQ_RES_CATS_TL (
238     RES_CAT_ID,
239     OBJECT_VERSION_NUMBER,
240     CREATED_BY,
241     CREATION_DATE,
242     LAST_UPDATED_BY,
243     LAST_UPDATE_DATE,
244     LAST_UPDATE_LOGIN,
245     NAME,
246     DESCRIPTION,
247     LANGUAGE,
248     SOURCE_LANG
249   ) select /*+ ORDERED */
250     B.RES_CAT_ID,
251     B.OBJECT_VERSION_NUMBER,
252     B.CREATED_BY,
253     B.CREATION_DATE,
254     B.LAST_UPDATED_BY,
255     B.LAST_UPDATE_DATE,
256     B.LAST_UPDATE_LOGIN,
257     B.NAME,
258     B.DESCRIPTION,
259     L.LANGUAGE_CODE,
260     B.SOURCE_LANG
261   from IEU_UWQ_RES_CATS_TL B, FND_LANGUAGES L
262   where L.INSTALLED_FLAG in ('I', 'B')
263   and B.LANGUAGE = userenv('LANG')
264   and not exists
265     (select NULL
266     from IEU_UWQ_RES_CATS_TL T
267     where T.RES_CAT_ID = B.RES_CAT_ID
268     and T.LANGUAGE = L.LANGUAGE_CODE);
269 end ADD_LANGUAGE;
270 
271 procedure LOAD_ROW (
272   X_RES_CAT_ID in NUMBER,
273   X_APPLICATION_SHORT_NAME in VARCHAR2,
274   X_WHERE_CLAUSE in VARCHAR2,
275   X_NAME in VARCHAR2,
276   X_DESCRIPTION in VARCHAR2,
277   X_OWNER in VARCHAR2
278 ) is
279   l_user_id  number := 0;
280   l_rowid    varchar2(50);
281   l_app_id   number;
282 
283 begin
284 
285   IF (x_owner = 'SEED') then
286     l_user_id := 1;
287   end if;
288 
289   select
290     application_id
291   into
292     l_app_id
293   from
294     fnd_application
295   where
296     application_short_name = x_application_short_name;
297 
298   begin
299 
300     UPDATE_ROW(
301       X_RES_CAT_ID => x_res_cat_id,
302       X_OBJECT_VERSION_NUMBER => 0,
303       X_APPLICATION_ID => l_app_id,
304       X_WHERE_CLAUSE => x_where_clause,
305       X_NAME => x_name,
306       X_DESCRIPTION => x_description,
307       X_LAST_UPDATE_DATE => SYSDATE,
308       --X_LAST_UPDATED_BY => l_user_id,
309       X_LAST_UPDATED_BY => fnd_load_util.owner_id(X_OWNER),
310       X_LAST_UPDATE_LOGIN => 0
311     );
312 
313     if (sql%notfound) then
314       raise no_data_found;
315     end if;
316 
317   exception
318     when no_data_found then
319 
320       INSERT_ROW(
321         X_ROWID => l_rowid,
322         X_RES_CAT_ID => x_res_cat_id,
323         X_OBJECT_VERSION_NUMBER => 0,
324         X_APPLICATION_ID => l_app_id,
325         X_WHERE_CLAUSE => x_where_clause,
326         X_NAME => x_name,
327         X_DESCRIPTION => x_description,
328         X_CREATION_DATE => SYSDATE,
329         --X_CREATED_BY => l_user_id,
330 	   X_CREATED_BY => fnd_load_util.owner_id(X_OWNER),
331         X_LAST_UPDATE_DATE => SYSDATE,
332         --X_LAST_UPDATED_BY => l_user_id,
333         X_LAST_UPDATED_BY => fnd_load_util.owner_id(X_OWNER),
334         X_LAST_UPDATE_LOGIN => 0
335       );
336 
337   end;
338 
339 end LOAD_ROW;
340 
341 
342 procedure TRANSLATE_ROW (
343   X_RES_CAT_ID in NUMBER,
344   X_NAME in VARCHAR2,
345   X_DESCRIPTION in VARCHAR2,
346   X_OWNER in VARCHAR2
347 ) is
348 begin
349 
350   -- only UPDATE rows that have not been altered by user
351 
352   UPDATE
353     IEU_UWQ_RES_CATS_TL
354   SET
355     source_lang = userenv('LANG'),
356     name = x_name,
357     description = x_description,
358     last_update_date = sysdate,
359     --last_updated_by = decode(x_owner, 'SEED', 1, 0),
360     last_updated_by = fnd_load_util.owner_id(X_OWNER),
361     last_update_login = 0
362   WHERE
363     (res_cat_id = x_res_cat_id) and
364     (userenv('LANG') IN (language, source_lang));
365 
366   if (sql%notfound) then
367     raise no_data_found;
368   end if;
369 
370 end TRANSLATE_ROW;
371 
372 procedure LOAD_SEED_ROW(
373   X_UPLOAD_MODE in VARCHAR2,
374   X_RES_CAT_ID in NUMBER,
375   X_NAME in VARCHAR2,
376   X_DESCRIPTION in VARCHAR2,
377   X_OWNER in VARCHAR2,
378   X_APPLICATION_SHORT_NAME in VARCHAR2,
379   X_WHERE_CLAUSE in VARCHAR2
380 ) is
381 begin
382 
383   if (X_UPLOAD_MODE = 'NLS') then
384     TRANSLATE_ROW (
385       X_RES_CAT_ID,
386       X_NAME,
387       X_DESCRIPTION,
388       X_OWNER);
389   else
390     LOAD_ROW (
391       X_RES_CAT_ID,
392       X_APPLICATION_SHORT_NAME,
393       X_WHERE_CLAUSE,
394       X_NAME,
395       X_DESCRIPTION,
396       X_OWNER);
397   end if;
398 
399 end LOAD_SEED_ROW;
400 
401 
402 end IEU_UWQ_RES_CATS_PKG;