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