[Home] [Help]
PACKAGE BODY: APPS.FND_RESPONSIBILITY_PKG
Source
1 package body FND_RESPONSIBILITY_PKG as
2 /* $Header: AFSCRSPB.pls 120.4.12010000.4 2010/03/23 18:42:34 jvalenti ship $ */
3
4
5 procedure INSERT_ROW (
6 X_ROWID in out nocopy VARCHAR2,
7 X_RESPONSIBILITY_ID in NUMBER,
8 X_APPLICATION_ID in NUMBER,
9 X_WEB_HOST_NAME in VARCHAR2,
10 X_WEB_AGENT_NAME in VARCHAR2,
11 X_DATA_GROUP_APPLICATION_ID in NUMBER,
12 X_DATA_GROUP_ID in NUMBER,
13 X_MENU_ID in NUMBER,
14 X_START_DATE in DATE,
15 X_END_DATE in DATE,
16 X_GROUP_APPLICATION_ID in NUMBER,
17 X_REQUEST_GROUP_ID in NUMBER,
18 X_VERSION in VARCHAR2,
19 X_RESPONSIBILITY_KEY in VARCHAR2,
20 X_RESPONSIBILITY_NAME in VARCHAR2,
21 X_DESCRIPTION in VARCHAR2,
22 X_CREATION_DATE in DATE,
23 X_CREATED_BY in NUMBER,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATED_BY in NUMBER,
26 X_LAST_UPDATE_LOGIN in NUMBER
27 ) is
28 cursor C is select ROWID from FND_RESPONSIBILITY
29 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
30 and APPLICATION_ID = X_APPLICATION_ID
31 ;
32 begin
33 insert into FND_RESPONSIBILITY (
34 WEB_HOST_NAME,
35 WEB_AGENT_NAME,
36 APPLICATION_ID,
37 RESPONSIBILITY_ID,
38 DATA_GROUP_APPLICATION_ID,
39 DATA_GROUP_ID,
40 MENU_ID,
41 START_DATE,
42 END_DATE,
43 GROUP_APPLICATION_ID,
44 REQUEST_GROUP_ID,
45 VERSION,
46 RESPONSIBILITY_KEY,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN
52 ) values (
53 X_WEB_HOST_NAME,
54 X_WEB_AGENT_NAME,
55 X_APPLICATION_ID,
56 X_RESPONSIBILITY_ID,
57 X_DATA_GROUP_APPLICATION_ID,
58 X_DATA_GROUP_ID,
59 X_MENU_ID,
60 X_START_DATE,
61 X_END_DATE,
62 X_GROUP_APPLICATION_ID,
63 X_REQUEST_GROUP_ID,
64 X_VERSION,
65 X_RESPONSIBILITY_KEY,
66 X_CREATION_DATE,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN
71 );
72
73 -- Added for Function Security Cache Invalidation Project
74 fnd_function_security_cache.insert_resp(X_RESPONSIBILITY_ID, X_APPLICATION_ID);
75
76 insert into FND_RESPONSIBILITY_TL (
77 APPLICATION_ID,
78 RESPONSIBILITY_ID,
79 RESPONSIBILITY_NAME,
80 DESCRIPTION,
81 CREATED_BY,
82 CREATION_DATE,
83 LAST_UPDATED_BY,
84 LAST_UPDATE_DATE,
85 LAST_UPDATE_LOGIN,
86 LANGUAGE,
87 SOURCE_LANG
88 ) select
89 X_APPLICATION_ID,
90 X_RESPONSIBILITY_ID,
91 X_RESPONSIBILITY_NAME,
92 X_DESCRIPTION,
93 X_CREATED_BY,
94 X_CREATION_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATE_LOGIN,
98 L.LANGUAGE_CODE,
99 userenv('LANG')
100 from FND_LANGUAGES L
101 where L.INSTALLED_FLAG in ('I', 'B')
102 and not exists
103 (select NULL
104 from FND_RESPONSIBILITY_TL T
105 where T.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
106 and T.APPLICATION_ID = X_APPLICATION_ID
107 and T.LANGUAGE = L.LANGUAGE_CODE);
108
109
110 -- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
111 fnd_user_resp_groups_api.sync_roles_all_secgrps(
112 X_RESPONSIBILITY_ID,
113 X_APPLICATION_ID,
114 X_RESPONSIBILITY_KEY,
115 X_START_DATE,
116 X_END_DATE);
117
118 open c;
119 fetch c into X_ROWID;
120 if (c%notfound) then
121 close c;
122 raise no_data_found;
123 end if;
124 close c;
125
126
127 end INSERT_ROW;
128
129 --Overloaded!
130
131 procedure TRANSLATE_ROW (
132 X_APP_SHORT_NAME in VARCHAR2,
133 X_RESP_KEY in VARCHAR2,
134 X_RESPONSIBILITY_NAME in VARCHAR2,
135 X_DESCRIPTION in VARCHAR2,
136 X_OWNER in VARCHAR2) is
137 appl_id number;
138 resp_id number;
139 begin
140
141 fnd_responsibility_pkg.translate_row(
142 X_APP_SHORT_NAME => X_APP_SHORT_NAME,
143 X_RESP_KEY => X_RESP_KEY,
144 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
145 X_DESCRIPTION => X_DESCRIPTION,
146 X_OWNER => X_OWNER,
147 x_custom_mode => null,
148 x_last_update_date => null);
149
150 end TRANSLATE_ROW;
151
152 -- ### OVERLOADED!
153 procedure TRANSLATE_ROW (
154 X_APP_SHORT_NAME in VARCHAR2,
155 X_RESP_KEY in VARCHAR2,
156 X_RESPONSIBILITY_NAME in VARCHAR2,
157 X_DESCRIPTION in VARCHAR2,
158 X_OWNER in VARCHAR2,
159 X_CUSTOM_MODE in VARCHAR2,
160 X_LAST_UPDATE_DATE in VARCHAR2) is
161
162 appl_id number;
163 resp_id number;
164 f_luby number; -- entity owner in file
165 f_ludate date; -- entity update date in file
166 db_luby number; -- entity owner in db
167 db_ludate date; -- entity update date in db
168
169 begin
170 -- Translate owner to file_last_updated_by
171 f_luby := fnd_load_util.owner_id(x_owner);
172
173 -- Translate char last_update_date to date
174 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
175
176 begin
177 select LAST_UPDATED_BY, LAST_UPDATE_DATE
178 into db_luby, db_ludate
179 from fnd_responsibility_tl
180 where (RESPONSIBILITY_ID, APPLICATION_ID)
181 = (select r.responsibility_id, r.application_id
182 from fnd_responsibility r, fnd_application a
183 where r.responsibility_key = X_RESP_KEY
184 and r.application_id = a.application_id
185 and a.application_short_name = X_APP_SHORT_NAME)
186 and LANGUAGE = userenv('LANG');
187
188 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
189 db_ludate, X_CUSTOM_MODE)) then
190 update fnd_responsibility_tl set
191 responsibility_name = nvl(X_RESPONSIBILITY_NAME, responsibility_name),
192 DESCRIPTION = nvl(X_DESCRIPTION, description),
193 LAST_UPDATE_DATE = f_ludate,
194 LAST_UPDATED_BY = f_luby,
195 LAST_UPDATE_LOGIN = 0,
196 SOURCE_LANG = userenv('LANG')
197 where (RESPONSIBILITY_ID, APPLICATION_ID)
198 = (select r.responsibility_id, r.application_id
199 from fnd_responsibility r, fnd_application a
200 where r.responsibility_key = X_RESP_KEY
201 and r.application_id = a.application_id
202 and a.application_short_name = X_APP_SHORT_NAME)
203 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
204
205 -- Sync with WF --
206 select application_id into appl_id
207 from fnd_application
208 where application_short_name = X_APP_SHORT_NAME;
209
210 select responsibility_id into resp_id
211 from fnd_responsibility
212 where responsibility_key = X_RESP_KEY
213 and application_id = appl_id;
214
215 fnd_responsibility_pkg.resp_synch(appl_id, resp_id);
216 end if;
217 exception
218 when no_data_found then
219 null;
220 end;
221 end TRANSLATE_ROW;
222
223
224 --Overloaded!!
225
226 procedure LOAD_ROW (
227 X_APP_SHORT_NAME in VARCHAR2,
228 X_RESP_KEY in VARCHAR2,
229 X_RESPONSIBILITY_ID in VARCHAR2,
230 X_RESPONSIBILITY_NAME in VARCHAR2,
231 X_OWNER in VARCHAR2,
232 X_DATA_GROUP_APP_SHORT_NAME in VARCHAR2,
233 X_DATA_GROUP_NAME in VARCHAR2,
234 X_MENU_NAME in VARCHAR2,
235 X_START_DATE in VARCHAR2,
236 X_END_DATE in VARCHAR2,
237 X_DESCRIPTION in VARCHAR2,
238 X_GROUP_APP_SHORT_NAME in VARCHAR2,
239 X_REQUEST_GROUP_NAME in VARCHAR2,
240 X_VERSION in VARCHAR2,
241 X_WEB_HOST_NAME in VARCHAR2,
242 X_WEB_AGENT_NAME in VARCHAR2 )
243 is
244 begin
245 fnd_responsibility_pkg.load_row(
246 X_APP_SHORT_NAME => X_APP_SHORT_NAME,
247 X_RESP_KEY => upper(X_RESP_KEY),
248 X_RESPONSIBILITY_ID => X_RESPONSIBILITY_ID,
249 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
250 X_OWNER => X_OWNER,
251 X_DATA_GROUP_APP_SHORT_NAME => X_DATA_GROUP_APP_SHORT_NAME,
252 X_DATA_GROUP_NAME => X_DATA_GROUP_NAME,
253 X_MENU_NAME => X_MENU_NAME,
254 X_START_DATE => X_START_DATE,
255 X_END_DATE => X_END_DATE,
256 X_DESCRIPTION => X_DESCRIPTION,
257 X_GROUP_APP_SHORT_NAME => X_GROUP_APP_SHORT_NAME,
258 X_REQUEST_GROUP_NAME => X_REQUEST_GROUP_NAME,
259 X_VERSION => X_VERSION,
260 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
261 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
262 x_custom_mode => '',
263 x_last_update_date => '');
264
265 end LOAD_ROW;
266
267 --Overloaded!!
268
269 procedure LOAD_ROW (
270 X_APP_SHORT_NAME in VARCHAR2,
271 X_RESP_KEY in VARCHAR2,
272 X_RESPONSIBILITY_ID in VARCHAR2,
273 X_RESPONSIBILITY_NAME in VARCHAR2,
274 X_OWNER in VARCHAR2,
275 X_DATA_GROUP_APP_SHORT_NAME in VARCHAR2,
276 X_DATA_GROUP_NAME in VARCHAR2,
277 X_MENU_NAME in VARCHAR2,
278 X_START_DATE in VARCHAR2,
279 X_END_DATE in VARCHAR2,
280 X_DESCRIPTION in VARCHAR2,
281 X_GROUP_APP_SHORT_NAME in VARCHAR2,
282 X_REQUEST_GROUP_NAME in VARCHAR2,
283 X_VERSION in VARCHAR2,
284 X_WEB_HOST_NAME in VARCHAR2,
285 X_WEB_AGENT_NAME in VARCHAR2,
286 X_CUSTOM_MODE in VARCHAR2,
287 X_LAST_UPDATE_DATE in VARCHAR2 )
288 is
289 begin
290 fnd_responsibility_pkg.load_row(
291 X_APP_SHORT_NAME => X_APP_SHORT_NAME,
292 X_RESP_KEY => upper(X_RESP_KEY),
293 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
294 X_OWNER => X_OWNER,
295 X_DATA_GROUP_APP_SHORT_NAME => X_DATA_GROUP_APP_SHORT_NAME,
296 X_DATA_GROUP_NAME => X_DATA_GROUP_NAME,
297 X_MENU_NAME => X_MENU_NAME,
298 X_START_DATE => X_START_DATE,
299 X_END_DATE => X_END_DATE,
300 X_DESCRIPTION => X_DESCRIPTION,
301 X_GROUP_APP_SHORT_NAME => X_GROUP_APP_SHORT_NAME,
302 X_REQUEST_GROUP_NAME => X_REQUEST_GROUP_NAME,
303 X_VERSION => X_VERSION,
304 X_WEB_HOST_NAME => X_WEB_HOST_NAME,
305 X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
306 x_custom_mode => X_CUSTOM_MODE, -- bug 5425214
307 x_last_update_date => X_LAST_UPDATE_DATE); -- bug 5425214
308
309 end LOAD_ROW;
310
311
312 procedure LOCK_ROW (
313 X_RESPONSIBILITY_ID in NUMBER,
314 X_APPLICATION_ID in NUMBER,
315 X_WEB_HOST_NAME in VARCHAR2,
316 X_WEB_AGENT_NAME in VARCHAR2,
317 X_DATA_GROUP_APPLICATION_ID in NUMBER,
318 X_DATA_GROUP_ID in NUMBER,
319 X_MENU_ID in NUMBER,
320 X_START_DATE in DATE,
321 X_END_DATE in DATE,
322 X_GROUP_APPLICATION_ID in NUMBER,
323 X_REQUEST_GROUP_ID in NUMBER,
324 X_VERSION in VARCHAR2,
325 X_RESPONSIBILITY_KEY in VARCHAR2,
326 X_RESPONSIBILITY_NAME in VARCHAR2,
327 X_DESCRIPTION in VARCHAR2
328 ) is
329 cursor c is select
330 WEB_HOST_NAME,
331 WEB_AGENT_NAME,
332 DATA_GROUP_APPLICATION_ID,
333 DATA_GROUP_ID,
334 MENU_ID,
335 START_DATE,
336 END_DATE,
337 GROUP_APPLICATION_ID,
338 REQUEST_GROUP_ID,
339 VERSION,
340 RESPONSIBILITY_KEY
341 from FND_RESPONSIBILITY
342 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
343 and APPLICATION_ID = X_APPLICATION_ID
344 for update of RESPONSIBILITY_ID nowait;
345 recinfo c%rowtype;
346
347 cursor c1 is select
348 RESPONSIBILITY_NAME,
349 DESCRIPTION
350 from FND_RESPONSIBILITY_TL
351 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
352 and APPLICATION_ID = X_APPLICATION_ID
353 and LANGUAGE = userenv('LANG')
354 for update of RESPONSIBILITY_ID nowait;
355 tlinfo c1%rowtype;
356
357 begin
358 open c;
359 fetch c into recinfo;
360 if (c%notfound) then
361 close c;
362 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
363 app_exception.raise_exception;
364 end if;
365 close c;
366 if ( ((recinfo.WEB_HOST_NAME = X_WEB_HOST_NAME)
367 OR ((recinfo.WEB_HOST_NAME is null) AND (X_WEB_HOST_NAME is null)))
368 AND ((recinfo.WEB_AGENT_NAME = X_WEB_AGENT_NAME)
369 OR ((recinfo.WEB_AGENT_NAME is null) AND (X_WEB_AGENT_NAME is null)))
370 AND (recinfo.DATA_GROUP_APPLICATION_ID = X_DATA_GROUP_APPLICATION_ID)
371 AND (recinfo.DATA_GROUP_ID = X_DATA_GROUP_ID)
372 AND (recinfo.MENU_ID = X_MENU_ID)
373 AND (recinfo.START_DATE = X_START_DATE)
374 AND ((recinfo.END_DATE = X_END_DATE)
375 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
376 AND ((recinfo.GROUP_APPLICATION_ID = X_GROUP_APPLICATION_ID)
377 OR ((recinfo.GROUP_APPLICATION_ID is null) AND (X_GROUP_APPLICATION_ID is null)))
378 AND ((recinfo.REQUEST_GROUP_ID = X_REQUEST_GROUP_ID)
379 OR ((recinfo.REQUEST_GROUP_ID is null) AND (X_REQUEST_GROUP_ID is null)))
380 AND ((recinfo.VERSION = X_VERSION)
381 OR ((recinfo.VERSION is null) AND (X_VERSION is null)))
382 AND (recinfo.RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY)
383 ) then
384 null;
385 else
386 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
387 app_exception.raise_exception;
388 end if;
389
390 open c1;
391 fetch c1 into tlinfo;
392 if (c1%notfound) then
393 close c1;
394 return;
395 end if;
396 close c1;
397
398 if ( (tlinfo.RESPONSIBILITY_NAME = X_RESPONSIBILITY_NAME)
399 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
400 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
401 ) then
402 null;
403 else
404 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
405 app_exception.raise_exception;
406 end if;
407 return;
408 end LOCK_ROW;
409
410 procedure UPDATE_ROW (
411 X_RESPONSIBILITY_ID in NUMBER,
412 X_APPLICATION_ID in NUMBER,
413 X_WEB_HOST_NAME in VARCHAR2,
414 X_WEB_AGENT_NAME in VARCHAR2,
415 X_DATA_GROUP_APPLICATION_ID in NUMBER,
416 X_DATA_GROUP_ID in NUMBER,
417 X_MENU_ID in NUMBER,
418 X_START_DATE in DATE,
419 X_END_DATE in DATE,
420 X_GROUP_APPLICATION_ID in NUMBER,
421 X_REQUEST_GROUP_ID in NUMBER,
422 X_VERSION in VARCHAR2,
423 X_RESPONSIBILITY_KEY in VARCHAR2,
424 X_RESPONSIBILITY_NAME in VARCHAR2,
425 X_DESCRIPTION in VARCHAR2,
426 X_LAST_UPDATE_DATE in DATE,
427 X_LAST_UPDATED_BY in NUMBER,
428 X_LAST_UPDATE_LOGIN in NUMBER
429 ) is
430 begin
431 update FND_RESPONSIBILITY set
432 WEB_HOST_NAME = X_WEB_HOST_NAME,
433 WEB_AGENT_NAME = X_WEB_AGENT_NAME,
434 DATA_GROUP_APPLICATION_ID = X_DATA_GROUP_APPLICATION_ID,
435 DATA_GROUP_ID = X_DATA_GROUP_ID,
436 MENU_ID = X_MENU_ID,
437 START_DATE = X_START_DATE,
438 END_DATE = X_END_DATE,
439 GROUP_APPLICATION_ID = X_GROUP_APPLICATION_ID,
440 REQUEST_GROUP_ID = X_REQUEST_GROUP_ID,
441 VERSION = X_VERSION,
442 RESPONSIBILITY_KEY = X_RESPONSIBILITY_KEY,
443 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
444 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
445 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
446 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
447 and APPLICATION_ID = X_APPLICATION_ID;
448
449 if (sql%notfound) then
450 raise no_data_found;
451 else
452 -- Added for Function Security Cache Invalidation Project
453 fnd_function_security_cache.update_resp(X_RESPONSIBILITY_ID,
454 X_APPLICATION_ID);
455
456 end if;
457
458 update FND_RESPONSIBILITY_TL set
459 RESPONSIBILITY_NAME = X_RESPONSIBILITY_NAME,
460 DESCRIPTION = X_DESCRIPTION,
461 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
462 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
463 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
464 SOURCE_LANG = userenv('LANG')
465 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
466 and APPLICATION_ID = X_APPLICATION_ID
467 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
468
469 if (sql%notfound) then
470 raise no_data_found;
471 end if;
472
473 -- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
474 --
475 -- Bug9306729 - Moved call to sync roles to after the TL table is
476 -- updated to ensure that the responsibility_name is correctly
477 -- updated in the WF tables when a responsibility is uploaded with
478 -- FNDLOAD.
479 --
480 fnd_user_resp_groups_api.sync_roles_all_secgrps(
481 X_RESPONSIBILITY_ID,
482 X_APPLICATION_ID,
483 X_RESPONSIBILITY_KEY,
484 X_START_DATE,
485 X_END_DATE);
486 end UPDATE_ROW;
487
488 procedure DELETE_ROW (
489 X_RESPONSIBILITY_ID in NUMBER,
490 X_APPLICATION_ID in NUMBER
491 ) is
492 myList wf_parameter_list_t;
493 begin
494 delete from FND_RESPONSIBILITY
495 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
496 and APPLICATION_ID = X_APPLICATION_ID;
497
498 if (sql%notfound) then
499 raise no_data_found;
500 else
501 -- Added for Function Security Cache Invalidation Project
502 fnd_function_security_cache.delete_resp(X_RESPONSIBILITY_ID, X_APPLICATION_ID);
503
504 end if;
505
506 delete from FND_RESPONSIBILITY_TL
507 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
508 and APPLICATION_ID = X_APPLICATION_ID;
509
510 if (sql%notfound) then
511 raise no_data_found;
512 end if;
513
514 end DELETE_ROW;
515
516 procedure ADD_LANGUAGE
517 is
518 begin
519 /* Mar/19/03 requested by Ric Ginsberg */
520 /* The following delete and update statements are commented out */
521 /* as a quick workaround to fix the time-consuming table handler issue */
522 /* Eventually we'll need to turn them into a separate fix_language procedure */
523 /*
524
525 delete from FND_RESPONSIBILITY_TL T
526 where not exists
527 (select NULL
528 from FND_RESPONSIBILITY B
529 where B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
530 and B.APPLICATION_ID = T.APPLICATION_ID
531 );
532
533 update FND_RESPONSIBILITY_TL T set (
534 RESPONSIBILITY_NAME,
535 DESCRIPTION
536 ) = (select
537 B.RESPONSIBILITY_NAME,
538 B.DESCRIPTION
539 from FND_RESPONSIBILITY_TL B
540 where B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
541 and B.APPLICATION_ID = T.APPLICATION_ID
542 and B.LANGUAGE = T.SOURCE_LANG)
543 where (
544 T.RESPONSIBILITY_ID,
545 T.APPLICATION_ID,
546 T.LANGUAGE
547 ) in (select
548 SUBT.RESPONSIBILITY_ID,
549 SUBT.APPLICATION_ID,
550 SUBT.LANGUAGE
551 from FND_RESPONSIBILITY_TL SUBB, FND_RESPONSIBILITY_TL SUBT
552 where SUBB.RESPONSIBILITY_ID = SUBT.RESPONSIBILITY_ID
553 and SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
554 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
555 and (SUBB.RESPONSIBILITY_NAME <> SUBT.RESPONSIBILITY_NAME
556 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
557 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
558 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
559 ));
560 */
561
562 insert /*+ append parallel(TT) */ into
563 FND_RESPONSIBILITY_TL TT(
564 APPLICATION_ID,
565 RESPONSIBILITY_ID,
566 RESPONSIBILITY_NAME,
567 DESCRIPTION,
568 CREATED_BY,
569 CREATION_DATE,
570 LAST_UPDATED_BY,
571 LAST_UPDATE_DATE,
572 LAST_UPDATE_LOGIN,
573 LANGUAGE,
574 SOURCE_LANG
575 ) select /*+ parallel(V) parallel(T) use_nl(T) */ V.* from
576 ( select /*+ no_merge ordered parallel(B) */
577 B.APPLICATION_ID,
578 B.RESPONSIBILITY_ID,
579 B.RESPONSIBILITY_NAME,
580 B.DESCRIPTION,
581 B.CREATED_BY,
582 B.CREATION_DATE,
583 B.LAST_UPDATED_BY,
584 B.LAST_UPDATE_DATE,
585 B.LAST_UPDATE_LOGIN,
586 L.LANGUAGE_CODE,
587 B.SOURCE_LANG
588 from FND_RESPONSIBILITY_TL B, FND_LANGUAGES L
589 where L.INSTALLED_FLAG in ('I', 'B')
590 and B.LANGUAGE = userenv('LANG')
591 )V, FND_RESPONSIBILITY_TL T
592 where T.RESPONSIBILITY_ID(+) = V.RESPONSIBILITY_ID
593 and T.APPLICATION_ID(+) = V.APPLICATION_ID
594 and T.LANGUAGE(+) = V.LANGUAGE_CODE
595 and T.APPLICATION_ID is NULL
596 and T.RESPONSIBILITY_ID is NULL;
597 end ADD_LANGUAGE;
598
599 --------------------------------------------------------------------------
600 /*
601 ** resp_synch - <described in AFSCRSPS.pls>
602 */
603 PROCEDURE resp_synch(p_application_id in number,
604 p_responsibility_id in number)
605 is
606 my_start date;
607 my_end date;
608 my_dispname varchar2(100);
609 my_desc varchar2(240);
610 my_respkey varchar2(30);
611 begin
612 -- 12/03- TMORROW recoded this routine to create diamonds of resps rather
613 -- than just calling wf_local_synch.propagate_role.
614
615 -- fetch info for synch --
616
617 select start_date, end_date, responsibility_key
618 into my_start, my_end, my_respkey
619 from fnd_responsibility
620 where responsibility_id = p_responsibility_id
621 and application_id = p_application_id;
622
623 -- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
624 fnd_user_resp_groups_api.sync_roles_all_secgrps(
625 p_responsibility_id,
626 p_application_id,
627 my_respkey,
628 my_start,
629 my_end);
630
631 end;
632 --------------------------------------------------------------------------
633
634 -- OVERLOADED
635 -- This overloaded version omits X_RESPONSIBILITY_ID because we no longer
636 -- rely on hardcoded responsibility_ids. We now always derive the
637 -- responsibility_id.
638
639 procedure LOAD_ROW (
640 X_APP_SHORT_NAME in VARCHAR2,
641 X_RESP_KEY in VARCHAR2,
642 X_RESPONSIBILITY_NAME in VARCHAR2,
643 X_OWNER in VARCHAR2,
644 X_DATA_GROUP_APP_SHORT_NAME in VARCHAR2,
645 X_DATA_GROUP_NAME in VARCHAR2,
646 X_MENU_NAME in VARCHAR2,
647 X_START_DATE in VARCHAR2,
648 X_END_DATE in VARCHAR2,
649 X_DESCRIPTION in VARCHAR2,
650 X_GROUP_APP_SHORT_NAME in VARCHAR2,
651 X_REQUEST_GROUP_NAME in VARCHAR2,
652 X_VERSION in VARCHAR2,
653 X_WEB_HOST_NAME in VARCHAR2,
654 X_WEB_AGENT_NAME in VARCHAR2,
655 X_CUSTOM_MODE in VARCHAR2,
656 X_LAST_UPDATE_DATE in VARCHAR2 )
657 is
658 user_id number := 0;
659 resp_id number;
660 app_id number;
661 dataGroupApp_id number;
662 dataGroup_id number;
663 requestGroupApp_id number;
664 requestGroup_id number;
665 menu_id number;
666 row_id varchar2(64);
667 f_luby number; -- entity owner in file
668 f_ludate date; -- entity update date in file
669 db_luby number; -- entity owner in db
670 db_ludate date; -- entity update date in db
671 l_end_date varchar2(11);
672 l_web_host_name varchar2(80);
673 l_web_agent_name varchar2(80);
674
675 begin
676
677 begin
678 select application_id into app_id
679 from fnd_application
680 where application_short_name = X_APP_SHORT_NAME;
681 exception
682 when no_data_found then
683 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
684 fnd_message.set_token('TABLE', 'FND_APPLICATION');
685 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
686 fnd_message.set_token('VALUE', x_app_short_name);
687 app_exception.raise_exception;
688 end;
689
690 begin
691 select dgu.data_group_id, dgu.application_id
692 into dataGroup_Id, dataGroupApp_id
693 from fnd_data_group_units dgu, fnd_data_groups dg, fnd_application a
694 where dgu.data_group_id = dg.data_group_id
695 and dg.data_group_name = X_DATA_GROUP_NAME
696 and dgu.application_id = a.application_id
697 and a.application_short_name = X_DATA_GROUP_APP_SHORT_NAME;
698 exception
699 when no_data_found then
700 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
701 fnd_message.set_token('TABLE', 'FND_DATA_GROUP_UNITS');
702 fnd_message.set_token('COLUMN',
703 '(DATA_GROUP_NAME, DATA_GROUP_APP_SHORT_NAME)');
704 fnd_message.set_token('VALUE', '('||X_DATA_GROUP_NAME||', '||
705 X_DATA_GROUP_APP_SHORT_NAME||')');
706 fnd_message.set_token('NOTE',
707 'This warning can be ignored while MRC '||
708 '(Multiple Reporting Currency) responsibilities '||
709 'are being uploaded. It simply means that the MRC '||
710 'responsibility is not being uploaded because it '||
711 'won''t be used. In later releases the MRC responsibilities '||
712 'will be moved out into ldt files that can be patched '||
713 'seperately, so this warning will not occur.');
714 /* Do not raise an exception because that would halt the upload */
715 /* of other resps. Instead, just fail for this resp and go on. */
716 /* app_exception.raise_exception;*/
717 return;
718 end;
719 begin
720 select menu_id into menu_id
721 from fnd_menus_vl
722 where menu_name = X_MENU_NAME;
723 exception
724 when no_data_found then
725 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
726 fnd_message.set_token('TABLE', 'FND_MENUS_VL');
727 fnd_message.set_token('COLUMN', 'MENU_NAME');
728 fnd_message.set_token('VALUE', x_menu_name);
729 app_exception.raise_exception;
730 end;
731
732 if ((X_GROUP_APP_SHORT_NAME is not null) or
733 (X_REQUEST_GROUP_NAME is not null)) then
734 begin
735 select application_id
736 into requestGroupApp_id
737 from fnd_application
738 where application_short_name = X_GROUP_APP_SHORT_NAME;
739
740 exception
741 when no_data_found then
742 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
743 fnd_message.set_token('TABLE', 'FND_APPLICATION');
744 fnd_message.set_token('COLUMN', 'GROUP_APP_SHORT_NAME');
745 fnd_message.set_token('VALUE', X_GROUP_APP_SHORT_NAME);
746 app_exception.raise_exception;
747 end;
748
749 begin
750 select request_group_id
751 into requestGroup_id
752 from fnd_request_groups
753 where request_group_name = X_REQUEST_GROUP_NAME
754 and application_id = requestGroupApp_id;
755
756 exception
757 when no_data_found then
758 --
759 -- create an empty request group to tide us over until
760 -- the request groups are uploaded anon. Using "create
761 -- request group" code taken from afcpreqg.lct.
762 --
763 select FND_REQUEST_GROUPS_S.nextval
764 into requestGroup_id
765 from dual;
766
767 insert into fnd_request_groups
768 (request_group_name,
769 request_group_id,
770 application_id,
771 description,
772 request_group_code,
773 last_updated_by,
774 last_update_date,
775 last_update_login,
776 creation_date,
777 created_by)
778 values
779 (X_REQUEST_GROUP_NAME,
780 requestGroup_id,
781 requestGroupApp_id,
782 'Empty request group',
783 null,
784 0,sysdate,0,sysdate,0);
785 end;
786 end if;
787
788 begin
789
790 -- Translate owner to file_last_updated_by
791 f_luby := fnd_load_util.owner_id(x_owner);
792
793 -- Translate char last_update_date to date
794 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
795
796 select decode(X_END_DATE, fnd_load_util.null_value, null,
797 null, X_END_DATE,
798 X_END_DATE),
799 decode(X_WEB_HOST_NAME, fnd_load_util.null_value, null,
800 null, X_WEB_HOST_NAME,
801 X_WEB_HOST_NAME),
802 decode(X_WEB_AGENT_NAME, fnd_load_util.null_value, null,
803 null, X_WEB_AGENT_NAME,
804 X_WEB_AGENT_NAME)
805 into l_end_date, l_web_host_name, l_web_agent_name
806 from dual;
807
808 select LAST_UPDATED_BY, LAST_UPDATE_DATE, responsibility_id
809 into db_luby, db_ludate, resp_id
810 from fnd_responsibility
811 where RESPONSIBILITY_KEY = upper(X_RESP_KEY)
812 and APPLICATION_ID = app_id;
813
814 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
815 db_ludate, X_CUSTOM_MODE)) then
816 fnd_responsibility_pkg.UPDATE_ROW (
817 X_RESPONSIBILITY_ID => resp_id,
818 X_APPLICATION_ID => app_id,
819 X_WEB_HOST_NAME => L_WEB_HOST_NAME,
820 X_WEB_AGENT_NAME => L_WEB_AGENT_NAME,
821 X_DATA_GROUP_APPLICATION_ID => dataGroupApp_id,
822 X_DATA_GROUP_ID => dataGroup_id,
823 X_MENU_ID => menu_id,
824 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
825 X_END_DATE => to_date(L_END_DATE, 'YYYY/MM/DD'),
826 X_GROUP_APPLICATION_ID => requestGroupApp_id,
827 X_REQUEST_GROUP_ID => requestGroup_id,
828 X_VERSION => X_VERSION,
829 X_RESPONSIBILITY_KEY => upper(X_RESP_KEY),
830 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
831 X_DESCRIPTION => X_DESCRIPTION,
832 X_LAST_UPDATE_DATE => f_ludate,
833 X_LAST_UPDATED_BY => f_luby,
834 X_LAST_UPDATE_LOGIN => 0 );
835 end if;
836
837 exception
838 when NO_DATA_FOUND then
839 -- Get a new resp_id if I don't have one yet
840 if (resp_id is null) then
841 select fnd_responsibility_s.nextval
842 into resp_id
843 from sys.dual;
844 end if;
845
846 fnd_responsibility_pkg.INSERT_ROW(
847 X_ROWID => row_id,
848 X_RESPONSIBILITY_ID => resp_id,
849 X_APPLICATION_ID => app_id,
850 X_WEB_HOST_NAME => L_WEB_HOST_NAME,
851 X_WEB_AGENT_NAME => L_WEB_AGENT_NAME,
852 X_DATA_GROUP_APPLICATION_ID => dataGroupApp_id,
853 X_DATA_GROUP_ID => dataGroup_id,
854 X_MENU_ID => menu_id,
855 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
856 X_END_DATE => to_date(L_END_DATE, 'YYYY/MM/DD'),
857 X_GROUP_APPLICATION_ID => requestGroupApp_id,
858 X_REQUEST_GROUP_ID => requestGroup_id,
859 X_VERSION => X_VERSION,
860 X_RESPONSIBILITY_KEY => upper(X_RESP_KEY),
861 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
862 X_DESCRIPTION => X_DESCRIPTION,
863 X_CREATION_DATE => f_ludate,
864 X_CREATED_BY => f_luby,
865 X_LAST_UPDATE_DATE => f_ludate,
866 X_LAST_UPDATED_BY => f_luby,
867 X_LAST_UPDATE_LOGIN => 0 );
868 end;
869 end LOAD_ROW;
870
871 end FND_RESPONSIBILITY_PKG;