[Home] [Help]
PACKAGE BODY: APPS.HR_PSF_DEL
Source
1 Package Body hr_psf_del as
2 /* $Header: hrpsfrhi.pkb 120.5.12000000.2 2007/02/06 06:59:04 ande 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 begin
268 select e.pos_structure_element_id
269 into l_pos_structure_element_id
270 from per_pos_structure_elements e
271 where e.parent_position_id = p_position_id
272 and not exists (
273 select null
274 from per_pos_structure_elements e2
275 where e2.subordinate_position_id = p_position_id)
276 and 1 = (
277 select count(e3.pos_structure_element_id)
278 from per_pos_structure_elements e3
279 where e3.parent_position_id = p_position_id);
280 exception when no_data_found then
281 null;
282 end;
283 if g_debug then
284 hr_utility.set_location(l_proc, 50);
285 end if;
286 l_exists := NULL;
287 if g_debug then
288 hr_utility.set_location(l_proc, 60);
289 end if;
290 if l_pos_structure_element_id is null then
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, 70);
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;
310 l_exists := NULL;
311 if g_debug then
312 hr_utility.set_location(l_proc, 80);
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, 90);
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, 100);
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, 110);
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, 120);
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, 130);
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, 140);
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
431 -- end if;
432
433 --
434 -- is po installed?
435 --
436 -- if p_po_ins = 'Y' then
437 begin
438 l_sql_text := 'select null '
439 ||' from sys.dual '
440 ||' where exists( select null '
441 ||' from po_system_parameters '
442 ||' where security_position_structure_id = '
443 ||to_char(p_position_id)
444 ||' ) '
445 ||' or exists( select null '
446 ||' from po_employee_hierarchies '
447 ||' where employee_position_id = '
448 ||to_char(p_position_id)
449 ||' or superior_position_id = '
450 ||to_char(p_position_id)
451 ||' ) ';
452 --
453 -- Open Cursor for Processing Sql statment.
454 --
455 if g_debug then
456 hr_utility.set_location(l_proc, 150);
457 end if;
458 l_sql_cursor := dbms_sql.open_cursor;
459 --
460 -- Parse SQL statement.
461 --
462 dbms_sql.parse(l_sql_cursor, l_sql_text, dbms_sql.v7);
463 --
464 -- Map the local variables to each returned Column
465 --
466 if g_debug then
467 hr_utility.set_location(l_proc, 160);
468 end if;
469 dbms_sql.define_column(l_sql_cursor, 1,l_oci_out,1);
470 --
471 -- Execute the SQL statement.
472 --
473 if g_debug then
474 hr_utility.set_location(l_proc, 170);
475 end if;
476 l_rows_fetched := dbms_sql.execute(l_sql_cursor);
477 --
478 if (dbms_sql.fetch_rows(l_sql_cursor) > 0)
479 then
480 hr_utility.set_message(800,'HR_6048_PO_POS_DEL_POS_CONT');
481 hr_utility.raise_error;
482 end if;
483 --
484 -- Close cursor used for processing SQL statement.
485 --
486 dbms_sql.close_cursor(l_sql_cursor);
487 if g_debug then
488 hr_utility.set_location(l_proc, 180);
489 end if;
490 end;
491 -- end if;
492 --
493 -- Ref Int check for OTA.
494 --
495 per_ota_predel_validation.ota_predel_pos_validation(p_position_id);
496 if g_debug then
497 hr_utility.set_location('Leaving : ' || l_proc, 300);
498 end if;
499 --
500 end if;
501 end pre_delete_checks;
502 --
503 -- ----------------------------------------------------------------------------
504 -- |------------------------------< pre_delete >------------------------------|
505 -- ----------------------------------------------------------------------------
506 -- {Start Of Comments}
507 --
508 -- Description:
509 -- This private procedure contains any processing which is required before
510 -- the delete dml.
511 --
512 -- Prerequisites:
513 -- This is an internal procedure which is called from the del procedure.
514 --
515 -- In Parameters:
516 -- A Pl/Sql record structre.
517 --
518 -- Post Success:
519 -- Processing continues.
520 --
521 -- Post Failure:
522 -- If an error has occurred, an error message and exception will be raised
523 -- but not handled.
524 --
525 -- Developer Implementation Notes:
526 -- Any pre-processing required before the delete dml is issued should be
527 -- coded within this procedure. It is important to note that any 3rd party
528 -- maintenance should be reviewed before placing in this procedure. The call
529 -- to the dt_delete_dml procedure should NOT be removed.
530 --
531 -- Access Status:
532 -- Internal Row Handler Use Only.
533 --
534 -- {End Of Comments}
535 -- ----------------------------------------------------------------------------
536 Procedure pre_delete
537 (p_rec in out nocopy hr_psf_shd.g_rec_type,
538 p_effective_date in date,
539 p_datetrack_mode in varchar2,
540 p_validation_start_date in date,
541 p_validation_end_date in date) is
542 --
543 l_proc varchar2(72) ;
544 --
545 cursor vgr is
546 select valid_grade_id, object_version_number from per_valid_grades
547 where position_id = p_rec.position_id;
548 Begin
549 if g_debug then
550 l_proc := g_package||'pre_delete';
551 hr_utility.set_location('Entering:'||l_proc, 5);
552 end if;
553 -- pre delete checks
554 pre_delete_checks(p_position_id => p_rec.position_id
555 ,p_business_group_id => p_rec.business_group_id
556 ,p_datetrack_mode => p_datetrack_mode);
557 /*
558 --
559 -- Delete record from PER_POSITION_LIST
560 --
561 if (p_datetrack_mode = 'ZAP') then
562 open c1;
563 fetch c1 into l_view_all_positions_flag;
564 close c1;
565 --
566 if l_view_all_positions_flag <> 'Y' then
567 Delete from PER_POSITION_LIST
568 WHERE position_id = p_rec.Position_id;
569 end if;
570 end if;
571 */
572 --
573 -- Delete from per_valid_grades rows corresponding to the position being deleted.
574 --
575 if (p_datetrack_mode = 'ZAP') then
576 for each_rec in vgr loop
577 per_vgr_del.del(p_valid_grade_id => each_rec.valid_grade_id,
578 p_object_version_number => each_rec.object_version_number);
579 end loop;
580 end if;
581 --
582 dt_pre_delete
583 (p_rec => p_rec,
584 p_effective_date => p_effective_date,
585 p_datetrack_mode => p_datetrack_mode,
586 p_validation_start_date => p_validation_start_date,
587 p_validation_end_date => p_validation_end_date);
588 --
589 if g_debug then
590 hr_utility.set_location(' Leaving:'||l_proc, 10);
591 end if;
592 End pre_delete;
593 --
594 -- ----------------------------------------------------------------------------
595 -- |-----------------------------< post_delete >------------------------------|
596 -- ----------------------------------------------------------------------------
597 -- {Start Of Comments}
598 --
599 -- Description:
600 -- This private procedure contains any processing which is required after the
601 -- delete dml.
602 --
603 -- Prerequisites:
604 -- This is an internal procedure which is called from the del procedure.
605 --
606 -- In Parameters:
607 -- A Pl/Sql record structre.
608 --
609 -- Post Success:
610 -- Processing continues.
611 --
612 -- Post Failure:
613 -- If an error has occurred, an error message and exception will be raised
614 -- but not handled
615 --
616 -- Developer Implementation Notes:
617 -- Any post-processing required after the delete dml is issued should be
618 -- coded within this procedure. It is important to note that any 3rd party
619 -- maintenance should be reviewed before placing in this procedure.
620 --
621 -- Access Status:
622 -- Internal Row Handler Use Only.
623 --
624 -- {End Of Comments}
625 -- ----------------------------------------------------------------------------
626 Procedure post_delete
627 (p_rec in hr_psf_shd.g_rec_type,
628 p_effective_date in date,
629 p_datetrack_mode in varchar2,
630 p_validation_start_date in date,
631 p_validation_end_date in date) is
632 --
633 l_proc varchar2(72) ;
634 --
635 Begin
636 if g_debug then
637 l_proc := g_package||'post_delete';
638 --
639 hr_utility.set_location('Entering:'||l_proc, 5);
640 end if;
641 --
642 -- User Hook
643 --
644 Begin
645 hr_psf_rkd.after_delete(
646 p_position_id => p_rec.position_id ,
647 p_effective_date => p_effective_date ,
648 p_effective_start_date_o => hr_psf_shd.g_old_rec.effective_start_date ,
649 p_effective_end_date_o => hr_psf_shd.g_old_rec.effective_end_date ,
650 p_availability_status_id_o => hr_psf_shd.g_old_rec.availability_status_id ,
651 p_business_group_id_o => hr_psf_shd.g_old_rec.business_group_id ,
652 p_entry_step_id_o => hr_psf_shd.g_old_rec.entry_step_id ,
653 p_entry_grade_rule_id_o => hr_psf_shd.g_old_rec.entry_grade_rule_id ,
654 p_job_id_o => hr_psf_shd.g_old_rec.job_id ,
655 p_location_id_o => hr_psf_shd.g_old_rec.location_id ,
656 p_organization_id_o => hr_psf_shd.g_old_rec.organization_id ,
657 p_pay_freq_payroll_id_o => hr_psf_shd.g_old_rec.pay_freq_payroll_id ,
658 p_position_definition_id_o => hr_psf_shd.g_old_rec.position_definition_id ,
659 p_position_transaction_id_o => hr_psf_shd.g_old_rec.position_transaction_id ,
660 p_prior_position_id_o => hr_psf_shd.g_old_rec.prior_position_id ,
661 p_relief_position_id_o => hr_psf_shd.g_old_rec.relief_position_id ,
662 p_entry_grade_id_o => hr_psf_shd.g_old_rec.entry_grade_id ,
663 p_successor_position_id_o => hr_psf_shd.g_old_rec.successor_position_id ,
664 p_supervisor_position_id_o => hr_psf_shd.g_old_rec.supervisor_position_id ,
665 p_amendment_date_o => hr_psf_shd.g_old_rec.amendment_date ,
666 p_amendment_recommendation_o => hr_psf_shd.g_old_rec.amendment_recommendation ,
667 p_amendment_ref_number_o => hr_psf_shd.g_old_rec.amendment_ref_number ,
668 p_bargaining_unit_cd_o => hr_psf_shd.g_old_rec.bargaining_unit_cd ,
669 p_comments_o => hr_psf_shd.g_old_rec.comments ,
670 p_current_job_prop_end_date_o => hr_psf_shd.g_old_rec.current_job_prop_end_date ,
671 p_current_org_prop_end_date_o => hr_psf_shd.g_old_rec.current_org_prop_end_date ,
672 p_avail_status_prop_end_date_o => hr_psf_shd.g_old_rec.avail_status_prop_end_date,
673 p_date_effective_o => hr_psf_shd.g_old_rec.date_effective ,
674 p_date_end_o => hr_psf_shd.g_old_rec.date_end ,
675 p_earliest_hire_date_o => hr_psf_shd.g_old_rec.earliest_hire_date ,
676 p_fill_by_date_o => hr_psf_shd.g_old_rec.fill_by_date ,
677 p_frequency_o => hr_psf_shd.g_old_rec.frequency ,
678 p_fte_o => hr_psf_shd.g_old_rec.fte ,
679 p_max_persons_o => hr_psf_shd.g_old_rec.max_persons ,
680 p_name_o => hr_psf_shd.g_old_rec.name ,
681 p_overlap_period_o => hr_psf_shd.g_old_rec.overlap_period ,
682 p_overlap_unit_cd_o => hr_psf_shd.g_old_rec.overlap_unit_cd ,
683 p_pay_term_end_day_cd_o => hr_psf_shd.g_old_rec.pay_term_end_day_cd ,
684 p_pay_term_end_month_cd_o => hr_psf_shd.g_old_rec.pay_term_end_month_cd ,
685 p_permanent_temporary_flag_o => hr_psf_shd.g_old_rec.permanent_temporary_flag ,
686 p_permit_recruitment_flag_o => hr_psf_shd.g_old_rec.permit_recruitment_flag ,
687 p_position_type_o => hr_psf_shd.g_old_rec.position_type ,
688 p_posting_description_o => hr_psf_shd.g_old_rec.posting_description ,
689 p_probation_period_o => hr_psf_shd.g_old_rec.probation_period ,
690 p_probation_period_unit_cd_o => hr_psf_shd.g_old_rec.probation_period_unit_cd ,
691 p_replacement_required_flag_o => hr_psf_shd.g_old_rec.replacement_required_flag ,
692 p_review_flag_o => hr_psf_shd.g_old_rec.review_flag ,
693 p_seasonal_flag_o => hr_psf_shd.g_old_rec.seasonal_flag ,
694 p_security_requirements_o => hr_psf_shd.g_old_rec.security_requirements ,
695 p_status_o => hr_psf_shd.g_old_rec.status ,
696 p_term_start_day_cd_o => hr_psf_shd.g_old_rec.term_start_day_cd ,
697 p_term_start_month_cd_o => hr_psf_shd.g_old_rec.term_start_month_cd ,
698 p_time_normal_finish_o => hr_psf_shd.g_old_rec.time_normal_finish ,
699 p_time_normal_start_o => hr_psf_shd.g_old_rec.time_normal_start ,
700 p_update_source_cd_o => hr_psf_shd.g_old_rec.update_source_cd ,
701 p_working_hours_o => hr_psf_shd.g_old_rec.working_hours ,
702 p_works_council_approval_fla_o => hr_psf_shd.g_old_rec.works_council_approval_flag,
703 p_work_period_type_cd_o => hr_psf_shd.g_old_rec.work_period_type_cd ,
704 p_work_term_end_day_cd_o => hr_psf_shd.g_old_rec.work_term_end_day_cd ,
705 p_work_term_end_month_cd_o => hr_psf_shd.g_old_rec.work_term_end_month_cd ,
706 p_proposed_fte_for_layoff_o => hr_psf_shd.g_old_rec.proposed_fte_for_layoff ,
707 p_proposed_date_for_layoff_o => hr_psf_shd.g_old_rec.proposed_date_for_layoff ,
708 p_pay_basis_id_o => hr_psf_shd.g_old_rec.pay_basis_id ,
709 p_supervisor_id_o => hr_psf_shd.g_old_rec.supervisor_id ,
710 p_copied_to_old_table_flag_o => hr_psf_shd.g_old_rec.copied_to_old_table_flag ,
711 p_information1_o => hr_psf_shd.g_old_rec.information1 ,
712 p_information2_o => hr_psf_shd.g_old_rec.information2 ,
713 p_information3_o => hr_psf_shd.g_old_rec.information3 ,
714 p_information4_o => hr_psf_shd.g_old_rec.information4 ,
715 p_information5_o => hr_psf_shd.g_old_rec.information5 ,
716 p_information6_o => hr_psf_shd.g_old_rec.information6 ,
717 p_information7_o => hr_psf_shd.g_old_rec.information7 ,
718 p_information8_o => hr_psf_shd.g_old_rec.information8 ,
719 p_information9_o => hr_psf_shd.g_old_rec.information9 ,
720 p_information10_o => hr_psf_shd.g_old_rec.information10 ,
721 p_information11_o => hr_psf_shd.g_old_rec.information11 ,
722 p_information12_o => hr_psf_shd.g_old_rec.information12 ,
723 p_information13_o => hr_psf_shd.g_old_rec.information13 ,
724 p_information14_o => hr_psf_shd.g_old_rec.information14 ,
725 p_information15_o => hr_psf_shd.g_old_rec.information15 ,
726 p_information16_o => hr_psf_shd.g_old_rec.information16 ,
727 p_information17_o => hr_psf_shd.g_old_rec.information17 ,
728 p_information18_o => hr_psf_shd.g_old_rec.information18 ,
729 p_information19_o => hr_psf_shd.g_old_rec.information19 ,
730 p_information20_o => hr_psf_shd.g_old_rec.information20 ,
731 p_information21_o => hr_psf_shd.g_old_rec.information21 ,
732 p_information22_o => hr_psf_shd.g_old_rec.information22 ,
733 p_information23_o => hr_psf_shd.g_old_rec.information23 ,
734 p_information24_o => hr_psf_shd.g_old_rec.information24 ,
735 p_information25_o => hr_psf_shd.g_old_rec.information25 ,
736 p_information26_o => hr_psf_shd.g_old_rec.information26 ,
737 p_information27_o => hr_psf_shd.g_old_rec.information27 ,
738 p_information28_o => hr_psf_shd.g_old_rec.information28 ,
739 p_information29_o => hr_psf_shd.g_old_rec.information29 ,
740 p_information30_o => hr_psf_shd.g_old_rec.information30 ,
741 p_information_category_o => hr_psf_shd.g_old_rec.information_category ,
742 p_attribute1_o => hr_psf_shd.g_old_rec.attribute1 ,
743 p_attribute2_o => hr_psf_shd.g_old_rec.attribute2 ,
744 p_attribute3_o => hr_psf_shd.g_old_rec.attribute3 ,
745 p_attribute4_o => hr_psf_shd.g_old_rec.attribute4 ,
746 p_attribute5_o => hr_psf_shd.g_old_rec.attribute5 ,
747 p_attribute6_o => hr_psf_shd.g_old_rec.attribute6 ,
748 p_attribute7_o => hr_psf_shd.g_old_rec.attribute7 ,
749 p_attribute8_o => hr_psf_shd.g_old_rec.attribute8 ,
750 p_attribute9_o => hr_psf_shd.g_old_rec.attribute9 ,
751 p_attribute10_o => hr_psf_shd.g_old_rec.attribute10 ,
752 p_attribute11_o => hr_psf_shd.g_old_rec.attribute11 ,
753 p_attribute12_o => hr_psf_shd.g_old_rec.attribute12 ,
754 p_attribute13_o => hr_psf_shd.g_old_rec.attribute13 ,
755 p_attribute14_o => hr_psf_shd.g_old_rec.attribute14 ,
756 p_attribute15_o => hr_psf_shd.g_old_rec.attribute15 ,
757 p_attribute16_o => hr_psf_shd.g_old_rec.attribute16 ,
758 p_attribute17_o => hr_psf_shd.g_old_rec.attribute17 ,
759 p_attribute18_o => hr_psf_shd.g_old_rec.attribute18 ,
760 p_attribute19_o => hr_psf_shd.g_old_rec.attribute19 ,
761 p_attribute20_o => hr_psf_shd.g_old_rec.attribute20 ,
762 p_attribute21_o => hr_psf_shd.g_old_rec.attribute21 ,
763 p_attribute22_o => hr_psf_shd.g_old_rec.attribute22 ,
764 p_attribute23_o => hr_psf_shd.g_old_rec.attribute23 ,
765 p_attribute24_o => hr_psf_shd.g_old_rec.attribute24 ,
766 p_attribute25_o => hr_psf_shd.g_old_rec.attribute25 ,
767 p_attribute26_o => hr_psf_shd.g_old_rec.attribute26 ,
768 p_attribute27_o => hr_psf_shd.g_old_rec.attribute27 ,
769 p_attribute28_o => hr_psf_shd.g_old_rec.attribute28 ,
770 p_attribute29_o => hr_psf_shd.g_old_rec.attribute29 ,
771 p_attribute30_o => hr_psf_shd.g_old_rec.attribute30 ,
772 p_attribute_category_o => hr_psf_shd.g_old_rec.attribute_category ,
773 p_request_id_o => hr_psf_shd.g_old_rec.request_id ,
774 p_program_application_id_o => hr_psf_shd.g_old_rec.program_application_id ,
775 p_program_id_o => hr_psf_shd.g_old_rec.program_id ,
776 p_program_update_date_o => hr_psf_shd.g_old_rec.program_update_date ,
777 p_object_version_number_o => hr_psf_shd.g_old_rec.object_version_number );
778 --
779 exception
780 when hr_api.cannot_find_prog_unit then
781 hr_api.cannot_find_prog_unit_error
782 (p_module_name => 'HR_ALL_POSITIONS'
783 ,p_hook_type => 'AD'
784 );
785 end;
786 --
787 hr_psf_shd.position_wf_sync(p_rec.position_id , p_validation_start_date);
788 --
789 if g_debug then
790 hr_utility.set_location(' Leaving:'||l_proc, 10);
791 end if;
792 End post_delete;
793 --
794 -- ----------------------------------------------------------------------------
795 -- |---------------------------------< del >----------------------------------|
796 -- ----------------------------------------------------------------------------
797 Procedure del
798 (
799 p_rec in out nocopy hr_psf_shd.g_rec_type,
800 p_effective_date in date,
801 p_datetrack_mode in varchar2,
802 p_validate in boolean default false
803 ) is
804 --
805 l_proc varchar2(72) ;
806 l_validation_start_date date;
807 l_validation_end_date date;
808 --
809 cursor c1 is
810 select availability_status_id
811 from hr_all_positions_f
812 where position_id = p_rec.position_id;
813 --
814 Begin
815 g_debug := hr_utility.debug_enabled;
816 if g_debug then
817 l_proc := g_package||'del';
818 hr_utility.set_location('Entering:'||l_proc, 5);
819 end if;
820 --
821 -- Ensure that the DateTrack delete mode is valid
822 --
823 dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
824 --
825 -- We must lock the row which we need to delete.
826 --
827 --
828 -- Determine if the business process is to be validated.
829 --
830 If p_validate then
831 --
832 -- Issue the savepoint.
833 --
834 SAVEPOINT del_per_per;
835 End If;
836 --
837 hr_psf_shd.lck
838 (p_effective_date => p_effective_date,
839 p_datetrack_mode => p_datetrack_mode,
840 p_position_id => p_rec.position_id,
841 p_object_version_number => p_rec.object_version_number,
842 p_validation_start_date => l_validation_start_date,
843 p_validation_end_date => l_validation_end_date);
844 --
845 -- get availability_status
846 --
847 if p_rec.availability_status_id is null then
848 open c1;
849 fetch c1 into p_rec.availability_status_id;
850 close c1;
851 end if;
852 --
853 -- Call the supporting delete validate operation
854 --
855 hr_psf_bus.delete_validate
856 (p_rec => p_rec,
857 p_effective_date => p_effective_date,
858 p_datetrack_mode => p_datetrack_mode,
859 p_validation_start_date => l_validation_start_date,
860 p_validation_end_date => l_validation_end_date);
861 --
862 -- Call the supporting pre-delete operation
863 --
864 pre_delete
865 (p_rec => p_rec,
866 p_effective_date => p_effective_date,
867 p_datetrack_mode => p_datetrack_mode,
868 p_validation_start_date => l_validation_start_date,
869 p_validation_end_date => l_validation_end_date);
870 --
871 -- Delete the row.
872 --
873 delete_dml
874 (p_rec => p_rec,
875 p_effective_date => p_effective_date,
876 p_datetrack_mode => p_datetrack_mode,
877 p_validation_start_date => l_validation_start_date,
878 p_validation_end_date => l_validation_end_date);
879 --
880 -- Call the supporting post-delete operation
881 --
882 post_delete
883 (p_rec => p_rec,
884 p_effective_date => p_effective_date,
885 p_datetrack_mode => p_datetrack_mode,
886 p_validation_start_date => l_validation_start_date,
887 p_validation_end_date => l_validation_end_date);
888 --
889 -- If we are validating then raise the Validate_Enabled exception
890 --
891 If p_validate then
892 Raise HR_Api.Validate_Enabled;
893 End If;
894 --
895 if g_debug then
896 hr_utility.set_location(' Leaving:'||l_proc, 10);
897 end if;
898
899 Exception
900 When HR_Api.Validate_Enabled Then
901 --
902 -- As the Validate_Enabled exception has been raised
903 -- we must rollback to the savepoint
904 --
905 ROLLBACK TO del_per_per;
906 End del;
907 --
908 -- ----------------------------------------------------------------------------
909 -- |---------------------------------< del >----------------------------------|
910 -- ----------------------------------------------------------------------------
911 Procedure del
912 (
913 p_position_id in number,
914 p_effective_start_date out nocopy date,
915 p_effective_end_date out nocopy date,
916 p_object_version_number in out nocopy number,
917 p_effective_date in date,
918 p_datetrack_mode in varchar2,
919 p_validate in boolean default false,
920 p_security_profile_id in number default hr_security.get_security_profile
921 ) is
922 --
923 l_rec hr_psf_shd.g_rec_type;
924 l_proc varchar2(72);
925 --
926 Begin
927 g_debug := hr_utility.debug_enabled;
928 if g_debug then
929 l_proc := g_package||'del';
930 hr_utility.set_location('Entering:'||l_proc, 5);
931 end if;
932 --
933 -- As the delete procedure accepts a plsql record structure we do need to
934 -- convert the arguments into the record structure.
935 -- We don't need to call the supplied conversion argument routine as we
936 -- only need a few attributes.
937 --
938 l_rec.position_id := p_position_id;
939 l_rec.object_version_number := p_object_version_number;
940 l_rec.security_profile_id := p_security_profile_id;
941
942 --
943 -- Having converted the arguments into the psf_rec
944 -- plsql record structure we must call the corresponding entity
945 -- business process
946 --
947 del(l_rec, p_effective_date, p_datetrack_mode, p_validate);
948 --
949 -- Set the out arguments
950 --
951 p_object_version_number := l_rec.object_version_number;
952 p_effective_start_date := l_rec.effective_start_date;
953 p_effective_end_date := l_rec.effective_end_date;
954 --
955 if g_debug then
956 hr_utility.set_location(' Leaving:'||l_proc, 10);
957 end if;
958 End del;
959
960 end hr_psf_del;