1 PACKAGE BODY INV_ORGHIERARCHY_PVT AS
2 /* $Header: INVVORGB.pls 120.2 2006/03/27 01:21:30 amohamme noship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVVORGB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Spec of INV_ORGHIERARCHY_PVT |
13 --| |
14 --| HISTORY |
15 --| 08/28/00 vjavli Created |
16 --| 09/06/00 vjavli Updated profile for business group id |
17 --| in the organization version cursor |
18 --| 10/13/00 vjavli updated with include top organization |
19 --| logic and to obtain the unique top most |
20 --| parent organization id in the cursor |
21 --| 10/29/00 vjavli updated cursor c_organization_version |
22 --| with effective date range of the |
23 --| hierarchy |
24 --| 10/29/00 vjavli added the logic for organization user |
25 --| responsibility access |
26 --| parent org id check for the user |
27 --| responsibility access and also for the |
28 --| child organizations |
29 --| 10/31/00 vjavli updated with organization end date |
30 --| validation |
31 --| 11/01/00 vjavli updated with parent organization end date|
32 --| validation |
33 --| 11/13/00 vjavli The function Org_Hier_Level_Resp_Access |
34 --| modified. Removed resp_appl_id parameter |
35 --| 11/29/00 vjavli performance tuning - cursor |
36 --| organization code |
37 --| 12/07/00 vjavli Overloading procedures with organization |
38 --| hierarchy level id |
39 --| 12/11/00 vjavli Overloading removed. hierarchy level id |
40 --| functions retained |
41 --| 05/22/01 vjavli Created api:Org_exists_in_hierarchy for |
42 --| usage in the valid query of forms |
43 --| 10/19/01 rschaub Performance Analysis |
44 --| 11/06/01 rschaub new functions get_organization_list |
45 --| and validate_property |
46 --| 11/14/01 vjavli Created new function |
47 --| Org_Hier_Origin_Resp_Access as a |
48 --| performance enhancement of the previous |
49 --| function Org_Hier_Level_Resp_Access |
50 --| 12/11/01 vjavli Org_exists_in_hierarchy modified |
51 --| Insert_hierarchy_index_list created |
52 --| 05/03/02 vjavli dbdrv hint added for the version:115.16 |
53 --| Version:115.17 has an issue since this |
54 --| file got updated with dbdrv hint for the |
55 --| earlier version of the file which does |
56 --| not have the performance apis |
57 --| 09/16/2002 vjavli Bug#2563291 There are two issues |
58 --| global hierarchy has to be eliminated |
59 --| organization list should contain only |
60 --| inventory organizations |
61 --| To eliminate global hierarchy entered |
62 --| through global hierarchy form in Global |
63 --| HR responsibility, profile business_group|
64 --| of the responsibility has to be validated|
65 --| For Global Hierarchies,business_group_id |
66 --| is NULL |
67 --| 09/16/2001 vjavli Bug#2563291 organization list should |
68 --| contain only inventory organizations |
69 --| 11/22/2002 vma Added NOCOPY to OUT parameters; Modify |
70 --| code to print to log only if debug |
71 --| profile option is enabled. |
72 --| 01/09/2003 vjavli Bug#2553313 fix: to_number problem RDBMS |
73 --| bug fix |
74 --| 01/28/2004 nkamaraj Changed the validate_property() logic |
75 --| 04/08/2004 nesoni Bug 3555234. Error/Exceptions should be |
76 --| logged irrespective of FND Debug Enabled |
77 --| profile option. |
78 --+======================================================================*/
79
80
81 --===================
82 -- GLOBALS
83 --===================
84 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ORGHIERARCHY_PVT';
85
86 g_log_level NUMBER := NULL; -- 0 for manual test
87 g_log_mode VARCHAR2(3) := 'OFF'; -- possible values: OFF, SQL, SRS
88 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
89
90 --========================================================================
91 -- FUNCTION : get_organization_list PUBLIC
92 -- PARAMETERS: p_hierarchy_id IN NUMBER
93 -- Organization Hierarchy Id
94 -- p_origin_org_id IN NUMBER
95 -- Hierarchy Origin Organization Id
96 -- org_id_tbl OUT NOCOPY OrgID_Tbl_Type
97 -- List of Organization Ids
98 -- p_include_origin IN VARCHAR DEFAULT 'Y'
99 -- Include the origin in list
100 --
101 -- COMMENT : returns a list containing all organizations from the hierarchy
102 -- below the origin organization to which the user
103 -- has access.
104 -- p_include_origin flag determines whether the origin org id is part
105 -- of the list or not.
106 -- Both Inventory Organization Security and HR Security Group
107 -- are enforced, as well as effective date ranges.
108 -- This api does not return the organizations in the list in any
109 -- particular order. The order may change between revisions.
110 -- origin_id:
111 --=========================================================================
112 PROCEDURE get_organization_list
113 ( p_hierarchy_id IN NUMBER
114 , p_origin_org_id IN NUMBER
115 , x_org_id_tbl OUT NOCOPY OrgID_Tbl_Type
116 , p_include_origin IN VARCHAR2
117 )
118 IS
119 l_responsibility_id NUMBER;
120 l_security_profile_id NUMBER;
121 l_business_group_id NUMBER;
122 l_hierarchy_name VARCHAR2(200);
123 l_hierarchy_id NUMBER;
124 l_view_all_flag VARCHAR2(1);
125 l_include_origin_flag VARCHAR2(1);
126 l_hierarchy_version_id NUMBER;
127 l_sec_hierarchy_version_id NUMBER;
128 l_sec_hierarchy_name VARCHAR2(200);
129 l_sec_hierarchy_id NUMBER;
130 l_sec_origin_org_id NUMBER;
131
132
133 -------------------------------------------------------
134 -------- List Cursor ----------------------------------
135
136
137 CURSOR list_sec_csr IS
138 SELECT organization_id_child
139 FROM
140 (
141 (
142 SELECT organization_id_child
143 FROM per_org_structure_elements arc
144 WHERE
145 (
146 NOT EXISTS( SELECT 1 FROM ORG_ACCESS acc
147 WHERE acc.organization_id = organization_id_child
148 )
149 OR EXISTS( SELECT 1 FROM ORG_ACCESS acc
150 WHERE acc.organization_id = organization_id_child
151 AND acc.responsibility_id = l_responsibility_id
152 )
153 )
154 CONNECT BY
155 arc.organization_id_parent = PRIOR arc.organization_id_child
156 AND arc.org_structure_version_id = PRIOR arc.org_structure_version_id
157 START WITH
158 arc.organization_id_parent = p_origin_org_id
159 AND arc.org_structure_version_id = l_hierarchy_version_id
160 )
161 INTERSECT
162 (
163 (
164 SELECT organization_id_child
165 FROM per_org_structure_elements arc
166 WHERE
167 (
168 NOT EXISTS( SELECT 1 FROM ORG_ACCESS acc
169 WHERE acc.organization_id = organization_id_child
170 )
171 OR EXISTS( SELECT 1 FROM ORG_ACCESS acc
172 WHERE acc.organization_id = organization_id_child
173 AND acc.responsibility_id = l_responsibility_id
174 )
175 )
176 CONNECT BY
177 arc.organization_id_parent = PRIOR arc.organization_id_child
178 AND arc.org_structure_version_id = PRIOR arc.org_structure_version_id
179 START WITH
180 arc.organization_id_parent = l_sec_origin_org_id
181 AND arc.org_structure_version_id = l_sec_hierarchy_version_id
182 )
183 UNION
184 ( SELECT l_sec_origin_org_id FROM DUAL WHERE l_include_origin_flag = 'Y')
185 )
186 ),
187 hr_all_organization_units org,
188 hr_organization_information hoi,
189 mtl_parameters mp
190 WHERE
191 org.organization_id = hoi.organization_id
192 AND org.organization_id = mp.organization_id
193 AND hoi.org_information1 = 'INV'
194 AND hoi.org_information2 = 'Y'
195 AND hoi.org_information_context = 'CLASS'
196 AND org.organization_id = organization_id_child
197 AND ( org.date_to >= SYSDATE OR org.date_to IS NULL )
198
199 ;
200
201
202
203
204 -------------------------------------------------------
205 -------- Cursor without Security Hierarchy ------------
206 ------ Difference to above: removed intersect clause --
207
208
209
210 CURSOR list_no_sec_csr IS
211 SELECT organization_id_child
212 FROM
213
214
215 (
216 -- full set of organizations from origin, not including origin
217 SELECT
218 organization_id_child
219 FROM
220 per_org_structure_elements arc
221 CONNECT BY
222 arc.organization_id_parent = PRIOR arc.organization_id_child
223 AND arc.org_structure_version_id = PRIOR arc.org_structure_version_id
224 START WITH
225 arc.organization_id_parent = p_origin_org_id
226 AND arc.org_structure_version_id = l_hierarchy_version_id
227 )
228
229 , hr_all_organization_units org
230 , hr_organization_information hoi
231 , mtl_parameters mp
232
233
234 WHERE
235 -- only inventory organizations as part of bug#2563291 fix
236 org.organization_id = hoi.organization_id
237 AND org.organization_id = mp.organization_id
238 AND hoi.org_information1 = 'INV'
239 AND hoi.org_information2 = 'Y' -- inventory enabled flag
240 AND hoi.org_information_context = 'CLASS'
241
242 -- expiration check
243 AND org.organization_id = organization_id_child
244 AND ( org.date_to >= SYSDATE
245 OR org.date_to IS NULL
246 )
247
248 -- inv security access check
249 AND ( NOT EXISTS( SELECT 1 FROM ORG_ACCESS acc
250 WHERE acc.organization_id = organization_id_child )
251 OR EXISTS( SELECT 1 FROM ORG_ACCESS acc
252 WHERE acc.organization_id = organization_id_child
253 AND acc.responsibility_id = l_responsibility_id
254 )
255 )
256 ;
257
258
259
260
261 ------------------------------------------------------------------
262 -------- List Cursor with Security Hierarchy but missing Origin --
263 ------ Difference to list_sec_csr: the connect by query for the --
264 ------ Sec. Hier. is replaced with a simple query retrieving --
265 ------ all orgs inside the Security Hierarchy --
266 ------ Note: Forms requires the Origin field to be non-empty --
267 ------ if the 'include Top Organization' box is checked --
268
269
270 CURSOR list_no_sec_origin_csr IS
271 SELECT organization_id_child
272 FROM
273
274 (
275 (
276 -- full set of organizations from origin, not including origin
277 SELECT
278 organization_id_child
279 FROM
280 per_org_structure_elements arc
281 CONNECT BY
282 arc.organization_id_parent = PRIOR arc.organization_id_child
283 AND arc.org_structure_version_id = PRIOR arc.org_structure_version_id
284 START WITH
285 arc.organization_id_parent = p_origin_org_id
286 AND arc.org_structure_version_id = l_hierarchy_version_id
287 )
288
289 INTERSECT
290
291 (
292 -- security hierarchy check
293 SELECT
294 organization_id_child
295 FROM
296 per_org_structure_elements arc
297 WHERE
298 arc.org_structure_version_id = l_sec_hierarchy_version_id
299 )
300
301 )
302
303
304 , hr_all_organization_units org
305 , hr_organization_information hoi
306 , mtl_parameters mp
307
308
309 WHERE
310 -- only inventory organizations as part of bug#2563291 fix
311 org.organization_id = hoi.organization_id
312 AND org.organization_id = mp.organization_id
313 AND hoi.org_information1 = 'INV'
314 AND hoi.org_information2 = 'Y' -- inventory enabled flag
315 AND hoi.org_information_context = 'CLASS'
316
317 -- expiration check
318 AND org.organization_id = organization_id_child
319 AND ( org.date_to >= SYSDATE
320 OR org.date_to IS NULL
321 )
322
323 -- inv security access check
324 AND ( NOT EXISTS( SELECT 1 FROM ORG_ACCESS acc
325 WHERE acc.organization_id = organization_id_child )
326 OR EXISTS( SELECT 1 FROM ORG_ACCESS acc
327 WHERE acc.organization_id = organization_id_child
328 AND acc.responsibility_id = l_responsibility_id
329 )
330 )
331 ;
332
333
334
335
336
337 BEGIN
338
339
340 l_responsibility_id :=
341 TO_NUMBER( FND_PROFILE.VALUE( 'RESP_ID' ) );
342 l_security_profile_id :=
343 TO_NUMBER( FND_PROFILE.value( 'PER_SECURITY_PROFILE_ID' ) );
344 l_business_group_id :=
345 TO_NUMBER( FND_PROFILE.VALUE( 'PER_BUSINESS_GROUP_ID' ) );
346
347
348 -- Note: oe_debug_pub is part of 11i baseline
349 oe_debug_pub.add( 'Responsibility Id: ' || l_responsibility_id, 2 );
350 oe_debug_pub.add( 'Security Profile Id: ' || l_security_profile_id, 2 );
351 oe_debug_pub.add( 'Business Group Id: ' || l_business_group_id, 2 );
352
353
354
358 , prof.view_all_organizations_flag
355 SELECT
356 hier.name
357 , hier.organization_structure_id
359 , prof.include_top_organization_flag
360 , prof.organization_id
361 INTO
362 l_sec_hierarchy_name
363 , l_sec_hierarchy_id
364 , l_view_all_flag
365 , l_include_origin_flag
366 , l_sec_origin_org_id
367 FROM
368 per_security_profiles prof
369 , per_organization_structures hier
370 WHERE
371 prof.security_profile_id = l_security_profile_id
372 AND hier.organization_structure_id (+) = prof.organization_structure_id
373 ;
374
375 oe_debug_pub.add( 'View All:' || l_view_all_flag, 2 );
376 oe_debug_pub.add( 'Include Origin:' || l_include_origin_flag, 2 );
377 oe_debug_pub.add( 'Security Hieararchy Name:' || l_sec_hierarchy_name, 2 );
378
379
380 SELECT
381 hierv.org_structure_version_id
382 INTO
383 l_hierarchy_version_id
384 FROM
385 PER_ORG_STRUCTURE_VERSIONS hierv
386 WHERE
387 hierv.organization_structure_id = p_hierarchy_id
388 AND ( hierv.date_to >= SYSDATE
389 OR hierv.date_to IS NULL
390 )
391 AND hierv.date_from <= SYSDATE
392 ;
393
394
395 oe_debug_pub.add( 'Hierarchy Version Id:' || l_hierarchy_version_id, 2 );
396
397
398 BEGIN
399 SELECT
400 hierv.org_structure_version_id
401 INTO
402 l_sec_hierarchy_version_id
403 FROM
404 PER_ORG_STRUCTURE_VERSIONS hierv
405 WHERE
406 hierv.organization_structure_id = l_sec_hierarchy_id
407 AND ( hierv.date_to >= SYSDATE
408 OR hierv.date_to IS NULL
409 )
410 AND hierv.date_from <= SYSDATE
411 ;
412 EXCEPTION
413 --TODO!: is the buffer cost doubled if no sec hierarchy exists?
414 -- if yes create seperate cursor for that case
415 WHEN NO_DATA_FOUND THEN
416 -- l_sec_hierarchy_version_id := l_hierarchy_version_id;
417 -- l_sec_origin_org_id := p_origin_org_id;
418 l_include_origin_flag := 'Y';
419 END;
420
421 oe_debug_pub.add( 'Security Hierarchy Version Id:' || l_sec_hierarchy_version_id, 2 );
422 oe_debug_pub.add( 'Security Origin Org Id:' || l_sec_origin_org_id, 2 );
423
424
425 IF l_sec_hierarchy_version_id IS NOT NULL
426 AND l_sec_origin_org_id IS NOT NULL
427 THEN
428
429 FOR l_org_id IN list_sec_csr LOOP
430
431 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
432 l_org_id.organization_id_child;
433
434 -- oe_debug_pub.add( ' org id: ' || l_org_id.organization_id_child, 2 );
435 END LOOP;
436
437 ELSIF l_sec_hierarchy_version_id IS NOT NULL
438 AND l_sec_origin_org_id IS NULL
439 THEN
440
441 FOR l_org_id IN list_no_sec_origin_csr LOOP
442
443 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
444 l_org_id.organization_id_child;
445
446 END LOOP;
447
448 ELSIF l_sec_hierarchy_version_id IS NULL
449 THEN
450
451 FOR l_org_id IN list_no_sec_csr LOOP
452
453 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
454 l_org_id.organization_id_child;
455
456 END LOOP;
457 END IF;
458
459 -- origin is always an inventory organization validated through LOV
460 IF p_include_origin = 'Y' THEN
461 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
462 p_origin_org_id;
463 END IF;
464
465
466 END get_organization_list;
467
468
469
470
471 --========================================================================
472 -- FUNCTION : contained_in_hierarchy PUBLIC
473 -- PARAMETERS: p_org_hierarchy_name IN VARCHAR2 Organization Hierarchy
474 -- Name
475 -- p_org_id IN NUMBER Organization Id
476 --
477 -- COMMENT : Returns 'Y' if p_org_id is contained in the current version of
478 -- the named organization hierarchy
479 --=========================================================================
480 FUNCTION contained_in_hierarchy
481 ( p_org_hierarchy_name IN VARCHAR2
482 , p_org_id IN NUMBER
483 )
484 RETURN VARCHAR2
485 IS
486
487 l_org_structure_version_id NUMBER;
488 l_count NUMBER;
489 l_contains VARCHAR2(1);
490 -- bug#2563291 fix
491 l_business_group_id NUMBER;
492
493
494 CURSOR hierarchy_version_csr IS
495 SELECT
496 sv.org_structure_version_id
497 FROM
498 per_org_structure_versions sv
499 , per_organization_structures s
500 WHERE
501 sv.organization_structure_id = s.organization_structure_id
502 AND SYSDATE >= sv.date_from
503 AND ( SYSDATE <= sv.date_to
504 OR sv.date_to IS NULL
505 )
506 AND s.name = p_org_hierarchy_name
507 AND s.business_group_id = l_business_group_id
508 ;
509
510
511 CURSOR hierarchy_contains_csr IS
512 SELECT
516 WHERE
513 organization_id_parent
514 FROM
515 per_org_structure_elements
517 ( organization_id_parent = p_org_id
518 OR organization_id_child = p_org_id
519 )
520 AND org_structure_version_id = l_org_structure_version_id
521 ;
522
523
524
525 BEGIN
526 -- bug#2563291 fix
527 -- get profile business group id of the responsibility
528 l_business_group_id := TO_NUMBER(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'));
529 IF l_business_group_id IS NOT NULL THEN
530 -- this check ensures that no glboal hierarchy is picked up
531 OPEN hierarchy_version_csr;
532 FETCH hierarchy_version_csr INTO l_org_structure_version_id;
533 CLOSE hierarchy_version_csr;
534
535 OPEN hierarchy_contains_csr;
536 FETCH hierarchy_contains_csr INTO l_count;
537 IF hierarchy_contains_csr%FOUND THEN
538 l_contains := 'Y';
539 ELSE
540 l_contains := 'N';
541 END IF;
542 CLOSE hierarchy_contains_csr;
543 ELSE
544 l_contains := 'N';
545 END IF;
546
547 RETURN l_contains;
548
549 END contained_in_hierarchy;
550
551
552 --========================================================================
553 -- FUNCTION : Org_Hierarchy_Access PUBLIC
554 -- PARAMETERS: p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
555 -- Name
556 --
557 -- COMMENT : This API accepts the name of an hierarchy and returns Y if the
558 -- user has access to it, N Otherwise The API checks whether the
559 -- user has an access or authorization for the organization
560 -- hierarchy based on the fact that atleast one of the organization
561 -- in the organization hierarchy belongs to the security profile
562 -- which has been assigned thru the responsibility to the user.
563 --=========================================================================
564 FUNCTION Org_Hierarchy_Access
565 (p_org_hierarchy_name IN VARCHAR2)
566 RETURN VARCHAR2 IS
567 l_profile_hierarchy_name VARCHAR2(30);
568 l_profile_id NUMBER;
569 l_org_hier_profile_id hr_all_organization_units.organization_id%TYPE;
570 l_org_count NUMBER := 0;
571 l_include_top_org_flag VARCHAR2(1);
572 l_top_organization_id hr_all_organization_units.organization_id%TYPE;
573 l_org_hier_level_id NUMBER := NULL; -- to facilitate overloading
574
575 l_security_profile_org_list OrgID_tbl_type;
576 l_org_list OrgID_tbl_type;
577 l_security_index BINARY_INTEGER;
578 l_org_index BINARY_INTEGER;
579 l_org_access_flag VARCHAR2(1);
580 l_view_all_org_flag VARCHAR2(1);
581 l_errorcode NUMBER;
582 l_errortext VARCHAR2(200);
583
584 -- cursor to obtain the security profile hierarchy name
585 CURSOR c_profile_hierarchy IS
586 SELECT pos.name,
587 psp.view_all_organizations_flag,
588 psp.include_top_organization_flag,
589 psp.organization_id
590 FROM per_security_profiles psp,
591 per_organization_structures pos
592 WHERE psp.security_profile_id = FND_PROFILE.value('PER_SECURITY_PROFILE_ID')
593 AND pos.organization_structure_id(+) = psp.organization_structure_id;
594
595 BEGIN
596 IF G_DEBUG = 'Y' THEN
597 INV_ORGHIERARCHY_PVT.Log
598 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
599 , 'Start of Proc:Org Hierarchy Access'
600 );
601 END IF;
602
603 -- get the profile id of the user
604 l_profile_id := fnd_profile.value('PER_SECURITY_PROFILE_ID');
605 IF l_profile_id is NULL THEN
606
607 /* This executable is used by concurrent program so
608 Error/Exception logging should not depend on
609 FND Debug Enabled profile otpion. Bug: 3555234
610 IF G_DEBUG = 'Y' THEN
611 */
612 INV_ORGHIERARCHY_PVT.Log
613 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
614 , 'Security Profile Id is not set for the responsibility');
615
616 --END IF;
617
618 RAISE_APPLICATION_ERROR( -20101, 'Security Profile Id is not set for the
619 responsibility');
620 END IF;
621
622 OPEN c_profile_hierarchy;
623 FETCH c_profile_hierarchy
624 INTO l_profile_hierarchy_name,
625
626 l_view_all_org_flag,
627 l_include_top_org_flag,
628 l_top_organization_id;
629
630 IF c_profile_hierarchy%NOTFOUND THEN
631 RAISE_APPLICATION_ERROR(-20100, 'Security Profile not found');
632 /* This executable is used by concurrent program so
633 Error/Exception logging should not depend on
634 FND Debug Enabled profile otpion. Bug: 3555234
635 IF G_DEBUG = 'Y' THEN
636 */
637 INV_ORGHIERARCHY_PVT.Log
638 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
639 ,'Security Profile not found');
640 --END IF;
641 END IF;
642
643 IF G_DEBUG = 'Y' THEN
644 INV_ORGHIERARCHY_PVT.Log
645 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
646 ,'Profile Hierarchy Name:' || l_profile_hierarchy_name
647 );
648 INV_ORGHIERARCHY_PVT.Log
652 || ' Top Organization Id:' || to_char(l_top_organization_id)
649 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
650 ,'View All Organizations Flag:' || l_view_all_org_flag
651 || ' Include Top Organization Flag:' ||l_include_top_org_flag
653 );
654 END IF;
655
656 l_org_access_flag := 'N';
657 IF ((l_profile_hierarchy_name is NOT NULL) AND
658 (l_view_all_org_flag = 'N')) THEN
659
660 -- Obtain List of Organizations for the Security profile Org
661 -- Hierarchy Name starting from the top organization
662 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
663 ( l_profile_hierarchy_name
664 , l_top_organization_id
665 , l_security_profile_org_list
666 );
667
668 -- Obtain List of Organizations for the Org Hierarchy Name
669 -- where hierarchy level id is null
670 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
671 ( p_org_hierarchy_name
672 , l_org_hier_level_id
673 , l_org_list
674 );
675
676 -- initialize the security profile org list index
677 l_security_index := l_security_profile_org_list.FIRST;
678
679 -- Check for the include top organization flag
680 IF (l_include_top_org_flag = 'N') THEN
681 -- exclude the top organization from the security profile
682 -- organization list
683 -- skip the top organization id
684 l_security_index := l_security_index + 1;
685 END IF;
686
687 -- To check whether the entered Organization Hierarchy has an access
688 -- for the user
689 WHILE (l_security_index <= l_security_profile_org_list.LAST) LOOP
690
691 l_org_index := l_org_list.FIRST;
692 WHILE (l_org_index <= l_org_list.LAST) LOOP
693 IF (l_security_profile_org_list(l_security_index) =
694 l_org_list(l_org_index) ) THEN
695 l_org_access_flag := 'Y';
696 EXIT;
697 END IF;
698 l_org_index := l_org_list.NEXT(l_org_index);
699 END LOOP;
700
701 l_security_index := l_security_profile_org_list.NEXT(l_security_index);
702 END LOOP;
703
704 ELSIF((l_profile_hierarchy_name is NULL) AND
705 (l_view_all_org_flag = 'Y')) THEN
706 -- User has access to view all the organizations.
707 -- Set the org access flag to Y
708 l_org_access_flag := 'Y';
709 END IF;
710
711 RETURN l_org_access_flag;
712
713 IF G_DEBUG = 'Y' THEN
714 INV_ORGHIERARCHY_PVT.Log
715 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
716 ,'End of Proc:Org Hierarchy Access'
717 );
718 END IF;
719
720 EXCEPTION
721 WHEN OTHERS THEN
722 l_errorcode := SQLCODE;
723 l_errortext := SUBSTR(SQLERRM,1,200);
724 /* This executable is used by concurrent program so
725 Error/Exception logging should not depend on
726 FND Debug Enabled profile otpion. Bug: 3555234
727 IF G_DEBUG = 'Y' THEN
728 */
729 INV_ORGHIERARCHY_PVT.Log
730 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
731 ,'Others' || to_char(l_errorcode) || l_errortext
732 );
733 --END IF;
734 RETURN NULL;
735
736 END Org_Hierarchy_Access;
737
738
739
740 --========================================================================
741 -- FUNCTION : Org_Hierarchy_Level_Access PUBLIC
742 -- PARAMETERS: p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
743 -- Name
744 -- p_org_hier_level_id IN NUMBER Organization Hierarchy
745 -- Level Id
746 --
747 -- COMMENT : This API accepts the name of an hierarchy,hierarchy level id and
748 -- returns Y if the user has access to it N otherwise
749 --=========================================================================
750 FUNCTION Org_Hierarchy_Level_Access
751 ( p_org_hierarchy_name IN VARCHAR2,
752 p_org_hier_level_id IN NUMBER)
753 RETURN VARCHAR2 IS
754 l_profile_hierarchy_name VARCHAR2(30);
755 l_profile_id NUMBER;
756 l_org_hier_profile_id hr_all_organization_units.organization_id%TYPE;
757 l_org_count NUMBER := 0;
758 l_include_top_org_flag VARCHAR2(1);
759 l_top_organization_id hr_all_organization_units.organization_id%TYPE;
760 l_org_hier_level_id NUMBER := NULL; -- to facilitate overloading
761
762
763 l_security_profile_org_list OrgID_tbl_type;
764 l_org_list OrgID_tbl_type;
765 l_return_status VARCHAR2(1);
766 l_security_index BINARY_INTEGER;
767 l_index BINARY_INTEGER;
768
769 l_org_level_validity_flag VARCHAR2(1);
770 l_org_level_access_flag VARCHAR2(1);
771 l_view_all_org_flag VARCHAR2(1);
772 l_errorcode NUMBER;
773 l_errortext VARCHAR2(200);
774
775 -- cursor to obtain the security profile hierarchy name
776 CURSOR c_profile_hierarchy IS
777 SELECT pos.name,
778 psp.view_all_organizations_flag,
779 psp.include_top_organization_flag,
780 psp.organization_id
781 FROM per_security_profiles psp,
782 per_organization_structures pos
783 WHERE psp.security_profile_id = FND_PROFILE.value('PER_SECURITY_PROFILE_ID')
787 BEGIN
784 AND pos.organization_structure_id(+) = psp.organization_structure_id;
785
786
788 IF G_DEBUG = 'Y' THEN
789 INV_ORGHIERARCHY_PVT.Log
790 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
791 ,'Start of Proc:Org Hierarchy Level Access'
792 );
793 END IF;
794 -- get the profile id of the user
795 l_profile_id := fnd_profile.value('PER_SECURITY_PROFILE_ID');
796 IF l_profile_id is NULL THEN
797 RAISE_APPLICATION_ERROR(-20101, 'Security Profile Id is not set for
798 the responsibility');
799 END IF;
800
801 OPEN c_profile_hierarchy;
802 FETCH c_profile_hierarchy
803 INTO l_profile_hierarchy_name,
804 l_view_all_org_flag,
805 l_include_top_org_flag,
806 l_top_organization_id;
807
808 IF c_profile_hierarchy%NOTFOUND THEN
809 RAISE_APPLICATION_ERROR(-20100, 'Profile hierarchy name not found');
810 /* This executable is used by concurrent program so
811 Error/Exception logging should not depend on
812 FND Debug Enabled profile otpion. Bug: 3555234
813 IF G_DEBUG = 'Y' THEN
814 */
815 INV_ORGHIERARCHY_PVT.Log
816 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
817 ,'Profile hierarchy name not found'
818 );
819 --END IF;
820 END IF;
821 CLOSE c_profile_hierarchy;
822
823 l_org_level_access_flag := 'N'; -- initialize the access flag
824
825
826 --rschaub: DON'T retrieve all orgs in hierarchy if no security
827 --hierarchy exists
828
829 --additional preconditions: p_org_hier_level_id is a valid
830 -- inventory organization. This should be true, otherwise check can be
831 -- added here.
832 -- Hierarchy business group matches profile business group.
833 -- This follows if origin business group matches profile bg.
834 -- (All hierarchies are local to a business group)
835 -- can add check for origin business group here.
836 IF l_view_all_org_flag = 'Y' THEN
837
838 IF contained_in_hierarchy( p_org_hierarchy_name, p_org_hier_level_id )
839 = 'Y'
840 THEN
841 RETURN 'Y';
842 ELSE
843 RETURN 'N';
844 END IF;
845
846 END IF;
847
848
849 -- Obtain List of Organizations for the Organization Hierarchy Name
850 -- where hierarchy level id is null
851 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
852 (p_org_hierarchy_name,l_org_hier_level_id,l_org_list);
853
854 -- To check whether the entered Organization Hierarchy Level Id is
855 -- within the entered organization hierarchy name
856 l_org_level_validity_flag := 'N';
857 l_index := l_org_list.FIRST;
858 WHILE (l_index <= l_org_list.LAST) LOOP
859 IF (p_org_hier_level_id = l_org_list(l_index)) THEN
860 -- hierarchy level is valid for the hierarchy name
861 l_org_level_validity_flag := 'Y';
862 EXIT;
863 END IF;
864 l_index := l_org_list.NEXT(l_index);
865 END LOOP;
866
867 IF G_DEBUG = 'Y' THEN
868 INV_ORGHIERARCHY_PVT.Log
869 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
870 , 'Organization Level Validity flag' ||
871 l_org_level_validity_flag
872 );
873 END IF;
874
875
876 IF (l_org_level_validity_flag = 'Y') THEN
877
878 IF ((l_profile_hierarchy_name IS NOT NULL) AND
879 (l_view_all_org_flag = 'N')) THEN
880
881 -- Obtain List of Organizations for the Security profile Org
882 -- Hierarchy Name
883 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
884 (l_profile_hierarchy_name,l_top_organization_id,
885 l_security_profile_org_list);
886
887 -- initialize the security profile org list index
888 l_security_index := l_security_profile_org_list.FIRST;
889
890 -- Check for the include top organization flag
891 IF (l_include_top_org_flag = 'N') THEN
892 -- exclude the top organization from the security profile
893 -- organization list
894 -- skip the top organization id
895 l_security_index := l_security_index + 1;
896 END IF;
897
898 -- To check whether the entered Organization Hierarchy Level has an
899 -- access for the user
900 WHILE (l_security_index <= l_security_profile_org_list.LAST) LOOP
901
902 IF (p_org_hier_level_id =
903 l_security_profile_org_list(l_security_index)) THEN
904 l_org_level_access_flag := 'Y';
905 EXIT;
906 END IF;
907
908 l_security_index :=
909 l_security_profile_org_list.NEXT(l_security_index);
910 END LOOP;
911
912 ELSIF ((l_profile_hierarchy_name IS NULL) AND
913 (l_view_all_org_flag = 'Y')) THEN
914 -- User has access to all the organizations
915 l_org_level_access_flag := 'Y';
916 END IF;
917 ELSE
918 l_org_level_access_flag := 'N';
919 END IF;
920
921 RETURN l_org_level_access_flag;
922
926 ,'End of Proc:Org Hierarchy Level Access'
923 IF G_DEBUG = 'Y' THEN
924 INV_ORGHIERARCHY_PVT.Log
925 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
927 );
928 END IF;
929
930 EXCEPTION
931
932 WHEN OTHERS THEN
933 l_errorcode := SQLCODE;
934 l_errortext := SUBSTR(SQLERRM,1,200);
935 /*This executable is used by concurrent program so
936 Error/Exception logging should not depend on
937 FND Debug Enabled profile otpion. Bug: 3555234
938 IF G_DEBUG = 'Y' THEN
939 */
940 INV_ORGHIERARCHY_PVT.Log
941 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
942 ,to_char(l_errorcode) || l_errortext
943 );
944 --END IF;
945 RETURN NULL;
946
947 END Org_Hierarchy_Level_Access;
948
949
950
951 --========================================================================
952 -- PROCEDURE : Org_Hierarchy_List PUBLIC
953 -- PARAMETERS: p_api_version_number known api version
954 -- p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
955 -- Name
956 -- p_org_hier_level_id IN NUMBER Organization Hierarchy
957 -- Level Id
958 -- x_org_code_list List of Organizations
959 --
960 -- COMMENT : API accepts the name of an hierarchy,hierarchy level id and
961 -- returns the list of organizations it contains.
962 -- p_org_hierarchy_name contains user input organization hierarchy
963 -- name
964 -- p_org_hier_level_id contains user input organization id
965 -- in the hierarchy
966 -- x_org_code_list contains list of organizations for a given org
967 -- hierarchy level id
968 --=========================================================================
969 PROCEDURE Org_Hierarchy_List
970 ( p_org_hierarchy_name IN VARCHAR2
971 , p_org_hier_level_id IN NUMBER
972 , x_org_code_list OUT NOCOPY OrgID_tbl_type
973 )
974 IS
975
976 l_structure_version_id NUMBER;
977 list_id NUMBER := 0;
978 l_orgcode hr_all_organization_units.organization_id%TYPE;
979 l_org_hierarchy_parent_id hr_all_organization_units.organization_id%TYPE;
980 l_business_group_id hr_all_organization_units.organization_id%TYPE;
981 l_level NUMBER;
982 l_errorcode NUMBER;
983 l_errortext VARCHAR2(200);
984
985 l_user_resp_id NUMBER;
986
987 -- cursor to obtain active hierarchy structure version and corresponding
988 -- business group
989 CURSOR c_organization_version( c_hierarchy_name VARCHAR2 ) IS
990 SELECT
991 OSV.org_structure_version_id
992 , OSV.business_group_id
993 FROM
994 PER_ORG_STRUCTURE_VERSIONS OSV,
995 PER_ORGANIZATION_STRUCTURES OS
996 WHERE
997 OSV.ORGANIZATION_STRUCTURE_ID = OS.ORGANIZATION_STRUCTURE_ID
998
999 -- rschaub: replaced NVL and date truncation so date index is used
1000 -- otherwise full table scan each time
1001 AND SYSDATE >= OSV.DATE_FROM
1002 AND ( SYSDATE <= OSV.DATE_TO
1003 OR OSV.DATE_TO IS NULL
1004 )
1005
1006 AND ltrim(rtrim(OS.NAME)) = ltrim(rtrim(c_hierarchy_name))
1007 AND OS.BUSINESS_GROUP_ID =
1008 TO_NUMBER( FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') );
1009
1010
1011 -- cursor to obtain the parent organization id for the organization structure
1012 -- hierarchy version when p_org_hierarchy_level is null
1013 CURSOR c_parent_organization( c_org_structure_version_id NUMBER ) IS
1014 SELECT organization_id_parent
1015 FROM PER_ORG_STRUCTURE_ELEMENTS
1016 WHERE ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id
1017 AND ORGANIZATION_ID_PARENT NOT IN
1018 (SELECT ORGANIZATION_ID_CHILD
1019 FROM PER_ORG_STRUCTURE_ELEMENTS
1020 WHERE ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id);
1021
1022 -- cursor to retrieve the inventory organization hierarchy tree for a given
1023 -- structure version id and parent organization id
1024 -- valid organization which is not expired
1025 -- organization has access for the user responsibility
1026 CURSOR c_organizations
1027 ( c_org_structure_version_id NUMBER
1028 , c_org_id NUMBER
1029 , c_business_group_id NUMBER
1030 )
1031 IS
1032 SELECT
1033 organization_id_child
1034 , level
1035 FROM
1036 PER_ORG_STRUCTURE_ELEMENTS POE
1037 CONNECT BY
1038 POE.ORGANIZATION_ID_PARENT = PRIOR POE.ORGANIZATION_ID_CHILD
1039 AND POE.ORG_STRUCTURE_VERSION_ID = PRIOR POE.ORG_STRUCTURE_VERSION_ID
1040 START WITH
1041 POE.ORGANIZATION_ID_PARENT = c_org_id
1042 AND POE.ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id;
1043 -- rschaub:
1044 -- precondition:
1045 -- that the hierarchy origin already has been
1046 -- security checked against hr profile security. So we
1047 -- don't need sql below to verify that again.
1048 -- Lists of general hierarchies are only retrieved in concunction
1049 -- with an origin. If the origin is null and therefore defaulted
1050 -- to the root of the hierarchy, it must be the profile security
1051 -- hierarchy, which by definition has security profile access.
1055 -- speedup: only about twice as fast
1052 -- (this was a bug anyway: when the root is not included in the profile,
1053 -- the security hierarchy list is empty because org_organization_def
1054 -- view security excludes the root)
1056
1057 -- AND POE.ORGANIZATION_ID_PARENT IN
1058 -- ( SELECT OOD.organization_id
1059 -- FROM ORG_ORGANIZATION_DEFINITIONS OOD
1060 -- WHERE OOD.BUSINESS_GROUP_ID = POE.BUSINESS_GROUP_ID );
1061
1062
1063 BEGIN
1064
1065 IF G_DEBUG = 'Y' THEN
1066 INV_ORGHIERARCHY_PVT.Log
1067 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1068 ,'Start of Proc:Org Hierarchy List'
1069 );
1070 END IF;
1071
1072 l_business_group_id :=
1073 TO_NUMBER(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'));
1074
1075 IF G_DEBUG = 'Y' THEN
1076 INV_ORGHIERARCHY_PVT.Log
1077 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1078 ,'Business Group Id:'||to_char(l_business_group_id)
1079 );
1080 END IF;
1081
1082 OPEN c_organization_version(p_org_hierarchy_name);
1083 FETCH c_organization_version
1084 INTO l_structure_version_id,
1085 l_business_group_id;
1086
1087 IF c_organization_version%NOTFOUND THEN
1088 IF G_DEBUG = 'Y' THEN
1089 INV_ORGHIERARCHY_PVT.Log
1090 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1091 ,'Organization Version Id:'||to_char(l_structure_version_id)
1092 );
1093 END IF;
1094
1095 RAISE_APPLICATION_ERROR(-20150,
1096 'Organization structure version id not found');
1097
1098 END IF;
1099
1100 CLOSE c_organization_version;
1101
1102 IF G_DEBUG = 'Y' THEN
1103 INV_ORGHIERARCHY_PVT.Log
1104 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1105 ,'Organization Version Id:' || to_char(l_structure_version_id)
1106 );
1107
1108 INV_ORGHIERARCHY_PVT.Log
1109 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1110 ,'Business Group Id:' || to_char(l_business_group_id)
1111 );
1112 END IF;
1113
1114 -- fetch the parent organization id when hierarchy level id is Null
1115 IF (p_org_hier_level_id IS NOT NULL) THEN
1116 l_org_hierarchy_parent_id := p_org_hier_level_id;
1117 ELSE
1118 OPEN c_parent_organization(l_structure_version_id);
1119
1120 FETCH c_parent_organization
1121 INTO l_org_hierarchy_parent_id;
1122
1123 IF c_parent_organization%NOTFOUND THEN
1124 RAISE_APPLICATION_ERROR(-20250,
1125 'Parent organization Id not found when hierarchy
1126 level is null');
1127 /*This executable is used by concurrent program so
1128 Error/Exception logging should not depend on
1129 FND Debug Enabled profile otpion. Bug: 3555234
1130 IF G_DEBUG = 'Y' THEN
1131 */
1132 INV_ORGHIERARCHY_PVT.Log
1133 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1134 ,'Parent Organization Id not found when hierarchy level null'
1135 );
1136 -- END IF;
1137 END IF;
1138
1139 CLOSE c_parent_organization;
1140
1141 END IF;
1142
1143 -- get the responsibility id
1144 l_user_resp_id := TO_NUMBER(FND_PROFILE.VALUE('RESP_ID'));
1145
1146 -- check whether the parent organization id is unexpired and
1147 -- has an access to the current user responsibility
1148 IF (INV_ORGHIERARCHY_PVT.
1149 Org_Hier_Level_Resp_Access(l_org_hierarchy_parent_id,
1150 l_business_group_id,
1151 l_user_resp_id) = 'Y') THEN
1152
1153 -- include the parent organization code in the dynamic table
1154 list_id := list_id + 1;
1155 x_org_code_list(list_id) := l_org_hierarchy_parent_id;
1156
1157 IF G_DEBUG = 'Y' THEN
1158 INV_ORGHIERARCHY_PVT.Log
1159 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1160 ,'Parent Organization Hierarchy Code:' ||
1161 to_char(x_org_code_list(list_id))
1162 );
1163 END IF;
1164 END IF;
1165
1166
1167 l_orgcode := NULL; /* initialize to verify whether child exist or not */
1168 FOR v_organizations IN c_organizations
1169 (l_structure_version_id,
1170 l_org_hierarchy_parent_id,
1171 l_business_group_id) LOOP
1172
1173 l_orgcode := v_organizations.organization_id_child;
1174 l_level := v_organizations.level;
1175
1176 -- check for valid organization and user responsibility access
1177 IF (INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access
1178 (l_orgcode,
1179 l_business_group_id,
1180 l_user_resp_id ) = 'Y' ) THEN
1181
1182 list_id := list_id + 1;
1183 x_org_code_list( list_id ) := l_orgcode;
1184
1185 IF G_DEBUG = 'Y' THEN
1186 INV_ORGHIERARCHY_PVT.Log
1187 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1188 , 'Organization Hierarchy Code:'||LPAD(' ', 6 * (l_level - 1))||
1189 to_char(x_org_code_list(list_id))
1190 );
1191 END IF;
1192
1193 END IF; -- valid organization and user access
1194
1195 END LOOP;
1196
1200 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1197 IF l_orgcode is NULL THEN
1198 IF G_DEBUG = 'Y' THEN
1199 INV_ORGHIERARCHY_PVT.Log
1201 ,'No Valid Child exist for the parent organization code:' ||
1202 to_char(l_org_hierarchy_parent_id)
1203 );
1204 END IF;
1205 END IF;
1206
1207 IF G_DEBUG = 'Y' THEN
1208 INV_ORGHIERARCHY_PVT.Log
1209 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1210 ,'End of Proc:Org Hierarchy List'
1211 );
1212 END IF;
1213
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 l_errorcode := SQLCODE;
1217 l_errortext := SUBSTR(SQLERRM,1,200);
1218 /*This executable is used by concurrent program so
1219 Error/Exception logging should not depend on
1220 FND Debug Enabled profile otpion. Log level has been
1221 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
1222 IF G_DEBUG = 'Y' THEN
1223 */
1224 INV_ORGHIERARCHY_PVT.Log
1225 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
1226 ,to_char(l_errorcode) || l_errortext
1227 );
1228 --END IF;
1229
1230 END Org_Hierarchy_List;
1231
1232
1233 --========================================================================
1234 -- FUNCTION : validate_property PUBLIC
1235 -- PARAMETERS: p_org_id_tbl This is a list of organization ids,
1236 -- typically obtained from a call to
1237 -- get_organization_list
1238 -- p_property Returns 'Y' if the property applies to
1239 -- the list of organizations
1240 -- p_property can be one of:
1241 -- 'MASTER'
1242 -- 'CALENDAR'
1243 -- 'CHART_OF_ACCOUNTS'
1244 --=========================================================================
1245
1246
1247 FUNCTION validate_property
1248 ( p_org_id_tbl IN OrgID_Tbl_Type
1249 , p_property IN VARCHAR2
1250 )
1251 RETURN VARCHAR2
1252 IS
1253
1254 l_prev_chart_of_accounts_id NUMBER;
1255 l_chart_of_accounts_id NUMBER;
1256 l_chart_of_accounts_count NUMBER;
1257 l_previous_calendar_name VARCHAR2(200);
1258 l_calendar_name VARCHAR2(200);
1259 l_calendar_count VARCHAR2(200);
1260 l_previous_master_org_id NUMBER;
1261 l_master_org_id NUMBER;
1262 l_master_org_count NUMBER;
1263 l_org_id NUMBER;
1264
1265 i BINARY_INTEGER;
1266
1267 l_errorcode NUMBER;
1268 l_errortext VARCHAR2(200);
1269
1270 BEGIN
1271
1272 -- check for unique item master
1273 IF p_property = 'MASTER' THEN
1274
1275 l_master_org_count := 0;
1276 l_previous_master_org_id := -1;
1277 i := p_org_id_tbl.FIRST;
1278 WHILE i IS NOT NULL LOOP
1279
1280 l_org_id := p_org_id_tbl(i);
1281 BEGIN
1282 SELECT master_organization_id
1283 INTO l_master_org_id
1284 FROM mtl_parameters
1285 WHERE organization_id = l_org_id;
1286
1287 EXCEPTION
1288 WHEN NO_DATA_FOUND THEN
1289 oe_debug_pub.add( 'Organization ' || l_org_id || ' has no Item Master'
1290 , 2 );
1291 RETURN 'N';
1292 WHEN OTHERS THEN
1293 oe_debug_pub.add( '(SQL EXCEPTION: Item Master for Organization '
1294 || l_org_id || ')', 2 );
1295 RAISE;
1296 END;
1297
1298 -- Bug :3296392 : Modified the following logic to exit immedidately after
1299 -- the first different Calendar found from the Orgs
1300 -- under the given hierarchy.
1301 IF l_previous_master_org_id = -1
1302 THEN
1303 l_previous_master_org_id := l_master_org_id;
1304 ELSE
1305 IF l_previous_master_org_id <> l_master_org_id
1306 THEN
1307 l_master_org_count := 1;
1308 EXIT;
1309 END IF;
1310 END IF;
1311
1312 i := p_org_id_tbl.NEXT(i);
1313
1314 END LOOP;
1315
1316 IF l_master_org_count = 0 THEN
1317 RETURN 'Y';
1318 ELSE
1319 oe_debug_pub.add( 'Organizations do not share unique Item Master', 2 );
1320 RETURN 'N';
1321 END IF;
1322
1323
1324
1325 -- check for unique calendar
1326 ELSIF p_property = 'CALENDAR' THEN
1327
1328 l_calendar_count := 0;
1329 l_previous_calendar_name := '-1';
1330 i := p_org_id_tbl.FIRST;
1331 WHILE i IS NOT NULL LOOP
1332
1333 l_org_id := p_org_id_tbl(i);
1334 BEGIN
1335 SELECT
1336 period_set_name
1337 INTO
1338 l_calendar_name
1339 FROM
1340 gl_sets_of_books, hr_organization_information
1341 WHERE UPPER( org_information_context ) = UPPER( 'Accounting Information' )
1342 AND organization_id = l_org_id
1343 AND set_of_books_id = TO_NUMBER(org_information1);
1344
1345 EXCEPTION
1349 RETURN 'N';
1346 WHEN NO_DATA_FOUND THEN
1347 oe_debug_pub.add( 'Organization ' || l_org_id || ' has no GL Calendar'
1348 , 2 );
1350 WHEN OTHERS THEN
1351 oe_debug_pub.add( '(SQL EXCEPTION: period_set_name for organization '
1352 || l_org_id || ')', 2 );
1353 RAISE;
1354 END;
1355
1356 -- Bug :3296392 : Modified the following logic to exit immedidately after
1357 -- the first different Calendar found from the Orgs
1358 -- under the given hierarchy.
1359
1360 IF l_previous_calendar_name = '-1'
1361 THEN
1362 l_previous_calendar_name := l_calendar_name;
1363 ELSE
1364 IF l_previous_calendar_name <> l_calendar_name
1365 THEN
1366 l_calendar_count := 1;
1367 EXIT;
1368 END IF;
1369 END IF;
1370
1371 -- IF l_previous_calendar_name <> l_calendar_name THEN
1372 -- l_calendar_count := l_calendar_count + 1;
1373 -- l_previous_calendar_name := l_calendar_name;
1374 -- END IF;
1375
1376 i := p_org_id_tbl.NEXT(i);
1377
1378 END LOOP;
1379
1380
1381 IF l_calendar_count = 0 THEN
1382 RETURN 'Y';
1383 ELSE
1384 oe_debug_pub.add( 'Organizations do not share unique GL calendar', 2 );
1385 RETURN 'N';
1386 END IF;
1387
1388
1389
1390 ELSIF p_property = 'CHART_OF_ACCOUNTS' THEN
1391
1392 l_chart_of_accounts_count := 0;
1393 l_prev_chart_of_accounts_id := '-1';
1394 i := p_org_id_tbl.FIRST;
1395 WHILE i IS NOT NULL LOOP
1396
1397 l_org_id := p_org_id_tbl(i);
1398 BEGIN
1399 SELECT
1400 chart_of_accounts_id
1401 INTO
1402 l_chart_of_accounts_id
1403 FROM
1404 gl_sets_of_books, hr_organization_information
1405 WHERE UPPER( org_information_context ) = UPPER( 'Accounting Information' )
1406 AND organization_id = l_org_id
1407 AND set_of_books_id = TO_NUMBER(org_information1);
1408
1409 EXCEPTION
1410 WHEN NO_DATA_FOUND THEN
1411 oe_debug_pub.add( 'Organization ' || l_org_id || ' has no COA', 2 );
1412 RETURN 'N';
1413 WHEN OTHERS THEN
1414 oe_debug_pub.add( '(SQL EXCEPTION: COA for organization ' || l_org_id || ')', 2 );
1415 RAISE;
1416 END;
1417
1418 -- Bug :3296392 : Modified the following logic to exit immedidately after
1419 -- the first different ChartOfAccounts found from the Orgs
1420 -- under the given hierarchy.
1421 IF l_prev_chart_of_accounts_id = '-1'
1422 THEN
1423 l_prev_chart_of_accounts_id := l_chart_of_accounts_id ;
1424 ELSE
1425 IF l_prev_chart_of_accounts_id <> l_chart_of_accounts_id
1426 THEN
1427 l_chart_of_accounts_count := 1;
1428 EXIT;
1429 END IF;
1430 END IF;
1431
1432 -- IF l_prev_chart_of_accounts_id <> l_chart_of_accounts_id THEN
1433 -- l_chart_of_accounts_count := l_chart_of_accounts_count + 1;
1434 -- l_prev_chart_of_accounts_id := l_chart_of_accounts_id;
1435 -- END IF;
1436
1437 i := p_org_id_tbl.NEXT(i);
1438
1439 END LOOP;
1440
1441
1442 IF l_chart_of_accounts_count = 0 THEN
1443 RETURN 'Y';
1444 ELSE
1445 oe_debug_pub.add( 'Organizations do not share unique COA', 2 );
1446 RETURN 'N';
1447 END IF;
1448
1449 END IF;
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452 l_errorcode := SQLCODE;
1453 l_errortext := SUBSTR( SQLERRM, 1, 200 );
1454 oe_debug_pub.add( to_char( l_errorcode ) || l_errortext, 1 );
1455
1456 RETURN 'N';
1457
1458 END validate_property;
1459
1460
1461 --========================================================================
1462 -- FUNCTION : Org_Hier_Level_Property_Access PUBLIC
1463 -- PARAMETERS: p_api_version_number known api version
1464 -- p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
1465 -- Name
1466 -- p_org_hier_level_id IN NUMBER Hierarchy Level Id
1467 --
1468 -- p_property_type IN VARCHAR2(25) Property Type
1469 --
1470 -- COMMENT : API accepts the name of an hierarchy,hierarchy level id,
1471 -- property and returns Y if the property is satisfied, N otherwise.
1472 -- The supported properties are:
1473 -- MASTER: all the organizations share the same item master
1474 -- CALENDAR: all the organizations share the same calendar
1475 -- CHART_OF_ACCOUNTS: all the organizations share the same chart of
1476 -- accounts
1477 --=========================================================================
1478 FUNCTION Org_Hier_Level_Property_Access
1479 ( p_org_hierarchy_name IN VARCHAR2 ,
1480 p_org_hier_level_id IN NUMBER ,
1481 p_property_type IN VARCHAR2 )
1482 RETURN VARCHAR2 IS
1483 l_structure_version_id NUMBER;
1484 list_id NUMBER := 0;
1485 l_master_orgid hr_all_organization_units.organization_id%TYPE;
1489 l_chart_accounts_id NUMBER(15);
1486 l_old_master_orgid hr_all_organization_units.organization_id%TYPE;
1487 l_period_set_name VARCHAR2(15);
1488 l_old_period_set_name VARCHAR2(15);
1490 l_old_chart_accounts_id NUMBER(15);
1491 l_return_status VARCHAR2(1);
1492 l_index BINARY_INTEGER;
1493 l_org_count NUMBER;
1494 l_inventory_item_id NUMBER;
1495 l_org_level_property_status VARCHAR2(1) := 'N';
1496 l_property_count NUMBER;
1497 l_errorcode NUMBER;
1498 l_errortext VARCHAR2(200);
1499
1500
1501 l_org_code_list OrgID_tbl_type;
1502
1503 v_more_than_one_master_item EXCEPTION;
1504 v_more_than_one_calendar EXCEPTION;
1505 v_one_chart_of_accounts EXCEPTION;
1506
1507 -- cursor to obtain item master organization id
1508 CURSOR c_item_master(c_organization_id NUMBER) IS
1509 SELECT master_organization_id
1510 FROM MTL_PARAMETERS
1511 WHERE ORGANIZATION_ID = c_organization_id;
1512
1513 -- cursor to obtain chart of accounts id for the organization
1514 CURSOR c_chart_of_accounts(c_organization_id NUMBER) IS
1515 SELECT
1516 chart_of_accounts_id
1517 FROM
1518 gl_sets_of_books, hr_organization_information
1519 WHERE upper( org_information_context ) = upper( 'Accounting Information' )
1520 AND organization_id = c_organization_id
1521 AND set_of_books_id = to_number(org_information1);
1522
1523 -- rschaub: org_organization_definitions view too expensive
1524 --SELECT chart_of_accounts_id
1525 --FROM ORG_ORGANIZATION_DEFINITIONS
1526 --WHERE ORGANIZATION_ID = c_organization_id;
1527
1528 -- cursor to obtain GL period set name for an organization
1529 CURSOR c_calendar(c_organization_id NUMBER) IS
1530
1531 SELECT
1532 period_set_name
1533 FROM
1534 gl_sets_of_books, hr_organization_information
1535 WHERE upper( org_information_context ) = upper( 'Accounting Information' )
1536 AND organization_id = c_organization_id
1537 AND set_of_books_id = to_number(org_information1);
1538
1539 -- rschaub: too expensive
1540 --SELECT period_set_name
1541 --FROM GL_SETS_OF_BOOKS
1542 --WHERE SET_OF_BOOKS_ID IN (SELECT set_of_books_id
1543 -- FROM ORG_ORGANIZATION_DEFINITIONS
1544 -- WHERE ORGANIZATION_ID = c_organization_id);
1545
1546
1547 BEGIN
1548 IF G_DEBUG = 'Y' THEN
1549 INV_ORGHIERARCHY_PVT.Log
1550 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1551 ,'Start of Proc:Org Hierarchy Level Property Access'
1552 );
1553 END IF;
1554
1555 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List(p_org_hierarchy_name,
1556 p_org_hier_level_id,l_org_code_list);
1557
1558 IF (p_property_type = 'MASTER') THEN
1559 -- check for unique item master for the organizations
1560 l_index := l_org_code_list.FIRST;
1561 l_property_count := 0;
1562 l_old_master_orgid := NULL;
1563 WHILE (l_index <= l_org_code_list.LAST) LOOP
1564 OPEN c_item_master(l_org_code_list(l_index));
1565
1566 FETCH c_item_master
1567 INTO l_master_orgid;
1568
1569 IF c_item_master%NOTFOUND THEN
1570 NULL;
1571 END IF;
1572
1573 IF l_old_master_orgid IS NULL THEN
1574 l_old_master_orgid := l_master_orgid;
1575 l_property_count := 1;
1576 ELSIF (l_master_orgid <> l_old_master_orgid) THEN
1577 l_property_count := l_property_count + 1;
1578 l_old_master_orgid := l_master_orgid;
1579 END IF;
1580
1581 CLOSE c_item_master;
1582
1583 IF (l_property_count > 1) THEN
1584 RAISE v_more_than_one_master_item;
1585 END IF;
1586
1587 l_index := l_org_code_list.NEXT(l_index);
1588 END LOOP;
1589
1590 -- ONE Item Master exists for the given organization hierarchy
1591 l_org_level_property_status := 'Y';
1592 IF G_DEBUG = 'Y' THEN
1593 INV_ORGHIERARCHY_PVT.Log
1594 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1595 ,'Master Org Id: ' || to_char(l_master_orgid)
1596 );
1597 END IF;
1598
1599 ELSIF(p_property_type = 'CALENDAR') THEN
1600 -- check for unique calendar for the organizations
1601 l_index := l_org_code_list.FIRST;
1602 l_property_count := 0;
1603 l_old_period_set_name := NULL;
1604
1605 WHILE(l_index <= l_org_code_list.LAST) LOOP
1606 OPEN c_calendar(l_org_code_list(l_index));
1607
1608 FETCH c_calendar
1609 INTO l_period_set_name;
1610
1611 IF c_calendar%NOTFOUND THEN
1612 NULL;
1613 END IF;
1614
1615 IF l_old_period_set_name IS NULL THEN
1616 l_old_period_set_name := l_period_set_name;
1617 l_property_count := 1;
1618 ELSIF (l_period_set_name <> l_old_period_set_name) THEN
1619 l_property_count := l_property_count + 1;
1620 l_old_period_set_name := l_period_set_name;
1621 END IF;
1622
1623 CLOSE c_calendar;
1624
1625 IF (l_property_count > 1) THEN
1626 RAISE v_more_than_one_calendar;
1627 END IF;
1628 l_index := l_org_code_list.NEXT(l_index);
1629 END LOOP;
1630 -- ONE Calendar exist for the given organization hierarchy
1634 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1631 l_org_level_property_status := 'Y';
1632 IF G_DEBUG = 'Y' THEN
1633 INV_ORGHIERARCHY_PVT.Log
1635 ,'Calendar: ' || l_period_set_name
1636 );
1637 END IF;
1638
1639 ELSIF(p_property_type = 'CHART_OF_ACCOUNTS') THEN
1640 -- check for unique chart of accounts for the organizations
1641 l_index := l_org_code_list.FIRST;
1642 l_property_count := 0;
1643 l_old_chart_accounts_id := NULL;
1644
1645 WHILE(l_index <= l_org_code_list.LAST) LOOP
1646 OPEN c_chart_of_accounts(l_org_code_list(l_index));
1647
1648 FETCH c_chart_of_accounts
1649 INTO l_chart_accounts_id;
1650
1651 IF c_chart_of_accounts%NOTFOUND THEN
1652 NULL;
1653 END IF;
1654
1655 IF l_old_chart_accounts_id IS NULL THEN
1656 l_old_chart_accounts_id := l_chart_accounts_id;
1657 l_property_count := 1;
1658 ELSIF (l_chart_accounts_id <> l_old_chart_accounts_id) THEN
1659 l_property_count := l_property_count + 1;
1660 l_old_chart_accounts_id := l_chart_accounts_id;
1661 END IF;
1662
1663 CLOSE c_chart_of_accounts;
1664
1665 IF l_property_count > 1 THEN
1666 RAISE v_one_chart_of_accounts;
1667 END IF;
1668
1669 l_index := l_org_code_list.NEXT(l_index);
1670 END LOOP;
1671 -- ONE Chart of Accounts exist for the given organization
1672 -- hierarchy
1673 l_org_level_property_status := 'Y';
1674 IF G_DEBUG = 'Y' THEN
1675 INV_ORGHIERARCHY_PVT.Log
1676 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1677 ,'Chart of Accounts:' || to_char(l_chart_accounts_id)
1678 );
1679 END IF;
1680
1681 END IF;
1682
1683 RETURN l_org_level_property_status;
1684
1685 IF G_DEBUG = 'Y' THEN
1686 INV_ORGHIERARCHY_PVT.Log
1687 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1688 ,'End of Proc:Org Hierarchy Level Property Access'
1689 );
1690 END IF;
1691
1692 EXCEPTION
1693
1694 WHEN v_more_than_one_master_item THEN
1695 l_org_level_property_status := 'N';
1696 /* This executable is used by concurrent program so
1697 Error/Exception logging should not depend on
1698 FND Debug Enabled profile otpion. Bug: 3555234
1699 IF G_DEBUG = 'Y' THEN
1700 */
1701 INV_ORGHIERARCHY_PVT.Log
1702 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1703 ,'More than one item master exists for the Organization Hierarchy'
1704 );
1705 --END IF;
1706 RETURN l_org_level_property_status;
1707
1708 WHEN v_more_than_one_calendar THEN
1709 l_org_level_property_status := 'N';
1710 /*This executable is used by concurrent program so
1711 Error/Exception logging should not depend on
1712 FND Debug Enabled profile otpion. Bug: 3555234
1713 IF G_DEBUG = 'Y' THEN
1714 */
1715 INV_ORGHIERARCHY_PVT.Log
1716 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1717 ,'More than one calendar exists for the Organization Hierarchy'
1718 );
1719 --END IF;
1720 RETURN l_org_level_property_status;
1721
1722 WHEN v_one_chart_of_accounts THEN
1723 l_org_level_property_status := 'N';
1724 /* This executable is used by concurrent program so
1725 Error/Exception logging should not depend on
1726 FND Debug Enabled profile otpion. Bug: 3555234
1727 IF G_DEBUG = 'Y' THEN
1728 */
1729 INV_ORGHIERARCHY_PVT.Log
1730 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1731 ,'More than one chart of accounts exists for the Organization
1732 Hierarchy'
1733 );
1734 --END IF;
1735 RETURN l_org_level_property_status;
1736
1737 WHEN OTHERS THEN
1738 l_errorcode := SQLCODE;
1739 l_errortext := SUBSTR(SQLERRM,1,200);
1740
1741 /*This executable is used by concurrent program so
1742 Error/Exception logging should not depend on
1743 FND Debug Enabled profile otpion. Log level has been
1744 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
1745 IF G_DEBUG = 'Y' THEN
1746 */
1747 INV_ORGHIERARCHY_PVT.Log
1748 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
1749 ,to_char(l_errorcode) || l_errortext
1750 );
1751 --END IF;
1752 RETURN NULL;
1753
1754 END Org_Hier_Level_Property_Access;
1755
1756
1757 --========================================================================
1758 -- FUNCTION : Org_Hier_Level_Resp_Access PUBLIC
1759 -- PARAMETERS: p_api_version_number known api version
1760 -- p_org_id IN NUMBER Hierarchy Level Id
1761 -- (Organization Id)
1762 -- p_business_group_id IN NUMBER Business Group Id
1763 -- p_responsibility_id IN NUMBER Current Responsibility
1764 -- Id
1765 -- COMMENT : API accepts the Organization Id of an organization
1766 -- hierarchy level(organization name), business group id,
1770 --=========================================================================
1767 -- current responsibility user has signed on and returns Y if
1768 -- the organization is a valid organization (unexpired) and has
1769 -- an access for the responsibility, N otherwise.
1771 FUNCTION Org_Hier_Level_Resp_Access
1772 ( p_org_id IN NUMBER,
1773 p_business_group_id IN NUMBER,
1774 p_responsibility_id IN NUMBER
1775 )
1776 RETURN VARCHAR2 IS
1777
1778 -- cursor to check for the unexpired organization
1779 CURSOR c_unxpire_organization(c_org_id NUMBER, c_business_group_id NUMBER) IS
1780 SELECT 'Y'
1781 FROM HR_ALL_ORGANIZATION_UNITS
1782 WHERE ORGANIZATION_ID = c_org_id
1783 AND BUSINESS_GROUP_ID = c_business_group_id
1784 AND NVL(DATE_TO,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
1785
1786 CURSOR c_org_access(c_organization_id NUMBER) IS
1787 SELECT RESPONSIBILITY_ID
1788 FROM ORG_ACCESS
1789 WHERE ORGANIZATION_ID = c_organization_id;
1790
1791 l_responsibility_id NUMBER;
1792 l_org_resp_access_flag VARCHAR2(1);
1793 l_org_valid_flag VARCHAR2(1);
1794
1795 l_errorcode NUMBER;
1796 l_errortext VARCHAR2(200);
1797
1798 BEGIN
1799 IF G_DEBUG = 'Y' THEN
1800 INV_ORGHIERARCHY_PVT.Log
1801 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1802 ,'Start of Proc:Org Hierarchy Level Responsibility Access'
1803 );
1804 END IF;
1805
1806 -- initialize the flag
1807 l_org_resp_access_flag := 'N';
1808
1809 -- initialize the valid organization flag
1810 l_org_valid_flag:= 'N';
1811
1812 -- check whether the organization is expired
1813 OPEN c_unxpire_organization(p_org_id,p_business_group_id);
1814 FETCH c_unxpire_organization
1815 INTO l_org_valid_flag;
1816
1817 IF c_unxpire_organization%NOTFOUND THEN
1818 RAISE_APPLICATION_ERROR(-20255,
1819 'organization expired');
1820 /*This executable is used by concurrent program so
1821 Error/Exception logging should not depend on
1822 FND Debug Enabled profile otpion. Bug: 3555234
1823 IF G_DEBUG = 'Y' THEN
1824 */
1825 INV_ORGHIERARCHY_PVT.Log
1826 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1827 ,'Organization Id expired:' || to_char(p_org_id) ||
1828 'Business Group Id:' || to_char(p_business_group_id)
1829 );
1830 --END IF;
1831 END IF;
1832
1833 CLOSE c_unxpire_organization;
1834
1835 -- Check only if the organization is unexpired
1836 IF (l_org_valid_flag = 'Y') THEN
1837
1838 -- Open the cursor
1839 OPEN c_org_access(p_org_id);
1840
1841 -- Retrieve the first row to setup for the WHILE loop
1842 FETCH c_org_access INTO l_responsibility_id;
1843
1844 -- rows not found set the flag to Y
1845 IF c_org_access%NOTFOUND THEN
1846 l_org_resp_access_flag := 'Y';
1847 END IF;
1848
1849 -- continue looping while there are more rows to fetch
1850 WHILE c_org_access%FOUND LOOP
1851 -- check for the matching current user responsibility
1852 IF(p_responsibility_id = l_responsibility_id) THEN
1853 l_org_resp_access_flag := 'Y';
1854 EXIT;
1855 END IF;
1856 -- retrieve next user responsbility
1857 FETCH c_org_access INTO l_responsibility_id;
1858 END LOOP;
1859
1860 CLOSE c_org_access;
1861
1862 END IF; -- for the valid organization
1863
1864 IF G_DEBUG = 'Y' THEN
1865 INV_ORGHIERARCHY_PVT.Log
1866 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1867 ,'Organization Responsibility Valid Access Flag:'|| l_org_resp_access_flag
1868 );
1869 END IF;
1870
1871 RETURN l_org_resp_access_flag;
1872
1873 IF G_DEBUG = 'Y' THEN
1874 INV_ORGHIERARCHY_PVT.Log
1875 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1876 ,'End of Proc:Org Hierarchy Level Responsibility Access'
1877 );
1878 END IF;
1879
1880 EXCEPTION
1881
1882 WHEN OTHERS THEN
1883 l_errorcode := SQLCODE;
1884 l_errortext := SUBSTR(SQLERRM,1,200);
1885 /*This executable is used by concurrent program so
1886 Error/Exception logging should not depend on
1887 FND Debug Enabled profile otpion. Log leve has b een
1888 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
1889 IF G_DEBUG = 'Y' THEN
1890 */
1891 INV_ORGHIERARCHY_PVT.Log
1892 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
1893 ,to_char(l_errorcode) || l_errortext
1894 );
1895 --END IF;
1896 RETURN NULL;
1897
1898 END Org_Hier_Level_Resp_Access;
1899
1900
1901 --========================================================================
1902 -- FUNCTION : Org_Hier_Origin_Resp_Access PUBLIC
1903 -- PARAMETERS: p_api_version_number known api version
1904 -- p_org_id IN NUMBER Hierarchy Origin Id
1905 -- (Organization Id)
1906 -- p_responsibility_id IN NUMBER Current Responsibility
1907 -- Id
1908 -- COMMENT : API accepts the Organization Id of an organization
1909 -- hierarchy origin(organization name),
1910 -- current responsibility user has signed on and returns Y if
1914 FUNCTION Org_Hier_Origin_Resp_Access
1911 -- the organization is a valid organization (unexpired) and has
1912 -- an access for the responsibility, N otherwise.
1913 --=========================================================================
1915 ( p_org_id IN NUMBER,
1916 p_responsibility_id IN NUMBER
1917 )
1918 RETURN VARCHAR2 IS
1919
1920
1921 CURSOR c_origin_resp_access(c_org_id NUMBER, c_responsibility_id NUMBER ) IS
1922 SELECT 'Y'
1923 FROM HR_ALL_ORGANIZATION_UNITS org
1924 WHERE
1925
1926 -- expiration check
1927 org.organization_id = c_org_id
1928 AND ( org.date_to >= SYSDATE
1929 OR org.date_to IS NULL
1930 )
1931
1932 -- inv security access check
1933 AND ( NOT EXISTS( SELECT 1 FROM ORG_ACCESS acc
1934 WHERE acc.organization_id = c_org_id )
1935 OR EXISTS( SELECT 1 FROM ORG_ACCESS acc
1936 WHERE acc.organization_id = c_org_id
1937 AND acc.responsibility_id = c_responsibility_id
1938 )
1939 );
1940
1941
1942 l_origin_resp_access_flag VARCHAR2(1);
1943
1944 l_errorcode NUMBER;
1945 l_errortext VARCHAR2(200);
1946
1947 BEGIN
1948
1949 IF G_DEBUG = 'Y' THEN
1950 INV_ORGHIERARCHY_PVT.Log
1951 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1952 ,'Start of Proc:Org Hierarchy Origin Responsibility Access'
1953 );
1954 END IF;
1955
1956 -- initialize the flag
1957 l_origin_resp_access_flag := 'N';
1958
1959 -- check whether the organization is unexpired and has responsibility
1960 -- access
1961 OPEN c_origin_resp_access(p_org_id, p_responsibility_id);
1962 FETCH c_origin_resp_access
1963 INTO l_origin_resp_access_flag;
1964
1965 IF c_origin_resp_access%NOTFOUND THEN
1966 RAISE_APPLICATION_ERROR(-20255,
1967 'organization has no access');
1968 /* This executable is used by concurrent program so
1969 Error/Exception logging should not depend on
1970 FND Debug Enabled profile otpion. Bug: 3555234
1971 IF G_DEBUG = 'Y' THEN
1972 */
1973 INV_ORGHIERARCHY_PVT.Log
1974 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1975 ,'Organization Id has no access:' || to_char(p_org_id)
1976 );
1977 --END IF;
1978 END IF;
1979
1980 CLOSE c_origin_resp_access;
1981
1982 IF G_DEBUG = 'Y' THEN
1983 INV_ORGHIERARCHY_PVT.Log
1984 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1985 ,'Origin responsibility access flag:'|| l_origin_resp_access_flag
1986 );
1987 END IF;
1988
1989 RETURN l_origin_resp_access_flag;
1990
1991 IF G_DEBUG = 'Y' THEN
1992 INV_ORGHIERARCHY_PVT.Log
1993 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1994 ,'End of Proc:Org Hierarchy Origin Responsibility Access'
1995 );
1996 END IF;
1997
1998 EXCEPTION
1999
2000 WHEN OTHERS THEN
2001 l_errorcode := SQLCODE;
2002 l_errortext := SUBSTR(SQLERRM,1,200);
2003 /*This executable is used by concurrent program so
2004 Error/Exception logging should not depend on
2005 FND Debug Enabled profile otpion. Log level has been
2006 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
2007 IF G_DEBUG = 'Y' THEN
2008 */
2009 INV_ORGHIERARCHY_PVT.Log
2010 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2011 ,to_char(l_errorcode) || l_errortext
2012 );
2013 --END IF;
2014 RETURN NULL;
2015
2016 END Org_Hier_Origin_Resp_Access;
2017
2018
2019 --========================================================================
2020 -- FUNCTION : Org_exists_in_hierarchy PUBLIC
2021 -- PARAMETERS: p_organization_id IN NUMBER Inventory Organization Id
2022 --
2023 -- COMMENT : This API accepts the organization id and returns Y if the
2024 -- organization id exists in the index list
2025 --=========================================================================
2026 FUNCTION Org_exists_in_hierarchy
2027 ( p_organization_id IN NUMBER)
2028 RETURN VARCHAR2 IS
2029
2030 l_org_index BINARY_INTEGER;
2031 l_org_exists_flag VARCHAR2(1);
2032
2033 l_errorcode NUMBER;
2034 l_errortext VARCHAR2(200);
2035
2036 BEGIN
2037 IF G_DEBUG = 'Y' THEN
2038 INV_ORGHIERARCHY_PVT.Log
2039 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2040 ,'Start of Proc:Org exists in hierarchy'
2041 );
2042 END IF;
2043
2044 -- initialize org exists flag
2045 l_org_exists_flag := 'N';
2046
2047 -- assign the organization_id into binary integer data type
2048 l_org_index := p_organization_id;
2049
2050 -- Check organization id exists
2051 -- note that index contains organization id
2052 IF g_orgid_index_list.EXISTS(l_org_index) THEN
2053 l_org_exists_flag := 'Y';
2054 END IF;
2055
2056 IF G_DEBUG = 'Y' THEN
2057 INV_ORGHIERARCHY_PVT.Log
2058 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2059 ,'Organization exists flag:'|| l_org_exists_flag
2060 );
2061 END IF;
2062
2063 RETURN l_org_exists_flag;
2064
2065 IF G_DEBUG = 'Y' THEN
2066 INV_ORGHIERARCHY_PVT.Log
2067 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2068 ,'End of Proc:Org exists in hierarchy'
2069 );
2070 END IF;
2071
2072 EXCEPTION
2073
2074 WHEN OTHERS THEN
2075 l_errorcode := SQLCODE;
2076 l_errortext := SUBSTR(SQLERRM,1,200);
2077 /*This executable is used by concurrent program so
2078 Error/Exception logging should not depend on
2079 FND Debug Enabled profile otpion. Log level has been
2080 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
2081 IF G_DEBUG = 'Y' THEN
2082 */
2083 INV_ORGHIERARCHY_PVT.Log
2084 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2085 ,to_char(l_errorcode) || l_errortext
2086 );
2087 --END IF;
2088 RETURN NULL;
2089
2090 END Org_exists_in_hierarchy;
2091
2092
2093
2094 --========================================================================
2095 -- PROCEDURE : Insert_hierarchy_index_list PUBLIC
2096 -- PARAMETERS: p_orgid_tbl_list IN orgID_tbl_type Orgid list of an
2097 -- hierarchy
2098 -- COMMENT : This API copies the organization list into the global
2099 -- variable organization id index list. The table index is
2100 -- the organization_id
2101 -- This api is used in the form: Transaction Open Interface
2102 --========================================================================
2106
2103 PROCEDURE Insert_hierarchy_index_list
2104 ( p_orgid_tbl_list IN orgID_tbl_type)
2105 IS
2107 l_org_index BINARY_INTEGER;
2108 l_organization_id hr_all_organization_units.organization_id%TYPE;
2109
2110 BEGIN
2111 IF G_DEBUG = 'Y' THEN
2112 INV_ORGHIERARCHY_PVT.Log
2113 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2114 ,'Start of Proc: Insert hierarchy index list'
2115 );
2116 END IF;
2117
2118 FOR v_index IN p_orgid_tbl_list.FIRST .. p_orgid_tbl_list.LAST LOOP
2119 l_organization_id := p_orgid_tbl_list(v_index);
2120 l_org_index := l_organization_id;
2121 g_orgid_index_list(l_org_index) := l_organization_id;
2122 END LOOP;
2123
2124 IF G_DEBUG = 'Y' THEN
2125 INV_ORGHIERARCHY_PVT.Log
2126 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2127 ,'End of Proc: Insert hierarchy index list'
2128 );
2129 END IF;
2130
2131 END;
2132
2133
2134
2135 --========================================================================
2136 -- PROCEDURE : Log_Initialize PUBLIC
2137 -- COMMENT : Initializes the log facility. It should be called from
2138 -- the top level procedure of each concurrent program
2139 --=======================================================================--
2140 PROCEDURE Log_Initialize
2141 IS
2142 BEGIN
2143 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
2144 IF g_log_level IS NULL THEN
2145 g_log_mode := 'OFF';
2146 ELSE
2147 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
2148 g_log_mode := 'SRS';
2149 ELSE
2150 g_log_mode := 'SQL';
2151 END IF;
2152 END IF;
2153
2154 END Log_Initialize;
2155
2156
2157 --========================================================================
2158 -- PROCEDURE : Log PUBLIC
2159 -- PARAMETERS: p_level IN priority of the message - from
2160 -- highest to lowest:
2161 -- -- G_LOG_ERROR
2162 -- -- G_LOG_EXCEPTION
2163 -- -- G_LOG_EVENT
2164 -- -- G_LOG_PROCEDURE
2165 -- -- G_LOG_STATEMENT
2166 -- p_msg IN message to be print on the log
2167 -- file
2168 -- COMMENT : Add an entry to the log
2169 --=======================================================================--
2170 PROCEDURE Log
2171 ( p_priority IN NUMBER
2172 , p_msg IN VARCHAR2
2173 )
2174 IS
2175 BEGIN
2176 --Additional IF clause is added to print log message if Priority is
2177 --Error or Exception. Bug: 3555234
2178 IF ((p_priority = G_LOG_ERROR) OR
2179 (p_priority = G_LOG_EXCEPTION) OR
2180 (p_priority = G_LOG_PRINT) OR
2181 ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level)))
2182 THEN
2183 IF g_log_mode = 'SQL'
2184 THEN
2185 -- SQL*Plus session: uncomment the next line during unit test
2186 -- DBMS_OUTPUT.put_line(p_msg);
2187 NULL;
2188 ELSE
2189 -- Concurrent request
2190 FND_FILE.put_line
2191 ( FND_FILE.log
2192 , p_msg
2193 );
2194 END IF;
2195 END IF;
2196 EXCEPTION
2197 WHEN OTHERS THEN
2198 NULL;
2199 END Log;
2200
2201
2202 END INV_ORGHIERARCHY_PVT;
[Home] [Help]