1 Package per_asg_bus1 as
2 /* $Header: peasgrhi.pkh 120.4.12010000.1 2008/07/28 04:11:01 appldev ship $ */
3 --
4 -- ---------------------------------------------------------------------------+
5 -- |------------------------< set_security_group_id >-------------------------|
6 -- ---------------------------------------------------------------------------+
7 -- {Start Of Comments}
8 --
9 -- Description:
10 -- Sets the security_group_id in CLIENT_INFO for the assignment's business
11 -- group context.
12 --
13 -- Prerequisites:
14 -- None,
15 --
16 -- In Parameters:
17 -- Name Reqd Type Description
18 -- p_assignment_id Yes Number Assignment to use for
19 -- deriving the security group
20 -- context.
21 --
22 -- Post Success:
23 -- The security_group_id will be set in CLIENT_INFO.
24 --
25 -- Post Failure:
26 -- An error is raised if the assignment_id does not exist.
27 --
28 -- Access Status:
29 -- Internal Development Use Only.
30 --
31 -- {End Of Comments}
32 --
33 -- ---------------------------------------------------------------------------+
34 procedure set_security_group_id
35 (
36 p_assignment_id in per_all_assignments_f.assignment_id%TYPE
37 ,p_associated_column1 in varchar2 default null
38 );
39 --
40 -- ---------------------------------------------------------------------------+
41 -- |----------------------< check_non_updateable_args >-----------------------|
42 -- ---------------------------------------------------------------------------+
43 -- {Start Of Comments}
44 --
45 -- Description:
46 -- This procedure is used to ensure that non updatetable attributes have
47 -- not been updated. If an attribute has been updated an error is generated.
48 --
49 -- Pre Conditions:
50 -- g_old_rec has been populated with details of the values currently in
51 -- the database.
52 --
53 -- In Arguments:
54 -- p_rec has been populated with the updated values the user would like the
55 --
56 -- Post Success:
57 -- Processing continues if all the non updateable attributes have not
58 -- changed.
59 --
60 -- Post Failure:
61 -- An application error is raised if any of the non updatable attributes
62 -- (business_group_id, person_id, assignment_sequence, assignment_type,
63 -- period_of_service_id, primary_flag, or assignment_id) have been altered.
64 --
65 -- Access Status:
66 -- Internal Table Handler Use Only.
67 --
68 Procedure check_non_updateable_args(p_rec in per_asg_shd.g_rec_type
69 ,p_effective_date in date);
70 --
71 -- ----------------------------------------------------------------------------
72 -- |--------------------------< dt_delete_validate >--------------------------|
73 -- ----------------------------------------------------------------------------
74 -- {Start Of Comments}
75 --
76 -- Description:
77 -- This procedure is used for referential integrity of datetracked
78 -- child entities when either a datetrack DELETE or ZAP is in operation
79 -- and where there is no cascading of delete defined for this entity.
80 -- For the datetrack mode of DELETE or ZAP we must ensure that no
81 -- datetracked child rows exist between the validation start and end
82 -- dates.
83 --
84 -- Pre Conditions:
85 -- This procedure is called from the delete_validate.
86 --
87 -- In Arguments:
88 --
89 -- Post Success:
90 -- Processing continues.
91 --
92 -- Post Failure:
93 -- If a row exists by determining the returning Boolean value from the
94 -- generic dt_api.rows_exist function then we must supply an error via
95 -- the use of the local exception handler l_rows_exist.
96 --
97 -- Developer Implementation Notes:
98 -- This procedure should not need maintenance unless the HR Schema model
99 -- changes.
100 --
101 -- Access Status:
102 -- Internal Table Handler Use Only.
103 --
104 -- {End Of Comments}
105 -- ----------------------------------------------------------------------------
106 Procedure dt_delete_validate
107 (p_assignment_id in number,
108 p_datetrack_mode in varchar2,
109 p_validation_start_date in date,
110 p_validation_end_date in date);
111 --
112 -- ----------------------------------------------------------------------------
113 -- |--------------------------< dt_update_validate >--------------------------|
114 -- ----------------------------------------------------------------------------
115 -- {Start Of Comments}
116 --
117 -- Description:
118 -- This procedure is used for referential integrity of datetracked
119 -- parent entities when a datetrack update operation is taking place
120 -- and where there is no cascading of update defined for this entity.
121 --
122 -- Pre Conditions:
123 -- This procedure is called from the update_validate.
124 --
125 -- In Arguments:
126 --
127 -- Post Success:
128 -- Processing continues.
129 --
130 -- Post Failure:
131 --
132 -- Developer Implementation Notes:
133 -- This procedure should not need maintenance unless the HR Schema model
134 -- changes.
135 --
136 -- Access Status:
137 -- Internal Table Handler Use Only.
138 --
139 -- {End Of Comments}
140 -- ----------------------------------------------------------------------------
141 Procedure dt_update_validate
142 (p_payroll_id in number default hr_api.g_number,
143 p_person_id in number default hr_api.g_number,
144 p_datetrack_mode in varchar2,
145 p_validation_start_date in date,
146 p_validation_end_date in date);
147 --
148 -- ----------------------------------------------------------------------------
149 -- |---------------------------< insert_validate >----------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure controls the execution of all insert business rules
155 -- validation.
156 --
157 -- Pre Conditions:
158 -- This private procedure is called from ins procedure.
159 --
160 -- In Arguments:
161 -- A Pl/Sql record structre.
162 --
163 -- Post Success:
164 -- Processing continues.
165 --
166 -- Post Failure:
167 -- If a business rules fails the error will not be handled by this procedure
168 -- unless explicity coded.
169 --
170 -- Developer Implementation Notes:
171 -- For insert, your business rules should be coded within this procedure and
172 -- should ideally (unless really necessary) just be straight procedure or
173 -- function calls. Try and avoid using conditional branching logic.
174 --
175 -- Access Status:
176 -- Internal Table Handler Use Only.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure insert_validate
181 (p_rec in out nocopy per_asg_shd.g_rec_type,
182 p_effective_date in date,
183 p_datetrack_mode in varchar2,
184 p_validation_start_date in date,
185 p_validation_end_date in date,
186 p_validate_df_flex in boolean,
187 p_other_manager_warning out nocopy boolean,
188 p_hourly_salaried_warning out nocopy boolean,
189 p_inv_pos_grade_warning out nocopy boolean
190 );
191 --
192 --
193 -- ----------------------------------------------------------------------------
194 -- |---------------------------< update_validate >----------------------------|
195 -- ----------------------------------------------------------------------------
196 -- {Start Of Comments}
197 --
198 -- Description:
199 -- This procedure controls the execution of all update business rules
200 -- validation.
201 --
202 -- Pre Conditions:
203 -- This private procedure is called from upd procedure.
204 --
205 -- In Arguments:
206 -- A Pl/Sql record structre.
207 --
208 -- Post Success:
209 -- Processing continues.
210 --
211 -- Post Failure:
212 -- If a business rules fails the error will not be handled by this procedure
213 -- unless explicity coded.
214 --
215 -- Developer Implementation Notes:
216 -- For update, your business rules should be coded within this procedure and
217 -- should ideally (unless really necessary) just be straight procedure or
218 -- function calls. Try and avoid using conditional branching logic.
219 --
220 -- Access Status:
221 -- Internal Table Handler Use Only.
222 --
223 -- {End Of Comments}
224 -- ----------------------------------------------------------------------------
225 Procedure update_validate
226 (p_rec in out nocopy per_asg_shd.g_rec_type,
227 p_effective_date in date,
228 p_datetrack_mode in varchar2,
229 p_validation_start_date in date,
230 p_validation_end_date in date,
231 p_payroll_id_updated out nocopy boolean,
232 p_other_manager_warning out nocopy boolean,
233 p_hourly_salaried_warning out nocopy boolean,
234 p_no_managers_warning out nocopy boolean,
235 p_org_now_no_manager_warning out nocopy boolean,
236 p_inv_pos_grade_warning out nocopy boolean
237 );
238 --
239 -- ----------------------------------------------------------------------------
240 -- |---------------------------< delete_validate >----------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 -- This procedure controls the execution of all delete business rules
246 -- validation.
247 --
248 -- Pre Conditions:
249 -- This private procedure is called from del procedure.
250 --
251 -- In Arguments:
252 -- A Pl/Sql record structre.
253 --
254 -- Post Success:
255 -- Processing continues.
256 --
257 -- Post Failure:
258 -- If a business rules fails the error will not be handled by this procedure
259 -- unless explicity coded.
260 --
261 -- Developer Implementation Notes:
262 -- For delete, your business rules should be coded within this procedure and
263 -- should ideally (unless really necessary) just be straight procedure or
264 -- function calls. Try and avoid using conditional branching logic.
265 --
266 -- Access Status:
267 -- Internal Table Handler Use Only.
268 --
269 -- {End Of Comments}
270 -- ----------------------------------------------------------------------------
271 Procedure delete_validate
272 (p_rec in per_asg_shd.g_rec_type,
273 p_effective_date in date,
274 p_datetrack_mode in varchar2,
275 p_validation_start_date in date,
276 p_validation_end_date in date,
277 p_org_now_no_manager_warning out nocopy boolean,
278 p_loc_change_tax_issues OUT nocopy boolean,
279 p_delete_asg_budgets OUT nocopy boolean,
280 p_element_salary_warning OUT nocopy boolean,
281 p_element_entries_warning OUT nocopy boolean,
282 p_spp_warning OUT nocopy boolean,
283 p_cost_warning OUT nocopy boolean,
284 p_life_events_exists OUT nocopy boolean,
285 p_cobra_coverage_elements OUT nocopy boolean,
286 p_assgt_term_elements OUT nocopy boolean,
287 ---
288 p_new_prim_ass_id OUT nocopy number,
289 p_prim_change_flag OUT nocopy varchar2,
290 p_new_end_date OUT nocopy date,
291 p_new_primary_flag OUT nocopy varchar2,
292 p_s_pay_id OUT nocopy number,
293 p_cancel_atd OUT nocopy date,
294 p_cancel_lspd OUT nocopy date,
295 p_reterm_atd OUT nocopy date,
296 p_reterm_lspd OUT nocopy date,
297 ---
298 p_appl_asg_new_end_date OUT nocopy date );
299 --
300 -- --------------------------------------------------------------------------+
301 -- |-----------------------< chk_application_id >----------------------------|
302 -- --------------------------------------------------------------------------+
303 --
304 -- Description:
305 -- - Validates for the first applicant assignment for an applicant that when
306 -- set the application exists in PER_APPLICATIONS and the application date
307 -- received is the same as the assignment effective start date.
308 -- - Validates for all applicant assignments apart from the first and all the
309 -- offer assignments including the first that when set the application exists
310 -- in PER_APPLICATIONS and the applicant assignment effective start date is
311 -- between the date received and date end of the application when date end is set.
312 -- - Validates that for an applicant and an offer assignment the application is set.
313 -- - Validates that when inserting an employee assignment that the
314 -- application is not set.
315 -- - Validates that the application is not nullified for the update of an
316 -- employee assignment with an application which is already set.
317 -- - Validates that the business group of the application is the same as the
318 -- business group of the assignment.
319 --
320 -- Pre-conditions:
321 -- Valid assignment type
322 -- Valid business group
323 --
324 -- In Arguments:
325 -- p_assignment_id
326 -- p_assignment_type
327 -- p_business_group_id
328 -- p_application_id
329 -- p_effective_date
330 -- p_object_version_number
331 -- p_validation_start_date
332 --
333 -- Post Success:
334 -- Processing continues if:
335 -- - the application is set for the first applicant assignment and exists
336 -- in PER_APPLICATIONS and the application date received is the same as
337 -- the assignment effective start date.
338 -- - the application is set for an applicant assignment other than the first
339 -- applicant assignment and exists in PER_APPLICATIONS and the effective
340 -- start date of the applicant assignment is between the date received and
341 -- date end of the application when date end is set.
342 -- the assignment effective start date.
343 -- - the application is set for an applicant and an offer assignment.
344 -- - the application is not set for the insert of an employee assignment.
345 -- - the application is not nullified for the update of an employee
346 -- assignment with an existing set application.
347 -- - the application is in the same business group as the
348 -- assignment business group.
349 --
350 -- Post Failure:
351 -- An application error is raised and processing is terminated if:
352 -- - the application is set for the first applicant assignment and does'nt
353 -- exist in PER_APPLICATIONS or the application date received is not the
354 -- same as the assignment effective start date.
355 -- - the application is set for an applicant assignment other than the first
356 -- applicant assignment and does'nt exist in PER_APPLICATIONS where the
357 -- effective start date of the applicant assignment is between the date
358 -- received and date end of the application when date end is set.
359 -- - the application is not set for an applicant or an offer assignment.
360 -- - the application is set for the insert of an employee assignment.
361 -- - the application is nullified for the update of an employee
362 -- assignment with an existing set application.
366 -- Access Status:
363 -- - the application is in a different business group as the
364 -- assignment business group.
365 --
367 -- Internal Table Handler Use Only.
368 --
369 procedure chk_application_id
370 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
371 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
372 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
373 ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
374 ,p_application_id in per_all_assignments_f.application_id%TYPE
375 ,p_effective_date in date
376 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
377 ,p_validation_start_date in date
378 );
379 --
380 -- --------------------------------------------------------------------------+
381 -- |---------------------< gen_chk_assignment_number >-----------------------|
382 -- --------------------------------------------------------------------------+
383 --
384 -- Description:
385 -- - Validates for applicant, benefits and offer assignments that assignment
386 -- number is not set.
387 -- - Validates for employee assignments that assignment number is set by
388 -- calling procedure hr_assignment.gen_new_ass_number to :
389 -- a) Generate a new assignment_number on insert. (Generation only
390 -- occurs when the user does not provide a value.)
391 -- b) Check the uniqueness of the assignment number on insert or
392 -- update.
393 --
394 -- Pre-conditions:
395 -- A valid business group
396 -- A valid assignment type
397 -- A valid assignment sequence
398 -- A valid person
399 --
400 -- In Arguments:
401 -- p_assignment_id
402 -- p_business_group_id
403 -- p_assignment_type
404 -- p_assignment_sequence
405 -- p_person
406 -- p_assignment_number
407 -- p_effective_date
408 -- p_object_version_number
409 --
410 -- Post Success:
411 -- Processing continues if:
412 -- - For applicant, benefits and offer assignments, the assignment number is not set.
413 -- - For employee assignments, the assignment number is set. On insert
414 -- if a null assignment_number is passed then in a new number is
415 -- generated and processing will continue. On update, if the new
416 -- assignment_number is valid after a uniqueness check then
417 -- processing continues.
418 --
419 -- Post Failure:
420 -- An application error is raised and processing ends if:
421 -- - For applicant benefits and offer assignments, the assignment number is set.
422 -- - For employee assignments, the assignment number is not set. Hence,
423 -- an assignment number cannot be generated.
424 --
425 -- Access Status:
426 -- Internal Table Handler Use Only.
427 --
428 procedure gen_chk_assignment_number
429 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
430 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
431 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
432 ,p_assignment_sequence in per_all_assignments_f.assignment_sequence%TYPE
433 ,p_assignment_number in out nocopy per_all_assignments_f.assignment_number%TYPE
434 ,p_person_id in per_all_assignments_f.person_id%TYPE
435 ,p_effective_date in date
436 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
437 );
438 --
439 -- ---------------------------------------------------------------------------+
440 -- |----------------------< chk_assignment_status_type >----------------------|
441 -- ---------------------------------------------------------------------------+
442 --
443 -- Description:
444 --
445 -- If the assignment status type id is passed in, then it is validated
446 -- against the expected system status and business group, otherwise the
447 -- default assignment status type id for the specified system status,
448 -- business group and legislation code is returned.
449 --
450 -- Pre-conditions:
451 -- None
452 --
453 -- In Arguments:
454 -- p_assignment_status_type_id
455 -- p_business_group_id
456 -- p_legislation_code
457 -- p_expected_system_status
458 --
459 -- Post Success:
460 -- If assignment_status_type_id is valid or can be derived then processing
461 -- continues
462 --
463 -- Post Failure:
464 -- If assignment_status_type_id is not valid or cannot be derived then an
465 -- application error is raised and processing is terminated
466 --
467 -- Access Status:
468 -- HR Development Use Only.
469 --
470 --
471 procedure chk_assignment_status_type
472 (p_assignment_status_type_id in out nocopy number
473 ,p_business_group_id in number
474 ,p_legislation_code in varchar2
475 ,p_expected_system_status in varchar2
476 );
477 --
478 -- 70.3 change c end.
479 --
480 -- --------------------------------------------------------------------------+
481 -- |--------------------< chk_assignment_status_type_id >--------------------|
482 -- --------------------------------------------------------------------------+
483 --
484 -- Description:
485 --
486 -- - Validates that the assignment status type is set.
487 -- - Validates that the assignment status type exists in
491 -- PER_ASS_STATUS_TYPE_AMENDS for the assignment business group then
488 -- PER_ASSIGNMENT_STATUS_TYPES.
489 -- - Validates that the assignment status type active flag is set to 'Y'.
490 -- When an assignment status type(ASS_STATUS_TYPE_AMEND_ID) exists in
492 -- active flag is validated against PER_ASS_STATUS_TYPE_AMENDS. When the
493 -- assignment status type does'nt exist in PER_ASS_STATUS_TYPE_AMENDS for
494 -- the assignment business group then active flag is validated against
495 -- PER_ASSIGNMENT_STATUS_TYPES.
496 -- - Validates that when the business group is set for the assignment status
497 -- type that the assignment status type is in the same business group
498 -- as the assignment.
499 -- - Validates on update of employee assignments, that when the assignment
500 -- status type PER_SYSTEM_STATUS is 'TERM_ASSIGN' no other attributes on
501 -- the employee assignment can be updated and the new PER_SYSTEM_STATUS
502 -- must also be 'TERM_ASSIGN'.
503 -- - Validates on update of employee assignments, that PER_SYSTEM_STATUS
504 -- for the assignment status type is either 'ACTIVE_ASSIGN', 'SUSP_ASSIGN'
505 -- or 'TERM_ASSIGN'.
506 -- - Validates on update of employee assignments and when the new
507 -- assignment status type PER_SYSTEM_STATUS is 'ACTIVE_ASSIGN' that an
508 -- assignment status type PER_SYSTEM_STATUS of 'ACTIVE_ASSIGN' exists
509 -- before the validation start date of the assignment.
510 -- - Validates on insert of employee assignments that the assignment
511 -- status type PER_SYSTEM_STATUS is 'ACTIVE_ASSIGN'.
512 -- - Validates on update of an applicant assignment that the assignment status
513 -- type PER_SYSTEM_STATUS is either 'ACTIVE_APL', 'OFFER' or 'ACCEPTED'.
514 -- - Validates on insert or update in datetrack 'CORRECTION' mode of applicant
515 -- assignments that the assignment status type PER_SYSTEM_STATUS is
516 -- 'ACTIVE_APL'.
517 -- - Validates on update that the first datetracked instance of an applicant
518 -- assignment does not have an assignment status type PER_SYSTEM_STATUS of
519 -- 'TERM_APL'.
520 --
521 -- Pre-conditions:
522 -- None
523 --
524 -- In Arguments:
525 -- p_rec
526 -- p_effective_date
527 -- p_validation_start_date
528 --
529 -- Post Success:
530 -- Processing continues if:
531 --
532 -- - the assignment status type is set.
533 -- - the assignment status type exists in PER_ASSIGNMENT_STATUS_TYPES.
534 -- - the assignment status type active flag is set to 'Y' in
535 -- PER_ASS_STATUS_TYPE_AMENDS or PER_ASSIGNMENT_STATUS_TYPES.
536 -- - when set the assignment status type business group is the same as the
537 -- assignment business group.
538 -- - the assignment status type is being updated for an employee assignment
539 -- and the existing assignment status type PER_SYSTEM_STATUS is
540 -- 'TERM_ASSIGN' but no other employee assignment attribute values have
541 -- been modified.
542 -- - the assignment status type is being updated for an employee assignment
543 -- and the existing assignment status type PER_SYSTEM_STATUS is
544 -- 'TERM_ASSIGN' and the new PER_SYSTEM_STATUS of the assignment status
545 -- type is 'TERM_ASSIGN'.
546 -- - the assignment status type is being updated for an employee assignment
547 -- and PER_SYSTEM_STATUS for the assignment status type is either
548 -- 'ACTIVE_ASSIGN', 'SUSP_ASSIGN' or 'TERM_ASSIGN'.
549 -- - the assignment status type is being updated for an employee assignment
550 -- and the new assignment status type PER_SYSTEM_STATUS is 'ACTIVE_ASSIGN'
551 -- and a assignment status type PER_SYSTEM_STATUS exists before the
552 -- validation start date of the assignment.
553 -- - the assignment status type is being inserted for an employee assignment
554 -- and the PER_SYSTEM_STATUS of the assignment status type is 'ACTIVE_ASSIGN'.
555 -- - the assignment status type is being updated for an applicant assignment
556 -- and the PER_SYSTEM_STATUS of the assignment status type is either
557 -- 'ACTIVE_APL', 'OFFER' or 'ACCEPTED'.
558 -- - the assignment status type is being inserted or updated in datetrack
559 -- 'CORRECTION' mode for an applicant assignment and the PER_SYSTEM_STATUS
560 -- of the assignment status type is 'ACTIVE_APL'.
561 -- - the existing assignment status type PER_SYSTEM_STATUS is not 'TERM_APL'
562 -- on update.
563 --
564 -- Post Failure:
565 -- An application error is raised and processing ends if:
566 --
567 -- - assignment status type is not set.
568 -- - assignment status type does not exist in PER_ASSIGNMENT_STATUS_TYPES.
569 -- - assignment status type active flag is not set to 'Y' in
570 -- PER_ASS_STATUS_TYPE_AMENDS or PER_ASSIGNMENT_STATUS_TYPES.
571 -- - when set the assignment status type business group is not the same as the
572 -- assignment business group.
573 -- - the assignment status type is being updated for an employee assignment
574 -- and the existing assignment status type is 'TERM_ASSIGN' but other
575 -- employee assignment attribute values have been modified.
576 -- - the assignment status type is being updated for an employee assignment
577 -- and the existing assignment status type PER_SYSTEM_STATUS is
578 -- 'TERM_ASSIGN' and the new PER_SYSTEM_STATUS of the assignment status
579 -- type is not 'TERM_ASSIGN'.
583 -- - the assignment status type is being updated for an employee assignment
580 -- - the assignment status type is being updated for an employee assignment
581 -- and PER_SYSTEM_STATUS for the assignment status type is not either
582 -- 'ACTIVE_ASSIGN', 'SUSP_ASSIGN' or 'TERM_ASSIGN'.
584 -- and the new assignment status type PER_SYSTEM_STATUS is 'ACTIVE_ASSIGN'
585 -- and a assignment status type PER_SYSTEM_STATUS does'nt exist before the
586 -- validation start date of the assignment.
587 -- - the assignment status type is being inserted for an employee assignment
588 -- and the PER_SYSTEM_STATUS of the assignment status type is not
589 -- 'ACTIVE_ASSIGN'.
590 -- - the assignment status type is being updated for an applicant assignment
591 -- and the PER_SYSTEM_STATUS of the assignment status type is not either
592 -- 'ACTIVE_APL', 'OFFER' or 'ACCEPTED'.
593 -- - the assignment status type is being inserted for an applicant assignment
594 -- and the PER_SYSTEM_STATUS of the assignment status type is not 'ACTIVE_APL'.
595 -- - the existing assignment status type PER_SYSTEM_STATUS is 'TERM_APL'
596 -- on update.
597 --
598 -- Access Status:
599 -- Internal Table Handler Use Only.
600 --
601 procedure chk_assignment_status_type_id
602 (p_rec in per_asg_shd.g_rec_type
603 ,p_effective_date in per_all_assignments_f.effective_start_date%TYPE
604 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
605 );
606 --
607 -- ---------------------------------------------------------------------------
608 -- |-----------------------< chk_assignment_category >------------------------|
609 -- ---------------------------------------------------------------------------
610 --
611 -- Description:
612 --
613 -- - Validates that the assignment is an non payrolled worker assignment.
614 -- - Validates that the assignment category number is set
615 -- for an NPW assignment.
616 -- - Validates that the assignment category exists in HR_LOOKUPS for the
617 -- LOOKUP_TYPE = 'NPW_ASG_CATEGORY'
618 --
619 -- Pre-conditions:
620 -- A valid assignment type
621 -- A Valid business group
622 --
623 -- In Arguments:
624 -- p_assignment_id
625 -- p_assignment_type
626 -- p_person_id
627 -- p_effective_date
628 -- p_assignment_category
629 -- p_validation_start_date
630 -- p_validation_end_date
631 -- p_object_version_number
632 --
633 -- Post Success:
634 -- Processing continues if:
635 -- - the assignment is an NPW assignment.
636 -- - assignment category is set for an NPW assignment.
637 -- - assignment cateogry exists in 'NPW_ASG_CATEGORY' lookup.
638 --
639 -- Post Failure:
640 -- An application error is raised and processing ends if:
641 -- - the assignment is not an NPW assignment.
642 -- - assignment category is set for a non NPW assignment.
643 -- - assignment cateogry does not exists in 'NPW_ASG_CATEGORY' lookup.
644 --
645 -- Access Status:
646 -- Internal Table Handler Use Only.
647 --
648 PROCEDURE chk_assignment_category
649 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
650 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
651 ,p_effective_date in date
652 ,p_assignment_category in per_assignments_f.assignment_category%TYPE
653 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
654 ,p_validation_start_date in date
655 ,p_validation_end_date in date);
656 --
657 -- --------------------------------------------------------------------------+
658 -- |-----------------------< chk_assignment_type >---------------------------|
659 -- --------------------------------------------------------------------------+
660 --
661 -- Description:
662 --
663 -- - Validates that assignment type is set to either 'A' or 'E' or 'C' or 'B' or 'O'
664 -- - Validates when assignment type is 'A' or 'O' that the system person
665 -- type of the person is either 'APL', 'APL_EX_APL', 'EMP_APL',
666 -- 'EX_EMP_APL'.
667 -- - Validates when assignment type is 'E' that the system person
668 -- type of the person is either 'EMP', 'EMP_APL' or 'EX_EMP'.
669 --
670 -- Pre-conditions:
671 -- - A valid person
672 --
673 -- In Arguments:
674 -- p_assignment_type
675 -- p_person_id
676 --
677 -- Post Success:
678 -- - assignment type is set and is either 'A' or 'E' or 'C' or 'B' or 'O'.
679 -- - assignment type is 'A' or 'O' and the system person type of the person
680 -- is either 'APL', 'APL_EX_APL', 'EMP_APL', 'EX_EMP_APL'.
681 -- - assignment type is 'E' and the system person type of the person
682 -- is either 'EMP', 'EMP_APL' or 'EX_EMP'.
683 --
684 -- Post Failure:
685 -- - assignment type is set and is not either 'A' or 'E' or 'C' or 'B' or 'O'.
686 -- - assignment type is 'A' or 'O' and the system person type of the person
687 -- is not either 'APL', 'APL_EX_APL', 'EMP_APL', 'EX_EMP_APL'.
688 -- - assignment type is 'E' and the system person type of the person
689 -- is not either 'EMP', 'EMP_APL' or 'EX_EMP'.
690 --
691 -- Access Status:
692 -- Internal Table Handler Use Only.
693 --
694 procedure chk_assignment_type
698 ,p_effective_date in date
695 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
696 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
697 ,p_person_id in per_all_assignments_f.person_id%TYPE
699 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
700 ,p_validation_start_date in date
701 );
702 --
703 -- --------------------------------------------------------------------------+
704 -- |-------------------------< chk_change_reason >---------------------------|
705 -- --------------------------------------------------------------------------+
706 --
707 -- Description:
708 -- - Validates that for an employee assignment the change reason exists as a
709 -- lookup code on HR_LOOKUPS for the lookup type 'EMP_ASSIGN_REASON' with
710 -- an enabled flag set to 'Y' and the effective start date of the assignment
711 -- between start date active and end date active on HR_LOOKUPS.
712 -- - Validates that for an applicant assignment the change reason exists as a
713 -- lookup code on HR_LOOKUPS for the lookup type 'APL_ASSIGN_REASON' with
714 -- an enabled flag set to 'Y' and the effective start date of the assignment
715 -- between start date active and end date active on HR_LOOKUPS.
716 --
717 -- Pre-conditions:
718 -- A valid assignment_type
719 --
720 -- In Arguments:
721 -- p_assignment_id
722 -- p_assignment_type
723 -- p_change_reason
724 -- p_effective_date
725 -- p_validation_start_date
726 -- p_validation_end_date
727 -- p_object_version_number
728 --
729 -- Post Success:
730 -- Processing continues if:
731 -- - for employee assignments, change reason exists as a lookup code in
732 -- HR_LOOKUPS for the lookup type 'EMP_ASSIGN_REASON' where the
733 -- enabled flag is 'Y' and the effective start date of the assignment
734 -- is between start date active and end date active on HR_LOOKUPS.
735 -- - for applicant assignments, change reason exists as a lookup code in
736 -- HR_LOOKUPS for the lookup type 'APL_ASSIGN_REASON' where the
737 -- enabled flag is 'Y' and the effective start date of the assignment
738 -- is between start date active and end date active on HR_LOOKUPS.
739 --
740 -- Post Failure:
741 -- An application error is raised and processing ends if:
742 -- - for employee assignments, change reason does'nt exist as a lookup
743 -- code in HR_LOOKUPS for the lookup type 'EMP_ASSIGN_REASON' where
744 -- the enabled flag is 'Y' and the effective start date of the
745 -- assignment is between start date active and end date active
746 -- on HR_LOOKUPS.
747 -- - for applicant assignments, change reason does'nt exist as a lookup
748 -- code in HR_LOOKUPS for the lookup type 'APL_ASSIGN_REASON' where
749 -- the enabled flag is 'Y' and the effective start date of the
750 -- assignment is between start date active and end date active
751 -- on HR_LOOKUPS.
752 --
753 -- Access Status:
754 -- Internal Table Handler Use Only.
755 --
756 procedure chk_change_reason
757 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
758 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
759 ,p_change_reason in per_all_assignments_f.change_reason%TYPE
760 ,p_effective_date in date
761 ,p_validation_start_date in date
762 ,p_validation_end_date in date
763 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
764 );
765 --
766 -- --------------------------------------------------------------------------+
767 -- |-------------------------< chk_contig_ass >------------------------------|
768 -- --------------------------------------------------------------------------+
769 --
770 -- Description:
771 -- Validates that if an attempt is made to date effectively delete
772 -- a primary assignment, another contiguous non-primary assignment must
773 -- exist in order to be converted to a primary assignment.
774 --
775 -- Pre-conditions:
776 -- None
777 --
778 -- In Arguments:
779 -- p_primary_flag
780 -- p_person_id
781 -- p_effective_date
782 -- p_datetrack_mode
783 --
784 -- Post Success:
785 -- If a contiguous non-primary assignment can be found then processing
786 -- continues.
787 --
788 -- Post Failure:
789 -- If no contiguous non-primary assignments can be found then an
790 -- application error is raised and processing is terminated.
791 --
792 -- Access Status:
793 -- Internal Table Handler Use Only.
794 --
795 procedure chk_contig_ass
796 (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
797 ,p_person_id in per_all_assignments_f.person_id%TYPE
798 ,p_effective_date in date
799 ,p_datetrack_mode in varchar2
800 );
801 --
802 -- --------------------------------------------------------------------------+
803 -- |---------------------< chk_date_probation_end >--------------------------|
804 -- --------------------------------------------------------------------------+
805 --
806 -- Description:
807 --
808 -- Validates that date probation end should be after the earliest effective
809 -- start date for the assignment.
810 --
814 --
811 -- On insert and when date probation end, probation period and probation
812 -- unit are all not null and the value of probation unit is not 'H' the
813 -- value of date probation end is calculated.
815 -- Pre-conditions:
816 -- Valid assignment type
817 -- Valid probation period
818 -- Valid probation unit
819 --
820 -- In Arguments:
821 -- p_assignment_id
822 -- p_date_probation_end
823 -- p_assignment_type
824 -- p_probation_period
825 -- p_probation_unit
826 -- p_primary_flag
827 -- p_validation_start_date
828 -- p_effective_date
829 -- p_object_version_number
830 --
831 -- Post Success:
832 -- Processing continues if:
833 -- - date probation end is null.
834 -- - date probation end is not null and employee type is employee.
835 -- - date probation end is not null and after the earliest effective
836 -- start date for the assignment.
837 -- - on update, date probation end is the same as or after the validation
838 -- start date for the assignment.
839 --
840 -- Post Failure:
841 -- An application error is raised and processing is terminated if:
842 -- - date probation end is not null and the assignment type is not of
843 -- employee.
844 -- - date probation end is not null but is before the earliest effective
845 -- start date for the assignment.
846 -- - on update, date probation end is not null but is before the
847 -- validation start date of the assignment.
848 --
849 -- Access Status:
850 -- Internal Table Handler Use Only.
851 --
852 procedure chk_date_probation_end
853 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
854 ,p_date_probation_end in per_all_assignments_f.date_probation_end%TYPE
855 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
856 ,p_probation_period in per_all_assignments_f.probation_period%TYPE
857 ,p_probation_unit in per_all_assignments_f.probation_unit%TYPE
858 ,p_validation_start_date in date
859 ,p_effective_date in date
860 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
861 );
862 --
863 -- --------------------------------------------------------------------------+
864 -- |--------------------< chk_default_code_comb_id >-------------------------|
865 -- --------------------------------------------------------------------------+
866 --
867 -- Description:
868 -- - Validates that when default code comb is not null, default code comb
869 -- exists in GL_CODE_COMBINATIONS.
870 -- - Validates that the default code comb is being set for an employee
871 -- or applicant or offer assignment.
872 -- - Validates that the enabled flag is set to 'Y' for the default code
873 -- combination.
874 -- - Validates that the effective start date of the assignment is between
875 -- the start date active and end date active of the default code
876 -- combination.
877 --
878 -- Pre-conditions:
879 -- None
880 --
881 -- In Arguments:
882 -- p_assignment_id
883 -- p_default_code_comb_id
884 -- p_assignment_type
885 -- p_validation_start_date
886 -- p_validation_end_date
887 -- p_effective_date
888 -- p_object_version_number
889 --
890 -- Post Success:
891 -- Processing continues if:
892 -- - default code comb is null.
893 -- - default code comb is not null and exists in GL_CODE_COMBINATIONS.
894 -- - default code comb is set for an employee or applicant or offer assignment.
895 -- - enabled flag for the default code combination is set to 'Y'.
896 -- - the effective start date of the assignment is between start date
897 -- active and end date active of the default code combination.
898 --
899 -- Post Failure:
900 -- An application error is raised and processing is terminated if:
901 -- - default code comb is not null but does'nt exist in
902 -- GL_CODE_COMBINATIONS.
903 -- - default code comb is set for a non employee or non applicant or non
904 -- offer assignment.
905 -- - enabled flag for the default code combination is not set to 'Y'.
906 -- - the effective start date of the assignment is not between start
907 -- date active and end date active of the default code combination.
908 --
909 -- Access Status:
910 -- Internal Table Handler Use Only
911 --
912 procedure chk_default_code_comb_id
913 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
914 ,p_default_code_comb_id in per_all_assignments_f.default_code_comb_id%TYPE
915 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
916 ,p_effective_date in date
917 ,p_validation_start_date in date
918 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
919 );
920 --
921 -- --------------------------------------------------------------------------+
922 -- |----------------------< chk_del_organization_id >------------------------|
923 -- --------------------------------------------------------------------------+
924 --
925 -- Description:
926 -- Checks to see if manager_flag is set to 'Y' on delete whether another
927 -- assignment also has the manager_flag set within the same organization.
928 --
929 -- Pre-conditions:
930 -- A valid Organization ID
934 -- p_effective_date
931 --
932 -- In Arguments:
933 -- p_assignment_id
935 -- p_manager_flag
936 -- p_organization_id
937 --
938 -- Post Success:
939 -- Boolean flags set as approrpiate.
940 --
941 -- Post Failure:
942 -- An application error is raised and processing is terminated if any
943 -- of the following cases are found :
944 -- - The organization_id is does not exists or is not date effective
945 -- - The business group of the organization is invalid
946 --
947 -- Access Status:
948 -- Internal Table Handler Use Only.
949 --
950 procedure chk_del_organization_id
951 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
952 ,p_effective_date in date
953 ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
954 ,p_organization_id in per_all_assignments_f.organization_id%TYPE
955 ,p_org_now_no_manager_warning in out nocopy boolean
956 );
957 -- --------------------------------------------------------------------------+
958 -- |-----------------------< chk_employment_category >-----------------------|
959 -- --------------------------------------------------------------------------+
960 --
961 -- Description:
962 -- - Validates that the employment category exists as a lookup code on
963 -- HR_LOOKUPS for the lookup type 'EMP_CAT' with an enabled flag set to
964 -- 'Y' and the effective start date of the assignment between start date
965 -- active and end date active on HR_LOOKUPS.
966 -- - Validates that the assignment is an employee or applicant or benefit or
967 -- offer assignment.
968 --
969 -- Pre-conditions:
970 -- A valid assignment type.
971 --
972 -- In Arguments:
973 -- p_assignment_id
974 -- p_assignment_type
975 -- p_employment_category
976 -- p_effective_date
977 -- p_validation_start_date
978 -- p_validation_end_date
979 -- p_object_version_number
980 --
981 -- Post Success:
982 -- Processing continues if:
983 -- - employment category exists as a lookup code in HR_LOOKUPS
984 -- for the lookup type 'EMP_CAT' where the enabled flag is 'Y'
985 -- and the effective start date of the assignment is between
986 -- start date active and end date active on HR_LOOKUPS.
987 -- - the assignment is an employee or applicant or benefit or offer assignment.
988 --
989 -- Post Failure:
990 -- An application error is raised and processing ends if:
991 -- - employment category does'nt exist as a lookup code in HR_LOOKUPS
992 -- for the lookup type 'EMP_CAT' where the enabled flag is 'Y'
993 -- and the effective start date of the assignment is between
994 -- start date active and end date active on HR_LOOKUPS.
995 -- - the assignment is'nt an employee or applicant or benefit or offer assignment.
996 --
997 -- Access Status:
998 -- Internal Table Handler Use Only.
999 --
1000 procedure chk_employment_category
1001 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1002 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1003 ,p_employment_category in per_all_assignments_f.employment_category%TYPE
1004 ,p_effective_date in date
1005 ,p_validation_start_date in date
1006 ,p_validation_end_date in date
1007 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1008 );
1009 --
1010 -- --------------------------------------------------------------------------+
1011 -- |-------------------------< chk_frequency >-------------------------------|
1012 -- --------------------------------------------------------------------------+
1013 --
1014 -- Description:
1015 -- - Validates that the frequency exists as a lookup code on HR_LOOKUPS for
1016 -- the lookup type 'FREQUENCY' with an enabled flag set to 'Y' and the
1017 -- effective start date of the assignment between start date active
1018 -- and end date active on HR_LOOKUPS.
1019 --
1020 -- Pre-conditions:
1021 -- None
1022 --
1023 -- In Arguments:
1024 -- p_assignment_id
1025 -- p_frequency
1026 -- p_effective_date
1027 -- p_object_version_number
1028 -- p_validation_start_date
1029 -- p_validation_end_date
1030 --
1031 -- Post Success:
1032 -- Processing continues if :
1033 -- - frequency exists as a lookup code in HR_LOOKUPS for the
1034 -- lookup type 'FREQUENCY' where the enabled flag is 'Y'
1035 -- and the effective start date of the assignment is between
1036 -- start date active and end date active on HR_LOOKUPS.
1037 --
1038 -- Post Failure:
1039 -- An application error is raised and processing ends if:
1040 -- - frequency does'nt exist as a lookup code in HR_LOOKUPS for the
1041 -- lookup type 'FREQUENCY' where the enabled flag is 'Y'
1042 -- and the effective start date of the assignment is between
1043 -- start date active and end date active on HR_LOOKUPS.
1044 --
1045 -- Access Status:
1046 -- Internal Table Handler Use Only.
1047 --
1048 procedure chk_frequency
1049 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1050 ,p_frequency in per_all_assignments_f.frequency%TYPE
1051 ,p_effective_date in date
1052 ,p_validation_start_date in date
1053 ,p_validation_end_date in date
1057 -- --------------------------------------------------------------------------+
1054 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1055 );
1056 --
1058 -- |------------------------< chk_future_primary >---------------------------|
1059 -- --------------------------------------------------------------------------+
1060 --
1061 -- Description:
1062 -- Validates that a non-primary assignment cannot be date effectively
1063 -- deleted if it is update to a primary assignment in the future.
1064 --
1065 -- Pre-conditions:
1066 -- None
1067 --
1068 -- In Arguments:
1069 -- p_assignment_id
1070 -- p_primary_flag
1071 -- p_effective_date
1072 --
1073 -- Post Success:
1074 -- If the non-primary assignment does not become primary in the future
1075 -- then processing continues.
1076 --
1077 -- Post Failure:
1078 -- If the non-primary assignment becomes primary in the future then an
1079 -- application error is raised and processing is terminated.
1080 --
1081 -- Access Status:
1082 -- Internal Table Handler Use Only.
1083 --
1084 procedure chk_future_primary
1085 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1086 ,p_primary_flag in per_all_assignments_f.primary_flag%TYPE
1087 ,p_datetrack_mode in varchar2
1088 ,p_effective_date in date
1089 );
1090 --
1091 -- --------------------------------------------------------------------------+
1092 -- |---------------------------< chk_grade_id >------------------------------|
1093 -- --------------------------------------------------------------------------+
1094 --
1095 -- Description:
1096 -- - Validates that the grade exists in PER_GRADES where the effective start
1097 -- date of the assignment is between date from and date to of the grade.
1098 -- - Validates that the business group of the grade is the same as the
1099 -- business group of the assignment.
1100 -- - Validates when the grade is not set that the special ceiling step is
1101 -- not set.
1102 --
1103 -- Pre-conditions:
1104 -- A valid business group
1105 -- A valid assignment type
1106 -- A valid vacancy
1107 --
1108 -- In Arguments:
1109 -- p_assignment_id
1110 -- p_business_group_id
1111 -- p_assignment_type
1112 -- p_grade_id
1113 -- p_vacancy_id
1114 -- p_special_ceiling_step_id
1115 -- p_effective_date
1116 -- p_object_version_number
1117 -- p_validation_start_date
1118 -- p_validation_end_date
1119 --
1120 -- Post Success:
1121 -- Processing continues if :
1122 -- - the grade exists in PER_GRADES where the effective start date of
1123 -- the assignment is between date from and date to of the grade.
1124 -- - the business group of the grade is the same as the business group
1125 -- of the assignment.
1126 -- - the grade and special ceiling step are both not set.
1127 --
1128 -- Post Failure:
1129 -- An application error is raised and processing ends if:
1130 -- - the grade does'nt exist in PER_GRADES where the effective start
1131 -- date of the assignment is between date from and date to of the
1132 -- grade.
1133 -- - the business group of the grade is different to the business group
1134 -- of the assignment.
1135 -- - the grade is not set but the special ceiling step is set.
1136 --
1137 procedure chk_grade_id
1138 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1139 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1140 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1141 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
1142 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1143 ,p_special_ceiling_step_id in per_all_assignments_f.special_ceiling_step_id%TYPE
1144 ,p_effective_date in date
1145 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1146 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1147 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1148 );
1149 --
1150 -- --------------------------------------------------------------------------+
1151 -- |-----------------------------< chk_job_id >------------------------------|
1152 -- --------------------------------------------------------------------------+
1153 --
1154 -- Description:
1155 -- - Validates that the job exists in PER_JOBS.
1156 -- - Validates that the end date of the job is the same as or after the
1157 -- validation start date of the assignment.
1158 -- - Validates that the business group of the assignment is the same as the
1159 -- business group of the job.
1160 --
1161 -- Pre-conditions:
1162 -- Valid business group
1163 -- Valid assignment type
1164 -- Valid vacancy
1165 --
1166 -- In Arguments:
1167 -- p_job_id
1168 -- p_business_group_id
1169 -- p_assignment_type
1170 -- p_vacancy_id
1171 -- p_validation_start_date
1172 -- p_validation_end_date
1173 --
1174 -- Post Success:
1175 -- Processing continues if:
1176 -- - the job exists and is date effective in PER_JOBS.
1177 -- - the job end date is the same as or after the validation start
1178 -- date of the assignment.
1179 -- - the business group of the job is the same as the business group
1180 -- of the assignment.
1181 --
1182 -- Post Failure:
1186 -- date of the assignment.
1183 -- An application error is raised and processing is terminated if:
1184 -- - the job does not exist or is not date effective in PER_JOBS.
1185 -- - the job end date is not the same as or after the validation start
1187 -- - the business group of the job is different to the business group
1188 -- of the assignment.
1189 --
1190 -- Access Status:
1191 -- Internal Table Handler Use Only.
1192 --
1193 procedure chk_job_id
1194 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1195 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1196 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1197 ,p_job_id in per_all_assignments_f.job_id%TYPE
1198 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1199 ,p_effective_date in date
1200 ,p_validation_start_date in date
1201 ,p_validation_end_date in date
1202 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1203 );
1204 --
1205 -- --------------------------------------------------------------------------+
1206 -- |----------------------< chk_job_id_grade_id >----------------------------|
1207 -- --------------------------------------------------------------------------+
1208 --
1209 -- Description:
1210 -- Validates that the job_id and grade_id combination in per_valid_grades
1211 -- matches the combination for the assignment.
1212 --
1213 -- Pre-conditions:
1214 -- A valid job_id
1215 -- A valid grade_id
1216 --
1217 -- In Arguments:
1218 -- p_job_id
1219 -- p_grade_id
1220 -- p_validation_start_date
1221 -- p_validation_end_date
1222 --
1223 -- Out Arguments:
1224 -- p_inv_job_grade_warning
1225 --
1226 -- Post Success:
1227 -- Processing continues if:
1228 -- - The <validation attribute 1> and <validation attribute 2>
1229 -- combination in <Child Table> matches the corresponding combination
1230 -- for the <entity> date effectively.
1231 --
1232 -- Post Failure:
1233 -- An application error is raised and processing is terminated if:
1234 -- - A flag (p_inv_<val. attr. 1>_<val. attr. 2>_warning) is set to true
1235 -- when the <val. attr. 1> and <val. attr. 2> combination do not match
1236 -- with a combination in <Child Table>. This flag is set to false when
1237 -- the combination does exist. The flag will always be false when
1238 -- <val. attr. 1> and <val. attr. 2> are not modified in this
1239 -- transaction.
1240 --
1241 procedure chk_job_id_grade_id
1242 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1243 ,p_job_id in per_all_assignments_f.job_id%TYPE
1244 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
1245 ,p_effective_date in date
1246 ,p_validation_start_date in date
1247 ,p_validation_end_date in date
1248 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1249 ,p_inv_job_grade_warning out nocopy boolean
1250 );
1251 -- --------------------------------------------------------------------------+
1252 -- |--------------------------< chk_location_id >----------------------------|
1253 -- --------------------------------------------------------------------------+
1254 --
1255 -- Description:
1256 -- - Validates that the location_id exists in HR_LOCATIONS.
1257 -- - Validates that when location inactive date is set that the inactive
1258 -- date is after the validation end date for the assignment.
1259 --
1260 -- Pre-conditions:
1261 -- A valid assignment_type
1262 -- A valid vacancy_id
1263 --
1264 -- In Arguments:
1265 -- p_assignment_id
1266 -- p_location_id
1267 -- p_assignment_type
1268 -- p_vacancy_id
1269 -- p_validation_start_date
1270 -- p_validation_end_date
1271 -- p_effective_date
1272 -- p_object_version_number
1273 --
1274 -- Post Success:
1275 -- - If the location_id exists and is 'active' on hr_locations then
1276 -- processing continues.
1277 -- - the location inactive date is set after the validation end date of
1278 -- the assignment.
1279 --
1280 -- Post Failure:
1281 -- - If the location_id does not exist or is 'inactive' on hr_locations
1282 -- then an application_error is raised and processing is terminated.
1283 -- - the location inactive date is not set after the validation end date of
1284 -- the assignment.
1285 --
1286 -- Access Status
1287 -- Internal Table Handler Use Only.
1288 --
1289 procedure chk_location_id
1290 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1291 ,p_location_id in per_all_assignments_f.location_id%TYPE
1292 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1293 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1294 ,p_effective_date in date
1295 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1296 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1297 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1298 );
1299 --
1300 -- --------------------------------------------------------------------------+
1301 -- |-------------------------< chk_manager_flag >----------------------------|
1305 -- - Validates that manager flag is set to either 'A' or 'E'.
1302 -- --------------------------------------------------------------------------+
1303 --
1304 -- Description:
1306 -- - Checks to see if manager flag is set to 'Y' on insert of an employee
1307 -- assignment whether another assignment also has the manager_flag set
1308 -- within the same organization.
1309 --
1310 -- - Checks to see if manager flag is changed from 'Y' to 'N' on update
1311 -- of an employee assignment whether other 'managers' exist within the
1312 -- same organization.
1313 --
1314 -- Pre-conditions:
1315 -- A valid assignment type.
1316 -- A valid Organization
1317 --
1318 -- In Arguments:
1319 -- p_assignment_id
1320 -- p_assignment_type
1321 -- p_manager_flag
1322 -- p_organization_id
1323 -- p_effective_date
1324 -- p_object_version_number
1325 --
1326 -- Post Success:
1327 -- Processing continues if:
1328 -- - manager flag is set and is either 'Y' or 'N'.
1329 --
1330 -- Post Failure:
1331 -- An application error is raised and processing ends if:
1332 -- - manager flag is set and is not either 'Y' or 'N'.
1333 --
1334 -- Warning flags are set if,
1335 -- - a manager already exists in the organisation on insert
1336 -- of an assignment or if manager exists in the
1337 -- organisation on update.
1338 --
1339 -- Access Status:
1340 -- Internal Table Handler Use Only.
1341 --
1342 procedure chk_manager_flag
1343 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1344 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1345 ,p_organization_id in per_all_assignments_f.organization_id%TYPE
1346 ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
1347 ,p_effective_date in date
1348 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1349 ,p_other_manager_warning in out nocopy boolean
1350 ,p_no_managers_warning in out nocopy boolean
1351 );
1352 --
1353 -- --------------------------------------------------------------------------+
1354 -- |--------------------< chk_frequency_normal_hours >-----------------------|
1355 -- --------------------------------------------------------------------------+
1356 --
1357 -- Description:
1358 -- - Validates that frequency and normal hours are either set or not set.
1359 -- - Validates that value for normal_hours does not exceed the maximum for
1360 -- the frequency.
1361 --
1362 -- Pre-conditions:
1363 -- A valid Frequency
1364 --
1365 -- In Arguments:
1366 -- p_assignment_id
1367 -- p_frequency
1368 -- p_normal_hours
1369 -- p_effective_date
1370 -- p_object_version_number
1371 --
1372 -- Post Success:
1373 -- Processing continues if :
1374 -- - frequency and normal hours are both set or not set.
1375 -- - The value for normal_hours does not exceed the corresponding
1376 -- frequency.
1377 --
1378 -- Post Failure:
1379 -- An application error is raised and processing ends if:
1380 -- - frequency and normal hours are not both set or not set.
1381 -- - The value for normal_hours exceeds the frequency.
1382 --
1383 -- Access Status:
1384 -- Internal Table Handler Use Only
1385 --
1386 procedure chk_frequency_normal_hours
1387 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1388 ,p_frequency in per_all_assignments_f.frequency%TYPE
1389 ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
1390 ,p_effective_date in date
1391 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1392 );
1393 -- --------------------------------------------------------------------------+
1394 -- |-----------------------< chk_organization_id >---------------------------|
1395 -- --------------------------------------------------------------------------+
1396 --
1397 -- Description:
1398 -- - Validates that organization exists and that the effective start date of
1399 -- the assignment is between date from and date to of the organization in
1400 -- HR_ORGANIZATION_UNITS.
1401 -- - Validates that business group of the organization is the same as
1402 -- the business group of the assignment.
1403 -- - Validates for non primary employee assignments and all applicant assignments
1404 -- other than the first that the organization exists in PER_ORGANIZATION_UNITS.
1405 -- - Validates that when inserting a primary assignment that the organization
1406 -- exists in HR_ORGANIZATION_UNITS between date from and date to.
1407 -- - Validates that when inserting or updating a non primary assignment that
1408 -- the organization exists in PER_ORGANIZATION_UNITS between date from and
1409 -- date to.
1410 --
1411 -- Pre-conditions:
1412 -- A valid primary flag
1413 -- A valid business group
1414 -- A valid assignment type
1415 -- A valid vacancy
1416 --
1417 -- In Arguments:
1418 -- p_primary_flag
1419 -- p_assignment_id
1420 -- p_organization_id
1421 -- p_business_group_id
1422 -- p_assignment_type
1423 -- p_vacancy_id
1424 -- p_validation_start_date
1425 -- p_validation_end_date
1426 -- p_effective_date
1427 -- p_manager_flag
1428 -- p_object_version_number
1429 --
1430 -- Post Success:
1431 -- Processing continues if :
1435 -- - The business group of the organization matches the business group
1432 -- - The organization exists in HR_ORGANIZATION_UNITS and the effective
1433 -- start date of the assignment is between date from and date to of the
1434 -- organization.
1436 -- of the assignment.
1437 -- - The organization exists in PER_ORGANIZATION_UNITS for non primary
1438 -- employee assignments and all other applicant assignments apart
1439 -- from the first.
1440 -- - when inserting a primary assignment the organization exists in
1441 -- HR_ORGANIZATION_UNITS between date from and date to.
1442 -- - when inserting or updating a primary assignment the organization
1443 -- exists in PER_ORGANIZATION_UNITS between date from and date to.
1444 --
1445 -- Post Failure:
1446 -- An application error is raised and processing is terminated if any
1447 -- of the following cases are found :
1448 -- - The organization does'nt exist in HR_ORGANIZATION_UNITS or the effective
1449 -- start date of the assignment is not between date from and date to of the
1450 -- organization.
1451 -- - The business group of the organization does not match the business group
1452 -- of the assignment.
1453 -- - The organization does'nt exist in PER_ORGANIZATION_UNITS for non primary
1454 -- employee assignments and all other applicant assignments apart
1455 -- from the first.
1456 -- - when inserting a primary assignment the organization does'nt exist
1457 -- in HR_ORGANIZATION_UNITS between date from and date to.
1458 -- - when inserting or updating a primary assignment the organization
1459 -- does'nt exist in PER_ORGANIZATION_UNITS between date from and date to.
1460 --
1461 -- Access Status:
1462 -- Internal Table Handler Use Only.
1463 --
1464 procedure chk_organization_id
1465 (p_primary_flag in per_all_assignments_f.primary_flag%TYPE
1466 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1467 ,p_organization_id in per_all_assignments_f.organization_id%TYPE
1468 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
1469 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1470 ,p_vacancy_id in per_all_assignments_f.vacancy_id%TYPE
1471 ,p_validation_start_date in per_all_assignments_f.effective_start_date%TYPE
1472 ,p_validation_end_date in per_all_assignments_f.effective_end_date%TYPE
1473 ,p_effective_date in date
1474 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1475 ,p_manager_flag in per_all_assignments_f.manager_flag%TYPE
1476 ,p_org_now_no_manager_warning in out nocopy boolean
1477 ,p_other_manager_warning in out nocopy boolean
1478 );
1479 --
1480 -- ---------------------------------------------------------------------------+
1481 -- |----------------------< chk_bargaining_unit_code >------------------------|
1482 -- ---------------------------------------------------------------------------+
1483 -- Description:
1484 -- Validates that the bargaining_unit_code entered exists in fnd_common_lookups
1485 -- on the effective date.
1486 --
1487 -- Pre-conditions:
1488 -- A valid bargaining_unit_code
1489 --
1490 -- In Arguments:
1491 -- p_assignment_id
1492 -- p_bargaining_unit_code
1493 -- p_effective_date
1494 -- p_object_version_number
1495 -- p_validation_start_date
1496 -- p_validation_end_date
1497 --
1498 -- Post Success:
1499 -- Processing continues if :
1500 -- - the bargaining_unit_code is valid
1501 --
1502 -- Post Failure:
1503 -- An application error is raised and processing ends if:
1504 -- - the bargaining_unit_code does not exist in fnd_common_lookups on the
1505 -- effective date.
1506 --
1507 -- Access Status:
1508 -- Internal Table Handler Use Only
1509 --
1510 --
1511 procedure chk_bargaining_unit_code
1512 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1513 ,p_bargaining_unit_code in per_all_assignments_f.bargaining_unit_code%TYPE
1514 ,p_effective_date in date
1515 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1516 ,p_validation_start_date in date
1517 ,p_validation_end_date in date
1518 );
1519 --
1520 -- ---------------------------------------------------------------------------+
1521 -- |----------------------< chk_hourly_salaried_code >------------------------|
1522 -- ---------------------------------------------------------------------------+
1523 -- Description:
1524 -- Validates that the hourly_salaried_code entered exists in fnd_common_lookups
1525 -- on the effective date.
1526 --
1527 -- Pre-conditions:
1528 -- A valid hourly_salaried_code
1529 --
1530 -- In Arguments:
1531 -- p_assignment_id
1532 -- p_hourly_salaried_code
1533 -- p_effective_date
1534 -- p_object_version_number
1535 -- p_validation_start_date
1536 -- p_validation_end_date
1537 -- p_pay_basis_id
1538 -- Out Argument:
1539 -- p_hourly_salaried_warning
1540 --
1541 -- Post Success:
1542 -- Processing continues if :
1543 -- - the hourly_salaried_code is valid
1544 --
1545 -- Post Failure:
1546 -- An application error is raised and processing ends if:
1547 -- - the hourly_salaried_code does not exist in fnd_common_lookups on the
1551 -- Internal Table Handler Use Only
1548 -- effective date.
1549 --
1550 -- Access Status:
1552 --
1553 --
1554 procedure chk_hourly_salaried_code
1555 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1556 ,p_hourly_salaried_code in per_all_assignments_f.hourly_salaried_code%TYPE
1557 ,p_effective_date in date
1558 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1559 ,p_validation_start_date in date
1560 ,p_validation_end_date in date
1561 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
1562 ,p_hourly_salaried_warning in out nocopy boolean
1563 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
1564 );
1565 --
1566 -- --------------------------------------------------------------------------+
1567 -- |---------------------< return_legislation_code >-------------------------|
1568 -- --------------------------------------------------------------------------+
1569 --
1570 -- Description:
1571 -- Return the legislation code for a specific assignment
1572 --
1573 -- Prerequisites:
1574 -- The assignment identified by p_assignment_id already exists.
1575 --
1576 -- In Arguments:
1577 -- p_assignment_id
1578 --
1579 -- Post Success:
1580 -- If the assignment is found this function will return the assignment's business
1581 -- group legislation code.
1582 --
1583 -- Post Failure:
1584 -- An error is raised if the assignment does not exist.
1585 --
1586 -- Access Status:
1587 -- Internal Development Use Only.
1588 --
1589 function return_legislation_code
1590 (p_assignment_id in number
1591 ) return varchar2;
1592 --
1593 --
1594 -- ---------------------------------------------------------------------------+
1595 -- |-------------------------< chk_frozen_single_pos >------------------------|
1596 -- ---------------------------------------------------------------------------+
1597 --
1598 -- Description:
1599 -- Validates that the whether another assignment exists for a Single Position
1600 -- on the effective date.
1601 --
1602 -- Pre-conditions:
1603 -- A valid position_id
1604 --
1605 -- In Arguments:
1606 -- p_assignment_id
1607 -- p_position_id
1608 -- p_effective_date
1609 --
1610 -- Post Success:
1611 -- Processing continues if :
1612 -- - the no assignment exist if the position is Single position
1613 --
1614 -- Post Failure:
1615 -- An application error is raised and processing ends if:
1616 -- - the assignment exists and the position is Single position
1617 -- as of effective date.
1618 --
1619 -- Access Status:
1620 -- Internal Table Handler Use Only
1621 --
1622 procedure chk_frozen_single_pos
1623 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1624 ,p_position_id in per_all_assignments_f.position_id%TYPE
1625 ,p_effective_date in date
1626 ,p_assignment_type in varchar2 default 'E'
1627 );
1628 --
1629 -- ---------------------------------------------------------------------------+
1630 -- |-------------------------< chk_single_position >------------------------|
1631 -- ---------------------------------------------------------------------------+
1632 --
1633 -- Description:
1634 -- Validates that the whether another assignment exists for a Single Position
1635 -- on the effective date.
1636 --
1637 -- Pre-conditions:
1638 -- A valid position_id
1639 --
1640 -- In Arguments:
1641 -- p_assignment_id
1642 -- p_position_id
1643 -- p_effective_date
1644 --
1645 -- Post Success:
1646 -- Processing continues if :
1647 -- - the no assignment exist if the position is Single position
1648 --
1649 -- Post Failure:
1650 -- An application error is raised and processing ends if:
1651 -- - the assignment exists and the position is Single position
1652 -- as of effective date.
1653 --
1654 -- Access Status:
1655 -- Internal Table Handler Use Only
1656 --
1657 procedure chk_single_position
1658 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1659 ,p_position_id in per_all_assignments_f.position_id%TYPE
1660 ,p_effective_date in date
1661 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
1662 ,p_assignment_type in per_all_assignments_f.assignment_type%type default 'E'
1663 );
1664 --
1665 --
1666 --
1667 -- ---------------------------------------------------------------------------+
1668 -- |----------------------< pos_assignments_exists >--------------------------|
1669 -- ---------------------------------------------------------------------------+
1670 --
1671 -- Description:
1672 -- Returns whether the assignment exists for the position passed or not as of
1673 -- effective_date
1674 --
1675 -- Pre-conditions:
1676 -- A valid position_id
1677 --
1678 -- In Arguments:
1679 -- p_position_id
1680 -- p_effective_date
1681 --
1682 -- Post Success:
1683 -- Returns true is assignment exists otherwise false
1684 --
1685 -- Post Failure:
1686 -- An application error is raised and processing ends if:
1687 -- No failure
1691 --
1688 --
1689 -- Access Status:
1690 -- Internal Table Handler Use Only
1692 function pos_assignments_exists(
1693 p_position_id number,
1694 p_effective_date date,
1695 p_except_assignment_id number) return boolean;
1696 --
1697 end per_asg_bus1;
[Home] [Help]