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