[Home] [Help]
PACKAGE BODY: APPS.HR_PSF_DEL
Source
1 Package Body hr_psf_del as
2 /* $Header: hrpsfrhi.pkb 120.14.12020000.5 2013/04/12 18:20:17 pathota ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := 'hr_psf_del.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------------< dt_delete_dml >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml delete logic for the datetrack
17 -- delete modes: ZAP, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE. The
18 -- execution is as follows:
19 -- 1) If the delete mode is DELETE_NEXT_CHANGE then delete where the
20 -- effective start date is equal to the validation start date.
21 -- 2) If the delete mode is not DELETE_NEXT_CHANGE then delete
22 -- all rows for the entity where the effective start date is greater
23 -- than or equal to the validation start date.
24 -- 3) To raise any errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the
28 -- delete_dml procedure.
29 --
30 -- In Parameters:
31 -- A Pl/Sql record structre.
32 --
33 -- Post Success:
34 -- The specified row will be delete from the schema.
35 --
36 -- Post Failure:
37 --
38 -- Developer Implementation Notes:
39 -- This is an internal private procedure which must be called from the
40 -- delete_dml procedure.
41 --
42 -- Access Status:
43 -- Internal Row Handler Use Only.
44 --
45 -- {End Of Comments}
46 -- ----------------------------------------------------------------------------
47 Procedure dt_delete_dml
48 (p_rec in out nocopy hr_psf_shd.g_rec_type,
49 p_effective_date in date,
50 p_datetrack_mode in varchar2,
51 p_validation_start_date in date,
52 p_validation_end_date in date) is
53 --
54 l_proc varchar2(72);
55 --
56 Begin
57 if g_debug then
58 l_proc := g_package||'dt_delete_dml';
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 end if;
61 If (p_datetrack_mode = 'DELETE_NEXT_CHANGE') then
62 if g_debug then
63 hr_utility.set_location(l_proc, 10);
64 end if;
65 --
66 -- Delete the where the effective start date is equal
67 -- to the validation end date.
68 --
69 delete from hr_all_positions_f
70 where position_id = p_rec.position_id
71 and effective_start_date = p_validation_start_date;
72 --
73 Else
74 if g_debug then
75 hr_utility.set_location(l_proc, 15);
76 end if;
77 --
78 -- Delete the row(s) where the effective start date is greater than
79 -- or equal to the validation start date.
80 --
81 delete from hr_all_positions_f
82 where position_id = p_rec.position_id
83 and effective_start_date >= p_validation_start_date;
84 --
85 End If;
86 --
87 if g_debug then
88 hr_utility.set_location(' Leaving:'||l_proc, 20);
89 end if;
90 --
91 Exception
92 When Others Then
93 Raise;
94 End dt_delete_dml;
95 --
96 -- ----------------------------------------------------------------------------
97 -- |------------------------------< delete_dml >------------------------------|
98 -- ----------------------------------------------------------------------------
99 Procedure delete_dml
100 (p_rec in out nocopy hr_psf_shd.g_rec_type,
101 p_effective_date in date,
102 p_datetrack_mode in varchar2,
103 p_validation_start_date in date,
104 p_validation_end_date in date) is
105 --
106 l_proc varchar2(72) ;
107 --
108 Begin
109 g_debug := hr_utility.debug_enabled;
110 if g_debug then
111 l_proc := g_package||'delete_dml';
112 hr_utility.set_location('Entering:'||l_proc, 5);
113 end if;
114 --
115 dt_delete_dml(p_rec => p_rec,
116 p_effective_date => p_effective_date,
117 p_datetrack_mode => p_datetrack_mode,
118 p_validation_start_date => p_validation_start_date,
119 p_validation_end_date => p_validation_end_date);
120 --
121 if g_debug then
122 hr_utility.set_location(' Leaving:'||l_proc, 10);
123 end if;
124 End delete_dml;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |----------------------------< dt_pre_delete >-----------------------------|
128 -- ----------------------------------------------------------------------------
129 -- {Start Of Comments}
130 --
131 -- Description:
132 -- The dt_pre_delete process controls the execution of dml
133 -- for the datetrack modes: DELETE, FUTURE_CHANGE
134 -- and DELETE_NEXT_CHANGE only.
135 --
136 -- Prerequisites:
137 -- This is an internal procedure which is called from the pre_delete
138 -- procedure.
139 --
140 -- In Parameters:
141 -- A Pl/Sql record structre.
142 --
143 -- Post Success:
144 -- Processing continues.
145 --
146 -- Post Failure:
147 -- If an error has occurred, an error message and exception will be raised
148 -- but not handled.
149 --
150 -- Developer Implementation Notes:
151 -- This is an internal procedure which is required by Datetrack. Don't
152 -- remove or modify.
153 --
154 -- Access Status:
155 -- Internal Row Handler Use Only.
156 --
157 -- {End Of Comments}
158 -- ----------------------------------------------------------------------------
159 Procedure dt_pre_delete
160 (p_rec in out nocopy hr_psf_shd.g_rec_type,
161 p_effective_date in date,
162 p_datetrack_mode in varchar2,
163 p_validation_start_date in date,
164 p_validation_end_date in date) is
165 --
166 l_proc varchar2(72) ;
167 --
168 Begin
169 if g_debug then
170 l_proc := g_package||'dt_pre_delete';
171 hr_utility.set_location('Entering:'||l_proc, 5);
172 end if;
173 --
174 If (p_datetrack_mode <> 'ZAP') then
175 --
176 p_rec.effective_start_date := hr_psf_shd.g_old_rec.effective_start_date;
177 --
178 If (p_datetrack_mode = 'DELETE') then
179 p_rec.effective_end_date := p_validation_start_date - 1;
180 Else
181 p_rec.effective_end_date := p_validation_end_date;
182 End If;
183 --
184 -- Update the current effective end date record
185 --
186 hr_psf_shd.upd_effective_end_date
187 (p_effective_date => p_effective_date,
188 p_base_key_value => p_rec.position_id,
189 p_new_effective_end_date => p_rec.effective_end_date,
190 p_validation_start_date => p_validation_start_date,
191 p_validation_end_date => p_validation_end_date,
192 p_object_version_number => p_rec.object_version_number);
193 Else
194 p_rec.effective_start_date := null;
195 p_rec.effective_end_date := null;
196 End If;
197 if g_debug then
198 hr_utility.set_location(' Leaving:'||l_proc, 10);
199 end if;
200 End dt_pre_delete;
201 --
202 --
203 -- ----------------------------------------------------------------------------
204 -- |-------------------------< pre_delete_checks >-----------------------------|
205 -- ----------------------------------------------------------------------------
206 --
207 PROCEDURE pre_delete_checks(p_position_id in number
208 ,p_business_group_id in number
209 ,p_datetrack_mode in varchar2
210 ) is
211 --
212 l_exists varchar2(1);
213 l_pos_structure_element_id number;
214 l_sql_text VARCHAR2(2000);
215 l_oci_out VARCHAR2(1);
216 l_sql_cursor NUMBER;
217 l_rows_fetched NUMBER;
218 l_proc varchar2(72) ;
219
220 begin
221 if g_debug then
222 l_proc := g_package||'pre_delete_checks';
223 hr_utility.set_location('Entering : ' || l_proc, 10);
224 end if;
225 if p_datetrack_mode = 'ZAP' then
226 if g_debug then
227 hr_utility.set_location(l_proc, 20);
228 end if;
229 l_exists := NULL;
230 -- if p_hr_ins = 'Y' then
231 l_exists := NULL;
232 begin
233 select '1'
234 into l_exists
235 from sys.dual
236 where exists(SELECT NULL
237 from PER_BUDGET_ELEMENTS BE
238 where BE.POSITION_ID = p_position_id);
239 exception when no_data_found then
240 null;
241 end;
242 if l_exists = '1' then
243 hr_utility.set_message(800,'HR_PSF_DEL_FAIL_BGT_ELE');
244 hr_utility.raise_error;
245 end if;
246 l_exists := NULL;
247 if g_debug then
248 hr_utility.set_location(l_proc, 30);
249 end if;
250 begin
251 select '1'
252 into l_exists
253 from sys.dual
254 where exists(SELECT NULL
255 from PER_VACANCIES VAC
256 where VAC.POSITION_ID = p_position_id);
257 exception when no_data_found then
258 null;
259 end;
260 if l_exists = '1' then
261 hr_utility.set_message(800,'PER_7861_DEL_POS_REC_ACT');
262 hr_utility.raise_error;
263 end if;
264 if g_debug then
265 hr_utility.set_location(l_proc, 40);
266 end if;
267 /****** Commented for bug 9146790 ********
268 begin
269 select e.pos_structure_element_id
270 into l_pos_structure_element_id
271 from per_pos_structure_elements e
272 where e.parent_position_id = p_position_id
273 and not exists (
274 select null
275 from per_pos_structure_elements e2
276 where e2.subordinate_position_id = p_position_id)
277 and 1 = (
278 select count(e3.pos_structure_element_id)
279 from per_pos_structure_elements e3
280 where e3.parent_position_id = p_position_id);
281 exception when no_data_found then
282 null;
283 end;
284 ****** Commented for bug 9146790 ********/
285
286 l_exists := NULL;
287 if g_debug then
288 hr_utility.set_location(l_proc, 50);
289 end if;
290 --if l_pos_structure_element_id is null then -- condition removed (Bug 9146790)
291 begin
292 select '1'
293 into l_exists
294 from sys.dual
295 where exists(SELECT NULL
296 FROM PER_POS_STRUCTURE_ELEMENTS PSE
297 WHERE PSE.PARENT_POSITION_ID = p_position_id
298 OR PSE.SUBORDINATE_POSITION_ID = p_position_id) ;
299 exception when no_data_found then
300 null;
301 end;
302 if g_debug then
303 hr_utility.set_location(l_proc, 60);
304 end if;
305 if l_exists = '1' then
306 hr_utility.set_message(800,'PER_7416_POS_IN_POS_HIER');
307 hr_utility.raise_error;
308 end if;
309 -- end if; -- Bug 9146790
310 l_exists := NULL;
311 if g_debug then
312 hr_utility.set_location(l_proc, 70);
313 end if;
314 begin
315 select '1'
316 into l_exists
317 from sys.dual
318 where exists(SELECT NULL
319 FROM PER_VALID_GRADES VG1
320 WHERE business_group_id + 0 = p_business_group_id
321 AND VG1.POSITION_ID = p_position_id);
322 exception when no_data_found then
323 null;
324 end;
325 if g_debug then
326 hr_utility.set_location(l_proc, 80);
327 end if;
328 if l_exists = '1' then
329 hr_utility.set_message(801,'PER_7865_DEF_POS_DEL_GRADE');
330 hr_utility.raise_error;
331 end if;
332 l_exists := NULL;
333 if g_debug then
334 hr_utility.set_location(l_proc, 90);
335 end if;
336 begin
337 select '1'
338 into l_exists
339 from sys.dual
340 where exists(select null
341 from per_job_requirements jre1
342 where jre1.position_id = p_position_id);
343 exception when no_data_found then
344 null;
345 end;
346 if l_exists = '1' then
347 hr_utility.set_message(801,'PER_7866_DEF_POS_DEL_REQ');
348 hr_utility.raise_error;
349 end if;
350 l_exists := NULL;
351 if g_debug then
352 hr_utility.set_location(l_proc, 100);
353 end if;
354 begin
355 select '1'
356 into l_exists
357 from sys.dual
358 where exists(select null
359 from per_job_evaluations jev1
360 where jev1.position_id = p_position_id);
361 exception when no_data_found then
362 null;
363 end;
364 if l_exists = '1' then
365 hr_utility.set_message(801,'PER_7867_DEF_POS_DEL_EVAL');
366 hr_utility.raise_error;
367 end if;
368
369 l_exists := NULL;
370 if g_debug then
371 hr_utility.set_location(l_proc, 110);
372 end if;
373 begin
374 select '1'
375 into l_exists
376 from sys.dual
377 where exists(select null
378 from hr_all_positions_f
379 where successor_position_id = p_position_id);
380 exception when no_data_found then
381 null;
382 end;
383
384 if l_exists = '1' then
385 hr_utility.set_message(801,'PER_7996_POS_SUCCESSOR_REF');
386 hr_utility.raise_error;
387 end if;
388
389 l_exists := NULL;
390 if g_debug then
391 hr_utility.set_location(l_proc, 120);
392 end if;
393
394 begin
395 select '1'
396 into l_exists
397 from sys.dual
398 where exists(select null
399 from hr_All_positions_f
400 where relief_position_id = p_position_id);
401 exception when no_data_found then
402 null;
403 end;
404
405 if l_exists = '1' then
406 hr_utility.set_message(801,'PER_7997_POS_RELIEF_REF');
407 hr_utility.raise_error;
408 end if;
409
410 l_exists := NULL;
411 if g_debug then
412 hr_utility.set_location(l_proc, 130);
413 end if;
414
415 begin
416 select '1'
417 into l_exists
418 from sys.dual
419 where exists(select null
420 from per_mm_positions
421 where new_position_id = p_position_id);
422 exception when no_data_found then
423 null;
424 end;
425
426 if l_exists = '1' then
427 hr_utility.set_message(800,'HR_52776_NOT_DEL_MM_POSITIONS');
428 hr_utility.raise_error;
429 end if;
430 if g_debug then
431 hr_utility.set_location(l_proc, 140);
432 end if;
433
434 -- end if;
435 -- fix for bug 8439584
436 --
437 -- is po installed?
438 --
439 -- if p_po_ins = 'Y' then
440 begin
441 l_sql_text := 'select null '
442 ||' from sys.dual '
443 ||' where exists( select null '
444 ||' from po_system_parameters '
445 ||' where security_position_structure_id = '
446 ||to_char(p_position_id)
447 ||' ) '
448 ||' or exists( select null '
449 ||' from po_employee_hierarchies '
450 ||' where employee_position_id = '
451 ||to_char(p_position_id)
452 ||' or superior_position_id = '
453 ||to_char(p_position_id)
454 ||' ) '
455 ||' or exists( select null '
456 ||' from PO_POSITION_CONTROLS_ALL '
457 ||' where position_id = '
458 ||to_char(p_position_id)
459 ||' )' ;
460 --
461 -- Open Cursor for Processing Sql statment.
462 --
463 -- fix for bug 8439584
464 if g_debug then
465 hr_utility.set_location(l_proc, 150);
466 end if;
467 l_sql_cursor := dbms_sql.open_cursor;
468 --
469 -- Parse SQL statement.
470 --
471 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
472 --
473 -- Map the local variables to each returned Column
474 --
475 if g_debug then
476 hr_utility.set_location(l_proc, 160);
477 end if;
478 dbms_sql.define_column(l_sql_cursor, 1,l_oci_out,1);
479 --
480 -- Execute the SQL statement.
481 --
482 if g_debug then
483 hr_utility.set_location(l_proc, 170);
484 end if;
485 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
486 --
487 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
488 then
489 hr_utility.set_message(800,'HR_6048_PO_POS_DEL_POS_CONT');
490 hr_utility.raise_error;
491 end if;
492 --
493 -- Close cursor used for processing SQL statement.
494 --
495 dbms_sql.close_cursor(l_sql_cursor);
496 if g_debug then
497 hr_utility.set_location(l_proc, 180);
498 end if;
499 end;
500 -- end if;
501 --
502 -- Ref Int check for OTA.
503 --
504 per_ota_predel_validation.ota_predel_pos_validation(p_position_id);
505 if g_debug then
506 hr_utility.set_location('Leaving : ' || l_proc, 300);
507 end if;
508 --
509 end if;
510 end pre_delete_checks;
511 --
512 -- ----------------------------------------------------------------------------
513 -- |------------------------------< pre_delete >------------------------------|
514 -- ----------------------------------------------------------------------------
515 -- {Start Of Comments}
516 --
517 -- Description:
518 -- This private procedure contains any processing which is required before
519 -- the delete dml.
520 --
521 -- Prerequisites:
522 -- This is an internal procedure which is called from the del procedure.
523 --
524 -- In Parameters:
525 -- A Pl/Sql record structre.
526 --
527 -- Post Success:
528 -- Processing continues.
529 --
530 -- Post Failure:
531 -- If an error has occurred, an error message and exception will be raised
532 -- but not handled.
533 --
534 -- Developer Implementation Notes:
535 -- Any pre-processing required before the delete dml is issued should be
536 -- coded within this procedure. It is important to note that any 3rd party
537 -- maintenance should be reviewed before placing in this procedure. The call
538 -- to the dt_delete_dml procedure should NOT be removed.
539 --
540 -- Access Status:
541 -- Internal Row Handler Use Only.
542 --
543 -- {End Of Comments}
544 -- ----------------------------------------------------------------------------
545 Procedure pre_delete
546 (p_rec in out nocopy hr_psf_shd.g_rec_type,
547 p_effective_date in date,
548 p_datetrack_mode in varchar2,
549 p_validation_start_date in date,
550 p_validation_end_date in date) is
551 --
552 l_proc varchar2(72) ;
553 --
554 cursor vgr is
555 select valid_grade_id, object_version_number from per_valid_grades
556 where position_id = p_rec.position_id;
557 Begin
558 if g_debug then
559 l_proc := g_package||'pre_delete';
560 hr_utility.set_location('Entering:'||l_proc, 5);
561 end if;
562 -- pre delete checks
563 pre_delete_checks(p_position_id => p_rec.position_id
564 ,p_business_group_id => p_rec.business_group_id
565 ,p_datetrack_mode => p_datetrack_mode);
566 /*
567 --
568 -- Delete record from PER_POSITION_LIST
569 --
570 if (p_datetrack_mode = 'ZAP') then
571 open c1;
572 fetch c1 into l_view_all_positions_flag;
573 close c1;
574 --
575 if l_view_all_positions_flag <> 'Y' then
576 Delete from PER_POSITION_LIST
577 WHERE position_id = p_rec.Position_id;
578 end if;
579 end if;
580 */
581 --
582 -- Delete from per_valid_grades rows corresponding to the position being deleted.
583 --
584 if (p_datetrack_mode = 'ZAP') then
585 for each_rec in vgr loop
586 per_vgr_del.del(p_valid_grade_id => each_rec.valid_grade_id,
587 p_object_version_number => each_rec.object_version_number);
588 end loop;
589 end if;
590 --
591 dt_pre_delete
592 (p_rec => p_rec,
593 p_effective_date => p_effective_date,
594 p_datetrack_mode => p_datetrack_mode,
595 p_validation_start_date => p_validation_start_date,
596 p_validation_end_date => p_validation_end_date);
597 --
598 if g_debug then
599 hr_utility.set_location(' Leaving:'||l_proc, 10);
600 end if;
601 End pre_delete;
602 --
603 -- ----------------------------------------------------------------------------
604 -- |-----------------------------< post_delete >------------------------------|
605 -- ----------------------------------------------------------------------------
606 -- {Start Of Comments}
607 --
608 -- Description:
609 -- This private procedure contains any processing which is required after the
610 -- delete dml.
611 --
612 -- Prerequisites:
613 -- This is an internal procedure which is called from the del procedure.
614 --
615 -- In Parameters:
616 -- A Pl/Sql record structre.
617 --
618 -- Post Success:
619 -- Processing continues.
620 --
621 -- Post Failure:
622 -- If an error has occurred, an error message and exception will be raised
623 -- but not handled
624 --
625 -- Developer Implementation Notes:
626 -- Any post-processing required after the delete dml is issued should be
627 -- coded within this procedure. It is important to note that any 3rd party
628 -- maintenance should be reviewed before placing in this procedure.
629 --
630 -- Access Status:
631 -- Internal Row Handler Use Only.
632 --
633 -- {End Of Comments}
634 -- ----------------------------------------------------------------------------
635 Procedure post_delete
636 (p_rec in hr_psf_shd.g_rec_type,
637 p_effective_date in date,
638 p_datetrack_mode in varchar2,
639 p_validation_start_date in date,
640 p_validation_end_date in date) is
641 --
642 l_proc varchar2(72) ;
643 --
644 Begin
645 if g_debug then
646 l_proc := g_package||'post_delete';
647 --
648 hr_utility.set_location('Entering:'||l_proc, 5);
649 end if;
650 --
651 -- User Hook
652 --
653 Begin
654 hr_psf_rkd.after_delete(
655 p_position_id => p_rec.position_id ,
656 p_effective_date => p_effective_date ,
657 p_effective_start_date_o => hr_psf_shd.g_old_rec.effective_start_date ,
658 p_effective_end_date_o => hr_psf_shd.g_old_rec.effective_end_date ,
659 p_availability_status_id_o => hr_psf_shd.g_old_rec.availability_status_id ,
660 p_business_group_id_o => hr_psf_shd.g_old_rec.business_group_id ,
661 p_entry_step_id_o => hr_psf_shd.g_old_rec.entry_step_id ,
662 p_entry_grade_rule_id_o => hr_psf_shd.g_old_rec.entry_grade_rule_id ,
663 p_job_id_o => hr_psf_shd.g_old_rec.job_id ,
664 p_location_id_o => hr_psf_shd.g_old_rec.location_id ,
665 p_organization_id_o => hr_psf_shd.g_old_rec.organization_id ,
666 p_pay_freq_payroll_id_o => hr_psf_shd.g_old_rec.pay_freq_payroll_id ,
667 p_position_definition_id_o => hr_psf_shd.g_old_rec.position_definition_id ,
668 p_position_transaction_id_o => hr_psf_shd.g_old_rec.position_transaction_id ,
669 p_prior_position_id_o => hr_psf_shd.g_old_rec.prior_position_id ,
670 p_relief_position_id_o => hr_psf_shd.g_old_rec.relief_position_id ,
671 p_entry_grade_id_o => hr_psf_shd.g_old_rec.entry_grade_id ,
672 p_successor_position_id_o => hr_psf_shd.g_old_rec.successor_position_id ,
673 p_supervisor_position_id_o => hr_psf_shd.g_old_rec.supervisor_position_id ,
674 p_amendment_date_o => hr_psf_shd.g_old_rec.amendment_date ,
675 p_amendment_recommendation_o => hr_psf_shd.g_old_rec.amendment_recommendation ,
676 p_amendment_ref_number_o => hr_psf_shd.g_old_rec.amendment_ref_number ,
677 p_bargaining_unit_cd_o => hr_psf_shd.g_old_rec.bargaining_unit_cd ,
678 p_comments_o => hr_psf_shd.g_old_rec.comments ,
679 p_current_job_prop_end_date_o => hr_psf_shd.g_old_rec.current_job_prop_end_date ,
680 p_current_org_prop_end_date_o => hr_psf_shd.g_old_rec.current_org_prop_end_date ,
681 p_avail_status_prop_end_date_o => hr_psf_shd.g_old_rec.avail_status_prop_end_date,
682 p_date_effective_o => hr_psf_shd.g_old_rec.date_effective ,
683 p_date_end_o => hr_psf_shd.g_old_rec.date_end ,
684 p_earliest_hire_date_o => hr_psf_shd.g_old_rec.earliest_hire_date ,
685 p_fill_by_date_o => hr_psf_shd.g_old_rec.fill_by_date ,
686 p_frequency_o => hr_psf_shd.g_old_rec.frequency ,
687 p_fte_o => hr_psf_shd.g_old_rec.fte ,
688 p_max_persons_o => hr_psf_shd.g_old_rec.max_persons ,
689 p_name_o => hr_psf_shd.g_old_rec.name ,
690 p_overlap_period_o => hr_psf_shd.g_old_rec.overlap_period ,
691 p_overlap_unit_cd_o => hr_psf_shd.g_old_rec.overlap_unit_cd ,
692 p_pay_term_end_day_cd_o => hr_psf_shd.g_old_rec.pay_term_end_day_cd ,
693 p_pay_term_end_month_cd_o => hr_psf_shd.g_old_rec.pay_term_end_month_cd ,
694 p_permanent_temporary_flag_o => hr_psf_shd.g_old_rec.permanent_temporary_flag ,
695 p_permit_recruitment_flag_o => hr_psf_shd.g_old_rec.permit_recruitment_flag ,
696 p_position_type_o => hr_psf_shd.g_old_rec.position_type ,
697 p_posting_description_o => hr_psf_shd.g_old_rec.posting_description ,
698 p_probation_period_o => hr_psf_shd.g_old_rec.probation_period ,
699 p_probation_period_unit_cd_o => hr_psf_shd.g_old_rec.probation_period_unit_cd ,
700 p_replacement_required_flag_o => hr_psf_shd.g_old_rec.replacement_required_flag ,
701 p_review_flag_o => hr_psf_shd.g_old_rec.review_flag ,
702 p_seasonal_flag_o => hr_psf_shd.g_old_rec.seasonal_flag ,
703 p_security_requirements_o => hr_psf_shd.g_old_rec.security_requirements ,
704 p_status_o => hr_psf_shd.g_old_rec.status ,
705 p_term_start_day_cd_o => hr_psf_shd.g_old_rec.term_start_day_cd ,
706 p_term_start_month_cd_o => hr_psf_shd.g_old_rec.term_start_month_cd ,
707 p_time_normal_finish_o => hr_psf_shd.g_old_rec.time_normal_finish ,
708 p_time_normal_start_o => hr_psf_shd.g_old_rec.time_normal_start ,
709 p_update_source_cd_o => hr_psf_shd.g_old_rec.update_source_cd ,
710 p_working_hours_o => hr_psf_shd.g_old_rec.working_hours ,
711 p_works_council_approval_fla_o => hr_psf_shd.g_old_rec.works_council_approval_flag,
712 p_work_period_type_cd_o => hr_psf_shd.g_old_rec.work_period_type_cd ,
713 p_work_term_end_day_cd_o => hr_psf_shd.g_old_rec.work_term_end_day_cd ,
714 p_work_term_end_month_cd_o => hr_psf_shd.g_old_rec.work_term_end_month_cd ,
715 p_proposed_fte_for_layoff_o => hr_psf_shd.g_old_rec.proposed_fte_for_layoff ,
716 p_proposed_date_for_layoff_o => hr_psf_shd.g_old_rec.proposed_date_for_layoff ,
717 p_pay_basis_id_o => hr_psf_shd.g_old_rec.pay_basis_id ,
718 p_supervisor_id_o => hr_psf_shd.g_old_rec.supervisor_id ,
719 p_copied_to_old_table_flag_o => hr_psf_shd.g_old_rec.copied_to_old_table_flag ,
720 p_information1_o => hr_psf_shd.g_old_rec.information1 ,
721 p_information2_o => hr_psf_shd.g_old_rec.information2 ,
722 p_information3_o => hr_psf_shd.g_old_rec.information3 ,
723 p_information4_o => hr_psf_shd.g_old_rec.information4 ,
724 p_information5_o => hr_psf_shd.g_old_rec.information5 ,
725 p_information6_o => hr_psf_shd.g_old_rec.information6 ,
726 p_information7_o => hr_psf_shd.g_old_rec.information7 ,
727 p_information8_o => hr_psf_shd.g_old_rec.information8 ,
728 p_information9_o => hr_psf_shd.g_old_rec.information9 ,
729 p_information10_o => hr_psf_shd.g_old_rec.information10 ,
730 p_information11_o => hr_psf_shd.g_old_rec.information11 ,
731 p_information12_o => hr_psf_shd.g_old_rec.information12 ,
732 p_information13_o => hr_psf_shd.g_old_rec.information13 ,
733 p_information14_o => hr_psf_shd.g_old_rec.information14 ,
734 p_information15_o => hr_psf_shd.g_old_rec.information15 ,
735 p_information16_o => hr_psf_shd.g_old_rec.information16 ,
736 p_information17_o => hr_psf_shd.g_old_rec.information17 ,
737 p_information18_o => hr_psf_shd.g_old_rec.information18 ,
738 p_information19_o => hr_psf_shd.g_old_rec.information19 ,
739 p_information20_o => hr_psf_shd.g_old_rec.information20 ,
740 p_information21_o => hr_psf_shd.g_old_rec.information21 ,
741 p_information22_o => hr_psf_shd.g_old_rec.information22 ,
742 p_information23_o => hr_psf_shd.g_old_rec.information23 ,
743 p_information24_o => hr_psf_shd.g_old_rec.information24 ,
744 p_information25_o => hr_psf_shd.g_old_rec.information25 ,
745 p_information26_o => hr_psf_shd.g_old_rec.information26 ,
746 p_information27_o => hr_psf_shd.g_old_rec.information27 ,
747 p_information28_o => hr_psf_shd.g_old_rec.information28 ,
748 p_information29_o => hr_psf_shd.g_old_rec.information29 ,
749 p_information30_o => hr_psf_shd.g_old_rec.information30 ,
750 p_information_category_o => hr_psf_shd.g_old_rec.information_category ,
751 p_attribute1_o => hr_psf_shd.g_old_rec.attribute1 ,
752 p_attribute2_o => hr_psf_shd.g_old_rec.attribute2 ,
753 p_attribute3_o => hr_psf_shd.g_old_rec.attribute3 ,
754 p_attribute4_o => hr_psf_shd.g_old_rec.attribute4 ,
755 p_attribute5_o => hr_psf_shd.g_old_rec.attribute5 ,
756 p_attribute6_o => hr_psf_shd.g_old_rec.attribute6 ,
757 p_attribute7_o => hr_psf_shd.g_old_rec.attribute7 ,
758 p_attribute8_o => hr_psf_shd.g_old_rec.attribute8 ,
759 p_attribute9_o => hr_psf_shd.g_old_rec.attribute9 ,
760 p_attribute10_o => hr_psf_shd.g_old_rec.attribute10 ,
761 p_attribute11_o => hr_psf_shd.g_old_rec.attribute11 ,
762 p_attribute12_o => hr_psf_shd.g_old_rec.attribute12 ,
763 p_attribute13_o => hr_psf_shd.g_old_rec.attribute13 ,
764 p_attribute14_o => hr_psf_shd.g_old_rec.attribute14 ,
765 p_attribute15_o => hr_psf_shd.g_old_rec.attribute15 ,
766 p_attribute16_o => hr_psf_shd.g_old_rec.attribute16 ,
767 p_attribute17_o => hr_psf_shd.g_old_rec.attribute17 ,
768 p_attribute18_o => hr_psf_shd.g_old_rec.attribute18 ,
769 p_attribute19_o => hr_psf_shd.g_old_rec.attribute19 ,
770 p_attribute20_o => hr_psf_shd.g_old_rec.attribute20 ,
771 p_attribute21_o => hr_psf_shd.g_old_rec.attribute21 ,
772 p_attribute22_o => hr_psf_shd.g_old_rec.attribute22 ,
773 p_attribute23_o => hr_psf_shd.g_old_rec.attribute23 ,
774 p_attribute24_o => hr_psf_shd.g_old_rec.attribute24 ,
775 p_attribute25_o => hr_psf_shd.g_old_rec.attribute25 ,
776 p_attribute26_o => hr_psf_shd.g_old_rec.attribute26 ,
777 p_attribute27_o => hr_psf_shd.g_old_rec.attribute27 ,
778 p_attribute28_o => hr_psf_shd.g_old_rec.attribute28 ,
779 p_attribute29_o => hr_psf_shd.g_old_rec.attribute29 ,
780 p_attribute30_o => hr_psf_shd.g_old_rec.attribute30 ,
781 p_attribute_category_o => hr_psf_shd.g_old_rec.attribute_category ,
782 p_request_id_o => hr_psf_shd.g_old_rec.request_id ,
783 p_program_application_id_o => hr_psf_shd.g_old_rec.program_application_id ,
784 p_program_id_o => hr_psf_shd.g_old_rec.program_id ,
785 p_program_update_date_o => hr_psf_shd.g_old_rec.program_update_date ,
786 p_object_version_number_o => hr_psf_shd.g_old_rec.object_version_number );
787 --
788 exception
789 when hr_api.cannot_find_prog_unit then
790 hr_api.cannot_find_prog_unit_error
791 (p_module_name => 'HR_ALL_POSITIONS'
792 ,p_hook_type => 'AD'
793 );
794 end;
795 --
796 --hr_psf_shd.position_wf_sync(p_rec.position_id , p_validation_start_date);
797 hr_psf_shd.position_wf_sync(p_position_id => p_rec.position_id
798 , p_effective_date => p_validation_start_date
799 , p_action => 'DELETE'
800 , p_eff_start_date => p_rec.effective_start_date
801 , p_eff_end_date => p_rec.effective_end_date );
802 --
803 if g_debug then
804 hr_utility.set_location(' Leaving:'||l_proc, 10);
805 end if;
806 End post_delete;
807 --
808 -- ----------------------------------------------------------------------------
809 -- |---------------------------------< del >----------------------------------|
810 -- ----------------------------------------------------------------------------
811 Procedure del
812 (
813 p_rec in out nocopy hr_psf_shd.g_rec_type,
814 p_effective_date in date,
815 p_datetrack_mode in varchar2,
816 p_validate in boolean default false
817 ) is
818 --
819 l_proc varchar2(72) ;
820 l_validation_start_date date;
821 l_validation_end_date date;
822 --
823 cursor c1 is
824 select availability_status_id
825 from hr_all_positions_f
826 where position_id = p_rec.position_id;
827 --
828 Begin
829 g_debug := hr_utility.debug_enabled;
830 if g_debug then
831 l_proc := g_package||'del';
832 hr_utility.set_location('Entering:'||l_proc, 5);
833 end if;
834 --
835 -- Ensure that the DateTrack delete mode is valid
836 --
837 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
838 --
839 -- We must lock the row which we need to delete.
840 --
841 --
842 -- Determine if the business process is to be validated.
843 --
844 If p_validate then
845 --
846 -- Issue the savepoint.
847 --
848 SAVEPOINT del_per_per;
849 End If;
850 --
851 hr_psf_shd.lck
852 (p_effective_date => p_effective_date,
853 p_datetrack_mode => p_datetrack_mode,
854 p_position_id => p_rec.position_id,
855 p_object_version_number => p_rec.object_version_number,
856 p_validation_start_date => l_validation_start_date,
857 p_validation_end_date => l_validation_end_date);
858 --
859 -- get availability_status
860 --
861 if p_rec.availability_status_id is null then
862 open c1;
863 fetch c1 into p_rec.availability_status_id;
864 close c1;
865 end if;
866 --
867 -- Call the supporting delete validate operation
868 --
869 hr_psf_bus.delete_validate
870 (p_rec => p_rec,
871 p_effective_date => p_effective_date,
872 p_datetrack_mode => p_datetrack_mode,
873 p_validation_start_date => l_validation_start_date,
874 p_validation_end_date => l_validation_end_date);
875 --
876 -- Call the supporting pre-delete operation
877 --
878 pre_delete
879 (p_rec => p_rec,
880 p_effective_date => p_effective_date,
881 p_datetrack_mode => p_datetrack_mode,
882 p_validation_start_date => l_validation_start_date,
883 p_validation_end_date => l_validation_end_date);
884 --
885 -- Delete the row.
886 --
887 delete_dml
888 (p_rec => p_rec,
889 p_effective_date => p_effective_date,
890 p_datetrack_mode => p_datetrack_mode,
891 p_validation_start_date => l_validation_start_date,
892 p_validation_end_date => l_validation_end_date);
893 --
894 -- Call the supporting post-delete operation
895 --
896 post_delete
897 (p_rec => p_rec,
898 p_effective_date => p_effective_date,
899 p_datetrack_mode => p_datetrack_mode,
900 p_validation_start_date => l_validation_start_date,
901 p_validation_end_date => l_validation_end_date);
902 --
903 -- If we are validating then raise the Validate_Enabled exception
904 --
905 If p_validate then
906 Raise HR_Api.Validate_Enabled;
907 End If;
908 --
909 if g_debug then
910 hr_utility.set_location(' Leaving:'||l_proc, 10);
911 end if;
912
913 Exception
914 When HR_Api.Validate_Enabled Then
915 --
916 -- As the Validate_Enabled exception has been raised
917 -- we must rollback to the savepoint
918 --
919 ROLLBACK TO del_per_per;
920 End del;
921 --
922 -- ----------------------------------------------------------------------------
923 -- |---------------------------------< del >----------------------------------|
924 -- ----------------------------------------------------------------------------
925 Procedure del
926 (
927 p_position_id in number,
928 p_effective_start_date out nocopy date,
929 p_effective_end_date out nocopy date,
930 p_object_version_number in out nocopy number,
931 p_effective_date in date,
932 p_datetrack_mode in varchar2,
933 p_validate in boolean default false,
934 p_security_profile_id in number default hr_security.get_security_profile
935 ) is
936 --
937 l_rec hr_psf_shd.g_rec_type;
938 l_proc varchar2(72);
939 --
940 Begin
941 g_debug := hr_utility.debug_enabled;
942 if g_debug then
943 l_proc := g_package||'del';
944 hr_utility.set_location('Entering:'||l_proc, 5);
945 end if;
946 --
947 -- As the delete procedure accepts a plsql record structure we do need to
948 -- convert the arguments into the record structure.
949 -- We don't need to call the supplied conversion argument routine as we
950 -- only need a few attributes.
951 --
952 l_rec.position_id := p_position_id;
953 l_rec.object_version_number := p_object_version_number;
954 l_rec.security_profile_id := p_security_profile_id;
955
956 --
957 -- Having converted the arguments into the psf_rec
958 -- plsql record structure we must call the corresponding entity
959 -- business process
960 --
961 del(l_rec, p_effective_date, p_datetrack_mode, p_validate);
962 --
963 -- Set the out arguments
964 --
965 p_object_version_number := l_rec.object_version_number;
966 p_effective_start_date := l_rec.effective_start_date;
967 p_effective_end_date := l_rec.effective_end_date;
968 --
969 if g_debug then
970 hr_utility.set_location(' Leaving:'||l_proc, 10);
971 end if;
972 End del;
973
974 end hr_psf_del;