1 Package per_asg_bus2 AUTHID CURRENT_USER as
2 /* $Header: peasgrhi.pkh 120.7 2011/03/08 09:20:38 sidsaxen ship $ */
3 --
4 -- --------------------------------------------------------------------------+
5 -- |------------------------< chk_pay_basis_id >-----------------------------|
6 -- --------------------------------------------------------------------------+
7 --
8 -- Description:
9 -- Validates that when pay basis is not null, Pay basis exists in
10 -- PER_PAY_BASES.
11 --
12 -- Validates that the pay basis is in the same business group as the
13 -- assignment being validated.
14 --
15 -- Validates on update that no pay proposals with change dates after the
16 -- validation start date for the assignment exist.
17 --
18 -- Validates that the pay basis is being set for an employee or applicant
19 -- or benefit or offer assignment.
20 --
21 -- Pre-conditions:
22 -- A valid business group
23 -- A valid assignment type
24 --
25 -- In Arguments:
26 -- p_assignment_id
27 -- p_pay_basis_id
28 -- p_assignment_type
29 -- p_business_group_id
30 -- p_effective_date
31 -- p_validation_start_date
32 -- p_object_version_number
33 --
34 -- Post Success:
35 -- Processing continues if:
36 -- - pay basis is not null and exists in PER_PAY_BASES.
37 -- - the pay basis is in the same business group as the assignment
38 -- pay basis.
39 -- - on update, all pay proposals for the assignment have change dates
40 -- equal to or before the validation start date of the assignment.
41 -- - pay basis is set for an employee or applicant or benefit or offer assignment.
42 --
43 -- Post Failure:
44 -- An application error is raised and processing is terminated if:
45 -- - pay basis is not null but does'nt exist in PER_PAY_BASES.
46 -- - the pay basis is in a different business group to the assignment
47 -- pay basis.
48 -- - on update, pay proposals exist for the assignment which have a
49 -- change date after the validation start date for the assignment.
50 -- - pay basis is set for a non employee or applicant or benefit or offer assignment.
51 --
52 -- Access Status:
53 -- Internal Table Handler Use Only.
54 --
55 procedure chk_pay_basis_id
56 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
57 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
58 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
59 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
60 ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
61 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
62 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
63 );
64 --
65 -- --------------------------------------------------------------------------+
66 -- |------------------------< chk_payroll_id >-------------------------------|
67 -- --------------------------------------------------------------------------+
68 --
69 -- Description:
70 -- - Validates that the payroll exists in PAY_PAYROLLS_F and the effective
71 -- start date of the assignment is the same as or after the effective start
72 -- date of the payroll. Also the effective end date of the assignment is
73 -- the same as or before the effective end date of the payroll.
74 --
75 -- - Validates that payroll_id cannot be set for an assignment which is
76 -- linked to an employee whose D.O.B. details have not been recorded.
77 --
78 -- - Validates that the business group of the payroll is the same as that
79 -- of the assignment.
80 --
81 -- - Validates that the assignment is an employee or applicant or benefits
82 -- or offer assignment.
83 --
84 -- - validates that the employee assignment has a primary address if
85 -- payroll is installed. If the address style is US then the address
86 -- must have a county.
87 --
88 -- Pre-conditions:
89 -- A valid Assignment Business Group
90 -- A valid Person
91 -- A valid assignment type.
92 --
93 -- In Arguments:
94 -- p_assignment_id
95 -- p_business_group_id
96 -- p_person_id
97 -- p_assignment_type
98 -- p_payroll_id
99 -- p_validation_start_date
100 -- p_validation_end_date
101 -- p_effective_date
102 -- p_datetrack_mode
103 -- p_object_version_number
104 --
105 -- Post Success:
106 -- Processing continues if :
107 -- - the payroll exists in PAY_PAYROLLS_F where the effective start
108 -- date of the assignment is the same as or after the effective
109 -- start date of the payroll. Also the effective end date of the
110 -- assignment is the same as or before the effective end date of
111 -- the payroll.
112 -- - The business group of the payroll is the same as the
113 -- business group of the assignment.
114 -- - The payroll_id is set and the D.O.B. has been recorded
115 -- for the person (employee only)
116 -- - the assignment is an employee or applicant or benefit or offer assignment.
117 --
118 -- Post Failure:
119 -- An application error is raised and processing ends if:
120 -- - the payroll does'nt exist in PAY_PAYROLLS_F where the effective
121 -- start date of the assignment is the same as or after the
122 -- effective start date of the payroll. Also the effective end date
123 -- of the assignment is the same as or before the effective end
124 -- date of the payroll.
125 -- - The business group of the payroll is invalid
126 -- - The person has no D.O.B recorded (employee only)
127 -- - the assignment is not an employee or applicant or benefit or offer assignment.
128 -- - the employee does not have a primary address.
129 --
130 -- Access Status:
131 -- Internal Table Handler Use Only.
132 --
133 procedure chk_payroll_id
134 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
135 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
136 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
137 ,p_person_id in per_all_assignments_f.person_id%TYPE
138 ,p_payroll_id in per_all_assignments_f.payroll_id%TYPE
139 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
140 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
141 ,p_effective_date in date
142 ,p_datetrack_mode in varchar2
143 ,p_payroll_id_updated out nocopy boolean
144 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
145 );
146 --
147 -- --------------------------------------------------------------------------+
148 -- |----------------------< chk_payroll_id_int >------------------------------|
149 -- --------------------------------------------------------------------------+
150 --
151 procedure chk_payroll_id_int
152 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
153 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
154 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
155 ,p_person_id in per_all_assignments_f.person_id%TYPE
156 ,p_payroll_id in per_all_assignments_f.payroll_id%TYPE
157 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
158 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
159 ,p_effective_date in date
160 ,p_datetrack_mode in varchar2
161 ,p_address_line1 in per_addresses.address_line1%type
162 ,p_date_of_birth in per_all_people_f.date_of_birth%type
163 ,p_payroll_id_updated out nocopy boolean
164 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
165 );
166 --
167 -- --------------------------------------------------------------------------+
168 -- |----------------------< chk_posting_content_id >-------------------------|
169 -- --------------------------------------------------------------------------+
170 --
171 -- Description:
172 -- - Validates that the assignment is an applicant assignment if specified
173 -- - Validates that (if specified) posting_content_id exists in
174 -- irc_posting_contents
175 -- In Arguments:
176 -- p_posting_content_id
177 -- p_assignment_type
178
179 procedure chk_posting_content_id
180 (p_posting_content_id in number
181 ,p_assignment_type in varchar2
182 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
183 ,p_effective_date in date
184 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE);
185
186 --
187 -- --------------------------------------------------------------------------+
188 -- |-------------------------< chk_applicant_rank >--------------------------|
189 -- --------------------------------------------------------------------------+
190 --
191 -- Description:
192 -- - Validates that the assignment is an applicant assignment if specified
193 -- - Validates that (if specified) applicant rank is between 0 and 100 inc.
194 --
195 -- In Arguments:
196 -- p_applicant_rank
197 -- p_assignment_type
198
199 procedure chk_applicant_rank
200 (p_applicant_rank in number
201 ,p_assignment_type in varchar2
202 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
203 ,p_effective_date in date
204 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE);
205
206
207 -- --------------------------------------------------------------------------+
208 -- |-----------------------< chk_people_group_id >---------------------------|
209 -- --------------------------------------------------------------------------+
210 --
211 -- Description:
212 -- - Validates that the people group exists in PAY_PEOPLE_GROUPS.
213 -- - Validates that the id_flex_num value of the people group is the same as
214 -- the people group structure of the assignment business group.
215 -- - Validates that the enabled flag is set to 'Y' for the people group.
216 -- - Validates that the effective start date of the assignment is between
217 -- the start date active and end date active of the people group.
218 --
219 -- Pre-conditions:
220 -- A valid business group
221 -- A valid assignment type
222 -- A valid vacancy
223 --
224 -- In Arguments:
225 -- p_assignment_id
226 -- p_business_group_id
227 -- p_assignment_type
228 -- p_people_group_id
229 -- p_vacancy_id
230 -- p_validation_start_date
231 -- p_validation_end_date
232 -- p_effective_date
233 -- p_object_version_number
234 --
235 -- Post Success:
236 -- Processing continues if :
237 -- - The people group exists in PAY_PEOPLE_GROUPS.
238 -- - The id flex num of the people group is the same as the people group
239 -- structure for the business group of the assignment.
240 -- - enabled flag for the people group is set to 'Y'.
241 -- - the effective start date of the assignment is between start date active
242 -- and end date active of the people group.
243 --
244 -- Post Failure:
245 -- An application error is raised and processing ends if:
246 -- - The people group does'nt exist in PAY_PEOPLE_GROUPS.
247 -- - The id flex num of the people group is different to the people group
248 -- structure for the business group of the assignment.
249 -- - enabled flag for the people group is not set to 'Y'.
250 -- - the effective start date of the assignment is not between start date
251 -- active and end date active of the people group.
252 --
253 -- Access Status:
254 -- Internal Table Handler Use Only.
255 --
256 procedure chk_people_group_id
257 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
258 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
259 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
260 ,p_people_group_id in per_all_assignments_f.people_group_id%TYPE
261 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
262 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
263 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
264 ,p_effective_date in date
265 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
266 );
267 --
268 -- --------------------------------------------------------------------------+
269 -- |-------------------< chk_perf_review_period_freq >-----------------------|
270 -- --------------------------------------------------------------------------+
271 --
272 -- Description:
273 -- - Validates that the performance review period frequency exists as a
274 -- lookup code on HR_LOOKUPS for the lookup type 'FREQUENCY' with an
275 -- enabled flag set to 'Y' and the effective start date of the assignment
276 -- between start date active and end date active on HR_LOOKUPS.
277 --
278 -- - Validates that the performance review period frequency is being set for
279 -- an employee or applicant or benefit or offer assignment.
280 --
281 -- Pre-conditions:
282 -- None
283 --
284 -- In Arguments:
285 -- p_assignment_id
286 -- p_perf_review_period_frequency
287 -- p_assignment_type
288 -- p_effective_date
289 -- p_validation_start_date
290 -- p_validation_end_date
291 -- p_object_version_number
292 --
293 -- Post Success:
294 -- Processing continues if:
295 -- - performance review period frequency is set.
296 -- - performance review period frequency exists as a lookup code
297 -- in HR_LOOKUPS for the lookup type 'FREQUENCY' where the enabled
298 -- flag is 'Y' and the effective start date of the assignment
299 -- is between start date active and end date active on HR_LOOKUPS.
300 -- - performance review period frequency is set for an employee
301 -- or applicant or benefit or offer assignment.
302 --
303 -- Post Failure:
304 -- An application error is raised and processing is terminated if:
305 -- - performance review period frequency does'nt exist as a lookup code
306 -- in HR_LOOKUPS for the lookup type 'FREQUENCY' where the enabled
307 -- flag is 'Y' and the effective start date of the assignment
308 -- is between start date active and end date active on HR_LOOKUPS.
309 -- - performance review period frequency is set for a non employee
310 -- or applicant or benefit or offer assignment.
311 --
312 -- Access Status:
313 -- Internal Table Handler Use Only.
314 --
315 procedure chk_perf_review_period_freq
316 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
320 ,p_validation_start_date in date
317 ,p_perf_review_period_frequency in per_all_assignments_f.perf_review_period_frequency%TYPE
318 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
319 ,p_effective_date in date
321 ,p_validation_end_date in date
322 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
323 );
324 --
325 -- --------------------------------------------------------------------------+
326 -- |-----------------------< chk_perf_review_period >------------------------|
327 -- --------------------------------------------------------------------------+
328 --
329 -- Description:
330 --
331 -- Validates that the perf review period is being set for an employee
332 -- or applicant or benefit or offer assignment.
333 --
334 -- Pre-conditions:
335 -- None
336 --
337 -- In Arguments:
338 -- p_assignment_id
339 -- p_perf_review_period
340 -- p_assignment_type
341 -- p_effective_date
342 -- p_object_version_number
343 --
344 -- Post Success:
345 -- Processing continues if:
346 -- - perf review period is null.
347 -- - perf review period frequency is set for an employee or applicant
348 -- or benefit or offer assignment.
349 --
350 -- Post Failure:
351 -- An application error is raised and processing is terminated if:
352 -- - perf review period frequency is set for a non employee or applicant
353 -- or benefit or offer assignment.
354 --
355 -- Access Status:
356 -- Internal Table Handler Use Only.
357 --
358 procedure chk_perf_review_period
359 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
360 ,p_perf_review_period in per_all_assignments_f.perf_review_period%TYPE
361 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
362 ,p_effective_date in date
363 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
364 );
365 --
366 -- --------------------------------------------------------------------------+
367 -- |-------------------< chk_perf_rp_freq_perf_rp >--------------------------|
368 -- --------------------------------------------------------------------------+
369 --
370 -- Description:
371 -- Validates that when perf review period frequency is not null then
372 -- perf review period is also not null,
373 --
374 -- Pre-conditions:
375 -- Valid perf review period frequency
376 -- Valid perf review period
377 --
378 -- In Arguments:
379 -- p_assignment_id
380 -- p_perf_review_period_frequency
381 -- p_perf_review_period
382 -- p_effective_date
383 -- p_object_version_number
384 --
385 -- Post Success:
386 -- Processing continues if:
387 -- - perf review period frequency and perf review period are both null.
388 -- - perf review period frequency and perf review period are both not
389 -- null.
390 --
391 -- Post Failure:
392 -- An application error is raised and processing is terminated if:
393 -- - perf review period frequency or perf review period are not null.
394 --
395 -- Access Status:
396 -- Internal Table Handler Use Only.
397 --
398 procedure chk_perf_rp_freq_perf_rp
399 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
400 ,p_perf_review_period_frequency in per_all_assignments_f.perf_review_period_frequency%TYPE
401 ,p_perf_review_period in per_all_assignments_f.perf_review_period%TYPE
402 ,p_effective_date in date
403 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
404 );
405 --
406 -- --------------------------------------------------------------------------+
407 -- |---------------------< chk_period_of_service_id >------------------------|
408 -- --------------------------------------------------------------------------+
409 --
410 -- Description:
411 --
412 -- - Validates that the assignment is an employee assignment.
413 -- - Validates that the period of service is set for an employee assignment.
414 -- - Validates that the period of service exists in PER_PERIODS_OF_SERVICE
415 -- between the period of service date start and actual termination date.
416 -- - Validates that the business_group_id of the Assignment is the same as
417 -- that of the period of service.
418 -- - Validates that the effective start date of the assignment is between
419 -- the date start and actual termination date of the period of service.
420 --
421 -- Pre-conditions:
422 -- A valid Person
423 -- A valid assignment type
424 -- A Valid business group
425 --
426 -- In Arguments:
427 -- p_assignment_id
428 -- p_business_group_id
429 -- p_person_id
430 -- p_assignment_type
431 -- p_period_of_service_id
432 -- p_effective_date
433 -- p_validation_start_date
434 -- p_validation_end_date
435 -- p_object_version_number
436 --
437 -- Post Success:
438 -- Processing continues if:
439 -- - the assignment is an employee assignment.
440 -- - period of service is set for an employee assignment.
441 -- - period of service exists in PER_PERIODS_OF_SERVICE between
445 -- - the effective start date of the assignment is between date start
442 -- date start and actual termination date.
443 -- - the period of service is in the same business group as the
444 -- assignment business group.
446 -- and actual termination date of the period of service.
447 --
448 -- Post Failure:
449 -- An application error is raised and processing ends if:
450 -- - the assignment is not an employee assignment.
451 -- - period of service is not set for a employee assignment.
452 -- - period of service does'nt exist in PER_PERIODS_OF_SERVICE between
453 -- date start and actual termination date.
454 -- - the period of service is in a different business group to
455 -- the assignment business group.
456 -- - the effective start date of the assignment is not between date
457 -- start and actual termination date of the period of service.
458 --
459 -- Access Status:
460 -- Internal Table Handler Use Only.
461 --
462 procedure chk_period_of_service_id
463 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
464 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
465 ,p_person_id in per_all_assignments_f.person_id%TYPE
466 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
467 ,p_period_of_service_id in per_all_assignments_f.period_of_service_id%TYPE
468 ,p_validation_start_date in date
469 ,p_validation_end_date in date
470 ,p_effective_date in date
471 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
472 );
473 --
474 -- --------------------------------------------------------------------------+
475 -- |--------------------------< chk_person_id >------------------------------|
476 -- --------------------------------------------------------------------------+
477 --
478 -- Description:
479 -- Validates that the business group of the person is the same as
480 -- the business group of the assignment.
481 --
482 -- Pre-conditions:
483 -- A valid Assignment Business Group
484 --
485 -- In Arguments:
486 -- p_person_id
487 -- p_business_group_id
488 --
489 -- Post Success:
490 -- Processing continues if :
491 -- - The business group validation succeeds
492 --
493 -- Post Failure:
494 -- An application error is raised and processing is terminated if any
495 -- of the following cases are found :
496 -- - The business group validation fails
497 --
498 -- Access status:
499 -- Internal Table Handler Use Only.
500 --
501 -- 70.2 change b start.
502 --
503 procedure chk_person_id
504 (p_person_id in per_all_assignments_f.person_id%TYPE
505 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
506 ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
507 );
508 --
509 -- --------------------------------------------------------------------------+
510 -- |---------------------< chk_person_referred_by_id >-----------------------|
511 -- --------------------------------------------------------------------------+
512 --
513 -- Description:
514 -- - Validates that the assignment is an applicant or offer assignment.
515 -- - Validates that the person referred by exists in PER_PEOPLE_F and
516 -- the effective start date of the assignment is between the effective
517 -- start date and effective end date of the person referred by.
518 -- - Validates that the person referred by is in the same business group
519 -- as the assignment.
520 -- - Validates that the person referred by is not the same as the person
521 -- of the assignment..
522 -- - Validates that the person referred by is an employee.
523 --
524 -- Pre-conditions:
525 -- A valid assignment type
526 -- A Valid business group
527 -- A valid person
528 --
529 -- In Arguments:
530 -- p_person_referred_by_id
531 -- p_assignment_type
532 -- p_business_group_id
533 -- p_person_id
534 -- p_validation_start_date
535 -- p_validation_end_date
536 --
537 -- Post Success:
538 -- Processing continues if:
539 -- - the assignment is an applicant or offer assignment.
540 -- - the person referred by exists in PER_PEOPLE_F where the effective
541 -- start date of the assignment is between the effective start
542 -- date and effective end date of the person referred by.
543 -- - the person referred by is in the same business group as the
544 -- assignment business group.
545 -- - the person referred by is not the person of the assignment.
546 -- - the current employee flag of the person referred by in
547 -- PER_PEOPLE_F is set to 'Y'.
548 --
549 -- Post Failure:
550 -- An application error is raised and processing ends if:
551 -- - the assignment is not an applicant or offer assignment.
552 -- - the person referred by does'nt exist in PER_PEOPLE_F where the
553 -- effective start date of the assignment is between the effective
554 -- start date and effective end date of the person referred by.
555 -- - the person referred by is in a different business group to
556 -- the assignment business group.
557 -- - the person referred by is the person of the assignment.
558 -- - the current employee flag of the person referred by in
562 -- Internal Table Handler Use Only.
559 -- PER_PEOPLE_F is not set to 'Y'.
560 --
561 -- Access Status:
563 --
564 procedure chk_person_referred_by_id
565 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
566 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
567 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
568 ,p_person_id in per_all_assignments_f.person_id%TYPE
569 ,p_person_referred_by_id in per_all_assignments_f.person_referred_by_id%TYPE
570 ,p_effective_date in date
571 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
572 ,p_validation_start_date in date
573 ,p_validation_end_date in date
574 );
575 --
576 -- --------------------------------------------------------------------------+
577 -- |------------------------< chk_position_id >------------------------------|
578 -- --------------------------------------------------------------------------+
579 --
580 -- Description:
581 -- - Validates that the position_id exists in per_positions date
582 -- effectively.
583 -- - Validates that the business_group_id in per_positions matches
584 -- the assignment business group date effectively.
585 --
586 -- Pre-conditions:
587 -- A valid business_group_id
588 -- A valid assignment type
589 -- A valid vacancy_id
590 --
591 -- In Arguments:
592 -- p_assignment_id
593 -- p_position_id
594 -- p_business_group_id
595 -- p_assignment_type
596 -- p_vacancy_id
597 -- p_validation_start_date
598 -- p_validation_end_date
599 -- p_effective_date
600 -- p_object_version_number
601 --
602 -- Post Success:
603 -- Processing continues if :
604 -- - The position exists and is date effective in per_positions.
605 -- - The business group of the position is the same as the assignment's
606 -- business group date effectively.
607 --
608 -- Post Failure:
609 -- An application error is raised and processing is terminated if any of
610 -- the following cases are found :
611 -- - The position_id does not exist or is not date effective in
612 -- per_positions.
613 -- - The business_group_id in per_positions does not match the assignment
614 -- business_group_id or is not date effective.
615 --
616 -- Access Status:
617 -- Internal Table Handler Use Only
618 --
619 procedure chk_position_id
620 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
621 ,p_position_id in per_all_assignments_f.position_id%TYPE
622 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
623 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
624 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
625 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
626 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
627 ,p_effective_date in date
628 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
629 );
630 -----------------------------------------------------------------------------+
631 -------------------< chk_position_id_grade_id >------------------------------+
632 -----------------------------------------------------------------------------+
633 --
634 -- Description:
635 -- Validates that the position_id and grade_id combination in
636 -- per_valid_grades matches the combination for the assignment.
637 --
638 -- Pre-conditions:
639 -- A valid position_id
640 -- A valid grade_id
641 --
642 -- In Arguments:
643 -- p_assignment_id
644 -- p_position_id
645 -- p_grade_id
646 -- p_validation_start_date
647 -- p_validation_end_date
648 -- p_effective_date
649 -- p_object_version_number
650 --
651 -- Out Arguments:
652 -- p_inv_pos_grade_warning
653 --
654 -- Post Success:
655 -- Processing continues if :
656 -- - The position_id and grade_id combination in per_valid_grades
657 -- matches the corresponding combination for the assignment date
658 -- effectively.
659 --
660 -- Post Failure:
661 -- A flag (p_inv_pos_grade_warning) is set to true when the position_id and
662 -- grade_id combination do not match with a combination in per_valid_grades.
663 -- This flag is set to false when the combination does exist. The flag will
664 -- always be false when position_id and grade_id are not modified in this
665 -- transaction.
666 --
667 -- Access Status:
668 -- Internal Table Handler Use Only
669 --
670 procedure chk_position_id_grade_id
671 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
672 ,p_position_id in per_all_assignments_f.position_id%TYPE
673 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
674 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
675 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
676 ,p_effective_date in date
677 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
678 ,p_inv_pos_grade_warning out nocopy boolean
679 );
680 -----------------------------------------------------------------------------+
684 -- Description:
681 --------------------------< chk_position_id_org_id >-------------------------+
682 -----------------------------------------------------------------------------+
683 --
685 -- - Validates that the position and organization combination in
686 -- per_positions matches the combination for the assignment.
687 --
688 -- Pre-conditions:
689 -- A valid position_id
690 -- A valid organization_id
691 --
692 -- In Arguments:
693 -- p_assignment_id
694 -- p_position_id
695 -- p_organization_id
696 -- p_validation_start_date
697 -- p_validation_end_date
698 -- p_effective_date
699 -- p_object_version_number
700 --
701 -- Post Success:
702 -- Processing continues if :
703 -- - The assignment's position and organization combination matches the
704 -- same combination for the position in PER_POSITIONS date effectively.
705 --
706 -- Post Failure:
707 -- An application error is raised and processing is terminated if any of
708 -- the following cases are found :
709 -- - The assignment's position and organization combination does not
710 -- match the combination in PER_POSITIONS date effectively.
711 --
712 -- Access Status:
713 -- Internal Table Handler Use Only
714 procedure chk_position_id_org_id
715 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
716 ,p_position_id in per_all_assignments_f.position_id%TYPE
717 ,p_organization_id in per_all_assignments_f.organization_id%TYPE
718 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
719 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
720 ,p_effective_date in date
721 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
722 );
723 -----------------------------------------------------------------------------+
724 -------------------------< chk_position_id_job_id >--------------------------+
725 -----------------------------------------------------------------------------+
726 --
727 -- Description:
728 -- - Validates that the job for the assignment's job and position
729 -- combination is not null.
730 -- - Validates that the position and job combination in
731 -- per_positions matches the combination for the assignment.
732 --
733 -- Pre-conditions:
734 -- A valid position_id
735 -- A valid job_id
736 --
737 -- In Arguments:
738 -- p_assignment_id
739 -- p_position_id
740 -- p_job_id
741 -- p_validation_start_date
742 -- p_validation_end_date
743 -- p_effective_date
744 -- p_object_version_number
745 --
746 -- Post Success:
747 -- Processing continues if :
748 -- - The job of the assignment position and job combination is not null.
749 -- - The assignment's position and job combination matches the
750 -- same combination for the position in PER_POSITIONS date effectively.
751 --
752 -- Post Failure:
753 -- An application error is raised and processing is terminated if any of
754 -- the following cases are found :
755 -- - The job of the assignment position and job combination is
756 -- null.
757 -- null.
758 -- - The assignment's position and job combination does not
759 -- match the combination in PER_POSITIONS date effectively.
760 --
761 -- Access Status:
762 -- Internal Table Handler Use Only
763 --
764 procedure chk_position_id_job_id
765 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
766 ,p_position_id in per_all_assignments_f.position_id%TYPE
767 ,p_job_id in per_all_assignments_f.job_id%TYPE
768 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
769 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
770 ,p_effective_date in date
771 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
772 );
773 --
774 -- --------------------------------------------------------------------------+
775 -- |-------------------------< chk_primary_flag >----------------------------|
776 -- --------------------------------------------------------------------------+
777 --
778 -- Description:
779 -- - Validates that primary flag is set to either 'Y' or 'N'.
780 -- - Validates that there is only one occurrence of an assignment where
781 -- the primary flag is set to 'Y' for a given person for a given
782 -- period of service.
783 --
784 -- - Validates that on insert of a non primary employee assignment, a primary
785 -- employee assignment must exist for the entire date range of the non primary.
786 --
787 -- - Validates that for applicant and offer assignments the primary flag is not set
788 -- to 'Y'.
789 --
790 -- - Validates that on insert of primary assignments that the effective end date
791 -- of the assignment is the end of time.
792 --
793 -- Pre-conditions:
794 -- A valid Person ID
795 -- A valid Period of Service ID
796 -- A valid Assignment Type
797 --
798 -- In Arguments:
799 -- p_primary_flag
800 -- p_assignment_type
801 -- p_person_id
802 -- p_period_of_service_id
803 -- p_validation_start_date
804 -- p_validation_end_date
805 --
806 -- Post Success:
807 -- Processing continues if:
808 -- - primary flag is either 'Y' or 'N'.
812 -- - For an applicant or offer assignment the primary flag is not set to 'Y'.
809 -- - No other primary assignment exists on insert of a primary.
810 -- - A primary employee assignment exists for the same date range as
811 -- the non-primary employee assignment to be inserted.
813 -- - On insert of primary assignments, the effective start date of
814 -- the assignment is the end of time.
815 --
816 -- Post Failure:
817 -- An application error is raised and processing ends if:
818 -- - primary flag is either 'Y' or 'N'.
819 -- - On insert of a primary assignment, another primary already
820 -- exists.
821 -- - A primary employee assignment does'nt exist for the same date
822 -- range as the non-primary employee assignment to be inserted.
823 -- - For an applicant or offer assignment the primary flag is set to 'Y'.
824 -- - On insert of primary assignments, the effective start date of
825 -- the assignment is not the end of time.
826 --
827 -- Access Status:
828 -- Internal Table Handler Use Only.
829 --
830 procedure chk_primary_flag
831 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
832 ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
833 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
834 ,p_person_id in per_all_assignments_f.person_id%TYPE
835 ,p_period_of_service_id in per_all_assignments_f.period_of_service_id%TYPE
836 ,p_pop_date_start in DATE
837 ,p_effective_date in date
838 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
839 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
840 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
841 );
842 --
843 -- --------------------------------------------------------------------------+
844 -- |----------------------< chk_probation_period >---------------------------|
845 -- --------------------------------------------------------------------------+
846 --
847 -- Description:
848 -- Validates that probation period is in the range of 0 to 9999.99.
849 --
850 -- Pre-conditions:
851 -- None
852 --
853 -- In Arguments:
854 -- p_assignment_id
855 -- p_probation_period
856 -- p_effective_date
857 -- p_object_version_number
858 --
859 -- Post Success:
860 -- Processing continues if:
861 -- - probation period is null.
862 -- - probation period is not null and within the range 0 to 9999.99
863 --
864 -- Post Failure:
865 -- An application error is raised and processing is terminated if:
866 -- - probation period is outside of the range 0 to 9999.99
867 --
868 -- Access Status:
869 -- Internal Table Handler Use Only.
870 --
871 procedure chk_probation_period
872 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
873 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
874 ,p_effective_date in date
875 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
876 );
877 --
878 -- --------------------------------------------------------------------------+
879 -- |------------------------< chk_probation_unit >---------------------------|
880 -- --------------------------------------------------------------------------+
881 --
882 -- Description:
883 -- - Validates that the probation unit exists as a lookup code on HR_LOOKUPS
884 -- for the lookup type 'QUALIFYING_UNITS' with an enabled flag set to 'Y'
885 -- and the effective start date of the assignment between start date active
886 -- and end date active on HR_LOOKUPS.
887 --
888 -- Pre-conditions:
889 -- None
890 --
891 -- In Arguments:
892 -- p_assignment_id
893 -- p_probation_unit
894 -- p_effective_date
895 -- p_validation_start_date
896 -- p_validation_end_date
897 -- p_object_version_number
898 --
899 -- Post Success:
900 -- Processing continues if:
901 -- - probation unit is null.
902 -- - probation unit exists as a lookup code in HR_LOOKUPS for
903 -- the lookup type 'QUALIFYING_UNITS' where the enabled flag is
904 -- 'Y' and the effective start date of the assignment is between
905 -- start date active and end date active on HR_LOOKUPS.
906 --
907 -- Post Failure:
908 -- An application error is raised and processing is terminated if:
909 -- - probation unit does'nt exist as a lookup code in HR_LOOKUPS for
910 -- the lookup type 'QUALIFYING_UNITS' where the enabled flag is
911 -- 'Y' and the effective start date of the assignment is between
912 -- start date active and end date active on HR_LOOKUPS.
913 --
914 procedure chk_probation_unit
915 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
916 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
917 ,p_effective_date in date
918 ,p_validation_start_date in date
919 ,p_validation_end_date in date
920 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
921 );
922 --
923 -- --------------------------------------------------------------------------+
924 -- |-------------------< chk_prob_unit_prob_period >-------------------------|
928 -- Validates that when probation unit is not null then probation period is
925 -- --------------------------------------------------------------------------+
926 --
927 -- Description:
929 -- also not null,
930 --
931 -- Pre-conditions:
932 -- Valid probation unit
933 -- Valid probation period
934 --
935 -- In Arguments:
936 -- p_assignment_id
937 -- p_probation_unit
938 -- p_probation_period
939 -- p_effective_date
940 -- p_object_version_number
941 --
942 -- Post Success:
943 -- Processing continues if:
944 -- - Probation unit and probation period are both not null.
945 --
946 -- Post Failure:
947 -- An application error is raised and processing is terminated if:
948 -- - Probation unit or probation period are not null.
949 --
950 -- Access Status:
951 -- Internal Table Handler Use Only.
952 --
953 procedure chk_prob_unit_prob_period
954 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
955 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
956 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
957 ,p_effective_date in date
958 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
959 );
960 --
961 -- --------------------------------------------------------------------------+
962 -- |------------------------< chk_recruiter_id >-----------------------------|
963 -- --------------------------------------------------------------------------+
964 --
965 -- Description:
966 -- - Validates that the assignment is an applicant or offer assignment.
967 -- - Validates that the recruiter is not the same as the person.
968 -- - Validates that the recruiter exists in PER_PEOPLE_F
969 -- between the effective start date and effective end date
970 -- of the assignment.
971 -- - Validates that the recruiter is an employee.
972 -- - Validates that the recruiter is in the same business group
973 -- as the applicant assignment.
974 --
975 -- Pre-conditions:
976 -- A valid person
977 -- A valid assignment type
978 -- A valid business group
979 -- A valid vacancy
980 --
981 -- In Arguments:
982 -- p_assignment_id
983 -- p_person_id
984 -- p_assignment_type
985 -- p_business_group_id
986 -- p_recruiter_id
987 -- p_vacancy_id
988 -- p_effective_date
989 -- p_validation_start_date
990 -- p_validation_end_date
991 --
992 -- Post Success:
993 -- Processing continues if:
994 -- - the assignment is an applicant, employee or offer assignment.
995 -- - the recruiter is not the person.
996 -- - the recruiter exists in PER_PEOPLE_F between the
997 -- effective start date and effective end date of the
998 -- assignment.
999 -- - the CURRENT_EMPLOYEE_FLAG of the recruiter in PER_PEOPLE_F is
1000 -- set to 'Y'
1001 -- - the recruiter is in the same business group as the assignment
1002 -- business group.
1003 --
1004 -- Post Failure:
1005 -- An application error is raised and processing ends if:
1006 -- - the assignment is not an applicant or offer or employee assignment.
1007 -- - the recruiter is the person.
1008 -- - the recruiter does'nt exist in PER_PEOPLE_F between the
1009 -- effective start date and effective end date of the
1010 -- assignment.
1011 -- - the CURRENT_EMPLOYEE_FLAG of the recruiter in PER_PEOPLE_F is
1012 -- not set to 'Y'
1013 -- - the recruiter is in a different business group to the assignment
1014 -- business group.
1015 --
1016 -- Access Status:
1017 -- Internal Table Handler Use Only.
1018 --
1019 procedure chk_recruiter_id
1020 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1021 ,p_person_id in per_all_assignments_f.person_id%TYPE
1022 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1023 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1024 ,p_recruiter_id in per_all_assignments_f.recruiter_id%TYPE
1025 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1026 ,p_effective_date in date
1027 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1028 ,p_validation_start_date in date
1029 ,p_validation_end_date in date
1030 );
1031 --
1032 -- --------------------------------------------------------------------------+
1033 -- |--------------------< chk_recruitment_activity_id >----------------------|
1034 -- --------------------------------------------------------------------------+
1035 --
1036 -- Description:
1037 -- - Validates that the assignment is an applicant or offer assignment.
1038 -- - Validates that the recruitment activity exists in
1039 -- PER_RECRUITMENT_ACTIVITIES and the effective start date of the
1040 -- assignment is between the date start and date end of the recruitment
1041 -- activity.
1042 -- - Validates that the recruitment activity is in the same business group
1043 -- as the assignment.
1044 --
1045 -- Pre-conditions:
1046 -- A valid assignment type
1047 -- A Valid business group
1048 --
1049 -- In Arguments:
1050 -- p_recruitment_activity_id
1054 -- p_validation_end_date
1051 -- p_assignment_type
1052 -- p_business_group_id
1053 -- p_validation_start_date
1055 --
1056 -- Post Success:
1057 -- Processing continues if:
1058 -- - the assignment is an applicant or offer assignment.
1059 -- - the recruitment activity exists in PER_RECRUITMENT_ACTIVITIES
1060 -- where the effective start date of the assignment is between the
1061 -- date start and date end of the recruitment activity.
1062 -- - the recruitment activity is in the same business group as the
1063 -- assignment business group.
1064 --
1065 -- Post Failure:
1066 -- An application error is raised and processing ends if:
1067 -- - the assignment is not an applicant or offer assignment.
1068 -- - the recruitment activity does'nt exist in PER_RECRUITMENT_ACTIVITIES
1069 -- where the effective start date of the assignment is between the
1070 -- date start and date end of the recruitment activity.
1071 -- - the recruitment activity is in a different business group to
1072 -- the assignment business group.
1073 --
1074 -- Access Status:
1075 -- Internal Table Handler Use Only.
1076 --
1077 procedure chk_recruitment_activity_id
1078 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1079 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1080 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1081 ,p_recruitment_activity_id in per_all_assignments_f.recruitment_activity_id%TYPE
1082 ,p_effective_date in date
1083 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1084 ,p_validation_start_date in date
1085 ,p_validation_end_date in date
1086 );
1087 --
1088 -- --------------------------------------------------------------------------+
1089 -- |-------------------------< chk_ref_int_del >-----------------------------|
1090 -- --------------------------------------------------------------------------+
1091 --
1092 -- Description:
1093 -- Validates that an assignment cannot be purged if foreign key
1094 -- references exist to any of the following tables :
1095 --
1096 -- - PER_EVENTS
1097 -- - PER_LETTER_REQUEST_LINES
1098 -- - PAY_COST_ALLOCATIONS_F
1099 -- - PAY_PAYROLL_ACTIONS
1100 -- - PAY_PERSONAL_PAYMENT_METHODS_F
1101 -- - PAY_ASSIGNMENT_ACTIONS
1102 -- - PER_COBRA_COV_ENROLLMENTS
1103 -- - PER_COBRA_COVERAGE_BENEFITS_F
1104 -- - PER_ASSIGNMENTS_EXTRA_INFO
1105 -- - HR_ASSIGNMENT_SET_AMENDMENTS
1106 -- - PER_SECONDARY_ASS_STATUSES
1107 --
1108 -- Pre-conditions:
1109 -- None
1110 --
1111 -- In Arguments:
1112 -- p_assignment_id
1113 -- p_validation_start_date
1114 -- p_validation_end_date
1115 -- p_datetrack_mode
1116 --
1117 -- Post Success:
1118 -- If no child rows exist in the table listed above then processing
1119 -- continues.
1120 --
1121 -- Post Failure:
1122 -- If child rows exist in any of the tables listed above, an application
1123 -- error is raised and processing is terminated.
1124 --
1125 procedure chk_ref_int_del
1126 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1127 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1128 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1129 ,p_datetrack_mode in varchar2
1130 );
1131 --
1132 -- --------------------------------------------------------------------------+
1133 -- |---------------------< chk_sal_review_period_freq >----------------------|
1134 -- --------------------------------------------------------------------------+
1135 --
1136 -- Description:
1137 -- - Validates that the salary review period frequency exists as a lookup
1138 -- code on HR_LOOKUPS for the lookup type 'FREQUENCY' with an enabled
1139 -- flag set to 'Y' and the effective start date of the assignment between
1140 -- start date active and end date active on HR_LOOKUPS.
1141 --
1142 -- Pre-conditions:
1143 -- None
1144 --
1145 -- In Arguments:
1146 -- p_assignment_id
1147 -- p_sal_review_period_frequency
1148 -- p_assignment_type
1149 -- p_effective_date
1150 -- p_validation_start_date
1151 -- p_validation_end_date
1152 -- p_object_version_number
1153 --
1154 -- Post Success:
1155 -- Processing continues if:
1156 -- - sal review period frequency is null.
1157 -- - salary review period frequency exists as a lookup code in
1158 -- HR_LOOKUPS for the lookup type 'FREQUENCY' where the enabled flag
1159 -- is 'Y' and the effective start date of the assignment is between
1160 -- start date active and end date active on HR_LOOKUPS.
1161 -- - sal review period frequency is set for an employee or applicant
1162 -- or benefit or offer assignment.
1163 --
1164 -- Post Failure:
1165 -- An application error is raised and processing is terminated if:
1166 -- - salary review period frequency does'nt exist as a lookup code in
1167 -- HR_LOOKUPS for the lookup type 'FREQUENCY' where the enabled flag
1168 -- is 'Y' and the effective start date of the assignment is between
1169 -- start date active and end date active on HR_LOOKUPS.
1170 -- - sal review period frequency is set for a non employee or applicant
1174 -- Internal Table Handler Use Only.
1171 -- or benefit or offer assignment.
1172 --
1173 -- Access Status:
1175 --
1176 procedure chk_sal_review_period_freq
1177 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1178 ,p_sal_review_period_frequency in
1179 per_all_assignments_f.sal_review_period_frequency%TYPE
1180 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1181 ,p_effective_date in date
1182 ,p_validation_start_date in date
1183 ,p_validation_end_date in date
1184 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1185 );
1186 --
1187 -- --------------------------------------------------------------------------+
1188 -- |-----------------------< chk_sal_review_period >------------------------|
1189 -- --------------------------------------------------------------------------+
1190 --
1191 -- Description:
1192 --
1193 -- Validates that the sal review period is being set for an employee
1194 -- or applicant or benefit or offer assignment.
1195 --
1196 -- Pre-conditions:
1197 -- None
1198 --
1199 -- In Arguments:
1200 -- p_assignment_id
1201 -- p_sal_review_period
1202 -- p_assignment_type
1203 -- p_effective_date
1204 -- p_object_version_number
1205 --
1206 -- Post Success:
1207 -- Processing continues if:
1208 -- - sal review period is null.
1209 -- - sal review period frequency is set for an employee or applicant
1210 -- or benefit or offer assignment.
1211 --
1212 -- Post Failure:
1213 -- An application error is raised and processing is terminated if:
1214 -- - sal review period frequency is set for a non employee or applicant
1215 -- or benefit or offer assignment.
1216 --
1217 -- Access Status:
1218 -- Internal Table Handler Use Only.
1219 --
1220 procedure chk_sal_review_period
1221 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1222 ,p_sal_review_period in per_all_assignments_f.sal_review_period%TYPE
1223 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1224 ,p_effective_date in date
1225 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1226 );
1227 --
1228 -- --------------------------------------------------------------------------+
1229 -- |---------------------< chk_sal_rp_freq_sal_rp >--------------------------|
1230 -- --------------------------------------------------------------------------+
1231 --
1232 -- Description:
1233 -- Validates that when sal review period frequency is not null then
1234 -- sal review period is also not null,
1235 --
1236 -- Pre-conditions:
1237 -- Valid sal review period frequency
1238 -- Valid sal review period
1239 --
1240 -- In Arguments:
1241 -- p_assignment_id
1242 -- p_sal_review_period_frequency
1243 -- p_sal_review_period
1244 -- p_effective_date
1245 -- p_object_version_number
1246 --
1247 -- Post Success:
1248 -- Processing continues if:
1249 -- - sal review period frequency and sal review period are both null.
1250 -- - sal review period frequency and sal review period are both not
1251 -- null.
1252 --
1253 -- Post Failure:
1254 -- An application error is raised and processing is terminated if:
1255 -- - sal review period frequency or sal review period are not null.
1256 --
1257 -- Access Status:
1258 -- Internal Table Handler Use Only.
1259 --
1260 procedure chk_sal_rp_freq_sal_rp
1261 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1262 ,p_sal_review_period_frequency in per_all_assignments_f.sal_review_period_frequency%TYPE
1263 ,p_sal_review_period in per_all_assignments_f.sal_review_period%TYPE
1264 ,p_effective_date in date
1265 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1266 );
1267 --
1268 -- --------------------------------------------------------------------------+
1269 -- |-----------------------< chk_set_of_books_id >---------------------------|
1270 -- --------------------------------------------------------------------------+
1271 --
1272 -- Description:
1273 -- - Validates that on insert when set of books is not null, set of books
1274 -- exists in GL_SETS_OF_BOOKS.
1275 -- - Validates when set of books is set that it exists in
1276 -- FINANCIALS_SYSTEM_PARAMS_ALL in the same business group as the
1277 -- assignment business group.
1278 --
1279 -- Validates that set of books cannot be updated for the assignment.
1280 --
1281 -- Validates that the set of books is being set for an employee
1282 -- or applicant or offer assignment.
1283 --
1284 -- Pre-conditions:
1285 -- A Valid business group
1286 --
1287 -- In Arguments:
1288 -- p_assignment_id
1289 -- p_assignment_type
1290 -- p_business_group_id
1291 -- p_set_of_books_id
1292 -- p_effective_date
1293 -- p_object_version_number
1294 --
1295 -- Post Success:
1296 -- Processing continues if:
1297 -- - set of books is null.
1298 -- - set of books is not null and exists in GL_SETS_OF_BOOKS.
1299 -- - set of books is set for an employee or applicant or
1300 -- offer assignment.
1304 --
1301 -- - set of books exists in FINANCIALS_SYSTEM_PARAMS_ALL and the
1302 -- business group for the assignment is the same as the business
1303 -- group for the financial system parameter.
1305 -- Post Failure:
1306 -- An application error is raised and processing is terminated if:
1307 -- - set of books is not null and does'nt exist in GL_SETS_OF_BOOKS.
1308 -- - set of books is set for a non employee or non applicant or
1309 -- non offer assignment.
1310 -- - set of books exists in FINANCIALS_SYSTEM_PARAMS_ALL and the
1311 -- business group for the assignment is different to the business
1312 -- group for the financial system parameter.
1313 --
1314 -- Access Status:
1315 -- Internal Table Handler Use Only
1316 --
1317 procedure chk_set_of_books_id
1318 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1319 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1320 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1321 ,p_set_of_books_id in per_all_assignments_f.set_of_books_id%TYPE
1322 ,p_effective_date in date
1323 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1324 );
1325 --
1326 -- --------------------------------------------------------------------------+
1327 -- |--------------------< chk_soft_coding_keyflex_id >-----------------------|
1328 -- --------------------------------------------------------------------------+
1329 --
1330 -- Description:
1331 -- - Validates that the assignment is an employee assignment.
1332 -- - Validates that the soft coding keyflex exists on
1333 -- hr_soft_coding_keyflex.
1334 -- - Validates that the enabled flag is set to 'Y' for the soft coding
1335 -- keyflex.
1336 -- - Validates that the effective start date of the assignment is between
1337 -- the start date active and end date active of the soft coding keyflex.
1338 -- - Refer to the chk_scl_segments procedure for further soft_coding_keyflex
1339 -- validation.
1340 -- - Where payroll_id is not null, population of soft_coded_key_flex_id is
1341 -- mandatory legislation code is 'US' or a rule exists in pay_legislation_rules
1342 --
1343 -- Pre-conditions:
1344 -- A valid business group.
1345 -- A valid assignment type.
1346 --
1347 -- In Arguments:
1348 -- p_assignment_id
1349 -- p_business_group_id
1350 -- p_assignment_type
1351 -- p_soft_coding_keyflex_id
1352 -- p_effective_date
1353 -- p_validation_start_date
1354 -- p_object_version_number
1355 -- p_payroll_id
1356 -- p_business_group_id
1357 --
1358 -- Post Success:
1359 -- Processing continues if:
1360 -- - the soft coding keyflex is not set.
1361 -- - the soft coding keyflex is set for an employee or applicant
1362 -- assignment.
1363 -- - the soft coding keyflex is set and exists in
1364 -- hr_soft_coding_keyflex table.
1365 -- - the enabled flag for the soft coding keyflex is set to 'Y'.
1366 -- - the effective start date of the assignment is between start
1367 -- date active and end date active of the soft coding keyflex.
1368 --
1369 -- Post Failure:
1370 -- An application error is raised and processing ends if:
1371 -- - the soft coding keyflex is not set for an employee or applicant
1372 -- assignment.
1373 -- - the soft coding keyflex_id is set and does not exist in
1374 -- hr_soft_coding_keyflex.
1375 -- - the enabled flag for the soft coding keyflex is not set to 'Y'.
1376 -- - the effective start date of the assignment is not between start
1377 -- date active and end date active of the soft coding keyflex.
1378 --
1379 -- Access Status:
1380 -- Internal Table Handler Use Only.
1381 --
1382 procedure chk_soft_coding_keyflex_id
1383 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1384 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1385 ,p_soft_coding_keyflex_id in per_all_assignments_f.soft_coding_keyflex_id%TYPE
1386 ,p_effective_date in date
1387 ,p_validation_start_date in date
1388 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1389 ,p_payroll_id in per_all_assignments_f.payroll_id%TYPE
1390 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1391 );
1392 --
1393 -- --------------------------------------------------------------------------+
1394 -- |--------------------< chk_source_organization_id >-----------------------|
1395 -- --------------------------------------------------------------------------+
1396 --
1397 -- Description:
1398 -- - Validates that on insert of an employee assignment that source
1399 -- organization is not set. Also on update of an employee assignment
1400 -- when source organization is set then checks that the existing
1401 -- source organization is the same as the set source organization.
1402 -- - Validates that the source organization exists in PER_ORGANIZATION_UNITS
1403 -- and the effective start date of the assignment is between the date from
1404 -- and date to of the source organization.
1405 -- - Validates that the source organization is in the same business group
1406 -- as the assignment.
1407 --
1408 -- Pre-conditions:
1409 -- A valid assignment type
1410 -- A Valid business group
1411 --
1412 -- In Arguments:
1413 -- p_assignment_type
1417 -- Post Success:
1414 -- p_business_group_id
1415 -- p_source_organization_id
1416 --
1418 -- Processing continues if:
1419 -- - the assignment is an employee assignment and is not set on insert.
1420 -- Or on update of an employee assignment when source organization
1421 -- is set, the existing source organization is the same as the set
1422 -- source organization.
1423 -- - the source organization exists in PER_ORGANIZATION_UNITS where the
1424 -- effective start date of the assignment is between the date from and
1425 -- date to of the source organization.
1426 -- - the source organization is in the same business group as the
1427 -- assignment business group.
1428 --
1429 -- Post Failure:
1430 -- An application error is raised and processing ends if:
1431 -- - the assignment is an employee assignment and is set on insert.
1432 -- Or on update of an employee assignment when source organization
1433 -- is set, the existing source organization is different to the set
1434 -- source organization.
1435 -- - the source organization does'nt exist in PER_ORGANIZATION_UNITS
1436 -- where the effective start date of the assignment is between the
1437 -- date from and date to of the source organization.
1438 -- - the source organization is in a different business group to
1439 -- the assignment business group.
1440 --
1441 -- Access Status:
1442 -- Internal Table Handler Use Only.
1443 --
1444 procedure chk_source_organization_id
1445 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1446 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1447 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1448 ,p_source_organization_id in per_all_assignments_f.source_organization_id%TYPE
1449 ,p_effective_date in date
1450 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1451 ,p_validation_start_date in date
1452 ,p_validation_end_date in date
1453 );
1454 --
1455 -- --------------------------------------------------------------------------+
1456 -- |------------------------< chk_source_type >------------------------------|
1457 -- --------------------------------------------------------------------------+
1458 --
1459 -- Description:
1460 -- - Validates that the source type exists as a lookup code on HR_LOOKUPS
1461 -- for the lookup type 'REC_TYPE' with an enabled flag set to 'Y' and
1462 -- the effective start date of the assignment between start date active
1463 -- and end date active on HR_LOOKUPS.
1464 --
1465 -- - Validates when the recruitment activity is set that the source type
1466 -- is the same as the type of the recruitment activity type.
1467 --
1468 -- Pre-conditions:
1469 -- None
1470 --
1471 -- In Arguments:
1472 -- p_assignment_id
1473 -- p_source_type
1474 -- p_recruitment_activity_id
1475 -- p_effective_date
1476 -- p_validation_start_date
1477 -- p_validation_end_date
1478 -- p_object_version_number
1479 --
1480 -- Post Success:
1481 -- Processing continues if:
1482 -- - source type is set.
1483 -- - source type exists as a lookup code in HR_LOOKUPS for the
1484 -- lookup type 'REC_TYPE' where the enabled flag is 'Y' and the
1485 -- effective start date of the assignment is between start date
1486 -- active and end date active on HR_LOOKUPS.
1487 -- - recruitment activity is set and the source type is the same as
1488 -- the type of the recruitment activity.
1489 --
1490 -- Post Failure:
1491 -- An application error is raised and processing is terminated if:
1492 -- - source type does'nt exist as a lookup code in HR_LOOKUPS for
1493 -- the lookup type 'REC_TYPE' where the enabled flag is 'Y' and
1494 -- the effective start date of the assignment is between start
1495 -- date active and end date active on HR_LOOKUPS.
1496 -- - recruitment activity is set and the source type is different
1497 -- to the type of the recruitment activity.
1498 --
1499 -- Access Status:
1500 -- Internal Table Handler Use Only
1501 --
1502 procedure chk_source_type
1503 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1504 ,p_source_type in per_all_assignments_f.source_type%TYPE
1505 ,p_recruitment_activity_id in per_all_assignments_f.recruitment_activity_id%TYPE
1506 ,p_effective_date in date
1507 ,p_validation_start_date in date
1508 ,p_validation_end_date in date
1509 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1510 );
1511 --
1512 -- --------------------------------------------------------------------------+
1513 -- |-------------------< chk_special_ceiling_step_id >-----------------------|
1514 -- --------------------------------------------------------------------------+
1515 --
1516 -- Description:
1517 -- - Validates that the special_ceiling_step_id exists and is date effective
1518 -- for the assignment.
1519 --
1520 -- - Validates that the business group of the special_ceiling_step_id on
1521 -- per_grade_spines is the same as the assignment business group.
1522 --
1523 -- - Validates when special ceiling step is set that grade is also set
1524 -- and is valid for the grade.
1525 --
1529 -- - Validates that if the value for special_ceiling_step_id is lower
1526 -- - Validates that if the value for the assignment grade_id is null
1527 -- then the value for special_ceiling_step_id should also be null.
1528 --
1530 -- than the spinal point placement specified fo the assignment, the
1531 -- special_ceiling_step_id cannot be selected.
1532 --
1533 -- - Validates that the assignment is an employee or applicant or offer or
1534 -- benefit assignment.
1535 --
1536 -- Pre-conditions:
1537 -- A valid Assignment Business Group.
1538 -- A valid assignment type.
1539 --
1540 -- In Arguments:
1541 -- p_assignment_id
1542 -- p_assignment_type
1543 -- p_special_ceiling_step_id
1544 -- p_grade_id
1545 -- p_business_group_id
1546 -- p_effective_start_date
1547 -- p_effective_end_date
1548 -- p_effective_date
1549 -- p_object_version_number
1550 --
1551 -- Post Success:
1552 -- Processing continues if :
1553 -- - The special_ceiling_step_id exists and is date effective
1554 -- - The business group of the special_ceiling_step_id
1555 -- is the same as that of the assignment
1556 -- - The special ceiling step is set and the grade is also set and
1557 -- valid in per_grade_spines.
1558 -- - The special_ceiling_step_id is >= than the spinal_point_placement
1559 -- specified for the assignment
1560 -- - the assignment is an employee or applicant or offer or benefit assignment.
1561 --
1562 -- Post Failure:
1563 -- An application error will be raised and processing terminated if any
1564 -- of the following cases are found :
1565 -- - The special_ceiling_step_id does not exist or is not date effective
1566 -- - The business group of the special_ceiling_step_id is not the same
1567 -- as that of the assignment
1568 -- - The special ceiling step is set and the grade is not set or valid
1569 -- in per_grade_spines.
1570 -- - The value for grade_id is not null and the special_ceiling_step_id
1571 -- is not valid for the grade on per_grade_spines
1572 -- - The special_ceiling_step_id is < than the spinal_point_placement(s)
1573 -- specified for the assignment.
1574 -- - the assignment is not an employee or applicant or offer or benefit assignment.
1575 --
1576 -- Access Status:
1577 -- Internal Table Handler Use Only.
1578 --
1579 procedure chk_special_ceiling_step_id
1580 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1581 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1582 ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
1583 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
1584 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1585 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1586 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1587 ,p_effective_date in date
1588 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1589 );
1590 --
1591 -- --------------------------------------------------------------------------+
1592 -- |--------------------------< chk_supervisor_id >--------------------------|
1593 -- --------------------------------------------------------------------------+
1594 --
1595 -- Description:
1596 -- - Validates that the supervisor is'nt the same as the person of the
1597 -- assignment.
1598 -- - Validates that the supervisor is date effectively valid for the
1599 -- validation period of the assignment.
1600 -- - Validates that the supervisor is in the same business group as the
1601 -- assignment being validated.
1602 -- - Validates that the supervisor is being set for an employee assignment.
1603 -- - Validates that the supervisor is an employee.
1604 --
1605 -- Pre-conditions:
1606 -- A valid person
1607 -- A valid business group
1608 --
1609 -- In Arguments:
1610 -- p_assignment_id
1611 -- p_supervisor_id
1612 -- p_person_id
1613 -- p_business_group_id
1614 -- p_validation_start_date
1615 -- p_effective_date
1616 -- p_object_version_number
1617 --
1618 -- Post Success:
1619 -- Processing continues if:
1620 -- - the supervisor is null.
1621 -- - the supervisor is not equal to the assignment person_id.
1622 -- - the supervisor exists and is date effective for the validation
1623 -- period of the assignment.
1624 -- - the supervisor is in the same business group as the assignment
1625 -- person.
1626 -- - the supervisor is set for an employee assignment.
1627 -- - the current employee flag of the supervisor in PER_PEOPLE_F
1628 -- is set to 'Y'.
1629 --
1630 -- Post Failure:
1631 -- An application error is raised and processing is terminated if:
1632 -- - the supervisor_id is equal to the assignment person_id.
1633 -- - the supervisor_id does'nt exist or is not date effective for the
1634 -- validation period of the assignment.
1635 -- - the supervisor_id is in a different business group to the assignment
1636 -- person_id.
1637 -- - the supervisor is set for a non employee assignment.
1638 -- - the current employee flag of the supervisor in PER_PEOPLE_F
1639 -- is not set to 'Y'.
1640 --
1641 -- Access Status:
1642 -- Internal Table Handler Use Only.
1643 --
1644 procedure chk_supervisor_id
1648 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1645 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1646 ,p_supervisor_id in per_all_assignments_f.supervisor_id%TYPE
1647 ,p_person_id in per_all_assignments_f.person_id%TYPE
1649 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1650 ,p_effective_date in date
1651 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1652 );
1653 --
1654 -- --------------------------------------------------------------------------+
1655 -- |------------------< chk_supervisor_assignment_id >-----------------------|
1656 -- --------------------------------------------------------------------------+
1657 --
1658 -- Description:
1659 -- - Validates that the supervisor assignment belongs to the supervisor.
1660 -- - Validates that the supervisor assignment is date effectively valid for
1661 -- the validation period of the assignment.
1662 -- - Validates that the supervisor is for an employee or contingent
1663 -- worker assignment.
1664 --
1665 -- Pre-conditions:
1666 -- A valid supervisor
1667 --
1668 -- In Arguments:
1669 -- p_assignment_id
1670 -- p_supervisor_id
1671 -- p_supervisor_assignment_id
1672 -- p_validation_start_date
1673 -- p_effective_date
1674 -- p_object_version_number
1675 --
1676 -- Post Success:
1677 -- Processing continues if:
1678 -- - the supervisor assignment is null.
1679 -- - the supervisor assignment belongs to the supervisor and is effective.
1680 -- - the supervisor assignment is an employee or contingent worker
1681 -- -- assignment.
1682 --
1683 -- Post Failure:
1684 -- An application error is raised and processing is terminated if:
1685 -- - the supervisor assignment does not belong to the supervisor
1686 -- - the supervisor assignment is not effective
1687 -- - the supervisor is not an employee or contingent worker assignment
1688 --
1689 -- Access Status:
1690 -- Internal Row Handler Use Only.
1691 --
1692 procedure chk_supervisor_assignment_id
1693 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1694 ,p_supervisor_id in per_all_assignments_f.supervisor_id%TYPE
1695 ,p_supervisor_assignment_id in out nocopy per_all_assignments_f.supervisor_assignment_id%TYPE
1696 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1697 ,p_effective_date in date
1698 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1699 );
1700 --
1701 -- --------------------------------------------------------------------------+
1702 -- |-----------------------< chk_system_pers_type >--------------------------|
1703 -- --------------------------------------------------------------------------+
1704 --
1705 -- Description:
1706 -- Validates that system person type has not changed in the future
1707 --
1708 -- Pre-conditions:
1709 -- None
1710 --
1711 -- In Arguments:
1712 -- p_person_id
1713 -- p_validation_start_date
1714 -- p_validation_end_date
1715 -- p_datetrack_mode
1716 -- p_effective_date
1717 --
1718 -- Post Success:
1719 -- If no system person type changes exist in the future then processing
1720 -- continues.
1721 --
1722 -- Post Failure:
1723 -- If the system person type changes in the future an application error
1724 -- is raised and processing is terminated.
1725 --
1726 -- Access Status:
1727 -- Internal Table Handler Use Only.
1728 --
1729 procedure chk_system_pers_type
1730 (p_person_id in per_all_assignments_f.person_id%TYPE
1731 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1732 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1733 ,p_datetrack_mode in varchar2
1734 ,p_effective_date in date
1735 );
1736 --
1737 -- --------------------------------------------------------------------------+
1738 -- |-------------------------< chk_term_status >-----------------------------|
1739 -- --------------------------------------------------------------------------+
1740 --
1741 -- Description:
1742 -- Validates an assignment cannot be deleted using the following datetrack
1743 -- modes :
1744 -- - DELETE_NEXT_CHANGE
1745 -- - DELETE_FUTURE_CHANGE
1746 -- - UPDATE_OVERRIDE
1747 --
1748 -- if the assignment is terminated in the future, i.e. Assignment Status
1749 -- Type set to 'TERM_ASSIGN'.
1750 --
1751 -- Pre-conditions:
1752 -- None
1753 --
1754 -- In Arguments:
1755 -- p_assignment_id
1756 -- p_validation_start_date
1757 -- p_datetrack_mode
1758 --
1759 -- Post Success:
1760 -- If assignment is not terminated in the future then processing
1761 -- continues.
1762 --
1763 -- Post Failure:
1764 -- If the assignment is terminated in the future then an
1765 -- application error is raised and processing is terminated.
1766 --
1767 -- Access Status:
1768 -- Internal Table Handler Use Only.
1769 --
1770 procedure chk_term_status
1771 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1772 ,p_datetrack_mode in varchar2
1773 ,p_validation_start_date in date
1774 );
1775 --
1776 -- 70.1 change d start.
1780 -- --------------------------------------------------------------------------+
1777 --
1778 -- --------------------------------------------------------------------------+
1779 -- |---------------------< chk_time_normal_finish >--------------------------|
1781 --
1782 -- Description:
1783 -- Validates that the time_normal_finish is a valid format.
1784 --
1785 -- Pre-conditions:
1786 -- None
1787 --
1788 -- In Arguments:
1789 -- p_time_normal_finish
1790 --
1791 -- Post Success:
1792 -- If time_normal_finish is a valid format then processing continues
1793 --
1794 -- Post Failure:
1795 -- If time_normal_finish is not a valid format then an application error is
1796 -- raised
1797 -- and processing is terminated
1798 --
1799 -- Access Status:
1800 -- Internal Table Handler Use Only.
1801 --
1802 procedure chk_time_normal_finish
1803 (p_time_normal_finish in per_all_assignments_f.time_normal_finish%TYPE
1804 );
1805 --
1806 --
1807 procedure chk_time_finish_formatted
1808 (p_time_normal_finish in out nocopy per_all_assignments_f.time_normal_finish%TYPE
1809 );
1810 --
1811 -- --------------------------------------------------------------------------+
1812 -- |---------------------< chk_time_normal_start >---------------------------|
1813 -- --------------------------------------------------------------------------+
1814 --
1815 -- Description:
1816 -- Validates that the time_normal_start is a valid format.
1817 --
1818 -- Pre-conditions:
1819 -- None
1820 --
1821 -- In Arguments:
1822 -- p_time_normal_start
1823 --
1824 -- Post Success:
1825 -- If time_normal_start is a valid format then processing continues
1826 --
1827 -- Post Failure:
1828 -- If time_normal_start is not a valid format then an application error is
1829 -- raised and processing is terminated
1830 --
1831 -- Access Status:
1832 -- Internal Table Handler Use Only.
1833 --
1834 procedure chk_time_normal_start
1835 (p_time_normal_start in per_all_assignments_f.time_normal_start%TYPE
1836 );
1837 --
1838 --
1839 procedure chk_time_start_formatted
1840 (p_time_normal_start in out nocopy per_all_assignments_f.time_normal_start%TYPE
1841 );
1842 --
1843 -- --------------------------------------------------------------------------+
1844 -- |------------------------< chk_dup_apl_vacancy >---------------------------|
1845 -- --------------------------------------------------------------------------+
1846 --
1847 -- Description:
1848 -- Validates this application to see if it is a duplicate for an existing open
1849 -- vacancy that has already been applied for by this person. This check is done
1850 -- only for applicant assignments.
1851 --
1852 -- Pre-conditions:
1853 -- The assignment should be an Applicant Assignment.
1854 --
1855 -- In Arguments:
1856 -- p_person_id
1857 -- p_business_group_id
1858 -- p_vacancy_id
1859 -- p_effective_date
1860 -- p_assignment_type
1861 --
1862 -- Post Success:
1863 -- If condition not met then processing continues
1864 --
1865 -- Post Failure:
1866 -- If condition met then an application error is raised
1867 -- and processing is terminated
1868 --
1869 procedure chk_dup_apl_vacancy
1870 (p_person_id in per_all_assignments_f.person_id%type
1871 ,p_business_group_id in per_all_assignments_f.business_group_id%type
1872 ,p_vacancy_id in per_all_assignments_f.vacancy_id%type
1873 ,p_effective_date in date
1874 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default null
1875 );
1876 --
1877 -- Start changes for bug 8687386
1878 -- --------------------------------------------------------------------------+
1879 -- |------------------------< chk_dup_apl_vacancy >---------------------------|
1880 -- --------------------------------------------------------------------------+
1881 --
1882 -- Description:
1883 -- Validates this application to see if it is a duplicate for an existing open
1884 -- vacancy that has already been applied for by this person. This check is done
1885 -- only for applicant assignments. For update cases, it will validate the vacancy_id
1886 -- with the other applicant applications except the current applicant application.
1887 --
1888 -- Pre-conditions:
1889 -- The assignment should be an Applicant Assignment.
1890 --
1891 -- In Arguments:
1892 -- p_person_id
1893 -- p_business_group_id
1894 -- p_vacancy_id
1895 -- p_effective_date
1896 -- p_assignment_type
1897 -- p_assignment_id
1898 -- p_validation_start_date
1899 -- p_validation_end_date
1900 -- p_datetrack_mode
1901 --
1902 -- Post Success:
1903 -- If condition not met then processing continues
1904 --
1905 -- Post Failure:
1906 -- If condition met then an application error is raised
1907 -- and processing is terminated
1908 --
1909 procedure chk_dup_apl_vacancy
1910 (p_person_id in per_all_assignments_f.person_id%type
1911 ,p_business_group_id in per_all_assignments_f.business_group_id%type
1912 ,p_vacancy_id in per_all_assignments_f.vacancy_id%type
1913 ,p_effective_date in date
1914 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE default null
1915 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1919 );
1916 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1917 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1918 ,p_datetrack_mode in varchar2
1920 -- End changes for bug 8687386
1921 --
1922 -- --------------------------------------------------------------------------+
1923 -- |-------------------------< chk_vacancy_id >------------------------------|
1924 -- --------------------------------------------------------------------------+
1925 --
1926 -- Description:
1927 -- - Validates that on insert of an employee assignment that
1928 -- vacancy is not set. Also on update of an employee assignment
1929 -- when vacancy is set then checks that the existing vacancy
1930 -- is the same as the set vacancy.
1931 -- - Validates that the vacancy exists in PER_VACANCIES and the effective
1932 -- start date is between the date from and date to of the vacancy.
1933 -- - If the assignment is of type Offers, validates that the vacancy exists
1934 -- in PER_VACANCIES
1935 -- - Validates that the vacancy is in the same business group as the assignment.
1936 --
1937 -- Pre-conditions:
1938 -- A valid assignment type
1939 -- A valid business group
1940 --
1941 -- In Arguments:
1942 -- p_vacancy_id
1943 -- p_assignment_type
1944 -- p_business_group_id
1945 -- p_validation_start_date
1946 -- p_validation_end_date
1947 --
1948 -- Post Success:
1949 -- Processing continues if:
1950 -- - the assignment is an employee assignment and is not set on insert.
1951 -- Or on update of an employee assignment when vacancy is set, the
1952 -- existing vacancy is the same as the set vacancy.
1953 -- - the vacancy exists in PER_VACANCIES where the effective start date of
1954 -- the assignment is between the date from and date to of the vacancy.
1955 -- - the vacancy is in the same business group as the assignment business
1956 -- group.
1957 --
1958 -- Post Failure:
1959 -- An application error is raised and processing ends if:
1960 -- - the assignment is an employee assignment and is set on insert.
1961 -- Or on update of an employee assignment when vacancy
1962 -- is set, the existing vacancy is different to the set vacancy.
1963 -- - the vacancy does'nt exist in PER_VACANCIES where the effective start
1964 -- date of the assignment is between the date from and date to of the
1965 -- vacancy. This will fail for all assignment types except for Offer.
1966 -- - the vacancy is in a different business group to the assignment
1967 -- business group.
1968 --
1969 -- Access Status:
1970 -- Internal Table Handler Use Only.
1971 --
1972 procedure chk_vacancy_id
1973 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1974 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1975 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1976 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1977 ,p_effective_date in date
1978 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1979 ,p_validation_start_date in date
1980 ,p_validation_end_date in date
1981 );
1982 --
1983 -- --------------------------------------------------------------------------+
1984 -- |----------------------< gen_assignment_sequence >------------------------|
1985 -- --------------------------------------------------------------------------+
1986 --
1987 -- Description:
1988 -- Validates that the assignment_sequence is generated on insert only
1989 -- and then cannot be updated. It is generated using the next unique
1990 -- assignment sequence for a given combination of person_id and
1991 -- assignment_type (which should always be 'E' as per Royal Navy
1992 -- requirements).
1993 --
1994 -- Pre-conditions:
1995 -- A valid person_id
1996 -- A valid assignment_type
1997 --
1998 -- In Arguments:
1999 -- p_assignment_type
2000 -- p_person_id
2001 --
2002 -- Post Success:
2003 -- This procedure should always succeed as the assignment_type and
2004 -- person_id are validated prior to the execution of the procedure.
2005 --
2006 -- Post Failure:
2007 -- This procedure should not fail as the assignment_type and person_id
2008 -- are validated prior to the execution of the procedure.
2009 --
2010 -- Access Status:
2011 -- Internal Table Handler Use Only.
2012 --
2013 procedure gen_assignment_sequence
2014 (p_assignment_type in per_all_assignments_f.assignment_type%TYPE
2015 ,p_person_id in per_all_assignments_f.person_id%TYPE
2016 ,p_assignment_sequence in out nocopy per_all_assignments_f.assignment_sequence%TYPE
2017 );
2018 --
2019 -- --------------------------------------------------------------------------+
2020 -- |-----------------------< other_managers_in_org >-------------------------|
2021 -- --------------------------------------------------------------------------+
2022 --
2023 -- Description:
2024 -- Checks to see if any other current assignments for the same organization
2025 -- have the manager_flag set to 'Y', and returns the appropriate boolean
2026 -- result.
2027 --
2028 -- Pre-conditions:
2029 -- A valid Organization ID
2030 --
2031 -- In Arguments:
2032 -- p_assignment_id
2033 -- p_effective_date
2034 -- p_organization_id
2035 --
2036 -- Post Success:
2040 -- If the cursor raises an error, it will be passed back to the calling
2037 -- TRUE if other managers found, FALSE otherwise.
2038 --
2039 -- Post Failure:
2041 -- routine as an unhandled exception.
2042 --
2043 -- Access Status:
2044 -- Internal Table Handler Use Only.
2045 --
2046 function other_managers_in_org
2047 (p_organization_id in per_all_assignments_f.organization_id%TYPE
2048 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2049 ,p_effective_date in date
2050 ) return boolean;
2051 --
2052 -- --------------------------------------------------------------------------+
2053 -- |-----------------------< gen_date_probation_end >------------------------|
2054 -- --------------------------------------------------------------------------+
2055 --
2056 -- Description:
2057 --
2058 -- Checks that when date probation end is not null that a value is not
2059 -- calculated and the parameter value is used. When date probation end is
2060 -- null then providing that probation period and probation unit are both
2061 -- not null and probation unit does not have the value of 'H' then date
2062 -- probation end is calculated based on the value of probation unit.
2063 --
2064 -- Pre-conditions:
2065 -- None
2066 --
2067 -- In Arguments:
2068 -- p_assignment_id
2069 -- p_effective_date
2070 -- p_probation_unit
2071 -- p_probation_period
2072 -- p_validation_start_date
2073 -- p_object_version_number
2074 -- p_date_probation_end
2075 --
2076 -- Out Arguments:
2077 -- p_date_probation_end
2078 --
2079 -- Post Success:
2080 -- If date probation end is not null then this not null value is passed out
2081 -- in the parameter date probation end.
2082 --
2083 -- If date probation end is null and probation unit and probation period
2084 -- are both not null and probation unit is not 'H' then date probation end
2085 -- is calculated and passed out in the parameter date probation end.
2086 --
2087 -- Post Failure:
2088 -- If any errors are raised they will be passed back to the calling routine
2089 -- as an unhandled exception.
2090 --
2091 -- Access Status:
2092 -- Internal Table Handler Use Only.
2093 --
2094 procedure gen_date_probation_end
2095 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2096 ,p_effective_date in date
2097 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
2098 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
2099 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
2100 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2101 ,p_date_probation_end in out nocopy per_all_assignments_f.date_probation_end%TYPE
2102 );
2103 --
2104 -- --------------------------------------------------------------------------+
2105 -- |--------------------< chk_internal_address_line >-----------------------|
2106 -- --------------------------------------------------------------------------+
2107 --
2108 -- Description:
2109 -- - Validates that the assignment is an employee or applicant or offer assignment.
2110 -- - Temporary: Validates that the <Val. attr.> is null or
2111 -- unchanged on insert.
2112 --
2113 -- Pre-conditions:
2114 -- A valid assignment type.
2115 --
2116 -- In Arguments:
2117 -- p_assignment_id
2118 -- p_assignment_type
2119 -- p_internal_address_line
2120 -- p_effective_date
2121 -- p_object_version_number
2122 --
2123 -- Post Success:
2124 -- Processing continues if:
2125 -- - the internal address line is set for an employee or applicant or
2126 -- offer assignment.
2127 --
2128 -- Post Failure:
2129 -- An application error is raised and processing ends if:
2130 -- - the internal address line is set for a non employee or
2131 -- applicant or offer assignment.
2132 --
2133 -- Access Status:
2134 -- Internal Table Handler Use Only.
2135 --
2136 procedure chk_internal_address_line
2137 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
2138 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
2139 ,p_internal_address_line in per_all_assignments_f.internal_address_line%TYPE
2140 ,p_effective_date in date
2141 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
2142 );
2143 end per_asg_bus2;
[Home] [Help]