1 PACKAGE BODY hr_org_pre_delete AS
2 /* $Header: pedelorg.pkb 115.2 99/10/12 23:43:14 porting shi $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hr_org_pre_delete (BODY)
24
25 Description : This package declares procedures required to test for referential
26 integrity errors which could potentially be caused by deleting
27 an organization which relationship rows with other tables.
28 (Although ORACLE7 does this automatically the message isn't
29 vey user friendly).
30
31 Change List
32 -----------
33
34 Version Date Author ER/CR No. Description of Change
35 -------+---------+----------+---------+--------------------------
36 70.0 31-MAR-93 TMATHERS Date Created.
37 70.1 01-APR-93 TMATHERS MOved org_predel_check to peorganz.
38 70.2 01-APR-93 TMATHERS Corrected error made by previous change
39 70.3 05-APR-93 TMATHERS Change set location message to be
40 hr_org_predel_check rather than
41 hr_organization.
42 70.4 22-APR-93 TMATHERS Added hr_strong_bg_chk.
43 70.5 21-Mar-95 TMATHERS Added extra checks to
44 hr_org_predel_checks for WWBUG #
45 267897.
46 70.10 19-May-97 MBOCUTT 417613 Removed ref. int. check for
47 per_organization_list table. Rows
48 for the org being delete are now
49 automatically removed on delete.
50 115.1 01-Oct-99 SCNair Date track position related changes
51 */
52 --------------------- BEGIN: hr_org_predel_check ------------------------------
53 procedure hr_org_predel_check(p_organization_id INTEGER
54 ,p_business_group_id INTEGER) is
55 /*
56 NAME
57 hr_org_predel_check
58 DESCRIPTION
59 Battery of tests to see if an organization may be deleted.
60 PARAMETERS
61 p_organization_id : Organization Id of Organization to be deleted.
62 p_business_group_id : Business Group id of rganization to be deleted.
63 */
64 --
65 -- Storage Variable.
66 --
67 l_test_func varchar2(60);
68 --
69 begin
70 -- If the organization id equals the business group id then
71 -- it is a business group and so do all relavant checks for Business group.
72 if p_organization_id = p_business_group_id then
73 begin
74 begin
75 -- Do Any rows Exist in PER_PEOPLE_F.
76 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
77 ,1);
78 select '1'
79 into l_test_func
80 from sys.dual
81 where exists ( select 1
82 from PER_PEOPLE_F x
83 where x.business_group_id = p_business_group_id);
84 --
85 if SQL%ROWCOUNT >0 THEN
86 hr_utility.set_message(801,'HR_6130_ORG_PEOPLE_EXIST');
87 hr_utility.raise_error;
88 end if;
89 exception
90 when NO_DATA_FOUND THEN
91 null;
92 end;
93 --
94 begin
95 -- Do Any rows Exist in HR_ORGANIZATION_UNITS.
96 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
97 ,2);
98 select '1'
99 into l_test_func
100 from sys.dual
101 where exists ( select 1
102 from HR_ORGANIZATION_UNITS x
103 where x.business_group_id = p_business_group_id
104 and x.organization_id <> p_business_group_id);
105 --
106 if SQL%ROWCOUNT >0 THEN
107 hr_utility.set_message(801,'HR_6571_ORG_ORG_EXIST');
108 hr_utility.raise_error;
109 end if;
110 exception
111 when NO_DATA_FOUND THEN
112 null;
113 end;
114 --
115 begin
116 -- Do Any rows Exist in PER_JOBS.
117 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
118 ,3);
119 select '1'
120 into l_test_func
121 from sys.dual
122 where exists ( select 1
123 from PER_JOBS x
124 where x.business_group_id = p_business_group_id);
125 --
126 if SQL%ROWCOUNT >0 THEN
127 hr_utility.set_message(801,'HR_6131_ORG_JOBS_EXIST');
128 hr_utility.raise_error;
129 end if;
130 exception
131 when NO_DATA_FOUND THEN
132 null;
133 end;
134 --
135 begin
136 --
137 -- Changed 02-Oct-99 SCNair (per_positions to hr_all_positions_f) date tracked pos. req.
138 --
139 -- Do Any rows Exist in HR_ALL_POSITIONS_F.
140 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
141 ,4);
142 select '1'
143 into l_test_func
144 from sys.dual
145 where exists ( select 1
146 from HR_ALL_POSITIONS_F x
147 where x.business_group_id = p_business_group_id);
148 --
149 if SQL%ROWCOUNT >0 THEN
150 hr_utility.set_message(801,'HR_6557_ORG_POSITIONS_EXIST');
151 hr_utility.raise_error;
152 end if;
153 exception
154 when NO_DATA_FOUND THEN
155 null;
156 end;
157 --
158 begin
159 -- Do Any rows Exist in PER_BUDGET_VALUES.
160 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
161 ,5);
162 select '1'
163 into l_test_func
164 from sys.dual
165 where exists ( select 1
166 from PER_BUDGET_VALUES x
167 where x.business_group_id = p_business_group_id);
168 --
169 if SQL%ROWCOUNT >0 THEN
170 hr_utility.set_message(801,'HR_6558_ORG_BUDGET_VAL_EXIST');
171 hr_utility.raise_error;
172 end if;
173 exception
174 when NO_DATA_FOUND THEN
175 null;
176 end;
177 --
178 begin
179 -- Do Any rows Exist in PER_RECRUITMENT_ACTIVITIES.
180 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
181 ,6);
182 select '1'
183 into l_test_func
184 from sys.dual
185 where exists ( select 1
186 from PER_RECRUITMENT_ACTIVITIES x
187 where x.business_group_id = p_business_group_id);
188 --
189 if SQL%ROWCOUNT >0 THEN
190 hr_utility.set_message(801,'HR_6568_ORG_RECRUIT_ACTS_EXIST');
191 hr_utility.raise_error;
192 end if;
193 exception
194 when NO_DATA_FOUND THEN
195 null;
196 end;
197 --
198 begin
199 -- Do Any rows Exist in PER_ORG_STRUCTURE_ELEMENTS.
200 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check'
201 ,7);
202 select '1'
203 into l_test_func
204 from sys.dual
205 where exists ( select 1
206 from PER_ORG_STRUCTURE_ELEMENTS x
207 where x.business_group_id = p_business_group_id);
208 --
209 if SQL%ROWCOUNT >0 THEN
210 hr_utility.set_message(801,'HR_6569_ORG_IN_HIERARCHY');
211 hr_utility.raise_error;
212 end if;
213 exception
214 when NO_DATA_FOUND THEN
215 null;
216 end;
217 end;
218 end if;
219 --
220 -- Now do all Organization specific checks.
221 begin
222 --
223 -- Changed 02-Oct-99 SCNair (per_positions to hr_all_positions_f) date tracked position requirement
224 --
225 -- Do Any rows Exist in HR_ALL_POSITIONS_F.
226 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',8);
227 select '1'
228 into l_test_func
229 from sys.dual
230 where exists ( select 1
231 from HR_ALL_POSITIONS_F x
232 where x.organization_id = p_organization_id);
233 --
234 if SQL%ROWCOUNT >0 THEN
235 hr_utility.set_message(801,'HR_6557_ORG_POSITIONS_EXIST');
236 hr_utility.raise_error;
237 end if;
238 exception
239 when NO_DATA_FOUND THEN
240 null;
241 end;
242 --
243 --
244 begin
245 -- Do Any rows Exist in PER_ORG_STRUCTURE_ELEMENTS.
246 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',9);
247 select '1'
248 into l_test_func
249 from sys.dual
250 where exists ( select 1
251 from PER_ORG_STRUCTURE_ELEMENTS x
252 where x.organization_id_child = p_organization_id);
253 --
254 if SQL%ROWCOUNT >0 THEN
255 hr_utility.set_message(801,'HR_6569_ORG_IN_HIERARCHY');
256 hr_utility.raise_error;
257 end if;
258 exception
259 when NO_DATA_FOUND THEN
260 null;
261 end;
262 --
263 begin
264 -- Do Any rows Exist in PER_ORG_STRUCTURE_ELEMENTS.
265 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',10);
266 select '1'
267 into l_test_func
268 from sys.dual
269 where exists ( select 1
270 from PER_ORG_STRUCTURE_ELEMENTS x
271 where x.organization_id_parent = p_organization_id);
272 --
273 if SQL%ROWCOUNT >0 THEN
274 hr_utility.set_message(801,'HR_6569_ORG_IN_HIERARCHY');
275 hr_utility.raise_error;
276 end if;
277 exception
278 when NO_DATA_FOUND THEN
279 null;
280 end;
281 --
282 begin
283 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',11);
284 select '1'
285 into l_test_func
286 from sys.dual where exists ( select 1
287 from per_assignments_f x
288 where x.source_organization_id = p_organization_id);
289 if SQL%ROWCOUNT >0 THEN
290 hr_utility.set_message(801,'HR_7333_ORG_ASSIGNMENTS_EXIST');
291
292 hr_utility.raise_error;
293 end if;
294 exception
295 when NO_DATA_FOUND THEN
296 null;
297 end;
298 --
299 begin
300 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',12);
301 select '1'
302 into l_test_func
303 from sys.dual where exists ( select 1
304 from per_assignments_f x
305 where x.organization_id = p_organization_id);
306 if SQL%ROWCOUNT >0 THEN
307 hr_utility.set_message(801,'HR_7333_ORG_ASSIGNMENTS_EXIST');
308 hr_utility.raise_error;
309 end if;
310 exception
311 when NO_DATA_FOUND THEN
312 null;
313 end;
314 --
315 begin
316 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',13);
317 select '1'
318 into l_test_func
319 from sys.dual where exists ( select 1
320 from per_recruitment_activities x
321 where x.run_by_organization_id = p_organization_id);
322 if SQL%ROWCOUNT >0 THEN
323 hr_utility.set_message(801,'HR_7336_ORG_REC_ACT_EXIST');
324 hr_utility.raise_error;
325 end if;
326 exception
327 when NO_DATA_FOUND THEN
328 null;
329 end;
330 --
331 begin
332 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',14);
333 select '1'
334 into l_test_func
335 from sys.dual where exists ( select 1
336 from per_vacancies x
337 where x.organization_id = p_organization_id);
338 if SQL%ROWCOUNT >0 THEN
339 hr_utility.set_message(801,'HR_7337_ORG_VACANCIES');
340 hr_utility.raise_error;
341 end if;
342 exception
343 when NO_DATA_FOUND THEN
344 null;
345 end;
346 --
347 begin
348 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',15);
349 select '1'
350 into l_test_func
351 from sys.dual where exists ( select 1
352 from per_events x
353 where x.organization_run_by_id = p_organization_id);
354 if SQL%ROWCOUNT >0 THEN
355 hr_utility.set_message(801,'HR_7334_ORG_EVENTS_EXIST');
356 hr_utility.raise_error;
357 end if;
358 exception
359 when NO_DATA_FOUND THEN
360 null;
361 end;
362 --
363 begin
364 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',16);
365 select '1'
366 into l_test_func
367 from sys.dual where exists ( select 1
368 from pay_element_links_f x
369 where x.organization_id = p_organization_id);
370 if SQL%ROWCOUNT >0 THEN
371 hr_utility.set_message(801,'HR_7330_ORG_LINKS_EXIST');
372 hr_utility.raise_error;
373 end if;
374 exception
375 when NO_DATA_FOUND THEN
376 null;
377 end;
378 --
379 begin
380 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',16);
381 select '1'
382 into l_test_func
383 from sys.dual where exists ( select 1
384 from pay_payrolls_f x
385 where x.organization_id = p_organization_id);
386 if SQL%ROWCOUNT >0 THEN
387 hr_utility.set_message(801,'HR_7331_ORG_PAYROLLS_EXIST');
388 hr_utility.raise_error;
389 end if;
390 exception
391 when NO_DATA_FOUND THEN
392 null;
393 end;
394 --
395 begin
396 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',17);
397 select '1'
398 into l_test_func
399 from sys.dual where exists ( select 1
400 from pay_wc_funds x
401 where x.carrier_id = p_organization_id);
402 if SQL%ROWCOUNT >0 THEN
403 hr_utility.set_message(801,'HR_7332_WC_FUNDS_EXIST');
404 hr_utility.raise_error;
405 end if;
406 exception
407 when NO_DATA_FOUND THEN
408 null;
409 end;
410 --
411 begin
412 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',18);
413 select '1'
414 into l_test_func
415 from sys.dual where exists ( select 1
416 from per_budget_elements x
417 where x.organization_id = p_organization_id);
418 if SQL%ROWCOUNT >0 THEN
419 hr_utility.set_message(801,'HR_7335_ORG_BUDGET_ELEMENTS');
420 hr_utility.raise_error;
421 end if;
422 exception
423 when NO_DATA_FOUND THEN
424 null;
425 end;
426 --
427 begin
428 hr_utility.set_location('hr_org_pre_delete.hr_org_predel_check',20);
429 select '1'
430 into l_test_func
431 from sys.dual where exists ( select 1
432 from per_security_profiles x
433 where x.organization_id = p_organization_id);
434 if SQL%ROWCOUNT >0 THEN
435 hr_utility.set_message(801,'HR_7339_ORG_SEC_PROFILE');
436 hr_utility.raise_error;
437 end if;
438 exception
439 when NO_DATA_FOUND THEN
440 null;
441 end;
442 --
443 end hr_org_predel_check;
444 --------------------- END: hr_org_predel_check ---------------------------------
445 --
446 --------------------- BEGIN: hr_strong_bg_chk ------------------------------
447 procedure hr_strong_bg_chk(
448 p_organization_id INTEGER) is
449 /*
450 NAME
451 hr_strong_bg_chk
452 DESCRIPTION
453 Test to see whether an Organization can become a business group.
454 PARAMETERS
455 p_organization_id : Id of Organization to be updated to business group.
456 */
457 -- Storage Variable.
458 l_test_func varchar2(60);
459 --
460 begin
461 --
462
463 begin
464 -- Doing check on PAY_ELEMENT_LINKS_F.
465 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',1);
466 select '1'
467 into l_test_func
468 from sys.dual
469 where exists ( select 1
470 from PAY_ELEMENT_LINKS_F x
471 where x.ORGANIZATION_ID = p_organization_id);
472 --
473 if SQL%ROWCOUNT >0 THEN
474 hr_utility.set_message(801,'HR_6719_BG_ELE_LINK');
475 hr_utility.raise_error;
476 end if;
477 exception
478 when NO_DATA_FOUND THEN
479 null;
480 end;
481 --
482 begin
483 -- Doing check on PAY_PAYROLLS_F.
484 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',2);
485 select '1'
486 into l_test_func
487 from sys.dual
488 where exists ( select 1
489 from PAY_PAYROLLS_F x
490 where x.ORGANIZATION_ID = p_organization_id);
491 --
492 if SQL%ROWCOUNT >0 THEN
493 hr_utility.set_message(801,'HR_6717_BG_PAYROLL_EXIST');
494 hr_utility.raise_error;
495 end if;
496 exception
497 when NO_DATA_FOUND THEN
498 null;
499 end;
500 --
501 begin
502 -- Doing check on PER_ASSIGNMENTS_F.
503 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',3);
504 select '1'
505 into l_test_func
506 from sys.dual
507 where exists ( select 1
508 from PER_ASSIGNMENTS_F x
509 where x.SOURCE_ORGANIZATION_ID = p_organization_id);
510 --
511 if SQL%ROWCOUNT >0 THEN
512 hr_utility.set_message(801,'HR_6718_BG_ASS_EXIST');
513 hr_utility.raise_error;
514 end if;
515 exception
516 when NO_DATA_FOUND THEN
517 null;
518 end;
519 --
520 begin
521 -- Doing check on PER_ASSIGNMENTS_F.
522 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',4);
523 select '1'
524 into l_test_func
525 from sys.dual
526 where exists ( select 1
527 from PER_ASSIGNMENTS_F x
528 where x.ORGANIZATION_ID = p_organization_id);
529 --
530 if SQL%ROWCOUNT >0 THEN
531 hr_utility.set_message(801,'HR_6718_BG_ASS_EXIST');
532 hr_utility.raise_error;
533 end if;
534 exception
535 when NO_DATA_FOUND THEN
536 null;
537 end;
538 --
539 begin
540 -- Doing check on PER_BUDGET_ELEMENTS.
541 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',5);
542 select '1'
543 into l_test_func
544 from sys.dual
545 where exists ( select 1
546 from PER_BUDGET_ELEMENTS x
547 where x.ORGANIZATION_ID = p_organization_id);
548 --
549 if SQL%ROWCOUNT >0 THEN
550 hr_utility.set_message(801,'HR_6720_BG_BUDGET_ELE_EXIST');
551 hr_utility.raise_error;
552 end if;
553 exception
554 when NO_DATA_FOUND THEN
555 null;
556 end;
557 --
558 begin
559 -- Doing check on PER_EVENTS.
560 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',6);
561 select '1'
562 into l_test_func
563 from sys.dual
564 where exists ( select 1
565 from PER_EVENTS x
566 where x.ORGANIZATION_RUN_BY_ID = p_organization_id);
567 --
568 if SQL%ROWCOUNT >0 THEN
569 hr_utility.set_message(801,'HR_6721_BG_EVENTS_EXIST');
570 hr_utility.raise_error;
571 end if;
572 exception
573 when NO_DATA_FOUND THEN
574 null;
575 end;
576 --
577 begin
578 -- Doing check on PER_ORG_STRUCTURE_ELEMENTS.
579 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',7);
580 select '1'
581 into l_test_func
582 from sys.dual
583 where exists ( select 1
584 from PER_ORG_STRUCTURE_ELEMENTS x
585 where x.ORGANIZATION_ID_PARENT = p_organization_id);
586 --
587 if SQL%ROWCOUNT >0 THEN
588 hr_utility.set_message(801,'HR_6722_BG_ORG_HIER');
589 hr_utility.raise_error;
590 end if;
591 exception
592 when NO_DATA_FOUND THEN
593 null;
594 end;
595 --
596 begin
597 -- Doing check on PER_ORG_STRUCTURE_ELEMENTS.
598 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',8);
599 select '1'
600 into l_test_func
601 from sys.dual
602 where exists ( select 1
603 from PER_ORG_STRUCTURE_ELEMENTS x
604 where x.ORGANIZATION_ID_CHILD = p_organization_id);
605 --
606 if SQL%ROWCOUNT >0 THEN
607 hr_utility.set_message(801,'HR_6722_BG_ORG_HIER');
608 hr_utility.raise_error;
609 end if;
610 exception
611 when NO_DATA_FOUND THEN
612 null;
613 end;
614 --
615 begin
616 --
617 -- Changed 02-Oct-99 SCNair (per_positions to hr_all_positions_f) date tracked position requirement
618 --
619 -- Doing check on HR_ALL_POSITIONS_F.
620 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',9);
621 select '1'
622 into l_test_func
623 from sys.dual
624 where exists ( select 1
625 from HR_ALL_POSITIONS_F x
626 where x.ORGANIZATION_ID = p_organization_id);
627 --
628 if SQL%ROWCOUNT >0 THEN
629 hr_utility.set_message(801,'HR_6726_BG_POS_EXIST');
630 hr_utility.raise_error;
631 end if;
632 exception
633 when NO_DATA_FOUND THEN
634 null;
635 end;
636 --
637 begin
638 -- Doing check on PER_RECRUITMENT_ACTIVITIES.
639 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',10);
640 select '1'
641 into l_test_func
642 from sys.dual
643 where exists ( select 1
644 from PER_RECRUITMENT_ACTIVITIES x
645 where x.RUN_BY_ORGANIZATION_ID = p_organization_id);
646 --
647 if SQL%ROWCOUNT >0 THEN
648 hr_utility.set_message(801,'HR_6723_BG_REC_ACT_EXIST');
649 hr_utility.raise_error;
650 end if;
651 exception
652 when NO_DATA_FOUND THEN
653 null;
654 end;
655 --
656 begin
657 -- Doing check on PER_SECURITY_PROFILES.
658 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',11);
659 select '1'
660 into l_test_func
661 from sys.dual
662 where exists ( select 1
663 from PER_SECURITY_PROFILES x
664 where x.ORGANIZATION_ID = p_organization_id);
665 --
666 if SQL%ROWCOUNT >0 THEN
667 hr_utility.set_message(801,'HR_6724_BG_SEC_PROF_EXIST');
668 hr_utility.raise_error;
669 end if;
670 exception
671 when NO_DATA_FOUND THEN
672 null;
673 end;
674 --
675 begin
676 -- Doing check on PER_VACANCIES.
677 hr_utility.set_location('hr_org_pre_delete.hr_strong_bg_chk',12);
678 select '1'
679 into l_test_func
680 from sys.dual
681 where exists ( select 1
682 from PER_VACANCIES x
683 where x.ORGANIZATION_ID = p_organization_id);
684 --
685 if SQL%ROWCOUNT >0 THEN
686 hr_utility.set_message(801,'HR_6725_BG_VAC_EXIST');
687 hr_utility.raise_error;
688 end if;
689 exception
690 when NO_DATA_FOUND THEN
691 null;
692 end;
693 --
694 end hr_strong_bg_chk;
695 --------------------- END: hr_strong_bg_chk -----------------------------------
696 END hr_org_pre_delete;
[Home] [Help]