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_RS_ROLES_PKG

Source


1 package body JTF_RS_ROLES_PKG as
2 /* $Header: jtfrstrb.pls 120.0 2005/05/11 08:22:32 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_ROLE_ID in NUMBER,
6   X_ATTRIBUTE3 in VARCHAR2,
7   X_ATTRIBUTE4 in VARCHAR2,
8   X_ATTRIBUTE5 in VARCHAR2,
9   X_ATTRIBUTE6 in VARCHAR2,
10   X_ATTRIBUTE7 in VARCHAR2,
11   X_ATTRIBUTE8 in VARCHAR2,
12   X_ATTRIBUTE9 in VARCHAR2,
13   X_ATTRIBUTE10 in VARCHAR2,
14   X_ATTRIBUTE11 in VARCHAR2,
15   X_ATTRIBUTE12 in VARCHAR2,
16   X_ATTRIBUTE13 in VARCHAR2,
17   X_ATTRIBUTE14 in VARCHAR2,
18   X_ATTRIBUTE15 in VARCHAR2,
19   X_ATTRIBUTE_CATEGORY in VARCHAR2,
20   X_ROLE_CODE in VARCHAR2,
21   X_ROLE_TYPE_CODE in VARCHAR2,
22   X_SEEDED_FLAG in VARCHAR2,
23   X_MEMBER_FLAG in VARCHAR2,
24   X_ADMIN_FLAG in VARCHAR2,
25   X_LEAD_FLAG in VARCHAR2,
26   X_MANAGER_FLAG in VARCHAR2,
27   X_ACTIVE_FLAG in VARCHAR2,
28   X_ATTRIBUTE1 in VARCHAR2,
29   X_ATTRIBUTE2 in VARCHAR2,
30   X_ROLE_NAME in VARCHAR2,
31   X_ROLE_DESC in VARCHAR2,
32   X_CREATION_DATE in DATE,
33   X_CREATED_BY in NUMBER,
34   X_LAST_UPDATE_DATE in DATE,
35   X_LAST_UPDATED_BY in NUMBER,
36   X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38   cursor C is select ROWID from JTF_RS_ROLES_B
39     where ROLE_ID = X_ROLE_ID
40     ;
41 begin
42   insert into JTF_RS_ROLES_B (
43     ATTRIBUTE3,
44     ATTRIBUTE4,
45     ATTRIBUTE5,
46     ATTRIBUTE6,
47     ATTRIBUTE7,
48     ATTRIBUTE8,
49     ATTRIBUTE9,
50     ATTRIBUTE10,
51     ATTRIBUTE11,
52     ATTRIBUTE12,
53     ATTRIBUTE13,
54     ATTRIBUTE14,
55     ATTRIBUTE15,
56     ATTRIBUTE_CATEGORY,
57     ROLE_ID,
58     ROLE_CODE,
59     ROLE_TYPE_CODE,
60     SEEDED_FLAG,
61     MEMBER_FLAG,
62     ADMIN_FLAG,
63     LEAD_FLAG,
64     MANAGER_FLAG,
65     ACTIVE_FLAG,
66     OBJECT_VERSION_NUMBER,
67     ATTRIBUTE1,
68     ATTRIBUTE2,
69     CREATION_DATE,
70     CREATED_BY,
71     LAST_UPDATE_DATE,
72     LAST_UPDATED_BY,
73     LAST_UPDATE_LOGIN
74   ) values (
75     X_ATTRIBUTE3,
76     X_ATTRIBUTE4,
77     X_ATTRIBUTE5,
78     X_ATTRIBUTE6,
79     X_ATTRIBUTE7,
80     X_ATTRIBUTE8,
81     X_ATTRIBUTE9,
82     X_ATTRIBUTE10,
83     X_ATTRIBUTE11,
84     X_ATTRIBUTE12,
85     X_ATTRIBUTE13,
86     X_ATTRIBUTE14,
87     X_ATTRIBUTE15,
88     X_ATTRIBUTE_CATEGORY,
89     X_ROLE_ID,
90     X_ROLE_CODE,
91     X_ROLE_TYPE_CODE,
92     X_SEEDED_FLAG,
93     X_MEMBER_FLAG,
94     X_ADMIN_FLAG,
95     X_LEAD_FLAG,
96     X_MANAGER_FLAG,
97     X_ACTIVE_FLAG,
98     1,
99     X_ATTRIBUTE1,
100     X_ATTRIBUTE2,
101     X_CREATION_DATE,
102     X_CREATED_BY,
103     X_LAST_UPDATE_DATE,
104     X_LAST_UPDATED_BY,
105     X_LAST_UPDATE_LOGIN
106   );
107 
108   insert into JTF_RS_ROLES_TL (
109     ROLE_ID,
110     CREATED_BY,
111     CREATION_DATE,
112     LAST_UPDATED_BY,
113     LAST_UPDATE_DATE,
114     LAST_UPDATE_LOGIN,
115     ROLE_NAME,
116     ROLE_DESC,
117     LANGUAGE,
118     SOURCE_LANG
119   ) select
120     X_ROLE_ID,
121     X_CREATED_BY,
122     X_CREATION_DATE,
123     X_LAST_UPDATED_BY,
124     X_LAST_UPDATE_DATE,
125     X_LAST_UPDATE_LOGIN,
126     X_ROLE_NAME,
127     X_ROLE_DESC,
128     L.LANGUAGE_CODE,
129     userenv('LANG')
130   from FND_LANGUAGES L
131   where L.INSTALLED_FLAG in ('I', 'B')
132   and not exists
133     (select NULL
134     from JTF_RS_ROLES_TL T
135     where T.ROLE_ID = X_ROLE_ID
136     and T.LANGUAGE = L.LANGUAGE_CODE);
137 
138   open c;
139   fetch c into X_ROWID;
140   if (c%notfound) then
141     close c;
142     raise no_data_found;
143   end if;
144   close c;
145 
146 end INSERT_ROW;
147 
148 procedure LOCK_ROW (
149   X_ROLE_ID in NUMBER,
150   X_OBJECT_VERSION_NUMBER in NUMBER
151 ) is
152   cursor c is select
153       OBJECT_VERSION_NUMBER
154     from JTF_RS_ROLES_B
155     where ROLE_ID = X_ROLE_ID
156     and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
157     for update of ROLE_ID nowait;
158   recinfo c%rowtype;
159 
160 begin
161   open c;
162   fetch c into recinfo;
163   if (c%notfound) then
164     close c;
165     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
166     app_exception.raise_exception;
167   end if;
168 
169   close c;
170 
171   if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
172     null;
173   else
174     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175     app_exception.raise_exception;
176   end if;
177 
178 end LOCK_ROW;
179 
180 procedure UPDATE_ROW (
181   X_ROLE_ID in NUMBER,
182   X_ATTRIBUTE3 in VARCHAR2,
183   X_ATTRIBUTE4 in VARCHAR2,
184   X_ATTRIBUTE5 in VARCHAR2,
185   X_ATTRIBUTE6 in VARCHAR2,
186   X_ATTRIBUTE7 in VARCHAR2,
187   X_ATTRIBUTE8 in VARCHAR2,
188   X_ATTRIBUTE9 in VARCHAR2,
189   X_ATTRIBUTE10 in VARCHAR2,
190   X_ATTRIBUTE11 in VARCHAR2,
191   X_ATTRIBUTE12 in VARCHAR2,
192   X_ATTRIBUTE13 in VARCHAR2,
193   X_ATTRIBUTE14 in VARCHAR2,
194   X_ATTRIBUTE15 in VARCHAR2,
195   X_ATTRIBUTE_CATEGORY in VARCHAR2,
196   X_ROLE_CODE in VARCHAR2,
197   X_ROLE_TYPE_CODE in VARCHAR2,
198   X_SEEDED_FLAG in VARCHAR2,
199   X_MEMBER_FLAG in VARCHAR2,
200   X_ADMIN_FLAG in VARCHAR2,
201   X_LEAD_FLAG in VARCHAR2,
202   X_MANAGER_FLAG in VARCHAR2,
203   X_ACTIVE_FLAG in VARCHAR2,
204   X_OBJECT_VERSION_NUMBER in NUMBER,
205   X_ATTRIBUTE1 in VARCHAR2,
206   X_ATTRIBUTE2 in VARCHAR2,
207   X_ROLE_NAME in VARCHAR2,
208   X_ROLE_DESC in VARCHAR2,
209   X_LAST_UPDATE_DATE in DATE,
210   X_LAST_UPDATED_BY in NUMBER,
211   X_LAST_UPDATE_LOGIN in NUMBER
212 ) is
213 begin
214   update JTF_RS_ROLES_B set
215     ATTRIBUTE3 = X_ATTRIBUTE3,
216     ATTRIBUTE4 = X_ATTRIBUTE4,
217     ATTRIBUTE5 = X_ATTRIBUTE5,
218     ATTRIBUTE6 = X_ATTRIBUTE6,
219     ATTRIBUTE7 = X_ATTRIBUTE7,
220     ATTRIBUTE8 = X_ATTRIBUTE8,
221     ATTRIBUTE9 = X_ATTRIBUTE9,
222     ATTRIBUTE10 = X_ATTRIBUTE10,
223     ATTRIBUTE11 = X_ATTRIBUTE11,
224     ATTRIBUTE12 = X_ATTRIBUTE12,
225     ATTRIBUTE13 = X_ATTRIBUTE13,
226     ATTRIBUTE14 = X_ATTRIBUTE14,
227     ATTRIBUTE15 = X_ATTRIBUTE15,
228     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
229     ROLE_CODE = X_ROLE_CODE,
230     ROLE_TYPE_CODE = X_ROLE_TYPE_CODE,
231     SEEDED_FLAG = X_SEEDED_FLAG,
232     MEMBER_FLAG = X_MEMBER_FLAG,
233     ADMIN_FLAG = X_ADMIN_FLAG,
234     LEAD_FLAG = X_LEAD_FLAG,
235     MANAGER_FLAG = X_MANAGER_FLAG,
236     ACTIVE_FLAG = X_ACTIVE_FLAG,
237     OBJECT_VERSION_NUMBER = nvl(OBJECT_VERSION_NUMBER,1) + 1,
238     ATTRIBUTE1 = X_ATTRIBUTE1,
239     ATTRIBUTE2 = X_ATTRIBUTE2,
240     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
241     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
242     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
243   where ROLE_ID = X_ROLE_ID;
244 
245   if (sql%notfound) then
246     raise no_data_found;
247   end if;
248 
249   update JTF_RS_ROLES_TL set
250     ROLE_NAME = X_ROLE_NAME,
251     ROLE_DESC = X_ROLE_DESC,
252     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
253     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
254     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
255     SOURCE_LANG = userenv('LANG')
256   where ROLE_ID = X_ROLE_ID
257   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
258 
259   if (sql%notfound) then
260     raise no_data_found;
261   end if;
262 end UPDATE_ROW;
263 
264 Procedure TRANSLATE_ROW
265 (X_role_id  in number,
266  X_role_name in varchar2,
267  x_role_desc in varchar2,
268  x_Last_update_date in date,
269  x_last_updated_by in number,
270  x_last_update_login in number)
271 is
272 begin
273 
274 Update jtf_rs_roles_tl set
275 role_name		= nvl(x_role_name,role_name),
276 role_desc		= nvl(x_role_desc,role_desc),
277 last_update_date	= nvl(x_last_update_date,sysdate),
278 last_updated_by		= x_last_updated_by,
279 last_update_login	= 0,
280 source_lang		= userenv('LANG')
281 where role_id		= x_role_id
282 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
283 
284 end TRANSLATE_ROW;
285 
286 procedure LOAD_ROW (
287   X_ROLE_ID in NUMBER,
288   X_ATTRIBUTE3 in VARCHAR2,
289   X_ATTRIBUTE4 in VARCHAR2,
290   X_ATTRIBUTE5 in VARCHAR2,
291   X_ATTRIBUTE6 in VARCHAR2,
292   X_ATTRIBUTE7 in VARCHAR2,
293   X_ATTRIBUTE8 in VARCHAR2,
294   X_ATTRIBUTE9 in VARCHAR2,
295   X_ATTRIBUTE10 in VARCHAR2,
296   X_ATTRIBUTE11 in VARCHAR2,
297   X_ATTRIBUTE12 in VARCHAR2,
298   X_ATTRIBUTE13 in VARCHAR2,
299   X_ATTRIBUTE14 in VARCHAR2,
300   X_ATTRIBUTE15 in VARCHAR2,
301   X_ATTRIBUTE_CATEGORY in VARCHAR2,
302   X_ROLE_CODE in VARCHAR2,
303   X_ROLE_TYPE_CODE in VARCHAR2,
304   X_SEEDED_FLAG in VARCHAR2,
305   X_MEMBER_FLAG in VARCHAR2,
306   X_ADMIN_FLAG in VARCHAR2,
307   X_LEAD_FLAG in VARCHAR2,
308   X_MANAGER_FLAG in VARCHAR2,
309   X_ACTIVE_FLAG in VARCHAR2,
310   X_OBJECT_VERSION_NUMBER in NUMBER,
311   X_ATTRIBUTE1 in VARCHAR2,
312   X_ATTRIBUTE2 in VARCHAR2,
313   X_ROLE_NAME in VARCHAR2,
314   X_ROLE_DESC in VARCHAR2,
315   X_OWNER in VARCHAR2
316 ) is
317 l_row_id rowid;
318 l_user_id number;
319 l_last_updated_by number := -1;
320 
321 CURSOR c_last_updated IS
322   SELECT last_updated_by from JTF_RS_ROLES_VL
323   WHERE role_id = X_ROLE_ID;
324 
325 begin
326 if (X_OWNER = 'SEED') then
327 	l_user_id := 1;
328 else
329 	l_user_id := 0;
330 end if;
331 
332 OPEN c_last_updated;
333 FETCH c_last_updated into l_last_updated_by;
334       IF c_last_updated%NOTFOUND THEN
335 	 jtf_rs_roles_pkg.insert_row(
336                 X_ROWID               => l_row_id ,
337                 X_ROLE_ID               => x_role_id ,
338                 X_ATTRIBUTE3            => X_ATTRIBUTE3 ,
339                 X_ATTRIBUTE4            => X_ATTRIBUTE4 ,
340                 X_ATTRIBUTE5            => X_ATTRIBUTE5 ,
341                 X_ATTRIBUTE6            => X_ATTRIBUTE6 ,
342                 X_ATTRIBUTE7            => X_ATTRIBUTE7 ,
343                 X_ATTRIBUTE8            => X_ATTRIBUTE8 ,
344                 X_ATTRIBUTE9            => X_ATTRIBUTE9 ,
345                 X_ATTRIBUTE10           => X_ATTRIBUTE10 ,
346                 X_ATTRIBUTE11           => X_ATTRIBUTE11 ,
347                 X_ATTRIBUTE12           => X_ATTRIBUTE12 ,
348                 X_ATTRIBUTE13           => X_ATTRIBUTE13 ,
349                 X_ATTRIBUTE14           => X_ATTRIBUTE14 ,
350                 X_ATTRIBUTE15           => X_ATTRIBUTE15 ,
351                 X_ATTRIBUTE_CATEGORY    => X_ATTRIBUTE_CATEGORY ,
352                 X_ROLE_CODE             => x_role_code ,
353                 X_ROLE_TYPE_CODE        => x_role_type_code ,
354                 X_SEEDED_FLAG           => x_seeded_flag ,
355                 X_MEMBER_FLAG           => x_member_flag ,
356                 X_ADMIN_FLAG            => x_admin_flag ,
357                 X_LEAD_FLAG             => x_lead_flag ,
358                 X_MANAGER_FLAG          => x_manager_flag ,
359                 X_ACTIVE_FLAG           => x_active_flag ,
360                 X_ATTRIBUTE1            => X_ATTRIBUTE1 ,
361                 X_ATTRIBUTE2            => X_ATTRIBUTE2 ,
362                 X_ROLE_NAME             => x_role_name ,
363                 X_ROLE_DESC             => x_role_desc ,
364                 X_CREATION_DATE		=> sysdate      ,
365                 X_CREATED_BY	        => l_user_id ,
366                 X_LAST_UPDATE_DATE      => sysdate      ,
367                 X_LAST_UPDATED_BY       => l_user_id ,
368                 X_LAST_UPDATE_LOGIN     => 0     );
369       ELSIF c_last_updated%FOUND THEN
370          IF l_last_updated_by = 1 THEN
371             jtf_rs_roles_pkg.update_row(
372                 X_ROLE_ID               => x_role_id ,
373                 X_ATTRIBUTE3            => X_ATTRIBUTE3 ,
374                 X_ATTRIBUTE4            => X_ATTRIBUTE4 ,
375                 X_ATTRIBUTE5            => X_ATTRIBUTE5 ,
376                 X_ATTRIBUTE6            => X_ATTRIBUTE6 ,
377                 X_ATTRIBUTE7            => X_ATTRIBUTE7 ,
378                 X_ATTRIBUTE8            => X_ATTRIBUTE8 ,
379                 X_ATTRIBUTE9            => X_ATTRIBUTE9 ,
380                 X_ATTRIBUTE10           => X_ATTRIBUTE10 ,
381                 X_ATTRIBUTE11           => X_ATTRIBUTE11 ,
382                 X_ATTRIBUTE12           => X_ATTRIBUTE12 ,
383                 X_ATTRIBUTE13           => X_ATTRIBUTE13 ,
384                 X_ATTRIBUTE14           => X_ATTRIBUTE14 ,
385                 X_ATTRIBUTE15           => X_ATTRIBUTE15 ,
386                 X_ATTRIBUTE_CATEGORY    => X_ATTRIBUTE_CATEGORY ,
387                 X_ROLE_CODE             => x_role_code ,
388                 X_ROLE_TYPE_CODE        => x_role_type_code ,
389                 X_SEEDED_FLAG           => x_seeded_flag ,
390                 X_MEMBER_FLAG           => x_member_flag ,
391                 X_ADMIN_FLAG            => x_admin_flag ,
392                 X_LEAD_FLAG             => x_lead_flag ,
393                 X_MANAGER_FLAG          => x_manager_flag ,
394                 X_ACTIVE_FLAG           => x_active_flag ,
395                 X_OBJECT_VERSION_NUMBER => x_object_version_number ,
396                 X_ATTRIBUTE1            => X_ATTRIBUTE1 ,
397                 X_ATTRIBUTE2            => X_ATTRIBUTE2 ,
398                 X_ROLE_NAME             => x_role_name ,
399                 X_ROLE_DESC             => x_role_desc ,
400                 X_LAST_UPDATE_DATE      => sysdate      ,
401                 X_LAST_UPDATED_BY       => l_user_id ,
402                 X_LAST_UPDATE_LOGIN     => 0     );
403            END IF;
404       END IF;
405 CLOSE c_last_updated;
406 End LOAD_ROW;
407 
408 
409 procedure DELETE_ROW (
410   X_ROLE_ID in NUMBER
411 ) is
412 begin
413   delete from JTF_RS_ROLES_TL
414   where ROLE_ID = X_ROLE_ID;
415 
416   if (sql%notfound) then
417     raise no_data_found;
418   end if;
419 
420   delete from JTF_RS_ROLES_B
421   where ROLE_ID = X_ROLE_ID;
422 
423   if (sql%notfound) then
424     raise no_data_found;
425   end if;
426 end DELETE_ROW;
427 
428 procedure ADD_LANGUAGE
429 is
430 begin
431   delete from JTF_RS_ROLES_TL T
432   where not exists
433     (select NULL
434     from JTF_RS_ROLES_B B
435     where B.ROLE_ID = T.ROLE_ID
436     );
437 
438   update JTF_RS_ROLES_TL T set (
439       ROLE_NAME,
440       ROLE_DESC
441     ) = (select
442       B.ROLE_NAME,
443       B.ROLE_DESC
444     from JTF_RS_ROLES_TL B
445     where B.ROLE_ID = T.ROLE_ID
446     and B.LANGUAGE = T.SOURCE_LANG)
447   where (
448       T.ROLE_ID,
449       T.LANGUAGE
450   ) in (select
451       SUBT.ROLE_ID,
452       SUBT.LANGUAGE
453     from JTF_RS_ROLES_TL SUBB, JTF_RS_ROLES_TL SUBT
454     where SUBB.ROLE_ID = SUBT.ROLE_ID
455     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
456     and (SUBB.ROLE_NAME <> SUBT.ROLE_NAME
457       or SUBB.ROLE_DESC <> SUBT.ROLE_DESC
458       or (SUBB.ROLE_DESC is null and SUBT.ROLE_DESC is not null)
459       or (SUBB.ROLE_DESC is not null and SUBT.ROLE_DESC is null)
460   ));
461 
462   insert into JTF_RS_ROLES_TL (
463     ROLE_ID,
464     CREATED_BY,
465     CREATION_DATE,
466     LAST_UPDATED_BY,
467     LAST_UPDATE_DATE,
468     LAST_UPDATE_LOGIN,
469     ROLE_NAME,
470     ROLE_DESC,
471     LANGUAGE,
472     SOURCE_LANG
473   ) select
474     B.ROLE_ID,
475     B.CREATED_BY,
476     B.CREATION_DATE,
477     B.LAST_UPDATED_BY,
478     B.LAST_UPDATE_DATE,
479     B.LAST_UPDATE_LOGIN,
480     B.ROLE_NAME,
481     B.ROLE_DESC,
482     L.LANGUAGE_CODE,
483     B.SOURCE_LANG
484   from JTF_RS_ROLES_TL B, FND_LANGUAGES L
485   where L.INSTALLED_FLAG in ('I', 'B')
486   and B.LANGUAGE = userenv('LANG')
487   and not exists
488     (select NULL
489     from JTF_RS_ROLES_TL T
490     where T.ROLE_ID = B.ROLE_ID
491     and T.LANGUAGE = L.LANGUAGE_CODE);
492 end ADD_LANGUAGE;
493 
494 end JTF_RS_ROLES_PKG;