[Home] [Help]
PACKAGE BODY: APPS.FND_RESPONSIBILITY_PKG
Source
1 package body FND_RESPONSIBILITY_PKG as
2 /* $Header: AFSCRSPB.pls 120.3.12000000.2 2007/03/16 23:27:12 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 => 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 => 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 -- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
459 fnd_user_resp_groups_api.sync_roles_all_secgrps(
460 X_RESPONSIBILITY_ID,
461 X_APPLICATION_ID,
462 X_RESPONSIBILITY_KEY,
463 X_START_DATE,
464 X_END_DATE);
465
466 update FND_RESPONSIBILITY_TL set
467 RESPONSIBILITY_NAME = X_RESPONSIBILITY_NAME,
468 DESCRIPTION = X_DESCRIPTION,
469 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
470 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
471 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
472 SOURCE_LANG = userenv('LANG')
473 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
474 and APPLICATION_ID = X_APPLICATION_ID
475 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
476
477 if (sql%notfound) then
478 raise no_data_found;
479 end if;
480
481 end UPDATE_ROW;
482
483 procedure DELETE_ROW (
484 X_RESPONSIBILITY_ID in NUMBER,
485 X_APPLICATION_ID in NUMBER
486 ) is
487 myList wf_parameter_list_t;
488 begin
489 delete from FND_RESPONSIBILITY
490 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
491 and APPLICATION_ID = X_APPLICATION_ID;
492
493 if (sql%notfound) then
494 raise no_data_found;
495 else
496 -- Added for Function Security Cache Invalidation Project
497 fnd_function_security_cache.delete_resp(X_RESPONSIBILITY_ID, X_APPLICATION_ID);
498
499 end if;
500
501 delete from FND_RESPONSIBILITY_TL
502 where RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
503 and APPLICATION_ID = X_APPLICATION_ID;
504
505 if (sql%notfound) then
506 raise no_data_found;
507 end if;
508
509 end DELETE_ROW;
510
511 procedure ADD_LANGUAGE
512 is
513 begin
514 /* Mar/19/03 requested by Ric Ginsberg */
515 /* The following delete and update statements are commented out */
516 /* as a quick workaround to fix the time-consuming table handler issue */
517 /* Eventually we'll need to turn them into a separate fix_language procedure */
518 /*
519
520 delete from FND_RESPONSIBILITY_TL T
521 where not exists
522 (select NULL
523 from FND_RESPONSIBILITY B
524 where B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
525 and B.APPLICATION_ID = T.APPLICATION_ID
526 );
527
528 update FND_RESPONSIBILITY_TL T set (
529 RESPONSIBILITY_NAME,
530 DESCRIPTION
531 ) = (select
532 B.RESPONSIBILITY_NAME,
533 B.DESCRIPTION
534 from FND_RESPONSIBILITY_TL B
535 where B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
536 and B.APPLICATION_ID = T.APPLICATION_ID
537 and B.LANGUAGE = T.SOURCE_LANG)
538 where (
539 T.RESPONSIBILITY_ID,
540 T.APPLICATION_ID,
541 T.LANGUAGE
542 ) in (select
543 SUBT.RESPONSIBILITY_ID,
544 SUBT.APPLICATION_ID,
545 SUBT.LANGUAGE
546 from FND_RESPONSIBILITY_TL SUBB, FND_RESPONSIBILITY_TL SUBT
547 where SUBB.RESPONSIBILITY_ID = SUBT.RESPONSIBILITY_ID
548 and SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
549 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
550 and (SUBB.RESPONSIBILITY_NAME <> SUBT.RESPONSIBILITY_NAME
551 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
552 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
553 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
554 ));
555 */
556
557 insert /*+ append parallel(TT) */ into
558 FND_RESPONSIBILITY_TL TT(
559 APPLICATION_ID,
560 RESPONSIBILITY_ID,
561 RESPONSIBILITY_NAME,
562 DESCRIPTION,
563 CREATED_BY,
564 CREATION_DATE,
565 LAST_UPDATED_BY,
566 LAST_UPDATE_DATE,
567 LAST_UPDATE_LOGIN,
568 LANGUAGE,
569 SOURCE_LANG
570 ) select /*+ parallel(V) parallel(T) use_nl(T) */ V.* from
571 ( select /*+ no_merge ordered parallel(B) */
572 B.APPLICATION_ID,
573 B.RESPONSIBILITY_ID,
574 B.RESPONSIBILITY_NAME,
575 B.DESCRIPTION,
576 B.CREATED_BY,
577 B.CREATION_DATE,
578 B.LAST_UPDATED_BY,
579 B.LAST_UPDATE_DATE,
580 B.LAST_UPDATE_LOGIN,
581 L.LANGUAGE_CODE,
582 B.SOURCE_LANG
583 from FND_RESPONSIBILITY_TL B, FND_LANGUAGES L
584 where L.INSTALLED_FLAG in ('I', 'B')
585 and B.LANGUAGE = userenv('LANG')
586 )V, FND_RESPONSIBILITY_TL T
587 where T.RESPONSIBILITY_ID(+) = V.RESPONSIBILITY_ID
588 and T.APPLICATION_ID(+) = V.APPLICATION_ID
589 and T.LANGUAGE(+) = V.LANGUAGE_CODE
590 and T.APPLICATION_ID is NULL
591 and T.RESPONSIBILITY_ID is NULL;
592 end ADD_LANGUAGE;
593
594 --------------------------------------------------------------------------
595 /*
596 ** resp_synch - <described in AFSCRSPS.pls>
597 */
598 PROCEDURE resp_synch(p_application_id in number,
599 p_responsibility_id in number)
600 is
601 my_start date;
602 my_end date;
603 my_dispname varchar2(100);
604 my_desc varchar2(240);
605 my_respkey varchar2(30);
606 begin
607 -- 12/03- TMORROW recoded this routine to create diamonds of resps rather
608 -- than just calling wf_local_synch.propagate_role.
609
610 -- fetch info for synch --
611
612 select start_date, end_date, responsibility_key
613 into my_start, my_end, my_respkey
614 from fnd_responsibility
615 where responsibility_id = p_responsibility_id
616 and application_id = p_application_id;
617
618 -- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
619 fnd_user_resp_groups_api.sync_roles_all_secgrps(
620 p_responsibility_id,
621 p_application_id,
622 my_respkey,
623 my_start,
624 my_end);
625
626 end;
627 --------------------------------------------------------------------------
628
629 -- OVERLOADED
630 -- This overloaded version omits X_RESPONSIBILITY_ID because we no longer
631 -- rely on hardcoded responsibility_ids. We now always derive the
632 -- responsibility_id.
633
634 procedure LOAD_ROW (
635 X_APP_SHORT_NAME in VARCHAR2,
636 X_RESP_KEY in VARCHAR2,
637 X_RESPONSIBILITY_NAME in VARCHAR2,
638 X_OWNER in VARCHAR2,
639 X_DATA_GROUP_APP_SHORT_NAME in VARCHAR2,
640 X_DATA_GROUP_NAME in VARCHAR2,
641 X_MENU_NAME in VARCHAR2,
642 X_START_DATE in VARCHAR2,
643 X_END_DATE in VARCHAR2,
644 X_DESCRIPTION in VARCHAR2,
645 X_GROUP_APP_SHORT_NAME in VARCHAR2,
646 X_REQUEST_GROUP_NAME in VARCHAR2,
647 X_VERSION in VARCHAR2,
648 X_WEB_HOST_NAME in VARCHAR2,
649 X_WEB_AGENT_NAME in VARCHAR2,
650 X_CUSTOM_MODE in VARCHAR2,
651 X_LAST_UPDATE_DATE in VARCHAR2 )
652 is
653 user_id number := 0;
654 resp_id number;
655 app_id number;
656 dataGroupApp_id number;
657 dataGroup_id number;
658 requestGroupApp_id number;
659 requestGroup_id number;
660 menu_id number;
661 row_id varchar2(64);
662 f_luby number; -- entity owner in file
663 f_ludate date; -- entity update date in file
664 db_luby number; -- entity owner in db
665 db_ludate date; -- entity update date in db
666 l_end_date varchar2(11);
667 l_web_host_name varchar2(80);
668 l_web_agent_name varchar2(80);
669
670 begin
671
672 begin
673 select application_id into app_id
674 from fnd_application
675 where application_short_name = X_APP_SHORT_NAME;
676 exception
677 when no_data_found then
678 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
679 fnd_message.set_token('TABLE', 'FND_APPLICATION');
680 fnd_message.set_token('COLUMN', 'APPLICATION_SHORT_NAME');
681 fnd_message.set_token('VALUE', x_app_short_name);
682 app_exception.raise_exception;
683 end;
684
685 begin
686 select dgu.data_group_id, dgu.application_id
687 into dataGroup_Id, dataGroupApp_id
688 from fnd_data_group_units dgu, fnd_data_groups dg, fnd_application a
689 where dgu.data_group_id = dg.data_group_id
690 and dg.data_group_name = X_DATA_GROUP_NAME
691 and dgu.application_id = a.application_id
692 and a.application_short_name = X_DATA_GROUP_APP_SHORT_NAME;
693 exception
694 when no_data_found then
695 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
696 fnd_message.set_token('TABLE', 'FND_DATA_GROUP_UNITS');
697 fnd_message.set_token('COLUMN',
698 '(DATA_GROUP_NAME, DATA_GROUP_APP_SHORT_NAME)');
699 fnd_message.set_token('VALUE', '('||X_DATA_GROUP_NAME||', '||
700 X_DATA_GROUP_APP_SHORT_NAME||')');
701 fnd_message.set_token('NOTE',
702 'This warning can be ignored while MRC '||
703 '(Multiple Reporting Currency) responsibilities '||
704 'are being uploaded. It simply means that the MRC '||
705 'responsibility is not being uploaded because it '||
706 'won''t be used. In later releases the MRC responsibilities '||
707 'will be moved out into ldt files that can be patched '||
708 'seperately, so this warning will not occur.');
709 /* Do not raise an exception because that would halt the upload */
710 /* of other resps. Instead, just fail for this resp and go on. */
711 /* app_exception.raise_exception;*/
712 return;
713 end;
714 begin
715 select menu_id into menu_id
716 from fnd_menus_vl
717 where menu_name = X_MENU_NAME;
718 exception
719 when no_data_found then
720 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
721 fnd_message.set_token('TABLE', 'FND_MENUS_VL');
722 fnd_message.set_token('COLUMN', 'MENU_NAME');
723 fnd_message.set_token('VALUE', x_menu_name);
724 app_exception.raise_exception;
725 end;
726
727 if ((X_GROUP_APP_SHORT_NAME is not null) or
728 (X_REQUEST_GROUP_NAME is not null)) then
729 begin
730 select application_id
731 into requestGroupApp_id
732 from fnd_application
733 where application_short_name = X_GROUP_APP_SHORT_NAME;
734
735 exception
736 when no_data_found then
737 fnd_message.set_name('FND', 'SQL_NO_DATA_FOUND');
738 fnd_message.set_token('TABLE', 'FND_APPLICATION');
739 fnd_message.set_token('COLUMN', 'GROUP_APP_SHORT_NAME');
740 fnd_message.set_token('VALUE', X_GROUP_APP_SHORT_NAME);
741 app_exception.raise_exception;
742 end;
743
744 begin
745 select request_group_id
746 into requestGroup_id
747 from fnd_request_groups
748 where request_group_name = X_REQUEST_GROUP_NAME
749 and application_id = requestGroupApp_id;
750
751 exception
752 when no_data_found then
753 --
754 -- create an empty request group to tide us over until
755 -- the request groups are uploaded anon. Using "create
756 -- request group" code taken from afcpreqg.lct.
757 --
758 select FND_REQUEST_GROUPS_S.nextval
759 into requestGroup_id
760 from dual;
761
762 insert into fnd_request_groups
763 (request_group_name,
764 request_group_id,
765 application_id,
766 description,
767 request_group_code,
768 last_updated_by,
769 last_update_date,
770 last_update_login,
771 creation_date,
772 created_by)
773 values
774 (X_REQUEST_GROUP_NAME,
775 requestGroup_id,
776 requestGroupApp_id,
777 'Empty request group',
778 null,
779 0,sysdate,0,sysdate,0);
780 end;
781 end if;
782
783 begin
784
785 -- Translate owner to file_last_updated_by
786 f_luby := fnd_load_util.owner_id(x_owner);
787
788 -- Translate char last_update_date to date
789 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
790
791 select decode(X_END_DATE, fnd_load_util.null_value, null,
792 null, X_END_DATE,
793 X_END_DATE),
794 decode(X_WEB_HOST_NAME, fnd_load_util.null_value, null,
795 null, X_WEB_HOST_NAME,
796 X_WEB_HOST_NAME),
797 decode(X_WEB_AGENT_NAME, fnd_load_util.null_value, null,
798 null, X_WEB_AGENT_NAME,
799 X_WEB_AGENT_NAME)
800 into l_end_date, l_web_host_name, l_web_agent_name
801 from dual;
802
803 select LAST_UPDATED_BY, LAST_UPDATE_DATE, responsibility_id
804 into db_luby, db_ludate, resp_id
805 from fnd_responsibility
806 where RESPONSIBILITY_KEY = X_RESP_KEY
807 and APPLICATION_ID = app_id;
808
809 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
810 db_ludate, X_CUSTOM_MODE)) then
811 fnd_responsibility_pkg.UPDATE_ROW (
812 X_RESPONSIBILITY_ID => resp_id,
813 X_APPLICATION_ID => app_id,
814 X_WEB_HOST_NAME => L_WEB_HOST_NAME,
815 X_WEB_AGENT_NAME => L_WEB_AGENT_NAME,
816 X_DATA_GROUP_APPLICATION_ID => dataGroupApp_id,
817 X_DATA_GROUP_ID => dataGroup_id,
818 X_MENU_ID => menu_id,
819 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
820 X_END_DATE => to_date(L_END_DATE, 'YYYY/MM/DD'),
821 X_GROUP_APPLICATION_ID => requestGroupApp_id,
822 X_REQUEST_GROUP_ID => requestGroup_id,
823 X_VERSION => X_VERSION,
824 X_RESPONSIBILITY_KEY => X_RESP_KEY,
825 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
826 X_DESCRIPTION => X_DESCRIPTION,
827 X_LAST_UPDATE_DATE => f_ludate,
828 X_LAST_UPDATED_BY => f_luby,
829 X_LAST_UPDATE_LOGIN => 0 );
830 end if;
831
832 exception
833 when NO_DATA_FOUND then
834 -- Get a new resp_id if I don't have one yet
835 if (resp_id is null) then
836 select fnd_responsibility_s.nextval
837 into resp_id
838 from sys.dual;
839 end if;
840
841 fnd_responsibility_pkg.INSERT_ROW(
842 X_ROWID => row_id,
843 X_RESPONSIBILITY_ID => resp_id,
844 X_APPLICATION_ID => app_id,
845 X_WEB_HOST_NAME => L_WEB_HOST_NAME,
846 X_WEB_AGENT_NAME => L_WEB_AGENT_NAME,
847 X_DATA_GROUP_APPLICATION_ID => dataGroupApp_id,
848 X_DATA_GROUP_ID => dataGroup_id,
849 X_MENU_ID => menu_id,
850 X_START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
851 X_END_DATE => to_date(L_END_DATE, 'YYYY/MM/DD'),
852 X_GROUP_APPLICATION_ID => requestGroupApp_id,
853 X_REQUEST_GROUP_ID => requestGroup_id,
854 X_VERSION => X_VERSION,
855 X_RESPONSIBILITY_KEY => X_RESP_KEY,
856 X_RESPONSIBILITY_NAME => X_RESPONSIBILITY_NAME,
857 X_DESCRIPTION => X_DESCRIPTION,
858 X_CREATION_DATE => f_ludate,
859 X_CREATED_BY => f_luby,
860 X_LAST_UPDATE_DATE => f_ludate,
861 X_LAST_UPDATED_BY => f_luby,
862 X_LAST_UPDATE_LOGIN => 0 );
863 end;
864 end LOAD_ROW;
865
866 end FND_RESPONSIBILITY_PKG;