[Home] [Help]
PACKAGE BODY: APPS.JTF_AUTH_PERMISSIONS_PKG
Source
1 package body JTF_AUTH_PERMISSIONS_PKG as
2 /* $Header: JTFSEAPB.pls 115.7 2002/06/11 22:06:16 sfazil ship $ */
3 procedure INSERT_ROW (
4 X_JTF_AUTH_PERMISSION_ID in NUMBER,
5 X_PERMISSION_DESC_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_PERMISSION_NAME in VARCHAR2,
8 X_APPLICATION_ID in NUMBER,
9 X_PERMISSION_CLASS_NAME in VARCHAR2,
10 X_OWNERABLE_FLAG in NUMBER,
11 X_IS_DAC_FLAG in NUMBER,
12 X_MAINTENANCE_MODE_FLAG in NUMBER,
13 X_PERMISSION_DESC in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from JTF_AUTH_PERMISSIONS_B
22 where JTF_AUTH_PERMISSION_ID = X_JTF_AUTH_PERMISSION_ID
23 ;
24 begin
25 insert into JTF_AUTH_PERMISSIONS_B (
26 JTF_AUTH_PERMISSION_ID,
27 OBJECT_VERSION_NUMBER,
28 PERMISSION_NAME,
29 APPLICATION_ID,
30 PERMISSION_CLASS_NAME,
31 OWNERABLE_FLAG,
32 PERMISSION_DESC_ID,
33 IS_DAC_FLAG,
34 MAINTENANCE_MODE_FLAG,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 X_JTF_AUTH_PERMISSION_ID,
42 X_OBJECT_VERSION_NUMBER,
43 X_PERMISSION_NAME,
44 X_APPLICATION_ID,
45 X_PERMISSION_CLASS_NAME,
46 X_OWNERABLE_FLAG,
47 X_PERMISSION_DESC_ID,
48 X_IS_DAC_FLAG,
49 X_MAINTENANCE_MODE_FLAG,
50 X_CREATION_DATE,
51 X_CREATED_BY,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_LAST_UPDATE_LOGIN
55 );
56
57 insert into JTF_AUTH_PERMISSIONS_TL (
58 PERMISSION_DESC_ID,
59 PERMISSION_DESC,
60 DESCRIPTION,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_JTF_AUTH_PERMISSION_ID,
70 X_PERMISSION_DESC,
71 X_DESCRIPTION,
72 X_CREATION_DATE,
73 X_CREATED_BY,
74 X_LAST_UPDATE_DATE,
75 X_LAST_UPDATED_BY,
76 X_LAST_UPDATE_LOGIN,
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 from FND_LANGUAGES L
80 where L.INSTALLED_FLAG in ('I', 'B')
81 and not exists
82 (select NULL
83 from JTF_AUTH_PERMISSIONS_TL T
84 where T.PERMISSION_DESC_ID = X_JTF_AUTH_PERMISSION_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 end INSERT_ROW;
95
96 procedure LOCK_ROW (
97 X_JTF_AUTH_PERMISSION_ID in NUMBER,
98 X_PERMISSION_DESC_ID in NUMBER,
99 X_OBJECT_VERSION_NUMBER in NUMBER,
100 X_PERMISSION_NAME in VARCHAR2,
101 X_APPLICATION_ID in NUMBER,
102 X_PERMISSION_CLASS_NAME in VARCHAR2,
103 X_OWNERABLE_FLAG in NUMBER,
104 X_IS_DAC_FLAG in NUMBER,
105 X_MAINTENANCE_MODE_FLAG in NUMBER,
106 X_PERMISSION_DESC in VARCHAR2,
107 X_DESCRIPTION in VARCHAR2
108 ) is
109 cursor c is select
110 PERMISSION_DESC_ID,
111 OBJECT_VERSION_NUMBER,
112 PERMISSION_NAME,
113 APPLICATION_ID,
114 PERMISSION_CLASS_NAME,
115 OWNERABLE_FLAG,
116 IS_DAC_FLAG,
117 MAINTENANCE_MODE_FLAG
118 from JTF_AUTH_PERMISSIONS_B
119 where JTF_AUTH_PERMISSION_ID = X_JTF_AUTH_PERMISSION_ID
120 for update of JTF_AUTH_PERMISSION_ID nowait;
121 recinfo c%rowtype;
122
123 cursor c1 is select
124 PERMISSION_DESC,
125 DESCRIPTION,
126 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
127 from JTF_AUTH_PERMISSIONS_TL
128 where PERMISSION_DESC_ID = X_JTF_AUTH_PERMISSION_ID
129 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
130 for update of PERMISSION_DESC_ID nowait;
131 begin
132 open c;
133 fetch c into recinfo;
134 if (c%notfound) then
135 close c;
136 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
137 app_exception.raise_exception;
138 end if;
139 close c;
140 if (
141 ((recinfo.PERMISSION_DESC_ID = X_PERMISSION_DESC_ID)
142 OR ((recinfo.PERMISSION_DESC_ID is null) AND (X_PERMISSION_DESC_ID is null)))
143 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
144 AND (recinfo.PERMISSION_NAME = X_PERMISSION_NAME)
145 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
146 AND ((recinfo.PERMISSION_CLASS_NAME = X_PERMISSION_CLASS_NAME)
147 OR ((recinfo.PERMISSION_CLASS_NAME is null) AND (X_PERMISSION_CLASS_NAME is null)))
148 AND (recinfo.OWNERABLE_FLAG = X_OWNERABLE_FLAG)
149 AND (recinfo.MAINTENANCE_MODE_FLAG = X_MAINTENANCE_MODE_FLAG)
150 AND ((recinfo.IS_DAC_FLAG = X_IS_DAC_FLAG)
151 OR ((recinfo.IS_DAC_FLAG is null) AND (X_IS_DAC_FLAG is null)))
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158
159 for tlinfo in c1 loop
160 if (tlinfo.BASELANG = 'Y') then
161 if ( ((tlinfo.PERMISSION_DESC = X_PERMISSION_DESC)
162 OR ((tlinfo.PERMISSION_DESC is null) AND (X_PERMISSION_DESC is null)))
163 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
164 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
165 ) then
166 null;
167 else
168 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
169 app_exception.raise_exception;
170 end if;
171 end if;
172 end loop;
173 return;
174 end LOCK_ROW;
175
176 procedure UPDATE_ROW (
177 X_JTF_AUTH_PERMISSION_ID in NUMBER,
178 X_PERMISSION_DESC_ID in NUMBER,
179 X_OBJECT_VERSION_NUMBER in NUMBER,
180 X_PERMISSION_NAME in VARCHAR2,
181 X_APPLICATION_ID in NUMBER,
182 X_PERMISSION_CLASS_NAME in VARCHAR2,
183 X_OWNERABLE_FLAG in NUMBER,
184 X_IS_DAC_FLAG in NUMBER,
185 X_MAINTENANCE_MODE_FLAG in NUMBER,
186 X_PERMISSION_DESC in VARCHAR2,
187 X_DESCRIPTION in VARCHAR2,
188 X_LAST_UPDATE_DATE in DATE,
189 X_LAST_UPDATED_BY in NUMBER,
190 X_LAST_UPDATE_LOGIN in NUMBER
191 ) is
192 begin
193 update JTF_AUTH_PERMISSIONS_B set
194 PERMISSION_DESC_ID = X_PERMISSION_DESC_ID,
195 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
196 PERMISSION_NAME = X_PERMISSION_NAME,
197 APPLICATION_ID = X_APPLICATION_ID,
198 PERMISSION_CLASS_NAME = X_PERMISSION_CLASS_NAME,
199 OWNERABLE_FLAG = X_OWNERABLE_FLAG,
200 IS_DAC_FLAG = X_IS_DAC_FLAG,
201 MAINTENANCE_MODE_FLAG = X_MAINTENANCE_MODE_FLAG,
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 where JTF_AUTH_PERMISSION_ID = X_JTF_AUTH_PERMISSION_ID;
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210
211 update JTF_AUTH_PERMISSIONS_TL set
212 PERMISSION_DESC = X_PERMISSION_DESC,
213 DESCRIPTION = X_DESCRIPTION,
214 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
215 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
216 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
217 SOURCE_LANG = userenv('LANG')
218 where PERMISSION_DESC_ID = X_JTF_AUTH_PERMISSION_ID
219 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224 end UPDATE_ROW;
225
226 procedure DELETE_ROW (
227 X_JTF_AUTH_PERMISSION_ID in NUMBER
228 ) is
229 begin
230 delete from JTF_AUTH_PERMISSIONS_TL
231 where PERMISSION_DESC_ID = X_JTF_AUTH_PERMISSION_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236
237 delete from JTF_AUTH_PERMISSIONS_B
238 where JTF_AUTH_PERMISSION_ID = X_JTF_AUTH_PERMISSION_ID;
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243 end DELETE_ROW;
244
245 procedure ADD_LANGUAGE
246 is
247 begin
248 delete from JTF_AUTH_PERMISSIONS_TL T
249 where not exists
250 (select NULL
251 from JTF_AUTH_PERMISSIONS_B B
252 where B.JTF_AUTH_PERMISSION_ID = T.PERMISSION_DESC_ID
253 );
254
255 update JTF_AUTH_PERMISSIONS_TL T set (
256 PERMISSION_DESC,
257 DESCRIPTION
258 ) = (select
259 B.PERMISSION_DESC,
260 B.DESCRIPTION
261 from JTF_AUTH_PERMISSIONS_TL B
262 where B.PERMISSION_DESC_ID = T.PERMISSION_DESC_ID
263 and B.LANGUAGE = T.SOURCE_LANG)
264 where (
265 T.PERMISSION_DESC_ID,
266 T.LANGUAGE
267 ) in (select
268 SUBT.PERMISSION_DESC_ID,
269 SUBT.LANGUAGE
270 from JTF_AUTH_PERMISSIONS_TL SUBB, JTF_AUTH_PERMISSIONS_TL SUBT
271 where SUBB.PERMISSION_DESC_ID = SUBT.PERMISSION_DESC_ID
272 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
273 and (SUBB.PERMISSION_DESC <> SUBT.PERMISSION_DESC
274 or (SUBB.PERMISSION_DESC is null and SUBT.PERMISSION_DESC is not null)
275 or (SUBB.PERMISSION_DESC is not null and SUBT.PERMISSION_DESC is null)
276 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
277 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
278 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
279 ));
280
281 insert into JTF_AUTH_PERMISSIONS_TL (
282 PERMISSION_DESC_ID,
283 PERMISSION_DESC,
284 DESCRIPTION,
285 CREATION_DATE,
286 CREATED_BY,
287 LAST_UPDATE_DATE,
288 LAST_UPDATED_BY,
289 LAST_UPDATE_LOGIN,
290 LANGUAGE,
291 SOURCE_LANG
292 ) select
293 B.PERMISSION_DESC_ID,
294 B.PERMISSION_DESC,
295 B.DESCRIPTION,
296 B.CREATION_DATE,
297 B.CREATED_BY,
298 B.LAST_UPDATE_DATE,
299 B.LAST_UPDATED_BY,
300 B.LAST_UPDATE_LOGIN,
301 L.LANGUAGE_CODE,
302 B.SOURCE_LANG
303 from JTF_AUTH_PERMISSIONS_TL B, FND_LANGUAGES L
304 where L.INSTALLED_FLAG in ('I', 'B')
305 and B.LANGUAGE = userenv('LANG')
306 and not exists
307 (select NULL
308 from JTF_AUTH_PERMISSIONS_TL T
309 where T.PERMISSION_DESC_ID = B.PERMISSION_DESC_ID
310 and T.LANGUAGE = L.LANGUAGE_CODE);
311 end ADD_LANGUAGE;
312
313 -- NEW DEVELOPER ADDED PROCEDURES
314
315 procedure TRANSLATE_ROW (
316 X_JTF_AUTH_PERMISSION_ID in NUMBER, -- key field
317 X_PERMISSION_DESC in VARCHAR2, -- translated field
318 X_DESCRIPTION in VARCHAR2, -- translated field
319 X_OWNER in VARCHAR2 -- owner fields
320 ) is
321
322 begin
323 update JTF_AUTH_PERMISSIONS_TL set
324 PERMISSION_DESC = x_PERMISSION_DESC,
325 DESCRIPTION = x_DESCRIPTION,
326 LAST_UPDATE_DATE = sysdate,
327 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
328 LAST_UPDATE_LOGIN = 0,
329 SOURCE_LANG = userenv('LANG')
330 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
331 and PERMISSION_DESC_ID = X_JTF_AUTH_PERMISSION_ID;
332 end TRANSLATE_ROW;
333
334
335 procedure LOAD_ROW (
336 X_JTF_AUTH_PERMISSION_ID in NUMBER, -- key fields
337 X_PERMISSION_DESC_ID in NUMBER, -- data fields
338 X_OBJECT_VERSION_NUMBER in NUMBER, -- data fields
339 X_PERMISSION_NAME in VARCHAR2, -- data fields
340 X_APPLICATION_ID in NUMBER, -- data fields
341 X_PERMISSION_CLASS_NAME in VARCHAR2, -- data fields
342 X_OWNERABLE_FLAG in NUMBER, -- data fields
343 X_IS_DAC_FLAG in NUMBER, -- data fields
344 X_MAINTENANCE_MODE_FLAG in NUMBER, -- data fields
345 X_PERMISSION_DESC in VARCHAR2,
346 X_DESCRIPTION in VARCHAR2,
347 X_OWNER in VARCHAR2 -- owner fields
348 ) is
349
350 l_rowid VARCHAR2(64);
351 l_user_id NUMBER := 0;
352
353 begin
354 if(x_owner = 'SEED') then
355 l_user_id := 1;
356 end if;
357
358 -- Update row if present
359 JTF_AUTH_PERMISSIONS_PKG.UPDATE_ROW (
360 X_JTF_AUTH_PERMISSION_ID => X_JTF_AUTH_PERMISSION_ID,
361 X_PERMISSION_DESC_ID => X_PERMISSION_DESC_ID,
362 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
363 X_PERMISSION_NAME => X_PERMISSION_NAME,
364 X_APPLICATION_ID => X_APPLICATION_ID,
365 X_PERMISSION_CLASS_NAME => X_PERMISSION_CLASS_NAME,
366 X_OWNERABLE_FLAG => X_OWNERABLE_FLAG,
367 X_IS_DAC_FLAG => X_IS_DAC_FLAG,
368 X_MAINTENANCE_MODE_FLAG => X_MAINTENANCE_MODE_FLAG,
369 X_PERMISSION_DESC => X_PERMISSION_DESC,
370 X_DESCRIPTION => X_DESCRIPTION,
371 X_LAST_UPDATE_DATE => sysdate,
372 X_LAST_UPDATED_BY => l_user_id,
373 X_LAST_UPDATE_LOGIN => 0 );
374 exception
375 when NO_DATA_FOUND then
376 -- Insert a row
377 JTF_AUTH_PERMISSIONS_PKG.INSERT_ROW (
378 -- X_ROWID => l_rowid,
379 X_JTF_AUTH_PERMISSION_ID => X_JTF_AUTH_PERMISSION_ID,
380 X_PERMISSION_DESC_ID => X_PERMISSION_DESC_ID,
381 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
382 X_PERMISSION_NAME => X_PERMISSION_NAME,
383 X_APPLICATION_ID => X_APPLICATION_ID,
384 X_PERMISSION_CLASS_NAME => X_PERMISSION_CLASS_NAME,
385 X_OWNERABLE_FLAG => X_OWNERABLE_FLAG,
386 X_IS_DAC_FLAG => X_IS_DAC_FLAG,
387 X_MAINTENANCE_MODE_FLAG => X_MAINTENANCE_MODE_FLAG,
388 X_PERMISSION_DESC => X_PERMISSION_DESC,
389 X_DESCRIPTION => X_DESCRIPTION,
390 X_CREATION_DATE => sysdate,
391 X_CREATED_BY => l_user_id,
392 X_LAST_UPDATE_DATE => sysdate,
393 X_LAST_UPDATED_BY => l_user_id,
394 X_LAST_UPDATE_LOGIN => 0 );
395
396 end LOAD_ROW;
397
398 end JTF_AUTH_PERMISSIONS_PKG;