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_TASK_PRIORITIES_PKG

Source


1 PACKAGE body JTF_TASK_PRIORITIES_PKG as
2 /* $Header: jtftkprb.pls 120.2 2006/05/30 13:16:22 sbarat ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in OUT NOCOPY VARCHAR2,
5   X_TASK_PRIORITY_ID in NUMBER,
6   X_IMPORTANCE_LEVEL in NUMBER,
7   X_START_DATE_ACTIVE in DATE,
8   X_END_DATE_ACTIVE in DATE,
9   X_SEEDED_FLAG in VARCHAR2,
10   X_ATTRIBUTE1 in VARCHAR2,
11   X_ATTRIBUTE2 in VARCHAR2,
12   X_ATTRIBUTE3 in VARCHAR2,
13   X_ATTRIBUTE4 in VARCHAR2,
14   X_ATTRIBUTE5 in VARCHAR2,
15   X_ATTRIBUTE6 in VARCHAR2,
16   X_ATTRIBUTE7 in VARCHAR2,
17   X_ATTRIBUTE8 in VARCHAR2,
18   X_ATTRIBUTE9 in VARCHAR2,
19   X_ATTRIBUTE10 in VARCHAR2,
20   X_ATTRIBUTE11 in VARCHAR2,
21   X_ATTRIBUTE12 in VARCHAR2,
22   X_ATTRIBUTE13 in VARCHAR2,
23   X_ATTRIBUTE14 in VARCHAR2,
24   X_ATTRIBUTE15 in VARCHAR2,
25   X_ATTRIBUTE_CATEGORY in VARCHAR2,
26   X_NAME in VARCHAR2,
27   X_DESCRIPTION in VARCHAR2,
28   X_CREATION_DATE in DATE,
29   X_CREATED_BY in NUMBER,
30   X_LAST_UPDATE_DATE in DATE,
31   X_LAST_UPDATED_BY in NUMBER,
32   X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34   cursor C is select ROWID from JTF_TASK_PRIORITIES_B
35     where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID
36     ;
37 begin
38   insert into JTF_TASK_PRIORITIES_B (
39     TASK_PRIORITY_ID,
40     IMPORTANCE_LEVEL,
41     START_DATE_ACTIVE,
42     END_DATE_ACTIVE,
43     SEEDED_FLAG,
44     ATTRIBUTE1,
45     ATTRIBUTE2,
46     ATTRIBUTE3,
47     ATTRIBUTE4,
48     ATTRIBUTE5,
49     ATTRIBUTE6,
50     ATTRIBUTE7,
51     ATTRIBUTE8,
52     ATTRIBUTE9,
53     ATTRIBUTE10,
54     ATTRIBUTE11,
55     ATTRIBUTE12,
56     ATTRIBUTE13,
57     ATTRIBUTE14,
58     ATTRIBUTE15,
59     ATTRIBUTE_CATEGORY,
60     CREATION_DATE,
61     CREATED_BY,
62     LAST_UPDATE_DATE,
63     LAST_UPDATED_BY,
64     LAST_UPDATE_LOGIN,
65     OBJECT_VERSION_NUMBER
66   ) values (
67     X_TASK_PRIORITY_ID,
68     X_IMPORTANCE_LEVEL,
69     X_START_DATE_ACTIVE,
70     X_END_DATE_ACTIVE,
71     X_SEEDED_FLAG,
72     X_ATTRIBUTE1,
73     X_ATTRIBUTE2,
74     X_ATTRIBUTE3,
75     X_ATTRIBUTE4,
76     X_ATTRIBUTE5,
77     X_ATTRIBUTE6,
78     X_ATTRIBUTE7,
79     X_ATTRIBUTE8,
80     X_ATTRIBUTE9,
81     X_ATTRIBUTE10,
82     X_ATTRIBUTE11,
83     X_ATTRIBUTE12,
84     X_ATTRIBUTE13,
85     X_ATTRIBUTE14,
86     X_ATTRIBUTE15,
87     X_ATTRIBUTE_CATEGORY,
88     X_CREATION_DATE,
89     X_CREATED_BY,
90     X_LAST_UPDATE_DATE,
91     X_LAST_UPDATED_BY,
92     X_LAST_UPDATE_LOGIN,
93     1
94   );
95 
96   insert into JTF_TASK_PRIORITIES_TL (
97     TASK_PRIORITY_ID,
98     NAME,
99     DESCRIPTION,
100     CREATED_BY,
101     CREATION_DATE,
102     LAST_UPDATED_BY,
103     LAST_UPDATE_DATE,
104     LAST_UPDATE_LOGIN,
105     LANGUAGE,
106     SOURCE_LANG
107   ) select
108     X_TASK_PRIORITY_ID,
109     X_NAME,
110     X_DESCRIPTION,
111     X_CREATED_BY,
112     X_CREATION_DATE,
113     X_LAST_UPDATED_BY,
114     X_LAST_UPDATE_DATE,
115     X_LAST_UPDATE_LOGIN,
116     L.LANGUAGE_CODE,
117     userenv('LANG')
118   from FND_LANGUAGES L
119   where L.INSTALLED_FLAG in ('I', 'B')
120   and not exists
121     (select NULL
122     from JTF_TASK_PRIORITIES_TL T
123     where T.TASK_PRIORITY_ID = X_TASK_PRIORITY_ID
124     and T.LANGUAGE = L.LANGUAGE_CODE);
125 
126   open c;
127   fetch c into X_ROWID;
128   if (c%notfound) then
129     close c;
130     raise no_data_found;
131   end if;
132   close c;
133 
134 end INSERT_ROW;
135 
136 procedure LOCK_ROW (
137   X_TASK_PRIORITY_ID in NUMBER,
138   X_OBJECT_VERSION_NUMBER in NUMBER
139 ) is
140   cursor c is select
141         OBJECT_VERSION_NUMBER
142     from JTF_TASK_PRIORITIES_VL
143     where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID
144     for update of TASK_PRIORITY_ID nowait;
145   recinfo c%rowtype;
146 
147 begin
148   open c;
149   fetch c into recinfo;
150   if (c%notfound) then
151     close c;
152     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
153     app_exception.raise_exception;
154   end if;
155   close c;
156 
157   if (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
158   then
159     null;
160   else
161     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162     app_exception.raise_exception;
163   end if;
164 
165 end LOCK_ROW;
166 
167 procedure UPDATE_ROW (
168   X_TASK_PRIORITY_ID in NUMBER,
169   X_OBJECT_VERSION_NUMBER in NUMBER,
170   X_IMPORTANCE_LEVEL in NUMBER,
171   X_START_DATE_ACTIVE in DATE,
172   X_END_DATE_ACTIVE in DATE,
173   X_SEEDED_FLAG in VARCHAR2,
174   X_ATTRIBUTE1 in VARCHAR2,
175   X_ATTRIBUTE2 in VARCHAR2,
176   X_ATTRIBUTE3 in VARCHAR2,
177   X_ATTRIBUTE4 in VARCHAR2,
178   X_ATTRIBUTE5 in VARCHAR2,
179   X_ATTRIBUTE6 in VARCHAR2,
180   X_ATTRIBUTE7 in VARCHAR2,
181   X_ATTRIBUTE8 in VARCHAR2,
182   X_ATTRIBUTE9 in VARCHAR2,
183   X_ATTRIBUTE10 in VARCHAR2,
184   X_ATTRIBUTE11 in VARCHAR2,
185   X_ATTRIBUTE12 in VARCHAR2,
186   X_ATTRIBUTE13 in VARCHAR2,
187   X_ATTRIBUTE14 in VARCHAR2,
188   X_ATTRIBUTE15 in VARCHAR2,
189   X_ATTRIBUTE_CATEGORY in VARCHAR2,
190   X_NAME in VARCHAR2,
191   X_DESCRIPTION in VARCHAR2,
192   X_LAST_UPDATE_DATE in DATE,
193   X_LAST_UPDATED_BY in NUMBER,
194   X_LAST_UPDATE_LOGIN in NUMBER
195 ) is
196 begin
197   update JTF_TASK_PRIORITIES_B set
198     IMPORTANCE_LEVEL = X_IMPORTANCE_LEVEL,
199     /*CHANGED TO OBJECT_VERSION_NUMBER +1 */
200     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER+1,
201     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
202     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
203     SEEDED_FLAG = X_SEEDED_FLAG,
204     ATTRIBUTE1 = X_ATTRIBUTE1,
205     ATTRIBUTE2 = X_ATTRIBUTE2,
206     ATTRIBUTE3 = X_ATTRIBUTE3,
207     ATTRIBUTE4 = X_ATTRIBUTE4,
208     ATTRIBUTE5 = X_ATTRIBUTE5,
209     ATTRIBUTE6 = X_ATTRIBUTE6,
210     ATTRIBUTE7 = X_ATTRIBUTE7,
211     ATTRIBUTE8 = X_ATTRIBUTE8,
212     ATTRIBUTE9 = X_ATTRIBUTE9,
213     ATTRIBUTE10 = X_ATTRIBUTE10,
214     ATTRIBUTE11 = X_ATTRIBUTE11,
215     ATTRIBUTE12 = X_ATTRIBUTE12,
216     ATTRIBUTE13 = X_ATTRIBUTE13,
217     ATTRIBUTE14 = X_ATTRIBUTE14,
218     ATTRIBUTE15 = X_ATTRIBUTE15,
219     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
220     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
222     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
223   where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID ;
224 
225   if (sql%notfound) then
226     raise no_data_found;
227   end if;
228 
229   update JTF_TASK_PRIORITIES_TL set
230     NAME = X_NAME,
231     DESCRIPTION = X_DESCRIPTION,
232     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
233     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
234     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
235     SOURCE_LANG = userenv('LANG')
236   where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID
237   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
238 
239   if (sql%notfound) then
240     raise no_data_found;
241   end if;
242 end UPDATE_ROW;
243 
244 procedure DELETE_ROW (
245   X_TASK_PRIORITY_ID in NUMBER
246 ) is
247 begin
248   delete from JTF_TASK_PRIORITIES_TL
249   where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID;
250 
251   if (sql%notfound) then
252     raise no_data_found;
253   end if;
254 
255   delete from JTF_TASK_PRIORITIES_B
256   where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID ;
257 
258   if (sql%notfound) then
259     raise no_data_found;
260   end if;
261 end DELETE_ROW;
262 
263 procedure ADD_LANGUAGE
264 is
265 begin
266 
267   /* Solving Perf. Bug 3723927*/
268      /* The following delete and update statements are commented out */
269      /* as a quick workaround to fix the time-consuming table handler issue */
270   /*
271   delete from JTF_TASK_PRIORITIES_TL T
272   where not exists
273     (select NULL
274     from JTF_TASK_PRIORITIES_B B
275     where B.TASK_PRIORITY_ID = T.TASK_PRIORITY_ID
276     );
277 
278   update JTF_TASK_PRIORITIES_TL T set (
279       NAME,
280       DESCRIPTION
281     ) = (select
282       B.NAME,
283       B.DESCRIPTION
284     from JTF_TASK_PRIORITIES_TL B
285     where B.TASK_PRIORITY_ID = T.TASK_PRIORITY_ID
286     and B.LANGUAGE = T.SOURCE_LANG)
287   where (
288       T.TASK_PRIORITY_ID,
289       T.LANGUAGE
290   ) in (select
291       SUBT.TASK_PRIORITY_ID,
292       SUBT.LANGUAGE
293     from JTF_TASK_PRIORITIES_TL SUBB, JTF_TASK_PRIORITIES_TL SUBT
294     where SUBB.TASK_PRIORITY_ID = SUBT.TASK_PRIORITY_ID
295     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
296     and (SUBB.NAME <> SUBT.NAME
297       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
298       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
299       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
300   ));
301   */
302   insert into JTF_TASK_PRIORITIES_TL (
303     TASK_PRIORITY_ID,
304     NAME,
305     DESCRIPTION,
306     CREATED_BY,
307     CREATION_DATE,
308     LAST_UPDATED_BY,
309     LAST_UPDATE_DATE,
310     LAST_UPDATE_LOGIN,
311     LANGUAGE,
312     SOURCE_LANG
313   ) select /*+ INDEX(b JTF_TASK_PRIORITIES_TL_U1) INDEX (l FND_LANGUAGES_N1) */  -- Added index hint for bug# 5213367 on 30/05/2006
314     B.TASK_PRIORITY_ID,
315     B.NAME,
316     B.DESCRIPTION,
317     B.CREATED_BY,
318     B.CREATION_DATE,
319     B.LAST_UPDATED_BY,
320     B.LAST_UPDATE_DATE,
321     B.LAST_UPDATE_LOGIN,
322     L.LANGUAGE_CODE,
323     B.SOURCE_LANG
324   from JTF_TASK_PRIORITIES_TL B, FND_LANGUAGES L
325   where L.INSTALLED_FLAG in ('I', 'B')
326   and B.LANGUAGE = userenv('LANG')
327   and not exists
328     (select NULL
329     from JTF_TASK_PRIORITIES_TL T
330     where T.TASK_PRIORITY_ID = B.TASK_PRIORITY_ID
331     and T.LANGUAGE = L.LANGUAGE_CODE);
332 end ADD_LANGUAGE;
333 
334 procedure TRANSLATE_ROW(
335   X_TASK_priority_ID in varchar2,
336   X_NAME in varchar2,
337   X_DESCRIPTION in varchar2,
338   x_owner in varchar2  ) is
339 l_user_id                 NUMBER := 0;
340    BEGIN
341       IF x_owner = 'SEED'
342       THEN
343          l_user_id := 1;
344       END IF;
345   update jtf_task_priorities_tl set
346     NAME= nvl(X_NAME,name),
347     DESCRIPTION= nvl(X_DESCRIPTION,description),
348     LAST_UPDATE_DATE = sysdate,
349     LAST_UPDATE_LOGIN = 0,
350     SOURCE_LANG = userenv('LANG'),
351     last_updated_by  = l_user_id
352     where task_priority_id = X_task_priority_id
353   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
354 
355   if (sql%notfound) then
356     raise no_data_found;
357   end if;
358 end TRANSLATE_ROW;
359 
360 procedure LOAD_ROW (
361   X_TASK_PRIORITY_ID in NUMBER,
362   X_IMPORTANCE_LEVEL in NUMBER,
363   X_START_DATE_ACTIVE in DATE,
364   X_END_DATE_ACTIVE in DATE,
365   X_SEEDED_FLAG in VARCHAR2,
366   X_ATTRIBUTE1 in VARCHAR2,
367   X_ATTRIBUTE2 in VARCHAR2,
368   X_ATTRIBUTE3 in VARCHAR2,
369   X_ATTRIBUTE4 in VARCHAR2,
370   X_ATTRIBUTE5 in VARCHAR2,
371   X_ATTRIBUTE6 in VARCHAR2,
372   X_ATTRIBUTE7 in VARCHAR2,
373   X_ATTRIBUTE8 in VARCHAR2,
374   X_ATTRIBUTE9 in VARCHAR2,
375   X_ATTRIBUTE10 in VARCHAR2,
376   X_ATTRIBUTE11 in VARCHAR2,
377   X_ATTRIBUTE12 in VARCHAR2,
378   X_ATTRIBUTE13 in VARCHAR2,
379   X_ATTRIBUTE14 in VARCHAR2,
380   X_ATTRIBUTE15 in VARCHAR2,
381   X_ATTRIBUTE_CATEGORY in VARCHAR2,
382   X_NAME in VARCHAR2,
383   X_DESCRIPTION in VARCHAR2,
384   X_OWNER in VARCHAR2
385 )
386 AS
387       l_user_id                 NUMBER := 0;
388       l_task_type_id            NUMBER;
389       l_rowid                   ROWID;
390       l_object_version_number   NUMBER;
391    BEGIN
392       IF x_owner = 'SEED'
393       THEN
394          l_user_id := 1;
395       END IF;
396 
397       --Check if there is record in the base table as well as in the TL table.
398       --If there is some faulty data where we populate the base table without the
399       --populating the TL table then this query will throw no_data_found exception.
400 
401 
402       SELECT object_version_number
403         INTO l_object_version_number
404          from jtf_task_priorities_vl
405          where  task_priority_id = x_task_priority_id;
406 
407 
408 
409       update JTF_TASK_PRIORITIES_B set
410         IMPORTANCE_LEVEL = X_IMPORTANCE_LEVEL,
411         /*CHANGED TO OBJECT_VERSION_NUMBER +1 */
412          OBJECT_VERSION_NUMBER = l_object_version_number+1,
413           START_DATE_ACTIVE = X_START_DATE_ACTIVE,
414            END_DATE_ACTIVE = X_END_DATE_ACTIVE,
415             SEEDED_FLAG = X_SEEDED_FLAG,
416              LAST_UPDATE_DATE = sysdate,
417             LAST_UPDATED_BY = l_user_id,
418            LAST_UPDATE_LOGIN = 0
419          where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID ;
420 
421 
422       update JTF_TASK_PRIORITIES_TL set
423          NAME = X_NAME,
424           DESCRIPTION = X_DESCRIPTION,
425             LAST_UPDATE_DATE = sysdate,
426            LAST_UPDATED_BY = l_user_id,
427           LAST_UPDATE_LOGIN = 0,
428          SOURCE_LANG = userenv('LANG')
429        where TASK_PRIORITY_ID = X_TASK_PRIORITY_ID
430         and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
431 
432 
433    exception
434    when no_data_found then
435 
436 
437    jtf_task_priorities_pkg.insert_row (
438 	    x_rowid => l_rowid ,
439          x_task_priority_id => x_task_priority_id,
440 	    x_importance_level => x_IMPORTANCE_LEVEL ,
441          x_start_date_active => x_start_date_active,
442          x_end_date_active => x_end_date_active,
443          x_seeded_flag => x_seeded_flag,
444          x_attribute1 => x_attribute1,
445          x_attribute2 => x_attribute2,
446          x_attribute3 => x_attribute3,
447          x_attribute4 => x_attribute4,
448          x_attribute5 => x_attribute5,
449          x_attribute6 => x_attribute6,
450          x_attribute7 => x_attribute7,
451          x_attribute8 => x_attribute8,
452          x_attribute9 => x_attribute9,
453          x_attribute10 => x_attribute10,
454          x_attribute11 => x_attribute11,
455          x_attribute12 => x_attribute12,
456          x_attribute13 => x_attribute13,
457          x_attribute14 => x_attribute14,
458          x_attribute15 => x_attribute15,
459          x_attribute_category => x_attribute_category,
460          x_name => x_name,
461          x_description => x_description,
462 	    x_creation_date => sysdate ,
463 	    x_created_by => l_user_id  ,
464          x_last_update_date => SYSDATE,
465          x_last_updated_by => l_user_id  ,
466          x_last_update_login => 0
467       );
468 
469 end ;
470 
471 
472 
473 end JTF_TASK_PRIORITIES_PKG;