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_PVT

Source


1 PACKAGE BODY ICX_CAT_CATEGORIES_PVT AS
2 /* $Header: ICXVCATB.pls 120.0 2005/10/20 06:48:08 srmani noship $ */
3 
4 procedure INSERT_ROW (
5   X_RT_CATEGORY_ID in NUMBER,
6   X_CATEGORY_NAME in VARCHAR2,
7   X_UPPER_CATEGORY_NAME in VARCHAR2,
8   X_DESCRIPTION in VARCHAR2,
9   X_TYPE in NUMBER,
10   X_KEY in VARCHAR2,
11   X_TITLE in VARCHAR2,
12   X_ITEM_COUNT in NUMBER,
13   X_CREATION_DATE in DATE,
14   X_CREATED_BY in NUMBER,
15   X_LAST_UPDATE_DATE in DATE,
16   X_LAST_UPDATED_BY in NUMBER,
17   X_LAST_UPDATE_LOGIN in NUMBER,
18   X_REQUEST_ID in NUMBER,
19   X_PROGRAM_APPLICATION_ID in NUMBER,
20   X_PROGRAM_ID in NUMBER,
21   X_PROGRAM_UPDATE_DATE in DATE
22 ) is
23   cursor C is select ROWID from ICX_CAT_CATEGORIES_TL
24     where RT_CATEGORY_ID = X_RT_CATEGORY_ID
25     and LANGUAGE = userenv('LANG');
26    X_ROWID   VARCHAR2(64);
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,
203       X_CUSTOM_MODE          IN VARCHAR2,
204       X_LAST_UPDATE_DATE     IN VARCHAR2)
205 IS
206 BEGIN
207   DECLARE
208      F_LUBY     NUMBER; -- entity owner in file
209      F_LUDATE   DATE; -- entity update in file
210      DB_LUBY    NUMBER; -- entity owner in db
211      DB_LUDATE  DATE; -- entity update in db
212 
213   BEGIN
214   -- Translate owner to file_last_updated_by
215     F_LUBY := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
216     F_LUDATE := NVL(TO_DATE(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), SYSDATE);
217 
218     SELECT LAST_UPDATED_BY,
219            LAST_UPDATE_DATE
220     INTO   DB_LUBY,
221            DB_LUDATE
222     FROM   ICX_CAT_CATEGORIES_TL
223     WHERE  LANGUAGE = USERENV('LANG')
224            AND RT_CATEGORY_ID = TO_NUMBER(X_RT_CATEGORY_ID); -- Update record, honoring customization mode.
225     -- Record should be updated only if:
226     -- a. CUSTOM_MODE = FORCE, or
227     -- b. file owner is CUSTOM, db owner is SEED
228     -- c. owners are the same, and file_date > db_date
229 
230     IF (FND_LOAD_UTIL.UPLOAD_TEST(P_FILE_ID => F_LUBY,
231                                   P_FILE_LUD => F_LUDATE,
232                                   P_DB_ID => DB_LUBY,
233                                   P_DB_LUD => DB_LUDATE,
234                                   P_CUSTOM_MODE => X_CUSTOM_MODE)) THEN
235       UPDATE ICX_CAT_CATEGORIES_TL
236       SET    CATEGORY_NAME = X_CATEGORY_NAME,
237              UPPER_CATEGORY_NAME = UPPER(X_CATEGORY_NAME),
238              DESCRIPTION = X_DESCRIPTION,
239              LAST_UPDATE_DATE = SYSDATE,
240              LAST_UPDATED_BY = F_LUBY,
241              LAST_UPDATE_LOGIN = 0,
242              SOURCE_LANG = USERENV('LANG')
243       WHERE  RT_CATEGORY_ID = TO_NUMBER(X_RT_CATEGORY_ID)
244              AND USERENV('LANG') IN (LANGUAGE,
245                                      SOURCE_LANG);
246     END IF;
247   END;
248 END TRANSLATE_ROW;
249 
250 
251 PROCEDURE LOAD_ROW
252      (X_RT_CATEGORY_ID       IN VARCHAR2,
253       X_OWNER                IN VARCHAR2,
254       X_CATEGORY_NAME        IN VARCHAR2,
255       X_UPPER_CATEGORY_NAME  IN VARCHAR2,
256       X_DESCRIPTION          IN VARCHAR2,
257       X_TYPE                 IN VARCHAR2,
258       X_KEY                  IN VARCHAR2,
259       X_TITLE                IN VARCHAR2,
260       X_ITEM_COUNT           IN VARCHAR2,
261       X_CUSTOM_MODE          IN VARCHAR2,
262       X_LAST_UPDATE_DATE     IN VARCHAR2)
263 IS
264 BEGIN
265   DECLARE
266     ROW_ID     VARCHAR2(64);
267      F_LUBY     NUMBER; -- entity owner in file
268      F_LUDATE   DATE; -- entity update in file
269      DB_LUBY    NUMBER; -- entity owner in db
270      DB_LUDATE  DATE; -- entity update in db
271   BEGIN
272   -- Translate owner to file_last_updated_by
273     F_LUBY := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
274 
275     F_LUDATE := NVL(TO_DATE(X_LAST_UPDATE_DATE,
276                             'YYYY/MM/DD'),
277                     SYSDATE);
278 
279     SELECT LAST_UPDATED_BY,
280            LAST_UPDATE_DATE
281     INTO   DB_LUBY,
282            DB_LUDATE
283     FROM   ICX_CAT_CATEGORIES_TL
284     WHERE  LANGUAGE = USERENV('LANG')
285            AND RT_CATEGORY_ID = TO_NUMBER(X_RT_CATEGORY_ID); -- Update record, honoring customization mode.
286     -- Record should be updated only if:
287     -- a. CUSTOM_MODE = FORCE, or
288     -- b. file owner is CUSTOM, db owner is SEED
289     -- c. owners are the same, and file_date > db_date
290 
291     IF (FND_LOAD_UTIL.UPLOAD_TEST(P_FILE_ID => F_LUBY,
292                                   P_FILE_LUD => F_LUDATE,
293                                   P_DB_ID => DB_LUBY,
294                                   P_DB_LUD => DB_LUDATE,
295                                   P_CUSTOM_MODE => X_CUSTOM_MODE)) THEN
296       ICX_CAT_CATEGORIES_PVT.UPDATE_ROW(X_RT_CATEGORY_ID => TO_NUMBER(X_RT_CATEGORY_ID),
297                                         X_CATEGORY_NAME => X_CATEGORY_NAME,
298                                         X_UPPER_CATEGORY_NAME => X_UPPER_CATEGORY_NAME,
299                                         X_DESCRIPTION => X_DESCRIPTION,
300                                         X_TYPE => TO_NUMBER(X_TYPE),
301                                         X_KEY => X_KEY,
302                                         X_TITLE => X_TITLE,
303                                         X_ITEM_COUNT => TO_NUMBER(X_ITEM_COUNT),
304                                         X_LAST_UPDATE_DATE => SYSDATE,
305                                         X_LAST_UPDATED_BY => F_LUBY,
306                                         X_LAST_UPDATE_LOGIN => 0,
307                                         X_REQUEST_ID => NULL,
308                                         X_PROGRAM_APPLICATION_ID => NULL,
309                                         X_PROGRAM_ID => NULL,
310                                         X_PROGRAM_UPDATE_DATE => NULL);
311     END IF;
312   EXCEPTION
313     WHEN NO_DATA_FOUND  THEN
314       ICX_CAT_CATEGORIES_PVT.INSERT_ROW(X_RT_CATEGORY_ID => TO_NUMBER(X_RT_CATEGORY_ID),
315                                         X_CATEGORY_NAME => X_CATEGORY_NAME,
316                                         X_UPPER_CATEGORY_NAME => X_UPPER_CATEGORY_NAME,
317                                         X_DESCRIPTION => X_DESCRIPTION,
318                                         X_TYPE => TO_NUMBER(X_TYPE),
319                                         X_KEY => X_KEY,
320                                         X_TITLE => X_TITLE,
321                                         X_ITEM_COUNT => TO_NUMBER(X_ITEM_COUNT),
322                                         X_CREATION_DATE => SYSDATE,
323                                         X_CREATED_BY => F_LUBY,
324                                         X_LAST_UPDATE_DATE => SYSDATE,
325                                         X_LAST_UPDATED_BY => F_LUBY,
326                                         X_LAST_UPDATE_LOGIN => 0,
327                                         X_REQUEST_ID => NULL,
328                                         X_PROGRAM_APPLICATION_ID => NULL,
329                                         X_PROGRAM_ID => NULL,
330                                         X_PROGRAM_UPDATE_DATE => NULL);
331   END;
332 END LOAD_ROW;
333 
334 
335 
336 
337 procedure ADD_LANGUAGE
338 is
339 begin
340   insert into ICX_CAT_CATEGORIES_TL (
341     RT_CATEGORY_ID,
342     CATEGORY_NAME,
343     UPPER_CATEGORY_NAME,
344     DESCRIPTION,
345     TYPE,
346     KEY,
347     UPPER_KEY,
348     TITLE,
349     ITEM_COUNT,
350     SECTION_MAP,
351     CREATED_BY,
352     CREATION_DATE,
353     LAST_UPDATED_BY,
354     LAST_UPDATE_DATE,
355     LAST_UPDATE_LOGIN,
356     REQUEST_ID,
357     PROGRAM_APPLICATION_ID,
358     PROGRAM_ID,
359     PROGRAM_UPDATE_DATE,
360     LANGUAGE,
361     SOURCE_LANG
362   ) select
363     B.RT_CATEGORY_ID,
364     B.CATEGORY_NAME,
365     upper(B.CATEGORY_NAME),
366     B.DESCRIPTION,
367     B.TYPE,
368     B.KEY,
369     upper(B.KEY),
370     B.TITLE,
371     B.ITEM_COUNT,
372     B.SECTION_MAP,
373     B.CREATED_BY,
374     B.CREATION_DATE,
375     B.LAST_UPDATED_BY,
376     B.LAST_UPDATE_DATE,
377     B.LAST_UPDATE_LOGIN,
378     B.REQUEST_ID,
379     B.PROGRAM_APPLICATION_ID,
380     B.PROGRAM_ID,
381     B.PROGRAM_UPDATE_DATE,
382     L.LANGUAGE_CODE,
383     B.SOURCE_LANG
384   from ICX_CAT_CATEGORIES_TL B, FND_LANGUAGES L
385   where L.INSTALLED_FLAG in ('I', 'B')
386   and B.LANGUAGE = userenv('LANG')
387   and not exists
388     (select NULL
389     from ICX_CAT_CATEGORIES_TL T
390     where T.RT_CATEGORY_ID = B.RT_CATEGORY_ID
391     and T.LANGUAGE = L.LANGUAGE_CODE);
392 
393 end ADD_LANGUAGE;
394 
395 
396 end ICX_CAT_CATEGORIES_PVT;