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