1 package body FND_SECURITY_GROUPS_PKG as
2 /* $Header: AFSCGRPB.pls 120.2 2006/02/13 01:55:15 stadepal ship $ */
3
4
5 -- Overloaded. This is the obsolete old version.
6 procedure LOAD_ROW (
7 X_SECURITY_GROUP_KEY in VARCHAR2,
8 X_OWNER in VARCHAR2,
9 X_SECURITY_GROUP_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2) is
11 begin
12
13 fnd_security_groups_pkg.LOAD_ROW (
14 X_SECURITY_GROUP_KEY => X_SECURITY_GROUP_KEY,
15 X_OWNER => X_OWNER,
16 X_SECURITY_GROUP_NAME => X_SECURITY_GROUP_NAME,
17 X_DESCRIPTION => X_DESCRIPTION,
18 x_custom_mode => '',
19 x_last_update_date => '');
20
21 end LOAD_ROW;
22
23 -- This is the overloaded version to use in new code.
24 procedure LOAD_ROW (
25 X_SECURITY_GROUP_KEY in VARCHAR2,
26 X_OWNER in VARCHAR2,
27 X_SECURITY_GROUP_NAME in VARCHAR2,
28 X_DESCRIPTION in VARCHAR2,
29 x_custom_mode in varchar2,
30 x_last_update_date in varchar2) is
31
32 sgroup_id number;
33 row_id varchar2(64);
34 f_luby number; -- entity owner in file
35 f_ludate date; -- entity update date in file
36 db_luby number; -- entity owner in db
37 db_ludate date; -- entity update date in db
38
39 begin
40 -- Translate owner to file_last_updated_by
41 f_luby := fnd_load_util.owner_id(x_owner);
42
43 -- Translate char last_update_date to date
44 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
45
46 begin
47 select security_group_id,LAST_UPDATED_BY, LAST_UPDATE_DATE
48 into sgroup_id, db_luby, db_ludate
49 from fnd_security_groups
50 where security_group_key = X_SECURITY_GROUP_KEY;
51
52 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
53 db_ludate, X_CUSTOM_MODE)) then
54 fnd_security_groups_pkg.UPDATE_ROW (
55 X_SECURITY_GROUP_ID => sgroup_id,
56 X_SECURITY_GROUP_KEY => X_SECURITY_GROUP_KEY,
57 X_SECURITY_GROUP_NAME => X_SECURITY_GROUP_NAME,
58 X_DESCRIPTION => X_DESCRIPTION,
59 X_LAST_UPDATE_DATE => f_ludate,
60 X_LAST_UPDATED_BY => f_luby,
61 X_LAST_UPDATE_LOGIN => 0 );
62 end if;
63 exception
64 when NO_DATA_FOUND then
65
66 select fnd_security_groups_s.nextval into sgroup_id from dual;
67
68 fnd_security_groups_pkg.INSERT_ROW (
69 X_ROWID => row_id,
70 X_SECURITY_GROUP_ID => sgroup_id,
71 X_SECURITY_GROUP_KEY => X_SECURITY_GROUP_KEY,
72 X_SECURITY_GROUP_NAME => X_SECURITY_GROUP_NAME,
73 X_DESCRIPTION => X_DESCRIPTION,
74 X_CREATION_DATE => f_ludate,
75 X_CREATED_BY => f_luby,
76 X_LAST_UPDATE_DATE => f_ludate,
77 X_LAST_UPDATED_BY => f_luby,
78 X_LAST_UPDATE_LOGIN => 0 );
79 end;
80 end LOAD_ROW;
81
82 -- OVERLOADED! This is the obsolete version for backward compatibility.
83 procedure TRANSLATE_ROW (
84 X_SECURITY_GROUP_KEY in VARCHAR2,
85 X_OWNER in VARCHAR2,
86 X_SECURITY_GROUP_NAME in VARCHAR2,
87 X_DESCRIPTION in VARCHAR2) is
88 begin
89
90 FND_SECURITY_GROUPS_PKG.translate_row(
91 x_security_group_key => x_security_group_key,
92 x_owner => x_owner,
93 x_security_group_name => x_security_group_name,
94 x_description => x_description,
95 x_custom_mode => '',
96 x_last_update_date => '');
97
98 end TRANSLATE_ROW;
99
100 -- OVERLOADED! This is the version to use in new code.
101 procedure TRANSLATE_ROW (
102 X_SECURITY_GROUP_KEY in VARCHAR2,
103 X_OWNER in VARCHAR2,
104 X_SECURITY_GROUP_NAME in VARCHAR2,
105 X_DESCRIPTION in VARCHAR2,
106 X_CUSTOM_MODE in VARCHAR2,
107 X_LAST_UPDATE_DATE in VARCHAR2) is
108
109 f_luby number; -- entity owner in file
110 f_ludate date; -- entity update date in file
111 db_luby number; -- entity owner in db
112 db_ludate date; -- entity update date in db
113
114 begin
115
116 -- Translate owner to file_last_updated_by
117 f_luby := fnd_load_util.owner_id(x_owner);
118
119 -- Translate char last_update_date to date
120 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
121
122 begin
123 select LAST_UPDATED_BY, LAST_UPDATE_DATE
124 into db_luby, db_ludate
125 from fnd_security_groups_tl
126 where security_group_id = (select security_group_id
127 from fnd_security_groups
128 where security_group_key = X_SECURITY_GROUP_KEY)
129 and LANGUAGE = userenv('LANG');
130
131 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
132 db_ludate, X_CUSTOM_MODE)) then
133 update fnd_security_groups_tl set
134 security_group_name = nvl(X_SECURITY_GROUP_NAME, security_group_name),
135 description = nvl(X_DESCRIPTION, description),
136 source_lang = userenv('LANG'),
137 last_update_date = f_ludate,
138 last_updated_by = f_luby,
139 last_update_login = 0
140 where security_group_id = (select security_group_id
141 from fnd_security_groups
142 where security_group_key = X_SECURITY_GROUP_KEY)
143 and userenv('LANG') in (language, source_lang);
144 end if;
145 exception
146 when no_data_found then
147 null;
148 end;
149
150 end TRANSLATE_ROW;
151
152 procedure INSERT_ROW (
153 X_ROWID in out nocopy VARCHAR2,
154 X_SECURITY_GROUP_ID in NUMBER,
155 X_SECURITY_GROUP_KEY in VARCHAR2,
156 X_SECURITY_GROUP_NAME in VARCHAR2,
157 X_DESCRIPTION in VARCHAR2,
158 X_CREATION_DATE in DATE,
159 X_CREATED_BY in NUMBER,
160 X_LAST_UPDATE_DATE in DATE,
161 X_LAST_UPDATED_BY in NUMBER,
162 X_LAST_UPDATE_LOGIN in NUMBER
163 ) is
164 cursor C is select ROWID from FND_SECURITY_GROUPS
165 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
166 ;
167 begin
168 insert into FND_SECURITY_GROUPS (
169 SECURITY_GROUP_ID,
170 SECURITY_GROUP_KEY,
171 CREATION_DATE,
172 CREATED_BY,
173 LAST_UPDATE_DATE,
174 LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN
176 ) values (
177 X_SECURITY_GROUP_ID,
178 X_SECURITY_GROUP_KEY,
179 X_CREATION_DATE,
180 X_CREATED_BY,
181 X_LAST_UPDATE_DATE,
182 X_LAST_UPDATED_BY,
183 X_LAST_UPDATE_LOGIN
184 );
185
186 -- Added for Function Security Cache Invalidation Project
187 fnd_function_security_cache.insert_secgrp(X_SECURITY_GROUP_ID);
188
189 insert into FND_SECURITY_GROUPS_TL (
190 SECURITY_GROUP_ID,
191 SECURITY_GROUP_NAME,
192 DESCRIPTION,
193 CREATED_BY,
194 CREATION_DATE,
195 LAST_UPDATED_BY,
196 LAST_UPDATE_DATE,
197 LAST_UPDATE_LOGIN,
198 LANGUAGE,
199 SOURCE_LANG
200 ) select
201 X_SECURITY_GROUP_ID,
202 X_SECURITY_GROUP_NAME,
203 X_DESCRIPTION,
204 X_CREATED_BY,
205 X_CREATION_DATE,
206 X_LAST_UPDATED_BY,
207 X_LAST_UPDATE_DATE,
208 X_LAST_UPDATE_LOGIN,
209 L.LANGUAGE_CODE,
210 userenv('LANG')
211 from FND_LANGUAGES L
212 where L.INSTALLED_FLAG in ('I', 'B')
213 and not exists
214 (select NULL
215 from FND_SECURITY_GROUPS_TL T
216 where T.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
217 and T.LANGUAGE = L.LANGUAGE_CODE);
218
219 -- Bug3813798 Moved this call to happen after the translation table
220 -- is updated to resolve the problem where the Security group key
221 -- was being used in the display name for the role instead of the
222 -- security group name when the security group is initially created.
223
224 fnd_user_resp_groups_api.sync_roles_all_resps(X_SECURITY_GROUP_ID,
225 X_SECURITY_GROUP_KEY);
226
227 open c;
228 fetch c into X_ROWID;
229 if (c%notfound) then
230 close c;
231 raise no_data_found;
232 end if;
233 close c;
234
235 end INSERT_ROW;
236
237 procedure LOCK_ROW (
238 X_SECURITY_GROUP_ID in NUMBER,
239 X_SECURITY_GROUP_KEY in VARCHAR2,
240 X_SECURITY_GROUP_NAME in VARCHAR2,
241 X_DESCRIPTION in VARCHAR2
242 ) is
243 cursor c is select
244 SECURITY_GROUP_KEY
245 from FND_SECURITY_GROUPS
246 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
247 for update of SECURITY_GROUP_ID nowait;
248 recinfo c%rowtype;
249
250 cursor c1 is select
251 SECURITY_GROUP_NAME,
252 DESCRIPTION,
253 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
254 from FND_SECURITY_GROUPS_TL
255 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
256 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
257 for update of SECURITY_GROUP_ID nowait;
258 begin
259 open c;
260 fetch c into recinfo;
261 if (c%notfound) then
262 close c;
263 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
264 app_exception.raise_exception;
265 end if;
266 close c;
267 if ( (recinfo.SECURITY_GROUP_KEY = X_SECURITY_GROUP_KEY)
268 ) then
269 null;
270 else
271 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
272 app_exception.raise_exception;
273 end if;
274
275 for tlinfo in c1 loop
276 if (tlinfo.BASELANG = 'Y') then
277 if ( (tlinfo.SECURITY_GROUP_NAME = X_SECURITY_GROUP_NAME)
278 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
279 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
280 ) then
281 null;
282 else
283 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
284 app_exception.raise_exception;
285 end if;
286 end if;
287 end loop;
288 return;
289 end LOCK_ROW;
290
291 procedure UPDATE_ROW (
292 X_SECURITY_GROUP_ID in NUMBER,
293 X_SECURITY_GROUP_KEY in VARCHAR2,
294 X_SECURITY_GROUP_NAME in VARCHAR2,
295 X_DESCRIPTION in VARCHAR2,
296 X_LAST_UPDATE_DATE in DATE,
297 X_LAST_UPDATED_BY in NUMBER,
298 X_LAST_UPDATE_LOGIN in NUMBER
299 ) is
300 p_security_group_name VARCHAR2(80);
301 begin
302 begin
303 -- Get the old Security_Group_Name from the d/b for the current session
304 select SECURITY_GROUP_NAME
305 into p_security_group_name
306 from FND_SECURITY_GROUPS_TL
307 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
308 and LANGUAGE = userenv('LANG');
309
310 exception
311 when no_data_found then
312 raise no_data_found;
313 end;
314
315 update FND_SECURITY_GROUPS set
316 SECURITY_GROUP_KEY = X_SECURITY_GROUP_KEY,
317 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
318 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
319 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
320 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID;
321
322 if (sql%notfound) then
323 raise no_data_found;
324 else
325 -- This means that a security group was updated.
326
327 -- Added for Function Security Cache Invalidation Project
328 fnd_function_security_cache.update_secgrp(X_SECURITY_GROUP_ID);
329
330 end if;
331
332 update FND_SECURITY_GROUPS_TL set
333 SECURITY_GROUP_NAME = X_SECURITY_GROUP_NAME,
334 DESCRIPTION = X_DESCRIPTION,
335 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
336 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
337 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
338 SOURCE_LANG = userenv('LANG')
339 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
340 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
341
342 if (sql%notfound) then
343 raise no_data_found;
344 else
345 -- This means that a security group translation was updated.
346
347 -- Bug3813798 Moved call so that the correct display name is
348 -- created for the roles defined for this security group.
349
350 -- Bug 4943583. The following check prevents the expensive api
351 -- 'fnd_user_resp_groups_api.sync_roles_all_resps' from being called when
352 -- columns other than 'SECURITY_GROUP_NAME' (which is used in WF role
353 -- DISPLAY_NAME) are modified. This api needs to be called only when the
354 -- SECURITY_GROUP_NAME is modified.
355 -- This change is to improve the performance.
356 -- NOTE: SECURITY_GROUP_KEY can never be updated either through forms or ldt
357 -- 2) WF role DISPLAY_NAME contains the SECURITY_GROUP_NAME only if
358 -- the SECURITY_GROUP_KEY <> 'STANDARD'. When SECURITY_GROUP_NAME is
359 -- 'STANDARD', then the DISPLAY_NAME is just the Responsibility_Name.
360 -- Hence the below call to update the DISPLAY_NAME is not required
361 -- when SECURITY_GROUP_KEY is STANDARD.
362
363 if ((X_SECURITY_GROUP_KEY <> 'STANDARD') and
364 (X_SECURITY_GROUP_NAME <> p_security_group_name)) then
365 -- Call this api only if SECURITY_GROUP_KEY is not 'STANDARD'
366 -- and there is a change in SECURITY_GROUP_NAME
367 fnd_user_resp_groups_api.sync_roles_all_resps(X_SECURITY_GROUP_ID,
368 X_SECURITY_GROUP_KEY);
369 end if;
370 end if;
371 end UPDATE_ROW;
372
373 procedure DELETE_ROW (
374 X_SECURITY_GROUP_ID in NUMBER
375 ) is
376 begin
377 delete from FND_SECURITY_GROUPS_TL
378 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID;
379
380 if (sql%notfound) then
381 raise no_data_found;
382 end if;
383
384 delete from FND_SECURITY_GROUPS
385 where SECURITY_GROUP_ID = X_SECURITY_GROUP_ID;
386
387 if (sql%notfound) then
388 raise no_data_found;
389 else
390 -- This means that a security group was deleted.
391
392 -- Added for Function Security Cache Invalidation Project
393 fnd_function_security_cache.delete_secgrp(X_SECURITY_GROUP_ID);
394 end if;
395 end DELETE_ROW;
396
397 procedure ADD_LANGUAGE
398 is
399 begin
400 /* Mar/19/03 requested by Ric Ginsberg */
401 /* The following delete and update statements are commented out */
402 /* as a quick workaround to fix the time-consuming table handler issue */
403 /* Eventually we'll need to turn them into a separate fix_language procedure */
404 /*
405
406 delete from FND_SECURITY_GROUPS_TL T
407 where not exists
408 (select NULL
409 from FND_SECURITY_GROUPS B
410 where B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
411 );
412
413 update FND_SECURITY_GROUPS_TL T set (
414 SECURITY_GROUP_NAME,
415 DESCRIPTION
416 ) = (select
417 B.SECURITY_GROUP_NAME,
418 B.DESCRIPTION
419 from FND_SECURITY_GROUPS_TL B
420 where B.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID
421 and B.LANGUAGE = T.SOURCE_LANG)
422 where (
423 T.SECURITY_GROUP_ID,
424 T.LANGUAGE
425 ) in (select
426 SUBT.SECURITY_GROUP_ID,
427 SUBT.LANGUAGE
428 from FND_SECURITY_GROUPS_TL SUBB, FND_SECURITY_GROUPS_TL SUBT
429 where SUBB.SECURITY_GROUP_ID = SUBT.SECURITY_GROUP_ID
430 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
431 and (SUBB.SECURITY_GROUP_NAME <> SUBT.SECURITY_GROUP_NAME
432 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
433 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
434 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
435 ));
436 */
437
438 insert into FND_SECURITY_GROUPS_TL (
439 SECURITY_GROUP_ID,
440 SECURITY_GROUP_NAME,
441 DESCRIPTION,
442 CREATED_BY,
443 CREATION_DATE,
444 LAST_UPDATED_BY,
445 LAST_UPDATE_DATE,
446 LAST_UPDATE_LOGIN,
447 LANGUAGE,
448 SOURCE_LANG
449 ) select
450 B.SECURITY_GROUP_ID,
451 B.SECURITY_GROUP_NAME,
452 B.DESCRIPTION,
453 B.CREATED_BY,
454 B.CREATION_DATE,
455 B.LAST_UPDATED_BY,
456 B.LAST_UPDATE_DATE,
457 B.LAST_UPDATE_LOGIN,
458 L.LANGUAGE_CODE,
459 B.SOURCE_LANG
460 from FND_SECURITY_GROUPS_TL B, FND_LANGUAGES L
461 where L.INSTALLED_FLAG in ('I', 'B')
462 and B.LANGUAGE = userenv('LANG')
463 and not exists
464 (select NULL
465 from FND_SECURITY_GROUPS_TL T
466 where T.SECURITY_GROUP_ID = B.SECURITY_GROUP_ID
467 and T.LANGUAGE = L.LANGUAGE_CODE);
468 end ADD_LANGUAGE;
469
470 end FND_SECURITY_GROUPS_PKG;
[Home] [Help]