1 PACKAGE BODY hr_delete AS
2 /* $Header: pedelete.pkb 120.0 2005/05/31 07:34:08 appldev noship $ */
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
24 Name : hr_delete (BODY)
25
26 Description : Contains the definition of general delete procedures
27 as declared in the hr_delete package header
28
29
30 Change List
31 -----------
32
33 Version Date Author ER/CR No. Description of Change
34 -------+---------+----------+---------+--------------------------
35 70.0 17-NOV-92 SZWILLIA Date Created
36 70.1 09-FEB-93 SZWILLIA Corrected ref to
37 PER_SECONDARY_ASS_STATUSES
38 70.2 11-MAR-93 NKHAN added 'exit' to the end
39 70.3 13-APR-93 abraae remove references to defunct "legal
40 company" tables
41 70.5 30-APR-93 M DYER Commented out reference to
42 ssp_maternity_pay_periods.
43 70.6 30-APR-93 M DYER now deletes database items for elements
44 and input values.
45 70.7 01-JUN-93 JTHURING Removed references to
46 ssp_maternity_pay_periods and
47 ssp_periods_of_incapacity
48 70.10 07-JUL-93 SZWILLIA Moved delete of PER_VACANCIES which
49 was in conflict with assignments.
50 Now in delete_per_misc. Also, moved
51 PER_REQUISITIONS.
52 70.10 07-JUL-93 mwcallag Delete all database items for a given
53 business group, procedure
54 'delete_database_items' added. Element
55 type and input value database item
56 deletion procedures removed from
57 'delete_element_direct', now all done
58 by 'delete_database_items'.
59 70.11 13-JUL-93 SZWILLIA B80 Previous changes made in response to
60 bug number 80.
61 80.1 15-DEC-93 JHOBBS G284 Corrected delete of PAY_USER_COLUMN_ ..
62 INSTANCES to use base table and not the
63 view.
64 G325 Made sure the removal of assignment
65 actions orders by descending payroll
66 actions so that multiple assignments
67 are dealt with correctly.
68 80.2 22-DEC-93 DSAXBY G470 Avoid constraint violation problem
69 by removing pre-payment delete and
70 leaving it to rollback assignment
71 actions procedure.
72 Also, fix problem that still exists
73 with the order by that drives the
74 delete of assignment actions (should
75 not order by
76 pay_payroll_actions.action_sequence).
77 80.3 22-FEB-94 JTHURING B402 Remove references to pay_holidays and
78 pay_holiday_parameters
79 80.4 28-APR-94 DSAXBY G655 Need to delete hr_assignment_set rows
80 after payroll actions.
81 70.15 23-NOV-94 RFINE G1725 Suppressed index on business_group_id
82 70.16 29-NOV-94 DSAXBY Altered the way payroll actions and
83 assignment actions are rolled back
84 following change in rollback rules.
85 70.17 16-FEB-95 DSAXBY Delete from pay_monetary_units.
86 70.18 24-FEB-95 NBRISTOW Delete from pay_magnetic_records.
87 70.19 14-JUN-95 NLBARLOW Delete from per_special_info_type_usages
88 70.20 26-JUL-95 Kev Koh Added deletions for per_person_types
89 70.21 23-AUG-95 DSAXBY Improved deletion from element entries
90 and values. Was causing full table
91 scans on both these tables!!
92 70.22 24-AUG-95 Kev Koh Improved deletion from link input
93 values.
94 70.23 24-AUG-95 Kev Koh Added deletions for per_number_
95 generation_controls in delete_misc.
96 70.24 27-SEP-95 akelly Added deletion from hr_organization_
97 information to delete_org_direct.
98 Added deletions from pay_wc_rates and
99 pay_wc_funds to delete_org_detail.
100 Added new procedure delete_location.
101 70.25 15-oct-95 akelly Removed delete_location.
102 70.26 17-OCT-95 nbristow Added delete_bal_load_struct to delete
103 from pay_balance_batch_headers and
104 pay_balance_batch_lines.
105 70.27 31-OCT-95 Kev Koh Enhanced delete_secure_objects with
106 delete cursor
107 70.28 31-OCT-95 Kev Koh Enhanced delete_secure_objects and
108 delete_bal_load_struct with
109 delete cursor
110 70.29 01-NOV-95 dsaxby Added p_preserve_org_information param
111 to the delete_below_bg procedure.
112 70.30 02-NOV-95 nbristow Corrected cursors in
113 delete_bal_load_struct and balance
114 transfers are rolled back.
115 70.31 06-NOV-95 nbristow delete_bal_load_struct now deletes from
116 pay_message_lines rather that doing a
117 full rollback of the batch upload.
118 70.32 17-JAN-96 nbristow Now deleting from per_pay_bases,
119 pay_freq_rule_periods,
120 pay_ele_freq_rules and
121 ben_benefit_contributions_f.
122 70.33 22-JAN-96 mhoyes Added delete statements to
123 delete_assign_detail which delete from
124 per_pay_proposal_components and
125 per_pay_proposals for a business group.
126 70.34 19-MAR-95 dsaxby Now call py_rollback_pkg version of
127 rollback_payroll_action.
128 70.35 28-JUN-96 mhoyes a Added delete statement to
129 delete_bg_misc to delete
130 financials_system param_all for
131 a business group.
132 b Moved the delete statement for
133 PER_PAY_BASES to before the delete
134 statement for PAY_RATES to avoid
135 the referential integrity error
136 when RATE_ID is set for a pay basis.
137 c Moved the delete statement for
138 PER_EVENTS to after the delete
139 statement for PER_PAY_PROPOSALS to
140 avoid the referential integrity error
141 when EVENT_ID is set for a pay proposal.
142 70.36 31-JUL-96 Tmathers a Made delete statment a dynamic sql
143 70.37 28-AUG-96 mhoyes a Created new procedure
144 delete_competence_detail to delete
145 business group data from new
146 competence tables.
147 cursor so it will not fail compilation
148 in a stand alone 10.5 environment.
149 70.38 16-SEP-96 DKerr Performance Tuning
150 a. delete_secure_objects :
151 Re-enabled use of business group index
152 in person_list cursor
153 b. delete_bal_load_struct :
154 Split PAY_BALANCE_BATCH_HEADERS cursor
155 into two separate statements.
156 70.39 18-SEP-96 GPerry Added per_estab_attendances to per
157 misc delete block.
158 70.40 18-SEP-96 M.J.Hoyes a Removed all code which suppressed
159 the business_group_id index.
160 b Restructured the order of all
161 personnel related delete statements.
162 c Placed cursors around groups of
163 delete statements which are
164 inter-dependent.
165 d Added procedures delete_grade_direct
166 and delete_job_direct.
167 70.41 25-SEP-96 Added delete calls to all tables
168 relevant to the professional qualification
169 modules. This comprises of the tables
170 PER_ESTABLISHMENTS
171 PER_SUBJECT_USAGES
172 PER_QUAL_HISTORIES
173 PER_QUAL_SUBJECT_USAGES
174 PER_QUALIFICATION_TYPES
175 PER_QUALIFICATIONS
176 70.42 30-SEP-96 N.Bristow Constraint error encountered when deleting
177 from hr_assignment_set_amendments,
178 deletions where being performed in the
179 wrong order.
180
181 70.43 06-OCT-96 D.Kerr Temporarily removed delete_competence_detail
182 during 10.7/Prod15 release phase
183 70.44 11-NOV-96 N.Bristow Uncommented the deletion from
184 pay_balance_batch_headers and
185 pay_balance_batch_lines.
186 70.45 18-NOV-96 Tmathers Added p_rt_running parameter to allow
187 Rt's once again to be re-runnable.
188 70.46 12-MAR-97 DLo 1 Added delete statements to
189 delete_job_direct to
190 delete per_position_extra_info and
191 per_job_extra_info.
192 2 Added delete statement to
193 delete_person_direct to delete
194 per_people_extra_info.
195 110.1 27-JAN-97 N.Bristow Now deleting balance types correctly.
196 110.2 16-APR-98 SASmith Change required from
197 per_assignment_budget_values to
198 per_assignment_budget_values_f.
199 This is due to the table changes to
200 make it date tracked.
201
202 110.3 07-MAY-98 NBristow Revised the order of deletion of
203 database items and assignment details
204 110.4 08-SEP-98 smcmilla Added procedure to delete from
205 questionnaire tables (delete_qun_misc).
206 115.4 08-MAR-99 ALogue Delete of Security Group Info.
207 115.5 16-APR-99 ALogue Delete of Assignment_sets + removed
208 deletion form pay_exchange_rates_f.
209 115.7 07-JUL-99 MStewart Added code to check the enable_security_groups
210 profile and only delete lookups and security
211 groups if it is enabled and the security group
212 id is more than zero.
213 115.8 01-Oct-99 SCNair Date Track Position related changes
214 115.9 26-Oct-99 Susivasu Delete pay_batch_headers and associated entities.
215 115.10 12-JUN-00 N.Bristow Added delete_run_types and now removing
216 process_events and iterative rules.
217 115.11 03-OCT-00 I.Harding Added delete of job groups
218 115.12 03-OCT-00 I.Harding Change to above fix.
219 115.13 31-OCT-00 D.Saxby Fixed deletion of hr_assignment_set_criteria.
220 115.14 28-MAR-01 N.Bristow Deleting from pay_dated_tables,
221 pay_event_updates, payevent_groups
222 and pay_datetracked_events.
223 115.15 19-JUN-01 G.Perry Fixed WWBUG 1833930.
224 Changed SQL code to use exists rather than
225 selecting every row from the db. This makes
226 a significant performance gain.
227 115.16 02-JUL-01 N.Bristow Deleting from pay_event_procedures now.
228 115.17 04-Sep-00 dsaxby Changes for purge (1682940).
229 - Added deletes for pay_us_asg_reporting,
230 pay_balance_sets, pay_balance_set_members.
231 - Update the secondary_status for any purge
232 assignment actions before attempting to
233 call rollback_payroll_action procedure.
234 115.18 21-NOV-02 N.Bristow Now deleting from pay_element_types_f_tl,
235 pay_input_values_f_tl and pay_balance_types_tl
236 115.19 09-DEC-02 jonward Deleted grade MLS table
237 115.20 13-DEC-02 pmfletch Added delete from positions MLS table
238 115.21 27-DEC-02 joward Added delete from jobs MLS table
239 115.23 01-JUL-03 tvankayl Procedure DELETE_PAY_MISC modified to
240 delete records from PAY_CUSTOM_RESTRICTIONS_TL
241 before deleting from PAY_CUSTOMIZED_RESTRICTIONS
242 115.24 15-JUL-03 scchakra Bug 2982582. Added deletion of
243 pay_monetary_units_tl in
244 delete_assign_low_detail.
245 115.25 28-AUG-03 nbristow Added procedure delete_retro_details.
246 115.26 sep-2003 mbocutt Ex-person security enhancements. Remove
247 references to per_person_list_changes.
248 This file is now dependent on other
249 security changes delivered in Nov 2003 FP.
250 115.27 09-DEC-03 nbristow Now delete from pay_latest_balances.
251 115.28 12-DEC-03 nbristow Now delete from pay_upgrade_status.
252 115.29 30-APR-04 alogue Performance Repository : remove deletion from
253 pay_quickpay_inclusions as occurs within rollback
254 anyway.
255 115.30 11-MAY-04 smparame 3622082 Modified the delete from per_organization_list in
256 delete_secure_objects procedure to improve
257 performance.
258 115.31 24-MAY-04 alogue 3640651 Performance Repository fixes:
259 Rewrote delete_formula_direct.
260 Remove deletion from pay_costs in
261 delete_assign_low_detail as occurs within
262 rollback anyway!
263 Remove deletion from pay_run_results in
264 delete_assign_detail as occurs within
265 rollback anyway!
266 Removed redundant cursor ass_actions from
267 delete_assign_detail.
268 115.32 02-JUN-04 sbuche 3598568 Private procedure delete_secure_objects directly
269 referred HRMS internal objects. Hence it is removed
270 from this package and added to hr_security_internal.
271 Call to this procedure in delete_below_bg is replaced
272 with hr_security.delete_list_for_bg.
273 115.33 23-JUN-04 adhunter 3710074 added delete of absence types tl table in
274 delete_person_direct
275 115.34 23-JUN-04 adhunter added revision comment
276 115.35 26-SEP-04 nbristow Changed code so that it can be run on the
277 test harness DB. Also not deleting
278 from pay_object_groups.
279 115.36 06-MAR-05 nbristow Now deleting the time definitions.
280 */
281 --
282 -- Package variables
283 --
284 g_package varchar2(33) := 'hr_delete.'; -- Global package name
285 --
286 PROCEDURE delete_time_def_direct(p_business_group_id NUMBER)
287 IS
288 --
289 cursor get_time_defs(p_bg_id number)
290 is
291 select time_definition_id
292 from pay_time_definitions
293 where business_group_id = p_bg_id;
294 --
295 BEGIN
296 --
297 for timrec in get_time_defs(p_business_group_id) loop
298 --
299 delete from per_time_periods
300 where time_definition_id = timrec.time_definition_id;
301 --
302 end loop;
303 --
304 delete from pay_time_definitions
305 where business_group_id = p_business_group_id;
306 --
307 END delete_time_def_direct;
308 --
309 PROCEDURE delete_retro_details(p_business_group_id NUMBER)
310 IS
311 --
312 cursor get_ret_asg(p_bg_id number) is
313 select pra.retro_assignment_id
314 from pay_retro_assignments pra
315 where pra.assignment_id in (select distinct paf.assignment_id
316 from per_assignments_f paf
317 where paf.business_group_id = p_bg_id);
318 --
319 cursor get_ret_comp_use(p_bg_id number) is
320 select retro_component_usage_id
321 from pay_retro_component_usages
322 where business_group_id = p_bg_id;
323 --
324 BEGIN
325 --
326 for rarec in get_ret_asg(p_business_group_id) loop
327 --
328 delete from pay_retro_entries
329 where retro_assignment_id = rarec.retro_assignment_id;
330 --
331 delete from pay_retro_assignments
332 where retro_assignment_id = rarec.retro_assignment_id;
333 --
334 end loop;
335 --
336 for retrec in get_ret_comp_use(p_business_group_id) loop
337 --
338 delete from pay_element_span_usages
339 where retro_component_usage_id = retrec.retro_component_usage_id;
340 --
341 delete from pay_retro_component_usages
342 where retro_component_usage_id = retrec.retro_component_usage_id;
343 --
344 end loop;
345 --
346 END delete_retro_details;
347 --
348 --
349 PROCEDURE delete_run_types(p_business_group_id NUMBER)
350 IS
351 cursor getrt(p_business_group_id number)
352 is
353 select run_type_id
354 from pay_run_types_f
355 where business_group_id = p_business_group_id;
356 BEGIN
357 --
358 for rtrec in getrt (p_business_group_id) loop
359 --
360 delete from pay_run_type_usages_f
361 where parent_run_type_id = rtrec.run_type_id;
362 delete from pay_run_type_usages_f
363 where child_run_type_id = rtrec.run_type_id;
364 delete from pay_element_type_usages_f
365 where run_type_id = rtrec.run_type_id;
366 delete from pay_run_type_org_methods_f
367 where run_type_id = rtrec.run_type_id;
368 delete from pay_run_types_f
369 where run_type_id = rtrec.run_type_id;
370 --
371 end loop;
372 --
373 END delete_run_types;
374 --
375 PROCEDURE delete_mag_structure(p_business_group_id NUMBER)
376 IS
377 begin
378 --
379 hr_utility.set_location('hr_delete.delete_mag_structure',1);
380 DELETE FROM pay_magnetic_records mr
381 WHERE EXISTS ( SELECT ''
382 FROM ff_formulas_f ff
383 WHERE ff.formula_id = mr.formula_id
384 AND ff.business_group_id = p_business_group_id);
385 --
386 --
387 end delete_mag_structure;
388 --
389 PROCEDURE delete_bal_load_struct(p_business_group_id NUMBER)
390 IS
391 --
392 l_business_group_name per_business_groups.name%type ;
393 --
394 CURSOR get_bg IS
395 SELECT bg.name
396 FROM per_business_groups bg
397 WHERE bg.business_group_id = p_business_group_id ;
398 --
399 CURSOR pbh IS
400 SELECT bh.batch_id,
401 bh.batch_status
402 FROM pay_balance_batch_headers bh
403 WHERE ( (bh.business_group_id = p_business_group_id)
404 OR ( upper(bh.business_group_name) = upper(l_business_group_name)) );
405 --
406 CURSOR pbl (p_batch in number) IS
407 SELECT bl.batch_line_id
408 FROM pay_balance_batch_lines bl
409 where bl.batch_id = p_batch;
410 --
411 begin
412 --
413 hr_utility.set_location('hr_delete.delete_bal_load_struct',10);
414 --
415 OPEN get_bg ;
416 FETCH get_bg INTO l_business_group_name ;
417 CLOSE get_bg ;
418 --
419 hr_utility.set_location('hr_delete.delete_bal_load_struct',20);
420 --
421 FOR pbhrec IN pbh LOOP
422 if (pbhrec.batch_status in ('E', 'P', 'V')) then
423 hr_utility.set_location('hr_delete.delete_bal_load_struct',30);
424 FOR pblrec IN pbl (pbhrec.batch_id) LOOP
425 DELETE FROM pay_message_lines
426 WHERE source_id = pblrec.batch_line_id
427 AND source_type = 'L';
428 END LOOP;
429 --
430 DELETE FROM pay_message_lines
431 WHERE source_id = pbhrec.batch_id
432 AND source_type = 'H';
433 end if;
434 DELETE FROM pay_balance_batch_lines bl
435 WHERE bl.batch_id = pbhrec.batch_id;
436 --
437 DELETE FROM pay_balance_batch_headers bh
438 WHERE bh.batch_id = pbhrec.batch_id;
439 END LOOP;
440 --
441 hr_utility.set_location('hr_delete.delete_bal_load_struct',99);
442 --
443 end delete_bal_load_struct;
444 --
445 PROCEDURE delete_formula_direct(p_business_group_id NUMBER)
446 IS
447 --
448 l_proc varchar2(80) := g_package||'delete_formula_direct';
449 --
450 cursor csr_get_bg_formulas
451 (c_business_group_id ff_formulas_f.business_group_id%TYPE)
452 is
453 SELECT distinct formula_id
454 FROM ff_formulas_f
455 WHERE business_group_id = c_business_group_id;
456 --
457 begin
458 hr_utility.set_location('Entering: '||l_proc,10);
459 --
460 -- Check if a formulas exist for the business group
461 --
462 for form in csr_get_bg_formulas(p_business_group_id) loop
463 --
464 DELETE FROM ff_compiled_info_f ci
465 WHERE ci.formula_id = form.formula_id;
466 hr_utility.set_location(l_proc,20);
467 --
468 DELETE FROM ff_fdi_usages_f fdi
469 WHERE fdi.formula_id = form.formula_id;
470 hr_utility.set_location(l_proc,30);
471 --
472 DELETE FROM ff_formulas_f ff
473 WHERE ff.formula_id = form.formula_id;
474 hr_utility.set_location(l_proc,40);
475 --
476 end loop;
477 --
478 hr_utility.set_location('Leaving: '||l_proc,100);
479 end delete_formula_direct;
480 --
481 PROCEDURE delete_database_items(p_business_group_id NUMBER)
482 IS
483 --
484 l_proc varchar2(80) := g_package||'delete_database_items';
485 --
486 l_exists varchar2(1);
487 --
488 cursor csr_get_ff_user_ents
489 (c_business_group_id ff_user_entities.business_group_id%TYPE)
490 is
491 SELECT null
492 FROM ff_user_entities
493 WHERE business_group_id = c_business_group_id;
494 --
495 begin
496 hr_utility.set_location('Entering: '||l_proc, 10);
497 --
498 -- Check if a user entities exist for the business group
499 --
500 open csr_get_ff_user_ents(p_business_group_id);
501 fetch csr_get_ff_user_ents into l_exists;
502 if csr_get_ff_user_ents%found then
503 --
504 DELETE FROM ff_user_entities
505 WHERE business_group_id = p_business_group_id;
506 hr_utility.set_location(l_proc, 20);
507 --
508 end if;
509 close csr_get_ff_user_ents;
510 hr_utility.set_location('Leaving: '||l_proc, 30);
511 --
512 end delete_database_items;
513 --
514 PROCEDURE delete_assign_low_detail(p_business_group_id NUMBER)
515 IS
516 begin
517 --
518 hr_utility.set_location('hr_delete.delete_assign_low_detail',1);
519 DELETE FROM pay_coin_anal_elements cae
520 WHERE EXISTS (SELECT ''
521 FROM pay_pre_payments ppp
522 WHERE ppp.pre_payment_id
523 = cae.pre_payment_id
524 AND EXISTS ( SELECT ''
525 FROM pay_assignment_actions paa
526 WHERE paa.assignment_action_id
527 = ppp.assignment_action_id
528 AND EXISTS (SELECT ''
529 FROM per_assignments_f pa
530 WHERE pa.assignment_id
531 = paa.assignment_id
532 AND pa.business_group_id
533 = p_business_group_id
534 )
535 )
536 );
537 --
538 hr_utility.set_location('hr_delete.delete_assign_low_detail',3);
539 DELETE FROM pay_monetary_units_tl montl
540 WHERE EXISTS ( SELECT ''
541 FROM pay_monetary_units mon
542 WHERE montl.monetary_unit_id = mon.monetary_unit_id
543 AND mon.business_group_id = p_business_group_id
544 );
545 --
546 DELETE FROM pay_monetary_units mon
547 WHERE mon.business_group_id = p_business_group_id;
548 --
549 hr_utility.set_location('hr_delete.delete_assign_low_detail',5);
550 DELETE FROM pay_process_events ppe
551 WHERE EXISTS (SELECT ''
552 FROM per_assignments_f pa
553 WHERE pa.assignment_id = ppe.assignment_id
554 AND pa.business_group_id = p_business_group_id
555 );
556 --
557 end delete_assign_low_detail;
558 --
559 --
560 PROCEDURE delete_assign_detail(p_business_group_id NUMBER)
561 IS
562 --
563 l_proc varchar2(80) := g_package||'delete_assign_detail';
564 --
565 l_exists varchar2(1);
566 --
567 -- This cursor used in the delete of entry values.
568 CURSOR cev is
569 SELECT pee.element_entry_id
570 from pay_element_entries_f pee,
571 pay_element_links_f pel
572 where pel.business_group_id = p_business_group_id
573 and pee.element_link_id = pel.element_link_id;
574 --
575 begin
576 hr_utility.set_location('Entering: '||l_proc, 10);
577 --
578 -- This performed here because we have to cope with
579 -- the rollback rules that prevent rolling back
580 -- single assignment actions for various action types.
581 --
582 declare
583 --
584 cursor lbcur(p_bg_id number) is
585 select palb.latest_balance_id,
586 'ASG' bal_type
587 from pay_assignment_latest_balances palb,
588 pay_assignment_actions paa,
589 pay_payroll_actions ppa
590 where ppa.business_group_id = p_bg_id
591 and ppa.payroll_action_id = paa.payroll_action_id
592 and paa.assignment_action_id = palb.assignment_action_id
593 union all
594 select pplb.latest_balance_id,
595 'PER' bal_type
596 from pay_person_latest_balances pplb,
597 pay_assignment_actions paa,
598 pay_payroll_actions ppa
599 where ppa.business_group_id = p_bg_id
600 and ppa.payroll_action_id = paa.payroll_action_id
601 and paa.assignment_action_id = pplb.assignment_action_id
602 union all
603 select plb.latest_balance_id,
604 'AP' bal_type
605 from pay_latest_balances plb,
606 pay_assignment_actions paa,
607 pay_payroll_actions ppa
608 where ppa.business_group_id = p_bg_id
609 and ppa.payroll_action_id = paa.payroll_action_id
610 and paa.assignment_action_id = plb.assignment_action_id;
611 --
612 cursor c1 is
613 select pac.payroll_action_id,
614 pac.action_type
615 from pay_payroll_actions pac
616 where pac.business_group_id = p_business_group_id
617 order by pac.effective_date desc,
618 pac.payroll_action_id desc;
619 begin
620 for c1rec in c1 loop
621 if(c1rec.action_type = 'Z') then
622 -- Ensure the Purge action can be rolled back.
623 update pay_assignment_actions act
624 set act.secondary_status = 'U'
625 where act.payroll_action_id = c1rec.payroll_action_id;
626 end if;
627 --
628 -- Delete any latest balances that exist prior to the rollback
629 -- This is done sine we don't know the value of SINGLE_BAL_TABLE
630 -- at the time of the original processing.
631 --
632 for lbrec in lbcur(p_business_group_id) loop
633 if (lbrec.bal_type in ('ASG', 'PER')) then
634 delete from pay_balance_context_values
635 where latest_balance_id = lbrec.latest_balance_id;
636 end if;
637 --
638 if lbrec.bal_type = 'ASG' then
639 delete from pay_assignment_latest_balances
640 where latest_balance_id = lbrec.latest_balance_id;
641 elsif lbrec.bal_type = 'PER' then
642 delete from pay_person_latest_balances
643 where latest_balance_id = lbrec.latest_balance_id;
644 else
645 delete from pay_latest_balances
646 where latest_balance_id = lbrec.latest_balance_id;
647 end if;
648 --
649 end loop;
650 --
651 py_rollback_pkg.rollback_payroll_action(c1rec.payroll_action_id);
652 end loop;
653 end;
654 hr_utility.set_location(l_proc, 30);
655 --
656 -- Delete entries and entry values.
657 --
658 for cevrec in cev loop
659 delete from pay_element_entry_values_f eev
660 where eev.element_entry_id = cevrec.element_entry_id;
661 --
662 delete from pay_entry_process_details
663 where element_entry_id = cevrec.element_entry_id;
664 --
665 delete from pay_element_entries_f pee
666 where pee.element_entry_id = cevrec.element_entry_id;
667 end loop;
668 hr_utility.set_location('Leaving: '||l_proc, 90);
669 --
670 end delete_assign_detail;
671 --
672 --
673 PROCEDURE delete_assign_direct(p_business_group_id NUMBER)
674 IS
675 --
676 l_proc varchar2(80) := g_package||'delete_assign_direct';
677 --
678 l_exists varchar2(1);
679 --
680 -- WWBUG 1833930.
681 -- Changed all following cursors to use exists and for assignments
682 -- to use the base table
683 --
684 cursor csr_get_asg
685 (c_business_group_id per_assignments_f.business_group_id%TYPE)
686 is
687 SELECT null
688 FROM sys.dual
689 WHERE exists(select null
690 FROM per_all_assignments_f
691 WHERE business_group_id = c_business_group_id);
692 --
693 cursor csr_get_ast
694 (c_business_group_id per_assignments_f.business_group_id%TYPE)
695 is
696 SELECT null
697 FROM sys.dual
698 WHERE exists(select null
699 FROM per_assignment_status_types
700 WHERE business_group_id = c_business_group_id);
701 --
702 cursor csr_get_ltp
703 (c_business_group_id per_letter_types.business_group_id%TYPE)
704 is
705 SELECT null
706 FROM sys.dual
707 WHERE exists(select null
708 FROM per_letter_types
709 WHERE business_group_id = c_business_group_id);
710 --
711 cursor csr_get_prs
712 (c_business_group_id per_parent_spines.business_group_id%TYPE)
713 is
714 SELECT null
715 FROM sys.dual
716 WHERE exists(select null
717 FROM per_parent_spines
718 WHERE business_group_id = c_business_group_id);
719 --
720 cursor csr_get_gra
721 (c_business_group_id per_grades.business_group_id%TYPE)
722 is
723 SELECT null
724 FROM sys.dual
725 WHERE exists(select null
726 FROM per_grades
727 WHERE business_group_id = c_business_group_id);
728 --
729 cursor csr_get_job
730 (c_business_group_id per_jobs.business_group_id%TYPE)
731 is
732 SELECT null
733 FROM sys.dual
734 WHERE exists(select null
735 FROM per_jobs
736 WHERE business_group_id = c_business_group_id);
737 --
738 cursor csr_get_rca
739 (c_business_group_id
740 PER_RECRUITMENT_ACTIVITIES.business_group_id%TYPE)
741 is
742 SELECT null
743 FROM sys.dual
744 WHERE exists(select null
745 FROM PER_RECRUITMENT_ACTIVITIES
746 WHERE business_group_id = c_business_group_id);
747 --
748 cursor csr_get_req
749 (c_business_group_id
750 PER_REQUISITIONS.business_group_id%TYPE)
751 is
752 SELECT null
753 FROM sys.dual
754 WHERE exists(select null
755 FROM PER_REQUISITIONS
756 WHERE business_group_id = c_business_group_id);
757 --
758 cursor csr_get_bud
759 (c_business_group_id
760 PER_BUDGETS.business_group_id%TYPE)
761 is
762 SELECT null
763 FROM sys.dual
764 WHERE exists(select null
765 FROM PER_BUDGETS
766 WHERE business_group_id = c_business_group_id);
767 --
768 cursor csr_get_pyp
769 (c_business_group_id
770 PER_PAY_PROPOSALS.business_group_id%TYPE)
771 is
772 SELECT null
773 FROM sys.dual
774 WHERE exists(select null
775 FROM PER_PAY_PROPOSALS
776 WHERE business_group_id = c_business_group_id);
777 --
778 cursor csr_get_crp
779 (c_business_group_id
780 PER_CAREER_PATHS.business_group_id%TYPE)
781 is
782 SELECT null
783 FROM sys.dual
784 WHERE exists(select null
785 FROM PER_CAREER_PATHS
786 WHERE business_group_id = c_business_group_id);
787 --
788 cursor csr_get_pst
789 (c_business_group_id
790 PER_POSITION_STRUCTURES.business_group_id%TYPE)
791 is
792 SELECT null
793 FROM sys.dual
794 WHERE exists(select null
795 FROM PER_POSITION_STRUCTURES
796 WHERE business_group_id = c_business_group_id);
797 --
798 cursor csr_get_evt
799 (c_business_group_id
800 per_events.business_group_id%TYPE)
801 is
802 SELECT null
803 FROM sys.dual
804 WHERE exists(select null
805 FROM per_events
806 WHERE business_group_id = c_business_group_id);
807 --
808 -- End of fix for WWBUG 1833930.
809 -- Changed all following cursors to use exists and for assignments
810 -- to use the base table
811 --
812 begin
813 hr_utility.set_location('Entering: '||l_proc,10);
814 --
815 -- Check if letter types exist for the
816 -- business group
817 --
818 open csr_get_ltp (p_business_group_id);
819 fetch csr_get_ltp into l_exists;
820 if csr_get_ltp%found then
821 --
822 DELETE FROM per_letter_request_lines
823 WHERE business_group_id = p_business_group_id;
824 hr_utility.set_location(l_proc,20);
825 --
826 DELETE per_letter_requests
827 WHERE business_group_id = p_business_group_id;
828 hr_utility.set_location(l_proc,30);
829 --
830 DELETE per_letter_gen_statuses
831 WHERE business_group_id = p_business_group_id;
832 hr_utility.set_location(l_proc,40);
833 --
834 DELETE per_letter_types
835 WHERE business_group_id = p_business_group_id;
836 hr_utility.set_location(l_proc,50);
837 --
838 end if;
839 close csr_get_ltp;
840 hr_utility.set_location(l_proc,60);
841 --
842 -- Check if pay proposals exist for the
843 -- business group
844 --
845 open csr_get_pyp (p_business_group_id);
846 fetch csr_get_pyp into l_exists;
847 if csr_get_pyp%found then
848 --
849 delete from per_pay_proposal_components ppc
850 where ppc.business_group_id = p_business_group_id;
851 hr_utility.set_location(l_proc, 70);
852 --
853 delete from per_pay_proposals
854 where business_group_id = p_business_group_id;
855 hr_utility.set_location(l_proc, 80);
856 --
857 end if;
858 close csr_get_pyp;
859 hr_utility.set_location(l_proc,90);
860 --
861 -- Check if an event exists for the business group.
862 --
863 open csr_get_evt(p_business_group_id);
864 fetch csr_get_evt into l_exists;
865 if csr_get_evt%found then
866 --
867 DELETE FROM per_bookings pb
868 WHERE pb.business_group_id = p_business_group_id;
869 hr_utility.set_location(l_proc,100);
870 --
871 DELETE FROM per_events
872 WHERE business_group_id = p_business_group_id;
873 hr_utility.set_location(l_proc,110);
874 --
875 end if;
876 close csr_get_evt;
877 hr_utility.set_location(l_proc,120);
878 --
879 -- Check if a budget exists for the
880 -- business group
881 --
882 open csr_get_bud (p_business_group_id);
883 fetch csr_get_bud into l_exists;
884 if csr_get_bud%found then
885 --
886 DELETE FROM per_assignment_budget_values_f abv
887 WHERE abv.business_group_id = p_business_group_id;
888 hr_utility.set_location(l_proc, 130);
889 --
890 DELETE FROM per_budget_values
891 WHERE business_group_id = p_business_group_id;
892 hr_utility.set_location(l_proc,140);
893 --
894 DELETE FROM per_budget_values
895 WHERE business_group_id = p_business_group_id;
896 hr_utility.set_location(l_proc,150);
897 --
898 DELETE FROM per_budget_elements
899 WHERE business_group_id = p_business_group_id;
900 hr_utility.set_location(l_proc,160);
901 --
902 DELETE FROM per_budget_versions
903 WHERE business_group_id = p_business_group_id;
904 hr_utility.set_location(l_proc,170);
905 --
906 DELETE FROM per_budgets
907 WHERE business_group_id = p_business_group_id;
908 hr_utility.set_location(l_proc,180);
909 --
910 end if;
911 close csr_get_bud;
912 hr_utility.set_location(l_proc,190);
913 --
914 -- Check if an assignment exists for the business group
915 --
916 open csr_get_asg (p_business_group_id);
917 fetch csr_get_asg into l_exists;
918 if csr_get_asg%found then
919 --
920 DELETE FROM pay_object_groups pog
921 WHERE
922 SOURCE_TYPE = 'PAF'
923 AND
924 EXISTS
925 (SELECT ''
926 FROM per_assignments_f pa
927 WHERE pa.assignment_id = pog.source_id
928 AND pa.business_group_id = p_business_group_id);
929 --
930 DELETE FROM pay_us_asg_reporting uar
931 WHERE
932 EXISTS
933 (SELECT ''
934 FROM per_assignments_f pa
935 WHERE pa.assignment_id = uar.assignment_id
936 AND pa.business_group_id = p_business_group_id);
937 --
938 DELETE FROM pay_personal_payment_methods_f ppm
939 WHERE ppm.business_group_id = p_business_group_id;
940 hr_utility.set_location(l_proc, 200);
941 --
942 DELETE FROM pay_cost_allocations_f ca
943 WHERE ca.business_group_id = p_business_group_id;
944 hr_utility.set_location(l_proc, 210);
945 --
946 DELETE FROM per_assignment_extra_info aei
947 WHERE
948 EXISTS
949 (SELECT ''
950 FROM per_assignments_f pa
951 WHERE pa.assignment_id = aei.assignment_id
952 AND pa.business_group_id = p_business_group_id);
953 hr_utility.set_location(l_proc, 220);
954 --
955 DELETE FROM per_quickpaint_result_text qrt
956 WHERE
957 EXISTS
958 (SELECT ''
959 FROM per_assignments_f pa
960 WHERE pa.assignment_id = qrt.assignment_id
961 AND pa.business_group_id = p_business_group_id);
962 hr_utility.set_location(l_proc,230);
963 --
964 DELETE FROM pay_assignment_link_usages alu
965 WHERE EXISTS ( SELECT ''
966 FROM per_assignments_f pa
967 WHERE pa.assignment_id = alu.assignment_id
968 AND pa.business_group_id = p_business_group_id);
969 hr_utility.set_location(l_proc,235);
970 --
971 DELETE FROM hr_assignment_set_amendments asa
972 WHERE EXISTS ( SELECT ''
973 FROM per_assignments_f pa
974 WHERE pa.assignment_id = asa.assignment_id
975 AND pa.business_group_id = p_business_group_id);
976 hr_utility.set_location('Leaving: '||l_proc,237);
977 --
978 DELETE FROM per_spinal_point_placements_f
979 WHERE business_group_id = p_business_group_id;
980 hr_utility.set_location(l_proc, 240);
981 --
982 DELETE FROM per_secondary_ass_statuses
983 WHERE business_group_id = p_business_group_id;
984 hr_utility.set_location(l_proc, 250);
985 --
986 -- WWBUG 1833930. Changed to use base table per_all_assignments_f
987 --
988 DELETE FROM per_all_assignments_f
989 WHERE business_group_id = p_business_group_id;
990 hr_utility.set_location(l_proc, 260);
991 --
992 end if;
993 close csr_get_asg;
994 hr_utility.set_location(l_proc, 270);
995 --
996 DELETE FROM per_pay_bases
997 WHERE business_group_id = p_business_group_id;
998 hr_utility.set_location(l_proc,160);
999 --
1000 -- Check if career paths exist for the business group
1001 --
1002 open csr_get_crp (p_business_group_id);
1003 fetch csr_get_crp into l_exists;
1004 if csr_get_crp%found then
1005 --
1006 DELETE FROM per_career_path_elements
1007 WHERE business_group_id = p_business_group_id;
1008 hr_utility.set_location(l_proc,60);
1009 --
1010 DELETE FROM per_career_paths
1011 WHERE business_group_id = p_business_group_id;
1012 hr_utility.set_location(l_proc,70);
1013 --
1014 end if;
1015 close csr_get_crp;
1016 hr_utility.set_location(l_proc,70);
1017 --
1018 -- Check if an assignment status type exists for the
1019 -- business group
1020 --
1021 open csr_get_ast (p_business_group_id);
1022 fetch csr_get_ast into l_exists;
1023 if csr_get_ast%found then
1024 --
1025 DELETE PER_ASS_STATUS_TYPE_AMENDS
1026 WHERE business_group_id = p_business_group_id;
1027 hr_utility.set_location(l_proc,150);
1028 --
1029 DELETE per_assignment_status_types
1030 WHERE business_group_id = p_business_group_id;
1031 hr_utility.set_location(l_proc,160);
1032 --
1033 end if;
1034 close csr_get_ast;
1035 hr_utility.set_location(l_proc,170);
1036 --
1037 -- Check if a recruitment activity exists for the
1038 -- business group
1039 --
1040 open csr_get_rca (p_business_group_id);
1041 fetch csr_get_rca into l_exists;
1042 if csr_get_rca%found then
1043 --
1044 DELETE FROM per_recruitment_activity_for
1045 WHERE business_group_id = p_business_group_id;
1046 hr_utility.set_location(l_proc,280);
1047 --
1048 DELETE FROM per_recruitment_activities
1049 WHERE business_group_id = p_business_group_id;
1050 hr_utility.set_location(l_proc,290);
1051 --
1052 end if;
1053 close csr_get_rca;
1054 hr_utility.set_location(l_proc,300);
1055 --
1056 -- Check if a requisition exists for the
1057 -- business group
1058 --
1059 open csr_get_req (p_business_group_id);
1060 fetch csr_get_req into l_exists;
1061 if csr_get_req%found then
1062 --
1063 DELETE FROM per_vacancies vac
1064 WHERE vac.business_group_id = p_business_group_id;
1065 hr_utility.set_location(l_proc,310);
1066 --
1067 DELETE FROM per_requisitions pr
1068 WHERE pr.business_group_id = p_business_group_id;
1069 hr_utility.set_location(l_proc,320);
1070 --
1071 end if;
1072 close csr_get_req;
1073 hr_utility.set_location(l_proc,330);
1074 --
1075 DELETE FROM pay_org_payment_methods_f
1076 WHERE business_group_id = p_business_group_id;
1077 hr_utility.set_location(l_proc,340);
1078 --
1079 end delete_assign_direct;
1080 --
1081 --
1082 PROCEDURE delete_grade_direct(p_business_group_id NUMBER)
1083 IS
1084 --
1085 l_proc varchar2(80) := g_package||'delete_grade_direct';
1086 --
1087 l_exists varchar2(1);
1088 --
1089 cursor csr_get_gra
1090 (c_business_group_id per_grades.business_group_id%TYPE)
1091 is
1092 SELECT null
1093 FROM per_grades
1094 WHERE business_group_id = c_business_group_id;
1095 --
1096 cursor csr_get_prs
1097 (c_business_group_id per_parent_spines.business_group_id%TYPE)
1098 is
1099 SELECT null
1100 FROM per_parent_spines
1101 WHERE business_group_id = c_business_group_id;
1102 --
1103 begin
1104 hr_utility.set_location('Entering: '||l_proc,10);
1105 --
1106 -- Check if grades exist for the
1107 -- business group
1108 --
1109 open csr_get_gra (p_business_group_id);
1110 fetch csr_get_gra into l_exists;
1111 if csr_get_gra%found then
1112 --
1113 DELETE FROM per_grade_spines_f gs
1114 WHERE gs.business_group_id = p_business_group_id;
1115 hr_utility.set_location(l_proc,200);
1116 --
1117 DELETE FROM per_valid_grades vg
1118 WHERE vg.business_group_id = p_business_group_id;
1119 hr_utility.set_location(l_proc,230);
1120 --
1121 DELETE FROM per_grades_tl gdt
1122 WHERE gdt.grade_id IN (SELECT pg.grade_id
1123 FROM per_grades pg
1124 WHERE pg.business_group_id = p_business_group_id);
1125 hr_utility.set_location(l_proc,235);
1126 --
1127 DELETE FROM per_grades pg
1128 WHERE pg.business_group_id = p_business_group_id;
1129 hr_utility.set_location(l_proc,240);
1130 --
1131 end if;
1132 close csr_get_gra;
1133 hr_utility.set_location(l_proc,170);
1134 --
1135 -- Check if parent spines exist for the
1136 -- business group
1137 --
1138 open csr_get_prs (p_business_group_id);
1139 fetch csr_get_prs into l_exists;
1140 if csr_get_prs%found then
1141 --
1142 DELETE FROM PER_SPINAL_POINT_STEPS_F
1143 WHERE business_group_id = p_business_group_id;
1144 hr_utility.set_location(l_proc,180);
1145 --
1146 DELETE FROM per_spinal_points
1147 WHERE business_group_id = p_business_group_id;
1148 hr_utility.set_location(l_proc,190);
1149 --
1150 DELETE FROM per_parent_spines
1151 WHERE business_group_id = p_business_group_id;
1152 hr_utility.set_location(l_proc,200);
1153 --
1154 end if;
1155 close csr_get_prs;
1156 hr_utility.set_location('Leaving: '||l_proc,130);
1157 --
1158 end delete_grade_direct;
1159 --
1160 --
1161 PROCEDURE delete_job_direct(p_business_group_id NUMBER)
1162 IS
1163 --
1164 l_proc varchar2(80) := g_package||'delete_job_direct';
1165 --
1166 l_exists varchar2(1);
1167 --
1168 cursor csr_get_pst
1169 (c_business_group_id
1170 PER_POSITION_STRUCTURES.business_group_id%TYPE)
1171 is
1172 SELECT null
1173 FROM PER_POSITION_STRUCTURES
1174 WHERE business_group_id = c_business_group_id;
1175 --
1176 cursor csr_get_job
1177 (c_business_group_id per_jobs.business_group_id%TYPE)
1178 is
1179 SELECT null
1180 FROM per_jobs
1181 WHERE business_group_id = c_business_group_id;
1182 --
1183 begin
1184 hr_utility.set_location('Entering: '||l_proc,10);
1185 --
1186 -- Check if position structures exist for the
1187 -- business group
1188 --
1189 open csr_get_pst (p_business_group_id);
1190 fetch csr_get_pst into l_exists;
1191 if csr_get_pst%found then
1192 --
1193 DELETE FROM per_pos_structure_elements pse
1194 WHERE pse.business_group_id = p_business_group_id;
1195 hr_utility.set_location(l_proc,20);
1196 --
1197 DELETE FROM per_pos_structure_versions psv
1198 WHERE psv.business_group_id = p_business_group_id;
1199 hr_utility.set_location(l_proc,30);
1200 --
1201 DELETE FROM per_position_structures ps
1202 WHERE ps.business_group_id = p_business_group_id;
1203 hr_utility.set_location(l_proc,40);
1204 --
1205 end if;
1206 close csr_get_pst;
1207 hr_utility.set_location(l_proc,50);
1208 --
1209 DELETE FROM per_job_evaluations
1210 WHERE business_group_id = p_business_group_id;
1211 hr_utility.set_location(l_proc,60);
1212 --
1213 DELETE FROM per_job_requirements
1214 WHERE business_group_id = p_business_group_id;
1215 hr_utility.set_location(l_proc,70);
1216 --
1217 -- Check if jobs exist for the business group
1218 --
1219 open csr_get_job (p_business_group_id);
1220 fetch csr_get_job into l_exists;
1221 if csr_get_job%found then
1222 --
1223 -- Changes 02-Oct-99 SCNair (per_positions to hr_all_positions_f) date track position req.
1224 --
1225 DELETE FROM per_position_extra_info poi
1226 WHERE EXISTS (SELECT ''
1227 FROM hr_all_positions_f pos
1228 WHERE pos.position_id = poi.position_id
1229 AND pos.business_group_id = p_business_group_id);
1230 hr_utility.set_location(l_proc,75);
1231 --
1232 -- PMFLETCH Delete from hr_all_positions_f_tl
1233 --
1234 DELETE FROM hr_all_positions_f_tl pft
1235 WHERE pft.position_id IN (SELECT psf.position_id
1236 FROM hr_all_positions_f psf
1237 WHERE psf.business_group_id = p_business_group_id);
1238 hr_utility.set_location(l_proc,76);
1239 --
1240 -- Changes 02-Oct-99 SCNair (delete hr_all_positions_f) date track position req.
1241 --
1242 DELETE FROM hr_all_positions_f
1243 WHERE business_group_id = p_business_group_id;
1244 hr_utility.set_location(l_proc,77);
1245 --
1246 DELETE FROM per_positions
1247 WHERE business_group_id = p_business_group_id;
1248 hr_utility.set_location(l_proc,80);
1249 --
1250 DELETE FROM per_job_extra_info jei
1251 WHERE EXISTS (SELECT ''
1252 FROM per_jobs job
1253 WHERE job.job_id = jei.job_id
1254 AND job.business_group_id = p_business_group_id);
1255 hr_utility.set_location(l_proc,85);
1256 --
1257 hr_utility.set_location(l_proc,86);
1258 --
1259 DELETE FROM per_jobs_tl jbt
1260 WHERE EXISTS (SELECT ''
1261 FROM per_jobs job
1262 WHERE job.job_id = jbt.job_id
1263 AND job.business_group_id = p_business_group_id);
1264 hr_utility.set_location(l_proc,87);
1265 --
1266 DELETE FROM per_jobs job
1267 WHERE job.business_group_id = p_business_group_id;
1268 --
1269 hr_utility.set_location(l_proc,90);
1270 --
1271 end if;
1272 close csr_get_job;
1273 --
1274 DELETE FROM per_job_groups jgr
1275 WHERE jgr.business_group_id = p_business_group_id;
1276 --
1277 hr_utility.set_location('Leaving: '||l_proc,100);
1278 --
1279 end delete_job_direct;
1280 --
1281 --
1282 --
1283 PROCEDURE delete_person_direct(p_business_group_id NUMBER)
1284 IS
1285 --
1286 l_proc varchar2(80) := g_package||'delete_person_direct';
1287 --
1288 l_exists varchar2(1);
1289 --
1290 cursor csr_get_person
1291 (c_business_group_id per_people_f.business_group_id%TYPE)
1292 is
1293 SELECT null
1294 FROM per_people_f
1295 WHERE business_group_id = c_business_group_id;
1296 --
1297 cursor csr_get_aats
1298 (c_business_group_id
1299 per_absence_attendance_types.business_group_id%TYPE)
1300 is
1301 SELECT null
1302 FROM per_absence_attendance_types
1303 WHERE business_group_id = c_business_group_id;
1304 --
1305 begin
1306 hr_utility.set_location('Entering: '||l_proc,10);
1307 --
1308 -- Check if a absence attendence types exist
1309 -- for the business group.
1310 --
1311 open csr_get_aats(p_business_group_id);
1312 fetch csr_get_aats into l_exists;
1313 if csr_get_aats%found then
1314 --
1315 DELETE FROM per_absence_attendances
1316 WHERE business_group_id = p_business_group_id;
1317 hr_utility.set_location(l_proc,20);
1318 --
1319 DELETE FROM per_abs_attendance_reasons
1320 WHERE business_group_id = p_business_group_id;
1321 hr_utility.set_location(l_proc,30);
1322 --
1323 DELETE FROM per_abs_attendance_types_tl t
1324 WHERE t.absence_attendance_type_id in
1325 (select b.absence_attendance_type_id
1326 from per_absence_attendance_types b
1327 where b.business_group_id = p_business_group_id);
1328 hr_utility.set_location(l_proc,40);
1329 --
1330 DELETE FROM per_absence_attendance_types
1331 WHERE business_group_id = p_business_group_id;
1332 hr_utility.set_location(l_proc,40);
1333 --
1334 end if;
1335 close csr_get_aats;
1336 hr_utility.set_location(l_proc,50);
1337 --
1338 -- Check if a person exists for the business group
1339 --
1340 open csr_get_person(p_business_group_id);
1341 fetch csr_get_person into l_exists;
1342 if csr_get_person%found then
1343 --
1344 DELETE FROM pay_object_groups pog
1345 WHERE
1346 SOURCE_TYPE = 'PPF'
1347 AND
1348 EXISTS (SELECT ''
1349 FROM per_people_f per
1350 WHERE per.person_id = pog.source_id
1351 AND per.business_group_id = p_business_group_id);
1352 --
1353 DELETE FROM per_people_extra_info pei
1354 WHERE EXISTS (SELECT ''
1355 FROM per_people_f per
1356 WHERE per.person_id = pei.person_id
1357 AND per.business_group_id = p_business_group_id);
1358 hr_utility.set_location(l_proc,55);
1359 --
1360 DELETE FROM per_person_analyses
1361 WHERE business_group_id = p_business_group_id;
1362 hr_utility.set_location(l_proc,60);
1363 --
1364 DELETE FROM per_contact_relationships cr
1365 WHERE cr.business_group_id = p_business_group_id;
1366 hr_utility.set_location(l_proc,70);
1367 --
1368 DELETE FROM per_applications app
1369 WHERE app.business_group_id = p_business_group_id;
1370 hr_utility.set_location(l_proc,80);
1371 --
1372 DELETE FROM per_periods_of_service pos
1373 WHERE pos.business_group_id = p_business_group_id;
1374 hr_utility.set_location(l_proc,90);
1375 --
1376 DELETE FROM per_addresses pa
1377 WHERE pa.business_group_id = p_business_group_id;
1378 hr_utility.set_location(l_proc,100);
1379 --
1380 DELETE FROM per_people_f pp
1381 WHERE pp.business_group_id = p_business_group_id;
1382 hr_utility.set_location(l_proc,110);
1383 --
1384 end if;
1385 close csr_get_person;
1386 hr_utility.set_location(l_proc,120);
1387 --
1388 DELETE FROM per_person_types
1389 WHERE business_group_id = p_business_group_id;
1390 hr_utility.set_location('Leaving: '||l_proc,130);
1391 --
1392 end delete_person_direct;
1393 --
1394 --
1395 PROCEDURE delete_per_misc(p_business_group_id NUMBER)
1396 IS
1397 --
1398 l_proc varchar2(80) := g_package||'delete_person_direct';
1399 --
1400 l_exists varchar2(1);
1401 --
1402 begin
1403 hr_utility.set_location('Entering: '||l_proc,10);
1404 --
1405 DELETE FROM per_number_generation_controls png
1406 WHERE png.business_group_id = p_business_group_id;
1407 hr_utility.set_location('Leaving: '||l_proc,10);
1408 --
1409 end delete_per_misc;
1410 --
1411 PROCEDURE delete_element_direct(p_business_group_id NUMBER)
1412 IS
1413 --
1414 l_proc varchar2(80) := g_package||'delete_element_direct';
1415 --
1416 l_exists varchar2(1);
1417 --
1418 cursor csr_get_ele_types
1419 (c_business_group_id pay_element_types_f.business_group_id%TYPE)
1420 is
1421 SELECT null
1422 FROM pay_element_types_f
1423 WHERE business_group_id = c_business_group_id;
1424 --
1425 --
1426 -- Cursor to loop through all input values in order to delete
1427 -- the database items.
1428 --
1429 CURSOR get_input_values is
1430 SELECT iv.input_value_id input_value_id,
1431 iv.generate_db_items_flag generate_db_items_flag
1432 FROM pay_input_values_f iv
1433 WHERE EXISTS ( SELECT ''
1434 FROM pay_element_types_f pet
1435 WHERE pet.element_type_id = iv.element_type_id
1436 AND pet.business_group_id = p_business_group_id)
1437 FOR UPDATE;
1438 --
1439 -- Cursor to loop through all the element types to delete all the
1440 -- database items
1441 --
1442 CURSOR get_element_types is
1443 SELECT element_type_id
1444 FROM pay_element_types_f
1445 WHERE business_group_id = p_business_group_id
1446 FOR UPDATE;
1447 --
1448 -- This cursor deletes link input values
1449 CURSOR lev is
1450 SELECT liv.link_input_value_id
1451 from pay_link_input_values_f liv,
1452 pay_element_links_f pel
1453 where pel.business_group_id = p_business_group_id
1454 and liv.element_link_id = pel.element_link_id;
1455 --
1456 begin
1457 hr_utility.set_location('Entering: '||l_proc, 10);
1458 DELETE FROM pay_balance_feeds_f pbf
1459 WHERE pbf.business_group_id = p_business_group_id;
1460 hr_utility.set_location(l_proc, 20);
1461 --
1462 FOR levrec in lev LOOP
1463 delete from pay_link_input_values_f liv
1464 where liv.link_input_value_id = levrec.link_input_value_id;
1465 END LOOP;
1466 hr_utility.set_location(l_proc, 30);
1467 --
1468 DELETE FROM pay_element_links_f el
1469 WHERE el.business_group_id = p_business_group_id;
1470 hr_utility.set_location(l_proc, 40);
1471 --
1472 DELETE FROM pay_element_type_rules etr
1473 WHERE EXISTS (SELECT ''
1474 FROM pay_element_sets es
1475 WHERE es.element_set_id = etr.element_set_id
1476 AND es.business_group_id = p_business_group_id);
1477 hr_utility.set_location(l_proc, 50);
1478 --
1479 DELETE FROM pay_ele_classification_rules ecr
1480 WHERE EXISTS (SELECT ''
1481 FROM pay_element_sets es
1482 WHERE es.element_set_id = ecr.element_set_id
1483 AND es.business_group_id = p_business_group_id);
1484 hr_utility.set_location(l_proc, 60);
1485 --
1486 DELETE FROM pay_element_sets es
1487 WHERE es.business_group_id = p_business_group_id;
1488 hr_utility.set_location(l_proc, 70);
1489 --
1490 DELETE FROM pay_sub_classification_rules_f scr
1491 WHERE scr.business_group_id = p_business_group_id;
1492 hr_utility.set_location(l_proc, 80);
1493 --
1494 DELETE FROM pay_formula_result_rules_f frr
1495 WHERE frr.business_group_id = p_business_group_id;
1496 hr_utility.set_location(l_proc, 90);
1497 --
1498 DELETE FROM pay_status_processing_rules_f spr
1499 WHERE spr.business_group_id = p_business_group_id;
1500 hr_utility.set_location(l_proc, 90);
1501 --
1502 DELETE FROM pay_iterative_rules_f pir
1503 WHERE exists (select ''
1504 from pay_element_types pet
1505 where pir.business_group_id = p_business_group_id
1506 and pet.element_type_id = pir.element_type_id
1507 );
1508 hr_utility.set_location(l_proc, 95);
1509 --
1510 -- Check if a element types exist for the business group
1511 --
1512 open csr_get_ele_types(p_business_group_id);
1513 fetch csr_get_ele_types into l_exists;
1514 if csr_get_ele_types%found then
1515 --
1516 -- Loop through and lock all input values within
1517 -- the business group.
1518 --
1519 for iv_rec in get_input_values loop
1520 --
1521 -- Delete input values
1522 --
1523 DELETE FROM pay_input_values_f
1524 WHERE CURRENT OF get_input_values;
1525 --
1526 delete from pay_input_values_f_tl
1527 where input_value_id = iv_rec.input_value_id;
1528 --
1529 end loop;
1530 hr_utility.set_location(l_proc, 100);
1531 --
1532 for et_rec in get_element_types loop
1533 --
1534 -- delete element types
1535 --
1536 delete from pay_element_types_f_tl
1537 where element_type_id = et_rec.element_type_id;
1538 --
1539 delete from pay_element_types_f
1540 where current of get_element_types;
1541 --
1542 --
1543 end loop;
1544 hr_utility.set_location(l_proc, 110);
1545 --
1546 end if;
1547 close csr_get_ele_types;
1548 hr_utility.set_location('Leaving: '||l_proc, 120);
1549 --
1550 end delete_element_direct;
1551 --
1552 --
1553 PROCEDURE delete_org_low_detail(p_business_group_id NUMBER)
1554 IS
1555 begin
1556 --
1557 hr_utility.set_location('hr_delete.delete_org_low_detail',10);
1558 DELETE FROM pay_grade_rules_f gr
1559 WHERE gr.business_group_id = p_business_group_id;
1560 --
1561 hr_utility.set_location('hr_delete.delete_org_low_detail',30);
1562 DELETE FROM pay_rates pr
1563 WHERE pr.business_group_id = p_business_group_id;
1564 hr_utility.set_location('hr_delete.delete_org_low_detail',70);
1565 --
1566 --
1567 end delete_org_low_detail;
1568 --
1569 --
1570 PROCEDURE delete_org_detail(p_business_group_id NUMBER)
1571 IS
1572 --
1573 l_proc varchar2(80) := g_package||'delete_org_detail';
1574 --
1575 l_exists varchar2(1);
1576 --
1577 cursor csr_get_spec_inf_type
1578 (c_business_group_id ff_formulas_f.business_group_id%TYPE)
1579 is
1580 SELECT null
1581 FROM per_special_info_types sit
1582 WHERE business_group_id = c_business_group_id;
1583 --
1584 begin
1585 --
1586 hr_utility.set_location('Entering: '||l_proc,10);
1587 DELETE FROM pay_wc_rates pwr
1588 WHERE pwr.business_group_id = p_business_group_id;
1589 hr_utility.set_location(l_proc,20);
1590 --
1591 DELETE FROM pay_wc_funds pwf
1592 WHERE pwf.business_group_id = p_business_group_id;
1593 hr_utility.set_location(l_proc,30);
1594 --
1595
1596
1597
1598 --
1599 -- Check if a special information type exists for the
1600 -- business group.
1601 --
1602 open csr_get_spec_inf_type(p_business_group_id);
1603 fetch csr_get_spec_inf_type into l_exists;
1604 if csr_get_spec_inf_type%found then
1605 --
1606 DELETE FROM per_special_info_type_usages situ
1607 WHERE situ.special_information_type_id =
1608 (SELECT sit.special_information_type_id
1609 FROM per_special_info_types sit
1610 WHERE sit.special_information_type_id = situ.special_information_type_id
1611 AND sit.business_group_id = p_business_group_id);
1612 hr_utility.set_location(l_proc,100);
1613 --
1614 DELETE FROM per_special_info_types sit
1615 WHERE sit.business_group_id = p_business_group_id;
1616 hr_utility.set_location(l_proc,110);
1617 --
1618 end if;
1619 close csr_get_spec_inf_type;
1620 hr_utility.set_location(l_proc,120);
1621 --
1622 hr_utility.set_location('Leaving: '||l_proc,200);
1623 --
1624 end delete_org_detail;
1625 --
1626 PROCEDURE delete_payroll_direct(p_business_group_id NUMBER)
1627 IS
1628 --
1629 l_proc varchar2(80) := g_package||'delete_payroll_direct';
1630 --
1631 l_exists varchar2(1);
1632 --
1633 cursor csr_get_bg_payrolls
1634 (c_business_group_id pay_payrolls_f.business_group_id%TYPE)
1635 is
1636 SELECT null
1637 FROM pay_payrolls_f
1638 WHERE business_group_id = c_business_group_id;
1639 --
1640 begin
1641 hr_utility.set_location('Entering: '||l_proc,10);
1642 --
1643 -- Check if a payrolls exist for the business group
1644 --
1645 open csr_get_bg_payrolls(p_business_group_id);
1646 fetch csr_get_bg_payrolls into l_exists;
1647 if csr_get_bg_payrolls%found then
1648 --
1649 DELETE FROM pay_org_pay_method_usages_f pmu
1650 WHERE
1651 EXISTS ( SELECT ''
1652 FROM pay_payrolls_f pp
1653 WHERE pp.payroll_id = pmu.payroll_id
1654 AND pp.business_group_id = p_business_group_id);
1655 hr_utility.set_location(l_proc,30);
1656 --
1657 DELETE FROM hr_assignment_set_criteria has
1658 WHERE
1659 EXISTS ( SELECT ''
1660 FROM hr_assignment_sets ase
1661 WHERE ase.assignment_set_id = has.assignment_set_id
1662 AND ase.business_group_id = p_business_group_id);
1663 hr_utility.set_location(l_proc,40);
1664 --
1665 DELETE FROM hr_assignment_sets ase
1666 WHERE ase.business_group_id = p_business_group_id;
1667 hr_utility.set_location(l_proc,50);
1668 --
1669 --
1670 DELETE FROM PER_TIME_PERIODS tim
1671 WHERE EXISTS ( SELECT ''
1672 FROM pay_payrolls_f pp
1673 WHERE pp.payroll_id = tim.payroll_id
1674 AND pp.business_group_id = p_business_group_id);
1675 hr_utility.set_location(l_proc,70);
1676 --
1677 DELETE FROM pay_payroll_gl_flex_maps glf
1678 WHERE EXISTS ( SELECT ''
1679 FROM pay_payrolls_f pp
1680 WHERE pp.payroll_id = glf.payroll_id
1681 AND pp.business_group_id = p_business_group_id);
1682 hr_utility.set_location(l_proc,80);
1683 --
1684 DELETE FROM pay_payrolls_f pay
1685 WHERE pay.business_group_id = p_business_group_id;
1686 hr_utility.set_location(l_proc,90);
1687 --
1688 end if;
1689 close csr_get_bg_payrolls;
1690 hr_utility.set_location('Leaving: '||l_proc,100);
1691 --
1692 --
1693 end delete_payroll_direct;
1694 --
1695 --
1696 PROCEDURE delete_balance_direct(p_business_group_id NUMBER)
1697 IS
1698 --
1699 l_proc varchar2(80) := g_package||'delete_balance_direct';
1700 --
1701 l_exists varchar2(1);
1702 --
1703 cursor csr_get_def_bals
1704 (c_business_group_id pay_payrolls_f.business_group_id%TYPE)
1705 is
1706 SELECT null
1707 FROM PAY_DEFINED_BALANCES
1708 WHERE business_group_id = c_business_group_id;
1709 --
1710 cursor csr_get_bal_types
1711 (c_business_group_id pay_payrolls_f.business_group_id%TYPE)
1712 is
1713 select balance_type_id
1714 from pay_balance_types
1715 where business_group_id = c_business_group_id;
1716 begin
1717 --
1718 hr_utility.set_location('Entering: '||l_proc,10);
1719 --
1720 DELETE FROM pay_backpay_rules br
1721 WHERE EXISTS ( SELECT ''
1722 FROM pay_backpay_sets bs
1723 WHERE bs.backpay_set_id = br.backpay_set_id
1724 AND bs.business_group_id = p_business_group_id);
1725 hr_utility.set_location(l_proc,30);
1726 --
1727 DELETE FROM pay_backpay_sets bs
1728 WHERE bs.business_group_id = p_business_group_id;
1729 hr_utility.set_location(l_proc,40);
1730 --
1731 DELETE FROM pay_balance_set_members bsm
1732 WHERE EXISTS ( SELECT ''
1733 FROM pay_balance_sets pbs
1734 WHERE pbs.balance_set_id = bsm.balance_set_id
1735 AND pbs.business_group_id = p_business_group_id);
1736 hr_utility.set_location(l_proc,50);
1737 --
1738 DELETE FROM pay_balance_sets pbs
1739 WHERE pbs.business_group_id = p_business_group_id;
1740 hr_utility.set_location(l_proc,60);
1741 --
1742 DELETE FROM pay_balance_classifications pbc
1743 WHERE pbc.business_group_id = p_business_group_id;
1744 hr_utility.set_location(l_proc,70);
1745 --
1746 -- Check if a defined balance exists for the business group
1747 --
1748 open csr_get_def_bals(p_business_group_id);
1749 fetch csr_get_def_bals into l_exists;
1750 if csr_get_def_bals%found then
1751 --
1752 DELETE FROM pay_defined_balances pdb
1753 WHERE pdb.business_group_id = p_business_group_id;
1754 hr_utility.set_location(l_proc,80);
1755 --
1756 end if;
1757 close csr_get_def_bals;
1758 hr_utility.set_location(l_proc,90);
1759 --
1760 DELETE FROM pay_balance_dimensions pbd
1761 WHERE pbd.business_group_id = p_business_group_id;
1762 hr_utility.set_location(l_proc,100);
1763 --
1764 for balrec in csr_get_bal_types(p_business_group_id) loop
1765 --
1766 DELETE FROM pay_balance_feeds_f pbf
1767 WHERE pbf.balance_type_id = balrec.balance_type_id;
1768 hr_utility.set_location('Leaving: '||l_proc,90);
1769 --
1770 DELETE FROM pay_balance_types_tl pbt
1771 WHERE pbt.balance_type_id = balrec.balance_type_id;
1772 hr_utility.set_location('Leaving: '||l_proc,95);
1773 --
1774 DELETE FROM pay_balance_types pbt
1775 WHERE pbt.balance_type_id = balrec.balance_type_id;
1776 hr_utility.set_location('Leaving: '||l_proc,100);
1777 --
1778 end loop;
1779 --
1780 end delete_balance_direct;
1781 --
1782 --
1783 PROCEDURE delete_pay_misc(p_business_group_id NUMBER)
1784 IS
1785 --
1786 l_proc varchar2(80) := g_package||'delete_pay_misc';
1787 --
1788 l_exists varchar2(1);
1789 --
1790 cursor csr_get_mess_lines
1791 (c_business_group_id pay_payrolls_f.business_group_id%TYPE)
1792 is
1793 SELECT null
1794 FROM PAY_MESSAGE_LINES
1795 WHERE source_id = c_business_group_id
1796 and source_type = 'B';
1797 --
1798 cursor csr_batch_header (p_bg_id number)
1799 is
1800 SELECT pbh.BATCH_ID
1801 FROM PAY_BATCH_HEADERS pbh
1802 WHERE pbh.BUSINESS_GROUP_ID = p_bg_id;
1803 --
1804 begin
1805 hr_utility.set_location('Entering: '||l_proc, 10);
1806 DELETE pay_consolidation_sets cs
1807 WHERE cs.business_group_id = p_business_group_id;
1808 hr_utility.set_location(l_proc, 20);
1809 --
1810 DELETE pay_restriction_values rv
1811 WHERE EXISTS (SELECT ''
1812 FROM pay_customized_restrictions cr
1813 WHERE cr.customized_restriction_id
1814 = rv.customized_restriction_id
1815 AND cr.business_group_id = p_business_group_id);
1816 hr_utility.set_location(l_proc, 30);
1817 --
1818
1819 DELETE pay_custom_restrictions_tl crtl
1820 WHERE EXISTS (SELECT ''
1821 FROM pay_customized_restrictions cr
1822 WHERE cr.customized_restriction_id
1823 = crtl.customized_restriction_id
1824 AND cr.business_group_id = p_business_group_id);
1825 hr_utility.set_location(l_proc, 35);
1826
1827 ------
1828
1829 DELETE pay_customized_restrictions cr
1830 WHERE cr.business_group_id = p_business_group_id;
1831 hr_utility.set_location(l_proc, 40);
1832 --
1833 DELETE pay_user_column_instances_f uci
1834 WHERE uci.business_group_id = p_business_group_id;
1835 hr_utility.set_location(l_proc, 50);
1836 --
1837 DELETE pay_user_columns uc
1838 WHERE uc.business_group_id = p_business_group_id;
1839 hr_utility.set_location(l_proc, 60);
1840 --
1841 DELETE pay_user_rows_f ur
1842 WHERE ur.business_group_id = p_business_group_id;
1843 hr_utility.set_location(l_proc, 70);
1844 --
1845 DELETE pay_user_tables ut
1846 WHERE ut.business_group_id = p_business_group_id;
1847 hr_utility.set_location(l_proc, 80);
1848 --
1849 DELETE pay_element_classifications ec
1850 WHERE ec.business_group_id = p_business_group_id;
1851 hr_utility.set_location(l_proc, 90);
1852 --
1853 -- Check if a message lines exist for the business group
1854 --
1855 open csr_get_mess_lines(p_business_group_id);
1856 fetch csr_get_mess_lines into l_exists;
1857 if csr_get_mess_lines%found then
1858 --
1859 DELETE FROM pay_message_lines ml
1860 WHERE ml.source_type = 'B'
1861 AND ml.source_id = p_business_group_id;
1862 --
1863 end if;
1864 close csr_get_mess_lines;
1865 hr_utility.set_location(l_proc, 100);
1866 --
1867 DELETE FROM pay_freq_rule_periods pfr
1868 WHERE pfr.business_group_id = p_business_group_id;
1869 hr_utility.set_location(l_proc, 110);
1870 --
1871 DELETE FROM pay_ele_payroll_freq_rules pef
1872 WHERE pef.business_group_id = p_business_group_id;
1873 hr_utility.set_location(l_proc, 120);
1874 --
1875 FOR bahrec IN csr_batch_header(p_business_group_id) LOOP
1876 --
1877 payplnk.purge(bahrec.batch_id);
1878 --
1879 END LOOP;
1880 hr_utility.set_location(l_proc, 130);
1881 --
1882 DELETE FROM ben_benefit_contributions_f bbc
1883 WHERE bbc.business_group_id = p_business_group_id;
1884 --
1885 DELETE FROM pay_datetracked_events
1886 WHERE business_group_id = p_business_group_id;
1887 hr_utility.set_location(l_proc, 140);
1888 --
1889 DELETE FROM pay_event_groups
1890 WHERE business_group_id = p_business_group_id;
1891 hr_utility.set_location(l_proc, 150);
1892 --
1893 DELETE FROM pay_event_procedures
1894 WHERE business_group_id = p_business_group_id;
1895 hr_utility.set_location(l_proc, 155);
1896 --
1897 DELETE FROM pay_event_updates
1898 WHERE business_group_id = p_business_group_id;
1899 hr_utility.set_location(l_proc, 160);
1900 --
1901 DELETE FROM pay_dated_tables
1902 WHERE business_group_id = p_business_group_id;
1903 --
1904 hr_utility.set_location('Leaving: '||l_proc, 200);
1905 end delete_pay_misc;
1906 --
1907 Procedure delete_qun_misc(p_business_group_id number)
1908 IS
1909 --
1910 l_proc varchar2(80) := g_package || 'delete_qun_misc';
1911 --
1912 BEGIN
1913 --
1914 hr_utility.set_location('Entering: '||l_proc,10);
1915 --
1916 DELETE from hr_quest_answer_values qsv
1917 WHERE qsv.quest_answer_val_id in (
1918 SELECT qv.quest_answer_val_id
1919 FROM hr_quest_answer_values qv
1920 , hr_quest_answers qa
1921 WHERE qa.questionnaire_answer_id = qv.questionnaire_answer_id
1922 AND qa.business_group_id = P_BUSINESS_GROUP_ID);
1923 --
1924 hr_utility.set_location(l_proc,20);
1925 --
1926 DELETE from hr_quest_answers qsa
1927 WHERE qsa.business_group_id = P_BUSINESS_GROUP_ID;
1928 --
1929 hr_utility.set_location(l_proc,30);
1930 --
1931 DELETE from hr_quest_fields qsf
1932 WHERE qsf.field_id in (
1933 SELECT qf.field_id
1934 FROM hr_quest_fields qf
1935 , hr_questionnaires qn
1936 WHERE qf.questionnaire_template_id = qn.questionnaire_template_id
1937 AND qn.business_group_id = P_BUSINESS_GROUP_ID);
1938 --
1939 hr_utility.set_location(l_proc,40);
1940 --
1941 DELETE from hr_questionnaires qsn
1942 WHERE qsn.business_group_id = P_BUSINESS_GROUP_ID;
1943 --
1944 hr_utility.set_location(l_proc,50);
1945 --
1946 DELETE from per_participants par
1947 WHERE par.business_group_id = P_BUSINESS_GROUP_ID;
1948 --
1949 hr_utility.set_location(l_proc,60);
1950 --
1951 DELETE from per_appraisals apr
1952 WHERE apr.business_group_id = P_BUSINESS_GROUP_ID;
1953 --
1954 hr_utility.set_location('Leaving: '||l_proc,70);
1955 --
1956 END;
1957 --
1958 PROCEDURE delete_org_direct(p_business_group_id NUMBER,
1959 p_rt_running in VARCHAR2 default 'N')
1960 IS
1961 --
1962 l_proc varchar2(80) := g_package||'delete_org_direct';
1963 --
1964 l_exists varchar2(1);
1965 --
1966 l_organization_id hr_organization_units.organization_id%TYPE;
1967 --
1968 l_security_group_id per_business_groups.security_group_id%TYPE;
1969 --
1970 cursor csr_get_ost
1971 (c_business_group_id
1972 PER_ORGANIZATION_STRUCTURES.business_group_id%TYPE)
1973 is
1974 SELECT null
1975 FROM PER_ORGANIZATION_STRUCTURES
1976 WHERE business_group_id = c_business_group_id;
1977 --
1978 cursor deltype (p_sec_grp in number) is
1979 select lookup_type
1980 from fnd_lookup_types
1981 where security_group_id = p_sec_grp;
1982 --
1983 begin
1984 hr_utility.set_location('Entering: '||l_proc, 10);
1985 --
1986 if p_rt_running = 'N' then
1987 --
1988 select security_group_id
1989 into l_security_group_id
1990 from per_business_groups
1991 where business_group_id = p_business_group_id;
1992 --
1993 hr_utility.set_location('Entering: '||l_proc, 13);
1994 --
1995 DELETE FROM hr_organization_information hoi
1996 WHERE
1997 EXISTS
1998 (SELECT ''
1999 FROM hr_organization_units hou
2000 WHERE hou.organization_id = hoi.organization_id
2001 AND hou.business_group_id = p_business_group_id);
2002 --
2003 hr_utility.set_location('Entering: '||l_proc, 14);
2004 --
2005 --
2006 -- Check if the enable_security_groups profile is set to 'Y'
2007 -- for any applications - if it is then delete the security
2008 -- group - if not, then leave it
2009 --
2010 DECLARE
2011 CURSOR c_sg_enabled
2012 IS
2013 SELECT 'Y'
2014 FROM fnd_profile_options po
2015 ,fnd_profile_option_values pov
2016 WHERE po.profile_option_name = 'ENABLE_SECURITY_GROUPS'
2017 AND po.profile_option_id = pov.profile_option_id
2018 AND po.application_id = pov.application_id
2019 AND pov.level_id = 10002
2020 AND pov.profile_option_value = 'Y'
2021 AND to_number(pov.level_value) BETWEEN 800 AND 900;
2022 --
2023 l_sg_enabled BOOLEAN DEFAULT FALSE;
2024 --
2025 BEGIN
2026 OPEN c_sg_enabled;
2027 --
2028 FETCH c_sg_enabled INTO l_exists;
2029 --
2030 IF c_sg_enabled%FOUND THEN
2031 l_sg_enabled := TRUE;
2032 ELSE
2033 l_sg_enabled := FALSE;
2034 END IF;
2035 --
2036 CLOSE c_sg_enabled;
2037 --
2038 IF l_sg_enabled AND l_security_group_id > 0 THEN
2039 for typrec in deltype (l_security_group_id) loop
2040 --
2041 DELETE FROM fnd_lookup_values
2042 WHERE security_group_id = l_security_group_id
2043 AND lookup_type = typrec.lookup_type;
2044 --
2045 DELETE FROM fnd_lookup_types_tl
2046 WHERE security_group_id = l_security_group_id
2047 AND lookup_type = typrec.lookup_type;
2048 --
2049 DELETE FROM fnd_lookup_types
2050 WHERE security_group_id = l_security_group_id
2051 AND lookup_type = typrec.lookup_type;
2052 --
2053 end loop;
2054 --
2055 hr_utility.set_location(l_proc, 15);
2056 --
2057 DELETE FROM fnd_security_groups_tl
2058 WHERE security_group_id = l_security_group_id;
2059 --
2060 hr_utility.set_location('Entering: '||l_proc, 16);
2061 --
2062 DELETE FROM fnd_security_groups
2063 WHERE security_group_id = l_security_group_id;
2064 --
2065 END IF;
2066 END;
2067 end if;
2068 --
2069 hr_utility.set_location(l_proc, 20);
2070 --
2071 -- Check if an organization structure exists for the
2072 -- business group
2073 --
2074 open csr_get_ost(p_business_group_id);
2075 fetch csr_get_ost into l_exists;
2076 if csr_get_ost%found then
2077 --
2078 DELETE per_org_structure_elements
2079 WHERE business_group_id = p_business_group_id;
2080 hr_utility.set_location(l_proc, 30);
2081 --
2082 DELETE per_org_structure_versions
2083 WHERE business_group_id = p_business_group_id;
2084 hr_utility.set_location(l_proc, 40);
2085 --
2086 DELETE per_organization_structures
2087 WHERE business_group_id = p_business_group_id;
2088 hr_utility.set_location(l_proc, 50);
2089 --
2090 end if;
2091 close csr_get_ost;
2092 hr_utility.set_location(l_proc, 60);
2093 --
2094 if p_rt_running = 'N' then
2095 DELETE hr_organization_units
2096 WHERE business_group_id = p_business_group_id
2097 AND organization_id <> p_business_group_id;
2098 end if;
2099 hr_utility.set_location('Leaving: '||l_proc, 100);
2100 end delete_org_direct;
2101 --
2102 --
2103 PROCEDURE delete_bg_misc(p_business_group_id NUMBER)
2104 IS
2105 --
2106 -- Bug fix required for 10,5 stand alone
2107 -- make delete from financials_system_parameters dynamic plsql.
2108 --
2109 -- Cursor to find out which of the financials_system_parameters
2110 -- tables is available
2111 -- FINANCIALS_SYSTEM_PARAMS_ALL (10.6 install)
2112 -- FINANCIALS_SYSTEM_PARAMETERS (10.5 HR + other apps install)
2113 -- none (10.5 HR only install)
2114 --The ORDER BY clause ensures we pick up FINANCIALS_SYSTEM_PARAMS_ALL
2115 --if it's there, ahead of FINANCIALS_SYSTEM_PARAMETERS.
2116 --
2117 cursor fsp_table_name is
2118 select table_name
2119 from user_catalog
2120 where table_name in ('FINANCIALS_SYSTEM_PARAMS_ALL',
2121 'FINANCIALS_SYSTEM_PARAMETERS')
2122 order by table_name desc;
2123 --
2124 l_fsp_table_name varchar2(30);
2125 l_sql_text varchar2(2000);
2126 l_sql_cursor number;
2127 l_rows_processed number;
2128 --
2129 begin
2130 --
2131 hr_utility.set_location('hr_delete.delete_bg_misc',1);
2132 hr_utility.set_location('hr_delete.delete_bg_misc',2);
2133 DELETE per_letter_gen_statuses
2134 WHERE business_group_id = p_business_group_id;
2135 --
2136 hr_utility.set_location('hr_delete.delete_bg_misc',4);
2137 --
2138 -- Get table name if it exists.
2139 --
2140 open fsp_table_name;
2141 fetch fsp_table_name into l_fsp_table_name;
2142 if fsp_table_name%found then
2143 close fsp_table_name;
2144 --
2145 hr_utility.set_location('hr_delete.delete_bg_misc',7);
2146 --
2147 -- Define the dynamic cursor.
2148 --
2149 l_sql_text := 'delete from '
2150 || l_fsp_table_name
2151 || ' where business_group_id = '
2152 || to_char (p_business_group_id);
2153 --
2154 -- Open Cursor for Processing Sql statment.
2155 --
2156 l_sql_cursor := dbms_sql.open_cursor;
2157 --
2158 hr_utility.set_location('hr_delete.delete_bg_misc',8);
2159 --
2160 -- Parse SQL statement.
2161 --
2162 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
2163 --
2164 hr_utility.set_location('hr_delete.delete_bg_misc',9);
2165 --
2166 -- Execute the sql
2167 --
2168 l_rows_processed := dbms_sql.execute(l_sql_cursor);
2169 --
2170 hr_utility.set_location('hr_delete.delete_bg_misc',10);
2171 --
2172 -- Close cursor.
2173 --
2174 dbms_sql.close_cursor(l_sql_cursor);
2175 --
2176 hr_utility.set_location('hr_delete.delete_bg_misc',11);
2177 --
2178 --
2179 else
2180 close fsp_table_name;
2181 --
2182 hr_utility.set_location('hr_delete.delete_bg_misc',12);
2183 end if;
2184 end delete_bg_misc;
2185 --
2186 PROCEDURE delete_upg_details(p_business_group_id NUMBER)
2187 IS
2188 --
2189 begin
2190 --
2191 hr_utility.set_location('hr_delete.p_business_group_id',1);
2192 --
2193 delete from pay_upgrade_status
2194 where business_group_id = p_business_group_id;
2195 --
2196 hr_utility.set_location('hr_delete.p_business_group_id',2);
2197 --
2198 end delete_upg_details;
2199 --
2200 -- ----------------------------------------------------------------------------
2201 -- |-------------------< delete_security_list_for_bg >-------------------------|
2202 -- ----------------------------------------------------------------------------
2203 PROCEDURE delete_security_list_for_bg(p_business_group_id NUMBER)
2204 IS
2205 --
2206 l_proc varchar2(80) := g_package||'delete_security_list_for_bg';
2207 --
2208 -- DK 16-SEP-1996 Enabled use of business group index
2209 -- In development there are lots of business groups
2210 -- and otherwise it is not a very high cost.
2211 CURSOR pev IS
2212 SELECT pp.person_id
2213 FROM per_people_f pp,
2214 per_person_list pl
2215 WHERE pp.person_id = pl.person_id
2216 AND pp.business_group_id = p_business_group_id;
2217 BEGIN
2218 --
2219 hr_utility.set_location(l_proc,20);
2220 --
2221 DELETE FROM pay_security_payrolls psp
2222 WHERE psp.business_group_id = p_business_group_id;
2223 hr_utility.set_location(l_proc,30);
2224 --
2225 DELETE FROM pay_payroll_list ppl
2226 WHERE EXISTS ( SELECT ''
2227 FROM pay_payrolls_f pay
2228 WHERE pay.payroll_id = ppl.payroll_id
2229 AND pay.business_group_id = p_business_group_id);
2230 hr_utility.set_location(l_proc,40);
2231 --
2232 FOR pevrec IN pev LOOP
2233 DELETE FROM per_person_list pl
2234 WHERE pl.person_id = pevrec.person_id;
2235 END LOOP;
2236 hr_utility.set_location(l_proc,50);
2237 --
2238 -- Changes 02-Oct-99 SCNair (per_positions to hr_all_positions_f) date track position req.
2239 --
2240 DELETE FROM per_position_list pol
2241 WHERE EXISTS ( SELECT ''
2242 FROM hr_all_positions_f pos
2243 WHERE pos.position_id = pol.position_id
2244 AND pos.business_group_id = p_business_group_id);
2245 --
2246 hr_utility.set_location('hr_delete.delete_security_list_for_bg',6);
2247
2248 -- Bug fix 3622082.
2249 -- Delete statement modified to improve performance.
2250
2251 DELETE FROM per_organization_list ol
2252 WHERE ol.organization_id IN ( SELECT ou.organization_id
2253 FROM hr_all_organization_units ou
2254 WHERE ou.business_group_id = p_business_group_id);
2255 --
2256 hr_utility.set_location('hr_delete.delete_security_list_for_bg',7);
2257 DELETE FROM per_security_profiles psp
2258 WHERE psp.business_group_id = p_business_group_id
2259 AND psp.view_all_flag = 'N';
2260 --
2261 hr_utility.set_location('hr_delete.delete_security_list_for_bg',8);
2262 DELETE FROM per_security_organizations pso
2263 WHERE pso.organization_id IN ( SELECT ou.organization_id
2264 FROM hr_all_organization_units ou
2265 WHERE ou.business_group_id = p_business_group_id);
2266 --
2267 hr_utility.set_location('hr_delete.delete_security_list_for_bg',9);
2268 DELETE FROM per_security_users psu
2269 WHERE psu.security_profile_id IN (SELECT sp.security_profile_id
2270 FROM per_security_profiles sp
2271 WHERE sp.business_group_id = p_business_group_id);
2272 --
2273 END delete_security_list_for_bg;
2274 --
2275 -- The p_preserve_org_information parameter allows this procedure to
2276 -- be called without deleting the org information and org structures
2277 -- that have been inserted. i.e. this preserves the essential
2278 -- business group information from being deleted. This is important
2279 -- in at least one testing application.
2280 PROCEDURE delete_below_bg(p_business_group_id NUMBER,
2281 p_preserve_org_information in VARCHAR2 default 'N',
2282 p_rt_running in VARCHAR2 default 'N')
2283 IS
2284 --
2285 l_proc varchar2(80) := g_package||'delete_below_bg';
2286 --
2287 l_exists varchar2(1);
2288 --
2289 begin
2290 hr_utility.set_location('Entering: '||l_proc,5);
2291 delete_run_types(p_business_group_id);
2292 --
2293 hr_utility.set_location(l_proc,10);
2294 delete_security_list_for_bg(p_business_group_id);
2295 hr_utility.set_location(l_proc,20);
2296 --
2297 delete_retro_details(p_business_group_id);
2298 hr_utility.set_location(l_proc,25);
2299 --
2300 delete_mag_structure(p_business_group_id);
2301 hr_utility.set_location(l_proc,30);
2302 --
2303 delete_bal_load_struct(p_business_group_id);
2304 hr_utility.set_location(l_proc,40);
2305 --
2306 delete_formula_direct(p_business_group_id);
2307 hr_utility.set_location(l_proc,50);
2308 --
2309 delete_assign_low_detail(p_business_group_id);
2310 hr_utility.set_location(l_proc,60);
2311 --
2312 delete_assign_detail(p_business_group_id);
2313 hr_utility.set_location(l_proc,70);
2314 --
2315 delete_database_items(p_business_group_id);
2316 hr_utility.set_location(l_proc,80);
2317 --
2318 delete_assign_direct(p_business_group_id);
2319 hr_utility.set_location(l_proc,90);
2320 --
2321 delete_grade_direct(p_business_group_id);
2322 hr_utility.set_location(l_proc,100);
2323 --
2324 delete_job_direct(p_business_group_id);
2325 hr_utility.set_location(l_proc,110);
2326 --
2327 delete_person_direct(p_business_group_id);
2328 hr_utility.set_location(l_proc,140);
2329 --
2330 delete_per_misc(p_business_group_id);
2331 hr_utility.set_location(l_proc,150);
2332 --
2333 delete_element_direct(p_business_group_id);
2334 hr_utility.set_location(l_proc,160);
2335 --
2336 delete_org_low_detail(p_business_group_id);
2337 hr_utility.set_location(l_proc,170);
2338 --
2339 delete_org_detail(p_business_group_id);
2340 hr_utility.set_location(l_proc,180);
2341 --
2342 delete_time_def_direct(p_business_group_id);
2343 hr_utility.set_location(l_proc,185);
2344 --
2345 delete_payroll_direct(p_business_group_id);
2346 hr_utility.set_location(l_proc,190);
2347 --
2348 delete_balance_direct(p_business_group_id);
2349 hr_utility.set_location(l_proc,200);
2350 --
2351 delete_pay_misc(p_business_group_id);
2352 hr_utility.set_location(l_proc,210);
2353 --
2354 delete_qun_misc(p_business_group_id);
2355 hr_utility.set_location(l_proc,215);
2356 --
2357 delete_upg_details(p_business_group_id);
2358 hr_utility.set_location(l_proc,217);
2359 --
2360 -- Caller can choose not to delete business group info.
2361 if p_preserve_org_information = 'N' then
2362 delete_org_direct(p_business_group_id,
2363 p_rt_running);
2364 hr_utility.set_location(l_proc,220);
2365 end if;
2366 hr_utility.set_location(l_proc,230);
2367 --
2368 delete_bg_misc(p_business_group_id);
2369 hr_utility.set_location('Leaving: '||l_proc,230);
2370 end delete_below_bg;
2371 --
2372 --
2373 end hr_delete;
[Home] [Help]