1 PACKAGE BODY MO_UTILS AS
2 /* $Header: AFMOUTLB.pls 120.3 2005/11/17 13:27:13 sryu noship $ */
3
4
5
6 --
7 -- Generic_Error (Internal)
8 --
9 -- Set error message and raise exception for unexpected sql errors.
10 --
11 PROCEDURE Generic_Error
12 ( routine IN VARCHAR2
13 , errcode IN NUMBER
14 , errmsg IN VARCHAR2
15 )
16 IS
17 BEGIN
18 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
19 fnd_message.set_token('ROUTINE', routine);
20 fnd_message.set_token('ERRNO', errcode);
21 fnd_message.set_token('REASON', errmsg);
22 app_exception.raise_exception;
23
24 EXCEPTION
25 WHEN OTHERS THEN RAISE;
26 END;
27
28 --
29 -- Get Set_Of_Books_Name
30 --
31 FUNCTION Get_Set_Of_Books_Name
32 ( p_operating_unit IN NUMBER
33 )
34 RETURN VARCHAR2 IS
35
36 BEGIN
37
38
39 RETURN Get_Ledger_Name(p_operating_unit);
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 Generic_Error( 'MO_UTILS.Get_Set_of_Books_Name'
44 , sqlcode
45 , sqlerrm);
46
47 END Get_Set_Of_Books_Name;
48
49
50 --
51 -- Get Set_Of_Books_Info
52 --
53 PROCEDURE Get_Set_Of_Books_Info
54 ( p_operating_unit IN NUMBER
55 , p_sob_id OUT NOCOPY NUMBER
56 , p_sob_name OUT NOCOPY VARCHAR2
57 )
58 IS
59
60 BEGIN
61 Get_Ledger_Info( p_operating_unit,
62 p_sob_id,
63 p_sob_name);
64 EXCEPTION
65 WHEN OTHERS THEN
66 Generic_Error( 'MO_UTILS.Get_Set_Of_Books_Info'
67 , sqlcode
68 , sqlerrm);
69
70 END Get_Set_Of_Books_Info;
71
72 --
73 -- Get_Ledger_Name
74 --
75 FUNCTION Get_Ledger_Name
76 ( p_operating_unit IN NUMBER
77 )
78 RETURN VARCHAR2
79 IS
80 l_ledger_name GL_LEDGERS.Name%TYPE;
81
82 BEGIN
83 SELECT gl.name
84 INTO l_ledger_name
85 FROM hr_organization_information o1,
86 hr_organization_information o2,
87 gl_ledgers_public_v gl
88 WHERE o1.organization_id = o2.organization_id
89 AND o1.organization_id = p_operating_unit
90 AND o1.org_information_context = 'CLASS'
91 AND o2.org_information_context = 'Operating Unit Information'
92 AND o1.org_information1 = 'OPERATING_UNIT'
93 AND o1.org_information2 = 'Y'
94 AND o2.org_information3 = gl.ledger_id;
95
96 RETURN l_ledger_name;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 Generic_Error( 'MO_UTILS.Get_Ledger_Name'
101 , sqlcode
102 , sqlerrm);
103
104 END Get_Ledger_Name;
105
106
107 --
108 -- Get_Ledger_Info
109 --
110 PROCEDURE Get_Ledger_Info
111 ( p_operating_unit IN NUMBER
112 , p_ledger_id OUT NOCOPY NUMBER
113 , p_ledger_name OUT NOCOPY VARCHAR2
114 )
115 IS
116 l_ledger_id GL_LEDGERS.Ledger_Id%TYPE;
117 l_ledger_name GL_LEDGERS.Name%TYPE;
118
119 BEGIN
120 SELECT to_number(o2.org_information3),
121 gl.name
122 INTO l_ledger_id,
123 l_ledger_name
124 FROM hr_organization_information o1,
125 hr_organization_information o2,
126 gl_ledgers_public_v gl
127 WHERE o1.organization_id = o2.organization_id
128 AND o1.organization_id = p_operating_unit
129 AND o1.org_information_context = 'CLASS'
130 AND o2.org_information_context = 'Operating Unit Information'
131 AND o1.org_information1 = 'OPERATING_UNIT'
132 AND o1.org_information2 = 'Y'
133 AND o2.org_information3 = gl.ledger_id;
134
135
136 p_ledger_id := l_ledger_id;
137 p_ledger_name := l_ledger_name;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 Generic_Error( 'MO_UTILS.Get_Ledger_Info'
142 , sqlcode
143 , sqlerrm);
144
145 END Get_Ledger_Info;
146
147 --
148 -- Get Multi_Org_Flag
149 --
150 FUNCTION Get_Multi_Org_Flag
151 RETURN VARCHAR2
152 IS
153
154 BEGIN
155
156 RETURN mo_global.is_multi_org_enabled;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 Generic_Error( 'MO_UTILS.Get_Multi_Org_Flag'
161 , sqlcode
162 , sqlerrm);
163
164 END Get_Multi_Org_Flag;
165
166
167 --
168 -- Get Default_ou
169 --
170 -- Purpose:
171 -- Returns a default operating unit based on the
172 -- MO: Default Operating Unit, MO:Security Profile and MO: Operating Unit
173 -- profile options.
174 --
175 PROCEDURE get_default_ou
176 ( p_default_org_id OUT NOCOPY NUMBER
177 , p_default_ou_name OUT NOCOPY VARCHAR2
178 , p_ou_count OUT NOCOPY NUMBER)
179 IS
180 l_prof_org_id hr_operating_units.organization_id%TYPE;
181 l_default_org_id hr_operating_units.organization_id%TYPE;
182 l_default_ou_name hr_operating_units.name%TYPE;
183 BEGIN
184 p_ou_count := mo_global.get_ou_count;
185
186 IF (get_multi_org_flag <> 'Y' OR p_ou_count = 0) THEN
187 RETURN; -- org id and name out parameters will be null,
188 -- ou count will be 0.
189 END IF;
190
191 --
192 -- If p_ou_count is 1, the session has access to only one operating
193 -- unit which will be returned as the default.
194 -- If p_ou_count is greater than 1, the session has access to more than
195 -- one operating units. In this case, the value of the profile option
196 -- MO: Default Operating Unit will be returned as the default, provided
197 -- it is included in the MO: Security Profile.
198 --
199
200 IF (p_ou_count = 1) THEN
201 --
202 -- Commented out the code given below, since from performance
203 -- perspective, using temporary table is the best way to achieve
204 -- multiple access. There is no necessity to populate a PL/SQL
205 -- array and consume memory.
206 --
207 --DECLARE
208 -- l_ou_name_tab mo_global.OUNameTab;
209 --BEGIN
210 -- l_ou_name_tab := mo_global.get_ou_tab;
211 -- l_default_org_id := l_ou_name_tab.FIRST;
212 -- l_default_ou_name := l_ou_name_tab(l_default_org_id);
213 BEGIN
214 SELECT mg.organization_id
215 , mg.organization_name
216 INTO l_default_org_id
217 , l_default_ou_name
218 FROM mo_glob_org_access_tmp mg;
219 EXCEPTION
220 WHEN OTHERS THEN
221 l_default_org_id := null;
222 l_default_ou_name := null;
223 END;
224 ELSE -- p_ou_count > 1
225 l_prof_org_id := fnd_profile.value('DEFAULT_ORG_ID');
226
227 IF (mo_global.check_access(l_prof_org_id) = 'Y') THEN
228 l_default_org_id := l_prof_org_id;
229 l_default_ou_name := mo_global.get_ou_name(l_default_org_id);
230 ELSE
231 l_default_org_id := NULL;
232 l_default_ou_name := NULL;
233 END IF;
234 END IF;
235
236 p_default_org_id := l_default_org_id;
237 p_default_ou_name := l_default_ou_name;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 Generic_Error('MO_UTILS.Get_Default_OU'
242 , sqlcode
243 , sqlerrm);
244 END get_default_ou;
245
246
247 --
248 -- Get Child_table_orgs
249 --
250 FUNCTION Get_Child_Tab_Orgs
251 ( p_table_name IN VARCHAR2
252 , p_where IN VARCHAR2)
253 RETURN VARCHAR2
254 IS
255 l_tb_org_id NUMBER;
256 l_tb_orgid_list VARCHAR2(4000) DEFAULT '@';
257 l_tb_orgid_cnt PLS_INTEGER;
258 l_tb_sql VARCHAR2(4000);
259
260 TYPE OrgCurTyp IS REF CURSOR;
261 --
262 -- Cursor Variable
263 --
264 c_tb OrgCurTyp;
265
266 BEGIN
267 --
268 -- Populate Table OUs to a local variable.
269 -- Note:
270 -- Bug 1133214 - Native dynamic sql does not work with bulk fetch.
271 --
272 l_tb_sql := ' SELECT DISTINCT org_id '
273 || ' FROM '
274 || p_table_name
275 || ' where 1=1 '
276 || p_where;
277 OPEN c_tb FOR l_tb_sql;
278 LOOP
279 FETCH c_tb
280 INTO l_tb_org_id;
281 l_tb_orgid_cnt := c_tb%ROWCOUNT;
282 EXIT WHEN c_tb%NOTFOUND;
283 l_tb_orgid_list := l_tb_orgid_list || l_tb_org_id || '@';
284 END LOOP;
285 CLOSE c_tb;
286 --
287 -- A child transaction is found.
288 --
289 IF l_tb_orgid_cnt > 0 THEN
290 RETURN (l_tb_orgid_list);
291 ELSE
292 -- No data found?
293 --
294 -- Trim default value '@'.
295 --
296 RETURN (NULL);
297 END IF;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 Generic_Error( 'MO_UTILS.Get_Child_Tab_Orgs'
302 , sqlcode
303 , sqlerrm);
304 END get_child_tab_orgs;
305
306 --
307 -- get_default_org_id
308 --
309 -- Purpose:
310 -- Returns the ORG_ID of the default operating unit.
311 --
312 FUNCTION get_default_org_id RETURN NUMBER
313 IS
314 l_def_org_id hr_operating_units.organization_id%TYPE;
315 l_def_org_name hr_operating_units.name%TYPE;
316 l_org_count pls_integer;
317 BEGIN
318 get_default_ou(l_def_org_id, l_def_org_name, l_org_count);
319 return l_def_org_id;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 generic_error('MO_UTILS.Get_Default_Org_ID', sqlcode, sqlerrm);
324
325 END get_default_org_id;
326
327 --
328 -- Function check_org_in_sp
329 --
330 -- Purpose
331 -- Returns 'Y' if an org exists in the MO: Security Profile.
332 -- Returns 'N' if an org does not exists in the MO: Security Profile or the
333 -- profile option is not set.
334 -- FND_GLOBAL.apps_initialize() must be called before calling this API, since
335 -- the profiles are read from the cache.
336 --
337 FUNCTION check_org_in_sp
338 ( p_org_id IN NUMBER
339 , p_org_class IN VARCHAR2)
340 RETURN VARCHAR2
341 IS
342 l_security_profile_id fnd_profile_option_values.profile_option_value%TYPE;
343 l_org_exists VARCHAR2(1) := 'N';
344 l_sp_name per_security_profiles.security_profile_name%TYPE;
345 l_bg_id per_security_profiles.business_group_id%TYPE;
346 is_view_all_org VARCHAR2(1);
347
348 BEGIN
349 --
350 -- Check if input parameters are passed
351 --
352 IF (p_org_id IS NULL OR p_org_class IS NULL) THEN
353 -- Should we raise an exception in this case or just return 'N'???????
354 RETURN 'N';
355 END IF;
356
357 --
358 -- Read the MO: Security Profile profile value
359 --
360 l_security_profile_id := fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
361
362 IF l_security_profile_id IS NULL THEN
363 -- Should we raise an exception in this case or just return 'N'???????
364 RETURN 'N';
365 ELSE
366
367 --
368 -- Check if this a view all or global view all organizations
369 -- security profile. The HR table per_organization_list is not
370 -- populated for view all or global view all organizations.
371 --
372 -- For a view all security profile within a business group,
373 -- the business group id is populated per_security_profiles.
374 --
375 SELECT security_profile_name
376 , business_group_id
377 , view_all_organizations_flag
378 INTO l_sp_name
379 , l_bg_id
380 , is_view_all_org
381 FROM per_security_profiles
382 WHERE security_profile_id = to_number(l_security_profile_id);
383
384 IF (is_view_all_org = 'Y') THEN
385 IF (l_bg_id IS NOT NULL) THEN
386 --
387 -- View all Within the Business Group Case
388 --
389 -- Check the classification and use appropriate views
390 -- based on the classification. This is done to ensure
391 -- that the org whose setup is complete is selected.
392 --
393 IF p_org_class = 'OPERATING_UNIT' THEN
394 BEGIN
395 SELECT 'Y'
396 INTO l_org_exists
397 FROM hr_operating_units
398 WHERE business_group_id = l_bg_id
399 AND organization_id = p_org_id;
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 l_org_exists := 'N';
403 END;
404 ELSIF p_org_class = 'HR_BG' THEN
405 BEGIN
406 SELECT 'Y'
407 INTO l_org_exists
408 FROM per_business_groups
409 WHERE organization_id = p_org_id
410 AND business_group_id = l_bg_id;
411 EXCEPTION
412 WHEN NO_DATA_FOUND THEN
413 l_org_exists := 'N';
414 END;
415 END IF;
416
417
418 ELSE
419 --
420 -- Global View all Case
421 --
422 -- Check the classification and use appropriate views
423 -- based on the classification. This is done to ensure
424 -- that the org whose setup is complete is selected.
425 --
426 IF p_org_class = 'OPERATING_UNIT' THEN
427 BEGIN
428 SELECT 'Y'
429 INTO l_org_exists
430 FROM hr_operating_units
431 WHERE organization_id = p_org_id;
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN
434 l_org_exists := 'N';
435 END;
436 ELSIF p_org_class = 'HR_BG' THEN
437 BEGIN
438 SELECT 'Y'
439 INTO l_org_exists
440 FROM per_business_groups
441 WHERE organization_id = p_org_id;
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 l_org_exists := 'N';
445 END;
446 END IF; -- for p_org_class
447 END IF; -- for l_bg_id
448
449 ELSE
450
451 --
452 -- Security Profile based on list or hierarchy Case
453 --
454 -- Check the classification and use appropriate views
455 -- based on the classification. This is done to ensure
456 -- that the org whose setup is complete is selected.
457 --
458 IF p_org_class = 'OPERATING_UNIT' THEN
459 BEGIN
460 SELECT 'Y'
461 INTO l_org_exists
462 FROM per_organization_list per,
463 hr_operating_units ou
464 WHERE per.organization_id = ou.organization_id
465 AND per.security_profile_id = l_security_profile_id
466 AND ou.organization_id = p_org_id;
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 l_org_exists := 'N';
470 END;
471 ELSIF p_org_class = 'HR_BG' THEN
472 BEGIN
473 SELECT 'Y'
474 INTO l_org_exists
475 FROM per_organization_list per,
476 per_business_groups bg
477 WHERE per.organization_id = bg.organization_id
478 AND per.security_profile_id = l_security_profile_id
479 AND bg.organization_id = p_org_id;
480 EXCEPTION
481 WHEN NO_DATA_FOUND THEN
482 l_org_exists := 'N';
483 END;
484 END IF; -- for p_org_class
485
486 END IF; -- for is_view_all_org
487
488 RETURN l_org_exists;
489
490 END IF; -- for l_security_profile_id
491
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 generic_error('MO_UTILS.Check_Org_In_SP', sqlcode, sqlerrm);
496 END check_org_in_sp;
497
498
499 --
500 -- Function check_ledger_in_sp
501 --
502 -- Purpose : use this function to determine if user has access to all Operating
503 -- Units for a given Ledger_ID that is passed in.
504 --
505 -- Returns 'Y' if an org exists in the MO: Security Profile.
506 -- Returns 'N' if an org does not exists in the MO: Security Profile or the
507 -- profile option is not set.
508 -- FND_GLOBAL.apps_initialize() must be called before calling this API, since
509 -- the profiles are read from the cache.
510
511 FUNCTION check_ledger_in_sp
512 ( p_ledger_id IN NUMBER )
513 RETURN VARCHAR2
514 IS
515 l_has_full_ledger_access VARCHAR2(1) := 'N';
516 TYPE OrgIdTab IS TABLE OF hr_operating_units.organization_id%TYPE
517 INDEX BY BINARY_INTEGER;
518 TYPE BGTab IS TABLE OF hr_operating_units.business_group_id%TYPE
519 INDEX BY BINARY_INTEGER;
520 t_org_id OrgIdTab;
521 t_bg_id BGTab;
522
523 cursor ledger_cur is
524 select distinct ou.organization_id , ou.business_group_id from hr_operating_units ou
525 where ou.set_of_books_id =p_ledger_id ;
526
527 begin
528 OPEN LEDGER_CUR;
529 fetch ledger_cur BULK COLLECT INTO t_org_id ,t_bg_id ;
530
531 FOR i IN t_org_id.FIRST .. t_org_id.LAST LOOP
532 -- check if bg and org id is same
533 -- if they are same then it is a business group
534 if t_org_id(i) = t_bg_id(i) then
535 l_has_full_ledger_access:=check_org_in_sp(t_bg_id(i),'HR_BG');
536 else
537 l_has_full_ledger_access:=check_org_in_sp(t_org_id(i),'OPERATING_UNIT');
538 end if;
539 if l_has_full_ledger_access = 'N' then
540 return 'N';
541 end if;
542 END LOOP;
543 -- if all organizations in the ledger(OU's and BG) are present in Security Profile
544 -- then return Y
545 return 'Y';
546
547 EXCEPTION
548 WHEN OTHERS THEN
549 -- generic_error('MO_UTILS.Check_Ledger_In_SP', sqlcode, sqlerrm);
550 return 'N';
551 END check_ledger_in_sp;
552
553
554 -- Get_Org_Name
555 --
556 FUNCTION Get_Org_Name
557 ( p_org_id IN NUMBER
558 )
559 RETURN VARCHAR2
560 IS
561 l_org_name HR_OPERATING_UNITS.Name%TYPE;
562
563 BEGIN
564
565 SELECT hr.NAME
566 INTO l_org_name
567 FROM hr_operating_units hr
568 WHERE hr.organization_id = p_org_id;
569
570 RETURN l_org_name;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 Generic_Error('MO_UTILS.Get_Org_Name'
575 , sqlcode
576 , sqlerrm);
577
578 END Get_Org_Name;
579
580
581 END MO_UTILS;
[Home] [Help]