1 package body per_qh_populate as
2 /* $Header: peqhpopl.pkb 120.3.12020000.4 2012/10/09 13:00:51 lbodired ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33):='per_qh_populate.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |---------------------------< get_location >----------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 function get_location
14 (p_location_id number) return varchar2 is
15 --
16 l_proc varchar2(72) :=g_package||'get_location';
17 l_location_name hr_locations.location_code%type;
18 --
19 cursor csr_location is
20 /*select location_code
21 from hr_locations
22 where location_id=p_location_id;*/
23 --Modified as per bug 5504410
24 --
25 SELECT lot.location_code
26 from hr_locations_all hrl, hr_locations_all_tl lot
27 where hrl.location_id = lot.location_id
28 and lot.language = userenv('LANG')
29 and hrl.location_id=p_location_id;
30
31 begin
32 --
33 hr_utility.set_location('Entering: '||l_proc,10);
34 open csr_location;
35 fetch csr_location into l_location_name;
36 close csr_location;
37 hr_utility.set_location('Leaving: '||l_proc,20);
38 return l_location_name;
39 end get_location;
40 --
41 -- ----------------------------------------------------------------------------
42 -- |---------------------------< get_organization >----------------------------|
43 -- ----------------------------------------------------------------------------
44 --
45 function get_organization
46 (p_organization_id number) return varchar2 is
47 --
48 l_proc varchar2(72) :=g_package||'get_organization';
49 l_organization_name hr_all_organization_units.name%type;
50 --
51 cursor csr_organization is
52 select hotl.name
53 from hr_all_organization_units ho
54 , hr_all_organization_units_tl hotl
55 where ho.organization_id=p_organization_id
56 and hotl.organization_id=ho.organization_id
57 and hotl.language=userenv('LANG');
58 begin
59 --
60 hr_utility.set_location('Entering: '||l_proc,10);
61 if p_organization_id is not null then
62 open csr_organization;
63 fetch csr_organization into l_organization_name;
64 close csr_organization;
65 else
66 l_organization_name:=null;
67 end if;
68 hr_utility.set_location('Leaving: '||l_proc,20);
69 return l_organization_name;
70 end get_organization;
71 --
72 -- ----------------------------------------------------------------------------
73 -- |---------------------------< get_job >----------------------------|
74 -- ----------------------------------------------------------------------------
75 --
76 function get_job
77 (p_job_id number) return varchar2 is
78 --
79 l_proc varchar2(72) :=g_package||'get_job';
80 l_job_name per_jobs.name%type;
81 --
82 cursor csr_job is
83 select name
84 from per_jobs_vl
85 where job_id=p_job_id;
86 begin
87 --
88 hr_utility.set_location('Entering: '||l_proc,10);
89 if p_job_id is not null then
90 open csr_job;
91 fetch csr_job into l_job_name;
92 close csr_job;
93 else
94 l_job_name:=null;
95 end if;
96 hr_utility.set_location('Leaving: '||l_proc,20);
97 return l_job_name;
98 end get_job;
99 --
100 -- ----------------------------------------------------------------------------
101 -- |---------------------------< get_position >----------------------------|
102 -- ----------------------------------------------------------------------------
103 --
104 function get_position
105 (p_position_id number
106 ,p_effective_date date) return varchar2 is
107 --
108 l_proc varchar2(72) :=g_package||'get_position';
109 l_position_name hr_all_positions.name%type;
110 --
111 -- PMFLETCH - MLS enabled name
112 --
113 cursor csr_position is
114 select name
115 from hr_all_positions_f_tl
116 where position_id=p_position_id
117 and language = userenv('LANG');
118 -- PMFLETCH - Effective date no longer used, MLS name is always eot value
119 --and p_effective_date between effective_start_date and effective_end_date;
120 begin
121 --
122 hr_utility.set_location('Entering: '||l_proc,10);
123 if p_position_id is not null then
124 open csr_position;
125 fetch csr_position into l_position_name;
126 close csr_position;
127 else
128 l_position_name:=null;
129 end if;
130 hr_utility.set_location('Leaving: '||l_proc,20);
131 return l_position_name;
132 end get_position;
133 --
134 -- ----------------------------------------------------------------------------
135 -- |---------------------------< get_salary_basis >----------------------------|
136 -- ----------------------------------------------------------------------------
137 --
138 procedure get_salary_basis
139 (p_pay_basis_id IN number
140 ,p_pay_basis OUT NOCOPY varchar2
141 ,p_pay_basis_meaning OUT NOCOPY VARCHAR2
142 ,p_salary_basis OUT NOCOPY VARCHAR2) is
143 --
144 l_proc varchar2(72) :=g_package||'get_salary_basis';
145 l_salary_basis_name per_pay_bases.name%type;
146 --
147 cursor csr_salary_basis is
148 select ppb.name
149 , ppb.pay_basis
150 from per_pay_bases ppb
151 where ppb.pay_basis_id=p_pay_basis_id;
152 begin
153 --
154 hr_utility.set_location('Entering: '||l_proc,10);
155 open csr_salary_basis;
156 fetch csr_salary_basis into p_salary_basis,p_pay_basis;
157 close csr_salary_basis;
158 p_pay_basis_meaning:=hr_reports.get_lookup_meaning('PAY_BASIS',p_pay_basis);
159 hr_utility.set_location('Leaving: '||l_proc,20);
160 end get_salary_basis;
161 --
162 -- ----------------------------------------------------------------------------
163 -- |---------------------------< get_payroll >----------------------------|
164 -- ----------------------------------------------------------------------------
165 --
166 function get_payroll
167 (p_payroll_id number
168 ,p_effective_date date) return varchar2 is
169 --
170 l_proc varchar2(72) :=g_package||'get_payroll';
171 l_payroll_name pay_all_payrolls_f.payroll_name%type;
172 --
173 cursor csr_payroll is
174 select payroll_name
175 from pay_all_payrolls_f
176 where payroll_id=p_payroll_id
177 and p_effective_date between effective_start_date and effective_end_date;
178 begin
179 --
180 hr_utility.set_location('Entering: '||l_proc,10);
181 open csr_payroll;
182 fetch csr_payroll into l_payroll_name;
183 close csr_payroll;
184 hr_utility.set_location('Leaving: '||l_proc,20);
185 return l_payroll_name;
186 end get_payroll;
187 --
188 -- ----------------------------------------------------------------------------
189 -- |---------------------------< get_full_name >----------------------------|
190 -- ----------------------------------------------------------------------------
191 --
192 function get_full_name
193 (p_person_id number
194 ,p_effective_date date) return varchar2 is
195 --
196 l_proc varchar2(72) :=g_package||'get_full_name';
197 l_full_name per_all_people_f.full_name%type;
198 --
199 cursor csr_full_name is
200 select full_name
201 from per_all_people_f
202 where person_id=p_person_id
203 and p_effective_date between effective_start_date and effective_end_date;
204 begin
205 --
206 hr_utility.set_location('Entering: '||l_proc,10);
207 open csr_full_name;
208 fetch csr_full_name into l_full_name;
209 close csr_full_name;
210 hr_utility.set_location('Leaving: '||l_proc,20);
211 return l_full_name;
212 end get_full_name;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-----------------< get_supervisor_assgn_number >--------------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 function get_supervisor_assgn_number
219 (p_supervisor_assgn_id number
220 ,p_business_group_id number) return varchar2 is
221 --
222 l_proc varchar2(72) :=g_package||'get_supervisor_assgn_number';
223 l_super_assgn_num per_assignments_v.supervisor_assignment_number%type;
224 --
225 /* Fix for the bug 14195421
226 cursor csr_super_assgn_num is
227 select supervisor_assignment_number
228 from per_assignments_v
229 where supervisor_assignment_id = p_supervisor_assgn_id
230 and business_group_id = p_business_group_id; */
231
232 cursor csr_super_assgn_num is
233 select assignment_number
234 from per_all_assignments_f
235 where assignment_id = p_supervisor_assgn_id;
236 -- and business_group_id = p_business_group_id;
237
238 begin
239 --
240 hr_utility.set_location('Entering: '||l_proc,10);
241 open csr_super_assgn_num;
242 fetch csr_super_assgn_num into l_super_assgn_num;
243 close csr_super_assgn_num;
244 hr_utility.set_location('Leaving: '||l_proc,20);
245 return l_super_assgn_num;
246 end get_supervisor_assgn_number;
247 --
248 --
249 -- ----------------------------------------------------------------------------
250 -- |---------------------------< get_grade >----------------------------|
251 -- ----------------------------------------------------------------------------
252 --
253 function get_grade
254 (p_grade_id number) return varchar2 is
255 --
256 l_proc varchar2(72) :=g_package||'get_grade';
257 l_grade_name per_grades.name%type;
258 --
259 cursor csr_grade is
260 select name
261 from per_grades_vl
262 where grade_id=p_grade_id;
263 begin
264 --
265 hr_utility.set_location('Entering: '||l_proc,10);
266 open csr_grade;
267 fetch csr_grade into l_grade_name;
268 close csr_grade;
269 hr_utility.set_location('Leaving: '||l_proc,20);
270 return l_grade_name;
271 end get_grade;
272 --
273 --
274 -- ----------------------------------------------------------------------------
275 -- |--------------------------< get_grade_ladder >----------------------------|
276 -- ----------------------------------------------------------------------------
277 --
278 function get_grade_ladder
279 (p_grade_ladder_pgm_id number
280 ,p_effective_date date) return varchar2 is
281 --
282 l_proc varchar2(72) :=g_package||'get_grade_ladder';
283 l_grade_ladder_name ben_pgm_f.name%type;
284 --
285 cursor csr_grade_ladder is
286 select name
287 from ben_pgm_f
288 where pgm_id = p_grade_ladder_pgm_id
289 and p_effective_date
290 between effective_start_date and effective_end_date;
291 --
292 begin
293 --
294 hr_utility.set_location('Entering: '||l_proc,10);
295 open csr_grade_ladder;
296 fetch csr_grade_ladder into l_grade_ladder_name;
297 close csr_grade_ladder;
298 hr_utility.trace('l_grade_ladder_name : ' || l_grade_ladder_name);
299 hr_utility.set_location('Leaving: '||l_proc,20);
300 return l_grade_ladder_name;
301 end get_grade_ladder;
302 --
303 --
304 --
305 -- ----------------------------------------------------------------------------
306 -- |---------------------------< get_bg_defaults >----------------------------|
307 -- ----------------------------------------------------------------------------
308 --
309 procedure get_bg_defaults
310 (p_business_group_id IN per_business_groups.business_group_id%type
311 ,p_defaulting IN varchar2
312 ,p_time_normal_start OUT NOCOPY per_all_assignments_f.time_normal_start%type
313 ,p_time_normal_finish OUT NOCOPY per_all_assignments_f.time_normal_finish%type
314 ,p_normal_hours OUT NOCOPY per_all_assignments_f.normal_hours%type
315 ,p_frequency IN OUT NOCOPY per_all_assignments_f.frequency%type
316 ,p_frequency_meaning OUT NOCOPY hr_lookups.meaning%type
317 ,p_location_id IN OUT NOCOPY per_all_assignments_f.location_id%type
318 ,p_location OUT NOCOPY hr_locations.location_code%type
319 ,p_gre OUT NOCOPY hr_soft_coding_keyflex.segment1%type
320 ) is
321 --
322 l_proc varchar2(72) :=g_package||'get_bg_defaults';
323 l_normal_hours varchar2(150);
324 l_location_id per_all_assignments_f.location_id%type;
325 --
326 cursor get_work_day_information is
327 select org_information1
328 , org_information2
329 , org_information3
330 , org_information4
331 from hr_organization_information
332 where organization_id=p_business_group_id
333 and org_information_context='Work Day Information';
334 --
335 cursor get_gre_information is
336 select org_information1
337 from hr_organization_information
338 where organization_id=p_business_group_id
339 and org_information_context='DEFAULT_GRE_INFO';
340 --
341 cursor get_loc is
342 select location_id
343 from hr_all_organization_units
344 where organization_id=p_business_group_id;
345 --
346 begin
347 hr_utility.set_location('Entering: '||l_proc,10);
348 --
349 if p_defaulting in ('C','S') then
350 open get_work_day_information;
351 fetch get_work_day_information into
352 p_time_normal_start
353 ,p_time_normal_finish
354 ,l_normal_hours
355 ,p_frequency;
356 if get_work_day_information%found then
357 close get_work_day_information;
358 p_normal_hours:=fnd_number.canonical_to_number(l_normal_hours);
359 else
360 close get_work_day_information;
361 end if;
362 --
363 hr_utility.set_location(l_proc,20);
364 open get_gre_information;
365 fetch get_gre_information into p_gre;
366 close get_gre_information;
367 --
368 hr_utility.set_location(l_proc,30);
369 open get_loc;
370 fetch get_loc into l_location_id;
371 if get_loc%found then
372 close get_loc;
373 if l_location_id is not null then
374 p_location_id:=l_location_id;
375 end if;
376 else
377 close get_loc;
378 end if;
379 end if;
380 --
381 p_frequency_meaning:=hr_reports.get_lookup_meaning('FREQUENCY',p_frequency);
382 p_location:=get_location(l_location_id);
383 hr_utility.set_location('Leaving: '||l_proc,50);
384 end get_bg_defaults;
385 --
386 -- ----------------------------------------------------------------------------
387 -- |--------------------------< get_pos_defaults >----------------------------|
388 -- ----------------------------------------------------------------------------
389 --
390 procedure get_pos_defaults
391 (p_position_id IN per_all_assignments_f.position_id%type
392 ,p_effective_date IN date
393 ,p_defaulting IN varchar2
394 ,p_organization_id IN OUT NOCOPY per_all_assignments_f.organization_id%type
395 ,p_organization OUT NOCOPY hr_organization_units.name%type
396 ,p_job_id IN OUT NOCOPY per_all_assignments_f.job_id%type
397 ,p_job OUT NOCOPY per_jobs.name%type
398 ,p_vacancy_id IN OUT NOCOPY per_vacancies.vacancy_id%type
399 ,p_vacancy IN OUT NOCOPY per_vacancies.name%type
400 ,p_time_normal_start IN OUT NOCOPY per_all_assignments_f.time_normal_start%type
401 ,p_time_normal_finish IN OUT NOCOPY per_all_assignments_f.time_normal_finish%type
402 ,p_normal_hours IN OUT NOCOPY per_all_assignments_f.normal_hours%type
403 ,p_frequency IN OUT NOCOPY per_all_assignments_f.frequency%type
404 ,p_frequency_meaning OUT NOCOPY hr_lookups.meaning%type
405 ,p_location_id IN OUT NOCOPY per_all_assignments_f.location_id%type
406 ,p_location OUT NOCOPY hr_locations.location_code%type
407 ,p_probation_period IN OUT NOCOPY per_all_assignments_f.probation_period%type
408 ,p_probation_unit IN OUT NOCOPY per_all_assignments_f.probation_unit%type
409 ,p_probation_unit_meaning OUT NOCOPY hr_lookups.meaning%type
410 ,p_pay_basis_id IN OUT NOCOPY per_all_assignments_f.pay_basis_id%type
411 ,p_salary_basis OUT NOCOPY per_pay_bases.name%type
412 ,p_pay_basis OUT NOCOPY per_pay_bases.pay_basis%type
413 ,p_pay_basis_meaning OUT NOCOPY hr_lookups.meaning%type
414 ,p_payroll_id IN OUT NOCOPY per_all_assignments_f.payroll_id%type
415 ,p_payroll OUT NOCOPY pay_all_payrolls_f.payroll_name%type
416 ,p_supervisor_id IN OUT NOCOPY per_all_assignments_f.supervisor_id%type
417 ,p_supervisor OUT NOCOPY per_all_people_f.full_name%type
418 ,p_grade_id IN OUT NOCOPY per_all_assignments_f.grade_id%type
419 ,p_grade OUT NOCOPY per_grades.name%type
420 ,p_bargaining_unit IN OUT NOCOPY per_all_assignments_f.bargaining_unit_code%type
421 ,p_bargaining_unit_meaning OUT NOCOPY hr_lookups.meaning%type
422 ) is
423 --
424 l_proc varchar2(72) :=g_package||'get_pos_defaults';
425 l_position_id per_all_assignments_f.position_id%type;
426 l_organization_id per_all_assignments_f.organization_id%type;
427 l_old_organization_id per_all_assignments_f.organization_id%type;
428 l_job_id per_all_assignments_f.job_id%type;
429 l_time_normal_start per_all_assignments_f.time_normal_start%type;
430 l_time_normal_finish per_all_assignments_f.time_normal_finish%type;
431 l_normal_hours per_all_assignments_f.normal_hours%type;
432 l_frequency per_all_assignments_f.frequency%type;
433 l_location_id per_all_assignments_f.location_id%type;
434 l_probation_period per_all_assignments_f.probation_period%type;
435 l_probation_unit per_all_assignments_f.probation_unit%type;
436 l_pay_basis_id per_all_assignments_f.pay_basis_id%type;
437 l_payroll_id per_all_assignments_f.payroll_id%type;
438 l_supervisor_id per_all_assignments_f.supervisor_id%type;
439 l_grade_id per_all_assignments_f.grade_id%type;
440 l_frequency_meaning hr_lookups.meaning%type;
441 l_location hr_locations.location_code%type;
442 l_probation_unit_meaning hr_lookups.meaning%type;
443 l_salary_basis per_pay_bases.name%type;
444 l_pay_basis per_pay_bases.pay_basis%type;
445 l_payroll pay_all_payrolls_f.payroll_name%type;
446 l_supervisor per_all_people_f.full_name%type;
447 l_bargaining_unit hr_all_positions_f.bargaining_unit_cd%type;
448 l_dummy number;
449 --
450 cursor get_pos_defs is
451 select organization_id
452 , job_id
453 , time_normal_start
454 , time_normal_finish
455 , working_hours
456 , frequency
457 , location_id
458 , probation_period
459 , probation_period_unit_cd
460 , pay_basis_id
461 , pay_freq_payroll_id
462 , supervisor_id
463 , entry_grade_id
464 , bargaining_unit_cd
465 from hr_all_positions_f
466 where position_id=p_position_id
467 and p_effective_date between effective_start_date and effective_end_date;
468 --
469 cursor get_vac is
470 select position_id
471 from per_vacancies
472 where vacancy_id=p_vacancy_id
473 and p_effective_date between date_from and nvl(date_to,p_effective_date);
474 --
475 begin
476 hr_utility.set_location('Entering: '||l_proc,10);
477 --
478 if p_vacancy_id is not null then
479 open get_vac;
480 fetch get_vac into l_position_id;
481 close get_vac;
482 if l_position_id is not null and p_position_id is not null then
483 if l_position_id<>p_position_id then
484 p_vacancy_id:=null;
485 p_vacancy:=null;
486 end if;
487 end if;
488 end if;
489 --
490 l_old_organization_id:=p_organization_id;
491 --
492 if p_defaulting in ('C','S') then
493 open get_pos_defs;
494 fetch get_pos_defs into
495 l_organization_id
496 ,l_job_id
497 ,l_time_normal_start
498 ,l_time_normal_finish
499 ,l_normal_hours
500 ,l_frequency
501 ,l_location_id
502 ,l_probation_period
503 ,l_probation_unit
504 ,l_pay_basis_id
505 ,l_payroll_id
506 ,l_supervisor_id
507 ,l_grade_id
508 ,l_bargaining_unit;
509 --
510 close get_pos_defs;
511 hr_utility.set_location(l_pay_basis_id||l_proc,20);
512 p_organization_id:=l_organization_id;
513 p_job_id:=l_job_id;
514 --
515 if p_time_normal_start is null then
516 p_time_normal_start:=l_time_normal_start;
517 end if;
518 if p_time_normal_finish is null then
519 p_time_normal_finish:=l_time_normal_finish;
520 end if;
521 if p_frequency is null then
522 p_frequency:=l_frequency;
523 end if;
524 if p_normal_hours is null then
525 p_normal_hours:=l_normal_hours;
526 end if;
527 if p_location_id is null then
528 p_location_id:=l_location_id;
529 end if;
530 if p_probation_period is null then
531 p_probation_period:=l_probation_period;
532 end if;
533 if p_probation_unit is null then
534 p_probation_unit:=l_probation_unit;
535 end if;
536 if p_pay_basis_id is null then
537 p_pay_basis_id:=l_pay_basis_id;
538 end if;
539 if p_payroll_id is null then
540 p_payroll_id:=l_payroll_id;
541 end if;
542 if p_supervisor_id is null then
543 p_supervisor_id:=l_supervisor_id;
544 end if;
545 if p_grade_id is null then
546 p_grade_id:=l_grade_id;
547 end if;
548 if p_bargaining_unit is null then
549 p_bargaining_unit:=l_bargaining_unit;
550 end if;
551 --
552 if p_defaulting='C'
553 and l_organization_id<>l_old_organization_id then
554 --
555 get_org_defaults
556 (p_organization_id => l_organization_id
557 ,p_defaulting => p_defaulting
558 ,p_effective_date => p_effective_date
559 ,p_vacancy_id => p_vacancy_id
560 ,p_vacancy => p_vacancy
561 ,p_time_normal_start => l_time_normal_start
562 ,p_time_normal_finish => l_time_normal_finish
563 ,p_normal_hours => l_normal_hours
564 ,p_frequency => l_frequency
565 ,p_frequency_meaning => l_frequency_meaning
566 ,p_location_id => l_location_id
567 ,p_location => l_location
568 ,p_probation_period => l_probation_period
569 ,p_probation_unit => l_probation_unit
570 ,p_probation_unit_meaning => l_probation_unit_meaning
571 ,p_pay_basis_id => l_pay_basis_id
572 ,p_salary_basis => l_salary_basis
573 ,p_pay_basis => l_pay_basis
574 ,p_pay_basis_meaning => p_pay_basis_meaning
575 ,p_payroll_id => l_payroll_id
576 ,p_payroll => l_payroll
577 ,p_supervisor_id => l_supervisor_id
578 ,p_supervisor => l_supervisor
579 ,p_position_id => l_dummy
580 );
581 --
582 if p_time_normal_start is null then
583 p_time_normal_start:=l_time_normal_start;
584 end if;
585 if p_time_normal_finish is null then
586 p_time_normal_finish:=l_time_normal_finish;
587 end if;
588 if p_frequency is null then
589 p_frequency:=l_frequency;
590 end if;
591 if p_normal_hours is null then
592 p_normal_hours:=l_normal_hours;
593 end if;
594 if p_location_id is null then
595 p_location_id:=l_location_id;
596 end if;
597 if p_probation_period is null then
598 p_probation_period:=l_probation_period;
599 end if;
600 if p_probation_unit is null then
601 p_probation_unit:=l_probation_unit;
602 end if;
603 if p_pay_basis_id is null then
604 p_pay_basis_id:=l_pay_basis_id;
605 end if;
606 if p_payroll_id is null then
607 p_payroll_id:=l_payroll_id;
608 end if;
609 if p_supervisor_id is null then
610 p_supervisor_id:=l_supervisor_id;
611 end if;
612 end if;
613 end if;
614 p_organization:=get_organization(p_organization_id);
615 p_job:=get_job(p_job_id);
616 p_frequency_meaning:=hr_reports.get_lookup_meaning('FREQUENCY',p_frequency);
617 p_location:=get_location(p_location_id);
618 p_probation_unit_meaning:=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',p_probation_unit);
619 get_salary_basis(p_pay_basis_id
620 ,p_pay_basis
621 ,p_pay_basis_meaning
622 ,p_salary_basis);
623 hr_utility.set_location(p_pay_basis_id||p_salary_basis||l_proc,45);
624
625 p_payroll:=get_payroll(p_payroll_id,p_effective_date);
626 p_supervisor:=get_full_name(p_supervisor_id,p_effective_date);
627 p_grade:=get_grade(p_grade_id);
628 p_bargaining_unit_meaning:=hr_reports.get_lookup_meaning('BARGAINING_UNIT_CODE',p_bargaining_unit);
629 hr_utility.set_location('Leaving: '||l_proc,50);
630 end get_pos_defaults;
631 --
632 -- ----------------------------------------------------------------------------
633 -- |--------------------------< get_org_defaults >----------------------------|
634 -- ----------------------------------------------------------------------------
635 --
636 procedure get_org_defaults
637 (p_organization_id IN per_all_assignments_f.organization_id%type
638 ,p_defaulting IN varchar2
639 ,p_effective_date IN date
640 ,p_vacancy_id IN OUT NOCOPY per_vacancies.vacancy_id%type
641 ,p_vacancy IN OUT NOCOPY per_vacancies.name%type
642 ,p_time_normal_start IN OUT NOCOPY per_all_assignments_f.time_normal_start%type
643 ,p_time_normal_finish IN OUT NOCOPY per_all_assignments_f.time_normal_finish%type
644 ,p_normal_hours IN OUT NOCOPY per_all_assignments_f.normal_hours%type
645 ,p_frequency IN OUT NOCOPY per_all_assignments_f.frequency%type
646 ,p_frequency_meaning OUT NOCOPY hr_lookups.meaning%type
647 ,p_location_id IN OUT NOCOPY per_all_assignments_f.location_id%type
648 ,p_location OUT NOCOPY hr_locations.location_code%type
649 ,p_probation_period IN OUT NOCOPY per_all_assignments_f.probation_period%type
650 ,p_probation_unit IN OUT NOCOPY per_all_assignments_f.probation_unit%type
651 ,p_probation_unit_meaning OUT NOCOPY hr_lookups.meaning%type
652 ,p_pay_basis_id IN OUT NOCOPY per_all_assignments_f.pay_basis_id%type
653 ,p_salary_basis OUT NOCOPY per_pay_bases.name%type
654 ,p_pay_basis OUT NOCOPY per_pay_bases.pay_basis%type
655 ,p_pay_basis_meaning OUT NOCOPY hr_lookups.meaning%type
656 ,p_payroll_id IN OUT NOCOPY per_all_assignments_f.payroll_id%type
657 ,p_payroll OUT NOCOPY pay_all_payrolls_f.payroll_name%type
658 ,p_supervisor_id IN OUT NOCOPY per_all_assignments_f.supervisor_id%type
659 ,p_supervisor OUT NOCOPY per_all_people_f.full_name%type
660 ,p_position_id IN OUT NOCOPY per_all_assignments_f.position_id%type
661 ) is
662 --
663 l_proc varchar2(72) :=g_package||'get_org_defaults';
664 l_normal_hours varchar2(150);
665 l_probation_period varchar2(150);
666 l_time_normal_start per_all_assignments_f.time_normal_start%type;
667 l_time_normal_finish per_all_assignments_f.time_normal_finish%type;
668 l_frequency per_all_assignments_f.frequency%type;
669 l_location_id per_all_assignments_f.location_id%type;
670 l_probation_unit per_all_assignments_f.probation_unit%type;
671 l_pay_basis_id per_all_assignments_f.pay_basis_id%type;
672 l_payroll_id per_all_assignments_f.payroll_id%type;
673 l_supervisor_id per_all_assignments_f.supervisor_id%type;
674 l_grade_id per_all_assignments_f.grade_id%type;
675 l_organization_id per_all_assignments_f.organization_id%type;
676 l_dummy number;
677 --
678 cursor get_work_day_information is
679 select org_information1
680 , org_information2
681 , org_information3
682 , org_information4
683 from hr_organization_information
684 where organization_id=p_organization_id
685 and org_information_context='Work Day Information';
686 --
687 cursor get_org_information is
688 select org_information1
689 , org_information2
690 , org_information3
691 , org_information4
692 , org_information5
693 from hr_organization_information
694 where organization_id=p_organization_id
695 and org_information_context='DEFAULT_HR_INFO';
696 --
697 cursor get_loc is
698 select location_id
699 from hr_all_organization_units
700 where organization_id=p_organization_id;
701 --
702 --
703 cursor get_vac is
704 select organization_id
705 from per_vacancies
706 where vacancy_id=p_vacancy_id
707 and p_effective_date between date_from and nvl(date_to,p_effective_date);
708 --
709 cursor chk_pos_org(ln_position_id number
710 ,ln_organization_id number) is
711 select 1
712 from hr_positions_f
713 where organization_id=ln_organization_id
714 and position_id=ln_position_id
715 and p_effective_date between effective_start_date
716 and effective_end_date;
717 --
718 --
719 begin
720 hr_utility.set_location('Entering: '||l_proc,10);
721 --
722 open get_vac;
723 fetch get_vac into l_organization_id;
724 close get_vac;
725 if l_organization_id is not null and p_organization_id is not null then
726 if l_organization_id<>p_organization_id then
727 p_vacancy_id:=null;
728 p_vacancy:=null;
729 end if;
730 end if;
731 --
732 open chk_pos_org(p_position_id,p_organization_id);
733 fetch chk_pos_org into l_dummy;
734 if chk_pos_org%notfound then
735 close chk_pos_org;
736 p_position_id :=null;
737 else
738 close chk_pos_org;
739 end if;
740
741 if p_defaulting in ('C','S') then
742 open get_work_day_information;
743 fetch get_work_day_information into
744 l_time_normal_start
745 ,l_time_normal_finish
746 ,l_normal_hours
747 ,l_frequency;
748 if get_work_day_information%found then
749 close get_work_day_information;
750 -- added the ' or ' conditions for the following if statements bug 4629833
751 if p_time_normal_start is null or (p_time_normal_start <> l_time_normal_start) then
752 p_time_normal_start:=l_time_normal_start;
753 end if;
754 if p_time_normal_finish is null or (p_time_normal_finish <>l_time_normal_finish) then
755 p_time_normal_finish:=l_time_normal_finish;
756 end if;
757 --modified if condition as part of bug 4898869
758 -- if p_normal_hours is null or (p_normal_hours <> l_normal_hours) then
759 if p_normal_hours is null or (p_normal_hours <> fnd_number.canonical_to_number(l_normal_hours)) then
760 p_normal_hours:=fnd_number.canonical_to_number(l_normal_hours);
761 end if;
762 if p_frequency is null or (p_frequency <> l_frequency ) then
763 p_frequency:=l_frequency;
764 end if;
765 -- end of bug 4629833
766 else
767 close get_work_day_information;
768 end if;
769 --
770 hr_utility.set_location(l_proc,20);
771 open get_loc;
772 fetch get_loc into l_location_id;
773 if get_loc%found then
774 close get_loc;
775 if p_location_id is null then
776 p_location_id:=l_location_id;
777 end if;
778 else
779 close get_loc;
780 end if;
781 --
782 hr_utility.set_location(l_proc,30);
783 open get_org_information;
784 fetch get_org_information into
785 p_payroll_id
786 ,l_supervisor_id
787 ,l_pay_basis_id
788 ,l_probation_period
789 ,l_probation_unit;
790 if get_org_information%found then
791 close get_org_information;
792 if p_payroll_id is null then
793 p_payroll_id:=l_payroll_id;
794 end if;
795 if p_supervisor_id is null then
796 p_supervisor_id:=l_supervisor_id;
797 end if;
798 if p_pay_basis_id is null then
799 p_pay_basis_id:=l_pay_basis_id;
800 end if;
801 if p_probation_period is null then
802 p_probation_period:=fnd_number.canonical_to_number(l_probation_period);
803 end if;
804 if p_probation_unit is null then
805 p_probation_unit:=l_probation_unit;
806 end if;
807 else
808 close get_org_information;
809 end if;
810 --
811 end if;
812 p_frequency_meaning:=hr_reports.get_lookup_meaning('FREQUENCY',p_frequency);
813 p_location:=get_location(p_location_id); --Bug 3099072. Passed id in place of location.
814 p_probation_unit_meaning:=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',p_probation_unit);
815 get_salary_basis(p_pay_basis_id
816 ,p_pay_basis
817 ,p_pay_basis_meaning
818 ,p_salary_basis);
819 p_supervisor:=get_full_name(p_supervisor_id,p_effective_date);
820 p_payroll:=get_payroll(p_payroll_id,p_effective_date);
821 hr_utility.set_location('Leaving: '||l_proc,50);
822 end get_org_defaults;
823 --
824 -- ----------------------------------------------------------------------------
825 -- |--------------------------< get_vac_defaults >----------------------------|
826 -- ----------------------------------------------------------------------------
827 --
828 procedure get_vac_defaults
829 (p_vacancy_id IN per_all_assignments_f.vacancy_id%type
830 ,p_defaulting IN varchar2
831 ,p_effective_date IN date
832 ,p_recruiter_id IN OUT NOCOPY per_all_assignments_f.recruiter_id%type
833 ,p_recruiter OUT NOCOPY per_all_people_f.full_name%type
834 ,p_grade_id IN OUT NOCOPY per_all_assignments_f.grade_id%type
835 ,p_grade OUT NOCOPY per_grades.name%type
836 ,p_position_id IN OUT NOCOPY per_all_assignments_f.position_id%type
837 ,p_position OUT NOCOPY hr_all_positions_f.name%type
838 ,p_job_id IN OUT NOCOPY per_all_assignments_f.job_id%type
839 ,p_job OUT NOCOPY per_jobs.name%type
840 ,p_location_id IN OUT NOCOPY per_all_assignments_f.location_id%type
841 ,p_location OUT NOCOPY hr_locations.location_code%type
842 ,p_organization_id IN OUT NOCOPY per_all_assignments_f.organization_id%type
843 ,p_organization OUT NOCOPY hr_organization_units.name%type
844 ,p_time_normal_start IN OUT NOCOPY per_all_assignments_f.time_normal_start%type
845 ,p_time_normal_finish IN OUT NOCOPY per_all_assignments_f.time_normal_finish%type
846 ,p_normal_hours IN OUT NOCOPY per_all_assignments_f.normal_hours%type
847 ,p_frequency IN OUT NOCOPY per_all_assignments_f.frequency%type
848 ,p_frequency_meaning OUT NOCOPY hr_lookups.meaning%type
849 ,p_probation_period IN OUT NOCOPY per_all_assignments_f.probation_period%type
850 ,p_probation_unit IN OUT NOCOPY per_all_assignments_f.probation_unit%type
851 ,p_probation_unit_meaning OUT NOCOPY hr_lookups.meaning%type
852 ,p_pay_basis_id IN OUT NOCOPY per_all_assignments_f.pay_basis_id%type
853 ,p_salary_basis OUT NOCOPY per_pay_bases.name%type
854 ,p_pay_basis OUT NOCOPY per_pay_bases.pay_basis%type
855 ,p_pay_basis_meaning OUT NOCOPY hr_lookups.meaning%type
856 ,p_payroll_id IN OUT NOCOPY per_all_assignments_f.payroll_id%type
857 ,p_payroll OUT NOCOPY pay_all_payrolls_f.payroll_name%type
858 ,p_supervisor_id IN OUT NOCOPY per_all_assignments_f.supervisor_id%type
859 ,p_supervisor OUT NOCOPY per_all_people_f.full_name%type
860 ,p_bargaining_unit IN OUT NOCOPY per_all_assignments_f.bargaining_unit_code%type
861 ,p_bargaining_unit_meaning OUT NOCOPY hr_lookups.meaning%type
862 ,p_people_group_id IN OUT NOCOPY per_all_assignments_f.people_group_id%type
863 ,p_pgp_segment1 OUT NOCOPY pay_people_groups.segment1%type
864 ,p_pgp_segment2 OUT NOCOPY pay_people_groups.segment2%type
865 ,p_pgp_segment3 OUT NOCOPY pay_people_groups.segment3%type
866 ,p_pgp_segment4 OUT NOCOPY pay_people_groups.segment4%type
867 ,p_pgp_segment5 OUT NOCOPY pay_people_groups.segment5%type
868 ,p_pgp_segment6 OUT NOCOPY pay_people_groups.segment6%type
869 ,p_pgp_segment7 OUT NOCOPY pay_people_groups.segment7%type
870 ,p_pgp_segment8 OUT NOCOPY pay_people_groups.segment8%type
871 ,p_pgp_segment9 OUT NOCOPY pay_people_groups.segment9%type
872 ,p_pgp_segment10 OUT NOCOPY pay_people_groups.segment10%type
873 ,p_pgp_segment11 OUT NOCOPY pay_people_groups.segment11%type
874 ,p_pgp_segment12 OUT NOCOPY pay_people_groups.segment12%type
875 ,p_pgp_segment13 OUT NOCOPY pay_people_groups.segment13%type
876 ,p_pgp_segment14 OUT NOCOPY pay_people_groups.segment14%type
877 ,p_pgp_segment15 OUT NOCOPY pay_people_groups.segment15%type
878 ,p_pgp_segment16 OUT NOCOPY pay_people_groups.segment16%type
879 ,p_pgp_segment17 OUT NOCOPY pay_people_groups.segment17%type
880 ,p_pgp_segment18 OUT NOCOPY pay_people_groups.segment18%type
881 ,p_pgp_segment19 OUT NOCOPY pay_people_groups.segment19%type
882 ,p_pgp_segment20 OUT NOCOPY pay_people_groups.segment20%type
883 ,p_pgp_segment21 OUT NOCOPY pay_people_groups.segment21%type
884 ,p_pgp_segment22 OUT NOCOPY pay_people_groups.segment22%type
885 ,p_pgp_segment23 OUT NOCOPY pay_people_groups.segment23%type
886 ,p_pgp_segment24 OUT NOCOPY pay_people_groups.segment24%type
887 ,p_pgp_segment25 OUT NOCOPY pay_people_groups.segment25%type
888 ,p_pgp_segment26 OUT NOCOPY pay_people_groups.segment26%type
889 ,p_pgp_segment27 OUT NOCOPY pay_people_groups.segment27%type
890 ,p_pgp_segment28 OUT NOCOPY pay_people_groups.segment28%type
891 ,p_pgp_segment29 OUT NOCOPY pay_people_groups.segment29%type
892 ,p_pgp_segment30 OUT NOCOPY pay_people_groups.segment30%type
893 ) is
894 --
895 l_proc varchar2(72) :=g_package||'get_vac_defaults';
896 l_recruiter_id per_all_assignments_f.recruiter_id%type;
897 l_grade_id per_all_assignments_f.grade_id%type;
898 l_position_id per_all_assignments_f.position_id%type;
899 l_old_position_id per_all_assignments_f.position_id%type;
900 l_job_id per_all_assignments_f.job_id%type;
901 l_location_id per_all_assignments_f.location_id%type;
902 l_organization_id per_all_assignments_f.organization_id%type;
903 l_people_group_id per_all_assignments_f.people_group_id%type;
904 l_pgp_rec pay_people_groups%rowtype;
905 l_vacancy_id per_vacancies.vacancy_id%type;
906 l_vacancy per_vacancies.name%type;
907 l_dummy number;
908 --
909 cursor get_vac_details is
910 select recruiter_id
911 , grade_id
912 , position_id
913 , job_id
914 , location_id
915 , organization_id
916 , people_group_id
917 from per_vacancies
918 where vacancy_id=p_vacancy_id;
919 --
920 cursor get_pgp(pgp_id number) is
921 select *
922 from pay_people_groups
923 where people_group_id=pgp_id;
924 --
925 cursor chk_job_pos(ln_job_id number
926 ,ln_position_id number) is
927 select 1
928 from hr_positions_f
929 where job_id=ln_job_id
930 and position_id=ln_position_id
931 and p_effective_date between effective_start_date
932 and effective_end_date;
933 --
934 cursor chk_pos_org(ln_position_id number
935 ,ln_organization_id number) is
936 select 1
937 from hr_positions_f
938 where organization_id=ln_organization_id
939 and position_id=ln_position_id
940 and p_effective_date between effective_start_date
941 and effective_end_date;
942 --
943 begin
944 hr_utility.set_location('Entering: '||l_proc,10);
945 --
946 l_old_position_id:=p_position_id;
947 --
948 if p_defaulting in ('C','S') then
949 open get_vac_details;
950 fetch get_vac_details into
951 l_recruiter_id
952 ,l_grade_id
953 ,l_position_id
954 ,l_job_id
955 ,l_location_id
956 ,l_organization_id
957 ,l_people_group_id;
958 if get_vac_details%found then
959 close get_vac_details;
960 if l_recruiter_id is not null then
961 p_recruiter_id:=l_recruiter_id;
962 end if;
963 if l_grade_id is not null then
964 p_grade_id:=l_grade_id;
965 end if;
966 if l_location_id is not null then
967 p_location_id:=l_location_id;
968 end if;
969 if l_people_group_id is not null then
970 p_people_group_id:=l_people_group_id;
971 end if;
972 --
973 if l_job_id is not null then
974 p_job_id:=l_job_id;
975 end if;
976 --
977 if l_position_id is not null then
978 p_position_id:=l_position_id;
979 if l_job_id is null and p_job_id is not null then
980 --
981 -- we have set the position, but not the job, so check that
982 -- the old job matches.
983 --
984 open chk_job_pos(p_job_id,p_position_id);
985 fetch chk_job_pos into l_dummy;
986 if chk_job_pos%notfound then
987 close chk_job_pos;
988 p_job_id:=null;
989 else
990 close chk_job_pos;
991 end if;
992 end if;
993 elsif p_position_id is not null and l_job_id is not null then
994 --
995 -- we have set the job but not the position, so check that
996 -- the old position matches
997 --
998 open chk_job_pos(p_job_id,p_position_id);
999 fetch chk_job_pos into l_dummy;
1000 if chk_job_pos%notfound then
1001 close chk_job_pos;
1002 p_position_id:=null;
1003 else
1004 close chk_job_pos;
1005 end if;
1006 end if;
1007 --
1008 if l_organization_id is not null then
1009 p_organization_id:=l_organization_id;
1010 if l_position_id is null and p_position_id is not null then
1011 --
1012 -- we have set the organization, but not the position, so
1013 -- check that the old position matches.
1014 --
1015 open chk_pos_org(p_position_id,p_organization_id);
1016 fetch chk_pos_org into l_dummy;
1017 if chk_pos_org%notfound then
1018 close chk_pos_org;
1019 p_position_id :=null;
1020 else
1021 close chk_pos_org;
1022 end if;
1023 end if;
1024 elsif p_organization_id is not null and l_position_id is not null then
1025 --
1026 -- we have set the position, but not the organization, so
1027 -- check that the old organization matches.
1028 --
1029 open chk_pos_org(p_position_id,p_organization_id);
1030 fetch chk_pos_org into l_dummy;
1031 if chk_pos_org%notfound then
1032 close chk_pos_org;
1033 p_organization_id :=null;
1034 else
1035 close chk_pos_org;
1036 end if;
1037 end if;
1038
1039 else
1040 close get_vac_details;
1041 end if;
1042 hr_utility.set_location(l_proc,20);
1043 open get_pgp(p_people_group_id);
1044 fetch get_pgp into l_pgp_rec;
1045 if get_pgp%found then
1046 close get_pgp;
1047 p_pgp_segment1 :=l_pgp_rec.segment1;
1048 p_pgp_segment2 :=l_pgp_rec.segment2;
1049 p_pgp_segment3 :=l_pgp_rec.segment3;
1050 p_pgp_segment4 :=l_pgp_rec.segment4;
1051 p_pgp_segment5 :=l_pgp_rec.segment5;
1052 p_pgp_segment6 :=l_pgp_rec.segment6;
1053 p_pgp_segment7 :=l_pgp_rec.segment7;
1054 p_pgp_segment8 :=l_pgp_rec.segment8;
1055 p_pgp_segment9 :=l_pgp_rec.segment9;
1056 p_pgp_segment10 :=l_pgp_rec.segment10;
1057 p_pgp_segment11 :=l_pgp_rec.segment11;
1058 p_pgp_segment12 :=l_pgp_rec.segment12;
1059 p_pgp_segment13 :=l_pgp_rec.segment13;
1060 p_pgp_segment14 :=l_pgp_rec.segment14;
1061 p_pgp_segment15 :=l_pgp_rec.segment15;
1062 p_pgp_segment16 :=l_pgp_rec.segment16;
1063 p_pgp_segment17 :=l_pgp_rec.segment17;
1064 p_pgp_segment18 :=l_pgp_rec.segment18;
1065 p_pgp_segment19 :=l_pgp_rec.segment19;
1066 p_pgp_segment20 :=l_pgp_rec.segment20;
1067 p_pgp_segment21 :=l_pgp_rec.segment21;
1068 p_pgp_segment22 :=l_pgp_rec.segment22;
1069 p_pgp_segment23 :=l_pgp_rec.segment23;
1070 p_pgp_segment24 :=l_pgp_rec.segment24;
1071 p_pgp_segment25 :=l_pgp_rec.segment25;
1072 p_pgp_segment26 :=l_pgp_rec.segment26;
1073 p_pgp_segment27 :=l_pgp_rec.segment27;
1074 p_pgp_segment28 :=l_pgp_rec.segment28;
1075 p_pgp_segment29 :=l_pgp_rec.segment29;
1076 p_pgp_segment30 :=l_pgp_rec.segment30;
1077 else
1078 close get_pgp;
1079 end if;
1080 end if;
1081 --
1082 hr_utility.set_location(l_proc,30);
1083 if p_defaulting='C'
1084 and l_position_id is not null
1085 and l_position_id <> nvl(l_old_position_id,hr_api.g_number) then
1086 get_pos_defaults
1087 (p_position_id => l_position_id
1088 ,p_effective_date => p_effective_date
1089 ,p_defaulting => p_defaulting
1090 ,p_organization_id => p_organization_id
1091 ,p_organization => p_organization
1092 ,p_job_id => p_job_id
1093 ,p_job => p_job
1094 ,p_vacancy_id => l_vacancy_id
1095 ,p_vacancy => l_vacancy
1096 ,p_time_normal_start => p_time_normal_start
1097 ,p_time_normal_finish => p_time_normal_finish
1098 ,p_normal_hours => p_normal_hours
1099 ,p_frequency => p_frequency
1100 ,p_frequency_meaning => p_frequency_meaning
1101 ,p_location_id => p_location_id
1102 ,p_location => p_location
1103 ,p_probation_period => p_probation_period
1104 ,p_probation_unit => p_probation_unit
1105 ,p_probation_unit_meaning => p_probation_unit_meaning
1106 ,p_pay_basis_id => p_pay_basis_id
1107 ,p_salary_basis => p_salary_basis
1108 ,p_pay_basis => p_pay_basis
1109 ,p_pay_basis_meaning => p_pay_basis_meaning
1110 ,p_payroll_id => p_payroll_id
1111 ,p_payroll => p_payroll
1112 ,p_supervisor_id => p_supervisor_id
1113 ,p_supervisor => p_supervisor
1114 ,p_grade_id => p_grade_id
1115 ,p_grade => p_grade
1116 ,p_bargaining_unit => p_bargaining_unit
1117 ,p_bargaining_unit_meaning => p_bargaining_unit_meaning
1118 );
1119 end if;
1120 p_recruiter:=get_full_name(p_recruiter_id,p_effective_date);
1121 p_grade:=get_grade(p_grade_id);
1122 p_position:=get_position(p_position_id,p_effective_date);
1123 p_job:=get_job(p_job_id);
1124 p_location:=get_location(p_location_id);
1125 p_organization:=get_organization(p_organization_id);
1126 p_frequency_meaning:=hr_reports.get_lookup_meaning('FREQUENCY',p_frequency);
1127 p_probation_unit_meaning:=hr_reports.get_lookup_meaning('QUALIFYING_UNITS',p_probation_unit);
1128 get_salary_basis(p_pay_basis_id
1129 ,p_pay_basis
1130 ,p_pay_basis_meaning
1131 ,p_salary_basis);
1132 p_supervisor:=get_full_name(p_supervisor_id,p_effective_date);
1133 p_payroll:=get_payroll(p_payroll_id,p_effective_date);
1134 hr_utility.set_location('Leaving: '||l_proc,50);
1135 end get_vac_defaults;
1136
1137 end per_qh_populate;
[Home] [Help]