1 PACKAGE BODY PER_ORG_STRUCTURE_ELEMENTS_PKG as
2 /* $Header: peose01t.pkb 120.0.12000000.2 2007/03/07 10:58:33 asgugupt 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 --
396 ,p_warning_raised IN OUT NOCOPY VARCHAR2) is
393 PROCEDURE check_org_active(p_org_id_parent NUMBER
394 ,p_date_from DATE
395 ,p_end_of_time DATE
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 PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
522 p_Org_Structure_Element_Id IN OUT NOCOPY NUMBER,
523 p_Business_Group_Id NUMBER,
524 p_Organization_Id_Parent NUMBER,
525 p_Org_Structure_Version_Id NUMBER,
526 p_Organization_Id_Child NUMBER,
530 p_end_of_time DATE,
527 p_date_from DATE,
528 p_security_profile_id NUMBER,
529 p_view_all_orgs VARCHAR2,
531 p_pos_control_enabled_flag VARCHAR2,
532 p_warning_raised IN OUT NOCOPY VARCHAR2
533 ) IS
534 --
535 CURSOR C IS SELECT rowid FROM PER_ORG_STRUCTURE_ELEMENTS
536 WHERE org_structure_element_id = p_Org_Structure_Element_Id;
537 CURSOR C2 IS SELECT per_org_structure_elements_s.nextval FROM sys.dual;
538 --
539 cursor get_details is select rowid,org_structure_element_id,
540 business_group_id,organization_id_parent,org_structure_version_id,
541 organization_id_child, position_control_enabled_flag
542 from per_org_structure_elements
543 where organization_id_child = p_Organization_Id_Child
544 and org_structure_version_id = p_org_structure_version_id;
545 --
546 Recinfo get_details%ROWTYPE;
547 --
548 BEGIN
549 --
550 -- Pre -insert validation
551 --
552 --
553 check_position_flag (
554 p_org_structure_version_id => p_org_structure_version_id
555 ,p_pos_control_enabled_flag => p_pos_control_enabled_flag);
556
557 check_org_active(p_Organization_Id_Parent,
558 p_date_from
559 ,p_end_of_time
560 ,p_warning_raised => p_warning_raised);
561 if ((hr_organization_units_pkg.exists_in_hierarchy(p_org_structure_version_id
562 ,p_organization_id_child) = 'Y')) then
563 --
564 -- Yes , then merely update the old structure element
565 --
566 open get_details;
567 fetch get_details into Recinfo;
568 if get_details%found then
569 --
570 -- Lock the row
571 --
572 PER_ORG_STRUCTURE_ELEMENTS_PKG.Lock_Row(p_Rowid => Recinfo.ROWID,
573 p_Org_Structure_Element_Id =>Recinfo.Org_Structure_Element_Id,
574 p_Business_Group_Id =>Recinfo.Business_Group_Id,
575 p_Organization_Id_Parent =>Recinfo.Organization_Id_Parent,
576 p_Org_Structure_Version_Id=>Recinfo.Org_Structure_Version_Id,
577 p_Organization_Id_Child =>Recinfo.Organization_Id_Child,
578 p_pos_control_enabled_flag => Recinfo.position_control_enabled_flag);
579 --
580 -- Update the row
581 --
582 PER_ORG_STRUCTURE_ELEMENTS_PKG.Update_Row(p_Rowid=> Recinfo.ROWID,
583 p_Org_Structure_Element_Id =>Recinfo.Org_Structure_Element_Id,
584 p_Business_Group_Id => Recinfo.Business_Group_Id,
585 p_Organization_Id_Parent =>p_Organization_Id_Parent,
586 p_Org_Structure_Version_Id =>Recinfo.Org_Structure_Version_Id,
587 p_Organization_Id_Child =>Recinfo.Organization_Id_Child,
588 p_pos_control_enabled_flag => Recinfo.position_control_enabled_flag);
589
590 p_Rowid := Recinfo.ROWID;
591 p_Org_Structure_Element_Id := Recinfo.Org_Structure_Element_Id;
592 close get_details;
593 return;
594 end if;
595 end if;
596 --
597 check_duplicate_entry(p_org_structure_version_id
598 ,p_Organization_Id_Child);
599 --
600 if (p_Org_Structure_Element_Id is NULL) then
601 OPEN C2;
602 FETCH C2 INTO p_Org_Structure_Element_Id;
603 CLOSE C2;
604 end if;
605 --
606 INSERT INTO PER_ORG_STRUCTURE_ELEMENTS(
607 org_structure_element_id,
608 business_group_id,
609 organization_id_parent,
610 org_structure_version_id,
611 organization_id_child,
612 position_control_enabled_flag
613 ) VALUES (
614 p_Org_Structure_Element_Id,
615 p_Business_Group_Id,
616 p_Organization_Id_Parent,
617 p_Org_Structure_Version_Id,
618 p_Organization_Id_Child,
619 p_pos_control_enabled_flag
620 );
621 --
622 OPEN C;
623 FETCH C INTO p_Rowid;
624 if (C%NOTFOUND) then
625 CLOSE C;
626 RAISE NO_DATA_FOUND;
627 end if;
628 CLOSE C;
629 --
630 -- Post-insert code
631 -- Maintains org_list security profiles
632 --
633 if p_view_all_orgs = 'N' then
634 per_org_structure_elements_pkg.maintain_org_lists(p_Business_Group_Id
635 ,p_security_profile_id
636 ,p_Organization_Id_Parent
637 );
638 end if;
639 --
640 END Insert_Row;
641 -------------------------------------------------------------------------------
642 PROCEDURE Lock_Row(p_Rowid VARCHAR2,
643 p_Org_Structure_Element_Id NUMBER,
644 p_Business_Group_Id NUMBER,
645 p_Organization_Id_Parent NUMBER,
646 p_Org_Structure_Version_Id NUMBER,
647 p_Organization_Id_Child NUMBER,
648 p_pos_control_enabled_flag VARCHAR2
649 ) IS
650 --
651 CURSOR C IS
652 SELECT *
653 FROM PER_ORG_STRUCTURE_ELEMENTS
654 WHERE rowid = p_Rowid
658 OPEN C;
655 FOR UPDATE of Org_Structure_Element_Id NOWAIT;
656 Recinfo C%ROWTYPE;
657 BEGIN
659 FETCH C INTO Recinfo;
660 if (C%NOTFOUND) then
661 CLOSE C;
662 RAISE NO_DATA_FOUND;
663 end if;
664 CLOSE C;
665 if (
666 ( (Recinfo.org_structure_element_id = p_Org_Structure_Element_Id)
667 OR ( (Recinfo.org_structure_element_id IS NULL)
668 AND (p_Org_Structure_Element_Id IS NULL)))
669 AND ( (Recinfo.business_group_id = p_Business_Group_Id)
670 OR ( (Recinfo.business_group_id IS NULL)
671 AND (p_Business_Group_Id IS NULL)))
672 AND ( (Recinfo.organization_id_parent = p_Organization_Id_Parent)
673 OR ( (Recinfo.organization_id_parent IS NULL)
674 AND (p_Organization_Id_Parent IS NULL)))
675 AND ( (Recinfo.org_structure_version_id = p_Org_Structure_Version_Id)
676 OR ( (Recinfo.org_structure_version_id IS NULL)
677 AND (p_Org_Structure_Version_Id IS NULL)))
678 AND ( (Recinfo.organization_id_child = p_Organization_Id_Child)
679 OR ( (Recinfo.organization_id_child IS NULL)
680 AND (p_Organization_Id_Child IS NULL)))
681 -- AND ( (Recinfo.position_control_enabled_flag = p_pos_control_enabled_flag)
682 -- OR ( (Recinfo.position_control_enabled_flag IS NULL)
683 -- AND (p_pos_control_enabled_flag IS NULL)))
684 ) then
685 return;
686 else
687 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
688 APP_EXCEPTION.RAISE_EXCEPTION;
689 end if;
690 END Lock_Row;
691 -------------------------------------------------------------------------------
692 --bug no 5912009 starts here
693 PROCEDURE Update_Row(p_Rowid VARCHAR2,
694 p_Org_Structure_Element_Id NUMBER,
695 p_Business_Group_Id NUMBER,
696 p_Organization_Id_Parent NUMBER,
697 p_Org_Structure_Version_Id NUMBER,
698 p_Organization_Id_Child NUMBER,
699 p_pos_control_enabled_flag VARCHAR2
700 ) IS
701 BEGIN
702 Update_Row(p_Rowid=>p_Rowid,
703 p_Org_Structure_Element_Id=>p_Org_Structure_Element_Id,
704 p_Business_Group_Id=>p_Business_Group_Id,
705 p_Organization_Id_Parent=>p_Organization_Id_Parent,
706 p_Org_Structure_Version_Id=>p_Org_Structure_Version_Id,
707 p_Organization_Id_Child =>p_Organization_Id_Child,
708 p_pos_control_enabled_flag=>p_pos_control_enabled_flag,
709 p_pa_installed=>'N');
710 END Update_Row;
711 PROCEDURE Update_Row(p_Rowid VARCHAR2,
712 p_Org_Structure_Element_Id NUMBER,
713 p_Business_Group_Id NUMBER,
714 p_Organization_Id_Parent NUMBER,
715 p_Org_Structure_Version_Id NUMBER,
716 p_Organization_Id_Child NUMBER,
717 p_pos_control_enabled_flag VARCHAR2,
718 p_pa_installed VARCHAR2
719 ) IS
720 --bug no 5912009 ends here
721 BEGIN
722 check_position_flag (
723 p_org_structure_version_id => p_org_structure_version_id
724 ,p_pos_control_enabled_flag => p_pos_control_enabled_flag);
725
726 --changes for bug 5912009 starts here
727 if p_pa_installed = 'I' then
728 pa_org.pa_ose_predel_validation(p_org_structure_element_id);
729 end if;
730 --changes for bug 5912009 ends here
731
732 UPDATE PER_ORG_STRUCTURE_ELEMENTS
733 SET
734 org_structure_element_id = p_Org_Structure_Element_Id,
735 business_group_id = p_Business_Group_Id,
736 organization_id_parent = p_Organization_Id_Parent,
737 org_structure_version_id = p_Org_Structure_Version_Id,
738 organization_id_child = p_Organization_Id_Child,
739 position_control_enabled_flag = p_pos_control_enabled_flag
740 WHERE rowid = p_rowid;
741 --
742 if (SQL%NOTFOUND) then
743 RAISE NO_DATA_FOUND;
744 end if;
745 END Update_Row;
746 -------------------------------------------------------------------------------
747 PROCEDURE Delete_Row(p_Rowid VARCHAR2
748 ,p_org_structure_version_id NUMBER
749 ,p_org_structure_element_id NUMBER
750 ,p_organization_id_child NUMBER
751 ,p_organization_id_parent NUMBER
752 ,p_hr_installed VARCHAR2
753 ,p_exists_in_hierarchy IN OUT NOCOPY VARCHAR2
754 ,p_pa_installed VARCHAR2) IS
755 --
756 BEGIN
757 --
758 -- Do pre-delete validation
759 --
760 per_org_structure_elements_pkg.delete_validation(
761 p_org_structure_version_id =>p_org_structure_version_id
762 ,p_org_structure_element_id => p_org_structure_element_id
763 ,p_org_id_child => p_organization_id_child
764 ,p_org_id_parent => p_organization_id_parent
765 ,p_hr_installed => p_hr_installed
766 ,p_pa_installed => p_pa_installed);
767 -- Perform the delete
768 --
769 DELETE FROM PER_ORG_STRUCTURE_ELEMENTS
770 WHERE rowid = p_Rowid;
771 --
772 if (SQL%NOTFOUND) then
773 RAISE NO_DATA_FOUND;
774 end if;
775 --
776 -- Post-delete checking
777 --
778 if per_org_structure_elements_pkg.post_delete_check(
779 p_org_structure_version_id => p_org_structure_version_id
780 ,p_organization_id => p_organization_id_parent) then
781 p_exists_in_hierarchy :='Y';
782 else
783 p_exists_in_hierarchy :='N';
784 end if;
785 END Delete_Row;
786 -------------------------------------------------------------------------------
787 END PER_ORG_STRUCTURE_ELEMENTS_PKG;
[Home] [Help]