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