1 PACKAGE BODY mo_global AS
2 /* $Header: AFMOGBLB.pls 120.40.12020000.2 2012/08/13 22:00:01 shnaraya ship $ */
3
4 g_multi_org_flag fnd_product_groups.multi_org_flag%TYPE;
5 g_access_mode varchar2(1);
6 g_current_org_id number(15);
7 g_ou_count PLS_INTEGER;
8 g_sync varchar2(1);
9 g_init_access_mode varchar2(1);
10 g_ou_id_tab OrgIdTab;
11
12 g_old_sp_id fnd_profile_option_values.profile_option_value%TYPE := NULL;
13 g_old_org_id fnd_profile_option_values.profile_option_value%TYPE := NULL;
14 g_old_user_id NUMBER;
15 g_old_resp_id NUMBER;
16
17 TYPE ApplShortNameTab is TABLE OF fnd_mo_product_init.application_short_name%TYPE
18 INDEX BY BINARY_INTEGER;
19 TYPE StatusTab is TABLE OF fnd_mo_product_init.status%TYPE
20 INDEX BY BINARY_INTEGER;
21
22 --
26 errcode in number,
23 -- Private functions and procedures
24 --
25 PROCEDURE generic_error(routine in varchar2,
27 errmsg in varchar2) IS
28 BEGIN
29 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
30 fnd_message.set_token('ROUTINE', routine);
31 fnd_message.set_token('ERRNO', errcode);
32 fnd_message.set_token('REASON', errmsg);
33 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
34 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, routine, FALSE);
35 END IF;
36 fnd_message.raise_error;
37 END;
38
39 --
40 -- This is an internal API that accepts the ORG_ID, SECURITY_PROFILE_ID
41 -- and populates the Multi-Org temporary table based on the access
42 -- enabled status of the product. The API returns the current org for
43 -- single access mode and view all flag for all access mode.
44 --
45 -- Product teams should never access the temporary table directly
46 -- because it may become obsolete in the future.
47 -- The contents of the temporary table can be accessed by the
48 -- APIs provided within this package and the MO_UTILS package.
49
50 PROCEDURE populate_orgs (p_org_id_char IN VARCHAR2,
51 p_sp_id_char IN VARCHAR2,
52 p_current_org_id OUT NOCOPY VARCHAR2,
53 p_view_all_org OUT NOCOPY VARCHAR2)
54 IS
55
56 t_org_id OrgidTab;
57 t_ou_name OuNameTab;
58 t_common_org_id OrgidTab;
59 t_common_ou_name OuNameTab;
60 t_pref_org_id OrgidTab;
61 t_delete_org_id OrgidTab;
62 sync_ind VARCHAR2(1) := 'N';
63 match_ind VARCHAR2(1);
64 k BINARY_INTEGER := 1;
65 l BINARY_INTEGER := 1;
66
67 is_view_all_org VARCHAR2(1);
68 l_sp_name per_security_profiles.security_profile_name%TYPE;
69 l_bg_id per_security_profiles.business_group_id%TYPE;
70
71
72 CURSOR c1 IS
73 SELECT per.organization_id organization_id
74 , hr.NAME name
75 FROM per_organization_list per
76 , hr_operating_units hr
77 WHERE per.security_profile_id = to_number(p_sp_id_char)
78 AND hr.organization_id = per.organization_id
79 AND hr.usable_flag is null;
80
81 CURSOR c2 IS
82 SELECT hr.organization_id organization_id
83 , hr.name name
84 FROM hr_operating_units hr
85 WHERE hr.organization_id = to_number(p_org_id_char)
86 AND hr.usable_flag is null;
87
88 -- Added the following cursor to support view all security profile with a
89 -- business group (BG). For a view all security profile within a BG, the
90 -- per_organization_list is not populated, so should directly get the
91 -- operating units for the particular business group from hr_operating_units
92 -- view.
93
94 -- Commented out the reference of the business group name in the WHERE
95 -- Clause, since the SQL is supposed to return all operating units under
96 -- the business group (Bug 2720910)
97
98 CURSOR c3 (X_sp_name VARCHAR2,
99 X_bg_id NUMBER) IS
100 SELECT hr.organization_id organization_id
101 , hr.name name
102 FROM hr_operating_units hr
103 WHERE hr.business_group_id = X_bg_id
104 AND hr.usable_flag is null;
105
106 -- Added the following cursor to support global view all security profile
107 -- For a global view all security profile, the per_organization_list is
108 -- not populated, so should directly get all operating units from
109 -- hr_operating_units view.
110
111 CURSOR c4 IS
112 SELECT hr.organization_id organization_id
113 , hr.name name
114 FROM hr_operating_units hr
115 WHERE hr.usable_flag is null;
116
117 -- Added the following cursor to support synchronization with the multi-org
118 -- preference setup.
119
120 CURSOR c5 IS
121 SELECT organization_id
122 FROM fnd_mo_sp_preferences
123 WHERE USER_ID = FND_GLOBAL.USER_ID
124 AND RESP_ID = FND_GLOBAL.RESP_ID;
125
126 l_user_id number;
127 l_resp_id number;
128 l_del_org_id hr_operating_units.organization_id%TYPE;
129
130 BEGIN
131
132 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
134 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.begin',
135 'Calling PL/SQL procedure '||
136 'MO_GLOBAL.POPULATE_ORGS');
137 END IF;
138
139 --
140 -- Initialize the count of the accessible operating units.
141 --
142 g_ou_count := 0;
143
144 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
146 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.input_parameters',
147 'p_org_id='||p_org_id_char||
148 ', p_sp_id='||p_sp_id_char);
149 END IF;
150
151 --
152 -- SP ID is NOT NULL and ORG ID is NULL or NOT NULL
153 --
154 -- Ignore org_id parameter if passed
155 --
156 IF (p_sp_id_char IS NOT NULL) THEN
157
158 -- Check if this a view all or global view all organizations
159 -- security profile. For a view all security profile within
160 -- a business group, the business group id is populated.
161
162 SELECT security_profile_name
163 , business_group_id
164 , view_all_organizations_flag
165 INTO l_sp_name
166 , l_bg_id
167 , p_view_all_org
168 FROM per_security_profiles
169 WHERE security_profile_id = to_number(p_sp_id_char);
170
171 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
172 FND_LOG.STRING(
173 FND_LOG.LEVEL_EVENT,
174 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.config',
175 'per_security_profiles.security_profile_name=>'||l_sp_name||
176 ', per_security_profiles.business_group_id=>'||l_bg_id||
177 ', per_security_profiles.view_all_organizations_flag=>'||is_view_all_org);
178 END IF;
179
180 IF (p_view_all_org = 'Y') THEN
181 IF (l_bg_id IS NOT NULL) THEN
182
183 -- View all Within the Business Group Case
184 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
185 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
186 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.retrieve_orgs_c3_cursor',
187 'Retrieving operating units using cursor c3 with arguments:'||
188 ' l_sp_name='||l_sp_name||
189 ', l_bg_id='||l_bg_id);
190 END IF;
191
192 OPEN c3(l_sp_name, l_bg_id);
193 LOOP
194 FETCH c3 BULK COLLECT
195 INTO t_org_id
196 , t_ou_name;
197 EXIT WHEN c3%NOTFOUND;
198 END LOOP;
199 CLOSE c3;
200
201 ELSE
202
203 -- Global View all Case
204 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
206 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.retrieve_orgs_c4_cursor',
207 'Retrieving operating units using cursor c4');
208 END IF;
209
210 OPEN c4;
211 LOOP
212 FETCH c4 BULK COLLECT
213 INTO t_org_id
214 , t_ou_name;
215 EXIT WHEN c4%NOTFOUND;
216 END LOOP;
217 CLOSE c4;
218
219 END IF; -- for l_bg_id is not null
220
221 ELSE
222
223 -- Security Profile based on list or hierarchy Case
224 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
226 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.retrieve_orgs_c1_cursor',
227 'Retrieving operating units using cursor c1');
228 END IF;
229
230 OPEN c1;
231 LOOP
232 FETCH c1 BULK COLLECT
233 INTO t_org_id
234 , t_ou_name;
235 EXIT WHEN c1%NOTFOUND;
236 END LOOP;
237 CLOSE c1;
238
239 END IF; -- for is_view_all_org
240
241 --
242 -- SP ID is NULL and ORG ID is NOT NULL
243 --
244 ELSE
245 IF (p_org_id_char is NOT NULL) THEN
246 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
247 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
248 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.retrieve_orgs_c2_cursor',
249 'Retrieving operating units using cursor c2');
250 END IF;
251
252 OPEN c2;
253 LOOP
254 FETCH c2 BULK COLLECT
255 INTO t_org_id
256 , t_ou_name;
257 EXIT WHEN c2%NOTFOUND;
258 END LOOP;
259 CLOSE c2;
260 END IF;
261
262 END IF;
263
264 --
265 -- Populate Org Information in MO_GLOB_ORG_ACCESS_TMP
266 --
267 -- Bug fix 4511279
268 -- Need to populate temp table even when access mode is "S"
269 --
270 IF t_org_id.COUNT >= 1 THEN
271
272 OPEN c5;
273 LOOP
274 FETCH c5 BULK COLLECT
275 into t_pref_org_id;
276 EXIT WHEN c5%NOTFOUND;
280 IF (t_pref_org_id.COUNT > 0) THEN
277 END LOOP;
278 CLOSE c5;
279
281 IF (g_sync <> 'N') THEN
282 sync_ind := 'Y';
283 END IF;
284 for i in t_pref_org_id.FIRST .. t_pref_org_id.LAST LOOP
285 match_ind := 'N';
286 for j in t_org_id.FIRST .. t_org_id.LAST LOOP
287 if t_pref_org_id(i) = t_org_id(j) then
288 match_ind := 'Y';
289 t_common_org_id(k) := t_org_id(j);
290 t_common_ou_name(k) := t_ou_name(j);
291 k := k+1;
292 exit;
293 end if;
294 end LOOP;
295 if match_ind = 'N' then
296 t_delete_org_id(l) := t_pref_org_id(i);
297 l := l + 1;
298 end if;
299 END LOOP;
300 -- IF t_delete_org_id.COUNT <> t_pref_org_id.COUNT THEN
301 IF (t_delete_org_id.COUNT > 0) THEN
302 if ( fnd_adg_support.is_standby )
303 then
304 $if fnd_adg_compile_directive.enable_rpc
305 $then
306 l_user_id := FND_GLOBAL.USER_ID;
307 l_resp_id := FND_GLOBAL.RESP_ID;
308
309 FOR m IN t_delete_org_id.FIRST .. t_delete_org_id.LAST LOOP
310
311 l_del_org_id := t_delete_org_id(m);
312
313 delete from FND_MO_SP_PREFERENCES_REMOTE
314 where user_id = l_user_id
315 and resp_id = l_resp_id
316 and organization_id = l_del_org_id;
317
318 END LOOP;
319 $else
320 null;
321 $end
322 else
323
324 FORALL m IN t_delete_org_id.FIRST .. t_delete_org_id.LAST
325 delete from FND_MO_SP_PREFERENCES
326 where user_id = FND_GLOBAL.USER_ID
327 and resp_id = FND_GLOBAL.RESP_ID
328 and organization_id = t_delete_org_id(m);
329
330 end if;
331
332 commit;
333 END IF;
334 END IF;
335
336 /*
337 IF (t_pref_org_id.COUNT > 0) and (g_sync <> 'N') THEN
338 sync_ind := 'Y';
339 for i in t_org_id.FIRST .. t_org_id.LAST LOOP
340 for j in t_pref_org_id.FIRST .. t_pref_org_id.LAST LOOP
341 if t_org_id(i) = t_pref_org_id(j) then
342 t_common_org_id(k) := t_org_id(i);
343 t_common_ou_name(k) := t_ou_name(i);
344 k := k+1;
345 exit;
346 end if;
347 end LOOP;
348 end LOOP;
349 END IF;
350 */
351
352 IF(sync_ind = 'Y') AND ( t_delete_org_id.COUNT < t_pref_org_id.COUNT) THEN
353 FOR i IN t_common_org_id.FIRST .. t_common_org_id.LAST LOOP
354 if ( fnd_adg_support.is_standby )
355 then
356 $if fnd_adg_compile_directive.enable_rpc
357 $then
358 INSERT
359 INTO mo_glob_org_access_tmp_remote
360 (organization_id
361 , organization_name)
362 VALUES (t_common_org_id(i)
363 , t_common_ou_name(i));
364 $else
365 null;
366 $end
367 else
368
369 INSERT
370 INTO mo_glob_org_access_tmp
371 (organization_id
372 , organization_name)
373 VALUES (t_common_org_id(i)
374 , t_common_ou_name(i));
375 end if;
376
377 -- needed for get_ou_tab function
378 g_ou_id_tab(i):=t_common_org_id(i);
379 END LOOP;
380 ELSE
381 FOR i IN t_org_id.FIRST .. t_org_id.LAST LOOP
382 if ( fnd_adg_support.is_standby )
383 then
384 $if fnd_adg_compile_directive.enable_rpc
385 $then
386 INSERT
387 INTO mo_glob_org_access_tmp_remote
388 (organization_id
389 , organization_name)
390 VALUES (t_org_id(i)
391 , t_ou_name(i));
392 $else
393 null;
394 $end
395 else
396
397 INSERT
398 INTO mo_glob_org_access_tmp
399 (organization_id
400 , organization_name)
401 VALUES (t_org_id(i)
402 , t_ou_name(i));
403
404 end if;
405 -- needed for get_ou_tab function
406 g_ou_id_tab(i):=t_org_id(i);
407 END LOOP;
408 END IF;
409
410 g_ou_count := t_org_id.COUNT;
411
412 END IF;
413
414 -- set context to 'M' for BG View All Security Profile
415 IF p_sp_id_char IS NOT NULL AND l_bg_id IS NOT NULL AND p_view_all_org = 'Y' THEN
416 p_view_all_org := 'N';
417 END IF;
418
419 -- setting init access mode to S, M or A
420 IF p_view_all_org = 'Y' THEN
421 g_init_access_mode:='A';
422 ELSIF g_ou_count = 1 THEN
423 g_init_access_mode:='S';
424 ELSIF g_ou_count > 1 THEN
425 g_init_access_mode:='M';
426 END IF;
427
428 IF g_ou_count = 1 THEN
429 p_current_org_id := t_org_id(1);
430 END IF;
431
432 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
433 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
434 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.temp_table_insert',
435 'Inserted '||g_ou_count||' record(s) into MO_GLOB_ORG_ACCESS_TMP');
436 END IF;
437
441 'Returning from PL/SQL procedure '||
438 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
439 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
440 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.end',
442 'MO_GLOBAL.POPULATE_ORGS: '||
443 'l_bg_id='||l_bg_id||
444 ', p_sp_id_char='||p_sp_id_char||
445 ', l_sp_name='||l_sp_name||
446 ', p_org_id_char='||p_org_id_char||
447 ', is_view_all_org='||is_view_all_org);
448 END IF;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
454 'fnd.plsql.MO_GLOBAL.POPULATE_ORGS.temp_table',
455 'temporary table other exception raised sqlerrm'||
456 '=>'||sqlerrm);
457 END IF;
458 generic_error('MO_GLOBAL.POPULATE_ORGS', sqlcode, sqlerrm);
459
460 END populate_orgs;
461
462
463 --
464 -- This is an internal API that deletes the temporary table data
465 --
466 PROCEDURE delete_orgs
467 IS
468 BEGIN
469 --
470 -- Remove all entries from the session specific temporary table.
471 -- Without this, when you switch responsibility you get ORA error
472 -- since the repopulation fails because of the unique constraint
473 -- violation.
474 --
475
476 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
478 'fnd.plsql.MO_GLOBAL.DELETE_ORGS.begin',
479 'Before flushing MO_GLOB_ORG_ACCESS_TMP');
480 END IF;
481
482 if ( fnd_adg_support.is_standby )
483 then
484 $if fnd_adg_compile_directive.enable_rpc
485 $then
486 DELETE FROM mo_glob_org_access_tmp_remote;
487 $else
488 null;
489 $end
490 else
491 DELETE FROM mo_glob_org_access_tmp;
492 end if;
493
494 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
495 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
496 'fnd.plsql.MO_GLOBAL.DELETE_ORGS.end',
497 'MO_GLOB_ORG_ACCESS_TMP was flushed');
498 END IF;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 generic_error('MO_GLOBAL.DELETE_ORGS', sqlcode, sqlerrm);
503
504 END delete_orgs;
505
506 --
507 -- Public functions and procedures
508 --
509
510 --
511 -- Name
512 -- is_multi_org_enabled
513 -- Purpose
514 -- This function determines whether this is a multi-org database
515 -- instance or not. Returns 'Y' or 'N'.
516 --
517 FUNCTION is_multi_org_enabled RETURN VARCHAR2
518 IS
519
520 BEGIN
521 IF (g_multi_org_flag IS NULL) THEN
522 SELECT nvl(multi_org_flag, 'N')
523 INTO g_multi_org_flag
524 FROM fnd_product_groups;
525 END IF;
526
527 RETURN g_multi_org_flag;
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 generic_error('MO_GLOBAL.IS_ACCESS_CONTROL_ENABLED', sqlcode, sqlerrm);
532 END;
533
534 --
535 -- Name
536 -- set_org_access
537 --
538 -- Purpose
539 -- This procedure determines which operating units can be accessed
540 -- from the current database session. It is called by
541 -- mo_global.init when an Oracle Applications session is started.
542 -- The parameters passed to set_org_access() are the values of the
543 -- MO: Operating Unit, MO: Security Profile profile options and
544 -- Application Owner.
545 --
546 -- If the application being initialized can handle more than one
547 -- Operating Unit, access will be allowed for the Operating Units
548 -- encompassed by the security profile (if specified) and the value of
549 -- the Operating Unit parameter will be ignored, provided access is
550 -- enabled for the application calling this api. If no security profile
551 -- is specified, access will be initialized for the Operating Unit
552 -- only. If both are unspecified an exception will be raised. If
553 -- Application owner is not passed, critical error will be raised.
554 --
555 -- The Multi-Org temporary table data is deleted first for all
556 -- products that call this API.
557 -- This procedure calls another API (populate_orgs) to populate values
558 -- in the Multi-Org temporary table when access control is enabled.
559 --
560 -- For Inquiry only access control for CRM products, care should be
561 -- taken during setup to ensure that the Operating Units included in
562 -- the security profile contain the value specified for the Operating
563 -- Unit parameter. Otherwise, the user will be able to enter records
564 -- for the Operating Unit but will not be able to query the same data
565 -- since read access for CRM is controlled by the security profile.
566 --
567 -- Arguments
568 -- p_org_id_char - The operating unit ID for the current session
569 -- p_sp_id_char - The security profile id for the current session
570 -- p_appl_short_name - Application owner for the current module or session
571 --
572 PROCEDURE do_set_org_access(p_org_id_char VARCHAR2,
573 p_sp_id_char VARCHAR2,
574 p_appl_short_name VARCHAR2)
575 IS
576 l_access_ctrl_enabled VARCHAR2(1);
577 l_security_profile_id fnd_profile_option_values.profile_option_value%TYPE := p_sp_id_char;
578 l_org_id fnd_profile_option_values.profile_option_value%TYPE := p_org_id_char;
579
580 l_current_org_id hr_operating_units.name%TYPE;
581 l_view_all_orgs VARCHAR2(1);
582
586
583 NO_SP_OU_FOUND EXCEPTION;
584 NO_ORG_ACCESS_FOUND EXCEPTION;
585 NO_APPL_NAME EXCEPTION;
587 BEGIN
588 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
589 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
590 'fnd.plsql.MO_GLOBAL.SET_ORG_ACCESS.begin',
591 'Calling PL/SQL procedure MO_GLOBAL.SET_ORG_ACCESS:'||
592 ' p_org_id_char=>'||p_org_id_char||
593 ', p_sp_id_char=>'||p_sp_id_char||
594 ', p_appl_short_name=>'||p_appl_short_name);
595 END IF;
596
597 IF is_multi_org_enabled <> 'Y' THEN
598 RETURN;
599 END IF;
600
601 IF p_org_id_char IS NULL AND p_sp_id_char IS NULL THEN
602 RAISE NO_SP_OU_FOUND;
603 ELSIF p_appl_short_name IS NULL THEN
604 RAISE NO_APPL_NAME; -- Should we seed a new mesg ???
605 END IF;
606 --
607 -- Replace this code with 10g shared globals
608 --
609 BEGIN
610 SELECT nvl(mpi.status, 'N')
611 INTO l_access_ctrl_enabled
612 FROM fnd_mo_product_init mpi
613 WHERE mpi.application_short_name = p_appl_short_name;
614 EXCEPTION
615 WHEN NO_DATA_FOUND THEN
616 fnd_message.set_name('FND','FND_MO_NO_APPL_NAME_FOUND'); -- raise error to
617 app_exception.raise_exception; -- enforce MO registration
618 WHEN OTHERS THEN
619 generic_error('MO_GLOBAL.SET_ORG_ACCESS', sqlcode, sqlerrm);
620 END;
621
622 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
623 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
624 'fnd.plsql.MO_GLOBAL.SET_ORG_ACCESS.access_status',
625 'Checking access status within PL/SQL procedure '||
626 'MO_GLOBAL.SET_ORG_ACCESS: '||
627 'l_access_ctrl_enabled=>'||l_access_ctrl_enabled);
628 END IF;
629
630 --
631 -- Delete temporary table data first for all products access enabled or not
632 --
633 delete_orgs;
634 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
635 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
636 'fnd.plsql.MO_GLOBAL.SET_ORG_ACCESS.after_delete',
637 'Returning from PL/SQL procedure '||
638 'MO_GLOBAL.DELETE_ORGS ');
639 END IF;
640 --
641 -- For all products, when the access control feature is enabled,
642 -- 1. Use the MO: Security Profile if it is set.
643 -- 2. Use the MO: Operating Unit if MO: Security Profile is not set
644 --
645 IF (l_access_ctrl_enabled = 'Y') THEN
646 IF l_security_profile_id IS NOT NULL THEN
647 l_org_id := null;
648 END IF;
649 --
650 -- Populate temp table
651 --
652 populate_orgs(l_org_id,
653 l_security_profile_id,
654 l_current_org_id,
655 l_view_all_orgs);
656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
658 'fnd.plsql.MO_GLOBAL.SET_ORG_ACCESS.After_Populate',
659 'Returning from PL/SQL procedure '||
660 'MO_GLOBAL.POPULATE_ORGS ');
661 END IF;
662 --
663 -- Check if you have access to at least one operating unit.
664 --
665 IF g_ou_count = 0 THEN
666 RAISE NO_ORG_ACCESS_FOUND;
667 ELSIF g_ou_count = 1 THEN
668 --
669 -- Set the 'Single' access contexts:
670 --
671 set_policy_context('S', l_current_org_id);
672 ELSE
673 --
674 -- Added code for All mode to avoid using the policy predicate
675 -- when user has access to global view all security profile
676 -- Bug (2720892)
677 -- Set the access contexts:
678 --
679 IF l_view_all_orgs = 'Y' THEN
680 set_policy_context('A','');
681 ELSE
682 set_policy_context('M','');
683 END IF;
684 END IF;
685 ELSE
686 IF l_org_id IS NOT NULL THEN
687 populate_orgs(l_org_id, -- Bug4475369 populate
688 null, -- 1 ou for S mode for the
689 l_current_org_id, -- timing being.
690 l_view_all_orgs);
691 set_policy_context('S',l_org_id);
692 END IF;
693 END IF;
694
695 commit;
696 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
697 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
698 'fnd.plsql.MO_GLOBAL.SET_ORG_ACCESS.end',
699 'Calling PL/SQL procedure MO_GLOBAL.SET_ORG_ACCESS:'||
700 ' p_org_id_char=>'||p_org_id_char||
701 ',p_sp_id_char=>'||p_sp_id_char||
702 ',p_appl_short_name=>'||p_appl_short_name||
703 ',l_view_all_orgs=>'||l_view_all_orgs||
704 ',g_ou_count=>'||g_ou_count);
705 END IF;
706
707 EXCEPTION
708 WHEN NO_ORG_ACCESS_FOUND THEN
709 fnd_message.set_name('FND','MO_ORG_ACCESS_NO_DATA_FOUND');
710 app_exception.raise_exception;
711 WHEN NO_SP_OU_FOUND THEN
712 fnd_message.set_name('FND','MO_ORG_ACCESS_NO_SP_OU_FOUND');
713 app_exception.raise_exception;
714 WHEN NO_APPL_NAME THEN
715 app_exception.raise_exception;
716 WHEN OTHERS THEN
717 generic_error('MO_GLOBAL.SET_ORG_ACCESS', sqlcode, sqlerrm);
721 PROCEDURE do_auto_set_org_access(p_org_id_char VARCHAR2,
718
719 END do_set_org_access;
720
722 p_sp_id_char VARCHAR2,
723 p_appl_short_name VARCHAR2)
724 IS
725 PRAGMA AUTONOMOUS_TRANSACTION;
726 begin
727 do_set_org_access(p_org_id_char,p_sp_id_char,p_appl_short_name);
728 end;
729
730 PROCEDURE set_org_access(p_org_id_char VARCHAR2,
731 p_sp_id_char VARCHAR2,
732 p_appl_short_name VARCHAR2)
733 IS
734 begin
735
736 if ( fnd_adg_support.is_standby )
737 then
738 do_set_org_access(p_org_id_char,p_sp_id_char,p_appl_short_name);
739 else
740 do_auto_set_org_access(p_org_id_char,p_sp_id_char,p_appl_short_name);
741 end if;
742
743 end;
744
745
746 --
747 -- Name
748 -- jtt_init
749 -- Purpose
750 -- Initialization code for Organization Security Policy. This is
751 -- mainly called from JTT java API's.
752 -- This will call the init API and will also initialize ICX sesion attribute
753 -- JTTCURRENTORG when temp table has only one record
754 --
755 PROCEDURE jtt_init(p_appl_short_name IN VARCHAR2,
756 p_icx_session_id IN NUMBER)
757 IS
758 begin
759 init(p_appl_short_name,'Y');
760
761 if g_current_org_id is not null
762 then
763 fnd_session_management.putSessionAttributeValue(p_name => 'JTTCURRENTORG',
764 p_value => g_current_org_id,
765 p_session_id => p_icx_session_id);
766 end if;
767 end jtt_init;
768
769
770 --
771 -- Name
772 -- clear_current_org_context
773 -- Purpose
774 -- This procedure clears the current org context in database session as
775 -- well as reset the ICX session attribute JTTCURRENTORG
776 --
777 PROCEDURE clear_current_org_context(p_icx_session_id IN NUMBER)
778 IS
779 BEGIN
780 dbms_session.set_context('multi_org2', 'current_org_id', '');
781 fnd_session_management.clearSessionAttributeValue(p_name => 'JTTCURRENTORG',
782 p_session_id => p_icx_session_id);
783 g_current_org_id := NULL;
784 END;
785
786 --
787 -- Name
788 -- init
789 -- Purpose
790 -- Initialization code for Organization Security Policy
791 --
792 PROCEDURE init(p_appl_short_name VARCHAR2)
793 IS
794 begin
795 init(p_appl_short_name,'Y');
796 end init;
797
798 PROCEDURE init(p_appl_short_name VARCHAR2, p_sync VARCHAR2)
799 IS
800
801 l_security_profile_id fnd_profile_option_values.profile_option_value%TYPE := NULL;
802 l_org_id fnd_profile_option_values.profile_option_value%TYPE := NULL;
803
804 BEGIN
805 --
806 -- Check if multi-org is enabled
807 --
808 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
809 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
810 'fnd.plsql.MO_GLOBAL.INIT.begin',
811 'Calling PL/SQL procedure MO_GLOBAL.INIT');
812 END IF;
813 IF is_multi_org_enabled = 'Y' THEN
814 --
815 -- Get the profile values and call set_org_access API
816 --
817 fnd_profile.get('XLA_MO_SECURITY_PROFILE_LEVEL', l_security_profile_id);
818 fnd_profile.get('ORG_ID', l_org_id);
819
820 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
821 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
822 'fnd.plsql.MO_GLOBAL.INIT.config',
823 'MO: Operating Unit=>'||l_org_id||
824 ', MO: Security Profile=>'||l_security_profile_id||
825 ', p_appl_short_name=>'||p_appl_short_name);
826 END IF;
827 IF p_sync = 'Y' THEN
828 g_sync := 'Y';
829 ELSE
830 g_sync := 'N';
831 END IF;
832 set_org_access(l_org_id, l_security_profile_id, p_appl_short_name);
833
834 -- store profile and org id in global variables
835 -- used for checking if new initialization is to be done
836 -- in is_mo_init_done API
837 g_old_sp_id:=l_security_profile_id;
838 IF g_old_sp_id IS NOT NULL THEN
839 g_old_org_id:=NULL;
840 ELSE
841 g_old_org_id:=l_org_id;
842 END IF;
843
844 END IF; -- multi org is enabled
845 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
846 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
847 'fnd.plsql.MO_GLOBAL.INIT.end',
848 'Exiting PL/SQL procedure MO_GLOBAL.INIT');
849 END IF;
850 EXCEPTION
851 WHEN others THEN
852 generic_error('MO_GLOBAL.INIT', sqlcode, sqlerrm);
853 END init;
854
855
856 --
857 -- Name
858 -- org_security
859 --
860 -- Purpose
861 -- This function implements the security policy for the Multi-Org
862 -- Access Control mechanism. It is automatically called by the oracle
863 -- server whenever a secured table or view is referenced by a SQL
864 -- statement. Products should not call this function directly.
865 --
866 -- The security policy function is expected to return a predicate
867 -- (a WHERE clause) that will control which records can be accessed
868 -- or modified by the SQL statement. After incorporating the
869 -- predicate, the server will parse, optimize and execute the
870 -- modified statement.
871 --
872 -- Arguments
873 -- obj_schema - the schema that owns the secured object
874 -- obj_name - the name of the secured object
875 --
876 FUNCTION org_security(obj_schema VARCHAR2,
877 obj_name VARCHAR2) RETURN VARCHAR2
878 IS
879 l_ci_debug fnd_profile_option_values.profile_option_value%TYPE := NULL;
880 BEGIN
881
882 --
883 -- Returns different predicates based on the access_mode
884 -- The codes for access_mode are
885 -- M - Multiple OU Access
886 -- A - All OU Access
887 -- S - Single OU Access
888 -- Null - Backward Compatibility - CLIENT_INFO case
889 --
890 -- The Predicates will be appended to Multi-Org synonyms
891
892 IF obj_name = 'AR_PAYMENT_SCHEDULES' and g_access_mode='S' THEN
893 RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'') OR (org_id = -3116)';
894
895 ELSIF g_access_mode IS NOT NULL THEN
896 IF g_access_mode = 'M' THEN
897
898 $if fnd_adg_compile_directive.enable_rpc
899 $then
900 if ( fnd_adg_support.is_standby )
901 then
902 RETURN 'EXISTS (SELECT /*+ no_unnest */ 1 --bug 13891445
903 FROM mo_glob_org_access_tmp_remote oa
904 WHERE oa.organization_id = org_id)';
905 end if;
906 $end
907
908 RETURN 'EXISTS (SELECT /*+ no_unnest */ 1 --bug 13891445
909 FROM mo_glob_org_access_tmp oa
910 WHERE oa.organization_id = org_id)';
911
912 ELSIF g_access_mode in ('A','B') THEN
913 RETURN 'org_id <> -3113'; -- Bug5109430 filter seed data from policy predicate
914 ELSIF g_access_mode = 'S' THEN
915 RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'')';
916 ELSIF g_access_mode = 'X' THEN
917 RETURN '1 = 2';
918 END IF;
919
920 ELSE -- This section is used reserved for debugging using CLIENT_INFO
921
922 --
923 -- Interim solution for MFG teams
924 --
925 fnd_profile.get('FND_MO_INIT_CI_DEBUG', l_ci_debug);
926 IF l_ci_debug = 'Y' THEN
927 RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
928 ELSE
929 RETURN '1=2';
930 END IF;
931
932 END IF;
933
934 END org_security;
935
936
937 --
938 -- Name
939 -- set_org_context
940 -- Purpose
941 -- Wrapper procedure for setting up the Operating Unit context in the client
942 -- info area and organization access list for Multi-Org Access Control for CRM
943 -- introduced in 11i.1
944 --
945 -- Arguments
946 -- p_org_id_char - org_id for the operating unit; can be up to 10
947 -- bytes long
948 -- p_sp_id_char - MO: Security profile id
949 -- p_appl_short_name - Application owner for the current module or session
950 --
951 PROCEDURE set_org_context(p_org_id_char VARCHAR2,
952 p_sp_id_char VARCHAR2,
953 p_appl_short_name VARCHAR2) is
954
955 l_ci_debug fnd_profile_option_values.profile_option_value%TYPE := NULL;
956
957 BEGIN
958 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
959 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
960 'fnd.plsql.MO_GLOBAL.INIT.config',
961 'MO: Operating Unit=>'||p_org_id_char||
962 ',MO: Security Profile=>'||p_sp_id_char||
963 ',p_appl_short_name=>'||p_appl_short_name);
964 END IF;
965
966 fnd_profile.get('FND_MO_INIT_CI_DEBUG', l_ci_debug);
967 -- Set up the Operating Unit context in the client info area
968 IF l_ci_debug = 'Y' THEN
969 fnd_client_info.set_org_context(p_org_id_char);
970 END IF;
971
972 -- Set up the organization access list for Multi- Org Access Control
973 set_org_access(p_org_id_char,p_sp_id_char, p_appl_short_name);
974
975 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
976 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
977 'fnd.plsql.MO_GLOBAL.SET_ORG_CONTEXT.end',
978 'Returning from PL/SQL procedure MO_GLOBAL.SET_ORG_CONTEXT');
979 END IF;
980
981 END set_org_context;
982
983
984 --
985 -- Name
986 -- check_access
987 -- Purpose
988 -- Checks
989 -- 1. if an Operating Unit exists in the PL/SQL array.
990 -- The PL/SQL array is populated by the set_org_access Multi-Org API.
991 -- 2. if Operating Unit is same as current org id for 'S'ingle org initialization
995 --
992 --
993 -- Arguments
994 -- p_org_id - org_id for the Operating Unit
996 FUNCTION check_access(p_org_id NUMBER)
997 RETURN VARCHAR2 IS
998
999 l_org_exists varchar2(1);
1000
1001 BEGIN
1002 IF g_access_mode = 'A' THEN
1003 -- if access mode is ALL then return true
1004 RETURN 'Y';
1005
1006 ELSIF (GET_OU_COUNT > 1) OR (g_access_mode = 'M') THEN -- added g_access_mode for Bug4575131
1007 -- if mo initialization is done
1008
1009 $if fnd_adg_compile_directive.enable_rpc
1010 $then
1011 if ( fnd_adg_support.is_standby )
1012 then
1013
1014 SELECT 'Y'
1015 INTO l_org_exists
1016 FROM mo_glob_org_access_tmp_remote
1017 WHERE organization_id = p_org_id;
1018 RETURN 'Y';
1019
1020 end if;
1021 $end
1022
1023 SELECT 'Y'
1024 INTO l_org_exists
1025 FROM mo_glob_org_access_tmp
1026 WHERE organization_id = p_org_id;
1027 RETURN 'Y';
1028
1029 ELSIF GET_CURRENT_ORG_ID IS NOT NULL THEN
1030 -- if mo initialization is not done but context is set to 'S'
1031
1032 IF P_ORG_ID = GET_CURRENT_ORG_ID THEN
1033 RETURN 'Y';
1034 END IF;
1035
1036 END IF;
1037
1038 RETURN 'N';
1039
1040 EXCEPTION
1041 WHEN NO_DATA_FOUND THEN
1042 RETURN 'N';
1043 WHEN VALUE_ERROR THEN
1044 RETURN 'N';
1045 END;
1046
1047 --
1048 -- Name
1049 -- get_ou_name
1050 -- Purpose
1051 -- This function returns the Operating Unit name for the org_id parameter
1052 -- passed, if it exists in the temporary table populated by
1053 -- set_org_access Multi-Org API.
1054 --
1055 -- Arguments
1056 -- p_org_id - org_id for the Operating Unit
1057 --
1058 FUNCTION get_ou_name(p_org_id NUMBER)
1059 RETURN VARCHAR2 IS
1060
1061 l_ou_name mo_glob_org_access_tmp.organization_name%TYPE;
1062
1063 BEGIN
1064
1065 $if fnd_adg_compile_directive.enable_rpc
1066 $then
1067 if ( fnd_adg_support.is_standby )
1068 then
1069
1070 SELECT organization_name
1071 INTO l_ou_name
1072 FROM mo_glob_org_access_tmp_remote
1073 WHERE organization_id = p_org_id;
1074 RETURN l_ou_name;
1075
1076 end if;
1077 $end
1078
1079 SELECT organization_name
1080 INTO l_ou_name
1081 FROM mo_glob_org_access_tmp
1082 WHERE organization_id = p_org_id;
1083 RETURN l_ou_name;
1084
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 RETURN NULL;
1088 WHEN VALUE_ERROR THEN
1089 RETURN NULL;
1090 END;
1091
1092 --
1093 -- Name
1094 -- check_valid_org
1095 -- Purpose
1096 -- Checks if the specified operating unit exists in the session's
1097 -- access control list. This function is equivalent to the
1098 -- check_access function but also posts an error message if the
1099 -- specified operating unit is null or not in the access list.
1100 -- The calling application can check the returned value of the
1101 -- function and raise an error if it is 'N'.
1102 --
1103 -- Arguments
1104 -- p_org_id - org_id for the Operating Unit
1105 --
1106 FUNCTION check_valid_org(p_org_id NUMBER) RETURN VARCHAR2
1107 IS
1108
1109 BEGIN
1110 IF (p_org_id is null) THEN
1111 -- Post an error message and return:
1112 fnd_message.set_name('FND', 'MO_ORG_REQUIRED');
1113 FND_MSG_PUB.ADD;
1114 RETURN 'N';
1115 END IF;
1116
1117 IF (check_access(p_org_id) = 'Y') THEN
1118 RETURN 'Y';
1119 END IF;
1120
1121 -- Post an error message and return:
1122 fnd_message.set_name('FND', 'MO_ORG_INVALID');
1123 FND_MSG_PUB.ADD;
1124 RETURN 'N';
1125 END;
1126
1127 --
1128 -- Name
1129 -- set_policy_context
1130 -- Purpose
1131 -- Sets the application context for the current org and the access
1132 -- mode to be used in server side code for validations as well as in
1133 -- the Multi-Org security policy function.
1134 --
1135 -- Arguments
1136 -- p_access_mode - specifies the operating unit access. 'S' for
1137 -- Single, 'M' for Multiple, 'A' for All.
1138 -- - Access Mode All is resrved for future use.
1139 -- - X is used to prevent returning any data from synonym
1140 -- p_org_id - org_id of the operating unit.
1141 --
1142 PROCEDURE set_policy_context(p_access_mode VARCHAR2,
1143 p_org_id NUMBER)
1144 IS
1145
1146 BEGIN
1147 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1149 'fnd.plsql.MO_GLOBAL.SET_POLICY_CONTEXT.begin',
1150 'Calling PL/SQL procedure MO_GLOBAL.SET_POLICY_CONTEXT:'||
1151 ' p_access_mode=>'||p_access_mode||
1152 ',p_org_id=>'||p_org_id);
1153 END IF;
1154 --
1155 -- Get the present values of access mode and current org id
1156 --
1157 IF (p_access_mode = g_access_mode
1158 and p_org_id = g_current_org_id
1159 and sys_context('multi_org2','current_org_id') = p_org_id) THEN
1160
1161 NULL; -- Bug5582505: quick exit if nothing to be reset
1162 ELSIF (p_access_mode = 'S') THEN
1163 IF (g_access_mode is NULL OR g_access_mode <> 'S') THEN
1164 --
1165 -- If single operating unit access, then mode should be set to 'S'
1166 --
1167 dbms_session.set_context('multi_org', 'access_mode', p_access_mode);
1171 OR sys_context('multi_org2','current_org_id') <> p_org_id -- Bug4916086
1168 g_access_mode := p_access_mode;
1169 END IF;
1170 IF (g_current_org_id IS NULL OR g_current_org_id <> p_org_id
1172 OR sys_context('multi_org2','current_org_id') is null) THEN
1173 --
1174 -- Set the current org context
1175 --
1176 dbms_session.set_context('multi_org2', 'current_org_id', p_org_id);
1177 g_current_org_id := p_org_id;
1178 -- Bug 7227733 Passing current org id to FND
1179 fnd_global.initialize('ORG_ID',g_current_org_id);
1180
1181 END IF;
1182
1183 ELSIF (p_access_mode = 'M') THEN
1184 IF (g_access_mode is NULL OR g_access_mode <> 'M') THEN
1185 --
1186 -- If multiple operating units access, then mode should be set to 'M'
1187 --
1188 dbms_session.set_context('multi_org', 'access_mode', p_access_mode);
1189 g_access_mode := p_access_mode;
1190 END IF;
1191 IF (g_current_org_id IS NOT NULL ) THEN
1192 --
1193 -- Unset the current org context, since it is not required for multiple
1194 -- access
1195 --
1196 dbms_session.set_context('multi_org2', 'current_org_id', '');
1197 g_current_org_id := NULL;
1198 -- Bug 7227733 Passing current org id to FND
1199 fnd_global.initialize('ORG_ID',g_current_org_id);
1200 END IF;
1201
1202 ELSIF (p_access_mode = 'A') and g_init_access_mode = 'A' THEN
1203 IF (g_access_mode is NULL OR g_access_mode <> 'A') THEN
1204 --
1205 -- If all operating units access, then mode should be set to 'A'
1206 --
1207 dbms_session.set_context('multi_org', 'access_mode', p_access_mode);
1208 g_access_mode := p_access_mode;
1209 END IF;
1210 IF (g_current_org_id IS NOT NULL ) THEN
1211 --
1212 -- Unset the current org context, since it is not required for all
1213 -- access
1214 --
1215 dbms_session.set_context('multi_org2', 'current_org_id', '');
1216 g_current_org_id := NULL;
1217 -- Bug 7227733 Passing current org id to FND
1218 fnd_global.initialize('ORG_ID',g_current_org_id);
1219 END IF;
1220
1221 ELSIF (p_access_mode in ('X','B')) THEN
1222 if sys_context('multi_org2','current_org_id') is not null then
1223 dbms_session.set_context('multi_org2', 'current_org_id', '');
1224 end if;
1225
1226 dbms_session.set_context('multi_org', 'access_mode', p_access_mode);
1227 g_current_org_id := NULL;
1228 g_access_mode := p_access_mode;
1229
1230 ELSIF (p_access_mode is NULL) THEN
1231 IF (g_access_mode IS NOT NULL) THEN
1232 --
1233 -- If access_mode is not passed, then unset it
1234 --
1235 dbms_session.set_context('multi_org', 'access_mode', p_access_mode);
1236 g_access_mode := p_access_mode;
1237 END IF;
1238 IF (g_current_org_id IS NOT NULL ) THEN
1239 --
1240 -- Unset the current org context, since it is not required when mode
1241 -- is not set
1242 --
1243 dbms_session.set_context('multi_org2', 'current_org_id', '');
1244 g_current_org_id := NULL;
1245 -- Bug 7227733 Passing current org id to FND
1246 fnd_global.initialize('ORG_ID',g_current_org_id);
1247 END IF;
1248
1249 END IF;
1250
1251 --
1252 -- store the user and resp. IDs, to be used for checking new OA user session
1253 -- in is_mo_init_done API
1254 --
1255 g_old_user_id:=sys_context('FND','USER_ID');
1256 g_old_resp_id:=sys_context('FND','RESP_ID');
1257
1258 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1259 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1260 'fnd.plsql.MO_GLOBAL.SET_POLICY_CONTEXT.end',
1261 'Returning from PL/SQL prcedure MO_GLOBAL.SET_POLICY_CONTEXT');
1262 END IF;
1263
1264 END set_policy_context;
1265
1266 --
1267 -- Name
1268 -- get_current_org_id
1269 -- Purpose
1270 -- This function returns the current_org_id stored in the application
1271 -- context.
1272 --
1273 FUNCTION get_current_org_id RETURN NUMBER
1274 IS
1275
1276 BEGIN
1277 RETURN to_number(g_current_org_id);
1278 EXCEPTION
1279 WHEN NO_DATA_FOUND THEN
1280 RETURN NULL;
1281 WHEN VALUE_ERROR THEN
1282 RETURN NULL;
1283 END get_current_org_id;
1284
1285 --
1286 -- Name
1287 -- get_access_mode
1288 -- Purpose
1289 -- This function returns the access mode stored in the application
1290 -- context.
1291 --
1292 FUNCTION get_access_mode RETURN VARCHAR2
1293 IS
1294
1295 BEGIN
1296 RETURN (g_access_mode);
1297 EXCEPTION
1298 WHEN NO_DATA_FOUND THEN
1299 RETURN NULL;
1300 WHEN VALUE_ERROR THEN
1301 RETURN NULL;
1302 END get_access_mode;
1303
1304
1305 --
1306 -- Name
1307 -- get_ou_count
1308 -- Purpose
1309 -- This function returns the count of the records stored in the Multi-Org
1310 -- temporary table.
1311 --
1312 FUNCTION get_ou_count RETURN NUMBER
1313 IS
1314
1315 BEGIN
1316 RETURN (g_ou_count);
1317 EXCEPTION
1318 WHEN NO_DATA_FOUND THEN
1319 RETURN 0;
1320 WHEN VALUE_ERROR THEN
1321 RETURN 0;
1322 END get_ou_count;
1323
1324 --
1325 -- Name
1326 -- get_valid_org
1327 -- Purpose
1328 -- This function determines and returns the valid ORG_ID.
1329 --
1330 FUNCTION get_valid_org(p_org_id NUMBER) RETURN NUMBER
1331 IS
1332
1333 l_org_id NUMBER;
1334 l_status VARCHAR2(1);
1335
1339 --
1336 BEGIN
1337 --
1338 -- Debug information
1340 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1341 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1342 'fnd.plsql.MO_GLOBAL.GET_VALID_ORG.begin',
1343 'Calling PL/SQL function '||
1344 'MO_GLOBAL.GET_VALID_ORG'||
1345 ' p_org_id=>'||p_org_id);
1346 END IF;
1347
1348 --
1349 -- Obtain org ID in the following order:
1350 -- 1. parameter from caller
1351 -- 2. current org ID
1352 -- 3. default org ID
1353 --
1354 IF (p_org_id = FND_API.G_MISS_NUM) THEN
1355 --
1356 -- If p_org_id is G_MISS_NUM (org id is not passed in), then get the org_id
1357 -- from current org_id. If that is also not available, get the default
1358 -- org_id
1359 --
1360 l_org_id := NVL(mo_global.get_current_org_id,
1361 mo_utils.get_default_org_id);
1362 ELSE
1363 --
1364 -- If p_org_id is null or different from G_MISS_NUM
1365 -- use explicitly passed in org_id
1366 --
1367 l_org_id := p_org_id;
1368 END IF;
1369
1370 --
1371 -- Now validate the org ID
1372 --
1373 l_status := check_valid_org(l_org_id);
1374
1375 --
1376 -- If the org_id is valid, return it. If it's invalid, return NULL
1377 --
1378 IF (l_status = 'N') THEN
1379 --
1380 -- Debug information
1381 --
1382 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1383 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1384 'fnd.plsql.MO_GLOBAL.GET_VALID_ORG.end',
1385 'Returning from PL/SQL function '||
1386 'MO_GLOBAL.GET_VALID_ORG:'||
1387 ' Returns NULL');
1388 END IF;
1389
1390 --
1391 -- Org_id is invalid
1392 --
1393 RETURN NULL;
1394
1395 ELSE
1396 --
1397 -- Debug information
1398 --
1399 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1400 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1401 'fnd.plsql.MO_GLOBAL.GET_VALID_ORG.end',
1402 'Returning from PL/SQL function '||
1403 'MO_GLOBAL.GET_VALID_ORG:'||
1404 ' Returns '||l_org_id);
1405 END IF;
1406
1407 --
1408 -- Org_id is valid
1409 --
1410 RETURN l_org_id;
1411
1412 END IF;
1413
1414 EXCEPTION
1415 WHEN OTHERS THEN
1416 generic_error('MO_GLOBAL.Get_Valid_Org', sqlcode, sqlerrm);
1417
1418 END get_valid_org;
1419
1420 -- validate_orgid_pub_api
1421 -- to be used in public API's for backword compatibilty
1422 --
1423 -- STATUS is 'S'uccess if org_id passed was
1424 -- 1. either valid w/ MO:SP or CURRENT ORG or MO:OU
1425 -- OR
1426 -- 2. we have derived from CURRENT ORG or MO:Def OU or MO:OU
1427 --
1428 -- STATUS is 'F'ailure if org_id passed was
1429 -- 1. either invalid w/ both MO:SP and CURRENT ORG and MO:OU
1430 -- OR
1431 -- 2. we could not derive that
1432 --
1433 -- To suppress the error pass ERROR_MESG_SUPPR as 'Y'
1434 -- arguments
1435 -- ORG_ID org_id for Operating Unit
1436 -- ERROR_MESG_SUPPR error message suppresser
1437 -- STATUS validation/derivation result
1438 --
1439
1440 PROCEDURE validate_orgid_pub_api(ORG_ID IN OUT NOCOPY NUMBER,
1441 ERROR_MESG_SUPPR IN VARCHAR2 DEFAULT 'N',
1442 STATUS OUT NOCOPY VARCHAR2)
1443 IS
1444 l_org_id number(15);
1445 ORG_ID_INVALID_OR_NON_DRV EXCEPTION;
1446
1447 BEGIN
1448
1449
1450 /* May consider the following logic to execute MO init w/in the proc in future.
1451
1452 IF g_ou_count = 0 AND
1453 FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL') is NOT NULL THEN
1454 mo_global.init('M');
1455
1456 ELSIF g_ou_count = 0 AND
1457 FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL') is NULL AND
1458 FND_PROFILE.VALUE('ORG_ID') is NOT NULL THEN
1459 mo_global.init('S');
1460 END IF;
1461 */
1462
1463 STATUS := 'F'; -- initialize the variable to F
1464
1465 IF FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL') is NOT NULL
1466 AND g_ou_count = 0 THEN
1467 FND_MESSAGE.SET_NAME('FND','FND_MO_NOINIT_SP_PUB_API');
1468 FND_MSG_PUB.ADD;
1469 APP_EXCEPTION.RAISE_EXCEPTION;
1470 END IF;
1474 STATUS:='F';
1471
1472 -- if org_id is passed explicitly
1473 IF ORG_ID IS NOT NULL AND ORG_ID <> FND_API.G_MISS_NUM THEN
1475 -- check if org_id passed is valid with
1476 -- 1. temp table
1477 -- 2. current_org_id
1478 -- 3. MO: OU
1479
1480 -- if mo init is done either w/ 'M'ultiple or 'S'ingle
1481 -- check if org_id is valid with orgs in temp table or in the current org
1482
1483 IF g_ou_count >=1 THEN
1484 IF CHECK_ACCESS(ORG_ID) = 'Y' THEN
1485 STATUS:='S';
1486 ELSE
1487 IF ERROR_MESG_SUPPR = 'N' THEN
1488 FND_MESSAGE.SET_NAME('FND','FND_MO_INVALID_OU_API');
1489 FND_MESSAGE.SET_TOKEN('ORG_NAME', mo_utils.get_org_name(ORG_ID));
1490 FND_MESSAGE.SET_TOKEN('ORG_ID', ORG_ID);
1491 FND_MSG_PUB.ADD;
1492 APP_EXCEPTION.RAISE_EXCEPTION;
1493 END IF;
1494 END IF;
1495
1496 ELSIF FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL') is NULL THEN
1497 -- mo initialization is not done.
1498 -- check if org_id passed id valid with MO:OU
1499 -- for backword compatibilty
1500 FND_PROFILE.GET('ORG_ID',l_org_id);
1501 IF ORG_ID = l_org_id THEN
1502 set_policy_context('S',l_org_id); -- setting org context for synonym
1503 STATUS := 'O';
1504
1505 END IF;
1506
1507 END IF;
1508
1509 ELSE -- org_id value is not passed in explicitly.
1510 -- try getting the org_id from
1511 -- 1. current org id
1512 -- 2. MO: Def OU
1513 -- 3. MO: OU
1514 STATUS:='F';
1515 -- looking here for current org id otherwise default OU
1516 -- if initialization is done
1517 IF g_ou_count >= 1 THEN
1518 ORG_ID := mo_global.get_current_org_id;
1519 IF ORG_ID is NOT NULL THEN
1520 STATUS := 'C';
1521 ELSE
1522 ORG_ID := GET_VALID_ORG(FND_API.G_MISS_NUM);
1523 IF ORG_ID is NOT NULL THEN
1524 STATUS := 'D';
1525 END IF;
1526 END IF;
1527
1528 -- for backword compatibility support. Return status O
1529 ELSIF FND_PROFILE.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL') is NULL THEN
1530 FND_PROFILE.GET('ORG_ID',l_org_id);
1531 ORG_ID := l_org_id;
1532 set_policy_context('S',l_org_id); -- setting org context for synonym
1533 STATUS := 'O';
1534 END IF;
1535
1536 END IF;
1537
1538
1539 IF STATUS='F' AND ERROR_MESG_SUPPR = 'N' THEN
1540 FND_MESSAGE.SET_NAME('FND','FND_MO_INVALID_OU_PUB_API');
1541 FND_MSG_PUB.ADD;
1542 APP_EXCEPTION.RAISE_EXCEPTION;
1543 -- RAISE ORG_ID_INVALID_OR_NON_DRV;
1544 END IF;
1545
1546 EXCEPTION
1547 WHEN ORG_ID_INVALID_OR_NON_DRV THEN
1548 FND_MESSAGE.SET_NAME('FND','FND_MO_INVALID_OU_PUB_API');
1549 APP_EXCEPTION.RAISE_EXCEPTION;
1550 WHEN others THEN
1551 STATUS:='F';
1552 generic_error('MO_GLOBAL.VALIDATE_ORGID_PUB_API', sqlcode, sqlerrm);
1553
1554 END validate_orgid_pub_api;
1555
1556 -- Name: is_mo_init_done
1557 -- Purpose: check if MO initialization is done
1558 -- if OA user session is different then check if SP is same, return Y if same
1559 -- Order is
1560 -- Temp table -> Current Org -> Access Mode (e.g S, M or A)
1561 --
1562
1563 FUNCTION is_mo_init_done RETURN VARCHAR2
1564 IS
1565 l_current_sp_id fnd_profile_option_values.profile_option_value%TYPE := NULL;
1566 l_current_org_id fnd_profile_option_values.profile_option_value%TYPE := NULL;
1567 l_user_id NUMBER:=NULL;
1568 l_resp_id NUMBER:=NULL;
1569 BEGIN
1570 --
1571 -- bug#5677563 - check for different user sessions, if SP/OU is same
1572 -- if SP is not same application should re-initialize the MOAC
1573 -- hence return 'N'
1574 --
1575
1576 fnd_profile.get('XLA_MO_SECURITY_PROFILE_LEVEL', l_current_sp_id);
1577 fnd_profile.get('ORG_ID', l_current_org_id);
1578
1579 l_user_id:=sys_context('FND','USER_ID');
1580 l_resp_id:=sys_context('FND','RESP_ID');
1581
1582 IF (g_ou_count >= 1) THEN
1583 IF l_current_sp_id IS NOT NULL AND l_current_sp_id <> FND_API.G_MISS_NUM THEN
1584 IF nvl(g_old_sp_id,-1) <> l_current_sp_id THEN
1585 return 'N';
1586 END IF;
1587 ELSIF l_current_org_id IS NOT NULL AND l_current_org_id <> FND_API.G_MISS_NUM THEN
1588 IF nvl(g_old_org_id,-1) <> l_current_org_id THEN
1589 return 'N';
1590 END IF;
1591 ELSE
1592 return 'N';
1593 END IF;
1594 ELSIF( g_current_org_id is not null OR g_access_mode = 'A' )THEN
1595 IF (nvl(g_old_user_id,-1) <> l_user_id ) OR (nvl(g_old_resp_id,-1) <> l_resp_id) THEN
1596 return 'N';
1597 END IF;
1598 ELSE
1599 return 'N';
1600
1601 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1602 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1603 'fnd.plsql.MO_GLOBAL.is_mo_init_done.begin',
1604 'g_ou_count=>'||g_ou_count||
1605 ', g_access_mode=>'||g_access_mode||
1606 ', g_current_org_id=>'||g_current_org_id||
1607 ', g_init_access_mode=>'||g_init_access_mode);
1608 END IF;
1609
1610 End IF;
1611 return 'Y';
1612
1613
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 generic_error('MO_GLOBAL.IS_MO_INIT_DONE', sqlcode, sqlerrm);
1617 END is_mo_init_done;
1618
1619 -- Name
1620 -- org_security_global function
1621 -- Purpose
1622 -- This is a restricted policy function to support global data -3116.
1623 FUNCTION org_security_global(obj_schema VARCHAR2,
1624 obj_name VARCHAR2) RETURN VARCHAR2
1625 IS
1626 l_ci_debug fnd_profile_option_values.profile_option_value%TYPE := NULL;
1627 BEGIN
1628
1629 --
1630 -- Returns different predicates based on the access_mode
1631 -- The codes for access_mode are
1632 -- M - Multiple OU Access
1633 -- A - All OU Access
1634 -- S - Single OU Access
1635 -- Null - Backward Compatibility - CLIENT_INFO case
1636 --
1637 -- The Predicates will be appended to Multi-Org synonyms
1638
1639 IF g_access_mode IS NOT NULL THEN
1640 IF g_access_mode = 'S' THEN
1641 RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'') OR (org_id = -3116)';
1642
1643 ELSIF g_access_mode = 'M' THEN
1644
1645 $if fnd_adg_compile_directive.enable_rpc
1646 $then
1647 if ( fnd_adg_support.is_standby )
1648 then
1649 RETURN '(EXISTS (SELECT 1
1650 FROM mo_glob_org_access_tmp_remote oa
1651 WHERE oa.organization_id = org_id))
1652 OR (org_id = -3116)';
1653 end if;
1654 $end
1655 RETURN '(EXISTS (SELECT 1
1656 FROM mo_glob_org_access_tmp oa
1657 WHERE oa.organization_id = org_id))
1658 OR (org_id = -3116)';
1659 ELSIF g_access_mode in ('A','B') THEN
1660 RETURN 'org_id <> -3113'; -- Bug5109430 filter seed data from policy predicate
1661 ELSIF g_access_mode = 'X' THEN
1662 RETURN '1 = 2';
1663
1664 END IF;
1665
1666 ELSE
1667 --
1668 -- Interim solution for MFG teams
1669 --
1670 fnd_profile.get('FND_MO_INIT_CI_DEBUG', l_ci_debug);
1671 IF l_ci_debug = 'Y' THEN
1672 RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
1673 ELSE
1674 RETURN '1=2';
1675 END IF;
1676
1677 END IF;
1678
1679 END org_security_global;
1680
1681 --
1682 -- Name
1683 -- get_ou_tab
1684 -- Purpose
1685 -- This function returns a table that contains the
1686 -- identifiers of all the accessible operating units.
1687 --
1688 FUNCTION get_ou_tab RETURN OrgIdTab
1689 IS
1690 BEGIN
1691 -- use memory instead of hitting the table
1692 -- select organization_id BULK COLLECT INTO l_ou_id_tab from mo_glob_org_access_tmp;
1693 RETURN g_ou_id_tab;
1694 END get_ou_tab;
1695
1696 --
1697 -- Name
1698 -- set_policy_context_server
1699 -- Purpose
1700 -- This wrapper is called from Forms client-side library to synchronize
1701 -- current_org_id variable w/ :GLOBAL.current_org_id when set_policy_context
1702 -- API is invoked in Forms.
1703 --
1704 PROCEDURE set_policy_context_server(p_access_mode VARCHAR2,
1705 p_org_id NUMBER)
1706 IS
1707
1708 BEGIN
1709
1710 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1711 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1712 'fnd.plsql.MO_GLOBAL.SET_POLICY_CONTEXT_SERVER.begin',
1713 'Calling PL/SQL procedure MO_GLOBAL.SET_POLICY_CONTEXT_SERVER:'||
1714 ' p_access_mode=>'||p_access_mode||
1715 ',p_org_id=>'||p_org_id);
1716 END IF;
1717
1718 MO_GLOBAL.set_policy_context(p_access_mode, p_org_id); -- Force server-side to sync
1719
1720 END set_policy_context_server;
1721
1722 --
1723 -- Name
1724 -- populate_organizations
1725 -- Purpose
1726 -- This is a wrapper API to populate_orgs called
1727 -- from FND_CONCURRENT API. Not to be used for
1728 -- any other purpose
1729 --
1730
1731
1732 Procedure populate_organizations(p_org_id_char IN VARCHAR2,
1733 p_sp_id_char IN VARCHAR2,
1734 p_current_org_id OUT NOCOPY VARCHAR2,
1735 p_view_all_org OUT NOCOPY VARCHAR2)
1736
1737 IS
1738 BEGIN
1739
1740 populate_orgs (p_org_id_char,
1741 p_sp_id_char,
1742 p_current_org_id,
1743 p_view_all_org);
1744 END populate_organizations;
1745
1746
1747 --
1748 -- Name
1749 -- delete_organizations
1750 -- Purpose
1751 -- This is a wrapper API to delete_orgs called
1752 -- from FND_CONCURRENT API. Not to be used for
1753 -- any other purpose
1754 --
1755 PROCEDURE delete_organizations
1756 IS
1757 BEGIN
1758 delete_orgs;
1759
1760 END delete_organizations;
1761
1762
1763 END mo_global;
[Home] [Help]