[Home] [Help]
PACKAGE BODY: APPS.HR_DM_DELETE
Source
1 PACKAGE BODY hr_dm_delete AS
2 /* $Header: perdmdel.pkb 120.1 2005/06/15 02:09:35 nhunur noship $ */
3
4
5
6 /*---------------------------- PUBLIC ROUTINES ----------------------------*/
7
8 -- ------------------------- set_active ------------------------
9 -- Description: The next group to be deleted is selected by finding the
10 -- first unprocessed group on the locking ladder and updating the table
11 -- hr_dm_migrations with its group id.
12 --
13 --
14 -- Input Parameters
15 -- p_migration_id - current migration
16 --
17 --
18 --
19 -- Output Parameters
20 -- <none>
21 --
22 --
23 -- ------------------------------------------------------------------------
24
25
26 --
27 PROCEDURE set_active(p_migration_id IN NUMBER) IS
28 --
29
30 l_active_group NUMBER;
31 l_delete_phase_id NUMBER;
32 l_range_phase_id NUMBER;
33
34
35 CURSOR csr_get_active IS
36 SELECT group_id
37 FROM hr_dm_application_groups
38 WHERE (migration_type = 'D')
39 AND (group_order IN (
40 SELECT MAX(apg.group_order)
41 FROM hr_dm_phase_items pi_dn,
42 hr_dm_tables tbl,
43 hr_dm_migration_ranges mr,
44 hr_dm_phase_items pi_rg,
45 hr_dm_table_groupings tgp,
46 hr_dm_application_groups apg
47 WHERE pi_rg.phase_id = l_range_phase_id
48 AND pi_rg.phase_item_id = mr.phase_item_id
49 AND pi_rg.table_name = tbl.table_name
50 AND pi_dn.phase_id = l_delete_phase_id
51 AND pi_dn.group_id = pi_rg.group_id
52 AND tbl.table_id = tgp.table_id
53 AND tgp.group_id = apg.group_id
54 AND apg.migration_type = 'D'
55 AND mr.status = 'NS')
56 );
57
58
59
60 --
61 BEGIN
62 --
63
64 hr_dm_utility.message('ROUT','entry:hr_dm_delete.set_active', 5);
65 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id || ')',
66 10);
67
68
69 -- get the delete phase_id
70 l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
71
72 -- get the range phase_id
73 l_range_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
74
75
76
77 -- get group id of next (or first) group to be processed
78 OPEN csr_get_active;
79 FETCH csr_get_active INTO l_active_group;
80 CLOSE csr_get_active;
81
82 -- update hr_dm_migrations with active group
83 UPDATE hr_dm_migrations
84 SET active_group = l_active_group
85 WHERE migration_id = p_migration_id;
86
87 COMMIT;
88
89
90
91 hr_dm_utility.message('INFO','Currently Active group set', 15);
92 hr_dm_utility.message('SUMM','Currently Active group set', 20);
93 hr_dm_utility.message('ROUT','exit:hr_dm_delete.set_active', 25);
94 hr_dm_utility.message('PARA','(none)', 30);
95
96 -- error handling
97 EXCEPTION
98 WHEN OTHERS THEN
99 hr_dm_utility.error(SQLCODE,'hr_dm_delete.set_active',
100 'Error setting currently active group','D');
101 RAISE;
102
103 --
104 END set_active;
105 --
106
107
108 -- ------------------------- del_fnd_info ------------------------
109 -- Description: For the business group being deleted, the following
110 -- items are removed via FND APIs:
111 --
112 -- * Local lookups
113 -- * FND_SECURITY_GROUPS
114 -- * FND_USER_RESP_GROUPS
115 --
116 -- Input Parameters
117 -- p_business_group_id - business_group_id to delete
118 --
119 --
120 --
121 -- Output Parameters
122 -- <none>
123 --
124 --
125 -- ------------------------------------------------------------------------
126
127
128 --
129 PROCEDURE del_fnd_info(p_business_group_id IN NUMBER) IS
130 --
131
132 l_security_group_id NUMBER;
133 l_lookup_type VARCHAR2(30);
134 l_lookup_code VARCHAR2(30);
135 l_view_application_id NUMBER(15);
136 l_username VARCHAR2(100);
137 l_resp_app VARCHAR2(50);
138 l_resp_key VARCHAR2(30);
139 l_security_group VARCHAR2(30);
140
141 CURSOR csr_sec_grp IS
142 SELECT security_group_id
143 FROM per_business_groups
144 WHERE business_group_id = p_business_group_id;
145
146 CURSOR csr_lu_type IS
147 SELECT lookup_type,
148 view_application_id
149 FROM fnd_lookup_types
150 WHERE security_group_id = l_security_group_id;
151
152 CURSOR csr_lu_code IS
153 SELECT DISTINCT lookup_code
154 FROM FND_LOOKUP_VALUES
155 WHERE security_group_id = l_security_group_id
156 AND lookup_type = l_lookup_type
157 AND view_application_id = l_view_application_id;
158
159 CURSOR csr_usrresgrp IS
160 SELECT u.user_name username,
161 a.application_short_name resp_app,
162 r.responsibility_key resp_key,
163 s.security_group_key security_group
164 FROM fnd_user_resp_groups rg,
165 fnd_user u,
166 fnd_application a,
167 fnd_responsibility r,
168 fnd_security_groups s
169 WHERE rg.user_id = u.user_id
170 AND rg.responsibility_application_id = a.application_id
171 AND rg.responsibility_id = r.responsibility_id
172 AND rg.security_group_id = s.security_group_id
173 AND rg.security_group_id = l_security_group_id;
174
175 --
176 BEGIN
177 --
178
179 hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');
180 hr_utility.trace_on;
181
182 -- find the security group id
183 OPEN csr_sec_grp;
184 FETCH csr_sec_grp INTO l_security_group_id;
185 CLOSE csr_sec_grp;
186
187 -- only delete when not using standard security group
188 IF (l_security_group_id <> 0) THEN
189
190 -- delete lookups
191
192 -- find the lookup type
193 OPEN csr_lu_type;
194 LOOP
195 FETCH csr_lu_type INTO l_lookup_type,
196 l_view_application_id;
197 EXIT WHEN csr_lu_type%NOTFOUND;
198
199 hr_utility.trace('Deleting lookup type ' || l_lookup_type ||
200 ' (' || l_view_application_id ||
201 ') and associated lookup codes');
202
203 -- find the codes to delete and delete them
204 OPEN csr_lu_code;
205 LOOP
206 FETCH csr_lu_code INTO l_lookup_code;
207 EXIT WHEN csr_lu_code%NOTFOUND;
208
209 -- call API to delete lookup code / value
210 fnd_lookup_values_pkg.delete_row(
211 X_LOOKUP_TYPE => l_lookup_type,
212 X_SECURITY_GROUP_ID => l_security_group_id,
213 X_VIEW_APPLICATION_ID => l_view_application_id,
214 X_LOOKUP_CODE => l_lookup_code);
215
216 END LOOP;
217 CLOSE csr_lu_code;
218
219 -- now delete the lookup type
220 fnd_lookup_types_pkg.delete_row(
221 X_LOOKUP_TYPE => l_lookup_type,
222 X_SECURITY_GROUP_ID => l_security_group_id,
223 X_VIEW_APPLICATION_ID => l_view_application_id);
224
225 COMMIT;
226
227 END LOOP;
228 CLOSE csr_lu_type;
229
230
231 -- delete FND_USER_RESP_GROUPS
232
233 OPEN csr_usrresgrp;
234 LOOP
235 FETCH csr_usrresgrp INTO
236 l_username,
237 l_resp_app,
238 l_resp_key,
239 l_security_group;
240
241 EXIT WHEN csr_usrresgrp%NOTFOUND;
242
243 hr_utility.trace('Deleting user/resp (' ||
244 l_username || '/' ||
245 l_resp_app || '/' ||
246 l_resp_key || '/' ||
247 l_security_group || ')');
248
249 fnd_user_pkg.DelResp(username => l_username,
250 resp_app => l_resp_app,
251 resp_key => l_resp_key,
252 security_group => l_security_group);
253
254 COMMIT;
255
256 END LOOP;
257 CLOSE csr_usrresgrp;
258
259
260 -- delete FND_SECURITY_GROUPS
261 fnd_security_groups_pkg.delete_row(l_security_group_id);
262
263
264 END IF;
265
266
267 -- error handling
268 EXCEPTION
269 WHEN OTHERS THEN
270 hr_utility.trace('An error occurred whilst deleting fnd information:');
271 hr_utility.trace(SQLERRM || ' in hr_dm_delete.del_fnd_info');
272 RAISE;
273
274 --
275 END del_fnd_info;
276 --
277
278
279 -- ------------------------- main ------------------------
280 -- Description: This is the delete phase slave. It reads an item from the
281 -- hr_dm_migration_ranges table that is applicable for the current group.
282 -- The data is then deleted using the appropriate TDS package.
283 --
284 -- When there are no more items left for the currently active group, the
285 -- process pauses until all the threads that are processing the group have
286 -- finished and then the next group to be processed is selected.
287 --
288 --
289 -- Input Parameters
290 -- p_migration_id - of current migration
291 --
292 -- p_concurrent_process - Y if program called from CM, otherwise
293 -- N prevents message logging
294 --
295 -- p_last_migration_date - date of last sucessful migration
296 --
297 -- p_process_number - the slave number to allow implicit locking
298 --
299 --
300 -- Output Parameters
301 -- errbuf - buffer for output message (for CM manager)
302 --
303 -- retcode - program return code (for CM manager)
304 --
305 --
306 -- ------------------------------------------------------------------------
307
308
309 --
310 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
311 retcode OUT NOCOPY NUMBER,
312 p_migration_id IN NUMBER,
313 p_concurrent_process IN VARCHAR2 DEFAULT 'Y',
314 p_last_migration_date IN DATE,
315 p_process_number IN NUMBER
316 ) IS
317 --
318
319 l_current_phase_status VARCHAR2(30);
320 l_delete_phase_id NUMBER;
321 l_range_phase_id NUMBER;
322 e_fatal_error EXCEPTION;
323 l_fatal_error_message VARCHAR2(200);
324 l_table_name VARCHAR2(30);
325 l_short_name VARCHAR2(30);
326 l_status VARCHAR2(30);
327 l_phase_item_id NUMBER;
328 l_business_group_id NUMBER;
329 l_migration_type VARCHAR2(30);
330 l_string VARCHAR2(500);
331 l_group_id NUMBER;
332 l_no_of_threads NUMBER;
333
334 l_chunk_size NUMBER;
335 l_dummy NUMBER;
336 l_active_group NUMBER;
337 l_group_work NUMBER := -1;
338 l_range_id NUMBER;
339 l_starting_process_sequence NUMBER;
340 l_ending_process_sequence NUMBER;
341
342
343 CURSOR csr_migration_info IS
344 SELECT business_group_id, migration_type
345 FROM hr_dm_migrations
346 WHERE migration_id = p_migration_id;
347
348 CURSOR csr_active_group IS
349 SELECT active_group
350 FROM hr_dm_migrations
351 WHERE migration_id = p_migration_id;
352
353 CURSOR csr_group_work IS
354 SELECT group_id
355 FROM hr_dm_application_groups
356 WHERE (migration_type = 'D')
357 AND (group_order IN (
358 SELECT MAX(apg.group_order)
359 FROM hr_dm_phase_items pi_dn,
360 hr_dm_tables tbl,
361 hr_dm_migration_ranges mr,
362 hr_dm_phase_items pi_rg,
363 hr_dm_table_groupings tgp,
364 hr_dm_application_groups apg
365 WHERE pi_rg.phase_id = l_range_phase_id
366 AND pi_rg.phase_item_id = mr.phase_item_id
367 AND pi_rg.table_name = tbl.table_name
368 AND pi_dn.phase_id = l_delete_phase_id
369 AND pi_dn.group_id = pi_rg.group_id
370 AND tbl.table_id = tgp.table_id
371 AND tgp.group_id = apg.group_id
372 AND apg.migration_type = 'D'
373 AND mr.status ='NS')
374 );
375
376 CURSOR csr_table_range IS
377 SELECT mr.range_id,
378 tbl.table_name,
379 tbl.short_name,
380 mr.starting_process_sequence,
381 mr.ending_process_sequence,
382 pi_del.phase_item_id
383 FROM hr_dm_phase_items pi_del,
384 hr_dm_tables tbl,
385 hr_dm_migration_ranges mr,
386 hr_dm_phase_items pi_rg,
387 hr_dm_table_groupings tgp
388 WHERE pi_rg.phase_id = l_range_phase_id
389 AND pi_rg.phase_item_id = mr.phase_item_id
390 AND mr.status = 'NS'
391 AND pi_rg.table_name = tbl.table_name
392 AND pi_del.phase_id = l_delete_phase_id
393 AND pi_del.group_id = pi_rg.group_id
394 AND tgp.table_id = tbl.table_id
395 AND tgp.group_id = l_active_group
396 AND ((MOD(mr.range_id, l_no_of_threads) + 1) = p_process_number);
397
398
399 --
400 BEGIN
401 --
402
403 -- initialize messaging (only for concurrent processing)
404 IF (p_concurrent_process = 'Y') THEN
405 hr_dm_utility.message_init;
406 END IF;
407
408 hr_dm_utility.message('ROUT','entry:hr_dm_delete.main', 5);
409 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
410 ')(p_last_migration_date - ' ||
411 p_last_migration_date || ')', 10);
412
413
414 -- Set the variable so as to disable the trigger on the table.
415 hr_general.g_data_migrator_mode := 'Y';
416
417 -- set the profile to disable the audit trigger
418 fnd_profile.put (name => 'AUDITTRAIL:ACTIVATE'
419 ,val => 'N'
420 );
421
422 -- get the delete phase_id
423 l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
424
425 -- get the range phase_id
426 l_range_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
427
428
429 -- get the business_group_id and migration_type
430 OPEN csr_migration_info;
431 LOOP
432 FETCH csr_migration_info INTO l_business_group_id, l_migration_type;
433 EXIT WHEN csr_migration_info%NOTFOUND;
434 END LOOP;
435 CLOSE csr_migration_info;
436
437 -- get the number of threads to enable modulus locking
438 l_no_of_threads := hr_dm_utility.number_of_threads(l_business_group_id);
439
440 -- find the chunk size
441 l_chunk_size := hr_dm_utility.chunk_size(l_business_group_id);
442
443
444 -- loop until either delete phase is in error or all delete phase items have
445 -- been processed
446 hr_dm_utility.message('INFO','loop start', 15);
447 LOOP
448
449 -- get status of delete phase, is phase completed?
450 -- if null returned, then assume it is NS.
451 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('D',
452 p_migration_id), 'NS');
453
454 -- if status is error, then raise an exception
455 IF (l_current_phase_status = 'E') THEN
456 l_fatal_error_message := 'Current phase in error - slave exiting';
457 RAISE e_fatal_error;
458 END IF;
459
460 -- get currently active group
461 OPEN csr_active_group;
462 FETCH csr_active_group INTO l_active_group;
463 CLOSE csr_active_group;
464 hr_dm_utility.message('INFO','active group is ' || l_active_group, 15);
465
466 -- fetch a row to process
467 l_table_name := NULL;
468 OPEN csr_table_range;
469 FETCH csr_table_range INTO l_range_id, l_table_name, l_short_name,
470 l_starting_process_sequence,
471 l_ending_process_sequence,
472 l_phase_item_id;
473 CLOSE csr_table_range;
474
475 IF l_table_name IS NOT NULL THEN
476 hr_dm_utility.message('INFO','deleting - ' || l_table_name, 11);
477 -- update status to started
478 hr_dm_utility.update_migration_ranges(p_new_status => 'S',
479 p_id => l_range_id);
480 --
481 -- call delete function...
482 --
486 l_starting_process_sequence || ''', ' ||
483 -- build parameter string
484 l_string := 'begin hrdmd_' || l_short_name || '.delete_source( ' ||
485 l_business_group_id || ', ''' ||
487 l_ending_process_sequence || ', ' ||
488 l_chunk_size || '); end;';
489 hr_dm_utility.message('INFO','using - ' || l_string, 12);
490 EXECUTE IMMEDIATE l_string;
491
492 -- update status to completed
493 hr_dm_utility.update_migration_ranges(p_new_status => 'C',
494 p_id => l_range_id);
495 COMMIT;
496
497 ELSE
498 -- no rows left to process in this group
499 -- check if all tables in this group have either been completed or errored
500 -- ie all slaves have completed work on this group
501 LOOP
502 hr_dm_utility.message('INFO','seeing if work on this group to do.', 15);
503
504 OPEN csr_group_work;
505 FETCH csr_group_work INTO l_group_work;
506 IF csr_group_work%NOTFOUND THEN
507 l_group_work := NULL;
508 END IF;
509 CLOSE csr_group_work;
510
511 -- set new active group
512 hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
513 hr_dm_utility.message('INFO','l_active_group - ' || l_active_group, 15);
514 IF l_group_work <> l_active_group THEN
515 hr_dm_utility.message('INFO','setting new active group', 15);
516 set_active(p_migration_id);
517 COMMIT;
518 ELSE
519 hr_dm_utility.message('INFO','Waiting for other slaves to finish this' ||
520 ' group', 13);
521 -- sleep for 5 seconds to allow other slaves to finish
522 -- read from a non-existant pipe, using time out feature to
523 -- give delay time
524 l_dummy := dbms_pipe.receive_message('temporary_unused_hrdm_pipe', 5);
525 END IF;
526
527 EXIT WHEN NVL(l_group_work,0) <> l_active_group
528 OR NVL(hr_dm_utility.get_phase_status('D',p_migration_id), 'NS') = 'E'
529 OR l_group_work IS NULL;
530
531 END LOOP;
532 END IF;
533
534 l_status := NVL(hr_dm_utility.get_phase_status('D', p_migration_id), 'NS');
535
536 hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
537 hr_dm_utility.message('INFO','l_status - ' || l_status, 15);
538 EXIT WHEN l_status IN ('C','E')
539 OR l_group_work IS NULL;
540
541 COMMIT;
542 END LOOP;
543
544
545 -- set up return values to concurrent manager
546 retcode := 0;
547 errbuf := 'No errors - examine logfiles for detailed reports.';
548
549
550 hr_dm_utility.message('INFO','delete - slave process', 15);
551 hr_dm_utility.message('SUMM','delete - slave process', 20);
552 hr_dm_utility.message('ROUT','exit:hr_dm_delete.main', 25);
553 hr_dm_utility.message('PARA','(retcode - ' || retcode ||
554 ')(errbuf - ' || errbuf || ')', 30);
555
556 -- error handling
557 EXCEPTION
558 WHEN e_fatal_error THEN
559 retcode := 0;
560 errbuf := 'An error occurred during the migration - examine logfiles for '
561 || 'detailed reports.';
562 hr_dm_utility.error(SQLCODE,'hr_dm_delete.main',l_fatal_error_message,'R');
563 WHEN OTHERS THEN
564 retcode := 2;
565 errbuf := 'An error occurred during the migration - examine logfiles for '
566 || 'detailed reports.';
567 -- update status to error
568 hr_dm_utility.update_phase_items(p_new_status => 'E',
569 p_id => l_phase_item_id);
570 hr_dm_utility.error(SQLCODE,'hr_dm_delete.main','(none)','R');
571
572 --
573 END main;
574 --
575
576
577
578 -- ------------------------- pre_delete_process ------------------------
579 -- Description: This procedure writes out a sql script to delete the entries
580 -- that the are striped by business_group_id (directly or indirectly) that
581 -- the DM can not delete itself.
582 --
583 --
584 -- Input Parameters
585 -- r_migration_data - migration record
586 --
587 --
588 -- Output Parameters
589 -- <none>
590 --
591 --
592 -- ------------------------------------------------------------------------
593
594 --
595 PROCEDURE pre_delete_process(r_migration_data IN
596 hr_dm_utility.r_migration_rec) IS
597 --
598
599 l_location VARCHAR2(2000);
600 l_aol_filename VARCHAR2(30);
601 l_filehandle UTL_FILE.FILE_TYPE;
602 l_sysdate VARCHAR2(30);
603 e_fatal_error EXCEPTION;
604 l_fatal_error_message VARCHAR2(200);
605
606 --
607 BEGIN
608 --
609
610 hr_dm_utility.message('ROUT','entry:hr_dm_delete.pre_delete_process', 5);
611 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
612
613
614
615 -- find logfile directory and open file for delete script
616 fnd_profile.get('UTL_FILE_LOG', l_location);
617 l_aol_filename := 'DM' || r_migration_data.migration_id || '.sql';
618 hr_dm_utility.message('INFO','l_location ' || l_location, 13);
619 hr_dm_utility.message('INFO','l_aol_filename ' || l_aol_filename, 13);
620
621 IF l_location IS NULL THEN
622 l_fatal_error_message := 'The profile named Stored Procedure Log ' ||
626 RAISE e_fatal_error;
623 'Directory has not been set. Set to a ' ||
624 'valid location where the database can ' ||
625 'write files to.';
627 END IF;
628
629
630 l_filehandle := utl_file.fopen(l_location, l_aol_filename, 'w');
631
632 -- add header info
633 utl_file.put_line(l_filehandle, 'REM');
634 utl_file.put_line(l_filehandle, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK;');
635 utl_file.put_line(l_filehandle, 'WHENEVER OSERROR EXIT FAILURE ROLLBACK;');
636 utl_file.put_line(l_filehandle, 'REM');
637 utl_file.put_line(l_filehandle, 'REM Data Migrator Delete Script');
638 utl_file.put_line(l_filehandle, 'REM ');
639 utl_file.put_line(l_filehandle, 'REM Migration ID ' || r_migration_data.migration_id);
640 SELECT to_char(sysdate,'HH:MI DD-MON-YYYY')
641 INTO l_sysdate
642 FROM dual;
643 utl_file.put_line(l_filehandle, 'REM Date ' || l_sysdate);
644 utl_file.put_line(l_filehandle, 'REM ');
645 utl_file.put_line(l_filehandle, 'REM ');
646
647 utl_file.put_line(l_filehandle, '--');
648 utl_file.put_line(l_filehandle, 'BEGIN');
649 utl_file.put_line(l_filehandle, '--');
650
651 utl_file.put_line(l_filehandle, '-- local lookups, security info');
652 utl_file.put_line(l_filehandle, 'hr_dm_delete.del_fnd_info(' ||
653 r_migration_data.business_group_id || ');');
654 utl_file.put_line(l_filehandle, '');
655
656 utl_file.put_line(l_filehandle, '');
657 utl_file.put_line(l_filehandle, '-- business group info');
658
659 utl_file.put_line(l_filehandle, 'delete from HR_ALL_ORGANIZATION_UNITS_TL');
660 utl_file.put_line(l_filehandle, 'where ORGANIZATION_ID in (');
661 utl_file.put_line(l_filehandle, ' select ORGANIZATION_ID');
662 utl_file.put_line(l_filehandle, ' from HR_ALL_ORGANIZATION_UNITS');
663 utl_file.put_line(l_filehandle, ' where BUSINESS_GROUP_ID = ' ||
664 r_migration_data.business_group_id || ');');
665 utl_file.put_line(l_filehandle, '');
666
667 utl_file.put_line(l_filehandle, 'delete from HR_LOCATIONS_ALL_TL');
668 utl_file.put_line(l_filehandle, 'where LOCATION_ID in (');
669 utl_file.put_line(l_filehandle, ' select LOCATION_ID');
670 utl_file.put_line(l_filehandle, ' from HR_LOCATIONS_ALL');
671 utl_file.put_line(l_filehandle, ' where BUSINESS_GROUP_ID = ' ||
672 r_migration_data.business_group_id || ');');
673 utl_file.put_line(l_filehandle, '');
674
675 utl_file.put_line(l_filehandle, 'delete from HR_ORGANIZATION_INFORMATION ');
676 utl_file.put_line(l_filehandle, 'where ORGANIZATION_ID in (');
677 utl_file.put_line(l_filehandle, ' select ORGANIZATION_ID');
678 utl_file.put_line(l_filehandle, ' from HR_ALL_ORGANIZATION_UNITS');
679 utl_file.put_line(l_filehandle, ' where BUSINESS_GROUP_ID = ' ||
680 r_migration_data.business_group_id || ');');
681 utl_file.put_line(l_filehandle, '');
682
683 utl_file.put_line(l_filehandle, 'delete from HR_ALL_ORGANIZATION_UNITS');
684 utl_file.put_line(l_filehandle, 'where BUSINESS_GROUP_ID = ' ||
685 r_migration_data.business_group_id || ';');
686 utl_file.put_line(l_filehandle, '');
687
688 utl_file.put_line(l_filehandle, 'delete from HR_LOCATIONS_ALL');
689 utl_file.put_line(l_filehandle, 'where BUSINESS_GROUP_ID = ' ||
690 r_migration_data.business_group_id || ';');
691 utl_file.put_line(l_filehandle, '');
692
693 utl_file.put_line(l_filehandle, '-- DM processes info');
694 utl_file.put_line(l_filehandle, 'delete from BEN_BATCH_PARAMETER');
695 utl_file.put_line(l_filehandle, 'where BUSINESS_GROUP_ID = ' ||
696 r_migration_data.business_group_id || ';');
697 utl_file.put_line(l_filehandle, '');
698
699 utl_file.put_line(l_filehandle, '-- misc tables');
700 utl_file.put_line(l_filehandle, 'delete from BEN_EXT_CHG_EVT_LOG');
701 utl_file.put_line(l_filehandle, 'where BUSINESS_GROUP_ID = ' ||
702 r_migration_data.business_group_id || ';');
703 utl_file.put_line(l_filehandle, '');
704
705
706 utl_file.put_line(l_filehandle, '-- migration info');
707 utl_file.put_line(l_filehandle, 'delete from hr_dm_migration_requests');
708 utl_file.put_line(l_filehandle, 'where migration_id in');
709 utl_file.put_line(l_filehandle, ' (select migration_id');
710 utl_file.put_line(l_filehandle, ' from hr_dm_migrations');
711 utl_file.put_line(l_filehandle, ' where business_group_id = ' ||
712 r_migration_data.business_group_id || ');');
713 utl_file.put_line(l_filehandle, '');
714
715 utl_file.put_line(l_filehandle, 'delete from hr_dm_migration_ranges');
716 utl_file.put_line(l_filehandle, 'where phase_item_id in');
717 utl_file.put_line(l_filehandle, ' (select phase_item_id');
718 utl_file.put_line(l_filehandle, ' from hr_dm_phase_items');
719 utl_file.put_line(l_filehandle, ' where phase_id in');
720 utl_file.put_line(l_filehandle, ' (select phase_id');
721 utl_file.put_line(l_filehandle, ' from hr_dm_phases');
722 utl_file.put_line(l_filehandle, ' where migration_id in');
723 utl_file.put_line(l_filehandle, ' (select migration_id');
724 utl_file.put_line(l_filehandle, ' from hr_dm_migrations');
725 utl_file.put_line(l_filehandle, ' where business_group_id = ' ||
726 r_migration_data.business_group_id || ')));');
727 utl_file.put_line(l_filehandle, '');
728
729 utl_file.put_line(l_filehandle, 'delete from hr_dm_phase_items');
730 utl_file.put_line(l_filehandle, 'where phase_item_id in');
731 utl_file.put_line(l_filehandle, ' (select phase_item_id');
732 utl_file.put_line(l_filehandle, ' from hr_dm_phase_items');
733 utl_file.put_line(l_filehandle, ' where phase_id in');
734 utl_file.put_line(l_filehandle, ' (select phase_id');
735 utl_file.put_line(l_filehandle, ' from hr_dm_phases');
736 utl_file.put_line(l_filehandle, ' where migration_id in');
737 utl_file.put_line(l_filehandle, ' (select migration_id');
738 utl_file.put_line(l_filehandle, ' from hr_dm_migrations');
739 utl_file.put_line(l_filehandle, ' where business_group_id = ' ||
740 r_migration_data.business_group_id || ')));');
741 utl_file.put_line(l_filehandle, '');
742
743 utl_file.put_line(l_filehandle, 'delete from hr_dm_phases');
744 utl_file.put_line(l_filehandle, 'where phase_id in');
745 utl_file.put_line(l_filehandle, ' (select phase_id');
746 utl_file.put_line(l_filehandle, ' from hr_dm_phases');
747 utl_file.put_line(l_filehandle, ' where migration_id in');
748 utl_file.put_line(l_filehandle, ' (select migration_id');
749 utl_file.put_line(l_filehandle, ' from hr_dm_migrations');
750 utl_file.put_line(l_filehandle, ' where business_group_id = ' ||
751 r_migration_data.business_group_id || '));');
752 utl_file.put_line(l_filehandle, '');
753
754 utl_file.put_line(l_filehandle, 'delete from hr_dm_migrations');
755 utl_file.put_line(l_filehandle, 'where migration_id in');
756 utl_file.put_line(l_filehandle, ' (select migration_id');
757 utl_file.put_line(l_filehandle, ' from hr_dm_migrations');
758 utl_file.put_line(l_filehandle, ' where business_group_id = ' ||
759 r_migration_data.business_group_id || ');');
760 utl_file.put_line(l_filehandle, '');
761
762
763 utl_file.put_line(l_filehandle, '--');
764 utl_file.put_line(l_filehandle, 'END;');
765 utl_file.put_line(l_filehandle, '--');
766 utl_file.put_line(l_filehandle, '/');
767 utl_file.put_line(l_filehandle, 'COMMIT;');
768 utl_file.put_line(l_filehandle, 'EXIT;');
769
770
771 -- close file
772 utl_file.fclose(l_filehandle);
773
774
775
776 hr_dm_utility.message('INFO','Delete - cleanup script', 15);
777 hr_dm_utility.message('SUMM','Delete - cleanup script', 20);
778 hr_dm_utility.message('ROUT','exit:hr_dm_delete.pre_delete_process', 25);
779 hr_dm_utility.message('PARA','(none)', 30);
780
781 -- error handling
782 EXCEPTION
783 WHEN e_fatal_error THEN
784 hr_dm_utility.error(SQLCODE,'hr_dm_delete.pre_delete_process',
785 l_fatal_error_message,'R');
786 RAISE;
787
788 WHEN OTHERS THEN
789 hr_dm_utility.error(SQLCODE,'hr_dm_delete.pre_delete_process',
790 '(none)','R');
791 RAISE;
792
793 --
794 END pre_delete_process;
795 --
796
797
798
799 END hr_dm_delete;