[Home] [Help]
PACKAGE BODY: APPS.PER_ORG_STRUCTURES_PKG
Source
1 PACKAGE BODY PER_ORG_STRUCTURES_PKG as
2 /* $Header: peors01t.pkb 120.1 2006/01/04 14:54:24 vbanner noship $ */
3 ------------------------------------------------------------------------------
4 PROCEDURE form_startup(p_business_group_id NUMBER
5 ,p_security_profile_id IN OUT NOCOPY NUMBER
6 ,p_view_all_orgs IN OUT NOCOPY VARCHAR2
7 ,p_hr_installed IN OUT NOCOPY VARCHAR2
8 ,p_pa_installed IN OUT NOCOPY VARCHAR2)is
9 --
10 -- local variables
11 --
12 l_pa_installed BOOLEAN;
13 l_industry VARCHAR2(1);
14 function get_hr_status return VARCHAR2 is
15 --
16 l_hr_installed varchar2(1);
17 begin
18 --
19 -- Get status of Any of HR's Product set.
20 --
21 select 'I'
22 into l_hr_installed
23 from sys.dual
24 where exists (select 'I'
25 from fnd_product_installations
26 where application_id between 800 and 899
27 and status = 'I');
28 return l_hr_installed;
29 exception
30 when no_data_found then
31 return 'S';
32 end;
33 --
34 begin
35 l_pa_installed := fnd_installation.get(appl_id => 275
36 ,dep_appl_id => 275
37 ,status => p_pa_installed
38 ,industry => l_industry);
39 --
40 -- Get HR INstallation status.
41 --
42 p_hr_installed := get_hr_status;
43 -- Now the security Profile.
44 -- Bug 4911254 ammended where clause.
45 --
46 begin
47 select psp.view_all_organizations_flag,
48 psp.security_profile_id
49 into p_view_all_orgs,
50 p_security_profile_id
51 from per_security_profiles psp
52 where psp.security_profile_id = fnd_profile.value('PER_SECURITY_PROFILE_ID')
53 and ( psp.business_group_id + 0 = p_business_group_id
54 or psp.business_group_id is null);
55 exception
56 when no_data_found then
57 hr_utility.set_message('801', 'HR_289521_GLOBAL_SEC_PROFILE');
58 hr_utility.set_message_token('PROCEDURE', 'form_startup');
59 hr_utility.set_message_token('STEP', '1');
60 hr_utility.raise_error;
61 end;
62 end form_startup;
63 ------------------------------------------------------------------------------
64 PROCEDURE check_name_unique(p_name VARCHAR2
65 ,p_business_group_id NUMBER
66 ,p_rowid VARCHAR2) is
67 l_exists VARCHAR2(1);
68 begin
69 select 'Y'
70 into l_exists
71 from sys.dual
72 where exists(
73 select 'Name exists'
74 from per_organization_structures os
75 where ( os.business_group_id + 0 = p_business_group_id
76 or ( os.business_group_id is null
77 and p_business_group_id is null))
78 and upper(os.name) = upper(p_name)
79 and (os.rowid <> p_rowid
80 or p_rowid is null));
81 --
82 hr_utility.set_message('801','PER_7901_SYS_DUPLICATE_RECORDS');
83 hr_utility.raise_error;
84 --
85 exception
86 when no_data_found then null;
87 when others then raise;
88 end;
89 ------------------------------------------------------------------------------
90 Procedure check_primary_flag(p_primary_flag VARCHAR2
91 ,p_business_group_id NUMBER
92 ,p_rowid VARCHAR2
93 ) is
94 --
95 l_exists VARCHAR2(1);
96 --
97 begin
98 select 'Y'
99 into l_exists
100 from sys.dual
101 where exists(
102 select 'Primary exists'
103 from per_organization_structures os
104 where ( os.business_group_id + 0 = p_business_group_id
105 or ( os.business_group_id is null
106 and p_business_group_id is null))
107 and os.primary_structure_flag = p_primary_flag
108 and (os.rowid <> p_rowid
109 or p_rowid is null));
110 --
111 hr_utility.set_message('801','HR_6085_PO_POS_ONE_PRIMARY');
112 hr_utility.raise_error;
113 --
114 exception
115 when no_data_found then null;
116 when others then raise;
117 end;
118 ------------------------------------------------------------------------------
119 PROCEDURE check_position_control_flag(
120 p_organization_structure_id NUMBER
121 ,p_pos_control_structure_flag VARCHAR2
122 ,p_business_group_id NUMBER
123 ) is
124
125 CURSOR C1 is
126 select position_control_structure_flg
127 from per_organization_structures
128 where position_control_structure_flg = 'Y'
129 and organization_structure_id <> nvl(p_organization_structure_id, -1)
130 -- BUG 1694549
131 -- add predicate to check for other pos control structures
132 -- within current business_group only, to allow 1 pos control org hierarchy
133 -- per business_group.
134 and business_group_id = p_business_group_id;
135
136
137 cursor C2 is
138 select 1 from dual
139 where exists (
140 select 1
141 from per_org_structure_elements ose,
142 per_org_structure_versions osv
143 where osv.org_structure_version_id = ose.org_structure_version_id(+)
144 and (ose.position_control_enabled_flag = 'Y' or
145 osv.topnode_pos_ctrl_enabled_flag = 'Y'));
146
147 CURSOR C3 is
148 select *
149 from per_organization_structures
150 where organization_structure_id = nvl(p_organization_structure_id, -1);
151
152 Recinfo C3%ROWTYPE;
153
154 l_duplicate varchar2(10);
155 l_residual number;
156
157 begin
158 --
159 open c3;
160 fetch c3 into Recinfo;
161 close c3;
162
163 if p_pos_control_structure_flag = 'Y' then
164 --
165 open c1;
166 fetch c1 into l_duplicate;
167
168 if c1%found then
169 --
170 close c1;
171 hr_utility.set_message('800','PER_50053_POS_CTRL_DUPLICATED');
172 hr_utility.raise_error;
173 --
174 end if;
175
176 close c1;
177 --
178 elsif ((p_pos_control_structure_flag = 'N') and
179 (Recinfo.position_control_structure_flg = 'Y')) then
180 --
181 open c2;
182 fetch c2 into l_residual;
183
184 if c2%found then
185 --
186 close c2;
187 hr_utility.set_message('800', 'PER_50054_POS_CTRL_CHILD');
188 hr_utility.raise_error;
189 --
190 end if;
191
192 close c2;
193 --
194 end if;
195 --
196 end check_position_control_flag;
197 ------------------------------------------------------------------------------
198 PROCEDURE delete_check(p_organization_structure_id NUMBER
199 ,p_business_group_id NUMBER
200 ,p_pa_installed VARCHAR2
201 ) is
202 --
203 l_temp VARCHAR2(1);
204 --
205 begin
206 begin
207 --
208 -- Is there an osv row below the current Hierarchy.
209 --
210 select 1
211 into l_temp
212 from sys.dual
213 where exists( select 1
214 from per_org_structure_versions osv
215 where ( osv.business_group_id + 0 = p_business_group_id
216 or ( osv.business_group_id is null
217 and p_business_group_id is null))
218 and osv.organization_structure_id =
219 p_organization_structure_id
220 );
221 -- Id I get to here then there's exists a row
222 -- so error
223 hr_utility.set_message('801','HR_6084_PO_POS_HAS_HIER_VER');
224 hr_utility.raise_error;
225 exception
226 when no_data_found then
227 null;
228 end;
229 --
230 if P_pa_installed = 'I' then
231 pa_org.pa_os_predel_validation(p_organization_structure_id);
232 end if;
233 end delete_check;
234 ------------------------------------------------------------------------------
235 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
236 X_Organization_Structure_Id IN OUT NOCOPY NUMBER,
237 X_Business_Group_Id NUMBER,
238 X_Name VARCHAR2,
239 X_Comments VARCHAR2,
240 X_Primary_Structure_Flag VARCHAR2,
241 X_Attribute_Category VARCHAR2,
242 X_Attribute1 VARCHAR2,
243 X_Attribute2 VARCHAR2,
244 X_Attribute3 VARCHAR2,
245 X_Attribute4 VARCHAR2,
246 X_Attribute5 VARCHAR2,
247 X_Attribute6 VARCHAR2,
248 X_Attribute7 VARCHAR2,
249 X_Attribute8 VARCHAR2,
250 X_Attribute9 VARCHAR2,
251 X_Attribute10 VARCHAR2,
252 X_Attribute11 VARCHAR2,
253 X_Attribute12 VARCHAR2,
254 X_Attribute13 VARCHAR2,
255 X_Attribute14 VARCHAR2,
256 X_Attribute15 VARCHAR2,
257 X_Attribute16 VARCHAR2,
258 X_Attribute17 VARCHAR2,
259 X_Attribute18 VARCHAR2,
260 X_Attribute19 VARCHAR2,
261 X_Attribute20 VARCHAR2,
262 X_Pos_Control_Structure_Flag VARCHAR2
263 ) IS
264 --
265 CURSOR C IS SELECT rowid FROM PER_ORGANIZATION_STRUCTURES
266 WHERE organization_structure_id = X_Organization_Structure_Id;
267 CURSOR C2 IS SELECT PER_ORGANIZATION_STRUCTURES_S.nextval FROM sys.dual;
268 --
269 BEGIN
270 --
271 if (X_Organization_Structure_Id is NULL) then
272 OPEN C2;
273 FETCH C2 INTO X_Organization_Structure_Id;
274 CLOSE C2;
275 end if;
276
277 check_position_control_flag(X_Organization_Structure_Id,
278 X_Pos_Control_Structure_Flag,
279 X_Business_Group_Id);
280
281 INSERT INTO PER_ORGANIZATION_STRUCTURES(
282 organization_structure_id,
283 business_group_id,
284 name,
285 comments,
286 primary_structure_flag,
287 attribute_category,
288 attribute1,
289 attribute2,
290 attribute3,
291 attribute4,
292 attribute5,
293 attribute6,
294 attribute7,
295 attribute8,
296 attribute9,
297 attribute10,
298 attribute11,
299 attribute12,
300 attribute13,
301 attribute14,
302 attribute15,
303 attribute16,
304 attribute17,
305 attribute18,
306 attribute19,
307 attribute20,
308 position_control_structure_flg
309 ) VALUES (
310 X_Organization_Structure_Id,
311 X_Business_Group_Id,
312 X_Name,
313 X_Comments,
314 X_Primary_Structure_Flag,
315 X_Attribute_Category,
316 X_Attribute1,
317 X_Attribute2,
318 X_Attribute3,
319 X_Attribute4,
320 X_Attribute5,
321 X_Attribute6,
322 X_Attribute7,
323 X_Attribute8,
324 X_Attribute9,
325 X_Attribute10,
326 X_Attribute11,
327 X_Attribute12,
328 X_Attribute13,
329 X_Attribute14,
330 X_Attribute15,
331 X_Attribute16,
332 X_Attribute17,
333 X_Attribute18,
334 X_Attribute19,
335 X_Attribute20,
336 X_Pos_Control_Structure_Flag
337 );
338 OPEN C;
339 FETCH C INTO X_Rowid;
340 if (C%NOTFOUND) then
341 CLOSE C;
342 RAISE NO_DATA_FOUND;
343 end if;
344 CLOSE C;
345 END Insert_Row;
346 ------------------------------------------------------------------------------
347 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
348 X_Organization_Structure_Id NUMBER,
349 X_Business_Group_Id NUMBER,
350 X_Name VARCHAR2,
351 X_Comments VARCHAR2,
352 X_Primary_Structure_Flag VARCHAR2,
353 X_Attribute_Category VARCHAR2,
354 X_Attribute1 VARCHAR2,
355 X_Attribute2 VARCHAR2,
356 X_Attribute3 VARCHAR2,
357 X_Attribute4 VARCHAR2,
358 X_Attribute5 VARCHAR2,
359 X_Attribute6 VARCHAR2,
360 X_Attribute7 VARCHAR2,
361 X_Attribute8 VARCHAR2,
362 X_Attribute9 VARCHAR2,
363 X_Attribute10 VARCHAR2,
364 X_Attribute11 VARCHAR2,
365 X_Attribute12 VARCHAR2,
366 X_Attribute13 VARCHAR2,
367 X_Attribute14 VARCHAR2,
368 X_Attribute15 VARCHAR2,
369 X_Attribute16 VARCHAR2,
370 X_Attribute17 VARCHAR2,
371 X_Attribute18 VARCHAR2,
372 X_Attribute19 VARCHAR2,
373 X_Attribute20 VARCHAR2,
374 X_Pos_Control_Structure_Flag VARCHAR2
375 ) IS
376 --
377 CURSOR C IS
378 SELECT *
379 FROM PER_ORGANIZATION_STRUCTURES
380 WHERE rowid = X_Rowid
381 FOR UPDATE of Organization_Structure_Id NOWAIT;
382 Recinfo C%ROWTYPE;
383 --
384 BEGIN
385 OPEN C;
386 FETCH C INTO Recinfo;
387 if (C%NOTFOUND) then
388 CLOSE C;
389 RAISE NO_DATA_FOUND;
390 end if;
391 CLOSE C;
392 --
393 -- Kludge required as forms automatically strips off
394 -- trailing spaces
395 --
396 recinfo.name := rtrim(recinfo.name);
397 recinfo.comments := rtrim(recinfo.comments);
398 recinfo.primary_structure_flag := rtrim(recinfo.primary_structure_flag);
399 recinfo.attribute_category := rtrim(recinfo.attribute_category);
400 recinfo.attribute1 := rtrim(recinfo.attribute1);
401 recinfo.attribute2 := rtrim(recinfo.attribute2);
402 recinfo.attribute3 := rtrim(recinfo.attribute3);
403 recinfo.attribute4 := rtrim(recinfo.attribute4);
404 recinfo.attribute5 := rtrim(recinfo.attribute5);
405 recinfo.attribute6 := rtrim(recinfo.attribute6);
406 recinfo.attribute7 := rtrim(recinfo.attribute7);
407 recinfo.attribute8 := rtrim(recinfo.attribute8);
408 recinfo.attribute9 := rtrim(recinfo.attribute9);
409 recinfo.attribute10 := rtrim(recinfo.attribute10);
410 recinfo.attribute11 := rtrim(recinfo.attribute11);
411 recinfo.attribute12 := rtrim(recinfo.attribute12);
412 recinfo.attribute13 := rtrim(recinfo.attribute13);
413 recinfo.attribute14 := rtrim(recinfo.attribute14);
414 recinfo.attribute15 := rtrim(recinfo.attribute15);
415 recinfo.attribute16 := rtrim(recinfo.attribute16);
416 recinfo.attribute17 := rtrim(recinfo.attribute17);
417 recinfo.attribute18 := rtrim(recinfo.attribute18);
418 recinfo.attribute19 := rtrim(recinfo.attribute19);
419 recinfo.attribute20 := rtrim(recinfo.attribute20);
420 --
421 if (
422 ( (Recinfo.organization_structure_id = X_Organization_Structure_Id)
423 OR ( (Recinfo.organization_structure_id IS NULL)
424 AND (X_Organization_Structure_Id IS NULL)))
425 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
426 OR ( (Recinfo.business_group_id IS NULL)
427 AND (X_Business_Group_Id IS NULL)))
428 AND ( (Recinfo.name = X_Name)
429 OR ( (Recinfo.name IS NULL)
430 AND (X_Name IS NULL)))
431 AND ( (Recinfo.comments = X_Comments)
432 OR ( (Recinfo.comments IS NULL)
433 AND (X_Comments IS NULL)))
434 AND ( (Recinfo.primary_structure_flag = X_Primary_Structure_Flag)
435 OR ( (Recinfo.primary_structure_flag IS NULL)
436 AND (X_Primary_Structure_Flag IS NULL)))
437 AND ( (Recinfo.attribute_category = X_Attribute_Category)
438 OR ( (Recinfo.attribute_category IS NULL)
439 AND (X_Attribute_Category IS NULL)))
440 AND ( (Recinfo.attribute1 = X_Attribute1)
441 OR ( (Recinfo.attribute1 IS NULL)
442 AND (X_Attribute1 IS NULL)))
443 AND ( (Recinfo.attribute2 = X_Attribute2)
444 OR ( (Recinfo.attribute2 IS NULL)
445 AND (X_Attribute2 IS NULL)))
446 AND ( (Recinfo.attribute3 = X_Attribute3)
447 OR ( (Recinfo.attribute3 IS NULL)
448 AND (X_Attribute3 IS NULL)))
449 AND ( (Recinfo.attribute4 = X_Attribute4)
450 OR ( (Recinfo.attribute4 IS NULL)
451 AND (X_Attribute4 IS NULL)))
452 AND ( (Recinfo.attribute5 = X_Attribute5)
453 OR ( (Recinfo.attribute5 IS NULL)
454 AND (X_Attribute5 IS NULL)))
455 AND ( (Recinfo.attribute6 = X_Attribute6)
456 OR ( (Recinfo.attribute6 IS NULL)
457 AND (X_Attribute6 IS NULL)))
458 AND ( (Recinfo.attribute7 = X_Attribute7)
459 OR ( (Recinfo.attribute7 IS NULL)
460 AND (X_Attribute7 IS NULL)))
461 AND ( (Recinfo.attribute8 = X_Attribute8)
462 OR ( (Recinfo.attribute8 IS NULL)
463 AND (X_Attribute8 IS NULL)))
464 AND ( (Recinfo.attribute9 = X_Attribute9)
465 OR ( (Recinfo.attribute9 IS NULL)
466 AND (X_Attribute9 IS NULL)))
467 AND ( (Recinfo.attribute10 = X_Attribute10)
468 OR ( (Recinfo.attribute10 IS NULL)
469 AND (X_Attribute10 IS NULL)))
470 AND ( (Recinfo.attribute11 = X_Attribute11)
471 OR ( (Recinfo.attribute11 IS NULL)
472 AND (X_Attribute11 IS NULL)))
473 AND ( (Recinfo.attribute12 = X_Attribute12)
474 OR ( (Recinfo.attribute12 IS NULL)
475 AND (X_Attribute12 IS NULL)))
476 AND ( (Recinfo.attribute13 = X_Attribute13)
477 OR ( (Recinfo.attribute13 IS NULL)
478 AND (X_Attribute13 IS NULL)))
479 AND ( (Recinfo.attribute14 = X_Attribute14)
480 OR ( (Recinfo.attribute14 IS NULL)
481 AND (X_Attribute14 IS NULL)))
482 AND ( (Recinfo.attribute15 = X_Attribute15)
483 OR ( (Recinfo.attribute15 IS NULL)
484 AND (X_Attribute15 IS NULL)))
485 AND ( (Recinfo.attribute16 = X_Attribute16)
486 OR ( (Recinfo.attribute16 IS NULL)
487 AND (X_Attribute16 IS NULL)))
488 AND ( (Recinfo.attribute17 = X_Attribute17)
489 OR ( (Recinfo.attribute17 IS NULL)
490 AND (X_Attribute17 IS NULL)))
491 AND ( (Recinfo.attribute18 = X_Attribute18)
492 OR ( (Recinfo.attribute18 IS NULL)
493 AND (X_Attribute18 IS NULL)))
494 AND ( (Recinfo.attribute19 = X_Attribute19)
495 OR ( (Recinfo.attribute19 IS NULL)
496 AND (X_Attribute19 IS NULL)))
497 AND ( (Recinfo.attribute20 = X_Attribute19)
498 OR ( (Recinfo.attribute20 IS NULL)
499 AND (X_Attribute20 IS NULL)))
500 AND ( (Recinfo.position_control_structure_flg = X_Pos_Control_Structure_Flag)
501 OR ( (Recinfo.position_control_structure_flg IS NULL)
502 AND (X_Pos_Control_Structure_Flag IS NULL)))
503 ) then
504 return;
505 else
506 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
507 APP_EXCEPTION.RAISE_EXCEPTION;
508 end if;
509 END Lock_Row;
510 ------------------------------------------------------------------------------
511 PROCEDURE Update_Row(X_Rowid VARCHAR2,
512 X_Organization_Structure_Id NUMBER,
513 X_Business_Group_Id NUMBER,
514 X_Name VARCHAR2,
515 X_Comments VARCHAR2,
516 X_Primary_Structure_Flag VARCHAR2,
517 X_Attribute_Category VARCHAR2,
518 X_Attribute1 VARCHAR2,
519 X_Attribute2 VARCHAR2,
520 X_Attribute3 VARCHAR2,
521 X_Attribute4 VARCHAR2,
522 X_Attribute5 VARCHAR2,
523 X_Attribute6 VARCHAR2,
524 X_Attribute7 VARCHAR2,
525 X_Attribute8 VARCHAR2,
526 X_Attribute9 VARCHAR2,
527 X_Attribute10 VARCHAR2,
528 X_Attribute11 VARCHAR2,
529 X_Attribute12 VARCHAR2,
530 X_Attribute13 VARCHAR2,
531 X_Attribute14 VARCHAR2,
532 X_Attribute15 VARCHAR2,
533 X_Attribute16 VARCHAR2,
534 X_Attribute17 VARCHAR2,
535 X_Attribute18 VARCHAR2,
536 X_Attribute19 VARCHAR2,
537 X_Attribute20 VARCHAR2,
538 X_Pos_Control_Structure_Flag VARCHAR2
539 ) IS
540 --
541 BEGIN
542
543 check_position_control_flag(X_Organization_Structure_Id,
544 X_Pos_Control_Structure_Flag,
545 X_Business_Group_Id);
546
547 UPDATE PER_ORGANIZATION_STRUCTURES
548 SET
549 organization_structure_id = X_Organization_Structure_Id,
550 business_group_id = X_Business_Group_Id,
551 name = X_Name,
552 comments = X_Comments,
553 primary_structure_flag = X_Primary_Structure_Flag,
554 attribute_category = X_Attribute_Category,
555 attribute1 = X_Attribute1,
556 attribute2 = X_Attribute2,
557 attribute3 = X_Attribute3,
558 attribute4 = X_Attribute4,
559 attribute5 = X_Attribute5,
560 attribute6 = X_Attribute6,
561 attribute7 = X_Attribute7,
562 attribute8 = X_Attribute8,
563 attribute9 = X_Attribute9,
564 attribute10 = X_Attribute10,
565 attribute11 = X_Attribute11,
566 attribute12 = X_Attribute12,
567 attribute13 = X_Attribute13,
568 attribute14 = X_Attribute14,
569 attribute15 = X_Attribute15,
570 attribute16 = X_Attribute16,
571 attribute17 = X_Attribute17,
572 attribute18 = X_Attribute18,
573 attribute19 = X_Attribute19,
574 attribute20 = X_Attribute20,
575 position_control_structure_flg = X_Pos_Control_Structure_Flag
576 WHERE rowid = X_rowid;
577 --
578 if (SQL%NOTFOUND) then
579 RAISE NO_DATA_FOUND;
580 end if;
581 --
582 END Update_Row;
583 ------------------------------------------------------------------------------
584 PROCEDURE Delete_Row(X_Rowid VARCHAR2
585 ,p_organization_structure_id NUMBER
586 ,p_business_group_id NUMBER
587 ,p_pa_installed VARCHAR2) IS
588 BEGIN
589 -- do pre-delete checks
590 per_org_structures_pkg.delete_check(
591 p_organization_structure_id =>p_organization_structure_id
592 ,p_business_group_id =>p_business_group_id
593 ,p_pa_installed => p_pa_installed
594 );
595 --
596 DELETE FROM PER_ORGANIZATION_STRUCTURES
597 WHERE rowid = X_Rowid;
598 --
599 if (SQL%NOTFOUND) then
600 RAISE NO_DATA_FOUND;
601 end if;
602 END Delete_Row;
603 ------------------------------------------------------------------------------
604 function postform(p_business_group_id NUMBER
605 ,p_org_structure_version_id IN NUMBER) return boolean is
606 --
607 -- local variables
608 --
609 l_dummy VARCHAR2(1);
610 begin
611 --
612 -- Bug 557463. Added extra clause to restrict the search to versions of
613 -- the hierarchy which is referenced on the security profile. Clause is
614 --
615 -- sp.organization_structure_id = osv.organization_structure_id
616 --
617 -- RMF 29-Sep-97.
618 --
619 -- CBS bug #1301741
620 -- Restrict the query further to the specific org_structure_version that has
621 -- been displayed by the calling form - to prevent the message showing
622 -- everytime DOH form is used in a business group that has a SP
623 -- where the top org has been removed and instead show message only where
624 -- the affected org_structure_version has actually been edited/queried back.
625 --
626 select null
627 into l_dummy
628 from sys.dual
629 where exists( select 1
630 from per_security_profiles sp
631 where sp.organization_id is not null
632 and ( sp.business_group_id + 0 = p_business_group_id
633 or ( sp.business_group_id is null
634 and p_business_group_id is null))
635 and exists (select null
636 from per_org_structure_versions osv
637 where ( osv.business_group_id = p_business_group_id
638 or ( osv.business_group_id is null
639 and p_business_group_id is null))
640 and sp.organization_structure_id =
641 osv.organization_structure_id
642 and osv.org_structure_version_id = p_org_structure_version_id /* 1301741 */
643 and not exists
644 (
645 select null
646 from per_org_structure_elements ose
647 where ose.org_structure_version_id =
648 osv.org_structure_version_id
649 and (sp.organization_id =
650 ose.organization_id_child
651 or sp.organization_id =
652 ose.organization_id_parent)
653 )
654 )
655 );
656 --
657 return TRUE;
658 --
659 exception
660 when no_data_found then
661 return false;
662 end;
663 ------------------------------------------------------------------------------
664 END PER_ORG_STRUCTURES_PKG;