[Home] [Help]
PACKAGE BODY: APPS.PER_ORG_STRUCTURE_ELEMENTS_PKG
Source
1 PACKAGE BODY PER_ORG_STRUCTURE_ELEMENTS_PKG as
2 /* $Header: peose01t.pkb 120.1.12010000.2 2009/02/25 08:33:18 sidsaxen ship $ */
3 ------------------------------------------------------------------------------
4 FUNCTION get_subordinates(p_view_all_orgs VARCHAR2
5 ,p_org_id_parent NUMBER
6 ,p_org_structure_version_id NUMBER
7 ,p_security_profile_id NUMBER) return NUMBER is
8 --
9 l_count NUMBER;
10 --
11 begin
12 --
13 -- Return the number of subordinates of a given organization
14 -- in a given hierarchy
15 --
16 -- If security exists then return only those in
17 -- the current security profile
18 --
19 --fix for bug 4389340 starts here
20 select nvl( count(ose.org_structure_element_id), 0)
21 into l_count
22 from per_org_structure_elements ose
23 where ((nvl(p_view_all_orgs,'Y') <> 'Y'
24 and EXISTS
25 (select '1'
26 from hr_organization_units hru
27 where hru.organization_id = ose.organization_id_child
28 ))
29 or nvl(p_view_all_orgs,'Y') = 'Y')
30 connect by
31 prior ose.organization_id_child = ose.organization_id_parent
32 and ose.org_structure_version_id = p_org_structure_version_id
33 start with
34 ose.organization_id_parent = p_org_id_parent
35 and ose.org_structure_version_id = p_org_structure_version_id;
36 -- fix for bug 4389340 ends here.
37 -- no exception necessary as a single row group FUNCTION
38 -- hence no no_data_found or Too_many_rows errors;
39 return l_count;
40 end;
41 -------------------------------------------------------------------------------
42 PROCEDURE maintain_org_lists(p_business_group_id NUMBER
43 ,p_security_profile_id NUMBER
44 ,p_organization_id NUMBER
45 ) is
46 --
47 -- Local variables
48 --
49 l_sec_view_all_orgs_flag VARCHAR2(1);
50 l_sec_org_structure_id NUMBER;
51 l_sec_org_id NUMBER;
52 l_sec_include_top_org_flag VARCHAR2(1);
53 -- Local Functions and Procedures
54 PROCEDURE insert_rows(p_security_profile_id NUMBER
55 ,p_organization_id NUMBER
56 ) is
57 --
58 begin
59 hr_security.add_organization(p_organization_id,
60 p_security_profile_id);
61 -- No rows inserted - raise error to the effect.
62 if SQL%ROWCOUNT <>1 then
63 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
64 hr_utility.set_message_token('PROCEDURE','maintain_org_lists');
65 hr_utility.set_message_token('STEP','2');
66 hr_utility.raise_error;
67 end if;
68 --
69 end insert_rows;
70 --
71 FUNCTION tree_walk(p_sec_org_structure_id NUMBER
72 ,p_business_group_id NUMBER
73 ,p_organization_id NUMBER
74 ,p_sec_org_id NUMBER) return BOOLEAN is
75 --
76 l_temp VARCHAR2(1);
77 --
78 begin
79 --
80 -- 'Walk' down the hierarchy.
81 -- If organization exists in tree,
82 -- Then return true else return false
83 --
84 select null
85 into l_temp
86 from sys.dual
87 where exists (select null
88 from sys.dual
89 where p_organization_id in
90 (select a.organization_id_child
91 from per_org_structure_elements a
92 where ( a.business_group_id + 0 = p_business_group_id
93 or ( a.business_group_id is null
94 and p_business_group_id is null))
95 and a.org_structure_version_id in
96 ( select x.org_structure_version_id
97 from per_org_structure_versions x
98 where x.organization_structure_id =
99 p_sec_org_structure_id)
100 connect by prior
101 a.organization_id_child = a.organization_id_parent
102 and a.org_structure_version_id in
103 ( select x.org_structure_version_id
104 from per_org_structure_versions x
105 where x.organization_structure_id =
106 p_sec_org_structure_id)
107 and ( a.business_group_id + 0 = p_business_group_id
108 or ( a.business_group_id is null
109 and p_business_group_id is null))
110 start with
111 a.organization_id_parent = p_sec_org_id
112 and a.org_structure_version_id in
113 ( select x.org_structure_version_id
114 from per_org_structure_versions x
115 where x.organization_structure_id =
116 p_sec_org_structure_id)
117 and ( a.business_group_id + 0 = p_business_group_id
118 or ( a.business_group_id is null
119 and p_business_group_id is null)))
120 );
121 --
122 return true;
123 --
124 exception
125 when no_data_found then
126 return false;
127 when others then
128 raise;
129 end tree_walk;
130 --
131 begin
132 --
133 -- Maintain the security profile
134 -- Organization list
135 --
136 begin
137 -- Get the security profile information.
138 select x.view_all_organizations_flag
139 , x.organization_structure_id
140 , x.organization_id
141 , x.include_top_organization_flag
142 into l_sec_view_all_orgs_flag
143 , l_sec_org_structure_id
144 , l_sec_org_id
145 , l_sec_include_top_org_flag
146 from per_security_profiles x
147 where ( x.business_group_id + 0 = p_business_group_id
148 or x.business_group_id is null)
149 and x.security_profile_id = p_security_profile_id;
150 --
151 exception
152 when no_data_found then
153 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
154 hr_utility.set_message_token('PROCEDURE','maintain_org_lists');
155 hr_utility.set_message_token('STEP','1');
156 hr_utility.raise_error;
157 when others then raise;
158 end;
159 --
160 if l_sec_view_all_orgs_flag = 'N' then
161 return;
162 elsif l_sec_view_all_orgs_flag = 'Y' then
163 insert_rows(p_security_profile_id
164 ,p_organization_id
165 );
166 elsif l_sec_include_top_org_flag = 'N' then
167 if tree_walk(l_sec_org_structure_id
168 ,p_business_group_id
169 ,p_organization_id
170 ,l_sec_org_id ) then
171 insert_rows(p_security_profile_id
172 ,p_organization_id
173 );
174 end if;
175 elsif l_sec_org_id = p_organization_id then
176 insert_rows(p_security_profile_id
177 ,p_organization_id
178 );
179 elsif tree_walk(l_sec_org_structure_id
180 ,p_business_group_id
181 ,p_organization_id
182 ,l_sec_org_id) then
183 insert_rows(p_security_profile_id
184 ,p_organization_id
185 );
186 end if;
187 end maintain_org_lists;
188 ------------------------------------------------------------------------------
189 PROCEDURE delete_validation(p_org_structure_version_id NUMBER
190 ,p_org_structure_element_id NUMBER
191 ,p_org_id_child NUMBER
192 ,p_org_id_parent NUMBER
193 ,p_hr_installed VARCHAR2
194 ,p_pa_installed VARCHAR2) is
195 --
196 l_temp VARCHAR2(1);
197 --
198 begin
199 --
200 -- Pre-delete checks for subordinate
201 -- organizations in the hierarchy.
202 --
203 begin
204 select null
205 into l_temp
206 from sys.dual
207 where exists (select null
208 from per_org_structure_elements ose
209 where ose.org_structure_version_id =
210 p_org_structure_version_id
211 and ose.organization_id_parent =
212 p_org_id_child);
213 --
214 hr_utility.set_message('801','HR_6190_ORG_CHILDREN_EXIST');
215 hr_utility.raise_error;
216 --
217 exception
218 when no_data_found then
219 null;
220 when others then
221 raise;
222 end;
223 --
224 --
225 -- If the child org in the element = top org in an
226 -- security_profile and hierarchies are the same
227 -- then cannot delete it.
228 -- similarly if the parent_org in the element = top org in a
229 --security_profile and hierarchies are the same
230 -- then you cannot delete it if it is the parent of no other
231 -- org_structure_element for this version.
232 --
233 if p_hr_installed = 'I' then
234 begin
235 select null
236 into l_temp
237 from sys.dual
238 where exists( select null
239 from per_security_profiles psp
240 where psp.include_top_organization_flag = 'Y'
241 and psp.organization_structure_id =
242 (select osv.organization_structure_id
243 from per_org_structure_versions osv
244 where osv.org_structure_version_id =
245 p_org_structure_version_id)
246 and ((psp.organization_id = p_org_id_child)
247 or(psp.organization_id = p_org_id_parent
248 and not exists (
249 select null
250 from per_org_structure_elements ose
251 where ose.org_structure_version_id =
252 p_org_structure_version_id
253 and ose.organization_id_child =
254 p_org_id_parent
255 )
256 and not exists (
257 select null
258 from per_org_structure_elements ose
259 where ose.org_structure_version_id =
260 p_org_structure_version_id
261 and ose.org_structure_element_id <>
262 p_org_structure_element_id
263 and ose.organization_id_parent =
264 p_org_id_parent
265 )
266 )
267 ) );
268 --
269 hr_utility.set_message('801','HR_6753_ORG_HIER_SP_DEL');
270 hr_utility.raise_error;
271 --
272 exception
273 when no_data_found then
274 null;
275 when others then
276 raise;
277 end;
278 end if;
279 --
280 -- Run the validation PROCEDURE writtrn by PA development group.
281 --
282 -- Bug 516069: Changed 'Y' to 'I' to pick up correct value.
283 --
284 if p_pa_installed = 'I' then
285 pa_org.pa_ose_predel_validation(p_org_structure_element_id);
286 end if;
287 end;
288 -------------------------------------------------------------------------------
289 PROCEDURE perwsdor_delete_check(p_org_structure_version_id NUMBER
290 ,p_org_structure_element_id NUMBER
291 ,p_org_id_child NUMBER
292 ,p_org_id_parent NUMBER
293 ,p_business_group_id NUMBER
294 ,p_hr_installed VARCHAR2
295 ,p_pa_installed VARCHAR2) is
296 cursor c1 is select 'Y'
297 from per_org_structure_elements pos
298 where pos.org_structure_version_id = p_org_structure_version_id
299 and (not exists
300 (select null
301 from per_org_structure_elements pos2
302 where pos2.org_structure_version_id
303 = p_org_structure_version_id
304 and pos2.organization_id_child =
305 p_org_id_parent
306 )
307 and 1 =(select count(pos3.ORG_STRUCTURE_VERSION_ID)
308 from per_org_structure_elements pos3
309 where pos3.org_structure_version_id
310 = p_org_structure_version_id
311 and pos3.organization_id_parent =
312 p_org_id_parent
313 )
314 OR (not exists
315 (select null
316 from per_org_structure_elements pos3
317 where pos3.org_structure_version_id =
318 p_org_structure_version_id
319 and pos3.organization_id_parent = p_org_id_child)
320 )
321 );
322 --
323 cursor c2 is
324 select 'Y'
325 from per_security_profiles psp
326 , per_org_structure_versions posv
327 where ( psp.business_group_id =p_business_group_id
328 or ( psp.business_group_id is null
329 and p_business_group_id is null))
330 and psp.organization_id = p_org_id_child
331 and ( psp.business_group_id = posv.business_group_id
332 or ( psp.business_group_id is null
333 and posv.business_group_id is null))
334 and psp.organization_structure_id = posv.organization_structure_id
335 and posv.org_structure_version_id = p_org_structure_version_id;
336 --
337 l_dummy varchar2(1);
338 begin
339 open c1;
340 fetch c1 into l_dummy;
341 if c1%FOUND then
342 close c1;
343 hr_utility.set_message('801','HR_6740_ORG_HIER_CANT_DELETE');
344 hr_utility.raise_error;
345 end if;
346 close c1;
347 if p_hr_installed IN ('I','S') then
348 open c2;
349 fetch c2 into l_dummy;
350 if c2%FOUND then
351 close c2;
352 hr_utility.set_message('801','PAY_7694_PER_NO_DEL_STRUCTURE');
353 hr_utility.raise_error;
354 end if;
355 end if;
356 --
357 -- Bug 516069: Changed <> 'Y' to = 'I' to pick up correct value.
358 --
359 if p_pa_installed = 'I' then
360 pa_org.pa_ose_predel_validation(p_org_structure_element_id);
361 end if;
362 end;
363 -------------------------------------------------------------------------------
364 PROCEDURE check_duplicate_entry (p_org_structure_version_id NUMBER
365 ,p_org_structure_element_id NUMBER) is
366 --
367 l_temp VARCHAR2(1);
368 --
369 begin
370 --
371 -- Duplicate subordinate name? (Child can only have one parent)
372 --
373 select null
374 into l_temp
375 from sys.dual
376 where exists( select null
377 from per_org_structure_elements ose
378 where ose.org_structure_version_id =
379 p_org_structure_version_id
380 and ose.organization_id_child =
381 p_org_structure_element_id);
382 --
383 hr_utility.set_message('801','HR_6186_ORG_DUP_CHILD');
384 hr_utility.raise_error;
385 --
386 exception
387 when no_data_found then
388 null;
389 when others then
390 raise;
391 end check_duplicate_entry;
392 --
393 PROCEDURE check_org_active(p_org_id_parent NUMBER
394 ,p_date_from DATE
395 ,p_end_of_time DATE
396 ,p_warning_raised IN OUT NOCOPY VARCHAR2) is
397 --
398 l_temp VARCHAR2(1);
399 --
400 begin
401 --
402 -- Is the Organization structure element effective as of start date
403 --
404 select null
405 into l_temp
406 from sys.dual
407 where exists(select null
408 from hr_all_organization_units org
409 where org.organization_id = p_org_id_parent
410 and p_date_from between
411 org.date_from and nvl(org.date_to, p_end_of_time)
412 );
413 --
414 --
415 p_warning_raised :='N';
416 exception
417 when no_data_found then
418 hr_utility.set_message('801','HR_ORG_INACT_ORG');
419 p_warning_raised:='Y';
420 when others then
421 raise;
422 end check_org_active;
423 -------------------------------------------------------------------------------
424 PROCEDURE check_position_flag (
425 p_org_structure_version_id NUMBER
426 ,p_pos_control_enabled_flag VARCHAR2) is
427
428 cursor c1 is
429 select str.position_control_structure_flg
430 from per_organization_structures str,
431 per_org_structure_versions osv
432 where osv.org_structure_version_id = p_org_structure_version_id
433 and osv.organization_structure_id = str.organization_structure_id;
434
435 l_result varchar2(10);
436
437 begin
438 --
439 if p_pos_control_enabled_flag = 'Y' then
440 --
441 open c1;
442 fetch c1 into l_result;
443
444 if c1%found then
445 --
446 if l_result <> 'Y' then
447 --
448 close c1;
449 hr_utility.set_message('800','PER_50055_NON_POS_CTRL_STRUCT');
450 hr_utility.raise_error;
451 --
452 end if;
453 --
454 end if;
455
456 close c1;
457 --
458 end if;
459 --
460 end check_position_flag;
461 ------------------------------------------------------------------------------
462 FUNCTION post_delete_check(p_org_structure_version_id NUMBER
463 ,p_organization_id NUMBER) return BOOLEAN is
464 --
465 l_temp VARCHAR2(1);
466 --
467 begin
468 --
469 -- Is the currently displayed organization
470 -- Still in the hierarchy?
471 --
472 select null
473 into l_temp
474 from sys.dual
475 where exists( select null
476 from per_org_structure_elements ose
477 where ose.org_structure_version_id =
478 p_org_structure_version_id
479 and (ose.organization_id_parent = p_organization_id
480 or ose.organization_id_child = p_organization_id)
481 );
482 --
483 return true;
484 --
485 exception
486 when no_data_found then
487 return false;
488 when others then
489 null;
490 end post_delete_check;
491 -------------------------------------------------------------------------------
492 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
493 p_Org_Structure_Element_Id IN OUT NOCOPY NUMBER,
494 p_Business_Group_Id NUMBER,
495 p_Organization_Id_Parent NUMBER,
496 p_Org_Structure_Version_Id NUMBER,
497 p_Organization_Id_Child NUMBER,
498 p_date_from DATE,
499 p_security_profile_id NUMBER,
500 p_view_all_orgs VARCHAR2,
501 p_end_of_time DATE,
502 p_pos_control_enabled_flag VARCHAR2
503 ) IS
504 --
505 l_warning_raised VARCHAR2(1);
506 begin
507 Insert_Row(p_Rowid => p_Rowid,
508 p_Org_Structure_Element_Id => p_Org_Structure_Element_Id,
509 p_Business_Group_Id => p_Business_Group_Id,
510 p_Organization_Id_Parent => p_Organization_Id_Parent,
511 p_Org_Structure_Version_Id => p_Org_Structure_Version_Id,
512 p_Organization_Id_Child => p_Organization_Id_Child,
513 p_date_from => p_date_from,
514 p_security_profile_id => p_security_profile_id,
515 p_view_all_orgs => p_view_all_orgs,
516 p_end_of_time => p_end_of_time,
517 p_pos_control_enabled_flag => p_pos_control_enabled_flag,
518 p_warning_raised => l_warning_raised);
519 end;
520 ------------------------------------------------------------------------------
521
522 -- start changes for bug 8200692
523 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
524 p_Org_Structure_Element_Id IN OUT NOCOPY NUMBER,
525 p_Business_Group_Id NUMBER,
526 p_Organization_Id_Parent NUMBER,
527 p_Org_Structure_Version_Id NUMBER,
528 p_Organization_Id_Child NUMBER,
529 p_date_from DATE,
530 p_security_profile_id NUMBER,
531 p_view_all_orgs VARCHAR2,
532 p_end_of_time DATE,
533 p_pos_control_enabled_flag VARCHAR2,
534 p_warning_raised IN OUT NOCOPY VARCHAR2
535 ) IS
536 BEGIN
537 Insert_Row(p_Rowid => p_Rowid,
538 p_Org_Structure_Element_Id => p_Org_Structure_Element_Id,
539 p_Business_Group_Id => p_Business_Group_Id,
540 p_Organization_Id_Parent => p_Organization_Id_Parent,
541 p_Org_Structure_Version_Id => p_Org_Structure_Version_Id,
542 p_Organization_Id_Child => p_Organization_Id_Child,
543 p_date_from => p_date_from,
544 p_security_profile_id => p_security_profile_id,
545 p_view_all_orgs => p_view_all_orgs,
546 p_end_of_time => p_end_of_time,
547 p_pos_control_enabled_flag => p_pos_control_enabled_flag,
548 p_warning_raised => p_warning_raised,
549 p_pa_installed => 'N');
550 END;
551 -- end changes for bug 8200692
552
553 ------------------------------------------------------------------------------
554 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
555 p_Org_Structure_Element_Id IN OUT NOCOPY NUMBER,
556 p_Business_Group_Id NUMBER,
557 p_Organization_Id_Parent NUMBER,
558 p_Org_Structure_Version_Id NUMBER,
559 p_Organization_Id_Child NUMBER,
560 p_date_from DATE,
561 p_security_profile_id NUMBER,
562 p_view_all_orgs VARCHAR2,
563 p_end_of_time DATE,
564 p_pos_control_enabled_flag VARCHAR2,
565 p_warning_raised IN OUT NOCOPY VARCHAR2,
566 p_pa_installed VARCHAR2 --added for bug 8200692
567 ) IS
568 --
569 CURSOR C IS SELECT rowid FROM PER_ORG_STRUCTURE_ELEMENTS
570 WHERE org_structure_element_id = p_Org_Structure_Element_Id;
571 CURSOR C2 IS SELECT per_org_structure_elements_s.nextval FROM sys.dual;
572 --
573 cursor get_details is select rowid,org_structure_element_id,
574 business_group_id,organization_id_parent,org_structure_version_id,
575 organization_id_child, position_control_enabled_flag
576 from per_org_structure_elements
577 where organization_id_child = p_Organization_Id_Child
578 and org_structure_version_id = p_org_structure_version_id;
579 --
580 Recinfo get_details%ROWTYPE;
581 --
582 BEGIN
583 --
584 -- Pre -insert validation
585 --
586 --
587 check_position_flag (
588 p_org_structure_version_id => p_org_structure_version_id
589 ,p_pos_control_enabled_flag => p_pos_control_enabled_flag);
590
591 check_org_active(p_Organization_Id_Parent,
592 p_date_from
593 ,p_end_of_time
594 ,p_warning_raised => p_warning_raised);
595 if ((hr_organization_units_pkg.exists_in_hierarchy(p_org_structure_version_id
596 ,p_organization_id_child) = 'Y')) then
597 --
598 -- Yes , then merely update the old structure element
599 --
600 open get_details;
601 fetch get_details into Recinfo;
602 if get_details%found then
603 --
604 -- Lock the row
605 --
606 PER_ORG_STRUCTURE_ELEMENTS_PKG.Lock_Row(p_Rowid => Recinfo.ROWID,
607 p_Org_Structure_Element_Id =>Recinfo.Org_Structure_Element_Id,
608 p_Business_Group_Id =>Recinfo.Business_Group_Id,
609 p_Organization_Id_Parent =>Recinfo.Organization_Id_Parent,
610 p_Org_Structure_Version_Id=>Recinfo.Org_Structure_Version_Id,
611 p_Organization_Id_Child =>Recinfo.Organization_Id_Child,
612 p_pos_control_enabled_flag => Recinfo.position_control_enabled_flag);
613 --
614 -- Update the row
615 --
616 PER_ORG_STRUCTURE_ELEMENTS_PKG.Update_Row(p_Rowid=> Recinfo.ROWID,
617 p_Org_Structure_Element_Id =>Recinfo.Org_Structure_Element_Id,
618 p_Business_Group_Id => Recinfo.Business_Group_Id,
619 p_Organization_Id_Parent =>p_Organization_Id_Parent,
620 p_Org_Structure_Version_Id =>Recinfo.Org_Structure_Version_Id,
621 p_Organization_Id_Child =>Recinfo.Organization_Id_Child,
622 p_pos_control_enabled_flag => Recinfo.position_control_enabled_flag,
623 p_pa_installed => p_pa_installed); --added for bug 8200692
624
625 p_Rowid := Recinfo.ROWID;
626 p_Org_Structure_Element_Id := Recinfo.Org_Structure_Element_Id;
627 close get_details;
628 return;
629 end if;
630 end if;
631 --
632 check_duplicate_entry(p_org_structure_version_id
633 ,p_Organization_Id_Child);
634 --
635 if (p_Org_Structure_Element_Id is NULL) then
636 OPEN C2;
637 FETCH C2 INTO p_Org_Structure_Element_Id;
638 CLOSE C2;
639 end if;
640 --
641 INSERT INTO PER_ORG_STRUCTURE_ELEMENTS(
642 org_structure_element_id,
643 business_group_id,
644 organization_id_parent,
645 org_structure_version_id,
646 organization_id_child,
647 position_control_enabled_flag
648 ) VALUES (
649 p_Org_Structure_Element_Id,
650 p_Business_Group_Id,
651 p_Organization_Id_Parent,
652 p_Org_Structure_Version_Id,
653 p_Organization_Id_Child,
654 p_pos_control_enabled_flag
655 );
656 --
657 OPEN C;
658 FETCH C INTO p_Rowid;
659 if (C%NOTFOUND) then
660 CLOSE C;
661 RAISE NO_DATA_FOUND;
662 end if;
663 CLOSE C;
664 --
665 -- Post-insert code
666 -- Maintains org_list security profiles
667 --
668 if p_view_all_orgs = 'N' then
669 per_org_structure_elements_pkg.maintain_org_lists(p_Business_Group_Id
670 ,p_security_profile_id
671 ,p_Organization_Id_Parent
672 );
673 end if;
674 --
675 END Insert_Row;
676 -------------------------------------------------------------------------------
677 PROCEDURE Lock_Row(p_Rowid VARCHAR2,
678 p_Org_Structure_Element_Id NUMBER,
679 p_Business_Group_Id NUMBER,
680 p_Organization_Id_Parent NUMBER,
681 p_Org_Structure_Version_Id NUMBER,
682 p_Organization_Id_Child NUMBER,
683 p_pos_control_enabled_flag VARCHAR2
684 ) IS
685 --
686 CURSOR C IS
687 SELECT *
688 FROM PER_ORG_STRUCTURE_ELEMENTS
689 WHERE rowid = p_Rowid
690 FOR UPDATE of Org_Structure_Element_Id NOWAIT;
691 Recinfo C%ROWTYPE;
692 BEGIN
693 OPEN C;
694 FETCH C INTO Recinfo;
695 if (C%NOTFOUND) then
696 CLOSE C;
697 RAISE NO_DATA_FOUND;
698 end if;
699 CLOSE C;
700 if (
701 ( (Recinfo.org_structure_element_id = p_Org_Structure_Element_Id)
702 OR ( (Recinfo.org_structure_element_id IS NULL)
703 AND (p_Org_Structure_Element_Id IS NULL)))
704 AND ( (Recinfo.business_group_id = p_Business_Group_Id)
705 OR ( (Recinfo.business_group_id IS NULL)
706 AND (p_Business_Group_Id IS NULL)))
707 AND ( (Recinfo.organization_id_parent = p_Organization_Id_Parent)
708 OR ( (Recinfo.organization_id_parent IS NULL)
709 AND (p_Organization_Id_Parent IS NULL)))
710 AND ( (Recinfo.org_structure_version_id = p_Org_Structure_Version_Id)
711 OR ( (Recinfo.org_structure_version_id IS NULL)
712 AND (p_Org_Structure_Version_Id IS NULL)))
713 AND ( (Recinfo.organization_id_child = p_Organization_Id_Child)
714 OR ( (Recinfo.organization_id_child IS NULL)
715 AND (p_Organization_Id_Child IS NULL)))
716 -- AND ( (Recinfo.position_control_enabled_flag = p_pos_control_enabled_flag)
717 -- OR ( (Recinfo.position_control_enabled_flag IS NULL)
718 -- AND (p_pos_control_enabled_flag IS NULL)))
719 ) then
720 return;
721 else
722 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
723 APP_EXCEPTION.RAISE_EXCEPTION;
724 end if;
725 END Lock_Row;
726 -------------------------------------------------------------------------------
727 --bug no 5912009 starts here
728 PROCEDURE Update_Row(p_Rowid VARCHAR2,
729 p_Org_Structure_Element_Id NUMBER,
730 p_Business_Group_Id NUMBER,
731 p_Organization_Id_Parent NUMBER,
732 p_Org_Structure_Version_Id NUMBER,
733 p_Organization_Id_Child NUMBER,
734 p_pos_control_enabled_flag VARCHAR2
735 ) IS
736 BEGIN
737 Update_Row(p_Rowid=>p_Rowid,
738 p_Org_Structure_Element_Id=>p_Org_Structure_Element_Id,
739 p_Business_Group_Id=>p_Business_Group_Id,
740 p_Organization_Id_Parent=>p_Organization_Id_Parent,
741 p_Org_Structure_Version_Id=>p_Org_Structure_Version_Id,
742 p_Organization_Id_Child =>p_Organization_Id_Child,
743 p_pos_control_enabled_flag=>p_pos_control_enabled_flag,
744 p_pa_installed=>'N');
745 END Update_Row;
746 PROCEDURE Update_Row(p_Rowid VARCHAR2,
747 p_Org_Structure_Element_Id NUMBER,
748 p_Business_Group_Id NUMBER,
749 p_Organization_Id_Parent NUMBER,
750 p_Org_Structure_Version_Id NUMBER,
751 p_Organization_Id_Child NUMBER,
752 p_pos_control_enabled_flag VARCHAR2,
753 p_pa_installed VARCHAR2
754 ) IS
755 --bug no 5912009 ends here
756 BEGIN
757 check_position_flag (
758 p_org_structure_version_id => p_org_structure_version_id
759 ,p_pos_control_enabled_flag => p_pos_control_enabled_flag);
760
761 --changes for bug 5912009 starts here
762 if p_pa_installed = 'I' then
763 pa_org.pa_ose_predel_validation(p_org_structure_element_id);
764 end if;
765 --changes for bug 5912009 ends here
766
767 UPDATE PER_ORG_STRUCTURE_ELEMENTS
768 SET
769 org_structure_element_id = p_Org_Structure_Element_Id,
770 business_group_id = p_Business_Group_Id,
771 organization_id_parent = p_Organization_Id_Parent,
772 org_structure_version_id = p_Org_Structure_Version_Id,
773 organization_id_child = p_Organization_Id_Child,
774 position_control_enabled_flag = p_pos_control_enabled_flag
775 WHERE rowid = p_rowid;
776 --
777 if (SQL%NOTFOUND) then
778 RAISE NO_DATA_FOUND;
779 end if;
780 END Update_Row;
781 -------------------------------------------------------------------------------
782 PROCEDURE Delete_Row(p_Rowid VARCHAR2
783 ,p_org_structure_version_id NUMBER
784 ,p_org_structure_element_id NUMBER
785 ,p_organization_id_child NUMBER
786 ,p_organization_id_parent NUMBER
787 ,p_hr_installed VARCHAR2
788 ,p_exists_in_hierarchy IN OUT NOCOPY VARCHAR2
789 ,p_pa_installed VARCHAR2) IS
790 --
791 BEGIN
792 --
793 -- Do pre-delete validation
794 --
795 per_org_structure_elements_pkg.delete_validation(
796 p_org_structure_version_id =>p_org_structure_version_id
797 ,p_org_structure_element_id => p_org_structure_element_id
798 ,p_org_id_child => p_organization_id_child
799 ,p_org_id_parent => p_organization_id_parent
800 ,p_hr_installed => p_hr_installed
801 ,p_pa_installed => p_pa_installed);
802 -- Perform the delete
803 --
804 DELETE FROM PER_ORG_STRUCTURE_ELEMENTS
805 WHERE rowid = p_Rowid;
806 --
807 if (SQL%NOTFOUND) then
808 RAISE NO_DATA_FOUND;
809 end if;
810 --
811 -- Post-delete checking
812 --
813 if per_org_structure_elements_pkg.post_delete_check(
814 p_org_structure_version_id => p_org_structure_version_id
815 ,p_organization_id => p_organization_id_parent) then
816 p_exists_in_hierarchy :='Y';
817 else
818 p_exists_in_hierarchy :='N';
819 end if;
820 END Delete_Row;
821 -------------------------------------------------------------------------------
822 END PER_ORG_STRUCTURE_ELEMENTS_PKG;