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_SH_ACT_TYPES_PKG

Source


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