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_ACTION_ITEMS_SEED_PKG

Source


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