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

Source


1 package body JTF_IH_ACTIONS_SEED_PKG as
2 /* $Header: JTFIHACB.pls 120.4 2006/04/18 12:59:57 rdday ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_ACTION_ID in NUMBER,
6   X_OBJECT_VERSION_NUMBER in NUMBER,
7   X_ACTION in VARCHAR2,
8   X_SHORT_DESCRIPTION in VARCHAR2,
9   X_CREATION_DATE in DATE,
10   X_CREATED_BY in NUMBER,
11   X_LAST_UPDATE_DATE in DATE,
12   X_LAST_UPDATED_BY in NUMBER,
13   X_LAST_UPDATE_LOGIN in NUMBER,
14   X_ACTIVE in VARCHAR2
15 ) is
16   cursor C is select ROWID from JTF_IH_ACTIONS_B
17     where ACTION_ID = X_ACTION_ID
18     ;
19     --Added for performance issue due to literals -  27-Jul-2004
20     L_ACTIVE_FLAG VARCHAR2(1);
21     L_INSTALLED_FLAG1 VARCHAR2(1);
22     L_INSTALLED_FLAG2 VARCHAR2(1);
23     L_LANG VARCHAR2(25);
24 begin
25     --Added for performance issue due to literals -  27-Jul-2004
26     L_ACTIVE_FLAG := 'Y';
27     L_INSTALLED_FLAG1 := 'I';
28     L_INSTALLED_FLAG2 := 'B';
29     L_LANG := userenv('LANG');
30 
31     insert into JTF_IH_ACTIONS_B (
32     ACTION_ID,
33     OBJECT_VERSION_NUMBER,
34     CREATION_DATE,
35     CREATED_BY,
36     LAST_UPDATE_DATE,
37     LAST_UPDATED_BY,
38     LAST_UPDATE_LOGIN,
39     ACTIVE
40   ) values (
41     X_ACTION_ID,
42     X_OBJECT_VERSION_NUMBER,
43     X_CREATION_DATE,
44     X_CREATED_BY,
45     X_LAST_UPDATE_DATE,
46     X_LAST_UPDATED_BY,
47     X_LAST_UPDATE_LOGIN,
48     --NVL(X_ACTIVE,'Y')
49     --Added for performance issue due to literals -  27-Jul-2004
50     NVL(X_ACTIVE,L_ACTIVE_FLAG)
51   );
52 
53   insert into JTF_IH_ACTIONS_TL (
54     ACTION_ID,
55     OBJECT_VERSION_NUMBER,
56     CREATED_BY,
57     CREATION_DATE,
58     LAST_UPDATED_BY,
59     LAST_UPDATE_DATE,
60     LAST_UPDATE_LOGIN,
61     ACTION,
62     SHORT_DESCRIPTION,
63     LANGUAGE,
64     SOURCE_LANG
65   ) select
66     X_ACTION_ID,
67     X_OBJECT_VERSION_NUMBER,
68     X_CREATED_BY,
69     X_CREATION_DATE,
70     X_LAST_UPDATED_BY,
71     X_LAST_UPDATE_DATE,
72     X_LAST_UPDATE_LOGIN,
73     X_ACTION,
74     X_SHORT_DESCRIPTION,
75     L.LANGUAGE_CODE,
76     --Added for performance issue due to literals -  27-Jul-2004
77     --userenv('LANG')
78     L_LANG
79   from FND_LANGUAGES L
80   --Added for performance issue due to literals -  27-Jul-2004
81   --where L.INSTALLED_FLAG in ('I', 'B')
82   where L.INSTALLED_FLAG in ( L_INSTALLED_FLAG1,  L_INSTALLED_FLAG2)
83   and not exists
84     (select NULL
85     from JTF_IH_ACTIONS_TL T
86     where T.ACTION_ID = X_ACTION_ID
87     and T.LANGUAGE = L.LANGUAGE_CODE);
88 
89   open c;
90   fetch c into X_ROWID;
91   if (c%notfound) then
92     close c;
93     raise no_data_found;
94   end if;
95   close c;
96 
97 end INSERT_ROW;
98 
99 procedure LOCK_ROW (
100   X_ACTION_ID in NUMBER,
101   X_OBJECT_VERSION_NUMBER in NUMBER,
102   X_ACTION in VARCHAR2,
103   X_SHORT_DESCRIPTION in VARCHAR2
104 ) is
105   cursor c is select
106       OBJECT_VERSION_NUMBER
107     from JTF_IH_ACTIONS_B
108     where ACTION_ID = X_ACTION_ID
109     for update of ACTION_ID nowait;
110   recinfo c%rowtype;
111   --Added for performance issue due to literals -  28-Jul-2004
112   L_LANG VARCHAR2(25);
113   L_YES VARCHAR2(1);
114   L_NO VARCHAR2(1);
115 
116   cursor c1 is select
117       ACTION,
118       SHORT_DESCRIPTION,
119       --Added for performance issue due to literals -  28-Jul-2004
120       --decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
121       decode(LANGUAGE, L_LANG, L_YES, L_NO) BASELANG
122     from JTF_IH_ACTIONS_TL
123     where ACTION_ID = X_ACTION_ID
124     --and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
125     --Added for performance issue due to literals -  28-Jul-2004
126     and L_LANG in (LANGUAGE, SOURCE_LANG)
127     for update of ACTION_ID nowait;
128 begin
129   --Added for performance issue due to literals -  28-Jul-2004
130   L_LANG := userenv('LANG');
131   L_YES := 'Y';
132   L_NO := 'N';
133 
134   open c;
135   fetch c into recinfo;
136   if (c%notfound) then
137     close c;
138     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
139     app_exception.raise_exception;
140   end if;
141   close c;
142   if (    ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
143            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
144   ) then
145     null;
146   else
147     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
148     app_exception.raise_exception;
149   end if;
150 
151   for tlinfo in c1 loop
152     if (tlinfo.BASELANG = 'Y') then
153       if (    ((tlinfo.ACTION = X_ACTION)
154                OR ((tlinfo.ACTION is null) AND (X_ACTION is null)))
155           AND ((tlinfo.SHORT_DESCRIPTION = X_SHORT_DESCRIPTION)
156                OR ((tlinfo.SHORT_DESCRIPTION is null) AND (X_SHORT_DESCRIPTION is null)))
157       ) then
158         null;
159       else
160         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
161         app_exception.raise_exception;
162       end if;
163     end if;
164   end loop;
165   return;
166 end LOCK_ROW;
167 
168 procedure UPDATE_ROW (
169   X_ACTION_ID in NUMBER,
170   X_OBJECT_VERSION_NUMBER in NUMBER,
171   X_ACTION in VARCHAR2,
172   X_SHORT_DESCRIPTION in VARCHAR2,
173   X_LAST_UPDATE_DATE in DATE,
174   X_LAST_UPDATED_BY in NUMBER,
175   X_LAST_UPDATE_LOGIN in NUMBER,
176   X_ACTIVE in VARCHAR2
177 ) is
178 --Added for performance issue due to literals -  27-Jul-2004
179 L_ACTIVE_FLAG VARCHAR2(1);
180 L_LANG VARCHAR2(25);
181 begin
182 --Added for performance issue due to literals -  27-Jul-2004
183 L_ACTIVE_FLAG := 'Y';
184 L_LANG := userenv('LANG');
185 
186   update JTF_IH_ACTIONS_B set
187     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
188     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
189     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
190     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
191     --Added for performance issue due to literals -  27-Jul-2004
192     --ACTIVE = NVL(X_ACTIVE,'Y')
193     ACTIVE = NVL(X_ACTIVE,L_ACTIVE_FLAG)
194   where ACTION_ID = X_ACTION_ID;
195 
196   if (sql%notfound) then
197     raise no_data_found;
198   end if;
199 
200   update JTF_IH_ACTIONS_TL set
201     ACTION = X_ACTION,
202     SHORT_DESCRIPTION = X_SHORT_DESCRIPTION,
203     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
204     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
205     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
206     --Added for performance issue due to literals -  27-Jul-2004
207     --SOURCE_LANG = userenv('LANG')
208     SOURCE_LANG = L_LANG
209   where ACTION_ID = X_ACTION_ID
210   --Added for performance issue due to literals -  27-Jul-2004
211   --and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
212   and L_LANG in (LANGUAGE, SOURCE_LANG);
213 
214   if (sql%notfound) then
215     raise no_data_found;
216   end if;
217 end UPDATE_ROW;
218 
219 procedure DELETE_ROW (
220   X_ACTION_ID in NUMBER
221 ) is
222 begin
223   delete from JTF_IH_ACTIONS_TL
224   where ACTION_ID = X_ACTION_ID;
225 
226   if (sql%notfound) then
227     raise no_data_found;
228   end if;
229 
230   delete from JTF_IH_ACTIONS_B
231   where ACTION_ID = X_ACTION_ID;
232 
233   if (sql%notfound) then
234     raise no_data_found;
235   end if;
236 end DELETE_ROW;
237 
238 procedure ADD_LANGUAGE
239 is
240     --Added for performance issue due to literals -  28-Jul-2004
241     L_INSTALLED_FLAG1 VARCHAR2(1);
242     L_INSTALLED_FLAG2 VARCHAR2(1);
243     L_LANG VARCHAR2(25);
244 
245 
246 begin
247     --Added for performance issue due to literals -  28-Jul-2004
248     L_INSTALLED_FLAG1 := 'I';
249     L_INSTALLED_FLAG2 := 'B';
250     L_LANG := userenv('LANG');
251 
252   delete from JTF_IH_ACTIONS_TL T
253   where not exists
254     (select NULL
255     from JTF_IH_ACTIONS_B B
256     where B.ACTION_ID = T.ACTION_ID
257     );
258 
259   update JTF_IH_ACTIONS_TL T set (
260       ACTION,
261       SHORT_DESCRIPTION
262     ) = (select
263       B.ACTION,
264       B.SHORT_DESCRIPTION
265     from JTF_IH_ACTIONS_TL B
266     where B.ACTION_ID = T.ACTION_ID
267     and B.LANGUAGE = T.SOURCE_LANG)
268   where (
269       T.ACTION_ID,
270       T.LANGUAGE
271   ) in (select
272       SUBT.ACTION_ID,
273       SUBT.LANGUAGE
274     from JTF_IH_ACTIONS_TL SUBB, JTF_IH_ACTIONS_TL SUBT
275     where SUBB.ACTION_ID = SUBT.ACTION_ID
276     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
277     and (SUBB.ACTION <> SUBT.ACTION
278       or (SUBB.ACTION is null and SUBT.ACTION is not null)
279       or (SUBB.ACTION is not null and SUBT.ACTION is null)
280       or SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
281       or (SUBB.SHORT_DESCRIPTION is null and SUBT.SHORT_DESCRIPTION is not null)
282       or (SUBB.SHORT_DESCRIPTION is not null and SUBT.SHORT_DESCRIPTION is null)
283   ));
284 
285   insert into JTF_IH_ACTIONS_TL (
286     ACTION_ID,
287     OBJECT_VERSION_NUMBER,
288     CREATED_BY,
289     CREATION_DATE,
290     LAST_UPDATED_BY,
291     LAST_UPDATE_DATE,
292     LAST_UPDATE_LOGIN,
293     ACTION,
294     SHORT_DESCRIPTION,
295     LANGUAGE,
296     SOURCE_LANG
297   ) select
298     B.ACTION_ID,
299     B.OBJECT_VERSION_NUMBER,
300     B.CREATED_BY,
301     B.CREATION_DATE,
302     B.LAST_UPDATED_BY,
303     B.LAST_UPDATE_DATE,
304     B.LAST_UPDATE_LOGIN,
305     B.ACTION,
306     B.SHORT_DESCRIPTION,
307     L.LANGUAGE_CODE,
308     B.SOURCE_LANG
309   from JTF_IH_ACTIONS_TL B, FND_LANGUAGES L
310   where L.INSTALLED_FLAG in (L_INSTALLED_FLAG1, L_INSTALLED_FLAG2)
311   and B.LANGUAGE = L_LANG
312   and not exists
313     (select NULL
314     from JTF_IH_ACTIONS_TL T
315     where T.ACTION_ID = B.ACTION_ID
316     and T.LANGUAGE = L.LANGUAGE_CODE);
317 end ADD_LANGUAGE;
318 
319 procedure LOAD_ROW (
320   X_ACTION_ID in NUMBER,
321   X_ACTION in VARCHAR2,
322   X_OBJECT_VERSION_NUMBER in NUMBER,
323   X_SHORT_DESCRIPTION in VARCHAR2,
324   X_OWNER IN VARCHAR2,
325   X_ACTIVE in VARCHAR2
326 ) IS
327 begin
328 declare
329 	user_id			NUMBER := 0;
330 	row_id			VARCHAR2(64);
331 	l_api_version		NUMBER := 1.0;
332 	l_return_status		VARCHAR2(1);
333 	l_msg_count		NUMBER;
334 	l_msg_data		VARCHAR2(100);
335 	l_init_msg_list		VARCHAR2(1) := 'F';
336 	l_commit		VARCHAR2(1) := 'F';
337 	l_validation_level 	NUMBER := 100;
338   	l_action_id 		NUMBER;
339   	l_object_version_number NUMBER;
340   	l_action		VARCHAR2(80);
341   	l_short_description 	VARCHAR2(240);
342 	l_last_update_date	DATE;
343 	l_last_updated_by	NUMBER;
344 	l_last_update_login	NUMBER;
345 	l_creation_date		DATE;
346 	l_created_by		NUMBER;
347     	l_active            VARCHAR2(1) := 'Y';
348 
349 begin
350 	--if (x_owner = 'SEED') then
351 	--	user_id := 1;
352 	--end if;
353         user_id := fnd_load_util.owner_id(x_owner);
354   	l_action_id := X_ACTION_ID;
355   	l_object_version_number := 1;
356   	l_action := X_ACTION;
357   	l_short_description := X_SHORT_DESCRIPTION;
358 	l_last_update_date := sysdate;
359 	l_last_updated_by := user_id;
360 	l_last_update_login := 0;
361 
362 	UPDATE_ROW(
363   			X_ACTION_ID => l_action_id,
364 			X_OBJECT_VERSION_NUMBER => l_object_version_number,
365   			X_ACTION => l_action,
366   			X_SHORT_DESCRIPTION => l_short_description,
367   			X_LAST_UPDATE_DATE => l_last_update_date,
368   			X_LAST_UPDATED_BY => l_last_updated_by,
369   			X_LAST_UPDATE_LOGIN => l_last_update_login,
370             X_ACTIVE => NVL(X_ACTIVE,'Y'));
371 	EXCEPTION
372 		when no_data_found then
373 			l_creation_date := sysdate;
374 			l_created_by := user_id;
375 			INSERT_ROW(
376 			row_id,
377   			X_ACTION_ID => l_action_id,
378   			X_OBJECT_VERSION_NUMBER => l_object_version_number,
379   			X_ACTION => l_action,
380   			X_SHORT_DESCRIPTION => l_short_description,
381 			X_CREATION_DATE => l_creation_date,
382 			X_CREATED_BY => l_created_by,
383   			X_LAST_UPDATE_DATE => l_last_update_date,
384   			X_LAST_UPDATED_BY => l_last_updated_by,
385   			X_LAST_UPDATE_LOGIN => l_last_update_login,
386             X_ACTIVE => NVL(X_ACTIVE,'Y'));
387 	end;
388 end LOAD_ROW;
389 
390 procedure TRANSLATE_ROW (
391   X_ACTION_ID in NUMBER,
392   X_ACTION in VARCHAR2,
393   X_SHORT_DESCRIPTION in VARCHAR2,
394   X_OWNER IN VARCHAR2) is
395    --Added for performance issue due to literals -  28-Jul-2004
396   L_LANG VARCHAR2(25);
397   L_SEED VARCHAR2(25);
398   L_UPDATEDBY1 NUMBER;
399   L_UPDATEDBY0 NUMBER;
400 begin
401         --Added for performance issue due to literals -  28-Jul-2004
402 	L_LANG := userenv('LANG');
403 	L_SEED := 'SEED';
404 	L_UPDATEDBY1 := 1;
405 	L_UPDATEDBY0 := 0;
406 
407 	UPDATE jtf_ih_actions_tl SET
408           action_id = X_ACTION_ID,
409 	  action = X_ACTION,
410 	  short_description = X_SHORT_DESCRIPTION,
411 	  last_update_date = sysdate,
412 	  --Added for performance issue due to literals -  28-Jul-2004
413 	  --last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
414 	  --last_updated_by = decode(X_OWNER, L_SEED, L_UPDATEDBY1, L_UPDATEDBY0),
415           last_updated_by = fnd_load_util.owner_id(x_owner),
416   	  last_update_login = 0,
417 	  --Added for performance issue due to literals -  28-Jul-2004
418 	  --source_lang = userenv('LANG')
419 	  source_lang = L_LANG
420 	  --WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
421 	  WHERE L_LANG in (LANGUAGE, SOURCE_LANG) AND
422 	  action_id = X_ACTION_ID;
423 end TRANSLATE_ROW;
424 
425 procedure LOAD_SEED_ROW (
426   X_ACTION_ID in NUMBER,
427   X_ACTION in VARCHAR2,
428   X_OBJECT_VERSION_NUMBER in NUMBER,
429   X_SHORT_DESCRIPTION in VARCHAR2,
430   X_OWNER IN VARCHAR2,
431   X_ACTIVE in VARCHAR2,
432   X_UPLOAD_MODE in VARCHAR2
433 ) IS
434 begin
435 if (X_UPLOAD_MODE = 'NLS') then
436 		JTF_IH_ACTIONS_SEED_PKG.TRANSLATE_ROW (
437 			X_ACTION_ID,
438 			X_ACTION,
439 			X_SHORT_DESCRIPTION,
440 			X_OWNER);
441 
442 	else
443 		JTF_IH_ACTIONS_SEED_PKG.LOAD_ROW (
444 			X_ACTION_ID,
445 			X_ACTION,
446 			X_OBJECT_VERSION_NUMBER,
447 			X_SHORT_DESCRIPTION,
448 			X_OWNER,
449 			X_ACTIVE);
450 	end if;
451 end LOAD_SEED_ROW;
452 
453 end JTF_IH_ACTIONS_SEED_PKG;