[Home] [Help]
PACKAGE BODY: APPS.HR_PSF_SHD
Source
1 Package Body hr_psf_shd 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_shd.'; -- Global package name
9 --
10 function get_availability_status(p_availability_status_id number
11 ,p_business_group_id number)
12 return varchar2 is
13 cursor c1 is select system_type_cd
14 from per_shared_types
15 where shared_type_id = p_availability_status_id
16 and lookup_type ='POSITION_AVAILABILITY_STATUS'
17 and (business_group_id = p_business_group_id or business_group_id is null);
18 l_avail_status varchar2(30);
19 begin
20 open c1;
21 fetch c1 into l_avail_status;
22 if c1%notfound then
23 close c1;
24 return null ;
25 else
26 close c1;
27 end if;
28 return l_avail_status;
29 end;
30 --
31 procedure get_position_job_org(p_position_id number,
32 p_effective_date date default sysdate,
33 p_job_id out nocopy number,
34 p_organization_id out nocopy number
35 ) is
36 cursor c1 is select job_id, organization_id
37 from hr_all_positions_f
38 where position_id = p_position_id
39 and p_effective_date
40 between effective_start_date
41 and effective_end_date;
42 l_job_id number;
43 l_organization_id number;
44 begin
45 open c1;
46 fetch c1 into p_job_id, p_organization_id;
47 close c1;
48 end;
49 ---
50 ---
51 ---
52 function POS_SYSTEM_AVAILABILITY_STATUS (
53 --
54 p_position_id number,
55 p_effective_date date) return varchar2 is
56 --
57 cursor csr_lookup is
58 select system_type_cd
59 from per_shared_types sht, hr_all_positions_f psf
60 where shared_type_id = psf.availability_status_id
61 and psf.position_id = p_position_id
62 and p_effective_date between psf.effective_start_date and psf.effective_end_date;
63 --
64 v_meaning varchar2(30) := null;
65 --
66 begin
67 --
68 -- Only open the cursor if the parameter is going to retrieve anything
69 --
70 if p_position_id is not null then
71 --
72 open csr_lookup;
73 fetch csr_lookup into v_meaning;
74 close csr_lookup;
75 --
76 end if;
77 return v_meaning;
78 end pos_system_availability_status;
79 --
80 --
81 --
82 function SYSTEM_AVAILABILITY_STATUS (
83 --
84 p_availability_status_id number) return varchar2 is
85 --
86 cursor csr_lookup is
87 select system_type_cd
88 from per_shared_types
89 where shared_type_id = p_availability_status_id;
90 --
91 v_meaning varchar2(30) := null;
92 --
93 begin
94 --
95 -- Only open the cursor if the parameter is going to retrieve anything
96 --
97 if p_availability_status_id is not null then
98 --
99 open csr_lookup;
100 fetch csr_lookup into v_meaning;
101 close csr_lookup;
102 --
103 end if;
104 return v_meaning;
105 end system_availability_status;
106
107 procedure position_wf_sync(p_position_id number, p_effective_date date) is
108 myparms wf_parameter_list_t;
109 l_future_change boolean;
110 l_position_id varchar2(15);
111 l_future_date date;
112 l_proc varchar2(30);
113
114 begin
115 g_debug := hr_utility.debug_enabled;
116 if g_debug then
117 l_proc := g_package||'position_wf_sync';
118 end if;
119
120 l_position_id := p_position_id;
121 --
122 hr_psf_shd.my_synch_routine(l_position_id);
123 if g_debug then
124 hr_utility.set_location('After my_synch_routine - ' ||l_proc, 16);
125 end if;
126 end;
127 --
128 --
129 -- ----------------------------------------------------------------------------
130 -- |---------------------------< my_synch_routine >---------------------------|
131 -- ----------------------------------------------------------------------------
132 --
133 procedure my_synch_routine(mykey in varchar2) is
134 --
135 l_position_id number := to_number(mykey);
136 l_plist wf_parameter_list_t;
137 l_proc varchar2(30);
138 l_dummy varchar2(10);
139 l_start_date date;
140 l_expiration_date date;
141 cnt number;
142 l_name hr_all_positions_f_tl.name%type;
143 --
144 begin
145 --
146 g_debug := hr_utility.debug_enabled;
147 if g_debug then
148 l_proc := g_package||'MY_SYNC_ROUTINE';
149 hr_utility.set_location('Before calling WF_SYNC package:'||l_proc, 30);
150 end if;
151
152 select count(*) into cnt from hr_all_positions_f where position_id = l_position_id and availability_status_id = 1;
153 if cnt = 0 then
154 select hr_general.effective_date into l_expiration_date from dual;
155
156 wf_event.AddParameterToList( 'USER_NAME', 'POS'||':'||l_position_id, l_plist);
157 wf_event.AddParameterToList( 'DISPLAYNAME', '-', l_plist);
158 wf_event.AddParameterToList( 'DESCRIPTION', '-', l_plist);
159 wf_event.AddParameterToList( 'orclWFOrigSystem','POS',l_plist);
160 wf_event.AddParameterToList( 'orclWFOrigSystemID',l_position_id,l_plist);
161 wf_event.AddParameterToList( 'orclWorkFlowNotificationPref', 'QUERY', l_plist);
162 wf_event.AddParameterToList( 'orclIsEnabled', 'ACTIVE', l_plist);
163 wf_event.AddParameterToList( 'ExpirationDate',to_char(l_expiration_date,wf_engine.date_format), l_plist);
164 wf_event.AddParameterToList( 'WFSYNCH_OVERWRITE','TRUE',l_plist);
165 -- wf_event.AddParameterToList( 'Raiseerrors', 'TRUE', l_plist);
166
167 WF_LOCAL_SYNCH.propagate_role(
168 p_orig_system => 'POS',
169 p_orig_system_id => l_position_id,
170 p_attributes => l_plist,
171 p_expiration_date => l_expiration_date);
172 else
173 select min(effective_start_date), max(effective_end_date)
174 into l_start_date, l_expiration_date from hr_all_positions_f
175 where position_id = l_position_id and availability_status_id = 1;
176
177 begin
178 select name into l_name from hr_all_positions_f_tl where position_id = l_position_id and language = userenv('LANG');
179 exception when others then
180 null;
181 end;
182
183 wf_event.AddParameterToList( 'USER_NAME', 'POS'||':'||l_position_id, l_plist);
184 wf_event.AddParameterToList( 'DISPLAYNAME', l_name, l_plist);
185 wf_event.AddParameterToList( 'DESCRIPTION', l_name, l_plist);
186 wf_event.AddParameterToList( 'orclWFOrigSystem','POS',l_plist);
187 wf_event.AddParameterToList( 'orclWFOrigSystemID',l_position_id,l_plist);
188 wf_event.AddParameterToList( 'orclWorkFlowNotificationPref', 'QUERY', l_plist);
189 wf_event.AddParameterToList( 'orclIsEnabled', 'ACTIVE', l_plist);
190 wf_event.AddParameterToList( 'ExpirationDate',to_char(l_expiration_date,wf_engine.date_format), l_plist);
191 wf_event.AddParameterToList( 'WFSYNCH_OVERWRITE','TRUE',l_plist);
192 -- wf_event.AddParameterToList( 'Raiseerrors', 'TRUE', l_plist);
193
194 if g_debug then
195 hr_utility.set_location('l_start_date is '||l_start_date, 20);
196 end if;
197
198 WF_LOCAL_SYNCH.propagate_role(
199 p_orig_system => 'POS',
200 p_orig_system_id => l_position_id,
201 p_attributes => l_plist,
202 p_start_date => l_start_date,
203 p_expiration_date => l_expiration_date);
204
205
206
207 end if;
208
209 if g_debug then
210 hr_utility.set_location('After calling WF_SYNC package:'||l_proc, 30);
211 end if;
212 --
213 end my_synch_routine;
214
215 --
216 -- ----------------------------------------------------------------------------
217 -- |---------------------------< constraint_error >---------------------------|
218 -- ----------------------------------------------------------------------------
219 Procedure constraint_error
220 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
221 --
222 l_proc varchar2(72);
223 --
224 Begin
225 g_debug := hr_utility.debug_enabled;
226 if g_debug then
227 l_proc := g_package||'constraint_error';
228 hr_utility.set_location('Entering:'||l_proc, 5);
229 end if;
230 --
231 If (p_constraint_name = 'HR_ALL_POSITIONS_F_FK11') Then
232 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
233 hr_utility.set_message_token('PROCEDURE', l_proc);
234 hr_utility.set_message_token('STEP','5');
235 hr_utility.raise_error;
236 ElsIf (p_constraint_name = 'HR_ALL_POSITIONS_F_FK12') Then
237 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
238 hr_utility.set_message_token('PROCEDURE', l_proc);
239 hr_utility.set_message_token('STEP','10');
240 hr_utility.raise_error;
241 ElsIf (p_constraint_name = 'HR_ALL_POSITIONS_F_FK4') Then
242 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
243 hr_utility.set_message_token('PROCEDURE', l_proc);
244 hr_utility.set_message_token('STEP','15');
245 hr_utility.raise_error;
246 ElsIf (p_constraint_name = 'HR_ALL_POSITIONS_F_FK5') Then
247 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
248 hr_utility.set_message_token('PROCEDURE', l_proc);
249 hr_utility.set_message_token('STEP','20');
250 hr_utility.raise_error;
251 ElsIf (p_constraint_name = 'HR_ALL_POSITIONS_F_FK6') Then
252 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
253 hr_utility.set_message_token('PROCEDURE', l_proc);
254 hr_utility.set_message_token('STEP','25');
255 hr_utility.raise_error;
256 ElsIf (p_constraint_name = 'HR_ALL_POSITIONS_F_FK7') Then
257 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
258 hr_utility.set_message_token('PROCEDURE', l_proc);
259 hr_utility.set_message_token('STEP','30');
260 hr_utility.raise_error;
261 ElsIf (p_constraint_name = 'HR_ALL_POSITIONS_F_PK') Then
262 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
263 hr_utility.set_message_token('PROCEDURE', l_proc);
264 hr_utility.set_message_token('STEP','35');
265 hr_utility.raise_error;
266 Else
267 hr_utility.set_message(800, 'HR_7877_API_INVALID_CONSTRAINT');
268 hr_utility.set_message_token('PROCEDURE', l_proc);
269 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
270 hr_utility.raise_error;
271 End If;
272 --
273 if g_debug then
274 hr_utility.set_location(' Leaving:'||l_proc, 10);
275 end if;
276 End constraint_error;
277 --
278 -- ----------------------------------------------------------------------------
279 -- |-----------------------------< api_updating >-----------------------------|
280 -- ----------------------------------------------------------------------------
281 Function api_updating
282 (p_effective_date in date,
283 p_position_id in number,
284 p_object_version_number in number
285 ) Return Boolean Is
286 --
287 --
288 -- Cursor selects the 'current' row from the HR Schema
289 --
290 Cursor C_Sel1 is
291 select
292 position_id,
293 effective_start_date,
294 effective_end_date,
295 availability_status_id,
296 business_group_id,
297 entry_step_id,
298 entry_grade_rule_id,
299 job_id,
300 location_id,
301 organization_id,
302 pay_freq_payroll_id,
303 position_definition_id,
304 position_transaction_id,
305 prior_position_id,
306 relief_position_id,
307 entry_grade_id,
308 successor_position_id,
309 supervisor_position_id,
310 amendment_date,
311 amendment_recommendation,
312 amendment_ref_number,
313 bargaining_unit_cd,
314 null,
315 current_job_prop_end_date,
316 current_org_prop_end_date,
317 avail_status_prop_end_date,
318 date_effective,
319 date_end,
320 earliest_hire_date,
321 fill_by_date,
322 frequency,
323 fte,
324 max_persons,
325 name,
326 overlap_period,
327 overlap_unit_cd,
328 pay_term_end_day_cd,
329 pay_term_end_month_cd,
330 permanent_temporary_flag,
331 permit_recruitment_flag,
332 position_type,
333 posting_description,
334 probation_period,
335 probation_period_unit_cd,
336 replacement_required_flag,
337 review_flag,
338 seasonal_flag,
339 security_requirements,
340 status,
341 term_start_day_cd,
342 term_start_month_cd,
343 time_normal_finish,
344 time_normal_start,
345 update_source_cd,
346 working_hours,
347 works_council_approval_flag,
348 work_period_type_cd,
349 work_term_end_day_cd,
350 work_term_end_month_cd,
351 proposed_fte_for_layoff,
352 proposed_date_for_layoff,
353 pay_basis_id ,
354 supervisor_id ,
355 copied_to_old_table_flag,
356 /*
357 position_id ,
358 effective_start_date ,
359 effective_end_date ,
360 availability_status_id ,
361 business_group_id ,
362 entry_step_id ,
363 entry_grade_rule_id ,
364 job_id ,
365 location_id ,
366 organization_id ,
367 pay_freq_payroll_id ,
368 position_definition_id ,
369 position_transaction_id ,
370 prior_position_id ,
371 relief_position_id ,
372 entry_grade_id ,
373 successor_position_id ,
374 supervisor_position_id ,
375 amendment_date ,
376 amendment_recommendation ,
377 amendment_ref_number ,
378 bargaining_unit_cd ,
379 comments ,
380 current_job_prop_end_date ,
381 current_org_prop_end_date ,
382 avail_status_prop_end_date,
383 date_effective ,
384 date_end ,
385 earliest_hire_date ,
386 fill_by_date ,
387 frequency ,
388 fte ,
392 overlap_unit_cd ,
389 max_persons ,
390 name ,
391 overlap_period ,
393 pay_term_end_day_cd ,
394 pay_term_end_month_cd ,
395 permanent_temporary_flag ,
396 permit_recruitment_flag ,
397 position_type ,
398 posting_description ,
399 probation_period ,
400 probation_period_unit_cd ,
401 replacement_required_flag ,
402 review_flag ,
403 seasonal_flag ,
404 security_requirements ,
405 status ,
406 term_start_day_cd ,
407 term_start_month_cd ,
408 time_normal_finish ,
409 time_normal_start ,
410 update_source_cd ,
411 working_hours ,
412 works_council_approval_flag,
413 work_period_type_cd ,
414 work_term_end_day_cd ,
415 work_term_end_month_cd ,
416 proposed_fte_for_layoff ,
417 proposed_date_for_layoff ,
418 pay_basis_id ,
419 supervisor_id ,
420 copied_to_old_table_flag ,
421 */
422 information1,
423 information2,
424 information3,
425 information4,
426 information5,
427 information6,
428 information7,
429 information8,
430 information9,
431 information10,
432 information11,
433 information12,
434 information13,
435 information14,
436 information15,
437 information16,
438 information17,
439 information18,
440 information19,
441 information20,
442 information21,
443 information22,
444 information23,
445 information24,
446 information25,
447 information26,
448 information27,
449 information28,
450 information29,
451 information30,
452 information_category,
453 attribute1,
454 attribute2,
455 attribute3,
456 attribute4,
457 attribute5,
458 attribute6,
459 attribute7,
460 attribute8,
461 attribute9,
462 attribute10,
463 attribute11,
464 attribute12,
465 attribute13,
466 attribute14,
467 attribute15,
468 attribute16,
469 attribute17,
470 attribute18,
471 attribute19,
472 attribute20,
473 attribute21,
474 attribute22,
475 attribute23,
476 attribute24,
477 attribute25,
478 attribute26,
479 attribute27,
480 attribute28,
481 attribute29,
482 attribute30,
483 attribute_category,
484 request_id,
485 program_application_id,
486 program_id,
487 program_update_date,
488 object_version_number,
489 null
490 from hr_all_positions_f
491 where position_id = p_position_id
492 and p_effective_date
493 between effective_start_date and effective_end_date;
494 --
495 l_proc varchar2(72);
496 l_fct_ret boolean;
497 --
498 Begin
499 g_debug := hr_utility.debug_enabled;
500 if g_debug then
501 l_proc := g_package||'api_updating';
502 hr_utility.set_location('Entering:'||l_proc, 5);
503 end if;
504 --
505 If (p_effective_date is null or
506 p_position_id is null or
507 p_object_version_number is null) Then
508 --
509 -- One of the primary key arguments is null therefore we must
510 -- set the returning function value to false
511 --
512 l_fct_ret := false;
513 Else
514 If (p_position_id = g_old_rec.position_id and
515 p_object_version_number = g_old_rec.object_version_number) Then
516 if g_debug then
517 hr_utility.set_location(l_proc, 10);
518 end if;
519 --
520 -- The g_old_rec is current therefore we must
521 -- set the returning function to true
522 --
523 l_fct_ret := true;
524 Else
525 --
526 -- Select the current row
527 --
528 Open C_Sel1;
529 Fetch C_Sel1 Into g_old_rec;
530 If C_Sel1%notfound Then
531 Close C_Sel1;
532 --
533 -- The primary key is invalid therefore we must error
534 --
535 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
536 hr_utility.raise_error;
537 End If;
538 Close C_Sel1;
539 If (p_object_version_number <> g_old_rec.object_version_number) Then
540 hr_utility.set_message(800, 'HR_7155_OBJECT_INVALID');
541 hr_utility.raise_error;
542 End If;
543 if g_debug then
544 hr_utility.set_location(l_proc, 15);
545 end if;
546 l_fct_ret := true;
547 End If;
548 End If;
549 if g_debug then
550 hr_utility.set_location(' Leaving:'||l_proc, 20);
551 end if;
552 Return (l_fct_ret);
553 --
554 End api_updating;
555 --
556 -- ----------------------------------------------------------------------------
557 -- |--------------------------< find_dt_del_modes >---------------------------|
558 -- ----------------------------------------------------------------------------
559 Procedure find_dt_del_modes
560 (p_effective_date in date,
561 p_base_key_value in number,
565 p_delete_next_change out nocopy boolean) is
562 p_zap out nocopy boolean,
563 p_delete out nocopy boolean,
564 p_future_change out nocopy boolean,
566 --
567 l_proc varchar2(72);
568 --
569 l_parent_key_value1 number;
570 l_parent_key_value2 number;
571 l_parent_key_value3 number;
572 -- l_parent_key_value4 number;
573 --
574 Cursor C_Sel1 Is
575 select t.supervisor_position_id,
576 t.successor_position_id,
577 t.relief_position_id
578 from hr_all_positions_f t
579 where t.position_id = p_base_key_value
580 and p_effective_date
581 between t.effective_start_date and t.effective_end_date;
582 --
583 Begin
584 g_debug := hr_utility.debug_enabled;
585 if g_debug then
586 l_proc := g_package||'find_dt_del_modes';
587 hr_utility.set_location('Entering:'||l_proc, 5);
588 end if;
589 Open C_Sel1;
590 Fetch C_Sel1 Into l_parent_key_value1,
591 l_parent_key_value2,
592 l_parent_key_value3;
593 If C_Sel1%notfound then
594 Close C_Sel1;
595 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
596 hr_utility.set_message_token('PROCEDURE', l_proc);
597 hr_utility.set_message_token('STEP','10');
598 hr_utility.raise_error;
599 End If;
600 Close C_Sel1;
601 --
602 -- Call the corresponding datetrack api
603 --
604 dt_api.find_dt_del_modes
605 (p_effective_date => p_effective_date,
606 p_base_table_name => 'hr_all_positions_f',
607 p_base_key_column => 'position_id',
608 p_base_key_value => p_base_key_value,
609 p_parent_table_name1 => 'hr_all_positions_f',
610 p_parent_key_column1 => 'successor_position_id',
611 p_parent_key_value1 => l_parent_key_value1,
612 p_parent_table_name2 => 'hr_all_positions_f',
613 p_parent_key_column2 => 'relief_position_id',
614 p_parent_key_value2 => l_parent_key_value2,
615 p_parent_table_name3 => 'hr_all_positions_f',
616 p_parent_key_column3 => 'supervisor_position_id',
617 p_parent_key_value3 => l_parent_key_value3,
618 p_zap => p_zap,
619 p_delete => p_delete,
620 p_future_change => p_future_change,
621 p_delete_next_change => p_delete_next_change);
622 --
623 p_delete := false ;
624
625 if g_debug then
626 hr_utility.set_location(' Leaving:'||l_proc, 10);
627 end if;
628 End find_dt_del_modes;
629 --
630 -- ----------------------------------------------------------------------------
631 -- |--------------------------< find_dt_upd_modes >---------------------------|
632 -- ----------------------------------------------------------------------------
633 Procedure find_dt_upd_modes
634 (p_effective_date in date,
635 p_base_key_value in number,
636 p_correction out nocopy boolean,
637 p_update out nocopy boolean,
638 p_update_override out nocopy boolean,
639 p_update_change_insert out nocopy boolean) is
640 --
641 l_proc varchar2(72);
642 --
643 Begin
644 g_debug := hr_utility.debug_enabled;
645 if g_debug then
646 l_proc := g_package||'find_dt_upd_modes';
647 hr_utility.set_location('Entering:'||l_proc, 5);
648 end if;
649 --
650 -- Call the corresponding datetrack api
651 --
652 dt_api.find_dt_upd_modes
653 (p_effective_date => p_effective_date,
654 p_base_table_name => 'hr_all_positions_f',
655 p_base_key_column => 'position_id',
656 p_base_key_value => p_base_key_value,
657 p_correction => p_correction,
658 p_update => p_update,
659 p_update_override => p_update_override,
660 p_update_change_insert => p_update_change_insert);
661 --
662 if g_debug then
663 hr_utility.set_location(' Leaving:'||l_proc, 10);
664 end if;
665 End find_dt_upd_modes;
666 --
667 -- ----------------------------------------------------------------------------
668 -- |------------------------< upd_effective_end_date >------------------------|
669 -- ----------------------------------------------------------------------------
670 Procedure upd_effective_end_date
671 (p_effective_date in date,
672 p_base_key_value in number,
673 p_new_effective_end_date in date,
674 p_validation_start_date in date,
675 p_validation_end_date in date,
676 p_object_version_number out nocopy number) is
677 --
678 l_proc varchar2(72);
679 l_object_version_number number;
680 --
681 Begin
682 g_debug := hr_utility.debug_enabled;
683 g_debug := hr_utility.debug_enabled;
684 if g_debug then
685 l_proc := g_package||'upd_effective_end_date';
686 hr_utility.set_location('Entering:'||l_proc, 5);
687 end if;
688 --
689 -- Because we are updating a row we must get the next object
690 -- version number.
691 --
692 l_object_version_number :=
693 dt_api.get_object_version_number
694 (p_base_table_name => 'hr_all_positions_f',
695 p_base_key_column => 'position_id',
696 p_base_key_value => p_base_key_value);
697 --
701 --
698 if g_debug then
699 hr_utility.set_location(l_proc, 10);
700 end if;
702 -- Update the specified datetrack row setting the effective
703 -- end date to the specified new effective end date.
704 --
705 update hr_all_positions_f t
706 set t.effective_end_date = p_new_effective_end_date,
707 t.object_version_number = l_object_version_number
708 where t.position_id = p_base_key_value
709 and p_effective_date
710 between t.effective_start_date and t.effective_end_date;
711 --
712 p_object_version_number := l_object_version_number;
713 if g_debug then
714 hr_utility.set_location(' Leaving:'||l_proc, 15);
715 end if;
716 --
717 Exception
718 When Others Then
719 Raise;
720 End upd_effective_end_date;
721 --
722 -- ----------------------------------------------------------------------------
723 -- |---------------------------------< lck >----------------------------------|
724 -- ----------------------------------------------------------------------------
725 Procedure lck
726 (p_effective_date in date,
727 p_datetrack_mode in varchar2,
728 p_position_id in number,
729 p_object_version_number in number,
730 p_validation_start_date out nocopy date,
731 p_validation_end_date out nocopy date) is
732 --
733 l_proc varchar2(72);
734 l_validation_start_date date;
735 l_validation_end_date date;
736 l_object_invalid exception;
737 l_argument varchar2(30);
738 --
739 -- Cursor C_Sel1 selects the current locked row as of session date
740 -- ensuring that the object version numbers match.
741 --
742 Cursor C_Sel1 is
743 select
744 position_id,
745 effective_start_date,
746 effective_end_date,
747 availability_status_id,
748 business_group_id,
749 entry_step_id,
750 entry_grade_rule_id,
751 job_id,
752 location_id,
753 organization_id,
754 pay_freq_payroll_id,
755 position_definition_id,
756 position_transaction_id,
757 prior_position_id,
758 relief_position_id,
759 entry_grade_id,
760 successor_position_id,
761 supervisor_position_id,
762 amendment_date,
763 amendment_recommendation,
764 amendment_ref_number,
765 bargaining_unit_cd,
766 comments,
767 current_job_prop_end_date,
768 current_org_prop_end_date,
769 avail_status_prop_end_date,
770 date_effective,
771 date_end,
772 earliest_hire_date,
773 fill_by_date,
774 frequency,
775 fte,
776 max_persons,
777 name,
778 overlap_period,
779 overlap_unit_cd,
780 pay_term_end_day_cd,
781 pay_term_end_month_cd,
782 permanent_temporary_flag,
783 permit_recruitment_flag,
784 position_type,
785 posting_description,
786 probation_period,
787 probation_period_unit_cd,
788 replacement_required_flag,
789 review_flag,
790 seasonal_flag,
791 security_requirements,
792 status,
793 term_start_day_cd,
794 term_start_month_cd,
795 time_normal_finish,
796 time_normal_start,
797 update_source_cd,
798 working_hours,
799 works_council_approval_flag,
800 work_period_type_cd,
801 work_term_end_day_cd,
802 work_term_end_month_cd,
803 proposed_fte_for_layoff,
804 proposed_date_for_layoff,
805 pay_basis_id ,
806 supervisor_id ,
807 copied_to_old_table_flag ,
808 information1,
809 information2,
810 information3,
811 information4,
812 information5,
813 information6,
814 information7,
815 information8,
816 information9,
817 information10,
818 information11,
819 information12,
820 information13,
821 information14,
822 information15,
823 information16,
824 information17,
825 information18,
826 information19,
827 information20,
828 information21,
829 information22,
830 information23,
831 information24,
832 information25,
833 information26,
834 information27,
835 information28,
836 information29,
837 information30,
838 information_category,
839 attribute1,
840 attribute2,
841 attribute3,
842 attribute4,
843 attribute5,
844 attribute6,
845 attribute7,
846 attribute8,
847 attribute9,
848 attribute10,
849 attribute11,
850 attribute12,
851 attribute13,
852 attribute14,
853 attribute15,
854 attribute16,
855 attribute17,
856 attribute18,
857 attribute19,
858 attribute20,
859 attribute21,
860 attribute22,
861 attribute23,
862 attribute24,
863 attribute25,
864 attribute26,
865 attribute27,
866 attribute28,
867 attribute29,
868 attribute30,
869 attribute_category,
870 request_id,
871 program_application_id,
872 program_id,
873 program_update_date,
874 object_version_number,
878 and p_effective_date
875 null
876 from hr_all_positions_f
877 where position_id = p_position_id
879 between effective_start_date and effective_end_date
880 for update nowait;
881 --
882 --
883 --
884 Begin
885 g_debug := hr_utility.debug_enabled;
886 if g_debug then
887 l_proc := g_package||'lck';
888 hr_utility.set_location('Entering:'||l_proc, 5);
889 end if;
890 --
891 -- Ensure that all the mandatory arguments are not null
892 --
893 hr_api.mandatory_arg_error(p_api_name => l_proc,
894 p_argument => 'effective_date',
895 p_argument_value => p_effective_date);
896 --
897 hr_api.mandatory_arg_error(p_api_name => l_proc,
898 p_argument => 'datetrack_mode',
899 p_argument_value => p_datetrack_mode);
900 --
901 hr_api.mandatory_arg_error(p_api_name => l_proc,
902 p_argument => 'position_id',
903 p_argument_value => p_position_id);
904 --
905 hr_api.mandatory_arg_error(p_api_name => l_proc,
906 p_argument => 'object_version_number',
907 p_argument_value => p_object_version_number);
908 --
909 -- Check to ensure the datetrack mode is not INSERT.
910 --
911 If (p_datetrack_mode <> 'INSERT') then
912 --
913 -- We must select and lock the current row.
914 --
915 Open C_Sel1;
916 Fetch C_Sel1 Into g_old_rec;
917 If C_Sel1%notfound then
918 Close C_Sel1;
919 --
920 -- The primary key is invalid therefore we must error
921 --
922 hr_utility.set_message(800, 'HR_7220_INVALID_PRIMARY_KEY');
923 hr_utility.raise_error;
924 End If;
925 Close C_Sel1;
926 If (p_object_version_number <> g_old_rec.object_version_number) Then
927 hr_utility.set_message(800, 'HR_7155_OBJECT_INVALID');
928 hr_utility.raise_error;
929 End If;
930 if g_debug then
931 hr_utility.set_location(l_proc, 15);
932 end if;
933 --
934 --
935 -- Validate the datetrack mode mode getting the validation start
936 -- and end dates for the specified datetrack operation.
937 --
938 if g_debug then
939 hr_utility.set_location(' effective date ' || p_Effective_Date || l_proc, 12125);
940 end if;
941
942 -- Bug 3199913
943 -- Removed refernce to 'per_all_assignments_f' since assignment and position
944 -- do not have parent-child relationship.
945 -- Removed refernce to 'pay_element_links_f' since element links and position
946 -- do not have parent-child relationship.
947 -- Removed reference to 'pay_payrolls_f'
948
949 dt_api.validate_dt_mode
950 (p_effective_date => p_effective_date,
951 p_datetrack_mode => p_datetrack_mode,
952 p_base_table_name => 'hr_all_positions_f',
953 p_base_key_column => 'position_id',
954 p_base_key_value => p_position_id,
955 p_parent_table_name1 => 'hr_all_positions_f',
956 p_parent_key_column1 => 'successor_position_id',
957 p_parent_key_value1 => g_old_rec.successor_position_id,
958 p_parent_table_name2 => 'hr_all_positions_f',
959 p_parent_key_column2 => 'relief_position_id',
960 p_parent_key_value2 => g_old_rec.relief_position_id,
961 p_parent_table_name3 => 'hr_all_positions_f',
962 p_parent_key_column3 => 'supervisor_position_id',
963 p_parent_key_value3 => g_old_rec.supervisor_position_id,
964 /*
965 p_child_table_name3 => 'hr_all_positions_f',
966 p_child_key_column3 => 'position_id',
967 p_child_table_name4 => 'hr_all_positions_f',
968 p_child_key_column4 => 'position_id',
969 */
970 p_enforce_foreign_locking => true,
971 p_validation_start_date => l_validation_start_date,
972 p_validation_end_date => l_validation_end_date);
973 Else
974 --
975 -- We are doing a datetrack 'INSERT' which is illegal within this
976 -- procedure therefore we must error (note: to lck on insert the
977 -- private procedure ins_lck should be called).
978 --
979 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
980 hr_utility.set_message_token('PROCEDURE', l_proc);
981 hr_utility.set_message_token('STEP','20');
982 hr_utility.raise_error;
983 End If;
984 --
985 -- Set the validation start and end date OUT arguments
986 --
987 p_validation_start_date := l_validation_start_date;
988 p_validation_end_date := l_validation_end_date;
989 --
990 if g_debug then
991 hr_utility.set_location(' Leaving:'||l_proc, 30);
992 end if;
993 --
994 -- We need to trap the ORA LOCK exception
995 --
996 Exception
997 When HR_Api.Object_Locked then
998 --
999 -- The object is locked therefore we need to supply a meaningful
1000 -- error message.
1001 --
1002 hr_utility.set_message(800, 'HR_7165_OBJECT_LOCKED');
1003 hr_utility.set_message_token('TABLE_NAME', 'hr_all_positions_f');
1007 -- The object doesn't exist or is invalid
1004 hr_utility.raise_error;
1005 When l_object_invalid then
1006 --
1008 --
1009 hr_utility.set_message(800, 'HR_7155_OBJECT_INVALID');
1010 hr_utility.set_message_token('TABLE_NAME', 'hr_all_positions_f');
1011 hr_utility.raise_error;
1012 End lck;
1013 --
1014 -- ----------------------------------------------------------------------------
1015 -- |-----------------------------< convert_args >-----------------------------|
1016 -- ----------------------------------------------------------------------------
1017 Function convert_args
1018 (
1019 p_position_id in number,
1020 p_effective_start_date in date,
1021 p_effective_end_date in date,
1022 p_availability_status_id in number,
1023 p_business_group_id in number,
1024 p_entry_step_id in number,
1025 p_entry_grade_rule_id in number,
1026 p_job_id in number,
1027 p_location_id in number,
1028 p_organization_id in number,
1029 p_pay_freq_payroll_id in number,
1030 p_position_definition_id in number,
1031 p_position_transaction_id in number,
1032 p_prior_position_id in number,
1033 p_relief_position_id in number,
1034 p_entry_grade_id in number,
1035 p_successor_position_id in number,
1036 p_supervisor_position_id in number,
1037 p_amendment_date in date,
1038 p_amendment_recommendation in varchar2,
1039 p_amendment_ref_number in varchar2,
1040 p_bargaining_unit_cd in varchar2,
1041 p_comments in varchar2,
1042 p_current_job_prop_end_date in date,
1043 p_current_org_prop_end_date in date,
1044 p_avail_status_prop_end_date in date,
1045 p_date_effective in date,
1046 p_date_end in date,
1047 p_earliest_hire_date in date,
1048 p_fill_by_date in date,
1049 p_frequency in varchar2,
1050 p_fte in number,
1051 p_max_persons in number,
1052 p_name in varchar2,
1053 p_overlap_period in number,
1054 p_overlap_unit_cd in varchar2,
1055 p_pay_term_end_day_cd in varchar2,
1056 p_pay_term_end_month_cd in varchar2,
1057 p_permanent_temporary_flag in varchar2,
1058 p_permit_recruitment_flag in varchar2,
1059 p_position_type in varchar2,
1060 p_posting_description in varchar2,
1061 p_probation_period in number,
1062 p_probation_period_unit_cd in varchar2,
1063 p_replacement_required_flag in varchar2,
1064 p_review_flag in varchar2,
1065 p_seasonal_flag in varchar2,
1066 p_security_requirements in varchar2,
1067 p_status in varchar2,
1068 p_term_start_day_cd in varchar2,
1069 p_term_start_month_cd in varchar2,
1070 p_time_normal_finish in varchar2,
1071 p_time_normal_start in varchar2,
1072 p_update_source_cd in varchar2,
1073 p_working_hours in number,
1074 p_works_council_approval_flag in varchar2,
1075 p_work_period_type_cd in varchar2,
1076 p_work_term_end_day_cd in varchar2,
1077 p_work_term_end_month_cd in varchar2,
1078 p_proposed_fte_for_layoff in number,
1079 p_proposed_date_for_layoff in date,
1080 p_pay_basis_id in number,
1081 p_supervisor_id in number,
1082 p_copied_to_old_table_flag in varchar2,
1083 p_information1 in varchar2,
1084 p_information2 in varchar2,
1085 p_information3 in varchar2,
1086 p_information4 in varchar2,
1087 p_information5 in varchar2,
1088 p_information6 in varchar2,
1089 p_information7 in varchar2,
1090 p_information8 in varchar2,
1091 p_information9 in varchar2,
1092 p_information10 in varchar2,
1093 p_information11 in varchar2,
1094 p_information12 in varchar2,
1095 p_information13 in varchar2,
1096 p_information14 in varchar2,
1097 p_information15 in varchar2,
1098 p_information16 in varchar2,
1099 p_information17 in varchar2,
1100 p_information18 in varchar2,
1101 p_information19 in varchar2,
1102 p_information20 in varchar2,
1103 p_information21 in varchar2,
1104 p_information22 in varchar2,
1105 p_information23 in varchar2,
1106 p_information24 in varchar2,
1107 p_information25 in varchar2,
1108 p_information26 in varchar2,
1109 p_information27 in varchar2,
1110 p_information28 in varchar2,
1111 p_information29 in varchar2,
1112 p_information30 in varchar2,
1116 p_attribute3 in varchar2,
1113 p_information_category in varchar2,
1114 p_attribute1 in varchar2,
1115 p_attribute2 in varchar2,
1117 p_attribute4 in varchar2,
1118 p_attribute5 in varchar2,
1119 p_attribute6 in varchar2,
1120 p_attribute7 in varchar2,
1121 p_attribute8 in varchar2,
1122 p_attribute9 in varchar2,
1123 p_attribute10 in varchar2,
1124 p_attribute11 in varchar2,
1125 p_attribute12 in varchar2,
1126 p_attribute13 in varchar2,
1127 p_attribute14 in varchar2,
1128 p_attribute15 in varchar2,
1129 p_attribute16 in varchar2,
1130 p_attribute17 in varchar2,
1131 p_attribute18 in varchar2,
1132 p_attribute19 in varchar2,
1133 p_attribute20 in varchar2,
1134 p_attribute21 in varchar2,
1135 p_attribute22 in varchar2,
1136 p_attribute23 in varchar2,
1137 p_attribute24 in varchar2,
1138 p_attribute25 in varchar2,
1139 p_attribute26 in varchar2,
1140 p_attribute27 in varchar2,
1141 p_attribute28 in varchar2,
1142 p_attribute29 in varchar2,
1143 p_attribute30 in varchar2,
1144 p_attribute_category in varchar2,
1145 p_request_id in number,
1146 p_program_application_id in number,
1147 p_program_id in number,
1148 p_program_update_date in date,
1149 p_object_version_number in number,
1150 p_security_profile_id in number
1151 )
1152 Return g_rec_type is
1153 --
1154 l_rec g_rec_type;
1155 l_proc varchar2(72);
1156 --
1157 Begin
1158 --
1159 g_debug := hr_utility.debug_enabled;
1160 if g_debug then
1161 l_proc := g_package||'convert_args';
1162 hr_utility.set_location('Entering:'||l_proc, 5);
1163 end if;
1164 --
1165 -- Convert arguments into local l_rec structure.
1166 --
1167 l_rec.position_id := p_position_id;
1168 l_rec.effective_start_date := p_effective_start_date;
1169 l_rec.effective_end_date := p_effective_end_date;
1170 l_rec.availability_status_id := p_availability_status_id;
1171 l_rec.business_group_id := p_business_group_id;
1172 l_rec.entry_step_id := p_entry_step_id;
1173 l_rec.entry_grade_rule_id := p_entry_grade_rule_id;
1174 l_rec.job_id := p_job_id;
1175 l_rec.location_id := p_location_id;
1176 l_rec.organization_id := p_organization_id;
1177 l_rec.pay_freq_payroll_id := p_pay_freq_payroll_id;
1178 l_rec.position_definition_id := p_position_definition_id;
1179 l_rec.position_transaction_id := p_position_transaction_id;
1180 l_rec.prior_position_id := p_prior_position_id;
1181 l_rec.relief_position_id := p_relief_position_id;
1182 l_rec.entry_grade_id := p_entry_grade_id;
1183 l_rec.successor_position_id := p_successor_position_id;
1184 l_rec.supervisor_position_id := p_supervisor_position_id;
1185 l_rec.amendment_date := p_amendment_date;
1186 l_rec.amendment_recommendation := p_amendment_recommendation;
1187 l_rec.amendment_ref_number := p_amendment_ref_number;
1188 l_rec.bargaining_unit_cd := p_bargaining_unit_cd;
1189 l_rec.comments := p_comments;
1190 l_rec.current_job_prop_end_date := p_current_job_prop_end_date;
1191 l_rec.current_org_prop_end_date := p_current_org_prop_end_date;
1192 l_rec.avail_status_prop_end_date := p_avail_status_prop_end_date;
1193 l_rec.date_effective := p_date_effective;
1194 l_rec.date_end := p_date_end;
1195 l_rec.earliest_hire_date := p_earliest_hire_date;
1196 l_rec.fill_by_date := p_fill_by_date;
1197 l_rec.frequency := p_frequency;
1198 l_rec.fte := p_fte;
1199 l_rec.max_persons := p_max_persons;
1200 l_rec.name := p_name;
1201 l_rec.overlap_period := p_overlap_period;
1202 l_rec.overlap_unit_cd := p_overlap_unit_cd;
1203 l_rec.pay_term_end_day_cd := p_pay_term_end_day_cd;
1204 l_rec.pay_term_end_month_cd := p_pay_term_end_month_cd;
1205 l_rec.permanent_temporary_flag := p_permanent_temporary_flag;
1206 l_rec.permit_recruitment_flag := p_permit_recruitment_flag;
1207 l_rec.position_type := p_position_type;
1208 l_rec.posting_description := p_posting_description;
1209 l_rec.probation_period := p_probation_period;
1210 l_rec.probation_period_unit_cd := p_probation_period_unit_cd;
1211 l_rec.replacement_required_flag := p_replacement_required_flag;
1212 l_rec.review_flag := p_review_flag;
1213 l_rec.seasonal_flag := p_seasonal_flag;
1214 l_rec.security_requirements := p_security_requirements;
1215 l_rec.status := p_status;
1219 l_rec.time_normal_start := p_time_normal_start;
1216 l_rec.term_start_day_cd := p_term_start_day_cd;
1217 l_rec.term_start_month_cd := p_term_start_month_cd;
1218 l_rec.time_normal_finish := p_time_normal_finish;
1220 l_rec.update_source_cd := p_update_source_cd;
1221 l_rec.working_hours := p_working_hours;
1222 l_rec.works_council_approval_flag := p_works_council_approval_flag;
1223 l_rec.work_period_type_cd := p_work_period_type_cd;
1224 l_rec.work_term_end_day_cd := p_work_term_end_day_cd;
1225 l_rec.work_term_end_month_cd := p_work_term_end_month_cd;
1226 l_rec.proposed_fte_for_layoff := p_proposed_fte_for_layoff;
1227 l_rec.proposed_date_for_layoff := p_proposed_date_for_layoff;
1228 l_rec.pay_basis_id := p_pay_basis_id;
1229 l_rec.supervisor_id := p_supervisor_id;
1230 l_rec.copied_to_old_table_flag := p_copied_to_old_table_flag;
1231 l_rec.information1 := p_information1;
1232 l_rec.information2 := p_information2;
1233 l_rec.information3 := p_information3;
1234 l_rec.information4 := p_information4;
1235 l_rec.information5 := p_information5;
1236 l_rec.information6 := p_information6;
1237 l_rec.information7 := p_information7;
1238 l_rec.information8 := p_information8;
1239 l_rec.information9 := p_information9;
1240 l_rec.information10 := p_information10;
1241 l_rec.information11 := p_information11;
1242 l_rec.information12 := p_information12;
1243 l_rec.information13 := p_information13;
1244 l_rec.information14 := p_information14;
1245 l_rec.information15 := p_information15;
1246 l_rec.information16 := p_information16;
1247 l_rec.information17 := p_information17;
1248 l_rec.information18 := p_information18;
1249 l_rec.information19 := p_information19;
1250 l_rec.information20 := p_information20;
1251 l_rec.information21 := p_information21;
1252 l_rec.information22 := p_information22;
1253 l_rec.information23 := p_information23;
1254 l_rec.information24 := p_information24;
1255 l_rec.information25 := p_information25;
1256 l_rec.information26 := p_information26;
1257 l_rec.information27 := p_information27;
1258 l_rec.information28 := p_information28;
1259 l_rec.information29 := p_information29;
1260 l_rec.information30 := p_information30;
1261 l_rec.information_category := p_information_category;
1262 l_rec.attribute1 := p_attribute1;
1263 l_rec.attribute2 := p_attribute2;
1264 l_rec.attribute3 := p_attribute3;
1265 l_rec.attribute4 := p_attribute4;
1266 l_rec.attribute5 := p_attribute5;
1267 l_rec.attribute6 := p_attribute6;
1268 l_rec.attribute7 := p_attribute7;
1269 l_rec.attribute8 := p_attribute8;
1270 l_rec.attribute9 := p_attribute9;
1271 l_rec.attribute10 := p_attribute10;
1272 l_rec.attribute11 := p_attribute11;
1273 l_rec.attribute12 := p_attribute12;
1274 l_rec.attribute13 := p_attribute13;
1275 l_rec.attribute14 := p_attribute14;
1276 l_rec.attribute15 := p_attribute15;
1277 l_rec.attribute16 := p_attribute16;
1278 l_rec.attribute17 := p_attribute17;
1279 l_rec.attribute18 := p_attribute18;
1280 l_rec.attribute19 := p_attribute19;
1281 l_rec.attribute20 := p_attribute20;
1282 l_rec.attribute21 := p_attribute21;
1283 l_rec.attribute22 := p_attribute22;
1284 l_rec.attribute23 := p_attribute23;
1285 l_rec.attribute24 := p_attribute24;
1286 l_rec.attribute25 := p_attribute25;
1287 l_rec.attribute26 := p_attribute26;
1288 l_rec.attribute27 := p_attribute27;
1289 l_rec.attribute28 := p_attribute28;
1290 l_rec.attribute29 := p_attribute29;
1291 l_rec.attribute30 := p_attribute30;
1292 l_rec.attribute_category := p_attribute_category;
1293 l_rec.request_id := p_request_id;
1294 l_rec.program_application_id := p_program_application_id;
1295 l_rec.program_id := p_program_id;
1296 l_rec.program_update_date := p_program_update_date;
1297 l_rec.object_version_number := p_object_version_number;
1298 l_rec.security_profile_id := p_security_profile_id;
1299 --
1300 -- Return the plsql record structure.
1301 --
1302 if g_debug then
1303 hr_utility.set_location(' Leaving:'||l_proc, 10);
1304 end if;
1305 Return(l_rec);
1306 --
1307 End convert_args;
1308 --
1309 end hr_psf_shd;