1 Package Body per_asg_bus2 as
2 /* $Header: peasgrhi.pkb 120.19.12010000.3 2008/11/12 06:37:24 skura ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_asg_bus2.'; -- Global package name
9 --
10 -- ---------------------------------------------------------------------------
11 -- |------------------------< chk_pay_basis_id >-----------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 procedure chk_pay_basis_id
15 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
16 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
17 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
18 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
19 ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
20 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
21 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
22 )
23 is
24 --
25 l_proc varchar2(72) := g_package||'chk_pay_basis_id';
26 l_api_updating boolean;
27 l_business_group_id per_business_groups.business_group_id%TYPE;
28 l_max_pp_chg_date date;
29 --
30 --
31 -- Cursor to validate that pay basis exists in PER_PAY_BASES
32 --
33 cursor csr_chk_pay_basis is
34 select business_group_id
35 from per_pay_bases
36 where pay_basis_id = p_pay_basis_id;
37 --
38 -- Cursor to validate that the validation start date for the assignment is
39 -- after all change dates for pay proposals of the assignment.
40 --
41 cursor csr_get_max_pp_chg_date is
42 select nvl(max(change_date),p_validation_start_date)
43 from per_pay_proposals
44 where assignment_id = p_assignment_id;
45 --
46 begin
47 hr_utility.set_location('Entering:'|| l_proc, 1);
48 --
49 -- Check mandatory parameters have been set
50 --
51 hr_api.mandatory_arg_error
52 (p_api_name => l_proc
53 ,p_argument => 'effective_date'
54 ,p_argument_value => p_effective_date
55 );
56 --
57 -- Check if the assignment is being updated
58 --
59 l_api_updating := per_asg_shd.api_updating
60 (p_assignment_id => p_assignment_id
61 ,p_effective_date => p_effective_date
62 ,p_object_version_number => p_object_version_number
63 );
64 hr_utility.set_location(l_proc, 2);
65 --
66 -- Check if the assignment is being inserted or updated.
67 --
68 if ((l_api_updating and
69 nvl(per_asg_shd.g_old_rec.pay_basis_id, hr_api.g_number)
70 <> nvl(p_pay_basis_id, hr_api.g_number)) or
71 (NOT l_api_updating)) then
72 hr_utility.set_location(l_proc, 3);
73 --
74 -- Check if the pay basis is set
75 --
76 if p_pay_basis_id is not null then
77 --
78 -- Check that the assignment is an Employee or Applicant
79 -- or Benefits or Offer assignment.
80 -- altered at allow applicants to have a pay basis. 28/1/99
81 --
82 -- <OAB_CHANGE>
83 --
84 if p_assignment_type not in ('E','A','B','O') then
85 --
86 hr_utility.set_message(801, 'HR_51176_ASG_INV_ASG_TYP_PBS');
87 hr_multi_message.add
88 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
89 );
90 --
91 end if;
92 hr_utility.set_location(l_proc, 4);
93 --
94 -- Check that the pay basis exists in PER_PAY_BASES.
95 --
96 open csr_chk_pay_basis;
97 fetch csr_chk_pay_basis into l_business_group_id;
98 if csr_chk_pay_basis%notfound then
99 close csr_chk_pay_basis;
100 hr_utility.set_message(801, 'HR_51168_ASG_INV_PAY_BASIS_ID');
101 hr_multi_message.add
102 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
103 );
104 else
105 close csr_chk_pay_basis;
106 end if;
107 hr_utility.set_location(l_proc, 5);
108 --
109 -- Check that the pay basis is in the same business group as the pay
110 -- basis of the assignment.
111 --
112 If p_business_group_id <> l_business_group_id then
113 --
114 hr_utility.set_message(801, 'HR_51169_ASG_INV_PAY_BAS_BG');
115 hr_multi_message.add
116 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID'
117 );
118 --
119 end if;
120 hr_utility.set_location(l_proc, 6);
121 --
122 -- Check if pay basis is being updated
123 --
124 if l_api_updating then
125 --
126 -- Get the latest change date for all pay proposals for the assignment
127 --
128 open csr_get_max_pp_chg_date;
129 fetch csr_get_max_pp_chg_date into l_max_pp_chg_date;
130 close csr_get_max_pp_chg_date;
131 hr_utility.set_location(l_proc, 7);
132 --
133 -- Check if any pay proposal change dates exist for the assignment
134 -- and error if a pay proposal change date exists after the validation
135 -- start date for the assignment.
136 --
137 if l_max_pp_chg_date > p_validation_start_date then
138 --
139 hr_utility.set_message(801, 'HR_51171_ASG_INV_PB_PP_CD');
140 hr_multi_message.add
141 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
142 );
143 --
144 end if;
145 hr_utility.set_location(l_proc, 8);
146 end if;
147 --
148 end if;
149 --
150 end if;
151 --
152 hr_utility.set_location(' Leaving:'|| l_proc, 9);
153 end chk_pay_basis_id;
154 --
155 -- ---------------------------------------------------------------------------
156 -- |------------------------< chk_payroll_id >-------------------------------|
157 -- ---------------------------------------------------------------------------
158 --
159 procedure chk_payroll_id
160 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
161 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
162 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
163 ,p_person_id in per_all_assignments_f.person_id%TYPE
164 ,p_payroll_id in per_all_assignments_f.payroll_id%TYPE
165 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
166 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
167 ,p_effective_date in date
168 ,p_datetrack_mode in varchar2
169 ,p_payroll_id_updated out nocopy boolean
170 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
171 )
172 is
173 --
174 l_proc varchar2(72) := g_package||'chk_payroll_id';
175 --
176 cursor csr_pradd_exists is
177 select address_line1
178 from per_addresses
179 where person_id = p_person_id
180 and primary_flag='Y'
181 and ( (style='US' and region_1 is not null)
182 or style<>'US');
183 --
184 l_address_line1 per_addresses.address_line1%type;
185 --
186 cursor csr_get_person_dob is
187 select date_of_birth
188 from per_people_f
189 where person_id = p_person_id
190 and p_effective_date between effective_start_date
191 and effective_end_date;
192 --
193 l_date_of_birth per_all_people_f.date_of_birth%type;
194 l_payroll_id_updated boolean;
195 --
196 begin
197 hr_utility.set_location('Entering:'|| l_proc, 10);
198 --
199 open csr_pradd_exists;
200 fetch csr_pradd_exists into l_address_line1;
201 close csr_pradd_exists;
202 --
203 hr_utility.set_location(l_proc, 20);
204 --
205 open csr_get_person_dob;
206 fetch csr_get_person_dob into l_date_of_birth;
207 close csr_get_person_dob;
208 --
209 hr_utility.set_location(l_proc, 30);
210 --
211 per_asg_bus2.chk_payroll_id_int
212 (p_assignment_id => p_assignment_id
213 ,p_business_group_id => p_business_group_id
214 ,p_assignment_type => p_assignment_type
215 ,p_person_id => p_person_id
216 ,p_payroll_id => p_payroll_id
217 ,p_validation_start_date => p_validation_start_date
218 ,p_validation_end_date => p_validation_end_date
219 ,p_effective_date => p_effective_date
220 ,p_datetrack_mode => p_datetrack_mode
221 ,p_address_line1 => l_address_line1
222 ,p_date_of_birth => l_date_of_birth
223 ,p_payroll_id_updated => l_payroll_id_updated
224 ,p_object_version_number => p_object_version_number
225 );
226 --
227 p_payroll_id_updated:=l_payroll_id_updated;
228 --
229 hr_utility.set_location(' Leaving:'|| l_proc, 300);
230 end chk_payroll_id;
231 --
232 --
233 -- ---------------------------------------------------------------------------
234 -- |----------------------< chk_payroll_id_int >------------------------------|
235 -- ---------------------------------------------------------------------------
236 --
237 procedure chk_payroll_id_int
238 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
239 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
240 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
241 ,p_person_id in per_all_assignments_f.person_id%TYPE
242 ,p_payroll_id in per_all_assignments_f.payroll_id%TYPE
243 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
244 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
245 ,p_effective_date in date
246 ,p_datetrack_mode in varchar2
247 ,p_address_line1 in per_addresses.address_line1%type
248 ,p_date_of_birth in per_all_people_f.date_of_birth%type
249 ,p_payroll_id_updated out nocopy boolean
250 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
251 )
252 is
253 --
254 l_legislation_code per_business_groups.legislation_code%TYPE;
255 l_api_updating boolean;
256 l_cur_opu_effective_start_date date;
257 l_cur_opu_effective_end_date date;
258 l_business_group_id number(15);
259 l_exists varchar2(1);
260 l_future_change boolean;
261 l_invalid_ppm boolean;
262 l_min_opu_effective_start_date date;
263 l_min_ppm_effective_start_date date;
264 l_max_opu_effective_end_date date;
265 l_max_ppm_effective_end_date date;
266 l_org_payment_method_id
267 pay_personal_payment_methods_f.org_payment_method_id%TYPE;
268 l_org_pay_method_usage_id
269 pay_org_pay_method_usages_f.org_pay_method_usage_id%TYPE;
270 l_personal_payment_method_id
271 pay_personal_payment_methods_f.personal_payment_method_id%TYPE;
272 l_proc varchar2(72) := g_package||'chk_payroll_id_int';
273 l_working_start_date date;
274 l_working_end_date date;
275
276 -- Bug 979903
277 cursor csr_get_legc_code is
278 select legislation_code
279 from per_business_groups_perf
280 where business_group_id = p_business_group_id;
281 --
282 --VS Bug:1402408. 11/14/00
283 cursor csr_payroll_exists is
284 select null
285 from sys.dual
286 where exists(select null
287 from pay_all_payrolls_f pp
288 where p_effective_date
289 between pp.effective_start_date
290 and pp.effective_end_date
291 and pp.payroll_id = p_payroll_id);
292 --
293 cursor csr_get_bus_grp is
294 select business_group_id
295 from pay_all_payrolls_f
296 where payroll_id = p_payroll_id
297 and p_effective_date between effective_start_date
298 and effective_end_date;
299 --
300 cursor csr_get_ppms is
301 select ppm.personal_payment_method_id
302 ,ppm.org_payment_method_id
303 ,min(ppm.effective_start_date)
304 ,max(ppm.effective_end_date)
305 from pay_personal_payment_methods_f ppm
306 where ppm.assignment_id = p_assignment_id
307 and ppm.effective_start_date <= p_validation_end_date
308 and ppm.effective_end_date >= p_validation_start_date
309 group by ppm.personal_payment_method_id
310 ,ppm.org_payment_method_id;
311 --
312 cursor csr_get_opus
313 (c_org_payment_method_id number
314 ,c_effective_start_date date
315 ,c_effective_end_date date
316 ) is
317 select opu.org_pay_method_usage_id
318 ,min(opu.effective_start_date)
319 ,max(opu.effective_end_date)
320 from pay_org_pay_method_usages_f opu
321 where opu.org_payment_method_id = c_org_payment_method_id
322 and opu.payroll_id = p_payroll_id
323 and opu.effective_start_date <= c_effective_end_date
324 and opu.effective_end_date >= c_effective_start_date
325 group by opu.org_pay_method_usage_id
326 order by 2;
327 --
328 cursor csr_any_future_changes is
329 select null
330 from per_all_assignments_f asg
331 where asg.assignment_id = p_assignment_id
332 and asg.payroll_id <> p_payroll_id
333 and asg.effective_start_date <= p_validation_end_date
334 and asg.effective_end_date >= p_validation_start_date;
335 --
336 cursor csr_any_future_asas is
337 select null
338 from pay_assignment_actions asa
339 ,pay_payroll_actions pra
340 ,per_all_assignments_f paf
341 where asa.assignment_id = p_assignment_id
342 and pra.payroll_action_id = asa.payroll_action_id
343 --
344 -- Fix for bug 3693830 starts here.
345 --
346 and paf.assignment_id = p_assignment_id
347 and nvl(paf.payroll_id,-1) <> nvl(p_payroll_id,-1)
348 and paf.effective_end_date >= p_validation_start_date
349 and paf.effective_start_date <= p_validation_end_date
350 --
351 -- Fix for bug 3693830 ends here.
352 --
353 -- Start of Bug fix: 2185300.
354 --
355 and pra.action_type not in ('X','BEE') -- Fix for bug# 2711532
356 and ((pra.effective_date
357 between p_validation_start_date
358 and p_validation_end_date )
359 or (nvl(pra.date_earned,p_validation_start_date-1) >= p_validation_start_date
360 and nvl(pra.date_earned,p_validation_end_date+1) <= p_validation_end_date )
361 );
362 --
363 -- End of Bug fix: 2185300.
364 --
365 begin
366 hr_utility.set_location('Entering:'|| l_proc, 10);
367 --
368 -- Initialize payroll updated flag
369 --
370 p_payroll_id_updated := FALSE;
371 --
372 -- Check mandatory parameters have been set
373 --
374 hr_api.mandatory_arg_error
375 (p_api_name => l_proc
376 ,p_argument => 'business_group_id'
377 ,p_argument_value => p_business_group_id
378 );
379 --
380 hr_api.mandatory_arg_error
381 (p_api_name => l_proc
382 ,p_argument => 'validation_start_date'
383 ,p_argument_value => p_validation_start_date
384 );
385 --
386 hr_api.mandatory_arg_error
387 (p_api_name => l_proc
388 ,p_argument => 'effective_date'
389 ,p_argument_value => p_effective_date
390 );
391 --
392 hr_api.mandatory_arg_error
393 (p_api_name => l_proc
394 ,p_argument => 'validation_end_date'
395 ,p_argument_value => p_validation_end_date
396 );
397 hr_utility.set_location(l_proc, 20);
398 --
399 l_api_updating := per_asg_shd.api_updating
400 (p_assignment_id => p_assignment_id
401 ,p_effective_date => p_effective_date
402 ,p_object_version_number => p_object_version_number
403 );
404 hr_utility.set_location(l_proc, 30);
405 --
406 if (l_api_updating and
407 ((nvl(per_asg_shd.g_old_rec.payroll_id, hr_api.g_number)
408 <> nvl(p_payroll_id, hr_api.g_number)) or
409 (per_asg_shd.g_old_rec.assignment_type='A' and
410 p_assignment_type='E')
411 )
412 )
413 or NOT l_api_updating
414 then
415 hr_utility.set_location(l_proc, 40);
416 --
417 if (l_api_updating and
418 nvl(per_asg_shd.g_old_rec.payroll_id, hr_api.g_number)
419 <> nvl(p_payroll_id, hr_api.g_number)) then
420 --
421 -- As payroll id has been updated, set p_payroll_id_updated to true.
422 -- This functionality is required for the /update/delete_assignment
423 -- business processes
424 --
425 hr_utility.set_location(l_proc, 45);
426 p_payroll_id_updated := TRUE;
427 end if;
428 --
429 if p_payroll_id is not null then
430 --
431 -- Check that the assignment is an employee or applicant or benefit
432 -- or offer assignment.
433 -- added functionality to allow applicant to have a payroll specified
434 --
435 -- <OAB_CHANGE> - Extend restriction to allow assignment type 'B'
436 --
437 if p_assignment_type not in ('E','A','B','O') then
438 --
439 hr_utility.set_message(801, 'HR_51226_ASG_INV_ASG_TYP_PAY');
440 hr_multi_message.add
441 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
442 );
443 --
444 end if;
445 hr_utility.set_location(l_proc, 50);
446 --
447 -- Check if GEOCODES is installed
448 --
449
450 -- Bug 979903
451 open csr_get_legc_code;
452 fetch csr_get_legc_code into l_legislation_code;
453 close csr_get_legc_code;
454
455 if hr_general.chk_geocodes_installed = 'Y'
456 and p_assignment_type = 'E'
457 and ( ( l_legislation_code = 'CA'
458 and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
459 p_legislation => 'CA'))
460 OR ( l_legislation_code = 'US'
461 and hr_utility.chk_product_install(p_product => 'Oracle Payroll',
462 p_legislation => 'US')))
463 then
464 --
465 -- Check if a primary address exists for the person
466 -- of the employee assignment
467 --
468 if p_address_line1 is null then
469 hr_utility.set_message(800, 'PER_52990_ASG_PRADD_NE_PAY');
470 hr_multi_message.add
471 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
472 );
473 --
474 end if;
475 hr_utility.set_location(l_proc, 55);
476 --
477 end if;
478 --
479 -- Check that payroll exists and the effective start date of the
480 -- assignment is the same as or after the effective start date
481 -- of the payroll. Also the effective end date of the assignment
482 -- is the same as or before the effective end date of the payroll.
483 --
484 open csr_payroll_exists;
485 fetch csr_payroll_exists into l_exists;
486 if csr_payroll_exists%notfound then
487 close csr_payroll_exists;
488 hr_utility.set_message(801, 'HR_7370_ASG_INVALID_PAYROLL');
489 hr_multi_message.add
490 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
491 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
492 );
493 else
494 close csr_payroll_exists;
495 end if;
496 hr_utility.set_location(l_proc, 60);
497 --
498 -- Check that business group of payroll is the
499 -- same as that of the assignment
500 --
501 open csr_get_bus_grp;
502 fetch csr_get_bus_grp into l_business_group_id;
503 if l_business_group_id <> p_business_group_id then
504 close csr_get_bus_grp;
505 hr_utility.set_message(801, 'HR_7373_ASG_INVALID_BG_PAYROLL');
506 hr_multi_message.add
507 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
508 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
509 );
510 else
511 close csr_get_bus_grp;
512 end if;
513 hr_utility.set_location(l_proc, 70);
514 --
515 -- Check that person to whom the assignment is linked
516 -- has their D.O.B. recorded on per_people_f
517 --
518 if p_assignment_type = 'E' then
519 hr_utility.set_location(l_proc, 75);
520 if p_date_of_birth is null then
521 hr_utility.set_message(801, 'HR_7378_ASG_NO_DATE_OF_BIRTH');
522 hr_multi_message.add
523 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATE_OF_BIRTH'
524 );
525 end if;
526 hr_utility.set_location(l_proc, 80);
527 end if;
528 end if;
529 end if;
530 --
531 -- Now determine if CHK_PAYROLL_ID / e should be enforced.
532 --
533 open csr_any_future_changes;
534 fetch csr_any_future_changes into l_exists;
535 l_future_change := csr_any_future_changes%FOUND;
536 close csr_any_future_changes;
537 hr_utility.set_location(l_proc, 90);
538 --
539 if (l_api_updating
540 and (per_asg_shd.g_old_rec.payroll_id is not null
541 and p_payroll_id is not null
542 and per_asg_shd.g_old_rec.payroll_id <> p_payroll_id))
543 or (l_future_change
544 and (p_datetrack_mode = 'DELETE_NEXT_CHANGE'
545 or p_datetrack_mode = 'FUTURE_CHANGE'
546 or p_datetrack_mode = 'UPDATE_OVERRIDE'))
547 then
548 --
549 hr_utility.set_location(l_proc, 100);
550 --
551 l_invalid_ppm := FALSE;
552 l_working_start_date := p_validation_start_date;
553 --
554 -- Get all PPMs for this assignment that are effective at some point in the
555 -- validation range.
556 --
557 open csr_get_ppms;
558 fetch csr_get_ppms
559 into l_personal_payment_method_id
560 ,l_org_payment_method_id
561 ,l_min_ppm_effective_start_date
562 ,l_max_ppm_effective_end_date;
563 --
564 hr_utility.set_location(l_proc, 110);
565 hr_utility.trace
566 ('p_payroll_id = ' || p_payroll_id);
567 hr_utility.trace
568 ('p_validation_start_date = ' || p_validation_start_date);
569 hr_utility.trace
570 ('p_validation_end_date = ' || p_validation_end_date);
571 hr_utility.trace
572 ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
573 hr_utility.trace
574 ('l_org_payment_method_id = ' || l_org_payment_method_id);
575 hr_utility.trace
576 ('l_min_ppm_effective_start_date = ' || l_min_ppm_effective_start_date);
577 hr_utility.trace
578 ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
579 --
580 -- If a PPM has been retrieved, and no invalid PPMs have been identified
581 -- yet and we have not yet reached the validation end date then check the
582 -- current PPM retrieved.
583 --
584 while csr_get_ppms%FOUND
585 and not l_invalid_ppm
586 and l_working_start_date < p_validation_end_date
587 loop
588 --
589 hr_utility.set_location(l_proc, 120);
590 --
591 -- Get the latest end date for all OPUs that are effective for the
592 -- current working date for the current PPM for the payroll id. As we are
593 -- only interested in OPUs that span the current PPM, setting the current
594 -- working date to the later of the validation start date or the PPM
595 -- start date restricts the date range required.
596 --
597 if l_min_ppm_effective_start_date > p_validation_start_date
598 then
599 l_working_start_date := l_min_ppm_effective_start_date;
600 else
601 l_working_start_date := p_validation_start_date;
602 end if;
603 --
604 if l_max_ppm_effective_end_date < p_validation_end_date
605 then
606 l_working_end_date := l_max_ppm_effective_end_date;
607 else
608 l_working_end_date := p_validation_end_date;
609 end if;
610 --
611 hr_utility.set_location(l_proc, 130);
612 hr_utility.trace
613 ('l_working_start_date = ' || l_working_start_date);
614 hr_utility.trace
615 ('l_working_end_date = ' || l_working_end_date);
616 --
617 open csr_get_opus
618 (l_org_payment_method_id
619 ,l_working_start_date
620 ,l_working_end_date
621 );
622 fetch csr_get_opus
623 into l_org_pay_method_usage_id
624 ,l_cur_opu_effective_start_date
625 ,l_cur_opu_effective_end_date;
626 --
627 l_min_opu_effective_start_date := nvl(l_cur_opu_effective_start_date,
628 hr_api.g_eot);
629 l_max_opu_effective_end_date := nvl(l_cur_opu_effective_end_date,
630 hr_api.g_date);
631 --
632 hr_utility.set_location(l_proc, 140);
633 hr_utility.trace
634 ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
635 hr_utility.trace
636 ('l_min_opu_effective_start_date = ' || l_min_opu_effective_start_date);
637 hr_utility.trace
638 ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
639 --
640 while csr_get_opus%FOUND
641 and not l_invalid_ppm
642 and (l_min_opu_effective_start_date > l_working_start_date
643 or l_max_opu_effective_end_date < l_working_end_date
644 )
645 loop
646 --
647 hr_utility.set_location(l_proc, 150);
648 --
649 if l_cur_opu_effective_start_date < l_min_opu_effective_start_date
650 then
651 l_min_opu_effective_start_date := l_cur_opu_effective_start_date;
652 end if;
653 --
654 if l_cur_opu_effective_end_date > l_max_opu_effective_end_date
655 then
656 l_max_opu_effective_end_date := l_cur_opu_effective_end_date;
657 end if;
658 --
659 fetch csr_get_opus
660 into l_org_pay_method_usage_id
661 ,l_cur_opu_effective_start_date
662 ,l_cur_opu_effective_end_date;
663 --
664 hr_utility.set_location(l_proc, 160);
665 hr_utility.trace
666 ('l_min_opu_effective_start_date = ' ||
667 l_min_opu_effective_start_date);
668 hr_utility.trace
669 ('l_max_opu_effective_end_date = ' || l_max_opu_effective_end_date);
670 hr_utility.trace
671 ('l_org_pay_method_usage_id = ' || l_org_pay_method_usage_id);
672 hr_utility.trace
673 ('l_cur_opu_effective_start_date = ' ||
674 l_cur_opu_effective_start_date);
675 hr_utility.trace
676 ('l_cur_opu_effective_end_date = ' || l_cur_opu_effective_end_date);
677 --
678 if l_cur_opu_effective_start_date - 1 > l_max_opu_effective_end_date
679 then
680 --
681 hr_utility.set_location(l_proc, 170);
682 --
683 -- We have found a 'hole'.
684 --
685 -- ie. h
686 -- <--------|o
687 -- l|------------>
688 -- e
689 --
690 l_invalid_ppm := TRUE;
691 end if;
692 end loop;
693 --
694 hr_utility.set_location(l_proc, 180);
695 --
696 close csr_get_opus;
697 --
698 if l_min_opu_effective_start_date > l_working_start_date
699 or l_max_opu_effective_end_date < l_working_end_date
700 then
701 --
702 hr_utility.set_location(l_proc, 190);
703 --
704 l_invalid_ppm := TRUE;
705 else
706 --
707 hr_utility.set_location(l_proc, 200);
708 --
709 fetch csr_get_ppms
710 into l_personal_payment_method_id
711 ,l_org_payment_method_id
712 ,l_min_ppm_effective_start_date
713 ,l_max_ppm_effective_end_date;
714 --
715 if l_min_ppm_effective_start_date > p_validation_start_date
716 then
717 l_working_start_date := l_min_ppm_effective_start_date;
718 else
719 l_working_start_date := p_validation_start_date;
720 end if;
721 --
722 hr_utility.set_location(l_proc, 210);
723 hr_utility.trace
724 ('l_personal_payment_method_id = ' || l_personal_payment_method_id);
725 hr_utility.trace
726 ('l_org_payment_method_id = ' || l_org_payment_method_id);
727 hr_utility.trace
728 ('l_min_ppm_effective_start_date = ' ||
729 l_min_ppm_effective_start_date);
730 hr_utility.trace
731 ('l_max_ppm_effective_end_date = ' || l_max_ppm_effective_end_date);
732 --
733 end if;
734 end loop;
735 --
736 close csr_get_ppms;
737 hr_utility.set_location(l_proc, 220);
738 --
739 if l_invalid_ppm
740 then
741 --
742 hr_utility.set_location(l_proc, 230);
743 --
744 hr_utility.set_message(801, 'HR_7969_ASG_INV_PAYROLL_PPMS');
745 hr_multi_message.add
746 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
747 );
748 end if;
749 end if;
750 --
751 if (l_api_updating
752 and per_asg_shd.g_old_rec.payroll_id <> p_payroll_id
753 )
754 or (p_datetrack_mode = 'DELETE_NEXT_CHANGE'
755 or p_datetrack_mode = 'FUTURE_CHANGE'
756 or p_datetrack_mode = 'UPDATE_OVERRIDE'
757 )
758 then
759 --
760 hr_utility.set_location(l_proc, 220);
761 --
762 -- Find any ASAs that arise after the change effective date.
763 --
764 open csr_any_future_asas;
765 fetch csr_any_future_asas
766 into l_exists;
767 --
768 if csr_any_future_asas%FOUND
769 then
770 --
771 hr_utility.set_location(l_proc, 230);
772 --
773 close csr_any_future_asas;
774 --
775 hr_utility.set_message(801, 'HR_7975_ASG_INV_FUTURE_ASA');
776 hr_multi_message.add
777 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
778 );
779 else
780 --
781 hr_utility.set_location(l_proc, 240);
782 --
783 close csr_any_future_asas;
784 end if;
785 --
786 hr_utility.set_location(l_proc, 250);
787 end if;
788 --
789 hr_utility.set_location(' Leaving:'|| l_proc, 300);
790 end chk_payroll_id_int;
791 --
792 -- ---------------------------------------------------------------------------
793 -- |-----------------------< chk_people_group_id >---------------------------|
794 -- ---------------------------------------------------------------------------
795 --
796 procedure chk_people_group_id
797 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
798 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
799 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
800 ,p_people_group_id in per_all_assignments_f.people_group_id%TYPE
801 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
802 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
803 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
804 ,p_effective_date in date
805 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
806 )
807 is
808 --
809 l_exists varchar2(1);
810 l_api_updating boolean;
811 l_proc varchar2(72) := g_package||'chk_people_group_id';
812 l_vac_people_group_id per_all_assignments_f.people_group_id%TYPE;
813 l_enabled_flag pay_people_groups.enabled_flag%TYPE;
814 --
815 cursor csr_valid_people_group is
816 select enabled_flag
817 from pay_people_groups
818 where people_group_id = p_people_group_id
819 and p_validation_start_date
820 between nvl(start_date_active,hr_api.g_sot)
821 and nvl(end_date_active,hr_api.g_eot);
822 --
823 cursor csr_valid_id_flex_num is
824 select null
825 from per_business_groups_perf pbg
826 ,pay_people_groups ppg
827 where ppg.people_group_id = p_people_group_id
828 and pbg.people_group_structure = to_char(ppg.id_flex_num)
829 and pbg.business_group_id = p_business_group_id;
830 --
831 begin
832 hr_utility.set_location('Entering:'|| l_proc, 10);
833 --
834 --
835 -- Check mandatory parameters have been set
836 --
837 hr_api.mandatory_arg_error
838 (p_api_name => l_proc
839 ,p_argument => 'business_group_id'
840 ,p_argument_value => p_business_group_id
841 );
842 --
843 hr_api.mandatory_arg_error
844 (p_api_name => l_proc
845 ,p_argument => 'validation_start_date'
846 ,p_argument_value => p_validation_start_date
847 );
848 --
849 hr_api.mandatory_arg_error
850 (p_api_name => l_proc
851 ,p_argument => 'effective_date'
852 ,p_argument_value => p_effective_date
853 );
854 --
855 hr_api.mandatory_arg_error
856 (p_api_name => l_proc
857 ,p_argument => 'validation_end_date'
858 ,p_argument_value => p_validation_end_date
859 );
860 hr_utility.set_location(l_proc, 20);
861 --
862 -- Only proceed with validation if :
863 -- a) The current g_old_rec is current and
864 -- b) The value for people group has changed
865 --
866 l_api_updating := per_asg_shd.api_updating
867 (p_assignment_id => p_assignment_id
868 ,p_effective_date => p_effective_date
869 ,p_object_version_number => p_object_version_number
870 );
871 hr_utility.set_location(l_proc, 30);
872 --
873 if ((l_api_updating and
874 nvl(per_asg_shd.g_old_rec.people_group_id, hr_api.g_number) <>
875 nvl(p_people_group_id, hr_api.g_number)) or
876 (NOT l_api_updating))
877 then
878 hr_utility.set_location(l_proc, 40);
879 --
880 -- Check if people group is set
881 --
882 if p_people_group_id is not null then
883 --
884 -- Check that the people group exists
885 --
886 open csr_valid_people_group;
887 fetch csr_valid_people_group into l_enabled_flag;
888 if csr_valid_people_group%notfound then
889 close csr_valid_people_group;
890 hr_utility.set_message(801, 'HR_7385_ASG_INV_PEOPLE_GROUP');
891 hr_multi_message.add
892 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
893 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
894 );
895 --
896 else
897 close csr_valid_people_group;
898 hr_utility.set_location(l_proc, 50);
899 --
900 -- Check that the enabled flag is set to 'Y' for the people group.
901 --
902 If l_enabled_flag <> 'Y' then
903 --
904 hr_utility.set_message(801, 'HR_51252_ASG_INV_PGP_ENBD_FLAG');
905 hr_multi_message.add
906 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
907 );
908 --
909 end if;
910 end if;
911 hr_utility.set_location(l_proc, 60);
912 --
913 -- Check that the id_flex_num value for the
914 -- people_group_id can be cross referenced to the
915 -- people_group_structure on per_business_groups for
916 -- the assignment business group
917 --
918 open csr_valid_id_flex_num;
919 fetch csr_valid_id_flex_num into l_exists;
920 if csr_valid_id_flex_num%notfound then
921 close csr_valid_id_flex_num;
922 hr_utility.set_message(801, 'HR_7386_ASG_INV_PEOP_GRP_LINK');
923 hr_multi_message.add
924 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PEOPLE_GROUP_ID'
925 );
926 --
927 else
928 close csr_valid_id_flex_num;
929 end if;
930 hr_utility.set_location(l_proc, 70);
931 --
932 end if;
933 --
934 end if;
935 hr_utility.set_location(' Leaving:'|| l_proc, 100);
936 --
937 end chk_people_group_id;
938 --
939 -- ---------------------------------------------------------------------------
940 -- |-------------------< chk_perf_review_period_freq >-----------------------|
941 -- ---------------------------------------------------------------------------
942 --
943 procedure chk_perf_review_period_freq
944 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
945 ,p_perf_review_period_frequency in per_all_assignments_f.perf_review_period_frequency%TYPE
946 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
947 ,p_effective_date in date
948 ,p_validation_start_date in date
949 ,p_validation_end_date in date
950 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
951 )
952 is
953 --
954 l_proc varchar2(72) := g_package||'chk_perf_review_period_freq';
955 l_api_updating boolean;
956 --
957 begin
958 hr_utility.set_location('Entering:'|| l_proc, 10);
959 --
960 -- Check mandatory parameters have been set
961 --
962 hr_api.mandatory_arg_error
963 (p_api_name => l_proc
964 ,p_argument => 'effective_date'
965 ,p_argument_value => p_effective_date
966 );
967 --
968 hr_api.mandatory_arg_error
969 (p_api_name => l_proc
970 ,p_argument => 'validation_start_date'
971 ,p_argument_value => p_validation_start_date
972 );
973 --
974 hr_api.mandatory_arg_error
975 (p_api_name => l_proc
976 ,p_argument => 'validation_end_date'
977 ,p_argument_value => p_validation_end_date
978 );
979 hr_utility.set_location(l_proc, 20);
980 --
981 -- Only proceed with validation if :
982 -- a) The current g_old_rec is current and
983 -- b) The value for performance review period frequency has changed
984 --
985 l_api_updating := per_asg_shd.api_updating
986 (p_assignment_id => p_assignment_id
987 ,p_effective_date => p_effective_date
988 ,p_object_version_number => p_object_version_number
989 );
990 hr_utility.set_location(l_proc, 30);
991 --
992 if ((l_api_updating and
993 nvl(per_asg_shd.g_old_rec.perf_review_period_frequency,
994 hr_api.g_varchar2) <> nvl(p_perf_review_period_frequency,
995 hr_api.g_varchar2))
996 or
997 (NOT l_api_updating))
998 then
999 hr_utility.set_location(l_proc, 40);
1000 --
1001 -- Check if performance review period frequency is set
1002 --
1003 if p_perf_review_period_frequency is not null then
1004 --
1005 -- Check that the assignment is an employee or applicant
1006 -- or benefit or offer assignment.
1007 --
1008 if p_assignment_type not in ('E','A','B','O') then
1009 --
1010 hr_utility.set_message(801, 'HR_51178_ASG_INV_ASG_TYP_PRPF');
1011 hr_multi_message.add
1012 (p_associated_column1 =>
1013 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1014 );
1015 --
1016 end if;
1017 hr_utility.set_location(l_proc, 50);
1018 --
1019 -- Check that the performance review period frequency exists in
1020 -- hr_lookups for the lookup type 'FREQUENCY' with an enabled
1021 -- flag set to 'Y' and that the effective start date of the
1022 -- assignment is between start date active and end date active
1023 -- in hr_lookups.
1024 --
1025 if hr_api.not_exists_in_dt_hr_lookups
1026 (p_effective_date => p_effective_date
1027 ,p_validation_start_date => p_validation_start_date
1028 ,p_validation_end_date => p_validation_end_date
1029 ,p_lookup_type => 'FREQUENCY'
1030 ,p_lookup_code => p_perf_review_period_frequency
1031 )
1032 then
1033 --
1034 hr_utility.set_message(801, 'HR_51149_ASG_INV_PRP_FREQ');
1035 hr_multi_message.add
1036 (p_associated_column1 =>
1037 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1038 );
1039 --
1040 end if;
1041 hr_utility.set_location(l_proc, 60);
1042 --
1043 end if;
1044 hr_utility.set_location(l_proc, 70);
1045 --
1046 end if;
1047 hr_utility.set_location(' Leaving:'|| l_proc, 80);
1048 --
1049 end chk_perf_review_period_freq;
1050 --
1051 -- ---------------------------------------------------------------------------
1052 -- |-----------------------< chk_perf_review_period >------------------------|
1053 -- ---------------------------------------------------------------------------
1054 --
1055 procedure chk_perf_review_period
1056 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1057 ,p_perf_review_period in per_all_assignments_f.perf_review_period%TYPE
1058 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1059 ,p_effective_date in date
1060 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1061 )
1062 is
1063 --
1064 l_proc varchar2(72) := g_package||'chk_perf_review_period';
1065 l_api_updating boolean;
1066 --
1067 begin
1068 hr_utility.set_location('Entering:'|| l_proc, 1);
1069 --
1070 -- Check mandatory parameters have been set
1071 --
1072 hr_api.mandatory_arg_error
1073 (p_api_name => l_proc
1074 ,p_argument => 'effective_date'
1075 ,p_argument_value => p_effective_date
1076 );
1077 --
1078 -- Only proceed with validation if :
1079 -- a) The current g_old_rec is current and
1080 -- b) The value for perf review period has changed
1081 --
1082 l_api_updating := per_asg_shd.api_updating
1083 (p_assignment_id => p_assignment_id
1084 ,p_effective_date => p_effective_date
1085 ,p_object_version_number => p_object_version_number);
1086 --
1087 hr_utility.set_location(l_proc, 2);
1088 --
1089 if ((l_api_updating and
1090 nvl(per_asg_shd.g_old_rec.perf_review_period,
1091 hr_api.g_number) <> nvl(p_perf_review_period,
1092 hr_api.g_number))
1093 or (NOT l_api_updating))
1094 then
1095 --
1096 hr_utility.set_location(l_proc, 3);
1097 --
1098 -- Check if perf review period is not null
1099 --
1100 if p_perf_review_period is not null then
1101 --
1102 -- Check that the assignment is an Employee or Applicant
1103 -- Benefit or Offer assignment.
1104 --
1105 if p_assignment_type not in ('E','A','B','O') then
1106 --
1107 hr_utility.set_message(801, 'HR_51179_ASG_INV_ASG_TYP_PRP');
1108 hr_utility.raise_error;
1109 end if;
1110 hr_utility.set_location(l_proc, 4);
1111 --
1112 end if;
1113 --
1114 end if;
1115 --
1116 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1117 exception
1118 when app_exception.application_exception then
1119 if hr_multi_message.exception_add
1120 (p_associated_column1 =>
1121 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1122 ) then
1123 hr_utility.set_location(' Leaving:'|| l_proc, 6);
1124 raise;
1125 end if;
1126 hr_utility.set_location(' Leaving:'|| l_proc, 7);
1127 end chk_perf_review_period;
1128 --
1129 -- ---------------------------------------------------------------------------
1130 -- |-------------------< chk_perf_rp_freq_perf_rp >--------------------------|
1131 -- ---------------------------------------------------------------------------
1132 --
1133 procedure chk_perf_rp_freq_perf_rp
1134 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1135 ,p_perf_review_period_frequency in per_all_assignments_f.perf_review_period_frequency%TYPE
1136 ,p_perf_review_period in per_all_assignments_f.perf_review_period%TYPE
1137 ,p_effective_date in date
1138 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1139 )
1140 is
1141 --
1142 l_proc varchar2(72):= g_package||'chk_perf_rp_freq_perf_rp';
1143 l_api_updating boolean;
1144 --
1145 begin
1146 hr_utility.set_location('Entering:'|| l_proc, 1);
1147 --
1148 if hr_multi_message.no_exclusive_error
1149 (p_check_column1 =>
1150 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1151 ,p_check_column2 =>
1152 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1153 ) then
1154 --
1155 -- Check mandatory parameters have been set
1156 --
1157 hr_api.mandatory_arg_error
1158 (p_api_name => l_proc
1159 ,p_argument => 'effective_date'
1160 ,p_argument_value => p_effective_date
1161 );
1162 --
1163 -- Check if the assignment is being updated.
1164 --
1165 l_api_updating := per_asg_shd.api_updating
1166 (p_assignment_id => p_assignment_id
1167 ,p_effective_date => p_effective_date
1168 ,p_object_version_number => p_object_version_number);
1169 --
1170 -- Only proceed with validation if :
1171 -- a) The current g_old_rec is current and
1172 -- b) The value for perf review period frequency or perf review period has
1173 -- changed.
1174 --
1175 if ((l_api_updating)
1176 and
1177 ((nvl(per_asg_shd.g_old_rec.perf_review_period_frequency,
1178 hr_api.g_varchar2) <> nvl(p_perf_review_period_frequency, hr_api.g_varchar2))
1179 or
1180 (nvl(per_asg_shd.g_old_rec.perf_review_period,
1181 hr_api.g_number) <> nvl(p_perf_review_period, hr_api.g_number)))
1182 or
1183 (NOT l_api_updating)) then
1184 --
1185 hr_utility.set_location(l_proc, 2);
1186 --
1187 -- Check if perf review period frequency or perf review period is not null.
1188 --
1189 if p_perf_review_period_frequency is not null
1190 or p_perf_review_period is not null then
1191 hr_utility.set_location(l_proc, 3);
1192 --
1193 -- Check if perf review period frequency or perf review period are null.
1194 --
1195 if p_perf_review_period_frequency is null
1196 or p_perf_review_period is null then
1197 --
1198 hr_utility.set_message(801, 'HR_51163_ASG_INV_PRPF_PRP_COMB');
1199 hr_utility.raise_error;
1200 --
1201 end if;
1202 --
1203 end if;
1204 --
1205 end if;
1206 end if;
1207 --
1208 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1209 --
1210 exception
1211 when app_exception.application_exception then
1212 if hr_multi_message.exception_add
1213 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD'
1214 ,p_associated_column2 =>
1215 'PER_ALL_ASSIGNMENTS_F.PERF_REVIEW_PERIOD_FREQUENCY'
1216 ) then
1217 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1218 raise;
1219 end if;
1220 hr_utility.set_location(' Leaving:'|| l_proc, 6);
1221 end chk_perf_rp_freq_perf_rp;
1222 --
1223 -- ---------------------------------------------------------------------------
1224 -- |---------------------< chk_period_of_service_id >------------------------|
1225 -- ---------------------------------------------------------------------------
1226 --
1227 procedure chk_period_of_service_id
1228 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1229 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1230 ,p_person_id in per_all_assignments_f.person_id%TYPE
1231 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1232 ,p_period_of_service_id in per_all_assignments_f.period_of_service_id%TYPE
1233 ,p_validation_start_date in date
1234 ,p_validation_end_date in date
1235 ,p_effective_date in date
1236 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1237 )
1238 is
1239 --
1240 l_api_updating boolean;
1241 l_exists varchar2(1);
1242 l_proc varchar2(72):= g_package||'chk_period_of_service_id';
1243 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
1244 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1245 --
1246 cursor csr_valid_pds is
1247 select business_group_id, actual_termination_date
1248 from per_periods_of_service
1249 where period_of_service_id = p_period_of_service_id
1250 and p_validation_start_date
1251 between date_start
1252 and nvl(actual_termination_date, hr_api.g_eot);
1253 --
1254 begin
1255 hr_utility.set_location('Entering:'|| l_proc, 10);
1256 --
1257 -- Check mandatory parameters have been set
1258 --
1259 hr_api.mandatory_arg_error
1260 (p_api_name => l_proc
1261 ,p_argument => 'person_id'
1262 ,p_argument_value => p_person_id
1263 );
1264 --
1265 hr_api.mandatory_arg_error
1266 (p_api_name => l_proc
1267 ,p_argument => 'validation_start_date'
1268 ,p_argument_value => p_validation_start_date
1269 );
1270 --
1271 hr_api.mandatory_arg_error
1272 (p_api_name => l_proc
1273 ,p_argument => 'validation_end_date'
1274 ,p_argument_value => p_validation_end_date
1275 );
1276 --
1277 hr_api.mandatory_arg_error
1278 (p_api_name => l_proc
1279 ,p_argument => 'validation_end_date'
1280 ,p_argument_value => p_effective_date
1281 );
1282 --
1283 hr_utility.set_location(l_proc, 20);
1284 --
1285 -- Check if the assignment is being updated.
1286 --
1287 l_api_updating := per_asg_shd.api_updating
1288 (p_assignment_id => p_assignment_id
1289 ,p_effective_date => p_effective_date
1290 ,p_object_version_number => p_object_version_number
1291 );
1292 hr_utility.set_location(l_proc, 30);
1293 --
1294 if NOT l_api_updating then
1295 --
1296 hr_utility.set_location(l_proc, 40);
1297 --
1298 -- Check that the assignment is an employee assignment.
1299 --
1300 if p_assignment_type <> 'E' then
1301 --
1302 -- Check that period of service is not set
1303 --
1304 If p_period_of_service_id is not null then
1305 --
1306 hr_utility.set_message(801, 'HR_51203_ASG_INV_ASG_TYP_PDS');
1307 hr_multi_message.add
1308 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1309 );
1310 --
1311 end if;
1312 hr_utility.set_location(l_proc, 50);
1313 --
1314 else
1315 --
1316 -- Check the mandatory parameter period of service for
1317 -- an employee.
1318 --
1319 hr_api.mandatory_arg_error
1320 (p_api_name => l_proc
1321 ,p_argument => 'period_of_service_id'
1322 ,p_argument_value => p_period_of_service_id
1323 );
1324 hr_utility.set_location(l_proc, 60);
1325 --
1326 -- Check if the period of service exists between
1327 -- the period of service date start and actual termination date.
1328 --
1329 open csr_valid_pds;
1330 fetch csr_valid_pds into l_business_group_id, l_actual_termination_date;
1331 if csr_valid_pds%notfound then
1332 close csr_valid_pds;
1333 hr_utility.set_message(801, 'HR_7391_ASG_INV_PERIOD_OF_SERV');
1334 hr_multi_message.add
1335 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1336 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1337 );
1338 --
1339 else
1340 close csr_valid_pds;
1341 end if;
1342 hr_utility.set_location(l_proc, 70);
1343 --
1344 -- Check that the period of service is in the same business group
1345 -- as the business group of the assignment.
1346 --
1347 If p_business_group_id <> l_business_group_id then
1348 --
1349 hr_utility.set_message(801, 'HR_51320_ASG_INV_PDS_BG');
1350 hr_multi_message.add
1351 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1352 );
1353 --
1354 end if;
1355 hr_utility.set_location(l_proc, 80);
1356 --
1357 -- Check if the period of service has been closed before the
1358 -- validation end date.
1359 --
1360 If p_validation_end_date > nvl(l_actual_termination_date, hr_api.g_eot) then
1361 --
1362 hr_utility.set_message(801, 'HR_6434_EMP_ASS_PER_CLOSED');
1363 hr_multi_message.add
1364 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
1365 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1366 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
1367 );
1368 --
1369 end if;
1370 hr_utility.set_location(l_proc, 90);
1371 --
1372 end if;
1373 --
1374 end if;
1375 hr_utility.set_location(' Leaving:'|| l_proc, 8);
1376 end chk_period_of_service_id;
1377 --
1378 -- ---------------------------------------------------------------------------
1379 -- |--------------------------< chk_person_id >------------------------------|
1380 -- ---------------------------------------------------------------------------
1381 --
1382 procedure chk_person_id
1383 (p_person_id in per_all_assignments_f.person_id%TYPE
1384 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1385 ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
1386 )
1387 is
1388 --
1389 l_exists varchar2(1);
1390 l_business_group_id number(15);
1391 l_proc varchar2(72) := g_package||'chk_person_id';
1392 --
1393 cursor csr_get_bus_grp is
1394 select ppf.business_group_id
1395 from per_people_f ppf
1396 where ppf.person_id = p_person_id
1397 and p_effective_date between ppf.effective_start_date
1398 and ppf.effective_end_date;
1399 --
1400 --
1401 begin
1402 hr_utility.set_location('Entering:'|| l_proc, 1);
1403 --
1404 -- Check mandatory parameters have been set
1405 --
1406 hr_api.mandatory_arg_error
1407 (p_api_name => l_proc
1408 ,p_argument => 'person_id'
1409 ,p_argument_value => p_person_id
1410 );
1411 --
1412 hr_utility.set_location(l_proc, 2);
1413 --
1414 -- Check that person business group is the same as
1415 -- the assignment business group
1416 --
1417 open csr_get_bus_grp;
1418 fetch csr_get_bus_grp into l_business_group_id;
1419 if l_business_group_id <> p_business_group_id then
1420 close csr_get_bus_grp;
1421 hr_utility.set_message(801, 'HR_7374_ASG_INVALID_BG_PERSON');
1422 hr_utility.raise_error;
1423 end if;
1424 close csr_get_bus_grp;
1425 --
1426 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1427 exception
1428 when app_exception.application_exception then
1429 if hr_multi_message.exception_add
1430 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1431 ) then
1432 hr_utility.set_location(' Leaving:'|| l_proc, 4);
1433 raise;
1434 end if;
1435 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1436 end chk_person_id;
1437 --
1438 -- ---------------------------------------------------------------------------
1439 -- |---------------------< chk_person_referred_by_id >-----------------------|
1440 -- ---------------------------------------------------------------------------
1441 --
1442 procedure chk_person_referred_by_id
1443 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1444 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1445 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1446 ,p_person_id in per_all_assignments_f.person_id%TYPE
1447 ,p_person_referred_by_id in per_all_assignments_f.person_referred_by_id%TYPE
1448 ,p_effective_date in date
1449 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1450 ,p_validation_start_date in date
1451 ,p_validation_end_date in date
1452 )
1453 is
1454 --
1455 l_proc varchar2(72) := g_package||'chk_person_referred_by_id';
1456 l_api_updating boolean;
1457 l_exists varchar2(1);
1458 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1459 l_current_employee_flag per_people_f.current_employee_flag%TYPE;
1460 l_current_npw_flag per_people_f.current_npw_flag%TYPE;
1461 --
1462 cursor csr_val_prb_id is
1463 select business_group_id, current_employee_flag, current_npw_flag
1464 from per_all_people_f
1465 where person_id = p_person_referred_by_id
1466 and p_validation_start_date
1467 between effective_start_date
1468 and effective_end_date;
1469 --
1470 begin
1471 hr_utility.set_location('Entering:'|| l_proc, 10);
1472 --
1473 -- Check mandatory parameters have been set
1474 --
1475 hr_api.mandatory_arg_error
1476 (p_api_name => l_proc
1477 ,p_argument => 'effective_date'
1478 ,p_argument_value => p_effective_date
1479 );
1480 --
1481 hr_api.mandatory_arg_error
1482 (p_api_name => l_proc
1483 ,p_argument => 'validation_start_date'
1484 ,p_argument_value => p_validation_start_date
1485 );
1486 --
1487 hr_api.mandatory_arg_error
1488 (p_api_name => l_proc
1489 ,p_argument => 'validation_end_date'
1490 ,p_argument_value => p_validation_end_date
1491 );
1492 --
1493 hr_utility.set_location(l_proc, 20);
1494 --
1495 -- Only proceed with validation if :
1496 -- a) The current g_old_rec is current and
1497 -- b) The value for person referred by has changed
1498 --
1499 l_api_updating := per_asg_shd.api_updating
1500 (p_assignment_id => p_assignment_id
1501 ,p_effective_date => p_effective_date
1502 ,p_object_version_number => p_object_version_number);
1503 hr_utility.set_location(l_proc, 30);
1504 --
1505 if ((l_api_updating and
1506 nvl(per_asg_shd.g_old_rec.person_referred_by_id, hr_api.g_number)
1507 <> nvl(p_person_referred_by_id, hr_api.g_number)) or
1508 (NOT l_api_updating)) then
1509 hr_utility.set_location(l_proc, 40);
1510 --
1511 -- Check if person referred by is not null
1512 --
1513 if p_person_referred_by_id is not null then
1514 --
1515 -- Check that the assignment is not an applicant or Offer assignment.
1516 --
1517 if p_assignment_type in ('E','B','C')then
1518 --
1519 hr_utility.set_message(801, 'HR_51224_ASG_INV_ASG_TYP_PRB');
1520 hr_multi_message.add
1521 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1522 );
1523 --
1524 end if;
1525 hr_utility.set_location(l_proc, 50);
1526 --
1527 -- Check that the person referred by is'nt the same as the person
1528 -- of the assignment.
1529 --
1530 If p_person_referred_by_id = p_person_id then
1531 --
1532 hr_utility.set_message(801, 'HR_51304_ASG_APL_EQUAL_PRB');
1533 hr_multi_message.add
1534 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1535 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
1536 );
1537 --
1538 end if;
1539 hr_utility.set_location(l_proc, 60);
1540 --
1541 -- Check if the person referred by exists where the effective
1542 -- start date of the assignment is between the effective start
1543 -- date and effective end date of the person referred by.
1544 --
1545 open csr_val_prb_id;
1546 fetch csr_val_prb_id
1547 into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
1548 --
1549 if csr_val_prb_id%notfound then
1550 close csr_val_prb_id;
1551 --
1552 -- Do not throw an error for Offer Assignment.
1553 --
1554 if p_assignment_type <> 'O'
1555 then
1556 --
1557 hr_utility.set_message(801, 'HR_51302_ASG_INV_PER_REF_BY');
1558 hr_multi_message.add
1559 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1560 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1561 );
1562 --
1563 end if;
1564 --
1565 else
1566 close csr_val_prb_id;
1567 end if;
1568 hr_utility.set_location(l_proc, 70);
1569 --
1570 -- Check that the person referred by is in the same business group
1571 -- as the business group of the assignment.
1572 --
1573 If (p_business_group_id <> l_business_group_id AND
1574 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N')
1575 then
1576 --
1577 hr_utility.set_message(801, 'HR_51303_ASG_INV_PER_REF_BY_BG');
1578 hr_multi_message.add
1579 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID'
1580 );
1581 --
1582 end if;
1583 hr_utility.set_location(l_proc, 80);
1584 --
1585 -- Check that the person referred by is an employee.
1586 --
1587 -- Bug 3190625
1588 -- Condition to check profile value also added
1589 -- If he is an employee or a contingent worker with the profile set,
1590 -- no errors shown
1591 if not ( (nvl(l_current_employee_flag,hr_api.g_varchar2) = 'Y' ) or
1592 ( nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y' and
1593 nvl(l_current_npw_flag, 'N') = 'Y') ) then
1594 hr_utility.set_message(801, 'HR_51305_ASG_PER_RB_NOT_EMP');
1595 hr_multi_message.add
1596 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_REFERRED_BY_ID');
1597 --
1598 end if;
1599 hr_utility.set_location(l_proc, 90);
1600 --
1601 end if;
1602 --
1603 end if;
1604 --
1605 hr_utility.set_location(' Leaving:'|| l_proc, 100);
1606 end chk_person_referred_by_id;
1607 --
1608 -- ---------------------------------------------------------------------------
1609 -- |------------------------< chk_position_id >------------------------------|
1610 -- ---------------------------------------------------------------------------
1611 --
1612 procedure chk_position_id
1613 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1614 ,p_position_id in per_all_assignments_f.position_id%TYPE
1615 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1616 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1617 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1618 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1619 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1620 ,p_effective_date in date
1621 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1622 )
1623 is
1624 --
1625 l_proc varchar2(72) := g_package||'chk_position_id';
1626 l_exists varchar2(1);
1627 l_api_updating boolean;
1628 l_position_id per_all_assignments_f.position_id%TYPE;
1629 l_pos_bus_group_id per_all_assignments_f.business_group_id%TYPE;
1630 l_vac_position_id per_all_assignments_f.position_id%TYPE;
1631 --
1632 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions_f) date tracked position requirement
1633
1634 cursor csr_valid_pos is
1635 select hp.business_group_id
1636 from hr_positions_f hp
1637 , per_shared_types ps
1638 where hp.position_id = p_position_id
1639 and p_validation_start_date
1640 between hp.effective_start_date
1641 and hp.effective_end_date
1642 and p_validation_start_date
1643 between hp.date_effective
1644 and nvl(hp.date_end, hr_api.g_eot)
1645 and ps.shared_type_id = hp.availability_status_id
1646 and ps.system_type_cd = 'ACTIVE' ;
1647 --
1648 begin
1649 hr_utility.set_location('Entering:'|| l_proc, 10);
1650 --
1651 --
1652 -- Check mandatory parameters have been set
1653 --
1654 hr_api.mandatory_arg_error
1655 (p_api_name => l_proc
1656 ,p_argument => 'effective_date'
1657 ,p_argument_value => p_effective_date
1658 );
1659 --
1660 hr_api.mandatory_arg_error
1661 (p_api_name => l_proc
1662 ,p_argument => 'validation_start_date'
1663 ,p_argument_value => p_validation_start_date
1664 );
1665 --
1666 hr_api.mandatory_arg_error
1667 (p_api_name => l_proc
1668 ,p_argument => 'validation_end_date'
1669 ,p_argument_value => p_validation_end_date
1670 );
1671 --
1672 hr_api.mandatory_arg_error
1673 (p_api_name => l_proc
1674 ,p_argument => 'business_group_id'
1675 ,p_argument_value => p_business_group_id
1676 );
1677 hr_utility.set_location(l_proc, 20);
1678 --
1679 -- Only proceed with validation if :
1680 -- a) The current g_old_rec is current and
1681 -- b) The value for position_id has changed
1682 --
1683 l_api_updating := per_asg_shd.api_updating
1684 (p_assignment_id => p_assignment_id
1685 ,p_effective_date => p_effective_date
1686 ,p_object_version_number => p_object_version_number);
1687 --
1688 if ((l_api_updating
1689 and
1690 nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
1691 nvl(p_position_id, hr_api.g_number))
1692 or
1693 (NOT l_api_updating)) then
1694 hr_utility.set_location(l_proc, 30);
1695 --
1696 -- Check that if the value for position_id is not null
1697 -- then it exists date effective in HR_POSITIONS
1698 --
1699 if p_position_id is not null then
1700 --
1701 -- Check if the position_id exists date effectively
1702 --
1703 open csr_valid_pos;
1704 fetch csr_valid_pos into l_pos_bus_group_id;
1705 if csr_valid_pos%notfound then
1706 close csr_valid_pos;
1707 hr_utility.set_message(801, 'HR_51000_ASG_INVALID_POS');
1708 hr_multi_message.add
1709 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1710 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1711 );
1712 else
1713 close csr_valid_pos;
1714 end if;
1715 hr_utility.set_location(l_proc, 40);
1716 --
1717 -- Check if the business_group_id for the assignment matches
1718 -- the business_group_id in HR_POSITIONS date effectively.
1719 --
1720 if l_pos_bus_group_id <> p_business_group_id then
1721 --
1722 hr_utility.set_message(801, 'HR_51009_ASG_INVALID_BG_POS');
1723 hr_multi_message.add
1724 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1725 );
1726 --
1727 end if;
1728 hr_utility.set_location(l_proc, 50);
1729 --
1730 end if;
1731 --
1732 --
1733 end if;
1734 --
1735 hr_utility.set_location('Leaving'||l_proc, 80);
1736 end chk_position_id;
1737 --
1738 ------------------------------------------------------------------------------
1739 -------------------< chk_position_id_grade_id >-------------------------------
1740 ------------------------------------------------------------------------------
1741 --
1742 procedure chk_position_id_grade_id
1743 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1744 ,p_position_id in per_all_assignments_f.position_id%TYPE
1745 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
1746 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1747 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1748 ,p_effective_date in date
1749 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1750 ,p_inv_pos_grade_warning out nocopy boolean
1751 )
1752 as
1753 l_proc varchar2(72) := g_package||'chk_position_id_grade_id';
1754 l_exists varchar2(1);
1755 l_exists1 varchar2(1); -- Bug 3566686
1756 l_api_updating boolean;
1757 l_inv_pos_grade_warning boolean := false;
1758 --
1759 -- Bug 3566686 Starts Here
1760 -- Description : The cursor checks whether ther are any grades defined as
1761 -- the valid grades for the selected POSITION.
1762 --
1763 cursor csr_valid_pos_val_grd_exists is
1764 select null
1765 from per_valid_grades
1766 where position_id = p_position_id
1767 and p_validation_start_date
1768 between date_from
1769 and nvl(date_to, hr_api.g_eot);
1770 --
1771 -- Bug 3566686 Ends Here
1772 --
1773 cursor csr_valid_pos_val_grd is
1774 select null
1775 from per_valid_grades
1776 where position_id = p_position_id
1777 and grade_id = p_grade_id
1778 and p_validation_start_date
1779 between date_from
1780 and nvl(date_to, hr_api.g_eot);
1781 --
1782 begin
1783 --
1784 hr_utility.set_location('Entering:'|| l_proc, 1);
1785 --
1786 if hr_multi_message.no_exclusive_error
1787 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1788 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
1789 ) then
1790 --
1791 -- Check mandatory parameters have been set
1792 --
1793 hr_api.mandatory_arg_error
1794 (p_api_name => l_proc
1795 ,p_argument => 'validation_start_date'
1796 ,p_argument_value => p_validation_start_date
1797 );
1798 --
1799 hr_api.mandatory_arg_error
1800 (p_api_name => l_proc
1801 ,p_argument => 'validation_end_date'
1802 ,p_argument_value => p_validation_end_date
1803 );
1804 --
1805 hr_api.mandatory_arg_error
1806 (p_api_name => l_proc
1807 ,p_argument => 'effective_date'
1808 ,p_argument_value => p_effective_date
1809 );
1810 --
1811 -- Only proceed with validation if :
1812 -- a) The current g_old_rec is current and
1813 -- b) The value for position_id or grade_id has changed
1814 --
1815 l_api_updating := per_asg_shd.api_updating
1816 (p_assignment_id => p_assignment_id
1817 ,p_effective_date => p_effective_date
1818 ,p_object_version_number => p_object_version_number);
1819 --
1820 if (l_api_updating and
1821 ((nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
1822 nvl(p_position_id, hr_api.g_number))
1823 or
1824 (nvl(per_asg_shd.g_old_rec.grade_id, hr_api.g_number) <>
1825 nvl(p_grade_id, hr_api.g_number))))
1826 or
1827 (NOT l_api_updating) then
1828 --
1829 hr_utility.set_location(l_proc, 2);
1830 --
1831 -- Check that position_id and grade_id both contain not null values
1832 --
1833 if p_position_id is not null and p_grade_id is not null then
1834 --
1835 -- Check if the position_id and grade_id exist date effectively
1836 --
1837 -- Bug 3566686 Starts Here
1838 -- Description : The first if condition checks whether there are any
1839 -- grades defined as the valid grades for the selected
1840 -- POSITION, if atleast one such grade exists then only
1841 -- it will check for the validity of the grade selected
1842 -- for the JOB.
1843 --
1844 open csr_valid_pos_val_grd_exists;
1845 fetch csr_valid_pos_val_grd_exists into l_exists1;
1846 if csr_valid_pos_val_grd_exists%found then
1847 close csr_valid_pos_val_grd_exists;
1848 open csr_valid_pos_val_grd;
1849 fetch csr_valid_pos_val_grd into l_exists;
1850 if csr_valid_pos_val_grd%notfound then
1851 l_inv_pos_grade_warning := true;
1852 end if;
1853 close csr_valid_pos_val_grd;
1854 else
1855 close csr_valid_pos_val_grd_exists;
1856 end if;
1857 --
1858 -- Bug 3566686 Ends Here
1859 --
1860 hr_utility.set_location(l_proc, 3);
1861 --
1862 end if;
1863 --
1864 end if;
1865 end if;
1866 --
1867 p_inv_pos_grade_warning := l_inv_pos_grade_warning;
1868 hr_utility.set_location('Leaving'||l_proc, 4);
1869 end chk_position_id_grade_id;
1870 --
1871 ------------------------------------------------------------------------------
1872 --------------------------< chk_position_id_org_id >--------------------------
1873 ------------------------------------------------------------------------------
1874 --
1875 procedure chk_position_id_org_id
1876 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1877 ,p_position_id in per_all_assignments_f.position_id%TYPE
1878 ,p_organization_id in per_all_assignments_f.organization_id%TYPE
1879 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1880 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1881 ,p_effective_date in date
1882 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1883 )
1884 as
1885 l_proc varchar2(72) := g_package||'chk_position_id_org_id';
1886 l_exists varchar2(1);
1887 l_api_updating boolean;
1888 --
1889 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position requirement
1890 --
1891
1892 cursor csr_valid_pos_org_comb is
1893 select null
1894 from hr_positions_f hp
1895 , per_shared_types ps
1896 where hp.position_id = p_position_id
1897 and p_validation_start_date
1898 between hp.effective_start_date
1899 and hp.effective_end_date
1900 and hp.organization_id = p_organization_id
1901 and p_validation_start_date
1902 between hp.date_effective
1903 and nvl(hp.date_end, hr_api.g_eot)
1904 and ps.shared_type_id = hp.availability_status_id
1905 and ps.system_type_cd = 'ACTIVE' ;
1906 --
1907 begin
1908 --
1909 hr_utility.set_location('Entering:'|| l_proc, 1);
1910 --
1911 if hr_multi_message.no_exclusive_error
1912 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1913 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
1914 ) then
1915 --
1916 -- Check mandatory parameters have been set
1917 --
1918 hr_api.mandatory_arg_error
1919 (p_api_name => l_proc
1920 ,p_argument => 'validation_start_date'
1921 ,p_argument_value => p_validation_start_date
1922 );
1923 --
1924 hr_api.mandatory_arg_error
1925 (p_api_name => l_proc
1926 ,p_argument => 'validation_end_date'
1927 ,p_argument_value => p_validation_end_date
1928 );
1929 --
1930 hr_api.mandatory_arg_error
1931 (p_api_name => l_proc
1932 ,p_argument => 'effective_date'
1933 ,p_argument_value => p_effective_date
1934 );
1935 --
1936 -- Only proceed with validation if :
1937 -- a) The current g_old_rec is current and
1938 -- b) The value for position_id or grade_id has changed
1939 --
1940 l_api_updating := per_asg_shd.api_updating
1941 (p_assignment_id => p_assignment_id
1942 ,p_effective_date => p_effective_date
1943 ,p_object_version_number => p_object_version_number);
1944 --
1945
1946 if (l_api_updating and
1947 ((nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
1948 nvl(p_position_id, hr_api.g_number))
1949 or
1950 (nvl(per_asg_shd.g_old_rec.organization_id, hr_api.g_number) <>
1951 nvl(p_organization_id, hr_api.g_number))))
1952 or
1953 (NOT l_api_updating) then
1954 --
1955 hr_utility.set_location(l_proc, 2);
1956 --
1957 -- Check if the position is null
1958 --
1959 If p_position_id is not null then
1960 --
1961 -- Check if assignment position_id and organization_id combination
1962 -- matches the combination in HR_POSITIONS.
1963 --
1964 hr_utility.set_location(l_proc, 3);
1965 open csr_valid_pos_org_comb;
1966 fetch csr_valid_pos_org_comb into l_exists;
1967 if csr_valid_pos_org_comb%notfound then
1968 close csr_valid_pos_org_comb;
1969 hr_utility.set_message(801, 'HR_51055_ASG_INV_POS_ORG_COMB');
1970 hr_utility.raise_error;
1971 end if;
1972 close csr_valid_pos_org_comb;
1973 --
1974 end if;
1975 end if;
1976 end if;
1977 --
1978 hr_utility.set_location('Leaving'||l_proc, 4);
1979 exception
1980 when app_exception.application_exception then
1981 if hr_multi_message.exception_add
1982 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
1983 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID'
1984 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
1985 ) then
1986 hr_utility.set_location(' Leaving:'|| l_proc, 5);
1987 raise;
1988 end if;
1989 hr_utility.set_location(' Leaving:'|| l_proc, 6);
1990 end chk_position_id_org_id;
1991 --
1992 ------------------------------------------------------------------------------
1993 -------------------------< chk_position_id_job_id >---------------------------
1994 ------------------------------------------------------------------------------
1995 --
1996 procedure chk_position_id_job_id
1997 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1998 ,p_position_id in per_all_assignments_f.position_id%TYPE
1999 ,p_job_id in per_all_assignments_f.job_id%TYPE
2000 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
2001 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
2002 ,p_effective_date in date
2003 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2004 )
2005 as
2006 l_proc varchar2(72) := g_package||'chk_position_id_job_id';
2007 l_exists varchar2(1);
2008 l_api_updating boolean;
2009 --
2010 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position requirement
2011 --
2012 cursor csr_valid_pos_job_comb is
2013 select null
2014 from hr_positions_f hp
2015 , per_shared_types ps
2016 where hp.position_id = p_position_id
2017 and p_validation_start_date
2018 between hp.effective_start_date
2019 and hp.effective_end_date
2020 and hp.job_id = p_job_id
2021 and p_validation_start_date
2022 between hp.date_effective
2023 and nvl(hp.date_end,hr_api.g_eot)
2024 and ps.shared_type_id = hp.availability_status_id
2025 and ps.system_type_cd = 'ACTIVE' ;
2026 --
2027 begin
2028 hr_utility.set_location('Entering:'|| l_proc, 10);
2029 --
2030 if hr_multi_message.no_exclusive_error
2031 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2032 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2033 ) then
2034 --
2035 -- Check mandatory parameters have been set
2036 --
2037 hr_api.mandatory_arg_error
2038 (p_api_name => l_proc
2039 ,p_argument => 'validation_start_date'
2040 ,p_argument_value => p_validation_start_date
2041 );
2042 --
2043 hr_api.mandatory_arg_error
2044 (p_api_name => l_proc
2045 ,p_argument => 'validation_end_date'
2046 ,p_argument_value => p_validation_end_date
2047 );
2048 --
2049 hr_api.mandatory_arg_error
2050 (p_api_name => l_proc
2051 ,p_argument => 'effective_date'
2052 ,p_argument_value => p_effective_date
2053 );
2054 hr_utility.set_location(l_proc, 20);
2055 --
2056 -- Only proceed with validation if :
2057 -- a) The current g_old_rec is current and
2058 -- b) The value for position or job has changed
2059 --
2060 l_api_updating := per_asg_shd.api_updating
2061 (p_assignment_id => p_assignment_id
2062 ,p_effective_date => p_effective_date
2063 ,p_object_version_number => p_object_version_number);
2064 hr_utility.set_location(l_proc, 30);
2065 --
2066 if (l_api_updating and
2067 ((nvl(per_asg_shd.g_old_rec.position_id, hr_api.g_number) <>
2068 nvl(p_position_id, hr_api.g_number))
2069 or
2070 (nvl(per_asg_shd.g_old_rec.job_id, hr_api.g_number) <>
2071 nvl(p_job_id, hr_api.g_number))))
2072 or
2073 (NOT l_api_updating)
2074 then
2075 hr_utility.set_location(l_proc, 40);
2076 --
2077 -- Check if the assignment job and position are not null
2078 --
2079 if p_position_id is not null and p_job_id is not null then
2080 --
2081 -- Check if assignment position and job combination matches
2082 -- the combination in HR_POSITIONS
2083 --
2084 open csr_valid_pos_job_comb;
2085 fetch csr_valid_pos_job_comb into l_exists;
2086 if csr_valid_pos_job_comb%notfound then
2087 close csr_valid_pos_job_comb;
2088 hr_utility.set_message(801, 'HR_51056_ASG_INV_POS_JOB_COMB');
2089 hr_multi_message.add
2090 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2091 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2092 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2093 );
2094 else
2095 close csr_valid_pos_job_comb;
2096 end if;
2097 --
2098 elsif p_job_id is null and p_position_id is not null then
2099 --
2100 -- Position is not null but job is null
2101 --
2102 hr_utility.set_message(801, 'HR_51057_ASG_JOB_NULL_VALUE');
2103 hr_multi_message.add
2104 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2105 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.POSITION_ID'
2106 );
2107 --
2108 end if;
2109 --
2110 end if;
2111 end if;
2112 --
2113 hr_utility.set_location('Leaving'||l_proc, 3);
2114 end chk_position_id_job_id;
2115 --
2116 -- ---------------------------------------------------------------------------
2117 -- |-------------------------< chk_primary_flag >----------------------------|
2118 -- ---------------------------------------------------------------------------
2119 --
2120 procedure chk_primary_flag
2121 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2122 ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
2123 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
2124 ,p_person_id in per_all_assignments_f.person_id%TYPE
2125 ,p_period_of_service_id in per_all_assignments_f.period_of_service_id%TYPE
2126 ,p_pop_date_start in DATE
2127 ,p_effective_date in date
2128 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2129 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
2130 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
2131 ) is
2132 --
2133 l_exists varchar2(1);
2134 l_proc varchar2(72) := g_package||'chk_primary_flag';
2135 l_api_updating boolean;
2136 --
2137 cursor csr_asg_exists is
2138 select null
2139 from per_all_assignments_f
2140 where person_id = p_person_id
2141 and period_of_service_id = p_period_of_service_id
2142 and primary_flag = 'Y';
2143 --
2144 cursor csr_cwk_asg_exists is
2145 select null
2146 from per_all_assignments_f
2147 where person_id = p_person_id
2148 and period_of_placement_date_start = p_pop_date_start
2149 and primary_flag = 'Y';
2150 --
2151 -- 120.10 (START)
2152 --
2153 CURSOR csr_get_bg_id IS
2154 SELECT business_group_id
2155 FROM per_all_people_f
2156 WHERE person_id = p_person_id
2157 AND p_effective_date BETWEEN effective_start_date
2158 AND effective_end_date;
2159 --
2160 l_bg_id per_all_people_f.business_group_id%TYPE;
2161 --
2162 CURSOR csr_chk_amends (p_bg_id per_all_people_f.business_group_id%TYPE) IS
2163 SELECT per_system_status
2164 FROM per_ass_status_type_amends
2165 WHERE assignment_status_type_id = per_asg_shd.g_old_rec.assignment_status_type_id
2166 AND business_group_id = csr_chk_amends.p_bg_id;
2167 --
2168 CURSOR csr_valid_ast IS
2169 SELECT per_system_status
2170 FROM per_assignment_status_types
2171 WHERE assignment_status_type_id = per_asg_shd.g_old_rec.assignment_status_type_id;
2172 --
2173 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
2174 --
2175 -- 120.10 (END)
2176 --
2177 --
2178 -- Bug 2782545. Removed the validation that checks the primary
2179 -- assignment continues until the validation_end_date because
2180 -- the primary assignment can be different at the validation start
2181 -- and end. It is instead safe to assume that if there is a primary
2182 -- assignment at the start there will be one at the end.
2183 -- An exception to this is corrupt data and it could be possible
2184 -- to check for that here but it means checking every date-track
2185 -- update for one and only primary assignment: high risk and
2186 -- reduced performance.
2187 --
2188 --
2189 -- 120.10 (START)
2190 --
2191 --cursor csr_ins_non_prim is
2192 cursor csr_ins_non_prim (p_per_system_status VARCHAR2) is
2193 --
2194 -- 120.10 (END)
2195 --
2196 select null
2197 from sys.dual
2198 where exists
2199 (select null
2200 from per_all_assignments_f pas
2201 where pas.effective_start_date <= p_validation_start_date
2202 and pas.person_id = p_person_id
2203 and pas.period_of_service_id = p_period_of_service_id
2204 --
2205 -- 120.10 (START)
2206 --
2207 --and pas.primary_flag = 'Y');
2208 and pas.primary_flag = 'Y')
2209 or (csr_ins_non_prim.p_per_system_status = 'TERM_ASSIGN' and exists
2210 (select null
2211 from per_all_assignments_f pas1
2212 where pas1.effective_start_date <= p_validation_start_date
2213 and pas1.person_id = p_person_id
2214 and pas1.period_of_service_id <> p_period_of_service_id
2215 and pas1.primary_flag = 'Y'
2216 )
2217 );
2218 --
2219 -- 120.10 (END)
2220 --
2221
2222 cursor csr_ins_non_cwk_prim is
2223 select null
2224 from sys.dual
2225 where exists
2226 (select null
2227 from per_all_assignments_f pas
2228 where pas.effective_start_date <= p_validation_start_date
2229 and pas.person_id = p_person_id
2230 and pas.period_of_placement_date_start = p_pop_date_start
2231 and pas.primary_flag = 'Y');
2232
2233 begin
2234 --
2235 hr_utility.set_location('Entering:'|| l_proc, 10);
2236 --
2237 --
2238 -- Check mandatory parameters have been set
2239 --
2240 hr_api.mandatory_arg_error
2241 (p_api_name => l_proc
2242 ,p_argument => 'primary_flag'
2243 ,p_argument_value => p_primary_flag
2244 );
2245 --
2246 hr_api.mandatory_arg_error
2247 (p_api_name => l_proc
2248 ,p_argument => 'person_id'
2249 ,p_argument_value => p_person_id
2250 );
2251 --
2252 hr_api.mandatory_arg_error
2253 (p_api_name => l_proc
2254
2255 ,p_argument => 'validation_start_date'
2256 ,p_argument_value => p_validation_start_date
2257 );
2258 --
2259 hr_api.mandatory_arg_error
2260 (p_api_name => l_proc
2261 ,p_argument => 'validation_end_date'
2262 ,p_argument_value => p_validation_end_date
2263 );
2264 hr_utility.set_location(l_proc, 20);
2265 --
2266 -- Only proceed with validation if :
2267 -- a) The current g_old_rec is current and
2268 -- b) The value for primary flag has changed
2269 --
2270 l_api_updating := per_asg_shd.api_updating
2271 (p_assignment_id => p_assignment_id
2272 ,p_effective_date => p_effective_date
2273 ,p_object_version_number => p_object_version_number);
2274 --
2275 hr_utility.set_location(l_proc, 30);
2276 --
2277 if ((l_api_updating and
2278 nvl(per_asg_shd.g_old_rec.primary_flag, hr_api.g_varchar2) <>
2279 nvl(p_primary_flag, hr_api.g_varchar2)) or
2280 (NOT l_api_updating)) then
2281 --
2282 -- Check if primary flag is either 'Y' or 'N'.
2283 --
2284 If p_primary_flag not in('Y','N') then
2285 --
2286 per_asg_shd.constraint_error
2287 (p_constraint_name => 'PER_ASS_PRIMARY_FLAG_CHK');
2288 --
2289 hr_utility.set_location(l_proc, 30);
2290 --
2291 end if;
2292 --
2293 hr_utility.set_location(l_proc, 40);
2294 --
2295 -- If inserting 'primary' assignment, check that no
2296 -- other primary assignments exist for the person
2297 -- the new assignment is linked to
2298 --
2299 if p_primary_flag = 'Y' then
2300 --
2301 -- Check if the assignment is an applicant or offer assignment
2302 --
2303 if p_assignment_type = 'A'
2304 or p_assignment_type = 'O' then
2305 --
2306 hr_utility.set_message(801, 'HR_51198_ASG_INV_APL_ASG_PF');
2307 --
2308 hr_multi_message.add
2309 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PRIMARY_FLAG');
2310 --
2311 end if;
2312 --
2313 hr_utility.set_location(l_proc, 50);
2314 --
2315 -- Check that the effective end date is the end of time
2316 --
2317 If p_validation_end_date <> hr_api.g_eot then
2318 --
2319 hr_utility.set_message(801, 'HR_51323_ASG_INV_PRIM_ASG_EED');
2320 --
2321 hr_multi_message.add
2322 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2323 --
2324 end if;
2325 --
2326 hr_utility.set_location(l_proc, 60);
2327 --
2328 if hr_multi_message.no_exclusive_error
2329 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2330 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2331 --
2332 hr_utility.set_location(l_proc, 70);
2333 --
2334 if p_assignment_type = 'C' then
2335 --
2336 hr_utility.set_location(l_proc, 80);
2337 --
2338 open csr_cwk_asg_exists;
2339 fetch csr_cwk_asg_exists into l_exists;
2340 --
2341 if csr_cwk_asg_exists%found then
2342 --
2343 close csr_cwk_asg_exists;
2344 --
2345 hr_utility.set_message(801, 'HR_7435_ASG_PRIM_ASS_EXISTS');
2346 --
2347 hr_multi_message.add
2348 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2349 ,p_associated_column2 =>
2350 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_PLACEMENT_DATE_START');
2351 --
2352 else
2353 --
2354 close csr_cwk_asg_exists;
2355 --
2356 end if;
2357 --
2358 hr_utility.set_location(l_proc, 90);
2359 --
2360 elsif p_assignment_type = 'E' then
2361 --
2362 hr_utility.set_location(l_proc, 100);
2363 --
2364 -- Check if a primary assignment already exists
2365 --
2366 open csr_asg_exists;
2367 fetch csr_asg_exists into l_exists;
2368 --
2369 if csr_asg_exists%found then
2370 --
2371 close csr_asg_exists;
2372 --
2373 hr_utility.set_message(801, 'HR_7435_ASG_PRIM_ASS_EXISTS');
2374 --
2375 hr_multi_message.add
2376 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2377 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID');
2378 --
2379 else
2380 --
2381 close csr_asg_exists;
2382 --
2383 end if;
2384 --
2385 hr_utility.set_location(l_proc, 110);
2386 --
2387 end if;
2388 --
2389 end if; -- no exclusive error
2390 --
2391 hr_utility.set_location(l_proc, 120);
2392 --
2393 else
2394 --
2395 hr_utility.set_location(l_proc, 130);
2396 --
2397 -- Check if the assignment is an employee assignment or a
2398 -- non payrolled worker assignment.
2399 --
2400 if p_assignment_type IN ('E','C') then
2401 --
2402 -- Check that a primary employee assignment exists during
2403 -- the entire date range of the non-primary assignment.
2404 --
2405 hr_utility.set_location(l_proc, 140);
2406 --
2407 if hr_multi_message.no_exclusive_error
2408 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2409 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID') then
2410 --
2411 hr_utility.set_location(l_proc, 150);
2412 --
2413 -- Check that the primary cwk assignment exists during
2414 -- the entire date range of the non-primary assignment
2415 --
2416 if p_assignment_type = 'C' then
2417 --
2418 hr_utility.set_location(l_proc, 160);
2419 --
2420 open csr_ins_non_cwk_prim;
2421 fetch csr_ins_non_cwk_prim into l_exists;
2422 --
2423 if csr_ins_non_cwk_prim%notfound then
2424 --
2425 close csr_ins_non_cwk_prim;
2426 --
2427 hr_utility.set_message(801, 'HR_7436_ASG_NO_PRIM_ASS');
2428 --
2429 hr_multi_message.add
2430 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2431 ,p_associated_column2 =>
2432 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_PLACEMENT_DATE_START'
2433 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2434 ,p_associated_column4 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2435 --
2436 else
2437 --
2438 close csr_ins_non_cwk_prim;
2439 --
2440 end if;
2441 --
2442 hr_utility.set_location(l_proc, 170);
2443 --
2444 elsif p_assignment_type = 'E' then
2445 --
2446 hr_utility.set_location(l_proc, 180);
2447 --
2448 --
2449 -- 120.10 (START)
2450 --
2451 --
2452 -- Get the person's BG Id
2453 --
2454 OPEN csr_get_bg_id;
2455 FETCH csr_get_bg_id INTO l_bg_id;
2456 CLOSE csr_get_bg_id;
2457 --
2458 -- Check for user defined assignment status
2459 --
2460 OPEN csr_chk_amends(l_bg_id);
2461 FETCH csr_chk_amends INTO l_per_system_status;
2462 IF csr_chk_amends%NOTFOUND THEN
2463 --
2464 -- Check for delivered assignment status
2465 --
2466 OPEN csr_valid_ast;
2467 FETCH csr_valid_ast INTO l_per_system_status;
2468 CLOSE csr_valid_ast;
2469 END IF;
2470 CLOSE csr_chk_amends;
2471 --open csr_ins_non_prim;
2472 open csr_ins_non_prim(l_per_system_status);
2473 --
2474 -- 120.10 (END)
2475 --
2476 fetch csr_ins_non_prim into l_exists;
2477 --
2478 if csr_ins_non_prim%notfound then
2479 --
2480 close csr_ins_non_prim;
2481 --
2482 hr_utility.set_message(801, 'HR_7436_ASG_NO_PRIM_ASS');
2483 --
2484 hr_multi_message.add
2485 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2486 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERIOD_OF_SERVICE_ID'
2487 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2488 ,p_associated_column4 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE');
2489 --
2490 else
2491 --
2492 close csr_ins_non_prim;
2493 --
2494 end if;
2495 --
2496 hr_utility.set_location(l_proc, 190);
2497 --
2498 end if;
2499 --
2500 end if; -- no exclusive error
2501 --
2502 hr_utility.set_location(l_proc, 200);
2503 --
2504 end if;
2505 --
2506 end if;
2507 --
2508 end if;
2509 --
2510 hr_utility.set_location(' Leaving:'|| l_proc, 999);
2511 --
2512 end chk_primary_flag;
2513 --
2514 -- ---------------------------------------------------------------------------
2515 -- |----------------------< chk_probation_period >---------------------------|
2516 -- ---------------------------------------------------------------------------
2517 procedure chk_probation_period
2518 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2519 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
2520 ,p_effective_date in date
2521 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2522 )
2523 is
2524 --
2525 l_proc varchar2(72) := g_package||'chk_probation_period';
2526 l_api_updating boolean;
2527 --
2528 begin
2529 hr_utility.set_location('Entering:'|| l_proc, 10);
2530 --
2531 hr_api.mandatory_arg_error
2532 (p_api_name => l_proc
2533 ,p_argument => 'effective_date'
2534 ,p_argument_value => p_effective_date
2535 );
2536 hr_utility.set_location(l_proc, 20);
2537 --
2538 -- Only proceed with validation if :
2539 -- a) The current g_old_rec is current and
2540 -- b) The value for probation period has changed
2541 --
2542 l_api_updating := per_asg_shd.api_updating
2543 (p_assignment_id => p_assignment_id
2544 ,p_effective_date => p_effective_date
2545 ,p_object_version_number => p_object_version_number
2546 );
2547 hr_utility.set_location(l_proc, 30);
2548 --
2549 if ((l_api_updating and
2550 nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number)
2551 <> nvl(p_probation_period, hr_api.g_number))
2552 or
2553 (NOT l_api_updating))
2554 then
2555 hr_utility.set_location(l_proc, 40);
2556 --
2557 -- Check that if probation period is set then it's value
2558 -- is in the range 0 to 9999.99
2559 --
2560 -- Bug 3293930. Extended the maximum limit of probation period
2561 -- from 99.99 to 9999.99
2562 if p_probation_period is not null
2563 and p_probation_period not between 0 and 9999.99
2564 then
2565 --
2566 hr_utility.set_message(801, 'HR_51167_ASG_PB_PD_OUT_OF_RAN');
2567 hr_utility.raise_error;
2568 end if;
2569 hr_utility.set_location(l_proc, 50);
2570 --
2571 end if;
2572 hr_utility.set_location(' Leaving:'|| l_proc, 60);
2573 exception
2574 when app_exception.application_exception then
2575 if hr_multi_message.exception_add
2576 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2577 ) then
2578 hr_utility.set_location(' Leaving:'|| l_proc, 70);
2579 raise;
2580 end if;
2581 hr_utility.set_location(' Leaving:'|| l_proc, 80);
2582 --
2583 end chk_probation_period;
2584 --
2585 -- ---------------------------------------------------------------------------
2586 -- |------------------------< chk_probation_unit >---------------------------|
2587 -- ---------------------------------------------------------------------------
2588 --
2589 procedure chk_probation_unit
2590 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2591 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
2592 ,p_effective_date in date
2593 ,p_validation_start_date in date
2594 ,p_validation_end_date in date
2595 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2596 )
2597 is
2598 --
2599 l_proc varchar2(72) := g_package||'chk_probation_unit';
2600 l_api_updating boolean;
2601 --
2602 begin
2603 hr_utility.set_location('Entering:'|| l_proc, 10);
2604 --
2605 -- Check mandatory parameters have been set
2606 --
2607 hr_api.mandatory_arg_error
2608 (p_api_name => l_proc
2609 ,p_argument => 'effective_date'
2610 ,p_argument_value => p_effective_date
2611 );
2612 --
2613 hr_api.mandatory_arg_error
2614 (p_api_name => l_proc
2615 ,p_argument => 'validation_start_date'
2616 ,p_argument_value => p_validation_start_date
2617 );
2618 --
2619 hr_api.mandatory_arg_error
2620 (p_api_name => l_proc
2621 ,p_argument => 'validation_end_date'
2622 ,p_argument_value => p_validation_end_date
2623 );
2624 hr_utility.set_location(l_proc, 20);
2625 --
2626 -- Only proceed with validation if :
2627 -- a) The current g_old_rec is current and
2628 -- b) The value for probation unit has changed
2629 --
2630 l_api_updating := per_asg_shd.api_updating
2631 (p_assignment_id => p_assignment_id
2632 ,p_effective_date => p_effective_date
2633 ,p_object_version_number => p_object_version_number
2634 );
2635 hr_utility.set_location(l_proc, 30);
2636 --
2637 if ((l_api_updating and
2638 nvl(per_asg_shd.g_old_rec.probation_unit, hr_api.g_varchar2)
2639 <> nvl(p_probation_unit, hr_api.g_varchar2))
2640 or
2641 (NOT l_api_updating))
2642 then
2643 hr_utility.set_location(l_proc, 40);
2644 --
2645 -- Check if probation unit is not null
2646 --
2647 if p_probation_unit is not null then
2648 --
2649 -- Check that the probation unit exists in hr_lookups for the
2650 -- lookup type 'QUALIFYING_UNITS' with an enabled flag set to 'Y'
2651 -- and that the effective start date of the assignment is between
2652 -- start date active and end date active in hr_lookups.
2653 --
2654 if hr_api.not_exists_in_dt_hr_lookups
2655 (p_effective_date => p_effective_date
2656 ,p_validation_start_date => p_validation_start_date
2657 ,p_validation_end_date => p_validation_end_date
2658 ,p_lookup_type => 'QUALIFYING_UNITS'
2659 ,p_lookup_code => p_probation_unit
2660 )
2661 then
2662 --
2663 hr_utility.set_message(801, 'HR_51151_ASG_INV_PROB_UNIT');
2664 hr_utility.raise_error;
2665 --
2666 end if;
2667 hr_utility.set_location(l_proc, 50);
2668 end if;
2669 --
2670 end if;
2671 --
2672 hr_utility.set_location(' Leaving:'|| l_proc, 60);
2673 exception
2674 when app_exception.application_exception then
2675 if hr_multi_message.exception_add
2676 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2677 ) then
2678 hr_utility.set_location(' Leaving:'|| l_proc, 70);
2679 raise;
2680 end if;
2681 hr_utility.set_location(' Leaving:'|| l_proc, 80);
2682 end chk_probation_unit;
2683 --
2684 -- ---------------------------------------------------------------------------
2685 -- |-------------------< chk_prob_unit_prob_period >-------------------------|
2686 -- ---------------------------------------------------------------------------
2687 --
2688 procedure chk_prob_unit_prob_period
2689 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2690 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
2691 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
2692 ,p_effective_date in date
2693 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2694 )
2695 is
2696 --
2697 l_proc varchar2(72):= g_package||'chk_prob_unit_prob_period';
2698 l_api_updating boolean;
2699 --
2700 begin
2701 hr_utility.set_location('Entering:'|| l_proc, 1);
2702 --
2703 -- Check mandatory parameters have been set
2704 --
2705 if hr_multi_message.no_exclusive_error
2706 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2707 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2708 ) then
2709 hr_api.mandatory_arg_error
2710 (p_api_name => l_proc
2711 ,p_argument => 'effective_date'
2712 ,p_argument_value => p_effective_date
2713 );
2714 --
2715 -- Only proceed with validation if :
2716 -- a) The current g_old_rec is current and
2717 -- b) The value for probation unit or probation period changed.
2718 --
2719 l_api_updating := per_asg_shd.api_updating
2720 (p_assignment_id => p_assignment_id
2721 ,p_effective_date => p_effective_date
2722 ,p_object_version_number => p_object_version_number);
2723 --
2724 if ((l_api_updating
2725 and
2726 nvl(per_asg_shd.g_old_rec.probation_unit, hr_api.g_varchar2)
2727 <> nvl(p_probation_unit, hr_api.g_varchar2)
2728 or
2729 nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number)
2730 <> nvl(p_probation_period, hr_api.g_number))
2731 or
2732 (NOT l_api_updating)) then
2733 --
2734 hr_utility.set_location(l_proc, 2);
2735 --
2736 -- Check if probation unit or probation period is not null.
2737 --
2738 if p_probation_unit is not null or p_probation_period is not null then
2739 --
2740 -- Check if probation unit or probation period are null.
2741 --
2742 if p_probation_unit is null or p_probation_period is null then
2743 --
2744 hr_utility.set_message(801, 'HR_51166_ASG_INV_PU_PP_COMB');
2745 hr_utility.raise_error;
2746 --
2747 end if;
2748 hr_utility.set_location(l_proc, 3);
2749 --
2750 end if;
2751 --
2752 end if;
2753 end if;
2754 --
2755 hr_utility.set_location(' Leaving:'|| l_proc, 4);
2756 exception
2757 when app_exception.application_exception then
2758 if hr_multi_message.exception_add
2759 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_UNIT'
2760 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PROBATION_PERIOD'
2761 ) then
2762 hr_utility.set_location(' Leaving:'|| l_proc, 5);
2763 raise;
2764 end if;
2765 hr_utility.set_location(' Leaving:'|| l_proc, 6);
2766 --
2767 end chk_prob_unit_prob_period;
2768 --
2769 -- ---------------------------------------------------------------------------
2770 -- |------------------------< chk_recruiter_id >-----------------------------|
2771 -- ---------------------------------------------------------------------------
2772 --
2773 procedure chk_recruiter_id
2774 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2775 ,p_person_id in per_all_assignments_f.person_id%TYPE
2776 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
2777 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
2778 ,p_recruiter_id in per_all_assignments_f.recruiter_id%TYPE
2779 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
2780 ,p_effective_date in date
2781 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2782 ,p_validation_start_date in date
2783 ,p_validation_end_date in date
2784 )
2785 is
2786 --
2787 l_proc varchar2(72) := g_package||'chk_recruiter_id';
2788 l_api_updating boolean;
2789 l_vac_recruiter_id per_all_assignments_f.recruiter_id%TYPE;
2790 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
2791 l_current_employee_flag per_people_f.current_employee_flag%TYPE;
2792 l_current_npw_flag per_people_f.current_npw_flag%TYPE;
2793 --
2794 cursor csr_val_recruiter is
2795 select business_group_id, current_employee_flag, current_npw_flag
2796 --from per_people_f bug 5078945
2797 from per_all_people_f
2798 where person_id = p_recruiter_id
2799 and p_validation_start_date
2800 between effective_start_date
2801 and nvl(effective_end_date, hr_api.g_eot);
2802 --
2803 begin
2804 hr_utility.set_location('Entering:'|| l_proc, 10);
2805 --
2806 --
2807 -- Check mandatory parameters have been set
2808 --
2809 hr_api.mandatory_arg_error
2810 (p_api_name => l_proc
2811 ,p_argument => 'effective_date'
2812 ,p_argument_value => p_effective_date
2813 );
2814 hr_utility.set_location(l_proc, 20);
2815 --
2816 hr_api.mandatory_arg_error
2817 (p_api_name => l_proc
2818 ,p_argument => 'validation_start_date'
2819 ,p_argument_value => p_validation_start_date
2820 );
2821 hr_utility.set_location(l_proc, 30);
2822 --
2823 hr_api.mandatory_arg_error
2824 (p_api_name => l_proc
2825 ,p_argument => 'validation_end_date'
2826 ,p_argument_value => p_validation_end_date
2827 );
2828 hr_utility.set_location(l_proc, 40);
2829 --
2830 -- Only proceed with validation if :
2831 -- a) The current g_old_rec is current and
2832 -- b) The value for recruiter has changed
2833 --
2834 l_api_updating := per_asg_shd.api_updating
2835 (p_assignment_id => p_assignment_id
2836 ,p_effective_date => p_effective_date
2837 ,p_object_version_number => p_object_version_number);
2838 hr_utility.set_location(l_proc, 50);
2839 --
2840 if ((l_api_updating and
2841 nvl(per_asg_shd.g_old_rec.recruiter_id, hr_api.g_number) <>
2842 nvl(p_recruiter_id, hr_api.g_number)) or
2843 (NOT l_api_updating)) then
2844 hr_utility.set_location(l_proc, 60);
2845 --
2846 -- Check if recruiter is not null
2847 --
2848 if p_recruiter_id is not null then
2849 --
2850 -- Check that the assignment is not an applicant or an offer assignment.
2851 --
2852 if p_assignment_type in ('E','C','B')
2853 then
2854 --
2855 hr_utility.set_message(801, 'HR_51216_ASG_INV_ASG_TYP_REC');
2856 hr_multi_message.add
2857 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2858 );
2859 --
2860 end if;
2861 hr_utility.set_location(l_proc, 70);
2862 --
2863 -- Check that the recruiter is'nt the same person as the assignment
2864 -- person.
2865 --
2866 If p_recruiter_id = p_person_id then
2867 --
2868 hr_utility.set_message(801, 'HR_51289_ASG_APL_EQUAL_RECRUIT');
2869 hr_multi_message.add
2870 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2871 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
2872 );
2873 --
2874 end if;
2875 hr_utility.set_location(l_proc, 80);
2876 --
2877 -- Check if the recruiter exists between the effective start date
2878 -- and effective end date of the assignment.
2879 --
2880 open csr_val_recruiter;
2881 fetch csr_val_recruiter
2882 into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
2883 if csr_val_recruiter%notfound then
2884 close csr_val_recruiter;
2885 hr_utility.set_message(801, 'HR_51280_ASG_INV_RECRUIT_ID');
2886 hr_multi_message.add
2887 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
2888 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2889 );
2890 --
2891 else
2892 close csr_val_recruiter;
2893 end if;
2894 --
2895 hr_utility.set_location(l_proc, 90);
2896 --
2897 -- Check that the recruiter is an employee.
2898 --
2899 -- Bug 3190625
2900 -- Condition to check profile value also added
2901 if not ( (nvl(l_current_employee_flag,hr_api.g_varchar2) = 'Y' ) or
2902 ( nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y' and
2903 nvl(l_current_npw_flag, 'N') = 'Y') ) then
2904 hr_utility.set_message(801, 'HR_51290_ASG_RECRUIT_NOT_EMP');
2905 hr_multi_message.add
2906 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID');
2907 end if;
2908 hr_utility.set_location(l_proc, 100);
2909 --
2910 -- Check that the recruiter is in the same business group
2911 -- as the business group of the applicant assignment.
2912 --
2913 If (p_business_group_id <> l_business_group_id AND
2914 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') = 'N')
2915 then
2916 --
2917 hr_utility.set_message(801, 'HR_51284_ASG_INV_RECRUIT_BG');
2918 hr_multi_message.add
2919 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITER_ID'
2920 );
2921 --
2922 end if;
2923 hr_utility.set_location(l_proc, 110);
2924 --
2925 end if;
2926 --
2927 end if;
2928 hr_utility.set_location(' Leaving:'||l_proc, 140);
2929 --
2930 end chk_recruiter_id;
2931 --
2932 -- ---------------------------------------------------------------------------
2933 -- |--------------------< chk_recruitment_activity_id >----------------------|
2934 -- ---------------------------------------------------------------------------
2935 --
2936 procedure chk_recruitment_activity_id
2937 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2938 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
2939 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
2940 ,p_recruitment_activity_id in per_all_assignments_f.recruitment_activity_id%TYPE
2941 ,p_effective_date in date
2942 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2943 ,p_validation_start_date in date
2944 ,p_validation_end_date in date
2945 )
2946 is
2947 --
2948 l_proc varchar2(72) := g_package||'chk_recruitment_activity_id';
2949 l_api_updating boolean;
2950 l_exists varchar2(1);
2951 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
2952 --
2953 cursor csr_val_rec_act_id is
2954 select business_group_id
2955 from per_recruitment_activities
2956 where recruitment_activity_id = p_recruitment_activity_id
2957 and p_validation_start_date
2958 between date_start
2959 and nvl(date_end, hr_api.g_eot);
2960 --
2961 begin
2962 hr_utility.set_location('Entering:'|| l_proc, 10);
2963 --
2964 -- Check mandatory parameters have been set
2965 --
2966 hr_api.mandatory_arg_error
2967 (p_api_name => l_proc
2968 ,p_argument => 'effective_date'
2969 ,p_argument_value => p_effective_date
2970 );
2971 --
2972 hr_api.mandatory_arg_error
2973 (p_api_name => l_proc
2974 ,p_argument => 'validation_start_date'
2975 ,p_argument_value => p_validation_start_date
2976 );
2977 --
2978 hr_api.mandatory_arg_error
2979 (p_api_name => l_proc
2980 ,p_argument => 'validation_end_date'
2981 ,p_argument_value => p_validation_end_date
2982 );
2983 hr_utility.set_location(l_proc, 20);
2984 --
2985 -- Only proceed with validation if :
2986 -- a) The current g_old_rec is current and
2987 -- b) The value for recruitment activity has changed
2988 --
2989 l_api_updating := per_asg_shd.api_updating
2990 (p_assignment_id => p_assignment_id
2991 ,p_effective_date => p_effective_date
2992 ,p_object_version_number => p_object_version_number
2993 );
2994 hr_utility.set_location(l_proc, 30);
2995 --
2996 if ((l_api_updating and
2997 nvl(per_asg_shd.g_old_rec.recruitment_activity_id, hr_api.g_number) <>
2998 nvl(p_recruitment_activity_id, hr_api.g_number)) or
2999 (NOT l_api_updating))
3000 then
3001 hr_utility.set_location(l_proc, 40);
3002 --
3003 -- Check if recruitment activity is not null
3004 --
3005 if p_recruitment_activity_id is not null then
3006 --
3007 -- Check that the assignment is not an applicant or offer assignment.
3008 --
3009 if p_assignment_type in ('E','C','B') then
3010 --
3011 hr_utility.set_message(801, 'HR_51223_ASG_INV_ASG_TYP_RCAT');
3012 hr_multi_message.add
3013 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3014 );
3015 --
3016 end if;
3017 hr_utility.set_location(l_proc, 50);
3018 --
3019 -- Check if the recruitment activity exists where the effective
3020 -- start date of the assignment is between the date start and
3021 -- date end of the recruitment activity.
3022 --
3023 open csr_val_rec_act_id;
3024 fetch csr_val_rec_act_id into l_business_group_id;
3025 if csr_val_rec_act_id%notfound then
3026 close csr_val_rec_act_id;
3027 --
3028 -- Do not throw an error for Offer Assignment.
3029 --
3030 if p_assignment_type <> 'O'
3031 then
3032 hr_utility.set_message(801, 'HR_51306_ASG_INV_REC_ACT');
3033 hr_multi_message.add
3034 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3035 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3036 );
3037 --
3038 end if;
3039 --
3040 else
3041 close csr_val_rec_act_id;
3042 end if;
3043 --
3044 hr_utility.set_location(l_proc, 60);
3045 --
3046 -- Check that the recruitment activity is in the same business group
3047 -- as the business group of the assignment.
3048 --
3049 If p_business_group_id <> l_business_group_id then
3050 --
3051 hr_utility.set_message(801, 'HR_51307_ASG_INV_REC_ACT_BG');
3052 hr_multi_message.add
3053 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
3054 );
3055 --
3056 end if;
3057 hr_utility.set_location(l_proc, 70);
3058 --
3059 end if;
3060 --
3061 end if;
3062 --
3063 hr_utility.set_location(' Leaving:'|| l_proc, 100);
3064 end chk_recruitment_activity_id;
3065 --
3066 -- ---------------------------------------------------------------------------
3067 -- |-------------------------< chk_ref_int_del >-----------------------------|
3068 -- ---------------------------------------------------------------------------
3069 --
3070 -- Description:
3071 -- Validates that an assignment cannot be purged if foreign key
3072 -- references exist to any of the following tables :
3073 --
3074 -- - PER_EVENTS
3075 -- - PER_LETTER_REQUEST_LINES
3076 -- - PAY_COST_ALLOCATIONS_F
3077 -- - PAY_PAYROLL_ACTIONS
3078 -- - PAY_PERSONAL_PAYMENT_METHODS_F
3079 -- - PAY_ASSIGNMENT_ACTIONS
3080 -- - PER_COBRA_COV_ENROLLMENTS
3081 -- - PER_COBRA_COVERAGE_BENEFITS_F
3082 -- - PER_ASSIGNMENTS_EXTRA_INFO
3083 -- - HR_ASSIGNMENT_SET_AMENDMENTS
3084 -- - PER_SECONDARY_ASS_STATUSES
3085 --
3086 -- Pre-conditions:
3087 -- None
3088 --
3089 -- In Arguments:
3090 -- p_assignment_id
3091 -- p_validation_start_date
3092 -- p_validation_end_date
3093 -- p_datetrack_mode
3094 --
3095 -- Post Success:
3096 -- If no child rows exist in the table listed above then processing
3097 -- continues.
3098 --
3099 -- Post Failure:
3100 -- If child rows exist in any of the tables listed above, an application
3101 -- error is raised and processing is terminated.
3102 --
3103 procedure chk_ref_int_del
3104 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3105 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
3106 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
3107 ,p_datetrack_mode in varchar2
3108 )
3109 is
3110 --
3111 l_exists varchar2(1);
3112 l_proc varchar2(72) := g_package||'chk_ref_int_del';
3113 --
3114 cursor csr_per_events is
3115 select null
3116 from sys.dual
3117 where exists(select null
3118 from per_events pe
3119 where pe.assignment_id = p_assignment_id
3120 and (p_datetrack_mode = 'ZAP'
3121 or (p_datetrack_mode = 'DELETE'
3122 and date_start > p_validation_start_date))
3123 and not exists
3124 (select null
3125 from irc_interview_details iid
3126 where pe.event_id = iid.event_id)
3127 );
3128 --
3129 -- Start of 3096114
3130 /*cursor csr_per_lett_req_lines is
3131 select null
3132 from sys.dual
3133 where exists(select null
3134 from per_letter_request_lines
3135 where assignment_id = p_assignment_id
3136 and (p_datetrack_mode = 'ZAP'
3137 or (p_datetrack_mode = 'DELETE'
3138 and date_from > p_validation_start_date)));*/
3139 -- End of 3096114
3140 --
3141 cursor csr_pay_cost_allocations_f is
3142 select null
3143 from sys.dual
3144 where exists(select null
3145 from pay_cost_allocations_f
3146 where assignment_id = p_assignment_id
3147 and (p_datetrack_mode = 'ZAP'
3148 or (p_datetrack_mode = 'DELETE'
3149 and effective_start_date > p_validation_start_date)));
3150 --
3151 cursor csr_pay_pers_payment_methods is
3152 select null
3153 from sys.dual
3154 where exists(select null
3155 from pay_personal_payment_methods_f
3156 where assignment_id = p_assignment_id
3157 and (p_datetrack_mode = 'ZAP'
3158 or (p_datetrack_mode = 'DELETE'
3159 and effective_start_date > p_validation_start_date)));
3160 --
3161 cursor csr_pay_assignment_actions is
3162 select null
3163 from sys.dual
3164 where exists(select null
3165 from pay_assignment_actions aa
3166 , pay_payroll_actions pa
3167 where aa.assignment_id = p_assignment_id
3168 and pa.payroll_action_id = aa.payroll_action_id
3169 and (p_datetrack_mode = 'ZAP'
3170 or (p_datetrack_mode = 'DELETE'
3171 and pa.effective_date > p_validation_start_date))
3172 and pa.action_type not in ('X','BEE')); -- Fix for bug# 2711532
3173 --
3174 cursor csr_per_secondary_ass_stat is
3175 select null
3176 from sys.dual
3177 where exists(select null
3178 from per_secondary_ass_statuses
3179 where assignment_id = p_assignment_id
3180 and (p_datetrack_mode = 'ZAP'
3181 or (p_datetrack_mode = 'DELETE'
3182 and start_date > p_validation_start_date)));
3183 --
3184 cursor csr_per_cobra_cov_enrol is
3185 select null
3186 from sys.dual
3187 where exists(select null
3188 from per_cobra_cov_enrollments
3189 where assignment_id = p_assignment_id
3190 and (p_datetrack_mode = 'ZAP'
3191 or ((p_datetrack_mode = 'DELETE'
3192 and coverage_start_date is null)
3193 or (coverage_start_date > p_validation_start_date))));
3194 --
3195 cursor csr_per_cobra_cov_bens is
3196 select null
3197 from sys.dual
3198 where exists(select null
3199 from per_cobra_coverage_benefits_f b
3200 , per_cobra_cov_enrollments e
3201 where e.assignment_id = p_assignment_id
3202 and e.cobra_coverage_enrollment_id =
3203 b.cobra_coverage_enrollment_id
3204 and (p_datetrack_mode = 'ZAP'
3205 or (p_datetrack_mode = 'DELETE'
3206 and b.effective_start_date > p_validation_start_date)));
3207 --
3208 cursor csr_per_ass_extra_info is
3209 select null
3210 from sys.dual
3211 where exists(select null
3212 from per_assignment_extra_info
3213 where assignment_id = p_assignment_id);
3214 --
3215 cursor csr_hr_ass_set_amend is
3216 select null
3217 from sys.dual
3218 where exists(select null
3219 from hr_assignment_set_amendments
3220 where assignment_id = p_assignment_id);
3221 --
3222 begin
3223 hr_utility.set_location('Entering:'|| l_proc, 1);
3224 --
3225 -- Check that no child records exist for the
3226 -- assignment on per_events when the assignment is
3227 -- deleted
3228 --
3229 open csr_per_events;
3230 fetch csr_per_events into l_exists;
3231 if csr_per_events%found then
3232 close csr_per_events;
3233 hr_utility.set_message(801,'HR_7400_ASG_NO_DEL_ASS_EVENTS');
3234 hr_utility.raise_error;
3235 end if;
3236 close csr_per_events;
3237 --
3238 hr_utility.set_location(l_proc, 2);
3239 --
3240 -- Check that no child records exist for the
3241 -- assignment on per_letter_request_lines when
3242 -- the assignment is deleted
3243 --
3244 -- Start of 3096114
3245 /*open csr_per_lett_req_lines;
3246 fetch csr_per_lett_req_lines into l_exists;
3247 if csr_per_lett_req_lines%found then
3248 close csr_per_lett_req_lines;
3249 hr_utility.set_message(801,'HR_7401_ASG_NO_DEL_ASS_LET_REQ');
3250 hr_utility.raise_error;
3251 end if;
3252 close csr_per_lett_req_lines;*/
3253 -- End of 3096114
3254 --
3255 hr_utility.set_location(l_proc, 3);
3256 --
3257 -- Check that no child records exist for the
3258 -- assignment on pay_cost_allocations_f when
3259 -- the assignment is deleted
3260 --
3261 open csr_pay_cost_allocations_f;
3262 fetch csr_pay_cost_allocations_f into l_exists;
3263 if csr_pay_cost_allocations_f%found then
3264 close csr_pay_cost_allocations_f;
3265 hr_utility.set_message(801,'HR_7402_ASG_NO_DEL_COST_ALLOCS');
3266 hr_utility.raise_error;
3267 end if;
3268 close csr_pay_cost_allocations_f;
3269 --
3270 hr_utility.set_location(l_proc, 4);
3271 --
3272 -- Check that no child records exist for the
3273 -- assignment on pay_personal_payment_methods when
3274 -- the assignment is deleted
3275 --
3276 open csr_pay_pers_payment_methods;
3277 fetch csr_pay_pers_payment_methods into l_exists;
3278 if csr_pay_pers_payment_methods%found then
3279 close csr_pay_pers_payment_methods;
3280 hr_utility.set_message(801,'HR_7404_ASG_NO_DEL_PER_PAY_MET');
3281 hr_utility.raise_error;
3282 end if;
3283 close csr_pay_pers_payment_methods;
3284 --
3285 hr_utility.set_location(l_proc, 5);
3286 --
3287 -- Check that no child records exist for the
3288 -- assignment on pay_payroll_actions when
3289 -- the assignment is deleted
3290 --
3291 open csr_pay_assignment_actions;
3292 fetch csr_pay_assignment_actions into l_exists;
3293 if csr_pay_assignment_actions%found then
3294 close csr_pay_assignment_actions;
3295 hr_utility.set_message(801,'HR_7403_ASG_NO_DEL_PAYROLL_ACT');
3296 hr_utility.raise_error;
3297 end if;
3298 close csr_pay_assignment_actions;
3299 --
3300 hr_utility.set_location(l_proc, 6);
3301 --
3302 -- Check that no child records exist for the
3303 -- assignment on per_secondary_ass_statuses when
3304 -- the assignment is deleted
3305 --
3306 open csr_per_secondary_ass_stat;
3307 fetch csr_per_secondary_ass_stat into l_exists;
3308 if csr_per_secondary_ass_stat%found then
3309 close csr_per_secondary_ass_stat;
3310 hr_utility.set_message(801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
3311 hr_utility.raise_error;
3312 end if;
3313 close csr_per_secondary_ass_stat;
3314 --
3315 hr_utility.set_location(l_proc, 7);
3316 --
3317 -- Check that no child records exist for the
3318 -- assignment on per_cobra_cov_enrollments
3319 -- when the assignment is deleted
3320 --
3321 open csr_per_cobra_cov_enrol;
3322 fetch csr_per_cobra_cov_enrol into l_exists;
3323 if csr_per_cobra_cov_enrol%found then
3324 close csr_per_cobra_cov_enrol;
3325 hr_utility.set_message(801,'HR_7405_ASG_NO_DEL_COB_COV_ENR');
3326 hr_utility.raise_error;
3327 end if;
3328 close csr_per_cobra_cov_enrol;
3329 --
3330 hr_utility.set_location(l_proc, 8);
3331 --
3332 -- Check that no child records exist for the
3333 -- assignment on per_cobra_coverage_benefits_f
3334 -- when the assignment is deleted
3335 --
3336 open csr_per_cobra_cov_bens;
3337 fetch csr_per_cobra_cov_bens into l_exists;
3338 if csr_per_cobra_cov_bens%found then
3339 close csr_per_cobra_cov_bens;
3340 hr_utility.set_message(801,'HR_7406_ASG_NO_DEL_COB_COV_BEN');
3341 hr_utility.raise_error;
3342 end if;
3343 close csr_per_cobra_cov_bens;
3344 --
3345 hr_utility.set_location(l_proc, 9);
3346 --
3347 -- Check that no child records exist for the
3348 -- assignment on per_assignment_extra_info when
3349 -- the assignment is deleted
3350 --
3351 -- Only allow processing in 'ZAP' mode
3352 --
3353 if p_datetrack_mode = 'ZAP' then
3354 open csr_per_ass_extra_info;
3355 fetch csr_per_ass_extra_info into l_exists;
3356 if csr_per_ass_extra_info%found then
3357 close csr_per_ass_extra_info;
3358 hr_utility.set_message(801,'HR_7409_ASG_NO_DEL_EXTR_INFO');
3359 hr_utility.raise_error;
3360 end if;
3361 close csr_per_ass_extra_info;
3362 end if;
3363 --
3364 hr_utility.set_location(l_proc, 10);
3365 --
3366 -- Check that no child records exist for the
3367 -- assignment on hr_assignment_set_amendments
3368 -- when the assignment is deleted
3369 --
3370 -- Only allow processing in 'ZAP' mode
3371 --
3372 if p_datetrack_mode = 'ZAP' then
3373 open csr_hr_ass_set_amend;
3374 fetch csr_hr_ass_set_amend into l_exists;
3375 if csr_hr_ass_set_amend%found then
3376 close csr_hr_ass_set_amend;
3377 hr_utility.set_message(801,'HR_7410_ASG_NO_DEL_ASS_SET_AMD');
3378 hr_utility.raise_error;
3379 end if;
3380 close csr_hr_ass_set_amend;
3381 end if;
3382 --
3383 hr_utility.set_location(' Leaving:'|| l_proc, 11);
3384 end chk_ref_int_del;
3385 --
3386 -- ---------------------------------------------------------------------------
3387 -- |---------------------< chk_sal_review_period_freq >----------------------|
3388 -- ---------------------------------------------------------------------------
3389 --
3390 procedure chk_sal_review_period_freq
3391 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3392 ,p_sal_review_period_frequency in
3393 per_all_assignments_f.sal_review_period_frequency%TYPE
3394 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3395 ,p_effective_date in date
3396 ,p_validation_start_date in date
3397 ,p_validation_end_date in date
3398 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3399 )
3400 is
3401 --
3402 l_proc varchar2(72) := g_package||'chk_sal_review_period_freq';
3403 l_api_updating boolean;
3404 --
3405 begin
3406 hr_utility.set_location('Entering:'|| l_proc, 10);
3407 --
3408 -- Check mandatory parameters have been set
3409 --
3410 hr_api.mandatory_arg_error
3411 (p_api_name => l_proc
3412 ,p_argument => 'effective_date'
3413 ,p_argument_value => p_effective_date
3414 );
3415 --
3416 hr_api.mandatory_arg_error
3417 (p_api_name => l_proc
3418 ,p_argument => 'validation_start_date'
3419 ,p_argument_value => p_validation_start_date
3420 );
3421 --
3422 hr_api.mandatory_arg_error
3423 (p_api_name => l_proc
3424 ,p_argument => 'validation_end_date'
3425 ,p_argument_value => p_validation_end_date
3426 );
3427 hr_utility.set_location(l_proc, 20);
3428 --
3429 -- Only proceed with validation if :
3430 -- a) The current g_old_rec is current and
3431 -- b) The value for salary review period frequency has changed
3432 --
3433 l_api_updating := per_asg_shd.api_updating
3434 (p_assignment_id => p_assignment_id
3435 ,p_effective_date => p_effective_date
3436 ,p_object_version_number => p_object_version_number
3437 );
3438 hr_utility.set_location(l_proc, 30);
3439 --
3440 if ((l_api_updating and
3441 nvl(per_asg_shd.g_old_rec.sal_review_period_frequency,
3442 hr_api.g_varchar2) <> nvl(p_sal_review_period_frequency,
3443 hr_api.g_varchar2))
3444 or
3445 (NOT l_api_updating))
3446 then
3447 hr_utility.set_location(l_proc, 40);
3448 --
3449 -- Check if sal review period frequency is not null
3450 --
3451 if p_sal_review_period_frequency is not null then
3452 --
3453 -- Check that the assignment is an employee or applicant
3454 -- or benefit or offer assignment.
3455 --
3456 if p_assignment_type not in ('E','A','B','O') then
3457 --
3458 hr_utility.set_message(801, 'HR_51181_ASG_INV_ASG_TYP_SRPF');
3459 hr_multi_message.add
3460 (p_associated_column1 =>
3461 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3462 );
3463 --
3464 end if;
3465 hr_utility.set_location(l_proc, 50);
3466 --
3467 -- Check that the salary review period frequency exists in
3468 -- hr_lookups for the lookup type 'FREQUENCY' with an enabled
3469 -- flag set to 'Y' and that the effective start date of the
3470 -- assignment is between start date active and end date active
3471 -- in hr_lookups.
3472 --
3473 if hr_api.not_exists_in_dt_hr_lookups
3474 (p_effective_date => p_effective_date
3475 ,p_validation_start_date => p_validation_start_date
3476 ,p_validation_end_date => p_validation_end_date
3477 ,p_lookup_type => 'FREQUENCY'
3478 ,p_lookup_code => p_sal_review_period_frequency
3479 )
3480 then
3481 --
3482 hr_utility.set_message(801, 'HR_51164_ASG_INV_SRP_FREQ');
3483 hr_multi_message.add
3484 (p_associated_column1 =>
3485 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3486 );
3487 --
3488 end if;
3489 hr_utility.set_location(l_proc, 60);
3490 --
3491 end if;
3492 hr_utility.set_location(l_proc, 70);
3493 --
3494 end if;
3495 hr_utility.set_location(' Leaving:'|| l_proc, 80);
3496 end chk_sal_review_period_freq;
3497 --
3498 -- ---------------------------------------------------------------------------
3499 -- |-----------------------< chk_sal_review_period >------------------------|
3500 -- ---------------------------------------------------------------------------
3501 --
3502 procedure chk_sal_review_period
3503 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3504 ,p_sal_review_period in per_all_assignments_f.sal_review_period%TYPE
3505 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3506 ,p_effective_date in date
3507 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3508 )
3509 is
3510 --
3511 l_proc varchar2(72) := g_package||'chk_sal_review_period';
3512 l_api_updating boolean;
3513 --
3514 begin
3515 hr_utility.set_location('Entering:'|| l_proc, 1);
3516 --
3517 -- Check mandatory parameters have been set
3518 --
3519 hr_api.mandatory_arg_error
3520 (p_api_name => l_proc
3521 ,p_argument => 'effective_date'
3522 ,p_argument_value => p_effective_date
3523 );
3524 --
3525 -- Only proceed with validation if :
3526 -- a) The current g_old_rec is current and
3527 -- b) The value for perf review period has changed
3528 --
3529 l_api_updating := per_asg_shd.api_updating
3530 (p_assignment_id => p_assignment_id
3531 ,p_effective_date => p_effective_date
3532 ,p_object_version_number => p_object_version_number);
3533 --
3534 hr_utility.set_location(l_proc, 2);
3535 --
3536 if ((l_api_updating and
3537 nvl(per_asg_shd.g_old_rec.sal_review_period,
3538 hr_api.g_number) <> nvl(p_sal_review_period,
3539 hr_api.g_number)) or (NOT l_api_updating)) then
3540 --
3541 hr_utility.set_location(l_proc, 3);
3542 --
3543 -- Check if sal review period is not null
3544 --
3545 if p_sal_review_period is not null then
3546 --
3547 -- Check that the assignment is an employee or applicant
3548 -- or benefit or offer assignment.
3549 --
3550 if p_assignment_type not in ('E','A','B','O') then
3551
3552 --
3553 hr_utility.set_message(801, 'HR_51180_ASG_INV_ASG_TYP_SRP');
3554 hr_utility.raise_error;
3555 end if;
3556 hr_utility.set_location(l_proc, 4);
3557 --
3558 end if;
3559 --
3560 end if;
3561 --
3562 hr_utility.set_location(' Leaving:'|| l_proc, 5);
3563 exception
3564 when app_exception.application_exception then
3565 if hr_multi_message.exception_add
3566 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3567 ) then
3568 hr_utility.set_location(' Leaving:'|| l_proc, 6);
3569 raise;
3570 end if;
3571 hr_utility.set_location(' Leaving:'|| l_proc, 7);
3572 end chk_sal_review_period;
3573 --
3574 -- ---------------------------------------------------------------------------
3575 -- |---------------------< chk_sal_rp_freq_sal_rp >--------------------------|
3576 -- ---------------------------------------------------------------------------
3577 --
3578 procedure chk_sal_rp_freq_sal_rp
3579 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3580 ,p_sal_review_period_frequency in per_all_assignments_f.sal_review_period_frequency%TYPE
3581 ,p_sal_review_period in per_all_assignments_f.sal_review_period%TYPE
3582 ,p_effective_date in date
3583 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3584 )
3585 is
3586 --
3587 l_proc varchar2(72):= g_package||'chk_sal_rp_freq_sal_rp';
3588 l_api_updating boolean;
3589 --
3590 begin
3591 hr_utility.set_location('Entering:'|| l_proc, 1);
3592 --
3593 if hr_multi_message.no_exclusive_error
3594 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3595 ,p_check_column2 =>
3596 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3597 ) then
3598 --
3599 -- Check mandatory parameters have been set
3600 --
3601 hr_api.mandatory_arg_error
3602 (p_api_name => l_proc
3603 ,p_argument => 'effective_date'
3604 ,p_argument_value => p_effective_date
3605 );
3606 --
3607 -- Only proceed with validation if :
3608 -- a) The current g_old_rec is current and
3609 -- b) The value for sal review period frequency or sal review period has
3610 -- changed.
3611 --
3612 l_api_updating := per_asg_shd.api_updating
3613 (p_assignment_id => p_assignment_id
3614 ,p_effective_date => p_effective_date
3615 ,p_object_version_number => p_object_version_number);
3616 --
3617 if ((l_api_updating and nvl(per_asg_shd.g_old_rec.sal_review_period_frequency,
3618 hr_api.g_varchar2) <> nvl(p_sal_review_period_frequency, hr_api.g_varchar2)
3619 or
3620 nvl(per_asg_shd.g_old_rec.sal_review_period,
3621 hr_api.g_number) <> nvl(p_sal_review_period, hr_api.g_number))
3622 or
3623 (NOT l_api_updating)) then
3624 --
3625 hr_utility.set_location(l_proc, 2);
3626 --
3627 -- Check if sal review period frequency or sal review period is not null.
3628 --
3629 if p_sal_review_period_frequency is not null
3630 or p_sal_review_period is not null then
3631 hr_utility.set_location(l_proc, 3);
3632 --
3633 -- Check if sal review period frequency or sal review period are null.
3634 --
3635 if p_sal_review_period_frequency is null
3636 or p_sal_review_period is null then
3637 --
3638 hr_utility.set_message(801, 'HR_51165_ASG_INV_SRPF_SRP_COMB');
3639 hr_utility.raise_error;
3640 --
3641 end if;
3642 --
3643 end if;
3644 --
3645 end if;
3646 end if;
3647 --
3648 hr_utility.set_location(' Leaving:'|| l_proc, 4);
3649 exception
3650 when app_exception.application_exception then
3651 if hr_multi_message.exception_add
3652 (p_associated_column1 =>
3653 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD'
3654 ,p_associated_column2 =>
3655 'PER_ALL_ASSIGNMENTS_F.SAL_REVIEW_PERIOD_FREQUENCY'
3656 ) then
3657 hr_utility.set_location(' Leaving:'|| l_proc, 5);
3658 raise;
3659 end if;
3660 hr_utility.set_location(' Leaving:'|| l_proc, 6);
3661 end chk_sal_rp_freq_sal_rp;
3662 --
3663 -- ---------------------------------------------------------------------------
3664 -- |-----------------------< chk_set_of_books_id >---------------------------|
3665 -- ---------------------------------------------------------------------------
3666 --
3667 procedure chk_set_of_books_id
3668 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3669 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3670 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3671 ,p_set_of_books_id in per_all_assignments_f.set_of_books_id%TYPE
3672 ,p_effective_date in date
3673 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3674 )
3675 is
3676 --
3677 l_proc varchar2(72) := g_package||'chk_set_of_books_id';
3678 l_exists varchar2(1);
3679 l_api_updating boolean;
3680 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3681 --
3682 cursor csr_valid_sob is
3683 select null
3684 from gl_sets_of_books
3685 where set_of_books_id = p_set_of_books_id;
3686 --
3687 cursor csr_valid_fsp_bg is
3688 select null
3689 from financials_system_params_all
3690 where set_of_books_id = p_set_of_books_id
3691 and business_group_id = p_business_group_id;
3692 --
3693 begin
3694 hr_utility.set_location('Entering:'|| l_proc, 10);
3695 --
3696 -- Check mandatory parameters have been set
3697 --
3698 hr_api.mandatory_arg_error
3699 (p_api_name => l_proc
3700 ,p_argument => 'effective_date'
3701 ,p_argument_value => p_effective_date
3702 );
3703 hr_utility.set_location(l_proc, 20);
3704 --
3705 -- Only proceed with validation if :
3706 -- a) The current g_old_rec is current and
3707 -- b) The value for set_of_books_id has changed
3708 --
3709 l_api_updating := per_asg_shd.api_updating
3710 (p_assignment_id => p_assignment_id
3711 ,p_effective_date => p_effective_date
3712 ,p_object_version_number => p_object_version_number
3713 );
3714 hr_utility.set_location(l_proc, 30);
3715 --
3716 if ((l_api_updating and
3717 nvl(per_asg_shd.g_old_rec.set_of_books_id,
3718 hr_api.g_number) <> nvl(p_set_of_books_id, hr_api.g_number)) or
3719 (NOT l_api_updating))
3720 then
3721 hr_utility.set_location(l_proc, 40);
3722 --
3723 -- Check if set of books is set
3724 --
3725 if p_set_of_books_id is not null then
3726 --
3727 -- Check that the assignment is an employee or applicant
3728 -- or contact or offer assignment.
3729 --
3730 if p_assignment_type not in ('E','A','C','O') then
3731 --
3732 hr_utility.set_message(801, 'HR_51175_ASG_INV_ASG_TYP_SOB');
3733 hr_utility.raise_error;
3734 --
3735 end if;
3736 hr_utility.set_location(l_proc, 50);
3737 --
3738 -- Check that the set of books exists in GL_SETS_OF_BOOKS.
3739 --
3740 open csr_valid_sob;
3741 fetch csr_valid_sob into l_exists;
3742 if csr_valid_sob%notfound then
3743 close csr_valid_sob;
3744 hr_utility.set_message(801, 'HR_51160_ASG_INV_SET_OF_BOOKS');
3745 hr_utility.raise_error;
3746 --
3747 end if;
3748 close csr_valid_sob;
3749 hr_utility.set_location(l_proc, 60);
3750 --
3751 -- Check that the set of books exists in
3752 -- FINANCIALS_SYSTEM_PARAMS_ALL for the assignment business
3753 -- group.
3754 --
3755 open csr_valid_fsp_bg;
3756 fetch csr_valid_fsp_bg into l_exists;
3757 if csr_valid_fsp_bg%notfound then
3758 close csr_valid_fsp_bg;
3759 hr_utility.set_message(801, 'HR_51316_ASG_INV_FSP_SOB_BG');
3760 hr_utility.raise_error;
3761 --
3762 end if;
3763 close csr_valid_fsp_bg;
3764 hr_utility.set_location(l_proc, 70);
3765 --
3766 end if;
3767 hr_utility.set_location(l_proc, 80);
3768 --
3769 end if;
3770 hr_utility.set_location(' Leaving:'|| l_proc, 100);
3771 exception
3772 when app_exception.application_exception then
3773 if hr_multi_message.exception_add
3774 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID'
3775 ) then
3776 hr_utility.set_location(' Leaving:'|| l_proc, 110);
3777 raise;
3778 end if;
3779 hr_utility.set_location(' Leaving:'|| l_proc, 120);
3780 end chk_set_of_books_id;
3781 --
3782 -- ---------------------------------------------------------------------------
3783 -- |--------------------< chk_soft_coding_keyflex_id >-----------------------|
3784 -- ---------------------------------------------------------------------------
3785 --
3786 procedure chk_soft_coding_keyflex_id
3787 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3788 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3789 ,p_soft_coding_keyflex_id in per_all_assignments_f.soft_coding_keyflex_id%TYPE
3790 ,p_effective_date in date
3791 ,p_validation_start_date in date
3792 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3793 ,p_payroll_id in per_all_assignments_f.payroll_id%TYPE
3794 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3795 )
3796 is
3797 --
3798 l_exists varchar2(1);
3799 l_api_updating boolean;
3800 l_proc varchar2(72) := g_package||'chk_soft_coding_keyflex_id';
3801 l_legislation_code per_business_groups.legislation_code%TYPE;
3802 --
3803 --
3804 cursor csr_valid_keyflex is
3805 select null
3806 from hr_soft_coding_keyflex
3807 where soft_coding_keyflex_id = p_soft_coding_keyflex_id
3808 and enabled_flag = 'Y'
3809 and p_validation_start_date
3810 between nvl(start_date_active,hr_api.g_sot)
3811 and nvl(end_date_active,hr_api.g_eot);
3812 --
3813 cursor csr_bg is
3814 select legislation_code
3815 from per_business_groups_perf
3816 where business_group_id = p_business_group_id;
3817 --
3818 cursor csr_pay_legislation_rules is
3819 select null
3820 from pay_legislation_rules
3821 where legislation_code = l_legislation_code
3822 and rule_type = 'TAX_UNIT'
3823 and rule_mode = 'Y';
3824 --
3825 cursor csr_tax_unit_message(p_message_name varchar2) is
3826 select 1 from fnd_new_messages
3827 where message_name = p_message_name
3828 and application_id = 801;
3829 --
3830 begin
3831 hr_utility.set_location('Entering:'|| l_proc, 10);
3832 --
3833 -- Check mandatory parameters have been set
3834 --
3835 hr_api.mandatory_arg_error
3836 (p_api_name => l_proc
3837 ,p_argument => 'effective_date'
3838 ,p_argument_value => p_effective_date
3839 );
3840 hr_utility.set_location(l_proc, 20);
3841 --
3842 -- Only proceed with validation if :
3843 -- a) The current g_old_rec is current and
3844 -- b) The value for soft coding keyflex has changed
3845 -- c) Soft coding keyflex is null and Payroll is populated. --#2182184
3846 --
3847 l_api_updating := per_asg_shd.api_updating
3848 (p_assignment_id => p_assignment_id
3849 ,p_effective_date => p_effective_date
3850 ,p_object_version_number => p_object_version_number
3851 );
3852 hr_utility.set_location(l_proc, 30);
3853 --
3854 if ((l_api_updating and
3855 nvl(per_asg_shd.g_old_rec.soft_coding_keyflex_id, hr_api.g_number) <>
3856 nvl(p_soft_coding_keyflex_id, hr_api.g_number)) or
3857 --
3858 -- ****** Start new code for bug #2182184 **************************
3859 --
3860 (l_api_updating and
3861 (p_soft_coding_keyflex_id is null and p_payroll_id is not null)) or
3862 --
3863 -- ****** End new code for bug #2182184 ****************************
3864 --
3865 (NOT l_api_updating)) then
3866 --
3867 hr_utility.set_location(l_proc, 40);
3868 --
3869 if p_soft_coding_keyflex_id is not null then
3870 --
3871 -- Check that the assignment is an employee assignment.
3872 -- altered to allow applicants to have this specified
3873 --
3874 -- <OAB_CHANGE> - Extend restriction to allow assignment type 'B'
3875 --
3876 if p_assignment_type not in ('E','A','B','C','O') then
3877 --
3878 hr_utility.set_message(801, 'HR_51227_ASG_INV_ASG_TYP_SCF');
3879 hr_multi_message.add
3880 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3881 );
3882 --
3883 end if;
3884 hr_utility.set_location(l_proc, 50);
3885 --
3886 -- Check that soft_coding_keyflex_id exists on
3887 -- hr_soft_coding_keyflex
3888 --
3889 open csr_valid_keyflex;
3890 fetch csr_valid_keyflex into l_exists;
3891 if csr_valid_keyflex%notfound then
3892 close csr_valid_keyflex;
3893 hr_utility.set_message(801, 'HR_7383_ASG_INV_KEYFLEX');
3894 hr_multi_message.add
3895 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOFT_CODING_KEYFLEX_ID'
3896 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
3897 );
3898 else
3899 close csr_valid_keyflex;
3900 end if;
3901 hr_utility.set_location(l_proc, 60);
3902 else
3903 --
3904 -- Check that for relevant legislations SCL is mandatory,
3905 -- when payroll_id is populated #909279
3906 --
3907 hr_utility.set_location(l_proc, 45);
3908 if p_payroll_id is not null and
3909 p_assignment_type = 'E' then
3910 open csr_bg;
3911 fetch csr_bg into l_legislation_code;
3912 close csr_bg;
3913 --
3914 hr_utility.set_location(l_proc, 55);
3915 open csr_pay_legislation_rules;
3916 fetch csr_pay_legislation_rules into l_exists;
3917 if csr_pay_legislation_rules%found then
3918 close csr_pay_legislation_rules;
3919 if l_legislation_code = 'US' then
3920 hr_utility.set_message(801, 'HR_50001_EMP_ASS_NO_GRE');
3921 else
3922 open csr_tax_unit_message('HR_INV_LEG_ENT_'||l_legislation_code);
3923 fetch csr_tax_unit_message into l_exists;
3924
3925 if csr_tax_unit_message%found then
3926 hr_utility.set_message(801, 'HR_INV_LEG_ENT_'||l_legislation_code);
3927 else
3928 hr_utility.set_message(801, 'HR_34024_IP_INV_LEG_ENT');
3929 end if;
3930 close csr_tax_unit_message;
3931 end if;
3932 hr_multi_message.add
3933 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PAYROLL_ID'
3934 );
3935 else
3936 close csr_pay_legislation_rules;
3937 end if;
3938 hr_utility.set_location(l_proc, 65);
3939 end if;
3940 end if;
3941 --
3942 end if;
3943 --
3944 hr_utility.set_location(' Leaving:'|| l_proc, 70);
3945 end chk_soft_coding_keyflex_id;
3946 --
3947 -- ---------------------------------------------------------------------------
3948 -- |--------------------< chk_source_organization_id >-----------------------|
3949 -- ---------------------------------------------------------------------------
3950 --
3951 procedure chk_source_organization_id
3952 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
3953 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
3954 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
3955 ,p_source_organization_id in per_all_assignments_f.source_organization_id%TYPE
3956 ,p_effective_date in date
3957 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
3958 ,p_validation_start_date in date
3959 ,p_validation_end_date in date
3960 )
3961 is
3962 --
3963 l_proc varchar2(72) := g_package||'chk_source_organization_id';
3964 l_api_updating boolean;
3965 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
3966 --
3967 cursor csr_val_source_org_id is
3968 select business_group_id
3969 from per_organization_units
3970 where organization_id = p_source_organization_id
3971 and p_validation_start_date
3972 between date_from
3973 and nvl(date_to, hr_api.g_eot);
3974 --
3975 begin
3976 hr_utility.set_location('Entering:'|| l_proc, 10);
3977 --
3978 -- Check mandatory parameters have been set
3979 --
3980 hr_api.mandatory_arg_error
3981 (p_api_name => l_proc
3982 ,p_argument => 'effective_date'
3983 ,p_argument_value => p_effective_date
3984 );
3985 --
3986 hr_api.mandatory_arg_error
3987 (p_api_name => l_proc
3988 ,p_argument => 'validation_start_date'
3989 ,p_argument_value => p_validation_start_date
3990 );
3991 --
3992 hr_api.mandatory_arg_error
3993 (p_api_name => l_proc
3994 ,p_argument => 'validation_end_date'
3995 ,p_argument_value => p_validation_end_date
3996 );
3997 hr_utility.set_location(l_proc, 20);
3998 --
3999 -- Only proceed with validation if :
4000 -- a) The current g_old_rec is current and
4001 -- b) The value for source organization has changed
4002 --
4003 l_api_updating := per_asg_shd.api_updating
4004 (p_assignment_id => p_assignment_id
4005 ,p_effective_date => p_effective_date
4006 ,p_object_version_number => p_object_version_number
4007 );
4008 hr_utility.set_location(l_proc, 30);
4009 --
4010 if ((l_api_updating
4011 and nvl(per_asg_shd.g_old_rec.source_organization_id, hr_api.g_number)
4012 <> nvl(p_source_organization_id, hr_api.g_number))
4013 or
4014 (NOT l_api_updating))
4015 then
4016 hr_utility.set_location(l_proc, 40);
4017 --
4018 -- Check if source organization is set
4019 --
4020 if p_source_organization_id is not null then
4021 --
4022 -- Check that the assignment is not an applicant or offer assignment.
4023 --
4024 if p_assignment_type in ('E','C','B') then
4025 --
4026 -- Check if the employee assignment is being updated
4027 --
4028 If l_api_updating then
4029 --
4030 hr_utility.set_message(801, 'HR_51220_ASG_INV_EASG_U_SORG');
4031 hr_multi_message.add
4032 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4033 );
4034 --
4035 else -- inserting an employee assignment
4036 --
4037 hr_utility.set_message(801, 'HR_51219_ASG_INV_EASG_I_SORG');
4038 hr_multi_message.add
4039 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4040 );
4041 --
4042 end if;
4043 hr_utility.set_location(l_proc, 60);
4044 --
4045 end if;
4046 hr_utility.set_location(l_proc, 70);
4047 --
4048 -- Check if the source organization exists where the effective
4049 -- start date of the assignment is between the date from and
4050 -- date to of the source organization.
4051 --
4052 open csr_val_source_org_id;
4053 fetch csr_val_source_org_id into l_business_group_id;
4054 if csr_val_source_org_id%notfound then
4055 close csr_val_source_org_id;
4056 hr_utility.set_message(801, 'HR_51308_ASG_INV_SOURCE_ORG');
4057 hr_multi_message.add
4058 (p_associated_column1 =>
4059 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4060 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4061 );
4062 --
4063 else
4064 close csr_val_source_org_id;
4065 end if;
4066 hr_utility.set_location(l_proc, 80);
4067 --
4068 -- Check that the source organization is in the same business group
4069 -- as the business group of the assignment.
4070 --
4071 If p_business_group_id <> l_business_group_id then
4072 --
4073 hr_utility.set_message(801, 'HR_51309_ASG_INV_SOURCE_ORG_BG');
4074 hr_multi_message.add
4075 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_ORGANIZATION_ID'
4076 );
4077 --
4078 end if;
4079 hr_utility.set_location(l_proc, 90);
4080 --
4081 end if;
4082 hr_utility.set_location(l_proc, 100);
4083 --
4084 end if;
4085 hr_utility.set_location(' Leaving:'|| l_proc, 110);
4086 end chk_source_organization_id;
4087 --
4088 -- ---------------------------------------------------------------------------
4089 -- |------------------------< chk_source_type >------------------------------|
4090 -- ---------------------------------------------------------------------------
4091 --
4092 procedure chk_source_type
4093 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4094 ,p_source_type in per_all_assignments_f.source_type%TYPE
4095 ,p_recruitment_activity_id in per_all_assignments_f.recruitment_activity_id%TYPE
4096 ,p_effective_date in date
4097 ,p_validation_start_date in date
4098 ,p_validation_end_date in date
4099 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4100 )
4101 is
4102 --
4103 l_proc varchar2(72) := g_package||'chk_source_type';
4104 l_api_updating boolean;
4105 l_rec_act_type per_recruitment_activities.type%TYPE;
4106 --
4107 cursor csr_get_rec_act_type is
4108 select type
4109 from per_recruitment_activities
4110 where recruitment_activity_id = p_recruitment_activity_id;
4111 --
4112 begin
4113 hr_utility.set_location('Entering:'|| l_proc, 10);
4114 --
4115 -- Check mandatory parameters have been set
4116 --
4117 hr_api.mandatory_arg_error
4118 (p_api_name => l_proc
4119 ,p_argument => 'effective_date'
4120 ,p_argument_value => p_effective_date
4121 );
4122 hr_api.mandatory_arg_error
4123 (p_api_name => l_proc
4124 ,p_argument => 'validation_start_date'
4125 ,p_argument_value => p_validation_start_date
4126 );
4127 --
4128 hr_api.mandatory_arg_error
4129 (p_api_name => l_proc
4130 ,p_argument => 'validation_end_date'
4131 ,p_argument_value => p_validation_end_date
4132 );
4133 hr_utility.set_location(l_proc, 20);
4134 --
4135 -- Only proceed with validation if :
4136 -- a) The current g_old_rec is current and
4137 -- b) The value for source type has changed
4138 --
4139 l_api_updating := per_asg_shd.api_updating
4140 (p_assignment_id => p_assignment_id
4141 ,p_effective_date => p_effective_date
4142 ,p_object_version_number => p_object_version_number
4143 );
4144 hr_utility.set_location(l_proc, 30);
4145 --
4146 if ((l_api_updating and
4147 nvl(per_asg_shd.g_old_rec.source_type, hr_api.g_varchar2) <>
4148 nvl(p_source_type, hr_api.g_varchar2))
4149 or (NOT l_api_updating))
4150 then
4151 hr_utility.set_location(l_proc, 40);
4152 --
4153 -- Check if source type is set
4154 --
4155 if p_source_type is not null then
4156 --
4157 -- Check that the source type exists in hr_lookups for the lookup
4158 -- type 'REC_TYPE' with an enabled flag set to 'Y' and that the
4159 -- effective start date of the assignment is between start date
4160 -- active and end date active in hr_lookups.
4161 --
4162 if hr_api.not_exists_in_dt_hr_lookups
4163 (p_effective_date => p_effective_date
4164 ,p_validation_start_date => p_validation_start_date
4165 ,p_validation_end_date => p_validation_end_date
4166 ,p_lookup_type => 'REC_TYPE'
4167 ,p_lookup_code => p_source_type
4168 )
4169 then
4170 --
4171 hr_utility.set_message(801, 'HR_51162_ASG_INV_SOURCE_TYPE');
4172 hr_multi_message.add
4173 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4174 );
4175 --
4176 end if;
4177 hr_utility.set_location(l_proc, 50);
4178 --
4179 -- Check if recruitment activity is set
4180 --
4181 If p_recruitment_activity_id is not null then
4182 --
4183 -- Check if the source type is the same as the type of the
4184 -- recruitment activity
4185 --
4186 open csr_get_rec_act_type;
4187 fetch csr_get_rec_act_type into l_rec_act_type;
4188 close csr_get_rec_act_type;
4189 hr_utility.set_location(l_proc, 60);
4190 --
4191 If p_source_type <> nvl(l_rec_act_type, hr_api.g_varchar2) then
4192 --
4193 hr_utility.set_message(801, 'HR_51325_ASG_INV_SOU_TYP_RAT');
4194 hr_multi_message.add
4195 (p_associated_column1 =>
4196 'PER_ALL_ASSIGNMENTS_F.RECRUITMENT_ACTIVITY_ID'
4197 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SOURCE_TYPE'
4198 );
4199 --
4200 end if;
4201 hr_utility.set_location(l_proc, 70);
4202 --
4203 end if;
4204 hr_utility.set_location(l_proc, 80);
4205 --
4206 end if;
4207 hr_utility.set_location(l_proc, 90);
4208 --
4209 end if;
4210 --
4211 hr_utility.set_location(' Leaving:'|| l_proc, 100);
4212 end chk_source_type;
4213 --
4214 -- ---------------------------------------------------------------------------
4215 -- |-------------------< chk_special_ceiling_step_id >-----------------------|
4216 -- ---------------------------------------------------------------------------
4217 --
4218 procedure chk_special_ceiling_step_id
4219 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4220 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
4221 ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
4222 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
4223 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
4224 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
4225 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
4226 ,p_effective_date in date
4227 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4228 )
4229 is
4230 --
4231 l_sequence per_spinal_point_steps_f.sequence%TYPE;
4232 l_exists varchar2(1);
4233 l_api_updating boolean;
4234 l_business_group_id number(15);
4235 l_proc varchar2(72) := g_package||'chk_special_ceiling_step_id';
4236 --
4237 cursor csr_valid_step is
4238 select 1
4239 from sys.dual
4240 where exists
4241 (select null
4242 from per_spinal_point_steps_f psps
4243 where psps.effective_start_date <= p_validation_start_date
4244 and psps.step_id = p_special_ceiling_step_id
4245 and (exists
4246 (select null
4247 from per_spinal_point_steps_f psps2
4248 where psps2.effective_end_date >= p_validation_end_date
4249 and psps2.step_id = p_special_ceiling_step_id
4250 and psps2.grade_spine_id = psps.grade_spine_id)));
4251 --
4252 cursor csr_get_bus_grp is
4253 select pgs.business_group_id
4254 from per_grade_spines_f pgs
4255 where pgs.ceiling_step_id = p_special_ceiling_step_id
4256 and p_effective_date between pgs.effective_start_date
4257 and pgs.effective_end_date;
4258 --
4259 cursor csr_valid_step_grade is
4260 select psps.sequence
4261 from per_grade_spines_f pgs,
4262 per_spinal_point_steps_f psps
4263 where psps.step_id = p_special_ceiling_step_id
4264 and pgs.grade_id = p_grade_id
4265 and pgs.grade_spine_id = psps.grade_spine_id
4266 and p_effective_date between pgs.effective_start_date
4267 and pgs.effective_end_date
4268 and p_effective_date between psps.effective_start_date
4269 and psps.effective_end_date;
4270 --
4271 cursor csr_low_step is
4272 select 1
4273 from sys.dual
4274 where exists(select null
4275 from per_spinal_point_placements_f pspp
4276 , per_spinal_point_steps_f psps
4277 , per_grade_spines_f pgs
4278 where pspp.assignment_id = p_assignment_id
4279 and pspp.step_id = psps.step_id
4280 and psps.grade_spine_id=pgs.grade_spine_id
4281 and pgs.grade_id = p_grade_id
4282 and psps.sequence > l_sequence
4283 and pspp.effective_start_date <= p_validation_end_date
4284 and pspp.effective_end_date >= p_validation_start_date
4285 and psps.effective_start_date between psps.effective_start_date
4286 and psps.effective_end_date
4287 and psps.effective_start_date between pgs.effective_start_date
4288 and pgs.effective_end_date);
4289 --
4290 begin
4291 hr_utility.set_location('Entering:'|| l_proc, 10);
4292 --
4293 --
4294 -- Check mandatory parameters have been set
4295 --
4296 hr_api.mandatory_arg_error
4297 (p_api_name => l_proc
4298 ,p_argument => 'validation_start_date'
4299 ,p_argument_value => p_validation_start_date
4300 );
4301 --
4302 hr_api.mandatory_arg_error
4303 (p_api_name => l_proc
4304 ,p_argument => 'validation_end_date'
4305 ,p_argument_value => p_validation_end_date
4306 );
4307 --
4308 hr_api.mandatory_arg_error
4309 (p_api_name => l_proc
4310 ,p_argument => 'effective_date'
4311 ,p_argument_value => p_effective_date
4312 );
4313 --
4314 hr_api.mandatory_arg_error
4315 (p_api_name => l_proc
4316 ,p_argument => 'business_group_id'
4317 ,p_argument_value => p_business_group_id
4318 );
4319 hr_utility.set_location(l_proc, 20);
4320 --
4321 -- Only proceed with validation if :
4322 -- a) The current g_old_rec is current and
4323 -- b) The value for special ceiling step has changed
4324 --
4325 l_api_updating := per_asg_shd.api_updating
4326 (p_assignment_id => p_assignment_id
4327 ,p_effective_date => p_effective_date
4328 ,p_object_version_number => p_object_version_number
4329 );
4330 hr_utility.set_location(l_proc, 30);
4331 --
4332 if ((l_api_updating and
4333 nvl(per_asg_shd.g_old_rec.special_ceiling_step_id, hr_api.g_number) <>
4334 nvl(p_special_ceiling_step_id, hr_api.g_number)) or
4335 (NOT l_api_updating)) then
4336 hr_utility.set_location(l_proc, 40);
4337 --
4338 if p_special_ceiling_step_id is not null then
4339 --
4340 -- Check that the assignment is an employee,applicant or benefits
4341 -- assignment.
4342 --
4343 if p_assignment_type not in ('E','A','B','O') then
4344 --
4345 hr_utility.set_message(801, 'HR_51225_ASG_INV_ASG_TYP_SCS');
4346 hr_multi_message.add
4347 (p_associated_column1 =>
4348 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4349 );
4350 --
4351 end if;
4352 hr_utility.set_location(l_proc, 50);
4353 --
4354 -- Check that special_ceiling_step_id exists and is date effective
4355 -- per_grade_spines_f
4356 --
4357 open csr_valid_step;
4358 fetch csr_valid_step into l_exists;
4359 if csr_valid_step%notfound then
4360 close csr_valid_step;
4361 hr_utility.set_message(801, 'HR_7379_ASG_INV_SPEC_CEIL_STEP');
4362 hr_multi_message.add
4363 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4364 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4365 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
4366 );
4367 --
4368 else
4369 close csr_valid_step;
4370 end if;
4371 hr_utility.set_location(l_proc, 60);
4372 --
4373 -- Check that the business group of the special_ceiling_step_id on
4374 -- per_grade_spines is the same as that of the assignment.
4375 --
4376 open csr_get_bus_grp;
4377 fetch csr_get_bus_grp into l_business_group_id;
4378 if l_business_group_id <> p_business_group_id then
4379 close csr_get_bus_grp;
4380 hr_utility.set_message(801, 'HR_7375_ASG_INV_BG_SP_CLG_STEP');
4381 hr_multi_message.add
4382 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4383 );
4384 else
4385 close csr_get_bus_grp;
4386 end if;
4387 hr_utility.set_location(l_proc, 70);
4388 --
4389 if hr_multi_message.no_exclusive_error
4390 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4391 ) then
4392 --
4393 -- Check that the special_ceiling_step_id is valid for the grade
4394 -- if p_grade is not null.
4395 --
4396 if p_grade_id is not null then
4397 open csr_valid_step_grade;
4398 fetch csr_valid_step_grade into l_sequence;
4399 if csr_valid_step_grade%notfound then
4400 close csr_valid_step_grade;
4401 hr_utility.set_message(801, 'HR_7380_ASG_STEP_INV_FOR_GRADE');
4402 hr_multi_message.add
4403 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4404 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4405 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4406 );
4407 else
4408 close csr_valid_step_grade;
4409 end if;
4410 hr_utility.set_location(l_proc, 80);
4411 else
4412 --
4413 -- If the value for special ceiling step is not null
4414 -- then grade id must also be not null
4415 --
4416 hr_utility.set_message(801, 'HR_7434_ASG_GRADE_REQUIRED');
4417 hr_multi_message.add
4418 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID'
4419 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4420 );
4421 end if;
4422 hr_utility.set_location(l_proc, 90);
4423 --
4424 -- Check if updating
4425 --
4426 if l_api_updating then
4427 --
4428 -- Check that special_ceiling_step_id is not lower than the
4429 -- spinal point placement for the assignment.
4430 --
4431 open csr_low_step;
4432 fetch csr_low_step into l_exists;
4433 if csr_low_step%found then
4434 close csr_low_step;
4435 hr_utility.set_message(801, 'HR_7381_ASG_CEIL_STEP_TOO_HIGH');
4436 hr_multi_message.add
4437 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_ID'
4438 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4439 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
4440 );
4441 else
4442 close csr_low_step;
4443 end if;
4444 hr_utility.set_location(l_proc, 110);
4445 --
4446 end if;
4447 hr_utility.set_location(l_proc, 120);
4448 end if;
4449 hr_utility.set_location(l_proc, 130);
4450 --
4451 end if; -- no exclusive error
4452 end if;
4453 --
4454 hr_utility.set_location(' Leaving:'|| l_proc, 140);
4455 end chk_special_ceiling_step_id;
4456 --
4457 -- ---------------------------------------------------------------------------
4458 -- |--------------------------< chk_supervisor_id >--------------------------|
4459 -- ---------------------------------------------------------------------------
4460 --
4461 procedure chk_supervisor_id
4462 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4463 ,p_supervisor_id in per_all_assignments_f.supervisor_id%TYPE
4464 ,p_person_id in per_all_assignments_f.person_id%TYPE
4465 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
4466 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
4467 ,p_effective_date in date
4468 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4469 )
4470 is
4471 --
4472 l_proc varchar2(72) := g_package||'chk_supervisor_id';
4473 l_api_updating boolean;
4474 --
4475 l_business_group_id per_people_f.business_group_id%TYPE;
4476 l_current_employee_flag per_people_f.current_employee_flag%TYPE;
4477 l_current_npw_flag per_people_f.current_npw_flag%TYPE;
4478 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
4479 --
4480 -- Fix for bug 4305723 starts here.
4481 --
4482 cursor csr_party_id(p_per_id number) IS
4483 select party_id
4484 from per_all_people_f
4485 where person_id = p_per_id
4486 and p_validation_start_date
4487 between effective_start_date
4488 and effective_end_date;
4489 --
4490 l_per_party_id number;
4491 l_sup_party_id number;
4492 --
4493 -- Fix for bug 4305723 ends here.
4494 --
4495 -- Bug#3917021
4496 cursor csr_asg_typ is
4497 select assignment_type
4498 from per_all_assignments_f asg
4499 where asg.assignment_id = p_assignment_id
4500 and p_validation_start_date
4501 between asg.effective_start_date
4502 and asg.effective_end_date;
4503 --
4504 cursor csr_valid_supervisor_id is
4505 select business_group_id, current_employee_flag, current_npw_flag
4506 from per_all_people_f
4507 where person_id = p_supervisor_id
4508 and p_validation_start_date
4509 between effective_start_date
4510 and effective_end_date;
4511 --
4512 begin
4513 hr_utility.set_location('Entering:'|| l_proc, 10);
4514 --
4515 if hr_multi_message.no_exclusive_error
4516 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4517 ) then
4518 --
4519 -- Check mandatory parameters have been set
4520 --
4521 hr_api.mandatory_arg_error
4522 (p_api_name => l_proc
4523 ,p_argument => 'person_id'
4524 ,p_argument_value => p_person_id
4525 );
4526 --
4527 hr_api.mandatory_arg_error
4528 (p_api_name => l_proc
4529 ,p_argument => 'business_group_id'
4530 ,p_argument_value => p_business_group_id
4531 );
4532 --
4533 hr_api.mandatory_arg_error
4534 (p_api_name => l_proc
4535 ,p_argument => 'validation_start_date'
4536 ,p_argument_value => p_validation_start_date
4537 );
4538 --
4539 hr_api.mandatory_arg_error
4540 (p_api_name => l_proc
4541 ,p_argument => 'effective_date'
4542 ,p_argument_value => p_effective_date
4543 );
4544 hr_utility.set_location(l_proc, 20);
4545 --
4546 -- Only proceed with validation if :
4547 -- a) The current g_old_rec is current and
4548 -- b) The value for supervisor_id has changed
4549 --
4550 l_api_updating := per_asg_shd.api_updating
4551 (p_assignment_id => p_assignment_id
4552 ,p_effective_date => p_effective_date
4553 ,p_object_version_number => p_object_version_number
4554 );
4555 hr_utility.set_location(l_proc, 30);
4556 --
4557 if ((l_api_updating and
4558 nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4559 nvl(p_supervisor_id, hr_api.g_number)) or
4560 (NOT l_api_updating))
4561 then
4562 hr_utility.set_location(l_proc, 40);
4563 --
4564 -- Check if supervisor is not null
4565 --
4566 if p_supervisor_id is not null then
4567 --
4568 -- Check that the supervisor is'nt the same person as the person of the
4569 -- assignment.
4570 --
4571 If p_supervisor_id = p_person_id then
4572 --
4573 hr_utility.set_message(801, 'HR_51143_ASG_EMP_EQUAL_SUP');
4574 hr_multi_message.add
4575 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4576 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4577 );
4578 end if;
4579 hr_utility.set_location(l_proc, 50);
4580 --
4581 -- Fix for bug 4305723 starts here. Check if the party_id is same for
4582 -- employee and supervisor.
4583 --
4584 open csr_party_id(p_person_id);
4585 fetch csr_party_id into l_per_party_id;
4586 close csr_party_id;
4587 --
4588 open csr_party_id(p_supervisor_id);
4589 fetch csr_party_id into l_sup_party_id;
4590 close csr_party_id;
4591 --
4592 If l_per_party_id = l_sup_party_id
4593 then
4594 hr_utility.set_message(800, 'HR_449603_ASG_SUP_DUP_PER');
4595 hr_multi_message.add
4596 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4597 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4598 );
4599 end if;
4600 --
4601 -- Fix for bug 4305723 ends here.
4602 --
4603 hr_utility.set_location(l_proc, 55);
4604 --
4605 -- Check that supervisor_id exists and that it is date effective within
4606 -- the validation period of the assignment.
4607 --
4608 open csr_valid_supervisor_id;
4609 fetch csr_valid_supervisor_id
4610 into l_business_group_id, l_current_employee_flag, l_current_npw_flag;
4611 if csr_valid_supervisor_id%notfound then
4612 close csr_valid_supervisor_id;
4613 --
4614 hr_utility.set_message(801, 'PAY_7599_SYS_SUP_DT_OUTDATE');
4615 hr_multi_message.add
4616 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4617 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4618 );
4619 else
4620 close csr_valid_supervisor_id;
4621 end if;
4622 hr_utility.set_location(l_proc, 60);
4623 --
4624 -- Check that the supervisor is in the same business group as the
4625 -- person of the assignment.
4626 --
4627 If (p_business_group_id <> l_business_group_id AND
4628 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='N')
4629 then
4630 --
4631 hr_utility.set_message(801, 'HR_51145_ASG_SUP_BG_NE_EMP_BG');
4632 hr_multi_message.add
4633 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4634 );
4635 end if;
4636 hr_utility.set_location(l_proc, 70);
4637 --
4638 -- Check that the supervisor is an employee or a contingent
4639 -- worker where the profile option permits.
4640 --
4641 If not (nvl(l_current_employee_flag, hr_api.g_varchar2) = 'Y'
4642 or (nvl(l_current_npw_flag, hr_api.g_varchar2) = 'Y' and
4643 nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'))
4644 Then
4645 --Bug3917021
4646 if csr_asg_typ%isopen then
4647 close csr_asg_typ;
4648 end if;
4649 open csr_asg_typ;
4650 fetch csr_asg_typ into l_assignment_type;
4651 close csr_asg_typ;
4652 if l_assignment_type in ('C','E','B') then
4653 --
4654 hr_utility.set_message(801, 'HR_51346_ASG_SUP_NOT_EMP');
4655 hr_multi_message.add
4656 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID');
4657 --
4658 end if;
4659 --Bug#3917021 ends here
4660 --
4661 end if;
4662 hr_utility.set_location(l_proc, 80);
4663 --
4664 end if;
4665 --
4666 end if;
4667 end if;
4668 --
4669 hr_utility.set_location(' Leaving:'|| l_proc, 90);
4670 end chk_supervisor_id;
4671 --
4672 -- ---------------------------------------------------------------------------
4673 -- |-------------------< chk_supervisor_assignment_id >----------------------|
4674 -- ---------------------------------------------------------------------------
4675 --
4676 procedure chk_supervisor_assignment_id
4677 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4678 ,p_supervisor_id in per_all_assignments_f.supervisor_id%TYPE
4679 ,p_supervisor_assignment_id in out nocopy per_all_assignments_f.supervisor_assignment_id%TYPE
4680 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
4681 ,p_effective_date in date
4682 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
4683 )
4684 is
4685 --
4686 l_proc varchar2(72) := g_package||'chk_supervisor_assignment_id';
4687 l_api_updating boolean;
4688 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
4689 --
4690 cursor csr_supervisor_assignment_id is
4691 select paaf.assignment_type
4692 from per_all_assignments_f paaf
4693 where paaf.person_id = p_supervisor_id
4694 and p_supervisor_id is not null
4695 and paaf.assignment_id = p_supervisor_assignment_id
4696 and p_validation_start_date between
4697 paaf.effective_start_date and paaf.effective_end_date;
4698 --
4699 begin
4700 hr_utility.set_location('Entering:'|| l_proc, 10);
4701 --
4702 if hr_multi_message.no_exclusive_error
4703 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4704 ) then
4705 --
4706 -- Check mandatory parameters have been set
4707 --
4708 hr_api.mandatory_arg_error
4709 (p_api_name => l_proc
4710 ,p_argument => 'validation_start_date'
4711 ,p_argument_value => p_validation_start_date
4712 );
4713 --
4714 hr_api.mandatory_arg_error
4715 (p_api_name => l_proc
4716 ,p_argument => 'effective_date'
4717 ,p_argument_value => p_effective_date
4718 );
4719 hr_utility.set_location(l_proc, 20);
4720 --
4721 -- Only proceed with validation if :
4722 -- a) The current g_old_rec is current and
4723 -- b) The value for supervisor_id has changed
4724 --
4725 l_api_updating := per_asg_shd.api_updating
4726 (p_assignment_id => p_assignment_id
4727 ,p_effective_date => p_effective_date
4728 ,p_object_version_number => p_object_version_number
4729 );
4730 hr_utility.set_location(l_proc, 30);
4731
4732 --
4733 -- Re-validate if either the supervisor or supervisor assignment
4734 -- has changed.
4735 --
4736 if (l_api_updating and
4737 ((nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4738 nvl(p_supervisor_id, hr_api.g_number))
4739 or
4740 (nvl(per_asg_shd.g_old_rec.supervisor_assignment_id, hr_api.g_number) <>
4741 nvl(p_supervisor_assignment_id, hr_api.g_number))) or
4742 (NOT l_api_updating))
4743 then
4744
4745 hr_utility.set_location(l_proc, 40);
4746 ----
4747 if ((nvl(per_asg_shd.g_old_rec.supervisor_id, hr_api.g_number) <>
4748 nvl(p_supervisor_id, hr_api.g_number))
4749 and
4750 (nvl(per_asg_shd.g_old_rec.supervisor_assignment_id, hr_api.g_number) =
4751 nvl(p_supervisor_assignment_id, hr_api.g_number))) then
4752 p_supervisor_assignment_id := NULL;
4753 end if;
4754 ---
4755
4756 if p_supervisor_assignment_id is not null then
4757 --
4758 --
4759 -- Only validate if the supervisor assignment is set.
4760 --
4761 -- There is no need to validate that the supervisor assignment is not
4762 -- the same as this person's assigment because the supervisor has already
4763 -- been validated at this point and so this assignment must belong to
4764 -- the given supervisor.
4765 --
4766 -- Check that supervisor assignment exists, that it is date effective
4767 -- within the validation period of the assignment and that it belongs
4768 -- to the given supervisor.
4769 --
4770 hr_utility.set_location(l_proc, 50);
4771
4772 open csr_supervisor_assignment_id;
4773 fetch csr_supervisor_assignment_id into l_assignment_type;
4774 if csr_supervisor_assignment_id%notfound then
4775 hr_utility.set_location(l_proc, 60);
4776 close csr_supervisor_assignment_id;
4777 --
4778 hr_utility.set_message(800, 'HR_50146_SUP_ASG_INVALID');
4779 hr_utility.raise_error;
4780 /* hr_multi_message.add
4781 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4782 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ID'
4783 ,p_associated_column3 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4784 ); */
4785 else
4786 close csr_supervisor_assignment_id;
4787 end if;
4788
4789 hr_utility.set_location(l_proc, 70);
4790
4791 --
4792 -- Check that the supervisor assignment is an employee or a contingent
4793 -- worker assignment.
4794 --
4795 If not (nvl(l_assignment_type, hr_api.g_varchar2) = 'E'
4796 or (nvl(l_assignment_type, hr_api.g_varchar2) = 'C' and
4797 nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'))
4798 Then
4799 --
4800 hr_utility.set_location(l_proc, 80);
4801 hr_utility.set_message(800, 'HR_50147_SUP_ASG_WRONG_TYPE');
4802 hr_utility.raise_error;
4803 /*hr_multi_message.add
4804 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.SUPERVISOR_ASSIGNMENT_ID'
4805 ); */
4806 --
4807 end if;
4808 hr_utility.set_location(l_proc, 90);
4809 --
4810 end if;
4811 --
4812 end if;
4813 end if;
4814 --
4815 hr_utility.set_location(' Leaving:'|| l_proc, 100);
4816
4817 end chk_supervisor_assignment_id;
4818 --
4819 -- ---------------------------------------------------------------------------
4820 -- |-----------------------< chk_system_pers_type >--------------------------|
4821 -- ---------------------------------------------------------------------------
4822 --
4823 -- Description:
4824 -- Validates that system person type has not changed in the future
4825 --
4826 -- Pre-conditions:
4827 -- None
4828 --
4829 -- In Arguments:
4830 -- p_person_id
4831 -- p_validation_start_date
4832 -- p_validation_end_date
4833 -- p_datetrack_mode
4834 -- p_effective_date
4835 --
4836 -- Post Success:
4837 -- If no system person type changes exist in the future then processing
4838 -- continues.
4839 --
4840 -- Post Failure:
4841 -- If the system person type changes in the future an application error
4842 -- is raised and processing is terminated.
4843 --
4844 -- Access Status:
4845 -- Internal Table Handler Use Only.
4846 --
4847 procedure chk_system_pers_type
4848 (p_person_id in per_all_assignments_f.person_id%TYPE
4849 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
4850 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
4851 ,p_datetrack_mode in varchar2
4852 ,p_effective_date in date
4853 )
4854 is
4855 --
4856 l_proc varchar2(72) := g_package||'chk_system_pers_type';
4857 --
4858 begin
4859 hr_utility.set_location('Entering:'|| l_proc, 1);
4860 --
4861 -- Check mandatory parameters have been set
4862 --
4863 hr_api.mandatory_arg_error
4864 (p_api_name => l_proc
4865 ,p_argument => 'person_id'
4866 ,p_argument_value => p_person_id
4867 );
4868 --
4869 hr_api.mandatory_arg_error
4870 (p_api_name => l_proc
4871 ,p_argument => 'validation_start_date'
4872 ,p_argument_value => p_validation_start_date
4873 );
4874 --
4875 hr_api.mandatory_arg_error
4876 (p_api_name => l_proc
4877 ,p_argument => 'validation_end_date'
4878 ,p_argument_value => p_validation_end_date
4879 );
4880 --
4881 hr_utility.set_location(l_proc, 2);
4882 --
4883 -- Only trigger validation for the following datetrack modes :
4884 -- - UPDATE_OVERRIDE
4885 -- - ZAP
4886 -- - DELETE -> No longer required. When setting FPD which is
4887 -- the same as ATD any future dated ASG changes relative
4888 -- to FPD(ATD) require deleting. There will always be one
4889 -- in this case for the change from ACTIVE_ASSIGN to
4890 -- TERM_ASSIGN which was created when the actual_termination...
4891 -- API was called.
4892 -- The only call to the ASG RH with a datetrack mode of
4893 -- DELETE is from final_process_emp_asg_sup.
4894 -- - FUTURE_CHANGE
4895 -- - DELETE_NEXT_CHANGE
4896 --
4897 if p_datetrack_mode in ('UPDATE_OVERRIDE',
4898 'ZAP',
4899 'FUTURE_CHANGE',
4900 'DELETE_NEXT_CHANGE') then
4901 --
4902 -- Get current value for system_person_type (i.e. as of the
4903 -- effective date)
4904 --
4905 per_per_bus.chk_system_pers_type
4906 (p_person_id => p_person_id
4907 ,p_validation_start_date => p_validation_start_date
4908 ,p_validation_end_date => p_validation_end_date
4909 ,p_datetrack_mode => p_datetrack_mode
4910 ,p_effective_date => p_effective_date
4911 );
4912 --
4913 end if;
4914 hr_utility.set_location(' Leaving:'|| l_proc, 4);
4915 --
4916 exception
4917 when app_exception.application_exception then
4918 if hr_multi_message.exception_add
4919 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
4920 ) then
4921 hr_utility.set_location(' Leaving:'|| l_proc, 5);
4922 raise;
4923 end if;
4924 hr_utility.set_location(' Leaving:'|| l_proc, 6);
4925 --
4926 end chk_system_pers_type;
4927 --
4928 -- ---------------------------------------------------------------------------
4929 -- |-------------------------< chk_term_status >-----------------------------|
4930 -- ---------------------------------------------------------------------------
4931 --
4932 -- Description:
4933 -- Validates an assignment cannot be deleted using the following datetrack
4934 -- modes :
4935 -- - DELETE_NEXT_CHANGE
4936 -- - DELETE_FUTURE_CHANGE
4937 -- - UPDATE_OVERRIDE
4938 --
4939 -- if the assignment is terminated in the future, i.e. Assignment Status
4940 -- Type set to 'TERM_ASSIGN'.
4941 --
4942 -- Pre-conditions:
4943 -- None
4944 --
4945 -- In Arguments:
4946 -- p_assignment_id
4947 -- p_validation_start_date
4948 -- p_datetrack_mode
4949 --
4950 -- Post Success:
4951 -- If assignment is not terminated in the future then processing
4952 -- continues.
4953 --
4954 -- Post Failure:
4955 -- If the assignment is terminated in the future then an
4956 -- application error is raised and processing is terminated.
4957 --
4958 -- Access Status:
4959 -- Internal Table Handler Use Only.
4960 --
4961 procedure chk_term_status
4962 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
4963 ,p_datetrack_mode in varchar2
4964 ,p_validation_start_date in date
4965 )
4966 is
4967 --
4968 l_exists varchar2(1);
4969 l_proc varchar2(72) := g_package||'chk_term_status';
4970 --
4971 cursor csr_chk_term_status is
4972 select null
4973 from per_all_assignments_f pas
4974 , per_assignment_status_types past
4975 where pas.assignment_id = p_assignment_id
4976 and pas.effective_start_date >= p_validation_start_date
4977 and past.assignment_status_type_id = pas.assignment_status_type_id
4978 and past.per_system_status = 'TERM_ASSIGN';
4979 --
4980 begin
4981 hr_utility.set_location('Entering:'|| l_proc, 1);
4982 --
4983 -- Check whether assignment is terminated in the future
4984 --
4985 if p_datetrack_mode in ('UPDATE_OVERRIDE'
4986 ,'FUTURE_CHANGE'
4987 ,'DELETE_NEXT_CHANGE') then
4988 open csr_chk_term_status;
4989 fetch csr_chk_term_status into l_exists;
4990 if csr_chk_term_status%found then
4991 close csr_chk_term_status;
4992 hr_utility.set_message(801, 'HR_7412_ASG_ASS_TERM_IN_FUTURE');
4993 hr_multi_message.add
4994 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.DATETRACK_MODE'
4995 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
4996 );
4997 else
4998 close csr_chk_term_status;
4999 end if;
5000 end if;
5001 --
5002 hr_utility.set_location(' Leaving:'|| l_proc, 2);
5003 end chk_term_status;
5004 --
5005 -- 70.1 change d start.
5006 --
5007 -- ---------------------------------------------------------------------------
5008 -- |---------------------< chk_time_normal_finish >--------------------------|
5009 -- ---------------------------------------------------------------------------
5010 -- << 2734822 >>
5011 --
5012 procedure chk_time_finish_formatted
5013 (p_time_normal_finish in out nocopy per_all_assignments_f.time_normal_finish%TYPE
5014 )
5015 is
5016 --
5017 l_proc varchar2(72) := g_package||'chk_time_finish_formatted';
5018 --
5019 begin
5020 hr_utility.set_location('Entering:'|| l_proc, 1);
5021 --
5022 -- Check that time_normal_finish is valid
5023 --
5024 if p_time_normal_finish is not null then
5025 --
5026 hr_dbchkfmt.is_db_format(p_value => p_time_normal_finish
5027 ,p_formatted_output => p_time_normal_finish -- #2734822
5028 ,p_arg_name => 'time_normal_finish'
5029 ,p_format => 'TIMES');
5030 --
5031 end if;
5032 --
5033 hr_utility.set_location(' Leaving:'|| l_proc, 2);
5034 exception
5035 when app_exception.application_exception then
5036 if hr_multi_message.exception_add
5037 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_FINISH'
5038 ) then
5039 hr_utility.set_location(' Leaving:'|| l_proc, 3);
5040 raise;
5041 end if;
5042 hr_utility.set_location(' Leaving:'|| l_proc, 4);
5043 --
5044 end chk_time_finish_formatted;
5045 --
5046 --
5047 procedure chk_time_normal_finish
5048 (p_time_normal_finish in per_all_assignments_f.time_normal_finish%TYPE
5049 )
5050 is
5051 --
5052 l_value per_all_assignments_f.time_normal_finish%TYPE;
5053 begin
5054
5055 l_value := p_time_normal_finish;
5056 chk_time_finish_formatted(l_value);
5057
5058 end chk_time_normal_finish;
5059 --
5060 -- ---------------------------------------------------------------------------
5061 -- |---------------------< chk_time_normal_start >---------------------------|
5062 -- ---------------------------------------------------------------------------
5063 --
5064 procedure chk_time_start_formatted -- #2734822
5065 (p_time_normal_start in out nocopy per_all_assignments_f.time_normal_start%TYPE
5066 )
5067 is
5068 --
5069 l_proc varchar2(72) := g_package||'chk_time_normal_start_formatted';
5070 --
5071 begin
5072 hr_utility.set_location('Entering:'|| l_proc, 1);
5073 --
5074 -- Check that time_normal_start is valid
5075 --
5076 if p_time_normal_start is not null then
5077 --
5078 hr_dbchkfmt.is_db_format(p_value => p_time_normal_start
5079 ,p_formatted_output => p_time_normal_start -- #2734822
5080 ,p_arg_name => 'time_normal_start'
5081 ,p_format => 'TIMES');
5082 --
5083 end if;
5084 --
5085 hr_utility.set_location(' Leaving:'|| l_proc, 2);
5086 exception
5087 when app_exception.application_exception then
5088 if hr_multi_message.exception_add
5089 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.TIME_NORMAL_START'
5090 ) then
5091 hr_utility.set_location(' Leaving:'|| l_proc, 3);
5092 raise;
5093 end if;
5094 hr_utility.set_location(' Leaving:'|| l_proc, 4);
5095 end chk_time_start_formatted;
5096 --
5097 -- << 2734822 >>
5098 --
5099 procedure chk_time_normal_start
5100 (p_time_normal_start in per_all_assignments_f.time_normal_start%TYPE
5101 )
5102 is
5103 --
5104 l_value per_all_assignments_f.time_normal_start%TYPE;
5105 begin
5106
5107 l_value := p_time_normal_start;
5108 chk_time_start_formatted(l_value);
5109
5110 end chk_time_normal_start;
5111 --
5112 -- ---------------------------------------------------------------------------
5113 -- |-----------------------< chk_dup_apl_vacancy >----------------------------|
5114 -- ---------------------------------------------------------------------------
5115 --
5116 procedure chk_dup_apl_vacancy
5117 (p_person_id in per_all_assignments_f.person_id%TYPE
5118 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5119 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5120 ,p_effective_date in date
5121 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default null
5122 )
5123 is
5124 --
5125 l_proc varchar2(72) := g_package||'chk_dup_apl_vacancy';
5126 l_application_id per_applications.application_id%type;
5127 --
5128 cursor csr_dup_apl_vacancy is
5129 select pa.application_id
5130 from
5131 per_applications pa,
5132 per_all_assignments_f paf,
5133 per_vacancies pv
5134 where
5135 paf.person_id = p_person_id
5136 and paf.vacancy_id = pv.vacancy_id
5137 and paf.vacancy_id = p_vacancy_id
5138 and paf.application_id = pa.application_id
5139 and paf.business_group_id = p_business_group_id
5140 and p_effective_date between paf.effective_start_date
5141 and paf.effective_end_date
5142 and pa.date_end is null;
5143 --
5144 begin
5145 --
5146 hr_utility.set_location('Entering:'|| l_proc, 10);
5147 --
5148 -- Check this for Applicant assignments Only.
5149 --
5150 if p_assignment_type = 'A'
5151 or p_assignment_type is null
5152 then
5153 --
5154 open csr_dup_apl_vacancy;
5155 fetch csr_dup_apl_vacancy into l_application_id;
5156 if csr_dup_apl_vacancy%found then
5157 close csr_dup_apl_vacancy;
5158 hr_utility.set_message(800, 'HR_52217_DUP_APL_VACANCY');
5159 hr_utility.raise_error;
5160 --
5161 end if;
5162 close csr_dup_apl_vacancy;
5163 hr_utility.set_location(l_proc, 20);
5164 --
5165 end if;
5166 exception
5167 when app_exception.application_exception then
5168 if hr_multi_message.exception_add
5169 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5170 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5171 ) then
5172 hr_utility.set_location(' Leaving:'|| l_proc, 30);
5173 raise;
5174 end if;
5175 hr_utility.set_location(' Leaving:'|| l_proc, 40);
5176 --
5177 end chk_dup_apl_vacancy;
5178 --
5179 --
5180 -- ---------------------------------------------------------------------------
5181 -- |-------------------------< chk_vacancy_id >------------------------------|
5182 -- ---------------------------------------------------------------------------
5183 --
5184 procedure chk_vacancy_id
5185 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5186 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5187 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
5188 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
5189 ,p_effective_date in date
5190 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5191 ,p_validation_start_date in date
5192 ,p_validation_end_date in date
5193 )
5194 is
5195 --
5196 l_proc varchar2(72) := g_package||'chk_vacancy_id';
5197 l_api_updating boolean;
5198 l_exists varchar2(1);
5199 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
5200 --
5201 cursor csr_val_vacancy_id is
5202 select business_group_id
5203 from per_vacancies
5204 where vacancy_id = p_vacancy_id
5205 and p_validation_start_date
5206 between date_from
5207 and nvl(date_to, hr_api.g_eot);
5208 --
5209 cursor csr_val_vacancy_id_offer is
5210 select business_group_id
5211 from per_vacancies
5212 where vacancy_id = p_vacancy_id;
5213 --
5214 begin
5215 hr_utility.set_location('Entering:'|| l_proc, 10);
5216 --
5217 -- Check mandatory parameters have been set
5218 --
5219 hr_api.mandatory_arg_error
5220 (p_api_name => l_proc
5221 ,p_argument => 'effective_date'
5222 ,p_argument_value => p_effective_date
5223 );
5224 --
5225 hr_api.mandatory_arg_error
5226 (p_api_name => l_proc
5227 ,p_argument => 'validation_start_date'
5228 ,p_argument_value => p_validation_start_date
5229 );
5230 --
5231 hr_api.mandatory_arg_error
5232 (p_api_name => l_proc
5233 ,p_argument => 'validation_end_date'
5234 ,p_argument_value => p_validation_end_date
5235 );
5236 hr_utility.set_location(l_proc, 20);
5237 --
5238 -- Only proceed with validation if :
5239 -- a) The current g_old_rec is current and
5240 -- b) The value for vacancy has changed
5241 --
5242 l_api_updating := per_asg_shd.api_updating
5243 (p_assignment_id => p_assignment_id
5244 ,p_effective_date => p_effective_date
5245 ,p_object_version_number => p_object_version_number
5246 );
5247 hr_utility.set_location(l_proc, 30);
5248 --
5249 if ((l_api_updating and
5250 nvl(per_asg_shd.g_old_rec.vacancy_id, hr_api.g_number) <>
5251 nvl(p_vacancy_id, hr_api.g_number)) or
5252 (NOT l_api_updating)) then
5253 hr_utility.set_location(l_proc, 40);
5254 --
5255 -- Check if vacancy is not null
5256 --
5257 if p_vacancy_id is not null then
5258 --
5259 -- Check that when inserting the the assignment is an applicant or offer
5260 -- assignment on insert.
5261 --
5262 if p_assignment_type in ('E','C','B') then
5263 --
5264 -- Check if the employee assignment is being updated
5265 --
5266 If l_api_updating then
5267 --
5268 hr_utility.set_message(801, 'HR_51222_ASG_INV_EASG_U_VAC');
5269 hr_multi_message.add
5270 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5271 );
5272 --
5273 else -- inserting a non employee
5274 --
5275 hr_utility.set_message(801, 'HR_51221_ASG_INV_EASG_I_VAC');
5276 hr_multi_message.add
5277 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5278 );
5279 --
5280 end if;
5281 hr_utility.set_location(l_proc, 50);
5282 --
5283 end if;
5284 hr_utility.set_location(l_proc, 60);
5285 --
5286 if p_assignment_type = 'O'
5287 then
5288 --
5289 -- Assignment is an Offer Assignment.
5290 -- Check if the the vacancy is a valid vacancy.
5291 --
5292 open csr_val_vacancy_id_offer;
5293 fetch csr_val_vacancy_id_offer into l_business_group_id;
5294 if csr_val_vacancy_id_offer%notfound
5295 then
5296 --
5297 close csr_val_vacancy_id;
5298 hr_utility.set_message(800, 'HR_52591_CEL_INVL_VAC_ID');
5299 hr_utility.raise_error;
5300 --
5301 end if;
5302 --
5303 else
5304 -- Assignment is not an Offer Assignment.
5305 --
5306 -- Check if the vacancy exists where the effective start date
5307 -- of the assignment is between the date from and date to of the
5308 -- vacancy.
5309 --
5310 open csr_val_vacancy_id;
5311 fetch csr_val_vacancy_id into l_business_group_id;
5312 if csr_val_vacancy_id%notfound then
5313 close csr_val_vacancy_id;
5314 hr_utility.set_message(801, 'HR_51297_ASG_INV_VACANCY');
5315 hr_utility.raise_error;
5316 /* hr_multi_message.add
5317 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VACANCY_ID'
5318 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
5319 ); */
5320 --
5321 else
5322 close csr_val_vacancy_id;
5323 end if;
5324 --
5325 end if;
5326 hr_utility.set_location(l_proc, 70);
5327 --
5328 -- Check that the vacancy is in the same business group
5329 -- as the business group of the assignment.
5330 --
5331 If p_business_group_id <> l_business_group_id then
5332 --
5333 hr_utility.set_message(801, 'HR_51300_ASG_INV_VAC_BG');
5334 hr_utility.raise_error;
5335 --
5336 end if;
5337 hr_utility.set_location(l_proc, 80);
5338 --
5339 end if;
5340 hr_utility.set_location(l_proc, 90);
5341 --
5342 end if;
5343 --
5344 hr_utility.set_location(' Leaving:'|| l_proc, 100);
5345 end chk_vacancy_id;
5346 --
5347 -- ---------------------------------------------------------------------------
5348 -- |----------------------< gen_assignment_sequence >------------------------|
5349 -- ---------------------------------------------------------------------------
5350 --
5351 procedure gen_assignment_sequence
5352 (p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5353 ,p_person_id in per_all_assignments_f.person_id%TYPE
5354 ,p_assignment_sequence in out nocopy per_all_assignments_f.assignment_sequence%TYPE
5355 )
5356 is
5357 --
5358 l_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
5359 l_proc varchar2(72) := g_package||'gen_assignment_sequence';
5360 --
5361 cursor csr_get_ass_seq is
5362 select nvl(max(assignment_sequence),0) +1
5363 from per_all_assignments_f
5364 where person_id = p_person_id
5365 and assignment_type = p_assignment_type;
5366 --
5367 begin
5368 hr_utility.set_location('Entering:'|| l_proc, 1);
5369 --
5370 if hr_multi_message.no_exclusive_error
5371 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
5372 ) then
5373 --
5374 -- Check mandatory parameters have been set
5375 --
5376 hr_api.mandatory_arg_error
5377 (p_api_name => l_proc
5378 ,p_argument => 'assignment_type'
5379 ,p_argument_value => p_assignment_type
5380 );
5381 --
5382 hr_api.mandatory_arg_error
5383 (p_api_name => l_proc
5384 ,p_argument => 'person_id'
5385 ,p_argument_value => p_person_id
5386 );
5387 --
5388 hr_utility.set_location(l_proc, 2);
5389 --
5390 -- Generate next assignment sequence
5391 --
5392 open csr_get_ass_seq;
5393 fetch csr_get_ass_seq into l_assignment_sequence;
5394 close csr_get_ass_seq;
5395 p_assignment_sequence := l_assignment_sequence;
5396 end if;
5397 hr_utility.set_location(' Leaving:'|| l_proc, 3);
5398 --
5399 end gen_assignment_sequence;
5400 --
5401 -- ---------------------------------------------------------------------------
5402 -- |-----------------------< other_managers_in_org >-------------------------|
5403 -- ---------------------------------------------------------------------------
5404 --
5405 -- Description:
5406 -- Checks to see if any other current assignments for the same organization
5407 -- have the manager_flag set to 'Y', and returns the appropriate boolean
5408 -- result.
5409 --
5410 -- Pre-conditions:
5411 -- A valid Organization ID
5412 --
5413 -- In Arguments:
5414 -- p_assignment_id
5415 -- p_effective_date
5416 -- p_organization_id
5417 --
5418 -- Post Success:
5419 -- TRUE if other managers found, FALSE otherwise.
5420 --
5421 -- Post Failure:
5422 -- If the cursor raises an error, it will be passed back to the calling
5423 -- routine as an unhandled exception.
5424 --
5425 -- Access Status:
5426 -- Internal Table Handler Use Only.
5427 --
5428 function other_managers_in_org
5429 (p_organization_id in per_all_assignments_f.organization_id%TYPE
5430 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5431 ,p_effective_date in date
5432 )
5433 return boolean is
5434 --
5435 l_exists varchar2(1);
5436 l_proc varchar2(72) := g_package||'other_managers_in_org';
5437 l_other_manager_exists boolean;
5438 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
5439 --
5440 cursor csr_other_manager_in_org is
5441 select null
5442 from per_all_assignments_f pas
5443 where pas.organization_id = p_organization_id
5444 and pas.assignment_type = 'E'
5445 and pas.manager_flag = 'Y'
5446 and pas.assignment_id <> l_assignment_id
5447 and p_effective_date between pas.effective_start_date
5448 and pas.effective_end_date;
5449 --
5450 begin
5451 hr_utility.set_location('Entering:'|| l_proc, 1);
5452 --
5453 -- Check mandatory parameters have been set
5454 --
5455 hr_api.mandatory_arg_error
5456 (p_api_name => l_proc
5457 ,p_argument => 'organization_id'
5458 ,p_argument_value => p_organization_id
5459 );
5460 --
5461 hr_api.mandatory_arg_error
5462 (p_api_name => l_proc
5463 ,p_argument => 'effective_date'
5464 ,p_argument_value => p_effective_date
5465 );
5466 --
5467 -- Assigment_id will be NULL on insert, so set to default value.
5468 --
5469 l_assignment_id := nvl(p_assignment_id, hr_api.g_number);
5470 --
5471 -- Check whether another current assignment exists in the same
5472 -- organization with manager flag set to 'Y'.
5473 --
5474 open csr_other_manager_in_org;
5475 fetch csr_other_manager_in_org into l_exists;
5476 --
5477 l_other_manager_exists := csr_other_manager_in_org%found;
5478 --
5479 close csr_other_manager_in_org;
5480 --
5481 hr_utility.set_location(' Leaving:'|| l_proc, 4);
5482 --
5483 return l_other_manager_exists;
5484 end other_managers_in_org;
5485 --
5486 -- ---------------------------------------------------------------------------
5487 -- |-----------------------< gen_date_probation_end >------------------------|
5488 -- ---------------------------------------------------------------------------
5489 --
5490 procedure gen_date_probation_end
5491 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5492 ,p_effective_date in date
5493 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
5494 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
5495 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
5496 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5497 ,p_date_probation_end in out nocopy per_all_assignments_f.date_probation_end%TYPE
5498 )
5499 is
5500 --
5501 l_proc varchar2(72) := g_package||'gen_date_probation_end';
5502 l_api_updating boolean;
5503 --
5504 begin
5505 hr_utility.set_location('Entering:'|| l_proc, 10);
5506 --
5507 -- Check mandatory parameters have been set
5508 --
5509 hr_api.mandatory_arg_error
5510 (p_api_name => l_proc
5511 ,p_argument => 'effective_date'
5512 ,p_argument_value => p_effective_date
5513 );
5514 --
5515 hr_api.mandatory_arg_error
5516 (p_api_name => l_proc
5517 ,p_argument => 'validation_start_date'
5518 ,p_argument_value => p_validation_start_date
5519 );
5520 hr_utility.set_location(l_proc, 20);
5521 --
5522 -- Only proceed with generation if :
5523 -- a) The current g_old_rec is current and
5524 -- b) One or more of the values for date probation end, probation period or
5525 -- probation unit has changed.
5526 --
5527 l_api_updating := per_asg_shd.api_updating
5528 (p_assignment_id => p_assignment_id
5529 ,p_effective_date => p_effective_date
5530 ,p_object_version_number => p_object_version_number
5531 );
5532 hr_utility.set_location(l_proc, 30);
5533 --
5534 if NOT l_api_updating
5535 or
5536 (l_api_updating and
5537 ((nvl(per_asg_shd.g_old_rec.date_probation_end, hr_api.g_date) <>
5538 nvl(p_date_probation_end, hr_api.g_date))
5539 or
5540 (nvl(per_asg_shd.g_old_rec.probation_unit, hr_api.g_varchar2) <>
5541 nvl(p_probation_unit, hr_api.g_varchar2))
5542 or
5543 (nvl(per_asg_shd.g_old_rec.probation_period, hr_api.g_number) <>
5544 nvl(p_probation_period, hr_api.g_number))))
5545 then
5546 hr_utility.set_location(l_proc, 40);
5547 --
5548 -- Check if probation unit and probation period are both not null.
5549 --
5550 if p_probation_unit is not null and p_probation_period is not null then
5551 --
5552 -- Check that probation unit is not 'H'
5553 --
5554 If p_probation_unit <> 'H' then
5555 --
5556 -- Check the value of probation unit and perform the appropriate
5557 -- calculation for date probation end.
5558 --
5559 If p_probation_unit = 'D' then
5560 --
5561 p_date_probation_end := p_validation_start_date
5562 + (p_probation_period-1);
5563 hr_utility.set_location(l_proc, 50);
5564 --
5565 elsif p_probation_unit = 'W' then
5566 --
5567 p_date_probation_end := p_validation_start_date
5568 + ((p_probation_period*7)-1);
5569 hr_utility.set_location(l_proc, 60);
5570 --
5571 elsif p_probation_unit = 'M' then
5572 --
5573 p_date_probation_end := add_months(p_validation_start_date,
5574 p_probation_period)-1;
5575 hr_utility.set_location(l_proc, 70);
5576 --
5577 elsif p_probation_unit = 'Y' then
5578 --
5579 p_date_probation_end := add_months(p_validation_start_date,
5580 12*p_probation_period)-1;
5581 hr_utility.set_location(l_proc, 80);
5582 --
5583 end if;
5584 --
5585 else
5586 --
5587 -- Nullify date probation end
5588 --
5589 p_date_probation_end := null;
5590 hr_utility.set_location(l_proc, 9);
5591 end if;
5592 --
5593 end if;
5594 --
5595 end if;
5596 --
5597 hr_utility.set_location(' Leaving:'|| l_proc, 10);
5598 end gen_date_probation_end;
5599 --
5600 -- ---------------------------------------------------------------------------
5601 -- |---------------------< chk_internal_address_line >-----------------------|
5602 -- ---------------------------------------------------------------------------
5603 --
5604 procedure chk_internal_address_line
5605 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5606 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
5607 ,p_internal_address_line in per_all_assignments_f.internal_address_line%TYPE
5608 ,p_effective_date in date
5609 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5610 )
5611 is
5612 --
5613 l_proc varchar2(72) := g_package||'chk_internal_address_line';
5614 l_api_updating boolean;
5615 --
5616 begin
5617 hr_utility.set_location('Entering:'|| l_proc, 10);
5618 --
5619 -- Check mandatory parameters have been set
5620 --
5621 hr_api.mandatory_arg_error
5622 (p_api_name => l_proc
5623 ,p_argument => 'effective_date'
5624 ,p_argument_value => p_effective_date
5625 );
5626 hr_utility.set_location(l_proc, 20);
5627 --
5628 -- Only proceed with validation if :
5629 -- a) The current g_old_rec is current and
5630 -- b) The value for internal address line has changed
5631 --
5632 l_api_updating := per_asg_shd.api_updating
5633 (p_assignment_id => p_assignment_id
5634 ,p_effective_date => p_effective_date
5635 ,p_object_version_number => p_object_version_number);
5636 hr_utility.set_location(l_proc, 30);
5637 --
5638 if ((l_api_updating and
5639 nvl(per_asg_shd.g_old_rec.internal_address_line, hr_api.g_varchar2)
5640 <> nvl(p_internal_address_line, hr_api.g_varchar2))
5641 or
5642 (NOT l_api_updating)) then
5643 hr_utility.set_location(l_proc, 40);
5644 --
5645 -- Check if internal address line is not null
5646 --
5647 if p_internal_address_line is not null then
5648 --
5649 -- Check that the assignment is an employee, applicant, offer or benefits
5650 -- assignment.
5651 --
5652 if p_assignment_type not in ('E','A','B','C','O') then
5653 --
5654 hr_utility.set_message(801, 'HR_51230_ASG_INV_ASG_TYP_IAL');
5655 hr_utility.raise_error;
5656 --
5657 end if;
5658 hr_utility.set_location(l_proc, 50);
5659 --
5660 end if;
5661 --
5662 end if;
5663 --
5664 hr_utility.set_location(' Leaving:'|| l_proc, 60);
5665 exception
5666 when app_exception.application_exception then
5667 if hr_multi_message.exception_add
5668 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.INTERNAL_ADDRESS_LINE'
5669 ) then
5670 hr_utility.set_location(' Leaving:'|| l_proc, 70);
5671 raise;
5672 end if;
5673 hr_utility.set_location(' Leaving:'|| l_proc, 80);
5674 --
5675 end chk_internal_address_line;
5676 --
5677 --
5678 --
5679 -- ---------------------------------------------------------------------------
5680 -- |-----------------------< chk_applicant_rank >---------------------------|
5681 -- ---------------------------------------------------------------------------
5682 --
5683 procedure chk_applicant_rank
5684 (p_applicant_rank in number
5685 ,p_assignment_type in varchar2
5686 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5687 ,p_effective_date in date
5688 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE)
5689 IS
5690 --
5691 l_proc varchar2(72) := g_package||'chk_applicant_rank';
5692 l_api_updating boolean;
5693 --
5694 begin
5695 hr_utility.set_location('Entering:'|| l_proc, 10);
5696 --
5697 --
5698 l_api_updating := per_asg_shd.api_updating
5699 (p_assignment_id => p_assignment_id
5700 ,p_effective_date => p_effective_date
5701 ,p_object_version_number => p_object_version_number
5702 );
5703 --
5704 if ((l_api_updating and
5705 nvl(per_asg_shd.g_old_rec.applicant_rank, hr_api.g_number) <>
5706 nvl(p_applicant_rank, hr_api.g_number)) or
5707 (NOT l_api_updating)) then
5708
5709 hr_utility.set_location(l_proc, 20);
5710 --
5711 -- Check if applicant_rank is not null
5712 --
5713 if p_applicant_rank IS NOT NULL then
5714 --
5715 hr_utility.set_location(l_proc, 30);
5716 --
5717 -- applicant rank must be between 0 and 100
5718 --
5719 if (p_applicant_rank < 0) or (p_applicant_rank >100) then
5720 --
5721 hr_utility.set_location(l_proc, 40);
5722 --
5723 hr_utility.set_message(800, 'PER_289768_APP_RANKING_INV'); --bug 3303215
5724 hr_utility.raise_error;
5725 --
5726 end if;
5727 --
5728 hr_utility.set_location(l_proc, 50);
5729 --
5730 -- Check that when inserting, the assignment is an applicant or offer assignment
5731 --
5732 if p_assignment_type in ('E','C','B') then
5733 hr_utility.set_location(l_proc, 60);
5734 --
5735 -- Check if the employee assignment is being updated
5736 --
5737 if l_api_updating then
5738 --
5739 -- non applicant, rank can only be updated to null
5740 --
5741 hr_utility.set_message(800, 'HR_289950_APP_RANK_INV_UPD');
5742 hr_utility.raise_error;
5743 --
5744 else -- inserting a non applicant
5745 --
5746 hr_utility.set_message(800, 'HR_289620_APPLICANT_RANK_ASG');
5747 hr_utility.raise_error;
5748 --
5749 end if;
5750 --
5751 end if;
5752 --
5753 end if;
5754 --
5755 end if;
5756 hr_utility.set_location('Leaving:'|| l_proc, 70);
5757 exception
5758 when app_exception.application_exception then
5759 if hr_multi_message.exception_add
5760 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.APPLICANT_RANK'
5761 ) then
5762 raise;
5763 end if;
5764 end chk_applicant_rank;
5765 --
5766 --
5767 -- ---------------------------------------------------------------------------
5768 -- |----------------------< chk_posting_content_id >-------------------------|
5769 -- ---------------------------------------------------------------------------
5770 --
5771 procedure chk_posting_content_id
5772 (p_posting_content_id in number
5773 ,p_assignment_type in varchar2
5774 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
5775 ,p_effective_date in date
5776 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
5777 ) IS
5778 --
5779 l_proc varchar2(72) := g_package||'chk_posting_content_id';
5780 l_api_updating boolean;
5781 l_count number;
5782 l_posting_content_id irc_posting_contents.posting_content_id%type;
5783 --
5784 cursor irc_exists(p_posting_content_id number) is
5785 select posting_content_id
5786 from irc_posting_contents
5787 where posting_content_id = p_posting_content_id
5788 and rownum = 1;
5789 --
5790 begin
5791 --
5792 hr_utility.set_location('Entering:'|| l_proc, 10);
5793 --
5794 l_api_updating := per_asg_shd.api_updating
5795 (p_assignment_id => p_assignment_id
5796 ,p_effective_date => p_effective_date
5797 ,p_object_version_number => p_object_version_number
5798 );
5799 --
5800 if ((l_api_updating and
5801 nvl(per_asg_shd.g_old_rec.posting_content_id, hr_api.g_number) <>
5802 nvl(p_posting_content_id, hr_api.g_number)) or
5803 (NOT l_api_updating)) then
5804
5805 hr_utility.set_location(l_proc, 20);
5806 --
5807 -- Check if posting_content_id is not null
5808 --
5809 if p_posting_content_id IS NOT NULL then
5810 --
5811 -- posting_content_id must exist in irc_posting_contents
5812 --
5813 open irc_exists(p_posting_content_id);
5814 fetch irc_exists into l_posting_content_id;
5815 --
5816 if irc_exists%notfound then
5817 l_posting_content_id := null;
5818 end if;
5819 --
5820 close irc_exists;
5821 hr_utility.set_location(l_proc, 30);
5822 --
5823 if (l_posting_content_id <> p_posting_content_id) then
5824 --
5825 hr_utility.set_message(800, 'HR_289621_INV_POSTING_CONTENT');
5826 hr_utility.raise_error;
5827 --
5828 end if;
5829 --
5830 -- Check that when inserting, the assignment is an applicant or offer assignment
5831 --
5832 if p_assignment_type in ('E','C','B') then
5833 hr_utility.set_location(l_proc, 40);
5834 --
5835 -- Check if the employee assignment is being updated
5836 --
5837 if l_api_updating then
5838 --
5839 -- non applicant/offer, posting_content_id can only be updated to null
5840 --
5841 hr_utility.set_message(800, 'HR_289951_POSTING_CONT_INV_UPD');
5842 hr_utility.raise_error;
5843 --
5844 else -- inserting a non applicant
5845 --
5846 hr_utility.set_message(800, 'HR_289619_POSTING_CONTENT_ASG');
5847 hr_utility.raise_error;
5848 --
5849 end if;
5850 --
5851 end if;
5852 --
5853 end if;
5854 --
5855 end if;
5856 hr_utility.set_location('Leaving: '||l_proc, 50);
5857 exception
5858 when app_exception.application_exception then
5859 if hr_multi_message.exception_add
5860 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.POSTING_CONTENT_ID'
5861 ) then
5862 raise;
5863 end if;
5864 --
5865 end chk_posting_content_id;
5866 --
5867 end per_asg_bus2;
[Home] [Help]