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

Source


1 PACKAGE BODY ICX_CAT_CATEGORIES_PKG AS
2 /* $Header: ICXCATIB.pls 120.1 2005/06/30 04:45:49 srmani noship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6   X_RT_CATEGORY_ID in NUMBER,
7   X_CATEGORY_NAME in VARCHAR2,
8   X_UPPER_CATEGORY_NAME in VARCHAR2,
9   X_DESCRIPTION in VARCHAR2,
10   X_TYPE in NUMBER,
11   X_KEY in VARCHAR2,
12   X_TITLE in VARCHAR2,
13   X_ITEM_COUNT in NUMBER,
14   X_CREATION_DATE in DATE,
15   X_CREATED_BY in NUMBER,
16   X_LAST_UPDATE_DATE in DATE,
17   X_LAST_UPDATED_BY in NUMBER,
18   X_LAST_UPDATE_LOGIN in NUMBER,
19   X_REQUEST_ID in NUMBER,
20   X_PROGRAM_APPLICATION_ID in NUMBER,
21   X_PROGRAM_ID in NUMBER,
22   X_PROGRAM_UPDATE_DATE in DATE
23 ) is
24   cursor C is select ROWID from ICX_CAT_CATEGORIES_TL
25     where RT_CATEGORY_ID = X_RT_CATEGORY_ID
26     and LANGUAGE = userenv('LANG');
27 begin
28   insert into ICX_CAT_CATEGORIES_TL (
29     RT_CATEGORY_ID,
30     CATEGORY_NAME,
31     UPPER_CATEGORY_NAME,
32     DESCRIPTION,
33     TYPE,
34     KEY,
35     UPPER_KEY,
36     TITLE,
37     ITEM_COUNT,
38     CREATION_DATE,
39     CREATED_BY,
40     LAST_UPDATE_DATE,
41     LAST_UPDATED_BY,
42     LAST_UPDATE_LOGIN,
43     REQUEST_ID,
44     PROGRAM_APPLICATION_ID,
45     PROGRAM_ID,
46     PROGRAM_UPDATE_DATE,
47     LANGUAGE,
48     SOURCE_LANG
49   ) select
50     X_RT_CATEGORY_ID,
51     X_CATEGORY_NAME,
52     upper(X_CATEGORY_NAME),
53     X_DESCRIPTION,
54     X_TYPE,
55     X_KEY,
56     upper(X_KEY),
57     X_TITLE,
58     X_ITEM_COUNT,
59     X_CREATION_DATE,
60     X_CREATED_BY,
61     X_LAST_UPDATE_DATE,
62     X_LAST_UPDATED_BY,
63     X_LAST_UPDATE_LOGIN,
64     X_REQUEST_ID,
65     X_PROGRAM_APPLICATION_ID,
66     X_PROGRAM_ID,
67     X_PROGRAM_UPDATE_DATE,
68     L.LANGUAGE_CODE,
69     userenv('LANG')
70   from FND_LANGUAGES L
71   where L.INSTALLED_FLAG in ('I', 'B')
72   and not exists
73     (select NULL
74     from ICX_CAT_CATEGORIES_TL T
75     where T.RT_CATEGORY_ID = X_RT_CATEGORY_ID
76     and T.LANGUAGE = L.LANGUAGE_CODE);
77 
78   open c;
79   fetch c into X_ROWID;
80   if (c%notfound) then
81     close c;
82     raise no_data_found;
83   end if;
84   close c;
85 
86 end INSERT_ROW;
87 
88 
89 
90 procedure LOCK_ROW (
91   X_RT_CATEGORY_ID in NUMBER,
92   X_CATEGORY_NAME in VARCHAR2,
93   X_UPPER_CATEGORY_NAME in VARCHAR2,
94   X_DESCRIPTION in VARCHAR2,
95   X_TYPE in NUMBER,
96   X_KEY in VARCHAR2,
97   X_TITLE in VARCHAR2,
98   X_ITEM_COUNT in NUMBER
99 ) is
100   cursor c1 is select
101       RT_CATEGORY_ID,
102       CATEGORY_NAME,
103       UPPER_CATEGORY_NAME,
104       DESCRIPTION,
105       TYPE,
106       KEY,
107       TITLE,
108       ITEM_COUNT,
109       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110     from ICX_CAT_CATEGORIES_TL
111     where RT_CATEGORY_ID = X_RT_CATEGORY_ID
112     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113     for update of RT_CATEGORY_ID nowait;
114 begin
115   for tlinfo in c1 loop
116     if (tlinfo.BASELANG = 'Y') then
117       if (    (tlinfo.CATEGORY_NAME = X_CATEGORY_NAME)
118           AND (tlinfo.UPPER_CATEGORY_NAME = X_UPPER_CATEGORY_NAME)
119           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
120                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
121           AND (tlinfo.TYPE = X_TYPE)
122           AND (tlinfo.KEY = X_KEY)
123           AND ((tlinfo.TITLE = X_TITLE)
124                OR ((tlinfo.TITLE is null) AND (X_TITLE is null)))
125           AND ((tlinfo.ITEM_COUNT = X_ITEM_COUNT)
126                OR ((tlinfo.ITEM_COUNT is null) AND (X_ITEM_COUNT is null)))
127       ) then
128         null;
129       else
130         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
131         app_exception.raise_exception;
132       end if;
133     end if;
134   end loop;
135   return;
136 
137 end LOCK_ROW;
138 
139 
140 procedure UPDATE_ROW (
141   X_RT_CATEGORY_ID in NUMBER,
142   X_CATEGORY_NAME in VARCHAR2,
143   X_UPPER_CATEGORY_NAME in VARCHAR2,
144   X_DESCRIPTION in VARCHAR2,
145   X_TYPE in NUMBER,
146   X_KEY in VARCHAR2,
147   X_TITLE in VARCHAR2,
148   X_ITEM_COUNT in NUMBER,
149   X_LAST_UPDATE_DATE in DATE,
150   X_LAST_UPDATED_BY in NUMBER,
151   X_LAST_UPDATE_LOGIN in NUMBER,
152   X_REQUEST_ID in NUMBER,
153   X_PROGRAM_APPLICATION_ID in NUMBER,
154   X_PROGRAM_ID in NUMBER,
155   X_PROGRAM_UPDATE_DATE in DATE
156 ) is
157 begin
158   update ICX_CAT_CATEGORIES_TL set
159     CATEGORY_NAME = X_CATEGORY_NAME,
160     UPPER_CATEGORY_NAME = upper(X_CATEGORY_NAME),
161     DESCRIPTION = X_DESCRIPTION,
162     TYPE = X_TYPE,
163     KEY = X_KEY,
164     UPPER_KEY = upper(X_KEY),
165     TITLE = X_TITLE,
166     ITEM_COUNT = X_ITEM_COUNT,
167     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
168     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
169     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
170     REQUEST_ID = X_REQUEST_ID,
171     PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
172     PROGRAM_ID = X_PROGRAM_ID,
173     PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
174     SOURCE_LANG = userenv('LANG')
175   where RT_CATEGORY_ID = X_RT_CATEGORY_ID
176   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
177 
178   if (sql%notfound) then
179     raise no_data_found;
180   end if;
181 end UPDATE_ROW;
182 
183 procedure DELETE_ROW (
184   X_RT_CATEGORY_ID in NUMBER
185 ) is
186 begin
187   delete from ICX_CAT_CATEGORIES_TL
188   where RT_CATEGORY_ID = X_RT_CATEGORY_ID;
189 
190   if (sql%notfound) then
191     raise no_data_found;
192   end if;
193 
194 end DELETE_ROW;
195 
196 
197 procedure TRANSLATE_ROW(
198       X_RT_CATEGORY_ID      in  VARCHAR2,
199       X_OWNER		    in  VARCHAR2,
200       X_CATEGORY_NAME       in  VARCHAR2,
201       X_UPPER_CATEGORY_NAME in  VARCHAR2,
202       X_DESCRIPTION         in  VARCHAR2 ) IS
203 begin
204 
205    update ICX_CAT_CATEGORIES_tl set
206      category_name       = X_CATEGORY_NAME,
207      upper_category_name = upper(X_CATEGORY_NAME),
208      description         = X_DESCRIPTION,
209      last_update_date    = sysdate,
210      last_updated_by     = decode(X_OWNER, 'SEED', -1, 0),
211      last_update_login   = 0,
212      source_lang         = userenv('LANG')
213    where RT_CATEGORY_ID  = to_number(X_RT_CATEGORY_ID)
214      and userenv('LANG') in (language, source_lang);
215 
216 end TRANSLATE_ROW;
217 
218 
219 procedure LOAD_ROW(
220       X_RT_CATEGORY_ID      in  VARCHAR2,
221       X_OWNER		    in  VARCHAR2,
222       X_CATEGORY_NAME       in  VARCHAR2,
223       X_UPPER_CATEGORY_NAME in  VARCHAR2,
224       X_DESCRIPTION         in  VARCHAR2,
225       X_TYPE                in  VARCHAR2,
226       X_KEY                 in  VARCHAR2,
227       X_TITLE               in  VARCHAR2,
228       X_ITEM_COUNT          in  VARCHAR2 ) IS
229 begin
230 
231   declare
232      user_id    number := 0;
233      row_id     varchar2(64);
234 
235   begin
236 
237      if (X_OWNER = 'SEED') then
238        user_id := 1;
239      end if;
240 
241      ICX_CAT_CATEGORIES_PKG.UPDATE_ROW (
242 	X_RT_CATEGORY_ID =>		to_number(X_RT_CATEGORY_ID),
243 	X_CATEGORY_NAME =>		X_CATEGORY_NAME,
244 	X_UPPER_CATEGORY_NAME =>	X_UPPER_CATEGORY_NAME,
245 	X_DESCRIPTION =>		X_DESCRIPTION,
246 	X_TYPE =>			to_number(X_TYPE),
247 	X_KEY =>			X_KEY,
248 	X_TITLE =>			X_TITLE,
249 	X_ITEM_COUNT =>			to_number(X_ITEM_COUNT),
250         X_LAST_UPDATE_DATE =>		sysdate,
251 	X_LAST_UPDATED_BY =>		user_id,
252 	X_LAST_UPDATE_LOGIN =>		0,
253 	X_REQUEST_ID =>			null,
254 	X_PROGRAM_APPLICATION_ID => 	null,
255 	X_PROGRAM_ID =>			null,
256 	X_PROGRAM_UPDATE_DATE =>	null);
257 
258   exception
259      when NO_DATA_FOUND then
260 
261        ICX_CAT_CATEGORIES_PKG.INSERT_ROW (
262           X_ROWID =>                    row_id,
263 	  X_RT_CATEGORY_ID =>		to_number(X_RT_CATEGORY_ID),
264 	  X_CATEGORY_NAME =>		X_CATEGORY_NAME,
265 	  X_UPPER_CATEGORY_NAME =>	X_UPPER_CATEGORY_NAME,
266 	  X_DESCRIPTION =>		X_DESCRIPTION,
267 	  X_TYPE =>			to_number(X_TYPE),
268 	  X_KEY =>			X_KEY,
269 	  X_TITLE =>			X_TITLE,
270 	  X_ITEM_COUNT =>		to_number(X_ITEM_COUNT),
271 	  X_CREATION_DATE =>		sysdate,
272 	  X_CREATED_BY =>		user_id,
273 	  X_LAST_UPDATE_DATE =>		sysdate,
274 	  X_LAST_UPDATED_BY =>		user_id,
275 	  X_LAST_UPDATE_LOGIN =>	0,
276 	  X_REQUEST_ID =>		null,
277 	  X_PROGRAM_APPLICATION_ID =>   null,
278 	  X_PROGRAM_ID =>		null,
279 	  X_PROGRAM_UPDATE_DATE =>      null);
280    end;
281 end LOAD_ROW;
282 
283 
284 procedure ADD_LANGUAGE
285 is
286 begin
287   /* comment out for bug 2085107
288   update ICX_CAT_CATEGORIES_TL T set (
289       CATEGORY_NAME,
290       UPPER_CATEGORY_NAME,
291       DESCRIPTION
292     ) = (select
293       B.CATEGORY_NAME,
294       upper(B.CATEGORY_NAME),
295       B.DESCRIPTION
296     from ICX_CAT_CATEGORIES_TL B
297     where B.RT_CATEGORY_ID = T.RT_CATEGORY_ID
298     and B.LANGUAGE = T.SOURCE_LANG)
299   where (
300       T.RT_CATEGORY_ID,
301       T.LANGUAGE
302   ) in (select
303       SUBT.RT_CATEGORY_ID,
304       SUBT.LANGUAGE
305     from ICX_CAT_CATEGORIES_TL SUBB, ICX_CAT_CATEGORIES_TL SUBT
306     where SUBB.RT_CATEGORY_ID = SUBT.RT_CATEGORY_ID
307     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
308     and (SUBB.CATEGORY_NAME <> SUBT.CATEGORY_NAME
309       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
310       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
311       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
312   ));
313   */
314 
315   insert into ICX_CAT_CATEGORIES_TL (
316     RT_CATEGORY_ID,
317     CATEGORY_NAME,
318     UPPER_CATEGORY_NAME,
319     DESCRIPTION,
320     TYPE,
321     KEY,
322     UPPER_KEY,
323     TITLE,
324     ITEM_COUNT,
325     SECTION_MAP,
326     CREATED_BY,
327     CREATION_DATE,
328     LAST_UPDATED_BY,
329     LAST_UPDATE_DATE,
330     LAST_UPDATE_LOGIN,
331     REQUEST_ID,
332     PROGRAM_APPLICATION_ID,
333     PROGRAM_ID,
334     PROGRAM_UPDATE_DATE,
335     LANGUAGE,
336     SOURCE_LANG
337   ) select
338     B.RT_CATEGORY_ID,
339     B.CATEGORY_NAME,
340     upper(B.CATEGORY_NAME),
341     B.DESCRIPTION,
342     B.TYPE,
343     B.KEY,
344     upper(B.KEY),
345     B.TITLE,
346     B.ITEM_COUNT,
347     B.SECTION_MAP,
348     B.CREATED_BY,
349     B.CREATION_DATE,
350     B.LAST_UPDATED_BY,
351     B.LAST_UPDATE_DATE,
352     B.LAST_UPDATE_LOGIN,
353     B.REQUEST_ID,
354     B.PROGRAM_APPLICATION_ID,
355     B.PROGRAM_ID,
356     B.PROGRAM_UPDATE_DATE,
357     L.LANGUAGE_CODE,
358     B.SOURCE_LANG
359   from ICX_CAT_CATEGORIES_TL B, FND_LANGUAGES L
360   where L.INSTALLED_FLAG in ('I', 'B')
361   and B.LANGUAGE = userenv('LANG')
362   and not exists
363     (select NULL
364     from ICX_CAT_CATEGORIES_TL T
365     where T.RT_CATEGORY_ID = B.RT_CATEGORY_ID
366     and T.LANGUAGE = L.LANGUAGE_CODE);
367 
368 end ADD_LANGUAGE;
369 
370 
371 end ICX_CAT_CATEGORIES_PKG;