1 PACKAGE BODY INV_ORGHIERARCHY_PVT AS
2 /* $Header: INVVORGB.pls 120.4.12020000.2 2013/04/02 13:40:30 abhissri ship $ */
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 -- Bugfix 16344080: fnd_profile.value is not working for 'PER_BUSINESS_GROUP_ID'.
345 -- Using fnd_global.per_business_group_id instead.
346 l_business_group_id :=
347 -- TO_NUMBER( FND_PROFILE.VALUE( 'PER_BUSINESS_GROUP_ID' ) );
348 TO_NUMBER( FND_GLOBAL.per_business_group_id );
349
350
351 -- Note: oe_debug_pub is part of 11i baseline
352 oe_debug_pub.add( 'Responsibility Id: ' || l_responsibility_id, 2 );
353 oe_debug_pub.add( 'Security Profile Id: ' || l_security_profile_id, 2 );
354 oe_debug_pub.add( 'Business Group Id: ' || l_business_group_id, 2 );
355
356
357
358 SELECT
359 hier.name
360 , hier.organization_structure_id
361 , prof.view_all_organizations_flag
362 , prof.include_top_organization_flag
363 , prof.organization_id
364 INTO
365 l_sec_hierarchy_name
366 , l_sec_hierarchy_id
367 , l_view_all_flag
368 , l_include_origin_flag
369 , l_sec_origin_org_id
370 FROM
371 per_security_profiles prof
372 , per_organization_structures hier
373 WHERE
374 prof.security_profile_id = l_security_profile_id
375 AND hier.organization_structure_id (+) = prof.organization_structure_id
376 ;
377
378 oe_debug_pub.add( 'View All:' || l_view_all_flag, 2 );
379 oe_debug_pub.add( 'Include Origin:' || l_include_origin_flag, 2 );
380 oe_debug_pub.add( 'Security Hieararchy Name:' || l_sec_hierarchy_name, 2 );
381
382
383 SELECT
384 hierv.org_structure_version_id
385 INTO
386 l_hierarchy_version_id
387 FROM
388 PER_ORG_STRUCTURE_VERSIONS hierv
389 WHERE
390 hierv.organization_structure_id = p_hierarchy_id
391 AND ( hierv.date_to >= SYSDATE
392 OR hierv.date_to IS NULL
393 )
394 AND hierv.date_from <= SYSDATE
395 ;
396
397
398 oe_debug_pub.add( 'Hierarchy Version Id:' || l_hierarchy_version_id, 2 );
399
400
401 BEGIN
402 SELECT
403 hierv.org_structure_version_id
404 INTO
405 l_sec_hierarchy_version_id
406 FROM
407 PER_ORG_STRUCTURE_VERSIONS hierv
408 WHERE
409 hierv.organization_structure_id = l_sec_hierarchy_id
410 AND ( hierv.date_to >= SYSDATE
411 OR hierv.date_to IS NULL
412 )
413 AND hierv.date_from <= SYSDATE
414 ;
415 EXCEPTION
416 --TODO!: is the buffer cost doubled if no sec hierarchy exists?
417 -- if yes create seperate cursor for that case
418 WHEN NO_DATA_FOUND THEN
419 -- l_sec_hierarchy_version_id := l_hierarchy_version_id;
420 -- l_sec_origin_org_id := p_origin_org_id;
421 l_include_origin_flag := 'Y';
422 END;
423
424 oe_debug_pub.add( 'Security Hierarchy Version Id:' || l_sec_hierarchy_version_id, 2 );
425 oe_debug_pub.add( 'Security Origin Org Id:' || l_sec_origin_org_id, 2 );
426
427
428 IF l_sec_hierarchy_version_id IS NOT NULL
429 AND l_sec_origin_org_id IS NOT NULL
430 THEN
431
432 FOR l_org_id IN list_sec_csr LOOP
433
434 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
435 l_org_id.organization_id_child;
436
437 -- oe_debug_pub.add( ' org id: ' || l_org_id.organization_id_child, 2 );
438 END LOOP;
439
440 ELSIF l_sec_hierarchy_version_id IS NOT NULL
441 AND l_sec_origin_org_id IS NULL
442 THEN
443
444 FOR l_org_id IN list_no_sec_origin_csr LOOP
445
446 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
447 l_org_id.organization_id_child;
448
449 END LOOP;
450
451 ELSIF l_sec_hierarchy_version_id IS NULL
452 THEN
453
454 FOR l_org_id IN list_no_sec_csr LOOP
455
456 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
457 l_org_id.organization_id_child;
458
459 END LOOP;
460 END IF;
461
462 -- origin is always an inventory organization validated through LOV
463 IF p_include_origin = 'Y' THEN
464 x_org_id_tbl( NVL( x_org_id_tbl.LAST, 0 ) + 1 ) :=
465 p_origin_org_id;
466 END IF;
467
468
469 END get_organization_list;
470
471
472
473
474 --========================================================================
475 -- FUNCTION : contained_in_hierarchy PUBLIC
476 -- PARAMETERS: p_org_hierarchy_name IN VARCHAR2 Organization Hierarchy
477 -- Name
478 -- p_org_id IN NUMBER Organization Id
479 --
480 -- COMMENT : Returns 'Y' if p_org_id is contained in the current version of
481 -- the named organization hierarchy
482 --=========================================================================
483 FUNCTION contained_in_hierarchy
484 ( p_org_hierarchy_name IN VARCHAR2
485 , p_org_id IN NUMBER
486 )
487 RETURN VARCHAR2
488 IS
489
490 l_org_structure_version_id NUMBER;
491 l_count NUMBER;
492 l_contains VARCHAR2(1);
493 -- bug#2563291 fix
494 l_business_group_id NUMBER;
495
496
497 CURSOR hierarchy_version_csr IS
498 SELECT
499 sv.org_structure_version_id
500 FROM
501 per_org_structure_versions sv
502 , per_organization_structures s
503 WHERE
504 sv.organization_structure_id = s.organization_structure_id
505 AND SYSDATE >= sv.date_from
506 AND ( SYSDATE <= sv.date_to
507 OR sv.date_to IS NULL
508 )
509 AND s.name = p_org_hierarchy_name
510 --Bug 9775787
511 AND (s.business_group_id = l_business_group_id OR
512 s.business_group_id IS NULL)
513 ;
514
515
516 CURSOR hierarchy_contains_csr IS
517 SELECT
518 organization_id_parent
519 FROM
520 per_org_structure_elements
521 WHERE
522 ( organization_id_parent = p_org_id
523 OR organization_id_child = p_org_id
524 )
525 AND org_structure_version_id = l_org_structure_version_id
526 ;
527
528
529
530 BEGIN
531 -- bug#2563291 fix
532 -- get profile business group id of the responsibility
533
534 -- Bugfix 16344080: fnd_profile.value is not working for 'PER_BUSINESS_GROUP_ID'.
535 -- Using fnd_global.per_business_group_id instead.
536
537 -- l_business_group_id := TO_NUMBER(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'));
538 l_business_group_id := TO_NUMBER(FND_GLOBAL.per_business_group_id);
539
540 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
541 THEN
542 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
543 , 'INV_ORGHIERARCHY_PVT.contained_in_hierarchy '
544 , 'Start of API p_org_hierarchy_name: '|| p_org_hierarchy_name ||
545 ':p_org_id :'|| p_org_id ||
546 ':New_Msg:l_business_group_id:' || l_business_group_id
547 );
548
549 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
550 , 'INV_ORGHIERARCHY_PVT.contained_in_hierarchy '
551 , 'User :'||fnd_global.user_id||
552 ':Resp :'||fnd_global.resp_id||
553 ':Appl :'||fnd_global.resp_appl_id||
554 ':New_Msg:Business Grp :'||fnd_global.per_business_group_id
555 );
556 END IF;
557
558 IF l_business_group_id IS NOT NULL THEN
559 -- this check ensures that no glboal hierarchy is picked up
560 OPEN hierarchy_version_csr;
561 FETCH hierarchy_version_csr INTO l_org_structure_version_id;
562 CLOSE hierarchy_version_csr;
563
564 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
565 THEN
566 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
567 , 'INV_ORGHIERARCHY_PVT.contained_in_hierarchy '
568 , 'l_org_structure_version_id: '|| l_org_structure_version_id
569 );
570 END IF;
571
572 OPEN hierarchy_contains_csr;
573 FETCH hierarchy_contains_csr INTO l_count;
574
575 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
576 THEN
577 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
578 , 'INV_ORGHIERARCHY_PVT.contained_in_hierarchy '
579 , 'l_count: '|| l_count
580 );
581 END IF;
582
583 IF hierarchy_contains_csr%FOUND THEN
584 l_contains := 'Y';
585 ELSE
586 l_contains := 'N';
587 END IF;
588 CLOSE hierarchy_contains_csr;
589 ELSE
590 l_contains := 'N';
591 END IF;
592 --Testfix: Moving this inside
593 --CLOSE hierarchy_contains_csr;
594
595 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
596 THEN
597 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
598 , 'INV_ORGHIERARCHY_PVT.contained_in_hierarchy '
599 , 'Exiting l_contains :'|| l_contains
600 );
601 END IF;
602
603 RETURN l_contains;
604
605 END contained_in_hierarchy;
606
607
608 --========================================================================
609 -- FUNCTION : Org_Hierarchy_Access PUBLIC
610 -- PARAMETERS: p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
611 -- Name
612 --
613 -- COMMENT : This API accepts the name of an hierarchy and returns Y if the
614 -- user has access to it, N Otherwise The API checks whether the
615 -- user has an access or authorization for the organization
616 -- hierarchy based on the fact that atleast one of the organization
617 -- in the organization hierarchy belongs to the security profile
618 -- which has been assigned thru the responsibility to the user.
619 --=========================================================================
620 FUNCTION Org_Hierarchy_Access
621 (p_org_hierarchy_name IN VARCHAR2)
622 RETURN VARCHAR2 IS
623 l_profile_hierarchy_name VARCHAR2(30);
624 l_profile_id NUMBER;
625 l_org_hier_profile_id hr_all_organization_units.organization_id%TYPE;
626 l_org_count NUMBER := 0;
627 l_include_top_org_flag VARCHAR2(1);
628 l_top_organization_id hr_all_organization_units.organization_id%TYPE;
629 l_org_hier_level_id NUMBER := NULL; -- to facilitate overloading
630
631 l_security_profile_org_list OrgID_tbl_type;
632 l_org_list OrgID_tbl_type;
633 l_security_index BINARY_INTEGER;
634 l_org_index BINARY_INTEGER;
635 l_org_access_flag VARCHAR2(1);
636 l_view_all_org_flag VARCHAR2(1);
637 l_errorcode NUMBER;
638 l_errortext VARCHAR2(200);
639
640 -- cursor to obtain the security profile hierarchy name
641 CURSOR c_profile_hierarchy IS
642 SELECT pos.name,
643 psp.view_all_organizations_flag,
644 psp.include_top_organization_flag,
645 psp.organization_id
646 FROM per_security_profiles psp,
647 per_organization_structures pos
648 WHERE psp.security_profile_id = FND_PROFILE.value('PER_SECURITY_PROFILE_ID')
649 AND pos.organization_structure_id(+) = psp.organization_structure_id;
650
651 BEGIN
652 IF G_DEBUG = 'Y' THEN
653 INV_ORGHIERARCHY_PVT.Log
654 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
655 , 'Start of Proc:Org Hierarchy Access'
656 );
657 END IF;
658
659 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
660 THEN
661 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
662 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
663 , 'Start of Proc:Org Hierarchy Access p_org_hierarchy_name : '||p_org_hierarchy_name
664 );
665 END IF;
666
667 -- get the profile id of the user
668 l_profile_id := fnd_profile.value('PER_SECURITY_PROFILE_ID');
669
670 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
671 THEN
672 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
673 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
674 , 'l_profile_id :' ||l_profile_id
675 );
676 END IF;
677
678 IF l_profile_id is NULL THEN
679
680 /* This executable is used by concurrent program so
681 Error/Exception logging should not depend on
682 FND Debug Enabled profile otpion. Bug: 3555234
683 IF G_DEBUG = 'Y' THEN
684 */
685 INV_ORGHIERARCHY_PVT.Log
686 ( INV_ORGHIERARCHY_PVT.G_LOG_ERROR
687 , 'Security Profile Id is not set for the responsibility');
688
689 --END IF;
690
691 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
692 THEN
693 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
694 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
695 , 'Security Profile Id is not set for the responsibility'
696 );
697 END IF;
698
699 RAISE_APPLICATION_ERROR( -20101, 'Security Profile Id is not set for the
700 responsibility');
701 END IF;
702
703 OPEN c_profile_hierarchy;
704 FETCH c_profile_hierarchy
705 INTO l_profile_hierarchy_name,
706 l_view_all_org_flag,
707 l_include_top_org_flag,
708 l_top_organization_id;
709
710 IF c_profile_hierarchy%NOTFOUND THEN
711 RAISE_APPLICATION_ERROR(-20100, 'Security Profile not found');
712 /* This executable is used by concurrent program so
713 Error/Exception logging should not depend on
714 FND Debug Enabled profile otpion. Bug: 3555234
715 IF G_DEBUG = 'Y' THEN
716 */
717 INV_ORGHIERARCHY_PVT.Log
718 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
719 ,'Security Profile not found');
720
721 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
722 THEN
723 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
724 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
725 , 'Security Profile not found'
726 );
727 END IF;
728 --END IF;
729 END IF;
730
731 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
732 THEN
733 INV_ORGHIERARCHY_PVT.Log
734 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
735 ,'Profile Hierarchy Name:' || l_profile_hierarchy_name
736 );
737 INV_ORGHIERARCHY_PVT.Log
738 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
739 ,'View All Organizations Flag:' || l_view_all_org_flag
740 || ' Include Top Organization Flag:' ||l_include_top_org_flag
741 || ' Top Organization Id:' || to_char(l_top_organization_id)
742 );
743
744 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
745 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
746 , 'Profile Hierarchy Name: '||l_profile_hierarchy_name
747 );
748
749 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
750 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
751 , 'View All Organizations Flag: '|| l_view_all_org_flag
752 );
753
754 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
755 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
756 , 'Include Top Organization Flag: '|| l_include_top_org_flag
757 );
758
759 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
760 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
761 , 'Top Organization Id: '|| to_char(l_top_organization_id)
762 );
763 END IF;
764
765 l_org_access_flag := 'N';
766
767 IF ((l_profile_hierarchy_name is NOT NULL) AND
768 (l_view_all_org_flag = 'N')) THEN
769
770 -- Obtain List of Organizations for the Security profile Org
771 -- Hierarchy Name starting from the top organization
772 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
773 ( l_profile_hierarchy_name
774 , l_top_organization_id
775 , l_security_profile_org_list
776 );
777
778 -- Obtain List of Organizations for the Org Hierarchy Name
779 -- where hierarchy level id is null
780 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
781 ( p_org_hierarchy_name
782 , l_org_hier_level_id
783 , l_org_list
784 );
785
786 -- initialize the security profile org list index
787 l_security_index := l_security_profile_org_list.FIRST;
788
789 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
790 THEN
791 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
792 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
793 , 'l_security_index :' || l_security_index
794 );
795 END IF;
796
797 -- Check for the include top organization flag
798 IF (l_include_top_org_flag = 'N') THEN
799 -- exclude the top organization from the security profile
800 -- organization list
801 -- skip the top organization id
802 l_security_index := l_security_index + 1;
803
804 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
805 THEN
806 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
807 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
808 , 'l_include_top_org_flag is N so increment l_security_index to :' || l_security_index
809 );
810 END IF;
811 END IF;
812
813 -- To check whether the entered Organization Hierarchy has an access
814 -- for the user
815 WHILE (l_security_index <= l_security_profile_org_list.LAST) LOOP
816 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
817 THEN
818 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
819 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
820 , 'l_security_index :'|| l_security_index
821 );
822 END IF;
823
824 l_org_index := l_org_list.FIRST;
825 WHILE (l_org_index <= l_org_list.LAST) LOOP
826
827 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
828 THEN
829 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
830 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
831 , 'l_org_index :'|| l_org_index ||
832 ' l_org_list(l_org_index) :' || l_org_list(l_org_index) ||
833 ' l_security_profile_org_list(l_security_index) :'|| l_security_profile_org_list(l_security_index)
834 );
835 END IF;
836
837 IF (l_security_profile_org_list(l_security_index) =
838 l_org_list(l_org_index) ) THEN
839 l_org_access_flag := 'Y';
840 EXIT;
841 END IF;
842 l_org_index := l_org_list.NEXT(l_org_index);
843 END LOOP;
844
845 l_security_index := l_security_profile_org_list.NEXT(l_security_index);
846 END LOOP;
847
848 ELSIF((l_profile_hierarchy_name is NULL) AND
849 (l_view_all_org_flag = 'Y')) THEN
850 -- User has access to view all the organizations.
851 -- Set the org access flag to Y
852 l_org_access_flag := 'Y';
853
854 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
855 THEN
856 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
857 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
858 , 'User has access to view all the organizations. Setting l_org_access_flag to Y'
859 );
860 END IF;
861 END IF;
862
863 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
864 THEN
865 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
866 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
867 , 'End of INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access returning l_org_access_flag :' ||l_org_access_flag
868 );
869 END IF;
870
871 RETURN l_org_access_flag;
872
873 IF G_DEBUG = 'Y' THEN
874 INV_ORGHIERARCHY_PVT.Log
875 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
876 ,'End of Proc:Org Hierarchy Access'
877 );
878 END IF;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 l_errorcode := SQLCODE;
883 l_errortext := SUBSTR(SQLERRM,1,200);
884 /* This executable is used by concurrent program so
885 Error/Exception logging should not depend on
886 FND Debug Enabled profile otpion. Bug: 3555234
887 IF G_DEBUG = 'Y' THEN
888 */
889 INV_ORGHIERARCHY_PVT.Log
890 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
891 ,'Others' || to_char(l_errorcode) || l_errortext
892 );
893 --END IF;
894
895 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
896 THEN
897 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
898 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access'
899 , 'Exception in INV_ORGHIERARCHY_PVT.Org_Hierarchy_Access: '||sqlerrm(sqlcode)
900 );
901 END IF;
902
903 RETURN NULL;
904
905 END Org_Hierarchy_Access;
906
907
908
909 --========================================================================
910 -- FUNCTION : Org_Hierarchy_Level_Access PUBLIC
911 -- PARAMETERS: p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
912 -- Name
913 -- p_org_hier_level_id IN NUMBER Organization Hierarchy
914 -- Level Id
915 --
916 -- COMMENT : This API accepts the name of an hierarchy,hierarchy level id and
917 -- returns Y if the user has access to it N otherwise
918 --=========================================================================
919 FUNCTION Org_Hierarchy_Level_Access
920 ( p_org_hierarchy_name IN VARCHAR2,
921 p_org_hier_level_id IN NUMBER)
922 RETURN VARCHAR2 IS
923 l_profile_hierarchy_name VARCHAR2(30);
924 l_profile_id NUMBER;
925 l_org_hier_profile_id hr_all_organization_units.organization_id%TYPE;
926 l_org_count NUMBER := 0;
927 l_include_top_org_flag VARCHAR2(1);
928 l_top_organization_id hr_all_organization_units.organization_id%TYPE;
929 l_org_hier_level_id NUMBER := NULL; -- to facilitate overloading
930
931
932 l_security_profile_org_list OrgID_tbl_type;
933 l_org_list OrgID_tbl_type;
934 l_return_status VARCHAR2(1);
935 l_security_index BINARY_INTEGER;
936 l_index BINARY_INTEGER;
937
938 l_org_level_validity_flag VARCHAR2(1);
939 l_org_level_access_flag VARCHAR2(1);
940 l_view_all_org_flag VARCHAR2(1);
941 l_errorcode NUMBER;
942 l_errortext VARCHAR2(200);
943
944 -- cursor to obtain the security profile hierarchy name
945 CURSOR c_profile_hierarchy IS
946 SELECT pos.name,
947 psp.view_all_organizations_flag,
948 psp.include_top_organization_flag,
949 psp.organization_id
950 FROM per_security_profiles psp,
951 per_organization_structures pos
952 WHERE psp.security_profile_id = FND_PROFILE.value('PER_SECURITY_PROFILE_ID')
953 AND pos.organization_structure_id(+) = psp.organization_structure_id;
954
955
956 BEGIN
957 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
958 THEN
959 INV_ORGHIERARCHY_PVT.Log
960 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
961 ,'Start of Proc:Org Hierarchy Level Access'
962 );
963
964 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
965 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
966 , 'Start of Proc:Org Hierarchy Level Access p_org_hierarchy_name :'|| p_org_hierarchy_name ||' p_org_hier_level_id :'||p_org_hier_level_id
967 );
968 END IF;
969 -- get the profile id of the user
970 l_profile_id := fnd_profile.value('PER_SECURITY_PROFILE_ID');
971
972 IF l_profile_id is NULL THEN
973 RAISE_APPLICATION_ERROR(-20101, 'Security Profile Id is not set for
974 the responsibility');
975 END IF;
976
977 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
978 THEN
979 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
980 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
981 , 'l_profile_id :'|| l_profile_id
982 );
983 END IF;
984
985 OPEN c_profile_hierarchy;
986 FETCH c_profile_hierarchy
987 INTO l_profile_hierarchy_name,
988 l_view_all_org_flag,
989 l_include_top_org_flag,
990 l_top_organization_id;
991
992 IF c_profile_hierarchy%NOTFOUND THEN
993 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
994 THEN
995 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
996 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
997 , 'Profile hierarchy name not found'
998 );
999 END IF;
1000
1001 RAISE_APPLICATION_ERROR(-20100, 'Profile hierarchy name not found');
1002
1003 /* This executable is used by concurrent program so
1004 Error/Exception logging should not depend on
1005 FND Debug Enabled profile otpion. Bug: 3555234
1006 IF G_DEBUG = 'Y' THEN
1007 */
1008 INV_ORGHIERARCHY_PVT.Log
1009 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1010 ,'Profile hierarchy name not found'
1011 );
1012 --END IF;
1013 END IF;
1014
1015 CLOSE c_profile_hierarchy;
1016
1017 l_org_level_access_flag := 'N'; -- initialize the access flag
1018
1019 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1020 THEN
1021 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1022 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1023 , 'l_profile_hierarchy_name :'|| l_profile_hierarchy_name
1024 );
1025
1026 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1027 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1028 , 'l_view_all_org_flag :'|| l_view_all_org_flag
1029 );
1030 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1031 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1032 , 'l_include_top_org_flag :'|| l_include_top_org_flag
1033 );
1034 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1035 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1036 , 'l_top_organization_id :'|| l_top_organization_id
1037 );
1038 END IF;
1039
1040 --rschaub: DON'T retrieve all orgs in hierarchy if no security
1041 --hierarchy exists
1042
1043 --additional preconditions: p_org_hier_level_id is a valid
1044 -- inventory organization. This should be true, otherwise check can be
1045 -- added here.
1046 -- Hierarchy business group matches profile business group.
1047 -- This follows if origin business group matches profile bg.
1048 -- (All hierarchies are local to a business group)
1049 -- can add check for origin business group here.
1050 IF l_view_all_org_flag = 'Y' THEN
1051
1052 IF contained_in_hierarchy( p_org_hierarchy_name, p_org_hier_level_id )
1053 = 'Y'
1054 THEN
1055 RETURN 'Y';
1056 ELSE
1057 RETURN 'N';
1058 END IF;
1059
1060 END IF;
1061
1062
1063 -- Obtain List of Organizations for the Organization Hierarchy Name
1064 -- where hierarchy level id is null
1065 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
1066 (p_org_hierarchy_name,l_org_hier_level_id,l_org_list);
1067
1068 -- To check whether the entered Organization Hierarchy Level Id is
1069 -- within the entered organization hierarchy name
1070 l_org_level_validity_flag := 'N';
1071
1072 l_index := l_org_list.FIRST;
1073
1074 WHILE (l_index <= l_org_list.LAST) LOOP
1075 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1076 THEN
1077 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1078 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1079 , 'l_index :'|| l_index ||' l_org_list(l_index) :'|| l_org_list(l_index)
1080 );
1081 END IF;
1082
1083 IF (p_org_hier_level_id = l_org_list(l_index)) THEN
1084 -- hierarchy level is valid for the hierarchy name
1085 l_org_level_validity_flag := 'Y';
1086 EXIT;
1087 END IF;
1088 l_index := l_org_list.NEXT(l_index);
1089 END LOOP;
1090
1091 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1092 THEN
1093 INV_ORGHIERARCHY_PVT.Log
1094 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1095 , 'Organization Level Validity flag' ||
1096 l_org_level_validity_flag
1097 );
1098
1099 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1100 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1101 , 'Organization Level Validity flag :'|| l_org_level_validity_flag
1102 );
1103 END IF;
1104
1105
1106 IF (l_org_level_validity_flag = 'Y') THEN
1107
1108 IF ((l_profile_hierarchy_name IS NOT NULL) AND
1109 (l_view_all_org_flag = 'N')) THEN
1110
1111 -- Obtain List of Organizations for the Security profile Org
1112 -- Hierarchy Name
1113 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List
1114 (l_profile_hierarchy_name,l_top_organization_id,
1115 l_security_profile_org_list);
1116
1117 -- initialize the security profile org list index
1118 l_security_index := l_security_profile_org_list.FIRST;
1119
1120 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1121 THEN
1122 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1123 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1124 , 'l_security_index :'|| l_security_index
1125 );
1126 END IF;
1127
1128 -- Check for the include top organization flag
1129 IF (l_include_top_org_flag = 'N') THEN
1130 -- exclude the top organization from the security profile
1131 -- organization list
1132 -- skip the top organization id
1133 l_security_index := l_security_index + 1;
1134
1135 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1136 THEN
1137 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1138 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1139 , 'exclude the top organization from the security profile so incr l_security_index :'|| l_security_index
1140 );
1141 END IF;
1142 END IF;
1143
1144 -- To check whether the entered Organization Hierarchy Level has an
1145 -- access for the user
1146 WHILE (l_security_index <= l_security_profile_org_list.LAST) LOOP
1147
1148 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1149 THEN
1150 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1151 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1152 , 'l_security_index :'|| l_security_index || ' l_security_profile_org_list(l_security_index) :'|| l_security_profile_org_list(l_security_index)
1153 );
1154 END IF;
1155
1156 IF (p_org_hier_level_id =
1157 l_security_profile_org_list(l_security_index)) THEN
1158 l_org_level_access_flag := 'Y';
1159 EXIT;
1160 END IF;
1161
1162 l_security_index :=
1163 l_security_profile_org_list.NEXT(l_security_index);
1164 END LOOP;
1165
1166 ELSIF ((l_profile_hierarchy_name IS NULL) AND
1167 (l_view_all_org_flag = 'Y')) THEN
1168 -- User has access to all the organizations
1169 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1170 THEN
1171 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1172 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1173 , 'User has access to all the organizations '
1174 );
1175 END IF;
1176
1177 l_org_level_access_flag := 'Y';
1178 END IF;
1179 ELSE
1180 l_org_level_access_flag := 'N';
1181 END IF;
1182
1183 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1184 THEN
1185 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1186 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access'
1187 , 'Exiting INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access returning l_org_level_access_flag: '|| l_org_level_access_flag
1188 );
1189 END IF;
1190
1191 RETURN l_org_level_access_flag;
1192
1193 IF G_DEBUG = 'Y' THEN
1194 INV_ORGHIERARCHY_PVT.Log
1195 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1196 ,'End of Proc:Org Hierarchy Level Access'
1197 );
1198 END IF;
1199
1200 EXCEPTION
1201
1202 WHEN OTHERS THEN
1203 l_errorcode := SQLCODE;
1204 l_errortext := SUBSTR(SQLERRM,1,200);
1205 /*This executable is used by concurrent program so
1206 Error/Exception logging should not depend on
1207 FND Debug Enabled profile otpion. Bug: 3555234
1208 IF G_DEBUG = 'Y' THEN
1209 */
1210 INV_ORGHIERARCHY_PVT.Log
1211 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
1212 ,to_char(l_errorcode) || l_errortext
1213 );
1214 --END IF;
1215
1216 IF c_profile_hierarchy%ISOPEN THEN
1217 CLOSE c_profile_hierarchy;
1218 END IF;
1219
1220 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1221 THEN
1222 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1223 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_Level_Access ERROR'
1224 , 'Exception :'|| sqlerrm(sqlcode)
1225 );
1226 END IF;
1227
1228 RETURN NULL;
1229
1230 END Org_Hierarchy_Level_Access;
1231
1232
1233
1234 --========================================================================
1235 -- PROCEDURE : Org_Hierarchy_List PUBLIC
1236 -- PARAMETERS: p_api_version_number known api version
1237 -- p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
1238 -- Name
1239 -- p_org_hier_level_id IN NUMBER Organization Hierarchy
1240 -- Level Id
1241 -- x_org_code_list List of Organizations
1242 --
1243 -- COMMENT : API accepts the name of an hierarchy,hierarchy level id and
1244 -- returns the list of organizations it contains.
1245 -- p_org_hierarchy_name contains user input organization hierarchy
1246 -- name
1247 -- p_org_hier_level_id contains user input organization id
1248 -- in the hierarchy
1249 -- x_org_code_list contains list of organizations for a given org
1250 -- hierarchy level id
1251 --=========================================================================
1252 PROCEDURE Org_Hierarchy_List
1253 ( p_org_hierarchy_name IN VARCHAR2
1254 , p_org_hier_level_id IN NUMBER
1255 , x_org_code_list OUT NOCOPY OrgID_tbl_type
1256 )
1257 IS
1258
1259 l_structure_version_id NUMBER;
1260 list_id NUMBER := 0;
1261 l_orgcode hr_all_organization_units.organization_id%TYPE;
1262 l_org_hierarchy_parent_id hr_all_organization_units.organization_id%TYPE;
1263 l_business_group_id hr_all_organization_units.organization_id%TYPE;
1264 l_level NUMBER;
1265 l_errorcode NUMBER;
1266 l_errortext VARCHAR2(200);
1267
1268 l_user_resp_id NUMBER;
1269
1270 -- cursor to obtain active hierarchy structure version and corresponding
1271 -- business group
1272 CURSOR c_organization_version( c_hierarchy_name VARCHAR2 ) IS
1273 SELECT
1274 OSV.org_structure_version_id
1275 , OSV.business_group_id
1276 FROM
1277 PER_ORG_STRUCTURE_VERSIONS OSV,
1278 PER_ORGANIZATION_STRUCTURES OS
1279 WHERE
1280 OSV.ORGANIZATION_STRUCTURE_ID = OS.ORGANIZATION_STRUCTURE_ID
1281
1282 -- rschaub: replaced NVL and date truncation so date index is used
1283 -- otherwise full table scan each time
1284 AND SYSDATE >= OSV.DATE_FROM
1285 AND ( SYSDATE <= OSV.DATE_TO
1286 OR OSV.DATE_TO IS NULL
1287 )
1288
1289 AND ltrim(rtrim(OS.NAME)) = ltrim(rtrim(c_hierarchy_name))
1290 --Bug 9775787
1291 AND (OS.BUSINESS_GROUP_ID =
1292 -- Bugfix 16344080: fnd_profile.value is not working for 'PER_BUSINESS_GROUP_ID'.
1293 -- Using fnd_global.per_business_group_id instead.
1294 -- TO_NUMBER( FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') ) OR
1295 TO_NUMBER(FND_GLOBAL.per_business_group_id) OR
1296 OS.BUSINESS_GROUP_ID IS NULL);
1297
1298
1299 -- cursor to obtain the parent organization id for the organization structure
1300 -- hierarchy version when p_org_hierarchy_level is null
1301 CURSOR c_parent_organization( c_org_structure_version_id NUMBER ) IS
1302 SELECT organization_id_parent
1303 FROM PER_ORG_STRUCTURE_ELEMENTS
1304 WHERE ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id
1305 AND ORGANIZATION_ID_PARENT NOT IN
1306 (SELECT ORGANIZATION_ID_CHILD
1307 FROM PER_ORG_STRUCTURE_ELEMENTS
1308 WHERE ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id);
1309
1310 -- cursor to retrieve the inventory organization hierarchy tree for a given
1311 -- structure version id and parent organization id
1312 -- valid organization which is not expired
1313 -- organization has access for the user responsibility
1314 CURSOR c_organizations
1315 ( c_org_structure_version_id NUMBER
1316 , c_org_id NUMBER
1317 , c_business_group_id NUMBER
1318 )
1319 IS
1320 SELECT
1321 organization_id_child
1322 , level
1323 FROM
1324 PER_ORG_STRUCTURE_ELEMENTS POE
1325 CONNECT BY
1326 POE.ORGANIZATION_ID_PARENT = PRIOR POE.ORGANIZATION_ID_CHILD
1327 AND POE.ORG_STRUCTURE_VERSION_ID = PRIOR POE.ORG_STRUCTURE_VERSION_ID
1328 START WITH
1329 POE.ORGANIZATION_ID_PARENT = c_org_id
1330 AND POE.ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id;
1331 -- rschaub:
1332 -- precondition:
1333 -- that the hierarchy origin already has been
1334 -- security checked against hr profile security. So we
1335 -- don't need sql below to verify that again.
1336 -- Lists of general hierarchies are only retrieved in concunction
1337 -- with an origin. If the origin is null and therefore defaulted
1338 -- to the root of the hierarchy, it must be the profile security
1339 -- hierarchy, which by definition has security profile access.
1340 -- (this was a bug anyway: when the root is not included in the profile,
1341 -- the security hierarchy list is empty because org_organization_def
1342 -- view security excludes the root)
1343 -- speedup: only about twice as fast
1344
1345 -- AND POE.ORGANIZATION_ID_PARENT IN
1346 -- ( SELECT OOD.organization_id
1347 -- FROM ORG_ORGANIZATION_DEFINITIONS OOD
1348 -- WHERE OOD.BUSINESS_GROUP_ID = POE.BUSINESS_GROUP_ID );
1349
1350
1351 BEGIN
1352
1353 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1354 THEN
1355 INV_ORGHIERARCHY_PVT.Log
1356 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1357 ,'Start of Proc:Org Hierarchy List'
1358 );
1359
1360 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1361 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1362 , 'Start of the API p_org_hierarchy_name :'|| p_org_hierarchy_name ||' p_org_hier_level_id :'|| p_org_hier_level_id
1363 );
1364 END IF;
1365
1366 -- Bugfix 16344080: fnd_profile.value is not working for 'PER_BUSINESS_GROUP_ID'.
1367 -- Using fnd_global.per_business_group_id instead.
1368 -- l_business_group_id := TO_NUMBER(FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'));
1369 l_business_group_id := TO_NUMBER(FND_GLOBAL.per_business_group_id);
1370
1371 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1372 THEN
1373 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1374 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1375 , 'l_business_group_id :'|| l_business_group_id
1376 );
1377 END IF;
1378
1379 IF G_DEBUG = 'Y' THEN
1380 INV_ORGHIERARCHY_PVT.Log
1381 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1382 ,'Business Group Id:'||to_char(l_business_group_id)
1383 );
1384 END IF;
1385
1386 OPEN c_organization_version(p_org_hierarchy_name);
1387 FETCH c_organization_version
1388 INTO l_structure_version_id,
1389 l_business_group_id;
1390
1391 IF c_organization_version%NOTFOUND THEN
1392 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1393 THEN
1394 INV_ORGHIERARCHY_PVT.Log
1395 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1396 ,'Organization Version Id:'||to_char(l_structure_version_id)
1397 );
1398
1399 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1400 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1401 , 'Organization structure version id not found '
1402 );
1403 END IF;
1404
1405 RAISE_APPLICATION_ERROR(-20150, 'Organization structure version id not found');
1406
1407 END IF;
1408
1409 CLOSE c_organization_version;
1410
1411 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1412 THEN
1413 INV_ORGHIERARCHY_PVT.Log
1414 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1415 ,'Organization Version Id:' || to_char(l_structure_version_id)
1416 );
1417
1418 INV_ORGHIERARCHY_PVT.Log
1419 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1420 ,'Business Group Id:' || to_char(l_business_group_id)
1421 );
1422
1423 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1424 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1425 , 'l_structure_version_id: '||l_structure_version_id
1426 );
1427
1428 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1429 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1430 , 'l_business_group_id: '|| l_business_group_id
1431 );
1432 END IF;
1433
1434 -- fetch the parent organization id when hierarchy level id is Null
1435 IF (p_org_hier_level_id IS NOT NULL) THEN
1436 l_org_hierarchy_parent_id := p_org_hier_level_id;
1437 ELSE
1438 OPEN c_parent_organization(l_structure_version_id);
1439 FETCH c_parent_organization INTO l_org_hierarchy_parent_id;
1440
1441 IF c_parent_organization%NOTFOUND THEN
1442 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1443 THEN
1444 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1445 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1446 , 'Parent organization Id not found when hierarchy level is null'
1447 );
1448 END IF;
1449
1450 RAISE_APPLICATION_ERROR(-20250,
1451 'Parent organization Id not found when hierarchy level is null');
1452 /*This executable is used by concurrent program so
1453 Error/Exception logging should not depend on
1454 FND Debug Enabled profile otpion. Bug: 3555234
1455 IF G_DEBUG = 'Y' THEN
1456 */
1457 INV_ORGHIERARCHY_PVT.Log
1458 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
1459 ,'Parent Organization Id not found when hierarchy level null'
1460 );
1461 -- END IF;
1462 END IF;
1463
1464 CLOSE c_parent_organization;
1465 END IF;
1466
1467 -- get the responsibility id
1468 l_user_resp_id := TO_NUMBER(FND_PROFILE.VALUE('RESP_ID'));
1469
1470 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1471 THEN
1472 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1473 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1474 , 'l_user_resp_id :' || l_user_resp_id
1475 );
1476 END IF;
1477
1478 -- check whether the parent organization id is unexpired and
1479 -- has an access to the current user responsibility
1480 IF (INV_ORGHIERARCHY_PVT.
1481 Org_Hier_Level_Resp_Access(l_org_hierarchy_parent_id,
1482 l_business_group_id,
1483 l_user_resp_id) = 'Y') THEN
1484
1485 -- include the parent organization code in the dynamic table
1486 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1487 THEN
1488 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1489 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1490 , 'Adding parent org :'||l_org_hierarchy_parent_id||' to the dynamic table'
1491 );
1492 END IF;
1493
1494 list_id := list_id + 1;
1495 x_org_code_list(list_id) := l_org_hierarchy_parent_id;
1496
1497 IF G_DEBUG = 'Y' THEN
1498 INV_ORGHIERARCHY_PVT.Log
1499 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1500 ,'Parent Organization Hierarchy Code:' ||
1501 to_char(x_org_code_list(list_id))
1502 );
1503 END IF;
1504 END IF;
1505
1506
1507 l_orgcode := NULL; /* initialize to verify whether child exist or not */
1508
1509 FOR v_organizations IN c_organizations
1510 (l_structure_version_id,
1511 l_org_hierarchy_parent_id,
1512 l_business_group_id) LOOP
1513
1514 l_orgcode := v_organizations.organization_id_child;
1515 l_level := v_organizations.level;
1516
1517 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1518 THEN
1519 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1520 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1521 , 'Checking for valid org resp access for l_orgcode :'|| l_orgcode||
1522 ' l_business_group_id :'|| l_business_group_id ||
1523 ' l_user_resp_id :'|| l_user_resp_id ||
1524 ' l_level :'|| l_level
1525 );
1526 END IF;
1527
1528 -- check for valid organization and user responsibility access
1529 IF (INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access
1530 (l_orgcode,
1531 l_business_group_id,
1532 l_user_resp_id ) = 'Y' ) THEN
1533
1534 list_id := list_id + 1;
1535 x_org_code_list( list_id ) := l_orgcode;
1536
1537 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1538 THEN
1539 INV_ORGHIERARCHY_PVT.Log
1540 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1541 , 'Organization Hierarchy Code:'||LPAD(' ', 6 * (l_level - 1))||
1542 to_char(x_org_code_list(list_id))
1543 );
1544
1545 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1546 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1547 , 'Access prrsent adding :'|| LPAD(' ', 6 * (l_level - 1))|| to_char(x_org_code_list(list_id))
1548 );
1549 END IF;
1550
1551 END IF; -- valid organization and user access
1552
1553 END LOOP;
1554
1555 IF l_orgcode is NULL THEN
1556 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1557 THEN
1558 INV_ORGHIERARCHY_PVT.Log
1559 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1560 ,'No Valid Child exist for the parent organization code:' ||
1561 to_char(l_org_hierarchy_parent_id)
1562 );
1563
1564 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1565 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1566 , 'No Valid Child exist for the parent organization code:'|| to_char(l_org_hierarchy_parent_id)
1567 );
1568 END IF;
1569 END IF;
1570
1571 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1572 THEN
1573 INV_ORGHIERARCHY_PVT.Log
1574 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1575 ,'End of Proc:Org Hierarchy List'
1576 );
1577
1578 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1579 , 'INV_ORGHIERARCHY_PVT.Org_Hierarchy_List '
1580 , 'Exiting Org_Hierarchy_List '
1581 );
1582 END IF;
1583
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 l_errorcode := SQLCODE;
1587 l_errortext := SUBSTR(SQLERRM,1,200);
1588 /*This executable is used by concurrent program so
1589 Error/Exception logging should not depend on
1590 FND Debug Enabled profile otpion. Log level has been
1591 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
1592 IF G_DEBUG = 'Y' THEN
1593 */
1594 INV_ORGHIERARCHY_PVT.Log
1595 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
1596 ,to_char(l_errorcode) || l_errortext
1597 );
1598 --END IF;
1599
1600 IF c_organization_version%ISOPEN THEN
1601 CLOSE c_organization_version;
1602 END IF;
1603
1604 IF c_parent_organization%ISOPEN THEN
1605 CLOSE c_parent_organization;
1606 END IF;
1607 END Org_Hierarchy_List;
1608
1609
1610 --========================================================================
1611 -- FUNCTION : validate_property PUBLIC
1612 -- PARAMETERS: p_org_id_tbl This is a list of organization ids,
1613 -- typically obtained from a call to
1614 -- get_organization_list
1615 -- p_property Returns 'Y' if the property applies to
1616 -- the list of organizations
1617 -- p_property can be one of:
1618 -- 'MASTER'
1619 -- 'CALENDAR'
1620 -- 'CHART_OF_ACCOUNTS'
1621 --=========================================================================
1622
1623
1624 FUNCTION validate_property
1625 ( p_org_id_tbl IN OrgID_Tbl_Type
1626 , p_property IN VARCHAR2
1627 )
1628 RETURN VARCHAR2
1629 IS
1630
1631 l_prev_chart_of_accounts_id NUMBER;
1632 l_chart_of_accounts_id NUMBER;
1633 l_chart_of_accounts_count NUMBER;
1634 l_previous_calendar_name VARCHAR2(200);
1635 l_calendar_name VARCHAR2(200);
1636 l_calendar_count VARCHAR2(200);
1637 l_previous_master_org_id NUMBER;
1638 l_master_org_id NUMBER;
1639 l_master_org_count NUMBER;
1640 l_org_id NUMBER;
1641
1642 i BINARY_INTEGER;
1643
1644 l_errorcode NUMBER;
1645 l_errortext VARCHAR2(200);
1646
1647 BEGIN
1648
1649 -- check for unique item master
1650 IF p_property = 'MASTER' THEN
1651
1652 l_master_org_count := 0;
1653 l_previous_master_org_id := -1;
1654 i := p_org_id_tbl.FIRST;
1655 WHILE i IS NOT NULL LOOP
1656
1657 l_org_id := p_org_id_tbl(i);
1658 BEGIN
1659 SELECT master_organization_id
1660 INTO l_master_org_id
1661 FROM mtl_parameters
1662 WHERE organization_id = l_org_id;
1663
1664 EXCEPTION
1665 WHEN NO_DATA_FOUND THEN
1666 oe_debug_pub.add( 'Organization ' || l_org_id || ' has no Item Master'
1667 , 2 );
1668 RETURN 'N';
1669 WHEN OTHERS THEN
1670 oe_debug_pub.add( '(SQL EXCEPTION: Item Master for Organization '
1671 || l_org_id || ')', 2 );
1672 RAISE;
1673 END;
1674
1675 -- Bug :3296392 : Modified the following logic to exit immedidately after
1676 -- the first different Calendar found from the Orgs
1677 -- under the given hierarchy.
1678 IF l_previous_master_org_id = -1
1679 THEN
1680 l_previous_master_org_id := l_master_org_id;
1681 ELSE
1682 IF l_previous_master_org_id <> l_master_org_id
1683 THEN
1684 l_master_org_count := 1;
1685 EXIT;
1686 END IF;
1687 END IF;
1688
1689 i := p_org_id_tbl.NEXT(i);
1690
1691 END LOOP;
1692
1693 IF l_master_org_count = 0 THEN
1694 RETURN 'Y';
1695 ELSE
1696 oe_debug_pub.add( 'Organizations do not share unique Item Master', 2 );
1697 RETURN 'N';
1698 END IF;
1699
1700
1701
1702 -- check for unique calendar
1703 ELSIF p_property = 'CALENDAR' THEN
1704
1705 l_calendar_count := 0;
1706 l_previous_calendar_name := '-1';
1707 i := p_org_id_tbl.FIRST;
1708 WHILE i IS NOT NULL LOOP
1709
1710 l_org_id := p_org_id_tbl(i);
1711 BEGIN
1712 SELECT
1713 period_set_name
1714 INTO
1715 l_calendar_name
1716 FROM
1717 gl_sets_of_books, hr_organization_information
1718 WHERE UPPER( org_information_context ) = UPPER( 'Accounting Information' )
1719 AND organization_id = l_org_id
1720 AND set_of_books_id = TO_NUMBER(org_information1);
1721
1722 EXCEPTION
1723 WHEN NO_DATA_FOUND THEN
1724 oe_debug_pub.add( 'Organization ' || l_org_id || ' has no GL Calendar'
1725 , 2 );
1726 RETURN 'N';
1727 WHEN OTHERS THEN
1728 oe_debug_pub.add( '(SQL EXCEPTION: period_set_name for organization '
1729 || l_org_id || ')', 2 );
1730 RAISE;
1731 END;
1732
1733 -- Bug :3296392 : Modified the following logic to exit immedidately after
1734 -- the first different Calendar found from the Orgs
1735 -- under the given hierarchy.
1736
1737 IF l_previous_calendar_name = '-1'
1738 THEN
1739 l_previous_calendar_name := l_calendar_name;
1740 ELSE
1741 IF l_previous_calendar_name <> l_calendar_name
1742 THEN
1743 l_calendar_count := 1;
1744 EXIT;
1745 END IF;
1746 END IF;
1747
1748 -- IF l_previous_calendar_name <> l_calendar_name THEN
1749 -- l_calendar_count := l_calendar_count + 1;
1750 -- l_previous_calendar_name := l_calendar_name;
1751 -- END IF;
1752
1753 i := p_org_id_tbl.NEXT(i);
1754
1755 END LOOP;
1756
1757
1758 IF l_calendar_count = 0 THEN
1759 RETURN 'Y';
1760 ELSE
1761 oe_debug_pub.add( 'Organizations do not share unique GL calendar', 2 );
1762 RETURN 'N';
1763 END IF;
1764
1765
1766
1767 ELSIF p_property = 'CHART_OF_ACCOUNTS' THEN
1768
1769 l_chart_of_accounts_count := 0;
1770 l_prev_chart_of_accounts_id := '-1';
1771 i := p_org_id_tbl.FIRST;
1772 WHILE i IS NOT NULL LOOP
1773
1774 l_org_id := p_org_id_tbl(i);
1775 BEGIN
1776 SELECT
1777 chart_of_accounts_id
1778 INTO
1779 l_chart_of_accounts_id
1780 FROM
1781 gl_sets_of_books, hr_organization_information
1782 WHERE UPPER( org_information_context ) = UPPER( 'Accounting Information' )
1783 AND organization_id = l_org_id
1784 AND set_of_books_id = TO_NUMBER(org_information1);
1785
1786 EXCEPTION
1787 WHEN NO_DATA_FOUND THEN
1788 oe_debug_pub.add( 'Organization ' || l_org_id || ' has no COA', 2 );
1789 RETURN 'N';
1790 WHEN OTHERS THEN
1791 oe_debug_pub.add( '(SQL EXCEPTION: COA for organization ' || l_org_id || ')', 2 );
1792 RAISE;
1793 END;
1794
1795 -- Bug :3296392 : Modified the following logic to exit immedidately after
1796 -- the first different ChartOfAccounts found from the Orgs
1797 -- under the given hierarchy.
1798 IF l_prev_chart_of_accounts_id = '-1'
1799 THEN
1800 l_prev_chart_of_accounts_id := l_chart_of_accounts_id ;
1801 ELSE
1802 IF l_prev_chart_of_accounts_id <> l_chart_of_accounts_id
1803 THEN
1804 l_chart_of_accounts_count := 1;
1805 EXIT;
1806 END IF;
1807 END IF;
1808
1809 -- IF l_prev_chart_of_accounts_id <> l_chart_of_accounts_id THEN
1810 -- l_chart_of_accounts_count := l_chart_of_accounts_count + 1;
1811 -- l_prev_chart_of_accounts_id := l_chart_of_accounts_id;
1812 -- END IF;
1813
1814 i := p_org_id_tbl.NEXT(i);
1815
1816 END LOOP;
1817
1818
1819 IF l_chart_of_accounts_count = 0 THEN
1820 RETURN 'Y';
1821 ELSE
1822 oe_debug_pub.add( 'Organizations do not share unique COA', 2 );
1823 RETURN 'N';
1824 END IF;
1825
1826 END IF;
1827 EXCEPTION
1828 WHEN OTHERS THEN
1829 l_errorcode := SQLCODE;
1830 l_errortext := SUBSTR( SQLERRM, 1, 200 );
1831 oe_debug_pub.add( to_char( l_errorcode ) || l_errortext, 1 );
1832
1833 RETURN 'N';
1834
1835 END validate_property;
1836
1837
1838 --========================================================================
1839 -- FUNCTION : Org_Hier_Level_Property_Access PUBLIC
1840 -- PARAMETERS: p_api_version_number known api version
1841 -- p_org_hierarchy_name IN VARCHAR2(30) Organization Hierarchy
1842 -- Name
1843 -- p_org_hier_level_id IN NUMBER Hierarchy Level Id
1844 --
1845 -- p_property_type IN VARCHAR2(25) Property Type
1846 --
1847 -- COMMENT : API accepts the name of an hierarchy,hierarchy level id,
1848 -- property and returns Y if the property is satisfied, N otherwise.
1849 -- The supported properties are:
1850 -- MASTER: all the organizations share the same item master
1851 -- CALENDAR: all the organizations share the same calendar
1852 -- CHART_OF_ACCOUNTS: all the organizations share the same chart of
1853 -- accounts
1854 --=========================================================================
1855 FUNCTION Org_Hier_Level_Property_Access
1856 ( p_org_hierarchy_name IN VARCHAR2 ,
1857 p_org_hier_level_id IN NUMBER ,
1858 p_property_type IN VARCHAR2 )
1859 RETURN VARCHAR2 IS
1860 l_structure_version_id NUMBER;
1861 list_id NUMBER := 0;
1862 l_master_orgid hr_all_organization_units.organization_id%TYPE;
1863 l_old_master_orgid hr_all_organization_units.organization_id%TYPE;
1864 l_period_set_name VARCHAR2(15);
1865 l_old_period_set_name VARCHAR2(15);
1866 l_chart_accounts_id NUMBER(15);
1867 l_old_chart_accounts_id NUMBER(15);
1868 l_return_status VARCHAR2(1);
1869 l_index BINARY_INTEGER;
1870 l_org_count NUMBER;
1871 l_inventory_item_id NUMBER;
1872 l_org_level_property_status VARCHAR2(1) := 'N';
1873 l_property_count NUMBER;
1874 l_errorcode NUMBER;
1875 l_errortext VARCHAR2(200);
1876
1877
1878 l_org_code_list OrgID_tbl_type;
1879
1880 v_more_than_one_master_item EXCEPTION;
1881 v_more_than_one_calendar EXCEPTION;
1882 v_one_chart_of_accounts EXCEPTION;
1883
1884 -- cursor to obtain item master organization id
1885 CURSOR c_item_master(c_organization_id NUMBER) IS
1886 SELECT master_organization_id
1887 FROM MTL_PARAMETERS
1888 WHERE ORGANIZATION_ID = c_organization_id;
1889
1890 -- cursor to obtain chart of accounts id for the organization
1891 CURSOR c_chart_of_accounts(c_organization_id NUMBER) IS
1892 SELECT
1893 chart_of_accounts_id
1894 FROM
1895 gl_sets_of_books, hr_organization_information
1896 WHERE upper( org_information_context ) = upper( 'Accounting Information' )
1897 AND organization_id = c_organization_id
1898 AND set_of_books_id = to_number(org_information1);
1899
1900 -- rschaub: org_organization_definitions view too expensive
1901 --SELECT chart_of_accounts_id
1902 --FROM ORG_ORGANIZATION_DEFINITIONS
1903 --WHERE ORGANIZATION_ID = c_organization_id;
1904
1905 -- cursor to obtain GL period set name for an organization
1906 CURSOR c_calendar(c_organization_id NUMBER) IS
1907
1908 SELECT
1909 period_set_name
1910 FROM
1911 gl_sets_of_books, hr_organization_information
1912 WHERE upper( org_information_context ) = upper( 'Accounting Information' )
1913 AND organization_id = c_organization_id
1914 AND set_of_books_id = to_number(org_information1);
1915
1916 -- rschaub: too expensive
1917 --SELECT period_set_name
1918 --FROM GL_SETS_OF_BOOKS
1919 --WHERE SET_OF_BOOKS_ID IN (SELECT set_of_books_id
1920 -- FROM ORG_ORGANIZATION_DEFINITIONS
1921 -- WHERE ORGANIZATION_ID = c_organization_id);
1922
1923
1924 BEGIN
1925 IF G_DEBUG = 'Y' THEN
1926 INV_ORGHIERARCHY_PVT.Log
1927 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
1928 ,'Start of Proc:Org Hierarchy Level Property Access'
1929 );
1930 END IF;
1931
1932 INV_ORGHIERARCHY_PVT.Org_Hierarchy_List(p_org_hierarchy_name,
1933 p_org_hier_level_id,l_org_code_list);
1934
1935 IF (p_property_type = 'MASTER') THEN
1936 -- check for unique item master for the organizations
1937 l_index := l_org_code_list.FIRST;
1938 l_property_count := 0;
1939 l_old_master_orgid := NULL;
1940 WHILE (l_index <= l_org_code_list.LAST) LOOP
1941 OPEN c_item_master(l_org_code_list(l_index));
1942 FETCH c_item_master INTO l_master_orgid;
1943
1944 IF c_item_master%NOTFOUND THEN
1945 NULL;
1946 END IF;
1947
1948 IF l_old_master_orgid IS NULL THEN
1949 l_old_master_orgid := l_master_orgid;
1950 l_property_count := 1;
1951 ELSIF (l_master_orgid <> l_old_master_orgid) THEN
1952 l_property_count := l_property_count + 1;
1953 l_old_master_orgid := l_master_orgid;
1954 END IF;
1955
1956 CLOSE c_item_master;
1957
1958 IF (l_property_count > 1) THEN
1959 RAISE v_more_than_one_master_item;
1960 END IF;
1961
1962 l_index := l_org_code_list.NEXT(l_index);
1963 END LOOP;
1964
1965 -- ONE Item Master exists for the given organization hierarchy
1966 l_org_level_property_status := 'Y';
1967 IF G_DEBUG = 'Y' THEN
1968 INV_ORGHIERARCHY_PVT.Log
1969 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
1970 ,'Master Org Id: ' || to_char(l_master_orgid)
1971 );
1972 END IF;
1973
1974 ELSIF(p_property_type = 'CALENDAR') THEN
1975 -- check for unique calendar for the organizations
1976 l_index := l_org_code_list.FIRST;
1977 l_property_count := 0;
1978 l_old_period_set_name := NULL;
1979
1980 WHILE(l_index <= l_org_code_list.LAST) LOOP
1981 OPEN c_calendar(l_org_code_list(l_index));
1982 FETCH c_calendar INTO l_period_set_name;
1983
1984 IF c_calendar%NOTFOUND THEN
1985 NULL;
1986 END IF;
1987
1988 IF l_old_period_set_name IS NULL THEN
1989 l_old_period_set_name := l_period_set_name;
1990 l_property_count := 1;
1991 ELSIF (l_period_set_name <> l_old_period_set_name) THEN
1992 l_property_count := l_property_count + 1;
1993 l_old_period_set_name := l_period_set_name;
1994 END IF;
1995
1996 CLOSE c_calendar;
1997
1998 IF (l_property_count > 1) THEN
1999 RAISE v_more_than_one_calendar;
2000 END IF;
2001 l_index := l_org_code_list.NEXT(l_index);
2002 END LOOP;
2003 -- ONE Calendar exist for the given organization hierarchy
2004 l_org_level_property_status := 'Y';
2005 IF G_DEBUG = 'Y' THEN
2006 INV_ORGHIERARCHY_PVT.Log
2007 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2008 ,'Calendar: ' || l_period_set_name
2009 );
2010 END IF;
2011
2012 ELSIF(p_property_type = 'CHART_OF_ACCOUNTS') THEN
2013 -- check for unique chart of accounts for the organizations
2014 l_index := l_org_code_list.FIRST;
2015 l_property_count := 0;
2016 l_old_chart_accounts_id := NULL;
2017
2018 WHILE(l_index <= l_org_code_list.LAST) LOOP
2019 OPEN c_chart_of_accounts(l_org_code_list(l_index));
2020 FETCH c_chart_of_accounts INTO l_chart_accounts_id;
2021
2022 IF c_chart_of_accounts%NOTFOUND THEN
2023 NULL;
2024 END IF;
2025
2026 IF l_old_chart_accounts_id IS NULL THEN
2027 l_old_chart_accounts_id := l_chart_accounts_id;
2028 l_property_count := 1;
2029 ELSIF (l_chart_accounts_id <> l_old_chart_accounts_id) THEN
2030 l_property_count := l_property_count + 1;
2031 l_old_chart_accounts_id := l_chart_accounts_id;
2032 END IF;
2033
2034 CLOSE c_chart_of_accounts ;
2035
2036 IF l_property_count > 1 THEN
2037 RAISE v_one_chart_of_accounts;
2038 END IF;
2039
2040 l_index := l_org_code_list.NEXT(l_index);
2041 END LOOP;
2042 -- ONE Chart of Accounts exist for the given organization
2043 -- hierarchy
2044 l_org_level_property_status := 'Y';
2045 IF G_DEBUG = 'Y' THEN
2046 INV_ORGHIERARCHY_PVT.Log
2047 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2048 ,'Chart of Accounts:' || to_char(l_chart_accounts_id)
2049 );
2050 END IF;
2051
2052 END IF;
2053
2054 RETURN l_org_level_property_status;
2055
2056 IF G_DEBUG = 'Y' THEN
2057 INV_ORGHIERARCHY_PVT.Log
2058 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2059 ,'End of Proc:Org Hierarchy Level Property Access'
2060 );
2061 END IF;
2062
2063 EXCEPTION
2064
2065 WHEN v_more_than_one_master_item THEN
2066 l_org_level_property_status := 'N';
2067 /* This executable is used by concurrent program so
2068 Error/Exception logging should not depend on
2069 FND Debug Enabled profile otpion. Bug: 3555234
2070 IF G_DEBUG = 'Y' THEN
2071 */
2072 INV_ORGHIERARCHY_PVT.Log
2073 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2074 ,'More than one item master exists for the Organization Hierarchy'
2075 );
2076 --END IF;
2077 RETURN l_org_level_property_status;
2078
2079 WHEN v_more_than_one_calendar THEN
2080 l_org_level_property_status := 'N';
2081 /*This executable is used by concurrent program so
2082 Error/Exception logging should not depend on
2083 FND Debug Enabled profile otpion. Bug: 3555234
2084 IF G_DEBUG = 'Y' THEN
2085 */
2086 INV_ORGHIERARCHY_PVT.Log
2087 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2088 ,'More than one calendar exists for the Organization Hierarchy'
2089 );
2090 --END IF;
2091 RETURN l_org_level_property_status;
2092
2093 WHEN v_one_chart_of_accounts THEN
2094 l_org_level_property_status := 'N';
2095 /* This executable is used by concurrent program so
2096 Error/Exception logging should not depend on
2097 FND Debug Enabled profile otpion. Bug: 3555234
2098 IF G_DEBUG = 'Y' THEN
2099 */
2100 INV_ORGHIERARCHY_PVT.Log
2101 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2102 ,'More than one chart of accounts exists for the Organization
2103 Hierarchy'
2104 );
2105 --END IF;
2106 RETURN l_org_level_property_status;
2107
2108 WHEN OTHERS THEN
2109 l_errorcode := SQLCODE;
2110 l_errortext := SUBSTR(SQLERRM,1,200);
2111
2112 /*This executable is used by concurrent program so
2113 Error/Exception logging should not depend on
2114 FND Debug Enabled profile otpion. Log level has been
2115 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
2116 IF G_DEBUG = 'Y' THEN
2117 */
2118 INV_ORGHIERARCHY_PVT.Log
2119 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2120 ,to_char(l_errorcode) || l_errortext
2121 );
2122 --END IF;
2123 RETURN NULL;
2124
2125 END Org_Hier_Level_Property_Access;
2126
2127
2128 --========================================================================
2129 -- FUNCTION : Org_Hier_Level_Resp_Access PUBLIC
2130 -- PARAMETERS: p_api_version_number known api version
2131 -- p_org_id IN NUMBER Hierarchy Level Id
2132 -- (Organization Id)
2133 -- p_business_group_id IN NUMBER Business Group Id
2134 -- p_responsibility_id IN NUMBER Current Responsibility
2135 -- Id
2136 -- COMMENT : API accepts the Organization Id of an organization
2137 -- hierarchy level(organization name), business group id,
2138 -- current responsibility user has signed on and returns Y if
2139 -- the organization is a valid organization (unexpired) and has
2140 -- an access for the responsibility, N otherwise.
2141 --=========================================================================
2142 FUNCTION Org_Hier_Level_Resp_Access
2143 ( p_org_id IN NUMBER,
2144 p_business_group_id IN NUMBER,
2145 p_responsibility_id IN NUMBER
2146 )
2147 RETURN VARCHAR2 IS
2148
2149 -- cursor to check for the unexpired organization
2150 CURSOR c_unxpire_organization(c_org_id NUMBER, c_business_group_id NUMBER) IS
2151 SELECT 'Y'
2152 FROM HR_ALL_ORGANIZATION_UNITS
2153 WHERE ORGANIZATION_ID = c_org_id
2154 AND BUSINESS_GROUP_ID = c_business_group_id
2155 AND NVL(DATE_TO,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
2156
2157 -- Bug 9775787, cursor to check for the unexpired organization in global organization hierarchy
2158 CURSOR c_unxpire_organization_global(c_org_id NUMBER) IS
2159 SELECT 'Y'
2160 FROM HR_ALL_ORGANIZATION_UNITS
2161 WHERE ORGANIZATION_ID = c_org_id
2162 AND NVL(DATE_TO,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
2163
2164 CURSOR c_org_access(c_organization_id NUMBER) IS
2165 SELECT RESPONSIBILITY_ID
2166 FROM ORG_ACCESS
2167 WHERE ORGANIZATION_ID = c_organization_id;
2168
2169 l_responsibility_id NUMBER;
2170 l_org_resp_access_flag VARCHAR2(1);
2171 l_org_valid_flag VARCHAR2(1);
2172
2173 l_errorcode NUMBER;
2174 l_errortext VARCHAR2(200);
2175
2176 BEGIN
2177 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2178 THEN
2179 INV_ORGHIERARCHY_PVT.Log
2180 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2181 ,'Start of Proc:Org Hierarchy Level Responsibility Access'
2182 );
2183
2184 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2185 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access '
2186 , 'Start of Proc: p_org_id :'|| p_org_id ||' p_business_group_id :'|| p_business_group_id ||' p_responsibility_id :'|| p_responsibility_id
2187 );
2188 END IF;
2189
2190 -- initialize the flag
2191 l_org_resp_access_flag := 'N';
2192
2193 -- initialize the valid organization flag
2194 l_org_valid_flag:= 'N';
2195
2196 --Bug 9775787. Begin code changes.
2197 IF p_business_group_id IS NOT NULL
2198 THEN
2199
2200 -- check whether the organization is expired
2201 OPEN c_unxpire_organization(p_org_id,p_business_group_id);
2202 FETCH c_unxpire_organization INTO l_org_valid_flag;
2203
2204 IF c_unxpire_organization%NOTFOUND THEN
2205 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2206 THEN
2207 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2208 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access '
2209 , 'c_unxpire_organization%NOTFOUND organization expired '
2210 );
2211 END IF;
2212
2213 RAISE_APPLICATION_ERROR(-20255,
2214 'organization expired');
2215 /*This executable is used by concurrent program so
2216 Error/Exception logging should not depend on
2217 FND Debug Enabled profile otpion. Bug: 3555234
2218 IF G_DEBUG = 'Y' THEN
2219 */
2220 INV_ORGHIERARCHY_PVT.Log
2221 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2222 ,'Organization Id expired:' || to_char(p_org_id) ||
2223 'Business Group Id:' || to_char(p_business_group_id)
2224 );
2225 --END IF;
2226 END IF;
2227
2228 CLOSE c_unxpire_organization ;
2229
2230 ELSE
2231
2232 -- check whether the organization is expired
2233 OPEN c_unxpire_organization_global(p_org_id);
2234 FETCH c_unxpire_organization_global INTO l_org_valid_flag;
2235
2236 IF c_unxpire_organization_global%NOTFOUND THEN
2237 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2238 THEN
2239 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2240 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access '
2241 , 'c_unxpire_organization_global%NOTFOUND organization expired '
2242 );
2243 END IF;
2244
2245 RAISE_APPLICATION_ERROR(-20255,
2246 'organization expired');
2247 /*This executable is used by concurrent program so
2248 Error/Exception logging should not depend on
2249 FND Debug Enabled profile otpion. Bug: 3555234
2250 IF G_DEBUG = 'Y' THEN
2251 */
2252 INV_ORGHIERARCHY_PVT.Log
2253 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2254 ,'Organization Id expired:' || to_char(p_org_id)
2255 );
2256 --END IF;
2257 END IF;
2258
2259 CLOSE c_unxpire_organization_global;
2260
2261 END IF;
2262
2263 --Bug 9775787. End code changes.
2264
2265 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2266 THEN
2267 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2268 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access '
2269 , 'l_org_valid_flag :'|| l_org_valid_flag
2270 );
2271 END IF;
2272
2273 -- Check only if the organization is unexpired
2274 IF (l_org_valid_flag = 'Y') THEN
2275
2276 -- Open the cursor
2277 OPEN c_org_access(p_org_id);
2278
2279 -- Retrieve the first row to setup for the WHILE loop
2280 FETCH c_org_access INTO l_responsibility_id;
2281
2282 -- rows not found set the flag to Y
2283 IF c_org_access%NOTFOUND THEN
2284 l_org_resp_access_flag := 'Y';
2285 END IF;
2286
2287 -- continue looping while there are more rows to fetch
2288 WHILE c_org_access%FOUND LOOP
2289 -- check for the matching current user responsibility
2290 IF(p_responsibility_id = l_responsibility_id) THEN
2291 l_org_resp_access_flag := 'Y';
2292 EXIT;
2293 END IF;
2294 -- retrieve next user responsbility
2295 FETCH c_org_access INTO l_responsibility_id;
2296 END LOOP;
2297
2298 CLOSE c_org_access;
2299
2300 END IF; -- for the valid organization
2301
2302 IF G_DEBUG = 'Y' THEN
2303 INV_ORGHIERARCHY_PVT.Log
2304 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2305 ,'Organization Responsibility Valid Access Flag:'|| l_org_resp_access_flag
2306 );
2307 END IF;
2308
2309 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2310 THEN
2311 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2312 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Level_Resp_Access '
2313 , 'Exiting l_org_resp_access_flag :'|| l_org_resp_access_flag
2314 );
2315 END IF;
2316
2317 RETURN l_org_resp_access_flag;
2318
2319 IF G_DEBUG = 'Y' THEN
2320 INV_ORGHIERARCHY_PVT.Log
2321 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2322 ,'End of Proc:Org Hierarchy Level Responsibility Access'
2323 );
2324 END IF;
2325
2326 EXCEPTION
2327
2328 WHEN OTHERS THEN
2329 l_errorcode := SQLCODE;
2330 l_errortext := SUBSTR(SQLERRM,1,200);
2331 /*This executable is used by concurrent program so
2332 Error/Exception logging should not depend on
2333 FND Debug Enabled profile otpion. Log leve has b een
2334 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
2335 IF G_DEBUG = 'Y' THEN
2336 */
2337 INV_ORGHIERARCHY_PVT.Log
2338 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2339 ,to_char(l_errorcode) || l_errortext
2340 );
2341 --END IF;
2342 IF c_unxpire_organization%ISOPEN THEN
2343 CLOSE c_unxpire_organization;
2344 END IF;
2345
2346 IF c_unxpire_organization_global%ISOPEN THEN
2347 CLOSE c_unxpire_organization_global;
2348 END IF;
2349
2350 RETURN NULL;
2351
2352 END Org_Hier_Level_Resp_Access;
2353
2354
2355 --========================================================================
2356 -- FUNCTION : Org_Hier_Origin_Resp_Access PUBLIC
2357 -- PARAMETERS: p_api_version_number known api version
2358 -- p_org_id IN NUMBER Hierarchy Origin Id
2359 -- (Organization Id)
2360 -- p_responsibility_id IN NUMBER Current Responsibility
2361 -- Id
2362 -- COMMENT : API accepts the Organization Id of an organization
2363 -- hierarchy origin(organization name),
2364 -- current responsibility user has signed on and returns Y if
2365 -- the organization is a valid organization (unexpired) and has
2366 -- an access for the responsibility, N otherwise.
2367 --=========================================================================
2368 FUNCTION Org_Hier_Origin_Resp_Access
2369 ( p_org_id IN NUMBER,
2370 p_responsibility_id IN NUMBER
2371 )
2372 RETURN VARCHAR2 IS
2373
2374
2375 CURSOR c_origin_resp_access(c_org_id NUMBER, c_responsibility_id NUMBER ) IS
2376 SELECT 'Y'
2377 FROM HR_ALL_ORGANIZATION_UNITS org
2378 WHERE
2379
2380 -- expiration check
2381 org.organization_id = c_org_id
2382 AND ( org.date_to >= SYSDATE
2383 OR org.date_to IS NULL
2384 )
2385
2386 -- inv security access check
2387 AND ( NOT EXISTS( SELECT 1 FROM ORG_ACCESS acc
2388 WHERE acc.organization_id = c_org_id )
2389 OR EXISTS( SELECT 1 FROM ORG_ACCESS acc
2390 WHERE acc.organization_id = c_org_id
2391 AND acc.responsibility_id = c_responsibility_id
2392 )
2393 );
2394
2395
2396 l_origin_resp_access_flag VARCHAR2(1);
2397
2398 l_errorcode NUMBER;
2399 l_errortext VARCHAR2(200);
2400
2401 BEGIN
2402
2403 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2404 THEN
2405 INV_ORGHIERARCHY_PVT.Log
2406 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2407 ,'Start of Proc:Org Hierarchy Origin Responsibility Access'
2408 );
2409
2410 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2411 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Origin_Resp_Access '
2412 , 'Start of API p_org_id :'|| p_org_id ||' p_responsibility_id :'|| p_responsibility_id
2413 );
2414 END IF;
2415
2416 -- initialize the flag
2417 l_origin_resp_access_flag := 'N';
2418
2419 -- check whether the organization is unexpired and has responsibility
2420 -- access
2421 OPEN c_origin_resp_access(p_org_id, p_responsibility_id);
2422 FETCH c_origin_resp_access INTO l_origin_resp_access_flag;
2423
2424 IF c_origin_resp_access%NOTFOUND THEN
2425 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2426 THEN
2427 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2428 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Origin_Resp_Access '
2429 , 'c_origin_resp_access%NOTFOUND Org has no access '
2430 );
2431 END IF;
2432
2433 RAISE_APPLICATION_ERROR(-20255,
2434 'organization has no access');
2435 /* This executable is used by concurrent program so
2436 Error/Exception logging should not depend on
2437 FND Debug Enabled profile otpion. Bug: 3555234
2438 IF G_DEBUG = 'Y' THEN
2439 */
2440 INV_ORGHIERARCHY_PVT.Log
2441 (INV_ORGHIERARCHY_PVT.G_LOG_ERROR
2442 ,'Organization Id has no access:' || to_char(p_org_id)
2443 );
2444 --END IF;
2445 END IF;
2446
2447 CLOSE c_origin_resp_access;
2448
2449 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2450 THEN
2451 INV_ORGHIERARCHY_PVT.Log
2452 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2453 ,'Origin responsibility access flag:'|| l_origin_resp_access_flag
2454 );
2455
2456 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2457 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Origin_Resp_Access '
2458 , 'l_origin_resp_access_flag :' || l_origin_resp_access_flag
2459 );
2460 END IF;
2461
2462 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2463 THEN
2464 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2465 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Origin_Resp_Access '
2466 , 'Exiting l_origin_resp_access_flag :'||l_origin_resp_access_flag
2467 );
2468 END IF;
2469
2470 RETURN l_origin_resp_access_flag;
2471
2472 IF G_DEBUG = 'Y' THEN
2473 INV_ORGHIERARCHY_PVT.Log
2474 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2475 ,'End of Proc:Org Hierarchy Origin Responsibility Access'
2476 );
2477 END IF;
2478
2479 EXCEPTION
2480
2481 WHEN OTHERS THEN
2482 l_errorcode := SQLCODE;
2483 l_errortext := SUBSTR(SQLERRM,1,200);
2484 /*This executable is used by concurrent program so
2485 Error/Exception logging should not depend on
2486 FND Debug Enabled profile otpion. Log level has been
2487 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
2488 IF G_DEBUG = 'Y' THEN
2489 */
2490 INV_ORGHIERARCHY_PVT.Log
2491 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2492 ,to_char(l_errorcode) || l_errortext
2493 );
2494 --END IF;
2495
2496 IF c_origin_resp_access%ISOPEN THEN
2497 CLOSE c_origin_resp_access;
2498 END IF;
2499
2500 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2501 THEN
2502 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2503 , 'INV_ORGHIERARCHY_PVT.Org_Hier_Origin_Resp_Access '
2504 , 'Exception :'||sqlerrm(sqlcode)
2505 );
2506 END IF;
2507
2508 RETURN NULL;
2509
2510 END Org_Hier_Origin_Resp_Access;
2511
2512
2513 --========================================================================
2514 -- FUNCTION : Org_exists_in_hierarchy PUBLIC
2515 -- PARAMETERS: p_organization_id IN NUMBER Inventory Organization Id
2516 --
2517 -- COMMENT : This API accepts the organization id and returns Y if the
2518 -- organization id exists in the index list
2519 --=========================================================================
2520 FUNCTION Org_exists_in_hierarchy
2521 ( p_organization_id IN NUMBER)
2522 RETURN VARCHAR2 IS
2523
2524 l_org_index BINARY_INTEGER;
2525 l_org_exists_flag VARCHAR2(1);
2526
2527 l_errorcode NUMBER;
2528 l_errortext VARCHAR2(200);
2529
2530 BEGIN
2531 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2532 THEN
2533 INV_ORGHIERARCHY_PVT.Log
2534 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2535 ,'Start of Proc:Org exists in hierarchy'
2536 );
2537
2538 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2539 , 'INV_ORGHIERARCHY_PVT.Org_exists_in_hierarchy '
2540 , 'Start of API p_organization_id :'|| p_organization_id
2541 );
2542 END IF;
2543
2544 -- initialize org exists flag
2545 l_org_exists_flag := 'N';
2546
2547 -- assign the organization_id into binary integer data type
2548 l_org_index := p_organization_id;
2549
2550 -- Check organization id exists
2551 -- note that index contains organization id
2552 IF g_orgid_index_list.EXISTS(l_org_index) THEN
2553 l_org_exists_flag := 'Y';
2554 END IF;
2555
2556 IF G_DEBUG = 'Y' AND (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2557 THEN
2558 INV_ORGHIERARCHY_PVT.Log
2559 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
2560 ,'Organization exists flag:'|| l_org_exists_flag
2561 );
2562
2563 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
2564 , 'INV_ORGHIERARCHY_PVT.Org_exists_in_hierarchy '
2565 , 'Organization exists flag :' || l_org_exists_flag
2566 );
2567 END IF;
2568
2569 RETURN l_org_exists_flag;
2570
2571 IF G_DEBUG = 'Y' THEN
2572 INV_ORGHIERARCHY_PVT.Log
2573 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2574 ,'End of Proc:Org exists in hierarchy'
2575 );
2576 END IF;
2577
2578 EXCEPTION
2579
2580 WHEN OTHERS THEN
2581 l_errorcode := SQLCODE;
2582 l_errortext := SUBSTR(SQLERRM,1,200);
2583 /*This executable is used by concurrent program so
2584 Error/Exception logging should not depend on
2585 FND Debug Enabled profile otpion. Log level has been
2586 changed from G_LOG_ERROR to G_LOG_EXCEPTION. Bug: 3555234
2587 IF G_DEBUG = 'Y' THEN
2588 */
2589 INV_ORGHIERARCHY_PVT.Log
2590 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
2591 ,to_char(l_errorcode) || l_errortext
2592 );
2593 --END IF;
2594 RETURN NULL;
2595
2596 END Org_exists_in_hierarchy;
2597
2598
2599
2600 --========================================================================
2601 -- PROCEDURE : Insert_hierarchy_index_list PUBLIC
2602 -- PARAMETERS: p_orgid_tbl_list IN orgID_tbl_type Orgid list of an
2603 -- hierarchy
2604 -- COMMENT : This API copies the organization list into the global
2605 -- variable organization id index list. The table index is
2606 -- the organization_id
2607 -- This api is used in the form: Transaction Open Interface
2608 --========================================================================
2609 PROCEDURE Insert_hierarchy_index_list
2610 ( p_orgid_tbl_list IN orgID_tbl_type)
2611 IS
2612
2613 l_org_index BINARY_INTEGER;
2614 l_organization_id hr_all_organization_units.organization_id%TYPE;
2615
2616 BEGIN
2617 IF G_DEBUG = 'Y' THEN
2618 INV_ORGHIERARCHY_PVT.Log
2619 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2620 ,'Start of Proc: Insert hierarchy index list'
2621 );
2622 END IF;
2623
2624 FOR v_index IN p_orgid_tbl_list.FIRST .. p_orgid_tbl_list.LAST LOOP
2625 l_organization_id := p_orgid_tbl_list(v_index);
2626 l_org_index := l_organization_id;
2627 g_orgid_index_list(l_org_index) := l_organization_id;
2628 END LOOP;
2629
2630 IF G_DEBUG = 'Y' THEN
2631 INV_ORGHIERARCHY_PVT.Log
2632 (INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
2633 ,'End of Proc: Insert hierarchy index list'
2634 );
2635 END IF;
2636
2637 END;
2638
2639
2640
2641 --========================================================================
2642 -- PROCEDURE : Log_Initialize PUBLIC
2643 -- COMMENT : Initializes the log facility. It should be called from
2644 -- the top level procedure of each concurrent program
2645 --=======================================================================--
2646 PROCEDURE Log_Initialize
2647 IS
2648 BEGIN
2649 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
2650 IF g_log_level IS NULL THEN
2651 g_log_mode := 'OFF';
2652 ELSE
2653 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
2654 g_log_mode := 'SRS';
2655 ELSE
2656 g_log_mode := 'SQL';
2657 END IF;
2658 END IF;
2659
2660 END Log_Initialize;
2661
2662
2663 --========================================================================
2664 -- PROCEDURE : Log PUBLIC
2665 -- PARAMETERS: p_level IN priority of the message - from
2666 -- highest to lowest:
2667 -- -- G_LOG_ERROR
2668 -- -- G_LOG_EXCEPTION
2669 -- -- G_LOG_EVENT
2670 -- -- G_LOG_PROCEDURE
2671 -- -- G_LOG_STATEMENT
2672 -- p_msg IN message to be print on the log
2673 -- file
2674 -- COMMENT : Add an entry to the log
2675 --=======================================================================--
2676 PROCEDURE Log
2677 ( p_priority IN NUMBER
2678 , p_msg IN VARCHAR2
2679 )
2680 IS
2681 BEGIN
2682 --Additional IF clause is added to print log message if Priority is
2683 --Error or Exception. Bug: 3555234
2684 IF ((p_priority = G_LOG_ERROR) OR
2685 (p_priority = G_LOG_EXCEPTION) OR
2686 (p_priority = G_LOG_PRINT) OR
2687 ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level)))
2688 THEN
2689 IF g_log_mode = 'SQL'
2690 THEN
2691 -- SQL*Plus session: uncomment the next line during unit test
2692 -- DBMS_OUTPUT.put_line(p_msg);
2693 NULL;
2694 ELSE
2695 -- Concurrent request
2696 FND_FILE.put_line
2697 ( FND_FILE.log
2698 , p_msg
2699 );
2700 END IF;
2701 END IF;
2702 EXCEPTION
2703 WHEN OTHERS THEN
2704 NULL;
2705 END Log;
2706
2707
2708 END INV_ORGHIERARCHY_PVT;
[Home] [Help]